[Home] [Help]
PACKAGE BODY: APPS.JAI_PAN_UPDATE_PKG
Source
1 package body jai_pan_update_pkg as
2 /* $Header: jai_pan_update_b.plb 120.0.12000000.1 2007/07/24 06:56:06 rallamse noship $ */
3
4 PROCEDURE Print_Log
5 (
6 P_debug IN VARCHAR2,
7 P_string IN VARCHAR2
8 ) IS
9
10 stemp VARCHAR2(1000);
11 nlength NUMBER := 1;
12
13 BEGIN
14
15 IF (P_Debug = 'Y') THEN
16 WHILE(length(P_string) >= nlength)
17 LOOP
18
19 stemp := substrb(P_string, nlength, 80);
20 fnd_file.put_line(FND_FILE.LOG, stemp);
21 nlength := (nlength + 80);
22
23 END LOOP;
24 END IF;
25
26 EXCEPTION
27 WHEN OTHERS THEN
28
29 IF (SQLCODE <> -20001) THEN
30 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
31 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
32 END IF;
33 APP_EXCEPTION.RAISE_EXCEPTION;
34
35 END Print_log;
36
37
38 Procedure pan_update ( P_errbuf OUT NOCOPY varchar2,
39 P_return_code OUT NOCOPY varchar2,
40 P_vendor_id IN PO_VENDORS.vendor_id%TYPE,
41 P_old_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
42 P_new_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
43 P_debug_flag IN varchar2) is
44
45
46 /* Cursor to lock the jai_ap_tds_thhold_grps */
47
48 Cursor C_lock_thhold_grps is
49 select threshold_grp_id,
50 vendor_id,
51 org_tan_num,
52 vendor_pan_num,
53 section_type,
54 section_code,
55 fin_year,
56 total_invoice_amount,
57 total_invoice_cancel_amount,
58 total_invoice_apply_amount,
59 total_invoice_unapply_amount,
60 total_tax_paid,
61 total_thhold_change_tax_paid,
62 current_threshold_slab_id,
63 created_by,
64 creation_date,
65 last_updated_by,
66 last_update_date,
67 last_update_login
68 from JAI_AP_TDS_THHOLD_GRPS
69 where vendor_id = P_vendor_id
70 and vendor_pan_num = p_old_pan_num
71 order by vendor_id,threshold_grp_id
72 for UPDATE of threshold_grp_id NOWAIT;
73
74
75
76 /* Update the tables in the following order
77
78 (1) ja_in_vendor_tds_info_hdr
79 (2) jai_ap_tds_thhold_grps
80 (3) jai_ap_tds_thhold_xceps
81
82 */
83
84 lv_vendor_site_id_updated varchar2(1000) ;
85 lv_thhold_grp_id_updated varchar2(1000) ;
86 lv_thhold_xcep_id_updated varchar2(1000) ;
87 ln_request_id number;
88 lv_debug_flag varchar2(30);
89 lv_debug_msg varchar2(4000) ;
90
91
92 begin
93
94 lv_debug_flag := nvl(p_debug_flag, 'N');
95
96 lv_vendor_site_id_updated := '';
97 lv_thhold_grp_id_updated := '';
98 lv_thhold_xcep_id_updated := '';
99
100 fnd_file.put_line(FND_FILE.LOG, 'START OF Procedure ');
101
102 ln_request_id := FND_GLOBAL.conc_request_id;
103
104 lv_debug_msg := ' A. Report Parameters';
105
106 If lv_debug_flag = 'Y' then
107 Print_log(lv_debug_flag, lv_debug_msg);
108 End if;
109
110 lv_debug_msg := ' B. request id '|| ln_request_id ;
111
112 If lv_debug_flag = 'Y' then
113 Print_log(lv_debug_flag, lv_debug_msg);
114 End if;
115
116 lv_debug_msg := ' C. debug flag ' || lv_debug_flag;
117
118 If lv_debug_flag = 'Y' then
119 Print_log(lv_debug_flag, lv_debug_msg);
120 End if;
121
122 lv_debug_msg := ' D. old pan ' || P_old_pan_num ;
123
124 If lv_debug_flag = 'Y' then
125 Print_log(lv_debug_flag, lv_debug_msg);
126 End if;
127
128 lv_debug_msg := ' E. new pan ' || P_new_pan_num ;
129
130 If lv_debug_flag = 'Y' then
131 Print_log(lv_debug_flag, lv_debug_msg);
132 End if;
133
134 lv_debug_msg :=' F. vendor id '|| P_vendor_id;
135
136 If lv_debug_flag = 'Y' then
137 Print_log(lv_debug_flag, lv_debug_msg);
138 End if;
139
140 -- Update the jai_ap_tds_thhold_grps
141
142 lv_debug_msg := ' 1. Update JAI_AP_TDS_THHOLD_GRPS';
143
144 If lv_debug_flag = 'Y' then
145 Print_log(lv_debug_flag, lv_debug_msg);
146 End if;
147
148 for thhold_grps in C_lock_thhold_grps
149 loop
150
151 lv_debug_msg := ' 2. Going to update JAI_AP_TDS_THHOLD_GRPS';
152
153 If lv_debug_flag = 'Y' then
154 Print_log(lv_debug_flag, lv_debug_msg);
155 End if;
156
157 update JAI_AP_TDS_THHOLD_GRPS
158 set vendor_pan_num = P_new_pan_num
159 where vendor_id = P_vendor_id
160 and vendor_pan_num = P_old_pan_num
161 and threshold_grp_id = thhold_grps.threshold_grp_id;
162
163 lv_debug_msg := ' 3. Done with update of '|| thhold_grps.threshold_grp_id;
164
165 If lv_debug_flag = 'Y' then
166 Print_log(lv_debug_flag, lv_debug_msg);
167 End if;
168
169 lv_thhold_grp_id_updated := lv_thhold_grp_id_updated || '-' || thhold_grps.threshold_grp_id;
170
171 lv_debug_msg := ' 4. Value of lv_thhold_grp_id_updated '|| lv_thhold_grp_id_updated;
172
173 If lv_debug_flag = 'Y' then
174 Print_log(lv_debug_flag, lv_debug_msg);
175 End if;
176
177
178 end loop;
179
180
181 -- Update the JAI_AP_TDS_VENDOR_HDRS
182 lv_debug_msg := ' 5. Update JAI_AP_TDS_VENDOR_HDRS';
183
184 If lv_debug_flag = 'Y' then
185 Print_log(lv_debug_flag, lv_debug_msg);
186 End if;
187
188 for vndr_tds_hdr in (select vthdr.*
189 from JAI_AP_TDS_VENDOR_HDRS vthdr
190 where vthdr.vendor_id = P_vendor_id
191 and vthdr.pan_no = P_old_pan_num)
192 loop
193
194 lv_debug_msg := ' 6. Going to update JAI_AP_TDS_VENDOR_HDRS';
195
196 If lv_debug_flag = 'Y' then
197 Print_log(lv_debug_flag, lv_debug_msg);
198 End if;
199
200 update JAI_AP_TDS_VENDOR_HDRS
201 set pan_no = P_new_pan_num
202 where vendor_id = vndr_tds_hdr.vendor_id
203 and vendor_site_id = vndr_tds_hdr.vendor_site_id
204 and pan_no = P_old_pan_num;
205
206
207 lv_debug_msg := ' 7. Done with update of vendor '|| vndr_tds_hdr.vendor_id;
208 lv_debug_msg := lv_debug_msg || ' site '|| vndr_tds_hdr.vendor_site_id ;
209
210 If lv_debug_flag = 'Y' then
211 Print_log(lv_debug_flag, lv_debug_msg);
212 End if;
213
214 If vndr_tds_hdr.vendor_site_id <> 0 Then
215 lv_vendor_site_id_updated := lv_vendor_site_id_updated || ' - '||vndr_tds_hdr.vendor_site_id;
216 End if;
217
218 lv_debug_msg := ' 8. Value of lv_vendor_site_id_updated '|| lv_vendor_site_id_updated;
219
220
221 If lv_debug_flag = 'Y' then
222 Print_log(lv_debug_flag, lv_debug_msg);
223 End if;
224
225 end loop;
226
227
228 -- jai_ap_tds_thhold_xceps
229
230 lv_debug_msg := ' 9. Update jai_ap_tds_thhold_xceps';
231
232 If lv_debug_flag = 'Y' then
233 Print_log(lv_debug_flag, lv_debug_msg);
234 End if;
235
236 for thhold_xceps in (select tdsxps.*
237 from jai_ap_tds_thhold_xceps tdsxps
238 where tdsxps.vendor_id = P_vendor_id
239 and vendor_pan = P_old_pan_num)
240 loop
241
242 lv_debug_msg := ' 10. Going to update jai_ap_tds_thhold_xceps';
243
244 If lv_debug_flag = 'Y' then
245 Print_log(lv_debug_flag, lv_debug_msg);
246 End if;
247
248 Update jai_ap_tds_thhold_xceps
249 set vendor_pan = P_new_pan_num
250 where vendor_id = P_vendor_id
251 and vendor_pan = P_old_pan_num;
252
253 lv_debug_msg := ' 11. Done with update of vendor'||P_vendor_id ;
254
255 If lv_debug_flag = 'Y' then
256 Print_log(lv_debug_flag, lv_debug_msg);
257 End if;
258
259 lv_thhold_xcep_id_updated := lv_thhold_xcep_id_updated || '-' || thhold_xceps.threshold_exception_id;
260
261 lv_debug_msg := ' 12. Value of lv_thhold_xcep_id_updated '|| lv_thhold_xcep_id_updated;
262
263 If lv_debug_flag = 'Y' then
264 Print_log(lv_debug_flag, lv_debug_msg);
265 End if;
266
267 end loop;
268
269
270 -- insert a record in jai_ap_tds_pan_changes
271 -- This help us to keep track of PAN changes for the given vendor
272
273
274 lv_debug_msg := ' 13. Inside insert - ';
275
276 If lv_debug_flag = 'Y' then
277 Print_log(lv_debug_flag, lv_debug_msg);
278 End if;
279
280 Insert into jai_ap_tds_pan_changes
281 ( pan_change_id,
282 vendor_id,
283 old_pan_num,
284 new_pan_num,
285 request_id,
286 request_date,
287 vendor_site_id_updated,
288 thhold_grp_id_updated,
289 thhold_xcep_id_updated,
290 creation_date,
291 created_by,
292 last_update_date,
293 last_updated_by,
294 last_update_login
295 )
296 values
297 ( jai_ap_tds_pan_changes_s.nextval,
298 P_vendor_id,
299 P_old_pan_num,
300 P_new_pan_num,
301 ln_request_id,
302 sysdate,
303 lv_vendor_site_id_updated,
304 lv_thhold_grp_id_updated,
305 lv_thhold_xcep_id_updated,
306 sysdate,
307 fnd_global.user_id,
308 sysdate,
309 fnd_global.user_id,
310 fnd_global.login_id
311 );
312
313
314 commit;
315
316 Exception
317 When others then
318
319 IF (SQLCODE < 0) then
320
321 If lv_debug_flag = 'Y' then
322 Print_log(lv_debug_flag,lv_debug_msg);
323 Print_log(lv_debug_flag,SQLERRM);
324 End if;
325 END IF;
326
327 IF (SQLCODE = -54) then
328 If lv_debug_flag = 'Y' then
329 Print_log(lv_debug_flag,'(Pan update :Exception) Vendor to be updated by this process are locked');
330 end if;
331 END IF;
332
333 End pan_update;
334
335 End jai_pan_update_pkg;