ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3A93869E053B46B63588994 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 Y PO POENDECAXML AR PO نموذج Endeca Application: المشتريات Source: Short Name: POENDECAXML DB package: D PO Endeca-Vorlage Application: Einkauf Source: Short Name: POENDECAXML DB package: F PO Modèle Endeca Application: Purchasing Source: Short Name: POENDECAXML DB package: US PO Endeca Template Application: Purchasing Source: Short Name: POENDECAXML DB package: ZHS PO Endeca 模板 Application: 采购管理系统 Source: Short Name: POENDECAXML DB package: BI Publisher :projectid