[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_UPDATE_VAT_RATE
Source
1 PACKAGE BODY IGI_RPI_UPDATE_VAT_RATE AS
2 -- $Header: igirruvrb.pls 120.0.12010000.1 2010/02/15 09:44:11 gaprasad noship $
3
4 l_state_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
5 l_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 l_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
7 l_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 l_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9 l_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10
11 PROCEDURE WriteToLogFile ( pp_msg_level in number,pp_path in varchar2, pp_mesg in varchar2 ) IS
12 BEGIN
13
14 IF pp_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
15 fnd_log.string(pp_msg_level, pp_path, pp_mesg );
16 END IF;
17 END;
18
19 PROCEDURE output (p_msg IN VARCHAR2) IS
20 BEGIN
21
22 fnd_file.put_line(fnd_file.output, p_msg);
23
24 END output;
25
26 PROCEDURE print_header (effective_date in date,old_tax_code varchar2,new_tax_code varchar2) IS
27 BEGIN
28
29 output ('IGIRRUVR Standing Charges : Preliminary VAT Rate Update Report '||'Date : '||trunc(SYSDATE));
30 output ('');
31 output ('');
32 output ('');
33 output ('');
34 output (' VAT Effective Date : '||effective_date);
35 output ('');
36 output (' Old VAT Rate : '||rtrim(old_tax_code));
37 output (' New VAT Rate : '||rtrim(new_tax_code));
38 output ('');
39 output ('');
40 output ('');
41 output (' ---------------------------------------------------------------------------------------------------------------------------------------------');
42 output (' Charge Reference Bill Period Start Date Next Due Date Description ');
43 output (' ---------------------------------------------------------------------------------------------------------------------------------------------');
44 output ('');
45 output ('');
46
47 END print_header;
48
49 PROCEDURE print_footer IS
50 BEGIN
51
52 output ('');
53 output ('');
54 output ('');
55 output ('');
56 output (' ********************** END OF REPORT ***************************** ');
57
58 END print_footer;
59
60
61 FUNCTION tax_name(vat_id NUMBER) RETURN varchar2 IS
62
63 l_tax_code varchar2(50);
64
65 BEGIN
66
67 SELECT tax_rate_code INTO l_tax_code
68 FROM zx_rates_b
69 WHERE tax_rate_id = vat_id;
70
71 RETURN l_tax_code;
72
73 EXCEPTION
74 WHEN OTHERS THEN
75 WriteToLogFile(l_state_level, 'igi.plsql.igirruvr.Tax_name',
76 '(Error) Tax Name Procedure');
77
78 END tax_name;
79
80
81
82 PROCEDURE update_vat_rate ( errbuf OUT NOCOPY VARCHAR2
83 , retcode OUT NOCOPY NUMBER
84 , p_org_id IN NUMBER
85 , p_old_vat_id IN NUMBER
86 , p_new_vat_id IN NUMBER
87 , p_effective_date IN VARCHAR2
88 , p_mode IN VARCHAR2
89 )
90 IS
91
92 l_request_id NUMBER;
93 l_rec_count NUMBER;
94 l_mesg varchar2(200) ;
95 l_effective_date DATE;
96 l_rowid varchar2(25) ;
97 l_old_tax_code varchar2(50) ;
98 l_new_tax_code varchar2(50) ;
99 l_legal_entity_id NUMBER;
100
101 x_return_status VARCHAR2(30);
102
103 x_effective_date DATE;
104 x_msg_count NUMBER;
105 x_msg_data VARCHAR2(100);
106
107
108 /*------------------------------------------------------*
109 | Cursor for Selecting Standing Charges |
110 *------------------------------------------------------*/
111
112 CURSOR C_standing_charges ( cp_org_id in number
113 , cp_effective_date in date ) IS
114 SELECT sc.*
115 FROM igi_rpi_standing_charges_all sc
116 WHERE org_id = cp_org_id
117 AND upper(sc.status) = 'ACTIVE'
118 AND sc.START_DATE <= cp_effective_date
119 AND sc.NEXT_DUE_DATE >= cp_effective_date;
120
121
122 /*-----------------------------------------------------------------------------------*
123 | Cursor for Line Details based on the Selected Standing Charge cursor above |
124 *-----------------------------------------------------------------------------------*/
125
126 CURSOR C_line_details (cp_standing_charge_id in number
127 ,cp_old_vat_id in number ) IS
128 SELECT ld.*
129 FROM igi_rpi_line_details_all ld
130 WHERE ld.standing_charge_id = cp_standing_charge_id
131 AND ld.vat_tax_id = cp_old_vat_id;
132
133
134 BEGIN
135
136 l_mesg := null;
137 l_rec_count := 0;
138
139 IF p_effective_date IS NOT NULL THEN
140 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
141 ELSE
142 l_effective_date := SYSDATE;
143 END IF;
144
145
146 IF igi_gen.is_req_installed('RPI') THEN
147 null;
148 ELSE
149 fnd_message.set_name( 'IGI', 'IGI_RPI_IS_DISABLED');
150 l_mesg := fnd_message.get;
151 WriteToLogFile(l_state_level, 'igi.plsql.igirruvr.update_vat_rate.Msg1',
152 l_mesg);
153 retcode := 2;
154 errbuf := l_mesg;
155 RETURN;
156 END IF;
157
158 WriteToLogFile(l_state_level, 'igi.plsql.igirruvr.update_vat_rate.Msg2',
159 'BEGIN Update VAT Rate');
160
161
162 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
163
164 l_old_tax_code := tax_name(p_old_vat_id);
165 l_new_tax_code := tax_name(p_new_vat_id);
166
167 /* Printing header into the output file */
168
169 print_header(l_effective_date,l_old_tax_code,l_new_tax_code);
170
171 FOR C_standing_charges_rec in C_standing_charges(p_org_id,l_effective_date) LOOP
172
173 FOR C_line_details_rec in C_line_details(C_standing_charges_rec.standing_charge_id,p_old_vat_id) LOOP
174
175 l_rec_count := l_rec_count + 1;
176
177 /* Printing records into the output file */
178
179
180 output (' '||rtrim(C_standing_charges_rec.charge_reference)||' '||C_standing_charges_rec.period_name||' '||
181 C_standing_charges_rec.start_date||' '||C_standing_charges_rec.next_due_date||' '||rtrim(substr(C_standing_charges_rec.description,1,30)));
182
183
184 /* Inserting into line_details_audit table for audit trail */
185
186 igi_rpi_line_audit_det_all_pkg.insert_row (
187 x_mode => 'R',
188 x_rowid => l_rowid,
189 x_standing_charge_id => TO_NUMBER (C_standing_charges_rec.standing_charge_id),
190 x_line_item_id => TO_NUMBER (C_line_details_rec.LINE_ITEM_ID),
191 x_charge_item_number => TO_NUMBER (C_line_details_rec.CHARGE_ITEM_NUMBER),
192 x_item_id => TO_NUMBER (C_line_details_rec.ITEM_ID),
193 x_price => nvl(C_line_details_rec.REVISED_PRICE,C_line_details_rec.PRICE),
194 x_effective_date => nvl(C_line_details_rec.REVISED_EFFECTIVE_DATE,C_line_details_rec.CURRENT_EFFECTIVE_DATE),
195 x_revised_price => C_line_details_rec.REVISED_PRICE,
196 x_revised_effective_date => C_line_details_rec.REVISED_EFFECTIVE_DATE,
197 x_run_id => TO_NUMBER (C_line_details_rec.RUN_ID),
198 x_org_id => TO_NUMBER (C_line_details_rec.ORG_ID),
199 x_previous_price => C_line_details_rec.PRICE,
200 x_previous_effective_date => C_line_details_rec.CURRENT_EFFECTIVE_DATE,
201 x_old_vat_id => p_old_vat_id,
202 x_new_vat_id => p_new_vat_id,
203 x_request_id => l_request_id
204 );
205
206
207 /* Updating new VAT Rate to all charge lines */
208
209 UPDATE igi_rpi_line_details_all ld SET
210 ld.VAT_TAX_ID = p_new_vat_id
211 WHERE ld.standing_charge_id = C_standing_charges_rec.standing_charge_id
212 AND ld.line_item_id = C_line_details_rec.line_item_id
213 AND ld.org_id = p_org_id;
214
215
216 END LOOP; /* C_line_details loop */
217
218 END LOOP; /* C_standing_charges loop */
219
220 IF (l_rec_count = 0) THEN
221
222 output (' -------- NO DATA FOUND -------');
223
224 END IF;
225
226
227 print_footer;
228
229 WriteToLogFile(l_state_level, 'igi.plsql.igirruvr.update_vat_rate.Msg3',
230 'END (Successful) Update VAT Rate');
231 errbuf := null;
232 retcode := 0;
233
234 /* Commit only when the request is run in final mode,rollback in preliminary mode */
235
236 IF p_mode = 'P' THEN
237 ROLLBACK;
238 END IF;
239
240 EXCEPTION WHEN OTHERS THEN
241
242 ROLLBACK;
243 WriteToLogFile(l_state_level, 'igi.plsql.igirruvr.update_vat_rate.Msg4',
244 'END (Error) Update VAT Rate');
245 errbuf := SQLERRM;
246 retcode := 2;
247
248 END update_vat_rate;
249
250 END IGI_RPI_UPDATE_VAT_RATE;