ECC Process Manufacturing, Outside Processing Batch
Description
Categories: Enterprise Command Center
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
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
Run
ECC Process Manufacturing, Outside Processing Batch and other Oracle EBS reports with Blitz Report™ on our demo environment
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, |