[Home] [Help]
PACKAGE BODY: APPS.PSP_ARCHIVE_RETRIEVE
Source
1 PACKAGE BODY PSP_ARCHIVE_RETRIEVE as
2 /* $Header: PSPARRTB.pls 120.6 2006/07/27 23:05:07 vdharmap noship $ */
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 END PSP_ARCHIVE_RETRIEVE;