[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.1.12000000.2 2007/10/10 12:23:33 jiabraha 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
823 ( p_workers IN g_request_tbl_type
820 -- the program till the completion of all of them.
821 --=========================================================================
822 PROCEDURE Wait_For_All_Workers
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 BEGIN
1382
1383 IF G_DEBUG = 'Y' THEN
1384 -- initialize log
1385 INV_ORGHIERARCHY_PVT.Log_Initialize;
1386 INV_ORGHIERARCHY_PVT.Log
1387 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1388 , '> Item_Org_Assignment'
1389 );
1390 END IF;
1391
1392 -- initialize the message stack
1393 FND_MSG_PUB.Initialize;
1394
1395 -- get the max batch size from the profile option;
1396 -- default it to 1000 if the profile option is not defined.
1397 l_max_batch_size := NVL( TO_NUMBER
1398 ( FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT')
1399 )
1400 , 1000
1401 );
1402
1403
1404 -- Get Organization List
1405 INV_ORGHIERARCHY_PVT.get_organization_list
1406 ( p_hierarchy_id => p_org_hierarchy_id
1407 , p_origin_org_id => p_org_hier_origin_id
1408 , x_org_id_tbl => l_org_tbl
1409 , p_include_origin => 'Y'
1410 );
1411
1412 -- Validate for the same item master
1413 l_property_flag := INV_ORGHIERARCHY_PVT.
1414 validate_property(l_org_tbl, 'MASTER');
1415
1416 IF G_DEBUG = 'Y' THEN
1417 INV_ORGHIERARCHY_PVT.Log
1418 ( INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1419 ,'Property Flag:' || l_property_flag );
1420 END IF;
1421
1422 -- 3306087 : Start Code Change
1423 -- NKILLEDA : Added validation of master org for new parameter
1424 -- source org. The source org. should have the same
1425 -- master org as all the orgs in the hierarchy.
1426 -- the master org, passed to item cursors for getting
1427 -- the item list, should be set to source master org.
1428 -- if it is not null, otherwise set to the master
1429 -- org of hierarchy origin.
1430 IF l_property_flag = 'Y'
1431 THEN
1432 l_master_org_id := Get_Master_Org(p_org_hier_origin_id);
1433 IF p_source_org_id is not null
1434 THEN
1435 l_src_master := Get_Master_Org(p_source_org_id);
1436 IF l_src_master <> l_master_org_id
1437 THEN
1438 FND_MESSAGE.set_name('INV', 'INV_INVALID_SOURCE_ORG');
1439 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1440 RAISE l_hierarchy_validation;
1441 ELSE
1442 -- assigning source organization to be used to get
1443 -- the list of items in the range specified.
1444 l_master_org_id := p_source_org_id;
1445 END IF;
1446 END IF;
1447 ELSE
1448 -- get hierarchy name
1449 SELECT name
1450 INTO l_hierarchy_name
1451 FROM per_organization_structures
1452 WHERE organization_structure_id = p_org_hierarchy_id;
1453
1454 -- get the hierarchy property text
1455 SELECT meaning
1456 INTO l_property
1457 FROM mfg_lookups
1458 WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
1459 AND lookup_code = 1;
1460
1461 -- raise hiearchy validation failure
1462 -- Set the message, tokens
1463 FND_MESSAGE.set_name('INV', 'INV_MGD_HIER_INVALID_PROPERTY');
1464 FND_MESSAGE.set_token('HIERARCHY', l_hierarchy_name);
1465 FND_MESSAGE.set_token('PROPERTY', l_property);
1466 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
1467
1468 RAISE l_hierarchy_validation;
1469
1470 END IF;
1471
1472 -- ====================================================================
1473 -- Retrieve GL Account Info and starting revision for each organization
1474 -- in the Organization List
1475 -- Store the values in global PL/SQL table G_ORG_GL_REV_TBL
1476 -- ====================================================================
1477 Retrieve_gl_rev(p_org_tbl => l_org_tbl);
1478
1479 -- =====================================================================
1480 -- Validate GL account info against GL_CODE_COMBINATIONS for each
1481 -- organization in the Organization List
1482 -- Update global pl/sql table G_ORG_GL_REV_TBL set the valid_flag to 'Y'
1483 -- for the successful validation
1484 -- Otherwise, set to 'N' for NOT a valid code combination
1485 -- Short List only for valid organizations
1486 -- =====================================================================
1487 validate_gl_account(p_org_tbl => l_org_tbl
1488 ,x_valid_org_tbl => l_valid_org_tbl
1489 );
1490
1491 IF G_DEBUG = 'Y' THEN
1492 INV_ORGHIERARCHY_PVT.Log
1493 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1494 , 'Valid Org table size: '|| TO_CHAR(l_valid_org_tbl.COUNT)
1495 );
1496 END IF;
1497
1498 -- get the master organization ID
1499 -- Note: all the organizations in the hierarchy share the same item master
1500 -- 3306087 : Start Code Change
1501 -- NKILLEDA : The items should be sourced from the source organization if
1502 -- source org is passed as parameter, otherwise the items should
1503 -- be assigned from master org to all organizations in hierarchy.
1504 -- The following line is not necessary as master org id is already
1505 -- assigned to source org id or hier origin master.
1506 --
1507 -- l_master_org_id := Get_Master_Org
1508 -- ( p_org_hier_origin_id => p_org_hier_origin_id );
1509 --
1510 -- 3306087 : End Code Change
1511
1512 l_batch_size := 0;
1513 l_set_process_id := Get_Set_Process_ID;
1514
1515 IF G_DEBUG = 'Y' THEN
1516 INV_ORGHIERARCHY_PVT.Log
1517 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1518 , 'About to call Build_Item_Cursor '
1519 );
1520 END IF;
1521
1522 l_scenario := NULL;
1523
1524 /* Following method interface has been modified to incorporate CategorySetId as filter criteria.
1525 * Added parameter p_category_set_id to method Build_Item_Cursor. Bug: 2642331
1526 */
1527 Build_Item_Cursor
1528 ( p_category_set_id => p_category_set_id
1529 , p_cat_structure_id => p_category_struct
1530 , p_cat_from => p_category_from
1531 , p_cat_to => p_category_to
1532 , p_item_from => p_item_from
1533 , p_item_to => p_item_to
1534 , p_master_org_id => l_master_org_id
1535 , x_item_cursor => l_item_cursor
1536 , x_scenario => l_scenario
1537 );
1538
1539 IF G_DEBUG = 'Y' THEN
1540 INV_ORGHIERARCHY_PVT.Log
1541 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1542 , 'Out of Build_Item_Cursor '
1543 );
1544 INV_ORGHIERARCHY_PVT.Log
1545 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1546 , 'l_scenario => '|| l_scenario
1547 );
1548 END IF;
1549
1550 -- List of items for the selected item range, category range
1551 -- and for the master organization of the hierarchy level id
1552
1553 --------------------------------------------------------------
1554 --- SQL Bind fix by using the USING command
1555 ---------------------------------------------------------------
1556 /* Bug: 2642331. l_item_cur is provided one more bind parameters for CategorySetId.
1557 */
1558 IF l_scenario = 'i1c1'
1559 THEN
1560 OPEN l_item_cur FOR l_item_cursor
1561 USING
1562 l_master_org_id,
1563 p_item_from, p_item_to,
1564 p_category_struct ,p_category_set_id ,
1565 p_category_from, p_category_to ;
1566
1567 ELSIF l_scenario = 'i2c1'
1568 THEN
1569 OPEN l_item_cur FOR l_item_cursor
1570 USING
1571 l_master_org_id,
1572 p_item_from,
1573 p_category_struct, p_category_set_id ,
1574 p_category_from, p_category_to ;
1575
1576 ELSIF l_scenario = 'i3c1'
1577 THEN
1578 OPEN l_item_cur FOR l_item_cursor
1579 USING
1580 l_master_org_id,
1581 p_item_to,
1582 p_category_struct ,p_category_set_id ,
1583 p_category_from, p_category_to ;
1584
1585 ELSIF l_scenario = 'i4c1'
1586 THEN
1587 OPEN l_item_cur FOR l_item_cursor
1588 USING
1589 l_master_org_id,
1590 p_category_struct, p_category_set_id ,
1591 p_category_from, p_category_to ;
1592
1593 ELSIF l_scenario = 'i1c2'
1594 THEN
1595 OPEN l_item_cur FOR l_item_cursor
1596 USING
1597 l_master_org_id,
1598 p_item_from, p_item_to,
1599 p_category_struct , p_category_set_id ,
1600 p_category_from ;
1601
1602 ELSIF l_scenario = 'i2c2'
1603 THEN
1604 OPEN l_item_cur FOR l_item_cursor
1605 USING
1606 l_master_org_id,
1607 p_item_from,
1608 p_category_struct ,p_category_set_id ,
1609 p_category_from ;
1610
1611 ELSIF l_scenario = 'i3c2'
1612 THEN
1613 OPEN l_item_cur FOR l_item_cursor
1614 USING
1615 l_master_org_id,
1616 p_item_to,
1617 p_category_struct ,p_category_set_id ,
1618 p_category_from ;
1619
1620 ELSIF l_scenario = 'i4c2'
1621 THEN
1622 OPEN l_item_cur FOR l_item_cursor
1623 USING
1624 l_master_org_id,
1625 p_category_struct, p_category_set_id ,
1626 p_category_from ;
1627
1628 ELSIF l_scenario = 'i1c3'
1629 THEN
1630 OPEN l_item_cur FOR l_item_cursor
1631 USING
1632 l_master_org_id,
1633 p_item_from, p_item_to,
1634 p_category_struct ,p_category_set_id ,
1635 p_category_to ;
1636
1637 ELSIF l_scenario = 'i2c3'
1638 THEN
1639 OPEN l_item_cur FOR l_item_cursor
1640 USING
1641 l_master_org_id,
1642 p_item_from,
1643 p_category_struct ,p_category_set_id ,
1644 p_category_to ;
1645
1646 ELSIF l_scenario = 'i3c3'
1647 THEN
1648 OPEN l_item_cur FOR l_item_cursor
1649 USING
1650 l_master_org_id,
1651 p_item_to,
1652 p_category_struct , p_category_set_id ,
1653 p_category_to ;
1654
1655 ELSIF l_scenario = 'i4c3'
1656 THEN
1657 OPEN l_item_cur FOR l_item_cursor
1658 USING
1659 l_master_org_id,
1660 p_category_struct, p_category_set_id ,
1661 p_category_to ;
1662
1663 ELSIF l_scenario = 'i1c4'
1664 THEN
1665 OPEN l_item_cur FOR l_item_cursor
1666 USING
1667 l_master_org_id,
1668 p_item_from, p_item_to ;
1669
1670 ELSIF l_scenario = 'i2c4'
1671 THEN
1672 OPEN l_item_cur FOR l_item_cursor
1673 USING
1674 l_master_org_id,
1675 p_item_from ;
1676
1677 ELSIF l_scenario = 'i3c4'
1681 p_item_to ;
1678 THEN
1679 OPEN l_item_cur FOR l_item_cursor
1680 USING l_master_org_id,
1682
1683 ELSIF l_scenario = 'i4c4'
1684 THEN
1685 OPEN l_item_cur FOR l_item_cursor
1686 USING l_master_org_id;
1687
1688 END IF;
1689
1690 ----------------------------- End BIND Fix ------------------
1691
1692 -- Old code commented --
1693 -- OPEN l_item_cur FOR l_item_cursor; --
1694
1695 IF G_DEBUG = 'Y' THEN
1696 INV_ORGHIERARCHY_PVT.Log
1697 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1698 , 'start LOOP on l_item_cur'
1699 );
1700 END IF;
1701
1702 LOOP
1703
1704 FETCH l_item_cur INTO l_inventory_item_id;
1705 EXIT WHEN l_item_cur%NOTFOUND;
1706
1707 -- ===========================================
1708 -- Get Primary Unit of Measure of the item
1709 -- validate with mtl_units_of_measure
1710 -- ===========================================
1711 OPEN primary_uom_cur(l_master_org_id
1712 ,l_inventory_item_id
1713 );
1714 FETCH primary_uom_cur
1715 INTO primary_uom_row;
1716
1717 IF primary_uom_cur%FOUND THEN
1718
1719
1720 Filter_Org_List
1721 ( p_org_tbl => l_valid_org_tbl
1722 , p_inventory_item_id => l_inventory_item_id
1723 , x_filtered_org_tbl => l_filtered_org_tbl
1724 );
1725
1726 IF G_DEBUG = 'Y' THEN
1727 INV_ORGHIERARCHY_PVT.Log
1728 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1729 , 'Filtered table size: '|| TO_CHAR(l_filtered_org_tbl.COUNT)
1730 );
1731 END IF;
1732
1733 IF l_filtered_org_tbl.COUNT > 0 THEN
1734
1735 l_min_index := 1;
1736
1737 IF l_filtered_org_tbl.COUNT > (l_max_batch_size - l_batch_size) THEN
1738 l_max_index := l_max_batch_size - l_batch_size;
1739 ELSE
1740 l_max_index := l_filtered_org_tbl.COUNT;
1741 END IF;
1742
1743 LOOP
1744
1745 IF G_DEBUG = 'Y' THEN
1746 INV_ORGHIERARCHY_PVT.Log
1747 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1748 , 'Loop on a batch'
1749 );
1750
1751 INV_ORGHIERARCHY_PVT.Log
1752 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1753 , 'Min index:'||TO_CHAR(l_min_index)
1754 );
1755 INV_ORGHIERARCHY_PVT.Log
1756 ( INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1757 , 'Max index:'||TO_CHAR(l_max_index)
1758 );
1759 END IF;
1760
1761
1762
1763 FORALL l_Idx IN l_min_index..l_max_index
1764 INSERT INTO mtl_system_items_interface
1765 ( process_flag
1766 , set_process_id
1767 , transaction_type
1768 , inventory_item_id
1769 , organization_id
1770 , primary_uom_code
1771 , primary_unit_of_measure
1772 , cost_of_sales_account
1773 , encumbrance_account
1774 , sales_account
1775 , expense_account
1776 , last_update_date
1777 , last_updated_by
1778 , creation_date
1779 , created_by
1780 , last_update_login
1781 , request_id
1782 , program_application_id
1783 , program_id
1784 , program_update_date
1785 )
1786 VALUES
1787 ( /*4 Bug 5962957 here the validation should happen so changing it to 1*/
1788 1
1789 , l_set_process_id
1790 , 'CREATE'
1791 , l_inventory_item_id
1792 , l_filtered_org_tbl(l_Idx)
1793 , primary_uom_row.primary_uom_code
1794 , primary_uom_row.primary_unit_of_measure
1795 , get_cost_of_sales_account(l_filtered_org_tbl(l_Idx))
1796 , get_encumbrance_account(l_filtered_org_tbl(l_Idx))
1797 , get_sales_account(l_filtered_org_tbl(l_Idx))
1798 , get_expense_account(l_filtered_org_tbl(l_Idx))
1799 , SYSDATE
1800 , FND_GLOBAL.user_id
1801 , SYSDATE
1802 , FND_GLOBAL.user_id
1803 , FND_GLOBAL.login_id
1804 , FND_GLOBAL.conc_request_id
1805 , FND_GLOBAL.prog_appl_id
1806 , FND_GLOBAL.conc_program_id
1807 , SYSDATE
1808 );
1809
1810
1811 -- =====================================================================
1812 -- for that range of index assign master defaults and insert into
1813 -- revision interface table
1814 -- =====================================================================
1815 FOR l_Idx IN l_min_index..l_max_index LOOP
1816
1817 -- Get rowid from items interface table
1818 OPEN items_interface_cur(l_set_process_id
1819 ,l_inventory_item_id
1820 ,l_filtered_org_tbl(l_Idx)
1821 );
1822 FETCH items_interface_cur
1823 INTO items_interface_row;
1824
1825 -- Assign Master Defaults
1826 l_return_code := INVPUTLI.Assign_master_defaults
1827 (Tran_id => NULL
1828 ,Item_id => items_interface_row.inventory_item_id
1829 ,Org_id => items_interface_row.organization_id
1830 ,Master_org_id => l_master_org_id
1831 ,Status_default => NULL
1832 ,Uom_default => NULL
1833 ,Allow_item_desc_flag => NULL
1834 ,Req_required_flag => NULL
1835 ,p_rowid => items_interface_row.rowid
1836 ,Err_text => l_err_text
1837 );
1838
1839 -- error while assigning master defaults
1840 IF l_return_code <> 0 THEN
1841 RAISE assign_master_default_except;
1842 ELSE
1843 -- Bug#4121148 fix: invoke INVPULI4.assign_status_attributes
1844 l_return_code := INVPULI4.assign_status_attributes(
1845 item_id => items_interface_row.inventory_item_id
1846 , org_id => items_interface_row.organization_id
1847 , err_text => l_err_text
1848 , xset_id => l_set_process_id
1849 , p_rowid => items_interface_row.rowid);
1850 if l_return_code <> 0 then
1851 raise assign_status_attrib_except;
1852 end if;
1853 END IF;
1854
1855
1856 /* commented for better performance
1857 IF G_DEBUG = 'Y' THEN
1858 INV_ORGHIERARCHY_PVT.Log
1859 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1860 , 'Assign Master default successful:'|| items_interface_row.rowid
1861 || ' Item Id:' || items_interface_row.inventory_item_id ||
1862 ' Org Id:' || items_interface_row.organization_id
1863 );
1864 END IF;
1865 */
1866
1867 /* Bug 4997972 The following statement is for storing revision label and revision */
1868
1869 l_rev_label := get_start_revision(items_interface_row.organization_id);
1870
1871 -- ===========================================================
1872 -- Insert into Revisions interface table
1873 -- ===========================================================
1874 INSERT INTO mtl_item_revisions_interface
1875 ( inventory_item_id
1876 , organization_id
1877 , revision
1878 , revision_label
1879 , implementation_date
1880 , effectivity_date
1881 , transaction_id
1882 , process_flag
1883 , transaction_type
1884 , set_process_id
1885 , last_update_date
1886 , last_updated_by
1887 , creation_date
1888 , created_by
1889 , last_update_login
1890 , request_id
1891 , program_application_id
1892 , program_id
1893 , program_update_date
1894 , revision_id
1895 )
1896 VALUES
1897 (items_interface_row.inventory_item_id
1898 ,items_interface_row.organization_id
1899 ,l_rev_label
1900 ,l_rev_label
1901 ,SYSDATE
1902 ,SYSDATE
1903 ,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1904 ,/*4 Bug 5962957 here the validation should happen so changing it to 1*/ 1
1905 ,'CREATE'
1906 ,l_set_process_id
1907 ,SYSDATE
1908 ,FND_GLOBAL.user_id
1909 ,SYSDATE
1910 ,FND_GLOBAL.user_id
1911 ,FND_GLOBAL.login_id
1912 ,FND_GLOBAL.conc_request_id
1913 ,FND_GLOBAL.prog_appl_id
1914 ,FND_GLOBAL.conc_program_id
1915 ,SYSDATE
1916 ,MTL_ITEM_REVISIONS_B_S.nextval
1917 );
1918
1919 /* commented for better performance
1920 IF G_DEBUG = 'Y' THEN
1921 INV_ORGHIERARCHY_PVT.Log
1922 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1923 , 'Revision interface successful:'|| items_interface_row.rowid
1924 || ' Item Id:' || items_interface_row.inventory_item_id ||
1925 ' Org Id:' || items_interface_row.organization_id
1926 );
1927 END IF;
1928 */
1929
1930 CLOSE items_interface_cur;
1931
1932 END LOOP; -- end loop for that range of index
1933
1934 l_batch_size := l_batch_size + l_max_index - l_min_index + 1;
1935
1936 IF l_batch_size >= l_max_batch_size THEN
1937 Submit_Item_Import
1938 ( p_organization_id => p_org_hier_origin_id
1939 , p_set_process_id => l_set_process_id
1940 , x_workers => l_workers_tbl
1941 , p_request_count => p_request_count
1942 );
1943
1944
1945 l_batch_size := 0;
1946 l_set_process_id := Get_Set_Process_ID;
1947 END IF;
1948
1949 l_min_index := l_max_index + 1;
1950 IF l_filtered_org_tbl.COUNT > (l_max_index + l_max_batch_size) THEN
1951 l_max_index := l_max_index+l_max_batch_size;
1952 ELSE
1953 l_max_index := l_filtered_org_tbl.COUNT;
1954 END IF;
1955
1956 EXIT WHEN (l_min_index > l_filtered_org_tbl.COUNT);
1957
1958 END LOOP; -- end loop of filter org list
1959
1960 IF G_DEBUG = 'Y' THEN
1961 INV_ORGHIERARCHY_PVT.Log
1962 (INV_ORGHIERARCHY_PVT.G_LOG_EVENT
1963 , 'Interface records inserted for Item:' || l_inventory_item_id
1964 || ' '|| 'Process Id:'|| l_set_process_id
1965 );
1966 END IF;
1967
1968 END IF; -- organization list check
1969
1970 ELSE
1971
1972 IF G_DEBUG = 'Y' THEN
1973 INV_ORGHIERARCHY_PVT.Log
1974 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1975 , 'Valid Primary Unit of Measure not found for the item id:'||
1976 l_inventory_item_id
1977 );
1978 END IF;
1979
1980 END IF; -- primary uom check
1981 CLOSE primary_uom_cur;
1982
1983 END LOOP; -- item cursor loop
1984
1985 CLOSE l_item_cur;
1986
1987 -- if there are records posted in the interface table but for which the
1988 -- Item Import program has not been submitted, submit it.
1989 IF l_batch_size > 0 THEN
1990 Submit_Item_Import
1991 ( p_organization_id =>p_org_hier_origin_id
1992 , p_set_process_id => l_set_process_id
1993 , x_workers => l_workers_tbl
1994 , p_request_count => p_request_count
1995 );
1996 END IF;
1997
1998 Wait_For_All_Workers(p_workers => l_workers_tbl);
1999
2000 Determine_Return_Code
2001 ( x_retcode => x_retcode
2002 , x_errbuff => x_errbuff
2003 );
2004
2005 IF G_DEBUG = 'Y' THEN
2006 INV_ORGHIERARCHY_PVT.Log
2007 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2008 , '< Item_Org_Assignment'
2009 );
2010 END IF;
2011
2012 EXCEPTION
2013
2014 WHEN g_submit_failure_exc THEN
2015 FND_MESSAGE.Set_Name('INV', 'INV_UNABLE_TO_SUBMIT_CONC');
2016 x_errbuff := SUBSTR(FND_MESSAGE.Get, 1, 255);
2017 x_retcode := RETCODE_ERROR;
2018
2019 WHEN l_hierarchy_validation THEN
2020 IF G_DEBUG = 'Y' THEN
2021 INV_ORGHIERARCHY_PVT.Log
2022 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,x_errbuff);
2023 END IF;
2024 x_retcode := RETCODE_ERROR;
2025
2026 WHEN assign_master_default_except THEN
2027 IF G_DEBUG = 'Y' THEN
2028 INV_ORGHIERARCHY_PVT.Log
2029 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,l_err_text);
2030 END IF;
2031 x_errbuff := l_err_text;
2032 x_retcode := l_return_code;
2033
2034 WHEN assign_status_attrib_except THEN
2035 IF G_DEBUG = 'Y' THEN
2036 INV_ORGHIERARCHY_PVT.Log
2037 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION,l_err_text);
2038 END IF;
2039 x_errbuff := l_err_text;
2040 x_retcode := l_return_code;
2041
2042 WHEN OTHERS THEN
2043 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2044 THEN
2045 FND_MSG_PUB.Add_Exc_Msg
2046 ( G_PKG_NAME
2047 , 'Item_Org_Assignment'
2048 );
2049 END IF;
2050 x_retcode := RETCODE_ERROR;
2051 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
2052
2053 END Item_Org_Assignment;
2054
2055
2056 END INV_ITEM_ORG_ASSIGN_CP;