ECC Process Manufacturing, Outside Processing Batch

select
x.*
from
(
 SELECT * FROM (  SELECT ECC_SPEC_ID, ECC_OP_SPEC_ID, JOB, STEP_ID, BATCH_NO, BATCH_STATUS, LANGUAGE,
BATCH_PLAN_START_DATE, BATCH_ACTUAL_START_DATE, BATCH_PLAN_CMPLT_DATE, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME,
FORMULA_NO, FORMULA_VERSION, RECIPE_NO, RECIPE_VERSION, ROUTING_NO, ROUTING_VERSION,  ROUTING_CLASS, OPRN_NO, ACTIVITY, BATCHSTEP_NO, PLAN_START_DATE, PLAN_CMPLT_DATE,
ACTUAL_START_DATE, RESOURCES, RESOURCE_DESCRIPTION, RESOURCE_TYPE,
RSR_ACTUAL_START_DATE, RSR_ACTUAL_CMPLT_DATE, SUPPLIER, SUPPLIER_TBL, BUYER_NAME, PURCHASE_ORDER_STATUS, PURCHASE_ORDER_NUMBER, REQUISITION_NUMBER,
LINE_NUMBER, CANCEL_FLAG, NEED_BY_DATE, PROMISE_DATE, PENDING_QUANTITY, QUANTITY_ORDERED, QUANTITY_DELIVERED, QUANTITY_CANCELLED, PO_LINE_UOM, LINE_ITEM, OSP_ITEM,
OSP_OPERATION, OSP_OPERATION_FLAG, STARTING_TODAY, COMPLETING_TODAY, STARTING_TOMORROW, COMPLETING_TOMORROW, STARTING_BY, FINISHING_BY, OPERATION_DELAY_DURATION,
OPERATION_DELAYED, STEP_STATUS, STEP_CODE, DELAYED_RECEIPTS, PO_RECEIPT_DELAY, OPERATION_START_DELAY, OPERATION_COMPLETION_DELAY, UPCOMING, UPCOMING_CODE, WIP_OPERATIONS,
WIP_OPERATIONS_CODE, START_DELAY_FLAG, START_DELAY, COMPLETION_DELAY_FLAG
  FROM ( SELECT
   TO_CHAR(gbh.batch_id || '-' || gbs.batchstep_id || '-' || crm.resources || '-' || gbh.organization_id || '-' || wip_osp.rec_key || '-OSP') ecc_spec_id,
   TO_CHAR(gbh.batch_id || '-' || gbs.batchstep_id || '-' || gbh.organization_id || '-OP') ecc_op_spec_id,
   gbs.batch_id job,
   gbs.batchstep_id step_id,
   gbh.batch_no batch_no,
   status_lkp.meaning batch_status,
   status_lkp.language LANGUAGE,
   gbh.plan_start_date batch_plan_start_date,
   gbh.actual_start_date batch_actual_start_date,
   gbh.plan_cmplt_date batch_plan_cmplt_date,
   gbh.organization_id organization_id,
   mp.organization_code organization_code,
   haout.name organization_name,
   ffm.FORMULA_NO FORMULA_NO,
   ffm.FORMULA_VERS formula_version,
   rcp.RECIPE_NO,
   rcp.RECIPE_VERSION,
   routing.routing_no "ROUTING_NO",
   routing.routing_vers "ROUTING_VERSION",
   routing.routing_class "ROUTING_CLASS",
   gmdoprn.oprn_no,
   gbsa.activity activity,
   gbs.batchstep_no batchstep_no,
   gbs.plan_start_date PLAN_START_DATE,
   gbs.plan_cmplt_date plan_cmplt_date,
   gbs.actual_start_date actual_start_date,
   crm.resources resources,
   crm.resource_desc resource_description,
   crc.resource_class_desc resource_type,
   gbsr.actual_start_date Rsr_actual_start_date,
   gbsr.actual_cmplt_date Rsr_actual_cmplt_date,
   NVL(wip_osp.vendor_name, nonelk.meaning) supplier,
   wip_osp.vendor_name supplier_tbl,
   NVL(wip_osp.buyer_name, 'None') buyer_name,
   wip_osp.approval_status_code purchase_order_status,
   wip_osp.po_number purchase_order_number,
   wip_osp.req_number requisition_number,
   wip_osp.line_number line_number,
   DECODE(wip_osp.cancel_flag, 'Y', 1, 2) cancel_flag,
   wip_osp.old_need_by_date need_by_date,
   wip_osp.old_promised_date promise_date,
   wip_osp.assembly_qty_pending pending_quantity ,
   wip_osp.quantity_ordered,
   wip_osp.quantity_delivered,
   wip_osp.quantity_cancelled,
   wip_osp.assembly_primary_uom po_line_uom ,
   wip_osp.osp_res_po_line_item line_item,
   wip_osp.osp_res_po_line_item OSP_ITEM,
   CASE
      WHEN
         wip_osp.po_number IS NULL
         AND wip_osp.req_number IS NULL
      THEN
         nolk.meaning
      ELSE
         yeslk.meaning
   END
   osp_operation,
   CASE
      WHEN
         wip_osp.po_number IS NULL
         AND wip_osp.req_number IS NULL
      THEN
         nolk.lookup_code
      ELSE
         yeslk.lookup_code
   END
   osp_operation_flag,
   CASE
      WHEN
         TRUNC(gbs.plan_start_date) = TRUNC(SYSDATE)
      THEN
         1
      ELSE
         0
   END
   starting_today,
   CASE
      WHEN
         TRUNC(gbs.plan_cmplt_date) = TRUNC(SYSDATE)
      THEN
         1
      ELSE
         0
   END
   Completing_Today,
   CASE
      WHEN
         TRUNC(gbs.plan_start_date) = TRUNC(SYSDATE + 1)
      THEN
         1
      ELSE
         0
   END
   starting_tomorrow,
   CASE
      WHEN
         TRUNC(gbs.plan_cmplt_date) = TRUNC(SYSDATE + 1)
      THEN
         1
      ELSE
         0
   END
   Completing_Tomorrow,
   (
      gbs.plan_start_date - SYSDATE
   )
   starting_by,
   (
      gbs.plan_cmplt_date - SYSDATE
   )
   finishing_by,
   CASE
      WHEN
         gbs.actual_cmplt_date IS NOT NULL
         AND gbs.actual_cmplt_date > (gbs.plan_cmplt_date )
      THEN
         CASE
            WHEN
               (gbs.actual_cmplt_date - (gbs.plan_cmplt_date )) > 0
            THEN
				(gbs.actual_cmplt_date - (gbs.plan_cmplt_date ))
            ELSE
               0
         END
         WHEN
            gbs.actual_cmplt_date IS NULL
            AND
            ( gbs.plan_cmplt_date) < sysdate
         THEN
            CASE
               WHEN
                  (sysdate - (gbs.plan_cmplt_date )) > 0
               THEN
				  (sysdate - (gbs.plan_cmplt_date ))
               ELSE
                  0
            END
            WHEN
               gbs.actual_start_date IS NOT NULL
               AND gbs.actual_start_date > (gbs.plan_start_date )
            THEN
               CASE
                  WHEN
                     (gbs.actual_start_date - (gbs.plan_start_date )) > 0
                  THEN
					 (gbs.actual_start_date - (gbs.plan_start_date ))
                  ELSE
                     0
               END
               WHEN
                  gbs.actual_start_date IS NULL
                  AND
                  (gbs.plan_start_date) < sysdate
               THEN
                  CASE
                     WHEN
                        (sysdate - (gbs.plan_start_date )) > 0
                     THEN
						(sysdate - (gbs.plan_start_date ))
                     ELSE
                        0
                  END
                  ELSE
                     0
   END
   operation_delay_duration,
   CASE
      WHEN
         gbs.actual_cmplt_date IS NOT NULL
         AND gbs.actual_cmplt_date > (gbs.plan_cmplt_date )
      THEN
         CASE
            WHEN
               (gbs.actual_cmplt_date - (gbs.plan_cmplt_date )) > 0
            THEN
               1
            ELSE
               0
         END
         WHEN
            gbs.actual_cmplt_date IS NULL
            AND
            (gbs.plan_cmplt_date) < sysdate
         THEN
            CASE
               WHEN
                  (sysdate - (gbs.plan_cmplt_date )) > 0
               THEN
                  1
               ELSE
                  0
            END
            WHEN
               gbs.actual_start_date IS NOT NULL
               AND gbs.actual_start_date > (gbs.plan_start_date )
            THEN
               CASE
                  WHEN
                     (gbs.actual_start_date - (gbs.plan_start_date )) > 0
                  THEN
                     1
                  ELSE
                     0
               END
               WHEN
                  gbs.actual_start_date IS NULL
                  AND
                  (gbs.plan_start_date) < sysdate
               THEN
                  CASE
                     WHEN
                        (sysdate - (gbs.plan_start_date )) > 0
                     THEN
                        1
                     ELSE
                        0
                  END
                  ELSE
                     0
   END
   operation_delayed ,
   step_status_lkp.meaning step_status,
   step_status_lkp.lookup_code step_code,
   delayed_receipts,
   CASE WHEN delayed_receipts =1 THEN 'YES' ELSE 'NO' END po_receipt_delay,
   	gbs.operation_start_delay,
	gbs.operation_completion_delay,
    CASE WHEN step_status_lkp.lookup_code = 1 AND status_lkp.lookup_code IN (1,2)
		THEN 1 ELSE 0 END upcoming,
	CASE WHEN step_status_lkp.lookup_code = 1 AND status_lkp.lookup_code IN (1,2)
		THEN 'YES'   ELSE 'NO'  END upcoming_code,
	CASE WHEN step_status_lkp.lookup_code = 2 AND status_lkp.lookup_code IN (1,2)
		THEN 1 ELSE 0 END WIP_operations,
	CASE WHEN step_status_lkp.lookup_code = 2 AND status_lkp.lookup_code IN (1,2)
		THEN 'YES'   ELSE 'NO'  END WIP_operations_code,
	CASE WHEN step_status_lkp.lookup_code IN (1,2) AND status_lkp.lookup_code IN (1,2) AND gbs.operation_start_delay > 0
		THEN 1   ELSE 0  END Start_delay_flag,
	CASE WHEN step_status_lkp.lookup_code IN (1,2) AND status_lkp.lookup_code IN (1,2) AND gbs.operation_start_delay > 0
		THEN 'YES'   ELSE 'NO'  END Start_delay,
	CASE WHEN step_status_lkp.lookup_code IN (2,3) AND status_lkp.lookup_code IN (1,2) AND gbs.operation_completion_delay > 0
		THEN 1  ELSE 0  END completion_delay_flag 
FROM
   wip_entities we,
   gme_batch_header gbh,
   FM_FORM_MST ffm,
   GMD_RECIPES_B rcp,
   fm_rout_hdr routing,
   cr_rsrc_mst_tl crmt,
   cr_rsrc_cls crc,
   cr_rsrc_mst_b crm,
   gme_batch_step_resources gbsr,
   gme_batch_step_activities gbsa,
   mtl_parameters mp,
   hr_all_organization_units_tl haout,
   (
      SELECT
         ml1.meaning,
         ml1.lookup_code,
         ml1.language
      FROM
         fnd_lookup_values ml1
      WHERE
         ml1.lookup_type = 'GME_BATCH_STATUS'
         AND ml1.view_application_id = 553
         AND ml1.security_group_id = 0
   )
   status_lkp,
   (
      SELECT
         f.meaning,
         f.lookup_code,
         f.language
      FROM
         fnd_lookup_values f
      WHERE
         f.lookup_type = 'GME_STEP_STATUS'
         AND f.view_application_id = 553
         AND f.security_group_id = 0
   )
   step_status_lkp,
   (
      SELECT
         wip_osp1.rec_key,
         wip_osp1.approval_status_code,
         wip_osp1.po_number,
         wip_osp1.req_number,
         wip_osp1.cancel_flag,
         wip_osp1.old_need_by_date,
         wip_osp1.old_promised_date,
         wip_osp1.CANCEL_FLAG_PL,
         wip_osp1.QUANTITY_RECEIVED,
         wip_osp1.QUANTITY,
         wip_osp1.QUANTITY_CANCELLED_PL,
         wip_osp1.CLOSED_CODE,
         wip_osp1.assembly_qty_pending,
         wip_osp1.quantity_ordered,
         wip_osp1.quantity_delivered,
         wip_osp1.quantity_cancelled,
         wip_osp1.assembly_primary_uom,
         wip_osp1.organization_id,
         wip_osp1.wip_entity_id,
         wip_osp1.operation_seq_num,
         wip_osp1.vendor_name,
		 wip_osp1.buyer_name,
         wip_osp1.po_item_number osp_res_po_line_item,
         wip_osp1.line_number,
         wip_osp1.resource_seq_num,
         CASE WHEN
			(NVL(wip_osp1.old_promised_date, wip_osp1.old_need_by_date) - SYSDATE) < 0
			AND
			(NVL(wip_osp1.old_promised_date, wip_osp1.old_need_by_date) - SYSDATE) < 0
			AND
			((wip_osp1.CANCEL_FLAG_PL IS NULL OR wip_osp1.CANCEL_FLAG_PL = 'N')
				AND(wip_osp1.QUANTITY_RECEIVED < (wip_osp1.QUANTITY - wip_osp1.QUANTITY_CANCELLED_PL))
				AND NVL(wip_osp1.CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED')
		THEN
			1
		ELSE
			0
		END
		delayed_receipts
      FROM
         (
            SELECT
               /*+ leading(we) index(we)*/
               ph.segment1 po_number,
               ph.type_lookup_code po_req_type,
               ph.authorization_status approval_status_code,
               ps.need_by_date old_need_by_date,
               ps.promised_date old_promised_date,
               ps.CANCEL_FLAG CANCEL_FLAG_PL,
               ps.QUANTITY_RECEIVED QUANTITY_RECEIVED,
               ps.QUANTITY QUANTITY,
               ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL,
               ps.CLOSED_CODE CLOSED_CODE,
               wip_osp.converttoprimarymoveqty( pl.item_id, pd.destination_organization_id,
			   greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0), 0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id),
               (
                  SELECT
                     gbsr.plan_rsrc_usage
                  FROM
                     gme_batch_step_resources gbsr
                  WHERE
                     gbsr.batch_id = pd.wip_entity_id
                     AND gbsr.organization_id = pd.destination_organization_id
                     AND gbsr.batchstep_resource_id = pd.wip_resource_seq_num
               )) assembly_qty_pending,
               pd.quantity_ordered,
               pd.quantity_delivered,
               pd.quantity_cancelled,
               pd.po_header_id || '-' || pd.po_line_id || '-' || pd.po_distribution_id rec_key,
               WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id) assembly_primary_uom,
               pd.wip_entity_id wip_entity_id,
               pd.destination_organization_id organization_id,
               pd.wip_operation_seq_num operation_seq_num,
               pd.wip_resource_seq_num resource_seq_num,
               NULL req_number,
               ps.cancel_flag cancel_flag,
               pl.line_num line_number,
               po_v.vendor_name vendor_name,
               WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id, pl.item_id ) po_item_number,
			   poag.agent_name buyer_name
            FROM
               wip_entities we,
               gme_batch_header gbh,
               po_distributions_all pd,
               po_line_locations_all ps,
               po_lines_all pl,
               po_headers_all ph,
               po_document_types_all_b pdt,
               ap_suppliers po_v,
			   po_agents_v poag
            WHERE
               pdt.document_type_code = 'PO'
               AND ph.type_lookup_code = 'STANDARD'
               AND ph.type_lookup_code = pdt.document_subtype
               AND ph.org_id = pdt.org_id
               AND pd.po_header_id = ph.po_header_id
               AND pd.po_line_id = pl.po_line_id
               AND pd.line_location_id = ps.line_location_id
               AND pd.wip_entity_id = we.wip_entity_id
               AND we.entity_type IN(9,10)
               AND we.wip_entity_id = gbh.batch_id
               AND we.organization_id = gbh.organization_id
               AND gbh.batch_status IN(1,2)
               AND ph.vendor_id = po_v.vendor_id( + )
			   AND ph.agent_id = poag.agent_id
            UNION ALL
            SELECT
               /*+ leading(we) index(we)*/
               ph.segment1 || '-' || pr.release_num,
               ph.type_lookup_code,
               pr.authorization_status,
               ps.need_by_date,
               ps.promised_date,
               ps.CANCEL_FLAG CANCLE_FLAG_PL,
               ps.QUANTITY_RECEIVED QUANTITY_RECEIVED,
               ps.QUANTITY QUANTITY,
               ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL,
               ps.CLOSED_CODE CLOSED_CODE,
               wip_osp.converttoprimarymoveqty( pl.item_id, pd.destination_organization_id,
			   greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0), 0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id),
               (
                  SELECT
                     gbsr.plan_rsrc_usage
                  FROM
                     gme_batch_step_resources gbsr
                  WHERE
                     gbsr.batch_id = pd.wip_entity_id
                     AND gbsr.organization_id = pd.destination_organization_id
                     AND gbsr.batchstep_resource_id = pd.wip_resource_seq_num
               )),
               pd.quantity_ordered,
               pd.quantity_delivered,
               pd.quantity_cancelled,
               pd.po_header_id || '-' || pd.po_line_id || '-' || pr.po_release_id || '-' || pd.po_distribution_id rec_key,
               WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id),
               pd.wip_entity_id,
               pd.destination_organization_id,
               pd.wip_operation_seq_num,
               pd.wip_resource_seq_num,
               NULL,
               ps.cancel_flag,
               pl.line_num,
               po_v.vendor_name,
               WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id, pl.item_id ),
			   poag.agent_name buyer_name
            FROM
               wip_entities we,
               gme_batch_header gbh,
               po_distributions_all pd,
               po_line_locations_all ps,
               po_lines_all pl,
               po_document_types_all_b pdt,
               po_headers_all ph,
               po_releases_all pr,
               ap_suppliers po_v,
			   po_agents_v poag
            WHERE
               pdt.document_type_code = 'RELEASE'
               AND ph.type_lookup_code = 'BLANKET'
               AND pr.release_type = pdt.document_subtype
               AND pr.org_id = pdt.org_id
               AND pr.po_release_id = ps.po_release_id
               AND pr.po_header_id = ph.po_header_id
               AND pd.po_header_id = ph.po_header_id
               AND pd.po_line_id = pl.po_line_id
               AND pd.line_location_id = ps.line_location_id
               AND pd.wip_entity_id = we.wip_entity_id
               AND we.entity_type IN (9,10)
               AND we.wip_entity_id = gbh.batch_id
               AND we.organization_id = gbh.organization_id
               AND gbh.batch_status IN (1,2)
               AND ph.vendor_id = po_v.vendor_id( + )
			   AND ph.agent_id = poag.agent_id
            UNION ALL
            SELECT
               /*+ leading(we) index(we)*/
               NULL,
               'REQUISITION',
               prh.authorization_status,
               prl.need_by_date,
               to_date(NULL),
               prl.cancel_flag CANCLE_FLAG_PL,
               0 QUANTITY_RECEIVED,
               prl.quantity QUANTITY,
               0 QUANTITY_CANCELLED_PL,
               NULL CLOSED_CODE,
               wip_osp.converttoprimarymoveqty( prl.item_id, prl.destination_organization_id, prl.quantity, prl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id),
               (
                  SELECT
                     gbsr.plan_rsrc_usage
                  FROM
                     gme_batch_step_resources gbsr
                  WHERE
                     gbsr.batch_id = prl.wip_entity_id
                     AND gbsr.organization_id = prl.destination_organization_id
                     AND gbsr.batchstep_resource_id = prl.wip_resource_seq_num
               )),
               prl.quantity quantity_ordered,
               0 quantity_delivered,
               0 quantity_cancelled,
               prl.requisition_header_id || '-' || prl.requisition_line_id rec_key,
               WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id, we.primary_item_id),
               prl.wip_entity_id,
               prl.destination_organization_id,
               prl.wip_operation_seq_num,
               prl.wip_resource_seq_num,
               prh.segment1,
               prl.cancel_flag,
               prl.line_num,
               NVL(po_v.vendor_name, prl.suggested_vendor_name),
               WIP_ECC_UTIL_PVT.get_concatenated_segments(prl.destination_organization_id, prl.item_id ),
			   NULL
            FROM
               wip_entities we,
               gme_batch_header gbh,
               po_requisition_headers_all prh,
               po_requisition_lines_all prl,
               ap_suppliers po_v
            WHERE
               NOT EXISTS
               (
                  SELECT
                     1
                  FROM
                     po_line_locations_all pll
                  WHERE
                     prl.line_location_id = pll.line_location_id
               )
               AND prl.requisition_header_id = prh.requisition_header_id
               AND prl.wip_entity_id = we.wip_entity_id
               AND we.entity_type IN (9,10)
               AND we.wip_entity_id = gbh.batch_id
               AND we.organization_id = gbh.organization_id
               AND gbh.batch_status IN (1,2)
               AND prl.vendor_id = po_v.vendor_id( + )
         )
         wip_osp1
   )
   wip_osp,
   gmd_operations_b gmdoprn,
      (
   SELECT
   gbs_steps.*,
         CASE
      WHEN
         gbs_steps.actual_start_date IS NOT NULL
         AND gbs_steps.actual_start_date > gbs_steps.plan_start_date
      THEN
         CASE
            WHEN
               (gbs_steps.actual_start_date - gbs_steps.plan_start_date) > 0
            THEN (gbs_steps.actual_start_date - gbs_steps.plan_start_date)
            ELSE
               0
         END
         WHEN
            gbs_steps.actual_start_date IS NULL
            AND
            (gbs_steps.plan_start_date) < sysdate
         THEN
            CASE
               WHEN (sysdate - gbs_steps.plan_start_date) > 0
               THEN (sysdate - gbs_steps.plan_start_date)
               ELSE
                  0
			END
            ELSE
               0
   END
   operation_start_delay ,
   CASE
      WHEN
         gbs_steps.actual_cmplt_date IS NOT NULL
         AND gbs_steps.actual_cmplt_date > (gbs_steps.plan_cmplt_date&#