ECC Process Manufacturing, Outside Processing Batch

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Ecc Op Spec Id, Job, Step Id, Batch No, Batch Plan Start Date, Batch Actual Start Date, Batch Plan Cmplt Date, Organization Id, Organization Code ...
Imported from Enterprise Command Center
Description: This data set holds information about process manufacturing outside processing operations
Dataset Key: gmo-ospdetails
Query Procedure: GMO_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: gmo_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
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)
      THEN
         CASE
            WHEN
               (gbs_steps.actual_cmplt_date - gbs_steps.plan_cmplt_date) > 0
            THEN
				(gbs_steps.actual_cmplt_date - gbs_steps.plan_cmplt_date)
            ELSE
               0
         END
         WHEN
            gbs_steps.actual_cmplt_date IS NULL AND (gbs_steps.plan_cmplt_date) < sysdate
         THEN
            CASE
               WHEN
                (sysdate - gbs_steps.plan_cmplt_date) > 0
               THEN
				(sysdate - gbs_steps.plan_cmplt_date)
               ELSE
                  0
            END
            ELSE
               0
   END
   operation_completion_delay
   FROM
   gme_batch_steps gbs_steps
   ) gbs,
 
   (
      SELECT
         meaning,
         lookup_code,
         language
      FROM
         fnd_lookup_values nonelk
      WHERE
         lookup_type = 'WIP_PO_NOT_CREATED'
         AND view_application_id = 700
         AND lookup_code = '1'
         AND security_group_id = 0
   )
   nonelk,
   (
      SELECT
         meaning,
         lookup_code,
         language
      FROM
         fnd_lookup_values nolk
      WHERE
         lookup_type = 'GME_YES_NO'
         AND view_application_id = 553
         AND lookup_code = '0'
         AND security_group_id = 0
   )
   nolk,
   (
      SELECT
         meaning,
         lookup_code,
         language
      FROM
         fnd_lookup_values yeslk
      WHERE
         lookup_type = 'GME_YES_NO'
         AND view_application_id = 553
         AND lookup_code = '1'
         AND security_group_id = 0
   )
   yeslk
WHERE
   we.wip_entity_id = gbh.batch_id
   AND gbh.batch_id = gbs.batch_id
   AND mp.organization_id = gbh.organization_id
   AND haout.organization_id = mp.organization_id
   AND gbs.oprn_id = gmdoprn.oprn_id
   AND gbs.batchstep_id = gbsr.batchstep_id
   AND gbs.step_status = step_status_lkp.lookup_code
   AND gbh.formula_id = ffm.formula_id
   AND gbh.formula_id = rcp.formula_id
   AND gbh.routing_id = routing.routing_id
   AND gbsr.batchstep_activity_id = gbsa.batchstep_activity_id
   AND crm.resources = gbsr.resources
   AND crm.resources = crmt.resources
   AND crm.resource_class = crc.resource_class( + )
   AND gbh.batch_status = status_lkp.lookup_code( + )
   AND gbsr.organization_id = wip_osp.organization_id( + )
   AND gbsr.batch_id = wip_osp.wip_entity_id( + )
   AND gbsr.batchstep_resource_id = wip_osp.resource_seq_num( + )
   AND haout.language = crmt.language
   AND haout.language = status_lkp.language
   AND haout.language = nonelk.language
   AND haout.language = yeslk.language
   AND haout.language = nolk.language
   AND haout.language = step_status_lkp.language
	AND EXISTS (SELECT /*+ push_subq no_unnest */
        1
        FROM gme_batch_step_resources gbsrosp, cr_rsrc_dtl crdosp, cr_rsrc_mst crmosp
        WHERE gbsrosp.resources       = crdosp.resources
        AND   gbsrosp.organization_id = crdosp.organization_id
        AND   crdosp.resources        = crmosp.resources
        AND   gbsrosp.batchstep_id =    gbs.batchstep_id
        AND   crdosp.purchase_item_id IS NOT NULL
        AND   NVL(crmosp.outside_process_ind,0) = 1 )
)WHERE language in ('US') ) PIVOT ( MAX(RESOURCE_TYPE) AS RESOURCE_TYPE,MAX(OSP_OPERATION) AS OSP_OPERATION,
		                                               MAX(BATCH_STATUS) AS BATCH_STATUS ,
													   MAX(SUPPLIER) AS SUPPLIER,
                                                       MAX(BUYER_NAME) AS BUYER_NAME,
													   MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME ,MAX(STEP_STATUS) AS STEP_STATUS
 
FOR LANGUAGE IN ('US' "US")) 
) x
where
2=2
Parameter Name SQL text Validation
Organization Code
x.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code)
LOV
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: