VIEW: APPS.JL_BR_AR_COLL_OCC_DOCS_H_V

Source

View Text - Preformatted

SELECT cd.ORG_ID ORG_ID, cd.DOCUMENT_ID JLBR_DOCUMENT_ID, oc.OCCURRENCE_ID JLBR_OCCURRENCE_ID, cd.DOCUMENT_ID JLBR_DOCUMENT_NUMBER, cd.RECEIPT_METHOD_ID JLBR_RECEIPT_METHOD_ID, cd.BANK_ACCT_USE_ID JLBR_BANK_ACCT_USE_ID, decode(oc.occurrence_status,'CANCELED',oc.gl_cancel_date, oc.occurrence_date) JLBR_OCC_DATE, ract.trx_number||'-'||to_char(cd.TERMS_SEQUENCE_NUMBER) JLBR_TRADE_NOTE_NUMBER, oc.GL_DATE JLBR_GL_DATE, decode(oc.occurrence_status,'CANCELED',to_number(NULL),oc.BANK_OCCURRENCE_CODE) JLBR_BANK_OCC_CODE, decode(oc.occurrence_status,'CANCELED',NULL,bo.DESCRIPTION) JLBR_BANK_OCC_DESC, nvl(oc.flag_post_gl,'N') JLBR_TRANSFER_TO_GL_FLAG, decode(oc.occurrence_status,'CANCELED', oc.CANCEL_EVENT_ID, oc.EVENT_ID) EVENT_ID, sys.CODE_COMBINATION_ID_LOSS JLBR_EXCHG_LOSS_CCID , sys.CODE_COMBINATION_ID_GAIN JLBR_EXCHG_GAIN_CCID FROM jl_br_ar_collection_docs_all cd, jl_br_ar_occurrence_docs_all oc, jl_br_ar_bank_occurrences bo, ra_customer_trx_all ract, ar_system_parameters_all sys WHERE cd.customer_trx_id = ract.customer_trx_id And oc.document_id = cd.document_id And bo.bank_occurrence_code = oc.bank_occurrence_code And bo.bank_occurrence_type = oc.bank_occurrence_type And bo.bank_party_id = oc.bank_party_id And ract.org_id = sys.org_id UNION SELECT cd.ORG_ID ORG_ID, cd.DOCUMENT_ID JLBR_DOCUMENT_ID, oc.OCCURRENCE_ID JLBR_OCCURRENCE_ID, cd.DOCUMENT_ID JLBR_DOCUMENT_NUMBER, cd.RECEIPT_METHOD_ID JLBR_RECEIPT_METHOD_ID, cd.BANK_ACCT_USE_ID JLBR_BANK_ACCT_USE_ID, oc.occurrence_date JLBR_OCC_DATE, ract.trx_number||'-'||to_char(cd.TERMS_SEQUENCE_NUMBER) JLBR_TRADE_NOTE_NUMBER, csc.GL_DATE JLBR_GL_DATE, oc.BANK_OCCURRENCE_CODE JLBR_BANK_OCC_CODE, bo.DESCRIPTION JLBR_BANK_OCC_DESC, decode(oc.gl_posted_date,NULL,'N','Y') JLBR_TRANSFER_TO_GL_FLAG, oc.EVENT_ID EVENT_ID, sys.CODE_COMBINATION_ID_LOSS JLBR_EXCHG_LOSS_CCID , sys.cODE_COMBINATION_ID_GAIN JLBR_EXCHG_GAIN_CCID FROM jl_br_ar_collection_docs_all cd, jl_br_ar_occurrence_docs_all oc, jl_br_ar_bank_occurrences bo, jl_br_ar_borderos_all b, jl_br_ar_select_accounts_all csc, ra_customer_trx_all ract, ar_system_parameters_all sys WHERE cd.customer_trx_id = ract.customer_trx_id And oc.document_id = cd.document_id And oc.occurrence_status = 'CANCELED' And bo.bank_occurrence_code = oc.bank_occurrence_code And bo.bank_occurrence_type = oc.bank_occurrence_type And bo.bank_party_id = oc.bank_party_id And bo.std_occurrence_code = 'REMITTANCE' And bo.bank_occurrence_type = 'REMITTANCE_OCCURRENCE' And b.bordero_id = cd.bordero_id And csc.select_account_id = b.select_account_id And ract.org_id = sys.org_id And exists(select 'Y' from xla_events xe where xe.event_id = oc.event_id and xe.process_status_code <> 'P')

View Text - HTML Formatted

SELECT CD.ORG_ID ORG_ID
, CD.DOCUMENT_ID JLBR_DOCUMENT_ID
, OC.OCCURRENCE_ID JLBR_OCCURRENCE_ID
, CD.DOCUMENT_ID JLBR_DOCUMENT_NUMBER
, CD.RECEIPT_METHOD_ID JLBR_RECEIPT_METHOD_ID
, CD.BANK_ACCT_USE_ID JLBR_BANK_ACCT_USE_ID
, DECODE(OC.OCCURRENCE_STATUS
, 'CANCELED'
, OC.GL_CANCEL_DATE
, OC.OCCURRENCE_DATE) JLBR_OCC_DATE
, RACT.TRX_NUMBER||'-'||TO_CHAR(CD.TERMS_SEQUENCE_NUMBER) JLBR_TRADE_NOTE_NUMBER
, OC.GL_DATE JLBR_GL_DATE
, DECODE(OC.OCCURRENCE_STATUS
, 'CANCELED'
, TO_NUMBER(NULL)
, OC.BANK_OCCURRENCE_CODE) JLBR_BANK_OCC_CODE
, DECODE(OC.OCCURRENCE_STATUS
, 'CANCELED'
, NULL
, BO.DESCRIPTION) JLBR_BANK_OCC_DESC
, NVL(OC.FLAG_POST_GL
, 'N') JLBR_TRANSFER_TO_GL_FLAG
, DECODE(OC.OCCURRENCE_STATUS
, 'CANCELED'
, OC.CANCEL_EVENT_ID
, OC.EVENT_ID) EVENT_ID
, SYS.CODE_COMBINATION_ID_LOSS JLBR_EXCHG_LOSS_CCID
, SYS.CODE_COMBINATION_ID_GAIN JLBR_EXCHG_GAIN_CCID
FROM JL_BR_AR_COLLECTION_DOCS_ALL CD
, JL_BR_AR_OCCURRENCE_DOCS_ALL OC
, JL_BR_AR_BANK_OCCURRENCES BO
, RA_CUSTOMER_TRX_ALL RACT
, AR_SYSTEM_PARAMETERS_ALL SYS
WHERE CD.CUSTOMER_TRX_ID = RACT.CUSTOMER_TRX_ID
AND OC.DOCUMENT_ID = CD.DOCUMENT_ID
AND BO.BANK_OCCURRENCE_CODE = OC.BANK_OCCURRENCE_CODE
AND BO.BANK_OCCURRENCE_TYPE = OC.BANK_OCCURRENCE_TYPE
AND BO.BANK_PARTY_ID = OC.BANK_PARTY_ID
AND RACT.ORG_ID = SYS.ORG_ID UNION SELECT CD.ORG_ID ORG_ID
, CD.DOCUMENT_ID JLBR_DOCUMENT_ID
, OC.OCCURRENCE_ID JLBR_OCCURRENCE_ID
, CD.DOCUMENT_ID JLBR_DOCUMENT_NUMBER
, CD.RECEIPT_METHOD_ID JLBR_RECEIPT_METHOD_ID
, CD.BANK_ACCT_USE_ID JLBR_BANK_ACCT_USE_ID
, OC.OCCURRENCE_DATE JLBR_OCC_DATE
, RACT.TRX_NUMBER||'-'||TO_CHAR(CD.TERMS_SEQUENCE_NUMBER) JLBR_TRADE_NOTE_NUMBER
, CSC.GL_DATE JLBR_GL_DATE
, OC.BANK_OCCURRENCE_CODE JLBR_BANK_OCC_CODE
, BO.DESCRIPTION JLBR_BANK_OCC_DESC
, DECODE(OC.GL_POSTED_DATE
, NULL
, 'N'
, 'Y') JLBR_TRANSFER_TO_GL_FLAG
, OC.EVENT_ID EVENT_ID
, SYS.CODE_COMBINATION_ID_LOSS JLBR_EXCHG_LOSS_CCID
, SYS.CODE_COMBINATION_ID_GAIN JLBR_EXCHG_GAIN_CCID
FROM JL_BR_AR_COLLECTION_DOCS_ALL CD
, JL_BR_AR_OCCURRENCE_DOCS_ALL OC
, JL_BR_AR_BANK_OCCURRENCES BO
, JL_BR_AR_BORDEROS_ALL B
, JL_BR_AR_SELECT_ACCOUNTS_ALL CSC
, RA_CUSTOMER_TRX_ALL RACT
, AR_SYSTEM_PARAMETERS_ALL SYS
WHERE CD.CUSTOMER_TRX_ID = RACT.CUSTOMER_TRX_ID
AND OC.DOCUMENT_ID = CD.DOCUMENT_ID
AND OC.OCCURRENCE_STATUS = 'CANCELED'
AND BO.BANK_OCCURRENCE_CODE = OC.BANK_OCCURRENCE_CODE
AND BO.BANK_OCCURRENCE_TYPE = OC.BANK_OCCURRENCE_TYPE
AND BO.BANK_PARTY_ID = OC.BANK_PARTY_ID
AND BO.STD_OCCURRENCE_CODE = 'REMITTANCE'
AND BO.BANK_OCCURRENCE_TYPE = 'REMITTANCE_OCCURRENCE'
AND B.BORDERO_ID = CD.BORDERO_ID
AND CSC.SELECT_ACCOUNT_ID = B.SELECT_ACCOUNT_ID
AND RACT.ORG_ID = SYS.ORG_ID
AND EXISTS(SELECT 'Y'
FROM XLA_EVENTS XE
WHERE XE.EVENT_ID = OC.EVENT_ID
AND XE.PROCESS_STATUS_CODE <> 'P')