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