Query to get Active suppliers-Category and Sites

 SELECT 

TO_CHAR(SUP.CREATION_DATE,'DD-MON-YY HH24:MI:SS','nls_date_language=american') CREATION_DATE

,SUP.segment1 supplier_number  

,HP.party_name supplier_name

,SUP.VENDOR_TYPE_LOOKUP_CODE  Supplier_Type

,pii.ORGANIZATION_TYPE

,pvs.VENDOR_SITE_CODE site

,hp.PARTY_NUMBER Customer_Number

,sup.TAXPAYER_COUNTRY

,pvs.PAYMENT_CURRENCY_CODE

,pii.INCOME_TAX_ID Tax_Payer_ID

,sup_tax.TAX_REGIME_CODE vat_code

,pii.VAT_REGISTRATION_NUM

,pvs.INACTIVE_DATE

,pzg.TAX_REG_COUNTRY_CODE

,sup_tax.tax_registration_number 

,(SELECT zr.registration_number

   FROM ZX_REGISTRATIONS zr,

ZX_PARTY_TAX_PROFILE zptn

  WHERE 1=1

AND zr.party_tax_profile_id = zptn.party_tax_profile_id

AND SYSDATE  BETWEEN NVL(zr.effective_from,SYSDATE) AND NVL(zr.effective_to,SYSDATE+1)

AND zptn.party_id = (SELECT DISTINCT hps.party_site_id 

   FROM HZ_PARTY_SITES hps

  WHERE hps.party_id = hp.party_id

AND hps.party_site_id = PVS.party_site_id

)

and rownum<2) tax_registration_number1

, (

  SELECT zr.registration_number

   FROM ZX_REGISTRATIONS zr,

ZX_PARTY_TAX_PROFILE zptn

  WHERE 1=1

AND zr.party_tax_profile_id = zptn.party_tax_profile_id

AND SYSDATE  BETWEEN NVL(zr.effective_from,SYSDATE) AND NVL(zr.effective_to,SYSDATE+1)

AND zptn.party_id = hp.party_id

and rownum<2

  )tax_registration_number2

, (

   SELECT zpt.rep_registration_number 

   FROM ZX_PARTY_TAX_PROFILE zpt

   WHERE 1=1

AND zpt.party_id = hp.party_id

and rownum<2

  )tax_registration_number3

,TO_CHAR(sup_tax.EFFECTIVE_FROM,'DD-MON-YY HH24:MI:SS','nls_date_language=american') EFFECTIVE_FROM

,hzl.ADDRESS1

,hzl.ADDRESS2

,hzl.ADDRESS3

,hzl.ADDRESS4

,hzl.CITY

,hzl.STATE

,hzl.POSTAL_CODE

,hzl.PROVINCE

,SUP.vendor_id

,( SELECT 

   DECODE(psps.purchasing_cat_flag,'N',pbct.category_name,'Y',ect.category_name) category_name

   FROM por_browse_categories_tl pbct,

        egp_categories_tl ect,

        poz_sup_products_services psps

  WHERE psps.category_id= pbct.category_id(+)

    AND psps.category_id = ect.category_id(+)

    AND pbct.language(+)=userenv('LANG')

    AND ect.language(+)=userenv('LANG')

    AND psps.VENDOR_ID = sup.vendor_id

AND rownum<2

) category_name

FROM 

(SELECT 

 ZR.TAX_REGIME_CODE

,ZR.REGISTRATION_NUMBER tax_registration_number

,ZR.EFFECTIVE_FROM

,TAX.party_id

 FROM zx_party_tax_profile tax

     ,zx_registrations ZR

 WHERE

 1=1

AND tax.party_type_code = 'THIRD_PARTY'

AND ZR.PARTY_TAX_PROFILE_ID = TAX.PARTY_TAX_PROFILE_ID 

) sup_tax

,hz_parties HP

,poz_suppliers SUP

,POZ_SUPPLIER_SITES_ALL_M PVS

,HZ_LOCATIONS hzl

,HZ_PARTY_SITES hps

,POZ_SUPPLIERS_PII pii

,poz_supplier_registrations  pzg

WHERE 1=1 

AND sup_tax.party_id(+) = HP.party_id

AND HP.party_id = SUP.party_id

AND hzl.location_id = pvs.location_id 

AND hps.party_site_id = pvs.party_site_id

AND hps.party_id = hp.party_id

AND pii.vendor_id = sup.vendor_id

AND pzg.vendor_id(+) = sup.vendor_id

--AND SUP.segment1 IN ( '10220044','10002954')

Query to get Invoice Line level Data

 select invoice_num,

       INVOICE_DATE,

   supplier_name,

   supplier_number,

   business_unit,

   inv_currency,

   country_code,

   INVOICE_AMOUNT,

   round(conversion_rate_gbp*INVOICE_AMOUNT,2) as inv_amt_gbp,

   AMOUNT_PAID ,

   round(conversion_rate_gbp*AMOUNT_PAID,2) inv_amt_paid_gbp ,

   inv_pay_status,

   PAYMENT_DATE,

   LINE_NUMBER,

   line_type,

   line_description,

   nominal,

   nominal_desc,

   bu_code,

   bu_desc,

   line_amount ,

   tax_amount,

   invoice_key,

   Inv_posted_date,

   PO_NUMBER,

   gl_code_combination,

   SUPPLIER_SITE,

   round(line_amount*conversion_rate_gbp,2) line_amt_gbp,

   round(tax_amount*conversion_rate_gbp,2) tax_amt_gbp

FROM (  

select distinct aia.invoice_num, --

       aia.INVOICE_AMOUNT,

   aia.invoice_id invoice_key,

   TO_CHAR(ail.ACCOUNTING_DATE ,'DD-MON-YY','nls_date_language=american')as Inv_posted_date,

   (SELECT pha.SEGMENT1 

    FROM po_headers_all pha

WHERE 1=1

and pha.vendor_id = supp.vendor_id

and pha.po_header_id = aia.po_header_id

)PO_NUMBER,

(gcc.SEGMENT1||'.'||

         gcc.SEGMENT2||'.'||

         gcc.SEGMENT3||'.'||

         gcc.SEGMENT4||'.'||

         gcc.SEGMENT5||'.'||

         gcc.SEGMENT6||'.'||

         gcc.SEGMENT7||'.'||

         gcc.SEGMENT8||'.'||

         gcc.SEGMENT9||'.'||

         gcc.SEGMENT10 ) gl_code_combination,

(SELECT PSSAM.VENDOR_SITE_CODE

          FROM POZ_SUPPLIER_SITES_ALL_M PSSAM

          WHERE PSSAM.vendor_id = supp.vendor_id AND ROWNUM < 2) AS SUPPLIER_SITE,

   aia.SOURCE,

   aia.AMOUNT_PAID,

   aia.INVOICE_CURRENCY_CODE inv_currency, --

   aia.INVOICE_TYPE_LOOKUP_CODE,

   to_char(aia.INVOICE_DATE, 'dd-Mon-yy') INVOICE_DATE, -- 

   aia.TAXATION_COUNTRY country,

   ail.LINE_NUMBER,

   ail.LINE_TYPE_LOOKUP_CODE,

   (select DESCRIPTION

    from fnd_lookup_values

where 1=1

and lookup_type = 'INVOICE LINE TYPE'

and LOOKUP_CODE = ail.LINE_TYPE_LOOKUP_CODE

and ENABLED_FLAG = 'Y'

   ) line_type,

   ail.DESCRIPTION as line_description,

   ail.LINE_SOURCE,

   ail.ITEM_DESCRIPTION,

   aida.AMOUNT as line_amount,

   (SELECT TAX_AMT from zx_lines zxl WHERE zxl.TRX_ID = ail.invoice_id AND zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER AND zxl.entity_code='AP_INVOICES' AND rownum<2) as tax_amount,

   supp.vendor_name supplier_name, --

   supp.segment1 supplier_number, --

   hao.NAME business_unit, -- 

   att.NAME payment_terms,

   case 

when aia.INVOICE_CURRENCY_CODE = 'GBP'

  THEN 1

else 

  TRUNC((select conversion_rate

   from gl_daily_rates

   where 1=1

   and from_currency = aia.INVOICE_CURRENCY_CODE

   and to_currency = 'GBP'

   and trunc(conversion_date) = (SELECT MAX(conversion_date) 

                                 from gl_daily_rates 

where 1=1

and from_currency = aia.INVOICE_CURRENCY_CODE

and conversion_type = 'Spot'

and to_currency = 'GBP')

   and conversion_type = 'Spot'

   ),2)

end conversion_rate_gbp ,

        CASE 

      WHEN aia.PAYMENT_STATUS_FLAG = 'N'

         THEN 'Not Paid'

         WHEN aia.PAYMENT_STATUS_FLAG = 'P'  

THEN 'Partially Paid'

         ELSE 'Fully Paid'  

        END inv_pay_status,

to_char(ipa.PAYMENT_DATE, 'dd-Mon-yy') PAYMENT_DATE ,

gcc.segment1,

gcc.segment2 bu_code,

(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,2,

                                                 GC.segment2)   

                FROM GL_CODE_COMBINATIONS GC

                WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) bu_desc,

gcc.segment3,

gcc.segment4 nominal,

(SELECT GL_FLEXFIELDS_PKG.get_description_sql(GC.CHART_OF_ACCOUNTS_ID,4,

                                                 GC.segment4)   DESCRIPTION

                FROM GL_CODE_COMBINATIONS GC

                WHERE gc.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID ) nominal_desc,

gcc.segment5,

gcc.segment6,

gcc.segment7,

gcc.segment8,

gcc.segment9 country_code,

gcc.segment10

        /*inv_pay.amount inv_amt_paid,

        inv_pay.payment_num,

        inv_pay.period_name,

        inv_pay.accounting_date,

        inv_pay.posted_flag,

        inv_pay.creation_date */

from AP_INVOICES_ALL aia,

     AP_INVOICE_LINES_ALL ail,

zx_lines zxl,

AP_INVOICE_DISTRIBUTIONS_ALL aid,

POZ_SUPPLIERS_V supp,

AP_TERMS_TL att,

HR_ALL_ORGANIZATION_UNITS hao,

AP_INVOICE_PAYMENTS_ALL inv_pay,

ap_checks_all aca,

iby_payments_all ipa,

gl_code_combinations gcc,

AP_INVOICE_DISTRIBUTIONS_ALL aida

where 1=1

and aia.party_id = supp.party_id

and aia.org_id = hao.ORGANIZATION_ID

and aia.TERMS_ID = att.TERM_ID

and aia.invoice_id = ail.invoice_id

and aia.invoice_id = aida.invoice_id

and zxl.TRX_ID = ail.invoice_id 

and zxl.TRX_LINE_NUMBER = ail.LINE_NUMBER 

and zxl.entity_code='AP_INVOICES'

and ail.LINE_TYPE_LOOKUP_CODE = 'ITEM'

and ail.invoice_id = aid.invoice_id

and ail.line_number = aid.INVOICE_LINE_NUMBER

and aid.DIST_CODE_COMBINATION_ID = gcc.code_combination_id

and aida.DIST_CODE_COMBINATION_ID = gcc.code_combination_id

and aia.invoice_id = aid.invoice_id

and att.LANGUAGE = 'US'

and aia.PAYMENT_STATUS_FLAG != 'N'

--and aia.PAYMENT_STATUS_FLAG = 'P'  --

and inv_pay.invoice_id = aia.invoice_id

and aida.FINAL_MATCH_FLAG = 'N'

and aca.check_id = inv_pay.check_id

and aca.STATUS_LOOKUP_CODE = 'CLEARED'  --  temp to be tested

and ipa.payment_id = aca.payment_id

and ail.ACCOUNTING_DATE  between :p_start_date and :p_end_date

----and gcc.segment2=1340

--and gcc.segment2=1274

--and aida.AMOUNT = 120

--and aia.invoice_num='50462'

)  

order by invoice_num, LINE_NUMBER