The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT serial_number_control_code
INTO L_ser_control
FROM mtl_system_items
WHERE inventory_item_id = X_item_id
AND organization_id = X_organization_id;
SELECT nvl(allow_cross_unitnum_issues,'N')
INTO L_allowed
FROM pjm_org_parameters
WHERE organization_id = L_org_id;
select nvl(eam_item_type, -2)
into l_item
from mtl_system_items_b
where inventory_item_id =X_ITEM_ID
and organization_id = X_organization_id;
SELECT decode(effectivity_control , 2 , 'Y' , 'N')
INTO G_Unit_Eff_Item
FROM mtl_system_items
WHERE inventory_item_id = X_item_id
AND organization_id = X_organization_id;
SELECT N2.unit_number
FROM pjm_unit_numbers N1
, pjm_unit_numbers N2
WHERE N1.unit_number = X_unit_number
AND N2.end_item_id = N1.end_item_id
AND N2.master_organization_id = N1.master_organization_id
AND N2.unit_number < N1.unit_number
ORDER BY N2.unit_number desc;
SELECT N2.unit_number
FROM pjm_unit_numbers N1
, pjm_unit_numbers N2
WHERE N1.unit_number = X_unit_number
AND N2.end_item_id = N1.end_item_id
AND N2.master_organization_id = N1.master_organization_id
AND N2.unit_number > N1.unit_number
ORDER BY N2.unit_number asc;
SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
4, fs.end_item_unit_number)
INTO G_WIP_Unit_Num
FROM wip_flow_schedules fs
, wip_discrete_jobs dj
, wip_entities e
WHERE e.wip_entity_id = X_wip_entity_id
AND e.organization_id = X_organization_id
AND fs.organization_id (+) = e.organization_id
AND fs.wip_entity_id (+) = e.wip_entity_id
AND dj.organization_id (+) = e.organization_id
AND dj.wip_entity_id (+) = e.wip_entity_id;
SELECT DECODE(e.entity_type, 1, dj.end_item_unit_number,
4, fs.end_item_unit_number)
INTO G_WIP_Unit_Num
FROM wip_flow_schedules fs
, wip_discrete_jobs dj
, wip_entities e
WHERE e.wip_entity_id = X_wip_entity_id
AND e.organization_id = X_organization_id
AND fs.organization_id (+) = e.organization_id
AND fs.wip_entity_id (+) = e.wip_entity_id
AND dj.organization_id (+) = e.organization_id
AND dj.wip_entity_id (+) = e.wip_entity_id;
SELECT po_distribution_id
, requisition_line_id
INTO L_po_distribution_id
, L_req_line_id
FROM rcv_transactions
WHERE transaction_id = X_rcv_transaction_id;
SELECT end_item_unit_number
INTO G_RCV_Unit_Num
FROM po_distributions_all
WHERE po_distribution_id = L_po_distribution_id;
SELECT end_item_unit_number
INTO G_RCV_Unit_Num
FROM po_req_distributions_all
WHERE requisition_line_id = L_req_line_id;
SELECT end_item_unit_number
INTO G_OE_Unit_Num
FROM oe_order_lines_all
WHERE line_id = X_so_line_id;
SELECT end_item_unit_number
INTO G_OE_Unit_Num
FROM oe_order_lines_all
WHERE line_id = X_so_line_id;
SELECT oe_order_line_id
INTO L_oe_order_line_id
FROM rcv_transactions
WHERE transaction_id = X_rcv_transaction_id;
SELECT end_item_unit_number
INTO L_serial_unitnum
FROM mtl_serial_numbers
WHERE serial_number = X_serial_number
AND inventory_item_id = X_item_id;
SELECT organization_id
, inventory_item_id
, transaction_source_type_id
, transaction_action_id
, transaction_source_id
, rcv_transaction_id
, SIGN(primary_quantity)
INTO L_organization_id
, L_item_id
, L_src_type_id
, L_trx_action_id
, L_trx_src_id
, L_rcv_trx_id
, L_direction
FROM mtl_material_transactions
WHERE transaction_id = X_transaction_id;
UPDATE mtl_serial_numbers
SET end_item_unit_number =
DECODE(L_direction, 1, L_unit_number, NULL)
WHERE (inventory_item_id, serial_number) in (
SELECT inventory_item_id, serial_number
FROM mtl_unit_transactions
WHERE transaction_id = X_transaction_id
UNION ALL /*Bug 7207502 (FP of 6391634): Added union part to select serial when assembly is lot serial controlled.*/
SELECT mut.inventory_item_id, mut.serial_number
FROM mtl_transaction_lot_numbers mtln, mtl_unit_transactions mut
WHERE mtln.transaction_id = X_transaction_id
AND mtln.serial_transaction_id = mut.transaction_id
);
INSERT INTO pjm_unit_serial_history
( serial_number
, inventory_item_id
, organization_id
, old_unit_number
, new_unit_number
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
SELECT X_serial_number
, X_item_id
, X_organization_id
, X_old_unit_number
, X_new_unit_number
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
FROM dual;