select distinct
------------------------------------
-- decode(c.invoice_type_lookup_code , 'PREPAYMENT' , c.attribute4 ,
nvl(c.attribute4 ,
decode(
(
select count( distinct pod.po_header_id )
from ap_invoice_distributions_all d , po_distributions_all pod , po_headers_all poh
where d.invoice_id = a.invoice_id
and d.po_distribution_id is not null
and d.po_distribution_id = pod.po_distribution_id
and pod.po_header_id = poh.po_header_id
) ,
0 , null,
1 , (
select max( poh.segment1 )
from ap_invoice_distributions_all d , po_distributions_all pod , po_headers_all poh
where d.invoice_id = a.invoice_id
and d.po_distribution_id is not null
and d.po_distribution_id = pod.po_distribution_id
and pod.po_header_id = poh.po_header_id
) ,
'Multiple POs'
) ) PO_NUMBER ,
--------------------------------------------
---Projects---
decode(
(
select count( distinct d.project_id )
from ap_invoice_distributions_all d , pa_projects_all ppa
where d.invoice_id = a.invoice_id
and d.project_id is not null
and d.project_id = ppa.project_id
) ,
0 , null,
1 , (
select max( ppa.segment1 )
from ap_invoice_distributions_all d , pa_projects_all ppa
where d.invoice_id = a.invoice_id
and d.project_id is not null
and d.project_id = ppa.project_id
) ,
'Multiple Projects'
) Project_Number ,
-----------------------------------------------
d.name OU , a.invoice_id , c.invoice_num, c.invoice_type_lookup_code , c.invoice_date,
c.invoice_currency_code , c.exchange_rate INVOICE_EXCH_RATE,
(nvl(c.base_amount , c.invoice_amount)) BASE_CCY_INVOICE_AMOUNT,
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'ACCRUAL', 'ITEM', 'IPV' , 'ERV')
) BASE_CCY_ITEM_LINE,
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'RETAINAGE')
) BASE_CCY_RETAINAGE,
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'PREPAY'
) BASE_CCY_PREPAY,
---------------
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'ACCRUAL', 'ITEM', 'IPV' , 'ERV' )
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'RETAINAGE')
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'PREPAY'
) BASE_CCY_SUBTOTAL,
-----------------
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'REC_TAX' ,'NONREC_TAX')
) BASE_CCY_TAX,
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'MISCELLANEOUS'
) BASE_CCY_MISCELLANEOUS,
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'FREIGHT'
) BASE_CCY_FREIGHT,
-----------
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'ACCRUAL', 'ITEM', 'IPV' , 'ERV' )
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'RETAINAGE')
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'PREPAY'
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code in ( 'REC_TAX' ,'NONREC_TAX')
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'MISCELLANEOUS'
) +
(select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'FREIGHT'
) BASE_CCY_TOTAL
------------
/* , (select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'IPV'
) IPV
*/
/* , (select nvl(sum(nvl(b.base_amount , b.amount)),0)
from ap_invoice_distributions_all b
where a.invoice_id = b.invoice_id
and a.org_id = b.org_id
and b.line_type_lookup_code = 'ERV'
) ERV
*/
from ap_invoice_distributions_all a, ap_invoices_all c, hr_operating_units d
where a.invoice_id = c.invoice_id
and a.org_id = c.org_id
and c.org_id = d.organization_id
and c.cancelled_date is null
--and a.line_type_lookup_code = 'ERV'
--and c.invoice_num = 'ERS-500011378-355503' -- '140480'
and
(
&p_cs_proj is null or ( &p_cs_proj is not null and
a.project_id in (
select ff.project_id from pa_projects_all ff
where ff.org_id = a.org_id
and instr(&p_cs_proj , ff.segment1) > 0
)
)
)
and
(
&p_cs_po_num is null or ( &p_cs_po_num is not null and
a.po_distribution_id in (
select hh.po_distribution_id from po_distributions_all hh, po_headers_all gg where
hh.po_header_id = gg.po_header_id
and hh.org_id = gg.org_id
and gg.org_id = a.org_id
and instr(&p_cs_po_num , gg.segment1) > 0
)
)
)
and a.org_id = nvl (&P_ORG_ID , a.org_id)
