VIEW: APPS.OKL_CS_LC_ACCOUNT_TAB_UV

Source

View Text - Preformatted

SELECT CNRB.ID, CNRB.CONSOLIDATED_INVOICE_NUMBER, cnrb.trx_status_code, fndv.meaning TRX_STATUS, CNRB.DUE_DATE, CNRB.DATE_CONSOLIDATED, CNRB.AMOUNT, CNRB.IXX_ID, CNRB.CURRENCY_CODE, sob.name set_of_books, cnrb.set_of_books_id, org.name org_name, cnrb.org_id, CNRB.INVOICE_PULL_YN FROM OKL_CNSLD_AR_HDRS_B CNRB,FND_LOOKUPS FNDV,HR_ALL_ORGANIZATION_UNITS ORG,GL_LEDGERS_PUBLIC_V SOB WHERE exists ( SELECT null FROM AR_PAYMENT_SCHEDULES_ALL APS , OKL_CNSLD_AR_STRMS_B LSM , OKL_CNSLD_AR_LINES_B LLN WHERE LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LLN.CNR_ID = CNRB.ID ) AND sob.ledger_id = cnrb.set_of_books_id AND cnrb.trx_status_code = fndv.lookup_code AND fndv.lookup_type = 'OKL_TRANSACTION_STATUS' AND cnrb.org_id = org.organization_id(+) UNION ALL SELECT distinct RACTRX.CUSTOMER_TRX_ID, RACTRX.TRX_NUMBER, TAI.TRX_STATUS_CODE, fndv.meaning TRX_STATUS, APS.DUE_DATE, RACTRX.TRX_DATE, APS.AMOUNT_DUE_ORIGINAL, RACTRX.BILL_TO_CUSTOMER_ID, RACTRX.INVOICE_CURRENCY_CODE, sob.name set_of_books, TAI.SET_OF_BOOKS_ID, org.name org_name, TAI.ORG_ID, TAI.INVOICE_PULL_YN FROM RA_CUSTOMER_TRX_ALL RACTRX, RA_CUSTOMER_TRX_LINES_ALL RACTRL, AR_PAYMENT_SCHEDULES_ALL APS, OKL_TRX_AR_INVOICES_B TAI, OKL_TXL_AR_INV_LNS_B TIL, OKL_TXD_AR_LN_DTLS_B TLD, FND_LOOKUPS FNDV, HR_ALL_ORGANIZATION_UNITS ORG, GL_LEDGERS_PUBLIC_V SOB WHERE RACTRX.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID AND RACTRL.INTERFACE_LINE_ATTRIBUTE14 = TLD.ID AND RACTRL.LINE_TYPE = 'LINE' AND RACTRL.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS' AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL AND TLD.TIL_ID_DETAILS = TIL.ID AND TIL.TAI_ID = TAI.ID AND RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID AND sob.ledger_id = TAI.set_of_books_id AND TAI.trx_status_code = fndv.lookup_code AND fndv.lookup_type = 'OKL_TRANSACTION_STATUS' AND TAI.org_id = org.organization_id(+) Order by 2

View Text - HTML Formatted

SELECT CNRB.ID
, CNRB.CONSOLIDATED_INVOICE_NUMBER
, CNRB.TRX_STATUS_CODE
, FNDV.MEANING TRX_STATUS
, CNRB.DUE_DATE
, CNRB.DATE_CONSOLIDATED
, CNRB.AMOUNT
, CNRB.IXX_ID
, CNRB.CURRENCY_CODE
, SOB.NAME SET_OF_BOOKS
, CNRB.SET_OF_BOOKS_ID
, ORG.NAME ORG_NAME
, CNRB.ORG_ID
, CNRB.INVOICE_PULL_YN
FROM OKL_CNSLD_AR_HDRS_B CNRB
, FND_LOOKUPS FNDV
, HR_ALL_ORGANIZATION_UNITS ORG
, GL_LEDGERS_PUBLIC_V SOB
WHERE EXISTS ( SELECT NULL
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
WHERE LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNRB.ID )
AND SOB.LEDGER_ID = CNRB.SET_OF_BOOKS_ID
AND CNRB.TRX_STATUS_CODE = FNDV.LOOKUP_CODE
AND FNDV.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS'
AND CNRB.ORG_ID = ORG.ORGANIZATION_ID(+) UNION ALL SELECT DISTINCT RACTRX.CUSTOMER_TRX_ID
, RACTRX.TRX_NUMBER
, TAI.TRX_STATUS_CODE
, FNDV.MEANING TRX_STATUS
, APS.DUE_DATE
, RACTRX.TRX_DATE
, APS.AMOUNT_DUE_ORIGINAL
, RACTRX.BILL_TO_CUSTOMER_ID
, RACTRX.INVOICE_CURRENCY_CODE
, SOB.NAME SET_OF_BOOKS
, TAI.SET_OF_BOOKS_ID
, ORG.NAME ORG_NAME
, TAI.ORG_ID
, TAI.INVOICE_PULL_YN
FROM RA_CUSTOMER_TRX_ALL RACTRX
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_TRX_AR_INVOICES_B TAI
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TXD_AR_LN_DTLS_B TLD
, FND_LOOKUPS FNDV
, HR_ALL_ORGANIZATION_UNITS ORG
, GL_LEDGERS_PUBLIC_V SOB
WHERE RACTRX.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.INTERFACE_LINE_ATTRIBUTE14 = TLD.ID
AND RACTRL.LINE_TYPE = 'LINE'
AND RACTRL.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND SOB.LEDGER_ID = TAI.SET_OF_BOOKS_ID
AND TAI.TRX_STATUS_CODE = FNDV.LOOKUP_CODE
AND FNDV.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS'
AND TAI.ORG_ID = ORG.ORGANIZATION_ID(+) ORDER BY 2