[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select i.transaction_id,
i.transaction_type,
i.inventory_item_id,
i.organization_id ,
i.revision,
rowid
from mtl_item_revisions_interface i
where i.process_flag = 2
and i.set_process_id = xset_id
and (i.organization_id = org_id or all_org = 1)
and not exists (select 'X'
from mtl_system_items m
where m.organization_id = i.organization_id
and m.inventory_item_id = i.inventory_item_id)
and not exists (select 'X'
from mtl_system_items_interface mi
where mi.organization_id = i.organization_id
and mi.inventory_item_id = i.inventory_item_id
and process_flag = 4);
select transaction_id,
transaction_type,
organization_id,
inventory_item_id,
revision,
revision_id,
rowid
from mtl_item_revisions_interface
where set_process_id = xset_id
and process_flag = 2;
select i.rowid,
i.organization_id,
i.inventory_item_id,
i.transaction_id,
i.created_by
from mtl_item_revisions_interface i
where i.set_process_id = xset_id
and i.process_flag = 2
and (i.transaction_type = 'UPDATE'
or exists (select null
from mtl_system_items_b m
where m.organization_id = i.organization_id
and m.inventory_item_id = i.inventory_item_id));
select m.transaction_id,
m.transaction_type,
m.organization_id,
m.inventory_item_id,
m.revision,
m.rowid
from mtl_item_revisions_interface m
where m.set_process_id = xset_id
and m.process_flag = 2;
select m.transaction_id,
m.transaction_type,
m.organization_id,
m.inventory_item_id,
m.revision,
m.revision_id,
m.effectivity_date,
m.ecn_initiation_date,
m.implementation_date,
m.revision_reason
from mtl_item_revisions_interface m
where m.set_process_id = xset_id
and m.process_flag = 2;
select i.transaction_id,
i.organization_id,
i.rowid
from mtl_item_revisions_b m,
mtl_item_revisions_interface i
where m.organization_id = i.organization_id
and i.set_process_id = xset_id
and m.inventory_item_id = i.inventory_item_id
and ( (m.revision < i.revision and m.effectivity_date >=
i.effectivity_date)
or (i.revision < m.revision and i.effectivity_date >=
m.effectivity_date)
)
and i.process_flag = 2;
SELECT rowid,
inventory_item_id,
organization_id,
lifecycle_id,
current_phase_id,
transaction_id,
transaction_type,
revision,
revision_id
FROM mtl_item_revisions_interface i
WHERE set_process_id = xset_id
AND process_flag = 2
FOR UPDATE OF current_phase_id NOWAIT;
--Changes added lifecycle,phase ids in the select list.
CURSOR c_get_item_ids(cp_org_id NUMBER,
cp_item_id NUMBER)
IS
SELECT mi.item_catalog_group_id
,mi.lifecycle_id
,mi.current_phase_id
,'U'
,mi.transaction_type
FROM mtl_system_items_interface mi
WHERE mi.organization_id = cp_org_id
AND mi.inventory_item_id = cp_item_id
AND mi.process_flag = 4
UNION
SELECT m.item_catalog_group_id
,m.lifecycle_id
,m.current_phase_id
,NVL(m.approval_status,'A')
,'EXISTS'
FROM mtl_system_items_b m
WHERE m.organization_id = cp_org_id
AND m.inventory_item_id = cp_item_id;
SELECT 'Y'
FROM fnd_lookup_values_vl
WHERE lookup_type = cp_type
AND lookup_code = cp_code
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
AND enabled_flag = 'Y';
select starting_revision
from mtl_parameters
where organization_id = cp_org_id;
SELECT 1 FROM ego_import_option_sets
WHERE batch_id = cp_xset_id
AND enabled_for_data_pool = 'Y';
--2808277: Update validations for Lifecycle-Phase
l_row_count NUMBER(3) := 0;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid ;
SELECT MTL_ITEM_REVISIONS_B_S.CURRVAL
INTO l_revision_id FROM DUAL;
SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL
INTO l_revision_id FROM DUAL;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
ELSIF cr.transaction_type ='UPDATE'
AND cr.revision IS NULL
AND cr.revision_id IS NULL THEN
l_revid_error := TRUE;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
SELECT count(1) INTO l_row_count
FROM mtl_item_revisions_b
WHERE organization_id = cr.organization_id
AND inventory_item_id = cr.inventory_item_id
AND (revision = cr.revision OR revision_id = cr.revision_id);
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
ELSIF cr.transaction_type ='UPDATE' AND l_row_count = 0 THEN
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
'INV_IOI_ITEMREV_UPDATE_PRIV',
err_text);
select count(*)
into temp_count
from mtl_item_revisions_interface i
where i.organization_id = cr.organization_id
and i.inventory_item_id = cr.inventory_item_id
and i.set_process_id + 0 = xset_id
and i.revision = cr.revision
--and i.transaction_id = cr.transaction_id --2808277 Removed for bug 5458317
and i.process_flag = 2;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where transaction_id = cr.transaction_id
and set_process_id = xset_id
and revision = cr.revision;
UPDATE mtl_system_items_interface
SET process_flag = l_process_flag_3
WHERE inventory_item_id = cr.inventory_item_id
AND organization_id = cr.organization_id
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_3
WHERE rowid = cr.rowid;
UPDATE mtl_item_revisions_interface
SET process_flag = l_process_flag_3
WHERE rowid = cr.rowid;
-- Cannot update past/current effective dates.
IF (cr.transaction_type='UPDATE') THEN
l_row_count:= 0;
SELECT count(1) INTO l_row_count
FROM mtl_item_revisions_b
WHERE revision_id = cr.revision_id
AND TRUNC(effectivity_date)<= TRUNC(sysdate)
AND TRUNC(effectivity_date) <> TRUNC(cr.effectivity_date);
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where transaction_id = cr.transaction_id
and set_process_id = xset_id
and revision = cr.revision;--Bug: 2593490
SELECT COUNT(1) INTO l_row_count
FROM mtl_item_revisions_b
where revision_id = cr.revision_id
AND change_notice IS NOT NULL
AND ((cr.ecn_initiation_date is NULL OR ecn_initiation_date <> cr.ecn_initiation_date)
OR (effectivity_date <> cr.effectivity_date)
OR ((implementation_date IS NULL AND cr.implementation_date IS NOT NULL)
OR(implementation_date <> cr.implementation_date)));
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where transaction_id = cr.transaction_id
and set_process_id = xset_id
and revision = cr.revision;--Bug: 2593490
select count(*)
into temp_count
from mtl_item_revisions_interface i
where i.organization_id = cr.organization_id
and i.inventory_item_id = cr.inventory_item_id
and i.set_process_id + 0 = xset_id
--and i.transaction_id = cr.transaction_id Commented for bug 5458317
and ((i.revision < cr.revision)
AND ((TRUNC(i.effectivity_date) = TRUNC(SYSDATE)
AND TRUNC(cr.effectivity_date) = TRUNC(SYSDATE)
AND i.effectivity_date = cr.effectivity_date)
OR(i.effectivity_date >= cr.effectivity_date)))
--2861248 : Effective date validation changed
--3569925 : Added = condition for > on effectivity dates.
/**Bug: 2593490 No need to check with greater revisions
or
( cr.revision < i.revision and cr.effectivity_date > i.effectivity_date)
***/
and i.process_flag = 2 ;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where transaction_id = cr.transaction_id
and set_process_id = xset_id
and revision = cr.revision;--Bug: 2593490
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where transaction_id = cr.transaction_id
and set_process_id = xset_id
and revision = cr.revision;
UPDATE mtl_system_items_interface
SET process_flag = l_process_flag_3
WHERE inventory_item_id = cr.inventory_item_id
AND organization_id = cr.organization_id
AND set_process_id = xset_id;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
--2808277: Start Revision update changes
IF cr.transaction_type = 'UPDATE' THEN
SELECT current_phase_id
INTO l_Old_Phase_Id
FROM mtl_item_revisions_b
WHERE revision_id = cr.revision_id;
--2808277: End Revision update changes
-- Bug: 3769153 -- added OR part
IF cr.lifecycle_id IS NOT NULL OR cr.current_phase_id IS NOT NULL THEN
OPEN c_get_item_ids(cp_org_id => cr.organization_id,
cp_item_id => cr.inventory_item_id);
update mtl_item_revisions_interface
set lifecycle_id = l_item_lifecycle_id
where rowid = cr.rowid;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
IF (cr.transaction_type = 'UPDATE')
AND (cr.current_phase_id <> l_Old_Phase_Id) THEN
--EGO Phase change policy called through INV wrapper.
INV_EGO_REVISION_VALIDATE.phase_change_policy
(P_ORGANIZATION_ID => cr.organization_id
,P_INVENTORY_ITEM_ID => cr.inventory_item_id
,P_CURR_PHASE_ID => l_item_phase_id
,P_FUTURE_PHASE_ID => cr.current_phase_id
,P_PHASE_CHANGE_CODE => NULL
,P_LIFECYCLE_ID => cr.lifecycle_id
,X_POLICY_CODE => l_Policy_Code
,X_RETURN_STATUS => l_Return_Status
,X_ERRORCODE => l_Error_Code
,X_MSG_COUNT => l_Msg_Count
,X_MSG_DATA => l_Msg_Data);
update mtl_item_revisions_interface
set process_flag = l_process_flag_3
where rowid = cr.rowid;
UPDATE mtl_system_items_interface
SET process_flag = l_process_flag_3
WHERE inventory_item_id = cr.inventory_item_id
AND organization_id = cr.organization_id
AND set_process_id = xset_id;
update mtl_item_revisions_interface
set process_flag = l_process_flag_4,
revision_label = NVL(revision_label,revision),
revision_id = NVL(revision_id,MTL_ITEM_REVISIONS_B_S.NEXTVAL) --2808277
where process_flag = l_process_flag_2
and set_process_id = xset_id
and (organization_id = org_id or all_org = l_all_org);
INV_EGO_REVISION_VALIDATE.Insert_Revision_UserAttr(P_Set_id=>xset_id);