[Home] [Help]
PACKAGE BODY: APPS.FTE_PTRACKING
Source
1 PACKAGE BODY FTE_PTRACKING AS
2 /* $Header: FTEPTRKB.pls 120.3 2005/08/18 22:10:00 shravisa noship $ */
3
4 --===================
5 -- TYPES
6 --===================
7
8
9 --===================
10 -- PROCEDURES
11 --===================
12
13
14
15 PROCEDURE GetParam(
16 p_key_list IN KeyTable,
17 p_value_list IN ValueTable,
18 p_type_list IN TypeTable,
19 p_key IN VARCHAR2,
20 x_value OUT NOCOPY VARCHAR2,
21 x_type OUT NOCOPY VARCHAR2) IS
22
23 i BINARY_INTEGER := 0;
24
25 -- This procedure finds the value corresponding to a key given a list of keys and values
26
27 BEGIN
28
29 -- dbms_output.put_line('-- IN GetParam ');
30 -- dbms_output.put_line(' p_key '|| p_key);
31
32 LOOP
33 i := i + 1;
34 IF p_key_list(i) = p_key
35 THEN
36 x_value := p_value_list(i);
37 x_type := p_type_list(i);
38 EXIT;
39 END IF;
40 END LOOP;
41
42
43 -- dbms_output.put_line(' x_value '|| x_value);
44 -- dbms_output.put_line(' x_type '|| x_type);
45 -- dbms_output.put_line('-- LEAVING GetParam ');
46
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 x_value := null;
51 x_type := 'NONE';
52 -- dbms_output.put_line('-- LEAVING GetParam with exception: '||x_type);
53
54 END GetParam;
55
56
57 PROCEDURE GetTokenValue(
58 p_table IN VARCHAR2,
59 p_column IN VARCHAR2,
60 p_kc1 IN VARCHAR2,
61 p_kc2 IN VARCHAR2,
62 p_kc3 IN VARCHAR2,
63 p_kc4 IN VARCHAR2,
64 p_kc5 IN VARCHAR2,
65 p_key_list IN KeyTable,
66 p_value_list IN ValueTable,
67 p_type_list IN TypeTable,
68 x_token_value OUT NOCOPY VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_err_msg OUT NOCOPY VARCHAR2) IS
71
72 -- This procedure takes a table, a source column, and a set of keys and values to
73 -- make a query to extract the contents of the source column from the source table
74 -- satisfying the set of keys.
75
76 l_k1 VARCHAR2(100) := null;
77 l_k2 VARCHAR2(100) := null;
78 l_k3 VARCHAR2(100) := null;
79 l_k4 VARCHAR2(100) := null;
80 l_k5 VARCHAR2(100) := null;
81
82 l_query VARCHAR2(2500) := null;
83 l_param VARCHAR2(100);
84 l_param1 VARCHAR2(100) := null;
85 l_param2 VARCHAR2(100) := null;
86 l_param3 VARCHAR2(100) := null;
87 l_param4 VARCHAR2(100) := null;
88 l_param5 VARCHAR2(100) := null;
89 l_type VARCHAR2(10);
90
91
92 KEY_MISSING EXCEPTION;
93
94 BEGIN
95
96 -- should check data integrity. Make sure token is built correctly: can't have first kc null.
97
98 /*
99 dbms_output.put_line('-- IN GetTokenValue ');
100 dbms_output.put_line(' p_table '|| p_table);
101 dbms_output.put_line(' p_column '|| p_column);
102 dbms_output.put_line(' p_kc1 '|| p_kc1);
103 dbms_output.put_line(' p_kc2 '|| p_kc2);
104 dbms_output.put_line(' p_kc3 '|| p_kc3);
105 dbms_output.put_line(' p_kc4 '|| p_kc4);
106 dbms_output.put_line(' p_kc5 '|| p_kc5);
107 */
108
109 l_query := 'SELECT ' ||p_column || ' FROM ' || p_table;
110
111 IF p_kc1 is null THEN
112 GOTO after_query;
113 ELSE
114
115 GetParam(p_key_list, p_value_list, p_type_list, p_kc1, l_param1, l_type);
116
117 IF l_type = 'NONE' THEN l_type := p_kc1; RAISE KEY_MISSING;
118 ELSIF l_type = 'SYSDATE' THEN l_query := l_query || ' WHERE ' || p_kc1 || ' = sysdate';
119 ELSIF l_type = 'DATE' THEN
120 l_query := l_query || ' WHERE ' || p_kc1 || ' = to_date(:1 , ''dd-mm-yyyy hh24:mi:ss'')';
121 ELSIF l_type = 'VARCHAR2' OR l_type = 'NUMBER' THEN l_query := l_query || ' WHERE ' || p_kc1 || ' = :1 ';
122 END IF;
123 l_param := l_param1;
124
125 END IF;
126
127
128
129 IF p_kc2 is null THEN
130
131 BEGIN
132 EXECUTE IMMEDIATE l_query INTO x_token_value USING l_param1;
133 EXCEPTION
134 WHEN OTHERS THEN
135 NULL;
136 END;
137
138
139 GOTO after_query;
140 ELSE
141 GetParam(p_key_list, p_value_list, p_type_list, p_kc2, l_param2, l_type);
142 IF l_type = 'NONE' THEN l_type := p_kc2; RAISE KEY_MISSING;
143 ELSIF l_type = 'SYSDATE' THEN l_query := l_query || ' AND ' || p_kc2 || ' = sysdate';
144 ELSIF l_type = 'DATE' THEN
145 l_query := l_query || ' AND ' || p_kc2 || ' = to_date( :2, ''dd-mm-yyyy hh24:mi:ss'')';
146 ELSIF l_type = 'VARCHAR2' OR l_type = 'NUMBER' THEN l_query := l_query || ' AND ' || p_kc2 || ' = :2 ';
147 END IF;
148 l_param := l_param2;
149 END IF;
150
151 IF p_kc3 is null THEN
152 begin
153 EXECUTE IMMEDIATE l_query INTO x_token_value USING l_param1, l_param2;
154 EXCEPTION
155 WHEN OTHERS THEN
156 NULL;
157 END;
158 GOTO after_query;
159 ELSE
160 GetParam(p_key_list, p_value_list, p_type_list, p_kc3, l_param3, l_type);
161 IF l_type = 'NONE' THEN l_type := p_kc3; RAISE KEY_MISSING;
162 ELSIF l_type = 'SYSDATE' THEN l_query := l_query || ' AND ' || p_kc3 || ' = sysdate';
163 ELSIF l_type = 'DATE' THEN
164 l_query := l_query || ' AND ' || p_kc3 || ' = to_date(:3, ''dd-mm-yyyy hh24:mi:ss'')';
165 ELSIF l_type = 'VARCHAR2' OR l_type = 'NUMBER' THEN l_query := l_query || ' AND ' || p_kc3 || ' = :3 ';
166 END IF;
167 l_param := l_param3;
168 END IF;
169
170 IF p_kc4 is null THEN
171 begin
172 EXECUTE IMMEDIATE l_query INTO x_token_value USING l_param1, l_param2, l_param3;
173 EXCEPTION
174 WHEN OTHERS THEN
175 NULL;
176 END;
177 GOTO after_query;
178 ELSE
179 GetParam(p_key_list, p_value_list, p_type_list, p_kc4, l_param4, l_type);
180 IF l_type = 'NONE' THEN l_type := p_kc4; RAISE KEY_MISSING;
181 ELSIF l_type = 'SYSDATE' THEN l_query := l_query || ' AND ' || p_kc4 || ' = sysdate';
182 ELSIF l_type = 'DATE' THEN
183 l_query := l_query || ' AND ' || p_kc4 || ' = to_date( :4, ''dd-mm-yyyy hh24:mi:ss'')';
184 ELSIF l_type = 'VARCHAR2' OR l_type = 'NUMBER' THEN l_query := l_query || ' AND ' || p_kc4 || ' = :4 ';
185 END IF;
186 l_param := l_param4;
187 END IF;
188
189 IF p_kc5 is null THEN
190 BEGIN
191 EXECUTE IMMEDIATE l_query INTO x_token_value USING l_param1, l_param2, l_param3, l_param4;
192 EXCEPTION
193 WHEN OTHERS THEN
194 NULL;
195 END;
196 GOTO after_query;
197 ELSE
198 GetParam(p_key_list, p_value_list, p_type_list, p_kc5, l_param5, l_type);
199 IF l_type = 'NONE' THEN l_type := p_kc5; RAISE KEY_MISSING;
200 ELSIF l_type = 'SYSDATE' THEN l_query := l_query || ' AND ' || p_kc5 || ' = sysdate';
201 ELSIF l_type = 'DATE' THEN
202 l_query := l_query || ' AND ' || p_kc5 || ' = to_date( :5, ''dd-mm-yyyy hh24:mi:ss'')';
203 ELSIF l_type = 'VARCHAR2' OR l_type = 'NUMBER' THEN l_query := l_query || ' AND ' || p_kc5 || ' = :5 ';
204 END IF;
205 BEGIN
206 EXECUTE IMMEDIATE l_query INTO x_token_value USING l_param1, l_param2, l_param3, l_param4, l_param5;
207 EXCEPTION
208 WHEN OTHERS THEN
209 NULL;
210 END;
211 l_param := l_param5;
212 END IF;
213
214
215 <<after_query>>
216
217 x_return_status := 'S';
218
219 /*
220 dbms_output.put_line(' l_query :');
221 IF LENGTH(l_query)<256 THEN dbms_output.put_line(l_query);
222 ELSE
223 dbms_output.put_line(SUBSTR(l_query,0,255));
224 IF LENGTH(l_query)>512 THEN dbms_output.put_line(SUBSTR(l_query,256,255));
225 ELSE
226 dbms_output.put_line(SUBSTR(l_query,256));
227 END IF;
228 END IF;
229
230 dbms_output.put_line(' x_token_value '|| x_token_value);
231 dbms_output.put_line(' x_return_status '|| x_return_status);
232 dbms_output.put_line(' x_err_msg '|| x_err_msg);
233 dbms_output.put_line('-- LEAVING GetTokenValue');
234 */
235
236 EXCEPTION
237 WHEN KEY_MISSING THEN
238 x_return_status := 'E';
239 x_err_msg := 'MISSING KEY PARAM: ' || l_param;
240 -- dbms_output.put_line('-- LEAVING GetTokenValue with exception: '||x_err_msg);
241 --WHEN OTHERS THEN
242 -- x_return_status := 'E';
243 -- x_err_msg := 'UNKNOWN ERROR';
244 -- dbms_output.put_line('-- LEAVING GetTokenValue with exception: '||x_err_msg);
245
246
247 END GetTokenValue;
248
249
250 FUNCTION InstallCheck return VARCHAR2 IS
251
252 l_fte_install_status VARCHAR2(10):= null;
253 l_industry VARCHAR2(50):= null;
254
255 BEGIN
256
257 IF(TRUE) THEN RETURN 'Y'; -- added to remove functionality 5/14
258 END IF;
259
260 IF (fnd_installation.get(716, 716,l_fte_install_status,l_industry)) THEN
261 IF (l_fte_install_status = 'I') THEN
262 RETURN 'Y';
263 ELSE
264 RETURN 'N';
265 END IF;
266 END IF;
267 END InstallCheck;
268
269
270 PROCEDURE Validate(
271 p_application_id IN NUMBER,
272 p_org_id IN NUMBER,
273 p_carrier_id IN NUMBER,
274 p_tracking_event IN VARCHAR2,
275 p_granularity IN NUMBER,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_page_id OUT NOCOPY NUMBER,
278 x_base_url OUT NOCOPY VARCHAR2,
279 x_request_method OUT NOCOPY VARCHAR2,
280 x_name OUT NOCOPY VARCHAR2,
281 x_description OUT NOCOPY VARCHAR2,
282 x_token OUT NOCOPY VARCHAR2
283 ) IS
284
285 -- this procedure returns a stored page if this granularity, tracking_event and
286 -- application combination is valid and includes information for this carrier and organization.
287
288 l_org_id NUMBER;
289
290
291 CURSOR get_page(x_org_id NUMBER) IS
292 SELECT page_id, base_url, request_method, name, description FROM FTE_PTRK_PAGES
293 WHERE STATUS = 'COMPLETED'
294 AND OWNER_APPLICATION_ID = p_application_id
295 AND CARRIER_PARTY_ID = p_carrier_id
296 AND GRANULARITY <= p_granularity
297 AND BUSINESS_CONCEPTS like ('%'||p_tracking_event||'%')
298 AND ORGANIZATIONS like ('%'||x_org_id||'%')
299 ORDER BY ORGANIZATIONS ASC; -- this forces the most restrictive page record.
300
301 CURSOR get_token(l_page_id NUMBER) IS
302 SELECT --p.param_id, p.param_name,
303 s.token_name
304 FROM fte_ptrk_params p, fte_ptrk_sources s
305 WHERE p.token_id = s.token_id
306 AND p.flag = 'Y'
307 AND p.page_id = l_page_id;
308
309
310 BEGIN
311
312 IF (InstallCheck() = 'N')
313 THEN
314 x_return_status := 'E';
315 x_page_id := null;
316 x_base_url := null;
317 x_request_method := null;
318 x_name := null;
319 x_description := null;
320 x_token := null;
321
322 RETURN;
323
324 END IF;
325
326
327 -- dbms_output.put_line('-- IN Validate ');
328 -- dbms_output.put_line(' p_application_id '|| p_application_id);
329 -- dbms_output.put_line(' p_org_id '|| p_org_id);
330 -- dbms_output.put_line(' p_carrier_id '|| p_carrier_id);
331 -- dbms_output.put_line(' p_tracking_event '|| p_tracking_event);
332 -- dbms_output.put_line(' p_granularity '|| p_granularity);
333
334 --------------------------------------------------
335 -- removed for pack J
336
337 -- SELECT organization_id into l_org_id
338 -- FROM wsh_new_deliveries
339 -- WHERE delivery_id = p_org_id;
340
341 --------------------------------------------------
342
343
344
345 OPEN get_page(p_org_id); -- changed from l_org_id for packJ
346 FETCH get_page
347 INTO x_page_id, x_base_url, x_request_method, x_name, x_description;
348
349 IF (get_page%NOTFOUND) THEN
350 x_return_status := 'NO_PAGE';
351 ELSE
352 x_return_status := 'VALID';
353 OPEN get_token(x_page_id);
354 FETCH get_token INTO x_token;
355 IF (get_token%NOTFOUND) THEN x_token := null; END IF;
356 CLOSE get_token;
357 END IF;
358 CLOSE get_page;
359
360
361 -- dbms_output.put_line(' x_return_status '|| x_return_status);
362 -- dbms_output.put_line(' x_page_id '|| x_page_id);
363 -- dbms_output.put_line(' x_base_url '|| x_base_url);
364 -- dbms_output.put_line(' x_request_method '|| x_request_method);
365 -- dbms_output.put_line(' x_name '|| x_name);
366 -- dbms_output.put_line(' x_description '|| x_description);
367 -- dbms_output.put_line('-- LEAVING Validate');
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 x_return_status := 'ERROR';
372 -- dbms_output.put_line('-- LEAVING Validate with exception: '|| SQLERRM);
373
374
375
376 END Validate;
377
378 PROCEDURE ParseParamList(
379 p_param_list IN VARCHAR2,
380 x_keyTable OUT NOCOPY KeyTable,
381 x_valueTable OUT NOCOPY ValueTable,
382 x_typeTable OUT NOCOPY TypeTable
383 ) IS
384
385
386
387 l_key VARCHAR2(50);
388 l_value VARCHAR2(100);
389 l_type VARCHAR2(20);
390
391 i NUMBER := 0;
392 j NUMBER := 1;
393 k NUMBER := 0;
394 l NUMBER := 0;
395 c CHAR := ' ';
396 c2 CHAR :=' ';
397
398 --type KeyTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
399 --type ValueTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
400 --type TypeTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
401
402 l_keyTable KeyTable;
403 l_valueTable ValueTable;
404 l_typeTable TypeTable;
405
406 param_index NUMBER := 1;
407 l_param_list VARCHAR2(10000);
408
409
410 BEGIN
411
412 l_param_list := p_param_list || ';';
413 FOR i IN 1..(LENGTH(l_param_list)) LOOP
414 c2 := c;
415 c := SUBSTR(l_param_list, i, 1);
416 IF c = ';' AND c2 <> '\' THEN
417 l_key := SUBSTR(l_param_list, j, k-j-1);
418 l_value := SUBSTR(l_param_list, k, l-k-1);
419 l_type := SUBSTR(l_param_list, l, i-l);
420 l_keyTable(param_index) := l_key;
421 l_valueTable(param_index) := l_value;
422 l_typeTable(param_index) := l_type;
423 param_index := param_index + 1;
424 j := i+1;
425 -- dbms_output.put_line(' l_key, l_value, l_type:'||
426 -- l_key ||','|| l_value ||','|| l_type);
427 ELSIF c = ',' AND c2 <> '\' THEN
428 IF l < k THEN l := i + 1;
429 ELSE k := i + 1;
430 END IF;
431 END IF;
432 END LOOP;
433
434 x_keyTable := l_keyTable;
435 x_valueTable := l_valueTable;
436 x_typeTable := l_typeTable;
437
438
439 END ParseParamList;
440
441
442
443 PROCEDURE Punchout(
444 p_application_id IN NUMBER,
445 p_org_id IN NUMBER,
446 p_carrier_id IN NUMBER,
447 p_tracking_event IN VARCHAR2,
448 p_granularity IN NUMBER,
449 p_param_list IN VARCHAR2,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_err_msg OUT NOCOPY VARCHAR2,
452 x_form_output OUT NOCOPY VARCHAR2) IS
453
454 -- Punchout takes in an application, organization, carrier, tracking event, granularity
455 -- and a list of key parameters from the user in order to create a form to punch out to a
456 -- remote carrier's tracking site. This differs from the other overloaded call in that the
457 -- key/value pairs are represented in a single long VARCHAR2.
458
459
460 --type KeyTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
461 --type ValueTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
462 --type TypeTable is TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
463
464 l_keyTable KeyTable;
465 l_valueTable ValueTable;
466 l_typeTable TypeTable;
467
468
469 BEGIN
470
471 IF (InstallCheck() = 'N')
472 THEN
473 x_return_status := 'E';
474 x_err_msg := 'FTE Not Installed';
475 x_form_output := null;
476
477 RETURN;
478
479 END IF;
480
481 -- dbms_output.put_line('-- IN Punchout (1)');
482 -- dbms_output.put_line('-- app_id : ' || p_application_id);
483 -- dbms_output.put_line('-- org_id : ' || p_org_id);
484 -- dbms_output.put_line('-- carrier_id : ' || p_carrier_id);
485 -- dbms_output.put_line('-- tracking_event : ' || p_tracking_event);
486 -- dbms_output.put_line('-- granularity : ' || p_granularity);
487 -- dbms_output.put_line('-- param_list : ' || p_param_list);
488
489 ParseParamList(p_param_list, l_keyTable, l_valueTable, l_typeTable);
490
491 Punchout(
492 p_application_id,
493 p_org_id,
494 p_carrier_id,
495 p_tracking_event,
496 p_granularity,
497 l_keyTable,
498 l_valueTable,
499 l_typeTable,
500 x_return_status,
501 x_err_msg,
502 x_form_output);
503
504
505 END Punchout;
506
507
508
509
510
511
512
513 PROCEDURE FindTokenValue(
514 p_application_id IN NUMBER,
515 p_token_name IN VARCHAR2,
516 p_param_list IN VARCHAR2,
517 x_token_value OUT NOCOPY VARCHAR2,
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_err_msg OUT NOCOPY VARCHAR2
520 ) IS
521
522 l_source_table VARCHAR2(30);
523 l_source_column VARCHAR2(30);
524 l_static_value VARCHAR2(100);
525 l_key_column1 VARCHAR2(50);
526 l_key_column2 VARCHAR2(50);
527 l_key_column3 VARCHAR2(50);
528 l_key_column4 VARCHAR2(50);
529 l_key_column5 VARCHAR2(50);
530
531 l_keyTable KeyTable;
532 l_valueTable ValueTable;
533 l_typeTable TypeTable;
534
535 l_token_value VARCHAR2(100);
536
537 CURSOR c_token IS
538 SELECT t.source_table, t.source_column, t.key_column1, t.key_column2, t.key_column3, t.key_column4, t.key_column5
539 FROM FTE_PTRK_SOURCES t
540 WHERE t.owner_application_id = p_application_id
541 AND t.token_name = p_token_name;
542
543
544 BEGIN
545
546 IF (InstallCheck() = 'N')
547 THEN
548 x_return_status := 'E';
549 x_err_msg := 'FTE Not Installed';
550 x_token_value := null;
551
552 RETURN;
553
554 END IF;
555
556 OPEN c_token;
557 FETCH c_token INTO l_source_table, l_source_column,
558 l_key_column1, l_key_column2, l_key_column3, l_key_column4, l_key_column5;
559 CLOSE c_token;
560
561
562 ParseParamList(p_param_list, l_keyTable, l_valueTable, l_typeTable);
563
564
565 GetTokenValue(
566 l_source_table, l_source_column,
567 l_key_column1, l_key_column2, l_key_column3, l_key_column4, l_key_column5,
568 l_keyTable, l_valueTable, l_typeTable,
569 l_token_value, x_return_status, x_err_msg);
570
571 -- dbms_output.put_line('---- l_source_table '|| l_source_table);
572 -- dbms_output.put_line('--- l_source_column '|| l_source_column);
573 -- dbms_output.put_line('---- l_key_column1 '|| l_key_column1);
574 -- dbms_output.put_line('---- l_key_column2 '|| l_key_column2);
575 -- dbms_output.put_line('---- l_key_column3 '|| l_key_column3);
576 -- dbms_output.put_line('---- l_key_column4 '|| l_key_column4);
577 -- dbms_output.put_line('---- l_key_column5 '|| l_key_column5);
578 -- dbms_output.put_line('---- l_token_value '|| l_token_value);
579 -- dbms_output.put_line('--- x_return_status '|| x_return_status);
580 -- dbms_output.put_line('---- x_err_msg '|| x_err_msg);
581
582
583 x_token_value := l_token_value;
584
585
586 END FindTokenValue;
587
588
589
590
591
592
593
594 PROCEDURE Punchout(
595 p_application_id IN NUMBER,
596 p_org_id IN NUMBER,
597 p_carrier_id IN NUMBER,
598 p_tracking_event IN VARCHAR2,
599 p_granularity IN NUMBER,
600 p_key_list IN KeyTable,
601 p_value_list IN ValueTable,
602 p_type_list IN TypeTable,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_err_msg OUT NOCOPY VARCHAR2,
605 x_form_output OUT NOCOPY VARCHAR2) IS
606
607 -- Punchout takes in an application, organization, carrier, tracking event, granularity
608 -- and a list of key parameters from the user in order to create a form to punch out to a
609 -- remote carrier's tracking site. This differs from the other overloaded call in that the
610 -- key/value pairs are represented in two PL/SQL tables.
611
612 l_page_id NUMBER;
613 l_base_url VARCHAR2(200);
614 l_request_method VARCHAR2(10);
615 l_page_name VARCHAR2(30);
616 l_page_description VARCHAR2(200);
617 l_prefix VARCHAR2(10);
618 l_token VARCHAR2(30);
619
620 l_validity VARCHAR2(30);
621 l_param_name VARCHAR2(30);
622 l_param_value VARCHAR2(300);
623 l_return_status VARCHAR2(10);
624 l_err_msg VARCHAR2(50);
625 l_source_table VARCHAR2(30);
626 l_source_column VARCHAR2(30);
627 l_static_value VARCHAR2(100);
628 l_key_column1 VARCHAR2(50);
629 l_key_column2 VARCHAR2(50);
630 l_key_column3 VARCHAR2(50);
631 l_key_column4 VARCHAR2(50);
632 l_key_column5 VARCHAR2(50);
633
634 l_token_name VARCHAR2(50);
635 l_token_id NUMBER;
636
637
638 CURSOR c_params(p_page_id IN NUMBER) IS
639 SELECT p.param_name, p.static_value, t.token_id, t.token_name, t.source_table, t.source_column,
640 t.key_column1, t.key_column2, t.key_column3, t.key_column4, t.key_column5
641 FROM FTE_PTRK_PARAMS p, FTE_PTRK_SOURCES t
642 WHERE p.page_id = p_page_id
643 and p.token_id = t.token_id (+);
644
645 INVALID_INPUT EXCEPTION;
646 TOKEN_ERROR EXCEPTION;
647
648 br VARCHAR2(2) := '
649 ';
650 -- CHR(13)||CHR(10);
651
652
653 BEGIN
654
655 IF (InstallCheck() = 'N')
656 THEN
657 x_return_status := 'E';
658 x_err_msg := 'FTE Not Installed';
659 x_form_output := null;
660
661 RETURN;
662
663 END IF;
664
665 Validate(p_application_id, p_org_id, p_carrier_id, p_tracking_event, p_granularity,
666 l_validity, l_page_id, l_base_url, l_request_method, l_page_name, l_page_description, l_token);
667
668 IF l_validity <> 'VALID' THEN RAISE INVALID_INPUT; END IF;
669
670 IF (UPPER(SUBSTR(l_base_url,1,4)) <> 'HTTP') THEN l_prefix := 'http://'; ELSE l_prefix := ''; END IF;
671
672 x_form_output := '<FORM ACTION="'|| l_prefix || l_base_url || '" METHOD="' || l_request_method ||
673 --'" NAME="' || l_page_name || '" >' || br;
674 '" NAME="TheForm" >' || br;
675
676
677 OPEN c_params(l_page_id);
678 LOOP
679 FETCH c_params INTO l_param_name, l_static_value, l_token_id, l_token_name, l_source_table, l_source_column,
680 l_key_column1, l_key_column2, l_key_column3, l_key_column4, l_key_column5;
681
682
683 EXIT WHEN c_params%NOTFOUND;
684
685 IF l_token_id is not null THEN -- find token value
686
687 --dbms_output.put_line('$$ TOKEN FOUND: ' || l_param_name || ' ' || to_char(l_token_id) || ' ' || l_token_name);
688 GetTokenValue(l_source_table, l_source_column,
689 l_key_column1, l_key_column2, l_key_column3, l_key_column4, l_key_column5,
690 p_key_list, p_value_list, p_type_list, l_param_value, l_return_status, l_err_msg);
691
692 IF l_return_status = 'E' THEN RAISE TOKEN_ERROR; END IF;
693
694 IF (l_param_value is not NULL) THEN
695
696 x_form_output := x_form_output ||
697 ' <INPUT TYPE="HIDDEN" NAME="' || l_param_name ||
698 '" VALUE="' || l_param_value || '" >' || br;
699
700 -- ELSIF l_static_value is not null THEN -- take static value
701 ELSIF (l_static_value is not null) THEN
702 --dbms_output.put_line('$$ STATIC FOUND: ' || l_param_name || ' ' || l_static_value);
703 x_form_output := x_form_output ||
704 ' <INPUT TYPE="HIDDEN" NAME="' || l_param_name ||
705 '" VALUE="' || l_static_value || '" >' || br;
706
707 END IF ;
708 END IF;
709
710 END LOOP;
711
712 CLOSE c_params;
713
714 x_form_output := x_form_output || '</FORM>';
715 EXCEPTION
716 WHEN INVALID_INPUT THEN
717 x_return_status := 'INVALID_INPUT';
718 x_err_msg := 'INVALID INPUT ' || l_validity;
719 WHEN TOKEN_ERROR THEN
720 x_return_status := l_return_status;
721 x_err_msg := l_err_msg;
722
723 END Punchout;
724
725
726
727 END FTE_PTRACKING;