[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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.SAMPLE_NO,A.SAMPLE_DESC,A.SAMPLING_EVENT_ID,A.REVISION,B.CONCATENATED_SEGMENTS,B.DESCRIPTION,
A.LOT_NUMBER,GES.DISPOSITION,A.SOURCE,D.SPEC_NAME||' / '||to_char(D.SPEC_VERS),
D.SPEC_VERS,D.GRADE_CODE, mp.organization_code
INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
L_LOT_NO,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
FROM gmd_samples a,mtl_system_items_kfv b,
gmd_sampling_events c,gmd_all_spec_vrs_vl d ,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_sample_spec_disp gss,
mtl_parameters mp
WHERE
A.sample_id=l_event_key AND
a.inventory_item_id=b.inventory_item_id AND
a.sampling_event_id=c.sampling_event_id AND
c.original_spec_vr_id=d.spec_vr_id AND
a.sampling_event_id = gse.sampling_event_id AND
gse.sampling_event_id = ges.sampling_event_id AND
ges.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND
ges.event_Spec_disp_id = gss.event_spec_disp_id AND
gss.sample_id = a.sample_id AND
ges.delete_mark = 0 AND
mp.organization_id = a.organization_id AND
b.organization_id = a.organization_id; --RLNAGARA added this condition as item is always bind to Organization in R12*/
/*SELECT A.SAMPLE_NO,A.SAMPLE_DESC,A.SAMPLING_EVENT_ID,A.REVISION,B.CONCATENATED_SEGMENTS,B.DESCRIPTION,
A.LOT_NUMBER,GES.DISPOSITION,A.SOURCE,E.SPEC_NAME||' / '||to_char(E.SPEC_VERS),
E.SPEC_VERS,E.GRADE_CODE, mp.organization_code
INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
L_LOT_NO,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
FROM gmd_samples a,
mtl_system_items_kfv b,
gmd_sampling_events c,
gmd_com_spec_vrs_vl d ,
gmd_specifications e,
gmd_sampling_events gse,
gmd_event_spec_disp ges,
gmd_sample_spec_disp gss,
mtl_parameters mp
WHERE
A.sample_id=l_event_key AND
a.inventory_item_id=b.inventory_item_id AND
a.sampling_event_id=c.sampling_event_id AND
c.original_spec_vr_id=d.spec_vr_id AND
d.spec_id = e.spec_id AND
a.sampling_event_id = gse.sampling_event_id AND
gse.sampling_event_id = ges.sampling_event_id AND
ges.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND
ges.event_Spec_disp_id = gss.event_spec_disp_id AND
gss.sample_id = a.sample_id AND
ges.delete_mark = 0 AND
mp.organization_id = a.organization_id AND
b.organization_id = a.organization_id;*/
SELECT
a.sample_no,a.sample_desc,a.sampling_event_id,a.revision,b.concatenated_segments,b.description,
a.lot_number,a.lpn_id,ges.disposition,a.source,e.spec_name||' / '||to_char(e.spec_vers),
e.spec_vers,e.grade_code, mp.organization_code
INTO L_SAMPLE_NO,L_SAMPLE_DESC,l_sampling_Event_id,L_ITEM_REVISION,L_ITEM_NO,L_ITEM_DESC,
L_LOT_NO,l_lpn_id,L_SAMPLE_DISPOSITION,L_SAMPLE_SOURCE,L_SPECIFICATION,
L_SPEC_VERS, L_GRADE_CODE, L_ORGN_CODE
FROM
gmd_samples a,
mtl_system_items_kfv b,
gmd_specifications_b e,
gmd_sampling_events gse,
(SELECT ges.sampling_event_id , ges.disposition ,ges.spec_vr_id,ges.spec_id
FROM gmd_event_spec_disp ges, gmd_sample_spec_disp gss
WHERE spec_used_for_lot_attrib_ind = 'Y'
AND ges.event_spec_disp_id = gss.event_spec_disp_id
AND ges.delete_mark = 0
AND gss.sample_id = l_event_key ) ges,
mtl_parameters mp
WHERE a.sample_id = l_event_key AND
a.inventory_item_id = b.inventory_item_id AND
ges.spec_id = e.spec_id AND
a.sampling_event_id = gse.sampling_event_id AND
gse.sampling_event_id = ges.sampling_event_id AND
mp.organization_id = a.organization_id AND
b.organization_id = a.organization_id;
SELECT meaning INTO l_sample_DISPOSITION FROM
gem_lookups WHERE LOOKUP_TYPE='GMD_QC_SAMPLE_DISP'
AND lookup_code=l_sample_disposition;
SELECT SAMPLE_ACTIVE_CNT INTO l_sample_event_count
FROM gmd_Sampling_events
WHERE sampling_event_id = l_event_key ;
SELECT s.sample_id INTO l_sample_id
FROM gmd_samples s,
gmd_event_spec_disp esd,
gmd_sample_spec_disp ssd
WHERE esd.sampling_event_id = l_event_key
AND esd.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND esd.sampling_event_id = s.sampling_event_id
AND esd. EVENT_SPEC_DISP_ID = ssd.EVENT_SPEC_DISP_ID
AND ssd.disposition NOT IN ('0RT', '7CN') ;
/*SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,
A.SOURCE,SPEC_NAME||' / '||to_char(SPEC_VERS),
d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,b.revision,b.organization_code
INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,
L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN,l_item_revision,l_orgn_code
FROM GMD_SAMPLING_EVENTS A
,GMD_ALL_SPEC_VRS_VL B
,MTL_SYSTEM_ITEMS_KFV C
,GMD_SAMPLING_PLANS D
WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
b.inventory_item_id=c.inventory_item_id AND
a.sampling_plan_id=d.sampling_plan_id(+) AND
a.sampling_event_id=l_event_key;*/
/*SELECT c.CONCATENATED_SEGMENTS, c.DESCRIPTION, A.LOT_NUMBER,
A.SOURCE, e.SPEC_NAME||' / '||to_char(e.SPEC_VERS),
d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,e.revision, f.organization_code
INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,
L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN,l_item_revision,l_orgn_code
FROM GMD_SAMPLING_EVENTS A
,GMD_COM_SPEC_VRS_VL B
,MTL_SYSTEM_ITEMS_B_KFV C
,GMD_SAMPLING_PLANS D
,GMD_SPECIFICATIONS_B E
,MTL_PARAMETERS F
WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
b.spec_id = e.spec_id AND
e.inventory_item_id=c.inventory_item_id AND
b.organization_id = f.organization_id(+) AND
a.sampling_plan_id=d.sampling_plan_id(+) AND
a.sampling_event_id= l_event_key;*/
select original_spec_vr_id, sampling_plan_id
into l_spec_vr_id, l_sampling_plan_id
from gmd_sampling_events
where sampling_event_id = l_event_key;
select a.sampling_plan_name || ' / ' || b.sampling_plan_desc
into l_sample_plan
from gmd_sampling_plans_b a, gmd_sampling_plans_tl b
where a.sampling_plan_id = b.sampling_plan_id
and a.sampling_plan_id = l_sampling_plan_id
and b.language = userenv('LANG');
select c.concatenated_segments, c.description, a.lot_number,a.lpn_id,
a.source, e.spec_name||' / '||to_char(e.spec_vers),
e.revision, f.organization_code
INTO l_item_no,l_item_desc,l_lot_no,l_lpn_id,
l_sample_source,l_specification,l_item_revision,l_orgn_code
from gmd_sampling_events a
,gmd_com_spec_vrs_vl b
,mtl_system_items_b_kfv c
,gmd_specifications_b e
,mtl_parameters f
where a.original_spec_vr_id = b.spec_vr_id and
b.spec_id = e.spec_id and
e.inventory_item_id = c.inventory_item_id and
e.owner_organization_id = c.organization_id and --RLNAGARA B5714223 Added this condition
b.organization_id = f.organization_id(+) and
a.sampling_event_id = l_event_key and
b.spec_vr_id = l_spec_vr_id;
/*SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,A.SOURCE,SPEC_NAME||' / '||to_char(SPEC_VERS),
d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,b.revision,b.organization_code
INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN ,l_item_revision,l_orgn_code
FROM GMD_SAMPLING_EVENTS A
,GMD_ALL_SPEC_VRS_VL B
,MTL_SYSTEM_ITEMS_KFV C
,GMD_SAMPLING_PLANS D
WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
b.inventory_item_id=c.inventory_item_id AND
a.sampling_plan_id=d.sampling_plan_id(+) AND
a.sampling_event_id=l_event_key;*/
/* SELECT c.CONCATENATED_SEGMENTS,c.DESCRIPTION,A.LOT_NUMBER,A.SOURCE,E.SPEC_NAME||' / '||to_char(E.SPEC_VERS),
d.SAMPLING_PLAN_NAME||' / '||d.SAMPLING_PLAN_DESC ,e.revision,f.organization_code
INTO L_ITEM_NO,L_ITEM_DESC,L_LOT_NO,L_SAMPLE_SOURCE,L_SPECIFICATION,L_SAMPLE_PLAN ,l_item_revision,l_orgn_code
FROM GMD_SAMPLING_EVENTS A
,GMD_COM_SPEC_VRS_VL B
,MTL_SYSTEM_ITEMS_B_KFV C
,GMD_SAMPLING_PLANS D
,GMD_SPECIFICATIONS_B E
,MTL_PARAMETERS F
WHERE a.original_spec_vr_id=b.SPEC_VR_ID AND
b.spec_id = e.spec_id AND
e.inventory_item_id=c.inventory_item_id AND
b.organization_id = f.organization_id(+) AND
a.sampling_plan_id=d.sampling_plan_id(+) AND
a.sampling_event_id= l_event_key; */
select original_spec_vr_id, sampling_plan_id
into l_spec_vr_id, l_sampling_plan_id
from gmd_sampling_events
where sampling_event_id = l_event_key;
select a.sampling_plan_name || ' / ' || b.sampling_plan_desc
into l_sample_plan
from gmd_sampling_plans_b a, gmd_sampling_plans_tl b
where a.sampling_plan_id = b.sampling_plan_id
and a.sampling_plan_id = l_sampling_plan_id
and b.language = userenv('LANG');
select c.concatenated_segments, c.description, a.lot_number,a.lpn_id,a.source, e.spec_name||' / '||to_char(e.spec_vers),
e.revision, f.organization_code
INTO l_item_no,l_item_desc,l_lot_no,l_lpn_id,l_sample_source,l_specification,
l_item_revision,l_orgn_code
from gmd_sampling_events a
,gmd_com_spec_vrs_vl b
,mtl_system_items_b_kfv c
,gmd_specifications_b e
,mtl_parameters f
where a.original_spec_vr_id = b.spec_vr_id and
b.spec_id = e.spec_id and
e.inventory_item_id = c.inventory_item_id and
e.owner_organization_id = c.organization_id and --RLNAGARA B5714223 Added this condition
b.organization_id = f.organization_id(+) and
a.sampling_event_id = l_event_key and
b.spec_vr_id = l_spec_vr_id;
SELECT meaning INTO l_sample_source FROM
gem_lookups WHERE LOOKUP_TYPE='GMD_QC_SOURCE'
AND lookup_code=l_sample_source;
select license_plate_number INTO l_lpn
from wms_license_plate_numbers
where lpn_id = l_lpn_id;
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 a,per_all_people b
where b.person_id=Approver.person_id and
a.employee_id is not null and
a.employee_id = b.person_id; */
/*select user_name into l_user from fnd_user a
where a.employee_id = Approver.person_id
and a.employee_id is not null
and exists (select 1 from per_all_people where person_id = Approver.person_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);
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 a,per_all_people b
where
b.person_id=Approver.person_id and
a.employee_id is not null and
a.employee_id = b.person_id; */
/*select user_name into l_user from fnd_user a
where a.employee_id = Approver.person_id
and a.employee_id is not null
and exists (select 1 from per_all_people where person_id = Approver.person_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);