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