View: JL_BR_AR_COLL_OCC_DOCS_H_V

Product:	JL - Latin America Localizations
Description:
Implementation/DBA Data:	APPS.JL_BR_AR_COLL_OCC_DOCS_H_V

View Text

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')

Columns