ECC Project Procurement, Procurement Plan

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-proc-plan
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues
Run ECC Project Procurement, Procurement Plan and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM
(
select  v.ENDECA_ID ECC_SPEC_ID,
		v.RECORD_TYPE,
		v.PROJ_ID,
    v.ENDECA_ID RECORD_IDENTIFIER,
		v.UOM_CODE,
		v.UOM,
		v.RECEIVED_QUANTITY,
		v.ORDERED_QUANTITY,
		v.REJECTED_QUANTITY,
		v.NEGOTIATED_PRICE,
		v.UNIT_PRICE,
		v.AMOUNT,
		v.OBLIGATED_AMOUNT,
		v.COMMITTED_AMOUNT,
		v.PROCUREMENT_PLAN_ID,
		v.PROCUREMENT_PLAN_LINE_ID,
		v.PLAN_START_DATE,
		v.PLAN_END_DATE,
		v.LINE_STATUS,
		v.PROJECT_NAME,
		v.PROJECT_MANAGER_NAME,
		v.PROJECT_CREATION_DATE,
		lt_tl.line_type LINE_TYPE,
		v.PART_NUMBER,
		v.ITEM_DESCRIPTION,
		v.QUANTITY,
		v.NEED_BY_DATE,
    v.NEED_BY_MONTH,
    v.NEED_BY_YEAR,
		v.BUYER_NAME,
		v.SUPPLIER_NAME,
		v.DELIVER_TO_LOCATION,
		v.TASK_NAME,
		v.AGREEMENT_ID,
		v.AGREEMENT_LINE_ID,
		v.ORDER_NUMBER,
		v.ITEM_CATEGORY,
		v.SUGGESTED_SUPPLIER_NAME,
		v.LEAD_TIME,
		v.CATALOG_TYPE,
		v.COST_CODE,
		v.ABC_CLASS,
		v.COUNTRY_OF_ORIGIN,
		v.GRADE_QUALITY,
		v.MANUFACTURER,
		v.EQUIPMENT_MODEL,
		v.DETAILED_SPECIFICATION,
		v.EXPENDITURE_TYPE,
		v.SUPPLIER_ID,
		v.PERIOD_NAME,
    v.PERIOD_NAME_PM,
		v.PLANNING_CURRENCY,
		v.PERIOD_START_DATE,
		v.PERIOD_QUANTITY,
		v.PO_STATUS,
		v.PO_STATUS_CODE,
		v.PO_SUPPLIER_NAME,
		v.PO_DESCRIPTION,
		v.PO_REVISION,
		v.CURRENT_PO_AMOUNT,
		polt_tl.line_type PO_LINE_TYPE,
		v.PO_ITEM_CATEGORY,
		v.PO_ITEM_NUMBER,
		v.PO_ITEM_DESCRIPTION,
		v.PO_QUANTITY_INVOICED,
		v.PO_NEED_BY_DATE,
		v.PO_EXPENDITURE_TYPE,
		v.PO_EXPENDITURE_ORGANIZATION PO_EXP_ORGANIZATION,
		v.PO_EXPENDITURE_ITEM_DATE,
		v.REQUISITION_NUMBER,
		v.REQ_DOCUMENT_STATUS,
		v.REQ_DOCUMENT_STATUS_CODE,
		v.REQ_BUYER_NAME,
		v.REQ_ITEM_DESCRIPTION,
		v.REQ_CATEGORY,
		v.REQ_QUANTITY,
		v.REQ_NEED_BY_DATE,
		v.REQ_EXPENDITURE_TYPE,
		v.REQ_EXPENDITURE_ORGANIZATION REQ_EXP_ORGANIZATION,
		v.REQ_EXPENDITURE_ITEM_DATE,
		v.REQUISITION_ID,
		v.PO_HEADER_ID,
		v.PO_LINE_ID,
		v.REQUISITION_LINE_ID,
		v.PO_CATEGORY_ID,
		v.LINE_TYPE_ID,
		v.ITEM_REVISION,
		v.LINE_NUMBER,
		v.SUPPLIER_PART_NUM,
		v.SUPPLIER_SITE_ID,
		v.SHOPPING_CATEGORY_ID,
		v.NEGOTIATED_BY_PREPARER_FLAG,
		/*v.REQUESTED_QUANTITY REQUESTED_QUANTITY,*/
    DECODE(v.services_line_code, 'N', v.REQUESTED_QUANTITY, decode(nvl(plan_gt.num4,0), 0, 0, v.PERIOD_QUANTITY)) REQUESTED_QUANTITY,
    v.ONCART_QUANTITY ONCART_QUANTITY,
		v.EFFECTIVE_DATE,
		v.EXPIRY_DATE,
		v.RFQ_NUMBER,
		v.MINIMUM_QUANTITY,
		v.MAXIMUM_QUANTITY,
		v.ADDITIONAL_INFORMATION,
		v.PROMISED_DATE,
		v.ORDER_BY_DATE,
		v.OVERDUE_FLAG,
		v.REJECT_FLAG,
		v.PRIORITY1,
		v.PRIORITY2,
		v.PRIORITY3,
		v.BLANKET_AGREEMENT_NUMBER,
		v.ITEM_CATEGORY_ID,
		v.REQ_ITEM_NUMBER,
		v.REQ_UOM,
		v.REQ_UNIT_PRICE,
		v.PO_UOM,
		v.PO_QUANTITY,
		v.PO_UNIT_PRICE,
		v.SCHEDULE_LINE_NUMBER,
		v.SOURCE_LINE_NUMBER,
		v.OVERDUE_QUANTITY,
		v.OVERDUE_MEANING_FLAG,
		v.REJECT_MEANING_FLAG,
		v.EXCEED_BUDGET_MEANING_FLAG,
		v.RFQ_REQUIRED_MEANING_FLAG,
		v.PROC_LINE_LAST_UPD_DATE,
		v.PROC_SUPP_LAST_UPD_DATE,
		v.REQ_LAST_UPD_DATE,
		v.PO_LINE_LAST_UPD_DATE,
		v.PLANNING_AMOUNT,
		v.FUNCTIONAL_CURRENCY_CODE,
		v.FUNCTIONAL_CURRENCY_AMOUNT,
		v.PROJECT_CURRENCY_CODE,
		v.PROJECT_CURRENCY_AMOUNT,
		v.PROC_PLAN_PERIOD_REQ_ID,
		v.PROC_PLAN_SCHEDULE_LINKS_ID,
		v.PROC_PLAN_SUPPLY_SOURCE_ID,
		v.PROJECT_NUMBER,
		v.TASK_NUMBER,
		v.PLANNED_YN,
		v.OVER_BUDGET_FLAG,
		v.REQ_TASK_NAME,
		v.REQ_TASK_NUMBER,
		v.PO_TASK_NAME,
		v.AGREEMENT_LINE_NUMBER,
		v.ORG_ID,
		v.ITEM_ID,
		v.ORGANIZATION_NAME,
		v.NEG_AUCTION_HDR_ID,
		v.NEG_TITLE,
		v.NEG_CLOSE_DATE,
		v.NEG_START_DATE,
		v.NEG_STATUS,
		v.NEG_STATUS_CODE,
		v.NEG_LINE_TYPE,
		v.NEG_LINE_NUMBER,
		v.NEG_ITEM,
		v.NEG_UOM,
		v.NEG_QUANTITY,
		v.NEG_CURRENCY,
		v.NEG_TARGET_PRICE,
		v.NEG_BEST_OFFER_PRICE,
		v.NEG_INVITED_SUPPLIERS,
		v.NEG_ACTIVE_OFFERS,
		v.AGR_AGREED_AMOUNT,
		v.EFFECTIVE_FROM,
		v.EFFECTIVE_TO,
		v.AGR_UOM,
		v.AGR_DESCRIPTION,
		v.AGR_CURRENCY,
		v.AGR_UNIT_PRICE,
		v.AGR_VENDOR_NAME,
		v.AGR_CATEGORY,
		v.AGR_ITEM,
		v.AGR_LEAD_TIME,
		v.LAT_PUB_ACTUAL_START_DATE,
		v.LAT_PUB_ACTUAL_FINISH_DATE,
		v.LAT_PUB_SCHEDULED_START_DATE,
		v.LAT_PUB_SCHEDULED_FINISH_DATE,
		v.WORKING_SCHEDULED_START_DATE,
		v.WORKING_SCHEDULED_FINISH_DATE,
		v.SHIPMENT_LATE_FLAG,
		v.ACQ_STRATEGY_OBJECTIVE1,
		v.ACQ_STRATEGY_OBJECTIVE2,
		v.PROD_SELECTION_OBJECTIVE1,
		v.PROD_SELECTION_OBJECTIVE2,
		v.INV_STRATEGY_OBJECTIVE1,
		v.INV_STRATEGY_OBJECTIVE2,
		v.PROJ_SUP_P1_CODE,
		v.PROJ_SUP_P2_CODE,
		v.LOG_STRAT_P1_CODE,
		v.LOG_STRAT_P2_CODE,
		v.AGREEMENT_STATUS,
		v.AGREEMENT_STATUS_CODE,
		v.AGREEMENT_SUPPLIER,
		v.NEG_MIN_PERIOD,
		v.ACQ_STRATEGY_OBJECTIVE1_CODE,
		v.ACQ_STRATEGY_OBJECTIVE2_CODE,
		v.PROD_SEL_OBJECTIVE1_CODE,
		v.PROD_SEL_OBJECTIVE2_CODE,
		v.INV_STRATEGY_OBJECTIVE1_CODE,
		v.INV_STRATEGY_OBJECTIVE2_CODE,
		v.PROJ_SUP_P1,
		v.PROJ_SUP_P2,
		v.LOG_STRAT_P1,
		v.LOG_STRAT_P2,
		v.LINE_STATUS_CODE,
		v.NEG_BUYER_NAME,
		v.TASK,
		v.SHIPMENT_QUALITY_RATING,
		v.SHIPMENT_PRICE_COMPLIANCE,
		v.SHIPMENT_ONTIME_FLAG,
		v.LINE_LOCATION_ID,
		v.PO_DISTRIBUTION_ID,
		v.ORDER_APPROVED_FLAG,
		v.APPROVAL_DATE_MONTH,
		v.APPROVAL_DATE_YEAR,
		v.SHIPMENT_AMOUNT,
		v.PAYMENT_CURRENCY_CODE,
		v.APPROVED_LIST_STATUS_FLAG,
		v.DEL_COUNTRY,
		v.SUPP_COUNTRY,
		v.PO_SUPPLIER_HOLD_FLAG,
		v.SUPPLIER_HOLD_FLAG,
		v.SUGGESTED_SUPPLIER_HOLD_FLAG,
	  v.INVOICED_AMOUNT,
		v.PAID_AMOUNT,
		v.REQUESTER_CHANGE_REQUESTS,
		v.SUPPLIER_CHANGE_REQUESTS,
		v.PO_PAYMENT_TERMS_ID,
		v.PO_PAYMENT_TERMS,
		v.ORDER_BY_FLAG,
	  v.ORDER_BY_FLAG_CODE,
		v.AGREEMENT_EXPIRY_FLAG,
		v.SUPP_CHANGE_REQUEST_FLAG,
		v.SUPP_CHANGE_REQUEST_FLAG_CODE,
		v.TASK_ID,
		v.TASK_MANAGER,
		v.TASK_LOCATION,
		v.TASK_STATUS,
		v.TASK_ORG,
		v.ETC_SOURCE,
		v.RECIEVE_PROJECT_INVOICE_FLAG,
		v.CAPITALIZABLE_FLAG,
		v.BILLABLE_FLAG,
		v.CHARGEABLE_FLAG,
		v.STRUCTURE_VERSION_NUMBER,
		v.ELEMENT_VERSION_ID,
		v.TASK_ELEMENT_ID,
		v.PA_SRC,
		v.ORDER_BY,
		v.SCHEDULED_START,
		v.UNASSIGNED_FLAG,
		v.UNASSIGNED_FLAG_MEANING,
		v.TASK_TYPE,
		v.WORK_TYPE,
		v.LAT_PUB_MILESTONE_FLAG,
		v.LAT_PUB_CRITICAL_FLAG,
		v.WORKING_MILESTONE_FLAG,
		v.WORKING_CRITICAL_FLAG,
		v.TASK_PROGR_STATUS,
		v.SERVICE_TYPE,
		v.TASK_PRIORITY,
		v.PROJ_CURRENCY_NAME,
		v.TOT_PLAN_COST_PC,
		v.TOT_PROC_PLAN_COST_PC,
		v.TOT_PLAN_REV_PC,
		v.TOT_MARGIN_PC,
		v.MARGIN_PERCENT_PC,
		v.PROC_PERC_REV_PC,
		v.TOT_ACT_COST_PC,
		v.TOT_ACT_PROC_COST_PC,
		v.TOT_FC_COST_PC,
		v.TOT_FC_REV_PC,
		v.FC_MARGIN_PERCENT_PC,
		v.PROJFUNC_CURRENCY_NAME,
		v.TOT_PLAN_COST_PFC,
		v.TOT_PROC_PLAN_COST_PFC,
		v.TOT_PLAN_REV_PFC,
		v.TOT_MARGIN_PFC,
		v.MARGIN_PERCENT_PFC,
		v.PROC_PERC_REV_PFC,
		v.TOT_ACT_COST_PFC,
		v.TOT_ACT_PROC_COST_PFC,
		v.TOT_FC_COST_PFC,
		v.TOT_FC_REV_PFC,
		v.FC_MARGIN_PERCENT_PFC,
		v.PER_PROC_ACT_COST_DEL_OT,
		v.PER_PROC_ACT_COST_DELAYED,
		v.TOT_PAYMENTS_PC,
		v.TOT_PAYMENTS_PFC,
		v.TOT_PAY_HOLD_PC,
		v.TOT_PAY_HOLD_PFC,
		v.PROC_PER_TOT_COST,
		v.PER_TOT_COST_RCV,
		v.PER_TOT_COST_RET,
		v.PER_TOT_COST_ORD,
		v.PER_TOT_COST_REQ,
		v.PER_TOT_COST_REM,
		v.TOT_PLAN_BURD_COST_PC,
		v.TOT_PLAN_BURD_COST_PFC,
		v.TOT_PROJ_COMMITMENTS_PC,
		v.TOT_ACT_BURD_COST_PC,
		v.TOT_ACT_MARGIN_PC,
		v.TOT_ACT_BURD_COST_VAR,
		v.TOT_ACT_PROC_PLAN_COST_VAR,
		v.TOT_ACT_MARGIN_VAR,
		v.CURRENCY_CONV_RATE_FLAG,
		v.RECEIVED_AMOUNT,
		v.PO_TASK_NUMBER,
		v.TASK_DISPLAY_SEQUENCE,
		v.SCHEDULED_START_DATE,
		v.SCHEDULED_FINISH_DATE,
		v.ACTUAL_START_DATE,
		v.ACTUAL_FINISH_DATE,
		v.PROJECT_STATUS,
		v.PROJECT_ORG,
		v.PROJECT_CURRENCY,
		v.PROJECT_FUNC_CURRENCY,
		v.PRODUCT_VARIETY_FLAG,
		v.PENDING_REQ_FLAG,
		v.REQ_DELAY_FLAG,
		v.ORD_DELAY_FLAG,
		v.ABC_CLASS_CODE,
		v.REQS_IN_POOL_FLAG,
		v.PO_CURRENCY,
		v.REQ_CURRENCY,
		v.ITEM_PURCHASE_HIS,
		v.NEXT_SHIPMENT_DATE,
		v.INVOICE_ID,
		v.INVOICE_DISTRIBUTION_ID,
		v.PO_YEAR,
		v.PO_MONTH,
		v.NEG_NUMBER,
		v.PO_PAY_SCH_LINK_ID,
		v.PO_PAY_ITEM_ID,
		v.PAYMENT_TYPE,
		v.PAY_DESCRIPTION,
		v.PAY_QUANTITY,
		v.PAY_UNIT,
		v.PAY_VALUE,
		v.PAY_PRICE,
		v.PAY_AMOUNT,
		v.PROG_NEED_BY_DATE,
		v.PO_PAY_ITEM_LINE_ID,
		v.PO_SCH_LINE_ID,
		v.PO_SCH_ID,
		v.SERVICES_LINE,
		v.SERVICES_LINE_CODE,
		v.SERVICES_TO_REQUISITION,
		v.SERVICES_TO_REQUISITION_CODE,
		v.INVOICE_TYPE,
		v.INVOICE_LINE_TYPE,
		v.ADVANCE_PAID,
		v.ADVANCE_BILLED,
		v.DOCUMENT_STYLE_ID,
		v.FINANCIALS_FLAG,
		v.PA_COST_CODE,
		v.SCHEDULES_REQUIRED_FLAG,
		v.SRC_SYS_NAME,
		v.SRC_SYS_DOC,
		v.SRC_SYS_DOC_VER,
		v.SRC_SYS_LINE_NUMBER,
		v.SRC_SYS_LINE_VER,
		v.SRC_SYS_DATE,
		v.VERSION_NUMBER,
			v.PLAN_ATTRIBUTE_CATEGORY ,
			v.PLAN_ATTRIBUTE1 ,
			v.PLAN_ATTRIBUTE2 ,
			v.PLAN_ATTRIBUTE3 ,
			v.PLAN_ATTRIBUTE4 ,
			v.PLAN_ATTRIBUTE5 ,
			v.PLAN_ATTRIBUTE6 ,
			v.PLAN_ATTRIBUTE7 ,
			v.PLAN_ATTRIBUTE8 ,
			v.PLAN_ATTRIBUTE9 ,
			v.PLAN_ATTRIBUTE10 ,
			v.PLAN_ATTRIBUTE11 ,
			v.PLAN_ATTRIBUTE12 ,
			v.PLAN_ATTRIBUTE13 ,
			v.PLAN_ATTRIBUTE14 ,
			v.PLAN_ATTRIBUTE15 ,
			v.PLAN_ATTRIBUTE16 ,
			v.PLAN_ATTRIBUTE17 ,
			v.PLAN_ATTRIBUTE18 ,
			v.PLAN_ATTRIBUTE19 ,
			v.PLAN_ATTRIBUTE20 ,
			v.SCHD_ATTRIBUTE_CATEGORY,
			v.SCHD_ATTRIBUTE1 ,
			v.SCHD_ATTRIBUTE2 ,
			v.SCHD_ATTRIBUTE3 ,
			v.SCHD_ATTRIBUTE4 ,
			v.SCHD_ATTRIBUTE5 ,
			v.SCHD_ATTRIBUTE6 ,
			v.SCHD_ATTRIBUTE7 ,
			v.SCHD_ATTRIBUTE8 ,
			v.SCHD_ATTRIBUTE9 ,
			v.SCHD_ATTRIBUTE10 ,
			v.SCHD_ATTRIBUTE11 ,
			v.SCHD_ATTRIBUTE12 ,
			v.SCHD_ATTRIBUTE13 ,
			v.SCHD_ATTRIBUTE14 ,
			v.SCHD_ATTRIBUTE15 ,
			v.SCHD_ATTRIBUTE16 ,
			v.SCHD_ATTRIBUTE17 ,
			v.SCHD_ATTRIBUTE18 ,
			v.SCHD_ATTRIBUTE19 ,
			v.SCHD_ATTRIBUTE20 ,
			v.TECH_SPEC_URL ,
			v.CONTRACT_TEMPLATE ,
			v.ACTION_TYPE ,
			v.ACTION_ASSIGNEE ,
			v.ACTION_DESCRIPTION ,
			v.ACTION_TARGET_DATE ,
			v.ACTION_COMPLETION_DATE ,
			v.ACTION_DAYS_REMAINING ,
			v.IS_ACTION_DELAYED ,
			v.ACTION_STATUS ,
			v.ACTION_COMMENTS ,
			v.ACTION_ID ,
			v.ACTION_TYPE_MEANING ,
			v.ACTION_ENTITY_TYPE ,
			v.ACTION_ENTITY_TYPE_CODE ,
			v.ACTION_ENTITY_ID ,
			v.ACTION_DUE_DATE_DIMEN ,
			v.ACTION_STATUS_MEANING,
			v.SCHEDULE_STATUS,
      v.SCHEDULE_STATUS_CODE,
      v.DESTINATION,
      v.DESTINATION_CODE,
			v.REQUESTER_NAME,
      v.QUANTITY_PENDING,
      v.ORD_LINE_SHIP_DIST,
      v.REQ_LINE_DIST,
      1 C_ONE,
	    v.LANGUAGE,
      DECODE(v.SHIPMENT_ONTIME_FLAG, 'Y', 1, 0) SHIP_ONTIME_FLAG_NUM,
			PO_PPCC_ECC_UTIL_PUB.PO_GET_ACTION_TEXT('ADD_TO_SHOP' , 20) ADD_TO_SHOP,
			PO_PPCC_ECC_UTIL_PUB.PO_GET_ACTION_TEXT('ADD_TO_DOC_BLDR' , 20) ADD_TO_DOC_BLDR,
      PRJ_GT.num1 as PROJ_PROC_ACT_COST_DEL_OT,
      PRJ_GT.num2 as PROJ_PROC_ACT_COST_DELAYED,
      PRJ_GT.num3 as PROJ_PER_TOT_COST_REQ,
      PRJ_GT.num4 as PROJ_PER_TOT_COST_ORD,
      PRJ_GT.num5 as PROJ_PER_TOT_COST_RCV,
      PRJ_GT.num6 as PROJ_PER_TOT_COST_RET,
      PRJ_GT.num7 as PROJ_PER_TOT_COST_REM,
      PRJ_GT.num8 as PROJ_TOT_PROC_PLAN_COST_PFC,
      PRJ_GT.num9 as PROJ_TOT_PROC_PLAN_COST_PC,
      PRJ_GT.num10 as PROJ_TOT_ACT_PROC_COST_PC,
      PRJ_GT.char1 as PROJ_TOT_ACT_PROC_COST_PFC,
      CASE WHEN v.services_line_code = 'Y' THEN
       decode(SUM(NVL(v.REQUESTED_QUANTITY,0)) over (partition by v.PROJ_ID,v.TASK_NUMBER,v.PERIOD_NAME,v.LINE_NUMBER),0,PERIOD_QUANTITY,0)
      ELSE   decode(sign(nvl(v.QUANTITY_PENDING,0)), -1, 0,
            (nvl(v.QUANTITY_PENDING,0)))
      END AS  QTY_TO_BE_REQUESTED,
			CASE WHEN v.services_line_code = 'Y' THEN
       decode(sign(nvl(v.PERIOD_QUANTITY, 1)- decode(v.po_header_id, null, 0, nvl(v.ordered_quantity,1))), -1, 0,
            (nvl(v.PERIOD_QUANTITY, 1)- decode(v.po_header_id, null, 0, nvl(v.ordered_quantity,1))))
      ELSE   decode(sign(nvl(v.requested_quantity, 0)-nvl(v.ordered_quantity,0)), -1, 0, (nvl(v.requested_quantity, 0)-nvl(v.ordered_quantity,0)))
      END AS  QTY_TO_BE_ORDERED,
			CASE WHEN v.services_line_code = 'Y' THEN
       SUM(DECODE(NVL(v.REQUISITION_LINE_ID,1),1,0,NVL(v.ORDERED_QUANTITY-v.RECEIVED_QUANTITY,0)))
			OVER (PARTITION BY v.PROJ_ID,v.TASK_NUMBER,v.PERIOD_NAME,v.LINE_NUMBER,SUBSTR(v.ENDECA_ID,INSTR(v.ENDECA_ID,'-',-1,2)+1,(INSTR(v.ENDECA_ID,'-',-1,1)-(INSTR(v.ENDECA_ID,'-',-1,2))-1)))
      ELSE   decode(sign(nvl(v.ordered_quantity, 0)-nvl(v.received_quantity,0)), -1, 0, (nvl(v.ordered_quantity, 0)-nvl(v.received_quantity,0)))
      END AS  QTY_TO_BE_RECEIVED,
      decode(v.line_status_code, 'PROC_STATUS_RELEASED', v.period_quantity, 0)  QTY_TO_BE_PLANNED,
      nvl(prl.currency_amount, prl.amount) REQ_LINE_AMOUNT
     ,plan_gt.num1  plan_line_amount
     ,plan_gt.num2  plan_obligated_amount
     ,plan_gt.num3  plan_committed_amount
     ,plan_gt.num4  plan_requested_quantity /*DECODE(v.services_line_code, 'N', plan_gt.num4, NVL(sum(v.requested_quantity) over (partition by v.PROJ_ID,v.LINE_NUMBER,v.SOURCE_LINE_NUMBER),0)) plan_requested_quantity*/
     ,plan_gt.num5  plan_overdue_quantity
     ,plan_gt.num6  plan_functional_amount
     ,plan_gt.num7  plan_project_amount
     ,plan_gt.num8  plan_invoiced_amount
     ,plan_gt.num9  plan_received_amount
     ,plan_gt.num10 plan_oncart_quantity /*DECODE(v.services_line_code, 'N', plan_gt.num10, (CASE WHEN (plan_gt.num10 > 0) THEN v.QUANTITY ELSE plan_gt.num10 END)) plan_oncart_quantity*/
     ,to_number(plan_gt.char1) plan_quantity_pending /*DECODE(v.services_line_code, 'N', to_number(plan_gt.char1), (CASE WHEN (to_number(plan_gt.char1) > 0) THEN v.QUANTITY ELSE to_number(plan_gt.char1) END)) plan_quantity_pending*/
     ,plan_gt.char2  plan_rejected_quantity
     ,plan_gt.char3  PLAN_ORDERED_QUANTITY
     ,plan_gt.char4  PLAN_RECEIVED_QUANTITY
     ,plan_gt.char5  PLAN_PAID_AMOUNT
     ,decode(sign((v.ordered_quantity-v.period_quantity)), -1, null, (v.ordered_quantity-v.period_quantity)) ORDER_QTY_OVER_ACT
	,decode(sign((v.obligated_amount-v.amount)), -1, NULL, (v.obligated_amount-v.amount)) ORDER_AMT_OVER_ACT
	,decode(sign((v.po_unit_price-v.unit_price)), -1, NULL, (v.po_unit_price-v.unit_price)) UNIT_PRICE_OVER_ACT
	,decode(sign((v.invoiced_amount-v.amount)), -1, NULL, (v.invoiced_amount-v.amount)) INVOICED_AMT_OVER_ACT
		from PO_PROC_PLAN_DATA_TEMP v,
   po_session_gt plan_gt,
	 po_session_gt prj_gt,
   po_requisition_lines_all prl,
       po_proc_plan_line ppl,
       po_line_types_tl polt_tl,
       po_line_types_tl lt_tl
   WHERE v.proj_id = prj_gt.index_num1
   and ppl.proc_plan_line_id = v.procurement_plan_line_id
   and polt_tl.line_type_id (+) = v.line_type_id
   and nvl(polt_tl.language, v.language) = v.language
   and lt_tl.line_type_id (+) = ppl.line_type_id
   and nvl(lt_tl.language, v.language) = v.language
   and prl.requisition_line_id (+) = v.REQUISITION_LINE_ID
   and plan_gt.key = PO_PPCC_ECC_UTIL_PUB.Get_g_proc_data_temp_key
   and plan_gt.index_char1 = 'PROC_PLAN_RECORD'
   and prj_gt.key = PO_PPCC_ECC_UTIL_PUB.Get_g_proc_data_temp_key
   and prj_gt.index_char1 = 'PO_PROJ_SUM_AMOUNTS'
   and plan_gt.index_num1 = nvl(v.procurement_plan_line_id, -99)
   and  v.LANGUAGE in ('US')
   ) PIVOT (
		MAX(LINE_STATUS) AS LINE_STATUS ,
		MAX(LINE_TYPE) AS LINE_TYPE ,
    MAX(TASK_ORG) AS TASK_ORG,
    MAX(PROJECT_ORG) PROJECT_ORG,
		MAX(PO_STATUS) AS PO_STATUS ,
		MAX(PO_LINE_TYPE) AS PO_LINE_TYPE ,
		MAX(PO_EXP_ORGANIZATION) AS PO_EXP_ORGANIZATION ,
		MAX(REQ_DOCUMENT_STATUS) AS REQ_DOCUMENT_STATUS ,
		MAX(REQ_EXP_ORGANIZATION) AS REQ_EXP_ORGANIZATION ,
		MAX(REQ_UOM) AS REQ_UOM ,
		MAX(PO_UOM) AS PO_UOM ,
		MAX(OVERDUE_MEANING_FLAG) AS OVERDUE_MEANING_FLAG ,
		MAX(REJECT_MEANING_FLAG) AS REJECT_MEANING_FLAG ,
		MAX(EXCEED_BUDGET_MEANING_FLAG) AS EXCEED_BUDGET_MEANING_FLAG ,
		MAX(RFQ_REQUIRED_MEANING_FLAG) AS RFQ_REQUIRED_MEANING_FLAG ,
		MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME ,
		MAX(NEG_STATUS) AS NEG_STATUS ,
		MAX(NEG_LINE_TYPE) AS NEG_LINE_TYPE ,
		MAX(NEG_UOM) AS NEG_UOM ,
		MAX(AGR_UOM) AS AGR_UOM ,
		MAX(ORDER_BY_FLAG) AS ORDER_BY_FLAG ,
		MAX(AGREEMENT_EXPIRY_FLAG) AS AGREEMENT_EXPIRY_FLAG ,
		MAX(SUPP_CHANGE_REQUEST_FLAG) AS SUPP_CHANGE_REQUEST_FLAG ,
		MAX(UNASSIGNED_FLAG_MEANING) AS UNASSIGNED_FLAG_MEANING ,
		MAX(SCHEDULE_STATUS) AS SCHEDULE_STATUS ,
		MAX(DESTINATION) AS DESTINATION ,
		MAX(LOG_STRAT_P1) AS LOG_STRAT_P1 ,
		MAX(LOG_STRAT_P2) AS LOG_STRAT_P2 ,
		MAX(PROJ_SUP_P1) AS PROJ_SUP_P1 ,
		MAX(PROJ_SUP_P2) AS PROJ_SUP_P2 ,
		MAX(INV_STRATEGY_OBJECTIVE1) AS INV_STRATEGY_OBJECTIVE1 ,
		MAX(INV_STRATEGY_OBJECTIVE2) AS INV_STRATEGY_OBJECTIVE2 ,
		MAX(PROD_SELECTION_OBJECTIVE1) AS PROD_SELECTION_OBJECTIVE1 ,
		MAX(PROD_SELECTION_OBJECTIVE2) AS PROD_SELECTION_OBJECTIVE2 ,
		MAX(ACQ_STRATEGY_OBJECTIVE1) AS ACQ_STRATEGY_OBJECTIVE1 ,
		MAX(ACQ_STRATEGY_OBJECTIVE2) AS ACQ_STRATEGY_OBJECTIVE2 ,
		MAX(DELIVER_TO_LOCATION) AS DELIVER_TO_LOCATION ,
		MAX(UOM) AS UOM ,
		MAX(PLANNED_YN) AS PLANNED_YN ,
		MAX(AGREEMENT_STATUS) AS AGREEMENT_STATUS ,
		MAX(PENDING_REQ_FLAG) AS PENDING_REQ_FLAG ,
		MAX(REQ_DELAY_FLAG) AS REQ_DELAY_FLAG ,
		MAX(ORD_DELAY_FLAG) AS ORD_DELAY_FLAG ,
		MAX(ITEM_PURCHASE_HIS) AS ITEM_PURCHASE_HIS ,
		MAX(PAYMENT_TYPE) AS PAYMENT_TYPE ,
		MAX(SERVICES_LINE) AS SERVICES_LINE ,
		MAX(SERVICES_TO_REQUISITION) AS SERVICES_TO_REQUISITION
   for LANGUAGE in ('US' "US")
   )
) x
where
2=2