EBS : AP Payment Useful Query

Query to get Process Payment Request count having multiple Currency

select PAYMENT_PROCESS_REQUEST_NAME,COUNT(PAYMENT_CURRENCY_CODE)

from (

select PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE

from apps.iby_paymentS_all

--where PAYMENT_PROCESS_REQUEST_NAME='FAITRAD'

group by PAYMENT_PROCESS_REQUEST_NAME, PAYMENT_CURRENCY_CODE

) xx

group by  PAYMENT_PROCESS_REQUEST_NAME--, PAYMENT_CURRENCY_CODE

having count(PAYMENT_PROCESS_REQUEST_NAME) > 1

order by 1

Query to get Payment Details 

select PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,     

        BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY

from (       

Select aba.Bank_Account_Num Bank_Account_Num, aba.BANK_ACCOUNT_NAME Bank_account_name,aba.bank_account_id bank_account_id,

abb.BANK_BRANCH_NAME,

(select bank_name from ce_banks_v where PK_ID =aba.bank_id) bank_name,

(select name from hr_operating_units where organization_id = ac.org_id ) ou_name,

(select NLS_TERRITORY from fnd_territories where TERRITORY_CODE = ac.COUNTRY) payee_country,

ac.org_id,ac.PAYMENT_METHOD_CODE,  ac.CURRENCY_CODE,ac.COUNTRY,

(select ispp.SYSTEM_PROFILE_NAME from iby_acct_pmt_profiles_b ibyac,IBY_SYS_PMT_PROFILES_VL ispp

where PAYMENT_PROFILE_ID =ac.PAYMENT_PROFILE_ID

and ibyac.SYSTEM_PROFILE_CODE=ispp.SYSTEM_PROFILE_CODE) payment_process_profile,ac.check_number

From AP_CHECKS_ALL ac,

     CE_PAYMENT_DOCUMENTS acs,

     CE_BANK_ACCOUNTS aba,

     CE_BANK_BRANCHES_V abb

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 abb.BRANCH_PARTY_ID = aba.Bank_Branch_Id

And upper(ac.Status_Lookup_Code) = upper('NEGOTIABLE')

and ac.BANK_ACCOUNT_NAME ='BOA USD ALL'

and trunc(ac.creation_Date) >= '31-AUG-2014' 

) xx

group by PAYMENT_METHOD_CODE,PAYMENT_PROCESS_PROFILE, CURRENCY_CODE, BANK_NAME,Bank_Account_Num,     

        BANK_ACCOUNT_NAME,BANK_BRANCH_NAME,OU_NAME,PAYEE_COUNTRY