AP Supplier Bank Account Details Queryl

 

with supplier_details as 

(

select  distinct hp.party_id,

ps.segment1 supplier_number,

hp.party_name supplier_name,

(case

                when ps.end_date_active is null 

                then 'Active'

                when ps.end_date_active is not null and trunc(ps.end_date_active)>trunc(sysdate)

                then 'Active'

                when ps.end_date_active is not null and trunc(ps.end_date_active)<=trunc(sysdate)

                then 'Inactive'

                else null

                end)supplier_status,

                pssam.vendor_site_code supplier_site_name,

                (case

                when pssam.inactive_date is null 

                then 'Active'

                when pssam.inactive_date is not null and trunc(pssam.inactive_date)>trunc(sysdate)

                then 'Active'

                when pssam.inactive_date is not null and trunc(pssam.inactive_date)<=trunc(sysdate)

                then 'Inactive'

                else null

                end)supplier_site_status,

ieba.bank_name,

ieba.bank_branch_name,

ieba.branch_number,

nvl(cibb.eft_swift_code,ieb.eft_swift_code) eft_swift_code,

ieb.bank_account_num,

ieb.iban,

(case

                when (

(ipt.end_date is not null and trunc(ipt.end_date)<=trunc(sysdate)) or 

(ieb.end_date is not null and trunc(ieb.end_date)<=trunc(sysdate))

)

                then 'Inactive'

                when (

(ipt.end_date is not null and trunc(ipt.end_date)>trunc(sysdate)) or 

(ieb.end_date is not null and trunc(ieb.end_date)>trunc(sysdate))

)

                then 'Active'

                when (ipt.end_date is null or ieb.end_date is null)

                then 'Active'

                else null

                end) bank_account_status

from poz_suppliers ps,

poz_supplier_sites_all_m pssam,

hz_parties hp,

iby_account_owners iao,

iby_ext_bank_accounts ieb,

iby_ext_bank_accounts_v ieba,

ce_index_bank_branches cibb,

iby_external_payees_all iep,

iby_pmt_instr_uses_all ipt

where ps.vendor_id = pssam.vendor_id 

and ps.party_id = hp.party_id

and hp.party_id=iao.account_owner_party_id

and iao.ext_bank_account_id=ieb.ext_bank_account_id

and ieb.ext_bank_account_id=ieba.ext_bank_account_id

and ieba.bank_party_id=cibb.bank_party_id(+)

and ieba.branch_party_id=cibb.branch_party_id(+)

and pssam.vendor_site_id=iep.supplier_site_id

and iep.ext_payee_id =ipt.ext_pmt_party_id

and ipt.instrument_id=ieb.ext_bank_account_id

and ieb.bank_account_num is not null

)

, party_details as

(

select distinct hp.party_id,

hp.party_number,

hp.party_name,

decode(hp.status,'A','Active',null) supplier_status,

            null,

            to_char(null),

nvl(ieba.bank_name,cib.bank_name) bank_name,

nvl(ieba.bank_branch_name,cibb.bank_branch_name) bank_branch_name,

ieba.branch_number,

nvl(cibb.eft_swift_code,ieb.eft_swift_code) eft_swift_code,

ieb.bank_account_num,

ieb.iban,

(case

            when ieb.end_date is null 

            then 'Active'

            when ieb.end_date is not null and trunc(ieb.end_date)>trunc(sysdate)

            then 'Active'

            when ieb.end_date is not null and trunc(ieb.end_date)<=trunc(sysdate)

            then 'Inactive'

            else null

            end) bank_account_status

from fnd_lookup_values flv,

hz_orig_sys_references hos,

hz_parties hp,

iby_account_owners iao,

iby_ext_bank_accounts ieb,

iby_ext_bank_accounts_v ieba,

ce_index_bank_branches cibb,

ce_index_banks cib

where lower(flv.lookup_type)='source'

and lower(flv.language)='us'

and lower(flv.description)='xxxxxxxx'

and lower(flv.lookup_code)=lower(hos.orig_system)

and lower(hos.owner_table_name)='hz_parties'

and hos.orig_system_reference=hp.orig_system_reference

and  hp.party_id=iao.account_owner_party_id(+)

and iao.ext_bank_account_id=ieb.ext_bank_account_id(+)

and ieb.ext_bank_account_id=ieba.ext_bank_account_id(+)

and ieba.branch_number=cibb.branch_number(+)

and cibb.bank_party_id=cib.bank_party_id(+)

and ieb.bank_account_num is not null

and not exists (select 1

                from  supplier_details

                where party_id=hp.party_id)

)

select 'Supplier1',

q1.*

from supplier_details q1

where 1=1

and 

(

(

q1.supplier_name in (:P_Supplier_Name)

or q1.supplier_number in (:P_Supplier_Number)

or q1.bank_account_num in (:P_Bank_Account_Number)

)

or 

(

(case 

when (

(('ALL') in (:P_Supplier_Name))

or (('ALL') in (:P_Supplier_Number))

or (('ALL') in (:P_Bank_Account_Number))

) 

then 1

else 0

end) =1

)

)