1 PACKAGE BODY jai_ar_match_tax_pkg
2 /* $Header: jai_ar_match_tax.plb 120.71.12020000.5 2013/03/18 07:53:38 qimeng ship $ */
3 AS
4 --+=======================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| jai_ar_match_tax.plb |
11 --| |
12 --| DESCRIPTION |
13 --| This package is mainly used for posting the |
14 --| taxes, VAT/excise invoice num to base AR table. |
15 --| |
16 --| TDD REFERENCE |
17 --| The procedure "display_vat_invoice_no" is referenced by |
18 --| the "VAT Invoice Number on AR Invoice Technical Design.doc" |
19 --| |
20 --| |
21 --| PURPOSE |
22 --| PROCEDURE process_batch |
23 --| PROCEDURE process_from_order_line |
24 --| PROCEDURE process_manual_invoice |
25 --| PROCEDURE acct_inclu_taxes |
26 --| PROCEDURE display_vat_invoice_no is used for updating the |
27 --| reference field in AR transaction workbench to show the |
28 --| VAT/Excise invoice numbers |
29 --| |
30 --| HISTORY |
31 --| Bug 5243532. Added by Lakshmi Gopalsami |
32 --| (1) --|oved the reference to fnd_profile.value('ORG_ID'); |
33 --| (2) --|oved the cursor ORG_CUR_UPD as it is same as ORG_CUR |
34 --| (3) --|oved the reference to hr_operating_units and |
35 --| Implemented using caching logic. |
36 --| |
37 --| Bug 5490479. Added by Harshita |
38 --| In the concurrent program, the multi org category has been |
39 --| set to 'S'. To accomodate the same, derived the org_id from |
40 --| the function mo_global.get_current_org_id and populated |
41 --| an internal variable.Used this variable in all places |
42 --| instead of p_org_id. |
43 --| |
44 --| Bug 6201263 Added by Sacsethi |
45 --| Problem - R12RUP03-ST2: IL TAXES NOT POPULATED IN PAYMENT S |
46 --| CHEDULE OF CM GENERATED FOR RMA |
47 --| Solution - 11i forward porting was missing |
48 --| |
49 --| 1. Code changes - |
50 --| 1.1 Following code added - |
51 --| 1.1.1 Private procedure added - |
52 --| maintain_mrc |
53 --| maintain_applications |
54 --| maintain_schedules |
55 --| insert_trx_line_gl_dist |
56 --| insert_trx_lines |
57 --| delete_trx_data |
58 --| 1.1.2 Procedure process_from_order_line is changed |
59 --| as compare to 11i code |
60 --| |
61 --| BUG 6012570 BRATHOD, File Version 120.9 |
62 --| Re-implemented project changes by --|oving comments |
63 --| and also added p_debug parameter in process_batch |
64 --| procedure |
65 --| |
66 --| 23-Jan-2008 rchandan for bug#6766561 - file version 120.16 |
67 --| delete from jai_ar_trx_lines_ins_t was missing for |
68 --| autoinvoiced records. This has been added. |
69 --| This change is made to merge the fix of version |
70 --| 120.14 made for bug#6691354 |
71 --| |
72 --| Bug 6784276 Changes performed by nprashar . The date columns |
73 --| actual_closed_date, gl_date_closed of table |
74 --| ar_payment_schedules_all, are set to a default |
75 --| value TO_DATE('31/12/4712','DD/MM/RRRR'). |
76 --| |
77 --| 19-Jan-2010 Bo Li modified for VAT/Excise Number shown |
78 --| in AR transaction workbench and Bug 9303168# can |
79 --| be tracked |
80 --| |
81 --| 09-Mar-2010 Bo Li modified the display_invoice_no to solve the |
82 --| bug which the length of reference is over 150 has |
83 --| not been truncated and Bug 9453040# can |
84 --| be tracked |
85 --| |
86 --| 10-Apr-2010 Allen Yang modified for bug 9485355 |
87 --| (12.1.3 non-shippable Enhancement) |
88 --| modified cursor c_ex_inv_no to include nonshippable|
89 --| items. |
90 --| |
91 --| 05-May-2010 Bo Li Modified for displaying VAT/EXCISE invoice |
92 --| on AR transaction when the reference already |
93 --| contains the VAT/EXCISE invoice number characters |
94 --| |
95 --| 04-Jun-2010 Allen Yang for bug #9709906 |
96 --| Issue: TST1213.XB1.QA.ORIGINAL TAX AMOUNT SHOULD BE|
97 --| BALANCED TO A CORRECT VALUE BY RMA. |
98 --| Fix: In procedure 'process_from_order_line', |
99 --| added IF condition to avoid using table |
100 --| lt_receipt_id_tab when lt_receipt_id_tab is |
101 --| NULL. |
102 --| |
103 --| 04-JUN-2010 Bo Li Modified for Bug#9771955 |
104 --| Issue - Accounting for inclusive tax is not |
105 --| correct when the order has been imported |
106 --| into AR transactions |
107 --| Fix - Change the accounting for inclusive tax |
108 --| Modified the procedure acct_inclu_taxes |
109 --| and function get_tax_account_id to get the |
110 --| correct accounting and insert into |
111 --| GL_INTERFACE table |
112 --| |
113 --| 02-JUL-2010 Bo Li Modified for Bug#9803555 |
114 --| Issue - Issue with "AR Tax and Freight |
115 --| Defaultation (India - AR Tax and Freight |
116 --| Defaultation)" |
117 --| If the concurrent program has been submited|
118 --| twice one time, the tax detail will not be |
119 --| inserted into distribution table |
120 --| Fix - Add the conditon for commit the trascation |
121 --| |
122 --| 09-JUL-2010 Bo Li Modified for Bug#9765108 |
123 --| Issue - The account code is not right for the |
124 --| non-shippable item RMA flow in the |
125 --| gl_interface table when the RMA order |
126 --| lines contain the inclusive tax |
127 --| Fix - Add the logic for handling with the inclusive|
128 --| tax for the non-shippable item RMA flow |
129 --| |
130 --| 01-Sep-2010 Bo Li for bug#10043656 |
131 --| GST Enhancement for R12.1.3 |
132 --| |
133 --| 19-Oct-2010 Bug 9892611 |
134 --| Description: Dummy Lines are inserted into GL |
135 --| Interface(Revenue) when Inclusive taxes are not |
136 --| present and taxes are imported to AR from OM |
137 --| Fix: Insert into Revenue Account only if Inclusive |
138 --| tax is non zero |
139 --| |
140 --| 21-FEB-2010 Bug 11735678 |
141 --| Description: ORA-06502: PL/SQL: numeric or |
142 --| value error occurs when importing taxes from |
143 --| Projects Invoices to AR |
144 --| Fix: Code for importing VAT Invoice into AR |
145 --| from OM matched Invoices fires for Projects |
146 --| imported invoices also. |
147 --| The values stored in interface_line_attribute6 |
148 --| and interface_line_attribute3 varies on the |
149 --| interface header context |
150 --| As VAT Invoice import in AR is not supported |
151 --| for Projects it is skipped |
152 --| |
153 --| 12-Apr-2011 Bug 11936630 |
154 --| Description: Inclusive Taxes are not accounted |
155 --| in GL for Order Imported AR Invoice if the Order |
156 --| has only Inclusive Taxes |
157 --| Fix: Data was inserted into JAI_AR_TRX_INS_LINES_T |
158 --| only if the AR Invoice had an Exclusive Tax. |
159 --| Hence accounting was not happening. |
160 --| Data is now inserted into JAI_AR_TRX_INS_LINES_T |
161 --| via JAI_JAR_TL_TRIGGER_PKG even if AR Invoice has |
162 --| only Inclusive Tax. As Inclusive Tax should not |
163 --| be part of AR Distributions the cursor in |
164 --| process_from_order_line (cur_temp_lines_insert) |
165 --| was altered to pick only exclusive lines |
166 --| 29-Sep-2011 Bug 13023443 By zhiwei |
167 --| Description: for inclusive tax accounting |
168 --| Do Inclusive Tax accounting: |
169 --| 1) Normal AR transaction |
170 --| 2) Standard Event AR Transaction |
171 --| 3) External Event & Service & GL < POT |
172 --| |
173 --+======================================================================*/
174
175 gv_projects_invoices constant varchar2(30) := 'PROJECTS INVOICES'; /* bug#6012570 (5876390) */
176 GV_MODULE_PREFIX CONSTANT VARCHAR2(30) := 'jai_ar_match_tax_pkg'; -- -- Added by Jia Li on tax inclusive computation on 2007/11/30
177
178
179 PROCEDURE process_batch (
180 ERRBUF OUT NOCOPY VARCHAR2,
181 RETCODE OUT NOCOPY VARCHAR2,
182 P_ORG_ID IN NUMBER,
183 p_all_orgs IN Varchar2
184 , p_debug in varchar2 default 'N'
185 , p_called_from IN VARCHAR2 default null /*parameter added for bug#6012570 (5876390)commented by kunkumar for bugno6066813 */
186 -- revoked the comments for 6012570
187 )
188 IS
189 lv_error_mesg VARCHAR2(255);
190 var_cust_trx_id NUMBER;
191 var_prev_cust_trx_id NUMBER(15);
192 var_rowid ROWID;
193 var_tax_amount NUMBER :=0;
194 var_freight_amount NUMBER :=0;
195 var_error_invoice CHAR(1);
196 error_from_called_unit EXCEPTION;
197 var_error_mesg VARCHAR2(1996);
198 v_org_id NUMBER; -- added by sriram - Bug # 2779967
199 lv_source JAI_AR_TRX_INS_LINES_T.source%TYPE ; --rchandan for bug#4428980
200
201 ln_org_id number ; -- Harshita for Bug 5490479
202 lv_debug varchar2(1) ;
203 lv_process_status VARCHAR2(2);
204 lv_process_message VARCHAR2(2000);
205 /*
206 commented by kunkumar for bug#6066813
207 Start, bug#6012570 (5876390)
208 */ -- Ended comments to redo the Project changes, 6012570
209 --Added by JMEENA for bug#8232976
210 cursor c_get_context(cp_customer_trx_id in number) is
211 select interface_header_context
212 from ra_customer_trx_all
213 where customer_trx_id = cp_customer_trx_id;
214 --End bug#8232976
215 lv_invoice_context ra_customer_trx_all.interface_header_context%type;
216 lv_projects_flag varchar2(1);
217 lv_called_from varchar2(30);
218
219 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30
220 ---------------------------------------------------------------
221 lv_inclu_tax_flag jai_ap_tds_years.inclusive_tax_flag%TYPE;
222 ln_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
223
224 CURSOR cur_separate_flag(pn_org_id IN NUMBER) IS
225 SELECT
226 nvl(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
227 FROM
228 jai_ap_tds_years ja
229 WHERE ja.legal_entity_id = pn_org_id
230 AND sysdate between ja.start_date and ja.end_date;
231
232 CURSOR cur_cust_trx_type(pn_customer_trx_id IN NUMBER) IS
233 SELECT
234 cust_trx_type_id
235 FROM
236 ra_customer_trx_all
237 WHERE customer_trx_id = pn_customer_trx_id;
238 ---------------------------------------------------------------
239
240 /* Start, bug#6012570 (5876390)
241 end commented by kunkumar */
242
243 --Added by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168,Begin
244 -----------------------------------------------------------------------------------------------
245 CURSOR c_delivery(pn_customer_trx_id IN NUMBER, pn_org_id IN NUMBER)
246 IS
247 SELECT rctl.interface_line_attribute3, rctl.interface_line_attribute6
248 FROM ra_customer_trx_all trx,
249 ra_customer_trx_lines_all rctl,
250 jai_ar_trx_lines jrctl,
251 JAI_AR_TRXS jrct
252 WHERE trx.customer_trx_id = rctl.customer_trx_id
253 AND jrct.customer_trx_id = trx.customer_trx_id
254 AND rctl.line_type = 'LINE'
255 AND trx.customer_trx_id =
256 nvl(pn_customer_trx_id, trx.customer_trx_id)
257 AND trx.org_id = nvl(pn_org_id, trx.org_id)
258 AND trx.created_from = 'RAXTRX'
259 AND rctl.customer_trx_line_id = jrctl.customer_trx_line_id;
260
261
262 CURSOR c_ex_inv_no(p_delivery_id VARCHAR2, p_order_line_id VARCHAR2)
263 IS
264 SELECT
265 excise_invoice_no
266 , vat_invoice_no
267 FROM
268 JAI_OM_WSH_LINES_ALL
269 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
270 -- WHERE delivery_id = p_delivery_id
271 WHERE (--delivery_id IS NULL OR /*commented condition by mmurtuza for bug 16040831*/
272 delivery_id = p_delivery_id)
273 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
274 AND ( order_line_id = p_order_line_id
275 OR order_line_id IN
276 ( SELECT
277 line_id
278 FROM
279 oe_order_lines_all
280 WHERE header_id IN
281 ( SELECT
282 header_id
283 FROM
284 oe_order_lines_all
285 WHERE line_id = p_order_line_id
286 )
287 AND item_type_code = 'CONFIG'
288 )
289 )
290 AND (excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL);
291
292 /*Added cursor by mmurtuza for bug 16040831*/
293 CURSOR c_ex_inv_no_2(p_order_line_id VARCHAR2)
294 IS
295 SELECT
296 excise_invoice_no
297 , vat_invoice_no
298 FROM
299 JAI_OM_WSH_LINES_ALL
300 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
301 -- WHERE delivery_id = p_delivery_id
302 WHERE (delivery_id IS NULL)
303 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
304 AND ( order_line_id = p_order_line_id
305 OR order_line_id IN
306 ( SELECT
307 line_id
308 FROM
309 oe_order_lines_all
310 WHERE header_id IN
311 ( SELECT
312 header_id
313 FROM
314 oe_order_lines_all
315 WHERE line_id = p_order_line_id
316 )
317 AND item_type_code = 'CONFIG'
318 )
319 )
320 AND (excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL);
321
322 ln_delivery_id NUMBER;
323 ln_order_line_id NUMBER;
324 lv_vat_invoice_no JAI_OM_WSH_LINES_ALL.Vat_Invoice_No%TYPE;
325 lv_excise_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
326 lv_display_flag VARCHAR2(1);
327 ------------------------------------------------------------------------------------------------------------------
328 --Added by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168 ,End
329
330 CURSOR c_st_invoice_cur(pn_customer_trx_id jai_ar_trxs.CUSTOMER_TRX_ID%TYPE)
331 IS
332 SELECT
333 st_inv_number
334 FROM JAI_AR_TRXS
335 WHERE customer_trx_id = pn_customer_trx_id;
336
337 CURSOR cur_st_taxes_exist(pn_customer_trx_id jai_ar_trxs.CUSTOMER_TRX_ID%TYPE,pn_org_id NUMBER)
338 IS
339 SELECT
340 regime_id ,
341 regime_code
342 FROM
343 JAI_AR_TRX_TAX_LINES jcttl,
344 JAI_AR_TRX_LINES jctl,
345 JAI_CMN_TAXES_ALL jtc ,
346 jai_regime_tax_types_v jrttv
347 WHERE
348 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
349 jctl.customer_trx_id = pn_customer_trx_id AND
350 jcttl.tax_id = jtc.tax_id AND
351 jtc.tax_type = jrttv.tax_type AND
352 regime_code = jai_constants.service_regime AND
353 jtc.org_id = pn_org_id ;
354
355 CURSOR cur_jai_trx_header(pn_customer_trx_id jai_ar_trxs.CUSTOMER_TRX_ID%TYPE)
356 IS
357 SELECT
358 jat.organization_id,
359 jat.LOCATION_ID,
360 jat.BATCH_SOURCE_ID
361 FROM jai_ar_trxs jat
362 where jat.CUSTOMER_TRX_ID = pn_customer_trx_id;
363 CURSOR cur_ar_trx_header(pn_customer_trx_id jai_ar_trxs.CUSTOMER_TRX_ID%TYPE)
364 IS
365 SELECT
366 rcta.SHIP_TO_CUSTOMER_ID,
367 rcta.SHIP_TO_SITE_USE_ID,
368 rcta.bill_to_customer_id,
369 rcta.bill_to_site_use_id,
370 rcta.TRX_DATE
371 FROM ra_customer_trx_all rcta
372 WHERE rcta.CUSTOMER_TRX_ID = pn_customer_trx_id;
373
374 CURSOR get_excise_inv_no_cur(pn_customer_trx_id NUMBER) IS
375 SELECT l.excise_invoice_no
376 FROM JAI_AR_TRX_LINES L
377 WHERE l.customer_trx_id = pn_customer_trx_id;
378
379 CURSOR get_vat_inv_no_cur(pn_customer_trx_id NUMBER) IS
380 SELECT h.vat_invoice_no
381 FROM JAI_AR_TRXS h
382 WHERE h.customer_trx_id = pn_customer_trx_id;
383
384 lv_st_inv_number jai_ar_trxs.st_inv_number%TYPE;
385 ln_regime_id jai_rgm_definitions.REGIME_ID%TYPE;
386 lv_regime_code jai_rgm_definitions.REGIME_CODE%TYPE;
387 ln_organization_id NUMBER;
388 ln_location_id NUMBER;
389 ln_batch_source_id NUMBER;
390 ln_ship_to_customer_id NUMBER;
391 ln_ship_to_site_use_id NUMBER;
392 ln_bill_to_customer_id NUMBER;
393 ln_bill_to_site_use_id NUMBER;
394 ld_trx_date DATE;
395 lv_doc_type_class VARCHAR2(2);
396 -----------------------------------------------------------------------------------
397 --Added by qinglei.meng 20/10/2011 ER: advance receipt for service tax
398 BEGIN
399
400
401 /* ------------------------------------------------------------------------------------------------------------------------
402 CHANGE HISTORY:
403 S.No DATE Author AND Details
404 ---------------------------------------------------------------------------------------------------------------------------
405 1 04-MAY-2002 Sriram. Procedure Created . This Procedure will be
406 invoked by the concurrent
407 'India Local Concurrent Procedure for processing Order Lines to AR' - JAINMREQ .
408 This will be only applicable for Invoiced Created from Order Entry.
409 2 09-MAY-2002 Sriram. Adding fnd_file.put_line -
410 to write logs from concurrent program.
411 3 24-May-2002 Sriram. Set the Code so that it works in the batch mode .
412 4. 09-JAN-2003 Sriram - Bug # 2740546 - File Version is 615.1
413 Added the substr function to the update statement that updates the
414 JAI_AR_TRX_INS_LINES_T table . If due to some reason the error message is
415 very long ,then it can be a potential problem.Because of this the program
416 should not halt.
417 5. 08/04/2003 Sriram - Bug # 2779967
418 Added logic to see that only records that belong to the current operating unit need
419 to be picked up for processing.This was done because records are inserted into the
420 JAI_AR_TRX_INS_LINES_T table from various 'India Local Receivables' responsibility
421 attached to various org ids , The concurrent program is not scheduled , but run by
422 the user , it picks up the records not only for the current org id but also for other
423 org ids as well which causes the problem.
424
425 6. 22/08/2003 Sriram - Bug # 3068927.
426 Added a new parameter P_ORG_ID to the Procedure. This has been done a new parameter
427 has been added in the concurrent program definition "JAINMREQ" to enable conflict domains.
428 The Concurrent program 'India Local Concurrent For Processing Order Lines to AR" has
429 been set incompatible to itself and also to autoinvoice import program . Because of the
430 previous bugfix , the concurrent has to be scheduled for each org id , hence causing performance
431 bottleneck because until one concurrent program runs , all others have to wait in pending state.
432 Hence , by using the conflict domains concept with the domain as org id , we are ensuring that
433 the concurrent are incompatible to itself only to the extent of those running in the same org id
434
435
436 7. 30/10/2003 Added another parameters P_all_orgs . This parameter is used for indicating whether to process for all
437 org ids or for the org id entered.
438 P_Org_id parameter is set as an optional parameter
439
440 8. 09/03/2004 ssumaith - bug# 3491600 file version 618.1
441
442 incorrect exception handling was done. variable width was smaller than the actual
443 width of the string assigned to the variable. This was causing the exception
444 'numeric or value error.'
445
446 9. 2004/08/11 Aiyer for bug#3826140. Version#115.1
447 Issue:-
448 Lines marked as deleted get reprocessed when a record is submitted for reprocessing from the the India Resubmit Errored OM
449 Tax Records form.
450
451 Reason:-
452 The current procedure previously used to also consider those records which have been marked as deleted.
453
454 Fix:-
455 The cursor temp_rec has been modified to discard all those lines which have been marked as 'R' or 'D'.
456
457 Dependency Due to this Bug:-
458 None
459
460 10. 2004/10/21 Aiyer for bug#3839560. Version#115.2
461 Issue:-
462 India Local Concurrent to Process Order Lines To AR corrrupts data in Base AR tables when two instances of this program
463 are run simultaneously with Process of Orgs = 'Y'
464
465 Reason:-
466 This is because the procedure ja_in_ra_order_lines_insert does not implement locking of records while processing in batch mode with Process of Orgs = 'Y'
467
468 Fix:-
469 This fix has been done in the procedure ja_ar_rec_process_validate.val_revrec_records called from procedure ja_in_ra_order_lines_insert.
470
471 Dependency Due to this Bug:-
472 This version of the file is dependent on the file jai_ar_match_tax_pkg.process_from_order_line version (115.1) due to the additions of a new parameter p_org_id.
473 It is also dependent on ja_in_ar_rec_prc_val_b.pls (115.0),ja_in_ar_rec_prc_val_s.pls (115.0) as jai_ar_match_tax_pkg.process_from_order_line version (115.1) calls
474 ja_ar_rec_process_validate.val_revrec_records(115.0).
475
476 11. 08-Jun-2005 Version 116.2 jai_ar_match_tax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
477 as required for CASE COMPLAINCE.
478
479 12 14-Jun-2005 rchandan for bug#4428980, Version 116.3
480 Modified the object to --|ove literals from DML statements and CURSORS.
481
482 13 23-Jun-2005 Ramananda for bug#4468353 ,version 116.4
483 Issue:
484 Impact on IL due to SLA Uptake by AR
485 Reason:
486 India Localization taxes and charges are inserted into RA_CUSTOIMER_TRX_LINES_ALL and
487 RA_CUST_LINES_GL_DIST_ALL, as Tax and Freight lines.Since India Localization directly updates
488 the above-mentioned tables, the accounting happens through the base AR accounting itself.
489 In R12, since the AR accounting will be handled through SLA IL tax lines that are inserted
490 in the RA_CUSTOIMER_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL will be impacted
491 Fix:
492 IL should ensure the following while inserting into RA_CUST_LINES_GL_DIST_ALL table:
493 1. The tax and freight lines that are inserted should be inserted before the associated base item lines
494 are posted to GL. This should be achieved by checking the Posting_Status by IL.
495
496 A new cursor is created to check the gl_posted_date for the base item. If the gl_posted_date is null,
497 then it inserts the tax and freight lines
498
499 2. Each of the Tax and freight lines should carry the same Accounting event information as the base
500 line. Event_Id field should be punched with the value as on the Item line. This value can be derived
501 from the call to 'Event Engine' for each line. IL will call the Event Engine API, and derive the
502 Event_Id for the base item line. This Event_Id will be punched to all the tax and freight lines related
503 to the base item line
504
505 A call is made to ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event) to update the event_id field
506
507 Issue:
508 Impact on IL due to ebTax Uptake by AR
509 Reason:
510 India Localization tax lines are inserted into AR transaction tables with an AR Tax code (Vat_Tax_Id).
511 In R12, the AR tax engine will be replace by ebTax. Due to this, all the tax code related setups will
512 be made in ebTax and not in AR. Since India Localization uses the Vat_Tax_Id for populating the
513 tax lines into the AR transaction tables and it will not uptake ebTax, it would be mandatory for
514 IL to have setups under the ebTax that can be used in the above transactions.
515
516 Fix:
517 Query logic is changed. Instead of querying vat_Tax_id from ar_vat_tax_all , tax_rate_id of zx_rates_b
518 is queried
519
520 14 25-Apr-2007 cbabu for Bug#6012570 (5876390), File Version 120.5 (115.5)
521 FP: Project billing implementation.
522 New concurrent JAINIPTR created for Project taxes to flow into AR and related
523 changes are made in process_batch
524
525 Excise invoice will not be updated in the Referece_field for Project Invoices as it is
526 giving error in the Invoices Form when Queried for Project Invoice
527
528 15. 17-09-2007 sacsethi for Bug#6407648 , File Version 120.3.12000000.3/ 120.11
529
530 Problem - R.TST1203.XB2.QA:INCORRECT IL TAXES ON RMA CM
531 Reason - Variable ln_created_by ,ld_creation_date initialization was missing .
532 Solution - Procedure maintain_applications is modified with initialization.
533
534 16. 18-sep-2007 anujsax for Bug#5636544, File Version 120.11
535 forward porting R11 bug 5629319 into R12 bug 5636544
536
537 17. 26-jan-2008 ssumaith - bug#6776085
538 following changes are done.
539 a. --|oved the code changes done for bug#5636544
540 b.did the code changes into the mainline for bug#6764386
541
542 18. 28-Jan-2009 CSahoo for bug#7645588, File Version 120.19.12010000.2
543 Issue: TAX ENTRIES ARE NOT VISIBLE IN DISTRIBUTIONS
544 Fix: Modified the code in the process_from_order_line. added the cursor cur_event_id
545 to get the event id. This cursor would get called only in case of a credit memo having
546 accounting rules defined. This would provide the event id of the REC account class.
547 The tax entries also need to be latched to this event id. so passed this event id to the
548 procedure insert_trx_line_gl_dist to get stamped in the table ra_cust_trx_line_gl_dist_all
549 table.
550 19 06-FEB-2009 JMEENA for bug#8232976
551 Created cursor c_get_context and to get the interface_header_context of the invoice and checked if it is PROJECT INVOICE.
552
553 20. 27-Feb-2009 CSahoo for bug#8276902, File Version 120.19.12010000.4
554 Issue: UNABLE TO ACCOUNT CREDIT MEMOS IN AR JAN-09
555 Fix: Added the following OR condition in the procedure process_from_order_line
556 "OR rec_customer_trx_lines.interface_line_context = gv_projects_invoices"
557
558 21. 02-Nov-2009 CSahoo for bug#8325824, File Version 120.19.12010000.11
559 Issue: REW:ROOTCAUSE: UNABLE TO POST AR TRANSACTIONS UPTO GL.
560 Fix: added the code to populate the AR distribution table. added the procedure
561 insert_ar_dist_entries.
562
563 21 09-dec-2009 vkaranam for bug#9177024,file version 120.19.12010000.12
564 Issue:Taxes doesn't become zero in Base AR Transaction screen even after deleting
565 the taxes in Localized AR Screen
566 Fix:
567 Added the call to delete_trx_data in process_manual_invoice procedure for
568 manual transactions.
569 22 23-MAR-2009 vkaranam for bug#9230409
570 Issue:
571 AR INVOICE IS SHOWING WRONG BALANCE WHENEVER A RMA CREDIT MEMO IS APPLIED.
572 Reason:
573 The problem is that when the credit memo
574 is created, it automatically unapplies the Receipt and applies the Credit Memo and
575 then re-applies the receipt for the remaining amount. This happens
576 fine for the base amount. But the same is not happening for the tax amount.
577
578 Hence the incorrect invoice balance issue.
579
580 The procedure jai_ar_match_tax_pkg.process_from_order_line
581 is responsible for populating the IL taxes in base AR table. Here we do not
582 check if a cash receipt is already applied to the invoice. The code just
583 directly applies the tax amount of the credit memo to the invoice. it does
584 not unapply the tax amount of the cash receipt applied before. Thats the
585 reason why this issue is coming.
586
587 fix:
588 changes are done in process_from_order_line procedure.
589 used base AR APIs to unapply the receipt and then appying the remaining.
590
591 23 19-jan-2011 vkaranam for bug#11652823
592 Issue: INCORRECT ACCOUNTING FOR INCLUSIVE SERVICE TAX
593 The interim liability account picked is at the regime registration level instead
594 of the organization level.
595
596 Fix:
597 changes are done in acct_inclu_taxes procedure.
598 added the code to fetch the accounts at the regime organization level.
599 24. 24-May-2011 Xiao for POT change, reg bug#12533434.
600 Fixed: Fetch gl_date from AR REV lines as accounting date. --Added by Chong.Lei for POT code port
601
602 25. 16-Feb-2012 mmurtuza for bug 13557242
603 Description: A/R - RMA AUTOINVOICE CRTD WITH ALLOW OVER APPLICATION, TAXES NOT INBASE TRN, HO
604 Fix: Added cursor c_period_dtl in procedure process_from_order_line to check period status. Added code to fetch start date of next first
605 open perioed if the gl_date of receipt application is closed. Passed this date while unapplyinh and reapplying the receipt.
606
607 26. 13-APr-2012 mmurtuza for bug 13958098
608 Description: INDIA LOCAL CONCURRENT IS ERRORING OUT WITH ERROR after application of patch 13557242
609 Fix: The assignment to variable lt_sob_id_tab was missing. Assgined the value to this variable
610
611 27. 26-Dec-2012 mmurtuza for bug 16040831
612 Description: INDIA - LOCAL CONCURRENT FOR PROCESSING ORDER LINES TO AR RUNNING MORE THAN NORM
613 Fix: Defined cursor c_ex_inv_no_2. Modified cursor c_ex_inv_no. cursor c_ex_inv_no is opened when p_delivery_id is not null and
614 cursor c_ex_inv_no_2 is opened when p_delivery_id is null
615
616 28. 17-Jan-2013 mmurtuza for bug 16181417
617 Description: AR CREATE ACCOUNTING PROGRAM ENDING IN ERROR IN JAI_AR_TRX_INS_LINES_T
618 Fix: Added condition adjustment_period_flag = 'N' for fetching next open period if receipt application period is closed.
619 Also added the logic done for bug 14194526 to avoid the charcter to number conversion and also to avoid performance issue
620 in cursors get_rma_flag_cur and inclu_rma_tax.
621
622
623 Future Dependencies For the release Of this Object:-
624 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
625 A datamodel change )
626
627 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
628 Current Version Current Bug Dependent Files Version Author Date --|arks
629 Of File On Bug/Patchset Dependent On
630 jai_ar_match_tax_pkg.process_batch
631 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
632 115.2 3839560 IN60105D2 jai_ar_match_tax_pkg.process_from_order_line 115.1 Aiyer 21/10/2004 New parameter p_org_id added
633 ja_in_ar_rec_prc_val_s.pls 115.0 Aiyer 21/10/2004 jai_ar_match_tax_pkg.process_from_order_line calls
634 ja_in_ar_rec_prc_val_b.pls 115.0 Aiyer 21/10/2004
635 ja_ar_rec_process_validate.val_revrec_records
636 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
637 /* Bug 5243532. Added by Lakshmi Gopalsami
638 --|oved the reference to fnd_profile.value('ORG_ID')
639 v_org_id := FND_PROFILE.VALUE('ORG_ID');
640 */
641 lv_debug := nvl(p_debug,'N');
642 lv_debug := 'Y';
643 fnd_file.put_line(FND_FILE.LOG,' Entering Procedure - jai_ar_match_tax_pkg.process_batch');
644
645 ln_org_id := mo_global.get_current_org_id() ; -- Harshita for Bug 5490479
646 /*commented by kunkumar for bug# 6066813 Start
647 6012570 (5876390) -- Revoked the comments for 6012570*/
648 if p_called_from is null then
649 lv_called_from := 'ORDER_ENTRY';
650 else
651 lv_called_from := p_called_from;
652 end if;
653 -- End commented by kunkumar for 6066813 */ revoked the comments for 6012570
654 if p_all_orgs = 'Y' or p_all_orgs = 'y' then
655 v_org_id := NULL;
656 else
657 v_org_id := ln_org_id; -- p_org_id -- Harshita for Bug 5490479
658 end if;
659
660 fnd_file.put_line(FND_FILE.LOG,' Org id retreived is - ' || v_org_id || ' Generate for All orgs is : ' || p_all_orgs);
661
662 lv_source := 'RAXTRX';
663 FOR temp_rec IN
664 (
665 SELECT DISTINCT customer_trx_id, org_id
666 FROM JAI_AR_TRX_INS_LINES_T
667 WHERE source = lv_source
668 AND org_id = nvl(ln_org_id, org_id)
669 MINUS
670 SELECT customer_trx_id, org_id
671 FROM JAI_AR_TRX_INS_LINES_T temp_dtl
672 WHERE source = 'RAXTRX'
673 AND org_id = nvl(ln_org_id, org_id)
674 AND error_flag IN ('R','D')
675 )
676 LOOP
677 /*Start commented by kunkumar for bug#6066813
678 -- Start, bug#6012570 (5876390)
679 */ -- Revoked comments for projects 6012570
680 lv_projects_flag := null;
681 lv_invoice_context := null;
682 --Added by JMEENA for bug#8232976
683 open c_get_context(temp_rec.customer_trx_id);
684 fetch c_get_context into lv_invoice_context;
685 close c_get_context;
686 --End bug#8232976
687 lv_projects_flag := is_this_projects_context(lv_invoice_context);
688 if lv_called_from = gv_projects_invoices
689 and lv_projects_flag = jai_constants.no
690 then
691 -- no need to process this customer trx
692 goto continue_with_next;
693
694 elsif lv_called_from <> gv_projects_invoices
695 and lv_projects_flag = jai_constants.yes
696 then
697 -- no need to process this customer trx
698 goto continue_with_next;
699 end if;
700 -- End, bug#6012570 (5876390)
701 -- End commented by kunkumar for bug 6066813*/ -- revoked the comments, 6012570
702 BEGIN
703
704 var_cust_trx_id := temp_rec.customer_trx_id;
705
706 jai_ar_match_tax_pkg.process_from_order_line(
707 temp_rec.customer_trx_id,
708 lv_debug ,
709 lv_process_status ,
710 lv_process_message
711 );
712
713 errbuf := lv_process_message ;
714
715 /*
716 Get the Status of the retcode flag - if it is not 2 it means success
717 else , it means error . On an Error Condition , rollback the transaction -
718 set the error_flag in the table for the
719 CUSTOMER_TRX_ID / LINK_TO_CUST_TRX_LINE_ID
720 COMBINATION to 'R' and err_mesg to
721 to the ERRBUF returned from the procedure.
722 */
723
724 -- The Following lines are for testing exception conditions.
725 -- Forcing an exception to occur and test the behaviour of the program
726
727 /*
728 IF temp_rec.link_to_cust_trx_line_id = 56673 THEN
729 RAISE NO_DATA_FOUND;
730 END IF;
731 */
732
733
734 IF lv_process_message IS NOT NULL THEN
735
736 /*
737 Error has Occured in the jai_ar_match_tax_pkg.process_from_order_line procedure .
738 Rollback all inserts , updates , deletes which have happened in
739 the procedure and update the temp_lines_insert procedure
740 setting the error flag to 'R' and err_mesg to ERRBUF
741 */
742
743 var_error_mesg := 'Error from called unit jai_ar_match_tax_pkg.process_from_order_line';
744 RAISE error_from_called_unit;
745
746 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30, Begin
747 -- TD17-Changed Account Inclusive taxes in AR separately
748 -----------------------------------------------------------------------
749 ELSE
750 -- Check if inclusive taxes needs to be accounted separately
751 /*Bug 11895705 - Derive Inclusive Tax Flag from temp_rec.org_id instead of v_org_id as
752 v_org_id is not populated in case All Org's parameter is Y and hence no accounting happens*/
753 OPEN cur_separate_flag(temp_rec.org_id);
754 FETCH cur_separate_flag INTO lv_inclu_tax_flag;
755 CLOSE cur_separate_flag;
756
757 OPEN cur_cust_trx_type(temp_rec.customer_trx_id);
758 FETCH cur_cust_trx_type INTO ln_cust_trx_type_id;
759 CLOSE cur_cust_trx_type;
760
761 IF lv_inclu_tax_flag = 'Y'
762 THEN
763 acct_inclu_taxes( pn_customer_trx_id => temp_rec.customer_trx_id
764 , pn_org_id => temp_rec.org_id
765 , pn_cust_trx_type_id => ln_cust_trx_type_id
766 , xv_process_flag => lv_process_status
767 , xv_process_message => lv_process_message);
768 END IF;
769
770 IF lv_process_status <> jai_constants.successful
771 THEN
772 RAISE error_from_called_unit;
773 END IF; -- lv_process_status <> 'SS'
774 -----------------------------------------------------------------------
775 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30, End
776 /* ssumaith bug# 6685976(6766561) */
777 delete from jai_ar_trx_ins_lines_t
778 WHERE customer_trx_id = temp_rec.customer_trx_id;
779
780 END IF;
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 IF var_error_mesg IS NULL THEN
785 -- the exception condition is not because of returned error from inner procedure
786 errbuf := substr(SQLERRM,1,200);
787 var_error_mesg := errbuf || 'Error in loop (not in jai_ar_match_tax_pkg.process_from_order_line procedure) ';
788 END IF;
789
790 ROLLBACK;
791
792 UPDATE JAI_AR_TRX_INS_LINES_T
793 SET ERROR_FLAG = 'R' ,
794 ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
795 WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
796
797
798 COMMIT;
799
800 fnd_file.put_line(FND_FILE.LOG , 'Error - ' || ' When Processing '||
801 temp_rec.customer_trx_id );
802 fnd_file.put_line(FND_FILE.LOG , 'Error is ' || var_error_mesg );
803
804 var_error_invoice := 'Y';
805
806 END;
807
808 --Added by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168,Begin
809 ---------------------------------------------------------------------------------------------------------
810 BEGIN
811
812 /*Bug 11735678 - Populate VAT Invoice Number into Base AR Reference column only if it is imported from ORDER ENTRY
813 Projects Imported AR Invoices is not within the scope of VAT Invoice Number on AR Invoice ER*/
814 fnd_file.put_line(FND_FILE.LOG , 'lv_called_from ' || lv_called_from);
815 fnd_file.put_line(FND_FILE.LOG , 'lv_projects_flag ' || lv_projects_flag);
816 /*IF (lv_called_from <> gv_projects_invoices and lv_projects_flag = jai_constants.no)
817 THEN*/
818 --Commented by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
819 --For Porjects Invoices also need to generate service invoice and display on AR Workbench
820
821 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
822 OPEN cur_st_taxes_exist(temp_rec.customer_trx_id,temp_rec.org_id);
823 FETCH cur_st_taxes_exist INTO ln_regime_id,lv_regime_code;
824 CLOSE cur_st_taxes_exist;
825 IF UPPER(NVL(lv_regime_code,'####')) = jai_constants.service_regime THEN
826 OPEN c_st_invoice_cur(temp_rec.customer_trx_id);
827 FETCH c_st_invoice_cur INTO lv_st_inv_number;
828 CLOSE c_st_invoice_cur;
829
830 IF lv_st_inv_number IS NULL THEN
831 OPEN cur_jai_trx_header(temp_rec.customer_trx_id);
832 FETCH cur_jai_trx_header INTO ln_organization_id,ln_location_id,ln_batch_source_id;
833 CLOSE cur_jai_trx_header;
834
835 OPEN cur_ar_trx_header(temp_rec.customer_trx_id);
836 FETCH cur_ar_trx_header
837 INTO ln_ship_to_customer_id,ln_ship_to_site_use_id,ln_bill_to_customer_id,
838 ln_bill_to_site_use_id,ld_trx_date;
839 CLOSE cur_ar_trx_header;
840
841 lv_doc_type_class := 'I';
842 fnd_file.put_line(FND_FILE.LOG,
843 'Start Generate Service Tax Invoice Number');
844 jai_cmn_rgm_setup_pkg.gen_invoice_number(
845 p_regime_id => ln_regime_id ,
846 p_organization_id => ln_organization_id ,
847 p_location_id => ln_location_id ,
848 p_date => ld_trx_date ,
849 p_doc_class => lv_doc_type_class , --added for bug#7475924
850 p_doc_type_id => ln_batch_source_id ,
851 p_invoice_number => lv_st_inv_number ,
852 p_process_flag => lv_process_status ,
853 p_process_msg => lv_process_message
854 );
855 IF lv_process_status = jai_constants.successful THEN
856 IF lv_st_inv_number IS NOT NULL THEN
857 fnd_file.put_line(FND_FILE.LOG,
858 'Service Tax Invoice Number has been successfully generated.lv_st_inv_number '||lv_st_inv_number);
859 update jai_ar_trxs set st_inv_number = lv_st_inv_number
860 WHERE customer_trx_id = temp_rec.customer_trx_id;
861 ELSE
862 var_error_invoice := 'Y';
863 END IF;
864 ELSE
865 var_error_invoice := 'Y';
866 errbuf := substr(errbuf ||lv_process_message,1,1500);
867 raise error_from_called_unit;
868 END IF;
869
870 END IF;
871
872 END IF;
873 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
874
875 fnd_file.put_line(FND_FILE.LOG,
876 'Display the VAT/Excise number in AR Transaction workbench');
877
878 --when there is no error happening in the above process
879 IF nvl(var_error_invoice, 'N') <> 'Y'
880 THEN
881
882 -- Initial the invoice number
883 lv_excise_invoice_no := NULL;
884 lv_vat_invoice_no := NULL;
885
886 IF (lv_called_from <> gv_projects_invoices and lv_projects_flag = jai_constants.no) THEN
887 OPEN c_delivery(temp_rec.customer_trx_id, v_org_id);
888 FETCH c_delivery
889 INTO ln_delivery_id, ln_order_line_id;
890 CLOSE c_delivery;
891
892 if (ln_delivery_id is not null) then -- Added if by mmurtuza for bug 16040831
893
894 OPEN
895 c_ex_inv_no(ln_delivery_id
896 , ln_order_line_id);
897 FETCH
898 c_ex_inv_no
899 INTO
900 lv_excise_invoice_no
901 , lv_vat_invoice_no;
902 CLOSE c_ex_inv_No;
903
904 /*start additions by mmurtuza for bug 16040831*/
905 else
906 OPEN
907 c_ex_inv_no_2(ln_order_line_id);
908 FETCH
909 c_ex_inv_no_2
910 INTO
911 lv_excise_invoice_no
912 , lv_vat_invoice_no;
913 CLOSE c_ex_inv_No_2;
914 end if;
915
916 /*end additions by mmurtuza for bug 16040831*/
917
918 ELSE
919 OPEN get_excise_inv_no_cur(temp_rec.customer_trx_id);
920 FETCH get_excise_inv_no_cur
921 INTO lv_excise_invoice_no;
922 CLOSE get_excise_inv_no_cur;
923
924 OPEN get_vat_inv_no_cur(temp_rec.customer_trx_id);
925 FETCH get_vat_inv_no_cur
926 INTO lv_vat_invoice_no;
927 CLOSE get_vat_inv_no_cur;
928 END IF;
929
930 lv_display_flag := FND_PROFILE.VALUE('JAI_DISP_VAT_EXC_INV_AR_TRX_REF');
931
932 FND_FILE.put_line(FND_FILE.LOG,
933 'JAI:Include Excise and VAT Invoice Number in AR transactions - Referencde is set to ' ||
934 lv_display_flag);
935
936 -- when then profile "JAI:Include Excise and VAT Invoice Number
937 -- in AR transactions - Referencde" set to "Yes"
938
939 FND_FILE.put_line( FND_FILE.LOG
940 ,'temp_rec.customer_trx_id :' || temp_rec.customer_trx_id);
941 FND_FILE.put_line( FND_FILE.LOG
942 , 'lv_excise_invoice_no :' ||lv_excise_invoice_no);
943 FND_FILE.put_line( FND_FILE.LOG
944 , 'lv_vat_invoice_no :' || lv_vat_invoice_no);
945
946 -- when the two inovice number has not been generated
947 -- and the profile has been set as "Yes", the default profile value is "No"
948 IF (lv_excise_invoice_no IS NOT NULL OR
949 lv_vat_invoice_no IS NOT NULL OR
950 lv_st_inv_number IS NOT NULL) AND
951 --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
952 nvl(lv_display_flag, 'N') = 'Y'
953 THEN
954 display_vat_invoice_no( pn_customer_trx_id => temp_rec.customer_trx_id
955 , pv_excise_invoice_no => lv_excise_invoice_no
956 , pv_vat_invoice_no => lv_vat_invoice_no);
957
958 fnd_file.put_line(FND_FILE.LOG,'The invoice number has been displayed successfully!');
959 END IF; --lv_excise_invoice_no IS NOT NULL
960
961 END IF;--nvl(var_error_invoice, 'N') <> 'Y'
962
963 --END IF; /*IF (lv_called_from <> gv_projects_invoices and lv_projects_flag = jai_constants.no)*/
964 --Commented by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
965 --For Porjects Invoices also need to generate service invoice and display on AR Workbench
966
967 END;
968 -----------------------------------------------------------------------------------------------------
969 --Added by Bo Li for VAT/Excise Number shown in AR transaction workbench on 19-Jan-2010 and In Bug 9303168,End
970
971 <<continue_with_next>>
972 NULL;
973
974 END LOOP;
975
976 -- write here to log the successful processing for last invoice
977
978 IF var_error_invoice <> 'Y' THEN
979 fnd_file.put_line(FND_FILE.LOG, 'Processed Customer_trx_id - ' ||var_cust_trx_id);
980 END IF;
981
982 COMMIT;
983 fnd_file.put_line(FND_FILE.LOG,'Successfully Exiting PROCEDURE - jai_ar_match_tax_pkg.process_batch');
984 EXCEPTION
985 WHEN OTHERS THEN
986 ROLLBACK;
987
988 UPDATE JAI_AR_TRX_INS_LINES_T
989 SET ERROR_FLAG = 'R' ,
990 ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
991 WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
992
993 COMMIT;
994
995 var_tax_amount :=0;
996 var_freight_amount :=0;
997
998 ERRBUF := SQLERRM;
999 RETCODE := 2;
1000 Fnd_file.put_line(FND_FILE.LOG,'EXCEPTION Occured - ' || ERRBUF || ' WHILE Processing Customer_trx_id - ' || var_cust_trx_id );
1001 END process_batch;
1002
1003 ------------------------------------------------ ---------------------------------------
1004 --=========================================================================================--
1005 --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all,
1006 --ar_receivable_applications_all
1007 --=========================================================================================--
1008
1009 PROCEDURE maintain_mrc( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1010 p_previous_cust_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE DEFAULT NULL,
1011 p_called_from IN VARCHAR2,
1012 p_process_status OUT NOCOPY VARCHAR2,
1013 p_process_message OUT NOCOPY VARCHAR2)
1014 IS
1015 lv_imported_trx VARCHAR2(10) := 'IMPORTED';
1016 ln_gl_dist_id ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE;
1017 lv_account_class_rec VARCHAR2(10) := 'REC';
1018
1019
1020 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1021 cp_procedure_name user_procedures.procedure_name%type) IS
1022 SELECT 1
1023 FROM user_procedures
1024 WHERE object_name = cp_object_name
1025 AND procedure_name = cp_procedure_name ;
1026
1027 CURSOR cur_payment_schedule_mrc(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1028 IS
1029 SELECT payment_schedule_id
1030 FROM ar_payment_schedules_all
1031 WHERE customer_trx_id = cp_customer_trx_id;
1032
1033 --get the cust_trx_line_gl_dist_id for the REC row from ra_cust_trx_line_gl_dist_all
1034 CURSOR cur_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1035 IS
1036 SELECT cust_trx_line_gl_dist_id
1037 FROM ra_cust_trx_line_gl_dist_all
1038 WHERE customer_trx_id = cp_customer_trx_id
1039 AND account_class = lv_account_class_rec --'REC'
1040 AND latest_rec_flag = jai_constants.yes; --'Y';
1041
1042 /* Ramananda for bug#5219225. */
1043 lv_object_name user_procedures.object_name%type ;
1044 lv_procedure_name user_procedures.procedure_name%type ;
1045 ln_exists NUMBER := 0 ;
1046 lv_sqlstmt VARCHAR2(2000) ;
1047
1048 BEGIN
1049 p_process_status := jai_constants.successful;
1050 p_process_message := NULL;
1051
1052 --get the cust_trx_line_gl_dist_id for the REC row from ra_cust_trx_line_gl_dist_all
1053 open cur_gl_dist(p_customer_trx_id);
1054 fetch cur_gl_dist into ln_gl_dist_id;
1055 close cur_gl_dist;
1056
1057 /* Ramananda for bug#5219225. START. Modified the following if..endif. and the call to be dynamic using execute immediate */
1058 lv_object_name := 'AR_MRC_ENGINE' ;
1059 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
1060
1061 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
1062 FETCH c_proc_exists INTO ln_exists ;
1063 CLOSE c_proc_exists ;
1064 IF ln_exists = 1 THEN
1065 --Update the mrc data for ra_cust_trx_line_gl_dist_all
1066 --This is done, irrespective of whether the transaction_type is CM or Invoice
1067 /* Commented for bug# 5219225
1068 ar_mrc_engine.maintain_mrc_data(
1069 p_event_mode => 'UPDATE',
1070 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
1071 p_mode => 'SINGLE',
1072 p_key_value => ln_gl_dist_id); */
1073
1074 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1075 p_event_mode => ''UPDATE'',
1076 p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
1077 p_mode => ''SINGLE'',
1078 p_key_value => :1
1079 );
1080 END; ';
1081 EXECUTE IMMEDIATE lv_sqlstmt USING ln_gl_dist_id ;
1082
1083 --if the program is called from process_imported_invoice
1084 IF p_called_from = lv_imported_trx THEN
1085 FOR rec_mrc IN cur_payment_schedule_mrc(p_customer_trx_id)
1086 LOOP
1087 /* Commented for bug# 5219225
1088 ar_mrc_engine.maintain_mrc_data(
1089 p_event_mode => 'UPDATE',
1090 p_table_name => 'AR_PAYMENT_SCHEDULES',
1091 p_mode => 'SINGLE',
1092 p_key_value => rec_mrc.payment_schedule_id); */
1093
1094 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1095 p_event_mode => ''UPDATE'',
1096 p_table_name => ''AR_PAYMENT_SCHEDULES'',
1097 p_mode => ''SINGLE'',
1098 p_key_value => :1
1099 );
1100 END; ';
1101 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.payment_schedule_id ;
1102 END LOOP;
1103 END IF;
1104
1105 --If the current transaction is a CM
1106 if p_previous_cust_trx_id IS NOT NULL THEN
1107
1108 FOR rec_mrc IN cur_payment_schedule_mrc(p_previous_cust_trx_id)
1109 LOOP
1110
1111 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1112 p_event_mode => ''UPDATE'',
1113 p_table_name => ''AR_PAYMENT_SCHEDULES'',
1114 p_mode => ''SINGLE'',
1115 p_key_value => :1
1116 );
1117 END; ';
1118 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.payment_schedule_id ;
1119 END LOOP;
1120
1121 for rec_ar_appl in
1122 ( select receivable_application_id
1123 from ar_receivable_applications_all
1124 where customer_trx_id = p_customer_trx_id
1125 )
1126 LOOP
1127
1128 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1129 p_event_mode => ''UPDATE'',
1130 p_table_name => ''AR_RECEIVABLE_APPLICATIONS'',
1131 p_mode => ''SINGLE'',
1132 p_key_value => :1
1133 );
1134 END;' ;
1135 EXECUTE IMMEDIATE lv_sqlstmt USING rec_ar_appl.receivable_application_id ;
1136 END LOOP;
1137 END IF;
1138 END IF ;
1139 /* Ramananda for bug#5219225. END */
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 p_process_status := jai_constants.unexpected_error;
1144 p_process_message := SUBSTR(SQLERRM,1,300);
1145 END maintain_mrc;
1146 --=========================================================================================--
1147 --This procedure maintains the history of ar_receivable_applications_all in jai_ar_rec_appl_audits
1148 --=========================================================================================--
1149
1150 PROCEDURE maintain_applications(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1151 p_receivable_application_id IN jai_ar_rec_appl_audits.receivable_application_id%TYPE,
1152 p_concurrent_req_num IN NUMBER,
1153 p_request_id IN NUMBER,
1154 p_operation_type IN VARCHAR2,
1155 p_rec_appl_audit_id IN OUT NOCOPY NUMBER,
1156 p_process_status OUT NOCOPY VARCHAR2,
1157 p_process_message OUT NOCOPY VARCHAR2)
1158 IS
1159 CURSOR cur_rec_appl_audits_s
1160 IS
1161 SELECT jai_ar_rec_appl_audits_s.nextval
1162 FROM dual;
1163
1164 ln_created_by jai_ar_payment_audits.created_by%TYPE;
1165 ld_creation_date jai_ar_payment_audits.creation_date%TYPE;
1166 ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
1167 ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
1168 ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
1169
1170 BEGIN
1171 p_process_status := jai_constants.successful;
1172 p_process_message := NULL;
1173
1174 --set the values for WHO columns
1175 ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
1176 ld_last_update_date := SYSDATE;
1177 ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
1178
1179 --In case of operation_type = 'UPDATE', the parameter p_payment_audit_id shall have a value
1180 --In case of 'INSERT', the value for parameter p_payment_audit_id shall be null
1181 IF p_rec_appl_audit_id IS NULL THEN
1182 OPEN cur_rec_appl_audits_s;
1183 FETCH cur_rec_appl_audits_s INTO p_rec_appl_audit_id;
1184 CLOSE cur_rec_appl_audits_s;
1185 END IF;
1186
1187 IF p_operation_type = 'INSERT' THEN
1188
1189 -- Date 17/09/2007 by sacsethi for bug 6407648
1190 ln_created_by := ln_last_updated_by;
1191 ld_creation_date := ld_last_update_date;
1192
1193 INSERT INTO jai_ar_rec_appl_audits(
1194 rec_appl_audit_id,
1195 concurrent_req_num,
1196 customer_trx_id,
1197 receivable_application_id,
1198 aapp_old,
1199 acctd_aapp_from_old,
1200 acctd_aapp_to_old,
1201 tapp_old,
1202 fapp_old,
1203 created_by,
1204 creation_date,
1205 last_updated_by,
1206 last_update_date,
1207 last_update_login
1208 )
1209 SELECT p_rec_appl_audit_id,
1210 p_concurrent_req_num,
1211 p_customer_trx_id,
1212 p_receivable_application_id,
1213 amount_applied,
1214 acctd_amount_applied_from,
1215 acctd_amount_applied_to,
1216 tax_applied,
1217 freight_applied,
1218 ln_created_by,
1219 ld_creation_date,
1220 ln_last_updated_by,
1221 ld_last_update_date,
1222 ln_last_update_login
1223 FROM ar_receivable_applications_all
1224 WHERE customer_trx_id = p_customer_trx_id
1225 AND receivable_application_id = p_receivable_application_id;
1226
1227 ELSIF p_operation_type = 'UPDATE' THEN
1228 UPDATE jai_ar_rec_appl_audits a
1229 SET (aapp_new,
1230 acctd_aapp_applied_from_new,
1231 acctd_aapp_applied_to_new,
1232 tapp_new,
1233 fapplied_new,
1234 last_updated_by,
1235 last_update_date,
1236 last_update_login) =
1237 (SELECT amount_applied,
1238 acctd_amount_applied_from,
1239 acctd_amount_applied_to,
1240 tax_applied,
1241 freight_applied,
1242 ln_last_updated_by,
1243 ld_last_update_date,
1244 ln_last_update_login
1245 FROM ar_receivable_applications_all b
1246 WHERE customer_trx_id = a.customer_trx_id
1247 AND receivable_application_id = a.receivable_application_id)
1248 WHERE customer_trx_id = p_customer_trx_id
1249 AND receivable_application_id = p_receivable_application_id
1250 AND rec_appl_audit_id = p_rec_appl_audit_id;
1251
1252 END IF;
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 p_process_status := jai_constants.unexpected_error;
1256 p_process_message := SUBSTR(SQLERRM,1,300);
1257 END maintain_applications;
1258
1259 --=========================================================================================--
1260 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
1261 --=========================================================================================--
1262
1263 PROCEDURE maintain_schedules( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1264 p_payment_schedule_id IN ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL,
1265 p_cm_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE DEFAULT NULL,
1266 p_invoice_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1267 p_concurrent_req_num IN NUMBER,
1268 p_request_id IN NUMBER,
1269 p_operation_type IN VARCHAR2,
1270 p_payment_audit_id IN OUT NOCOPY jai_ar_payment_audits.payment_audit_id%TYPE,
1271 p_process_status OUT NOCOPY VARCHAR2,
1272 p_process_message OUT NOCOPY VARCHAR2)
1273 IS
1274
1275
1276
1277
1278 CURSOR cur_payment_audits_s
1279 IS
1280 SELECT jai_ar_payment_audits_s.nextval
1281 FROM dual;
1282
1283 ln_created_by jai_ar_payment_audits.created_by%TYPE;
1284 ld_creation_date jai_ar_payment_audits.creation_date%TYPE;
1285 ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
1286 ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
1287 ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
1288 BEGIN
1289 p_process_status := jai_constants.successful;
1290 p_process_message := NULL;
1291
1292 --set the values for WHO columns
1293 ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
1294 ld_last_update_date := SYSDATE;
1295 ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
1296
1297 --In case of operation_type = 'UPDATE', the parameter p_payment_audit_id shall have a value
1298 --In case of 'INSERT', the value for parameter p_payment_audit_id shall be null
1299 IF p_payment_audit_id IS NULL THEN
1300 OPEN cur_payment_audits_s;
1301 FETCH cur_payment_audits_s INTO p_payment_audit_id;
1302 CLOSE cur_payment_audits_s;
1303 END IF;
1304
1305 IF p_operation_type = 'INSERT' THEN
1306
1307 ln_created_by := ln_last_updated_by;
1308 ld_creation_date := ld_last_update_date;
1309
1310 INSERT INTO jai_ar_payment_audits(
1311 payment_audit_id,
1312 concurrent_req_num,
1313 payment_schedule_id,
1314 cm_customer_trx_id,
1315 invoice_customer_trx_id,
1316 original_customer_trx_id,
1317 ado_old,
1318 to_old,
1319 fo_old,
1320 aapp_old,
1321 adr_old,
1322 fr_old,
1323 tr_old,
1324 acctd_adr_old,
1325 acred_old,
1326 alio_old,
1327 status_old,
1328 gl_date_closed_old,
1329 actual_date_closed_old,
1330 created_by,
1331 creation_date,
1332 last_updated_by,
1333 last_update_date,
1334 last_update_login
1335 )
1336 SELECT p_payment_audit_id,
1337 p_concurrent_req_num,
1338 payment_schedule_id,
1339 p_cm_customer_trx_id,
1340 p_invoice_customer_trx_id,
1341 p_customer_trx_id,
1342 amount_due_original,
1343 tax_original,
1344 freight_original,
1345 amount_applied,
1346 amount_due_remaining,
1347 freight_remaining,
1348 tax_remaining,
1349 acctd_amount_due_remaining,
1350 amount_credited,
1351 amount_line_items_original,
1352 status,
1353 gl_date_closed,
1354 actual_date_closed,
1355 ln_created_by,
1356 ld_creation_date,
1357 ln_last_updated_by,
1358 ld_last_update_date,
1359 ln_last_update_login
1360 FROM ar_payment_schedules_all
1361 WHERE customer_trx_id = p_customer_trx_id
1362 AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id);
1363
1364 ELSIF p_operation_type = 'UPDATE' THEN
1365 UPDATE jai_ar_payment_audits a
1366 SET (ado_new,
1367 to_new,
1368 fo_new,
1369 aapp_new,
1370 adr_new,
1371 fr_new,
1372 tr_new,
1373 acctd_adr_new,
1374 acred_new,
1375 alio_new,
1376 status_new,
1377 gl_date_closed_new,
1378 actual_date_closed_new,
1379 last_updated_by,
1380 last_update_date,
1381 last_update_login) =
1382 (SELECT amount_due_original,
1383 tax_original,
1384 freight_original,
1385 amount_applied,
1386 amount_due_remaining,
1387 freight_remaining,
1388 tax_remaining,
1389 acctd_amount_due_remaining,
1390 amount_credited,
1391 amount_line_items_original,
1392 status,
1393 gl_date_closed,
1394 actual_date_closed,
1395 ln_last_updated_by,
1396 ld_last_update_date,
1397 ln_last_update_login
1398 FROM ar_payment_schedules_all b
1399 WHERE customer_trx_id = a.original_customer_trx_id
1400 AND payment_schedule_id = a.payment_schedule_id)
1401 WHERE original_customer_trx_id = p_customer_trx_id
1402 AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id)
1403 AND payment_audit_id = p_payment_audit_id;
1404
1405 END IF;
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 p_process_status := jai_constants.unexpected_error;
1409 p_process_message := SUBSTR(SQLERRM,1,300);
1410 END maintain_schedules;
1411
1412 PROCEDURE insert_trx_line_gl_dist(p_account_class IN ra_cust_trx_line_gl_dist_all.account_class%TYPE,
1413 p_account_set_flag IN ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE,
1414 p_acctd_amount IN ra_cust_trx_line_gl_dist_all.acctd_amount%TYPE,
1415 p_amount IN ra_cust_trx_line_gl_dist_all.amount%TYPE,
1416 p_code_combination_id IN ra_cust_trx_line_gl_dist_all.code_combination_id%TYPE,
1417 p_cust_trx_line_gl_dist_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE,
1418 p_cust_trx_line_salesrep_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_salesrep_id%TYPE,
1419 p_customer_trx_id IN ra_cust_trx_line_gl_dist_all.customer_trx_id%TYPE,
1420 p_customer_trx_line_id IN ra_cust_trx_line_gl_dist_all.customer_trx_line_id%TYPE,
1421 p_gl_date IN ra_cust_trx_line_gl_dist_all.gl_date%TYPE,
1422 p_last_update_date IN ra_cust_trx_line_gl_dist_all.last_update_date%TYPE,
1423 p_last_updated_by IN ra_cust_trx_line_gl_dist_all.last_updated_by%TYPE,
1424 p_creation_date IN ra_cust_trx_line_gl_dist_all.creation_date%TYPE,
1425 p_created_by IN ra_cust_trx_line_gl_dist_all.created_by%TYPE,
1426 p_last_update_login IN ra_cust_trx_line_gl_dist_all.last_update_login%TYPE,
1427 p_org_id IN ra_cust_trx_line_gl_dist_all.org_id%TYPE,
1428 p_percent IN ra_cust_trx_line_gl_dist_all.percent%TYPE,
1429 p_posting_control_id IN ra_cust_trx_line_gl_dist_all.posting_control_id%TYPE,
1430 p_set_of_books_id IN ra_cust_trx_line_gl_dist_all.set_of_books_id%TYPE,
1431 p_seq_id OUT NOCOPY NUMBER,
1432 p_process_status OUT NOCOPY VARCHAR2,
1433 p_process_message OUT NOCOPY VARCHAR2,
1434 p_event_id IN NUMBER DEFAULT NULL) --added for bug#7645588
1435 IS
1436 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1437 cp_procedure_name user_procedures.procedure_name%type) IS
1438 SELECT 1
1439 FROM user_procedures
1440 WHERE object_name = cp_object_name
1441 AND procedure_name = cp_procedure_name ;
1442
1443 CURSOR cur_gl_seq
1444 IS
1445 SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
1446 FROM dual;
1447
1448 /* Ramananda for bug#5219225. */
1449 lv_object_name user_procedures.object_name%type ;
1450 lv_procedure_name user_procedures.procedure_name%type ;
1451 ln_exists NUMBER := 0 ;
1452 lv_sqlstmt VARCHAR2(2000);
1453
1454 ln_cust_trx_line_gl_dist_id ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE;
1455 BEGIN
1456 p_process_status := jai_constants.successful;
1457 p_process_message := NULL;
1458
1459 --get the value for cust_trx_line_gl_dist_id
1460 OPEN cur_gl_seq;
1461 FETCH cur_gl_seq into ln_cust_trx_line_gl_dist_id;
1462 CLOSE cur_gl_seq;
1463
1464
1465
1466 INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
1467 account_set_flag,
1468 acctd_amount,
1469 amount,
1470 code_combination_id,
1471 cust_trx_line_gl_dist_id,
1472 cust_trx_line_salesrep_id,
1473 customer_trx_id,
1474 customer_trx_line_id,
1475 gl_date,
1476 last_update_date,
1477 last_updated_by,
1478 creation_date,
1479 created_by,
1480 last_update_login,
1481 org_id,
1482 percent,
1483 posting_control_id,
1484 set_of_books_id,
1485 event_id) --added for bug#7645588
1486 VALUES(p_account_class,
1487 p_account_set_flag,
1488 p_acctd_amount,
1489 p_amount,
1490 p_code_combination_id,
1491 ln_cust_trx_line_gl_dist_id,
1492 p_cust_trx_line_salesrep_id,
1493 p_customer_trx_id,
1494 p_customer_trx_line_id,
1495 p_gl_date,
1496 p_last_update_date,
1497 p_last_updated_by,
1498 p_creation_date,
1499 p_created_by,
1500 p_last_update_login,
1501 p_org_id,
1502 p_percent,
1503 p_posting_control_id,
1504 p_set_of_books_id,
1505 p_event_id); --added for bug#7645588
1506
1507 /* Ramananda for bug#5219225. START */
1508 lv_object_name := 'AR_MRC_ENGINE' ;
1509 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
1510
1511 p_seq_id := ln_cust_trx_line_gl_dist_id ;
1512 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
1513 FETCH c_proc_exists INTO ln_exists ;
1514 CLOSE c_proc_exists ;
1515 IF ln_exists = 1 THEN
1516
1517 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1518 p_event_mode => ''INSERT'',
1519 p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
1520 p_mode => ''SINGLE'',
1521 p_key_value => :1
1522 );
1523 END; ';
1524 EXECUTE IMMEDIATE lv_sqlstmt USING ln_cust_trx_line_gl_dist_id ;
1525 END IF ;
1526 /* Ramananda for bug#5219225. END */
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 p_process_status := jai_constants.unexpected_error;
1531 p_process_message := SUBSTR(SQLERRM,1,300);
1532 END insert_trx_line_gl_dist;
1533 PROCEDURE insert_trx_lines(p_extended_amount IN ra_customer_trx_lines_all.extended_amount%TYPE,
1534 p_taxable_amount IN ra_customer_trx_lines_all.taxable_amount%TYPE,
1535 p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE,
1536 p_last_update_date IN ra_customer_trx_lines_all.last_update_date%TYPE,
1537 p_last_updated_by IN ra_customer_trx_lines_all.last_updated_by%TYPE,
1538 p_creation_date IN ra_customer_trx_lines_all.creation_date%TYPE,
1539 p_created_by IN ra_customer_trx_lines_all.created_by%TYPE,
1540 p_last_update_login IN ra_customer_trx_lines_all.last_update_login%TYPE,
1541 p_customer_trx_id IN ra_customer_trx_lines_all.customer_trx_id%TYPE,
1542 p_line_number IN ra_customer_trx_lines_all.line_number%TYPE,
1543 p_set_of_books_id IN ra_customer_trx_lines_all.set_of_books_id%TYPE,
1544 p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE,
1545 p_line_type IN ra_customer_trx_lines_all.line_type%TYPE,
1546 p_org_id IN ra_customer_trx_lines_all.org_id%TYPE,
1547 p_uom_code IN ra_customer_trx_lines_all.uom_code%TYPE,
1548 p_autotax IN ra_customer_trx_lines_all.autotax%TYPE,
1549 p_vat_tax_id IN ra_customer_trx_lines_all.vat_tax_id%TYPE,
1550 p_interface_line_context IN ra_customer_trx_lines_all.interface_line_context%TYPE,
1551 p_interface_line_attribute6 IN ra_customer_trx_lines_all.interface_line_attribute6%TYPE,
1552 p_interface_line_attribute3 IN ra_customer_trx_lines_all.interface_line_attribute3%TYPE,
1553 p_process_status OUT NOCOPY VARCHAR2,
1554 p_process_message OUT NOCOPY VARCHAR2)
1555 IS
1556 BEGIN
1557 p_process_status := jai_constants.successful;
1558 p_process_message := NULL;
1559
1560 INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
1561 taxable_amount,
1562 customer_trx_line_id,
1563 last_update_date,
1564 last_updated_by,
1565 creation_date,
1566 created_by,
1567 last_update_login,
1568 customer_trx_id,
1569 line_number,
1570 set_of_books_id,
1571 link_to_cust_trx_line_id,
1572 line_type,
1573 org_id,
1574 uom_code,
1575 autotax,
1576 vat_tax_id,
1577 interface_line_context,
1578 interface_line_attribute6,
1579 interface_line_attribute3)
1580 VALUES ( p_extended_amount,
1581 p_taxable_amount,
1582 p_customer_trx_line_id,
1583 p_last_update_date,
1584 p_last_updated_by,
1585 p_creation_date,
1586 p_created_by,
1587 p_last_update_login,
1588 p_customer_trx_id,
1589 p_line_number,
1590 p_set_of_books_id,
1591 p_link_to_cust_trx_line_id,
1592 p_line_type,
1593 p_org_id,
1594 p_uom_code,
1595 p_autotax,
1596 p_vat_tax_id,
1597 p_interface_line_context,
1598 p_interface_line_attribute6,
1599 p_interface_line_attribute3);
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 p_process_status := jai_constants.unexpected_error;
1603 p_process_message := SUBSTR(SQLERRM,1,300);
1604 END insert_trx_lines;
1605
1606 /*added the following procedure for bug#8325824*/
1607 PROCEDURE insert_ar_dist_entries (p_customer_trx_id IN NUMBER,
1608 p_receivable_appl_id IN NUMBER,
1609 p_debug IN VARCHAR2 DEFAULT 'N',
1610 p_process_status OUT NOCOPY VARCHAR2,
1611 p_process_message OUT NOCOPY VARCHAR2)
1612 IS
1613 BEGIN
1614 IF p_debug = 'Y' THEN
1615 fnd_file.put_line(FND_FILE.LOG, 'Before deleting ar_distributions : p_receivable_appl_id '|| p_receivable_appl_id);
1616 END IF;
1617
1618 DELETE ar_distributions
1619 where source_id = p_receivable_appl_id
1620 and source_table = 'RA' ;
1621
1622 IF p_debug = 'Y' THEN
1623 fnd_file.put_line(FND_FILE.LOG, 'Before call to create_Acct_entry: p_customer_trx_id '|| p_customer_trx_id||
1624 ' p_receivable_appl_id '|| p_receivable_appl_id);
1625 END IF;
1626
1627 arp_acct_main.create_Acct_entry('CREDIT_MEMO',
1628 p_customer_trx_id,
1629 'ONE',
1630 'RA',
1631 p_receivable_appl_id,
1632 null,
1633 null,
1634 'Y',
1635 'C',
1636 'N',
1637 null);
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 p_process_status := jai_constants.unexpected_error;
1641 p_process_message := SUBSTR(SQLERRM,1,300);
1642 END insert_ar_dist_entries;
1643
1644
1645
1646 PROCEDURE delete_trx_data(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1647 p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE DEFAULT NULL,
1648 p_process_status OUT NOCOPY VARCHAR2,
1649 p_process_message OUT NOCOPY VARCHAR2)
1650 IS
1651 /* Ramananda for bug#5219225. */
1652 lv_object_name user_procedures.object_name%type ;
1653 lv_procedure_name user_procedures.procedure_name%type ;
1654 ln_exists NUMBER := 0 ;
1655 lv_sqlstmt VARCHAR2(2000) ;
1656 lv_account_class_tax VARCHAR2(10) := 'TAX';
1657 lv_account_class_freight VARCHAR2(10) := 'FREIGHT';
1658
1659 --get the sum of amount, acctd_amount and max of acctd_amount from ra_cust_trx_line_gl_dist_all for cp_customer_trx_id
1660 --and account_class in ('TAX','FREIGHT')
1661 CURSOR cur_total_amt_gl_dist( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1662 IS
1663 SELECT NVL(SUM(amount),0) amount,
1664 NVL(SUM(acctd_amount),0) acctd_amount,
1665 MAX(acctd_amount) max_acctd_amount
1666 FROM ra_cust_trx_line_gl_dist_all
1667 WHERE customer_trx_id = cp_customer_trx_id
1668 AND account_class IN (lv_account_class_tax,lv_account_class_freight);
1669
1670 --get the data from JAI_AR_TRX_INS_LINES_T for customer_trx_id and link_to_cust_trx_line_id
1671 CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1672 cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
1673 IS
1674 SELECT *
1675 FROM JAI_AR_TRX_INS_LINES_T
1676 WHERE customer_trx_id = cp_customer_trx_id
1677 AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1678 ORDER BY link_to_cust_trx_line_id,
1679 customer_trx_line_id;
1680
1681
1682 /* Ramananda for bug#5219225. */
1683 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1684 cp_procedure_name user_procedures.procedure_name%type) IS
1685 SELECT 1
1686 FROM user_procedures
1687 WHERE object_name = cp_object_name
1688 AND procedure_name = cp_procedure_name ;
1689
1690 BEGIN
1691 p_process_status := jai_constants.successful;
1692 p_process_message := NULL;
1693
1694 /* Ramananda for bug#5219225. START */
1695 lv_object_name := 'AR_MRC_ENGINE' ;
1696 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
1697
1698 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
1699 FETCH c_proc_exists INTO ln_exists ;
1700 CLOSE c_proc_exists ;
1701 IF ln_exists = 1 THEN
1702 --delete the mrc data from ra_cust_trx_line_gl_dist_all
1703 FOR rec_mrc IN
1704 ( SELECT cust_trx_line_gl_dist_id
1705 FROM ra_cust_trx_line_gl_dist_all
1706 WHERE customer_trx_id = p_customer_trx_id
1707 AND account_class IN ('TAX','FREIGHT')
1708 AND customer_trx_line_id IN
1709 (SELECT customer_trx_line_id
1710 FROM ra_customer_trx_lines_all
1711 WHERE customer_trx_id = p_customer_trx_id
1712 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1713 AND line_type in ('TAX','FREIGHT')
1714 )
1715 )
1716 LOOP
1717
1718 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1719 p_event_mode =>''DELETE'',
1720 p_table_name =>''RA_CUST_TRX_LINE_GL_DIST'',
1721 p_mode =>''SINGLE'',
1722 p_key_value => :1
1723 );
1724
1725 END; ' ;
1726 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.cust_trx_line_gl_dist_id ;
1727 END LOOP;
1728 END IF ;
1729 /* Ramananda for bug#5219225. END */
1730
1731 --delete the data from ra_cust_trx_line_gl_dist_all
1732 DELETE ra_cust_trx_line_gl_dist_all
1733 WHERE customer_trx_id = p_customer_trx_id
1734 AND account_class IN ('TAX','FREIGHT')
1735 AND customer_trx_line_id IN
1736 (SELECT customer_trx_line_id
1737 FROM ra_customer_trx_lines_all
1738 WHERE customer_trx_id = p_customer_trx_id
1739 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1740 AND line_type in ('TAX','FREIGHT')
1741 );
1742
1743 --delete the data from ra_customer_trx_lines_all
1744 DELETE ra_customer_trx_lines_all
1745 WHERE customer_trx_id = p_customer_trx_id
1746 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1747 AND line_type IN ('TAX','FREIGHT');
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 p_process_status := jai_constants.unexpected_error;
1752 p_process_message := SUBSTR(SQLERRM,1,300);
1753 END delete_trx_data;
1754
1755
1756 PROCEDURE process_from_order_line( p_customer_trx_id IN NUMBER,
1757 p_debug IN VARCHAR2 DEFAULT 'N',
1758 p_process_status OUT NOCOPY VARCHAR2,
1759 p_process_message OUT NOCOPY VARCHAR2)
1760 IS
1761
1762
1763 v_org_id number;
1764 lv_object_name user_procedures.object_name%type ;
1765 lv_procedure_name user_procedures.procedure_name%type ;
1766 ln_exists NUMBER := 0 ;
1767 lv_sqlstmt VARCHAR2(2000) ;
1768 lv_account_class_tax VARCHAR2(10) := 'TAX';
1769 lv_account_class_freight VARCHAR2(10) := 'FREIGHT';
1770 lv_loc_tax_code VARCHAR2(20) := 'Localization';
1771 lv_line_type_line VARCHAR2(10) := 'LINE';
1772 ld_gl_posted_date RA_CUST_TRX_LINE_GL_DIST_ALL.gl_posted_date%type ;
1773 lv_account_class_rec VARCHAR2(10) := 'REC';
1774 l_xla_event arp_xla_events.xla_events_type;
1775 ln_gl_seq Number;
1776 imported_trx VARCHAR2(10) := 'IMPORTED';
1777
1778 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
1779 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
1780 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
1781
1782 localization_tax_not_defined EXCEPTION;
1783 Item_lines_already_accounted EXCEPTION; /* Ramanand for SLA Uptake */
1784 rounding_account_not_defined EXCEPTION;
1785 resource_busy EXCEPTION;
1786
1787 --get the allow_overapplication_flag from ra_cust_trx_types_all for cust_trx_type_id
1788 CURSOR cur_trx_types( cp_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%TYPE)
1789 IS
1790 SELECT allow_overapplication_flag
1791 FROM ra_cust_trx_types_all
1792 WHERE cust_trx_type_id = cp_cust_trx_type_id;
1793
1794 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
1795 CURSOR cur_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1796 cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
1797 IS
1798 SELECT payment_schedule_id,
1799 term_id,
1800 terms_sequence_number,
1801 amount_line_items_original,
1802 amount_line_items_remaining,
1803 tax_original,
1804 tax_remaining,
1805 freight_original,
1806 amount_due_remaining
1807 FROM ar_payment_schedules_all
1808 WHERE customer_trx_id = cp_customer_trx_id
1809 AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
1810
1811 --get the sum of extended_amount, taxable_amount from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type
1812 CURSOR cur_total_amt_trx_lines( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1813 cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE DEFAULT NULL,
1814 cp_line_type ra_customer_trx_lines_all.line_type%TYPE)
1815 IS
1816 SELECT NVL(SUM(extended_amount),0) extended_amount,
1817 NVL(SUM(taxable_amount),0) taxable_amount
1818 FROM ra_customer_trx_lines_all
1819 WHERE customer_trx_id = cp_customer_trx_id
1820 AND customer_trx_line_id = NVL(cp_customer_trx_line_id, customer_trx_line_id)
1821 AND line_type = cp_line_type;
1822
1823
1824
1825 CURSOR c_gl_posted_date_cur(p_customer_trx_line_id RA_CUST_TRX_LINE_GL_DIST_ALL.customer_trx_line_id%type) IS
1826 SELECT gl_posted_date
1827 from RA_CUST_TRX_LINE_GL_DIST_ALL
1828 where customer_trx_line_id = p_customer_trx_line_id
1829 and account_class = 'REC'
1830 and latest_rec_flag = 'Y';
1831
1832 --get the accounting_rule_id from ra_customer_trx_lines_all for customer_trx_line_id
1833 CURSOR accounting_set_cur(cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
1834 IS
1835 SELECT accounting_rule_id
1836 FROM ra_customer_trx_lines_all
1837 WHERE customer_trx_line_id = cp_customer_trx_line_id;
1838
1839 /* Ramananda for bug#5219225. */
1840 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1841 cp_procedure_name user_procedures.procedure_name%type) IS
1842 SELECT 1
1843 FROM user_procedures
1844 WHERE object_name = cp_object_name
1845 AND procedure_name = cp_procedure_name ;
1846
1847 --get the sum of amount, acctd_amount and max of acctd_amount from ra_cust_trx_line_gl_dist_all for cp_customer_trx_id
1848 --and account_class in ('TAX','FREIGHT')
1849
1850 CURSOR cur_total_amt_gl_dist( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1851 IS
1852 SELECT NVL(SUM(amount),0) amount,
1853 NVL(SUM(acctd_amount),0) acctd_amount,
1854 MAX(acctd_amount) max_acctd_amount
1855 FROM ra_cust_trx_line_gl_dist_all
1856 WHERE customer_trx_id = cp_customer_trx_id
1857 AND account_class IN (lv_account_class_tax,lv_account_class_freight);
1858
1859 --get the data from JAI_AR_TRX_INS_LINES_T for customer_trx_id and link_to_cust_trx_line_id
1860 /*Bug 11936630 - Ensure only Exclusive Tax lines get interfaced to AR*/
1861 CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1862 cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
1863 IS
1864 SELECT *
1865 FROM JAI_AR_TRX_INS_LINES_T jatilt
1866 WHERE customer_trx_id = cp_customer_trx_id
1867 AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1868 AND EXISTS (select '1'
1869 from JAI_AR_TRX_TAX_LINES jattl
1870 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
1871 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
1872 and exists (select '1'
1873 from JAI_CMN_TAXES_ALL
1874 where jattl.tax_id = tax_id
1875 and NVL(inclusive_tax_flag,'N') = 'N'))
1876 ORDER BY link_to_cust_trx_line_id,
1877 customer_trx_line_id;
1878
1879 --get the data from ra_cust_trx_line_gl_dist_all for customer_trx_id and account_class = 'REC'
1880 --CURSOR cur_gl_date(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE) --Comment by Chong.Lei for POT code port
1881 -- Added by Chong.Lei for POT code port begin
1882 CURSOR cur_gl_date(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE
1883 , cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE)--Add by Xiao for POT change, reg bug#12533434
1884 -- Added by Chong.Lei for POT code port end
1885 IS
1886 SELECT gl_date
1887 FROM ra_cust_trx_line_gl_dist_all
1888 WHERE customer_trx_id = cp_customer_trx_id
1889 --Commented by Chong.Lei for POT code port begin
1890 /*
1891 AND account_class = 'REC'
1892 AND latest_rec_flag = 'Y';
1893 */
1894 --Commented by Chong.Lei for POT code port end
1895 -- Added by Chong.Lei for POT code port begin
1896 AND account_class = 'REV' --Modified by Xiao for POT change, reg bug#12533434
1897 AND customer_trx_line_id = cp_customer_trx_line_id; --Modified by Xiao for POT change, reg bug#12533434
1898 -- Added by Chong.Lei for POT code port end
1899
1900 --get the currency precision from fnd_currencies for the set_of_books_id
1901 CURSOR cur_curr_precision(cp_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE)
1902 IS
1903 SELECT NVL(minimum_accountable_unit,NVL(precision,2))
1904 FROM fnd_currencies
1905 WHERE currency_code IN
1906 (
1907 SELECT Currency_code
1908 FROM gl_sets_of_books
1909 WHERE set_of_books_id = cp_set_of_books_id
1910 );
1911
1912 --get the data from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type = 'LINE'
1913 CURSOR cur_customer_trx_lines(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1914 cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
1915 IS
1916 SELECT interface_line_attribute6,
1917 interface_line_attribute3,
1918 interface_line_context,
1919 NVL(extended_amount,0) extended_amount,
1920 NVL(taxable_amount,0) taxable_amount
1921 FROM ra_customer_trx_lines_all
1922 WHERE customer_trx_id = cp_customer_trx_id
1923 AND customer_trx_line_id = cp_customer_trx_line_id
1924 AND line_type = lv_line_type_line; --'LINE'
1925
1926
1927 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
1928 CURSOR cur_min_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1929 IS
1930 SELECT MIN(payment_schedule_id) payment_schedule_id,
1931 MIN(term_id) term_id
1932 FROM ar_payment_schedules_all
1933 WHERE customer_trx_id = cp_customer_trx_id;
1934
1935
1936 --get the data from ra_customer_trx_all for a customer_trx_id
1937 CURSOR cur_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1938 IS
1939 SELECT org_id,
1940 NVL(exchange_rate,1) exchange_rate,
1941 trx_number,
1942 cust_trx_type_id,
1943 created_from,
1944 set_of_books_id,
1945 previous_customer_trx_id
1946 FROM ra_customer_trx_all
1947 WHERE customer_trx_id = cp_customer_trx_id;
1948
1949
1950 --Lock all the rows from JAI_AR_TRX_INS_LINES_T for a customer_trx_id, which are to be processed
1951 CURSOR cur_lock_temp(cp_customer_trx_id JAI_AR_TRX_INS_LINES_T.customer_trx_id%TYPE)
1952 IS
1953 SELECT *
1954 FROM JAI_AR_TRX_INS_LINES_T
1955 WHERE customer_trx_id = cp_customer_trx_id
1956 FOR UPDATE NOWAIT;
1957
1958
1959 --Get the first_installment_code and base_amount from ra_terms
1960 CURSOR cur_term_details(cp_term_id ra_terms.term_id%TYPE)
1961 IS
1962 SELECT first_installment_code,
1963 DECODE(base_amount, 0, 1, base_amount) base_amount
1964 FROM ra_terms
1965 WHERE term_id = cp_term_id;
1966
1967
1968 --Get the relative_amount from ra_terms_lines
1969 CURSOR cur_term_lines(cp_term_id ra_terms_lines.term_id%TYPE,
1970 cp_sequence_num ra_terms_lines.sequence_num%TYPE)
1971 IS
1972 SELECT relative_amount
1973 FROM ra_terms_lines
1974 WHERE term_id = cp_term_id
1975 AND sequence_num = cp_sequence_num;
1976
1977
1978 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
1979 CURSOR cur_tot_amt_for_cms(cp_applied_customer_trx_id ar_receivable_applications_all.applied_customer_trx_id%TYPE,
1980 cp_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE)
1981 IS
1982 SELECT NVL(SUM(amount),0) amount
1983 FROM ra_cust_trx_line_gl_dist_all
1984 WHERE customer_trx_id IN
1985 (
1986 SELECT customer_trx_id
1987 FROM ar_receivable_applications_all
1988 WHERE applied_customer_trx_id = cp_applied_customer_trx_id
1989 AND application_type = 'CM'
1990 AND display = 'Y'
1991 AND status = 'APP'
1992 )
1993 AND account_class = cp_account_class;
1994
1995
1996 --Get the SUM of tax_applied and freight_applied from ar_receivable_applications_all for Cash receipts applied
1997 CURSOR cur_tot_cash_rcpt(cp_applied_customer_trx_id ar_receivable_applications_all.applied_customer_trx_id%TYPE)
1998 IS
1999 SELECT NVL(sum(tax_applied),0) tax_applied,
2000 NVL(sum(freight_applied),0) freight_applied
2001 FROM ar_receivable_applications_all
2002 WHERE applied_customer_trx_id = cp_applied_customer_trx_id
2003 AND application_type = 'CASH'
2004 AND display = 'Y'
2005 AND status = 'APP';
2006
2007
2008 --Get the SUM of line_applied from ar_receivable_applications_all for CM
2009 CURSOR cur_tot_recv_appl( cp_applied_customer_Trx_id ar_receivable_applications_all.applied_customer_Trx_id%TYPE,
2010 cp_applied_payment_Schedule_id ar_receivable_applications_all.applied_payment_Schedule_id%TYPE)
2011 IS
2012 SELECT NVL(sum(line_applied),0) line_applied
2013 FROM ar_receivable_applications_all
2014 WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
2015 AND application_type = 'CM'
2016 AND display = 'Y'
2017 and status = 'APP'
2018 AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
2019
2020
2021 --Get the receivable_application_id from ar_receivable_applications_all for the Invoice's payment_schedule_id
2022 CURSOR cur_recv_appl_id(cp_applied_customer_Trx_id ar_receivable_applications_all.applied_customer_Trx_id%TYPE,
2023 cp_customer_trx_id ar_receivable_applications_all.customer_trx_id%TYPE,
2024 cp_applied_payment_Schedule_id ar_receivable_applications_all.applied_payment_Schedule_id%TYPE)
2025 IS
2026 SELECT receivable_application_id
2027 FROM ar_receivable_applications_all
2028 WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
2029 AND customer_trx_id = cp_customer_trx_id
2030 AND application_type = 'CM'
2031 AND display = 'Y'
2032 and status = 'APP'
2033 AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
2034
2035 --Get the data from ar_payment_schedules_all for customer_trx_id
2036 CURSOR cur_prev_payment_schedule( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
2037 cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
2038 IS
2039 SELECT amount_line_items_original,
2040 amount_line_items_remaining,
2041 tax_original,
2042 tax_remaining,
2043 freight_original,
2044 amount_due_remaining
2045 FROM ar_payment_schedules_all
2046 WHERE customer_trx_id = cp_customer_trx_id
2047 AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
2048
2049 CURSOR ORG_CUR IS
2050 SELECT ORG_ID, CREATED_FROM
2051 FROM RA_CUSTOMER_TRX_ALL
2052 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
2053
2054 --added the cursor for bug#7645588
2055 CURSOR cur_event_id (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
2056 IS
2057 SELECT event_id
2058 FROM ra_cust_trx_line_gl_dist_all
2059 WHERE customer_trx_id = cp_customer_trx_id
2060 AND account_class = 'REC'
2061 AND latest_rec_flag = 'Y'
2062 AND account_set_flag = 'N' ;
2063
2064 --Start Addition by anujsax for Bug#5636544
2065 CURSOR cur_excise_invoice_number(cp_customer_trx_id NUMBER)
2066 IS
2067 SELECT excise_invoice_no
2068 FROM JAI_AR_TRX_LINES
2069 WHERE customer_trx_id = cp_customer_trx_id
2070 AND excise_invoice_no is NOT NULL;
2071
2072 r_excise_invoice_number cur_excise_invoice_number%ROWTYPE;
2073 lv_errbuf VARCHAR2(4000);
2074 lv_retcode VARCHAR2(10);
2075 --End of addition by Anujsax for Bug#5636544
2076
2077 --added for bug#9230409, start
2078 TYPE RECEIPT_ID_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2079 TYPE receipt_date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2080 lt_apply_date receipt_date_tab;
2081 lt_gl_date receipt_date_tab;
2082 lt_receipt_id_tab RECEIPT_ID_TAB;
2083 ln_row_count NUMBER;
2084 ln_msg_count NUMBER;
2085 lv_msg_data VARCHAR2(2000);
2086 lv_return_status VARCHAR2(1);
2087
2088 CURSOR cur_get_apply_date (ln_cash_receipt_id IN NUMBER,
2089 ln_customer_trx_id IN NUMBER)
2090 IS
2091 SELECT max(apply_date)
2092 FROM ar_receivable_applications_all
2093 WHERE cash_receipt_id = ln_cash_receipt_id
2094 AND applied_customer_trx_id = ln_customer_trx_id;
2095
2096 CURSOR cur_get_gl_date (ln_cash_receipt_id IN NUMBER,
2097 ln_customer_trx_id IN NUMBER,
2098 ld_apply_date IN DATE)
2099 IS
2100 SELECT gl_date
2101 FROM ar_receivable_applications_all
2102 WHERE cash_receipt_id = ln_cash_receipt_id
2103 AND applied_customer_trx_id = ln_customer_trx_id
2104 AND receivable_application_id = (Select max(receivable_application_id)
2105 from ar_receivable_applications_all
2106 where cash_receipt_id = ln_cash_receipt_id
2107 and applied_customer_trx_id = ln_customer_trx_id
2108 and apply_date = ld_apply_date);
2109 --bug#9230409, end
2110
2111 /*Start additions by mmurtuza for bug 13557242*/
2112 CURSOR c_period_dtl(cp_sob_id IN NUMBER, cp_accounting_date IN DATE) IS
2113 SELECT period_name,
2114 start_date,
2115 end_date,
2116 closing_status
2117 FROM gl_period_statuses
2118 WHERE application_id = jai_constants.gl_application_id
2119 AND set_of_books_id = cp_sob_id
2120 AND closing_status IN('O', 'F')
2121 AND cp_accounting_date BETWEEN start_date
2122 AND end_date
2123 ORDER BY period_year,
2124 period_num;
2125
2126 r_period_dtl c_period_dtl % rowtype;
2127
2128 TYPE SOB_ID_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2129 lt_sob_id_tab SOB_ID_TAB;
2130 ld_accounting_date receipt_date_tab;
2131
2132 /*End additions by mmurtuza for bug 13557242*/
2133
2134
2135
2136 rec_customer_trx cur_customer_trx%ROWTYPE;
2137 rec_inv_customer_trx cur_customer_trx%ROWTYPE;
2138 rec_min_payment_schedule cur_min_payment_schedule%ROWTYPE;
2139 rec_inv_payment_schedule cur_min_payment_schedule%ROWTYPE;
2140 rec_customer_trx_lines cur_customer_trx_lines%ROWTYPE;
2141 rec_term_details cur_term_details%ROWTYPE;
2142 rec_prev_payment_schedule cur_prev_payment_schedule%ROWTYPE;
2143
2144 ln_previous_customer_trx_id ra_customer_trx_lines_all.previous_customer_trx_id%TYPE;
2145 ln_inv_curr_conv_rate ra_customer_trx_all.exchange_rate%TYPE;
2146 ln_cm_curr_conv_rate ra_customer_trx_all.exchange_rate%TYPE;
2147 ld_gl_date ra_cust_trx_line_gl_dist_all.gl_date%TYPE;
2148 ln_vat_tax_id ar_vat_tax_all.vat_tax_id%TYPE;
2149 lv_amount_includes_tax_flag ar_vat_tax_all.amount_includes_tax_flag%TYPE;
2150 lv_account_Set_flag ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE;
2151 ln_precision fnd_currencies.precision%TYPE;
2152 ln_accounting_rule_id NUMBER;
2153 ln_old_amount NUMBER;
2154 ln_taxable_amount NUMBER;
2155 ln_tax_amt NUMBER;
2156 ln_tax_acctd_amount NUMBER;
2157 ln_max_tax_acctd_amount NUMBER;
2158 ln_old_acctd_amount NUMBER;
2159 ln_adjusted_tax NUMBER;
2160 ln_diff_tax_frt NUMBER;
2161 ln_total_tax_amt_for_inv NUMBER;
2162 ln_total_frt_amt_for_inv NUMBER;
2163 ln_inst_tax_amt_for_inv NUMBER;
2164 ln_inst_frt_amt_for_inv NUMBER;
2165 ln_relative_amt NUMBER;
2166 ln_recv_appln_id NUMBER;
2167 ln_tax_amt_cms NUMBER;
2168 ln_frt_amt_cms NUMBER;
2169 ln_apportion_factor NUMBER;
2170 lv_allow_overappln_flag VARCHAR2(10);
2171 ln_amt_due_rem NUMBER;
2172 ln_line_applied NUMBER;
2173 ln_frt_amt_cashrcpt NUMBER;
2174 ln_tax_amt_cashrcpt NUMBER;
2175 ln_payment_audit_id jai_ar_payment_audits.payment_audit_id%TYPE;
2176 ln_rec_appl_audit_id jai_ar_rec_appl_audits.rec_appl_audit_id%TYPE;
2177 lv_process_status VARCHAR2(2);
2178 lv_process_message VARCHAR2(1000);
2179 v_upd_created_from VARCHAR2(15);
2180 ln_event_id NUMBER; --added for bug#7645588
2181 BEGIN
2182
2183 fnd_file.put_line(FND_FILE.LOG, 'START process_imported_invoice');
2184
2185 --Lock all the rows from JAI_AR_TRX_INS_LINES_T for a customer_trx_id, which are to be processed
2186 FOR i IN cur_lock_temp(p_customer_trx_id)
2187 LOOP
2188 EXIT;
2189 END LOOP;
2190
2191 --get the data from ra_customer_trx_all for a customer_trx_id
2192 OPEN cur_customer_trx(p_customer_trx_id);
2193 FETCH cur_customer_trx INTO rec_customer_trx;
2194 CLOSE cur_customer_trx;
2195
2196 OPEN ORG_CUR;
2197 FETCH ORG_CUR INTO v_org_id, v_upd_created_from;
2198 CLOSE ORG_CUR;
2199
2200 --If ln_previous_customer_trx_id is not null, then current transaction is a CM
2201 ln_previous_customer_trx_id := rec_customer_trx.previous_customer_trx_id;
2202
2203 IF p_debug = 'Y' THEN
2204 fnd_file.put_line(FND_FILE.LOG, 'Before pre_validation');
2205 END IF;
2206
2207 --do the basic validations before processing the transaction
2208 jai_ar_validate_data_pkg.pre_validation
2209 ( p_customer_trx_id => p_customer_trx_id,
2210 p_process_status => lv_process_status,
2211 p_process_message => lv_process_message);
2212
2213 IF p_debug = 'Y' THEN
2214 fnd_file.put_line(FND_FILE.LOG, 'Before pre_validation');
2215 END IF;
2216
2217 IF lv_process_status <> jai_constants.successful THEN
2218 p_process_status := lv_process_status;
2219 p_process_message := lv_process_message;
2220 goto EXIT_POINT;
2221 END IF;
2222
2223 --If it is CM
2224 IF ln_previous_customer_trx_id IS NOT NULL THEN
2225 --get the data from ra_customer_trx_all for the Invoice
2226 OPEN cur_customer_trx(ln_previous_customer_trx_id);
2227 FETCH cur_customer_trx INTO rec_inv_customer_trx;
2228 CLOSE cur_customer_trx;
2229
2230 ln_inv_curr_conv_rate := rec_inv_customer_trx.exchange_rate;
2231 ln_cm_curr_conv_rate := rec_customer_trx.exchange_rate;
2232 ELSE
2233 ln_inv_curr_conv_rate := rec_customer_trx.exchange_rate;
2234 END IF;
2235
2236 --Added by Zhiwei for Bug#14040855 begin
2237 ----------------------------------------------------------------------
2238 jai_tax_processing_pkg.process_complete(p_customer_trx_id);
2239 ----------------------------------------------------------------------
2240 --Added by Zhiwei for Bug#14040855 end
2241
2242 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
2243 OPEN cur_min_payment_schedule(p_customer_trx_id);
2244 FETCH cur_min_payment_schedule INTO rec_min_payment_schedule;
2245 CLOSE cur_min_payment_schedule;
2246
2247
2248 --get the currency precision from fnd_currencies for the set_of_books_id
2249 OPEN cur_curr_precision(rec_customer_trx.set_of_books_id);
2250 FETCH cur_curr_precision INTO ln_precision;
2251 CLOSE cur_curr_precision;
2252
2253 IF p_debug = 'Y' THEN
2254 fnd_file.put_line(FND_FILE.LOG, 'Before delete_trx_data');
2255 END IF;
2256
2257 --This procedure deletes the data from ra_cust_trx_line_gl_dist_all, ra_customer_trx_lines_all
2258 --Also deletes the MRC data from ra_cust_trx_line_gl_dist
2259 delete_trx_data(p_customer_trx_id => p_customer_trx_id,
2260 p_process_status => lv_process_status,
2261 p_process_message => lv_process_message);
2262
2263 IF p_debug = 'Y' THEN
2264 fnd_file.put_line(FND_FILE.LOG, 'After delete_trx_data');
2265 END IF;
2266
2267 IF lv_process_status <> jai_constants.successful THEN
2268 p_process_status := lv_process_status;
2269 p_process_message := lv_process_message;
2270 goto EXIT_POINT;
2271 END IF;
2272
2273 --Commented by Chong.Lei for POT code port begin
2274 /*
2275 --get the data from ra_cust_trx_line_gl_dist_all for customer_trx_id and account_class = 'REC'
2276 OPEN cur_gl_date(p_customer_trx_id);
2277 FETCH cur_gl_date INTO ld_gl_date;
2278 CLOSE cur_gl_date;
2279 */
2280 --Commented by Chong.Lei for POT code port end
2281
2282
2283
2284 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
2285 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
2286 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
2287 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
2288
2289 OPEN jai_ar_trx_pkg.c_party_tax_profile_id_cur(V_ORG_ID);
2290 FETCH jai_ar_trx_pkg.c_party_tax_profile_id_cur INTO ln_party_tax_profile_id;
2291 CLOSE jai_ar_trx_pkg.c_party_tax_profile_id_cur ;
2292
2293 OPEN jai_ar_trx_pkg.c_tax_rate_id_cur(lv_tax_regime_code, ln_party_tax_profile_id);
2294 FETCH jai_ar_trx_pkg.c_tax_rate_id_cur INTO ln_tax_rate_id;
2295 CLOSE jai_ar_trx_pkg.c_tax_rate_id_cur ;
2296
2297 if ln_tax_rate_id is null then
2298 raise Localization_tax_not_defined;
2299 end if;
2300 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
2301
2302
2303 IF p_debug = 'Y' THEN
2304 fnd_file.put_line(FND_FILE.LOG, 'Before cur_temp_lines_insert LOOP');
2305 END IF;
2306
2307 FOR rec_temp IN cur_temp_lines_insert(p_customer_trx_id)
2308 LOOP
2309
2310 --get the accounting_rule_id from ra_customer_trx_lines_all for customer_trx_line_id
2311 OPEN accounting_set_cur(rec_temp.link_to_cust_trx_line_id);
2312 FETCH accounting_set_cur INTO ln_accounting_rule_id;
2313 CLOSE accounting_set_cur;
2314
2315 IF ln_accounting_rule_id IS NOT NULL THEN
2316 -- Added the IF condition for bug#7645588, start
2317 IF ln_previous_customer_trx_id IS NOT NULL THEN --it is a CM transaction
2318 lv_account_Set_flag := 'N';
2319 ELSE
2320 lv_account_Set_flag := 'Y';
2321 END IF;
2322 --bug#7645588, end
2323 ELSE
2324 lv_account_Set_flag := 'N';
2325 END IF;
2326
2327 --get the data from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type = 'LINE'
2328 OPEN cur_customer_trx_lines(p_customer_trx_id,
2329 rec_temp.link_to_cust_trx_line_id);
2330 FETCH cur_customer_trx_lines INTO rec_customer_trx_lines;
2331 CLOSE cur_customer_trx_lines;
2332
2333 -- Added by Chong.Lei for POT code port begin
2334 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, begin
2335 ------------------------------------------------------------------------------------
2336 --get the data from ra_cust_trx_line_gl_dist_all for customer_trx_id and account_class = 'REV'
2337 OPEN cur_gl_date(p_customer_trx_id, rec_temp.link_to_cust_trx_line_id);
2338 FETCH cur_gl_date INTO ld_gl_date;
2339 CLOSE cur_gl_date;
2340 ------------------------------------------------------------------------------------
2341 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
2342 -- Added by Chong.Lei for POT code port end
2343
2344 --added the following IF block for bug#7645588
2345 -- moved the following piece of code for bug#8276902
2346 -- added the OR condition for bug#8276902
2347 IF (ln_accounting_rule_id is not null OR rec_customer_trx_lines.interface_line_context = gv_projects_invoices)
2348 and ln_previous_customer_trx_id IS NOT NULL then
2349 open cur_event_id (p_customer_trx_id);
2350 Fetch cur_event_id into ln_event_id;
2351 close cur_event_id;
2352 ELSE
2353 ln_event_id := null;
2354 END if;
2355 -- bug#7645588, end
2356
2357 IF rec_temp.insert_update_flag IN('U','X') THEN
2358
2359 IF lv_amount_includes_tax_flag = 'Y' then
2360 ln_taxable_amount := rec_customer_trx_lines.extended_amount - rec_temp.extended_amount;
2361 ELSE
2362 ln_taxable_amount := rec_customer_trx_lines.extended_amount;
2363 END IF;
2364
2365 IF p_debug = 'Y' THEN
2366 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_lines');
2367 END IF;
2368
2369
2370 -- #### IMPORTANT FOR R12
2371 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, start */
2372 OPEN c_gl_posted_date_cur( rec_temp.link_to_cust_trx_line_id);
2373 FETCH c_gl_posted_date_cur INTO ld_gl_posted_date ;
2374 CLOSE c_gl_posted_date_cur ;
2375
2376 IF ld_gl_posted_date is NULL THEN
2377
2378 -- #### IMPORTANT FOR R12 ENDS HERE
2379
2380 --This procedure inserts the data into ra_customer_trx_lines_all
2381 insert_trx_lines(p_extended_amount => rec_temp.extended_amount,
2382 p_taxable_amount => ln_taxable_amount,
2383 p_customer_trx_line_id => rec_temp.customer_trx_line_id,
2384 p_last_update_date => rec_temp.last_update_date,
2385 p_last_updated_by => rec_temp.last_updated_by,
2386 p_creation_date => rec_temp.creation_date,
2387 p_created_by => rec_temp.created_by,
2388 p_last_update_login => rec_temp.last_update_login,
2389 p_customer_trx_id => rec_temp.customer_trx_id,
2390 p_line_number => rec_temp.line_number,
2391 p_set_of_books_id => rec_temp.set_of_books_id,
2392 p_link_to_cust_trx_line_id => rec_temp.link_to_cust_trx_line_id,
2393 p_line_type => rec_temp.line_type,
2394 p_org_id => rec_customer_trx.org_id,
2395 p_uom_code => rec_temp.uom_code,
2396 p_autotax => 'N',
2397 p_vat_tax_id => ln_vat_tax_id,
2398 p_interface_line_context => rec_customer_trx_lines.interface_line_context,
2399 p_interface_line_attribute6 => rec_customer_trx_lines.interface_line_attribute6,
2400 p_interface_line_attribute3 => rec_customer_trx_lines.interface_line_attribute3,
2401 p_process_status => lv_process_status,
2402 p_process_message => lv_process_message);
2403
2404 IF p_debug = 'Y' THEN
2405 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_lines');
2406 END IF;
2407
2408 IF lv_process_status <> jai_constants.successful THEN
2409 p_process_status := lv_process_status;
2410 p_process_message := lv_process_message;
2411 goto EXIT_POINT;
2412 END IF;
2413
2414 IF p_debug = 'Y' THEN
2415 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_line_gl_dist');
2416 END IF;
2417
2418
2419 --This procedure inserts the data into ra_cust_trx_line_gl_dist_all
2420 insert_trx_line_gl_dist(p_account_class => rec_temp.line_type,
2421 p_account_set_flag => lv_account_set_flag,
2422 p_acctd_amount => ROUND(rec_temp.acctd_amount, ln_precision),
2423 p_amount => rec_temp.amount,
2424 p_code_combination_id => rec_temp.code_combination_id,
2425 p_cust_trx_line_gl_dist_id => NULL,
2426 p_cust_trx_line_salesrep_id => rec_temp.cust_trx_line_sales_rep_id,
2427 p_customer_trx_id => rec_temp.customer_trx_id,
2428 p_customer_trx_line_id => rec_temp.customer_trx_line_id,
2429 p_gl_date => ld_gl_date,
2430 p_last_update_date => rec_temp.last_update_date,
2431 p_last_updated_by => rec_temp.last_updated_by,
2432 p_creation_date => rec_temp.creation_date,
2433 p_created_by => rec_temp.created_by,
2434 p_last_update_login => rec_temp.last_update_login,
2435 p_org_id => rec_customer_trx.org_id,
2436 p_percent => 100,
2437 p_posting_control_id => -3,
2438 p_set_of_books_id => rec_temp.set_of_books_id,
2439 p_process_status => lv_process_status,
2440 p_process_message => lv_process_message ,
2441 p_seq_id => ln_gl_seq,
2442 p_event_id => ln_event_id); --added for bug#7645588
2443
2444 IF p_debug = 'Y' THEN
2445 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_line_gl_dist');
2446 END IF;
2447
2448 --added the following IF condition for bug#7645588
2449 --added the OR condition for bug#8276902
2450 IF (ln_accounting_rule_id is not null OR rec_customer_trx_lines.interface_line_context = gv_projects_invoices)
2451 and ln_previous_customer_trx_id IS NOT NULL then
2452 NULL;
2453 ELSE
2454 -- ###### IMPORTANT FOR R12
2455 /* following added by Ramanand for Bug#4468353 as part of SLA uptake */
2456 l_xla_event.xla_req_id := NULL ;
2457 l_xla_event.xla_dist_id := ln_gl_seq ;
2458 l_xla_event.xla_doc_table := 'CT' ;
2459 l_xla_event.xla_doc_event := NULL ;
2460 l_xla_event.xla_mode := 'O' ;
2461 l_xla_event.xla_call := 'D' ;
2462 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event);
2463 -- ###### IMPORTANT FOR R12
2464 END IF;
2465
2466
2467
2468 IF lv_process_status <> jai_constants.successful THEN
2469 p_process_status := lv_process_status;
2470 p_process_message := lv_process_message;
2471 goto EXIT_POINT;
2472 END IF;
2473
2474
2475 --- #### IMPORTANT FOR R12
2476 ELSE /* ld_gl_posted_date will not be null when the execution comes here */
2477 raise Item_lines_already_accounted;
2478 END IF ; --ld_gl_posted_date is null
2479 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, end */
2480 END IF;
2481
2482 END LOOP; --End rec_temp
2483
2484 --get the sum of extended_amount, taxable_amount from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type
2485 OPEN cur_total_amt_trx_lines( p_customer_trx_id,
2486 NULL,
2487 lv_line_type_line);
2488 FETCH cur_total_amt_trx_lines INTO ln_old_amount,
2489 ln_taxable_amount; --this variable is not being used
2490 CLOSE cur_total_amt_trx_lines;
2491
2492
2493 --get the sum of amount, acctd_amount and max of acctd_amount from ra_cust_trx_line_gl_dist_all for cp_customer_trx_id
2494 --and account_class in ('TAX','FREIGHT')
2495 OPEN cur_total_amt_gl_dist(p_customer_trx_id);
2496 FETCH cur_total_amt_gl_dist INTO ln_tax_amt,
2497 ln_tax_acctd_amount,
2498 ln_max_tax_acctd_amount;
2499 CLOSE cur_total_amt_gl_dist;
2500
2501 IF ln_previous_customer_trx_id IS NOT NULL THEN --CM
2502 ln_old_acctd_amount := ln_old_amount * ln_cm_curr_conv_rate;
2503 ln_adjusted_tax := ROUND(ln_tax_amt * ln_cm_curr_conv_rate, ln_precision);
2504 else
2505 ln_old_acctd_amount := ln_old_amount * ln_inv_curr_conv_rate;
2506 ln_adjusted_tax := ROUND(ln_tax_amt * ln_inv_curr_conv_rate, ln_precision);
2507 end if;
2508
2509 /* Modified for bug#5495711
2510 || acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount + ln_diff_tax_frt), ln_precision)
2511 */
2512 UPDATE ra_cust_trx_line_gl_dist_all
2513 SET amount = ln_old_amount + ln_tax_amt,
2514 acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount ), ln_precision)
2515 WHERE customer_trx_id = p_customer_trx_id
2516 AND account_class = lv_account_class_rec --'REC'
2517 AND latest_rec_flag = 'Y';
2518
2519 IF lv_process_status <> jai_constants.successful THEN
2520
2521 p_process_status := lv_process_status;
2522 p_process_message := lv_process_message;
2523 goto EXIT_POINT;
2524
2525 END IF;
2526 --Added by Ramananda for bug#5495711, Ends
2527
2528 --get the sum of extended_amount, taxable_amount from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type
2529 OPEN cur_total_amt_trx_lines(p_customer_trx_id,
2530 NULL,
2531 lv_account_class_tax);
2532 FETCH cur_total_amt_trx_lines INTO ln_total_tax_amt_for_inv,
2533 ln_taxable_amount; --this variable is not being used
2534 CLOSE cur_total_amt_trx_lines;
2535
2536
2537 --get the sum of extended_amount, taxable_amount from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type
2538 OPEN cur_total_amt_trx_lines(p_customer_trx_id,
2539 NULL,
2540 lv_account_class_freight);
2541 FETCH cur_total_amt_trx_lines INTO ln_total_frt_amt_for_inv,
2542 ln_taxable_amount; --this variable is not being used
2543 CLOSE cur_total_amt_trx_lines;
2544
2545 IF p_debug = 'Y' THEN
2546 fnd_file.put_line(FND_FILE.LOG, 'Before ln_previous_customer_trx_id is not null'||ln_previous_customer_trx_id);
2547 END IF;
2548
2549 IF ln_previous_customer_trx_id IS NOT NULL THEN --CM
2550
2551 ln_payment_audit_id := NULL;
2552 ln_rec_appl_audit_id := NULL;
2553
2554 -- Added for bug#9230409, start
2555 /*unapplication of the receipt logic****/
2556 ln_row_count := 1;
2557 FOR rec_get_receipt_id IN ( select distinct cash_receipt_id, set_of_books_id /*Added set_of_books_id by mmurtuza for bug 13557242*/
2558 from ar_receivable_applications_all
2559 where status = 'APP'
2560 and application_type = 'CASH'
2561 and applied_customer_trx_id = ln_previous_customer_trx_id
2562 group by cash_receipt_id, set_of_books_id /*Added set_of_books_id by mmurtuza for bug 13557242*/
2563 having sum(acctd_amount_applied_from) > 0)
2564 LOOP
2565 lt_receipt_id_tab(ln_row_count) := rec_get_receipt_id.cash_receipt_id;
2566 lt_sob_id_tab(ln_row_count) := rec_get_receipt_id.set_of_books_id; -- Added for bug 13958098
2567
2568 OPEN cur_get_apply_date (lt_receipt_id_tab(ln_row_count),
2569 ln_previous_customer_trx_id);
2570 FETCH cur_get_apply_date INTO lt_apply_date(ln_row_count);
2571 CLOSE cur_get_apply_date;
2572
2573 OPEN cur_get_gl_date (lt_receipt_id_tab(ln_row_count),
2574 ln_previous_customer_trx_id,
2575 lt_apply_date(ln_row_count));
2576 FETCH cur_get_gl_date INTO lt_gl_date(ln_row_count);
2577 CLOSE cur_get_gl_date;
2578
2579 /*Start additions by mmurtuza for bug 13557242*/
2580
2581 OPEN c_period_dtl(lt_sob_id_tab(ln_row_count), lt_gl_date(ln_row_count));
2582 FETCH c_period_dtl
2583 INTO r_period_dtl;
2584 CLOSE c_period_dtl;
2585
2586 fnd_file.PUT_LINE(fnd_file.LOG, 'r_period_dtl.closing_status = ' || r_period_dtl.closing_status);
2587
2588 IF r_period_dtl.closing_status IN('O', 'F') THEN
2589 ld_accounting_date(ln_row_count) := lt_gl_date(ln_row_count);
2590
2591 ELSE
2592 FOR period IN
2593 (SELECT period_name,
2594 start_date,
2595 end_date,
2596 closing_status
2597 FROM gl_period_statuses
2598 WHERE application_id = jai_constants.gl_application_id
2599 AND set_of_books_id = lt_sob_id_tab(ln_row_count)
2600 AND start_date > lt_gl_date(ln_row_count)
2601 AND adjustment_period_flag = 'N' -- mmurtuza for bug 16181417
2602 ORDER BY period_year,
2603 period_num)
2604 LOOP
2605 IF period.closing_status IN('O', 'F') THEN
2606 ld_accounting_date(ln_row_count) := period.start_date;
2607 EXIT;
2608 END IF;
2609 END LOOP;
2610
2611 fnd_file.PUT_LINE(fnd_file.LOG, 'GL Period is closed for Accounting Date:' || to_char(lt_gl_date(ln_row_count)) || '. Hence passing with Entries for ' || to_char(ld_accounting_date(ln_row_count)));
2612
2613 END IF;
2614
2615 fnd_file.PUT_LINE(fnd_file.LOG, 'ld_accounting_date(ln_row_count) = ' || ld_accounting_date(ln_row_count));
2616
2617 /*End additions by mmurtuza for bug 13557242*/
2618
2619
2620 fnd_file.put_line(FND_FILE.LOG, 'lt_receipt_id_tab(i) '|| lt_receipt_id_tab(ln_row_count) ||
2621 ' ln_previous_customer_trx_id '|| ln_previous_customer_trx_id ||
2622 ' lt_apply_date(i) '|| lt_apply_date(ln_row_count) ||
2623 ' lt_gl_date(i) '|| lt_gl_date(ln_row_count) );
2624
2625
2626 AR_RECEIPT_API_PUB.Unapply (
2627 p_api_version => 1.0 ,
2628 p_init_msg_list => FND_API.G_TRUE ,
2629 x_return_status => lv_return_status ,
2630 x_msg_count => ln_msg_count ,
2631 x_msg_data => lv_msg_data ,
2632 p_cash_receipt_id => lt_receipt_id_tab(ln_row_count),
2633 p_customer_trx_id => ln_previous_customer_trx_id,
2634 --p_reversal_gl_date => lt_gl_date(ln_row_count)); /*commented and added below by mmurtuza for bug 13557242*/
2635 p_reversal_gl_date => ld_accounting_date(ln_row_count));
2636
2637
2638
2639 IF (lv_return_status <> 'S')
2640 THEN
2641 fnd_file.put_line(FND_FILE.LOG, '>>>>>>>>>> Problems during Receipt Unapplication');
2642 fnd_file.put_line(FND_FILE.LOG, 'lv_return_status : ' || lv_return_status);
2643 fnd_file.put_line(FND_FILE.LOG, 'ln_msg_count : ' || ln_msg_count);
2644 fnd_file.put_line(FND_FILE.LOG, 'lv_msg_data : ' || lv_msg_data);
2645 p_process_status := lv_return_status;
2646 p_process_message := lv_msg_data;
2647 goto EXIT_POINT;
2648 END IF;
2649
2650 ln_row_count := ln_row_count + 1;
2651
2652 END LOOP;
2653 --bug#9230409, end
2654
2655
2656 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2657 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2658 p_payment_schedule_id => NULL,
2659 p_cm_customer_trx_id => p_customer_trx_id,
2660 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2661 p_concurrent_req_num => NULL,
2662 p_request_id => NULL,
2663 p_operation_type => 'INSERT',
2664 p_payment_audit_id => ln_payment_audit_id,
2665 p_process_status => lv_process_status,
2666 p_process_message => lv_process_message);
2667
2668 IF lv_process_status <> jai_constants.successful THEN
2669 p_process_status := lv_process_status;
2670 p_process_message := lv_process_message;
2671 goto EXIT_POINT;
2672 END IF;
2673
2674 IF p_debug = 'Y' THEN
2675 fnd_file.put_line(FND_FILE.LOG, 'After INSERT call to maintain_schedules');
2676 END IF;
2677
2678 UPDATE ar_payment_schedules_all
2679 SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
2680 tax_original = ROUND(ln_total_tax_amt_for_inv, ln_precision),
2681 freight_original = ROUND(ln_total_frt_amt_for_inv, ln_precision),
2682 amount_applied = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
2683 last_update_date = SYSDATE
2684 WHERE customer_trx_id = p_customer_trx_id;
2685
2686 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2687 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2688 p_payment_schedule_id => NULL,
2689 p_cm_customer_trx_id => p_customer_trx_id,
2690 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2691 p_concurrent_req_num => NULL,
2692 p_request_id => NULL,
2693 p_operation_type => 'UPDATE',
2694 p_payment_audit_id => ln_payment_audit_id,
2695 p_process_status => lv_process_status,
2696 p_process_message => lv_process_message);
2697
2698 IF lv_process_status <> jai_constants.successful THEN
2699 p_process_status := lv_process_status;
2700 p_process_message := lv_process_message;
2701 goto EXIT_POINT;
2702 END IF;
2703
2704 IF p_debug = 'Y' THEN
2705 fnd_file.put_line(FND_FILE.LOG, 'After UPDATE call to maintain_schedules');
2706 END IF;
2707
2708
2709 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
2710 OPEN cur_min_payment_schedule(ln_previous_customer_trx_id);
2711 FETCH cur_min_payment_schedule INTO rec_inv_payment_schedule;
2712 CLOSE cur_min_payment_schedule;
2713
2714 --Get the first_installment_code and base_amount from ra_terms
2715 OPEN cur_term_details(rec_inv_payment_schedule.term_id);
2716 FETCH cur_term_details INTO rec_term_details;
2717 CLOSE cur_term_details;
2718
2719 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
2720 OPEN cur_tot_amt_for_cms(ln_previous_customer_trx_id,
2721 lv_account_class_tax);
2722 FETCH cur_tot_amt_for_cms INTO ln_tax_amt_cms;
2723 CLOSE cur_tot_amt_for_cms;
2724
2725 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
2726 OPEN cur_tot_amt_for_cms(ln_previous_customer_trx_id,
2727 lv_account_class_freight);
2728 FETCH cur_tot_amt_for_cms INTO ln_frt_amt_cms;
2729 CLOSE cur_tot_amt_for_cms;
2730
2731 --Get the SUM of tax_applied and freight_applied from ar_receivable_applications_all for Cash receipts applied
2732 OPEN cur_tot_cash_rcpt(ln_previous_customer_trx_id);
2733 FETCH cur_tot_cash_rcpt INTO ln_tax_amt_cashrcpt,
2734 ln_frt_amt_cashrcpt;
2735 CLOSE cur_tot_cash_rcpt;
2736
2737 IF p_debug = 'Y' THEN
2738 fnd_file.put_line(FND_FILE.LOG, 'Before entering rec_payment LOOP');
2739 END IF;
2740
2741 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
2742 FOR rec_payment in cur_payment_schedule(ln_previous_customer_trx_id)
2743 LOOP
2744 IF p_debug = 'Y' THEN
2745 fnd_file.put_line(FND_FILE.LOG, 'In rec_payment LOOP');
2746 END IF;
2747
2748
2749 --Get the relative_amount from ra_terms_lines
2750 OPEN cur_term_lines(rec_payment.term_id,
2751 rec_payment.terms_sequence_number);
2752 FETCH cur_term_lines INTO ln_relative_amt;
2753 CLOSE cur_term_lines;
2754
2755 IF p_debug = 'Y' THEN
2756 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_term_lines');
2757 END IF;
2758
2759 IF rec_term_details.first_installment_code = 'ALLOCATE' THEN
2760 ln_apportion_factor := ln_relative_amt/rec_term_details.base_amount;
2761 ELSE
2762 ln_apportion_factor := 1;
2763 rec_payment.payment_schedule_id := rec_inv_payment_schedule.payment_schedule_id;
2764 END IF;
2765
2766 --Get the SUM of line_applied from ar_receivable_applications_all for CM
2767 OPEN cur_tot_recv_appl( ln_previous_customer_trx_id,
2768 rec_payment.payment_schedule_id);
2769 FETCH cur_tot_recv_appl INTO ln_line_applied;
2770 CLOSE cur_tot_recv_appl;
2771
2772 IF p_debug = 'Y' THEN
2773 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_tot_recv_appl');
2774 END IF;
2775
2776 ln_inst_tax_amt_for_inv := ln_tax_amt_cms * ln_apportion_factor;
2777 ln_inst_frt_amt_for_inv := ln_frt_amt_cms * ln_apportion_factor;
2778
2779
2780 --get the allow_overapplication_flag from ra_cust_trx_types_all for cust_trx_type_id
2781 OPEN cur_trx_types(rec_inv_customer_trx.cust_trx_type_id);
2782 FETCH cur_trx_types INTO lv_allow_overappln_flag ;
2783 CLOSE cur_trx_types ;
2784
2785 IF p_debug = 'Y' THEN
2786 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_trx_types');
2787 END IF;
2788
2789
2790 --Get the data from ar_payment_schedules_all for customer_trx_id
2791 OPEN cur_prev_payment_schedule( ln_previous_customer_trx_id,
2792 rec_payment.payment_schedule_id);
2793 FETCH cur_prev_payment_schedule INTO rec_prev_payment_schedule;
2794 CLOSE cur_prev_payment_schedule;
2795
2796 IF p_debug = 'Y' THEN
2797 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_payment_schedule');
2798 END IF;
2799
2800 ln_amt_due_rem := NVL(rec_prev_payment_schedule.amount_line_items_remaining,0)
2801 + NVL(rec_prev_payment_schedule.tax_original,0)
2802 + NVL(rec_prev_payment_schedule.freight_original,0)
2803 + NVL(ln_inst_tax_amt_for_inv,0)
2804 + NVL(ln_inst_frt_amt_for_inv,0)
2805 - ln_tax_amt_cashrcpt
2806 - ln_frt_amt_cashrcpt;
2807
2808 IF p_debug = 'Y' THEN
2809 fnd_file.put_line(FND_FILE.LOG, 'ln_amt_due_--| '||ln_amt_due_rem);
2810 END IF;
2811
2812 IF ( NVL(lv_allow_overappln_flag,'N') = 'Y' ) OR
2813 ( NVL(lv_allow_overappln_flag,'N') = 'N' AND ln_amt_due_rem >= 0 )
2814 THEN
2815
2816 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2817 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2818 p_payment_schedule_id => rec_payment.payment_schedule_id,
2819 p_cm_customer_trx_id => p_customer_trx_id,
2820 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2821 p_concurrent_req_num => NULL,
2822 p_request_id => NULL,
2823 p_operation_type => 'INSERT',
2824 p_payment_audit_id => ln_payment_audit_id,
2825 p_process_status => lv_process_status,
2826 p_process_message => lv_process_message);
2827
2828 IF lv_process_status <> jai_constants.successful THEN
2829 p_process_status := lv_process_status;
2830 p_process_message := lv_process_message;
2831 goto EXIT_POINT;
2832 END IF;
2833
2834 IF p_debug = 'Y' THEN
2835 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_schedules');
2836 END IF;
2837
2838 UPDATE ar_payment_schedules_all
2839 SET amount_due_remaining = ROUND (ln_amt_due_rem ,ln_precision) ,
2840 tax_remaining = ROUND(tax_original - ln_tax_amt_cashrcpt + NVL(ln_inst_tax_amt_for_inv,0),ln_precision) ,
2841 freight_remaining = ROUND(freight_original - ln_frt_amt_cashrcpt + NVL(ln_inst_frt_amt_for_inv,0),ln_precision) ,
2842 acctd_amount_due_remaining = ROUND(ln_amt_due_rem * ln_inv_curr_conv_rate, ln_precision) ,
2843 amount_credited = (-1) * ROUND( ( NVL(ln_line_Applied,0) - NVL(ln_inst_tax_amt_for_inv,0) - NVL(ln_inst_frt_amt_for_inv,0)),ln_precision),
2844 last_update_date = SYSDATE
2845 WHERE customer_trx_id = ln_previous_customer_trx_id
2846 AND payment_schedule_id = rec_payment.payment_schedule_id;
2847
2848
2849 IF p_debug = 'Y' THEN
2850 fnd_file.put_line(FND_FILE.LOG, 'After first update fo ar_payment_schedules_all');
2851 END IF;
2852
2853 UPDATE ar_payment_schedules_all
2854 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2855 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
2856 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ),-- TO_DATE('31-DEC-4712','DD-MON-YYYY')),
2857 last_update_date = SYSDATE
2858 WHERE customer_trx_id = ln_previous_customer_trx_id
2859 AND payment_schedule_id = rec_payment.payment_schedule_id;
2860
2861 IF p_debug = 'Y' THEN
2862 fnd_file.put_line(FND_FILE.LOG, 'After second update fo ar_payment_schedules_all');
2863 END IF;
2864
2865 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2866 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2867 p_payment_schedule_id => rec_payment.payment_schedule_id,
2868 p_cm_customer_trx_id => p_customer_trx_id,
2869 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2870 p_concurrent_req_num => NULL,
2871 p_request_id => NULL,
2872 p_operation_type => 'UPDATE',
2873 p_payment_audit_id => ln_payment_audit_id,
2874 p_process_status => lv_process_status,
2875 p_process_message => lv_process_message);
2876
2877 IF lv_process_status <> jai_constants.successful THEN
2878 p_process_status := lv_process_status;
2879 p_process_message := lv_process_message;
2880 goto EXIT_POINT;
2881 END IF;
2882
2883 IF p_debug = 'Y' THEN
2884 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_schedules');
2885 END IF;
2886
2887
2888 --Get the receivable_application_id from ar_receivable_applications_all for the Invoice's payment_schedule_id
2889 OPEN cur_recv_appl_id(ln_previous_customer_trx_id,
2890 p_customer_trx_id,
2891 rec_payment.payment_schedule_id);
2892 FETCH cur_recv_appl_id INTO ln_recv_appln_id;
2893 CLOSE cur_recv_appl_id;
2894
2895
2896 --This procedure maintains the history of ar_receivable_applications_all in jai_ar_rec_appl_audits
2897 maintain_applications(p_customer_trx_id => p_customer_trx_id,
2898 p_receivable_application_id => ln_recv_appln_id,
2899 p_concurrent_req_num => NULL,
2900 p_request_id => NULL,
2901 p_operation_type => 'INSERT',
2902 p_rec_appl_audit_id => ln_rec_appl_audit_id,
2903 p_process_status => lv_process_status,
2904 p_process_message => lv_process_message);
2905
2906 IF lv_process_status <> jai_constants.successful THEN
2907 p_process_status := lv_process_status;
2908 p_process_message := lv_process_message;
2909 goto EXIT_POINT;
2910 END IF;
2911
2912 IF p_debug = 'Y' THEN
2913 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_applications');
2914 END IF;
2915
2916
2917 UPDATE ar_receivable_applications_all
2918 SET amount_applied = ROUND( NVL(line_applied,0)
2919 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2920 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2921 ,ln_precision),
2922 acctd_amount_applied_from = ROUND( ( NVL(line_applied,0)
2923 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2924 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2925 ) * ln_cm_curr_conv_rate
2926 ,ln_precision),
2927 acctd_amount_applied_to = ROUND( ( NVL(line_applied,0)
2928 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2929 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2930 ) * ln_inv_curr_conv_rate
2931 ,ln_precision),
2932 tax_applied = (ROUND( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
2933 freight_applied = (ROUND( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
2934 last_update_date = SYSDATE
2935 WHERE customer_trx_id = p_customer_trx_id
2936 AND receivable_application_id = ln_recv_appln_id;
2937 /*
2938 || Modified by Ramananda for bug#5495711, Ends
2939 */
2940
2941 IF p_debug = 'Y' THEN
2942 fnd_file.put_line(FND_FILE.LOG, 'after update of ar_receivable_applications_all');
2943 END IF;
2944
2945 maintain_applications(p_customer_trx_id => p_customer_trx_id,
2946 p_receivable_application_id => ln_recv_appln_id,
2947 p_concurrent_req_num => NULL,
2948 p_request_id => NULL,
2949 p_operation_type => 'UPDATE',
2950 p_rec_appl_audit_id => ln_rec_appl_audit_id,
2951 p_process_status => lv_process_status,
2952 p_process_message => lv_process_message);
2953
2954 IF lv_process_status <> jai_constants.successful THEN
2955 p_process_status := lv_process_status;
2956 p_process_message := lv_process_message;
2957 goto EXIT_POINT;
2958 END IF;
2959
2960 IF p_debug = 'Y' THEN
2961 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_applications');
2962 END IF;
2963
2964 --added for bug#8325824, start
2965 IF p_debug = 'Y' THEN
2966 fnd_file.put_line(FND_FILE.LOG, 'before insert into ar_distributions_all');
2967 END IF;
2968 insert_ar_dist_entries( p_customer_trx_id => p_customer_trx_id,
2969 p_receivable_appl_id => ln_recv_appln_id,
2970 p_debug => p_debug,
2971 p_process_status => lv_process_status,
2972 p_process_message => lv_process_message
2973 );
2974
2975 IF p_debug = 'Y' THEN
2976 fnd_file.put_line(FND_FILE.LOG, 'after insert into ar_distributions_all');
2977 END IF;
2978
2979 --bug#8325824, end
2980
2981 IF rec_term_details.first_installment_code <> 'ALLOCATE' THEN
2982 EXIT;
2983 END IF;
2984
2985 ELSE --over_application condition
2986 p_process_message := 'CM : Allow Over application on invoice is not allowed , hence not processing the taxes on the credit memo';
2987 p_process_status := jai_constants.expected_error;
2988
2989 goto EXIT_POINT;
2990
2991 END IF ;
2992
2993 END LOOP; --End rec_payment cursor
2994 --added for bug#9230409,start
2995 /**application of the receipt for the remaining amount i.e after application of CM***/
2996 -- added by Allen Yang 04-Jun-2010 for bug #9709906, begin
2997 --------------------------------------------------------------------------
2998 /* for non-shippable RMAs, receipt action is not required and supported,
2999 hence lt_receipt_id_tab will be null for non-shippable RMAs*/
3000 IF lt_receipt_id_tab.COUNT > 0 THEN
3001 --------------------------------------------------------------------------
3002 -- added by Allen Yang 04-Jun-2010 for bug #9709906, end
3003 FOR i in lt_receipt_id_tab.FIRST..lt_receipt_id_tab.LAST
3004 LOOP
3005 fnd_file.put_line(FND_FILE.LOG, 'lt_receipt_id_tab(i) '|| lt_receipt_id_tab(i) ||
3006 ' ln_previous_customer_trx_id '|| ln_previous_customer_trx_id ||
3007 ' lt_apply_date(i) '|| lt_apply_date(i) ||
3008 ' lt_gl_date(i) '|| lt_gl_date(i) );
3009 AR_RECEIPT_API_PUB.Apply (
3010 p_api_version => 1.0 ,
3011 p_init_msg_list => FND_API.G_TRUE ,
3012 x_return_status => lv_return_status ,
3013 x_msg_count => ln_msg_count ,
3014 x_msg_data => lv_msg_data ,
3015 p_cash_receipt_id => lt_receipt_id_tab(i) ,
3016 p_customer_trx_id => ln_previous_customer_trx_id,
3017 p_apply_date => lt_apply_date(i) ,
3018 --p_apply_gl_date => lt_gl_date(i) /*commented and added below by mmurtuza for bug 13557242*/
3019 p_apply_gl_date => ld_accounting_date(i)
3020 );
3021 IF (lv_return_status <> 'S')
3022 THEN
3023 fnd_file.put_line(FND_FILE.LOG, '>>>>>>>>>> Problems during Receipt Application');
3024 fnd_file.put_line(FND_FILE.LOG, 'lv_return_status : ' || lv_return_status);
3025 fnd_file.put_line(FND_FILE.LOG, 'ln_msg_count : ' || ln_msg_count);
3026 fnd_file.put_line(FND_FILE.LOG, 'lv_msg_data : ' || lv_msg_data);
3027 p_process_status := lv_return_status;
3028 p_process_message := lv_msg_data;
3029 goto EXIT_POINT;
3030 END IF;
3031 END LOOP;
3032 -- added by Allen Yang 04-Jun-2010 for bug #9709906, begin
3033 END IF; -- lt_receipt_id_tab.COUNT > 0
3034 -- added by Allen Yang 04-Jun-2010 for bug #9709906, end
3035 --bug#9230409, end
3036
3037 ELSE --In case of invoice
3038
3039 ln_payment_audit_id := NULL;
3040
3041 IF p_debug = 'Y' THEN
3042 fnd_file.put_line(FND_FILE.LOG, 'In else of previous_customer_trx_id');
3043 END IF;
3044
3045 --Get the first_installment_code and base_amount from ra_terms
3046 OPEN cur_term_details(rec_min_payment_schedule.term_id);
3047 FETCH cur_term_details INTO rec_term_details;
3048 CLOSE cur_term_details;
3049
3050 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
3051 FOR rec_payment in cur_payment_schedule(p_customer_trx_id)
3052 LOOP
3053
3054 IF p_debug = 'Y' THEN
3055 fnd_file.put_line(FND_FILE.LOG, 'In LOOP cur_payment_schedule - rec_payment.payment_schedule_id'||rec_payment.payment_schedule_id);
3056 fnd_file.put_line(FND_FILE.LOG, 'In LOOP cur_payment_schedule - rec_term_details.first_installment_code'||rec_term_details.first_installment_code);
3057 END IF;
3058
3059 --Get the relative_amount from ra_terms_lines
3060 OPEN cur_term_lines(rec_payment.term_id,
3061 rec_payment.terms_sequence_number);
3062 FETCH cur_term_lines INTO ln_relative_amt;
3063 CLOSE cur_term_lines;
3064
3065 IF rec_term_details.first_installment_code = 'ALLOCATE' THEN
3066 ln_apportion_factor := ln_relative_amt/rec_term_details.base_amount;
3067 ELSE
3068 ln_apportion_factor := 1;
3069 rec_payment.payment_schedule_id := rec_min_payment_schedule.payment_schedule_id;
3070 END IF;
3071
3072 ln_inst_tax_amt_for_inv := ln_total_tax_amt_for_inv * ln_apportion_factor;
3073 ln_inst_frt_amt_for_inv := ln_total_frt_amt_for_inv * ln_apportion_factor;
3074
3075
3076 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
3077 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
3078 p_payment_schedule_id => rec_payment.payment_schedule_id,
3079 p_cm_customer_trx_id => NULL,
3080 p_invoice_customer_trx_id => p_customer_trx_id,
3081 p_concurrent_req_num => NULL,
3082 p_request_id => NULL,
3083 p_operation_type => 'INSERT',
3084 p_payment_audit_id => ln_payment_audit_id,
3085 p_process_status => lv_process_status,
3086 p_process_message => lv_process_message);
3087
3088 IF lv_process_status <> jai_constants.successful THEN
3089 p_process_status := lv_process_status;
3090 p_process_message := lv_process_message;
3091 goto EXIT_POINT;
3092 END IF;
3093
3094 IF p_debug = 'Y' THEN
3095 fnd_file.put_line(FND_FILE.LOG, 'After call to maintain_schedules');
3096 END IF;
3097
3098 UPDATE ar_payment_schedules_all
3099 SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0) , ln_precision),
3100 amount_due_remaining = ROUND(NVL(amount_line_items_remaining,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
3101 tax_original = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
3102 tax_remaining = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
3103 freight_original = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
3104 freight_remaining = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
3105 acctd_amount_due_remaining = ROUND(( NVL(amount_line_items_remaining,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0) ) * ln_inv_curr_conv_rate, ln_precision),
3106 last_update_date = SYSDATE
3107 WHERE customer_trx_id = p_customer_trx_id
3108 AND payment_schedule_id = rec_payment.payment_schedule_id;
3109
3110 IF p_debug = 'Y' THEN
3111 fnd_file.put_line(FND_FILE.LOG, 'After First update of ar_payment_schedules_all');
3112 END IF;
3113
3114 UPDATE ar_payment_schedules_all
3115 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
3116 gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
3117 actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')),
3118 last_update_date = SYSDATE
3119 WHERE customer_trx_id = p_customer_trx_id
3120 AND payment_schedule_id = rec_payment.payment_schedule_id;
3121
3122 IF p_debug = 'Y' THEN
3123 fnd_file.put_line(FND_FILE.LOG, 'After Second update of ar_payment_schedules_all '||SQL%ROWCOUNT);
3124 END IF;
3125
3126
3127 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
3128 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
3129 p_payment_schedule_id => rec_payment.payment_schedule_id,
3130 p_cm_customer_trx_id => NULL,
3131 p_invoice_customer_trx_id => p_customer_trx_id,
3132 p_concurrent_req_num => NULL,
3133 p_request_id => NULL,
3134 p_operation_type => 'UPDATE',
3135 p_payment_audit_id => ln_payment_audit_id,
3136 p_process_status => lv_process_status,
3137 p_process_message => lv_process_message);
3138
3139 fnd_file.put_line(FND_FILE.LOG, 'Out ln_payment_audit_id '||ln_payment_audit_id);
3140
3141 IF lv_process_status <> jai_constants.successful THEN
3142 p_process_status := lv_process_status;
3143 p_process_message := lv_process_message;
3144 goto EXIT_POINT;
3145 END IF;
3146
3147 IF p_debug = 'Y' THEN
3148 fnd_file.put_line(FND_FILE.LOG, 'After call to maintain_schedules');
3149 END IF;
3150
3151 IF rec_term_details.first_installment_code <> 'ALLOCATE' THEN
3152 EXIT;
3153 END IF;
3154
3155 END LOOP; --End cursor rec_payment
3156 END IF;
3157
3158
3159 --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all, ar_receivable_applications_all
3160 maintain_mrc( p_customer_trx_id => p_customer_trx_id,
3161 p_previous_cust_trx_id => ln_previous_customer_trx_id,
3162 p_called_from => 7/13/2007,
3163 p_process_status => lv_process_status,
3164 p_process_message => lv_process_message);
3165
3166 IF lv_process_status <> jai_constants.successful THEN
3167 p_process_status := lv_process_status;
3168 p_process_message := lv_process_message;
3169 goto EXIT_POINT;
3170 END IF;
3171
3172 <<EXIT_POINT>>
3173 NULL;
3174
3175 EXCEPTION
3176 WHEN resource_busy THEN
3177 fnd_file.put_line(FND_FILE.LOG,'Resource Busy,record '||p_customer_trx_id||' has been locked by another resource');
3178 p_process_message:= ' Resource Busy,record '||p_customer_trx_id||' has been locked by another resource ';
3179 p_process_status := jai_constants.unexpected_error;
3180
3181 WHEN LOCALIZATION_TAX_NOT_DEFINED THEN
3182 fnd_file.put_line(FND_FILE.LOG,' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ');
3183 p_process_message:= ' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ';
3184 p_process_status := jai_constants.expected_error;
3185
3186 WHEN ROUNDING_ACCOUNT_NOT_DEFINED THEN
3187 fnd_file.put_line(FND_FILE.LOG, lv_process_message );
3188 p_process_message := lv_process_message;
3189 p_process_status := lv_process_status;
3190
3191 WHEN OTHERS THEN
3192 fnd_file.put_line(FND_FILE.LOG,sqlerrm);
3193 p_process_status := jai_constants.unexpected_error;
3194 p_process_message :=SUBSTR(SQLERRM,1,120);
3195
3196 UPDATE JAI_AR_TRX_INS_LINES_T
3197 SET error_flag = 'R',
3198 err_mesg = p_process_message
3199 WHERE customer_trx_id = p_customer_trx_id;
3200 END process_from_order_line;
3201
3202 ----------------------------------------------- --------------------
3203
3204
3205 PROCEDURE process_manual_invoice(ERRBUF OUT NOCOPY VARCHAR2,
3206 RETCODE OUT NOCOPY VARCHAR2,
3207 P_CUSTOMER_TRX_ID IN NUMBER,
3208 P_LINK_LINE_ID IN NUMBER)
3209 IS
3210 v_counter Number:= 0;
3211 v_gl_date Date;
3212 v_org_id Number;
3213 v_line_no Number := 0;
3214 v_receivable_amount Number := 0;
3215 v_receivable_acctd_amount Number := 0;
3216 v_old_amount Number := 0;
3217 v_old_acctd_amount Number := 0;
3218 v_vat_tax_id nUMBER(15);
3219 v_created_from Varchar2(40);
3220 v_tax_amount Number := 0;
3221 v_tax_amount1 Number := 0;
3222 v_freight_amount Number := 0;
3223 v_freight_amount1 Number := 0;
3224 v_payment_schedule_id Number ;
3225 lv_tax_const CONSTANT VARChar2(10) := 'TAX'; --rchandan for bug#4428980
3226 lv_freight_acc_class CONSTANT varchar2(10) := 'FREIGHT';--rchandan for bug#4428980
3227 lv_acc_class_rev CONSTANT varchar2(10) := 'REV';--rchandan for bug#4428980
3228 lv_acc_class_rec CONSTANT varchar2(10) := 'REC';--rchandan for bug#4428980
3229
3230 /*Bug 11936630*/
3231 CURSOR count_cur IS
3232 SELECT count(customer_trx_line_id) FROM JAI_AR_TRX_INS_LINES_T jatilt
3233 WHERE customer_trx_id = P_CUSTOMER_TRX_ID
3234 AND EXISTS (select '1'
3235 from JAI_AR_TRX_TAX_LINES jattl
3236 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
3237 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
3238 and exists (select '1'
3239 from JAI_CMN_TAXES_ALL
3240 where jattl.tax_id = tax_id
3241 and NVL(inclusive_tax_flag,'N') = 'N'));
3242
3243 CURSOR ORG_CUR IS
3244 SELECT ORG_ID, CREATED_FROM
3245 FROM RA_CUSTOMER_TRX_ALL
3246 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
3247
3248
3249 /*Bug 11936630*/
3250 CURSOR TEMP_CUR IS
3251 SELECT EXTENDED_AMOUNT,CUSTOMER_TRX_LINE_ID,CUSTOMER_TRX_ID ,SET_OF_BOOKS_ID,
3252 LINK_TO_CUST_TRX_LINE_ID,LINE_TYPE ,UOM_CODE,VAT_TAX_ID,ACCTD_AMOUNT,AMOUNT,
3253 CODE_COMBINATION_ID,CUST_TRX_LINE_SALES_REP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
3254 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,INSERT_UPDATE_FLAG
3255 FROM JAI_AR_TRX_INS_LINES_T jatilt
3256 WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
3257 link_to_cust_trx_line_id = p_link_line_id
3258 AND EXISTS (select '1'
3259 from JAI_AR_TRX_TAX_LINES jattl
3260 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
3261 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
3262 and exists (select '1'
3263 from JAI_CMN_TAXES_ALL
3264 where jattl.tax_id = tax_id
3265 and NVL(inclusive_tax_flag,'N') = 'N'))
3266 order by CUSTOMER_TRX_LINE_ID;
3267
3268
3269 /* Added by Ramananda for bug#4468353 due to SLA uptake by AR */
3270 CURSOR c_gl_posted_date_cur(p_customer_trx_line_id RA_CUST_TRX_LINE_GL_DIST_ALL.customer_trx_line_id%type) IS
3271 SELECT gl_posted_date
3272 from RA_CUST_TRX_LINE_GL_DIST_ALL
3273 where customer_trx_line_id = p_customer_trx_line_id
3274 and account_class = 'REC'
3275 and latest_rec_flag = 'Y';
3276
3277 ld_gl_posted_date RA_CUST_TRX_LINE_GL_DIST_ALL.gl_posted_date%type ;
3278 /*Bug 11936630*/
3279 CURSOR GL_DATE_CUR IS
3280 SELECT DISTINCT gl_date
3281 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
3282 WHERE CUSTOMER_TRX_LINE_ID IN (SELECT LINK_TO_CUST_TRX_LINE_ID FROM JAI_AR_TRX_INS_LINES_T jatilt
3283 WHERE customer_trx_id = P_CUSTOMER_TRX_ID
3284 AND EXISTS (select '1'
3285 from JAI_AR_TRX_TAX_LINES jattl
3286 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
3287 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
3288 and exists (select '1'
3289 from JAI_CMN_TAXES_ALL
3290 where jattl.tax_id = tax_id
3291 and NVL(inclusive_tax_flag,'N') = 'N')));
3292
3293
3294 CURSOR MAX_LINE_CUR(p_cust_link_line_id IN NUMBER, p_line_type IN VARCHAR2) IS
3295 SELECT NVL(MAX(line_number),0)
3296 FROM RA_CUSTOMER_TRX_LINES_ALL
3297 WHERE link_to_cust_trx_line_id = p_cust_link_line_id
3298 and line_type = p_line_type;
3299
3300 /*Bug 11936630*/
3301 CURSOR LINK_LINE_CUR IS
3302 SELECT LINK_TO_CUST_TRX_LINE_ID,ERROR_FLAG --added the error_flag condition to process the records,which got stuck up
3303 FROM JAI_AR_TRX_INS_LINES_T jatilt
3304 WHERE customer_trx_id = P_CUSTOMER_TRX_ID AND LINK_TO_CUST_TRX_LINE_ID = p_link_line_id
3305 AND EXISTS (select '1'
3306 from JAI_AR_TRX_TAX_LINES jattl
3307 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
3308 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
3309 and exists (select '1'
3310 from JAI_CMN_TAXES_ALL
3311 where jattl.tax_id = tax_id
3312 and NVL(inclusive_tax_flag,'N') = 'N'));
3313 /*Bug 11936630*/
3314 CURSOR PREVIOUS_AMOUNT_CUR IS
3315 SELECT A.AMOUNT , A.ACCTD_AMOUNT
3316 FROM RA_CUST_TRX_LINE_GL_DIST_ALL A, RA_CUSTOMER_TRX_LINES_ALL B, JAI_AR_TRX_INS_LINES_T C
3317 WHERE A.CUSTOMER_TRX_LINE_ID = B.CUSTOMER_TRX_LINE_ID
3318 AND B.LINK_TO_CUST_TRX_LINE_ID = C.LINK_TO_CUST_TRX_LINE_ID
3319 AND C.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3320 AND A.ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class)
3321 AND A.CUSTOMER_TRX_LINE_ID = C.CUSTOMER_TRX_LINE_ID
3322 AND EXISTS (select '1'
3323 from JAI_AR_TRX_TAX_LINES jattl
3324 where jattl.link_to_cust_trx_line_id = c.link_to_cust_trx_line_id
3325 and jattl.customer_trx_line_id = c.customer_trx_line_id
3326 and exists (select '1'
3327 from JAI_CMN_TAXES_ALL
3328 where jattl.tax_id = tax_id
3329 and NVL(inclusive_tax_flag,'N') = 'N'));
3330
3331
3332 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
3333 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
3334 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
3335 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
3336 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
3337
3338 --2001/06/26 Anuradha Parthasarathy
3339 Cursor payment_schedule_cur IS
3340 Select min(payment_schedule_id)
3341 From Ar_Payment_Schedules_All
3342 Where Customer_trx_ID = p_customer_trx_id;
3343
3344 /* AR Transactions with Invoicing Acctg Rules not supported by Localization, Enhancement Done on 16th NOV */
3345 Cursor accounting_set_cur IS
3346 Select accounting_rule_id
3347 From Ra_Customer_Trx_Lines_All
3348 Where Customer_Trx_Line_Id = p_link_line_id;
3349 v_accounting_rule_id Number;
3350 v_account_set_flag Char(1);
3351
3352 Cursor prev_customer_trx_cur(p_line_type ra_customer_trx_lines_all.line_type%TYPE ) is
3353 Select previous_customer_trx_id
3354 from ra_customer_trx_lines_all
3355 where customer_trx_id = P_CUSTOMER_TRX_ID
3356 and line_type = p_line_type;
3357 --AND customer_trx_line_id = p_link_line_id; --Added this condition on 05-Apr-2002 as it should fetch only one value
3358 v_prev_customer_trx_id Number;
3359
3360 Cursor Inv_payment_schedule_cur(p_prev_customer_trx_id IN Number) is
3361 Select payment_schedule_id
3362 from ar_payment_schedules_all
3363 where customer_trx_id = p_prev_customer_trx_id;
3364
3365 --2001/07/04 Anuradha Parthasarathy
3366 v_interface_line_attribute6 Varchar2(30);
3367 v_return_reference_type_code Varchar2(30);
3368 v_credit_invoice_line_id Number;
3369
3370 Cursor line_id_cur(p_line_type ra_customer_trx_lines_all.line_type%TYPE ) is
3371 Select interface_line_attribute6
3372 From ra_customer_trx_lines_all
3373 Where customer_trx_id = p_customer_trx_id
3374 and line_type = p_line_type;
3375
3376 Cursor Ref_type_cur(p_line_id IN Number) is
3377 Select context,reference_line_id
3378 From oe_order_lines_all
3379 Where line_id = p_line_id;
3380
3381 v_upd_created_from varchar2(15);
3382 v_rma_check Number;
3383 v_temp_cust_trx_id Number;
3384
3385 CURSOR ORG_CUR_UPD IS
3386 SELECT created_from,
3387 NVL(exchange_rate,1) exchange_rate --9177024
3388 FROM RA_CUSTOMER_TRX_ALL
3389 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
3390
3391 CURSOR check_rma_ref IS
3392 SELECT 1 from JAI_OM_OE_RMA_LINES
3393 WHERE TO_CHAR(RMA_NUMBER) IN (SELECT INTERFACE_HEADER_ATTRIBUTE1 FROM RA_CUSTOMER_TRX_ALL
3394 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID)
3395 AND Rma_line_id in ( Select RMA_LINE_ID from JAI_OM_OE_RMA_TAXES a,JAI_CMN_TAXES_ALL b
3396 Where a.tax_id = b.tax_id
3397 AND b.tax_type = jai_constants.tax_type_freight );
3398
3399 --added 12-Mar-2002
3400 CURSOR tax_type IS SELECT b.tax_type t_type,a.customer_trx_line_id line_id
3401 FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
3402 WHERE link_to_cust_trx_line_id = p_link_line_id
3403 and A.tax_id = B.tax_id;
3404
3405 CURSOR get_reason IS
3406 SELECT reason_code FROM
3407 RA_CUSTOMER_TRX_ALL WHERE
3408 CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
3409 v_reason_code ra_customer_trx_all.reason_code%TYPE;
3410 --end 12-Mar-2002
3411 v_tax_amt Number;
3412 v_err_mesg VARCHAR2(250);
3413 l_retcode NUMBER(1);
3414 l_errbuf VARCHAR2(1996);
3415
3416 CURSOR get_trx_num IS SELECT --21-Mar-2002 for ar tax and freight
3417 trx_number FROM
3418 ra_customer_trx_all WHERE
3419 customer_trx_id = p_customer_trx_id;
3420
3421 --added the following cursor for bug#8476512
3422 CURSOR cur_chk_jai_tax_dtls ( cp_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE,
3423 cp_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE)
3424 IS
3425 SELECT 1
3426 FROM jai_ar_trx_tax_lines
3427 WHERE customer_trx_line_id = cp_customer_trx_line_id
3428 AND link_to_cust_trx_line_id = cp_link_to_cust_trx_line_id;
3429 ln_tax_line_exists NUMBER; --added for bug#8476512
3430
3431
3432 v_trx_num ra_customer_trx_all.trx_number%TYPE;
3433 v_count_trx NUMBER;
3434 V_sum_amt NUMBER;
3435
3436 ------------------------------------------------------------------------------------------------
3437 -- start of modification added by subbu and Jagdish on 10-jun-01 for discount issue.
3438 v_extended_amount_line number;
3439 v_taxable_amt number := 0;
3440
3441 Cursor get_ext_amt_ln( p_line_type ra_customer_trx_lines.line_type%TYPE )
3442 is Select extended_amount
3443 from Ra_customer_trx_lines_all
3444 where customer_trx_id = P_CUSTOMER_TRX_ID
3445 and customer_trx_line_id = P_LINK_LINE_ID
3446 and line_type = p_line_type;--rchandan for bug#4428980
3447
3448 Cursor get_ext_amt_tax is Select extended_amount,customer_trx_line_id
3449 from Ra_customer_trx_lines_all
3450 where customer_trx_id = P_CUSTOMER_TRX_ID
3451 and Link_to_cust_trx_line_id = P_LINK_LINE_ID
3452 and line_type = lv_tax_const;--rchandan for bug#4428980
3453
3454 get_ext_amt_tax_rec get_ext_amt_tax%rowtype;
3455
3456 Cursor get_taxable_amt(cust_trx_ln_id number) Is Select nvl(taxable_amount,0)
3457 from ra_customer_trx_lines_all
3458 where customer_trx_line_id = cust_trx_ln_id
3459 and customer_trx_id = P_CUSTOMER_TRX_ID
3460 and line_type = lv_tax_const;--rchandan for bug#4428980
3461
3462
3463 -- end of modification by subbu and Jagdish on 10-jun-01 for discount issue.
3464 ------------------------------------------------------------------------------------------------
3465 --05-Apr-2002
3466
3467 v_rec_ctr Number ;
3468 v_PAYMENT_amt Number :=0;
3469 v_FREIGHT_amt Number :=0;
3470 V_TEMP Number ;
3471 v_sql_num Number ;
3472 v_amt_a NUMBER;
3473 v_tot_amount NUMBER;
3474 v_sql_count NUMBER;
3475 v1_sql_count NUMBER;
3476
3477 Localization_tax_not_defined EXCEPTION; -- added by sriram - 3340594
3478 Item_lines_already_accounted EXCEPTION;
3479 -- declaration for mrc starts here bug # 3326394
3480
3481 cursor c_gl_dist_cur is
3482 select cust_trx_line_gl_dist_id
3483 from ra_cust_trx_line_gl_dist_all
3484 where customer_trx_id = p_customer_trx_id
3485 and account_class = lv_acc_class_rec --rchandan for bug#4428980
3486 and latest_rec_flag = 'Y';
3487
3488 v_gl_dist_id number;
3489
3490
3491 /* Added by Ramananda for bug#4468353 due to sla uptake by AR, end */
3492 l_xla_event arp_xla_events.xla_events_type;
3493 --start additions for bug#9177024
3494
3495 lv_process_status VARCHAR2(100);
3496 lv_process_message VARCHAR2(1000);
3497 ln_exchange_rate jai_ar_trxs.exchange_rate%type;
3498 lv_account_class_tax VARCHAR2(10) := 'TAX';
3499 lv_account_class_freight VARCHAR2(10) := 'FREIGHT';
3500 lv_account_class_rec VARCHAR2(10) := 'REC';
3501 ln_precision fnd_currencies.precision%TYPE;
3502 ln_old_amount NUMBER;
3503 ln_taxable_amount NUMBER;
3504 ln_tax_amt NUMBER;
3505 ln_tax_acctd_amount NUMBER;
3506
3507
3508 CURSOR cur_total_amt_trx_lines( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
3509 cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE DEFAULT NULL,
3510 cp_line_type ra_customer_trx_lines_all.line_type%TYPE)
3511 IS
3512 SELECT NVL(SUM(extended_amount),0) extended_amount
3513 FROM ra_customer_trx_lines_all
3514 WHERE customer_trx_id = cp_customer_trx_id
3515 AND customer_trx_line_id = NVL(cp_customer_trx_line_id, customer_trx_line_id)
3516 AND line_type = cp_line_type;
3517
3518
3519 CURSOR cur_total_amt_gl_dist( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
3520 IS
3521 SELECT NVL(SUM(amount),0) amount,
3522 NVL(SUM(acctd_amount),0) acctd_amount
3523 FROM ra_cust_trx_line_gl_dist_all
3524 WHERE customer_trx_id = cp_customer_trx_id
3525 AND account_class IN (lv_account_class_tax,lv_account_class_freight);
3526
3527
3528
3529 --end additions for bug#9177024
3530
3531 --Added by Bo Li for Bug9803555 on 01-JUL-2010 ,Begin
3532 ---------------------------------------------------------------------
3533 ln_max_req_id NUMBER;
3534
3535 CURSOR get_max_request_id_cur(pn_customer_trx_id NUMBER,
3536 pn_customer_trx_line_id NUMBER)
3537 IS
3538 Select nvl(Max(Request_ID),-1)
3539 From Fnd_Concurrent_Programs FCP,
3540 Fnd_Application FA ,
3541 Fnd_Concurrent_Requests FCR
3542 Where FCR.Program_Application_ID = FA.Application_ID
3543 AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
3544 AND FA.Application_ID = FCP.Application_ID
3545 AND Concurrent_Program_Name = 'JAILINEGL'
3546 AND FA.Application_Short_Name = 'JA'
3547 AND FCR.argument1 = to_char(pn_customer_trx_id) -- added tochar 10385256
3548 AND FCR.argument2 = to_char(pn_customer_trx_line_id);--added tochar 10385266
3549
3550 ---------------------------------------------------------------------
3551 --Added by Bo Li for Bug9803555 on 01-JUL-2010,End
3552
3553
3554 BEGIN --MAIN BLOCK BEGIN
3555
3556
3557 /*------------------------------------------------------------------------------------------
3558 FILENAME: jai_ar_match_tax_pkg.process_manual_invoice.sql
3559
3560 CHANGE HISTORY:
3561
3562 S.No Date Author and Details
3563 1. 2001/06/26 Anuradha Parthasarathy
3564 Cursor defn changed for proper defaultation of Tax Code.
3565 2. 2001/07/04 Anuradha Parthasarathy
3566 Code added to credit tax amounts when rma references
3567 a Sales Order or an Invoice.
3568 3 2002/03/22 RPK : for BUG#2285636
3569
3570 Code modified to rollback the entire transactions if a transaction
3571 is failed.That is, the tax lines will be inserted all or none in the
3572 tables ra_customer_trx_lines_all ,ra_cust_trx_line_gl_dist_all.
3573 Also,whenever any record got errored out,then the corresponding invoice
3574 taxes will not be processed to the base table itself and that record will
3575 be updated to 'R'(column error_flag).
3576
3577 Code modified to get the freight lines for the RMA Transactions.
3578
3579 4 2002/04/04 Code merged for the issue of the receipt not getting saved when applied to an
3580 invoice having the discounts attached.
3581
3582 5 2002/04/09 For the BUG:2303830
3583 Added the condition IF v_rec_ctr > 0 to update only if the record is
3584 found in gl_dist table.
3585
3586 6 2002/04/22 RPK
3587 BUG#2247013
3588 Code modified to populate the freight lines for the Credit memo generated
3589 against a RMA transaction and the update the customer balances for the
3590 original invoice against which,this credit memo is generated.
3591
3592 7 2002/04/26 Sriram
3593 For Bug #2316589 for handling duplicate customer trx ids that are
3594 processed from manual invoice that might be stuck in the temp_lines
3595 insert table
3596
3597 8 2002/05/30 RPK
3598 BUG#2247013
3599 Bug re-opened to prevent the duplication of the taxes in the credit memo
3600 for the RMA transaction and also corresponding updation of the balances
3601 of the original invoice.
3602 10. 2003/02/17 Sriram - Bug # 2784431 - The select statement that identifies whether
3603 tax records already exist is not written correctly , it has been
3604 corrected.
3605
3606 11. 2003/12/26 Sriram - bug# 3340594 File Version 618.1
3607
3608 'Localization' tax if is end dates or is not present , it should show a meaningful
3609 error message instead of a cannot insert null into type of error.This has been
3610 acheived by adding an exception 'Localization_tax_not_defined' , raising the exception
3611 and handling the exception with the appropriate error message.
3612
3613 12. 2003/26/12 Sriram - bug# 3326394 File Version 618.2
3614
3615 incorporating code changes for multiple reporting currencies.
3616 api calls to ar_mrc_maintain procedure have been made at appropriate places to
3617 delete data from ra_mc_trx_line_gl_dist table in case of re-processing records.
3618
3619 api calls to ar_mrc_maintain package insert records in the RA_MC_TRX_LINE_GL_DIST table
3620 have been added to insert tax and freight records
3621
3622 api call to update the REC row for the gl dist also has been written .
3623
3624 no calls made for ar_payment_schedules and ar_receivable_applications because
3625 it is taken care when invoice is completed by base apps itself.
3626
3627 13. 28-May-2009 JMEENA for bug#8476512
3628 Modified the code in the procedure process_manual_invoice. Added the cursor cur_chk_jai_tax_dtls.
3629
3630 14 14-feb-2011 vkaranam for bug#10385256
3631 Issue:
3632 ERRATIC BEHAVIOUR IN INDIA AR TRANSACIONS TAX ACCOUNTING
3633 Reason :
3634 Issue is happening if the concurrent is running simultaneously.
3635 then the modifications for the second concurrent will be lost and will
3636 not be processed.
3637
3638
3639 process_manual_invoice will fetch the transactions from temp table (jai_ar_trx_ins_lines_t)
3640 and process the taxes to distributions and then flushout the temp table.
3641
3642 During this the second concurrent fires and will find no data in temp table and will not process
3643 the modified data.
3644 hence the issue.
3645
3646
3647
3648 fix:
3649 Modified the code in process_manual_invoice
3650 if maxreqid then
3651 delete temp table;
3652
3653 else
3654
3655 update temp table
3656 error_flag='D';
3657
3658 Temp table will be populated in trigger ra_cust_trx_lines_all.
3659 during insertion for temp table error_flag will be 'P' for auto import transactions
3660 for manual invoice error_flag is NULL. if we dont update the error_flag,while completion
3661 it will popup an error message saying il tax not applied...
3662 please wait for ar tax and freight defaultation concurrent...
3663 PROCESSED tax lines will be always flushed by the concurrent
3664 updating to 'D' will only happen with concurrents firing simultaneously for ct_trx_id..
3665 --------------------------------------------------------------------------------------------*/
3666
3667 v_sql_num := 0;
3668
3669 OPEN get_trx_num;
3670 FETCH get_trx_num INTO v_trx_num;
3671 CLOSE get_trx_num;
3672
3673 l_retcode := 0;
3674 v_sql_num :=1;
3675
3676 open prev_customer_trx_cur('LINE');--rchandan for bug#4428980
3677 fetch prev_customer_trx_cur into v_temp_cust_trx_id;
3678 close prev_customer_trx_cur;
3679 v_sql_num :=2;
3680 OPEN ORG_CUR_UPD;
3681 FETCH ORG_CUR_UPD INTO v_upd_created_from,ln_exchange_rate;
3682 CLOSE ORG_CUR_UPD;
3683 v_sql_num :=3;
3684
3685 OPEN check_rma_ref;
3686 FETCH check_rma_ref INTO v_rma_check;
3687 CLOSE check_rma_ref;
3688 v_sql_num :=4;
3689
3690 --12-MAR-2002
3691 OPEN get_reason;
3692 FETCH get_reason INTO v_reason_code;
3693 CLOSE get_reason;
3694 v_sql_num :=5;
3695
3696 OPEN payment_schedule_cur;
3697 FETCH payment_schedule_cur INTO v_payment_schedule_id;
3698 CLOSE payment_schedule_cur;
3699 v_sql_num :=6;
3700
3701 OPEN prev_customer_trx_cur('LINE');--rchandan for bug#4428980
3702 FETCH prev_customer_trx_cur INTO v_prev_customer_trx_id;
3703 CLOSE prev_customer_trx_cur;
3704 v_sql_num :=7;
3705
3706 BEGIN --RMA Block.This block is for processing localization taxes for the Credit memos of RMA
3707
3708 IF v_temp_cust_trx_id IS NOT NULL AND v_upd_created_from = 'RAXTRX' THEN
3709 -- AND v_reason_code = 'RETURN' THEN --commented for the BUG#2247013 as the reason_code can be anything.
3710 FOR tax_type_rec IN tax_type
3711 LOOP
3712 IF tax_type_rec.t_type <> 'Freight' THEN
3713 DELETE JAI_AR_TRX_INS_LINES_T
3714 WHERE customer_trx_id = P_CUSTOMER_TRX_ID
3715 and link_to_cust_trx_line_id = P_LINK_LINE_ID
3716 and customer_trx_line_id = tax_type_rec.line_id
3717 and tax_type_rec.t_type <> jai_constants.tax_type_freight;
3718 v_sql_num :=8;
3719 v_sql_count := SQL%ROWCOUNT;
3720 END IF;
3721 END LOOP;
3722 END IF;
3723 fnd_file.put_line(FND_FILE.LOG, 'Deletion in the RMA Blk...No. of rows deleted '|| v_sql_count);
3724 fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the Invoice..RMA Blk.. '|| v_trx_num);
3725
3726 EXCEPTION
3727 WHEN OTHERS THEN
3728 --retcode :=3;
3729 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN FOR RMA. Retcode = '|| retcode);
3730 fnd_file.put_line(FND_FILE.LOG, 'ERROR IN PROCESSING ..... ' || SQLERRM);
3731 END; --End block of RMA processing
3732 v_sql_num :=9;
3733
3734 --start additions for bug#9177024
3735 if v_upd_created_from ='ARXTWMAI'
3736 then
3737 delete_trx_data(p_customer_trx_id => p_customer_trx_id,
3738 p_link_to_cust_trx_line_id=>p_link_line_id,
3739 p_process_status => lv_process_status,
3740 p_process_message => lv_process_message);
3741
3742 --get the tax amount
3743 OPEN cur_total_amt_gl_dist(p_customer_trx_id);
3744 FETCH cur_total_amt_gl_dist INTO ln_tax_amt,
3745 ln_tax_acctd_amount;
3746 CLOSE cur_total_amt_gl_dist;
3747
3748 --get the line amount
3749 OPEN cur_total_amt_trx_lines( p_customer_trx_id,
3750 NULL,
3751 'LINE');
3752 FETCH cur_total_amt_trx_lines INTO ln_old_amount ;
3753 CLOSE cur_total_amt_trx_lines;
3754
3755
3756
3757
3758
3759 /* Modified for bug#5495711
3760 || acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount + ln_diff_tax_frt), ln_precision)
3761 */
3762 UPDATE ra_cust_trx_line_gl_dist_all
3763 SET amount = ln_old_amount + ln_tax_amt,
3764 acctd_amount = ROUND( ( ln_old_amount *ln_exchange_rate + ln_tax_acctd_amount ), ln_precision)
3765 WHERE customer_trx_id = p_customer_trx_id
3766 AND account_class = lv_account_class_rec --'REC'
3767 AND latest_rec_flag = 'Y';
3768
3769 end if;
3770 --end addtiions for bug#9177024
3771
3772
3773 SELECT NVL(SUM(AMOUNT),0) INTO V_sum_amt FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE
3774 ACCOUNT_CLASS = lv_acc_class_rev AND CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID; --added on 22-Mar-2002 to get the revenue amount for the invoice
3775 v_sql_num :=10;
3776
3777 OPEN COUNT_CUR;
3778 FETCH COUNT_CUR INTO v_counter;
3779 CLOSE COUNT_CUR;
3780 v_sql_num :=11;
3781
3782 IF NVL(v_counter,0) > 0 THEN --Main v_counter if
3783 FOR PREVIOUS_AMOUNT_REC IN PREVIOUS_AMOUNT_CUR
3784 LOOP
3785 v_old_amount := NVL(v_old_amount,0) + nvl(PREVIOUS_AMOUNT_REC.amount,0);
3786 v_old_acctd_amount := NVL(v_old_acctd_amount,0) + NVL(PREVIOUS_AMOUNT_REC.acctd_amount,0);
3787
3788 END LOOP;
3789 v_sql_num :=12;
3790
3791 FOR LINK_REC IN LINK_LINE_CUR
3792 LOOP
3793 v_sql_num :=13;
3794
3795
3796 -- the following select statement commented and using the next one instead
3797 -- because this is wrong.We need to compare the ra_customer_trx_lines_all table
3798 -- with the link_to_cust_trx_line_id column instead of based on the customer_trx_line_id
3799 -- column in the ra_cust_trx_line_gl_dist_all table. -- bug # 2784431
3800
3801 /*
3802 SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr FROM ra_cust_trx_line_gl_dist_all
3803 WHERE customer_trx_line_id = P_LINK_LINE_ID
3804 AND Account_class IN ('TAX','FREIGHT'); --Added on 09-Apr-2002 For the BUG#2303830
3805 */
3806
3807 SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr
3808 FROM ra_customer_trx_lines_all
3809 where link_to_cust_trx_line_id = p_link_line_id
3810 and line_type in (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
3811
3812
3813 IF v_rec_ctr > 0 THEN --Added on 09-Apr-2002 for the BUG#2303830
3814
3815 v_sql_num :=14;
3816
3817 DELETE RA_CUST_TRX_LINE_GL_DIST_ALL
3818 WHERE CUSTOMER_TRX_LINE_ID IN (SELECT CUSTOMER_TRX_LINE_ID
3819 FROM RA_CUSTOMER_TRX_LINES_ALL
3820 WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID)
3821 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
3822
3823 -- added for mrc -- sriram - 26/12 -- 3326394
3824
3825 for mrc_rec in
3826 (
3827 SELECT CUST_TRX_LINE_GL_DIST_ID
3828 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
3829 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3830 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
3831 AND CUSTOMER_TRX_LINE_ID
3832 IN
3833 (SELECT CUSTOMER_TRX_LINE_ID
3834 FROM RA_CUSTOMER_TRX_LINES_ALL
3835 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3836 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
3837 AND LINE_TYPE IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
3838 )
3839 )
3840 Loop
3841 ar_mrc_engine.maintain_mrc_data(
3842 p_event_mode => 'DELETE',
3843 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3844 p_mode => 'SINGLE',
3845 p_key_value => mrc_rec.CUST_TRX_LINE_GL_DIST_ID
3846 );
3847
3848 end loop;
3849
3850 /*
3851 DELETE RA_MC_TRX_LINE_GL_DIST
3852 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3853 AND CUST_TRX_LINE_GL_DIST_ID IN
3854 (
3855 SELECT CUST_TRX_LINE_GL_DIST_ID
3856 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
3857 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3858 AND ACCOUNT_CLASS IN ('TAX','FREIGHT')
3859 AND CUSTOMER_TRX_LINE_ID
3860 IN
3861 (SELECT CUSTOMER_TRX_LINE_ID
3862 FROM RA_CUSTOMER_TRX_LINES_ALL
3863 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
3864 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
3865 AND LINE_TYPE IN ('TAX','FREIGHT')
3866 )
3867 )
3868 AND ACCOUNT_CLASS IN ('TAX','FREIGHT');
3869
3870 */
3871
3872 v_sql_num :=15;
3873
3874 DELETE RA_CUSTOMER_TRX_LINES_ALL
3875 WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID;
3876 v_sql_num :=16;
3877
3878 Update Ar_Payment_Schedules_All
3879 Set Tax_Original = 0,
3880 Tax_remaining = 0,
3881 Freight_Original = 0,
3882 Freight_remaining = 0,
3883 Amount_Due_Original = v_sum_amt,
3884 Amount_Due_remaining = v_sum_amt,
3885 Acctd_amount_due_remaining = v_sum_amt
3886 Where Customer_Trx_ID = p_customer_trx_id
3887 And Payment_Schedule_ID = v_payment_schedule_id;
3888
3889 END IF;
3890
3891 v_sql_num :=17;
3892
3893 END LOOP;
3894
3895
3896 OPEN ORG_CUR;
3897 FETCH ORG_CUR INTO V_ORG_ID, V_CREATED_FROM;
3898 CLOSE ORG_CUR;
3899 v_sql_num :=18;
3900
3901 OPEN GL_DATE_CUR;
3902 FETCH GL_DATE_CUR INTO v_gl_date;
3903 CLOSE GL_DATE_CUR;
3904 v_sql_num :=19;
3905
3906 /* Commented by Ramananda for bug#4468353 due to ebtax uptake by AR */
3907 --2001/06/26 Anuradha Parthasarathy
3908 /*
3909 OPEN VAT_TAX_ID_CUR(V_ORG_ID,'Localization');--rchandan for bug#4428980
3910 FETCH VAT_TAX_ID_CUR INTO v_vat_tax_id;
3911 CLOSE VAT_TAX_ID_CUR;
3912
3913 if v_vat_tax_id is null then
3914 raise Localization_tax_not_defined;
3915 end if;
3916 */
3917
3918 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
3919 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
3920 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
3921 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
3922
3923 OPEN jai_ar_trx_pkg.c_party_tax_profile_id_cur(V_ORG_ID);
3924 FETCH jai_ar_trx_pkg.c_party_tax_profile_id_cur INTO ln_party_tax_profile_id;
3925 CLOSE jai_ar_trx_pkg.c_party_tax_profile_id_cur ;
3926
3927 OPEN jai_ar_trx_pkg.c_tax_rate_id_cur(lv_tax_regime_code, ln_party_tax_profile_id);
3928 FETCH jai_ar_trx_pkg.c_tax_rate_id_cur INTO ln_tax_rate_id;
3929 CLOSE jai_ar_trx_pkg.c_tax_rate_id_cur ;
3930
3931 if ln_tax_rate_id is null then
3932 raise Localization_tax_not_defined;
3933 end if;
3934 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
3935
3936 v_sql_num :=20;
3937
3938 OPEN ACCOUNTING_SET_CUR;
3939 FETCH ACCOUNTING_SET_CUR INTO v_accounting_rule_id;
3940 CLOSE ACCOUNTING_SET_CUR;
3941 v_sql_num :=21;
3942
3943 IF v_accounting_rule_id IS NOT NULL THEN
3944 v_account_Set_flag := 'Y';
3945 ELSE
3946 v_account_Set_flag := 'N';
3947 END IF;
3948
3949
3950 BEGIN --Begin Temp_Cur Block
3951
3952 SAVEPOINT TEMP_CUR_BLK_SVP;
3953
3954
3955 FOR TEMP_REC IN TEMP_CUR
3956 LOOP
3957
3958 --added for bug#8476512,start
3959 OPEN cur_chk_jai_tax_dtls( temp_rec.customer_trx_line_id,
3960 temp_rec.link_to_cust_trx_line_id);
3961 FETCH cur_chk_jai_tax_dtls INTO ln_tax_line_exists;
3962 CLOSE cur_chk_jai_tax_dtls;
3963
3964 IF nvl(ln_tax_line_exists,0) <> 1 THEN
3965 Delete JAI_AR_TRX_INS_LINES_T
3966 WHERE customer_trx_line_id = temp_rec.customer_trx_line_id
3967 AND link_to_cust_trx_line_id = temp_rec.link_to_cust_trx_line_id;
3968 ELSE
3969 --End bug#8476512
3970 OPEN MAX_LINE_CUR(TEMP_REC.LINK_TO_CUST_TRX_LINE_ID, TEMP_REC.line_type);
3971 FETCH MAX_LINE_CUR INTO v_line_no;
3972 CLOSE MAX_LINE_CUR;
3973 v_sql_num :=22;
3974
3975 v_line_no := NVL(v_line_no,0) + 1;
3976
3977 IF TEMP_REC.INSERT_UPDATE_FLAG IN('U','X') THEN
3978 v_sql_num :=23;
3979
3980 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, start */
3981 OPEN c_gl_posted_date_cur( TEMP_REC.link_to_cust_trx_line_id ) ;
3982 FETCH c_gl_posted_date_cur INTO ld_gl_posted_date ;
3983 CLOSE c_gl_posted_date_cur ;
3984
3985 IF ld_gl_posted_date is NULL THEN
3986
3987 INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
3988 customer_trx_line_id,
3989 last_update_date,
3990 last_updated_by,
3991 creation_date,
3992 created_by,
3993 last_update_login,
3994 customer_trx_id,
3995 line_number,
3996 set_of_books_id,
3997 link_to_cust_trx_line_id,
3998 line_type,
3999 org_id,
4000 uom_code,
4001 autotax,
4002 vat_tax_id)
4003 VALUES ( TEMP_REC.extended_amount,
4004 TEMP_REC.customer_trx_line_id,
4005 TEMP_REC.last_update_date,
4006 TEMP_REC.last_updated_by,
4007 TEMP_REC.creation_date,
4008 TEMP_REC.created_by,
4009 TEMP_REC.last_update_login,
4010 TEMP_REC.customer_trx_id,
4011 v_line_no,
4012 TEMP_REC.set_of_books_id,
4013 TEMP_REC.link_to_cust_trx_line_id,
4014 TEMP_REC.line_type,
4015 v_org_id,
4016 TEMP_REC.uom_code,
4017 'N',
4018 v_vat_tax_id);
4019 v_sql_num :=24;
4020
4021 INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
4022 account_set_flag,
4023 acctd_amount,
4024 amount,
4025 code_combination_id,
4026 cust_trx_line_gl_dist_id,
4027 cust_trx_line_salesrep_id,
4028 customer_trx_id,
4029 customer_trx_line_id,
4030 gl_date,
4031 last_update_date,
4032 last_updated_by,
4033 creation_date,
4034 created_by,
4035 last_update_login,
4036 org_id,
4037 percent,
4038 posting_control_id,
4039 set_of_books_id )
4040 VALUES( TEMP_REC.line_type,
4041 v_account_set_flag,
4042 TEMP_REC.acctd_amount,
4043 TEMP_REC.amount,
4044 TEMP_REC.CODE_COMBINATION_ID,
4045 RA_CUST_TRX_LINE_GL_DIST_S.nextval,
4046 TEMP_REC.cust_trx_line_sales_rep_id,
4047 TEMP_REC.customer_trx_id,
4048 TEMP_REC.customer_trx_line_id,
4049 v_gl_date,
4050 TEMP_REC.last_update_date,
4051 TEMP_REC.last_updated_by,
4052 TEMP_REC.creation_date,
4053 TEMP_REC.created_by,
4054 TEMP_REC.last_update_login,
4055 v_org_id,
4056 100,
4057 -3,
4058 TEMP_REC.set_of_books_id ) RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
4059
4060 /* SLA Impact uptake */
4061 --l_xla_event.xla_from_doc_id ;
4062 --l_xla_event.xla_to_doc_id ;
4063 l_xla_event.xla_req_id := NULL ;
4064 l_xla_event.xla_dist_id := v_gl_dist_id ;
4065 l_xla_event.xla_doc_table := 'CT' ;
4066 l_xla_event.xla_doc_event := NULL ;
4067 l_xla_event.xla_mode := 'O' ;
4068 l_xla_event.xla_call := 'D' ;
4069 --l_xla_event.xla_fetch_size
4070
4071 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event);
4072
4073
4074 -- code for mrc insert starts here -- bug # 3326394
4075 ar_mrc_engine.maintain_mrc_data(
4076 p_event_mode => 'INSERT',
4077 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
4078 p_mode => 'SINGLE',
4079 p_key_value => v_gl_dist_id);
4080
4081 -- code for mrc ends here -- bug # 3326394
4082
4083 v_sql_num :=25;
4084
4085 ELSE /* v_gl_posted_date will not be null when the execution comes here */
4086 raise Item_lines_already_accounted;
4087 END IF ; --v_gl_posted_date is null
4088 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, end */
4089
4090 ELSE
4091 UPDATE RA_CUSTOMER_TRX_LINES_ALL
4092 SET EXTENDED_AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
4093 LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
4094 LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
4095 CREATION_DATE = TEMP_REC.CREATION_DATE,
4096 CREATED_BY = TEMP_REC.CREATED_BY,
4097 LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
4098 WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
4099 v_sql_num :=26;
4100
4101 UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
4102 SET ACCTD_AMOUNT = TEMP_REC.ACCTD_AMOUNT,
4103 AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
4104 LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
4105 LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
4106 CREATION_DATE = TEMP_REC.CREATION_DATE,
4107 CREATED_BY = TEMP_REC.CREATED_BY,
4108 LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
4109 WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
4110 v_sql_num :=27;
4111
4112 END IF;
4113
4114 IF TEMP_REC.LINE_TYPE = 'TAX' THEN
4115 v_tax_amount := nvl(v_tax_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
4116
4117 ELSIF TEMP_REC.LINE_TYPE = 'FREIGHT' THEN
4118 v_freight_amount := nvl(v_freight_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
4119
4120 END IF;
4121
4122 v_receivable_amount := nvl(v_receivable_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
4123 v_receivable_acctd_amount := nvl(v_receivable_acctd_amount,0) + nvl(TEMP_REC.ACCTD_AMOUNT,0);
4124 END IF; -- 8476512 IF nvl(ln_tax_line_exists,0) <> 1 THEN
4125 END LOOP;
4126
4127 v_sql_num :=28;
4128
4129 Select SUM(amount),SUM(acctd_amount) into v_old_amount,v_old_acctd_amount --Added this stmt for the above stmt
4130 From RA_CUST_TRX_LINE_GL_DIST_ALL
4131 Where customer_trx_id = P_CUSTOMER_TRX_ID
4132 AND ACCOUNT_CLASS = lv_acc_class_rev; --rchandan for bug#4428980
4133 v_sql_num :=29;
4134
4135 Select SUM(amount) INTO v_tax_amt
4136 From RA_CUST_TRX_LINE_GL_DIST_ALL
4137 Where customer_trx_id = P_CUSTOMER_TRX_ID
4138 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
4139 v_sql_num :=30;
4140
4141 UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
4142 SET AMOUNT = NVL(v_old_amount,0) + NVL(v_tax_amt,0),
4143 ACCTD_AMOUNT = NVL(v_old_acctd_amount,0) + NVL(v_tax_amt,0)
4144 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID AND
4145 ACCOUNT_CLASS = lv_acc_class_rec; --rchandan for bug#4428980
4146
4147 -- mrc update for gl dist - bug # 3326394
4148 open c_gl_dist_cur;
4149 fetch c_gl_dist_cur into v_gl_dist_id;
4150 close c_gl_dist_cur;
4151
4152
4153 ar_mrc_engine.maintain_mrc_data(
4154 p_event_mode => 'UPDATE',
4155 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
4156 p_mode => 'SINGLE',
4157 p_key_value => v_gl_dist_id);
4158
4159
4160
4161 v_amt_a := NVL(v_old_amount,0) + NVL(v_tax_amt,0);
4162
4163 v_sql_num :=31;
4164
4165 --22-MAR-2002 FOR PROPER UPDATION
4166
4167 If v_prev_customer_trx_id is null then
4168
4169 v_sql_num :=32;
4170
4171 Update Ar_Payment_Schedules_All
4172 Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
4173 Tax_remaining = NVL(Tax_remaining,0) + NVL(v_tax_amount,0),
4174 Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
4175 Freight_remaining = NVL(Freight_remaining,0) + NVL(v_freight_amount,0),
4176 Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0),
4177 Amount_Due_remaining = NVL(Amount_Due_remaining,0) + NVL(v_receivable_amount,0),
4178 Acctd_amount_due_remaining = NVL(Acctd_amount_due_remaining,0) + NVL(v_receivable_acctd_amount,0)
4179 Where Customer_Trx_ID = p_customer_trx_id
4180 And Payment_Schedule_ID = v_payment_schedule_id;
4181
4182 fnd_file.put_line(FND_FILE.LOG, 'TAX ORIGINAL 1.......' || v_trx_num || 'is ' || NVL(v_tax_amount,-111));
4183 fnd_file.put_line(FND_FILE.LOG, 'TAX --|AINING 1......' || v_trx_num || 'is ' || NVL(v_tax_amount,-111));
4184 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT ORIGINAL 1...' || v_trx_num || 'is ' || NVL(v_freight_amount,-77));
4185 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT --|AINING 1..' || v_trx_num || 'is ' || NVL(v_freight_amount,-66));
4186 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE --|AINING 1...' || v_trx_num || 'is ' || NVL(v_receivable_amount,-222));
4187 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE ORIGINAL 1...' || v_trx_num || 'is ' || NVL(v_receivable_amount,333));
4188 fnd_file.put_line(FND_FILE.LOG, 'ACCTD AMOUNT DUE 1...' || v_trx_num || 'is ' || NVL(v_receivable_acctd_amount,444));
4189
4190 V_TEMP := NVL(v_old_amount,0) + NVL(v_tax_amt,0);
4191
4192 fnd_file.put_line(FND_FILE.LOG, 'TAX ORIGINAL 2.......' || v_trx_num || 'is ' || NVL(v_PAYMENT_amt,-111));
4193 fnd_file.put_line(FND_FILE.LOG, 'TAX --|AINING 2......' || v_trx_num || 'is ' || NVL(v_PAYMENT_amt,-111));
4194 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT ORIGINAL 2...' || v_trx_num || 'is ' || NVL(v_FREIGHT_amt,-77));
4195 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT --|AINING 2...' || v_trx_num || 'is ' || NVL(v_FREIGHT_amt,-66));
4196 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE --|AINING 2...' || v_trx_num || 'is ' || V_TEMP);
4197 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE ORIGINAL 2...' || v_trx_num || 'is ' || V_TEMP);
4198 fnd_file.put_line(FND_FILE.LOG, 'ACCTD AMOUNT DUE 2...' || v_trx_num || 'is ' || NVL(v_receivable_acctd_amount,444));
4199
4200 end if; --END 22-MAR-02 FOR PROPER UPDATION
4201
4202 v_sql_num :=33;
4203
4204 /*added for bug#12589786*/
4205 DELETE JAI_AR_TRX_INS_LINES_T
4206 WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
4207 link_to_cust_trx_line_id = P_LINK_LINE_ID;
4208 v_sql_num :=34;
4209
4210
4211 ERRBUF :=SQLERRM;
4212 -- retcode := 0;
4213 fnd_file.put_line(FND_FILE.LOG, 'The total tax amount for the line is....' || v_tax_amt);
4214 fnd_file.put_line(FND_FILE.LOG, 'The receivable amount for the line is....' || v_receivable_amount);
4215 fnd_file.put_line(FND_FILE.LOG, 'Successfully Processed the Invoice... '|| v_trx_num);
4216 -- fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the Invoice Retcode = '|| retcode);
4217
4218 EXCEPTION
4219 -- retcode := 5;
4220 when others then
4221 ERRBUF :=SUBSTR(SQLERRM,1,230);
4222 ROLLBACK TO TEMP_CUR_BLK_SVP;
4223
4224 UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG = ERRBUF WHERE
4225 CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
4226 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
4227
4228 COMMIT;
4229
4230 -- fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
4231 fnd_file.put_line(FND_FILE.LOG, 'Updated the customer_trx_id error_flag to ...' || 'R');
4232 fnd_file.put_line(FND_FILE.LOG, 'Unable to Process the invoice... '|| v_trx_num);
4233 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM);
4234 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM || v_sql_num);
4235
4236
4237 END; --End Temp_Cur Block
4238
4239
4240 IF v_created_from = 'RAXTRX' THEN
4241
4242 If v_prev_customer_trx_id is NOT null THEN --added on 22-Mar-2002
4243
4244 Open line_id_cur('LINE');--rchandan for bug#4428980
4245 Fetch line_id_cur into v_interface_line_attribute6;
4246 Close line_id_cur;
4247
4248 Open Ref_type_cur(v_interface_line_attribute6);
4249 Fetch Ref_type_cur into v_return_reference_type_code,v_credit_invoice_line_id;
4250 Close Ref_type_cur;
4251
4252 --2001/07/04 Anuradha Parthasarathy
4253
4254 IF v_return_reference_type_code = 'Sales Order India' and v_credit_invoice_line_id IS NULL THEN
4255 Update Ar_Payment_Schedules_All
4256 Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
4257 Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
4258 Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0)
4259 Where Customer_Trx_ID = p_customer_trx_id
4260 And Payment_Schedule_ID = v_payment_schedule_id;
4261 fnd_file.put_line(FND_FILE.LOG, ' DEBUG:SPATIAL) checking Tax Details updating updating v_return_reference_type_code ' || v_return_reference_type_code );
4262 ELSIF v_return_reference_type_code in ('Invoice India','Sales Order India')
4263 and v_credit_invoice_line_id IS NOT NULL THEN
4264
4265 Select sum(amount) INTO v_tax_amount1
4266 FROM ra_cust_trx_line_gl_dist_all
4267 Where customer_trx_id = p_customer_trx_id
4268 And Account_class = lv_tax_const; --rchandan for bug#4428980
4269
4270 Select sum(amount) INTO v_freight_amount1
4271 FROM ra_cust_trx_line_gl_dist_all
4272 Where customer_trx_id = p_customer_trx_id
4273 And Account_class = lv_freight_acc_class; --rchandan for bug#4428980
4274
4275 SELECT SUM(AMOUNT) INTO v_tot_amount
4276 FROM ra_cust_trx_line_gl_dist_all
4277 WHERE customer_trx_id = p_customer_trx_id
4278 AND account_class = lv_acc_class_rec; --rchandan for bug#4428980
4279
4280
4281 Update Ar_Payment_Schedules_All
4282 Set Tax_Original = NVL(v_tax_amount1,0),
4283 Freight_Original = NVL(v_freight_amount1,0),
4284 Amount_Due_Original = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0),
4285 Amount_Applied = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0)
4286 Where Customer_Trx_ID = p_customer_trx_id
4287 And Payment_Schedule_ID = v_payment_schedule_id;
4288
4289
4290 --In the below statement only the freight amount is getting updated to all the columns,because the tax amount is
4291 --automatcally updated by the base apps product
4292
4293 Update Ar_Receivable_Applications_All
4294 Set Amount_Applied = NVL(Amount_Applied,0) - (NVL(v_freight_amount1,0)),
4295 --Tax_Applied = NVL(Tax_Applied,0) - NVL(v_tax_amount1,0),
4296 Freight_Applied = NVL(Freight_Applied,0) - NVL(v_freight_amount1,0),
4297 Acctd_Amount_Applied_From = NVL(Acctd_Amount_Applied_From,0) - ( NVL(v_freight_amount1,0) ),
4298 Acctd_Amount_Applied_To = NVL(Acctd_Amount_Applied_To,0) - ( NVL(v_freight_amount1,0) )
4299 Where Customer_Trx_ID = p_customer_trx_id
4300 And Payment_Schedule_ID = v_payment_schedule_id; --20-Apr-2002
4301
4302 /* Updating Ar_Payment_Schedules for the Invoice against which this credit memo is applied */
4303
4304 OPEN Inv_payment_schedule_cur(v_prev_customer_trx_id);
4305 FETCH Inv_payment_schedule_cur into v_payment_schedule_id;
4306 CLOSE Inv_payment_schedule_cur;
4307
4308 Update Ar_Payment_Schedules_All
4309 Set --Tax_remaining = NVL(Tax_remaining,0) - NVL(v_tax_amount1,0),
4310 Freight_remaining = NVL(Freight_remaining,0) + NVL(v_freight_amount1,0),
4311 Amount_Due_remaining = NVL(Amount_Due_remaining,0) + NVL(v_freight_amount1,0),
4312 Amount_Credited = NVL(Amount_Credited,0) + NVL(v_freight_amount1,0),
4313 Acctd_Amount_Due_remaining = NVL(Acctd_Amount_Due_remaining,0) + NVL(v_freight_amount1,0)
4314 Where Customer_Trx_Id = v_prev_customer_trx_id
4315 And Payment_Schedule_Id = v_payment_Schedule_id; --18-apr-2002
4316
4317 fnd_file.put_line(FND_FILE.LOG, 'v_tot_amount = '|| v_tot_amount);
4318 fnd_file.put_line(FND_FILE.LOG, 'v_tax_amount1 = '|| v_tax_amount1);
4319 fnd_file.put_line(FND_FILE.LOG, 'v_freight_amount1 = '|| v_freight_amount1);
4320 fnd_file.put_line(FND_FILE.LOG, ' DEBUG:SPATIAL) checking Tax Details updating updating v_return_reference_type_code ' || v_return_reference_type_code );
4321 END IF;
4322
4323 END IF;
4324
4325 END IF;
4326
4327 END IF; --End Main v_counter if
4328
4329 ------------------------------------------------------------------------------------------------
4330 -- Start modifications by subbu and Jagdish on 10-jun-01 for receipt discount issue.
4331 OPEN get_ext_amt_ln('LINE');--rchandan for bug#4428980
4332 FETCH get_ext_amt_ln INTO v_extended_amount_line;
4333 CLOSE get_ext_amt_ln;
4334
4335 OPEN get_ext_amt_tax ;
4336 LOOP
4337 FETCH get_ext_amt_tax INTO get_ext_amt_tax_rec;
4338 EXIT WHEN get_ext_amt_tax%NOTFOUND;
4339 OPEN get_taxable_amt(get_ext_amt_tax_rec.customer_trx_line_id);
4340 FETCH get_taxable_amt INTO v_taxable_amt;
4341 IF v_taxable_amt = 0 THEN
4342 UPDATE ra_customer_trx_lines_all
4343 SET Taxable_amount = (v_extended_amount_line - get_ext_amt_tax_rec.extended_amount)
4344 WHERE Customer_trx_line_id = get_ext_amt_tax_rec.customer_trx_line_id
4345 and customer_trx_id = P_CUSTOMER_TRX_ID
4346 and link_to_cust_trx_line_id = P_LINK_LINE_ID
4347 and Line_type = lv_tax_const;
4348 END IF;
4349 CLOSE get_taxable_amt;
4350 END LOOP;
4351 CLOSE get_ext_amt_tax;
4352 -- end modifications by subbu and Jagdish on 10-jun-01 for receipt discount issue.
4353 ------------------------------------------------------------------------------------------------
4354 /*Bug 11936630 - Delete the Inclusive Tax Lines inserted into JAI_AR_TRX_INS_LINES_T*/
4355 DELETE JAI_AR_TRX_INS_LINES_T jatilt
4356 WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
4357 link_to_cust_trx_line_id = p_link_line_id
4358 AND EXISTS (select '1'
4359 from JAI_AR_TRX_TAX_LINES jattl
4360 where jattl.link_to_cust_trx_line_id = jatilt.link_to_cust_trx_line_id
4361 and jattl.customer_trx_line_id = jatilt.customer_trx_line_id
4362 and exists (select '1'
4363 from JAI_CMN_TAXES_ALL
4364 where jattl.tax_id = tax_id
4365 and NVL(inclusive_tax_flag,'N') = 'Y'));
4366 ERRBUF := SQLERRM;
4367 -- retcode := 2;
4368 v_err_mesg := ERRBUF;
4369
4370 --Added by Bo Li for Bug9803555 on 01-JUL-2010 ,Begin
4371 --------------------------------------------------------------------------
4372 OPEN get_max_request_id_cur(p_customer_trx_id, p_link_line_id);
4373 FETCH get_max_request_id_cur
4374 INTO ln_max_req_id;
4375 CLOSE get_max_request_id_cur;
4376
4377 IF ln_max_req_id = fnd_global.conc_request_id
4378 THEN
4379 COMMIT;
4380 ELSE
4381 ROLLBACK;
4382 End IF;
4383 --------------------------------------------------------------------------
4384 --Added by Bo Li for Bug9803555 on 01-JUL-2010 ,End
4385
4386 -- retcode := 0;
4387 fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the invoice = '|| v_trx_num);
4388 -- fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the customer_trx_id Retcode = '|| retcode);
4389
4390 EXCEPTION
4391
4392 when Localization_tax_not_defined then
4393 fnd_file.put_line(FND_FILE.LOG,' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ');
4394 errbuf:= ' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ';
4395 retcode := 2;
4396
4397 WHEN OTHERS THEN
4398
4399 ERRBUF :=SUBSTR(SQLERRM,1,230);
4400 UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG=ERRBUF WHERE CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
4401 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
4402 COMMIT;
4403 -- retcode := 7;
4404 -- fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
4405 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the invoice = '|| v_trx_num);
4406 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM);
4407 fnd_file.put_line(FND_FILE.LOG, 'Main Block.... the err = '|| SQLERRM);
4408 fnd_file.put_line(FND_FILE.LOG, 'Please Contact the System Administrator Or Oracle Software Support Services...');
4409
4410 END process_manual_invoice;
4411 -- Start commented by kunkumar for bug#6066813
4412 -- following function added for bug#6012570 (5876390) --> revoked the comments, 6012570
4413 function is_this_projects_context(pv_context in varchar2) return varchar2 is
4414 begin
4415 if jai_ar_rctla_trigger_pkg.is_this_projects_context(pv_context) then
4416 return jai_constants.yes;
4417 else
4418 return jai_constants.no;
4419 end if;
4420 end is_this_projects_context;
4421 -- End commented by kunkumar for bug#6066813*/, revoked the comments, 6012570
4422
4423
4424 -- Added by Jia Li on tax inclusive computation on 2007/11/30, Begin
4425 --==========================================================================
4426 -- FUNCTION NAME:
4427 --
4428 -- get_tax_account_id Private
4429 --
4430 -- DESCRIPTION:
4431 --
4432 -- This function is get tax account ccid
4433 --
4434 -- PARAMETERS:
4435 -- In: pn_tax_id
4436 -- pn_tax_type
4437 -- pn_org_id
4438 --
4439 -- DESIGN REFERENCES:
4440 -- Inclusive Tax Technical Design V1.4.doc
4441 --
4442 -- CHANGE HISTORY:
4443 --
4444 -- 20-DEC-2007 Jia Li created
4445 -- 20-Apr-2009 Jia Li Modified for fixed bug#7205349
4446 -- Issue: VAT accounting is not proper for manual ar transaction
4447 -- when inclusive VAT taxes are used.
4448 -- Fixed: Use VAT Interim Liability account to replace VAT Interim recovery account
4449 -- 30-Apr-2009 Jia Li Modified for fixed bug#8474445
4450 -- Issue: VAT accounting is not proper for manual ar transaction
4451 -- when inclusive VAT taxes are used.
4452 -- Fixed: Used VAT Liability account to replace VAT Interim Liability account.
4453 -- 04-JUN-2010 Bo Li Modified for fixed bug#9771955
4454 -- Issue: VAT accounting is not proper for ORDER ENTRY
4455 -- when inclusive VAT taxes are used.
4456 -- Fixed: Used VAT Interim Liability account when the AR transation has
4457 -- generated by autoinvoice
4458
4459 --==========================================================================
4460 FUNCTION get_tax_account_id
4461 ( pn_tax_id IN NUMBER
4462 , pv_tax_type IN VARCHAR2
4463 , pn_org_id IN NUMBER
4464 )
4465 RETURN NUMBER
4466 IS
4467 ln_tax_def_acc_id NUMBER;
4468 ln_tax_rgm_acc_id NUMBER;
4469 ln_tax_acc_id NUMBER;
4470 lv_procedure_name VARCHAR2(40):='get_tax_account_id';
4471 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4472 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
4473
4474 BEGIN
4475 --logging for debug
4476 IF (ln_proc_level >= ln_dbg_level)
4477 THEN
4478 FND_LOG.STRING( ln_proc_level
4479 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
4480 , 'Enter procedure'
4481 );
4482 FND_LOG.STRING( ln_proc_level
4483 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
4484 , 'Org_id = ' || pn_org_id
4485 );
4486 FND_LOG.STRING( ln_proc_level
4487 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
4488 , 'Tax_id = '|| pn_tax_id ||' Tax_type = ' || pv_tax_type
4489 );
4490 END IF; --ln_proc_level>=l_dbg_level
4491
4492 -- Get tax_account_id from tax defination
4493 BEGIN
4494 SELECT
4495 tax_account_id
4496 INTO
4497 ln_tax_def_acc_id
4498 FROM
4499 jai_cmn_taxes_all
4500 WHERE tax_id = pn_tax_id
4501 AND org_id = pn_org_id;
4502 EXCEPTION
4503 WHEN OTHERS THEN
4504 ln_tax_def_acc_id := -1;
4505 END;
4506
4507 -- Get tax_account_id from rgm setup for SERVICE and VAT tax.
4508 BEGIN
4509 SELECT
4510 TO_NUMBER(acc_rgm.attribute_value)
4511 INTO
4512 ln_tax_rgm_acc_id
4513 FROM
4514 jai_rgm_definitions rgm_def
4515 , jai_rgm_registrations tax_rgm
4516 , jai_rgm_registrations acc_rgm
4517 WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
4518 AND tax_rgm.regime_id = rgm_def.regime_id
4519 AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
4520 AND tax_rgm.attribute_code = pv_tax_type
4521 AND tax_rgm.regime_id = acc_rgm.regime_id
4522 AND acc_rgm.registration_type = jai_constants.regn_type_accounts
4523 --AND acc_rgm.attribute_code = jai_constants.recovery_interim -- --|oved by Jia for fixed bug#7205349 on 20-Apr-2009
4524 -- AND acc_rgm.attribute_code = jai_constants.liability -- Modified by Jia for fixed bug#8474445 on 30-Apr-2009, use liability account to replace liability interim account
4525
4526 AND acc_rgm.attribute_code = jai_constants.liability_interim --Added by Bo Li for bug#9771955 on 2010-06-04
4527 AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
4528
4529 ln_tax_acc_id := ln_tax_rgm_acc_id;
4530 EXCEPTION
4531 WHEN no_data_found THEN
4532 ln_tax_acc_id := ln_tax_def_acc_id;
4533 WHEN OTHERS THEN
4534 ln_tax_acc_id := -1;
4535 END;
4536
4537 IF (ln_proc_level >= ln_dbg_level)
4538 THEN
4539 FND_LOG.STRING( ln_proc_level
4540 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.result'
4541 , 'Tax Account ID = ' || ln_tax_acc_id
4542 );
4543 FND_LOG.STRING( ln_proc_level
4544 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
4545 , 'Enter procedure'
4546 );
4547 END IF; -- ln_proc_level >= ln_dbg_level
4548
4549 RETURN ln_tax_acc_id;
4550
4551 END get_tax_account_id;
4552
4553 /*Bug 11821537 - Start*/
4554 FUNCTION get_transaction_date(pn_customer_trx_id IN NUMBER)
4555 RETURN DATE
4556 IS
4557 PRAGMA AUTONOMOUS_TRANSACTION;
4558 ld_trx_date DATE;
4559 CURSOR c_get_trx_date
4560 IS
4561 --Commented by Chong.Lei for POT code port begin
4562 /*
4563 SELECT trx_date
4564 FROM ra_customer_trx_all
4565 WHERE customer_trx_id = pn_customer_trx_id;
4566 */
4567 --Commented by Chong.Lei for POT code port end
4568 --Added by Chong.Lei for POT code port begin
4569 ------------------------------------------------------------------------------------
4570 --Modified by Xiao for POT changes, reg bug#12533434
4571 SELECT gl_date
4572 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
4573 WHERE customer_trx_id = pn_customer_trx_id
4574 AND account_class = 'REV'
4575 AND ROWNUM = 1;
4576 --Modified by Xiao for POT changes, reg bug#12533434
4577 ------------------------------------------------------------------------------------
4578 --Added by Chong.Lei for POT code port end
4579 BEGIN
4580 OPEN c_get_trx_date;
4581 FETCH c_get_trx_date INTO ld_trx_date;
4582 CLOSE c_get_trx_date;
4583 RETURN ld_trx_date;
4584 END;
4585 /*Bug 11821537 - End*/
4586
4587 --==========================================================================
4588 -- PROCEDURE NAME:
4589 --
4590 -- acct_inclu_taxes Public
4591 --
4592 -- DESCRIPTION:
4593 --
4594 -- This procedure is written that whould pass GL entries for inclusive taxes in GL interface
4595 --
4596 -- PARAMETERS:
4597 -- In: pn_customer_trx_id Indicates the customer trx id
4598 -- pn_org_id Indicates the transaction org id
4599 -- pn_cust_trx_type_id Indicates the custormer trx tye id
4600 -- OUt: xv_process_flag Indicates the process flag, 'SS' for success
4601 -- 'EE' for expected error
4602 -- 'UE' for unexpected error
4603 -- xv_process_message Indicates the process message
4604 --
4605 --
4606 -- DESIGN REFERENCES:
4607 -- Inclusive Tax Technical Design V1.4.doc
4608 --
4609 -- CHANGE HISTORY:
4610 --
4611 -- 30-NOV-2007 Jia Li created
4612 -- 30-Apr-2009 Jia Li Modified for fixed bug#8474445
4613 -- Issue: VAT accounting is not proper for manual ar transaction
4614 -- when inclusive VAT taxes are used.
4615 -- Fixed: Used Revenue account to replace Receivables account for Inclusive Dr Accounting
4616 --
4617 -- 01-Mar-2011 Xiao Lv Modified for Open Interface ER bug#11683927.
4618 -- 1. When 'External' event, accounting for inclusive tax to gl interface,
4619 -- ccid will be be directly fetched from jai_interface_lines.
4620 -- 2. Fetch ccid from tax definition for 'Excise' and other taxes.
4621 -- As the orginal code only handles accounting for taxes under 'Service'
4622 -- and 'VAT' regime.
4623 -- 17-Mar-2011 Enhancement 11821537
4624 -- Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
4625 -- Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
4626 -- i.e. Accounting changes to Accrual basis from Cash Basis
4627 -- Fix: Service Tax Libility Account will be hit directly instead of Interim Liablity as there
4628 -- will be no set off during Receipt/CM Application.
4629 -- 07-Apr-2011 Bug 11905163
4630 -- Description: Code combination ID is not derived from Tax Definition for Inclusive Tax
4631 -- Accounting for Excise and Adhoc Taxes
4632 -- Fix: If jai_cmn_rgm_Recording_pkg.get_account does not derive the Tax Account ID then the
4633 -- same shall be fetched from Tax Definition
4634 -- 08-Apr-2011 Xiao Lv Modified for bug#11936390.
4635 -- 1. Accounting for Open Interface ER is changed.
4636 -- For external, do not generate accounting for inclusive tax in GL Interface.
4637 -- 2. Fixed rounding issue for dr/cr when accounting is generated in GL.
4638 --
4639 -- 29-Sep-2011 Zhiwei Hou Modified for bug#13023443.
4640 -- 1. Accounting for Open Interface ER is changed for POT.
4641 -- 2. For External & Service % GL < POT should insert gl_interface, and accounting from setup.
4642 -- 20-Jun-2012 mmurtuza for bug 14194526
4643 -- Description: INDIA - LOCAL CONCURRENT FOR PROCESSING ORDER LINES TO AR
4644 -- Fix: Added logic to eliminate to_char(line_id) in get_rma_flag_cur and inclu_rma_tax
4645 --
4646 --
4647 --
4648 --==========================================================================
4649 PROCEDURE acct_inclu_taxes
4650 ( pn_customer_trx_id IN NUMBER
4651 , pn_org_id IN NUMBER
4652 , pn_cust_trx_type_id IN NUMBER
4653 , xv_process_flag OUT NOCOPY VARCHAR2
4654 , xv_process_message OUT NOCOPY VARCHAR2
4655 )
4656 IS
4657 ln_org_id ra_customer_trx_all.org_id%TYPE;
4658 ln_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
4659 lv_inv_num ra_customer_trx_all.trx_number%TYPE;
4660 ld_cur_conversion_date jai_ar_trxs.exchange_date%TYPE;
4661 lv_cur_conversion_type jai_ar_trxs.exchange_rate_type%TYPE;
4662 ln_cur_conversion_rate jai_ar_trxs.exchange_rate%TYPE;
4663 lv_currency_code jai_ar_trxs.invoice_currency_code%TYPE;
4664 ln_inv_org_id jai_ar_trxs.organization_id%TYPE;
4665 ln_inv_loc_id jai_ar_trxs.location_id%TYPE; --11652823
4666 lv_inv_org_code mtl_parameters.organization_code%TYPE;
4667 ln_rec_account_id ra_cust_trx_types_all.gl_id_rec%TYPE;
4668 ln_set_of_books_id ra_cust_trx_line_gl_dist_all.set_of_books_id%TYPE;
4669 ld_gl_date ra_cust_trx_line_gl_dist_all.gl_date%TYPE;
4670 ln_tax_account_id NUMBER;
4671 ln_total_inclu_tax_amt NUMBER;
4672 exception_error EXCEPTION;
4673
4674 CURSOR inclu_tax IS
4675 SELECT
4676 a.tax_id tax_id
4677 , b.tax_type tax_type
4678 , SUM(a.tax_amount) tax_amount
4679 , nvl(b.rounding_factor,0) rounding_factor --Add by Xiao for bug#11936390, for rounding issue.
4680 FROM
4681 jai_cmn_taxes_all b
4682 , jai_ar_trx_tax_lines a
4683 WHERE a.tax_id = b.tax_id
4684 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
4685 AND a.link_to_cust_trx_line_id IN (SELECT
4686 customer_trx_line_id
4687 FROM
4688 jai_ar_trx_lines
4689 WHERE customer_trx_id = pn_customer_trx_id)
4690 GROUP BY
4691 a.tax_id
4692 , b.tax_type
4693 , nvl(b.rounding_factor,0);--Added by zhiwei for bug#12433891 20110509;
4694
4695 lv_procedure_name VARCHAR2(40):='acct_inclu_taxes';
4696 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4697 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
4698
4699
4700 lv_acc_class_rev CONSTANT varchar2(10) := 'REV'; --bug#9461197
4701
4702 /*start additions by vkaranam for bug#9461197*/
4703 cursor c_rev_acc is
4704 select code_combination_id
4705 from ra_cust_trx_line_gl_dist_all
4706 where customer_trx_id = pn_customer_trx_id
4707 and account_class = lv_acc_class_rev;
4708
4709
4710 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 Begin
4711 -----------------------------------------------------------------------
4712 Cursor get_rma_flag_cur
4713 IS
4714 SELECT COUNT(customer_trx_line_id)
4715 FROM (SELECT rctl.customer_trx_line_id
4716 FROM OE_ORDER_HEADERS_ALL oh,
4717 OE_ORDER_LINES_ALL ol,
4718 OE_TRANSACTION_TYPES_TL ot,
4719 oe_workflow_assignments owf,
4720 ra_customer_trx_lines_all rctl
4721 WHERE oh.header_id = ol.header_id
4722 AND oh.order_type_id = ot.transaction_type_id
4723 AND oh.order_type_id = owf.order_type_id
4724 AND ol.line_type_id = owf.line_type_id
4725 AND ot.LANGUAGE = userenv('LANG')
4726 /* AND to_char(ol.line_id) = rctl.interface_line_attribute6 */ /*Commented and added below by mmurtuza for bug 14194526*/
4727 AND ol.line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
4728 NULL, rctl.interface_line_attribute6, NULL)
4729 AND owf.process_name IN
4730 ('R_RMA_CREDIT_APP_HDR_INV', 'R_RMA_CREDIT_WO_SHIP_APPROVE',
4731 'R_RMA_CREDIT_WO_SHIP_HDR_INV',
4732 'R_RMA_FOR_CREDIT_WO_SHIPMENT', 'R_RMA_FOR_OTA_CREDIT')
4733 AND rctl.customer_trx_id = pn_customer_trx_id
4734 UNION
4735 SELECT rctl.customer_trx_line_id
4736 FROM MTL_SYSTEM_ITEMS msi,
4737 JAI_OM_OE_RMA_LINES l,
4738 ra_customer_trx_lines_all rctl
4739 WHERE msi.inventory_item_id = rctl.inventory_item_id
4740 AND msi.inventory_item_id = l.inventory_item_id
4741 /*AND l.rma_line_id = rctl.interface_line_attribute6*/ /*Commented and added below by mmurtuza for bug 16181417*/
4742 AND l.rma_line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
4743 NULL, rctl.interface_line_attribute6, NULL)
4744 AND msi.shippable_item_flag = 'N'
4745 /*AND msi.organization_id = rctl.interface_line_attribute10*/ /*Commented and added below by mmurtuza for bug 16181417*/
4746 AND msi.organization_id = decode(replace(translate(rctl.interface_line_attribute10,'123456789','000000000'),'0',''),
4747 NULL, rctl.interface_line_attribute10, NULL)
4748 AND rctl.customer_trx_id = pn_customer_trx_id);
4749
4750
4751
4752
4753 CURSOR inclu_rma_tax IS
4754 SELECT
4755 a.tax_id tax_id
4756 , b.tax_type tax_type
4757 , SUM(a.tax_amount) tax_amount
4758 , nvl(b.rounding_factor,0) rounding_factor --Add by Xiao for bug#11936390, for rounding issue.
4759 FROM
4760 jai_cmn_taxes_all b
4761 , jai_ar_trx_tax_lines a
4762 WHERE a.tax_id = b.tax_id
4763 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
4764 AND a.link_to_cust_trx_line_id IN
4765 (SELECT rctl.customer_trx_line_id
4766 FROM OE_ORDER_HEADERS_ALL oh,
4767 OE_ORDER_LINES_ALL ol,
4768 OE_TRANSACTION_TYPES_TL ot,
4769 oe_workflow_assignments owf,
4770 ra_customer_trx_lines_all rctl
4771 WHERE oh.header_id = ol.header_id
4772 AND oh.order_type_id = ot.transaction_type_id
4773 AND oh.order_type_id = owf.order_type_id
4774 AND ol.line_type_id = owf.line_type_id
4775 AND ot.LANGUAGE = userenv('LANG')
4776 /* AND to_char(ol.line_id) = rctl.interface_line_attribute6 */ /*Commented and added below by mmurtuza for bug 14194526*/
4777 AND ol.line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
4778 NULL, rctl.interface_line_attribute6, NULL)
4779 AND owf.process_name IN
4780 ('R_RMA_CREDIT_APP_HDR_INV', 'R_RMA_CREDIT_WO_SHIP_APPROVE',
4781 'R_RMA_CREDIT_WO_SHIP_HDR_INV',
4782 'R_RMA_FOR_CREDIT_WO_SHIPMENT', 'R_RMA_FOR_OTA_CREDIT')
4783 AND rctl.customer_trx_id = pn_customer_trx_id
4784 UNION
4785 SELECT rctl.customer_trx_line_id
4786 FROM MTL_SYSTEM_ITEMS msi,
4787 JAI_OM_OE_RMA_LINES l,
4788 ra_customer_trx_lines_all rctl
4789 WHERE msi.inventory_item_id = rctl.inventory_item_id
4790 AND msi.inventory_item_id = l.inventory_item_id
4791 /*AND l.rma_line_id = rctl.interface_line_attribute6*/ /*Commented and added below by mmurtuza for bug 16181417*/
4792 AND l.rma_line_id = decode(replace(translate(rctl.interface_line_attribute6,'123456789','000000000'),'0',''),
4793 NULL, rctl.interface_line_attribute6, NULL)
4794 AND msi.shippable_item_flag = 'N'
4795 /*AND msi.organization_id = rctl.interface_line_attribute10*/ /*Commented and added below by mmurtuza for bug 16181417*/
4796 AND msi.organization_id = decode(replace(translate(rctl.interface_line_attribute10,'123456789','000000000'),'0',''),
4797 NULL, rctl.interface_line_attribute10, NULL)
4798 AND rctl.customer_trx_id = pn_customer_trx_id)
4799 GROUP BY
4800 a.tax_id
4801 , b.tax_type;
4802
4803
4804 ln_rma_line_number NUMBER ;
4805
4806 --start additions for bug#11652823
4807 cursor get_regime_id(cp_tax_type in varchar2)
4808 is
4809 select regime_id, regime_code /*Bug 11821537 - Added Regime Code*/
4810 from jai_regime_tax_types_v
4811 where regime_code in (jai_constants.service_regime,jai_constants.vat_regime)
4812 and tax_type=cp_tax_type;
4813
4814 ln_regime_id number;
4815 ln_regime_code jai_rgm_definitions.regime_code%TYPE; /*Bug 11821537 - Added Regime Code*/
4816 --end additions for bug#11652823
4817 ------------------------------------------------------------------
4818 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 End
4819
4820 /*Bug 11821537 - Fetch the effective date on which Service Tax needs to accounted in accrual basis instead of cash - Start*/
4821 ld_st_accrual_date DATE;
4822 ld_trx_date DATE;
4823 --Commented by Chong.Lei for POT code port begin
4824 /*
4825 CURSOR c_get_st_accrual_date (p_regime_id NUMBER)
4826 IS
4827 SELECT to_date(attribute_value, 'DD/MM/YYYY')
4828 FROM jai_rgm_registrations
4829 WHERE regime_id = p_regime_id
4830 AND attribute_code = 'EFF_DATE_ST_PT'
4831 AND attribute_type_code = 'OTHERS'
4832 AND registration_type = 'OTHERS';
4833 */
4834 --Commented by Chong.Lei for POT code port end
4835 --Added by Chong.Lei for POT code port start
4836 ------------------------------------------------------------------------------------
4837 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
4838 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
4839 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
4840 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
4841 select to_date(attribute_value, 'DD/MM/YYYY')
4842 from JAI_RGM_ORG_REGNS_V
4843 where regime_id = p_regime_id
4844 and organization_id = p_organization_id
4845 and location_id = p_location_id
4846 AND attribute_code = 'EFF_DATE_ST_PT'
4847 AND attribute_type_code = 'OTHERS'
4848 AND registration_type = 'OTHERS'
4849 AND (NOT EXISTS
4850 (select '1'
4851 from JAI_RGM_ORG_REGNS_V
4852 where regime_id = p_regime_id
4853 and attribute_code IN 'INV_ORG_CLASSIFICATION'
4854 and attribute_value <> 'ORGANIZATION'
4855 and organization_id = p_organization_id
4856 and location_id = p_location_id)
4857 OR
4858 NOT EXISTS
4859 (select '1'
4860 from JAI_RGM_ORG_REGNS_V
4861 where regime_id = p_regime_id
4862 and attribute_code IN 'SERVICE TYPE'
4863 and attribute_value <> 'OTHER'
4864 and organization_id = p_organization_id
4865 and location_id = p_location_id)
4866 );
4867 ------------------------------------------------------------------------------------
4868 --Added by Chong.Lei for POT code port end
4869 /*Bug 11821537 - End*/
4870
4871 /*Bug 11905163 - Start*/
4872 CURSOR c_get_tax_account(p_tax_id IN NUMBER)
4873 IS
4874 SELECT tax_account_id
4875 FROM jai_cmn_taxes_all
4876 WHERE tax_id = p_tax_id;
4877 /*Bug 11905163 - End*/
4878
4879 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
4880 ------------------------------------------------------------------------------
4881
4882
4883 ln_amount NUMBER;
4884 ------------------------------------------------------------------------------
4885 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
4886
4887
4888 --Added by zhiwei for POT Change Bug#13023443 on 20110929 begin
4889 ----------------------------------------------------------------------------------
4890 Cursor chk_external_event
4891 is
4892 select 1
4893 from dual
4894 where exists
4895 ( select customer_trx_line_id
4896 from jai_ar_trx_lines
4897 where customer_trx_id = pn_customer_trx_id
4898 AND NVL(interface_flag,'#') = 'Y'
4899 AND NVL(interface_event,'#')= JAI_OPEN_API_PKG.GV_TAXABLE_EVENT_EXTERNAL
4900 );
4901
4902 ln_external_flag number; -- 1:external
4903 ln_process_flag number; -- 0:ignore, 1:process
4904 ----------------------------------------------------------------------------------
4905 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
4906
4907 BEGIN
4908 --logging for debug
4909 IF (ln_proc_level >= ln_dbg_level)
4910 THEN
4911 FND_LOG.STRING( ln_proc_level
4912 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
4913 , 'Enter procedure'
4914 );
4915 END IF; --ln_proc_level>=l_dbg_level
4916
4917 ln_org_id := pn_org_id;
4918 ln_cust_trx_type_id := pn_cust_trx_type_id;
4919
4920 -- Get customer info
4921 BEGIN
4922 SELECT
4923 jat.trx_number
4924 , jat.exchange_date
4925 , jat.exchange_rate_type
4926 , jat.exchange_rate
4927 , jat.invoice_currency_code
4928 , jat.organization_id
4929 ,jat.location_id --11652823
4930 , mp.organization_code
4931 INTO
4932 lv_inv_num
4933 , ld_cur_conversion_date
4934 , lv_cur_conversion_type
4935 , ln_cur_conversion_rate
4936 , lv_currency_code
4937 , ln_inv_org_id
4938 ,ln_inv_loc_id -- 11652823
4939 , lv_inv_org_code
4940 FROM
4941 jai_ar_trxs jat
4942 , mtl_parameters mp
4943 WHERE jat.customer_trx_id = pn_customer_trx_id
4944 AND jat.organization_id = mp.organization_id;
4945
4946 EXCEPTION
4947 WHEN OTHERS THEN
4948 xv_process_message := Sqlerrm||'. Get customer info in acct_inclu_taxes procedure.';
4949 RAISE exception_error;
4950 END;
4951
4952 -- Get Revenue dr accounting id
4953 BEGIN
4954 /*
4955 SELECT
4956 gl_id_rev -- Modified by Jia for fixed bug#8474445, use gl_id_rev to replace gl_id_rec
4957 INTO
4958 ln_rec_account_id
4959 FROM
4960 ra_cust_trx_types_all
4961 WHERE org_id = ln_org_id
4962 AND cust_trx_type_id = ln_cust_trx_type_id;
4963 *//*commented by vkaranam for bug#9461197*/
4964
4965 --added the below by vkaranam for bug#9461197
4966 open c_rev_acc;
4967 fetch c_rev_acc into ln_rec_account_id;
4968 close c_rev_acc;
4969
4970 EXCEPTION
4971 WHEN OTHERS THEN
4972 xv_process_message := Sqlerrm||'. Get revenue dr accounting in acct_inclu_taxes procedure.';
4973 RAISE exception_error;
4974 END;
4975
4976
4977 BEGIN
4978 SELECT
4979 set_of_books_id
4980 , gl_date
4981 INTO
4982 ln_set_of_books_id
4983 , ld_gl_date
4984 FROM ra_cust_trx_line_gl_dist_all
4985 WHERE customer_trx_id = pn_customer_trx_id
4986 AND rownum = 1;
4987
4988 EXCEPTION
4989 WHEN OTHERS THEN
4990 xv_process_message := Sqlerrm||'. Get gl date in acct_inclu_taxes procedure.';
4991 RAISE exception_error;
4992 END;
4993
4994 -- Insert inclusive taxes into GL Interface table
4995 ln_total_inclu_tax_amt := 0;
4996
4997 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 Begin
4998 -----------------------------------------------------------------
4999 OPEN get_rma_flag_cur;
5000 FETCH get_rma_flag_cur
5001 INTO ln_rma_line_number;
5002 CLOSE get_rma_flag_cur;
5003
5004 IF ln_rma_line_number > 0
5005 THEN
5006
5007 FOR inclu_tax_csr IN inclu_rma_tax
5008 LOOP
5009
5010 BEGIN
5011 /*commented by vkaranam for bug#11652823
5012 SELECT
5013 TO_NUMBER(acc_rgm.attribute_value)
5014 INTO
5015 ln_tax_account_id
5016 FROM
5017 jai_rgm_definitions rgm_def
5018 , jai_rgm_registrations tax_rgm
5019 , jai_rgm_registrations acc_rgm
5020 WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
5021 AND tax_rgm.regime_id = rgm_def.regime_id
5022 AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
5023 AND tax_rgm.attribute_code = inclu_tax_csr.tax_type
5024 AND tax_rgm.regime_id = acc_rgm.regime_id
5025 AND acc_rgm.registration_type = jai_constants.regn_type_accounts
5026 AND acc_rgm.attribute_code = jai_constants.recovery
5027 AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
5028 */
5029 --start additions by vkaranam for bug#11652823
5030 open get_regime_id( inclu_tax_csr.tax_type);
5031 fetch get_regime_id into ln_regime_id, ln_regime_code;
5032 close get_regime_id;
5033 ln_tax_account_id := NULL; /*Bug 11905163*/
5034 ln_tax_account_id:= jai_cmn_rgm_Recording_pkg.get_account(
5035 p_regime_id => ln_regime_id,
5036 p_organization_type =>'IO',
5037 p_organization_id => ln_inv_org_id,
5038 p_location_id =>ln_inv_loc_id,
5039 p_tax_type => inclu_tax_csr.tax_type,
5040 p_account_name => jai_constants.recovery
5041 );
5042 --end additions for bug#11652823
5043 /*Bug 11905163 - Start*/
5044 IF (ln_tax_account_id IS NULL) THEN
5045 OPEN c_get_tax_account(inclu_tax_csr.tax_id);
5046 FETCH c_get_tax_account INTO ln_tax_account_id;
5047 CLOSE c_get_tax_account;
5048 END IF;
5049 /*Bug 11905163 - End*/
5050 EXCEPTION
5051 WHEN NO_DATA_FOUND THEN
5052 SELECT tax_account_id
5053 INTO ln_tax_account_id
5054 FROM jai_cmn_taxes_all
5055 WHERE tax_id = inclu_tax_csr.tax_id
5056 AND org_id = pn_org_id;
5057
5058 WHEN OTHERS THEN
5059 ln_tax_account_id := -1;
5060 END;
5061 --Add by Xiao for bug#11936390, rounding issue for inclusive tax accounting in gl
5062 ln_amount := round(inclu_tax_csr.tax_amount, inclu_tax_csr.rounding_factor);
5063
5064 INSERT INTO gl_interface
5065 ( status
5066 , set_of_books_id
5067 , user_je_source_name
5068 , user_je_category_name
5069 , accounting_date
5070 , currency_code
5071 , date_created
5072 , created_by
5073 , actual_flag
5074 , entered_cr
5075 , entered_dr
5076 , transaction_date
5077 , code_combination_id
5078 , currency_conversion_date
5079 , user_currency_conversion_type
5080 , currency_conversion_rate
5081 , reference1
5082 , reference10
5083 , reference22
5084 , reference23
5085 , reference24
5086 , reference25
5087 , reference26
5088 , reference27
5089 )
5090 VALUES
5091 ( 'NEW'
5092 , ln_set_of_books_id -- the set of books id
5093 , 'Receivables India' -- je source name 'Receivables India'
5094 , 'Register India' -- je category name 'Register India'
5095 , ld_gl_date -- accounting date (GL date of the invoice)
5096 , lv_currency_code -- currency code
5097 , sysdate -- standard who column
5098 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
5099 , 'A' -- actual flag, hard coded value
5100 --, inclu_tax_csr.tax_amount -- credit amt, inclusive tax amount
5101 , ln_amount -- modified by Xiao for bug#11936390.
5102 , null -- debit amt
5103 , sysdate -- invoice date
5104 , ln_tax_account_id -- code combination
5105 , ld_cur_conversion_date
5106 , lv_cur_conversion_type
5107 , ln_cur_conversion_rate
5108 , lv_inv_org_code -- inventory organization code
5109 , 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
5110 , 'India Localization Entry' -- hard code string
5111 , 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
5112 , 'RA_CUSTOMER_TRX_ALL' -- hard code string
5113 , 'CUSTOMER_TRX_ID' -- hard code string
5114 , pn_customer_trx_id -- value of customer_trx_id
5115 , ln_inv_org_id -- organization id of the inventory organization id
5116 );
5117
5118 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + inclu_tax_csr.tax_amount;
5119 FND_FILE.PUT_LINE ( FND_FILE.LOG
5120 , 'Insert tax info: '
5121 || 'tax_account_id = ' || ln_tax_account_id
5122 || ' tax_amount = '|| inclu_tax_csr.tax_amount
5123 );
5124 END LOOP; -- inclu_rma_tax cusor
5125
5126 ELSE
5127 ------------------------------------------------------------------
5128 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 End
5129
5130
5131 FOR inclu_tax_csr IN inclu_tax
5132 LOOP
5133 /*COMMENTED by vkaranam for bug#11652823
5134 ln_tax_account_id := get_tax_account_id
5135 ( pn_tax_id => inclu_tax_csr.tax_id
5136 , pv_tax_type => inclu_tax_csr.tax_type
5137 , pn_org_id => ln_org_id
5138 );
5139 */
5140 /*start additions for bug#11652823*/
5141 --start additions for bug#11652823
5142
5143 open get_regime_id( inclu_tax_csr.tax_type);
5144 fetch get_regime_id into ln_regime_id, ln_regime_code;
5145 close get_regime_id;
5146 ln_tax_account_id := NULL; /*Bug 11905163*/
5147
5148
5149 --Added by zhiwei for POT Change Bug#13023443 on 20110929 begin
5150 ----------------------------------------------------------------------------------
5151 open chk_external_event;
5152 fetch chk_external_event into ln_external_flag;
5153 close chk_external_event;
5154
5155 /*
5156 || For Service Tax
5157 if 1) From Open Interface
5158 2) External
5159 3) GL > POT
5160 not populate gl_interface
5161 else
5162 populate gl_interface.
5163
5164 For Other Tax
5165 if 1) From Open Interface
5166 2) External
5167 not populate gl_interface
5168 else
5169 populate gl_interface
5170
5171 ----------------------------------------
5172 In another side :
5173 Do Inclusive Tax accounting:
5174 1) Normal AR transaction
5175 2) Standard Event AR Transaction
5176 3) External Event & Service & GL < POT
5177 ----------------------------------------
5178 In other side :
5179
5180 Normal AR Transaction
5181 should do inclusive tax accounting(YES)
5182 From API and Standard
5183 should do inclusive tax accounting(YES)
5184 From API and External
5185 not Service Tax
5186 Not do inclusive tax accounting
5187 Service Tax
5188 GL >= POT
5189 not do inclusive tax accounting
5190 GL < POT
5191 should do inclusive tax accounting(YES)
5192 */
5193
5194 ----------------------------------------------------------------------------------
5195 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
5196
5197
5198
5199
5200 /*ER 11821537 - Service Tax must hit Liability instead of Interim Liability due to change in point of taxation to accural from cash - Start*/
5201 If upper(ln_regime_code) = 'SERVICE' THEN
5202
5203 -- OPEN c_get_st_accrual_date(ln_regime_id); --Comment by Chong.Lei for POT code port
5204 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/ --Added by Chong.Lei for POT code port
5205 OPEN c_get_st_accrual_date(ln_regime_id, ln_inv_org_id, ln_inv_loc_id); --Added by Chong.Lei for POT code port
5206 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
5207 CLOSE c_get_st_accrual_date;
5208
5209 ld_trx_date := get_transaction_date(pn_customer_trx_id);
5210
5211 --Added by zhiwei for POT Change Bug#13023443 on 20110929 begin
5212 ----------------------------------------------------------------------------------
5213 --For Service Tax ,not populate gl_interface only while From Open Interface and External Event and GL >= POT
5214 if( nvl(ln_external_flag,0) = 1
5215 and
5216 ld_trx_date >= ld_st_accrual_date
5217 )then
5218 ln_process_flag := 0;
5219 else
5220 ln_process_flag := 1;
5221 end if;
5222 ----------------------------------------------------------------------------------
5223 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
5224
5225
5226 IF ld_trx_date >= ld_st_accrual_date THEN
5227 ln_tax_account_id := jai_cmn_rgm_Recording_pkg.get_account(
5228 p_regime_id => ln_regime_id,
5229 p_organization_type => 'IO',
5230 p_organization_id => ln_inv_org_id,
5231 p_location_id => ln_inv_loc_id,
5232 p_tax_type => inclu_tax_csr.tax_type,
5233 p_account_name => jai_constants.liability
5234 );
5235 ELSE
5236 ln_tax_account_id := jai_cmn_rgm_Recording_pkg.get_account(
5237 p_regime_id => ln_regime_id,
5238 p_organization_type => 'IO',
5239 p_organization_id => ln_inv_org_id,
5240 p_location_id => ln_inv_loc_id,
5241 p_tax_type => inclu_tax_csr.tax_type,
5242 p_account_name => jai_constants.liability_interim
5243 );
5244 END IF;
5245
5246
5247 ELSE
5248
5249 --Added by zhiwei for POT Change Bug#13023443 on 20110929 begin
5250 ----------------------------------------------------------------------------------
5251 --For other tax except Service, not populate gl_interface while From Open APi and External Event.
5252 if( nvl(ln_external_flag,0) = 1 )then
5253 ln_process_flag := 0;
5254 else
5255 ln_process_flag := 1;
5256 end if;
5257 ----------------------------------------------------------------------------------
5258 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
5259
5260 ln_tax_account_id := jai_cmn_rgm_Recording_pkg.get_account(
5261 p_regime_id => ln_regime_id,
5262 p_organization_type => 'IO',
5263 p_organization_id => ln_inv_org_id,
5264 p_location_id => ln_inv_loc_id,
5265 p_tax_type => inclu_tax_csr.tax_type,
5266 p_account_name => jai_constants.liability_interim
5267 );
5268 END IF;
5269 /*ER 11821537 - End*/
5270 /*Bug 11905163 - Start*/
5271 IF (ln_tax_account_id IS NULL) THEN
5272 OPEN c_get_tax_account(inclu_tax_csr.tax_id);
5273 FETCH c_get_tax_account INTO ln_tax_account_id;
5274 CLOSE c_get_tax_account;
5275 END IF;
5276 /*Bug 11905163 - End*/
5277 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
5278 -----------------------------------------------------------------------------------
5279 -- Following code fetch ccid from tax definition, when ccid is not fetch for
5280 -- 'Excise' and other taxes.
5281
5282
5283 IF ln_tax_account_id IS NULL THEN
5284 ln_tax_account_id := -1;
5285 END IF;
5286
5287 -----------------------------------------------------------------------------------
5288 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, end
5289 --Add by Xiao for bug#11936390, rounding issue for inclusive tax accounting in GL interface.
5290 ln_amount := round(inclu_tax_csr.tax_amount , inclu_tax_csr.rounding_factor);
5291
5292 --Added by zhiwei for POT change Bug#13023443 on 20110929 begin
5293 ----------------------------------------------------------------------
5294 if (nvl(ln_process_flag,0) = 1) then
5295 ----------------------------------------------------------------------
5296 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
5297 INSERT INTO gl_interface
5298 ( status
5299 , set_of_books_id
5300 , user_je_source_name
5301 , user_je_category_name
5302 , accounting_date
5303 , currency_code
5304 , date_created
5305 , created_by
5306 , actual_flag
5307 , entered_cr
5308 , entered_dr
5309 , transaction_date
5310 , code_combination_id
5311 , currency_conversion_date
5312 , user_currency_conversion_type
5313 , currency_conversion_rate
5314 , reference1
5315 , reference10
5316 , reference22
5317 , reference23
5318 , reference24
5319 , reference25
5320 , reference26
5321 , reference27
5322 )
5323 VALUES
5324 ( 'NEW'
5325 , ln_set_of_books_id -- the set of books id
5326 , 'Receivables India' -- je source name 'Receivables India'
5327 , 'Register India' -- je category name 'Register India'
5328 , ld_gl_date -- accounting date (GL date of the invoice)
5329 , lv_currency_code -- currency code
5330 , sysdate -- standard who column
5331 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
5332 , 'A' -- actual flag, hard coded value
5333 --, inclu_tax_csr.tax_amount -- credit amt, inclusive tax amount
5334 , ln_amount -- modified by Xiao for bug#11936390
5335 , null -- debit amt
5336 , sysdate -- invoice date
5337 , ln_tax_account_id -- code combination
5338 , ld_cur_conversion_date
5339 , lv_cur_conversion_type
5340 , ln_cur_conversion_rate
5341 , lv_inv_org_code -- inventory organization code
5342 , 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
5343 , 'India Localization Entry' -- hard code string
5344 , 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
5345 , 'RA_CUSTOMER_TRX_ALL' -- hard code string
5346 , 'CUSTOMER_TRX_ID' -- hard code string
5347 , pn_customer_trx_id -- value of customer_trx_id
5348 , ln_inv_org_id -- organization id of the inventory organization id
5349 );
5350
5351 -- ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + inclu_tax_csr.tax_amount;
5352 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + ln_amount; --Add by Xiao for bug#11936390
5353 FND_FILE.PUT_LINE ( FND_FILE.LOG
5354 , 'Insert tax info: '
5355 || 'tax_account_id = ' || ln_tax_account_id
5356 || ' tax_amount = '|| inclu_tax_csr.tax_amount
5357 );
5358 --Added by zhiwei for POT change Bug#13023443 on 20110929 begin
5359 ----------------------------------------------------------------------
5360 end if; --Only process 1) Normal transaction;
5361 -- 2) Standard event from OPEN api ;
5362 -- 3) External event from Open APi
5363 -- Service Tax
5364 -- GL < POT date ;
5365 ----------------------------------------------------------------------
5366 --Added by zhiwei for POT change Bug#13023443 on 20110929 end
5367
5368 END LOOP; -- inclu_tax cusor
5369
5370 END IF; --ln_rma_line_number > 0 Added by Bo Li for Bug#9765108 on 09-JUL-2010
5371
5372 /*9892611 - Check if Inclusive Tax Amount is non zero before inserting into gl_interface
5373 Dummy records are inserted into GL_INTERFACE with zero amounts otherwise*/
5374
5375 IF ln_total_inclu_tax_amt <> 0 THEN
5376 -- Insert revenue amount into GL Interface table
5377
5378 INSERT INTO gl_interface
5379 ( status
5380 , set_of_books_id
5381 , user_je_source_name
5382 , user_je_category_name
5383 , accounting_date
5384 , currency_code
5385 , date_created
5386 , created_by
5387 , actual_flag
5388 , entered_cr
5389 , entered_dr
5390 , transaction_date
5391 , code_combination_id
5392 , currency_conversion_date
5393 , user_currency_conversion_type
5394 , currency_conversion_rate
5395 , reference1
5396 , reference10
5397 , reference22
5398 , reference23
5399 , reference24
5400 , reference25
5401 , reference26
5402 , reference27
5403 )
5404 VALUES
5405 ( 'NEW'
5406 , ln_set_of_books_id -- the set of books id
5407 , 'Receivables India' -- je source name 'Receivables India'
5408 , 'Register India' -- je category name 'Register India'
5409 , ld_gl_date -- accounting date (GL date of the invoice)
5410 , lv_currency_code -- currency code
5411 , sysdate -- standard who column
5412 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
5413 , 'A' -- actual flag, hard coded value
5414 , null -- credit amt, inclusive tax amount
5415 , ln_total_inclu_tax_amt -- debit amt
5416 , sysdate -- invoice date
5417 , ln_rec_account_id -- code combination
5418 , ld_cur_conversion_date
5419 , lv_cur_conversion_type
5420 , ln_cur_conversion_rate
5421 , lv_inv_org_code -- inventory organization code
5422 , 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
5423 , 'India Localization Entry' -- hard code string
5424 , 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
5425 , 'RA_CUSTOMER_TRX_ALL' -- hard code string
5426 , 'CUSTOMER_TRX_ID' -- hard code string
5427 , pn_customer_trx_id -- value of customer_trx_id
5428 , ln_inv_org_id -- organization id of the inventory organization id
5429 );
5430
5431 FND_FILE.PUT_LINE ( FND_FILE.LOG
5432 , 'Insert debit info: '
5433 || 'account_id = ' || ln_rec_account_id
5434 || ' amount = '|| ln_total_inclu_tax_amt
5435 );
5436
5437 END IF;
5438
5439 xv_process_flag := 'SS';
5440 xv_process_message := 'Inclusive taxes have successed into GL Interface';
5441
5442 --logging for debug
5443 IF (ln_proc_level >= ln_dbg_level)
5444 THEN
5445 FND_LOG.STRING( ln_proc_level
5446 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
5447 , 'Exit procedure'
5448 );
5449 END IF; -- (ln_proc_level>=ln_dbg_level)
5450
5451 EXCEPTION
5452 WHEN exception_error THEN
5453 xv_process_flag := 'EE';
5454 WHEN OTHERS THEN
5455 xv_process_flag := 'UE';
5456 xv_process_message := Sqlerrm||'. Exception error in acct_inclu_taxes procedure';
5457
5458 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5459 THEN
5460 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
5461 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
5462 , Sqlcode||Sqlerrm);
5463 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5464
5465 END acct_inclu_taxes;
5466 -- Added by Jia Li on tax inclusive computation on 2007/11/30, End
5467
5468 /*============================================================================================================
5469 -- PROCEDURE NAME:
5470 -- update_All_Invoice_Num Private
5471 --
5472 -- DESCRIPTION:
5473 -- This procedure is written that update the ra_customer_trx_all ct_reference column
5474 -- to display the VAT/Excise/Service Invoice Number in AR
5475 --
5476 --
5477 -- ER NAME/BUG#
5478 -- Advanced Receipt for Service Tax enhancement
5479 --
5480 -- PARAMETERS:
5481 -- In: pn_customer_trx_id Indicates the customer trx id
5482 -- pv_excise_invoice_no Indicates the excise invoice number
5483 -- pv_vat_invoice_no Indicates vat invoice number
5484 -- pv_st_invoice_num Indicates service tax invoice number
5485 --
5486 --
5487 -- DESIGN REFERENCES:
5488 -- TD named "VAT Invoice Number on AR Invoice Technical Design.doc" has been
5489 -- referenced in the section 6.1
5490 -- TD named "TDD_122_FIN_JAI_Service_Tax_on_Advanced_Receipt.doc" has been referenced
5491 --
5492 --
5493 -- CALL FROM
5494 -- JAI_AR_MATCH_TAX_PKG.display_vat_invoice_number
5495 --
5496 --
5497 --
5498 -- CHANGE HISTORY:
5499 -- 24-Oct-2011 Created by Qinglei
5500 -- 26-Dec-2011 Modified by Qinglei for bug#13531089
5501 When both excise and vat inv number are null, should populate service
5502 Invoice Number to AR Workbench also.
5503 ==============================================================================================================*/
5504 PROCEDURE update_all_invoice_num
5505 ( pn_customer_trx_id IN NUMBER
5506 , pv_excise_invoice_no IN VARCHAR2
5507 , pv_vat_invoice_no IN VARCHAR2
5508 , pv_st_invoice_num IN VARCHAR2
5509 )
5510 IS
5511
5512 cv_seperator CONSTANT VARCHAR2(30) := ';';
5513 lv_reference ra_customer_trx_all.ct_reference%TYPE;
5514 lv_reference_check ra_customer_trx_all.ct_reference%TYPE;
5515 lv_procedure_name VARCHAR2(40):='display_all_invoice_num';
5516 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5517 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
5518
5519 -- check the invoice numbers are in the referece field or not
5520 CURSOR check_reference IS
5521 SELECT ct_reference
5522 FROM ra_customer_trx_all
5523 WHERE customer_trx_id = pn_customer_trx_id;
5524
5525 BEGIN
5526 --logging for debug
5527 IF (ln_proc_level >= ln_dbg_level)
5528 THEN
5529 FND_LOG.STRING( ln_proc_level
5530 , lv_procedure_name || '.begin'
5531 , 'Enter procedure'
5532 );
5533 END IF; --l_proc_level>=l_dbg_level
5534
5535
5536 -- get the exist value of reference field
5537 OPEN check_reference;
5538 FETCH check_reference
5539 INTO lv_reference_check;
5540 CLOSE check_reference;
5541
5542 fnd_file.put_line(FND_FILE.LOG,
5543 'The exist reference is: '||lv_reference_check);
5544
5545 lv_reference := NULL;
5546
5547 -- When the vat invoice number is not null
5548 IF pv_vat_invoice_no IS NOT NULL
5549 THEN
5550 --when the vat invoice number and excise invoice no
5551 --are the same
5552 IF pv_vat_invoice_no = pv_excise_invoice_no
5553 THEN
5554 --When the vat and excise invoice has not been imported
5555 IF instr(nvl(lv_reference_check,'$$'),nvl(pv_vat_invoice_no,'##')) = 0
5556 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0)
5557 THEN
5558 --When the service invoice has not been imported
5559 IF instr(nvl(lv_reference_check,'$$'),pv_st_invoice_num) = 0
5560 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0) THEN
5561 lv_reference:=substr(lv_reference_check||cv_seperator
5562 ||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5563 --When the service invoice has been imported
5564 ELSIF instr(nvl(lv_reference_check,'$$'),nvl(pv_st_invoice_num,'##')) > 0
5565 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator) > 0) THEN
5566 lv_reference:=substr(substr(lv_reference_check,1,instr(lv_reference_check,cv_seperator,1,1))||
5567 pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5568 END IF;
5569 END IF;--instr(nvl(lv_reference_check,'$$'),pv_vat_invoice_no)
5570
5571 ELSE -- When the vat and excise invoice number are different
5572 --When neither the vat and excise number is displayed
5573 IF ( instr(nvl(lv_reference_check,'$$'),pv_vat_invoice_no) = 0
5574 AND instr(nvl(lv_reference_check,'$$'),nvl(pv_excise_invoice_no,'##')) = 0 )
5575 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0)--Added by Bo Li on 2010-05-05
5576 THEN
5577 IF instr(nvl(lv_reference_check,'$$'),pv_st_invoice_num) = 0
5578 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0) THEN
5579 lv_reference:=substr(lv_reference_check||cv_seperator||pv_excise_invoice_no||cv_seperator
5580 ||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5581 ELSIF instr(nvl(lv_reference_check,'$$'),nvl(pv_st_invoice_num,'##')) > 0
5582 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator) > 0) THEN
5583 lv_reference:=substr(substr(lv_reference_check,1,instr(lv_reference_check,cv_seperator,1,1))||
5584 pv_excise_invoice_no||cv_seperator||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5585 END IF;
5586 -- when the vat is displayed and excise invoice number is not displayed
5587 --when the excise is displayed and vat invoice number is not displayed
5588 ELSIF ((instr(nvl(lv_reference_check,'$$'),pv_vat_invoice_no) > 0
5589 OR instr(nvl(lv_reference_check,'$$'),nvl(pv_excise_invoice_no,'##')) > 0))
5590 AND (instr(nvl(lv_reference_check,'$$'),pv_excise_invoice_no||cv_seperator||pv_vat_invoice_no)>=0)
5591 AND (instr(nvl(lv_reference_check,'$$'),cv_seperator)>0) --Added by Bo Li on 2010-05-05
5592 THEN
5593 /*IF instr(nvl(lv_reference_check,'$$'),pv_st_invoice_num) = 0
5594 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0) THEN
5595 lv_reference:=substr(lv_reference_check||cv_seperator||pv_excise_invoice_no||cv_seperator
5596 ||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5597 ELSIF instr(nvl(lv_reference_check,'$$'),nvl(pv_st_invoice_num,'##')) > 0
5598 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator) > 0) THEN*/
5599 --Commented by Qinglei on 26-dec-2011 as either excise or vat invovice number has been imported,
5600 --should concatenate reference as following
5601 lv_reference:=substr(substr(lv_reference_check,1,instr(lv_reference_check,cv_seperator,1,1))||
5602 pv_excise_invoice_no||cv_seperator||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5603 /*END IF;*/
5604
5605 END IF; --((instr(nvl(lv_reference_check,'$$'),pv_vat_invoice_no) > 0
5606 END IF; --pv_vat_invoice_no = pv_excise_invoice_no
5607 -- When vat invoice number is null
5608 ELSE
5609 --when the excise invoice is not null
5610 IF pv_excise_invoice_no IS NOT NULL
5611 THEN
5612 --When excise invoice has not been imported
5613 IF instr(nvl(lv_reference_check,'$$'),pv_excise_invoice_no) = 0
5614 THEN
5615 IF instr(nvl(lv_reference_check,'$$'),pv_st_invoice_num) = 0
5616 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0) THEN
5617 lv_reference:=substr(lv_reference_check||cv_seperator||pv_excise_invoice_no||cv_seperator
5618 ||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5619 ELSIF instr(nvl(lv_reference_check,'$$'),nvl(pv_st_invoice_num,'##')) > 0
5620 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator) > 0) THEN
5621 lv_reference:=substr(substr(lv_reference_check,1,instr(lv_reference_check,cv_seperator,1,1))||
5622 pv_excise_invoice_no||cv_seperator||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5623 END IF;
5624 ELSIF instr(nvl(lv_reference_check,'$$'),pv_excise_invoice_no) > 0 THEN
5625 lv_reference:=substr(substr(lv_reference_check,1,instr(lv_reference_check,cv_seperator,1,1))||
5626 pv_excise_invoice_no||cv_seperator||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5627 END IF;
5628 --Added by Qinglei 26-Dec-2011 for bug#13531089 begin
5629 --Both excise and vat invoice number are null
5630 ELSE
5631 IF pv_st_invoice_num IS NOT NULL THEN
5632 IF instr(nvl(lv_reference_check,'$$'),pv_excise_invoice_no) = 0
5633 OR (instr(nvl(lv_reference_check,'$$'),cv_seperator)=0) THEN
5634 lv_reference:=substr(lv_reference_check||cv_seperator||pv_excise_invoice_no||cv_seperator
5635 ||pv_vat_invoice_no||cv_seperator||pv_st_invoice_num,1,150);
5636 END IF;
5637 END IF;
5638 --Added by Qinglei 26-Dec-2011 for bug#13531089 end
5639 END IF; -- pv_excise_invoice_no IS NOT NULL
5640 END IF; --pv_vat_invoice_no IS NOT NULL
5641
5642
5643 IF lv_reference IS NOT NULL
5644 THEN
5645 -- update the reference column in the ra_customer_trx_all
5646 UPDATE ra_customer_trx_all
5647 SET ct_reference = lv_reference
5648 WHERE customer_trx_id = pn_customer_trx_id;
5649 END IF; --lv_reference IS NOT NULL
5650
5651 --logging for debug
5652 /*IF (ln_proc_level >= ln_dbg_level)
5653 THEN
5654 FND_LOG.STRING( ln_proc_level
5655 , lv_procedure_name || '.end'
5656 , 'Exit procedure');
5657 END IF;*/
5658 END update_all_invoice_num;
5659
5660 --==========================================================================
5661 -- PROCEDURE NAME:
5662 -- Display_Vat_Invoice_No Public
5663 --
5664 -- DESCRIPTION:
5665 -- This procedure is written that update the ra_customer_trx_all ct_reference column
5666 -- to display the VAT/Excise Number in AR
5667 --
5668 -- ER NAME/BUG#
5669 -- VAT/Excise Number shown in AR transaction workbench'
5670 -- Bug 9303168
5671 --
5672 -- PARAMETERS:
5673 -- In: pn_customer_trx_id Indicates the customer trx id
5674 -- pv_excise_invoice_no Indicates the excise invoice number
5675 -- pv_vat_invoice_no Indicates vat invoice number
5676 --
5677 --
5678 -- DESIGN REFERENCES:
5679 -- TD named "VAT Invoice Number on AR Invoice Technical Design.doc" has been
5680 -- referenced in the section 6.1
5681 --
5682 -- CALL FROM
5683 -- JAI_AR_MATCH_TAX_PKG.process_batch
5684 -- JAI_AR_TRX.update_excise_invoice_no
5685 -- JAI_AR_TRX.update_reference
5686 --
5687 -- CHANGE HISTORY:
5688 -- 19-Jan-2010 Created by Bo Li
5689 -- 09-Mar-2010 Fix the bug#9453040 for the recording the bug which
5690 -- length of reference over 150 is not truncated
5691 -- automatically
5692 -- 05-May-2010 Add conditon for the IF clauses
5693 --==========================================================================
5694 PROCEDURE display_vat_invoice_no
5695 ( pn_customer_trx_id IN NUMBER
5696 , pv_excise_invoice_no IN VARCHAR2
5697 , pv_vat_invoice_no IN VARCHAR2
5698 )
5699 IS
5700 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
5701 CURSOR get_st_invoice_cur IS
5702 SELECT st_inv_number
5703 FROM JAI_AR_TRXS
5704 WHERE customer_trx_id = pn_customer_trx_id;
5705 lv_st_inv_number jai_ar_trxs.ST_INV_NUMBER%TYPE;
5706
5707 BEGIN
5708
5709 OPEN get_st_invoice_cur;
5710 FETCH get_st_invoice_cur INTO lv_st_inv_number;
5711 CLOSE get_st_invoice_cur;
5712
5713 update_all_invoice_num(pn_customer_trx_id,
5714 pv_excise_invoice_no,
5715 pv_vat_invoice_no,
5716 lv_st_inv_number);
5717 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
5718 END display_vat_invoice_no;
5719
5720 END jai_ar_match_tax_pkg;
[Home] [Help]