[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select packages_cnt,
base_spec_id ,
storage_plan_id,
storage_conditions_cnt ,
scheduled_start_date,
actual_start_date,
created_by
from gmd_stability_studies_b
where ss_id = p_stability_study_id
and delete_mark=0;
select ss_id,
source_id,
batch_id,
recipe_id ,
lot_number ,
yield_date
from gmd_ss_material_sources
where delete_mark=0
and ss_id = p_ss_id
and (p_material_source_id IS NULL
OR
source_id = p_material_source_id)
;
select max(cnt)
into l_package_cnt
from
(select count(*) cnt,
material_source_id,
storage_spec_id
from gmd_ss_variants a
where a.ss_id = p_stability_study
group by material_source_id, storage_spec_id);
select storage_spec_id ,
resources ,
storage_organization_id,
storage_subinventory,
storage_locator_id,
test_interval_plan_id ,
storage_plan_detail_id
from gmd_storage_plan_details
where storage_plan_id = p_storage_plan_id;
select gmd_qc_ss_variant_id_s.nextval
into l_variant_id
from dual;
insert into gmd_ss_variants
(variant_id,
variant_no,
ss_id,
material_source_id,
storage_plan_detail_id,
default_spec_id,
scheduled_start_date,
samples_per_time_point,
retained_samples,
storage_spec_id,
resources,
storage_organization_id, --INVCONV
storage_subinventory, --INVCONV
storage_locator_id, --INVCONV
storage_date,
delete_mark,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login)
values (l_variant_id,
l_variant_no,
p_ss_id,
p_material_source_id,
l_storage_plan.storage_plan_detail_id,
p_base_spec_id,
p_scheduled_start_date,
1,
0,
l_storage_plan.storage_spec_id,
l_storage_plan.resources,
l_storage_plan.storage_organization_id, --INVCONV
l_storage_plan.storage_subinventory, --INVCONV
l_storage_plan.storage_locator_id, --INVCONV
p_scheduled_start_date,
0,
sysdate,
p_created_by,
p_created_by,
sysdate,
p_created_by);
select period_id, name ,
years_from_start ,
months_from_start ,
weeks_from_start ,
days_from_start ,
hours_from_start
from gmd_test_interval_plan_periods
where test_interval_plan_id = p_test_interval_plan_id
and nvl(exclude,'N') = 'N'
order by simulated_date;
select a.test_interval_plan_id
into l_test_interval_plan_id
from gmd_storage_plan_details a,
gmd_ss_variants b
where a.storage_plan_detail_id = b.storage_plan_detail_id
and b.variant_id = p_variant_id
;
insert into gmd_ss_time_points
(
time_point_id,
name,
variant_id,
period_id ,
years_from_start,
months_from_start,
weeks_from_start,
days_from_start,
hours_from_start,
additional_ind,
spec_id,
scheduled_date,
--actual_date,
samples_per_time_point,
delete_mark,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login)
values
(gmd_qc_ss_time_point_id_s.nextval,
l_time_interval_plan.name,
p_variant_id,
l_time_interval_plan.period_id,
l_time_interval_plan.years_from_start,
l_time_interval_plan.months_from_start,
l_time_interval_plan.weeks_from_start,
l_time_interval_plan.days_from_start ,
l_time_interval_plan.hours_from_start,
'N',
p_base_spec_id,
ADD_MONTHS(p_scheduled_start_date,
((NVL(l_time_interval_plan.years_from_start,0) * 12 )
+ NVL(l_time_interval_plan.months_from_start,0)))
+((NVL(l_time_interval_plan.weeks_from_start,0) * 7 )
+ NVL(l_time_interval_plan.days_from_start,0)
+ (NVL(l_time_interval_plan.hours_from_start,0)/24)),
/* ADD_MONTHS(p_actual_date,
((NVL(l_time_interval_plan.years_from_start,0) * 12 )
+ NVL(l_time_interval_plan.months_from_start,0)))
+((NVL(l_time_interval_plan.weeks_from_start,0) * 7 )
+ NVL(l_time_interval_plan.days_from_start,0)
+ (NVL(l_time_interval_plan.hours_from_start,0)/24)),*/
p_samples_per_time_point,
0,
sysdate,
p_created_by,
p_created_by,
sysdate,
p_created_by);
PROCEDURE delete_variants
(p_material_source_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
Cursor c_ss_id is
select ss_id
from gmd_ss_variants
where material_source_id = p_material_source_id;
SELECT variant_id from gmd_ss_variants
where material_source_id = p_material_source_id
for update of variant_id NOWAIT;
SELECT time_point_id from gmd_ss_time_points
where variant_id in (select variant_id
from gmd_ss_variants
where material_source_id = p_material_source_id)
for update of time_point_id NOWAIT ;
delete gmd_ss_time_points
where variant_id in (select variant_id
from gmd_ss_variants
where material_source_id = p_material_source_id);
delete gmd_ss_variants
where material_source_id = p_material_source_id;
update_variant_seq(l_ss_id, l_return_status);
'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.DELETE_VARIANTS',
'ERROR', SUBSTR(SQLERRM,1,100));
end delete_variants;
PROCEDURE delete_time_points
(p_variant_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
Cursor c_ss_id is
select ss_id
from gmd_ss_material_sources
where ss_id = p_variant_id;
SELECT time_point_id from gmd_ss_time_points
where variant_id = p_variant_id
for update of time_point_id NOWAIT ;
delete gmd_ss_time_points
where variant_id = p_variant_id;
'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.DELETE_TIME_POINTS',
'ERROR', SUBSTR(SQLERRM,1,100));
end delete_time_points;
select nvl(max(a.variant_no),0)
into l_variant_num
from gmd_ss_variants a
where a.ss_id = p_ss_id
;
PROCEDURE update_variant_seq
(ss_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) is
cursor c_variant_no (p_ss_id NUMBER) is
select variant_id,variant_no, rownum
from gmd_ss_variants
where ss_id = p_ss_id
order by variant_no;
update gmd_ss_variants
set variant_no = l_variant_no.rownum
where variant_id = l_variant_no.variant_id;
'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_VARIANT_SEQ',
'ERROR', SUBSTR(SQLERRM,1,100));
end update_variant_seq;
PROCEDURE update_base_spec( p_ss_id IN NUMBER,
p_base_spec_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 ) IS
-- mahesh.
CURSOR cr_variants IS
SELECT default_spec_id from gmd_ss_variants
where ss_id = p_ss_id
for update of default_spec_id NOWAIT;
SELECT spec_id from gmd_ss_time_points
where variant_id in (select variant_id
from gmd_ss_variants
where ss_id = p_ss_id)
for update of spec_id NOWAIT ;
UPDATE gmd_ss_variants
SET default_spec_id = p_base_spec_id
WHERE ss_id = p_ss_id ;
UPDATE gmd_ss_time_points
SET spec_id = p_base_spec_id
WHERE variant_id in (select variant_id from gmd_ss_variants
where ss_id = p_ss_id) ;
gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_BASE_SPEC');
'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_BASE_SPEC','ERROR', SUBSTR(SQLERRM,1,100));
END update_base_spec ;
PROCEDURE update_scheduled_start_date
(p_ss_id IN NUMBER,
p_scheduled_start_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2 ) IS
-- mahesh.
CURSOR cr_variants IS
SELECT scheduled_start_date from gmd_ss_variants
where ss_id = p_ss_id
for update of scheduled_start_date NOWAIT;
SELECT spec_id from gmd_ss_time_points
where variant_id in (select variant_id
from gmd_ss_variants
where ss_id = p_ss_id)
for update of spec_id NOWAIT ;
UPDATE gmd_ss_variants
SET scheduled_start_date = p_scheduled_start_date
WHERE ss_id = p_ss_id ;
UPDATE gmd_ss_time_points
SET scheduled_date = ADD_MONTHS(p_scheduled_start_date,((nvl(years_from_start,0) * 12 ) + nvl(months_from_start,0))) +
((nvl(weeks_from_start,0) * 7 ) + nvl(days_from_start,0) + (nvl(hours_from_start,0)/24))
WHERE variant_id in (select variant_id from gmd_ss_variants
where ss_id = p_ss_id) ;
gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_SCHEDULED_START_DATE');
'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_SCHEDULED_START_DATE','ERROR', SUBSTR(SQLERRM,1,100));
END update_scheduled_start_date;