1 PACKAGE BODY PSP_NON_ORCL_RLBK_PKG AS
2 --$Header: PSPNORBB.pls 115.11 2002/11/18 11:54:44 ddubey ship $
3
4 PROCEDURE change_records(c_Batch_Name IN VARCHAR2,c_payroll_control_id IN NUMBER,
5 c_business_group_id IN NUMBER,c_set_of_bks_id IN NUMBER);
6
7 PROCEDURE check_rollback(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_Batch_Name IN Varchar2,
8 p_business_group_id IN NUMBER,p_set_of_bks_id IN NUMBER) IS
9
10 l_dist_cr_amount NUMBER;
11 l_dist_dr_amount NUMBER;
12 l_payroll_control_id NUMBER;
13
14 CURSOR cursor_batch IS
15 SELECT dist_dr_amount,dist_cr_amount,payroll_control_id
16 FROM psp_payroll_controls a
17 WHERE a.batch_name = p_batch_name
18 AND a.status_code = 'N'
19 AND a.business_group_id = p_business_group_id
20 AND a.set_of_books_id = p_set_of_bks_id;
21
22 cursor_batch_agg cursor_batch%RowType;
23
24 BEGIN
25
26 fnd_msg_pub.initialize;
27
28 FOR cursor_batch_agg IN cursor_batch LOOP
29
30 l_dist_dr_amount := cursor_batch_agg.dist_dr_amount;
31 l_dist_cr_amount := cursor_batch_agg.dist_cr_amount;
32 l_payroll_control_id := cursor_batch_agg.payroll_control_id;
33
34 IF l_dist_dr_amount IS NULL AND l_dist_cr_amount IS NULL THEN
35 change_records(c_Batch_Name => p_batch_name,
36 c_payroll_control_id => l_payroll_control_id,
37 c_business_group_id => p_business_group_id,
38 c_set_of_bks_id => p_set_of_bks_id);
39 retcode := FND_API.G_RET_STS_SUCCESS;
40 PSP_MESSAGE_S.Print_Success;
41 --dbms_output.put_line('after calling the change_record procedure');
42 ELSE
43 fnd_message.set_name('PSP','PSP_NON_ORCL_RLBK');
44 fnd_message.set_token('BATCH_NAME',p_Batch_Name);
45 fnd_msg_pub.add;
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 --dbms_output.put_line('......The BAtch Name Does not exists');
48 END IF;
49 END LOOP;
50
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 fnd_message.set_name('PSP','PSP_NON_ORCL_RLBK_EXCP');
54 fnd_message.set_token('BATCH_NAME',p_Batch_Name);
55 fnd_msg_pub.add;
56 --dbms_output.put_line('No data found for the particular batch_name');
57
58 retcode := 2;
59 psp_message_s.print_error(p_mode => FND_FILE.LOG,
60 p_print_header => FND_API.G_TRUE);
61 return;
62
63 WHEN OTHERS THEN
64 fnd_message.set_name('PSP','PSP_NON_ORCL_RLBK_EXCP');
65 fnd_message.set_token('BATCH_NAME',p_Batch_Name);
66 fnd_msg_pub.add;
67 --dbms_output.put_line('Query raised unhandled exceptions');
68
69 retcode := 2;
70 psp_message_s.print_error(p_mode => FND_FILE.LOG,
71 p_print_header => FND_API.G_TRUE);
72 return;
73
74 END check_rollback;
75
76 -- The Procedure to Delete the records from the Psp_payroll_controls table and to update the psp_payroll_interface table -- if the dist_dr_amount and the dist_cr_amount is NULL for the particular batch_name
77
78 PROCEDURE change_records(c_batch_name IN Varchar2,c_payroll_control_id IN NUMBER,
79 c_business_group_id IN NUMBER,c_set_of_bks_id IN NUMBER) IS
80
81 CURSOR payroll_id_cur IS
82 SELECT payroll_line_id
83 FROM psp_payroll_lines
84 WHERE payroll_control_id = c_payroll_control_id;
85
86 l_payroll_line_id NUMBER;
87
88 BEGIN
89 /********************* The payroll_lines in the payroll_lines and payroll_sub_lines table
90 would be deleted for roll back of non-oracle payroll*******************/
91
92 OPEN payroll_id_cur;
93 LOOP
94 FETCH payroll_id_cur INTO l_payroll_line_id;
95 IF payroll_id_cur%NOTFOUND THEN
96 CLOSE payroll_id_cur;
97 --dbms_output.put_line('Payroll Id for the Particular Batch_name ' ||c_batch_name||'not found');
98 EXIT;
99 END IF;
100
101 DELETE FROM psp_payroll_sub_lines
102 WHERE payroll_line_id = l_payroll_line_id;
103
104 END LOOP;
105
106 DELETE FROM psp_payroll_lines
107 WHERE payroll_control_id = c_payroll_control_id;
108
109 DELETE FROM psp_payroll_controls
110 WHERE batch_name = c_batch_name
111 AND business_group_id = c_business_group_id
112 AND set_of_books_id = c_set_of_bks_id;
113
114 --dbms_output.put_line('successful deletion of psp_payroll_controls ......');
115
116 UPDATE psp_payroll_interface SET status_code = 'N'
117 WHERE batch_name = c_batch_name
118 AND business_group_id = c_business_group_id
119 AND set_of_books_id = c_set_of_bks_id;
120
121 --dbms_output.put_line('Successful Updation of psp_payroll_interface..... last step');
122
123 commit;
124
125 --dbms_output.put_line('successful completion of delete and update on psp_payroll_controls and payroll_interface');
126 END change_records;
127
128 END PSP_NON_ORCL_RLBK_PKG;
[Home] [Help]