AR_BR_FUNDS_CAPTURE_ORDERS

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
CALL_APP_SERVICE_REQ_CODE BATCH_DATE SETTLEMENT_DATE SETTLEMENT_DUE_DATE BR_MATURITY_DATE ORDER_DESCRIPTION NAME SETTLEMENT_CURRENCY_CODE SETTLEMENT_AMOUNT PAYMENT_TRXN_EXTENSION_ID INTERNAL_BANK_ACCOUNT_ID PAYER_PARTY_ID CUST_ACCOUNT_ID ACCT_SITE_USE_ID DRAWEE_SITE_USE_ID ORG_ID ORG_TYPE LEGAL_ENTITY_ID BILL_TO_ADDRESS_ID SETTLEMENT_CUSTOMER_REFERENCE BR_DRAWEE_ISSUED_FLAG BR_SIGNED_FLAG BR_DISPUTED_FLAG ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER RECEIPT_METHOD_ID STRUCTURED_PAYMENT_REFERENCE

Name

CALL_APP_SERVICE_REQ_CODE

BATCH_DATE

SETTLEMENT_DATE

SETTLEMENT_DUE_DATE

BR_MATURITY_DATE

ORDER_DESCRIPTION

NAME

SETTLEMENT_CURRENCY_CODE

SETTLEMENT_AMOUNT

PAYMENT_TRXN_EXTENSION_ID

INTERNAL_BANK_ACCOUNT_ID

PAYER_PARTY_ID

CUST_ACCOUNT_ID

ACCT_SITE_USE_ID

DRAWEE_SITE_USE_ID

ORG_ID

ORG_TYPE

LEGAL_ENTITY_ID

BILL_TO_ADDRESS_ID

SETTLEMENT_CUSTOMER_REFERENCE

BR_DRAWEE_ISSUED_FLAG

BR_SIGNED_FLAG

BR_DISPUTED_FLAG

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

RECEIPT_METHOD_ID

STRUCTURED_PAYMENT_REFERENCE

Query

SQL_Statement
SELECT 'ARBR_' \|\| TRX.REMITTANCE_BATCH_ID CALL_APP_SERVICE_REQ_CODE , bat.batch_date, TRX.trx_date SETTLEMENT_DATE, PS.DUE_DATE SETTLEMENT_DUE_DATE, TRX.term_due_date BR_MATURITY_DATE, null ORDER_DESCRIPTION, CTT.NAME, PS.INVOICE_CURRENCY_CODE SETTLEMENT_CURRENCY_CODE, TRX.BR_AMOUNT SETTLEMENT_AMOUNT, TRX.PAYMENT_TRXN_EXTENSION_ID, bau.bank_account_id INTERNAL_BANK_ACCOUNT_ID, PARTY.PARTY_ID PAYER_PARTY_ID, CUST_ACCT.cust_account_id, SU.site_use_id ACCT_SITE_USE_ID, TRX.DRAWEE_SITE_USE_ID, TRX.ORG_Id, 'OPERATING_UNIT' ORG_TYPE , TRX.LEGAL_ENTITY_ID, party_site.location_id BILL_TO_ADDRESS_ID, TRX.CUSTOMER_REFERENCE SETTLEMENT_CUSTOMER_REFERENCE, CTT.DRAWEE_ISSUED_FLAG BR_DRAWEE_ISSUED_FLAG, CTT.SIGNED_FLAG BR_SIGNED_FLAG, decode(trh.status, 'PROTESTED', 'Y', 'N') BR_DISPUTED_FLAG , TRX.ATTRIBUTE_CATEGORY, TRX.ATTRIBUTE1, TRX.ATTRIBUTE2, TRX.ATTRIBUTE3, TRX.ATTRIBUTE4, TRX.ATTRIBUTE5, TRX.ATTRIBUTE6, TRX.ATTRIBUTE7, TRX.ATTRIBUTE8, TRX.ATTRIBUTE9, TRX.ATTRIBUTE10, TRX.ATTRIBUTE11, TRX.ATTRIBUTE12, TRX.ATTRIBUTE13, TRX.ATTRIBUTE14, TRX.ATTRIBUTE15, TRX.CREATED_BY, TRX.CREATION_DATE, TRX.LAST_UPDATED_BY, TRX.LAST_UPDATE_DATE, TRX.LAST_UPDATE_LOGIN, 1 OBJECT_VERSION_NUMBER , BAT.RECEIPT_METHOD_ID, NULL STRUCTURED_PAYMENT_REFERENCE FROM AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, RA_CUST_TRX_TYPES_ALL CTT, RA_CUSTOMER_TRX TRX, ar_transaction_history trh, ar_batches bat, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_SITE_USES_ALL SU, ce_bank_acct_uses_ou_v bau WHERE ps.customer_trx_id = TRX.customer_trx_id AND TRX.BR_AMOUNT is Not NULL and nvl(trh.current_record_flag, 'Y') = 'Y' and ps.customer_trx_id = trh.customer_trx_id and bat.batch_id = trx.remittance_batch_id AND TRX.drawee_id = CUST_ACCT.cust_account_id AND CUST_ACCT.party_id = PARTY.party_id AND PS.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id and ctt.type = 'BR' and TRX.drawee_site_use_id = su.site_use_id and su.cust_acct_site_id = acct_site.cust_acct_site_id and acct_site.party_site_id = party_site.party_site_id and loc.location_id = party_site.location_id and cust_acct.cust_account_id = acct_site.cust_account_id and bau.bank_acct_use_id = trx.remit_bank_acct_use_id

SQL_Statement

SELECT 'ARBR_' || TRX.REMITTANCE_BATCH_ID CALL_APP_SERVICE_REQ_CODE ,

bat.batch_date,

TRX.trx_date SETTLEMENT_DATE,

PS.DUE_DATE SETTLEMENT_DUE_DATE,

TRX.term_due_date BR_MATURITY_DATE,

null ORDER_DESCRIPTION,

CTT.NAME,

PS.INVOICE_CURRENCY_CODE SETTLEMENT_CURRENCY_CODE,

TRX.BR_AMOUNT SETTLEMENT_AMOUNT,

TRX.PAYMENT_TRXN_EXTENSION_ID,

bau.bank_account_id INTERNAL_BANK_ACCOUNT_ID,

PARTY.PARTY_ID PAYER_PARTY_ID,

CUST_ACCT.cust_account_id,

SU.site_use_id ACCT_SITE_USE_ID,

TRX.DRAWEE_SITE_USE_ID,

TRX.ORG_Id,

'OPERATING_UNIT' ORG_TYPE ,

TRX.LEGAL_ENTITY_ID,

party_site.location_id BILL_TO_ADDRESS_ID,

TRX.CUSTOMER_REFERENCE SETTLEMENT_CUSTOMER_REFERENCE,

CTT.DRAWEE_ISSUED_FLAG BR_DRAWEE_ISSUED_FLAG,

CTT.SIGNED_FLAG BR_SIGNED_FLAG,

decode(trh.status, 'PROTESTED', 'Y', 'N') BR_DISPUTED_FLAG ,

TRX.ATTRIBUTE_CATEGORY,

TRX.ATTRIBUTE1,

TRX.ATTRIBUTE2,

TRX.ATTRIBUTE3,

TRX.ATTRIBUTE4,

TRX.ATTRIBUTE5,

TRX.ATTRIBUTE6,

TRX.ATTRIBUTE7,

TRX.ATTRIBUTE8,

TRX.ATTRIBUTE9,

TRX.ATTRIBUTE10,

TRX.ATTRIBUTE11,

TRX.ATTRIBUTE12,

TRX.ATTRIBUTE13,

TRX.ATTRIBUTE14,

TRX.ATTRIBUTE15,

TRX.CREATED_BY,

TRX.CREATION_DATE,

TRX.LAST_UPDATED_BY,

TRX.LAST_UPDATE_DATE,

TRX.LAST_UPDATE_LOGIN,

1 OBJECT_VERSION_NUMBER ,

BAT.RECEIPT_METHOD_ID,

NULL STRUCTURED_PAYMENT_REFERENCE

FROM AR_PAYMENT_SCHEDULES PS,

HZ_CUST_ACCOUNTS CUST_ACCT,

HZ_PARTIES PARTY,

RA_CUST_TRX_TYPES_ALL CTT,

RA_CUSTOMER_TRX TRX,

ar_transaction_history trh,

ar_batches bat,

HZ_CUST_ACCT_SITES_ALL ACCT_SITE,

HZ_PARTY_SITES PARTY_SITE,

HZ_LOCATIONS LOC,

HZ_CUST_SITE_USES_ALL SU,

ce_bank_acct_uses_ou_v bau

WHERE ps.customer_trx_id = TRX.customer_trx_id

AND TRX.BR_AMOUNT is Not NULL

and nvl(trh.current_record_flag, 'Y') = 'Y'

and ps.customer_trx_id = trh.customer_trx_id

and bat.batch_id = trx.remittance_batch_id

AND TRX.drawee_id = CUST_ACCT.cust_account_id

AND CUST_ACCT.party_id = PARTY.party_id

AND PS.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id

and ctt.type = 'BR'

and TRX.drawee_site_use_id = su.site_use_id

and su.cust_acct_site_id = acct_site.cust_acct_site_id

and acct_site.party_site_id = party_site.party_site_id

and loc.location_id = party_site.location_id

and cust_acct.cust_account_id = acct_site.cust_account_id

and bau.bank_acct_use_id = trx.remit_bank_acct_use_id