1 PACKAGE BODY jai_ar_match_tax_pkg
2 /* $Header: jai_ar_match_tax.plb 120.19.12010000.3 2009/02/06 12:38:22 jmeena ship $ */
3 AS
4
5 gv_projects_invoices constant varchar2(30) := 'PROJECTS INVOICES'; /* bug#6012570 (5876390) */
6 GV_MODULE_PREFIX CONSTANT VARCHAR2(30) := 'jai_ar_match_tax_pkg'; -- -- Added by Jia Li on tax inclusive computation on 2007/11/30
7
8
9 PROCEDURE process_batch (
10 ERRBUF OUT NOCOPY VARCHAR2,
11 RETCODE OUT NOCOPY VARCHAR2,
12 P_ORG_ID IN NUMBER,
13 p_all_orgs IN Varchar2
14 , p_debug in varchar2 default 'N'
15 , p_called_from IN VARCHAR2 default null /*parameter added for bug#6012570 (5876390)commented by kunkumar for bugno6066813 */
16 -- revoked the comments for 6012570
17 )
18 IS
19 lv_error_mesg VARCHAR2(255);
20 var_cust_trx_id NUMBER;
21 var_prev_cust_trx_id NUMBER(15);
22 var_rowid ROWID;
23 var_tax_amount NUMBER :=0;
24 var_freight_amount NUMBER :=0;
25 var_error_invoice CHAR(1);
26 error_from_called_unit EXCEPTION;
27 var_error_mesg VARCHAR2(1996);
28 v_org_id NUMBER; -- added by sriram - Bug # 2779967
29 lv_source JAI_AR_TRX_INS_LINES_T.source%TYPE ; --rchandan for bug#4428980
30
31 ln_org_id number ; -- Harshita for Bug 5490479
32 lv_debug varchar2(1) ;
33 lv_process_status VARCHAR2(2);
34 lv_process_message VARCHAR2(2000);
35 /*
36 commented by kunkumar for bug#6066813
37 Start, bug#6012570 (5876390)
38 */ -- Ended comments to redo the Project changes, 6012570
39 --Added by JMEENA for bug#8232976
40 cursor c_get_context(cp_customer_trx_id in number) is
41 select interface_header_context
42 from ra_customer_trx_all
43 where customer_trx_id = cp_customer_trx_id;
44 --End bug#8232976
45 lv_invoice_context ra_customer_trx_all.interface_header_context%type;
46 lv_projects_flag varchar2(1);
47 lv_called_from varchar2(30);
48
49 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30
50 ---------------------------------------------------------------
51 lv_inclu_tax_flag jai_ap_tds_years.inclusive_tax_flag%TYPE;
52 ln_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
53
54 CURSOR cur_separate_flag(pn_org_id IN NUMBER) IS
55 SELECT
56 nvl(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
57 FROM
58 jai_ap_tds_years ja
59 WHERE ja.legal_entity_id = pn_org_id
60 AND sysdate between ja.start_date and ja.end_date;
61
62 CURSOR cur_cust_trx_type(pn_customer_trx_id IN NUMBER) IS
63 SELECT
64 cust_trx_type_id
65 FROM
66 ra_customer_trx_all
67 WHERE customer_trx_id = pn_customer_trx_id;
68 ---------------------------------------------------------------
69
70 /* Start, bug#6012570 (5876390)
71 end commented by kunkumar */
72 BEGIN
73
74
75 /* ------------------------------------------------------------------------------------------------------------------------
76 CHANGE HISTORY:
77 S.No DATE Author AND Details
78 ---------------------------------------------------------------------------------------------------------------------------
79 1 04-MAY-2002 Sriram. Procedure Created . This Procedure will be
80 invoked by the concurrent
81 'India Local Concurrent Procedure for processing Order Lines to AR' - JAINMREQ .
82 This will be only applicable for Invoiced Created from Order Entry.
83 2 09-MAY-2002 Sriram. Adding fnd_file.put_line -
84 to write logs from concurrent program.
85 3 24-May-2002 Sriram. Set the Code so that it works in the batch mode .
86 4. 09-JAN-2003 Sriram - Bug # 2740546 - File Version is 615.1
87 Added the substr function to the update statement that updates the
88 JAI_AR_TRX_INS_LINES_T table . If due to some reason the error message is
89 very long ,then it can be a potential problem.Because of this the program
90 should not halt.
91 5. 08/04/2003 Sriram - Bug # 2779967
92 Added logic to see that only records that belong to the current operating unit need
93 to be picked up for processing.This was done because records are inserted into the
94 JAI_AR_TRX_INS_LINES_T table from various 'India Local Receivables' responsibility
95 attached to various org ids , The concurrent program is not scheduled , but run by
96 the user , it picks up the records not only for the current org id but also for other
97 org ids as well which causes the problem.
98
99 6. 22/08/2003 Sriram - Bug # 3068927.
100 Added a new parameter P_ORG_ID to the Procedure. This has been done a new parameter
101 has been added in the concurrent program definition "JAINMREQ" to enable conflict domains.
102 The Concurrent program 'India Local Concurrent For Processing Order Lines to AR" has
103 been set incompatible to itself and also to autoinvoice import program . Because of the
104 previous bugfix , the concurrent has to be scheduled for each org id , hence causing performance
105 bottleneck because until one concurrent program runs , all others have to wait in pending state.
106 Hence , by using the conflict domains concept with the domain as org id , we are ensuring that
107 the concurrent are incompatible to itself only to the extent of those running in the same org id
108
109
110 7. 30/10/2003 Added another parameters P_all_orgs . This parameter is used for indicating whether to process for all
111 org ids or for the org id entered.
112 P_Org_id parameter is set as an optional parameter
113
114 8. 09/03/2004 ssumaith - bug# 3491600 file version 618.1
115
116 incorrect exception handling was done. variable width was smaller than the actual
117 width of the string assigned to the variable. This was causing the exception
118 'numeric or value error.'
119
120 9. 2004/08/11 Aiyer for bug#3826140. Version#115.1
121 Issue:-
122 Lines marked as deleted get reprocessed when a record is submitted for reprocessing from the the India Resubmit Errored OM
123 Tax Records form.
124
125 Reason:-
126 The current procedure previously used to also consider those records which have been marked as deleted.
127
128 Fix:-
129 The cursor temp_rec has been modified to discard all those lines which have been marked as 'R' or 'D'.
130
131 Dependency Due to this Bug:-
132 None
133
134 10. 2004/10/21 Aiyer for bug#3839560. Version#115.2
135 Issue:-
136 India Local Concurrent to Process Order Lines To AR corrrupts data in Base AR tables when two instances of this program
137 are run simultaneously with Process of Orgs = 'Y'
138
139 Reason:-
140 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'
141
142 Fix:-
143 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.
144
145 Dependency Due to this Bug:-
146 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.
147 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
148 ja_ar_rec_process_validate.val_revrec_records(115.0).
149
150 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
151 as required for CASE COMPLAINCE.
152
153 12 14-Jun-2005 rchandan for bug#4428980, Version 116.3
154 Modified the object to remove literals from DML statements and CURSORS.
155
156 13 23-Jun-2005 Ramananda for bug#4468353 ,version 116.4
157 Issue:
158 Impact on IL due to SLA Uptake by AR
159 Reason:
160 India Localization taxes and charges are inserted into RA_CUSTOIMER_TRX_LINES_ALL and
161 RA_CUST_LINES_GL_DIST_ALL, as Tax and Freight lines.Since India Localization directly updates
162 the above-mentioned tables, the accounting happens through the base AR accounting itself.
163 In R12, since the AR accounting will be handled through SLA IL tax lines that are inserted
164 in the RA_CUSTOIMER_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL will be impacted
165 Fix:
166 IL should ensure the following while inserting into RA_CUST_LINES_GL_DIST_ALL table:
167 1. The tax and freight lines that are inserted should be inserted before the associated base item lines
168 are posted to GL. This should be achieved by checking the Posting_Status by IL.
169
170 A new cursor is created to check the gl_posted_date for the base item. If the gl_posted_date is null,
171 then it inserts the tax and freight lines
172
173 2. Each of the Tax and freight lines should carry the same Accounting event information as the base
174 line. Event_Id field should be punched with the value as on the Item line. This value can be derived
175 from the call to 'Event Engine' for each line. IL will call the Event Engine API, and derive the
176 Event_Id for the base item line. This Event_Id will be punched to all the tax and freight lines related
177 to the base item line
178
179 A call is made to ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event) to update the event_id field
180
181 Issue:
182 Impact on IL due to ebTax Uptake by AR
183 Reason:
184 India Localization tax lines are inserted into AR transaction tables with an AR Tax code (Vat_Tax_Id).
185 In R12, the AR tax engine will be replace by ebTax. Due to this, all the tax code related setups will
186 be made in ebTax and not in AR. Since India Localization uses the Vat_Tax_Id for populating the
187 tax lines into the AR transaction tables and it will not uptake ebTax, it would be mandatory for
188 IL to have setups under the ebTax that can be used in the above transactions.
189
190 Fix:
191 Query logic is changed. Instead of querying vat_Tax_id from ar_vat_tax_all , tax_rate_id of zx_rates_b
192 is queried
193
194 14 25-Apr-2007 cbabu for Bug#6012570 (5876390), File Version 120.5 (115.5)
195 FP: Project billing implementation.
196 New concurrent JAINIPTR created for Project taxes to flow into AR and related
197 changes are made in process_batch
198
199 Excise invoice will not be updated in the Referece_field for Project Invoices as it is
200 giving error in the Invoices Form when Queried for Project Invoice
201
202 15. 17-09-2007 sacsethi for Bug#6407648 , File Version 120.3.12000000.3/ 120.11
203
204 Problem - R.TST1203.XB2.QA:INCORRECT IL TAXES ON RMA CM
205 Reason - Variable ln_created_by ,ld_creation_date initialization was missing .
206 Solution - Procedure maintain_applications is modified with initialization.
207
208 16. 18-sep-2007 anujsax for Bug#5636544, File Version 120.11
209 forward porting R11 bug 5629319 into R12 bug 5636544
210
211 17. 26-jan-2008 ssumaith - bug#6776085
212 following changes are done.
213 a. removed the code changes done for bug#5636544
214 b.did the code changes into the mainline for bug#6764386
215
216 18. 28-Jan-2009 CSahoo for bug#7645588, File Version 120.19.12010000.2
217 Issue: TAX ENTRIES ARE NOT VISIBLE IN DISTRIBUTIONS
218 Fix: Modified the code in the process_from_order_line. added the cursor cur_event_id
219 to get the event id. This cursor would get called only in case of a credit memo having
220 accounting rules defined. This would provide the event id of the REC account class.
221 The tax entries also need to be latched to this event id. so passed this event id to the
222 procedure insert_trx_line_gl_dist to get stamped in the table ra_cust_trx_line_gl_dist_all
223 table.
224 19 06-FEB-2009 JMEENA for bug#8232976
225 Created cursor c_get_context and to get the interface_header_context of the invoice and checked if it is PROJECT INVOICE.
226 Future Dependencies For the release Of this Object:-
227 (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/
228 A datamodel change )
229
230 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
231 Current Version Current Bug Dependent Files Version Author Date Remarks
232 Of File On Bug/Patchset Dependent On
233 jai_ar_match_tax_pkg.process_batch
234 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
235 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
236 ja_in_ar_rec_prc_val_s.pls 115.0 Aiyer 21/10/2004 jai_ar_match_tax_pkg.process_from_order_line calls
237 ja_in_ar_rec_prc_val_b.pls 115.0 Aiyer 21/10/2004
238 ja_ar_rec_process_validate.val_revrec_records
239 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
240 /* Bug 5243532. Added by Lakshmi Gopalsami
241 Removed the reference to fnd_profile.value('ORG_ID')
242 v_org_id := FND_PROFILE.VALUE('ORG_ID');
243 */
244 lv_debug := nvl(p_debug,'N');
245 lv_debug := 'Y';
246 fnd_file.put_line(FND_FILE.LOG,' Entering Procedure - jai_ar_match_tax_pkg.process_batch');
247
248 ln_org_id := mo_global.get_current_org_id() ; -- Harshita for Bug 5490479
249 /*commented by kunkumar for bug# 6066813 Start
250 6012570 (5876390) -- Revoked the comments for 6012570*/
251 if p_called_from is null then
252 lv_called_from := 'ORDER_ENTRY';
253 else
254 lv_called_from := p_called_from;
255 end if;
256 -- End commented by kunkumar for 6066813 */ revoked the comments for 6012570
257 if p_all_orgs = 'Y' or p_all_orgs = 'y' then
258 v_org_id := NULL;
259 else
260 v_org_id := ln_org_id; -- p_org_id -- Harshita for Bug 5490479
261 end if;
262
263 fnd_file.put_line(FND_FILE.LOG,' Org id retreived is - ' || v_org_id || ' Generate for All orgs is : ' || p_all_orgs);
264
265 lv_source := 'RAXTRX';
266 FOR temp_rec IN
267 (
268 SELECT DISTINCT customer_trx_id
269 FROM JAI_AR_TRX_INS_LINES_T
270 WHERE source = lv_source
271 AND org_id = nvl(ln_org_id, org_id)
272 MINUS
273 SELECT customer_trx_id
274 FROM JAI_AR_TRX_INS_LINES_T temp_dtl
275 WHERE source = 'RAXTRX'
276 AND org_id = nvl(ln_org_id, org_id)
277 AND error_flag IN ('R','D')
278 )
279 LOOP
280 /*Start commented by kunkumar for bug#6066813
281 -- Start, bug#6012570 (5876390)
282 */ -- Revoked comments for projects 6012570
283 lv_projects_flag := null;
284 lv_invoice_context := null;
285 --Added by JMEENA for bug#8232976
286 open c_get_context(temp_rec.customer_trx_id);
287 fetch c_get_context into lv_invoice_context;
288 close c_get_context;
289 --End bug#8232976
290 lv_projects_flag := is_this_projects_context(lv_invoice_context);
291 if lv_called_from = gv_projects_invoices
292 and lv_projects_flag = jai_constants.no
293 then
294 -- no need to process this customer trx
295 goto continue_with_next;
296
297 elsif lv_called_from <> gv_projects_invoices
298 and lv_projects_flag = jai_constants.yes
299 then
300 -- no need to process this customer trx
301 goto continue_with_next;
302 end if;
303 -- End, bug#6012570 (5876390)
304 -- End commented by kunkumar for bug 6066813*/ -- revoked the comments, 6012570
305 BEGIN
306
307 var_cust_trx_id := temp_rec.customer_trx_id;
308
309 jai_ar_match_tax_pkg.process_from_order_line(
310 temp_rec.customer_trx_id,
311 lv_debug ,
312 lv_process_status ,
313 lv_process_message
314 );
315
316 errbuf := lv_process_message ;
317
318 /*
319 Get the Status of the retcode flag - if it is not 2 it means success
320 else , it means error . On an Error Condition , rollback the transaction -
321 set the error_flag in the table for the
322 CUSTOMER_TRX_ID / LINK_TO_CUST_TRX_LINE_ID
323 COMBINATION to 'R' and err_mesg to
324 to the ERRBUF returned from the procedure.
325 */
326
327 -- The Following lines are for testing exception conditions.
328 -- Forcing an exception to occur and test the behaviour of the program
329
330 /*
331 IF temp_rec.link_to_cust_trx_line_id = 56673 THEN
332 RAISE NO_DATA_FOUND;
333 END IF;
334 */
335
336
337 IF lv_process_message IS NOT NULL THEN
338
339 /*
340 Error has Occured in the jai_ar_match_tax_pkg.process_from_order_line procedure .
341 Rollback all inserts , updates , deletes which have happened in
342 the procedure and update the temp_lines_insert procedure
343 setting the error flag to 'R' and err_mesg to ERRBUF
344 */
345
346 var_error_mesg := 'Error from called unit jai_ar_match_tax_pkg.process_from_order_line';
347 RAISE error_from_called_unit;
348
349 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30, Begin
350 -- TD17-Changed Account Inclusive taxes in AR separately
351 -----------------------------------------------------------------------
352 ELSE
353 -- Check if inclusive taxes needs to be accounted separately
354 OPEN cur_separate_flag(v_org_id);
355 FETCH cur_separate_flag INTO lv_inclu_tax_flag;
356 CLOSE cur_separate_flag;
357
358 OPEN cur_cust_trx_type(temp_rec.customer_trx_id);
359 FETCH cur_cust_trx_type INTO ln_cust_trx_type_id;
360 CLOSE cur_cust_trx_type;
361
362 IF lv_inclu_tax_flag = 'Y'
363 THEN
364 acct_inclu_taxes( pn_customer_trx_id => temp_rec.customer_trx_id
365 , pn_org_id => v_org_id
366 , pn_cust_trx_type_id => ln_cust_trx_type_id
367 , xv_process_flag => lv_process_status
368 , xv_process_message => lv_process_message);
369 END IF;
370
371 IF lv_process_status <> jai_constants.successful
372 THEN
373 RAISE error_from_called_unit;
374 END IF; -- lv_process_status <> 'SS'
375 -----------------------------------------------------------------------
376 -- Added by Jia Li for Tax Inclusive Computations on 2007/11/30, End
377 /* ssumaith bug# 6685976(6766561) */
378 delete from jai_ar_trx_ins_lines_t
379 WHERE customer_trx_id = temp_rec.customer_trx_id;
380
381 END IF;
382
383 EXCEPTION
384 WHEN OTHERS THEN
385 IF var_error_mesg IS NULL THEN
386 -- the exception condition is not because of returned error from inner procedure
387 errbuf := substr(SQLERRM,1,200);
388 var_error_mesg := errbuf || 'Error in loop (not in jai_ar_match_tax_pkg.process_from_order_line procedure) ';
389 END IF;
390
391 ROLLBACK;
392
393 UPDATE JAI_AR_TRX_INS_LINES_T
394 SET ERROR_FLAG = 'R' ,
395 ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
396 WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
397
398
399 COMMIT;
400
401 fnd_file.put_line(FND_FILE.LOG , 'Error - ' || ' When Processing '||
402 temp_rec.customer_trx_id );
403 fnd_file.put_line(FND_FILE.LOG , 'Error is ' || var_error_mesg );
404
405 var_error_invoice := 'Y';
406
407 END;
408
409 <<continue_with_next>>
410 NULL;
411
412 END LOOP;
413
414 -- write here to log the successful processing for last invoice
415
416 IF var_error_invoice <> 'Y' THEN
417 fnd_file.put_line(FND_FILE.LOG, 'Processed Customer_trx_id - ' ||var_cust_trx_id);
418 END IF;
419
420 COMMIT;
421 fnd_file.put_line(FND_FILE.LOG,'Successfully Exiting PROCEDURE - jai_ar_match_tax_pkg.process_batch');
422 EXCEPTION
423 WHEN OTHERS THEN
424 ROLLBACK;
425
426 UPDATE JAI_AR_TRX_INS_LINES_T
427 SET ERROR_FLAG = 'R' ,
428 ERR_MESG = SUBSTR(ERRBUF,1,230) -- substr added by sriram Bug # 2740546
429 WHERE CUSTOMER_TRX_ID = var_cust_trx_id;
430
431 COMMIT;
432
433 var_tax_amount :=0;
434 var_freight_amount :=0;
435
436 ERRBUF := SQLERRM;
437 RETCODE := 2;
438 Fnd_file.put_line(FND_FILE.LOG,'EXCEPTION Occured - ' || ERRBUF || ' WHILE Processing Customer_trx_id - ' || var_cust_trx_id );
439 END process_batch;
440
441 ------------------------------------------------ ---------------------------------------
442 --=========================================================================================--
443 --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all,
444 --ar_receivable_applications_all
445 --=========================================================================================--
446
447 PROCEDURE maintain_mrc( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
448 p_previous_cust_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE DEFAULT NULL,
449 p_called_from IN VARCHAR2,
450 p_process_status OUT NOCOPY VARCHAR2,
451 p_process_message OUT NOCOPY VARCHAR2)
452 IS
453 lv_imported_trx VARCHAR2(10) := 'IMPORTED';
454 ln_gl_dist_id ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE;
455 lv_account_class_rec VARCHAR2(10) := 'REC';
456
457
458 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
459 cp_procedure_name user_procedures.procedure_name%type) IS
460 SELECT 1
461 FROM user_procedures
462 WHERE object_name = cp_object_name
463 AND procedure_name = cp_procedure_name ;
464
465 CURSOR cur_payment_schedule_mrc(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
466 IS
467 SELECT payment_schedule_id
468 FROM ar_payment_schedules_all
469 WHERE customer_trx_id = cp_customer_trx_id;
470
471 --get the cust_trx_line_gl_dist_id for the REC row from ra_cust_trx_line_gl_dist_all
472 CURSOR cur_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
473 IS
474 SELECT cust_trx_line_gl_dist_id
475 FROM ra_cust_trx_line_gl_dist_all
476 WHERE customer_trx_id = cp_customer_trx_id
477 AND account_class = lv_account_class_rec --'REC'
478 AND latest_rec_flag = jai_constants.yes; --'Y';
479
480 /* Ramananda for bug#5219225. */
481 lv_object_name user_procedures.object_name%type ;
482 lv_procedure_name user_procedures.procedure_name%type ;
483 ln_exists NUMBER := 0 ;
484 lv_sqlstmt VARCHAR2(2000) ;
485
486 BEGIN
487 p_process_status := jai_constants.successful;
488 p_process_message := NULL;
489
490 --get the cust_trx_line_gl_dist_id for the REC row from ra_cust_trx_line_gl_dist_all
491 open cur_gl_dist(p_customer_trx_id);
492 fetch cur_gl_dist into ln_gl_dist_id;
493 close cur_gl_dist;
494
495 /* Ramananda for bug#5219225. START. Modified the following if..endif. and the call to be dynamic using execute immediate */
496 lv_object_name := 'AR_MRC_ENGINE' ;
497 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
498
499 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
500 FETCH c_proc_exists INTO ln_exists ;
501 CLOSE c_proc_exists ;
502 IF ln_exists = 1 THEN
503 --Update the mrc data for ra_cust_trx_line_gl_dist_all
504 --This is done, irrespective of whether the transaction_type is CM or Invoice
505 /* Commented for bug# 5219225
506 ar_mrc_engine.maintain_mrc_data(
507 p_event_mode => 'UPDATE',
508 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
509 p_mode => 'SINGLE',
510 p_key_value => ln_gl_dist_id); */
511
512 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
513 p_event_mode => ''UPDATE'',
514 p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
515 p_mode => ''SINGLE'',
516 p_key_value => :1
517 );
518 END; ';
519 EXECUTE IMMEDIATE lv_sqlstmt USING ln_gl_dist_id ;
520
521 --if the program is called from process_imported_invoice
522 IF p_called_from = lv_imported_trx THEN
523 FOR rec_mrc IN cur_payment_schedule_mrc(p_customer_trx_id)
524 LOOP
525 /* Commented for bug# 5219225
526 ar_mrc_engine.maintain_mrc_data(
527 p_event_mode => 'UPDATE',
528 p_table_name => 'AR_PAYMENT_SCHEDULES',
529 p_mode => 'SINGLE',
530 p_key_value => rec_mrc.payment_schedule_id); */
531
532 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
533 p_event_mode => ''UPDATE'',
534 p_table_name => ''AR_PAYMENT_SCHEDULES'',
535 p_mode => ''SINGLE'',
536 p_key_value => :1
537 );
538 END; ';
539 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.payment_schedule_id ;
540 END LOOP;
541 END IF;
542
543 --If the current transaction is a CM
544 if p_previous_cust_trx_id IS NOT NULL THEN
545
546 FOR rec_mrc IN cur_payment_schedule_mrc(p_previous_cust_trx_id)
547 LOOP
548
549 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
550 p_event_mode => ''UPDATE'',
551 p_table_name => ''AR_PAYMENT_SCHEDULES'',
552 p_mode => ''SINGLE'',
553 p_key_value => :1
554 );
555 END; ';
556 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.payment_schedule_id ;
557 END LOOP;
558
559 for rec_ar_appl in
560 ( select receivable_application_id
561 from ar_receivable_applications_all
562 where customer_trx_id = p_customer_trx_id
563 )
564 LOOP
565
566 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
567 p_event_mode => ''UPDATE'',
568 p_table_name => ''AR_RECEIVABLE_APPLICATIONS'',
569 p_mode => ''SINGLE'',
570 p_key_value => :1
571 );
572 END;' ;
573 EXECUTE IMMEDIATE lv_sqlstmt USING rec_ar_appl.receivable_application_id ;
574 END LOOP;
575 END IF;
576 END IF ;
577 /* Ramananda for bug#5219225. END */
578
579 EXCEPTION
580 WHEN OTHERS THEN
581 p_process_status := jai_constants.unexpected_error;
582 p_process_message := SUBSTR(SQLERRM,1,300);
583 END maintain_mrc;
584 --=========================================================================================--
585 --This procedure maintains the history of ar_receivable_applications_all in jai_ar_rec_appl_audits
586 --=========================================================================================--
587
588 PROCEDURE maintain_applications(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
589 p_receivable_application_id IN jai_ar_rec_appl_audits.receivable_application_id%TYPE,
590 p_concurrent_req_num IN NUMBER,
591 p_request_id IN NUMBER,
592 p_operation_type IN VARCHAR2,
593 p_rec_appl_audit_id IN OUT NOCOPY NUMBER,
594 p_process_status OUT NOCOPY VARCHAR2,
595 p_process_message OUT NOCOPY VARCHAR2)
596 IS
597 CURSOR cur_rec_appl_audits_s
598 IS
599 SELECT jai_ar_rec_appl_audits_s.nextval
600 FROM dual;
601
602 ln_created_by jai_ar_payment_audits.created_by%TYPE;
603 ld_creation_date jai_ar_payment_audits.creation_date%TYPE;
604 ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
605 ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
606 ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
607
608 BEGIN
609 p_process_status := jai_constants.successful;
610 p_process_message := NULL;
611
612 --set the values for WHO columns
613 ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
614 ld_last_update_date := SYSDATE;
615 ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
616
617 --In case of operation_type = 'UPDATE', the parameter p_payment_audit_id shall have a value
618 --In case of 'INSERT', the value for parameter p_payment_audit_id shall be null
619 IF p_rec_appl_audit_id IS NULL THEN
620 OPEN cur_rec_appl_audits_s;
621 FETCH cur_rec_appl_audits_s INTO p_rec_appl_audit_id;
622 CLOSE cur_rec_appl_audits_s;
623 END IF;
624
625 IF p_operation_type = 'INSERT' THEN
626
627 -- Date 17/09/2007 by sacsethi for bug 6407648
628 ln_created_by := ln_last_updated_by;
629 ld_creation_date := ld_last_update_date;
630
631 INSERT INTO jai_ar_rec_appl_audits(
632 rec_appl_audit_id,
633 concurrent_req_num,
634 customer_trx_id,
635 receivable_application_id,
636 aapp_old,
637 acctd_aapp_from_old,
638 acctd_aapp_to_old,
639 tapp_old,
640 fapp_old,
641 created_by,
642 creation_date,
643 last_updated_by,
644 last_update_date,
645 last_update_login
646 )
647 SELECT p_rec_appl_audit_id,
648 p_concurrent_req_num,
649 p_customer_trx_id,
650 p_receivable_application_id,
651 amount_applied,
652 acctd_amount_applied_from,
653 acctd_amount_applied_to,
654 tax_applied,
655 freight_applied,
656 ln_created_by,
657 ld_creation_date,
658 ln_last_updated_by,
659 ld_last_update_date,
660 ln_last_update_login
661 FROM ar_receivable_applications_all
662 WHERE customer_trx_id = p_customer_trx_id
663 AND receivable_application_id = p_receivable_application_id;
664
665 ELSIF p_operation_type = 'UPDATE' THEN
666 UPDATE jai_ar_rec_appl_audits a
667 SET (aapp_new,
668 acctd_aapp_applied_from_new,
669 acctd_aapp_applied_to_new,
670 tapp_new,
671 fapplied_new,
672 last_updated_by,
673 last_update_date,
674 last_update_login) =
675 (SELECT amount_applied,
676 acctd_amount_applied_from,
677 acctd_amount_applied_to,
678 tax_applied,
679 freight_applied,
680 ln_last_updated_by,
681 ld_last_update_date,
682 ln_last_update_login
683 FROM ar_receivable_applications_all b
684 WHERE customer_trx_id = a.customer_trx_id
685 AND receivable_application_id = a.receivable_application_id)
686 WHERE customer_trx_id = p_customer_trx_id
687 AND receivable_application_id = p_receivable_application_id
688 AND rec_appl_audit_id = p_rec_appl_audit_id;
689
690 END IF;
691 EXCEPTION
692 WHEN OTHERS THEN
693 p_process_status := jai_constants.unexpected_error;
694 p_process_message := SUBSTR(SQLERRM,1,300);
695 END maintain_applications;
696
697 --=========================================================================================--
698 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
699 --=========================================================================================--
700
701 PROCEDURE maintain_schedules( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
702 p_payment_schedule_id IN ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL,
703 p_cm_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE DEFAULT NULL,
704 p_invoice_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
705 p_concurrent_req_num IN NUMBER,
706 p_request_id IN NUMBER,
707 p_operation_type IN VARCHAR2,
708 p_payment_audit_id IN OUT NOCOPY jai_ar_payment_audits.payment_audit_id%TYPE,
709 p_process_status OUT NOCOPY VARCHAR2,
710 p_process_message OUT NOCOPY VARCHAR2)
711 IS
712
713
714
715
716 CURSOR cur_payment_audits_s
717 IS
718 SELECT jai_ar_payment_audits_s.nextval
719 FROM dual;
720
721 ln_created_by jai_ar_payment_audits.created_by%TYPE;
722 ld_creation_date jai_ar_payment_audits.creation_date%TYPE;
723 ln_last_updated_by jai_ar_payment_audits.last_updated_by%TYPE;
724 ld_last_update_date jai_ar_payment_audits.last_update_date%TYPE;
725 ln_last_update_login jai_ar_payment_audits.last_update_login%TYPE;
726 BEGIN
727 p_process_status := jai_constants.successful;
728 p_process_message := NULL;
729
730 --set the values for WHO columns
731 ln_last_updated_by := TO_NUMBER(fnd_profile.value('USER_ID'));
732 ld_last_update_date := SYSDATE;
733 ln_last_update_login := TO_NUMBER(fnd_profile.value('LOGIN_ID'));
734
735 --In case of operation_type = 'UPDATE', the parameter p_payment_audit_id shall have a value
736 --In case of 'INSERT', the value for parameter p_payment_audit_id shall be null
737 IF p_payment_audit_id IS NULL THEN
738 OPEN cur_payment_audits_s;
739 FETCH cur_payment_audits_s INTO p_payment_audit_id;
740 CLOSE cur_payment_audits_s;
741 END IF;
742
743 IF p_operation_type = 'INSERT' THEN
744
745 ln_created_by := ln_last_updated_by;
746 ld_creation_date := ld_last_update_date;
747
748 INSERT INTO jai_ar_payment_audits(
749 payment_audit_id,
750 concurrent_req_num,
751 payment_schedule_id,
752 cm_customer_trx_id,
753 invoice_customer_trx_id,
754 original_customer_trx_id,
755 ado_old,
756 to_old,
757 fo_old,
758 aapp_old,
759 adr_old,
760 fr_old,
761 tr_old,
762 acctd_adr_old,
763 acred_old,
764 alio_old,
765 status_old,
766 gl_date_closed_old,
767 actual_date_closed_old,
768 created_by,
769 creation_date,
770 last_updated_by,
771 last_update_date,
772 last_update_login
773 )
774 SELECT p_payment_audit_id,
775 p_concurrent_req_num,
776 payment_schedule_id,
777 p_cm_customer_trx_id,
778 p_invoice_customer_trx_id,
779 p_customer_trx_id,
780 amount_due_original,
781 tax_original,
782 freight_original,
783 amount_applied,
784 amount_due_remaining,
785 freight_remaining,
786 tax_remaining,
787 acctd_amount_due_remaining,
788 amount_credited,
789 amount_line_items_original,
790 status,
791 gl_date_closed,
792 actual_date_closed,
793 ln_created_by,
794 ld_creation_date,
795 ln_last_updated_by,
796 ld_last_update_date,
797 ln_last_update_login
798 FROM ar_payment_schedules_all
799 WHERE customer_trx_id = p_customer_trx_id
800 AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id);
801
802 ELSIF p_operation_type = 'UPDATE' THEN
803 UPDATE jai_ar_payment_audits a
804 SET (ado_new,
805 to_new,
806 fo_new,
807 aapp_new,
808 adr_new,
809 fr_new,
810 tr_new,
811 acctd_adr_new,
812 acred_new,
813 alio_new,
814 status_new,
815 gl_date_closed_new,
816 actual_date_closed_new,
817 last_updated_by,
818 last_update_date,
819 last_update_login) =
820 (SELECT amount_due_original,
821 tax_original,
822 freight_original,
823 amount_applied,
824 amount_due_remaining,
825 freight_remaining,
826 tax_remaining,
827 acctd_amount_due_remaining,
828 amount_credited,
829 amount_line_items_original,
830 status,
831 gl_date_closed,
832 actual_date_closed,
833 ln_last_updated_by,
834 ld_last_update_date,
835 ln_last_update_login
836 FROM ar_payment_schedules_all b
837 WHERE customer_trx_id = a.original_customer_trx_id
838 AND payment_schedule_id = a.payment_schedule_id)
839 WHERE original_customer_trx_id = p_customer_trx_id
840 AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id)
841 AND payment_audit_id = p_payment_audit_id;
842
843 END IF;
844 EXCEPTION
845 WHEN OTHERS THEN
846 p_process_status := jai_constants.unexpected_error;
847 p_process_message := SUBSTR(SQLERRM,1,300);
848 END maintain_schedules;
849
850 PROCEDURE insert_trx_line_gl_dist(p_account_class IN ra_cust_trx_line_gl_dist_all.account_class%TYPE,
851 p_account_set_flag IN ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE,
852 p_acctd_amount IN ra_cust_trx_line_gl_dist_all.acctd_amount%TYPE,
853 p_amount IN ra_cust_trx_line_gl_dist_all.amount%TYPE,
854 p_code_combination_id IN ra_cust_trx_line_gl_dist_all.code_combination_id%TYPE,
855 p_cust_trx_line_gl_dist_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE,
856 p_cust_trx_line_salesrep_id IN ra_cust_trx_line_gl_dist_all.cust_trx_line_salesrep_id%TYPE,
857 p_customer_trx_id IN ra_cust_trx_line_gl_dist_all.customer_trx_id%TYPE,
858 p_customer_trx_line_id IN ra_cust_trx_line_gl_dist_all.customer_trx_line_id%TYPE,
859 p_gl_date IN ra_cust_trx_line_gl_dist_all.gl_date%TYPE,
860 p_last_update_date IN ra_cust_trx_line_gl_dist_all.last_update_date%TYPE,
861 p_last_updated_by IN ra_cust_trx_line_gl_dist_all.last_updated_by%TYPE,
862 p_creation_date IN ra_cust_trx_line_gl_dist_all.creation_date%TYPE,
863 p_created_by IN ra_cust_trx_line_gl_dist_all.created_by%TYPE,
864 p_last_update_login IN ra_cust_trx_line_gl_dist_all.last_update_login%TYPE,
865 p_org_id IN ra_cust_trx_line_gl_dist_all.org_id%TYPE,
866 p_percent IN ra_cust_trx_line_gl_dist_all.percent%TYPE,
867 p_posting_control_id IN ra_cust_trx_line_gl_dist_all.posting_control_id%TYPE,
868 p_set_of_books_id IN ra_cust_trx_line_gl_dist_all.set_of_books_id%TYPE,
869 p_seq_id OUT NOCOPY NUMBER,
870 p_process_status OUT NOCOPY VARCHAR2,
871 p_process_message OUT NOCOPY VARCHAR2,
872 p_event_id IN NUMBER DEFAULT NULL) --added for bug#7645588
873 IS
874 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
875 cp_procedure_name user_procedures.procedure_name%type) IS
876 SELECT 1
877 FROM user_procedures
878 WHERE object_name = cp_object_name
879 AND procedure_name = cp_procedure_name ;
880
881 CURSOR cur_gl_seq
882 IS
883 SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
884 FROM dual;
885
886 /* Ramananda for bug#5219225. */
887 lv_object_name user_procedures.object_name%type ;
888 lv_procedure_name user_procedures.procedure_name%type ;
889 ln_exists NUMBER := 0 ;
890 lv_sqlstmt VARCHAR2(2000);
891
892 ln_cust_trx_line_gl_dist_id ra_cust_trx_line_gl_dist_all.cust_trx_line_gl_dist_id%TYPE;
893 BEGIN
894 p_process_status := jai_constants.successful;
895 p_process_message := NULL;
896
897 --get the value for cust_trx_line_gl_dist_id
898 OPEN cur_gl_seq;
899 FETCH cur_gl_seq into ln_cust_trx_line_gl_dist_id;
900 CLOSE cur_gl_seq;
901
902
903
904 INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
905 account_set_flag,
906 acctd_amount,
907 amount,
908 code_combination_id,
909 cust_trx_line_gl_dist_id,
910 cust_trx_line_salesrep_id,
911 customer_trx_id,
912 customer_trx_line_id,
913 gl_date,
914 last_update_date,
915 last_updated_by,
916 creation_date,
917 created_by,
918 last_update_login,
919 org_id,
920 percent,
921 posting_control_id,
922 set_of_books_id,
923 event_id) --added for bug#7645588
924 VALUES(p_account_class,
925 p_account_set_flag,
926 p_acctd_amount,
927 p_amount,
928 p_code_combination_id,
929 ln_cust_trx_line_gl_dist_id,
930 p_cust_trx_line_salesrep_id,
931 p_customer_trx_id,
932 p_customer_trx_line_id,
933 p_gl_date,
934 p_last_update_date,
935 p_last_updated_by,
936 p_creation_date,
937 p_created_by,
938 p_last_update_login,
939 p_org_id,
940 p_percent,
941 p_posting_control_id,
942 p_set_of_books_id,
943 p_event_id); --added for bug#7645588
944
945 /* Ramananda for bug#5219225. START */
946 lv_object_name := 'AR_MRC_ENGINE' ;
947 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
948
949 p_seq_id := ln_cust_trx_line_gl_dist_id ;
950 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
951 FETCH c_proc_exists INTO ln_exists ;
952 CLOSE c_proc_exists ;
953 IF ln_exists = 1 THEN
954
955 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
956 p_event_mode => ''INSERT'',
957 p_table_name => ''RA_CUST_TRX_LINE_GL_DIST'',
958 p_mode => ''SINGLE'',
959 p_key_value => :1
960 );
961 END; ';
962 EXECUTE IMMEDIATE lv_sqlstmt USING ln_cust_trx_line_gl_dist_id ;
963 END IF ;
964 /* Ramananda for bug#5219225. END */
965
966 EXCEPTION
967 WHEN OTHERS THEN
968 p_process_status := jai_constants.unexpected_error;
969 p_process_message := SUBSTR(SQLERRM,1,300);
970 END insert_trx_line_gl_dist;
971 PROCEDURE insert_trx_lines(p_extended_amount IN ra_customer_trx_lines_all.extended_amount%TYPE,
972 p_taxable_amount IN ra_customer_trx_lines_all.taxable_amount%TYPE,
973 p_customer_trx_line_id IN ra_customer_trx_lines_all.customer_trx_line_id%TYPE,
974 p_last_update_date IN ra_customer_trx_lines_all.last_update_date%TYPE,
975 p_last_updated_by IN ra_customer_trx_lines_all.last_updated_by%TYPE,
976 p_creation_date IN ra_customer_trx_lines_all.creation_date%TYPE,
977 p_created_by IN ra_customer_trx_lines_all.created_by%TYPE,
978 p_last_update_login IN ra_customer_trx_lines_all.last_update_login%TYPE,
979 p_customer_trx_id IN ra_customer_trx_lines_all.customer_trx_id%TYPE,
980 p_line_number IN ra_customer_trx_lines_all.line_number%TYPE,
981 p_set_of_books_id IN ra_customer_trx_lines_all.set_of_books_id%TYPE,
982 p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE,
983 p_line_type IN ra_customer_trx_lines_all.line_type%TYPE,
984 p_org_id IN ra_customer_trx_lines_all.org_id%TYPE,
985 p_uom_code IN ra_customer_trx_lines_all.uom_code%TYPE,
986 p_autotax IN ra_customer_trx_lines_all.autotax%TYPE,
987 p_vat_tax_id IN ra_customer_trx_lines_all.vat_tax_id%TYPE,
988 p_interface_line_context IN ra_customer_trx_lines_all.interface_line_context%TYPE,
989 p_interface_line_attribute6 IN ra_customer_trx_lines_all.interface_line_attribute6%TYPE,
990 p_interface_line_attribute3 IN ra_customer_trx_lines_all.interface_line_attribute3%TYPE,
991 p_process_status OUT NOCOPY VARCHAR2,
992 p_process_message OUT NOCOPY VARCHAR2)
993 IS
994 BEGIN
995 p_process_status := jai_constants.successful;
996 p_process_message := NULL;
997
998 INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
999 taxable_amount,
1000 customer_trx_line_id,
1001 last_update_date,
1002 last_updated_by,
1003 creation_date,
1004 created_by,
1005 last_update_login,
1006 customer_trx_id,
1007 line_number,
1008 set_of_books_id,
1009 link_to_cust_trx_line_id,
1010 line_type,
1011 org_id,
1012 uom_code,
1013 autotax,
1014 vat_tax_id,
1015 interface_line_context,
1016 interface_line_attribute6,
1017 interface_line_attribute3)
1018 VALUES ( p_extended_amount,
1019 p_taxable_amount,
1020 p_customer_trx_line_id,
1021 p_last_update_date,
1022 p_last_updated_by,
1023 p_creation_date,
1024 p_created_by,
1025 p_last_update_login,
1026 p_customer_trx_id,
1027 p_line_number,
1028 p_set_of_books_id,
1029 p_link_to_cust_trx_line_id,
1030 p_line_type,
1031 p_org_id,
1032 p_uom_code,
1033 p_autotax,
1034 p_vat_tax_id,
1035 p_interface_line_context,
1036 p_interface_line_attribute6,
1037 p_interface_line_attribute3);
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 p_process_status := jai_constants.unexpected_error;
1041 p_process_message := SUBSTR(SQLERRM,1,300);
1042 END insert_trx_lines;
1043
1044
1045 PROCEDURE delete_trx_data(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1046 p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE DEFAULT NULL,
1047 p_process_status OUT NOCOPY VARCHAR2,
1048 p_process_message OUT NOCOPY VARCHAR2)
1049 IS
1050 /* Ramananda for bug#5219225. */
1051 lv_object_name user_procedures.object_name%type ;
1052 lv_procedure_name user_procedures.procedure_name%type ;
1053 ln_exists NUMBER := 0 ;
1054 lv_sqlstmt VARCHAR2(2000) ;
1055 lv_account_class_tax VARCHAR2(10) := 'TAX';
1056 lv_account_class_freight VARCHAR2(10) := 'FREIGHT';
1057
1058 --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
1059 --and account_class in ('TAX','FREIGHT')
1060 CURSOR cur_total_amt_gl_dist( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1061 IS
1062 SELECT NVL(SUM(amount),0) amount,
1063 NVL(SUM(acctd_amount),0) acctd_amount,
1064 MAX(acctd_amount) max_acctd_amount
1065 FROM ra_cust_trx_line_gl_dist_all
1066 WHERE customer_trx_id = cp_customer_trx_id
1067 AND account_class IN (lv_account_class_tax,lv_account_class_freight);
1068
1069 --get the data from JAI_AR_TRX_INS_LINES_T for customer_trx_id and link_to_cust_trx_line_id
1070 CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1071 cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
1072 IS
1073 SELECT *
1074 FROM JAI_AR_TRX_INS_LINES_T
1075 WHERE customer_trx_id = cp_customer_trx_id
1076 AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1077 ORDER BY link_to_cust_trx_line_id,
1078 customer_trx_line_id;
1079
1080
1081 /* Ramananda for bug#5219225. */
1082 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1083 cp_procedure_name user_procedures.procedure_name%type) IS
1084 SELECT 1
1085 FROM user_procedures
1086 WHERE object_name = cp_object_name
1087 AND procedure_name = cp_procedure_name ;
1088
1089 BEGIN
1090 p_process_status := jai_constants.successful;
1091 p_process_message := NULL;
1092
1093 /* Ramananda for bug#5219225. START */
1094 lv_object_name := 'AR_MRC_ENGINE' ;
1095 lv_procedure_name := 'MAINTAIN_MRC_DATA' ;
1096
1097 OPEN c_proc_exists(lv_object_name, lv_procedure_name) ;
1098 FETCH c_proc_exists INTO ln_exists ;
1099 CLOSE c_proc_exists ;
1100 IF ln_exists = 1 THEN
1101 --delete the mrc data from ra_cust_trx_line_gl_dist_all
1102 FOR rec_mrc IN
1103 ( SELECT cust_trx_line_gl_dist_id
1104 FROM ra_cust_trx_line_gl_dist_all
1105 WHERE customer_trx_id = p_customer_trx_id
1106 AND account_class IN ('TAX','FREIGHT')
1107 AND customer_trx_line_id IN
1108 (SELECT customer_trx_line_id
1109 FROM ra_customer_trx_lines_all
1110 WHERE customer_trx_id = p_customer_trx_id
1111 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1112 AND line_type in ('TAX','FREIGHT')
1113 )
1114 )
1115 LOOP
1116
1117 lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
1118 p_event_mode =>''DELETE'',
1119 p_table_name =>''RA_CUST_TRX_LINE_GL_DIST'',
1120 p_mode =>''SINGLE'',
1121 p_key_value => :1
1122 );
1123
1124 END; ' ;
1125 EXECUTE IMMEDIATE lv_sqlstmt USING rec_mrc.cust_trx_line_gl_dist_id ;
1126 END LOOP;
1127 END IF ;
1128 /* Ramananda for bug#5219225. END */
1129
1130 --delete the data from ra_cust_trx_line_gl_dist_all
1131 DELETE ra_cust_trx_line_gl_dist_all
1132 WHERE customer_trx_id = p_customer_trx_id
1133 AND account_class IN ('TAX','FREIGHT')
1134 AND customer_trx_line_id IN
1135 (SELECT customer_trx_line_id
1136 FROM ra_customer_trx_lines_all
1137 WHERE customer_trx_id = p_customer_trx_id
1138 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1139 AND line_type in ('TAX','FREIGHT')
1140 );
1141
1142 --delete the data from ra_customer_trx_lines_all
1143 DELETE ra_customer_trx_lines_all
1144 WHERE customer_trx_id = p_customer_trx_id
1145 AND link_to_cust_trx_line_id = NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1146 AND line_type IN ('TAX','FREIGHT');
1147
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 p_process_status := jai_constants.unexpected_error;
1151 p_process_message := SUBSTR(SQLERRM,1,300);
1152 END delete_trx_data;
1153
1154
1155 PROCEDURE process_from_order_line( p_customer_trx_id IN NUMBER,
1156 p_debug IN VARCHAR2 DEFAULT 'N',
1157 p_process_status OUT NOCOPY VARCHAR2,
1158 p_process_message OUT NOCOPY VARCHAR2)
1159 IS
1160
1161
1162 v_org_id number;
1163 lv_object_name user_procedures.object_name%type ;
1164 lv_procedure_name user_procedures.procedure_name%type ;
1165 ln_exists NUMBER := 0 ;
1166 lv_sqlstmt VARCHAR2(2000) ;
1167 lv_account_class_tax VARCHAR2(10) := 'TAX';
1168 lv_account_class_freight VARCHAR2(10) := 'FREIGHT';
1169 lv_loc_tax_code VARCHAR2(20) := 'Localization';
1170 lv_line_type_line VARCHAR2(10) := 'LINE';
1171 ld_gl_posted_date RA_CUST_TRX_LINE_GL_DIST_ALL.gl_posted_date%type ;
1172 lv_account_class_rec VARCHAR2(10) := 'REC';
1173 l_xla_event arp_xla_events.xla_events_type;
1174 ln_gl_seq Number;
1175 imported_trx VARCHAR2(10) := 'IMPORTED';
1176
1177 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
1178 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
1179 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
1180
1181 localization_tax_not_defined EXCEPTION;
1182 Item_lines_already_accounted EXCEPTION; /* Ramanand for SLA Uptake */
1183 rounding_account_not_defined EXCEPTION;
1184 resource_busy EXCEPTION;
1185
1186 --get the allow_overapplication_flag from ra_cust_trx_types_all for cust_trx_type_id
1187 CURSOR cur_trx_types( cp_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%TYPE)
1188 IS
1189 SELECT allow_overapplication_flag
1190 FROM ra_cust_trx_types_all
1191 WHERE cust_trx_type_id = cp_cust_trx_type_id;
1192
1193 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
1194 CURSOR cur_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1195 cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
1196 IS
1197 SELECT payment_schedule_id,
1198 term_id,
1199 terms_sequence_number,
1200 amount_line_items_original,
1201 amount_line_items_remaining,
1202 tax_original,
1203 tax_remaining,
1204 freight_original,
1205 amount_due_remaining
1206 FROM ar_payment_schedules_all
1207 WHERE customer_trx_id = cp_customer_trx_id
1208 AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
1209
1210 --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
1211 CURSOR cur_total_amt_trx_lines( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1212 cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE DEFAULT NULL,
1213 cp_line_type ra_customer_trx_lines_all.line_type%TYPE)
1214 IS
1215 SELECT NVL(SUM(extended_amount),0) extended_amount,
1216 NVL(SUM(taxable_amount),0) taxable_amount
1217 FROM ra_customer_trx_lines_all
1218 WHERE customer_trx_id = cp_customer_trx_id
1219 AND customer_trx_line_id = NVL(cp_customer_trx_line_id, customer_trx_line_id)
1220 AND line_type = cp_line_type;
1221
1222
1223
1224 CURSOR c_gl_posted_date_cur(p_customer_trx_line_id RA_CUST_TRX_LINE_GL_DIST_ALL.customer_trx_line_id%type) IS
1225 SELECT gl_posted_date
1226 from RA_CUST_TRX_LINE_GL_DIST_ALL
1227 where customer_trx_line_id = p_customer_trx_line_id
1228 and account_class = 'REC'
1229 and latest_rec_flag = 'Y';
1230
1231 --get the accounting_rule_id from ra_customer_trx_lines_all for customer_trx_line_id
1232 CURSOR accounting_set_cur(cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
1233 IS
1234 SELECT accounting_rule_id
1235 FROM ra_customer_trx_lines_all
1236 WHERE customer_trx_line_id = cp_customer_trx_line_id;
1237
1238 /* Ramananda for bug#5219225. */
1239 CURSOR c_proc_exists(cp_object_name user_procedures.object_name%type,
1240 cp_procedure_name user_procedures.procedure_name%type) IS
1241 SELECT 1
1242 FROM user_procedures
1243 WHERE object_name = cp_object_name
1244 AND procedure_name = cp_procedure_name ;
1245
1246 --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
1247 --and account_class in ('TAX','FREIGHT')
1248
1249 CURSOR cur_total_amt_gl_dist( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1250 IS
1251 SELECT NVL(SUM(amount),0) amount,
1252 NVL(SUM(acctd_amount),0) acctd_amount,
1253 MAX(acctd_amount) max_acctd_amount
1254 FROM ra_cust_trx_line_gl_dist_all
1255 WHERE customer_trx_id = cp_customer_trx_id
1256 AND account_class IN (lv_account_class_tax,lv_account_class_freight);
1257
1258 --get the data from JAI_AR_TRX_INS_LINES_T for customer_trx_id and link_to_cust_trx_line_id
1259 CURSOR cur_temp_lines_insert( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1260 cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL)
1261 IS
1262 SELECT *
1263 FROM JAI_AR_TRX_INS_LINES_T
1264 WHERE customer_trx_id = cp_customer_trx_id
1265 AND link_to_cust_trx_line_id = NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
1266 ORDER BY link_to_cust_trx_line_id,
1267 customer_trx_line_id;
1268
1269 --get the data from ra_cust_trx_line_gl_dist_all for customer_trx_id and account_class = 'REC'
1270 CURSOR cur_gl_date(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1271 IS
1272 SELECT gl_date
1273 FROM ra_cust_trx_line_gl_dist_all
1274 WHERE customer_trx_id = cp_customer_trx_id
1275 AND account_class = 'REC'
1276 AND latest_rec_flag = 'Y';
1277
1278 --get the currency precision from fnd_currencies for the set_of_books_id
1279 CURSOR cur_curr_precision(cp_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE)
1280 IS
1281 SELECT NVL(minimum_accountable_unit,NVL(precision,2))
1282 FROM fnd_currencies
1283 WHERE currency_code IN
1284 (
1285 SELECT Currency_code
1286 FROM gl_sets_of_books
1287 WHERE set_of_books_id = cp_set_of_books_id
1288 );
1289
1290 --get the data from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type = 'LINE'
1291 CURSOR cur_customer_trx_lines(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1292 cp_customer_trx_line_id ra_customer_trx_lines_all.customer_trx_line_id%TYPE)
1293 IS
1294 SELECT interface_line_attribute6,
1295 interface_line_attribute3,
1296 interface_line_context,
1297 NVL(extended_amount,0) extended_amount,
1298 NVL(taxable_amount,0) taxable_amount
1299 FROM ra_customer_trx_lines_all
1300 WHERE customer_trx_id = cp_customer_trx_id
1301 AND customer_trx_line_id = cp_customer_trx_line_id
1302 AND line_type = lv_line_type_line; --'LINE'
1303
1304
1305 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
1306 CURSOR cur_min_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1307 IS
1308 SELECT MIN(payment_schedule_id) payment_schedule_id,
1309 MIN(term_id) term_id
1310 FROM ar_payment_schedules_all
1311 WHERE customer_trx_id = cp_customer_trx_id;
1312
1313
1314 --get the data from ra_customer_trx_all for a customer_trx_id
1315 CURSOR cur_customer_trx(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1316 IS
1317 SELECT org_id,
1318 NVL(exchange_rate,1) exchange_rate,
1319 trx_number,
1320 cust_trx_type_id,
1321 created_from,
1322 set_of_books_id,
1323 previous_customer_trx_id
1324 FROM ra_customer_trx_all
1325 WHERE customer_trx_id = cp_customer_trx_id;
1326
1327
1328 --Lock all the rows from JAI_AR_TRX_INS_LINES_T for a customer_trx_id, which are to be processed
1329 CURSOR cur_lock_temp(cp_customer_trx_id JAI_AR_TRX_INS_LINES_T.customer_trx_id%TYPE)
1330 IS
1331 SELECT *
1332 FROM JAI_AR_TRX_INS_LINES_T
1333 WHERE customer_trx_id = cp_customer_trx_id
1334 FOR UPDATE NOWAIT;
1335
1336
1337 --Get the first_installment_code and base_amount from ra_terms
1338 CURSOR cur_term_details(cp_term_id ra_terms.term_id%TYPE)
1339 IS
1340 SELECT first_installment_code,
1341 DECODE(base_amount, 0, 1, base_amount) base_amount
1342 FROM ra_terms
1343 WHERE term_id = cp_term_id;
1344
1345
1346 --Get the relative_amount from ra_terms_lines
1347 CURSOR cur_term_lines(cp_term_id ra_terms_lines.term_id%TYPE,
1348 cp_sequence_num ra_terms_lines.sequence_num%TYPE)
1349 IS
1350 SELECT relative_amount
1351 FROM ra_terms_lines
1352 WHERE term_id = cp_term_id
1353 AND sequence_num = cp_sequence_num;
1354
1355
1356 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
1357 CURSOR cur_tot_amt_for_cms(cp_applied_customer_trx_id ar_receivable_applications_all.applied_customer_trx_id%TYPE,
1358 cp_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE)
1359 IS
1360 SELECT NVL(SUM(amount),0) amount
1361 FROM ra_cust_trx_line_gl_dist_all
1362 WHERE customer_trx_id IN
1363 (
1364 SELECT customer_trx_id
1365 FROM ar_receivable_applications_all
1366 WHERE applied_customer_trx_id = cp_applied_customer_trx_id
1367 AND application_type = 'CM'
1368 AND display = 'Y'
1369 AND status = 'APP'
1370 )
1371 AND account_class = cp_account_class;
1372
1373
1374 --Get the SUM of tax_applied and freight_applied from ar_receivable_applications_all for Cash receipts applied
1375 CURSOR cur_tot_cash_rcpt(cp_applied_customer_trx_id ar_receivable_applications_all.applied_customer_trx_id%TYPE)
1376 IS
1377 SELECT NVL(sum(tax_applied),0) tax_applied,
1378 NVL(sum(freight_applied),0) freight_applied
1379 FROM ar_receivable_applications_all
1380 WHERE applied_customer_trx_id = cp_applied_customer_trx_id
1381 AND application_type = 'CASH'
1382 AND display = 'Y'
1383 AND status = 'APP';
1384
1385
1386 --Get the SUM of line_applied from ar_receivable_applications_all for CM
1387 CURSOR cur_tot_recv_appl( cp_applied_customer_Trx_id ar_receivable_applications_all.applied_customer_Trx_id%TYPE,
1388 cp_applied_payment_Schedule_id ar_receivable_applications_all.applied_payment_Schedule_id%TYPE)
1389 IS
1390 SELECT NVL(sum(line_applied),0) line_applied
1391 FROM ar_receivable_applications_all
1392 WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
1393 AND application_type = 'CM'
1394 AND display = 'Y'
1395 and status = 'APP'
1396 AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
1397
1398
1399 --Get the receivable_application_id from ar_receivable_applications_all for the Invoice's payment_schedule_id
1400 CURSOR cur_recv_appl_id(cp_applied_customer_Trx_id ar_receivable_applications_all.applied_customer_Trx_id%TYPE,
1401 cp_customer_trx_id ar_receivable_applications_all.customer_trx_id%TYPE,
1402 cp_applied_payment_Schedule_id ar_receivable_applications_all.applied_payment_Schedule_id%TYPE)
1403 IS
1404 SELECT receivable_application_id
1405 FROM ar_receivable_applications_all
1406 WHERE applied_customer_Trx_id = cp_applied_customer_Trx_id
1407 AND customer_trx_id = cp_customer_trx_id
1408 AND application_type = 'CM'
1409 AND display = 'Y'
1410 and status = 'APP'
1411 AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
1412
1413 --Get the data from ar_payment_schedules_all for customer_trx_id
1414 CURSOR cur_prev_payment_schedule( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1415 cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
1416 IS
1417 SELECT amount_line_items_original,
1418 amount_line_items_remaining,
1419 tax_original,
1420 tax_remaining,
1421 freight_original,
1422 amount_due_remaining
1423 FROM ar_payment_schedules_all
1424 WHERE customer_trx_id = cp_customer_trx_id
1425 AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
1426
1427 CURSOR ORG_CUR IS
1428 SELECT ORG_ID, CREATED_FROM
1429 FROM RA_CUSTOMER_TRX_ALL
1430 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
1431
1432 --added the cursor for bug#7645588
1433 CURSOR cur_event_id (cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1434 IS
1435 SELECT event_id
1436 FROM ra_cust_trx_line_gl_dist_all
1437 WHERE customer_trx_id = cp_customer_trx_id
1438 AND account_class = 'REC'
1439 AND latest_rec_flag = 'Y'
1440 AND account_set_flag = 'N' ;
1441
1442 --Start Addition by anujsax for Bug#5636544
1443 CURSOR cur_excise_invoice_number(cp_customer_trx_id NUMBER)
1444 IS
1445 SELECT excise_invoice_no
1446 FROM JAI_AR_TRX_LINES
1447 WHERE customer_trx_id = cp_customer_trx_id
1448 AND excise_invoice_no is NOT NULL;
1449
1450 r_excise_invoice_number cur_excise_invoice_number%ROWTYPE;
1451 lv_errbuf VARCHAR2(4000);
1452 lv_retcode VARCHAR2(10);
1453 --End of addition by Anujsax for Bug#5636544
1454
1455 rec_customer_trx cur_customer_trx%ROWTYPE;
1456 rec_inv_customer_trx cur_customer_trx%ROWTYPE;
1457 rec_min_payment_schedule cur_min_payment_schedule%ROWTYPE;
1458 rec_inv_payment_schedule cur_min_payment_schedule%ROWTYPE;
1459 rec_customer_trx_lines cur_customer_trx_lines%ROWTYPE;
1460 rec_term_details cur_term_details%ROWTYPE;
1461 rec_prev_payment_schedule cur_prev_payment_schedule%ROWTYPE;
1462
1463 ln_previous_customer_trx_id ra_customer_trx_lines_all.previous_customer_trx_id%TYPE;
1464 ln_inv_curr_conv_rate ra_customer_trx_all.exchange_rate%TYPE;
1465 ln_cm_curr_conv_rate ra_customer_trx_all.exchange_rate%TYPE;
1466 ld_gl_date ra_cust_trx_line_gl_dist_all.gl_date%TYPE;
1467 ln_vat_tax_id ar_vat_tax_all.vat_tax_id%TYPE;
1468 lv_amount_includes_tax_flag ar_vat_tax_all.amount_includes_tax_flag%TYPE;
1469 lv_account_Set_flag ra_cust_trx_line_gl_dist_all.account_set_flag%TYPE;
1470 ln_precision fnd_currencies.precision%TYPE;
1471 ln_accounting_rule_id NUMBER;
1472 ln_old_amount NUMBER;
1473 ln_taxable_amount NUMBER;
1474 ln_tax_amt NUMBER;
1475 ln_tax_acctd_amount NUMBER;
1476 ln_max_tax_acctd_amount NUMBER;
1477 ln_old_acctd_amount NUMBER;
1478 ln_adjusted_tax NUMBER;
1479 ln_diff_tax_frt NUMBER;
1480 ln_total_tax_amt_for_inv NUMBER;
1481 ln_total_frt_amt_for_inv NUMBER;
1482 ln_inst_tax_amt_for_inv NUMBER;
1483 ln_inst_frt_amt_for_inv NUMBER;
1484 ln_relative_amt NUMBER;
1485 ln_recv_appln_id NUMBER;
1486 ln_tax_amt_cms NUMBER;
1487 ln_frt_amt_cms NUMBER;
1488 ln_apportion_factor NUMBER;
1489 lv_allow_overappln_flag VARCHAR2(10);
1490 ln_amt_due_rem NUMBER;
1491 ln_line_applied NUMBER;
1492 ln_frt_amt_cashrcpt NUMBER;
1493 ln_tax_amt_cashrcpt NUMBER;
1494 ln_payment_audit_id jai_ar_payment_audits.payment_audit_id%TYPE;
1495 ln_rec_appl_audit_id jai_ar_rec_appl_audits.rec_appl_audit_id%TYPE;
1496 lv_process_status VARCHAR2(2);
1497 lv_process_message VARCHAR2(1000);
1498 v_upd_created_from VARCHAR2(15);
1499 ln_event_id NUMBER; --added for bug#7645588
1500 BEGIN
1501
1502 fnd_file.put_line(FND_FILE.LOG, 'START process_imported_invoice');
1503
1504 --Lock all the rows from JAI_AR_TRX_INS_LINES_T for a customer_trx_id, which are to be processed
1505 FOR i IN cur_lock_temp(p_customer_trx_id)
1506 LOOP
1507 EXIT;
1508 END LOOP;
1509
1510 --get the data from ra_customer_trx_all for a customer_trx_id
1511 OPEN cur_customer_trx(p_customer_trx_id);
1512 FETCH cur_customer_trx INTO rec_customer_trx;
1513 CLOSE cur_customer_trx;
1514
1515 OPEN ORG_CUR;
1516 FETCH ORG_CUR INTO v_org_id, v_upd_created_from;
1517 CLOSE ORG_CUR;
1518
1519 --If ln_previous_customer_trx_id is not null, then current transaction is a CM
1520 ln_previous_customer_trx_id := rec_customer_trx.previous_customer_trx_id;
1521
1522 IF p_debug = 'Y' THEN
1523 fnd_file.put_line(FND_FILE.LOG, 'Before pre_validation');
1524 END IF;
1525
1526 --do the basic validations before processing the transaction
1527 jai_ar_validate_data_pkg.pre_validation
1528 ( p_customer_trx_id => p_customer_trx_id,
1529 p_process_status => lv_process_status,
1530 p_process_message => lv_process_message);
1531
1532 IF p_debug = 'Y' THEN
1533 fnd_file.put_line(FND_FILE.LOG, 'Before pre_validation');
1534 END IF;
1535
1536 IF lv_process_status <> jai_constants.successful THEN
1537 p_process_status := lv_process_status;
1538 p_process_message := lv_process_message;
1539 goto EXIT_POINT;
1540 END IF;
1541
1542 --If it is CM
1543 IF ln_previous_customer_trx_id IS NOT NULL THEN
1544 --get the data from ra_customer_trx_all for the Invoice
1545 OPEN cur_customer_trx(ln_previous_customer_trx_id);
1546 FETCH cur_customer_trx INTO rec_inv_customer_trx;
1547 CLOSE cur_customer_trx;
1548
1549 ln_inv_curr_conv_rate := rec_inv_customer_trx.exchange_rate;
1550 ln_cm_curr_conv_rate := rec_customer_trx.exchange_rate;
1551 ELSE
1552 ln_inv_curr_conv_rate := rec_customer_trx.exchange_rate;
1553 END IF;
1554
1555 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
1556 OPEN cur_min_payment_schedule(p_customer_trx_id);
1557 FETCH cur_min_payment_schedule INTO rec_min_payment_schedule;
1558 CLOSE cur_min_payment_schedule;
1559
1560
1561 --get the currency precision from fnd_currencies for the set_of_books_id
1562 OPEN cur_curr_precision(rec_customer_trx.set_of_books_id);
1563 FETCH cur_curr_precision INTO ln_precision;
1564 CLOSE cur_curr_precision;
1565
1566 IF p_debug = 'Y' THEN
1567 fnd_file.put_line(FND_FILE.LOG, 'Before delete_trx_data');
1568 END IF;
1569
1570 --This procedure deletes the data from ra_cust_trx_line_gl_dist_all, ra_customer_trx_lines_all
1571 --Also deletes the MRC data from ra_cust_trx_line_gl_dist
1572 delete_trx_data(p_customer_trx_id => p_customer_trx_id,
1573 p_process_status => lv_process_status,
1574 p_process_message => lv_process_message);
1575
1576 IF p_debug = 'Y' THEN
1577 fnd_file.put_line(FND_FILE.LOG, 'After delete_trx_data');
1578 END IF;
1579
1580 IF lv_process_status <> jai_constants.successful THEN
1581 p_process_status := lv_process_status;
1582 p_process_message := lv_process_message;
1583 goto EXIT_POINT;
1584 END IF;
1585
1586 --get the data from ra_cust_trx_line_gl_dist_all for customer_trx_id and account_class = 'REC'
1587 OPEN cur_gl_date(p_customer_trx_id);
1588 FETCH cur_gl_date INTO ld_gl_date;
1589 CLOSE cur_gl_date;
1590
1591
1592
1593 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
1594 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
1595 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
1596 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
1597
1598 OPEN jai_ar_trx_pkg.c_party_tax_profile_id_cur(V_ORG_ID);
1599 FETCH jai_ar_trx_pkg.c_party_tax_profile_id_cur INTO ln_party_tax_profile_id;
1600 CLOSE jai_ar_trx_pkg.c_party_tax_profile_id_cur ;
1601
1602 OPEN jai_ar_trx_pkg.c_tax_rate_id_cur(lv_tax_regime_code, ln_party_tax_profile_id);
1603 FETCH jai_ar_trx_pkg.c_tax_rate_id_cur INTO ln_tax_rate_id;
1604 CLOSE jai_ar_trx_pkg.c_tax_rate_id_cur ;
1605
1606 if ln_tax_rate_id is null then
1607 raise Localization_tax_not_defined;
1608 end if;
1609 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
1610
1611
1612 IF p_debug = 'Y' THEN
1613 fnd_file.put_line(FND_FILE.LOG, 'Before cur_temp_lines_insert LOOP');
1614 END IF;
1615
1616 FOR rec_temp IN cur_temp_lines_insert(p_customer_trx_id)
1617 LOOP
1618
1619 --get the accounting_rule_id from ra_customer_trx_lines_all for customer_trx_line_id
1620 OPEN accounting_set_cur(rec_temp.link_to_cust_trx_line_id);
1621 FETCH accounting_set_cur INTO ln_accounting_rule_id;
1622 CLOSE accounting_set_cur;
1623
1624 IF ln_accounting_rule_id IS NOT NULL THEN
1625 -- Added the IF condition for bug#7645588, start
1626 IF ln_previous_customer_trx_id IS NOT NULL THEN --it is a CM transaction
1627 lv_account_Set_flag := 'N';
1628 ELSE
1629 lv_account_Set_flag := 'Y';
1630 END IF;
1631 --bug#7645588, end
1632 ELSE
1633 lv_account_Set_flag := 'N';
1634 END IF;
1635
1636 --added the following IF block for bug#7645588
1637 IF ln_accounting_rule_id is not null and ln_previous_customer_trx_id IS NOT NULL then
1638 open cur_event_id (p_customer_trx_id);
1639 Fetch cur_event_id into ln_event_id;
1640 close cur_event_id;
1641 ELSE
1642 ln_event_id := null;
1643 END if;
1644 -- bug#7645588, end
1645
1646
1647 --get the data from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type = 'LINE'
1648 OPEN cur_customer_trx_lines(p_customer_trx_id,
1649 rec_temp.link_to_cust_trx_line_id);
1650 FETCH cur_customer_trx_lines INTO rec_customer_trx_lines;
1651 CLOSE cur_customer_trx_lines;
1652
1653 IF rec_temp.insert_update_flag IN('U','X') THEN
1654
1655 IF lv_amount_includes_tax_flag = 'Y' then
1656 ln_taxable_amount := rec_customer_trx_lines.extended_amount - rec_temp.extended_amount;
1657 ELSE
1658 ln_taxable_amount := rec_customer_trx_lines.extended_amount;
1659 END IF;
1660
1661 IF p_debug = 'Y' THEN
1662 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_lines');
1663 END IF;
1664
1665
1666 -- #### IMPORTANT FOR R12
1667 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, start */
1668 OPEN c_gl_posted_date_cur( rec_temp.link_to_cust_trx_line_id);
1669 FETCH c_gl_posted_date_cur INTO ld_gl_posted_date ;
1670 CLOSE c_gl_posted_date_cur ;
1671
1672 IF ld_gl_posted_date is NULL THEN
1673
1674 -- #### IMPORTANT FOR R12 ENDS HERE
1675
1676 --This procedure inserts the data into ra_customer_trx_lines_all
1677 insert_trx_lines(p_extended_amount => rec_temp.extended_amount,
1678 p_taxable_amount => ln_taxable_amount,
1679 p_customer_trx_line_id => rec_temp.customer_trx_line_id,
1680 p_last_update_date => rec_temp.last_update_date,
1681 p_last_updated_by => rec_temp.last_updated_by,
1682 p_creation_date => rec_temp.creation_date,
1683 p_created_by => rec_temp.created_by,
1684 p_last_update_login => rec_temp.last_update_login,
1685 p_customer_trx_id => rec_temp.customer_trx_id,
1686 p_line_number => rec_temp.line_number,
1687 p_set_of_books_id => rec_temp.set_of_books_id,
1688 p_link_to_cust_trx_line_id => rec_temp.link_to_cust_trx_line_id,
1689 p_line_type => rec_temp.line_type,
1690 p_org_id => rec_customer_trx.org_id,
1691 p_uom_code => rec_temp.uom_code,
1692 p_autotax => 'N',
1693 p_vat_tax_id => ln_vat_tax_id,
1694 p_interface_line_context => rec_customer_trx_lines.interface_line_context,
1695 p_interface_line_attribute6 => rec_customer_trx_lines.interface_line_attribute6,
1696 p_interface_line_attribute3 => rec_customer_trx_lines.interface_line_attribute3,
1697 p_process_status => lv_process_status,
1698 p_process_message => lv_process_message);
1699
1700 IF p_debug = 'Y' THEN
1701 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_lines');
1702 END IF;
1703
1704 IF lv_process_status <> jai_constants.successful THEN
1705 p_process_status := lv_process_status;
1706 p_process_message := lv_process_message;
1707 goto EXIT_POINT;
1708 END IF;
1709
1710 IF p_debug = 'Y' THEN
1711 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - before insert_trx_line_gl_dist');
1712 END IF;
1713
1714
1715 --This procedure inserts the data into ra_cust_trx_line_gl_dist_all
1716 insert_trx_line_gl_dist(p_account_class => rec_temp.line_type,
1717 p_account_set_flag => lv_account_set_flag,
1718 p_acctd_amount => ROUND(rec_temp.acctd_amount, ln_precision),
1719 p_amount => rec_temp.amount,
1720 p_code_combination_id => rec_temp.code_combination_id,
1721 p_cust_trx_line_gl_dist_id => NULL,
1722 p_cust_trx_line_salesrep_id => rec_temp.cust_trx_line_sales_rep_id,
1723 p_customer_trx_id => rec_temp.customer_trx_id,
1724 p_customer_trx_line_id => rec_temp.customer_trx_line_id,
1725 p_gl_date => ld_gl_date,
1726 p_last_update_date => rec_temp.last_update_date,
1727 p_last_updated_by => rec_temp.last_updated_by,
1728 p_creation_date => rec_temp.creation_date,
1729 p_created_by => rec_temp.created_by,
1730 p_last_update_login => rec_temp.last_update_login,
1731 p_org_id => rec_customer_trx.org_id,
1732 p_percent => 100,
1733 p_posting_control_id => -3,
1734 p_set_of_books_id => rec_temp.set_of_books_id,
1735 p_process_status => lv_process_status,
1736 p_process_message => lv_process_message ,
1737 p_seq_id => ln_gl_seq,
1738 p_event_id => ln_event_id); --added for bug#7645588
1739
1740 IF p_debug = 'Y' THEN
1741 fnd_file.put_line(FND_FILE.LOG, 'In loop cur_temp_lines_insert - after insert_trx_line_gl_dist');
1742 END IF;
1743
1744 --added the following IF condition for bug#7645588
1745 IF ln_accounting_rule_id is not null and ln_previous_customer_trx_id IS NOT NULL then
1746 NULL;
1747 ELSE
1748 -- ###### IMPORTANT FOR R12
1749 /* following added by Ramanand for Bug#4468353 as part of SLA uptake */
1750 l_xla_event.xla_req_id := NULL ;
1751 l_xla_event.xla_dist_id := ln_gl_seq ;
1752 l_xla_event.xla_doc_table := 'CT' ;
1753 l_xla_event.xla_doc_event := NULL ;
1754 l_xla_event.xla_mode := 'O' ;
1755 l_xla_event.xla_call := 'D' ;
1756 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event);
1757 -- ###### IMPORTANT FOR R12
1758 END IF;
1759
1760
1761
1762 IF lv_process_status <> jai_constants.successful THEN
1763 p_process_status := lv_process_status;
1764 p_process_message := lv_process_message;
1765 goto EXIT_POINT;
1766 END IF;
1767
1768
1769 --- #### IMPORTANT FOR R12
1770 ELSE /* ld_gl_posted_date will not be null when the execution comes here */
1771 raise Item_lines_already_accounted;
1772 END IF ; --ld_gl_posted_date is null
1773 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, end */
1774 END IF;
1775
1776 END LOOP; --End rec_temp
1777
1778 --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
1779 OPEN cur_total_amt_trx_lines( p_customer_trx_id,
1780 NULL,
1781 lv_line_type_line);
1782 FETCH cur_total_amt_trx_lines INTO ln_old_amount,
1783 ln_taxable_amount; --this variable is not being used
1784 CLOSE cur_total_amt_trx_lines;
1785
1786
1787 --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
1788 --and account_class in ('TAX','FREIGHT')
1789 OPEN cur_total_amt_gl_dist(p_customer_trx_id);
1790 FETCH cur_total_amt_gl_dist INTO ln_tax_amt,
1791 ln_tax_acctd_amount,
1792 ln_max_tax_acctd_amount;
1793 CLOSE cur_total_amt_gl_dist;
1794
1795 IF ln_previous_customer_trx_id IS NOT NULL THEN --CM
1796 ln_old_acctd_amount := ln_old_amount * ln_cm_curr_conv_rate;
1797 ln_adjusted_tax := ROUND(ln_tax_amt * ln_cm_curr_conv_rate, ln_precision);
1798 else
1799 ln_old_acctd_amount := ln_old_amount * ln_inv_curr_conv_rate;
1800 ln_adjusted_tax := ROUND(ln_tax_amt * ln_inv_curr_conv_rate, ln_precision);
1801 end if;
1802
1803 /* Modified for bug#5495711
1804 || acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount + ln_diff_tax_frt), ln_precision)
1805 */
1806 UPDATE ra_cust_trx_line_gl_dist_all
1807 SET amount = ln_old_amount + ln_tax_amt,
1808 acctd_amount = ROUND( (ln_old_acctd_amount + ln_tax_acctd_amount ), ln_precision)
1809 WHERE customer_trx_id = p_customer_trx_id
1810 AND account_class = lv_account_class_rec --'REC'
1811 AND latest_rec_flag = 'Y';
1812
1813 IF lv_process_status <> jai_constants.successful THEN
1814
1815 p_process_status := lv_process_status;
1816 p_process_message := lv_process_message;
1817 goto EXIT_POINT;
1818
1819 END IF;
1820 --Added by Ramananda for bug#5495711, Ends
1821
1822 --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
1823 OPEN cur_total_amt_trx_lines(p_customer_trx_id,
1824 NULL,
1825 lv_account_class_tax);
1826 FETCH cur_total_amt_trx_lines INTO ln_total_tax_amt_for_inv,
1827 ln_taxable_amount; --this variable is not being used
1828 CLOSE cur_total_amt_trx_lines;
1829
1830
1831 --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
1832 OPEN cur_total_amt_trx_lines(p_customer_trx_id,
1833 NULL,
1834 lv_account_class_freight);
1835 FETCH cur_total_amt_trx_lines INTO ln_total_frt_amt_for_inv,
1836 ln_taxable_amount; --this variable is not being used
1837 CLOSE cur_total_amt_trx_lines;
1838
1839 IF p_debug = 'Y' THEN
1840 fnd_file.put_line(FND_FILE.LOG, 'Before ln_previous_customer_trx_id is not null'||ln_previous_customer_trx_id);
1841 END IF;
1842
1843 IF ln_previous_customer_trx_id IS NOT NULL THEN --CM
1844
1845 ln_payment_audit_id := NULL;
1846 ln_rec_appl_audit_id := NULL;
1847
1848 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
1849 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
1850 p_payment_schedule_id => NULL,
1851 p_cm_customer_trx_id => p_customer_trx_id,
1852 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
1853 p_concurrent_req_num => NULL,
1854 p_request_id => NULL,
1855 p_operation_type => 'INSERT',
1856 p_payment_audit_id => ln_payment_audit_id,
1857 p_process_status => lv_process_status,
1858 p_process_message => lv_process_message);
1859
1860 IF lv_process_status <> jai_constants.successful THEN
1861 p_process_status := lv_process_status;
1862 p_process_message := lv_process_message;
1863 goto EXIT_POINT;
1864 END IF;
1865
1866 IF p_debug = 'Y' THEN
1867 fnd_file.put_line(FND_FILE.LOG, 'After INSERT call to maintain_schedules');
1868 END IF;
1869
1870 UPDATE ar_payment_schedules_all
1871 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),
1872 tax_original = ROUND(ln_total_tax_amt_for_inv, ln_precision),
1873 freight_original = ROUND(ln_total_frt_amt_for_inv, ln_precision),
1874 amount_applied = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
1875 last_update_date = SYSDATE
1876 WHERE customer_trx_id = p_customer_trx_id;
1877
1878 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
1879 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
1880 p_payment_schedule_id => NULL,
1881 p_cm_customer_trx_id => p_customer_trx_id,
1882 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
1883 p_concurrent_req_num => NULL,
1884 p_request_id => NULL,
1885 p_operation_type => 'UPDATE',
1886 p_payment_audit_id => ln_payment_audit_id,
1887 p_process_status => lv_process_status,
1888 p_process_message => lv_process_message);
1889
1890 IF lv_process_status <> jai_constants.successful THEN
1891 p_process_status := lv_process_status;
1892 p_process_message := lv_process_message;
1893 goto EXIT_POINT;
1894 END IF;
1895
1896 IF p_debug = 'Y' THEN
1897 fnd_file.put_line(FND_FILE.LOG, 'After UPDATE call to maintain_schedules');
1898 END IF;
1899
1900
1901 --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
1902 OPEN cur_min_payment_schedule(ln_previous_customer_trx_id);
1903 FETCH cur_min_payment_schedule INTO rec_inv_payment_schedule;
1904 CLOSE cur_min_payment_schedule;
1905
1906 --Get the first_installment_code and base_amount from ra_terms
1907 OPEN cur_term_details(rec_inv_payment_schedule.term_id);
1908 FETCH cur_term_details INTO rec_term_details;
1909 CLOSE cur_term_details;
1910
1911 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
1912 OPEN cur_tot_amt_for_cms(ln_previous_customer_trx_id,
1913 lv_account_class_tax);
1914 FETCH cur_tot_amt_for_cms INTO ln_tax_amt_cms;
1915 CLOSE cur_tot_amt_for_cms;
1916
1917 --Get the SUM(amount) from ra_cust_trx_line_gl_dist_all for the Credit Memo
1918 OPEN cur_tot_amt_for_cms(ln_previous_customer_trx_id,
1919 lv_account_class_freight);
1920 FETCH cur_tot_amt_for_cms INTO ln_frt_amt_cms;
1921 CLOSE cur_tot_amt_for_cms;
1922
1923 --Get the SUM of tax_applied and freight_applied from ar_receivable_applications_all for Cash receipts applied
1924 OPEN cur_tot_cash_rcpt(ln_previous_customer_trx_id);
1925 FETCH cur_tot_cash_rcpt INTO ln_tax_amt_cashrcpt,
1926 ln_frt_amt_cashrcpt;
1927 CLOSE cur_tot_cash_rcpt;
1928
1929 IF p_debug = 'Y' THEN
1930 fnd_file.put_line(FND_FILE.LOG, 'Before entering rec_payment LOOP');
1931 END IF;
1932
1933 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
1934 FOR rec_payment in cur_payment_schedule(ln_previous_customer_trx_id)
1935 LOOP
1936 IF p_debug = 'Y' THEN
1937 fnd_file.put_line(FND_FILE.LOG, 'In rec_payment LOOP');
1938 END IF;
1939
1940
1941 --Get the relative_amount from ra_terms_lines
1942 OPEN cur_term_lines(rec_payment.term_id,
1943 rec_payment.terms_sequence_number);
1944 FETCH cur_term_lines INTO ln_relative_amt;
1945 CLOSE cur_term_lines;
1946
1947 IF p_debug = 'Y' THEN
1948 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_term_lines');
1949 END IF;
1950
1951 IF rec_term_details.first_installment_code = 'ALLOCATE' THEN
1952 ln_apportion_factor := ln_relative_amt/rec_term_details.base_amount;
1953 ELSE
1954 ln_apportion_factor := 1;
1955 rec_payment.payment_schedule_id := rec_inv_payment_schedule.payment_schedule_id;
1956 END IF;
1957
1958 --Get the SUM of line_applied from ar_receivable_applications_all for CM
1959 OPEN cur_tot_recv_appl( ln_previous_customer_trx_id,
1960 rec_payment.payment_schedule_id);
1961 FETCH cur_tot_recv_appl INTO ln_line_applied;
1962 CLOSE cur_tot_recv_appl;
1963
1964 IF p_debug = 'Y' THEN
1965 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_tot_recv_appl');
1966 END IF;
1967
1968 ln_inst_tax_amt_for_inv := ln_tax_amt_cms * ln_apportion_factor;
1969 ln_inst_frt_amt_for_inv := ln_frt_amt_cms * ln_apportion_factor;
1970
1971
1972 --get the allow_overapplication_flag from ra_cust_trx_types_all for cust_trx_type_id
1973 OPEN cur_trx_types(rec_inv_customer_trx.cust_trx_type_id);
1974 FETCH cur_trx_types INTO lv_allow_overappln_flag ;
1975 CLOSE cur_trx_types ;
1976
1977 IF p_debug = 'Y' THEN
1978 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_trx_types');
1979 END IF;
1980
1981
1982 --Get the data from ar_payment_schedules_all for customer_trx_id
1983 OPEN cur_prev_payment_schedule( ln_previous_customer_trx_id,
1984 rec_payment.payment_schedule_id);
1985 FETCH cur_prev_payment_schedule INTO rec_prev_payment_schedule;
1986 CLOSE cur_prev_payment_schedule;
1987
1988 IF p_debug = 'Y' THEN
1989 fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_payment_schedule');
1990 END IF;
1991
1992 ln_amt_due_rem := NVL(rec_prev_payment_schedule.amount_line_items_remaining,0)
1993 + NVL(rec_prev_payment_schedule.tax_original,0)
1994 + NVL(rec_prev_payment_schedule.freight_original,0)
1995 + NVL(ln_inst_tax_amt_for_inv,0)
1996 + NVL(ln_inst_frt_amt_for_inv,0)
1997 - ln_tax_amt_cashrcpt
1998 - ln_frt_amt_cashrcpt;
1999
2000 IF p_debug = 'Y' THEN
2001 fnd_file.put_line(FND_FILE.LOG, 'ln_amt_due_rem '||ln_amt_due_rem);
2002 END IF;
2003
2004 IF ( NVL(lv_allow_overappln_flag,'N') = 'Y' ) OR
2005 ( NVL(lv_allow_overappln_flag,'N') = 'N' AND ln_amt_due_rem >= 0 )
2006 THEN
2007
2008 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2009 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2010 p_payment_schedule_id => rec_payment.payment_schedule_id,
2011 p_cm_customer_trx_id => p_customer_trx_id,
2012 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2013 p_concurrent_req_num => NULL,
2014 p_request_id => NULL,
2015 p_operation_type => 'INSERT',
2016 p_payment_audit_id => ln_payment_audit_id,
2017 p_process_status => lv_process_status,
2018 p_process_message => lv_process_message);
2019
2020 IF lv_process_status <> jai_constants.successful THEN
2021 p_process_status := lv_process_status;
2022 p_process_message := lv_process_message;
2023 goto EXIT_POINT;
2024 END IF;
2025
2026 IF p_debug = 'Y' THEN
2027 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_schedules');
2028 END IF;
2029
2030 UPDATE ar_payment_schedules_all
2031 SET amount_due_remaining = ROUND (ln_amt_due_rem ,ln_precision) ,
2032 tax_remaining = ROUND(tax_original - ln_tax_amt_cashrcpt + NVL(ln_inst_tax_amt_for_inv,0),ln_precision) ,
2033 freight_remaining = ROUND(freight_original - ln_frt_amt_cashrcpt + NVL(ln_inst_frt_amt_for_inv,0),ln_precision) ,
2034 acctd_amount_due_remaining = ROUND(ln_amt_due_rem * ln_inv_curr_conv_rate, ln_precision) ,
2035 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),
2036 last_update_date = SYSDATE
2037 WHERE customer_trx_id = ln_previous_customer_trx_id
2038 AND payment_schedule_id = rec_payment.payment_schedule_id;
2039
2040
2041 IF p_debug = 'Y' THEN
2042 fnd_file.put_line(FND_FILE.LOG, 'After first update fo ar_payment_schedules_all');
2043 END IF;
2044
2045 UPDATE ar_payment_schedules_all
2046 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2047 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')) ,
2048 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')),
2049 last_update_date = SYSDATE
2050 WHERE customer_trx_id = ln_previous_customer_trx_id
2051 AND payment_schedule_id = rec_payment.payment_schedule_id;
2052
2053 IF p_debug = 'Y' THEN
2054 fnd_file.put_line(FND_FILE.LOG, 'After second update fo ar_payment_schedules_all');
2055 END IF;
2056
2057 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2058 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2059 p_payment_schedule_id => rec_payment.payment_schedule_id,
2060 p_cm_customer_trx_id => p_customer_trx_id,
2061 p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2062 p_concurrent_req_num => NULL,
2063 p_request_id => NULL,
2064 p_operation_type => 'UPDATE',
2065 p_payment_audit_id => ln_payment_audit_id,
2066 p_process_status => lv_process_status,
2067 p_process_message => lv_process_message);
2068
2069 IF lv_process_status <> jai_constants.successful THEN
2070 p_process_status := lv_process_status;
2071 p_process_message := lv_process_message;
2072 goto EXIT_POINT;
2073 END IF;
2074
2075 IF p_debug = 'Y' THEN
2076 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_schedules');
2077 END IF;
2078
2079
2080 --Get the receivable_application_id from ar_receivable_applications_all for the Invoice's payment_schedule_id
2081 OPEN cur_recv_appl_id(ln_previous_customer_trx_id,
2082 p_customer_trx_id,
2083 rec_payment.payment_schedule_id);
2084 FETCH cur_recv_appl_id INTO ln_recv_appln_id;
2085 CLOSE cur_recv_appl_id;
2086
2087
2088 --This procedure maintains the history of ar_receivable_applications_all in jai_ar_rec_appl_audits
2089 maintain_applications(p_customer_trx_id => p_customer_trx_id,
2090 p_receivable_application_id => ln_recv_appln_id,
2091 p_concurrent_req_num => NULL,
2092 p_request_id => NULL,
2093 p_operation_type => 'INSERT',
2094 p_rec_appl_audit_id => ln_rec_appl_audit_id,
2095 p_process_status => lv_process_status,
2096 p_process_message => lv_process_message);
2097
2098 IF lv_process_status <> jai_constants.successful THEN
2099 p_process_status := lv_process_status;
2100 p_process_message := lv_process_message;
2101 goto EXIT_POINT;
2102 END IF;
2103
2104 IF p_debug = 'Y' THEN
2105 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_applications');
2106 END IF;
2107
2108
2109 UPDATE ar_receivable_applications_all
2110 SET amount_applied = ROUND( NVL(line_applied,0)
2111 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2112 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2113 ,ln_precision),
2114 acctd_amount_applied_from = ROUND( ( NVL(line_applied,0)
2115 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2116 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2117 ) * ln_cm_curr_conv_rate
2118 ,ln_precision),
2119 acctd_amount_applied_to = ROUND( ( NVL(line_applied,0)
2120 + ( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor)
2121 + ( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor)
2122 ) * ln_inv_curr_conv_rate
2123 ,ln_precision),
2124 tax_applied = (ROUND( (-1) * NVL(ln_total_tax_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
2125 freight_applied = (ROUND( (-1) * NVL(ln_total_frt_amt_for_inv,0) * ln_apportion_factor , ln_precision )),
2126 last_update_date = SYSDATE
2127 WHERE customer_trx_id = p_customer_trx_id
2128 AND receivable_application_id = ln_recv_appln_id;
2129 /*
2130 || Modified by Ramananda for bug#5495711, Ends
2131 */
2132
2133 IF p_debug = 'Y' THEN
2134 fnd_file.put_line(FND_FILE.LOG, 'after update of ar_receivable_applications_all');
2135 END IF;
2136
2137 maintain_applications(p_customer_trx_id => p_customer_trx_id,
2138 p_receivable_application_id => ln_recv_appln_id,
2139 p_concurrent_req_num => NULL,
2140 p_request_id => NULL,
2141 p_operation_type => 'UPDATE',
2142 p_rec_appl_audit_id => ln_rec_appl_audit_id,
2143 p_process_status => lv_process_status,
2144 p_process_message => lv_process_message);
2145
2146 IF lv_process_status <> jai_constants.successful THEN
2147 p_process_status := lv_process_status;
2148 p_process_message := lv_process_message;
2149 goto EXIT_POINT;
2150 END IF;
2151
2152 IF p_debug = 'Y' THEN
2153 fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_applications');
2154 END IF;
2155
2156 IF rec_term_details.first_installment_code <> 'ALLOCATE' THEN
2157 EXIT;
2158 END IF;
2159
2160 ELSE --over_application condition
2161 p_process_message := 'CM : Allow Over application on invoice is not allowed , hence not processing the taxes on the credit memo';
2162 p_process_status := jai_constants.expected_error;
2163
2164 goto EXIT_POINT;
2165
2166 END IF ;
2167
2168 END LOOP; --End rec_payment cursor
2169
2170 ELSE --In case of invoice
2171
2172 ln_payment_audit_id := NULL;
2173
2174 IF p_debug = 'Y' THEN
2175 fnd_file.put_line(FND_FILE.LOG, 'In else of previous_customer_trx_id');
2176 END IF;
2177
2178 --Get the first_installment_code and base_amount from ra_terms
2179 OPEN cur_term_details(rec_min_payment_schedule.term_id);
2180 FETCH cur_term_details INTO rec_term_details;
2181 CLOSE cur_term_details;
2182
2183 --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
2184 FOR rec_payment in cur_payment_schedule(p_customer_trx_id)
2185 LOOP
2186
2187 IF p_debug = 'Y' THEN
2188 fnd_file.put_line(FND_FILE.LOG, 'In LOOP cur_payment_schedule - rec_payment.payment_schedule_id'||rec_payment.payment_schedule_id);
2189 fnd_file.put_line(FND_FILE.LOG, 'In LOOP cur_payment_schedule - rec_term_details.first_installment_code'||rec_term_details.first_installment_code);
2190 END IF;
2191
2192 --Get the relative_amount from ra_terms_lines
2193 OPEN cur_term_lines(rec_payment.term_id,
2194 rec_payment.terms_sequence_number);
2195 FETCH cur_term_lines INTO ln_relative_amt;
2196 CLOSE cur_term_lines;
2197
2198 IF rec_term_details.first_installment_code = 'ALLOCATE' THEN
2199 ln_apportion_factor := ln_relative_amt/rec_term_details.base_amount;
2200 ELSE
2201 ln_apportion_factor := 1;
2202 rec_payment.payment_schedule_id := rec_min_payment_schedule.payment_schedule_id;
2203 END IF;
2204
2205 ln_inst_tax_amt_for_inv := ln_total_tax_amt_for_inv * ln_apportion_factor;
2206 ln_inst_frt_amt_for_inv := ln_total_frt_amt_for_inv * ln_apportion_factor;
2207
2208
2209 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2210 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2211 p_payment_schedule_id => rec_payment.payment_schedule_id,
2212 p_cm_customer_trx_id => NULL,
2213 p_invoice_customer_trx_id => p_customer_trx_id,
2214 p_concurrent_req_num => NULL,
2215 p_request_id => NULL,
2216 p_operation_type => 'INSERT',
2217 p_payment_audit_id => ln_payment_audit_id,
2218 p_process_status => lv_process_status,
2219 p_process_message => lv_process_message);
2220
2221 IF lv_process_status <> jai_constants.successful THEN
2222 p_process_status := lv_process_status;
2223 p_process_message := lv_process_message;
2224 goto EXIT_POINT;
2225 END IF;
2226
2227 IF p_debug = 'Y' THEN
2228 fnd_file.put_line(FND_FILE.LOG, 'After call to maintain_schedules');
2229 END IF;
2230
2231 UPDATE ar_payment_schedules_all
2232 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),
2233 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),
2234 tax_original = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
2235 tax_remaining = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
2236 freight_original = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
2237 freight_remaining = ROUND(NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
2238 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),
2239 last_update_date = SYSDATE
2240 WHERE customer_trx_id = p_customer_trx_id
2241 AND payment_schedule_id = rec_payment.payment_schedule_id;
2242
2243 IF p_debug = 'Y' THEN
2244 fnd_file.put_line(FND_FILE.LOG, 'After First update of ar_payment_schedules_all');
2245 END IF;
2246
2247 UPDATE ar_payment_schedules_all
2248 SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2249 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')) ,
2250 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')),
2251 last_update_date = SYSDATE
2252 WHERE customer_trx_id = p_customer_trx_id
2253 AND payment_schedule_id = rec_payment.payment_schedule_id;
2254
2255 IF p_debug = 'Y' THEN
2256 fnd_file.put_line(FND_FILE.LOG, 'After Second update of ar_payment_schedules_all '||SQL%ROWCOUNT);
2257 END IF;
2258
2259
2260 --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2261 maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2262 p_payment_schedule_id => rec_payment.payment_schedule_id,
2263 p_cm_customer_trx_id => NULL,
2264 p_invoice_customer_trx_id => p_customer_trx_id,
2265 p_concurrent_req_num => NULL,
2266 p_request_id => NULL,
2267 p_operation_type => 'UPDATE',
2268 p_payment_audit_id => ln_payment_audit_id,
2269 p_process_status => lv_process_status,
2270 p_process_message => lv_process_message);
2271
2272 fnd_file.put_line(FND_FILE.LOG, 'Out ln_payment_audit_id '||ln_payment_audit_id);
2273
2274 IF lv_process_status <> jai_constants.successful THEN
2275 p_process_status := lv_process_status;
2276 p_process_message := lv_process_message;
2277 goto EXIT_POINT;
2278 END IF;
2279
2280 IF p_debug = 'Y' THEN
2281 fnd_file.put_line(FND_FILE.LOG, 'After call to maintain_schedules');
2282 END IF;
2283
2284 IF rec_term_details.first_installment_code <> 'ALLOCATE' THEN
2285 EXIT;
2286 END IF;
2287
2288 END LOOP; --End cursor rec_payment
2289 END IF;
2290
2291
2292 --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all, ar_receivable_applications_all
2293 maintain_mrc( p_customer_trx_id => p_customer_trx_id,
2294 p_previous_cust_trx_id => ln_previous_customer_trx_id,
2295 p_called_from => 7/13/2007,
2296 p_process_status => lv_process_status,
2297 p_process_message => lv_process_message);
2298
2299 IF lv_process_status <> jai_constants.successful THEN
2300 p_process_status := lv_process_status;
2301 p_process_message := lv_process_message;
2302 goto EXIT_POINT;
2303 END IF;
2304
2305 <<EXIT_POINT>>
2306 NULL;
2307
2308 EXCEPTION
2309 WHEN resource_busy THEN
2310 fnd_file.put_line(FND_FILE.LOG,'Resource Busy,record '||p_customer_trx_id||' has been locked by another resource');
2311 p_process_message:= ' Resource Busy,record '||p_customer_trx_id||' has been locked by another resource ';
2312 p_process_status := jai_constants.unexpected_error;
2313
2314 WHEN LOCALIZATION_TAX_NOT_DEFINED THEN
2315 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 ');
2316 p_process_message:= ' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ';
2317 p_process_status := jai_constants.expected_error;
2318
2319 WHEN ROUNDING_ACCOUNT_NOT_DEFINED THEN
2320 fnd_file.put_line(FND_FILE.LOG, lv_process_message );
2321 p_process_message := lv_process_message;
2322 p_process_status := lv_process_status;
2323
2324 WHEN OTHERS THEN
2325 fnd_file.put_line(FND_FILE.LOG,sqlerrm);
2326 p_process_status := jai_constants.unexpected_error;
2327 p_process_message :=SUBSTR(SQLERRM,1,120);
2328
2329 UPDATE JAI_AR_TRX_INS_LINES_T
2330 SET error_flag = 'R',
2331 err_mesg = p_process_message
2332 WHERE customer_trx_id = p_customer_trx_id;
2333 END process_from_order_line;
2334
2335 ----------------------------------------------- --------------------
2336
2337
2338 PROCEDURE process_manual_invoice(ERRBUF OUT NOCOPY VARCHAR2,
2339 RETCODE OUT NOCOPY VARCHAR2,
2340 P_CUSTOMER_TRX_ID IN NUMBER,
2341 P_LINK_LINE_ID IN NUMBER)
2342 IS
2343 v_counter Number:= 0;
2344 v_gl_date Date;
2345 v_org_id Number;
2346 v_line_no Number := 0;
2347 v_receivable_amount Number := 0;
2348 v_receivable_acctd_amount Number := 0;
2349 v_old_amount Number := 0;
2350 v_old_acctd_amount Number := 0;
2351 v_vat_tax_id nUMBER(15);
2352 v_created_from Varchar2(40);
2353 v_tax_amount Number := 0;
2354 v_tax_amount1 Number := 0;
2355 v_freight_amount Number := 0;
2356 v_freight_amount1 Number := 0;
2357 v_payment_schedule_id Number ;
2358 lv_tax_const CONSTANT VARChar2(10) := 'TAX'; --rchandan for bug#4428980
2359 lv_freight_acc_class CONSTANT varchar2(10) := 'FREIGHT';--rchandan for bug#4428980
2360 lv_acc_class_rev CONSTANT varchar2(10) := 'REV';--rchandan for bug#4428980
2361 lv_acc_class_rec CONSTANT varchar2(10) := 'REC';--rchandan for bug#4428980
2362
2363 CURSOR count_cur IS
2364 SELECT count(customer_trx_line_id) FROM JAI_AR_TRX_INS_LINES_T
2365 WHERE customer_trx_id = P_CUSTOMER_TRX_ID;
2366
2367 CURSOR ORG_CUR IS
2368 SELECT ORG_ID, CREATED_FROM
2369 FROM RA_CUSTOMER_TRX_ALL
2370 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
2371
2372 CURSOR TEMP_CUR IS
2373 SELECT EXTENDED_AMOUNT,CUSTOMER_TRX_LINE_ID,CUSTOMER_TRX_ID ,SET_OF_BOOKS_ID,
2374 LINK_TO_CUST_TRX_LINE_ID,LINE_TYPE ,UOM_CODE,VAT_TAX_ID,ACCTD_AMOUNT,AMOUNT,
2375 CODE_COMBINATION_ID,CUST_TRX_LINE_SALES_REP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
2376 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,INSERT_UPDATE_FLAG
2377 FROM JAI_AR_TRX_INS_LINES_T
2378 WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
2379 link_to_cust_trx_line_id = p_link_line_id
2380 order by CUSTOMER_TRX_LINE_ID;
2381
2382
2383 /* Added by Ramananda for bug#4468353 due to SLA uptake by AR */
2384 CURSOR c_gl_posted_date_cur(p_customer_trx_line_id RA_CUST_TRX_LINE_GL_DIST_ALL.customer_trx_line_id%type) IS
2385 SELECT gl_posted_date
2386 from RA_CUST_TRX_LINE_GL_DIST_ALL
2387 where customer_trx_line_id = p_customer_trx_line_id
2388 and account_class = 'REC'
2389 and latest_rec_flag = 'Y';
2390
2391 ld_gl_posted_date RA_CUST_TRX_LINE_GL_DIST_ALL.gl_posted_date%type ;
2392
2393 CURSOR GL_DATE_CUR IS
2394 SELECT DISTINCT gl_date
2395 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
2396 WHERE CUSTOMER_TRX_LINE_ID IN (SELECT LINK_TO_CUST_TRX_LINE_ID FROM JAI_AR_TRX_INS_LINES_T
2397 WHERE customer_trx_id = P_CUSTOMER_TRX_ID);
2398
2399
2400 CURSOR MAX_LINE_CUR(p_cust_link_line_id IN NUMBER, p_line_type IN VARCHAR2) IS
2401 SELECT NVL(MAX(line_number),0)
2402 FROM RA_CUSTOMER_TRX_LINES_ALL
2403 WHERE link_to_cust_trx_line_id = p_cust_link_line_id
2404 and line_type = p_line_type;
2405
2406
2407 CURSOR LINK_LINE_CUR IS
2408 SELECT LINK_TO_CUST_TRX_LINE_ID,ERROR_FLAG --added the error_flag condition to process the records,which got stuck up
2409 FROM JAI_AR_TRX_INS_LINES_T
2410 WHERE customer_trx_id = P_CUSTOMER_TRX_ID AND LINK_TO_CUST_TRX_LINE_ID = p_link_line_id;
2411
2412 CURSOR PREVIOUS_AMOUNT_CUR IS
2413 SELECT A.AMOUNT , A.ACCTD_AMOUNT
2414 FROM RA_CUST_TRX_LINE_GL_DIST_ALL A, RA_CUSTOMER_TRX_LINES_ALL B, JAI_AR_TRX_INS_LINES_T C
2415 WHERE A.CUSTOMER_TRX_LINE_ID = B.CUSTOMER_TRX_LINE_ID
2416 AND B.LINK_TO_CUST_TRX_LINE_ID = C.LINK_TO_CUST_TRX_LINE_ID
2417 AND C.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2418 AND A.ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class)
2419 AND A.CUSTOMER_TRX_LINE_ID = C.CUSTOMER_TRX_LINE_ID;
2420
2421
2422 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
2423 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
2424 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
2425 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
2426 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
2427
2428 --2001/06/26 Anuradha Parthasarathy
2429 Cursor payment_schedule_cur IS
2430 Select min(payment_schedule_id)
2431 From Ar_Payment_Schedules_All
2432 Where Customer_trx_ID = p_customer_trx_id;
2433
2434 /* AR Transactions with Invoicing Acctg Rules not supported by Localization, Enhancement Done on 16th NOV */
2435 Cursor accounting_set_cur IS
2436 Select accounting_rule_id
2437 From Ra_Customer_Trx_Lines_All
2438 Where Customer_Trx_Line_Id = p_link_line_id;
2439 v_accounting_rule_id Number;
2440 v_account_set_flag Char(1);
2441
2442 Cursor prev_customer_trx_cur(p_line_type ra_customer_trx_lines_all.line_type%TYPE ) is
2443 Select previous_customer_trx_id
2444 from ra_customer_trx_lines_all
2445 where customer_trx_id = P_CUSTOMER_TRX_ID
2446 and line_type = p_line_type;
2447 --AND customer_trx_line_id = p_link_line_id; --Added this condition on 05-Apr-2002 as it should fetch only one value
2448 v_prev_customer_trx_id Number;
2449
2450 Cursor Inv_payment_schedule_cur(p_prev_customer_trx_id IN Number) is
2451 Select payment_schedule_id
2452 from ar_payment_schedules_all
2453 where customer_trx_id = p_prev_customer_trx_id;
2454
2455 --2001/07/04 Anuradha Parthasarathy
2456 v_interface_line_attribute6 Varchar2(30);
2457 v_return_reference_type_code Varchar2(30);
2458 v_credit_invoice_line_id Number;
2459
2460 Cursor line_id_cur(p_line_type ra_customer_trx_lines_all.line_type%TYPE ) is
2461 Select interface_line_attribute6
2462 From ra_customer_trx_lines_all
2463 Where customer_trx_id = p_customer_trx_id
2464 and line_type = p_line_type;
2465
2466 Cursor Ref_type_cur(p_line_id IN Number) is
2467 Select context,reference_line_id
2468 From oe_order_lines_all
2469 Where line_id = p_line_id;
2470
2471 v_upd_created_from varchar2(15);
2472 v_rma_check Number;
2473 v_temp_cust_trx_id Number;
2474
2475 CURSOR ORG_CUR_UPD IS
2476 SELECT CREATED_FROM
2477 FROM RA_CUSTOMER_TRX_ALL
2478 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
2479
2480 CURSOR check_rma_ref IS
2481 SELECT 1 from JAI_OM_OE_RMA_LINES
2482 WHERE TO_CHAR(RMA_NUMBER) IN (SELECT INTERFACE_HEADER_ATTRIBUTE1 FROM RA_CUSTOMER_TRX_ALL
2483 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID)
2484 AND Rma_line_id in ( Select RMA_LINE_ID from JAI_OM_OE_RMA_TAXES a,JAI_CMN_TAXES_ALL b
2485 Where a.tax_id = b.tax_id
2486 AND b.tax_type = jai_constants.tax_type_freight );
2487
2488 --added 12-Mar-2002
2489 CURSOR tax_type IS SELECT b.tax_type t_type,a.customer_trx_line_id line_id
2490 FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
2491 WHERE link_to_cust_trx_line_id = p_link_line_id
2492 and A.tax_id = B.tax_id;
2493
2494 CURSOR get_reason IS
2495 SELECT reason_code FROM
2496 RA_CUSTOMER_TRX_ALL WHERE
2497 CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
2498 v_reason_code ra_customer_trx_all.reason_code%TYPE;
2499 --end 12-Mar-2002
2500 v_tax_amt Number;
2501 v_err_mesg VARCHAR2(250);
2502 l_retcode NUMBER(1);
2503 l_errbuf VARCHAR2(1996);
2504
2505 CURSOR get_trx_num IS SELECT --21-Mar-2002 for ar tax and freight
2506 trx_number FROM
2507 ra_customer_trx_all WHERE
2508 customer_trx_id = p_customer_trx_id;
2509 v_trx_num ra_customer_trx_all.trx_number%TYPE;
2510 v_count_trx NUMBER;
2511 V_sum_amt NUMBER;
2512
2513 ------------------------------------------------------------------------------------------------
2514 -- start of modification added by subbu and Jagdish on 10-jun-01 for discount issue.
2515 v_extended_amount_line number;
2516 v_taxable_amt number := 0;
2517
2518 Cursor get_ext_amt_ln( p_line_type ra_customer_trx_lines.line_type%TYPE )
2519 is Select extended_amount
2520 from Ra_customer_trx_lines_all
2521 where customer_trx_id = P_CUSTOMER_TRX_ID
2522 and customer_trx_line_id = P_LINK_LINE_ID
2523 and line_type = p_line_type;--rchandan for bug#4428980
2524
2525 Cursor get_ext_amt_tax is Select extended_amount,customer_trx_line_id
2526 from Ra_customer_trx_lines_all
2527 where customer_trx_id = P_CUSTOMER_TRX_ID
2528 and Link_to_cust_trx_line_id = P_LINK_LINE_ID
2529 and line_type = lv_tax_const;--rchandan for bug#4428980
2530
2531 get_ext_amt_tax_rec get_ext_amt_tax%rowtype;
2532
2533 Cursor get_taxable_amt(cust_trx_ln_id number) Is Select nvl(taxable_amount,0)
2534 from ra_customer_trx_lines_all
2535 where customer_trx_line_id = cust_trx_ln_id
2536 and customer_trx_id = P_CUSTOMER_TRX_ID
2537 and line_type = lv_tax_const;--rchandan for bug#4428980
2538
2539 -- end of modification by subbu and Jagdish on 10-jun-01 for discount issue.
2540 ------------------------------------------------------------------------------------------------
2541 --05-Apr-2002
2542
2543 v_rec_ctr Number ;
2544 v_PAYMENT_amt Number :=0;
2545 v_FREIGHT_amt Number :=0;
2546 V_TEMP Number ;
2547 v_sql_num Number ;
2548 v_amt_a NUMBER;
2549 v_tot_amount NUMBER;
2550 v_sql_count NUMBER;
2551 v1_sql_count NUMBER;
2552
2553 Localization_tax_not_defined EXCEPTION; -- added by sriram - 3340594
2554 Item_lines_already_accounted EXCEPTION;
2555 -- declaration for mrc starts here bug # 3326394
2556
2557 cursor c_gl_dist_cur is
2558 select cust_trx_line_gl_dist_id
2559 from ra_cust_trx_line_gl_dist_all
2560 where customer_trx_id = p_customer_trx_id
2561 and account_class = lv_acc_class_rec --rchandan for bug#4428980
2562 and latest_rec_flag = 'Y';
2563
2564 v_gl_dist_id number;
2565
2566
2567 /* Added by Ramananda for bug#4468353 due to sla uptake by AR, end */
2568 l_xla_event arp_xla_events.xla_events_type;
2569
2570 BEGIN --MAIN BLOCK BEGIN
2571
2572
2573 /*------------------------------------------------------------------------------------------
2574 FILENAME: jai_ar_match_tax_pkg.process_manual_invoice.sql
2575
2576 CHANGE HISTORY:
2577
2578 S.No Date Author and Details
2579 1. 2001/06/26 Anuradha Parthasarathy
2580 Cursor defn changed for proper defaultation of Tax Code.
2581 2. 2001/07/04 Anuradha Parthasarathy
2582 Code added to credit tax amounts when rma references
2583 a Sales Order or an Invoice.
2584 3 2002/03/22 RPK : for BUG#2285636
2585
2586 Code modified to rollback the entire transactions if a transaction
2587 is failed.That is, the tax lines will be inserted all or none in the
2588 tables ra_customer_trx_lines_all ,ra_cust_trx_line_gl_dist_all.
2589 Also,whenever any record got errored out,then the corresponding invoice
2590 taxes will not be processed to the base table itself and that record will
2591 be updated to 'R'(column error_flag).
2592
2593 Code modified to get the freight lines for the RMA Transactions.
2594
2595 4 2002/04/04 Code merged for the issue of the receipt not getting saved when applied to an
2596 invoice having the discounts attached.
2597
2598 5 2002/04/09 For the BUG:2303830
2599 Added the condition IF v_rec_ctr > 0 to update only if the record is
2600 found in gl_dist table.
2601
2602 6 2002/04/22 RPK
2603 BUG#2247013
2604 Code modified to populate the freight lines for the Credit memo generated
2605 against a RMA transaction and the update the customer balances for the
2606 original invoice against which,this credit memo is generated.
2607
2608 7 2002/04/26 Sriram
2609 For Bug #2316589 for handling duplicate customer trx ids that are
2610 processed from manual invoice that might be stuck in the temp_lines
2611 insert table
2612
2613 8 2002/05/30 RPK
2614 BUG#2247013
2615 Bug re-opened to prevent the duplication of the taxes in the credit memo
2616 for the RMA transaction and also corresponding updation of the balances
2617 of the original invoice.
2618 10. 2003/02/17 Sriram - Bug # 2784431 - The select statement that identifies whether
2619 tax records already exist is not written correctly , it has been
2620 corrected.
2621
2622 11. 2003/12/26 Sriram - bug# 3340594 File Version 618.1
2623
2624 'Localization' tax if is end dates or is not present , it should show a meaningful
2625 error message instead of a cannot insert null into type of error.This has been
2626 acheived by adding an exception 'Localization_tax_not_defined' , raising the exception
2627 and handling the exception with the appropriate error message.
2628
2629 12. 2003/26/12 Sriram - bug# 3326394 File Version 618.2
2630
2631 incorporating code changes for multiple reporting currencies.
2632 api calls to ar_mrc_maintain procedure have been made at appropriate places to
2633 delete data from ra_mc_trx_line_gl_dist table in case of re-processing records.
2634
2635 api calls to ar_mrc_maintain package insert records in the RA_MC_TRX_LINE_GL_DIST table
2636 have been added to insert tax and freight records
2637
2638 api call to update the REC row for the gl dist also has been written .
2639
2640 no calls made for ar_payment_schedules and ar_receivable_applications because
2641 it is taken care when invoice is completed by base apps itself.
2642
2643 --------------------------------------------------------------------------------------------*/
2644
2645 v_sql_num := 0;
2646
2647 OPEN get_trx_num;
2648 FETCH get_trx_num INTO v_trx_num;
2649 CLOSE get_trx_num;
2650
2651 l_retcode := 0;
2652 v_sql_num :=1;
2653
2654 open prev_customer_trx_cur('LINE');--rchandan for bug#4428980
2655 fetch prev_customer_trx_cur into v_temp_cust_trx_id;
2656 close prev_customer_trx_cur;
2657 v_sql_num :=2;
2658 OPEN ORG_CUR_UPD;
2659 FETCH ORG_CUR_UPD INTO v_upd_created_from;
2660 CLOSE ORG_CUR_UPD;
2661 v_sql_num :=3;
2662
2663 OPEN check_rma_ref;
2664 FETCH check_rma_ref INTO v_rma_check;
2665 CLOSE check_rma_ref;
2666 v_sql_num :=4;
2667
2668 --12-MAR-2002
2669 OPEN get_reason;
2670 FETCH get_reason INTO v_reason_code;
2671 CLOSE get_reason;
2672 v_sql_num :=5;
2673
2674 OPEN payment_schedule_cur;
2675 FETCH payment_schedule_cur INTO v_payment_schedule_id;
2676 CLOSE payment_schedule_cur;
2677 v_sql_num :=6;
2678
2679 OPEN prev_customer_trx_cur('LINE');--rchandan for bug#4428980
2680 FETCH prev_customer_trx_cur INTO v_prev_customer_trx_id;
2681 CLOSE prev_customer_trx_cur;
2682 v_sql_num :=7;
2683
2684 BEGIN --RMA Block.This block is for processing localization taxes for the Credit memos of RMA
2685
2686 IF v_temp_cust_trx_id IS NOT NULL AND v_upd_created_from = 'RAXTRX' THEN
2687 -- AND v_reason_code = 'RETURN' THEN --commented for the BUG#2247013 as the reason_code can be anything.
2688 FOR tax_type_rec IN tax_type
2689 LOOP
2690 IF tax_type_rec.t_type <> 'Freight' THEN
2691 DELETE JAI_AR_TRX_INS_LINES_T
2692 WHERE customer_trx_id = P_CUSTOMER_TRX_ID
2693 and link_to_cust_trx_line_id = P_LINK_LINE_ID
2694 and customer_trx_line_id = tax_type_rec.line_id
2695 and tax_type_rec.t_type <> jai_constants.tax_type_freight;
2696 v_sql_num :=8;
2697 v_sql_count := SQL%ROWCOUNT;
2698 END IF;
2699 END LOOP;
2700 END IF;
2701 fnd_file.put_line(FND_FILE.LOG, 'Deletion in the RMA Blk...No. of rows deleted '|| v_sql_count);
2702 fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the Invoice..RMA Blk.. '|| v_trx_num);
2703
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 --retcode :=3;
2707 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN FOR RMA. Retcode = '|| retcode);
2708 fnd_file.put_line(FND_FILE.LOG, 'ERROR IN PROCESSING ..... ' || SQLERRM);
2709 END; --End block of RMA processing
2710 v_sql_num :=9;
2711
2712 SELECT NVL(SUM(AMOUNT),0) INTO V_sum_amt FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE
2713 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
2714 v_sql_num :=10;
2715
2716 OPEN COUNT_CUR;
2717 FETCH COUNT_CUR INTO v_counter;
2718 CLOSE COUNT_CUR;
2719 v_sql_num :=11;
2720
2721 IF NVL(v_counter,0) > 0 THEN --Main v_counter if
2722 FOR PREVIOUS_AMOUNT_REC IN PREVIOUS_AMOUNT_CUR
2723 LOOP
2724 v_old_amount := NVL(v_old_amount,0) + nvl(PREVIOUS_AMOUNT_REC.amount,0);
2725 v_old_acctd_amount := NVL(v_old_acctd_amount,0) + NVL(PREVIOUS_AMOUNT_REC.acctd_amount,0);
2726
2727 END LOOP;
2728 v_sql_num :=12;
2729
2730 FOR LINK_REC IN LINK_LINE_CUR
2731 LOOP
2732 v_sql_num :=13;
2733
2734
2735 -- the following select statement commented and using the next one instead
2736 -- because this is wrong.We need to compare the ra_customer_trx_lines_all table
2737 -- with the link_to_cust_trx_line_id column instead of based on the customer_trx_line_id
2738 -- column in the ra_cust_trx_line_gl_dist_all table. -- bug # 2784431
2739
2740 /*
2741 SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr FROM ra_cust_trx_line_gl_dist_all
2742 WHERE customer_trx_line_id = P_LINK_LINE_ID
2743 AND Account_class IN ('TAX','FREIGHT'); --Added on 09-Apr-2002 For the BUG#2303830
2744 */
2745
2746 SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr
2747 FROM ra_customer_trx_lines_all
2748 where link_to_cust_trx_line_id = p_link_line_id
2749 and line_type in (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
2750
2751
2752 IF v_rec_ctr > 0 THEN --Added on 09-Apr-2002 for the BUG#2303830
2753
2754 v_sql_num :=14;
2755
2756 DELETE RA_CUST_TRX_LINE_GL_DIST_ALL
2757 WHERE CUSTOMER_TRX_LINE_ID IN (SELECT CUSTOMER_TRX_LINE_ID
2758 FROM RA_CUSTOMER_TRX_LINES_ALL
2759 WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID)
2760 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
2761
2762 -- added for mrc -- sriram - 26/12 -- 3326394
2763
2764 for mrc_rec in
2765 (
2766 SELECT CUST_TRX_LINE_GL_DIST_ID
2767 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
2768 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2769 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
2770 AND CUSTOMER_TRX_LINE_ID
2771 IN
2772 (SELECT CUSTOMER_TRX_LINE_ID
2773 FROM RA_CUSTOMER_TRX_LINES_ALL
2774 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2775 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
2776 AND LINE_TYPE IN (lv_tax_const,lv_freight_acc_class) --rchandan for bug#4428980
2777 )
2778 )
2779 Loop
2780 ar_mrc_engine.maintain_mrc_data(
2781 p_event_mode => 'DELETE',
2782 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2783 p_mode => 'SINGLE',
2784 p_key_value => mrc_rec.CUST_TRX_LINE_GL_DIST_ID
2785 );
2786
2787 end loop;
2788
2789 /*
2790 DELETE RA_MC_TRX_LINE_GL_DIST
2791 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2792 AND CUST_TRX_LINE_GL_DIST_ID IN
2793 (
2794 SELECT CUST_TRX_LINE_GL_DIST_ID
2795 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
2796 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2797 AND ACCOUNT_CLASS IN ('TAX','FREIGHT')
2798 AND CUSTOMER_TRX_LINE_ID
2799 IN
2800 (SELECT CUSTOMER_TRX_LINE_ID
2801 FROM RA_CUSTOMER_TRX_LINES_ALL
2802 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
2803 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID
2804 AND LINE_TYPE IN ('TAX','FREIGHT')
2805 )
2806 )
2807 AND ACCOUNT_CLASS IN ('TAX','FREIGHT');
2808
2809 */
2810
2811 v_sql_num :=15;
2812
2813 DELETE RA_CUSTOMER_TRX_LINES_ALL
2814 WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID;
2815 v_sql_num :=16;
2816
2817 Update Ar_Payment_Schedules_All
2818 Set Tax_Original = 0,
2819 Tax_Remaining = 0,
2820 Freight_Original = 0,
2821 Freight_Remaining = 0,
2822 Amount_Due_Original = v_sum_amt,
2823 Amount_Due_Remaining = v_sum_amt,
2824 Acctd_amount_due_remaining = v_sum_amt
2825 Where Customer_Trx_ID = p_customer_trx_id
2826 And Payment_Schedule_ID = v_payment_schedule_id;
2827
2828 END IF;
2829
2830 v_sql_num :=17;
2831
2832 END LOOP;
2833
2834
2835 OPEN ORG_CUR;
2836 FETCH ORG_CUR INTO V_ORG_ID, V_CREATED_FROM;
2837 CLOSE ORG_CUR;
2838 v_sql_num :=18;
2839
2840 OPEN GL_DATE_CUR;
2841 FETCH GL_DATE_CUR INTO v_gl_date;
2842 CLOSE GL_DATE_CUR;
2843 v_sql_num :=19;
2844
2845 /* Commented by Ramananda for bug#4468353 due to ebtax uptake by AR */
2846 --2001/06/26 Anuradha Parthasarathy
2847 /*
2848 OPEN VAT_TAX_ID_CUR(V_ORG_ID,'Localization');--rchandan for bug#4428980
2849 FETCH VAT_TAX_ID_CUR INTO v_vat_tax_id;
2850 CLOSE VAT_TAX_ID_CUR;
2851
2852 if v_vat_tax_id is null then
2853 raise Localization_tax_not_defined;
2854 end if;
2855 */
2856
2857 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start */
2858 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
2859 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
2860 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
2861
2862 OPEN jai_ar_trx_pkg.c_party_tax_profile_id_cur(V_ORG_ID);
2863 FETCH jai_ar_trx_pkg.c_party_tax_profile_id_cur INTO ln_party_tax_profile_id;
2864 CLOSE jai_ar_trx_pkg.c_party_tax_profile_id_cur ;
2865
2866 OPEN jai_ar_trx_pkg.c_tax_rate_id_cur(lv_tax_regime_code, ln_party_tax_profile_id);
2867 FETCH jai_ar_trx_pkg.c_tax_rate_id_cur INTO ln_tax_rate_id;
2868 CLOSE jai_ar_trx_pkg.c_tax_rate_id_cur ;
2869
2870 if ln_tax_rate_id is null then
2871 raise Localization_tax_not_defined;
2872 end if;
2873 /* Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end */
2874
2875 v_sql_num :=20;
2876
2877 OPEN ACCOUNTING_SET_CUR;
2878 FETCH ACCOUNTING_SET_CUR INTO v_accounting_rule_id;
2879 CLOSE ACCOUNTING_SET_CUR;
2880 v_sql_num :=21;
2881
2882 IF v_accounting_rule_id IS NOT NULL THEN
2883 v_account_Set_flag := 'Y';
2884 ELSE
2885 v_account_Set_flag := 'N';
2886 END IF;
2887
2888
2889 BEGIN --Begin Temp_Cur Block
2890
2891 SAVEPOINT TEMP_CUR_BLK_SVP;
2892
2893
2894 FOR TEMP_REC IN TEMP_CUR
2895 LOOP
2896 OPEN MAX_LINE_CUR(TEMP_REC.LINK_TO_CUST_TRX_LINE_ID, TEMP_REC.line_type);
2897 FETCH MAX_LINE_CUR INTO v_line_no;
2898 CLOSE MAX_LINE_CUR;
2899 v_sql_num :=22;
2900
2901 v_line_no := NVL(v_line_no,0) + 1;
2902
2903 IF TEMP_REC.INSERT_UPDATE_FLAG IN('U','X') THEN
2904 v_sql_num :=23;
2905
2906 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, start */
2907 OPEN c_gl_posted_date_cur( TEMP_REC.link_to_cust_trx_line_id ) ;
2908 FETCH c_gl_posted_date_cur INTO ld_gl_posted_date ;
2909 CLOSE c_gl_posted_date_cur ;
2910
2911 IF ld_gl_posted_date is NULL THEN
2912
2913 INSERT INTO RA_CUSTOMER_TRX_LINES_ALL ( extended_amount,
2914 customer_trx_line_id,
2915 last_update_date,
2916 last_updated_by,
2917 creation_date,
2918 created_by,
2919 last_update_login,
2920 customer_trx_id,
2921 line_number,
2922 set_of_books_id,
2923 link_to_cust_trx_line_id,
2924 line_type,
2925 org_id,
2926 uom_code,
2927 autotax,
2928 vat_tax_id)
2929 VALUES ( TEMP_REC.extended_amount,
2930 TEMP_REC.customer_trx_line_id,
2931 TEMP_REC.last_update_date,
2932 TEMP_REC.last_updated_by,
2933 TEMP_REC.creation_date,
2934 TEMP_REC.created_by,
2935 TEMP_REC.last_update_login,
2936 TEMP_REC.customer_trx_id,
2937 v_line_no,
2938 TEMP_REC.set_of_books_id,
2939 TEMP_REC.link_to_cust_trx_line_id,
2940 TEMP_REC.line_type,
2941 v_org_id,
2942 TEMP_REC.uom_code,
2943 'N',
2944 v_vat_tax_id);
2945 v_sql_num :=24;
2946
2947 INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL(account_class,
2948 account_set_flag,
2949 acctd_amount,
2950 amount,
2951 code_combination_id,
2952 cust_trx_line_gl_dist_id,
2953 cust_trx_line_salesrep_id,
2954 customer_trx_id,
2955 customer_trx_line_id,
2956 gl_date,
2957 last_update_date,
2958 last_updated_by,
2959 creation_date,
2960 created_by,
2961 last_update_login,
2962 org_id,
2963 percent,
2964 posting_control_id,
2965 set_of_books_id )
2966 VALUES( TEMP_REC.line_type,
2967 v_account_set_flag,
2968 TEMP_REC.acctd_amount,
2969 TEMP_REC.amount,
2970 TEMP_REC.CODE_COMBINATION_ID,
2971 RA_CUST_TRX_LINE_GL_DIST_S.nextval,
2972 TEMP_REC.cust_trx_line_sales_rep_id,
2973 TEMP_REC.customer_trx_id,
2974 TEMP_REC.customer_trx_line_id,
2975 v_gl_date,
2976 TEMP_REC.last_update_date,
2977 TEMP_REC.last_updated_by,
2978 TEMP_REC.creation_date,
2979 TEMP_REC.created_by,
2980 TEMP_REC.last_update_login,
2981 v_org_id,
2982 100,
2983 -3,
2984 TEMP_REC.set_of_books_id ) RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
2985
2986 /* SLA Impact uptake */
2987 --l_xla_event.xla_from_doc_id ;
2988 --l_xla_event.xla_to_doc_id ;
2989 l_xla_event.xla_req_id := NULL ;
2990 l_xla_event.xla_dist_id := v_gl_dist_id ;
2991 l_xla_event.xla_doc_table := 'CT' ;
2992 l_xla_event.xla_doc_event := NULL ;
2993 l_xla_event.xla_mode := 'O' ;
2994 l_xla_event.xla_call := 'D' ;
2995 --l_xla_event.xla_fetch_size
2996
2997 ARP_XLA_EVENTS.CREATE_EVENTS(p_xla_ev_rec => l_xla_event);
2998
2999
3000 -- code for mrc insert starts here -- bug # 3326394
3001 ar_mrc_engine.maintain_mrc_data(
3002 p_event_mode => 'INSERT',
3003 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3004 p_mode => 'SINGLE',
3005 p_key_value => v_gl_dist_id);
3006
3007 -- code for mrc ends here -- bug # 3326394
3008
3009 v_sql_num :=25;
3010
3011 ELSE /* v_gl_posted_date will not be null when the execution comes here */
3012 raise Item_lines_already_accounted;
3013 END IF ; --v_gl_posted_date is null
3014 /* Modified by Ramananda for bug#4468353 due to sla uptake by AR, end */
3015
3016 ELSE
3017 UPDATE RA_CUSTOMER_TRX_LINES_ALL
3018 SET EXTENDED_AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
3019 LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
3020 LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
3021 CREATION_DATE = TEMP_REC.CREATION_DATE,
3022 CREATED_BY = TEMP_REC.CREATED_BY,
3023 LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
3024 WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
3025 v_sql_num :=26;
3026
3027 UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
3028 SET ACCTD_AMOUNT = TEMP_REC.ACCTD_AMOUNT,
3029 AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
3030 LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
3031 LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
3032 CREATION_DATE = TEMP_REC.CREATION_DATE,
3033 CREATED_BY = TEMP_REC.CREATED_BY,
3034 LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
3035 WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
3036 v_sql_num :=27;
3037
3038 END IF;
3039
3040 IF TEMP_REC.LINE_TYPE = 'TAX' THEN
3041 v_tax_amount := nvl(v_tax_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
3042
3043 ELSIF TEMP_REC.LINE_TYPE = 'FREIGHT' THEN
3044 v_freight_amount := nvl(v_freight_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
3045
3046 END IF;
3047
3048 v_receivable_amount := nvl(v_receivable_amount,0) + nvl(TEMP_REC.EXTENDED_AMOUNT,0);
3049 v_receivable_acctd_amount := nvl(v_receivable_acctd_amount,0) + nvl(TEMP_REC.ACCTD_AMOUNT,0);
3050
3051 END LOOP;
3052
3053 v_sql_num :=28;
3054
3055 Select SUM(amount),SUM(acctd_amount) into v_old_amount,v_old_acctd_amount --Added this stmt for the above stmt
3056 From RA_CUST_TRX_LINE_GL_DIST_ALL
3057 Where customer_trx_id = P_CUSTOMER_TRX_ID
3058 AND ACCOUNT_CLASS = lv_acc_class_rev; --rchandan for bug#4428980
3059 v_sql_num :=29;
3060
3061 Select SUM(amount) INTO v_tax_amt
3062 From RA_CUST_TRX_LINE_GL_DIST_ALL
3063 Where customer_trx_id = P_CUSTOMER_TRX_ID
3064 AND ACCOUNT_CLASS IN (lv_tax_const,lv_freight_acc_class); --rchandan for bug#4428980
3065 v_sql_num :=30;
3066
3067 UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
3068 SET AMOUNT = NVL(v_old_amount,0) + NVL(v_tax_amt,0),
3069 ACCTD_AMOUNT = NVL(v_old_acctd_amount,0) + NVL(v_tax_amt,0)
3070 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID AND
3071 ACCOUNT_CLASS = lv_acc_class_rec; --rchandan for bug#4428980
3072
3073 -- mrc update for gl dist - bug # 3326394
3074 open c_gl_dist_cur;
3075 fetch c_gl_dist_cur into v_gl_dist_id;
3076 close c_gl_dist_cur;
3077
3078
3079 ar_mrc_engine.maintain_mrc_data(
3080 p_event_mode => 'UPDATE',
3081 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3082 p_mode => 'SINGLE',
3083 p_key_value => v_gl_dist_id);
3084
3085
3086
3087 v_amt_a := NVL(v_old_amount,0) + NVL(v_tax_amt,0);
3088
3089 v_sql_num :=31;
3090
3091 --22-MAR-2002 FOR PROPER UPDATION
3092
3093 If v_prev_customer_trx_id is null then
3094
3095 v_sql_num :=32;
3096
3097 Update Ar_Payment_Schedules_All
3098 Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
3099 Tax_Remaining = NVL(Tax_Remaining,0) + NVL(v_tax_amount,0),
3100 Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
3101 Freight_Remaining = NVL(Freight_Remaining,0) + NVL(v_freight_amount,0),
3102 Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0),
3103 Amount_Due_Remaining = NVL(Amount_Due_Remaining,0) + NVL(v_receivable_amount,0),
3104 Acctd_amount_due_remaining = NVL(Acctd_amount_due_remaining,0) + NVL(v_receivable_acctd_amount,0)
3105 Where Customer_Trx_ID = p_customer_trx_id
3106 And Payment_Schedule_ID = v_payment_schedule_id;
3107
3108 fnd_file.put_line(FND_FILE.LOG, 'TAX ORIGINAL 1.......' || v_trx_num || 'is ' || NVL(v_tax_amount,-111));
3109 fnd_file.put_line(FND_FILE.LOG, 'TAX REMAINING 1......' || v_trx_num || 'is ' || NVL(v_tax_amount,-111));
3110 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT ORIGINAL 1...' || v_trx_num || 'is ' || NVL(v_freight_amount,-77));
3111 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT REMAINING 1..' || v_trx_num || 'is ' || NVL(v_freight_amount,-66));
3112 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE REMAINING 1...' || v_trx_num || 'is ' || NVL(v_receivable_amount,-222));
3113 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE ORIGINAL 1...' || v_trx_num || 'is ' || NVL(v_receivable_amount,333));
3114 fnd_file.put_line(FND_FILE.LOG, 'ACCTD AMOUNT DUE 1...' || v_trx_num || 'is ' || NVL(v_receivable_acctd_amount,444));
3115
3116 V_TEMP := NVL(v_old_amount,0) + NVL(v_tax_amt,0);
3117
3118 fnd_file.put_line(FND_FILE.LOG, 'TAX ORIGINAL 2.......' || v_trx_num || 'is ' || NVL(v_PAYMENT_amt,-111));
3119 fnd_file.put_line(FND_FILE.LOG, 'TAX REMAINING 2......' || v_trx_num || 'is ' || NVL(v_PAYMENT_amt,-111));
3120 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT ORIGINAL 2...' || v_trx_num || 'is ' || NVL(v_FREIGHT_amt,-77));
3121 fnd_file.put_line(FND_FILE.LOG, 'FREIGHT REMAINING 2...' || v_trx_num || 'is ' || NVL(v_FREIGHT_amt,-66));
3122 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE REMAINING 2...' || v_trx_num || 'is ' || V_TEMP);
3123 fnd_file.put_line(FND_FILE.LOG, 'AMOUNT DUE ORIGINAL 2...' || v_trx_num || 'is ' || V_TEMP);
3124 fnd_file.put_line(FND_FILE.LOG, 'ACCTD AMOUNT DUE 2...' || v_trx_num || 'is ' || NVL(v_receivable_acctd_amount,444));
3125
3126 end if; --END 22-MAR-02 FOR PROPER UPDATION
3127
3128 v_sql_num :=33;
3129
3130 DELETE JAI_AR_TRX_INS_LINES_T
3131 WHERE customer_trx_id = P_CUSTOMER_TRX_ID and
3132 link_to_cust_trx_line_id = P_LINK_LINE_ID;
3133 v_sql_num :=34;
3134
3135 ERRBUF :=SQLERRM;
3136 -- retcode := 0;
3137 fnd_file.put_line(FND_FILE.LOG, 'The total tax amount for the line is....' || v_tax_amt);
3138 fnd_file.put_line(FND_FILE.LOG, 'The receivable amount for the line is....' || v_receivable_amount);
3139 fnd_file.put_line(FND_FILE.LOG, 'Successfully Processed the Invoice... '|| v_trx_num);
3140 -- fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the Invoice Retcode = '|| retcode);
3141
3142 EXCEPTION
3143 -- retcode := 5;
3144 when others then
3145 ERRBUF :=SUBSTR(SQLERRM,1,230);
3146 ROLLBACK TO TEMP_CUR_BLK_SVP;
3147
3148 UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG = ERRBUF WHERE
3149 CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
3150 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
3151
3152 COMMIT;
3153
3154 -- fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
3155 fnd_file.put_line(FND_FILE.LOG, 'Updated the customer_trx_id error_flag to ...' || 'R');
3156 fnd_file.put_line(FND_FILE.LOG, 'Unable to Process the invoice... '|| v_trx_num);
3157 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM);
3158 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM || v_sql_num);
3159
3160
3161 END; --End Temp_Cur Block
3162
3163
3164 IF v_created_from = 'RAXTRX' THEN
3165
3166 If v_prev_customer_trx_id is NOT null THEN --added on 22-Mar-2002
3167
3168 Open line_id_cur('LINE');--rchandan for bug#4428980
3169 Fetch line_id_cur into v_interface_line_attribute6;
3170 Close line_id_cur;
3171
3172 Open Ref_type_cur(v_interface_line_attribute6);
3173 Fetch Ref_type_cur into v_return_reference_type_code,v_credit_invoice_line_id;
3174 Close Ref_type_cur;
3175
3176 --2001/07/04 Anuradha Parthasarathy
3177
3178 IF v_return_reference_type_code = 'Sales Order India' and v_credit_invoice_line_id IS NULL THEN
3179 Update Ar_Payment_Schedules_All
3180 Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
3181 Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
3182 Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0)
3183 Where Customer_Trx_ID = p_customer_trx_id
3184 And Payment_Schedule_ID = v_payment_schedule_id;
3185 fnd_file.put_line(FND_FILE.LOG, ' DEBUG:SPATIAL) checking Tax Details updating updating v_return_reference_type_code ' || v_return_reference_type_code );
3186 ELSIF v_return_reference_type_code in ('Invoice India','Sales Order India')
3187 and v_credit_invoice_line_id IS NOT NULL THEN
3188
3189 Select sum(amount) INTO v_tax_amount1
3190 FROM ra_cust_trx_line_gl_dist_all
3191 Where customer_trx_id = p_customer_trx_id
3192 And Account_class = lv_tax_const; --rchandan for bug#4428980
3193
3194 Select sum(amount) INTO v_freight_amount1
3195 FROM ra_cust_trx_line_gl_dist_all
3196 Where customer_trx_id = p_customer_trx_id
3197 And Account_class = lv_freight_acc_class; --rchandan for bug#4428980
3198
3199 SELECT SUM(AMOUNT) INTO v_tot_amount
3200 FROM ra_cust_trx_line_gl_dist_all
3201 WHERE customer_trx_id = p_customer_trx_id
3202 AND account_class = lv_acc_class_rec; --rchandan for bug#4428980
3203
3204
3205 Update Ar_Payment_Schedules_All
3206 Set Tax_Original = NVL(v_tax_amount1,0),
3207 Freight_Original = NVL(v_freight_amount1,0),
3208 Amount_Due_Original = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0),
3209 Amount_Applied = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0)
3210 Where Customer_Trx_ID = p_customer_trx_id
3211 And Payment_Schedule_ID = v_payment_schedule_id;
3212
3213
3214 --In the below statement only the freight amount is getting updated to all the columns,because the tax amount is
3215 --automatcally updated by the base apps product
3216
3217 Update Ar_Receivable_Applications_All
3218 Set Amount_Applied = NVL(Amount_Applied,0) - (NVL(v_freight_amount1,0)),
3219 --Tax_Applied = NVL(Tax_Applied,0) - NVL(v_tax_amount1,0),
3220 Freight_Applied = NVL(Freight_Applied,0) - NVL(v_freight_amount1,0),
3221 Acctd_Amount_Applied_From = NVL(Acctd_Amount_Applied_From,0) - ( NVL(v_freight_amount1,0) ),
3222 Acctd_Amount_Applied_To = NVL(Acctd_Amount_Applied_To,0) - ( NVL(v_freight_amount1,0) )
3223 Where Customer_Trx_ID = p_customer_trx_id
3224 And Payment_Schedule_ID = v_payment_schedule_id; --20-Apr-2002
3225
3226 /* Updating Ar_Payment_Schedules for the Invoice against which this credit memo is applied */
3227
3228 OPEN Inv_payment_schedule_cur(v_prev_customer_trx_id);
3229 FETCH Inv_payment_schedule_cur into v_payment_schedule_id;
3230 CLOSE Inv_payment_schedule_cur;
3231
3232 Update Ar_Payment_Schedules_All
3233 Set --Tax_Remaining = NVL(Tax_remaining,0) - NVL(v_tax_amount1,0),
3234 Freight_Remaining = NVL(Freight_Remaining,0) + NVL(v_freight_amount1,0),
3235 Amount_Due_Remaining = NVL(Amount_Due_Remaining,0) + NVL(v_freight_amount1,0),
3236 Amount_Credited = NVL(Amount_Credited,0) + NVL(v_freight_amount1,0),
3237 Acctd_Amount_Due_Remaining = NVL(Acctd_Amount_Due_Remaining,0) + NVL(v_freight_amount1,0)
3238 Where Customer_Trx_Id = v_prev_customer_trx_id
3239 And Payment_Schedule_Id = v_payment_Schedule_id; --18-apr-2002
3240
3241 fnd_file.put_line(FND_FILE.LOG, 'v_tot_amount = '|| v_tot_amount);
3242 fnd_file.put_line(FND_FILE.LOG, 'v_tax_amount1 = '|| v_tax_amount1);
3243 fnd_file.put_line(FND_FILE.LOG, 'v_freight_amount1 = '|| v_freight_amount1);
3244 fnd_file.put_line(FND_FILE.LOG, ' DEBUG:SPATIAL) checking Tax Details updating updating v_return_reference_type_code ' || v_return_reference_type_code );
3245 END IF;
3246
3247 END IF;
3248
3249 END IF;
3250
3251 END IF; --End Main v_counter if
3252
3253 ------------------------------------------------------------------------------------------------
3254 -- Start modifications by subbu and Jagdish on 10-jun-01 for receipt discount issue.
3255 OPEN get_ext_amt_ln('LINE');--rchandan for bug#4428980
3256 FETCH get_ext_amt_ln INTO v_extended_amount_line;
3257 CLOSE get_ext_amt_ln;
3258
3259 OPEN get_ext_amt_tax ;
3260 LOOP
3261 FETCH get_ext_amt_tax INTO get_ext_amt_tax_rec;
3262 EXIT WHEN get_ext_amt_tax%NOTFOUND;
3263 OPEN get_taxable_amt(get_ext_amt_tax_rec.customer_trx_line_id);
3264 FETCH get_taxable_amt INTO v_taxable_amt;
3265 IF v_taxable_amt = 0 THEN
3266 UPDATE ra_customer_trx_lines_all
3267 SET Taxable_amount = (v_extended_amount_line - get_ext_amt_tax_rec.extended_amount)
3268 WHERE Customer_trx_line_id = get_ext_amt_tax_rec.customer_trx_line_id
3269 and customer_trx_id = P_CUSTOMER_TRX_ID
3270 and link_to_cust_trx_line_id = P_LINK_LINE_ID
3271 and Line_type = lv_tax_const;
3272 END IF;
3273 CLOSE get_taxable_amt;
3274 END LOOP;
3275 CLOSE get_ext_amt_tax;
3276 -- end modifications by subbu and Jagdish on 10-jun-01 for receipt discount issue.
3277 ------------------------------------------------------------------------------------------------
3278
3279 ERRBUF := SQLERRM;
3280 -- retcode := 2;
3281 v_err_mesg := ERRBUF;
3282
3283 COMMIT;
3284
3285 -- retcode := 0;
3286 fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the invoice = '|| v_trx_num);
3287 -- fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN.Processed the customer_trx_id Retcode = '|| retcode);
3288
3289 EXCEPTION
3290
3291 when Localization_tax_not_defined then
3292 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 ');
3293 errbuf:= ' ''Localization'' Tax not defined or is end-dated. Please ensure that a valid ''Localization'' Tax exists and is not enddated ';
3294 retcode := 2;
3295
3296 WHEN OTHERS THEN
3297
3298 ERRBUF :=SUBSTR(SQLERRM,1,230);
3299 UPDATE JAI_AR_TRX_INS_LINES_T SET ERROR_FLAG = 'R',ERR_MESG=ERRBUF WHERE CUSTOMER_TRX_ID=P_CUSTOMER_TRX_ID
3300 AND LINK_TO_CUST_TRX_LINE_ID = P_LINK_LINE_ID;
3301 COMMIT;
3302 -- retcode := 7;
3303 -- fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
3304 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the invoice = '|| v_trx_num);
3305 fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN... the err = '|| SQLERRM);
3306 fnd_file.put_line(FND_FILE.LOG, 'Main Block.... the err = '|| SQLERRM);
3307 fnd_file.put_line(FND_FILE.LOG, 'Please Contact the System Administrator Or Oracle Software Support Services...');
3308
3309 END process_manual_invoice;
3310 -- Start commented by kunkumar for bug#6066813
3311 -- following function added for bug#6012570 (5876390) --> revoked the comments, 6012570
3312 function is_this_projects_context(pv_context in varchar2) return varchar2 is
3313 begin
3314 if jai_ar_rctla_trigger_pkg.is_this_projects_context(pv_context) then
3315 return jai_constants.yes;
3316 else
3317 return jai_constants.no;
3318 end if;
3319 end is_this_projects_context;
3320 -- End commented by kunkumar for bug#6066813*/, revoked the comments, 6012570
3321
3322
3323 -- Added by Jia Li on tax inclusive computation on 2007/11/30, Begin
3324 --==========================================================================
3325 -- FUNCTION NAME:
3326 --
3327 -- get_tax_account_id Private
3328 --
3329 -- DESCRIPTION:
3330 --
3331 -- This function is get tax account ccid
3332 --
3333 -- PARAMETERS:
3334 -- In: pn_tax_id
3335 -- pn_tax_type
3336 -- pn_org_id
3337 --
3338 -- DESIGN REFERENCES:
3339 -- Inclusive Tax Technical Design V1.4.doc
3340 --
3341 -- CHANGE HISTORY:
3342 --
3343 -- 20-DEC-2007 Jia Li created
3344 --==========================================================================
3345 FUNCTION get_tax_account_id
3346 ( pn_tax_id IN NUMBER
3347 , pv_tax_type IN VARCHAR2
3348 , pn_org_id IN NUMBER
3349 )
3350 RETURN NUMBER
3351 IS
3352 ln_tax_def_acc_id NUMBER;
3353 ln_tax_rgm_acc_id NUMBER;
3354 ln_tax_acc_id NUMBER;
3355 lv_procedure_name VARCHAR2(40):='get_tax_account_id';
3356 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3357 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
3358
3359 BEGIN
3360 --logging for debug
3361 IF (ln_proc_level >= ln_dbg_level)
3362 THEN
3363 FND_LOG.STRING( ln_proc_level
3364 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
3365 , 'Enter procedure'
3366 );
3367 FND_LOG.STRING( ln_proc_level
3368 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
3369 , 'Org_id = ' || pn_org_id
3370 );
3371 FND_LOG.STRING( ln_proc_level
3372 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
3373 , 'Tax_id = '|| pn_tax_id ||' Tax_type = ' || pv_tax_type
3374 );
3375 END IF; --ln_proc_level>=l_dbg_level
3376
3377 -- Get tax_account_id from tax defination
3378 BEGIN
3379 SELECT
3380 tax_account_id
3381 INTO
3382 ln_tax_def_acc_id
3383 FROM
3384 jai_cmn_taxes_all
3385 WHERE tax_id = pn_tax_id
3386 AND org_id = pn_org_id;
3387 EXCEPTION
3388 WHEN OTHERS THEN
3389 ln_tax_def_acc_id := -1;
3390 END;
3391
3392 -- Get tax_account_id from rgm setup for SERVICE and VAT tax.
3393 BEGIN
3394 SELECT
3395 TO_NUMBER(acc_rgm.attribute_value)
3396 INTO
3397 ln_tax_rgm_acc_id
3398 FROM
3399 jai_rgm_definitions rgm_def
3400 , jai_rgm_registrations tax_rgm
3401 , jai_rgm_registrations acc_rgm
3402 WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
3403 AND tax_rgm.regime_id = rgm_def.regime_id
3404 AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
3405 AND tax_rgm.attribute_code = pv_tax_type
3406 AND tax_rgm.regime_id = acc_rgm.regime_id
3407 AND acc_rgm.registration_type = jai_constants.regn_type_accounts
3408 AND acc_rgm.attribute_code = jai_constants.recovery_interim
3409 AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
3410
3411 ln_tax_acc_id := ln_tax_rgm_acc_id;
3412 EXCEPTION
3413 WHEN no_data_found THEN
3414 ln_tax_acc_id := ln_tax_def_acc_id;
3415 WHEN OTHERS THEN
3416 ln_tax_acc_id := -1;
3417 END;
3418
3419 IF (ln_proc_level >= ln_dbg_level)
3420 THEN
3421 FND_LOG.STRING( ln_proc_level
3422 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.result'
3423 , 'Tax Account ID = ' || ln_tax_acc_id
3424 );
3425 FND_LOG.STRING( ln_proc_level
3426 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
3427 , 'Enter procedure'
3428 );
3429 END IF; -- ln_proc_level >= ln_dbg_level
3430
3431 RETURN ln_tax_acc_id;
3432
3433 END get_tax_account_id;
3434
3435
3436 --==========================================================================
3437 -- PROCEDURE NAME:
3438 --
3439 -- acct_inclu_taxes Public
3440 --
3441 -- DESCRIPTION:
3442 --
3443 -- This procedure is written that whould pass GL entries for inclusive taxes in GL interface
3444 --
3445 -- PARAMETERS:
3446 -- In: pn_customer_trx_id Indicates the customer trx id
3447 -- pn_org_id Indicates the transaction org id
3448 -- pn_cust_trx_type_id Indicates the custormer trx tye id
3449 -- OUt: xv_process_flag Indicates the process flag, 'SS' for success
3450 -- 'EE' for expected error
3451 -- 'UE' for unexpected error
3452 -- xv_process_message Indicates the process message
3453 --
3454 --
3455 -- DESIGN REFERENCES:
3456 -- Inclusive Tax Technical Design V1.4.doc
3457 --
3458 -- CHANGE HISTORY:
3459 --
3460 -- 30-NOV-2007 Jia Li created
3461 --==========================================================================
3462 PROCEDURE acct_inclu_taxes
3463 ( pn_customer_trx_id IN NUMBER
3464 , pn_org_id IN NUMBER
3465 , pn_cust_trx_type_id IN NUMBER
3466 , xv_process_flag OUT NOCOPY VARCHAR2
3467 , xv_process_message OUT NOCOPY VARCHAR2
3468 )
3469 IS
3470 ln_org_id ra_customer_trx_all.org_id%TYPE;
3471 ln_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%TYPE;
3472 lv_inv_num ra_customer_trx_all.trx_number%TYPE;
3473 ld_cur_conversion_date jai_ar_trxs.exchange_date%TYPE;
3474 lv_cur_conversion_type jai_ar_trxs.exchange_rate_type%TYPE;
3475 ln_cur_conversion_rate jai_ar_trxs.exchange_rate%TYPE;
3476 lv_currency_code jai_ar_trxs.invoice_currency_code%TYPE;
3477 ln_inv_org_id jai_ar_trxs.organization_id%TYPE;
3478 lv_inv_org_code mtl_parameters.organization_code%TYPE;
3479 ln_rec_account_id ra_cust_trx_types_all.gl_id_rec%TYPE;
3480 ln_set_of_books_id ra_cust_trx_line_gl_dist_all.set_of_books_id%TYPE;
3481 ld_gl_date ra_cust_trx_line_gl_dist_all.gl_date%TYPE;
3482 ln_tax_account_id NUMBER;
3483 ln_total_inclu_tax_amt NUMBER;
3484 exception_error EXCEPTION;
3485
3486 CURSOR inclu_tax IS
3487 SELECT
3488 a.tax_id tax_id
3489 , b.tax_type tax_type
3490 , SUM(a.tax_amount) tax_amount
3491 FROM
3492 jai_cmn_taxes_all b
3493 , jai_ar_trx_tax_lines a
3494 WHERE a.tax_id = b.tax_id
3495 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
3496 AND a.link_to_cust_trx_line_id IN (SELECT
3497 customer_trx_line_id
3498 FROM
3499 jai_ar_trx_lines
3500 WHERE customer_trx_id = pn_customer_trx_id)
3501 GROUP BY
3502 a.tax_id
3503 , b.tax_type;
3504
3505 lv_procedure_name VARCHAR2(40):='acct_inclu_taxes';
3506 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3507 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
3508
3509 BEGIN
3510 --logging for debug
3511 IF (ln_proc_level >= ln_dbg_level)
3512 THEN
3513 FND_LOG.STRING( ln_proc_level
3514 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
3515 , 'Enter procedure'
3516 );
3517 END IF; --ln_proc_level>=l_dbg_level
3518
3519 ln_org_id := pn_org_id;
3520 ln_cust_trx_type_id := pn_cust_trx_type_id;
3521
3522 -- Get customer info
3523 BEGIN
3524 SELECT
3525 jat.trx_number
3526 , jat.exchange_date
3527 , jat.exchange_rate_type
3528 , jat.exchange_rate
3529 , jat.invoice_currency_code
3530 , jat.organization_id
3531 , mp.organization_code
3532 INTO
3533 lv_inv_num
3534 , ld_cur_conversion_date
3535 , lv_cur_conversion_type
3536 , ln_cur_conversion_rate
3537 , lv_currency_code
3538 , ln_inv_org_id
3539 , lv_inv_org_code
3540 FROM
3541 jai_ar_trxs jat
3542 , mtl_parameters mp
3543 WHERE jat.customer_trx_id = pn_customer_trx_id
3544 AND jat.organization_id = mp.organization_id;
3545
3546 EXCEPTION
3547 WHEN OTHERS THEN
3548 xv_process_message := Sqlerrm||'. Get customer info in acct_inclu_taxes procedure.';
3549 RAISE exception_error;
3550 END;
3551
3552 -- Get receivables dr accounting id
3553 BEGIN
3554 SELECT
3555 gl_id_rec
3556 INTO
3557 ln_rec_account_id
3558 FROM
3559 ra_cust_trx_types_all
3560 WHERE org_id = ln_org_id
3561 AND cust_trx_type_id = ln_cust_trx_type_id;
3562
3563 EXCEPTION
3564 WHEN OTHERS THEN
3565 xv_process_message := Sqlerrm||'. Get receivables dr accounting in acct_inclu_taxes procedure.';
3566 RAISE exception_error;
3567 END;
3568
3569
3570 BEGIN
3571 SELECT
3572 set_of_books_id
3573 , gl_date
3574 INTO
3575 ln_set_of_books_id
3576 , ld_gl_date
3577 FROM ra_cust_trx_line_gl_dist_all
3578 WHERE customer_trx_id = pn_customer_trx_id
3579 AND rownum = 1;
3580
3581 EXCEPTION
3582 WHEN OTHERS THEN
3583 xv_process_message := Sqlerrm||'. Get gl date in acct_inclu_taxes procedure.';
3584 RAISE exception_error;
3585 END;
3586
3587 -- Insert receivable inclusive taxes into GL Interface table
3588 ln_total_inclu_tax_amt := 0;
3589
3590 FOR inclu_tax_csr IN inclu_tax
3591 LOOP
3592 ln_tax_account_id := get_tax_account_id
3593 ( pn_tax_id => inclu_tax_csr.tax_id
3594 , pv_tax_type => inclu_tax_csr.tax_type
3595 , pn_org_id => ln_org_id
3596 );
3597 INSERT INTO gl_interface
3598 ( status
3599 , set_of_books_id
3600 , user_je_source_name
3601 , user_je_category_name
3602 , accounting_date
3603 , currency_code
3604 , date_created
3605 , created_by
3606 , actual_flag
3607 , entered_cr
3608 , entered_dr
3609 , transaction_date
3610 , code_combination_id
3611 , currency_conversion_date
3612 , user_currency_conversion_type
3613 , currency_conversion_rate
3614 , reference1
3615 , reference10
3616 , reference22
3617 , reference23
3618 , reference24
3619 , reference25
3620 , reference26
3621 , reference27
3622 )
3623 VALUES
3624 ( 'NEW'
3625 , ln_set_of_books_id -- the set of books id
3626 , 'Receivables India' -- je source name 'Receivables India'
3627 , 'Register India' -- je category name 'Register India'
3628 , ld_gl_date -- accounting date (GL date of the invoice)
3629 , lv_currency_code -- currency code
3630 , sysdate -- standard who column
3631 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
3632 , 'A' -- actual flag, hard coded value
3633 , inclu_tax_csr.tax_amount -- credit amt, inclusive tax amount
3634 , null -- debit amt
3635 , sysdate -- invoice date
3636 , ln_tax_account_id -- code combination
3637 , ld_cur_conversion_date
3638 , lv_cur_conversion_type
3639 , ln_cur_conversion_rate
3640 , lv_inv_org_code -- inventory organization code
3641 , 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
3642 , 'India Localization Entry' -- hard code string
3643 , 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
3644 , 'RA_CUSTOMER_TRX_ALL' -- hard code string
3645 , 'CUSTOMER_TRX_ID' -- hard code string
3646 , pn_customer_trx_id -- value of customer_trx_id
3647 , ln_inv_org_id -- organization id of the inventory organization id
3648 );
3649
3650 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + inclu_tax_csr.tax_amount;
3651 FND_FILE.PUT_LINE ( FND_FILE.LOG
3652 , 'Insert tax info: '
3653 || 'tax_account_id = ' || ln_tax_account_id
3654 || ' tax_amount = '|| inclu_tax_csr.tax_amount
3655 );
3656 END LOOP; -- inclu_tax cusor
3657
3658 -- Insert receivable amount into GL Interface table
3659 INSERT INTO gl_interface
3660 ( status
3661 , set_of_books_id
3662 , user_je_source_name
3663 , user_je_category_name
3664 , accounting_date
3665 , currency_code
3666 , date_created
3667 , created_by
3668 , actual_flag
3669 , entered_cr
3670 , entered_dr
3671 , transaction_date
3672 , code_combination_id
3673 , currency_conversion_date
3674 , user_currency_conversion_type
3675 , currency_conversion_rate
3676 , reference1
3677 , reference10
3678 , reference22
3679 , reference23
3680 , reference24
3681 , reference25
3682 , reference26
3683 , reference27
3684 )
3685 VALUES
3686 ( 'NEW'
3687 , ln_set_of_books_id -- the set of books id
3688 , 'Receivables India' -- je source name 'Receivables India'
3689 , 'Register India' -- je category name 'Register India'
3690 , ld_gl_date -- accounting date (GL date of the invoice)
3691 , lv_currency_code -- currency code
3692 , sysdate -- standard who column
3693 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
3694 , 'A' -- actual flag, hard coded value
3695 , null -- credit amt, inclusive tax amount
3696 , ln_total_inclu_tax_amt -- debit amt
3697 , sysdate -- invoice date
3698 , ln_rec_account_id -- code combination
3699 , ld_cur_conversion_date
3700 , lv_cur_conversion_type
3701 , ln_cur_conversion_rate
3702 , lv_inv_org_code -- inventory organization code
3703 , 'India Localization Entry for accounting inclusive taxes for invoice'||lv_inv_num
3704 , 'India Localization Entry' -- hard code string
3705 , 'acct_inclu_taxes' -- procedure name that makes the insert into gl_interface hard code string
3706 , 'RA_CUSTOMER_TRX_ALL' -- hard code string
3707 , 'CUSTOMER_TRX_ID' -- hard code string
3708 , pn_customer_trx_id -- value of customer_trx_id
3709 , ln_inv_org_id -- organization id of the inventory organization id
3710 );
3711
3712 FND_FILE.PUT_LINE ( FND_FILE.LOG
3713 , 'Insert debit info: '
3714 || 'account_id = ' || ln_rec_account_id
3715 || ' amount = '|| ln_total_inclu_tax_amt
3716 );
3717
3718 xv_process_flag := 'SS';
3719 xv_process_message := 'Inclusive taxes have successed into GL Interface';
3720
3721 --logging for debug
3722 IF (ln_proc_level >= ln_dbg_level)
3723 THEN
3724 FND_LOG.STRING( ln_proc_level
3725 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
3726 , 'Exit procedure'
3727 );
3728 END IF; -- (ln_proc_level>=ln_dbg_level)
3729
3730 EXCEPTION
3731 WHEN exception_error THEN
3732 xv_process_flag := 'EE';
3733 WHEN OTHERS THEN
3734 xv_process_flag := 'UE';
3735 xv_process_message := Sqlerrm||'. Exception error in acct_inclu_taxes procedure';
3736
3737 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3738 THEN
3739 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3740 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
3741 , Sqlcode||Sqlerrm);
3742 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3743
3744 END acct_inclu_taxes;
3745 -- Added by Jia Li on tax inclusive computation on 2007/11/30, End
3746
3747 END jai_ar_match_tax_pkg;
[Home] [Help]