[Home] [Help]
PACKAGE BODY: APPS.PSP_ROLLBACK_DIST_LINES
Source
1 PACKAGE BODY psp_rollback_dist_lines AS
2 /*$Header: PSPRBDLB.pls 120.0.12010000.5 2009/09/18 06:34:02 amakrish ship $*/
3
4 PROCEDURE DELETE_LINES(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY VARCHAR2,
6 p_source_type IN VARCHAR2,
7 p_source_code IN VARCHAR2,
8 p_payroll_id IN NUMBER,
9 p_time_period_id IN NUMBER,
10 p_batch_name IN VARCHAR2,
11 p_business_group_id IN NUMBER,
12 p_set_of_books_id IN NUMBER) IS
13
14 CURSOR payroll_control_cur IS -- Bug 6686483
15 select payroll_control_id,status_code,time_period_id
16 from psp_payroll_controls
17 where business_group_id = p_business_group_id
18 AND set_of_books_id = p_set_of_books_id
19 AND source_type = nvl(p_source_type,source_type)
20 AND source_type <> 'A' -- Bug 7136917
21 AND payroll_source_code = nvl(p_source_code,payroll_source_code)
22 AND payroll_id = nvl(p_payroll_id,payroll_id)
23 AND time_period_id = nvl(p_time_period_id,time_period_id)
24 AND nvl(batch_name,'N') = nvl(nvl(p_batch_name,batch_name),'N')
25 AND parent_payroll_control_id IS NULL
26 UNION
27 select payroll_control_id,status_code,time_period_id
28 from psp_payroll_controls ppc1
29 where ppc1.parent_payroll_control_id in(select payroll_control_id
30 from psp_payroll_controls
31 where business_group_id = p_business_group_id
32 AND set_of_books_id = p_set_of_books_id
33 AND source_type = nvl(p_source_type,source_type)
34 AND source_type <> 'A' -- Bug 7136917
35 AND payroll_source_code = nvl(p_source_code,payroll_source_code)
36 AND payroll_id = nvl(p_payroll_id,payroll_id)
37 AND time_period_id = nvl(p_time_period_id,time_period_id)
38 AND nvl(batch_name,'N') = nvl(nvl(p_batch_name,batch_name),'N'));
39
40 payroll_control_rec payroll_control_cur%ROWTYPE;
41 l_period_name VARCHAR2(80);
42 l_payroll_name VARCHAR2(80); -- Bug 6686483
43 l_deleted BOOLEAN := TRUE;
44 cnt NUMBER := 0;
45 BEGIN
46 fnd_msg_pub.initialize;
47
48 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Rollback CDL parameters :' );
49 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_source_type = ' || p_source_type);
50 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_source_code = ' || p_source_code);
51 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_payroll_id = ' || p_payroll_id);
52 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_time_period_id = ' || p_time_period_id);
53 fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_batch_name = ' || p_batch_name);
54
55 fnd_file.put_line(fnd_file.log,'');
56
57
58 OPEN payroll_control_cur;
59 LOOP
60 FETCH payroll_control_cur INTO payroll_control_rec;
61 IF payroll_control_cur%NOTFOUND
62 THEN
63 CLOSE payroll_control_cur;
64 commit;
65 EXIT;
66 END IF;
67 BEGIN
68
69 -- Fix for bug 8922889
70 SELECT ptp.period_name, ppf.payroll_name into l_period_name, l_payroll_name
71 FROM per_time_periods ptp, pay_payrolls_f ppf
72 WHERE ptp.time_period_id = payroll_control_rec.time_period_id
73 and ptp.payroll_id = ppf.payroll_id
74 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
75
76 /* Record has been processed by Summarise and Transfer */
77 IF payroll_control_rec.status_code in ('P','I')
78 THEN
79 fnd_message.set_name('PSP','PSP_DL_ALREADY_PROCESSED');
80 fnd_message.set_token('TIME_PERIOD',l_period_name);
81 fnd_msg_pub.add;
82 l_deleted := FALSE;
83 ELSIF payroll_control_rec.status_code = 'N'
84 THEN
85 SELECT COUNT(*) INTO cnt
86 FROM psp_summary_lines
87 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
88 IF cnt = 0 /* lines not yet processed */
89 THEN
90 BEGIN
91 DELETE FROM PSP_DISTRIBUTION_LINES
92 WHERE payroll_sub_line_id in (select payroll_sub_line_id
93 from psp_payroll_sub_lines
94 where payroll_line_id in (
95 select payroll_line_id from psp_payroll_lines
96 where payroll_control_id = payroll_control_rec.payroll_control_id));
97 UPDATE PSP_PAYROLL_CONTROLS
98 SET dist_dr_amount = NULL,
99 dist_cr_amount = NULL,
100 cdl_payroll_action_id = NULL --- salary cap 4304623
101 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
102
103 fnd_file.put_line(fnd_file.log,'***** Completed Rollback Distribution Lines for ' || l_payroll_name || ' , ' || l_period_name);
104
105 END;
106 ELSE /* count(*) != 0 */
107 /* Though the payroll control record are having status N
108 but the lines have processed by ST but failed. */
109
110 fnd_message.set_name('PSP','PSP_DL_ALREADY_PROCESSED');
111 fnd_message.set_token('TIME_PERIOD',l_period_name);
112 fnd_msg_pub.add;
113 l_deleted := FALSE;
114 END IF; /* count(*) = 0 */
115 END IF;
116 END;
117 END LOOP;
118 if l_deleted = FALSE
119 THEN
120 psp_message_s.print_error(p_mode => FND_FILE.LOG,
121 p_print_header => FND_API.G_TRUE);
122 END IF;
123 --Introduced For the Bug 2665152
124 retcode:=0;
125 psp_message_s.print_success;
126 --End of Bug fix 2665152
127 EXCEPTION
128 WHEN OTHERS
129 THEN
130 ROLLBACK;
131 fnd_message.set_name('PSP','PSP_SQL_ERROR');
132 fnd_message.set_token('SQLERROR',sqlerrm);
133 fnd_msg_pub.add;
134 --For Bug 2665152 : Introduced the Error Message and assigned value to retcode
135 psp_message_s.print_error(p_mode => FND_FILE.LOG,
136 p_print_header => FND_API.G_TRUE);
137 retcode:=2;
138
139 END delete_lines;
140 END psp_rollback_dist_lines;