[Home] [Help]
PACKAGE BODY: APPS.INV_COPY_ORGANIZATION_REPORT
Source
1 PACKAGE BODY INV_COPY_ORGANIZATION_REPORT AS
2 -- $Header: INVCORPB.pls 120.6.12000000.2 2007/02/26 11:30:07 myerrams ship $
3 --+===========================================================================+
4 --| Copyright (c) YYYY Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| FILENAME |
9 --| INVCORPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package will be used to generated the data required for Copy Orgn |
13 --| Report. |
14 --| |
15 --| HISTORY |
16 --| 07/21/2003 nkilleda Created. |
17 --| 02/27/2003 nkilleda Bug 3441641 : Modified Validate_Locs procedure to |
18 --| use location id to retrieve the creation date |
19 --| instead of location code. |
20 --| 04/26/2004 nesoni modified for bug 3550415 |
21 --| 05/12/2004 aujain Bug 3623168 Modified Get_Err_Org_Information |
22 --| function for 10G compilation issue |
23 --| 24/05/2004 nkamaraj Bug 3637921 Modified qeury to reduce cost and avoid|
24 --| FTS for the Item Revisions count |
25 --| 11/06/2004 shpandey Bug 3683490 Added New function clob_to_varchar |
26 --| added to convert clob |
27 --| field to varchar2 as to_char function(clob) is not |
28 --| supported for version 8i. |
29 --| 14/06/2004 shpandey Modified the If condition used before inserting the|
30 --| Qualitative data into MTL_COPY_ORG_REPORT table. |
31 --| for bug# 3678706. |
32 --| 18/08/2004 aujain Modified size of variables retreiving FND Messages |
33 --| for Bug 3838706. |
34 --| 12/04/2004 shpandey Added a function Get_Err_Wip_Acc_Classes for |
35 --| supporting Wip Accounting Classes entity for R12 |
36 --| also modified procedure Validate_Locs for |
37 --| supporting report if model org does not have |
38 --| location attached to it. bug#4111958 |
39 --| 11/08/2005 nesoni Modified for bug #3575494 |
40 --| EntityTypes are replaced with lookup codes defined |
41 --| for new lookup type 'INV_COPY_ORG_REPORT_ENTITIES'.|
42 --| Commented unused local variable l_entity_type, |
43 --| l_return_status, l_copyorg_time_stamp, |
44 --| l_locs_validated, l_orgs_validated, l_copy_boms, |
45 --| l_copy_routings, l_copy_items, l_copy_ship_net , |
46 --| l_assgn_hier, l_return_status, l_msg_count, |
47 --| l_msg_data,l_excp_loc_cd_invalid,l_excp_loc_message|
48 --| l_org_id, l_error_status_flag, l_count,l_message |
49 --| Commented unused global vairables:g_log_level |
50 --| ,g_table_name |
51 --| 09/09/2005 vmutyala Modified java style comments to PLS comments for |
52 --| bug:4599267 |
53 --| 12/12/2005 vmutyala Modified Get_Err_Routings to remove inconsistancy |
54 --| in comparing routings |
55 --| 19/12/2005 vmutyala Modified Get_Err_Bom_Bom to remove inconsistancy |
56 --| in comparing Bom |
57 --| 20/01/2006 myerrams Replaced the view BOM_BILL_OF_MATERIALS |
58 --| with BOM_STRUCTURES_B table. Bug: 4913484. |
59 --| 26/12/2006 myerrams Bug5592181 Added New function Get_Err_StdOperations|
60 --| to Validate StandardOperations created for new org |
61 --| against those in model org. |
62 --+===========================================================================+
63
64 --=============================================================================
65 -- CONSTANTS
66 --=============================================================================
67 G_DEBUG_LEVEL VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_LEVEL'), '6');
68 G_DEBUG VARCHAR2(1) := 'N';
69 G_THRESHOLD NUMBER := 50;
70 --=============================================================================
71 -- PUBLIC VARIABLES
72 --=============================================================================
73 --g_log_level NUMBER := NULL;
74 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF,SQL,SRS
75 g_group_code VARCHAR2(30);
76 g_model_org_id NUMBER;
77 g_organization_id NUMBER;
78 g_model_org_code VARCHAR2(10);
79 g_organization_code VARCHAR2(10);
80 g_copy_boms VARCHAR2(10);
81 g_copy_routings VARCHAR2(10);
82 g_copy_items VARCHAR2(10);
83 g_copy_ship_net VARCHAR2(10);
84 g_assgn_hier VARCHAR2(10);
85 g_location_status VARCHAR2(10);
86 g_exp_modify_cnt NUMBER;
87 g_exp_copy_cnt NUMBER;
88 g_copy_cnt NUMBER;
89 g_modify_cnt NUMBER;
90 g_entity_idx NUMBER;
91 g_entity_count NUMBER;
92 g_entity_names Char_Array;
93 g_not_to_copy BOOLEAN:=false;
94 --g_table_name VARCHAR2(100);
95 g_entity_type VARCHAR2(100);
96 --------------------------------------------------------------------
97 -- Removed Support for Modified entities
98 --------------------------------------------------------------------
99 -- g_modified BOOLEAN:=false;
100 --------------------------------------------------------------------
101 g_error_status_flag BOOLEAN:=false;
102
103 --=============================================================================
104 -- PROCEDURES AND FUNCTIONS
105 --=============================================================================
106
107 --=============================================================================
108 -- API NAME : Generate_Report_Data
109 -- API TYPE : PUBLIC
110 -- PRE-CONDITIONS: None.
111 -- COMMENTS : PL/SQL API called from Before_Report trigger of the
112 -- report. Calls initialize(), Validate_Locs() and
113 -- Validate_Orgs() functions to generate report data.
114 -- PARAMETERS :
115 -- p_api_version REQUIRED. As per standards.
116 -- p_init_msg_list REQUIRED. As per standards.
117 -- p_commit REQUIRED. As per standards.
118 -- x_return_status REQUIRED. Value can be
119 -- FND_API.G_RET_STS_SUCCESS
120 -- FND_API.G_RET_STS_ERROR
121 -- FND_API.G_RET_STS_UNEXP_ERROR
122 -- x_msg_count REQUIRED. As per standards.
123 -- x_msg_data REQUIRED. As per standards.
124 -- p_group_code REQUIRED. Group code created for Copy Org request.
125 -- p_model_org_code REQUIRED. Model organization to copy from.
126 -- p_organization_code REQUIRED. New organization to be created.
127 -- p_copy_boms REQUIRED. Flag indicating to Copy BOMs.
128 -- p_copy_routings REQUIRED. Flag indicating to Copy Routings.
129 -- p_copy_items REQUIRED. Flag indicating to Copy Items.
130 -- p_copy_ship_net REQUIRED. Flag indicating to Copy Shipping Networks.
131 -- p_assgn_hier REQUIRED. Flag indicating to Copy Assign Hierarchies.
132 -- p_location_status REQUIRED. Flag indicating if loc was created by req.
133 --
134 -- EXCEPTIONS : None.
135 --
136 --=============================================================================
137 PROCEDURE Generate_Report_Data
138 ( p_api_version IN NUMBER
139 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
140 , p_commit IN VARCHAR2 := FND_API.G_FALSE
141 , x_return_status OUT NOCOPY VARCHAR2
142 , x_msg_count OUT NOCOPY NUMBER
143 , x_msg_data OUT NOCOPY VARCHAR2
144 , p_group_code IN VARCHAR2
145 , p_model_org_code IN VARCHAR2
146 , p_organization_code IN VARCHAR2
147 , p_copy_boms IN VARCHAR2
148 , p_copy_routings IN VARCHAR2
149 , p_copy_items IN VARCHAR2
150 , p_copy_ship_net IN VARCHAR2
151 , p_assgn_hier IN VARCHAR2
152 , p_location_status IN VARCHAR2
153 )
154 IS
155 l_api_name CONSTANT VARCHAR2(30) := ' Generate_Report_Data ';
156 l_api_version CONSTANT NUMBER := 1.0;
157 ---------------------------------------------------------------------------
158 -- The initialize procedure has been obsoleted as it is not required
159 -- the procedure only initialized an array with the list of attrs
160 -- that have been modified ( through Input XML )
161 -- Since modified attributes are not longer supported, this procedure
162 -- need not be called.
163 ---------------------------------------------------------------------------
164 --l_initialized BOOLEAN:=true;
165 ---------------------------------------------------------------------------
166 --l_locs_validated BOOLEAN:=true;
167 --l_orgs_validated BOOLEAN:=true;
168 --l_copy_boms BOOLEAN:=false;
169 --l_copy_routings BOOLEAN:=false;
170 --l_copy_items BOOLEAN:=false;
171 --l_copy_ship_net BOOLEAN:=false;
172 --l_assgn_hier BOOLEAN:=false;
173 --l_return_status VARCHAR2(30);
174 --l_msg_count NUMBER;
175 --l_msg_data VARCHAR2(100);
176
177 BEGIN
178 -----------------------------------------------------------------------------
179 -- Standard Start of API savepoint
180 -----------------------------------------------------------------------------
181 SAVEPOINT Generate_Report_Data_PVT;
182
183 IF G_DEBUG_LEVEL = '6'
184 THEN
185 G_DEBUG := 'Y';
186 END IF;
187
188 IF G_DEBUG = 'Y' THEN
189 FND_MSG_PUB.ADD_EXC_MSG
190 ( G_PKG_NAME
191 , l_api_name
192 , '> Generate_Report_Data => '||
193 'Input Paramters' ||
194 '-> '||p_group_code ||
195 ' , '||p_model_org_code ||
196 ' , '||p_organization_code ||
197 ' , '||p_copy_boms ||
198 ' , '||p_copy_routings ||
199 ' , '||p_copy_items ||
200 ' , '||p_copy_ship_net ||
201 ' , '||p_assgn_hier ||
202 ' , '||p_location_status
203 );
204 END IF;
205
206 --
207 -- Assign values to global variables
208 --
209 g_organization_id := Get_Organization_Id( p_organization_code );
210 g_model_org_id := Get_Organization_Id( p_model_org_code );
211 g_group_code := p_group_code;
212 g_model_org_code := p_model_org_code;
213 g_organization_code := p_organization_code;
214 g_copy_boms := p_copy_boms;
215 g_copy_routings := p_copy_routings;
216 g_copy_items := p_copy_items;
217 g_copy_ship_net := p_copy_ship_net;
218 g_assgn_hier := p_assgn_hier;
219 g_location_status := p_location_status;
220 -----------------------------------------------------------------------------
221 -- Check for call compatibility.
222 -----------------------------------------------------------------------------
223 IF NOT FND_API.Compatible_API_Call( l_api_version
224 , p_api_version
225 , l_api_name
226 , G_PKG_NAME
227 )
228 THEN
229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230 END IF;
231 -----------------------------------------------------------------------------
232 -- Initialize API message list if necessary.
233 -- Initialize message list if p_init_msg_list is set to TRUE.
234 -----------------------------------------------------------------------------
235 IF FND_API.to_Boolean( p_init_msg_list )
236 THEN
237 FND_MSG_PUB.initialize;
238 END IF;
239
240 ---------------------------------------------------------------------------
241 -- The initialize procedure has been obsoleted as it is not required
242 -- the procedure only initialized an array with the list of attrs
243 -- that have been modified ( through Input XML )
244 -- Since modified attributes are not longer supported, this procedure
245 -- need not be called.
246 ---------------------------------------------------------------------------
247 --IF G_DEBUG = 'Y' THEN
248 -- FND_MSG_PUB.ADD_EXC_MSG
249 -- ( G_PKG_NAME
250 -- , l_api_name
251 -- , '> initialize() for populating entity/attribute lists'
252 -- );
253 -- --dbms_output.put_line('> initialize for populating' ||
254 -- -- ' modified entity list' );
255 --END IF;
256 -----------------------------------------------------------------------------
257 -- Call initialize() function to initialize the entity list, attribute
258 -- list, and modified attributes entered by user.
259 -----------------------------------------------------------------------------
260 --initialize;
261 -----------------------------------------------------------------------------
262
263 IF G_DEBUG = 'Y' THEN
264 FND_MSG_PUB.ADD_EXC_MSG
265 ( G_PKG_NAME
266 , l_api_name
267 , '> Validating location - writing messages to Report tab'
268 );
269 --dbms_output.put_line('> Validating location, '||
270 -- ' writing messages to Report tab' );
271 END IF;
272 ---------------------------------------------------------------------------
273 -- Call Validate_Locs() function to validate the location
274 -- created by Copy Org and write an appropriate message
275 -- to the Report Table : MTL_COPY_ORG_REPORT.
276 ---------------------------------------------------------------------------
277 Validate_Locs;
278
279 IF G_DEBUG = 'Y' THEN
280 FND_MSG_PUB.ADD_EXC_MSG
281 ( G_PKG_NAME
282 , l_api_name
283 , '> Validating organization, writing messages to Report tab'
284 );
285 --dbms_output.put_line('> Validating organization, '||
286 -- 'writing messages to Report tab');
287 END IF;
288 -------------------------------------------------------------------------
289 -- Call Validate_Orgs() function to validate the organization
290 -- created by Copy Org and write appropriate messages
291 -- to the Report Table : MTL_COPY_ORG_REPORT.
292 -------------------------------------------------------------------------
293 Validate_Orgs();
294 -------------------------------------------------------------------------
295 -- Standard check of p_commit.
296 -------------------------------------------------------------------------
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298 IF FND_API.To_Boolean( p_commit ) THEN
299 COMMIT WORK;
300 END IF;
301 ---------------------------------------------------------------------------
302 -- set return status to appropriate value based in org
303 -- validation status.
304 ---------------------------------------------------------------------------
305
306 EXCEPTION
307 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
308 ROLLBACK TO Generate_Report_Data_PVT;
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
310 FND_MSG_PUB.Count_And_Get( p_count=>x_msg_count, p_data=>x_msg_data);
311
312 WHEN OTHERS THEN
313 ROLLBACK TO Generate_Report_Data_PVT;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
315 FND_MSG_PUB.Count_And_Get( p_count=>x_msg_count, p_data=>x_msg_data);
316
317 END Generate_Report_Data;
318
319 --=============================================================================
320 -- API NAME : initialize()
321 -- PRE-CONDITIONS: None.
322 -- DESCRIPTION : initialize the list m_user_values with values
323 -- from Report Table.
324 -- PARAMETERS : None
325 -- EXCEPTIONS : None.
326 --
327 -- This procedure is OBSOLETED as modified attributes are not supported
328 -- anymore by the Copy Org Report.
329 --=============================================================================
330 --PROCEDURE initialize
331 --IS
332 -- CURSOR l_user_values ( p_group_code VARCHAR2 )
333 -- IS
334 -- SELECT entity_type, entity_name, field_name
335 -- FROM mtl_copy_org_report
336 -- WHERE rec_type = 'INPUT_XML'
337 -- AND group_code = p_group_code;
338 --
339 -- l_user_rec l_user_values%ROWTYPE;
340 -- l_usr_index NUMBER:=1;
341 --
342 -- l_api_name VARCHAR2(100):=' Initialize ';
343 --
344 --BEGIN
345 --
346 -- IF G_DEBUG = 'Y' THEN
347 -- FND_MSG_PUB.ADD_EXC_MSG
348 -- ( G_PKG_NAME
349 -- , l_api_name
350 -- , '> initialize'
351 -- );
352 -- END IF;
353 --
354 -- ---------------------------------------------------------------------------
355 -- -- Retrieve the list of entities and modified attributes
356 -- -- entered by the user, written by CopyLoader into the
357 -- -- copy org report table.
358 -- ---------------------------------------------------------------------------
359 --
360 -- OPEN l_user_values ( g_group_code );
361 -- FETCH l_user_values INTO l_user_rec;
362 --
363 -- IF l_user_values%NOTFOUND THEN
364 -- RETURN;
365 -- END IF;
366 --
367 -- LOOP
368 -- m_user_values(l_usr_index).entity_type := l_user_rec.entity_type;
369 -- m_user_values(l_usr_index).table_name := l_user_rec.entity_name;
370 -- m_user_values(l_usr_index).field_name := l_user_rec.field_name;
371 -- l_usr_index := l_usr_index + 1;
372 --
373 -- FETCH l_user_values INTO l_user_rec;
374 -- EXIT WHEN l_user_values%NOTFOUND;
375 -- END LOOP;
376 -- CLOSE l_user_values;
377 --
378 -- IF G_DEBUG = 'Y' THEN
379 -- FND_MSG_PUB.ADD_EXC_MSG
380 -- ( G_PKG_NAME
381 -- , l_api_name
382 -- , '< initialize'
383 --
384 -- );
385 -- END IF;
386 --EXCEPTION
387 -- WHEN OTHERS THEN
388 -- FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
389 -- FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
390 -- FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
391 -- FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
392 -- FND_MSG_PUB.Add;
393 -- RAISE;
394 --
395 --END initialize;
396 --
397 --=============================================================================
398 -- API NAME : Validate_Orgs
399 -- PRE-CONDITIONS: New Organization, Location, Parameters must be created.
400 -- DESCRIPTION : Validate the org and call the function to validate entities
401 -- for model org against new org.
402 -- PARAMETERS :
403 -- p_group_code REQUIRED. Group code created for Copy Org request.
404 -- p_model_org_code REQUIRED. Model organization to copy from.
405 -- p_organization_code REQUIRED. New organization to be created.
406 --
407 -- EXCEPTIONS : None.
408 --
409 --=============================================================================
410 PROCEDURE Validate_Orgs
411 IS
412 l_api_name VARCHAR2(30):='Validate_Orgs';
413 l_error_msg VARCHAR2(100):='';
414 l_status VARCHAR2(10);
415 l_rec_type VARCHAR2(30):='NEW_ORGANIZATION_SUMMARY';
416
417 BEGIN
418
419 IF G_DEBUG = 'Y' THEN
420 FND_MSG_PUB.ADD_EXC_MSG
421 ( G_PKG_NAME
422 , l_api_name
423 , '> Validate_Orgs '
424 );
425 END IF;
426 ---------------------------------------------------------------------------
427 -- Call function to validate all entity records
428 ---------------------------------------------------------------------------
429 l_status := Validate_Entities;
430 ---------------------------------------------------------------------------
431 -- Insert record into Report table for succesfully
432 -- created organization.
433 ---------------------------------------------------------------------------
434 Insert_Row ( p_location_code => ''
435 , p_business_group_name => ''
436 , p_status => l_status
437 , p_error_msg => l_error_msg
438 , p_rec_type => l_rec_type
439 , p_entity_type => ''
440 , p_copy_cnt => null
441 , p_modify_cnt => null
442 , p_exp_copy_cnt => null
443 , p_exp_modify_cnt => null
444 , p_entity_name => ''
445 , p_entity_inconsistency => ''
446 , p_put_orgs => true
447 );
448
449 IF G_DEBUG = 'Y' THEN
450 FND_MSG_PUB.ADD_EXC_MSG
451 ( G_PKG_NAME
452 , l_api_name
453 , '< Validate_Orgs '
454 );
455 END IF;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
460 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
461 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
462 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
463 FND_MSG_PUB.Add;
464 RAISE;
465
466 END Validate_Orgs;
467
468 --=============================================================================
469 -- PROC NAME : Validate_Locs
470 -- PRE-CONDITIONS: None.
471 -- DESCRIPTION : Validate the location for model org against new org.
472 -- PARAMETERS : None.
473 --
474 --=============================================================================
475 PROCEDURE Validate_Locs
476 IS
477 l_api_name VARCHAR2(30):='Validate_Locs';
478 l_creation_date DATE;
479 --l_copyorg_time_stamp DATE;
480 l_location_code VARCHAR2(255);
481 l_location_id NUMBER;
482 l_business_group_name VARCHAR2(255);
483 l_status VARCHAR2(10);
484 l_rec_type VARCHAR2(30):='NEW_LOCATION_SUMMARY';
485 -- Modified for bug 3838706
486 -- l_message VARCHAR2(255);
487 l_message VARCHAR2(2000);
488 --l_org_id NUMBER;
489 --l_excp_loc_cd_invalid EXCEPTION;
490 --l_excp_loc_message EXCEPTION;
491
492 --shpandey, added for R12 bug#4111958
493 l_model_loc_exists BOOLEAN := TRUE;
494 BEGIN
495 IF G_DEBUG = 'Y' THEN
496 FND_MSG_PUB.ADD_EXC_MSG
497 ( G_PKG_NAME
498 , l_api_name
499 , '> Validate_Locs'
500 );
501 END IF;
502
503 l_status := g_location_status;
504
505 IF G_DEBUG = 'Y' THEN
506 FND_MSG_PUB.ADD_EXC_MSG
507 ( G_PKG_NAME
508 , l_api_name
509 , '- Retrieving Location Code, Business Group Name'
510 );
511 END IF;
512 ---------------------------------------------------------------------------
513 -- get location code, business group name for model
514 ---------------------------------------------------------------------------
515 -- Bug 4111958 : Added the following query block to check if model org have
516 -- location or not.
517 IF (l_status = 'PRE_EXIST') THEN
518 BEGIN
519 SELECT loc.location_code, hou1.name, loc.location_id
520 INTO l_location_code, l_business_group_name, l_location_id
521 FROM hr_locations loc
522 , hr_all_organization_units hou
523 , hr_all_organization_units hou1
524 WHERE loc.location_id = hou.location_id
525 AND hou1.organization_id = hou.business_group_id
526 AND hou.organization_id = g_model_org_id;
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 l_model_loc_exists := FALSE;
530 END;
531 END IF;
532 -- START OF IF bug 4111958
533 IF l_model_loc_exists THEN
534 ---------------------------------------------------------------------------
535 -- get location code, business group name for model/new organization.
536 ---------------------------------------------------------------------------
537 -- Bug 3441641 : Added location_id to the select clause
538 -- Location id is used in the query below to get creation time
539 --
540 BEGIN
541 SELECT loc.location_code, hou1.name, loc.location_id
542 INTO l_location_code, l_business_group_name, l_location_id
543 FROM hr_locations loc
544 , hr_all_organization_units hou
545 , hr_all_organization_units hou1
546 WHERE loc.location_id = hou.location_id
547 AND hou1.organization_id = hou.business_group_id
548 AND hou.organization_id = g_organization_id;
549 EXCEPTION
550 WHEN OTHERS THEN
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END;
553 END IF;
554 -- END OF IF bug 4111958
555
556 IF G_DEBUG = 'Y' THEN
557 FND_MSG_PUB.ADD_EXC_MSG
558 ( G_PKG_NAME
559 , l_api_name
560 , '- Retrieving Location creation date, copy org req. timestamp.'
561 );
562 END IF;
563 -------------------------------------------------------------------------
564 -- get message for location for status - PRE_EXIST, SUCCESS
565 -------------------------------------------------------------------------
566 -- Bug 3441641 : Changed query to retrieve the creation date based on
567 -- location id. Earlier it was based on location code, but this fails
568 -- in case the report is run in pseudo translated environment.
569 --
570 -- Bug 4111958 changed the if logic.
571 IF (l_status = 'PRE_EXIST' AND l_model_loc_exists) THEN
572 BEGIN
573 SELECT creation_date
574 INTO l_creation_date
575 FROM hr_locations locs
576 WHERE locs.location_id = l_location_id;
577 EXCEPTION
578 WHEN OTHERS THEN
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END;
581
582 FND_MESSAGE.SET_NAME (application=>'INV', name=>'PRE_EXIST_ERROR');
583 FND_MESSAGE.SET_TOKEN ('CREATION_DATE' , l_creation_date );
584 FND_MESSAGE.SET_TOKEN ('TIMESTAMP', null);
585 l_message := FND_MESSAGE.GET();
586
587 ELSIF l_status = 'SUCCESS' THEN
588 l_message := '';
589 END IF;
590 -------------------------------------------------------------------------
591 -- Write message to report table for location.
592 -------------------------------------------------------------------------
593 Insert_Row ( p_location_code => l_location_code
594 , p_business_group_name => l_business_group_name
595 , p_status => l_status
596 , p_error_msg => l_message
597 , p_rec_type => l_rec_type
598 , p_entity_type => ''
599 , p_copy_cnt => null
600 , p_modify_cnt => null
601 , p_exp_copy_cnt => null
602 , p_exp_modify_cnt => null
603 , p_entity_name => ''
604 , p_entity_inconsistency => ''
605 , p_put_orgs => false
606 );
607
608 IF G_DEBUG = 'Y' THEN
609 FND_MSG_PUB.ADD_EXC_MSG
610 ( G_PKG_NAME
611 , l_api_name
612 , '< Validate_Locs'
613 );
614 END IF;
615
616 EXCEPTION
617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
618 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
619 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
620 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
621 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
622 FND_MSG_PUB.Add;
623 RAISE;
624
625 WHEN OTHERS THEN
626 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
627 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
628 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
629 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
630 FND_MSG_PUB.Add;
631 RAISE;
632
633 END Validate_Locs;
634
635 --=============================================================================
636 -- API NAME : Trim_Array
637 -- PRE-CONDITIONS: None.
638 -- DESCRIPTION : Remove blank / null elements from the array.
639 -- PARAMETERS :
640 -- p_in_array REQUIRED. Input Array to be trimmed.
641 --
642 -- EXCEPTIONS : None.
643 --
644 --=============================================================================
645 FUNCTION Trim_Array (p_in_array Char_Array) RETURN Char_Array
646 IS
647 l_out_array Char_Array := Char_Array();
648 l_out_ctr NUMBER := 0;
649 l_out_idx NUMBER := 1;
650 BEGIN
651 FOR i IN 1..p_in_array.COUNT
652 LOOP
653 IF p_in_array(i) IS NOT NULL
654 THEN
655 l_out_ctr := l_out_ctr + 1;
656 END IF;
657 END LOOP;
658 l_out_array.EXTEND(l_out_ctr);
659 FOR i IN 1..p_in_array.COUNT
660 LOOP
661 IF p_in_array(i) IS NOT NULL
662 THEN
663 l_out_array(l_out_idx) := p_in_array(i);
664 l_out_idx := l_out_idx + 1;
665 END IF;
666 END LOOP;
667 RETURN l_out_array;
668 END Trim_Array;
669
670 --=============================================================================
671 -- PROC NAME : Init_Vars
672 -- PRE-CONDITIONS: None.
673 -- DESCRIPTION : Initialize variables for processing entities.
674 -- PARAMETERS : p_entity_type. REQUIRED : Entity Type being validated.
675 -- EXCEPTIONS : None.
676 --
677 --=============================================================================
678 PROCEDURE Init_Vars ( p_entity_type IN VARCHAR2 )
679 IS
680 l_api_name VARCHAR2(100):=' Init_Vars ';
681
682 BEGIN
683 g_exp_modify_cnt := 0;
684 g_exp_copy_cnt := 0;
685 g_copy_cnt := 0;
686 g_modify_cnt := 0;
687 g_entity_idx := 1;
688 g_entity_count := 0;
689 g_entity_names := Char_Array();
690 g_not_to_copy := false;
691 g_entity_type := p_entity_type;
692 --------------------------------------------------------------------
693 -- Removed support for Modified records.
694 --------------------------------------------------------------------
695 --g_modified := false;
696 --g_modified := Is_Entity_Modified( g_entity_type );
697 --------------------------------------------------------------------
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
702 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
703 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
704 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
705 FND_MSG_PUB.Add;
706 RAISE;
707
708 END Init_Vars;
709
710 --=============================================================================
711 -- PROC NAME : Validate_Entities
712 -- PRE-CONDITIONS: New Organization, Location, Parameters must be created.
713 -- DESCRIPTION : Validate all entities for model org against new org.
714 -- PARAMETERS : None.
715 -- EXCEPTIONS : None.
716 --
717 --=============================================================================
718 FUNCTION Validate_Entities RETURN VARCHAR2
719 IS
720 l_api_name VARCHAR2(100):= ' Validate_Entities ';
721 l_entity_idx NUMBER:=1;
722 --l_error_status_flag BOOLEAN:=false;
723 l_model_org_count NUMBER;
724 l_new_org_count NUMBER;
725
726 CURSOR l_bom_params ( p_org_id IN NUMBER ) IS
727 SELECT COUNT(organization_id)
728 FROM bom_parameters
729 WHERE ORGANIZATION_ID = p_org_id;
730
731 CURSOR l_rcv_params ( p_org_id IN NUMBER ) IS
732 SELECT COUNT(organization_id)
733 FROM rcv_parameters
734 WHERE ORGANIZATION_ID = p_org_id;
735
736 /*shpandey, Added the following cursors for newly added entities for R12*/
737 CURSOR l_wip_params ( p_org_id IN NUMBER ) IS
738 SELECT COUNT(organization_id)
739 FROM wip_parameters
740 WHERE ORGANIZATION_ID = p_org_id;
741
742 CURSOR l_shipping_params ( p_org_id IN NUMBER ) IS
743 SELECT COUNT(organization_id)
744 FROM wsh_shipping_parameters
745 WHERE ORGANIZATION_ID = p_org_id;
746
747 CURSOR l_planning_params ( p_org_id IN NUMBER ) IS
748 SELECT COUNT(organization_id)
749 FROM mrp_parameters
750 WHERE ORGANIZATION_ID = p_org_id;
751 /*shpandey,R12*/
752
753 BEGIN
754
755 IF G_DEBUG = 'Y' THEN
756 FND_MSG_PUB.ADD_EXC_MSG
757 ( G_PKG_NAME
758 , l_api_name
759 , '> Validate_Entities'
760 );
761 END IF;
762 /*-----------------------------------
763 ---- Organization Information -------
764 -----------------------------------*/
765 Init_Vars('INV_ORGANIZATION_INFORMATION');
766 g_entity_names := Get_Err_Org_Information;
767 Put_Report_Data;
768
769 /*-----------------------------------
770 ---- Inventory Parameters -------
771 -----------------------------------*/
772 Init_Vars('INV_PARAMETERS');
773 g_entity_names.EXTEND;
774 g_entity_names(l_entity_idx):=1;
775 Put_Report_Data;
776
777 /*-----------------------------------
778 ---- BOM Parameters -------
779 -----------------------------------*/
780 Init_Vars('BOM_PARAMETERS');
781 Open l_bom_params ( g_organization_id );
782 Fetch l_bom_params Into l_new_org_count;
783 Close l_bom_params;
784
785 Open l_bom_params ( g_model_org_id );
786 Fetch l_bom_params Into l_model_org_count;
787 Close l_bom_params;
788
789 IF l_new_org_count < l_model_org_count
790 THEN
791 g_entity_names.EXTEND;
792 --Set entity name as null for parameters APIs. bug:3575494 bug:4599267
793 --g_entity_names(g_entity_idx):=g_entity_type; bug:4599267
794 g_entity_names(g_entity_idx):=null;
795 g_entity_idx:=g_entity_idx+1;
796 END IF;
797 g_entity_names.EXTEND;
798 g_entity_names(g_entity_idx):=l_model_org_count;
799 Put_Report_Data;
800
801 /*-----------------------------------
802 ---- Receiving Parameters -------
803 -----------------------------------*/
804 Init_Vars('RCV_PARAMETERS');
805 Open l_rcv_params ( g_organization_id );
806 Fetch l_rcv_params Into l_new_org_count;
807 Close l_rcv_params;
808
809 Open l_rcv_params ( g_model_org_id );
810 Fetch l_rcv_params Into l_model_org_count;
811 Close l_rcv_params;
812
813 IF l_new_org_count < l_model_org_count
814 THEN
815 g_entity_names.EXTEND;
816 --Set entity name as null for parameters APIs. bug:3575494 bug:4599267
817 --g_entity_names(g_entity_idx):=g_entity_type; bug:4599267
818 g_entity_names(g_entity_idx):=null;
819
820 g_entity_idx:=g_entity_idx+1;
821 END IF;
822 g_entity_names.EXTEND;
823 g_entity_names(g_entity_idx):=l_model_org_count;
824 Put_Report_Data;
825
826 /* shpandey, Added for R12, code block start*/
827 /*-----------------------------------
828 ---- WIP Parameters -------
829 -----------------------------------*/
830 Init_Vars('WIP_PARAMETERS');
831 Open l_wip_params ( g_organization_id );
832 Fetch l_wip_params Into l_new_org_count;
833 Close l_wip_params;
834
835 Open l_wip_params ( g_model_org_id );
836 Fetch l_wip_params Into l_model_org_count;
837 Close l_wip_params;
838
839 IF l_new_org_count < l_model_org_count
840 THEN
841 g_entity_names.EXTEND;
842 --Set entity name as null for parameters APIs. bug:3575494 bug:4599267
843 --g_entity_names(g_entity_idx):=g_entity_type; bug:4599267
844 g_entity_names(g_entity_idx):= null;
845
846 g_entity_idx:=g_entity_idx+1;
847 END IF;
848 g_entity_names.EXTEND;
849 g_entity_names(g_entity_idx):=l_model_org_count;
850 Put_Report_Data;
851
852 /*-----------------------------------
853 ---- WIP Accounting Classes -------
854 -----------------------------------*/
855 Init_Vars('WIP_ACCOUNTING_CLASSES');
856 g_entity_names := Get_Err_Wip_Acc_Classes;
857 Put_Report_Data;
858
859 /*-----------------------------------
860 ---- Shipping Parameters -------
861 -----------------------------------*/
862 Init_Vars('WSH_SHIPPING_PARAMETERS');
863 Open l_shipping_params ( g_organization_id );
864 Fetch l_shipping_params Into l_new_org_count;
865 Close l_shipping_params;
866
867 Open l_shipping_params ( g_model_org_id );
868 Fetch l_shipping_params Into l_model_org_count;
869 Close l_shipping_params;
870
871 IF l_new_org_count < l_model_org_count
872 THEN
873 g_entity_names.EXTEND;
874 --Set entity name as null for parameters APIs. bug:3575494 bug:4599267
875 --g_entity_names(g_entity_idx):=g_entity_type; bug:4599267
876 g_entity_names(g_entity_idx):= null;
877
878 g_entity_idx:=g_entity_idx+1;
879 END IF;
880 g_entity_names.EXTEND;
881 g_entity_names(g_entity_idx):=l_model_org_count;
882 Put_Report_Data;
883
884 /*-----------------------------------
885 ---- Planning Parameters -------
886 -----------------------------------*/
887 Init_Vars('MRP_PLANNING_PARAMETERS');
888 Open l_planning_params ( g_organization_id );
889 Fetch l_planning_params Into l_new_org_count;
890 Close l_planning_params;
891
892 Open l_planning_params ( g_model_org_id );
893 Fetch l_planning_params Into l_model_org_count;
894 Close l_planning_params;
895
896 IF l_new_org_count < l_model_org_count
897 THEN
898 g_entity_names.EXTEND;
899 --Set entity name as null for parameters APIs. bug:3575494 bug:4599267
900 --g_entity_names(g_entity_idx):=g_entity_type; bug:4599267
901 g_entity_names(g_entity_idx):= null;
902 g_entity_idx:=g_entity_idx+1;
903 END IF;
904 g_entity_names.EXTEND;
905 g_entity_names(g_entity_idx):=l_model_org_count;
906 Put_Report_Data;
907 /* R12 code block end*/
908
909 /*-----------------------------------
910 ---- Subinventories -------
911 -----------------------------------*/
912 Init_Vars('SUBINVENTORIES');
913 g_entity_names := Get_Err_Subinv_Names;
914 Put_Report_Data;
915
916 /*-----------------------------------
917 ---- Locators -------
918 -----------------------------------*/
919 Init_Vars('LOCATORS');
920 g_entity_names := Get_Err_Mtl_Item_Locations;
921 Put_Report_Data;
922
923 /*-----------------------------------
924 ---- Hierarchy Relations -------
925 -----------------------------------*/
926 IF g_assgn_hier = 'Y'
927 THEN
928 Init_Vars('HIERARCHY_RELATIONS');
929 g_entity_names := Get_Err_Hierarchy;
930 Put_Report_Data;
931 END IF;
932
933 /*-----------------------------------
934 ---- Shipping Networks -------
935 -----------------------------------*/
936 IF g_copy_ship_net = 'Y'
937 THEN
938 Init_Vars('SHIPPING_NETWORKS');
939 g_entity_names := Get_Err_Ship_Net;
940 Put_Report_Data;
941 END IF;
942
943 /*-----------------------------------
944 ---- Routings -------
945 -----------------------------------*/
946 IF g_copy_routings = 'Y'
947 THEN
948 Init_Vars('BOM_DEPARTMENTS');
949 g_entity_names := Get_Err_Bom_Departments;
950 Put_Report_Data;
951
952 Init_Vars('BOM_DEPARTMENT_RESOURCES');
953 g_entity_names := Get_Err_Bom_Dept_Res;
954 Put_Report_Data;
955
956 Init_Vars('BOM_RESOURCES');
957 g_entity_names := Get_Err_Bom_Resources;
958 Put_Report_Data;
959
960 Init_Vars('CST_RESOURCE_COSTS');
961 g_entity_names := Get_Err_Cst_Res_Costs;
962 Put_Report_Data;
963
964 Init_Vars('CST_RESOURCE_OVERHEAD');
965 g_entity_names := Get_Err_Cst_Res_Ovhds;
966 Put_Report_Data;
967
968 Init_Vars('BOM_DEPARTMENT_CLASSES');
969 g_entity_names := Get_Err_Bom_Dept_Classes;
970 Put_Report_Data;
971
972 Init_Vars('STANDARD_OPERATIONS');
973 g_entity_names := Get_Err_StdOperations;
974 Put_Report_Data;
975
976 Init_Vars('ROUTINGS');
977 g_entity_names := Get_Err_Routings;
978 Put_Report_Data;
979 END IF;
980
981 /*-----------------------------------
982 ---- BOMS -------
983 -----------------------------------*/
984 IF g_copy_boms = 'Y'
985 THEN
986 Init_Vars('BOMS');
987 g_entity_names := Get_Err_Bom_Bom;
988 Put_Report_Data;
989 END IF;
990
991 /*-----------------------------------
992 ---- BOMS Or ROUTINGS -------
993 -----------------------------------*/
994 IF g_copy_boms = 'Y'
995 OR g_copy_routings = 'Y'
996 THEN
997 Init_Vars('BOM_ALTERNATE_DESIGNATORS');
998 g_entity_names := Get_Err_Bom_Alt_Desig;
999 Put_Report_Data;
1000 END IF;
1001
1002 /*-----------------------------------
1003 ---- Items -------
1004 -----------------------------------*/
1005 IF g_copy_items = 'Y'
1006 THEN
1007 Init_Vars('ITEMS');
1008 g_entity_names := Get_Err_Mtl_Items;
1009 Put_Report_Data;
1010
1011 Init_Vars('ITEM_CATEGORIES');
1012 g_entity_names := Get_Err_Mtl_Item_Cat;
1013 Put_Report_Data;
1014
1015 Init_Vars('ITEM_REVISIONS');
1016 g_entity_names := Get_Err_Mtl_Item_Rev;
1017 Put_Report_Data;
1018
1019 Init_Vars('ITEM_SUBINVENTORIES');
1020 g_entity_names := Get_Err_Mtl_Items_Subinv;
1021 Put_Report_Data;
1022 END IF;
1023
1024 IF G_DEBUG = 'Y' THEN
1025 FND_MSG_PUB.ADD_EXC_MSG
1026 ( G_PKG_NAME
1027 , l_api_name
1028 , '< Validate_Entities'
1029 );
1030 END IF;
1031
1032 IF g_error_status_flag THEN
1033 RETURN 'ERROR';
1034 END IF;
1035
1036 RETURN 'SUCCESS';
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1041 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1042 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1043 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1044 FND_MSG_PUB.Add;
1045 RAISE;
1046
1047 END Validate_Entities;
1048
1049 --=============================================================================
1050 -- FUNCTION NAME : Get_Err_Hierarchy
1051 -- PRE-CONDITIONS: None.
1052 -- DESCRIPTION : Validate hierarchies to which new org is assigned against
1053 -- those for model org.
1054 -- PARAMETERS : None
1055 -- EXCEPTIONS : None.
1056 --
1057 --=============================================================================
1058 FUNCTION Get_Err_Hierarchy RETURN Char_Array
1059 IS
1060 l_api_name VARCHAR2(100):= ' Get_Err_Hierarchy ';
1061 l_entity_arr_idx NUMBER := 1;
1062 l_entity_names Char_Array := Char_Array();
1063 --l_entity_type VARCHAR2(100):= 'Hierarchy Relations';
1064 l_model_org_count NUMBER;
1065 l_new_org_count NUMBER;
1066 l_hierarchy VARCHAR2(100);
1067
1068 CURSOR l_cursor ( p_model_org_id NUMBER
1069 , p_organization_id NUMBER
1070 )
1071 IS
1072 SELECT DISTINCT hier.NAME
1073 FROM PER_ORG_STRUCTURE_ELEMENTS mdl
1074 , PER_ORG_STRUCTURE_VERSIONS ver
1075 , PER_ORGANIZATION_STRUCTURES hier
1076 WHERE ORGANIZATION_ID_CHILD = p_model_org_id
1077 AND ver.ORG_STRUCTURE_VERSION_ID = mdl.ORG_STRUCTURE_VERSION_ID
1078 AND hier.ORGANIZATION_STRUCTURE_ID = ver.ORGANIZATION_STRUCTURE_ID
1079 AND NOT EXISTS
1080 ( SELECT 'x'
1081 FROM PER_ORG_STRUCTURE_ELEMENTS new
1082 WHERE ORGANIZATION_ID_CHILD = p_organization_id
1083 AND new.ORG_STRUCTURE_VERSION_ID = mdl.ORG_STRUCTURE_VERSION_ID
1084 );
1085
1086 CURSOR l_cnt_csr ( p_org_id NUMBER )
1087 IS
1088 SELECT COUNT(pos.ORG_STRUCTURE_ELEMENT_ID)
1089 FROM PER_ORG_STRUCTURE_ELEMENTS pos
1090 WHERE pos.ORGANIZATION_ID_CHILD = p_org_id;
1091
1092 BEGIN
1093 IF G_DEBUG = 'Y' THEN
1094 FND_MSG_PUB.ADD_EXC_MSG
1095 ( G_PKG_NAME
1096 , l_api_name
1097 , '> Get_Err_Hierarchy'
1098 );
1099 --dbms_output.put_line('> Get_Err_Hierarchy');
1100 END IF;
1101
1102 OPEN l_cnt_csr ( g_model_org_id );
1103 FETCH l_cnt_csr INTO l_model_org_count;
1104 CLOSE l_cnt_csr;
1105
1106 OPEN l_cnt_csr ( g_organization_id );
1107 FETCH l_cnt_csr INTO l_new_org_count;
1108 CLOSE l_cnt_csr;
1109
1110 IF l_new_org_count < l_model_org_count THEN
1111 OPEN l_cursor ( g_model_org_id, g_organization_id );
1112 FETCH l_cursor INTO l_hierarchy;
1113 LOOP
1114 l_entity_names.EXTEND;
1115 l_entity_names(l_entity_arr_idx) := l_hierarchy;
1116 l_entity_arr_idx := l_entity_arr_idx + 1;
1117
1118 FETCH l_cursor INTO l_hierarchy;
1119 EXIT WHEN l_cursor%NOTFOUND;
1120 END LOOP;
1121 CLOSE l_cursor;
1122 END IF;
1123
1124 l_entity_names.EXTEND;
1125 l_entity_names(l_entity_arr_idx) := l_model_org_count;
1126
1127 RETURN l_entity_names;
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1131 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1132 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1133 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1134 FND_MSG_PUB.Add;
1135 RAISE;
1136
1137 END Get_Err_Hierarchy;
1138 --=============================================================================
1139 -- FUNCTION NAME : Get_Err_Subinv_Names
1140 -- PRE-CONDITIONS: None.
1141 -- DESCRIPTION : Validate subinventories created for new org against
1142 -- those in model org.
1143 -- PARAMETERS : None.
1144 -- EXCEPTIONS : None.
1145 --
1146 --=============================================================================
1147 FUNCTION Get_Err_Subinv_Names RETURN Char_Array
1148 IS
1149 l_api_name VARCHAR2(100):= ' Get_Err_Subinv_Names ';
1150 l_entity_arr_idx NUMBER := 1;
1151 l_entity_names Char_Array := Char_Array();
1152 --l_entity_type VARCHAR2(100):= ' Subinventories ';
1153 l_model_org_count NUMBER;
1154 l_new_org_count NUMBER;
1155 l_subinventory VARCHAR2(100);
1156
1157 CURSOR l_cursor ( p_model_org_id NUMBER
1158 , p_organization_id NUMBER
1159 )
1160 IS
1161 SELECT msi.SECONDARY_INVENTORY_NAME
1162 FROM MTL_SECONDARY_INVENTORIES msi
1163 WHERE msi.ORGANIZATION_ID = p_model_org_id
1164 AND NOT EXISTS
1165 ( SELECT 'x'
1166 FROM MTL_SECONDARY_INVENTORIES msi2
1167 WHERE msi2.ORGANIZATION_ID = p_organization_id
1168 AND msi.SECONDARY_INVENTORY_NAME = msi2.SECONDARY_INVENTORY_NAME
1169 );
1170 CURSOR l_cnt_csr ( p_org_id NUMBER )
1171 IS
1172 SELECT COUNT(msi.SECONDARY_INVENTORY_NAME)
1173 FROM MTL_SECONDARY_INVENTORIES msi
1174 WHERE msi.ORGANIZATION_ID = p_org_id;
1175
1176 BEGIN
1177 IF G_DEBUG = 'Y' THEN
1178 FND_MSG_PUB.ADD_EXC_MSG
1179 ( G_PKG_NAME
1180 , l_api_name
1181 , '> Get_Err_Subinv_Names'
1182 );
1183 --dbms_output.put_line('> Get_Err_Subinv_Names');
1184 END IF;
1185
1186 OPEN l_cnt_csr ( g_model_org_id );
1187 FETCH l_cnt_csr INTO l_model_org_count;
1188 CLOSE l_cnt_csr;
1189
1190 OPEN l_cnt_csr ( g_organization_id );
1191 FETCH l_cnt_csr INTO l_new_org_count;
1192 CLOSE l_cnt_csr;
1193
1194 IF l_new_org_count < l_model_org_count THEN
1195 OPEN l_cursor ( g_model_org_id, g_organization_id );
1196 FETCH l_cursor INTO l_subinventory;
1197 LOOP
1198 l_entity_names.EXTEND;
1199 l_entity_names(l_entity_arr_idx) := l_subinventory;
1200 l_entity_arr_idx := l_entity_arr_idx + 1;
1201
1202 FETCH l_cursor INTO l_subinventory;
1203 EXIT WHEN l_cursor%NOTFOUND;
1204 END LOOP;
1205 CLOSE l_cursor;
1206 END IF;
1207
1208 l_entity_names.EXTEND;
1209 l_entity_names(l_entity_arr_idx) := l_model_org_count;
1210
1211 RETURN l_entity_names;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1215 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1216 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1217 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1218 FND_MSG_PUB.Add;
1219 RAISE;
1220
1221 END Get_Err_Subinv_Names;
1222
1223 --=============================================================================
1224 -- FUNCTION NAME : Get_Err_Bom_Resources
1225 -- PRE-CONDITIONS: None.
1226 -- DESCRIPTION : Validate bom resources created for new org against
1227 -- those in model org.
1228 -- PARAMETERS : None.
1229 -- EXCEPTIONS : None.
1230 --
1231 --=============================================================================
1232 FUNCTION Get_Err_Bom_Resources RETURN Char_Array
1233 IS
1234 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Resources ';
1235 l_entity_arr_idx NUMBER := 1;
1236 l_entity_names Char_Array := Char_Array();
1237 --l_entity_type VARCHAR2(100):= ' Resources ';
1238 l_model_org_count NUMBER;
1239 l_new_org_count NUMBER;
1240 l_res_code VARCHAR2(100);
1241
1242 CURSOR l_cursor ( p_model_org_id NUMBER
1243 , p_organization_id NUMBER
1244 )
1245 IS
1246 SELECT br1.RESOURCE_CODE rescode
1247 FROM BOM_RESOURCES br1
1248 WHERE br1.ORGANIZATION_ID = g_model_org_id
1249 AND br1.COST_ELEMENT_ID IN (3,4) /* vmutyala added this condition to distinguish resource overheads and resources in the report */
1250 AND NOT EXISTS ( SELECT 'x'
1251 FROM BOM_RESOURCES br2
1252 WHERE br2.ORGANIZATION_ID = g_organization_id
1253 AND br2.RESOURCE_CODE = br1.RESOURCE_CODE
1254 AND br2.COST_ELEMENT_ID IN (3,4) /* vmutyala added this condition to distinguish
1255 resource overheads and resources in the report */
1256 );
1257
1258 CURSOR l_cnt_csr ( p_org_id NUMBER )
1259 IS
1260 SELECT COUNT(br.RESOURCE_CODE)
1261 FROM BOM_RESOURCES br
1262 WHERE br.ORGANIZATION_ID = p_org_id
1263 AND br.COST_ELEMENT_ID IN (3,4); /* vmutyala added this condition to distinguish resource overheads
1264 and resources in the report */
1265
1266 BEGIN
1267 IF G_DEBUG = 'Y' THEN
1268 FND_MSG_PUB.ADD_EXC_MSG
1269 ( G_PKG_NAME
1270 , l_api_name
1271 , '> Get_Err_Bom_Resources'
1272 );
1273 END IF;
1274
1275 OPEN l_cnt_csr ( g_model_org_id );
1276 FETCH l_cnt_csr INTO l_model_org_count;
1277 CLOSE l_cnt_csr;
1278
1279 OPEN l_cnt_csr ( g_organization_id );
1280 FETCH l_cnt_csr INTO l_new_org_count;
1281 CLOSE l_cnt_csr;
1282
1283 IF l_new_org_count < l_model_org_count
1284 THEN
1285 OPEN l_cursor ( g_model_org_id, g_organization_id );
1286 FETCH l_cursor INTO l_res_code;
1287 LOOP
1288 l_entity_names.EXTEND;
1289 l_entity_names(l_entity_arr_idx) := l_res_code;
1290 l_entity_arr_idx := l_entity_arr_idx + 1;
1291 FETCH l_cursor INTO l_res_code;
1292 EXIT WHEN l_cursor%NOTFOUND;
1293 END LOOP;
1294 CLOSE l_cursor;
1295 END IF;
1296 l_entity_names.EXTEND;
1297 l_entity_names(l_entity_arr_idx):=l_model_org_count;
1298
1299 RETURN l_entity_names;
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1303 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1304 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1305 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1306 FND_MSG_PUB.Add;
1307 RAISE;
1308
1309 END Get_Err_Bom_Resources;
1310 --=============================================================================
1311 -- FUNCTION NAME : Get_Err_Bom_Departments
1312 -- PRE-CONDITIONS: None.
1313 -- DESCRIPTION : Validate bom departments created for new org against
1314 -- those in model org.
1315 -- PARAMETERS : None.
1316 -- EXCEPTIONS : None.
1317 --
1318 --=============================================================================
1319 FUNCTION Get_Err_Bom_Departments RETURN Char_Array
1320 IS
1321 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Departments ';
1322 l_model_org_count NUMBER;
1323 l_new_org_count NUMBER;
1324 l_entity_names Char_Array := Char_Array();
1325 l_entity_arr_idx NUMBER := 1;
1326 l_dep_code VARCHAR2(100);
1327 --l_entity_type VARCHAR2(100):= ' Departments ';
1328
1329 CURSOR l_cursor ( p_model_org_id NUMBER
1330 , p_organization_id NUMBER
1331 )
1332 IS
1333 SELECT bd1.DEPARTMENT_CODE
1334 FROM BOM_DEPARTMENTS bd1
1335 WHERE bd1.ORGANIZATION_ID = p_model_org_id
1336 AND NOT EXISTS ( SELECT 'x'
1337 FROM BOM_DEPARTMENTS bd2
1338 WHERE bd2.ORGANIZATION_ID=p_organization_id
1339 AND bd2.DEPARTMENT_CODE=bd1.DEPARTMENT_CODE
1340 );
1341
1342 CURSOR l_cnt_csr ( p_org_id NUMBER )
1343 IS
1344 SELECT COUNT(bd.DEPARTMENT_CODE)
1345 FROM BOM_DEPARTMENTS bd
1346 WHERE bd.ORGANIZATION_ID = p_org_id;
1347
1348 BEGIN
1349
1350 IF G_DEBUG = 'Y' THEN
1351 FND_MSG_PUB.ADD_EXC_MSG
1352 ( G_PKG_NAME
1353 , l_api_name
1354 , '> Get_Err_Bom_Departments'
1355 );
1356 END IF;
1357
1358 OPEN l_cnt_csr ( g_model_org_id );
1359 FETCH l_cnt_csr INTO l_model_org_count;
1360 CLOSE l_cnt_csr;
1361
1362 OPEN l_cnt_csr ( g_organization_id );
1363 FETCH l_cnt_csr INTO l_new_org_count;
1364 CLOSE l_cnt_csr;
1365
1366 IF l_new_org_count < l_model_org_count
1367 THEN
1368 OPEN l_cursor ( g_model_org_id, g_organization_id );
1369 FETCH l_cursor INTO l_dep_code;
1370 LOOP
1371 l_entity_names.EXTEND;
1372 l_entity_names(l_entity_arr_idx) := l_dep_code;
1373 l_entity_arr_idx := l_entity_arr_idx + 1;
1374
1375 FETCH l_cursor INTO l_dep_code;
1376 EXIT WHEN l_cursor%NOTFOUND;
1377 END LOOP;
1378 CLOSE l_cursor;
1379 END IF;
1380
1381 l_entity_names.EXTEND;
1382 l_entity_names(l_entity_arr_idx):=l_model_org_count;
1383 RETURN l_entity_names;
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1387 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1388 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1389 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1390 FND_MSG_PUB.Add;
1391 RAISE;
1392
1393 END Get_Err_Bom_Departments;
1394
1395 --=============================================================================
1396 -- FUNCTION NAME : Get_Err_Bom_Dept_Classes
1397 -- PRE-CONDITIONS: None.
1398 -- DESCRIPTION : Validate bom department classes created for new org against
1399 -- those in model org.
1400 -- PARAMETERS :
1401 -- EXCEPTIONS : None.
1402 --
1403 --=============================================================================
1404 FUNCTION Get_Err_Bom_Dept_Classes RETURN Char_Array
1405 IS
1406 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Dept_Classes ';
1407 l_model_org_count NUMBER;
1408 l_new_org_count NUMBER;
1409 l_entity_names Char_Array := Char_Array();
1410 l_entity_arr_idx NUMBER := 1;
1411 l_dep_cls_code VARCHAR2(100);
1412 --l_entity_type VARCHAR2(100):= ' Department Classes ';
1413
1414 CURSOR l_cursor ( p_model_org_id NUMBER
1415 , p_organization_id NUMBER
1416 )
1417 IS
1418 SELECT bdc1.DEPARTMENT_CLASS_CODE
1419 FROM BOM_DEPARTMENT_CLASSES bdc1
1420 WHERE bdc1.ORGANIZATION_ID = p_model_org_id
1421 AND NOT EXISTS
1422 ( SELECT 'x'
1423 FROM BOM_DEPARTMENT_CLASSES bdc2
1424 WHERE bdc2.ORGANIZATION_ID = p_organization_id
1425 AND bdc2.DEPARTMENT_CLASS_CODE = bdc1.DEPARTMENT_CLASS_CODE
1426 );
1427
1428 CURSOR l_cnt_csr ( p_org_id NUMBER )
1429 IS
1430 SELECT COUNT(ent.DEPARTMENT_CLASS_CODE)
1431 FROM BOM_DEPARTMENT_CLASSES ent
1432 WHERE ent.ORGANIZATION_ID = p_org_id;
1433
1434 BEGIN
1435
1436 IF G_DEBUG = 'Y' THEN
1437 FND_MSG_PUB.ADD_EXC_MSG
1438 ( G_PKG_NAME
1439 , l_api_name
1440 , 'IN INVCORPB: '||l_api_name
1441 );
1442 END IF;
1443
1444 OPEN l_cnt_csr ( g_model_org_id );
1445 FETCH l_cnt_csr INTO l_model_org_count;
1446 CLOSE l_cnt_csr;
1447
1448 OPEN l_cnt_csr ( g_organization_id );
1449 FETCH l_cnt_csr INTO l_new_org_count;
1450 CLOSE l_cnt_csr;
1451
1452 IF l_new_org_count < l_model_org_count
1453 THEN
1454 OPEN l_cursor ( g_model_org_id, g_organization_id );
1455 FETCH l_cursor INTO l_dep_cls_code;
1456 LOOP
1457 l_entity_names.EXTEND;
1458 l_entity_names(l_entity_arr_idx) := l_dep_cls_code;
1459 l_entity_arr_idx := l_entity_arr_idx + 1;
1460
1461 FETCH l_cursor INTO l_dep_cls_code;
1462 EXIT WHEN l_cursor%NOTFOUND;
1463 END LOOP;
1464 CLOSE l_cursor;
1465 END IF;
1466 l_entity_names.EXTEND;
1467 l_entity_names(l_entity_arr_idx):=l_model_org_count;
1468
1469 RETURN l_entity_names;
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1473 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1474 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1475 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1476 FND_MSG_PUB.Add;
1477 RAISE;
1478
1479 END Get_Err_Bom_Dept_Classes;
1480
1481 --=============================================================================
1482 -- FUNCTION NAME : Get_Err_Bom_Alt_Desig
1483 -- PRE-CONDITIONS: None.
1484 -- DESCRIPTION : Validate bom alternate designators created for new org
1485 -- against those in model org.
1486 -- PARAMETERS : None.
1487 -- EXCEPTIONS : None.
1488 --
1489 --=============================================================================
1490 FUNCTION Get_Err_Bom_Alt_Desig RETURN Char_Array
1491 IS
1492 l_alt_desig_code VARCHAR2(100);
1493 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Alt_Desig ';
1494 l_entity_arr_idx NUMBER := 1;
1495 l_entity_names Char_Array := Char_Array();
1496 --l_entity_type VARCHAR2(100):= ' Alternate Designators ';
1497 l_model_org_count NUMBER;
1498 l_new_org_count NUMBER;
1499
1500 CURSOR l_cursor ( p_model_org_id NUMBER
1501 , p_organization_id NUMBER
1502 )
1503 IS
1504 SELECT bad1.ALTERNATE_DESIGNATOR_CODE
1505 FROM BOM_ALTERNATE_DESIGNATORS bad1
1506 WHERE bad1.ORGANIZATION_ID=g_model_org_id
1507 AND NOT EXISTS
1508 ( SELECT bad2.ALTERNATE_DESIGNATOR_CODE
1509 FROM BOM_ALTERNATE_DESIGNATORS bad2
1510 WHERE bad2.ORGANIZATION_ID=g_organization_id
1511 AND bad2.ALTERNATE_DESIGNATOR_CODE=bad1.ALTERNATE_DESIGNATOR_CODE
1512 );
1513
1514 CURSOR l_cnt_csr ( p_org_id NUMBER )
1515 IS
1516 SELECT COUNT(bad.ALTERNATE_DESIGNATOR_CODE)
1517 FROM BOM_ALTERNATE_DESIGNATORS bad
1518 WHERE bad.ORGANIZATION_ID = p_org_id;
1519
1520 BEGIN
1521
1522 IF G_DEBUG = 'Y' THEN
1523 FND_MSG_PUB.ADD_EXC_MSG
1524 ( G_PKG_NAME
1525 , l_api_name
1526 , 'IN INVCORPB: '||l_api_name
1527 );
1528 END IF;
1529
1530 OPEN l_cnt_csr ( g_model_org_id );
1531 FETCH l_cnt_csr INTO l_model_org_count;
1532 CLOSE l_cnt_csr;
1533
1534 OPEN l_cnt_csr ( g_organization_id );
1535 FETCH l_cnt_csr INTO l_new_org_count;
1536 CLOSE l_cnt_csr;
1537
1538 IF l_new_org_count < l_model_org_count
1539 THEN
1540 OPEN l_cursor ( g_model_org_id, g_organization_id );
1541 FETCH l_cursor INTO l_alt_desig_code;
1542 LOOP
1543 l_entity_names.EXTEND;
1544 l_entity_names(l_entity_arr_idx) := l_alt_desig_code;
1545 l_entity_arr_idx := l_entity_arr_idx + 1;
1546
1547 FETCH l_cursor INTO l_alt_desig_code;
1548 EXIT WHEN l_cursor%NOTFOUND;
1549 END LOOP;
1550 CLOSE l_cursor;
1551 END IF;
1552
1553 l_entity_names.EXTEND;
1554 l_entity_names(l_entity_arr_idx):=l_model_org_count;
1555
1556 RETURN l_entity_names;
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1560 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1561 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1562 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1563 FND_MSG_PUB.Add;
1564 RAISE;
1565
1566 END Get_Err_Bom_Alt_Desig;
1567 --=============================================================================
1568 -- FUNCTION NAME : Get_Err_Ship_Net
1569 -- PRE-CONDITIONS: None.
1570 -- DESCRIPTION : Validate shipping networks created for new org against
1571 -- those in model org.
1572 -- PARAMETERS : None.
1573 -- EXCEPTIONS : None.
1574 --
1575 --=============================================================================
1576 FUNCTION Get_Err_Ship_Net RETURN Char_Array
1577 IS
1578 l_api_name VARCHAR2(100):= ' Get_Err_Ship_Net ';
1579 l_entity_arr_idx NUMBER := 1;
1580 l_entity_names Char_Array := Char_Array();
1581 --l_entity_type VARCHAR2(100):= ' Shipping Networks ';
1582 l_model_count_from NUMBER;
1583 l_model_count_to NUMBER;
1584 l_model_org_count NUMBER;
1585 l_new_count_from NUMBER;
1586 l_new_count_to NUMBER;
1587 l_new_org_count NUMBER;
1588 l_org_code VARCHAR2(100);
1589
1590 CURSOR l_from ( p_model_org_id NUMBER
1591 , p_organization_id NUMBER
1592 )
1593 IS
1594 SELECT mp.ORGANIZATION_CODE
1595 FROM MTL_INTERORG_PARAMETERS mip1
1596 , MTL_PARAMETERS mp
1597 WHERE mip1.FROM_ORGANIZATION_ID = p_model_org_id
1598 AND mp.ORGANIZATION_ID = mip1.TO_ORGANIZATION_ID
1599 AND NOT EXISTS
1600 ( SELECT 'x'
1601 FROM MTL_INTERORG_PARAMETERS mip2
1602 WHERE mip2.FROM_ORGANIZATION_ID = p_organization_id
1603 AND mip2.TO_ORGANIZATION_ID = mip1.TO_ORGANIZATION_ID
1604 );
1605
1606 CURSOR l_to ( p_model_org_id NUMBER
1607 , p_organization_id NUMBER
1608 )
1609 IS
1610 SELECT mp.ORGANIZATION_CODE
1611 FROM MTL_INTERORG_PARAMETERS mip1
1612 , MTL_PARAMETERS mp
1613 WHERE mip1.TO_ORGANIZATION_ID = p_model_org_id
1614 AND mp.ORGANIZATION_ID = mip1.FROM_ORGANIZATION_ID
1615 AND NOT EXISTS
1616 ( SELECT 'x'
1617 FROM MTL_INTERORG_PARAMETERS mip2
1618 WHERE mip2.TO_ORGANIZATION_ID = p_organization_id
1619 AND mip2.FROM_ORGANIZATION_ID = mip1.FROM_ORGANIZATION_ID
1620 );
1621
1622 CURSOR l_cnt_csr1 ( p_org_id NUMBER )
1623 IS
1624 SELECT COUNT(mip.TO_ORGANIZATION_ID)
1625 FROM MTL_INTERORG_PARAMETERS mip
1626 WHERE mip.FROM_ORGANIZATION_ID = p_org_id;
1627
1628 CURSOR l_cnt_csr2 ( p_org_id NUMBER )
1629 IS
1630 SELECT COUNT(mip.FROM_ORGANIZATION_ID)
1631 FROM MTL_INTERORG_PARAMETERS mip
1632 WHERE mip.TO_ORGANIZATION_ID = p_org_id;
1633
1634 BEGIN
1635
1636 IF G_DEBUG = 'Y' THEN
1637 FND_MSG_PUB.ADD_EXC_MSG
1638 ( G_PKG_NAME
1639 , l_api_name
1640 , 'IN INVCORPB: '||l_api_name
1641 );
1642 END IF;
1643
1644 OPEN l_cnt_csr1 ( g_model_org_id );
1645 FETCH l_cnt_csr1 INTO l_model_count_from;
1646 CLOSE l_cnt_csr1;
1647
1648 OPEN l_cnt_csr2 ( g_model_org_id );
1649 FETCH l_cnt_csr2 INTO l_model_count_to;
1650 CLOSE l_cnt_csr2;
1651
1652 OPEN l_cnt_csr1 ( g_organization_id );
1653 FETCH l_cnt_csr1 INTO l_new_count_from;
1654 CLOSE l_cnt_csr1;
1655
1656 OPEN l_cnt_csr2 ( g_organization_id );
1657 FETCH l_cnt_csr2 INTO l_new_count_to;
1658 CLOSE l_cnt_csr2;
1659
1660 IF l_new_count_from < l_model_count_from
1661 THEN
1662 OPEN l_from ( g_model_org_id, g_organization_id );
1663 FETCH l_from INTO l_org_code;
1664 LOOP
1665 l_entity_names.EXTEND;
1666 l_entity_names(l_entity_arr_idx) := l_org_code;
1667 l_entity_arr_idx := l_entity_arr_idx + 1;
1668
1669 FETCH l_from INTO l_org_code;
1670 EXIT WHEN l_from%NOTFOUND;
1671 END LOOP;
1672 CLOSE l_from;
1673 END IF;
1674
1675 IF l_new_count_to < l_model_count_to
1676 THEN
1677 OPEN l_to ( g_model_org_id, g_organization_id );
1678 FETCH l_to INTO l_org_code;
1679 LOOP
1680 l_entity_names.EXTEND;
1681 l_entity_names(l_entity_arr_idx) := l_org_code;
1682 l_entity_arr_idx := l_entity_arr_idx + 1;
1683
1684 FETCH l_to INTO l_org_code;
1685 EXIT WHEN l_to%NOTFOUND;
1686 END LOOP;
1687 CLOSE l_to;
1688 END IF;
1689 l_model_org_count := l_model_count_from + l_model_count_to;
1690 l_entity_names.EXTEND;
1691 l_entity_names(l_entity_arr_idx):=l_model_org_count;
1692 RETURN l_entity_names;
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
1696 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
1697 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
1698 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
1699 FND_MSG_PUB.Add;
1700 RAISE;
1701
1702 END Get_Err_Ship_Net;
1703 --=============================================================================
1704 -- FUNCTION NAME : Get_Err_Bom_Bom
1705 -- PRE-CONDITIONS: None.
1706 -- DESCRIPTION : Validate boms created for new org against
1707 -- those in model org.
1708 -- PARAMETERS : None.
1709 -- EXCEPTIONS : None.
1710 --
1711 --=============================================================================
1712 FUNCTION Get_Err_Bom_Bom RETURN Char_Array
1713 IS
1714 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Bom ';
1715 --l_count NUMBER:=0;
1716 l_p_model_count NUMBER;
1717 l_p_new_count NUMBER;
1718 l_c1_model_count NUMBER;
1719 l_c1_new_count NUMBER;
1720 l_c2_model_count NUMBER;
1721 l_c2_new_count NUMBER;
1722 l_c3_model_count NUMBER;
1723 l_c3_new_count NUMBER;
1724 l_c4_model_count NUMBER;
1725 l_c4_new_count NUMBER;
1726 l_count_tmp NUMBER;
1727 l_entity_names Char_Array := Char_Array();
1728 l_entity_names_fin Char_Array := Char_Array();
1729 l_entity_arr_idx NUMBER := 1;
1730 l_bom_code VARCHAR2(100);
1731 --l_entity_type VARCHAR2(100):= ' Bill of Materials ';
1732
1733 CURSOR l_parent ( p_model_org_id NUMBER
1734 , p_organization_id NUMBER
1735 )
1736 IS
1737 SELECT msi.CONCATENATED_SEGMENTS
1738 FROM BOM_BILL_OF_MATERIALS bom
1739 , MTL_SYSTEM_ITEMS_KFV msi
1740 WHERE msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1741 AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1742 AND bom.ORGANIZATION_ID = p_model_org_id
1743 AND NOT EXISTS
1744 ( SELECT 'x'
1745 FROM BOM_BILL_OF_MATERIALS bom1
1746 WHERE bom1.ORGANIZATION_ID = p_organization_id
1747 AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1748 /*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
1749 AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
1750 );
1751
1752 CURSOR l_parent_cnt ( p_org_id NUMBER )
1753 IS
1754 SELECT COUNT(ASSEMBLY_ITEM_ID)
1755 FROM BOM_STRUCTURES_B --myerrams, Bug: 4913484. Replaced the view BOM_BILL_OF_MATERIALS with BOM_STRUCTURES_B table
1756 WHERE ORGANIZATION_ID = p_org_id;
1757
1758 CURSOR l_child_1 ( p_model_org_id NUMBER
1759 , p_organization_id NUMBER
1760 )
1761 IS
1762 SELECT kfv.CONCATENATED_SEGMENTS, COUNT(bic.COMPONENT_SEQUENCE_ID)
1763 FROM BOM_INVENTORY_COMPONENTS bic
1764 , BOM_BILL_OF_MATERIALS bom
1765 , MTL_SYSTEM_ITEMS_KFV kfv
1766 WHERE bom.ORGANIZATION_ID = p_model_org_id
1767 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1768 AND kfv.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1769 AND kfv.ORGANIZATION_ID = bom.ORGANIZATION_ID
1770 AND NOT EXISTS
1771 (
1772 SELECT 'x'
1773 FROM BOM_INVENTORY_COMPONENTS bic1
1774 , BOM_BILL_OF_MATERIALS bom1
1775 WHERE bom1.ORGANIZATION_ID = p_organization_id
1776 AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1777 AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1778 /*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
1779 AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
1780 AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
1781 )
1782 GROUP BY kfv.CONCATENATED_SEGMENTS;
1783
1784 CURSOR l_child_1_cnt (p_org_id NUMBER)
1785 IS
1786 SELECT COUNT(bic.COMPONENT_SEQUENCE_ID)
1787 FROM BOM_INVENTORY_COMPONENTS bic
1788 , BOM_BILL_OF_MATERIALS bom
1789 WHERE bom.ORGANIZATION_ID = p_org_id
1790 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID;
1791
1792 CURSOR l_child_2 ( p_model_org_id NUMBER
1793 , p_organization_id NUMBER
1794 )
1795 IS
1796 SELECT msi.CONCATENATED_SEGMENTS, COUNT(bsc.COMPONENT_SEQUENCE_ID)
1797 FROM BOM_SUBSTITUTE_COMPONENTS bsc
1798 , BOM_INVENTORY_COMPONENTS bic
1799 , BOM_BILL_OF_MATERIALS bom
1800 , MTL_SYSTEM_ITEMS_KFV msi
1801 WHERE bom.ORGANIZATION_ID = p_model_org_id
1802 AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1803 AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1804 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1805 AND bsc.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1806 AND NOT EXISTS
1807 (
1808 SELECT 'x'
1809 FROM BOM_INVENTORY_COMPONENTS bic1
1810 , BOM_BILL_OF_MATERIALS bom1
1811 , BOM_SUBSTITUTE_COMPONENTS bsc1
1812 WHERE bom1.ORGANIZATION_ID = p_organization_id
1813 AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1814 AND bsc1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
1815 AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1816 /*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
1817 AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
1818 AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
1819 AND nvl(bsc.ACD_TYPE,1) = nvl(bsc1.ACD_TYPE,1)
1820 AND bsc.SUBSTITUTE_COMPONENT_ID = bsc1.SUBSTITUTE_COMPONENT_ID
1821 )
1822 GROUP BY MSI.CONCATENATED_SEGMENTS;
1823
1824 CURSOR l_child_2_cnt (p_org_id NUMBER)
1825 IS
1826 SELECT COUNT(bsc.SUBSTITUTE_COMPONENT_ID)
1827 FROM BOM_INVENTORY_COMPONENTS bic
1828 , BOM_BILL_OF_MATERIALS bom
1829 , BOM_SUBSTITUTE_COMPONENTS bsc
1830 WHERE bom.ORGANIZATION_ID = p_org_id
1831 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1832 AND bsc.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
1833
1834 CURSOR l_child_3 ( p_model_org_id NUMBER
1835 , p_organization_id NUMBER
1836 )
1837 IS
1838 SELECT msi.CONCATENATED_SEGMENTS, COUNT(brd.COMPONENT_SEQUENCE_ID)
1839 FROM BOM_REFERENCE_DESIGNATORS brd
1840 , BOM_INVENTORY_COMPONENTS bic
1841 , BOM_BILL_OF_MATERIALS bom
1842 , MTL_SYSTEM_ITEMS_KFV msi
1843 WHERE bom.ORGANIZATION_ID = p_model_org_id
1844 AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1845 AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1846 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1847 AND brd.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1848 AND NOT EXISTS
1849 (
1850 SELECT 'x'
1851 FROM BOM_INVENTORY_COMPONENTS bic1
1852 , BOM_BILL_OF_MATERIALS bom1
1853 , BOM_REFERENCE_DESIGNATORS brd1
1854 WHERE bom1.ORGANIZATION_ID = p_organization_id
1855 AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1856 AND brd1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
1857 AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1858 /*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
1859 AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
1860 AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
1861 AND brd.COMPONENT_REFERENCE_DESIGNATOR = brd1.COMPONENT_REFERENCE_DESIGNATOR
1862 AND nvl(brd.ACD_TYPE,1) = nvl(brd1.ACD_TYPE,1)
1863 )
1864 GROUP BY MSI.CONCATENATED_SEGMENTS;
1865
1866 CURSOR l_child_3_cnt (p_org_id NUMBER)
1867 IS
1868 SELECT COUNT(brd.COMPONENT_REFERENCE_DESIGNATOR)
1869 FROM BOM_INVENTORY_COMPONENTS bic
1870 , BOM_BILL_OF_MATERIALS bom
1871 , BOM_REFERENCE_DESIGNATORS brd
1872 WHERE bom.ORGANIZATION_ID = p_org_id
1873 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1874 AND brd.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
1875
1876 CURSOR l_child_4 ( p_model_org_id NUMBER
1877 , p_organization_id NUMBER
1878 )
1879 IS
1880 SELECT msi.CONCATENATED_SEGMENTS, COUNT(bco.COMPONENT_SEQUENCE_ID)
1881 FROM BOM_COMPONENT_OPERATIONS bco
1882 , BOM_INVENTORY_COMPONENTS bic
1883 , BOM_BILL_OF_MATERIALS bom
1884 , MTL_SYSTEM_ITEMS_KFV msi
1885 WHERE bom.ORGANIZATION_ID = p_model_org_id
1886 AND msi.ORGANIZATION_ID = bom.ORGANIZATION_ID
1887 AND msi.INVENTORY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1888 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1889 AND bco.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID
1890 AND NOT EXISTS
1891 (
1892 SELECT 'x'
1893 FROM BOM_INVENTORY_COMPONENTS bic1
1894 , BOM_BILL_OF_MATERIALS bom1
1895 , BOM_COMPONENT_OPERATIONS bco1
1896 WHERE bom1.ORGANIZATION_ID = p_organization_id
1897 AND bic1.BILL_SEQUENCE_ID = bom1.BILL_SEQUENCE_ID
1898 AND bco1.COMPONENT_SEQUENCE_ID = bic1.COMPONENT_SEQUENCE_ID
1899 AND bom1.ASSEMBLY_ITEM_ID = bom.ASSEMBLY_ITEM_ID
1900 /*vmutyala added this condition because alternate boms which are not copied are not being reported by earlier query*/
1901 AND nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NULL') = nvl(bom1.ALTERNATE_BOM_DESIGNATOR,'NULL')
1902 AND nvl(bic.COMPONENT_ITEM_ID,1) = nvl(bic1.COMPONENT_ITEM_ID,1)
1903 )
1904 GROUP BY MSI.CONCATENATED_SEGMENTS;
1905
1906 CURSOR l_child_4_cnt (p_org_id NUMBER)
1907 IS
1908 SELECT COUNT(bco.COMP_OPERATION_SEQ_ID)
1909 FROM BOM_INVENTORY_COMPONENTS bic
1910 , BOM_BILL_OF_MATERIALS bom
1911 , BOM_COMPONENT_OPERATIONS bco
1912 WHERE bom.ORGANIZATION_ID = p_org_id
1913 AND bic.BILL_SEQUENCE_ID = bom.BILL_SEQUENCE_ID
1914 AND bco.COMPONENT_SEQUENCE_ID = bic.COMPONENT_SEQUENCE_ID;
1915
1916 BEGIN
1917
1918 IF G_DEBUG = 'Y' THEN
1919 FND_MSG_PUB.ADD_EXC_MSG
1920 ( G_PKG_NAME
1921 , l_api_name
1922 , 'IN INVCORPB: '||l_api_name
1923 );
1924 END IF;
1925
1926 OPEN l_parent_cnt ( g_model_org_id );
1927 FETCH l_parent_cnt INTO l_p_model_count;
1928 CLOSE l_parent_cnt;
1929
1930 OPEN l_parent_cnt ( g_organization_id );
1931 FETCH l_parent_cnt INTO l_p_new_count;
1932 CLOSE l_parent_cnt;
1933
1934 IF l_p_new_count < l_p_model_count
1935 THEN
1936 OPEN l_parent ( g_model_org_id
1937 , g_organization_id
1938 );
1939 FETCH l_parent INTO l_bom_code;
1940 LOOP
1941 l_entity_names.EXTEND;
1942 l_entity_names(l_entity_arr_idx) := l_bom_code;
1943 l_entity_arr_idx := l_entity_arr_idx + 1;
1944
1945 FETCH l_parent INTO l_bom_code;
1946 EXIT WHEN l_parent%NOTFOUND;
1947 END LOOP;
1948 CLOSE l_parent;
1949
1950 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
1951 l_entity_names := l_entity_names_fin;
1952 l_entity_arr_idx := l_entity_names.COUNT + 1;
1953
1954 END IF;
1955
1956 OPEN l_child_1_cnt ( g_model_org_id );
1957 FETCH l_child_1_cnt INTO l_c1_model_count;
1958 CLOSE l_child_1_cnt;
1959
1960 OPEN l_child_1_cnt ( g_organization_id );
1961 FETCH l_child_1_cnt INTO l_c1_new_count;
1962 CLOSE l_child_1_cnt;
1963
1964 IF l_c1_new_count < l_c1_model_count
1965 THEN
1966 OPEN l_child_1 ( g_model_org_id
1967 , g_organization_id
1968 );
1969 FETCH l_child_1 INTO l_bom_code, l_count_tmp;
1970 LOOP
1971 l_entity_names.EXTEND;
1972 l_entity_names(l_entity_arr_idx) := l_bom_code;
1973 l_entity_arr_idx := l_entity_arr_idx + 1;
1974
1975 FETCH l_child_1 INTO l_bom_code, l_count_tmp;
1976 EXIT WHEN l_child_1%NOTFOUND;
1977 END LOOP;
1978 CLOSE l_child_1;
1979
1980 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
1981 l_entity_names := l_entity_names_fin;
1982 l_entity_arr_idx := l_entity_names.COUNT + 1;
1983 END IF;
1984
1985 OPEN l_child_2_cnt ( g_model_org_id );
1986 FETCH l_child_2_cnt INTO l_c2_model_count;
1987 CLOSE l_child_2_cnt;
1988
1989 OPEN l_child_2_cnt ( g_organization_id );
1990 FETCH l_child_2_cnt INTO l_c2_new_count;
1991 CLOSE l_child_2_cnt;
1992
1993 IF l_c2_new_count < l_c2_model_count
1994 THEN
1995 OPEN l_child_2 ( g_model_org_id
1996 , g_organization_id
1997 );
1998 FETCH l_child_2 INTO l_bom_code, l_count_tmp;
1999 LOOP
2000 l_entity_names.EXTEND;
2001 l_entity_names(l_entity_arr_idx) := l_bom_code;
2002 l_entity_arr_idx := l_entity_arr_idx + 1;
2003
2004 FETCH l_child_2 INTO l_bom_code, l_count_tmp;
2005 EXIT WHEN l_child_2%NOTFOUND;
2006 END LOOP;
2007 CLOSE l_child_2;
2008
2009 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2010 l_entity_names := l_entity_names_fin;
2011 l_entity_arr_idx := l_entity_names.COUNT + 1;
2012
2013 END IF;
2014
2015 OPEN l_child_3_cnt ( g_model_org_id );
2016 FETCH l_child_3_cnt INTO l_c3_model_count;
2017 CLOSE l_child_3_cnt;
2018
2019 OPEN l_child_3_cnt ( g_organization_id );
2020 FETCH l_child_3_cnt INTO l_c3_new_count;
2021 CLOSE l_child_3_cnt;
2022
2023 IF l_c3_new_count < l_c3_model_count
2024 THEN
2025 OPEN l_child_3 ( g_model_org_id
2026 , g_organization_id
2027 );
2028 FETCH l_child_3 INTO l_bom_code, l_count_tmp;
2029 LOOP
2030 l_entity_names.EXTEND;
2031 l_entity_names(l_entity_arr_idx) := l_bom_code;
2032 l_entity_arr_idx := l_entity_arr_idx + 1;
2033
2034 FETCH l_child_3 INTO l_bom_code, l_count_tmp;
2035 EXIT WHEN l_child_3%NOTFOUND;
2036 END LOOP;
2037 CLOSE l_child_3;
2038
2039 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2040 l_entity_names := l_entity_names_fin;
2041 l_entity_arr_idx := l_entity_names.COUNT + 1;
2042
2043 END IF;
2044
2045 OPEN l_child_4_cnt ( g_model_org_id );
2046 FETCH l_child_4_cnt INTO l_c4_model_count;
2047 CLOSE l_child_4_cnt;
2048
2049 OPEN l_child_4_cnt ( g_organization_id );
2050 FETCH l_child_4_cnt INTO l_c4_new_count;
2051 CLOSE l_child_4_cnt;
2052
2053 IF l_c4_new_count < l_c4_model_count
2054 THEN
2055 OPEN l_child_4 ( g_model_org_id
2056 , g_organization_id
2057 );
2058 FETCH l_child_4 INTO l_bom_code, l_count_tmp;
2059 LOOP
2060 l_entity_names.EXTEND;
2061 l_entity_names(l_entity_arr_idx) := l_bom_code;
2062 l_entity_arr_idx := l_entity_arr_idx + 1;
2063
2064 FETCH l_child_4 INTO l_bom_code, l_count_tmp;
2065 EXIT WHEN l_child_4%NOTFOUND;
2066 END LOOP;
2067 CLOSE l_child_4;
2068
2069 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2070 l_entity_names := l_entity_names_fin;
2071 l_entity_arr_idx := l_entity_names.COUNT + 1;
2072
2073 END IF;
2074
2075 l_entity_names.EXTEND;
2076 l_entity_names(l_entity_arr_idx):=l_p_model_count;
2077
2078 RETURN l_entity_names;
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2082 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2083 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2084 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2085 FND_MSG_PUB.Add;
2086 RAISE;
2087
2088 END Get_Err_Bom_Bom;
2089
2090 --=============================================================================
2091 -- FUNCTION NAME : Get_Err_Org_Information
2092 -- PRE-CONDITIONS: None.
2093 -- DESCRIPTION : Validate org information created for new org against
2094 -- those in model org.
2095 -- PARAMETERS : None.
2096 -- EXCEPTIONS : None.
2097 --
2098 --=============================================================================
2099 FUNCTION Get_Err_Org_Information RETURN Char_Array
2100 IS
2101 l_api_name VARCHAR2(100):= ' Get_Err_Org_Information ';
2102 --l_count NUMBER:=0;
2103 l_model_org_count NUMBER:=0;
2104 l_new_org_count NUMBER:=0;
2105 l_entity_names Char_Array := Char_Array();
2106 l_entity_arr_idx NUMBER := 1;
2107 l_org_info_context VARCHAR2(100);
2108 --l_entity_type VARCHAR2(100):= ' Organization Information ';
2109
2110 CURSOR l_cursor ( p_model_org_id NUMBER
2111 , p_organization_id NUMBER
2112 )
2113 IS
2114 SELECT ent.ORG_INFORMATION_CONTEXT
2115 FROM HR_ORGANIZATION_INFORMATION ent
2116 WHERE ent.ORGANIZATION_ID = p_model_org_id
2117 AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
2118 AND ORG_INFORMATION1 = 'INV'
2119 )
2120 OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
2121 AND ORG_INFORMATION_CONTEXT IN
2122 ( SELECT ORG_INFORMATION_TYPE
2123 FROM HR_ORG_INFO_TYPES_BY_CLASS
2124 WHERE ORG_CLASSIFICATION = 'INV'
2125 )
2126 )
2127 )
2128 MINUS
2129 SELECT ent.ORG_INFORMATION_CONTEXT
2130 FROM HR_ORGANIZATION_INFORMATION ent
2131 WHERE ent.ORGANIZATION_ID = p_organization_id
2132 AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
2133 AND ORG_INFORMATION1 = 'INV'
2134 )
2135 OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
2136 AND ORG_INFORMATION_CONTEXT IN
2137 ( SELECT ORG_INFORMATION_TYPE
2138 FROM HR_ORG_INFO_TYPES_BY_CLASS
2139 WHERE ORG_CLASSIFICATION = 'INV'
2140 )
2141 )
2142 );
2143
2144 CURSOR l_cursor_cnt ( p_org_id NUMBER )
2145 IS
2146 SELECT COUNT(ent.ORG_INFORMATION_CONTEXT)
2147 FROM HR_ORGANIZATION_INFORMATION ent
2148 WHERE ent.ORGANIZATION_ID = p_org_id
2149 AND ( (ORG_INFORMATION_CONTEXT = 'CLASS'
2150 AND ORG_INFORMATION1 = 'INV'
2151 )
2152 OR (ORG_INFORMATION_CONTEXT <> 'CLASS'
2153 AND ORG_INFORMATION_CONTEXT IN
2154 ( SELECT ORG_INFORMATION_TYPE
2155 FROM HR_ORG_INFO_TYPES_BY_CLASS
2156 WHERE ORG_CLASSIFICATION = 'INV'
2157 )
2158 )
2159 );
2160
2161 BEGIN
2162
2163 IF G_DEBUG = 'Y' THEN
2164 FND_MSG_PUB.ADD_EXC_MSG
2165 ( G_PKG_NAME
2166 , l_api_name
2167 , 'IN INVCORPB: '||l_api_name
2168 );
2169 END IF;
2170
2171 OPEN l_cursor_cnt ( g_model_org_id );
2172 FETCH l_cursor_cnt INTO l_model_org_count;
2173 CLOSE l_cursor_cnt;
2174
2175 OPEN l_cursor_cnt ( g_organization_id );
2176 FETCH l_cursor_cnt INTO l_new_org_count;
2177 CLOSE l_cursor_cnt;
2178
2179 IF l_new_org_count < l_model_org_count
2180 THEN
2181 OPEN l_cursor ( g_model_org_id, g_organization_id );
2182 FETCH l_cursor INTO l_org_info_context;
2183 LOOP
2184 IF l_org_info_context <> null
2185 AND l_org_info_context <> ''
2186 THEN
2187 l_entity_names.EXTEND;
2188 l_entity_names(l_entity_arr_idx) := l_org_info_context;
2189 l_entity_arr_idx := l_entity_arr_idx + 1;
2190
2191 -- moved these statements after if block
2192 -- for bug 3623168
2193 -- FETCH l_cursor INTO l_org_info_context;
2194 -- EXIT WHEN l_cursor%NOTFOUND;
2195 END IF;
2196 -- Added these two statements for bug 3623168
2197 FETCH l_cursor INTO l_org_info_context;
2198 EXIT WHEN l_cursor%NOTFOUND;
2199
2200 END LOOP;
2201 CLOSE l_cursor;
2202 END IF;
2203
2204 l_entity_names.EXTEND;
2205 l_entity_names(l_entity_arr_idx):=to_char(l_model_org_count);
2206
2207 RETURN l_entity_names;
2208 EXCEPTION
2209 WHEN OTHERS THEN
2210 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2211 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2212 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2213 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2214 FND_MSG_PUB.Add;
2215 RAISE;
2216
2217 END Get_Err_Org_Information;
2218
2219 --=============================================================================
2220 -- FUNCTION NAME : Get_Err_Mtl_Items
2221 -- PRE-CONDITIONS: None.
2222 -- DESCRIPTION : Validate items, categories created for new org against
2223 -- those in model org.
2224 -- PARAMETERS : None.
2225 --
2226 -- EXCEPTIONS : None.
2227 --
2228 --=============================================================================
2229 FUNCTION Get_Err_Mtl_Items RETURN Char_Array
2230 IS
2231 l_api_name VARCHAR2(100):= ' Get_Err_Mtl_Items ';
2232 l_entity_arr_idx NUMBER := 1;
2233 l_entity_names Char_Array := Char_Array();
2234 --l_entity_type VARCHAR2(100):= ' Items ';
2235 l_item_name VARCHAR2(100);
2236 l_model_org_count NUMBER:=0;
2237 l_new_org_count NUMBER:=0;
2238
2239 CURSOR l_cursor ( p_model_org_id NUMBER
2240 , p_organization_id NUMBER
2241 )
2242 IS
2243 SELECT kfv.CONCATENATED_SEGMENTS
2244 FROM MTL_SYSTEM_ITEMS_B msi1
2245 , MTL_SYSTEM_ITEMS_KFV kfv
2246 WHERE kfv.ORGANIZATION_ID = msi1.ORGANIZATION_ID
2247 AND kfv.INVENTORY_ITEM_ID = msi1.INVENTORY_ITEM_ID
2248 AND msi1.ORGANIZATION_ID = p_model_org_id
2249 AND NOT EXISTS
2250 ( SELECT 'x'
2251 FROM MTL_SYSTEM_ITEMS_B msi2
2252 WHERE msi2.INVENTORY_ITEM_ID = msi1.INVENTORY_ITEM_ID
2253 AND msi2.ORGANIZATION_ID = p_organization_id
2254 );
2255
2256 CURSOR l_cnt_csr ( p_org_id NUMBER )
2257 IS
2258 SELECT COUNT(msi.INVENTORY_ITEM_ID)
2259 FROM MTL_SYSTEM_ITEMS_B msi
2260 WHERE msi.ORGANIZATION_ID = p_org_id;
2261
2262 BEGIN
2263
2264 IF G_DEBUG = 'Y' THEN
2265 FND_MSG_PUB.ADD_EXC_MSG
2266 ( G_PKG_NAME
2267 , l_api_name
2268 , 'IN INVCORPB: '||l_api_name
2269 );
2270 END IF;
2271
2272 OPEN l_cnt_csr ( g_model_org_id );
2273 FETCH l_cnt_csr INTO l_model_org_count;
2274 CLOSE l_cnt_csr;
2275
2276 OPEN l_cnt_csr ( g_organization_id );
2277 FETCH l_cnt_csr INTO l_new_org_count;
2278 CLOSE l_cnt_csr;
2279
2280 IF l_new_org_count < l_model_org_count
2281 THEN
2282 OPEN l_cursor ( g_model_org_id
2283 , g_organization_id
2284 );
2285 FETCH l_cursor INTO l_item_name;
2286 LOOP
2287 l_entity_names.EXTEND;
2288 l_entity_names(l_entity_arr_idx) := l_item_name;
2289 l_entity_arr_idx := l_entity_arr_idx + 1;
2290
2291 FETCH l_cursor INTO l_item_name;
2292 EXIT WHEN l_cursor%NOTFOUND;
2293 END LOOP;
2294 CLOSE l_cursor;
2295 END IF;
2296
2297 l_entity_names.EXTEND;
2298 l_entity_names(l_entity_arr_idx):=l_model_org_count;
2299
2300 RETURN l_entity_names;
2301 EXCEPTION
2302 WHEN OTHERS THEN
2303 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2304 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2305 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2306 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2307 FND_MSG_PUB.Add;
2308 RAISE;
2309
2310 END Get_Err_Mtl_Items;
2311
2312 --=============================================================================
2313 -- FUNCTION NAME : Get_Err_Mtl_Item_Cat
2314 -- PRE-CONDITIONS: None.
2315 -- DESCRIPTION : Validate item categories created for new org against
2316 -- those in model org.
2317 -- PARAMETERS : None.
2318 -- EXCEPTIONS : None.
2319 --
2320 --=============================================================================
2321 FUNCTION Get_Err_Mtl_Item_Cat RETURN Char_Array
2322 IS
2323 l_api_name VARCHAR2(100):=' Get_Err_Mtl_Item_Cat ';
2324 l_entity_arr_idx NUMBER:=1;
2325 l_entity_names Char_Array:= Char_Array();
2326 --l_entity_type VARCHAR2(100):=' Item Categories ';
2327 l_item_name VARCHAR2(100);
2328 l_model_org_count NUMBER:=0;
2329 l_new_org_count NUMBER:=0;
2330
2331 CURSOR l_cursor ( p_model_org_id NUMBER
2332 , p_organization_id NUMBER
2333 )
2334 IS
2335 SELECT distinct kfv.CONCATENATED_SEGMENTS
2336 FROM MTL_ITEM_CATEGORIES mic1
2337 , MTL_CATEGORIES_KFV kfv
2338 WHERE kfv.CATEGORY_ID = mic1.CATEGORY_ID
2339 AND mic1.ORGANIZATION_ID = p_model_org_id
2340 AND NOT EXISTS
2341 ( SELECT 'x'
2342 FROM MTL_ITEM_CATEGORIES mic2
2343 WHERE mic2.CATEGORY_ID = mic1.CATEGORY_ID
2344 AND mic2.ORGANIZATION_ID = p_organization_id
2345 );
2346
2347 CURSOR l_cnt_csr ( p_org_id NUMBER )
2348 IS
2349 SELECT COUNT(DISTINCT kfv.CONCATENATED_SEGMENTS)
2350 FROM MTL_ITEM_CATEGORIES mic1
2351 , MTL_CATEGORIES_KFV kfv
2352 WHERE kfv.CATEGORY_ID = mic1.CATEGORY_ID
2353 AND mic1.ORGANIZATION_ID = p_org_id;
2354
2355 BEGIN
2356
2357 IF G_DEBUG = 'Y' THEN
2358 FND_MSG_PUB.ADD_EXC_MSG
2359 ( G_PKG_NAME
2360 , l_api_name
2361 , 'IN INVCORPB: '||l_api_name
2362 );
2363 END IF;
2364
2365 OPEN l_cnt_csr ( g_model_org_id );
2366 FETCH l_cnt_csr INTO l_model_org_count;
2367 CLOSE l_cnt_csr;
2368
2369 OPEN l_cnt_csr ( g_organization_id );
2370 FETCH l_cnt_csr INTO l_new_org_count;
2371 CLOSE l_cnt_csr;
2372
2373 IF l_new_org_count < l_model_org_count
2374 THEN
2375 OPEN l_cursor ( g_model_org_id
2376 , g_organization_id
2377 );
2378 FETCH l_cursor INTO l_item_name;
2379 LOOP
2380 l_entity_names.EXTEND;
2381 l_entity_names(l_entity_arr_idx) := l_item_name;
2382 l_entity_arr_idx := l_entity_arr_idx + 1;
2383
2384 FETCH l_cursor INTO l_item_name;
2385 EXIT WHEN l_cursor%NOTFOUND;
2386 END LOOP;
2387 CLOSE l_cursor;
2388 END IF;
2389
2390 l_entity_names.EXTEND;
2391 l_entity_names(l_entity_arr_idx):=l_model_org_count;
2392
2393 RETURN l_entity_names;
2394 EXCEPTION
2395 WHEN OTHERS THEN
2396 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2397 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2398 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2399 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2400 FND_MSG_PUB.Add;
2401 RAISE;
2402
2403 END Get_Err_Mtl_Item_Cat;
2404
2405 --=============================================================================
2406 -- FUNCTION NAME : Get_Err_Mtl_Item_Rev
2407 -- PRE-CONDITIONS: None.
2408 -- DESCRIPTION : Validate item revisions created for new org against
2409 -- those in model org.
2410 -- PARAMETERS : None.
2411 -- EXCEPTIONS : None.
2412 --
2413 --=============================================================================
2414 FUNCTION Get_Err_Mtl_Item_Rev RETURN Char_Array
2415 IS
2416 l_api_name VARCHAR2(100):=' Get_Err_Mtl_Item_Rev ';
2417 l_entity_arr_idx NUMBER:=1;
2418 l_entity_names Char_Array:= Char_Array();
2419 --l_entity_type VARCHAR2(100):=' Item Revisions ';
2420 l_item_name VARCHAR2(100);
2421 l_model_org_count NUMBER:=0;
2422 l_new_org_count NUMBER:=0;
2423
2424 CURSOR l_cursor ( p_model_org_id NUMBER
2425 , p_organization_id NUMBER
2426 )
2427 IS
2428 SELECT kfv.CONCATENATED_SEGMENTS||': '||rev1.REVISION
2429 FROM MTL_ITEM_REVISIONS rev1
2430 , MTL_SYSTEM_ITEMS_KFV kfv
2431 WHERE kfv.ORGANIZATION_ID = rev1.ORGANIZATION_ID
2432 AND kfv.INVENTORY_ITEM_ID = rev1.INVENTORY_ITEM_ID
2433 AND rev1.ORGANIZATION_ID = p_model_org_id
2434 AND NOT EXISTS
2435 ( SELECT 'x'
2436 FROM MTL_ITEM_REVISIONS rev2
2437 WHERE rev2.REVISION = rev1.REVISION
2438 AND rev2.INVENTORY_ITEM_ID = rev1.INVENTORY_ITEM_ID
2439 AND rev2.ORGANIZATION_ID = p_organization_id
2440 );
2441
2442 -- Bug : 3637921 Modified the MTL_ITEM_REVISIONS view usage with
2443 -- with the MTL_ITEM_REVISIONS_B base table.
2444 CURSOR l_cnt_csr ( p_org_id NUMBER )
2445 IS
2446 SELECT COUNT(rev.REVISION_ID)
2447 FROM MTL_ITEM_REVISIONS_B rev
2448 WHERE rev.ORGANIZATION_ID=p_org_id;
2449
2450 BEGIN
2451
2452 IF G_DEBUG = 'Y' THEN
2453 FND_MSG_PUB.ADD_EXC_MSG
2454 ( G_PKG_NAME
2455 , l_api_name
2456 , 'IN INVCORPB: '||l_api_name
2457 );
2458 END IF;
2459
2460 OPEN l_cnt_csr ( g_model_org_id );
2461 FETCH l_cnt_csr INTO l_model_org_count;
2462 CLOSE l_cnt_csr;
2463
2464 OPEN l_cnt_csr ( g_organization_id );
2465 FETCH l_cnt_csr INTO l_new_org_count;
2466 CLOSE l_cnt_csr;
2467
2468 IF l_new_org_count < l_model_org_count
2469 THEN
2470 OPEN l_cursor ( g_model_org_id
2471 , g_organization_id
2472 );
2473 FETCH l_cursor INTO l_item_name;
2474 LOOP
2475 l_entity_names.EXTEND;
2476 l_entity_names(l_entity_arr_idx) := l_item_name;
2477 l_entity_arr_idx := l_entity_arr_idx + 1;
2478
2479 FETCH l_cursor INTO l_item_name;
2480 EXIT WHEN l_cursor%NOTFOUND;
2481 END LOOP;
2482 CLOSE l_cursor;
2483
2484 END IF;
2485
2486 l_entity_names.EXTEND;
2487 l_entity_names(l_entity_arr_idx):=l_model_org_count;
2488
2489 RETURN l_entity_names;
2490 EXCEPTION
2491 WHEN OTHERS THEN
2492 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2493 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2494 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2495 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2496 FND_MSG_PUB.Add;
2497 RAISE;
2498
2499 END Get_Err_Mtl_Item_Rev;
2500
2501 --=============================================================================
2502 -- FUNCTION NAME : Get_Err_Mtl_Items_Subinv
2503 -- PRE-CONDITIONS: None.
2504 -- DESCRIPTION : Validate item subinventories created for new org against
2505 -- those in model org.
2506 -- PARAMETERS : None.
2507 -- EXCEPTIONS : None.
2508 --
2509 --=============================================================================
2510 FUNCTION Get_Err_Mtl_Items_Subinv RETURN Char_Array
2511 IS
2512 l_api_name VARCHAR2(100):= ' Get_Err_Mtl_Items_Subinv ';
2513 l_model_org_count NUMBER;
2514 l_new_org_count NUMBER;
2515 l_entity_names Char_Array := Char_Array();
2516 l_entity_arr_idx NUMBER := 1;
2517 l_item_name VARCHAR2(100);
2518 --l_entity_type VARCHAR2(100):= ' Item Subinventory ';
2519
2520 CURSOR l_cursor ( p_model_org_id NUMBER
2521 , p_organization_id NUMBER
2522 )
2523 IS
2524 SELECT kfv.CONCATENATED_SEGMENTS||': '||sub1.SECONDARY_INVENTORY
2525 FROM MTL_ITEM_SUB_INVENTORIES sub1
2526 , MTL_SYSTEM_ITEMS_KFV kfv
2527 WHERE kfv.ORGANIZATION_ID = sub1.ORGANIZATION_ID
2528 AND kfv.INVENTORY_ITEM_ID = sub1.INVENTORY_ITEM_ID
2529 AND sub1.ORGANIZATION_ID = p_model_org_id
2530 AND NOT EXISTS
2531 ( SELECT 'x'
2532 FROM MTL_ITEM_SUB_INVENTORIES sub2
2533 WHERE sub2.SECONDARY_INVENTORY = sub1.SECONDARY_INVENTORY
2534 AND sub2.INVENTORY_ITEM_ID = sub1.INVENTORY_ITEM_ID
2535 AND sub2.ORGANIZATION_ID = p_organization_id
2536 );
2537
2538 CURSOR l_cnt_csr ( p_org_id NUMBER )
2539 IS
2540 SELECT COUNT(sub.SECONDARY_INVENTORY)
2541 FROM MTL_ITEM_SUB_INVENTORIES sub
2542 WHERE sub.ORGANIZATION_ID=p_org_id;
2543
2544 BEGIN
2545
2546 IF G_DEBUG = 'Y' THEN
2547 FND_MSG_PUB.ADD_EXC_MSG
2548 ( G_PKG_NAME
2549 , l_api_name
2550 , 'IN INVCORPB: '||l_api_name
2551 );
2552 END IF;
2553
2554 OPEN l_cnt_csr ( g_model_org_id );
2555 FETCH l_cnt_csr INTO l_model_org_count;
2556 CLOSE l_cnt_csr;
2557
2558 OPEN l_cnt_csr ( g_organization_id );
2559 FETCH l_cnt_csr INTO l_new_org_count;
2560 CLOSE l_cnt_csr;
2561
2562 IF l_new_org_count < l_model_org_count
2563 THEN
2564 OPEN l_cursor ( g_model_org_id
2565 , g_organization_id
2566 );
2567 FETCH l_cursor INTO l_item_name;
2568 LOOP
2569 l_entity_names.EXTEND;
2570 l_entity_names(l_entity_arr_idx) := l_item_name;
2571 l_entity_arr_idx := l_entity_arr_idx + 1;
2572
2573 FETCH l_cursor INTO l_item_name;
2574 EXIT WHEN l_cursor%NOTFOUND;
2575 END LOOP;
2576 CLOSE l_cursor;
2577 END IF;
2578
2579 l_entity_names.EXTEND;
2580 l_entity_names(l_entity_arr_idx):=l_model_org_count;
2581
2582 RETURN l_entity_names;
2583 EXCEPTION
2584 WHEN OTHERS THEN
2585 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2586 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2587 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2588 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2589 FND_MSG_PUB.Add;
2590 RAISE;
2591
2592 END Get_Err_Mtl_Items_Subinv;
2593 --=============================================================================
2594 -- FUNCTION NAME : Get_Err_Mtl_Item_Locations
2595 -- PRE-CONDITIONS: None.
2596 -- DESCRIPTION : Validate Item Locations created for new org against
2597 -- those in model org.
2598 -- PARAMETERS : None.
2599 -- EXCEPTIONS : None.
2600 --
2601 --=============================================================================
2602 FUNCTION Get_Err_Mtl_Item_Locations RETURN Char_Array
2603 IS
2604 l_api_name VARCHAR2(100):= ' Get_Err_Mtl_Item_Locations ';
2605 l_entity_arr_idx NUMBER := 1;
2606 l_entity_names Char_Array := Char_Array();
2607 --l_entity_type VARCHAR2(100):= ' Item Locations ';
2608 l_item_name VARCHAR2(100);
2609 l_model_org_count NUMBER;
2610 l_new_org_count NUMBER:=0;
2611
2612 CURSOR l_cursor ( p_model_org_id NUMBER
2613 , p_organization_id NUMBER
2614 )
2615 IS
2616 SELECT kfv1.CONCATENATED_SEGMENTS
2617 FROM MTL_ITEM_LOCATIONS loc1
2618 , MTL_ITEM_LOCATIONS_KFV kfv1
2619 WHERE kfv1.ORGANIZATION_ID=loc1.ORGANIZATION_ID
2620 AND kfv1.INVENTORY_LOCATION_ID=loc1.INVENTORY_LOCATION_ID
2621 AND loc1.ORGANIZATION_ID=p_model_org_id
2622 AND NOT EXISTS
2623 ( SELECT 'x'
2624 FROM MTL_ITEM_LOCATIONS loc2
2625 , MTL_ITEM_LOCATIONS_KFV kfv2
2626 WHERE loc2.ORGANIZATION_ID=p_organization_id
2627 AND kfv2.INVENTORY_LOCATION_ID=loc2.INVENTORY_LOCATION_ID
2628 AND kfv2.ORGANIZATION_ID=loc2.ORGANIZATION_ID
2629 AND kfv2.CONCATENATED_SEGMENTS=kfv1.CONCATENATED_SEGMENTS
2630 );
2631
2632 CURSOR l_cnt_csr ( p_org_id NUMBER )
2633 IS
2634 SELECT COUNT(loc.INVENTORY_LOCATION_ID)
2635 FROM MTL_ITEM_LOCATIONS loc
2636 WHERE loc.ORGANIZATION_ID=p_org_id;
2637
2638 BEGIN
2639
2640 IF G_DEBUG = 'Y' THEN
2641 FND_MSG_PUB.ADD_EXC_MSG
2642 ( G_PKG_NAME
2643 , l_api_name
2644 , 'IN INVCORPB: '||l_api_name
2645 );
2646 END IF;
2647
2648 OPEN l_cnt_csr ( g_model_org_id );
2649 FETCH l_cnt_csr INTO l_model_org_count;
2650 CLOSE l_cnt_csr;
2651
2652 OPEN l_cnt_csr ( g_organization_id );
2653 FETCH l_cnt_csr INTO l_new_org_count;
2654 CLOSE l_cnt_csr;
2655
2656 IF l_new_org_count < l_model_org_count
2657 THEN
2658 OPEN l_cursor ( g_model_org_id
2659 , g_organization_id
2660 );
2661 FETCH l_cursor INTO l_item_name;
2662 LOOP
2663 l_entity_names.EXTEND;
2664 l_entity_names(l_entity_arr_idx) := l_item_name;
2665 l_entity_arr_idx := l_entity_arr_idx + 1;
2666
2667 FETCH l_cursor INTO l_item_name;
2668 EXIT WHEN l_cursor%NOTFOUND;
2669 END LOOP;
2670 CLOSE l_cursor;
2671 END IF;
2672
2673 l_entity_names.EXTEND;
2674 l_entity_names(l_entity_arr_idx):=l_model_org_count;
2675
2676 RETURN l_entity_names;
2677 EXCEPTION
2678 WHEN OTHERS THEN
2679 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2680 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2681 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2682 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2683 FND_MSG_PUB.Add;
2684 RAISE;
2685
2686 END Get_Err_Mtl_Item_Locations;
2687 --=============================================================================
2688 -- FUNCTION NAME : Get_Unique_List
2689 -- PRE-CONDITIONS: None.
2690 -- DESCRIPTION : Remove duplicate entries from an array.
2691 -- PARAMETERS :
2692 -- p_array REQUIRED. Array containing duplicate records.
2693 --
2694 -- RETURNS : Array containing unique records ( Char_Array )
2695 -- EXCEPTIONS : None.
2696 --
2697 --=============================================================================
2698 FUNCTION Get_Unique_List ( p_array Char_Array ) RETURN Char_Array
2699 IS
2700 l_unique_list Char_Array := Char_Array();
2701 l_counter NUMBER := 1;
2702 b_duplicate_rec BOOLEAN := false;
2703 BEGIN
2704 l_unique_list.extend(p_array.COUNT);
2705 FOR j IN 1..p_array.COUNT
2706 LOOP
2707 FOR i IN 1..j
2708 LOOP
2709 IF ( p_array(j) = l_unique_list(i) )
2710 THEN
2711 b_duplicate_rec := true;
2712 END IF;
2713 END LOOP;
2714 IF NOT b_duplicate_rec
2715 THEN
2716 l_unique_list(l_counter) := p_array(j);
2717 l_counter := l_counter + 1;
2718 END IF;
2719 b_duplicate_rec := false;
2720 END LOOP;
2721 RETURN (l_unique_list);
2722 END Get_Unique_List;
2723
2724 --=============================================================================
2725 -- FUNCTION NAME : Get_Err_StdOperations
2726 -- PRE-CONDITIONS: None.
2727 -- DESCRIPTION : Validate StandardOperations created for new org against
2728 -- those in model org.
2729 -- PARAMETERS :
2730 -- EXCEPTIONS : None.
2731 --
2732 --=============================================================================
2733 FUNCTION Get_Err_StdOperations RETURN Char_Array
2734 IS
2735 l_api_name VARCHAR2(100):= ' Get_Err_StdOperations ';
2736 l_model_org_count NUMBER;
2737 l_p_count_mdl NUMBER;
2738 l_p_count_new NUMBER;
2739 l_c1_count_mdl NUMBER;
2740 l_c1_count_new NUMBER;
2741 l_c2_count_mdl NUMBER;
2742 l_c2_count_new NUMBER;
2743
2744 l_count_tmp NUMBER:=0;
2745 l_entity_names Char_Array := Char_Array();
2746 l_entity_names_fin Char_Array := Char_Array();
2747 l_entity_arr_idx NUMBER := 1;
2748 l_std_op_code BOM_STANDARD_OPERATIONS.OPERATION_CODE%TYPE;
2749 -------------------------------------------------------------------
2750 -------------------------------------------------------------------
2751 CURSOR l_parent ( p_model_org_id NUMBER
2752 , p_organization_id NUMBER
2753 )
2754 IS
2755 SELECT DISTINCT stdops.OPERATION_CODE
2756 FROM BOM_STANDARD_OPERATIONS stdops
2757 WHERE stdops.ORGANIZATION_ID = p_model_org_id
2758 AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
2759 AND NOT EXISTS
2760 ( SELECT 'x'
2761 FROM BOM_STANDARD_OPERATIONS stdops1
2762 WHERE stdops1.ORGANIZATION_ID = p_organization_id
2763 AND stdops1.OPERATION_CODE = stdops.OPERATION_CODE
2764 );
2765
2766 CURSOR l_parent_cnt ( p_org_id NUMBER )
2767 IS
2768 SELECT COUNT(stdops.STANDARD_OPERATION_ID)
2769 FROM BOM_STANDARD_OPERATIONS stdops
2770 WHERE stdops.ORGANIZATION_ID = p_org_id
2771 AND stdops.Line_Id is null and stdops.Operation_type = 1; --myerrams, testing
2772 -------------------------------------------------------------------
2773 -------------------------------------------------------------------
2774 CURSOR l_child_1 ( p_model_org_id NUMBER
2775 , p_organization_id NUMBER
2776 )
2777 IS
2778 SELECT stdops.OPERATION_CODE
2779 , COUNT(stdopres.RESOURCE_ID)
2780 FROM BOM_STD_OP_RESOURCES stdopres
2781 , BOM_STANDARD_OPERATIONS stdops
2782 , BOM_RESOURCES res
2783 WHERE stdopres.STANDARD_OPERATION_ID = stdops.STANDARD_OPERATION_ID
2784 AND stdops.ORGANIZATION_ID = p_model_org_id
2785 AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
2786 AND res.RESOURCE_ID = stdopres.RESOURCE_ID
2787
2788 AND NOT EXISTS
2789 ( SELECT 'x'
2790 FROM BOM_STD_OP_RESOURCES stdopres1
2791 , BOM_STANDARD_OPERATIONS stdops1
2792 , BOM_RESOURCES res1
2793 WHERE stdops1.ORGANIZATION_ID = p_organization_id
2794 AND stdopres1.STANDARD_OPERATION_ID = stdops1.STANDARD_OPERATION_ID
2795 AND stdopres1.RESOURCE_SEQ_NUM = stdopres.RESOURCE_SEQ_NUM
2796 AND res1.RESOURCE_ID = stdopres1.RESOURCE_ID
2797 AND res1.RESOURCE_CODE = res.RESOURCE_CODE
2798 AND stdops1.OPERATION_CODE = stdops.OPERATION_CODE --myerrams, testing
2799 )
2800 GROUP BY stdops.OPERATION_CODE;
2801
2802 CURSOR l_child_1_cnt ( p_org_id NUMBER )
2803 IS
2804 SELECT COUNT(stdopres.RESOURCE_ID)
2805 FROM BOM_STD_OP_RESOURCES stdopres
2806 , BOM_STANDARD_OPERATIONS stdops
2807 WHERE stdopres.STANDARD_OPERATION_ID = stdops.STANDARD_OPERATION_ID
2808 AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
2809 AND stdops.ORGANIZATION_ID = p_org_id;
2810 -------------------------------------------------------------------
2811 -------------------------------------------------------------------
2812 CURSOR l_child_2 ( p_model_org_id NUMBER
2813 , p_organization_id NUMBER
2814 )
2815 IS
2816 SELECT stdops.OPERATION_CODE
2817 , COUNT(stdsubopres.SUBSTITUTE_GROUP_NUM)
2818 FROM BOM_STD_SUB_OP_RESOURCES stdsubopres
2819 , BOM_STD_OP_RESOURCES stdopres
2820 , BOM_STANDARD_OPERATIONS stdops
2821 , BOM_RESOURCES res
2822 WHERE stdops.ORGANIZATION_ID = p_model_org_id
2823 AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
2824 AND stdopres.SUBSTITUTE_GROUP_NUM = stdsubopres.SUBSTITUTE_GROUP_NUM
2825 AND stdops.STANDARD_OPERATION_ID = stdopres.STANDARD_OPERATION_ID
2826 AND stdops.STANDARD_OPERATION_ID = stdsubopres.STANDARD_OPERATION_ID
2827 AND res.RESOURCE_ID = stdsubopres.RESOURCE_ID
2828 AND NOT EXISTS
2829 ( SELECT 'x'
2830 FROM BOM_STD_SUB_OP_RESOURCES stdsubopres1
2831 , BOM_STD_OP_RESOURCES stdopres1
2832 , BOM_STANDARD_OPERATIONS stdops1
2833 , BOM_RESOURCES res1
2834 WHERE stdops1.STANDARD_OPERATION_ID = stdsubopres1.STANDARD_OPERATION_ID
2835 AND stdops1.STANDARD_OPERATION_ID = stdopres1.STANDARD_OPERATION_ID
2836 AND stdops1.ORGANIZATION_ID = p_organization_id
2837 AND res1.RESOURCE_ID = stdsubopres1.RESOURCE_ID
2838 AND res1.RESOURCE_CODE = res.RESOURCE_CODE
2839 AND stdopres1.SUBSTITUTE_GROUP_NUM = stdsubopres1.SUBSTITUTE_GROUP_NUM
2840 AND stdsubopres.SUBSTITUTE_GROUP_NUM = stdsubopres1.SUBSTITUTE_GROUP_NUM
2841 AND stdsubopres.REPLACEMENT_GROUP_NUM = stdsubopres1.REPLACEMENT_GROUP_NUM
2842 )
2843 GROUP BY stdops.OPERATION_CODE;
2844
2845 CURSOR l_child_2_cnt ( p_org_id NUMBER )
2846 IS
2847 SELECT COUNT(stdsubopres.SUBSTITUTE_GROUP_NUM)
2848 FROM BOM_STD_SUB_OP_RESOURCES stdsubopres
2849 , BOM_STD_OP_RESOURCES stdopres
2850 , BOM_STANDARD_OPERATIONS stdops
2851 WHERE stdops.ORGANIZATION_ID = p_org_id
2852 AND stdops.Line_Id is null and stdops.Operation_type = 1 --myerrams, testing
2853 AND stdopres.SUBSTITUTE_GROUP_NUM = stdsubopres.SUBSTITUTE_GROUP_NUM
2854 AND stdops.STANDARD_OPERATION_ID = stdopres.STANDARD_OPERATION_ID
2855 AND stdops.STANDARD_OPERATION_ID = stdsubopres.STANDARD_OPERATION_ID;
2856 -------------------------------------------------------------------
2857 -------------------------------------------------------------------
2858 BEGIN
2859
2860 IF G_DEBUG = 'Y' THEN
2861 FND_MSG_PUB.ADD_EXC_MSG
2862 ( G_PKG_NAME
2863 , l_api_name
2864 , 'IN INVCORPB: '||l_api_name
2865 );
2866 END IF;
2867
2868 OPEN l_parent_cnt ( g_model_org_id );
2869 FETCH l_parent_cnt INTO l_p_count_mdl;
2870 CLOSE l_parent_cnt;
2871
2872 OPEN l_parent_cnt ( g_organization_id );
2873 FETCH l_parent_cnt INTO l_p_count_new;
2874 CLOSE l_parent_cnt;
2875
2876 IF l_p_count_new < l_p_count_mdl
2877 THEN
2878 OPEN l_parent ( g_model_org_id
2879 , g_organization_id
2880 );
2881 FETCH l_parent INTO l_std_op_code;
2882 LOOP
2883 l_entity_names.EXTEND;
2884 l_entity_names(l_entity_arr_idx) := l_std_op_code;
2885
2886 l_entity_arr_idx := l_entity_arr_idx + 1;
2887
2888 FETCH l_parent INTO l_std_op_code;
2889 EXIT WHEN l_parent%NOTFOUND;
2890 END LOOP;
2891 CLOSE l_parent;
2892
2893 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2894 l_entity_names := l_entity_names_fin;
2895 l_entity_arr_idx := l_entity_names.COUNT + 1;
2896 END IF;
2897 -------------------------------------------------------------------
2898 -------------------------------------------------------------------
2899 IF l_p_count_new = 0
2900 THEN
2901 l_entity_names.EXTEND;
2902 l_entity_names(l_entity_arr_idx):=l_p_count_mdl;
2903 RETURN l_entity_names;
2904 END IF;
2905 -------------------------------------------------------------------
2906 -------------------------------------------------------------------
2907 OPEN l_child_1_cnt ( g_model_org_id );
2908 FETCH l_child_1_cnt INTO l_c1_count_mdl;
2909 CLOSE l_child_1_cnt;
2910
2911 OPEN l_child_1_cnt ( g_organization_id );
2912 FETCH l_child_1_cnt INTO l_c1_count_new;
2913 CLOSE l_child_1_cnt;
2914
2915 IF l_c1_count_new < l_c1_count_mdl
2916 THEN
2917 OPEN l_child_1 ( g_model_org_id
2918 , g_organization_id
2919 );
2920 FETCH l_child_1 INTO l_std_op_code, l_count_tmp;
2921 LOOP
2922 l_entity_names.EXTEND;
2923 l_entity_names(l_entity_arr_idx) := l_std_op_code;
2924
2925 l_entity_arr_idx := l_entity_arr_idx + 1;
2926
2927 FETCH l_child_1 INTO l_std_op_code, l_count_tmp;
2928 EXIT WHEN l_child_1%NOTFOUND;
2929 END LOOP;
2930 CLOSE l_child_1;
2931
2932 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2933 l_entity_names := l_entity_names_fin;
2934 l_entity_arr_idx := l_entity_names.COUNT + 1;
2935 END IF;
2936 -------------------------------------------------------------------
2937 -------------------------------------------------------------------
2938 OPEN l_child_2_cnt ( g_model_org_id );
2939 FETCH l_child_2_cnt INTO l_c2_count_mdl;
2940 CLOSE l_child_2_cnt;
2941
2942 OPEN l_child_2_cnt ( g_organization_id );
2943 FETCH l_child_2_cnt INTO l_c2_count_new;
2944 CLOSE l_child_2_cnt;
2945
2946 IF l_c2_count_new < l_c2_count_mdl
2947 THEN
2948 OPEN l_child_2 ( g_model_org_id
2949 , g_organization_id
2950 );
2951 FETCH l_child_2 INTO l_std_op_code, l_count_tmp;
2952 LOOP
2953 l_entity_names.EXTEND;
2954 l_entity_names(l_entity_arr_idx) := l_std_op_code;
2955
2956 l_entity_arr_idx := l_entity_arr_idx + 1;
2957
2958 FETCH l_child_2 INTO l_std_op_code, l_count_tmp;
2959 EXIT WHEN l_child_2%NOTFOUND;
2960 END LOOP;
2961 CLOSE l_child_2;
2962
2963 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
2964 l_entity_names := l_entity_names_fin;
2965 l_entity_arr_idx := l_entity_names.COUNT + 1;
2966 END IF;
2967
2968 l_entity_names.EXTEND;
2969 l_entity_names(l_entity_arr_idx):=l_p_count_mdl;
2970
2971 RETURN l_entity_names;
2972 EXCEPTION
2973 WHEN OTHERS THEN
2974 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
2975 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
2976 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
2977 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
2978 FND_MSG_PUB.Add;
2979 RAISE;
2980
2981 END Get_Err_StdOperations;
2982
2983 --=============================================================================
2984 -- FUNCTION NAME : Get_Err_Routings
2985 -- PRE-CONDITIONS: None.
2986 -- DESCRIPTION : Validate routings created for new org against
2987 -- those in model org.
2988 -- PARAMETERS :
2989 -- EXCEPTIONS : None.
2990 --
2991 --=============================================================================
2992 FUNCTION Get_Err_Routings RETURN Char_Array
2993 IS
2994 l_api_name VARCHAR2(100):= ' Get_Err_Routings ';
2995 --l_count NUMBER:=0;
2996 l_model_org_count NUMBER;
2997 l_p_count_mdl NUMBER;
2998 l_p_count_new NUMBER;
2999 l_c1_count_mdl NUMBER;
3000 l_c1_count_new NUMBER;
3001 l_c2_count_mdl NUMBER;
3002 l_c2_count_new NUMBER;
3003 l_c3_count_mdl NUMBER;
3004 l_c3_count_new NUMBER;
3005 l_c4_count_mdl NUMBER;
3006 l_c4_count_new NUMBER;
3007 l_ins_rec_cnt NUMBER;
3008 l_count_tmp NUMBER:=0;
3009 l_entity_names Char_Array := Char_Array();
3010 l_entity_names_fin Char_Array := Char_Array();
3011 l_entity_arr_idx NUMBER := 1;
3012 l_item_name VARCHAR2(100);
3013 l_alternate VARCHAR2(100);
3014 --l_entity_type VARCHAR2(100):= ' Routings ';
3015
3016 /* vmutyala modified the cursor to add condition
3017 nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3018 to the inner query */
3019 CURSOR l_parent ( p_model_org_id NUMBER
3020 , p_organization_id NUMBER
3021 )
3022 IS
3023 SELECT DISTINCT msi.CONCATENATED_SEGMENTS, boru.ALTERNATE_ROUTING_DESIGNATOR
3024 FROM BOM_OPERATIONAL_ROUTINGS boru
3025 , MTL_SYSTEM_ITEMS_KFV msi
3026 WHERE msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3027 AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3028 AND boru.ORGANIZATION_ID = p_model_org_id
3029 AND NOT EXISTS
3030 ( SELECT 'x'
3031 FROM BOM_OPERATIONAL_ROUTINGS boru1
3032 WHERE boru1.ORGANIZATION_ID = p_organization_id
3033 AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3034 AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3035 );
3036
3037 CURSOR l_parent_cnt ( p_org_id NUMBER )
3038 IS
3039 SELECT COUNT(boru.ASSEMBLY_ITEM_ID)
3040 FROM BOM_OPERATIONAL_ROUTINGS boru
3041 WHERE boru.ORGANIZATION_ID = p_org_id;
3042
3043 /* vmutyala modified the l_child_1 cursor because bos1.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3044 would never be true for two different organizations
3045 CURSOR l_child_1 ( p_model_org_id NUMBER
3046 , p_organization_id NUMBER
3047 )
3048 IS
3049 SELECT MSI.CONCATENATED_SEGMENTS
3050 , BOR.ALTERNATE_ROUTING_DESIGNATOR
3051 , COUNT(OPERATION_SEQ_NUM)
3052 FROM BOM_OPERATION_SEQUENCES BOS
3053 , BOM_OPERATIONAL_ROUTINGS BOR
3054 , MTL_SYSTEM_ITEMS_KFV MSI
3055 WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
3056 AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
3057 AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
3058 AND BOR.ORGANIZATION_ID = p_model_org_id
3059 AND NOT EXISTS
3060 ( SELECT 'x'
3061 FROM BOM_OPERATION_SEQUENCES bos1
3062 , BOM_OPERATIONAL_ROUTINGS bor1
3063 WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
3064 AND bor1.ORGANIZATION_ID = p_organization_id
3065 AND bos1.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3066 )
3067 GROUP BY MSI.CONCATENATED_SEGMENTS
3068 , BOR.ALTERNATE_ROUTING_DESIGNATOR;
3069 */
3070
3071 CURSOR l_child_1 ( p_model_org_id NUMBER
3072 , p_organization_id NUMBER
3073 )
3074 IS
3075 SELECT MSI.CONCATENATED_SEGMENTS
3076 , BOR.ALTERNATE_ROUTING_DESIGNATOR
3077 , COUNT(OPERATION_SEQ_NUM)
3078 FROM BOM_OPERATION_SEQUENCES BOS
3079 , BOM_OPERATIONAL_ROUTINGS BOR
3080 , MTL_SYSTEM_ITEMS_KFV MSI
3081 WHERE MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
3082 AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
3083 AND BOS.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
3084 AND BOR.ORGANIZATION_ID = p_model_org_id
3085 AND NOT EXISTS
3086 ( SELECT 'x'
3087 FROM BOM_OPERATION_SEQUENCES bos1
3088 , BOM_OPERATIONAL_ROUTINGS bor1
3089 WHERE bos1.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
3090 AND bor1.ORGANIZATION_ID = p_organization_id
3091 AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
3092 AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
3093 AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
3094 AND bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
3095 AND nvl(bor1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3096 = nvl(bor.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3097 )
3098 GROUP BY MSI.CONCATENATED_SEGMENTS
3099 , BOR.ALTERNATE_ROUTING_DESIGNATOR;
3100
3101 CURSOR l_child_1_cnt ( p_org_id NUMBER )
3102 IS
3103 SELECT COUNT(bos.OPERATION_SEQUENCE_ID)
3104 FROM BOM_OPERATIONAL_ROUTINGS boru
3105 , BOM_OPERATION_SEQUENCES bos
3106 WHERE bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3107 AND boru.ORGANIZATION_ID = p_org_id;
3108
3109 /* vmutyala modified the l_child_2 cursor because bor1.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3110 would never be true for two different organizations and acd_type could be null
3111 CURSOR l_child_2 ( p_model_org_id NUMBER
3112 , p_organization_id NUMBER
3113 )
3114 IS
3115 SELECT msi.CONCATENATED_SEGMENTS
3116 , boru.ALTERNATE_ROUTING_DESIGNATOR
3117 , COUNT(bor.RESOURCE_ID)
3118 FROM BOM_OPERATION_RESOURCES bor
3119 , BOM_OPERATION_SEQUENCES bos
3120 , BOM_OPERATIONAL_ROUTINGS boru
3121 , MTL_SYSTEM_ITEMS_KFV msi
3122 WHERE boru.ORGANIZATION_ID = p_model_org_id
3123 AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3124 AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3125 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3126 AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3127 AND NOT EXISTS
3128 ( SELECT 'x'
3129 FROM BOM_OPERATIONAL_ROUTINGS boru1
3130 , BOM_OPERATION_SEQUENCES bos1
3131 , BOM_OPERATION_RESOURCES bor1
3132 WHERE boru1.ORGANIZATION_ID = p_organization_id
3133 AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3134 AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3135 AND bor1.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3136 AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
3137 AND bor1.ACD_TYPE = bor.ACD_TYPE
3138 )
3139 GROUP BY MSI.CONCATENATED_SEGMENTS
3140 , boru.ALTERNATE_ROUTING_DESIGNATOR;
3141 */
3142 CURSOR l_child_2 ( p_model_org_id NUMBER
3143 , p_organization_id NUMBER
3144 )
3145 IS
3146 SELECT msi.CONCATENATED_SEGMENTS
3147 , boru.ALTERNATE_ROUTING_DESIGNATOR
3148 , COUNT(bor.RESOURCE_ID)
3149 FROM BOM_OPERATION_RESOURCES bor
3150 , BOM_OPERATION_SEQUENCES bos
3151 , BOM_OPERATIONAL_ROUTINGS boru
3152 , MTL_SYSTEM_ITEMS_KFV msi
3153 WHERE boru.ORGANIZATION_ID = p_model_org_id
3154 AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3155 AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3156 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3157 AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3158 AND NOT EXISTS
3159 ( SELECT 'x'
3160 FROM BOM_OPERATIONAL_ROUTINGS boru1
3161 , BOM_OPERATION_SEQUENCES bos1
3162 , BOM_OPERATION_RESOURCES bor1
3163 WHERE boru1.ORGANIZATION_ID = p_organization_id
3164 AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3165 AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3166 AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
3167 AND nvl(bor1.ACD_TYPE, 1) = nvl(bor.ACD_TYPE, 1)
3168 AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
3169 AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
3170 AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
3171 AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3172 AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3173 )
3174 GROUP BY MSI.CONCATENATED_SEGMENTS
3175 , boru.ALTERNATE_ROUTING_DESIGNATOR;
3176 CURSOR l_child_2_cnt ( p_org_id NUMBER )
3177 IS
3178 SELECT COUNT(bor.RESOURCE_SEQ_NUM)
3179 FROM BOM_OPERATIONAL_ROUTINGS boru
3180 , BOM_OPERATION_SEQUENCES bos
3181 , BOM_OPERATION_RESOURCES bor
3182 WHERE bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3183 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3184 AND boru.ORGANIZATION_ID = p_org_id;
3185
3186 /* vmutyala modified l_child_3 cursor because bsor1.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
3187 and bsor1.RESOURCE_ID = bsor.RESOURCE_ID will never be true for two different organizations
3188 CURSOR l_child_3 ( p_model_org_id NUMBER
3189 , p_organization_id NUMBER
3190 )
3191 IS
3192 SELECT msi.CONCATENATED_SEGMENTS
3193 , boru.ALTERNATE_ROUTING_DESIGNATOR
3194 , COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3195 FROM BOM_SUB_OPERATION_RESOURCES bsor
3196 , BOM_OPERATION_RESOURCES bor
3197 , BOM_OPERATION_SEQUENCES bos
3198 , BOM_OPERATIONAL_ROUTINGS boru
3199 , MTL_SYSTEM_ITEMS_KFV msi
3200 WHERE boru.ORGANIZATION_ID = p_model_org_id
3201 AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3202 AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3203 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3204 AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3205 AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3206 AND bsor.RESOURCE_ID = bor.RESOURCE_ID
3207 AND NOT EXISTS
3208 ( SELECT 'x'
3209 FROM BOM_SUB_OPERATION_RESOURCES bsor1
3210 , BOM_OPERATION_RESOURCES bor1
3211 , BOM_OPERATION_SEQUENCES bos1
3212 , BOM_OPERATIONAL_ROUTINGS boru1
3213 WHERE boru1.ORGANIZATION_ID = p_organization_id
3214 AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3215 AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3216 AND bsor1.OPERATION_SEQUENCE_ID = bor1.OPERATION_SEQUENCE_ID
3217 AND bsor1.RESOURCE_ID = bor1.RESOURCE_ID
3218 AND bsor1.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
3219 AND bsor1.RESOURCE_ID = bsor.RESOURCE_ID
3220 AND bsor1.SUBSTITUTE_GROUP_NUM = bsor.SUBSTITUTE_GROUP_NUM
3221 AND bsor1.REPLACEMENT_GROUP_NUM = bsor.REPLACEMENT_GROUP_NUM
3222 )
3223 GROUP BY MSI.CONCATENATED_SEGMENTS
3224 , boru.ALTERNATE_ROUTING_DESIGNATOR;
3225 */
3226
3227 CURSOR l_child_3 ( p_model_org_id NUMBER
3228 , p_organization_id NUMBER
3229 )
3230 IS
3231 SELECT msi.CONCATENATED_SEGMENTS
3232 , boru.ALTERNATE_ROUTING_DESIGNATOR
3233 , COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3234 FROM BOM_SUB_OPERATION_RESOURCES bsor
3235 , BOM_OPERATION_RESOURCES bor
3236 , BOM_OPERATION_SEQUENCES bos
3237 , BOM_OPERATIONAL_ROUTINGS boru
3238 , MTL_SYSTEM_ITEMS_KFV msi
3239 WHERE boru.ORGANIZATION_ID = p_model_org_id
3240 AND msi.ORGANIZATION_ID = boru.ORGANIZATION_ID
3241 AND msi.INVENTORY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3242 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3243 AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3244 AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3245 AND bsor.RESOURCE_ID = bor.RESOURCE_ID
3246 AND NOT EXISTS
3247 ( SELECT 'x'
3248 FROM BOM_SUB_OPERATION_RESOURCES bsor1
3249 , BOM_OPERATION_RESOURCES bor1
3250 , BOM_OPERATION_SEQUENCES bos1
3251 , BOM_OPERATIONAL_ROUTINGS boru1
3252 WHERE boru1.ORGANIZATION_ID = p_organization_id
3253 AND bos1.ROUTING_SEQUENCE_ID = boru1.ROUTING_SEQUENCE_ID
3254 AND bor1.OPERATION_SEQUENCE_ID = bos1.OPERATION_SEQUENCE_ID
3255 AND bsor1.OPERATION_SEQUENCE_ID = bor1.OPERATION_SEQUENCE_ID
3256 AND bsor1.RESOURCE_ID = bor1.RESOURCE_ID
3257 -- AND bsor1.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
3258 -- AND bsor1.RESOURCE_ID = bsor.RESOURCE_ID
3259 AND bor1.RESOURCE_SEQ_NUM = bor.RESOURCE_SEQ_NUM
3260 AND nvl(bor1.ACD_TYPE, 1) = nvl(bor.ACD_TYPE, 1)
3261 AND bos1.OPERATION_TYPE = bos.OPERATION_TYPE
3262 AND bos1.OPERATION_SEQ_NUM = bos.OPERATION_SEQ_NUM
3263 AND bos1.EFFECTIVITY_DATE = bos.EFFECTIVITY_DATE
3264 AND boru1.ASSEMBLY_ITEM_ID = boru.ASSEMBLY_ITEM_ID
3265 AND nvl(boru1.ALTERNATE_ROUTING_DESIGNATOR, 'NULL') = nvl(boru.ALTERNATE_ROUTING_DESIGNATOR, 'NULL')
3266 AND bsor1.SUBSTITUTE_GROUP_NUM = bsor.SUBSTITUTE_GROUP_NUM
3267 AND bsor1.REPLACEMENT_GROUP_NUM = bsor.REPLACEMENT_GROUP_NUM
3268 )
3269 GROUP BY MSI.CONCATENATED_SEGMENTS
3270 , boru.ALTERNATE_ROUTING_DESIGNATOR;
3271
3272 CURSOR l_child_3_cnt ( p_org_id NUMBER )
3273 IS
3274 SELECT COUNT(bsor.SUBSTITUTE_GROUP_NUM)
3275 FROM BOM_OPERATIONAL_ROUTINGS boru
3276 , BOM_OPERATION_SEQUENCES bos
3277 , BOM_OPERATION_RESOURCES bor
3278 , BOM_SUB_OPERATION_RESOURCES bsor
3279 WHERE bsor.RESOURCE_ID = bor.RESOURCE_ID
3280 AND bsor.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
3281 AND bor.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
3282 AND bos.ROUTING_SEQUENCE_ID = boru.ROUTING_SEQUENCE_ID
3283 AND boru.ORGANIZATION_ID = p_org_id;
3284
3285 CURSOR l_child_4 ( p_model_org_id NUMBER
3286 , p_organization_id NUMBER
3287 )
3288 IS
3289 SELECT kfv.CONCATENATED_SEGMENTS
3290 FROM MTL_RTG_ITEM_REVISIONS rev
3291 , MTL_SYSTEM_ITEMS_KFV kfv
3292 WHERE kfv.ORGANIZATION_ID = rev.ORGANIZATION_ID
3293 AND kfv.INVENTORY_ITEM_ID = rev.INVENTORY_ITEM_ID
3294 AND rev.ORGANIZATION_ID = p_model_org_id
3295 AND NOT EXISTS
3296 ( SELECT 'x'
3297 FROM MTL_RTG_ITEM_REVISIONS rev1
3298 WHERE rev1.ORGANIZATION_ID = p_organization_id
3299 AND rev1.INVENTORY_ITEM_ID = rev.INVENTORY_ITEM_ID
3300 AND rev1.PROCESS_REVISION = rev.PROCESS_REVISION
3301 );
3302
3303 CURSOR l_child_4_cnt ( p_org_id NUMBER )
3304 IS
3305 SELECT COUNT(rev.PROCESS_REVISION)
3306 FROM MTL_RTG_ITEM_REVISIONS rev
3307 WHERE rev.ORGANIZATION_ID = p_org_id;
3308
3309 BEGIN
3310
3311 IF G_DEBUG = 'Y' THEN
3312 FND_MSG_PUB.ADD_EXC_MSG
3313 ( G_PKG_NAME
3314 , l_api_name
3315 , 'IN INVCORPB: '||l_api_name
3316 );
3317 END IF;
3318
3319 OPEN l_parent_cnt ( g_model_org_id );
3320 FETCH l_parent_cnt INTO l_p_count_mdl;
3321 CLOSE l_parent_cnt;
3322
3323 OPEN l_parent_cnt ( g_organization_id );
3324 FETCH l_parent_cnt INTO l_p_count_new;
3325 CLOSE l_parent_cnt;
3326
3327 IF l_p_count_new < l_p_count_mdl
3328 THEN
3329 OPEN l_parent ( g_model_org_id
3330 , g_organization_id
3331 );
3332 FETCH l_parent INTO l_item_name, l_alternate;
3333 LOOP
3334 l_entity_names.EXTEND;
3335 IF l_alternate IS NOT NULL
3336 AND l_alternate <> ''
3337 THEN
3338 l_entity_names(l_entity_arr_idx) := l_item_name||', '||l_alternate;
3339 ELSE
3340 l_entity_names(l_entity_arr_idx) := l_item_name;
3341 END IF;
3342 l_entity_arr_idx := l_entity_arr_idx + 1;
3343
3344 FETCH l_parent INTO l_item_name, l_alternate;
3345 EXIT WHEN l_parent%NOTFOUND;
3346 END LOOP;
3347 CLOSE l_parent;
3348
3349 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
3350 l_entity_names := l_entity_names_fin;
3351 l_entity_arr_idx := l_entity_names.COUNT + 1;
3352 END IF;
3353
3354 IF l_p_count_new = 0
3355 THEN
3356 l_entity_names.EXTEND;
3357 l_entity_names(l_entity_arr_idx):=l_p_count_mdl;
3358 RETURN l_entity_names;
3359 END IF;
3360
3361 OPEN l_child_1_cnt ( g_model_org_id );
3362 FETCH l_child_1_cnt INTO l_c1_count_mdl;
3363 CLOSE l_child_1_cnt;
3364
3365 OPEN l_child_1_cnt ( g_organization_id );
3366 FETCH l_child_1_cnt INTO l_c1_count_new;
3367 CLOSE l_child_1_cnt;
3368
3369 IF l_c1_count_new < l_c1_count_mdl
3370 THEN
3371 OPEN l_child_1 ( g_model_org_id
3372 , g_organization_id
3373 );
3374 FETCH l_child_1 INTO l_item_name, l_alternate, l_count_tmp;
3375 LOOP
3376 l_entity_names.EXTEND;
3377 IF l_alternate IS NOT NULL
3378 AND l_alternate <> ''
3379 THEN
3380 l_entity_names(l_entity_arr_idx) := l_item_name||', '||l_alternate;
3381 ELSE
3382 l_entity_names(l_entity_arr_idx) := l_item_name;
3383 END IF;
3384 l_entity_arr_idx := l_entity_arr_idx + 1;
3385
3386 FETCH l_child_1 INTO l_item_name, l_alternate, l_count_tmp;
3387 EXIT WHEN l_child_1%NOTFOUND;
3388 END LOOP;
3389 CLOSE l_child_1;
3390
3391 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
3392 l_entity_names := l_entity_names_fin;
3393 l_entity_arr_idx := l_entity_names.COUNT + 1;
3394 END IF;
3395
3396 OPEN l_child_2_cnt ( g_model_org_id );
3397 FETCH l_child_2_cnt INTO l_c2_count_mdl;
3398 CLOSE l_child_2_cnt;
3399
3400 OPEN l_child_2_cnt ( g_organization_id );
3401 FETCH l_child_2_cnt INTO l_c2_count_new;
3402 CLOSE l_child_2_cnt;
3403
3404 IF l_c2_count_new < l_c2_count_mdl
3405 THEN
3406 OPEN l_child_2 ( g_model_org_id
3407 , g_organization_id
3408 );
3409 FETCH l_child_2 INTO l_item_name, l_alternate, l_count_tmp;
3410 LOOP
3411 l_entity_names.EXTEND;
3412 IF l_alternate IS NOT NULL
3413 AND l_alternate <> ''
3414 THEN
3415 l_entity_names(l_entity_arr_idx) := l_item_name||', '||l_alternate;
3416 ELSE
3417 l_entity_names(l_entity_arr_idx) := l_item_name;
3418 END IF;
3419 l_entity_arr_idx := l_entity_arr_idx + 1;
3420
3421 FETCH l_child_2 INTO l_item_name, l_alternate, l_count_tmp;
3422 EXIT WHEN l_child_2%NOTFOUND;
3423 END LOOP;
3424 CLOSE l_child_2;
3425
3426 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
3427 l_entity_names := l_entity_names_fin;
3428 l_entity_arr_idx := l_entity_names.COUNT + 1;
3429 END IF;
3430
3431 OPEN l_child_3_cnt ( g_model_org_id );
3432 FETCH l_child_3_cnt INTO l_c3_count_mdl;
3433 CLOSE l_child_3_cnt;
3434
3435 OPEN l_child_3_cnt ( g_organization_id );
3436 FETCH l_child_3_cnt INTO l_c3_count_new;
3437 CLOSE l_child_3_cnt;
3438
3439 IF l_c3_count_new < l_c3_count_mdl
3440 THEN
3441 OPEN l_child_3 ( g_model_org_id
3442 , g_organization_id
3443 );
3444 FETCH l_child_3 INTO l_item_name, l_alternate, l_count_tmp;
3445 LOOP
3446 l_entity_names.EXTEND;
3447 IF l_alternate IS NOT NULL
3448 AND l_alternate <> ''
3449 THEN
3450 l_entity_names(l_entity_arr_idx) := l_item_name||', '||l_alternate;
3451 ELSE
3452 l_entity_names(l_entity_arr_idx) := l_item_name;
3453 END IF;
3454 l_entity_arr_idx := l_entity_arr_idx + 1;
3455
3456 FETCH l_child_3 INTO l_item_name, l_alternate, l_count_tmp;
3457 EXIT WHEN l_child_3%NOTFOUND;
3458 END LOOP;
3459 CLOSE l_child_3;
3460
3461 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
3462 l_entity_names := l_entity_names_fin;
3463 l_entity_arr_idx := l_entity_names.COUNT + 1;
3464 END IF;
3465
3466 OPEN l_child_4_cnt ( g_model_org_id );
3467 FETCH l_child_4_cnt INTO l_c4_count_mdl;
3468 CLOSE l_child_4_cnt;
3469
3470 OPEN l_child_4_cnt ( g_organization_id );
3471 FETCH l_child_4_cnt INTO l_c4_count_new;
3472 CLOSE l_child_4_cnt;
3473
3474 IF l_c4_count_new < l_c4_count_mdl
3475 THEN
3476 OPEN l_child_4 ( g_model_org_id
3477 , g_organization_id
3478 );
3479 FETCH l_child_4 INTO l_item_name;
3480 LOOP
3481 l_entity_names.EXTEND;
3482 l_entity_names(l_entity_arr_idx) := l_item_name;
3483 l_entity_arr_idx := l_entity_arr_idx + 1;
3484
3485 FETCH l_child_4 INTO l_item_name;
3486 EXIT WHEN l_child_4%NOTFOUND;
3487 END LOOP;
3488 CLOSE l_child_4;
3489
3490 l_entity_names_fin := Trim_Array(Get_Unique_List(l_entity_names));
3491 l_entity_names := l_entity_names_fin;
3492 l_entity_arr_idx := l_entity_names.COUNT + 1;
3493 END IF;
3494
3495 l_entity_names.EXTEND;
3496 l_entity_names(l_entity_arr_idx):=l_p_count_mdl;
3497
3498 RETURN l_entity_names;
3499 EXCEPTION
3500 WHEN OTHERS THEN
3501 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
3502 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
3503 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
3504 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
3505 FND_MSG_PUB.Add;
3506 RAISE;
3507
3508 END Get_Err_Routings;
3509
3510 --=============================================================================
3511 -- FUNCTION NAME : Get_Err_Bom_Dept_Res
3512 -- PRE-CONDITIONS: None.
3513 -- DESCRIPTION : Validate department resources created for new org against
3514 -- those in model org.
3515 -- PARAMETERS : None.
3516 -- EXCEPTIONS : None.
3517 --
3518 --=============================================================================
3519 FUNCTION Get_Err_Bom_Dept_Res RETURN Char_Array
3520 IS
3521 l_api_name VARCHAR2(100):= ' Get_Err_Bom_Dept_Res ';
3522 --l_count NUMBER:=0;
3523 l_model_org_count NUMBER:=0;
3524 l_new_org_count NUMBER:=0;
3525 l_entity_names Char_Array := Char_Array();
3526 l_entity_arr_idx NUMBER:=1;
3527 l_res_code VARCHAR2(100);
3528 l_dept_code VARCHAR2(100);
3529 --l_entity_type VARCHAR2(100):= ' Department-Resources ';
3530
3531 CURSOR l_cursor ( p_model_org_id NUMBER
3532 , p_organization_id NUMBER
3533 )
3534 IS
3535 SELECT bd.DEPARTMENT_CODE, br.RESOURCE_CODE
3536 FROM BOM_DEPARTMENT_RESOURCES ent
3537 , BOM_DEPARTMENTS bd
3538 , BOM_RESOURCES br
3539 WHERE br.RESOURCE_ID = ent.RESOURCE_ID
3540 AND ent.RESOURCE_ID IN ( SELECT br.RESOURCE_ID
3541 FROM BOM_RESOURCES br
3542 WHERE br.ORGANIZATION_ID=p_model_org_id
3543 )
3544 AND bd.DEPARTMENT_ID = ent.DEPARTMENT_ID
3545 AND ent.DEPARTMENT_ID IN ( SELECT bd.DEPARTMENT_ID
3546 FROM BOM_DEPARTMENTS bd
3547 WHERE bd.ORGANIZATION_ID=p_model_org_id
3548 )
3549 MINUS
3550 SELECT bd.DEPARTMENT_CODE, br.RESOURCE_CODE
3551 FROM BOM_DEPARTMENT_RESOURCES ent
3552 , BOM_DEPARTMENTS bd
3553 , BOM_RESOURCES br
3554 WHERE br.RESOURCE_ID = ent.RESOURCE_ID
3555 AND ent.RESOURCE_ID IN ( SELECT br.RESOURCE_ID
3556 FROM BOM_RESOURCES br
3557 WHERE br.ORGANIZATION_ID=p_organization_id
3558 )
3559 AND bd.DEPARTMENT_ID = ent.DEPARTMENT_ID
3560 AND ent.DEPARTMENT_ID IN ( SELECT bd.DEPARTMENT_ID
3561 FROM BOM_DEPARTMENTS bd
3562 WHERE bd.ORGANIZATION_ID=p_organization_id
3563 );
3564
3565 CURSOR l_cursor_cnt ( p_org_id NUMBER )
3566 IS
3567 SELECT COUNT(ent.RESOURCE_ID)
3568 FROM BOM_DEPARTMENT_RESOURCES ent
3569 WHERE ent.RESOURCE_ID IN
3570 (
3571 SELECT br.RESOURCE_ID
3572 FROM BOM_RESOURCES br
3573 WHERE br.ORGANIZATION_ID=p_org_id
3574 )
3575 AND ent.DEPARTMENT_ID IN
3576 (
3577 SELECT bd.DEPARTMENT_ID
3578 FROM BOM_DEPARTMENTS bd
3579 WHERE bd.ORGANIZATION_ID=p_org_id
3580 );
3581
3582 BEGIN
3583
3584 IF G_DEBUG = 'Y' THEN
3585 FND_MSG_PUB.ADD_EXC_MSG
3586 ( G_PKG_NAME
3587 , l_api_name
3588 , 'IN INVCORPB: '||l_api_name
3589 );
3590 END IF;
3591
3592 OPEN l_cursor_cnt ( g_model_org_id );
3593 FETCH l_cursor_cnt INTO l_model_org_count;
3594 CLOSE l_cursor_cnt;
3595
3596 OPEN l_cursor_cnt ( g_organization_id );
3597 FETCH l_cursor_cnt INTO l_new_org_count;
3598 CLOSE l_cursor_cnt;
3599
3600 IF l_new_org_count < l_model_org_count
3601 THEN
3602 OPEN l_cursor ( g_model_org_id
3603 , g_organization_id
3604 );
3605 FETCH l_cursor INTO l_dept_code, l_res_code;
3606 LOOP
3607 l_entity_names.EXTEND;
3608 l_entity_names(l_entity_arr_idx) := l_dept_code ||' : '||l_res_code;
3609 l_entity_arr_idx := l_entity_arr_idx + 1;
3610
3611 FETCH l_cursor INTO l_dept_code, l_res_code;
3612 EXIT WHEN l_cursor%NOTFOUND;
3613 END LOOP;
3614 CLOSE l_cursor;
3615 END IF;
3616
3617 l_entity_names.EXTEND;
3618 l_entity_names(l_entity_arr_idx):=l_model_org_count;
3619
3620 RETURN l_entity_names;
3621 EXCEPTION
3622 WHEN OTHERS THEN
3623 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
3624 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
3625 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
3626 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
3627 FND_MSG_PUB.Add;
3628 RAISE;
3629
3630 END Get_Err_Bom_Dept_Res;
3631
3632 --=============================================================================
3633 -- FUNCTION NAME : Get_Err_Cst_Res_Costs
3634 -- PRE-CONDITIONS: None.
3635 -- DESCRIPTION : Validate resource costs created for new org against
3636 -- those in model org.
3637 -- PARAMETERS : None.
3638 -- EXCEPTIONS : None.
3639 --
3640 --=============================================================================
3641 FUNCTION Get_Err_Cst_Res_Costs RETURN Char_Array
3642 IS
3643 l_api_name VARCHAR2(100):=' Get_Err_Cst_Res_Costs ';
3644 l_cost_type VARCHAR2(100);
3645 l_entity_arr_idx NUMBER:=1;
3646 l_entity_names Char_Array:= Char_Array();
3647 --l_entity_type VARCHAR2(100):=' Resource Costs ';
3648 l_model_org_count NUMBER:=0;
3649 l_new_org_count NUMBER:=0;
3650 l_res_code VARCHAR2(100);
3651
3652 CURSOR l_cursor ( p_model_org_id NUMBER
3653 , p_organization_id NUMBER
3654 )
3655 IS
3656 SELECT br1.RESOURCE_CODE, cct1.COST_TYPE
3657 FROM CST_RESOURCE_COSTS crc1
3658 , CST_COST_TYPES cct1
3659 , BOM_RESOURCES br1
3660 WHERE crc1.ORGANIZATION_ID = p_model_org_id
3661 AND br1.RESOURCE_ID = crc1.RESOURCE_ID
3662 AND cct1.COST_TYPE_ID = crc1.COST_TYPE_ID
3663 AND NOT EXISTS ( SELECT 'x'
3664 FROM CST_RESOURCE_COSTS crc2
3665 , CST_COST_TYPES cct2
3666 , BOM_RESOURCES br2
3667 WHERE crc2.ORGANIZATION_ID = p_organization_id
3668 AND br2.RESOURCE_ID = crc2.RESOURCE_ID
3669 AND br2.RESOURCE_CODE = br1.RESOURCE_CODE
3670 AND cct2.COST_TYPE_ID = crc2.COST_TYPE_ID
3671 AND cct2.COST_TYPE = cct1.COST_TYPE
3672 );
3673 CURSOR l_cnt_csr ( p_org_id NUMBER )
3674 IS
3675 SELECT COUNT(crc.RESOURCE_ID)
3676 FROM CST_RESOURCE_COSTS crc
3677 WHERE crc.ORGANIZATION_ID = p_org_id;
3678
3679 BEGIN
3680
3681 IF G_DEBUG = 'Y' THEN
3682 FND_MSG_PUB.ADD_EXC_MSG
3683 ( G_PKG_NAME
3684 , l_api_name
3685 , 'IN INVCORPB: '||l_api_name
3686 );
3687 END IF;
3688
3689 OPEN l_cnt_csr ( g_model_org_id );
3690 FETCH l_cnt_csr INTO l_model_org_count;
3691 CLOSE l_cnt_csr;
3692
3693 OPEN l_cnt_csr ( g_organization_id );
3694 FETCH l_cnt_csr INTO l_new_org_count;
3695 CLOSE l_cnt_csr;
3696
3697 IF l_new_org_count < l_model_org_count
3698 THEN
3699 OPEN l_cursor ( g_model_org_id
3700 , g_organization_id
3701 );
3702 FETCH l_cursor INTO l_res_code, l_cost_type;
3703 LOOP
3704 l_entity_names.EXTEND;
3705 l_entity_names(l_entity_arr_idx) := l_res_code ||' : '||l_cost_type;
3706 l_entity_arr_idx := l_entity_arr_idx + 1;
3707
3708 FETCH l_cursor INTO l_res_code, l_cost_type;
3709 EXIT WHEN l_cursor%NOTFOUND;
3710 END LOOP;
3711 CLOSE l_cursor;
3712 END IF;
3713
3714 l_entity_names.EXTEND;
3715 l_entity_names(l_entity_arr_idx):=l_model_org_count;
3716
3717 RETURN l_entity_names;
3718 EXCEPTION
3719 WHEN OTHERS THEN
3720 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
3721 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
3722 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
3723 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
3724 FND_MSG_PUB.Add;
3725 RAISE;
3726
3727 END Get_Err_Cst_Res_Costs;
3728 --=============================================================================
3729 -- FUNCTION NAME : Get_Err_Cst_Res_Ovhds
3730 -- PRE-CONDITIONS: None.
3731 -- DESCRIPTION : Validate resource overheads created for new org against
3732 -- those in model org.
3733 -- PARAMETERS : None.
3734 -- EXCEPTIONS : None.
3735 --
3736 --=============================================================================
3737 FUNCTION Get_Err_Cst_Res_Ovhds RETURN Char_Array
3738 IS
3739 l_api_name VARCHAR2(100):= ' Get_Err_Cst_Res_Ovhds ';
3740 l_cost_type VARCHAR2(100);
3741 l_entity_arr_idx NUMBER := 1;
3742 l_entity_names Char_Array := Char_Array();
3743 --l_entity_type VARCHAR2(100):= ' Resource Overheads ';
3744 l_model_org_count NUMBER;
3745 l_new_org_count NUMBER;
3746 l_res_code VARCHAR2(100);
3747 l_res_ovhd VARCHAR2(100);
3748
3749 CURSOR l_cursor ( p_model_org_id NUMBER
3750 , p_organization_id NUMBER
3751 )
3752 IS
3753 SELECT br1.RESOURCE_CODE
3754 , cct1.COST_TYPE
3755 , br2.RESOURCE_CODE
3756 FROM CST_RESOURCE_OVERHEADS crh1
3757 , CST_COST_TYPES cct1
3758 , BOM_RESOURCES br1
3759 , BOM_RESOURCES br2
3760 WHERE crh1.ORGANIZATION_ID = p_model_org_id
3761 AND br1.RESOURCE_ID = crh1.RESOURCE_ID
3762 AND br2.RESOURCE_ID = crh1.OVERHEAD_ID
3763 AND cct1.COST_TYPE_ID = crh1.COST_TYPE_ID
3764 AND NOT EXISTS ( SELECT 'x'
3765 FROM CST_RESOURCE_OVERHEADS crh2
3766 , CST_COST_TYPES cct2
3767 , BOM_RESOURCES br3
3768 WHERE crh2.ORGANIZATION_ID = p_organization_id
3769 AND br3.RESOURCE_ID = crh2.RESOURCE_ID
3770 AND br3.RESOURCE_CODE = br1.RESOURCE_CODE
3771 AND cct2.COST_TYPE_ID = crh2.COST_TYPE_ID
3772 AND cct2.COST_TYPE = cct1.COST_TYPE
3773 );
3774
3775 CURSOR l_cnt_csr ( p_org_id NUMBER )
3776 IS
3777 SELECT COUNT(crh.RESOURCE_ID)
3778 FROM CST_RESOURCE_OVERHEADS crh
3779 WHERE crh.ORGANIZATION_ID = p_org_id;
3780
3781 BEGIN
3782
3783 IF G_DEBUG = 'Y' THEN
3784 FND_MSG_PUB.ADD_EXC_MSG
3785 ( G_PKG_NAME
3786 , l_api_name
3787 , '> Get_Err_Cst_Res_Ovhds'
3788 );
3789 END IF;
3790
3791 OPEN l_cnt_csr ( g_model_org_id );
3792 FETCH l_cnt_csr INTO l_model_org_count;
3793 CLOSE l_cnt_csr;
3794
3795 OPEN l_cnt_csr ( g_organization_id );
3796 FETCH l_cnt_csr INTO l_new_org_count;
3797 CLOSE l_cnt_csr;
3798
3799 IF l_new_org_count < l_model_org_count
3800 THEN
3801 OPEN l_cursor ( g_model_org_id
3802 , g_organization_id
3803 );
3804 FETCH l_cursor INTO l_res_code, l_cost_type, l_res_ovhd;
3805 LOOP
3806 l_entity_names.EXTEND;
3807 l_entity_names(l_entity_arr_idx) := l_res_code ||' : '||
3808 l_cost_type||' , '||l_res_ovhd;
3809 l_entity_arr_idx := l_entity_arr_idx + 1;
3810
3811 FETCH l_cursor INTO l_res_code, l_cost_type, l_res_ovhd;
3812 EXIT WHEN l_cursor%NOTFOUND;
3813 END LOOP;
3814 CLOSE l_cursor;
3815 END IF;
3816
3817 l_entity_names.EXTEND;
3818 l_entity_names(l_entity_arr_idx):=l_model_org_count;
3819
3820 IF G_DEBUG = 'Y' THEN
3821 FND_MSG_PUB.ADD_EXC_MSG
3822 ( G_PKG_NAME
3823 , l_api_name
3824 , '< Get_Err_Cst_Res_Ovhds'
3825 );
3826 END IF;
3827
3828 RETURN l_entity_names;
3829
3830 EXCEPTION
3831 WHEN OTHERS THEN
3832 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
3833 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
3834 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
3835 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
3836 FND_MSG_PUB.Add;
3837 RAISE;
3838
3839 END Get_Err_Cst_Res_Ovhds;
3840
3841 --=============================================================================
3842 -- PROC NAME : Put_Report_Data
3843 -- PRE-CONDITIONS: None.
3844 -- DESCRIPTION : Insert records for Qualitative / Quantitative data
3845 -- for all entities.
3846 -- PARAMETERS : None
3847 --
3848 -- EXCEPTIONS : None.
3849 --
3850 --=============================================================================
3851 PROCEDURE Put_Report_Data
3852 IS
3853 l_model_rec_cnt NUMBER:=0;
3854 l_new_rec_cnt NUMBER:=0;
3855 l_not_to_copy BOOLEAN:=false;
3856 l_mesg_name VARCHAR2(100);
3857 l_entity_err VARCHAR2(100);
3858 -- Modified for Bug 3838706
3859 -- l_message VARCHAR2(240):='';
3860 l_message VARCHAR2(2000):='';
3861 l_entity_count NUMBER:=0;
3862 l_api_name VARCHAR2(100):=' Put_Report_Data ';
3863
3864 BEGIN
3865
3866 BEGIN
3867 l_model_rec_cnt := g_entity_names(g_entity_names.COUNT);
3868 l_new_rec_cnt := l_model_rec_cnt - (g_entity_names.COUNT - 1);
3869 EXCEPTION
3870 WHEN OTHERS THEN
3871 l_model_rec_cnt := 0;
3872 l_new_rec_cnt := 0;
3873 l_not_to_copy := true;
3874 END;
3875
3876 IF NOT l_not_to_copy
3877 THEN
3878
3879 IF l_new_rec_cnt > l_model_rec_cnt
3880 THEN
3881 l_new_rec_cnt := l_model_rec_cnt;
3882 END IF;
3883
3884 --------------------------------------------------------------------
3885 -- Get copied / modified counts for entity .
3886 -- Modified records column has been removed from the report
3887 -- as decided in the telecon on 30/10/2003
3888 -- No check for modified flag is required anymor.
3889 --------------------------------------------------------------------
3890 --------------------------------------------------------------------
3891 --IF g_modified
3892 --THEN
3893 -- g_exp_modify_cnt := g_exp_modify_cnt + l_model_rec_cnt;
3894 -- g_modify_cnt := g_modify_cnt + l_new_rec_cnt;
3895 -- l_mesg_name := 'NOT_MODIFIED_ERROR';
3896 -- l_entity_err := 'NOT COPIED AND NOT MODIFIED';
3897 --ELSE
3898 --------------------------------------------------------------------
3899 g_exp_copy_cnt := g_exp_copy_cnt + l_model_rec_cnt;
3900 g_copy_cnt := g_copy_cnt + l_new_rec_cnt;
3901 l_mesg_name := 'NOT_COPIED_ERROR';
3902 l_entity_err := 'NOT COPIED';
3903 --------------------------------------------------------------------
3904 --END IF;
3905 --------------------------------------------------------------------
3906 IF G_DEBUG = 'Y' THEN
3907 --------------------------------------------------------------------
3908 -- Commenting dbms_output statements.
3909 --------------------------------------------------------------------
3910 -- dbms_output.put_line
3911 -- ( g_entity_type ||
3912 -- '- Copied Records(Exp/Actual) :'||g_exp_copy_cnt
3913 -- ||'/'
3914 -- ||g_copy_cnt
3915 --------------------------------------------------------------------
3916 -- Removed support for Modified records.
3917 --------------------------------------------------------------------
3918 -- ||
3919 -- '- Modified Records(Exp/Actual):'||g_exp_modify_cnt
3920 -- ||'/'
3921 -- ||g_modify_cnt
3922 --------------------------------------------------------------------
3923 -- );
3924 FND_MSG_PUB.ADD_EXC_MSG
3925 ( G_PKG_NAME
3926 , l_api_name
3927 , '- Copied Records(Exp/Actual) :'||g_exp_copy_cnt
3928 ||'/'
3929 ||g_copy_cnt
3930 --------------------------------------------------------------------
3931 -- Removed support for Modified records.
3932 --------------------------------------------------------------------
3933 -- ||
3934 -- '- Modified Records(Exp/Actual):'||g_exp_modify_cnt
3935 -- ||'/'
3936 -- ||g_modify_cnt
3937 --------------------------------------------------------------------
3938 );
3939 END IF;
3940 --Bug: 3550415.
3941 --If Location already exists then Receiving Subinventories are not migrated, in such scenario
3942 --add proper message to Report.
3943 IF (g_location_status = 'PRE_EXIST') AND (g_entity_type = 'SUBINVENTORIES') THEN
3944 IF (Receiving_Subinv_Exist(g_model_org_id) = 'TRUE') THEN
3945 FND_MESSAGE.SET_NAME (application=>'INV', name=>'INV_REC_SUB_INV_NOT_MIGRATED');
3946 l_message := FND_MESSAGE.GET();
3947 END IF;
3948 END IF;
3949 -------------------------------------------------------------------------
3950 -- Insert record counts for new / model org into report table.
3951 -------------------------------------------------------------------------
3952 IF l_model_rec_cnt > 0
3953 THEN
3954 Insert_Row ( p_location_code => ''
3955 , p_business_group_name => ''
3956 , p_status => ''
3957 , p_error_msg => l_message
3958 , p_rec_type => 'ENTITY_TYPE_SUMMARY'
3959 , p_entity_type => g_entity_type
3960 , p_copy_cnt => g_copy_cnt
3961 , p_exp_copy_cnt => g_exp_copy_cnt
3962 , p_modify_cnt => g_modify_cnt
3963 , p_exp_modify_cnt => g_exp_modify_cnt
3964 , p_entity_name => ''
3965 , p_entity_inconsistency => ''
3966 , p_put_orgs => true
3967 );
3968 END IF;
3969 --dbms_output.put_line('Quant');
3970 -------------------------------------------------------------------------
3971 -- Get Threshold message if error records > G_THRESHOLD
3972 -------------------------------------------------------------------------
3973 IF g_entity_names.COUNT >= G_THRESHOLD
3974 THEN
3975 FND_MESSAGE.SET_NAME (application=>'INV', name=>'INV_CO_THRESHOLD_MSG');
3976 FND_MESSAGE.SET_TOKEN('entity_type', g_entity_type);
3977 FND_MESSAGE.SET_TOKEN('threshold' , G_THRESHOLD);
3978 g_entity_names.EXTEND;
3979 g_entity_names(G_THRESHOLD) := '> '||FND_MESSAGE.GET();
3980 l_entity_count := G_THRESHOLD - 1;
3981 l_message := g_entity_names(G_THRESHOLD);
3982 Insert_Row ( p_location_code => ''
3983 , p_business_group_name => ''
3984 , p_status => ''
3985 , p_error_msg => l_message
3986 , p_rec_type => 'INCONSISTENT_ENTITY_SUMMARY'
3987 , p_entity_type => g_entity_type
3988 , p_copy_cnt => null
3989 , p_exp_copy_cnt => null
3990 , p_modify_cnt => null
3991 , p_exp_modify_cnt => null
3992 , p_entity_name => g_entity_names(G_THRESHOLD)
3993 , p_entity_inconsistency => l_entity_err
3994 , p_put_orgs => true
3995 );
3996 ELSE
3997 l_entity_count := g_entity_names.COUNT - 1;
3998 END IF;
3999 -------------------------------------------------------------------------
4000 -- Insert qualitative data if error records cnt > 1 into report table.
4001 -------------------------------------------------------------------------
4002 --dbms_output.put_line(g_entity_type||': '||l_entity_count);
4003 --shpandey, modified the condition below from "=" to ">=" to handle single failed entity
4004 --for bug# 3678706
4005 IF l_entity_count >= 1
4006 -- end of fix for bug# 3678706
4007 THEN
4008 FND_MESSAGE.SET_NAME ( application=>'INV', name=>l_mesg_name );
4009 FND_MESSAGE.SET_TOKEN ( 'entity_type', g_entity_type );
4010 l_message := FND_MESSAGE.GET();
4011 --dbms_output.put_line(l_message);
4012 FORALL i IN 1..l_entity_count
4013 INSERT INTO mtl_copy_org_report
4014 ( GROUP_CODE
4015 , MODEL_ORGANIZATION_CODE
4016 , ORGANIZATION_CODE
4017 , ERROR_MSG
4018 , REC_TYPE
4019 , ENTITY_TYPE
4020 , ENTITY_NAME
4021 , ENTITY_INCONSISTENCY
4022 )
4023 VALUES
4024 ( g_group_code
4025 , g_model_org_code
4026 , g_organization_code
4027 , l_message
4028 , 'INCONSISTENT_ENTITY_SUMMARY'
4029 , g_entity_type
4030 , ' - '||g_entity_names(i)
4031 , l_entity_err
4032 );
4033 g_error_status_flag := true;
4034 END IF;
4035 END IF;
4036
4037 IF G_DEBUG = 'Y' THEN
4038 FND_MSG_PUB.ADD_EXC_MSG
4039 ( G_PKG_NAME
4040 , l_api_name
4041 , '< Put_Report_Data'
4042 );
4043 END IF;
4044
4045 EXCEPTION
4046 WHEN OTHERS THEN
4047 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4048 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4049 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4050 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4051 FND_MSG_PUB.Add;
4052 RAISE;
4053
4054 END Put_Report_Data;
4055
4056 --=============================================================================
4057 -- PROC NAME : Insert_Row
4058 -- PRE-CONDITIONS: None.
4059 -- DESCRIPTION : Get the translated message from FND MESSAGES table.
4060 -- PARAMETERS :
4061 -- p_msg_name REQUIRED. Name of the error message.
4062 -- p_token_array REQUIRED. List of token name/values in error message.
4063 --
4064 -- EXCEPTIONS : None.
4065 --
4066 --=============================================================================
4067 PROCEDURE Insert_Row
4068 ( p_location_code IN VARCHAR2
4069 , p_business_group_name IN VARCHAR2
4070 , p_status IN VARCHAR2
4071 , p_error_msg IN VARCHAR2
4072 , p_rec_type IN VARCHAR2
4073 , p_entity_type IN VARCHAR2
4074 , p_copy_cnt IN NUMBER
4075 , p_modify_cnt IN NUMBER
4076 , p_exp_copy_cnt IN NUMBER
4077 , p_exp_modify_cnt IN NUMBER
4078 , p_entity_name IN VARCHAR2
4079 , p_entity_inconsistency IN VARCHAR2
4080 , p_put_orgs IN BOOLEAN
4081 )
4082 IS
4083 --l_message VARCHAR2(1000);
4084 l_new_org VARCHAR2(10):='';
4085 l_model_org VARCHAR2(10):='';
4086 l_api_name VARCHAR2(100):=' Insert_Row ';
4087 BEGIN
4088
4089 IF G_DEBUG = 'Y' THEN
4090 FND_MSG_PUB.ADD_EXC_MSG
4091 ( G_PKG_NAME
4092 , l_api_name
4093 , '> Insert_Row '
4094 );
4095 END IF;
4096
4097 IF p_put_orgs THEN
4098 l_new_org := g_organization_code;
4099 l_model_org := g_model_org_code;
4100 END IF;
4101
4102 INSERT INTO MTL_COPY_ORG_REPORT
4103 ( group_code
4104 , model_organization_code
4105 , organization_code
4106 , location_code
4107 , business_group_name
4108 , status
4109 , error_msg
4110 , rec_type
4111 , entity_type
4112 , copied_count
4113 , modified_count
4114 , expected_copied_count
4115 , expected_modified_count
4116 , entity_name
4117 , entity_inconsistency
4118 )
4119 VALUES
4120 ( g_group_code
4121 , l_model_org
4122 , l_new_org
4123 , p_location_code
4124 , p_business_group_name
4125 , p_status
4126 , p_error_msg
4127 , p_rec_type
4128 , p_entity_type
4129 , p_copy_cnt
4130 , p_modify_cnt
4131 , p_exp_copy_cnt
4132 , p_exp_modify_cnt
4133 , p_entity_name
4134 , p_entity_inconsistency
4135 );
4136
4137 IF G_DEBUG = 'Y' THEN
4138 FND_MSG_PUB.ADD_EXC_MSG
4139 ( G_PKG_NAME
4140 , l_api_name
4141 , '< Insert_Row '
4142 );
4143 END IF;
4144
4145 EXCEPTION
4146 WHEN OTHERS THEN
4147 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4148 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4149 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4150 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4151 FND_MSG_PUB.Add;
4152 RAISE;
4153
4154 END Insert_Row;
4155 --=============================================================================
4156 -- FUNCTION NAME : Is_Entity_Modified
4157 -- PRE-CONDITIONS: None.
4158 -- DESCRIPTION : Return True if there are any attributes to be modified
4159 -- for this entity
4160 -- PARAMETERS :
4161 -- p_entity_type REQUIRED. Entity to be checked for modifications.
4162 --
4163 -- EXCEPTIONS : None.
4164 --
4165 -- This FUNCTION is OBSOLETED as support for Modified entities has
4166 -- been removed from FPJ. (as decided on 10/30/2003)
4167 --=============================================================================
4168 --FUNCTION Is_Entity_Modified ( p_entity_type IN VARCHAR2 ) RETURN BOOLEAN
4169 --IS
4170 -- l_api_name VARCHAR2(100):=' Is_Entity_Modified ';
4171 --BEGIN
4172 -- FOR j IN 1..m_user_values.COUNT
4173 -- LOOP
4174 -- IF m_user_values(j).entity_type = p_entity_type
4175 -- THEN
4176 -- RETURN true;
4177 -- END IF;
4178 -- END LOOP;
4179 --
4180 -- IF G_DEBUG = 'Y' THEN
4181 -- FND_MSG_PUB.ADD_EXC_MSG
4182 -- ( G_PKG_NAME
4183 -- , l_api_name
4184 -- , '< Is_Entity_Modified '
4185 -- );
4186 -- END IF;
4187 -- RETURN false;
4188 --END Is_Entity_Modified;
4189 --=============================================================================
4190 -- FUNCTION NAME : Get_Organization_Id
4191 -- PRE-CONDITIONS: None.
4192 -- DESCRIPTION : Get the organization Id for this org code.
4193 -- PARAMETERS :
4194 -- p_org_code REQUIRED. Org Code to be converted to Id.
4195 --
4196 -- EXCEPTIONS : None.
4197 --=============================================================================
4198 FUNCTION Get_Organization_Id ( p_org_code IN VARCHAR2 ) RETURN NUMBER
4199 IS
4200 l_org_id NUMBER;
4201 l_api_name VARCHAR2(100):=' Get_Organization_Id ';
4202 BEGIN
4203 SELECT organization_id
4204 INTO l_org_id
4205 FROM mtl_parameters
4206 WHERE organization_code = p_org_code;
4207
4208 RETURN l_org_id;
4209 EXCEPTION
4210 WHEN OTHERS THEN
4211 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4212 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4213 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4214 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4215 FND_MSG_PUB.Add;
4216 RAISE;
4217
4218 END Get_Organization_Id;
4219 --=============================================================================
4220 -- FUNCTION NAME : Get_Fnd_Message
4221 -- PRE-CONDITIONS: None.
4222 -- DESCRIPTION : Get the translated message from FND MESSAGES table.
4223 -- PARAMETERS :
4224 -- p_msg_name REQUIRED. Name of the error message.
4225 -- p_token_array REQUIRED. List of token name/values in error message.
4226 --
4227 -- EXCEPTIONS : None.
4228 --
4229 --=============================================================================
4230 FUNCTION Get_Fnd_Message
4231 ( p_msg_name IN VARCHAR2
4232 , p_token_array IN Token_Hash
4233 ) RETURN VARCHAR2
4234 IS
4235 -- Modified for bug 3838706
4236 -- l_message VARCHAR2(1000);
4237 l_message VARCHAR2(2000);
4238 l_app VARCHAR2(10):='INV';
4239 l_api_name VARCHAR2(100):=' Get_Fnd_Message ';
4240 BEGIN
4241
4242 IF G_DEBUG = 'Y' THEN
4243 FND_MSG_PUB.ADD_EXC_MSG
4244 ( G_PKG_NAME
4245 , l_api_name
4246 , '> Get_Fnd_Message '
4247 );
4248 END IF;
4249
4250 FND_MESSAGE.SET_NAME( application => l_app, name => p_msg_name );
4251 FOR i IN 1..p_token_array.COUNT
4252 LOOP
4253 FND_MESSAGE.SET_TOKEN
4254 ( p_token_array(i).token_name
4255 , p_token_array(i).token_value
4256 );
4257 END LOOP;
4258
4259 SELECT FND_MESSAGE.GET()
4260 INTO l_message
4261 FROM dual;
4262
4263 IF G_DEBUG = 'Y' THEN
4264 FND_MSG_PUB.ADD_EXC_MSG
4265 ( G_PKG_NAME
4266 , l_api_name
4267 , '< Get_Fnd_Message '
4268 );
4269 END IF;
4270
4271 RETURN l_message;
4272
4273 END Get_Fnd_Message;
4274 --=============================================================================
4275 -- FUNCTION NAME : Purge_Copy_Org_Report
4276 -- PRE-CONDITIONS: None.
4277 -- DESCRIPTION : Purge records from the Copy Org Report table for the
4278 -- group code.
4279 -- PARAMETERS :
4280 -- p_group_code REQUIRED. Group code created for Copy Org request.
4281 --
4282 -- EXCEPTIONS : None.
4283 --
4284 --=============================================================================
4285 FUNCTION Purge_Copy_Org_Report RETURN BOOLEAN
4286 IS
4287 l_group_code VARCHAR2(30);
4288 l_api_name VARCHAR2(100):=' Purge_Copy_Org_Report ';
4289
4290 BEGIN
4291 IF G_DEBUG = 'Y' THEN
4292 FND_MSG_PUB.ADD_EXC_MSG
4293 ( G_PKG_NAME
4294 , l_api_name
4295 , '> Purge Report Data'
4296 );
4297 END IF;
4298
4299 IF (g_group_code = 'SEED') THEN
4300 RETURN false;
4301 END IF;
4302
4303 DELETE mtl_copy_org_report
4304 WHERE group_code = g_group_code;
4305
4306 IF G_DEBUG = 'Y' THEN
4307 FND_MSG_PUB.ADD_EXC_MSG
4308 ( G_PKG_NAME
4309 , l_api_name
4310 , '< Purge Report Data'
4311 );
4312 END IF;
4313
4314 RETURN true;
4315
4316 EXCEPTION
4317 WHEN OTHERS THEN
4318 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4319 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4320 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4321 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4322 FND_MSG_PUB.Add;
4323 RAISE;
4324
4325 END Purge_Copy_Org_Report;
4326
4327 --Bug: 3550415. New function added to verify if Receiving Subinventories exist
4328 -- corresponding to Model Org
4329 --=============================================================================
4330 -- FUNCTION NAME : Receiving_Subinv_Exist
4331 -- PRE-CONDITIONS: None.
4332 -- DESCRIPTION : Returns TRUE if Receiving Subinventories exist for the
4333 -- model org else returns FALSE.
4334 -- PARAMETERS :
4335 -- p_organization_id REQUIRED. Organization Id.
4336 -- EXCEPTIONS : None.
4337 --
4338 --=============================================================================
4339 FUNCTION Receiving_Subinv_Exist (p_organization_id IN NUMBER ) RETURN VARCHAR2
4340 IS
4341 l_api_name VARCHAR2(100):='Receiving_Subinv_Exist';
4342 l_receiving_subinv_count NUMBER;
4343
4344 BEGIN
4345
4346 IF G_DEBUG = 'Y' THEN
4347 FND_MSG_PUB.ADD_EXC_MSG
4348 ( G_PKG_NAME
4349 , l_api_name
4350 , '> Receiving_Subinv_Exist '
4351 );
4352 END IF;
4353
4354 SELECT 1
4355 INTO l_receiving_subinv_count
4356 FROM MTL_SECONDARY_INVENTORIES
4357 WHERE ORGANIZATION_ID = p_organization_id
4358 AND SUBINVENTORY_TYPE = 2
4359 AND ROWNUM = 1;
4360
4361 IF G_DEBUG = 'Y' THEN
4362 FND_MSG_PUB.ADD_EXC_MSG
4363 ( G_PKG_NAME
4364 , l_api_name
4365 , '< Receiving_Subinv_Exist '
4366 );
4367 END IF;
4368
4369 IF l_receiving_subinv_count > 0 THEN
4370 RETURN 'TRUE';
4371 ELSE
4372 RETURN 'FALSE';
4373 END IF;
4374
4375
4376 EXCEPTION
4377 WHEN NO_DATA_FOUND THEN
4378 RETURN 'FALSE';
4379 WHEN OTHERS THEN
4380 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4381 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4382 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4383 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4384 FND_MSG_PUB.Add;
4385 RAISE;
4386
4387 END Receiving_Subinv_Exist;
4388 --=============================================================================
4389 --Bug: 3683490. New function added to convert clob field to varchar2
4390 -- as to_char function() is not supported for version 8i.
4391 --=============================================================================
4392 /*
4393 ** -------------------------------------------------------------------------
4394 ** Function: clob_to_varchar
4395 ** Description: Takes in a CLOB database object and returns the
4396 ** corresponding VARCHAR2 object
4397 ** Input:
4398 ** lobsrc
4399 ** The CLOB to be converted into a VARCHAR2 string
4400 **
4401 ** Returns:
4402 ** The VARCHAR2 string that was converted from the passed in CLOB
4403 ** --------------------------------------------------------------------------
4404 */
4405
4406 FUNCTION clob_to_varchar( lobsrc IN CLOB ) RETURN VARCHAR2
4407 IS
4408 -- Modified for bug 3838706
4409 buffer VARCHAR2( 2000 );
4410 -- buffer VARCHAR2( 1800 );
4411
4412 amount NUMBER;
4413 BEGIN
4414 -- Modified for bug 3838706
4415 amount := 2000;
4416 -- amount := 1800;
4417
4418 IF lobsrc IS NOT NULL THEN
4419 DBMS_LOB.READ( lobsrc, amount, 1, buffer );
4420 END IF;
4421
4422 RETURN buffer;
4423 END clob_to_varchar;
4424
4425 --=============================================================================
4426 -- FUNCTION NAME : Get_Err_Wip_Acc_Classes
4427 -- PRE-CONDITIONS: None.
4428 -- DESCRIPTION : Validate Wip Accounting classes created for new org against
4429 -- those in model org.
4430 -- PARAMETERS : None.
4431 -- EXCEPTIONS : None.
4432 -- shpandey, added the function below for R12 development.
4433 --=============================================================================
4434 FUNCTION Get_Err_Wip_Acc_Classes RETURN Char_Array
4435 IS
4436 l_api_name VARCHAR2(100):= ' Get_Err_Wip_Acc_Classes ';
4437 l_entity_arr_idx NUMBER := 1;
4438 l_entity_names Char_Array := Char_Array();
4439 --l_entity_type VARCHAR2(100):= ' WIP Accounting Classes ';
4440 l_class_code VARCHAR2(10);
4441 l_model_org_count NUMBER;
4442 l_new_org_count NUMBER:=0;
4443
4444 CURSOR l_cursor ( p_model_org_id NUMBER
4445 , p_organization_id NUMBER
4446 )
4447 IS
4448 SELECT wac1.class_code
4449 FROM wip_accounting_classes wac1
4450 WHERE wac1.organization_id = p_model_org_id
4451 AND NOT EXISTS
4452 ( SELECT 'x'
4453 FROM wip_accounting_classes wac2
4454 WHERE wac2.organization_id = p_organization_id
4455 AND wac1.class_code = wac2.class_code
4456 );
4457
4458 CURSOR l_cnt_csr ( p_org_id NUMBER )
4459 IS
4460 SELECT COUNT(wac.class_code)
4461 FROM wip_accounting_classes wac
4462 WHERE wac.organization_id = p_org_id;
4463
4464 BEGIN
4465
4466 IF G_DEBUG = 'Y' THEN
4467 FND_MSG_PUB.ADD_EXC_MSG
4468 ( G_PKG_NAME
4469 , l_api_name
4470 , 'IN INVCORPB: '||l_api_name
4471 );
4472 END IF;
4473
4474 OPEN l_cnt_csr ( g_model_org_id );
4475 FETCH l_cnt_csr INTO l_model_org_count;
4476 CLOSE l_cnt_csr;
4477
4478 OPEN l_cnt_csr ( g_organization_id );
4479 FETCH l_cnt_csr INTO l_new_org_count;
4480 CLOSE l_cnt_csr;
4481
4482 IF l_new_org_count < l_model_org_count
4483 THEN
4484 OPEN l_cursor ( g_model_org_id
4485 , g_organization_id
4486 );
4487 FETCH l_cursor INTO l_class_code;
4488 LOOP
4489 l_entity_names.EXTEND;
4490 l_entity_names(l_entity_arr_idx) := l_class_code;
4491 l_entity_arr_idx := l_entity_arr_idx + 1;
4492
4493 FETCH l_cursor INTO l_class_code;
4494 EXIT WHEN l_cursor%NOTFOUND;
4495 END LOOP;
4496 CLOSE l_cursor;
4497 END IF;
4498
4499 l_entity_names.EXTEND;
4500 l_entity_names(l_entity_arr_idx):=l_model_org_count;
4501
4502 RETURN l_entity_names;
4503 EXCEPTION
4504 WHEN OTHERS THEN
4505 FND_MESSAGE.SET_NAME(application=>'FND',name=>'FND_AS_UNEXPECTED_ERROR');
4506 FND_MESSAGE.SET_TOKEN('error_text', SQLERRM(sqlcode));
4507 FND_MESSAGE.SET_TOKEN('pkg_name' , G_PKG_NAME);
4508 FND_MESSAGE.SET_TOKEN('procedure_name' , l_api_name );
4509 FND_MSG_PUB.Add;
4510 RAISE;
4511
4512 END Get_Err_Wip_Acc_Classes;
4513
4514
4515 END INV_COPY_ORGANIZATION_REPORT;