[Home] [Help]
PACKAGE BODY: APPS.CZ_ORAAPPS_INTEGRATE
Source
1 PACKAGE BODY CZ_ORAAPPS_INTEGRATE AS
2 /* $Header: czcaintb.pls 120.16 2008/01/31 16:14:08 lamrute 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 IF x_mtl_system_items_tl_exists THEN
1619 v_intl_text_string_1:= 'CREATE OR REPLACE VIEW CZ_EXV_INTL_TEXT AS ' ||
1620 'SELECT distinct T.DESCRIPTION, T.LANGUAGE, T.SOURCE_LANG, ' ||
1621 'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
1622 'P.TOP_ITEM_ID, P.EXPLOSION_TYPE, P.ORGANIZATION_ID, ' ||
1623 'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
1624 'FROM CZ_XFR_PROJECT_BILLS P, ' ||
1625 'BOM_EXPLOSIONS' || v_fndnam_link_name || ' B, ' ||
1626 'BOM_BILL_OF_MATERIALS' || v_fndnam_link_name || ' BBM, ' ||
1627 'MTL_SYSTEM_ITEMS_TL' || v_fndnam_link_name || ' T ' ||
1628 'WHERE P.ORGANIZATION_ID=BBM.ORGANIZATION_ID ' ||
1629 'AND P.TOP_ITEM_ID=BBM.ASSEMBLY_ITEM_ID ' ||
1630 'AND P.EXPLOSION_TYPE=B.EXPLOSION_TYPE ' ||
1631 'AND T.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
1632 'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1633 'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1634 'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
1635 'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
1636 'AND P.DELETED_FLAG=''0'' ' ||
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 v_intl_text_string_2:= 'CREATE OR REPLACE VIEW CZ_EXV_INTL_TEXT AS ' ||
1645 'SELECT distinct T.DESCRIPTION, F.LANGUAGE_CODE, F.LANGUAGE_CODE AS SOURCE_LANG, ' ||
1646 'B.COMPONENT_SEQUENCE_ID,B.COMMON_COMPONENT_SEQUENCE_ID, ' ||
1647 'P.TOP_ITEM_ID, P.EXPLOSION_TYPE, P.ORGANIZATION_ID, ' ||
1648 'B.COMPONENT_ITEM_ID, B.BILL_SEQUENCE_ID, B.COMPONENT_CODE ' ||
1649 'FROM CZ_XFR_PROJECT_BILLS P, ' ||
1650 'BOM_EXPLOSIONS'|| v_fndnam_link_name || ' B, ' ||
1651 'BOM_BILL_OF_MATERIALS'|| v_fndnam_link_name || ' BBM, ' ||
1652 'MTL_SYSTEM_ITEMS'|| v_fndnam_link_name || ' T, ' ||
1653 'FND_LANGUAGES'|| v_fndnam_link_name || ' F ' ||
1654 'WHERE P.ORGANIZATION_ID=BBM.ORGANIZATION_ID ' ||
1655 'AND P.TOP_ITEM_ID=BBM.ASSEMBLY_ITEM_ID ' ||
1656 'AND P.EXPLOSION_TYPE=B.EXPLOSION_TYPE ' ||
1657 'AND M.INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID ' ||
1658 'AND T.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1659 'AND BBM.ORGANIZATION_ID = B.ORGANIZATION_ID ' ||
1660 'AND BBM.BILL_SEQUENCE_ID = B.TOP_BILL_SEQUENCE_ID ' ||
1661 'AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL ' ||
1662 'AND P.DELETED_FLAG=''0'' ' ||
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 attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
1851 'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
1852 'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
1853 'AND usgs.ATTR_ID = attrs.ATTR_ID '||
1854 'AND usgs.ENABLED_FLAG = ''Y'' '||
1855 'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
1856 ' assocs.DATA_LEVEL=''ITEM_LEVEL'' AND attrs.ENABLED_FLAG=''Y'' '|| v_rownum;
1857
1858 EXECUTE IMMEDIATE v_apc_props_string;
1859
1860 v_apc_prop_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS '||
1861 ' SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL'|| v_fndnam_link_name||' c '||
1862 ' WHERE EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES a WHERE a.attr_group_id=c.attr_group_id) AND '||
1863 ' EXISTS(SELECT NULL FROM CZ_EXV_APC_PROPERTIES b WHERE b.item_catalog_group_id=c.item_catalog_group_id)' || v_rownum;
1864
1865 v_apc_prop_values_string := 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS '||
1866 'SELECT * FROM EGO_MTL_SY_ITEMS_EXT_VL '|| v_fndnam_link_name||' c '||
1867 ' WHERE EXISTS '||
1868 '(SELECT NULL FROM '||
1869 'EGO_ATTR_GROUPS_V '||v_fndnam_link_name||' attrgrps '||
1870 ',EGO_ATTRS_V '||v_fndnam_link_name||' attrs '||
1871 ',EGO_ITMATTR_APPL_USGS_B '||v_fndnam_link_name||' usgs '||
1872 ',FND_OBJECTS '||v_fndnam_link_name||' objs '||
1873 'WHERE attrgrps.attr_group_id=c.attr_group_id AND objs.OBJ_NAME = ''EGO_ITEM'' '||
1874 'AND attrgrps.ATTR_GROUP_TYPE = attrs.ATTR_GROUP_TYPE '||
1875 'AND attrgrps.ATTR_GROUP_NAME = attrs.ATTR_GROUP_NAME '||
1876 'AND attrs.APPLICATION_ID = 431 AND attrgrps.APPLICATION_ID = 431 '||
1877 'AND usgs.ATTR_ID = attrs.ATTR_ID '||
1878 'AND usgs.ENABLED_FLAG = ''Y'' '||
1879 'AND usgs.APPLICATION_ID = 708 AND attrgrps.attr_group_type=''EGO_ITEMMGMT_GROUP'' AND '||
1880 ' attrs.ENABLED_FLAG=''Y'') '||v_rownum;
1881
1882 EXECUTE IMMEDIATE v_apc_prop_values_string;
1883
1884 EXCEPTION
1885 WHEN OTHERS THEN
1886
1887 log_report('APC is not installed, stub views will be created.'||
1888 cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR_DTL','SQLERRM',Sqlerrm));
1889
1890 v_apc_props_string :=
1891 'CREATE OR REPLACE VIEW CZ_EXV_APC_PROPERTIES AS ' ||
1892 ' SELECT ' ||
1893 ' -1 AS ATTR_GROUP_ID ' ||
1894 ' ,-1 AS ITEM_CATALOG_GROUP_ID ' ||
1895 ' ,-1 AS ATTR_ID ' ||
1896 ' ,-1 AS APPLICATION_ID ' ||
1897 ' ,-1 AS ATTR_GROUP_TYPE ' ||
1898 ' ,''*'' AS ATTR_GROUP_NAME ' ||
1899 ' ,''*'' AS ATTR_NAME ' ||
1900 ' ,''*'' AS ATTR_DISPLAY_NAME ' ||
1901 ' ,''*'' AS DESCRIPTION ' ||
1902 ' ,''*'' AS DATABASE_COLUMN ' ||
1903 ' ,''*'' AS DATA_TYPE_CODE ' ||
1904 ' ,-1 AS SEQUENCE ' ||
1905 ' ,''*'' AS UNIQUE_KEY_FLAG ' ||
1906 ' ,''*'' AS DEFAULT_VALUE ' ||
1907 ' ,''*'' AS INFO_1 ' ||
1908 ' ,''*'' AS UOM_CLASS ' ||
1909 ' ,-1 AS CONTROL_LEVEL ' ||
1910 ' ,-1 AS VALUE_SET_ID ' ||
1911 ' ,''*'' AS VALUE_SET_NAME ' ||
1912 ' ,''*'' AS FORMAT_CODE ' ||
1913 ' ,-1 AS MAXIMUM_SIZE ' ||
1914 ' ,''*'' AS VALIDATION_CODE ' ||
1915 ' ,''*'' AS LONGLIST_FLAG ' ||
1916 ' ,''*'' AS ENABLED_FLAG ' ||
1917 ' ,''*'' AS ENABLED_MEANING ' ||
1918 ' ,''*'' AS REQUIRED_FLAG ' ||
1919 ' ,''*'' AS REQUIRED_MEANING ' ||
1920 ' ,''*'' AS SEARCH_FLAG ' ||
1921 ' ,''*'' AS SEARCH_MEANING ' ||
1922 ' ,''*'' AS DISPLAY_CODE ' ||
1923 ' ,''*'' AS DISPLAY_MEANING ' ||
1924 ' ,''*'' AS ATTRIBUTE_CODE ' ||
1925 ' ,''*'' AS VIEW_IN_HIERARCHY_CODE ' ||
1926 ' ,''*'' AS EDIT_IN_HIERARCHY_CODE ' ||
1927 ' ,''*'' AS CUSTOMIZATION_LEVEL ' ||
1928 ' FROM dual';
1929 EXECUTE IMMEDIATE v_apc_props_string;
1930
1931 v_apc_prop_values_string :=
1932 'CREATE OR REPLACE VIEW CZ_EXV_ITEM_APC_PROP_VALUES AS ' ||
1933 'SELECT ' ||
1934 '-1 AS EXTENSION_ID ' ||
1935 ',-1 AS ORGANIZATION_ID ' ||
1936 ',-1 AS INVENTORY_ITEM_ID ' ||
1937 ',-1 AS REVISION_ID ' ||
1938 ',-1 AS ITEM_CATALOG_GROUP_ID ' ||
1939 ',-1 AS ATTR_GROUP_ID ' ||
1940 ',''*'' AS SOURCE_LANG ' ||
1941 ',''*'' AS LANGUAGE ' ||
1942 'FROM dual';
1943 EXECUTE IMMEDIATE v_apc_prop_values_string;
1944
1945 END;
1946
1947
1948 return v_success;
1949
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 log_report(v_errorString || cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR_DTL','SQLERRM',Sqlerrm));
1953 return v_error;
1954 END create_exv_views;
1955
1956 ------------------------------------------------------------------------------
1957
1958 PROCEDURE recreate_exv_views
1959 (p_link_status OUT NOCOPY VARCHAR2,
1960 p_db_link OUT NOCOPY VARCHAR2,
1961 p_do_compile IN VARCHAR2 -- DEFAULT '1'
1962 ) IS
1963 v_code VARCHAR2(1);
1964 v_import_enabled cz_servers.import_enabled%TYPE;
1965 v_local_name cz_servers.local_name%TYPE;
1966 BEGIN
1967 BEGIN
1968 SELECT local_name,fndnam_link_name,import_enabled
1969 INTO v_local_name,p_db_link,v_import_enabled FROM CZ_SERVERS
1970 WHERE import_enabled='1';
1971
1972 ---- check DB Link ----
1973 p_link_status:=isLinkAlive(p_db_link);
1974 EXCEPTION
1975 WHEN NO_DATA_FOUND THEN
1976 p_link_status:=LINK_IS_DOWN;
1977 END;
1978
1979 IF p_link_status=LINK_IS_DOWN THEN
1980
1981 ---- recreate views based on local tables ----
1982 v_code:=create_exv_views('ERROR');
1983
1984 IF v_code='0' THEN
1985 p_link_status:=LINK_IS_DOWN;
1986 ELSE
1987 p_link_status:=v_code;
1988 END IF;
1989
1990 END IF;
1991
1992 IF p_link_status=LINK_WORKS THEN
1993
1994 ---- recreate views based on remote tables ----
1995 v_code:=create_exv_views(v_local_name);
1996
1997 IF v_code='0' THEN
1998 p_link_status:=LINK_WORKS;
1999 ELSE
2000 p_link_status:=v_code;
2001 END IF;
2002 END IF;
2003
2004 IF UPPER(p_do_compile) IN ('1','Y','YES') THEN
2005 compile_Dependents('CZ_IMP%');
2006 END IF;
2007 COMMIT;
2008 EXCEPTION
2009 WHEN TOO_MANY_ROWS THEN
2010 log_report('recreate_exv_views: ' || CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS'));
2011 WHEN OTHERS THEN
2012 log_report(SQLERRM);
2013 END;
2014
2015 ------------------------------------------------------------------------------
2016
2017 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2018 ---------- recreate extraction views concurrent program
2019
2020 PROCEDURE recreate_exv_views_cp(errbuf IN OUT NOCOPY VARCHAR2,
2021 retcode IN OUT NOCOPY INTEGER) IS
2022 v_views_status VARCHAR2(1);
2023 v_db_link CZ_SERVERS.fndnam_link_name%TYPE;
2024 BEGIN
2025 retcode:=0;
2026 recreate_exv_views(v_views_status,v_db_link, 1);
2027 IF v_views_status=LINK_WORKS THEN
2028 errbuf:='';
2029 END IF;
2030 IF v_views_status=LINK_IS_DOWN THEN
2031 retcode:=2;
2032 errbuf:=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
2033 END IF;
2034 END;
2035
2036 ------------------------------------------------------------------------------
2037
2038 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2039 ---------- drop exv views
2040
2041 FUNCTION drop_exv_views
2042
2043 RETURN VARCHAR2
2044 AS
2045
2046 v_bom_string varchar2(4000);
2047 v_item_master_string varchar2(4000);
2048 v_mtl_system_items_string varchar2(4000);
2049 v_items_string varchar2(4000);
2050 v_addresses_string varchar2(4000);
2051 v_address_uses_string varchar2(4000);
2052 v_customers_string varchar2(4000);
2053 v_contacts_string varchar2(4000);
2054 v_price_list_string varchar2(4000);
2055 v_price_list_line_string varchar2(4000);
2056 v_end_user_string varchar2(4000);
2057 v_item_properties_string varchar2(4000);
2058 v_item_property_values_string varchar2(4000);
2059 v_item_types_string varchar2(4000);
2060 v_organization_string varchar2(4000);
2061 v_string varchar2(4000);
2062
2063 v_success char(1) := '0';
2064 v_warning char(1) := '1';
2065 v_error char(1) := '2';
2066 v_errorString VARCHAR2(1024) := 'DROP_EXV_VIEWS : ';
2067
2068 BEGIN
2069
2070 v_bom_string := 'DROP VIEW CZ_EXV_BILL_OF_MATERIALS';
2071 EXECUTE IMMEDIATE v_bom_string;
2072
2073 v_item_master_string := 'DROP VIEW CZ_EXV_ITEM_MASTER';
2074 EXECUTE IMMEDIATE v_item_master_string;
2075
2076 v_organization_string := 'DROP VIEW CZ_EXV_ORGANIZATIONS';
2077 EXECUTE IMMEDIATE v_organization_string;
2078
2079 v_mtl_system_items_string := 'DROP VIEW CZ_EXV_MTL_SYSTEM_ITEMS';
2080 EXECUTE IMMEDIATE v_mtl_system_items_string;
2081
2082 v_items_string := 'DROP VIEW CZ_EXV_ITEMS';
2083 EXECUTE IMMEDIATE v_items_string;
2084
2085 v_addresses_string := 'DROP VIEW CZ_EXV_ADDRESSES';
2086 EXECUTE IMMEDIATE v_addresses_string;
2087
2088 v_address_uses_string :='DROP VIEW CZ_EXV_ADDRESS_USES';
2089 EXECUTE IMMEDIATE v_address_uses_string ;
2090
2091 v_customers_string := 'DROP VIEW CZ_EXV_CUSTOMERS';
2092 EXECUTE IMMEDIATE v_customers_string;
2093
2094 v_contacts_string := 'DROP VIEW CZ_EXV_CONTACTS';
2095 EXECUTE IMMEDIATE v_contacts_string;
2096
2097 v_price_list_string := 'DROP VIEW CZ_EXV_PRICE_LISTS';
2098 EXECUTE IMMEDIATE v_price_list_string;
2099
2100 v_price_list_line_string := 'DROP VIEW CZ_EXV_PRICE_LIST_LINES';
2101 EXECUTE IMMEDIATE v_price_list_line_string;
2102
2103 v_end_user_string := 'DROP VIEW CZ_EXV_END_USER';
2104 EXECUTE IMMEDIATE v_end_user_string;
2105
2106 v_item_property_values_string := 'DROP VIEW CZ_EXV_ITEM_PROPERTY_VALUES';
2107 EXECUTE IMMEDIATE v_item_property_values_string;
2108
2109 v_item_properties_string := 'DROP VIEW CZ_EXV_ITEM_PROPERTIES';
2110 EXECUTE IMMEDIATE v_item_properties_string;
2111
2112 v_item_types_string := 'DROP VIEW CZ_EXV_ITEM_TYPES';
2113 EXECUTE IMMEDIATE v_item_types_string;
2114
2115 v_bom_string := 'DROP VIEW CZ_EXV_BOM_EXPLOSIONS';
2116 EXECUTE IMMEDIATE v_bom_string;
2117
2118 return v_success;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 log_report(v_errorString || cz_utils.get_text('CZ_DROP_EXT_VIEWS_ERR','SQLERRM',Sqlerrm));
2123 return v_error;
2124 END drop_exv_views;
2125
2126
2127 --------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2128 ---------- to insert records into cz_servers
2129
2130 PROCEDURE populate_cz_server_cp( Errbuf IN OUT NOCOPY VARCHAR2,
2131 Retcode IN OUT NOCOPY PLS_INTEGER,
2132 LOCAL_NAME IN VARCHAR2
2133 ,HOSTNAME IN VARCHAR2
2134 ,DB_LISTENER_PORT IN NUMBER
2135 ,INSTANCE_NAME IN VARCHAR2
2136 ,FNDNAM IN VARCHAR2
2137 ,GLOBAL_IDENTITY IN VARCHAR2
2138 ,NOTES IN VARCHAR2
2139 ,FNDNAM_LINK_NAME IN VARCHAR2
2140 ,IMPORT_ENABLED IN VARCHAR2
2141 )
2142 AS
2143
2144 v_hostname cz_servers.hostname%TYPE;
2145 v_db_listener_port cz_servers.db_listener_port%TYPE;
2146 v_instance_name cz_servers.instance_name%TYPE;
2147 v_local_name cz_servers.local_name%TYPE;
2148 v_fndnam cz_servers.fndnam%TYPE;
2149 v_global_identity cz_servers.global_identity%TYPE;
2150 v_notes cz_servers.notes%TYPE;
2151 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2152 v_import_enabled cz_servers.import_enabled%TYPE;
2153
2154 v_error_status VARCHAR2(4000) := '0';
2155 v_success char(1) := '0';
2156 v_warning char(1) := '1';
2157 v_error char(1) := '2';
2158 x_error BOOLEAN:=FALSE;
2159 xerror char(1);
2160 v_errorString VARCHAR2(1024) :='POPULATE_SERVER_CP : ';
2161
2162 v_server_count NUMBER := 0;
2163 v_server_id NUMBER;
2164 v_import_count NUMBER := 0;
2165 v_fnd_link_count NUMBER := 0;
2166 v_cursor NUMBER;
2167 v_NumRows NUMBER;
2168 v_dummy NUMBER;
2169 v_name VARCHAR2(10);
2170
2171 v_createstring varchar2(4000);
2172 v_bom varchar2(4000);
2173 v_item_master varchar2(4000);
2174
2175 BEGIN
2176
2177 v_hostname := LTRIM(RTRIM(HOSTNAME)) ;
2178 v_instance_name := LTRIM(RTRIM(INSTANCE_NAME));
2179 v_db_listener_port := DB_LISTENER_PORT ;
2180 v_local_name := LTRIM(RTRIM(LOCAL_NAME));
2181 v_fndnam := LTRIM(RTRIM(FNDNAM));
2182 v_global_identity := LTRIM(RTRIM(GLOBAL_IDENTITY));
2183 v_notes := LTRIM(RTRIM(NOTES));
2184 v_fndnam_link_name := LTRIM(RTRIM(FNDNAM_LINK_NAME));
2185 v_import_enabled := LTRIM(RTRIM(IMPORT_ENABLED));
2186
2187 Errbuf := '';
2188 Retcode := 0;
2189
2190 BEGIN
2191 SELECT count(*)
2192 INTO v_server_count
2193 FROM cz_servers
2194 WHERE cz_servers.local_name = v_local_name
2195 OR ( cz_servers.hostname = v_hostname
2196 AND cz_servers.instance_name = v_instance_name
2197 AND cz_servers.db_listener_port = v_db_listener_port) ;
2198 EXCEPTION
2199 WHEN OTHERS THEN
2200 v_server_count := 0;
2201 END;
2202
2203 IF (v_server_count = 0) THEN
2204 BEGIN
2205
2206 SELECT decode(import_enabled,'Y','1','N','0','0')
2207 INTO v_import_enabled
2208 FROM dual;
2209
2210 IF (v_import_enabled = '1') THEN
2211 SELECT count(*) INTO v_import_count FROM cz_servers
2212 WHERE import_enabled = '1';
2213 IF (V_IMPORT_count > 0) THEN
2214 Errbuf := cz_utils.get_text('CZ_IMP_SERVER_EXISTS_DEF');
2215 log_report(Errbuf);
2216 Retcode := '2';
2217 return;
2218 END IF;
2219 END IF;
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 v_import_count := 0;
2223 END;
2224
2225 IF v_fndnam_link_name IS NOT NULL THEN
2226 BEGIN
2227
2228 v_dummy := doesLinkExist(v_fndnam_link_name);
2229
2230 if (v_dummy > 0) THEN
2231 errbuf := cz_utils.get_text('CZ_DB_LINK_EXISTS','LINKNAME',v_fndnam_link_name);
2232 log_report(v_errorString || errbuf);
2233 Retcode := '2';
2234 return;
2235 END IF;
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 errbuf := cz_utils.get_text('CZ_DB_LINK_ERROR', 'SQLERRM', Sqlerrm);
2239 log_report(v_errorString || errbuf);
2240 Retcode := '2';
2241 return;
2242 END;
2243 END IF;
2244
2245 BEGIN
2246 SELECT cz_servers_s.NEXTVAL
2247 INTO v_server_id
2248 FROM dual;
2249 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting ' || v_server_id);
2250 INSERT INTO CZ_SERVERS (SERVER_LOCAL_ID,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
2251 INSTANCE_NAME, FNDNAM, GLOBAL_IDENTITY, NOTES,
2252 FNDNAM_LINK_NAME, IMPORT_ENABLED)
2253 VALUES (v_server_id,LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT,
2254 INSTANCE_NAME, FNDNAM, V_GLOBAL_IDENTITY, NOTES,
2255 FNDNAM_LINK_NAME, v_IMPORT_ENABLED );
2256 COMMIT;
2257 EXCEPTION
2258 WHEN OTHERS THEN
2259 errbuf := cz_utils.get_text('CZ_CANNOT_INSERT_SERVER','SQLERRM',Sqlerrm);
2260 v_error_status := SQLERRM;
2261 log_report(v_errorString || errbuf);
2262 Retcode := '2';
2263 return;
2264 END;
2265 ELSE
2266 -- this message should have name of existing server with same configuration
2267 -- should change code to get the name of this server and add it to the message
2268 errbuf := cz_utils.get_text('CZ_SERVER_EXISTS_DEF');
2269 log_report(v_errorString || errbuf);
2270 Retcode := '0';
2271 return;
2272 END IF;
2273
2274 END populate_cz_server_cp;
2275
2276 ---------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2277 ----------proc for altering records into cz_servers
2278
2279 PROCEDURE alter_cz_server_cp( Errbuf IN OUT NOCOPY VARCHAR2,
2280 Retcode IN OUT NOCOPY PLS_INTEGER,
2281 LOCAL_NAME IN VARCHAR2,
2282 HOSTNAME IN VARCHAR2,
2283 DB_LISTENER_PORT IN NUMBER,
2284 INSTANCE_NAME IN VARCHAR2,
2285 FNDNAM IN VARCHAR2,
2286 GLOBAL_IDENTITY IN VARCHAR2,
2287 NOTES IN VARCHAR2,
2288 FNDNAM_LINK_NAME IN VARCHAR2,
2289 IMPORT_ENABLED IN VARCHAR2
2290 )
2291 AS
2292 v_error_status VARCHAR2(4000) := '0';
2293 v_hostname cz_servers.hostname%TYPE;
2294 v_db_listener_port cz_servers.db_listener_port%TYPE;
2295 v_instance_name cz_servers.instance_name%TYPE;
2296 v_local_name cz_servers.local_name%TYPE;
2297 v_fndnam cz_servers.fndnam%TYPE;
2298 v_global_identity cz_servers.global_identity%TYPE;
2299 v_notes cz_servers.notes%TYPE;
2300 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2301 v_import_enabled cz_servers.import_enabled%TYPE;
2302
2303 v_server_count NUMBER := 0;
2304 v_server_id NUMBER ;
2305 v_import_count NUMBER := 0;
2306 v_dummy NUMBER := 0;
2307 v_name VARCHAR2(10);
2308 x_error BOOLEAN:=FALSE;
2309 xerror char(1);
2310 v_errorString VARCHAR2(1024) :='ALTER_SERVER_CP: ';
2311 v_createstring varchar2(4000);
2312
2313 v_views_status VARCHAR2(1);
2314 v_db_link CZ_SERVERS.fndnam_link_name%TYPE;
2315
2316 l_hostname cz_servers.hostname%TYPE;
2317 l_db_listener_port cz_servers.db_listener_port%TYPE;
2318 l_instance_name cz_servers.instance_name%TYPE;
2319 l_fndnam cz_servers.fndnam%TYPE;
2320 l_global_identity cz_servers.global_identity%TYPE;
2321 l_notes cz_servers.notes%TYPE;
2322 l_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2323 l_import_enabled cz_servers.import_enabled%TYPE;
2324 matching_local_name cz_servers.local_name%TYPE;
2325
2326 BEGIN
2327
2328 v_hostname := LTRIM(RTRIM(HOSTNAME)) ;
2329 v_instance_name := LTRIM(RTRIM(INSTANCE_NAME));
2330 v_db_listener_port := DB_LISTENER_PORT ;
2331 v_local_name := LTRIM(RTRIM(LOCAL_NAME));
2332 v_fndnam := LTRIM(RTRIM(FNDNAM));
2333 v_global_identity := LTRIM(RTRIM(GLOBAL_IDENTITY));
2334 v_notes := LTRIM(RTRIM(NOTES));
2335 v_fndnam_link_name := upper(LTRIM(RTRIM(FNDNAM_LINK_NAME)));
2336 v_import_enabled := LTRIM(RTRIM(IMPORT_ENABLED));
2337 Errbuf := NULL;
2338 Retcode := 0;
2339
2340 BEGIN
2341 SELECT SERVER_LOCAL_ID,hostname,db_listener_port,instance_name,
2342 fndnam,global_identity,notes,upper(fndnam_link_name),import_enabled
2343 INTO v_server_id,l_hostname,l_db_listener_port,l_instance_name,
2344 l_fndnam,l_global_identity,l_notes,l_fndnam_link_name,l_import_enabled
2345 FROM cz_servers
2346 WHERE cz_servers.local_name = v_local_name;
2347
2348 BEGIN
2349 BEGIN
2350 SELECT decode(import_enabled,'Y','1','N','0','0')
2351 INTO v_import_enabled
2352 FROM dual;
2353
2354 SELECT local_name into matching_local_name
2355 FROM cz_servers
2356 WHERE hostname = v_hostname
2357 AND instance_name = v_instance_name
2358 AND db_listener_port = v_db_listener_port
2359 AND fndnam = v_fndnam
2360 AND local_name <> v_local_name;
2361 IF (sql%FOUND) THEN
2362 errbuf := cz_utils.get_text('CZ_SERVER_EXISTS_MOD','SVRNAME',matching_local_name);
2363 log_report(v_errorString || errbuf);
2364 Retcode := '2';
2365 return;
2366 END IF;
2367 EXCEPTION
2368 WHEN OTHERS THEN
2369 null;
2370 END;
2371
2372 IF ( (upper(v_local_name) = 'LOCAL')
2373 and ((v_hostname <> l_hostname) or (v_db_listener_port <> l_db_listener_port)
2374 or (v_instance_name <> l_instance_name) or (v_fndnam <> l_fndnam)
2375 or (v_global_identity <> l_global_identity) or (v_notes <> l_notes)
2376 or (v_fndnam_link_name <> l_fndnam_link_name)) ) THEN
2377 -- cannot alter anything other than import_enabled for Local entry
2378 errbuf := cz_utils.get_text('CZ_IMP_ALTER_LOCAL_SERVER_ERR');
2379 log_report(v_errorString || errbuf);
2380 Retcode := '2';
2381 return;
2382 END IF;
2383
2384 IF (v_import_enabled = '1') THEN
2385 BEGIN
2386 SELECT count(*) INTO v_import_count FROM cz_servers
2387 WHERE import_enabled = '1'
2388 AND local_name <> v_local_name;
2389 IF (V_IMPORT_count > 0) THEN
2390 -- another server has import enabled, only one is allowed
2391 errbuf := cz_utils.get_text('CZ_IMP_SERVER_EXISTS_MOD');
2392 log_report(v_errorString || errbuf);
2393 Retcode := '2';
2394 return;
2395 END IF;
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 v_import_count := 0;
2399 END;
2400 END IF;
2401
2402 BEGIN
2403 IF ( ((v_hostname <> l_hostname) or (v_db_listener_port <> l_db_listener_port)
2404 or (v_instance_name <> l_instance_name)
2405 or (v_fndnam <> l_fndnam) or (v_fndnam_link_name <> l_fndnam_link_name)
2406 or ((v_import_enabled = '0') and (l_import_enabled = '1')) )
2407 and ((l_fndnam_link_name is NOT NULL)
2408 or (upper(v_local_name) = 'LOCAL')) ) THEN
2409
2410 -- IF (l_fndnam_link_name is NOT NULL) THEN
2411 IF (v_fndnam_link_name <> l_fndnam_link_name) THEN
2412 BEGIN
2413
2414 v_dummy := doesLinkExist(l_fndnam_link_name);
2415
2416 if (v_dummy > 0) then
2417 v_CreateString := 'drop database link ' || l_fndnam_link_name;
2418 EXECUTE IMMEDIATE v_CreateString;
2419 log_report(v_errorstring ||
2420 cz_utils.get_text('CZ_DB_LINK_DROPPED','LINKNAME',
2421 l_fndnam_link_name));
2422 END IF;
2423 EXCEPTION
2424 WHEN OTHERS THEN
2425 log_report(v_errorString ||
2426 cz_utils.get_text('CZ_DB_LINK_ERROR','SQLERRM',Sqlerrm));
2427 Retcode := '1';
2428 END;
2429 END IF;
2430
2431 -- if import_enabled changes from 1 to 0, drop views
2432 IF ((v_import_enabled = '0') and (l_import_enabled = '1')
2433 and ((l_fndnam_link_name is NOT NULL)
2434 or (upper(v_local_name) = 'LOCAL')) ) THEN
2435 BEGIN
2436 --
2437 -- don't drop views --
2438 -- ( this is an old code )--
2439 --
2440 --v_dummy := drop_exv_views;--
2441
2442 --
2443 -- just recreate them instead --
2444 --
2445 xerror:= create_exv_views('ERROR');
2446 IF (xerror<> '0') THEN
2447 -- error has already been logged
2448 Retcode := '1';
2449 END IF;
2450
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 Retcode := '2';
2454 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2455 log_report(v_errorString|| errbuf);
2456 return;
2457 END;
2458 END IF;
2459 END IF;
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 Retcode := '2';
2463 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2464 log_report(v_errorString|| errbuf);
2465 return;
2466 END;
2467
2468 BEGIN
2469 UPDATE CZ_SERVERS SET
2470 LOCAL_NAME = v_local_name,
2471 HOSTNAME = v_hostname,
2472 DB_LISTENER_PORT = v_db_listener_port,
2473 INSTANCE_NAME = v_instance_name,
2474 FNDNAM = v_fndnam ,
2475 GLOBAL_IDENTITY = v_global_identity ,
2476 NOTES = v_notes ,
2477 FNDNAM_LINK_NAME = v_fndnam_link_name ,
2478 IMPORT_ENABLED = v_import_enabled
2479 WHERE cz_servers.server_local_id = v_server_id ;
2480 EXCEPTION
2481 WHEN OTHERS THEN
2482 Retcode := '2';
2483 errbuf := cz_utils.get_text('CZ_MOD_SERVER_ERR','SQLERRM',Sqlerrm);
2484 log_report(v_errorString|| errbuf);
2485 return;
2486 END;
2487
2488 -- if import_enabled changes from 0 to 1, for LOCAL entry, create views
2489 IF ((v_import_enabled = '1') and (l_import_enabled = '0')
2490 /*and (upper(v_local_name) = 'LOCAL')*/ ) THEN
2491 BEGIN
2492 xerror := create_exv_views(v_local_name);
2493 IF (xerror <> '0') THEN
2494 -- detailed error has already been logged
2495 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2496 Retcode := '2';
2497 return;
2498 END IF;
2499 EXCEPTION
2500 WHEN OTHERS THEN
2501 Retcode := '2';
2502 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2503 log_report(v_errorString|| errbuf);
2504 return;
2505 END;
2506 END IF;
2507
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 v_server_count := 0;
2511 END;
2512 COMMIT;
2513
2514 EXCEPTION
2515 WHEN NO_DATA_FOUND THEN
2516 errbuf := cz_utils.get_text('CZ_SERVER_NOT_EXIST','SVRNAME',v_local_name);
2517 log_report(v_errorString || errbuf);
2518 RetCode := '1';
2519 WHEN OTHERS THEN
2520 Retcode := '2';
2521 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2522 log_report(v_errorString|| errbuf);
2523 END;
2524 END alter_cz_server_cp;
2525
2526
2527 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2528 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2529 ------------------------------------------------------------------------------
2530 PROCEDURE show_cz_servers_cp(Errbuf OUT NOCOPY VARCHAR2,
2531 Retcode OUT NOCOPY NUMBER)
2532 AS
2533
2534 CURSOR C_GETSERVERS IS
2535 SELECT LOCAL_NAME, HOSTNAME, DB_LISTENER_PORT, INSTANCE_NAME,
2536 SERVER_DB_VERSION, FNDNAM, GLOBAL_IDENTITY, NOTES,FNDNAM_LINK_NAME, Decode(IMPORT_ENABLED, '1', 'Y', '0','N',IMPORT_ENABLED)
2537 FROM CZ_SERVERS
2538 WHERE SERVER_LOCAL_ID >= 0;
2539
2540 lLocalName CZ_SERVERS.LOCAL_NAME%TYPE;
2541 lHostName CZ_SERVERS.HOSTNAME%TYPE;
2542 lDbListenerPort CZ_SERVERS.DB_LISTENER_PORT%TYPE;
2543 lInstanceName CZ_SERVERS.INSTANCE_NAME%TYPE;
2544 lServerDbVersion CZ_SERVERS.SERVER_DB_VERSION%TYPE;
2545 lFndNam CZ_SERVERS.FNDNAM%TYPE;
2546 lGlobalIdentity CZ_SERVERS.GLOBAL_IDENTITY%TYPE;
2547 lNotes CZ_SERVERS.NOTES%TYPE;
2548 lFndNamLinkName CZ_SERVERS.FNDNAM_LINK_NAME%TYPE;
2549 lImportEnabled CZ_SERVERS.IMPORT_ENABLED%TYPE;
2550
2551 v_errorString VARCHAR2(1024) :='SHOW_SERVERS_CP: ';
2552
2553 BEGIN
2554 Errbuf:=NULL;
2555 Retcode:=0;
2556
2557 OPEN C_GETSERVERS;
2558 LOOP
2559 FETCH C_GETSERVERS INTO lLocalName,lHostName,lDbListenerPort,
2560 lInstanceName,lServerDbVersion,lFndNam,lGlobalIdentity,
2561 lNotes,lFndNamLinkName,lImportEnabled;
2562 EXIT WHEN C_GETSERVERS%NOTFOUND;
2563
2564 FND_FILE.PUT_LINE(FND_FILE.LOG,'Server Name='||lLocalName);
2565 FND_FILE.PUT_LINE(FND_FILE.LOG,'Host Name='||lHostName);
2566 FND_FILE.PUT_LINE(FND_FILE.LOG,'Port='||lDbListenerPort);
2567 FND_FILE.PUT_LINE(FND_FILE.LOG,'Instance Name='||lInstanceName);
2568 FND_FILE.PUT_LINE(FND_FILE.LOG,'Server Db Version='||lServerDbVersion);
2569 FND_FILE.PUT_LINE(FND_FILE.LOG,'FND Name='||lFndNam);
2570 FND_FILE.PUT_LINE(FND_FILE.LOG,'Global Name='||lGlobalIdentity);
2571 FND_FILE.PUT_LINE(FND_FILE.LOG,'Notes='||lNotes);
2572 FND_FILE.PUT_LINE(FND_FILE.LOG,'FND Link Name='||lFndNamLinkName);
2573 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Import Enabled='||Decode(lImportEnabled, '1', 'Y', '0','N',lImportEnabled));
2574 FND_FILE.PUT_LINE(FND_FILE.LOG,'Import Enabled=' || lImportEnabled);
2575 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------');
2576
2577 END LOOP;
2578 CLOSE C_GETSERVERS;
2579 EXCEPTION
2580 WHEN OTHERS THEN
2581 Errbuf:= cz_utils.get_text('CZ_SHOW_SERVERS_ERR','SQLERRM',Sqlerrm);
2582 log_report(v_errorString || errbuf);
2583 retcode := 2;
2584 END show_cz_servers_cp;
2585
2586 ------------------------------------------------------------------------------
2587 FUNCTION create_remote_hgrid_view(p_server_id IN NUMBER, p_fndnam_link_name IN VARCHAR2)
2588 RETURN VARCHAR2 IS
2589 v_success varchar2(1) := '0';
2590 v_error varchar2(1) := '2';
2591 v_link_name VARCHAR2(2000) := p_fndnam_link_name;
2592 BEGIN
2593
2594 IF(SUBSTR(v_link_name, 1, 1) = '@')THEN v_link_name := SUBSTR(v_link_name, 2); END IF;
2595
2596 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW cz_repos_folders_on_' || TO_CHAR(p_server_id) || '_v AS ' ||
2597 'SELECT * FROM cz_repository_main_hgrid_v@' || v_link_name ||
2598 ' WHERE object_type = ''FLD''';
2599 RETURN v_success;
2600 EXCEPTION
2601 WHEN OTHERS THEN
2602 log_report(cz_utils.get_text('CZ_HGRID_VIEW_ERROR_DTL', 'ERRORTEXT', SQLERRM));
2603 RETURN v_error;
2604 END;
2605 ---------- Create db link ----------------------------------------------------
2606 PROCEDURE create_link_cp(Errbuf IN OUT NOCOPY VARCHAR2,
2607 Retcode IN OUT NOCOPY PLS_INTEGER,
2608 LOCAL_NAME IN VARCHAR2,
2609 PASSWORD IN VARCHAR2)
2610 AS
2611
2612 v_hostname cz_servers.hostname%TYPE;
2613 v_instance_name cz_servers.instance_name%TYPE;
2614 v_db_listener_port cz_servers.db_listener_port%TYPE;
2615 v_fndnam cz_servers.fndnam%TYPE;
2616 v_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
2617 v_local_name cz_servers.local_name%TYPE;
2618 v_server_db_version cz_servers.server_db_version%TYPE;
2619 v_import_enabled cz_servers.import_enabled%TYPE;
2620 v_server_id cz_servers.server_local_id%TYPE;
2621 v_cursor NUMBER;
2622 v_NumRows NUMBER;
2623 v_dummy NUMBER;
2624 xerror char(1);
2625 v_cur integer;
2626 v_res integer;
2627 v_link_status VARCHAR2(1);
2628 v_CreateString VARCHAR2(8000):='' ;
2629 v_dropString VARCHAR2(8000) := '';
2630 v_newString VARCHAR2(8000):='' ;
2631 v_errorString VARCHAR2(1024) :='CREATE_LINK_CP: ';
2632
2633 BEGIN
2634
2635 v_local_name := local_name;
2636
2637 BEGIN
2638 SELECT hostname,
2639 instance_name,
2640 db_listener_port,
2641 fndnam,
2642 fndnam_link_name,
2643 import_enabled,
2644 server_local_id
2645 INTO v_hostname,
2646 v_instance_name,
2647 v_db_listener_port,
2648 v_fndnam,
2649 v_fndnam_link_name,
2650 v_import_enabled,
2651 v_server_id
2652 FROM cz_servers
2653 WHERE cz_servers.local_name = v_local_name;
2654
2655 IF v_fndnam_link_name IS NOT NULL THEN
2656 BEGIN
2657
2658 v_dummy := doesLinkExist(v_fndnam_link_name);
2659
2660 --
2661 -- DB link already exists --
2662 --
2663 IF (v_dummy > 0) THEN
2664
2665 --
2666 -- check DB Link --
2667 --
2668 v_link_status:=isLinkAlive(v_fndnam_link_name);
2669
2670 IF v_link_status = LINK_IS_DOWN THEN
2671 errbuf := CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_fndnam_link_name);
2672 Retcode := 1;
2673 RETURN;
2674 END IF;
2675
2676 xerror := create_remote_hgrid_view(v_server_id, v_fndnam_link_name);
2677
2678 IF (xerror <> '0') THEN
2679 -- detailed error has already been logged
2680 Retcode := 2;
2681 END IF;
2682
2683 IF (v_import_enabled = '1') THEN
2684
2685 xerror := create_exv_views(v_local_name);
2686
2687 IF (xerror <> '0') THEN
2688 -- detailed error has already been logged
2689 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2690 Retcode := 2;
2691 return;
2692 END IF;
2693 END IF;
2694
2695 -- errbuf := cz_utils.get_text('CZ_LINK_FOR_CREATION_EXISTS','LINKNAME',v_fndnam_link_name);
2696 -- log_report(v_errorString || errbuf);
2697 -- retCode := 2;
2698 -- retCode := 1;
2699 -- return;
2700 ELSE
2701 BEGIN
2702 v_CreateString := 'create database link '||v_fndnam_link_name||' connect to '||v_fndnam||' identified by '||password||
2703 ' using ''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = '||v_hostname||')(PORT = '||v_db_listener_port||'))(CONNECT_DATA = (SID = '||v_instance_name||')))''';
2704 EXECUTE IMMEDIATE v_CreateString;
2705 EXCEPTION
2706 WHEN OTHERS THEN
2707 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2708 log_report(v_errorString || errbuf);
2709 retCode := 2;
2710 return;
2711 END;
2712
2713 BEGIN
2714 v_newString := 'SELECT version INTO :v_server_db_version FROM V$INSTANCE@' || v_fndnam_link_name ;
2715 -- || ' WHERE database_status in (''ACTIVE'', ''OPEN'') ';
2716 v_cur := dbms_sql.open_cursor;
2717 dbms_sql.parse(v_cur,v_newString,dbms_sql.native);
2718 dbms_sql.define_column(v_cur,1,v_server_db_version,40);
2719 v_res:=dbms_sql.execute(v_cur);
2720
2721 if (dbms_sql.fetch_rows(v_cur) > 0) then
2722 dbms_sql.column_value(v_cur,1,v_server_db_version);
2723 end if;
2724 dbms_sql.close_cursor(v_cur);
2725 EXCEPTION
2726 WHEN OTHERS THEN
2727 errbuf := cz_utils.get_text('CZ_DB_VERSION_NOT_FOUND', 'SQLERRM', Sqlerrm);
2728 Retcode := 1;
2729 log_report(v_errorString || errbuf);
2730 END;
2731
2732
2733 BEGIN
2734 UPDATE CZ_SERVERS SET
2735 SERVER_DB_VERSION = v_server_db_version
2736 WHERE cz_servers.local_name = v_local_name ;
2737 COMMIT;
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 errbuf := cz_utils.get_text('CZ_DB_VERSION_UPDATE_FAILURE', 'SQLERRM', Sqlerrm);
2741 Retcode := 2;
2742 log_report(v_errorString || errbuf);
2743 return;
2744 END;
2745 END IF;
2746
2747 v_link_status:=isLinkAlive(v_fndnam_link_name);
2748
2749 IF v_link_status = LINK_IS_DOWN THEN
2750 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_fndnam_link_name);
2751 Retcode := 2;
2752 RETURN;
2753 END IF;
2754
2755 xerror := create_remote_hgrid_view(v_server_id, v_fndnam_link_name);
2756
2757 IF (xerror <> '0') THEN
2758 -- detailed error has already been logged
2759 Retcode := 2;
2760 END IF;
2761
2762 IF(v_import_enabled = '1') THEN
2763
2764 IF (xerror <> '0') THEN
2765 -- detailed error has already been logged
2766 errbuf := cz_utils.get_text('CZ_EXT_VIEW_CREATION_ERR');
2767 Retcode := 2;
2768 RETURN;
2769 END IF;
2770 END IF;
2771
2772 EXCEPTION
2773 WHEN OTHERS THEN
2774 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2775 log_report(v_errorString || errbuf);
2776 Retcode := 2;
2777 return;
2778 END;
2779 END IF;
2780 EXCEPTION
2781 WHEN OTHERS THEN
2782 errbuf := cz_utils.get_text('CZ_LINK_CREATION_ERR','SQLERRM',Sqlerrm);
2783 log_report(v_errorString || errbuf);
2784 Retcode := 2;
2785 return;
2786 END;
2787 END create_link_cp;
2788 ------------------------------------------------------------------------------
2789
2790 --Register Maintain server concurrent process
2791
2792
2793 PROCEDURE register_maint_server_process
2794 (application_name IN VARCHAR2 , -- default 'Oracle Configurator',
2795 Request_Group IN VARCHAR2 default NULL,
2796 cz_schema IN VARCHAR2 default NULL)
2797 AS
2798
2799 var_schema VARCHAR2(40);
2800 ar_application_name VARCHAR2(50):='Oracle Configurator';
2801 ar_request_group VARCHAR2(50):=NULL;
2802 creation_failure EXCEPTION;
2803 exec_exists EXCEPTION;
2804 no_req_group EXCEPTION;
2805
2806 BEGIN
2807
2808
2809 BEGIN
2810 register_spx_process('Enable Remote Server',
2811 'CZCREATELNK',
2812 application_name,
2813 'Enable Remote Server for Configurator',
2814 'CZ_ORAAPPS_INTEGRATE.CREATE_LINK_CP',
2815 request_group,
2816 cz_schema);
2817 fnd_program.parameter(program_short_name=>'CZCREATELNK',
2818 application=>application_name,
2819 sequence=>1,
2820 parameter=>'LOCAL_NAME',
2821 description=>'Local Name',
2822 value_set=>'40 Chars',
2823 display_size=>20,
2824 description_size=>20,
2825 concatenated_description_size=>50,
2826 prompt=>'Server Local name');
2827 fnd_program.parameter(program_short_name=>'CZCREATELNK',
2828 application=>application_name,
2829 sequence=>1,
2830 parameter=>'PASSWORD',
2831 description=>'Password',
2832 value_set=>'40 Chars',
2833 display_size=>20,
2834 description_size=>20,
2835 concatenated_description_size=>50,
2836 prompt=>'Password');
2837
2838 EXCEPTION
2839 WHEN OTHERS THEN
2840 LOG_REPORT('Error : < Enable Remote Server for Configurator > REGISTRATION');
2841 END;
2842
2843
2844 BEGIN
2845 register_spx_process('Define Remote Server',
2846 'CZPOPULATESRV',
2847 application_name,
2848 'Define Remote Server',
2849 'CZ_ORAAPPS_INTEGRATE.POPULATE_CZ_SERVER_CP',
2850 request_group,
2851 cz_schema);
2852 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2853 application=>application_name,
2854 sequence=>1,
2855 parameter=>'LOCAL_NAME',
2856 description=>'Local Name',
2857 value_set=>'40 Chars',
2858 display_size=>20,
2859 description_size=>20,
2860 concatenated_description_size=>50,
2861 prompt=>'Local name');
2862 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2863 application=>application_name,
2864 sequence=>2,
2865 parameter=>'HOSTNAME',
2866 description=>'Host Name',
2867 value_set=>'90 Characters',
2868 display_size=>20,
2869 description_size=>20,
2870 concatenated_description_size=>50,
2871 prompt=>'Host name');
2872 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2873 application=>application_name,
2874 sequence=>3,
2875 parameter=>'DB_LISTENER_PORT',
2876 description=>'DB Listener Port',
2877 value_set=>'9 Number',
2878 display_size=>10,
2879 description_size=>10,
2880 concatenated_description_size=>10,
2881 prompt=>'DB Listener Port');
2882 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2883 application=>application_name,
2884 sequence=>4,
2885 parameter=>'INSTANCE_NAME',
2886 description=>'Instance Name',
2887 value_set=>'40 Chars',
2888 display_size=>20,
2889 description_size=>20,
2890 concatenated_description_size=>50,
2891 prompt=>'Instance name');
2892 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2893 application=>application_name,
2894 sequence=>5,
2895 parameter=>'FNDNAM',
2896 description=>'FND Name',
2897 value_set=>'40 Chars',
2898 display_size=>20,
2899 description_size=>20,
2900 concatenated_description_size=>50,
2901 prompt=>'Oracle Applications Schema Name (FNDNAM)');
2902 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2903 application=>application_name,
2904 sequence=>6,
2905 parameter=>'GLOBAL_IDENTITY',
2906 description=>'Global Identity',
2907 value_set=>'90 Characters',
2908 display_size=>20,
2909 description_size=>20,
2910 concatenated_description_size=>50,
2911 prompt=>'Global Identity');
2912 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2913 application=>application_name,
2914 sequence=>7,
2915 parameter=>'NOTES',
2916 description=>'Notes',
2917 value_set=>'90 Characters',
2918 display_size=>50,
2919 description_size=>50,
2920 concatenated_description_size=>50,
2921 prompt=>'Description');
2922 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2923 application=>application_name,
2924 sequence=>8,
2925 parameter=>'FND_LINK_NAME',
2926 description=>'FND Link Name',
2927 value_set=>'40 Chars',
2928 display_size=>20,
2929 description_size=>20,
2930 concatenated_description_size=>50,
2931 prompt=>'FND Link Name');
2932 fnd_program.parameter(program_short_name=>'CZPOPULATESRV',
2933 application=>application_name,
2934 sequence=>9,
2935 parameter=>'IMPORT_ENABLED',
2936 description=>'Import Enabled',
2937 value_set=>'CZ_ENABLE_FLAG',
2938 display_size=>20,
2939 description_size=>20,
2940 concatenated_description_size=>50,
2941 prompt=>'Import Enabled');
2942
2943 EXCEPTION
2944 WHEN OTHERS THEN
2945 LOG_REPORT('Error : < Define Remote Server > REGISTRATION');
2946 END;
2947
2948 BEGIN
2949 register_spx_process('Modify Server Definition',
2950 'CZALTERSERVER',
2951 application_name,
2952 'Modify Remote Server',
2953 'CZ_ORAAPPS_INTEGRATE.ALTER_CZ_SERVER_CP',
2954 request_group,
2955 cz_schema);
2956
2957 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2958 application=>application_name,
2959 sequence=>1,
2960 parameter=>'LOCAL_NAME',
2961 description=>'Local Name',
2962 value_set=>'40 Chars',
2963 display_size=>20,
2964 description_size=>20,
2965 concatenated_description_size=>50,
2966 prompt=>'Local name');
2967 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2968 application=>application_name,
2969 sequence=>2,
2970 parameter=>'HOSTNAME',
2971 description=>'Host Name',
2972 value_set=>'90 Characters',
2973 display_size=>20,
2974 description_size=>20,
2975 concatenated_description_size=>50,
2976 prompt=>'Host name');
2977 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2978 application=>application_name,
2979 sequence=>3,
2980 parameter=>'DB_LISTENER_PORT',
2981 description=>'DB Listener Port',
2982 value_set=>'9 Number',
2983 display_size=>10,
2984 description_size=>10,
2985 concatenated_description_size=>10,
2986 prompt=>'DB Listener Port');
2987 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2988 application=>application_name,
2989 sequence=>4,
2990 parameter=>'INSTANCE_NAME',
2991 description=>'Instance Name',
2992 value_set=>'40 Chars',
2993 display_size=>20,
2994 description_size=>20,
2995 concatenated_description_size=>50,
2996 prompt=>'Instance name');
2997 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
2998 application=>application_name,
2999 sequence=>5,
3000 parameter=>'FNDNAM',
3001 description=>'FND Name',
3002 value_set=>'40 Chars',
3003 display_size=>20,
3004 description_size=>20,
3005 concatenated_description_size=>50,
3006 prompt=>'Oracle Applications Schema Name (FNDNAM)');
3007 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3008 application=>application_name,
3009 sequence=>6,
3010 parameter=>'GLOBAL_IDENTITY',
3011 description=>'Global Identity',
3012 value_set=>'90 Characters',
3013 display_size=>20,
3014 description_size=>20,
3015 concatenated_description_size=>50,
3016 prompt=>'Global Identity');
3017 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3018 application=>application_name,
3019 sequence=>7,
3020 parameter=>'NOTES',
3021 description=>'Notes',
3022 value_set=>'90 Characters',
3023 display_size=>50,
3024 description_size=>50,
3025 concatenated_description_size=>50,
3026 prompt=>'Description');
3027 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3028 application=>application_name,
3029 sequence=>8,
3030 parameter=>'FND_LINK_NAME',
3031 description=>'FND Link Name',
3032 value_set=>'40 Chars',
3033 display_size=>20,
3034 description_size=>20,
3035 concatenated_description_size=>50,
3036 prompt=>'FND Link Name');
3037 fnd_program.parameter(program_short_name=>'CZALTERSERVER',
3038 application=>application_name,
3039 sequence=>9,
3040 parameter=>'IMPORT_ENABLED',
3041 description=>'Import Enabled',
3042 value_set=>'CZ_ENABLE_FLAG',
3043 display_size=>20,
3044 description_size=>20,
3045 concatenated_description_size=>50,
3046 prompt=>'Import Enabled');
3047 EXCEPTION
3048 WHEN OTHERS THEN
3049 LOG_REPORT('Error : < Modify Remote Server > REGISTRATION');
3050 END;
3051
3052 BEGIN
3053 register_spx_process('View Servers',
3054 'CZSHOWSERVERS',
3055 application_name,
3056 'Show Remote Servers',
3057 'CZ_ORAAPPS_INTEGRATE.SHOW_CZ_SERVERS_CP',
3058 request_group,
3059 cz_schema);
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 LOG_REPORT('Error : < Show Remote Servers > REGISTRATION');
3063 END;
3064
3065 END;
3066
3067
3068 ------------------------------------------------------------------------------------------
3069
3070 PROCEDURE RefreshSingleModel_cp
3071 (errbuf OUT NOCOPY VARCHAR2,
3072 retcode OUT NOCOPY NUMBER,
3073 iFolder_ID IN NUMBER,
3074 iModel_Id IN VARCHAR2,
3075 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0')
3076
3077 AS
3078
3079 lOrg_Id CZ_XFR_PROJECT_BILLS.ORGANIZATION_ID%TYPE;
3080 lTop_Id CZ_XFR_PROJECT_BILLS.TOP_ITEM_ID%TYPE;
3081 lCopy_Child_Models PLS_INTEGER;
3082
3083 v_link_status VARCHAR2(1);
3084 v_code VARCHAR2(1);
3085 v_db_link cz_servers.fndnam_link_name%TYPE;
3086 v_local_name cz_servers.local_name%TYPE;
3087 v_import_enabled cz_servers.import_enabled%TYPE;
3088 v_ret BOOLEAN := false;
3089
3090 BEGIN
3091 retcode:=0;
3092 errbuf:='';
3093
3094 BEGIN
3095 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)) -
3096 length(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2))) )) ,
3097 rtrim(substr(orig_sys_ref,instr(orig_sys_ref,':',1,2)+1))
3098 INTO lOrg_Id, lTop_Id
3099 FROM cz_devl_projects
3100 WHERE devl_project_id = iModel_Id
3101 AND deleted_flag = '0';
3102
3103 select decode(copy_child_models,'Y',0,'N',1,0) into lCopy_Child_Models from dual;
3104
3105 EXCEPTION
3106 WHEN NO_DATA_FOUND THEN
3107 RAISE CZ_ADMIN.IMP_MODEL_NOT_FOUND;
3108 END;
3109
3110 BEGIN
3111 SELECT local_name,fndnam_link_name,import_enabled
3112 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3113 WHERE import_enabled='1';
3114 EXCEPTION
3115 WHEN TOO_MANY_ROWS THEN
3116 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3117 WHEN NO_DATA_FOUND THEN
3118 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3119 END;
3120
3121 ---- check DB Link ----
3122 v_link_status:=isLinkAlive(v_db_link);
3123
3124 IF v_link_status=LINK_IS_DOWN THEN
3125 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3126 ELSE
3127 ---- recreate views based on remote tables ----
3128 v_code:=create_exv_views(v_local_name);
3129 compile_Dependents('CZ_IMP%');
3130
3131 EXECUTE IMMEDIATE
3132 'BEGIN cz_imp_all.goSingleBill_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE'||
3133 ',:lORG_ID,:lTOP_ID,:lCOPY_CHILD_MODELS,:iMODEL_ID); END;'
3134 USING lORG_ID,lTOP_ID,lCOPY_CHILD_MODELS,iMODEL_ID;
3135
3136 errbuf:=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3137 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3138 END IF;
3139
3140 EXCEPTION
3141 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3142 retcode:=2;
3143 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
3144 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3145 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3146 retcode:=2;
3147 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3148 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3149 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3150 retcode:=2;
3151 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3152 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3153 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3154 retcode:=2;
3155 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3156 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3157 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3158 retcode:=2;
3159 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3160 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3161 WHEN CZ_ADMIN.IMP_MODEL_NOT_FOUND THEN
3162 retcode:=2;
3163 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_NOT_FOUND', 'MODELID', iModel_Id);
3164 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3165 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3166 retcode:=2;
3167 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3168 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3169 WHEN OTHERS THEN
3170 retcode:=2;
3171 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3172 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.RefreshSingleModel_cp',11276,NULL);
3173 END RefreshSingleModel_cp;
3174
3175 ------------------------------------------------------------------------------------------
3176 PROCEDURE RemoveModel_cp
3177 (errbuf OUT NOCOPY VARCHAR2,
3178 retcode OUT NOCOPY NUMBER,
3179 iFolder_ID IN NUMBER,
3180 iModel_Id IN VARCHAR2,
3181 iImportEnabled IN VARCHAR2) IS
3182
3183 BEGIN
3184 retcode:=0;
3185 errbuf:='';
3186
3187 UPDATE cz_xfr_project_bills
3188 SET deleted_flag=Decode(iImportEnabled, 'Y', '0', 'N', '1', iImportEnabled)
3189 WHERE model_ps_node_id = iModel_Id;
3190
3191 COMMIT;
3192
3193 EXCEPTION
3194 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3195 retcode:=2;
3196 errbuf := cz_utils.get_text('CZ_IMP_ACTIVE_SESSION_EXISTS');
3197 log_report('REMOVEMODEL_CP: ' || errbuf);
3198 WHEN OTHERS THEN
3199 retcode:=2;
3200 errbuf:=cz_utils.get_text('CZ_IMP_CHANGE_PARAMS_ERR','SQLERRM',sqlerrm);
3201 log_report('REMOVEMODEL_CP: ' || errbuf);
3202 END;
3203
3204 ------------------------------------------------------------------------------------------
3205
3206 PROCEDURE go_cp
3207 (errbuf OUT NOCOPY VARCHAR2,
3208 retcode OUT NOCOPY NUMBER ) IS
3209 v_link_status VARCHAR2(1);
3210 v_code VARCHAR2(1);
3211 v_ret BOOLEAN:=FALSE;
3212 v_db_link cz_servers.fndnam_link_name%TYPE;
3213 v_local_name cz_servers.local_name%TYPE;
3214 v_import_enabled cz_servers.import_enabled%TYPE;
3215 BEGIN
3216 BEGIN
3217 SELECT local_name,fndnam_link_name,import_enabled
3218 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3219 WHERE import_enabled='1';
3220 EXCEPTION
3221 WHEN TOO_MANY_ROWS THEN
3222 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3223 WHEN NO_DATA_FOUND THEN
3224 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3225 END;
3226
3227 ---- check DB Link ----
3228 v_link_status:=isLinkAlive(v_db_link);
3229
3230 IF v_link_status=LINK_IS_DOWN THEN
3231 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3232 ELSE
3233 v_code:=create_exv_views(v_local_name);
3234 compile_Dependents('CZ_IMP%');
3235 EXECUTE IMMEDIATE
3236 'BEGIN CZ_IMP_ALL.GO_CP(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE); END;';
3237 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3238 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3239 END IF;
3240 EXCEPTION
3241 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3242 retcode:=2;
3243 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
3244 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3245 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3246 retcode:=2;
3247 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3248 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3249 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3250 retcode:=2;
3251 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3252 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3253 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3254 retcode:=2;
3255 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3256 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3257 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3258 retcode:=2;
3259 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3260 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3261 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3262 retcode:=2;
3263 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3264 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3265 WHEN OTHERS THEN
3266 retcode:=2;
3267 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3268 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.go_cp',11276,NULL);
3269 END;
3270
3271 ------------------------------------------------------------------------------------------
3272
3273 PROCEDURE PopulateModels_cp
3274 (errbuf OUT NOCOPY VARCHAR2,
3275 retcode OUT NOCOPY NUMBER,
3276 sOrg_ID IN VARCHAR2,
3277 dsOrg_ID IN VARCHAR2,
3278 sFrom IN VARCHAR2,
3279 sTo IN VARCHAR2,
3280 COPY_CHILD_MODELS IN VARCHAR2 DEFAULT '0') IS
3281 v_link_status VARCHAR2(1);
3282 v_code VARCHAR2(1);
3283 v_ret BOOLEAN:=FALSE;
3284 v_db_link cz_servers.fndnam_link_name%TYPE;
3285 v_local_name cz_servers.local_name%TYPE;
3286 v_import_enabled cz_servers.import_enabled%TYPE;
3287 BEGIN
3288 BEGIN
3289 SELECT local_name,fndnam_link_name,import_enabled
3290 INTO v_local_name,v_db_link,v_import_enabled FROM CZ_SERVERS
3291 WHERE import_enabled='1';
3292 EXCEPTION
3293 WHEN TOO_MANY_ROWS THEN
3294 RAISE CZ_ADMIN.IMP_TOO_MANY_SERVERS;
3295 WHEN NO_DATA_FOUND THEN
3296 RAISE CZ_ADMIN.IMP_NO_IMP_SERVER;
3297 END;
3298
3299 ---- check DB Link ----
3300 v_link_status:=isLinkAlive(v_db_link);
3301
3302 IF v_link_status=LINK_IS_DOWN THEN
3303 RAISE CZ_ADMIN.IMP_LINK_IS_DOWN;
3304 ELSE
3305 v_code:=create_exv_views(v_local_name);
3306 compile_Dependents('CZ_IMP%');
3307
3308 EXECUTE IMMEDIATE
3309 'BEGIN CZ_IMP_ALL.PopulateModels_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3310 ':sOrg_ID,:dsOrg_ID,:sFrom,:sTo,:COPY_CHILD_MODELS); END;'
3311 USING sOrg_ID,dsOrg_ID,sFrom,sTo,COPY_CHILD_MODELS;
3312 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3313 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3314 END IF;
3315 EXCEPTION
3316 WHEN CZ_ADMIN.IMP_ACTIVE_SESSION_EXISTS THEN
3317 retcode:=2;
3318 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_ACTIVE_SESSION_EXISTS');
3319 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3320 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
3321 retcode:=2;
3322 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED');
3323 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3324 WHEN CZ_ADMIN.IMP_TOO_MANY_SERVERS THEN
3325 retcode:=2;
3326 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3327 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3328 WHEN CZ_ADMIN.IMP_NO_IMP_SERVER THEN
3329 retcode:=2;
3330 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3331 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3332 WHEN CZ_ADMIN.IMP_LINK_IS_DOWN THEN
3333 retcode:=2;
3334 errbuf :=CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','DBLINK',v_db_link);
3335 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3336 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3337 retcode:=2;
3338 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3339 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3340 WHEN OTHERS THEN
3341 retcode:=2;
3342 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
3343 v_ret:=CZ_UTILS.log_report(errbuf,1,'CZ_ORAAPPS_INTEGRATE.PopulateModels_cp',11276,NULL);
3344 END;
3345
3346 ------------------------------------------------------------------------------------------
3347
3348 PROCEDURE check_model_similarity_cp
3349 (errbuf OUT NOCOPY VARCHAR2,
3350 retcode OUT NOCOPY NUMBER,
3351 p_TARGET_INSTANCE IN VARCHAR2,
3352 p_FOLDER_ID IN NUMBER,
3353 p_MODEL_ID IN NUMBER) IS
3354
3355 BEGIN
3356 EXECUTE IMMEDIATE
3357 'BEGIN CZ_BOM_SYNCH.report_model_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3358 ':p_TARGET_INSTANCE,:p_MODEL_ID); END;'
3359 USING p_TARGET_INSTANCE,p_MODEL_ID;
3360 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3361 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3362 EXCEPTION
3363 WHEN OTHERS THEN
3364 retcode:=2;
3365 errbuf:=SQLERRM;
3366 log_report('check_model_similarity_cp: ' || errbuf);
3367 END;
3368
3369 ------------------------------------------------------------------------------------------
3370
3371 PROCEDURE check_all_models_similarity_cp
3372 (errbuf OUT NOCOPY VARCHAR2,
3373 retcode OUT NOCOPY NUMBER,
3374 p_TARGET_INSTANCE IN VARCHAR2) IS
3375
3376 BEGIN
3377 EXECUTE IMMEDIATE
3378 'BEGIN CZ_BOM_SYNCH.report_all_models_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3379 ':p_TARGET_INSTANCE); END;'
3380 USING p_TARGET_INSTANCE;
3381 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3382 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3383 EXCEPTION
3384 WHEN OTHERS THEN
3385 retcode:=2;
3386 errbuf:=SQLERRM;
3387 log_report('check_all_models_similarity_cp: ' || errbuf);
3388 END;
3389
3390 ------------------------------------------------------------------------------------------
3391
3392 PROCEDURE sync_all_models_cp
3393 (errbuf OUT NOCOPY VARCHAR2,
3394 retcode OUT NOCOPY NUMBER) IS
3395 v_ret BOOLEAN;
3396 v_TARGET_INSTANCE CZ_SERVERS.INSTANCE_NAME%TYPE;
3397 BEGIN
3398 SELECT LOCAL_NAME
3399 INTO v_TARGET_INSTANCE
3400 FROM CZ_SERVERS
3401 WHERE import_enabled='1';
3402
3403 EXECUTE IMMEDIATE
3404 'BEGIN CZ_BOM_SYNCH.synchronize_all_models_cp(CZ_ORAAPPS_INTEGRATE.mERRBUF,CZ_ORAAPPS_INTEGRATE.mRETCODE,'||
3405 ':v_TARGET_INSTANCE); END;'
3406 USING v_TARGET_INSTANCE;
3407 errbuf :=CZ_ORAAPPS_INTEGRATE.mERRBUF;
3408 retcode:=CZ_ORAAPPS_INTEGRATE.mRETCODE;
3409 EXCEPTION
3410 WHEN TOO_MANY_ROWS THEN
3411 retcode:=2;
3412 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_TOO_MANY_SERVERS');
3413 log_report('sync_all_models_cp: ' || errbuf);
3414 WHEN NO_DATA_FOUND THEN
3415 retcode:=2;
3416 errbuf:=CZ_UTILS.GET_TEXT('CZ_IMP_NO_SERVERS');
3417 log_report('sync_all_models_cp: ' || errbuf);
3418 WHEN OTHERS THEN
3419 retcode:=2;
3420 errbuf:=SQLERRM;
3421 log_report('sync_all_models_cp: ' || errbuf);
3422 END;
3423
3424 ------------------------------------------------------------------------------------------
3425
3426 PROCEDURE Repopulate_cp
3427 (errbuf OUT NOCOPY VARCHAR2,
3428 retcode OUT NOCOPY NUMBER,
3429 p_FOLDER_ID IN NUMBER,
3430 p_MODEL_ID IN NUMBER) IS
3431 v_err INTEGER;
3432 v_ret BOOLEAN;
3433 BEGIN
3434 CZ_POPULATORS_PKG.Repopulate(p_MODEL_ID,'1','1','1',v_err);
3435
3436 --
3437 -- return last error message if error
3438 --
3439 IF v_err<>0 THEN
3440 FOR i IN(SELECT message FROM CZ_DB_LOGS
3441 WHERE run_id=v_err ORDER BY logtime)
3442 LOOP
3443 errbuf:=i.message;
3444 END LOOP;
3445 retcode:=1;
3446 END IF;
3447
3448 EXCEPTION
3449 WHEN OTHERS THEN
3450 retcode:=2;
3451 errbuf:=SQLERRM;
3452 log_report('Repopulate_cp: ' || errbuf);
3453 END;
3454
3455 END;