[Home] [Help]
PACKAGE BODY: APPS.CZ_ORAAPPS_INTEGRATE
Source
1 PACKAGE BODY CZ_ORAAPPS_INTEGRATE AS
2 /* $Header: czcaintb.pls 120.19.12020000.2 2012/09/28 17:38:28 smanna ship $ */
3
4 PROCEDURE get_App_Info(p_app_short_name IN VARCHAR2,
5 p_link_name IN VARCHAR2,
6 x_oracle_schema OUT NOCOPY VARCHAR2) IS
7
8 v_status VARCHAR2(255);
9 v_industry VARCHAR2(255);
10 BEGIN
11
12 EXECUTE IMMEDIATE
13 'DECLARE v_ret BOOLEAN; BEGIN v_ret := FND_INSTALLATION.GET_APP_INFO' || p_link_name ||
14 '(:1, :2, :3, :4); END;'
15 USING IN p_app_short_name, OUT v_status, OUT v_industry, OUT x_oracle_schema;
16 END;
17
18 FUNCTION ITEM_SURROGATE_KEY(nITEM_ID IN VARCHAR2, nORG_ID IN VARCHAR2)
19 RETURN VARCHAR2
20 IS
21 BEGIN
22 DECLARE
23 X CHAR :=':';
24 nSURR_KEY VARCHAR2(255);
25 BEGIN
26 nSURR_KEY :=nITEM_ID||X||nORG_ID;
27 RETURN nSURR_KEY;
28 END;
29 END ITEM_SURROGATE_KEY;
30 ----------------------------------------------------------------------
31 FUNCTION COMPONENT_SURROGATE_KEY(sCOMPONENT_SEQUENCE_ID IN VARCHAR2,
32 sEXPLOSION_TYPE IN VARCHAR2,
33 sORG_ID IN VARCHAR2,
34 sTOP_ITEM_ID IN VARCHAR2)
35 RETURN VARCHAR2
36 IS
37 BEGIN
38 RETURN CONCAT(sCOMPONENT_SEQUENCE_ID,CONCAT(':',CONCAT(CONCAT(sEXPLOSION_TYPE,CONCAT(':',sORG_ID)),CONCAT(':',sTOP_ITEM_ID))));
39 END COMPONENT_SURROGATE_KEY;
40 ----------------------------------------------------------------------
41 FUNCTION PROJECT_SURROGATE_KEY(sEXPLOSION_TYPE IN VARCHAR2,
42 sORG_ID IN VARCHAR2,
43 sTOP_ITEM_ID IN VARCHAR2)
44 RETURN VARCHAR2
45 IS
46 BEGIN
47 RETURN CONCAT(sEXPLOSION_TYPE, CONCAT(':', CONCAT(sORG_ID,CONCAT(':',sTOP_ITEM_ID))));
48 END PROJECT_SURROGATE_KEY;
49 ----------------------------------------------------------------------
50 FUNCTION ENDUSER_SURROGATE_KEY(sORG_ID IN VARCHAR2, sSalesrep_ID IN VARCHAR2)
51 RETURN VARCHAR2
52 IS
53 BEGIN
54 RETURN CONCAT(sORG_ID,CONCAT(':',sSalesrep_ID));
55 END ENDUSER_SURROGATE_KEY;
56 ----------------------------------------------------------------------
57 PROCEDURE ITEM_EXTERNAL_PK(nSURR_KEY IN VARCHAR2,xITEM_ID OUT NOCOPY VARCHAR2,
58 xORG_ID OUT NOCOPY VARCHAR2)
59 IS
60 BEGIN
61 DECLARE
62 nITEM_ID VARCHAR2(255);
63 nORG_ID VARCHAR2(255);
64 BEGIN
65 SELECT SUBSTR(nSURR_KEY,((INSTR(nSURR_KEY,':')+1)))
66 INTO nORG_ID
67 FROM DUAL;
68 SELECT RTRIM(nSURR_KEY,nORG_ID)
69 INTO nITEM_ID
70 FROM DUAL;
71 SELECT RTRIM(nITEM_ID,':')
72 INTO nITEM_ID
73 FROM DUAL;
74 xORG_ID:=nORG_ID;
75 xITEM_ID:=nITEM_ID;
76 END;
77 END ITEM_EXTERNAL_PK;
78 ------------------------------------------------------------------------------
79 ------------------------------------------------------------------------------
80 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
81 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
82
83 PROCEDURE register_spx_process
84 (p_name IN VARCHAR2,
85 p_short_name IN VARCHAR2,
86 p_application IN VARCHAR2,
87 p_description IN VARCHAR2,
88 p_procedure_name IN VARCHAR2,
89 p_request_group IN VARCHAR2,
90 cz_schema IN VARCHAR2 default NULL) AS
91
92 var_schema VARCHAR2(40);
93 creation_failure EXCEPTION;
94 exec_exists EXCEPTION;
95 no_req_group EXCEPTION;
96
97 BEGIN
98 IF NOT(fnd_program.executable_exists(executable_short_name=>p_short_name,
99 application => p_application)) THEN
100 fnd_program.executable(executable => p_name,
101 short_name => p_short_name,
102 application => p_application,
103 description => p_description,
104 execution_method => 'PL/SQL Stored Procedure',
105 execution_file_name => p_procedure_name);
106 ELSE
107 raise exec_exists;
108 END IF;
109 BEGIN
110 fnd_program.register(program => p_name,
111 application => p_application,
112 enabled => 'Y',
113 short_name => p_short_name,
114 description => p_description,
115 executable_short_name => p_short_name,
116 executable_application => p_application,
117 use_in_srs => 'Y');
118 EXCEPTION
119 WHEN OTHERS THEN
120 LOG_REPORT('Failure creating program: ' || SQLERRM);
121 raise creation_failure;
122 END;
123
124 -- Add concurrent program to OE Concurrent Program request group,
125 -- so it can be seen in Apps UI.
126
127 IF p_request_group IS NOT NULL THEN
128 BEGIN
129 IF fnd_program.request_group_exists(p_request_group, p_application)
130 THEN
131 fnd_program.add_to_group(p_short_name, p_application, p_request_group, p_application);
132 ELSE
133 raise no_req_group;
134 END IF;
135 END;
136 END IF;
137
138 commit;
139
140 EXCEPTION
141 WHEN exec_exists THEN
142 LOG_REPORT('This executable already exists. Script terminated.');
143 WHEN creation_failure THEN
144 LOG_REPORT('Creation failure');
145 WHEN no_req_group THEN
146 LOG_REPORT('<'||p_request_group || '> request group does not exist.');
147 WHEN OTHERS THEN
148 LOG_REPORT(SQLERRM);
149 END;
150
151 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
152 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
153
154 PROCEDURE delete_spx_process
155 (p_short_name IN VARCHAR2,
156 p_application IN VARCHAR2) AS
157 BEGIN
158 fnd_program.delete_program(p_short_name,p_application);
159 fnd_program.delete_executable(p_short_name,p_application);
160 commit;
161 END;
162
163
164
165 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
166 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
167
168 --Register SellingPoint Export concurrent process
169
170 PROCEDURE register_export_process
171 (application_name IN VARCHAR2 , -- default 'Oracle Order Entry',
172 Request_Group IN VARCHAR2 , -- default 'OE Concurrent Programs',
173 cz_schema IN VARCHAR2 default NULL) AS
174
175 var_schema VARCHAR2(40);
176 ar_application_name VARCHAR2(50):='Oracle Receivables';
177 ar_request_group VARCHAR2(50):=NULL;
178 creation_failure EXCEPTION;
179 exec_exists EXCEPTION;
180 no_req_group EXCEPTION;
181 BEGIN
182 BEGIN
183 register_spx_process(
184 'SellingPoint : Export',
185 'SPOEEXP',
186 application_name,
187 'Export Orders from SellingPoint to OE',
188 'CZ_EXPORT.SUBMIT_ALL_CP',
189 request_group,
190 cz_schema);
191 EXCEPTION
192 WHEN OTHERS THEN
193 LOG_REPORT('Error : < SellingPoint Order Export > REGISTRATION');
194 END;
195
196 BEGIN
197 register_spx_process(
198 'SellingPoint : Export for Single Order',
199 'SPOEEXPSO',
200 application_name,
201 'Export of Single Order from SellingPoint to OE',
202 'CZ_EXPORT.SUBMIT_FOR_QUOTE_CP',
203 request_group,
204 cz_schema);
205
206 fnd_program.parameter(program_short_name=>'SPOEEXPSO',
207 application=>application_name,
208 sequence=>1,
209 parameter=>'QUOTE_HDR_ID',
210 description=>'QUOTE ID',
211 value_set=>'CZ_QUOTE_ID',
212 display_size=>20,
213 description_size=>20,
214 concatenated_description_size=>50,
215 prompt=>'QUOTE ID');
216
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 LOG_REPORT('Error : < SellingPoint : Export for Single Order > REGISTRATION');
221 END;
222
223 BEGIN
224 register_spx_process(
225 'SellingPoint : Order Export Update Status Process',
226 'SPOEEXPUS',
227 application_name,
228 'Update Status of Exported Orders from SellingPoint to OE',
229 'CZ_EXPORT.ORDER_STATUS_UPDATE_CP',
230 request_group,
231 cz_schema);
232 EXCEPTION
233 WHEN OTHERS THEN
234 LOG_REPORT('Error : < SellingPoint Order Export Update Status Process > REGISTRATION');
235 END;
236 /* >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
237 BEGIN
238 register_spx_process(
239 'SellingPoint : Customer Export',
240 'SPAREXP',
241 ar_application_name,
242 'Export Customers from SellingPoint to OE',
243 'CZ_EXPORT.ALL_CUSTOMERS_EXPORT_CP',
244 ar_request_group,
245 cz_schema);
246 EXCEPTION
247 WHEN OTHERS THEN
248 LOG_REPORT('Error : < SellingPoint Customer Export > REGISTRATION');
249 END;
250
251 BEGIN
252 register_spx_process(
253 'SellingPoint : Customer Export Update Status',
254 'SPAREXPUS',
255 ar_application_name,
256 'Update Status of Exported Customers from SellingPoint to OE',
257 'CZ_EXPORT.CUSTOMER_STATUS_UPDATE_CP',
258 ar_request_group,
259 cz_schema);
260 EXCEPTION
261 WHEN OTHERS THEN
262 LOG_REPORT('Error : < SellingPoint Customer Export Update Status Process > REGISTRATION');
263 END;
264
265 BEGIN
266 register_spx_process(
267 'SellingPoint : Customer Export for Single Customer',
268 'SPAREXPSC',
269 ar_application_name,
270 'Export of Single Customer from SellingPoint to OE',
271 'CZ_EXPORT.CUSTOMER_EXPORT_CP',
272 ar_request_group,
273 cz_schema);
274 fnd_program.parameter(program_short_name=>'SPAREXPSC',
275 application=>ar_application_name,
276 sequence=>1,
277 parameter=>'CUSTOMER_ID',
278 description=>'CUSTOMER_ID',
279 value_set=>'7/Number',
280 display_size=>20,
281 description_size=>20,
282 concatenated_description_size=>50,
283 prompt=>'CUSTOMER ID');
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 LOG_REPORT('Error : < SellingPoint Customer Export for Single Customer > REGISTRATION');
288 END;
289 */
290
291 END;
292
293 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
294 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
295
296 --Register Configurator Import concurrent process
297
298 PROCEDURE register_import_process
299 (application_name IN VARCHAR2 , -- default 'Oracle Configurator',
300 request_group IN VARCHAR2 default NULL,
301 cz_schema IN VARCHAR2 default NULL) AS
302 BEGIN
303 BEGIN
304 register_spx_process('Refresh All Imported Configuration Models',
305 'CZAPPIMP',
306 application_name,
307 'Import Apps Data into Configurator',
308 'CZ_ORAAPPS_INTEGRATE.GO_CP',
309 request_group,
310 cz_schema);
311 EXCEPTION
312 WHEN OTHERS THEN
313 LOG_REPORT('Error : < Refresh Configuration Models from BOM > REGISTRATION');
314 END;
315 BEGIN
316 register_spx_process('Import Model Bills',
317 'CZAPPIMPPCM',
318 application_name,
319 'Populate Configuration Models',
320 'CZ_ORAAPPS_INTEGRATE.POPULATEMODELS_CP',
321 request_group,
322 cz_schema);
323 fnd_program.parameter(program_short_name=>'CZAPPIMPPCM',
324 application=>application_name,
325 sequence=>1,
326 parameter=>'sORG_ID',
327 description=>'Organization Code',
328 value_set=>'CZ_ORG_ID',
329 display_size=>10,
330 description_size=>10,
331 concatenated_description_size=>50,
332 prompt=>'Organization Code');
333
334 fnd_program.parameter(program_short_name=>'CZAPPIMPPCM',
335 application=>application_name,
336 sequence=>2,
337 parameter=>'dsORG_ID',
338 description=>'dummy value',
339 value_set=>'CZ_ORG_DUMMY',
340 default_type=>'SQL Statement',
341 default_value=>'select :$FLEX$.CZ_ORG_ID from dual',
342 display=>'N',
343 display_size=>10,
344 description_size=>10,
345 concatenated_description_size=>50,
346 prompt=>'dsORG_ID');
347
348 fnd_program.parameter(program_short_name=>'CZAPPIMPPCM',
349 application=>application_name,
350 sequence=>3,
351 parameter=>'sFrom',
352 description=>'Model Inventory Item From',
353 value_set=>'CZ_MODEL_ITM',
354 display_size=>10,
355 description_size=>10,
356 concatenated_description_size=>50,
357 prompt=>'Model Inventory Item From');
358 fnd_program.parameter(program_short_name=>'CZAPPIMPPCM',
359 application=>application_name,
360 sequence=>4,
361 parameter=>'sTo',
362 description=>'Model Inventory Item To',
363 value_set=>'CZ_MODEL_ITM',
364 display_size=>10,
365 description_size=>10,
366 concatenated_description_size=>50,
367 prompt=>'Model Inventory Item To');
368 fnd_program.parameter(program_short_name=>'CZAPPIMPPCM',
369 application=>application_name,
370 sequence=>5,
371 parameter=>'sChild',
372 description=>'Reference existing child models when available?',
373 value_set=>'CZ_ENABLE_FLAG',
374 display_size=>1,
375 description_size=>10,
376 concatenated_description_size=>50,
377 prompt=>'Reference existing child models when available?');
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 LOG_REPORT('Error : < Import New Configuration Models from BOM > REGISTRATION');
382 END;
383
384 BEGIN
385 register_spx_process('Refresh a Single Configuration Model',
386 'CZAPPIMPRFCM',
387 application_name,
388 'Refresh a Configuration Model',
389 'CZ_ORAAPPS_INTEGRATE.REFRESHSINGLEMODEL_CP',
390 request_group,
391 cz_schema);
392
393 fnd_program.parameter(program_short_name=>'CZAPPIMPRFCM',
394 application=>application_name,
395 sequence=>1,
396 parameter=>'sFolder_Id',
397 description=>'Folder',
398 value_set=>'CZ_FLD',
399 display_size=>20,
400 description_size=>20,
401 concatenated_description_size=>50,
402 prompt=>'Folder'
403 );
404
405 fnd_program.parameter(program_short_name=>'CZAPPIMPRFCM',
406 application=>application_name,
407 sequence=>2,
408 parameter=>'sModel_Id',
409 description=>'Configuration Model',
410 value_set=>'CZ_FOLDER_MODEL',
411 display_size=>20,
412 description_size=>20,
413 concatenated_description_size=>50,
414 prompt=>'Configuration Model Id'
415 );
416
417 fnd_program.parameter(program_short_name=>'CZAPPIMPRFCM',
418 application=>application_name,
419 sequence=>3,
420 parameter=>'sChild',
421 description=>'Reference existing child models when available?',
422 value_set=>'CZ_ENABLE_FLAG',
423 display_size=>1,
424 description_size=>10,
425 concatenated_description_size=>50,
426 prompt=>'Reference existing child models when available?');
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 LOG_REPORT('Error : < Refresh Single Configuration Model from BOM > REGISTRATION');
431 END;
432
433
434 BEGIN
435 register_spx_process('Disable/Enable Refresh of a Configuration Model',
436 'CZAPPIMPRMCM',
437 application_name,
438 'Change Import Parameters for a Configuration Model',
439 'CZ_ORAAPPS_INTEGRATE.REMOVEMODEL_CP',
440 request_group,
441 cz_schema);
442
443 fnd_program.parameter(program_short_name=>'CZAPPIMPRMCM',
444 application=>application_name,
445 sequence=>1,
446 parameter=>'sFolder_Id',
447 description=>'Folder',
448 value_set=>'CZ_FLD',
449 display_size=>20,
450 description_size=>20,
451 concatenated_description_size=>50,
452 prompt=>'Folder'
453 );
454
455 fnd_program.parameter(program_short_name=>'CZAPPIMPRMCM',
456 application=>application_name,
457 sequence=>2,
458 parameter=>'sModel_Id',
459 description=>'Configuration Model',
460 value_set=>'CZ_FOLDER_MODEL',
461 display_size=>20,
462 description_size=>20,
463 concatenated_description_size=>50,
464 prompt=>'Configuration Model Id'
465 );
466
467 fnd_program.parameter(program_short_name=>'CZAPPIMPRMCM',
468 application=>application_name,
469 sequence=>3,
470 parameter=>'sImportEnabled',
471 description=>'Import Enabled (Y/N)?',
472 value_set=>'CZ_ENABLE_FLAG',
473 display_size=>1,
474 description_size=>10,
475 concatenated_description_size=>50,
476 prompt=>'Import Enabled (Y/N)?');
477 EXCEPTION
478 WHEN OTHERS THEN
479 LOG_REPORT('Error : < Change Import Parameters for a Configuration Model > REGISTRATION');
480 END;
481
482 BEGIN
483 register_spx_process('Purge Configurator Tables',
484 'CZPURGE',
485 application_name,
486 'Purge Configurator Tables',
487 'CZ_MANAGER.PURGE_CP',
488 request_group,
489 cz_schema);
490 EXCEPTION
491 WHEN OTHERS THEN
492 LOG_REPORT('Error : < Purge Configurator Tables > REGISTRATION');
493 END;
494
495
496 commit;
497 END;
498
499
500 -- Register Configurator BOM syncronization concurrent process --
501
502 PROCEDURE register_bom_sync_process
503 (application_name IN VARCHAR2 , -- default 'Oracle Configurator',
504 request_group IN VARCHAR2 default NULL,
505 cz_schema IN VARCHAR2 default NULL) AS
506 BEGIN
507
508 BEGIN
509 register_spx_process('Check model(s)/bill(s) similarity',
510 'CZBOMSIM',
511 application_name,
512 'Check model(s)/bill(s) similarity',
513 'CZ_ORAAPPS_INTEGRATE.check_model_similarity_cp',
514 request_group,
515 cz_schema);
516
517 fnd_program.parameter(program_short_name=>'CZBOMSIM',
518 application=>application_name,
519 sequence=>1,
520 parameter=>'P_TARGET_INSTANCE',
521 description=>'Target Instance',
522 value_set=>'CZ_REMOTE_SERVER',
523 display_size=>20,
524 description_size=>20,
525 concatenated_description_size=>50,
526 prompt=>'Target Instance');
527
528
529 fnd_program.parameter(program_short_name=>'CZBOMSIM',
530 application=>application_name,
531 sequence=>2,
532 parameter=>'P_FOLDER_ID',
533 description=>'Folder',
534 value_set=>'CZ_FLD',
535 display_size=>20,
536 description_size=>20,
537 concatenated_description_size=>50,
538 prompt=>'Folder'
539 );
540
541 fnd_program.parameter(program_short_name=>'CZBOMSIM',
542 application=>application_name,
543 sequence=>3,
544 parameter=>'P_MODEL_ID',
545 description=>'List Of Models',
546 value_set=>'CZ_FOLDER_MODEL',
547 display_size=>20,
548 description_size=>20,
549 concatenated_description_size=>50,
550 prompt=>'List Of Models');
551
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 LOG_REPORT('Error : < Check model(s)/bills(s) similarity > REGISTRATION');
556 END;
557
558 BEGIN
559 register_spx_process('Check all models/bills similarity',
560 'CZALLBOMSIM',
561 application_name,
562 'Check all models/bills similarity',
563 'CZ_ORAAPPS_INTEGRATE.check_all_models_similarity_cp',
564 request_group,
565 cz_schema);
566
567 fnd_program.parameter(program_short_name=>'CZALLBOMSIM',
568 application=>application_name,
569 sequence=>1,
570 parameter=>'P_TARGET_INSTANCE',
571 description=>'Target Instance',
572 value_set=>'CZ_REMOTE_SERVER',
573 display_size=>20,
574 description_size=>20,
575 concatenated_description_size=>50,
576 prompt=>'Target Instance');
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 LOG_REPORT('Error : < Check all models/bills similarity > REGISTRATION');
581 END;
582
583 BEGIN
584 register_spx_process('Synchronize all models',
585 'CZALLBOMSYNC',
586 application_name,
587 'Synchronize all models',
588 'CZ_ORAAPPS_INTEGRATE.sync_all_models_cp',
589 request_group,
590 cz_schema);
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 LOG_REPORT('Error : < Synchronize all models > REGISTRATION');
595 END;
596
597 END;
598
599 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
600 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
601
602 PROCEDURE delete_bom_sync_process
603 (application_name IN VARCHAR2 -- default 'Oracle Configurator'
604 ) AS
605 BEGIN
606 BEGIN
607 fnd_program.delete_program('CZBOMSIM',application_name);
608 fnd_program.delete_executable('CZBOMSIM',application_name);
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611 LOG_REPORT('Error : < CZBOMSIM > does not exist.');
612 WHEN OTHERS THEN
613 LOG_REPORT('Error : < CZBOMSIM > :'||SQLERRM);
614 END;
615
616 BEGIN
617 fnd_program.delete_program('CZALLBOMSIM',application_name);
618 fnd_program.delete_executable('CZALLBOMSIM',application_name);
619 EXCEPTION
620 WHEN NO_DATA_FOUND THEN
621 LOG_REPORT('Error : < CZALLBOMSIM > does not exist.');
622 WHEN OTHERS THEN
623 LOG_REPORT('Error : < CZALLBOMSIM > :'||SQLERRM);
624 END;
625
626 BEGIN
627 fnd_program.delete_program('CZALLBOMSYNC',application_name);
628 fnd_program.delete_executable('CZALLBOMSYNC',application_name);
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 LOG_REPORT('Error : < CZALLBOMSYNC > does not exist.');
632 WHEN OTHERS THEN
633 LOG_REPORT('Error : < CZALLBOMSYNC > :'||SQLERRM);
634 END;
635
636 END;
637
638 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
639 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
640
641 PROCEDURE register_service_process(application_name IN VARCHAR2 , -- default 'Oracle Configurator',
642 request_group IN VARCHAR2 default NULL,
643 cz_schema IN VARCHAR2 default NULL) AS
644
645 BEGIN
646
647 BEGIN
648 register_spx_process('Repopulate',
649 'CZREPOP',
650 application_name,
651 'Repopulate',
652 'CZ_ORAAPPS_INTEGRATE.Repopulate_cp',
653 request_group,
654 cz_schema);
655
656 fnd_program.parameter(program_short_name=>'CZREPOP',
657 application=>application_name,
658 sequence=>1,
659 parameter=>'sFolder_Id',
660 description=>'Folder',
661 value_set=>'CZ_FLD',
662 display_size=>20,
663 description_size=>20,
664 concatenated_description_size=>50,
665 prompt=>'Folder');
666
667 fnd_program.parameter(program_short_name=>'CZREPOP',
668 application=>application_name,
669 sequence=>2,
670 parameter=>'sModel_Id',
671 description=>'Configuration Model',
672 value_set=>'CZ_FOLDER_MODEL',
673 display_size=>20,
674 description_size=>20,
675 concatenated_description_size=>50,
676 prompt=>'Configuration Model Id');
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 LOG_REPORT('Error : < Show Configurator Application Settings > REGISTRATION');
681 END;
682
683
684 BEGIN
685 register_spx_process('Show Configurator Application Settings',
686 'CZREPGS',
687 application_name,
688 'Show Configurator Application Settings',
689 'CZ_ORAAPPS_INTEGRATE.GETSETTING',
690 request_group,
691 cz_schema);
692
693 fnd_program.parameter(program_short_name=>'CZREPGS',
694 application=>application_name,
695 sequence=>1,
696 parameter=>'LIKE_SECTION_NAME',
697 description=>'SECTION NAME',
698 value_set=>'30 Characters',
699 default_type=>'Constant',
700 default_value=>'%',
701 display_size=>20,
702 description_size=>20,
703 concatenated_description_size=>50,
704 prompt=>'SECTION NAME');
705 fnd_program.parameter(program_short_name=>'CZREPGS',
706 application=>application_name,
707 sequence=>2,
708 parameter=>'LIKE_SETTING_ID',
709 description=>'SETTING_ID',
710 value_set=>'30 Characters',
711 default_type=>'Constant',
712 default_value=>'%',
713 display_size=>20,
714 description_size=>20,
715 concatenated_description_size=>50,
716 prompt=>'SETTING_ID');
717 EXCEPTION
718 WHEN OTHERS THEN
719 LOG_REPORT('Error : < Show Configurator Application Settings > REGISTRATION');
720 END;
721
722 BEGIN
723 register_spx_process('Change Configurator Application Settings',
724 'CZREPIN',
725 application_name,
726 'Change Configurator Application Settings',
727 'CZ_ORAAPPS_INTEGRATE.ASSIGNSETTING',
728 request_group,
729 cz_schema);
730
731 fnd_program.parameter(program_short_name=>'CZREPIN',
732 application=>application_name,
733 sequence=>1,
734 parameter=>'SECTION_NAME',
735 description=>'SECTION NAME',
736 value_set=>'CZ_DB_SETTINGS_SECTION_NAME',
737 display_size=>20,
738 description_size=>20,
739 concatenated_description_size=>50,
740 prompt=>'SECTION_NAME');
741 fnd_program.parameter(program_short_name=>'CZREPIN',
742 application=>application_name,
743 sequence=>2,
744 parameter=>'SETTING_ID',
745 description=>'SETTING_ID',
746 value_set=>'CZ_DB_SETTINGS_SETTING_ID',
747 display_size=>20,
748 description_size=>20,
749 concatenated_description_size=>50,
750 prompt=>'SETTING_ID');
751 fnd_program.parameter(program_short_name=>'CZREPIN',
752 application=>application_name,
753 sequence=>3,
754 parameter=>'VALUE',
755 description=>'VALUE',
756 value_set=>'30 Characters',
757 display_size=>20,
758 description_size=>20,
759 concatenated_description_size=>50,
760 prompt=>'VALUE');
761 fnd_program.parameter(program_short_name=>'CZREPIN',
762 application=>application_name,
763 sequence=>4,
764 parameter=>'TYPE',
765 description=>'TYPE',
766 value_set=>'CZ_DB_SETTINGS_TYPE',
767 display_size=>20,
768 description_size=>20,
769 concatenated_description_size=>50,
770 prompt=>'TYPE');
771 fnd_program.parameter(program_short_name=>'CZREPIN',
772 application=>application_name,
773 sequence=>5,
774 parameter=>'DESCRIPTION',
775 description=>'DESCRIPTION',
776 value_set=>'30 Characters',
777 display_size=>50,
778 description_size=>50,
779 concatenated_description_size=>50,
780 prompt=>'DESCRIPTION');
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 LOG_REPORT('< Change Configurator Application Settings >');
785 END;
786
787 BEGIN
788 register_spx_process('Show tables to be Imported',
789 'CZREPGIMP',
790 application_name,
791 'Show tables to be Imported',
792 'CZ_ORAAPPS_INTEGRATE.GETTABLEIMPORT',
793 request_group,
794 cz_schema);
795
796 fnd_program.parameter(program_short_name=>'CZREPGIMP',
797 application=>application_name,
798 sequence=>1,
799 parameter=>'LIKE_DST_TABLE_NAME',
800 description=>'TABLE_NAME',
801 value_set=>'30 Characters',
802 default_type=>'Constant',
803 default_value=>'%',
804 display_size=>20,
805 description_size=>20,
806 concatenated_description_size=>20,
807 prompt=>'TABLE NAME');
808 fnd_program.parameter(program_short_name=>'CZREPGIMP',
809 application=>application_name,
810 sequence=>2,
811 parameter=>'IMPORT_GROUP',
812 description=>'Import Group',
813 value_set=>'30 Characters',
814 default_type=>'Constant',
815 default_value=>'%',
816 display_size=>20,
817 description_size=>20,
818 concatenated_description_size=>50,
819 prompt=>'IMPORT GROUP');
820 EXCEPTION
821 WHEN OTHERS THEN
822 LOG_REPORT('Error : < Show tables to be Imported > REGISTRATION');
823 END;
824
825 BEGIN
826 register_spx_process('Select Tables to Be Imported',
827 'CZREPSIMP',
828 application_name,
829 'Select Tables to Be Imported',
830 'CZ_ORAAPPS_INTEGRATE.SETTABLEIMPORT',
831 request_group,
832 cz_schema);
833
834 fnd_program.parameter(program_short_name=>'CZREPSIMP',
835 application=>application_name,
836 sequence=>1,
837 parameter=>'DST_TABLE_NAME',
838 description=>'DST_TABLE_NAME',
839 value_set=>'CZ_IMPORT_DESTINATION_TABLE',
840 display_size=>20,
841 description_size=>20,
842 concatenated_description_size=>50,
843 prompt=>'Destination Table Name');
844 fnd_program.parameter(program_short_name=>'CZREPSIMP',
845 application=>application_name,
846 sequence=>2,
847 parameter=>'IMPORT_GROUP',
848 description=>'Import Group',
849 value_set=>'CZ_IMPORT_PHASES',
850 display_size=>20,
851 description_size=>20,
852 concatenated_description_size=>50,
853 prompt=>'Import Group');
854 fnd_program.parameter(program_short_name=>'CZREPSIMP',
855 application=>application_name,
856 sequence=>3,
857 parameter=>'ENABLEIMPORT',
858 description=>'Enable Import',
859 value_set=>'CZ_ENABLE_FLAG',
860 display_size=>20,
861 description_size=>20,
862 concatenated_description_size=>50,
863 prompt=>'Enable(Y/N)');
864 EXCEPTION
865 WHEN OTHERS THEN
866 LOG_REPORT('Error : < Select Tables to Be Imported > REGISTRATION');
867 END;
868
869
870 BEGIN
871 register_spx_process('Process Pending Publications',
872 'CZPUBLISHMODEL',
873 application_name,
874 'Export All Published Models',
875 'CZ_PB_MGR.publish_models_cp',
876 request_group,
877 cz_schema);
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 LOG_REPORT('Error : < Export All Published Models > REGISTRATION');
882 END;
883
884 BEGIN
885 register_spx_process('Process a Single Publication',
886 'CZPUBLISHSINGLE',
887 application_name,
888 'Export a Single Publication',
889 'CZ_PB_MGR.publish_single_model_cp',
890 request_group,
891 cz_schema);
892 fnd_program.parameter(program_short_name=>'CZPUBLISHSINGLE',
893 application=>application_name,
894 sequence=>1,
895 parameter=>'PublicationId',
896 description=>'Publication',
897 value_set=>'CZ_PUBLICATION',
898 display_size=>20,
899 description_size=>20,
900 concatenated_description_size=>50,
901 prompt=>'Publication');
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 LOG_REPORT('Error : < Export a Single Publication > REGISTRATION');
906 END;
907
908 END;
909
910 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
911 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
912
913 PROCEDURE delete_service_process
914 (application_name IN VARCHAR2 -- default 'System Administration'
915 ) AS
916 BEGIN
917 BEGIN
918 fnd_program.delete_program('SPREPGS',application_name);
919 fnd_program.delete_executable('SPREPGS',application_name);
920 EXCEPTION
921 WHEN NO_DATA_FOUND THEN
922 LOG_REPORT('Error : < SPREPGS > does not exist.');
923 WHEN OTHERS THEN
924 LOG_REPORT('Error : < SPREPGS > :'||SQLERRM);
925 END;
926
927 BEGIN
928 fnd_program.delete_program('SPREPIN',application_name);
929 fnd_program.delete_executable('SPREPIN',application_name);
930 EXCEPTION
931 WHEN NO_DATA_FOUND THEN
932 LOG_REPORT('Error : < SPREPIN > does not exist.');
933 WHEN OTHERS THEN
934 LOG_REPORT('Error : < SPREPIN > :'||SQLERRM);
935 END;
936
937 BEGIN
938 fnd_program.delete_program('SPREPGIMP',application_name);
939 fnd_program.delete_executable('SPREPGIMP',application_name);
940 EXCEPTION
941 WHEN NO_DATA_FOUND THEN
942 LOG_REPORT('Error : < SPREPGIMP > does not exist.');
943 WHEN OTHERS THEN
944 LOG_REPORT('Error : < SPREPGIMP > :'||SQLERRM);
945 END;
946
947 BEGIN
948 fnd_program.delete_program('SPREPSIMP',application_name);
949 fnd_program.delete_executable('SPREPSIMP',application_name);
950 EXCEPTION
951 WHEN NO_DATA_FOUND THEN
952 LOG_REPORT('Error : < SPREPSIMP > does not exist.');
953 WHEN OTHERS THEN
954 LOG_REPORT('Error : < SPREPSIMP > :'||SQLERRM);
955 END;
956
957 BEGIN
958 fnd_program.delete_program('SPCREATELNK',application_name);
959 fnd_program.delete_executable('SPCREATELNK',application_name);
960 EXCEPTION
961 WHEN NO_DATA_FOUND THEN
962 LOG_REPORT('Error : < SPCREATELNK > does not exist.');
963 WHEN OTHERS THEN
964 LOG_REPORT('Error : < SPCREATELNK > :'||SQLERRM);
965 END;
966
967 BEGIN
968 fnd_program.delete_program('SPPUBLISHMODEL',application_name);
969 fnd_program.delete_executable('SPPUBLISHMODEL',application_name);
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 LOG_REPORT('Error : < SPPUBLISHMODEL > does not exist.');
973 WHEN OTHERS THEN
974 LOG_REPORT('Error : < SPPUBLISHMODEL > :'||SQLERRM);
975 END;
976
977 BEGIN
978 fnd_program.delete_program('SPPOPULATESRV',application_name);
979 fnd_program.delete_executable('SPPOPULATESRV',application_name);
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 LOG_REPORT('Error : < SPPOPULATESRV > does not exist.');
983 WHEN OTHERS THEN
984 LOG_REPORT('Error : < SPPOPULATESRV > :'||SQLERRM);
985 END;
986
987
988 commit;
989 END;
990
991 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
992 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
993
994 --Delete SellingPoint Export concurrent process
995
996 PROCEDURE delete_export_process
997 (application_name IN VARCHAR2 -- default 'Oracle Order Entry'
998 ) AS
999 ar_application_name VARCHAR2(50):='Oracle Receivables';
1000 BEGIN
1001 BEGIN
1002 fnd_program.delete_program('SPOEEXP',application_name);
1003 fnd_program.delete_executable('SPOEEXP',application_name);
1004 EXCEPTION
1005 WHEN NO_DATA_FOUND THEN
1006 LOG_REPORT('Error : < SPOEEXP > does not exist.');
1007 WHEN OTHERS THEN
1008 LOG_REPORT('Error : < SPOEEXP > :'||SQLERRM);
1009 END;
1010
1011 BEGIN
1012 fnd_program.delete_program('SPOEEXPUS',application_name);
1013 fnd_program.delete_executable('SPOEEXPUS',application_name);
1014 EXCEPTION
1015 WHEN NO_DATA_FOUND THEN
1016 LOG_REPORT('Error : < SPOEEXPUS > does not exist.');
1017 WHEN OTHERS THEN
1018 LOG_REPORT('Error : < SPOEEXPUS > :'||SQLERRM);
1019 END;
1020
1021
1022 /*
1023 BEGIN
1024 fnd_program.delete_program('SPAREXP',ar_application_name);
1025 fnd_program.delete_executable('SPAREXP',ar_application_name);
1026 EXCEPTION
1027 WHEN NO_DATA_FOUND THEN
1028 LOG_REPORT('Error : < SPAREXP > does not exist.');
1029 WHEN OTHERS THEN
1030 LOG_REPORT('Error : < SPAREXP > :'||SQLERRM);
1031 END;
1032
1033 BEGIN
1034 fnd_program.delete_program('SPAREXPUS',ar_application_name);
1035 fnd_program.delete_executable('SPAREXPUS',ar_application_name);
1036 EXCEPTION
1037 WHEN NO_DATA_FOUND THEN
1038 LOG_REPORT('Error : < SPAREXPUS > does not exist.');
1039 WHEN OTHERS THEN
1040 LOG_REPORT('Error : < SPAREXPUS > :'||SQLERRM);
1041 END;
1042
1043 BEGIN
1044 fnd_program.delete_program('SPAREXPSC',ar_application_name);
1045 fnd_program.delete_executable('SPAREXPSC',ar_application_name);
1046 EXCEPTION
1047 WHEN NO_DATA_FOUND THEN
1048 LOG_REPORT('Error : < SPAREXPSC > does not exist.');
1049 WHEN OTHERS THEN
1050 LOG_REPORT('Error : < SPAREXPSC > :'||SQLERRM);
1051 END;
1052
1053 BEGIN
1054 fnd_program.delete_program('SPOEEXPSO',application_name);
1055 fnd_program.delete_executable('SPOEEXPSO',application_name);
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN
1058 LOG_REPORT('Error : < SPOEEXPSO > does not exist.');
1059 WHEN OTHERS THEN
1060 LOG_REPORT('Error : < SPOEEXPSO > :'||SQLERRM);
1061 END;
1062
1063 */
1064 commit;
1065 END;
1066
1067 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1068 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1069
1070 --Delete SellingPoint Import concurrent process
1071
1072 PROCEDURE delete_import_process
1073 (application_name IN VARCHAR2 -- default 'Oracle Bills of Material'
1074 ) AS
1075 BEGIN
1076 BEGIN
1077 fnd_program.delete_program('SPAPPIMP','System Administration');
1078 fnd_program.delete_executable('SPAPPIMP','System Administrationdel');
1079 EXCEPTION
1080 WHEN NO_DATA_FOUND THEN
1081 LOG_REPORT('Error : < SPAPPIMP > does not exist.');
1082 WHEN OTHERS THEN
1083 LOG_REPORT('Error : < SPAPPIMP > :'||SQLERRM);
1084 END;
1085 BEGIN
1086 fnd_program.delete_program('SPAPPIMPPCM',application_name);
1087 fnd_program.delete_executable('SPAPPIMPPCM',application_name);
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND THEN
1090 LOG_REPORT('Error : < SPAPPIMPPCM > does not exist.');
1091 WHEN OTHERS THEN
1092 LOG_REPORT('Error : < SPAPPIMPPCM > :'||SQLERRM);
1093 END;
1094
1095 BEGIN
1096 fnd_program.delete_program('SPAPPIMPRFCM',application_name);
1097 fnd_program.delete_executable('SPAPPIMPRFCM',application_name);
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 LOG_REPORT('Error : < SPAPPIMPRFCM > does not exist.');
1101 WHEN OTHERS THEN
1102 LOG_REPORT('Error : < SPAPPIMPRFCM > :'||SQLERRM);
1103 END;
1104
1105 BEGIN
1106 fnd_program.delete_program('SPAPPIMPRMCM',application_name);
1107 fnd_program.delete_executable('SPAPPIMPRMCM',application_name);
1108 EXCEPTION
1109 WHEN NO_DATA_FOUND THEN
1110 LOG_REPORT('Error : < SPAPPIMPRMCM > does not exist.');
1111 WHEN OTHERS THEN
1112 LOG_REPORT('Error : < SPAPPIMPRMCM > :'||SQLERRM);
1113 END;
1114
1115 commit;
1116 END;
1117
1118 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1119 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1120
1121 PROCEDURE submit_export_request
1122 (v_user_id IN NUMBER,
1123 v_resp_id IN NUMBER,
1124 vision_org_id IN NUMBER , -- default 204,
1125 v_appl_id IN NUMBER -- default 708 --CZ
1126 ) AS
1127
1128 --v_user_id NUMBER := 1068;
1129
1130 p_interval NUMBER:=10;
1131 p_max_wait NUMBER:=1000;
1132 oe_phase VARCHAR2(20);
1133 oe_status VARCHAR2(20);
1134 oe_dev_phase VARCHAR2(20);
1135 oe_dev_status VARCHAR2(20);
1136 oe_message VARCHAR2(50);
1137 oe_request_id NUMBER;
1138 oe_request_result BOOLEAN;
1139 cur NUMBER;
1140 var_row NUMBER;
1141 stmt VARCHAR2(1000);
1142 var_schema VARCHAR2(40);
1143 oe_submit_failure EXCEPTION;
1144
1145 BEGIN
1146
1147 oe_request_id := fnd_request.submit_request(
1148 'OE', -- APPlication Name from FND_APPLICATION
1149 'SPOEEXP', -- Application Concurrent Prgm from FND_CONCURRENT_PROGRAMS
1150 'Export Orders from SellingPoint to OE', -- Description from FND_CONCURRENT_PROGRAMS
1151 NULL, -- Start Date
1152 FALSE -- Is called from another concurrent request?
1153 );
1154 COMMIT;
1155 LOG_REPORT('Request ID : ' || to_char(oe_request_id));
1156 IF oe_request_id <> 0 THEN
1157 oe_request_result :=fnd_concurrent.wait_for_request(oe_request_id,
1158 p_interval,
1159 p_max_wait,
1160 oe_phase,
1161 oe_status,
1162 oe_dev_phase,
1163 oe_dev_status,
1164 oe_message);
1165 ELSE
1166 raise oe_submit_failure;
1167 END IF;
1168 COMMIT;
1169 EXCEPTION
1170 WHEN oe_submit_failure THEN
1171 LOG_REPORT('Error : SellingPoint Export process could not be submitted.');
1172 WHEN OTHERS THEN
1173 LOG_REPORT(SQLERRM);
1174
1175 END;
1176
1177 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1178 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1179
1180 PROCEDURE set_request_schedule
1181 (repeat_time IN VARCHAR2 default NULL,
1182 repeat_interval IN NUMBER , -- default 60,
1183 repeat_unit IN VARCHAR2 , -- default 'MINUTES',
1184 repeat_type IN VARCHAR2 , -- default 'START',
1185 repeat_end_time IN VARCHAR2 default NULL) AS
1186 ret BOOLEAN;
1187 BEGIN
1188 ret:=fnd_request.set_repeat_options(repeat_time,repeat_interval,repeat_unit,repeat_type,repeat_end_time);
1189 IF ret=FALSE THEN
1190 LOG_REPORT('Error in repeating request.');
1191 END IF;
1192 END;
1193
1194 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1195 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1196
1197 PROCEDURE SettingReport
1198 (section_name IN VARCHAR2 , -- default '%',
1199 setting_id IN VARCHAR2 , -- default '%',
1200 cz_schema IN VARCHAR2 default NULL ) AS
1201 cur INTEGER;
1202 stmt VARCHAR2(1000);
1203 var_setting_id VARCHAR2(40);
1204 var_value VARCHAR2(255);
1205 var_schema VARCHAR2(40);
1206 var_row INTEGER;
1207 BEGIN
1208 IF cz_schema is NULL THEN
1209 var_schema:=NULL;
1210 ELSE
1211 var_schema:=cz_schema||'.';
1212 END IF;
1213 cur:=dbms_sql.open_cursor;
1214 stmt:='SELECT setting_id,value from '||var_schema||'CZ_DB_SETTINGS WHERE section_name like '''||section_name||
1215 ''' and setting_id like '''||setting_id||''' ';
1216
1217 dbms_sql.parse(cur,stmt,dbms_sql.native);
1218 dbms_sql.define_column(cur,1,var_setting_id,40);
1219 dbms_sql.define_column(cur,2,var_value,300);
1220 var_row:=dbms_sql.execute(cur);
1221 WHILE (dbms_sql.fetch_rows(cur)>0) LOOP
1222 dbms_sql.column_value(cur,1,var_setting_id);
1223 dbms_sql.column_value(cur,2,var_value);
1224 LOG_REPORT(rpad(var_setting_id,40)||var_value);
1225 END LOOP;
1226 dbms_sql.close_cursor(cur);
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 LOG_REPORT(SQLERRM);
1230 END;
1231
1232 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1233 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1234 PROCEDURE LOG_REPORT
1235 (inStr IN VARCHAR2) AS
1236
1237 BEGIN
1238 cz_utils.log_report('CZ_ORAAPPS_INTEGRATE', null, null,
1239 instr, fnd_log.LEVEL_ERROR);
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 ROLLBACK;
1243 END;
1244
1245 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1246 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1247 ------------------------------------------------------------------------------
1248 PROCEDURE GetSetting(errbuf OUT NOCOPY VARCHAR2,
1249 retcode OUT NOCOPY NUMBER,
1250 LIKE_SectionName IN VARCHAR2 , -- DEFAULT '%',
1251 LIKE_SettingID IN VARCHAR2 -- DEFAULT '%'
1252 ) IS
1253 CURSOR C_GETSET IS
1254 SELECT SECTION_NAME,SETTING_ID,VALUE,DESC_TEXT FROM CZ_DB_SETTINGS
1255 WHERE SECTION_NAME LIKE LIKE_SectionName AND SETTING_ID LIKE LIKE_SettingID;
1256 slSectionName CZ_DB_SETTINGS.SECTION_NAME%TYPE;
1257 slSettingID CZ_DB_SETTINGS.SETTING_ID%TYPE;
1258 slValue CZ_DB_SETTINGS.VALUE%TYPE;
1259 slDescription CZ_DB_SETTINGS.DESC_TEXT%TYPE;
1260 BEGIN
1261 errbuf:='';
1262 retcode:=0;
1263 OPEN C_GETSET;
1264 LOOP
1265 FETCH C_GETSET INTO slSectionName,slSettingID,slValue,slDescription;
1266 EXIT WHEN C_GETSET%NOTFOUND;
1267
1268 FND_FILE.PUT_LINE(FND_FILE.LOG,'SECTION_NAME = '||slSectionName);
1269 FND_FILE.PUT_LINE(FND_FILE.LOG,'SETTING_ID = '||slSettingID);
1270 FND_FILE.PUT_LINE(FND_FILE.LOG,'VALUE = '||slValue);
1271 FND_FILE.PUT_LINE(FND_FILE.LOG,'DESCRIPTION = '||slDescription);
1272 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------------------------');
1273
1274 END LOOP;
1275 CLOSE C_GETSET;
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_GETSETTING_FAILED','ERRORTEXT',SQLERRM);
1279 --Now done from GET_TEXT
1280 --FND_FILE.PUT_LINE(FND_FILE.LOG,'GetSetting failed: '||SQLERRM);
1281 END;
1282 ------------------------------------------------------------------------------
1283 PROCEDURE AssignSetting(errbuf OUT NOCOPY VARCHAR2,
1284 retcode OUT NOCOPY NUMBER,
1285 sSECTION_NAME IN VARCHAR2,
1286 sSETTING_ID IN VARCHAR2,
1287 sVALUE IN VARCHAR2,
1288 sTYPE IN VARCHAR2 , -- DEFAULT '4',
1289 sDESCRIPTION IN VARCHAR2 DEFAULT NULL) IS
1290 CURSOR c1 IS
1291 SELECT 'X' FROM cz_db_settings
1292 WHERE section_name=section_Name AND
1293 setting_id=sSetting_Id;
1294 var1 varchar2(1);
1295 BEGIN
1296 errbuf:='';
1297 retcode:=0;
1298 OPEN c1;
1299 FETCH c1 INTO var1;
1300 IF c1%found THEN
1301 UPDATE cz_db_settings SET value=sValue WHERE section_name=sSection_Name AND setting_id=sSetting_Id;
1302 ELSE
1303 INSERT INTO cz_db_settings(setting_id,section_name,data_type,value,desc_text)
1304 VALUES(sSetting_Id,sSection_Name,sType,sValue,sDESCRIPTION);
1305 END IF;
1306 CLOSE c1;
1307 COMMIT;
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_SETSETTING_FAILED','ERRORTEXT',SQLERRM);
1311 --Now done from GET_TEXT
1312 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting not assigned: '||SQLERRM);
1313 END;
1314 ------------------------------------------------------------------------------
1315 PROCEDURE GetTableImport(errbuf OUT NOCOPY VARCHAR2,
1316 retcode OUT NOCOPY NUMBER,
1317 LIKE_DstTableName IN VARCHAR2 , -- DEFAULT '%',
1318 LIKE_PhaseName IN VARCHAR2 -- DEFAULT '%'
1319 ) IS
1320 CURSOR C_GETSET IS
1321 SELECT DST_TABLE,XFR_GROUP,DISABLED FROM CZ_XFR_TABLES
1322 WHERE DST_TABLE LIKE LIKE_DstTableName AND XFR_GROUP LIKE LIKE_PhaseName;
1323 slDstTable CZ_XFR_TABLES.DST_TABLE%TYPE;
1324 slXfrGroup CZ_XFR_TABLES.XFR_GROUP%TYPE;
1325 slDisabled CZ_XFR_TABLES.DISABLED%TYPE;
1326 BEGIN
1327 errbuf:='';
1328 retcode:=0;
1329 OPEN C_GETSET;
1330 LOOP
1331 FETCH C_GETSET INTO slDstTable,slXfrGroup,slDisabled;
1332 EXIT WHEN C_GETSET%NOTFOUND;
1333
1334 FND_FILE.PUT_LINE(FND_FILE.LOG,'DST_TABLE = '||slDstTable);
1335 FND_FILE.PUT_LINE(FND_FILE.LOG,'XFR_GROUP = '||slXfrGroup);
1336 FND_FILE.PUT_LINE(FND_FILE.LOG,'DISABLED_FLAG = '||slDisabled);
1337 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------------------------');
1338
1339 END LOOP;
1340 CLOSE C_GETSET;
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_READXFRTABLE_FAILED','ERRORTEXT',SQLERRM);
1344 END;
1345 ------------------------------------------------------------------------------
1346 PROCEDURE SetTableImport(errbuf OUT NOCOPY VARCHAR2,
1347 retcode OUT NOCOPY NUMBER,
1348 DstTableName IN VARCHAR2,
1349 LIKE_PhaseName IN VARCHAR2 , -- DEFAULT '%',
1350 EnableImport IN VARCHAR2 -- DEFAULT '1'
1351 ) IS
1352 BEGIN
1353 errbuf:='';
1354 retcode:=0;
1355 UPDATE CZ_XFR_TABLES SET
1356 DISABLED=DECODE(UPPER(EnableImport),'0','1','OFF','1','N','1','DISABLE','1',
1357 '1','0','ON','0','Y','0','ENABLE','0',DISABLED)
1358 WHERE DST_TABLE=DstTableName AND XFR_GROUP LIKE LIKE_PhaseName;
1359 IF(SQL%NOTFOUND)THEN
1360 LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_NOXFRTABLEDATA'));
1361 END IF;
1362 COMMIT;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_SETXFRTABLE_FAILED','ERRORTEXT',SQLERRM);
1366 LOG_REPORT(errbuf);
1367 END;
1368
1369 ------------------------------------------------------------------------------
1370 --The function returns 1 if the link exists, 0 otherwise. This is done for
1371 --compatibility with the previously used SELECT COUNT(*) FROM USER_DB_LINKS
1372 --method which didn't work well because of GLOBAL_NAMES.
1373
1374 FUNCTION doesLinkExist(p_link_name IN VARCHAR2) RETURN PLS_INTEGER IS
1375 v_null PLS_INTEGER;
1376 BEGIN
1377 EXECUTE IMMEDIATE 'SELECT NULL FROM DUAL@' || p_link_name;
1378 SELECT NULL INTO v_null FROM user_db_links WHERE UPPER(db_link) = UPPER(p_link_name)
1379 OR UPPER(db_link) LIKE UPPER(p_link_name) || '.%';
1380 RETURN 1;
1381 EXCEPTION
1382 WHEN NO_DATA_FOUND THEN
1383 RETURN 0;
1384 WHEN OTHERS THEN
1385
1386 --ORA-02019: connection description for remote database not found
1387
1388 IF SQLCODE = -2019 THEN RETURN 0; ELSE RETURN 1; END IF;
1389 END;
1390 ------------------------------------------------------------------------------
1391 FUNCTION isLinkAlive(sDb_Link IN VARCHAR2) RETURN VARCHAR2 IS
1392 v_temp DATE;
1393 v_db_Link VARCHAR2(255):='';
1394 BEGIN
1395 -- IF sDb_Link IS NULL OR sDb_Link='' OR sDb_Link=' ' THEN
1396 IF (replace(sDb_Link,' ',NULL) IS NULL) THEN
1397 v_db_link:='';
1398 ELSE
1399 v_db_link:='@'||sDB_Link;
1400 END IF;
1401 --
1402 -- to check DB link --
1403 -- use probe select statement --
1404 --
1405 EXECUTE IMMEDIATE 'SELECT sysdate FROM dual'||v_db_link
1406 INTO v_temp;
1407 RETURN LINK_WORKS;
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 RETURN LINK_IS_DOWN;
1411 END isLinkAlive;
1412 ------------------------------------------------------------------------------
1413
1414 PROCEDURE compile_Dependents(p_filter IN VARCHAR2 -- DEFAULT 'CZ_IMP%'
1415 ) IS
1416
1417 BEGIN
1418 FOR i IN (SELECT object_name FROM user_objects WHERE object_name like p_filter AND
1419 object_type='PACKAGE BODY' AND status='INVALID')
1420 LOOP
1421 BEGIN
1422 EXECUTE IMMEDIATE 'ALTER PACKAGE '||i.object_name||' COMPILE BODY';
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 log_report('compile_Dependents: Package ' || i.object_name || ' can not be compiled : ' || SQLERRM);
1426 END;
1427 END LOOP;
1428 END;
1429
1430 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1431 ---------- create views
1432
1433 FUNCTION create_exv_views(slocal_name IN VARCHAR2) RETURN VARCHAR2 AS
1434 v_fndnam_link_name cz_servers.local_name%type;
1435
1436 v_bom_string varchar2(4000);
1437 v_item_master_string varchar2(4000);
1438 v_items_string varchar2(4000);
1439 v_addresses_string varchar2(4000);
1440 v_address_uses_string varchar2(4000);
1441 v_customers_string varchar2(4000);
1442 v_contacts_string varchar2(4000);
1443 v_price_list_string varchar2(4000);
1444 v_price_list_line_string varchar2(4000);
1445 v_end_user_string varchar2(4000);
1446 v_item_properties_string varchar2(4000);
1447 v_item_property_values_string varchar2(4000);
1448 v_item_types_string varchar2(4000);
1449 v_mtl_system_items_string varchar2(4000);
1450 v_organization_string varchar2(4000);
1451 v_apc_props_string varchar2(4000);
1452 v_apc_prop_values_string varchar2(4000);
1453 v_string varchar2(4000);
1454 v_versioned_string varchar2(100);
1455 v_column_name varchar2(35);
1456 v_trackable_flag varchar2(35) := ' COMMS_NL_TRACKABLE_FLAG';
1457 v_config_model_type varchar2(35) := ' CONFIG_MODEL_TYPE';
1458 x_mtl_system_items_tl_exists BOOLEAN;
1459 v_table_name varchar2(355);
1460 v_intl_text_string_1 varchar2(4000);
1461 v_intl_text_string_2 varchar2(4000);
1462
1463 v_success char(1) := '0';
1464 v_warning char(1) := '1';
1465 v_error char(1) := '2';
1466 v_rownum VARCHAR2(20):='';
1467 v_where VARCHAR2(40):='';
1468 v_errorString VARCHAR2(1024) := 'CREATE_EXV_VIEWS : ';
1469 v_version_10_flag BOOLEAN := FALSE;
1470 v_count int;
1471 v_cur INTEGER;
1472 v_res INTEGER;
1473 v_inv_oracle_schema VARCHAR2(255);
1474
1475 localTable VARCHAR2(20) := '';
1476 settingVal PLS_INTEGER;
1477 v_mtl_system_items_tl VARCHAR2(240);
1478 v_where_tl VARCHAR2(240) := ' AND M.ORGANIZATION_ID = T.ORGANIZATION_ID AND M.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID ';
1479 v_where_tl_2 VARCHAR2(240) := ' WHERE M.ORGANIZATION_ID = T.ORGANIZATION_ID AND M.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID ';
1480 v_desc_tl VARCHAR2(240) := ' T.DESCRIPTION AS ITEM_DESC, ';
1481 v_description_tl VARCHAR2(240) := ' T.DESCRIPTION, ';
1482 v_where_lang_tl VARCHAR2(240) := ' AND T.LANGUAGE = userenv(''LANG'') ';
1483 v_language VARCHAR2(35) := ' T.LANGUAGE,';
1484
1485 BEGIN
1486
1487 IF (upper(slocal_name) NOT IN ('LOCAL','ERROR')) THEN
1488 SELECT fndnam_link_name INTO v_fndnam_link_name
1489 FROM cz_servers
1490 WHERE local_name = slocal_name;
1491 v_fndnam_link_name := '@'|| v_fndnam_link_name;
1492 ELSE
1493 v_fndnam_link_name := '';
1494 END IF;
1495
1496 v_mtl_system_items_tl := ', MTL_SYSTEM_ITEMS_TL'||v_fndnam_link_name||' T ';
1497
1498 IF (upper(slocal_name)='ERROR') THEN
1499 v_where:=' WHERE rownum<1';
1500 v_rownum:=' AND rownum<1';
1501 END IF;
1502
1503 --Reading the db settings for the bug #2713743 to see whether we need to include a local
1504 --table in the join for the definition of CZ_EXV_ITEM_PROPERTY_VALUES and CZ_EXV_ITEMS.
1505
1506 BEGIN
1507
1508 SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
1509 '0', 0, 'OFF', 0, 'N', 0, 'NO', 0,'FALSE', 0, 'DISABLE', 0,
1510 0) --default value
1511 INTO settingVal FROM cz_db_settings
1512 WHERE UPPER(section_name) = 'IMPORT'
1513 AND UPPER(setting_id) = 'USELOCALTABLEINEXTRACTIONVIEWS';
1514
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 settingVal := 0; --default value
1518 END;
1519
1520 --Even if the db setting exists it makes no sense to use it if the import source server
1521 --is local.
1522
1523 IF(settingVal = 1 AND v_fndnam_link_name IS NOT NULL)THEN localTable := ', DUAL'; END IF;
1524
1525 v_bom_string := 'CREATE OR REPLACE VIEW CZ_EXV_BILL_OF_MATERIALS AS ' ||
1526 ' SELECT ORGANIZATION_ID,ASSEMBLY_ITEM_ID,ASSEMBLY_TYPE,ALTERNATE_BOM_DESIGNATOR,COMMON_BILL_SEQUENCE_ID,BILL_SEQUENCE_ID ' ||
1527 ' FROM BOM_BILL_OF_MATERIALS' || v_fndnam_link_name ||' WHERE ALTERNATE_BOM_DESIGNATOR IS NULL'||v_rownum;
1528 EXECUTE IMMEDIATE v_bom_string;
1529
1530
1531 /* v_organization_string := 'CREATE OR REPLACE VIEW CZ_EXV_ORGANIZATIONS AS
1532 SELECT ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME
1533 FROM ORG_ORGANIZATION_DEFINITIONS'|| v_fndnam_link_name;*/
1534
1535 v_organization_string := 'CREATE OR REPLACE VIEW cz_exv_organizations AS ' ||
1536 'SELECT A.organization_id ORGANIZATION_ID, b.organization_code ORGANIZATION_CODE, A.name ORGANIZATION_NAME ' ||
1537 'FROM hr_all_organization_units'|| v_fndnam_link_name || ' A, ' ||
1538 ' mtl_parameters'|| v_fndnam_link_name || ' b, ' ||
1539 ' hr_organization_information'|| v_fndnam_link_name || ' c, ' ||
1540 ' hr_organization_information'|| v_fndnam_link_name || ' c1, ' ||
1541 ' gl_sets_of_books'|| v_fndnam_link_name || ' gsob ' ||
1542 'WHERE A.organization_id = b.organization_id ' ||
1543 'AND A.organization_id = c.organization_id ' ||
1544 'AND A.organization_id = c1.organization_id ' ||
1545 'AND c.org_information1 = ''INV'' ' ||
1546 'AND c.ORG_INFORMATION2 = ''Y'' ' ||
1547 'AND ( c.ORG_INFORMATION_CONTEXT || '''') = ''CLASS'' ' ||
1548 'AND ( c1.ORG_INFORMATION_CONTEXT || '''') =''Accounting Information'' ' ||
1549 'AND c1.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)'||v_rownum;
1550 EXECUTE IMMEDIATE v_organization_string;
1551
1552
1553 -- Check version of apps for existence of indivisible_flag
1554 -- mtl_system_items.indivisible_flag and mtl_system_items.concatenated_segments do not exist in ver 10.
1555
1556 v_versioned_string := ' INDIVISIBLE_FLAG';
1557 v_count := 0;
1558
1559 BEGIN
1560 get_App_Info('INV', v_fndnam_link_name, v_inv_oracle_schema);
1561 v_string := 'select count(*) from all_tab_columns'||v_fndnam_link_name||
1562 ' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%''
1563 and column_name = ''INDIVISIBLE_FLAG''';
1564 v_cur := dbms_sql.open_cursor;
1565 dbms_sql.parse(v_cur, v_string, dbms_sql.native);
1566 dbms_sql.define_column(v_cur,1,v_count);
1567 v_res := dbms_sql.execute(v_cur);
1568 if (dbms_sql.fetch_rows(v_cur) > 0) then
1569 dbms_sql.column_value(v_cur,1,v_count);
1570 end if;
1571 dbms_sql.close_cursor(v_cur);
1572
1573 IF (v_count = 0) THEN
1574 -- version 10
1575 v_versioned_string := ' ''Y'' INDIVISIBLE_FLAG';
1576 v_mtl_system_items_tl :='';
1577 v_where_tl := '';
1578 v_where_tl_2 :='';
1579 v_where_lang_tl :='';
1580 v_desc_tl := ' M.DESCRIPTION AS ITEM_DESC, ';
1581 v_description_tl := ' M.DESCRIPTION, ';
1582 v_language := ' ''' || userenv('LANG') || ''' AS LANGUAGE,';
1583 END IF;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 if (dbms_sql.is_open(v_cur)) then
1588 dbms_sql.close_cursor(v_cur);
1589 end if;
1590 END;
1591
1592 -- Check version of apps for existence of comms_nl_trackable_flag in mtl_system_items
1593
1594 v_trackable_flag := ' COMMS_NL_TRACKABLE_FLAG';
1595 BEGIN
1596 get_App_Info('INV', v_fndnam_link_name, v_inv_oracle_schema);
1597 v_string := 'select distinct column_name from all_tab_columns'||v_fndnam_link_name||
1598 ' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%'' and column_name = ''COMMS_NL_TRACKABLE_FLAG''';
1599 EXECUTE IMMEDIATE v_string INTO v_column_name;
1600
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 v_trackable_flag := ' ''N'' COMMS_NL_TRACKABLE_FLAG';
1604 END;
1605
1606 -- Check version of apps for existence of ml_system_items_tl
1607 x_mtl_system_items_tl_exists := TRUE;
1608 BEGIN
1609 get_App_Info('INV', v_fndnam_link_name, v_inv_oracle_schema);
1610 v_string := 'select distinct table_name from all_tables'||v_fndnam_link_name||
1611 ' where owner='''||v_inv_oracle_schema||''' AND table_name = ''MTL_SYSTEM_ITEMS_TL'' ';
1612 EXECUTE IMMEDIATE v_string INTO v_table_name;
1613
1614 EXCEPTION
1615 WHEN NO_DATA_FOUND THEN
1616 x_mtl_system_items_tl_exists := FALSE;
1617 END;
1618
1619 -- Changed view definition by dropping local table for performance improvement. Ref:9446997
1620
1621 IF x_mtl_system_items_tl_exists THEN
1622 v_intl_text_string_1:= 'CREATE OR REPLACE VIEW CZ_EXV_INTL_TEXT AS ' ||
1623 'SELECT distinct T.DESCRIPTION, T.LANGUAGE, T.SOURCE_LANG, ' ||
1624 'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
1625 'B.TOP_ITEM_ID, B.EXPLOSION_TYPE, BBM.ORGANIZATION_ID, ' ||
1626 'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
1627 'FROM BOM_EXPLOSIONS' || v_fndnam_link_name || ' B, ' ||
1628 'BOM_BILL_OF_MATERIALS' || v_fndnam_link_name || ' BBM, ' ||
1629 'MTL_SYSTEM_ITEMS_TL' || v_fndnam_link_name || ' T ' ||
1630 'WHERE T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
1631 'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1632 'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1633 'AND BBM.ASSEMBLY_ITEM_ID = B.TOP_ITEM_ID ' ||
1634 'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
1635 'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
1636 'AND B.EXPLOSION_TYPE = ''OPTIONAL'' ' ||
1637 'AND T.LANGUAGE IN (SELECT language_code ' ||
1638 ' FROM fnd_languages' || v_fndnam_link_name ||
1639 ' WHERE installed_flag IN (''B'', ''I''))';
1640
1641 EXECUTE IMMEDIATE v_intl_text_string_1;
1642 ELSE
1643
1644 -- Usually this block is not executed as MTL_SYSTEM_ITEMS_TL always exist
1645 -- but otherwise need to fix this view as not able to make any join with fnd_languages table
1646 -- due to absence of language column in other tables
1647 v_intl_text_string_2:= 'CREATE OR REPLACE VIEW CZ_EXV_INTL_TEXT AS ' ||
1648 'SELECT distinct T.DESCRIPTION, F.LANGUAGE_CODE, F.LANGUAGE_CODE AS SOURCE_LANG, ' ||
1649 'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
1650 'B.TOP_ITEM_ID, B.EXPLOSION_TYPE, BBM.ORGANIZATION_ID, ' ||
1651 'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
1652 'FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
1653 'BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM, ' ||
1654 'MTL_SYSTEM_ITEMS'|| v_fndnam_link_name || ' T, ' ||
1655 'FND_LANGUAGES'|| v_fndnam_link_name || ' F ' ||
1656 'WHERE T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
1657 'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1658 'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1659 'AND BBM.ASSEMBLY_ITEM_ID = B.TOP_ITEM_ID ' ||
1660 'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
1661 'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
1662 'AND B.EXPLOSION_TYPE = ''OPTIONAL'' ' ||
1663 'AND F.INSTALLED_FLAG in (''B'', ''I''))';
1664
1665 EXECUTE IMMEDIATE v_intl_text_string_2;
1666 END IF;
1667
1668 -- Check version of apps for existence of config_model_type in mtl_system_items
1669
1670 v_config_model_type := ' CONFIG_MODEL_TYPE';
1671 BEGIN
1672 get_App_Info('INV', v_fndnam_link_name, v_inv_oracle_schema);
1673 v_string := 'select distinct column_name from all_tab_columns' || v_fndnam_link_name ||
1674 ' where owner='''||v_inv_oracle_schema||''' AND table_name like ''MTL_SYSTEM_ITEMS%'' ' ||
1675 ' and column_name = ''CONFIG_MODEL_TYPE''';
1676 EXECUTE IMMEDIATE v_string INTO v_column_name;
1677
1678 EXCEPTION
1679 WHEN NO_DATA_FOUND THEN
1680 v_config_model_type := ' '''' CONFIG_MODEL_TYPE';
1681 END;
1682
1683 v_mtl_system_items_string := 'CREATE OR REPLACE VIEW CZ_EXV_MTL_SYSTEM_ITEMS AS ' ||
1684 ' SELECT M.INVENTORY_ITEM_ID,M.ORGANIZATION_ID,SEGMENT1,BOM_ITEM_TYPE, ' ||
1685 ' FIXED_LEAD_TIME,START_DATE_ACTIVE,END_DATE_ACTIVE, ' ||
1686 ' CUSTOMER_ORDER_ENABLED_FLAG, '||v_desc_tl||
1687 ' INVENTORY_ITEM_STATUS_CODE,ITEM_CATALOG_GROUP_ID,CONCATENATED_SEGMENTS,' || v_versioned_string ||
1688 ' FROM MTL_SYSTEM_ITEMS_KFV' || v_fndnam_link_name|| ' M ' ||v_mtl_system_items_tl||
1689 v_where_tl_2||v_where_lang_tl||v_rownum;
1690 EXECUTE IMMEDIATE v_mtl_system_items_string;
1691
1692 v_item_master_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_MASTER AS ' ||
1693 'SELECT ' ||
1694 'M.INVENTORY_ITEM_ID,B.ORGANIZATION_ID,M.SEGMENT1, ' ||
1695 'M.FIXED_LEAD_TIME,M.START_DATE_ACTIVE,M.END_DATE_ACTIVE, ' ||
1696 'M.CUSTOMER_ORDER_ENABLED_FLAG, '||v_desc_tl||
1697 ' M.INVENTORY_ITEM_STATUS_CODE,M.ITEM_CATALOG_GROUP_ID,' || v_versioned_string ||
1698 ',M.CONCATENATED_SEGMENTS, ' ||
1699 ' BBM.ASSEMBLY_ITEM_ID AS TOP_ITEM_ID,B.EXPLOSION_TYPE, ' ||
1700 ' B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID,B.COMPONENT_ITEM_ID, ' ||
1701 ' B.PLAN_LEVEL,B.SORT_ORDER,B.CREATION_DATE,B.CREATED_BY,B.LAST_UPDATE_DATE, ' ||
1702 ' B.LAST_UPDATED_BY,B.OPTIONAL,B.MUTUALLY_EXCLUSIVE_OPTIONS,B.LOW_QUANTITY, ' ||
1703 ' B.HIGH_QUANTITY,B.COMPONENT_QUANTITY,B.PRIMARY_UOM_CODE,B.BOM_ITEM_TYPE, ' ||
1704 ' B.PICK_COMPONENTS_FLAG,B.DESCRIPTION,B.ASSEMBLY_ITEM_ID,B.COMPONENT_CODE, ' ||
1705 ' B.EFFECTIVITY_DATE,B.DISABLE_DATE,' || v_language || ' ' ||
1706 ' DECODE (M.BOM_ITEM_TYPE || M.PICK_COMPONENTS_FLAG, ''1Y'',''P'',''1N'',''A'','''') ' ||
1707 ' AS MODEL_TYPE, ' ||
1708 v_trackable_flag || ', ' || v_config_model_type ||
1709 ', 702 AS BOM_APPLICATION_ID, 401 AS INV_APPLICATION_ID, ' ||
1710 ' DECODE (M.IB_ITEM_INSTANCE_CLASS,''LINK'',''1'',''0'') AS IB_LINK_ITEM_FLAG, ' ||
1711 ' B.shippable_item_flag, ' ||
1712 ' M.mtl_transactions_enabled_flag, ' ||
1713 ' B.replenish_to_order_flag, ' ||
1714 ' M.serial_number_control_code ' ||
1715 ' FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
1716 ' MTL_SYSTEM_ITEMS_KFV'|| v_fndnam_link_name || ' M, ' ||
1717 ' BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM '||v_mtl_system_items_tl||
1718 ' WHERE M.INVENTORY_ITEM_ID(+) = B.COMPONENT_ITEM_ID ' ||
1719 ' AND M.ORGANIZATION_ID(+) = B.ORGANIZATION_ID ' ||
1720 ' AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1721 ' AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
1722 ' AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL '||v_where_tl||v_rownum;
1723 EXECUTE IMMEDIATE v_item_master_string;
1724 -- kdande; 10-Jan-2008; Bug 5934249; Added BOM_BILL_OF_MATERIALS to the FROM to improve the performance via join
1725 v_items_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEMS AS ' ||
1726 'SELECT M.INVENTORY_ITEM_ID, M.ORGANIZATION_ID, M.SEGMENT1, ' ||
1727 'M.FIXED_LEAD_TIME, M.START_DATE_ACTIVE, M.END_DATE_ACTIVE, ' ||
1728 'M.CUSTOMER_ORDER_ENABLED_FLAG,' || v_versioned_string ||', M.CONCATENATED_SEGMENTS, ' ||
1729 'M.INVENTORY_ITEM_STATUS_CODE, M.ITEM_CATALOG_GROUP_ID, ' ||
1730 'M.PRIMARY_UOM_CODE, M.BOM_ITEM_TYPE, ' ||v_description_tl || v_desc_tl ||
1731 ' B.TOP_ITEM_ID, B.EXPLOSION_TYPE, 401 AS INV_APPLICATION_ID ' ||
1732 'FROM BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
1733 'MTL_SYSTEM_ITEMS_KFV'|| v_fndnam_link_name || ' M ' ||
1734 ',BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM ' ||
1735 localTable || v_mtl_system_items_tl ||
1736 ' WHERE M.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
1737 'AND B.top_item_id = BBM.assembly_item_id AND b.organization_id = BBM.organization_id ' ||
1738 'AND bbm.alternate_bom_designator IS NULL AND b.top_bill_sequence_id = bbm.bill_sequence_id ' ||
1739 'AND M.ORGANIZATION_ID = B.ORGANIZATION_ID' || v_where_tl || v_where_lang_tl || v_rownum;
1740
1741 EXECUTE IMMEDIATE v_items_string;
1742
1743 /*
1744 v_addresses_string := 'CREATE OR REPLACE VIEW CZ_EXV_ADDRESSES AS
1745 SELECT ADDRESS_ID,CUSTOMER_ID,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,COUNTRY,
1746 CITY,POSTAL_CODE,STATE,PROVINCE,COUNTY,BILL_TO_FLAG,SHIP_TO_FLAG
1747 FROM RA_ADDRESSES_ALL'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;
1748 EXECUTE IMMEDIATE v_addresses_string;
1749
1750 v_address_uses_string :='CREATE OR REPLACE VIEW CZ_EXV_ADDRESS_USES AS
1751 SELECT ADDRESS_ID,SITE_USE_CODE,WAREHOUSE_ID,SITE_USE_ID
1752 FROM RA_SITE_USES_ALL'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;
1753 EXECUTE IMMEDIATE v_address_uses_string ;
1754
1755 v_customers_string := 'CREATE OR REPLACE VIEW CZ_EXV_CUSTOMERS AS
1756 SELECT CUSTOMER_NAME,CUSTOMER_ID,PRICE_LIST_ID,CUSTOMER_CATEGORY_CODE,
1757 WAREHOUSE_ID,PRIMARY_SALESREP_ID
1758 FROM RA_CUSTOMERS'|| v_fndnam_link_name || ' WHERE STATUS=''A'''||v_rownum;
1759 EXECUTE IMMEDIATE v_customers_string;
1760
1761 v_contacts_string := 'CREATE OR REPLACE VIEW CZ_EXV_CONTACTS AS
1762 SELECT C.CUSTOMER_ID,C.ADDRESS_ID,C.SALUTATION,C.LAST_NAME,C.FIRST_NAME,C.SUFFIX,
1763 C.TITLE,C.PRIMARY_ROLE,C.EMAIL_ADDRESS,C.CONTACT_ID,P.PHONE_NUMBER PHONE,
1764 R.PHONE_NUMBER FAX
1765 FROM RA_CONTACTS'|| v_fndnam_link_name || ' C,RA_PHONES'|| v_fndnam_link_name || ' P,
1766 RA_PHONES'|| v_fndnam_link_name || ' R
1767 WHERE C.CUSTOMER_ID=P.CUSTOMER_ID (+)
1768 AND C.ADDRESS_ID=P.ADDRESS_ID (+)
1769 AND C.CONTACT_ID=P.CONTACT_ID (+)
1770 AND R.CUSTOMER_ID (+) =P.CUSTOMER_ID
1771 AND R.ADDRESS_ID (+) =P.ADDRESS_ID
1772 AND R.CONTACT_ID (+) =P.CONTACT_ID
1773 AND R.PHONE_TYPE(+)=''FAX'' AND P.PHONE_TYPE (+)=''GEN''
1774 AND C.STATUS=''A'''||v_rownum;
1775 EXECUTE IMMEDIATE v_contacts_string;
1776
1777 v_price_list_string := 'CREATE OR REPLACE VIEW CZ_EXV_PRICE_LISTS AS
1778 SELECT L.NAME,L.DESCRIPTION,L.CURRENCY_CODE,L.PRICE_LIST_ID
1779 FROM CZ_XFR_PRICE_LISTS P,SO_PRICE_LISTS'|| v_fndnam_link_name || ' L
1780 WHERE P.PRICE_LIST_ID=L.PRICE_LIST_ID
1781 AND P.DELETED_FLAG=''0'''||v_rownum;
1782 EXECUTE IMMEDIATE v_price_list_string;
1783
1784 v_price_list_line_string := 'CREATE OR REPLACE VIEW CZ_EXV_PRICE_LIST_LINES AS
1785 SELECT M.INVENTORY_ITEM_ID,M.ORGANIZATION_ID,
1786 L.LIST_PRICE,L.PRICE_LIST_ID,L.PRICE_LIST_LINE_ID
1787 FROM CZ_ITEM_MASTERS I,MTL_SYSTEM_ITEMS'|| v_fndnam_link_name || ' M,
1788 SO_PRICE_LIST_LINES'|| v_fndnam_link_name || ' L
1789 WHERE M.INVENTORY_ITEM_ID=L.INVENTORY_ITEM_ID
1790 AND to_char(M.ORGANIZATION_ID)=substr(I.ORIG_SYS_REF,instr(I.ORIG_SYS_REF,'':'',-1,1)+1)
1791 AND to_char(M.INVENTORY_ITEM_ID)=substr(I.ORIG_SYS_REF,1,instr(I.ORIG_SYS_REF,'':'',1,1)-1)
1792 AND I.DELETED_FLAG=''0'''||v_rownum;
1793 EXECUTE IMMEDIATE v_price_list_line_string;
1794
1795 v_end_user_string := 'CREATE OR REPLACE VIEW CZ_EXV_END_USER AS
1796 SELECT SALESREP_ID,ORG_ID,EMAIL_ADDRESS,NAME
1797 FROM RA_SALESREPS_ALL'|| v_fndnam_link_name || '
1798 WHERE STATUS=''A'''||v_rownum;
1799 EXECUTE IMMEDIATE v_end_user_string;
1800
1801 --Bug #2323864 - this view is being replaced with the one below.
1802
1803 v_item_property_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_PROPERTY_VALUES AS
1804 SELECT M.ELEMENT_NAME,M.ELEMENT_VALUE,M.INVENTORY_ITEM_ID,
1805 I.ORGANIZATION_ID,I.TOP_ITEM_ID,I.EXPLOSION_TYPE
1806 FROM CZ_EXV_ITEM_MASTER I,MTL_DESCR_ELEMENT_VALUES'|| v_fndnam_link_name || ' M
1807 WHERE M.INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID'||v_rownum;
1808 */
1809
1810 v_item_property_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_PROPERTY_VALUES ' ||
1811 ' (ELEMENT_NAME, ELEMENT_VALUE, INVENTORY_ITEM_ID, ORGANIZATION_ID) AS ' ||
1812 ' SELECT M.ELEMENT_NAME, M.ELEMENT_VALUE, M.INVENTORY_ITEM_ID, MTL.ORGANIZATION_ID ' ||
1813 ' FROM MTL_SYSTEM_ITEMS_KFV' || v_fndnam_link_name || ' MTL, ' ||
1814 ' MTL_DESCR_ELEMENT_VALUES' || v_fndnam_link_name || ' M ' ||
1815 ' WHERE M.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID' || v_rownum;
1816 EXECUTE IMMEDIATE v_item_property_values_string;
1817
1818 v_item_properties_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_PROPERTIES AS ' ||
1819 'SELECT ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,DESCRIPTION ' ||
1820 'FROM MTL_DESCRIPTIVE_ELEMENTS'|| v_fndnam_link_name||v_where;
1821 EXECUTE IMMEDIATE v_item_properties_string;
1822
1823 v_item_types_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_TYPES AS ' ||
1824 'SELECT ITEM_CATALOG_GROUP_ID,PARENT_CATALOG_GROUP_ID,DESCRIPTION,CATALOG_CONCAT_SEGS ' ||
1825 'FROM MTL_ITEM_CATALOG_GROUPS_V'|| v_fndnam_link_name||v_where;
1826 EXECUTE IMMEDIATE v_item_types_string;
1827
1828 v_bom_string := 'CREATE OR REPLACE VIEW cz_exv_descr_element_values AS ' ||
1829 ' SELECT element_name, element_value ' ||
1830 ' FROM mtl_descr_element_values' || v_fndnam_link_name ||' WHERE element_value IS NOT NULL' || v_rownum;
1831 EXECUTE IMMEDIATE v_bom_string;
1832
1833 v_bom_string := 'CREATE OR REPLACE VIEW cz_exv_bom_explosions AS
1834 SELECT organization_id, top_item_id, explosion_type, rexplode_flag
1835 FROM bom_explosions' || v_fndnam_link_name || v_where;
1836 EXECUTE IMMEDIATE v_bom_string;
1837
1838 BEGIN
1839
1840 v_apc_props_string := 'CREATE OR REPLACE VIEW CZ_EXV_APC_PROPERTIES AS '||
1841 'SELECT attrgrps.attr_group_id,assocs.classification_code AS item_catalog_group_id,attrs.* '||
1842 'FROM EGO_ATTR_GROUPS_V'||v_fndnam_link_name||' attrgrps '||
1843 ',EGO_ATTRS_V'||v_fndnam_link_name||' attrs '||
1844 ',EGO_OBJ_AG_ASSOCS_B'||v_fndnam_link_name||' assocs '||
1845 ',EGO_ITMATTR_APPL_USGS_B'||v_fndnam_link_name||' usgs '||
1846 ',FND_OBJECTS'||v_fndnam_link_name||' objs '||
1847 'WHERE objs.OBJ_NAME = ''EGO_ITEM'' '||
1848 'AND objs.OBJECT_ID = assocs.OBJECT_ID '||
1849 'AND assocs.ATTR_GROUP_ID = attrgrps.ATTR_GROUP_ID '||
1850 'AND assocs.ATTR_GROUP_ID = usgs.ATTR_GROUP_ID '|| --Bug14633568. Added additional clause
1851 'AND attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
1852 'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
1853 'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
1854 'AND usgs.ATTR_ID = attrs.ATTR_ID '||
1855 'AND usgs.ENABLED_FLAG = ''Y'' '||
1856 'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
1857 ' assocs.DATA_LEVEL=''ITEM_LEVEL'' AND attrs.ENABLED_FLAG=''Y'' '|| v_rownum;
1858
1859 EXECUTE IMMEDIATE v_apc_props_string;
1860
1861 v_apc_prop_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS '||
1862 ' SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL'|| v_fndnam_link_name||' c '||
1863 ' WHERE EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES a WHERE a.attr_group_id=c.attr_group_id) AND '||
1864 ' EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES b WHERE b.item_catalog_group_id=c.item_catalog_group_id)' || v_rownum;
1865
1866 v_apc_prop_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS '||
1867 'SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL '|| v_fndnam_link_name||' c '||
1868 ' WHERE EXISTS '||
1869 '(SELECT NULL FROM '||
1870 'EGO_ATTR_GROUPS_V '||v_fndnam_link_name||' attrgrps '||
1871 ',EGO_ATTRS_V '||v_fndnam_link_name||' attrs '||
1872 ',EGO_ITMATTR_APPL_USGS_B '||v_fndnam_link_name||' usgs '||
1873 ',FND_OBJECTS '||v_fndnam_link_name||' objs '||
1874 'WHERE attrgrps.attr_group_id=c.attr_group_id AND objs.OBJ_NAME = ''EGO_ITEM'' '||
1875 'AND usgs.attr_group_id=c.attr_group_id '|| --Bug14633568. Added additional clause
1876 'AND attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
1877 'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
1878 'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
1879 'AND usgs.ATTR_ID = attrs.ATTR_ID '||
1880 'AND usgs.ENABLED_FLAG = ''Y'' '||
1881 'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
1882 ' attrs.ENABLED_FLAG=''Y'') '||v_rownum;
1883
1884 EXECUTE IMMEDIATE v_apc_prop_values_string;
1885
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888
1889 log_report('APC is not installed, stub views will be created.'||
1890 cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR_DTL','SQLERRM',Sqlerrm));
1891
1892 v_apc_props_string :=
1893 'CREATE OR REPLACE VIEW CZ_EXV_APC_PROPERTIES AS ' ||
1894 ' SELECT ' ||
1895 ' -1 AS ATTR_GROUP_ID ' ||
1896 ' ,-1 AS ITEM_CATALOG_GROUP_ID ' ||
1897 ' ,-1 AS ATTR_ID ' ||
1898 ' ,-1 AS APPLICATION_ID ' ||
1899 ' ,-1 AS ATTR_GROUP_TYPE ' ||
1900 ' ,''*'' AS ATTR_GROUP_NAME ' ||
1901 ' ,''*'' AS ATTR_NAME ' ||
1902 ' ,''*'' AS ATTR_DISPLAY_NAME ' ||
1903 ' ,''*'' AS DESCRIPTION ' ||
1904 ' ,''*'' AS DATABASE_COLUMN ' ||
1905 ' ,''*'' AS DATA_TYPE_CODE ' ||
1906 ' ,-1 AS SEQUENCE ' ||
1907 ' ,''*'' AS UNIQUE_KEY_FLAG ' ||
1908 ' ,''*'' AS DEFAULT_VALUE ' ||
1909 ' ,''*'' AS INFO_1 ' ||
1910 ' ,''*'' AS UOM_CLASS ' ||
1911 ' ,-1 AS CONTROL_LEVEL ' ||
1912 ' ,-1 AS VALUE_SET_ID ' ||
1913 ' ,''*'' AS VALUE_SET_NAME ' ||
1914 ' ,''*'' AS FORMAT_CODE ' ||
1915 ' ,-1 AS MAXIMUM_SIZE ' ||
1916 ' ,''*'' AS VALIDATION_CODE ' ||
1917 ' ,''*'' AS LONGLIST_FLAG ' ||
1918 ' ,''*'' AS ENABLED_FLAG ' ||
1919 ' ,''*'' AS ENABLED_MEANING ' ||
1920 ' ,''*'' AS REQUIRED_FLAG ' ||
1921 ' ,''*'' AS REQUIRED_MEANING ' ||
1922 ' ,''*'' AS SEARCH_FLAG ' ||
1923 ' ,''*'' AS SEARCH_MEANING ' ||
1924 ' ,''*'' AS DISPLAY_CODE ' ||
1925 ' ,''*'' AS DISPLAY_MEANING ' ||
1926 ' ,''*'' AS ATTRIBUTE_CODE ' ||
1927 ' ,''*'' AS VIEW_IN_HIERARCHY_CODE ' ||
1928 ' ,''*'' AS EDIT_IN_HIERARCHY_CODE ' ||
1929 ' ,''*'' AS CUSTOMIZATION_LEVEL ' ||
1930 ' FROM dual';
1931 EXECUTE IMMEDIATE v_apc_props_string;
1932
1933 v_apc_prop_values_string :=
1934 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS ' ||
1935 'SELECT ' ||
1936 '-1 AS EXTENSION_ID ' ||
1937 ',-1 AS ORGANIZATION_ID ' ||
1938 ',-1 AS INVENTORY_ITEM_ID ' ||
1939 ',-1 AS REVISION_ID ' ||
1940 ',-1 AS ITEM_CATALOG_GROUP_ID ' ||
1941 ',-1 AS ATTR_GROUP_ID ' ||
1942 ',''*'' AS SOURCE_LANG ' ||
1943 ',''*'' AS LANGUAGE ' ||
1944 'FROM dual';
1945 EXECUTE IMMEDIATE v_apc_prop_values_string;
1946
1947 END;
1948
1949
1950 return v_success;
1951
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 log_report(v_errorString || cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR_DTL','SQLERRM',Sqlerrm));
1955 return v_error;
1956 END create_exv_views;
1957
1958 ------------------------------------------------------------------------------
1959
1960 PROCEDURE recreate_exv_views
1961 (p_link_status OUT NOCOPY VARCHAR2,
1962 p_db_link OUT NOCOPY VARCHAR2,
1963 p_do_compile IN VARCHAR2 -- DEFAULT '1'
1964 ) IS
1965 v_code VARCHAR2(1);
1966 v_import_enabled cz_servers.import_enabled%TYPE;
1967 v_local_name cz_servers.local_name%TYPE;
1968 BEGIN
1969 BEGIN
1970 SELECT local_name,fndnam_link_name,import_enabled
1971 INTO v_local_name,p_db_link,v_import_enabled FROM CZ_SERVERS
1972 WHERE import_enabled='1';
1973
1974 ---- check DB Link ----
1975 p_link_status:=isLinkAlive(p_db_link);
1976 EXCEPTION
1977 WHEN NO_DATA_FOUND THEN
1978 p_link_status:=LINK_IS_DOWN;
1979 END;
1980
1981 IF p_link_status=LINK_IS_DOWN THEN
1982
1983 ---- recreate views based on local tables ----
1984 v_code:=create_exv_views('ERROR');
1985
1986 IF v_code='0' THEN
1987 p_link_status:=LINK_IS_DOWN;
1988 ELSE
1989 p_link_status:=v_code;
1990 END IF;
1991
1992 END IF;
1993
1994 IF p_link_status=LINK_WORKS THEN
1995
1996 ---- recreate views based on remote tables ----
1997 v_code:=create_exv_views(v_local_name);
1998
1999 IF v_code='0' THEN
2000 p_link_status:=LINK_WORKS;
2001 ELSE
2002 p_link_status:=v_code;
2003 END IF;
2004 END IF;
2005
2006 IF UPPER(p_do_compile) IN ('1','Y','YES') THEN
2007 compile_Dependents('CZ_IMP%');
2008 END IF;
2009 COMMIT;
2010 EXCEPTION
2011 WHEN TOO_MANY_ROWS THEN
2012 log_report('recreate_exv_views: ' || CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS'));
2013 WHEN OTHERS THEN
2014 log_report(SQLERRM);
2015 END;
2016
2017 ------------------------------------------------------------------------------
2018
2019 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2020 ---------- recreate extraction views concurrent program
2021
2022 PROCEDURE recreate_exv_views_cp(errbuf IN OUT NOCOPY VARCHAR2,
2023 retcode IN OUT NOCOPY INTEGER) IS
2024 v_views_status VARCHAR2(1);
2025 v_db_link CZ_SERVERS.fndnam_link_name%TYPE;
2026 BEGIN
2027 retcode:=0;
2028 recreate_exv_views(v_views_status,v_db_link, 1);
2029 IF v_views_status=LINK_WORKS THEN
2030 errbuf:='';
2031 END IF;
2032 IF v_views_status=LINK_IS_DOWN THEN
2033 retcode:=2;
2034 errbuf:=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
2035 END IF;
2036 END;
2037
2038 ------------------------------------------------------------------------------
2039
2040 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2041 ---------- drop exv views
2042
2043 FUNCTION drop_exv_views
2044
2045 RETURN VARCHAR2
2046 AS
2047
2048 v_bom_string varchar2(4000);
2049 v_item_master_string varchar2(4000);
2050 v_mtl_system_items_string varchar2(4000);
2051 v_items_string varchar2(4000);
2052 v_addresses_string varchar2(4000);
2053 v_address_uses_string varchar2(4000);
2054 v_customers_string varchar2(4000);
2055 v_contacts_string varchar2(4000);
2056 v_price_list_string varchar2(4000);
2057 v_price_list_line_string varchar2(4000);
2058 v_end_user_string varchar2(4000);
2059 v_item_properties_string varchar2(4000);
2060 v_item_property_values_string varchar2(4000);
2061 v_item_types_string varchar2(4000);
2062 v_organization_string varchar2(4000);
2063 v_string varchar2(4000);
2064
2065 v_success char(1) := '0';
2066 v_warning char(1) := '1';
2067 v_error char(1) := '2';
2068 v_errorString VARCHAR2(1024) := 'DROP_EXV_VIEWS : ';
2069
2070 BEGIN
2071
2072 v_bom_string := 'DROP VIEW CZ_EXV_BILL_OF_MATERIALS';
2073 EXECUTE IMMEDIATE v_bom_string;
2074
2075 v_item_master_string := 'DROP VIEW CZ_EXV_ITEM_MASTER';
2076 EXECUTE IMMEDIATE v_item_master_string;
2077
2078 v_organization_string := 'DROP VIEW CZ_EXV_ORGANIZATIONS';
2079 EXECUTE IMMEDIATE v_organization_string;
2080
2081 v_mtl_system_items_string := 'DROP VIEW CZ_EXV_MTL_SYSTEM_ITEMS';
2082 EXECUTE IMMEDIATE v_mtl_system_items_string;
2083
2084 v_items_string := 'DROP VIEW CZ_EXV_ITEMS';
2085 EXECUTE IMMEDIATE v_items_string;
2086
2087 v_addresses_string := 'DROP VIEW CZ_EXV_ADDRESSES';
2088 EXECUTE IMMEDIATE v_addresses_string;
2089
2090 v_address_uses_string :='DROP VIEW CZ_EXV_ADDRESS_USES';
2091 EXECUTE IMMEDIATE v_address_uses_string ;
2092
2093 v_customers_string := 'DROP VIEW CZ_EXV_CUSTOMERS';
2094 EXECUTE IMMEDIATE v_customers_string;
2095
2096 v_contacts_string := 'DROP VIEW CZ_EXV_CONTACTS';
2097 EXECUTE IMMEDIATE v_contacts_string;
2098
2099 v_price_list_string := 'DROP VIEW CZ_EXV_PRICE_LISTS';
2100 EXECUTE IMMEDIATE v_price_list_string;
2101
2102 v_price_list_line_string := 'DROP VIEW CZ_EXV_PRICE_LIST_LINES';
2103 EXECUTE IMMEDIATE v_price_list_line_string;
2104
2105 v_end_user_string := 'DROP VIEW CZ_EXV_END_USER';
2106 EXECUTE IMMEDIATE v_end_user_string;
2107
2108 v_item_property_values_string := 'DROP VIEW CZ_EXV_ITEM_PROPERTY_VALUES';
2109 EXECUTE IMMEDIATE v_item_property_values_string;
2110
2111 v_item_properties_string := 'DROP VIEW CZ_EXV_ITEM_PROPERTIES';
2112 EXECUTE IMMEDIATE v_item_properties_string;
2113
2114 v_item_types_string := 'DROP VIEW CZ_EXV_ITEM_TYPES';
2115 EXECUTE IMMEDIATE v_item_types_string;
2116
2117 v_bom_string := 'DROP VIEW CZ_EXV_BOM_EXPLOSIONS';
2118 EXECUTE IMMEDIATE v_bom_string;
2119
2120 return v_success;
2121
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 log_report(v_errorString || cz_utils.get_text('CZ_DROP_EXT_VIEWS_ERR','SQLERRM',Sqlerrm));
2125 return v_error;
2126 END drop_exv_views;
2127
2128
2129 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2130 ---------- to insert records into cz_servers
2131
2132 PROCEDURE populate_cz_server_cp( Errbuf IN OUT NOCOPY VARCHAR2,
2133 Retcode IN OUT NOCOPY PLS_INTEGER,
2134 LOCAL_NAME IN VARCHAR2
2135 ,HOSTNAME IN VARCHAR2
2136 ,DB_LISTENER_PORT IN NUMBER
2137 ,INSTANCE_NAME IN VARCHAR2
2138 ,FNDNAM IN VARCHAR2
2139 ,GLOBAL_IDENTITY IN VARCHAR2
2140 ,NOTES IN VARCHAR2
2141 ,FNDNAM_LINK_NAME IN VARCHAR2
2142 ,IMPORT_ENABLED IN VARCHAR2
2143 )
2144 AS
2145
2146 v_hostname cz_servers.hostname%TYPE;
2147 v_db_listener_port cz_servers.db_listener_port%TYPE;
2148 v_instance_name cz_servers.instance_name%TYPE;
2149 v_local_name cz_servers.local_name%TYPE;
2150 v_fndnam cz_servers.fndnam%TYPE;
2151 v_global_identity cz_servers.global_identity%TYPE;
2152 v_notes cz_servers.notes%TYPE;
2153 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2154 v_import_enabled cz_servers.import_enabled%TYPE;
2155
2156 v_error_status VARCHAR2(4000) := '0';
2157 v_success char(1) := '0';
2158 v_warning char(1) := '1';
2159 v_error char(1) := '2';
2160 x_error BOOLEAN:=FALSE;
2161 xerror char(1);
2162 v_errorString VARCHAR2(1024) :='POPULATE_SERVER_CP : ';
2163
2164 v_server_count NUMBER := 0;
2165 v_server_id NUMBER;
2166 v_import_count NUMBER := 0;
2167 v_fnd_link_count NUMBER := 0;
2168 v_cursor NUMBER;
2169 v_NumRows NUMBER;
2170 v_dummy NUMBER;
2171 v_name VARCHAR2(10);
2172
2173 v_createstring varchar2(4000);
2174 v_bom varchar2(4000);
2175 v_item_master varchar2(4000);
2176
2177 BEGIN
2178
2179 v_hostname := LTRIM(RTRIM(HOSTNAME)) ;
2180 v_instance_name := LTRIM(RTRIM(INSTANCE_NAME));
2181 v_db_listener_port := DB_LISTENER_PORT ;
2182 v_local_name := LTRIM(RTRIM(LOCAL_NAME));
2183 v_fndnam := LTRIM(RTRIM(FNDNAM));
2184 v_global_identity := LTRIM(RTRIM(GLOBAL_IDENTITY));
2185 v_notes := LTRIM(RTRIM(NOTES));
2186 v_fndnam_link_name := LTRIM(RTRIM(FNDNAM_LINK_NAME));
2187 v_import_enabled := LTRIM(RTRIM(IMPORT_ENABLED));
2188
2189 Errbuf := '';
2190 Retcode := 0;
2191
2192 BEGIN
2193 SELECT count(*)
2194 INTO v_server_count
2195 FROM cz_servers
2196 WHERE cz_servers.local_name = v_local_name
2197 OR ( cz_servers.hostname = v_hostname
2198 AND cz_servers.instance_name = v_instance_name
2199 AND cz_servers.db_listener_port = v_db_listener_port) ;
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 v_server_count := 0;
2203 END;
2204
2205 IF (v_server_count = 0) THEN
2206 BEGIN
2207
2208 SELECT decode(import_enabled,'Y','1','N','0','0')
2209 INTO v_import_enabled
2210 FROM dual;
2211
2212 IF (v_import_enabled = '1') THEN
2213 SELECT count(*) INTO v_import_count FROM cz_servers
2214 WHERE import_enabled = '1';
2215 IF (V_IMPORT_count > 0) THEN
2216 Errbuf := cz_utils.get_text('CZ_IMP_SERVER_EXISTS_DEF');
2217 log_report(Errbuf);
2218 Retcode := '2';
2219 return;
2220 END IF;
2221 END IF;
2222 EXCEPTION
2223 WHEN OTHERS THEN
2224 v_import_count := 0;
2225 END;
2226
2227 IF v_fndnam_link_name IS NOT NULL THEN
2228 BEGIN
2229
2230 v_dummy := doesLinkExist(v_fndnam_link_name);
2231
2232 if (v_dummy > 0) THEN
2233 errbuf := cz_utils.get_text('CZ_DB_LINK_EXISTS','LINKNAME',v_fndnam_link_name);
2234 log_report(v_errorString || errbuf);
2235 Retcode := '2';
2236 return;
2237 END IF;
2238 EXCEPTION
2239 WHEN OTHERS THEN
2240 errbuf := cz_utils.get_text('CZ_DB_LINK_ERROR', 'SQLERRM', Sqlerrm);
2241 log_report(v_errorString || errbuf);
2242 Retcode := '2';
2243 return;
2244 END;
2245 END IF;
2246
2247 BEGIN
2248 SELECT cz_servers_s.NEXTVAL
2249 INTO v_server_id
2250 FROM dual;
2251 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting ' || v_server_id);
2252 INSERT INTO CZ_SERVERS (SERVER_LOCAL_ID,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
2253 INSTANCE_NAME, FNDNAM, GLOBAL_IDENTITY, NOTES,
2254 FNDNAM_LINK_NAME, IMPORT_ENABLED)
2255 VALUES (v_server_id,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
2256 INSTANCE_NAME, FNDNAM, V_GLOBAL_IDENTITY, NOTES,
2257 FNDNAM_LINK_NAME, v_IMPORT_ENABLED );
2258 COMMIT;
2259 EXCEPTION
2260 WHEN OTHERS THEN
2261 errbuf := cz_utils.get_text('CZ_CANNOT_INSERT_SERVER','SQLERRM',Sqlerrm);
2262 v_error_status := SQLERRM;
2263 log_report(v_errorString || errbuf);
2264 Retcode := '2';
2265 return;
2266 END;
2267 ELSE
2268 -- this message should have name of existing server with same configuration
2269 -- should change code to get the name of this server and add it to the message
2270 errbuf := cz_utils.get_text('CZ_SERVER_EXISTS_DEF');
2271 log_report(v_errorString || errbuf);
2272 Retcode := '0';
2273 return;
2274 END IF;
2275
2276 END populate_cz_server_cp;
2277
2278 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2279 ----------proc for altering records into cz_servers
2280
2281 PROCEDURE alter_cz_server_cp( Errbuf IN OUT NOCOPY VARCHAR2,
2282 Retcode IN OUT NOCOPY PLS_INTEGER,
2283 LOCAL_NAME IN VARCHAR2,
2284 HOSTNAME IN VARCHAR2,
2285 DB_LISTENER_PORT IN NUMBER,
2286 INSTANCE_NAME IN VARCHAR2,
2287 FNDNAM IN VARCHAR2,
2288 GLOBAL_IDENTITY IN VARCHAR2,
2289 NOTES IN VARCHAR2,
2290 FNDNAM_LINK_NAME IN VARCHAR2,
2291 IMPORT_ENABLED IN VARCHAR2
2292 )
2293 AS
2294 v_error_status VARCHAR2(4000) := '0';
2295 v_hostname cz_servers.hostname%TYPE;
2296 v_db_listener_port cz_servers.db_listener_port%TYPE;
2297 v_instance_name cz_servers.instance_name%TYPE;
2298 v_local_name cz_servers.local_name%TYPE;
2299 v_fndnam cz_servers.fndnam%TYPE;
2300 v_global_identity cz_servers.global_identity%TYPE;
2301 v_notes cz_servers.notes%TYPE;
2302 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2303 v_import_enabled cz_servers.import_enabled%TYPE;
2304
2305 v_server_count NUMBER := 0;
2306 v_server_id NUMBER ;
2307 v_import_count NUMBER := 0;
2308 v_dummy NUMBER := 0;
2309 v_name VARCHAR2(10);
2310 x_error BOOLEAN:=FALSE;
2311 xerror char(1);
2312 v_errorString VARCHAR2(1024) :='ALTER_SERVER_CP: ';
2313 v_createstring varchar2(4000);
2314
2315 v_views_status VARCHAR2(1);
2316 v_db_link CZ_SERVERS.fndnam_link_name%TYPE;
2317
2318 l_hostname cz_servers.hostname%TYPE;
2319 l_db_listener_port cz_servers.db_listener_port%TYPE;
2320 l_instance_name cz_servers.instance_name%TYPE;
2321 l_fndnam cz_servers.fndnam%TYPE;
2322 l_global_identity cz_servers.global_identity%TYPE;
2323 l_notes cz_servers.notes%TYPE;
2324 l_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2325 l_import_enabled cz_servers.import_enabled%TYPE;
2326 matching_local_name cz_servers.local_name%TYPE;
2327
2328 BEGIN
2329
2330 v_hostname := LTRIM(RTRIM(HOSTNAME)) ;
2331 v_instance_name := LTRIM(RTRIM(INSTANCE_NAME));
2332 v_db_listener_port := DB_LISTENER_PORT ;
2333 v_local_name := LTRIM(RTRIM(LOCAL_NAME));
2334 v_fndnam := LTRIM(RTRIM(FNDNAM));
2335 v_global_identity := LTRIM(RTRIM(GLOBAL_IDENTITY));
2336 v_notes := LTRIM(RTRIM(NOTES));
2337 v_fndnam_link_name := upper(LTRIM(RTRIM(FNDNAM_LINK_NAME)));
2338 v_import_enabled := LTRIM(RTRIM(IMPORT_ENABLED));
2339 Errbuf := NULL;
2340 Retcode := 0;
2341
2342 BEGIN
2343 SELECT SERVER_LOCAL_ID,hostname,db_listener_port,instance_name,
2344 fndnam,global_identity,notes,upper(fndnam_link_name),import_enabled
2345 INTO v_server_id,l_hostname,l_db_listener_port,l_instance_name,
2346 l_fndnam,l_global_identity,l_notes,l_fndnam_link_name,l_import_enabled
2347 FROM cz_servers
2348 WHERE cz_servers.local_name = v_local_name;
2349
2350 BEGIN
2351 BEGIN
2352 SELECT decode(import_enabled,'Y','1','N','0','0')
2353 INTO v_import_enabled
2354 FROM dual;
2355
2356 SELECT local_name into matching_local_name
2357 FROM cz_servers
2358 WHERE hostname = v_hostname
2359 AND instance_name = v_instance_name
2360 AND db_listener_port = v_db_listener_port
2361 AND fndnam = v_fndnam
2362 AND local_name <> v_local_name;
2363 IF (sql%FOUND) THEN
2364 errbuf := cz_utils.get_text('CZ_SERVER_EXISTS_MOD','SVRNAME',matching_local_name);
2365 log_report(v_errorString || errbuf);
2366 Retcode := '2';
2367 return;
2368 END IF;
2369 EXCEPTION
2370 WHEN OTHERS THEN
2371 null;
2372 END;
2373
2374 IF ( (upper(v_local_name) = 'LOCAL')
2375 and ((v_hostname <> l_hostname) or (v_db_listener_port <> l_db_listener_port)
2376 or (v_instance_name <> l_instance_name) or (v_fndnam <> l_fndnam)
2377 or (v_global_identity <> l_global_identity) or (v_notes <> l_notes)
2378 or (v_fndnam_link_name <> l_fndnam_link_name)) ) THEN
2379 -- cannot alter anything other than import_enabled for Local entry
2380 errbuf := cz_utils.get_text('CZ_IMP_ALTER_LOCAL_SERVER_ERR');
2381 log_report(v_errorString || errbuf);
2382 Retcode := '2';
2383 return;
2384 END IF;
2385
2386 IF (v_import_enabled = '1') THEN
2387 BEGIN
2388 SELECT count(*) INTO v_import_count FROM cz_servers
2389 WHERE import_enabled = '1'
2390 AND local_name <> v_local_name;
2391 IF (V_IMPORT_count > 0) THEN
2392 -- another server has import enabled, only one is allowed
2393 errbuf := cz_utils.get_text('CZ_IMP_SERVER_EXISTS_MOD');
2394 log_report(v_errorString || errbuf);
2395 Retcode := '2';
2396 return;
2397 END IF;
2398 EXCEPTION
2399 WHEN OTHERS THEN
2400 v_import_count := 0;
2401 END;
2402 END IF;
2403
2404 BEGIN
2405 IF ( ((v_hostname <> l_hostname) or (v_db_listener_port <> l_db_listener_port)
2406 or (v_instance_name <> l_instance_name)
2407 or (v_fndnam <> l_fndnam) or (v_fndnam_link_name <> l_fndnam_link_name)
2408 or ((v_import_enabled = '0') and (l_import_enabled = '1')) )
2409 and ((l_fndnam_link_name is NOT NULL)
2410 or (upper(v_local_name) = 'LOCAL')) ) THEN
2411
2412 -- IF (l_fndnam_link_name is NOT NULL) THEN
2413 IF (v_fndnam_link_name <> l_fndnam_link_name) THEN
2414 BEGIN
2415
2416 v_dummy := doesLinkExist(l_fndnam_link_name);
2417
2418 if (v_dummy > 0) then
2419 v_CreateString := 'drop database link ' || l_fndnam_link_name;
2420 EXECUTE IMMEDIATE v_CreateString;
2421 log_report(v_errorstring ||
2422 cz_utils.get_text('CZ_DB_LINK_DROPPED','LINKNAME',
2423 l_fndnam_link_name));
2424 END IF;
2425 EXCEPTION
2426 WHEN OTHERS THEN
2427 log_report(v_errorString ||
2428 cz_utils.get_text('CZ_DB_LINK_ERROR','SQLERRM',Sqlerrm));
2429 Retcode := '1';
2430 END;
2431 END IF;
2432
2433 -- if import_enabled changes from 1 to 0, drop views
2434 IF ((v_import_enabled = '0') and (l_import_enabled = '1')
2435 and ((l_fndnam_link_name is NOT NULL)
2436 or (upper(v_local_name) = 'LOCAL')) ) THEN
2437 BEGIN
2438 --
2439 -- don't drop views --
2440 -- ( this is an old code )--
2441 --
2442 --v_dummy := drop_exv_views;--
2443
2444 --
2445 -- just recreate them instead --
2446 --
2447 xerror:= create_exv_views('ERROR');
2448 IF (xerror<> '0') THEN
2449 -- error has already been logged
2450 Retcode := '1';
2451 END IF;
2452
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 Retcode := '2';
2456 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2457 log_report(v_errorString|| errbuf);
2458 return;
2459 END;
2460 END IF;
2461 END IF;
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 Retcode := '2';
2465 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2466 log_report(v_errorString|| errbuf);
2467 return;
2468 END;
2469
2470 BEGIN
2471 UPDATE CZ_SERVERS SET
2472 LOCAL_NAME = v_local_name,
2473 HOSTNAME = v_hostname,
2474 DB_LISTENER_PORT = v_db_listener_port,
2475 INSTANCE_NAME = v_instance_name,
2476 FNDNAM = v_fndnam ,
2477 GLOBAL_IDENTITY = v_global_identity ,
2478 NOTES = v_notes ,
2479 FNDNAM_LINK_NAME = v_fndnam_link_name ,
2480 IMPORT_ENABLED = v_import_enabled
2481 WHERE cz_servers.server_local_id = v_server_id ;
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 Retcode := '2';
2485 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2486 log_report(v_errorString|| errbuf);
2487 return;
2488 END;
2489
2490 -- if import_enabled changes from 0 to 1, for LOCAL entry, create views
2491 IF ((v_import_enabled = '1') and (l_import_enabled = '0')
2492 /*and (upper(v_local_name) = 'LOCAL')*/ ) THEN
2493 BEGIN
2494 xerror := create_exv_views(v_local_name);
2495 IF (xerror <> '0') THEN
2496 -- detailed error has already been logged
2497 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2498 Retcode := '2';
2499 return;
2500 END IF;
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 Retcode := '2';
2504 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2505 log_report(v_errorString|| errbuf);
2506 return;
2507 END;
2508 END IF;
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 v_server_count := 0;
2513 END;
2514 COMMIT;
2515
2516 EXCEPTION
2517 WHEN NO_DATA_FOUND THEN
2518 errbuf := cz_utils.get_text('CZ_SERVER_NOT_EXIST','SVRNAME',v_local_name);
2519 log_report(v_errorString || errbuf);
2520 RetCode := '1';
2521 WHEN OTHERS THEN
2522 Retcode := '2';
2523 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2524 log_report(v_errorString|| errbuf);
2525 END;
2526 END alter_cz_server_cp;
2527
2528
2529 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2530 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2531 ------------------------------------------------------------------------------
2532 PROCEDURE show_cz_servers_cp(Errbuf OUT NOCOPY VARCHAR2,
2533 Retcode OUT NOCOPY NUMBER)
2534 AS
2535
2536 CURSOR C_GETSERVERS IS
2537 SELECT LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT, INSTANCE_NAME,
2538 SERVER_DB_VERSION, FNDNAM, GLOBAL_IDENTITY, NOTES,FNDNAM_LINK_NAME, Decode(IMPORT_ENABLED, '1', 'Y', '0','N',IMPORT_ENABLED)
2539 FROM CZ_SERVERS
2540 WHERE SERVER_LOCAL_ID >= 0;
2541
2542 lLocalName CZ_SERVERS.LOCAL_NAME%TYPE;
2543 lHostName CZ_SERVERS.HOSTNAME%TYPE;
2544 lDbListenerPort CZ_SERVERS.DB_LISTENER_PORT%TYPE;
2545 lInstanceName CZ_SERVERS.INSTANCE_NAME%TYPE;
2546 lServerDbVersion CZ_SERVERS.SERVER_DB_VERSION%TYPE;
2547 lFndNam CZ_SERVERS.FNDNAM%TYPE;
2548 lGlobalIdentity CZ_SERVERS.GLOBAL_IDENTITY%TYPE;
2549 lNotes CZ_SERVERS.NOTES%TYPE;
2550 lFndNamLinkName CZ_SERVERS.FNDNAM_LINK_NAME%TYPE;
2551 lImportEnabled CZ_SERVERS.IMPORT_ENABLED%TYPE;
2552
2553 v_errorString VARCHAR2(1024) :='SHOW_SERVERS_CP: ';
2554
2555 BEGIN
2556 Errbuf:=NULL;
2557 Retcode:=0;
2558
2559 OPEN C_GETSERVERS;
2560 LOOP
2561 FETCH C_GETSERVERS INTO lLocalName,lHostName,lDbListenerPort,
2562 lInstanceName,lServerDbVersion,lFndNam,lGlobalIdentity,
2563 lNotes,lFndNamLinkName,lImportEnabled;
2564 EXIT WHEN C_GETSERVERS%NOTFOUND;
2565
2566 FND_FILE.PUT_LINE(FND_FILE.LOG,'Server Name='||lLocalName);
2567 FND_FILE.PUT_LINE(FND_FILE.LOG,'Host Name='||lHostName);
2568 FND_FILE.PUT_LINE(FND_FILE.LOG,'Port='||lDbListenerPort);
2569 FND_FILE.PUT_LINE(FND_FILE.LOG,'Instance Name='||lInstanceName);
2570 FND_FILE.PUT_LINE(FND_FILE.LOG,'Server Db Version='||lServerDbVersion);
2571 FND_FILE.PUT_LINE(FND_FILE.LOG,'FND Name='||lFndNam);
2572 FND_FILE.PUT_LINE(FND_FILE.LOG,'Global Name='||lGlobalIdentity);
2573 FND_FILE.PUT_LINE(FND_FILE.LOG,'Notes='||lNotes);
2574 FND_FILE.PUT_LINE(FND_FILE.LOG,'FND Link Name='||lFndNamLinkName);
2575 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Import Enabled='||Decode(lImportEnabled, '1', 'Y', '0','N',lImportEnabled));
2576 FND_FILE.PUT_LINE(FND_FILE.LOG,'Import Enabled=' || lImportEnabled);
2577 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------');
2578
2579 END LOOP;
2580 CLOSE C_GETSERVERS;
2581 EXCEPTION
2582 WHEN OTHERS THEN
2583 Errbuf:= cz_utils.get_text('CZ_SHOW_SERVERS_ERR','SQLERRM',Sqlerrm);
2584 log_report(v_errorString || errbuf);
2585 retcode := 2;
2586 END show_cz_servers_cp;
2587
2588 ------------------------------------------------------------------------------
2589 FUNCTION create_remote_hgrid_view(p_server_id IN NUMBER, p_fndnam_link_name IN VARCHAR2)
2590 RETURN VARCHAR2 IS
2591 v_success varchar2(1) := '0';
2592 v_error varchar2(1) := '2';
2593 v_link_name VARCHAR2(2000) := p_fndnam_link_name;
2594 BEGIN
2595
2596 IF(SUBSTR(v_link_name, 1, 1) = '@')THEN v_link_name := SUBSTR(v_link_name, 2); END IF;
2597
2598 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW cz_repos_folders_on_' || TO_CHAR(p_server_id) || '_v AS ' ||
2599 'SELECT * FROM cz_repository_main_hgrid_v@' || v_link_name ||
2600 ' WHERE object_type = ''FLD''';
2601 RETURN v_success;
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 log_report(cz_utils.get_text('CZ_HGRID_VIEW_ERROR_DTL', 'ERRORTEXT', SQLERRM));
2605 RETURN v_error;
2606 END;
2607 ---------- Create db link ----------------------------------------------------
2608 PROCEDURE create_link_cp(Errbuf IN OUT NOCOPY VARCHAR2,
2609 Retcode IN OUT NOCOPY PLS_INTEGER,
2610 LOCAL_NAME IN VARCHAR2,
2611 PASSWORD IN VARCHAR2)
2612 AS
2613
2614 v_hostname cz_servers.hostname%TYPE;
2615 v_instance_name cz_servers.instance_name%TYPE;
2616 v_db_listener_port cz_servers.db_listener_port%TYPE;
2617 v_fndnam cz_servers.fndnam%TYPE;
2618 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2619 v_local_name cz_servers.local_name%TYPE;
2620 v_server_db_version cz_servers.server_db_version%TYPE;
2621 v_import_enabled cz_servers.import_enabled%TYPE;
2622 v_server_id cz_servers.server_local_id%TYPE;
2623 v_cursor NUMBER;
2624 v_NumRows NUMBER;
2625 v_dummy NUMBER;
2626 xerror char(1);
2627 v_cur integer;
2628 v_res integer;
2629 v_link_status VARCHAR2(1);
2630 v_CreateString VARCHAR2(8000):='' ;
2631 v_dropString VARCHAR2(8000) := '';
2632 v_newString VARCHAR2(8000):='' ;
2633 v_errorString VARCHAR2(1024) :='CREATE_LINK_CP: ';
2634
2635 BEGIN
2636
2637 v_local_name := local_name;
2638
2639 BEGIN
2640 SELECT hostname,
2641 instance_name,
2642 db_listener_port,
2643 fndnam,
2644 fndnam_link_name,
2645 import_enabled,
2646 server_local_id
2647 INTO v_hostname,
2648 v_instance_name,
2649 v_db_listener_port,
2650 v_fndnam,
2651 v_fndnam_link_name,
2652 v_import_enabled,
2653 v_server_id
2654 FROM cz_servers
2655 WHERE cz_servers.local_name = v_local_name;
2656
2657 IF v_fndnam_link_name IS NOT NULL THEN
2658 BEGIN
2659
2660 v_dummy := doesLinkExist(v_fndnam_link_name);
2661
2662 --
2663 -- DB link already exists --
2664 --
2665 IF (v_dummy > 0) THEN
2666
2667 --
2668 -- check DB Link --
2669 --
2670 v_link_status:=isLinkAlive(v_fndnam_link_name);
2671
2672 IF v_link_status = LINK_IS_DOWN THEN
2673 errbuf := CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_fndnam_link_name);
2674 Retcode := 1;
2675 RETURN;
2676 END IF;
2677
2678 xerror := create_remote_hgrid_view(v_server_id, v_fndnam_link_name);
2679
2680 IF (xerror <> '0') THEN
2681 -- detailed error has already been logged
2682 Retcode := 2;
2683 END IF;
2684
2685 IF (v_import_enabled = '1') THEN
2686
2687 xerror := create_exv_views(v_local_name);
2688
2689 IF (xerror <> '0') THEN
2690 -- detailed error has already been logged
2691 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2692 Retcode := 2;
2693 return;
2694 END IF;
2695 END IF;
2696
2697 -- errbuf := cz_utils.get_text('CZ_LINK_FOR_CREATION_EXISTS','LINKNAME',v_fndnam_link_name);
2698 -- log_report(v_errorString || errbuf);
2699 -- retCode := 2;
2700 -- retCode := 1;
2701 -- return;
2702 ELSE
2703 BEGIN
2704 v_CreateString := 'create database link '||v_fndnam_link_name||' connect to '||v_fndnam||' identified by '||password||
2705 ' using ''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = '||v_hostname||')(PORT = '||v_db_listener_port||'))(CONNECT_DATA = (SID = '||v_instance_name||')))''';
2706 EXECUTE IMMEDIATE v_CreateString;
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2710 log_report(v_errorString || errbuf);
2711 retCode := 2;
2712 return;
2713 END;
2714
2715 BEGIN
2716 v_newString := 'SELECT version INTO :v_server_db_version FROM V$INSTANCE@' || v_fndnam_link_name ;
2717 -- || ' WHERE database_status in (''ACTIVE'', ''OPEN'') ';
2718 v_cur := dbms_sql.open_cursor;
2719 dbms_sql.parse(v_cur,v_newString,dbms_sql.native);
2720 dbms_sql.define_column(v_cur,1,v_server_db_version,40);
2721 v_res:=dbms_sql.execute(v_cur);
2722
2723 if (dbms_sql.fetch_rows(v_cur) > 0) then
2724 dbms_sql.column_value(v_cur,1,v_server_db_version);
2725 end if;
2726 dbms_sql.close_cursor(v_cur);
2727 EXCEPTION
2728 WHEN OTHERS THEN
2729 errbuf := cz_utils.get_text('CZ_DB_VERSION_NOT_FOUND', 'SQLERRM', Sqlerrm);
2730 Retcode := 1;
2731 log_report(v_errorString || errbuf);
2732 END;
2733
2734
2735 BEGIN
2736 UPDATE CZ_SERVERS SET
2737 SERVER_DB_VERSION = v_server_db_version
2738 WHERE cz_servers.local_name = v_local_name ;
2739 COMMIT;
2740 EXCEPTION
2741 WHEN OTHERS THEN
2742 errbuf := cz_utils.get_text('CZ_DB_VERSION_UPDATE_FAILURE', 'SQLERRM', Sqlerrm);
2743 Retcode := 2;
2744 log_report(v_errorString || errbuf);
2745 return;
2746 END;
2747 END IF;
2748
2749 v_link_status:=isLinkAlive(v_fndnam_link_name);
2750
2751 IF v_link_status = LINK_IS_DOWN THEN
2752 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_fndnam_link_name);
2753 Retcode := 2;
2754 RETURN;
2755 END IF;
2756
2757 xerror := create_remote_hgrid_view(v_server_id, v_fndnam_link_name);
2758
2759 IF (xerror <> '0') THEN
2760 -- detailed error has already been logged
2761 Retcode := 2;
2762 END IF;
2763
2764 IF(v_import_enabled = '1') THEN
2765
2766 IF (xerror <> '0') THEN
2767 -- detailed error has already been logged
2768 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2769 Retcode := 2;
2770 RETURN;
2771 END IF;
2772 END IF;
2773
2774 EXCEPTION
2775 WHEN OTHERS THEN
2776 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2777 log_report(v_errorString || errbuf);
2778 Retcode := 2;
2779 return;
2780 END;
2781 END IF;
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2785 log_report(v_errorString || errbuf);
2786 Retcode := 2;
2787 return;
2788 END;
2789 END create_link_cp;
2790 ------------------------------------------------------------------------------
2791
2792 --Register Maintain server concurrent process
2793
2794
2795 PROCEDURE register_maint_server_process
2796 (application_name IN VARCHAR2 , -- default 'Oracle Configurator',
2797 Request_Group IN VARCHAR2 default NULL,
2798 cz_schema IN VARCHAR2 default NULL)
2799 AS
2800
2801 var_schema VARCHAR2(40);
2802 ar_application_name VARCHAR2(50):='Oracle Configurator';
2803 ar_request_group VARCHAR2(50):=NULL;
2804 creation_failure EXCEPTION;
2805 exec_exists EXCEPTION;
2806 no_req_group EXCEPTION;
2807
2808 BEGIN
2809
2810
2811 BEGIN
2812 register_spx_process('Enable Remote Server',
2813 'CZCREATELNK',
2814 application_name,
2815 'Enable Remote Server for Configurator',
2816 'CZ_ORAAPPS_INTEGRATE.CREATE_LINK_CP',
2817 request_group,
2818 cz_schema);
2819 fnd_program.parameter(program_short_name=>'CZCREATELNK',
2820 application=>application_name,
2821 sequence=>1,
2822 parameter=>'LOCAL_NAME',
2823 description=>'Local Name',
2824 value_set=>'40 Chars',
2825 display_size=>20,
2826 description_size=>20,
2827 concatenated_description_size=>50,
2828 prompt=>'Server Local name');
2829 fnd_program.parameter(program_short_name=>'CZCREATELNK',
2830 application=>application_name,
2831 sequence=>1,
2832 parameter=>'PASSWORD',
2833 description=>'Password',
2834 value_set=>'40 Chars',
2835 display_size=>20,
2836 description_size=>20,
2837 concatenated_description_size=>50,
2838 prompt=>'Password');
2839
2840 EXCEPTION
2841 WHEN OTHERS THEN
2842 LOG_REPORT('Error : < Enable Remote Server for Configurator > REGISTRATION');
2843 END;
2844
2845
2846 BEGIN
2847 register_spx_process('Define Remote Server',
2848 'CZPOPULATESRV',
2849 application_name,
2850 'Define Remote Server',
2851 'CZ_ORAAPPS_INTEGRATE.POPULATE_CZ_SERVER_CP',
2852 request_group,
2853 cz_schema);
2854 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2855 application=>application_name,
2856 sequence=>1,
2857 parameter=>'LOCAL_NAME',
2858 description=>'Local Name',
2859 value_set=>'40 Chars',
2860 display_size=>20,
2861 description_size=>20,
2862 concatenated_description_size=>50,
2863 prompt=>'Local name');
2864 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2865 application=>application_name,
2866 sequence=>2,
2867 parameter=>'HOSTNAME',
2868 description=>'Host Name',
2869 value_set=>'90 Characters',
2870 display_size=>20,
2871 description_size=>20,
2872 concatenated_description_size=>50,
2873 prompt=>'Host name');
2874 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2875 application=>application_name,
2876 sequence=>3,
2877 parameter=>'DB_LISTENER_PORT',
2878 description=>'DB Listener Port',
2879 value_set=>'9 Number',
2880 display_size=>10,
2881 description_size=>10,
2882 concatenated_description_size=>10,
2883 prompt=>'DB Listener Port');
2884 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2885 application=>application_name,
2886 sequence=>4,
2887 parameter=>'INSTANCE_NAME',
2888 description=>'Instance Name',
2889 value_set=>'40 Chars',
2890 display_size=>20,
2891 description_size=>20,
2892 concatenated_description_size=>50,
2893 prompt=>'Instance name');
2894 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2895 application=>application_name,
2896 sequence=>5,
2897 parameter=>'FNDNAM',
2898 description=>'FND Name',
2899 value_set=>'40 Chars',
2900 display_size=>20,
2901 description_size=>20,
2902 concatenated_description_size=>50,
2903 prompt=>'Oracle Applications Schema Name (FNDNAM)');
2904 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2905 application=>application_name,
2906 sequence=>6,
2907 parameter=>'GLOBAL_IDENTITY',
2908 description=>'Global Identity',
2909 value_set=>'90 Characters',
2910 display_size=>20,
2911 description_size=>20,
2912 concatenated_description_size=>50,
2913 prompt=>'Global Identity');
2914 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2915 application=>application_name,
2916 sequence=>7,
2917 parameter=>'NOTES',
2918 description=>'Notes',
2919 value_set=>'90 Characters',
2920 display_size=>50,
2921 description_size=>50,
2922 concatenated_description_size=>50,
2923 prompt=>'Description');
2924 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2925 application=>application_name,
2926 sequence=>8,
2927 parameter=>'FND_LINK_NAME',
2928 description=>'FND Link Name',
2929 value_set=>'40 Chars',
2930 display_size=>20,
2931 description_size=>20,
2932 concatenated_description_size=>50,
2933 prompt=>'FND Link Name');
2934 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2935 application=>application_name,
2936 sequence=>9,
2937 parameter=>'IMPORT_ENABLED',
2938 description=>'Import Enabled',
2939 value_set=>'CZ_ENABLE_FLAG',
2940 display_size=>20,
2941 description_size=>20,
2942 concatenated_description_size=>50,
2943 prompt=>'Import Enabled');
2944
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 LOG_REPORT('Error : < Define Remote Server > REGISTRATION');
2948 END;
2949
2950 BEGIN
2951 register_spx_process('Modify Server Definition',
2952 'CZALTERSERVER',
2953 application_name,
2954 'Modify Remote Server',
2955 'CZ_ORAAPPS_INTEGRATE.ALTER_CZ_SERVER_CP',
2956 request_group,
2957 cz_schema);
2958
2959 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2960 application=>application_name,
2961 sequence=>1,
2962 parameter=>'LOCAL_NAME',
2963 description=>'Local Name',
2964 value_set=>'40 Chars',
2965 display_size=>20,
2966 description_size=>20,
2967 concatenated_description_size=>50,
2968 prompt=>'Local name');
2969 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2970 application=>application_name,
2971 sequence=>2,
2972 parameter=>'HOSTNAME',
2973 description=>'Host Name',
2974 value_set=>'90 Characters',
2975 display_size=>20,
2976 description_size=>20,
2977 concatenated_description_size=>50,
2978 prompt=>'Host name');
2979 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2980 application=>application_name,
2981 sequence=>3,
2982 parameter=>'DB_LISTENER_PORT',
2983 description=>'DB Listener Port',
2984 value_set=>'9 Number',
2985 display_size=>10,
2986 description_size=>10,
2987 concatenated_description_size=>10,
2988 prompt=>'DB Listener Port');
2989 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2990 application=>application_name,
2991 sequence=>4,
2992 parameter=>'INSTANCE_NAME',
2993 description=>'Instance Name',
2994 value_set=>'40 Chars',
2995 display_size=>20,
2996 description_size=>20,
2997 concatenated_description_size=>50,
2998 prompt=>'Instance name');
2999 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3000 application=>application_name,
3001 sequence=>5,
3002 parameter=>'FNDNAM',
3003 description=>'FND Name',
3004 value_set=>'40 Chars',
3005 display_size=>20,
3006 description_size=>20,
3007 concatenated_description_size=>50,
3008 prompt=>'Oracle Applications Schema Name (FNDNAM)');
3009 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3010 application=>application_name,
3011 sequence=>6,
3012 parameter=>'GLOBAL_IDENTITY',
3013 description=>'Global Identity',
3014 value_set=>'90 Characters',
3015 display_size=>20,
3016 description_size=>20,
3017 concatenated_description_size=>50,
3018 prompt=>'Global Identity');
3019 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3020 application=>application_name,
3021 sequence=>7,
3022 parameter=>'NOTES',
3023 description=>'Notes',
3024 value_set=>'90 Characters',
3025 display_size=>50,
3026 description_size=>50,
3027 concatenated_description_size=>50,
3028 prompt=>'Description');
3029 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3030 application=>application_name,
3031 sequence=>8,
3032 parameter=>'FND_LINK_NAME',
3033 description=>'FND Link Name',
3034 value_set=>'40 Chars',
3035 display_size=>20,
3036 description_size=>20,
3037 concatenated_description_size=>50,
3038 prompt=>'FND Link Name');
3039 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3040 application=>application_name,
3041 sequence=>9,
3042 parameter=>'IMPORT_ENABLED',
3043 description=>'Import Enabled',
3044 value_set=>'CZ_ENABLE_FLAG',
3045 display_size=>20,
3046 description_size=>20,
3047 concatenated_description_size=>50,
3048 prompt=>'Import Enabled');
3049 EXCEPTION
3050 WHEN OTHERS THEN
3051 LOG_REPORT('Error : < Modify Remote Server > REGISTRATION');
3052 END;
3053
3054 BEGIN
3055 register_spx_process('View Servers',
3056 'CZSHOWSERVERS',
3057 application_name,
3058 'Show Remote Servers',
3059 'CZ_ORAAPPS_INTEGRATE.SHOW_CZ_SERVERS_CP',
3060 request_group,
3061 cz_schema);
3062 EXCEPTION
3063 WHEN OTHERS THEN
3064 LOG_REPORT('Error : < Show Remote Servers > REGISTRATION');
3065 END;
3066
3067 END;
3068
3069
3070 ------------------------------------------------------------------------------------------
3071
3072 PROCEDURE RefreshSingleModel_cp
3073 (errbuf OUT NOCOPY VARCHAR2,
3074 retcode OUT NOCOPY NUMBER,
3075 iFolder_ID IN NUMBER,
3076 iModel_Id IN VARCHAR2,
3077 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0')
3078
3079 AS
3080
3081 lOrg_Id CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
3082 lTop_Id CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
3083 lCopy_Child_Models PLS_INTEGER;
3084
3085 v_link_status VARCHAR2(1);
3086 v_code VARCHAR2(1);
3087 v_db_link cz_servers.fndnam_link_name%TYPE;
3088 v_local_name cz_servers.local_name%TYPE;
3089 v_import_enabled cz_servers.import_enabled%TYPE;
3090 v_ret BOOLEAN := false;
3091
3092 BEGIN
3093 retcode:=0;
3094 errbuf:='';
3095
3096 BEGIN
3097 SELECT rtrim(substr(orig_sys_ref,instr(orig_sys_ref,':',1,1)+1,length(substr(orig_sys_ref,instr(orig_sys_ref,':',1,1)+1)) -
3098 length(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2))) )) ,
3099 rtrim(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2)+1))
3100 INTO lOrg_Id, lTop_Id
3101 FROM cz_devl_projects
3102 WHERE devl_project_id = iModel_Id
3103 AND deleted_flag = '0';
3104
3105 select decode(copy_child_models,'Y',0,'N',1,0) into lCopy_Child_Models from dual;
3106
3107 EXCEPTION
3108 WHEN NO_DATA_FOUND THEN
3109 RAISE CZ_ADMIN.IMP_MODEL_NOT_FOUND;
3110 END;
3111
3112 BEGIN
3113 SELECT local_name,fndnam_link_name,import_enabled
3114 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3115 WHERE import_enabled='1';
3116 EXCEPTION
3117 WHEN TOO_MANY_ROWS THEN
3118 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3119 WHEN NO_DATA_FOUND THEN
3120 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3121 END;
3122
3123 ---- check DB Link ----
3124 v_link_status:=isLinkAlive(v_db_link);
3125
3126 IF v_link_status=LINK_IS_DOWN THEN
3127 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3128 ELSE
3129 ---- recreate views based on remote tables ----
3130 v_code:=create_exv_views(v_local_name);
3131 compile_Dependents('CZ_IMP%');
3132
3133 EXECUTE IMMEDIATE
3134 'BEGIN cz_imp_all.goSingleBill_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE'||
3135 ',:lORG_ID,:lTOP_ID,:lCOPY_CHILD_MODELS,:iMODEL_ID); END;'
3136 USING lORG_ID,lTOP_ID,lCOPY_CHILD_MODELS,iMODEL_ID;
3137
3138 errbuf:=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3139 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3140 END IF;
3141
3142 EXCEPTION
3143 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3144 retcode:=2;
3145 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
3146 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3147 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3148 retcode:=2;
3149 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3150 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3151 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3152 retcode:=2;
3153 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3154 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3155 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3156 retcode:=2;
3157 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3158 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3159 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3160 retcode:=2;
3161 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3162 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3163 WHEN CZ_ADMIN.IMP_MODEL_NOT_FOUND THEN
3164 retcode:=2;
3165 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_NOT_FOUND', 'MODELID', iModel_Id);
3166 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3167 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3168 retcode:=2;
3169 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3170 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3171 WHEN OTHERS THEN
3172 retcode:=2;
3173 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3174 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3175 END RefreshSingleModel_cp;
3176
3177 ------------------------------------------------------------------------------------------
3178 PROCEDURE RemoveModel_cp
3179 (errbuf OUT NOCOPY VARCHAR2,
3180 retcode OUT NOCOPY NUMBER,
3181 iFolder_ID IN NUMBER,
3182 iModel_Id IN VARCHAR2,
3183 iImportEnabled IN VARCHAR2) IS
3184
3185 BEGIN
3186 retcode:=0;
3187 errbuf:='';
3188
3189 UPDATE cz_xfr_project_bills
3190 SET deleted_flag=Decode(iImportEnabled, 'Y', '0', 'N', '1', iImportEnabled)
3191 WHERE model_ps_node_id = iModel_Id;
3192
3193 COMMIT;
3194
3195 EXCEPTION
3196 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3197 retcode:=2;
3198 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
3199 log_report('REMOVEMODEL_CP: ' || errbuf);
3200 WHEN OTHERS THEN
3201 retcode:=2;
3202 errbuf:=cz_utils.get_text('CZ_IMP_CHANGE_PARAMS_ERR','SQLERRM',sqlerrm);
3203 log_report('REMOVEMODEL_CP: ' || errbuf);
3204 END;
3205
3206 ------------------------------------------------------------------------------------------
3207
3208 PROCEDURE go_cp
3209 (errbuf OUT NOCOPY VARCHAR2,
3210 retcode OUT NOCOPY NUMBER ) IS
3211 v_link_status VARCHAR2(1);
3212 v_code VARCHAR2(1);
3213 v_ret BOOLEAN:=FALSE;
3214 v_db_link cz_servers.fndnam_link_name%TYPE;
3215 v_local_name cz_servers.local_name%TYPE;
3216 v_import_enabled cz_servers.import_enabled%TYPE;
3217 BEGIN
3218 BEGIN
3219 SELECT local_name,fndnam_link_name,import_enabled
3220 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3221 WHERE import_enabled='1';
3222 EXCEPTION
3223 WHEN TOO_MANY_ROWS THEN
3224 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3225 WHEN NO_DATA_FOUND THEN
3226 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3227 END;
3228
3229 ---- check DB Link ----
3230 v_link_status:=isLinkAlive(v_db_link);
3231
3232 IF v_link_status=LINK_IS_DOWN THEN
3233 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3234 ELSE
3235 v_code:=create_exv_views(v_local_name);
3236 compile_Dependents('CZ_IMP%');
3237 EXECUTE IMMEDIATE
3238 'BEGIN CZ_IMP_ALL.GO_CP(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE); END;';
3239 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3240 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3241 END IF;
3242 EXCEPTION
3243 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3244 retcode:=2;
3245 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
3246 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3247 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3248 retcode:=2;
3249 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3250 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3251 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3252 retcode:=2;
3253 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3254 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3255 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3256 retcode:=2;
3257 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3258 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3259 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3260 retcode:=2;
3261 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3262 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3263 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3264 retcode:=2;
3265 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3266 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3267 WHEN OTHERS THEN
3268 retcode:=2;
3269 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3270 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3271 END;
3272
3273 ------------------------------------------------------------------------------------------
3274
3275 PROCEDURE PopulateModels_cp
3276 (errbuf OUT NOCOPY VARCHAR2,
3277 retcode OUT NOCOPY NUMBER,
3278 sOrg_ID IN VARCHAR2,
3279 dsOrg_ID IN VARCHAR2,
3280 sFrom IN VARCHAR2,
3281 sTo IN VARCHAR2,
3282 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
3283 v_link_status VARCHAR2(1);
3284 v_code VARCHAR2(1);
3285 v_ret BOOLEAN:=FALSE;
3286 v_db_link cz_servers.fndnam_link_name%TYPE;
3287 v_local_name cz_servers.local_name%TYPE;
3288 v_import_enabled cz_servers.import_enabled%TYPE;
3289 BEGIN
3290 BEGIN
3291 SELECT local_name,fndnam_link_name,import_enabled
3292 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3293 WHERE import_enabled='1';
3294 EXCEPTION
3295 WHEN TOO_MANY_ROWS THEN
3296 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3297 WHEN NO_DATA_FOUND THEN
3298 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3299 END;
3300
3301 ---- check DB Link ----
3302 v_link_status:=isLinkAlive(v_db_link);
3303
3304 IF v_link_status=LINK_IS_DOWN THEN
3305 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3306 ELSE
3307 v_code:=create_exv_views(v_local_name);
3308 compile_Dependents('CZ_IMP%');
3309
3310 EXECUTE IMMEDIATE
3311 'BEGIN CZ_IMP_ALL.PopulateModels_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3312 ':sOrg_ID,:dsOrg_ID,:sFrom,:sTo,:COPY_CHILD_MODELS); END;'
3313 USING sOrg_ID,dsOrg_ID,sFrom,sTo,COPY_CHILD_MODELS;
3314 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3315 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3316 END IF;
3317 EXCEPTION
3318 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3319 retcode:=2;
3320 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
3321 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3322 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3323 retcode:=2;
3324 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3325 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3326 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3327 retcode:=2;
3328 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3329 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3330 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3331 retcode:=2;
3332 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3333 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3334 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3335 retcode:=2;
3336 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3337 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3338 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3339 retcode:=2;
3340 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3341 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3342 WHEN OTHERS THEN
3343 retcode:=2;
3344 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3345 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3346 END;
3347
3348 ------------------------------------------------------------------------------------------
3349 -- Bug10011026 This procedure was introduced for Sun implementation where new Concurrent Program
3350 -- was introduced with same (segment1) value for sFrom and sTo parameters and cursor in original
3351 -- procedure was changed to go against only cz_exv_mtl_system_items for performance improvement
3352
3353 PROCEDURE PopulateSingleModel_cp
3354 (errbuf OUT NOCOPY VARCHAR2,
3355 retcode OUT NOCOPY NUMBER,
3356 sOrg_ID IN VARCHAR2,
3357 dsOrg_ID IN VARCHAR2,
3358 sFrom IN VARCHAR2,
3359 sTo IN VARCHAR2,
3360 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
3361 v_link_status VARCHAR2(1);
3362 v_code VARCHAR2(1);
3363 v_ret BOOLEAN:=FALSE;
3364 v_db_link cz_servers.fndnam_link_name%TYPE;
3365 v_local_name cz_servers.local_name%TYPE;
3366 v_import_enabled cz_servers.import_enabled%TYPE;
3367 BEGIN
3368 BEGIN
3369 SELECT local_name,fndnam_link_name,import_enabled
3370 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3371 WHERE import_enabled='1';
3372 EXCEPTION
3373 WHEN TOO_MANY_ROWS THEN
3374 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3375 WHEN NO_DATA_FOUND THEN
3376 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3377 END;
3378
3379 ---- check DB Link ----
3380 v_link_status:=isLinkAlive(v_db_link);
3381
3382 IF v_link_status=LINK_IS_DOWN THEN
3383 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3384 ELSE
3385 v_code:=create_exv_views(v_local_name);
3386 compile_Dependents('CZ_IMP%');
3387
3388 EXECUTE IMMEDIATE
3389 'BEGIN CZ_IMP_ALL.PopulateSingleModel_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3390 ':sOrg_ID,:dsOrg_ID,:sFrom,:sTo,:COPY_CHILD_MODELS); END;'
3391 USING sOrg_ID,dsOrg_ID,sFrom,sTo,COPY_CHILD_MODELS;
3392 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3393 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3394 END IF;
3395 EXCEPTION
3396 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3397 retcode:=2;
3398 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
3399 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3400 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3401 retcode:=2;
3402 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3403 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3404 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3405 retcode:=2;
3406 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3407 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3408 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3409 retcode:=2;
3410 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3411 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3412 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3413 retcode:=2;
3414 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3415 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3416 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3417 retcode:=2;
3418 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3419 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3420 WHEN OTHERS THEN
3421 retcode:=2;
3422 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3423 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateSingleModel_cp',11276,NULL);
3424 END;
3425
3426 ------------------------------------------------------------------------------------------
3427
3428
3429 PROCEDURE check_model_similarity_cp
3430 (errbuf OUT NOCOPY VARCHAR2,
3431 retcode OUT NOCOPY NUMBER,
3432 p_TARGET_INSTANCE IN VARCHAR2,
3433 p_FOLDER_ID IN NUMBER,
3434 p_MODEL_ID IN NUMBER) IS
3435
3436 BEGIN
3437 EXECUTE IMMEDIATE
3438 'BEGIN CZ_BOM_SYNCH.report_model_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3439 ':p_TARGET_INSTANCE,:p_MODEL_ID); END;'
3440 USING p_TARGET_INSTANCE,p_MODEL_ID;
3441 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3442 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3443 EXCEPTION
3444 WHEN OTHERS THEN
3445 retcode:=2;
3446 errbuf:=SQLERRM;
3447 log_report('check_model_similarity_cp: ' || errbuf);
3448 END;
3449
3450 ------------------------------------------------------------------------------------------
3451
3452 PROCEDURE check_all_models_similarity_cp
3453 (errbuf OUT NOCOPY VARCHAR2,
3454 retcode OUT NOCOPY NUMBER,
3455 p_TARGET_INSTANCE IN VARCHAR2) IS
3456
3457 BEGIN
3458 EXECUTE IMMEDIATE
3459 'BEGIN CZ_BOM_SYNCH.report_all_models_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3460 ':p_TARGET_INSTANCE); END;'
3461 USING p_TARGET_INSTANCE;
3462 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3463 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3464 EXCEPTION
3465 WHEN OTHERS THEN
3466 retcode:=2;
3467 errbuf:=SQLERRM;
3468 log_report('check_all_models_similarity_cp: ' || errbuf);
3469 END;
3470
3471 ------------------------------------------------------------------------------------------
3472
3473 PROCEDURE sync_all_models_cp
3474 (errbuf OUT NOCOPY VARCHAR2,
3475 retcode OUT NOCOPY NUMBER) IS
3476 v_ret BOOLEAN;
3477 v_TARGET_INSTANCE CZ_SERVERS.INSTANCE_NAME%TYPE;
3478 BEGIN
3479 SELECT LOCAL_NAME
3480 INTO v_TARGET_INSTANCE
3481 FROM CZ_SERVERS
3482 WHERE import_enabled='1';
3483
3484 EXECUTE IMMEDIATE
3485 'BEGIN CZ_BOM_SYNCH.synchronize_all_models_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3486 ':v_TARGET_INSTANCE); END;'
3487 USING v_TARGET_INSTANCE;
3488 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3489 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3490 EXCEPTION
3491 WHEN TOO_MANY_ROWS THEN
3492 retcode:=2;
3493 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3494 log_report('sync_all_models_cp: ' || errbuf);
3495 WHEN NO_DATA_FOUND THEN
3496 retcode:=2;
3497 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3498 log_report('sync_all_models_cp: ' || errbuf);
3499 WHEN OTHERS THEN
3500 retcode:=2;
3501 errbuf:=SQLERRM;
3502 log_report('sync_all_models_cp: ' || errbuf);
3503 END;
3504
3505 ------------------------------------------------------------------------------------------
3506
3507 PROCEDURE Repopulate_cp
3508 (errbuf OUT NOCOPY VARCHAR2,
3509 retcode OUT NOCOPY NUMBER,
3510 p_FOLDER_ID IN NUMBER,
3511 p_MODEL_ID IN NUMBER) IS
3512 v_err INTEGER;
3513 v_ret BOOLEAN;
3514 BEGIN
3515 CZ_POPULATORS_PKG.Repopulate(p_MODEL_ID,'1','1','1',v_err);
3516
3517 --
3518 -- return last error message if error
3519 --
3520 IF v_err<>0 THEN
3521 FOR i IN(SELECT message FROM CZ_DB_LOGS
3522 WHERE run_id=v_err ORDER BY logtime)
3523 LOOP
3524 errbuf:=i.message;
3525 END LOOP;
3526 retcode:=1;
3527 END IF;
3528
3529 EXCEPTION
3530 WHEN OTHERS THEN
3531 retcode:=2;
3532 errbuf:=SQLERRM;
3533 log_report('Repopulate_cp: ' || errbuf);
3534 END;
3535
3536 ------------------------------------------------------------------------------------------
3537 -- LA 9595142
3538 PROCEDURE UpdateImportFlag_cp
3539 (errbuf OUT NOCOPY VARCHAR2,
3540 retcode OUT NOCOPY NUMBER,
3541 iFolder_ID IN NUMBER,
3542 iModel_Id IN VARCHAR2,
3543 iImportEnabled IN VARCHAR2) IS
3544
3545 retStatus VARCHAR2(3);
3546
3547 BEGIN
3548 retcode:=0;
3549 errbuf:='';
3550
3551 IF (iImportEnabled = 'Y') THEN
3552 CZ_CF_API.enable_model_for_import(iModel_Id, retStatus);
3553 ELSIF (iImportEnabled = 'N') THEN
3554 CZ_CF_API.disable_model_for_import(iModel_Id, retStatus);
3555 END IF;
3556
3557 IF retStatus = 'ERR' THEN
3558 retcode := 2;
3559 errbuf := 'Error while updating import enabled flag for the model';
3560 log_report('UpdateImportFlag_cp: ' || errbuf);
3561 END IF;
3562
3563 EXCEPTION
3564 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3565 retcode:=2;
3566 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
3567 log_report('UpdateImportFlag_cp: ' || errbuf);
3568 WHEN OTHERS THEN
3569 retcode:=2;
3570 errbuf:=cz_utils.get_text('CZ_IMP_CHANGE_PARAMS_ERR','SQLERRM',sqlerrm);
3571 log_report('UpdateImportFlag_cp: ' || errbuf);
3572 END;
3573
3574
3575 END;