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

)

)

Query for Supplier Payment Comparison of that Period(Vendor Spend Query)

 SELECT POS.SEGMENT1

       , HP.PARTY_NAME

  ,cur_period.PAYMENT_AMOUNT  cur_pay_amt

  ,prev_period.PAYMENT_AMOUNT  prev_pay_amt

  ,ROUND((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100),1)||'%' increase_decrease

FROM

        (SELECT    AIA.VENDOR_ID              VENDOR_ID

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name' 

AND gl.PERIOD_YEAR = :P_Fiscal_Year

AND gl.PERIOD_NAME = :P_Period

--AND   SYSDATE BETWEEN gl.start_date and gl.end_date

GROUP BY AIA.VENDOR_ID

        ) cur_period,

        (SELECT     AIA.VENDOR_ID     VENDOR_ID  

          ,SUM(NVL(aipa.AMOUNT,0))    PAYMENT_AMOUNT

        FROM  AP_INVOICES_ALL AIA,

         ap_invoice_payments_all       aipa,

         gl_periods                    gl

        WHERE 1=1

        AND   AIA.INVOICE_ID                = AIPA.INVOICE_ID

        AND   aipa.period_name              = gl.period_name

        AND gl.period_set_name ='XX Period Name'

AND gl.period_name = (

                      SELECT  gl.period_name

                              FROM  

                               gl_periods      gl,

                       gl_periods      gl2

                              WHERE 1=1

                      AND gl.period_set_name ='XX Period Name'

                      AND (

       ( 

           gl2.period_name = :P_Comp_Period

  AND gl2.PERIOD_YEAR = :P_Comp_Fis_Year

  AND trunc(gl2.start_date) between gl.start_date and gl.end_date

    )

  OR

  (

  ( trunc(gl2.start_date-1) between gl.start_date and gl.end_date ) 

   AND gl2.period_name = :P_Period

   AND gl2.PERIOD_YEAR = :P_Fiscal_Year

   AND :P_Comp_Period IS NULL

   AND :P_Comp_Fis_Year IS NULL

  )

  )

                      AND gl2.period_set_name ='XX Period Name'

  )   

        GROUP BY AIA.VENDOR_ID

        ) prev_period

        ,POZ_SUPPLIERS POS

        ,HZ_PARTIES     HP

WHERE POS.VENDOR_ID = cur_period.vendor_id(+)

AND   POS.VENDOR_ID  = Prev_period.vendor_id(+)

AND   POS.party_id  = hp.party_id

AND ((COALESCE(NULL, :P_Supplier) IS NULL)

       OR (HP.PARTY_NAME IN (:P_Supplier)))

and (NVL(cur_period.PAYMENT_AMOUNT ,0)<>0 OR NVl(prev_period.PAYMENT_AMOUNT ,0)<>0)

AND abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)) > = NVL(:P_Change,abs((((cur_period.PAYMENT_AMOUNT-prev_period.PAYMENT_AMOUNT)/prev_period.PAYMENT_AMOUNT)*100)))