This AR Transaction Batch Source query retrieves detailed configuration and status information for invoice batch sources in Oracle Receivables, including numbering settings, transaction types, grouping rules, and various rule-based input methods used in batch processing.
#sqlquery
SELECT
--SQL4FUSION (AN ORBIT ANALYTICS PROJECT)
--Summarizes invoice batch sources to help identify configurations.
SUBSTRB(RBSA.NAME, 1, 30) BATCH_SOURCE_NAME,
SUBSTRB(RBSA.DESCRIPTION, 1, 40) BATCH_SOURCE_DESCRIPTION,
AL6.MEANING TYPE,
AL1.MEANING STATUS,
DECODE(RBSA.AUTO_BATCH_NUMBERING_FLAG, 'Y', 'YES', 'N', 'NO',
NULL) AUTO_BATCH_NUMBERING,
DECODE(RBSA.AUTO_TRX_NUMBERING_FLAG, 'Y', 'YES', 'N', 'NO',
NULL) AUTO_INVOICE_NUMBERING,
DECODE(RBSA.AUTO_TRX_NUMBERING_FLAG,
'N',
0,
'Y',
(
SELECT
NVL(MAX(TRX_NUMBER),
0)
FROM
RA_CUSTOMER_TRX
WHERE
BATCH_SOURCE_ID = RBSA.BATCH_SOURCE_ID
),
0) LAST_INVOICE_NUMBER,
RBSA.LAST_BATCH_NUM LAST_BATCH_NUMBER,
RCTTA.NAME STANDARD_TRANSACTION_TYPE,
RBSA.START_DATE EFFECTIVE_DATE,
RBSA.END_DATE INEFFECTIVE_DATE,
RBSA.AUTO_TRX_NUMBERING_FLAG,
AL4.MEANING INVALID_TAX_RATE,
DECODE(RBSA.CREATE_CLEARING_FLAG, 'Y', 'YES', 'N', 'NO',
NULL) CREATE_CLEARING,
DECODE(RBSA.ALLOW_SALES_CREDIT_FLAG, 'Y', 'YES', 'N', 'NO',
NULL) ALLOW_SALES_CREDIT,
AL5.MEANING INVALID_LINES,
AL7.MEANING GL_DATE_IN_A_CLOSED_PERIOD,
RGR.NAME GROUPING_RULE_NAME
FROM
RA_BATCH_SOURCES_ALL RBSA,
RA_CUST_TRX_TYPES_ALL RCTTA,
RA_GROUPING_RULES RGR,
AR_LOOKUPS AL1,
AR_LOOKUPS AL4,
AR_LOOKUPS AL5,
AR_LOOKUPS AL6,
AR_LOOKUPS AL7
WHERE
1 = 1
AND RBSA.STATUS = AL1.LOOKUP_CODE
AND AL1.LOOKUP_TYPE = 'CODE_STATUS'
AND RBSA.BATCH_SOURCE_TYPE = AL6.LOOKUP_CODE
AND AL6.LOOKUP_TYPE = 'BATCH_SOURCE'
AND RBSA.DEFAULT_INV_TRX_TYPE_SEQ_ID = RCTTA.CUST_TRX_TYPE_SEQ_ID (+)
AND RBSA.INVALID_TAX_RATE_RULE = AL4.LOOKUP_CODE (+)
AND NVL(AL4.LOOKUP_TYPE, 'COR/REJ') = 'COR/REJ'
AND RBSA.INVALID_LINES_RULE = AL5.LOOKUP_CODE (+)
AND NVL(AL5.LOOKUP_TYPE, 'CRE/REJ') = 'CRE/REJ'
AND RBSA.GROUPING_RULE_ID = RGR.GROUPING_RULE_ID (+)
AND RBSA.GL_DATE_PERIOD_RULE = AL7.LOOKUP_CODE (+)
AND NVL(AL7.LOOKUP_TYPE, 'ADJ/REJ') = 'ADJ/REJ'
ORDER BY
RBSA.NAME;