1 PACKAGE BODY HXT_HXT956E_XMLP_PKG AS
2 /* $Header: HXT956EB.pls 120.0 2007/12/03 11:12:13 amakrish noship $ */
3
4 function ppremformula(P_ELT_PRIOR_PREM_ID in number, P_EFFECTIVE_START_DATE in date, P_EFFECTIVE_END_DATE in date) return varchar2 is
5 PPREM VARCHAR2(80);
6 begin
7 PPREM := ' ';
8 DECLARE
9 CURSOR C IS
10 SELECT ELTT.ELEMENT_NAME
11 FROM PAY_ELEMENT_TYPES_F ELT1
12 ,PAY_ELEMENT_TYPES_F_TL ELTT
13 ,HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV1
14 WHERE ELTV1.ELEMENT_TYPE_ID = P_ELT_PRIOR_PREM_ID
15 AND ELTV1.EFFECTIVE_START_DATE <= P_EFFECTIVE_START_DATE
16 AND ELTV1.EFFECTIVE_END_DATE >= P_EFFECTIVE_END_DATE
17 AND ELTV1.ELEMENT_TYPE_ID = ELT1.ELEMENT_TYPE_ID
18 AND ELT1.EFFECTIVE_START_DATE <= P_EFFECTIVE_START_DATE
19 AND ELT1.EFFECTIVE_END_DATE >= P_EFFECTIVE_END_DATE
20 AND ELT1.element_type_id = eltt.element_type_id
21 AND ELTV1.element_type_id = elt1.element_type_id
22 and eltt.language = userenv('LANG')
23 and (eltv1.hxt_premium_type = 'FACTOR'
24 AND eltv1.hxt_earning_category = 'OVT'
25 or eltv1.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS'));
26 BEGIN
27 IF P_ELT_PRIOR_PREM_ID IS NOT NULL THEN
28 OPEN C;
29 FETCH C
30 INTO PPREM;
31 IF C%NOTFOUND THEN
32 RAISE NO_DATA_FOUND;
33 END IF;
34 CLOSE C;
35 END IF;
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 PPREM := ' ';
39 WHEN OTHERS THEN
40 /*SRW.MESSAGE(01,'ERROR '||PPREM);*/null;
41
42 END;
43 RETURN PPREM;
44 end;
45
46 function incexcformula(APPLY_PRIOR_PREM_YN in varchar2) return varchar2 is
47 INCEXC VARCHAR2(10);
48 begin
49 INCEXC := ' ';
50 IF APPLY_PRIOR_PREM_YN = 'Y' THEN
51 INCEXC := 'Include';
52 ELSE
53 IF APPLY_PRIOR_PREM_YN = 'N' THEN
54 INCEXC := 'Exclude';
55 ELSE
56 INCEXC := APPLY_PRIOR_PREM_YN;
57 END IF;
58 END IF;
59 RETURN INCEXC;
60 end;
61
62 function edateformula(EFFECTIVE_END_DATE in date) return date is
63 EDATE DATE;
64 begin
65 EDATE := NULL;
66 IF EFFECTIVE_END_DATE = TO_DATE('31/12/4712','DD/MM/YYYY') THEN
67 EDATE := NULL;
68 ELSE
69 EDATE := EFFECTIVE_END_DATE;
70 END IF;
71 RETURN EDATE;
72 end;
73
74 function earn_premformula(P_ELT_EARNED_PREM_ID in number, P_EFFECTIVE_START_DATE in date, P_EFFECTIVE_END_DATE in date) return varchar2 is
75 EARN_PREM VARCHAR2(80);
76 begin
77 EARN_PREM := ' ';
78 DECLARE
79 CURSOR C IS
80 SELECT ELTT.ELEMENT_NAME
81 FROM PAY_ELEMENT_TYPES_F ELT
82 ,PAY_ELEMENT_TYPES_F_TL ELTT
83 ,HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV
84 WHERE ELTV.ELEMENT_TYPE_ID = P_ELT_EARNED_PREM_ID
85 AND ELTV.EFFECTIVE_START_DATE <= P_EFFECTIVE_START_DATE
86 AND ELTV.EFFECTIVE_END_DATE >= P_EFFECTIVE_END_DATE
87 AND ELTV.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
88 AND ELT.EFFECTIVE_START_DATE <= P_EFFECTIVE_START_DATE
89 AND ELT.EFFECTIVE_END_DATE >= P_EFFECTIVE_END_DATE
90 AND ELT.element_type_id = eltt.element_type_id
91 AND ELTV.element_type_id = elt.element_type_id
92 and eltt.language = userenv('LANG')
93 and (eltv.hxt_premium_type = 'FACTOR'
94 AND eltv.hxt_earning_category = 'OVT'
95 or eltv.hxt_earning_category NOT IN ('REG', 'OVT', 'ABS'));
96 BEGIN
97 IF P_ELT_EARNED_PREM_ID IS NOT NULL THEN
98 OPEN C;
99 FETCH C
100 INTO EARN_PREM;
101 IF C%NOTFOUND THEN
102 RAISE NO_DATA_FOUND;
103 END IF;
104 CLOSE C;
105 END IF;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 EARN_PREM := ' ';
109 WHEN OTHERS THEN
110 /*SRW.MESSAGE(01,'ERROR '||EARN_PREM);*/null;
111
112 END;
113 RETURN EARN_PREM;
114 end;
115
116 function BeforePForm return boolean is
117 begin
118
119 return (TRUE);
120 end;
121
122 function AfterPForm return boolean is
123 begin
124
125 return (TRUE);
126 end;
127
128 function BeforeReport return boolean is
129 begin
130 /*SRW.USER_EXIT('FND SRWINIT');*/null;
131
132 return (TRUE);
133 end;
134
135 function BetweenPage return boolean is
136 begin
137
138 return (TRUE);
139 end;
140
141 function AfterReport return boolean is
142 begin
143 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
144
145 return (TRUE);
146 end;
147
148 --Functions to refer Oracle report placeholders--
149
150 END HXT_HXT956E_XMLP_PKG ;
[Home] [Help]