[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
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_MATERIAL_TRANSACTIONS M,
MTL_TRANSACTION_TYPES T,
mtl_parameters mp
WHERE M.transaction_id = l_event_key
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_event_key
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
FROM mtl_lot_numbers A,
mtl_system_items_b_kfv 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 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
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 LOT_NUMBER
FROM mtl_lot_numbers
WHERE GEN_OBJECT_ID = p_gen_obj_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
FROM rcv_transactions rt,
mtl_system_items_b_kfv 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;
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 := 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
IF NOT 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:=FND_GLOBAL.USER_ID;
gmd_debug.put_line('Going to insert event spec disp');
IF NOT 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 := FND_GLOBAL.USER_ID;
l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
gmd_debug.put_line('Going to insert sample event');
IF NOT 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:=FND_GLOBAL.USER_ID;
gmd_debug.put_line('Going to insert event spec disp');
IF NOT 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
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
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,
MTL_SYSTEM_ITEMS_B_KFV 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
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,
MTL_SYSTEM_ITEMS_B_KFV 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 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_LAST_UPDATE_BY;
l_sampling_events.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_sampling_events.LAST_UPDATE_DATE := SYSDATE;
IF NOT 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;
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');
IF NOT 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);