[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_RGM_PROCESSING_PKG
Source
1 PACKAGE BODY jai_ar_rgm_processing_pkg
2 /* $Header: jai_ar_rgm_proc.plb 120.7.12010000.3 2008/11/25 04:24:50 mbremkum ship $ */
3
4 /******************************************************************************************************************************************************
5 Created By : aiyer
6 Created Date : 27-jan-2005
7 Enhancement Bug : 4146634
8 Purpose : Process the Service Tax AR records (Invoices,Credit memo's and Cash Receipts Applications) and populate
9 the jai_rgm_trx_refs and jai_rgms_trx_records appropriately.
10 Called From : jai_rgm_trx_processing.process_batch
11
12 Dependency Due To The Current Bug :
13 This object has been newly created with as a part of the service tax enhancement.
14 Needs to be always released along with the bug 4146708.
15
16 Change History: -
17 =================
18 1 20-Feb-2005 aiyer - Bug # 4193633 - File Version# 115.1
19 Issue
20 The tax earned and unearned discount are not getting apportioned properly for the service type of taxes and hence the India - Service Tax concurrent
21 ends up in a warning for records with these issues
22
23 Fix
24 The procedure get_ar_tax_disc_accnt has been modified for the fix of this bug.
25 Please refer the procedure change history for the details of this bug
26
27 Dependency Due To This Bug:
28 Dependency exists due to specification change of the current procedure.
29 Always sent the following packages together:-
30
31 1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
32 2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
33 3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
34 4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
35
36 2. 08-Jun-2005 Version 116.2 jai_ar_rgm_proc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
37 as required for CASE COMPLAINCE.
38
39 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.3
40 Modified the object to remove literals from DML statements and CURSORS.
41
42 4. 14-May-2005 CSahoo for bug#5879769. File Version 120.4
43 Forward porting of 11i BUG#5694855
44 SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION
45
46 5. 29-Nov-2006 Walton for inclusive tax computation
47
48 6. 26-June-2008 Changes by nprashar for bug 6997453
49 Issue : Invoice date column in 'India - Service tax pending liability' report
50 is showing creation date. (The reason for making changes in this file is that
51 the service tax processing program's behavior was also wrong w.r.to the date
52 paratmeters passed and the way AR invoices are picked up for populating
53 jai_rgm_trx_refs table
54 Cause : The query in the report is using jai_rgm_trx_refs.creation_date as
55 the invoice date. While checking how this field is populated, observed that
56 the records are populated while running the service tax processor.
57 In the procedure populate_inv_cm_references, the invoices are picked up
58 based on the creation_date. This is wrong behavior, and by common sense it
59 should be picked up on the basis of trx_date of the invoice.
60 Fix : Modified the cursor c_fetch_inv_cm_rec as described above.
61
62 7. 04/11/2008 Forward Port Bug 6474509
63 Issue: INDIA TAXES NOT REVERSING ON REVERSING A RECEIPT
64 Reason: Cursor c_get_refrec_for_upd doesn't fetch invoices to process the receipt reversals
65 only when there is no discounted amount during receipt application
66 Fix : Cursor c_get_refrec_for_upd (Added = condition) is modified to fetch the invoices
67 "nvl(recoverable_amount,0) - nvl(discounted_amount,0) >= nvl(recovered_amount,0)"
68
69 Future Dependencies For the release Of this Object:-
70 (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/
71 A datamodel change )
72 ----------------------------------------------------------------------------------------------------------------------------------------------------
73 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
74 Of File On Bug/Patchset
75 jai_rgm_process_ar_taxes_pkg_b.sql
76 ----------------------------------------------------------------------------------------------------------------------------------------------------
77 115.0 4146634 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug
78 4146708 for SERVICE/CESS enhancement release
79 115.1 4193633 jai_cmn_rgm_recording_pkg 115.1 Aiyer 23-Feb-2005 Functional dependency due to spec change.
80
81 ----------------------------------------------------------------------------------------------------------------------------------------------------
82
83
84 ********************************************************************************************************************************************************/
85 AS
86
87 /*csahoo for bug#5879769...start*/
88
89 lv_service_type_code JAI_PO_LINE_LOCATIONS.service_type_code%TYPE;
90 ln_organization_id NUMBER;
91 ln_location_id NUMBER;
92 lv_process_flag VARCHAR2(15);
93 lv_process_message VARCHAR2(4000);
94
95 /*csahoo for bug#5879769...end*/
96
97 procedure get_regime_info ( p_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ,
98 p_tax_type_code JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE ,
99 p_regime_id OUT NOCOPY JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
100 p_error_flag OUT NOCOPY VARCHAR2 ,
101 p_error_message OUT NOCOPY VARCHAR2
102 )
103 IS
104
105 -- Start of bug 4089440
106 /*
107 || Get the regime id based on regime code
108 */
109 CURSOR c_get_regime_id
110 IS
111 SELECT
112 regime_id
113 FROM
114 JAI_RGM_DEFINITIONS
115 WHERE
116 regime_code = p_regime_code;
117 /*
118 ||Get the meaning for a corresponding lookup_type and lookup_code
119 || TBD - Check effectivity for regime end date
120 */
121 CURSOR c_get_lookup_meaning ( cp_lookup_type FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE ,
122 cp_lookup_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE
123 )
124 IS
125 SELECT
126 meaning
127 FROM
128 fnd_lookup_values
129 WHERE
130 lookup_type = cp_lookup_type AND
131 lookup_code = cp_lookup_code;
132
133 /*
134 || Check whether a tax type exists in the regime registrations table for the
135 || tax types attached to a invoice tax line.
136 */
137 CURSOR c_chk_service_tax ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE ,
138 cp_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE
139 )
140 IS
141 SELECT
142 1
143 FROM
144 JAI_RGM_REGISTRATIONS
145 WHERE
146 regime_id = cp_regime_id AND
147 upper(registration_type) = upper(jai_constants.regn_type_tax_types) AND
148 upper(attribute_code) = upper(cp_tax_type);
149
150 lv_exists VARCHAR2(2) ;
151 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
152 lv_meaning FND_LOOKUP_VALUES.MEANING%TYPE ;
153 lv_meaning_rgm FND_LOOKUP_VALUES.MEANING%TYPE ;
154
155 BEGIN
156
157 /*################################################################################################################
158 || Initialize the variables
159 ################################################################################################################*/
160 p_error_flag := jai_constants.successful ;
161 p_error_message := NULL ;
162 p_regime_id := NULL ;
163
164 /*################################################################################################################
165 || Validate Regime Setup Information
166 ################################################################################################################*/
167 /*
168 ||Get the regime id info
169 */
170 OPEN c_get_regime_id ;
171 FETCH c_get_regime_id INTO ln_regime_id ;
172
173 IF c_get_regime_id%NOTFOUND THEN
174 /*
175 ||As regime has not been defined for this tax type hence error out
176 */
177 CLOSE c_get_regime_id;
178
179 OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_tax_type ,
180 cp_lookup_code => p_tax_type_code
181 );
182 FETCH c_get_lookup_meaning INTO lv_meaning;
183 CLOSE c_get_lookup_meaning;
184 p_error_flag := jai_constants.expected_error;
185 p_error_message := 'A regime has to be defined for taxes with tax type as '||lv_meaning;
186 return;
187 END IF;
188 CLOSE c_get_regime_id;
189
190
191
192 /*################################################################################################################
193 || Validate Regime Registration Setup Information
194 ################################################################################################################*/
195
196 /*
197 || Check whether a tax type exists in the regime registrations table for the
198 || tax types attached to a invoice tax line.
199 */
200 OPEN c_chk_service_tax ( cp_tax_type => p_tax_type_code ,
201 cp_regime_id => ln_regime_id
202 );
203 FETCH c_chk_service_tax INTO lv_exists;
204 IF c_chk_service_tax%NOTFOUND THEN
205 /*
206 ||As regime has not been defined for this tax type hence raise an error.
207 */
208 CLOSE c_chk_service_tax;
209 /*
210 || Get the meaning from lookup tables for lookup type TAX_TYPE and the lookup code as the current tax type
211 */
212 OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_tax_type ,
213 cp_lookup_code => p_tax_type_code
214 );
215 FETCH c_get_lookup_meaning INTO lv_meaning;
216 CLOSE c_get_lookup_meaning;
217 /*
218 || Get the meaning from lookup tables for lookup type 'JAI_INDIA_TAX_REGIMES' and the lookup code as 'SERVICE'
219 */
220 OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_ind_tax_rgms ,
221 cp_lookup_code => p_tax_type_code
222 );
223 FETCH c_get_lookup_meaning INTO lv_meaning_rgm;
224 CLOSE c_get_lookup_meaning;
225 p_error_flag := jai_constants.expected_error;
226 p_error_message := 'A tax type of '|| lv_meaning ||'should be defined for the regime '||lv_meaning_rgm;
227 return;
228 END IF;
229 CLOSE c_chk_service_tax;
230
231 p_regime_id := ln_regime_id ;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 p_error_flag := jai_constants.unexpected_error;
236 p_error_message := 'Unexpected Error Occured in procedure jai_ar_rgm_processing_pkg.get_regime_info - '||substr(sqlerrm,1,300);
237 END get_regime_info;
238
239 procedure get_ar_tax_disc_accnt ( p_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE ,
240 p_org_id AR_RECEIVABLES_TRX_ALL.ORG_ID%TYPE ,
241 p_total_disc_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ,/*Parameter added for the bug 4193633 */
242 p_tax_ediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_EDISCOUNTED%TYPE ,
243 p_earned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
244 p_tax_uediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_UEDISCOUNTED%TYPE ,
245 p_unearned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
246 p_process_flag OUT NOCOPY VARCHAR2 ,
247 p_process_message OUT NOCOPY VARCHAR2
248 )
249
250 /*****************************************************************************************************************************************************************
251 Created By : aiyer
252 Created Date : 27-jan-2005
253 Enhancement Bug : 4146634
254 Purpose : Gte the tax earned and Unearned discounts associated with the Receivable application
255 the jai_rgm_trx_refs and jai_rgms_trx_records appropriately.
256 Called From : jai_cmn_rgm_recording_pkg.insert_reference
257
258 Change History: -
259 =================
260 1 20-Feb-2005 aiyer - Bug # 4193633 - File Version# 115.1
261 Issue
262 The tax earned and unearned discount are not getting apportioned properly of service type of taxes and hence the India - Service Tax concurrent
263 ends up in a warning for records with these issues
264
265 Reason:-
266 In case of invoices having Service taxes and other type of taxes, the tax earned and unearned discounts should be approtioned across all the type of taxes
267 (Both Service and Non Service).
268 This apportionment logic was not present initially. This needs to be added
269
270 Fix: -
271 Modified the procedure. Did the following :-
272 1. Added a extra parameter p_total_disc_amount to the procedure.
273 2. used this parameter to apportion the tax earned discount amount and tax unearned discount amount
274
275 Dependency Due To This Bug:
276 Dependency exists due to specification change of the current procedure.
277 Always sent the following packages together:-
278
279 1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
280 2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
281 3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
282 4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
283
284
285 2. 17/04/2007 Bgowrava for forward porting bug#5989740, 11i BUG#5907436. File Version 120.2
286 ENH: Handling Secondary and Higher Education Cess
287 Added a input paramter cp_sh_service_edu_cess to the cursor c_fetch_inv_cm_rec.
288
289 3. 05/06/2007 sacsethi for bug 6109941
290 R12RUP03-ST1: CODE REVIEW COMMENTS FOR ENHANCEMENTS
291
292 Some code was found which missed during fp of bug 5879769
293
294 *****************************************************************************************************************************************************************/
295
296 IS
297 CURSOR cur_get_receivable_app
298 IS
299 SELECT
300 nvl(tax_ediscounted,0) tax_ediscounted ,
301 nvl(tax_uediscounted,0) tax_uediscounted
302 FROM
303 ar_receivable_applications_all
304 WHERE
305 receivable_application_id = p_receivable_application_id AND
306 org_id = p_org_id;
307
308 CURSOR cur_get_disc_ccid (cp_type AR_RECEIVABLES_TRX_ALL.TYPE%TYPE,p_lookup_type ar_lookups.lookup_type%type,p_status ar_receivables_trx_all.status%TYPE )--rchandan for bug#4428980
309 IS
310 SELECT
311 code_combination_id
312 FROM
313 ar_receivables_trx_all rtrx,
314 ar_lookups lkup
315 WHERE
316 rtrx.type = lkup.lookup_code AND
317 lkup.lookup_code = cp_type AND
318 lkup.lookup_type = p_lookup_type AND --rchandan for bug#4428980
319 org_id = p_org_id AND
320 status = p_status; --rchandan for bug#4428980
321
322 rec_cur_get_receivable_app CUR_GET_RECEIVABLE_APP%ROWTYPE ;
323 rec_cur_get_disc_ccid CUR_GET_DISC_CCID%ROWTYPE ;
324 ln_total_rec_disc_amt JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
325
326 BEGIN
327
328 fnd_file.put_line(fnd_file.LOG,'********************* 1 START OF PROCEDURE jai_ar_rgm_processing_pkg.GET_AR_TAX_DISC_ACCNT *********************');
329
330 /*
331 || Variable Intialization
332 */
333 p_process_flag := jai_constants.successful ;
334 p_process_message := null ;
335
336 fnd_file.put_line(fnd_file.LOG,' 2 Variables Initialised ');
337
338 IF p_total_disc_amount IS NULL THEN
339 p_process_flag := jai_constants.expected_error;
340 p_process_message := 'Service Tax Discounted cannot be NULL ';
341 fnd_file.put_line(fnd_file.LOG,' 3 EXPECTED ERROR - Service Tax Discounted amount cannot be NULL ');
342 return;
343 END IF;
344
345 /*
346 || Get the receivable_application Tax Earned and Tax Unearned Discount amounts
347 */
348 OPEN cur_get_receivable_app ;
349 FETCH cur_get_receivable_app into rec_cur_get_receivable_app ;
350 IF CUR_GET_RECEIVABLE_APP%NOTFOUND THEN
351 CLOSE cur_get_receivable_app;
352 p_process_flag := jai_constants.expected_error;
353 p_process_message := 'Receivable Application record with the receivable_application_id -> '||p_receivable_application_id ||' not found ';
354 fnd_file.put_line(fnd_file.LOG,' 4 EXPECTED ERROR -Receivable Application record with the receivable_application_id -> '||p_receivable_application_id ||' not found ');
355 return;
356 END IF;
357
358 /*
359 || Start of 4193633
360 || Apportion the discounted tax earned and discounted tax unearned based on the parameter p_total_disc_amount (total discount tax amount
361 || applicable all service taxes for a particular invoice
362 */
363 ln_total_rec_disc_amt := rec_cur_get_receivable_app.tax_ediscounted + rec_cur_get_receivable_app.tax_uediscounted ;
364 --Added by walton for inclusive tax
365 -------------------------------------------
366 IF nvl(ln_total_rec_disc_amt,0) =0
367 THEN
368 ln_total_rec_disc_amt:=1;
369 END IF;
370 ------------------------------------------
371 p_tax_ediscounted := ( rec_cur_get_receivable_app.tax_ediscounted / ln_total_rec_disc_amt ) * p_total_disc_amount ;
372 p_tax_uediscounted := ( rec_cur_get_receivable_app.tax_uediscounted / ln_total_rec_disc_amt ) * p_total_disc_amount ;
373
374 /*
375 || End of 4193633
376 */
377 fnd_file.put_line(fnd_file.LOG,' 5 value of p_tax_ediscounted -> '|| p_tax_ediscounted
378 ||', p_tax_uediscounted -> ' || p_tax_uediscounted
379 ||', ln_total_rec_disc_amt -> '|| ln_total_rec_disc_amt
380 ||', rec_cur_get_receivable_app.tax_ediscounted -> '||rec_cur_get_receivable_app.tax_ediscounted
381 ||', rec_cur_get_receivable_app.tax_uediscounted -> '||rec_cur_get_receivable_app.tax_uediscounted
382 );
383
384 /*
385 || Get the code combination id for the Earned Discount Account
386 */
387
388 IF rec_cur_get_receivable_app.tax_ediscounted <> 0 THEN
389
390 OPEN cur_get_disc_ccid ('EDISC','RECEIVABLES_TRX','A'); --rchandan for bug#4428980
391 FETCH cur_get_disc_ccid INTO rec_cur_get_disc_ccid;
392
393 IF CUR_GET_DISC_CCID%NOTFOUND THEN
394 CLOSE cur_get_receivable_app;
395 CLOSE cur_get_disc_ccid;
396 p_process_flag := jai_constants.expected_error ;
397 p_process_message := 'Earned Discount Account Setup not found in ar_receivables_trx_all ';
398 fnd_file.put_line(fnd_file.LOG,' 6 EXPECTED ERROR - Earned Discount Account Setup not found in ar_receivables_trx_all ');
399 return;
400 END IF;
401 p_earned_disc_ccid := rec_cur_get_disc_ccid.code_combination_id ;
402 CLOSE cur_get_disc_ccid;
403 END IF;
404
405 fnd_file.put_line(fnd_file.LOG,' 6 Earned Discount Account code combination id is '||rec_cur_get_disc_ccid.code_combination_id);
406 /*
407 || Get the code combination id for the Unearned Discount Account
408 */
409 IF rec_cur_get_receivable_app.tax_uediscounted <> 0 THEN
410
411 OPEN cur_get_disc_ccid ('UNEDISC','RECEIVABLES_TRX','A'); --rchandan for bug#4428980
412 FETCH cur_get_disc_ccid INTO rec_cur_get_disc_ccid;
413
414 IF CUR_GET_DISC_CCID%NOTFOUND THEN
415 CLOSE cur_get_receivable_app;
416 CLOSE cur_get_disc_ccid;
417 p_process_flag := jai_constants.expected_error ;
418 p_process_message := 'UnEarned Discount Account Setup not found in ar_receivables_trx_all ';
419 fnd_file.put_line(fnd_file.LOG,' 8 EXPECTED ERROR - UnEarned Discount Account Setup not found in ar_receivables_trx_all ');
420 return;
421 END IF;
422 p_unearned_disc_ccid := rec_cur_get_disc_ccid.code_combination_id ;
423 CLOSE cur_get_disc_ccid;
424
425 END IF;
426 fnd_file.put_line(fnd_file.LOG,' 9 Unearned Account CCID is -> '||rec_cur_get_disc_ccid.code_combination_id);
427 CLOSE cur_get_receivable_app;
428 fnd_file.put_line(fnd_file.LOG,' 10 Value of out variables p_tax_ediscounted ->' || p_tax_ediscounted
429 ||', p_earned_disc_ccid ->' || p_earned_disc_ccid
430 ||', p_tax_uediscounted ->' || p_tax_uediscounted
431 ||', p_unearned_disc_ccid ->' || p_unearned_disc_ccid
432 ||', p_process_flag ->' || p_process_flag
433 ||', p_process_message ->' || p_process_message
434 );
435 fnd_file.put_line(fnd_file.LOG,'********************* 10 END OF PROCEDURE jai_ar_rgm_processing_pkg.GET_AR_TAX_DISC_ACCNT *********************');
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 p_process_flag := jai_constants.unexpected_error ;
440 p_process_message := 'Unexpeced error occured in procedure get_ar_tax_disc_accnt for receivable_application_id -> '||p_receivable_application_id ||substr(SQLERRM,1,300);
441
442 END get_ar_tax_disc_accnt ;
443
444
445
446 procedure populate_inv_cm_references ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
447 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
448 p_from_date IN DATE ,
449 p_to_date IN DATE ,
450 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
451 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
452 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
453 p_process_flag OUT NOCOPY VARCHAR2 ,
454 p_process_message OUT NOCOPY VARCHAR2,
455 p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL
456 )
457 IS
458 /******************************
459 ||Variable Declaration Section
460 *******************************/
461 ln_reference_id JAI_RGM_TRX_REFS.REFERENCE_ID%TYPE ;
462 lv_process_flag VARCHAR2(2) ;
463 lv_process_message VARCHAR2(1996) ;
464 ln_invoices_processed NUMBER(10) ;
465
466 /******************************
467 ||Cursor Declarations Section
468 *******************************/
469
470 CURSOR c_fetch_inv_cm_rec ( cp_invoice_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_INV%TYPE ,
471 cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
472 cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
473 cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
474 cp_sh_service_edu_cess JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS%TYPE --Added By Bgowrava for forward porting bug#5989740
475 )
476 IS
477 SELECT
478 trx.customer_trx_id ,
479 trx.invoice_currency_code ,
480 trx.exchange_date ,
481 trx.exchange_rate ,
482 trx.org_id ,
483 trx.cust_trx_type_id ,
484 trx.previous_customer_trx_id ,
485 nvl(trx.bill_to_customer_id,trx.ship_to_customer_id) customer_id ,
486 nvl(trx.bill_to_site_use_id,trx.ship_to_site_use_id) customer_site_id ,
487 jtc.tax_type tax_type ,
488 nvl(decode(upper(trx_types.type),cp_cm_type,'Y','N'),'N') reversal_flag ,
489 jtrxl.inventory_item_id ,
490 jtrxtl.customer_trx_line_id ,
491 jtrxtl.tax_id ,
492 jtrxtl.tax_rate ,
493 jtrxtl.tax_amount ,
494 jtrxtl.func_tax_amount ,
495 jtrxtl.base_tax_amount ,
496 decode(upper(trx_types.type),cp_invoice_type,nvl(jtc.mod_cr_percentage,0),100) mod_cr_percentage ,
497 jtrxtl.link_to_cust_trx_line_id
498 FROM
499 ra_customer_trx_all trx ,
500 JAI_AR_TRXS jtrx ,
501 ra_cust_trx_types_all trx_types ,
502 JAI_AR_TRX_LINES jtrxl ,
503 JAI_AR_TRX_TAX_LINES jtrxtl ,
504 JAI_CMN_TAXES_ALL jtc
505 WHERE
506 trx.org_id = nvl(p_org_id,trx.org_id) AND
507 trx.complete_flag ='Y' AND
508 trx.customer_trx_id = jtrx.customer_trx_id AND
509 jtrx.organization_id = p_organization_id AND/*5879769*/
510 /* nvl(jtrx.tax_amount,0) <> 0 AND *//*Safeguard against invoice tax amount being null or zero and service type of taxes still existing at tax level */
511 /*trunc(trx.creation_date)*/ trunc(trx.trx_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND /*Commented by nprashar for bug # 6997453*/
512 upper(trx_types.type) IN (cp_invoice_type,cp_cm_type) AND
513 trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
514 trx_types.org_id = trx.org_id AND
515 jtrx.customer_trx_id = jtrxl.customer_trx_id AND
516 jtrxl.customer_trx_line_id = jtrxtl.link_to_cust_trx_line_id AND
517 jtrxtl.tax_id = jtc.tax_id AND
518 upper(jtc.tax_type) IN ( cp_service_tax,cp_service_edu_cess ,cp_sh_service_edu_cess) AND -- cp_sh_service_edu_cess Bgowrava for forward porting bug#5989740 AND
519 ( /**** Check that in case of INV mod_Cr_percentage should be > 0 and no check in case of CM ****/
520 (
521 upper(trx_types.type) = cp_cm_type
522 ) OR
523 (
524 upper(trx_types.type) = cp_invoice_type AND
525 nvl(jtc.mod_cr_percentage,0) > 0
526 )
527 ) AND
528 NOT EXISTS ( SELECT /*A ref of invoice/cm should not exist in the reference table */
529 1
530 FROM jai_rgm_trx_refs rgtr
531 WHERE
532 rgtr.source = p_source AND
533 rgtr.invoice_id = trx.customer_trx_id AND
534 rgtr.line_id = jtrxtl.customer_trx_line_id AND
535 rgtr.item_line_id = jtrxtl.link_to_cust_trx_line_id AND
536 rgtr.tax_id = jtrxtl.tax_id
537 )
538 ORDER BY
539 trx_types.type desc;
540
541 BEGIN
542
543
544 /*****
545 ||Based on the input parameters get invoice details
546 ||for which localization has corresponding service tax
547 ******/
548
549 fnd_file.put_line(fnd_file.LOG,'1 Entering procedure : jai_ar_rgm_processing_pkg.populate_inv_cm_references' );
550
551 fnd_file.put_line(fnd_file.LOG,'p_org_id:'||p_org_id );
552 fnd_file.put_line(fnd_file.LOG,'p_organization_id:'||p_organization_id );
553
554 /*
555 || Variable Initialization
556 */
557
558 lv_process_flag := jai_constants.successful ;
559 lv_process_message := NULL ;
560
561 p_process_flag := lv_process_flag ;
562 p_process_message := lv_process_message ;
563
564 ln_invoices_processed := 0 ;
565
566 FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
567 cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
568 cp_service_tax => upper(jai_constants.tax_type_service) ,
569 cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
570 cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
571 )
572 LOOP
573
574 fnd_file.put_line(fnd_file.LOG,'2 processing record, customer_trx_id '||rec_c_fetch_inv_cm_rec.customer_trx_id
575 ||', invoice line id '||rec_c_fetch_inv_cm_rec.link_to_cust_trx_line_id
576 ||', tax line id '||rec_c_fetch_inv_cm_rec.customer_trx_line_id
577 ||', tax_id '|| rec_c_fetch_inv_cm_rec.tax_id
578 ||', tax rate '||rec_c_fetch_inv_cm_rec.tax_rate
579 ||', transactional tax amount '||rec_c_fetch_inv_cm_rec.tax_amount
580 ||', functional tax amount '||rec_c_fetch_inv_cm_rec.func_tax_amount
581 ||', currency '||rec_c_fetch_inv_cm_rec.invoice_currency_code
582 ||', recoverable percentage '||rec_c_fetch_inv_cm_rec.mod_cr_percentage
583 ||', recoverable_amount '||rec_c_fetch_inv_cm_rec.tax_amount * (rec_c_fetch_inv_cm_rec.mod_cr_percentage/100)
584 );
585
586 /****
587 ||insert the invoices and credit memo's into the jai_rgm_trx_refs
588 ||using the procedure jai_cmn_rgm_recording_pkg.insert_reference
589 *****/
590 fnd_file.put_line(fnd_file.LOG,'3 before call to procedure jai_cmn_rgm_recording_pkg.insert_reference ');
591
592 savepoint before_ref_inv_cm;
593
594 jai_cmn_rgm_recording_pkg.insert_reference (
595 p_reference_id => ln_reference_id ,
596 p_organization_id => p_organization_id ,/*5879769*/
597 p_source => p_source ,
598 p_invoice_id => rec_c_fetch_inv_cm_rec.customer_trx_id ,
599 p_line_id => rec_c_fetch_inv_cm_rec.customer_trx_line_id ,
600 p_tax_type => rec_c_fetch_inv_cm_rec.tax_type ,
601 p_tax_id => rec_c_fetch_inv_cm_rec.tax_id ,
602 p_tax_rate => rec_c_fetch_inv_cm_rec.tax_rate ,
603 p_recoverable_ptg => rec_c_fetch_inv_cm_rec.mod_cr_percentage ,
604 p_party_type => jai_constants.party_type_customer ,
605 p_party_id => rec_c_fetch_inv_cm_rec.customer_id ,
606 p_party_site_id => rec_c_fetch_inv_cm_rec.customer_site_id ,
607 p_trx_tax_amount => rec_c_fetch_inv_cm_rec.tax_amount ,
608 p_trx_currency => rec_c_fetch_inv_cm_rec.invoice_currency_code ,
609 p_curr_conv_date => rec_c_fetch_inv_cm_rec.exchange_date ,
610 p_curr_conv_rate => rec_c_fetch_inv_cm_rec.exchange_rate ,
611 p_tax_amount => rec_c_fetch_inv_cm_rec.tax_amount * (rec_c_fetch_inv_cm_rec.mod_cr_percentage/100) ,
612 p_recoverable_amount => rec_c_fetch_inv_cm_rec.tax_amount * (rec_c_fetch_inv_cm_rec.mod_cr_percentage/100) ,
613 p_recovered_amount => 0 ,
614 p_item_line_id => rec_c_fetch_inv_cm_rec.link_to_cust_trx_line_id ,
615 p_item_id => rec_c_fetch_inv_cm_rec.inventory_item_id ,
616 p_taxable_basis => rec_c_fetch_inv_cm_rec.base_tax_amount ,
617 p_parent_reference_id => NULL ,
618 p_reversal_flag => rec_c_fetch_inv_cm_rec.reversal_flag ,
619 p_batch_id => p_batch_id ,
620 p_process_flag => lv_process_flag ,
621 p_process_message => lv_process_message
622 );
623
624 fnd_file.put_line(fnd_file.LOG,'4 returned from procedure jai_cmn_rgm_recording_pkg.insert_reference, lv_process_flag - '||lv_process_flag
625 ||'lv_process_message - '||lv_process_message);
626
627
628 IF lv_process_flag = jai_constants.expected_error OR
629 lv_process_flag = jai_constants.unexpected_error
630 THEN
631 /*
632 || as Returned status is an error hence:-
633 ||1. Rollback to save point
634 ||2. Set out variables p_process_flag and p_process_message accordingly
635 */
636 ROLLBACK to before_ref_inv_cm;
637 fnd_file.put_line( fnd_file.log, '5 error in call to jai_cmn_rgm_recording_pkg.insert_reference - lv_process_flag '||lv_process_flag
638 ||', lv_process_message'||lv_process_message);
639 p_process_flag := lv_process_flag ;
640 p_process_message := lv_process_message ;
641 END IF;
642
643 ln_invoices_processed := ln_invoices_processed + 1;
644 fnd_file.put_line(fnd_file.LOG,'6 inserted record in jai_rgm_trx_refs with reference_id '||ln_reference_id );
645
646 END LOOP;
647
648 fnd_file.put_line(fnd_file.LOG,'5 End of procedure : jai_rgm_process_ar.populate_inv_cm_references, number of invoices/CM processed '||ln_invoices_processed );
649
650 EXCEPTION
651 WHEN OTHERS THEN
652
653 lv_process_flag := jai_constants.unexpected_error;
654 lv_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_inv_cm_references'||substr(SQLERRM,1,500) ;
655 ROLLBACK to before_ref_inv_cm;
656
657 END populate_inv_cm_references;
658
659 procedure delete_non_existant_cm ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
660 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
661 p_from_date IN DATE ,
662 p_to_date IN DATE ,
663 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
664 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
665 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
666 p_process_flag OUT NOCOPY VARCHAR2 ,
667 p_process_message OUT NOCOPY VARCHAR2
668 ,p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
669 IS
670
671 ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
672 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
673 ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
674 ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
675 ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
676 ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
677 lv_process_flag VARCHAR2(2) ;
678 lv_process_message VARCHAR2(1996) ;
679 lv_source_trx_type VARCHAR2(50) ;
680 ln_uncommitted_transactions NUMBER(10) := 0 ;
681 lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ; --rchandan for bug#4428980
682 lv_source_table jai_rgm_trx_records.source_table_name%TYPE ; --rchandan for bug#4428980
683
684 /* Added by Ramananda for bug#4407165 */
685 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_rgm_processing_pkg.delete_non_existant_cm';
686
687 /*
688 || Get all the cm's which exist in the reference table jai_rgm_trx_refs and have been incompleted/incompleted and deleted from AR base table
689 || IF a CM has been incompleted then it would exist with complete_flag = 'N' . if a CM has been incompleted and deleted then it would not exist
690 || in the ra_customer_trx_all table.
691 */
692 CURSOR c_get_incompleted_cm
693 IS
694 SELECT
695 rgtr.invoice_id cm_customer_trx_id /*,
696 rgtr.reference_id cm_reference_id */
697 FROM
698 jai_rgm_trx_refs rgtr
699 WHERE
700 rgtr.source = p_source AND
701 rgtr.organization_id = p_organization_id AND /*5879769*/
702 rgtr.reversal_flag = 'Y' AND
703 nvl(rgtr.recovered_amount,0) <> 0 AND
704 NOT EXISTS ( SELECT
705 1
706 FROM
707 ra_customer_trx_all trx ,
708 ra_cust_trx_types_all trx_types
709 WHERE
710 trx.customer_trx_id = rgtr.invoice_id AND
711 trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
712 trx_types.org_id = trx.org_id AND
713 upper(trx_types.type) = upper(jai_constants.ar_invoice_type_cm) AND
714 trx.complete_flag = 'Y'
715 )
716 GROUP BY
717 rgtr.invoice_id;
718 /*
719 || Get all the data of the incompleted CM from the
720 || repository, so that the same record with an exactly opposite amount can be passed. This would be the CM-CM-REV record
721 */
722 CURSOR c_get_cm_cm_app_rec ( /*cp_cm_reference_id JAI_RGM_TRX_RECORDS.REFERENCE_ID%TYPE ,*/
723 cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE,
724 cp_attribute_context jai_rgm_trx_records.attribute_context%TYPE --rchandan for bug#4428980
725 )
726 IS
727 SELECT
728 *
729 FROM
730 jai_rgm_trx_records
731 WHERE
732 attribute_context = cp_attribute_context AND
733 attribute2 = cp_cm_customer_trx_id /* AND
734 reference_id = cp_cm_reference_id */;
735
736 /*
737 || Update all the credit memo reference records.
738 */
739 CURSOR cur_upd_cm_ref ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE )
740 IS
741 SELECT
742 *
743 FROM
744 jai_rgm_trx_refs
745 WHERE
746 invoice_id = cp_cm_customer_trx_id AND
747 nvl(recovered_amount,0) <> 0 ;
748
749 /*
750 || Get all the data of the CM applied to invoices (i.e CM-INV-APP) from the repository.
751 || So that the same record with an exactly opposite amount can be passed. This would be the new CM-INV-REV record
752 */
753 CURSOR c_get_cm_inv_app_rec ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE,
754 cp_attribute_context jai_rgm_trx_records.attribute_context%TYPE ) --rchandan for bug#4428980
755 IS
756 SELECT
757 *
758 FROM
759 jai_rgm_trx_records
760 WHERE
761 attribute_context = cp_attribute_context AND
762 attribute2 = cp_cm_customer_trx_id ;
763
764 BEGIN
765
766 /*
767 || Variable Intialization
768 */
769 lv_process_flag := jai_constants.successful ;
770 lv_process_message := null ;
771
772 p_process_flag := lv_process_flag ;
773 p_process_message := lv_process_message ;
774
775 fnd_file.put_line(fnd_file.LOG,'delete_non_existant_cm p_org_id:'||p_org_id );
776 fnd_file.put_line(fnd_file.LOG,'p_organization_id:'||p_organization_id );
777
778 /*
779 || Source type would indicate CREDIT MEMO REVERSAL
780 */
781 lv_source_trx_type := jai_constants.trx_type_cm_rvs ;
782
783 /*
784 || Update all the credit memo from reference, reset recovered_amount = 0
785 || Insert repository records ('CM-CM-APP') corresponding to the above effect
786 */
787 FOR rec_c_get_incompleted_cm IN c_get_incompleted_cm
788 LOOP
789
790 /*########################################################################################################
791 || SET SAVE POINT POINT FOR EACH CM_CUSTOMER_TRX_ID RECORD
792 ########################################################################################################*/
793 fnd_file.put_line(fnd_file.LOG,' ********************1 PROCESSING REC_C_GET_INCOMPLETED_CM.CM_CUSTOMER_TRX_ID -> '||rec_c_get_incompleted_cm.cm_customer_trx_id
794 ||' ******************** ');
795
796
797 /*
798 || Set the savepoint at the begin of processing every new cm_customer_trx_id from jai_rgm_refs table
799 || IF an error occurs while processing any reference record of a cm_customer_trx_id then the rollback
800 || can be done for all the records of that cm_customer_trx_id
801 */
802 savepoint roll_to_last_cm;
803
804
805
806 /*########################################################################################################
807 || PASS CM-CM-REV RECORD ENTRIES IN REPOSITORY AND UPDATE THE CM REFERENCES
808 || Insert Credit Memo repository entries to the effect of the CM incompletion.
809 || This record would be exactly opposite of the earlier CM creation repository record
810 ########################################################################################################*/
811
812
813 IF nvl(ln_err_cm_customer_trx_id,-1) <> nvl(rec_c_get_incompleted_cm.cm_customer_trx_id,-1) THEN
814
815 FOR rec_c_get_cm_cm_app_rec IN c_get_cm_cm_app_rec ( /*cp_cm_reference_id => rec_c_get_incompleted_cm.cm_reference_id ,*/
816 cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id,
817 cp_attribute_context => 'CM-CM-APP'
818 ) --rchandan for bug#4428980
819 LOOP
820
821 /*
822 || Insert a record into the repository corresponding to the 'CM-CM-REV'
823 */
824 fnd_file.put_line(fnd_file.LOG,' 3 Passing CM-CM-REV record, for the CM-CM-APP with rec_c_get_cm_cm_app_rec.cm_customer_trx_id -> '||rec_c_get_cm_cm_app_rec.attribute2
825 ||' and reference_id -> '||rec_c_get_cm_cm_app_rec.reference_id
826 ||',repository_id -> '|| rec_c_get_cm_cm_app_rec.repository_id);
827
828
829
830 ln_amount := nvl(rec_c_get_cm_cm_app_rec.debit_amount,rec_c_get_cm_cm_app_rec.credit_amount) * (-1);
831
832 fnd_file.put_line(fnd_file.LOG,' 3.1 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry original amount -> '||nvl(rec_c_get_cm_cm_app_rec.debit_amount,rec_c_get_cm_cm_app_rec.credit_amount)
833 ||', reversal entry amount -> '||ln_amount );
834
835 /*csahoo for bug#5879769...start*/
836
837 ln_organization_id := NULL;
838 ln_location_id := NULL;
839 lv_service_type_code := NULL;
840 lv_process_flag := NULL;
841 lv_process_message := NULL;
842
843 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_c_get_cm_cm_app_rec.reference_id,
844 p_organization_id => ln_organization_id,
845 p_location_id => ln_location_id,
846 p_service_type_code => lv_service_type_code,
847 p_process_flag => lv_process_flag,
848 p_process_message => lv_process_message
849 );
850
851 IF lv_process_flag <> jai_constants.successful THEN
852 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
853 return;
854 END IF;
855
856 /*csahoo for bug#5879769...end*/
857
858 lv_attribute_context := 'CM-CM-REV'; --rchandan for bug#4428980
859
860 jai_cmn_rgm_recording_pkg.insert_repository_entry (
861 p_repository_id => ln_repository_id ,
862 p_regime_id => p_regime_id ,
863 p_tax_type => rec_c_get_cm_cm_app_rec.tax_type ,
864 p_organization_type => p_organization_type ,
865 p_organization_id => p_organization_id ,/*5879769*/
866 p_location_id => ln_location_id ,/*5879769*/
867 p_service_type_code => lv_service_type_code ,/*5879769*/
868 p_source => p_source ,
869 p_source_trx_type => lv_source_trx_type ,
870 p_source_table_name => UPPER(jai_constants.repository_name) ,
871 p_source_document_id => rec_c_get_cm_cm_app_rec.repository_id ,
872 p_transaction_date => rec_c_get_cm_cm_app_rec.creation_date ,
873 p_account_name => NULL ,
874 p_charge_account_id => NULL ,
875 p_balancing_account_id => NULL ,
876 p_amount => ln_amount ,
877 p_assessable_value => NULL ,
878 p_tax_rate => rec_c_get_cm_cm_app_rec.tax_rate ,
879 p_reference_id => rec_c_get_cm_cm_app_rec.reference_id ,
880 p_batch_id => p_batch_id ,
881 p_called_from => lv_object_name , --rchandan for bug#4428980
882 p_process_flag => lv_process_flag ,
883 p_process_message => lv_process_message ,
884 p_discounted_amount => ln_discounted_amount ,
885 p_inv_organization_id => rec_c_get_cm_cm_app_rec.inv_organization_id ,
886 p_accounting_date => sysdate ,
887 p_currency_code => rec_c_get_cm_cm_app_rec.trx_currency ,
888 p_curr_conv_date => rec_c_get_cm_cm_app_rec.curr_conv_date ,
889 p_curr_conv_type => NULL ,
890 p_curr_conv_rate => rec_c_get_cm_cm_app_rec.curr_conv_rate ,
891 p_trx_amount => ln_amount ,
892 p_attribute_context => lv_attribute_context ,
893 p_attribute2 => rec_c_get_incompleted_cm.cm_customer_trx_id
894 , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
895 );
896
897 fnd_file.put_line(fnd_file.LOG,' 4 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
898
899
900 IF lv_process_flag = jai_constants.expected_error OR
901 lv_process_flag = jai_constants.unexpected_error
902 THEN
903 /*
904 || as Returned status is an error hence:-
905 ||1. set the ln_err_cm_customer_trx_id to the cm_customer_Trx_id which errored out
906 ||2. Rollback to save point
907 ||3. Set out variables p_process_flag and p_process_message accordingly
908 ||4. Exit from the loop
909 */
910 ln_err_cm_customer_trx_id := rec_c_get_incompleted_cm.cm_customer_trx_id;
911 ROLLBACK to roll_to_last_cm;
912 fnd_file.put_line( fnd_file.log, '5 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
913 ||', lv_process_message'||lv_process_message ||'cm_customer_trx_id - '||ln_err_cm_customer_trx_id);
914 p_process_flag := lv_process_flag ;
915 p_process_message := lv_process_message ;
916 EXIT;
917 END IF;
918
919 END LOOP; /*End of 'CM-CM-REV' record processing for an incompleted CM*/
920 END IF; /* End if of ln_err_cm_customer_trx_id <> rec_c_get_incompleted_cm.cm_customer_trx_id*/
921
922 IF nvl(ln_err_cm_customer_trx_id,-1) <> nvl(rec_c_get_incompleted_cm.cm_customer_trx_id,-1) THEN
923
924 /*########################################################################################################
925 || Update the Credit Reference and set Recovered Amount to 0 as this credit memo has been incompleted
926 ########################################################################################################*/
927 FOR rec_cur_upd_cm_ref IN cur_upd_cm_ref (cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id)
928 LOOP
929 fnd_file.put_line(fnd_file.LOG,' 6 before call to jai_cmn_rgm_recording_pkg.update_reference for updating CM reference to 0-> '||rec_c_get_incompleted_cm.cm_customer_trx_id
930 ||', reference_id -> '||rec_cur_upd_cm_ref.reference_id );
931
932 jai_cmn_rgm_recording_pkg.update_reference (
933 p_source => p_source ,
934 p_reference_id => rec_cur_upd_cm_ref.reference_id ,
935 p_recovered_amount => rec_cur_upd_cm_ref.recovered_amount * (-1) ,
936 p_process_flag => lv_process_flag ,
937 p_process_message => lv_process_message
938 );
939 IF lv_process_flag = jai_constants.expected_error OR
940 lv_process_flag = jai_constants.unexpected_error
941 THEN
942 /*
943 || as Returned status is an error hence:-
944 || Set out variables p_process_flag and p_process_message accordingly
945 */
946 ln_err_cm_customer_trx_id := rec_c_get_incompleted_cm.cm_customer_trx_id;
947 ROLLBACK to roll_to_last_cm;
948 fnd_file.put_line( fnd_file.log, '7 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
949 ||', lv_process_message'||lv_process_message);
950 p_process_flag := lv_process_flag ;
951 p_process_message := lv_process_message ;
952 END IF;
953
954 fnd_file.put_line(fnd_file.LOG,' 8 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating CM reference' );
955 END LOOP;
956
957 END IF; /* End if of ln_err_cm_customer_trx_id <> rec_c_get_incompleted_cm.cm_customer_trx_id*/
958
959
960 /*########################################################################################################
961 || PASS CM-INV-REV ENTRIES , UPDATE INV REFERENCES
962 || Reverse CM application to invoices into repository entries to the effect of the CM incompletion.
963 || This record would be exactly opposite of the earlier CM creation repository record
964 ########################################################################################################*/
965 IF nvl(ln_err_cm_customer_trx_id,-1) <> nvl(rec_c_get_incompleted_cm.cm_customer_trx_id,-1) THEN
966
967 FOR rec_c_get_cm_inv_app_rec IN c_get_cm_inv_app_rec ( cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id,
968 cp_attribute_context => 'CM-INV-APP' )
969 LOOP
970
971 fnd_file.put_line(fnd_file.LOG,' 9 Passing CM-INV-REV record, for the CM-INV-APP with rec_c_get_cm_inv_app_rec.inv_customer_trx_id -> '||rec_c_get_cm_inv_app_rec.attribute1
972 ||', rec_c_get_cm_inv_app_rec.cm_customer_trx_id -> '||rec_c_get_cm_inv_app_rec.attribute2
973 ||' reference_id -> '||rec_c_get_cm_inv_app_rec.reference_id
974 ||', repository_id -> '|| rec_c_get_cm_inv_app_rec.repository_id);
975
976 /*
977 || Insert a record into the repository corresponding to the 'CM-CM-REV'
978 */
979 fnd_file.put_line(fnd_file.LOG,' 9.1 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
980
981 ln_amount := nvl(rec_c_get_cm_inv_app_rec.debit_amount,rec_c_get_cm_inv_app_rec.credit_amount) * (-1);
982
983 fnd_file.put_line(fnd_file.LOG,' 10 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry original amount -> '||nvl(rec_c_get_cm_inv_app_rec.debit_amount,rec_c_get_cm_inv_app_rec.credit_amount)
984 ||', reversal entry amount -> '||ln_amount );
985
986 /*csahoo for bug#5879769...start*/
987
988 ln_organization_id := NULL;
989 ln_location_id := NULL;
990 lv_service_type_code := NULL;
991 lv_process_flag := NULL;
992 lv_process_message := NULL;
993
994 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_c_get_cm_inv_app_rec.reference_id,
995 p_organization_id => ln_organization_id,
996 p_location_id => ln_location_id,
997 p_service_type_code => lv_service_type_code,
998 p_process_flag => lv_process_flag,
999 p_process_message => lv_process_message
1000 );
1001
1002 IF lv_process_flag <> jai_constants.successful THEN
1003 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1004 return;
1005 END IF;
1006
1007 /*csahoo for bug#5879769...end*/
1008
1009 lv_attribute_context := 'CM-INV-REV'; --rchandan for bug#4428980
1010 lv_source_table := 'JAI_RGM_TRX_RECORDS'; --rchandan for bug#4428980
1011 jai_cmn_rgm_recording_pkg.insert_repository_entry (
1012 p_repository_id => ln_repository_id ,
1013 p_regime_id => p_regime_id ,
1014 p_tax_type => rec_c_get_cm_inv_app_rec.tax_type ,
1015 p_organization_type => p_organization_type ,
1016 p_organization_id => ln_organization_id ,/*5879769*/
1017 p_location_id => ln_location_id ,/*5879769*/
1018 p_service_type_code => lv_service_type_code ,/*5879769*/
1019 p_source => p_source ,
1020 p_source_trx_type => lv_source_trx_type ,
1021 p_source_table_name => lv_source_table , --rchandan for bug#4428980
1022 p_source_document_id => rec_c_get_cm_inv_app_rec.repository_id ,
1023 p_transaction_date => rec_c_get_cm_inv_app_rec.creation_date ,
1024 p_account_name => NULL ,
1025 p_charge_account_id => NULL ,
1026 p_balancing_account_id => NULL ,
1027 p_amount => ln_amount ,
1028 p_assessable_value => NULL ,
1029 p_tax_rate => rec_c_get_cm_inv_app_rec.tax_rate ,
1030 p_reference_id => rec_c_get_cm_inv_app_rec.reference_id ,
1031 p_batch_id => p_batch_id ,
1032 p_called_from => lv_object_name ,
1033 p_process_flag => lv_process_flag ,
1034 p_process_message => lv_process_message ,
1035 p_discounted_amount => ln_discounted_amount ,
1036 p_inv_organization_id => rec_c_get_cm_inv_app_rec.inv_organization_id ,
1037 p_accounting_date => sysdate ,
1038 p_currency_code => rec_c_get_cm_inv_app_rec.trx_currency ,
1039 p_curr_conv_date => rec_c_get_cm_inv_app_rec.curr_conv_date ,
1040 p_curr_conv_type => NULL ,
1041 p_curr_conv_rate => rec_c_get_cm_inv_app_rec.curr_conv_rate ,
1042 p_trx_amount => ln_amount , --rchandan for bug#4428980
1043 p_attribute_context => lv_attribute_context ,
1044 p_attribute1 => rec_c_get_cm_inv_app_rec.attribute1 ,
1045 p_attribute2 => rec_c_get_cm_inv_app_rec.attribute2
1046 , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
1047 );
1048
1049 fnd_file.put_line(fnd_file.LOG,' 10.1 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1050
1051
1052 IF lv_process_flag = jai_constants.expected_error OR
1053 lv_process_flag = jai_constants.unexpected_error
1054 THEN
1055 /*
1056 || as Returned status is an error hence:-
1057 ||1. Rollback to save point
1058 ||2. Set out variables p_process_flag and p_process_message accordingly
1059 */
1060 ln_err_cm_customer_trx_id := rec_c_get_incompleted_cm.cm_customer_trx_id;
1061 ROLLBACK to roll_to_last_cm;
1062 fnd_file.put_line( fnd_file.log, '11 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
1063 ||', lv_process_message'||lv_process_message);
1064 p_process_flag := lv_process_flag ;
1065 p_process_message := lv_process_message ;
1066 EXIT;
1067 END IF;
1068
1069
1070 /*########################################################################################################
1071 || Update the Recovered Amount of the Invoice Reference against the CM application
1072 ########################################################################################################*/
1073
1074 fnd_file.put_line(fnd_file.LOG,' 12 before call to jai_cmn_rgm_recording_pkg.update_reference for updating INV reference_id - '||rec_c_get_cm_inv_app_rec.reference_id
1075 ||', amount to be adjusted from recovered_Amount -> '||nvl(rec_c_get_cm_inv_app_rec.debit_amount,rec_c_get_cm_inv_app_rec.credit_amount) * (-1)
1076 );
1077
1078
1079 jai_cmn_rgm_recording_pkg.update_reference (
1080 p_source => p_source ,
1081 p_reference_id => rec_c_get_cm_inv_app_rec.reference_id ,
1082 p_recovered_amount => ln_amount ,
1083 p_process_flag => lv_process_flag ,
1084 p_process_message => lv_process_message
1085 );
1086 IF lv_process_flag = jai_constants.expected_error OR
1087 lv_process_flag = jai_constants.unexpected_error
1088 THEN
1089 /*
1090 || as Returned status is an error hence:-
1091 || Set out variables p_process_flag and p_process_message accordingly
1092 */
1093 ln_err_cm_customer_trx_id := rec_c_get_incompleted_cm.cm_customer_trx_id;
1094 ROLLBACK to roll_to_last_cm;
1095 fnd_file.put_line( fnd_file.log, '13 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
1096 ||', lv_process_message'||lv_process_message);
1097 p_process_flag := lv_process_flag ;
1098 p_process_message := lv_process_message ;
1099 EXIT;
1100 END IF;
1101
1102 fnd_file.put_line(fnd_file.LOG,' 14 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating invoice reference' );
1103
1104 END LOOP; /* End of 'CM-INV-APP record processing for an CM application against an invoice' */
1105
1106 END IF; /* End if of ln_err_cm_customer_trx_id <> rec_c_get_incompleted_cm.cm_customer_trx_id*/
1107
1108 /*
1109 || Reset the savepoint to the previous cm_customer_trx_id
1110 ln_set_save_point := rec_c_get_incompleted_cm.cm_customer_trx_id;
1111 */
1112 fnd_file.put_line(fnd_file.LOG,' 15 ******************** END of one CM processing ******************** ');
1113 END LOOP; /* End of CM reference processing */
1114
1115
1116 /* Added by Ramananda for bug#4407165 */
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 p_process_flag := null;
1120 p_process_message := null;
1121 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1122 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1123 app_exception.raise_exception;
1124
1125 END delete_non_existant_cm;
1126
1127
1128
1129 procedure populate_cm_app ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
1130 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
1131 p_from_date IN DATE ,
1132 p_to_date IN DATE ,
1133 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
1134 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1135 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
1136 p_process_flag OUT NOCOPY VARCHAR2 ,
1137 p_process_message OUT NOCOPY VARCHAR2
1138 , p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL )
1139 IS
1140
1141 ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1142 ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1143 ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1144 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1145 lv_source_trx_type VARCHAR2(50) ;
1146 ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1147 ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1148 ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1149 ln_sign_of_credit_memo NUMBER ;
1150 ln_cm_ref_ratio NUMBER ;
1151 ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1152 ln_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE := -1 ;
1153 lv_process_flag VARCHAR2(2) ;
1154 lv_process_message VARCHAR2(1996) ;
1155 ln_uncommitted_transactions NUMBER(10) := 0 ;
1156 lv_service_type_code JAI_AR_TRX_LINES.service_type_code%TYPE ;/*5879769*/
1157 lv_application_type ar_receivable_applications_all.application_type%TYPE ;--rchandan for bug#4428980
1158 lv_status ar_receivable_applications_all.status%TYPE ;--rchandan for bug#4428980
1159 lv_type ra_cust_trx_types_all.type%TYPE ;--rchandan for bug#4428980
1160 lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AR_RECEIVABLE_APPLICATIONS_ALL';--rchandan for bug#4428980
1161 lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.POPULATE_CM_APP';--rchandan for bug#4428980
1162 lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ;--rchandan for bug#4428980
1163
1164 ln_total_tax_applied NUMBER; --added by walton for inclusive tax 29-Nov-07
1165 ln_line_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1166 ln_inclusive_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1167
1168 /*
1169 || Get the credit memo applications to invoices and the total invoice tax amount .
1170 || Consider only those credit memo applications which follow the following conditions:-
1171 || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
1172 || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
1173 || 3. Consider cash receipt application against an invoice only
1174 || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount - discounted_amount in references table
1175 */
1176
1177
1178 CURSOR c_get_cm_rec_app ( cp_source_ar varchar2) --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE )
1179 IS
1180 SELECT
1181 aral.customer_trx_id cm_customer_trx_id ,
1182 aral.applied_customer_trx_id inv_customer_trx_id ,
1183 aral.receivable_application_id ,
1184 aral.gl_date ,
1185 nvl(aral.tax_applied,0) cm_app_tax_amt ,
1186 nvl(aral.line_applied,0) cm_app_line_amt , --added by walton for inclusive tax 29-Nov-07
1187 nvl(aral.amount_applied,0) cm_app_amount ,
1188 cm_trx.trx_date cm_transaction_date ,
1189 cm_trx.invoice_currency_code cm_currency_code ,
1190 cm_trx.exchange_date cm_exchange_date ,
1191 cm_trx.exchange_rate cm_exchange_rate ,
1192 cm_trx.exchange_rate_type cm_exchange_rate_type ,
1193 inv_jtrx.organization_id inv_invn_organization_id ,
1194 cm_jtrx.organization_id cm_invn_organization_id
1195 FROM
1196 ar_receivable_applications_all aral ,
1197 ra_customer_trx_all cm_trx ,
1198 ra_customer_trx_all inv_trx ,
1199 ra_cust_trx_types_all trx_types ,
1200 JAI_AR_TRXS inv_jtrx ,
1201 JAI_AR_TRXS cm_jtrx
1202 WHERE
1203 aral.customer_trx_id = cm_trx.customer_trx_id AND
1204 cm_trx.customer_trx_id = cm_jtrx.customer_trx_id AND
1205 aral.applied_customer_trx_id = inv_trx.customer_trx_id AND
1206 trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
1207 aral.application_type = lv_application_type AND--rchandan for bug#4428980
1208 aral.status = lv_status AND--rchandan for bug#4428980
1209 /*nvl(aral.tax_applied,0) <> 0 AND*/--Commented by walton for inclusive tax 29-Nov-07
1210 inv_trx.org_id = nvl(p_org_id,inv_trx.org_id) AND
1211 cm_trx.org_id = nvl(p_org_id,cm_trx.org_id) AND
1212 inv_trx.complete_flag = 'Y' AND
1213 cm_trx.complete_flag = 'Y' AND
1214 trx_types.cust_trx_type_id = inv_trx.cust_trx_type_id AND
1215 trx_types.type = lv_type AND--rchandan for bug#4428980
1216 trx_types.org_id = inv_trx.org_id AND
1217 inv_trx.customer_trx_id = inv_jtrx.customer_trx_id AND
1218 inv_jtrx.organization_id = p_organization_id AND/*5879769*/
1219 NOT EXISTS ( SELECT /*A credit memo application does not exist in repository */
1220 1
1221 FROM
1222 jai_rgm_trx_records rgtr
1223 WHERE
1224 rgtr.source = cp_source_ar AND
1225 rgtr.organization_id = p_organization_id AND -- Date 05/06/2007 by sacsethi for bug 6109941
1226 rgtr.source_table_name = lv_source_table AND--rchandan for bug#4428980
1227 rgtr.source_document_id = aral.receivable_application_id
1228 ) AND
1229 EXISTS (
1230 SELECT /* A credit memo exists in the reference table with total recoverable amount <> recovered amount*/
1231 1
1232 FROM
1233 jai_rgm_trx_refs rgtf
1234 WHERE
1235 rgtf.source = cp_source_ar AND
1236 rgtf.invoice_id = aral.customer_trx_id AND
1237 nvl(rgtf.recoverable_amount,0) <> nvl(rgtf.recovered_amount,0)
1238 ) AND
1239 EXISTS (
1240 SELECT /* A invoice exists in the reference table with total recoverable amount > recovered amount*/
1241 1
1242 FROM
1243 jai_rgm_trx_refs rgtf
1244 WHERE
1245 rgtf.source = cp_source_ar AND
1246 rgtf.invoice_id = aral.applied_customer_trx_id AND
1247 nvl(rgtf.recoverable_amount,0) - nvl(discounted_amount,0) > nvl(rgtf.recovered_amount,0)
1248 ) ;
1249
1250 /*
1251 || Get the Total transactional tax amount for the invoice
1252 */
1253 CURSOR cur_get_inv_tottax_amt (cp_inv_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE)
1254 IS
1255 SELECT
1256 nvl(sum(jrttl.tax_amount),0) inv_tot_tax_amount
1257 FROM
1258 JAI_AR_TRX_LINES jrtl ,
1259 JAI_AR_TRX_TAX_LINES jrttl
1260 WHERE
1261 jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
1262 jrtl.customer_trx_id = cp_inv_customer_trx_id ;
1263
1264
1265 /*
1266 || Get the sign of the credit memo
1267 */
1268 CURSOR cur_sign_of_cm (cp_cm_customer_trx_id JAI_AR_TRX_LINES.CUSTOMER_TRX_ID%TYPE )
1269 IS
1270 SELECT
1271 sign(nvl(sum(jrttl.tax_amount),0)) sign_of_credit_memo
1272 FROM
1273 JAI_AR_TRX_LINES jrtl ,
1274 JAI_AR_TRX_TAX_LINES jrttl
1275 WHERE
1276 jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
1277 jrtl.customer_trx_id = cp_cm_customer_trx_id;
1278
1279 --added by walton for inclusive tax 29-Nov-07
1280 -----------------------------------------------------
1281 --Get the Total CM inclusive tax amount for the cm,
1282 CURSOR cur_get_cm_inclusive_tax_amt
1283 ( pn_cm_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
1284 )
1285 IS
1286 SELECT
1287 nvl(sum(jrttl.tax_amount),0) inv_tot_inclusive_tax_amt
1288 FROM
1289 JAI_AR_TRX_LINES jrtl
1290 , JAI_AR_TRX_TAX_LINES jrttl
1291 , jai_cmn_taxes_all tax
1292 WHERE jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id
1293 AND jrtl.customer_trx_id = pn_cm_customer_trx_id
1294 AND jrttl.tax_id = tax.tax_id
1295 AND NVL(tax.inclusive_tax_flag,'N') = 'Y' ;
1296
1297 --Get the Total CM line amount,
1298 CURSOR cur_get_cm_line_amt
1299 ( pn_cm_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
1300 )
1301 IS
1302 SELECT
1303 nvl(sum(jrtl.line_amount),0) inv_tot_line_amt
1304 FROM
1305 JAI_AR_TRX_LINES jrtl
1306 WHERE jrtl.customer_trx_id = pn_cm_customer_trx_id;
1307 ----------------------------------------------------------
1308
1309 /*
1310 || Get the ref tax lines pertaining to the invoice against which the cash receipt has been applied : -
1311
1312 */
1313 CURSOR c_get_refinvrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1314 cp_inv_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1315 )
1316 IS
1317 SELECT
1318 reference_id ,
1319 tax_type ,
1320 tax_rate ,
1321 nvl(recoverable_amount,0) - nvl(discounted_amount,0) recoverable_amount ,
1322 nvl(recovered_amount,0) recovered_amount ,
1323 recoverable_ptg ,
1324 item_line_id /*5879769*/
1325 FROM
1326 jai_rgm_trx_refs
1327 WHERE
1328 source = cp_source_ar AND
1329 invoice_id = cp_inv_customer_trx_id AND
1330 nvl(recoverable_amount,0) - nvl(discounted_amount,0) > nvl(recovered_amount,0)
1331 FOR UPDATE NOWAIT ;
1332
1333
1334 CURSOR c_get_refcmrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1335 cp_cm_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1336 )
1337 IS
1338 SELECT
1339 reference_id ,
1340 tax_type ,
1341 tax_rate ,
1342 nvl(recoverable_amount,0) recoverable_amount ,
1343 nvl(recovered_amount,0) recovered_amount ,
1344 item_line_id /*5879769*/
1345 FROM
1346 jai_rgm_trx_refs
1347 WHERE
1348 source = cp_source_ar AND
1349 invoice_id = cp_cm_customer_trx_id AND
1350 nvl(recoverable_amount,0) <> nvl(recovered_amount,0)
1351 FOR UPDATE NOWAIT ;
1352
1353 /*
1354 || Get the total effective CM amount from the CM lines in reference table against which a CM application has to be recovered.
1355 */
1356 CURSOR c_get_cmref_totrd_amt ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1357 cp_cm_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1358 )
1359 IS
1360 SELECT
1361 nvl(sum(recoverable_amount - recovered_amount),0) tot_effcm_rb_amt
1362 FROM
1363 jai_rgm_trx_refs
1364 WHERE
1365 source = cp_source_ar AND
1366 invoice_id = cp_cm_customer_trx_id ;
1367
1368 BEGIN
1369
1370 fnd_file.put_line(fnd_file.LOG,'1 Entering procedure : jai_rgm_process_ar.populate_cm_app' );
1371 fnd_file.put_line(fnd_file.LOG,'p_org_id:'||p_org_id );
1372 fnd_file.put_line(fnd_file.LOG,'p_organization_id:'||p_organization_id );
1373
1374 p_process_flag := jai_constants.successful ;
1375 p_process_message := null ;
1376
1377 /*****
1378 || Get all the valid CM receivables
1379 *****/
1380 lv_status := 'APP';
1381 lv_application_type:= 'CM';
1382 lv_type:= 'INV';
1383 FOR rec_c_get_cm_rec_app IN c_get_cm_rec_app ( cp_source_ar => p_source )
1384 LOOP
1385
1386 fnd_file.put_line(fnd_file.LOG,'2 processing credit memo with receivable_application_id as '||rec_c_get_cm_rec_app.receivable_application_id
1387 ||'against invoice with customer_trx_id '||rec_c_get_cm_rec_app.inv_customer_trx_id );
1388
1389 /*########################################################################################################
1390 || INITIALIZING THE VARIABLES
1391 ########################################################################################################*/
1392 ln_receivable_application_id := null ;
1393 ln_amount := null ;
1394 ln_eff_cm_tax_amount := null ;
1395 ln_eff_cm_tottax_amt := null ;
1396 ln_tot_effcm_rb_amt := null ;
1397 ln_inv_tot_tax_amt := null ;
1398 ln_cm_ref_ratio := null ;
1399 ln_cm_ref_upd := null ;
1400 lv_source_trx_type := null ;
1401 lv_process_flag := jai_constants.successful ;
1402 lv_process_message := null ;
1403
1404 ln_inclusive_total_amt := null ; --added by walton for inclusive tax 29-Nov-07
1405
1406 fnd_file.put_line(fnd_file.LOG,'3 Variables initialized' );
1407
1408 /*########################################################################################################
1409 || SET A SAVEPOINT FOR EACH NEW CREDIT MEMO RECEIVABLE APPLICATION
1410 ########################################################################################################*/
1411
1412 SAVEPOINT roll_to_cm_app;
1413
1414
1415 /*########################################################################################################
1416 || GET THE FOLLOWING INFORMATION:-
1417 || 1.INVOICE TOTAL TAX AMOUNT
1418 || 2. SIGN OF CREDIT MEMO
1419 || 3. DETERMINE THE CM RECEIPT APPLICATION TYPE (CREDIT MEMO APPLICATION, CREDIT MEMO REVERSAL)
1420 ########################################################################################################*/
1421
1422 OPEN cur_get_inv_tottax_amt (cp_inv_customer_trx_id => rec_c_get_cm_rec_app.inv_customer_trx_id);
1423 FETCH cur_get_inv_tottax_amt INTO ln_inv_tot_tax_amt;
1424 CLOSE cur_get_inv_tottax_amt ;
1425 /*
1426 || get the sign of the credit memo
1427 */
1428 OPEN cur_sign_of_cm (cp_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id );
1429 FETCH cur_sign_of_cm INTO ln_sign_of_credit_memo;
1430 CLOSE cur_sign_of_cm;
1431
1432 --added by walton for inclusive tax 29-Nov-07
1433 ------------------------------------------------------------------------------------------------------
1434 OPEN cur_get_cm_inclusive_tax_amt( pn_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
1435 );
1436 FETCH cur_get_cm_inclusive_tax_amt
1437 INTO ln_inclusive_total_amt;
1438 CLOSE cur_get_cm_inclusive_tax_amt ;
1439
1440 OPEN cur_get_cm_line_amt( pn_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
1441 );
1442 FETCH cur_get_cm_line_amt
1443 INTO ln_line_total_amt;
1444 CLOSE cur_get_cm_line_amt ;
1445
1446 ln_total_tax_applied := rec_c_get_cm_rec_app.cm_app_tax_amt + (rec_c_get_cm_rec_app.cm_app_line_amt/ln_line_total_amt) * ln_inclusive_total_amt;
1447 ------------------------------------------------------------------------------------------------------
1448
1449 fnd_file.put_line(fnd_file.LOG,'5 start of determine application source type');
1450
1451 /*
1452 || If the CM tax amount is +ve then it is CREDIT MEMO REVERSAL/UNAPPLICATION
1453 || IF the CM tax receipt amount is -ve then it is CREDIT MEMO APPLICATION
1454 || This info would go into the source_trx_type in the repository table jai_rgm_trx_records .
1455 || This would help distinctly identify a CM application, CREDIT MEMO reversal
1456 || IF sign = 0 i.e CM amount = 0 then skip the cm application and proceed to the next
1457 */
1458
1459 IF ln_sign_of_credit_memo > 0 THEN
1460 /*
1461 || +ve credit memo hence CREDIT MEMO REVERSAL
1462 */
1463 lv_source_trx_type := jai_constants.trx_type_cm_rvs ;
1464
1465 ELSIF ln_sign_of_credit_memo < 0 THEN
1466 /*
1467 || -ve credit memo hence CREDIT MEMO APPLICATION
1468 */
1469 lv_source_trx_type := jai_constants.trx_type_cm_app ;
1470
1471 END IF;
1472
1473 fnd_file.put_line(fnd_file.LOG,'7 credit memo type is lv_source_trx_type-> '||lv_source_trx_type);
1474
1475
1476 IF ln_sign_of_credit_memo <> 0 THEN
1477 /*########################################################################################################
1478 || Fetch all invoice reference lines for processing/passing CM-INV-APP entries
1479 ########################################################################################################*/
1480
1481 FOR rec_c_get_refinvrec_for_upd IN c_get_refinvrec_for_upd ( cp_source_ar => p_source ,
1482 cp_inv_customer_trx_id => rec_c_get_cm_rec_app.inv_customer_trx_id
1483 )
1484 LOOP
1485
1486 fnd_file.put_line(fnd_file.LOG,'4 start of invoice reference tax line, rec_c_get_refinvrec_for_upd.reference_id -> '|| rec_c_get_refinvrec_for_upd.reference_id );
1487
1488
1489 /*########################################################################################################
1490 || Calculation of the Service Tax Component of Credit Memo Tax Amount which needs to be considered
1491 ########################################################################################################*/
1492
1493 fnd_file.put_line(fnd_file.LOG,'8 Start of Credit Memo Service Tax Component calculation');
1494
1495 ln_eff_cm_tax_amount := (abs(ln_total_tax_applied)/ln_inv_tot_tax_amt) * rec_c_get_refinvrec_for_upd.recoverable_amount; --modified by walton for inclusive tax 29-Nov-07
1496
1497 fnd_file.put_line(fnd_file.LOG,'9 rec_c_get_cm_rec_app.cm_app_tax_amt effective -> '||rec_c_get_cm_rec_app.cm_app_tax_amt
1498 ||', ln_inv_tot_tax_amt -> '||ln_inv_tot_tax_amt
1499 ||', rec_c_get_refinvrec_for_upd.recoverable_amount -> '||rec_c_get_refinvrec_for_upd.recoverable_amount
1500 ||', cash receipt tax amount is ln_eff_cm_tax_amount -> '||ln_eff_cm_tax_amount
1501 ||', rec_c_get_refinvrec_for_upd.recovered_amount -> '||rec_c_get_refinvrec_for_upd.recovered_amount);
1502
1503 /*########################################################################################################
1504 || Validate the effective credit memo tax component does not exceed the invoice recoverable amount on updation
1505 ########################################################################################################*/
1506
1507
1508 IF rec_c_get_refinvrec_for_upd.recovered_amount + abs(ln_eff_cm_tax_amount) > rec_c_get_refinvrec_for_upd.recoverable_amount THEN
1509 /*
1510 || +ve Credit Memo
1511 || Check that if recovered amount + credit Memo amount > recoverable amount.
1512 || IF yes then set Credit Memo amount = recoverable amount - recovered amount
1513 || so that the recovered amount never exceeds the recoverable amount
1514 */
1515 ln_eff_cm_tax_amount := (rec_c_get_refinvrec_for_upd.recoverable_amount - rec_c_get_refinvrec_for_upd.recovered_amount) ;
1516 fnd_file.put_line(fnd_file.LOG,'10 rec_c_get_refinvrec_for_upd.recovered_amount + ln_eff_cm_tax_amount -> rec_c_get_refinvrec_for_upd.recoverable_amount hence ln_eff_cm_tax_amount '||ln_eff_cm_tax_amount);
1517 END IF;
1518
1519
1520 /*########################################################################################################
1521 || Insert the effective Credit Memo tax amount into the repository
1522 ########################################################################################################*/
1523 /*
1524 || Make an entry into the repository with the apportioned Credit Memo Tax amount to be applied against a reference invoice
1525 */
1526 /* ln_amount := abs(ln_eff_cm_tax_amount) * ln_sign_of_credit_memo ; */
1527 ln_amount := abs(ln_eff_cm_tax_amount) ;
1528 fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1529
1530 /*csahoo for bug#5879769...start*/
1531
1532 ln_organization_id := NULL;
1533 ln_location_id := NULL;
1534 lv_service_type_code := NULL;
1535 lv_process_flag := NULL;
1536 lv_process_message := NULL;
1537
1538 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_c_get_refinvrec_for_upd.reference_id,
1539 p_organization_id => ln_organization_id,
1540 p_location_id => ln_location_id,
1541 p_service_type_code => lv_service_type_code,
1542 p_process_flag => lv_process_flag,
1543 p_process_message => lv_process_message
1544 );
1545
1546 IF lv_process_flag <> jai_constants.successful THEN
1547 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1548 return;
1549 END IF;
1550
1551 /*csahoo for bug#5879769...end*/
1552
1553 lv_attribute_context := 'CM-INV-APP';
1554 jai_cmn_rgm_recording_pkg.insert_repository_entry (
1555 p_repository_id => ln_repository_id ,
1556 p_regime_id => p_regime_id ,
1557 p_tax_type => rec_c_get_refinvrec_for_upd.tax_type ,
1558 p_organization_type => p_organization_type ,
1559 p_organization_id => ln_organization_id ,/*5879769*/
1560 p_location_id => ln_location_id ,/*5879769*/
1561 p_service_type_code => lv_service_type_code ,/*5879769*/
1562 p_source => p_source ,
1563 p_source_trx_type => lv_source_trx_type ,
1564 p_source_table_name => lv_source_table ,
1565 p_source_document_id => rec_c_get_cm_rec_app.receivable_application_id ,
1566 p_transaction_date => rec_c_get_cm_rec_app.cm_transaction_date ,
1567 p_account_name => NULL ,
1568 p_charge_account_id => NULL ,
1569 p_balancing_account_id => NULL ,
1570 p_amount => ln_amount ,
1571 p_assessable_value => NULL ,
1572 p_tax_rate => rec_c_get_refinvrec_for_upd.tax_rate ,
1573 p_reference_id => rec_c_get_refinvrec_for_upd.reference_id ,
1574 p_batch_id => p_batch_id ,
1575 p_called_from => lv_called_from ,
1576 p_process_flag => lv_process_flag ,
1577 p_process_message => lv_process_message ,
1578 p_discounted_amount => ln_discounted_amount ,
1579 p_inv_organization_id => ln_organization_id ,/*5879769*/
1580 p_accounting_date => rec_c_get_cm_rec_app.gl_date ,
1581 p_currency_code => rec_c_get_cm_rec_app.cm_currency_code ,
1582 p_curr_conv_date => rec_c_get_cm_rec_app.cm_exchange_date ,
1583 p_curr_conv_type => rec_c_get_cm_rec_app.cm_exchange_rate_type ,
1584 p_curr_conv_rate => rec_c_get_cm_rec_app.cm_exchange_rate ,
1585 p_trx_amount => ln_amount ,
1586 p_attribute_context => lv_attribute_context ,
1587 p_attribute1 => rec_c_get_cm_rec_app.inv_customer_trx_id ,
1588 p_attribute2 => rec_c_get_cm_rec_app.cm_customer_trx_id
1589 , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
1590 );
1591
1592 fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1593
1594
1595 IF lv_process_flag = jai_constants.expected_error OR
1596 lv_process_flag = jai_constants.unexpected_error
1597 THEN
1598 /*
1599 || As Returned status is an error hence:-
1600 ||1. Rollback to save point
1601 ||2. Set out variables p_process_flag and p_process_message accordingly
1602 */
1603 ln_receivable_application_id := rec_c_get_cm_rec_app.receivable_application_id ;
1604 ROLLBACK to roll_to_cm_app;
1605 fnd_file.put_line( fnd_file.log, '16 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
1606 ||', lv_process_message'||lv_process_message);
1607 p_process_flag := lv_process_flag ;
1608 p_process_message := lv_process_message ;
1609 EXIT;
1610 END IF;
1611
1612
1613 /*########################################################################################################
1614 || Update the Invoice Reference Recovered Amount with the effective Credit Memo tax amount
1615 ########################################################################################################*/
1616
1617 fnd_file.put_line(fnd_file.LOG,' 11 before call to jai_cmn_rgm_recording_pkg.update_reference for updating invoice reference' );
1618
1619 jai_cmn_rgm_recording_pkg.update_reference (
1620 p_source => p_source ,
1621 p_reference_id => rec_c_get_refinvrec_for_upd.reference_id ,
1622 p_recovered_amount => ln_amount ,
1623 p_process_flag => lv_process_flag ,
1624 p_process_message => lv_process_message
1625 );
1626 IF lv_process_flag = jai_constants.expected_error OR
1627 lv_process_flag = jai_constants.unexpected_error
1628 THEN
1629 /*
1630 || as Returned status is an error hence:-
1631 || Set out variables p_process_flag and p_process_message accordingly
1632 */
1633 ln_receivable_application_id := rec_c_get_cm_rec_app.receivable_application_id ;
1634 ROLLBACK to roll_to_cm_app;
1635 p_process_flag := lv_process_flag ;
1636 p_process_message := lv_process_message ;
1637 fnd_file.put_line( fnd_file.log, '12 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
1638 ||', lv_process_message'||lv_process_message);
1639
1640 EXIT;
1641 END IF;
1642
1643
1644 fnd_file.put_line(fnd_file.LOG,' 13 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating invoice reference' );
1645
1646 /*
1647 || Get the sum of the effective cm tax amount applied across all the reference invoice lines
1648 */
1649 ln_eff_cm_tottax_amt := nvl(abs(ln_eff_cm_tottax_amt),0) + abs(ln_eff_cm_tax_amount);
1650
1651 fnd_file.put_line( fnd_file.log, '17 value of ln_eff_cm_tottax_amt is - '||ln_eff_cm_tottax_amt );
1652 END LOOP; -- End of Invoice Reference line processing
1653
1654
1655
1656 /*########################################################################################################
1657 || INSERT CM-CM-APP ENTRIES IN REPOSITORY AND UPDATE THE CREDIT MEMO REFERENCE RECORDS
1658 ########################################################################################################*/
1659 IF nvl(ln_receivable_application_id ,-1) <> rec_c_get_cm_rec_app.receivable_application_id THEN
1660 /*
1661 || Get the total effective recoverable credit memo amount from the reference table - CM record
1662 */
1663 OPEN c_get_cmref_totrd_amt ( cp_source_ar => p_source ,
1664 cp_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
1665 );
1666 FETCH c_get_cmref_totrd_amt INTO ln_tot_effcm_rb_amt ;
1667 CLOSE c_get_cmref_totrd_amt;
1668 /*
1669 || ln_eff_cm_tottax_amt - represents the total effective cm amount applied across all the corresponding reference invoice records
1670 || ln_tot_effcm_rb_amt - represents the sum of the total effective recoverable CM amount from the reference table
1671 || (Recoverable amount = total R.B amt - total R.D amount for all the CM records inref table).
1672 || Get the ratio in which the CM needs to be apportioned
1673 */
1674 ln_cm_ref_ratio := abs(ln_eff_cm_tottax_amt)/abs(ln_tot_effcm_rb_amt);
1675
1676 fnd_file.put_line(fnd_file.LOG,'18 Total Effective CM recoverable amount ln_tot_effcm_rb_amt - ' || ln_tot_effcm_rb_amt
1677 ||', actual total cm apportioned amoungst ln_eff_cm_tottax_amt - '|| ln_eff_cm_tottax_amt
1678 ||', cm eff ratio ln_cm_ref_ratio - '||ln_cm_ref_ratio
1679 );
1680
1681
1682 /*
1683 || Update the credit memo reference lines
1684 */
1685 FOR rec_c_get_refcmrec_for_upd IN c_get_refcmrec_for_upd ( cp_source_ar => p_source ,
1686 cp_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
1687 )
1688 LOOP
1689
1690 /*
1691 || Initialize the variable ln_cm_ref_upd
1692 */
1693 ln_cm_ref_upd := null;
1694
1695 fnd_file.put_line(fnd_file.LOG,'19 Processing Credit memo reference tax line, rec_c_get_refcmrec_for_upd.reference_id -> '|| rec_c_get_refcmrec_for_upd.reference_id );
1696
1697 /*
1698 || Calculate the value of ln_cm_ref_upd
1699 */
1700 ln_cm_ref_upd := abs(ln_cm_ref_ratio) * abs(rec_c_get_refcmrec_for_upd.recoverable_amount) * ln_sign_of_credit_memo;
1701
1702 /*########################################################################################################
1703 || Insert the effective Credit Memo tax amount into the repository
1704 ########################################################################################################*/
1705 /*
1706 || Make an entry into the repository with the apportioned Credit Memo Tax amount to be applied against a reference Credit Memo
1707 */
1708
1709 /*csahoo for bug#5879769...start*/
1710
1711 ln_organization_id := NULL;
1712 ln_location_id := NULL;
1713 lv_service_type_code := NULL;
1714 lv_process_flag := NULL;
1715 lv_process_message := NULL;
1716
1717 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_c_get_refcmrec_for_upd.reference_id,
1718 p_organization_id => ln_organization_id,
1719 p_location_id => ln_location_id,
1720 p_service_type_code => lv_service_type_code,
1721 p_process_flag => lv_process_flag,
1722 p_process_message => lv_process_message
1723 );
1724
1725 IF lv_process_flag <> jai_constants.successful THEN
1726 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1727 return;
1728 END IF;
1729
1730 /*csahoo for bug#5879769...end*/
1731
1732 fnd_file.put_line(fnd_file.LOG,' 23 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1733 lv_attribute_context := 'CM-CM-APP';--rchandan for bug#4428980
1734 jai_cmn_rgm_recording_pkg.insert_repository_entry (
1735 p_repository_id => ln_repository_id ,
1736 p_regime_id => p_regime_id ,
1737 p_tax_type => rec_c_get_refcmrec_for_upd.tax_type ,
1738 p_organization_type => p_organization_type ,
1739 p_organization_id => ln_organization_id ,/*5879769*/
1740 p_location_id => ln_location_id ,/*5879769*/
1741 p_service_type_code => lv_service_type_code ,/*5879769*/
1742 p_source => p_source ,
1743 p_source_trx_type => lv_source_trx_type ,
1744 p_source_table_name => lv_source_table ,
1745 p_source_document_id => rec_c_get_cm_rec_app.receivable_application_id ,
1746 p_transaction_date => rec_c_get_cm_rec_app.cm_transaction_date ,
1747 p_account_name => NULL ,
1748 p_charge_account_id => NULL ,
1749 p_balancing_account_id => NULL ,
1750 p_amount => ln_cm_ref_upd ,
1751 p_assessable_value => NULL ,
1752 p_tax_rate => rec_c_get_refcmrec_for_upd.tax_rate ,
1753 p_reference_id => rec_c_get_refcmrec_for_upd.reference_id ,
1754 p_batch_id => p_batch_id ,
1755 p_called_from => lv_called_from ,
1756 p_process_flag => lv_process_flag ,
1757 p_process_message => lv_process_message ,
1758 p_discounted_amount => ln_discounted_amount ,
1759 p_inv_organization_id => ln_organization_id ,/*5879769*/
1760 p_accounting_date => rec_c_get_cm_rec_app.gl_date ,
1761 p_currency_code => rec_c_get_cm_rec_app.cm_currency_code ,
1762 p_curr_conv_date => rec_c_get_cm_rec_app.cm_exchange_date ,
1763 p_curr_conv_type => rec_c_get_cm_rec_app.cm_exchange_rate_type ,
1764 p_curr_conv_rate => rec_c_get_cm_rec_app.cm_exchange_rate ,
1765 p_trx_amount => ln_cm_ref_upd ,
1766 p_attribute_context => lv_attribute_context ,
1767 p_attribute2 => rec_c_get_cm_rec_app.cm_customer_trx_id
1768 , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
1769 );
1770
1771 fnd_file.put_line(fnd_file.LOG,' 24 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1772
1773
1774 IF lv_process_flag = jai_constants.expected_error OR
1775 lv_process_flag = jai_constants.unexpected_error
1776 THEN
1777 /*
1778 || as Returned status is an error hence:-
1779 ||1. Rollback to save point
1780 ||2. Set out variables p_process_flag and p_process_message accordingly
1781 */
1782 ROLLBACK to roll_to_cm_app;
1783 fnd_file.put_line( fnd_file.log, '25 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
1784 ||', lv_process_message'||lv_process_message);
1785 p_process_flag := lv_process_flag ;
1786 p_process_message := lv_process_message ;
1787 EXIT;
1788 END IF;
1789
1790
1791 fnd_file.put_line(fnd_file.LOG,' 20 before call to jai_cmn_rgm_recording_pkg.update_reference for credit memo references '
1792 ||' ,abs(rec_c_get_refcmrec_for_upd.recoverable_amount) -> '||abs(rec_c_get_refcmrec_for_upd.recoverable_amount)
1793 ||' ,recovered amount i.e ln_cm_ref_upd -> '||ln_cm_ref_upd
1794 );
1795
1796
1797 /*
1798 || Update the cm reference line with the amount in ln_cm_ref_upd
1799 */
1800 jai_cmn_rgm_recording_pkg.update_reference (
1801 p_source => p_source ,
1802 p_reference_id => rec_c_get_refcmrec_for_upd.reference_id ,
1803 p_recovered_amount => ln_cm_ref_upd ,
1804 p_process_flag => lv_process_flag ,
1805 p_process_message => lv_process_message
1806 );
1807 fnd_file.put_line(fnd_file.LOG,' 21 Returned from jai_cmn_rgm_recording_pkg.update_reference for credit memo references' );
1808
1809 IF lv_process_flag = jai_constants.expected_error OR
1810 lv_process_flag = jai_constants.unexpected_error
1811 THEN
1812 /*
1813 || As Returned status is an error hence:-
1814 ||1. Rollback to save point
1815 ||2. Set out variables p_process_flag and p_process_message accordingly
1816 */
1817 ROLLBACK to roll_to_cm_app;
1818 fnd_file.put_line( fnd_file.log, '22 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
1819 ||', lv_process_message'||lv_process_message);
1820 p_process_flag := lv_process_flag ;
1821 p_process_message := lv_process_message ;
1822 EXIT;
1823 END IF;
1824
1825 END LOOP; /* End of Update Credit Memo references */
1826 END IF; /* END IF of nvl(ln_receivable_application_id,-1) <> rec_c_get_cm_rec_app.receivable_application_id */
1827
1828 END IF; /* End if of sign of credit memo*/
1829
1830 ln_uncommitted_transactions := ln_uncommitted_transactions + 1;
1831 fnd_file.put_line(fnd_file.LOG,' 26 Finished processing the receivable' );
1832
1833 IF ln_uncommitted_transactions >= 500 THEN
1834 commit;
1835 ln_uncommitted_transactions := 0;
1836 END IF;
1837 END LOOP; -- End of receivables fetch loop
1838
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 fnd_file.put_line(fnd_file.LOG,' 27 In exception section of jai_ar_rgm_processing_pkg.populate_cm_app' );
1842 p_process_flag := jai_constants.unexpected_error;
1843 p_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_cm_app'||SQLERRM ;
1844 END populate_cm_app;
1845
1846
1847 procedure populate_receipt_records ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
1848 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
1849 p_from_date IN DATE ,
1850 p_to_date IN DATE ,
1851 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
1852 p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1853 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
1854 p_process_flag OUT NOCOPY VARCHAR2 ,
1855 p_process_message OUT NOCOPY VARCHAR2
1856 ,p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL )
1857 IS
1858
1859 ln_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE ;
1860 ln_eff_cr_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1861 ln_eff_cr_disc_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_EDISCOUNTED%TYPE ;
1862 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1863 ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1864 lv_process_flag VARCHAR2(2) ;
1865 lv_process_message VARCHAR2(1996) ;
1866 lv_source_trx_type VARCHAR2(50) ;
1867 lv_attribute_context VARCHAR2(50) ;
1868 ln_uncommitted_transactions NUMBER(10) := 0 ;
1869 lv_service_type_code JAI_AR_TRX_LINES.service_type_code%TYPE ;/*5879769*/
1870 ln_location_id NUMBER(15);/*5879769*/
1871 lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AR_RECEIVABLE_APPLICATIONS_ALL';--rchandan for bug#4428980
1872 lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS';--rchandan for bug#4428980
1873
1874 ln_total_tax_applied NUMBER; --added by walton for inclusive tax 29-Nov-07
1875 ln_line_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1876 ln_inclusive_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1877 /*
1878 || Get the cash receipt, Invoice and Total Effective Invoice Recoverable Amount for them.
1879 || Consider only those cash receipts which follow the conditions as given below:-
1880 || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
1881 || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
1882 || 3. Consider cash receipt application against an invoice only
1883 || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount in references table
1884 */
1885
1886
1887 CURSOR c_get_rec_app ( cp_source_ar varchar2) --File.Sql.35 Cbabu ( jai_constants.SOURCE_AR%TYPE )
1888 IS
1889 SELECT
1890 trx.customer_trx_id ,
1891 acrl.cash_receipt_id ,
1892 aral.receivable_application_id ,
1893 aral.gl_date ,
1894 sign(nvl(aral.tax_applied,0)) sign_of_cash_receipt ,
1895 sign(nvl(tax_uediscounted,0) + nvl(tax_ediscounted,0)) sign_of_cr_disc ,
1896 nvl(aral.tax_applied,0) cash_rcpt_tax_amt ,
1897 nvl(tax_uediscounted,0) + nvl(tax_ediscounted,0) cr_tax_disc_amt ,
1898 nvl(aral.amount_applied,0) receipt_amount ,
1899 nvl(aral.line_applied,0) cash_rcpt_line_amt , --added by walton for inclusive tax 29-Nov-07
1900 acrl.receipt_date ,
1901 acrl.currency_code receipt_currency_code ,
1902 acrl.exchange_date receipt_exchange_date ,
1903 acrl.exchange_rate receipt_exchange_rate ,
1904 acrl.exchange_rate_type receipt_exchange_rate_type ,
1905 jtrx.organization_id inv_organization_id
1906 FROM
1907 ar_receivable_applications_all aral ,
1908 ar_cash_receipts_all acrl ,
1909 ra_customer_trx_all trx ,
1910 ra_cust_trx_types_all trx_types ,
1911 JAI_AR_TRXS jtrx
1912 WHERE
1913 aral.cash_receipt_id = acrl.cash_receipt_id AND
1914 aral.applied_customer_trx_id = trx.customer_trx_id AND
1915 trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
1916 upper(aral.application_type) = upper(jai_constants.ar_cash) AND
1917 upper(aral.status) = upper(jai_constants.ar_status_app) AND
1918 jtrx.organization_id =p_organization_id AND --Added by kunkumar for forward porting to R12
1919 /*nvl(aral.tax_applied,0) <> 0 AND*/--Modified by walton for inclusive tax 29-Nov-07
1920 trx.org_id = nvl(p_org_id,trx.org_id) AND
1921 trx.complete_flag = 'Y' AND
1922 trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
1923 upper(trx_types.type) IN (upper(jai_constants.ar_invoice_type_inv),upper(jai_constants.ar_invoice_type_cm)) AND
1924 trx_types.org_id = trx.org_id AND
1925 trx.customer_trx_id = jtrx.customer_trx_id AND
1926 NOT EXISTS ( SELECT /*A receipt application does not exist in repository */
1927 1
1928 FROM
1929 jai_rgm_trx_records rgtr
1930 WHERE
1931 rgtr.source = cp_source_ar AND
1932 rgtr.organization_id = p_organization_id AND/*5879769*/
1933 rgtr.source_table_name = lv_source_table AND
1934 rgtr.source_document_id = aral.receivable_application_id
1935 ) AND
1936 EXISTS (
1937 SELECT /* A invoice exists in the reference table with total recoverable amount - discounted_amount > recovered amount*/
1938 1
1939 FROM
1940 jai_rgm_trx_refs rgtf
1941 WHERE
1942 rgtf.source = jai_constants.SOURCE_AR AND
1943 rgtf.invoice_id = aral.applied_customer_trx_id AND
1944 (
1945 ( /*Scope of recovery is possible for cash receipt application */
1946 nvl(rgtf.recoverable_amount,0) - nvl(rgtf.discounted_amount,0) > nvl(rgtf.recovered_amount,0) AND
1947 nvl(aral.tax_applied,0) > 0
1948 ) OR
1949 ( /* As it is a case of cash receipt reversal hence do not check for recovery. */
1950 nvl(aral.tax_applied,0) < 0
1951 )
1952 )
1953 );
1954
1955 /*
1956 || Get the Total transactional tax amount for the invoice
1957 */
1958 CURSOR cur_get_inv_tottax_amt (cp_inv_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE)
1959 IS
1960 SELECT
1961 nvl(sum(jrttl.tax_amount),0) inv_tot_tax_amount
1962 FROM
1963 JAI_AR_TRX_LINES jrtl ,
1964 JAI_AR_TRX_TAX_LINES jrttl
1965 WHERE
1966 jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
1967 jrtl.customer_trx_id = cp_inv_customer_trx_id ;
1968
1969 --added by walton for inclusive tax 29-Nov-07
1970 ---------------------------------------------------------------
1971 --Get the Total inclusive tax amount for the invoice,
1972 CURSOR cur_get_inv_inclusive_tax_amt
1973 ( pn_inv_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
1974 )
1975 IS
1976 SELECT
1977 nvl(sum(jrttl.tax_amount),0) inv_tot_inclusive_tax_amt
1978 FROM
1979 JAI_AR_TRX_LINES jrtl
1980 , JAI_AR_TRX_TAX_LINES jrttl
1981 , jai_cmn_taxes_all tax
1982 WHERE jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id
1983 AND jrtl.customer_trx_id = pn_inv_customer_trx_id
1984 AND jrttl.tax_id = tax.tax_id
1985 AND NVL(tax.inclusive_tax_flag,'N') = 'Y' ;
1986
1987 --Get the Total AR transaction line amount,
1988 CURSOR cur_get_inv_line_amt
1989 ( pn_inv_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
1990 )
1991 IS
1992 SELECT
1993 nvl(sum(jrtl.line_amount),0) inv_tot_line_amt
1994 FROM
1995 JAI_AR_TRX_LINES jrtl
1996 WHERE jrtl.customer_trx_id = pn_inv_customer_trx_id;
1997 --------------------------------------------------------------------------------
1998 /*
1999 || Get the ref tax lines pertaining to the invoice against which the cash receipt has been applied : -
2000 */
2001 CURSOR c_get_refrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
2002 cp_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
2003 )
2004 IS
2005 SELECT
2006 reference_id ,
2007 tax_type ,
2008 tax_rate ,
2009 nvl(discounted_amount,0) discounted_amount ,
2010 nvl(recoverable_amount,0) - nvl(discounted_amount,0) recoverable_amount ,
2011 nvl(recovered_amount,0) recovered_amount ,
2012 item_line_id /*5879769*/
2013 FROM
2014 jai_rgm_trx_refs
2015 WHERE
2016 source = cp_source_ar AND
2017 invoice_id = cp_customer_trx_id AND
2018 nvl(recoverable_amount,0) - nvl(discounted_amount,0) >= nvl(recovered_amount,0) /*Modified the comparison condition to >= for Bug 6474509*/
2019 FOR UPDATE NOWAIT ;
2020
2021
2022 BEGIN
2023
2024 fnd_file.put_line(fnd_file.LOG,'1 Entering procedure : jai_rgm_process_ar.populate_receipt_records' );
2025
2026 /*
2027 ||Initialize the variables
2028 */
2029 p_process_flag := jai_constants.successful ;
2030 p_process_message := null ;
2031
2032
2033 /*****
2034 || Get all the valid receivables
2035 *****/
2036 FOR rec_c_get_rec_app IN c_get_rec_app ( cp_source_ar => p_source )
2037 LOOP
2038
2039 fnd_file.put_line(fnd_file.LOG,'2 processing cash receipt with receivable_application_id as '||rec_c_get_rec_app.receivable_application_id
2040 ||'against invoice with customer_trx_id '||rec_c_get_rec_app.customer_trx_id );
2041
2042 /*########################################################################################################
2043 || Initializing the variables
2044 ########################################################################################################*/
2045 ln_receivable_application_id := null ;
2046 ln_eff_cr_tax_amount := null ;
2047 ln_eff_cr_disc_amount := null ;
2048 lv_source_trx_type := null ;
2049 ln_inv_tot_tax_amt := null ;
2050 lv_process_flag := jai_constants.successful ;
2051 lv_process_message := null ;
2052
2053 ln_inclusive_total_amt := null ; --added by walton for inclusive tax 29-Nov-07
2054
2055 fnd_file.put_line(fnd_file.LOG,'3 Variables initialized' );
2056
2057 SAVEPOINT roll_to_last_receivable;
2058
2059 OPEN cur_get_inv_tottax_amt (cp_inv_customer_trx_id => rec_c_get_rec_app.customer_trx_id);
2060 FETCH cur_get_inv_tottax_amt INTO ln_inv_tot_tax_amt;
2061 CLOSE cur_get_inv_tottax_amt ;
2062
2063 --added by walton for inclusive tax 29-Nov-07
2064 OPEN cur_get_inv_inclusive_tax_amt( pn_inv_customer_trx_id => rec_c_get_rec_app.customer_trx_id
2065 );
2066 FETCH cur_get_inv_inclusive_tax_amt
2067 INTO ln_inclusive_total_amt;
2068 CLOSE cur_get_inv_inclusive_tax_amt ;
2069
2070 OPEN cur_get_inv_line_amt(pn_inv_customer_trx_id => rec_c_get_rec_app.customer_trx_id
2071 );
2072 FETCH cur_get_inv_line_amt
2073 INTO ln_line_total_amt;
2074 CLOSE cur_get_inv_line_amt ;
2075
2076 --added by walton for inclusive tax 29-Nov-07
2077 ln_total_tax_applied := rec_c_get_rec_app.cash_rcpt_tax_amt + (rec_c_get_rec_app.cash_rcpt_line_amt/ln_line_total_amt) * ln_inclusive_total_amt;
2078
2079 FOR rec_c_get_refrec_for_upd IN c_get_refrec_for_upd ( cp_source_ar => p_source ,
2080 cp_customer_trx_id => rec_c_get_rec_app.customer_trx_id
2081 )
2082 LOOP
2083
2084 fnd_file.put_line(fnd_file.LOG,'4 start of invoice reference tax line , rec_c_get_refrec_for_upd.reference_id -> '|| rec_c_get_refrec_for_upd.reference_id );
2085 lv_attribute_context := NULL;
2086 /*########################################################################################################
2087 || Determine the receipt Application Type (Receipt Application, Receipt Reversal)
2088 ########################################################################################################*/
2089 fnd_file.put_line(fnd_file.LOG,'5 start of determine application source type');
2090
2091 /*
2092 || If the cash receipt tax amount is -ve then it is RECEIPT REVERSAL/UNAPPLICATION
2093 || IF the cash receipt tax receipt amount is +ve then it is RECEIPT APPLICATION
2094 || This info would go into the source_trx_type in the repository table jai_rgm_trx_records .
2095 || This would help distinctly identify a receipt application, receipt reversal and a credit memo
2096 || IF sign = 0 i.e cash receipt amount = 0 then proceed exit the current loop and proceed with the next receivable application.
2097 */
2098 IF rec_c_get_rec_app.sign_of_cash_receipt > 0 or ln_total_tax_applied >0 THEN --modified by walton for inclusive tax 29-Nov-07
2099 /*
2100 || +ve cash receipt hence RECEIPT APPLICATION
2101 */
2102 lv_source_trx_type := jai_constants.trx_type_rct_app ;
2103 lv_attribute_context := 'CR-INV-APP' ;
2104
2105 ELSIF rec_c_get_rec_app.sign_of_cash_receipt < 0 or ln_total_tax_applied <0 THEN --modified by walton for inclusive tax 29-Nov-07
2106 /*
2107 || -ve cash receipt hence RECEIPT REVERSAL/UNAPPLICATION
2108 */
2109 lv_source_trx_type := jai_constants.trx_type_rct_rvs ;
2110 lv_attribute_context := 'CR-INV-REV' ;
2111
2112 ELSE
2113 /*
2114 || cash receipt amount is zero hence exit current loop and process next receipt application
2115 */
2116 fnd_file.put_line(fnd_file.LOG,'6 cash receipt has sign = 0 i.e tax applied amount = 0 hence exit ');
2117 exit;
2118 END IF;
2119
2120 fnd_file.put_line(fnd_file.LOG,'7 cash receipt is lv_source_trx_type->'||lv_source_trx_type);
2121
2122 /*########################################################################################################
2123 || Calculation of the Service Tax Component of Cash Receipt Tax Amount which needs to be considered
2124 ########################################################################################################*/
2125
2126 fnd_file.put_line(fnd_file.LOG,'8 Start of Cash Receipt Service Tax Component calculation, ln_inv_tot_tax_amt'||ln_inv_tot_tax_amt);
2127
2128 ln_eff_cr_tax_amount := (ln_total_tax_applied/ln_inv_tot_tax_amt ) * rec_c_get_refrec_for_upd.recoverable_amount ; --modified by walton for inclusive tax 29-Nov-07
2129
2130
2131 IF rec_c_get_refrec_for_upd.recovered_amount + ln_eff_cr_tax_amount < 0 THEN
2132 /*
2133 || -ve cash receipt
2134 || Check that if recovered amount + cash receipt amount < 0 then the cash receipt amount should be equal to the recovered amount
2135 || so that the net recovered amount equals zero.
2136 */
2137 ln_eff_cr_tax_amount := rec_c_get_refrec_for_upd.recovered_amount * rec_c_get_rec_app.sign_of_cash_receipt;
2138
2139 fnd_file.put_line(fnd_file.LOG,'10 rec_c_get_refrec_for_upd.recovered_amount + ln_eff_cr_tax_amount < 0 , ln_eff_cr_tax_amount '||ln_eff_cr_tax_amount);
2140
2141
2142 ELSIF rec_c_get_refrec_for_upd.recovered_amount + ln_eff_cr_tax_amount > rec_c_get_refrec_for_upd.recoverable_amount THEN
2143 /*
2144 || +ve cash receipt
2145 || Check that if recoverd amount + cash receipt amount > recoverable amount.
2146 || IF yes then set cash receipt amount = recoverable amount - recovered amount
2147 || so that the recovered amount never exceeds the recoverable amount
2148 */
2149 ln_eff_cr_tax_amount := rec_c_get_refrec_for_upd.recoverable_amount - rec_c_get_refrec_for_upd.recovered_amount ;
2150 fnd_file.put_line(fnd_file.LOG,'11 rec_c_get_refrec_for_upd.recovered_amount + ln_eff_cr_tax_amount > rec_c_get_refrec_for_upd.recoverable_amount hence ln_eff_cr_tax_amount '||ln_eff_cr_tax_amount);
2151 END IF;
2152
2153
2154 /*########################################################################################################
2155 || Calculation of the Service Tax Discount Component of Cash Receipt Discounted Amount which needs to be considered
2156 ########################################################################################################*/
2157
2158 ln_eff_cr_disc_amount := (rec_c_get_rec_app.cr_tax_disc_amt/ln_inv_tot_tax_amt) * rec_c_get_refrec_for_upd.recoverable_amount ;
2159
2160 fnd_file.put_line(fnd_file.LOG,'9 rec_c_get_rec_app.cr_tax_disc_amt effective ->'||rec_c_get_rec_app.cr_tax_disc_amt
2161 ||', ln_inv_tot_tax_amt ->'||ln_inv_tot_tax_amt
2162 ||', rec_c_get_refrec_for_upd.recoverable_amount ->'||rec_c_get_refrec_for_upd.recoverable_amount
2163 ||', cash receipt tax amount is ln_eff_cr_tax_amount ->'||ln_eff_cr_tax_amount
2164 ||', rec_c_get_refrec_for_upd.recovered_amount ->'||rec_c_get_refrec_for_upd.recovered_amount);
2165
2166 IF nvl(rec_c_get_refrec_for_upd.recoverable_amount,0) - nvl(ln_eff_cr_disc_amount,0) <
2167 nvl(rec_c_get_refrec_for_upd.recovered_amount,0) + nvl(ln_eff_cr_tax_amount,0)
2168 THEN
2169 /*
2170 || +ve discounted amount
2171 || The effective recovered amount portion should never be greater than the effective recoverable_amount
2172 || Keeping this condition in mind , the discounted amount should get adjusted
2173 */
2174 ln_eff_cr_disc_amount := nvl(rec_c_get_refrec_for_upd.recoverable_amount,0) - (nvl(rec_c_get_refrec_for_upd.recovered_amount,0) + nvl(ln_eff_cr_tax_amount,0) );
2175
2176 fnd_file.put_line(fnd_file.LOG,'10 effective recovered amount > effective recoverable amount hence, ln_eff_cr_disc_amount '||ln_eff_cr_disc_amount);
2177
2178 ELSIF rec_c_get_refrec_for_upd.discounted_amount + ln_eff_cr_disc_amount < 0 THEN
2179 /*
2180 || -ve discounted amount
2181 || The total discounted amount cannot be lesser than 0
2182 */
2183 ln_eff_cr_disc_amount := rec_c_get_refrec_for_upd.discounted_amount * rec_c_get_rec_app.sign_of_cr_disc;
2184
2185 fnd_file.put_line(fnd_file.LOG,'10 rec_c_get_refrec_for_upd.discounted_amount + ln_eff_cr_disc_amount < 0 , ln_eff_cr_tax_amount '||ln_eff_cr_tax_amount);
2186
2187 END IF;
2188
2189 /*########################################################################################################
2190 || Insert the effective cash receipt tax amount into the repository
2191 ########################################################################################################*/
2192 /*
2193 || Make an entry into the repository with the apportioned Cash Receipt Tax amount
2194 */
2195
2196 /*csahoo for bug#5879769...start*/
2197
2198 ln_organization_id := NULL;
2199 ln_location_id := NULL;
2200 lv_service_type_code := NULL;
2201 lv_process_flag := NULL;
2202 lv_process_message := NULL;
2203
2204 jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => rec_c_get_refrec_for_upd.reference_id,
2205 p_organization_id => ln_organization_id,
2206 p_location_id => ln_location_id,
2207 p_service_type_code => lv_service_type_code,
2208 p_process_flag => lv_process_flag,
2209 p_process_message => lv_process_message
2210 );
2211
2212 IF lv_process_flag <> jai_constants.successful THEN
2213 FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
2214 return;
2215 END IF;
2216
2217 /*csahoo for bug#5879769...end*/
2218
2219
2220 fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2221
2222 jai_cmn_rgm_recording_pkg.insert_repository_entry (
2223 p_repository_id => ln_repository_id ,
2224 p_regime_id => p_regime_id ,
2225 p_tax_type => rec_c_get_refrec_for_upd.tax_type ,
2226 p_organization_type => p_organization_type ,
2227 p_organization_id => ln_organization_id ,/*5879769*/
2228 p_location_id => ln_location_id ,/*5879769*/
2229 p_service_type_code => lv_service_type_code ,/*5879769*/
2230 p_source => p_source ,
2231 p_source_trx_type => lv_source_trx_type ,
2232 p_source_table_name => lv_source_table ,
2233 p_source_document_id => rec_c_get_rec_app.receivable_application_id ,
2234 p_transaction_date => rec_c_get_rec_app.receipt_date ,
2235 p_account_name => NULL ,
2236 p_charge_account_id => NULL ,
2237 p_balancing_account_id => NULL ,
2238 p_amount => ln_eff_cr_tax_amount ,
2239 p_assessable_value => NULL ,
2240 p_tax_rate => rec_c_get_refrec_for_upd.tax_rate ,
2241 p_reference_id => rec_c_get_refrec_for_upd.reference_id ,
2242 p_batch_id => p_batch_id ,
2243 p_called_from => lv_called_from ,
2244 p_process_flag => lv_process_flag ,
2245 p_process_message => lv_process_message ,
2246 p_discounted_amount => ln_eff_cr_disc_amount ,
2247 p_inv_organization_id => ln_organization_id ,/*5879769*/
2248 p_accounting_date => rec_c_get_rec_app.gl_date ,
2249 p_currency_code => rec_c_get_rec_app.receipt_currency_code ,
2250 p_curr_conv_date => rec_c_get_rec_app.receipt_exchange_date ,
2251 p_curr_conv_type => rec_c_get_rec_app.receipt_exchange_rate_type ,
2252 p_curr_conv_rate => rec_c_get_rec_app.receipt_exchange_rate ,
2253 p_trx_amount => ln_eff_cr_tax_amount ,
2254 p_attribute_context => lv_attribute_context ,
2255 p_attribute1 => rec_c_get_rec_app.customer_trx_id ,
2256 p_attribute2 => rec_c_get_rec_app.cash_receipt_id
2257 , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
2258 );
2259
2260 fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2261
2262
2263 IF lv_process_flag = jai_constants.expected_error OR
2264 lv_process_flag = jai_constants.unexpected_error
2265 THEN
2266 /*
2267 || as Returned status is an error hence:-
2268 ||1. Rollback to save point
2269 ||2. Set out variables p_process_flag and p_process_message accordingly
2270 */
2271 ln_receivable_application_id := rec_c_get_rec_app.receivable_application_id;
2272 ROLLBACK to roll_to_last_receivable;
2273 p_process_flag := lv_process_flag ;
2274 p_process_message := lv_process_message ;
2275 fnd_file.put_line( fnd_file.log, '16 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
2276 ||', lv_process_message'||lv_process_message);
2277 EXIT;
2278 END IF;
2279
2280
2281 /*########################################################################################################
2282 || update the effective cash receipt tax amount into the reference table
2283 ########################################################################################################*/
2284
2285 fnd_file.put_line(fnd_file.LOG,' 12 before call to jai_cmn_rgm_recording_pkg.update_reference ' );
2286
2287 savepoint before_ref_upd;
2288 jai_cmn_rgm_recording_pkg.update_reference (
2289 p_source => p_source ,
2290 p_reference_id => rec_c_get_refrec_for_upd.reference_id ,
2291 p_recovered_amount => ln_eff_cr_tax_amount ,
2292 p_discounted_amount => ln_eff_cr_disc_amount ,
2293 p_process_flag => lv_process_flag ,
2294 p_process_message => lv_process_message
2295 );
2296
2297
2298 IF lv_process_flag = jai_constants.expected_error OR
2299 lv_process_flag = jai_constants.unexpected_error
2300 THEN
2301 /*
2302 || as Returned status is an error hence:-
2303 ||Set out variables p_process_flag and p_process_message accordingly
2304 */
2305 ln_receivable_application_id := rec_c_get_rec_app.receivable_application_id;
2306 ROLLBACK to roll_to_last_receivable;
2307 p_process_flag := lv_process_flag ;
2308 p_process_message := lv_process_message ;
2309 fnd_file.put_line( fnd_file.log, '12.1 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
2310 ||', lv_process_message'||lv_process_message);
2311
2312 EXIT;
2313 END IF;
2314
2315 fnd_file.put_line(fnd_file.LOG,' 13 Returned from jai_cmn_rgm_recording_pkg.update_reference ' );
2316
2317 END LOOP;
2318
2319 ln_uncommitted_transactions := ln_uncommitted_transactions + 1;
2320 fnd_file.put_line(fnd_file.LOG,' 17 Finished processing the cash receipt ' );
2321
2322 IF ln_uncommitted_transactions >= 50 THEN
2323 commit;
2324 ln_uncommitted_transactions := 0;
2325 END IF;
2326 END LOOP;
2327
2328 EXCEPTION
2329 WHEN OTHERS THEN
2330 p_process_flag := jai_constants.unexpected_error;
2331 p_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_receipt_records'||SQLERRM ;
2332
2333 END populate_receipt_records;
2334
2335
2336
2337
2338
2339 procedure process_records ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
2340 p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
2341 p_from_date IN DATE ,
2342 p_to_date IN DATE ,
2343 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
2344 p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
2345 p_process_flag OUT NOCOPY VARCHAR2 ,
2346 p_process_message OUT NOCOPY VARCHAR2 ,
2347 p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL /*5879769*/
2348 )
2349 IS
2350
2351 lv_source_ar varchar2(2); --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ;
2352 lv_process_flag VARCHAR2(2) ;
2353 lv_process_message VARCHAR2(1996) ;
2354 lv_organization_id jai_rgm_parties.organization_id%type;
2355 BEGIN
2356
2357 fnd_file.put_line(fnd_file.LOG,'############################## 1 START OF PROCEDURE TO jai_ar_rgm_processing_pkg.PROCESS_RECORDS ############################## ');
2358
2359 /*########################################################################################################
2360 || VARIABLES INITIALIZATION
2361 ########################################################################################################*/
2362
2363 lv_source_ar := jai_constants.source_ar ;
2364
2365 lv_process_flag := jai_constants.successful ;
2366 lv_process_message := null ;
2367
2368 p_process_flag := lv_process_flag ;
2369 p_process_message := lv_process_message ;
2370
2371 fnd_file.put_line(fnd_file.LOG,'2 i/p is p_regime_id ->'||p_regime_id
2372 ||', p_regime_id ->'||p_regime_id
2373 ||', p_organization_type ->'||p_organization_type
2374 ||', p_from_date ->'||p_from_date
2375 ||', p_to_date ->'||p_to_date
2376 ||', p_org_id ->'||p_org_id
2377 ||', p_batch_id ->'||p_batch_id
2378 );
2379
2380
2381 /*########################################################################################################
2382 || PROCESS AR INVOICE AND CREDIT MEMO'S FOR REFERENCE ENTRIES
2383 ########################################################################################################*/
2384
2385 fnd_file.put_line(fnd_file.LOG,'############################## 3 BEFORE CALL TO jai_ar_rgm_processing_pkg.POPULATE_INV_CM_REFERENCES ##############################');
2386
2387 populate_inv_cm_references ( p_regime_id => p_regime_id ,
2388 p_organization_type => p_organization_type ,
2389 p_organization_id => p_organization_id ,/*5879769*/
2390 p_from_date => p_from_date ,
2391 p_to_date => p_to_date ,
2392 p_org_id => p_org_id ,
2393 p_batch_id => p_batch_id ,
2394 p_source => lv_source_ar ,
2395 p_process_flag => lv_process_flag ,
2396 p_process_message => lv_process_message
2397 );
2398
2399
2400
2401 IF lv_process_flag = jai_constants.expected_error OR
2402 lv_process_flag = jai_constants.unexpected_error
2403 THEN
2404 /*
2405 || As Returned status is an error hence:-
2406 || Set out variables p_process_flag and p_process_message accordingly
2407 */
2408
2409 fnd_file.put_line( fnd_file.log, '4 ERROR IN CALL TO jai_ar_rgm_processing_pkg.POPULATE_INV_CM_REFERENCES - lv_process_flag '||lv_process_flag
2410 ||', lv_process_message'||lv_process_message);
2411 p_process_flag := lv_process_flag ;
2412 p_process_message := lv_process_message ;
2413 END IF;
2414
2415 fnd_file.put_line(fnd_file.LOG,'############################## 5 RETURNED FROM jai_ar_rgm_processing_pkg.POPULATE_INV_CM_REFERENCES'||'lv_process_flag - '||lv_process_flag||
2416 ' lv_process_message- '||lv_process_message||'############################## ');
2417
2418
2419 /*########################################################################################################
2420 || DELETE NON INCOMPLETE/NON-EXISTING CREDIT MEMO'S
2421 ########################################################################################################*/
2422
2423 /*
2424 || Reverse all those AR Credit Memo's which have been incompleted/incompleted
2425 || and deleted from base ar tables
2426 */
2427 fnd_file.put_line(fnd_file.LOG,'############################## 6 BEFORE CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM ############################## ');
2428
2429 delete_non_existant_cm ( p_regime_id => p_regime_id ,
2430 p_organization_type => p_organization_type ,
2431 p_organization_id => p_organization_id ,/*5879769*/
2432 p_from_date => p_from_date ,
2433 p_to_date => p_to_date ,
2434 p_org_id => p_org_id ,
2435 p_source => lv_source_ar ,
2436 p_batch_id => p_batch_id ,
2437 p_process_flag => lv_process_flag ,
2438 p_process_message => lv_process_message
2439 );
2440
2441 IF lv_process_flag = jai_constants.expected_error OR
2442 lv_process_flag = jai_constants.unexpected_error
2443 THEN
2444 /*
2445 || As Returned status is an error hence:-
2446 || Set out variables p_process_flag and p_process_message accordingly
2447 */
2448
2449 fnd_file.put_line( fnd_file.log, '7 ERROR IN CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM - lv_process_flag '||lv_process_flag
2450 ||', lv_process_message'||lv_process_message);
2451 p_process_flag := lv_process_flag ;
2452 p_process_message := lv_process_message ;
2453 END IF;
2454
2455
2456 fnd_file.put_line(fnd_file.LOG,'############################## 8 RETURNED FROM jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM'||'lv_process_flag - '||lv_process_flag||
2457 ' lv_process_message- '||lv_process_message||'############################## ');
2458
2459
2460 /*########################################################################################################
2461 || PROCESS AR CREDIT MEMO APPLICATIONS AGAINST INVOICES
2462 ########################################################################################################*/
2463
2464
2465 fnd_file.put_line(fnd_file.LOG,'############################## 9 BEFORE CALL TO jai_ar_rgm_processing_pkg.POPULATE_CM_APP ############################## ');
2466
2467 populate_cm_app ( p_regime_id => p_regime_id ,
2468 p_organization_type => p_organization_type ,
2469 p_organization_id => p_organization_id ,/*5879769*/
2470 p_from_date => p_from_date ,
2471 p_to_date => p_to_date ,
2472 p_org_id => p_org_id ,
2473 p_source => lv_source_ar ,
2474 p_batch_id => p_batch_id ,
2475 p_process_flag => lv_process_flag ,
2476 p_process_message => lv_process_message
2477 );
2478
2479
2480 IF lv_process_flag = jai_constants.expected_error OR
2481 lv_process_flag = jai_constants.unexpected_error
2482 THEN
2483 /*
2484 || As Returned status is an error hence:-
2485 || Set out variables p_process_flag and p_process_message accordingly
2486 */
2487 fnd_file.put_line( fnd_file.log, '10 error in call to jai_ar_rgm_processing_pkg.populate_cm_app - lv_process_flag '||lv_process_flag
2488 ||', lv_process_message'||lv_process_message);
2489 p_process_flag := lv_process_flag ;
2490 p_process_message := lv_process_message ;
2491 END IF;
2492
2493
2494 fnd_file.put_line(fnd_file.LOG,'############################## 11 RETURNED FROM CALL TO jai_ar_rgm_processing_pkg.POPULATE_CM_APP '||'lv_process_flag - '||lv_process_flag||
2495 ' lv_process_message- '||lv_process_message||'##############################');
2496
2497
2498 /*########################################################################################################
2499 || PROCESS AR CASH RECEIPT APPLICATIONS AGAINST INVOICES
2500 ########################################################################################################*/
2501
2502
2503 fnd_file.put_line(fnd_file.LOG,'############################## 12 BEFORE CALL TO jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS ##############################');
2504
2505 populate_receipt_records ( p_regime_id => p_regime_id ,
2506 p_organization_type => p_organization_type ,
2507 p_organization_id => p_organization_id ,/*5879769*/
2508 p_from_date => p_from_date ,
2509 p_to_date => p_to_date ,
2510 p_org_id => p_org_id ,
2511 p_batch_id => p_batch_id ,
2512 p_source => lv_source_ar ,
2513 p_process_flag => lv_process_flag ,
2514 p_process_message => lv_process_message
2515 );
2516
2517 IF lv_process_flag = jai_constants.expected_error OR
2518 lv_process_flag = jai_constants.unexpected_error
2519 THEN
2520 /*
2521 || As Returned status is an error hence:-
2522 || Set out variables p_process_flag and p_process_message accordingly
2523 */
2524 fnd_file.put_line( fnd_file.log, '13 ERROR IN CALL TO jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS - lv_process_flag '||lv_process_flag
2525 ||', lv_process_message'||lv_process_message);
2526 p_process_flag := lv_process_flag ;
2527 p_process_message := lv_process_message ;
2528 END IF;
2529
2530 fnd_file.put_line(fnd_file.LOG,'##############################14 RETURNED FROM jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS'||'lv_process_flag - '||lv_process_flag||
2531 ' lv_process_message- '||lv_process_message||'##############################');
2532
2533
2534 fnd_file.put_line(fnd_file.LOG,'############################## 15 END OF PROCEDURE TO jai_ar_rgm_processing_pkg.PROCESS_RECORDS - ##############################');
2535
2536 EXCEPTION
2537 WHEN OTHERS THEN
2538 lv_process_flag := jai_constants.unexpected_error;
2539 lv_process_message := ' 16 Unexpected error occured while processing jai_ar_rgm_processing_pkg.process_records'||SQLERRM ;
2540
2541 END process_records;
2542
2543 END jai_ar_rgm_processing_pkg ;