[Home] [Help]
PACKAGE BODY: APPS.ZX_TPI_PLUGIN_PKG
Source
1 PACKAGE BODY ZX_TPI_PLUGIN_PKG AS
2 /* $Header: zxisrvctypcgpvtb.pls 120.6 2006/08/14 23:47:50 svaze ship $ */
3
4 /* ---------------------------------------------------------------------------*/
5 /* Declaration of Local Variable declaration */
6 /* ---------------------------------------------------------------------------*/
7 l_pack_name VARCHAR2(1000);
8 l_string VARCHAR2(4000);
9 l_first BOOLEAN;
10 l_switch_mode BOOLEAN;
11 l_no_of_strings Binary_integer;
12 l_count binary_integer;
13 l_prvidx BINARY_INTEGER;
14 l_srvcidx Binary_integer;
15 l_apiidx Binary_integer;
16 l_break BOOLEAN;
17 dummy VARCHAR2(2000);
18 dummy_body VARCHAR2(2000);
19 l_exists VARCHAR2(1);
20 /* -------------------------------------------------------------------------- */
21 /* Cursors to select api owners for that service category */
22 /* -------------------------------------------------------------------------- */
23
24 Cursor c_apiowner(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
25 SELECT distinct api_owner_id
26 , status_code
27 FROM zx_api_owner_statuses
28 WHERE upper(service_category_code) = upper(p_srvc_cat)
29 AND api_owner_id = nvl(p_api_owner_id, api_owner_id)
30 ORDER BY api_owner_id asc;
31
32 /* -------------------------------------------------------------------------- */
33 /* Cursor to select distinct service types for that service category */
34 /* -------------------------------------------------------------------------- */
35
36 Cursor c_srvctyp(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
37 SELECT distinct a.api_owner_id
38 , b.service_type_id
39 , b.service_type_code
40 , b.data_transfer_code
41 FROM zx_api_registrations a
42 , zx_service_types b
43 , zx_api_owner_statuses c
44 WHERE a.service_type_id = b.service_type_id
45 AND a.api_owner_id = c.api_owner_id
46 AND upper(c.service_category_code) = upper(p_srvc_cat)
47 AND a.api_owner_id = nvl(p_api_owner_id,a.api_owner_id)
48 ORDER BY a.api_owner_id asc
49 , b.data_transfer_code desc
50 , b.service_type_id asc;
51
52 /* -------------------------------------------------------------------------- */
53 /* Cursor to select distinct context ids for that service category */
54 /* -------------------------------------------------------------------------- */
55
56 Cursor c_api(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
57 SELECT distinct a.api_owner_id
58 , a.service_type_id
59 , a.context_ccid
60 , a.package_name
61 , a.procedure_name
62 , b.service_type_code
63 FROM zx_api_registrations a
64 , zx_service_types b
65 , zx_api_owner_statuses c
66 WHERE a.service_type_id = b.service_type_id
67 and a.api_owner_id = c.api_owner_id
68 and upper(c.service_category_code) = upper(p_srvc_cat)
69 and a.api_owner_id = nvl(p_api_owner_id, a.api_owner_id)
70 ORDER BY a.api_owner_id asc
71 , b.service_type_code asc
72 , a.context_ccid asc;
73
74 /* -------------------------------------------------------------------------- */
75 /* Procedure to increment the counter */
76 /* -------------------------------------------------------------------------- */
77
78 PROCEDURE Increment_counter IS
79 BEGIN
80
81 g_counter := g_counter + 1;
82
83 END;
84
85 /* -------------------------------------------------------------------------- */
86 /* Procedure to print the strings */
87 /* -------------------------------------------------------------------------- */
88
89 PROCEDURE print_string(p_str IN VARCHAR2) IS
90 BEGIN
91
92 ad_ddl.build_statement(p_str,g_counter);
93 increment_counter;
94 -- dbms_output.put_line(p_str);
95 Fnd_file.put_line(FND_FILE.log,p_str);
96 END;
97
98 /* -------------------------------------------------------------------------- */
99 /* Procedure to print debug messages */
100 /* -------------------------------------------------------------------------- */
101
102 PROCEDURE print_debug(p_str IN VARCHAR2) IS
103 BEGIN
104
105 -- dbms_output.put_line(p_str);
106 Fnd_file.put_line(FND_FILE.log,p_str);
107 END;
108
109 /* -------------------------------------------------------------------------- */
110 /* Procedure to insert global variables for debug */
111 /* Bug # 4769082 */
112 /* -------------------------------------------------------------------------- */
113
114 PROCEDURE insert_gbl_var_for_debug(p_string IN VARCHAR2) IS
115 BEGIN
116 l_string := '/* Global Data Types */';
117 print_string(l_string);
118
119 l_string := 'G_PKG_NAME CONSTANT VARCHAR2(80) := ''';
120 l_string := l_string || p_string || ''';';
121 print_string(l_string);
122
123 l_string := 'G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;';
124 print_string(l_string);
125
126 l_string := 'G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;';
127 print_string(l_string);
128
129 l_string := 'G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;';
130 print_string(l_string);
131
132 l_string := 'G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;';
133 print_string(l_string);
134
135 l_string := 'G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;';
136 print_string(l_string);
137
138 l_string := 'G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;';
139 print_string(l_string);
140
141 l_string := 'G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;';
142 print_string(l_string);
143
144 l_string := 'G_MODULE_NAME CONSTANT VARCHAR2(80) := ''';
145 l_string := l_string || 'ZX.PLSQL.' || p_string || '.'';';
146 print_string(l_string);
147
148 END insert_gbl_var_for_debug;
149
150 /* -------------------------------------------------------------------------- */
151 /* Procedure to insert debug statement */
152 /* Bug # 4769082 */
153 /* -------------------------------------------------------------------------- */
154
155 PROCEDURE insert_debug( p_stmt_type IN VARCHAR2
156 , p_str IN VARCHAR2) IS
157 BEGIN
158 l_string := 'IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN';
159 print_string(l_string);
160
161 IF p_stmt_type = 'BEGIN' THEN
162 l_string := 'FND_LOG.STRING(G_LEVEL_PROCEDURE';
163 print_string(l_string);
164 l_string := ',G_MODULE_NAME || l_api_name ||';
165 -- l_string := l_string || '''-BEG''';
166 l_string := l_string || '''.BEGIN''';
167 print_string(l_string);
168 l_string := ',G_PKG_NAME||' ||''': '''|| '||l_api_name||' ||'''()+'||''');';
169 print_string(l_string);
170 ELSE
171 l_string := 'FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name';
172 l_string := l_string || ', ''' || p_str || ''');';
173 print_string(l_string);
174 END IF;
175
176 l_string := 'END IF;';
177 print_string(l_string);
178
179 END;
180
181 /* -------------------------------------------------------------------------- */
182 /* Procedure get_srvc_typ_params to get the parameter string */
183 /* -------------------------------------------------------------------------- */
184
185 PROCEDURE get_srvc_typ_params ( p_srvc_typ_id IN NUMBER,
186 p_service_type_code IN VARCHAR2,
187 p_data_transfer_code IN VARCHAR2,
188 p_location IN VARCHAR2,
189 p_package_name IN VARCHAR2,
190 p_procedure_name IN VARCHAR2,
191 x_param_string_tbl OUT NOCOPY t_srvcparamtbl,
192 x_no_of_strings OUT NOCOPY BINARY_INTEGER,
193 x_return_status OUT NOCOPY VARCHAR2) IS
194
195 CURSOR c_params IS
196 SELECT service_type_id,
197 parameter_name,
198 position,
199 param_usage_code,
200 map_global_var_code,
201 map_gbl_var_data_type
202 FROM zx_srvc_typ_params
203 WHERE service_type_id = p_srvc_typ_id;
204
205 l_stp_first BOOLEAN;
206 l_param_string VARCHAR2(255);
207 l_counter BINARY_INTEGER;
208 l_return_status VARCHAR2(1);
209 BEGIN
210 print_debug('-- CG: get_srvc_typ_params(+)');
211 print_debug('-- CG: p_location = '||p_location);
212 print_debug('-- CG: p_srvc_typ_id = '||to_char(p_srvc_typ_id));
213 print_debug('-- CG: p_service_type_code = '||p_service_type_code);
214 print_debug('-- CG: p_data_transfer_code = '||p_data_transfer_code);
215 l_stp_first := TRUE;
216 l_counter := 0;
217 FOR rec IN c_params LOOP
218
219 IF p_location = 'HEADER' THEN
220 l_param_string := ltrim(rtrim(rec.parameter_name)) || ' '
221 || ltrim(rtrim(rec.param_usage_code))||' '
222 || ltrim(rtrim(rec.map_gbl_var_data_type));
223 IF l_stp_first THEN
224 x_param_string_tbl(l_counter) := 'PROCEDURE ' || p_service_type_code
225 || '( p_context_ccid IN NUMBER';
226 l_counter := l_counter + 1;
227 l_stp_first := FALSE;
228 END IF;
229 l_param_string := ', ' || l_param_string;
230 END IF;
231
232 IF p_location = 'CALL' THEN
233 l_param_string := rec.map_global_var_code;
234 IF l_stp_first THEN
235 x_param_string_tbl(l_counter) := p_service_type_code
236 || '( p_context_ccid';
237 l_counter := l_counter + 1;
238 l_stp_first := FALSE;
239 END IF;
240 l_param_string := ', ' || l_param_string;
241 END IF;
242
243 IF p_location = 'PARTNER' THEN
244 l_param_string := rec.parameter_name;
245 IF l_stp_first THEN
246 l_param_string := p_package_name || '.'
247 || p_procedure_name || '('
248 || l_param_string;
249 l_stp_first := FALSE;
250 ELSE
251 l_param_string := ', ' || l_param_string;
252 END IF;
253 END IF;
254
255 IF rec.parameter_name = 'ERROR_STATUS' THEN
256 g_rtn_status_var := rec.map_global_var_code;
257 END IF;
258
259 IF rec.parameter_name = 'ERROR_DEBUG_MSG_TBL' THEN
260 g_rtn_msgtbl_var := rec.map_global_var_code;
261 END IF;
262
263 x_param_string_tbl(l_counter) := l_param_string ;
264 l_counter := l_counter + 1;
265
266 End Loop;
267
268 IF p_location = 'HEADER' THEN
269 l_param_string := ') IS';
270 ELSE
271 l_param_string := ');';
272 END IF;
273 x_param_string_tbl(l_counter) := l_param_string ;
274 x_no_of_strings := l_counter;
275
276 print_debug('-- CG: get_srvc_typ_params(-)');
277 EXCEPTION
278 WHEN OTHERS THEN
279 print_debug('-- CG: sqlerrm = '||sqlerrm);
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 END get_srvc_typ_params;
282
283
284 PROCEDURE create_third_party_pkg_spec
285 IS
286
287 BEGIN
288 l_string := 'CREATE OR REPLACE PACKAGE '||l_pack_name||' AS';
289 print_string(l_string);
290
291 l_string := 'PROCEDURE main_router (p_srvc_type_id IN NUMBER';
292 print_string(l_string);
293 l_string := ', p_context_ccid IN NUMBER';
294 print_string(l_string);
295 l_string := ', p_data_transfer_code IN VARCHAR2';
296 print_string(l_string);
297 l_string := ', x_return_status OUT NOCOPY VARCHAR2);';
298 print_string(l_string);
299
300 l_string := 'END '||l_pack_name||' ;';
301 print_string(l_string);
302
303 ad_ddl.create_plsql_object(
304 'APPS','ZX',l_pack_name,1,(g_counter-1),'TRUE',dummy);
305
306 END create_third_party_pkg_spec;
307 /*------------------------------------------------------------------------*/
308 /* Creating the individual package body for the api owner */
309 /*------------------------------------------------------------------------*/
310
311 PROCEDURE create_main_router_body (p_prvidx IN NUMBER)
312 IS
313
314 l_pls BOOLEAN;
315 l_return_status VARCHAR2(1);
316 BEGIN
317
318 l_string := 'PROCEDURE MAIN_ROUTER (p_srvc_type_id IN NUMBER';
319 print_string(l_string);
320 l_string := ', p_context_ccid IN NUMBER';
321 print_string(l_string);
322 l_string := ', p_data_transfer_code IN VARCHAR2';
323 print_string(l_string);
324 l_string := ', x_return_status OUT NOCOPY VARCHAR2';
325 print_string(l_string);
326 l_string := ') IS';
330 print_string(l_string);
327 print_string(l_string);
328
329 l_string := 'InvalidServiceType Exception;';
331
332 l_string := 'InvalidDataTransferMode Exception;';
333 print_string(l_string);
334
335 l_string := 'l_api_name CONSTANT VARCHAR2(80) := ''MAIN_ROUTER'';';
336 print_string(l_string);
337
338 l_string := ' BEGIN ';
339 print_string(l_string);
340
341 insert_debug('BEGIN', NULL);
342
343 l_string := 'x_return_status := FND_API.G_RET_STS_SUCCESS;';
344 print_string(l_string);
345
346 l_first := TRUE;
347
348 l_break := FALSE;
349
350 /* -------------------------------------------------------------------------- */
351 /* Check to see if the individual data transfer mode is PLS */
352 /* -------------------------------------------------------------------------- */
353
354 -- l_pls is for the first iteration
355
356 l_pls := TRUE;
357
358 FOR l_srvcidx in 1..(nvl(t_srvc.api_owner_id.LAST,0)) LOOP
359
360 IF (t_srvc.api_owner_id(l_srvcidx) = t_prv.api_owner_id(p_prvidx)) THEN
361
362 l_break := TRUE;
363
364 l_switch_mode := TRUE;
365
366 IF ( t_srvc.data_transfer_code(l_srvcidx) = 'PLS' and l_pls ) THEN
367
368 l_string := ' IF p_data_transfer_code = ''PLS'' THEN ';
369
370 print_string(l_string);
371
372 l_pls := FALSE;
373
374 /* -------------------------------------------------------------------------- */
375 /* IF the data transfer Mode is GTT then switch */
376 /* -------------------------------------------------------------------------- */
377
378 ELSIF ( t_srvc.data_transfer_code(l_srvcidx) = 'GTT' AND l_switch_mode) THEN
379
380 l_string := 'ELSE ';
381 print_string(l_string);
382
383 l_string := ' Raise InvalidServiceType; ';
384 print_string(l_string);
385
386 l_string := 'END IF; ';
387 print_string(l_string);
388
389 l_string := 'ELSIF p_data_transfer_code = ''GTT'' THEN ';
390 print_string(l_string);
391
392 l_first := TRUE;
393 l_switch_mode := FALSE;
394
395 END IF;
396
397 /* -------------------------------------------------------------------------- */
398 /* Giving a call to get_srvc_typ_params to get the parameters for the service*/
399 /* type */
400 /* -------------------------------------------------------------------------- */
401 get_srvc_typ_params ( t_srvc.service_type_id(l_srvcidx),
402 t_srvc.service_type_code(l_srvcidx),
403 t_srvc.data_transfer_code(l_srvcidx),
404 'CALL',
405 NULL,
406 NULL,
407 r_srvcparamtbl,
408 l_no_of_strings,
409 l_return_status);
410 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
411 print_debug('Parameter data not seeded for Service type Id '||to_char(t_srvc.service_type_id(l_srvcidx)));
412
413 EXIT;
414
415 END IF;
416
417 IF l_first THEN
418
419 l_string := ' IF p_srvc_type_id = '''||to_char(t_srvc.service_type_id(l_srvcidx))||''' THEN ';
420 print_string(l_string);
421
422 l_first := FALSE;
423
424 ELSE
425
426 l_string := ' ELSIF p_srvc_type_id = '''||to_char(t_srvc.service_type_id(l_srvcidx))||''' THEN ';
427 print_string(l_string);
428
429 END IF;
430
431 FOR i in 0..l_no_of_strings LOOP
432 l_string := r_srvcparamtbl(i);
433 print_string(l_string);
434 END LOOP;
435
436 IF g_rtn_status_var is NOT NULL THEN
437 l_string :=
438 ' IF '||g_rtn_status_var||' <> FND_API.G_RET_STS_SUCCESS THEN ';
439 print_string(l_string);
440
441 l_string := ' x_return_status := FND_API.G_RET_STS_ERROR;';
442 print_string(l_string);
443
444 l_string := ' Return ;';
445 print_string(l_string);
446
447 l_string := ' END IF ;';
448 print_string(l_string);
449
450 END IF;
451
452 END IF;
453
454 IF ((t_srvc.api_owner_id(l_srvcidx) <> t_prv.api_owner_id(p_prvidx)) AND l_break ) THEN
455
456 EXIT;
457
458 END IF;
459
460 END Loop;
461
462 l_string := 'ELSE ';
463 print_string(l_string);
464
465 l_string := ' Raise InvalidServiceType; ';
466 print_string(l_string);
467
468 l_string := 'END IF; ';
469 print_string(l_string);
470
471 l_string :=
472 'ELSE ';
473 print_string(l_string);
474
475 l_string := 'Raise Invaliddatatransfermode;';
476 print_string(l_string);
477
478 l_string := 'END IF; ';
479 print_string(l_string);
480
481 l_string := 'EXCEPTION ';
482 print_string(l_string);
483
487 l_string := 'FND_MESSAGE.SET_NAME(''ZX'',''ZX_INVALID_SERVICE_TYPE'');';
484 l_string := 'WHEN InvalidServiceType THEN ';
485 print_string(l_string);
486
488 print_string(l_string);
489
490 l_string := 'FND_MSG_PUB.ADD; ';
491 print_string(l_string);
492
493 l_string := 'Return ; ';
494 print_string(l_string);
495
496 l_string := 'WHEN InvalidDataTransferMode THEN ';
497 print_string(l_string);
498
499 l_string := 'FND_MESSAGE.SET_NAME(''ZX'',''ZX_INVALID_data_transfer_code'');';
500 print_string(l_string);
501
502 l_string := 'FND_MSG_PUB.ADD; ';
503 print_string(l_string);
504
505 l_string := 'Return ; ';
506 print_string(l_string);
507
508 l_string := 'END main_router;';
509 print_string(l_string);
510
511 END create_main_router_body;
512 /*--------------------------------------------------------------*/
513 /* Main */
514 /*--------------------------------------------------------------*/
515 PROCEDURE generate_code(
516 errbuf OUT NOCOPY VARCHAR2,
517 retcode OUT NOCOPY VARCHAR2,
518 p_srvc_category IN VARCHAR2,
519 p_api_owner_id IN NUMBER
520 ) IS
521 l_return_status VARCHAR2(1);
522 l_exists_in_owner_statuses VARCHAR2(1);
523 l_api_owner_id_char VARCHAR2(30);
524 BEGIN
525 /*-----------------------------------------------------------------------*/
526 /* If the code is to be generated for main wrapper package for service */
527 /* category */
528 /*-----------------------------------------------------------------------*/
529 Begin
530 SELECT 'Y' into l_exists
531 FROM dual
532 WHERE exists
533 ( SELECT api_owner_id
534 FROM zx_api_owner_statuses
535 WHERE
536 status_code in ('DELETED','NEW')
537 );
538 EXCEPTION
539 WHEN NO_DATA_FOUND THEN
540 l_exists := 'N';
541 END;
542
543 retcode := FND_API.G_RET_STS_SUCCESS ;
544
545
546 --Initialize;
547 /* -------------------------------------------------------------------------- */
548 /* Fetching the individual cursors into record of tables. */
549 /* -------------------------------------------------------------------------- */
550
551 IF p_api_owner_id is NOT NULL THEN
552 l_exists_in_owner_statuses := 'Y';
553 BEGIN
554 SELECT 'Y'
555 INTO l_exists_in_owner_statuses
556 FROM zx_api_owner_statuses
557 WHERE upper(service_category_code) = upper(p_srvc_category)
558 AND api_owner_id = p_api_owner_id;
559 EXCEPTION
560 WHEN OTHERS THEN
561 l_exists_in_owner_statuses := 'N';
562 END;
563
564 IF l_exists_in_owner_statuses = 'N' THEN
565 print_debug('-- CG: Inserting a record in zx_api_owner_statuses for API owners');
566 INSERT INTO zx_api_owner_statuses(api_owner_id
567 , service_category_code
568 , status_code
569 , creation_date
570 , created_by
571 , last_update_date
572 , last_updated_by
573 , last_update_login)
574 values(p_api_owner_id
575 , p_srvc_category
576 , 'NEW'
577 , sysdate
578 , fnd_global.user_id
579 , sysdate
580 , fnd_global.user_id
581 , fnd_global.user_id);
582 END IF;
583
584 END IF;
585
586 Open c_apiowner(p_srvc_category,p_api_owner_id);
587
588 print_debug('-- CG: Opening for API owners');
589
590 LOOP
591 FETCH c_apiowner BULK COLLECT INTO
592 t_prv.api_owner_id,
593 t_prv.status_code;
594 EXIT WHEN c_apiowner%NOTFOUND;
595 END LOOP;
596
597 print_debug('-- CG: Closing for API owners');
598 Close c_apiowner;
599
600 Open c_srvctyp(p_srvc_category,p_api_owner_id);
601
602 print_debug('-- CG: Opening for service types');
603
604 LOOP
605
606 FETCH c_srvctyp BULK COLLECT INTO
607 t_srvc.api_owner_id,
608 t_srvc.service_type_id,
609 t_srvc.service_type_code,
610 t_srvc.data_transfer_code;
611
612 EXIT WHEN c_srvctyp%NOTFOUND;
613
614 END LOOP;
615
616 IF c_srvctyp%ROWCOUNT = 0 THEN
617 print_debug('-- CG: No data found in ZX_API_REGISTRATIONS');
618 Close c_srvctyp;
619 retcode := FND_API.G_RET_STS_ERROR;
620 return;
621 ELSE
622 Close c_srvctyp;
623 END IF;
624
625
626 Open c_api(p_srvc_category,p_api_owner_id);
627 LOOP
628
629 print_debug('-- CG: Opening for apis');
630
631 FETCH c_api BULK COLLECT INTO
632 t_api.api_owner_id,
633 t_api.service_type_id,
634 t_api.context_ccid,
635 t_api.package_name,
636 t_api.procedure_name,
637 t_api.service_type_code;
638
642
639 EXIT WHEN c_api%NOTFOUND;
640
641 END LOOP;
643 print_debug('-- CG: before close c_api');
644 Close c_api;
645
646 g_counter := 1;
647
648 l_count := 0;
649
650 l_break := FALSE;
651
652 /*------------------------------------------------------------------------*/
653 /* Generating the individual provider package */
654 /* to call the service category services for different service types */
655 /*------------------------------------------------------------------------*/
656
657 IF t_prv.api_owner_id.LAST < 0 THEN
658 print_debug('-- CG: before for loop GCO');
659 ELSE
660 print_debug('-- CG: before for loop '||to_char(t_prv.api_owner_id.LAST));
661 END IF;
662
663 For l_prvidx in 1..nvl(t_prv.api_owner_id.LAST,0) LOOP
664
665 print_debug('-- CG: before l_pack_name ');
666
667 IF t_prv.api_owner_id(l_prvidx) = -99 THEN
668 l_api_owner_id_char := 'GCO';
669 ELSE
670 l_api_owner_id_char := to_char(t_prv.api_owner_id(l_prvidx));
671 END IF;
672 l_pack_name := 'ZX_THIRD_PARTY_'|| ltrim(rtrim(l_api_owner_id_char)) ||'_PKG';
673
674 /*------------------------------------------------------------------------*/
675 /* IF all the statuses for the provider are DELETED then drop the */
676 /* package */
677 /*------------------------------------------------------------------------*/
678
679 IF (t_prv.status_code(l_prvidx) = 'DELETED') THEN
680
681 ad_ddl.do_ddl(
682 'APPS','ZX','AD_DDL.DROP_TABLE','DROP PACKAGE '||l_pack_name,l_pack_name );
683
684 ELSIF (t_prv.status_code(l_prvidx) <> 'GENERATED') THEN
685
686 g_counter := 1;
687 /*------------------------------------------------------------------------*/
688 /* IF distinct statuses are not GENERATED then only generate the */
689 /* individual package specification */
690 /*------------------------------------------------------------------------*/
691
692 print_debug('-- CG: create_third_party ');
693 create_third_party_pkg_spec;
694 /* ---------------------------------------------------------------------------
695 Looping through the record of tables again for generating the individual
696 service type procedures
697 ----------------------------------------------------------------------------*/
698
699 g_counter := 1;
700
701 l_string := 'CREATE OR REPLACE PACKAGE BODY '||l_pack_name||' AS';
702 print_string(l_string);
703
704 print_debug('-- CG: Before insert_gbl_val_for_debug');
705 insert_gbl_var_for_debug(l_pack_name);
706
707 l_break := FALSE;
708
709 FOR l_srvcidx in 1..nvl(t_srvc.api_owner_id.LAST,0) LOOP
710
711 IF (t_srvc.api_owner_id(l_srvcidx) = t_prv.api_owner_id(l_prvidx)) THEN
712
713 l_break := TRUE;
714
715 l_switch_mode := TRUE;
716
717 get_srvc_typ_params ( t_srvc.service_type_id(l_srvcidx),
718 t_srvc.service_type_code(l_srvcidx),
719 t_srvc.data_transfer_code(l_srvcidx),
720 'HEADER',
721 NULL,
722 NULL,
723 r_srvcparamtbl,
724 l_no_of_strings,
725 l_return_status);
726
727 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
728 retcode := l_return_status;
729 print_debug('Parameter data not seeded for Service type Id '||to_char(t_srvc.service_type_id(l_srvcidx)));
730 EXIT;
731
732 END IF;
733
734 print_debug('-- CG: after get_srvctyp_params');
735 FOR i in 0..l_no_of_strings LOOP
736 l_string := r_srvcparamtbl(i);
737 print_string(l_string);
738 END LOOP;
739
740 l_string := 'InvalidContextCcid Exception;';
741 print_string(l_string);
742
743 l_string := 'l_api_name CONSTANT VARCHAR2(80) := ''';
744 l_string := l_string || t_srvc.service_type_code(l_srvcidx)|| ''';';
745 print_string(l_string);
746
747 l_string := ' Begin ';
748 print_string(l_string);
749
750 insert_debug('BEGIN', NULL);
751
752 l_first := TRUE;
753
754 FOR l_apiidx in 1..nvl(t_api.api_owner_id.LAST,0) Loop
755
756 IF (t_srvc.api_owner_id(l_srvcidx) = t_api.api_owner_id(l_apiidx) AND
757 t_srvc.service_type_id(l_srvcidx) = t_api.service_type_id(l_apiidx)) THEN
758
759 IF l_first THEN
760
761 l_string := 'IF p_context_ccid = '||to_char(t_api.context_ccid(l_apiidx))||' THEN ';
762 print_string(l_string);
763 l_first := FALSE;
764
765 ELSE
766
767 l_string := 'ELSIF p_context_ccid = '||to_char(t_api.context_ccid(l_apiidx))||' THEN ';
768 print_string(l_string);
769
770 END IF;
771
772 print_debug('-- CG: before 2nd get_srvctyp_params');
773 get_srvc_typ_params ( t_srvc.service_type_id(l_srvcidx),
774 t_srvc.service_type_code(l_srvcidx),
775 t_srvc.data_transfer_code(l_srvcidx),
776 'PARTNER',
780 l_no_of_strings,
777 t_api.package_name(l_apiidx),
778 t_api.procedure_name(l_apiidx),
779 r_srvcparamtbl,
781 l_return_status);
782
783 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
784 retcode := l_return_status;
785 print_debug('Parameter data not seeded for Service type Id '||to_char(t_srvc.service_type_id(l_srvcidx)));
786 EXIT;
787
788 END IF;
789
790 FOR i in 0..l_no_of_strings LOOP
791 l_string := r_srvcparamtbl(i);
792 print_string(l_string);
793 END LOOP;
794
795 END IF;
796
797 END LOOP;
798
799 l_string := 'ELSE ';
800 print_string(l_string);
801
802 l_string := 'Raise InvalidContextCcid; ';
803 print_string(l_string);
804
805 l_string := 'END IF; ';
806 print_string(l_string);
807
808 l_string := 'EXCEPTION ';
809 print_string(l_string);
810
811 l_string := 'WHEN InvalidContextccid THEN ';
812 print_string(l_string);
813
814 l_string := 'ERROR_STATUS := FND_API.G_RET_STS_ERROR;';
815 print_string(l_string);
816
817 l_string := 'FND_MESSAGE.SET_NAME(''ZX'',''ZX_INVALID_CONTEXT_CCID'');';
818 print_string(l_string);
819
820 l_string := 'FND_MSG_PUB.ADD;';
821 print_string(l_string);
822
823 l_string := 'Return; ';
824 print_string(l_string);
825
826 l_string := 'END '||t_srvc.service_type_code(l_srvcidx)||';';
827 print_string(l_string);
828
829 /* -----------------------------------------------------------------------------*/
830 /* Updating the status of all records to GENERATED */
831 /* ----------------------------------------------------------------------------- */
832
833
834 UPDATE ZX_API_OWNER_STATUSES SET STATUS_CODE = 'GENERATED'
835 WHERE api_owner_id = t_prv.api_owner_id(l_prvidx);
836
837 END IF;
838
839 IF ((t_srvc.api_owner_id(l_srvcidx) <> t_prv.api_owner_id(l_prvidx))
840 AND l_break ) THEN
841
842 EXIT;
843
844 END IF;
845
846 END LOOP;
847
848 print_debug('-- CG: before create_main_router_body');
849 create_main_router_body(l_prvidx);
850 l_string := 'END '||l_pack_name||' ;';
851 print_string(l_string);
852 print_debug('-- CG: Before creating pl/sql object '||l_pack_name || 'G_counter = '|| to_char(g_counter));
853
854 ad_ddl.create_plsql_object(
855 'APPS','ZX',l_pack_name,1,(g_counter-1),'TRUE',dummy);
856
857 print_debug('-- CG: After creating pl/sql object');
858
859
860 END IF;
861
862 END LOOP;
863
864 /*------------------------------------------------------------------------*/
865 /* Creating the main wrapper package body */
866 /*------------------------------------------------------------------------*/
867
868 IF (nvl(l_exists,'N') = 'Y') THEN
869
870 g_counter := 1;
871
872 l_first := TRUE;
873
874 l_string := 'CREATE OR REPLACE PACKAGE BODY ZX_'||p_srvc_category||'_PKG AS';
875 print_string(l_string);
876
877 l_pack_name := 'ZX_' || p_srvc_category || '_PKG';
878 insert_gbl_var_for_debug(l_pack_name);
879
880 l_string := 'PROCEDURE INVOKE_THIRD_PARTY_INTERFACE(p_api_owner_id IN Number';
881 print_string(l_string);
882 l_string := ', p_service_type_id IN Number';
883 print_string(l_string);
884 l_string := ', p_context_ccid IN Number';
885 print_string(l_string);
886 l_string := ', p_data_transfer_mode IN VARCHAR2';
887 print_string(l_string);
888 l_string := ', x_return_status OUT NOCOPY VARCHAR2) IS ';
889 print_string(l_string);
890
891 l_string := 'InvalidApiownId Exception; ';
892 print_string(l_string);
893
894 l_string := 'l_api_name CONSTANT VARCHAR2(80) := ''INVOKE_THIRD_PARTY_INTERFACE'';';
895 print_string(l_string);
896
897 l_string := 'Begin ';
898 print_string(l_string);
899
900 insert_debug('BEGIN', NULL);
901
902 For l_prvidx in 1..t_prv.api_owner_id.LAST LOOP
903
904 IF l_first THEN
905
906 l_string := 'IF p_api_owner_id = '||to_char(t_prv.api_owner_id(l_prvidx))||' THEN ';
907 print_string(l_string);
908
909 l_first := FALSE;
910
911 ELSE
912
913 l_string := 'ELSIF p_api_owner_id = '||to_char(t_prv.api_owner_id(l_prvidx))||' THEN ';
914 print_string(l_string);
915
916 END IF;
917
918 IF t_prv.api_owner_id(l_prvidx) = -99 THEN
919 l_api_owner_id_char := 'GCO';
920 ELSE
921 l_api_owner_id_char := to_char(t_prv.api_owner_id(l_prvidx));
922 END IF;
923
924 l_string := 'ZX_Third_party_' ||
925 rtrim(ltrim(l_api_owner_id_char)) ||
926 -- rtrim(ltrim(to_char(t_prv.api_owner_id(l_prvidx)))) ||
927 '_pkg.main_router(p_service_type_id';
928 print_string(l_string);
929 l_string := ', p_context_ccid';
930 print_string(l_string);
931 l_string := ', p_data_transfer_mode';
932 print_string(l_string);
933 l_string := ', x_return_status );';
934 print_string(l_string);
935
936 l_string := 'IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN';
937 print_string(l_string);
938
939 l_string := ' Return; ';
940 print_string(l_string);
941
942 l_string := ' END IF; ';
943 print_string(l_string);
944
945 END LOOP;
946
947 l_string := 'ELSE ';
948 print_string(l_string);
949
950 l_string := 'Raise InvalidApiownid ; ';
951 print_string(l_string);
952
953 l_string := 'END IF; ';
954 print_string(l_string);
955
956 l_string := 'END invoke_third_party_interface; ';
957 print_string(l_string);
958
959 l_string := 'END ZX_'||p_srvc_category||'_PKG ;';
960 print_string(l_string);
961
962 ad_ddl.create_plsql_object(
963 'APPS','ZX','ZX_'||p_srvc_category||'_PKG',1,(g_counter-1),'TRUE',dummy);
964
965 END IF;
966
967 END generate_code;
968 END ZX_TPI_PLUGIN_PKG;