[Home] [Help]
PACKAGE BODY: APPS.PA_CAPITALIZED_INTEREST
Source
1 package body PA_CAPITALIZED_INTEREST as
2 /* $Header: PAXCINTB.pls 120.1 2005/08/09 04:16:24 avajain noship $ */
3
4 p_pa_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
5
6 PROCEDURE cint_compile_schedule(errbuf IN OUT NOCOPY varchar2,
7 retcode IN OUT NOCOPY varchar2,
8 p_sch_rev_id IN varchar2)
9 IS
10 CURSOR org_cursor(ver_id NUMBER, org_id NUMBER)
11 IS
12 SELECT organization_id_child, organization_id_parent
13 FROM per_org_structure_elements
14 CONNECT BY PRIOR organization_id_child = organization_id_parent
15 AND org_structure_version_id = ver_id
16 START WITH organization_id_parent = org_id
17 AND org_structure_version_id = ver_id;
18
19 status number;
20 stage number;
21
22 -- Standard who columns
23 l_last_updated_by NUMBER(15);
24 l_last_update_login NUMBER(15);
25 l_request_id NUMBER(15);
26 l_program_application_id NUMBER(15);
27 l_program_id NUMBER(15);
28
29 l_ind_rate_sch_id NUMBER;
30 l_ind_rate_sch_rev_id NUMBER;
31 l_org_struc_ver_id NUMBER;
32 l_start_org NUMBER;
33
34 l_debug_mode VARCHAR2(30);
35 l_module_name VARCHAR2(100) := 'cint_compile_schedule';
36 l_temp NUMBER;
37 completion_status BOOLEAN;
38 BEGIN
39
40 status := 0;
41
42 IF P_PA_DEBUG_MODE = 'Y' THEN
43 pa_debug.init_err_stack('PA_CAPITALIZED_INTEREST.cint_compile_schedule');
44 pa_debug.set_process('PLSQL','LOG', P_PA_DEBUG_MODE);
45 END IF;
46
47 l_ind_rate_sch_rev_id := p_sch_rev_id;
48 --
49 -- Get the standard who information
50 --
51 l_last_updated_by := FND_GLOBAL.USER_ID;
52 l_last_update_login := FND_GLOBAL.LOGIN_ID;
53 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
54 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
55 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
56
57 -- During the start of the compilation process, pa_ind_rate_sch_revisions.complied_flag
58 -- is set to an intermediate value 'I' (IN-PROCESS). Once the compilation process is
59 -- successfully completed, the flag is updated to 'Y'
60
61 --
62 -- Set the compilation time in the rate schedule revision
63 --
64
65 UPDATE pa_ind_rate_sch_revisions
66 SET
67 last_update_date = SYSDATE,
68 last_updated_by = l_last_updated_by,
69 last_update_login = l_last_update_login,
70 request_id = l_request_id,
71 program_application_id = l_program_application_id,
72 program_id = l_program_id,
73 program_update_date = SYSDATE,
74 compiled_flag = 'I'
75 WHERE
76 ind_rate_sch_revision_id = l_ind_rate_sch_rev_id;
77
78 COMMIT;
79
80 -- Whenever user recompiles the schedule by making changes for the rate structure,
81 -- the compilation process will blow off the de normalized records for the given
82 -- schedule revision from the pa_cint_rate_multiplers table and create new records.
83
84 delete pa_cint_rate_multipliers
85 where ind_rate_sch_revision_id = l_ind_rate_sch_rev_id;
86
87 -- When deleted the records from multipliers in the Burden Schedules form,
88 -- the pa_ind_cost_multipliers.ready_to_compile_flag is marked with X but not
89 -- actually deleted. So, deleting those marked with X during compilation process.
90
91 delete pa_ind_cost_multipliers
92 where ind_rate_sch_revision_id = l_ind_rate_sch_rev_id
93 and ready_to_compile_flag = 'X';
94
95 IF P_PA_DEBUG_MODE = 'Y' THEN
96 pa_debug.g_err_stage := 'Deleted all the existing compiled multipliers. No. of rows deleted: '||to_char(SQL%ROWCOUNT);
97 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
98 END IF;
99
100 IF P_PA_DEBUG_MODE = 'Y' THEN
101 pa_debug.g_err_stage := 'Set the compilation time and compiled_flag = I in the rate schedule revision';
102 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
103 END IF;
104
105 pa_cost_plus.get_hierarchy_from_revision(p_sch_rev_id => l_ind_rate_sch_rev_id,
106 x_org_struc_ver_id => l_org_struc_ver_id,
107 x_start_org => l_start_org,
108 x_status => status,
109 x_stage => stage);
110
111 IF P_PA_DEBUG_MODE = 'Y' THEN
112 pa_debug.g_err_stage := 'Getting org_struct_ver_id and start_org_id : '||l_org_struc_ver_id||' and '||l_start_org;
113 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
114 END IF;
115
116 IF status <> 0 THEN
117 stage := 50;
118 RETURN;
119 END IF;
120
121 select ind_rate_sch_id
122 into l_ind_rate_sch_id
123 from pa_ind_rate_sch_revisions
124 where ind_rate_sch_revision_id = l_ind_rate_sch_rev_id;
125
126 --
127 -- Compile rates for all organizations starting from the highest organization
128 --
129
130 --
131 -- First compile for the start organization
132 --
133
134 cint_compile_org_rates(p_rate_sch_rev_id => l_ind_rate_sch_rev_id,
135 p_ind_rate_sch_id => l_ind_rate_sch_id,
136 p_current_org_id => l_start_org,
137 p_org_id_parent => l_start_org,
138 p_org_struc_ver_id => l_org_struc_ver_id,
139 p_start_org => l_start_org,
140 status => status,
141 stage => stage);
142
143 IF P_PA_DEBUG_MODE = 'Y' THEN
144 pa_debug.g_err_stage := 'Compiled multipliers for the start organization';
145 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
146 END IF;
147
148 --
149 -- Compile all the organizations under the start organization
150 --
151
152 FOR org_row IN org_cursor(l_org_struc_ver_id, l_start_org) LOOP
153
154 cint_compile_org_rates(p_rate_sch_rev_id => l_ind_rate_sch_rev_id,
155 p_ind_rate_sch_id => l_ind_rate_sch_id,
156 p_current_org_id => org_row.organization_id_child,
157 p_org_id_parent => org_row.organization_id_parent,
158 p_org_struc_ver_id => l_org_struc_ver_id,
159 p_start_org => l_start_org,
160 status => status,
161 stage => stage);
162
163 IF status <> 0 THEN
164 IF P_PA_DEBUG_MODE = 'Y' THEN
165 pa_debug.g_err_stage := 'Error while compiling multipliers for the organization: '||org_row.organization_id_child;
166 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
167 END IF;
168 RETURN;
169 END IF;
170
171 END LOOP;
172
173 IF P_PA_DEBUG_MODE = 'Y' THEN
174 pa_debug.g_err_stage := 'Compiled multipliers for all the child orgnizations';
175 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
176 END IF;
177 --
178 -- Set the compilation time in the rate schedule revision
179 --
180
181 UPDATE pa_ind_rate_sch_revisions
182 SET
183 compiled_flag = 'Y',
184 compiled_date = SYSDATE
185 WHERE
186 ind_rate_sch_revision_id = l_ind_rate_sch_rev_id;
187
188 IF P_PA_DEBUG_MODE = 'Y' THEN
189 pa_debug.g_err_stage := 'Updated the compiled_flag to Y ';
190 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
191 END IF;
192
193 COMMIT;
194
195 IF P_PA_DEBUG_MODE = 'Y' THEN
196 pa_debug.reset_err_stack;
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 IF p_pa_debug_mode = 'Y' THEN
202 pa_debug.g_err_stage:= 'Unexpected Error '||SQLERRM(SQLCODE);
203 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
204 pa_debug.reset_curr_function;
205 END IF;
206
207 ROLLBACK;
208 UPDATE pa_ind_rate_sch_revisions
209 SET compiled_flag = 'N'
210 WHERE ind_rate_sch_revision_id = l_ind_rate_sch_rev_id;
211 COMMIT;
212 completion_status := fnd_concurrent.set_completion_status('ERROR', SQLERRM);
213
214 END cint_compile_schedule;
215
216 procedure cint_compile_org_rates (p_rate_sch_rev_id IN number,
217 p_ind_rate_sch_id IN NUMBER,
218 p_current_org_id IN number,
219 p_org_id_parent IN number,
220 p_org_struc_ver_id IN number,
221 p_start_org IN number,
222 status IN OUT NOCOPY number,
223 stage IN OUT NOCOPY number)
224 IS
225
226 -- Standard who
227 l_last_updated_by NUMBER(15);
228 l_created_by NUMBER(15);
229 l_last_update_login NUMBER(15);
230 l_request_id NUMBER(15);
231 l_program_application_id NUMBER(15);
232 l_program_id NUMBER(15);
233 l_module_name VARCHAR2(100) := 'cint_compile_org_rates';
234
235 BEGIN
236
237 status := 0;
238 stage := 100;
239
240 IF P_PA_DEBUG_MODE = 'Y' THEN
241 pa_debug.init_err_stack('PA_CAPITALIZED_INTEREST.cint_compile_org_rates');
242 pa_debug.set_process('PLSQL','LOG', P_PA_DEBUG_MODE);
243 END IF;
244
245 --
246 -- Get the standard who information
247 --
248 l_created_by := FND_GLOBAL.USER_ID;
249 l_last_updated_by := FND_GLOBAL.USER_ID;
250 l_last_update_login := FND_GLOBAL.LOGIN_ID;
251 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
252 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
253 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
254
255 INSERT INTO pa_cint_rate_multipliers
256 (IND_RATE_SCH_REVISION_ID,
257 ORGANIZATION_ID,
258 IND_RATE_SCH_ID,
259 RATE_NAME,
260 MULTIPLIER,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 CREATED_BY,
264 CREATION_DATE,
265 LAST_UPDATE_LOGIN,
266 REQUEST_ID,
267 PROGRAM_APPLICATION_ID,
268 PROGRAM_ID,
269 PROGRAM_UPDATE_DATE)
270 SELECT p_rate_sch_rev_id,
271 p_current_org_id,
272 p_ind_rate_sch_id,
273 cm.ind_cost_code,
274 cm.multiplier,
275 SYSDATE,
276 l_last_updated_by,
277 l_created_by,
278 SYSDATE,
279 l_last_update_login,
280 l_request_id,
284 from pa_ind_cost_multipliers cm
281 l_program_application_id,
282 l_program_id,
283 SYSDATE
285 where cm.ind_rate_sch_revision_id = p_rate_sch_rev_id
286 and cm.organization_id = p_current_org_id
287 UNION ALL
288 SELECT p_rate_sch_rev_id,
289 p_current_org_id,
290 p_ind_rate_sch_id,
291 icm.rate_name,
292 icm.multiplier,
293 SYSDATE,
294 l_last_updated_by,
295 l_created_by,
296 SYSDATE,
297 l_last_update_login,
298 l_request_id,
299 l_program_application_id,
300 l_program_id,
301 SYSDATE
302 from pa_cint_rate_multipliers icm
303 where icm.ind_rate_sch_revision_id = p_rate_sch_rev_id
304 and icm.organization_id = p_org_id_parent
305 and icm.rate_name not in (select cm1.ind_cost_code
306 from pa_ind_cost_multipliers cm1
307 where cm1.ind_rate_sch_revision_id = p_rate_sch_rev_id
308 and cm1.organization_id = p_current_org_id);
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF p_pa_debug_mode = 'Y' THEN
313 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM(SQLCODE);
314 pa_debug.write_file(l_module_name, pa_debug.g_err_stage, 3);
315 pa_debug.reset_curr_function;
316 END IF;
317 RAISE;
318
319 END cint_compile_org_rates;
320
321 END PA_CAPITALIZED_INTEREST;