PO Endeca Template

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source:
Short Name: POENDECAXML
DB package:
SELECT PPL.PROC_PLAN_LINE_ID PROCUREMENT_PLAN_LINE_ID,
  PPL.LINE_NUMBER LINE_NUMBER,
  PPL.PARENT_LINE PARENT_LINE,
  PPL_LTV.LINE_TYPE LINE_TYPE,
  MCK.CONCATENATED_SEGMENTS ITEM_CATEGORY,
  MSI.CONCATENATED_SEGMENTS PART_NUMBER,
  NVL(PPL.ITEM_DESCRIPTION,MSI.DESCRIPTION) ITEM_DESCRIPTION,
  (SELECT meaning
FROM
fnd_lookup_values
WHERE LOOKUP_TYPE = 'DESTINATION TYPE'
AND LOOKUP_CODE = ppl.destination
AND LANGUAGE=fnd_global.CURRENT_LANGUAGE) destination,
  emp.full_name buyer_name,											
  PPL.SUGGESTED_SUPPLIER_NAME SUGGESTED_SUPPLIER_NAME,
  (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PO_PROC_STATUS'
AND  LOOKUP_CODE =  ppl.status
AND LANGUAGE=fnd_global.CURRENT_LANGUAGE
AND ENABLED_FLAG = 'Y'  AND START_DATE_ACTIVE<=SYSDATE 
AND (END_DATE_ACTIVE IS NULL
    OR END_DATE_ACTIVE   >=SYSDATE) ) LINE_STATUS,
  (SELECT pl.meaning
  FROM pa_lookups pl
  WHERE pl.lookup_type = 'PA_PSC_SUPP_SELECTION'
  AND pl.lookup_code   = NVL(ppl.priority1,
    (SELECT psco1.acq_strat_p1_code
    FROM pa_supply_chain_options psco1
    WHERE psco1.project_id = pph.project_id
    AND psco1.object_type  = 'PA_PROJECTS'
    ))
  ) sup_selection_priority1,
  (SELECT pl.meaning
  FROM pa_lookups pl
  WHERE pl.lookup_type = 'PA_PSC_SUPP_SELECTION'
  AND pl.lookup_code   = NVL(ppl.priority2,
    (SELECT psco1.acq_strat_p2_code
    FROM pa_supply_chain_options psco1
    WHERE psco1.project_id = pph.project_id
    AND psco1.object_type  = 'PA_PROJECTS'
    ))
  ) sup_selection_priority2,
  PPL.ADDITIONAL_INFORMATION,
  PPL.EXPENDITURE_TYPE EXPENDITURE_TYPE,
  ppl.planning_currency_code PLANNING_CURRENCY,
  PPL.PLANNING_RATE_UNIT AS UNIT_PRICE,
  DECODE(ppl_ltv.order_type_lookup_code, 'FIXED PRICE', PPL.PLANNING_RATE_UNIT,
  ROUND((SELECT SUM(amount)
  FROM PO_PROC_PLAN_PERIOD_REQ
  WHERE proc_plan_line_id = ppl.proc_plan_line_id
  GROUP BY PROC_PLAN_LINE_ID
  ),2)) PLANNING_AMOUNT,
    ROUND(SUM(obligated_amount ),2) AS OBLIGATED_AMOUNT,
  ROUND(sum(committed_amount), 2)    AS COMMITTED_AMOUNT,
  UNITS.UNIT_OF_MEASURE_TL UOM,
  ROUND((SELECT SUM(quantity)
  FROM PO_PROC_PLAN_PERIOD_REQ 
  WHERE proc_plan_line_id = ppl.proc_plan_line_id
  GROUP BY PROC_PLAN_LINE_ID
  ),2) quantity,
  SUM(ordered_quantity)   AS ORDERED_QUANTITY,
  SUM(REQUESTED_QUANTITY) AS REQUESTED_QUANTITY,
  SUM(received_quantity) AS RECEIVED_QUANTITY,
  SUM(rejected_Quantity) AS rejected_quantity,
  SUM(
  overdue_quantity ) overdue_quantity,
 To_Char(Min(next_shipment_date), 'DD-MON-YY') next_shipment_date,
SUM(invoiced_amount) invoiced_amount,
NULL retained_amount,
  Sum(paid_amount) paid_amount,
  Sum(received_amount)  received_amount,
PPL.src_sys_name, 
PPL.src_sys_doc, 
PPL.src_sys_doc_ver,
PPL.src_sys_line_number,
PPL.src_sys_line_ver,
CASE
    WHEN PPL.src_sys_date IS NOT NULL
    THEN to_char(PPL.src_sys_date,'YYYY-MM-DD')|| 'T00:00:00.000'
    ELSE NULL
END src_sys_date,
PPL.version_number ,
PPL.attribute_category,
PPL.attribute1,
PPL.attribute2,
PPL.attribute3,
PPL.attribute4,
PPL.attribute5,
PPL.attribute6,
PPL.attribute7,
PPL.attribute8,
PPL.attribute9,
PPL.attribute10,
PPL.attribute11,
PPL.attribute12,
PPL.attribute13,
PPL.attribute14,
PPL.attribute15,
PPL.attribute16,
PPL.attribute17,
PPL.attribute18,
PPL.attribute19,
PPL.attribute20,
PPL.tech_spec_url,
PPL.contract_template
FROM PO_PROC_PLAN_HEADER pph,
  PO_PROC_PLAN_LINE PPL,
  PO_PROC_PLAN_PERIOD_REQ PPPR,
  PA_SUPPLY_CHAIN_OPTIONS PSCO,
 (select 
    PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,Pod.expenditure_item_date,NULL,'GET_CURR_AMOUNTS',
		poh.currency_code,PPPR1.Project_currency_code,
    DECODE(POL.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', (nvl(Pod.AMOUNT_ordered,0)-nvl(pod.amount_cancelled,0)), (nvl(Pod.QUANTITY_ordered,0)-nvl(pod.quantity_cancelled,0)) * PLL.PRICE_OVERRIDE)
    , poh.rate_date,poh.rate_type,poh.rate) 
   AS OBLIGATED_AMOUNT,
    PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,Prd.expenditure_item_date,NULL,'GET_CURR_AMOUNTS',
		NVL(prl.currency_code,
    (SELECT sob.currency_code
    FROM gl_sets_of_books sob,
      financials_system_params_all 
fsp
    WHERE sob.set_of_books_id = fsp.set_of_books_id
    AND fsp.org_id            = prl.org_id
)),PPPR1.Project_currency_code,
    DECODE(PRL.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', nvl(prl.currency_amount, PRL.AMOUNT), 'RATE', nvl(prl.currency_amount, PRL.AMOUNT), ( NVL(PRL.QUANTITY,0) - NVL(PRL.QUANTITY_CANCELLED,0)) * nvl(prl.currency_unit_price, prl.unit_price))
    , prl.rate_date,prl.rate_type,prl.rate) 
     AS COMMITTED_AMOUNT,
   DECODE(PRL.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', PRL.AMOUNT, 'RATE', TO_NUMBER(NULL), NVL(PRL.QUANTITY,0) - NVL(PRL.QUANTITY_CANCELLED,0))     AS REQUESTED_QUANTITY,
  DECODE(PLL.VALUE_BASIS, 'RATE', TO_NUMBER(NULL), 'FIXED PRICE', PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', 
poh.currency_code,PPPR1.Project_currency_code, Pod.AMOUNT_ordered, poh.rate_date,poh.rate_type,poh.rate) ,nvl(Pod.QUANTITY_ordered,0)-nvl(Pod.QUANTITY_CANCELLED,0))   AS ORDERED_QUANTITY,
  DECODE(PLL.VALUE_BASIS, 'RATE', TO_NUMBER(NULL), 'FIXED PRICE', PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', 
poh.currency_code,PPPR1.Project_currency_code, Pod.AMOUNT_delivered, poh.rate_date,poh.rate_type,poh.rate), decode(Pod.QUANTITY_DELIVERED, 0, pll.quantity_received, pod.quantity_delivered)) AS RECEIVED_QUANTITY,
  DECODE(PLL.VALUE_BASIS, 'RATE', TO_NUMBER(NULL), 'FIXED PRICE', PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', 
poh.currency_code,PPPR1.Project_currency_code, PLL.AMOUNT_REJECTED, poh.rate_date,poh.rate_type,poh.rate) , PLL.QUANTITY_REJECTED) AS rejected_quantity,
    (SELECT Sum( DECODE(pll.VALUE_BASIS,'FIXED PRICE',PAY_ITEM_AMOUNT, quantity))
  FROM PO_PROC_PLAN_PERIOD_REQ
  WHERE proc_plan_line_id = PPPR1.proc_plan_line_id
  GROUP BY PROC_PLAN_LINE_ID
  ) QUANTITY,
  CASE
    WHEN (pll.need_by_date IS NOT NULL
    AND pll.need_by_date    < SYSDATE )
    THEN NVL((nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0)), 0) - NVL(pll.QUANTITY_RECEIVED, 0)
    ELSE NULL
  END overdue_quantity,
  CASE
      WHEN pll.quantity_received<pll.quantity  - nvl(pll.quantity_cancelled,0)
      AND pll.NEED_BY_DATE      < sysdate
      THEN
        NVL(pll.PROMISED_DATE,pll.NEED_BY_DATE)
      WHEN pll.quantity_received<pll.quantity  - nvl(pll.quantity_cancelled,0)
      AND pll.NEED_BY_DATE      > sysdate
      THEN NVL(pll.PROMISED_DATE,pll.NEED_BY_DATE)
      ELSE NULL
 END  next_shipment_date,
PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT 
( pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', poh.currency_code,PPPR1.Project_currency_code,
(select sum(aid.amount) from ap_invoice_distributions_all aid
 where aid.po_distribution_id = POD.PO_DISTRIBUTION_ID and nvl(aid.posted_flag, 'N') = 'Y'), 
poh.rate_date,poh.rate_type,poh.rate) invoiced_amount,
NULL retained_amount,
  PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( 
pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', poh.currency_code,PPPR1.Project_currency_code,
(select sum(app.amount) from AP_INVOICE_PAYMENTS_ALL app where app.invoice_id in
  (select aid.invoice_id from ap_invoice_distributions_all aid
    where aid.project_id > 0
    and aid.po_distribution_id = POD.PO_DISTRIBUTION_ID)
  and nvl(app.posted_flag, 'N') = 'Y'	), 
poh.rate_date,poh.rate_type,poh.rate) paid_amount,
  PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pt.task_id,PPA1.project_id,pod.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', 
poh.currency_code,PPPR1.Project_currency_code,DECODE(pol.ORDER_TYPE_LOOKUP_CODE, 'FIXED PRICE', pll.amount_received, 
decode(Pod.QUANTITY_DELIVERED, 0, pll.quantity_received, pod.quantity_delivered) * PLL.PRICE_OVERRIDE), poh.rate_date,poh.rate_type,poh.rate)   received_amount,
ppsr.proc_plan_period_req_id,
pll.value_basis
   from   PO_PROC_PLAN_SCHEDULE_LINKS PPSR,
          PO_PROC_PLAN_PERIOD_REQ PPPR1,
				  PO_REQUISITION_HEADERS_ALL prh,
				  PO_REQUISITION_LINES_ALL prl,
				  PO_REQ_DISTRIBUTIONS_ALL prd,
				  po_headers_all poh,
				  PO_LINES_ALL POL,
				  PO_LINE_LOCATIONS_ALL PLL,
				  PO_DISTRIBUTIONS_ALL POD,
                  PA_TASKS_ALL_EXPEND_V pt,
				  PA_PROJECTS_ALL PPA1
   where  ppsr.requisition_line_id      = prl.REQUISITION_LINE_ID(+)
    AND pppr1.proc_plan_period_req_id = ppsr.proc_plan_period_req_id
		AND prl.REQUISITION_HEADER_ID     = prh.REQUISITION_HEADER_ID(+)
		AND PRH.TYPE_LOOKUP_CODE(+)       = 'PURCHASE'
    AND NVL(PRH.AUTHORIZATION_STATUS, 'XXX') <> 'SYSTEM_SAVED'
		AND PRD.REQUISITION_LINE_ID(+)    = PRL.REQUISITION_LINE_ID
		AND pod.req_distribution_id(+)    = PRD.DISTRIBUTION_ID
		AND POD.line_location_id          = PLL.line_location_id(+)
		AND pll.PO_LINE_ID                = pol.PO_LINE_ID(+)
		AND pol.po_header_id              = poh.po_header_id(+)
		AND 'STANDARD'                    = poh.TYPE_LOOKUP_CODE(+)
    AND pppr1.task_id = pt.task_id(+)
	AND pt.project_id = ppa1.project_id (+)
	AND nvl(pt.expenditure_org_id , -99) = nvl(ppa1.org_id, -99)
   ) tbl, 
  PER_ALL_PEOPLE_F EMP,
  ap_suppliers pv,
  mtl_system_items_b_kfv msi,
  MTL_CATEGORIES_KFV MCK,
  MTL_CATEGORIES_VL MCV,
  MTL_UNITS_OF_MEASURE_TL UNITS,
  po_line_types_vl ppl_ltv,
  PA_TASKS_ALL_EXPEND_V PT,
  PA_PROJECTS_ALL PPA
WHERE PPH.PROC_PLAN_ID            = PPL.PROC_PLAN_ID
AND PPL.PROC_PLAN_LINE_ID         = PPpr.PROC_PLAN_LINE_ID(+)
AND PPpr.PROC_PLAN_PERIOD_REQ_ID  = tbl.PROC_PLAN_PERIOD_REQ_ID(+)
AND PSCO.OBJECT_TYPE              = 'PA_PROJECTS'
AND PSCO.PROJECT_ID               = PPH.PROJECT_ID
AND NVL(PSCO.ENABLE_SCP_FLAG,'N') = 'Y'
AND EMP.PERSON_ID(+)              = ppl.agent_id
AND TRUNC(SYSDATE) BETWEEN NVL(EMP.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(EMP.EFFECTIVE_END_DATE, SYSDATE + 1)
AND PV.VENDOR_ID(+)           = PPL.SUGGESTED_SUPPLIER_ID
AND msi.inventory_item_id(+)  = ppl.item_id
AND msi.organization_id(+)    = ppl.inventory_organization_id
AND MCK.CATEGORY_ID           = MCV.CATEGORY_ID(+)
AND MCK.CATEGORY_ID(+)        = PPL.CATEGORY_ID
AND PPL.UNIT_MEAS_LOOKUP_CODE = UNITS.UOM_CODE(+)
AND UNITS.LANGUAGE(+)         = USERENV('LANG')
AND ppl_ltv.line_type_id(+)   = ppl.line_type_id
AND pt.project_id= PPH.PROJECT_ID
AND PPpr.task_id=  pt.task_id
AND PPA.PROJECT_ID = pph.PROJECT_ID
AND PT.EXPENDITURE_ORG_ID = PPA.ORG_ID
AND pph.project_id = :projectId
AND SOURCE_FLAG='S'
GROUP BY PPL.ADDITIONAL_INFORMATION,
  PPL.PROC_PLAN_LINE_ID,
  ppl.line_number,
  UNITS.UNIT_OF_MEASURE_TL ,
  PPL.PLANNING_RATE_UNIT ,
  PPL.STATUS ,
  ppl_ltv.line_type ,
  MSI.CONCATENATED_SEGMENTS ,
  NVL(ppl.item_description,msi.description),
  emp.full_name ,
  mck.concatenated_segments ,
  PPL.SUGGESTED_SUPPLIER_NAME,
  PPL.EXPENDITURE_TYPE ,
  PPL.PLANNING_CURRENCY_CODE ,
  PPL.priority1,
  PPL.priority2,
  pph.project_id ,
  ppl.parent_line,
  ppl.Destination,
  ppl_ltv.order_type_lookup_code,
  PPL.src_sys_name, 
  PPL.src_sys_doc, 
  PPL.src_sys_doc_ver,
  PPL.src_sys_line_number,
  PPL.src_sys_line_ver,
  PPL.src_sys_date,
  PPL.version_number ,
	PPL.attribute_category,
	PPL.attribute1,
	PPL.attribute2,
	PPL.attribute3,
	PPL.attribute4,
	PPL.attribute5,
	PPL.attribute6,
	PPL.attribute7,
	PPL.attribute8,
	PPL.attribute9,
	PPL.attribute10,
	PPL.attribute11,
	PPL.attribute12,
	PPL.attribute13,
	PPL.attribute14,
	PPL.attribute15,
	PPL.attribute16,
	PPL.attribute17,
	PPL.attribute18,
	PPL.attribute19,
	PPL.attribute20,
	PPL.tech_spec_url,
	PPL.contract_template
UNION ALL
SELECT 
PPL.PROC_PLAN_LINE_ID 
PROCUREMENT_PLAN_LINE_ID,
  PPL.LINE_NUMBER LINE_NUMBER,
  PPL.PARENT_LINE PARENT_LINE,
  PPL_LTV.LINE_TYPE 
LINE_TYPE,
  MCK.CONCATENATED_SEGMENTS ITEM_CATEGORY,
  MSI.CONCATENATED_SEGMENTS PART_NUMBER,
  NVL(PPL.ITEM_DESCRIPTION,MSI.DESCRIPTION) 
ITEM_DESCRIPTION,
  (SELECT meaning
FROM
fnd_lookup_values
WHERE LOOKUP_TYPE = 'DESTINATION TYPE'
AND LOOKUP_CODE = ppl.destination
AND LANGUAGE=fnd_global.CURRENT_LANGUAGE) destination,
  emp.full_name buyer_name,
  PPL.SUGGESTED_SUPPLIER_NAME 
SUGGESTED_SUPPLIER_NAME,
  (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'PO_PROC_STATUS'
AND  
LOOKUP_CODE =  ppl.status
AND LANGUAGE=fnd_global.CURRENT_LANGUAGE
AND ENABLED_FLAG = 'Y'  AND START_DATE_ACTIVE<=SYSDATE
AND 
(END_DATE_ACTIVE IS NULL
    OR END_DATE_ACTIVE   >=SYSDATE) ) LINE_STATUS,
  (SELECT pl.meaning
  FROM 
pa_lookups pl
  WHERE pl.lookup_type = 'PA_PSC_SUPP_SELECTION'
  AND pl.lookup_code   = NVL(ppl.priority1,
(SELECT psco1.acq_strat_p1_code
    FROM pa_supply_chain_options psco1
    WHERE psco1.project_id 
= pph.project_id
    AND psco1.object_type  = 'PA_PROJECTS'
    ))
  ) sup_selection_priority1,
  (SELECT 
pl.meaning
  FROM pa_lookups pl
  WHERE pl.lookup_type = 'PA_PSC_SUPP_SELECTION'
  AND pl.lookup_code   
= NVL(ppl.priority2,
    (SELECT psco1.acq_strat_p2_code
    FROM pa_supply_chain_options psco1
WHERE psco1.project_id = pph.project_id
    AND psco1.object_type  = 'PA_PROJECTS'
    ))
  ) sup_selection_priority2,
PPL.ADDITIONAL_INFORMATION,
  PPL.EXPENDITURE_TYPE EXPENDITURE_TYPE,
  ppl.planning_currency_code PLANNING_CURRENCY,
PPL.PLANNING_RATE_UNIT AS UNIT_PRICE,
  DECODE(ppl_ltv.order_type_lookup_code, 'FIXED PRICE', PPL.PLANNING_RATE_UNIT,
ROUND((SELECT SUM(amount)
  FROM PO_PROC_PLAN_PERIOD_REQ
  WHERE proc_plan_line_id = ppl.proc_plan_line_id
GROUP BY PROC_PLAN_LINE_ID
  ),2)) PLANNING_AMOUNT,
  ROUND(min(line_amt.obligated_amount),2) AS OBLIGATED_AMOUNT,
ROUND(min(req_amts.committed_amount), 2)    AS COMMITTED_AMOUNT,
  UNITS.UNIT_OF_MEASURE_TL UOM,
  NULL 
QUANTITY,
  NULL  AS ORDERED_QUANTITY,
 NULL  AS REQUESTED_QUANTITY,
  NULL AS RECEIVED_QUANTITY,
  NULL AS rejected_quantity,
  NULL 
overdue_quantity,
NULL next_shipment_date,
min(line_amt.invoiced_amount) 
invoiced_amount,
min(line_amt.retained_amount) retained_amount,
min(line_amt.paid_amount) paid_amount,
min(line_amt.received_amount) 
received_amount,
PPL.src_sys_name, 
PPL.src_sys_doc, 
PPL.src_sys_doc_ver,
PPL.src_sys_line_number,
PPL.src_sys_line_ver,
CASE
    WHEN PPL.src_sys_date IS NOT NULL
    THEN to_char(PPL.src_sys_date,'YYYY-MM-DD')|| 'T00:00:00.000'
    ELSE NULL
END src_sys_date,
PPL.version_number,
PPL.attribute_category,
PPL.attribute1,
PPL.attribute2,
PPL.attribute3,
PPL.attribute4,
PPL.attribute5,
PPL.attribute6,
PPL.attribute7,
PPL.attribute8,
PPL.attribute9,
PPL.attribute10,
PPL.attribute11,
PPL.attribute12,
PPL.attribute13,
PPL.attribute14,
PPL.attribute15,
PPL.attribute16,
PPL.attribute17,
PPL.attribute18,
PPL.attribute19,
PPL.attribute20,
PPL.tech_spec_url,
PPL.contract_template
FROM PO_PROC_PLAN_HEADER pph,
  PO_PROC_PLAN_LINE PPL,
  PO_PROC_PLAN_PERIOD_REQ 
PPPR,
  PA_SUPPLY_CHAIN_OPTIONS PSCO,
  po_headers_all poh,
  PO_LINES_ALL POL,
  PO_LINE_LOCATIONS_ALL 
PLL,
  PER_ALL_PEOPLE_F EMP,
  ap_suppliers pv,
  mtl_system_items_b_kfv msi,
  MTL_CATEGORIES_KFV MCK,
MTL_CATEGORIES_VL MCV,
  MTL_UNITS_OF_MEASURE_TL UNITS,
  po_line_types_vl ppl_ltv,
  PA_TASKS_ALL_EXPEND_V PT,
  PA_PROJECTS_ALL PPA,
PA_PROJ_ELEMENT_VERSIONS PEV,
  (
   select
To_Char(Min( CASE
      WHEN pll2.quantity_received<pll2.quantity  
- pll2.quantity_cancelled
      AND pll2.NEED_BY_DATE      < sysdate
      THEN
        NVL(pll2.PROMISED_DATE,pll2.NEED_BY_DATE)
WHEN pll2.quantity_received<pll2.quantity  - pll2.quantity_cancelled
      AND pll2.NEED_BY_DATE      
> sysdate
      THEN NVL(pll2.PROMISED_DATE,pll2.NEED_BY_DATE)
      ELSE NULL
END ), 'DD-MON-YY') next_shipment_date,
Sum(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( pod2.task_id,ppa.project_id,Pod2.expenditure_item_date,NULL,'GET_CURR_AMOUNTS',
poh2.currency_code,ppa.Project_currency_code,NVL( 
Pod2.AMOUNT_delivered, Pod2.QUANTITY_delivereD * PLL2.PRICE_OVERRIDE), poh2.rate_date,poh2.rate_type,poh2.rate) 
)  received_amount,
SUM(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT
( pod2.task_id,ppa.project_id,Pod2.expenditure_item_date,NULL,'GET_CURR_AMOUNTS', 
poh2.currency_code,ppa.Project_currency_code,
(
select sum(aid2.amount) from ap_invoices_all aia2, ap_invoice_distributions_all 
aid2
where aia2.invoice_id = aid2.invoice_id
and aid2.po_distribution_id = pod2.po_distribution_id
 and nvl(aid2.posted_flag, 'N') = 'Y'
and 
aid2.line_type_lookup_code <> 'RETAINAGE'
),
poh2.rate_date,poh2.rate_type,poh2.rate)) as invoiced_amount,
sum(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT
( 
pod2.task_id,ppa.project_id,Pod2.expenditure_item_date,NULL,'GET_CURR_AMOUNTS', poh2.currency_code,ppa.Project_currency_code,
(
select 
sum( -1*aid2.amount) from ap_invoices_all aia2, ap_invoice_distributions_all aid2
where aia2.invoice_id 
= aid2.invoice_id
and aid2.po_distribution_id = pod2.po_distribution_id
 and nvl(aid2.posted_flag, 'N') = 'Y'
and aid2.line_type_lookup_code 
= 'RETAINAGE'
),
poh2.rate_date,poh2.rate_type,poh2.rate)) as retained_amount,
max(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT
( 
pod2.task_id,ppa.project_id,Pod2.expenditure_item_date,NULL,'GET_CURR_AMOUNTS', poh2.currency_code,ppa.Project_currency_code,
(
select 
sum(app.amount) from AP_INVOICE_PAYMENTS_ALL app
where app.invoice_id in
  (select aid.invoice_id from ap_invoice_distributions_all aid
    where aid.project_id > 0
    and aid.po_distribution_id = POD2.PO_DISTRIBUTION_ID)
  and nvl(app.posted_flag, 'N') = 'Y'	) 
,
poh2.rate_date,poh2.rate_type,poh2.rate)) 
as paid_amount,
  ROUND(SUM(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT
( 
pod2.task_id,ppa.project_id,Pod2.expenditure_item_date,NULL,'GET_CURR_AMOUNTS', poh2.currency_code,ppa.Project_currency_code,
NVL(Pod2.AMOUNT_ordered, Pod2.QUANTITY_ordered * PLL2.PRICE_OVERRIDE),
poh2.rate_date,poh2.rate_type,poh2.rate) ),2) AS OBLIGATED_AMOUNT,
pol2.po_line_id as po_line_id
   from
     pa_projects_all ppa,
     po_headers_all poh2,
     po_lines_all 
pol2,
     po_line_locations_all pll2,
     po_distributions_all pod2
   where pol2.po_line_id = pll2.po_line_id
and pol2.po_header_id = poh2.po_header_id
    and pod2.line_location_id = pll2.line_location_id
    and 
pod2.project_id = ppa.project_id
    and pll2.payment_type <> 'ADVANCE'
    group by pol2.po_line_id
) line_amt,
(
  select 
    round(max(PA_SCM_ENDECA_PVT.GET_CURRENCY_AMOUNT ( prd.task_id,ppa1.project_id,prd.EXPENDITURE_ITEM_DATE,NULL,'GET_CURR_AMOUNTS', 
NVL(prl.currency_code,
    (SELECT sob.currency_code
    FROM gl_sets_of_books sob,
      financials_system_params_all 
fsp
    WHERE sob.set_of_books_id = fsp.set_of_books_id
    AND fsp.org_id            = prl.org_id
)),ppa1.Project_currency_code,DECODE(prl.order_type_lookup_code, 'FIXED PRICE', nvl(prl.currency_amount, 
prl.amount), 'RATE', 
     nvl(prl.currency_amount, prl.amount), (prl.quantity-NVL(prl.quantity_cancelled,0)) 
* nvl(prl.currency_unit_price, prl.unit_price)), prl.rate_date,prl.rate_type,prl.rate)),2) AS committed_amount,
ppsr.proc_plan_period_req_id
from 
       po_req_distributions_all prd,
     Po_requisition_lines_all prl,
     pa_projects_all ppa1,
    po_requisition_headers_all prh,
po_proc_plan_schedule_links ppsr
  where ppsr.requisition_line_id =  prl.requisition_line_id(+)
		AND prl.REQUISITION_HEADER_ID     = prh.REQUISITION_HEADER_ID(+)
		AND PRH.TYPE_LOOKUP_CODE(+)       = 'PURCHASE'
    AND NVL(PRH.AUTHORIZATION_STATUS, 'XXX') <> 'SYSTEM_SAVED'    
and prl.requisition_line_id = prd.requisition_line_id(+)
    and prd.project_id = ppa1.project_id(+)
group by ppsr.proc_plan_period_req_id
  ) req_amts
WHERE PPH.PROC_PLAN_ID            = PPL.PROC_PLAN_ID
AND 
PPL.PROC_PLAN_LINE_ID         = PPpr.PROC_PLAN_LINE_ID(+)
AND line_amt.po_line_id(+) = ppl.po_line_id
and 
pppr.proc_plan_period_req_id = req_amts.proc_plan_period_req_id(+)
AND PSCO.OBJECT_TYPE              
= 'PA_PROJECTS'
AND PSCO.PROJECT_ID               = PPH.PROJECT_ID
AND NVL(PSCO.ENABLE_SCP_FLAG,'N') 
= 'Y'
AND ppl.po_line_id                = pol.po_line_id (+)
AND pll.PO_LINE_ID(+)             = pol.PO_LINE_ID
and 
nvl(pll.payment_type, 'XXX') <> 'ADVANCE'
AND pol.po_header_id              = poh.po_header_id(+)
AND 
'STANDARD'                    = poh.TYPE_LOOKUP_CODE(+)
AND EMP.PERSON_ID(+)              = ppl.agent_id
AND 
TRUNC(SYSDATE) BETWEEN NVL(EMP.EFFECTIVE_START_DATE, SYSDATE - 1) AND NVL(EMP.EFFECTIVE_END_DATE, SYSDATE 
+ 1)
AND PV.VENDOR_ID(+)           = PPL.SUGGESTED_SUPPLIER_ID
AND msi.inventory_item_id(+)  = ppl.item_id
AND 
msi.organization_id(+)    = ppl.inventory_organization_id
AND MCK.CATEGORY_ID           = MCV.CATEGORY_ID(+)
AND 
MCK.CATEGORY_ID(+)        = PPL.CATEGORY_ID
AND PPL.UNIT_MEAS_LOOKUP_CODE = UNITS.UOM_CODE(+)
AND UNITS.LANGUAGE(+)         
= USERENV('LANG')
AND ppl_ltv.line_type_id(+)   = ppl.line_type_id
AND pt.project_id= PPH.PROJECT_ID
AND 
pt.task_id= PEV.PROJ_ELEMENT_ID (+)
AND PPpr.task_id=  pt.task_id
AND PPA.PROJECT_ID = PPH.PROJECT_ID
AND PT.EXPENDITURE_ORG_ID = PPA.ORG_ID
AND pph.project_id = :projectId
AND 
SOURCE_FLAG='P'
AND Nvl(pll.shipment_num, PPPR.pay_item_number)= PPPR.pay_item_number
GROUP 
BY PPL.ADDITIONAL_INFORMATION,
  PPL.PROC_PLAN_LINE_ID,
  ppl.line_number,
  UNITS.UNIT_OF_MEASURE_TL 
,
  PPL.PLANNING_RATE_UNIT ,
  PPL.STATUS ,
  ppl_ltv.line_type ,
  MSI.CONCATENATED_SEGMENTS ,
  NVL(ppl.item_description,msi.description),
emp.full_name ,
  mck.concatenated_segments ,
  PPL.SUGGESTED_SUPPLIER_NAME,
  PPL.EXPENDITURE_TYPE ,
PPL.PLANNING_CURRENCY_CODE ,
  PPL.priority1,
  PPL.priority2,
  pph.project_id ,
  ppl.parent_line,
ppl.Destination,
  ppl_ltv.order_type_lookup_code,
  PPL.src_sys_name, 
PPL.src_sys_doc, 
PPL.src_sys_doc_ver,
PPL.src_sys_line_number,
PPL.src_sys_line_ver,
PPL.src_sys_date,
PPL.version_number,
PPL.attribute_category,
PPL.attribute1,
PPL.attribute2,
PPL.attribute3,
PPL.attribute4,
PPL.attribute5,
PPL.attribute6,
PPL.attribute7,
PPL.attribute8,
PPL.attribute9,
PPL.attribute10,
PPL.attribute11,
PPL.attribute12,
PPL.attribute13,
PPL.attribute14,
PPL.attribute15,
PPL.attribute16,
PPL.attribute17,
PPL.attribute18,
PPL.attribute19,
PPL.attribute20,
PPL.tech_spec_url,
PPL.contract_template
  ORDER BY line_number