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