The following lines contain the word 'select', 'insert', 'update' or 'delete':
--select nvl(item_desc1, '')
--from ic_item_mst
--where item_id = x_item_id_in ;
SELECT nvl(description, '')
FROM mtl_system_items_b_kfv
WHERE organization_id = x_sampling_event.organization_id
AND inventory_item_id = x_inventory_item_id;
select nvl(sample_cnt_req, 0) sample_cnt_req,
nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
nvl(sample_qty, 0) sample_qty, sample_qty_uom,
nvl(RESERVE_QTY, 0) reserve_qty,
nvl(ARCHIVE_QTY,0) archive_qty
from gmd_sampling_plans_b sm
where sm.sampling_plan_id = x_sampling_plan_id ;
/* select nvl (SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
from gmd_all_spec_vrs
where spec_vr_id = x_spec_vr_id ; */
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_INVENTORY_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
MTL_SYSTEM_ITEMS_KFV I ,
GMD_QC_STATUS_TL T ,
GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ( 'LANG' )
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_WIP_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
MTL_SYSTEM_ITEMS_KFV I ,
GMD_QC_STATUS_TL T ,
GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND I.ORGANIZATION_ID = S.OWNER_ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ( 'LANG' )
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_CUSTOMER_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
MTL_SYSTEM_ITEMS_KFV I ,
GMD_QC_STATUS_TL T ,
GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND S.SPEC_STATUS = P.STATUS_CODE -- NEW
AND P.ENTITY_TYPE = 'S' -- NEW
AND P.LANGUAGE = USERENV ( 'LANG' ) -- NEW
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_SUPPLIER_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
MTL_SYSTEM_ITEMS_KFV I ,
GMD_QC_STATUS_TL T ,
GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ( 'LANG' )
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT 'N' SAMPLE_INV_TRANS_IND
FROM GMD_MONITORING_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
GMD_QC_STATUS_TL P ,
GMD_QC_STATUS_TL T
WHERE V.SPEC_ID = S.SPEC_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ( 'LANG' )
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT 'N' SAMPLE_INV_TRANS_IND
FROM GMD_STABILITY_SPEC_VRS V ,
GMD_SPECIFICATIONS_B S ,
GMD_QC_STATUS_TL T ,
GMD_QC_STATUS_TL L
WHERE V.SPEC_ID = S.SPEC_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND S.SPEC_STATUS = L.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND L.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ( 'LANG' )
AND L.LANGUAGE = USERENV ( 'LANG' )
and v.spec_vr_id = x_spec_vr_id;
/* Update created sampling event */
p_event_spec_disp.sampling_event_id := x_sampling_event.sampling_event_id;
p_event_spec_disp.DELETE_MARK := 0;
p_event_spec_disp.LAST_UPDATE_DATE := sysdate;
p_event_spec_disp.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row(
p_event_spec_disp =>p_event_spec_disp,
x_event_spec_disp =>x_event_spec_disp) THEN
RAISE FND_API.G_EXC_ERROR;
p_sample.delete_mark := 0;
p_sample.last_update_date := sysdate;
p_sample.last_updated_by := FND_GLOBAL.USER_ID;
IF not GMD_SAMPLES_PVT.insert_row (
p_sample, x_sample ) THEN
raise fnd_api.g_exc_error;
p_sample_spec_disp.delete_mark := 0;
p_sample_spec_disp.last_update_date := sysdate;
p_sample_spec_disp.last_updated_by := FND_GLOBAL.USER_ID;
IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
p_sample_spec_disp ) THEN
raise fnd_api.g_exc_error;
p_sample.delete_mark := 0;
p_sample.last_update_date := sysdate;
p_sample.last_updated_by := FND_GLOBAL.USER_ID;
IF not GMD_SAMPLES_PVT.insert_row (
p_sample, x_sample ) THEN
raise fnd_api.g_exc_error;
IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
p_sample_spec_disp ) THEN
raise fnd_api.g_exc_error;
p_sample.delete_mark := 0;
p_sample.last_update_date := sysdate;
p_sample.last_updated_by := FND_GLOBAL.USER_ID;
IF not GMD_SAMPLES_PVT.insert_row (
p_sample, x_sample ) THEN
raise fnd_api.g_exc_error;
IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
p_sample_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* Update the sampling event samples taken */
update gmd_sampling_events
set SAMPLE_TAKEN_CNT = 0
where sampling_event_id = x_sampling_event.sampling_event_id ;
update gmd_sampling_events
set ARCHIVED_TAKEN = 0
where sampling_event_id = x_sampling_event.sampling_event_id ;
update gmd_sampling_events
set RESERVED_TAKEN = 0
where sampling_event_id = x_sampling_event.sampling_event_id ;
update gmd_sampling_events
set SAMPLE_REQ_CNT = l_sample_cnt_req
where sampling_event_id = x_sampling_event.sampling_event_id ;
/* Update the sampling event disposition to Planned */
update gmd_sampling_events
set disposition = '0PL'
where sampling_event_id = x_sampling_event.sampling_event_id ;