1: package body BOMPINRV as
2: /* $Header: BOMINRVB.pls 120.3 2005/12/05 03:17:03 vhymavat ship $ */
3: Procedure increment_revision(
4: i_item_id in mtl_item_revisions.inventory_item_id%type,
5: i_org_id in mtl_item_revisions.organization_id%type,
6: i_date_time in mtl_item_revisions.effectivity_date%type,
7: who in ProgramInfoStruct,
8: o_out_code in out nocopy mtl_item_revisions.revision%type,
1: package body BOMPINRV as
2: /* $Header: BOMINRVB.pls 120.3 2005/12/05 03:17:03 vhymavat ship $ */
3: Procedure increment_revision(
4: i_item_id in mtl_item_revisions.inventory_item_id%type,
5: i_org_id in mtl_item_revisions.organization_id%type,
6: i_date_time in mtl_item_revisions.effectivity_date%type,
7: who in ProgramInfoStruct,
8: o_out_code in out nocopy mtl_item_revisions.revision%type,
9: error_message in out nocopy varchar) is
2: /* $Header: BOMINRVB.pls 120.3 2005/12/05 03:17:03 vhymavat ship $ */
3: Procedure increment_revision(
4: i_item_id in mtl_item_revisions.inventory_item_id%type,
5: i_org_id in mtl_item_revisions.organization_id%type,
6: i_date_time in mtl_item_revisions.effectivity_date%type,
7: who in ProgramInfoStruct,
8: o_out_code in out nocopy mtl_item_revisions.revision%type,
9: error_message in out nocopy varchar) is
10:
4: i_item_id in mtl_item_revisions.inventory_item_id%type,
5: i_org_id in mtl_item_revisions.organization_id%type,
6: i_date_time in mtl_item_revisions.effectivity_date%type,
7: who in ProgramInfoStruct,
8: o_out_code in out nocopy mtl_item_revisions.revision%type,
9: error_message in out nocopy varchar) is
10:
11: Cursor get_current_rev is
12: select a.revision,
10:
11: Cursor get_current_rev is
12: select a.revision,
13: nvl(length(rtrim(a.revision,'0123456789')),0) rev_len
14: from mtl_item_revisions_b a
15: where a.organization_id = i_org_id
16: and a.inventory_item_id = i_item_id
17: and a.effectivity_date = (
18: select max(b.effectivity_date)
15: where a.organization_id = i_org_id
16: and a.inventory_item_id = i_item_id
17: and a.effectivity_date = (
18: select max(b.effectivity_date)
19: from mtl_item_revisions_b b
20: where b.inventory_item_id = a.inventory_item_id
21: and b.organization_id = a.organization_id
22: and b.effectivity_date <= i_date_time)
23: -- and b.implementation_date is not null)
25: order by a.revision desc; --* Added for Bug #3483066
26: current_rev get_current_rev%rowtype;
27: Cursor check_duplicate_rev(rev in varchar) is
28: select 'x'
29: from mtl_item_revisions_b r
30: where r.organization_id = i_org_id
31: and r.inventory_item_id = i_item_id
32: and r.revision = rev;
33: dummy varchar2(1);
30: where r.organization_id = i_org_id
31: and r.inventory_item_id = i_item_id
32: and r.revision = rev;
33: dummy varchar2(1);
34: new_item_revision mtl_item_revisions.revision%type := null;
35: old_rev_len NUMBER;
36: new_rev_len NUMBER;
37: i_revision_id NUMBER;
38: i_language_code VARCHAR2(3);
66: Fetch check_duplicate_rev into dummy;
67: If check_duplicate_rev%found then
68: o_out_code := null;
69: else
70: Insert into mtl_item_revisions_b(
71: inventory_item_id,
72: organization_id,
73: revision,
74: last_update_date,
97: who.appid,
98: who.progid,
99: sysdate,
100: who.reqstid,
101: MTL_ITEM_REVISIONS_B_S.nextval,
102: new_item_revision,
103: 1) RETURNING revision_id INTO i_revision_id;
104:
105: SELECT userenv('LANG') INTO i_language_code FROM dual;
102: new_item_revision,
103: 1) RETURNING revision_id INTO i_revision_id;
104:
105: SELECT userenv('LANG') INTO i_language_code FROM dual;
106: -- description is stored in MTL_ITEM_REVISIONS_TL
107: insert into MTL_ITEM_REVISIONS_TL (
108: inventory_item_id,
109: organization_id,
110: revision_id,
103: 1) RETURNING revision_id INTO i_revision_id;
104:
105: SELECT userenv('LANG') INTO i_language_code FROM dual;
106: -- description is stored in MTL_ITEM_REVISIONS_TL
107: insert into MTL_ITEM_REVISIONS_TL (
108: inventory_item_id,
109: organization_id,
110: revision_id,
111: language,
130: FROM FND_LANGUAGES lang
131: where lang.INSTALLED_FLAG in ('I', 'B')
132: and not exists
133: (select NULL
134: from MTL_ITEM_REVISIONS_TL T
135: where T.INVENTORY_ITEM_ID = i_item_id
136: and T.ORGANIZATION_ID = i_org_id
137: and T.REVISION_ID = i_revision_id
138: and T.LANGUAGE = lang.LANGUAGE_CODE);