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
