[Home] [Help]
PACKAGE BODY: APPS.PSP_ARCHIVE_RETRIEVE
Source
1 PACKAGE BODY PSP_ARCHIVE_RETRIEVE as
2 /* $Header: PSPARRTB.pls 120.6.12020000.5 2012/07/04 09:05:19 amnaraya ship $ */
3
4 /****************************************************************************************
5 Created By : Ddubey/Lveerubh
6
7 Date Created By : 23-FEB-2001
8
9 Purpose : This procedure is to archive labor cost distribution history
10 for a given payroll name ,begin period and end period.
11
12 Know limitations, enhancements or remarks :
13
14 Change History :
15
16 ****************************************************************************************/
17 PROCEDURE archive_distribution (errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY VARCHAR2,
19 p_payroll_id IN NUMBER,
20 p_begin_period IN NUMBER,
21 p_end_period IN NUMBER,
22 p_business_group_id IN NUMBER,
23 p_set_of_books_id IN NUMBER) IS
24
25 --Cursor to check if the begin and end period for the selected payroll name are valid
26 CURSOR error_check_cur IS
27 SELECT distinct PPC.source_type,
28 PPC.time_period_id,
29 PTP.period_name
30 FROM PSP_PAYROLL_CONTROLS PPC,
31 PER_TIME_PERIODS PTP
32 WHERE PPC.time_period_id = PTP.time_period_id
33 and PPC.payroll_id = p_payroll_id
34 and PPC.time_period_id >= p_begin_period
35 and PPC.time_period_id <= p_end_period
36 and PPC.business_group_id = p_business_group_id
37 and PPC.set_of_books_id = p_set_of_books_id
38 and PPC.archive_flag is NULL
39 --For bug fix 1767315
40 and PPC.status_code <> 'P'
41 --and PPC.time_period_id in ( SELECT PPC2.time_period_id
42 -- FROM psp_payroll_controls PPC2
43 -- WHERE PPC2.time_period_id = PPC.time_period_id
44 -- and PPC2.status_code <> 'P'
45 -- )
46 --End of Bug fix
47 ORDER BY PPC.time_period_id;
48
49 -- the following cursor has been redefined to include time periodwise error message
50 --For bug fix 1769523:Archiving : EFFT RPT Pending for certification,Payroll Period Archived
51 --Cursor to check if there are any pending effort reports for the selected begin and end periods
52 -- and for the selected payroll
53 --CURSOR effort_pending_cur IS
54 --SELECT 1
55 --FROM DUAL
56 --WHERE EXISTS
57 -- (
58 -- SELECT DISTINCT PER.effort_report_id,
59 -- PER.version_num
60 -- FROM PSP_EFFORT_REPORTS PER,
61 -- PSP_EFFORT_REPORT_DETAILS PERD,
62 -- PER_ASSIGNMENTS_F PAF,
63 -- PER_TIME_PERIODS PTP1,
64 -- PER_TIME_PERIODS PTP2,
65 -- PSP_EFFORT_REPORT_PERIODS PERP,
66 -- PSP_EFFORT_REPORT_TEMPLATES PERT
67 -- WHERE PER.status_Code <> 'C'
68 -- and PER.effort_Report_id = PERD.effort_report_id
69 -- and PER.version_num = PERD.version_num
70 -- and PAF.assignment_id = PERD.assignment_id
71 -- and PAF.payroll_id = p_payroll_id
72 -- and PER.template_id = PERT.template_id
73 -- and PERT.effort_report_period_name = PERP.effort_report_period_name
74 -- and PTP1.time_period_id = p_begin_period
75 -- and PTP2.TIME_PERIOD_ID = p_end_period
76 -- and (
77 -- PERP.start_date_active between PTP1.start_date and PTP2.end_date
78 -- or PERP.end_date_active between PTP1.start_date and PTP2.end_date
79 -- or PTP1.start_date between PERP.start_date_active and PERP.end_date_active
80 -- or PTP2.end_date between PERP.start_date_active and PERP.end_date_active
81 -- )
82 -- and PER.business_group_id = p_business_group_id
83 -- and PER.set_of_books_id = p_set_of_books_id
84 -- );
85
86 CURSOR effort_pending_cur IS
87 SELECT period_name
88 FROM per_time_periods PTP
89 WHERE PTP.payroll_id = p_payroll_id
90 AND PTP.time_period_id >= p_begin_period
91 AND PTP.time_period_id <= p_end_period
92 AND EXISTS (SELECT 1
93 FROM PSP_EFFORT_REPORTS PER,
94 PSP_EFFORT_REPORT_DETAILS PERD,
95 PER_ASSIGNMENTS_F PAF,
96 PSP_EFFORT_REPORT_PERIODS PERP,
97 PSP_EFFORT_REPORT_TEMPLATES PERT
98 WHERE PER.status_code <> 'C'
99 and PER.effort_Report_id = PERD.effort_report_id
100 and PER.version_num = PERD.version_num
101 and PAF.assignment_id = PERD.assignment_id
102 and PAF.payroll_id = PTP.payroll_id
103 and PER.template_id = PERT.template_id
104 and PERT.effort_report_period_name = PERP.effort_report_period_name
105 and ( PERP.start_date_active BETWEEN PTP.start_date and PTP.end_date
106 OR PERP.end_date_active BETWEEN PTP.start_date and PTP.end_date
107 OR PTP.start_date BETWEEN PERP.start_date_active and PERP.end_date_active
108 OR PTP.end_date BETWEEN PERP.start_date_active and PERP.end_date_active)
109 and PER.business_group_id = p_business_group_id
110 and PER.set_of_books_id = p_set_of_books_id);
111
112 -- Cursor to select valid periods that can be archived
113 -- Included 'distinct' in the select for bug fix 1759548
114 CURSOR valid_period_cur IS
115 SELECT distinct PPC.time_period_id,
116 PTP.period_name
117 FROM PSP_PAYROLL_CONTROLS PPC,
118 PER_TIME_PERIODS PTP
119 WHERE PPC.payroll_id = p_payroll_id
120 and PPC.time_period_id >= p_begin_period
121 and PPC.time_period_id <= p_end_period
122 and PPC.archive_flag is NULL
123 and PPC.business_group_id = p_business_group_id
124 and PPC. set_of_books_id = p_set_of_books_id
125 and PPC.time_period_id = PTP.time_period_id
126 ORDER BY PPC.time_period_id;
127
128 -- Cursors to get payroll name, begin period name, end period name for displaying in the messages
129 CURSOR payroll_name_cur IS
130 SELECT distinct PPF.payroll_name
131 FROM PAY_PAYROLLS_F PPF
132 WHERE PPF.payroll_id = p_payroll_id
133 and PPF.business_group_id = p_business_group_id
134 and PPF.gl_set_of_books_id = p_set_of_books_id;
135
136 CURSOR begin_period_name_cur IS
137 SELECT distinct PTP.period_name
138 FROM PER_TIME_PERIODS PTP
139 WHERE PTP.time_period_id = p_begin_period;
140
141 CURSOR end_period_name_cur IS
142 SELECT distinct PTP.period_name
143 FROM PER_TIME_PERIODS PTP
144 WHERE PTP.time_period_id = p_end_period;
145
146 l_error_api_name VARCHAR2(2000);
147 l_status VARCHAR2(15);
148 l_error_period_count NUMBER;
149 l_time_period NUMBER(15);
150 l_period_name VARCHAR2(70);
151 l_begin_period_name VARCHAR2(70);
152 l_end_period_name VARCHAR2(70);
153 l_process_type VARCHAR2(15) := 'Archive';
154 l_lines_type VARCHAR2(30) := 'Distribution Lines';
155 l_payroll_name VARCHAR2(80);
156 l_effort_count NUMBER(5);
157 payroll_name_rec payroll_name_cur%ROWTYPE;
158 begin_period_name_rec begin_period_name_cur%ROWTYPE;
159 end_period_name_rec end_period_name_cur%ROWTYPE;
160 error_check_rec error_check_cur%ROWTYPE;
161 valid_period_rec valid_period_cur%ROWTYPE;
162 effort_pending_rec effort_pending_cur%ROWTYPE;
163
164 BEGIN
165 fnd_msg_pub.initialize;
166 OPEN payroll_name_cur;
167 FETCH payroll_name_cur INTO payroll_name_rec;
168 l_payroll_name := payroll_name_rec.payroll_name;
169 CLOSE payroll_name_cur;
170
171 OPEN begin_period_name_cur;
172 FETCH begin_period_name_cur INTO begin_period_name_rec;
173 l_begin_period_name := begin_period_name_rec.period_name;
174 CLOSE begin_period_name_cur;
175
176 OPEN end_period_name_cur;
177 FETCH end_period_name_cur INTO end_period_name_rec;
178 l_end_period_name := end_period_name_rec.period_name;
179 CLOSE end_period_name_cur;
180
181 --For bug fixing 1769523
182 --Checking if any Effort Reports are pending for Certification ,in which Archiving cannot be done
183 OPEN effort_pending_cur;
184 FETCH effort_pending_cur INTO effort_pending_rec;
185 l_effort_count := effort_pending_cur%ROWCOUNT;
186 CLOSE effort_pending_cur;
187 --End of Bug Fix
188 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_START');
189 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
190 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
191 FND_MESSAGE.SET_TOKEN('BEGIN_PERIOD', l_begin_period_name);
192 FND_MESSAGE.SET_TOKEN('END_PERIOD', l_end_period_name);
193 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
194 fnd_msg_pub.add;
195
196 -- Checking for periods which have unprocessed lines
197 OPEN error_check_cur;
198 FETCH error_check_cur INTO error_check_rec;
199 l_error_period_count := error_check_cur%ROWCOUNT;
200 CLOSE error_check_cur;
201
202 IF (l_error_period_count > 0) THEN
203 OPEN error_check_cur;
204 LOOP
205 FETCH error_check_cur INTO error_check_rec;
206 EXIT WHEN error_check_cur%NOTFOUND ;
207 l_time_period := error_check_rec.time_period_id;
208 l_period_name := error_check_rec.period_name;
209 IF (error_check_rec.source_type = 'O') THEN
210 FND_MESSAGE.SET_NAME('PSP','PSP_ARC_LDO_CANNOT_ARCHIVE');
211 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
212 FND_MESSAGE.SET_TOKEN('TIME_PERIOD',l_period_name);
213 fnd_msg_pub.add;
214
215 ELSIF (error_check_rec.source_type = 'N') THEN
216 FND_MESSAGE.SET_NAME('PSP','PSP_ARC_LDN_CANNOT_ARCHIVE');
217 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
218 FND_MESSAGE.SET_TOKEN('TIME_PERIOD',l_period_name);
219 fnd_msg_pub.add;
220
221 ELSIF (error_check_rec.source_type = 'P') THEN
222 FND_MESSAGE.SET_NAME('PSP','PSP_ARC_LDP_CANNOT_ARCHIVE');
223 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
224 FND_MESSAGE.SET_TOKEN('TIME_PERIOD',l_period_name);
225 fnd_msg_pub.add;
226
227 ELSIF (error_check_rec.source_type = 'A') THEN
228 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_LDA_CANNOT_ARCHIVE');
229 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
230 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
231 fnd_msg_pub.add;
232 END IF;
233 END LOOP;
234 CLOSE error_check_cur;
235
236 --For bug fixing 1769523
237 --Checking if any Effort Reports are pending for Certification ,in which Archiving cannot be done
238 IF (l_effort_count > 0) THEN
239 -- Included the following cursor for bug fix 1818874
240 OPEN effort_pending_cur;
241 LOOP
242 FETCH effort_pending_cur INTO effort_pending_rec;
243 EXIT WHEN effort_pending_cur%NOTFOUND ;
244
245 l_period_name := effort_pending_rec.period_name;
246 FND_MESSAGE.SET_NAME('PSP', 'PSP_EFFORT_ARCH_PENDING');
247 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
248 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
249 fnd_msg_pub.add;
250 END LOOP;
251 CLOSE effort_pending_cur;
252 END IF;
253 --End of Bug Fix
254
255 l_status := 'unsuccessful';
256
257 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
258 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
259 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
260 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
261 fnd_msg_pub.add;
262 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264
265 --For bug fixing 1769523
266 --Checking if any Effort Reports are pending for Certification ,in which Archiving cannot be done
267 IF (l_effort_count > 0) THEN
268 -- Included the following cursor for bug fix 1818874
269 l_status := 'unsuccessful';
270 OPEN effort_pending_cur;
271 LOOP
272 FETCH effort_pending_cur INTO effort_pending_rec;
273 EXIT WHEN effort_pending_cur%NOTFOUND ;
274
275 l_period_name := effort_pending_rec.period_name;
276 FND_MESSAGE.SET_NAME('PSP', 'PSP_EFFORT_ARCH_PENDING');
277 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
278 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
279 fnd_msg_pub.add;
280 END LOOP;
281 CLOSE effort_pending_cur;
282
283 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
284 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
285 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
286 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
287 fnd_msg_pub.add;
288 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290 --End of Bug Fix
291
292 -- Archiving data into the new archival tables and purging the data from the history tables
293 -- Standard WHO columns and Concurrent WHO columns
294
295
296 OPEN valid_period_cur;
297 LOOP
298 FETCH valid_period_cur INTO valid_period_rec;
299 EXIT WHEN valid_period_cur%NOTFOUND ;
300 l_period_name := valid_period_rec.period_name;
301
302 -- Insert data into archive table PSP_DISTRIBUTION_LINES_ARCH from PSP_DISTRIBUTION_LINES_HISTORY for the valid_period
303 INSERT INTO PSP_DISTRIBUTION_LINES_ARCH
304 (
305 distribution_line_id, distribution_date,
306 effective_date, distribution_amount,
307 status_code, default_reason_code,
308 suspense_reason_code, include_in_er_flag,
309 effort_report_id, version_num,
310 schedule_line_id, summary_line_id,
311 default_org_account_id, suspense_org_account_id,
312 element_account_id, org_schedule_id,
313 gl_project_flag, reversal_entry_flag,
314 user_defined_field, adjustment_batch_name,
315 set_of_books_id, payroll_sub_line_id,
316 auto_expenditure_type, auto_gl_code_combination_id,
317 business_group_id, attribute_category, -- Introduced DFF columns for bug fix 2908859
318 attribute1, attribute2, --- 4304623:nih salary cap
319 attribute3, attribute4,
320 attribute5, attribute6,
321 attribute7, attribute8,
322 attribute9, attribute10,
323 cap_excess_glccid, cap_excess_award_id,
324 cap_excess_task_id, cap_excess_project_id,
325 cap_excess_exp_type, cap_excess_exp_org_id,
326 funding_source_code, annual_salary_cap,
327 cap_excess_dist_line_id, suspense_auto_glccid,
328 suspense_auto_exp_type, adj_account_flag) -- added for 5080403
329 SELECT
330 PDLH.distribution_line_id, PDLH.distribution_date,
331 PDLH.effective_date, PDLH.distribution_amount,
332 PDLH.status_code, PDLH.default_reason_code,
333 PDLH.suspense_reason_code, PDLH.include_in_er_flag,
334 PDLH.effort_report_id, PDLH.version_num,
335 PDLH.schedule_line_id, PDLH.summary_line_id,
336 PDLH.default_org_account_id, PDLH.suspense_org_account_id,
337 PDLH.element_account_id, PDLH.org_schedule_id,
338 PDLH.gl_project_flag, PDLH.reversal_entry_flag,
339 PDLH.user_defined_field, PDLH.adjustment_batch_name,
340 PDLH.set_of_books_id, PDLH.payroll_sub_line_id,
341 PDLH.auto_expenditure_type, PDLH.auto_gl_code_combination_id,
342 PDLH.business_group_id, pdlh.attribute_category, -- Introduced DFF columns for bug fix 2908859
343 pdlh.attribute1, pdlh.attribute2,
344 pdlh.attribute3, pdlh.attribute4,
345 pdlh.attribute5, pdlh.attribute6,
346 pdlh.attribute7, pdlh.attribute8,
347 pdlh.attribute9, pdlh.attribute10,
348 pdlh.cap_excess_glccid, pdlh.cap_excess_award_id,
349 pdlh.cap_excess_task_id, pdlh.cap_excess_project_id,
350 pdlh.cap_excess_exp_type, pdlh.cap_excess_exp_org_id,
351 pdlh.funding_source_code, pdlh.annual_salary_cap,
352 pdlh.cap_excess_dist_line_id, pdlh.suspense_auto_glccid,
353 pdlh.suspense_auto_exp_type, pdlh.adj_account_flag -- added for 5080403
354 FROM PSP_DISTRIBUTION_LINES_HISTORY PDLH,
355 PSP_SUMMARY_LINES PSL
356 WHERE PDLH.summary_line_id = PSL.summary_line_id
357 and PSL.time_period_id = valid_period_rec.time_period_id ;
358
359 -- Insert data into archive table PSP_ADJUSTMENT_LINES_ARCH from PSP_ADJUSTMENT_LINES_HISTORY for the valid_period
360 INSERT INTO PSP_ADJUSTMENT_LINES_ARCH
361 (
362 adjustment_line_id, person_id,
363 assignment_id, element_type_id,
364 distribution_date, effective_date,
365 distribution_amount, dr_cr_flag,
366 payroll_control_id, source_code,
367 time_period_id, batch_name,
368 status_code, set_of_books_id,
369 gl_code_combination_id, project_id,
370 expenditure_organization_id, expenditure_type,
371 task_id, award_id,
372 suspense_org_account_id, suspense_reason_code,
373 include_in_er_flag, effort_report_id,
374 version_num, summary_line_id,
375 reversal_entry_flag, original_line_flag,
376 user_defined_field, adjustment_batch_name,
377 percent, orig_source_type,
378 orig_line_id, attribute_category,
379 attribute1, attribute2,
380 attribute3, attribute4,
381 attribute5, attribute6,
382 attribute7, attribute8,
383 attribute9, attribute10,
384 attribute11, attribute12,
385 attribute13, attribute14,
386 attribute15, last_update_date,
387 last_updated_by, last_update_login,
388 created_by, creation_date,
389 source_type, business_group_id,
390 adj_set_number, line_number)
391 SELECT
392 PALH.adjustment_line_id, PALH.person_id,
393 PALH.assignment_id, PALH.element_type_id,
394 PALH.distribution_date, PALH.effective_date,
395 PALH.distribution_amount, PALH.dr_cr_flag,
396 PALH.payroll_control_id, PALH.source_code,
397 PALH.time_period_id, PALH.batch_name,
398 PALH.status_code, PALH.set_of_books_id,
399 PALH.gl_code_combination_id, PALH.project_id,
400 PALH.expenditure_organization_id, PALH.expenditure_type,
401 PALH.task_id, PALH.award_id,
402 PALH.suspense_org_account_id, PALH.suspense_reason_code,
403 PALH.include_in_er_flag, PALH.effort_report_id,
404 PALH.version_num, PALH.summary_line_id,
405 PALH.reversal_entry_flag, PALH.original_line_flag,
406 PALH.user_defined_field, PALH.adjustment_batch_name,
407 PALH.percent, PALH.orig_source_type,
408 PALH.orig_line_id, PALH.attribute_category,
409 PALH.attribute1, PALH.attribute2,
410 PALH.attribute3, PALH.attribute4,
411 PALH.attribute5, PALH.attribute6,
412 PALH.attribute7, PALH.attribute8,
413 PALH.attribute9, PALH.attribute10,
414 PALH.attribute11, PALH.attribute12,
415 PALH.attribute13, PALH.attribute14,
416 PALH.attribute15, PALH.last_update_date,
417 PALH.last_updated_by, PALH.last_update_login,
418 PALH.created_by, PALH.creation_date,
419 PALH.source_type, PALH.business_group_id,
420 PALH.adj_set_number, PALH.line_number
421 FROM PSP_ADJUSTMENT_LINES_HISTORY PALH
422 WHERE PALH.time_period_id = valid_period_rec.time_period_id ;
423
424 -- Insert data into archive table PSP_PRE_GEN_DIST_LINES_ARCH from PSP_PRE_GEN_LINES_HISTORY for the valid_period.
425 INSERT INTO PSP_PRE_GEN_DIST_LINES_ARCH
426 (
427 pre_gen_dist_line_id, distribution_interface_id,
428 person_id, assignment_id,
429 element_type_id, distribution_date,
430 effective_date, distribution_amount,
431 dr_cr_flag, payroll_control_id,
432 source_type, source_code,
433 time_period_id, batch_name,
434 status_code, set_of_books_id,
435 gl_code_combination_id, project_id,
436 expenditure_organization_id, expenditure_type,
437 task_id, award_id,
438 suspense_org_account_id, suspense_reason_code,
439 include_in_er_flag, effort_report_id,
440 version_num, summary_line_id,
441 reversal_entry_flag, user_defined_field,
442 adjustment_batch_name, business_group_id,
443 attribute_category, -- Introduced DFF columns for bug fix 2908859
444 attribute1, attribute2,
445 attribute3, attribute4,
446 attribute5, attribute6,
447 attribute7, attribute8,
448 attribute9, attribute10,
449 suspense_auto_glccid, suspense_auto_exp_type)
450 SELECT
451 PGDLH.pre_gen_dist_line_id, PGDLH.distribution_interface_id,
452 PGDLH.person_id, PGDLH.assignment_id,
453 PGDLH.element_type_id, PGDLH.distribution_date,
454 PGDLH.effective_date, PGDLH.distribution_amount,
455 PGDLH.dr_cr_flag, PGDLH.payroll_control_id,
456 PGDLH.source_type, PGDLH.source_code,
457 PGDLH.time_period_id, PGDLH.batch_name,
458 PGDLH.status_code, PGDLH.set_of_books_id,
459 PGDLH.gl_code_combination_id, PGDLH.project_id,
460 PGDLH.expenditure_organization_id, PGDLH.expenditure_type,
461 PGDLH.task_id, PGDLH.award_id,
462 PGDLH.suspense_org_account_id, PGDLH.suspense_reason_code,
463 PGDLH.include_in_er_flag, PGDLH.effort_report_id,
464 PGDLH.version_num, PGDLH.summary_line_id,
465 PGDLH.reversal_entry_flag, PGDLH.user_defined_field,
466 PGDLH.adjustment_batch_name, PGDLH.business_group_id,
467 pgdlh.attribute_category, -- Introduced DFF columns for bug fix 2908859
468 pgdlh.attribute1, pgdlh.attribute2,
469 pgdlh.attribute3, pgdlh.attribute4,
470 pgdlh.attribute5, pgdlh.attribute6,
471 pgdlh.attribute7, pgdlh.attribute8,
472 pgdlh.attribute9, pgdlh.attribute10,
473 pgdlh.suspense_auto_glccid, pgdlh.suspense_auto_exp_type
474 FROM PSP_PRE_GEN_DIST_LINES_HISTORY PGDLH
475 WHERE PGDLH.time_period_id = valid_period_rec.time_period_id ;
476
477 -- Insert data into archive table PSP_SUMMARY_LINES_ARCH from PSP_SUMMARY_LINES for the valid_period
478 INSERT INTO PSP_SUMMARY_LINES_ARCH
479 (
480 summary_line_id, source_type,
481 source_code, time_period_id,
482 interface_batch_name, person_id,
483 assignment_id, effective_date,
484 accounting_date, exchange_rate_type,
485 payroll_control_id, gl_code_combination_id,
486 project_id, expenditure_organization_id,
487 expenditure_type, task_id,
488 award_id, summary_amount,
489 dr_cr_flag, group_id,
490 interface_status, attribute_category,
491 attribute1, attribute2,
492 attribute3, attribute4,
493 attribute5, attribute6,
494 attribute7, attribute8,
495 attribute9, attribute10,
496 attribute11, attribute12,
497 attribute13, attribute14,
498 attribute15, attribute16,
499 attribute17, attribute18,
500 attribute19, attribute20,
501 attribute21, attribute22,
502 attribute23, attribute24,
503 attribute25, attribute26,
504 attribute27, attribute28,
505 attribute29, attribute30,
506 last_update_date, last_updated_by,
507 last_update_login, created_by,
508 creation_date, set_of_books_id,
509 business_group_id, status_code,
510 gms_batch_name, gms_posting_effective_date,/* added posting eff dt. for Zero work Days */
511 expenditure_id, expenditure_item_id, -- added five exp columns for 2445196
512 expenditure_ending_date, interface_id,
513 txn_interface_id, actual_summary_amount -- For Bug 2496661 : Added new column actual_summary_amount
514 )
515 SELECT
516 PSL.summary_line_id, PSL.source_type,
517 PSL.source_code, PSL.time_period_id,
518 PSL.interface_batch_name, PSL.person_id,
519 PSL.assignment_id, PSL.effective_date,
520 PSL.accounting_date, PSL.exchange_rate_type,
521 PSL.payroll_control_id, PSL.gl_code_combination_id,
522 PSL.project_id, PSL.expenditure_organization_id,
523 PSL.expenditure_type, PSL.task_id,
524 PSL.award_id, PSL.summary_amount,
525 PSL.dr_cr_flag, PSL.group_id,
526 PSL.interface_status, PSL.attribute_category,
527 PSL.attribute1, PSL.attribute2,
528 PSL.attribute3, PSL.attribute4,
529 PSL.attribute5, PSL.attribute6,
530 PSL.attribute7, PSL.attribute8,
531 PSL.attribute9, PSL.attribute10,
532 PSL.attribute11, PSL.attribute12,
533 PSL.attribute13, PSL.attribute14,
534 PSL.attribute15, PSL.attribute16,
535 PSL.attribute17, PSL.attribute18,
536 PSL.attribute19, PSL.attribute20,
537 PSL.attribute21, PSL.attribute22,
538 PSL.attribute23, PSL.attribute24,
539 PSL.attribute25, PSL.attribute26,
540 PSL.attribute27, PSL.attribute28,
541 PSL.attribute29, PSL.attribute30,
542 PSL.last_update_date, PSL.last_updated_by,
543 PSL.last_update_login, PSL.created_by,
544 PSL.creation_date, PSL.set_of_books_id,
545 PSL.business_group_id, PSL.status_code,
546 PSL.gms_batch_name, PSL.gms_posting_effective_date,/*posting eff dt added for zero work days */
547 PSL.expenditure_id, PSL.expenditure_item_id, -- added five exp columns for 2445196
548 PSL.expenditure_ending_date, PSL.interface_id,
549 PSL.txn_interface_id, PSL.actual_summary_amount --For bug 2496661 : Added a new column actual_summary_amount
550 FROM PSP_SUMMARY_LINES PSL
551 WHERE PSL.time_period_id = valid_period_rec.time_period_id;
552
553 -- Delete from the actual table PSP_DISTRIBUTION_LINES_HISTORY for the valid_period
554 DELETE PSP_DISTRIBUTION_LINES_HISTORY PDLH
555 WHERE PDLH.summary_line_id in
556 (
557 SELECT PSL.summary_line_id
558 FROM PSP_SUMMARY_LINES PSL
559 WHERE PSL.summary_line_id = PDLH.summary_line_id
560 and PSL.time_period_id = valid_period_rec.time_period_id
561 );
562
563 -- Delete from the actual table PSP_ADJUSTMENT_LINES_HISTORY for the valid_period
564 DELETE PSP_ADJUSTMENT_LINES_HISTORY PALH
565 WHERE PALH.time_period_id = valid_period_rec.time_period_id;
566
567 -- Delete from the actual table PSP_PRE_GEN_DIST_LINES_HISTORYfor the curr_period
568 DELETE PSP_PRE_GEN_DIST_LINES_HISTORY PPGDH
569 WHERE PPGDH. time_period_id = valid_period_rec.time_period_id;
570
571 -- Delete from the actual table PSP_SUMMARY_LINES for the valid_period
572 DELETE PSP_SUMMARY_LINES PSL
573 WHERE PSL.time_period_id = valid_period_rec.time_period_id;
574
575 -- Update the status of archive_flag in PSP_PAYROLL_CONTROLS to 'Y'
576 UPDATE PSP_PAYROLL_CONTROLS PPC
577 SET PPC.archive_flag = 'Y'
578 WHERE PPC.time_period_id = valid_period_rec.time_period_id;
579
580 Commit;
581 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_LD_ARCHIVE_PERIOD');
582 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
583 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
584 fnd_msg_pub.add;
585 END LOOP;
586 CLOSE valid_period_cur;
587
588 l_status:='successful';
589 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
590 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
591 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
592 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
593 fnd_msg_pub.add;
594 --psp_message_s.print_success;
595 psp_message_s.print_error(p_mode=>FND_FILE.log,
596 p_print_header=>FND_API.G_FALSE);
597 retcode := 0;
598 EXCEPTION
599
600 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
601 /* Following added for bug 2482603 */
602 g_error_api_path := SUBSTR('ARCHIVE_DISTRIBUTION '||g_error_api_path,1,230);
603 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
604 psp_message_s.print_error( p_mode => FND_FILE.LOG,
605 p_print_header => FND_API.G_TRUE);
606 retcode := 2;
607
608 WHEN OTHERS THEN
609 /* Following added for bug 2482603 */
610 g_error_api_path := SUBSTR('ARCHIVE_DISTRIBUTION '||g_error_api_path,1,230);
611 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
612 psp_message_s.print_error( p_mode => FND_FILE.LOG,
613 p_print_header => FND_API.G_TRUE);
614 retcode := 2;
615 END archive_distribution;
616
617 /****************************************************************************************
618 Created By : Lveerubh
619
620 Date Created By : 03-MAR-2001
621
622 Purpose : This procedure is to retrieve labor cost distribution history
623 for a given payroll name ,begin period and end period.
624
625 Know limitations, enhancements or remarks :
626
627 Change History :
628
629 ****************************************************************************************/
630 PROCEDURE retrieve_distribution(errbuf OUT NOCOPY VARCHAR2,
631 retcode OUT NOCOPY VARCHAR2,
632 p_payroll_id IN NUMBER,
633 p_begin_period IN NUMBER,
634 p_end_period IN NUMBER,
635 p_business_group_id IN NUMBER,
636 p_set_of_books_id IN NUMBER)
637 IS
638 -- Cursor to select valid periods that can be retrieved
639 -- For bug fix 1777003, 1778727, retained the following select stmt. as the functionality had changed.
640 CURSOR valid_period_cur IS
641 SELECT distinct PPC.time_period_id,
642 PTP.period_name
643 FROM PSP_PAYROLL_CONTROLS PPC,
644 PER_TIME_PERIODS PTP
645 WHERE PPC.payroll_id = p_payroll_id
646 and PPC.time_period_id >= p_begin_period
647 and PPC.time_period_id <= p_end_period
648 and PPC.archive_flag = 'Y'
649 and PPC.business_group_id = p_business_group_id
650 and PPC. set_of_books_id = p_set_of_books_id
651 and PPC.time_period_id = PTP.time_period_id
652 ORDER BY PPC.time_period_id;
653
654 -- Cursors to get payroll name, begin period name, end period name for displaying in the messages
655 CURSOR payroll_name_cur IS
656 SELECT distinct PPF.payroll_name
657 FROM PAY_PAYROLLS_F PPF
658 WHERE PPF.payroll_id = p_payroll_id
659 and PPF.business_group_id = p_business_group_id
660 and PPF.gl_set_of_books_id = p_set_of_books_id;
661
662 CURSOR begin_period_name_cur IS
663 SELECT distinct PTP.period_name
664 FROM PER_TIME_PERIODS PTP
665 WHERE PTP.time_period_id = p_begin_period;
666
667 CURSOR end_period_name_cur IS
668 SELECT distinct PTP.period_name
669 FROM PER_TIME_PERIODS PTP
670 WHERE PTP.time_period_id = p_end_period;
671
672 l_error_api_name VARCHAR2(2000);
673 l_status VARCHAR2(15);
674 l_error_period_count NUMBER;
675 l_time_period NUMBER(15);
676 l_period_name VARCHAR2(70);
677 l_begin_period_name VARCHAR2(70);
678 l_end_period_name VARCHAR2(70);
679 l_process_type VARCHAR2(15) := 'Retrieve';
680 l_lines_type VARCHAR2(30) := 'Distribution Lines';
681 l_payroll_name VARCHAR2(80);
682 payroll_name_rec payroll_name_cur%ROWTYPE;
683 begin_period_name_rec begin_period_name_cur%ROWTYPE;
684 end_period_name_rec end_period_name_cur%ROWTYPE;
685 valid_period_rec valid_period_cur%ROWTYPE;
686
687 BEGIN
688 fnd_msg_pub.initialize;
689 OPEN payroll_name_cur;
690 FETCH payroll_name_cur INTO payroll_name_rec;
691 l_payroll_name := payroll_name_rec.payroll_name;
692 CLOSE payroll_name_cur;
693
694 OPEN begin_period_name_cur;
695 FETCH begin_period_name_cur INTO begin_period_name_rec;
696 l_begin_period_name := begin_period_name_rec.period_name;
697 CLOSE begin_period_name_cur;
698
699 OPEN end_period_name_cur;
700 FETCH end_period_name_cur INTO end_period_name_rec;
701 l_end_period_name := end_period_name_rec.period_name;
702 CLOSE end_period_name_cur;
703
704 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_START');
705 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
706 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
707 FND_MESSAGE.SET_TOKEN('BEGIN_PERIOD', l_begin_period_name);
708 FND_MESSAGE.SET_TOKEN('END_PERIOD', l_end_period_name);
709 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
710 fnd_msg_pub.add;
711
712 OPEN valid_period_cur;
713 LOOP
714 FETCH valid_period_cur INTO valid_period_rec;
715 EXIT WHEN valid_period_cur%NOTFOUND ;
716 l_period_name := valid_period_rec.period_name;
717
718
719 -- Insert data from archive table PSP_DISTRIBUTION_LINES_ARCH into PSP_DISTRIBUTION_LINES_HISTORY for the valid_period
720
721 INSERT INTO PSP_DISTRIBUTION_LINES_HISTORY
722 (
723 distribution_line_id, distribution_date,
724 effective_date, distribution_amount,
725 status_code, default_reason_code,
726 suspense_reason_code, include_in_er_flag,
727 effort_report_id, version_num,
728 schedule_line_id, summary_line_id,
729 default_org_account_id, suspense_org_account_id,
730 element_account_id, org_schedule_id,
731 gl_project_flag, reversal_entry_flag,
732 user_defined_field, adjustment_batch_name,
733 set_of_books_id, payroll_sub_line_id,
734 auto_expenditure_type, auto_gl_code_combination_id,
735 business_group_id, attribute_category, -- Introduced DFF columns for bug fix 2908859
736 attribute1, attribute2,
737 attribute3, attribute4,
738 attribute5, attribute6,
739 attribute7, attribute8,
740 attribute9, attribute10,
741 cap_excess_glccid, cap_excess_award_id,
742 cap_excess_task_id, cap_excess_project_id,
743 cap_excess_exp_type, cap_excess_exp_org_id,
744 funding_source_code, annual_salary_cap,
745 cap_excess_dist_line_id, suspense_auto_glccid,
746 suspense_auto_exp_type, adj_account_flag)
747 SELECT
748 PDLA.distribution_line_id, PDLA.distribution_date,
749 PDLA.effective_date, PDLA.distribution_amount,
750 PDLA.status_code, PDLA.default_reason_code,
751 PDLA.suspense_reason_code, PDLA.include_in_er_flag,
752 PDLA.effort_report_id, PDLA.version_num,
753 PDLA.schedule_line_id, PDLA.summary_line_id,
754 PDLA.default_org_account_id, PDLA.suspense_org_account_id,
755 PDLA.element_account_id, PDLA.org_schedule_id,
756 PDLA.gl_project_flag, PDLA.reversal_entry_flag,
757 PDLA.user_defined_field, PDLA.adjustment_batch_name,
758 PDLA.set_of_books_id, PDLA.payroll_sub_line_id,
759 PDLA.auto_expenditure_type, PDLA.auto_gl_code_combination_id,
760 pdla.business_group_id, pdla.attribute_category, -- Introduced DFF columns for bug fix 2908859
761 pdla.attribute1, pdla.attribute2,
762 pdla.attribute3, pdla.attribute4,
763 pdla.attribute5, pdla.attribute6,
764 pdla.attribute7, pdla.attribute8,
765 pdla.attribute9, pdla.attribute10,
766 pdla.cap_excess_glccid, pdla.cap_excess_award_id, --4304623: nih salary cap
767 pdla.cap_excess_task_id, pdla.cap_excess_project_id,
768 pdla.cap_excess_exp_type, pdla.cap_excess_exp_org_id,
769 pdla.funding_source_code, pdla.annual_salary_cap,
770 pdla.cap_excess_dist_line_id, pdla.suspense_auto_glccid,
771 pdla.suspense_auto_exp_type, pdla.adj_account_flag
772 FROM PSP_DISTRIBUTION_LINES_ARCH PDLA,
773 PSP_SUMMARY_LINES_ARCH PSLA
774 WHERE PDLA.summary_line_id = PSLA.summary_line_id
775 and PSLA.time_period_id = valid_period_rec.time_period_id ;
776
777 -- Insert data from archive table PSP_ADJUSTMENT_LINES_ARCH into PSP_ADJUSTMENT_LINES_HISTORY for the valid_period
778 INSERT INTO PSP_ADJUSTMENT_LINES_HISTORY
779 (
780 adjustment_line_id, person_id,
781 assignment_id, element_type_id,
782 distribution_date, effective_date,
783 distribution_amount, dr_cr_flag,
784 payroll_control_id, source_code,
785 time_period_id, batch_name,
786 status_code, set_of_books_id,
787 gl_code_combination_id, project_id,
788 expenditure_organization_id, expenditure_type,
789 task_id, award_id,
790 suspense_org_account_id, suspense_reason_code,
791 include_in_er_flag, effort_report_id,
792 version_num, summary_line_id,
793 reversal_entry_flag, original_line_flag,
794 user_defined_field, adjustment_batch_name,
795 percent, orig_source_type,
796 orig_line_id, attribute_category,
797 attribute1, attribute2,
798 attribute3, attribute4,
799 attribute5, attribute6,
800 attribute7, attribute8,
801 attribute9, attribute10,
802 attribute11, attribute12,
803 attribute13, attribute14,
804 attribute15, last_update_date,
805 last_updated_by, last_update_login,
806 created_by, creation_date,
807 source_type, business_group_id,
808 adj_set_number, line_number
809 )
810 SELECT
811 PALA.adjustment_line_id, PALA.person_id,
812 PALA.assignment_id, PALA.element_type_id,
813 PALA.distribution_date, PALA.effective_date,
814 PALA.distribution_amount, PALA.dr_cr_flag,
815 PALA.payroll_control_id, PALA.source_code,
816 PALA.time_period_id, PALA.batch_name,
817 PALA.status_code, PALA.set_of_books_id,
818 PALA.gl_code_combination_id, PALA.project_id,
819 PALA.expenditure_organization_id, PALA.expenditure_type,
820 PALA.task_id, PALA.award_id,
821 PALA.suspense_org_account_id, PALA.suspense_reason_code,
822 PALA.include_in_er_flag, PALA.effort_report_id,
823 PALA.version_num, PALA.summary_line_id,
824 PALA.reversal_entry_flag, PALA.original_line_flag,
825 PALA.user_defined_field, PALA.adjustment_batch_name,
826 PALA.percent, PALA.orig_source_type,
827 PALA.orig_line_id, PALA.attribute_category,
828 PALA.attribute1, PALA.attribute2,
829 PALA.attribute3, PALA.attribute4,
830 PALA.attribute5, PALA.attribute6,
831 PALA.attribute7, PALA.attribute8,
832 PALA.attribute9, PALA.attribute10,
833 PALA.attribute11, PALA.attribute12,
834 PALA.attribute13, PALA.attribute14,
835 PALA.attribute15, PALA.last_update_date,
836 PALA.last_updated_by, PALA.last_update_login,
837 PALA.created_by, PALA.creation_date,
838 PALA.source_type, PALA.business_group_id,
839 PALA.adj_set_number, PALA.line_number
840 FROM PSP_ADJUSTMENT_LINES_ARCH PALA
841 WHERE PALA.time_period_id = valid_period_rec.time_period_id ;
842
843 -- Insert data from archive table PSP_PRE_GEN_DIST_LINES_ARCH into PSP_PRE_GEN_LINES_HISTORY for the valid_period.
844 INSERT INTO PSP_PRE_GEN_DIST_LINES_HISTORY
845 (
846 pre_gen_dist_line_id, distribution_interface_id,
847 person_id, assignment_id,
848 element_type_id, distribution_date,
849 effective_date, distribution_amount,
850 dr_cr_flag, payroll_control_id,
851 source_type, source_code,
852 time_period_id, batch_name,
853 status_code, set_of_books_id,
854 gl_code_combination_id, project_id,
855 expenditure_organization_id, expenditure_type,
856 task_id, award_id,
857 suspense_org_account_id, suspense_reason_code,
858 include_in_er_flag, effort_report_id,
859 version_num, summary_line_id,
860 reversal_entry_flag, user_defined_field,
861 adjustment_batch_name, business_group_id,
862 attribute_category, -- Introduced DFF columns for bug fix 2908859
863 attribute1, attribute2,
864 attribute3, attribute4,
865 attribute5, attribute6,
866 attribute7, attribute8,
867 attribute9, attribute10,
868 suspense_auto_glccid, suspense_auto_exp_type
869 )
870 SELECT
871 PGDLA.pre_gen_dist_line_id, PGDLA.distribution_interface_id,
872 PGDLA.person_id, PGDLA.assignment_id,
873 PGDLA.element_type_id, PGDLA.distribution_date,
874 PGDLA.effective_date, PGDLA.distribution_amount,
875 PGDLA.dr_cr_flag, PGDLA.payroll_control_id,
876 PGDLA.source_type, PGDLA.source_code,
877 PGDLA.time_period_id, PGDLA.batch_name,
878 PGDLA.status_code, PGDLA.set_of_books_id,
879 PGDLA.gl_code_combination_id, PGDLA.project_id,
880 PGDLA.expenditure_organization_id, PGDLA.expenditure_type,
881 PGDLA.task_id, PGDLA.award_id,
882 PGDLA.suspense_org_account_id, PGDLA.suspense_reason_code,
883 PGDLA.include_in_er_flag, PGDLA.effort_report_id,
884 PGDLA.version_num, PGDLA.summary_line_id,
885 PGDLA.reversal_entry_flag, PGDLA.user_defined_field,
886 PGDLA.adjustment_batch_name, PGDLA.business_group_id,
887 pgdla.attribute_category, -- Introduced DFF columns for bug fix 2908859
888 pgdla.attribute1, pgdla.attribute2,
889 pgdla.attribute3, pgdla.attribute4,
890 pgdla.attribute5, pgdla.attribute6,
891 pgdla.attribute7, pgdla.attribute8,
892 pgdla.attribute9, pgdla.attribute10,
893 pgdla.suspense_auto_glccid, pgdla.suspense_auto_exp_type
894 FROM PSP_PRE_GEN_DIST_LINES_ARCH PGDLA
895 WHERE PGDLA.time_period_id = valid_period_rec.time_period_id ;
896
897 -- Insert data from archive table PSP_SUMMARY_LINES_ARCH into PSP_SUMMARY_LINES for the valid_period
898 INSERT INTO PSP_SUMMARY_LINES
899 (
900 summary_line_id, source_type,
901 source_code, time_period_id,
902 interface_batch_name, person_id,
903 assignment_id, effective_date,
904 accounting_date, exchange_rate_type,
905 payroll_control_id, gl_code_combination_id,
906 project_id, expenditure_organization_id,
907 expenditure_type, task_id,
908 award_id, summary_amount,
909 dr_cr_flag, group_id,
910 interface_status, attribute_category,
911 attribute1, attribute2,
912 attribute3, attribute4,
913 attribute5, attribute6,
914 attribute7, attribute8,
915 attribute9, attribute10,
916 attribute11, attribute12,
917 attribute13, attribute14,
918 attribute15, attribute16,
919 attribute17, attribute18,
920 attribute19, attribute20,
921 attribute21, attribute22,
922 attribute23, attribute24,
923 attribute25, attribute26,
924 attribute27, attribute28,
925 attribute29, attribute30,
926 last_update_date, last_updated_by,
927 last_update_login, created_by,
928 creation_date, set_of_books_id,
929 business_group_id, status_code,
930 gms_batch_name, gms_posting_effective_date, /*posting eff-dt added for zero work days */
931 expenditure_id, expenditure_item_id, -- added five exp columns for 2445196
932 expenditure_ending_date, interface_id,
933 txn_interface_id, actual_summary_amount --For Bug 2496661 : Added a new column
934 )
935 SELECT
936 PSLA.summary_line_id, PSLA.source_type,
937 PSLA.source_code, PSLA.time_period_id,
938 PSLA.interface_batch_name, PSLA.person_id,
939 PSLA.assignment_id, PSLA.effective_date,
940 PSLA.accounting_date, PSLA.exchange_rate_type,
941 PSLA.payroll_control_id, PSLA.gl_code_combination_id,
942 PSLA.project_id, PSLA.expenditure_organization_id,
943 PSLA.expenditure_type, PSLA.task_id,
944 PSLA.award_id, PSLA.summary_amount,
945 PSLA.dr_cr_flag, PSLA.group_id,
946 PSLA.interface_status, PSLA.attribute_category,
947 PSLA.attribute1, PSLA.attribute2,
948 PSLA.attribute3, PSLA.attribute4,
949 PSLA.attribute5, PSLA.attribute6,
950 PSLA.attribute7, PSLA.attribute8,
951 PSLA.attribute9, PSLA.attribute10,
952 PSLA.attribute11, PSLA.attribute12,
953 PSLA.attribute13, PSLA.attribute14,
954 PSLA.attribute15, PSLA.attribute16,
955 PSLA.attribute17, PSLA.attribute18,
956 PSLA.attribute19, PSLA.attribute20,
957 PSLA.attribute21, PSLA.attribute22,
958 PSLA.attribute23, PSLA.attribute24,
959 PSLA.attribute25, PSLA.attribute26,
960 PSLA.attribute27, PSLA.attribute28,
961 PSLA.attribute29, PSLA.attribute30,
962 PSLA.last_update_date, PSLA.last_updated_by,
963 PSLA.last_update_login, PSLA.created_by,
964 PSLA.creation_date, PSLA.set_of_books_id,
965 PSLA.business_group_id, PSLA.status_code,
966 PSLA.gms_batch_name, PSLA.gms_posting_effective_date, /* column been added for zero work days */
967 PSLA.expenditure_id, PSLA.expenditure_item_id, -- added five columns for 2445196
968 PSLA.expenditure_ending_date, PSLA.interface_id,
969 PSLA.txn_interface_id, PSLA.actual_summary_amount --For Bug 2496661: Added new column
970 FROM PSP_SUMMARY_LINES_ARCH PSLA
971 WHERE PSLA.time_period_id = valid_period_rec.time_period_id;
972
973 -- Delete from the archive table PSP_DISTRIBUTION_LINES_ARCH for the valid_period
974 DELETE PSP_DISTRIBUTION_LINES_ARCH PDLA
975 WHERE PDLA.summary_line_id in
976 (
977 SELECT PSLA.summary_line_id
978 FROM PSP_SUMMARY_LINES PSLA
979 WHERE PSLA.summary_line_id = PDLA.summary_line_id
980 and PSLA.time_period_id = valid_period_rec.time_period_id
981 );
982
983 -- Delete from the archive table PSP_ADJUSTMENT_LINES_ARCH for the valid_period
984 DELETE PSP_ADJUSTMENT_LINES_ARCH PALA
985 WHERE PALA.time_period_id = valid_period_rec.time_period_id;
986
987 -- Delete from the archive table PSP_PRE_GEN_DIST_LINES_ARCH for the curr_period
988 DELETE PSP_PRE_GEN_DIST_LINES_ARCH PPGDA
989 WHERE PPGDA.time_period_id = valid_period_rec.time_period_id;
990
991 -- Delete from the archive table PSP_SUMMARY_LINES_ARCH for the valid_period
992 -- Replaced PSP_SUMMARY_LINES table with PSP_SUMMARY_LINES_ARCH for bug fix 1761830
993 DELETE PSP_SUMMARY_LINES_ARCH PSLA
994 WHERE PSLA.time_period_id = valid_period_rec.time_period_id;
995
996 -- Update the status of archive_flag in PSP_PAYROLL_CONTROLS to NULL
997 UPDATE PSP_PAYROLL_CONTROLS PPC
998 SET PPC.archive_flag = NULL
999 WHERE PPC.time_period_id = valid_period_rec.time_period_id;
1000
1001 Commit;
1002 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_LD_RETRIEVE_PERIOD');
1003 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
1004 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
1005 fnd_msg_pub.add;
1006 END LOOP;
1007 CLOSE valid_period_cur;
1008
1009 l_status:='successful';
1010 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
1011 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE', l_process_type);
1012 FND_MESSAGE.SET_TOKEN('LINES_TYPE', l_lines_type);
1013 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
1014 fnd_msg_pub.add;
1015 --psp_message_s.print_success;
1016 psp_message_s.print_error(p_mode=>FND_FILE.log,
1017 p_print_header=>FND_API.G_FALSE);
1018 retcode := 0;
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 /* Following Added for bug 2482603 */
1022 g_error_api_path := SUBSTR('RETRIEVE_DISTRIBUTION '||g_error_api_path,1,230);
1023 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
1024 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1025 p_print_header => FND_API.G_TRUE);
1026 retcode := 2;
1027 END retrieve_distribution;
1028
1029 /****************************************************************************************
1030 Created By : spchakra
1031
1032 Date Created By : 23-FEB-2001
1033
1034 Purpose : This procedure is to archive encumbrance history
1035 for a given payroll name ,begin period and end period.s
1036
1037 Know limitations, enhancements or remarks :
1038
1039 Change History :
1040
1041 ****************************************************************************************/
1042 PROCEDURE archive_encumbrance( errbuf OUT NOCOPY VARCHAR2,
1043 retcode OUT NOCOPY VARCHAR2,
1044 p_payroll_id IN NUMBER,
1045 p_begin_period IN NUMBER,
1046 p_end_period IN NUMBER,
1047 p_business_group_id IN NUMBER,
1048 p_set_of_books_id IN NUMBER)
1049 IS
1050 -- Cursor to select invalid time periods
1051 CURSOR invalid_period_cur
1052 IS Select distinct period_name
1053 FROM PSP_ENC_CONTROLS PEC,
1054 PER_TIME_PERIODS PTP
1055 WHERE PEC.time_period_id = PTP.time_period_id
1056 AND PEC.payroll_id = p_payroll_id
1057 AND PEC.time_period_id >= p_begin_period
1058 AND PEC.time_period_id <= p_end_period
1059 AND PEC.archive_flag is NULL
1060 AND PEC.time_period_id in (SELECT time_period_id
1061 FROM PSP_ENC_CONTROLS PEC2
1062 WHERE PEC2.time_period_id = PEC.time_period_id
1063 AND PEC2.action_code <> 'L');
1064
1065 -- Cursor to select valid periods that can be archived
1066 CURSOR valid_period_cur
1067 IS SELECT distinct PEC.time_period_id, PTP.period_name
1068 FROM PSP_ENC_CONTROLS PEC,
1069 PER_TIME_PERIODS PTP
1070 WHERE PTP.time_period_id = PEC.time_period_id
1071 AND PEC.payroll_id = p_payroll_id
1072 AND PEC.time_period_id >= p_begin_period
1073 AND PEC.time_period_id <= p_end_period
1074 AND PEC.archive_flag is NULL
1075 AND PEC.business_group_id = p_business_group_id
1076 AND PEC. set_of_books_id = p_set_of_books_id
1077 ORDER BY PEC.time_period_id;
1078
1079 -- Cursor to get payroll name, begin period name, end period name for displaying in the messages
1080 CURSOR parameter_cur
1081 IS SELECT distinct PPF.payroll_name, PTP1.period_name, PTP2.period_name
1082 FROM PAY_PAYROLLS_F PPF,
1083 PER_TIME_PERIODS PTP1,
1084 PER_TIME_PERIODS PTP2
1085 WHERE PPF.payroll_id = p_payroll_id
1086 AND PTP1.payroll_id = p_payroll_id
1087 AND PTP1.time_period_id = p_begin_period
1088 AND PTP2.payroll_id = p_payroll_id
1089 AND PTP2.time_period_id = p_end_period
1090 AND PPF.business_group_id = p_business_group_id;
1091
1092 l_status VarChar2(80);
1093 l_payroll_name VarChar2(80);
1094 l_begin_period VarChar2(70);
1095 l_end_period VarChar2(70);
1096 l_process_type VarChar2(15) := 'Archive';
1097 l_lines_type VarChar2(30) := 'Encumbrance Lines';
1098 l_period_name VarChar2(70);
1099
1100 l_time_period Number(15);
1101 l_error_period_count Number(15);
1102
1103 BEGIN
1104
1105 fnd_msg_pub.initialize;
1106
1107 -- Retrieve the payroll name, begin , end period names for process initialization message
1108 Open parameter_cur;
1109 Fetch parameter_cur Into l_payroll_name, l_begin_period, l_end_period;
1110 Close parameter_cur;
1111
1112 -- Process Initialization Message
1113 FND_MESSAGE.SET_NAME('PSP','PSP_ARC_ARCHIVE_RETRIEVE_START');
1114 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_process_type);
1115 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
1116 FND_MESSAGE.SET_TOKEN('LINES_TYPE',l_lines_type);
1117 FND_MESSAGE.SET_TOKEN('BEGIN_PERIOD',l_begin_period);
1118 FND_MESSAGE.SET_TOKEN('END_PERIOD',l_end_period);
1119 fnd_msg_pub.add;
1120
1121 -- Check for Error time periods, If present log their corresponding messages
1122 Open invalid_period_cur;
1123 Fetch invalid_period_cur Into l_period_name;
1124 l_error_period_count := invalid_period_cur%ROWCOUNT;
1125
1126 If (l_error_period_count > 0) Then
1127 Loop
1128 -- Print the log message for the current period
1129 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_EN_CANNOT_ARCHIVE');
1130 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
1131 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
1132 fnd_msg_pub.add;
1133
1134 Fetch invalid_period_cur Into l_period_name;
1135 Exit When invalid_period_cur%NOTFOUND;
1136 End Loop;
1137
1138 Close invalid_period_cur;
1139
1140 -- Set the status flag to 'Unsuccesful' for printing it on the log message
1141 l_status:='Unsuccessful';
1142
1143 -- Print the process end log message
1144 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
1145 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_process_type);
1146 FND_MESSAGE.SET_TOKEN('LINES_TYPE',l_lines_type);
1147 FND_MESSAGE.SET_TOKEN('STATUS',l_status);
1148 fnd_msg_pub.add;
1149
1150 -- Raise Unexpected Error exception to end the Procedure
1151 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1152 End If;
1153
1154 -- The valid period loop starts here
1155 Open valid_period_cur;
1156 Loop
1157 Fetch valid_period_cur Into l_time_period, l_period_name;
1158 Exit When valid_period_cur%NOTFOUND;
1159
1160 -- Insert data into ARCHIVE tables from HISTORY tables
1161 -- Insert data Into PSP_ENC_LINES_ARCH from PSP_ENC_LINES_HISTORY for the current period
1162 -- Added enc_start_date,enc_end_date for Enh. Bug# 2259310.
1163 INSERT INTO PSP_ENC_LINES_ARCH
1164 (enc_line_id, business_group_id, enc_element_type_id,
1165 encumbrance_date, enc_line_type, schedule_line_id,
1166 org_schedule_id, default_org_account_id, suspense_org_account_id,
1167 element_account_id, gl_project_flag, enc_summary_line_id,
1168 person_id, assignment_id, award_id,
1169 task_id, expenditure_type, expenditure_organization_id,
1170 project_id, gl_code_combination_id, time_period_id,
1171 payroll_id, set_of_books_id, default_reason_code,
1172 suspense_reason_code, status_code, enc_control_id,
1173 dr_cr_flag, last_update_date, last_updated_by,
1174 last_update_login, created_by, creation_date,
1175 encumbrance_amount, change_flag, enc_start_date,
1176 enc_end_date, attribute_category, attribute1,
1177 attribute2, attribute3, attribute4,
1178 attribute5, attribute6, attribute7,
1179 attribute8, attribute9, attribute10,
1180 payroll_action_id, orig_gl_code_combination_id, orig_project_id, orig_task_id,
1181 orig_award_id, orig_expenditure_org_id, orig_expenditure_type, hierarchy_code,
1182 hierarchy_start_date, hierarchy_end_date)
1183 SELECT PELH.enc_line_id, PELH.business_group_id, PELH.enc_element_type_id,
1184 PELH.encumbrance_date, PELH.enc_line_type, PELH.schedule_line_id,
1185 PELH.org_schedule_id, PELH.default_org_account_id, PELH.suspense_org_account_id,
1186 PELH.element_account_id, PELH.gl_project_flag, PELH.enc_summary_line_id,
1187 PELH.person_id, PELH.assignment_id, PELH.award_id,
1188 PELH.task_id, PELH.expenditure_type, PELH.expenditure_organization_id,
1189 PELH.project_id, PELH.gl_code_combination_id, PELH.time_period_id,
1190 PELH.payroll_id, PELH.set_of_books_id, PELH.default_reason_code,
1191 PELH.suspense_reason_code, PELH.status_code, PELH.enc_control_id,
1192 PELH.dr_cr_flag, PELH.last_update_date, PELH.last_updated_by,
1193 PELH.last_update_login, PELH.created_by, PELH.creation_date,
1194 PELH.encumbrance_amount, PELH.change_flag, PELH.enc_start_date,
1195 PELH.enc_end_date, pelh.attribute_category, pelh.attribute1,
1196 pelh.attribute2, pelh.attribute3, pelh.attribute4,
1197 pelh.attribute5, pelh.attribute6, pelh.attribute7,
1198 pelh.attribute8, pelh.attribute9, pelh.attribute10,
1199 pelh.payroll_action_id, pelh.orig_gl_code_combination_id, pelh.orig_project_id,
1200 pelh.orig_task_id, pelh.orig_award_id, pelh.orig_expenditure_org_id,
1201 pelh.orig_expenditure_type, pelh.hierarchy_code,
1202 pelh.hierarchy_start_date, pelh.hierarchy_end_date
1203 FROM PSP_ENC_LINES_HISTORY PELH
1204 WHERE PELH.time_period_id = l_time_period;
1205
1206 -- Insert data Into PSP_ENC_SUMMARY_LINES_ARCH from PSP_ENC_SUMMARY_LINES for the current period
1207 INSERT INTO PSP_ENC_SUMMARY_LINES_ARCH
1208 (enc_summary_line_id, business_group_id, gms_batch_name,
1209 time_period_id, person_id, assignment_id,
1210 effective_date, set_of_books_id, gl_code_combination_id,
1211 project_id, expenditure_organization_id, expenditure_type,
1212 task_id, award_id, summary_amount,
1213 dr_cr_flag, group_id, interface_status,
1214 payroll_id, gl_period_id, gl_project_flag,
1215 attribute_category, attribute1, attribute2,
1216 attribute3, attribute4, attribute5,
1217 attribute6, attribute7, attribute8,
1218 attribute9, attribute10, attribute11,
1219 attribute12, attribute13, attribute14,
1220 attribute15, attribute16, attribute17,
1221 attribute18, attribute19, attribute20,
1222 attribute21, attribute22, attribute23,
1223 attribute24, attribute25, attribute26,
1224 attribute27, attribute28, attribute29,
1225 attribute30, reject_reason_code, enc_control_id,
1226 status_code, last_update_date, last_updated_by,
1227 last_update_login, created_by, creation_date,
1228 suspense_org_account_id, superceded_line_id, gms_posting_override_date,
1229 gl_posting_override_date,
1230 expenditure_id, expenditure_item_id, -- added five exp columns for 2445196
1231 expenditure_ending_date, interface_id,
1232 txn_interface_id, payroll_action_id, liquidate_request_id, proposed_termination_date, update_flag)
1233 SELECT PESL.enc_summary_line_id, PESL.business_group_id, PESL.gms_batch_name,
1234 PESL.time_period_id, PESL.person_id, PESL.assignment_id,
1235 PESL.effective_date, PESL.set_of_books_id, PESL.gl_code_combination_id,
1236 PESL.project_id, PESL.expenditure_organization_id, PESL.expenditure_type,
1237 PESL.task_id, PESL.award_id, PESL.summary_amount,
1238 PESL.dr_cr_flag, PESL.group_id, PESL.interface_status,
1239 PESL.payroll_id, PESL.gl_period_id, PESL.gl_project_flag,
1240 PESL.attribute_category, PESL.attribute1, PESL.attribute2,
1241 PESL.attribute3, PESL.attribute4, PESL.attribute5,
1242 PESL.attribute6, PESL.attribute7, PESL.attribute8,
1243 PESL.attribute9, PESL.attribute10, PESL.attribute11,
1244 PESL.attribute12, PESL.attribute13, PESL.attribute14,
1245 PESL.attribute15, PESL.attribute16, PESL.attribute17,
1246 PESL.attribute18, PESL.attribute19, PESL.attribute20,
1247 PESL.attribute21, PESL.attribute22, PESL.attribute23,
1248 PESL.attribute24, PESL.attribute25, PESL.attribute26,
1249 PESL.attribute27, PESL.attribute28, PESL.attribute29,
1250 PESL.attribute30, PESL.reject_reason_code, PESL.enc_control_id,
1251 PESL.status_code, PESL.last_update_date, PESL.last_updated_by,
1252 PESL.last_update_login, PESL.created_by, PESL.creation_date,
1253 PESL.suspense_org_account_id, PESL.superceded_line_id, PESL.gms_posting_override_date,
1254 PESL.gl_posting_override_date,
1255 PESL.expenditure_id, PESL.expenditure_item_id, -- added five exp columns for 2445196
1256 PESL.expenditure_ending_date, PESL.interface_id,
1257 PESL.txn_interface_id, payroll_action_id, liquidate_request_id, proposed_termination_date, update_flag
1258 FROM PSP_ENC_SUMMARY_LINES PESL
1259 WHERE PESL.time_period_id = l_time_period;
1260
1261 -- Purge the archived data
1262 -- Delete from PSP_ENC_LINES_HISTORY for the current period
1263 DELETE PSP_ENC_LINES_HISTORY
1264 WHERE time_period_id = l_time_period;
1265
1266 -- Delete from PSP_ENC_SUMMARY_LINES for the current period
1267 DELETE PSP_ENC_SUMMARY_LINES
1268 WHERE time_period_id = l_time_period;
1269
1270 -- Update the status of archive_flag in PSP_ENC_CONTROLS to 'Y'
1271 Update PSP_ENC_CONTROLS
1272 Set archive_flag = 'Y'
1273 WHERE time_period_id = l_time_period;
1274
1275 -- Commit the changes made for thecurrent period
1276 COMMIT;
1277
1278 -- Update the log with with current periods message
1279 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_EN_ARCHIVE_PERIOD');
1280 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
1281 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
1282 fnd_msg_pub.add;
1283
1284 -- Continue with the next period
1285 End Loop;
1286
1287 -- Set the status flag to 'Successfull' as all the periods have been successfully archived
1288 l_status:='Successful';
1289
1290 -- Print the end of process message to the log
1291 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
1292 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_process_type);
1293 FND_MESSAGE.SET_TOKEN('LINES_TYPE',l_lines_type);
1294 FND_MESSAGE.SET_TOKEN('STATUS',l_status);
1295 fnd_msg_pub.add;
1296
1297 -- Write all the accumulated messages into the log
1298 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1299 p_print_header => FND_API.G_FALSE);
1300 retcode := 0;
1301
1302 --Exception handling starts here
1303 Exception
1304 -- The following Exception occurs as part of user call for invalid time periods
1305 When FND_API.G_EXC_UNEXPECTED_ERROR Then
1306 -- Update the log with the messages accumulated so far
1307 /* Following added for bug 2482603 */
1308 g_error_api_path := SUBSTR('ARCHIVE_ENCUMBRANCE '||g_error_api_path,1,230);
1309 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
1310 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1311 p_print_header => FND_API.G_TRUE);
1312 retcode := 2;
1313
1314 -- For any other exception, ROLLBACK
1315 When OTHERS Then
1316 /* Following added for bug 2482603 */
1317 g_error_api_path := SUBSTR('ARCHIVE_ENCUMBRANCE '||g_error_api_path,1,230);
1318 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
1319 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1320 p_print_header => FND_API.G_TRUE);
1321 retcode := 2;
1322 End archive_encumbrance;
1323
1324 /****************************************************************************************
1325 Created By : spchakra
1326
1327 Date Created By : 02-MAR-2001
1328
1329 Purpose : This procedure is to retrieves into encumbrance history
1330 for a given payroll name ,begin period and end period.s
1331
1332 Know limitations, enhancements or remarks :
1333
1334 Change History :
1335
1336 ****************************************************************************************/
1337
1338
1339 PROCEDURE retrieve_encumbrance(errbuf OUT NOCOPY VARCHAR2,
1340 retcode OUT NOCOPY VARCHAR2,
1341 p_payroll_id IN NUMBER,
1342 p_begin_period IN NUMBER,
1343 p_end_period IN NUMBER,
1344 p_business_group_id IN NUMBER,
1345 p_set_of_books_id IN NUMBER)
1346 IS
1347 -- Cursor for selecting valid time periods
1348 -- For bug fix 17770033, 1778727, changed the begin period to end period check as the functionality had changed
1349 CURSOR valid_period_cur
1350 IS SELECT distinct PEC.time_period_id, PTP.period_name
1351 FROM PSP_ENC_CONTROLS PEC,
1352 PER_TIME_PERIODS PTP
1353 WHERE PTP.time_period_id = PEC.time_period_id
1354 AND PEC.payroll_id = p_payroll_id
1355 AND PEC.time_period_id >= p_begin_period
1356 AND PEC.time_period_id <= p_end_period
1357 AND PEC.archive_flag = 'Y'
1358 AND PEC.business_group_id = p_business_group_id
1359 AND PEC. set_of_books_id = p_set_of_books_id
1360 ORDER BY PEC.time_period_id;
1361
1362 -- Cursor to get payroll name, begin period name, end period name for displaying in the messages
1363 CURSOR parameter_cur
1364 IS SELECT distinct PPF.payroll_name, PTP1.period_name, PTP2.period_name
1365 FROM PAY_PAYROLLS_F PPF,
1366 PER_TIME_PERIODS PTP1,
1367 PER_TIME_PERIODS PTP2
1368 WHERE PPF.payroll_id = p_payroll_id
1369 AND PTP1.payroll_id = p_payroll_id
1370 AND PTP1.time_period_id = p_begin_period
1371 AND PTP2.payroll_id = p_payroll_id
1372 AND PTP2.time_period_id = p_end_period
1373 AND PPF.business_group_id = p_business_group_id;
1374
1375 l_status VarChar2(80);
1376 l_payroll_name VarChar2(80);
1377 l_begin_period VarChar2(70);
1378 l_end_period VarChar2(70);
1379 l_process_type VarChar2(15) := 'Retrieve';
1380 l_lines_type VarChar2(30) := 'Encumbrance Lines';
1381 l_period_name VarChar2(70);
1382
1383 l_time_period Number(15);
1384 l_error_period_count Number(15);
1385
1386 BEGIN
1387 fnd_msg_pub.initialize;
1388
1389 -- Retrieve the payroll name, begin , end period names for process initialization message
1390 Open parameter_cur;
1391 Fetch parameter_cur Into l_payroll_name, l_begin_period, l_end_period;
1392 Close parameter_cur;
1393
1394 -- Process Initialization Message
1395 FND_MESSAGE.SET_NAME('PSP','PSP_ARC_ARCHIVE_RETRIEVE_START');
1396 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_process_type);
1397 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME',l_payroll_name);
1398 FND_MESSAGE.SET_TOKEN('LINES_TYPE',l_lines_type);
1399 FND_MESSAGE.SET_TOKEN('BEGIN_PERIOD',l_begin_period);
1400 FND_MESSAGE.SET_TOKEN('END_PERIOD',l_end_period);
1401 fnd_msg_pub.add;
1402
1403 -- The valid period loop starts here
1404 Open valid_period_cur;
1405 Loop
1406 Fetch valid_period_cur Into l_time_period, l_period_name;
1407 Exit When valid_period_cur%NOTFOUND;
1408
1409 -- Insert data into ARCHIVE tables from HISTORY tables
1410 -- Insert data Into PSP_ENC_LINES_HISTORY from PSP_ENC_LINES_ARCH for the current period
1411 -- Added enc_start_date,enc_end_date for Enh. Enc Redesign - Prorata.Bug # 2259310.
1412 INSERT INTO PSP_ENC_LINES_HISTORY
1413 (enc_line_id, business_group_id, enc_element_type_id,
1414 encumbrance_date, enc_line_type, schedule_line_id,
1415 org_schedule_id, default_org_account_id, suspense_org_account_id,
1416 element_account_id, gl_project_flag, enc_summary_line_id,
1417 person_id, assignment_id, award_id,
1418 task_id, expenditure_type, expenditure_organization_id,
1419 project_id, gl_code_combination_id, time_period_id,
1420 payroll_id, set_of_books_id, default_reason_code,
1421 suspense_reason_code, status_code, enc_control_id,
1422 last_update_date, last_updated_by, last_update_login,
1423 created_by, creation_date, dr_cr_flag,
1424 encumbrance_amount, change_flag, enc_start_date,
1425 enc_end_date, attribute_category, attribute1,
1426 attribute2, attribute3, attribute4,
1427 attribute5, attribute6, attribute7,
1428 attribute8, attribute9, attribute10,
1429 payroll_action_id, orig_gl_code_combination_id, orig_project_id, orig_task_id,
1430 orig_award_id, orig_expenditure_org_id, orig_expenditure_type, hierarchy_code,
1431 hierarchy_start_date, hierarchy_end_date)
1432 SELECT PELA.enc_line_id, PELA.business_group_id, PELA.enc_element_type_id,
1433 PELA.encumbrance_date, PELA.enc_line_type, PELA.schedule_line_id,
1434 PELA.org_schedule_id, PELA.default_org_account_id, PELA.suspense_org_account_id,
1435 PELA.element_account_id, PELA.gl_project_flag, PELA.enc_summary_line_id,
1436 PELA.person_id, PELA.assignment_id, PELA.award_id,
1437 PELA.task_id, PELA.expenditure_type, PELA.expenditure_organization_id,
1438 PELA.project_id, PELA.gl_code_combination_id, PELA.time_period_id,
1439 PELA.payroll_id, PELA.set_of_books_id, PELA.default_reason_code,
1440 PELA.suspense_reason_code, PELA.status_code, PELA.enc_control_id,
1441 PELA.last_update_date, PELA.last_updated_by, PELA.last_update_login,
1442 PELA.created_by, PELA.creation_date, PELA.dr_cr_flag,
1443 PELA.encumbrance_amount, PELA.change_flag, PELA.enc_start_date,
1444 PELA.enc_end_date, pela.attribute_category, pela.attribute1,
1445 pela.attribute2, pela.attribute3, pela.attribute4,
1446 pela.attribute5, pela.attribute6, pela.attribute7,
1447 pela.attribute8, pela.attribute9, pela.attribute10,
1448 pela.payroll_action_id, pela.orig_gl_code_combination_id, pela.orig_project_id,
1449 pela.orig_task_id, pela.orig_award_id, pela.orig_expenditure_org_id,
1450 pela.orig_expenditure_type, pela.hierarchy_code,
1451 pela.hierarchy_start_date, pela.hierarchy_end_date
1452 FROM PSP_ENC_LINES_ARCH PELA
1453 WHERE PELA.time_period_id = l_time_period;
1454
1455 -- Insert data Into PSP_ENC_SUMMARY_LINES from PSP_ENC_SUMMARY_LINES_ARCH for the current period
1456 INSERT INTO PSP_ENC_SUMMARY_LINES
1457 (enc_summary_line_id, business_group_id, gms_batch_name,
1458 time_period_id, person_id, assignment_id,
1459 effective_date, set_of_books_id, gl_code_combination_id,
1460 project_id, expenditure_organization_id, expenditure_type,
1461 task_id, award_id, summary_amount,
1462 dr_cr_flag, group_id, interface_status,
1463 payroll_id, gl_period_id, gl_project_flag,
1464 attribute_category, attribute1, attribute2,
1465 attribute3, attribute4, attribute5,
1466 attribute6, attribute7, attribute8,
1467 attribute9, attribute10, attribute11,
1468 attribute12, attribute13, attribute14,
1469 attribute15, attribute16, attribute17,
1470 attribute18, attribute19, attribute20,
1471 attribute21, attribute22, attribute23,
1472 attribute24, attribute25, attribute26,
1473 attribute27, attribute28, attribute29,
1474 attribute30, reject_reason_code, enc_control_id,
1475 status_code, last_update_date, last_updated_by,
1476 last_update_login, created_by, creation_date,
1477 suspense_org_account_id, superceded_line_id, gms_posting_override_date,
1478 gl_posting_override_date,
1479 expenditure_id, expenditure_item_id, -- added five exp columns for 2445196
1480 expenditure_ending_date, interface_id,
1481 txn_interface_id,
1482 payroll_action_id, liquidate_request_id, proposed_termination_date, update_flag)
1483 SELECT PESLA.enc_summary_line_id, PESLA.business_group_id, PESLA.gms_batch_name,
1484 PESLA.time_period_id, PESLA.person_id, PESLA.assignment_id,
1485 PESLA.effective_date, PESLA.set_of_books_id, PESLA.gl_code_combination_id,
1486 PESLA.project_id, PESLA.expenditure_organization_id, PESLA.expenditure_type,
1487 PESLA.task_id, PESLA.award_id, PESLA.summary_amount,
1488 PESLA.dr_cr_flag, PESLA.group_id, PESLA.interface_status,
1489 PESLA.payroll_id, PESLA.gl_period_id, PESLA.gl_project_flag,
1490 PESLA.attribute_category, PESLA.attribute1, PESLA.attribute2,
1491 PESLA.attribute3, PESLA.attribute4, PESLA.attribute5,
1492 PESLA.attribute6, PESLA.attribute7, PESLA.attribute8,
1493 PESLA.attribute9, PESLA.attribute10, PESLA.attribute11,
1494 PESLA.attribute12, PESLA.attribute13, PESLA.attribute14,
1495 PESLA.attribute15, PESLA.attribute16, PESLA.attribute17,
1496 PESLA.attribute18, PESLA.attribute19, PESLA.attribute20,
1497 PESLA.attribute21, PESLA.attribute22, PESLA.attribute23,
1498 PESLA.attribute24, PESLA.attribute25, PESLA.attribute26,
1499 PESLA.attribute27, PESLA.attribute28, PESLA.attribute29,
1500 PESLA.attribute30, PESLA.reject_reason_code, PESLA.enc_control_id,
1501 PESLA.status_code, PESLA.last_update_date, PESLA.last_updated_by,
1502 PESLA.last_update_login, PESLA.created_by, PESLA.creation_date,
1503 PESLA.suspense_org_account_id, PESLA.superceded_line_id, PESLA.gms_posting_override_date,
1504 PESLA.gl_posting_override_date,
1505 PESLA.expenditure_id, PESLA.expenditure_item_id, --added five exp columns for 2445196
1506 PESLA.expenditure_ending_date, PESLA.interface_id,
1507 PESLA.txn_interface_id, payroll_action_id, liquidate_request_id, proposed_termination_date, update_flag
1508
1509 FROM PSP_ENC_SUMMARY_LINES_ARCH PESLA
1510 WHERE PESLA.time_period_id = l_time_period;
1511
1512 -- Purge the archived data
1513 -- Delete from PSP_ENC_LINES_ARCH for the current period
1514 DELETE PSP_ENC_LINES_ARCH
1515 WHERE time_period_id = l_time_period;
1516
1517 -- Delete from PSP_ENC_SUMMARY_LINES_ARCH for the current period
1518 DELETE PSP_ENC_SUMMARY_LINES_ARCH
1519 WHERE time_period_id = l_time_period;
1520
1521 -- Update the status of archive_flag in PSP_ENC_CONTROLS to NULL
1522 Update PSP_ENC_CONTROLS
1523 Set archive_flag = NULL
1524 WHERE time_period_id = l_time_period;
1525
1526 -- Commit the changes made for thecurrent period
1527 COMMIT;
1528
1529 -- Update the log with with current periods message
1530 -- FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_EN_ARCHIVE_PERIOD'); --For Bug 2783253
1531 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_EN_RETRIEVE_PERIOD'); --For Bug 2783253
1532 FND_MESSAGE.SET_TOKEN('PAYROLL_NAME', l_payroll_name);
1533 FND_MESSAGE.SET_TOKEN('TIME_PERIOD', l_period_name);
1534 fnd_msg_pub.add;
1535
1536 -- Continue with the next period
1537 End Loop;
1538
1539 -- Set the status flag to 'Successfull' as all the periods have been successfully archived
1540 l_status:='Successful';
1541
1542 -- Print the end of process message to the log
1543 FND_MESSAGE.SET_NAME('PSP', 'PSP_ARC_ARCHIVE_RETRIEVE_END');
1544 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_process_type);
1545 FND_MESSAGE.SET_TOKEN('LINES_TYPE',l_lines_type);
1546 FND_MESSAGE.SET_TOKEN('STATUS',l_status);
1547 fnd_msg_pub.add;
1548
1549 -- Write all messages into Log
1550 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1551 p_print_header => FND_API.G_FALSE);
1552 retcode := 0;
1553
1554 --Exception handling starts here
1555 Exception
1556 -- For any other kind of Exception, ROLLBACK
1557 When OTHERS Then
1558 /* Folllowing is added for bug 2482603 */
1559 g_error_api_path := SUBSTR('RETRIEVE_ENCUMBRANCE '||g_error_api_path,1,230);
1560 fnd_msg_pub.add_exc_msg('PSP_ARCHIVE_RETRIEVE',g_error_api_path);
1561 psp_message_s.print_error( p_mode => FND_FILE.LOG,
1562 p_print_header => FND_API.G_TRUE);
1563 retcode := 2;
1564 End retrieve_encumbrance;
1565
1566 /****************************************************************************************
1567 Created By : Jabez/ Tested By Narayana.
1568
1569 Date Created By : 24-Feb-2012
1570
1571 Purpose : This procedure is used to archive pre gen dist lines from the original table
1572 to the archive table with a new archival code name ,in order to
1573 track the archived data.
1574
1575 Know limitations, enhancements or remarks :
1576
1577 Change History :
1578
1579 ****************************************************************************************/
1580
1581 PROCEDURE Archive_distribution_int (errbuf OUT NOCOPY VARCHAR2,
1582 retcode OUT NOCOPY VARCHAR2,
1583 p_archival_code IN VARCHAR2, -- for user to remember the request easily, map this to request_id internally
1584 p_begin_date IN VARCHAR2,
1585 p_end_date IN VARCHAR2) IS
1586
1587 --Cursor to pick the batch_names,the no of records to be archived , min distribution date and max distribution date which falls between the start date and the end date of the archive process
1588
1589 CURSOR batch_name_cur(p_begin_date VARCHAR2,
1590 p_end_date VARCHAR2,
1591 l_business_group_id NUMBER,
1592 l_set_of_books_id NUMBER) IS
1593 SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N2)*/
1594 batch_name
1595 , count (*)
1596 , min (distribution_date)
1597 , max (distribution_date)
1598 FROM psp_distribution_interface pdi
1599 WHERE pdi.distribution_date
1600 BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1601 AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1602 AND business_group_id = l_business_group_id
1603 AND set_of_books_id = l_set_of_books_id
1604 AND NOT EXISTS ---- See is there is atleast one record that is falling out of the period specified OR not yet transferred for the batch selected above
1605 (
1606 SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1607 1
1608 FROM psp_distribution_interface pdi2
1609 WHERE pdi2.batch_name = pdi.batch_name
1610 AND (
1611 pdi2.distribution_date
1612 NOT BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1613 AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1614 OR pdi2.status_code <> 'T'
1615 )
1616 )
1617 GROUP BY batch_name;
1618
1619
1620 --Cursor to pick the records of the BATCH selected
1621
1622 CURSOR batch_cur(p_batch_name VARCHAR2,
1623 l_business_group_id NUMBER,
1624 l_set_of_books_id NUMBER) IS
1625 SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N2)*/
1626 rowid
1627 , distribution_interface_id
1628 , person_id
1629 , assignment_id
1630 , element_type_id
1631 , distribution_date
1632 , distribution_amount
1633 , payroll_id
1634 , time_period_id
1635 , dr_cr_flag
1636 , source_code
1637 , gl_code_combination_id
1638 , project_id
1639 , expenditure_organization_id
1640 , expenditure_type
1641 , task_id
1642 , award_id
1643 , status_code
1644 , batch_name
1645 , error_code
1646 , attribute_category
1647 , attribute1
1648 , attribute2
1649 , attribute3
1650 , attribute4
1651 , attribute5
1652 , attribute6
1653 , attribute7
1654 , attribute8
1655 , attribute9
1656 , attribute10
1657 , attribute11
1658 , attribute12
1659 , attribute13
1660 , attribute14
1661 , attribute15
1662 , last_update_date
1663 , last_updated_by
1664 , last_update_login
1665 , created_by
1666 , creation_date
1667 , gl_posting_override_date
1668 , gms_posting_override_date
1669 , business_group_id
1670 , set_of_books_id
1671 , suspense_org_account_id
1672 , currency_code
1673 , suspense_auto_glccid
1674 , suspense_auto_exp_type
1675 FROM psp_distribution_interface pdi
1676 WHERE pdi.batch_name LIKE p_batch_name
1677 AND pdi.distribution_date
1678 BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1679 AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1680 AND business_group_id = l_business_group_id
1681 AND set_of_books_id = l_set_of_books_id
1682 AND NOT EXISTS
1683 (
1684 SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1685 1
1686 FROM psp_distribution_interface pdi2
1687 WHERE pdi2.batch_name = pdi.batch_name
1688 AND (
1689 pdi2.distribution_date
1690 NOT BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1691 AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1692 OR pdi2.status_code <> 'T'
1693 )
1694 );
1695
1696 l_valid_batch_exists BOOLEAN := false;
1697
1698 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1699 TYPE t_varchar_50_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
1700 TYPE t_varchar_150_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1701 TYPE t_num_10d2_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1702 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1703 TYPE t_num_15d2_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1704
1705 TYPE t_rowid_tab_type IS TABLE OF ROWID;
1706 TYPE t_batch_name_type IS TABLE OF VARCHAR2(50);
1707
1708 -- Record type to store BATCH information
1709 TYPE batch_name_rec_type IS RECORD (batch_name T_BATCH_NAME_TYPE,batch_size T_NUM_15_TYPE,min_dist_date T_DATE_TYPE,max_dist_date T_DATE_TYPE);
1710 batch_name_rec BATCH_NAME_REC_TYPE;
1711
1712
1713 -- Record type to store records of a BATCH
1714 TYPE dist_int_rec_type IS RECORD
1715 (
1716 t_row_id T_ROWID_TAB_TYPE
1717 ,t_distribution_interface_id T_NUM_15_TYPE
1718 ,t_person_id T_NUM_15_TYPE
1719 ,t_assignment_id T_NUM_15_TYPE
1720 ,t_element_type_id T_NUM_15_TYPE
1721 ,t_distribution_date T_DATE_TYPE
1722 ,t_distribution_amount T_NUM_10D2_TYPE
1723 ,t_payroll_id T_NUM_15_TYPE
1724 ,t_time_period_id T_NUM_15_TYPE
1725 ,t_dr_cr_flag T_VARCHAR_50_TYPE
1726 ,t_source_code T_VARCHAR_50_TYPE
1727 ,t_gl_code_combination_id T_NUM_15_TYPE
1728 ,t_project_id T_NUM_15_TYPE
1729 ,t_expenditure_organization_id T_NUM_15_TYPE
1730 ,t_expenditure_type T_VARCHAR_50_TYPE
1731 ,t_task_id T_NUM_15_TYPE
1732 ,t_award_id T_NUM_15_TYPE
1733 ,t_status_code T_VARCHAR_50_TYPE
1734 ,t_batch_name T_VARCHAR_50_TYPE
1735 ,t_error_code T_VARCHAR_50_TYPE
1736 ,t_attribute_category T_VARCHAR_50_TYPE
1737 ,t_attribute1 T_VARCHAR_150_TYPE
1738 ,t_attribute2 T_VARCHAR_150_TYPE
1739 ,t_attribute3 T_VARCHAR_150_TYPE
1740 ,t_attribute4 T_VARCHAR_150_TYPE
1741 ,t_attribute5 T_VARCHAR_150_TYPE
1742 ,t_attribute6 T_VARCHAR_150_TYPE
1743 ,t_attribute7 T_VARCHAR_150_TYPE
1744 ,t_attribute8 T_VARCHAR_150_TYPE
1745 ,t_attribute9 T_VARCHAR_150_TYPE
1746 ,t_attribute10 T_VARCHAR_150_TYPE
1747 ,t_attribute11 T_VARCHAR_150_TYPE
1748 ,t_attribute12 T_VARCHAR_150_TYPE
1749 ,t_attribute13 T_VARCHAR_150_TYPE
1750 ,t_attribute14 T_VARCHAR_150_TYPE
1751 ,t_attribute15 T_VARCHAR_150_TYPE
1752 ,t_last_update_date T_DATE_TYPE
1753 ,t_last_updated_by T_NUM_15_TYPE
1754 ,t_last_update_login T_NUM_15_TYPE
1755 ,t_created_by T_NUM_15_TYPE
1756 ,t_creation_date T_DATE_TYPE
1757 ,t_gl_posting_override_date T_DATE_TYPE
1758 ,t_gms_posting_override_date T_DATE_TYPE
1759 ,t_business_group_id T_NUM_15_TYPE
1760 ,t_set_of_books_id T_NUM_15_TYPE
1761 ,t_suspense_org_account_id T_NUM_15_TYPE
1762 ,t_currency_code T_VARCHAR_50_TYPE
1763 ,t_suspense_auto_glccid T_NUM_15_TYPE
1764 ,t_suspense_auto_exp_type T_VARCHAR_50_TYPE
1765 );
1766 r_batch_rec DIST_INT_REC_TYPE;
1767
1768
1769 g_bulk_row_count NUMBER;
1770 l_request_id NUMBER;
1771 l_archived_batch_size NUMBER := 0;
1772 l_count NUMBER := 0;
1773 l_record_count NUMBER;
1774 l_business_group_id NUMBER;
1775 l_set_of_books_id NUMBER;
1776 l_original_size NUMBER := 0;
1777
1778
1779 BEGIN
1780
1781 hr_general.g_data_migrator_mode := 'Y';
1782
1783 g_debug_enabled := hr_utility.Debug_enabled();
1784
1785 IF g_debug_enabled THEN
1786 hr_utility.Trace('Entering DI Archive');
1787 END IF;
1788
1789 SELECT To_number(fnd_profile.VALUE ('PER_BUSINESS_GROUP_ID'))
1790 INTO l_business_group_id
1791 FROM dual;
1792
1793 SELECT To_number(fnd_profile.VALUE ('GL_SET_OF_BKS_ID'))
1794 INTO l_set_of_books_id
1795 FROM dual;
1796
1797 IF g_debug_enabled THEN
1798 hr_utility.Trace('Business_group_id '||l_business_group_id);
1799 hr_utility.Trace('Set_of_books_id '||l_set_of_books_id);
1800 END IF;
1801
1802 fnd_file.Put_line(fnd_file.log,'-----------------------------------------------------------------------');
1803 fnd_file.Put_line(fnd_file.log,' Archiving Pre-Gen Distribution Interface for the following parameters ');
1804 fnd_file.Put_line(fnd_file.log,' Begin date of the batch: '||p_begin_date);
1805 fnd_file.Put_line(fnd_file.log,' End date of the batch: '||p_end_date);
1806 fnd_file.Put_line(fnd_file.log,' Archival Code: '||p_archival_code);
1807 fnd_file.Put_line(fnd_file.log,' Business_group_id: '||l_business_group_id);
1808 fnd_file.Put_line(fnd_file.log,' Set_of_books_id: '||l_set_of_books_id);
1809 fnd_file.Put_line(fnd_file.log,'-----------------------------------------------------------------------');
1810
1811
1812 -- Picks the batch informations i.e batch_name, batch size, min distribution date and max distribution date basing on the start date and end date of the archival process.
1813
1814 OPEN batch_name_cur(p_begin_date,p_end_date,l_business_group_id,l_set_of_books_id);
1815 FETCH batch_name_cur BULK COLLECT INTO batch_name_rec.batch_name,batch_name_rec.batch_size,batch_name_rec.min_dist_date,batch_name_rec.max_dist_date;
1816 CLOSE batch_name_cur;
1817
1818 IF g_debug_enabled THEN
1819 hr_utility.Trace('After - batch_name_rec.batch_name.count: '||batch_name_rec.batch_name.COUNT);
1820 END IF;
1821
1822 fnd_file.Put_line(fnd_file.log,'The following '||batch_name_rec.batch_name.COUNT||' batches are selected for archival');
1823
1824 -- To display the batch names which were selected for archival process.
1825
1826 FOR i IN 1..batch_name_rec.batch_name.COUNT
1827 LOOP
1828 IF g_debug_enabled THEN
1829 hr_utility.Trace('Batch: '||batch_name_rec.Batch_name(i));
1830 END IF;
1831 fnd_file.Put_line(fnd_file.log,' '||i||' - '||batch_name_rec.Batch_name(i));
1832 END LOOP;
1833
1834
1835 IF batch_name_rec.batch_name.COUNT = 0
1836 THEN
1837 IF g_debug_enabled THEN
1838 hr_utility.Trace('No Batches to process');
1839 END IF;
1840 fnd_file.Put_line(fnd_file.log,'No Valid batches to archive for period '||p_begin_date||' - '||p_end_date);
1841 RETURN; --Control returns back if there are no batches to be archived.
1842 END IF;
1843
1844 l_request_id := fnd_global.conc_request_id;
1845
1846 fnd_file.Put_line(fnd_file.log,'--------------------------------------------------------------------------------------------------------------------');
1847 fnd_file.Put_line(fnd_file.log,' Batch Name Minimum Distribution Date Maximum Distribution Date Batch Size');
1848 fnd_file.Put_line(fnd_file.log,'---------------------------------------------------------------------------------------------------------------------');
1849
1850 FOR j IN batch_name_rec.batch_name.first..batch_name_rec.batch_name.last
1851 LOOP
1852 IF batch_name_rec.batch_name.EXISTS(j) THEN
1853 IF g_debug_enabled THEN
1854 hr_utility.Trace(j||' - Processing Batch: '||batch_name_rec.Batch_name(j));
1855 END IF;
1856
1857 -- Creates a control record in the control table for EVERY BATCH with the request id,archival_code,batch_size,archival dates and the distribution dates.
1858 -- Archival Process dates --> start_date and end_date
1859 -- Distribution Dates within a batch --> batch_name_rec.Min_dist_date and batch_name_rec.Max_dist_date
1860
1861 INSERT
1862 INTO psp_arch_ret_requests (request_id
1863 , start_date
1864 , end_date
1865 , arch_ret_flag
1866 , batch_name
1867 , archival_code
1868 , batch_size
1869 , min_dist_date
1870 , max_dist_date)
1871 VALUES (l_request_id
1872 , fnd_date.canonical_to_date (p_begin_date)
1873 , fnd_date.canonical_to_date (p_end_date)
1874 , NULL
1875 , batch_name_rec.batch_name (j)
1876 , p_archival_code
1877 , batch_name_rec.batch_size (j)
1878 , batch_name_rec.min_dist_date (j)
1879 , batch_name_rec.max_dist_date (j));
1880
1881 /* Cursor is opened and the following process is handled for EACH BATCH
1882
1883 - Fetches data for each batch with a limit of 500 records
1884 - Inserted data into archive table and then deletes from the main table.
1885 - Updates the no of records archived in the control table.
1886 - Commits partially for every 500 archived records.
1887
1888 */
1889
1890 OPEN batch_cur(batch_name_rec.Batch_name(j),l_business_group_id ,l_set_of_books_id );
1891 LOOP
1892
1893 --Fetches data from main table
1894
1895 FETCH batch_cur BULK COLLECT INTO
1896 r_batch_rec.t_row_id
1897 ,r_batch_rec.t_distribution_interface_id
1898 ,r_batch_rec.t_person_id
1899 ,r_batch_rec.t_assignment_id
1900 ,r_batch_rec.t_element_type_id
1901 ,r_batch_rec.t_distribution_date
1902 ,r_batch_rec.t_distribution_amount
1903 ,r_batch_rec.t_payroll_id
1904 ,r_batch_rec.t_time_period_id
1905 ,r_batch_rec.t_dr_cr_flag
1906 ,r_batch_rec.t_source_code
1907 ,r_batch_rec.t_gl_code_combination_id
1908 ,r_batch_rec.t_project_id
1909 ,r_batch_rec.t_expenditure_organization_id
1910 ,r_batch_rec.t_expenditure_type
1911 ,r_batch_rec.t_task_id
1912 ,r_batch_rec.t_award_id
1913 ,r_batch_rec.t_status_code
1914 ,r_batch_rec.t_batch_name
1915 ,r_batch_rec.t_error_code
1916 ,r_batch_rec.t_attribute_category
1917 ,r_batch_rec.t_attribute1
1918 ,r_batch_rec.t_attribute2
1919 ,r_batch_rec.t_attribute3
1920 ,r_batch_rec.t_attribute4
1921 ,r_batch_rec.t_attribute5
1922 ,r_batch_rec.t_attribute6
1923 ,r_batch_rec.t_attribute7
1924 ,r_batch_rec.t_attribute8
1925 ,r_batch_rec.t_attribute9
1926 ,r_batch_rec.t_attribute10
1927 ,r_batch_rec.t_attribute11
1928 ,r_batch_rec.t_attribute12
1929 ,r_batch_rec.t_attribute13
1930 ,r_batch_rec.t_attribute14
1931 ,r_batch_rec.t_attribute15
1932 ,r_batch_rec.t_last_update_date
1933 ,r_batch_rec.t_last_updated_by
1934 ,r_batch_rec.t_last_update_login
1935 ,r_batch_rec.t_created_by
1936 ,r_batch_rec.t_creation_date
1937 ,r_batch_rec.t_gl_posting_override_date
1938 ,r_batch_rec.t_gms_posting_override_date
1939 ,r_batch_rec.t_business_group_id
1940 ,r_batch_rec.t_set_of_books_id
1941 ,r_batch_rec.t_suspense_org_account_id
1942 ,r_batch_rec.t_currency_code
1943 ,r_batch_rec.t_suspense_auto_glccid
1944 ,r_batch_rec.t_suspense_auto_exp_type
1945 LIMIT 500;
1946
1947 IF g_debug_enabled THEN
1948 hr_utility.Trace('count: '||r_batch_rec.t_distribution_interface_id.COUNT);
1949 END IF;
1950
1951 exit WHEN r_batch_rec.t_distribution_interface_id.COUNT = 0;
1952
1953 -- Transferring data to archive table
1954
1955 FORALL k IN 1 .. r_batch_rec.t_distribution_interface_id.COUNT
1956 INSERT
1957 INTO psp_distribution_int_arch (distribution_interface_id
1958 , person_id
1959 , assignment_id
1960 , element_type_id
1961 , distribution_date
1962 , distribution_amount
1963 , payroll_id
1964 , time_period_id
1965 , dr_cr_flag
1966 , source_code
1967 , gl_code_combination_id
1968 , project_id
1969 , expenditure_organization_id
1970 , expenditure_type
1971 , task_id
1972 , award_id
1973 , status_code
1974 , batch_name
1975 , error_code
1976 , attribute_category
1977 , attribute1
1978 , attribute2
1979 , attribute3
1980 , attribute4
1981 , attribute5
1982 , attribute6
1983 , attribute7
1984 , attribute8
1985 , attribute9
1986 , attribute10
1987 , attribute11
1988 , attribute12
1989 , attribute13
1990 , attribute14
1991 , attribute15
1992 , last_update_date
1993 , last_updated_by
1994 , last_update_login
1995 , created_by
1996 , creation_date
1997 , gl_posting_override_date
1998 , gms_posting_override_date
1999 , business_group_id
2000 , set_of_books_id
2001 , suspense_org_account_id
2002 , currency_code
2003 , suspense_auto_glccid
2004 , suspense_auto_exp_type
2005 , request_id)
2006 VALUES (r_batch_rec.t_distribution_interface_id (k)
2007 , r_batch_rec.t_person_id (k)
2008 , r_batch_rec.t_assignment_id (k)
2009 , r_batch_rec.t_element_type_id (k)
2010 , r_batch_rec.t_distribution_date (k)
2011 , r_batch_rec.t_distribution_amount (k)
2012 , r_batch_rec.t_payroll_id (k)
2013 , r_batch_rec.t_time_period_id (k)
2014 , r_batch_rec.t_dr_cr_flag (k)
2015 , r_batch_rec.t_source_code (k)
2016 , r_batch_rec.t_gl_code_combination_id (k)
2017 , r_batch_rec.t_project_id (k)
2018 , r_batch_rec.t_expenditure_organization_id (k)
2019 , r_batch_rec.t_expenditure_type (k)
2020 , r_batch_rec.t_task_id (k)
2021 , r_batch_rec.t_award_id (k)
2022 , r_batch_rec.t_status_code (k)
2023 , r_batch_rec.t_batch_name (k)
2024 , r_batch_rec.t_error_code (k)
2025 , r_batch_rec.t_attribute_category (k)
2026 , r_batch_rec.t_attribute1 (k)
2027 , r_batch_rec.t_attribute2 (k)
2028 , r_batch_rec.t_attribute3 (k)
2029 , r_batch_rec.t_attribute4 (k)
2030 , r_batch_rec.t_attribute5 (k)
2031 , r_batch_rec.t_attribute6 (k)
2032 , r_batch_rec.t_attribute7 (k)
2033 , r_batch_rec.t_attribute8 (k)
2034 , r_batch_rec.t_attribute9 (k)
2035 , r_batch_rec.t_attribute10 (k)
2036 , r_batch_rec.t_attribute11 (k)
2037 , r_batch_rec.t_attribute12 (k)
2038 , r_batch_rec.t_attribute13 (k)
2039 , r_batch_rec.t_attribute14 (k)
2040 , r_batch_rec.t_attribute15 (k)
2041 , r_batch_rec.t_last_update_date (k)
2042 , r_batch_rec.t_last_updated_by (k)
2043 , r_batch_rec.t_last_update_login (k)
2044 , r_batch_rec.t_created_by (k)
2045 , r_batch_rec.t_creation_date (k)
2046 , r_batch_rec.t_gl_posting_override_date (k)
2047 , r_batch_rec.t_gms_posting_override_date (k)
2048 , r_batch_rec.t_business_group_id (k)
2049 , r_batch_rec.t_set_of_books_id (k)
2050 , r_batch_rec.t_suspense_org_account_id (k)
2051 , r_batch_rec.t_currency_code (k)
2052 , r_batch_rec.t_suspense_auto_glccid (k)
2053 , r_batch_rec.t_suspense_auto_exp_type (k)
2054 , l_request_id);
2055
2056
2057 g_bulk_row_count :=0;
2058 FOR bulk_idx IN 1..r_batch_rec.t_distribution_interface_id.COUNT
2059 LOOP
2060 g_bulk_row_count := g_bulk_row_count + SQL%bulk_rowcount(bulk_idx);
2061 END LOOP;
2062
2063 IF g_debug_enabled THEN
2064 hr_utility.Trace('g_bulk_row_count: '||g_bulk_row_count);
2065 END IF;
2066
2067 -- Deletion of archived data from original table
2068
2069 FORALL k IN 1 .. r_batch_rec.t_distribution_interface_id.COUNT
2070 DELETE FROM psp_distribution_interface
2071 WHERE rowid = r_batch_rec.t_row_id (k);
2072
2073 -- Updating the count of archived data for the batch in the control table
2074
2075 UPDATE psp_arch_ret_requests
2076 SET archived_batch_size = Nvl(archived_batch_size,0)+g_bulk_row_count
2077 WHERE request_id = l_request_id
2078 AND batch_name LIKE batch_name_rec.Batch_name(j);
2079
2080 IF g_debug_enabled THEN
2081 hr_utility.Trace('update psp_arch_ret_requests count: '||SQL%rowcount);
2082 END IF;
2083
2084 COMMIT; -- Committing partial batch
2085
2086 END LOOP;
2087
2088 CLOSE batch_cur;
2089
2090
2091
2092 IF g_debug_enabled THEN
2093 hr_utility.Trace(j||' - Successfully processed batch: '|| batch_name_rec.Batch_name(j));
2094 END IF;
2095
2096 --Once the complete data is archived the corresponding BATCH is updated as A.
2097
2098 UPDATE psp_arch_ret_requests
2099 SET arch_ret_flag = 'A'
2100 WHERE request_id = l_request_id
2101 AND batch_name = batch_name_rec.Batch_name(j);
2102
2103 IF g_debug_enabled THEN
2104 hr_utility.Trace('Updated psp_arch_ret_requests setting to A: '||SQL%rowcount);
2105 END IF;
2106
2107 -- Below query helps to validate whether the complete data is archived or not
2108
2109 SELECT archived_batch_size,batch_size
2110 INTO l_archived_batch_size,l_original_size
2111 FROM psp_arch_ret_requests
2112 WHERE request_id = l_request_id
2113 AND batch_name = batch_name_rec.Batch_name(j);
2114
2115
2116 IF l_original_size = l_archived_batch_size THEN
2117 fnd_file.Put_line(fnd_file.log,j||'. '||batch_name_rec.Batch_name(j)||' '||batch_name_rec.Min_dist_date(j)||' '||batch_name_rec.Max_dist_date(j)||' '||l_original_size);
2118 ELSE
2119 fnd_file.Put_line(fnd_file.log,j||'. '||batch_name_rec.Batch_name(j)||' '||batch_name_rec.Min_dist_date(j)||' '||batch_name_rec.Max_dist_date(j)||' '||l_original_size|| ' Archival Incomplete');
2120 END IF;
2121
2122
2123 COMMIT; -- Commit updates
2124 END IF;
2125 END LOOP;
2126 fnd_file.Put_line(fnd_file.log,'---------------------------------------------------------------------------------------------------------------------');
2127
2128
2129 fnd_file.Put_line(fnd_file.log,' Archive Distribtuion Interface processed successfully');
2130 IF g_debug_enabled THEN
2131 hr_utility.Trace('Archive Distribtuion Interface processed successfully ');
2132 END IF;
2133
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136
2137 fnd_file.Put_line(fnd_file.log,dbms_utility.format_error_backtrace);
2138 fnd_file.Put_line(fnd_file.log,'ARCHIVE_DISTRIBUTION_INT---> exception '||sqlerrm);
2139
2140 END archive_distribution_int;
2141
2142
2143 /****************************************************************************************
2144 Created By : Jabez/Tested By Narayana
2145
2146 Date Created By : 24-Feb-2012
2147
2148 Purpose : This procedure is to retrieve pre gen dist lines from the archive table and
2149 move them back to the the original table based on the archival code used during the
2150 archival process.
2151
2152 Know limitations, enhancements or remarks :
2153
2154 Change History :
2155
2156 ****************************************************************************************/
2157 PROCEDURE Retrieve_distribution_int (errbuf OUT NOCOPY VARCHAR2,
2158 retcode OUT NOCOPY VARCHAR2,
2159 p_arch_request_id IN NUMBER) IS -- Get the request_id corresponding to the archival code entered
2160
2161 -- Cursor selects batches based on the arch_request_id in order to move the data back to its original table
2162 CURSOR batch_name_cur(p_arch_request_id NUMBER) IS
2163 SELECT batch_name
2164 , batch_size
2165 , min_dist_date
2166 , max_dist_date
2167 FROM psp_arch_ret_requests
2168 WHERE request_id = p_arch_request_id;
2169
2170 -- Cursor selects the archived records from the interface table for the batches which were picked based on the arch request id.
2171
2172 CURSOR batch_cur(p_batch_name VARCHAR2) IS
2173 SELECT/*+INDEX (pdia PSP_DISTRIBUTION_INT_ARCH_N1)*/ pdia.ROWID
2174 ,distribution_interface_id
2175 ,person_id
2176 ,assignment_id
2177 ,element_type_id
2178 ,distribution_date
2179 ,distribution_amount
2180 ,payroll_id
2181 ,time_period_id
2182 ,dr_cr_flag
2183 ,source_code
2184 ,gl_code_combination_id
2185 ,project_id
2186 ,expenditure_organization_id
2187 ,expenditure_type
2188 ,task_id
2189 ,award_id
2190 ,status_code
2191 ,pdia.batch_name
2192 ,error_code
2193 ,attribute_category
2194 ,attribute1
2195 ,attribute2
2196 ,attribute3
2197 ,attribute4
2198 ,attribute5
2199 ,attribute6
2200 ,attribute7
2201 ,attribute8
2202 ,attribute9
2203 ,attribute10
2204 ,attribute11
2205 ,attribute12
2206 ,attribute13
2207 ,attribute14
2208 ,attribute15
2209 ,last_update_date
2210 ,last_updated_by
2211 ,last_update_login
2212 ,created_by
2213 ,creation_date
2214 ,gl_posting_override_date
2215 ,gms_posting_override_date
2216 ,business_group_id
2217 ,set_of_books_id
2218 ,suspense_org_account_id
2219 ,currency_code
2220 ,suspense_auto_glccid
2221 ,suspense_auto_exp_type
2222 FROM psp_distribution_int_arch pdia,psp_arch_ret_requests parr
2223 WHERE parr.request_id = p_arch_request_id
2224 AND parr.batch_name = p_batch_name
2225 AND pdia.request_id = parr.request_id
2226 AND pdia.batch_name = parr.batch_name;
2227
2228 CURSOR batch_counts_cur(p_batch_name VARCHAR2) IS
2229 SELECT archived_batch_size
2230 , batch_size
2231 FROM psp_arch_ret_requests
2232 WHERE request_id = p_arch_request_id
2233 AND batch_name = p_batch_name;
2234
2235 l_valid_batch_exists BOOLEAN := false;
2236
2237
2238 TYPE batch_tab_type IS TABLE OF psp_distribution_int_arch%ROWTYPE INDEX BY PLS_INTEGER;
2239
2240
2241 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2242 TYPE t_varchar_50_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
2243 TYPE t_varchar_150_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
2244 TYPE t_num_10d2_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2245 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2246 TYPE t_num_15d2_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2247
2248
2249 TYPE t_batch_name_type IS TABLE OF psp_distribution_int_arch.batch_name%TYPE INDEX BY PLS_INTEGER;
2250
2251 -- Record type to store batch information
2252 TYPE batch_name_rec_type IS RECORD (batch_name T_BATCH_NAME_TYPE,batch_size T_NUM_15_TYPE,min_dist_date T_DATE_TYPE,max_dist_date T_DATE_TYPE);
2253
2254 batch_name_rec BATCH_NAME_REC_TYPE;
2255
2256 l_remaining_records NUMBER := 0;
2257 l_original_batch_size NUMBER := 0;
2258
2259 TYPE t_rowid_tab_type IS TABLE OF ROWID;
2260
2261 -- Record type to store records of the archive table corresponding to the batch selected
2262 TYPE dist_int_rec_type IS RECORD
2263 (
2264 t_row_id T_ROWID_TAB_TYPE
2265 ,t_distribution_interface_id T_NUM_15_TYPE
2266 ,t_person_id T_NUM_15_TYPE
2267 ,t_assignment_id T_NUM_15_TYPE
2268 ,t_element_type_id T_NUM_15_TYPE
2269 ,t_distribution_date T_DATE_TYPE
2270 ,t_distribution_amount T_NUM_10D2_TYPE
2271 ,t_payroll_id T_NUM_15_TYPE
2272 ,t_time_period_id T_NUM_15_TYPE
2273 ,t_dr_cr_flag T_VARCHAR_50_TYPE
2274 ,t_source_code T_VARCHAR_50_TYPE
2275 ,t_gl_code_combination_id T_NUM_15_TYPE
2276 ,t_project_id T_NUM_15_TYPE
2277 ,t_expenditure_organization_id T_NUM_15_TYPE
2278 ,t_expenditure_type T_VARCHAR_50_TYPE
2279 ,t_task_id T_NUM_15_TYPE
2280 ,t_award_id T_NUM_15_TYPE
2281 ,t_status_code T_VARCHAR_50_TYPE
2282 ,t_batch_name T_VARCHAR_50_TYPE
2283 ,t_error_code T_VARCHAR_50_TYPE
2284 ,t_attribute_category T_VARCHAR_50_TYPE
2285 ,t_attribute1 T_VARCHAR_150_TYPE
2286 ,t_attribute2 T_VARCHAR_150_TYPE
2287 ,t_attribute3 T_VARCHAR_150_TYPE
2288 ,t_attribute4 T_VARCHAR_150_TYPE
2289 ,t_attribute5 T_VARCHAR_150_TYPE
2290 ,t_attribute6 T_VARCHAR_150_TYPE
2291 ,t_attribute7 T_VARCHAR_150_TYPE
2292 ,t_attribute8 T_VARCHAR_150_TYPE
2293 ,t_attribute9 T_VARCHAR_150_TYPE
2294 ,t_attribute10 T_VARCHAR_150_TYPE
2295 ,t_attribute11 T_VARCHAR_150_TYPE
2296 ,t_attribute12 T_VARCHAR_150_TYPE
2297 ,t_attribute13 T_VARCHAR_150_TYPE
2298 ,t_attribute14 T_VARCHAR_150_TYPE
2299 ,t_attribute15 T_VARCHAR_150_TYPE
2300 ,t_last_update_date T_DATE_TYPE
2301 ,t_last_updated_by T_NUM_15_TYPE
2302 ,t_last_update_login T_NUM_15_TYPE
2303 ,t_created_by T_NUM_15_TYPE
2304 ,t_creation_date T_DATE_TYPE
2305 ,t_gl_posting_override_date T_DATE_TYPE
2306 ,t_gms_posting_override_date T_DATE_TYPE
2307 ,t_business_group_id T_NUM_15_TYPE
2308 ,t_set_of_books_id T_NUM_15_TYPE
2309 ,t_suspense_org_account_id T_NUM_15_TYPE
2310 ,t_currency_code T_VARCHAR_50_TYPE
2311 ,t_suspense_auto_glccid T_NUM_15_TYPE
2312 ,t_suspense_auto_exp_type T_VARCHAR_50_TYPE
2313 );
2314 r_batch_rec DIST_INT_REC_TYPE;
2315
2316
2317 g_bulk_row_count NUMBER;
2318 l_request_id NUMBER;
2319
2320 l_begin_date DATE;
2321 l_end_date DATE;
2322 l_archival_code VARCHAR2(30);
2323
2324
2325 BEGIN
2326
2327 hr_general.g_data_migrator_mode := 'Y';
2328
2329 g_debug_enabled := hr_utility.Debug_enabled();
2330
2331 IF g_debug_enabled THEN
2332 hr_utility.Trace('Entering DI Retrieve');
2333 END IF;
2334
2335
2336 -- Selects archival code information which is same for all the batchs for the corresponding arch request id
2337
2338 SELECT start_date, end_date, archival_code
2339 INTO l_begin_date,l_end_date,l_archival_code
2340 FROM psp_arch_ret_requests
2341 WHERE request_id = p_arch_request_id
2342 AND rownum < 2;
2343
2344 fnd_file.Put_line(fnd_file.log,'Retrieve Distribution Interface for Archival Code: '||l_archival_code|| ' for Start Date: '||l_begin_date||' and End Date: '||l_end_date);
2345
2346 -- Initially the arch_ret_flag will be A, before the retrieval process begins the flag will change to R for all the batches of the request
2347
2348 UPDATE psp_arch_ret_requests
2349 SET arch_ret_flag = 'R' -- Locking the batches to be retrieved
2350 WHERE request_id = p_arch_request_id;
2351
2352 IF g_debug_enabled THEN
2353 hr_utility.Trace('updated batches in psp_arch_ret_requests set to R - count: '||SQL%rowcount);
2354 END IF;
2355
2356 COMMIT;
2357
2358 -- Picks the batch information i.e batch_name, batch size, min distribution date and max distribution date basing request id
2359
2360 OPEN batch_name_cur(p_arch_request_id);
2361 FETCH batch_name_cur BULK COLLECT INTO batch_name_rec.batch_name,batch_name_rec.batch_size,batch_name_rec.min_dist_date,batch_name_rec.max_dist_date;
2362 CLOSE batch_name_cur;
2363
2364 fnd_file.Put_line(fnd_file.log,'The following '||batch_name_rec.batch_name.COUNT||' batches are selected for Retrieval'); --13923287
2365
2366
2367 FOR i IN 1..batch_name_rec.batch_name.COUNT
2368 LOOP
2369 IF g_debug_enabled THEN
2370 hr_utility.Trace('Batch: '||batch_name_rec.Batch_name(i));
2371 END IF;
2372 fnd_file.Put_line(fnd_file.log,' '||i||' - '||batch_name_rec.Batch_name(i));
2373 END LOOP;
2374
2375
2376
2377 fnd_file.Put_line(fnd_file.log,'--------------------------------------------------------------------------------------------------------------------');
2378 fnd_file.Put_line(fnd_file.log,' Batch Name Minimum Distribution Date Maximum Distribution Date Batch Size');
2379 fnd_file.Put_line(fnd_file.log,'---------------------------------------------------------------------------------------------------------------------');
2380
2381
2382 FOR i IN 1..batch_name_rec.batch_name.COUNT
2383 LOOP
2384
2385
2386 /* Cursor batch_cur is opened and the following process is handled for EACH BATCH
2387
2388 - Fetches data for each batch with a limit of 500 records from the archive table
2389 - Inserted data into main table and then deletes from the main table.
2390 - Updates the no of records in the control table
2391 - Commits partially for every 500 records.
2392
2393 */
2394 OPEN batch_cur(batch_name_rec.Batch_name(i)) ; -- get records for this p_arch_request_id
2395 LOOP
2396
2397
2398 FETCH batch_cur BULK COLLECT INTO
2399 r_batch_rec.t_row_id
2400 ,r_batch_rec.t_distribution_interface_id
2401 ,r_batch_rec.t_person_id
2402 ,r_batch_rec.t_assignment_id
2403 ,r_batch_rec.t_element_type_id
2404 ,r_batch_rec.t_distribution_date
2405 ,r_batch_rec.t_distribution_amount
2406 ,r_batch_rec.t_payroll_id
2407 ,r_batch_rec.t_time_period_id
2408 ,r_batch_rec.t_dr_cr_flag
2409 ,r_batch_rec.t_source_code
2410 ,r_batch_rec.t_gl_code_combination_id
2411 ,r_batch_rec.t_project_id
2412 ,r_batch_rec.t_expenditure_organization_id
2413 ,r_batch_rec.t_expenditure_type
2414 ,r_batch_rec.t_task_id
2415 ,r_batch_rec.t_award_id
2416 ,r_batch_rec.t_status_code
2417 ,r_batch_rec.t_batch_name
2418 ,r_batch_rec.t_error_code
2419 ,r_batch_rec.t_attribute_category
2420 ,r_batch_rec.t_attribute1
2421 ,r_batch_rec.t_attribute2
2422 ,r_batch_rec.t_attribute3
2423 ,r_batch_rec.t_attribute4
2424 ,r_batch_rec.t_attribute5
2425 ,r_batch_rec.t_attribute6
2426 ,r_batch_rec.t_attribute7
2427 ,r_batch_rec.t_attribute8
2428 ,r_batch_rec.t_attribute9
2429 ,r_batch_rec.t_attribute10
2430 ,r_batch_rec.t_attribute11
2431 ,r_batch_rec.t_attribute12
2432 ,r_batch_rec.t_attribute13
2433 ,r_batch_rec.t_attribute14
2434 ,r_batch_rec.t_attribute15
2435 ,r_batch_rec.t_last_update_date
2436 ,r_batch_rec.t_last_updated_by
2437 ,r_batch_rec.t_last_update_login
2438 ,r_batch_rec.t_created_by
2439 ,r_batch_rec.t_creation_date
2440 ,r_batch_rec.t_gl_posting_override_date
2441 ,r_batch_rec.t_gms_posting_override_date
2442 ,r_batch_rec.t_business_group_id
2443 ,r_batch_rec.t_set_of_books_id
2444 ,r_batch_rec.t_suspense_org_account_id
2445 ,r_batch_rec.t_currency_code
2446 ,r_batch_rec.t_suspense_auto_glccid
2447 ,r_batch_rec.t_suspense_auto_exp_type
2448 LIMIT 500;
2449
2450 IF g_debug_enabled THEN
2451 hr_utility.Trace('count: '||r_batch_rec.t_distribution_interface_id.COUNT);
2452 END IF;
2453
2454 exit WHEN r_batch_rec.t_distribution_interface_id.COUNT = 0;
2455
2456 -- Transferring data to main table
2457 FORALL k IN 1 .. r_batch_rec.t_distribution_interface_id.COUNT
2458 INSERT
2459 INTO psp_distribution_interface (distribution_interface_id
2460 , person_id
2461 , assignment_id
2462 , element_type_id
2463 , distribution_date
2464 , distribution_amount
2465 , payroll_id
2466 , time_period_id
2467 , dr_cr_flag
2468 , source_code
2469 , gl_code_combination_id
2470 , project_id
2471 , expenditure_organization_id
2472 , expenditure_type
2473 , task_id
2474 , award_id
2475 , status_code
2476 , batch_name
2477 , error_code
2478 , attribute_category
2479 , attribute1
2480 , attribute2
2481 , attribute3
2482 , attribute4
2483 , attribute5
2484 , attribute6
2485 , attribute7
2486 , attribute8
2487 , attribute9
2488 , attribute10
2489 , attribute11
2490 , attribute12
2491 , attribute13
2492 , attribute14
2493 , attribute15
2494 , last_update_date
2495 , last_updated_by
2496 , last_update_login
2497 , created_by
2498 , creation_date
2499 , gl_posting_override_date
2500 , gms_posting_override_date
2501 , business_group_id
2502 , set_of_books_id
2503 , suspense_org_account_id
2504 , currency_code
2505 , suspense_auto_glccid
2506 , suspense_auto_exp_type)
2507 VALUES (r_batch_rec.t_distribution_interface_id (k)
2508 , r_batch_rec.t_person_id (k)
2509 , r_batch_rec.t_assignment_id (k)
2510 , r_batch_rec.t_element_type_id (k)
2511 , r_batch_rec.t_distribution_date (k)
2512 , r_batch_rec.t_distribution_amount (k)
2513 , r_batch_rec.t_payroll_id (k)
2514 , r_batch_rec.t_time_period_id (k)
2515 , r_batch_rec.t_dr_cr_flag (k)
2516 , r_batch_rec.t_source_code (k)
2517 , r_batch_rec.t_gl_code_combination_id (k)
2518 , r_batch_rec.t_project_id (k)
2519 , r_batch_rec.t_expenditure_organization_id (k)
2520 , r_batch_rec.t_expenditure_type (k)
2521 , r_batch_rec.t_task_id (k)
2522 , r_batch_rec.t_award_id (k)
2523 , r_batch_rec.t_status_code (k)
2524 , r_batch_rec.t_batch_name (k)
2525 , r_batch_rec.t_error_code (k)
2526 , r_batch_rec.t_attribute_category (k)
2527 , r_batch_rec.t_attribute1 (k)
2528 , r_batch_rec.t_attribute2 (k)
2529 , r_batch_rec.t_attribute3 (k)
2530 , r_batch_rec.t_attribute4 (k)
2531 , r_batch_rec.t_attribute5 (k)
2532 , r_batch_rec.t_attribute6 (k)
2533 , r_batch_rec.t_attribute7 (k)
2534 , r_batch_rec.t_attribute8 (k)
2535 , r_batch_rec.t_attribute9 (k)
2536 , r_batch_rec.t_attribute10 (k)
2537 , r_batch_rec.t_attribute11 (k)
2538 , r_batch_rec.t_attribute12 (k)
2539 , r_batch_rec.t_attribute13 (k)
2540 , r_batch_rec.t_attribute14 (k)
2541 , r_batch_rec.t_attribute15 (k)
2542 , r_batch_rec.t_last_update_date (k)
2543 , r_batch_rec.t_last_updated_by (k)
2544 , r_batch_rec.t_last_update_login (k)
2545 , r_batch_rec.t_created_by (k)
2546 , r_batch_rec.t_creation_date (k)
2547 , r_batch_rec.t_gl_posting_override_date (k)
2548 , r_batch_rec.t_gms_posting_override_date (k)
2549 , r_batch_rec.t_business_group_id (k)
2550 , r_batch_rec.t_set_of_books_id (k)
2551 , r_batch_rec.t_suspense_org_account_id (k)
2552 , r_batch_rec.t_currency_code (k)
2553 , r_batch_rec.t_suspense_auto_glccid (k)
2554 , r_batch_rec.t_suspense_auto_exp_type (k));
2555
2556
2557 g_bulk_row_count :=0;
2558 FOR bulk_idx IN 1..r_batch_rec.t_distribution_interface_id.COUNT
2559 LOOP
2560 g_bulk_row_count := g_bulk_row_count + SQL%bulk_rowcount(bulk_idx);
2561 END LOOP;
2562
2563 IF g_debug_enabled THEN
2564 hr_utility.Trace('g_bulk_row_count insert : '||g_bulk_row_count);
2565 END IF;
2566
2567 -- Deletes the records from the archive table once the records get created in main table.
2568
2569 FORALL k IN 1..r_batch_rec.t_distribution_interface_id.COUNT
2570 DELETE FROM psp_distribution_int_arch
2571 WHERE ROWID = r_batch_rec.T_row_id(k);
2572
2573 g_bulk_row_count :=0;
2574 FOR bulk_idx IN 1..r_batch_rec.t_distribution_interface_id.COUNT
2575 LOOP
2576 g_bulk_row_count := g_bulk_row_count + SQL%bulk_rowcount(bulk_idx);
2577 END LOOP;
2578
2579 IF g_debug_enabled THEN
2580 hr_utility.Trace('g_bulk_row_count delete: '||g_bulk_row_count);
2581 END IF;
2582
2583 -- Reduces the size of the batch from control table when the records were moved partially from archive to main table.
2584 UPDATE psp_arch_ret_requests
2585 SET archived_batch_size = archived_batch_size - g_bulk_row_count
2586 WHERE request_id = p_arch_request_id
2587 AND batch_name = batch_name_rec.Batch_name(i);
2588
2589 IF g_debug_enabled THEN
2590 hr_utility.Trace('Batch Name: '||batch_name_rec.Batch_name(i)|| ' request_id: '||p_arch_request_id);
2591 hr_utility.Trace('Updated psp_arch_ret_requests: '||SQL%rowcount);
2592 END IF;
2593
2594 COMMIT; -- Committing partial batch Jbz
2595 END LOOP;
2596 CLOSE batch_cur;
2597
2598 -- Once the complete batch is finished displays the status of the batch
2599
2600 OPEN batch_counts_cur(batch_name_rec.Batch_name(i));
2601 FETCH batch_counts_cur INTO l_remaining_records,l_original_batch_size;
2602 CLOSE batch_counts_cur;
2603
2604 IF g_debug_enabled THEN
2605 hr_utility.Trace('after select: '||l_remaining_records||' : '||l_original_batch_size);
2606 END IF;
2607
2608 IF l_remaining_records = 0 THEN
2609 fnd_file.Put_line(fnd_file.log,i||'. '||batch_name_rec.Batch_name(i)||' '||batch_name_rec.Min_dist_date(i)||' '||batch_name_rec.Max_dist_date(i)||' '||l_original_batch_size);
2610 ELSE
2611 fnd_file.Put_line(fnd_file.log,i||'. '||batch_name_rec.Batch_name(i)||' '||batch_name_rec.Min_dist_date(i)||' '||batch_name_rec.Max_dist_date(i)||' '||l_original_batch_size|| ' Retrival Incomplete');
2612 END IF;
2613
2614 -- Once the complete batch is processed then the next batch will start for retrival process
2615
2616 END LOOP;
2617
2618 fnd_file.Put_line(fnd_file.log,'---------------------------------------------------------------------------------------------------------------------');
2619
2620 -- Once all the batches were completed the records corresponding to the arch request id will be deleted from the control table.
2621
2622 DELETE FROM psp_arch_ret_requests
2623 WHERE request_id = p_arch_request_id;
2624
2625 IF g_debug_enabled THEN
2626 hr_utility.Trace('delete count: '||SQL%rowcount);
2627 END IF;
2628
2629 COMMIT;
2630
2631 fnd_file.Put_line(fnd_file.log,'Retrieve Distribution Interface successfully completed');
2632
2633 EXCEPTION
2634 WHEN OTHERS THEN
2635
2636 fnd_file.Put_line(fnd_file.log,dbms_utility.format_error_backtrace);
2637 fnd_file.Put_line(fnd_file.log,'RETRIEVE_DISTRIBUTION_INT---> exception '||sqlerrm);
2638
2639
2640 END retrieve_distribution_int;
2641
2642
2643
2644 END PSP_ARCHIVE_RETRIEVE;