Tech Trainings

Oracle Fusion AR Receipts- Extract for ADFdi Import

November 18, 2022 by admin

SELECT 
fabu.bu_name,
aba.name batch_name,
absa.name batch_source_name,
aba.status batch_status,
aba.BATCH_APPLIED_STATUS,
aba.CONTROL_COUNT,
aba.ACTUAL_COUNT,
aba.ACTUAL_AMOUNT,
aba.CONTROL_AMOUNT,
AICRA.CASH_RECEIPT_ID ,
arm.NAME receipt_method_name,
AICRA.CREATED_BY ,
AICRA.CREATION_DATE ,
AICRA.AMOUNT ,
AICRA.CURRENCY_CODE ,
AICRA.BATCH_ID ,
hp.party_number,
hp.party_name,
hca.account_number,
AICRA.PAY_FROM_CUSTOMER ,
AICRA.PAYMENT_SCHEDULE_ID ,
AICRA.STATUS ,
AICRA.TYPE ,
AICRA.RECEIPT_NUMBER ,
AICRA.RECEIPT_DATE ,
AICRA.GL_DATE ,
AICRA.SPECIAL_TYPE ,
AICRA.RECEIPT_METHOD_ID ,
AICRA.ANTICIPATED_CLEARING_DATE ,
AICRA.ORG_ID ,
AICRA.REMIT_BANK_ACCT_USE_ID ,
(
select cea.BANK_ACCOUNT_NAME
 from 
  ce_bank_acct_uses_all  cbaua
  , CE_BANK_ACCOUNTS CEA
where  1=1
and cbaua.BANK_ACCT_USE_ID  =AICRA.REMIT_BANK_ACCT_USE_ID
and cbaua.BANK_ACCOUNT_ID = CEA.bank_account_id
) BANK_ACCOUNT_NAME 
FROM AR_INTERIM_CASH_RECEIPTS_ALL    AICRA	
  ,hz_parties hp
  , hz_cust_accounts hca 
  , ar_batches_all aba
  , ar_receipt_methods arm
 , FUN_ALL_BUSINESS_UNITS_V fabu
 , ar_batch_sources_all absa

where 1=1
and aba.batch_id = aicra.batch_id 
and hp.party_id = hca.party_id
and hca.cust_account_id = aicra.pay_from_customer   
and arm.RECEIPT_METHOD_ID = aicra.RECEIPT_METHOD_ID
and aicra.org_id = fabu.bu_id
and absa.BATCH_SOURCE_SEQ_ID = aba.BATCH_SOURCE_SEQ_ID

Comments |0|