Wednesday 20 February 2013

AR to Bank

SELECT   cba.bank_account_num bank_account_num
       , cba.bank_account_id,
         bb.bank_name bank_account_name,
         SUM (acr.amount) doller_amount,
         SUM (1) item_count
    FROM ar_cash_receipts_all acr,
         hz_parties hp,
         hz_cust_accounts hca,
         ce_bank_acct_uses_all remit_bank,
         ce_bank_accounts cba,
         ce_bank_branches_v bb,
         ar_receipt_methods rec_method,
         ar_receipt_classes rc,
         iby_ext_bank_accounts_v iebav
   WHERE acr.status IN ('APP', 'REV', 'NSF', 'STOP')
     AND acr.confirmed_flag = 'Y'
     AND acr.status = 'APP'
     AND acr.pay_from_customer = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND remit_bank.bank_acct_use_id(+) = acr.remit_bank_acct_use_id
     AND remit_bank.org_id(+) = acr.org_id
     AND remit_bank.bank_account_id = cba.bank_account_id(+)
     AND bb.branch_party_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 iebav.ext_bank_account_id(+) = remit_bank.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')
GROUP BY cba.bank_account_num, cba.bank_account_id, bb.bank_name; 


==============    **********************************         ===================
SELECT cba.bank_account_num bank_account_num
     , cba.bank_account_id
     , bb.bank_name bank_account_name
                                     --,bb.bank_branch_name remit_bank_branch
       , 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
       , acr.org_id service_line
  FROM ar_cash_receipts_all acr,
       hz_parties hp,
       hz_cust_accounts hca,
       ce_bank_acct_uses_all remit_bank,
       ce_bank_accounts cba,
       ce_bank_branches_v bb,
       ar_receipt_methods rec_method,
       ar_receipt_classes rc,
       iby_ext_bank_accounts_v iebav
 WHERE acr.status IN ('APP', 'REV', 'NSF', 'STOP')
   AND acr.confirmed_flag = 'Y'
   AND acr.status = 'APP'
   AND acr.pay_from_customer = hca.cust_account_id
   AND hp.party_id = hca.party_id
   AND remit_bank.bank_acct_use_id(+) = acr.remit_bank_acct_use_id
   AND remit_bank.org_id(+) = acr.org_id
   AND remit_bank.bank_account_id = cba.bank_account_id(+)
   AND bb.branch_party_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 iebav.ext_bank_account_id(+) = remit_bank.bank_account_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