APPS.INVPVALI dependencies on MTL_ITEM_REVISIONS_B

Line 145: -- Bug 4299292. Use base table mtl_item_revisions_b to fix performance issue

141: **effective date..ie an invalid condition
142: **NP 21DEC95 removed check on past effectivity dates.
143: **They are allowed now.
144: */
145: -- Bug 4299292. Use base table mtl_item_revisions_b to fix performance issue
146: CURSOR gg is
147: select i.transaction_id,
148: i.organization_id,
149: i.rowid

Line 150: from mtl_item_revisions_b m,

146: CURSOR gg is
147: select i.transaction_id,
148: i.organization_id,
149: i.rowid
150: from mtl_item_revisions_b m,
151: mtl_item_revisions_interface i
152: where m.organization_id = i.organization_id
153: and i.set_process_id = xset_id
154: and m.inventory_item_id = i.inventory_item_id

Line 245: l_Old_Phase_Id mtl_item_revisions_b.current_phase_id%TYPE;

241: l_lifecycle_error BOOLEAN := FALSE;
242:
243: --2808277: Update validations for Lifecycle-Phase
244: l_row_count NUMBER(3) := 0;
245: l_Old_Phase_Id mtl_item_revisions_b.current_phase_id%TYPE;
246: l_Policy_Code VARCHAR2(20);
247: l_Return_Status VARCHAR2(1);
248: l_Error_Code NUMBER;
249: l_Msg_Count NUMBER;

Line 254: l_item_phase_id mtl_item_revisions_b.current_phase_id%TYPE;

250: l_Msg_Data VARCHAR2(2000);
251: l_has_privilege VARCHAR2(1) := 'F';
252:
253: --Start:3059993:Revision create should honour items phase policy
254: l_item_phase_id mtl_item_revisions_b.current_phase_id%TYPE;
255: l_item_lifecycle_id mtl_item_revisions_b.lifecycle_id%TYPE;
256: --End:3059993:Revision create should honour items phase policy
257: l_revision_id mtl_item_revisions_interface.revision_id%TYPE;
258: l_revid_error BOOLEAN := FALSE;

Line 255: l_item_lifecycle_id mtl_item_revisions_b.lifecycle_id%TYPE;

251: l_has_privilege VARCHAR2(1) := 'F';
252:
253: --Start:3059993:Revision create should honour items phase policy
254: l_item_phase_id mtl_item_revisions_b.current_phase_id%TYPE;
255: l_item_lifecycle_id mtl_item_revisions_b.lifecycle_id%TYPE;
256: --End:3059993:Revision create should honour items phase policy
257: l_revision_id mtl_item_revisions_interface.revision_id%TYPE;
258: l_revid_error BOOLEAN := FALSE;
259:

Line 304: SELECT MTL_ITEM_REVISIONS_B_S.CURRVAL

300: l_revid_error := FALSE;
301: IF cr.transaction_type ='CREATE' AND cr.revision_id IS NOT NULL THEN
302: /* Bug 7513461*/
303: BEGIN
304: SELECT MTL_ITEM_REVISIONS_B_S.CURRVAL
305: INTO l_revision_id FROM DUAL;
306: EXCEPTION
307: WHEN currval_not_def THEN
308: SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL

Line 308: SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL

304: SELECT MTL_ITEM_REVISIONS_B_S.CURRVAL
305: INTO l_revision_id FROM DUAL;
306: EXCEPTION
307: WHEN currval_not_def THEN
308: SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL
309: INTO l_revision_id FROM DUAL;
310: WHEN OTHERS THEN
311: l_revision_id := cr.revision_id - 1;
312: END;

Line 376: FROM mtl_item_revisions_b

372: END IF;
373: --End :3456560 Revision code validation.
374:
375: SELECT count(1) INTO l_row_count
376: FROM mtl_item_revisions_b
377: WHERE organization_id = cr.organization_id
378: AND inventory_item_id = cr.inventory_item_id
379: AND (revision = cr.revision OR revision_id = cr.revision_id);
380:

Line 646: FROM mtl_item_revisions_b

642:
643: l_row_count:= 0;
644:
645: SELECT count(1) INTO l_row_count
646: FROM mtl_item_revisions_b
647: WHERE revision_id = cr.revision_id
648: AND TRUNC(effectivity_date)<= TRUNC(sysdate)
649: AND TRUNC(effectivity_date) <> TRUNC(cr.effectivity_date);
650:

Line 678: FROM mtl_item_revisions_b

674: END IF;
675:
676: --3070781:ECO Rev's update allow on description and rev label.
677: SELECT COUNT(1) INTO l_row_count
678: FROM mtl_item_revisions_b
679: where revision_id = cr.revision_id
680: AND change_notice IS NOT NULL
681: AND ((cr.ecn_initiation_date is NULL OR ecn_initiation_date <> cr.ecn_initiation_date)
682: OR (effectivity_date <> cr.effectivity_date)

Line 860: FROM mtl_item_revisions_b

856: --2808277: Start Revision update changes
857: IF cr.transaction_type = 'UPDATE' THEN
858: SELECT current_phase_id
859: INTO l_Old_Phase_Id
860: FROM mtl_item_revisions_b
861: WHERE revision_id = cr.revision_id;
862: END IF;
863: --2808277: End Revision update changes
864:

Line 1081: revision_id = NVL(revision_id,MTL_ITEM_REVISIONS_B_S.NEXTVAL) --2808277

1077:
1078: update mtl_item_revisions_interface
1079: set process_flag = l_process_flag_4,
1080: revision_label = NVL(revision_label,revision),
1081: revision_id = NVL(revision_id,MTL_ITEM_REVISIONS_B_S.NEXTVAL) --2808277
1082: where process_flag = l_process_flag_2
1083: and set_process_id = xset_id
1084: and (organization_id = org_id or all_org = l_all_org);
1085: