1 PACKAGE BODY PSP_RBKPAY AS
2 --$Header: PSPORRBB.pls 120.3 2006/08/04 23:13:40 vdharmap noship $
3 PROCEDURE rollback_paytrans(errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY VARCHAR2,
5 p_period_type IN VARCHAR2 ,
6 p_time_period_id IN NUMBER) IS
7
8 -- Get Payroll start date , end date, payroll id
9 -- from per_time_periods based on time period id
10
11 CURSOR get_payroll_id_csr is
12 SELECT start_date, end_date, payroll_id
13 FROM per_time_periods
14 WHERE time_period_id = p_time_period_id ;
15
16 CURSOR get_payroll_control_id_csr is
17 SELECT payroll_control_id, status_code, dist_cr_amount,dist_dr_amount
18 FROM psp_payroll_controls
19 where time_period_id=p_time_period_id
20 and payroll_source_code='PAY'
21 and source_type='O'
22 union --- added union for 509002
23 SELECT payroll_control_id, status_code, dist_cr_amount,dist_dr_amount
24 FROM psp_payroll_controls
25 where parent_payroll_control_id in
26 (SELECT payroll_control_id
27 FROM psp_payroll_controls
28 where time_period_id=p_time_period_id
29 and payroll_source_code='PAY'
30 and source_type='O');
31
32 g_payroll_control_rec get_payroll_control_id_csr%ROWTYPE;
33
34 l_start_date date;
35 l_end_date date;
36 l_payroll_id number(9);
37
38 --Bug 3950282 : removed the cursor get_payroll_line_csr; Deleting records based on control id's.
39 /*
40 CURSOR get_payroll_line_csr is
41 SELECT payroll_line_id from psp_payroll_lines
42 where payroll_control_id = g_payroll_control_rec.payroll_control_id;
43
44
45 g_payroll_line_rec get_payroll_line_csr%ROWTYPE;
46 */
47
48 -- Error Handling variables
49
50 l_error_api_name varchar2(2000);
51 l_return_status varchar2(1);
52 l_msg_count number;
53 l_msg_data varchar2(2000);
54 l_msg_index_out number;
55 --
56 l_api_name varchar2(30) := 'RBK_PAYTRN';
57
58 -- Other Variables
59 debug boolean:=FALSE;
60
61 l_all_sum_trans_flag boolean := TRUE;
62
63 BEGIN
64 FND_MSG_PUB.Initialize;
65
66 /* Check whether timeperiod id given is valid */
67
68 open get_payroll_id_csr;
69 fetch get_payroll_id_csr into l_start_date,l_end_date, l_payroll_id;
70 if get_payroll_id_csr%NOTFOUND then
71 fnd_message.set_name('PSP','PSP_INVALID_PERIOD');
72 fnd_msg_pub.add;
73 close get_payroll_id_csr;
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 end if;
76
77 close get_payroll_id_csr ;
78
79
80 open get_payroll_control_id_csr;
81 fetch get_payroll_control_id_csr into g_payroll_control_rec;
82
83 if get_payroll_control_id_csr%NOTFOUND then
84 fnd_message.set_name('PSP','PSP_PAY_RBK_PRD');
85
86 /*************************************************************************************************
87 Message Details would be either Summarize and transfer has already been run , or
88 create dist lines. Need to first rollback CDL, before attempting here
89 **************************************************************************************************/
90 fnd_msg_pub.add;
91 close get_payroll_control_id_csr;
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 end if;
94
95 close get_payroll_control_id_csr;
96
97
98 open get_payroll_control_id_csr;
99 LOOP
100 fetch get_payroll_control_id_csr into g_payroll_control_rec;
101 exit when get_payroll_control_id_csr%NOTFOUND;
102
103 IF g_payroll_control_rec.status_code = 'N'
104 THEN
105 l_all_sum_trans_flag := FALSE;
106 IF (g_payroll_control_rec.dist_cr_amount is not null or g_payroll_control_rec.dist_cr_amount is not null)
107 /***********************************************************************************************
108 Check whether Create Distribution Lines has been run
109 If so, need to rollback create distribution lines first
110 ***********************************************************************************************/
111 THEN
112 fnd_message.set_name('PSP','PSP_PAY_RBK_DST');
113 fnd_msg_pub.add;
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115
116 ELSE
117
118 --Bug 3950282 : removed the cursor get_payroll_line_csr; Deleting records based on control id's.
119 /*
120 open get_payroll_line_csr;
121 loop
122 fetch get_payroll_line_csr into g_payroll_line_rec;
123 EXIT when get_payroll_line_csr%NOTFOUND;
124 */
125 delete from psp_sub_line_reasons where payroll_sub_line_id in
126 (select payroll_sub_line_id from psp_payroll_sub_lines where payroll_line_id in
127 (select payroll_line_id from psp_payroll_lines where payroll_control_id = g_payroll_control_rec.payroll_control_id));
128
129 delete from psp_payroll_sub_lines where payroll_line_id in
130 (select payroll_line_id from psp_payroll_lines where payroll_control_id = g_payroll_control_rec.payroll_control_id);
131
132 delete from psp_payroll_lines where payroll_control_id = g_payroll_control_rec.payroll_control_id;
133
134 --Bug 3950282 : removed the cursor get_payroll_line_csr; Deleting records based on control id's.
135 -- end loop;
136 -- close get_payroll_line_csr;
137
138 delete from psp_payroll_controls where payroll_control_id=g_payroll_control_rec.payroll_control_id;
139 END IF;
140 END IF;
141 END LOOP;
142
143
144
145 /***********************************************************************************************
146 Summarize and transfer may have been run : If all the records in psp_payroll_controls are
147 Summerized and transfered (status_code= 'P') then dont allow rollback and show error.
148 *********************************************************************************************** */
149 -- Bug 3950282:raise error only if all rows in Control table are already summerised and transfered.
150 IF l_all_sum_trans_flag = TRUE THEN
151 fnd_message.set_name('PSP','PSP_PAY_RBK_STA');
152 fnd_msg_pub.add;
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 commit;
157 retcode:= FND_API.G_RET_STS_SUCCESS;
158 PSP_MESSAGE_S.Print_Success;
159 return;
160
161 EXCEPTION
162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
163
164 /* fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
165 p_encoded => FND_API.G_FALSE,
166 p_data => l_msg_data,
167 p_msg_index_out => l_msg_count); */
168
169 errbuf :='Exception raised' ;
170 retcode:=2; /* Error */
171 psp_message_s.print_error(p_mode => FND_FILE.LOG,
172 p_print_header => FND_API.G_TRUE);
173 end;
174
175 end;
[Home] [Help]