1 PACKAGE BODY jai_ar_trx_pkg
2 /* $Header: jai_ar_trx.plb 120.19.12020000.2 2013/03/18 07:45:49 qimeng ship $ */
3 AS
4 --+=======================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| jai_ar_match_tax.plb |
11 --| |
12 --| DESCRIPTION |
13 --| |
14 --| |
15 --| TDD REFERENCE |
16 --| |
17 --| |
18 --| PURPOSE |
19 --| PROCEDURE update_excise_invoice_no |
20 --| PROCEDURE validate_invoice |
21 --| |
22 --| HISTORY |
23 --| Bug 5096787 Added by Lakshmi GopalsamiAdded by Lakshmi Gopalsami |
24 --| Added following parameters in |
25 --| procedure update_excise_invoice_no |
26 --| (1) p_start_date |
27 --| (2) p_end_date |
28 --| |
29 --| Bug 5490479 Added by Harshita |
30 --| In the concurrent program, the multi org category |
31 --| has been set to 'S'. |
32 --| To accomodate the same, derived the org_id from |
33 --| the function mo_global.get_current_org_id |
34 --| and populated an internal variable. |
35 --| Used this variable in all places instead of |
36 --| p_org_id. |
37 --| |
38 --| 08-Jun-2005 Version 116.1 jai_ar_trx -Object is Modified to refer |
39 --| to New DB Entity names in place of Old DB Entity Names|
40 --| as required for CASE COMPLAINCE. |
41 --| |
42 --| 14-Jun-2005 rchandan for bug#4428980, Version 116.2 |
43 --| Modified the object to remove literals from |
44 --| DML statements and CURSORS. |
45 --| |
46 --| 01-Jul-2005 Ramananda for bug#4468353 due to ebtax uptake by AR, |
47 --| File Version 116.3 |
48 --| |
49 --| 29-Jul-2005 Ramananda for bug#4523064, File Version 120.2 |
50 --| Changed the cursor from tax_regime_code_cur |
51 --| to c_tax_regime_code_cur |
52 --| |
53 --| 28/12/2005 4892111 Hjujjuru, File Version 120.3 |
54 --| Modified the Hard Coded value of the tax |
55 --| 'Localization' to 'LOCALIZATION' in all its occurences |
56 --| |
57 --| 12-Jan-2006 rallamse bug#4931630, Version 120.4 |
58 --| Issue : Remove references to ar_vat_tax_all as it |
59 --| is obsoleted. |
60 --| Impacted code:cp_loc_tax_code |
61 --| ar_vat_tax_all.tax_code%TYPE |
62 --| Fix: Changed the cp_loc_tax_code from |
63 --| ar_vat_tax_all.tax_code%type to zx_rates_b.tax |
64 --| |
65 --| 05-Jul-2006 Aiyer for the bug 5369250, Version 120.7 |
66 --| Issue:- |
67 --| The concurrent failes with the following error :- |
68 --| "FDPSTP failed due to ORA-01861: literal does not |
69 --| match format string ORA-06512: at line 1 " |
70 --| |
71 --| Reason:- |
72 --| The procedure update_excise_invoice_no has two |
73 --| parameters p_start_date and p_end_date which are of |
74 --| type date , however the concurrent program passes it |
75 --| in the canonical format and hence the failure. |
76 --| |
77 --| Fix:- |
78 --| Modified the procedure update_excise_invoice_no. |
79 --| Changed the datatype of p_start_date and p_end_date |
80 --| from date to varchar2 as this parameter. |
81 --| Also added the new parameters ld_start_date and |
82 --| ld_end_date. The values in p_start_date and p_end_date |
83 --| would be converted to date format and stored in these |
84 --| local variables |
85 --| Dependency due to this fix:- |
86 --| None |
87 --| |
88 --| 20-Feb-2007 kvaidyan for bug 5894175 |
89 --| Modified cursor c_delivery to accept parameters |
90 --| cp_start_date and cp_end_date, the values are passed |
91 --| into cursor delivery_rec in c_delivery(ld_start_date , |
92 --| ld_end_Date). Added filter condition 'excise_invoice_no|
93 --| IS NOT NULL' to cursor c_ex_inv_no. |
94 --| 17-Sep-2007 anujsax for Bug#5636544, File Version 120.10 |
95 --| forward porting for R11 bug 5629319 into R12 bug5636544|
96 --| |
97 --| 13-Oct-2008 CSahoo for bug#6685050, File Version 120.11 |
98 --| Issue: Enhancement for including the vat invoice number|
99 --| in the order reference field in AR invoice. |
100 --| Fix: Modified the code in the procedure |
101 --| update_excise_invoice_no. Added the cursor |
102 --| c_same_inv_no.Modified the cursor c_ex_inv_no. |
103 --| |
104 --| 20-Nov-2008 JMEENA for Bug#6391684( FP of 6386592) |
105 --| Issue: AUTOINVOICE FOR CERTAIN CTO SALES ORDERS GOING |
106 --| INTO ERRORS.Because Excise Invoice# and VAT Invoice# |
107 --| are not getting imported into AR) |
108 --| Reason: Import program is considering the Model item |
109 --| while importing the excise and vat invoice number. |
110 --| As the Config item is shipped for ATO Orders,Excise |
111 --| Invoice and VAT Invoice are not getting imported |
112 --| Fix: Modified the cursor c_ex_inv_no. Included an |
113 --| condition to check the order_line_id against the |
114 --| line_id of 'CONFIG' item |
115 --| |
116 --| 19-nov-08 vkaranam for bug#5194107 |
117 --| forwardported the changes in 115 bug#5174616 |
118 --| |
119 --| 05-DEC-2008 JMEENA for bug#7621541 |
120 --| Reverted the changes of bug#5636544 as this should |
121 --| not go in 12.1.1 release because this bug is still |
122 --| open. |
123 --| |
124 --| 19-Jan-2010 Bo Li modified for VAT/Excise Number shown |
125 --| in AR transaction workbench and Bug 9303168# can |
126 --| be tracked |
127 --| 02-Apr-2010 Allen Yang modified for bug 9485355 |
128 --| (12.1.3 non-shippable Enhancement) |
129 --| Modified procedure update_excise_invoice_no |
130 --| to enable processing of non-shippable items |
131 --| 01-Jul-2010 Bug 9569551 |
132 --| Description: Wrong VAT Invoice Number gets updated |
133 --| on all transactions when Invoice Number parameter is |
134 --| not provided during submission of India - Importing |
135 --| Excise/VAT Invoice Numbers in AR |
136 --| Fix: Reinitialize VAT Invoice Number and Date for |
137 --| each iteration |
138 --| |
139 --| 05-Jan-2010 Bo Li modified for bug#9710105 |
140 --| Issue:The non-shippable item line will always have no |
141 --| EXCISE INV# so that non-shippable SO line can be |
142 --| considered as the objects which need import |
143 --| EXCISE/VAT invoice#. |
144 --| Fix : Modified the coursor c_delivery |
145 --+======================================================================*/
146 PROCEDURE update_excise_invoice_no(
147 retcode OUT NOCOPY varchar2,
148 errbuf OUT NOCOPY varchar2,
149 p_org_id number, /* Bug 5096787. Added by Lakshmi Gopalsami Added following two parameters.*/
150 p_start_date VARCHAR2, /* modified by aiyer for the bug 5369250 */
151 p_end_date VARCHAR2 DEFAULT NULL, /* modified by aiyer for the bug 5369250 */
152 p_customer_trx_id number
153 )
154 IS
155
156 ln_org_id number ; -- Harshita for Bug 5490479
157
158 /*
159 || Code modified for the bug 4474256
160 || Change the c_delivery
161 */
162 CURSOR c_delivery(cp_start_Date IN DATE , cp_end_date IN DATE )
163 IS
164 SELECT trx.customer_trx_id,
165 rctl.customer_trx_line_id ,
166 rctl.interface_line_attribute3 ,
167 rctl.interface_line_attribute6
168 FROM
169 ra_customer_trx_all trx ,
170 ra_customer_trx_lines_all rctl ,
171 jai_ar_trx_lines jrctl, -- Changed for Bug 5894175
172 -- ja_in_ra_customer_trx_lines jrctl
173 JAI_AR_TRXS jrct --bug#5194107
174 WHERE
175 trx.customer_trx_id = rctl.customer_trx_id
176 AND jrct.customer_trx_id = trx.customer_trx_id --5194107
177 AND rctl.line_type = 'LINE'
178 AND trunc(trx.trx_date) BETWEEN trunc(cp_start_date) AND nvl(trunc(cp_end_date),trunc(sysdate))
179 AND trx.customer_trx_id = nvl(p_customer_trx_id,trx.customer_trx_id)
180 AND trx.org_id = p_org_id
181 -- AND trx.created_from = 'RAXTRX' -- modified by Bo Li for display VAT/Excise inv # in the reference
182 -- by manual AR transation
183 AND rctl.customer_trx_line_id = jrctl.customer_trx_line_id
184 --Added and mofidied by Bo Li for bug#9710105 on 05-JUL-2010 Begin
185 ----------------------------------------------------------------------------------------------
186 -- AND ( jrctl.excise_invoice_no IS NULL OR jrct.vat_invoice_no IS NULL ) ;--bug#5194107
187 AND (((jrctl.excise_invoice_no IS NULL OR jrct.vat_invoice_no IS NULL) AND rctl.interface_line_attribute3 > 0)
188 OR (jrct.vat_invoice_no IS NULL AND rctl.interface_line_attribute3 = 0));
189 -----------------------------------------------------------------------------------------------
190 --Added and mofidied by Bo Li for bug#9710105 on 05-JUL-2010 End
191
192 --added for bug#6685050,csahoo
193 CURSOR c_same_inv_no (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%type)
194 IS
195 SELECT jror.attribute_Value
196 FROM JAI_RGM_ORG_REGNS_V jror, jai_ar_trxs jat
197 WHERE regime_code = 'VAT'
198 AND jror.attribute_type_code = jai_constants.regn_type_others
199 AND jror.attribute_code = jai_constants.attr_code_same_inv_no
200 AND jror.organization_id = jat.organization_id
201 AND jror.location_id = jat.location_id
202 AND jat.customer_trx_id = cp_customer_trx_id;
203
204
205 CURSOR c_ex_inv_no(p_delivery_id varchar2, p_order_line_id varchar2) is
206 SELECT excise_invoice_no , excise_invoice_date,
207 vat_invoice_no, vat_invoice_date --added for bug#6685050
208 FROM JAI_OM_WSH_LINES_ALL
209 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
210 -- WHERE delivery_id = p_delivery_id
211 WHERE (delivery_id IS NULL OR delivery_id = p_delivery_id)
212 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
213 AND (order_line_id = p_order_line_id
214 /* Added for bug#6391684, Starts */
215 OR order_line_id in (SELECT line_id FROM oe_order_lines_all
216 WHERE header_id in (SELECT header_id
217 FROM oe_order_lines_all
218 WHERE line_id = p_order_line_id)
219 AND item_type_code = 'CONFIG')
220 ) /* Added for bug#6391684, Ends */
221 AND ( excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL ) ; -- Bug Fixed 5894175
222
223 ld_start_date DATE ;
224 ld_end_date DATE ;
225 --Commented below for bug#7621541
226 --ln_excise_invoice_no JAI_AR_TRX_LINES.excise_invoice_no%TYPE; --Added by Anujsax for bug#5636544
227 ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_No%TYPE; --added for bug#6685050
228 lv_same_inv_no VARCHAR2(1); --added for bug#6685050
229 lv_updt_exc_no VARCHAR2(1); --added for bug#6685050
230
231 --start additions for bug#5194107
232 ln_last_updated_by JAI_AR_TRX_LINES.LAST_UPDATED_BY%TYPE ;
233 ln_last_update_login JAI_AR_TRX_LINES.LAST_UPDATE_LOGIN%TYPE ;
234 ld_vat_invoice_date JAI_AR_TRXs.VAT_INVOICE_DATE%TYPE;
235
236 --add by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168#,begin
237 ----------------------------------------------------------------------------------------------------------------
238 lv_display_flag VARCHAR2(1);
239 lv_excise_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
240 --------------------------------------------------------------------------------------------------------------
241 --add by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168#,end
242 --Added by Zhiwei for bug#14040855 JAI Trigger elimination begin
243 ---------------------------------------------------------
244 cursor c_get_rec(cn_customer_trx_id number)
245 is
246 select *
247 from jai_ar_trxs
248 where customer_trx_id = cn_customer_trx_id;
249
250 t_rec_new jai_ar_trxs%rowtype;
251 t_rec_old jai_ar_trxs%rowtype;
252
253 lv_action VARCHAR2(20);
254 lv_return_message VARCHAR2(2000);
255 lv_return_code VARCHAR2(100) ;
256 le_error EXCEPTION;
257
258 ---------------------------------------------------------
259 --Added by Zhiwei for bug#14040855 JAI Trigger elimination end
260 BEGIN
261 /*
262 || start of 5369250
263 ||code added by aiyer for the bug 5369250
264 */
265 ld_start_date := fnd_date.canonical_to_date(p_start_date);
266 ld_end_date := fnd_date.canonical_to_date(p_end_date) ;
267
268 fnd_file.put_line(FND_FILE.LOG,'Input parameters are p_org_id-> ' || p_org_id
269 ||' ,p_start_date -> ' || p_start_date
270 ||' ,p_end_date -> ' || p_end_date
271 ||' ,p_customer_trx_id -> ' || p_customer_trx_id); --bug#5194107
272
273 /* End of 5369250 */
274
275 ln_last_updated_by := fnd_global.user_id; --bug#5194107
276 ln_last_update_login := fnd_global.login_id; --bug#5194107
277
278 ln_org_id := mo_global.get_current_org_id() ; -- Harshita for Bug 5490479
279
280 fnd_file.put_line(FND_FILE.LOG,'Processing Customer Trx id : ' || p_customer_trx_id);
281
282 for delivery_rec in c_delivery(ld_start_date , ld_end_Date)
283 Loop
284 fnd_file.put_line(FND_FILE.LOG,'Delivery id : ' || delivery_rec.interface_line_attribute3);
285 fnd_file.put_line(FND_FILE.LOG,'Customer Trx Line id : ' ||delivery_rec.customer_trx_line_id);
286 --added for bug#6685050, start
287 OPEN c_same_inv_no(delivery_rec.customer_trx_id);
288 FETCH c_same_inv_no INTO lv_same_inv_no;
289 CLOSE c_same_inv_no;
290 -- bug#6685050, end
291 /*
292 Bug 9569551 - Reinitialize VAT Invoice Number and Date for each iteration. Else the last valid value
293 gets updated for all transactions
294 */
295 ln_vat_invoice_no := NULL;
296 ld_vat_invoice_date := NULL;
297 For ex_inv_rec in c_ex_inv_no(to_number(delivery_rec.interface_line_attribute3), to_number(delivery_rec.interface_line_attribute6))--bug#5194107
298 loop
299 update JAI_AR_TRX_LINES
300 set excise_invoice_no = ex_inv_rec.excise_invoice_no ,
301 excise_invoice_date = ex_inv_rec.excise_invoice_date
302 where customer_trx_line_id = delivery_rec.customer_trx_line_id;
303 /* --Commented below for bug#7621541
304 --Added the below by Anujsax for bug#5636544
305 IF ln_excise_invoice_no IS NULL AND ex_inv_rec.excise_invoice_no IS NOT NULL THEN
306 ln_excise_invoice_no := ex_inv_rec.excise_invoice_no;
307 END IF;
308 --ended by anujsax for bug#5636544
309 */
310 --added for bug#6685050, start
311 IF ln_vat_invoice_no IS NULL AND ex_inv_rec.vat_invoice_no IS NOT NULL THEN
312 ln_vat_invoice_no := ex_inv_rec.vat_invoice_no;
313 END IF;
314 -- bug#6685050, end
315
316 ld_vat_invoice_date := ex_inv_rec.vat_invoice_date; --BUG#5194107
317
318 --add by Bo Li for VAT/Excise Number shown in AR transaction workbench(Bug 9303168#) on 19-Jan-2010 ,begin
319 -----------------------------------------------------------------------------------------------------------------
320 lv_excise_invoice_no := ex_inv_rec.excise_invoice_no;
321 -----------------------------------------------------------------------------------------------------------------
322 --add by Bo Li for VAT/Excise Number shown in AR transaction workbench(Bug 9303168#) on 19-Jan-2010, end
323 end loop;
324
325 --start additions by vkaranam for bug#5194107
326 IF ln_vat_invoice_no IS NOT NULL or ld_vat_invoice_date IS NOT NULL THEN
327
328 fnd_file.put_line(FND_FILE.LOG,' VAT invoice No -> '||ln_vat_invoice_no
329 || ', VAT inv date -> ' ||ld_vat_invoice_date
330 ||' for customer_trx_id -> '||delivery_rec.customer_trx_id );
331
332 --Added by Zhiwei for bug#14040855 JAI Trigger elimination begin
333 ---------------------------------------------------------
334 open c_get_rec(delivery_rec.customer_trx_id);
335 fetch c_get_rec into t_rec_old;
336 close c_get_rec;
337
338 ---------------------------------------------------------
339 --Added by Zhiwei for bug#14040855 JAI Trigger elimination end
340
341 UPDATE JAI_AR_TRXS
342 SET vat_invoice_no = nvl( ln_vat_invoice_no ,vat_invoice_no ) ,
343 vat_invoice_date = nvl( ld_vat_invoice_date,vat_invoice_date ) ,
344 last_update_date = sysdate ,
345 last_updated_by = ln_last_updated_by ,
346 last_update_login = ln_last_update_login
347 WHERE customer_trx_id = delivery_rec.customer_trx_id;
348
349 --Added by Zhiwei for bug#14040855 JAI Trigger elimination begin
350 ---------------------------------------------------------
351 open c_get_rec(delivery_rec.customer_trx_id);
352 fetch c_get_rec into t_rec_new;
353 close c_get_rec;
354
355
356
357 if(t_rec_new.once_completed_flag = 'Y')then
358
359 lv_action := jai_constants.updating ;
360
361 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
362 pr_old => t_rec_old ,
363 pr_new => t_rec_new ,
364 pv_action => lv_action ,
365 pv_return_code => lv_return_code ,
366 pv_return_message => lv_return_message
367 );
368
369 IF lv_return_code <> jai_constants.successful then
370 RAISE le_error;
371 END IF;
372
373 end if;
374 ---------------------------------------------------------
375 --Added by Zhiwei for bug#14040855 JAI Trigger elimination end
376 END IF;
377 --end additions for bug#5194107
378
379
380 lv_updt_exc_no := FND_PROFILE.value('JAI_INCLUDE_EXC_INV_AR_TRX_REF'); --added for bug#6685050
381
382 --add by Bo Li for VAT/Excise Number shown in AR transaction workbench(Bug 9303168#) on 19-Jan-2010,begin
383 -----------------------------------------------------------------------------------------------------------------
384 -- when then profile "JAI:Include Excise and VAT Invoice Number in AR transactions - Referencde" set to "Yes"
385 lv_display_flag := FND_PROFILE.VALUE('JAI_DISP_VAT_EXC_INV_AR_TRX_REF');
386
387 fnd_file.put_line( FND_FILE.LOG
388 , 'JAI:Include Excise and VAT Invoice Number in AR transactions - Referencde is set to ' ||
389 lv_display_flag);
390
391 fnd_file.put_line( FND_FILE.LOG
392 , 'delivery_rec.customer_trx_id :'||delivery_rec.customer_trx_id);
393 fnd_file.put_line( FND_FILE.LOG
394 , 'lv_excise_invoice_no :' || lv_excise_invoice_no);
395 fnd_file.put_line( FND_FILE.LOG
396 , 'lv_vat_invoice_no :' || ln_vat_invoice_no);
397
398 IF ( lv_excise_invoice_no IS NOT NULL OR ln_vat_invoice_no IS NOT NULL)
399 AND nvl(lv_display_flag, 'N') = 'Y'
400 THEN
401 JAI_AR_MATCH_TAX_PKG.display_vat_invoice_no( pn_customer_trx_id => delivery_rec.customer_trx_id
402 , pv_excise_invoice_no => lv_excise_invoice_no
403 , pv_vat_invoice_no => ln_vat_invoice_no
404 );
405
406 fnd_file.put_line(FND_FILE.LOG,'The invoice number has been displayed successfully!');
407 END IF;
408 -----------------------------------------------------------------------------------------------------------------
409 --Add by Bo Li for VAT/Excise Number shown in AR transaction workbench(Bug 9303168#) on 19-Jan-2010,End
410 end loop;
411 commit;
412 retcode :='0';
413 exception
414 when others then
415 rollback;
416 retcode :='2';
417 errbuf := sqlerrm;
418 end update_excise_invoice_no;
419
420 procedure validate_invoice ( p_customer_trx_id IN RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID%TYPE ,
421 p_trx_number IN RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE , /* should not be used in a where clause as this is not unique */
422 p_error_flag OUT NOCOPY VARCHAR2 ,
423 p_error_message OUT NOCOPY VARCHAR2
424 )
425
426 /* --------------------------------------------------------------------------------------
427 Filename:
428
429 Change History:
430
431 Date Bug Remarks
432 --------- ---------- -------------------------------------------------------------
433
434 07-Feb-2006 Aiyer for the bug 5021243, Version 120.5
435 Issue:-
436 Error plsql numeric or value error encontered while
437 execution of the procedure jai_ar_trx_pkg.validate_invoice
438
439 Fix:-
440 The error was occuring as the variable lv_tax_regime_code was
441 declared as Number however the cursor
442 jai_ar_trx_pkg.c_tax_regime_code_cur fetches a varchar value
443 into this variable.
444 Changed the variable from number to
445 ZX_RATES_B.TAX_REGIME_CODE%TYPE.Also changed the parameter
446 cp_tax_regime_code of the cursor cur_chk_non_il_taxes from
447 number to ZX_RATES_B.TAX_REGIME_CODE%TYPE.
448
449 Dependency due to this fix:-
450 None
451 --------------------------------------------------------------------------------------------------
452 */
453
454 IS
455
456
457
458 V_ORG_ID number ;
459 /*Changed the Variable lv_tax_regime_code datatype from NUMBER to
460 ZX_RATES_B.TAX_REGIME_CODE%TYPE for the bug 5021243 */
461 lv_tax_regime_code ZX_RATES_B.TAX_REGIME_CODE%TYPE ;
462
463 CURSOR ORG_CUR IS
464 SELECT ORG_ID
465 FROM RA_CUSTOMER_TRX_ALL
466 WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
467 /*
468 || Modified by Ramananda for bug#4468353
469 || Check whether any other taxes other than localizations taxes exist.
470 || rallamse bug#4931630 changed the cp_loc_tax_code from ar_vat_tax_all.tax_code%type
471 || to zx_rates_b.tax
472 */
473 CURSOR cur_chk_non_il_taxes(cp_loc_tax_code ZX_RATES_B.TAX%TYPE,
474 cp_line_type_tax RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%TYPE,
475 cp_line_type_freight RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%TYPE,
476 cp_tax_regime_code ZX_RATES_B.TAX_REGIME_CODE%TYPE
477 /*Variable datatype changed from number to
478 ZX_RATES_B.TAX_REGIME_CODE%TYPE for the bug 5021243 */
479 ) --rchandan for bug#4428980
480 IS
481 SELECT
482 1
483 FROM ra_customer_trx_lines_all rctl,
484 zx_rates_b zrb,
485 zx_party_tax_profile zptp
486 WHERE
487 zrb.tax = 'LOCALIZATION' -- 'Localization' , Harshita for Bug 4907217
488 AND zrb.tax_regime_code = cp_tax_regime_code
489 AND zrb.tax_status_code = 'STANDARD'
490 AND zrb.active_flag = 'Y'
491 AND trunc(sysdate) between trunc(zrb.effective_from) and trunc(nvl(zrb.effective_to, sysdate))
492 AND zrb.content_owner_id = zptp.party_tax_profile_id
493 AND rctl.vat_tax_id = zrb.tax_rate_id
494 AND rctl.org_id = zptp.party_id
495 AND zrb.tax <> cp_loc_tax_code
496 AND rctl.customer_trx_id = p_customer_trx_id
497 AND rctl.line_type IN (cp_line_type_tax,cp_line_type_freight) ;--rchandan for bug#4428980
498
499
500
501 /*
502 ||Check whether the accounting rules have been used by the invoice
503 ||This code is commented for the time being, as the functionality is not quite clear
504 */
505 CURSOR cur_chk_account_rule
506 IS
507 SELECT
508 1
509 FROM
510 ra_customer_trx_lines_all
511 WHERE
512 customer_trx_id = p_customer_trx_id AND
513 accounting_rule_id IS NOT NULL;
514
515 /*
516 ||Check whether the Revenue Recognition Program has been already run for an invoice with rules
517 */
518 CURSOR cur_revrec_run( p_acc_class ra_cust_trx_line_gl_dist_all.account_class%TYPE )
519 IS
520 SELECT
521 1
522 FROM
523 ra_cust_trx_line_gl_dist_all gl_dist,
524 ra_customer_trx_all rctx
525 WHERE
526 rctx.customer_trx_id = gl_dist.customer_trx_id AND
527 rctx.invoicing_rule_id IS NOT NULL AND
528 gl_dist.account_class = p_acc_class AND
529 gl_dist.account_set_flag = 'N' AND
530 gl_dist.latest_rec_flag = 'Y' AND
531 gl_dist.customer_trx_id = p_customer_trx_id ;
532
533 /*
534 ||Check whether the invoice has been gl posted
535 */
536 CURSOR cur_chk_gl_posting
537 IS
538 SELECT
539 1
540 FROM
541 ra_cust_trx_line_gl_dist_all
542 WHERE
543 customer_trx_id = p_customer_trx_id AND
544 account_set_flag = 'N' AND
545 posting_control_id <> -3 AND
546 rownum = 1;
547
548 ln_exists NUMBER;
549
550 BEGIN
551
552 /*
553 || Initialize the variables
554 */
555 p_error_message := null;
556 p_error_flag := 'SS';
557
558 /*************************
559 ||############################################################################################
560 ||Check Whether any other tax other than India Localization Exists, IF yes report error
561 ||############################################################################################
562 *************************/
563
564 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
565 OPEN ORG_CUR;
566 FETCH ORG_CUR INTO V_ORG_ID;
567 CLOSE ORG_CUR;
568
569 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
570 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
571 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
572 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
573
574
575 OPEN cur_chk_non_il_taxes('LOCALIZATION', 'TAX','FREIGHT',lv_tax_regime_code ); -- 'Localization' , Harshita for Bug 4907217
576 FETCH cur_chk_non_il_taxes INTO ln_exists ;
577
578 IF cur_chk_non_il_taxes%FOUND THEN
579 CLOSE cur_chk_non_il_taxes;
580 p_error_flag := 'EE' ;
581 p_error_message := 'Invoice lines have taxes other than localization type of tax for the invoice TRX No'||p_trx_number||'. Please delete it and reprocess the invoice';
582 return;
583 END IF;
584 CLOSE cur_chk_non_il_taxes;
585
586 /*************************
587 ||############################################################################################
588 ||Check whether the invoice uses accounting rules, IF yes report error and return
589 ||############################################################################################
590 *************************/
591 OPEN cur_chk_account_rule;
592 FETCH cur_chk_account_rule INTO ln_exists ;
593 IF cur_chk_account_rule%FOUND THEN
594 CLOSE cur_chk_account_rule;
595 p_error_flag := 'EE' ;
596 p_error_message := 'Invoice with TRX No '||p_trx_number||' uses accounting rules. Cannot process invoice ';
597 return;
598 END IF;
599 CLOSE cur_chk_account_rule;
600
601
602
603
604 /*************************
605 ||#############################################################################################
606 ||Check Whether the Revenue Recognition Program has been already been run for an invoice with
607 ||rules. If yes then report error
608 ||#############################################################################################
609 *************************/
610 OPEN cur_revrec_run('REC');
611 FETCH cur_revrec_run INTO ln_exists ;
612 IF cur_revrec_run%FOUND THEN
613
614 /*
615 || Invoice has already been revenue recognised
616 || cannot process the taxes related to the record, return.
617 */
618
619 CLOSE cur_revrec_run;
620 p_error_flag := 'EE' ;
621 p_error_message := 'Invoice has already been revenue recognised. Taxes related to invoice TRX No'||p_trx_number||' cannot be processed' ;
622 return;
623 END IF;
624 CLOSE cur_revrec_run;
625
626
627
628
629
630 /*************************
631 ||############################################################################################
632 ||Check whether the invoice has already been gl posted. IF yes report error and return
633 ||############################################################################################
634 *************************/
635 OPEN cur_chk_gl_posting;
636 FETCH cur_chk_gl_posting INTO ln_exists ;
637 IF cur_chk_gl_posting%FOUND THEN
638 /*
639 Invoice has already been gl posted,
640 cannot process the taxes related to the record, return.
641 */
642 CLOSE cur_chk_gl_posting;
643 p_error_flag := 'EE' ;
644 p_error_message := 'Invoice TRX No '||p_trx_number||' has already been GL posted. Taxes related to this invoice cannot be processed' ;
645 return;
646 END IF;
647 CLOSE cur_chk_gl_posting;
648
649
650
651 /*
652 || set out variables to success as no error has been encountered above.
653 */
654 p_error_flag := 'SS' ;
655 p_error_message := null ;
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 p_error_flag := 'UE';
660 p_error_message := 'Unexpected error while processing invoice TRX No'||p_trx_number||' - '||substr(SQLERRM,1,300);
661 END validate_invoice;
662
663 --==========================================================================
664 -- PROCEDURE NAME:
665 -- update_reference Public
666 --
667 -- DESCRIPTION:
668 -- This procedure is written that update the reference field in AR
669 -- transaction workbench when the AR invoice has been created manually
670 --
671 -- ER NAME/BUG#
672 -- VAT/Excise Number shown in AR transaction workbench
673 -- Bug 9303168
674 --
675 -- PARAMETERS:
676 -- In: pn_customer_trx_id Indicates the customer trx id
677 --
678 -- DESIGN REFERENCES:
679 -- TD named "VAT Invoice Number on AR Invoice Technical Design.doc" has been
680 -- referenced in the section 6.1
681 --
682 -- CALL FROM
683 -- The concurrent program "India - Excise/VAT Number in Transactions Workbench"
684 --
685 -- CHANGE HISTORY:
686 -- 25-Jan-2010 Bo Li Created by Bo Li
687
688 --==========================================================================
689 PROCEDURE update_reference
690 ( retcode OUT NOCOPY VARCHAR2
691 , errbuf OUT NOCOPY VARCHAR2
692 , pn_customer_trx_id NUMBER
693 )
694 IS
695
696 -- get vat invoice number
697 CURSOR get_vat_invoice_cur IS
698 SELECT vat_invoice_no
699 FROM JAI_AR_TRXS
700 WHERE customer_trx_id = pn_customer_trx_id;
701 -- get excise invoice number
702 CURSOR get_exc_inv_no_cur IS
703 SELECT excise_invoice_no
704 FROM JAI_AR_TRX_LINES
705 WHERE customer_trx_id = pn_customer_trx_id;
706
707 CURSOR get_st_invoice_cur IS
708 SELECT st_inv_number
709 FROM JAI_AR_TRXS
710 WHERE customer_trx_id = pn_customer_trx_id;
711 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
712
713 lv_vat_invoice_no JAI_AR_TRXS.VAT_INVOICE_NO%Type;
714 lv_excise_invoice_no JAI_AR_TRX_LINES.excise_invoice_no%type;
715 lv_st_invoice_num JAI_AR_TRXS.st_inv_number%Type;
716 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
717 lv_display_flag VARCHAR2(240);
718 lv_procedure_name VARCHAR2(40):='update_reference';
719 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
720 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
721
722 BEGIN
723 --logging for debug
724 IF (ln_proc_level >= ln_dbg_level)
725 THEN
726 FND_LOG.STRING( ln_proc_level
727 , lv_procedure_name || '.begin'
728 , 'Enter procedure'
729 );
730 END IF; --l_proc_level>=l_dbg_level
731
732 -- get the values of VAT/Excise invoice number
733 OPEN get_vat_invoice_cur;
734 FETCH get_vat_invoice_cur
735 INTO lv_vat_invoice_no;
736 CLOSE get_vat_invoice_cur;
737
738 OPEN get_exc_inv_no_cur;
739 FETCH get_exc_inv_no_cur
740 INTO lv_excise_invoice_no;
741 CLOSE get_exc_inv_no_cur;
742
743 OPEN get_st_invoice_cur;
744 FETCH get_st_invoice_cur
745 INTO lv_st_invoice_num;
746 CLOSE get_st_invoice_cur;
747
748 --get the profile value
749 lv_display_flag := FND_PROFILE.VALUE('JAI_DISP_VAT_EXC_INV_AR_TRX_REF');
750
751 fnd_file.put_line( FND_FILE.LOG
752 , 'pn_customer_trx_id :' || pn_customer_trx_id);
753 fnd_file.put_line( FND_FILE.LOG
754 , 'lv_excise_invoice_no :' || lv_excise_invoice_no);
755 fnd_file.put_line( FND_FILE.LOG
756 , 'lv_vat_invoice_no :' || lv_vat_invoice_no);
757
758 -- update the reference field in the AR transaction workbench
759 IF (lv_excise_invoice_no IS NOT NULL OR lv_vat_invoice_no IS NOT NULL OR lv_st_invoice_num IS NOT NULL)
760 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
761 AND nvl(lv_display_flag, 'N') = 'Y'
762 THEN
763 JAI_AR_MATCH_TAX_PKG.display_vat_invoice_no(pn_customer_trx_id => pn_customer_trx_id,
764 pv_excise_invoice_no => lv_excise_invoice_no,
765 pv_vat_invoice_no => lv_vat_invoice_no);
766
767 fnd_file.put_line( FND_FILE.LOG
768 , 'The invoice number has been displayed successfully!');
769 END IF;
770
771 COMMIT;
772 retcode := '0';
773
774 --logging for debug
775 IF (ln_proc_level >= ln_dbg_level)
776 THEN
777 FND_LOG.STRING( ln_proc_level
778 , lv_procedure_name || '.end'
779 , 'Exit procedure'
780 );
781 END IF; --l_proc_level>=l_dbg_level
782 EXCEPTION
783 WHEN OTHERS THEN
784 ROLLBACK;
785 retcode := '2';
786 errbuf := SQLERRM;
787 END update_reference;
788
789 END jai_ar_trx_pkg;
[Home] [Help]