AR Bills Receivable Register (Oracle Fusion SQL)

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

This AR Bills Receivable Register SQL query retrieves detailed customer transaction information (invoices or credit memos) from Oracle Receivables, with general ledger and account details.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query retrieves detailed customer transaction information
  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.CUSTOMER_TRX_ID = RCTTA.CUST_TRX_TYPE_ID
  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.CUSTOMER_TRX_ID = RCTTA.CUST_TRX_TYPE_ID
  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