[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.4 2008/10/01 05:38:37 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 -- Bug 6686483
69 -- Added distinct keyword for bug 7444009
70 SELECT distinct 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
75 /* Record has been processed by Summarise and Transfer */
76 IF payroll_control_rec.status_code in ('P','I')
77 THEN
78 fnd_message.set_name('PSP','PSP_DL_ALREADY_PROCESSED');
79 fnd_message.set_token('TIME_PERIOD',l_period_name);
80 fnd_msg_pub.add;
81 l_deleted := FALSE;
82 ELSIF payroll_control_rec.status_code = 'N'
83 THEN
84 SELECT COUNT(*) INTO cnt
85 FROM psp_summary_lines
86 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
87 IF cnt = 0 /* lines not yet processed */
88 THEN
89 BEGIN
90 DELETE FROM PSP_DISTRIBUTION_LINES
91 WHERE payroll_sub_line_id in (select payroll_sub_line_id
92 from psp_payroll_sub_lines
93 where payroll_line_id in (
94 select payroll_line_id from psp_payroll_lines
95 where payroll_control_id = payroll_control_rec.payroll_control_id));
96 UPDATE PSP_PAYROLL_CONTROLS
97 SET dist_dr_amount = NULL,
98 dist_cr_amount = NULL,
99 cdl_payroll_action_id = NULL --- salary cap 4304623
100 WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
101
102 fnd_file.put_line(fnd_file.log,'***** Completed Rollback Distribution Lines for ' || l_payroll_name || ' , ' || l_period_name);
103
104 END;
105 ELSE /* count(*) != 0 */
106 /* Though the payroll control record are having status N
107 but the lines have processed by ST but failed. */
108
109 fnd_message.set_name('PSP','PSP_DL_ALREADY_PROCESSED');
110 fnd_message.set_token('TIME_PERIOD',l_period_name);
111 fnd_msg_pub.add;
112 l_deleted := FALSE;
113 END IF; /* count(*) = 0 */
114 END IF;
115 END;
116 END LOOP;
117 if l_deleted = FALSE
118 THEN
119 psp_message_s.print_error(p_mode => FND_FILE.LOG,
120 p_print_header => FND_API.G_TRUE);
121 END IF;
122 --Introduced For the Bug 2665152
123 retcode:=0;
124 psp_message_s.print_success;
125 --End of Bug fix 2665152
126 EXCEPTION
127 WHEN OTHERS
128 THEN
129 ROLLBACK;
130 fnd_message.set_name('PSP','PSP_SQL_ERROR');
131 fnd_message.set_token('SQLERROR',sqlerrm);
132 fnd_msg_pub.add;
133 --For Bug 2665152 : Introduced the Error Message and assigned value to retcode
134 psp_message_s.print_error(p_mode => FND_FILE.LOG,
135 p_print_header => FND_API.G_TRUE);
136 retcode:=2;
137
138 END delete_lines;
139 END psp_rollback_dist_lines;