[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select sub.pk1_value,
sub.pk2_value
BULK COLLECT into l_items_array,
l_org_id_array
from eng_change_subjects sub ,
eng_change_lines lines
where lines.change_id = p_change_id
and lines.change_id = sub.change_id
and lines.change_line_id = sub.change_line_id
and lines.STATUS_CODE not in (G_ENG_NEW_ITEM_REJECTED, G_ENG_NEW_ITEM_CANCELLED)
and entity_name='EGO_ITEM';
/* Select sub.pk1_value,
sub.pk2_value
BULK COLLECT into l_items_array,
l_org_id_array
-- l_appr_statuses_array
from eng_change_subjects sub ,
eng_change_lines lines
where lines.change_id = p_change_id
and lines.change_id = sub.change_id
and lines.STATUS_CODE not in (5, 14)
and entity_name='EGO_ITEM';*/
EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_items_array(i),
l_org_id_array(i),
l_approval_status,
p_change_id);
UPDATE eng_change_lines
SET STATUS_CODE = G_ENG_NEW_ITEM_REJECTED
WHERE change_id = p_change_id
AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
UPDATE eng_change_lines
SET STATUS_CODE = G_ENG_NEW_ITEM_SFA
WHERE change_id = p_change_id
AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
UPDATE eng_change_lines
SET STATUS_CODE = G_ENG_NEW_ITEM_APPROVED
WHERE change_id = p_change_id
AND status_CODE not in(G_ENG_NEW_ITEM_REJECTED,G_ENG_NEW_ITEM_CANCELLED);
UPDATE_ITEM_APPROVAL_STATUS(l_items_array(i),
l_org_id_array(i),
l_approval_status); */
FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS '||'itemId: '||l_item_id||' OrgId: '||l_organization_id|| ' approvalStstus: '||l_approval_status);
SELECT PERSON_ID into l_party_id FROM EGO_PEOPLE_V WHERE USER_ID = p_wf_user_id;
SELECT PK1_VALUE, PK2_VALUE INTO l_inventory_item_id, l_organization_id
FROM ENG_CHANGE_SUBJECTS
WHERE ENTITY_NAME = 'EGO_ITEM' AND CHANGE_ID = p_change_id;
SELECT WF_ITEM_TYPE, WF_ITEM_KEY, WF_PROCESS_NAME
INTO l_wf_item_type, l_wf_item_key, l_wf_process_name
FROM ENG_CHANGE_ROUTES WHERE OBJECT_ID1 = p_change_id AND status_code = 'IN_PROGRESS';
UPDATE ENG_ENGINEERING_CHANGES
SET STATUS_TYPE = 5, STATUS_CODE = 5, CANCELLATION_DATE = sysdate, CANCELLATION_COMMENTS = p_cancel_comments
WHERE CHANGE_ID = p_change_id;
SELECT A.CHANGE_ID, B.CHANGE_NOTICE
FROM ENG_CHANGE_SUBJECTS A,
ENG_ENGINEERING_CHANGES B,
ENG_CHANGE_LINES LINES
WHERE A.PK1_VALUE = p_item_id
AND A.PK2_VALUE = p_org_id
AND A.ENTITY_NAME = 'EGO_ITEM'
AND A.CHANGE_LINE_ID = LINES.CHANGE_LINE_ID
AND LINES.CHANGE_ID = B.CHANGE_ID
AND A.CHANGE_ID = B.CHANGE_ID
AND LINES.STATUS_CODE NOT IN (G_ENG_NEW_ITEM_CANCELLED, G_ENG_NEW_ITEM_REJECTED);
SELECT PERSON_ID into l_party_id FROM EGO_PEOPLE_V WHERE USER_ID = p_wf_user_id;
select count(change_id) into l_uncancelled_change_id
from eng_change_lines where change_id = nirs.change_id
and status_code <> 5;
PROCEDURE Delete_Child_Associations(
p_parent_icc_id IN NUMBER,
p_item_catalog_group_ids IN VARCHAR2,
p_route_people_id IN NUMBER DEFAULT NULL,
p_attribute_group_id IN NUMBER DEFAULT NULL,
p_commit IN VARCHAR2
)
IS
l_delete_assocs_stmt VARCHAR2(32767);
SELECT assoc_obj_pk1_value, route_people_id, object_id1, To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM ENG_CHANGE_ROUTE_ASSOCS WHERE ASSOC_OBJ_PK1_VALUE = p_parent_icc_id;
SELECT To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') INTO l_parent_assoc_creation_date FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = p_parent_icc_id
AND ROUTE_PEOPLE_ID = p_route_people_id
AND OBJECT_ID1 = p_attribute_group_id;
DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
AND ASSOC_OBJ_PK1_VALUE <> p_parent_icc_id
AND ROUTE_PEOPLE_ID = p_route_people_id
AND OBJECT_ID1 = p_attribute_group_id
AND To_Char(CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') = l_parent_assoc_creation_date; -- Delete only if AG assoc is inherited : if inherited then the creation date will be same
DELETE FROM ENG_CHANGE_ROUTE_ASSOCS WHERE ROUTE_ASSOCIATION_ID IN (
SELECT ROUTE_ASSOCIATION_ID FROM (
SELECT a.route_association_id,
b.item_catalog_group_id,
b.parent_catalog_group_id,
b.NEW_ITEM_REQUEST_REQD
FROM eng_change_route_assocs a, mtl_item_catalog_groups_v b
WHERE a.assoc_obj_pk1_value = b.item_catalog_group_id
AND a.object_id1= rec.object_id1
AND To_Char(a.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') = rec.creation_date
) CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id AND NEW_ITEM_REQUEST_REQD = 'I'
START WITH item_catalog_group_id = rec.assoc_obj_pk1_value );
END Delete_Child_Associations;
p_assoc_last_update_date IN DATE DEFAULT NULL,
p_assoc_last_update_login IN NUMBER DEFAULT NULL,
p_assoc_last_updated_by IN NUMBER DEFAULT NULL,
p_commit IN VARCHAR2
)
IS
l_create_assocs_stmt VARCHAR2(32767);
SELECT
ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE, ASSOC_OBJ_PK2_VALUE, ASSOC_OBJ_PK3_VALUE, ASSOC_OBJ_PK4_VALUE,
ASSOC_OBJ_PK5_VALUE, ADHOC_ASSOC_FLAG, OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE, ORIGINAL_SYSTEM_REFERENCE
FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = l_source_icc_id;
SELECT count(route_association_id) INTO k
FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
AND ROUTE_PEOPLE_ID = ag_association.route_people_id
AND OBJECT_ID1 = ag_association.object_id1;
SELECT ENG_CHANGE_ROUTE_ASSOCS_S.NEXTVAL INTO l_route_association_id FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_ASSOCS (ROUTE_ASSOCIATION_ID, ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE,
ASSOC_OBJ_PK2_VALUE, ASSOC_OBJ_PK3_VALUE, ASSOC_OBJ_PK4_VALUE, ASSOC_OBJ_PK5_VALUE, ADHOC_ASSOC_FLAG,
OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE, ORIGINAL_SYSTEM_REFERENCE)
VALUES (l_route_association_id, ag_association.route_people_id, ag_association.assoc_object_name,
l_child_item_catalog_group_id, ag_association.assoc_obj_pk2_value, ag_association.assoc_obj_pk3_value,
ag_association.assoc_obj_pk4_value, ag_association.assoc_obj_pk5_value, 'N',
ag_association.object_name, ag_association.object_id1, ag_association.object_id2, ag_association.object_id3,
ag_association.object_id4, ag_association.object_id5, ag_association.creation_date, ag_association.created_by,
ag_association.last_update_date, ag_association.last_updated_by, ag_association.last_update_login,
ag_association.request_id, ag_association.program_id, ag_association.program_application_id,
ag_association.program_update_date, ag_association.original_system_reference);
SELECT count(route_association_id) INTO k
FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
AND ROUTE_PEOPLE_ID = p_route_people_id
AND OBJECT_ID1 = p_attribute_group_id;
SELECT ENG_CHANGE_ROUTE_ASSOCS_S.NEXTVAL INTO l_route_association_id FROM DUAL;
INSERT INTO ENG_CHANGE_ROUTE_ASSOCS (ROUTE_ASSOCIATION_ID, ROUTE_PEOPLE_ID, ASSOC_OBJECT_NAME, ASSOC_OBJ_PK1_VALUE,
ADHOC_ASSOC_FLAG, OBJECT_NAME, OBJECT_ID1, OBJECT_ID2, OBJECT_ID3, OBJECT_ID4, OBJECT_ID5,
CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
VALUES (l_route_association_id, p_route_people_id, 'EGO_CATALOG_GROUP', l_child_item_catalog_group_id, 'N',
'EGO_ITEM_ATTR_GROUP', p_attribute_group_id, 0,0,0,0, p_assoc_creation_date, p_assoc_created_by,
p_assoc_last_update_date, p_assoc_last_update_login, p_assoc_last_updated_by);
PROCEDURE Update_Child_Associations(
p_parent_item_catalog_group_id IN VARCHAR2,
p_child_item_catalog_group_ids IN VARCHAR2,
p_route_people_id IN NUMBER DEFAULT NULL,
p_attribute_group_id IN NUMBER DEFAULT NULL,
p_route_association_id IN NUMBER,
p_commit IN VARCHAR2
)
IS
l_old_attr_group_id NUMBER;
SELECT object_id1 INTO l_old_attr_group_id FROM ENG_CHANGE_ROUTE_ASSOCS WHERE route_association_id = p_route_association_id;
DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
AND ASSOC_OBJ_PK1_VALUE <> p_parent_item_catalog_group_id
AND ROUTE_PEOPLE_ID = p_route_people_id
AND OBJECT_ID1 = l_old_attr_group_id;
UPDATE ENG_CHANGE_ROUTE_ASSOCS
SET OBJECT_ID1 = p_attribute_group_id
WHERE ASSOC_OBJ_PK1_VALUE = l_child_item_catalog_group_id
AND ASSOC_OBJ_PK1_VALUE <> p_parent_item_catalog_group_id
AND ROUTE_PEOPLE_ID = p_route_people_id;
END Update_Child_Associations;
SELECT status_code INTO l_change_status_code FROM eng_engineering_changes WHERE change_id = p_change_id;
SELECT change_line_id INTO l_change_line_id FROM eng_change_subjects
WHERE change_id = p_change_id
AND pk1_value = p_item_id
AND pk2_value = p_org_id
AND entity_name = 'EGO_ITEM';
DELETE FROM eng_change_subjects WHERE change_id = p_change_id ;
DELETE FROM eng_change_lines WHERE change_id = p_change_id and change_line_id = l_change_line_id;
DELETE FROM eng_change_lines_tl WHERE change_line_id = l_change_line_id;
SELECT change_line_id INTO l_change_line_id FROM eng_change_subjects
WHERE change_id = p_change_id
AND pk1_value = p_item_id
AND pk2_value = p_org_id
AND entity_name = 'EGO_ITEM';
UPDATE ENG_CHANGE_LINES SET status_code = 5 -- Cancelled
WHERE change_id = p_change_id AND change_line_id = l_change_line_id;
PROCEDURE Update_Item_Approval_Status (
p_change_id IN NUMBER,
p_change_line_id IN NUMBER,
p_approval_status IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_item_id NUMBER;
SELECT to_number(pk1_value), to_number(pk2_value)
INTO l_item_id, l_organization_id
FROM eng_change_subjects
WHERE change_line_id = p_change_line_id
AND entity_name='EGO_ITEM';
/* call API to update approval status on the Item to approved*/
-- l_ret_item_app_st := EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_item_id,l_organization_id, l_approval_status);
EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS(l_item_id,l_organization_id, l_approval_status, p_change_id);
FND_MESSAGE.Set_Token('OBJECT_NAME', 'EGO_ITEM_PUB.UPDATE_ITEM_APPROVAL_STATUS '||'itemId: '||l_item_id||' OrgId: '||l_organization_id|| ' approvalStstus: '||l_approval_status || ' changeId: ' || p_change_id);
END Update_Item_Approval_Status;
select change_line_id
into l_change_line_id
from eng_change_lines
where change_id = p_change_id
and status_code in( 1,3)
and rownum=1;
This method will be called from ENG_CHANGE_LIFECYCLE_UTIL.Update_Header_Appr_Status() procedure
This method should be called when the Workflow is aborted in the Approval phase and when the NIR is demoted to Approval phase
All the Lines and Line Items status and approval status respectively should be reset to Open and Submitted for Approval respectively.
This method will reset the Approval Status of Items and Status of Lines which are Rejected using the NIR.
Spl Test Case : If there is another NIR with one of the line items and submitted since it is rejected in previous NIR,
and if the previous NIR Workflow is restarted, then the Line Status and Item Approval status will not be reset because it has to
be tracked with the other NIR.
Test Case :
SNIR_277 (Line1 - Item1)
Line is Rejected
NIR is Approved
SNIR_279 (Line1 - Item1) Submitted, Item - SFA
SNIR_277 Wf is Restarted
Result : Should not update the Item App Status and Line Status in SNIR_277. SNIR_279 should be used to track the Item1.
*/
PROCEDURE Update_Line_Items_App_St(
p_change_id IN NUMBER,
p_item_approval_status IN NUMBER,
x_sfa_line_items_exists OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_line_items_in_nir IS
SELECT change_line_id, pk1_value, pk2_value, pk3_value FROM ENG_CHANGE_SUBJECTS WHERE change_id = p_change_id
AND ENTITY_NAME='EGO_ITEM';
SELECT approval_status INTO l_item_approval_status FROM MTL_SYSTEM_ITEMS WHERE inventory_item_id = l_pk1_value AND organization_id = l_pk2_value;
Update_Item_Approval_Status(p_change_id, line_items.change_line_id, p_item_approval_status, l_ret_status);
UPDATE eng_change_lines SET STATUS_CODE = 1 WHERE change_line_id = l_change_line_id;
SELECT CONCATENATED_SEGMENTS INTO l_sfa_item
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = l_pk1_value
AND organization_id = l_pk2_value
AND LAST_SUBMITTED_NIR_ID <> p_change_id;
END Update_Line_Items_App_St;