AR Invoice Register (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Invoice Register SQL query retrieves transaction details for completed customer invoices (transactions) from Oracle Receivables (AR). It includes data about the transaction, its type, customer, GL Account, and organizational context.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query retrieves transaction details for completed customer invoices
  HAOU.NAME ORGANIZATION_NAME,
  RCTA.TRX_NUMBER TRANSACTION_NUMBER,
  RCTA.TRX_DATE TRANSACTION_DATE,  
  RCTTA.NAME TRANSACTION_TYPE,
  RCTTA.TYPE TRANSACTION_CLASS,
  RCTTA.POST_TO_GL POSTABLE_FLAG,  
  RCTLG.GL_DATE ,
  RCTA.INVOICE_CURRENCY_CODE,
  NVL (RCTLG.AMOUNT, 0) ENTERED_AMOUNT,
  NVL (RCTLG.ACCTD_AMOUNT, 0) FUNCTIONAL_AMOUNT,
  HP.PARTY_NAME CUSTOMER_NAME,
  HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT,
  HCA.TAX_ROUNDING_RULE,
  HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
  HCA.CUSTOMER_TYPE,
  RCTA.FOB_POINT,
  RCTA.STATUS_TRX TRANSACTION_STATUS,
  RCTA.REASON_CODE,
  RCTA.DEFAULT_TAX_EXEMPT_FLAG
FROM
  RA_CUSTOMER_TRX_ALL RCTA,
  RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
  RA_CUST_TRX_TYPES_ALL RCTTA,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  GL_CODE_COMBINATIONS GCC
WHERE
  1 = 1
  AND RCTA.CUSTOMER_TRX_ID = RCTLG.CUSTOMER_TRX_ID(+)
  AND RCTLG.ACCOUNT_CLASS = 'REC'
  AND RCTLG.ACCOUNT_SET_FLAG = 'N'
  AND RCTA.CUST_TRX_TYPE_SEQ_ID =RCTTA.CUST_TRX_TYPE_SEQ_ID
  AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID= HP.PARTY_ID
  AND RCTA.COMPLETE_FLAG = 'Y'
  AND RCTA.ORG_ID = HAOU.ORGANIZATION_ID
  AND RCTLG.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
UNION
SELECT 
  HAOU.NAME ORGANIZATION_NAME,
  RCTA.TRX_NUMBER TRANSACTION_NUMBER,
  RCTA.TRX_DATE TRANSACTION_DATE,  
  RCTTA.NAME TRANSACTION_TYPE,
  RCTTA.TYPE TRANSACTION_CLASS,
  RCTTA.POST_TO_GL POSTABLE_FLAG,
  RCTLG.GL_DATE ,
  RCTA.INVOICE_CURRENCY_CODE,
  NVL (RCTLG.AMOUNT, 0) ENTERED_AMOUNT,
  NVL (RCTLG.ACCTD_AMOUNT, 0) FUNCTIONAL_AMOUNT,
  HP.PARTY_NAME CUSTOMER_NAME,
  HCA.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT,
  HCA.TAX_ROUNDING_RULE,
  HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
  HCA.CUSTOMER_TYPE,
  RCTA.FOB_POINT,
  RCTA.STATUS_TRX TRANSACTION_STATUS,
  RCTA.REASON_CODE,
  RCTA.DEFAULT_TAX_EXEMPT_FLAG
FROM
  RA_CUSTOMER_TRX_ALL RCTA,
  RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG,
  RA_CUST_TRX_TYPES_ALL RCTTA,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  GL_CODE_COMBINATIONS GCC
WHERE
  1 = 1
  AND RCTA.CUSTOMER_TRX_ID = RCTLG.CUSTOMER_TRX_ID(+)
  AND RCTLG.ACCOUNT_CLASS = 'REV'
  AND RCTLG.ACCOUNT_SET_FLAG = 'N'
  AND RCTA.CUST_TRX_TYPE_SEQ_ID =RCTTA.CUST_TRX_TYPE_SEQ_ID
  AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID= HP.PARTY_ID
  AND RCTA.COMPLETE_FLAG = 'Y'
  AND RCTA.ORG_ID = HAOU.ORGANIZATION_ID
  AND RCTLG.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID