JL_BR_AR_COLL_OCC_DOCS_H

Details

Schema: FUSION
Object owner: JL
Object type: VIEW

Columns

Name
ORG_ID JLBR_DOCUMENT_ID JLBR_OCCURRENCE_ID JLBR_DOCUMENT_NUMBER JLBR_RECEIPT_METHOD_ID JLBR_BANK_ACCT_USE_ID JLBR_OCC_DATE JLBR_TRADE_NOTE_NUMBER JLBR_GL_DATE JLBR_BANK_OCC_CODE JLBR_BANK_OCC_DESC JLBR_TRANSFER_TO_GL_FLAG EVENT_ID JLBR_EXCHG_LOSS_CCID JLBR_EXCHG_GAIN_CCID OBJECT_VERSION_NUMBER COLL_BATCH_ID BILL_TO_SITE_ID PARTY_ID INTEREST_TYPE INTEREST_RATE_AMOUNT INTEREST_PERIOD INTEREST_FORMULA GRACE_DAYS PENALTY_TYPE PENALTY_RATE_AMOUNT PAYMENT_SCHEDULE_ID

Name

ORG_ID

JLBR_DOCUMENT_ID

JLBR_OCCURRENCE_ID

JLBR_DOCUMENT_NUMBER

JLBR_RECEIPT_METHOD_ID

JLBR_BANK_ACCT_USE_ID

JLBR_OCC_DATE

JLBR_TRADE_NOTE_NUMBER

JLBR_GL_DATE

JLBR_BANK_OCC_CODE

JLBR_BANK_OCC_DESC

JLBR_TRANSFER_TO_GL_FLAG

EVENT_ID

JLBR_EXCHG_LOSS_CCID

JLBR_EXCHG_GAIN_CCID

OBJECT_VERSION_NUMBER

COLL_BATCH_ID

BILL_TO_SITE_ID

PARTY_ID

INTEREST_TYPE

INTEREST_RATE_AMOUNT

INTEREST_PERIOD

INTEREST_FORMULA

GRACE_DAYS

PENALTY_TYPE

PENALTY_RATE_AMOUNT

PAYMENT_SCHEDULE_ID

Query

SQL_Statement
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',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 ,cd.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,cd.COLL_BATCH_ID COLL_BATCH_ID, cd.BILL_TO_SITE_ID BILL_TO_SITE_ID, cd.PARTY_ID PARTY_ID, cd.INTEREST_TYPE INTEREST_TYPE ,cd.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,cd.INTEREST_PERIOD INTEREST_PERIOD , cd.INTEREST_FORMULA INTEREST_FORMULA , cd.GRACE_DAYS GRACE_DAYS , cd.PENALTY_TYPE PENALTY_TYPE , cd.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT,oc.PAYMENT_SCHEDULE_ID 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 ,cd.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,cd.COLL_BATCH_ID COLL_BATCH_ID, cd.BILL_TO_SITE_ID BILL_TO_SITE_ID, cd.PARTY_ID PARTY_ID, cd.INTEREST_TYPE INTEREST_TYPE ,cd.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,cd.INTEREST_PERIOD INTEREST_PERIOD , cd.INTEREST_FORMULA INTEREST_FORMULA , cd.GRACE_DAYS GRACE_DAYS , cd.PENALTY_TYPE PENALTY_TYPE , cd.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT,oc.PAYMENT_SCHEDULE_ID 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')

SQL_Statement

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',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 ,cd.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,cd.COLL_BATCH_ID COLL_BATCH_ID, cd.BILL_TO_SITE_ID BILL_TO_SITE_ID, cd.PARTY_ID PARTY_ID, cd.INTEREST_TYPE INTEREST_TYPE ,cd.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,cd.INTEREST_PERIOD INTEREST_PERIOD ,

cd.INTEREST_FORMULA INTEREST_FORMULA , cd.GRACE_DAYS GRACE_DAYS , cd.PENALTY_TYPE PENALTY_TYPE , cd.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT,oc.PAYMENT_SCHEDULE_ID

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 ,cd.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,cd.COLL_BATCH_ID COLL_BATCH_ID, cd.BILL_TO_SITE_ID BILL_TO_SITE_ID, cd.PARTY_ID PARTY_ID, cd.INTEREST_TYPE INTEREST_TYPE ,cd.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,cd.INTEREST_PERIOD INTEREST_PERIOD ,

cd.INTEREST_FORMULA INTEREST_FORMULA , cd.GRACE_DAYS GRACE_DAYS , cd.PENALTY_TYPE PENALTY_TYPE , cd.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT,oc.PAYMENT_SCHEDULE_ID

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