[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT owner_role
INTO x_item_owner_role
FROM WF_ITEMS
WHERE item_type = itemtype
AND item_key = itemkey ;
/* Fix for bug 5215778- Added task_id, project_id to the select statement.
Fix for bug 5200489- In the where clause, replaced person_id with party_id in the join between eec and mev */
SELECT eec.description,
(SELECT meaning FROM mfg_lookups WHERE eec.status_type = lookup_code
AND lookup_type = 'ECG_ECN_STATUS'), /* eco status */
eec.initiation_date,
eec.priority_code,
eec.reason_code,
eec.estimated_eng_cost,
eec.estimated_mfg_cost,
eec.attribute_category,
eec.attribute1,
eec.attribute2,
eec.attribute3,
eec.attribute4,
eec.attribute5,
eec.attribute6,
eec.attribute7,
eec.attribute8,
eec.attribute9,
eec.attribute10,
eec.attribute11,
eec.attribute12,
eec.attribute13,
eec.attribute14,
eec.attribute15,
(SELECT meaning FROM mfg_lookups WHERE eec.approval_status_type =
lookup_code
AND lookup_type = 'ENG_ECN_APPROVAL_STATUS'), /*approval status*/
(SELECT organization_code FROM mtl_parameters WHERE
organization_id=eec.organization_id),
(SELECT NAME FROM hr_all_organization_units WHERE
organization_id=eec.organization_id),
mev.full_name,
ecot.type_name,
(SELECT NAME FROM hr_all_organization_units WHERE
organization_id=eec.responsible_organization_id),
(SELECT organization_code FROM mtl_parameters WHERE
organization_id=eec.responsible_organization_id),
eec.task_id,
eec.project_id
INTO X_description,
X_eco_status,
X_initiation_date,
X_priority_code,
X_reason_code,
X_estimated_eng_cost,
X_estimated_mfg_cost,
X_attribute_category,
X_attribute1,
X_attribute2,
X_attribute3,
X_attribute4,
X_attribute5,
X_attribute6,
X_attribute7,
X_attribute8,
X_attribute9,
X_attribute10,
X_attribute11,
X_attribute12,
X_attribute13,
X_attribute14,
X_attribute15,
X_approval_status,
X_org_code,
X_org_name,
X_requestor,
X_change_type,
X_eco_dept_name,
X_eco_dept_code,
X_task_id,
X_project_id
FROM per_people_f mev,
eng_change_order_types_vl ecot,
eng_engineering_changes eec
WHERE eec.organization_id = X_org_id
AND eec.change_notice = X_change_notice
AND eec.requestor_id = mev.party_id(+)--mev.person_id(+) -- Bug 4644000
AND eec.change_order_type_id = ecot.change_order_type_id
AND rownum = 1;
SELECT r.name
INTO X_approval_list_name
FROM wf_roles r, eng_engineering_changes eec, eng_ecn_approval_lists al
WHERE eec.approval_list_id = al.approval_list_id
AND eec.organization_id = X_org_id
AND eec.change_notice = X_change_notice
AND al.approval_list_name = r.display_name ;
select r.name
INTO X_approval_list_name
from wf_local_roles r
where r.display_name in (SELECT al.approval_list_name
FROM eng_ecn_approval_lists al, eng_engineering_changes eec
WHERE eec.approval_list_id = al.approval_list_id
AND eec.organization_id = X_org_id
AND eec.change_notice = X_change_notice) ;
This procedure updates an ECO's Approval Status to "Approved".
If the user updates the status to "Approved" then the Approval Date gets
updated to today's date.
************************************************************************ */
PROCEDURE Approve_Eco( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result IN OUT NOCOPY VARCHAR2)
IS
X_eco_result VARCHAR2(2000);
UPDATE eng_engineering_changes
SET approval_status_type = 5,
approval_date = sysdate
WHERE organization_id = X_org_id
AND change_notice = X_change_notice;
UPDATE eng_revised_items
SET status_type = 4 /* Set Rev Item Status = Scheduled */
,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
WHERE change_notice = X_change_notice
AND organization_id = X_org_id
AND status_type = 1; /* Rev Item Status = Open */
UPDATE eng_engineering_changes
SET status_type = 4
,status_code = 4 --Bug 3526627: Changes for 11.5.10, set the status_code also
WHERE change_notice = X_change_notice
AND organization_id = X_org_id
AND status_type = 1;
This procedure updates an ECO's Approval Status to "Rejected".
The Approval Date is set to null.
************************************************************************ */
PROCEDURE Reject_Eco( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result IN OUT NOCOPY VARCHAR2)
IS
X_eco_result VARCHAR2(2000);
UPDATE eng_engineering_changes
SET approval_status_type = 4,
approval_date = null
WHERE organization_id = X_org_id
AND change_notice = X_change_notice;
UPDATE eng_engineering_changes
SET approval_status_type = 7,
approval_date = ''
WHERE organization_id = X_org_id
AND change_notice = X_change_notice;
This procedure updates the MRP Active flag to 'Yes' for all the revised
items for a given ECO only if the revised item is at Status 'Open' or
'Scheduled'.
************************************************************************ */
PROCEDURE Set_Mrp_Active( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result IN OUT NOCOPY VARCHAR2)
IS
X_eco_result VARCHAR2(2000);
select revised_item_sequence_id from eng_revised_items
where change_notice = c_change_notice
and organization_id = c_org_id;
select eri1.revised_item_sequence_id
from eng_revised_items eri, eng_revised_items eri1
where eri.change_notice = c_change_notice
AND eri.organization_id = c_org_id
AND eri.revised_item_sequence_id = c_rev_item_seq_id
AND eri1.organization_id = c_org_id
AND eri1.revised_item_sequence_id <> eri.revised_item_sequence_id
AND eri1.revised_item_id = eri.revised_item_id
AND eri1.scheduled_date = eri.scheduled_date
AND eri1.mrp_active = 1
AND eri1.status_type <> 5;
Select bill_sequence_id, component_item_id from bom_components_b
where revised_item_sequence_id = c_rev_item_seq_id;
Select bill_sequence_id, component_item_id from bom_components_b
where revised_item_sequence_id = c_rev_item_seq_id;
SELECT COUNT(*)
INTO l_duplicate
FROM eng_revised_items eri,
eng_revised_items eri1
WHERE eri.change_notice = X_change_notice
AND eri.organization_id = X_org_id
AND eri1.organization_id = X_org_id
AND eri1.revised_item_sequence_id <> eri.revised_item_sequence_id
AND eri1.revised_item_id = eri.revised_item_id
AND eri1.scheduled_date = eri.scheduled_date
AND eri1.mrp_active = 1
AND eri1.status_type <> 5; -- Not Cancelled
Select count(*) into num_comp_rev_item from bom_components_b
where revised_item_sequence_id = l_rev_seq_id;
Select count(*) into num_comp_other_rev_item from bom_components_b
where revised_item_sequence_id = other_rev_seq_id;
UPDATE eng_revised_items
SET mrp_active = 1 /* Set MRP Active=Yes */
WHERE change_notice = X_change_notice
AND organization_id = X_org_id
AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
UPDATE eng_revised_items
SET mrp_active = 1 /* Set MRP Active=Yes */
WHERE change_notice = X_change_notice
AND organization_id = X_org_id
AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
This procedure updates the MRP Active flag to 'No' for all the revised
items for a given ECO only if the revised item is at Status 'Open' or
'Scheduled'.
************************************************************************ */
PROCEDURE Set_Mrp_Inactive( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result IN OUT NOCOPY VARCHAR2)
IS
X_eco_result VARCHAR2(2000);
UPDATE eng_revised_items
SET mrp_active = 2 /* Set MRP Active=Yes */
WHERE change_notice = X_change_notice
AND organization_id = X_org_id
AND status_type in (1, 4); /* Rev Item Status=Open or Scheduled */
PROCEDURE UPDATE_EVIDENCE (p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_resultout OUT NOCOPY VARCHAR2
) IS
l_requester varchar2(240);
SELECT NOTIFICATION_ID
INTO l_nid
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_KEY = p_itemkey
AND ITEM_TYPE = p_itemtype
AND NOTIFICATION_ID IS NOT NULL;
p_resultout := 'COMPLETE:UPDATE_EVIDENCE';
WF_CORE.CONTEXT ('ENG_WORKFLOW_API_PKG','UPDATE_EVIDENCE',
p_itemtype,p_itemkey,SQLERRM);
END UPDATE_EVIDENCE;
SELECT
eec.change_id
INTO
X_change_id
FROM eng_engineering_changes eec
WHERE eec.organization_id = X_org_id
AND eec.change_notice = X_change_notice;
select wfl.lookup_code result_code
from wf_lookups wfl,
wf_activities wfa,
wf_process_activities wfpa,
wf_items wfi where wfl.lookup_type = wfa.result_type
and wfa.name = wfpa.activity_name
and wfi.begin_date >= wfa.begin_date
and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
and wfpa.activity_item_type = wfa.item_type
and wfpa.instance_id = actid
and wfi.item_key = itemkey
and wfi.item_type = itemtype;
UPDATE_EVIDENCE (p_itemtype =>itemtype,
p_itemkey =>itemkey,
p_actid =>actid,
p_funcmode => funcmode,
p_resultout => l_resultout);