Friday 19 April 2013

11i AR to Bank Query ...........

/* Formatted on 4/19/2013 9:47:51 AM (QP5 v5.114.809.3010) */
SELECT   cba.bank_account_num bank_account_num,
         cba.bank_account_id,
         bb.bank_name bank_account_name
         ,
         acr.deposit_date deposit_date,
         hp.party_name customer_name,
         hca.account_number customer_number,
         acr.receipt_number receipt_number,
         acr.receipt_date receipt_date,
         acr.amount receipt_amount
         ,
         rec_method.name Document_type
  FROM   ar_cash_receipts_all acr,
         hz_parties hp,
         hz_cust_accounts_all hca,
         ap_bank_accounts_all cba,
         ap_bank_branches bb,
         ar_receipt_methods rec_method,
         ar_receipt_classes rc
  WHERE       1 = 1             
         AND acr.confirmed_flag = 'Y'
         AND acr.pay_from_customer = hca.cust_account_id(+)
         AND hp.party_id(+) = hca.party_id
         AND acr.remittance_bank_account_id = cba.bank_account_id(+)
         AND bb.bank_branch_id(+) = cba.bank_branch_id
         AND acr.receipt_method_id = rec_method.receipt_method_id
         AND rec_method.receipt_class_id = rc.receipt_class_id
         AND cba.Bank_Account_Id =
               NVL (:l_bank_account_id, cba.Bank_Account_Id)
         AND acr.receipt_number >= NVL (:p_start_rec_no, acr.receipt_number)
         AND acr.receipt_number <= NVL (:p_end_rec_no, acr.receipt_number)
         AND TRUNC (acr.receipt_date) >= :p_start_rec_date
         AND TRUNC (acr.receipt_date) <= :p_end_rec_date
         AND acr.org_id = fnd_profile.VALUE ('ORG_ID')

No comments:

Post a Comment