1 PACKAGE BODY OKC_QUERY AS
2 /*$Header: OKCQURYB.pls 120.2 2007/11/03 10:15:39 vgujarat ship $*/
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --===================
7 -- TYPES
8 --===================
9 -- add your type declarations here if any
10 --
11 --===================
12 -- PACKAGE CONSTANTS
13 --===================
14 --
15 x_msg_count NUMBER;
16 x_msg_data VARCHAR2(2000);
17 x_return_status VARCHAR2(1);
18
19 --===================
20 -- LOCAL PROCEDURES AND FUNCTIONS
21 --===================
22 --
23 --
24 -- ---------------------------------------------------------------------------------
25 -- FUNCTION: GetContractPartyName --
26 -- DESCRIPTION: --
27 -- using the record passed --
28 -- DEPENDENCIES: none --
29 -- CHANGE HISTORY: --
30 -- --
31 -- --
32 -- ---------------------------------------------------------------------------------
33 --
34 FUNCTION GetContractPartyName ( p_contract_id IN NUMBER,
35 p_class_code IN VARCHAR2)
36 RETURN VARCHAR2 IS
37
38 BEGIN
39 return 'ContractName TBD';
40
41 END GetContractPartyName;
42
43
44 --
45
46 --
47 -- ---------------------------------------------------------------------------------
48 -- FUNCTION: GetContractAmountDisplay --
49 -- DESCRIPTION: --
50 -- using the record passed --
51 -- DEPENDENCIES: none --
52 -- CHANGE HISTORY: --
53 -- --
54 -- --
55 -- -
56 --
57 FUNCTION GetContractDisplayAmount ( p_contract_id IN NUMBER,
58 p_class_code IN VARCHAR2)
59 RETURN VARCHAR2 IS
60
61 BEGIN
62 return '1.00 USD';
63
64 END GetContractDisplayAmount;
65
66
67 --
68 -- ---------------------------------------------------------------------------------
69 -- FUNCTION: SetSublineIndent --
70 -- DESCRIPTION: --
71 -- using the record passed --
72 -- DEPENDENCIES: none --
73 -- CHANGE HISTORY: --
74 -- --
75 -- --
76 -- ---------------------------------------------------------------------------------
77 --
78 -- Changed by msengupt on 02/09/2001 regarding deruving the new line name
79 -- Earlier version had dummy code as
80 -- return '-->' || p_line_descr;
81 --FUNCTION SetSublineIndent ( p_contract_id IN NUMBER,
82 -- p_line_id IN NUMBER,
83 -- p_line_descr IN VARCHAR2)
84 -- RETURN VARCHAR2 IS
85 -- IS
86 -- return '-->' || p_line_descr;
87 --END SetSublineIndent;
88
89 FUNCTION SetSublineIndent ( p_contract_id IN NUMBER,
90 p_line_id IN NUMBER,
91 p_line_descr IN VARCHAR2)
92 RETURN VARCHAR2 IS
93 l_object1_id1 VARCHAR2(40);
94 l_object1_id2 VARCHAR2(200);
95 l_object_code VARCHAR2(30);
96 --l_name VARCHAR2(150);
97 l_name VARCHAR2(255);
98 l_found BOOLEAN;
99
100 Cursor l_cimv_csr(p_cle_id NUMBER) Is
101 SELECT
102 cimv.OBJECT1_ID1,
103 cimv.OBJECT1_ID2,
104 cimv.JTOT_OBJECT1_CODE
105 FROM OKC_K_ITEMS cimv
106 WHERE cimv.CLE_ID = p_cle_id;
107 BEGIN
108 if p_line_descr is NOT NULL Then
109 return p_line_descr;
110 end if;
111 l_name := NULL;
112 open l_cimv_csr(p_line_id);
113 fetch l_cimv_csr into l_object1_id1, l_object1_id2, l_object_code;
114 l_found := l_cimv_csr%FOUND;
115 close l_cimv_csr;
116 If (l_found = TRUE) Then
117 If (l_object1_id1 is not null) Then
118 /* Short term solution - should be done at the Header level */
119 -- OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_chr_id => p_contract_id);
120 l_name := OKC_UTIL.get_name_from_jtfv(l_object_code, l_object1_id1, l_object1_id2);
121 End If;
122 End If;
123 return l_name;
124
125 EXCEPTION
126 when OTHERS then
127 l_name := NULL;
128 return l_name;
129
130 END SetSublineIndent;
131
132
133 --
134 -- ---------------------------------------------------------------------------------
135 -- FUNCTION: GetContractPartyName --
136 -- DESCRIPTION: --
137 -- using the record passed --
138 -- DEPENDENCIES: none --
139 -- CHANGE HISTORY: --
140 -- --
141 -- --
142 -- ---------------------------------------------------------------------------------
143 --
144 FUNCTION GetLinePartyName ( p_contract_id IN NUMBER,
145 p_line_id IN NUMBER)
146 RETURN VARCHAR2 IS
147
148 BEGIN
149 return 'LineName TBD';
150
151 END GetLinePartyName;
152
153
154 --
155 -- ---------------------------------------------------------------------------------
156 -- FUNCTION: GetParentLineNUMber --
157 -- DESCRIPTION: --
158 -- using the record passed --
159 -- DEPENDENCIES: none --
160 -- CHANGE HISTORY: --
161 -- --
162 -- --
163 -- ---------------------------------------------------------------------------------
164 --
165 --
166 FUNCTION GetParentLineNumber ( p_line_id IN NUMBER)
167 RETURN VARCHAR2 IS
168
169 BEGIN
170 return 'TBD';
171
172 END getParentLineNumber;
173
174 --
175 -- ---------------------------------------------------------------------------------
176 -- FUNCTION: GetChildCount --
177 -- DESCRIPTION: gets a count of the number of child groups in OKC_K_GRPINGS --
178 -- using the recordid passed --
179 -- DEPENDENCIES: none --
180 -- CHANGE HISTORY: --
181 -- --
182 -- --
183 -- ---------------------------------------------------------------------------------
184 --
185 FUNCTION GetChildCount (p_group_id IN NUMBER)
186 RETURN NUMBER IS
187 /*
188 CURSOR cg(p_id IN NUMBER) IS
189 select
190 count(*)
191 from okc_k_grpings
192 where cgp_parent_id = p_id
193 and included_chr_id is NULL;
194 */
195 CURSOR cg(p_id IN NUMBER) IS
196 select /*+ FIRST_ROWS */
197 1
198 from okc_k_grpings
199 where cgp_parent_id = p_id
200 and included_cgp_id is NOT NULL;
201 ret NUMBER;
202
203 BEGIN
204 ret := 0;
205 open cg(p_group_id);
206 fetch cg into ret;
207 close cg;
208 return ret;
209
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 return 0;
213 WHEN OTHERS THEN
214 raise_application_error(-20471,'Exception in GetChildCount for ' || to_char(p_group_id)
215 , TRUE);
216
217 END GetChildCount;
218
219 ---------------------------------------------------------------------
220 -- FUNCTION: GetKid
221 -- DESCRIPTION : Gets the contract number from the attribute value
222 -- from wf_notification_attributes for a notification_id
223 -- used in launchpad_inbox_view.
224 ----------------------------------------------------------------------
225
226 FUNCTION GetKid(p_notification_id IN NUMBER,
227 p_att_name IN VARCHAR2)
228 RETURN NUMBER
229 IS
230 /*nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
231 /* v_id okc_k_headers_b.id%TYPE; */
232 v_id okc_k_headers_all_b.id%TYPE;
233 -- end bug # 4666846
234 BEGIN
235
236 v_id := wf_notification.getattrnumber(p_notification_id,
237 p_att_name);
238 IF v_id IS NOT NULL THEN
239 RETURN(v_id);
240 ELSE
241 RETURN(NULL);
242 END IF;
243
244 EXCEPTION
245 when others then
246 RETURN(NULL);
247 END GetKid;
248
249
250 ---------------------------------------------------------------------
251 -- FUNCTION: GetKnumber
252 -- DESCRIPTION : Gets the contract number from the attribute value
253 -- from wf_notification_attributes for a notification_id
254 -- used in launchpad_inbox_view.
255 ----------------------------------------------------------------------
256 FUNCTION GetKnumber(p_notification_id IN NUMBER,
257 p_att_name IN VARCHAR2)
258
259 RETURN VARCHAR2 is
260 CURSOR k_cur(x in number)
261 IS
262 select k.contract_number
263 /*nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
264 /* from okc_k_headers_b k */
265 from okc_k_headers_all_b k
266 --end bug # 4666846
267 where k.id = x;
268 k_rec k_cur%ROWTYPE;
269 /*nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
270 /* v_id okc_k_headers_b.id%TYPE;
271 x_knumber okc_k_headers_b.contract_number%TYPE; */
272 v_id okc_k_headers_all_b.id%TYPE;
273 x_knumber okc_k_headers_all_b.contract_number%TYPE;
274 --end bug # 4666846
275 BEGIN
276
277 v_id := wf_notification.getattrnumber(p_notification_id,
278 p_att_name);
279 IF v_id IS NOT NULL THEN
280 OPEN k_cur(v_id);
281 FETCH k_cur INTO k_rec;
282 x_knumber := k_rec.contract_number;
283 CLOSE k_cur;
284 RETURN(x_knumber);
285 ELSE
286 x_knumber := 'Not Available';
287 RETURN(x_knumber);
288 END IF;
289
290 EXCEPTION
291 when others then
292 x_knumber := 'Not Available';
293 RETURN(x_knumber);
294 END GetKnumber;
295
296 --
297 --
298 --mmadhavi added GetKmodifier
299 ---------------------------------------------------------------------------
300 -- FUNCTION: GetKnumber
301 -- DESCRIPTION : Gets the contract number modifier from the attribute value
302 -- from wf_notification_attributes for a notification_id
303 -- used in launchpad_inbox_view.
304 ---------------------------------------------------------------------------
305 FUNCTION GetKmodifier(p_notification_id IN NUMBER,
306 p_att_name IN VARCHAR2)
307
308 RETURN VARCHAR2 is
309 CURSOR k_cur(x in number)
310 IS
311 select k.contract_number_modifier
312 /*nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
313 /* from okc_k_headers_b k */
314 from okc_k_headers_all_b k
315 -- end bug # 4666846
316 where k.id = x;
317 k_rec k_cur%ROWTYPE;
318 /*nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
319 /* v_id okc_k_headers_b.id%TYPE;
320 x_knumber okc_k_headers_b.contract_number_modifier%TYPE; */
321 v_id okc_k_headers_all_b.id%TYPE;
322 x_knumber okc_k_headers_all_b.contract_number_modifier%TYPE;
323 -- end bug # 4666846
324 BEGIN
325
326 v_id := wf_notification.getattrnumber(p_notification_id,
327 p_att_name);
328 IF v_id IS NOT NULL THEN
329 OPEN k_cur(v_id);
330 FETCH k_cur INTO k_rec;
331 x_knumber := k_rec.contract_number_modifier;
332 CLOSE k_cur;
333 RETURN(x_knumber);
334 ELSE
335 x_knumber := 'Not Available';
336 RETURN(x_knumber);
337 END IF;
338
339 EXCEPTION
340 when others then
341 x_knumber := 'Not Available';
342 RETURN(x_knumber);
343 END GetKmodifier;
344
345 --
346 --
347
348 -- ================================================================================== --
349 -- --
350 -- GetAuthorFormName
351 -- DESCRIPTION: gets the Authoring form name for the Class code passed --
352 -- --
353 -- ================================================================================== --
354 --
355 FUNCTION GetAuthorFormName ( p_class_code IN VARCHAR2) RETURN VARCHAR2 IS
356
357 CURSOR s (c_code IN VARCHAR2) IS
358 SELECT fct.function_name
359 FROM okc_classes_b cls,
360 fnd_form_functions fct
361 WHERE cls.code = c_code
362 AND cls.fff_function_id = fct.function_id;
363
364 fct_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE := null;
365
366 BEGIN
367 OPEN s (p_class_code);
368 FETCH s INTO fct_name;
369 CLOSE s;
370
371 return fct_name;
372
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 IF s%ISOPEN THEN
376 close s;
377 return 'none found';
378 ELSE
379 return 'none found';
380 END IF;
381
382 END GetAuthorFormName;
383
384
385
386 --
387 --
388 -- ==================================================================================== --
389 -- --
390 -- FUNCTION: GetContractVersion --
391 -- DESCRIPTION: returns the major/minor versions in a formated string for the --
392 -- the contract id passed --
393 -- --
394 -- ==================================================================================== --
395 --
396 FUNCTION GetContractVersion (p_contract_id IN NUMBER)
397 RETURN VARCHAR2 IS
398
399 CURSOR s (k_id IN NUMBER) IS
400 SELECT major_version
401 ,minor_version
402 FROM okc_k_vers_numbers
403 WHERE chr_id = k_id;
404
405 maj_ver NUMBER(4) := 0;
406 min_ver NUMBER := 0;
407
408 BEGIN
409 OPEN s(p_contract_id);
410 FETCH s into maj_ver, min_ver;
411 CLOSE s;
412
413 return ltrim(to_char(maj_ver,'9999')) || '.' || ltrim(to_char(min_ver));
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 IF s%ISOPEN THEN
418 close s;
419 return 'none';
420 ELSE
421 return 'none';
422 END IF;
423
424
425 END GetContractVersion;
426
427 FUNCTION GetGroupName (p_contract_id IN NUMBER)
428 RETURN VARCHAR2 IS
429
430 CURSOR cg(p_contract_id IN NUMBER) IS
431 select g.name
432 from okc_k_grpings, okc_k_groups_v g
433 where included_chr_id = p_contract_id
434 and cgp_parent_id = g.id and
435 ((g.public_YN = 'Y') or
436 (g.public_YN = 'N' and user_id = FND_GLOBAL.USER_ID));
437 gpname VARCHAR2(300);
438 cnt NUMBER := 0;
439
440 BEGIN
441 gpname := 0;
442 open cg(p_contract_id);
443 LOOP
444 fetch cg into gpname;
445 EXIT WHEN cg%NOTFOUND;
446 cnt := cnt + 1;
447 if cnt = 2 then
448 exit;
449 end if;
450 END LOOP;
451 close cg;
452 if cnt = 0 then
453 return NULL;
454 elsif cnt = 2 then
455 return '*';
456 else
457 return gpname;
458 end if;
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 return '?????';
463
464 END GetGroupName;
465
466 FUNCTION get_number(p_string IN VARCHAR2)
467 RETURN NUMBER IS
468 l_number NUMBER;
469 BEGIN
470 l_number := TO_NUMBER(p_string);
471 RETURN (l_number);
472 EXCEPTION
473 WHEN OTHERS THEN
474 RETURN 1;
475 END get_number;
476
477
478 -- Returns TRUE if role or role and party are in a given contract.
479 FUNCTION GET_K_ROLE_PARTY(p_contract_id IN NUMBER
480 ,p_party_role IN VARCHAR2
481 ,p_party_name IN VARCHAR2) RETURN BOOLEAN IS
482
483 -- Variables
484 l_party_name VARCHAR2(60);
485
486 -- Cursors
487 CURSOR pty_csr IS
488 SELECT kpr.rle_code, kpr.object1_id1, kpr.object1_id2, kpr.jtot_object1_code
489 FROM okc_k_party_roles_v kpr
490 WHERE dnz_chr_id = p_contract_id
491 AND rle_code = p_party_role;
492
493
494 BEGIN
495
496 FOR r_pty_csr IN pty_csr LOOP
497
498 IF p_party_name <> OKC_API.G_MISS_CHAR THEN
499
500 l_party_name := OKC_UTIL.GET_NAME_FROM_JTFV(p_object_code => r_pty_csr.jtot_object1_code
501 ,p_id1 => r_pty_csr.object1_id1
502 ,p_id2 => r_pty_csr.object1_id2 );
503
504 IF l_party_name = p_party_name THEN -- role and party exist in contract
505 RETURN(TRUE);
506 END IF;
507
508 ELSE -- role exists in contract
509 RETURN(TRUE);
510 END IF;
511
512 END LOOP;
513
514 RETURN(FALSE); -- role or role and party not found in contract
515
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 RETURN(FALSE);
519
520 END; -- FUNCTION GET_K_ROLE_PARTY
521
522
523 FUNCTION Get_Contract_Number(p_rtv_id IN NUMBER )
524 RETURN VARCHAR2 IS
525
526 /*
527 This function takes the resolved_timevalues ID as input and
528 returns the contract number.
529 */
530 /* nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
531 /* l_contract_number okc_k_headers_b.contract_number%TYPE;*/
532 l_contract_number okc_k_headers_all_b.contract_number%TYPE;
533 --end bug # 4666846
534
535 CURSOR csr_k_no IS
536 SELECT k.contract_number
537 FROM okc_resolved_timevalues_v rtv,
538 okc_timevalues tve,
539 /* nechatur 18-oct-2005 bug # 4666846 replacing okc_k_headers_b to okc_k_headers_all_b */
540 /* okc_k_headers_b k */
541 okc_k_headers_all_b k
542 -- end #4666846
543 WHERE rtv.tve_id = tve.id
544 AND tve.dnz_chr_id = k.id
545 AND rtv.id = p_rtv_id;
546
547
548 BEGIN
549
550 OPEN csr_k_no;
551 FETCH csr_k_no INTO l_contract_number;
552 CLOSE csr_k_no;
553
554 RETURN l_contract_number;
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 RETURN null;
559 END; -- end Get_Contract_Number
560
561 FUNCTION Get_source_doc_number(p_coe_id IN NUMBER ) RETURN VARCHAR2 IS
562 --
563 -- This function takes the condition occurrence id as input and
564 -- returns the the document source number (e.g. contract number).
565 -- Used by Events to set the document number when creating a task.
566 --
567
568 l_source_doc_number_yn VARCHAR2(200);
569 l_acn_id OKC_ACTIONS_V.ID%TYPE;
570 l_cnh_id OKC_CONDITION_HEADERS_V.ID%TYPE;
571 l_aae_id OKC_ACTION_ATTRIBUTES_V.ID%TYPE;
572 l_aae_value OKC_ACTION_ATT_VALS_V.VALUE%TYPE;
573
574 CURSOR c_cnh_id IS
575 SELECT cnh_id
576 FROM okc_condition_occurs
577 WHERE id = p_coe_id;
578
579 CURSOR c_acn_id IS
580 SELECT acn_id
581 FROM okc_condition_headers_b
582 where id = l_cnh_id;
583
584 CURSOR c_aae_id IS
585 SELECT id
586 FROM okc_action_attributes_b
587 WHERE acn_id = l_acn_id
588 AND source_doc_number_yn = 'Y';
589
590 CURSOR c_aae_value IS
591 SELECT value
592 FROM okc_action_att_vals
593 WHERE aae_id = l_aae_id
594 AND coe_id = p_coe_id;
595
596 BEGIN
597
598 -- get the condition header id
599 OPEN c_cnh_id;
600 FETCH c_cnh_id INTO l_cnh_id;
601 IF c_cnh_id%NOTFOUND THEN
602 return p_coe_id;
603 END IF;
604 CLOSE c_cnh_id;
605
606 -- get the action id used in the condition
607 OPEN c_acn_id;
608 FETCH c_acn_id INTO l_acn_id;
609 IF c_acn_id%NOTFOUND THEN
610 return p_coe_id;
611 END IF;
612 CLOSE c_acn_id;
613
614 -- get each attribute that is marked as a document source number
615 FOR r_aae_id IN c_aae_id LOOP
616
617 l_aae_id := r_aae_id.id;
618
619 -- get the value of the action attribute
620 FOR r_aae_value IN c_aae_value LOOP
621 IF r_aae_value.value IS NULL THEN
622 EXIT;
623 END IF;
624 IF l_aae_value IS NOT NULL THEN
625 l_aae_value := l_aae_value ||' '|| r_aae_value.value;
626 ELSE
627 l_aae_value := r_aae_value.value;
628 END IF;
629 END LOOP; -- c_aae_value
630 END LOOP; -- c_aae_id
631
632 IF l_aae_value IS NOT NULL THEN
633 return l_aae_value;
634 ELSE
635 return p_coe_id;
636 END IF;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 RETURN p_coe_id;
641 END; -- end Get_source_doc_number
642
643
644 --
645 --
646 -- ================================================================================== --
647 -- --
648 -- GetFormFunctionName
649 -- DESCRIPTION: gets the form function name for the Class code passed --
650 -- --
651 -- ================================================================================== --
652 --
653 FUNCTION GetFormFunctionName ( p_subclass_code IN VARCHAR2,
654 p_operation IN VARCHAR2)
655 RETURN VARCHAR2 IS
656
657 CURSOR s (c_subclass_code IN VARCHAR2,
658 c_operation IN VARCHAR2) IS
659 SELECT fct.function_name
660 FROM okc_subclasses_b scls,
661 okc_class_operations opr,
662 fnd_form_functions fct
663 WHERE scls.code = c_subclass_code
664 AND scls.cls_code = opr.cls_code
665 AND opr.opn_code = c_operation
666 AND opr.detail_function_id = fct.function_id;
667
668 fct_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE := null;
669
670 BEGIN
671 OPEN s (p_subclass_code,p_operation);
672 FETCH s INTO fct_name;
673 CLOSE s;
674
675 return fct_name;
676
677 EXCEPTION
678 WHEN NO_DATA_FOUND THEN
679 IF s%ISOPEN THEN
680 close s;
681 return NULL;
682 ELSE
683 return NULL;
684 END IF;
685
686 END GetFormFunctionName;
687
688
689 --
690 --
691 /*
692 PROCEDURE GetEbizLinkName ( x_function_name out nocopy VARCHAR2) IS
693 BEGIN
694 x_function_name := 'OKC_KPRINT_JSP';
695
696 END getEbizLinkName;
697 */
698
699
700 FUNCTION GetEbizLinkName
701 --( p_col_name IN VARCHAR2)
702 RETURN VARCHAR2 IS
703
704 BEGIN
705 return 'OKC_KPRINT_JSP';
706
707 END getEbizLinkName;
708
709 /* Added new function for Bug#2323327 */
710
711 FUNCTION GetClassFunctionName ( p_cls_code IN VARCHAR2,
712 p_operation IN VARCHAR2)
713 RETURN VARCHAR2 IS
714
715 CURSOR s (c_cls_code IN VARCHAR2,
716 c_operation IN VARCHAR2) IS
717 SELECT fct.function_name
718 FROM okc_class_operations opr,
719 fnd_form_functions fct
720 WHERE opr.cls_code = c_cls_code
721 AND opr.opn_code = c_operation
722 AND opr.detail_function_id = fct.function_id;
723
724 fct_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE := null;
725
726 BEGIN
727 OPEN s (p_cls_code,p_operation);
728 FETCH s INTO fct_name;
729 CLOSE s;
730
731 return fct_name;
732
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 IF s%ISOPEN THEN
736 close s;
737 return NULL;
738 ELSE
739 return NULL;
740 END IF;
741
742 END GetClassFunctionName;
743
744 --
745 -- function get_contact (moved here from OKC_RULE_PUB)
746 --
747 -- returns HZ_PARTIES related contacts points
748 -- otherwise (or if not found) returns contact description
749 -- through jtf_objects_vl
750 --
751 -- all parameters are regular jtf_objects related
752 --
753
754 function get_contact(
755 p_object_code in varchar2,
756 p_object_id1 in varchar2,
757 p_object_id2 in varchar2
758 )
759 return varchar2 is
760 L_MESSAGE varchar2(2000);
761 L_EMAIL_ADDRESS varchar2(100);
762 L_PHONE_COUNTRY_CODE varchar2(100);
763 L_PHONE_AREA_CODE varchar2(100);
764 L_PHONE_NUMBER varchar2(100);
765 L_PHONE_EXTENSION varchar2(100);
766 L_FAX_COUNTRY_CODE varchar2(100);
767 L_FAX_AREA_CODE varchar2(100);
768 L_FAX_NUMBER varchar2(100);
769 L_FAX_EXTENSION varchar2(100);
770 cursor l_primary_contact_phone_csr(pcode varchar2, pid number) is
771 select
772 EMAIL_ADDRESS,
773 PHONE_COUNTRY_CODE,
774 PHONE_AREA_CODE,
775 PHONE_NUMBER,
776 PHONE_EXTENSION
777 from okx_contact_points_v
778 where PRIMARY_FLAG = 'Y'
779 and owner_table_name = 'HZ_PARTIES'
780 and owner_table_id = pid
781 and pcode = 'OKX_PCONTACT'
782 ;
783 cursor l_primary_contact_fax_csr(pid number) is
784 select
785 PHONE_COUNTRY_CODE FAX_COUNTRY_CODE,
786 PHONE_AREA_CODE FAX_AREA_CODE,
787 PHONE_NUMBER FAX_NUMBER,
788 PHONE_EXTENSION FAX_EXTENSION
789 from okx_contact_points_v
790 where PHONE_LINE_TYPE = 'FAX'
791 and owner_table_name = 'HZ_PARTIES'
792 and owner_table_id = pid
793 and status = 'A'
794 ;
795 --
796 l_proc varchar2(72) := 'OKC_QUERY.get_contact';
797 --
798
799 begin
800
801
802
803
804 open l_primary_contact_phone_csr(p_object_code, p_object_id1);
805 fetch l_primary_contact_phone_csr into L_EMAIL_ADDRESS,L_PHONE_COUNTRY_CODE,L_PHONE_AREA_CODE,L_PHONE_NUMBER,L_PHONE_EXTENSION;
806 close l_primary_contact_phone_csr;
807 if (L_PHONE_NUMBER is null) then
808
809
810
811
812 return OKC_RULE_PUB.get_object_dsc(p_object_code, p_object_id1, p_object_id2);
813 end if;
814
815 open l_primary_contact_fax_csr(p_object_id1);
816 fetch l_primary_contact_fax_csr into L_FAX_COUNTRY_CODE,L_FAX_AREA_CODE,L_FAX_NUMBER,L_FAX_EXTENSION;
817 close l_primary_contact_fax_csr;
818
819 FND_MESSAGE.SET_NAME(application => 'OKC', name => 'OKC_GET_CONTACT');
820 FND_MESSAGE.SET_TOKEN(token => 'EMAIL_ADDRESS' , value => L_EMAIL_ADDRESS);
821 FND_MESSAGE.SET_TOKEN(token => 'PHONE_COUNTRY_CODE' , value => L_PHONE_COUNTRY_CODE);
822 FND_MESSAGE.SET_TOKEN(token => 'PHONE_AREA_CODE' , value => L_PHONE_AREA_CODE);
823 FND_MESSAGE.SET_TOKEN(token => 'PHONE_NUMBER' , value => L_PHONE_NUMBER);
824 FND_MESSAGE.SET_TOKEN(token => 'PHONE_EXTENSION' , value => L_PHONE_EXTENSION);
825 FND_MESSAGE.SET_TOKEN(token => 'FAX_COUNTRY_CODE' , value => L_FAX_COUNTRY_CODE);
826 FND_MESSAGE.SET_TOKEN(token => 'FAX_AREA_CODE' , value => L_FAX_AREA_CODE);
827 FND_MESSAGE.SET_TOKEN(token => 'FAX_NUMBER' , value => L_FAX_NUMBER);
828 FND_MESSAGE.SET_TOKEN(token => 'FAX_EXTENSION' , value => L_FAX_EXTENSION);
829
830
831
832
833 return FND_MESSAGE.get;
834 end;
835
836 -- Currentely it's hardcoded for Contracts for Sales
837 -- When ADDRESS is required for any ORG JTF SOURCE we will retrieve it
838 FUNCTION Get_Address(
839 p_object_code IN VARCHAR2,
840 p_object_id1 in varchar2,
841 p_object_id2 in varchar2,
842 ADR_TYPE IN VARCHAR2 -- ADDRESS TYPE : 'MAIN', 'BILL', 'SHIP'
843 ) RETURN VARCHAR2 IS
844 l_address VARCHAR2(2000);
845 l_sql_stmt VARCHAR2(3900);
846 l_not_found BOOLEAN;
847
848 Type SOURCE_CSR IS REF CURSOR;
849 c SOURCE_CSR;
850 BEGIN
851
852 -- START OF HARDCODED SECTION --
853 IF p_object_code = 'OKX_OPERUNIT' OR p_object_code = 'OKX_LEGAL_ENTITY' THEN
854 l_sql_stmt := 'select ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,LOC.ADDRESS_LINE_1,LOC.ADDRESS_LINE_2,LOC.ADDRESS_LINE_3,'
855 || 'NULL,LOC.TOWN_OR_CITY,NULL,NULL,LOC.REGION_1,LOC.POSTAL_CODE,NULL,LOC.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL, NULL,''N'',''N'',200,1,1) ADDRESS '
856 || ' from HR_LOCATIONS_ALL LOC, HR_ALL_ORGANIZATION_UNITS OU'
857 || ' WHERE OU.ORGANIZATION_ID=:id1 and LOC.LOCATION_ID(+) = OU.LOCATION_ID';
858 ELSIF p_object_code = 'OKX_PARTY' THEN
859 l_sql_stmt := 'select ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,'
860 ||'CITY,COUNTY,STATE,PROVINCE,POSTAL_CODE,NULL,COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL, NULL,''N'',''N'',200,1,1) ADDRESS from OKX_PARTIES_V WHERE ID1 = :id_1';
861 END IF;
862 -- END OF HARDCODED SECTION --
863
864 IF l_sql_stmt IS NOT NULL THEN
865 open c for l_sql_stmt using p_object_id1;
866 fetch c into l_address;
867 l_not_found := c%NOTFOUND;
868 close c;
869 END IF;
870
871 If (l_not_found OR l_sql_stmt IS NULL ) Then
872 l_address := 'it should be an '||ADR_TYPE||' address for '||p_object_code||' #'||p_object_id1;
873 return NULL;
874 End if;
875 return l_address;
876 EXCEPTION
877 when OTHERS then
878 If (c%ISOPEN) Then
879 Close c;
880 End If;
881 return 'Retrieving Address Error:'||sqlerrm||': '||l_sql_stmt;
882 END;
883
884 FUNCTION GET_EMAIL_FROM_JTFV(
885 p_object_code IN VARCHAR2,
886 p_object_id1 in varchar2,
887 p_object_id2 in varchar2
888 ) RETURN VARCHAR2 IS
889 l_name VARCHAR2(255);
890 l_from_table VARCHAR2(200);
891 l_where_clause VARCHAR2(2000);
892 l_sql_stmt VARCHAR2(500);
893 l_not_found BOOLEAN := TRUE;
894
895 Cursor jtfv_csr IS
896 SELECT FROM_TABLE, WHERE_CLAUSE
897 FROM JTF_OBJECTS_B
898 WHERE OBJECT_CODE = p_object_code;
899 Type SOURCE_CSR IS REF CURSOR;
900 c SOURCE_CSR;
901
902 BEGIN
903 open jtfv_csr;
904 fetch jtfv_csr into l_from_table, l_where_clause;
905 l_not_found := jtfv_csr%NOTFOUND;
906 close jtfv_csr;
907
908 If (l_not_found) Then
909 return NULL;
910 End if;
911
912 l_sql_stmt := 'SELECT EMAIL_ADDRESS FROM ' || l_from_table
913 || ' WHERE ID1 = :id_1 AND ID2 = :id2';
914 If (l_where_clause is not null) Then
915 l_sql_stmt := l_sql_stmt || ' AND ' || l_where_clause;
916 End If;
917 open c for l_sql_stmt using p_object_id1, p_object_id2;
918 fetch c into l_name;
919 l_not_found := c%NOTFOUND;
920 close c;
921
922 If (l_not_found) Then
923 return NULL;
924 End if;
925 return l_name;
926 EXCEPTION
927 when OTHERS then
928 If (jtfv_csr%ISOPEN) Then
929 Close jtfv_csr;
930 End If;
931 If (c%ISOPEN) Then
932 Close c;
933 End If;
934 return NULL;
935 END;
936 --
937 -- function get_contact
938 --
939 -- returns HZ_PARTIES related contacts points
940 -- otherwise (or if not found) returns contact description
941 -- through jtf_objects_vl
942 --
943 -- all parameters are regular jtf_objects related
944 --
945 function Get_Email_From_Cont_Points(
946 p_object_code in varchar2,
947 p_object_id1 in varchar2,
948 p_object_id2 in varchar2
949 ) return varchar2 is
950 -- OKC_UTIL
951 -- OKC_RULE_PUB
952 L_RETVAL varchar2(2000);
953 l_not_found BOOLEAN := TRUE;
954 cursor c(pid number) is
955 select EMAIL_ADDRESS
956 from okx_contact_points_v
957 where owner_table_name = 'HZ_PARTIES'
958 and owner_table_id = pid
959 and EMAIL_ADDRESS IS NOT NULL
960 order by decode(PRIMARY_FLAG,'Y',1,2)
961 ;
962 BEGIN
963 IF p_object_code = 'OKX_PCONTACT' THEN
964 open c( p_object_id1 );
965 fetch c into L_retval;
966 l_not_found := c%NOTFOUND;
967 close c;
968 END IF;
969 If (l_not_found) Then
970 return NULL;
971 End if;
972 return l_retval;
973 EXCEPTION
974 when OTHERS then
975 If (c%ISOPEN) Then
976 Close c;
977 End If;
978 return NULL;
979 END;
980
981 function Get_Phone(
982 p_object_code in varchar2,
983 p_object_id1 in varchar2,
984 p_object_id2 in varchar2
985 ) return varchar2 is
986 -- OKC_UTIL
987 -- OKC_RULE_PUB
988 L_RETVAL varchar2(2000);
989 l_not_found BOOLEAN := TRUE;
990 cursor c1(pid number) is
991 select decode(PNT.PHONE_COUNTRY_CODE,NULL,NULL,'+'||PNT.PHONE_COUNTRY_CODE||' ')
992 ||decode(PNT.PHONE_AREA_CODE,NULL,NULL,'('||PNT.PHONE_AREA_CODE||')')
993 ||PNT.PHONE_NUMBER||decode(PNT.PHONE_EXTENSION,NULL,NULL,'.'||PNT.PHONE_EXTENSION) PHONE
994 from okx_contact_points_v PNT
995 where owner_table_name = 'HZ_PARTIES'
996 and owner_table_id = pid
997 and PHONE_NUMBER IS NOT NULL
998 order by decode(PRIMARY_FLAG,'Y',1,2)
999 ;
1000 cursor c2(pid number) is
1001 select WORK_TELEPHONE PHONE
1002 from OKX_BUYERS_V
1003 where id1 = pid
1004 ;
1005 BEGIN
1006 IF p_object_code = 'OKX_PCONTACT' THEN
1007 open c1( p_object_id1 );
1008 fetch c1 into L_retval;
1009 l_not_found := c1%NOTFOUND;
1010 close c1;
1011 ELSIF p_object_code = 'OKX_BUYER' THEN
1012 open c2( p_object_id1 );
1013 fetch c2 into L_retval;
1014 l_not_found := c2%NOTFOUND;
1015 close c2;
1016 END IF;
1017 If (l_not_found) Then
1018 return NULL;
1019 End if;
1020 return l_retval;
1021 EXCEPTION
1022 when OTHERS then
1023 If (c1%ISOPEN) Then
1024 Close c1;
1025 End If;
1026 If (c2%ISOPEN) Then
1027 Close c2;
1028 End If;
1029 return NULL;
1030 END;
1031
1032 function get_contact_info(
1033 p_object_code in varchar2,
1034 p_object_id1 in varchar2,
1035 p_object_id2 in varchar2,
1036 p_info_type in varchar2 -- 'EMAIL', 'PHONE', 'FAX'
1037 ) return varchar2 is
1038 -- OKC_UTIL
1039 -- OKC_RULE_PUB
1040 L_RETVAL varchar2(2000);
1041 begin
1042
1043 IF p_info_type = 'EMAIL' THEN
1044 l_retval := GET_EMAIL_FROM_JTFV( p_object_code, p_object_id1, p_object_id2 );
1045 IF l_retval IS NULL THEN
1046 l_retval := Get_Email_From_Cont_Points( p_object_code, p_object_id1, p_object_id2 );
1047 END IF;
1048 ELSIF p_info_type = 'PHONE' THEN
1049 l_retval := Get_Phone( p_object_code, p_object_id1, p_object_id2 );
1050 -- ELSIF p_info_type = 'FAX' THEN
1051 -- l_retval := Get_Fax_From_Cont_Points( p_object_code, p_object_id1, p_object_id2 );
1052 END IF;
1053 return l_retval;
1054 end;
1055
1056 END OKC_QUERY;
[Home] [Help]