VIEW: APPS.OKL_BPD_AR_HEADER_V

Source

View Text - Preformatted

SELECT TO_CHAR(A.ID) ID, A.KHR_ID, A.CONTRACT_NUMBER, A.SOURCE_CODE, A.SOURCE, A.TRANSACTION_NUMBER, A.TRANSACTION_TYPE, A.TRANSACTION_STATUS, A.TRANSACTION_DATE, A.TRX_TYPE, A.DESCRIPTION, A.PRODUCT_NAME, A.AMOUNT, A.CURRENCY_CODE, A.ACTUAL_DRAFT_STATUS_CODE, A.ACTUAL_DRAFT_STATUS, ( SELECT SUM(B.EXTENDED_AMOUNT) FROM RA_CUSTOMER_TRX_LINES_ALL B WHERE A.ID = B.CUSTOMER_TRX_ID AND B.LINE_TYPE IN ('LINE', 'TAX') ) TOTAL_TRANSACTION_AMOUNT, A.CHECK_ID, A.LEDGER_ID, A.ORG_ID, A.TRY_ID, A.ACCOUNT_NUMBER FROM ( SELECT RCT.CUSTOMER_TRX_ID ID, OKHB.ID KHR_ID, OKHB.CONTRACT_NUMBER, 'REC' SOURCE_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES','REC') SOURCE, RCT.TRX_NUMBER TRANSACTION_NUMBER, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS',RCTT.TYPE) TRANSACTION_TYPE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT',RCT.COMPLETE_FLAG,222,222) TRANSACTION_STATUS, RCT.TRX_DATE TRANSACTION_DATE, RCTT.TYPE TRX_TYPE, RCT.INTERFACE_HEADER_ATTRIBUTE1 || decode(RCT.INTERFACE_HEADER_ATTRIBUTE1, NULL, '', ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM', RCTT.TYPE,222,222) DESCRIPTION, OP.NAME PRODUCT_NAME, SUM(EXTENDED_AMOUNT + NVL(TAX_RECOVERABLE,0)) AMOUNT, /* APS.AMOUNT_DUE_ORIGINAL AMOUNT, */ RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE , 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS','ACTUAL') ACTUAL_DRAFT_STATUS, SUM(RCTL.QUANTITY_INVOICED * RCTL.UNIT_SELLING_PRICE) TOTAL_TRANSACTION_AMOUNT2, NULL CHECK_ID, RCT.SET_OF_BOOKS_ID LEDGER_ID, RCT.ORG_ID ORG_ID, ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, TAI.TRY_ID TRY_ID FROM OKL_TXD_AR_LN_DTLS_B TXD, OKC_K_HEADERS_B OKHB, OKL_K_HEADERS OKH, OKL_PRODUCTS OP, RA_CUSTOMER_TRX_ALL RCT, RA_CUSTOMER_TRX_LINES_ALL RCTL, RA_CUST_TRX_TYPES RCTT , AR_PAYMENT_SCHEDULES_ALL APS, HZ_CUST_ACCOUNTS ACCT, OKL_TXL_AR_INV_LNS_B TIL, OKL_TRX_AR_INVOICES_B TAI WHERE TXD.KHR_ID = OKHB.ID AND OKHB.ID = OKH.ID AND OKH.PDT_ID = OP.ID AND TXD.ID = RCTL.INTERFACE_LINE_ATTRIBUTE14 AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID AND RCT.ORG_ID = RCTT.ORG_ID AND RCT.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID AND TXD.TIL_ID_DETAILS = TIL.ID AND TIL.TAI_ID = TAI.ID AND OKHB.CONTRACT_NUMBER = RCTL.INTERFACE_LINE_ATTRIBUTE6 GROUP BY RCT.CUSTOMER_TRX_ID, OKHB.ID, OKHB.CONTRACT_NUMBER, 'REC', OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES','REC'), RCT.TRX_NUMBER, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS',RCTT.TYPE), OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT',RCT.COMPLETE_FLAG,222,222), RCT.TRX_DATE, RCTT.TYPE, RCT.INTERFACE_HEADER_ATTRIBUTE1 || decode(RCT.INTERFACE_HEADER_ATTRIBUTE1, NULL, '', ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM', RCTT.TYPE,222,222) , OP.NAME, APS.AMOUNT_DUE_ORIGINAL, RCT.INVOICE_CURRENCY_CODE, 'ACTUAL', OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS','ACTUAL'), NULL, RCT.SET_OF_BOOKS_ID, RCT.ORG_ID, ACCT.ACCOUNT_NUMBER, TAI.TRY_ID ) A

View Text - HTML Formatted

SELECT TO_CHAR(A.ID) ID
, A.KHR_ID
, A.CONTRACT_NUMBER
, A.SOURCE_CODE
, A.SOURCE
, A.TRANSACTION_NUMBER
, A.TRANSACTION_TYPE
, A.TRANSACTION_STATUS
, A.TRANSACTION_DATE
, A.TRX_TYPE
, A.DESCRIPTION
, A.PRODUCT_NAME
, A.AMOUNT
, A.CURRENCY_CODE
, A.ACTUAL_DRAFT_STATUS_CODE
, A.ACTUAL_DRAFT_STATUS
, ( SELECT SUM(B.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE A.ID = B.CUSTOMER_TRX_ID
AND B.LINE_TYPE IN ('LINE'
, 'TAX') ) TOTAL_TRANSACTION_AMOUNT
, A.CHECK_ID
, A.LEDGER_ID
, A.ORG_ID
, A.TRY_ID
, A.ACCOUNT_NUMBER
FROM ( SELECT RCT.CUSTOMER_TRX_ID ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER
, 'REC' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'REC') SOURCE
, RCT.TRX_NUMBER TRANSACTION_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS'
, RCTT.TYPE) TRANSACTION_TYPE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT'
, RCT.COMPLETE_FLAG
, 222
, 222) TRANSACTION_STATUS
, RCT.TRX_DATE TRANSACTION_DATE
, RCTT.TYPE TRX_TYPE
, RCT.INTERFACE_HEADER_ATTRIBUTE1 || DECODE(RCT.INTERFACE_HEADER_ATTRIBUTE1
, NULL
, ''
, ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM'
, RCTT.TYPE
, 222
, 222) DESCRIPTION
, OP.NAME PRODUCT_NAME
, SUM(EXTENDED_AMOUNT + NVL(TAX_RECOVERABLE
, 0)) AMOUNT
, /* APS.AMOUNT_DUE_ORIGINAL AMOUNT
, */ RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, SUM(RCTL.QUANTITY_INVOICED * RCTL.UNIT_SELLING_PRICE) TOTAL_TRANSACTION_AMOUNT2
, NULL CHECK_ID
, RCT.SET_OF_BOOKS_ID LEDGER_ID
, RCT.ORG_ID ORG_ID
, ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, TAI.TRY_ID TRY_ID
FROM OKL_TXD_AR_LN_DTLS_B TXD
, OKC_K_HEADERS_B OKHB
, OKL_K_HEADERS OKH
, OKL_PRODUCTS OP
, RA_CUSTOMER_TRX_ALL RCT
, RA_CUSTOMER_TRX_LINES_ALL RCTL
, RA_CUST_TRX_TYPES RCTT
, AR_PAYMENT_SCHEDULES_ALL APS
, HZ_CUST_ACCOUNTS ACCT
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TXD.KHR_ID = OKHB.ID
AND OKHB.ID = OKH.ID
AND OKH.PDT_ID = OP.ID
AND TXD.ID = RCTL.INTERFACE_LINE_ATTRIBUTE14
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND RCT.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND TXD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND OKHB.CONTRACT_NUMBER = RCTL.INTERFACE_LINE_ATTRIBUTE6 GROUP BY RCT.CUSTOMER_TRX_ID
, OKHB.ID
, OKHB.CONTRACT_NUMBER
, 'REC'
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'REC')
, RCT.TRX_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS'
, RCTT.TYPE)
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT'
, RCT.COMPLETE_FLAG
, 222
, 222)
, RCT.TRX_DATE
, RCTT.TYPE
, RCT.INTERFACE_HEADER_ATTRIBUTE1 || DECODE(RCT.INTERFACE_HEADER_ATTRIBUTE1
, NULL
, ''
, ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM'
, RCTT.TYPE
, 222
, 222)
, OP.NAME
, APS.AMOUNT_DUE_ORIGINAL
, RCT.INVOICE_CURRENCY_CODE
, 'ACTUAL'
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL')
, NULL
, RCT.SET_OF_BOOKS_ID
, RCT.ORG_ID
, ACCT.ACCOUNT_NUMBER
, TAI.TRY_ID ) A