GME OPM Outside Processing
Description
Categories: BI Publisher
Imported from BI Publisher
Description: OPM Outside Processing Report(XML)
Application: Process Manufacturing Process Execution
Source: OPM Outside Processing Report (XML)
Short Name: GMEOSP
DB package:
Description: OPM Outside Processing Report(XML)
Application: Process Manufacturing Process Execution
Source: OPM Outside Processing Report (XML)
Short Name: GMEOSP
DB package:
Run
GME OPM Outside Processing and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT gbh.batch_id batch_id, gbh.batch_no batch_no, gl.meaning status, gbh.plan_start_date sched_start_date, gbh.plan_cmplt_date sched_cmplt_date, msib.concatenated_segments product, gmd.dtl_um uom, gmd.plan_qty scheduled_qty, gmd.actual_qty qty_completed FROM gme_batch_header gbh, gme_material_details gmd, wip_entities we, mtl_system_items_kfv msib, gem_lookups gl WHERE (:p_batch_from IS NULL OR (:p_batch_from IS NOT NULL AND gbh.batch_no >= :p_batch_from)) AND (:p_batch_to IS NULL OR (:p_batch_to IS NOT NULL AND gbh.batch_no <= :p_batch_to)) AND gbh.organization_id = :p_organization_id AND gbh.batch_id = gmd.batch_id AND (:p_product_from IS NULL OR (:p_product_from IS NOT NULL AND msib.concatenated_segments >= :p_product_from)) AND (:p_product_to IS NULL OR (:p_product_to IS NOT NULL AND msib.concatenated_segments <= :p_product_to)) AND msib.organization_id = :p_organization_id AND (:p_batch_status IS NULL OR (:p_batch_status IS NOT NULL AND gbh.batch_status = :p_batch_status)) AND gbh.batch_id = we.wip_entity_id AND we.primary_item_id = gmd.inventory_item_id AND msib.inventory_item_id = gmd.inventory_item_id AND msib.organization_id = gbh.organization_id AND gmd.line_type = 1 AND gl.lookup_type = 'GME_BATCH_STATUS' AND gbh.batch_status = gl.lookup_code AND (NVL(:p_open_po_flag,'N') = 'N' OR (:p_open_po_flag = 'Y' AND EXISTS ( SELECT 'PO/REQ Linked' FROM po_releases_all pr, po_headers_all ph, po_distributions_all pd, po_line_locations_all pll WHERE pd.po_line_id IS NOT NULL AND pd.line_location_id IS NOT NULL AND pd.wip_entity_id = gbh.batch_id AND pd.destination_organization_id = gbh.organization_id AND ph.po_header_id = pd.po_header_id AND pll.line_location_id = pd.line_location_id AND pr.po_release_id (+) = pd.po_release_id AND (pll.cancel_flag IS NULL OR pll.cancel_flag = 'N') AND (pll.quantity_received < (pll.quantity-pll.quantity_cancelled)) AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED' UNION ALL SELECT 'PO/REQ Linked' FROM po_requisition_lines_all prl WHERE prl.wip_entity_id = gbh.batch_id AND prl.destination_organization_id = gbh.organization_id AND nvl(prl.cancel_flag, 'N') = 'N' AND prl.line_location_id IS NULL UNION ALL SELECT 'PO/REQ Linked' FROM po_requisitions_interface_all pri WHERE pri.wip_entity_id = gbh.batch_id AND pri.destination_organization_id = gbh.organization_id))) ORDER BY gbh.batch_id |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Batch From |
|
Char | |
Batch To |
|
Char | |
Product From |
|
Char | |
Product To |
|
Char | |
Status |
|
LOV Oracle | |
Open POs Only |
|
LOV Oracle |