[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SCE_COLLECTION
Source
1 PACKAGE BODY MSC_CL_SCE_COLLECTION AS -- body
2 /* $Header: MSCXCSCB.pls 120.9 2011/08/18 12:29:08 vjuluri ship $ */
3
4
5 CURSOR newCompCursor(p_sr_instance_id NUMBER) IS
6 SELECT mst.company_name
7 from msc_st_trading_partners mst
8 where sr_instance_id = p_sr_instance_id
9 and company_name is not null
10 MINUS
11 SELECT mc.company_name
12 from msc_companies mc;
13
14 names companyNames;
15 v_my_company msc_companies.company_name%TYPE;
16 v_sr_instance_id NUMBER;
17 lv_sql_stmt VARCHAR2(2048);
18 lv_sql_stmt1 VARCHAR2(2048);
19
20 --=====================================================================
21 -- Get the profile value from profile option MSC:Configuration
22 -- If the profile option is null or not defined then assume it's value
23 -- as 'APS'.
24 --=====================================================================
25
26 G_MSC_CONFIGURATION VARCHAR2(20) := nvl(fnd_profile.value('MSC_X_CONFIGURATION'), G_CONF_APS);
27
28 PROCEDURE PROCESS_COMPANY_CHANGE(p_status OUT NOCOPY NUMBER) IS
29
30 v_my_company_old_name msc_companies.company_name%TYPE;
31 v_my_company_new_name msc_companies.company_name%TYPE;
32
33 BEGIN
34 -- ========== Get My company's old Name ============
35 v_my_company_old_name := MSC_CL_SCE_COLLECTION.GET_MY_COMPANY;
36 IF (v_my_company_old_name = null) then
37
38 LOG_MESSAGE('Error while fetching Company Name');
39 p_status := MSC_CL_COLLECTION.G_ERROR;
40
41 END IF;
42
43 -- ========== Get My company's new name ============
44 v_my_company_new_name := fnd_profile.value('MSC_X_COMPANY_NAME');
45 -- LOG_MESSAGE('The OEM''s Company new name is :'||v_my_company_new_name);
46 --LOG_MESSAGE('The OEM''s Company old name is :'||v_my_company_old_name);
47
48 -- ========== Update msc_companies and msc_trading_partners with new name ===
49
50 if v_my_company_new_name <> v_my_company_old_name then
51
52 -- dbms_output.put_line('In Here');
53
54 -- ==== Update msc_companies ====
55 BEGIN
56 update msc_companies
57 set company_name = v_my_company_new_name
58 where company_id = G_OEM_ID;
59
60 EXCEPTION WHEN OTHERS THEN
61 LOG_MESSAGE('Error while updating Company Name in msc_companies');
62 ROLLBACK;
63 p_status := MSC_CL_COLLECTION.G_ERROR;
64 END;
65
66 -- =======================================
67 -- Update msc_trading_partners.
68 -- Update all records where
69 -- sr_company_id = -1 (This indicates OEM)
70 -- and partner_name = v_my_company_old_name
71 -- ========================================
72 BEGIN
73 update msc_trading_partners
74 set partner_name = v_my_company_new_name
75 where
76 partner_name = v_my_company_old_name
77 and partner_type in (G_SUPPLIER, G_CUSTOMER)
78 and sr_tp_id = -1
79 and nvl(company_id, 1) <> 1;
80 EXCEPTION WHEN OTHERS THEN
81 LOG_MESSAGE('Error while updating Company Name in msc_trading_partners');
82 ROLLBACK;
83 p_status := MSC_CL_COLLECTION.G_ERROR;
84 END;
85 end if;
86
87 COMMIT;
88
89 p_status := MSC_CL_COLLECTION.G_SUCCESS;
90
91 END; -- PROCESS_COMPANY_CHANGE
92
93 FUNCTION SCE_TRANSFORM_KEYS(p_instance_id NUMBER,
94 p_current_user NUMBER,
95 p_current_date DATE,
96 p_last_collection_id NUMBER,
97 p_is_incremental_refresh BOOLEAN,
98 p_is_complete_refresh BOOLEAN,
99 p_is_partial_refresh BOOLEAN,
100 p_is_cont_refresh BOOLEAN,
101 p_supplier_enabled NUMBER,
102 p_customer_enabled NUMBER) RETURN BOOLEAN IS
103 lv_msc_tp_coll_window NUMBER;
104 lv_control_flag NUMBER; -- Bug#12863892 fp of bug 9645763
105 BEGIN
106 -- Initialize the instance_id
107 v_sr_instance_id := null;
108
109 -- Populate instance Id with Current Instance Id
110 v_sr_instance_id := p_instance_id;
111
112 -- LOG_MESSAGE('The instance_id is '||v_sr_instance_id);
113
114 -- ======================================================================================
115 -- Delete all Company related LID tables. These tables will be
116 -- populated again during Collection.
117 -- Perform this step for Complete and partial collections only.
118 -- We need not to delete these tables for net change scenario
119 -- ======================================================================================
120 BEGIN
121 lv_msc_tp_coll_window := NVL(TO_NUMBER(FND_PROFILE.VALUE('MSC_COLLECTION_WINDOW_FOR_TP_CHANGES')),0);
122 EXCEPTION
123 WHEN OTHERS THEN
124 lv_msc_tp_coll_window := 0;
125 END;
126
127 --Bug#12863892 fp of bug 9645763
128 BEGIN
129 SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
130 INTO lv_control_flag
131 FROM dual;
132 EXCEPTION
133 WHEN OTHERS THEN
134 lv_control_flag := 2;
135 END;
136
137 BEGIN
138
139 IF (p_is_incremental_refresh <> TRUE) THEN
140
141 --Bug#12863892 fp of bug 9645763
142 -- ========== Check for updates in company names ==========
143 LOG_MESSAGE('lv_control_flag value = '||lv_control_flag);
144 IF (lv_control_flag = 1) THEN
145 UPDATE_COMPANY_NAMES(p_current_user,
146 p_current_date);
147
148 COMMIT;
149 END IF;
150
151 -- ========== Check for updates in company site names ==========
152 UPDATE_COMPANY_SITE_NAMES;
153
154
155 IF (p_is_complete_refresh) THEN
156
157 -- ======================================================================================
158 -- Delete Records from msc_trading_partner_maps which are of typr "Planning organization".
159 -- These records will be found using msc_company_site_id_lid as reference.
160 -- ======================================================================================
161 lv_sql_stmt1 := ' delete msc_trading_partner_maps mtpm '||
162 ' where exists(select 1 '||
163 ' from msc_company_site_id_lid mcsil '||
164 ' where mcsil.company_site_id = mtpm.company_key '||
165 ' and mcsil.partner_type = 3'||
166 ' and mcsil.sr_instance_id = :v_sr_instance_id ) '||
167 ' and mtpm.map_type = 2';
168
169 EXECUTE IMMEDIATE lv_sql_stmt1 USING v_sr_instance_id;
170
171 COMMIT;
172
173 IF lv_msc_tp_coll_window = 0 THEN
174 DELETE MSC_COMPANY_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
175 DELETE MSC_COMPANY_SITE_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
176 END IF;
177
178
179 -- ======================================================================================
180 -- Delete Records from msc_trading_partner_maps which are of typr "Planning organization".
181 -- This step is required because APS always deletes the Planning Org records and collect
182 -- it fresh.
183 -- ======================================================================================
184
185 lv_sql_stmt:= ' delete msc_trading_partner_maps mtpm '||
186 ' where exists (select 1 '||
187 ' from msc_trading_partners mtp '||
188 ' where mtp.partner_type = 3'||
189 ' and mtp.sr_instance_id = :v_sr_instance_id'||
190 ' and mtp.partner_id = mtpm.tp_key '||
191 ' )'||
192 ' and mtpm.map_type = 2';
193
194 EXECUTE IMMEDIATE lv_sql_stmt USING v_sr_instance_id;
195
196 COMMIT;
197
198 ELSIF (p_is_partial_refresh or p_is_cont_refresh) THEN
199
200 IF ((p_supplier_enabled = MSC_CL_COLLECTION.SYS_YES)
201 OR
202 (p_customer_enabled = MSC_CL_COLLECTION.SYS_YES)) THEN
203
204 -- ======================================================================================
205 -- Delete Records from msc_trading_partner_maps which are of typr "Planning organization".
206 -- These records will be found using msc_company_site_id_lid as reference.
207 -- ======================================================================================
208 lv_sql_stmt1 := ' delete msc_trading_partner_maps mtpm '||
209 ' where exists(select 1 '||
210 ' from msc_company_site_id_lid mcsil '||
211 ' where mcsil.company_site_id = mtpm.company_key '||
212 ' and mcsil.partner_type = 3'||
213 ' and mcsil.sr_instance_id = :v_sr_instance_id ) '||
214 ' and mtpm.map_type = 2';
215
216 EXECUTE IMMEDIATE lv_sql_stmt1 USING v_sr_instance_id;
217
218 COMMIT;
219 IF lv_msc_tp_coll_window = 0 THEN
220 DELETE MSC_COMPANY_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
221 DELETE MSC_COMPANY_SITE_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
222 END IF;
223
224 -- ======================================================================================
225 -- Delete Records from msc_trading_partner_maps which are of typr "Planning organization".
226 -- This step is required because APS always deletes the Planning Org records and collect
227 -- it fresh.
228 -- ======================================================================================
229
230 lv_sql_stmt:= ' delete msc_trading_partner_maps mtpm '||
231 ' where exists (select 1 '||
232 ' from msc_trading_partners mtp '||
233 ' where mtp.partner_type = 3'||
234 ' and mtp.sr_instance_id = :v_sr_instance_id'||
235 ' and mtp.partner_id = mtpm.tp_key '||
236 ' )'||
237 ' and mtpm.map_type = 2';
238
239 EXECUTE IMMEDIATE lv_sql_stmt USING v_sr_instance_id;
240
241 COMMIT;
242
243 END IF;
244
245 END IF;
246
247 END IF;
248 EXCEPTION WHEN OTHERS THEN
249 ROLLBACK;
250 LOG_MESSAGE('Error while deleting the SCE LID/ Maps tables');
251 END;
252
253 -- ======================================================================================
254 -- Create company_id for new companies.
255 -- ======================================================================================
256
257 LOG_MESSAGE('Creating global Ids for new Companies');
258 CREATE_NEW_COMPANIES ( p_current_user,
259 p_current_date,
260 p_last_collection_id );
261 COMMIT;
262
263 -- ======================================================================================
264 -- Populate msc_company_id_lid table with new Company information
265 -- ======================================================================================
266
267 LOG_MESSAGE('Populating msc_company_id_lid');
268 POPULATE_COMPANY_ID_LID;
269 COMMIT;
270
271 -- ======================================================================================
272 -- Create relationships for new Companies
273 -- ======================================================================================
274
275 LOG_MESSAGE('Populating new Company Relationships');
276 CREATE_NEW_RELATIONSHIPS;
277 COMMIT;
278
279 -- ======================================================================================
280 -- Create new Company Sites
281 -- ======================================================================================
282
283 LOG_MESSAGE('Creating new company sites');
284 CREATE_NEW_COMPANY_SITES;
285 COMMIT;
286
287 -- Populate msc_company_site_id_lid
288 LOG_MESSAGE('Populating msc_company_site_id_lid');
289 POPULATE_COMPANY_SITE_ID_LID;
290 COMMIT;
291
292 -- Collect Company Information
293 -- This step is commented out since we do not require any Company attributes in
294 -- msc_companies.
295
296 -- LOG_MESSAGE('Collecting Companies');
297 -- COLLECT_COMPANIES;
298
299 -- ======================================================================================
300 -- Collect Company Site Information
301 -- This will collect the all Site attributes. The attributes, we are currently
302 -- interested in are
303 -- 1. planning_enabled_flag.
304 -- 2. Address attributes.
305 -- 3. Location Code.
306 -- Rest of the attributes are also collected for future use.
307 -- ======================================================================================
308
309 LOG_MESSAGE('Collecting Company sites');
310 COLLECT_COMPANY_SITES;
311 COMMIT;
312
313 -- Following code is commented because we will not be collecting locations seperately.
314 -- LOG_MESSAGE('Collecting Company Locations');
315 -- CREATE_NEW_COMPANY_LOCATIONS (v_sr_instance_id);
316 -- COLLECT_COMPANY_LOCATIONS (v_sr_instance_id);
317
318 -- If there is no error in processing the return TRUE
319 return TRUE;
320
321
322 END SCE_TRANSFORM_KEYS;
323
324 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
325 IS
326 BEGIN
327
328 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
329
330 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
331
332 ELSE
333
334 -- dbms_output.put_line( pBUFF);
335 null;
336
337 END IF;
338
339 END LOG_MESSAGE;
340
341 FUNCTION GET_MY_COMPANY return VARCHAR2 IS
342 p_my_company msc_companies.company_name%TYPE;
343 BEGIN
344
345 /* Get the name of the own Company */
346 /* This name is seeded with company_is = 1 in msc_companies */
347 BEGIN
348 select company_name into p_my_company
349 from msc_companies
350 where company_id = 1;
351 EXCEPTION
352 WHEN OTHERS THEN
353 return 'My Company';
354 END;
355
356 LOG_MESSAGE('The name in GET_MY_COMPANY :'||p_my_company);
357 return p_my_company;
358
359 END GET_MY_COMPANY;
360
361 ----Bug#12863892 fp of bug 9645763, PARTNER NAME CHANGE START
362 PROCEDURE UPDATE_COMPANY_NAMES( p_current_user NUMBER,
363 p_current_date DATE) IS
364
365 -- Cursor for changed company names
366 CURSOR updateCompanyNames IS
367 SELECT mc.company_id, mtps.company_name
368 FROM msc_st_trading_partners mtps,
369 msc_company_id_lid mcil,
370 msc_companies mc
371 WHERE mtps.sr_instance_id = v_sr_instance_id
372 AND mcil.sr_instance_id = mtps.sr_instance_id
373 AND mcil.partner_type = decode(mtps.partner_type, G_SUPPLIER, G_CUSTOMER, G_CUSTOMER, G_SUPPLIER)
374 AND mcil.sr_company_id = mtps.company_id
375 AND mc.company_id = mcil.company_id
376 AND mc.company_name <> mtps.company_name
377 AND mtps.partner_type in (G_SUPPLIER, G_CUSTOMER);
378
379 a_company_id number_arr;
380 a_company_name companyNames;
381
382 BEGIN
383
384 LOG_MESSAGE('In UPDATE_COMPANY_NAMES Procedure...');
385 OPEN updateCompanyNames;
386
387 FETCH updateCompanyNames BULK COLLECT INTO
388 a_company_id,
389 a_company_name;
390
391 CLOSE updateCompanyNames;
392
393
394 LOG_MESSAGE('No. of company name change records = '||a_company_id.COUNT);
395 -- update msc_companies table
396 -- Perform this step only if some data is fetched
397 IF a_company_id.COUNT > 0 THEN
398 FOR i IN 1..a_company_id.COUNT LOOP
399
400 BEGIN
401 UPDATE msc_companies
402 SET company_name = a_company_name(i),
403 last_update_date = p_current_date,
404 last_updated_by = p_current_user,
405 last_update_login = p_current_user
406 WHERE company_id = a_company_id(i);
407
408 EXCEPTION
409 WHEN DUP_VAL_ON_INDEX THEN
410 LOG_MESSAGE('Unique key violation while updating company_names');
411 LOG_MESSAGE('Clean up required for the company - Company ID: '||a_company_id(i));
412 LOG_MESSAGE(SQLERRM);
413 WHEN OTHERS THEN
414 LOG_MESSAGE('Error while updating company_names');
415 LOG_MESSAGE(SQLERRM);
416 END;
417 END LOOP;
418 END IF;
419
420 END UPDATE_COMPANY_NAMES;
421 ----Bug#12863892 fp of bug 9645763, PARTNER NAME CHANGE END
422
423 PROCEDURE UPDATE_COMPANY_SITE_NAMES IS
424
425 -- Cursor for changed company site names
426 CURSOR updCompanyNameRecords IS
427 SELECT mcs.company_id, mcs.company_site_id,
428 decode(mtps.partner_type,G_SUPPLIER, mtps.tp_site_code, G_CUSTOMER, mtps.LOCATION)
429 FROM msc_st_Trading_partner_sites mtps,
430 msc_company_site_id_lid mcsl,
431 msc_company_sites mcs --bug 5097405
432 WHERE mtps.sr_instance_id = mcsl.sr_instance_id
433 AND mtps.sr_instance_id = v_sr_instance_id
434 AND mtps.partner_type = mcsl.partner_type
435 AND mtps.sr_tp_site_id = mcsl.sr_company_site_id
436 AND mcs.company_site_id = mcsl.company_site_id
437 AND mcs.company_site_name <> decode(mtps.partner_type,G_SUPPLIER, mtps.tp_site_code, G_CUSTOMER, mtps.LOCATION)
438 AND mtps.partner_type in (G_SUPPLIER, G_CUSTOMER);
439
440
441 a_company_id number_arr;
442 a_company_site_id number_arr;
443 a_company_site_name companySites;
444
445 BEGIN
446
447 OPEN updCompanyNameRecords;
448
449 FETCH updCompanyNameRecords BULK COLLECT INTO
450 a_company_id,
451 a_company_site_id,
452 a_company_site_name;
453
454 CLOSE updCompanyNameRecords;
455
456
457 LOG_MESSAGE('No. of msc_company_sites name change records = '||a_company_site_id.COUNT);
458 -- update msc_company_sites table
459 -- Perform this step only if some data is fetched
460 IF a_company_site_id.COUNT > 0 THEN
461 FOR i IN 1..a_company_site_id.COUNT LOOP
462 BEGIN
463 UPDATE msc_company_sites
464 SET company_site_name=a_company_site_name(i)
465 WHERE company_site_id = a_company_site_id(i)
466 AND company_id = a_company_id(i);
467 EXCEPTION
468 WHEN DUP_VAL_ON_INDEX THEN
469 LOG_MESSAGE('Unique key violation while updating company_site_names');
470 LOG_MESSAGE('Clean up required for the site - Company ID: '||a_company_id(i)||', Site ID: '||a_company_site_id(i)||', Site Name: '||a_company_site_name(i));
471 LOG_MESSAGE(SQLERRM);
472 WHEN OTHERS THEN
473 LOG_MESSAGE('Error while updating company_site_names');
474 LOG_MESSAGE(SQLERRM);
475 END;
476 END LOOP;
477 END IF;
478
479 END UPDATE_COMPANY_SITE_NAMES;
480
481 PROCEDURE CREATE_NEW_COMPANIES( p_current_user NUMBER,
482 p_current_date DATE,
483 p_last_collection_id NUMBER ) IS
484 BEGIN
485
486 -- LOG_MESSAGE('Fetched My Company name');
487 v_my_company := GET_MY_COMPANY;
488
489 /* Bulk Collect new Company Names into names */
490
491 open newCompCursor(v_sr_instance_id);
492 FETCH newCompCursor BULK COLLECT INTO names;
493 close newCompCursor;
494
495 -- LOG_MESSAGE('Bulk Collected new Company names');
496
497 /* Bulk insert the new Companies in msc_companies */
498 /* Do this step only if there is data fetched for insert */
499
500 if names.LAST > 0 then
501 BEGIN
502 FORALL i IN names.FIRST..names.LAST
503 insert into msc_companies
504 (COMPANY_ID ,
505 COMPANY_NAME ,
506 CREATION_DATE ,
507 CREATED_BY ,
508 LAST_UPDATE_DATE ,
509 LAST_UPDATED_BY ,
510 LAST_UPDATE_LOGIN
511 )
512 values
513 ( msc_companies_s.nextval,
514 names(i),
515 p_current_date,
516 p_current_user,
517 p_current_date,
518 p_current_user,
519 p_current_user
520 );
521 EXCEPTION
522 WHEN OTHERS THEN
523 LOG_MESSAGE('Error while creating new companies');
524 LOG_MESSAGE('========================================');
525 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_NEW_COMP_ERR');
526 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CREATE_NEW_COMPANIES');
527 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPANIES');
528 LOG_MESSAGE(FND_MESSAGE.GET);
529
530 LOG_MESSAGE(SQLERRM);
531 END;
532 COMMIT;
533 --Bug 5155944: Analysing the table to improve performance
534 msc_analyse_tables_pk.analyse_table( 'MSC_COMPANIES');
535 END IF;
536
537 END CREATE_NEW_COMPANIES;
538
539 PROCEDURE POPULATE_COMPANY_ID_LID IS
540
541 -- Cursor for msc_company_id_lid
542 CURSOR newCompLidRecords IS
543 select distinct
544 mst.sr_instance_id sr_instance_id,
545 nvl(mst.company_id, -1) sr_company_id,
546 decode(mst.partner_type, G_SUPPLIER, G_CUSTOMER,
547 G_CUSTOMER, G_SUPPLIER,
548 mst.partner_type) partner_type,
549 mc.company_id company_id
550 from msc_st_trading_partners mst,
551 msc_companies mc
552 where nvl(mst.company_name, v_my_company) = mc.company_name
553 and mst.sr_instance_id = v_sr_instance_id
554 MINUS
555 select mcil.sr_instance_id,
556 mcil.sr_company_id,
557 mcil.partner_type,
558 mcil.company_id
559 from msc_company_id_lid mcil;
560
561 a_sr_instance_id number_arr;
562 a_sr_company_id number_arr;
563 a_partner_type number_arr;
564 a_company_id number_arr;
565
566
567 BEGIN
568
569 open newCompLidRecords;
570 FETCH newCompLidRecords BULK COLLECT INTO
571 a_sr_instance_id,
572 a_sr_company_id,
573 a_partner_type,
574 a_company_id;
575 close newCompLidRecords;
576
577
578 LOG_MESSAGE('No. of company_id_lid records = '||a_sr_instance_id.COUNT);
579 -- Populate msc_company_id_lid table
580 -- Perform this step only if some data is fetched
581 BEGIN
582 IF a_sr_instance_id.COUNT > 0 THEN
583
584 FORALL i IN 1..a_sr_instance_id.COUNT
585 insert into msc_company_id_lid
586 ( sr_instance_id,
587 sr_company_id,
588 partner_type,
589 company_id
590 )
591 values
592 ( a_sr_instance_id(i),
593 a_sr_company_id(i),
594 a_partner_type(i),
595 a_company_id(i)
596 );
597 END IF;
598 EXCEPTION WHEN OTHERS THEN
599 LOG_MESSAGE('Error while uploading msc_company_id_lid table');
600
601 LOG_MESSAGE('========================================');
602 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_COMP_LID_ERR');
603 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_COMPANY_ID_LID');
604 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPANY_ID_LID');
605 LOG_MESSAGE(FND_MESSAGE.GET);
606
607 LOG_MESSAGE(SQLERRM);
608
609 ROLLBACK;
610 RETURN ;
611 END;
612
613 -- Commit the transaction
614 COMMIT;
615 --Bug 5155944: Analysing the table to improve performance
616 msc_analyse_tables_pk.analyse_table( 'MSC_COMPANY_ID_LID');
617
618
619 END POPULATE_COMPANY_ID_LID;
620
621 PROCEDURE CREATE_NEW_RELATIONSHIPS IS
622
623 -- Cursor of new relationships --
624
625 CURSOR newCompRelCursor IS
626 select mc1.company_id subject_id,
627 mc2.company_id object_id,
628 mst.partner_type relationship_type
629 from msc_st_trading_partners mst,
630 msc_companies mc1,
631 msc_companies mc2
632 where nvl(mst.company_name, v_my_company) = mc2.company_name
633 and nvl(mst.partner_name, v_my_company) = mc1.company_name
634 and mst.sr_instance_id = v_sr_instance_id
635 -- Do not include Inventory Organizations in relationship records.
636 and mst.partner_type <> 3
637 MINUS
638 select subject_id, object_id, relationship_type
639 from msc_company_relationships;
640
641 a_subject_id number_arr;
642 a_object_id number_arr;
643 a_relationship_type number_arr;
644
645 BEGIN
646 LOG_MESSAGE('Uploading new Company relationships');
647
648 -- Bulk Collect new Relationships
649
650 open newCompRelCursor;
651 FETCH newCompRelCursor BULK COLLECT INTO
652 a_subject_id,
653 a_object_id,
654 a_relationship_type;
655 close newCompRelCursor;
656
657
658 -- Insert new Relationships
659 -- Do this step only if some data is fetched in earlier step
660 IF a_subject_id.COUNT > 0 THEN
661 BEGIN
662 FORALL i IN 1..a_subject_id.COUNT
663 INSERT INTO msc_company_relationships
664 ( RELATIONSHIP_ID ,
665 SUBJECT_ID ,
666 OBJECT_ID ,
667 RELATIONSHIP_TYPE ,
668 CREATION_DATE ,
669 CREATED_BY ,
670 LAST_UPDATE_DATE ,
671 LAST_UPDATED_BY
672 )
673 values
674 ( msc_company_rels_s.nextval,
675 a_subject_id(i),
676 a_object_id(i),
677 a_relationship_type(i),
678 sysdate,
679 -1,
680 sysdate,
681 -1
682 );
683 EXCEPTION
684 WHEN OTHERS THEN
685 LOG_MESSAGE('Error while creating new relationships');
686
687 LOG_MESSAGE('========================================');
688 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_COMPANY_REL_ERR');
689 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CREARE_NEW_RELATIONSHIPS');
690 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPANY_RELATIONSHIPS');
691 LOG_MESSAGE(FND_MESSAGE.GET);
692
693 LOG_MESSAGE(SQLERRM);
694 ROLLBACK;
695 END;
696 COMMIT;
697 --Bug 5155944: Analysing the table to improve performance
698 msc_analyse_tables_pk.analyse_table( 'MSC_COMPANY_RELATIONSHIPS');
699 END IF;
700
701 END CREATE_NEW_RELATIONSHIPS;
702
703 PROCEDURE CREATE_NEW_COMPANY_SITES IS
704
705 -- Cursor for New Company Sites
706
707 -- Get the Planning and non planning Sites
708 CURSOR newCompSites IS
709 Select mc.company_id company_id,
710 mst.organization_code company_site_name
711 from msc_st_trading_partners mst,
712 msc_companies mc
713 where nvl(mst.company_name, v_my_company) = mc.company_name
714 and mst.sr_instance_id = v_sr_instance_id
715 and mst.partner_type = 3
716
717 UNION
718
719 -- Add Sites from msc_st_trading_partner_sites for CUSTOMERS
720 -- This step is required because for Oracle ERP data there won't be
721 -- any record in msc_trading_partners for Supplier and Customer Sites.
722
723 select mcil.company_id company_id,
724 mstp.LOCATION company_site_name
725 from msc_st_trading_partner_sites mstp,
726 msc_company_id_lid mcil
727 where
728 -- Make sure that the Trading partner is defined as Company
729 mstp.sr_tp_id = mcil.sr_company_id
730 and mstp.sr_instance_id = mcil.sr_instance_id
731 and mstp.partner_type = mcil.partner_type
732 and mstp.sr_instance_id = v_sr_instance_id
733
734 -- Partner_type should not be 3, i.e. organization
735 -- because this Partner Type is already included in previous query.
736 and mstp.partner_type = G_CUSTOMER
737
738 UNION
739
740 -- Add sites from msc_trading_partner_sites for Suppliers.
741 SELECT mcil.company_id company_id,
742 mstp.tp_site_code company_site_name
743 from msc_st_trading_partner_sites mstp,
744 msc_company_id_lid mcil
745 where
746 -- Make sure that the Trading partner is defined as Company
747 mstp.sr_tp_id = mcil.sr_company_id
748 and mstp.sr_instance_id = mcil.sr_instance_id
749 and mstp.sr_instance_id = v_sr_instance_id
750 and mstp.partner_type = mcil.partner_type
751
752 -- Partner_type should not be 3, i.e. organization
753 -- Partner Type is already included in above query.
754 and mstp.partner_type = G_SUPPLIER
755 MINUS
756 select mcs.company_id company_id,
757 mcs.company_site_name company_site_name
758 from msc_company_sites mcs;
759
760 -- Variables to hold company_id and company_site_name
761 a_company_id number_arr;
762 a_company_site_name companySites;
763
764 BEGIN
765 /* Fetch new site records */
766
767 OPEN newCompSites;
768
769 FETCH newCompSites BULK COLLECT INTO
770 a_company_id,
771 a_company_site_name;
772
773 close newCompSites;
774
775
776 /* Populate msc_company_sites with new records.
777 Do this step only if some data is fetched */
778
779
780 if a_company_id.COUNT > 0 THEN
781 BEGIN
782 FORALL i IN 1..a_company_id.COUNT
783 INSERT INTO msc_company_sites
784 ( company_site_id,
785 company_id,
786 company_site_name,
787 creation_date,
788 created_by,
789 last_update_date,
790 last_updated_by
791 )
792 values
793 ( msc_company_sites_s.nextval,
794 a_company_id(i),
795 a_company_site_name(i),
796 sysdate,
797 -1,
798 sysdate,
799 -1
800 );
801 EXCEPTION
802 WHEN OTHERS THEN
803 LOG_MESSAGE('Error while creating new Sites');
804
805 LOG_MESSAGE('========================================');
806 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_COMP_SITES_ERR');
807 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CREATE_NEW_COMPANY_SITES');
808 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPANY_SITES');
809 LOG_MESSAGE(FND_MESSAGE.GET);
810
811 LOG_MESSAGE(SQLERRM);
812 ROLLBACK;
813 END;
814 COMMIT;
815 --Bug 5155944: Analysing the table to improve performance
816 msc_analyse_tables_pk.analyse_table( 'MSC_COMPANY_SITES');
817 END IF;
818
819 END CREATE_NEW_COMPANY_SITES;
820
821 PROCEDURE POPULATE_COMPANY_SITE_ID_LID IS
822
823 BEGIN
824 LOG_MESSAGE('POPULATE_COMPANY_SITE_ID_LID started');
825
826 BEGIN
827
828 INSERT INTO MSC_COMPANY_SITE_ID_LID
829 ( SR_INSTANCE_ID,
830 SR_COMPANY_ID,
831 SR_COMPANY_SITE_ID,
832 PARTNER_TYPE,
833 COMPANY_SITE_ID
834 )
835 SELECT mst.sr_instance_id sr_instace_id,
836 nvl(mst.company_id, -1) sr_company_id,
837 mst.sr_tp_id sr_company_site_id,
838 mst.partner_type partner_type,
839 mcs.company_site_id
840 from msc_st_trading_partners mst,
841 msc_company_id_lid mcil,
842 msc_company_sites mcs
843 where nvl(mst.company_id, -1) = mcil.sr_company_id
844 and mst.sr_instance_id = mcil.sr_instance_id
845 and mst.partner_type = mcil.partner_type
846 and mst.sr_instance_id = v_sr_instance_id
847 and mst.partner_type = G_ORGANIZATION
848 and mcil.company_id = mcs.company_id
849 and mst.organization_code = mcs.company_site_name
850 and not exists (select 1
851 from msc_company_site_id_lid mcsil
852 where mcsil.sr_instance_id = mst.sr_instance_id
853 and mcsil.sr_company_id = nvl(mst.company_id, -1)
854 and mcsil.sr_company_site_id = mst.sr_tp_id
855 and mcsil.partner_type = mst.partner_type
856 and mcsil.company_site_id = mcs.company_site_id)
857 UNION
858 -- Local Id - Source Id map for Customer and Supplier Sites.
859 SELECT mtps.sr_instance_id,
860 mtps.sr_tp_id sr_company_id,
861 mtps.sr_tp_site_id sr_company_site_id,
862 mtps.partner_type,
863 mcs.company_site_id
864 from msc_st_trading_partner_sites mtps,
865 msc_company_id_lid mcil,
866 msc_company_sites mcs
867 where mtps.sr_instance_id = mcil.sr_instance_id
868 and mtps.sr_instance_id = v_sr_instance_id
869 and mtps.sr_tp_id = mcil.sr_company_id
870 and mtps.partner_type = mcil.partner_type
871 and mcil.company_id = mcs.company_id
872 and decode(mtps.partner_type, 2, mtps.LOCATION,
873 1, tp_site_code ) = mcs.company_site_name
874 and not exists (select 1
875 from msc_company_site_id_lid mcsil
876 where mcsil.sr_instance_id = mtps.sr_instance_id
877 and mcsil.sr_company_id = mtps.sr_tp_id
878 and mcsil.sr_company_site_id = mtps.sr_tp_site_id
879 and mcsil.partner_type = mtps.partner_type
880 and mcsil.company_site_id = mcs.company_site_id);
881 EXCEPTION
882 WHEN OTHERS THEN
883 LOG_MESSAGE('Error while POPULATing COMPANY_SITE_ID_LID');
884 LOG_MESSAGE('========================================');
885 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_COMPSITE_IDLID_ERR');
886 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'POPULATE_COMPANY_SITE_ID_LID');
887 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPANY_SITE_ID_LID');
888 LOG_MESSAGE(FND_MESSAGE.GET);
889 LOG_MESSAGE(SQLERRM);
890
891 END;
892 COMMIT;
893 --Bug 5155944: Analysing the table to improve performance
894 msc_analyse_tables_pk.analyse_table( 'MSC_COMPANY_SITE_ID_LID');
895
896 LOG_MESSAGE('Successfully populated MSC_COMPANY_SITE_ID_LID');
897
898 END POPULATE_COMPANY_SITE_ID_LID;
899
900
901 -- Trading Partner Data cleanup.
902
903 FUNCTION CLEANSE_DATA_FOR_SCE(p_instance_id NUMBER ,
904 p_my_company VARCHAR2) RETURN BOOLEAN IS
905
906 CURSOR biDirectional IS
907 select
908 sr_instance_id,
909 sr_tp_id company_id,
910 nvl(company_id, -1) sr_tp_id,
911 partner_name company_name,
912 decode(partner_type,G_SUPPLIER, G_CUSTOMER, G_CUSTOMER, G_SUPPLIER) partner_type,
913 nvl(company_name, p_my_company) partner_name
914 from msc_st_trading_partners
915 where sr_instance_id = p_instance_id
916 and partner_type in (G_SUPPLIER, G_CUSTOMER)
917 MINUS
918 select
919 sr_instance_id,
920 nvl(company_id, -1) company_id,
921 sr_tp_id,
922 nvl(company_name, p_my_company) company_name,
923 partner_type,
924 partner_name
925 from msc_st_trading_partners
926 where sr_instance_id = p_instance_id
927 and partner_type in (G_SUPPLIER, G_CUSTOMER);
928
929 a_sr_tp_id number_arr;
930 a_partner_name companyNames;
931 a_sr_company_id number_arr;
932 a_partner_type number_arr;
933 a_company_name companyNames;
934 a_sr_instance_id number_arr;
935
936
937 /* Error out records in msc_st_item_suppliers if
938 same Supplier Item belonging to same Supplier Site is pointing
939 to multiple master items.
940 */
941 CURSOR validateItemSuppliers IS
942 select
943 nvl(company_id ,-1),
944 using_organization_id,
945 organization_id,
946 supplier_id,
947 supplier_site_id,
948 item_name,
949 count(*) count
950 from msc_st_item_suppliers
951 where sr_instance_id = p_instance_id
952 and item_name is not null
953 group by nvl(company_id ,-1), using_organization_id,
954 organization_id, supplier_id, supplier_site_id, item_name
955 having count(*) > 1 ;
956
957 a_cust_company_id number_arr;
958 a_using_organization_id number_arr;
959 a_organization_id number_arr;
960 a_supplier_id number_arr;
961 a_supplier_site_id number_arr;
962 a_item_name items;
963 a_count number_arr;
964
965 c_non_my_company NUMBER;
966 a_instance_type NUMBER;
967
968 CURSOR validateItemCustomers IS
969 select
970 customer_id,
971 customer_site_id,
972 customer_item_name,
973 count(*) count
974 from msc_st_item_customers mic
975 where sr_instance_id = p_instance_id
976 and item_name is not null
977 group by customer_id,
978 customer_site_id,
979 company_id,
980 customer_item_name
981 having count(*) > 1;
982
983 a_customer_id number_arr;
984 a_customer_site_id number_arr;
985 a_company_id number_arr;
986 a1_item_name items;
987 a1_count number_arr;
988
989
990 BEGIN
991 LOG_MESSAGE('CLEANSE_DATA_FOR_SCE started');
992 LOG_MESSAGE('The instance_id is : '||p_instance_id);
993 -- return TRUE;
994
995 /* Data validation starts */
996
997 /* Abandon Collection if
998 - MSC:Configuration = 'APS' and
999 - Company_name <> v_my_company
1000
1001 company_name <> v_my_company indicates that it's a multi company data.
1002 */
1003
1004 --=============================================================================
1005 -- Initialize G_MSC_CONFIGURATION if it is not initialized already
1006 --=============================================================================
1007
1008 IF G_MSC_CONFIGURATION = NULL THEN
1009 G_MSC_CONFIGURATION := nvl(fnd_profile.value('MSC_ATP_DEFAULT_INSTANCE'), G_CONF_APS);
1010 END IF;
1011
1012 IF (G_MSC_CONFIGURATION = G_CONF_APS) THEN
1013 BEGIN
1014 select count(*)
1015 into c_non_my_company
1016 from msc_st_trading_partners mstp
1017 where nvl(mstp.company_name, p_my_company) <> p_my_company
1018 and mstp.sr_instance_id = p_instance_id;
1019
1020 IF (c_non_my_company > 0) THEN
1021
1022 LOG_MESSAGE('========================================');
1023 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_MULTICOMP_DATA_ERR');
1024 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CLEANSE_DATA_FOR_SCE');
1025 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ST_TRADING_PARTNERS');
1026 LOG_MESSAGE(FND_MESSAGE.GET);
1027
1028
1029 RETURN FALSE;
1030 END IF;
1031
1032 EXCEPTION WHEN OTHERS THEN
1033
1034 LOG_MESSAGE('========================================');
1035
1036 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1037 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CLEANSE_DATA_FOR_SCE');
1038 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ST_TRADING_PARTNERS');
1039 LOG_MESSAGE(FND_MESSAGE.GET);
1040
1041
1042 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_MULTICOMP_SQL_ERR');
1043 LOG_MESSAGE(FND_MESSAGE.GET);
1044
1045 LOG_MESSAGE(SQLERRM);
1046
1047 RETURN FALSE;
1048 END;
1049 END IF;
1050
1051
1052 /* Abandon the Collection if
1053 - Source = Exchange and
1054 - MSC:Configuration = 'APS'
1055 */
1056
1057 IF (G_MSC_CONFIGURATION = G_CONF_APS) THEN
1058 BEGIN
1059 select instance_type
1060 into a_instance_type
1061 from msc_apps_instances mai
1062 where mai.instance_id = p_instance_id;
1063
1064 IF (a_instance_type = 3) THEN
1065
1066 LOG_MESSAGE('========================================');
1067 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_MPX_COLL_ERR');
1068 LOG_MESSAGE(FND_MESSAGE.GET);
1069
1070 RETURN FALSE;
1071 END IF;
1072
1073 EXCEPTION WHEN OTHERS THEN
1074 LOG_MESSAGE('ERROR while validating Trading Partner staing table for APS configuration');
1075 LOG_MESSAGE(SQLERRM);
1076 RETURN FALSE;
1077 END;
1078 END IF;
1079
1080 --==== Data Validation for the many to one records in msc_st_item_suppliers
1081
1082 /* Error out records in msc_st_item_suppliers if
1083 same Supplier Item belonging to same Supplier Site is pointing
1084 to multiple master items.
1085 */
1086
1087 BEGIN
1088 LOG_MESSAGE('Validation of msc_item_suppliers started');
1089
1090 OPEN validateItemSuppliers;
1091
1092 FETCH validateItemSuppliers BULK COLLECT INTO
1093 a_cust_company_id,
1094 a_using_organization_id,
1095 a_organization_id ,
1096 a_supplier_id ,
1097 a_supplier_site_id ,
1098 a_item_name,
1099 a_count ;
1100 CLOSE validateItemSuppliers;
1101
1102 EXCEPTION WHEN OTHERS THEN
1103 LOG_MESSAGE('Error while fetching records from validateItemSuppliers cursor');
1104 LOG_MESSAGE(SQLERRM);
1105 return FALSE;
1106 END;
1107
1108 IF a_organization_id.COUNT > 0 THEN
1109 BEGIN
1110
1111 --==== Put this information into LOG file ====
1112 LOG_MESSAGE('========================================');
1113 LOG_MESSAGE('Supplier Item is being cross referenced with multiple Master Items');
1114 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_ITEM_SUPP_1');
1115 LOG_MESSAGE(FND_MESSAGE.GET);
1116
1117 FOR i in 1..a_organization_id.COUNT LOOP
1118 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_ITEM_SUPP_2');
1119 FND_MESSAGE.SET_TOKEN('SUPPLIER_ID', a_supplier_id(i));
1120 FND_MESSAGE.SET_TOKEN('SUPPLIER_SITE_ID', a_supplier_site_id(i));
1121 FND_MESSAGE.SET_TOKEN('SUPPLIER_ITEM_NAME', a_item_name(i));
1122 LOG_MESSAGE(FND_MESSAGE.GET);
1123 END LOOP;
1124
1125
1126
1127 FORALL i IN 1..a_organization_id.COUNT
1128 UPDATE msc_st_item_suppliers msis
1129 set process_flag = MSC_CL_COLLECTION.G_ERROR
1130 where msis.organization_id = a_organization_id(i)
1131 and msis.supplier_id = a_supplier_id(i)
1132 and nvl(msis.supplier_site_id, -99) = nvl(a_supplier_site_id (i), -99)
1133 and msis.item_name = a_item_name(i);
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137
1138 LOG_MESSAGE('Error while updating invalid records in msc_st_item_suppliers');
1139 LOG_MESSAGE(SQLERRM);
1140 return FALSE;
1141 END;
1142 END IF;
1143
1144 LOG_MESSAGE('Validation of msc_item_suppliers finished successfully');
1145
1146 /* Error out records in msc_st_item_customers if
1147 same Customer Item belonging to same Customer Site is pointing
1148 to multiple master items.
1149 */
1150
1151 BEGIN
1152
1153 OPEN validateItemCustomers;
1154
1155 FETCH validateItemCustomers BULK COLLECT INTO
1156 a_customer_id ,
1157 a_customer_site_id ,
1158 a1_item_name,
1159 a1_count ;
1160 CLOSE validateItemCustomers;
1161
1162 EXCEPTION WHEN OTHERS THEN
1163 LOG_MESSAGE('Error while fetching records from validateItemCustomers cursor');
1164 LOG_MESSAGE(SQLERRM);
1165 return FALSE;
1166 END;
1167
1168
1169 IF a_customer_id.COUNT > 0 THEN
1170 BEGIN
1171
1172 --==== Put this information into LOG file ====
1173 LOG_MESSAGE('========================================');
1174
1175 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_ITEM_CUST_1');
1176 LOG_MESSAGE(FND_MESSAGE.GET);
1177
1178 FOR i in 1..a_company_id.COUNT LOOP
1179
1180 FND_MESSAGE.SET_NAME('MSC', 'MSC_X_ITEM_CUST_2');
1181 FND_MESSAGE.SET_TOKEN('CUSTOMER_ID', a_customer_id(i));
1182 FND_MESSAGE.SET_TOKEN('SUPPLIER_SITE_ID', a_customer_site_id(i));
1183 FND_MESSAGE.SET_TOKEN('SUPPLIER_ITEM_NAME', a1_item_name(i));
1184 LOG_MESSAGE(FND_MESSAGE.GET);
1185
1186 END LOOP;
1187
1188 FORALL i IN 1..a_company_id.COUNT
1189 UPDATE msc_st_item_customers msic
1190 set process_flag = MSC_CL_COLLECTION.G_ERROR
1191 where msic.customer_id = a_customer_id(i)
1192 and nvl(msic.customer_site_id, -99) = nvl(a_customer_site_id (i), -99)
1193 and msic.customer_item_name = a1_item_name(i);
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 LOG_MESSAGE('Error while validating Item Customers');
1198 LOG_MESSAGE(SQLERRM);
1199 return FALSE;
1200 END;
1201 END IF;
1202 LOG_MESSAGE('Validation of msc_item_customers finished');
1203
1204 /* Data validation ends */
1205
1206
1207 -- ==== Data Cleanup Starts here ====
1208
1209 /*
1210 If MSC:Configuration = 'APS+SCE' and
1211 Source = 'ERP'
1212 - Update Company_name and company_id column for TPs whose
1213 sites are modeled as Inventory Organizaion
1214 */
1215
1216 /* Create Bi-Directional records */
1217
1218 OPEN biDirectional;
1219
1220 FETCH biDirectional BULK COLLECT INTO
1221 a_sr_instance_id ,
1222 a_sr_company_id ,
1223 a_sr_tp_id ,
1224 a_company_name,
1225 a_partner_type ,
1226 a_partner_name ;
1227 CLOSE biDirectional;
1228
1229 LOG_MESSAGE('Number of Bi-Directional records : '||a_sr_instance_id.COUNT);
1230
1231 IF a_sr_instance_id.COUNT > 0 THEN
1232 BEGIN
1233
1234 FORALL i IN 1..a_sr_instance_id.COUNT
1235 INSERT INTO MSC_ST_TRADING_PARTNERS
1236 ( SR_INSTANCE_ID,
1237 COMPANY_ID,
1238 COMPANY_NAME,
1239 SR_TP_ID,
1240 PARTNER_NAME,
1241 PARTNER_TYPE
1242 )
1243 VALUES
1244 ( a_sr_instance_id(i),
1245 a_sr_company_id(i),
1246 a_company_name(i),
1247 a_sr_tp_id(i),
1248 a_partner_name(i),
1249 a_partner_type(i)
1250 );
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 LOG_MESSAGE('Error while creating Bi-Directional records');
1255
1256 LOG_MESSAGE('========================================');
1257 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1258 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CLEANSE_DATA_FOR_SCE');
1259 LOG_MESSAGE(FND_MESSAGE.GET);
1260
1261 LOG_MESSAGE(SQLERRM);
1262 return FALSE;
1263 END;
1264 END IF;
1265
1266
1267 -- ==== Data Clean up ends here ====
1268
1269 return TRUE;
1270
1271 END CLEANSE_DATA_FOR_SCE;
1272
1273 -- ==== Data Clean up for Trading Partner Items ====
1274
1275 PROCEDURE CLEANSE_TP_ITEMS(p_instance_id NUMBER) IS
1276
1277 CURSOR supItemCompanies IS
1278 select ROWIDTOCHAR(msis.rowid),
1279 mcil.company_id,
1280 -99
1281 from msc_st_item_suppliers msis,
1282 msc_company_id_lid mcil
1283 where msis.supplier_id = mcil.sr_company_id
1284 and msis.sr_instance_id = mcil.sr_instance_id
1285 and mcil.partner_type = G_SUPPLIER
1286 and msis.sr_instance_id = p_instance_id
1287 and msis.supplier_site_id is null
1288 and msis.item_name is not null
1289 UNION
1290 select ROWIDTOCHAR(msis.rowid),
1291 mcil.company_id,
1292 mcsil.company_site_id
1293 from msc_st_item_suppliers msis,
1294 msc_company_id_lid mcil,
1295 msc_company_site_id_lid mcsil
1296 where msis.supplier_id = mcil.sr_company_id
1297 and msis.sr_instance_id = mcil.sr_instance_id
1298 and mcil.partner_type = G_SUPPLIER
1299 and msis.sr_instance_id = p_instance_id
1300 and msis.supplier_site_id = mcsil.sr_company_site_id
1301 and msis.sr_instance_id = mcsil.sr_instance_id
1302 and msis.supplier_id = mcsil.sr_company_id
1303 and mcsil.partner_type = G_SUPPLIER
1304 and msis.supplier_site_id is not null
1305 and msis.item_name is not null;
1306
1307 a_rowid rowids;
1308 a_company_id number_arr;
1309 a_company_site_id number_arr;
1310
1311 CURSOR custItemCompanies IS
1312 select ROWIDTOCHAR(msic.rowid),
1313 mcil.company_id,
1314 -99
1315 from msc_st_item_customers msic,
1316 msc_company_id_lid mcil
1317 where msic.customer_id = mcil.sr_company_id
1318 and msic.sr_instance_id = mcil.sr_instance_id
1319 and mcil.partner_type = G_CUSTOMER
1320 and msic.sr_instance_id = p_instance_id
1321 and msic.customer_site_id is null
1322 and msic.customer_item_name is not null
1323 UNION
1324 select ROWIDTOCHAR(msic.rowid),
1325 mcil.company_id,
1326 mcsil.company_site_id
1327 from msc_st_item_customers msic,
1328 msc_company_id_lid mcil,
1329 msc_company_site_id_lid mcsil
1330 where msic.customer_id = mcil.sr_company_id
1331 and msic.sr_instance_id = mcil.sr_instance_id
1332 and mcil.partner_type = G_CUSTOMER
1333 and msic.sr_instance_id = p_instance_id
1334 and msic.customer_site_id = mcsil.sr_company_site_id
1335 and msic.sr_instance_id = mcsil.sr_instance_id
1336 and msic.customer_id = mcsil.sr_company_id
1337 and mcsil.partner_type = G_CUSTOMER
1338 and msic.customer_site_id is not null
1339 and msic.customer_item_name is not null;
1340
1341 BEGIN
1342
1343 LOG_MESSAGE('INSIDE CLEANSE_TP_ITEMS');
1344 /*
1345 OPEN supItemCompanies;
1346
1347 FETCH supItemCompanies BULK COLLECT INTO
1348 a_rowid,
1349 a_company_id,
1350 a_company_site_id;
1351 CLOSE supItemCompanies;
1352
1353 IF a_rowid.COUNT > 0 THEN
1354 BEGIN
1355 FORALL i IN 1..a_rowid.COUNT
1356 UPDATE msc_st_item_suppliers mis
1357 set supplier_company_id = a_company_id(i),
1358 supplier_company_site_id = decode(a_company_site_id(i), -99, null, a_company_site_id(i))
1359 where mis.rowid = CHARTOROWID(a_rowid(i));
1360 EXCEPTION WHEN OTHERS THEN
1361 LOG_MESSAGE('Error while updating msc_item_suppliers with company_id and company_site_id');
1362 LOG_MESSAGE('========================================');
1363 FND_MESSAGE.SET_NAME('MSC', 'MSC_CLEAN_TP_ITEM_ERR');
1364 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CLEANSE_TP_ITEMS');
1365 LOG_MESSAGE(FND_MESSAGE.GET);
1366 END;
1367 END IF;
1368 */
1369
1370 /*
1371 Add a code to address Customer Items.
1372 */
1373
1374 /*
1375 OPEN custItemCompanies;
1376
1377 FETCH custItemCompanies BULK COLLECT INTO
1378 a_rowid,
1379 a_company_id,
1380 a_company_site_id;
1381 CLOSE custItemCompanies;
1382
1383 IF a_rowid.COUNT > 0 THEN
1384 BEGIN
1385 FORALL i IN 1..a_rowid.COUNT
1386 UPDATE msc_st_item_customers msic
1387 set company_id = a_company_id(i),
1388 company_site_id = decode(a_company_site_id(i), -99, null, a_company_site_id(i))
1389 where msic.rowid = CHARTOROWID(a_rowid(i));
1390 EXCEPTION WHEN OTHERS THEN
1391 LOG_MESSAGE('Error while updating msc_item_customers with company_id and company_site_id');
1392 LOG_MESSAGE('========================================');
1393 FND_MESSAGE.SET_NAME('MSC', 'MSC_CLEAN_TP_ITEM_ERR');
1394 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'CLEANSE_TP_ITEMS');
1395 LOG_MESSAGE(FND_MESSAGE.GET);
1396 END;
1397 END IF;
1398
1399
1400 LOG_MESSAGE('CLEANSE_TP_ITEMS SUCCESSFUL');
1401 */
1402
1403 END CLEANSE_TP_ITEMS;
1404
1405 -- ==== COLLECT_COMPANY_SITES ====
1406 /*
1407 This procedure collects following information
1408
1409 - Planning Sites from msc_st_trading_partners into msc_company_sites
1410
1411 - Non Planning Sites from msc_st_trading_partners into msc_company_sites
1412
1413 - Customer / Supplier non planning Sites from msc_st_trading_partner_sites
1414 into msc_company_sites
1415 */
1416 PROCEDURE COLLECT_COMPANY_SITES IS
1417
1418 -- Planning / Non Planning Sites from msc_st_trading_partners
1419 CURSOR collCompanySites IS
1420 SELECT
1421 mcsil.COMPANY_SITE_ID,
1422 nvl(mtp.PLANNING_ENABLED_FLAG, 'Y')
1423 from msc_st_trading_partners mtp,
1424 msc_company_site_id_lid mcsil
1425 where nvl(mtp.company_id, -1) = mcsil.sr_company_id
1426 and mtp.sr_instance_id = mcsil.sr_instance_id
1427 and mtp.sr_instance_id = v_sr_instance_id
1428 and mtp.sr_tp_id = mcsil.sr_company_site_id
1429 and mtp.partner_type = mcsil.partner_type
1430 and mtp.partner_type = G_ORGANIZATION;
1431
1432 -- Collect the Trading Partner Sites.
1433 CURSOR collCompanyTpSites IS
1434 SELECT distinct
1435 mcsil.COMPANY_SITE_ID,
1436 mstps.LOCATION,
1437 mstps.LONGITUDE,
1438 mstps.LATITUDE,
1439 mstps.ADDRESS1,
1440 mstps.ADDRESS2,
1441 mstps.ADDRESS3,
1442 mstps.ADDRESS4,
1443 mstps.country,
1444 mstps.state,
1445 mstps.city,
1446 mstps.county,
1447 mstps.province,
1448 mstps.postal_code
1449 FROM MSC_ST_TRADING_PARTNER_SITES mstps,
1450 msc_company_site_id_lid mcsil
1451 WHERE mcsil.SR_COMPANY_ID = nvl(mstps.sr_tp_id, -1)
1452 AND mcsil.SR_COMPANY_SITE_ID= mstps.SR_TP_SITE_ID
1453 AND mcsil.SR_INSTANCE_ID= mstps.SR_INSTANCE_ID
1454 AND mcsil.partner_type = mstps.partner_type
1455 AND mstps.SR_INSTANCE_ID= v_sr_instance_id
1456 AND mstps.partner_type IN (G_SUPPLIER, G_CUSTOMER)
1457 ORDER BY
1458 mcsil.COMPANY_SITE_ID;
1459
1460 --=======================================================================================
1461 -- Following cursor will be used to collect location and address information
1462 -- for planning Organization. This information is stored in msc_st_trading_partner_sites
1463 -- table.
1464 --=======================================================================================
1465 CURSOR collOrgSiteAttrib IS
1466 SELECT
1467 mcsil.COMPANY_SITE_ID,
1468 mtps.location,
1469 mtps.longitude,
1470 mtps.latitude,
1471 mtps.ADDRESS1,
1472 mtps.ADDRESS2,
1473 mtps.ADDRESS3,
1474 mtps.ADDRESS4,
1475 mtps.country,
1476 mtps.state,
1477 mtps.city,
1478 mtps.county,
1479 mtps.province,
1480 mtps.postal_code
1481 from msc_st_trading_partner_sites mtps,
1482 msc_company_site_id_lid mcsil
1483 where nvl(mtps.company_id, -1) = mcsil.sr_company_id
1484 and mtps.sr_instance_id = mcsil.sr_instance_id
1485 and mtps.sr_instance_id = v_sr_instance_id
1486 and mtps.sr_tp_id = mcsil.sr_company_site_id
1487 and mtps.partner_type = mcsil.partner_type
1488 and mtps.partner_type = G_ORGANIZATION;
1489
1490 a_COMPANY_SITE_ID number_arr;
1491 -- a_DELETED_FLAG number_arr;
1492 -- a_REFRESH_ID number_arr;
1493 -- a_OPERATING_UNIT number_arr;
1494 -- a_DISABLE_DATE date_arr;
1495 -- a_MASTER_ORGANIZATION number_arr;
1496 -- a_WEIGHT_UOM char3_arr;
1497 -- a_MAXIMUM_WEIGHT number_arr;
1498 -- a_VOLUME_UOM char3_arr;
1499 -- a_MAXIMUM_VOLUME number_arr;
1500 a_PLANNING_ENABLED_FLAG char_arr;
1501 -- a_CALENDAR_CODE calendarCodes;
1502 -- a_CALENDAR_EXCEPTION_SET_ID number_arr;
1503 -- a_PROJECT_REFERENCE_ENABLED number_arr;
1504 -- a_PROJECT_CONTROL_LEVEL number_arr;
1505 -- a_DEMAND_LATENESS_COST number_arr;
1506 -- a_SUPPLIER_CAP_OVERUTIL_COST number_arr;
1507 -- a_RESOURCE_CAP_OVERUTIL_COST number_arr;
1508 -- a_DEFAULT_DEMAND_CLASS defaultDemandClasses;
1509 -- a_TRANSPORT_CAP_OVER_UTIL_COST number_arr;
1510 -- a_USE_PHANTOM_ROUTINGS number_arr;
1511 -- a_INHERIT_PHANTOM_OP_SEQ number_arr;
1512 -- a_DEFAULT_ATP_RULE_ID number_arr;
1513 -- a_MATERIAL_ACCOUNT number_arr;
1514 -- a_EXPENSE_ACCOUNT number_arr;
1515 -- a_CUSTOMER_CLASS_CODE customerClassCodes;
1516 -- a_SERVICE_LEVEL number_arr;
1517 -- a_ORGANIZATION_TYPE number_arr;
1518 a_LOCATION locationCodes;
1519 a_LONGITUDE number_arr;
1520 a_LATITUDE number_arr;
1521
1522 a_ADDRESS1 addressLines;
1523 a_ADDRESS2 addressLines;
1524 a_ADDRESS3 addressLines;
1525 a_ADDRESS4 addressLines;
1526 a_country countries;
1527 a_state states;
1528 a_city cities;
1529 a_postal_code postalCodes;
1530 a_county counties;
1531 a_province provinces;
1532
1533 BEGIN
1534 LOG_MESSAGE('Inside collect_company_sites');
1535
1536 --==================================================================================
1537 -- Following SQL will update planning_enabled flag in msc_company_sites. This will
1538 -- also take care of situation where previous non planning site has become planning
1539 -- organization.
1540 --==================================================================================
1541 OPEN collCompanySites;
1542 FETCH collCompanySites BULK COLLECT INTO
1543 a_COMPANY_SITE_ID,
1544 a_PLANNING_ENABLED_FLAG;
1545 CLOSE collCompanySites;
1546
1547
1548 IF a_company_site_id.COUNT > 0 THEN
1549 BEGIN
1550 FORALL i IN 1..a_company_site_id.COUNT
1551 UPDATE MSC_COMPANY_SITES
1552 SET PLANNING_ENABLED = a_PLANNING_ENABLED_FLAG(i)
1553 WHERE COMPANY_SITE_ID = a_COMPANY_SITE_ID(i);
1554 EXCEPTION WHEN OTHERS THEN
1555 LOG_MESSAGE('Error while collecting Planning/ Non Planning Company Sites');
1556 END;
1557 END IF;
1558
1559
1560 --==================================================================================
1561 -- Collect Location / Address information for non Planning sites. Mainly sites of
1562 -- the trading partners (Customer / Supplier). If the collections happen from
1563 -- marketplace Exchange then we might have Company's own non planning sites in
1564 -- this CURSOR.
1565 --==================================================================================
1566 OPEN collCompanyTpSites;
1567
1568 FETCH collCompanyTpSites BULK COLLECT INTO
1569 a_COMPANY_SITE_ID,
1570 a_LOCATION,
1571 a_LONGITUDE,
1572 a_LATITUDE,
1573 a_ADDRESS1,
1574 a_ADDRESS2,
1575 a_ADDRESS3,
1576 a_ADDRESS4,
1577 a_country,
1578 a_state,
1579 a_city,
1580 a_county,
1581 a_province,
1582 a_postal_code;
1583 CLOSE collCompanyTpSites;
1584
1585 IF a_COMPANY_SITE_ID.COUNT > 0 THEN
1586 BEGIN
1587 FORALL i IN 1..a_COMPANY_SITE_ID.COUNT
1588 UPDATE MSC_COMPANY_SITES
1589 set LOCATION = a_LOCATION(i),
1590 LONGITUDE = a_LONGITUDE(i),
1591 LATITUDE = a_LATITUDE(i),
1592 ADDRESS1 = a_ADDRESS1(i),
1593 ADDRESS2 = a_ADDRESS2(i),
1594 ADDRESS3 = a_ADDRESS3(i),
1595 ADDRESS4 = a_ADDRESS4(i),
1596 country = a_country(i),
1597 state = a_state(i),
1598 city = a_city(i),
1599 county = a_county(i),
1600 province = a_province(i),
1601 postal_code = a_postal_code(i)
1602 where company_site_id = a_company_site_id(i);
1603 EXCEPTION WHEN OTHERS THEN
1604 LOG_MESSAGE('Error while Collecting Company Sites for TPs');
1605 END;
1606 END IF;
1607
1608
1609 --==================================================================================
1610 -- Collect Location / Address information for planning Orgs.
1611 --==================================================================================
1612
1613 BEGIN
1614
1615 OPEN collOrgSiteAttrib;
1616
1617 FETCH collOrgSiteAttrib BULK COLLECT INTO
1618 a_COMPANY_SITE_ID,
1619 a_LOCATION,
1620 a_LONGITUDE,
1621 a_LATITUDE,
1622 a_ADDRESS1,
1623 a_ADDRESS2,
1624 a_ADDRESS3,
1625 a_ADDRESS4,
1626 a_country,
1627 a_state,
1628 a_city,
1629 a_county,
1630 a_province,
1631 a_postal_code;
1632 CLOSE collOrgSiteAttrib;
1633
1634 EXCEPTION WHEN OTHERS THEN
1635 LOG_MESSAGE('Error while opening and hetching from collOrgSiteAttrib');
1636 LOG_MESSAGE(SQLERRM);
1637 END;
1638
1639 IF a_COMPANY_SITE_ID.COUNT > 0 THEN
1640 BEGIN
1641 FORALL i IN 1..a_COMPANY_SITE_ID.COUNT
1642 UPDATE MSC_COMPANY_SITES
1643 set LOCATION = a_LOCATION(i),
1644 LONGITUDE = a_LONGITUDE(i),
1645 LATITUDE = a_LATITUDE(i),
1646 ADDRESS1 = a_ADDRESS1(i),
1647 ADDRESS2 = a_ADDRESS2(i),
1648 ADDRESS3 = a_ADDRESS3(i),
1649 ADDRESS4 = a_ADDRESS4(i),
1650 country = a_country(i),
1651 state = a_state(i),
1652 city = a_city(i),
1653 county = a_county(i),
1654 province = a_province(i),
1655 postal_code = a_postal_code(i)
1656 where company_site_id = a_company_site_id(i);
1657 EXCEPTION WHEN OTHERS THEN
1658 LOG_MESSAGE('Error while Collecting Location / Address information for Planning Organizations.');
1659 LOG_MESSAGE(SQLERRM);
1660 END;
1661 END IF;
1662
1663 END COLLECT_COMPANY_SITES;
1664
1665
1666
1667 PROCEDURE POPULATE_TP_MAP_TABLE(p_instance_id NUMBER) IS
1668
1669 -- ==== Cursor for 'Trading Partner' maps
1670 cursor newTpMap is
1671 select DISTINCT mtp.partner_id,
1672 mcr.relationship_id
1673 from msc_trading_partners mtp,
1674 msc_tp_id_lid mtil,
1675 msc_company_id_lid mcil,
1676 msc_company_relationships mcr
1677 where mtp.partner_id = mtil.tp_id
1678 and mtil.sr_instance_id = p_instance_id
1679 and mtil.sr_tp_id = mcil.sr_company_id
1680 and mtil.sr_instance_id = mcil.sr_instance_id
1681 and mtil.partner_type = mcil.partner_type
1682 and mcil.company_id = mcr.object_id
1683 /* Perf changes start */
1684 /* Removed nvl(mtp.company_id,...) */
1685 and mtp.company_id IS NULL
1686 and mcr.subject_id = MSC_CL_COLLECTION.G_MY_COMPANY_ID
1687 /* and nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID) = mcr.subject_id */
1688 /* Perf changes end */
1689 and decode(mtp.partner_type, G_SUPPLIER, G_CUSTOMER, G_CUSTOMER, G_SUPPLIER)
1690 = mcr.relationship_type
1691 -- Make sure that only trading Partner records are considered.
1692 and mtp.partner_type IN (G_SUPPLIER, G_CUSTOMER)
1693 /* Perf changes */
1694 /* Removed Minus and added following code lines for performance fix */
1695 and not exists ( select 1
1696 from msc_trading_partner_maps mtpm
1697 where mtpm.tp_key = mtp.partner_id
1698 and mtpm.company_key = mcr.relationship_id
1699 and mtpm.map_type = 1);
1700 /* MINUS
1701 select tp_key,
1702 company_key
1703 from msc_trading_partner_maps
1704 where map_type = 1; */
1705
1706 a_tp_id number_arr;
1707 a_company_id number_arr;
1708
1709 -- ==== Cursor for 'Organization' Maps
1710 cursor newOrgMap is
1711 select DISTINCT mtp.partner_id,
1712 mcs.company_site_id
1713 from msc_company_sites mcs,
1714 msc_company_site_id_lid mcsil,
1715 msc_trading_partners mtp
1716 where mcs.company_site_id = mcsil.company_site_id
1717 -- Process for the current instance only
1718 and mcsil.sr_instance_id = p_instance_id
1719 -- Join for Organization
1720 and mcsil.sr_instance_id = mtp.sr_instance_id
1721 and mcsil.sr_company_site_id = mtp.sr_tp_id
1722 and mcsil.partner_type = mtp.partner_type
1723 and mtp.partner_type = G_ORGANIZATION
1724 -- Join for company_id
1725 /* Perf changes */
1726 /* and mcs.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID) */
1727 and mcs.company_id = MSC_CL_COLLECTION.G_MY_COMPANY_ID
1728 and mtp.company_id IS NULL
1729 and not exists ( select 1
1730 from msc_trading_partner_maps mtpm
1731 where mtpm.tp_key = mtp.partner_id
1732 and mtpm.company_key
1733 = mcs.company_site_id
1734 and mtpm.map_type = 2);
1735 /*
1736 MINUS
1737 select tp_key,
1738 company_key
1739 from msc_trading_partner_maps
1740 where map_type = 2; */
1741
1742 a_partner_id number_arr;
1743 a_company_site_id number_arr;
1744
1745 -- ==== Cursor for TP Sites Maps.
1746 cursor newTpSIteMap is
1747 select DISTINCT mtsil.tp_site_id,
1748 mcs.company_site_id
1749 from msc_company_sites mcs,
1750 msc_company_site_id_lid mcsil,
1751 msc_tp_site_id_lid mtsil
1752 where mcs.company_site_id = mcsil.company_site_id
1753 and mcsil.sr_instance_id = p_instance_id
1754 and mcsil.sr_instance_id = mtsil.sr_instance_id
1755 and mcsil.partner_type = mtsil.partner_type
1756 and mtsil.sr_company_id = -1
1757 and mcsil.sr_company_site_id = mtsil.sr_tp_site_id
1758 and not exists (select 1
1759 from msc_trading_partner_maps mtpm
1760 where mtpm.tp_key = mtsil.tp_site_id
1761 and mtpm.company_key = mcs.company_site_id
1762 and mtpm.map_type = 3);
1763
1764
1765 /* MINUS
1766 select tp_key,
1767 company_key
1768 from msc_trading_partner_maps
1769 where map_type = 3; */
1770
1771 a_tp_site_id number_arr;
1772 a1_company_site_id number_arr;
1773
1774 BEGIN
1775 /* Fetch 'Trading Partner' Map records
1776 Assuming Map Types as follows
1777 1 - Trading Partners
1778 2 - Planning Organizations
1779 3 - Trading Partner Sites
1780 */
1781
1782 OPEN newTpMap;
1783
1784 FETCH newTpMap BULK COLLECT INTO
1785 a_tp_id,
1786 a_company_id;
1787
1788 close newTpMap;
1789
1790 IF a_tp_id.COUNT > 0 THEN
1791 BEGIN
1792 FORALL i IN 1..a_tp_id.COUNT
1793 insert into msc_trading_partner_maps
1794 ( map_id,
1795 map_type,
1796 tp_key,
1797 COMPANY_KEY ,
1798 CREATION_DATE,
1799 CREATED_BY ,
1800 LAST_UPDATE_DATE ,
1801 LAST_UPDATED_BY ,
1802 LAST_UPDATE_LOGIN
1803 ) values
1804 ( msc_tp_maps_s.nextval,
1805 1,
1806 a_tp_id(i),
1807 a_company_id(i),
1808 sysdate,
1809 -1,
1810 sysdate,
1811 -1,
1812 -1
1813 );
1814
1815 COMMIT;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 LOG_MESSAGE('Error while populating TP map');
1820 LOG_MESSAGE(SQLERRM);
1821 RETURN;
1822 END;
1823 END IF;
1824
1825
1826 /* Now Populate the map records for Planning Organization */
1827
1828 OPEN newOrgMap;
1829
1830 FETCH newOrgMap BULK COLLECT INTO
1831 a_partner_id,
1832 a_company_site_id;
1833
1834 close newOrgMap;
1835
1836 IF a_partner_id.COUNT > 0 THEN
1837 BEGIN
1838 FORALL i IN 1..a_partner_id.COUNT
1839 insert into msc_trading_partner_maps
1840 ( map_id,
1841 map_type,
1842 tp_key,
1843 COMPANY_KEY ,
1844 CREATION_DATE,
1845 CREATED_BY ,
1846 LAST_UPDATE_DATE ,
1847 LAST_UPDATED_BY ,
1848 LAST_UPDATE_LOGIN
1849 )
1850 values
1851 ( msc_tp_maps_s.nextval,
1852 2,
1853 a_partner_id(i),
1854 a_company_site_id(i),
1855 sysdate,
1856 -1,
1857 sysdate,
1858 -1,
1859 -1
1860 );
1861
1862 COMMIT;
1863
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 LOG_MESSAGE('Error while populating Planning Org map');
1867 LOG_MESSAGE(SQLERRM);
1868 RETURN;
1869 END;
1870 END IF;
1871
1872 /* Now Populate the map records for Trading Partner Organization */
1873
1874 BEGIN
1875 OPEN newTpSIteMap;
1876 FETCH newTpSIteMap BULK COLLECT INTO
1877 a_tp_site_id,
1878 a1_company_site_id;
1879
1880 close newTpSIteMap;
1881 EXCEPTION WHEN OTHERS THEN
1882 LOG_MESSAGE('Error while fetching from newTpSIteMap');
1883 LOG_MESSAGE(SQLERRM);
1884 END;
1885
1886 LOG_MESSAGE('Total Company Site Maps : '||a_tp_site_id.COUNT);
1887
1888 IF a_tp_site_id.COUNT > 0 THEN
1889 BEGIN
1890 FORALL i IN 1..a_tp_site_id.COUNT
1891 insert into msc_trading_partner_maps
1892 ( map_id,
1893 map_type,
1894 tp_key,
1895 COMPANY_KEY ,
1896 CREATION_DATE,
1897 CREATED_BY ,
1898 LAST_UPDATE_DATE ,
1899 LAST_UPDATED_BY ,
1900 LAST_UPDATE_LOGIN
1901 )
1902 values
1903 ( msc_tp_maps_s.nextval,
1904 3,
1905 a_tp_site_id(i),
1906 a1_company_site_id(i),
1907 sysdate,
1908 -1,
1909 sysdate,
1910 -1,
1911 -1
1912 );
1913
1914 COMMIT;
1915
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 LOG_MESSAGE('Error while populating Trading Partner Site Maps');
1919 LOG_MESSAGE(SQLERRM);
1920 RETURN;
1921 END;
1922 END IF;
1923 --Bug 5155944: Analysing the table to improve performance
1924 msc_analyse_tables_pk.analyse_table( 'MSC_TRADING_PARTNER_MAPS');
1925
1926 END POPULATE_TP_MAP_TABLE;
1927
1928
1929 --==== ODS Load for msc_item_customers ====
1930 PROCEDURE LOAD_ITEM_CUSTOMERS(p_instance_id NUMBER) IS
1931
1932 CURSOR itemCustomers IS
1933 select t1.inventory_item_id,
1934 mtil.tp_id,
1935 mtsil.tp_site_id,
1936 mic.customer_item_name,
1937 mic.description,
1938 mic.lead_time,
1939 mic.uom_code,
1940 mic.list_price,
1941 mic.planner_code,
1942 mic.refresh_number
1943 from msc_st_item_customers mic,
1944 msc_tp_id_lid mtil,
1945 msc_tp_site_id_lid mtsil,
1946 msc_item_id_lid t1
1947 where t1.SR_INVENTORY_ITEM_ID = mic.inventory_item_id
1948 AND t1.sr_instance_id= mic.sr_instance_id
1949 and mic.customer_id = mtil.sr_tp_id
1950 and nvl(mic.company_id, -1) = nvl(mtil.sr_company_id, -1)
1951 and mic.sr_instance_id = mtil.sr_instance_id
1952 and mic.sr_instance_id = p_instance_id
1953 and mtil.partner_type = G_CUSTOMER
1954 and mic.customer_site_id = mtsil.sr_tp_site_id (+)
1955 and mic.sr_instance_id = mtsil.sr_instance_id (+)
1956 and nvl(mic.company_id, -1) = nvl(mtsil.sr_company_id, -1)
1957 and mtsil.partner_type (+) = G_CUSTOMER;
1958
1959 a_inventory_item_id number_arr;
1960 a_tp_id number_arr;
1961 a_tp_site_id number_arr;
1962 a_item_name items;
1963 a_description descriptions;
1964 a_lead_time number_arr;
1965 a_uom_code uomCodes;
1966 a_list_price number_arr;
1967 a_planner_code plannerCodes;
1968 a_refresh_number number_arr;
1969
1970 /* Variables initiated for insert operation */
1971 a_ins_inventory_item_id number_arr := number_arr();
1972 a_ins_tp_id number_arr := number_arr();
1973 a_ins_tp_site_id number_arr := number_arr();
1974 a_ins_item_name items := items();
1975 a_ins_description descriptions := descriptions();
1976 a_ins_lead_time number_arr := number_arr();
1977 a_ins_uom_code uomCodes := uomCodes();
1978 a_ins_list_price number_arr := number_arr();
1979 a_ins_planner_code plannerCodes := plannerCodes();
1980 a_ins_refresh_number number_arr := number_arr();
1981 a_ins_count number_arr := number_arr();
1982 BEGIN
1983
1984 OPEN itemCustomers;
1985
1986 FETCH itemCustomers BULK COLLECT INTO
1987 a_inventory_item_id,
1988 a_tp_id,
1989 a_tp_site_id,
1990 a_item_name,
1991 a_description,
1992 a_lead_time,
1993 a_uom_code,
1994 a_list_price,
1995 a_planner_code,
1996 a_refresh_number;
1997
1998 CLOSE itemCustomers;
1999
2000 LOG_MESSAGE('Total customer Item cross references :'||a_inventory_item_id.COUNT);
2001
2002 IF a_inventory_item_id.COUNT > 0 THEN
2003 BEGIN
2004
2005 /* Update the record if it already exists */
2006 BEGIN
2007
2008 FORALL i IN 1..a_inventory_item_id.COUNT
2009 UPDATE MSC_ITEM_CUSTOMERS mic
2010 set lead_time = a_lead_time(i),
2011 uom_code = a_uom_code(i),
2012 list_price = a_list_price(i),
2013 refresh_number = a_refresh_number(i),
2014 last_update_date = sysdate,
2015 last_updated_by = -1
2016 where mic.plan_id = -1
2017 and inventory_item_id = a_inventory_item_id(i)
2018 and customer_id = a_tp_id(i)
2019 and nvl(customer_site_id, -99) = nvl(a_tp_site_id(i), -99);
2020
2021 EXCEPTION WHEN OTHERS THEN
2022 LOG_MESSAGE('ERROR while updating msc_item_customers');
2023 LOG_MESSAGE(SQLERRM);
2024 END;
2025
2026 /* Build the collection objects for insertion */
2027 FOR i IN 1..a_inventory_item_id.COUNT LOOP
2028 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
2029
2030 /* Extend the Collection objects */
2031 a_ins_count.EXTEND;
2032 a_ins_inventory_item_id.EXTEND;
2033 a_ins_tp_id.EXTEND;
2034 a_ins_tp_site_id.EXTEND;
2035 a_ins_item_name.EXTEND;
2036 a_ins_description.EXTEND;
2037 a_ins_lead_time.EXTEND;
2038 a_ins_uom_code.EXTEND;
2039 a_ins_list_price.EXTEND;
2040 a_ins_planner_code.EXTEND;
2041 a_ins_refresh_number.EXTEND;
2042
2043
2044 /* Populate collection objects */
2045 a_ins_count(a_ins_count.COUNT) := i;
2046 a_ins_inventory_item_id(a_ins_count.COUNT) := a_inventory_item_Id(i);
2047 a_ins_tp_id(a_ins_count.COUNT) := a_tp_id(i);
2048 a_ins_tp_site_id(a_ins_count.COUNT) := a_tp_site_id(i);
2049 a_ins_item_name(a_ins_count.COUNT) := a_item_name(i);
2050 a_ins_description(a_ins_count.COUNT) := a_description(i);
2051 a_ins_lead_time(a_ins_count.COUNT) := a_lead_time(i);
2052 a_ins_uom_code(a_ins_count.COUNT) := a_uom_code(i);
2053 a_ins_list_price(a_ins_count.COUNT) := a_list_price(i);
2054 a_ins_planner_code(a_ins_count.COUNT) := a_planner_code(i);
2055 a_ins_refresh_number(a_ins_count.COUNT) := a_refresh_number(i);
2056
2057 END IF; -- (SQL%BULK_ROWCOUNT(i) = 0)
2058 END LOOP;
2059
2060 /* Insert the record if the record does not exist */
2061
2062 IF a_ins_count.COUNT > 0 THEN
2063
2064 FORALL i IN 1..a_ins_count.COUNT
2065 INSERT INTO MSC_ITEM_CUSTOMERS
2066 (PLAN_ID ,
2067 CUSTOMER_ID ,
2068 CUSTOMER_SITE_ID,
2069 INVENTORY_ITEM_ID,
2070 CUSTOMER_ITEM_NAME ,
2071 DESCRIPTION ,
2072 LEAD_TIME ,
2073 UOM_CODE,
2074 LIST_PRICE ,
2075 PLANNER_CODE,
2076 REFRESH_NUMBER ,
2077 LAST_UPDATE_DATE,
2078 LAST_UPDATED_BY,
2079 CREATION_DATE ,
2080 CREATED_BY
2081 )
2082 VALUES
2083 (
2084 -1, -- Plan Id for Collections Plan
2085 a_ins_tp_id(i),
2086 a_ins_tp_site_id(i),
2087 a_ins_inventory_item_id(i),
2088 a_ins_item_name(i),
2089 a_ins_description(i),
2090 a_ins_lead_time(i),
2091 a_ins_uom_code(i),
2092 a_ins_list_price(i),
2093 a_ins_planner_code(i),
2094 a_ins_refresh_number(i),
2095 sysdate,
2096 -1,
2097 sysdate,
2098 -1
2099 );
2100
2101 END IF; -- a_ins_count.COUNT > 0
2102
2103 EXCEPTION WHEN OTHERS THEN
2104 LOG_MESSAGE('Error while inserting into msc_item_customers');
2105 LOG_MESSAGE(SQLERRM);
2106
2107 END;
2108 END IF; -- a_inventory_item_id.COUNT > 0
2109
2110 END LOAD_ITEM_CUSTOMERS;
2111
2112 --======================================================================
2113 -- Collection Pull for User Company Association.
2114 -- This procedure brings User - Company association from ERP.
2115 -- This procedure expectsfollowing set up.
2116 -- 1. Users are defined with same name in Source as well as
2117 -- destination
2118 -- instance.
2119 -- 2. Users are associated with Customer / Supplier in source
2120 -- instance.
2121 -- If the user is not associated with any company then the procedure
2122 -- assumes
2123 -- that the User belongs to OEM company.
2124 --======================================================================
2125
2126 PROCEDURE PULL_USER_COMPANY(p_dblink varchar2,
2127 p_instance_id NUMBER,
2128 p_return_status OUT NOCOPY BOOLEAN,
2129 p_user_company_mode NUMBER) IS
2130
2131 v_sql_stmt VARCHAR2(2048);
2132
2133 BEGIN
2134
2135 --=======================================================
2136 -- Collect the records only if p_user_company_mode is set
2137 -- to User Company Association OR
2138 -- User Company Association and User Collections
2139 --=======================================================
2140
2141 IF ( p_user_company_mode = COMPANY_ONLY OR
2142 p_user_company_mode = USER_AND_COMPANY) THEN
2143
2144 v_sql_stmt :=
2145 ' insert into msc_st_company_users '
2146 ||' ( user_name ,'
2147 ||' sr_company_id ,'
2148 ||' sr_instance_id ,'
2149 ||' partner_type ,'
2150 ||' start_date ,'
2151 ||' end_date ,'
2152 ||' description ,'
2153 ||' email_address ,'
2154 ||' fax ,'
2155 ||' user_guid ,' -- Bug#12863892 fp of 9754732
2156 ||' collection_parameter '
2157 ||' ) '
2158 ||' select distinct'
2159 ||' x.user_name ,'
2160 ||' x.sr_company_id ,'
2161 ||' :v_sr_instance_id ,'
2162 ||' x.partner_type ,'
2163 ||' x.start_date ,'
2164 ||' x.end_date ,'
2165 ||' x.description ,'
2166 ||' x.email_address ,'
2167 ||' x.fax ,'
2168 ||' x.user_guid ,'
2169 ||' :v_collection_parameter '
2170 ||' from MRP_AP_COMPANY_USERS_V'||p_dblink||' x';
2171
2172 COMMIT;
2173 -- Collect the records
2174 BEGIN
2175 EXECUTE IMMEDIATE v_sql_stmt USING p_instance_id, p_user_company_mode;
2176 EXCEPTION WHEN OTHERS THEN
2177 LOG_MESSAGE('Error while pulling msc_company_users data from '||p_instance_id||' (sr_instance_id)');
2178 LOG_MESSAGE(SQLERRM);
2179 p_return_status := FALSE;
2180 END;
2181
2182 END IF; -- IF p_user_company_mode
2183
2184 END PULL_USER_COMPANY;
2185
2186 -- Bug 4872872 : Create password based on value of profile "SignOn Password Length" .
2187
2188 FUNCTION build_passwd RETURN VARCHAR2
2189 IS
2190
2191 passwd_len number := NVL(FND_PROFILE.VALUE('SIGNON_PASSWORD_LENGTH') , 5) ;
2192 msize number := passwd_len -8 ;
2193 p_password varchar2(30) := 'welcome1';
2194 Begin
2195
2196 log_message('Profile "SignOn Password Length" = '||passwd_len);
2197
2198 IF (msize > 0 ) THEN
2199 FOR i in 1..msize LOOP
2200
2201 p_password := p_password||'i+1' ;
2202
2203 END LOOP;
2204 END IF;
2205 --log_message('Password :'||p_password);
2206 RETURN p_password ;
2207
2208 EXCEPTION WHEN OTHERS THEN
2209 RETURN 'welcome1' ;
2210
2211 END build_passwd ;
2212
2213 PROCEDURE LOAD_USER_COMPANY (p_sr_instance_id NUMBER) IS
2214 -- =================
2215 -- Get the new users
2216 -- =================
2217 CURSOR newUsers IS
2218 select distinct user_name,
2219 start_date,
2220 end_date,
2221 description,
2222 email_address,
2223 fax,
2224 user_guid -- Bug#12863892 fp of 9754732
2225 from msc_st_company_users mscu
2226 where mscu.sr_instance_id = p_sr_instance_id
2227 and not exists (select '1'
2228 from fnd_user fu
2229 where fu.user_name = UPPER(mscu.user_name))
2230 -- ================================================================================
2231 -- Pull only if Collection Parameter is set to "Users and User Company Association"
2232 -- ================================================================================
2233 and mscu.collection_parameter = USER_AND_COMPANY;
2234
2235 --===============================================================================================
2236 -- If User-Company association already exists in system by virtue of collection from one source
2237 -- system then we need to reject User-Company association from other source instance for the same
2238 -- user name.
2239 --===============================================================================================
2240
2241 CURSOR invalidUsers IS
2242 select distinct mscu.user_name user_name
2243 from msc_st_company_users mscu,
2244 fnd_user fu
2245 where UPPER(mscu.user_name) = fu.user_name
2246 and mscu.sr_instance_id = p_sr_instance_id
2247 and exists (select '1'
2248 from msc_company_users mcu
2249 where mcu.user_id = fu.user_id
2250 and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
2251 );
2252
2253 CURSOR validUsersUpdate IS
2254 select fu.user_name user_name,
2255 mscu.sr_instance_id sr_instance_id,
2256 mscu.start_date start_date,
2257 mscu.end_date end_date,
2258 mscu.description description,
2259 mscu.email_address email_address,
2260 mscu.fax fax
2261 from msc_st_company_users mscu,
2262 fnd_user fu,
2263 msc_company_id_lid mcil
2264 where mscu.sr_instance_id = p_sr_instance_id
2265 and UPPER(mscu.user_name) = fu.user_name
2266 and mscu.sr_company_id = mcil.sr_company_id
2267 and mscu.sr_instance_id = mcil.sr_instance_id
2268 and mscu.partner_type = mcil.partner_type
2269 and not exists (select '1'
2270 from msc_company_users mcu
2271 where mcu.user_id = fu.user_id
2272 and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
2273 )
2274 -- ==========================================================
2275 -- Get Users for updation only if the collection_parameter is
2276 -- "Users and User Company Association"
2277 -- ==========================================================
2278 and mscu.collection_parameter = USER_AND_COMPANY
2279 MINUS
2280 select fu.user_name user_name,
2281 mcu.sr_instance_id sr_instance_id,
2282 fu.start_date start_date,
2283 fu.end_date end_date,
2284 fu.description description,
2285 fu.email_address email_address,
2286 fu.fax fax
2287 from msc_company_users mcu,
2288 fnd_user fu
2289 where mcu.user_id = fu.user_id
2290 and mcu.sr_instance_id = p_sr_instance_id;
2291
2292 CURSOR validUsers IS
2293 select fu.user_id user_id,
2294 mscu.sr_instance_id sr_instance_id,
2295 mcil.company_id company_id
2296 from msc_st_company_users mscu,
2297 fnd_user fu,
2298 msc_company_id_lid mcil
2299 where mscu.sr_instance_id = p_sr_instance_id
2300 and UPPER(mscu.user_name) = fu.user_name
2301 and mscu.sr_company_id = mcil.sr_company_id
2302 and mscu.sr_instance_id = mcil.sr_instance_id
2303 and mscu.partner_type = mcil.partner_type
2304 and mscu.sr_company_id <>-1
2305 and not exists (select '1'
2306 from msc_company_users mcu
2307 where mcu.user_id = fu.user_id
2308 and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
2309 )
2310
2311 UNION
2312
2313 select fu.user_id user_id,
2314 mscu.sr_instance_id sr_instance_id,
2315 1 company_id
2316 from msc_st_company_users mscu,
2317 fnd_user fu
2318 where UPPER(mscu.user_name) = fu.user_name
2319 and mscu.sr_company_id = -1
2320 and not exists (select '1'
2321 from msc_company_users mcu
2322 where mcu.user_id = fu.user_id
2323 and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
2324 )
2325
2326
2327 MINUS
2328 select mcu.user_id,
2329 mcu.sr_instance_id,
2330 mcu.company_id
2331 from msc_company_users mcu
2332 where sr_instance_id = p_sr_instance_id;
2333
2334
2335 a_user_name users;
2336 a_user_id number_arr;
2337 a_instance_id number_arr;
2338 a_company_id number_arr;
2339
2340 a_ins_user_id number_arr := number_arr();
2341 a_ins_instance_id number_arr := number_arr();
2342 a_ins_company_id number_arr := number_arr();
2343 a_ins_count number_arr := number_arr();
2344
2345 c_passwd varchar2(30) ;
2346
2347 BEGIN
2348
2349 --======================================================================
2350 -- Report invalid user-company association to LOG file.
2351 --======================================================================
2352
2353 OPEN invalidUsers;
2354 FETCH invalidUsers BULK COLLECT INTO
2355 a_user_name;
2356 CLOSE invalidUsers;
2357
2358 IF a_user_name.COUNT > 0 then
2359 LOG_MESSAGE('User company association validations');
2360 LOG_MESSAGE('==============================');
2361 FOR i IN 1..a_user_name.COUNT LOOP
2362 LOG_MESSAGE('User '||a_user_name(i)||' is already associated with other Company');
2363 END LOOP;
2364 LOG_MESSAGE('==============================');
2365 END IF;
2366
2367 --============================================
2368 -- Create new users. Perform this task only if
2369 -- p_user_company_mode is USER_AND_COMPANY
2370 --============================================
2371 BEGIN
2372 FOR C1 in newUsers LOOP
2373 Begin
2374 FND_USER_PKG.createUser( x_user_name => C1.user_name,
2375 x_owner => 'CUST',
2376 x_unencrypted_password => 'welcome',
2377 x_start_date => C1.start_date,
2378 x_end_date => C1.end_date,
2379 x_description => C1.description,
2380 x_email_address => C1.email_address,
2381 x_fax => C1.fax,
2382 x_user_guid => C1.user_guid -- Bug#12863892 fp of 9754732
2383 );
2384
2385 Exception
2386 WHEN OTHERS THEN
2387 LOG_MESSAGE('While creating a new user, following error occured. Will try to create the User again');
2388 LOG_MESSAGE(SQLERRM);
2389
2390 c_passwd := build_passwd ;
2391
2392 FND_USER_PKG.createUser( x_user_name => C1.user_name,
2393 x_owner => 'CUST',
2394 x_unencrypted_password => c_passwd,
2395 x_start_date => C1.start_date,
2396 x_end_date => C1.end_date,
2397 x_description => C1.description,
2398 x_email_address => C1.email_address,
2399 x_fax => C1.fax,
2400 x_user_guid => C1.user_guid -- Bug#12863892 fp of 9754732
2401 );
2402 LOG_MESSAGE('Created the User successfully.');
2403 End;
2404
2405 --==========================================================================
2406 -- Once the user is created, assign MSCX_SC_PLANNER responsibility
2407 -- to the user
2408 --==========================================================================
2409
2410 Fnd_User_Resp_Groups_Api.LOAD_ROW ( x_user_name => C1.user_name,
2411 x_resp_key => 'MSCX_SC_PLANNER',
2412 x_app_short_name => 'MSC',
2413 x_security_group => 'STANDARD',
2414 x_owner => NULL,
2415 x_start_date => to_char(C1.start_date, 'YYYY/MM/DD'),
2416 x_end_date => to_char(C1.end_date, 'YYYY/MM/DD'),
2417 x_description => C1.description
2418 );
2419
2420 END LOOP;
2421
2422 COMMIT;
2423 EXCEPTION
2424 WHEN NO_DATA_FOUND THEN
2425 LOG_MESSAGE('Please make sure that MSCX_SC_PLANNER responsibility exists');
2426 LOG_MESSAGE(SQLERRM);
2427 ROLLBACK;
2428 RAISE;
2429 WHEN OTHERS THEN
2430 LOG_MESSAGE('ERROR while creating a new user using FND_USER_PKG.createUser API');
2431 LOG_MESSAGE(SQLERRM);
2432 ROLLBACK;
2433 RAISE;
2434 END;
2435
2436 COMMIT;
2437
2438 --====================================================
2439 -- insert/update the valid users' company association.
2440 --====================================================
2441
2442 BEGIN
2443 OPEN validUsers;
2444 FETCH validUsers BULK COLLECT INTO
2445 a_user_id,
2446 a_instance_id,
2447 a_company_id;
2448
2449 CLOSE validUsers;
2450 EXCEPTION WHEN OTHERS THEN
2451 LOG_MESSAGE('Error while fetching records from validUsers cursor');
2452 LOG_MESSAGE(SQLERRM);
2453 END;
2454
2455 IF a_user_id.COUNT > 0 THEN
2456 BEGIN
2457 FORALL i in 1..a_user_id.COUNT
2458 UPDATE msc_company_users mcu
2459 set
2460 company_id = a_company_id(i)
2461 where mcu.user_id = a_user_id(i)
2462 and mcu.sr_instance_id = a_instance_id(i);
2463
2464 EXCEPTION WHEN OTHERS THEN
2465 LOG_MESSAGE('Error while updating msc_company_users');
2466 LOG_MESSAGE(SQLERRM);
2467 RAISE;
2468 END;
2469 END IF;
2470
2471
2472 /* Build the collection objects for insertion */
2473 FOR i IN 1..a_user_id.COUNT LOOP
2474 IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
2475
2476 /* Extend the Collection objects */
2477 a_ins_count.EXTEND;
2478 a_ins_user_id.EXTEND;
2479 a_ins_instance_id.EXTEND;
2480 a_ins_company_id.EXTEND;
2481
2482 /* Populate collection objects */
2483 a_ins_count(a_ins_count.COUNT) := i;
2484 a_ins_user_id(a_ins_count.COUNT) := a_user_id(i);
2485 a_ins_instance_id(a_ins_count.COUNT):= a_instance_id(i);
2486 a_ins_company_id(a_ins_count.COUNT) := a_company_id(i);
2487 END IF;
2488 END LOOP;
2489
2490
2491 IF a_ins_count.COUNT > 0 THEN
2492 BEGIN
2493
2494 FORALL i in 1..a_ins_count.COUNT
2495 INSERT INTO MSC_COMPANY_USERS
2496 ( USER_ID,
2497 COMPANY_ID,
2498 SR_INSTANCE_ID
2499 )
2500 VALUES
2501 ( a_ins_user_id(i),
2502 a_ins_company_id(i),
2503 a_ins_instance_id(i)
2504 );
2505
2506 EXCEPTION WHEN OTHERS THEN
2507 LOG_MESSAGE('Error while inserting records in msc_company_users');
2508 LOG_MESSAGE(SQLERRM);
2509 RAISE;
2510 END;
2511 END IF;
2512
2513 --==============================================
2514 -- Update current users with changed information
2515 --==============================================
2516
2517 BEGIN
2518
2519 FOR C1 IN validUsersUpdate LOOP
2520 FND_USER_PKG.UpdateUser( x_user_name => C1.user_name,
2521 x_owner => 'CUST',
2522 x_start_date => C1.start_date,
2523 x_end_date => C1.end_date,
2524 x_description => C1.description,
2525 x_email_address => C1.email_address,
2526 x_fax => C1.fax
2527 );
2528
2529 END LOOP;
2530
2531 COMMIT;
2532 EXCEPTION WHEN OTHERS THEN
2533 LOG_MESSAGE('ERROR while updating the User information');
2534 LOG_MESSAGE(SQLERRM);
2535 ROLLBACK;
2536 RAISE;
2537 END;
2538
2539 END LOAD_USER_COMPANY;
2540
2541 END MSC_CL_SCE_COLLECTION;