The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT spec_vr_id
FROM GMD_STABILITY_SPEC_VRS
WHERE spec_id = p_spec_id;
select gmd.gmd_qc_spec_vr_id_s.NEXTVAL
into x_spec_vr_id
from dual;
/* insert into bfs_msg
values
( 'NEW: spec_vr_id = ' || x_spec_vr_id );
INSERT INTO GMD_STABILITY_SPEC_VRS
(
SPEC_VR_ID
,SPEC_ID
,SPEC_VR_STATUS
,START_DATE
,DELETE_MARK
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY )
values (
x_spec_vr_id
,p_spec_id
,700
,sysdate
,0
,sysdate
,p_created_by
,sysdate
,p_created_by
);
SELECT ss_id,
sample_qty,
sample_quantity_uom --INVCONV
FROM GMD_SS_VARIANTS v
WHERE v.variant_id = p_variant_id;
SELECT inventory_item_id,
revision,
organization_id,
created_by
FROM gmd_stability_studies ss
WHERE ss_id = var_rec.ss_id;
p_sampling_event.last_updated_by := ss_rec.created_by;
p_sampling_event.last_update_date := sysdate;
p_event_spec_disp.delete_mark := 0;
p_event_spec_disp.last_update_date := sysdate;
p_event_spec_disp.last_updated_by := ss_rec.created_by;
IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
p_sampling_event,
x_sampling_event ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'sampling_event_id = '||
x_sampling_event.sampling_event_id );
IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
p_event_spec_disp,
x_event_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'event_spec_disp_id = '||
x_event_spec_disp.event_spec_disp_id );
SELECT inventory_item_id,
status,
organization_id,
lab_organization_id,
created_by,
last_updated_by,
revision
FROM GMD_STABILITY_STUDIES_B
WHERE ss_id = p_ss_id;
SELECT a.source_id,
a.source_organization_id,
a.batch_id,
a.recipe_id,
a.recipe_no,
a.lot_number,
a.sampling_event_id
FROM GMD_SS_MATERIAL_SOURCES a
WHERE ss_id = p_ss_id;
SELECT variant_id,
variant_no,
samples_per_time_point,
retained_samples,
default_spec_id,
/* default_spec_vr_id, */
sample_qty,
sample_quantity_uom
FROM GMD_SS_VARIANTS
WHERE material_source_id = mat_rec.source_id;
SELECT time_point_id,
spec_id,
scheduled_date,
samples_per_time_point
FROM GMD_SS_TIME_POINTS tp
WHERE tp.variant_id = var_rec.variant_id ;
SELECT 'X' FROM gmd_ss_variants gsv
where gsv.ss_id = p_ss_id
and exists
( select 'x' from gmd_sampling_events gse
where gse.variant_id = gsv.variant_id ) ;
/* insert into bfs_msg
values
( 'p_ss_id = ' || p_ss_id); */
/* insert into bfs_msg
values
( 'ss_rec.item_id = '|| ss_rec.item_id );
/* insert into bfs_msg
values
( 'Create Sample workflow notification= ' ||
to_char(mat_rec.source_id,9999999999) );
/* insert into bfs_msg
values
( 'Create Batch workflow notification= '||
mat_rec.source_id);
/* insert into bfs_msg
values
('creating time point samples');
/* insert into bfs_msg
values
('Varint_id = '|| var_rec.variant_id);
p_sampling_event.last_updated_by := ss_rec.created_by;
p_sampling_event.last_update_date := sysdate;
p_sample.delete_mark := 0;
p_sample.last_update_date := sysdate;
p_sample.last_updated_by := ss_rec.created_by;
p_event_spec_disp.delete_mark := 0;
p_event_spec_disp.last_update_date := sysdate;
p_event_spec_disp.last_updated_by := ss_rec.created_by;
p_sample_spec_disp.delete_mark := 0;
p_sample_spec_disp.last_update_date := sysdate;
p_sample_spec_disp.last_updated_by := ss_rec.created_by;
IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
p_sampling_event,
x_sampling_event ) THEN
raise fnd_api.g_exc_error;
UPDATE gmd_ss_variants
set sampling_event_id =
x_sampling_event.sampling_event_id
WHERE variant_id = var_rec.variant_id;
IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
p_event_spec_disp,
x_event_spec_disp ) THEN
raise fnd_api.g_exc_error;
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;
/* insert into bfs_msg
values
( ' time_point_id = ' ||
tp_rec.time_point_id );
IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
p_sampling_event,
x_sampling_event ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'sampling_event_id = '||
x_sampling_event.sampling_event_id );
/* GMD_SS_VARIANTS is updated with the sampling_event_id for the
-- Variant's retained samples. The variant's retained samples are
-- associated with the same sampling event. */
UPDATE gmd_ss_time_points
set sampling_event_id =
x_sampling_event.sampling_event_id
WHERE time_point_id = tp_rec.time_point_id;
IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
p_event_spec_disp,
x_event_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'event_spec_disp_id = '||
x_event_spec_disp.event_spec_disp_id );
/* insert into bfs_msg
values
('time_point_id = '|| tp_rec.time_point_id ||
' tp_cnt= '||tp_cnt);
/* insert into bfs_msg
values
(' sample_no = ' || p_sample.sample_no );
IF not GMD_SAMPLES_PVT.insert_row (
p_sample,
x_sample ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
(' sample_id = ' || x_sample.sample_id );
IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
p_sample_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
(' sample_id = ' || x_sample.sample_id );
IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
p_sampling_event,
x_sampling_event ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'Var: sampling_event_id = '||
x_sampling_event.sampling_event_id );
/* GMD_SS_VARIANTS is updated with the sampling_event_id for the
-- Variant's retained samples. The variant's retained samples are
-- associated with the same sampling event. */
UPDATE gmd_ss_variants
set sampling_event_id =
x_sampling_event.sampling_event_id
WHERE variant_id = var_rec.variant_id;
IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
p_event_spec_disp,
x_event_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
( 'Var: event_spec_disp_id = '||
x_event_spec_disp.event_spec_disp_id );
/* insert into bfs_msg
values
(' sample_no = ' || p_sample.sample_no );
IF not GMD_SAMPLES_PVT.insert_row (
p_sample,
x_sample ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
(' sample_id = ' || x_sample.sample_id );
IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
p_sample_spec_disp ) THEN
raise fnd_api.g_exc_error;
/* insert into bfs_msg
values
(' Sample Spec: sample_id = ' || x_sample.sample_id );