[Home] [Help]
PACKAGE BODY: APPS.BOM_REVISION_API
Source
1 PACKAGE BODY Bom_Revision_Api AS
2 /* $Header: BOMOIRVB.pls 115.5 2002/06/14 12:33:30 pkm ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMOIRVB.pls |
9 | DESCRIPTION : This package contains functions used to assign, validate |
10 | and transact Item Revision data in the |
11 | MTL_ITEM_REVISIONS_INTERFACE table. |
12 | Parameters: org_id organization_id |
13 | all_org process all orgs or just current org |
14 | 1 - all orgs |
15 | 2 - only org_id |
16 | prog_appid program application_id |
17 | prog_id program id |
18 | req_id request_id |
19 | user_id user id |
20 | login_id login id |
21 | History: |
22 | 03/10/97 Julie Maeyama Created this new package |
23 +==========================================================================*/
24
25 /* -------------------------- Assign_Item_Revision --------------------------*/
26 /*
27 NAME
28 Assign_Item_Revision
29 DESCRIPTION
30 Assign defaults and ID's to item revision record in the interface table
31 REQUIRES
32 err_text out buffer to return error message
33 MODIFIES
34 MTL_ITEM_REVISIONS_INTERFACE
35 MTL_INTERFACE_ERRORS
36 RETURNS
37 0 if successful
38 SQLCODE if unsuccessful
39 NOTES
40 -----------------------------------------------------------------------------*/
41 FUNCTION Assign_Item_Revision (
42 org_id NUMBER,
43 all_org NUMBER := 2,
44 user_id NUMBER,
45 login_id NUMBER,
46 prog_appid NUMBER,
47 prog_id NUMBER,
48 req_id NUMBER,
49 err_text IN OUT VARCHAR2
50 )
51 return INTEGER
52 IS
53 stmt_num NUMBER := 0;
54 ret_code NUMBER;
55 commit_cnt NUMBER;
56 continue_loop BOOLEAN := TRUE;
57
58 CURSOR c1 IS
59 SELECT organization_code OC, organization_id OI,
60 revision R, inventory_item_id III, item_number IIN,
61 transaction_id TI, implementation_date ID, effectivity_date ED,
62 transaction_type A
63 FROM mtl_item_revisions_interface
64 WHERE process_flag = 1
65 and transaction_type in (G_Insert, G_Update)
66 and (all_org = 1
67 OR
68 (all_org = 2 and organization_id = org_id))
69 and rownum < G_rows_to_commit;
70
71 BEGIN
72 /** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
73 stmt_num := 0.5 ;
74 LOOP
75 UPDATE mtl_item_revisions_interface
76 SET transaction_type = G_Insert
77 WHERE process_flag = 1
78 AND upper(transaction_type) = 'INSERT'
79 AND rownum < G_rows_to_commit;
80 EXIT when SQL%NOTFOUND;
81 COMMIT;
82 END LOOP;
83
84 /*
85 ** ALL INSERTS and UPDATES - Assign Org Id
86 */
87 stmt_num := 1;
88 LOOP
89 UPDATE mtl_item_revisions_interface ori
90 SET organization_id = (SELECT organization_id
91 FROM mtl_parameters a
92 WHERE a.organization_code = ori.organization_code)
93 WHERE process_flag = 1
94 AND upper(transaction_type) in (G_Insert, G_Update)
95 AND organization_id is null
96 AND organization_code is not null
97 AND exists (SELECT organization_code
98 FROM mtl_parameters b
99 WHERE b.organization_code = ori.organization_code)
100 AND rownum < G_rows_to_commit;
101 EXIT when SQL%NOTFOUND;
102 COMMIT;
103 END LOOP;
104
105 /*
106 ** FOR INSERTS and UPDATES - Assign transaction ids
107 */
108 stmt_num := 2;
109 LOOP
110 UPDATE mtl_item_revisions_interface
111 SET transaction_id = mtl_system_items_interface_s.nextval,
112 transaction_type = upper(transaction_type)
113 WHERE transaction_id is null
114 and process_flag = 1
115 and upper(transaction_type) in (G_Insert, G_Update)
116 and rownum < G_rows_to_commit;
117 EXIT when SQL%NOTFOUND;
118 COMMIT;
119 END LOOP;
120 /*
121 ** FOR INSERTS and UPDATES - Check if ORGANIZATION_ID is null
122 */
123 WHILE continue_loop LOOP
124 commit_cnt := 0;
125 FOR c1rec IN c1 LOOP
126 commit_cnt := commit_cnt + 1;
127 stmt_num := 3;
128 IF (c1rec.OI is null) THEN
129 ret_code := INVPUOPI.mtl_log_interface_err(
130 org_id => NULL,
131 user_id => user_id,
132 login_id => login_id,
133 prog_appid => prog_appid,
134 prog_id => prog_id,
135 req_id => req_id,
136 trans_id => c1rec.TI,
137 error_text => err_text,
138 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
139 msg_name => 'BOM_ORG_ID_MISSING',
140 err_text => err_text);
141 UPDATE mtl_item_revisions_interface
142 SET process_flag = 3
143 WHERE transaction_id = c1rec.TI;
144
145 GOTO continue_loop;
146 END IF;
147 /*
148 ** Check if INVENTORY_ITEM_ID is null
149 */
150 stmt_num := 4;
151 IF (c1rec.III is null) THEN
152 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
153 org_id => c1rec.OI,
154 flex_code => 'MSTK',
155 flex_name => c1rec.IIN,
156 flex_id => c1rec.III,
157 set_id => -1,
158 err_text => err_text);
159 IF (ret_code <> 0) THEN
160 ret_code := INVPUOPI.mtl_log_interface_err(
161 org_id => c1rec.OI,
162 user_id => user_id,
163 login_id => login_id,
164 prog_appid => prog_appid,
165 prog_id => prog_id,
166 req_id => req_id,
167 trans_id => c1rec.TI,
168 error_text => err_text,
169 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
170 msg_name => 'BOM_INV_ITEM_ID_MISSING',
171 err_text => err_text);
172 UPDATE mtl_item_revisions_interface
173 SET process_flag = 3
174 WHERE transaction_id = c1rec.TI;
175
176 IF (ret_code <> 0) THEN
177 RETURN(ret_code);
178 END IF;
179 GOTO continue_loop;
180 END IF;
181 END IF;
182 /*
183 ** Assign values to interface record
184 */
185 IF (c1rec.A = G_Insert) THEN
186 /* For Inserts */
187 stmt_num := 5;
188 UPDATE mtl_item_revisions_interface
189 SET organization_id = nvl(organization_id, c1rec.OI),
190 inventory_item_id = nvl(inventory_item_id, c1rec.III),
191 revision = UPPER(c1rec.R),
192 process_flag = 2,
193 last_update_date = nvl(last_update_date, sysdate),
194 last_updated_by = nvl(last_updated_by, user_id),
195 creation_date = nvl(creation_date, sysdate),
196 created_by = nvl(created_by, user_id),
197 last_update_login = nvl(last_update_login, user_id),
198 request_id = nvl(request_id, req_id),
199 program_application_id = nvl(program_application_id,
200 prog_appid),
201 program_id = nvl(program_id, prog_id),
202 program_update_date = nvl(program_update_date, sysdate),
203 effectivity_date = nvl(effectivity_date, sysdate),
204 implementation_date = nvl(effectivity_date, sysdate)
205 WHERE transaction_id = c1rec.TI;
206
207 IF (SQL%NOTFOUND) THEN
208 err_text := 'BOM_REVISION_API(' || stmt_num || ')' ||
209 substrb(SQLERRM, 1, 60);
210 RETURN(SQLCODE);
211 END IF;
212 ELSE
213 /* For Updates */
214 stmt_num := 6;
215 UPDATE mtl_item_revisions_interface
216 SET organization_id = nvl(organization_id, c1rec.OI),
217 inventory_item_id = nvl(inventory_item_id, c1rec.III),
218 revision = UPPER(c1rec.R),
219 process_flag = 2,
220 last_update_date = nvl(last_update_date, sysdate),
221 last_updated_by = nvl(last_updated_by, user_id),
222 last_update_login = nvl(last_update_login, user_id),
223 implementation_date = nvl(effectivity_date, NULL)
224 WHERE transaction_id = c1rec.TI;
225
226 IF (SQL%NOTFOUND) THEN
227 err_text := 'BOM_REVISION_API(' || stmt_num || ')' ||
228 substrb(SQLERRM, 1, 60);
229 RETURN(SQLCODE);
230 END IF;
231 END IF;
232
233 <<continue_loop>>
234 NULL;
235 END LOOP;
236
237 stmt_num := 7;
238 COMMIT;
239
240 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
241 continue_loop := FALSE;
242 END IF;
243 END LOOP;
244
245 RETURN (0);
246 EXCEPTION
247 WHEN others THEN
248 err_text := 'BOM_REVISION_API(Assign-'||stmt_num||') '||substrb(SQLERRM,1,500);
249 RETURN(SQLCODE);
250 END Assign_Item_Revision;
251
252
253 /* -------------------------- Check_Revision_Order ------------------------- */
254 /*
255 NAME
256 Check_Revision_Order
257 DESCRIPTION
258 Ensure revs in ascending order
259 Ensure no duplicate revs
260 REQUIRES
261 org_id NUMBER
262 assy_id NUMBER
263 err_text out buffer to return error message
264 MODIFIES
265 RETURNS
266 0 if successful
267 SQLCODE if unsuccessful
268 NOTES
269 -----------------------------------------------------------------------------*/
270 FUNCTION Check_Revision_Order (
271 org_id NUMBER,
272 assy_id NUMBER,
273 user_id NUMBER,
274 login_id NUMBER,
275 prog_appid NUMBER,
276 prog_id NUMBER,
277 req_id NUMBER,
278 err_text IN OUT VARCHAR2
279 )
280 return INTEGER
281 IS
282 CURSOR c1 is
283 SELECT revision R, effectivity_date ED,
284 transaction_id TI, transaction_type TT
285 FROM mtl_item_revisions_interface
286 WHERE organization_id = org_id
287 and inventory_item_id = assy_id
288 and transaction_type in (G_Insert, G_Update)
289 and process_flag = 99;
290 ret_code NUMBER;
291 err_cnt NUMBER;
292 err_flag NUMBER;
293 stmt_num NUMBER := 0;
294
295 BEGIN
296 FOR c1rec IN c1 LOOP
297 err_cnt := 0;
298 stmt_num := 1;
299 /*
300 ** FOR INSERTS and UPDATES - Check for ascending order and identical revs
301 */
302 SELECT count(*)
303 INTO err_cnt
304 FROM mtl_item_revisions_interface a
305 WHERE transaction_id <> c1rec.TI
306 and inventory_item_id = assy_id
307 and organization_id = org_id
308 and process_flag = 4
309 and ( (revision = c1rec.R)
310 OR
311 (effectivity_date > c1rec.ED
312 and revision < c1rec.R)
313 OR
314 (effectivity_date < c1rec.ED
315 and revision > c1rec.R));
316
317 IF (err_cnt <> 0) THEN
318 GOTO write_error;
319 END IF;
320
321 /*
322 ** FOR INSERTS - Check production table
323 */
324 stmt_num := 2;
325 IF (c1rec.TT = G_Insert) THEN
326 SELECT count(*)
327 INTO err_cnt
328 FROM mtl_item_revisions mir
329 WHERE inventory_item_id = assy_id
330 and organization_id = org_id
331 and NOT EXISTS (select 'x'
332 from mtl_item_revisions_interface miri
333 where miri.inventory_item_id = mir.inventory_item_id
334 and miri.organization_id = mir.organization_id
335 and miri.revision = mir.revision
336 and miri.process_flag = 4)
337 and ((revision = c1rec.R)
338 OR
339 (effectivity_date > c1rec.ED
340 AND revision < c1rec.R)
341 OR
345 IF (err_cnt <> 0) THEN
342 (effectivity_date < c1rec.ED
343 AND revision > c1rec.R));
344
346 GOTO write_error;
347 END IF;
348 ELSE
349 /*
350 ** FOR UPDATES - Check production table
351 */
352 stmt_num := 3;
353 SELECT count(*)
354 INTO err_cnt
355 FROM mtl_item_revisions mir
356 WHERE inventory_item_id = assy_id
357 and organization_id = org_id
358 and revision <> c1rec.R
359 and NOT EXISTS (select 'x'
360 from mtl_item_revisions_interface miri
361 where miri.inventory_item_id = mir.inventory_item_id
362 and miri.organization_id = mir.organization_id
363 and miri.revision = mir.revision
364 and miri.process_flag = 4)
365 and ((effectivity_date > c1rec.ED
366 AND revision < c1rec.R)
367 OR
368 (effectivity_date < c1rec.ED
369 AND revision > c1rec.R));
370
371 IF (err_cnt <> 0) THEN
372 GOTO write_error;
373 END IF;
374 END IF;
375
376 stmt_num := 4;
377 UPDATE mtl_item_revisions_interface
378 SET process_flag = 4
379 WHERE transaction_id = c1rec.TI;
380 GOTO continue_loop;
381
382 <<write_error>>
383 ret_code := INVPUOPI.mtl_log_interface_err(
384 org_id => org_id,
385 user_id => user_id,
386 login_id => login_id,
387 prog_appid => prog_appid,
388 prog_id => prog_id,
389 req_id => req_id,
390 trans_id => c1rec.TI,
391 error_text => err_text,
392 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
393 msg_name => 'BOM_REV_INVALID',
394 err_text => err_text);
395 UPDATE mtl_item_revisions_interface
396 SET process_flag = 3
397 WHERE transaction_id = c1rec.TI;
398 <<continue_loop>>
399 null;
400 END LOOP;
401 return(0);
402 EXCEPTION
403 WHEN others THEN
404 err_text := 'BOM_REVISION_API(Check-'||stmt_num||') '||substrb(SQLERRM,1,60);
405 return(SQLCODE);
406 END Check_Revision_Order;
407
408
409 /* ------------------------- Validate_Item_Revision ------------------------ */
410 /*
411 NAME
412 Validate_Item_Revision
413 DESCRIPTION
414 REQUIRES
415 org_id org id to validate
416 all_org all_org flag
417 user_id user id
418 login_id login id
419 prog_appid program application id
420 prod_id program id
421 req_id request id
422 err_text out buffer to return error message
423 MODIFIES
424 MTL_INTERFACE_ERRORS
425 RETURNS
426 0 if successful
427 SQLCODE if unsuccessful
428 NOTES
429 -----------------------------------------------------------------------------*/
430 FUNCTION Validate_Item_Revision (
431 org_id NUMBER,
432 all_org NUMBER := 2,
433 user_id NUMBER,
434 login_id NUMBER,
435 prog_appid NUMBER,
436 prog_id NUMBER,
437 req_id NUMBER,
438 err_text IN OUT VARCHAR2
439 )
440 return INTEGER
441 IS
442 ret_code NUMBER;
443 dummy NUMBER;
444 dummy_id NUMBER;
445 stmt_num NUMBER := 0;
446 commit_cnt NUMBER;
447 dummy_bill NUMBER;
448 continue_loop1 BOOLEAN := TRUE;
449 continue_loop2 BOOLEAN := TRUE;
450 X_creation_date DATE;
451 X_created_by NUMBER;
452 X_change_notice VARCHAR2(10);
453 X_ecn_initiation_date DATE;
454 X_implementation_date DATE;
455 X_effectivity_date DATE;
456 X_attribute_category VARCHAR2(30);
457 X_attribute1 VARCHAR2(150);
458 X_attribute2 VARCHAR2(150);
459 X_attribute3 VARCHAR2(150);
460 X_attribute4 VARCHAR2(150);
461 X_attribute5 VARCHAR2(150);
462 X_attribute6 VARCHAR2(150);
463 X_attribute7 VARCHAR2(150);
464 X_attribute8 VARCHAR2(150);
465 X_attribute9 VARCHAR2(150);
466 X_attribute10 VARCHAR2(150);
467 X_attribute11 VARCHAR2(150);
468 X_attribute12 VARCHAR2(150);
469 X_attribute13 VARCHAR2(150);
470 X_attribute14 VARCHAR2(150);
471 X_attribute15 VARCHAR2(150);
472 X_request_id NUMBER;
473 X_program_application_id NUMBER;
474 X_program_id NUMBER;
475 X_program_update_date DATE;
476 X_revised_item_sequence_id NUMBER;
480 */
477 X_description VARCHAR2(240);
478 /*
479 ** All "Insert" records
481 CURSOR c0 IS
482 select inventory_item_id AII, organization_id OI,
483 revision R, transaction_id TI,
484 change_notice CN
485 from mtl_item_revisions_interface
486 where process_flag = 2
487 and transaction_type = G_Insert
488 and rownum < G_rows_to_commit;
489 /*
490 ** All "Insert" and "Update" records grouped by Item
491 */
492 CURSOR c1 IS
493 select inventory_item_id AII, organization_id OI
494 from mtl_item_revisions_interface
495 where process_flag = 99
496 and transaction_type in (G_Insert, G_Update)
497 group by organization_id, inventory_item_id;
498
499 /*
500 ** All "Update" records
501 */
502 CURSOR c3 IS
503 select inventory_item_id III, organization_id OI,
504 revision R, transaction_id TI,
505 creation_date CD, created_by CB, change_notice CN,
506 ecn_initiation_date EID, implementation_date ID,
507 effectivity_date ED, revised_item_sequence_id RISI,
508 attribute_category AC, attribute1 A1, attribute2 A2,
509 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
510 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
511 attribute11 A11, attribute12 A12, attribute13 A13,
512 attribute14 A14, attribute15 A15, request_id RI,
513 program_application_id PAI, program_id PI,
514 program_update_date PUD, description D
515 from mtl_item_revisions_interface
516 where process_flag = 2
517 and transaction_type = G_Update
518 and rownum < G_rows_to_commit;
519
520 BEGIN
521 /*
522 ** FOR UPDATES - Validate
523 */
524
525 stmt_num := 1;
526 WHILE continue_loop1 LOOP
527 commit_cnt := 0;
528 FOR c3rec IN c3 LOOP
529 commit_cnt := commit_cnt + 1;
530 /*
531 ** Check if implemented "update" record exists in Production
532 */
533 stmt_num := 2;
534 BEGIN
535 SELECT creation_date, created_by, change_notice,
536 ecn_initiation_date, implementation_date,
537 effectivity_date, attribute_category, attribute1,
538 attribute2, attribute3, attribute4, attribute5,
539 attribute6, attribute7, attribute8, attribute9,
540 attribute10, attribute11, attribute12, attribute13,
541 attribute14, attribute15, request_id,
542 program_application_id, program_id, program_update_date,
543 revised_item_sequence_id, description
544 INTO X_creation_date, X_created_by, X_change_notice,
545 X_ecn_initiation_date, X_implementation_date,
546 X_effectivity_date, X_attribute_category, X_attribute1,
547 X_attribute2, X_attribute3, X_attribute4, X_attribute5,
548 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
549 X_attribute10, X_attribute11, X_attribute12, X_attribute13,
550 X_attribute14, X_attribute15, X_request_id,
551 X_program_application_id, X_program_id,
552 X_program_update_date, X_revised_item_sequence_id,
553 X_description
554 FROM mtl_item_revisions
555 WHERE organization_id = c3rec.OI
556 and inventory_item_id = c3rec.III
557 and revision = c3rec.R
558 and implementation_date is NOT NULL;
559 EXCEPTION
560 WHEN No_Data_Found THEN
561 ret_code := INVPUOPI.mtl_log_interface_err(
562 org_id => c3rec.OI,
563 user_id => user_id,
564 login_id => login_id,
565 prog_appid => prog_appid,
566 prog_id => prog_id,
567 req_id => req_id,
568 trans_id => c3rec.TI,
569 error_text => err_text,
570 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
571 msg_name => 'BOM_REV_RECORD_MISSING',
572 err_text => err_text);
573
574 UPDATE mtl_item_revisions_interface
575 SET process_flag = 3
576 WHERE transaction_id = c3rec.TI;
577
578 IF (ret_code <> 0) THEN
579 return(ret_code);
580 END IF;
581 GOTO continue_loop1;
582 END;
583 /*
584 ** Check if column is non-updatable and give warning if user filled it in
585 */
586 IF (c3rec.CD is not null
587 OR c3rec.CB is not null
588 OR c3rec.CN is not null
589 OR c3rec.EID is not null
590 OR c3rec.RISI is not null) THEN
591 ret_code := INVPUOPI.mtl_log_interface_err(
592 org_id => c3rec.OI,
593 user_id => user_id,
594 login_id => login_id,
595 prog_appid => prog_appid,
596 prog_id => prog_id,
597 req_id => req_id,
601 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
598 trans_id => c3rec.TI,
599 error_text => err_text,
600 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
602 err_text => err_text);
603
604 UPDATE mtl_item_revisions_interface
605 SET process_flag = 3
606 WHERE transaction_id = c3rec.TI;
607
608 IF (ret_code <> 0) THEN
609 return(ret_code);
610 END IF;
611 GOTO continue_loop1;
612 END IF;
613 /*
614 ** Update interface record with production record's values
615 */
616
617 stmt_num := 3;
618 UPDATE mtl_item_revisions_interface
619 SET creation_date = X_creation_date,
620 created_by = X_created_by,
621 change_notice = X_change_notice,
622 ecn_initiation_date = X_ecn_initiation_date,
623 revised_item_sequence_id = X_revised_item_sequence_id,
624 process_flag = 99,
625 effectivity_date = nvl(c3rec.ED, X_effectivity_date),
626 implementation_date = nvl(c3rec.ID, X_implementation_date),
627 attribute_category = decode(c3rec.AC, G_NullChar, '', NULL,
628 X_attribute_category, c3rec.AC),
629 attribute1 = decode(c3rec.A1, G_NullChar, '', NULL,
630 X_attribute1, c3rec.A1),
631 attribute2 = decode(c3rec.A2, G_NullChar, '', NULL,
632 X_attribute2, c3rec.A2),
633 attribute3 = decode(c3rec.A3, G_NullChar, '', NULL,
634 X_attribute3, c3rec.A3),
635 attribute4 = decode(c3rec.A4, G_NullChar, '', NULL,
636 X_attribute4, c3rec.A4),
637 attribute5 = decode(c3rec.A5, G_NullChar, '', NULL,
638 X_attribute5, c3rec.A5),
639 attribute6 = decode(c3rec.A6, G_NullChar, '', NULL,
640 X_attribute6, c3rec.A6),
641 attribute7 = decode(c3rec.A7, G_NullChar, '', NULL,
642 X_attribute7, c3rec.A7),
643 attribute8 = decode(c3rec.A8, G_NullChar, '', NULL,
644 X_attribute8, c3rec.A8),
645 attribute9 = decode(c3rec.A9, G_NullChar, '', NULL,
646 X_attribute9, c3rec.A9),
647 attribute10 = decode(c3rec.A10, G_NullChar, '', NULL,
648 X_attribute10, c3rec.A10),
649 attribute11 = decode(c3rec.A11, G_NullChar, '', NULL,
650 X_attribute11, c3rec.A11),
651 attribute12 = decode(c3rec.A12, G_NullChar, '', NULL,
652 X_attribute12, c3rec.A12),
653 attribute13 = decode(c3rec.A13, G_NullChar, '', NULL,
654 X_attribute13, c3rec.A13),
655 attribute14 = decode(c3rec.A14, G_NullChar, '', NULL,
656 X_attribute14, c3rec.A14),
657 attribute15 = decode(c3rec.A15, G_NullChar, '', NULL,
658 X_attribute15, c3rec.A15),
659 request_id = decode(c3rec.RI, G_NullChar, '', NULL,
660 X_request_id, c3rec.RI),
661 program_application_id = decode(c3rec.PAI, G_NullNum, '',
662 NULL,
663 X_program_application_id, c3rec.PAI),
664 program_id = decode(c3rec.PI, G_NullNum, '', NULL,
665 X_program_id, c3rec.PI),
666 program_update_date = decode(c3rec.PUD, G_NullDate, '', NULL,
667 X_program_update_date, c3rec.PUD),
668 description = decode(c3rec.D, G_NullChar, '', NULL,
669 X_description, c3rec.D)
670 WHERE transaction_id = c3rec.TI;
671
672 <<continue_loop1>>
673 NULL;
674 END LOOP;
675
676 stmt_num := 4;
677 COMMIT;
678 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
679 continue_loop1 := FALSE;
680 END IF;
681
682 END LOOP;
683
684
685 /*
686 ** FOR INSERTS - Validate
687 */
688 stmt_num := 5;
689 WHILE continue_loop2 LOOP
690 commit_cnt := 0;
691 FOR c0rec IN c0 LOOP
692 commit_cnt := commit_cnt + 1;
693
694 /*
695 ** Check if revision is null
696 */
697 IF (c0rec.R is null) THEN
698 ret_code := INVPUOPI.mtl_log_interface_err(
699 org_id => org_id,
700 user_id => user_id,
701 login_id => login_id,
702 prog_appid => prog_appid,
703 prog_id => prog_id,
704 req_id => req_id,
705 trans_id => c0rec.TI,
706 error_text => err_text,
707 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
708 msg_name => 'BOM_NULL_REV',
709 err_text => err_text);
710
711 UPDATE mtl_item_revisions_interface
715 IF (ret_code <> 0) THEN
712 SET process_flag = 3
713 WHERE transaction_id = c0rec.TI;
714
716 return(ret_code);
717 END IF;
718 GOTO continue_loop2;
719 END IF;
720
721 /*
722 ** Verify org id
723 */
724 stmt_num := 6;
725 BEGIN
726 SELECT organization_id
727 INTO dummy_id
728 FROM mtl_parameters
729 WHERE organization_id = c0rec.OI;
730 EXCEPTION
731 WHEN No_Data_Found THEN
732 ret_code := INVPUOPI.mtl_log_interface_err(
733 org_id => c0rec.OI,
734 user_id => user_id,
735 login_id => login_id,
736 prog_appid => prog_appid,
737 prog_id => prog_id,
738 req_id => req_id,
739 trans_id => c0rec.TI,
740 error_text => err_text,
741 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
742 msg_name => 'BOM_INVALID_ORG_ID',
743 err_text => err_text);
744
745 UPDATE mtl_item_revisions_interface
746 SET process_flag = 3
747 WHERE transaction_id = c0rec.TI;
748
749 IF (ret_code <> 0) THEN
750 return(ret_code);
751 END IF;
752 GOTO continue_loop2;
753 END;
754
755 /*
756 ** Check if assembly item exists
757 */
758 stmt_num := 7;
759 BEGIN
760 select 1
761 into dummy
762 from mtl_system_items
763 where organization_id = c0rec.OI
764 and inventory_item_id = c0rec.AII;
765 EXCEPTION
766 WHEN No_Data_Found THEN
767 ret_code := INVPUOPI.mtl_log_interface_err(
768 org_id => c0rec.OI,
769 user_id => user_id,
770 login_id => login_id,
771 prog_appid => prog_appid,
772 prog_id => prog_id,
773 req_id => req_id,
774 trans_id => c0rec.TI,
775 error_text => err_text,
776 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
777 msg_name => 'BOM_INV_ITEM_INVALID',
778 err_text => err_text);
779 UPDATE mtl_item_revisions_interface
780 SET process_flag = 3
781 WHERE transaction_id = c0rec.TI;
782
783 IF (ret_code <> 0) THEN
784 return(ret_code);
785 END IF;
786 GOTO continue_loop2;
787 END;
788
789
790 /*
791 ** Verfify Change_Notice
792 */
793 stmt_num := 7.5;
794
795 If (c0rec.CN is not NULL) THEN
796 BEGIN
797 SELECT 1
798 INTO dummy
799 FROM eng_engineering_changes
800 WHERE organization_id = c0rec.OI
801 AND change_notice = c0rec.CN;
802 EXCEPTION
803 WHEN no_data_found THEN
804 ret_code := INVPUOPI.mtl_log_interface_err(
805 org_id => c0rec.OI,
806 user_id => user_id,
807 login_id => login_id,
808 prog_appid => prog_appid,
809 prog_id => prog_id,
810 req_id => req_id,
811 trans_id => c0rec.TI,
812 error_text => err_text,
813 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
814 msg_name => 'ENG_PARENTECO_NOT_EXIST',
815 err_text => err_text);
816 UPDATE mtl_item_revisions_interface
817 SET process_flag = 3
818 WHERE transaction_id = c0rec.TI;
819
820 IF (ret_code <> 0) THEN
821 RETURN(ret_code);
822 END IF;
823 GOTO continue_loop2;
824 END;
825 END IF;
826
827 stmt_num := 8;
828 UPDATE mtl_item_revisions_interface
829 SET process_flag = 99
830 WHERE transaction_id = c0rec.TI;
831
832 <<continue_loop2>>
833 NULL;
834 END LOOP;
835
836 stmt_num := 9;
837 COMMIT;
838 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
839 continue_loop2 := FALSE;
840 END IF;
841
842 END LOOP; -- End c0 cursor
843
844 /*
845 ** For each Item verify revisions are in correct order
846 */
847 commit_cnt := 0;
848
849 FOR c1rec IN c1 LOOP
850 commit_cnt := commit_cnt + 1;
851 stmt_num := 11;
852 ret_code := Check_Revision_Order (
853 org_id => c1rec.OI,
854 assy_id => c1rec.AII,
855 user_id => user_id,
859 req_id => req_id,
856 login_id => login_id,
857 prog_appid => prog_appid,
858 prog_id => prog_id,
860 err_text => err_text);
861 IF (ret_code <> 0) THEN
862 return(ret_code);
863 END IF;
864
865 IF (commit_cnt = G_rows_to_commit) THEN
866 COMMIT;
867 commit_cnt := 0;
868 END IF;
869 END LOOP;
870
871 COMMIT;
872
873 RETURN(0);
874
875 EXCEPTION
876 WHEN others THEN
877 err_text := 'BOM_REVISION_API(Validate-'||stmt_num||') '||substrb(SQLERRM,1,500);
878 RETURN(SQLCODE);
879 END Validate_Item_Revision;
880
881
882 /* ------------------------- Transact_Item_Revision -------------------------*/
883 /*
884 NAME
885 Transact_Item_Revision
886 DESCRIPTION
887 Insert and update item revision data from the interface
888 table, MTL_ITEM_REVISIONS_INTERFACE, into the production table,
889 MTL_ITEM_REVISIONS.
890 REQUIRES
891 prog_appid Program application id
892 prog_id Program id
893 req_id Request id
894 user_id User id
895 login_id Login id
896 MODIFIES
897 MTL_ITEM_REVISIONS_INTERFACE
898 MTL_ITEM_REVISIONS
899 RETURNS
900 0 if successful
901 SQLCODE if error
902 NOTES
903 -----------------------------------------------------------------------------*/
904 FUNCTION Transact_Item_Revision
905 ( user_id NUMBER,
906 login_id NUMBER,
907 prog_appid NUMBER,
908 prog_id NUMBER,
909 req_id NUMBER,
910 err_text OUT VARCHAR2)
911 return integer
912 IS
913 stmt_num NUMBER := 0;
914 continue_loop BOOLEAN := TRUE;
915 commit_cnt NUMBER;
916 /*
917 ** Select "Update" item revision records
918 */
919 CURSOR c1 IS
920 SELECT inventory_item_id III, organization_id OI,
921 revision R, last_update_date LUD, last_updated_by LUB,
922 last_update_login LUL, implementation_date ID,
923 effectivity_date ED,
924 attribute_category AC, attribute1 A1, attribute2 A2,
925 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
926 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
927 attribute11 A11, attribute12 A12, attribute13 A13,
928 attribute14 A14, attribute15 A15, request_id RI,
929 program_application_id PAI, program_id PI,
930 program_update_date PUD, description D, transaction_id TI
931 FROM mtl_item_revisions_interface
932 WHERE process_flag = 4
933 AND transaction_type = G_Update
934 AND rownum < G_rows_to_commit;
935 BEGIN
936 /*
937 ** Insert Item Revisions
938 */
939 stmt_num := 1;
940 LOOP
941 INSERT INTO mtl_item_revisions
942 (
943 INVENTORY_ITEM_ID,
944 ORGANIZATION_ID,
945 REVISION,
946 LAST_UPDATE_DATE,
947 LAST_UPDATED_BY,
948 CREATION_DATE,
949 CREATED_BY,
950 LAST_UPDATE_LOGIN,
951 CHANGE_NOTICE,
952 IMPLEMENTATION_DATE,
953 EFFECTIVITY_DATE,
954 ATTRIBUTE_CATEGORY,
955 ATTRIBUTE1,
956 ATTRIBUTE2,
957 ATTRIBUTE3,
958 ATTRIBUTE4,
959 ATTRIBUTE5,
960 ATTRIBUTE6,
961 ATTRIBUTE7,
962 ATTRIBUTE8,
963 ATTRIBUTE9,
964 ATTRIBUTE10,
965 ATTRIBUTE11,
966 ATTRIBUTE12,
967 ATTRIBUTE13,
968 ATTRIBUTE14,
969 ATTRIBUTE15,
970 PROGRAM_APPLICATION_ID,
971 PROGRAM_ID,
972 PROGRAM_UPDATE_DATE,
973 REQUEST_ID,
974 DESCRIPTION)
975 SELECT
976 INVENTORY_ITEM_ID,
977 ORGANIZATION_ID,
978 REVISION,
979 LAST_UPDATE_DATE,
980 LAST_UPDATED_BY,
981 CREATION_DATE,
982 CREATED_BY,
983 LAST_UPDATE_LOGIN,
984 CHANGE_NOTICE,
985 IMPLEMENTATION_DATE,
986 EFFECTIVITY_DATE,
987 ATTRIBUTE_CATEGORY,
988 ATTRIBUTE1,
989 ATTRIBUTE2,
990 ATTRIBUTE3,
991 ATTRIBUTE4,
992 ATTRIBUTE5,
993 ATTRIBUTE6,
997 ATTRIBUTE10,
994 ATTRIBUTE7,
995 ATTRIBUTE8,
996 ATTRIBUTE9,
998 ATTRIBUTE11,
999 ATTRIBUTE12,
1000 ATTRIBUTE13,
1001 ATTRIBUTE14,
1002 ATTRIBUTE15,
1003 PROGRAM_APPLICATION_ID,
1004 PROGRAM_ID,
1005 PROGRAM_UPDATE_DATE,
1006 REQUEST_ID,
1007 DESCRIPTION
1008 FROM mtl_item_revisions_interface
1009 WHERE process_flag = 4
1010 and transaction_type = G_Insert
1011 and rownum < 500;
1012
1013 EXIT when SQL%NOTFOUND;
1014
1015 stmt_num := 2;
1016 UPDATE mtl_item_revisions_interface mri
1017 SET process_flag = 7
1018 WHERE process_flag = 4
1019 and transaction_type = G_Insert
1020 and EXISTS (SELECT NULL
1021 FROM mtl_item_revisions mir
1022 WHERE mir.inventory_item_id = mri.inventory_item_id
1023 AND mir.organization_id = mri.organization_id
1024 AND mir.revision = mri.revision);
1025 stmt_num := 3;
1026 COMMIT;
1027 END LOOP;
1028
1029 /*
1030 ** Update Item Revisions
1031 */
1032 stmt_num := 4;
1033 continue_loop := TRUE;
1034 WHILE continue_loop LOOP
1035 commit_cnt := 0;
1036 FOR c1rec IN c1 LOOP
1037 commit_cnt := commit_cnt + 1;
1038 UPDATE mtl_item_revisions
1039 SET last_update_date = c1rec.LUD,
1040 last_updated_by = c1rec.LUB,
1041 last_update_login = c1rec.LUL,
1042 implementation_date = c1rec.ID,
1043 effectivity_date = c1rec.ED,
1044 attribute_category = c1rec.AC,
1045 attribute1 = c1rec.A1,
1046 attribute2 = c1rec.A2,
1047 attribute3 = c1rec.A3,
1048 attribute4 = c1rec.A4,
1049 attribute5 = c1rec.A5,
1050 attribute6 = c1rec.A6,
1051 attribute7 = c1rec.A7,
1052 attribute8 = c1rec.A8,
1053 attribute9 = c1rec.A9,
1054 attribute10 = c1rec.A10,
1055 attribute11 = c1rec.A11,
1056 attribute12 = c1rec.A12,
1057 attribute13 = c1rec.A13,
1058 attribute14 = c1rec.A14,
1059 attribute15 = c1rec.A15,
1060 request_id = c1rec.RI,
1061 program_application_id = c1rec.PAI,
1062 program_id = c1rec.PI,
1063 program_update_date = c1rec.PUD,
1064 description = c1rec.D
1065 WHERE inventory_item_id = c1rec.III
1066 AND organization_id = c1rec.OI
1067 AND revision = c1rec.R;
1068
1069 stmt_num := 5;
1070 UPDATE mtl_item_revisions_interface mri
1071 SET process_flag = 7
1072 WHERE transaction_id = c1rec.TI;
1073 END LOOP;
1074
1075 stmt_num := 6;
1076 COMMIT;
1077 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
1078 continue_loop := FALSE;
1079 END IF;
1080
1081 END LOOP;
1082
1083 RETURN(0);
1084
1085 EXCEPTION
1086 WHEN no_data_found THEN
1087 RETURN(0);
1088 WHEN others THEN
1089 ROLLBACK;
1090 err_text := 'BOM_REVISION_API(Transact-'||stmt_num||') '||substrb(SQLERRM,1,500);
1091 return(SQLCODE);
1092
1093 END Transact_Item_Revision;
1094
1095 /* --------------------------- Import_Item_Revision ----------------------- */
1096 /*
1097 NAME
1098 Import_Item_Revision
1099 DESCRIPTION
1100 Assign, Validate, and Transact the Item Revision record in the
1101 interface table, MTL_ITEM_REVISIONS_INTERFACE.
1102 REQUIRES
1103 err_text out buffer to return error message
1104 MODIFIES
1105 RETURNS
1106 0 if successful
1107 SQLCODE if unsuccessful
1108 NOTES
1109 -----------------------------------------------------------------------------*/
1110 FUNCTION Import_Item_Revision (
1111 org_id NUMBER,
1112 all_org NUMBER := 1,
1113 user_id NUMBER := -1,
1114 login_id NUMBER := -1,
1115 prog_appid NUMBER := -1,
1116 prog_id NUMBER := -1,
1117 req_id NUMBER := -1,
1118 del_rec_flag NUMBER := 1,
1119 err_text IN OUT VARCHAR2
1120 )
1121 return INTEGER
1122 IS
1123 err_msg VARCHAR2(2000);
1124 ret_code NUMBER := 1;
1125 stmt_num NUMBER := 0;
1126 BEGIN
1127 stmt_num := 1;
1128 ret_code := Assign_Item_Revision (
1129 org_id => org_id,
1130 all_org => all_org,
1131 user_id => user_id,
1132 login_id => login_id,
1133 prog_appid => prog_appid,
1134 prog_id => prog_id,
1135 req_id => req_id,
1139 ROLLBACK;
1136 err_text => err_msg);
1137 IF (ret_code <> 0) THEN
1138 err_text := 'Assign_Item_Revision '||substrb(err_msg, 1,1500);
1140 RETURN(ret_code);
1141 END IF;
1142 COMMIT;
1143
1144 stmt_num := 2;
1145 ret_code := Validate_Item_Revision (
1146 org_id => org_id,
1147 all_org => all_org,
1148 user_id => user_id,
1149 login_id => login_id,
1150 prog_appid => prog_appid,
1151 prog_id => prog_id,
1152 req_id => req_id,
1153 err_text => err_msg);
1154 IF (ret_code <> 0) THEN
1155 err_text := 'Validate_Item_Revision '||substrb(err_msg, 1,1500);
1156 ROLLBACK;
1157 RETURN(ret_code);
1158 END IF;
1159 COMMIT;
1160
1161 stmt_num := 3;
1162 ret_code := Transact_Item_Revision (
1163 user_id => user_id,
1164 login_id => login_id,
1165 prog_appid => prog_appid,
1166 prog_id => prog_id,
1167 req_id => req_id,
1168 err_text => err_msg);
1169
1170 IF (ret_code <> 0) THEN
1171 err_text := 'Transact_Item_Revision '||substrb(err_msg, 1,1500);
1172 ROLLBACK;
1173 RETURN(ret_code);
1174 END IF;
1175 COMMIT;
1176
1177 stmt_num := 4;
1178 IF (del_rec_flag = 1) THEN
1179 LOOP
1180 DELETE from mtl_item_revisions_interface
1181 WHERE process_flag = 7
1182 AND rownum < G_rows_to_commit;
1183
1184 EXIT when SQL%NOTFOUND;
1185 COMMIT;
1186 END LOOP;
1187 END IF;
1188
1189 RETURN(0);
1190
1191 EXCEPTION
1192 WHEN others THEN
1193 err_text := 'BOM_REVISION_API(Import-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1194 RETURN(ret_code);
1195 END Import_Item_Revision;
1196
1197
1198 END Bom_Revision_Api;