[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_ORG_ASSIGN_CP
Source
1 PACKAGE BODY INV_ITEM_ORG_ASSIGN_CP AS
2 /* $Header: INVCOSGB.pls 120.4 2010/07/29 14:24:13 ccsingh ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVCOSGB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Spec of INV_ITEM_ORG_ASSIGN_CP |
13 --| |
14 --| HISTORY |
15 --| 09/01/00 vjavli Created |
16 --| 09/12/00 vjavli Updated modified to category_id |
17 --| 10/16/00 vjavli updated swapped the loop for item and |
18 --| organization list. item range cursor |
19 --| modified to select only for master org id |
20 --| 12/11/00 vjavli Signature updated to p_org_hier_level_id |
21 --| 06/02/01 pjuvara Restructured to use Item Open Interface |
22 --| 11/20/01 vjavli new api's implemented to improve the |
23 --| performance |
24 --| 01/23/02 vjavli log exception for org property validation |
25 --| 10/15/02 vjavli Bug#2591335 fix: Build_Item_Cursor modified |
26 --| 11/21/02 vma Improve performance: print debug messages |
27 --| to log only if profile option is enabled |
28 --| 30-Apr-2003 rajkrish sqlBind Issue |
29 --| 31-Aug-2003 vjavli Performance enhancement for Retail Customer |
30 --| re-design to bypass validation phase of Item |
31 --| Import API |
32 --| Bug#3095409 fix |
33 --| 17-Sep-2003 vjavli Performance enhancement completed |
34 --| Found that the program is 5 times faster than |
35 --| before |
36 --| 03-Feb-2004 nkilleda Bug 3306087 fix The Item_Org_Assignment |
37 --| procedure has been modified as follows |
38 --| > Accept a new parameter p_source_org_id of |
39 --| type number. |
40 --| > A new validation for the source organization|
41 --| to have same master as that of the hierarchy|
42 --| origin has been added. |
43 --| > If the source organization is not null, then|
44 --| the list of items is generated from source |
45 --| org. based on the range / category specified|
46 --| Otherwise, the item list is got from the |
47 --| item master org. of the hierarchy origin |
48 --| > x_errbuff and x_retcode should be in order |
49 --| according to AOL standards inorder to |
50 --| display warning and error messages. |
51 --| Otherwise, conc. manager will consider as |
52 --| completed normal eventhough exception raised|
53 --| > Build_Item_Cursor: x_xcenario NOCOPY added |
54 --| for previous version; |
55 --| wait_for_worker: OUT NOCOPY added |
56 --| 06/22/2004 nesoni Bug 2642331. Interface of procedure |
57 --| Item_Org_Assignment is modified to accept |
58 --| parameter p_category_set_name as NUMERIC. |
59 --| Earlier it was VARCHAR2. Parameter text |
60 --| is replaced from p_category_set_name to |
61 --| p_category_set_id. |
62 --| 01/17/2004 vjavli Bug#4121148 fix: INVPULI4.assign_status_ |
63 --| attributes added as recommended by BOM team |
64 --| since the signature has been modified by BOM |
65 --| 01/27/2006 vmutyala Bug#4997972 revision label not getting copied|
66 --+======================================================================*/
67
68 --===================
69 -- GLOBALS
70 --===================
71
72 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_ORG_ASSIGN_CP';
73 G_SLEEP_TIME NUMBER := 15;
74
75 g_submit_failure_exc EXCEPTION;
76
77 TYPE g_request_tbl_type IS TABLE OF NUMBER
78 INDEX BY BINARY_INTEGER;
79 TYPE g_item_cur_type IS REF CURSOR;
80
81 g_unit_test_mode BOOLEAN := FALSE;
82 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
83
84
85 --===================
86 -- PRIVATE PROCEDURES AND FUNCTIONS
87 --===================
88
89 -- ==========================================================================
90 -- PROCEDURE : validate_gl_account PRIVATE
91 -- PARAMETERS: p_org_tbl IN INV_OrgHierarchy_PVT.OrgID_tbl_type
92 -- Organization List of the Hierarchy and Origin
93 --
94 -- x_valid_org_tbl OUT INV_OrgHierarchy_PVT.OrgID_tbl_type
95 -- Shortened Organization List containing only valid organizations
96 --
97 -- COMMENT : Validate GL account info against GL_CODE_COMBINATIONS for each
98 -- organization in the Organization List
99 -- Update global pl/sql table G_ORG_GL_REV_TBL set the valid_flag
100 -- to 'Y' for the successful validation
101 -- Otherwise, set to 'N' for NOT a valid code combination
102 -- Short list the organization list only for valid organizations
103 -- =====================================================================
104 PROCEDURE validate_gl_account
105 (p_org_tbl IN INV_OrgHierarchy_PVT.OrgID_tbl_type
106 ,x_valid_org_tbl OUT NOCOPY INV_OrgHierarchy_PVT.OrgID_tbl_type
107 )
108 IS
109
110 -- Get Chart of Accounts Id for the organization
111 CURSOR chart_of_accounts_cur(c_organization_id NUMBER)
112 IS
113 SELECT
114 chart_of_accounts_id
115 FROM
116 gl_sets_of_books
117 , hr_organization_information
118 WHERE set_of_books_id = org_information1
119 AND upper(org_information_context) = upper('Accounting Information')
120 AND organization_id = c_organization_id;
121
122 -- check GL code combination id exists
123 CURSOR ccid_exists_cur(c_chart_of_accounts_id NUMBER
124 ,c_code_combination_id NUMBER)
125 IS
126 SELECT
127 code_combination_id
128 FROM
129 gl_code_combinations
130 WHERE chart_of_accounts_id = c_chart_of_accounts_id
131 AND code_combination_id = c_code_combination_id
132 AND nvl(start_date_active,SYSDATE) <= SYSDATE
133 AND nvl(end_date_active,SYSDATE) >= SYSDATE;
134
135 l_organization_id NUMBER;
136 l_cost_of_sales_account NUMBER;
137 l_encumbrance_account NUMBER;
138 l_sales_account NUMBER;
139 l_expense_account NUMBER;
140 l_chart_of_accounts_id NUMBER;
141 l_code_combination_id NUMBER;
142 l_valid_flag VARCHAR2(1);
143 l_organization_id_idx BINARY_INTEGER;
144
145 BEGIN
146
147 IF G_DEBUG = 'Y' THEN
148 INV_ORGHIERARCHY_PVT.Log
149 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
150 , '> validate_gl_account'
151 );
152 END IF;
153
154 -- initialize short list table
155 x_valid_org_tbl.DELETE;
156
157 FOR l_org_id_idx IN 1 .. p_org_tbl.COUNT LOOP
158
159 l_organization_id := p_org_tbl(l_org_id_idx);
160 l_organization_id_idx := l_organization_id;
161
162 -- Initialize valid flag
163 l_valid_flag := 'N';
164
165 -- get chart of accounts id
166 OPEN chart_of_accounts_cur(l_organization_id);
167 FETCH chart_of_accounts_cur
168 INTO l_chart_of_accounts_id;
169 CLOSE chart_of_accounts_cur;
170
171 -- ============================================
172 -- validate cost of sales account
173 -- ============================================
174 l_cost_of_sales_account :=
175 G_ORG_GL_REV_TBL(l_organization_id_idx).cost_of_sales_account;
176 -- check for NULL value
177 -- do not check for NULL values
178 IF l_cost_of_sales_account IS NOT NULL THEN
179 OPEN ccid_exists_cur(l_chart_of_accounts_id
180 ,l_cost_of_sales_account
181 );
182 FETCH ccid_exists_cur
183 INTO l_code_combination_id;
184
185 IF ccid_exists_cur%FOUND THEN
186 l_valid_flag := 'Y';
187 ELSE
188 l_valid_flag := 'N';
189 IF G_DEBUG = 'Y' THEN
190 INV_ORGHIERARCHY_PVT.Log
191 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
192 ,'Cost of Sales Account NOT valid for organization Id:' || l_organization_id
193 );
194 END IF;
195 END IF;
196 CLOSE ccid_exists_cur;
197 END IF; -- null value check
198
199 -- ========================================
200 -- validate encumbrance account
201 -- ========================================
202 IF l_valid_flag = 'Y' THEN
203
204 l_encumbrance_account :=
205 G_ORG_GL_REV_TBL(l_organization_id_idx).encumbrance_account;
206 -- Do not check for null value
207 IF l_encumbrance_account IS NOT NULL THEN
208 OPEN ccid_exists_cur(l_chart_of_accounts_id
209 ,l_encumbrance_account
210 );
211 FETCH ccid_exists_cur
212 INTO l_code_combination_id;
213
214 IF ccid_exists_cur%FOUND THEN
215 l_valid_flag := 'Y';
216 ELSE
217 l_valid_flag := 'N';
218 IF G_DEBUG = 'Y' THEN
219 INV_ORGHIERARCHY_PVT.Log
220 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
221 ,'Encumbrance Account NOT valid for organization Id:' || l_organization_id
222 );
223 END IF;
224 END IF;
225
226 CLOSE ccid_exists_cur;
227 END IF; -- null value check
228
229 END IF;
230
231 -- =============================================
232 -- validate sales account
233 -- =============================================
234 IF l_valid_flag = 'Y' THEN
235
236 l_sales_account :=
237 G_ORG_GL_REV_TBL(l_organization_id_idx).sales_account;
238 -- Do not check for null value
239 IF l_sales_account IS NOT NULL THEN
240 OPEN ccid_exists_cur(l_chart_of_accounts_id
241 ,l_sales_account
242 );
243 FETCH ccid_exists_cur
244 INTO l_code_combination_id;
245
246 IF ccid_exists_cur%FOUND THEN
247 l_valid_flag := 'Y';
248 ELSE
249 l_valid_flag := 'N';
250 IF G_DEBUG = 'Y' THEN
251 INV_ORGHIERARCHY_PVT.Log
252 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
253 ,'Sales Account NOT valid for organization Id:' || l_organization_id
254 );
255 END IF;
256 END IF;
257 CLOSE ccid_exists_cur;
258 END IF; -- null value check
259
260 END IF;
261
262 -- ==========================================
263 -- validate expense account
264 -- ==========================================
265 IF l_valid_flag = 'Y' THEN
266 l_expense_account :=
267 G_ORG_GL_REV_TBL(l_organization_id_idx).expense_account;
268 -- Do not check for null value
269 IF l_expense_account IS NOT NULL THEN
270 OPEN ccid_exists_cur(l_chart_of_accounts_id
271 ,l_expense_account
272 );
273 FETCH ccid_exists_cur
274 INTO l_code_combination_id;
275
276 IF ccid_exists_cur%FOUND THEN
277 l_valid_flag := 'Y';
278 ELSE
279 l_valid_flag := 'N';
280 IF G_DEBUG = 'Y' THEN
281 INV_ORGHIERARCHY_PVT.Log
282 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
283 ,'Expense Account NOT valid for organization Id:' || l_organization_id
284 );
285 END IF;
286 END IF;
287 CLOSE ccid_exists_cur;
288 END IF; -- null vaue check
289
290 END IF;
291
292 -- assign valid_flag value
293 -- valid flag will be 'Y' if all the gl accounts are valid
294 -- if any of the gl account is NOT valid, then the valid_flag
295 -- is set to 'N'
296 G_ORG_GL_REV_TBL(l_organization_id_idx).valid_flag := l_valid_flag;
297
298 IF G_DEBUG = 'Y' THEN
299 IF l_valid_flag = 'Y' THEN
300 INV_ORGHIERARCHY_PVT.Log
301 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
302 , 'GL Account Info Valid for the organization Id:' || l_organization_id
303 );
304 END IF;
305 END IF;
306
307 -- ========================================================
308 -- Short List only valid organizations
309 -- ========================================================
310 IF l_valid_flag = 'Y' THEN
311 x_valid_org_tbl(x_valid_org_tbl.COUNT + 1 )
312 := p_org_tbl(l_org_id_idx);
313 END IF;
314
315 END LOOP;
316
317 IF G_DEBUG = 'Y' THEN
318 INV_ORGHIERARCHY_PVT.Log
319 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
320 , '< validate_gl_account'
321 );
322 END IF;
323
324 END;
325
326
327 -- ====================================================================
328 -- PROCEDURE : Retrieve_gl_rev PRIVATE
329 -- PARAMETERS: p_org_tbl IN INV_OrgHierarchy_PVT.OrgID_tbl_type
330 -- COMMENT : Retrieve GL Account Info and starting revision for each
331 -- organization in the Organization List
332 -- Store the values in global PL/SQL table G_ORG_GL_REV_TBL
333 -- Organization Id itself is the index
334 -- ====================================================================
335 PROCEDURE Retrieve_gl_rev(p_org_tbl IN INV_OrgHierarchy_PVT.OrgID_tbl_type)
336 IS
337
338 -- Cursor to retrieve GL Account Info and starting revision
339 -- of the organization
340 CURSOR gl_account_revision_cur(c_organization_id NUMBER)
341 IS
342 SELECT
343 organization_id
344 , cost_of_sales_account
345 , encumbrance_account
346 , sales_account
347 , expense_account
348 , starting_revision
349 FROM
350 mtl_parameters
351 WHERE organization_id = c_organization_id;
352
353 gl_account_rev_row gl_account_revision_cur%ROWTYPE;
354
355 l_organization_id NUMBER;
356 l_organization_id_idx BINARY_INTEGER;
357
358 BEGIN
359
360 IF G_DEBUG = 'Y' THEN
361 INV_ORGHIERARCHY_PVT.Log
362 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
363 , '> Retrieve_gl_rev'
364 );
365 END IF;
366
367 FOR l_org_id_idx IN 1 .. p_org_tbl.COUNT LOOP
368
369 l_organization_id := p_org_tbl(l_org_id_idx);
370 l_organization_id_idx := l_organization_id;
371
372 OPEN gl_account_revision_cur(l_organization_id);
373 FETCH gl_account_revision_cur
374 INTO gl_account_rev_row;
375
376 CLOSE gl_account_revision_cur;
377
378 G_ORG_GL_REV_TBL(l_organization_id_idx).organization_id :=
379 gl_account_rev_row.organization_id;
380 G_ORG_GL_REV_TBL(l_organization_id_idx).cost_of_sales_account :=
381 gl_account_rev_row.cost_of_sales_account;
382 G_ORG_GL_REV_TBL(l_organization_id_idx).encumbrance_account :=
383 gl_account_rev_row.encumbrance_account;
384 G_ORG_GL_REV_TBL(l_organization_id_idx).sales_account :=
385 gl_account_rev_row.sales_account;
386 G_ORG_GL_REV_TBL(l_organization_id_idx).expense_account :=
387 gl_account_rev_row.expense_account;
388 G_ORG_GL_REV_TBL(l_organization_id_idx).starting_revision :=
389 gl_account_rev_row.starting_revision;
390
391 END LOOP;
392
393 IF G_DEBUG = 'Y' THEN
394 INV_ORGHIERARCHY_PVT.Log
395 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
396 , 'Org GL Revision table count: ' || G_ORG_GL_REV_TBL.COUNT
397 );
398 END IF;
399
400 END; -- Retrieve_gl_rev
401
402
403 --========================================================================
404 -- PROCEDURE : Build_Item_Cursor PRIVATE
405 -- PARAMETERS: p_cat_structure_id IN Category flexfield
406 -- structure ID
407 -- x_item_cursor OUT NOCOPY item cursor statement
408 -- COMMENT : This procedure builds the item cursor statement. This statement
409 -- needs to be built at run time (dynamic SQL) because of the
410 -- dynamic nature of the System Item and Category flexfields.
411 --=========================================================================
412 /* Following method interface has been modified to incorporate CategorySetId as filter criteria.
413 * Added parameter p_category_set_id to method Build_Item_Cursor. Bug: 2642331
414 */
415 PROCEDURE Build_Item_Cursor
416 ( p_category_set_id IN NUMBER --Changed data type from VARCHAR2 to NUMBER. Bug:2642331
417 , p_cat_structure_id IN NUMBER
418 , p_cat_from IN VARCHAR2
419 , p_cat_to IN VARCHAR2
420 , p_item_from IN VARCHAR2
421 , p_item_to IN VARCHAR2
422 , p_master_org_id IN NUMBER
423 , x_item_cursor IN OUT NOCOPY VARCHAR2
424 , x_scenario OUT NOCOPY VARCHAR2
425 )
426 IS
427 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
428 l_structure_rec FND_FLEX_KEY_API.structure_type;
429 l_segment_rec FND_FLEX_KEY_API.segment_type;
430 l_segment_tbl FND_FLEX_KEY_API.segment_list;
431 l_segment_number NUMBER;
432 l_mstk_segs VARCHAR2(850);
433 l_mcat_segs VARCHAR2(850);
434 l_mcat_f VARCHAR2(2000);
435 l_mcat_w1 VARCHAR2(2000);
436 l_mcat_w2 VARCHAR2(2000);
437 l_mstk_w VARCHAR2(2000);
438
439 l_item_scenario VARCHAR2(2);
440 l_cat_scenario VARCHAR2(2);
441 BEGIN
442
443 IF G_DEBUG = 'Y' THEN
444 INV_ORGHIERARCHY_PVT.Log
445 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
446 , '> Build_Item_Cursor'
447 );
448 END IF;
449
450 FND_FLEX_KEY_API.set_session_mode('customer_data');
451
452 -- retrieve system item concatenated flexfield
453 l_mstk_segs := '';
454 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
455 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
456 FND_FLEX_KEY_API.get_segments
457 ( flexfield => l_flexfield_rec
458 , structure => l_structure_rec
459 , nsegments => l_segment_number
460 , segments => l_segment_tbl
461 );
462 FOR l_idx IN 1..l_segment_number LOOP
463 l_segment_rec := FND_FLEX_KEY_API.find_segment
464 ( l_flexfield_rec
465 , l_structure_rec
466 , l_segment_tbl(l_idx)
467 );
468 l_mstk_segs := l_mstk_segs ||'msi.'||l_segment_rec.column_name;
469 IF l_idx < l_segment_number THEN
470 l_mstk_segs := l_mstk_segs||'||'||''''||l_structure_rec.segment_separator||''''||'||';
471 END IF;
472 END LOOP;
473
474 IF G_DEBUG = 'Y' THEN
475 INV_ORGHIERARCHY_PVT.Log
476 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
477 , 'item flexfield segments:'||l_mstk_segs
478 );
479 END IF;
480
481 -- retrieve item category concatenated flexfield
482 l_mcat_segs := '';
483 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MCAT');
484 l_structure_rec := FND_FLEX_KEY_API.find_structure
485 ( l_flexfield_rec
486 , p_cat_structure_id
487 );
488 FND_FLEX_KEY_API.get_segments
489 ( flexfield => l_flexfield_rec
490 , structure => l_structure_rec
491 , nsegments => l_segment_number
492 , segments => l_segment_tbl
493 );
494 FOR l_idx IN 1..l_segment_number LOOP
495 l_segment_rec := FND_FLEX_KEY_API.find_segment
496 ( l_flexfield_rec
497 , l_structure_rec
498 , l_segment_tbl(l_idx)
499 );
500 l_mcat_segs := l_mcat_segs ||'mc.'||l_segment_rec.column_name;
501 IF l_idx < l_segment_number THEN
502 l_mcat_segs := l_mcat_segs||'||'||''''||
503 l_structure_rec.segment_separator||''''||'||';
504 END IF;
505 END LOOP;
506
507 IF G_DEBUG = 'Y' THEN
508 INV_ORGHIERARCHY_PVT.Log
509 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
510 , 'category flexfield segments:'||l_mcat_segs
511 );
512 END IF;
513
514 IF p_item_from IS NOT NULL AND p_item_to IS NOT NULL THEN
515 l_mstk_w := ' AND '||l_mstk_segs||
516 ' BETWEEN :b_item_from AND :b_item_to ';
517
518 l_item_scenario := 'i1';
519
520 ELSIF p_item_from IS NOT NULL AND p_item_to IS NULL THEN
521 l_mstk_w := ' AND '||l_mstk_segs||' >= :b_item_from ';
522
523 l_item_scenario := 'i2';
524
525 ELSIF p_item_from IS NULL AND p_item_to IS NOT NULL THEN
526 l_mstk_w := ' AND '||l_mstk_segs||' <= :b_item_to ';
527
528 l_item_scenario := 'i3';
529
530 ELSE
531 l_mstk_w := NULL;
532
533 l_item_scenario := 'i4';
534
535 END IF;
536
537 /* Following dynamic From clause and Where clasue have been modified
538 to incorporate CategorySetId as filter criteria.
539 Bug: 2642331
540 l_mcat_f := ', mtl_item_categories mic, mtl_categories_b mc';
541 l_mcat_w1 := ' AND msi.inventory_item_id = mic.inventory_item_id'||
542 ' AND msi.organization_id = mic.organization_id' ||
543 ' AND mic.category_id = mc.category_id' ||
544 ' AND mc.structure_id = :b_cat_structure_id ';
545 */
546 l_mcat_f := ', mtl_item_categories mic, mtl_categories_b mc ';
547 l_mcat_w1 := ' AND msi.inventory_item_id = mic.inventory_item_id'||
548 ' AND msi.organization_id = mic.organization_id' ||
549 ' AND mic.category_id = mc.category_id' ||
550 ' AND mc.structure_id = :b_cat_structure_id ' ||
551 ' AND mic.category_set_id = :b_category_set_id ';
552
553 IF p_cat_from IS NOT NULL AND p_cat_to IS NOT NULL THEN
554 l_mcat_w2 := ' AND '||l_mcat_segs||
555 ' BETWEEN :b_cat_from AND :b_cat_to ';
556
557 l_cat_scenario := 'c1' ;
558
559 ELSIF p_cat_from IS NOT NULL AND p_cat_to IS NULL THEN
560 l_mcat_w2 := ' AND '||l_mcat_segs||' >= :b_cat_from ';
561
562 l_cat_scenario := 'c2' ;
563
564 ELSIF p_cat_from IS NULL AND p_cat_to IS NOT NULL THEN
565 l_mcat_w2 := ' AND '||l_mcat_segs||' <= :b_cat_to ';
566
567 l_cat_scenario := 'c3' ;
568
569 ELSE
570
571 l_mcat_f := NULL;
572 l_mcat_w1 := NULL;
573 l_mcat_w2 := NULL;
574
575 l_cat_scenario := 'c4' ;
576 END IF;
577
578 x_scenario := null;
579 x_scenario := l_item_scenario || l_cat_scenario ;
580
581 IF G_DEBUG = 'Y' THEN
582 INV_ORGHIERARCHY_PVT.Log
583 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
584 , 'l_item_scenario => '|| l_item_scenario
585 );
586
587 INV_ORGHIERARCHY_PVT.Log
588 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
589 , 'l_cat_scenario => '|| l_cat_scenario
590 );
591
592 INV_ORGHIERARCHY_PVT.Log
593 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
594 , 'before cursor , x_scenario => '|| x_scenario
595 );
596 END IF;
597
598 x_item_cursor := 'SELECT msi.inventory_item_id' ||
599 ' FROM mtl_system_items_b msi' ||
600 l_mcat_f ||
601 ' WHERE msi.organization_id = :b_master_org_id ' ||
602 l_mstk_w ||
603 l_mcat_w1 ||
604 l_mcat_w2;
605
606 IF G_DEBUG = 'Y' THEN
607 INV_ORGHIERARCHY_PVT.Log
608 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
609 , SUBSTR(x_item_cursor, 1, 250)
610 );
611 INV_ORGHIERARCHY_PVT.Log
612 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
613 , SUBSTR(x_item_cursor, 251, 500)
614 );
615 INV_ORGHIERARCHY_PVT.Log
616 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
617 , SUBSTR(x_item_cursor, 501, 750)
618 );
619
620 INV_ORGHIERARCHY_PVT.Log
621 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
622 , '< Build_Item_Cursor'
623 );
624 END IF;
625
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 IF G_DEBUG = 'Y' THEN
630 INV_ORGHIERARCHY_PVT.Log
631 ( INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
632 , FND_FLEX_KEY_API.message
633 );
634 END IF;
635 RAISE;
636
637 END Build_Item_Cursor;
638
639
640 --========================================================================
641 -- FUNCTION : Get_Master_Org PRIVATE
642 -- PARAMETERS: p_org_hier_origin_id IN Organization Hierarchy
643 -- origin Id
644 -- RETURNS : NUMBER
645 -- COMMENT : This function returns the ID of the master organization
646 -- common to all the organizations in the hierarchy.
647 --=========================================================================
648 FUNCTION Get_Master_Org
649 ( p_org_hier_origin_id IN NUMBER
650 )
651 RETURN NUMBER
652 IS
653 l_master_org_id NUMBER;
654 BEGIN
655
656 IF G_DEBUG = 'Y' THEN
657 INV_ORGHIERARCHY_PVT.Log
658 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
659 , '> Get_Master_Org'
660 );
661 END IF;
662
663 SELECT master_organization_id
664 INTO l_master_org_id
665 FROM mtl_parameters
666 WHERE organization_id = p_org_hier_origin_id;
667
668 IF G_DEBUG = 'Y' THEN
669 INV_ORGHIERARCHY_PVT.Log
670 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
671 , '< Get_Master_Org'
672 );
673 END IF;
674
675 RETURN l_master_org_id;
676
677 END Get_Master_Org;
678
679 --========================================================================
680 -- FUNCTION : Get_Set_Process_ID PRIVATE
681 -- PARAMETERS: None
682 -- RETURNS : NUMBER
683 -- COMMENT : This function returns the next set process ID to be used to
684 -- run the Item Open Interface
685 --=========================================================================
686 FUNCTION Get_Set_Process_ID
687 RETURN NUMBER
688 IS
689 l_set_process_id NUMBER;
690 BEGIN
691
692 IF G_DEBUG = 'Y' THEN
693 INV_ORGHIERARCHY_PVT.Log
694 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
695 , '> Get_Set_Process_ID'
696 );
697 END IF;
698
699 SELECT mtl_system_items_intf_sets_s.NEXTVAL
700 INTO l_set_process_id
701 FROM dual;
702
703 IF G_DEBUG = 'Y' THEN
704 INV_ORGHIERARCHY_PVT.Log
705 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
706 , '< Get_Set_Process_ID'
707 );
708 END IF;
709
710 RETURN l_set_process_id;
711
712 END Get_Set_Process_ID;
713
714
715 --========================================================================
716 -- FUNCTION : Has_Worker_Completed PRIVATE
717 -- PARAMETERS: p_request_id IN NUMBER
718 -- RETURNS : BOOLEAN
719 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
720 -- has completed; FALSE otherwise
721 --=========================================================================
722 FUNCTION Has_Worker_Completed
723 ( p_request_id IN NUMBER
724 )
725 RETURN BOOLEAN
726 IS
727 l_count NUMBER;
728 l_result BOOLEAN;
729 BEGIN
730
731 IF G_DEBUG = 'Y' THEN
732 INV_ORGHIERARCHY_PVT.Log
733 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
734 , '> Has_Worker_Completed'
735 );
736 END IF;
737
738 SELECT COUNT(*)
739 INTO l_count
740 FROM fnd_concurrent_requests
741 WHERE request_id = p_request_id
742 AND phase_code = 'C';
743
744 IF l_count = 1 THEN
745 l_result := TRUE;
746 ELSE
747 l_result := FALSE;
748 END IF;
749
750 IF G_DEBUG = 'Y' THEN
751 INV_ORGHIERARCHY_PVT.Log
752 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
753 , '< Has_Worker_Completed'
754 );
755 END IF;
756
757 RETURN l_result;
758
759 END Has_Worker_Completed;
760
761
762 --========================================================================
763 -- PROCEDURE : Wait_For_Worker PRIVATE
764 -- PARAMETERS: p_workers IN workers' request ID
765 -- x_worker_idx OUT position in p_workers of the
766 -- completed worked
767 -- COMMENT : This procedure polls the submitted workers and suspend
768 -- the program till the completion of one of them; it returns
769 -- the completed worker through x_worker_idx
770 --=========================================================================
771 PROCEDURE Wait_For_Worker
772 ( p_workers IN g_request_tbl_type
773 , x_worker_idx OUT NOCOPY BINARY_INTEGER
774 )
775 IS
776 l_done BOOLEAN;
777 BEGIN
778
779 IF G_DEBUG = 'Y' THEN
780 INV_ORGHIERARCHY_PVT.Log
781 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
782 , '> Wait_For_Worker'
783 );
784 END IF;
785
786 l_done := FALSE;
787
788 WHILE (NOT l_done) LOOP
789
790 FOR l_Idx IN 1..p_workers.COUNT LOOP
791
792 IF Has_Worker_Completed(p_workers(l_Idx)) THEN
793 l_done := TRUE;
794 x_worker_idx := l_Idx;
795 EXIT;
796 END IF;
797
798 END LOOP;
799
800 IF (NOT l_done) THEN
801 DBMS_LOCK.sleep(G_SLEEP_TIME);
802 END IF;
803
804 END LOOP;
805
806 IF G_DEBUG = 'Y' THEN
807 INV_ORGHIERARCHY_PVT.Log
808 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
809 , '< Wait_For_Worker'
810 );
811 END IF;
812
813 END Wait_For_Worker;
814
815
816 --========================================================================
817 -- PROCEDURE : Wait_For_All_Workers PRIVATE
818 -- PARAMETERS: p_workers IN workers' request ID
819 -- COMMENT : This procedure polls the submitted workers and suspend
820 -- the program till the completion of all of them.
821 --=========================================================================
822 PROCEDURE Wait_For_All_Workers
823 ( p_workers IN g_request_tbl_type
824 )
825 IS
826 l_done BOOLEAN;
827 BEGIN
828
829 IF G_DEBUG = 'Y' THEN
830 INV_ORGHIERARCHY_PVT.Log
831 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
832 , '> Wait_For_All_Workers'
833 );
834 END IF;
835
836 l_done := FALSE;
837
838 WHILE (NOT l_done) LOOP
839
840 l_done := TRUE;
841
842 FOR l_Idx IN 1..p_workers.COUNT LOOP
843
844 IF NOT Has_Worker_Completed(p_workers(l_Idx)) THEN
845 l_done := FALSE;
846 EXIT;
847 END IF;
848
849 END LOOP;
850
851 IF (NOT l_done) THEN
852 DBMS_LOCK.sleep(G_SLEEP_TIME);
853 END IF;
854
855 END LOOP;
856
857 IF G_DEBUG = 'Y' THEN
858 INV_ORGHIERARCHY_PVT.Log
859 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
860 , '< Wait_For_All_Workers'
861 );
862 END IF;
863
864 END Wait_For_All_Workers;
865
866
867 --========================================================================
868 -- PROCEDURE : Submit_Item_Import PRIVATE
869 -- PARAMETERS: p_organization_id IN an organization
870 -- p_set_process_id IN Set process ID
871 -- x_workers IN OUT NOCOPY workers' request ID
872 -- p_request_count IN max worker number
873 -- COMMENT : This procedure submits the Item Import concurrent program.
874 -- Before submitting the request, it verifies that there are
875 -- enough workers available and wait for the completion of one
876 -- if necessary.
877 -- The list of workers' request ID is updated.
878 --=========================================================================
879 PROCEDURE Submit_Item_Import
880 ( p_organization_id IN NUMBER
881 , p_set_process_id IN NUMBER
882 , x_workers IN OUT NOCOPY g_request_tbl_type
883 , p_request_count IN NUMBER
884 )
885 IS
886 l_worker_idx BINARY_INTEGER;
887 l_request_id NUMBER;
888 BEGIN
889
890 IF G_DEBUG = 'Y' THEN
891 INV_ORGHIERARCHY_PVT.Log
892 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
893 , '> Submit_Item_Import'
894 );
895 END IF;
896
897 IF NOT g_unit_test_mode THEN
898
899 IF x_workers.COUNT < p_request_count THEN
900 -- number of workers submitted so far does not exceed the maximum
901 -- number of workers allowed
902 l_worker_idx := x_workers.COUNT + 1;
903 ELSE
904 -- need to wait for a submitted worker to finish
905 Wait_For_Worker
906 ( p_workers => x_workers
907 , x_worker_idx => l_worker_idx
908 );
909 END IF;
910
911 IF NOT FND_REQUEST.Set_Options
912 ( implicit => 'WARNING'
913 , protected => 'YES'
914 )
915 THEN
916 RAISE g_submit_failure_exc;
917 END IF;
918 x_workers(l_worker_idx):= FND_REQUEST.Submit_Request
919 ( application => 'INV'
920 , program => 'INCOIN'
921 , argument1 => p_organization_id
922 , argument2 => 1
923 , argument3 => /*2 Bug 5962957 to ensure that the validation happens settting this to 1*/ 1
924 , argument4 => 1
925 , argument5 => 1
926 , argument6 => p_set_process_id
927 , argument7 => 1
928 );
929 IF x_workers(l_worker_idx) = 0 THEN
930 RAISE g_submit_failure_exc;
931 END IF;
932
933 END IF;
934
935 COMMIT;
936
937 IF G_DEBUG = 'Y' THEN
938 INV_ORGHIERARCHY_PVT.Log
939 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
940 , '< Submit_Item_Import'
941 );
942 END IF;
943
944 END Submit_Item_Import;
945
946
947 --========================================================================
948 -- PROCEDURE : Filter_Org_List PRIVATE
949 -- PARAMETERS: p_org_tbl IN List of organizations
950 -- p_inventory_item_id IN Item
951 -- x_filtered_org_tbl IN OUT NOCOPY Filtered list of orgs
952 -- COMMENT : This procedure returns a shortened organization list
953 -- where the organization to which a given item is already
954 -- assigned are removed.
955 --=========================================================================
956 PROCEDURE Filter_Org_List
957 ( p_org_tbl IN INV_ORGHIERARCHY_PVT.OrgID_tbl_type
958 , p_inventory_item_id IN NUMBER
959 , x_filtered_org_tbl IN OUT NOCOPY INV_ORGHIERARCHY_PVT.OrgID_tbl_type
960 )
961 IS
962 l_count NUMBER;
963 BEGIN
964
965 IF G_DEBUG = 'Y' THEN
966 INV_ORGHIERARCHY_PVT.Log
967 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
968 , '> Filter_Org_List'
969 );
970 END IF;
971
972 x_filtered_org_tbl.DELETE;
973
974 FOR l_Idx IN 1..p_org_tbl.COUNT
975 LOOP
976
977 SELECT COUNT(*)
978 INTO l_count
979 FROM mtl_system_items
980 WHERE organization_id = p_org_tbl(l_Idx)
981 AND inventory_item_id = p_inventory_item_id;
982
983 IF l_count = 0 THEN
984 x_filtered_org_tbl(x_filtered_org_tbl.COUNT+1) := p_org_tbl(l_Idx);
985 END IF;
986
987 END LOOP;
988
989 IF G_DEBUG = 'Y' THEN
990 INV_ORGHIERARCHY_PVT.Log
991 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
992 , '< Filter_Org_List'
993 );
994 END IF;
995
996 END Filter_Org_List;
997
998 --========================================================================
999 -- PROCEDURE : Determine_Return_Code PRIVATE
1000 -- PARAMETERS: x_retcode OUT NOCOPY Return code
1001 -- x_errbuff OUT NOCOPY Return message
1002 -- COMMENT : This procedure verifies that all the records have been
1003 -- successfully processed by the Item Open Interface program and
1004 -- returns a warning in case of failure.
1005 --=========================================================================
1006 PROCEDURE Determine_Return_Code
1007 ( x_retcode OUT NOCOPY VARCHAR2
1008 , x_errbuff OUT NOCOPY VARCHAR2
1009 )
1010 IS
1011 l_error_count NUMBER;
1012 BEGIN
1013
1014 IF G_DEBUG = 'Y' THEN
1015 INV_ORGHIERARCHY_PVT.Log
1016 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1017 , '> Determine_Return_Code'
1018 );
1019 END IF;
1020
1021 -- Check for any left over records
1022 -- If the item record is successful, that record will be deleted
1023 -- from the interface tables
1024 -- Otherwise, that record exists in the interface tables
1025 SELECT COUNT(*)
1026 INTO l_error_count
1027 FROM mtl_system_items_interface
1028 WHERE request_id = FND_GLOBAL.conc_request_id
1029 /*AND process_flag = 4; Bug 5962957 Changing this to 1 since the records are passed in as process_flag 1*/
1030 AND process_flag = 1;
1031
1032 IF l_error_count > 0 THEN
1033 x_retcode := RETCODE_WARNING;
1034 FND_MESSAGE.Set_Name('INV', 'INV_MGD_ITEM_ORG_ASSIGN_WARN');
1035 FND_MESSAGE.Set_Token('RECORD_NUMBER', l_error_count);
1036 x_errbuff := FND_MESSAGE.Get;
1037 ELSE
1038 x_retcode := RETCODE_SUCCESS;
1039 x_errbuff := NULL;
1040 END IF;
1041 IF G_DEBUG = 'Y' THEN
1042 INV_ORGHIERARCHY_PVT.Log
1043 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1044 , '< Determine_Return_Code'
1045 );
1046 END IF;
1047
1048 END Determine_Return_Code;
1049
1050 --===================
1051 -- PUBLIC PROCEDURES AND FUNCTIONS
1052 --===================
1053
1054 --========================================================================
1055 -- FUNCTION : Get_cost_of_sales_account PUBLIC
1056 -- COMMENT : This function is to get the Cost of Sales Account from
1057 -- : global pl/sql table: g_org_gl_rev_tbl for the corresponding
1058 -- : organization id
1059 --
1060 -- PRE-COND : none
1061 -- EXCEPTIONS: none
1062 --========================================================================
1063 FUNCTION Get_cost_of_sales_account(p_organization_id IN NUMBER)
1064 RETURN NUMBER
1065 IS
1066
1067 l_organization_id_idx BINARY_INTEGER;
1068 l_cost_of_sales_account NUMBER;
1069
1070 cost_of_sales_no_found_exc EXCEPTION;
1071
1072 BEGIN
1073 l_organization_id_idx := p_organization_id;
1074 IF G_ORG_GL_REV_TBL.EXISTS(l_organization_id_idx) THEN
1075 l_cost_of_sales_account :=
1076 G_ORG_GL_REV_TBL(l_organization_id_idx).cost_of_sales_account;
1077 RETURN(l_cost_of_sales_account);
1078 ELSE
1079 RAISE cost_of_sales_no_found_exc;
1080 END IF;
1081
1082 EXCEPTION
1083 WHEN cost_of_sales_no_found_exc THEN
1084 IF G_DEBUG = 'Y' THEN
1085 INV_ORGHIERARCHY_PVT.Log
1086 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,'Cost of Sales Account NOT found for the organization' || p_organization_id);
1087 END IF;
1088 RAISE;
1089
1090 END;
1091
1092
1093 --========================================================================
1094 -- FUNCTION : Get_encumbrance_account PUBLIC
1095 -- COMMENT : This function is to get the Encumbrance Account from
1096 -- : global pl/sql table: g_org_gl_rev_tbl for the corresponding
1097 -- : organization id
1098 --
1099 -- PRE-COND : none
1100 -- EXCEPTIONS: none
1101 --========================================================================
1102 FUNCTION Get_encumbrance_account(p_organization_id IN NUMBER)
1103 RETURN NUMBER
1104 IS
1105
1106 l_organization_id_idx BINARY_INTEGER;
1107 l_encumbrance_account NUMBER;
1108
1109 encumbrance_account_no_exc EXCEPTION;
1110
1111 BEGIN
1112 l_organization_id_idx := p_organization_id;
1113 IF G_ORG_GL_REV_TBL.EXISTS(l_organization_id_idx) THEN
1114 l_encumbrance_account :=
1115 G_ORG_GL_REV_TBL(l_organization_id_idx).encumbrance_account;
1116 RETURN(l_encumbrance_account);
1117 ELSE
1118 RAISE encumbrance_account_no_exc;
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN encumbrance_account_no_exc THEN
1123 IF G_DEBUG = 'Y' THEN
1124 INV_ORGHIERARCHY_PVT.Log
1125 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,'Encumbrance Account NOT found for the organization' || p_organization_id);
1126 END IF;
1127 RAISE;
1128
1129 END;
1130
1131 --========================================================================
1132 -- FUNCTION : Get_sales_account PUBLIC
1133 -- COMMENT : This function is to get the Sales Account from
1134 -- : global pl/sql table: g_org_gl_rev_tbl for the corresponding
1135 -- : organization id
1136 --
1137 -- PRE-COND : none
1138 -- EXCEPTIONS: none
1139 --========================================================================
1140 FUNCTION Get_sales_account(p_organization_id IN NUMBER)
1141 RETURN NUMBER
1142 IS
1143
1144 l_organization_id_idx BINARY_INTEGER;
1145 l_sales_account NUMBER;
1146
1147 sales_account_no_found_exc EXCEPTION;
1148
1149 BEGIN
1150 l_organization_id_idx := p_organization_id;
1151 IF G_ORG_GL_REV_TBL.EXISTS(l_organization_id_idx) THEN
1152 l_sales_account :=
1153 G_ORG_GL_REV_TBL(l_organization_id_idx).sales_account;
1154 RETURN(l_sales_account);
1155 ELSE
1156 RAISE sales_account_no_found_exc;
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN sales_account_no_found_exc THEN
1161 IF G_DEBUG = 'Y' THEN
1162 INV_ORGHIERARCHY_PVT.Log
1163 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,'Sales Account NOT found for the organization' || p_organization_id);
1164 END IF;
1165 RAISE;
1166
1167 END;
1168
1169 --========================================================================
1170 -- FUNCTION : Get_expense_account PUBLIC
1171 -- COMMENT : This function is to get the Expense Account from
1172 -- : global pl/sql table: g_org_gl_rev_tbl for the corresponding
1173 -- : organization id
1174 --
1175 -- PRE-COND : none
1176 -- EXCEPTIONS: none
1177 --========================================================================
1178 FUNCTION Get_expense_account(p_organization_id IN NUMBER)
1179 RETURN NUMBER
1180 IS
1181
1182 l_organization_id_idx BINARY_INTEGER;
1183 l_expense_account NUMBER;
1184
1185 expense_account_no_found_exc EXCEPTION;
1186
1187 BEGIN
1188 l_organization_id_idx := p_organization_id;
1189 IF G_ORG_GL_REV_TBL.EXISTS(l_organization_id_idx) THEN
1190 l_expense_account :=
1191 G_ORG_GL_REV_TBL(l_organization_id_idx).expense_account;
1192 RETURN(l_expense_account);
1193 ELSE
1194 RAISE expense_account_no_found_exc;
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN expense_account_no_found_exc THEN
1199 IF G_DEBUG = 'Y' THEN
1200 INV_ORGHIERARCHY_PVT.Log
1201 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,'Expense Account NOT found for the organization Id:' || p_organization_id);
1202 END IF;
1203 RAISE;
1204
1205 END;
1206
1207 --========================================================================
1208 -- FUNCTION : Get_start_revision PUBLIC
1209 -- COMMENT : This function is to get the starting revision from
1210 -- : global pl/sql table: g_org_gl_rev_tbl for the corresponding
1211 -- : organization id
1212 --
1213 -- PRE-COND : none
1214 -- EXCEPTIONS: none
1215 --========================================================================
1216 FUNCTION Get_start_revision(p_organization_id IN NUMBER)
1217 RETURN VARCHAR2
1218 IS
1219
1220 l_organization_id_idx BINARY_INTEGER;
1221 l_start_revision VARCHAR2(3);
1222
1223 start_revision_no_found_exc EXCEPTION;
1224
1225 BEGIN
1226 l_organization_id_idx := p_organization_id;
1227 IF G_ORG_GL_REV_TBL.EXISTS(l_organization_id_idx) THEN
1228 l_start_revision :=
1229 G_ORG_GL_REV_TBL(l_organization_id_idx).starting_revision;
1230 RETURN(l_start_revision);
1231 ELSE
1232 RAISE start_revision_no_found_exc;
1233 END IF;
1234
1235 EXCEPTION
1236 WHEN start_revision_no_found_exc THEN
1237 IF G_DEBUG = 'Y' THEN
1238 INV_ORGHIERARCHY_PVT.Log
1239 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,'Starting Reivision NOT found for the organization' || p_organization_id);
1240 END IF;
1241 RAISE;
1242
1243 END;
1244
1245 --========================================================================
1246 -- PROCEDURE : Set_Unit_Test_Mode PUBLIC
1247 -- COMMENT : This procedure sets the unit test mode that prevents the
1248 -- program from attempting to submit concurrent requests and
1249 -- enables it to run it from SQL*Plus. The Item Interface will
1250 -- not be run.
1251 --=========================================================================
1252 PROCEDURE Set_Unit_Test
1253 IS
1254 BEGIN
1255 g_unit_test_mode := TRUE;
1256 END Set_Unit_Test;
1257
1258
1259 --========================================================================
1260 -- PROCEDURE : Item_Org_Assignment PUBLIC
1261 -- PARAMETERS: x_errbuff return error messages
1262 -- x_retcode return status
1263 -- ************# Bug 3306087 : added new parameter ***********************
1264 -- p_source_org_id IN Source Organization Id
1265 -- ***********************************************************************
1266 -- p_org_hier_origin_id IN Organization Hierarchy
1267 -- Origin Id
1268 -- p_org_hierarchy_id IN Organization Hierarchy Id
1269 -- where all the organizations for the selected hierarchy origin in
1270 -- each hierarchy share the same item master.
1271 -- p_category_set_id IN Category set id
1272 -- p_category_struct IN Category Structure used by category pair
1273 -- p_category_from IN Item Category name from
1274 -- p_category_to IN Item Category name to
1275 -- p_item_from IN From Item Number
1276 -- p_item_to IN To Item Number
1277 -- p_request_count IN Maximum number of workers
1278 --
1279 -- COMMENT : This is a procedure which creates new items for all the
1280 -- organizations in an hierarchy origin. This also include the
1281 -- hierarchy origin itself.
1282 --=========================================================================
1283 /* Bug 2642331. Interface of procedure Item_Org_Assignment is modified to accept
1284 * parameter p_category_set_name as NUMERIC.Earlier it was VARCHAR2. Parameter text
1285 * is replaced from p_category_set_name to p_category_set_id
1286 */
1287 PROCEDURE Item_Org_Assignment
1288 ( x_errbuff OUT NOCOPY VARCHAR2
1289 , x_retcode OUT NOCOPY VARCHAR2
1290 , p_source_org_id IN NUMBER
1291 , p_org_hier_origin_id IN NUMBER
1292 , p_org_hierarchy_id IN NUMBER
1293 --, p_category_set_name IN VARCHAR2 made it numeric from varchar.
1294 --Parameter text is replaced from p_category_set_name to p_category_set_id Bug:2642331
1295 , p_category_set_id IN NUMBER
1296 , p_category_struct IN NUMBER
1297 , p_category_from IN VARCHAR2
1298 , p_category_to IN VARCHAR2
1299 , p_item_from IN VARCHAR2
1300 , p_item_to IN VARCHAR2
1301 , p_request_count IN NUMBER
1302 )
1303 IS
1304
1305 l_org_tbl INV_OrgHierarchy_PVT.OrgID_tbl_type;
1306 l_filtered_org_tbl INV_OrgHierarchy_PVT.OrgID_tbl_type;
1307 l_org_id NUMBER;
1308 l_src_master NUMBER; -- Source Org. Item Master
1309 l_master_org_id NUMBER;
1310 l_max_batch_size NUMBER;
1311 l_batch_size NUMBER;
1312 l_set_process_id NUMBER;
1313 l_inventory_item_id NUMBER;
1314 l_workers_tbl g_request_tbl_type;
1315 l_min_index BINARY_INTEGER;
1316 l_max_index BINARY_INTEGER;
1317 l_item_cur g_item_cur_type;
1318 l_item_cursor VARCHAR2(4000);
1319 l_property_msg VARCHAR2(2000);
1320 l_hierarchy_name VARCHAR2(30);
1321 l_property VARCHAR2(100);
1322 l_property_flag VARCHAR2(1);
1323
1324 l_hierarchy_validation EXCEPTION;
1325
1326 l_scenario VARCHAR2(30);
1327
1328 -- Variable to store revision_label Bug 4997972
1329 l_rev_label VARCHAR2(80);
1330 -- Variables for Assign Master Defaults
1331 l_return_code INTEGER;
1332 l_err_text VARCHAR2(240);
1333
1334 -- GL Account valid organization List
1335 l_valid_org_tbl INV_OrgHierarchy_PVT.OrgID_tbl_type;
1336
1337 -- Cursor to retrieve primary unit of measure for the item
1338 -- and also to validate with mtl_units_of_measure
1339 CURSOR primary_uom_cur(c_master_org_id NUMBER
1340 ,c_inventory_item_id NUMBER
1341 )
1342 IS
1343 SELECT
1344 msib.primary_uom_code primary_uom_code
1345 , msib.primary_unit_of_measure primary_unit_of_measure
1346 FROM
1347 mtl_system_items_b msib
1348 , mtl_units_of_measure muom
1349 WHERE msib.organization_id = c_master_org_id
1350 AND msib.inventory_item_id = c_inventory_item_id
1351 AND msib.primary_unit_of_measure = muom.unit_of_measure
1352 AND SYSDATE < nvl(muom.disable_date,SYSDATE+1);
1353
1354 primary_uom_row primary_uom_cur%ROWTYPE;
1355
1356 -- Cursor to retrieve a row in items interface table
1357 CURSOR items_interface_cur(c_set_process_id NUMBER
1358 ,c_inventory_item_id NUMBER
1359 ,c_organization_id NUMBER
1360 )
1361 IS
1362 SELECT
1363 rowid
1364 , inventory_item_id
1365 , organization_id
1366 , transaction_id
1367 , set_process_id
1368 FROM
1369 mtl_system_items_interface
1370 WHERE set_process_id = c_set_process_id
1371 AND inventory_item_id = c_inventory_item_id
1372 AND organization_id = c_organization_id
1373 FOR UPDATE;
1374
1375 items_interface_row items_interface_cur%ROWTYPE;
1376
1377 -- Exception for assign master default
1378 assign_master_default_except EXCEPTION;
1379 assign_status_attrib_except EXCEPTION;
1380
1381 --serial_tagging enh -- bug 9913552
1382 Serial_Tagging_Exception EXCEPTION;
1383 x_ret_sts VARCHAR2(1);
1384
1385 BEGIN
1386
1387 IF G_DEBUG = 'Y' THEN
1388 -- initialize log
1389 INV_ORGHIERARCHY_PVT.Log_Initialize;
1390 INV_ORGHIERARCHY_PVT.Log
1391 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1392 , '> Item_Org_Assignment'
1393 );
1394 END IF;
1395
1396 -- initialize the message stack
1397 FND_MSG_PUB.Initialize;
1398
1399 -- get the max batch size from the profile option;
1400 -- default it to 1000 if the profile option is not defined.
1401 l_max_batch_size := NVL( TO_NUMBER
1402 ( FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT')
1403 )
1404 , 1000
1405 );
1406
1407
1408 -- Get Organization List
1409 INV_ORGHIERARCHY_PVT.get_organization_list
1410 ( p_hierarchy_id => p_org_hierarchy_id
1411 , p_origin_org_id => p_org_hier_origin_id
1412 , x_org_id_tbl => l_org_tbl
1413 , p_include_origin => 'Y'
1414 );
1415
1416 -- Validate for the same item master
1417 l_property_flag := INV_ORGHIERARCHY_PVT.
1418 validate_property(l_org_tbl, 'MASTER');
1419
1420 IF G_DEBUG = 'Y' THEN
1421 INV_ORGHIERARCHY_PVT.Log
1422 ( INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1423 ,'Property Flag:' || l_property_flag );
1424 END IF;
1425
1426 -- 3306087 : Start Code Change
1427 -- NKILLEDA : Added validation of master org for new parameter
1428 -- source org. The source org. should have the same
1429 -- master org as all the orgs in the hierarchy.
1430 -- the master org, passed to item cursors for getting
1431 -- the item list, should be set to source master org.
1432 -- if it is not null, otherwise set to the master
1433 -- org of hierarchy origin.
1434 IF l_property_flag = 'Y'
1435 THEN
1436 l_master_org_id := Get_Master_Org(p_org_hier_origin_id);
1437 IF p_source_org_id is not null
1438 THEN
1439 l_src_master := Get_Master_Org(p_source_org_id);
1440 IF l_src_master <> l_master_org_id
1441 THEN
1442 FND_MESSAGE.set_name('INV', 'INV_INVALID_SOURCE_ORG');
1443 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1444 RAISE l_hierarchy_validation;
1445 ELSE
1446 -- assigning source organization to be used to get
1447 -- the list of items in the range specified.
1448 l_master_org_id := p_source_org_id;
1449 END IF;
1450 END IF;
1451 ELSE
1452 -- get hierarchy name
1453 SELECT name
1454 INTO l_hierarchy_name
1455 FROM per_organization_structures
1456 WHERE organization_structure_id = p_org_hierarchy_id;
1457
1458 -- get the hierarchy property text
1459 SELECT meaning
1460 INTO l_property
1461 FROM mfg_lookups
1462 WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
1463 AND lookup_code = 1;
1464
1465 -- raise hiearchy validation failure
1466 -- Set the message, tokens
1467 FND_MESSAGE.set_name('INV', 'INV_MGD_HIER_INVALID_PROPERTY');
1468 FND_MESSAGE.set_token('HIERARCHY', l_hierarchy_name);
1469 FND_MESSAGE.set_token('PROPERTY', l_property);
1470 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1471
1472 RAISE l_hierarchy_validation;
1473
1474 END IF;
1475
1476 -- ====================================================================
1477 -- Retrieve GL Account Info and starting revision for each organization
1478 -- in the Organization List
1479 -- Store the values in global PL/SQL table G_ORG_GL_REV_TBL
1480 -- ====================================================================
1481 Retrieve_gl_rev(p_org_tbl => l_org_tbl);
1482
1483 -- =====================================================================
1484 -- Validate GL account info against GL_CODE_COMBINATIONS for each
1485 -- organization in the Organization List
1486 -- Update global pl/sql table G_ORG_GL_REV_TBL set the valid_flag to 'Y'
1487 -- for the successful validation
1488 -- Otherwise, set to 'N' for NOT a valid code combination
1489 -- Short List only for valid organizations
1490 -- =====================================================================
1491 validate_gl_account(p_org_tbl => l_org_tbl
1492 ,x_valid_org_tbl => l_valid_org_tbl
1493 );
1494
1495 IF G_DEBUG = 'Y' THEN
1496 INV_ORGHIERARCHY_PVT.Log
1497 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1498 , 'Valid Org table size: '|| TO_CHAR(l_valid_org_tbl.COUNT)
1499 );
1500 END IF;
1501
1502 -- get the master organization ID
1503 -- Note: all the organizations in the hierarchy share the same item master
1504 -- 3306087 : Start Code Change
1505 -- NKILLEDA : The items should be sourced from the source organization if
1506 -- source org is passed as parameter, otherwise the items should
1507 -- be assigned from master org to all organizations in hierarchy.
1508 -- The following line is not necessary as master org id is already
1509 -- assigned to source org id or hier origin master.
1510 --
1511 -- l_master_org_id := Get_Master_Org
1512 -- ( p_org_hier_origin_id => p_org_hier_origin_id );
1513 --
1514 -- 3306087 : End Code Change
1515
1516 l_batch_size := 0;
1517 l_set_process_id := Get_Set_Process_ID;
1518
1519 IF G_DEBUG = 'Y' THEN
1520 INV_ORGHIERARCHY_PVT.Log
1521 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1522 , 'About to call Build_Item_Cursor '
1523 );
1524 END IF;
1525
1526 l_scenario := NULL;
1527
1528 /* Following method interface has been modified to incorporate CategorySetId as filter criteria.
1529 * Added parameter p_category_set_id to method Build_Item_Cursor. Bug: 2642331
1530 */
1531 Build_Item_Cursor
1532 ( p_category_set_id => p_category_set_id
1533 , p_cat_structure_id => p_category_struct
1534 , p_cat_from => p_category_from
1535 , p_cat_to => p_category_to
1536 , p_item_from => p_item_from
1537 , p_item_to => p_item_to
1538 , p_master_org_id => l_master_org_id
1539 , x_item_cursor => l_item_cursor
1540 , x_scenario => l_scenario
1541 );
1542
1543 IF G_DEBUG = 'Y' THEN
1544 INV_ORGHIERARCHY_PVT.Log
1545 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1546 , 'Out of Build_Item_Cursor '
1547 );
1548 INV_ORGHIERARCHY_PVT.Log
1549 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1550 , 'l_scenario => '|| l_scenario
1551 );
1552 END IF;
1553
1554 -- List of items for the selected item range, category range
1555 -- and for the master organization of the hierarchy level id
1556
1557 --------------------------------------------------------------
1558 --- SQL Bind fix by using the USING command
1559 ---------------------------------------------------------------
1560 /* Bug: 2642331. l_item_cur is provided one more bind parameters for CategorySetId.
1561 */
1562 IF l_scenario = 'i1c1'
1563 THEN
1564 OPEN l_item_cur FOR l_item_cursor
1565 USING
1566 l_master_org_id,
1567 p_item_from, p_item_to,
1568 p_category_struct ,p_category_set_id ,
1569 p_category_from, p_category_to ;
1570
1571 ELSIF l_scenario = 'i2c1'
1572 THEN
1573 OPEN l_item_cur FOR l_item_cursor
1574 USING
1575 l_master_org_id,
1576 p_item_from,
1577 p_category_struct, p_category_set_id ,
1578 p_category_from, p_category_to ;
1579
1580 ELSIF l_scenario = 'i3c1'
1581 THEN
1582 OPEN l_item_cur FOR l_item_cursor
1583 USING
1584 l_master_org_id,
1585 p_item_to,
1586 p_category_struct ,p_category_set_id ,
1587 p_category_from, p_category_to ;
1588
1589 ELSIF l_scenario = 'i4c1'
1590 THEN
1591 OPEN l_item_cur FOR l_item_cursor
1592 USING
1593 l_master_org_id,
1594 p_category_struct, p_category_set_id ,
1595 p_category_from, p_category_to ;
1596
1597 ELSIF l_scenario = 'i1c2'
1598 THEN
1599 OPEN l_item_cur FOR l_item_cursor
1600 USING
1601 l_master_org_id,
1602 p_item_from, p_item_to,
1603 p_category_struct , p_category_set_id ,
1604 p_category_from ;
1605
1606 ELSIF l_scenario = 'i2c2'
1607 THEN
1608 OPEN l_item_cur FOR l_item_cursor
1609 USING
1610 l_master_org_id,
1611 p_item_from,
1612 p_category_struct ,p_category_set_id ,
1613 p_category_from ;
1614
1615 ELSIF l_scenario = 'i3c2'
1616 THEN
1617 OPEN l_item_cur FOR l_item_cursor
1618 USING
1619 l_master_org_id,
1620 p_item_to,
1621 p_category_struct ,p_category_set_id ,
1622 p_category_from ;
1623
1624 ELSIF l_scenario = 'i4c2'
1625 THEN
1626 OPEN l_item_cur FOR l_item_cursor
1627 USING
1628 l_master_org_id,
1629 p_category_struct, p_category_set_id ,
1630 p_category_from ;
1631
1632 ELSIF l_scenario = 'i1c3'
1633 THEN
1634 OPEN l_item_cur FOR l_item_cursor
1635 USING
1636 l_master_org_id,
1637 p_item_from, p_item_to,
1638 p_category_struct ,p_category_set_id ,
1639 p_category_to ;
1640
1641 ELSIF l_scenario = 'i2c3'
1642 THEN
1643 OPEN l_item_cur FOR l_item_cursor
1644 USING
1645 l_master_org_id,
1646 p_item_from,
1647 p_category_struct ,p_category_set_id ,
1648 p_category_to ;
1649
1650 ELSIF l_scenario = 'i3c3'
1651 THEN
1652 OPEN l_item_cur FOR l_item_cursor
1653 USING
1654 l_master_org_id,
1655 p_item_to,
1656 p_category_struct , p_category_set_id ,
1657 p_category_to ;
1658
1659 ELSIF l_scenario = 'i4c3'
1660 THEN
1661 OPEN l_item_cur FOR l_item_cursor
1662 USING
1663 l_master_org_id,
1664 p_category_struct, p_category_set_id ,
1665 p_category_to ;
1666
1667 ELSIF l_scenario = 'i1c4'
1668 THEN
1669 OPEN l_item_cur FOR l_item_cursor
1670 USING
1671 l_master_org_id,
1672 p_item_from, p_item_to ;
1673
1674 ELSIF l_scenario = 'i2c4'
1675 THEN
1676 OPEN l_item_cur FOR l_item_cursor
1677 USING
1678 l_master_org_id,
1679 p_item_from ;
1680
1681 ELSIF l_scenario = 'i3c4'
1682 THEN
1683 OPEN l_item_cur FOR l_item_cursor
1684 USING l_master_org_id,
1685 p_item_to ;
1686
1687 ELSIF l_scenario = 'i4c4'
1688 THEN
1689 OPEN l_item_cur FOR l_item_cursor
1690 USING l_master_org_id;
1691
1692 END IF;
1693
1694 ----------------------------- End BIND Fix ------------------
1695
1696 -- Old code commented --
1697 -- OPEN l_item_cur FOR l_item_cursor; --
1698
1699 IF G_DEBUG = 'Y' THEN
1700 INV_ORGHIERARCHY_PVT.Log
1701 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1702 , 'start LOOP on l_item_cur'
1703 );
1704 END IF;
1705
1706 LOOP
1707
1708 FETCH l_item_cur INTO l_inventory_item_id;
1709 EXIT WHEN l_item_cur%NOTFOUND;
1710
1711 -- ===========================================
1712 -- Get Primary Unit of Measure of the item
1713 -- validate with mtl_units_of_measure
1714 -- ===========================================
1715 OPEN primary_uom_cur(l_master_org_id
1716 ,l_inventory_item_id
1717 );
1718 FETCH primary_uom_cur
1719 INTO primary_uom_row;
1720
1721 IF primary_uom_cur%FOUND THEN
1722
1723
1724 Filter_Org_List
1725 ( p_org_tbl => l_valid_org_tbl
1726 , p_inventory_item_id => l_inventory_item_id
1727 , x_filtered_org_tbl => l_filtered_org_tbl
1728 );
1729
1730 IF G_DEBUG = 'Y' THEN
1731 INV_ORGHIERARCHY_PVT.Log
1732 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1733 , 'Filtered table size: '|| TO_CHAR(l_filtered_org_tbl.COUNT)
1734 );
1735 END IF;
1736
1737 IF l_filtered_org_tbl.COUNT > 0 THEN
1738
1739 l_min_index := 1;
1740
1741 IF l_filtered_org_tbl.COUNT > (l_max_batch_size - l_batch_size) THEN
1742 l_max_index := l_max_batch_size - l_batch_size;
1743 ELSE
1744 l_max_index := l_filtered_org_tbl.COUNT;
1745 END IF;
1746
1747 LOOP
1748
1749 IF G_DEBUG = 'Y' THEN
1750 INV_ORGHIERARCHY_PVT.Log
1751 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1752 , 'Loop on a batch'
1753 );
1754
1755 INV_ORGHIERARCHY_PVT.Log
1756 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1757 , 'Min index:'||TO_CHAR(l_min_index)
1758 );
1759 INV_ORGHIERARCHY_PVT.Log
1760 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1761 , 'Max index:'||TO_CHAR(l_max_index)
1762 );
1763 END IF;
1764
1765
1766
1767 FORALL l_Idx IN l_min_index..l_max_index
1768 INSERT INTO mtl_system_items_interface
1769 ( process_flag
1770 , set_process_id
1771 , transaction_type
1772 , inventory_item_id
1773 , organization_id
1774 , primary_uom_code
1775 , primary_unit_of_measure
1776 , cost_of_sales_account
1777 , encumbrance_account
1778 , sales_account
1779 , expense_account
1780 , last_update_date
1781 , last_updated_by
1782 , creation_date
1783 , created_by
1784 , last_update_login
1785 , request_id
1786 , program_application_id
1787 , program_id
1788 , program_update_date
1789 )
1790 VALUES
1791 ( /*4 Bug 5962957 here the validation should happen so changing it to 1*/
1792 1
1793 , l_set_process_id
1794 , 'CREATE'
1795 , l_inventory_item_id
1796 , l_filtered_org_tbl(l_Idx)
1797 , primary_uom_row.primary_uom_code
1798 , primary_uom_row.primary_unit_of_measure
1799 , get_cost_of_sales_account(l_filtered_org_tbl(l_Idx))
1800 , get_encumbrance_account(l_filtered_org_tbl(l_Idx))
1801 , get_sales_account(l_filtered_org_tbl(l_Idx))
1802 , get_expense_account(l_filtered_org_tbl(l_Idx))
1803 , SYSDATE
1804 , FND_GLOBAL.user_id
1805 , SYSDATE
1806 , FND_GLOBAL.user_id
1807 , FND_GLOBAL.login_id
1808 , FND_GLOBAL.conc_request_id
1809 , FND_GLOBAL.prog_appl_id
1810 , FND_GLOBAL.conc_program_id
1811 , SYSDATE
1812 );
1813
1814
1815 -- =====================================================================
1816 -- for that range of index assign master defaults and insert into
1817 -- revision interface table
1818 -- =====================================================================
1819 FOR l_Idx IN l_min_index..l_max_index LOOP
1820
1821 -- Get rowid from items interface table
1822 OPEN items_interface_cur(l_set_process_id
1823 ,l_inventory_item_id
1824 ,l_filtered_org_tbl(l_Idx)
1825 );
1826 FETCH items_interface_cur
1827 INTO items_interface_row;
1828
1829 -- Assign Master Defaults
1830 l_return_code := INVPUTLI.Assign_master_defaults
1831 (Tran_id => NULL
1832 ,Item_id => items_interface_row.inventory_item_id
1833 ,Org_id => items_interface_row.organization_id
1834 ,Master_org_id => l_master_org_id
1835 ,Status_default => NULL
1836 ,Uom_default => NULL
1837 ,Allow_item_desc_flag => NULL
1838 ,Req_required_flag => NULL
1839 ,p_rowid => items_interface_row.rowid
1840 ,Err_text => l_err_text
1841 );
1842
1843 -- error while assigning master defaults
1844 IF l_return_code <> 0 THEN
1845 RAISE assign_master_default_except;
1846 ELSE
1847 -- Bug#4121148 fix: invoke INVPULI4.assign_status_attributes
1848 -- Bug 8549754 vggarg Passed master_org_id containing the value of source org id as parameter.
1849 l_return_code := INVPULI4.assign_status_attributes(
1850 item_id => items_interface_row.inventory_item_id
1851 , org_id => items_interface_row.organization_id
1852 , err_text => l_err_text
1853 , xset_id => l_set_process_id
1854 , p_rowid => items_interface_row.rowid
1855 , master_org_id => l_master_org_id);
1856 if l_return_code <> 0 then
1857 raise assign_status_attrib_except;
1858 end if;
1859 END IF;
1860
1861 -- Serial_tagging -- bug 9913552
1862 IF ( INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => l_inventory_item_id,
1863 p_organization_id => l_master_org_id)=2 ) THEN
1864
1865 /* both p_from_item_id and p_to_item_id will be same in this case */
1866 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
1867 p_from_item_id => l_inventory_item_id,
1868 p_from_org_id => l_master_org_id,
1869 p_to_item_id => items_interface_row.inventory_item_id,
1870 p_to_org_id => items_interface_row.organization_id,
1871 x_return_status => x_ret_sts);
1872
1873 IF x_ret_sts <>FND_API.G_RET_STS_SUCCESS THEN
1874
1875 FND_MESSAGE.set_name('INV', 'INV_COPY_SER_FAIL_UNEXP');
1876 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1877
1878 RAISE Serial_Tagging_Exception;
1879
1880 END IF ;
1881
1882 END IF ;
1883
1884
1885 /* commented for better performance
1886 IF G_DEBUG = 'Y' THEN
1887 INV_ORGHIERARCHY_PVT.Log
1888 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1889 , 'Assign Master default successful:'|| items_interface_row.rowid
1890 || ' Item Id:' || items_interface_row.inventory_item_id ||
1891 ' Org Id:' || items_interface_row.organization_id
1892 );
1893 END IF;
1894 */
1895
1896 /* Bug 4997972 The following statement is for storing revision label and revision */
1897
1898 l_rev_label := get_start_revision(items_interface_row.organization_id);
1899
1900 -- ===========================================================
1901 -- Insert into Revisions interface table
1902 -- ===========================================================
1903 INSERT INTO mtl_item_revisions_interface
1904 ( inventory_item_id
1905 , organization_id
1906 , revision
1907 , revision_label
1908 , implementation_date
1909 , effectivity_date
1910 , transaction_id
1911 , process_flag
1912 , transaction_type
1913 , set_process_id
1914 , last_update_date
1915 , last_updated_by
1916 , creation_date
1917 , created_by
1918 , last_update_login
1919 , request_id
1920 , program_application_id
1921 , program_id
1922 , program_update_date
1923 , revision_id
1924 )
1925 VALUES
1926 (items_interface_row.inventory_item_id
1927 ,items_interface_row.organization_id
1928 ,l_rev_label
1929 ,l_rev_label
1930 ,SYSDATE
1931 ,SYSDATE
1932 ,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1933 ,/*4 Bug 5962957 here the validation should happen so changing it to 1*/ 1
1934 ,'CREATE'
1935 ,l_set_process_id
1936 ,SYSDATE
1937 ,FND_GLOBAL.user_id
1938 ,SYSDATE
1939 ,FND_GLOBAL.user_id
1940 ,FND_GLOBAL.login_id
1941 ,FND_GLOBAL.conc_request_id
1942 ,FND_GLOBAL.prog_appl_id
1943 ,FND_GLOBAL.conc_program_id
1944 ,SYSDATE
1945 ,MTL_ITEM_REVISIONS_B_S.nextval
1946 );
1947
1948 /* commented for better performance
1949 IF G_DEBUG = 'Y' THEN
1950 INV_ORGHIERARCHY_PVT.Log
1951 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1952 , 'Revision interface successful:'|| items_interface_row.rowid
1953 || ' Item Id:' || items_interface_row.inventory_item_id ||
1954 ' Org Id:' || items_interface_row.organization_id
1955 );
1956 END IF;
1957 */
1958
1959 CLOSE items_interface_cur;
1960
1961 END LOOP; -- end loop for that range of index
1962
1963 l_batch_size := l_batch_size + l_max_index - l_min_index + 1;
1964
1965 IF l_batch_size >= l_max_batch_size THEN
1966 Submit_Item_Import
1967 ( p_organization_id => p_org_hier_origin_id
1968 , p_set_process_id => l_set_process_id
1969 , x_workers => l_workers_tbl
1970 , p_request_count => p_request_count
1971 );
1972
1973
1974 l_batch_size := 0;
1975 l_set_process_id := Get_Set_Process_ID;
1976 END IF;
1977
1978 l_min_index := l_max_index + 1;
1979 IF l_filtered_org_tbl.COUNT > (l_max_index + l_max_batch_size) THEN
1980 l_max_index := l_max_index+l_max_batch_size;
1981 ELSE
1982 l_max_index := l_filtered_org_tbl.COUNT;
1983 END IF;
1984
1985 EXIT WHEN (l_min_index > l_filtered_org_tbl.COUNT);
1986
1987 END LOOP; -- end loop of filter org list
1988
1989 IF G_DEBUG = 'Y' THEN
1990 INV_ORGHIERARCHY_PVT.Log
1991 (INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1992 , 'Interface records inserted for Item:' || l_inventory_item_id
1993 || ' '|| 'Process Id:'|| l_set_process_id
1994 );
1995 END IF;
1996
1997 END IF; -- organization list check
1998
1999 ELSE
2000
2001 IF G_DEBUG = 'Y' THEN
2002 INV_ORGHIERARCHY_PVT.Log
2003 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2004 , 'Valid Primary Unit of Measure not found for the item id:'||
2005 l_inventory_item_id
2006 );
2007 END IF;
2008
2009 END IF; -- primary uom check
2010 CLOSE primary_uom_cur;
2011
2012 END LOOP; -- item cursor loop
2013
2014 CLOSE l_item_cur;
2015
2016 -- if there are records posted in the interface table but for which the
2017 -- Item Import program has not been submitted, submit it.
2018 IF l_batch_size > 0 THEN
2019 Submit_Item_Import
2020 ( p_organization_id =>p_org_hier_origin_id
2021 , p_set_process_id => l_set_process_id
2022 , x_workers => l_workers_tbl
2023 , p_request_count => p_request_count
2024 );
2025 END IF;
2026
2027 Wait_For_All_Workers(p_workers => l_workers_tbl);
2028
2029 Determine_Return_Code
2030 ( x_retcode => x_retcode
2031 , x_errbuff => x_errbuff
2032 );
2033
2034 IF G_DEBUG = 'Y' THEN
2035 INV_ORGHIERARCHY_PVT.Log
2036 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2037 , '< Item_Org_Assignment'
2038 );
2039 END IF;
2040
2041 EXCEPTION
2042
2043 WHEN g_submit_failure_exc THEN
2044 FND_MESSAGE.Set_Name('INV', 'INV_UNABLE_TO_SUBMIT_CONC');
2045 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
2046 x_retcode := RETCODE_ERROR;
2047
2048 WHEN l_hierarchy_validation THEN
2049 IF G_DEBUG = 'Y' THEN
2050 INV_ORGHIERARCHY_PVT.Log
2051 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,x_errbuff);
2052 END IF;
2053 x_retcode := RETCODE_ERROR;
2054
2055 WHEN assign_master_default_except THEN
2056 IF G_DEBUG = 'Y' THEN
2057 INV_ORGHIERARCHY_PVT.Log
2058 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,l_err_text);
2059 END IF;
2060 x_errbuff := l_err_text;
2061 x_retcode := l_return_code;
2062
2063 WHEN assign_status_attrib_except THEN
2064 IF G_DEBUG = 'Y' THEN
2065 INV_ORGHIERARCHY_PVT.Log
2066 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,l_err_text);
2067 END IF;
2068 x_errbuff := l_err_text;
2069 x_retcode := l_return_code;
2070 --serial_tagging eng -- bug 9913552
2071 WHEN Serial_Tagging_Exception THEN
2072 IF G_DEBUG = 'Y' THEN
2073 INV_ORGHIERARCHY_PVT.Log
2074 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,x_errbuff);
2075 END IF;
2076 x_retcode := RETCODE_ERROR;
2077
2078 WHEN OTHERS THEN
2079 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2080 THEN
2081 FND_MSG_PUB.Add_Exc_Msg
2082 ( G_PKG_NAME
2083 , 'Item_Org_Assignment'
2084 );
2085 END IF;
2086 x_retcode := RETCODE_ERROR;
2087 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
2088
2089 END Item_Org_Assignment;
2090
2091
2092 END INV_ITEM_ORG_ASSIGN_CP;