This AR Automatic Transactions Batch Details query retrieves detailed information about auto-billing receipt transactions, including customer details, bill numbers, invoice data, payment methods, currency, maturity date, transaction types, and associated batch and contact information.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves detailed information about auto-billing receipt transactions
HAOU.NAME ORGANIZATION_NAME,
RBA.NAME BATCH_NAME,
DECODE(ABTBR.BATCH_STATUS,'DRAFT','--------',ABTBR.BILL_NUMBER) BILL_NUMBER,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HCA.TAX_ROUNDING_RULE,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HP.CATEGORY_CODE,
ABTBR.MATURITY_DATE,
RBA.SPECIAL_INSTRUCTIONS,
SUBSTRB (HP1.PERSON_FIRST_NAME, 1, 40) || ' ' || SUBSTRB (HP1.PERSON_LAST_NAME, 1, 50) CUSTOMER_CONTACT,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
RCTTA.NAME TRANSACTION_TYPE,
RCTA.STATUS_TRX TRANSACTION_STATUS,
ABTBR.RECEIPT_METHOD_NAME,
AL.MEANING GROUPING_RULE,
RCTA.EXCHANGE_RATE,
ABTBR.BR_CURRENCY BILL_RECEIVABLE_CURRENCY,
NVL(ABTBR.BR_AMOUNT, 0) BILL_RECEIVABLE_AMOUNT,
NVL(ABTBR.BR_MAX_ACCTD_AMOUNT,0) MAXIMUM_ACCOUNTED_AMOUNT,
NVL(ABTBR.BR_MIN_ACCTD_AMOUNT,0) MINIMUM_ACCOUNTED_AMOUNT,
NVL(ABTBR.AMOUNT_ASSIGNED,0) BILL_RECEIVABLE_APPLIED_AMOUNT,
ABTBR.LEAD_DAYS,
RCTA.FOB_POINT CUST_TRX_FOB_POINT,
RCTA.REASON_CODE,
HR.RELATIONSHIP_CODE,
RCTA.DEFAULT_TAX_EXEMPT_FLAG
FROM
AR_BR_TRX_BATCH_RPT ABTBR,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
RA_BATCHES_ALL RBA,
HZ_CUST_ACCOUNT_ROLES HCAR,
HZ_PARTIES HP1,
HZ_RELATIONSHIPS HR,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_TYPES_ALL RCTTA,
AR_LOOKUPS AL,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ABTBR.DRAWEE_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND ABTBR.BATCH_ID = RBA.BATCH_ID
AND ABTBR.DRAWEE_CONTACT_ID = HCAR.CUST_ACCOUNT_ROLE_ID
AND HCAR.ROLE_TYPE= 'CONTACT'
AND HR.SUBJECT_ID = HP1.PARTY_ID
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TABLE_NAME= 'HZ_PARTIES'
AND HR.DIRECTIONAL_FLAG= 'F'
AND ABTBR.TRANSACTION_ID = RCTA.CUSTOMER_TRX_ID
AND RCTTA.CUST_TRX_TYPE_SEQ_ID = RCTA.CUST_TRX_TYPE_SEQ_ID
AND ABTBR.RECEIPT_CREATION_RULE_CODE = AL.LOOKUP_CODE
AND AL.LOOKUP_TYPE= 'AUTO_RECEIPT_CREATION_RULES'
AND RCTTA.ORG_ID = HAOU.ORGANIZATION_ID
ORDER BY
HP.PARTY_NAME,
ABTBR.RECEIPT_METHOD_NAME,
ABTBR.BR_CURRENCY,
ABTBR.BILL_NUMBER,
RCTA.TRX_NUMBER