1 PACKAGE BODY PSP_PSPENASG_XMLP_PKG AS
2 /* $Header: PSPENASGB.pls 120.4 2007/10/29 07:21:18 amakrish noship $ */
3
4 function cf_change_sourceformula(change_type in varchar2) return char is
5 CURSOR c_source IS
6 SELECT meaning
7 FROM fnd_lookups
8 WHERE lookup_type = 'PSP_ENC_CHANGE_SOURCE'
9 AND lookup_code = change_type;
10
11 v_source fnd_lookups.meaning%TYPE;
12
13 BEGIN
14
15 OPEN c_source;
16 FETCH c_source INTO v_source;
17 CLOSE c_source;
18
19 RETURN v_source;
20
21 EXCEPTION
22 WHEN NO_DATA_FOUND
23 THEN RETURN('No Data Found');
24
25 WHEN OTHERS
26 THEN RETURN('Other Error');
27
28 end;
29
30 function cf_org_nameformula(reference_id in number, assignment_id in number, action_type in varchar2, change_type in varchar2) return char is
31 CURSOR c_org IS
32 SELECT name
33 FROM hr_all_organization_units
34 WHERE organization_id = reference_id;
35
36 CURSOR c_org_acct IS
37 SELECT hrou.name
38 FROM hr_all_organization_units hrou,
39 Psp_organization_accounts poa
40 WHERE poa.organization_id = hrou.organization_id
41 AND poa.organization_account_id = reference_id;
42
43 CURSOR c_org_enc_date IS
44 SELECT hrou.name
45 FROM hr_all_organization_units hrou,
46 Psp_enc_end_dates peed
47 WHERE peed.organization_id = hrou.organization_id
48 AND peed.enc_end_date_id = reference_id;
49
50
51 CURSOR c_org_acct_del IS
52 SELECT hou.name
53 FROM hr_all_organization_units hou,
54 per_all_assignments_f paf
55 WHERE paf.assignment_id =assignment_id
56 AND paf.organization_id= hou.organization_id
57 AND sysdate between paf.effective_start_date and paf.effective_end_date;
58
59
60
61 CURSOR c_org_def_sch IS
62 SELECT hrou.name
63 FROM hr_all_organization_units hrou,
64 psp_default_labor_schedules pdls
65 WHERE pdls.organization_id = hrou.organization_id
66 AND pdls.org_schedule_id = reference_id;
67
68
69 CURSOR c_org_generic_del IS
70 SELECT hou.name
71 FROM hr_organization_units hou ,
72 psp_enc_changed_sch_history pecsh
73 WHERE hou.organization_id = pecsh.reference_id
74 AND pecsh.request_id = p_request_id
75 AND pecsh.change_type = 'GS'
76 AND pecsh.action_type ='G' ;
77
78 v_orgName hr_all_organization_units.name%TYPE;
79
80 BEGIN
81
82 IF action_type = 'I'
83 THEN
84
85 IF change_type IN ('DA', 'SA', 'DS', 'GS')
86 THEN
87 OPEN c_org;
88 FETCH c_org INTO v_orgName;
89 CLOSE c_org;
90 END IF;
91
92 ELSIF action_type ='U' THEN
93 IF change_type IN ('DA', 'SA','GS') THEN
94 OPEN c_org_acct;
95 FETCH c_org_acct INTO v_orgName;
96
97 IF c_org_acct%NOTFOUND THEN
98 IF change_type IN ( 'GS') THEN
99 OPEN c_org_generic_del;
100 FETCH c_org_generic_del INTO v_orgName;
101 CLOSE c_org_generic_del;
102 ELSE
103 OPEN c_org_acct_del;
104 FETCH c_org_acct_del INTO v_OrgName;
105 CLOSE c_org_acct_del;
106 END IF; END IF; CLOSE c_org_acct;
107 ELSIF change_type = 'OE'
108 THEN
109 OPEN c_org_enc_date;
110 FETCH c_org_enc_date INTO v_orgName;
111 CLOSE c_org_enc_date;
112
113
114 ELSIF change_type ='DS' THEN
115 OPEN c_org_def_sch;
116 FETCH c_org_def_sch INTO v_orgName;
117
118 IF c_org_def_sch%NOTFOUND THEN
119 OPEN c_org_acct_del;
120 FETCH c_org_acct_del INTO v_OrgName;
121 CLOSE c_org_acct_del;
122 END IF; CLOSE c_org_def_sch;
123 END IF; END IF;
124
125 RETURN(v_orgName);
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND
129 THEN RETURN('No Data Found');
130
131 WHEN OTHERS
132 THEN RETURN('Other Error');
133
134 END;
135
136 function cf_element_nameformula(reference_id in number, action_type in varchar2, change_type in varchar2) return char is
137
138 CURSOR c_element IS
139 SELECT element_name
140 FROM pay_element_types_f petf
141 WHERE element_type_id = reference_id
142 AND ( trunc(sysdate) BETWEEN effective_start_date AND effective_end_date
143 OR ( trunc(sysdate) < (select min(effective_start_date ) from pay_element_types_f petf1
144 where petf1.element_type_id= petf.element_type_id)));
145
146 CURSOR c_element_acct IS
147 SELECT pet.element_name
148 FROM pay_element_types_f pet,
149 Psp_element_type_accounts peta
150 WHERE pet.element_type_id = peta.element_type_id
151 AND Peta.element_account_id = reference_id
152 AND ( trunc(sysdate) BETWEEN pet.effective_start_date AND
153 pet.effective_end_date
154 OR ( trunc(sysdate) < (select min(effective_start_date ) from pay_element_types_f petf1
155 where petf1.element_type_id= pet.element_type_id)));
156
157
158 CURSOR c_element_acct_del IS
159 SELECT pet.element_name
160 FROM pay_element_types_f pet,
161 psp_enc_lines_history pelh
162 WHERE
163 pelh.element_account_id= reference_id
164 AND pelh.enc_element_type_id= pet.element_type_id
165 AND rownum=1 ;
166
167 v_elementName pay_element_types_f.element_name%TYPE;
168
169
170 BEGIN
171
172 IF action_type ='U' THEN
173 IF change_type IN ('ED')
174 THEN
175 OPEN c_element;
176 FETCH c_element INTO v_elementName;
177 CLOSE c_element;
178 ELSIF change_type IN ('GE')
179 THEN
180 OPEN c_element_acct;
181 FETCH c_element_acct INTO v_elementName;
182
183 IF c_element_acct%NOTFOUND THEN
184 OPEN c_element_acct_del;
185 FETCH c_element_acct_del INTO v_elementname;
186 CLOSE c_element_acct_del;
187 END IF; CLOSE c_element_acct;
188 END IF;
189
190 ELSIF action_type = 'I'
191 THEN
192 IF change_type IN ('GE')
193 THEN
194
195 OPEN c_element;
196 FETCH c_element INTO v_elementName;
197 CLOSE c_element;
198 END IF;
199 END IF;
200
201 RETURN(v_elementName);
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND
205 THEN RETURN('No Data Found');
206
207 WHEN OTHERS
208 THEN RETURN('Other Error');
209
210 END;
211
212 function BeforeReport return boolean is
213 begin
214
215
216 --hr_standard.event('BEFORE REPORT');
217 return (TRUE);
218 end;
219
220 function AfterReport return boolean is
221 begin
222 --hr_standard.event('AFTER REPORT');
223 return (TRUE);
224 end;
225
226 --Functions to refer Oracle report placeholders--
227
228 END PSP_PSPENASG_XMLP_PKG ;
[Home] [Help]