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