[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_TRX_HEADERS_PKG
Source
1 PACKAGE BODY JAI_AR_TRX_HEADERS_PKG AS
2 /* $Header: jai_ar_trx_headers.plb 120.0.12020000.2 2013/03/19 00:26:29 vkaranam noship $ */
3 /*------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY
5 ------------------------------------------------------------------------------------------------------------
6 Sl.No. Date Developer BugNo Version Remarks
7 ------------------------------------------------------------------------------------------------------------
8 1. 13-June-2012 qinglei 14040855 115.1 Created the initial version
9
10 --------------------------------------------------------------------------------------------------------------*/
11
12 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
15 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
17 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
19 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'JAI.PLSQL.JAI_AR_TRX_HEADERS_PKG.';
20 G_PACKAGE_NAME CONSTANT VARCHAR2(100) := 'JAI_AR_TRX_HEADERS_PKG';
21 FUNCTION check_reg_dealer(pn_customer_id NUMBER, pn_site_use_id NUMBER)
22 return boolean
23
24 IS
25 ln_address_id NUMBER;
26 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
27
28 CURSOR c_get_address is
29 SELECT hzcas.cust_acct_site_id
30 FROM hz_cust_site_uses_all hzcsu, hz_cust_acct_sites_all hzcas
31 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
32 AND hzcsu.site_use_id = pn_site_use_id
33 AND hzcas.cust_account_id = pn_customer_id;
34
35 CURSOR c_regno(pn_address_id NUMBER) IS
36 SELECT vat_Reg_no
37 FROM JAI_CMN_CUS_ADDRESSES
38 WHERE customer_id = pn_customer_id
39 AND address_id = pn_address_id;
40
41 BEGIN
42
43 open c_get_address;
44 fetch c_get_address
45 into ln_address_id;
46 close c_get_address;
47
48 IF ln_address_id IS NOT NULL THEN
49
50 open c_regno(ln_address_id);
51 fetch c_regno
52 into lv_regno;
53 close c_regno;
54 END IF;
55
56 IF lv_regno IS NULL THEN
57 return(false);
58 ELSE
59 return(true);
60 END IF;
61
62 END check_reg_dealer;
63 PROCEDURE POPULATE_JAI_AR_TRXS(pr_new T_REC%TYPE,
64 pv_action VARCHAR2,
65 pv_return_code OUT NOCOPY VARCHAR2,
66 pv_return_message OUT NOCOPY VARCHAR2) IS
67 v_org_id NUMBER;
68 v_loc_id NUMBER;
69 v_reg_code VARCHAR2(30);
70 v_excise_invoice_no NUMBER;
71 v_update_rg VARCHAR2(1);
72 v_update_rg23d_flag VARCHAR2(1);
73 v_reg_type VARCHAR2(10);
74 v_complete_flag VARCHAR2(1);
75
76 v_parent_trx_number VARCHAR2(20);
77 v_trans_type VARCHAR2(30);
78 lv_api_name CONSTANT VARCHAR2(200) := 'POPULATE_AR_TRXS';
79 lv_debug_info VARCHAR2(4000);
80
81 CURSOR loc_app_cur IS
82 SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
83
84 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
85 SELECT register_code
86 FROM JAI_OM_OE_BOND_REG_HDRS
87 WHERE organization_id = p_org_id
88 AND location_id = p_loc_id
89 AND register_id in (SELECT register_id
90 FROM JAI_OM_OE_BOND_REG_DTLS
91 WHERE order_type_id = pr_new.batch_source_id
92 AND order_flag = 'N');
93
94 CURSOR organization_cur IS
95 SELECT organization_id, location_id
96 FROM JAI_AR_TRXS
97 WHERE trx_number = v_parent_trx_number;
98
99 CURSOR transaction_type_cur IS
100 SELECT type
101 FROM RA_CUST_TRX_TYPES_ALL
102 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
103 AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
104
105 CURSOR localization_header_info IS
106 SELECT organization_id, location_id, update_rg_flag
107 FROM JAI_AR_TRXS
108 WHERE customer_trx_id = pr_new.previous_customer_trx_id;
109
110 v_currency_code gl_sets_of_books.currency_code%TYPE;
111 CURSOR curr(c_sob NUMBER) IS
112 SELECT currency_code
113 FROM gl_sets_of_books
114 WHERE set_of_books_id = c_sob;
115 V_CURR CURR%ROWTYPE;
116 BEGIN
117 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
118 FND_LOG.STRING(G_LEVEL_PROCEDURE,
119 G_MODULE_NAME || lv_api_name,
120 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
121 END IF;
122
123 pv_return_code := jai_constants.successful;
124 v_update_rg := 'Y';
125 v_update_rg23d_flag := 'Y';
126 v_org_id := -1;
127 v_parent_trx_number := pr_new.recurred_from_trx_number;
128
129 OPEN curr(pr_new.set_of_books_id);
130 FETCH curr
131 into v_curr;
132 CLOSE curr;
133
134 OPEN transaction_type_cur;
135 FETCH transaction_type_cur
136 INTO v_trans_type;
137 CLOSE transaction_type_cur;
138
139 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
140 FND_LOG.STRING(G_LEVEL_PROCEDURE,
141 G_MODULE_NAME || lv_api_name,
142 'v_trans_type = ' || v_trans_type);
143 END IF;
144
145 IF NVL(v_trans_type, 'N') NOT IN ('CM', 'INV', 'DM') THEN
146 RETURN;
147 END IF;
148
149 IF pr_new.created_from = 'ARXREC' THEN
150 /*IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
151 lv_debug_info := 'Insert table JAI_AR_TRX_COPY_HDR_T. pr_new.trx_number = ' ||
152 pr_new.trx_number || 'pr_new.CUSTOMER_TRX_ID = ' ||
153 pr_new.CUSTOMER_TRX_ID || 'v_parent_trx_number = ' ||
154 v_parent_trx_number || 'pr_new.BATCH_SOURCE_ID = ' ||
155 pr_new.BATCH_SOURCE_ID || 'pr_new.CREATED_FROM = ' ||
156 pr_new.CREATED_FROM || 'pr_new.CREATION_DATE = ' ||
157 pr_new.CREATION_DATE || 'pr_new.CREATED_BY = ' ||
158 pr_new.CREATED_BY || 'pr_new.LAST_UPDATE_DATE = ' ||
159 pr_new.LAST_UPDATE_DATE ||
160 'pr_new.LAST_UPDATED_BY = ' ||
161 pr_new.LAST_UPDATED_BY ||
162 'pr_new.LAST_UPDATE_LOGIN = ' ||
163 pr_new.LAST_UPDATE_LOGIN;
164 FND_LOG.STRING(G_LEVEL_PROCEDURE,
165 G_MODULE_NAME || lv_api_name,
166 lv_debug_info);
167 END IF;
168
169 INSERT INTO JAI_AR_TRX_COPY_HDR_T
170 (TRX_NUMBER,
171 CUSTOMER_TRX_ID,
172 RECURRED_FROM_TRX_NUMBER,
173 BATCH_SOURCE_ID,
174 CREATED_FROM,
175 CREATION_DATE,
176 CREATED_BY,
177 LAST_UPDATE_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN)
180 VALUES
181 (pr_new.trx_number,
182 pr_new.CUSTOMER_TRX_ID,
183 v_parent_trx_number,
184 pr_new.BATCH_SOURCE_ID,
185 pr_new.CREATED_FROM,
186 pr_new.CREATION_DATE,
187 pr_new.CREATED_BY,
188 pr_new.LAST_UPDATE_DATE,
189 pr_new.LAST_UPDATED_BY,
190 pr_new.LAST_UPDATE_LOGIN);*/
191 NULL;
192 ELSE
193 IF pr_new.created_from = 'ARXTWCMI' THEN
194 IF pr_new.previous_customer_trx_id IS NOT NULL Then
195 OPEN localization_header_info;
196 FETCH localization_header_info
197 INTO v_org_id, v_loc_id, v_update_rg;
198 CLOSE localization_header_info;
199 ELSE
200 RETURN;
201 END IF;
202 ELSE
203 OPEN loc_app_cur;
204 FETCH loc_app_cur
205 INTO v_org_id, v_loc_id;
206 CLOSE loc_app_cur;
207
208 IF NVL(v_org_id, 999999) = 999999 THEN
209 IF v_parent_trx_number IS NULL THEN
210 RETURN;
211 ELSE
212 OPEN organization_cur;
213 FETCH organization_cur
214 INTO v_org_id, v_loc_id;
215 CLOSE organization_cur;
216 END IF;
217 END IF;
218 IF NVL(v_org_id, 999999) = 999999 THEN
219 RETURN;
220 END IF;
221
222 OPEN register_code_cur(v_org_id, v_loc_id);
223 FETCH register_code_cur
224 INTO v_reg_code;
225 CLOSE register_code_cur;
226
227 IF v_reg_code IS NULL THEN
228 v_update_rg := 'N';
229 v_update_rg23d_flag := 'N';
230 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE',
231 '23D_EXPORT_EXCISE',
232 '23D_EXPORT_WITHOUT_EXCISE',
233 '23D_DOM_WITHOUT_EXCISE') THEN
234 v_update_rg23d_flag := 'Y';
235 v_update_rg := 'N';
236 ELSIF v_reg_code IN ('DOMESTIC_EXCISE',
237 'EXPORT_EXCISE',
238 'BOND_REG',
239 'DOM_WITHOUT_EXCISE') THEN
240 v_update_rg := 'Y';
241 v_update_rg23d_flag := 'N';
242 END IF;
243 END IF;
244
245 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
246 lv_debug_info := 'Insert table JAI_AR_TRXS. pr_new.CUSTOMER_TRX_ID = ' ||
247 pr_new.CUSTOMER_TRX_ID || ', V_ORG_ID = ' ||
248 V_ORG_ID || ', V_LOC_ID = ' || V_LOC_ID ||
249 ', pr_new.TRX_NUMBER = ' || pr_new.TRX_NUMBER ||
250 ', V_UPDATE_RG = ' || V_UPDATE_RG ||
251 ', v_update_rg23d_flag = ' || v_update_rg23d_flag ||
252 ', pr_new.COMPLETE_FLAG = ' ||
253 pr_new.COMPLETE_FLAG ||
254 ', pr_new.BATCH_SOURCE_ID = ' ||
255 pr_new.BATCH_SOURCE_ID ||
256 ', pr_new.SET_OF_BOOKS_ID = ' ||
257 pr_new.SET_OF_BOOKS_ID ||
258 ', pr_new.PRIMARY_SALESREP_ID = ' ||
259 pr_new.PRIMARY_SALESREP_ID ||
260 ', pr_new.INVOICE_CURRENCY_CODE = ' ||
261 pr_new.INVOICE_CURRENCY_CODE ||
262 ', pr_new.EXCHANGE_RATE_TYPE = ' ||
263 pr_new.EXCHANGE_RATE_TYPE ||
264 ', pr_new.EXCHANGE_DATE = ' ||
265 pr_new.EXCHANGE_DATE ||
266 ', pr_new.EXCHANGE_RATE = ' ||
267 pr_new.EXCHANGE_RATE || ', pr_new.CREATED_FROM = ' ||
268 pr_new.CREATED_FROM || ', pr_new.CREATION_DATE = ' ||
269 pr_new.CREATION_DATE || ', pr_new.CREATED_BY = ' ||
270 pr_new.CREATED_BY ||
271 ', pr_new.LAST_UPDATE_DATE = ' ||
272 pr_new.LAST_UPDATE_DATE ||
273 ', pr_new.LAST_UPDATE_LOGIN = ' ||
274 pr_new.LAST_UPDATE_LOGIN ||
275 ', pr_new.LAST_UPDATED_BY = ' ||
276 pr_new.LAST_UPDATED_BY ||
277 ', pr_new.LEGAL_ENTITY_ID ' ||
278 pr_new.LEGAL_ENTITY_ID;
279 FND_LOG.STRING(G_LEVEL_PROCEDURE,
280 G_MODULE_NAME || lv_api_name,
281 lv_debug_info);
282 END IF;
283
284 INSERT INTO JAI_AR_TRXS
285 (CUSTOMER_TRX_ID,
286 ORGANIZATION_ID,
287 LOCATION_ID,
288 TRX_NUMBER,
289 UPDATE_RG_FLAG,
290 UPDATE_RG23d_FLAG,
291 ONCE_COMPLETED_FLAG,
292 BATCH_SOURCE_ID,
293 SET_OF_BOOKS_ID,
294 PRIMARY_SALESREP_ID,
295 INVOICE_CURRENCY_CODE,
296 EXCHANGE_RATE_TYPE,
297 EXCHANGE_DATE,
298 EXCHANGE_RATE,
299 CREATED_FROM,
300 CREATION_DATE,
301 CREATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATE_LOGIN,
304 LAST_UPDATED_BY,
305 LEGAL_ENTITY_ID,
306 COMPLETE_FLAG,
307 SHIP_TO_CUSTOMER_ID,
308 SHIP_TO_SITE_USE_ID)
309 VALUES
310 (pr_new.CUSTOMER_TRX_ID,
311 V_ORG_ID,
312 V_LOC_ID,
313 pr_new.TRX_NUMBER,
314 V_UPDATE_RG,
315 v_update_rg23d_flag,
316 pr_new.COMPLETE_FLAG,
317 pr_new.BATCH_SOURCE_ID,
318 pr_new.SET_OF_BOOKS_ID,
319 pr_new.PRIMARY_SALESREP_ID,
320 pr_new.INVOICE_CURRENCY_CODE,
321 pr_new.EXCHANGE_RATE_TYPE,
322 pr_new.EXCHANGE_DATE,
323 pr_new.EXCHANGE_RATE,
324 pr_new.CREATED_FROM,
325 pr_new.CREATION_DATE,
326 pr_new.CREATED_BY,
327 pr_new.LAST_UPDATE_DATE,
328 pr_new.LAST_UPDATE_LOGIN,
329 pr_new.LAST_UPDATED_BY,
330 pr_new.LEGAL_ENTITY_ID,
331 pr_new.COMPLETE_FLAG,
332 pr_new.SHIP_TO_CUSTOMER_ID,
333 pr_new.SHIP_TO_SITE_USE_ID);
334 END IF;
335
336 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING(G_LEVEL_PROCEDURE,
338 G_MODULE_NAME || lv_api_name,
339 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
340 END IF;
341 EXCEPTION
342 WHEN OTHERS THEN
343 Pv_return_code := jai_constants.unexpected_error;
344 Pv_return_message := 'Encountered an error in JAI_AR_TRX_HEADERS_PKG.POPULATE_AR_TRXS. ' ||
345 substr(SQLERRM, 1, 1900);
346 IF G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL THEN
347 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
348 G_MODULE_NAME || lv_api_name,
349 SUBSTR('Encountered an error in JAI_AR_TRX_HEADERS_PKG.POPULATE_AR_TRXS. ' ||
350 SQLERRM,
351 1,
352 4000));
353 END IF;
354 END POPULATE_JAI_AR_TRXS;
355
356 PROCEDURE POPULATE_COPIED_TRANSACTIONS(PR_NEW T_REC%TYPE,
357 PV_ACTION VARCHAR2,
358 PV_RETURN_CODE OUT NOCOPY VARCHAR2,
359 PV_RETURN_MESSAGE OUT NOCOPY VARCHAR2) IS
360 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
361 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
362 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
363 SELECT *
364 FROM jai_ar_trx_lines
365 WHERE customer_trx_line_id = pn_customer_trx_line_id;
366 lv_action VARCHAR2(20);
367 lv_return_message VARCHAR2(2000);
368 lv_return_code VARCHAR2(100);
369 le_error EXCEPTION;
370
371 v_created_from Varchar2(30);
372 v_header_id Number;
373 v_customer_trx_line_id Number;
374 v_recurred_from_trx_number Varchar2(20);
375 v_trx_number Varchar2(20);
376 v_once_completed_flag Varchar2(1);
377 x Number;
378 v_batch_source_id Number := 0;
379 v_parent_header_id Number;
380 v_line_tax_amount Number := 0;
381 v_header_tax_amount Number := 0;
382 v_last_update_date Date;
383 v_last_updated_by Number;
384 v_creation_date Date;
385 v_created_by Number;
386 v_last_update_login Number;
387 v_service_type VARCHAR2(30);
388 lv_api_name CONSTANT VARCHAR2(100) := 'POPULATE_COPIED_TRANSACTIONS';
389
390 /* CURSOR temp_fetch IS
391 SELECT trx_number,
392 customer_trx_id,
393 recurred_from_trx_number,
394 batch_source_id,
395 created_from,
396 creation_date,
397 created_by,
398 last_update_date,
399 last_updated_by,
400 last_update_login
401 FROM JAI_AR_TRX_COPY_HDR_T
402 ORDER BY customer_trx_id;*/
403 CURSOR temp_fetch IS
404 SELECT trx_number,
405 customer_trx_id,
406 recurred_from_trx_number,
407 batch_source_id,
408 created_from,
409 creation_date,
410 created_by,
411 last_update_date,
412 last_updated_by,
413 last_update_login
414 FROM RA_CUSTOMER_TRX_ALL
415 WHERE customer_trx_id = pr_new.customer_trx_id
416 ORDER BY customer_trx_id;
417
418 CURSOR ONCE_COMPLETE_FLAG_CUR(p_header_id IN NUMBER,
419 p_batch_source_id IN Number) IS
420 SELECT once_completed_flag, 1
421 FROM JAI_AR_TRXS
422 WHERE customer_trx_id = p_header_id
423 AND NVL(batch_source_id, 0) = p_batch_source_id;
424
425 CURSOR parent_header_id(p_recurred_from_trx_number IN Varchar2,
426 p_batch_source_id IN Number) IS
427 SELECT a.customer_trx_id
428 FROM JAI_AR_TRXS a
429 WHERE a.trx_number = p_recurred_from_trx_number
430 AND NVL(batch_source_id, 0) = p_batch_source_id;
431
432 CURSOR LINES_INFO_CUR(p_parent_header_id IN Number) IS
433 SELECT customer_trx_line_id,
434 line_number,
435 description,
436 inventory_item_id,
437 unit_code,
438 quantity,
439 tax_category_id,
440 auto_invoice_flag,
441 unit_selling_price,
442 line_amount,
443 gl_date,
444 tax_amount,
445 total_amount,
446 assessable_value
447 FROM JAI_AR_TRX_LINES
448 WHERE customer_trx_id = p_parent_header_id
449 ORDER BY customer_trx_line_id;
450
451 CURSOR TAX_INFO_CUR(p_parent_line_id IN NUMBER) IS
452 SELECT a.tax_line_no,
453 a.precedence_1,
454 a.precedence_2,
455 a.precedence_3,
456 a.precedence_4,
457 a.precedence_5,
458 a.precedence_6,
459 a.precedence_7,
460 a.precedence_8,
461 a.precedence_9,
462 a.precedence_10,
463 a.tax_id,
464 a.tax_rate,
465 a.qty_rate,
466 a.uom,
467 a.tax_amount,
468 a.base_tax_amount,
469 a.func_tax_amount,
470 b.end_date valid_date,
471 b.tax_type
472 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
473 WHERE a.link_to_cust_trx_line_id = p_parent_line_id
474 AND a.tax_id = b.tax_id
475 ORDER BY a.tax_line_no;
476
477 CURSOR HEADER_INFO_CUR(p_recurred_from_trx_number IN Varchar2,
478 p_batch_source_id IN Number) IS
479 SELECT CUSTOMER_TRX_ID,
480 ORGANIZATION_ID,
481 LOCATION_ID,
482 UPDATE_RG_FLAG,
483 UPDATE_RG23D_FLAG,
484 TAX_AMOUNT,
485 LINE_AMOUNT,
486 TOTAL_AMOUNT,
487 BATCH_SOURCE_ID,
488 legal_entity_id,
489 complete_flag,
490 ship_to_customer_id,
491 ship_to_site_use_id
492 FROM JAI_AR_TRXS
493 WHERE trx_number = p_recurred_from_trx_number
494 AND NVL(batch_source_id, 0) = p_batch_source_id;
495 BEGIN
496 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING(G_LEVEL_PROCEDURE,
498 G_MODULE_NAME || lv_api_name,
499 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
500 END IF;
501
502 pv_return_code := jai_constants.successful;
503
504 OPEN temp_fetch;
505 FETCH temp_fetch
506 INTO v_trx_number,
507 v_header_id,
508 v_recurred_from_trx_number,
509 v_batch_source_id,
510 v_created_from,
511 v_creation_date,
512 v_created_by,
513 v_last_update_date,
514 v_last_updated_by,
515 v_last_update_login;
516 CLOSE temp_fetch;
517
518 --DELETE JAI_AR_TRX_COPY_HDR_T WHERE customer_trx_id = v_header_id;
519
520 IF v_trx_number IS NULL THEN
521 Return;
522 END IF;
523 IF v_created_from <> 'ARXREC' THEN
524 RETURN;
525 END IF;
526
527 OPEN ONCE_COMPLETE_FLAG_CUR(v_header_id, v_batch_source_id);
528 FETCH ONCE_COMPLETE_FLAG_CUR
529 INTO v_once_completed_flag, x;
530 CLOSE ONCE_COMPLETE_FLAG_CUR;
531 IF NVL(v_once_completed_flag, 'N') = 'Y' THEN
532 RETURN;
533 END IF;
534
535 OPEN parent_header_id(v_recurred_from_trx_number, v_batch_source_id);
536 FETCH parent_header_id
537 INTO v_parent_header_id;
538 CLOSE parent_header_id;
539
540 IF NVL(x, 0) <> 1 THEN
541
542 FOR hdr in HEADER_INFO_CUR(v_recurred_from_trx_number,
543 v_batch_source_id) LOOP
544 INSERT INTO JAI_AR_TRXS
545 (customer_trx_id,
546 organization_id,
547 location_id,
548 update_rg23d_flag,
549 update_rg_flag,
550 trx_number,
551 once_completed_flag,
552 line_amount,
553 batch_source_id,
554 created_from,
555 creation_date,
556 created_by,
557 last_update_date,
558 last_updated_by,
559 last_update_login,
560 legal_entity_id,
561 COMPLETE_FLAG,
562 SHIP_TO_CUSTOMER_ID,
563 SHIP_TO_SITE_USE_ID)
564 VALUES
565 (v_header_id,
566 hdr.organization_id,
567 hdr.location_id,
568 hdr.update_rg23d_flag,
569 hdr.update_rg_flag,
570 v_trx_number,
571 'N',
572 hdr.line_amount,
573 hdr.batch_source_id,
574 v_created_from,
575 v_creation_date,
576 v_created_by,
577 v_last_update_date,
578 v_last_updated_by,
579 v_last_update_login,
580 hdr.legal_entity_id,
581 hdr.COMPLETE_FLAG,
582 hdr.SHIP_TO_CUSTOMER_ID,
583 hdr.SHIP_TO_SITE_USE_ID);
584 END LOOP;
585 END IF;
586
587 v_service_type := JAI_AR_RCTLA_TRIGGER_PKG.get_service_type(NVL(pr_new.SHIP_TO_CUSTOMER_ID,
588 pr_new.BILL_TO_CUSTOMER_ID),
589 NVL(pr_new.SHIP_TO_SITE_USE_ID,
590 pr_new.BILL_TO_SITE_USE_ID),
591 'C');
592
593 FOR rec in LINES_INFO_CUR(v_parent_header_id) LOOP
594
595 INSERT INTO JAI_AR_TRX_LINES
596 (customer_trx_line_id,
597 line_number,
598 customer_trx_id,
599 description,
600 inventory_item_id,
601 unit_code,
602 quantity,
603 tax_category_id,
604 auto_invoice_flag,
605 unit_selling_price,
606 line_amount,
607 gl_date,
608 assessable_value,
609 creation_date,
610 created_by,
611 last_update_date,
612 last_updated_by,
613 last_update_login,
614 service_type_code)
615 VALUES
616 (ra_customer_trx_lines_s.nextval,
617 rec.line_number,
618 v_header_id,
619 rec.description,
620 rec.inventory_item_id,
621 rec.unit_code,
622 rec.quantity,
623 rec.tax_category_id,
624 rec.auto_invoice_flag,
625 rec.unit_selling_price,
626 rec.line_amount,
627 rec.gl_date,
628 rec.assessable_value,
629 v_creation_date,
630 v_created_by,
631 v_last_update_date,
632 v_last_updated_by,
633 v_last_update_login,
634 v_service_type)
635 returning customer_trx_line_id into v_customer_trx_line_id;
636 OPEN c_jai_ar_trx_lines(v_customer_trx_line_id);
637 FETCH c_jai_ar_trx_lines
638 INTO t_jai_line_rec_new;
639 CLOSE c_jai_ar_trx_lines;
640
641 lv_action := JAI_CONSTANTS.INSERTING;
642 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
643 pr_new => t_jai_line_rec_new,
644 pv_action => lv_action,
645 pv_return_code => lv_return_code,
646 pv_return_message => lv_return_message);
647 IF PV_RETURN_CODE <> JAI_CONSTANTS.successful THEN
648 RAISE le_error;
649 END IF;
650
651 FOR rec1 in TAX_INFO_CUR(rec.customer_trx_line_id) LOOP
652 IF rec1.valid_date < sysdate THEN
653 rec1.tax_amount := 0;
654 rec1.base_tax_amount := 0;
655 rec1.func_tax_amount := 0;
656 END IF;
657 INSERT INTO JAI_AR_TRX_TAX_LINES
658 (customer_trx_line_id,
659 link_to_cust_trx_line_id,
660 tax_line_no,
661 precedence_1,
662 precedence_2,
663 precedence_3,
664 precedence_4,
665 precedence_5,
666 precedence_6,
667 precedence_7,
668 precedence_8,
669 precedence_9,
670 precedence_10,
671 tax_id,
672 tax_rate,
673 qty_rate,
674 uom,
675 tax_amount,
676 base_tax_amount,
677 func_tax_amount,
678 creation_date,
679 created_by,
680 last_update_date,
681 last_updated_by,
682 last_update_login)
683 VALUES
684 (ra_customer_trx_lines_s.nextval,
685 v_customer_trx_line_id,
686 rec1.tax_line_no,
687 rec1.precedence_1,
688 rec1.precedence_2,
689 rec1.precedence_3,
690 rec1.precedence_4,
691 rec1.precedence_5,
692 rec1.precedence_6,
693 rec1.precedence_7,
694 rec1.precedence_8,
695 rec1.precedence_9,
696 rec1.precedence_10,
697 rec1.tax_id,
698 rec1.tax_rate,
699 rec1.qty_rate,
700 rec1.uom,
701 rec1.tax_amount,
702 rec1.base_tax_amount,
703 rec1.func_tax_amount,
704 v_creation_date,
705 v_created_by,
706 v_last_update_date,
707 v_last_updated_by,
708 v_last_update_login);
709
710 IF rec1.tax_type <> 'TDS' THEN
711 v_line_tax_amount := nvl(v_line_tax_amount, 0) +
712 nvl(rec1.tax_amount, 0);
713 END IF;
714
715 IF rec1.tax_type in ('Excise', 'Addl. Excise', 'Other Excise') THEN
716 v_header_tax_amount := nvl(v_header_tax_amount, 0) +
717 nvl(rec1.tax_amount, 0);
718 END IF;
719
720 END LOOP;
721
722 OPEN c_jai_ar_trx_lines(v_customer_trx_line_id);
723 FETCH c_jai_ar_trx_lines
724 INTO t_jai_line_rec_old;
725 CLOSE c_jai_ar_trx_lines;
726
727 UPDATE JAI_AR_TRX_LINES
728 SET tax_amount = v_line_tax_amount,
729 total_amount = nvl(line_amount, 0) + v_line_tax_amount
730 WHERE customer_trx_line_id = v_customer_trx_line_id;
731
732 OPEN c_jai_ar_trx_lines(v_customer_trx_line_id);
733 FETCH c_jai_ar_trx_lines
734 INTO t_jai_line_rec_new;
735 CLOSE c_jai_ar_trx_lines;
736 lv_action := JAI_CONSTANTS.UPDATING;
737 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
738 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
739 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
740 (t_jai_line_rec_new.payment_Register IS NULL) AND
741 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
742 (t_jai_line_rec_new.Customer_Trx_Id <>
743 t_jai_line_rec_old.Customer_Trx_Id)) THEN
744 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
745 pr_new => t_jai_line_rec_new,
746 pv_action => lv_action,
747 pv_return_code => lv_return_code,
748 pv_return_message => lv_return_message);
749
750 IF lv_return_code <> jai_constants.successful then
751 RAISE le_error;
752 END IF;
753 END IF;
754
755 v_line_tax_amount := 0;
756 END LOOP;
757
758 UPDATE JAI_AR_TRXS
759 SET tax_amount = v_header_tax_amount,
760 total_amount = nvl(line_amount, 0) + v_header_tax_amount
761 WHERE customer_trx_id = v_header_id;
762 v_header_tax_amount := 0;
763
764 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
765 FND_LOG.STRING(G_LEVEL_PROCEDURE,
766 G_MODULE_NAME || lv_api_name,
767 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
768 END IF;
769 EXCEPTION
770 WHEN OTHERS THEN
771 Pv_return_code := jai_constants.unexpected_error;
772 Pv_return_message := 'Encountered an error in JAI_AR_TRX_HEADERS_PKG.POPULATE_COPIED_TRANSACTIONS. ' ||
773 substr(SQLERRM, 1, 1900);
774 IF G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL THEN
775 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
776 G_MODULE_NAME || lv_api_name,
777 SUBSTR('Encountered an error in JAI_AR_TRX_HEADERS_PKG.POPULATE_COPIED_TRANSACTIONS. ' ||
778 SQLERRM,
779 1,
780 4000));
781 END IF;
782 END POPULATE_COPIED_TRANSACTIONS;
783
784 PROCEDURE UPDATE_TRX_NUMBER(pr_old t_jai_rec%type,
785 pr_new t_rec%type,
786 pv_action varchar2,
787 pv_return_code out NOCOPY varchar2,
788 pv_return_message out NOCOPY varchar2) IS
789 v_trans_type Varchar2(30);
790 v_trx_number varchar2(30);
791 v_ref_line_id varchar2(30);
792
793 Cursor transaction_type_cur IS
794 Select type
795 From RA_CUST_TRX_TYPES_ALL
796 Where cust_trx_type_id = pr_new.cust_trx_type_id
797 And NVL(org_id, 0) = NVL(pr_new.org_id, 0);
798
799 v_currency_code gl_sets_of_books.currency_code%type;
800 lv_api_name CONSTANT VARCHAR2(200) := 'UPDATE_TRX_NUMBER';
801 lv_debug_info VARCHAR2(4000);
802 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
803 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
804 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
805 SELECT *
806 FROM jai_ar_trx_lines
807 WHERE customer_trx_line_id = pn_customer_trx_line_id;
808 lv_action VARCHAR2(20);
809 lv_return_message VARCHAR2(2000);
810 lv_return_code VARCHAR2(100);
811 le_error EXCEPTION;
812 BEGIN
813 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
814 FND_LOG.STRING(G_LEVEL_PROCEDURE,
815 G_MODULE_NAME || lv_api_name,
816 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
817 END IF;
818 pv_return_code := jai_constants.successful;
819 v_trx_number := pr_new.Trx_Number;
820 v_ref_line_id := pr_new.interface_header_attribute7;
821
822 IF pr_new.created_from = 'RAXTRX' THEN
823 IF pr_new.CUSTOMER_TRX_ID <> pr_old.CUSTOMER_TRX_ID THEN
824 Update JAI_AR_TRXS
825 Set Customer_Trx_ID = pr_new.Customer_Trx_ID
826 Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
827
828 FOR c_jai_ar_trx_lines_rec IN (SELECT customer_trx_line_id
829 FROM JAI_AR_TRX_LINES
830 WHERE customer_trx_id =
831 pr_old.customer_trx_id) LOOP
832
833 OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
834 FETCH c_jai_ar_trx_lines
835 INTO t_jai_line_rec_old;
836 CLOSE c_jai_ar_trx_lines;
837
838 Update JAI_AR_TRX_LINES
839 Set Customer_Trx_Id = pr_new.Customer_Trx_ID
840 Where Customer_Trx_ID = pr_old.Customer_Trx_ID
841 AND customer_trx_line_id =
842 c_jai_ar_trx_lines_rec.customer_trx_line_id;
843
844 OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
845 FETCH c_jai_ar_trx_lines
846 INTO t_jai_line_rec_new;
847 CLOSE c_jai_ar_trx_lines;
848 lv_action := JAI_CONSTANTS.UPDATING;
849 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
850 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
851 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
852 (t_jai_line_rec_new.payment_Register IS NULL) AND
853 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
854 (t_jai_line_rec_new.Customer_Trx_Id <>
855 t_jai_line_rec_old.Customer_Trx_Id)) THEN
856 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
857 pr_new => t_jai_line_rec_new,
858 pv_action => lv_action,
859 pv_return_code => lv_return_code,
860 pv_return_message => lv_return_message);
861
862 IF lv_return_code <> jai_constants.successful then
863 RAISE le_error;
864 END IF;
865 END IF;
866 END LOOP;
867 END IF;
868
869 Update JAI_AR_TRXS
870 Set Trx_Number = pr_new.Trx_Number
871 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
872 END IF;
873
874 OPEN transaction_type_cur;
875 FETCH transaction_type_cur
876 INTO v_trans_type;
877 CLOSE transaction_type_cur;
878 IF NVL(v_trans_type, 'N') in ('CM', 'DM') THEN
879
880 IF pr_new.created_from = 'RAXTRX' THEN
881 Update JAI_AR_TRXS
882 Set Trx_Number = pr_new.Trx_Number
883 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
884 ELSE
885
886 Update JAI_AR_TRXS
887 Set Trx_Number = pr_new.Trx_Number,
888 Once_Completed_Flag = NVL(pr_new.Complete_Flag, 'N')
889 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
890 END IF;
891
892 ELSIF NVL(v_trans_type, 'N') = 'INV' THEN
893 Update JAI_AR_TRXS
894 Set Trx_Number = pr_new.Trx_Number
895 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
896 END IF;
897 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING(G_LEVEL_PROCEDURE,
899 G_MODULE_NAME || lv_api_name,
900 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
901 END IF;
902 EXCEPTION
903 WHEN OTHERS THEN
904 Pv_return_code := jai_constants.unexpected_error;
905 Pv_return_message := 'Encountered an error in ' || G_PACKAGE_NAME || '.' ||
906 lv_api_name || substr(sqlerrm, 1, 1900);
907 END UPDATE_TRX_NUMBER;
908
909 PROCEDURE RECALCULATE_TAX(pr_old t_jai_rec%type,
910 pr_new t_rec%type,
911 pv_action varchar2,
912 pv_return_code out NOCOPY varchar2,
913 pv_return_message out NOCOPY varchar2) IS
914 lv_api_name CONSTANT VARCHAR2(200) := 'RECALCULATE_TAX';
915 lv_debug_info VARCHAR2(4000);
916 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
917 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
918 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
919 SELECT *
920 FROM jai_ar_trx_lines
921 WHERE customer_trx_line_id = pn_customer_trx_line_id;
922 lv_action VARCHAR2(20);
923 lv_return_message VARCHAR2(2000);
924 lv_return_code VARCHAR2(100);
925 le_error EXCEPTION;
926 v_customer_id Number;
927 v_org_id Number;
928 v_header_id Number;
929 v_ship_to_site_use_id Number;
930 v_created_from Varchar2(30);
931 v_last_update_date Date;
932 v_last_updated_by Number;
933 v_creation_date Date;
934 v_created_by Number;
935 v_last_update_login Number;
936 c_from_currency_code Varchar2(15);
937 c_conversion_type Varchar2(30);
938 c_conversion_date Date;
939 c_conversion_rate Number;
940 v_books_id Number;
941 v_inventory_item_id Number;
942 v_address_id Number;
943 v_once_completed_flag Varchar2(1);
944 v_organization_id Number;
945 v_location_id NUMBER;
946 v_tax_category_id Number;
947 v_price_list Number := 0;
948 v_price_list_uom_code Varchar2(10);
949 v_conversion_rate Number;
950 v_price_list_val Number := 0;
951 v_converted_rate Number;
952 v_line_tax_amount Number := 0;
953 v_trx_date Date;
954 v_service_type VARCHAR2(30);
955
956 Cursor address_cur(p_ship_to_site_use_id IN Number) IS
957 SELECT cust_acct_site_id address_id
958 FROM hz_cust_site_uses_all A
959 WHERE A.site_use_id = p_ship_to_site_use_id;
960
961 CURSOR price_list_cur(p_customer_id IN Number,
962 p_inventory_item_id IN Number,
963 p_address_id IN Number DEFAULT 0,
964 v_uom_code VARCHAR2,
965 p_trx_date DATE) IS
966 select list_price, unit_code
967 from so_price_list_lines
968 where price_list_id in
969 (select price_list_id
970 from JAI_CMN_CUS_ADDRESSES
971 where customer_id = p_customer_id
972 and address_id = p_address_id)
973 and inventory_item_id = p_inventory_item_id
974 and unit_code = v_uom_code
975 AND NVL(end_date_active, SYSDATE) >= p_trx_date;
976
977 CURSOR ORG_CUR IS
978 SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
979
980 CURSOR organization_cur IS
981 SELECT organization_id, location_id
982 FROM JAI_AR_TRXS
983 WHERE trx_number = pr_new.recurred_from_trx_number;
984
985 CURSOR ONCE_COMPLETE_FLAG_CUR IS
986 SELECT once_completed_flag
987 FROM JAI_AR_TRXS
988 WHERE customer_trx_id = v_header_id;
989
990 v_trans_type Varchar2(30);
991
992 Cursor transaction_type_cur IS
993 Select a.type
994 From RA_CUST_TRX_TYPES_ALL a
995 Where a.cust_trx_type_id = pr_new.cust_trx_type_id
996 And a.org_id = v_org_id;
997
998 Cursor Ar_Line_Cur IS
999 Select Customer_Trx_Line_ID,
1000 Inventory_Item_ID,
1001 Unit_Code,
1002 Line_Amount,
1003 Quantity,
1004 unit_selling_price
1005 From JAI_AR_TRX_LINES
1006 Where Customer_Trx_ID = v_header_id;
1007
1008 ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1009
1010 LN_TCS_EXISTS NUMBER;
1011 LN_TCS_REGIME_ID JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
1012 LN_THRESHOLD_SLAB_ID JAI_AP_TDS_THHOLD_SLABS.THRESHOLD_SLAB_ID%TYPE;
1013 LN_THRESHOLD_TAX_CAT_ID JAI_AP_TDS_THHOLD_TAXES.TAX_CATEGORY_ID%TYPE;
1014
1015 CURSOR GC_CHK_RGM_TAX_EXISTS(CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE,
1016 CP_RGM_TAX_TYPE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE,
1017 CP_TAX_CATEGORY_ID JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_ID%TYPE) IS
1018 SELECT COUNT(1)
1019 FROM JAI_CMN_TAX_CTG_LINES CATL,
1020 JAI_CMN_TAXES_ALL CODES,
1021 JAI_REGIME_TAX_TYPES_V JRTTV
1022 WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
1023 AND CATL.TAX_ID = CODES.TAX_ID
1024 AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
1025 AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
1026
1027 CURSOR GC_GET_REGIME_ID(CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE) IS
1028 SELECT REGIME_ID
1029 FROM JAI_RGM_DEFINITIONS
1030 WHERE REGIME_CODE = CP_REGIME_CODE;
1031
1032 LV_PROCESS_FLAG VARCHAR2(2);
1033 LV_PROCESS_MESSAGE VARCHAR2(1998);
1034 BEGIN
1035 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1037 G_MODULE_NAME || lv_api_name,
1038 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1039 END IF;
1040 pv_return_code := jai_constants.successful;
1041 v_customer_id := pr_new.Ship_To_Customer_ID;
1042 v_org_id := NVL(pr_new.Org_ID, 0);
1043 v_header_id := pr_new.customer_trx_id;
1044 v_ship_to_site_use_id := NVL(pr_new.Ship_To_Site_Use_ID, 0);
1045 v_created_from := pr_new.Created_From;
1046 v_last_update_date := pr_new.last_update_date;
1047 v_last_updated_by := pr_new.last_updated_by;
1048 v_creation_date := pr_new.creation_date;
1049 v_created_by := pr_new.created_by;
1050 v_last_update_login := pr_new.last_update_login;
1051 c_from_currency_code := pr_new.invoice_currency_code;
1052 c_conversion_type := pr_new.exchange_rate_type;
1053 c_conversion_date := NVL(pr_new.exchange_date, pr_new.trx_date);
1054 c_conversion_rate := NVL(pr_new.exchange_rate, 0);
1055 v_books_id := pr_new.set_of_books_id;
1056 v_trx_date := pr_new.trx_date;
1057
1058 OPEN transaction_type_cur;
1059 FETCH transaction_type_cur
1060 INTO v_trans_type;
1061 CLOSE transaction_type_cur;
1062 IF NVL(v_trans_type, 'N') <> 'INV' THEN
1063 Return;
1064 END IF;
1065
1066 OPEN ONCE_COMPLETE_FLAG_CUR;
1067 FETCH ONCE_COMPLETE_FLAG_CUR
1068 INTO v_once_completed_flag;
1069 CLOSE ONCE_COMPLETE_FLAG_CUR;
1070 IF NVL(v_once_completed_flag, 'N') = 'Y' THEN
1071 RETURN;
1072 END IF;
1073 IF v_created_from in ('RAXTRX', 'ARXREC') THEN
1074 RETURN;
1075 END IF;
1076 IF pr_new.invoice_currency_code <> pr_old.invoice_currency_code THEN
1077
1078 UPDATE JAI_AR_TRXS
1079 SET invoice_currency_code = pr_new.invoice_currency_code,
1080 exchange_rate_type = pr_new.exchange_rate_type,
1081 exchange_date = pr_new.exchange_date,
1082 exchange_rate = pr_new.exchange_rate
1083 WHERE customer_trx_id = pr_new.customer_trx_id;
1084
1085 END IF;
1086
1087 IF (pr_new.ship_to_customer_id <> pr_old.ship_to_customer_id)
1088 OR (pr_new.ship_to_site_use_id <> pr_old.ship_to_site_use_id) THEN
1089
1090 UPDATE JAI_AR_TRXS
1091 SET ship_to_customer_id = pr_new.ship_to_customer_id,
1092 ship_to_site_use_id = pr_new.ship_to_site_use_id
1093 WHERE customer_trx_id = pr_new.customer_trx_id;
1094 END IF;
1095
1096 OPEN ORG_CUR;
1097 FETCH ORG_CUR
1098 INTO v_organization_id, v_location_id;
1099 CLOSE ORG_CUR;
1100 IF NVL(v_organization_id, 999999) = 999999 THEN
1101
1102 OPEN organization_cur;
1103 FETCH organization_cur
1104 INTO v_organization_id, v_location_id;
1105 CLOSE organization_cur;
1106 END IF;
1107 IF NVL(v_organization_id, 999999) = 999999 THEN
1108 RETURN;
1109 END IF;
1110 OPEN address_cur(v_ship_to_site_use_id);
1111 FETCH address_cur
1112 INTO v_address_id;
1113 CLOSE address_cur;
1114
1115 FOR rec In Ar_Line_Cur LOOP
1116 v_tax_category_id := '';
1117 v_price_list := '';
1118 v_price_list_uom_code := '';
1119 v_conversion_rate := '';
1120 v_price_list_val := '';
1121 v_converted_rate := '';
1122 v_line_tax_amount := 0;
1123
1124 DELETE JAI_AR_TRX_TAX_LINES
1125 WHERE LINK_TO_CUST_TRX_LINE_ID = Rec.CUSTOMER_TRX_LINE_ID;
1126
1127 IF v_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
1128 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(v_organization_id,
1129 v_customer_id,
1130 v_ship_to_site_use_id,
1131 rec.inventory_item_id,
1132 v_header_id,
1133 rec.customer_trx_line_id,
1134 v_tax_category_id);
1135 ELSE
1136 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(v_organization_id,
1137 rec.inventory_item_id,
1138 v_tax_category_id);
1139
1140 END IF;
1141
1142 IF v_tax_category_id IS NOT NULL THEN
1143 OPEN price_list_cur(v_customer_id,
1144 rec.inventory_item_id,
1145 v_address_id,
1146 rec.unit_code,
1147 v_trx_date);
1148 FETCH price_list_cur
1149 INTO v_price_list, v_price_list_uom_code;
1150 CLOSE price_list_cur;
1151 IF v_price_list IS NULL THEN
1152 OPEN price_list_cur(v_customer_id,
1153 rec.inventory_item_id,
1154 0,
1155 rec.unit_code,
1156 v_trx_date);
1157 FETCH price_list_cur
1158 INTO v_price_list, v_price_list_uom_code;
1159 CLOSE price_list_cur;
1160 END IF;
1161 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value(p_party_id => v_customer_id,
1162 p_party_site_id => v_ship_to_site_use_id,
1163 p_inventory_item_id => rec.inventory_item_id,
1164 p_uom_code => rec.unit_code,
1165 p_default_price => nvl(rec.unit_selling_price,
1166 0),
1167 p_ass_value_date => pr_new.trx_date,
1168 p_party_type => 'C');
1169
1170 ln_vat_assessable_value := NVL(ln_vat_assessable_value, 0) *
1171 rec.quantity;
1172
1173 v_line_tax_amount := nvl(rec.line_amount, 0);
1174 IF NVL(v_price_list, 0) > 0 THEN
1175 IF v_price_list_uom_code IS NOT NULL THEN
1176 INV_CONVERT.inv_um_conversion(rec.unit_code,
1177 v_price_list_uom_code,
1178 rec.inventory_item_id,
1179 v_conversion_rate);
1180 IF nvl(v_conversion_rate, 0) <= 0 THEN
1181 INV_CONVERT.inv_um_conversion(rec.unit_code,
1182 v_price_list_uom_code,
1183 0,
1184 v_conversion_rate);
1185 IF nvl(v_conversion_rate, 0) <= 0 THEN
1186 v_conversion_rate := 0;
1187 END IF;
1188 END IF;
1189 END IF;
1190 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_books_id,
1191 c_from_currency_code,
1192 c_conversion_date,
1193 c_conversion_type,
1194 c_conversion_rate);
1195 v_price_list := NVL(1 / v_converted_rate, 0) *
1196 nvl(v_price_list, 0) * v_conversion_rate;
1197 v_price_list_val := nvl(rec.quantity * v_price_list, 0);
1198 ELSE
1199 v_price_list := rec.unit_selling_price;
1200 v_price_list_val := rec.unit_selling_price * rec.quantity;
1201 END IF;
1202
1203 OPEN GC_CHK_RGM_TAX_EXISTS(CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME,
1204 CP_RGM_TAX_TYPE => JAI_CONSTANTS.TAX_TYPE_TCS,
1205 CP_TAX_CATEGORY_ID => V_TAX_CATEGORY_ID);
1206 FETCH GC_CHK_RGM_TAX_EXISTS
1207 INTO LN_TCS_EXISTS;
1208 CLOSE GC_CHK_RGM_TAX_EXISTS;
1209
1210 IF LN_TCS_EXISTS IS NOT NULL THEN
1211 OPEN GC_GET_REGIME_ID(CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME);
1212 FETCH GC_GET_REGIME_ID
1213 INTO LN_TCS_REGIME_ID;
1214 CLOSE GC_GET_REGIME_ID;
1215
1216 jai_rgm_thhold_proc_pkg.get_threshold_slab_id(p_regime_id => ln_tcs_regime_id,
1217 p_organization_id => v_organization_id,
1218 p_party_type => jai_constants.party_type_customer,
1219 p_party_id => v_customer_id,
1220 p_org_id => v_org_id,
1221 p_source_trx_date => v_trx_date,
1222 p_threshold_slab_id => ln_threshold_slab_id,
1223 p_process_flag => lv_process_flag,
1224 p_process_message => lv_process_message);
1225 if lv_process_flag <> jai_constants.successful then
1226 app_exception.raise_exception(exception_type => 'APP',
1227 exception_code => -20275,
1228 exception_text => lv_process_message);
1229 end if;
1230
1231 if ln_threshold_slab_id is not null then
1232
1233 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id(p_threshold_slab_id => ln_threshold_slab_id,
1234 p_org_id => v_org_id,
1235 p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
1236 p_process_flag => lv_process_flag,
1237 p_process_message => lv_process_message);
1238 if lv_process_flag <> jai_constants.successful then
1239 app_exception.raise_exception(exception_type => 'APP',
1240 exception_code => -20275,
1241 exception_text => lv_process_message);
1242 end if;
1243 end if;
1244 end if;
1245
1246 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes('AR_LINES',
1247 v_tax_category_id,
1248 v_header_id,
1249 rec.customer_trx_line_id,
1250 v_price_list_val,
1251 v_line_tax_amount,
1252 rec.inventory_item_id,
1253 NVL(rec.quantity,
1254 0),
1255 rec.unit_code,
1256 NULL,
1257 NULL,
1258 v_converted_rate,
1259 v_creation_date,
1260 v_created_by,
1261 v_last_update_date,
1262 v_last_updated_by,
1263 v_last_update_login,
1264 null,
1265 ln_vat_assessable_value,
1266 p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs,
1267 p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
1268 p_source_trx_type => null,
1269 p_source_table_name => null,
1270 p_action => jai_constants.default_taxes);
1271
1272 END IF;
1273
1274 v_service_type := JAI_AR_RCTLA_TRIGGER_PKG.get_service_type(v_customer_id,
1275 v_ship_to_site_use_id,
1276 'C');
1277 OPEN c_jai_ar_trx_lines(rec.customer_trx_line_id);
1278 FETCH c_jai_ar_trx_lines
1279 INTO t_jai_line_rec_old;
1280 CLOSE c_jai_ar_trx_lines;
1281
1282 UPDATE JAI_AR_TRX_LINES
1283 SET tax_category_id = v_tax_category_id,
1284 service_type_code = v_service_type,
1285 assessable_value = nvl(v_price_list, 0),
1286 vat_assessable_value = ln_vat_assessable_value,
1287 tax_amount = v_line_tax_amount,
1288 total_amount = nvl(rec.line_amount, 0) +
1289 v_line_tax_amount,
1290 last_update_date = v_last_update_date,
1291 last_updated_by = v_last_updated_by,
1292 last_update_login = v_last_update_login
1293 WHERE Customer_Trx_Line_ID = rec.customer_trx_line_id;
1294
1295 OPEN c_jai_ar_trx_lines(rec.customer_trx_line_id);
1296 FETCH c_jai_ar_trx_lines
1297 INTO t_jai_line_rec_new;
1298 CLOSE c_jai_ar_trx_lines;
1299 lv_action := JAI_CONSTANTS.UPDATING;
1300 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1301 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
1302 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1303 (t_jai_line_rec_new.payment_Register IS NULL) AND
1304 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
1305 (t_jai_line_rec_new.Customer_Trx_Id <>
1306 t_jai_line_rec_old.Customer_Trx_Id)) THEN
1307 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
1308 pr_new => t_jai_line_rec_new,
1309 pv_action => lv_action,
1310 pv_return_code => lv_return_code,
1311 pv_return_message => lv_return_message);
1312
1313 IF lv_return_code <> jai_constants.successful then
1314 RAISE le_error;
1315 END IF;
1316 END IF;
1317 END LOOP;
1318 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1320 G_MODULE_NAME || lv_api_name,
1321 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1322 END IF;
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 Pv_return_code := jai_constants.unexpected_error;
1326 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARI_T7 ' ||
1327 substr(sqlerrm, 1, 1900);
1328
1329 END RECALCULATE_TAX;
1330 --DELETING
1331 PROCEDURE DELETE_AR_TRXS(pr_old t_jai_rec%type,
1332 pv_action varchar2,
1333 pv_return_code out NOCOPY varchar2,
1334 pv_return_message out NOCOPY varchar2) IS
1335 lv_api_name CONSTANT VARCHAR2(100) := 'DELETE_AR_TRXS';
1336 lv_debug_info VARCHAR2(4000);
1337 BEGIN
1338 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1339 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1340 G_MODULE_NAME || lv_api_name,
1341 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1342 END IF;
1343 DELETE JAI_AR_TRXS WHERE customer_trx_id = pr_old.customer_trx_id;
1344
1345 pv_return_message := '';
1346 pv_return_code := jai_constants.successful;
1347 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1348 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1349 G_MODULE_NAME || lv_api_name,
1350 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1351 END IF;
1352 EXCEPTION
1353 when others then
1354 Pv_return_message := 'Encountered an error in ' || G_MODULE_NAME || '.' ||
1355 lv_api_name || ':' || substr(sqlerrm, 1, 1900);
1356 pv_return_code := jai_constants.unexpected_error;
1357 END DELETE_AR_TRXS;
1358
1359 --COMPLETING
1360 PROCEDURE GENERATE_VAT_INVOICE_NUMBER(pr_old t_jai_rec%type,
1361 pr_new t_rec%type,
1362 pv_action varchar2,
1363 pv_return_code out NOCOPY varchar2,
1364 pv_return_message out NOCOPY varchar2) IS
1365 lv_api_name CONSTANT VARCHAR2(100) := 'GENERATE_VAT_INVOICE_NUMBER';
1366 lv_debug_info VARCHAR2(4000);
1367 v_vat_start_num JAI_CMN_INVENTORY_ORGS.current_number%Type;
1368 v_vat_jump_by JAI_CMN_INVENTORY_ORGS.jump_by%type;
1369 v_vat_prefix JAI_CMN_INVENTORY_ORGS.prefix%type;
1370 v_vat_invoice_no JAI_AR_TRXS.tax_invoice_no%type;
1371 v_vat_reg_no JAI_CMN_INVENTORY_ORGS.vat_reg_no%type;
1372
1373 v_organization_id Number;
1374 v_loc_id Number;
1375 v_vat_taxes_exist Number;
1376 v_trans_type VARCHAR2(30);
1377 v_loc_vat_inv_no JAI_AR_TRXS.tax_invoice_no%type;
1378
1379 CURSOR organization_cur IS
1380 SELECT organization_id, location_id
1381 FROM JAI_AR_TRXS
1382 where customer_trx_id = pr_new.customer_trx_id;
1383
1384 CURSOR C_VAT_INVOICE_CUR IS
1385 SELECT TAX_INVOICE_NO
1386 FROM JAI_AR_TRXS
1387 WHERE Customer_Trx_Id = pr_new.customer_trx_id;
1388
1389 cursor c_vat_taxes_exist is
1390 select 1
1391 from JAI_AR_TRX_TAX_LINES
1392 where link_to_cust_trx_line_id in
1393 (select customer_trx_line_id
1394 from JAI_AR_TRX_LINES
1395 where customer_trx_id = pr_new.customer_trx_id)
1396 and tax_id in (select tax_id
1397 from JAI_CMN_TAXES_ALL
1398 where vat_flag = 'Y'
1399 and org_id = pr_new.org_id);
1400
1401 CURSOR transaction_type_cur IS
1402 SELECT TYPE
1403 FROM RA_CUST_TRX_TYPES_ALL
1404 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
1405 AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
1406
1407 Procedure Generate_Tax_Invoice_no(p_organization_id Number,
1408 p_loc_id Number) is
1409
1410 Cursor c_get_vat_reg_no is
1411 select vat_reg_no
1412 from JAI_CMN_INVENTORY_ORGS
1413 where organization_id = p_organization_id
1414 and location_id = p_loc_id;
1415
1416 cursor c_get_vat_invoice_no is
1417 select current_number, jump_by, prefix
1418 from JAI_CMN_INVENTORY_ORGS
1419 where organization_id = p_organization_id
1420 and location_id = p_loc_id;
1421 --Added by Zhiwei for JAI Trigger elimination begin
1422 ---------------------------------------------------------
1423 cursor c_get_rec(cn_customer_trx_id number) is
1424 select *
1425 from jai_ar_trxs
1426 where customer_trx_id = cn_customer_trx_id;
1427
1428 t_rec_new jai_ar_trxs%rowtype;
1429 t_rec_old jai_ar_trxs%rowtype;
1430 lv_action VARCHAR2(20);
1431 lv_return_message VARCHAR2(2000);
1432 lv_return_code VARCHAR2(100);
1433 le_error EXCEPTION;
1434 ---------------------------------------------------------
1435 --Added by Zhiwei for JAI Trigger elimination end
1436
1437 Begin
1438
1439 open c_get_vat_reg_no;
1440 fetch c_get_vat_reg_no
1441 into v_vat_reg_no;
1442 close c_get_vat_reg_no;
1443
1444 if v_vat_reg_no is null then
1445 return;
1446 end if;
1447
1448 update JAI_CMN_INVENTORY_ORGS
1449 set last_update_date = last_update_date
1450 where vat_reg_no = v_vat_reg_no;
1451
1452 Open c_get_vat_invoice_no;
1453 Fetch c_get_vat_invoice_no
1454 into v_vat_start_num, v_vat_jump_by, v_vat_prefix;
1455 close c_get_vat_invoice_no;
1456
1457 v_vat_start_num := NVL(v_vat_start_num, 0) + NVL(v_vat_jump_by, 1);
1458
1459 if v_vat_prefix is not null then
1460 v_vat_invoice_no := v_vat_prefix || '/' || v_vat_start_num;
1461 else
1462 v_vat_invoice_no := v_vat_start_num;
1463 end if;
1464
1465 --Added by Qinglei for JAI Trigger elimination begin
1466 ---------------------------------------------------------
1467 open c_get_rec(pr_new.customer_trx_id);
1468 fetch c_get_rec
1469 into t_rec_old;
1470 close c_get_rec;
1471 ---------------------------------------------------------
1472 --Added by Qinglei for JAI Trigger elimination end
1473
1474 update JAI_AR_TRXS
1475 set tax_invoice_no = v_vat_invoice_no
1476 where customer_trx_id = pr_new.customer_trx_id;
1477
1478 --Added by Qinglei for JAI Trigger elimination begin
1479 ---------------------------------------------------------
1480 open c_get_rec(pr_new.customer_trx_id);
1481 fetch c_get_rec
1482 into t_rec_new;
1483 close c_get_rec;
1484
1485 if (t_rec_new.once_completed_flag = 'Y') then
1486
1487 lv_action := jai_constants.updating;
1488
1489 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
1490 pr_new => t_rec_new,
1491 pv_action => lv_action,
1492 pv_return_code => lv_return_code,
1493 pv_return_message => lv_return_message);
1494
1495 IF lv_return_code <> jai_constants.successful then
1496 RAISE le_error;
1497 END IF;
1498
1499 end if;
1500 ---------------------------------------------------------
1501 --Added by Qinglei for JAI Trigger elimination end
1502
1503 update JAI_CMN_INVENTORY_ORGS
1504 set current_number = NVL(v_vat_start_num, 0),
1505 prefix = v_vat_prefix,
1506 jump_by = v_vat_jump_by
1507 where vat_Reg_no = v_vat_reg_no;
1508
1509 End;
1510
1511 BEGIN
1512 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1513 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1514 G_MODULE_NAME || lv_api_name,
1515 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1516 END IF;
1517 pv_return_code := jai_constants.successful;
1518 Open C_VAT_INVOICE_CUR;
1519 Fetch C_VAT_INVOICE_CUR
1520 into v_loc_vat_inv_no;
1521 close C_VAT_INVOICE_CUR;
1522
1523 if v_loc_vat_inv_no is not null then
1524 return;
1525 end if;
1526
1527 OPEN transaction_type_cur;
1528 FETCH transaction_type_cur
1529 INTO v_trans_type;
1530 CLOSE transaction_type_cur;
1531
1532 IF NVL(v_trans_type, 'N') <> 'INV' THEN
1533 RETURN;
1534 END IF;
1535
1536 OPEN organization_cur;
1537 FETCH organization_cur
1538 INTO v_organization_id, v_loc_id;
1539 CLOSE organization_cur;
1540
1541 Open c_vat_taxes_exist;
1542 Fetch c_vat_taxes_exist
1543 into v_vat_taxes_exist;
1544 Close c_vat_taxes_exist;
1545
1546 if v_vat_taxes_exist = 1 then
1547 Generate_Tax_Invoice_no(v_organization_id, v_loc_id);
1548 end if;
1549 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1550 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1551 G_MODULE_NAME || lv_api_name,
1552 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1553 END IF;
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 Pv_return_code := jai_constants.unexpected_error;
1557 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T1 ' ||
1558 substr(sqlerrm, 1, 1900);
1559
1560 END GENERATE_VAT_INVOICE_NUMBER;
1561
1562 PROCEDURE VAT_ACCOUNTING(pr_old t_jai_rec%type,
1563 pr_new t_rec%type,
1564 pv_action varchar2,
1565 pv_return_code out NOCOPY varchar2,
1566 pv_return_message out NOCOPY varchar2) IS
1567 lv_api_name CONSTANT VARCHAR2(100) := 'VAT_ACCOUNTING_FOR_CM';
1568 lv_debug_info VARCHAR2(4000);
1569 v_organization_id NUMBER;
1570 v_loc_id NUMBER;
1571 v_trans_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE;
1572 lv_vat_invoice_no JAI_AR_TRXS.VAT_INVOICE_NO%TYPE;
1573 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
1574 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1575 lv_process_flag VARCHAR2(10);
1576 lv_process_message VARCHAR2(4000);
1577 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE;
1578 ld_vat_invoice_date JAI_AR_TRXS.VAT_INVOICE_DATE%TYPE;
1579
1580 CURSOR organization_cur IS
1581 SELECT organization_id, location_id, vat_invoice_no, vat_invoice_date
1582 FROM JAI_AR_TRXS
1583 WHERE customer_trx_id = pr_new.customer_trx_id;
1584
1585 CURSOR transaction_type_cur IS
1586 SELECT type
1587 FROM ra_cust_trx_types_all
1588 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
1589 AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
1590
1591 CURSOR cur_vat_taxes_exist IS
1592 SELECT regime_id, regime_code
1593 FROM JAI_AR_TRX_TAX_LINES jcttl,
1594 JAI_AR_TRX_LINES jctl,
1595 JAI_CMN_TAXES_ALL jtc,
1596 jai_regime_tax_types_v jrttv
1597 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1598 AND jctl.customer_trx_id = pr_new.customer_trx_id
1599 AND jcttl.tax_id = jtc.tax_id
1600 AND jtc.tax_type = jrttv.tax_type
1601 AND regime_code = jai_constants.vat_regime
1602 AND jtc.org_id = pr_new.org_id;
1603
1604 CURSOR cur_get_gl_date(cp_acct_class ra_cust_trx_line_gl_dist_all.account_class%type) IS
1605 SELECT gl_date
1606 FROM ra_cust_trx_line_gl_dist_all
1607 WHERE customer_trx_id = pr_new.customer_trx_id
1608 AND account_class = cp_acct_class
1609 AND latest_rec_flag = 'Y';
1610
1611 CURSOR cur_get_in_vat_no IS
1612 SELECT vat_invoice_no
1613 FROM JAI_AR_TRXS
1614 WHERE customer_trx_id = pr_new.previous_customer_trx_id;
1615
1616 CURSOR c_chk_vat_reversal(cp_tax_type jai_cmn_taxes_all.tax_type%TYPE) IS
1617 SELECT 1
1618 FROM JAI_AR_TRX_TAX_LINES jcttl,
1619 JAI_AR_TRX_LINES jctl,
1620 JAI_CMN_TAXES_ALL jtc
1621 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1622 AND jctl.customer_trx_id = pr_new.customer_trx_id
1623 AND jcttl.tax_id = jtc.tax_id
1624 AND jtc.org_id = pr_new.org_id
1625 AND jtc.tax_type = cp_tax_type;
1626
1627 lv_vat_reversal VARCHAR2(30);
1628 ln_vat_reversal_exists NUMBER;
1629
1630 CURSOR c_get_regime_id IS
1631 SELECT regime_id
1632 FROM jai_regime_tax_types_v
1633 WHERE regime_code = jai_constants.vat_regime
1634 AND rownum = 1;
1635 cursor c_get_rec(cn_customer_trx_id number) is
1636 select * from jai_ar_trxs where customer_trx_id = cn_customer_trx_id;
1637
1638 t_rec_new jai_ar_trxs%rowtype;
1639 t_rec_old jai_ar_trxs%rowtype;
1640 lv_action VARCHAR2(20);
1641 lv_return_message VARCHAR2(2000);
1642 lv_return_code VARCHAR2(100);
1643 le_error EXCEPTION;
1644 BEGIN
1645 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1647 G_MODULE_NAME || lv_api_name,
1648 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1649 END IF;
1650 pv_return_code := jai_constants.successful;
1651
1652 OPEN organization_cur;
1653 FETCH organization_cur
1654 INTO v_organization_id,
1655 v_loc_id,
1656 lv_vat_invoice_no,
1657 ld_vat_invoice_date;
1658 CLOSE organization_cur;
1659 IF lv_vat_invoice_no IS NOT NULL OR ld_vat_invoice_date IS NOT NULL THEN
1660 return;
1661 END IF;
1662
1663 OPEN transaction_type_cur;
1664 FETCH transaction_type_cur
1665 INTO v_trans_type;
1666 CLOSE transaction_type_cur;
1667
1668 IF NVL(v_trans_type, 'N') <> 'CM' THEN
1669 return;
1670 END IF;
1671
1672 OPEN cur_vat_taxes_exist;
1673 FETCH cur_vat_taxes_exist
1674 into ln_regime_id, ln_regime_code;
1675 CLOSE cur_vat_taxes_exist;
1676
1677 IF UPPER(nvl(ln_regime_code, '####')) <>
1678 UPPER(jai_constants.vat_regime) THEN
1679 return;
1680 END IF;
1681 IF ln_regime_id IS NULL THEN
1682 lv_vat_reversal := 'VAT REVERSAL';
1683 OPEN c_chk_vat_reversal(lv_vat_reversal);
1684 FETCH c_chk_vat_reversal
1685 INTO ln_vat_reversal_exists;
1686 CLOSE c_chk_vat_reversal;
1687
1688 IF ln_vat_reversal_exists = 1 THEN
1689 OPEN c_get_regime_id;
1690 FETCH c_get_regime_id
1691 INTO ln_regime_id;
1692 CLOSE c_get_regime_id;
1693
1694 IF ln_regime_id IS NOT NULL THEN
1695 ln_regime_code := jai_constants.vat_regime;
1696 END IF;
1697 END IF;
1698 END IF;
1699
1700 IF pr_new.previous_customer_trx_id is NOT NULL THEN
1701 OPEN cur_get_in_vat_no;
1702 FETCH cur_get_in_vat_no
1703 INTO lv_vat_invoice_no;
1704 CLOSE cur_get_in_vat_no;
1705 END IF;
1706
1707 OPEN cur_get_gl_date('REC');
1708 FETCH cur_get_gl_date
1709 INTO ld_gl_date;
1710 CLOSE cur_get_gl_date;
1711
1712 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice(p_regime_id => ln_regime_id,
1713 p_source => jai_constants.source_ar,
1714 p_organization_id => v_organization_id,
1715 p_location_id => v_loc_id,
1716 p_delivery_id => NULL,
1717 p_customer_trx_id => pr_new.customer_trx_id,
1718 p_transaction_type => v_trans_type,
1719 p_vat_invoice_no => lv_vat_invoice_no,
1720 p_default_invoice_date => nvl(ld_gl_date,
1721 pr_new.trx_date),
1722 p_batch_id => NULL,
1723 p_called_from => 'JA_IN_LOC_AR_HDR_UPD_TRG_VAT', /* The string 'JA_IN_LOC_AR_HDR_UPD_TRG_VAT' is also being used in jai_cmn_rgm_vat_accnt_pkg.process_order_invoice*/
1724 p_debug => jai_constants.no,
1725 p_process_flag => lv_process_flag,
1726 p_process_message => lv_process_message);
1727
1728 IF lv_process_flag = jai_constants.expected_error OR
1729 lv_process_flag = jai_constants.unexpected_error THEN
1730
1731 pv_return_code := jai_constants.expected_error;
1732 pv_return_message := lv_process_message;
1733 return;
1734
1735 END IF;
1736
1737 --Added by Zhiwei for JAI Trigger elimination begin
1738 ---------------------------------------------------------
1739 open c_get_rec(pr_new.customer_trx_id);
1740 fetch c_get_rec
1741 into t_rec_old;
1742 close c_get_rec;
1743 ---------------------------------------------------------
1744 --Added by Zhiwei for JAI Trigger elimination end
1745
1746 UPDATE JAI_AR_TRXS
1747 SET vat_invoice_no = lv_vat_invoice_no,
1748 vat_invoice_date = nvl(ld_gl_date, pr_new.trx_date)
1749 WHERE customer_trx_id = pr_new.customer_trx_id;
1750
1751 --Added by Zhiwei for JAI Trigger elimination begin
1752 ---------------------------------------------------------
1753 open c_get_rec(pr_new.customer_trx_id);
1754 fetch c_get_rec
1755 into t_rec_new;
1756 close c_get_rec;
1757
1758 if (t_rec_new.once_completed_flag = 'Y') then
1759
1760 lv_action := jai_constants.updating;
1761
1762 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
1763 pr_new => t_rec_new,
1764 pv_action => lv_action,
1765 pv_return_code => lv_return_code,
1766 pv_return_message => lv_return_message);
1767
1768 IF lv_return_code <> jai_constants.successful then
1769 RAISE le_error;
1770 END IF;
1771
1772 end if;
1773 ---------------------------------------------------------
1774 --Added by Zhiwei for JAI Trigger elimination end
1775 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1776 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1777 G_MODULE_NAME || lv_api_name,
1778 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1779 END IF;
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 Pv_return_code := jai_constants.unexpected_error;
1783 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T3 ' ||
1784 substr(sqlerrm, 1, 1900);
1785 END VAT_ACCOUNTING;
1786
1787 PROCEDURE PROCESS_EXCISE_TAXES(pr_old t_jai_rec%type,
1788 pr_new t_rec%type,
1789 pv_action varchar2,
1790 pv_return_code out NOCOPY varchar2,
1791 pv_return_message out NOCOPY varchar2) IS
1792 lv_api_name CONSTANT VARCHAR2(100) := 'VAT_ACCOUNTING_FOR_CM';
1793 lv_debug_info VARCHAR2(4000);
1794 v_org_id NUMBER;
1795 v_loc_id NUMBER;
1796 v_reg_code VARCHAR2(30);
1797 v_update_rg VARCHAR2(1);
1798 v_reg_type VARCHAR2(10);
1799 v_excise_paid_register VARCHAR2(10);
1800 v_rg23a_type VARCHAR2(10);
1801 v_rg23c_type VARCHAR2(10);
1802 v_complete_flag VARCHAR2(1);
1803 v_rg_flag VARCHAR2(1);
1804 v_update_rg_flag VARCHAR2(1);
1805 v_tax_amount NUMBER := 0;
1806 v_rg23a_tax_amount NUMBER := 0;
1807 v_rg23c_tax_amount NUMBER := 0;
1808 v_other_tax_amount NUMBER := 0;
1809 v_basic_ed NUMBER := 0;
1810 v_additional_ed NUMBER := 0;
1811 v_other_ed NUMBER := 0;
1812 v_item_class VARCHAR2(10);
1813 v_excise_flag VARCHAR2(1);
1814 v_fin_year NUMBER;
1815 v_gp_1 NUMBER := 0;
1816 v_gp_2 NUMBER := 0;
1817 v_rg23a_bal NUMBER := 0;
1818 v_rg23c_bal NUMBER := 0;
1819 v_pla_bal NUMBER := 0;
1820 v_invoice_no VARCHAR2(200);
1821 v_other_invoice_no NUMBER;
1822 v_rg23a_invoice_no NUMBER;
1823 v_rg23c_invoice_no NUMBER;
1824 rg23a NUMBER := 0;
1825 rg23c NUMBER := 0;
1826 pla NUMBER := 0;
1827 v_parent_trx_number VARCHAR2(20);
1828 v_register_balance NUMBER := 0;
1829 v_rg23d_register_balance NUMBER := 0;
1830 v_customer_trx_id NUMBER;
1831 v_converted_rate NUMBER := 1;
1832 v_ssi_unit_flag VARCHAR2(1);
1833 v_trans_type VARCHAR2(30);
1834 v_last_update_date DATE;
1835 v_last_updated_by NUMBER;
1836 v_creation_date DATE;
1837 v_created_by NUMBER;
1838 v_last_update_login NUMBER;
1839 v_bond_tax_amount NUMBER := 0;
1840 V_rg23d_tax_amount NUMBER := 0;
1841 v_modvat_tax_rate NUMBER;
1842 v_exempt_bal NUMBER;
1843 v_matched_qty NUMBER;
1844 VSQLERRM VARCHAR2(240);
1845 v_trans_type_up VARCHAR2(3);
1846 v_order_invoice_type_up VARCHAR2(25);
1847 v_register_code_up VARCHAR2(25);
1848 v_errbuf VARCHAR2(250);
1849 v_register_id JAI_OM_OE_BOND_REG_HDRS.register_id%type;
1850 v_register_exp_date JAI_OM_OE_BOND_REG_HDRS.bond_expiry_date%type;
1851 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.lou_flag%type;
1852 v_trading_flag JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
1853 v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;
1854
1855 CURSOR complete_cur IS
1856 SELECT organization_id,
1857 location_id,
1858 once_completed_flag,
1859 decode(once_completed_flag,
1860 'A',
1861 'RG23A',
1862 'C',
1863 'RG23C',
1864 'P',
1865 'PLA') register_type,
1866 update_rg_flag,
1867 nvl(update_rg23d_flag, 'N')
1868 FROM JAI_AR_TRXS
1869 WHERE customer_trx_id = v_customer_trx_id;
1870
1871 CURSOR REG_BALANCE_CUR(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1872 SELECT NVL(rg23a_balance, 0) rg23a_balance,
1873 NVL(rg23c_balance, 0) rg23c_balance,
1874 NVL(pla_balance, 0) pla_balance
1875 FROM JAI_CMN_RG_BALANCES
1876 WHERE organization_id = p_org_id
1877 AND location_id = p_loc_id;
1878
1879 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1880 SELECT register_code
1881 FROM JAI_OM_OE_BOND_REG_HDRS
1882 WHERE organization_id = p_org_id
1883 AND location_id = p_loc_id
1884 AND register_id IN (SELECT register_id
1885 FROM JAI_OM_OE_BOND_REG_DTLS
1886 WHERE order_type_id = pr_new.batch_source_id
1887 AND order_flag = 'N');
1888
1889 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
1890 SELECT MAX(A.fin_year)
1891 FROM JAI_CMN_FIN_YEARS A
1892 WHERE organization_id = p_org_id
1893 AND fin_active_flag = 'Y';
1894
1895 CURSOR tax_amount_cur IS
1896 SELECT NVL(tax_amount, 0) tax_amount
1897 FROM JAI_AR_TRXS
1898 WHERE customer_trx_id = v_customer_trx_id;
1899
1900 CURSOR preference_reg_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1901 SELECT pref_rg23a, pref_rg23c, pref_pla
1902 FROM JAI_CMN_INVENTORY_ORGS
1903 WHERE organization_id = p_org_id
1904 AND location_id = p_loc_id;
1905
1906 CURSOR item_class_cur(P_ORG_ID IN NUMBER, P_Item_id IN NUMBER) IS
1907 SELECT item_class, excise_flag
1908 FROM JAI_INV_ITM_SETUPS
1909 WHERE inventory_item_id = P_Item_Id
1910 AND ORGANIZATION_ID = P_ORG_ID;
1911
1912 CURSOR organization_cur IS
1913 SELECT organization_id, location_id
1914 FROM JAI_AR_TRXS
1915 WHERE trx_number = v_parent_trx_number;
1916
1917 CURSOR register_balance_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1918 SELECT NVL(register_balance, 0) register_balance
1919 FROM JAI_OM_OE_BOND_TRXS
1920 WHERE transaction_id =
1921 (SELECT MAX(A.transaction_id)
1922 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1923 WHERE A.register_id = B.register_id
1924 AND B.organization_id = p_org_id
1925 AND B.location_id = p_loc_id);
1926
1927 CURSOR register_balance_cur1(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1928 SELECT NVL(rg23d_register_balance, 0) rg23d_register_balance
1929 FROM JAI_OM_OE_BOND_TRXS
1930 WHERE transaction_id =
1931 (SELECT MAX(A.transaction_id)
1932 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1933 WHERE A.register_id = B.register_id
1934 AND B.organization_id = p_org_id
1935 AND B.location_id = p_loc_id);
1936
1937 CURSOR line_cur IS
1938 SELECT customer_trx_line_id,
1939 inventory_item_id,
1940 quantity,
1941 line_number,
1942 excise_exempt_type,
1943 assessable_value
1944 FROM JAI_AR_TRX_LINES
1945 WHERE customer_trx_id = v_customer_trx_id
1946 ORDER BY customer_trx_line_id;
1947
1948 CURSOR matched_qty_cur(p_customer_trx_line_id NUMBER) IS
1949 SELECT SUM(quantity_applied)
1950 FROM JAI_CMN_MATCH_RECEIPTS
1951 WHERE ref_line_id = p_customer_trx_line_id;
1952
1953 CURSOR excise_cal_cur(p_line_id IN NUMBER,
1954 p_inventory_item_id IN NUMBER,
1955 p_org_id IN NUMBER) IS
1956 SELECT A.tax_id,
1957 A.tax_rate t_rate,
1958 A.tax_amount tax_amt,
1959 A.func_tax_amount func_amt,
1960 b.tax_type t_type,
1961 b.stform_type,
1962 A.tax_line_no
1963 FROM JAI_AR_TRX_TAX_LINES A,
1964 JAI_CMN_TAXES_ALL B,
1965 JAI_INV_ITM_SETUPS C
1966 WHERE link_to_cust_trx_line_id = p_line_id
1967 AND b.tax_type IN (jai_constants.tax_type_excise,
1968 jai_constants.tax_type_exc_additional,
1969 jai_constants.tax_type_exc_other)
1970 AND A.tax_id = b.tax_id
1971 AND c.inventory_item_id = p_inventory_item_id
1972 AND c.organization_id = p_org_id
1973 AND c.item_class IN
1974 (jai_constants.item_class_rmin,
1975 jai_constants.item_class_rmex,
1976 jai_constants.item_class_cgex,
1977 jai_constants.item_class_cgin,
1978 jai_constants.item_class_ccex,
1979 jai_constants.item_class_ccin,
1980 jai_constants.item_class_fgin,
1981 jai_constants.item_class_fgex)
1982 ORDER BY 1;
1983
1984 CURSOR ssi_unit_flag_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1985 SELECT ssi_unit_flag, nvl(trading, 'N')
1986 FROM JAI_CMN_INVENTORY_ORGS
1987 WHERE organization_id = p_org_id
1988 AND location_id = p_loc_id;
1989
1990 CURSOR transaction_type_cur IS
1991 SELECT TYPE
1992 FROM RA_CUST_TRX_TYPES_ALL
1993 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
1994 AND (org_id = pr_new.org_id OR
1995 (org_id is null and pr_new.org_id is null));
1996 CURSOR Batch_Source_Name_Cur IS
1997 SELECT name
1998 FROM Ra_Batch_Sources_All
1999 WHERE batch_source_id = pr_new.batch_source_id
2000 AND (org_id = pr_new.org_id OR
2001 (org_id is null AND pr_new.org_id is null));
2002
2003 CURSOR Def_Excise_Invoice_Cur(p_organization_id IN NUMBER,
2004 p_location_id IN NUMBER,
2005 p_fin_year IN NUMBER,
2006 p_batch_name IN VARCHAR2,
2007 p_register_code IN VARCHAR2) IS
2008 SELECT start_number, end_number, jump_by, prefix
2009 FROM JAI_CMN_RG_EXC_INV_NOS
2010 WHERE organization_id = p_organization_id
2011 AND location_id = p_location_id
2012 AND fin_year = p_fin_year
2013 AND transaction_type IN ('I', 'DOM', 'EXP')
2014 AND order_invoice_type = p_batch_name
2015 AND register_code = p_register_code;
2016
2017 CURSOR excise_invoice_cur(p_org_id IN NUMBER,
2018 p_loc_id IN NUMBER,
2019 p_fin_year IN NUMBER) IS
2020 SELECT NVL(MAX(GP1), 0), NVL(MAX(GP2), 0)
2021 FROM JAI_CMN_RG_EXC_INV_NOS
2022 WHERE organization_id = p_org_id
2023 AND location_id = p_loc_id
2024 AND fin_year = p_fin_year
2025 AND transaction_type IS NULL
2026 AND order_invoice_type IS NULL
2027 AND register_code IS NULL;
2028
2029 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2,
2030 cp_register_type ja_lookups.lookup_type%type) IS
2031 SELECT meaning
2032 FROM ja_lookups
2033 WHERE lookup_code = p_register_code
2034 AND lookup_type = cp_register_type;
2035 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
2036 SELECT MODVAT_REVERSE_PERCENT
2037 FROM JAI_CMN_INVENTORY_ORGS
2038 WHERE organization_id = v_org_id
2039 AND (location_id = v_location_id OR
2040 (location_id is NULL and v_location_id is NULL));
2041 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
2042 SELECT A.tax_rate
2043 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
2044 WHERE A.tax_id = b.tax_id
2045 AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
2046 AND b.tax_type = jai_constants.tax_type_modvat_recovery;
2047
2048 v_start_number NUMBER;
2049 v_end_number NUMBER;
2050 v_jump_by NUMBER;
2051 v_order_invoice_type VARCHAR2(50);
2052 v_prefix VARCHAR2(50);
2053 v_meaning VARCHAR2(80);
2054 v_set_of_books_id NUMBER;
2055 CURSOR ec_code_cur(p_organization_id IN NUMBER,
2056 p_location_id IN NUMBER) IS
2057 SELECT A.Organization_Id, A.Location_Id
2058 FROM JAI_CMN_INVENTORY_ORGS A
2059 WHERE A.Ec_Code IN
2060 (SELECT B.Ec_Code
2061 FROM JAI_CMN_INVENTORY_ORGS B
2062 WHERE B.Organization_Id = p_organization_id
2063 AND B.Location_Id = p_location_id);
2064
2065 CURSOR c_total_Excise_amt IS
2066 SELECT nvl(sum(jrtl.func_tax_amount), 0)
2067 FROM JAI_AR_TRXS jtrx,
2068 JAI_AR_TRX_LINES jtl,
2069 JAI_AR_TRX_TAX_LINES jrtl,
2070 JAI_CMN_TAXES_ALL jtc,
2071 JAI_INV_ITM_SETUPS jmtl
2072 WHERE jrtl.tax_id = jtc.tax_id
2073 AND jtrx.customer_trx_id = jtl.customer_Trx_id
2074 AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
2075 AND jtl.inventory_item_id = jmtl.inventory_item_id
2076 AND jtrx.organization_id = jmtl.organization_id
2077 AND jmtl.item_class IN
2078 (jai_constants.item_class_rmin,
2079 jai_constants.item_class_rmex,
2080 jai_constants.item_class_cgex,
2081 jai_constants.item_class_cgin,
2082 jai_constants.item_class_ccex,
2083 jai_constants.item_class_ccin,
2084 jai_constants.item_class_fgin,
2085 jai_constants.item_class_fgex)
2086 AND jtc.tax_type like '%Excise%'
2087 AND jtl.customer_trx_id = pr_new.customer_trx_id
2088 AND jtrx.customer_trx_id = pr_new.customer_trx_id;
2089
2090 v_total_excise_amt NUMBER := 0;
2091
2092 CURSOR c_cess_amount is
2093 SELECT NVL(SUM(jrctl.func_tax_amount), 0) tax_amount
2094 FROM JAI_AR_TRX_TAX_LINES jrctl, JAI_CMN_TAXES_ALL jtc
2095 WHERE jtc.tax_id = jrctl.tax_id
2096 AND link_to_cust_trx_line_id IN
2097 (SELECT customer_trx_line_id
2098 FROM JAI_AR_TRX_LINES
2099 WHERE customer_trx_id = pr_new.customer_trx_id)
2100 AND upper(jtc.tax_type) IN
2101 (upper(jai_constants.tax_type_cvd_edu_cess),
2102 upper(jai_constants.tax_type_exc_edu_cess));
2103
2104 CURSOR cur_chk_temp_lines_exist(cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE) IS
2105
2106 SELECT '1'
2107 FROM Fnd_Concurrent_Requests FCR
2108 WHERE FCR.argument1 = to_char(cp_customer_trx_id)
2109 and fcr.status_code <> 'C'
2110 and fcr.phase_code <> 'C'
2111 AND fcr.request_id IN
2112 (Select nvl(Max(Request_ID), -1)
2113 From Fnd_Concurrent_Programs FCP,
2114 Fnd_Application FA,
2115 Fnd_Concurrent_Requests FCR
2116 Where FCR.Program_Application_ID = FA.Application_ID
2117 AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
2118 AND FA.Application_ID = FCP.Application_ID
2119 AND Concurrent_Program_Name = 'JAILINEGL'
2120 AND FA.Application_Short_Name = 'JA'
2121 AND FCR.argument1 = To_Char(cp_customer_trx_id));
2122
2123 CURSOR c_vat_invoice_cur IS
2124 SELECT vat_invoice_no
2125 FROM JAI_AR_TRXS
2126 WHERE customer_trx_id = pr_new.customer_trx_id;
2127
2128 CURSOR cur_vat_taxes_exist IS
2129 SELECT regime_id, regime_code
2130 FROM JAI_AR_TRX_TAX_LINES jcttl,
2131 JAI_AR_TRX_LINES jctl,
2132 JAI_CMN_TAXES_ALL jtc,
2133 jai_regime_tax_types_v jrttv
2134 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
2135 AND jctl.customer_trx_id = pr_new.customer_trx_id
2136 AND jcttl.tax_id = jtc.tax_id
2137 AND jtc.tax_type = jrttv.tax_type
2138 AND regime_code = jai_constants.vat_regime
2139 AND jtc.org_id = pr_new.org_id;
2140
2141 CURSOR c_chk_vat_reversal(cp_tax_type jai_cmn_taxes_all.tax_type%TYPE) IS
2142 SELECT 1
2143 FROM JAI_AR_TRX_TAX_LINES jcttl,
2144 JAI_AR_TRX_LINES jctl,
2145 JAI_CMN_TAXES_ALL jtc
2146 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
2147 AND jctl.customer_trx_id = pr_new.customer_trx_id
2148 AND jcttl.tax_id = jtc.tax_id
2149 AND jtc.org_id = pr_new.org_id
2150 AND jtc.tax_type = cp_tax_type;
2151
2152 CURSOR c_get_regime_id IS
2153 SELECT regime_id
2154 FROM jai_regime_tax_types_v
2155 WHERE regime_code = jai_constants.vat_regime
2156 AND rownum = 1;
2157
2158 ln_vat_reversal_exists NUMBER;
2159 lv_vat_reversal VARCHAR2(100);
2160
2161 CURSOR cur_get_same_inv_no(cp_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE,
2162 cp_location_id JAI_AR_TRXS.LOCATION_ID%TYPE) IS
2163 SELECT nvl(attribute_value, 'N') attribute_value
2164 FROM JAI_RGM_ORG_REGNS_V
2165 WHERE regime_code = jai_constants.vat_regime
2166 AND attribute_type_code = jai_constants.regn_type_others
2167 AND attribute_code = jai_constants.attr_code_same_inv_no
2168 AND organization_id = cp_organization_id
2169 AND location_id = cp_location_id;
2170
2171 CURSOR cur_get_exc_inv_no IS
2172 SELECT excise_invoice_no
2173 FROM JAI_AR_TRX_LINES
2174 WHERE customer_trx_id = pr_new.customer_trx_id;
2175
2176 CURSOR cur_get_gl_date(cp_account_class ra_cust_trx_line_gl_dist_all.account_class%type) IS
2177 SELECT gl_date
2178 FROM ra_cust_trx_line_gl_dist_all
2179 WHERE customer_trx_id = pr_new.customer_trx_id
2180 AND account_class = cp_account_class
2181 AND latest_rec_flag = 'Y';
2182
2183 ln_exists NUMBER;
2184 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
2185 lv_process_flag VARCHAR2(2);
2186 lv_process_message VARCHAR2(1996);
2187 lv_register_type VARCHAR2(5);
2188 lv_rg23a_cess_avlbl VARCHAR2(10);
2189 lv_rg23c_cess_avlbl VARCHAR2(10);
2190 lv_pla_cess_avlbl VARCHAR2(10);
2191 lv_vat_invoice_number JAI_AR_TRXS.VAT_INVOICE_NO%TYPE;
2192 lv_vat_taxes_exist VARCHAR2(1);
2193 lv_vat_no_same_exc_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
2194 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE;
2195 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
2196 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
2197
2198 lv_doc_type_class varchar2(2);
2199 ln_cgst_regime_id NUMBER;
2200 ln_sgst_regime_id NUMBER;
2201 lv_cgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
2202 lv_sgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
2203
2204 cursor c_get_rec(cn_customer_trx_id number) is
2205 select * from jai_ar_trxs where customer_trx_id = cn_customer_trx_id;
2206
2207 t_rec_new jai_ar_trxs%rowtype;
2208 t_rec_old jai_ar_trxs%rowtype;
2209 lv_action VARCHAR2(20);
2210 lv_return_message VARCHAR2(2000);
2211 lv_return_code VARCHAR2(100);
2212 le_error EXCEPTION;
2213
2214 BEGIN
2215 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2216 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2217 G_MODULE_NAME || lv_api_name,
2218 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
2219 END IF;
2220 pv_return_code := jai_constants.successful;
2221 v_complete_flag := 'N';
2222 v_rg_flag := 'N';
2223 v_update_rg_flag := 'N';
2224 v_item_class := 'N';
2225 v_parent_trx_number := pr_new.recurred_from_trx_number;
2226 v_customer_trx_id := pr_old.customer_trx_id;
2227 v_last_update_date := pr_new.last_update_date;
2228 v_last_updated_by := pr_new.last_updated_by;
2229 v_creation_date := pr_new.creation_date;
2230 v_created_by := pr_new.created_by;
2231 v_last_update_login := pr_new.last_update_login;
2232 v_set_of_books_id := pr_new.set_of_books_id;
2233 lv_vat_no_same_exc_no := 'N';
2234
2235 IF pr_new.created_from = 'ARXTWMAI' THEN
2236 OPEN cur_chk_temp_lines_exist(cp_customer_trx_id => v_customer_trx_id);
2237 FETCH cur_chk_temp_lines_exist
2238 INTO ln_exists;
2239 IF CUR_CHK_TEMP_LINES_EXIST%FOUND THEN
2240 CLOSE cur_chk_temp_lines_exist;
2241
2242 pv_return_code := jai_constants.expected_error;
2243 pv_return_message := 'IL Tax not applied - Please wait for AR Tax and Freight Defaultation Concurrent Request to complete';
2244 return;
2245 END IF;
2246 CLOSE cur_chk_temp_lines_exist;
2247 END IF;
2248
2249 OPEN transaction_type_cur;
2250 FETCH transaction_type_cur
2251 INTO v_trans_type;
2252 CLOSE transaction_type_cur;
2253
2254 OPEN Complete_Cur;
2255 FETCH Complete_Cur
2256 INTO v_org_id,
2257 v_loc_id,
2258 v_complete_flag,
2259 v_reg_type,
2260 v_update_rg_flag,
2261 v_update_rg23d_flag;
2262 CLOSE Complete_Cur;
2263
2264 IF pr_new.COMPLETE_FLAG <> pr_old.COMPLETE_FLAG THEN
2265 v_rg_flag := v_update_rg_flag;
2266
2267 IF NVL(v_complete_flag, 'N') = 'Y' THEN
2268 RETURN;
2269 END IF;
2270
2271 IF NVL(v_trans_type, 'N') <> 'INV' THEN
2272 --Added by Zhiwei for JAI Trigger elimination begin
2273 ---------------------------------------------------------
2274 open c_get_rec(V_CUSTOMER_TRX_ID);
2275 fetch c_get_rec
2276 into t_rec_old;
2277 close c_get_rec;
2278 ---------------------------------------------------------
2279 --Added by Zhiwei for JAI Trigger elimination end
2280
2281 UPDATE JAI_AR_TRXS
2282 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
2283 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
2284
2285 --Added by Zhiwei for JAI Trigger elimination begin
2286 ---------------------------------------------------------
2287 open c_get_rec(V_CUSTOMER_TRX_ID);
2288 fetch c_get_rec
2289 into t_rec_new;
2290 close c_get_rec;
2291
2292 if (t_rec_new.once_completed_flag = 'Y') then
2293
2294 lv_action := jai_constants.updating;
2295
2296 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
2297 pr_new => t_rec_new,
2298 pv_action => lv_action,
2299 pv_return_code => lv_return_code,
2300 pv_return_message => lv_return_message);
2301
2302 IF lv_return_code <> jai_constants.successful then
2303 RAISE le_error;
2304 END IF;
2305
2306 end if;
2307 ---------------------------------------------------------
2308 --Added by Zhiwei for JAI Trigger elimination end
2309 RETURN;
2310 END IF;
2311 IF pr_new.created_from = 'RAXTRX' THEN
2312 --Added by Zhiwei for JAI Trigger elimination begin
2313 ---------------------------------------------------------
2314 open c_get_rec(V_CUSTOMER_TRX_ID);
2315 fetch c_get_rec
2316 into t_rec_old;
2317 close c_get_rec;
2318 ---------------------------------------------------------
2319 --Added by Zhiwei for JAI Trigger elimination end
2320
2321 UPDATE JAI_AR_TRXS
2322 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
2323 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
2324
2325 --Added by Zhiwei for JAI Trigger elimination begin
2326 ---------------------------------------------------------
2327 open c_get_rec(V_CUSTOMER_TRX_ID);
2328 fetch c_get_rec
2329 into t_rec_new;
2330 close c_get_rec;
2331
2332 if (t_rec_new.once_completed_flag = 'Y') then
2333
2334 lv_action := jai_constants.updating;
2335
2336 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
2337 pr_new => t_rec_new,
2338 pv_action => lv_action,
2339 pv_return_code => lv_return_code,
2340 pv_return_message => lv_return_message);
2341
2342 IF lv_return_code <> jai_constants.successful then
2343 RAISE le_error;
2344 END IF;
2345
2346 end if;
2347 ---------------------------------------------------------
2348 --Added by Zhiwei for JAI Trigger elimination end
2349 ELSE
2350
2351 IF NVL(v_org_id, 999999) = 999999 THEN
2352
2353 IF v_parent_trx_number IS NULL THEN
2354 RETURN;
2355 ELSE
2356 OPEN organization_cur;
2357 FETCH organization_cur
2358 INTO v_org_id, v_loc_id;
2359 CLOSE organization_cur;
2360 v_rg_flag := 'Y';
2361 END IF;
2362 END IF;
2363 IF NVL(v_org_id, 999999) = 999999 THEN
2364 RETURN;
2365 END IF;
2366 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_org_id,
2367 v_loc_id,
2368 NVL(pr_new.batch_source_id,
2369 0),
2370 'N',
2371 v_register_id,
2372 v_reg_code);
2373
2374 OPEN register_code_meaning_cur(v_reg_code, 'JAI_REGISTER_TYPE');
2375 FETCH register_code_meaning_cur
2376 INTO v_meaning;
2377 CLOSE register_code_meaning_cur;
2378 OPEN fin_year_cur(v_org_id);
2379 FETCH fin_year_cur
2380 INTO v_fin_year;
2381 CLOSE fin_year_cur;
2382 OPEN Batch_Source_Name_Cur;
2383 FETCH Batch_Source_Name_Cur
2384 INTO v_order_invoice_type;
2385 CLOSE Batch_Source_Name_Cur;
2386
2387 IF v_reg_code IN ('DOMESTIC_EXCISE',
2388 'EXPORT_EXCISE',
2389 'DOM_WITHOUT_EXCISE',
2390 'BOND_REG') THEN
2391 v_rg_flag := 'Y';
2392
2393 ELSIF upper(v_reg_code) IN
2394 ('23D_DOMESTIC_EXCISE',
2395 '23D_EXPORT_EXCISE',
2396 '23D_DOM_WITHOUT_EXCISE',
2397 '23D_EXPORT_WITHOUT_EXCISE') THEN
2398 v_rg_flag := 'N';
2399 END IF;
2400
2401 v_update_rg_flag := 'Y';
2402
2403 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
2404 FETCH REG_BALANCE_CUR
2405 INTO v_rg23a_bal, v_rg23c_bal, v_pla_bal;
2406 CLOSE REG_BALANCE_CUR;
2407 OPEN ssi_unit_flag_cur(v_org_id, v_loc_id);
2408 FETCH ssi_unit_flag_cur
2409 INTO v_ssi_unit_flag, v_trading_flag;
2410 CLOSE ssi_unit_flag_cur;
2411
2412 IF NVL(v_complete_flag, 'N') IN ('N', 'A', 'C', 'P') AND
2413 (v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
2414 v_reg_code IS NOT NULL THEN
2415
2416 FOR Line_Rec IN Line_Cur LOOP
2417 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id,
2418 Line_Rec.Inventory_Item_ID,
2419 v_org_id) LOOP
2420 IF excise_cal_rec.t_type IN ('Excise') THEN
2421 v_basic_ed := NVL(v_basic_ed, 0) +
2422 NVL(excise_cal_rec.func_amt, 0);
2423 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
2424 v_additional_ed := NVL(v_additional_ed, 0) +
2425 NVL(excise_cal_rec.func_amt, 0);
2426 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
2427 v_other_ed := NVL(v_other_ed, 0) +
2428 NVL(excise_cal_rec.func_amt, 0);
2429 END IF;
2430 END LOOP;
2431 v_tax_amount := NVL(v_basic_ed, 0) + NVL(v_additional_ed, 0) +
2432 NVL(v_other_ed, 0);
2433 IF v_reg_code IN ('DOMESTIC_EXCISE', 'EXPORT_EXCISE') THEN
2434 OPEN item_class_cur(V_ORG_ID, Line_Rec.Inventory_Item_Id);
2435 FETCH item_class_cur
2436 INTO v_item_class, v_excise_flag;
2437 CLOSE item_class_cur;
2438
2439 IF NVL(v_excise_flag, 'N') = 'Y' THEN
2440 IF NVL(v_ssi_unit_flag, 'N') = 'N' AND
2441 NVL(line_rec.excise_exempt_type, '@@@') NOT IN
2442 ('CT2',
2443 'EXCISE_EXEMPT_CERT',
2444 'CT2_OTH',
2445 'EXCISE_EXEMPT_CERT_OTH') THEN
2446 IF v_item_class IN ('CGEX', 'CGIN') THEN
2447 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount, 0) +
2448 NVL(v_tax_amount, 0);
2449 ELSIF v_item_class IN ('RMIN', 'RMEX') THEN
2450 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount, 0) +
2451 NVL(v_tax_amount, 0);
2452 ELSIF v_item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX') THEN
2453 v_other_tax_amount := NVL(v_other_tax_amount, 0) +
2454 NVL(v_tax_amount, 0);
2455 ELSIF NVL(v_ssi_unit_flag, 'N') = 'N' AND
2456 NVL(line_rec.excise_exempt_type, '@@@') IN
2457 ('CT2',
2458 'EXCISE_EXEMPT_CERT',
2459 'CT2_OTH',
2460 'EXCISE_EXEMPT_CERT_OTH') THEN
2461 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
2462 IF line_rec.excise_exempt_type IN
2463 ('CT2 - OTHERS', 'Excise Exempted OTHERS') THEN
2464 OPEN for_modvat_tax_rate(line_rec.customer_trx_line_id);
2465 FETCH for_modvat_tax_rate
2466 INTO v_modvat_tax_rate;
2467 CLOSE for_modvat_tax_rate;
2468 ELSE
2469 OPEN for_modvat_percentage(v_org_id, v_loc_id);
2470 FETCH for_modvat_percentage
2471 INTO v_modvat_tax_rate;
2472 CLOSE for_modvat_percentage;
2473 END IF;
2474 v_exempt_bal := (NVL(v_exempt_bal, 0) +
2475 line_rec.quantity *
2476 line_rec.assessable_value *
2477 NVL(v_modvat_tax_rate, 0)) / 100;
2478 IF v_exempt_bal > v_rg23a_bal THEN
2479
2480 pv_return_code := jai_constants.expected_error;
2481 pv_return_message := 'Register RG23A PART II Balance -> ' ||
2482 TO_CHAR(v_rg23a_bal) ||
2483 ' IS less than the Modvat Amount ->' ||
2484 TO_CHAR(v_exempt_bal);
2485 return;
2486 END IF;
2487 END IF;
2488 END IF;
2489 END IF;
2490 END IF;
2491 ELSIF v_reg_code IN ('BOND_REG') THEN
2492 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_register_id,
2493 v_register_balance,
2494 v_register_exp_date,
2495 v_lou_flag);
2496
2497 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(pr_new.set_of_books_id,
2498 pr_new.invoice_currency_code,
2499 pr_new.exchange_date,
2500 pr_new.exchange_rate_type,
2501 pr_new.exchange_rate);
2502 v_bond_tax_amount := NVL(v_tax_amount, 0) +
2503 NVL(v_bond_tax_amount, 0);
2504
2505 IF (v_register_balance < v_bond_tax_amount) AND
2506 (NVL(v_lou_flag, 'N') = 'N') THEN
2507
2508 pv_return_code := jai_constants.expected_error;
2509 pv_return_message := 'Bonded Register Has Balance -> ' ||
2510 TO_CHAR(v_register_balance) ||
2511 ' ,which IS less than Excisable Amount -> ' ||
2512 TO_CHAR(v_bond_tax_amount);
2513 return;
2514 END IF;
2515
2516 IF (nvl(v_register_exp_date, sysdate) < Sysdate) THEN
2517
2518 pv_return_code := jai_constants.expected_error;
2519 pv_return_message := 'Validity Date of the Bond Register has expired';
2520 return;
2521 END IF;
2522 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE',
2523 '23D_EXPORT_EXCISE',
2524 '23D_DOM_WITHOUT_EXCISE',
2525 '23D_EXPORT_WITHOUT_EXCISE') THEN
2526
2527 IF v_trading_flag = 'Y' AND v_update_rg23d_flag = 'Y' THEN
2528
2529 if line_rec.inventory_item_id is not null then
2530 OPEN matched_qty_cur(line_rec.customer_trx_line_id);
2531 FETCH matched_qty_cur
2532 INTO v_matched_qty;
2533 CLOSE matched_qty_cur;
2534 IF NVL(v_matched_qty, 0) <> NVL(line_rec.quantity, 0) THEN
2535
2536 pv_return_code := jai_constants.expected_error;
2537 pv_return_message := 'Matched Quantity -> ' ||
2538 TO_CHAR(v_matched_qty) ||
2539 ' , IS less than Invoiced Quantity -> ' ||
2540 TO_CHAR(line_rec.quantity) ||
2541 ' ,FOR line NUMBER -> ' ||
2542 TO_CHAR(line_rec.line_number);
2543 return;
2544 EXIT;
2545 END IF;
2546 END IF;
2547
2548 IF v_reg_code = '23D_EXPORT_WITHOUT_EXCISE' THEN
2549 v_rg23d_tax_amount := NVL(v_tax_amount, 0) +
2550 NVL(v_rg23d_tax_amount, 0);
2551 IF NVL(v_rg23d_register_balance, 0) <
2552 NVL(v_rg23d_tax_amount, 0) and
2553 (NVL(v_lou_flag, 'N') = 'N') THEN
2554
2555 pv_return_code := jai_constants.expected_error;
2556 pv_return_message := 'RG23D Bonded Register Has Balance -> ' ||
2557 TO_CHAR(v_rg23d_register_balance) ||
2558 ' ,which IS less than Excisable Amount -> ' ||
2559 TO_CHAR(v_rg23d_tax_amount);
2560 return;
2561 END IF;
2562
2563 IF (v_register_exp_date > Sysdate) THEN
2564
2565 pv_return_code := jai_constants.expected_error;
2566 pv_return_message := 'Validity Date of the Bond Register has expired';
2567 return;
2568
2569 END IF;
2570 END IF;
2571 END IF;
2572 END IF;
2573 END LOOP;
2574 v_basic_Ed := 0;
2575 v_additional_ed := 0;
2576 v_other_ed := 0;
2577 v_tax_amount := 0;
2578 v_other_tax_amount := 0;
2579 v_rg23a_tax_amount := 0;
2580 v_rg23c_tax_amount := 0;
2581 v_rg23d_tax_Amount := 0;
2582
2583 FOR Line_Rec IN Line_Cur LOOP
2584
2585 Open item_class_cur(v_org_id, line_rec.Inventory_item_id);
2586 fetch item_class_cur
2587 into v_item_class, v_excise_flag;
2588 close item_class_cur;
2589
2590 IF NVL(v_excise_flag, 'N') = 'Y' THEN
2591 IF v_invoice_no is Null THEN
2592 jai_cmn_setup_pkg.generate_excise_invoice_no(v_org_id,
2593 v_loc_id,
2594 'I',
2595 pr_new.batch_source_id,
2596 v_fin_year,
2597 v_invoice_no,
2598 v_errbuf);
2599 END IF;
2600
2601 IF v_errbuf is not null THEN
2602
2603 pv_return_code := jai_constants.expected_error;
2604 pv_return_message := 'Error During Excise Invoice Generation ! ' ||
2605 v_errbuf;
2606 return;
2607 END IF;
2608
2609 IF NVL(v_item_class, '~') not in ('OTIN') THEN
2610
2611 UPDATE JAI_AR_TRX_LINES
2612 SET EXCISE_INVOICE_NO = v_invoice_no,
2613 EXCISE_INVOICE_DATE = SYSDATE
2614 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id
2615 AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
2616 AND CUSTOMER_TRX_ID = v_customer_trx_id;
2617 END IF;
2618 END IF;
2619 END LOOP;
2620
2621 open c_total_Excise_amt;
2622 fetch c_total_Excise_amt
2623 into v_total_excise_amt;
2624 close c_total_Excise_amt;
2625
2626 open c_cess_amount;
2627 fetch c_cess_amount
2628 into ln_Cess_amount;
2629 close c_cess_amount;
2630
2631 lv_register_type := 'RG23A';
2632 jai_cmn_rg_others_pkg.check_balances(p_organization_id => v_org_id,
2633 p_location_id => v_loc_id,
2634 p_register_type => lv_register_type,
2635 p_trx_amount => ln_cess_amount,
2636 p_process_flag => lv_process_flag,
2637 p_process_message => lv_process_message);
2638
2639 if lv_process_flag <> jai_constants.successful then
2640 lv_rg23a_cess_avlbl := 'FALSE';
2641 else
2642 lv_rg23a_cess_avlbl := 'TRUE';
2643 end if;
2644
2645 lv_register_type := 'RG23C';
2646 jai_cmn_rg_others_pkg.check_balances(p_organization_id => v_org_id,
2647 p_location_id => v_loc_id,
2648 p_register_type => lv_register_type,
2649 p_trx_amount => ln_cess_amount,
2650 p_process_flag => lv_process_flag,
2651 p_process_message => lv_process_message);
2652
2653 if lv_process_flag <> jai_constants.successful then
2654 lv_rg23c_cess_avlbl := 'FALSE';
2655 else
2656 lv_rg23c_cess_avlbl := 'TRUE';
2657 end if;
2658
2659 lv_register_type := 'PLA';
2660 jai_cmn_rg_others_pkg.check_balances(p_organization_id => v_org_id,
2661 p_location_id => v_loc_id,
2662 p_register_type => lv_register_type,
2663 p_trx_amount => ln_cess_amount,
2664 p_process_flag => lv_process_flag,
2665 p_process_message => lv_process_message);
2666
2667 if lv_process_flag <> jai_constants.successful then
2668 lv_pla_cess_avlbl := 'FALSE';
2669 else
2670 lv_pla_cess_avlbl := 'TRUE';
2671 end if;
2672
2673 FOR Line_Rec IN Line_Cur LOOP
2674 OPEN item_class_cur(V_ORG_ID, Line_Rec.Inventory_Item_Id);
2675 FETCH item_class_cur
2676 INTO v_item_class, v_excise_flag;
2677 CLOSE item_class_cur;
2678 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id,
2679 Line_Rec.Inventory_Item_ID,
2680 v_org_id) LOOP
2681 IF excise_cal_rec.t_type IN ('Excise') THEN
2682 v_basic_ed := NVL(v_basic_ed, 0) +
2683 NVL(excise_cal_rec.func_amt, 0);
2684 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
2685 v_additional_ed := NVL(v_additional_ed, 0) +
2686 NVL(excise_cal_rec.func_amt, 0);
2687 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
2688 v_other_ed := NVL(v_other_ed, 0) +
2689 NVL(excise_cal_rec.func_amt, 0);
2690 END IF;
2691 END LOOP;
2692 v_tax_amount := NVL(v_basic_ed, 0) + NVL(v_additional_ed, 0) +
2693 NVL(v_other_ed, 0);
2694 v_basic_Ed := 0;
2695 v_additional_ed := 0;
2696 v_other_ed := 0;
2697 IF v_item_class IN ('CGEX', 'CGIN') THEN
2698 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount, 0) +
2699 NVL(v_tax_amount, 0);
2700 ELSIF v_item_class IN ('RMIN', 'RMEX') THEN
2701 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount, 0) +
2702 NVL(v_tax_amount, 0);
2703 ELSIF v_item_class IN ('FGIN', 'FGEX', 'CCIN', 'CCEX') THEN
2704 v_other_tax_amount := NVL(v_other_tax_amount, 0) +
2705 NVL(v_tax_amount, 0);
2706 END IF;
2707
2708 v_tax_amount := v_total_excise_amt;
2709
2710 IF NVL(v_excise_flag, 'N') = 'Y' THEN
2711 IF NVL(v_ssi_unit_flag, 'N') = 'N' THEN
2712
2713 IF v_complete_flag IN ('N', 'A', 'C', 'P') THEN
2714 IF v_rg_flag = 'Y' THEN
2715 IF v_reg_code IN ('DOMESTIC_EXCISE', 'EXPORT_EXCISE') THEN
2716
2717 IF v_item_class IN ('FGIN',
2718 'FGEX',
2719 'CCIN',
2720 'CCEX',
2721 'CGIN',
2722 'CGEX',
2723 'RMIN',
2724 'RMEX') THEN
2725 IF v_reg_type IS NULL THEN
2726 OPEN preference_reg_cur(v_org_id, v_loc_id);
2727 FETCH preference_reg_cur
2728 INTO rg23a, rg23c, pla;
2729 CLOSE preference_reg_cur;
2730
2731 FOR reg_balance IN reg_balance_cur(v_org_id,
2732 v_loc_id) LOOP
2733 IF rg23a = 1 THEN
2734 IF reg_balance.rg23a_balance >= v_tax_amount AND
2735 lv_rg23a_cess_avlbl = 'TRUE' THEN
2736 v_rg23a_tax_amount := v_tax_amount;
2737 v_reg_type := 'RG23A';
2738 ELSE
2739 IF rg23c = 2 THEN
2740 IF reg_balance.rg23c_balance >=
2741 v_tax_amount AND
2742 lv_rg23c_cess_avlbl = 'TRUE' THEN
2743 v_rg23c_tax_amount := v_tax_amount;
2744 v_reg_type := 'RG23C';
2745 ELSIF reg_balance.pla_balance >=
2746 v_tax_amount AND
2747 lv_pla_cess_avlbl = 'TRUE' THEN
2748 v_reg_type := 'PLA';
2749 END IF;
2750 ELSIF pla = 2 THEN
2751 IF reg_balance.pla_balance >=
2752 v_tax_amount AND
2753 lv_pla_cess_avlbl = 'TRUE' THEN
2754 v_reg_type := 'PLA';
2755 ELSIF reg_balance.rg23c_balance >=
2756 v_tax_amount AND
2757 lv_rg23c_cess_avlbl = 'TRUE' THEN
2758 v_rg23c_tax_amount := v_tax_amount;
2759 v_reg_type := 'RG23C';
2760 END IF;
2761 END IF;
2762 END IF;
2763 ELSIF rg23c = 1 THEN
2764 IF reg_balance.rg23c_balance >= v_tax_amount AND
2765 lv_rg23c_cess_avlbl = 'TRUE' THEN
2766 v_rg23c_tax_amount := v_tax_amount;
2767 v_reg_type := 'RG23C';
2768 ELSE
2769 IF rg23a = 2 THEN
2770 IF reg_balance.rg23a_balance >=
2771 v_tax_amount AND
2772 lv_rg23a_cess_avlbl = 'TRUE' THEN
2773 v_rg23a_tax_amount := v_tax_amount;
2774 v_reg_type := 'RG23A';
2775 ELSIF reg_balance.pla_balance >=
2776 v_tax_amount AND
2777 lv_pla_cess_avlbl = 'TRUE' THEN
2778 v_reg_type := 'PLA';
2779 END IF;
2780 ELSIF pla = 2 THEN
2781 IF reg_balance.pla_balance >=
2782 v_tax_amount AND
2783 lv_pla_cess_avlbl = 'TRUE' THEN
2784 v_reg_type := 'PLA';
2785 ELSIF reg_balance.rg23a_balance >=
2786 v_tax_amount AND
2787 lv_rg23a_cess_avlbl = 'TRUE' THEN
2788 v_rg23a_tax_amount := v_tax_amount;
2789 v_reg_type := 'RG23A';
2790 END IF;
2791 END IF;
2792 END IF;
2793 ELSIF pla = 1 THEN
2794 IF reg_balance.pla_balance >= v_tax_amount AND
2795 lv_pla_cess_avlbl = 'TRUE' THEN
2796 v_reg_type := 'PLA';
2797 ELSE
2798 IF rg23c = 2 THEN
2799 IF reg_balance.rg23c_balance >=
2800 v_tax_amount AND
2801 lv_rg23c_cess_avlbl = 'TRUE' THEN
2802 v_rg23c_tax_amount := v_tax_amount;
2803 v_reg_type := 'RG23C';
2804 ELSIF reg_balance.rg23a_balance >=
2805 v_tax_amount AND
2806 lv_rg23a_cess_avlbl = 'TRUE' THEN
2807 v_rg23a_tax_amount := v_tax_amount;
2808 v_reg_type := 'RG23A';
2809 END IF;
2810 ELSIF rg23a = 2 THEN
2811 IF reg_balance.rg23a_balance >=
2812 v_tax_amount AND
2813 lv_rg23a_cess_avlbl = 'TRUE' THEN
2814 v_rg23a_tax_amount := v_tax_amount;
2815 v_reg_type := 'RG23A';
2816 ELSIF reg_balance.rg23c_balance >=
2817 v_tax_amount AND
2818 lv_rg23c_cess_avlbl = 'TRUE' THEN
2819 v_rg23c_tax_amount := v_tax_amount;
2820 v_reg_type := 'RG23C';
2821 END IF;
2822 END IF;
2823 END IF;
2824 END IF;
2825
2826 IF v_reg_type is null THEN
2827
2828 pv_return_code := jai_constants.expected_error;
2829 pv_return_message := 'None of the registers have enough balance for the excise duty -> ' ||
2830 v_tax_amount ||
2831 ' Or Cess amount => ' ||
2832 ln_Cess_amount;
2833 return;
2834 END IF;
2835 IF v_reg_type = 'PLA' and
2836 NVL(v_ssi_unit_flag, 'N') <> 'Y' THEN
2837 IF v_tax_amount > reg_balance.pla_balance AND
2838 lv_pla_cess_avlbl = 'TRUE' THEN
2839
2840 pv_return_code := jai_constants.expected_error;
2841 pv_return_message := 'PLA Balance -> ' ||
2842 reg_balance.pla_balance ||
2843 ' is not enough for the excise duty -> ' ||
2844 v_tax_amount;
2845 return;
2846 END IF;
2847 ELSIF v_reg_type = 'RG23A' THEN
2848 IF v_tax_amount > reg_balance.rg23a_balance AND
2849 lv_rg23a_cess_avlbl = 'TRUE' THEN
2850
2851 pv_return_code := jai_constants.expected_error;
2852 pv_return_message := 'RG23A Balance -> ' ||
2853 reg_balance.rg23a_balance ||
2854 ' is not enough for the excise duty -> ' ||
2855 v_tax_amount;
2856 return;
2857 END IF;
2858 ELSIF v_reg_type = 'RG23C' THEN
2859 IF v_tax_amount > reg_balance.rg23c_balance AND
2860 lv_rg23c_cess_avlbl = 'TRUE' THEN
2861
2862 pv_return_code := jai_constants.expected_error;
2863 pv_return_message := 'RG23C Balance -> ' ||
2864 reg_balance.rg23c_balance ||
2865 ' is not enough for the excise duty -> ' ||
2866 v_tax_amount;
2867 return;
2868 END IF;
2869 END IF;
2870 END LOOP;
2871 END IF;
2872 v_excise_paid_register := v_reg_type;
2873 END IF;
2874 END IF;
2875
2876 Declare
2877 v_reg_type VARCHAR2(10);
2878 Begin
2879 SELECT once_completed_flag
2880 INTO v_reg_type
2881 FROM JAI_AR_TRXS
2882 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
2883
2884 IF v_reg_type = 'P' THEN
2885 v_reg_type := 'PLA';
2886 ELSIF v_reg_type = 'A' THEN
2887 v_reg_type := 'RG23A';
2888 ELSIF v_reg_type = 'C' THEN
2889 v_reg_type := 'RG23C';
2890 END IF;
2891
2892 IF v_reg_type is not null and v_reg_type <> 'N' THEN
2893 v_excise_paid_register := v_reg_type;
2894 END IF;
2895
2896 Exception
2897 When Others Then
2898
2899 pv_return_code := jai_constants.expected_error;
2900 pv_return_message := SQLERRM;
2901 return;
2902 End;
2903
2904 UPDATE JAI_AR_TRX_LINES
2905 SET PAYMENT_REGISTER = v_excise_paid_register
2906 WHERE CUSTOMER_TRX_LINE_ID =
2907 LINE_REC.customer_trx_line_id
2908 AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
2909 AND CUSTOMER_TRX_ID = v_customer_trx_id;
2910 END IF;
2911 v_excise_paid_register := '';
2912 END IF;
2913 ELSIF NVL(v_ssi_unit_flag, 'N') = 'Y' THEN
2914 IF v_item_class IN ('RMIN',
2915 'RMEX',
2916 'CGEX',
2917 'CGIN',
2918 'FGIN',
2919 'FGEX',
2920 'CCIN',
2921 'CCEX') THEN
2922
2923 IF v_complete_flag IN ('N', 'A', 'C', 'P') THEN
2924 IF v_rg_flag = 'Y' THEN
2925 IF v_reg_code IN ('DOMESTIC_EXCISE', 'EXPORT_EXCISE') THEN
2926 IF v_reg_type IS NULL THEN
2927 OPEN preference_reg_cur(v_org_id, v_loc_id);
2928 FETCH preference_reg_cur
2929 INTO rg23a, rg23c, pla;
2930 CLOSE preference_reg_cur;
2931 FOR reg_balance IN reg_balance_cur(v_org_id,
2932 v_loc_id) LOOP
2933
2934 IF rg23a = 1 THEN
2935 IF reg_balance.rg23a_balance >= v_tax_amount AND
2936 lv_rg23a_cess_avlbl = 'TRUE' THEN
2937 v_rg23a_tax_amount := v_tax_amount;
2938 v_reg_type := 'RG23A';
2939 ELSE
2940 IF rg23c = 2 THEN
2941 IF reg_balance.rg23c_balance >=
2942 v_tax_amount AND
2943 lv_rg23c_cess_avlbl = 'TRUE' THEN
2944 v_rg23c_tax_amount := v_tax_amount;
2945 v_reg_type := 'RG23C';
2946 ELSE
2947 v_reg_type := 'PLA';
2948 END IF;
2949 ELSIF pla = 2 THEN
2950 v_reg_type := 'PLA';
2951 END IF;
2952 END IF;
2953 ELSIF rg23c = 1 THEN
2954 IF reg_balance.rg23c_balance >= v_tax_amount AND
2955 lv_rg23c_cess_avlbl = 'TRUE' THEN
2956 v_rg23c_tax_amount := v_tax_amount;
2957 v_reg_type := 'RG23C';
2958 ELSE
2959 IF rg23a = 2 THEN
2960 IF reg_balance.rg23a_balance >=
2961 v_tax_amount AND
2962 lv_rg23a_cess_avlbl = 'TRUE' THEN
2963 v_rg23a_tax_amount := v_tax_amount;
2964 v_reg_type := 'RG23A';
2965 ELSE
2966 v_reg_type := 'PLA';
2967 END IF;
2968 ELSIF pla = 2 THEN
2969 v_reg_type := 'PLA';
2970 END IF;
2971 END IF;
2972 ELSIF pla = 1 THEN
2973 v_reg_type := 'PLA';
2974 END IF;
2975
2976 IF v_reg_type = 'RG23A' THEN
2977 IF v_tax_amount > reg_balance.rg23a_balance AND
2978 lv_rg23a_cess_avlbl = 'TRUE' THEN
2979
2980 pv_return_code := jai_constants.expected_error;
2981 pv_return_message := 'RG23A Balance -> ' ||
2982 reg_balance.rg23a_balance ||
2983 ' is not enough for the excise duty -> ' ||
2984 v_tax_amount;
2985 return;
2986 END IF;
2987 ELSIF v_reg_type = 'RG23C' THEN
2988 IF v_tax_amount > reg_balance.rg23c_balance AND
2989 lv_rg23c_cess_avlbl = 'TRUE' THEN
2990
2991 pv_return_code := jai_constants.expected_error;
2992 pv_return_message := 'RG23C Balance -> ' ||
2993 reg_balance.rg23c_balance ||
2994 ' is not enough for the excise duty -> ' ||
2995 v_tax_amount;
2996 return;
2997 END IF;
2998 END IF;
2999
3000 END LOOP;
3001 END IF;
3002
3003 v_excise_paid_register := v_reg_type;
3004
3005 END IF;
3006
3007 Declare
3008 v_reg_type1 VARCHAR2(10);
3009 Begin
3010 SELECT once_completed_flag
3011 INTO v_reg_type1
3012 FROM JAI_AR_TRXS
3013 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
3014
3015 If v_reg_type1 = 'P' THEN
3016 v_reg_type1 := 'PLA';
3017 ELSIF v_reg_type1 = 'A' THEN
3018 v_reg_type1 := 'RG23A';
3019 ELSIF v_reg_type1 = 'C' THEN
3020 v_reg_type1 := 'RG23C';
3021 END IF;
3022
3023 if v_reg_type1 is not null and v_reg_type1 <> 'N' then
3024 v_excise_paid_register := v_reg_type1;
3025 end if;
3026
3027 Exception
3028 When Others Then
3029
3030 pv_return_code := jai_constants.expected_error;
3031 pv_return_message := SQLERRM;
3032 return;
3033 END;
3034
3035 UPDATE JAI_AR_TRX_LINES
3036 SET PAYMENT_REGISTER = v_excise_paid_register
3037 WHERE CUSTOMER_TRX_LINE_ID =
3038 LINE_REC.customer_trx_line_id
3039 AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
3040 AND CUSTOMER_TRX_ID = v_customer_trx_id;
3041 END IF;
3042 v_excise_paid_register := '';
3043 END IF;
3044 END IF;
3045 END IF;
3046 END IF;
3047
3048 END LOOP;
3049 INSERT INTO JAI_AR_TRX_INS_HDRS_T
3050 (ORGANIZATION_ID,
3051 LOCATION_ID,
3052 CUSTOMER_TRX_ID,
3053 SHIP_TO_CUSTOMER_ID,
3054 SHIP_TO_SITE_USE_ID,
3055 CUST_TRX_TYPE_ID,
3056 TRX_DATE,
3057 SOLD_TO_CUSTOMER_ID,
3058 BATCH_SOURCE_ID,
3059 BILL_TO_CUSTOMER_ID,
3060 BILL_TO_SITE_USE_ID,
3061 CREATED_BY,
3062 CREATION_DATE,
3063 LAST_UPDATED_BY,
3064 LAST_UPDATE_DATE)
3065 VALUES
3066 (V_ORG_ID,
3067 V_LOC_ID,
3068 V_CUSTOMER_TRX_ID,
3069 pr_new.SHIP_TO_CUSTOMER_ID,
3070 pr_new.SHIP_TO_SITE_USE_ID,
3071 pr_new.CUST_TRX_TYPE_ID,
3072 pr_new.TRX_DATE,
3073 pr_new.SOLD_TO_CUSTOMER_ID,
3074 pr_new.BATCH_SOURCE_ID,
3075 pr_new.BILL_TO_CUSTOMER_ID,
3076 pr_new.BILL_TO_SITE_USE_ID,
3077 FND_GLOBAL.USER_ID,
3078 SYSDATE,
3079 FND_GLOBAL.USER_ID,
3080 SYSDATE);
3081 END IF;
3082 --Added by Zhiwei for JAI Trigger elimination begin
3083 ---------------------------------------------------------
3084 open c_get_rec(V_CUSTOMER_TRX_ID);
3085 fetch c_get_rec
3086 into t_rec_old;
3087 close c_get_rec;
3088 ---------------------------------------------------------
3089 --Added by Zhiwei for JAI Trigger elimination end
3090
3091 UPDATE JAI_AR_TRXS
3092 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
3093 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
3094
3095 --Added by Zhiwei for JAI Trigger elimination begin
3096 ---------------------------------------------------------
3097 open c_get_rec(V_CUSTOMER_TRX_ID);
3098 fetch c_get_rec
3099 into t_rec_new;
3100 close c_get_rec;
3101
3102 if (t_rec_new.once_completed_flag = 'Y') then
3103
3104 lv_action := jai_constants.updating;
3105
3106 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
3107 pr_new => t_rec_new,
3108 pv_action => lv_action,
3109 pv_return_code => lv_return_code,
3110 pv_return_message => lv_return_message);
3111
3112 IF lv_return_code <> jai_constants.successful then
3113 RAISE le_error;
3114 END IF;
3115
3116 end if;
3117 ---------------------------------------------------------
3118 --Added by Zhiwei for JAI Trigger elimination end
3119 END IF;
3120 END IF;
3121
3122 IF NVL(v_trans_type, 'N') NOT IN ('INV', 'DM') THEN
3123 RETURN;
3124 END IF;
3125
3126 IF nvl(pr_new.created_from, '###') = 'ARXTWMAI' THEN
3127
3128 OPEN c_vat_invoice_cur;
3129 FETCH c_vat_invoice_cur
3130 INTO lv_vat_invoice_number;
3131 CLOSE c_vat_invoice_cur;
3132
3133 IF lv_vat_invoice_number IS NOT NULL THEN
3134 return;
3135 END IF;
3136
3137 OPEN cur_vat_taxes_exist;
3138 FETCH cur_vat_taxes_exist
3139 into ln_regime_id, ln_regime_code;
3140
3141 IF ln_regime_id IS NULL THEN
3142 lv_vat_reversal := 'VAT REVERSAL';
3143 OPEN c_chk_vat_reversal(lv_vat_reversal);
3144 FETCH c_chk_vat_reversal
3145 INTO ln_vat_reversal_exists;
3146 CLOSE c_chk_vat_reversal;
3147
3148 IF ln_vat_reversal_exists = 1 THEN
3149 OPEN c_get_regime_id;
3150 FETCH c_get_regime_id
3151 INTO ln_regime_id;
3152 CLOSE c_get_regime_id;
3153
3154 IF ln_regime_id IS NOT NULL THEN
3155 ln_regime_code := jai_constants.vat_regime;
3156 END IF;
3157 END IF;
3158 END IF;
3159
3160 IF UPPER(nvl(ln_regime_code, '####')) = jai_constants.vat_regime THEN
3161
3162 OPEN cur_get_same_inv_no(cp_organization_id => v_org_id,
3163 cp_location_id => v_loc_id);
3164 FETCH cur_get_same_inv_no
3165 INTO lv_vat_no_same_exc_no;
3166 CLOSE cur_get_same_inv_no;
3167
3168 IF nvl(lv_vat_no_same_exc_no, 'N') = 'Y' THEN
3169
3170 OPEN cur_get_exc_inv_no;
3171 FETCH cur_get_exc_inv_no
3172 INTO lv_vat_invoice_number;
3173 CLOSE cur_get_exc_inv_no;
3174 END IF;
3175
3176 IF lv_vat_invoice_number IS NULL THEN
3177
3178 IF check_reg_dealer(NVL(pr_new.SHIP_TO_CUSTOMER_ID,
3179 pr_new.BILL_TO_CUSTOMER_ID),
3180 NVL(pr_new.SHIP_TO_SITE_USE_ID,
3181 pr_new.BILL_TO_SITE_USE_ID)) THEN
3182 lv_doc_type_class := 'I';
3183 ELSE
3184 lv_doc_type_class := 'UI';
3185 END IF;
3186
3187 jai_cmn_rgm_setup_pkg.gen_invoice_number(p_regime_id => ln_regime_id,
3188 p_organization_id => v_org_id,
3189 p_location_id => v_loc_id,
3190 p_date => pr_new.trx_date,
3191 p_doc_class => lv_doc_type_class,
3192 p_doc_type_id => pr_new.batch_source_id,
3193 p_invoice_number => lv_vat_invoice_number,
3194 p_process_flag => lv_process_flag,
3195 p_process_msg => lv_process_message);
3196
3197 IF lv_process_flag = jai_constants.expected_error OR
3198 lv_process_flag = jai_constants.unexpected_error THEN
3199 CLOSE cur_vat_taxes_exist;
3200
3201 pv_return_code := jai_constants.expected_error;
3202 pv_return_message := lv_process_message;
3203 return;
3204
3205 END IF;
3206 END IF;
3207
3208 OPEN cur_get_gl_date('REC');
3209 FETCH cur_get_gl_date
3210 INTO ld_gl_date;
3211 CLOSE cur_get_gl_date;
3212
3213 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice(p_regime_id => ln_regime_id,
3214 p_source => jai_constants.source_ar,
3215 p_organization_id => v_org_id,
3216 p_location_id => v_loc_id,
3217 p_delivery_id => NULL,
3218 p_customer_trx_id => pr_new.customer_trx_id,
3219 p_transaction_type => v_trans_type,
3220 p_vat_invoice_no => lv_vat_invoice_number,
3221 p_default_invoice_date => nvl(ld_gl_date,
3222 pr_new.trx_date),
3223 p_batch_id => NULL,
3224 p_called_from => jai_constants.vat_repo_call_inv_comp,
3225 p_debug => jai_constants.no,
3226 p_process_flag => lv_process_flag,
3227 p_process_message => lv_process_message);
3228
3229 IF lv_process_flag = jai_constants.expected_error OR
3230 lv_process_flag = jai_constants.unexpected_error THEN
3231 CLOSE cur_vat_taxes_exist;
3232
3233 pv_return_code := jai_constants.expected_error;
3234 pv_return_message := lv_process_message;
3235 return;
3236
3237 END IF;
3238
3239 --Added by Zhiwei for JAI Trigger elimination begin
3240 ---------------------------------------------------------
3241 open c_get_rec(pr_new.customer_trx_id);
3242 fetch c_get_rec
3243 into t_rec_old;
3244 close c_get_rec;
3245 ---------------------------------------------------------
3246 --Added by Zhiwei for JAI Trigger elimination end
3247
3248 UPDATE JAI_AR_TRXS
3249 SET vat_invoice_no = lv_vat_invoice_number,
3250 vat_invoice_date = nvl(ld_gl_date, pr_new.trx_date)
3251 WHERE customer_trx_id = pr_new.customer_trx_id;
3252
3253 --Added by Zhiwei for JAI Trigger elimination begin
3254 ---------------------------------------------------------
3255 open c_get_rec(pr_new.customer_trx_id);
3256 fetch c_get_rec
3257 into t_rec_new;
3258 close c_get_rec;
3259
3260 if (t_rec_new.once_completed_flag = 'Y') then
3261
3262 lv_action := jai_constants.updating;
3263
3264 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
3265 pr_new => t_rec_new,
3266 pv_action => lv_action,
3267 pv_return_code => lv_return_code,
3268 pv_return_message => lv_return_message);
3269
3270 IF lv_return_code <> jai_constants.successful then
3271 RAISE le_error;
3272 END IF;
3273
3274 end if;
3275 ---------------------------------------------------------
3276 --Added by Zhiwei for JAI Trigger elimination end
3277
3278 END IF;
3279
3280 CLOSE cur_vat_taxes_exist;
3281
3282 END IF;
3283 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3284 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3285 G_MODULE_NAME || lv_api_name,
3286 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
3287 END IF;
3288 EXCEPTION
3289 WHEN OTHERS THEN
3290 Pv_return_code := jai_constants.unexpected_error;
3291 Pv_return_message := 'Encountered an error in ' || G_PACKAGE_NAME || '.' ||
3292 lv_api_name || ' ' || substr(sqlerrm, 1, 1900);
3293
3294 END PROCESS_EXCISE_TAXES;
3295
3296 PROCEDURE PROCESS_TCS_TAXES(pr_old t_jai_rec%type,
3297 pr_new t_rec%type,
3298 pv_action varchar2,
3299 pv_return_code out NOCOPY varchar2,
3300 pv_return_message out NOCOPY varchar2) IS
3301 lv_api_name CONSTANT VARCHAR2(100) := 'PROCESS_TCS_TAXES';
3302 lv_debug_info VARCHAR2(4000);
3303 LV_DOCUMENT_TYPE VARCHAR2(40);
3304 LN_REG_ID NUMBER;
3305 LV_ONCE_COMPLETED_FLAG JAI_AR_TRXS.ONCE_COMPLETED_FLAG%TYPE;
3306 V_HEADER_ID NUMBER;
3307
3308 CURSOR ONCE_COMPLETE_FLAG_CUR IS
3309 SELECT ONCE_COMPLETED_FLAG
3310 FROM JAI_AR_TRXS
3311 WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
3312
3313 BEGIN
3314 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3315 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3316 G_MODULE_NAME || lv_api_name,
3317 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
3318 END IF;
3319 V_HEADER_ID := PR_NEW.CUSTOMER_TRX_ID;
3320 IF NVL(PR_NEW.COMPLETE_FLAG, JAI_CONSTANTS.NO) = JAI_CONSTANTS.YES THEN
3321 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_COMP;
3322
3323 OPEN ONCE_COMPLETE_FLAG_CUR;
3324 FETCH ONCE_COMPLETE_FLAG_CUR
3325 INTO LV_ONCE_COMPLETED_FLAG;
3326 CLOSE ONCE_COMPLETE_FLAG_CUR;
3327
3328 IF Pr_new.created_from <> 'RAXTRX' AND
3329 lv_once_completed_flag = jai_constants.yes THEN
3330
3331 JAI_AR_TCS_REP_PKG.AR_ACCOUNTING(P_RACT => PR_NEW,
3332 P_PROCESS_FLAG => PV_RETURN_CODE,
3333 P_PROCESS_MESSAGE => PV_RETURN_MESSAGE);
3334
3335 END IF;
3336
3337 ELSIF NVL(PR_NEW.COMPLETE_FLAG, JAI_CONSTANTS.NO) = JAI_CONSTANTS.NO THEN
3338 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_INCOMP;
3339 END IF;
3340
3341 JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS(P_RACT => PR_NEW,
3342 P_EVENT => JAI_CONSTANTS.TRX_EVENT_COMPLETION,
3343 P_PROCESS_FLAG => PV_RETURN_CODE,
3344 P_PROCESS_MESSAGE => PV_RETURN_MESSAGE);
3345
3346 IF PV_RETURN_CODE <> JAI_CONSTANTS.SUCCESSFUL THEN
3347 RETURN;
3348 END IF;
3349 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3350 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3351 G_MODULE_NAME || lv_api_name,
3352 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
3353 END IF;
3354 EXCEPTION
3355 WHEN OTHERS THEN
3356 Pv_return_code := jai_constants.unexpected_error;
3357 Pv_return_message := 'Encountered an error in ' || G_PACKAGE_NAME || '.' ||
3358 lv_api_name || ' ' || substr(sqlerrm, 1, 1900);
3359 END PROCESS_TCS_TAXES;
3360 END JAI_AR_TRX_HEADERS_PKG;