[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT B.INVENTORY_ITEM_ID,
M.revision,
B.concatenated_segments,
B.DESCRIPTION,
M.TRANSACTION_TYPE_ID,
T.TRANSACTION_TYPE_NAME,
M.TRANSACTION_QUANTITY,
M.SECONDARY_TRANSACTION_QUANTITY,
M.TRANSACTION_UOM,
M.SECONDARY_UOM_CODE,
M.ORGANIZATION_ID,
mp.organization_code,
M.SUBINVENTORY_code,
M.LOCATOR_ID,
M.transaction_source_type_id, -- 9756188 added
M.transaction_action_id, -- 9756188 added
M.last_updated_by -- 6276012 added
--QZENG Bug 13881118 Changed from MTL_SYSTEM_ITEMS_B_KFV to MTL_SYSTEM_ITEMS_VL to support NLS
--FROM MTL_SYSTEM_ITEMS_B_KFV B,
FROM MTL_SYSTEM_ITEMS_VL B,
MTL_MATERIAL_TRANSACTIONS M,
MTL_TRANSACTION_TYPES T,
mtl_parameters mp
WHERE M.transaction_id = l_trans_id -- replace l_event_key by l_trans_id -- 9756188
AND M.organization_id = B.organization_id
AND mp.organization_id = B.organization_id
AND M.inventory_item_id = B.inventory_item_id
AND M.transaction_type_id = T.transaction_type_id;
SELECT c.PARENT_LOT_NUMBER, c.LOT_NUMBER
FROM MTL_LOT_NUMBERS C,
MTL_MATERIAL_TRANSACTIONS M,
MTL_TRANSACTION_LOT_NUMBERS L
WHERE M.transaction_id = l_trans_id -- replace l_event_key by l_trans_id -- 9756188
AND M.inventory_item_id = C.inventory_item_id
AND M.organization_id = C.organization_id
AND C.LOT_NUMBER = L.LOT_NUMBER
AND M.transaction_id = L.transaction_id;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = loc_id;
SELECT B.inventory_item_id,
mp.organization_code, --RLNAGARA B4905645
B.organization_id,
B.concatenated_segments ITEM_Number,
B.description,
A.parent_LOT_Number,
A.LOT_Number,
B.primary_uom_code,
B.primary_uom_code,
A.expiration_date,
A.Retest_Date,
A.last_updated_by --Bug#6276012 added
--QZENG Bug 13881118 Changed from MTL_SYSTEM_ITEMS_B_KFV to MTL_SYSTEM_ITEMS_VL to support NLS
--FROM mtl_lot_numbers A, mtl_system_items_b_kfv B, mtl_parameters mp --RLNAGARA B4905645
FROM mtl_lot_numbers A, mtl_system_items_vl B, mtl_parameters mp --RLNAGARA B4905645
WHERE A.inventory_item_id = B.inventory_item_id
AND A.organization_id = B.organization_id
AND mp.organization_id = A.organization_id
AND --RLNAGARA B4905645
A.organization_id || '-' || A.inventory_item_id || '-' ||
A.LOT_number = l_event_key;
SELECT supplier_lot_number, rlt.lot_num
from rcv_lot_transactions rlt,
mtl_lot_numbers mln
where rlt.TRANSACTION_ID = l_transaction_id
and rlt.item_id = l_inventory_item_id
AND mln.lot_number = rlt.lot_num
AND mln.organization_id = l_organization_id
AND mln.inventory_item_id = l_inventory_item_id;
SELECT so.organization_code,
im.organization_id,
rt.subinventory,
im.inventory_Item_id,
sl.item_revision,
im.concatenated_segments item_number,
im.description,
rt.Quantity,
rt.secondary_quantity,
rt.unit_of_measure,
rt.secondary_unit_of_measure,
rt.shipment_header_id,
rt.po_header_id,
rt.po_line_id,
rt.vendor_id,
rt.vendor_site_id,
sl.shipment_line_id,
po.segment1 purchase_no,
sh.receipt_num,
rt.locator_id,
po.org_id, --RLNAGARA B5018797
hou.name, --RLNAGARA B5018797
rt.vendor_lot_num, --Bug#6145310
rt.MOBILE_TXN, -- 9509656
rt.TRANSACTION_ID, -- 9509656
rt.last_updated_by --Bug#6276012 added
FROM rcv_transactions rt,
mtl_system_items_kfv im,
mtl_parameters so,
rcv_shipment_lines sl,
po_headers_all po,
rcv_shipment_headers sh,
hr_operating_units hou --RLNAGARA B5018797
WHERE rt.TRANSACTION_ID = l_event_key
AND rt.shipment_header_id = sl.shipment_header_id
AND rt.shipment_line_id = sl.shipment_line_id
AND sl.item_id = im.inventory_item_id
AND sl.to_organization_id = im.organization_id
AND so.organization_id = im.organization_id
AND rt.organization_id = im.ORGANIZATION_ID
AND po.po_header_id = rt.po_header_id
AND sh.shipment_header_id = rt.shipment_header_id
AND po.org_id = hou.organization_id; --RLNAGARA B5018797
SELECT description
FROM mtl_secondary_inventories
WHERE organization_id = l_organization_id
AND secondary_inventory_name = l_subinventory;
SELECT so.organization_code,
im.organization_id,
rt.subinventory,
im.inventory_Item_id,
sl.item_revision,
im.concatenated_segments item_number,
im.description,
rt.Quantity,
rt.secondary_quantity,
rt.unit_of_measure,
rt.secondary_unit_of_measure,
rt.shipment_header_id,
rt.po_header_id,
rt.po_line_id,
rt.vendor_id,
rt.vendor_site_id,
sl.shipment_line_id,
po.segment1 purchase_no,
sh.receipt_num,
rt.locator_id,
po.org_id, --RLNAGARA B5018797
hou.name, --RLNAGARA B5018797
rt.vendor_lot_num, --Bug#6145310
rt.MOBILE_TXN, -- 9509656
rt.TRANSACTION_ID -- 9509656
FROM rcv_transactions rt,
mtl_system_items_kfv im,
mtl_parameters so,
rcv_shipment_lines sl,
po_headers_all po,
rcv_shipment_headers sh,
hr_operating_units hou --RLNAGARA B5018797
WHERE rt.TRANSACTION_ID = l_trans_id
AND rt.shipment_header_id = sl.shipment_header_id
AND rt.shipment_line_id = sl.shipment_line_id
AND sl.item_id = im.inventory_item_id
AND sl.to_organization_id = im.organization_id
AND so.organization_id = im.organization_id
AND rt.organization_id = im.ORGANIZATION_ID
AND po.po_header_id = rt.po_header_id
AND sh.shipment_header_id = rt.shipment_header_id
AND po.org_id = hou.organization_id; --RLNAGARA B5018797
SELECT LOT_NUMBER
FROM mtl_lot_numbers
WHERE GEN_OBJECT_ID = p_gen_obj_id;
select mtln.lot_number, mln.parent_lot_number,mtln.transaction_quantity, mtln.secondary_transaction_quantity
from mtl_transaction_lot_numbers mtln,mtl_lot_numbers mln
where mtln.transaction_id = p_trans_id
and mln.gen_object_id = p_gen_obj_id
and mln.lot_number = mtln.lot_number
and mln.inventory_item_id = mtln.inventory_item_id
and mln.organization_id = mtln.organization_id;
SELECT rcv_transaction_id
FROM mtl_material_transactions
WHERE transaction_id = l_trans_id;
SELECT so.organization_code,
im.organization_id,
rt.subinventory,
im.inventory_Item_id,
sl.item_revision,
im.concatenated_segments item_number,
im.description,
rt.Quantity total_primary,
rt.secondary_quantity total_secondary,
rt.unit_of_measure,
rt.secondary_unit_of_measure,
rt.shipment_header_id,
rt.po_header_id,
rt.po_line_id,
rt.vendor_id,
rt.vendor_site_id,
sl.shipment_line_id,
po.segment1,
sh.receipt_num,
rt.locator_id,
lot.lot_number,
lot.parent_lot_number,
po.org_id,
hou.name,
sum(tran.quantity) lot_primary,
sum(tran.secondary_quantity) lot_secondary,
rt.vendor_lot_num ,--Bug#6145310
lot.grade_code, -- 10044459
tran.last_updated_by --Bug#6276012 added
FROM rcv_transactions rt,
--QZENG Bug 13881118 Change view from mtl_system_items_b_kfv to mtl_system_items_vl to support NLS
--mtl_system_items_b_kfv im,
mtl_system_items_vl im,
mtl_parameters so,
rcv_shipment_lines sl,
po_headers_all po,
rcv_shipment_headers sh,
rcv_lot_transactions tran,
mtl_lot_numbers lot,
hr_operating_units hou
WHERE rt.TRANSACTION_ID = p_trans_id
AND rt.shipment_header_id = sl.shipment_header_id
AND rt.shipment_line_id = sl.shipment_line_id
AND sl.item_id = im.inventory_item_id
AND sl.to_organization_id = im.organization_id
AND so.organization_id = im.organization_id
AND rt.organization_id = im.ORGANIZATION_ID
AND po.po_header_id = rt.po_header_id
AND sh.shipment_header_id = rt.shipment_header_id
AND rt.transaction_id = tran.transaction_id
AND tran.lot_num = p_lot_number
AND lot.inventory_item_id = im.inventory_item_id
AND tran.lot_num = lot.lot_number
AND lot.organization_id = im.organization_id
AND po.org_id = hou.organization_id
GROUP BY so.organization_code,
im.organization_id,
rt.subinventory,
im.inventory_Item_id,
sl.item_revision,
im.concatenated_segments,
im.description,
rt.Quantity,
rt.secondary_quantity,
rt.unit_of_measure,
rt.secondary_unit_of_measure,
rt.shipment_header_id,
rt.po_header_id,
rt.po_line_id,
rt.vendor_id,
rt.vendor_site_id,
sl.shipment_line_id,
po.segment1,
sh.receipt_num,
rt.locator_id,
lot.lot_number,
lot.parent_lot_number,
po.org_id,
hou.name,
rt.vendor_lot_num,
lot.grade_code, -- 10044459
tran.last_updated_by; --Bug#6276012 added
select nvl(sample_cnt_req, 0) sample_cnt_req,
nvl(sample_qty, 0) sample_qty,
sample_qty_uom,
frequency_cnt,
frequency_per,
sm.sampling_plan_id,
frequency_type,
nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
nvl(RESERVE_QTY, 0) reserve_qty,
nvl(ARCHIVE_CNT_REQ, 0) archive_cnt_req,
nvl(ARCHIVE_QTY, 0) archive_qty
from gmd_com_spec_vrs_vl sv, --gmd_all_spec_vrs sv, performance bug# 4916912
gmd_sampling_plans_b sm
where sv.sampling_plan_id = sm.sampling_plan_id
and sv.spec_vr_id = x_spec_vr_id_in;
select nvl(sample_cnt_req, 0) sample_cnt_req,
nvl(sample_qty, 0) sample_qty,
sample_qty_uom,
frequency_cnt,
frequency_per,
sm.sampling_plan_id,
frequency_type,
nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
nvl(RESERVE_QTY, 0) reserve_qty,
nvl(ARCHIVE_CNT_REQ, 0) archive_cnt_req,
nvl(ARCHIVE_QTY, 0) archive_qty
from gmd_sampling_plans_b sm, gmd_inventory_spec_vrs sv
where sv.sampling_plan_id = sm.sampling_plan_id
and sv.spec_vr_id = X_SPEC_VR_ID;
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
and lookup_code = x_frequency_per;
select nvl(auto_sample_ind, 'N')
from GMD_INVENTORY_SPEC_VRS
where spec_vr_id = X_SPEC_VR_ID;
Select nvl(auto_sample_ind, 'N')
from GMD_SUPPLIER_SPEC_VRS
where spec_vr_id = X_SPEC_VR_ID;
Select nvl(auto_sample_ind, 'N')
from GMD_COM_SPEC_VRS_VL --GMD_ALL_SPEC_VRS performance bug# 4916912
where spec_vr_id = X_SPEC_VR_ID;
select sample_no
from gmd_Samples
where sampling_event_id = x_Sampling_event_in
and retain_as = x_retain_as_in;
select sample_no
from gmd_Samples
where sampling_event_id = x_Sampling_event_in
and retain_as is NULL;
select nvl(text, '')
from wf_Resources
where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
and language = userenv('LANG');
SELECT a.segment1
FROM po_vendors a, mtl_parameters m
WHERE a.vendor_id = l_vendor_id
AND m.organization_id = l_organization_id
AND m.process_enabled_flag = 'Y';
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'GMD';
SELECT user_name FROM fnd_user WHERE user_id = x_user_id;
ELSIF (l_event_name = 'oracle.apps.gmi.lotexpirydate.update') THEN
gmd_debug.put_line('Lot Expiry Date '); /* 4576699 */
ELSIF (l_event_name = 'oracle.apps.gmi.lotretestdate.update') THEN
gmd_debug.put_line('Lot Retest Date '); /* 4576699 */
l_sampling_events.LAST_UPDATED_BY := local_user; --Bug#6276012 replace FND_GLOBAL.USER_ID;
l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
l_sampling_events.org_id := l_org_id; --RLNAGARA B5018797 Added this parameter to insert
GMD_SAMPLING_EVENTS_PVT.insert_row(p_sampling_events => l_sampling_events,
x_sampling_events => l_sampling_events_out) THEN
gmd_debug.put_line('Sampling Event Creation Failed'); -- Bug # 4576699
SELECT uom_code
INTO l_trans_qty_uom
FROM mtl_units_of_measure
WHERE unit_of_measure = l_trans_qty_uom;
UPDATE GMD_SUPPLIER_SPEC_VRS
SET RECEIPT_FREQUENCY = NVL(RECEIPT_FREQUENCY, 0) + 1 --RLNAGARA Bug 5910300 Rework Added NVL
WHERE SPEC_VR_ID = l_spec_vr_id;
select RECEIPT_FREQUENCY
into r
from GMD_SUPPLIER_SPEC_VRS
where spec_vr_id = l_spec_vr_id;
UPDATE GMD_SUPPLIER_SPEC_VRS
SET RECEIPT_FREQUENCY = 0
WHERE SPEC_VR_ID = l_spec_vr_id;
l_event_spec_disp.DELETE_MARK := 0;
l_event_spec_disp.LAST_UPDATE_DATE := sysdate;
l_event_spec_disp.LAST_UPDATED_BY := local_user; --Bug#6276012 replace FND_GLOBAL.USER_ID;
gmd_debug.put_line('Going to insert event spec disp');
GMD_EVENT_SPEC_DISP_PVT.INSERT_ROW(p_event_spec_disp => l_event_spec_disp,
x_event_spec_disp => l_event_spec_disp_out) THEN
gmd_debug.put_line('Sampling Event disposition Creation Failed'); -- Bug # 4576699
l_sampling_events.LAST_UPDATED_BY := local_user; --Bug#6276012 replace FND_GLOBAL.USER_ID;
l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
gmd_debug.put_line('Going to insert sample event');
GMD_SAMPLING_EVENTS_PVT.insert_row(p_sampling_events => l_sampling_events,
x_sampling_events => l_sampling_events_out) THEN
gmd_debug.put_line('Sampling Event Creation Failed'); -- Bug # 4576699
l_event_spec_disp.DELETE_MARK := 0;
l_event_spec_disp.LAST_UPDATE_DATE := sysdate;
l_event_spec_disp.LAST_UPDATED_BY := local_user; --Bug#6276012 replace FND_GLOBAL.USER_ID;
gmd_debug.put_line('Going to insert event spec disp');
GMD_EVENT_SPEC_DISP_PVT.insert_row(
p_event_spec_disp => l_event_spec_disp,
x_event_spec_disp => l_event_spec_disp_out) THEN
gmd_debug.put_line('Sampling Event disposition Creation Failed'); -- Bug # 4576699
END IF; -- IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row
AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
transactionIdIn => l_event_key,
approverIn => Approver,
transactionTypeIn => l_transaction_type,
forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);
select application_id
into l_application_id
from fnd_application
where application_short_name = 'GMD';
select user_name
into l_user
from fnd_user
where user_id = Approver.user_id;
select user_name
into l_user
from fnd_user
where user_id = AME_UTIL.PERSONIDTOUSERID(Approver.person_id);
AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
transactionIdIn => l_event_key,
approverIn => Approver,
transactionTypeIn => l_transaction_type,
forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);
l_last_update_by number;
SELECT nvl(sample_cnt_req, 0) sample_cnt_req,
nvl(sample_qty, 0) sample_qty,
sample_qty_uom,
frequency_cnt,
frequency_per,
sm.sampling_plan_id,
frequency_type,
nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
nvl(RESERVE_QTY, 0) reserve_qty,
nvl(ARCHIVE_CNT_REQ, 0) archive_cnt_req,
nvl(ARCHIVE_QTY, 0) archive_qty
FROM gmd_sampling_plans_b sm, gmd_wip_spec_vrs sv
WHERE sv.sampling_plan_id = sm.sampling_plan_id
AND sv.spec_vr_id = X_SPEC_VR_ID;
SELECT meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
AND lookup_code = x_frequency_per;
SELECT sampling_plan_id, nvl(lot_optional_on_sample,'N'), nvl(delayed_lot_entry,'N') -- 10406191
FROM gmd_com_spec_vrs_vl --gmd_all_spec_vrs performance bug# 4916912
WHERE spec_vr_id = x_spec_vr_id_in;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = loc_id;
SELECT spec_name, spec_vers
FROM gmd_specifications_b
WHERE spec_id = x_spec_id_in;
SELECT A.BATCH_NO,
A.BATCH_ID,
A.ORGANIZATION_ID,
P.ORGANIZATION_CODE,
A.ROUTING_ID,
C.RECIPE_ID,
A.FORMULA_ID,
A.ROUTING_ID,
F.INVENTORY_ITEM_ID,
F.REVISION,
F.FORMULALINE_ID,
F.MATERIAL_DETAIL_ID,
F.LOCATOR_ID, --RLNAGARA B5389806
F.SUBINVENTORY, --RLNAGARA B5389806
to_number(NULL) BATCHSTEP_ID,
to_number(NULL) BATCHSTEP_NO,
to_number(NULL) OPRN_ID,
C.RECIPE_NO || ' / ' || C.RECIPE_VERSION,
H.FORMULA_NO || ' / ' || FORMULA_VERS,
I.CONCATENATED_SEGMENTS,
I.DESCRIPTION ITEM_DESC1,
A.LAST_UPDATED_BY,
nvl(f.wip_plan_qty, f.plan_qty) trans_qty, -- 13107927
f.dtl_um um -- 13107927
FROM GME_BATCH_HEADER A,
GMD_RECIPE_VALIDITY_RULES b,
GMD_RECIPES_B C, --GMD_RECIPES C performance bug# 4916912
GME_MATERIAL_DETAILS F,
FM_FORM_MST_B H, --FM_FORM_MST H performance bug# 4916912
MTL_PARAMETERS P,
--QZENG Bug 13881118 Change view from mtl_system_items_b_kfv to mtl_system_items_vl to support NLS
--MTL_SYSTEM_ITEMS_B_KFV I
MTL_SYSTEM_ITEMS_VL I
WHERE A.BATCH_ID = x_batch_id
AND A.BATCH_ID = F.BATCH_ID
AND A.RECIPE_VALIDITY_RULE_ID = B.RECIPE_VALIDITY_RULE_ID
AND B.RECIPE_ID = C.RECIPE_ID
AND NVL(x_batch_step_id, 1) = 1
AND F.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND H.FORMULA_ID = A.FORMULA_ID
AND F.LINE_TYPE = 1 /*Bug#6638743*/
AND P.ORGANIZATION_ID = A.ORGANIZATION_ID
AND (B.ORGANIZATION_ID = A.ORGANIZATION_ID OR
B.ORGANIZATION_ID IS NULL)
AND -- PK Bug 6595576
I.ORGANIZATION_ID = A.ORGANIZATION_ID
AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
UNION
SELECT A.BATCH_NO,
A.BATCH_ID,
A.ORGANIZATION_ID,
P.ORGANIZATION_CODE,
A.ROUTING_ID,
C.RECIPE_ID,
A.FORMULA_ID,
A.ROUTING_ID,
F.INVENTORY_ITEM_ID,
F.REVISION,
F.FORMULALINE_ID,
F.MATERIAL_DETAIL_ID,
F.LOCATOR_ID, --RLNAGARA B5389806
F.SUBINVENTORY, --RLNAGARA B5389806
D.BATCHSTEP_ID,
D.BATCHSTEP_NO BATCHSTEP_NO,
D.OPRN_ID,
C.RECIPE_NO || ' / ' || C.RECIPE_VERSION,
H.FORMULA_NO || ' / ' || FORMULA_VERS,
I.CONCATENATED_SEGMENTS ITEM_NUMBER,
I.DESCRIPTION,
A.LAST_UPDATED_BY,
d.plan_step_qty trans_qty, -- 13107927
d.step_qty_um um -- 13107927
FROM GME_BATCH_HEADER A,
GMD_RECIPE_VALIDITY_RULES b,
GMD_RECIPES_B C, --GMD_RECIPES C performance bug# 4916912
GME_BATCH_STEPS D,
GME_MATERIAL_DETAILS F,
FM_FORM_MST_B H, --FM_FORM_MST H performance bug# 4916912
MTL_PARAMETERS P,
--QZENG Bug 13881118 Change view from mtl_system_items_b_kfv to mtl_system_items_vl to support NLS
--MTL_SYSTEM_ITEMS_B_KFV I
MTL_SYSTEM_ITEMS_VL I
WHERE A.BATCH_ID = x_batch_id
AND A.BATCH_ID = F.BATCH_ID
AND A.BATCH_ID = D.BATCH_ID
AND A.RECIPE_VALIDITY_RULE_ID = B.RECIPE_VALIDITY_RULE_ID
AND B.RECIPE_ID = C.RECIPE_ID
AND D.BATCHSTEP_ID = x_batch_step_id
AND --RLNAGARA Bug 5032406 (FP of 4604305 ME) For batch creation, do not select steps
F.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND H.FORMULA_ID = A.FORMULA_ID
AND P.ORGANIZATION_ID = A.ORGANIZATION_ID
AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
AND (B.ORGANIZATION_ID = A.ORGANIZATION_ID OR
B.ORGANIZATION_ID IS NULL)
AND -- PK Bug 6595576
I.ORGANIZATION_ID = A.ORGANIZATION_ID
AND F.LINE_TYPE = 1 /*Bug#6638743*/
ORDER by BATCHSTEP_NO DESC;
select nvl(auto_sample_ind, 'N')
from GMD_WIP_SPEC_VRS
where spec_vr_id = X_SPEC_VR_ID;
select sample_no
from gmd_Samples
where sampling_event_id = x_Sampling_event_in
and retain_as = x_retain_as;
select sample_no
from gmd_Samples
where sampling_event_id = x_Sampling_event_in
and retain_as is NULL;
select nvl(text, '')
from wf_Resources
where name = 'WF_ADMIN_ROLE' --RLNAGARA B5654562 Changed from WF_ADMIN to WF_ADMIN_ROLE
and language = userenv('LANG');
select mln.lot_number, mln.parent_lot_number
from mtl_lot_numbers mln, gme_material_details d, GME_PENDING_PRODUCT_LOTS ppl
where mln.lot_number = ppl.lot_number
and ppl.material_detail_id = x_material_detail_id
and ppl.material_detail_id = d.material_detail_id
and d.organization_id = mln.organization_id
and d.inventory_item_id = mln.inventory_item_id;
select mln.lot_number, mln.parent_lot_number
from mtl_lot_numbers mln
where lot_number =
(select lot_number
from GME_PENDING_PRODUCT_LOTS
where material_detail_id = x_material_detail_id
and pending_product_lot_id =
(select min(pending_product_lot_id)
from GME_PENDING_PRODUCT_LOTS
where material_detail_id = x_material_detail_id));
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'GMD';
SELECT BATCH_ID
INTO t_batch_id
FROM gme_batch_steps
WHERE batchstep_id = l_event_key;
SELECT BATCH_ID
INTO t_batch_id
FROM gme_batch_steps
WHERE batchstep_id = l_event_key;
L_STEP_ID, L_STEP_NO, L_OPRN_ID, L_RECIPE_NO, L_FORMULA_NO, L_ITEM_NUMBER, L_ITEM_DESC, L_LAST_UPDATE_BY, l_plan_step_qty, l_step_qty_um; -- 13107927 added 2 last fields
l_sampling_events.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
GMD_SAMPLING_EVENTS_PVT.insert_row(p_sampling_events => l_sampling_events,
x_sampling_events => l_sampling_events_out) THEN
RAISE FND_API.G_EXC_ERROR;
UPDATE GMD_WIP_SPEC_VRS
SET BATCH_FREQUENCY = NVL(BATCH_FREQUENCY, 0) + 1 --RLNAGARA Bug 5910300 Rework Added NVL
WHERE SPEC_VR_ID = l_spec_vr_id;
select batch_frequency
into b
from gmd_wip_spec_vrs
where spec_vr_id = l_spec_vr_id;
UPDATE GMD_WIP_SPEC_VRS
SET BATCH_FREQUENCY = 0
WHERE SPEC_VR_ID = l_spec_vr_id;
SELECT uom_code
INTO l_trans_qty_uom
FROM mtl_units_of_measure
WHERE unit_of_measure = l_step_qty_um;
l_event_spec_disp.DELETE_MARK := 0;
l_event_spec_disp.LAST_UPDATE_DATE := sysdate;
l_event_spec_disp.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
gmd_debug.put_line('Going to insert event spec disp');
GMD_EVENT_SPEC_DISP_PVT.insert_row(p_event_spec_disp => l_event_spec_disp,
x_event_spec_disp => l_event_spec_disp_out) THEN
RAISE FND_API.G_EXC_ERROR;
fnd_global.apps_initialize(USER_ID => l_last_update_by,
resp_id => NULL,
resp_appl_id => NULL);
fnd_profile.initialize(l_last_update_by);
gme_api_grp.update_step_quality_status(p_batchstep_id => l_step_id,
p_org_id => l_organization_id,
p_quality_status => 2,
x_return_status => l_return_status);
select user_name
into l_user
from fnd_user
where user_id = Approver.user_id;
select user_name
into l_user
from fnd_user
where user_id =
AME_UTIL.PERSONIDTOUSERID(Approver.person_id);
AME_API.UPDATEAPPROVALSTATUS(applicationIdIn => l_application_id,
transactionIdIn => l_event_key,
approverIn => Approver,
transactionTypeIn => l_transaction_type,
forwardeeIn => AME_UTIL.EMPTYAPPROVERRECORD);