eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.JMF_SHIKYU_ALLOCATIONS_V

Source

View Text - Preformatted


SELECT jso.subcontract_po_shipment_id, (sum(wro.required_quantity) + sum(jsadj.total_adjustment)) total_quantity, sum(jsa.allocated_quantity) quantity_allocated, (CASE WHEN (sum(wro.required_quantity) + sum(jsadj.total_adjustment)) > sum(jsa.allocated_quantity) THEN 'Y' ELSE 'N' END) allocatable_flag FROM jmf_subcontract_orders jso , (SELECT jsc.subcontract_po_shipment_id, jsc.shikyu_component_id, sum(nvl(jsa.allocated_quantity, 0)) AS allocated_quantity FROM jmf_shikyu_components jsc, jmf_shikyu_allocations jsa WHERE jsc.subcontract_po_shipment_id = jsa.subcontract_po_shipment_id (+) AND jsc.shikyu_component_id = jsa.shikyu_component_id (+) GROUP BY jsc.subcontract_po_shipment_id, jsc.shikyu_component_id) jsa, (SELECT jsc.subcontract_po_shipment_id, jsc.shikyu_component_id, sum(decode(jsadj.request_id, NULL, 0, jsadj.adjustment)) AS total_adjustment FROM jmf_shikyu_components jsc, jmf_shikyu_adjustments jsadj WHERE jsc.subcontract_po_shipment_id = jsadj.subcontract_po_shipment_id (+) AND jsc.shikyu_component_id = jsadj.shikyu_component_id (+) GROUP BY jsc.subcontract_po_shipment_id , jsc.shikyu_component_id) jsadj, wip_requirement_operations wro WHERE jso.subcontract_po_shipment_id = jsa.subcontract_po_shipment_id AND jso.wip_entity_id = wro.wip_entity_id AND wro.inventory_item_id = jsa.shikyu_component_id AND wro.organization_id = jso.tp_organization_id AND jso.subcontract_po_shipment_id = jsadj.subcontract_po_shipment_id AND wro.inventory_item_id = jsadj.shikyu_component_id GROUP BY jso.subcontract_po_shipment_id

View Text - HTML Formatted

SELECT JSO.SUBCONTRACT_PO_SHIPMENT_ID
, (SUM(WRO.REQUIRED_QUANTITY) + SUM(JSADJ.TOTAL_ADJUSTMENT)) TOTAL_QUANTITY
, SUM(JSA.ALLOCATED_QUANTITY) QUANTITY_ALLOCATED
, (CASE WHEN (SUM(WRO.REQUIRED_QUANTITY) + SUM(JSADJ.TOTAL_ADJUSTMENT)) > SUM(JSA.ALLOCATED_QUANTITY) THEN 'Y' ELSE 'N' END) ALLOCATABLE_FLAG
FROM JMF_SUBCONTRACT_ORDERS JSO
, (SELECT JSC.SUBCONTRACT_PO_SHIPMENT_ID
, JSC.SHIKYU_COMPONENT_ID
, SUM(NVL(JSA.ALLOCATED_QUANTITY
, 0)) AS ALLOCATED_QUANTITY
FROM JMF_SHIKYU_COMPONENTS JSC
, JMF_SHIKYU_ALLOCATIONS JSA
WHERE JSC.SUBCONTRACT_PO_SHIPMENT_ID = JSA.SUBCONTRACT_PO_SHIPMENT_ID (+)
AND JSC.SHIKYU_COMPONENT_ID = JSA.SHIKYU_COMPONENT_ID (+) GROUP BY JSC.SUBCONTRACT_PO_SHIPMENT_ID
, JSC.SHIKYU_COMPONENT_ID) JSA
, (SELECT JSC.SUBCONTRACT_PO_SHIPMENT_ID
, JSC.SHIKYU_COMPONENT_ID
, SUM(DECODE(JSADJ.REQUEST_ID
, NULL
, 0
, JSADJ.ADJUSTMENT)) AS TOTAL_ADJUSTMENT
FROM JMF_SHIKYU_COMPONENTS JSC
, JMF_SHIKYU_ADJUSTMENTS JSADJ
WHERE JSC.SUBCONTRACT_PO_SHIPMENT_ID = JSADJ.SUBCONTRACT_PO_SHIPMENT_ID (+)
AND JSC.SHIKYU_COMPONENT_ID = JSADJ.SHIKYU_COMPONENT_ID (+) GROUP BY JSC.SUBCONTRACT_PO_SHIPMENT_ID
, JSC.SHIKYU_COMPONENT_ID) JSADJ
, WIP_REQUIREMENT_OPERATIONS WRO
WHERE JSO.SUBCONTRACT_PO_SHIPMENT_ID = JSA.SUBCONTRACT_PO_SHIPMENT_ID
AND JSO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WRO.INVENTORY_ITEM_ID = JSA.SHIKYU_COMPONENT_ID
AND WRO.ORGANIZATION_ID = JSO.TP_ORGANIZATION_ID
AND JSO.SUBCONTRACT_PO_SHIPMENT_ID = JSADJ.SUBCONTRACT_PO_SHIPMENT_ID
AND WRO.INVENTORY_ITEM_ID = JSADJ.SHIKYU_COMPONENT_ID GROUP BY JSO.SUBCONTRACT_PO_SHIPMENT_ID