[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT primary_unit_of_measure
INTO l_uom
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
UPDATE JMF_SUBCONTRACT_ORDERS sco
SET sco.INTERLOCK_STATUS = 'T'
WHERE sco.INTERLOCK_STATUS IN ('N','E','U' )
AND sco.SUBCONTRACT_PO_SHIPMENT_ID IN
( SELECT poll.line_location_id
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
AND pol.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
AND poll.line_location_id = sco.SUBCONTRACT_PO_SHIPMENT_ID
AND poll.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
AND poll.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
AND poll.po_line_id = pol.po_line_id
AND poll.po_header_id = pol.po_header_id
AND pol.po_header_id = poh.po_header_id
AND ( pol.cancel_flag = 'Y' OR
poh.cancel_flag = 'Y' OR
poll.cancel_flag = 'Y'
)
) ;
SELECT primary_uom_code
INTO l_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_unit_of_measure;
SELECT sum(RT1.PRIMARY_QUANTITY)
INTO l_returned_quantity
FROM RCV_TRANSACTIONS RT1, RCV_TRANSACTIONS RT2
WHERE RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND RT1.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID
AND RT2.REPLENISH_ORDER_LINE_ID = p_replenishment_so_line_id;
SELECT PRIMARY_QUANTITY
INTO l_received_quantity
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_TYPE IN ('RECEIVE','DELIVER')
AND REPLENISH_ORDER_LINE_ID = p_replenishment_so_line_id
AND ROWNUM = 1;
SELECT SUM(QUANTITY_ISSUED)
INTO l_quantity
FROM WIP_REQUIREMENT_OPERATIONS
WHERE INVENTORY_ITEM_ID = p_shikyu_component_id
AND ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id ;
SELECT uom_code
INTO l_from_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_purchasing_UOM ;
SELECT mum.uom_code
INTO l_to_code
FROM mtl_units_of_measure mum
, mtl_system_items msi
WHERE mum.unit_of_measure = msi.primary_unit_of_measure
AND msi.inventory_item_id = p_inventory_item_id
AND msi.primary_unit_of_measure = mum.unit_of_measure
AND msi.organization_id = p_inventory_org_id;
SELECT NVL(intransit_time, 0)
INTO l_days
FROM mtl_interorg_ship_methods
WHERE from_organization_id = p_oem_organization
AND to_organization_id = p_tp_organization
AND default_flag = 1;
SELECT SCHEDULED_COMPLETION_DATE , scheduled_start_date
INTO l_completion_date , l_start_date
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = p_wip_entity_id;
SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
INTO l_xsd_date_string
FROM DUAL;
SELECT TO_CHAR(p_date, 'YYYY-MM-DD') || 'T' || TO_CHAR(p_date, 'HH24:MI:SS')
INTO l_xsd_date_string
FROM DUAL;
SELECT jso.oem_organization_id,
jso.tp_organization_id
INTO x_oem_organization_id,
x_tp_organization_id
FROM JMF_SUBCONTRACT_ORDERS jso
WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT outsourced_assembly,
subcontracting_component
INTO x_outsourced_assembly,
x_subcontracting_component
FROM MTL_SYSTEM_ITEMS_B
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT uom,
shikyu_component_price,
primary_uom,
primary_uom_price
INTO x_component_uom,
x_component_price,
x_primary_uom,
x_primary_uom_price
FROM JMF_SHIKYU_COMPONENTS
WHERE shikyu_component_id = p_shikyu_component_id
AND subcontract_po_shipment_id = p_subcontract_po_shipment_id;
SELECT SUM(allocated_primary_uom_quantity),
MAX(uom)
INTO x_allocated_primary_uom_qty,
x_primary_uom
FROM jmf_shikyu_replenishments
WHERE replenishment_po_shipment_id = p_replen_po_shipment_id;
SELECT SUM(ordered_primary_uom_quantity),
MAX(primary_uom)
INTO x_ordered_primary_uom_qty,
x_primary_uom
FROM jmf_shikyu_replenishments
WHERE replenishment_po_shipment_id = p_replen_po_shipment_id;
SELECT allocated_quantity,
uom,
allocated_primary_uom_quantity,
primary_uom
INTO x_allocated_qty,
x_uom,
x_allocated_primary_uom_qty,
x_primary_uom
FROM JMF_SHIKYU_REPLENISHMENTS
WHERE replenishment_so_line_id = p_replen_so_line_id;
SELECT NVL(SUM(allocated_quantity), 0)
INTO l_allocated_qty
FROM jmf_shikyu_allocations
WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
AND shikyu_component_id = p_component_id;
SELECT pricing_quantity_uom,
unit_selling_price
INTO x_uom,
x_price
FROM oe_order_lines_all
WHERE line_id = p_replenishment_so_line_id;
SELECT mip.shikyu_tp_offset_account_id
INTO x_offset_account
FROM mtl_interorg_parameters mip,
jmf_subcontract_orders jso
WHERE jso.subcontract_po_shipment_id = p_po_shipment_id
AND jso.oem_organization_id = mip.from_organization_id
AND jso.tp_organization_id = mip.to_organization_id
--AND mip.shikyu_enabled_flag = 'Y';
SELECT COUNT(*)
INTO l_count
FROM fnd_concurrent_requests
WHERE request_id = p_request_id
AND phase_code = 'C';