[Home] [Help]
PACKAGE BODY: APPS.INVPAGI3
Source
1 PACKAGE BODY INVPAGI3 AS
2 /* $Header: INVPAG3B.pls 120.10.12020000.5 2013/01/23 08:22:50 lmai ship $ */
3
4 l_process_flag_1 CONSTANT NUMBER := 1;
5 l_process_flag_2 CONSTANT NUMBER := 2;
6 l_process_flag_3 CONSTANT NUMBER := 3;
7 l_all_org CONSTANT NUMBER := 1;
8
9 ------------------------------- assign_item_revs ------------------------------
10
11 function assign_item_revs
12 (
13 org_id number,
14 all_org NUMBER := 2,
15 prog_appid NUMBER := -1,
16 prog_id NUMBER := -1,
17 request_id NUMBER := -1,
18 user_id NUMBER := -1,
19 login_id NUMBER := -1,
20 err_text in out NOCOPY varchar2,
21 xset_id IN NUMBER DEFAULT -999,
22 default_flag IN NUMBER DEFAULT 1
23 )
24 return integer
25 is
26
27 CURSOR c_item_number_err IS
28 SELECT organization_id,rowid
29 FROM mtl_item_revisions_interface
30 WHERE inventory_item_id IS NULL
31 AND set_process_id = xset_id
32 AND process_flag = 1
33 AND (organization_id = org_id OR
34 all_org = l_all_org );
35 /*
36 ** for assign item id from item number
37 */
38 CURSOR cc is select distinct item_number,
39 organization_id
40 from mtl_item_revisions_interface
41 where inventory_item_id is NULL
42 and item_number is not NULL
43 and organization_id is not NULL
44 and set_process_id = xset_id
45 and process_flag = 1;
46
47 /*
48 ** for assign transacton id
49 */
50 CURSOR ff is select distinct inventory_item_id,
51 organization_id
52 from mtl_item_revisions_interface
53 where process_flag = 1
54 and set_process_id = xset_id
55 and transaction_id IS NULL --Bug: 3019435 Added condition
56 and (organization_id = org_id or
57 all_org = 1);
58
59 /*
60 ** R12 C for assign revision id during default
61 */
62 CURSOR c_null_rev_id IS
63 SELECT rowid
64 FROM mtl_item_revisions_interface
65 WHERE process_flag = 1
66 AND set_process_id = xset_id
67 AND transaction_type = 'CREATE'
68 AND revision_id IS NULL
69 AND (organization_id = org_id OR all_org = 1)
70 ORDER BY revision;
71
72 flex_id number;
73 status number := 0;
74 dumm_status number;
75 tran_id number := 0;
76 rev_id number;
77 l_sysdate date := sysdate;
78
79 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
80 ASSIGN_ERROR exception;
81 LOGGING_ERROR exception;
82 error_msg varchar2(300); --bug 13813134
83
84 --Begin: Jewen
85 /*
86 *Bug:9154307
87 *for assign item id based on transaction_id
88 */
89 CURSOR ct is select distinct transaction_id
90 from mtl_item_revisions_interface
91 where inventory_item_id is NULL
92 and transaction_id is not NULL
93 and organization_id is not NULL
94 and set_process_id = xset_id
95 and process_flag = 1;
96
97 --End: Jewen
98 begin
99
100 IF l_inv_debug_level IN(101, 102) THEN
101 INVPUTLI.info('INVPAGI3.assign_item_revs : begin');
102 END IF;
103
104 /*
105 ** assign all the missing organization_id from organization_code
106 */
107
108 update MTL_ITEM_REVISIONS_INTERFACE i
109 set i.organization_id = (select o.organization_id
110 from MTL_PARAMETERS o
111 where o.organization_code = i.organization_code)
112 where i.organization_id is NULL
113 and set_process_id = xset_id
114 and i.process_flag = l_process_flag_1;
115
116 --Begin:Jewen
117 /*
118 *Bug:9154307
119 *for assign item id based on transaction_id
120 */
121 for ctr in ct LOOP
122 /* Start of Bug 9099489 : Added the Exception Block */
123 begin
124 SELECT inventory_item_id INTO flex_id
125 FROM mtl_system_items_interface
126 WHERE transaction_id = ctr.transaction_id
127 AND ROWNUM = 1;
128
129 UPDATE mtl_item_revisions_interface
130 SET inventory_item_id = flex_id
131 WHERE transaction_id = ctr.transaction_id
132 AND set_process_id = xset_id;
133
134 exception
135 when no_data_found then
136 NULL;
137 end;
138 /* End of Bug 9099489 */
139
140 end loop;
141 flex_id := 0;
142 --End:Jewen
143
144 /*
145 ** assign missing inventory_item_id from item number
146 */
147
148 for cr in cc loop
149
150 status := INVPUOPI.mtl_pr_parse_flex_name (
151 cr.organization_id,
152 'MSTK',
153 cr.item_number,
154 flex_id,
155 0,
156 err_text);
157
158 if status <> 0 then /* Oracle error */
159 ----Bug: 3019435 Changed the code with in IF st.
160 update mtl_item_revisions_interface
161 set process_flag = l_process_flag_3,
162 transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
163
164 where item_number = cr.item_number
165 and inventory_item_id is NULL
166 and process_flag = l_process_flag_1
167 and set_process_id = xset_id
168 and organization_id = cr.organization_id
169 RETURNING transaction_id INTO tran_id;
170
171 /*
172 select MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
173 into tran_id
174 from dual;
175 */
176 dumm_status := INVPUOPI.mtl_log_interface_err(
177 cr.organization_id,
178 user_id,
179 login_id,
180 prog_appid,
181 prog_id,
182 request_id,
183 tran_id,
184 err_text,
185 'item_number',
186 'MTL_ITEM_REVISIONS_INTERFACE',
187 'BOM_OP_VALIDATION_ERR',
188 err_text);
189 If dumm_status < 0 then
190 raise LOGGING_ERROR ;
191 End if ;
192 /*
193 update mtl_item_revisions_interface
194 set process_flag = l_process_flag_3,
195 transaction_id = tran_id
196 where item_number = cr.item_number
197 and inventory_item_id is NULL
198 and process_flag = l_process_flag_1
199 and set_process_id = xset_id
200 and organization_id = cr.organization_id;
201 */
202 if status < 0 then
203 raise ASSIGN_ERROR;
204 end if;
205
206 else if status = 0 then
207 update mtl_item_revisions_interface
208 set inventory_item_id = flex_id
209 where item_number = cr.item_number
210 and set_process_id = xset_id
211 and organization_id = cr.organization_id;
212 end if;
213 end if;
214 end loop;
215
216 /*
217 ** Assign transaction_id
218 */
219 for cr in ff loop
220
221 select MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
222 into tran_id
223 from dual;
224
225 update mtl_item_revisions_interface
226 set transaction_id = tran_id
227 where inventory_item_id = cr.inventory_item_id
228 and organization_id = cr.organization_id
229 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
230 and set_process_id = xset_id
231 and process_flag = l_process_flag_1;
232
233 end loop;
234
235 /* Assigning Revision Ids to all CREATE records during default phase - R12C */
236 FOR cr IN c_null_rev_id LOOP
237 select MTL_ITEM_REVISIONS_B_S.nextval
238 into rev_id
239 from dual;
240
241 update mtl_item_revisions_interface
242 set revision_id = rev_id
243 where rowid = cr.rowid;
244 END LOOP;
245
246
247 /*
248 ** update process flag , at last
249 ** For bug 3226359 added code to update date fields with sysdate + 1/86400 (1 sec) if they are NULL
250 */
251 update mtl_item_revisions_interface
252 set process_flag = DECODE(default_flag, 1, l_process_flag_2 , l_process_flag_1),
253 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,(sysdate + 1/86400)),
254 /* LAST_UPDATED_BY = -1,
255 ** NP 13OCT94 If you encounter ORA-6502 then see TAR 106456.555
256 ** The decode stmts are the culprits!
257 */
258 LAST_UPDATED_BY = decode(LAST_UPDATED_BY, NULL, user_id,LAST_UPDATED_BY),
259 CREATION_DATE = nvl(CREATION_DATE,(sysdate + 1/86400)),
260 /*CREATED_BY = -1,*/
261 CREATED_BY = decode(LAST_UPDATED_BY, NULL, user_id,LAST_UPDATED_BY),
262 CHANGE_NOTICE = NULL,
263 ECN_INITIATION_DATE = NULL,
264 IMPLEMENTATION_DATE = nvl(effectivity_date, (l_sysdate + 1/86400)),
265 implemented_serial_number = NULL,
266 revised_item_sequence_id = NULL ,
267 effectivity_date = nvl(effectivity_date, (l_sysdate + 1/86400)),
268 revision = trim(revision) --Bugfix 6457167
269 where inventory_item_id is not null
270 and process_flag = l_process_flag_1
271 and set_process_id = xset_id
272 and (organization_id = org_id or all_org = l_all_org);
273
274 /*
275 ** set process flag for the records with errors
276 */
277
278 --Bug :3625086
279 /* update mtl_item_revisions_interface i
280 set i.process_flag = l_process_flag_3,
281 i.LAST_UPDATE_DATE = sysdate,
282 i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
283 i.CREATION_DATE = l_sysdate,
284 i.CREATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY)
285 where ( i.inventory_item_id is NULL or
286 i.organization_id is NULL)
287 and set_process_id = xset_id
288 and i.process_flag = l_process_flag_1
289 and ( i.organization_id = org_id or
290 all_org = l_all_org );*/
291 /*
292 ** failed within the same set
293 */
294
295 FOR rec IN c_item_number_err LOOP
296 UPDATE mtl_item_revisions_interface i
297 SET i.process_flag = l_process_flag_3,
298 i.LAST_UPDATE_DATE = sysdate,
299 i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
300 i.CREATION_DATE = l_sysdate,
301 i.CREATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
302 i.transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
303 WHERE i.rowid = rec.rowid
304 RETURNING i.transaction_id INTO tran_id ;
305
306 dumm_status := INVPUOPI.mtl_log_interface_err(
307 rec.organization_id,
308 user_id,
309 login_id,
310 prog_appid,
311 prog_id,
312 request_id,
313 tran_id,
314 null,
315 'item_number',
316 'MTL_ITEM_REVISIONS_INTERFACE',
317 'INV_IOI_ITEM_NUMBER_NO_EXIST',
318 err_text);
319
320 IF dumm_status < 0 THEN
321 raise LOGGING_ERROR ;
322 END IF ;
323 END LOOP;
324 --End 3625086
325 update mtl_item_revisions_interface i
326 set i.process_flag = l_process_flag_3
327 where i.transaction_id in (select m.transaction_id
328 from mtl_item_revisions_interface m
329 where m.process_flag = l_process_flag_3
330 and (m.organization_id = org_id or
331 all_org = l_all_org )
332 and set_process_id = xset_id )
333 and i.process_flag = l_process_flag_2
334 and set_process_id = xset_id
335 and (i.organization_id = org_id or
336 all_org = l_all_org);
337
338 return (0);
339
340 exception
341
342 when ASSIGN_ERROR then
343 err_text := 'assign error raised in INVPAGI3.assign_item_revs with message:' || err_text; --added by bug 11894684
344 return(status);
345 when LOGGING_ERROR then
346 err_text := 'logging error raised in INVPAGI3.assign_item_revs with message:' || err_text; --added by bug 11894684
347 return(dumm_status);
348 when OTHERS then
349 --Bug 13813134
350 error_msg := substr('INVPAGI3.assign_item_revs:' || SQLERRM , 1, 240);
351 err_text := error_msg;
352 dumm_status := INVPUOPI.mtl_log_interface_err(
353 org_id,
354 user_id,
355 login_id,
356 prog_appid,
357 prog_id,
358 request_id,
359 tran_id,
360 error_msg,
361 null,
362 'MTL_ITEM_REVISIONS_INTERFACE',
363 'BOM_PARSE_ITEM_ERROR',
364 err_text);
365 return(SQLCODE);
366 -- return(status);
367
368 end assign_item_revs;
369
370
371 end INVPAGI3;