Wednesday, 20 February 2013

AP to Bank

Select aba.Bank_Account_Num Bank_Account_Num, 
       aba.BANK_ACCOUNT_NAME Bank_account_name,
       aba.bank_account_id bank_account_id,
       sum(ac.Amount) Doller_Amount ,
       count(1) Item_count
From AP_CHECKS_ALL ac,
     CE_PAYMENT_DOCUMENTS acs,
     CE_BANK_ACCOUNTS aba,
     CE_BANK_BRANCHES_V abb,
     ap_suppliers Pv,   
     ap_supplier_Sites_all Pvs
WHERE 1 = 1
and ac.PAYMENT_METHOD_CODE = 'CHECK'
and ac.STATUS_LOOKUP_CODE = 'NEGOTIABLE'
AND ac.PAYMENT_DOCUMENT_ID = acs.PAYMENT_DOCUMENT_ID
AND acs.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
And ac.Vendor_Id = Pv.Vendor_Id
And Pvs.Vendor_Id = Pv.Vendor_Id
And Pvs.Vendor_Site_Code = ac.Vendor_Site_Code
And abb.BRANCH_PARTY_ID = aba.Bank_Branch_Id
And upper(ac.Status_Lookup_Code) = upper('NEGOTIABLE')
And ac.Check_Date >= P_Start_Check_Date
And ac.Check_Date <= P_End_Check_Date
And ac.Check_Number >= Nvl ( P_Start_Check_Num, Check_Number )
And ac.Check_Number <= Nvl ( P_End_Check_Num, Check_Number )
AND ac.org_id = fnd_profile.VALUE ('ORG_ID')
group by aba.Bank_Account_Num,      
       aba.bank_account_id,
       aba.BANK_ACCOUNT_NAME;


==========        *******************************         -=====================
       Select aba.Bank_Account_Num Bank_Account,
       ac.Check_Number Document_Number,
       ac.Check_Date  Check_Date,
       Pv.Vendor_Name Supplier_Name,
       ac.Vendor_Site_Code Site,
       ac.Amount Amount ,
       ac.Status_Lookup_Code,
       ac.Org_Id Service_Line,
       aba.bank_account_id bank_account_id
From AP_CHECKS_ALL ac,
     CE_PAYMENT_DOCUMENTS acs,
     CE_BANK_ACCOUNTS aba,
     CE_BANK_BRANCHES_V abb,
     ap_suppliers Pv,   
     ap_supplier_Sites_all Pvs
WHERE 1 = 1
and ac.PAYMENT_METHOD_CODE = 'CHECK'
and ac.STATUS_LOOKUP_CODE = 'NEGOTIABLE'
AND ac.PAYMENT_DOCUMENT_ID = acs.PAYMENT_DOCUMENT_ID
AND acs.INTERNAL_BANK_ACCOUNT_ID = aba.bank_account_id
And ac.Vendor_Id = Pv.Vendor_Id
And Pvs.Vendor_Id = Pv.Vendor_Id
And Pvs.Vendor_Site_Code = ac.Vendor_Site_Code
And abb.BRANCH_PARTY_ID = aba.Bank_Branch_Id
And upper(ac.Status_Lookup_Code) = upper('NEGOTIABLE')
And ac.Check_Date >= P_Start_Check_Date
And ac.Check_Date <= P_End_Check_Date
And ac.Check_Number >= Nvl ( P_Start_Check_Num, Check_Number )
And ac.Check_Number <= Nvl ( P_End_Check_Num, Check_Number )
and aba.bank_account_id = v_bank_account_id
AND ac.org_id = fnd_profile.VALUE ('ORG_ID');

No comments:

Post a Comment