[Home] [Help]
PACKAGE BODY: APPS.BOMPINRV
Source
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
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)
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)
24 -- and a.implementation_date is not null commneted for bug 4637312
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);
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);
39 Begin
40
41 /* Get current implemented rev */
42
43 Open get_current_rev;
44 Fetch get_current_rev into current_rev;
45 If get_current_rev%notfound then
46 Close get_current_rev;
47 Raise no_data_found;
48 end if;
49 Close get_current_rev;
50
51 /*
52 * Check to see if the current rev is strictly numeric, increment it
53 * and then check if the new one exists.
54 */
55
56 If current_rev.rev_len = 0 then /* Numbers only */
57 new_item_revision :=
58 to_char(to_number(current_rev.revision)+1);
59 old_rev_len := NVL(length(current_rev.revision),0);
60 new_rev_len := NVL(length(new_item_revision),0);
61 if (new_rev_len < old_rev_len) then
62 new_item_revision := lpad(new_item_revision,
63 old_rev_len,'0');
64 end if;
65 Open check_duplicate_rev(new_item_revision);
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,
75 last_updated_by,
76 creation_date,
77 created_by,
78 last_update_login,
79 effectivity_date,
80 program_application_id,
81 program_id,
82 program_update_date,
83 request_id,
84 REVISION_ID,
85 REVISION_LABEL,
86 OBJECT_VERSION_NUMBER)
87 values (
88 i_item_id,
89 i_org_id,
90 new_item_revision,
91 sysdate,
92 who.userid,
93 sysdate,
94 who.userid,
95 who.loginid,
96 i_date_time,
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;
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,
112 source_lang,
113 last_update_date,
114 last_updated_by,
115 creation_date,
116 created_by,
117 last_update_login,
118 description )
119 SELECT i_item_id,
120 i_org_id,
121 i_revision_id,
122 lang.language_code,
123 i_language_code,
124 sysdate,
125 who.userid,
126 sysdate,
127 who.userid,
128 who.userid,
129 NULL
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);
139
140 o_out_code := new_item_revision;
141 end if;
142 Close check_duplicate_rev;
143 else
144 o_out_code := null;
145 end if;
146 error_message := null;
147 Exception
148 when NO_DATA_FOUND then /* no revs found for item - ignore */
149 o_out_code := null;
150 error_message := null;
151 when OTHERS then
152 o_out_code := null;
153 error_message := substrb(sqlerrm, 1, 150);
154 end increment_revision;
155
156 end BOMPINRV;