1 PACKAGE BODY ota_cert_xmlp_pkg AS
2 /* $Header: otacertxmlp.pkb 120.3 2011/04/07 13:33:06 shwnayak ship $ */
3 calendar_aware number:=2;
4
5 FUNCTION C_CERTIFICATION_NAME_p RETURN varchar2 IS
6 cursor c_certification is
7 select name
8 from ota_certifications_tl
9 where certification_id = P_CERTIFICATION_ID
10 and language = userenv('LANG') ;
11 BEGIN
12 open c_certification;
13 fetch c_certification into C_CERTIFICATION_NAME;
14 close c_certification;
15 return C_CERTIFICATION_NAME;
16 END C_CERTIFICATION_NAME_p;
17
18 FUNCTION C_SUBSCRIPTION_STATUS_p RETURN varchar2 IS
19 cursor c_lookup_code is
20 select es.meaning
21 from hr_lookups es
22 WHERE es.lookup_type='OTA_CERT_ENROLL_STATUS'
23 AND sysdate BETWEEN NVL(es.start_date_active,sysdate) AND NVL (es.end_date_active, sysdate)
24 AND es.enabled_flag ='Y'
25 AND es.lookup_code = P_SUBSCRIPTION_STATUS ;
26
27 BEGIN
28 open c_lookup_code;
29 fetch c_lookup_code into C_SUBSCRIPTION_STATUS;
30 close c_lookup_code;
31 return C_SUBSCRIPTION_STATUS;
32 END C_SUBSCRIPTION_STATUS_p;
33
34 Function C_SUBSCRIPTION_START_DATE_p return varchar2 is
35 Begin
36 select fnd_date.date_to_displaydate(to_date(substr((P_SUBSCRIPTION_START_DATE),1,10),'yyyy/mm/dd'),calendar_aware)
37 into C_SUBSCRIPTION_START_DATE
38 from dual;
39 return C_SUBSCRIPTION_START_DATE;
40 END;
41
42 Function C_SUBSCRIPTION_END_DATE_p return varchar2 is
43 Begin
44 select fnd_date.date_to_displaydate(to_date(substr((P_SUBSCRIPTION_END_DATE),1,10),'yyyy/mm/dd'),calendar_aware)
45 into C_SUBSCRIPTION_END_DATE
46 from dual;
47 return C_SUBSCRIPTION_END_DATE;
48 END;
49
50 Function C_EXPIRY_FROM_DATE_p return varchar2 is
51 Begin
52 select fnd_date.date_to_displaydate(to_date(substr((P_EXPIRY_FROM_DATE),1,10),'yyyy/mm/dd'))
53 into C_EXPIRY_FROM_DATE
54 from dual;
55 return C_EXPIRY_FROM_DATE;
56 END;
57
58 Function C_EXPIRY_TO_DATE_p return varchar2 is
59 Begin
60 select fnd_date.date_to_displaydate(to_date(substr((P_EXPIRY_TO_DATE),1,10),'yyyy/mm/dd'))
61 into C_EXPIRY_TO_DATE
62 from dual;
63 return C_EXPIRY_TO_DATE;
64 END;
65
66 END ota_cert_xmlp_pkg;
67
[Home] [Help]