[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_TCS_REP_PKG
Source
1 package body jai_ar_tcs_rep_pkg AS
2 /* $Header: jai_tcs_repo_pkg.plb 120.11.12010000.2 2008/08/07 13:51:04 jmeena ship $ */
3
4
5 /** Package level variables used in debug package*/
6 lv_object_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE DEFAULT 'TCS.JAI_AR_TCS_REP_PKG';
7 lv_member_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
8 lv_context jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
9 /****
10 || Get the primary key
11 || for the table jai_rgm_refs_all
12 *****/
13 CURSOR cur_get_trx_ref_id
14 IS
15 SELECT
16 jai_rgm_refs_all_s1.nextval
17 FROM
18 dual;
19
20 /****
21 || Get the primary key
22 || for the table jai_rgm_taxes
23 *****/
24 CURSOR cur_get_tax_det_id
25 IS
26 SELECT
27 jai_rgm_taxes_s.nextval
28 FROM
29 dual;
30
31 /*
32 ||Get the parent reference_id of source document , This gives the last line of the source document. (needs to be discussed )
33 */
34 CURSOR cur_get_parent_transaction ( cp_source_document_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ,
35 cp_source_document_type JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE
36 )
37 IS
38 SELECT
39 max(transaction_id) parent_transaction_id
40 FROM
41 jai_rgm_refs_all
42 WHERE
43 source_document_id = cp_source_document_id
44 AND source_document_type = cp_source_document_type;
45
46 /*
47 || Generate the transaction_id from the sequence
48 */
49 CURSOR cur_get_transaction_id
50 IS
51 SELECT
52 jai_rgm_refs_all_s2.nextval
53 FROM
54 dual;
55
56 ln_event VARCHAR2(100); /*package private variable*/
57 ln_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE ;
58
59 PROCEDURE set_debug_context
60 IS
61 BEGIN
62 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
63 END set_debug_context;
64
65 PROCEDURE wsh_interim_accounting ( p_delivery_id IN JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE ,
66 p_delivery_detail_id IN JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE ,
67 p_order_header_id IN JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE ,
68 p_organization_id IN JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE ,
69 p_location_id IN JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE ,
70 p_currency_code IN VARCHAR2 ,
71 p_process_flag OUT NOCOPY VARCHAR2 ,
72 p_process_message OUT NOCOPY VARCHAR2
73 )
74
75 IS
76 ln_reg_id NUMBER;
77 CURSOR cur_get_picking_taxes
78 IS
79 SELECT
80 jsptl.* ,
81 jrttv.regime_id,
82 jtc.tax_type
83 FROM
84 JAI_OM_WSH_LINES_ALL jspl ,
85 JAI_OM_WSH_LINE_TAXES jsptl,
86 JAI_CMN_TAXES_ALL jtc ,
87 jai_regime_tax_types_v jrttv
88 WHERE
89 jspl.delivery_detail_id = jsptl.delivery_detail_id
90 AND jspl.delivery_id = p_delivery_id
91 AND jspl.delivery_detail_id = p_delivery_detail_id
92 AND jsptl.tax_id = jtc.tax_id
93 AND jtc.tax_type = jrttv.tax_type
94 AND jrttv.regime_code = jai_constants.tcs_regime;
95
96 CURSOR cur_get_order_num( cp_hdr_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE)
97 IS
98 SELECT
99 order_number
100 FROM
101 oe_order_headers_all
102 WHERE
103 header_id = cp_hdr_id;
104
105 v_ref_10 GL_INTERFACE.REFERENCE10%TYPE ;
106 v_std_text VARCHAR2(50) ; -- bug # 3158976
107 v_ref_23 GL_INTERFACE.REFERENCE23%TYPE ; -- holds the object name -- 'ja_in_wsh_dlry_rg'
108 v_ref_24 GL_INTERFACE.REFERENCE24%TYPE ; -- holds the table name -- ' wsh_new_deliveries'
109 v_ref_25 GL_INTERFACE.REFERENCE25%TYPE ; -- holds the column name -- 'delivery_id'
110 v_ref_26 GL_INTERFACE.REFERENCE26%TYPE ; -- holds the column value -- eg -- 13645
111
112 ln_order_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE ;
113 lv_source_name VARCHAR2(100) ;
114 lv_category_name VARCHAR2(100) ;
115
116 v_int_liab_acc_ccid NUMBER;
117 v_liab_acc_ccid NUMBER;
118 BEGIN
119 /*########################################################################################################
120 || VARIABLES INITIALIZATION - PART -1
121 ########################################################################################################*/
122 lv_member_name := 'WSH_INTERIM_ACCOUNTING';
123 v_std_text := 'India Localization Entry for sales order #' ;
124 v_ref_23 := 'jai_ar_tcs_rep_pkg.wsh_interim_accounting';
125 v_ref_24 := 'wsh_new_deliveries';
126 v_ref_25 := 'delivery_id';
127 lv_source_name := jai_constants.tcs_source ; -- modified by csahoo for bug#6155839
128 lv_category_name := 'Receivables India' ; -- modified by csahoo for bug#6155839
129
130
131
132 set_debug_context;
133
134
135 /*commented by csahoo for bug# 6401388
136 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
137 pn_reg_id => ln_reg_id
138 );*/
139 p_process_flag := jai_constants.successful ;
140 p_process_message := null ;
141
142 OPEN cur_get_order_num( cp_hdr_id => p_order_header_id );
143 FETCH cur_get_order_num INTO ln_order_number;
144 CLOSE cur_get_order_num ;
145
146 v_ref_26 := p_delivery_id ;
147
148
149 --|| Added the delivery_id to the v_Ref_10 variable so that delivery id can also be seen
150 --|| in the journal screen when the gl import is done.
151
152 v_ref_10 := v_std_text || ln_order_number || ' and Delivery id :' || p_delivery_id || ' and Delivery Detail id :' || p_delivery_detail_id ;
153
154
155 FOR rec_cur_get_picking_taxes IN cur_get_picking_taxes
156 LOOP
157
158
159 /*********************************************************************************************************
160 || Get the code combination id from the Organization/Regime Registration setup
161 || by calling the function jai_cmn_rgm_recording_pkg.get_account
162 *********************************************************************************************************/
163
164 /*commented by csahoo for bug# 6401388
165 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
166 pv_log_msg => 'Before call to jai_cmn_rgm_recording_pkg.get_account for int liab'
167 );*/
168 v_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
169 p_regime_id => rec_cur_get_picking_taxes.regime_id ,
170 p_organization_type => jai_constants.orgn_type_io ,
171 p_organization_id => p_organization_id ,
172 p_location_id => p_location_id ,
173 p_tax_type => rec_cur_get_picking_taxes.tax_type ,
174 p_account_name => jai_constants.liability
175 );
176
177 /*commented by csahoo for bug# 6401388
178 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
179 pv_log_msg => 'Before call to jai_cmn_rgm_recording_pkg.get_account for liab'
180 );*/
181 v_int_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
182 p_regime_id => rec_cur_get_picking_taxes.regime_id ,
183 p_organization_type => jai_constants.orgn_type_io ,
184 p_organization_id => p_organization_id ,
185 p_location_id => p_location_id ,
186 p_tax_type => rec_cur_get_picking_taxes.tax_type ,
187 p_account_name => jai_constants.liability_interim
188 );
189
190
191
192 IF v_int_liab_acc_ccid IS NULL OR
193 v_liab_acc_ccid IS NULL
194 THEN
195 /**********************************************************************************************************
196 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
197 || This is an error condition and the current processing has to be stopped
198 **********************************************************************************************************/
199 /*commented by csahoo for bug# 6401388
200 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
201 pv_log_msg => 'Error in call to jai_cmn_rgm_recording_pkg.get_account'
202 );*/
203 p_process_flag := jai_constants.expected_error;
204 p_process_message := 'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup';
205 return;
206 END IF;
207
208
209 /*
210 ||Credit the liability account
211 */
212 /*commented by csahoo for bug# 6401388
213 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
214 PV_LOG_MSG => 'CREDIT MEMO ENTRIES GETTING PASSED TO jai_cmn_gl_pkg.create_gl_entry ARE :- ' ||fnd_global.local_chr(10)
215 ||', p_organization_id -> '|| p_organization_id ||fnd_global.local_chr(10)
216 ||', p_currency_code -> '|| p_currency_code ||fnd_global.local_chr(10)
217 ||', p_credit_amount -> '|| round(rec_cur_get_picking_taxes.func_tax_amount) ||fnd_global.local_chr(10)
218 ||', p_debit_amount -> '|| 0 ||fnd_global.local_chr(10)
219 ||', p_cc_id -> '|| v_liab_acc_ccid ||fnd_global.local_chr(10)
220 ||', p_je_source_name -> '|| lv_source_name ||fnd_global.local_chr(10)
221 ||', p_je_category_name -> '|| lv_category_name ||fnd_global.local_chr(10)
222 ||', p_created_by -> '|| rec_cur_get_picking_taxes.created_by ||fnd_global.local_chr(10)
223 ||', p_accounting_date -> '|| trunc(sysdate) ||fnd_global.local_chr(10)
224 ||', p_currency_conversion_date -> '|| NULL ||fnd_global.local_chr(10)
225 ||', p_currency_conversion_type -> '|| NULL ||fnd_global.local_chr(10)
226 ||', p_currency_conversion_rate -> '|| NULL ||fnd_global.local_chr(10)
227 ||', p_reference_10 -> '|| v_ref_10 ||fnd_global.local_chr(10)
228 ||', p_reference_23 -> '|| v_ref_23 ||fnd_global.local_chr(10)
229 ||', p_reference_24 -> '|| v_ref_24 ||fnd_global.local_chr(10)
230 ||', p_reference_25 -> '|| v_ref_25 ||fnd_global.local_chr(10)
231 ||', p_reference_26 -> '|| v_ref_26
232 );*/
233
234
235 jai_cmn_gl_pkg.create_gl_entry
236 (p_organization_id => p_organization_id ,
237 p_currency_code => p_currency_code ,
238 p_credit_amount => round(rec_cur_get_picking_taxes.func_tax_amount) ,
239 p_debit_amount => 0 ,
240 p_cc_id => v_liab_acc_ccid ,
241 p_je_source_name => lv_source_name ,
242 p_je_category_name => lv_category_name ,
243 p_created_by => rec_cur_get_picking_taxes.created_by ,
244 p_accounting_date => trunc(sysdate) ,
245 p_currency_conversion_date => NULL ,
246 p_currency_conversion_type => NULL ,
247 p_currency_conversion_rate => NULL ,
248 p_reference_10 => v_ref_10 ,
249 p_reference_23 => v_ref_23 ,
250 p_reference_24 => v_ref_24 ,
251 p_reference_25 => v_ref_25 ,
252 p_reference_26 => v_ref_26
253 );
254
255 /*
256 ||Debit the Interim liability account
257 */
258 /*
259 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
260 pv_log_msg => 'DEBIT MEMO ENTRIES GETTING PASSED TO jai_cmn_gl_pkg.create_gl_entry ARE :- '||fnd_global.local_chr(10)
261 ||', p_organization_id -> '|| p_organization_id ||fnd_global.local_chr(10)
262 ||', p_currency_code -> '|| p_currency_code ||fnd_global.local_chr(10)
263 ||', p_credit_amount -> '|| 0 ||fnd_global.local_chr(10)
264 ||', p_debit_amount -> '|| round(rec_cur_get_picking_taxes.func_tax_amount) ||fnd_global.local_chr(10)
265 ||', p_cc_id -> '|| v_int_liab_acc_ccid ||fnd_global.local_chr(10)
266 ||', p_je_source_name -> '|| lv_source_name ||fnd_global.local_chr(10)
267 ||', p_je_category_name -> '|| lv_category_name ||fnd_global.local_chr(10)
268 ||', p_created_by -> '|| rec_cur_get_picking_taxes.created_by ||fnd_global.local_chr(10)
269 ||', p_accounting_date -> '|| trunc(sysdate) ||fnd_global.local_chr(10)
270 ||', p_currency_conversion_date -> '|| NULL ||fnd_global.local_chr(10)
271 ||', p_currency_conversion_type -> '|| NULL ||fnd_global.local_chr(10)
272 ||', p_currency_conversion_rate -> '|| NULL ||fnd_global.local_chr(10)
273 ||', p_reference_10 -> '|| v_ref_10 ||fnd_global.local_chr(10)
274 ||', p_reference_23 -> '|| v_ref_23 ||fnd_global.local_chr(10)
275 ||', p_reference_24 -> '|| v_ref_24 ||fnd_global.local_chr(10)
279 jai_cmn_gl_pkg.create_gl_entry
276 ||', p_reference_25 -> '|| v_ref_25 ||fnd_global.local_chr(10)
277 ||', p_reference_26 -> '|| v_ref_26
278 );*/
280 (p_organization_id => p_organization_id ,
281 p_currency_code => p_currency_code ,
282 p_credit_amount => 0 ,
283 p_debit_amount => round(rec_cur_get_picking_taxes.func_tax_amount) ,
284 p_cc_id => v_int_liab_acc_ccid ,
285 p_je_source_name => lv_source_name ,
286 p_je_category_name => lv_category_name ,
287 p_created_by => rec_cur_get_picking_taxes.created_by ,
288 p_accounting_date => trunc(sysdate) ,
289 p_currency_conversion_date => NULL ,
290 p_currency_conversion_type => NULL ,
291 p_currency_conversion_rate => NULL ,
292 p_reference_10 => v_ref_10 ,
293 p_reference_23 => v_ref_23 ,
294 p_reference_24 => v_ref_24 ,
295 p_reference_25 => v_ref_25 ,
296 p_reference_26 => v_ref_26
297 );
298
299 END LOOP;
300
301 /*commented by csahoo for bug# 6401388
302 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
303 pv_log_msg => '**************** WSH_INTERIM_ACCOUNTING SUCCESSFULLY COMPLETED ****************'
304 );
305 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 p_process_flag := jai_constants.unexpected_error;
310 p_process_message := 'Unexpected error in the jai_ar_tcs_rep_pkg.wsh_interim_accounting '||substr(sqlerrm,1,300);
311
312 END wsh_interim_accounting;
313
314 PROCEDURE ar_accounting ( p_ract IN RA_CUSTOMER_TRX_ALL%ROWTYPE DEFAULT NULL ,
315 p_ractl IN RA_CUSTOMER_TRX_LINES_ALL%ROWTYPE DEFAULT NULL ,
316 p_process_flag OUT NOCOPY VARCHAR2 ,
317 p_process_message OUT NOCOPY VARCHAR2
318 )
319
320 IS
321 ln_reg_id NUMBER;
322 CURSOR cur_get_inv_det ( cp_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE ,
323 cp_customer_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID%TYPE DEFAULT NULL
324 )
325 IS
326 SELECT
327 jrcttl.* ,
328 jrct.organization_id ,
329 jrct.location_id ,
330 jrttv.regime_id ,
331 jtc.tax_type
332 FROM
333 JAI_AR_TRXS jrct ,
334 JAI_AR_TRX_LINES jrctl ,
335 JAI_AR_TRX_TAX_LINES jrcttl ,
336 JAI_CMN_TAXES_ALL jtc ,
337 jai_regime_tax_types_v jrttv
338
339 WHERE
340 jrct.customer_trx_id = jrctl.customer_trx_id
341 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
342 AND jrcttl.tax_id = jtc.tax_id
343 AND jtc.tax_type = jrttv.tax_type
344 AND jrttv.regime_code = jai_constants.tcs_regime
345 AND jrct.customer_trx_id = cp_customer_trx_id
346 AND jrctl.customer_trx_line_id = nvl( cp_customer_trx_line_id , jrctl.customer_trx_line_id );
347
348 v_ref_10 GL_INTERFACE.REFERENCE10%TYPE ;
349 v_std_text VARCHAR2(50) ;
350 v_ref_23 GL_INTERFACE.REFERENCE23%TYPE ; -- holds the object name
351 v_ref_24 GL_INTERFACE.REFERENCE24%TYPE ; -- holds the table name
352 v_ref_25 GL_INTERFACE.REFERENCE25%TYPE ; -- holds the column name
353 v_ref_26 GL_INTERFACE.REFERENCE26%TYPE ; -- holds the column value -- eg -- 13645
354
355 lv_source_name VARCHAR2(100) ;
356 lv_category_name VARCHAR2(100) ;
357
358 v_int_liab_acc_ccid NUMBER;
359 v_liab_acc_ccid NUMBER;
360 BEGIN
361 /*########################################################################################################
362 || VARIABLES INITIALIZATION - PART -1
363 ########################################################################################################*/
364 lv_member_name := 'AR_ACCOUNTING';
365 v_ref_23 := 'jai_ar_tcs_rep_pkg.ar_accounting';
369 lv_category_name := 'Receivables India' ; -- modified by csahoo for bug#6155839
366 v_ref_24 := 'ra_customer_trx_all' ;
367 v_ref_25 := 'customer_trx_id' ;
368 lv_source_name := jai_constants.tcs_source ; -- modified by csahoo for bug#6155839
370 set_debug_context;
371
372
373 /* jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
374 pn_reg_id => ln_reg_id
375 ); --commmented by CSahoo, BUG#5631784*/
376 p_process_flag := jai_constants.successful ;
377 p_process_message := null ;
378
379 v_ref_26 := nvl(p_ract.customer_trx_id,p_ractl.customer_trx_id) ;
380
381 IF p_ract.customer_trx_id IS NOT NULL THEN
382 v_std_text := 'India Localization Entry for Manual Invoices#' ;
383 ELSIF p_ractl.customer_trx_id IS NOT NULL THEN
384 v_std_text := 'India Localization Entry for Bill Only Invoices#' ;
385 END IF;
386
387 v_ref_10 := v_std_text || p_ract.trx_number || ' and customer_trx_id :' || nvl(p_ract.customer_trx_id,p_ractl.customer_trx_id) ;
388
389
390 FOR rec_cur_get_inv_det IN cur_get_inv_det ( cp_customer_trx_id => nvl(p_ract.customer_trx_id,p_ractl.customer_trx_id ),
391 cp_customer_trx_line_id => p_ractl.customer_trx_line_id
392 )
393
394 LOOP
395
396
397 /*********************************************************************************************************
398 || Get the code combination id from the Organization/Regime Registration setup
399 || by calling the function jai_cmn_rgm_recording_pkg.get_account
400 *********************************************************************************************************/
401
402 /*commented by csahoo for bug# 6401388
403 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
404 pv_log_msg => 'Before call to jai_cmn_rgm_recording_pkg.get_account for int liab'
405 );*/
406 v_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
407 p_regime_id => rec_cur_get_inv_det.regime_id ,
408 p_organization_type => jai_constants.orgn_type_io ,
409 p_organization_id => rec_cur_get_inv_det.organization_id ,
410 p_location_id => rec_cur_get_inv_det.location_id ,
411 p_tax_type => rec_cur_get_inv_det.tax_type ,
412 p_account_name => jai_constants.liability
413 );
414
415 /*commented by csahoo for bug# 6401388
416 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
417 pv_log_msg => 'Before call to jai_cmn_rgm_recording_pkg.get_account for liab'
418 );*/
419 v_int_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
420 p_regime_id => rec_cur_get_inv_det.regime_id ,
421 p_organization_type => jai_constants.orgn_type_io ,
422 p_organization_id => rec_cur_get_inv_det.organization_id ,
423 p_location_id => rec_cur_get_inv_det.location_id ,
424 p_tax_type => rec_cur_get_inv_det.tax_type ,
425 p_account_name => jai_constants.liability_interim
426 );
427
428
429
430 IF v_int_liab_acc_ccid IS NULL OR
431 v_liab_acc_ccid IS NULL
432 THEN
433 /**********************************************************************************************************
434 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
435 || This is an error condition and the current processing has to be stopped
436 **********************************************************************************************************/
437 /*commented by csahoo for bug# 6401388
438 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
439 pv_log_msg => 'Error in call to jai_cmn_rgm_recording_pkg.get_account'
440 );*/
441 p_process_flag := jai_constants.expected_error;
442 p_process_message := 'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup';
443 return;
444 END IF;
445
446
447 /*
448 ||Credit the liability account
449 */
450 /*commented by csahoo for bug# 6401388
451 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
452 PV_LOG_MSG => 'CREDIT MEMO ENTRIES GETTING PASSED TO jai_cmn_gl_pkg.create_gl_entry ARE :- ' ||fnd_global.local_chr(10)
456 ||', p_debit_amount -> '|| 0 ||fnd_global.local_chr(10)
453 ||', p_organization_id -> '|| rec_cur_get_inv_det.organization_id ||fnd_global.local_chr(10)
454 ||', p_currency_code -> '|| p_ract.invoice_currency_code ||fnd_global.local_chr(10)
455 ||', p_credit_amount -> '|| round(rec_cur_get_inv_det.func_tax_amount) ||fnd_global.local_chr(10)
457 ||', p_cc_id -> '|| v_liab_acc_ccid ||fnd_global.local_chr(10)
458 ||', p_je_source_name -> '|| lv_source_name ||fnd_global.local_chr(10)
459 ||', p_je_category_name -> '|| lv_category_name ||fnd_global.local_chr(10)
460 ||', p_created_by -> '|| rec_cur_get_inv_det.created_by ||fnd_global.local_chr(10)
461 ||', p_accounting_date -> '|| trunc(sysdate) ||fnd_global.local_chr(10)
462 ||', p_currency_conversion_date -> '|| NULL ||fnd_global.local_chr(10)
463 ||', p_currency_conversion_type -> '|| NULL ||fnd_global.local_chr(10)
464 ||', p_currency_conversion_rate -> '|| NULL ||fnd_global.local_chr(10)
465 ||', p_reference_10 -> '|| v_ref_10 ||fnd_global.local_chr(10)
466 ||', p_reference_23 -> '|| v_ref_23 ||fnd_global.local_chr(10)
467 ||', p_reference_24 -> '|| v_ref_24 ||fnd_global.local_chr(10)
468 ||', p_reference_25 -> '|| v_ref_25 ||fnd_global.local_chr(10)
469 ||', p_reference_26 -> '|| v_ref_26
470 );*/
471
472 jai_cmn_gl_pkg.create_gl_entry
473 (p_organization_id => rec_cur_get_inv_det.organization_id ,
474 p_currency_code => p_ract.invoice_currency_code ,
475 p_credit_amount => round(rec_cur_get_inv_det.func_tax_amount) ,
476 p_debit_amount => 0 ,
477 p_cc_id => v_liab_acc_ccid ,
478 p_je_source_name => lv_source_name ,
479 p_je_category_name => lv_category_name ,
480 p_created_by => rec_cur_get_inv_det.created_by ,
481 p_accounting_date => trunc(sysdate) ,
482 p_currency_conversion_date => NULL ,
483 p_currency_conversion_type => NULL ,
484 p_currency_conversion_rate => NULL ,
485 p_reference_10 => v_ref_10 ,
486 p_reference_23 => v_ref_23 ,
487 p_reference_24 => v_ref_24 ,
488 p_reference_25 => v_ref_25 ,
489 p_reference_26 => v_ref_26
490 );
491
492
493 /*
494 ||Debit the Interim liability account
495 */
496 /*commented by csahoo for bug# 6401388
497 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
498 pv_log_msg => 'DEBIT MEMO ENTRIES GETTING PASSED TO jai_cmn_gl_pkg.create_gl_entry ARE :- '||fnd_global.local_chr(10)
499 ||', p_organization_id -> '|| rec_cur_get_inv_det.organization_id ||fnd_global.local_chr(10)
500 ||', p_currency_code -> '|| p_ract.invoice_currency_code ||fnd_global.local_chr(10)
501 ||', p_credit_amount -> '|| 0 ||fnd_global.local_chr(10)
502 ||', p_debit_amount -> '|| round(rec_cur_get_inv_det.func_tax_amount) ||fnd_global.local_chr(10)
503 ||', p_cc_id -> '|| v_int_liab_acc_ccid ||fnd_global.local_chr(10)
504 ||', p_je_source_name -> '|| lv_source_name ||fnd_global.local_chr(10)
505 ||', p_je_category_name -> '|| lv_category_name ||fnd_global.local_chr(10)
506 ||', p_created_by -> '|| rec_cur_get_inv_det.created_by ||fnd_global.local_chr(10)
510 ||', p_currency_conversion_rate -> '|| NULL ||fnd_global.local_chr(10)
507 ||', p_accounting_date -> '|| trunc(sysdate) ||fnd_global.local_chr(10)
508 ||', p_currency_conversion_date -> '|| NULL ||fnd_global.local_chr(10)
509 ||', p_currency_conversion_type -> '|| NULL ||fnd_global.local_chr(10)
511 ||', p_reference_10 -> '|| v_ref_10 ||fnd_global.local_chr(10)
512 ||', p_reference_23 -> '|| v_ref_23 ||fnd_global.local_chr(10)
513 ||', p_reference_24 -> '|| v_ref_24 ||fnd_global.local_chr(10)
514 ||', p_reference_25 -> '|| v_ref_25 ||fnd_global.local_chr(10)
515 ||', p_reference_26 -> '|| v_ref_26
516 );*/
517
518 jai_cmn_gl_pkg.create_gl_entry
519 (p_organization_id => rec_cur_get_inv_det.organization_id ,
520 p_currency_code => p_ract.invoice_currency_code ,
521 p_credit_amount => 0 ,
522 p_debit_amount => round(rec_cur_get_inv_det.func_tax_amount) ,
523 p_cc_id => v_int_liab_acc_ccid ,
524 p_je_source_name => lv_source_name ,
525 p_je_category_name => lv_category_name ,
526 p_created_by => rec_cur_get_inv_det.created_by ,
527 p_accounting_date => trunc(sysdate) ,
528 p_currency_conversion_date => NULL ,
529 p_currency_conversion_type => NULL ,
530 p_currency_conversion_rate => NULL ,
531 p_reference_10 => v_ref_10 ,
532 p_reference_23 => v_ref_23 ,
533 p_reference_24 => v_ref_24 ,
534 p_reference_25 => v_ref_25 ,
535 p_reference_26 => v_ref_26
536 );
537
538 END LOOP;
539
540 /*commented by csahoo for bug# 6401388
541 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
542 pv_log_msg => '**************** MAN_AR_COMPLETION_ACCOUNTING SUCCESSFULLY COMPLETED ****************'
543 );
544 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
545
546 EXCEPTION
547 WHEN OTHERS THEN
548 p_process_flag := jai_constants.unexpected_error;
549 p_process_message := 'Unexpected error in the jai_ar_tcs_rep_pkg.man_ar_completion_accounting '||substr(sqlerrm,1,300);
550
551 END ar_accounting;
552
553
554 PROCEDURE validate_sales_order ( p_ooh IN OE_ORDER_HEADERS_ALL%ROWTYPE ,
555 p_process_flag OUT NOCOPY VARCHAR2 ,
556 p_process_message OUT NOCOPY VARCHAR2
557 )
558 IS
559 ln_reg_id NUMBER;
560
561 /*
562 || Check that the document has has TCS type of tax.
563 */
564 CURSOR cur_chk_tcs_applicable ( cp_header_id JAI_OM_OE_SO_LINES.HEADER_ID%TYPE )
565 IS
566 SELECT
567 1
568 FROM
569 JAI_OM_OE_SO_LINES jsl ,
570 JAI_OM_OE_SO_TAXES jstl ,
571 JAI_CMN_TAXES_ALL jtc ,
572 jai_regime_tax_types_v jrttv
573 WHERE
574 jsl.header_id = cp_header_id
575 AND jsl.line_id = jstl.line_id
576 AND jtc.tax_id = jstl.tax_id
577 AND jtc.tax_type = jrttv.tax_type
578 AND jrttv.regime_code = jai_constants.tcs_regime; /* Applied to doc has got TCS type of tax*/
579
580
581
582 /*
583 ||Now that some lines have got TCS type of taxes , check that all lines have got tcs type of taxes
584 ||if any one line does not have TCS type of tax then throw an error
585 */
586 CURSOR cur_chk_tcs_for_all_lines ( cp_header_id JAI_OM_OE_SO_LINES.HEADER_ID%TYPE )
587 IS
588 SELECT
589 1
590 FROM
591 JAI_OM_OE_SO_LINES jsl
592 WHERE
593 jsl.header_id = cp_header_id
594 AND NOT EXISTS (
595 SELECT
596 1
597 FROM
598 JAI_OM_OE_SO_TAXES jstl ,
599 JAI_CMN_TAXES_ALL jtc ,
600 jai_regime_tax_types_v jrttv
601 WHERE
602 jsl.line_id = jstl.line_id
606 );
603 AND jtc.tax_id = jstl.tax_id
604 AND jtc.tax_type = jrttv.tax_type
605 AND jrttv.regime_code = jai_constants.tcs_regime /* Applied to doc has got TCS type of tax*/
607
608 /*******
609 || Validate that the inventory_items for all lines of the sales order should have the same TCS item classification
610 || for the same has already been settled
611 ********/
612 CURSOR cur_validate_all_items ( cp_header_id JAI_OM_OE_SO_LINES.HEADER_ID%TYPE )
613 IS
614 SELECT
615 inventory_item_id
616 FROM
617 JAI_OM_OE_SO_LINES jsl
618 WHERE
619 jsl.header_id = cp_header_id;
620
621 lv_object_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE ;
622 lv_member_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
623 lv_context jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
624
625 ln_exists NUMBER ;
626 lv_first_itm_class JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
627 lv_item_classification JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
628
629 lv_process_flag VARCHAR2(2) ;
630 lv_process_message VARCHAR2(4000) ;
631
632 BEGIN
633
634 /*########################################################################################################
635 || VARIABLES INITIALIZATION - PART -1
636 ########################################################################################################*/
637 lv_member_name := 'VALIDATE_SALES_ORDER';
638 lv_object_name := 'TCS.JAI_AR_TCS_REP_PKG';
639 set_debug_context;
640 /*commented by csahoo for bug# 6401388
641 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
642 pn_reg_id => ln_reg_id
643 );*/
644 lv_process_flag := jai_constants.successful ;
645 lv_process_message := null ;
646
647 p_process_flag := lv_process_flag ;
648 p_process_message := lv_process_message ;
649
650 /*########################################################################################################
651 || SALES ORDER TCS APPLICABILITY CHECK - PART - 1
652 ########################################################################################################*/
653
654 /*
655 ||Check that the order has a flow status code as booked else skip the transaction
656 */
657 IF ln_event = jai_constants.order_booked AND
658 p_ooh.flow_status_code <> jai_constants.order_booked
659 THEN
660 /*commented by csahoo for bug# 6401388
661 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
662 pv_log_msg => ' Skip the transaction as order has a flow status code as '|| p_ooh.flow_status_code ||' which is different from BOOKED '
663 );*/
664 p_process_flag := jai_constants.not_applicable ;
665 return;
666
667 END IF;
668
669 /*
670 || Check that for sales order TCS applicability
671 || IF no then return
672 */
673
674 OPEN cur_chk_tcs_applicable ( cp_header_id => p_ooh.header_id ) ;
675 FETCH cur_chk_tcs_applicable INTO ln_exists ;
676 IF CUR_CHK_TCS_APPLICABLE%NOTFOUND THEN
677 /*commented by csahoo for bug# 6401388
678 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
679 pv_log_msg => ' Skip as order does not have TCS type of taxes '
680 );*/
681 p_process_flag := jai_constants.not_applicable ;
682 return;
683 END IF;
684 CLOSE cur_chk_tcs_applicable;
685
686
687
688 /*########################################################################################################
689 || VALIDATE THAT ALL LINES SHOULD HAVE TCS OR NIETHER SHOUD HAVE ANY - PART - 2
690 ########################################################################################################*/
691 IF ln_event IN ( jai_constants.order_booked ,
692 jai_constants.wsh_ship_confirm
693 )
694 THEN
695
696 /****************
697 || Event is Sales Order Booking
698 || Validate that all lines have TCS type of taxes
699 || if no then error out
700 *****************/
701
702 OPEN cur_chk_tcs_for_all_lines ( cp_header_id => p_ooh.header_id ) ;
703 FETCH cur_chk_tcs_for_all_lines INTO ln_exists;
704 IF CUR_CHK_TCS_FOR_ALL_LINES%FOUND THEN
705 /*
706 ||Rows with no TCS type of taxes exists hence error out
707 */
708 CLOSE cur_chk_tcs_for_all_lines ;
709 /*commented by csahoo for bug# 6401388
710 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
711 pv_log_msg => 'Error :- Cannot Book/ship the sales order if some lines have TCS type of tax and some dont '||fnd_global.local_chr(10)
712 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
713 ||', lv_process_message -> '||lv_process_message
714 );*/
715 p_process_flag := jai_constants.expected_error;
716 p_process_message := 'Cannot Book/Ship the Sales Order as some lines do not have TCS type of taxes ';
717 return;
718 CLOSE cur_chk_tcs_for_all_lines ;
719 END IF;
720
724 ########################################################################################################*/
721 /*########################################################################################################
722 || VALIDATE THAT THE INVENTORY_ITEMS FOR ALL LINES OF THE SALES ORDER
723 || SHOULD HAVE THE SAME TCS ITEM CLASSIFICATION - PART - 3
725
726 lv_first_itm_class := null;
727 lv_item_classification := null;
728 FOR rec_cur_validate_all_items IN cur_validate_all_items ( cp_header_id => p_ooh.header_id )
729 LOOP
730
731 /*
732 ||Get the value for the item classification pertaining to the IO and inventory item combination
733 */
734 jai_inv_items_pkg.jai_get_attrib (
735 p_regime_code => jai_constants.tcs_regime ,
736 p_organization_id => p_ooh.ship_from_org_id , -- Organization id of the Selling organization (warehouse_id)
737 p_inventory_item_id => rec_cur_validate_all_items.inventory_item_id ,
738 p_attribute_code => jai_constants.rgm_attr_cd_itm_class ,
739 p_attribute_value => lv_item_classification ,
740 p_process_flag => lv_process_flag ,
741 p_process_msg => lv_process_message
742 );
743
744 IF lv_process_flag = jai_constants.expected_error OR ---------A2
745 lv_process_flag = jai_constants.unexpected_error
746 THEN
747 /*
748 || As Returned status is an error/not applicable hence:-
749 || Set out variables p_process_flag and p_process_message accordingly
750 */
751 --call to debug package
752 /*commented by csahoo for bug# 6401388
753 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
754 pv_log_msg => 'Error In processing of jai_inv_items_pkg.jai_get_attrib'||fnd_global.local_chr(10)
755 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
756 ||', lv_process_message -> '||lv_process_message
757 );*/
758
759 p_process_flag := lv_process_flag ;
760 p_process_message := lv_process_message ;
761 return;
762 END IF; ---------A2
763
764 IF lv_first_itm_class IS NULL THEN
765 /*
766 ||First time assignment
767 */
768 lv_first_itm_class := lv_item_classification;
769 END IF;
770
771 /*
772 ||IF any one of the lines do not match with the item TCS classification of the first line
773 || then stop the transaction and throw an error.
774 */
775 IF nvl(lv_first_itm_class,'$$') <> nvl(lv_item_classification,'###') THEN
776 /*commented by csahoo for bug# 6401388
777 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
778 pv_log_msg => 'Error :- Cannot Book/Ship the sales order as as all lines do not belong to the same Item Classification '||fnd_global.local_chr(10)
779 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
780 ||', lv_process_message -> '||lv_process_message
781 );*/
782
783 p_process_flag := jai_constants.expected_error ;
784 p_process_message := 'Cannot Book/Ship the sales order. All lines should either have the same TCS item classification or none of the line should have TCS type of taxes' ;
785 return;
786 END IF;
787 END LOOP;
788
789 END IF;
790
791 /*commented by csahoo for bug# 6401388
792 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
793 pv_log_msg => '**************** VALIDATE_SALES_ORDER SUCCESSFULLY COMPLETED ****************'
794 );
795 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
796
797
798 END validate_sales_order ;
799
800 PROCEDURE validate_invoice ( p_ract IN RA_CUSTOMER_TRX_ALL%ROWTYPE ,
801 p_document_type OUT NOCOPY VARCHAR2 ,
802 p_process_flag OUT NOCOPY VARCHAR2 ,
803 p_process_message OUT NOCOPY VARCHAR2
804 )
805 IS
806 ln_reg_id NUMBER;
807 /*
808 || Check that the document has has TCS type of tax.
809 */
810 CURSOR cur_chk_tcs_applicable ( cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE )
811 IS
812 SELECT
813 gl_dist.gl_date ,
814 jrct.organization_id
815 FROM
816 ra_cust_trx_line_gl_dist_all gl_dist ,
817 JAI_AR_TRXS jrct ,
818 JAI_AR_TRX_LINES jrctl ,
819 JAI_AR_TRX_TAX_LINES jrcttl ,
820 JAI_CMN_TAXES_ALL jtc ,
821 jai_regime_tax_types_v jrttv
822 WHERE
823 gl_dist.customer_trx_id = jrct.customer_trx_id
824 AND gl_dist.account_class = jai_constants.account_class_rec
825 AND gl_dist.latest_rec_flag = jai_constants.yes
829 AND jtc.tax_id = jrcttl.tax_id
826 AND jrct.customer_trx_id = cp_customer_trx_id
827 AND jrct.customer_trx_id = jrctl.customer_trx_id
828 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
830 AND jtc.tax_type = jrttv.tax_type
831 AND jrttv.regime_code = jai_constants.tcs_regime; /* Applied to doc has got TCS type of tax*/
832
833
834 /*
835 ||Get the trx type of the document
836 */
837 CURSOR cur_get_doc_det ( cp_cust_trx_type_id RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID%TYPE )
838 IS
839 SELECT
840 type
841 FROM
842 ra_cust_trx_types_all
843 WHERE
844 cust_trx_type_id = cp_cust_trx_type_id;
845
846 /*
847 ||Check whether TCS on the invoice/DEbit memo have been settled.
848 */
849 CURSOR cur_chk_tcs_settlement ( cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE )
850 IS
851 SELECT
852 '1'
853 FROM
854 jai_rgm_refs_all
855 WHERE
856 source_document_id = cp_customer_trx_id
857 AND settlement_id IS NOT NULL ;
858
859 /*
860 ||Now that some lines have got TCS type of taxes , check that all lines have got tcs type of taxes
861 ||if any one line does not have TCS type of tax then throw an error
862 */
863 CURSOR cur_chk_tcs_for_all_lines (cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE )
864 IS
865 SELECT
866 1
867 FROM
868 JAI_AR_TRX_LINES jrctl
869 WHERE
870 jrctl.customer_trx_id = cp_customer_trx_id
871 AND NOT EXISTS ( SELECT /*Check that TCS type of taxes are not */
872 1
873 FROM
874 JAI_AR_TRX_TAX_LINES jrcttl ,
875 JAI_CMN_TAXES_ALL jtc ,
876 jai_regime_tax_types_v jrttv
877 WHERE
878 jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
879 AND jtc.tax_id = jrcttl.tax_id
880 AND jtc.tax_type = jrttv.tax_type
881 AND jrttv.regime_code = jai_constants.tcs_regime
882 );
883
884
885 /*
886 ||Check tcs surcharge applicability on document
887 */
888 CURSOR cur_chk_tcs_sur_tax ( cp_customer_trx_id JAI_AR_TRX_LINES.CUSTOMER_TRX_ID%TYPE )
889 IS
890 SELECT
891 count(*) surcharge_cnt
892 FROM
893 JAI_AR_TRX_LINES jrctl ,
894 JAI_AR_TRX_TAX_LINES jrcttl ,
895 JAI_CMN_TAXES_ALL jtc ,
896 jai_regime_tax_types_v jrttv
897 WHERE
898 jrctl.customer_trx_id = cp_customer_trx_id
899 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
900 AND jrcttl.tax_id = jtc.tax_id
901 AND jtc.tax_type = jrttv.tax_type
902 AND jrttv.tax_type = jai_constants.tax_type_tcs_surcharge
903 AND jrttv.regime_code = jai_constants.tcs_regime;
904
905
906
907 /*******
908 || Validate that the inventory_items for all lines of the invoice should have the same TCS item classification
909 || for the same has already been settled
910 ********/
911 CURSOR cur_validate_all_items ( cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE )
912 IS
913 SELECT
914 organization_id ,
915 inventory_item_id
916 FROM
917 JAI_AR_TRXS jrct ,
918 JAI_AR_TRX_LINES jrctl
919 WHERE
920 jrct.customer_trx_id = cp_customer_trx_id
921 AND jrct.customer_trx_id = jrctl.customer_trx_id;
922
923 lv_trx_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE ;
924 lv_doc_type VARCHAR2(100) ;
925 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
926 lv_org_tan_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
927 ln_threshold_slab_id JAI_RGM_REFS_ALL.THRESHOLD_SLAB_ID%TYPE ;
928 ln_exists NUMBER(2) ;
929 lv_first_itm_class JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
930 lv_item_classification JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
931 lv_process_flag VARCHAR2(2) ;
932 lv_process_message VARCHAR2(4000) ;
933 ln_organization_id JAI_RGM_REFS_ALL.ORGANIZATION_ID%TYPE ;
934 ln_surcharge_cnt NUMBER(2) := 0 ;
935 ld_source_doc_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE ;
936 BEGIN
937
938 /*########################################################################################################
939 || VARIABLES INITIALIZATION - PART -1
940 ########################################################################################################*/
941 lv_member_name := 'VALIDATE_INVOICE';
942 set_debug_context;
943
944 /*commented by csahoo for bug# 6401388
945 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
946 pn_reg_id => ln_reg_id
947 );*/
948
952 p_process_flag := lv_process_flag ;
949 lv_process_flag := jai_constants.successful ;
950 lv_process_message := null ;
951
953 p_process_message := lv_process_message ;
954
955 /*commented by csahoo for bug# 6401388
956 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
957 pv_log_msg => ' Validating the Document '||fnd_global.local_chr(10)
958 ||', p_ract.trx_number -> '||p_ract.trx_number ||fnd_global.local_chr(10)
959 ||', p_ract.customer_trx_id -> '||p_ract.customer_trx_id
960 );*/
961 /*########################################################################################################
962 || CHECK TCS APPLICABILITY PART -2
963 ########################################################################################################*/
964 /*commented by csahoo for bug# 6401388
965 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
966 pv_log_msg => ' Check TCS applicability '
967 );*/
968 /*
969 || Check whether the TCS is applicable on the document if no
970 || do not process
971 */
972 OPEN cur_chk_tcs_applicable ( cp_customer_trx_id => p_ract.customer_trx_id );
973 FETCH cur_chk_tcs_applicable INTO ld_source_doc_date ,ln_organization_id;
974 IF cur_chk_tcs_applicable%NOTFOUND THEN
975 /*commented by csahoo for bug# 6401388
976 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
977 pv_log_msg => ' TCS taxes not present on Invoice '
978 );*/
979 CLOSE cur_chk_tcs_applicable ;
980 /*
981 ||Check whether the invoice has been created due to the secondary creation
982 || IF yes punch/reset to null, the customer_trx_id into jai_rgm_item_gen_docs.generated_doc_id based on the
983 ||complete flag
984 */
985 /*commented by csahoo for bug# 6401388
986 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
987 pv_log_msg => 'Call to procedure update_item_gen_docs to check whether this is the TCS secondary document'
988 );*/
989
990 update_item_gen_docs ( p_trx_number => p_ract.trx_number ,
991 p_customer_trx_id => p_ract.customer_trx_id ,
992 p_complete_flag => p_ract.complete_flag ,
993 p_org_id => p_ract.org_id ,
994 p_process_flag => lv_process_flag ,
995 p_process_message => lv_process_message
996 );
997 /*commented by csahoo for bug# 6401388
998 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
999 pv_log_msg => 'returned from update_item_gen_docs lv_process_flag -> '||fnd_global.local_chr(10)
1000 ||', lv_process_flag -> '|| lv_process_flag || fnd_global.local_chr(10)
1001 ||', lv_process_message -> '|| lv_process_message || fnd_global.local_chr(10)
1002 );*/
1003
1004 /*commented by csahoo for bug# 6401388
1005 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1006 pv_log_msg => 'Skip furthur processing as Invoice does not have TCS type of taxes '
1007 );*/
1008
1009 p_process_flag := lv_process_flag ;
1010 p_process_message := lv_process_message ;
1011
1012 return;
1013
1014 END IF;
1015 CLOSE cur_chk_tcs_applicable ;
1016
1017 /*commented by csahoo for bug# 6401388
1018 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1019 pv_log_msg => ' Document Parameters '||fnd_global.local_chr(10)
1020 ||', source_doc_date -> '||ld_source_doc_date ||fnd_global.local_chr(10)
1021 ||', organization_id-> '||ln_organization_id
1022 );*/
1023 /*########################################################################################################
1024 || SKIP FOR CM PART -3
1025 ########################################################################################################*/
1026
1027 OPEN cur_get_doc_det ( cp_cust_trx_type_id => p_ract.cust_trx_type_id );
1028 FETCH cur_get_doc_det INTO lv_trx_type;
1029 CLOSE cur_get_doc_det ;
1030
1031 /*commented by csahoo for bug# 6401388
1032 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1033 pv_log_msg => ' Current document type is '||lv_trx_type
1034 );*/
1035 /*
1036 || Return if document type is a credit memo
1037 || as there is no functionality around a Credit Memo Completion/Incompletion
1038 */
1039 IF lv_trx_type = 'CM' THEN
1040 /*commented by csahoo for bug# 6401388
1041 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1042 pv_log_msg => ' Current document is a CM hence SKIP'
1043 );*/
1044 p_process_flag := jai_constants.not_applicable;
1045 return;
1046 END IF;
1047
1051
1048 /*########################################################################################################
1049 || VALIDATIONS FOR INVOICE COMPLETION PART - 4
1050 ########################################################################################################*/
1052 IF ln_event = jai_constants.trx_event_completion THEN
1053
1054 /*commented by csahoo for bug# 6401388
1055 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1056 pv_log_msg => ' Event is -> '||ln_event
1057 );*/
1058
1059 /*########################################################################################################
1060 || DERIVE DOCUMENT TYPE - PART - 4.1
1061 ########################################################################################################*/
1062
1063
1064 IF p_ract.complete_flag = jai_constants.yes THEN
1065
1066 /*commented by csahoo for bug# 6401388
1067 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1068 pv_log_msg => ' Complete -> '||p_ract.complete_flag
1069 );*/
1070
1071 IF lv_trx_type IN (jai_constants.ar_invoice_type_inv,jai_constants.ar_doc_type_dm) THEN
1072 /*
1073 ||Invoice/DM completion
1074 */
1075 lv_doc_type := jai_constants.trx_type_inv_comp;
1076 ELSE
1077 /*commented by csahoo for bug# 6401388
1078 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1079 pv_log_msg => ' Skip as type -> '||lv_trx_type ||' not applicable for TCS processing '
1080 );*/
1081 p_process_flag := jai_constants.not_applicable;
1082 return;
1083 END IF;
1084
1085 ELSIF p_ract.complete_flag = jai_constants.no THEN
1086 /*
1087 ||
1088 */
1089 IF lv_trx_type IN (jai_constants.ar_invoice_type_inv,jai_constants.ar_doc_type_dm) THEN
1090 /*
1091 ||Invoice/DM incompletion
1092 */
1093 lv_doc_type := jai_constants.trx_type_inv_incomp;
1094 ELSE
1095 /*commented by csahoo for bug# 6401388
1096 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1097 pv_log_msg => ' Skip as type -> '||lv_trx_type ||' not applicable for TCS processing '
1098 );*/
1099 p_process_flag := jai_constants.not_applicable;
1100 return;
1101 END IF;
1102 END IF;
1103
1104
1105 /*########################################################################################################
1106 || INVOICE INCOMPLETION VALIDATIONS - PART - 4.2
1107 ########################################################################################################*/
1108
1109
1110 /*******
1111 || Validate that an invoice cannot be incompleted if the TCS
1112 || for the same has already been settled
1113 ********/
1114 IF lv_trx_type IN ( jai_constants.ar_invoice_type_inv,
1115 jai_constants.ar_doc_type_dm
1116 ) AND
1117 lv_doc_type = jai_constants.trx_type_inv_incomp
1118
1119 THEN -----------------A1
1120 /*
1121 ||Trx type is invoice or Debit memo
1122 */
1123 OPEN cur_chk_tcs_settlement ( cp_customer_trx_id => p_ract.customer_trx_id );
1124 FETCH cur_chk_tcs_settlement INTO ln_exists;
1125 IF CUR_CHK_TCS_SETTLEMENT%FOUND THEN
1126 /*commented by csahoo for bug# 6401388
1127 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1128 pv_log_msg => 'Error :- Cannot Incomplete the invoice if it has already been settled '||fnd_global.local_chr(10)
1129 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
1130 ||', lv_process_message -> '||lv_process_message
1131 );*/
1132 p_process_flag := jai_constants.expected_error;
1133 p_process_message := 'Cannot incomplete the Invoice/Debit Memo as TCS taxes have already been settled';
1134 return;
1135 END IF;
1136 CLOSE cur_chk_tcs_settlement;
1137 END IF; -----------------A2
1138 END IF;
1139
1140
1141 /*########################################################################################################
1142 || INVOICE COMPLETION VALIDATIONS - PART - 4.2
1143 ########################################################################################################*/
1144 IF lv_trx_type IN ( jai_constants.ar_invoice_type_inv,
1145 jai_constants.ar_doc_type_dm
1146 ) AND
1147 lv_doc_type = jai_constants.trx_type_inv_comp
1148 THEN -----------------A3
1149 /****************
1150 || Event is completion
1151 || Validate that all lines have TCS type of taxes
1152 || if no then error out
1153 *****************/
1154
1155 OPEN cur_chk_tcs_for_all_lines ( cp_customer_trx_id => p_ract.customer_trx_id );
1156 FETCH cur_chk_tcs_for_all_lines INTO ln_exists;
1160 */
1157 IF CUR_CHK_TCS_FOR_ALL_LINES%FOUND THEN
1158 /*
1159 ||Rows with no TCS type of taxes exists hence error out
1161 CLOSE cur_chk_tcs_for_all_lines ;
1162 /*commented by csahoo for bug# 6401388
1163 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1164 pv_log_msg => 'Error :- Cannot complete the invoice if some lines have TCS type of tax and some dont '||fnd_global.local_chr(10)
1165 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
1166 ||', lv_process_message -> '||lv_process_message
1167 );*/
1168 p_process_flag := jai_constants.expected_error;
1169 p_process_message := 'Cannot complete the Invoice/Debit Memo as some lines of the document do not have TCS type of taxes ';
1170 return;
1171 CLOSE cur_chk_tcs_for_all_lines ;
1172 END IF;
1173
1174 /*******
1175 || Validate that the inventory_items for all lines of the invoice should have the same TCS item classification
1176 ********/
1177 lv_first_itm_class := null;
1178 lv_item_classification := null;
1179 FOR rec_cur_validate_all_items IN cur_validate_all_items ( cp_customer_trx_id => p_ract.customer_trx_id )
1180 LOOP
1181
1182 /*
1183 ||Get the value for the item classification pertaining to the IO and inventory item combination
1184 */
1185 jai_inv_items_pkg.jai_get_attrib (
1186 p_regime_code => jai_constants.tcs_regime ,
1187 p_organization_id => rec_cur_validate_all_items.organization_id ,
1188 p_inventory_item_id => rec_cur_validate_all_items.inventory_item_id ,
1189 p_attribute_code => jai_constants.rgm_attr_cd_itm_class ,
1190 p_attribute_value => lv_item_classification ,
1191 p_process_flag => lv_process_flag ,
1192 p_process_msg => lv_process_message
1193 );
1194
1195 IF lv_process_flag = jai_constants.expected_error OR ---------A2
1196 lv_process_flag = jai_constants.unexpected_error
1197 THEN
1198 /*
1199 || As Returned status is an error/not applicable hence:-
1200 || Set out variables p_process_flag and p_process_message accordingly
1201 */
1202 --call to debug package
1203 /*commented by csahoo for bug# 6401388
1204 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1205 pv_log_msg => 'Error In processing of jai_inv_items_pkg.jai_get_attrib'||fnd_global.local_chr(10)
1206 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
1207 ||', lv_process_message -> '||lv_process_message
1208 );*/
1209
1210 p_process_flag := lv_process_flag ;
1211 p_process_message := lv_process_message ;
1212 return;
1213 END IF; ---------A2
1214
1215 IF lv_first_itm_class IS NULL THEN
1216 /*
1217 ||First time assignment
1218 */
1219 lv_first_itm_class := lv_item_classification;
1220 END IF;
1221
1222 /*
1223 ||IF any one of the lines do not match with the item TCS classification of the first line
1224 || then stop the transaction and throw an error.
1225 */
1226 IF nvl(lv_first_itm_class,'$$') <> nvl(lv_item_classification,'###') THEN
1227 /*commented by csahoo for bug# 6401388
1228 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1229 pv_log_msg => 'Error :- Cannot complete as all lines do not belong to the same Item Classification '||fnd_global.local_chr(10)
1230 ||', lv_process_flag -> '|| lv_process_flag ||fnd_global.local_chr(10)
1231 ||', lv_process_message -> '||lv_process_message
1232 );*/
1233
1234 p_process_flag := jai_constants.expected_error ;
1235 p_process_message := 'Cannot complete invoice. All lines should either have the same TCS item classification or none of the line should have TCS type of taxes' ;
1236 return;
1237 END IF;
1238
1239 END LOOP;
1240
1241 /*########################################################################################################
1242 || THRESHOLD VALIDATIONS - PART - 4.3
1243 ########################################################################################################*/
1244
1245 OPEN cur_chk_tcs_sur_tax ( cp_customer_trx_id => p_ract.customer_trx_id );
1246 FETCH cur_chk_tcs_sur_tax INTO ln_surcharge_cnt;
1247 CLOSE cur_chk_tcs_sur_tax ;
1248
1249 OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.tcs_regime ,
1253 FETCH c_get_rgm_attribute INTO ln_regime_id, lv_org_tan_no ;
1250 cp_attribute_code => jai_constants.rgm_attr_code_org_tan ,
1251 cp_organization_id => ln_organization_id
1252 ) ;
1254 CLOSE c_get_rgm_attribute;
1255
1256 jai_rgm_thhold_proc_pkg.get_threshold_slab_id (
1257 p_regime_id => ln_regime_id ,
1258 p_org_tan_no => lv_org_tan_no ,
1259 p_party_type => jai_constants.party_type_customer ,
1260 p_party_id => nvl(p_ract.ship_to_customer_id,p_ract.bill_to_customer_id) ,
1261 p_source_trx_date => ld_source_doc_date ,
1262 p_org_id => p_ract.org_id ,
1263 p_threshold_slab_id => ln_threshold_slab_id ,
1264 p_process_flag => lv_process_flag ,
1265 p_process_message => lv_process_message
1266 );
1267
1268 IF lv_process_flag = jai_constants.expected_error OR ---------A2
1269 lv_process_flag = jai_constants.unexpected_error OR
1270 lv_process_flag = jai_constants.not_applicable
1271 THEN
1272 /*
1273 || As Returned status is an error/not applicable hence:-
1274 || Set out variables p_process_flag and p_process_message accordingly
1275 */
1276 --call to debug package
1277 p_process_flag := lv_process_flag ;
1278 p_process_message := lv_process_message ;
1279 return;
1280 END IF; ---------A2
1281
1282
1283 IF ln_threshold_slab_id IS NOT NULL THEN
1284 /*
1285 ||IF threshold level is up and surcharge type of taxes not present
1286 || on the invoice line then error
1287 */
1288 IF ln_surcharge_cnt = 0 THEN /* Surcharge does not exist */
1289 p_process_flag := jai_constants.expected_error ;
1290 p_process_message := 'Cannot complete invoice as surcharge is applicable however TCS Surcharge tax is not found on the document' ;
1291 return;
1292 END IF;
1293 ELSE
1294 /*
1295 ||IF threshold level is down and surcharge type of taxes are present
1296 || on the invoice line then error
1297 */
1298 IF ln_surcharge_cnt = 1 THEN /* Surcharge exist */
1299 p_process_flag := jai_constants.expected_error ;
1300 p_process_message := 'Cannot complete invoice as surcharge is not applicable however TCS Surcharge tax is found on the document' ;
1301 return;
1302 END IF;
1303 END IF;
1304 END IF; /*Event is Completion and document type is invoice or DM*/ -----------------A3
1305
1306 p_document_type := lv_doc_type;
1307
1308 /*commented by csahoo for bug# 6401388
1309 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1310 pv_log_msg => '**************** END OF VALIDATE_INVOICE ****************'
1311 );
1312 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
1313 END validate_invoice;
1314
1315
1316
1317 PROCEDURE validate_app_unapp (
1318 p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE ,
1319 p_document_type OUT NOCOPY VARCHAR2 ,
1320 p_item_classification OUT NOCOPY JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ,
1321 p_process_flag OUT NOCOPY VARCHAR2 ,
1322 p_process_message OUT NOCOPY VARCHAR2
1323 )
1324 AS
1325 ln_reg_id NUMBER;
1326 /*
1327 || Applied to Document of the receivable application has TCS type of tax.
1328 */
1329 CURSOR cur_chk_tcs_applicable (cp_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE )
1330 IS
1331 SELECT
1332 jrct.organization_id ,
1333 jrctl.inventory_item_id ,
1334 gl_dist.amount
1335 FROM
1336 ra_customer_trx_all rcta ,
1337 ra_cust_trx_line_gl_dist_all gl_dist ,
1338 JAI_AR_TRXS jrct ,
1339 JAI_AR_TRX_LINES jrctl ,
1340 JAI_AR_TRX_TAX_LINES jrcttl ,
1341 JAI_CMN_TAXES_ALL jtc ,
1342 jai_regime_tax_types_v jrttv
1343 WHERE
1344 rcta.complete_flag = jai_constants.yes
1345 AND rcta.customer_trx_id = cp_customer_trx_id
1346 AND gl_dist.customer_trx_id = rcta.customer_trx_id
1347 AND gl_dist.account_class = jai_constants.account_class_rec
1351 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
1348 AND gl_dist.latest_rec_flag = jai_constants.yes
1349 AND rcta.customer_trx_id = jrct.customer_trx_id
1350 AND jrct.customer_trx_id = jrctl.customer_trx_id
1352 AND jtc.tax_id = jrcttl.tax_id
1353 AND jrttv.tax_type = jtc.tax_type /* Applied to doc has got TCS type of tax*/
1354 AND jrttv.regime_code = jai_constants.tcs_regime;
1355
1356 /*
1357 ||Get the sign of the Cash receipt document
1358 */
1359 CURSOR cur_get_cr_sign (cp_cash_receipt_id JAI_AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE )
1360 IS
1361 SELECT
1362 sign (nvl(amount,0)) app_fr_sign
1363 FROM
1364 ar_cash_receipts_all
1365 WHERE
1366 cash_receipt_id = cp_cash_receipt_id;
1367
1368 /*
1369 ||Get the sign of the Credit Memo document
1370 */
1371 CURSOR cur_get_cm_sign (cp_cm_customer_trx_id RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID%TYPE )
1372 IS
1373 SELECT
1374 sign(nvl(amount,0)) app_fr_sign
1375 FROM
1376 ra_cust_trx_line_gl_dist_all
1377 WHERE
1378 account_class = jai_constants.account_class_rec
1379 AND latest_rec_flag = jai_constants.yes
1380 AND customer_trx_id = cp_cm_customer_trx_id;
1381
1382 /*
1383 || Check that the Cash receipt has got TCS type of confirmed taxes
1384 */
1385 CURSOR cur_chk_crtcs_applicable ( cp_cash_receipt_id JAI_AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE )
1386 IS
1387 SELECT
1388 jcra.item_classification,
1389 acra.amount
1390 FROM
1391 ar_cash_receipts_all acra ,
1392 jai_ar_cash_receipts_all jcra ,
1393 jai_cmn_document_taxes jdt ,
1394 jai_regime_tax_types_v jrttv
1395 WHERE
1396 jcra.cash_receipt_id = acra.cash_receipt_id
1397 AND jcra.cash_receipt_id = cp_cash_receipt_id
1398 AND jcra.cash_receipt_id = jdt.source_doc_id
1399 AND jdt.source_table_name = jai_constants.jai_cash_rcpts /* 'JAI_AR_CASH_RECEIPTS_ALL' */
1400 AND jcra.confirm_flag = jai_constants.yes
1401 AND jdt.tax_type = jrttv.tax_type /* Applied to doc has got TCS type of tax*/
1402 AND jrttv.regime_code = jai_constants.tcs_regime
1403 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
1404
1405
1406 /*
1407 || Get the application details for the current unapplications from the repository
1408 */
1409 CURSOR cur_chk_parent_rec ( cp_applied_fr_doc_id JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_ID%TYPE ,
1410 cp_applied_to_doc_id JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_ID%TYPE
1411 )
1412 IS
1413 SELECT
1414 trx_ref_id ,
1415 settlement_id
1416 FROM
1417 jai_rgm_refs_all
1418 WHERE
1419 app_from_document_id = cp_applied_fr_doc_id
1420 AND app_to_document_id = cp_applied_to_doc_id ;
1421
1422 ln_sign_of_app_fr_doc NUMBER(3) ;
1423 ln_sign_of_app NUMBER(3) ;
1424 lv_exists VARCHAR2(1) ;
1425 ln_app_fr_itm_class JAI_AR_CASH_RECEIPTS_ALL.ITEM_CLASSIFICATION%TYPE ;
1426 ln_app_fr_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE ;
1427 ln_app_fr_inventory_item_id JAI_AR_TRX_LINES.INVENTORY_ITEM_ID%TYPE ;
1428 ln_app_fr_amount RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE ;
1429
1430 ln_app_to_amount RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE ;
1431 ln_app_to_itm_class JAI_AR_CASH_RECEIPTS_ALL.ITEM_CLASSIFICATION%TYPE ;
1432 ln_app_to_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE ;
1433 ln_app_to_inventory_item_id JAI_AR_TRX_LINES.INVENTORY_ITEM_ID%TYPE ;
1434
1435 lv_app_doc_type VARCHAR2(100) ;
1436 rec_cur_chk_parent_rec CUR_CHK_PARENT_REC%ROWTYPE ;
1437 lv_process_flag VARCHAR2(2) ;
1438 lv_process_message VARCHAR2(4000) ;
1439
1440
1441 BEGIN
1442
1443 /*########################################################################################################
1444 || VARIABLES INITIALIZATION - PART -1
1445 ########################################################################################################*/
1446
1447 lv_member_name := 'VALIDATE_APP_UNAPP';
1448 set_debug_context;
1449
1450 /*commented by csahoo for bug# 6401388
1451 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
1452 pn_reg_id => ln_reg_id
1453 );*/
1454
1455 lv_process_flag := jai_constants.successful ;
1456 lv_process_message := null ;
1457
1458 p_process_flag := lv_process_flag ;
1459 p_process_message := lv_process_message ;
1460 ln_sign_of_app_fr_doc := null ;
1461 ln_sign_of_app := sign(nvl(p_araa.amount_applied,0));
1462
1463 /*commented by csahoo for bug# 6401388
1464 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1465 pv_log_msg => ' Processing the APPLICATION record ' ||fnd_global.local_chr(10)
1469 ||', display -> ' ||p_araa.display ||fnd_global.local_chr(10)
1466 ||', receivable_application_id -> ' ||p_araa.receivable_application_id ||fnd_global.local_chr(10)
1467 ||', application_type -> ' ||p_araa.application_type ||fnd_global.local_chr(10)
1468 ||', status -> ' ||p_araa.status ||fnd_global.local_chr(10)
1470 ||', cash_receipt_id -> ' ||p_araa.cash_receipt_id ||fnd_global.local_chr(10)
1471 ||', amount_applied -> ' ||p_araa.amount_applied ||fnd_global.local_chr(10)
1472 ||', applied_customer_trx_id -> ' ||p_araa.applied_customer_trx_id
1473 );*/
1474
1475
1476 /*########################################################################################################
1477 || CHECK TCS APPLICABILITY ON APPLIED FROM DOCUMENTS AND DERIVE APPLICATION TYPE - PART -2
1478 ########################################################################################################*/
1479
1480 IF p_araa.application_type IN (jai_constants.ar_cash ,
1481 jai_constants.ar_status_activity
1482 ) AND -------------A1
1483 p_araa.cash_receipt_id IS NOT NULL
1484 THEN
1485 /*
1486 || Application is CASH
1487 || Check that cash receipt has tcs type of taxes which have been confirmed
1488 || Exit processing if the same is not found
1489 */
1490 /*commented by csahoo for bug# 6401388
1491 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1492 pv_log_msg => ' Checking TCS applicability On applied from documents'
1493 );*/
1494 OPEN cur_chk_crtcs_applicable (cp_cash_receipt_id => p_araa.cash_receipt_id );
1495 FETCH cur_chk_crtcs_applicable INTO ln_app_fr_itm_class, ln_app_fr_amount;
1496
1497 IF cur_chk_crtcs_applicable%NOTFOUND THEN
1498 /*commented by csahoo for bug# 6401388
1499 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1500 pv_log_msg => ' Skip As TCS not applicable ON Derived from documents '
1501 );*/
1502 CLOSE cur_chk_crtcs_applicable;
1503 p_process_flag := jai_constants.not_applicable ;
1504 return ;
1505 ELSE
1506 /*
1507 ||Check for ACTIVITY I.E receipt to receipt or receipt to credit memo any other type of application other than Receipt/CM to INV or DM
1508 */
1509 /*commented by csahoo for bug# 6401388
1510 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1511 pv_log_msg => 'TCS is applicable'
1512 );*/
1513
1514 IF p_araa.status = jai_constants.ar_status_activity THEN
1515 /*
1516 || As current receivable application is an activity indicating a receipt to receipt getting applied to another receipt
1517 || or Credit Memo hence stop this processing as otherwise this would lead to down stream TCS data corruption.
1518 */
1519 /*commented by csahoo for bug# 6401388
1520 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1521 pv_log_msg => 'Cannot apply a RECEIPT to any other document (any of them having TCS applicability) other than a Invoice or Debit Memo'
1522 );*/
1523 p_process_flag := jai_constants.expected_error;
1524 p_process_message := 'Cannot apply a RECEIPT to any other document (any of them having TCS applicability) other than a Invoice or Debit Memo';
1525 return;
1526
1527 END IF;
1528 END IF;
1529
1530 CLOSE cur_chk_crtcs_applicable;
1531
1532 /*
1533 ||Get the sign of the cash receipt
1534 */
1535 OPEN cur_get_cr_sign ( cp_cash_receipt_id => p_araa.cash_receipt_id );
1536 FETCH cur_get_cr_sign INTO ln_sign_of_app_fr_doc ;
1537 CLOSE cur_get_cr_sign;
1538
1539 /*
1540 || IF sign of amount field of receivable application is the same as the sign of the cash receipt amount
1541 || then application is receipt application else it would be receipt un application
1542 */
1543 IF ln_sign_of_app = ln_sign_of_app_fr_doc THEN
1544 lv_app_doc_type := jai_constants.trx_type_rct_app ; /* Event is 'RECEIPT_APPLICATION' */
1545 ELSE
1546 lv_app_doc_type := jai_constants.trx_type_rct_unapp ;/* Event is 'RECEIPT_UNAPPLICATION' */
1547 END IF;
1548
1549
1550 ELSIF p_araa.application_type = jai_constants.ar_invoice_type_cm AND -------------A1
1551 p_araa.customer_trx_id IS NOT NULL
1552 THEN
1553 /*
1554 ||Application is Credit Memo
1555 */
1556 OPEN cur_chk_tcs_applicable( cp_customer_trx_id => p_araa.customer_trx_id );
1557 FETCH cur_chk_tcs_applicable INTO ln_app_fr_organization_id, ln_app_fr_inventory_item_id, ln_app_fr_amount;
1558
1559 /*commented by csahoo for bug# 6401388
1560 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1561 pv_log_msg => 'Application type is '||p_araa.application_type
1562 );*/
1563
1564 IF CUR_CHK_TCS_APPLICABLE%NOTFOUND THEN
1565 /*commented by csahoo for bug# 6401388
1569 CLOSE cur_chk_tcs_applicable;
1566 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1567 pv_log_msg => ' Skip As TCS not applicable ON Derived from documents '
1568 );*/
1570 p_process_flag := jai_constants.not_applicable ;
1571 return;
1572 END IF;
1573 CLOSE cur_chk_tcs_applicable;
1574
1575 /*
1576 ||Get the sign of the Credit Memo
1577 */
1578 OPEN cur_get_cm_sign ( cp_cm_customer_trx_id => p_araa.customer_trx_id ) ;
1579 FETCH cur_get_cm_sign INTO ln_sign_of_app_fr_doc;
1580 CLOSE cur_get_cm_sign;
1581
1582 /*
1583 || IF sign of amount field of receivable application is the same as the sign of the Credit Memo amount
1584 || then application is Credit Memo Application else it would be Credit Memo Unapplication
1585 */
1586 IF ln_sign_of_app = ln_sign_of_app_fr_doc THEN
1587 lv_app_doc_type := jai_constants.trx_type_cm_app ; /* Event is 'CREDIT_MEMO_APPLICATION' */
1588 ELSE
1589 lv_app_doc_type := jai_constants.trx_type_cm_unapp ;/* Event is 'CREDIT_MEMO_UNAPPLICATION' */
1590 END IF;
1591
1592 /*
1593 ||Get the value for the item classification pertaining to the IO and inventory item combination
1594 */
1595 jai_inv_items_pkg.jai_get_attrib (
1596 p_regime_code => jai_constants.tcs_regime ,
1597 p_organization_id => ln_app_fr_organization_id ,
1598 p_inventory_item_id => ln_app_fr_inventory_item_id ,
1599 p_attribute_code => jai_constants.rgm_attr_cd_itm_class ,
1600 p_attribute_value => ln_app_fr_itm_class ,
1601 p_process_flag => lv_process_flag ,
1602 p_process_msg => lv_process_message
1603 );
1604
1605 IF lv_process_flag = jai_constants.expected_error OR ---------A2
1606 lv_process_flag = jai_constants.unexpected_error
1607 THEN
1608 /*
1609 || As Returned status is an error hence:-
1610 || Set out variables p_process_flag and p_process_message accordingly
1611 */
1612 --call to debug package
1613 /*commented by csahoo for bug# 6401388
1614 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1615 pv_log_msg => ' Error in getting the item attribute'||fnd_global.local_chr(10)
1616 ||', p_process_flag -> '|| p_process_flag
1617 ||', lv_process_message -> '|| lv_process_message
1618 );*/
1619 p_process_flag := lv_process_flag ;
1620 p_process_message := lv_process_message ;
1621 return;
1622 END IF; ---------A2
1623
1624 ELSE -------------A1
1625 /*
1626 ||Return in case the scenario is niether CASH nor CM
1627 */
1628 /*commented by csahoo for bug# 6401388
1629 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1630 pv_log_msg => ' Skip as scenario is niether CASH nor CM '
1631 );*/
1632 p_process_flag := jai_constants.not_applicable ;
1633 return;
1634 END IF; -------------A1
1635
1636 /*commented by csahoo for bug# 6401388
1637 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1638 pv_log_msg => ' Application document type is '||lv_app_doc_type
1639 );*/
1640
1641
1642 /*########################################################################################################
1643 || Derive APPLIED TO DOCUMENT VALUES AND CHECK TCS APPLICABILITY ON APPLIED TO DOCUMENTS - PART -3
1644 ########################################################################################################*/
1645
1646 /*
1647 || Check that the applied to document has been completed and has got TCS type of taxes.
1648 || IF no then return
1649 */
1650 OPEN cur_chk_tcs_applicable( cp_customer_trx_id => p_araa.applied_customer_trx_id );
1651 FETCH cur_chk_tcs_applicable INTO ln_app_to_organization_id, ln_app_to_inventory_item_id, ln_app_to_amount ;
1652
1653 IF CUR_CHK_TCS_APPLICABLE%NOTFOUND THEN
1654 CLOSE cur_chk_tcs_applicable;
1655 p_process_flag := jai_constants.not_applicable ;
1656 /*commented by csahoo for bug# 6401388
1657 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1658 pv_log_msg => ' Skip the processing as TCS is not applicable on the transaction'
1659 );*/
1660 return;
1661 END IF;
1662 CLOSE cur_chk_tcs_applicable;
1663
1664 /*
1665 ||Get the value for the item classification pertaining to the IO and inventory item combination
1666 */
1667 jai_inv_items_pkg.jai_get_attrib (
1668 p_regime_code => jai_constants.tcs_regime ,
1669 p_organization_id => ln_app_to_organization_id ,
1670 p_inventory_item_id => ln_app_to_inventory_item_id ,
1671 p_attribute_code => jai_constants.rgm_attr_cd_itm_class ,
1675 );
1672 p_attribute_value => ln_app_to_itm_class ,
1673 p_process_flag => lv_process_flag ,
1674 p_process_msg => lv_process_message
1676
1677 IF lv_process_flag = jai_constants.expected_error OR ---------A2
1678 lv_process_flag = jai_constants.unexpected_error
1679 THEN
1680 /*
1681 || As Returned status is an error hence:-
1682 || Set out variables p_process_flag and p_process_message accordingly
1683 */
1684 --call to debug package
1685 /*commented by csahoo for bug# 6401388
1686 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1687 pv_log_msg => ' Error in getting the item classification '||fnd_global.local_chr(10)
1688 ||', p_process_flag -> '|| p_process_flag
1689 ||', lv_process_message -> '|| lv_process_message
1690 );*/
1691 p_process_flag := lv_process_flag ;
1692 p_process_message := lv_process_message ;
1693 return;
1694 END IF; ---------A2
1695
1696
1697 /*########################################################################################################
1698 || RESTRICTIONS ON APPLICATION - PART -4
1699 ########################################################################################################*/
1700
1701 /*
1702 || All lines of the applied from and to documents should belong to the same item classification.
1703 || Do not allow a transaction if this rule is not followed.
1704 */
1705
1706 IF lv_app_doc_type IN ( jai_constants.trx_type_rct_app ,
1707 jai_constants.trx_type_cm_app
1708 )
1709 THEN
1710 IF ln_app_fr_itm_class <> ln_app_to_itm_class THEN
1711
1712 /*commented by csahoo for bug# 6401388
1713 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1714 pv_log_msg => ' Item classification for the application from and to item class do not match hence error '||fnd_global.local_chr(10)
1715 ||', p_process_flag -> '|| p_process_flag
1716 ||', lv_process_message -> '|| lv_process_message
1717 );*/
1718 p_process_flag := jai_constants.expected_error ;
1719 p_process_message := 'Application is not allowed as the APPLIED FROM and TO DOCUMENTS have different item classifications.';
1720 return;
1721 END IF;
1722 END IF;
1723
1724 /*
1725 ||Do not allow overapplication transactions in case they both have TCS type of taxes
1726 */
1727 IF nvl(p_araa.amount_applied,0) > nvl(ln_app_to_amount,0) THEN
1728 /*commented by csahoo for bug# 6401388
1729 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1730 pv_log_msg => 'Case for Overapplication detected , Stop '||fnd_global.local_chr(10)
1731 ||', p_araa.amount_applied -> '|| p_araa.amount_applied ||fnd_global.local_chr(10)
1732 ||', applied to amount -> '|| ln_app_to_amount ||fnd_global.local_chr(10)
1733 ||', p_process_flag -> '|| p_process_flag ||fnd_global.local_chr(10)
1734 ||', lv_process_message -> '|| lv_process_message
1735 );*/
1736 p_process_flag := jai_constants.expected_error ;
1737 p_process_message := 'Over Application of a document to other is not allowed if both the taxes have TCS type of taxes .';
1738 return;
1739 END IF;
1740
1741 /*
1742 ||Do not allow a receipt to be applied to another receipt in case both have TCS type of taxes
1743 */
1744
1745
1746 /*########################################################################################################
1747 || RESTRICTIONS ON RECEIPT UNAPPLICATION
1748 ########################################################################################################*/
1749
1750 IF lv_app_doc_type = jai_constants.trx_type_rct_unapp THEN
1751 /*
1752 ||Validate that the parent receipt application record is present in the repository.
1753 */
1754 OPEN cur_chk_parent_rec ( cp_applied_fr_doc_id => p_araa.cash_receipt_id ,
1755 cp_applied_to_doc_id => p_araa.applied_customer_trx_id
1756 );
1757 FETCH cur_chk_parent_rec INTO rec_cur_chk_parent_rec;
1758
1759 IF CUR_CHK_PARENT_REC%NOTFOUND THEN
1760 /*
1761 ||Exit processing as original application did not hit the repository.
1762 */
1763 CLOSE cur_chk_parent_rec;
1764 /*commented by csahoo for bug# 6401388
1765 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1766 pv_log_msg => 'Skip,case For unapplication, Parent transaction record is not present in the repository'||fnd_global.local_chr(10)
1767 ||', p_process_flag -> '|| p_process_flag ||fnd_global.local_chr(10)
1768 );*/
1769
1770 p_process_flag := jai_constants.not_applicable ;
1771 return;
1772 END IF;
1773
1777 /*
1774 CLOSE cur_chk_parent_rec;
1775
1776 IF rec_cur_chk_parent_rec.settlement_id is NOT NULL THEN
1778 || Original Application already settled hence
1779 */
1780 p_process_flag := jai_constants.expected_error ;
1781 p_process_message := 'Parent application has already been settled. hence cannot unapply';
1782 /*commented by csahoo for bug# 6401388
1783 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1784 pv_log_msg => ' Error :-Parent application has already been settled. hence cannot unapply'||fnd_global.local_chr(10)
1785 ||', p_process_flag -> '|| p_process_flag
1786 ||', lv_process_message -> '|| lv_process_message
1787 );*/
1788
1789 return;
1790 END IF;
1791
1792 END IF;
1793
1794
1795 /*########################################################################################################
1796 || RESTRICTIONS ON CREDIT MEMO UNAPPLICATION
1797 ########################################################################################################*/
1798
1799 IF lv_app_doc_type = jai_constants.trx_type_cm_unapp THEN
1800 /*
1801 ||Validate that the parent credit memo application record is present in the repository.
1802 */
1803 OPEN cur_chk_parent_rec ( cp_applied_fr_doc_id => p_araa.customer_trx_id ,
1804 cp_applied_to_doc_id => p_araa.applied_customer_trx_id
1805 );
1806 FETCH cur_chk_parent_rec INTO rec_cur_chk_parent_rec;
1807 IF CUR_CHK_PARENT_REC%NOTFOUND THEN
1808 /*
1809 ||Exit processing as original application did not hit the repository.
1810 */
1811 /*commented by csahoo for bug# 6401388
1812 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1813 pv_log_msg => ' Skip the processing as TCS is not applicable on the transaction'||fnd_global.local_chr(10)
1814 ||', p_process_flag -> '|| p_process_flag
1815 );*/
1816 CLOSE cur_chk_parent_rec;
1817 p_process_flag := jai_constants.not_applicable ;
1818 return;
1819 END IF;
1820
1821 CLOSE cur_chk_parent_rec;
1822
1823 IF rec_cur_chk_parent_rec.settlement_id is NOT NULL THEN
1824 /*
1825 || Original Application already settled hence
1826 */
1827 /*commented by csahoo for bug# 6401388
1828 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1829 pv_log_msg => ' Error :-Parent application has already been settled. hence cannot unapply'||fnd_global.local_chr(10)
1830 ||', p_process_flag -> '|| p_process_flag
1831 ||', lv_process_message -> '|| lv_process_message
1832 );*/
1833 p_process_flag := jai_constants.expected_error ;
1834 p_process_message := 'Parent application has already been settled. hence cannot unapply';
1835 return;
1836 END IF;
1837 END IF;
1838
1839
1840 /*########################################################################################################
1841 || Assign values to return variables
1842 ########################################################################################################*/
1843
1844 p_document_type := lv_app_doc_type ;
1845 p_item_classification := ln_app_to_itm_class ;
1846
1847 /*commented by csahoo for bug# 6401388
1848 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1849 pv_log_msg => '**************** PROCEDURE VALIDATE_APP_UNAPP SUCCESSFUL ****************'
1850 );
1851
1852 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
1853
1854 END validate_app_unapp;
1855
1856 PROCEDURE validate_receipts ( p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE ,
1857 p_document_type IN VARCHAR2 ,
1858 p_process_flag OUT NOCOPY VARCHAR2 ,
1859 p_process_message OUT NOCOPY VARCHAR2
1860 )
1861 IS
1862 ln_reg_id NUMBER;
1863 lv_process_flag VARCHAR2(2) ;
1864 lv_process_message VARCHAR2(2000) ;
1865
1866 /*
1867 || Check that the document has has TCS type of tax.
1868 */
1869 CURSOR cur_chk_tcs_applicable (cp_cash_receipt_id JAI_AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE )
1870 IS
1871 SELECT
1872 1
1873 FROM
1874 jai_ar_cash_receipts_all jcra ,
1875 jai_cmn_document_taxes jdt ,
1876 jai_regime_tax_types_v jrttv
1877 WHERE
1878 jcra.cash_receipt_id = cp_cash_receipt_id
1879 AND jcra.cash_receipt_id = jdt.source_doc_id
1880 AND jdt.tax_type = jrttv.tax_type /* Applied to doc has got TCS type of tax*/
1881 AND jrttv.regime_code = jai_constants.tcs_regime
1882 AND jcra.confirm_flag = jai_constants.yes
1883 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
1884
1885 /*
1886 ||Get the last record pertaining to the cash receipt confirmation
1887 */
1891 IS
1888 CURSOR cur_chk_tcs_settlement ( cp_source_document_id jai_rgm_refs_all.source_document_id%TYPE ,
1889 cp_source_document_type jai_rgm_refs_all.source_document_type%TYPE
1890 )
1892 SELECT
1893 trx_ref_id ,
1894 settlement_id
1895 FROM
1896 jai_rgm_refs_all a
1897 WHERE
1898 trx_ref_id = ( SELECT
1899 max(trx_ref_id)
1900 FROM
1901 jai_rgm_refs_all b
1902 WHERE
1903 b.source_document_id = cp_source_document_id
1904 AND b.source_document_type = cp_source_document_type
1905 );
1906
1907 ln_exists NUMBER(2) ;
1908 ln_settlement_id JAI_RGM_REFS_ALL.SETTLEMENT_ID%TYPE ;
1909 rec_cur_chk_tcs_settlement CUR_CHK_TCS_SETTLEMENT%ROWTYPE ;
1910 BEGIN
1911
1912 /*########################################################################################################
1913 || VARIABLES INITIALIZATION
1914 ########################################################################################################*/
1915
1916 lv_member_name := 'VALIDATE_RECEIPTS';
1917 set_debug_context;
1918 /*commented by csahoo for bug# 6401388
1919 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
1920 , pn_reg_id => ln_reg_id
1921 ); --commmented by CSahoo, BUG#5631784
1922
1923
1924 /*commented by csahoo for bug# 6401388
1925 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1926 pv_log_msg => ' PARAMETERS VALUES PASSED TO VALIDATE_RECEIPTS : - ' ||fnd_global.local_chr(10)
1927 ||', p_acra.receipt_number -> '||p_acra.receipt_number ||fnd_global.local_chr(10)
1928 ||', p_acra.cash_receipt_id -> '||p_acra.cash_receipt_id ||fnd_global.local_chr(10)
1929 ||', p_acra.amount -> '||p_acra.amount ||fnd_global.local_chr(10)
1930 ||', p_acra.type -> '||p_acra.type ||fnd_global.local_chr(10)
1931 );*/
1932
1933 lv_process_flag := jai_constants.successful ;
1934 lv_process_message := null ;
1935
1936 p_process_flag := lv_process_flag ;
1937 p_process_message := lv_process_message ;
1938
1939
1940 /*########################################################################################################
1941 || CHECK TCS APPLICABILITY PART -2
1942 ########################################################################################################*/
1943 /*commented by csahoo for bug# 6401388
1944 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1945 pv_log_msg => ' Check TCS applicability '
1946 );*/
1947 /*
1948 || Check whether the TCS is applicable on the document if no
1949 || do not process
1950 */
1951 OPEN cur_chk_tcs_applicable ( cp_cash_receipt_id => p_acra.cash_receipt_id );
1952 FETCH cur_chk_tcs_applicable INTO ln_exists;
1953 IF cur_chk_tcs_applicable%NOTFOUND THEN
1954 CLOSE cur_chk_tcs_applicable ;
1955 p_process_flag := jai_constants.not_applicable;
1956 return;
1957 END IF;
1958 CLOSE cur_chk_tcs_applicable ;
1959
1960 /*########################################################################################################
1961 || VALIDATIONS FOR RECEIPT REVERSAL
1962 ########################################################################################################*/
1963
1964 IF p_document_type = jai_constants.trx_type_rct_rvs THEN /* 'RECEIPT_REVERSAL' */
1965 /*
1966 || Check that an original record with cash receipt confirmation exists in the repository.
1967 || In case it does not exist, then error out the record as it need not hit the repository
1968 || If it exists then check whether it has been settled . If yes then throw an error
1969 */
1970 /*commented by csahoo for bug# 6401388
1971 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1972 pv_log_msg => 'Validate for Receipt Reversal '
1973 );*/
1974 OPEN cur_chk_tcs_settlement ( cp_source_document_id => p_acra.cash_receipt_id ,
1975 cp_source_document_type => jai_constants.ar_cash_tax_confirmed
1976 );
1977 FETCH cur_chk_tcs_settlement INTO rec_cur_chk_tcs_settlement;
1978 IF CUR_CHK_TCS_SETTLEMENT%NOTFOUND THEN
1979 /*
1980 ||Original receipt not found in repository hence throw an error
1981 */
1982 /*commented by csahoo for bug# 6401388
1983 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1984 pv_log_msg => 'Original Receipt Confirmation records does not exists in the repository. Cannot reverse receipt'
1985 );*/
1986 CLOSE cur_chk_tcs_settlement;
1987 p_process_flag := jai_constants.expected_error;
1988 p_process_message := 'Cannot reverse the receipt as the receipt confirmation record does not exists in the repository ';
1989 return;
1990 ELSE
1991 /*
1992 ||Check whether the TCS tax has been settled.
1993 */
1997 || Cannot allow receipt to be reversed.
1994 IF rec_cur_chk_tcs_settlement.settlement_id IS NOT NULL THEN
1995 /*
1996 || TCS taxes pertaining to the receipt have already been settled
1998 */
1999 /*commented by csahoo for bug# 6401388
2000 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2001 pv_log_msg => ' TCS On the original confirmed receipt has been settled. CAnnot reverse the receipt'
2002 );*/
2003 p_process_flag := jai_constants.expected_error;
2004 p_process_message := 'Cannot reverse the receipt as the tcs taxes pertaining to the receipt have been confirmed ';
2005 return;
2006 END IF;
2007 END IF;
2008 CLOSE cur_chk_tcs_settlement;
2009 END IF; /* Receipt reversal */
2010
2011 /*commented by csahoo for bug# 6401388
2012 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2013 pv_log_msg => '**************** END OF VALIDATE_RECEIPTS ****************'
2014 );
2015
2016 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
2017 END validate_receipts;
2018
2019 PROCEDURE process_invoices ( p_ract IN RA_CUSTOMER_TRX_ALL%ROWTYPE ,
2020 p_document_type VARCHAR2 ,
2021 p_process_flag OUT NOCOPY VARCHAR2 ,
2022 p_process_message OUT NOCOPY VARCHAR2
2023 )
2024 IS
2025
2026 ln_reg_id NUMBER;
2027 /*****
2028 ||Get the line invoice_total amount
2029 *****/
2030 CURSOR cur_get_inv_amt_date
2031 IS
2032 SELECT
2033 -- nvl(acctd_amount,0) total_invoice_amount , --deleted by eric for inclusive tax
2034 --NVL(jatl.total_amount,0) total_invoice_amount , --added by eric for inclusive tax on 26-dec,2007
2035 NVL(jatl.line_amount,0) total_invoice_amount , --added by Jia Li for inclusive tax on 2008-01-18
2036 rct.gl_date
2037 FROM
2038 ra_cust_trx_line_gl_dist_all rct
2039 , jai_ar_trx_lines jatl --added by eric for inclusive tax
2040 WHERE jatl.customer_trx_id = rct.customer_trx_id --added by eric for inclusive tax
2041 AND rct.customer_trx_id = p_ract.customer_trx_id
2042 AND rct.account_class = jai_constants.account_class_rec
2043 AND rct.latest_rec_flag = jai_constants.yes;
2044
2045 -- Added by Jia Li for inclusive taxes on 2008-01-18
2046 ----------------------------------------------------------------
2047 CURSOR cur_get_inv_exclu_amt
2048 IS
2049 SELECT
2050 SUM(a.func_tax_amount)
2051 FROM
2052 jai_ar_trx_tax_lines a
2053 , jai_cmn_taxes_all b
2054 WHERE link_to_cust_trx_line_id IN
2055 ( SELECT
2056 customer_trx_line_id
2057 FROM
2058 jai_ar_trx_lines
2059 WHERE customer_trx_id = p_ract.customer_trx_id )
2060 AND a.tax_id = b.tax_id
2061 AND NVL(b.inclusive_tax_flag,'N') = 'N';
2062 ln_total_inv_exclu_amt jai_ar_trx_tax_lines.func_tax_amount%TYPE;
2063 ------------------------------------------------------------------
2064
2065 /*****
2066 || Get the invoice line details
2067 *****/
2068 CURSOR cur_get_inv_line_det
2069 IS
2070 SELECT
2071 rctla.customer_trx_line_id ,
2072 jrct.organization_id ,
2073 rctla.extended_amount line_amount ,
2074 rctla.inventory_item_id
2075 FROM
2076 JAI_AR_TRXS jrct ,
2077 ra_customer_trx_lines_all rctla
2078 WHERE
2079 jrct.customer_trx_id = rctla.customer_trx_id
2080 AND jrct.customer_trx_id = p_ract.customer_trx_id
2081 AND rctla.customer_trx_id = rctla.customer_trx_id
2082 AND rctla.line_type = 'LINE';
2083
2084 /*****
2085 ||Get the Invoice tax details
2086 ******/
2087 CURSOR cur_get_inv_taxes (cp_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE )
2088 IS
2089 SELECT
2090 jrcttl.customer_trx_line_id ,
2091 jrcttl.tax_id ,
2092 jrcttl.tax_rate ,
2093 jtc.tax_type ,
2094 jrcttl.tax_amount ,
2095 jrcttl.func_tax_amount
2096 FROM
2097 JAI_AR_TRX_TAX_LINES jrcttl ,
2098 JAI_CMN_TAXES_ALL jtc ,
2099 jai_regime_tax_types_v jrttv
2100 WHERE
2101 jrcttl.link_to_cust_trx_line_id = cp_customer_trx_line_id
2102 AND jrcttl.tax_id = jtc.tax_id
2103 AND jrttv.tax_type = jtc.tax_type
2104 AND jrttv.regime_code = jai_constants.tcs_regime;
2105
2106 CURSOR cur_get_no_of_rows
2107 IS
2108 SELECT
2109 count(*) no_of_rows
2110 FROM
2111 JAI_AR_TRX_LINES
2112 WHERE
2113 customer_trx_id = p_ract.customer_trx_id ;
2114
2115 ln_row_count NUMBER(3) ;
2116 ln_line_counter NUMBER(3) := 0 ;
2117 ln_last_line_flag VARCHAR2(1) := jai_constants.no ;
2121 ln_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ;
2118 lv_process_flag VARCHAR2(2) ;
2119 lv_process_message VARCHAR2(2000) ;
2120 lv_document_type VARCHAR2(100) ;
2122 ln_apportion_factor NUMBER(3) ;
2123 lv_item_classification JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
2124 ln_total_inv_amount RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT%TYPE ;
2125 ld_document_date JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_DATE%TYPE ;
2126 BEGIN
2127 /*########################################################################################################
2128 || VARIABLES INITIALIZATION
2129 ########################################################################################################*/
2130
2131 lv_member_name := 'PROCESS_INVOICES';
2132 set_debug_context;
2133 /*commented by csahoo for bug# 6401388
2134 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
2135 , pn_reg_id => ln_reg_id
2136 );*/
2137
2138 lv_process_flag := jai_constants.successful ;
2139 lv_process_message := null ;
2140
2141 p_process_flag := lv_process_flag ;
2142 p_process_message := lv_process_message ;
2143 ln_apportion_factor := 1 ;
2144
2145 /******
2146 ||Check that the document is of type
2147 ||Confirmed cash receipt
2148 *******/
2149
2150 IF p_document_type IN ( jai_constants.trx_type_inv_comp ,
2151 jai_constants.trx_type_inv_incomp
2152 )
2153 THEN ---------A1
2154 /*########################################################################################################
2155 || DERIVE VALUES AND INSERT COMPLETED INVOICES INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
2156 ########################################################################################################*/
2157
2158 /*
2159 ||Get the receivable amount which is the would be the total invoice amount
2160 */
2161 OPEN cur_get_inv_amt_date ;
2162 FETCH cur_get_inv_amt_date INTO ln_total_inv_amount,ld_document_date;
2163 CLOSE cur_get_inv_amt_date;
2164
2165 -- Added by Jia Li for inclusive taxes on 2008-01-18
2166 ---------------------------------------------------------------------
2167 OPEN cur_get_inv_exclu_amt;
2168 FETCH cur_get_inv_exclu_amt INTO ln_total_inv_exclu_amt;
2169 CLOSE cur_get_inv_exclu_amt;
2170
2171 ln_total_inv_amount := ln_total_inv_amount + ln_total_inv_exclu_amt;
2172 ----------------------------------------------------------------------
2173
2174 IF p_document_type = jai_constants.trx_type_inv_incomp THEN
2175 /*
2176 ||Reverse the sign of the amount if the invoice is gettin incompleted.
2177 */
2178 ln_total_inv_amount := ln_total_inv_amount * (-1);
2179 END IF;
2180
2181 /*
2182 || Loop through each line and fetch its details
2183 || At this point of time it needs not be checked that a line has TCS type of taxes as it has been already validated in
2184 || the validate_process procedure
2185 */
2186 FOR rec_cur_get_inv_line_det IN cur_get_inv_line_det
2187 LOOP
2188 /*
2189 ||Get the value for the item classification pertaining to the IO and inventory item combination
2190 */
2191 jai_inv_items_pkg.jai_get_attrib (
2192 p_regime_code => jai_constants.tcs_regime ,
2193 p_organization_id => rec_cur_get_inv_line_det.organization_id ,
2194 p_inventory_item_id => rec_cur_get_inv_line_det.inventory_item_id ,
2195 p_attribute_code => jai_constants.rgm_attr_cd_itm_class ,
2196 p_attribute_value => lv_item_classification ,
2197 p_process_flag => lv_process_flag ,
2198 p_process_msg => lv_process_message
2199 );
2200
2201
2202 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2203 lv_process_flag = jai_constants.unexpected_error
2204 THEN
2205 /*
2206 || As Returned status is an error/not applicable hence:-
2207 || Set out variables p_process_flag and p_process_message accordingly
2208 */
2209 --call to debug package
2210 p_process_flag := lv_process_flag ;
2211 p_process_message := lv_process_message ;
2212 return;
2213 END IF; ---------A2
2214
2215 IF p_document_type = jai_constants.trx_type_inv_incomp THEN
2216 /*
2217 ||Reverse the sign of the amount if the invoice is getting incompleted.
2218 */
2219 rec_cur_get_inv_line_det.line_amount := rec_cur_get_inv_line_det.line_amount * (-1);
2220 END IF;
2221
2222 /*
2223 ||Get the sequence generated unique key for the transaction
2224 */
2225 OPEN cur_get_transaction_id ;
2229 /*
2226 FETCH cur_get_transaction_id INTO ln_transaction_id ;
2227 CLOSE cur_get_transaction_id ;
2228
2230 ||Insert into the repository.
2231 */
2232 insert_repository_references (
2233 p_regime_id => NULL ,
2234 p_transaction_id => ln_transaction_id ,
2235 p_source_ref_document_id => p_ract.customer_trx_id ,
2236 p_source_ref_document_type => p_document_type ,
2237 p_app_from_document_id => NULL ,
2238 p_app_from_document_type => NULL ,
2239 p_app_to_document_id => NULL ,
2240 p_app_to_document_type => NULL ,
2241 p_parent_transaction_id => NULL ,
2242 p_org_tan_no => NULL ,
2243 p_document_id => p_ract.customer_trx_id ,
2244 p_document_type => p_document_type ,
2245 p_document_line_id => rec_cur_get_inv_line_det.customer_trx_line_id ,
2246 p_document_date => ld_document_date ,
2247 p_table_name => jai_constants.ar_inv_lines_table ,
2248 p_line_amount => rec_cur_get_inv_line_det.line_amount * nvl( p_ract.exchange_rate , 1 ) ,
2249 p_document_amount => ln_total_inv_amount ,
2250 p_org_id => p_ract.org_id ,
2251 p_organization_id => rec_cur_get_inv_line_det.organization_id ,
2252 p_party_id => nvl(p_ract.bill_to_customer_id,p_ract.ship_to_customer_id) ,
2253 p_party_site_id => nvl(p_ract.bill_to_site_use_id,p_ract.ship_to_site_use_id) ,
2254 p_item_classification => lv_item_classification ,
2255 p_trx_ref_id => ln_trx_ref_id ,
2256 p_process_flag => lv_process_flag ,
2257 p_process_message => lv_process_message
2258 );
2259
2260 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2261 lv_process_flag = jai_constants.unexpected_error
2262 THEN
2263 /*
2264 || As Returned status is an error/not applicable hence:-
2265 || Set out variables p_process_flag and p_process_message accordingly
2266 */
2267 --call to debug package
2268 p_process_flag := lv_process_flag ;
2269 p_process_message := lv_process_message ;
2270 return;
2271 END IF; ---------A2
2272
2273 /*########################################################################################################
2274 || INSERT INVOICE TAXES INTO JAI_RGM_TAXES TABLE ---- PART -2
2275 ########################################################################################################*/
2276
2277 IF p_document_type = jai_constants.trx_type_inv_incomp THEN
2278 /*
2279 ||Reverse the sign of the amount if the invoice is getting incompleted.
2280 */
2281 ln_apportion_factor := -1;
2282 END IF;
2283 /*
2284 || Copy the taxes from the invoice/DM transaction to the TCS tax repository
2285 */
2286 copy_taxes_from_source ( p_source_document_type => p_document_type ,
2287 p_source_document_id => p_ract.customer_trx_id ,
2288 p_source_document_line_id => rec_cur_get_inv_line_det.customer_trx_line_id ,
2289 p_apportion_factor => ln_apportion_factor ,
2290 p_trx_ref_id => ln_trx_ref_id ,
2294
2291 p_process_flag => lv_process_flag ,
2292 p_process_message => lv_process_message
2293 );
2295 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2296 lv_process_flag = jai_constants.unexpected_error OR
2297 lv_process_flag = jai_constants.not_applicable
2298 THEN
2299 /*
2300 || As Returned status is an error/not applicable hence:-
2301 || Set out variables p_process_flag and p_process_message accordingly
2302 */
2303 --call to debug package
2304 p_process_flag := lv_process_flag ;
2305 p_process_message := lv_process_message ;
2306 return;
2307 END IF; ---------A2
2308
2309 END LOOP; /*End loop for invoice lines */
2310
2311 END IF; /* Invoice completion / Incompletion*/
2312
2313 /*commented by csahoo for bug# 6401388
2314 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2315 pv_log_msg => '**************** END OF PROCESS_INVOICES ****************'
2316 );
2317 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
2318 END process_invoices;
2319
2320
2321 procedure process_receipts ( p_acra AR_CASH_RECEIPTS_ALL%ROWTYPE ,
2322 p_document_type VARCHAR2 ,
2323 p_process_flag OUT NOCOPY VARCHAR2 ,
2324 p_process_message OUT NOCOPY VARCHAR2
2325 )
2326 IS
2327 ln_reg_id NUMBER;
2328 /*****
2329 || Get the details of the cash_receipts
2330 *****/
2331 CURSOR cur_get_cr_details ( cp_cash_receipt_id JAI_AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE )
2332 IS
2333 SELECT
2334 jcra.customer_id ,
2335 jcra.organization_id ,
2336 jcra.gl_date ,
2337 jcra.item_classification
2338 FROM
2339 jai_ar_cash_receipts_all jcra
2340 WHERE
2341 jcra.cash_receipt_id = cp_cash_receipt_id ;
2342
2343 /*****
2344 ||Get the receipt tax details
2345 *****/
2346 CURSOR cur_get_rcpt_taxes
2347 IS
2348 SELECT
2349 jdt.tax_id ,
2350 jdt.tax_rate ,
2351 jtc.tax_type ,
2352 jdt.tax_amt ,
2353 jdt.func_tax_amt
2354 FROM
2355 jai_cmn_document_taxes jdt ,
2356 JAI_CMN_TAXES_ALL jtc ,
2357 jai_regime_tax_types_v jrttv
2358 WHERE
2359 jdt.tax_id = jtc.tax_id
2360 AND jtc.tax_type = jrttv.tax_type
2361 AND jdt.source_doc_id = p_acra.cash_receipt_id
2362 AND jrttv.regime_code = jai_constants.tcs_regime
2363 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
2364
2365 /*****
2366 || Get all the applications for which Cash Receipt has got TCS type of taxes
2367 || and the corresponding invoice also have got TCS type of tax.
2368 || Unapplications needs not be considered as there applications also would not have gone
2369 *****/
2370 CURSOR cur_get_ar_rec_app_all (cp_cash_receipt_id AR_RECEIVABLE_APPLICATIONS_ALL.CASH_RECEIPT_ID%TYPE)
2371 IS
2372 SELECT
2373 ra.*
2374 FROM
2375 ar_receivable_applications_all ra ,
2376 jai_ar_cash_receipts_all jcra
2377 WHERE
2378 ra.cash_receipt_id = jcra.cash_receipt_id
2379 AND ra.cash_receipt_id = cp_cash_receipt_id
2380 AND ra.status = 'APP'
2381 AND ra.application_type = 'CASH'
2382 AND ra.display = jai_constants.yes
2383 AND jcra.confirm_flag = jai_constants.yes
2384 AND exists ( SELECT /* TCS type of taxes exist for the receipt */
2385 1
2386 FROM
2387 jai_cmn_document_taxes jdt ,
2388 jai_regime_tax_types_v jrttv
2389 WHERE
2390 jdt.source_doc_id = jcra.cash_receipt_id
2391 AND jdt.source_table_name = jai_constants.jai_cash_rcpts /* 'JAI_AR_CASH_RECEIPTS_ALL' */
2392 AND jdt.tax_type = jrttv.tax_type
2393 AND jrttv.regime_code = jai_constants.tcs_regime
2394 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash --added by eric for a bug
2395 )
2396 AND exists ( SELECT /* TCS type of taxes exist for the corresponding Invoice */
2397 1
2398 FROM
2399 JAI_AR_TRX_LINES jrctl ,
2400 JAI_AR_TRX_TAX_LINES jrcttl,
2401 JAI_CMN_TAXES_ALL jtc ,
2402 jai_regime_tax_types_v jrttv
2403 WHERE
2404 jrctl.customer_trx_id = ra.applied_customer_trx_id
2405 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
2409 );
2406 AND jrcttl.tax_id = jtc.tax_id
2407 AND jtc.tax_type = jrttv.tax_type
2408 AND jrttv.regime_code = jai_constants.tcs_regime
2410
2411 /********
2412 || Get the details of the source receipt
2413 || from the repository
2414 ********/
2415 CURSOR cur_copy_src_rcpt (cp_source_document_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE)
2416 IS
2417 SELECT
2418 *
2419 FROM
2420 jai_rgm_refs_all
2421 WHERE
2422 source_document_id = cp_source_document_id;
2423
2424 /********
2425 || Get the details of the source receipt taxes
2426 || from the repository
2427 ********/
2428 CURSOR cur_copy_tax_rcpt_rev ( cp_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE )
2429 IS
2430 SELECT
2431 *
2432 FROM
2433 jai_rgm_taxes
2434 WHERE
2435 trx_ref_id = cp_trx_ref_id;
2436
2437 p_araa AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE ;
2438 rec_cur_get_cr_details CUR_GET_CR_DETAILS%ROWTYPE ;
2439 rec_cur_copy_src_rcpt CUR_COPY_SRC_RCPT%ROWTYPE ;
2440 ln_rcpt_amount JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_AMT%TYPE ;
2441 lv_process_flag VARCHAR2(2) ;
2442 lv_process_message VARCHAR2(2000) ;
2443 lv_document_type VARCHAR2(100) ;
2444 ln_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ;
2445 ln_apportion_factor NUMBER(3) ;
2446 ln_local_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE ;
2447 ln_parent_transaction_id JAI_RGM_REFS_ALL.PARENT_TRANSACTION_ID%TYPE ;
2448
2449 BEGIN
2450
2451 /*########################################################################################################
2452 || VARIABLES INITIALIZATION
2453 ########################################################################################################*/
2454 lv_member_name := 'PROCESS_RECEIPTS';
2455 set_debug_context;
2456 /*commented by csahoo for bug# 6401388
2457 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
2458 , pn_reg_id => ln_reg_id
2459 );*/
2460
2461 lv_process_flag := jai_constants.successful ;
2462 lv_process_message := null ;
2463
2464 p_process_flag := lv_process_flag ;
2465 p_process_message := lv_process_message ;
2466
2467 ln_rcpt_amount := p_acra.amount * nvl(p_acra.exchange_rate ,1 ) ;
2468 ln_apportion_factor := 1 ;
2469
2470 /******
2471 ||Check that the document is of type
2472 ||Confirmed cash receipt
2473
2474 *******/
2475
2476 IF p_document_type = jai_constants.ar_cash_tax_confirmed THEN ---------C1
2477 /*commented by csahoo for bug# 6401388
2478 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2479 pv_log_msg => ' Confirmation processing for the receipts with the following details'||fnd_global.local_chr(10)
2480 ||', p_acra.receipt_number -> '||p_acra.receipt_number ||fnd_global.local_chr(10)
2481 ||', p_acra.cash_receipt_id -> '||p_acra.cash_receipt_id ||fnd_global.local_chr(10)
2482 ||', p_acra.amount -> '||p_acra.amount ||fnd_global.local_chr(10)
2483 ||', p_acra.type -> '||p_acra.type
2484 );*/
2485 OPEN cur_get_cr_details ( cp_cash_receipt_id => p_acra.cash_receipt_id );
2486 FETCH cur_get_cr_details INTO rec_cur_get_cr_details;
2487 CLOSE cur_get_cr_details;
2488
2489 /*########################################################################################################
2490 || INSERT CASH RECEIPTS INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
2491 ########################################################################################################*/
2492
2493 /*
2494 ||Get the sequence generated unique key for the transaction
2495 */
2496 OPEN cur_get_transaction_id ;
2497 FETCH cur_get_transaction_id INTO ln_transaction_id ;
2498 CLOSE cur_get_transaction_id ;
2499
2500 ln_local_transaction_id := ln_transaction_id;
2501
2502 /*commented by csahoo for bug# 6401388
2503 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2504 pv_log_msg => ' Before call to insert_repository_references '
2505 );*/
2506
2507 insert_repository_references (
2508 p_regime_id => NULL ,
2509 p_transaction_id => ln_transaction_id ,
2510 p_source_ref_document_id => p_acra.cash_receipt_id ,
2511 p_source_ref_document_type => p_document_type ,
2512 p_app_from_document_id => NULL ,
2516 p_parent_transaction_id => NULL ,
2513 p_app_from_document_type => NULL ,
2514 p_app_to_document_id => NULL ,
2515 p_app_to_document_type => NULL ,
2517 p_org_tan_no => NULL ,
2518 p_document_id => p_acra.cash_receipt_id ,
2519 p_document_type => p_document_type ,
2520 p_document_line_id => p_acra.cash_receipt_id ,
2521 p_document_date => rec_cur_get_cr_details.gl_date ,
2522 p_table_name => jai_constants.jai_cash_rcpts ,
2523 p_line_amount => ln_rcpt_amount ,
2524 p_document_amount => ln_rcpt_amount ,
2525 p_org_id => p_acra.org_id ,
2526 p_organization_id => rec_cur_get_cr_details.organization_id ,
2527 p_party_id => rec_cur_get_cr_details.customer_id ,
2528 p_party_site_id => p_acra.customer_site_use_id ,
2529 p_item_classification => rec_cur_get_cr_details.item_classification ,
2530 p_trx_ref_id => ln_trx_ref_id ,
2531 p_process_flag => lv_process_flag ,
2532 p_process_message => lv_process_message
2533 );
2534
2535 /*commented by csahoo for bug# 6401388
2536 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2537 pv_log_msg => ' Return from insert_repository_references '
2538 );*/
2539
2540 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2541 lv_process_flag = jai_constants.unexpected_error OR
2542 lv_process_flag = jai_constants.not_applicable
2543 THEN
2544 /*
2545 || As Returned status is an error/not applicable hence:-
2546 || Set out variables p_process_flag and p_process_message accordingly
2547 */
2548 --call to debug package
2549 /*commented by csahoo for bug# 6401388
2550 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2551 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
2552 );*/
2553
2554 p_process_flag := lv_process_flag ;
2555 p_process_message := lv_process_message ;
2556 return;
2557 END IF; ---------A2
2558
2559
2560 /*########################################################################################################
2561 || INSERT CASH RECEIPTS INTO JAI_RGM_TAXES TABLE ---- PART -2
2562 ########################################################################################################*/
2563
2564 /*
2565 || Copy the taxes from the invoice/DM transaction to the TCS tax repository
2566 */
2567 /*commented by csahoo for bug# 6401388
2568 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2569 pv_log_msg => ' Before call to copy_taxes_from_source '
2570 );*/
2571
2572 copy_taxes_from_source ( p_source_document_type => p_document_type ,
2573 p_source_document_id => p_acra.cash_receipt_id ,
2574 p_apportion_factor => ln_apportion_factor ,
2575 p_trx_ref_id => ln_trx_ref_id ,
2576 p_process_flag => lv_process_flag ,
2577 p_process_message => lv_process_message
2578 );
2579 /*commented by csahoo for bug# 6401388
2580 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2581 pv_log_msg => ' Return from copy_taxes_from_source '
2582 );*/
2583 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2584 lv_process_flag = jai_constants.unexpected_error OR
2585 lv_process_flag = jai_constants.not_applicable
2586 THEN
2587 /*
2588 || As Returned status is an error/not applicable hence:-
2589 || Set out variables p_process_flag and p_process_message accordingly
2590 */
2591 --call to debug package
2592 /*commented by csahoo for bug# 6401388
2593 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2594 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
2598 p_process_message := lv_process_message ;
2595 );*/
2596
2597 p_process_flag := lv_process_flag ;
2599 return;
2600 END IF;
2601
2602 /*########################################################################################################
2603 || PROCESS AR CASH RECEIPT APPLICATIONS/UNAPPLICATIONS ---- PART -3
2604 ########################################################################################################*/
2605 /*commented by csahoo for bug# 6401388
2606 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2607 pv_log_msg => ' Cash receipt application processing '
2608 );*/
2609 /******
2610 ||Fetch all the latest applications related to the cash receipt
2611 *******/
2612 FOR rec_get_ar_rec_app_all IN cur_get_ar_rec_app_all ( cp_cash_receipt_id => p_acra.cash_receipt_id )
2613 LOOP
2614 p_araa := rec_get_ar_rec_app_all ;
2615 /*********
2616 || Consider only the latest applications
2617 **********/
2618
2619 /*******************************************
2620 || Call the procedure to process
2621 || cash receipt applications/unapplications
2622 *******************************************/
2623 /*commented by csahoo for bug# 6401388
2624 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2625 pv_log_msg => ' before call to jai_ar_tcs_rep_pkg.process_transactions '
2626 );*/
2627 jai_ar_tcs_rep_pkg.process_transactions ( p_araa => p_araa ,
2628 p_event => p_araa.application_type ,
2629 -- p_called_from => lv_document_type ,
2630 p_process_flag => lv_process_flag ,
2631 p_process_message => lv_process_message
2632 );
2633 /*commented by csahoo for bug# 6401388
2634 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2635 pv_log_msg => ' Return from jai_ar_tcs_rep_pkg.process_transactions '
2636 );*/
2637 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2638 lv_process_flag = jai_constants.unexpected_error OR
2639 lv_process_flag = jai_constants.not_applicable
2640 THEN
2641 /*
2642 || As Returned status is an error/not applicable hence:-
2643 || Set out variables p_process_flag and p_process_message accordingly
2644 */
2645 --call to debug package
2646 /*commented by csahoo for bug# 6401388
2647 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2648 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
2649 );*/
2650
2651 p_process_flag := lv_process_flag ;
2652 p_process_message := lv_process_message ;
2653 return;
2654 END IF; ---------A2
2655
2656
2657 END LOOP;
2658
2659 /*
2660 ||Reset the receipt transaction ln_transaction_id from ln_local_transaction_id
2661 */
2662 ln_transaction_id := ln_local_transaction_id ;
2663
2664 /*########################################################################################################
2665 || PROCESS AR CASH RECEIPT REVERSALS ---- PART -4
2666 ########################################################################################################*/
2667
2668 ELSIF p_document_type = jai_constants.trx_type_rct_rvs THEN ---------C1
2669 /*commented by csahoo for bug# 6401388
2670 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2671 pv_log_msg => ' Start of receipt reversal processing '
2672 );*/
2673 OPEN cur_get_parent_transaction ( cp_source_document_id => p_acra.cash_receipt_id ,
2674 cp_source_document_type => jai_constants.ar_cash_tax_confirmed
2675 ) ;
2676
2677 FETCH cur_get_parent_transaction INTO ln_parent_transaction_id;
2678 /*
2679 || Check that the source receipt confirmation record has been found in the TCS repository.
2680 || If not found then receipt reversal also need not hit the repository.
2681 */
2682 IF CUR_GET_PARENT_TRANSACTION%FOUND THEN
2683 /*commented by csahoo for bug# 6401388
2684 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2685 pv_log_msg => ' Parent receipt confirmation record found parent_transaction_id -> '||ln_parent_transaction_id
2686 );*/
2687
2688 copy_references ( p_parent_transaction_id => ln_parent_transaction_id ,
2689 p_new_document_id => p_acra.cash_receipt_id ,
2690 p_new_document_type => p_document_type ,
2691 p_new_document_date => p_acra.reversal_date ,
2692 p_apportion_factor => -1 ,/* As reversal cannot be partial */
2693 p_process_flag => lv_process_flag ,
2697 lv_process_flag = jai_constants.unexpected_error OR
2694 p_process_message => lv_process_message
2695 );
2696 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2698 lv_process_flag = jai_constants.not_applicable
2699 THEN
2700 /*
2701 || As Returned status is an error/not applicable hence:-
2702 || Set out variables p_process_flag and p_process_message accordingly
2703 */
2704 CLOSE cur_get_parent_transaction ;
2705 /*commented by csahoo for bug# 6401388
2706 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2707 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
2708 );*/
2709
2710 p_process_flag := lv_process_flag ;
2711 p_process_message := lv_process_message ;
2712 return;
2713 END IF; ---------A2
2714 END IF;
2715 CLOSE cur_get_parent_transaction ;
2716 END IF;
2717 /*commented by csahoo for bug# 6401388
2718 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2719 pv_log_msg => '**************** END OF PROCESS_RECEIPTS ****************'
2720 );
2721
2722 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
2723 END process_receipts;
2724
2725
2726 PROCEDURE process_applications ( p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE ,
2727 p_document_type IN VARCHAR2 ,
2728 p_item_classification IN JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ,
2729 p_process_flag OUT NOCOPY VARCHAR2 ,
2730 p_process_message OUT NOCOPY VARCHAR2
2731 )
2732 IS
2733 ln_reg_id NUMBER;
2734 /*
2735 ||Get the application and applied to document details
2736 */
2737 CURSOR cur_get_app_to_det (cp_applied_customer_trx_id AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID%TYPE )
2738 IS
2739 SELECT
2740 trx_types.type app_to_doc_type ,
2741 --nvl(gl_dist.acctd_amount,0) app_to_doc_amt ,--Deleted by eric for inclusive tax
2742 --NVL(jrctl.total_amount,0) app_to_doc_amt ,--Added by eric for inclusive tax for 26-Dec,2007
2743 NVL(jrctl.line_amount,0) app_to_doc_amt , --Added by Jia Li for inclusive tax for 2008-01-18
2744 rcta.org_id app_to_org_id ,
2745 jrct.organization_id app_to_organization_id ,
2746 nvl(bill_to_customer_id, ship_to_customer_id) app_to_customer_id , -- Bug 6132484
2747 nvl(bill_to_site_use_id,ship_to_site_use_id) app_to_customer_site_use_id -- Bug 6132484
2748 FROM
2749 ra_customer_trx_all rcta ,
2750 ra_cust_trx_types_all trx_types ,
2751 ra_cust_trx_line_gl_dist_all gl_dist ,
2752 JAI_AR_TRXS jrct ,
2753 JAI_AR_TRX_LINES jrctl ,
2754 JAI_AR_TRX_TAX_LINES jrcttl ,
2755 JAI_CMN_TAXES_ALL jtc ,
2756 jai_regime_tax_types_v jrttv
2757 WHERE
2758 rcta.customer_trx_id = cp_applied_customer_trx_id
2759 AND rcta.complete_flag = jai_constants.yes
2760 AND rcta.customer_trx_id = gl_dist.customer_trx_id
2761 AND gl_dist.account_class = jai_constants.account_class_rec
2762 AND gl_dist.latest_rec_flag = jai_constants.yes
2763 AND trx_types.cust_trx_type_id = rcta.cust_trx_type_id
2764 AND rcta.customer_trx_id = jrct.customer_trx_id
2765 AND jrct.customer_trx_id = jrctl.customer_trx_id
2766 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
2767 AND jrcttl.tax_id = jtc.tax_id
2768 AND jtc.tax_type = jrttv.tax_type
2769 AND jrttv.regime_code = jai_constants.tcs_regime
2770 AND trx_types.type IN ( jai_constants.ar_invoice_type_inv , /* Applied to doc has to be either a invoice or DM or CM */
2771 jai_constants.ar_invoice_type_cm ,
2772 jai_constants.ar_doc_type_dm
2773 );
2774
2775 -- Added by Jia Li for inclusive taxes on 2008-01-18
2776 ----------------------------------------------------------------
2777 CURSOR cur_get_exclu_amt(cp_customer_trx_id RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID%TYPE )
2778 IS
2779 SELECT
2780 SUM(a.func_tax_amount)
2781 FROM
2782 jai_ar_trx_tax_lines a
2783 , jai_cmn_taxes_all b
2787 FROM
2784 WHERE link_to_cust_trx_line_id IN
2785 ( SELECT
2786 customer_trx_line_id
2788 jai_ar_trx_lines
2789 WHERE customer_trx_id = cp_customer_trx_id )
2790 AND a.tax_id = b.tax_id
2791 AND NVL(b.inclusive_tax_flag,'N') = 'N';
2792 ln_total_exclu_amt jai_ar_trx_tax_lines.func_tax_amount%TYPE;
2793 ------------------------------------------------------------------
2794
2795 /*
2796 ||Get the type of the document
2797 */
2798 CURSOR cur_get_doc_type ( cp_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE )
2799 IS
2800 SELECT
2801 trx_types.type
2802 FROM
2803 ra_customer_trx_all ract,
2804 ra_cust_trx_types_all trx_types
2805 WHERE
2806 ract.cust_trx_type_id = trx_types.cust_trx_type_id
2807 AND ract.customer_trx_id = cp_customer_trx_id;
2808
2809 /*
2810 ||Get the applied to document cash receipt details
2811 */
2812 CURSOR cur_get_cr_details ( cp_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE )
2813 IS
2814 SELECT
2815 nvl(acra.amount,0) * nvl(acra.exchange_rate , 1 ) app_fr_doc_amt ,
2816 nvl(acra.exchange_rate,1) exchange_rate ,
2817 arps.gl_date app_fr_doc_date ,
2818 jcra.organization_id app_fr_organization_id ,
2819 acra.pay_from_customer ,
2820 acra.customer_site_use_id ,
2821 acra.org_id
2822 FROM
2823 ar_cash_receipts_all acra ,
2824 ar_payment_schedules_all arps ,
2825 jai_ar_cash_receipts_all jcra ,
2826 jai_cmn_document_taxes jdt ,
2827 jai_regime_tax_types_v jrttv
2828 WHERE
2829 acra.cash_receipt_id = arps.cash_receipt_id
2830 AND acra.cash_receipt_id = jcra.cash_receipt_id
2831 AND jcra.cash_receipt_id = jdt.source_doc_id
2832 AND jdt.tax_type = jrttv.tax_type
2833 AND acra.cash_receipt_id = cp_cash_receipt_id
2834 AND jrttv.regime_code = jai_constants.tcs_regime
2835 AND jcra.confirm_flag = jai_constants.yes
2836 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
2837 /*
2838 ||Get the applied to document CM details
2839 */
2840 CURSOR cur_get_cm_details (cp_customer_trx_id RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID%TYPE)
2841 IS
2842 SELECT
2843 --nvl(gl_dist.acctd_amount,0) app_fr_doc_amt ,--Deleted by eric for inclusive tax
2844 --NVL(jrctl.total_amount,0) app_fr_doc_amt ,--Added by eric for Inclusisve Tax on 26-dec,2007
2845 NVL(jrctl.line_amount,0) app_fr_doc_amt ,--Added by Jia Li for Inclusisve Tax on 2008-01-18
2846 nvl(rcta.exchange_rate,1) exchange_rate ,
2847 gl_dist.gl_date app_fr_doc_date ,
2848 rcta.org_id app_to_org_id ,
2849 jrct.organization_id app_to_organization_id ,
2850 nvl( bill_to_customer_id , ship_to_customer_id ) app_to_customer_id ,
2851 nvl( bill_to_site_use_id , ship_to_site_use_id ) app_to_customer_site_use_id
2852 FROM
2853 ra_customer_trx_all rcta ,
2854 ra_cust_trx_types_all trx_types ,
2855 ra_cust_trx_line_gl_dist_all gl_dist ,
2856 JAI_AR_TRXS jrct ,
2857 JAI_AR_TRX_LINES jrctl ,
2858 JAI_AR_TRX_TAX_LINES jrcttl ,
2859 JAI_CMN_TAXES_ALL jtc ,
2860 jai_regime_tax_types_v jrttv
2861 WHERE
2862 rcta.complete_flag = jai_constants.yes
2863 AND trx_types.cust_trx_type_id = rcta.cust_trx_type_id
2864 AND trx_types.type = jai_constants.ar_invoice_type_cm
2865 AND rcta.customer_trx_id = gl_dist.customer_trx_id
2866 AND gl_dist.account_class = jai_constants.account_class_rec
2867 AND gl_dist.latest_rec_flag = jai_constants.yes
2868 AND rcta.customer_trx_id = jrct.customer_trx_id
2869 AND jrct.customer_trx_id = jrctl.customer_trx_id
2870 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
2871 AND jrcttl.tax_id = jtc.tax_id
2872 AND jtc.tax_type = jrttv.tax_type
2873 AND rcta.customer_trx_id = cp_customer_trx_id
2874 AND jrttv.regime_code = jai_constants.tcs_regime;
2875
2876 ln_app_fr_doc_amt NUMBER ;
2877 ld_app_fr_doc_date DATE ;
2878 ln_app_fr_doc_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ;
2879 ln_app_fr_doc_type JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ;
2880 ln_app_fr_doc_table JAI_RGM_REFS_ALL.SOURCE_TABLE_NAME%TYPE ;
2884
2881 ln_fr_organization_id JAI_RGM_REFS_ALL.ORGANIZATION_ID%TYPE ;
2882 ln_fr_party_id JAI_RGM_REFS_ALL.PARTY_ID%TYPE ;
2883 ln_fr_party_site_id JAI_RGM_REFS_ALL.PARTY_SITE_ID%TYPE ;
2885 ln_app_to_doc_amt NUMBER ;
2886 ld_app_to_doc_date DATE ;
2887 ln_app_to_doc_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ;
2888 ln_app_to_doc_type JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ;
2889 ln_app_to_doc_table JAI_RGM_REFS_ALL.SOURCE_TABLE_NAME%TYPE ;
2890 ln_to_organization_id JAI_RGM_REFS_ALL.ORGANIZATION_ID%TYPE ;
2891 ln_to_party_id JAI_RGM_REFS_ALL.PARTY_ID%TYPE ;
2892 ln_to_party_site_id JAI_RGM_REFS_ALL.PARTY_SITE_ID%TYPE ;
2893
2894
2895 ln_app_amount NUMBER ;
2896 ln_apportion_factor NUMBER ;
2897 ln_app_ref_doc_id JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_ID%TYPE ;
2898 ln_app_ref_doc_type JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_TYPE%TYPE ;
2899 ln_app_ref_doc_table JAI_RGM_REFS_ALL.SOURCE_TABLE_NAME%TYPE ;
2900 ld_source_document_date JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_DATE%TYPE ;
2901 ln_parent_transaction_id JAI_RGM_REFS_ALL.parent_transaction_id%TYPE ;
2902 ln_organization_id JAI_RGM_REFS_ALL.ORGANIZATION_ID%TYPE ;
2903 ln_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ;
2904 ln_party_id JAI_RGM_REFS_ALL.PARTY_ID%TYPE ;
2905 ln_party_site_id JAI_RGM_REFS_ALL.PARTY_SITE_ID%TYPE ;
2906 ln_exchange_rate NUMBER ;
2907 rec_cur_get_app_to_det CUR_GET_APP_TO_DET%ROWTYPE ;
2908 rec_cur_get_cr_details CUR_GET_CR_DETAILS%ROWTYPE ;
2909 rec_cur_get_cm_details CUR_GET_CM_DETAILS%ROWTYPE ;
2910 lv_process_flag VARCHAR2(2) ;
2911 lv_process_message VARCHAR2(4000) ;
2912
2913 BEGIN
2914 /*########################################################################################################
2915 || Initialize Variables ---- PART -1
2916 ########################################################################################################*/
2917 lv_member_name := 'PROCESS_APPLICATIONS';
2918 set_debug_context;
2919 /*commented by csahoo for bug# 6401388
2920 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
2921 pn_reg_id => ln_reg_id
2922 );*/
2923
2924 /*commented by csahoo for bug# 6401388
2925 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2926 pv_log_msg => ' Processing the APPLICATION record ' ||fnd_global.local_chr(10)
2927 ||', receivable_application_id -> ' ||p_araa.receivable_application_id ||fnd_global.local_chr(10)
2928 ||', application_type -> ' ||p_araa.application_type ||fnd_global.local_chr(10)
2929 ||', status -> ' ||p_araa.status ||fnd_global.local_chr(10)
2930 ||', display -> ' ||p_araa.display ||fnd_global.local_chr(10)
2931 ||', cash_receipt_id -> ' ||p_araa.cash_receipt_id ||fnd_global.local_chr(10)
2932 ||', customer_trx_id -> ' ||p_araa.customer_trx_id ||fnd_global.local_chr(10)
2933 ||', applied_customer_trx_id -> ' ||p_araa.applied_customer_trx_id ||fnd_global.local_chr(10)
2934 ||', amount_applied -> ' ||p_araa.amount_applied ||fnd_global.local_chr(10)
2935 ||', gl_date -> ' ||p_araa.gl_date ||fnd_global.local_chr(10)
2936 ||', org_id -> ' ||p_araa.org_id
2937 );*/
2938 lv_process_flag := jai_constants.successful ;
2939 lv_process_message := null ;
2940 p_process_flag := lv_process_flag ;
2941 p_process_message := lv_process_message ;
2942 ln_app_fr_doc_amt := NULL ;
2943 ld_app_fr_doc_date := NULL ;
2944 ln_parent_transaction_id := NULL ;
2945
2946 /*########################################################################################################
2947 || GET APPLICATION and APPLIED TO DOCUMENT DETAILS ---- PART -2
2948 ########################################################################################################*/
2949 OPEN cur_get_app_to_det (cp_applied_customer_trx_id => p_araa.applied_customer_trx_id ) ;
2950 FETCH cur_get_app_to_det INTO rec_cur_get_app_to_det ;
2954 ---------------------------------------------------------------------
2951 CLOSE cur_get_app_to_det ;
2952
2953 -- Added by Jia Li for inclusive taxes on 2008-01-18
2955 OPEN cur_get_exclu_amt (cp_customer_trx_id => p_araa.applied_customer_trx_id );
2956 FETCH cur_get_exclu_amt INTO ln_total_exclu_amt;
2957 CLOSE cur_get_exclu_amt;
2958 ----------------------------------------------------------------------
2959
2960 ln_app_to_doc_amt := rec_cur_get_app_to_det.app_to_doc_amt + ln_total_exclu_amt ; -- Modified by Jia Li for inclusive tax on 2008-01-18
2961 ld_app_to_doc_date := p_araa.gl_date ;
2962 ln_app_to_doc_id := p_araa.applied_customer_trx_id ;
2963
2964 /*
2965 || Derive the to Document type from the application information
2966 || APPLIED TO DOCUMENT BEING CASH NOT HANDLED CURRENTLY - STOP AND VALIDATION TIME
2967 */
2968 OPEN cur_get_doc_type ( cp_customer_trx_id => p_araa.applied_customer_trx_id ) ;
2969 FETCH cur_get_doc_type INTO ln_app_to_doc_type ;
2970 CLOSE cur_get_doc_type ;
2971
2972 ln_app_to_doc_table := jai_constants.ar_inv_lines_table ; /* table JAI_AR_TRX_LINES */
2973 ln_app_to_doc_type := rec_cur_get_app_to_det.app_to_doc_type ;
2974 ln_to_organization_id := rec_cur_get_app_to_det.app_to_organization_id ;
2975 ln_to_party_id := rec_cur_get_app_to_det.app_to_customer_id ;
2976 ln_to_party_site_id := rec_cur_get_app_to_det.app_to_customer_site_use_id ;
2977
2978 /*########################################################################################################
2979 || VALIDATE AND GET APPLIED FROM DOCUMENT DETAILS ---- PART -3
2980 ########################################################################################################*/
2981 IF p_document_type = jai_constants.trx_type_rct_app THEN -----------A1
2982 /*
2983 ||Applied from document type is Cash Receipt Application
2984 */
2985 OPEN cur_get_cr_details ( cp_cash_receipt_id => p_araa.cash_receipt_id ) ;
2986 FETCH cur_get_cr_details INTO rec_cur_get_cr_details ;
2987 CLOSE cur_get_cr_details ;
2988
2989 ld_app_fr_doc_date := rec_cur_get_cr_details.app_fr_doc_date ;
2990 ln_app_fr_doc_amt := rec_cur_get_cr_details.app_fr_doc_amt ;
2991 ln_exchange_rate := rec_cur_get_cr_details.exchange_rate ;
2992
2993 ln_app_fr_doc_id := p_araa.cash_receipt_id ;
2994 ln_app_fr_doc_type := jai_constants.ar_cash_tax_confirmed ; /* Receipt confirmation */
2995 ln_app_fr_doc_table := jai_constants.jai_cash_rcpts ; /* table JAI_AR_CASH_RECEIPTS_ALL */
2996
2997 ln_fr_organization_id := rec_cur_get_cr_details.app_fr_organization_id ;
2998 ln_fr_party_id := rec_cur_get_cr_details.pay_from_customer ;
2999 ln_fr_party_site_id := rec_cur_get_cr_details.customer_site_use_id ;
3000
3001 ELSIF p_document_type = jai_constants.trx_type_cm_app THEN
3002
3003 OPEN cur_get_cm_details ( cp_customer_trx_id => p_araa.customer_trx_id ) ;
3004 FETCH cur_get_cm_details INTO rec_cur_get_cm_details ;
3005 CLOSE cur_get_cm_details ;
3006
3007 -- Added by Jia Li for inclusive taxes on 2008-01-18
3008 ---------------------------------------------------------------------
3009 OPEN cur_get_exclu_amt (cp_customer_trx_id => p_araa.customer_trx_id );
3010 FETCH cur_get_exclu_amt INTO ln_total_exclu_amt;
3011 CLOSE cur_get_exclu_amt;
3012 ----------------------------------------------------------------------
3013 ld_app_fr_doc_date := rec_cur_get_cm_details.app_fr_doc_date ;
3014 ln_app_fr_doc_amt := rec_cur_get_cm_details.app_fr_doc_amt + ln_total_exclu_amt;-- Modified by Jia Li for inclusive tax on 2008-01-18
3015 ln_exchange_rate := rec_cur_get_cm_details.exchange_rate ;
3016
3017 ln_app_fr_doc_id := p_araa.customer_trx_id ;
3018 ln_app_fr_doc_type := jai_constants.ar_invoice_type_cm ; /* 'CM' */
3019 ln_app_fr_doc_table := jai_constants.ar_inv_lines_table ; /* table JAI_AR_CASH_RECEIPTS_ALL */
3020
3021 ln_fr_organization_id := rec_cur_get_cm_details.app_to_organization_id ;
3022 ln_fr_party_id := rec_cur_get_cm_details.app_to_customer_id ;
3023 ln_fr_party_site_id := rec_cur_get_cm_details.app_to_customer_site_use_id ;
3024
3025 END IF;
3026
3027 ln_app_amount := nvl(p_araa.amount_applied,0) * ln_exchange_rate ;
3028
3029
3030 /*commented by csahoo for bug# 6401388
3031 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3032 pv_log_msg => ' Values for the Internal FROm and TO variables are ' ||fnd_global.local_chr(10)
3033 ||', ln_app_to_doc_id -> ' ||ln_app_to_doc_id ||fnd_global.local_chr(10)
3034 ||', ln_app_to_doc_type -> ' ||ln_app_to_doc_type ||fnd_global.local_chr(10)
3038 ||', ln_to_organization_id -> ' ||ln_to_organization_id ||fnd_global.local_chr(10)
3035 ||', ln_app_to_doc_table -> ' ||ln_app_to_doc_table ||fnd_global.local_chr(10)
3036 ||', ln_app_to_doc_amt -> ' ||ln_app_to_doc_amt ||fnd_global.local_chr(10)
3037 ||', ld_app_to_doc_date -> ' ||ld_app_to_doc_date ||fnd_global.local_chr(10)
3039 ||', ln_to_party_id -> ' ||ln_to_party_id ||fnd_global.local_chr(10)
3040 ||', ln_to_party_site_id -> ' ||ln_to_party_site_id ||fnd_global.local_chr(10)
3041 ||', ln_app_fr_doc_id -> ' ||ln_app_fr_doc_id ||fnd_global.local_chr(10)
3042 ||', ln_app_to_doc_type -> ' ||ln_app_to_doc_type ||fnd_global.local_chr(10)
3043 ||', ln_app_fr_doc_table -> ' ||ln_app_fr_doc_table ||fnd_global.local_chr(10)
3044 ||', ln_app_fr_doc_amt -> ' ||ln_app_fr_doc_amt ||fnd_global.local_chr(10)
3045 ||', ld_app_fr_doc_date -> ' ||ld_app_fr_doc_date ||fnd_global.local_chr(10)
3046 ||', ln_fr_organization_id -> ' ||ln_fr_organization_id ||fnd_global.local_chr(10)
3047 ||', ln_fr_party_id -> ' ||ln_fr_party_id ||fnd_global.local_chr(10)
3048 ||', ln_fr_party_site_id -> ' ||ln_fr_party_site_id ||fnd_global.local_chr(10)
3049 ||', ln_exchange_rate -> ' ||ln_exchange_rate ||fnd_global.local_chr(10)
3050 ||', ln_app_amount -> ' ||ln_app_amount ||fnd_global.local_chr(10)
3051 );*/
3052
3053 /*########################################################################################################
3054 || DERIVE DATE AS PER LATER DOCUMENT ---- PART -4
3055 ########################################################################################################*/
3056
3057 /*****
3058 || Derive the document date based on the date of
3059 || the later document
3060 || Also get the tcs amount based on the later document date
3061 ******/
3062 IF ld_app_fr_doc_date >= ld_app_to_doc_date THEN -----------A2
3063 /*commented by csahoo for bug# 6401388
3064 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3065 pv_log_msg => ' From date >= To date '||fnd_global.local_chr(10)
3066 ||', ld_app_fr_doc_date -> '||ld_app_fr_doc_date
3067 ||', ld_app_to_doc_date => '|| ld_app_to_doc_date ||fnd_global.local_chr(10)
3068 );*/
3069 ln_app_ref_doc_id := ln_app_fr_doc_id ;
3070 ln_app_ref_doc_type := ln_app_fr_doc_type ;
3071 ln_app_ref_doc_table := ln_app_fr_doc_table ;
3072 ld_source_document_date := ld_app_fr_doc_date ;
3073 ln_apportion_factor := ln_app_amount/nvl(ln_app_fr_doc_amt,1) ;
3074
3075 ln_organization_id := ln_fr_organization_id ;
3076 ln_party_id := ln_fr_party_id ;
3077 ln_party_site_id := ln_fr_party_site_id ;
3078
3079 ELSE -----------A2
3080 /*
3081 || Applied To document is later
3082 */
3083
3084 /*commented by csahoo for bug# 6401388
3085 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3086 pv_log_msg => ' To date is greater than from date '||fnd_global.local_chr(10)
3087 ||', ld_app_fr_doc_date -> '||ld_app_fr_doc_date ||fnd_global.local_chr(10)
3088 ||', ld_app_to_doc_date => '|| ld_app_to_doc_date
3089 );*/
3090
3091 ln_app_ref_doc_id := ln_app_to_doc_id ;
3092 ln_app_ref_doc_type := ln_app_to_doc_type ;
3093 ln_app_ref_doc_table := ln_app_to_doc_table;
3094 ld_source_document_date := ld_app_to_doc_date;
3095 ln_apportion_factor := ln_app_amount/nvl(ln_app_to_doc_amt,1) ;
3096
3097 ln_organization_id := ln_to_organization_id ;
3098 ln_party_id := ln_to_party_id ;
3099 ln_party_site_id := ln_to_party_site_id ;
3100
3101 END IF; -----------A2
3102
3103
3104 /*commented by csahoo for bug# 6401388
3105 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3106 pv_log_msg => ' based on later date derivation logic values derived are ' ||fnd_global.local_chr(10)
3107 ||', ln_app_ref_doc_id -> ' ||ln_app_ref_doc_id ||fnd_global.local_chr(10)
3108 ||', ln_app_ref_doc_type -> ' ||ln_app_ref_doc_type ||fnd_global.local_chr(10)
3112 ||', ln_organization_id -> ' ||ln_organization_id ||fnd_global.local_chr(10)
3109 ||', ln_app_ref_doc_table -> ' ||ln_app_ref_doc_table ||fnd_global.local_chr(10)
3110 ||', ld_source_document_date -> ' ||ld_source_document_date ||fnd_global.local_chr(10)
3111 ||', ln_apportion_factor -> ' ||ln_apportion_factor ||fnd_global.local_chr(10)
3113 ||', ln_party_id -> ' ||ln_party_id ||fnd_global.local_chr(10)
3114 ||', ln_party_site_id -> ' ||ln_party_site_id
3115 );*/
3116
3117
3118 /*########################################################################################################
3119 || DERIVE TCS AMOUNT IN CASE OF OVERAPPLICATION ---- CURRENTLY COMMENTED PART -5
3120 ########################################################################################################
3121
3122
3123 -- || Derive the tcs tax amount according to the below formula :-
3124 -- || Check if application_amount > applied to amount
3125 -- || IF yes then it indicates overapplication else it is normal application
3126
3127 IF abs(ln_app_amount) > abs(ln_app_to_doc_amt) THEN
3128
3129 -- || Case is OVERAPPLICATION
3130 -- || Hence consider the tcs amount from the APPLIED TO DOCUMENT
3131
3132 ln_app_amount := ln_app_to_doc_amt;
3133 ln_app_ref_doc_id := ln_app_to_doc_id ;
3134 ln_app_ref_doc_type := ln_app_to_doc_type ;
3135 ln_app_ref_doc_table := ln_app_to_doc_table;
3136 ln_apportion_factor := 1 ;
3137 END IF; -----------A2
3138 */
3139
3140 /*########################################################################################################
3141 || DERIVE THE PARENT DOCUMENT REFERENCE ---- PART -5
3142 ########################################################################################################*/
3143
3144 OPEN cur_get_parent_transaction ( cp_source_document_id => ln_app_ref_doc_id ,
3145 cp_source_document_type => ln_app_ref_doc_type
3146 );
3147 FETCH cur_get_parent_transaction INTO ln_parent_transaction_id;
3148 CLOSE cur_get_parent_transaction ;
3149 /*commented by csahoo for bug# 6401388
3150 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3151 pv_log_msg => ' parent transaction id is (ln_parent_transaction_id)' ||ln_parent_transaction_id
3152 );*/
3153
3154
3155 /*########################################################################################################
3156 || INSERT APPLICATION RECORDS INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
3157 ########################################################################################################*/
3158 /*
3159 ||Get the sequence generated unique key for the transaction
3160 */
3161 OPEN cur_get_transaction_id ;
3162 FETCH cur_get_transaction_id INTO ln_transaction_id ;
3163 CLOSE cur_get_transaction_id ;
3164
3165 /*commented by csahoo for bug# 6401388
3166 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3167 pv_log_msg => ' Before call to insert_repository_references '
3168 );*/
3169 insert_repository_references (
3170 p_regime_id => NULL ,
3171 p_transaction_id => ln_transaction_id ,
3172 p_source_ref_document_id => ln_app_ref_doc_id ,
3173 p_source_ref_document_type => ln_app_ref_doc_type ,
3174 p_app_from_document_id => ln_app_fr_doc_id ,
3175 p_app_from_document_type => ln_app_fr_doc_type ,
3176 p_app_to_document_id => ln_app_to_doc_id ,
3177 p_app_to_document_type => ln_app_to_doc_type ,
3178 p_parent_transaction_id => ln_parent_transaction_id ,
3179 p_org_tan_no => NULL ,
3180 p_document_id => p_araa.receivable_application_id ,
3181 p_document_type => p_document_type ,
3182 p_document_line_id => p_araa.receivable_application_id ,
3183 p_document_date => ld_source_document_date ,
3184 p_table_name => jai_constants.ar_receipt_app ,
3188 p_organization_id => ln_organization_id ,
3185 p_line_amount => ln_app_amount ,
3186 p_document_amount => ln_app_amount ,
3187 p_org_id => p_araa.org_id ,
3189 p_party_id => ln_party_id ,
3190 p_party_site_id => ln_party_site_id ,
3191 p_item_classification => p_item_classification ,
3192 p_trx_ref_id => ln_trx_ref_id ,
3193 p_process_flag => lv_process_flag ,
3194 p_process_message => lv_process_message
3195 );
3196
3197
3198 /*commented by csahoo for bug# 6401388
3199 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3200 pv_log_msg => ' Returned from call to insert_repository_references, lv_process_flag -> '||lv_process_flag
3201 );*/
3202 IF lv_process_flag = jai_constants.expected_error OR ---------A2
3203 lv_process_flag = jai_constants.unexpected_error OR
3204 lv_process_flag = jai_constants.not_applicable
3205 THEN
3206 /*
3207 || As Returned status is an error/not applicable hence:-
3208 || Set out variables p_process_flag and p_process_message accordingly
3209 */
3210 --call to debug package
3211 /*commented by csahoo for bug# 6401388
3212 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3213 pv_log_msg => ' Error in procedure insert_repository_references '
3214 ||', p_process_flag -> '|| p_process_flag
3215 ||', lv_process_message -> '|| lv_process_message
3216 );*/
3217 p_process_flag := lv_process_flag ;
3218 p_process_message := lv_process_message ;
3219 return;
3220 END IF; ---------A2
3221
3222 /*########################################################################################################
3223 || COPY APPLICATION TAXES FROM SOURCE TRANSACTION TABLES INTO JAI_RGM_TAXES TABLE ---- PART -2
3224 ########################################################################################################*/
3225 /*commented by csahoo for bug# 6401388
3226 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3227 pv_log_msg => ' Before call to copy_taxes_from_source '
3228 );*/
3229 copy_taxes_from_source ( p_source_document_type => ln_app_ref_doc_type ,
3230 p_source_document_id => ln_app_ref_doc_id ,
3231 p_apportion_factor => ln_apportion_factor ,
3232 p_trx_ref_id => ln_trx_ref_id ,
3233 p_process_flag => lv_process_flag ,
3234 p_process_message => lv_process_message
3235 );
3236
3237 /*commented by csahoo for bug# 6401388
3238 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3239 pv_log_msg => ' Returned from call to copy_taxes_from_source, lv_process_flag -> '||lv_process_flag
3240 );*/
3241 IF lv_process_flag = jai_constants.expected_error OR ---------A2
3242 lv_process_flag = jai_constants.unexpected_error OR
3243 lv_process_flag = jai_constants.not_applicable
3244 THEN
3245 /*
3246 || As Returned status is an error/not applicable hence:-
3247 || Set out variables p_process_flag and p_process_message accordingly
3248 */
3249 --call to debug package
3250 /*commented by csahoo for bug# 6401388
3251 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3252 pv_log_msg => ' Error in procedure copy_taxes_from_source '
3253 ||', p_process_flag -> '|| p_process_flag
3254 ||', lv_process_message -> '|| lv_process_message
3255 );*/
3256 p_process_flag := lv_process_flag ;
3257 p_process_message := lv_process_message ;
3258 return;
3259 END IF; ---------A2
3260 /*commented by csahoo for bug# 6401388
3261 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3262 pv_log_msg => '**************** PROCEDURE PROCESS_APPLICATIONS SUCCESSFULLY COMPLETED ****************'
3263 );
3264 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
3265 END process_applications;
3266
3267
3268 PROCEDURE process_unapp_rcpt_rev ( p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE DEFAULT NULL ,
3272 p_process_message OUT NOCOPY VARCHAR2
3269 p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE DEFAULT NULL ,
3270 p_document_type IN VARCHAR2 ,
3271 p_process_flag OUT NOCOPY VARCHAR2 ,
3273 )
3274
3275 IS
3276
3277 ln_reg_id NUMBER;
3278 /*
3279 ||Get the parent record for an type of record
3280 */
3281 CURSOR cur_get_parent_trx ( cp_source_document_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE DEFAULT NULL ,
3282 cp_source_document_type JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ,
3283 cp_app_from_document_id JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_ID%TYPE DEFAULT NULL ,
3284 cp_app_to_document_id JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_ID%TYPE DEFAULT NULL
3285 )
3286 IS
3287 SELECT
3288 to_number(max(transaction_id)) parent_transaction_id
3289 FROM
3290 jai_rgm_refs_all
3291 WHERE
3292 source_document_id = nvl(cp_source_document_id , source_document_id)
3293 AND source_document_type = cp_source_document_type
3294 AND app_from_document_id = nvl(app_from_document_id , app_from_document_id )
3295 AND app_to_document_id = nvl(app_to_document_id , app_to_document_id ) ;
3296
3297 ln_new_document_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ;
3298 ln_source_document_id JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ;
3299 ld_new_document_date JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_DATE%TYPE ;
3300 lv_source_document_type JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ;
3301 ln_app_from_document_id JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_ID%TYPE ;
3302 ln_app_to_document_id JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_ID%TYPE ;
3303 ln_parent_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE ;
3304 lv_process_flag VARCHAR2(2) ;
3305 lv_process_message VARCHAR2(2000) ;
3306
3307 BEGIN
3308 /*########################################################################################################
3309 || VARIABLES INITIALIZATION
3310 ########################################################################################################*/
3311 lv_member_name := 'PROCESS_UNAPP_RCPT_REV';
3312 set_debug_context;
3313 /*commented by csahoo for bug# 6401388
3314 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
3315 , pn_reg_id => ln_reg_id
3316 );*/
3317
3318 lv_process_flag := jai_constants.successful ;
3319 lv_process_message := null ;
3320
3321 p_process_flag := lv_process_flag ;
3322 p_process_message := lv_process_message ;
3323
3324 /* IF p_document_type In ( jai_constants.trx_type_rct_unapp ,
3325 jai_constants.trx_type_cm_unapp
3326 )
3327 THEN
3328 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3329 pv_log_msg => ' Processing the UN APPLICATION record ' ||fnd_global.local_chr(10)
3330 ||', p_document_type -> ' ||p_document_type ||fnd_global.local_chr(10)
3331 ||', receivable_application_id -> ' ||p_araa.receivable_application_id ||fnd_global.local_chr(10)
3332 ||', application_type -> ' ||p_araa.application_type ||fnd_global.local_chr(10)
3333 ||', status -> ' ||p_araa.status ||fnd_global.local_chr(10)
3334 ||', display -> ' ||p_araa.display ||fnd_global.local_chr(10)
3335 ||', cash_receipt_id -> ' ||p_araa.cash_receipt_id ||fnd_global.local_chr(10)
3336 ||', customer_trx_id -> ' ||p_araa.customer_trx_id ||fnd_global.local_chr(10)
3337 ||', applied_customer_trx_id -> ' ||p_araa.applied_customer_trx_id ||fnd_global.local_chr(10)
3338 ||', amount_applied -> ' ||p_araa.amount_applied ||fnd_global.local_chr(10)
3339 ||', gl_date -> ' ||p_araa.gl_date ||fnd_global.local_chr(10)
3340 ||', org_id -> ' ||p_araa.org_id
3341 );
3342 ELSIF p_document_type = jai_constants.trx_type_rct_rvs THEN
3343 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3344 pv_log_msg => ' Processing the Receipt reversal record ' ||fnd_global.local_chr(10)
3345 ||', p_document_type -> ' ||p_document_type ||fnd_global.local_chr(10)
3346 ||', cash_receipt_id -> ' ||p_acra.cash_receipt_id||fnd_global.local_chr(10)
3347 ||', receipt_number -> ' ||p_acra.cash_receipt_id||fnd_global.local_chr(10)
3348 ||', amount -> ' ||p_acra.cash_receipt_id||fnd_global.local_chr(10)
3352
3349 );
3350
3351 END IF;
3353
3354 /*########################################################################################################
3355 || DERIVE VALUES BASED ON APPLICATION TYPE
3356 ########################################################################################################*/
3357
3358 IF p_document_type = jai_constants.trx_type_rct_rvs THEN
3359 ln_new_document_id := p_acra.cash_receipt_id ; /* New document id to be created */
3360 ld_new_document_date := p_acra.reversal_date ;
3361
3362 ln_source_document_id := p_acra.cash_receipt_id ;
3363 lv_source_document_type := jai_constants.ar_cash_tax_confirmed ; /* Parent Document type */
3364
3365
3366 ELSIF p_document_type = jai_constants.trx_type_rct_unapp THEN
3367 ln_new_document_id := p_araa.receivable_application_id ; /* New document id to be created */
3368 ld_new_document_date := p_araa.apply_date ;
3369
3370 lv_source_document_type := jai_constants.trx_type_rct_app ; /* Parent Document type */
3371 ln_app_from_document_id := p_araa.cash_receipt_id ;
3372 ln_app_to_document_id := p_araa.applied_customer_trx_id ;
3373
3374
3375 ELSIF p_document_type = jai_constants.trx_type_cm_unapp THEN
3376 ln_new_document_id := p_araa.receivable_application_id ; /* New document id to be created */
3377 ld_new_document_date := p_araa.apply_date ;
3378
3379 lv_source_document_type := jai_constants.trx_type_cm_app ; /* Parent Document type */
3380 ln_app_from_document_id := p_araa.customer_trx_id ;
3381 ln_app_to_document_id := p_araa.applied_customer_trx_id ;
3382
3383 END IF;
3384
3385
3386 /*commented by csahoo for bug# 6401388
3387 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3388 pv_log_msg => ' Values derived for an application/reversal are ' ||fnd_global.local_chr(10)
3389 ||', ln_new_document_id -> ' ||ln_new_document_id ||fnd_global.local_chr(10)
3390 ||', ld_new_document_date -> ' ||ld_new_document_date ||fnd_global.local_chr(10)
3391 ||', ln_source_document_id -> ' ||ln_source_document_id ||fnd_global.local_chr(10)
3392 ||', lv_source_document_type -> ' ||lv_source_document_type ||fnd_global.local_chr(10)
3393 ||', ln_app_from_document_id -> ' ||ln_app_from_document_id ||fnd_global.local_chr(10)
3394 ||', ln_app_to_document_id -> ' ||ln_app_to_document_id
3395 );*/
3396
3397
3398 /*
3399 ||Get the parent transaction for the receipt reversal/Unapplication
3400 */
3401
3402 OPEN cur_get_parent_trx ( cp_source_document_id => ln_source_document_id ,
3403 cp_source_document_type => lv_source_document_type ,
3404 cp_app_from_document_id => ln_app_from_document_id ,
3405 cp_app_to_document_id => ln_app_to_document_id
3406 ) ;
3407 FETCH cur_get_parent_trx INTO ln_parent_transaction_id ;
3408
3409 /*commented by csahoo for bug# 6401388
3410 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3411 pv_log_msg => ' Parent transaction is ->' ||ln_parent_transaction_id
3412 );*/
3413 /*
3414 || Check that the source receipt confirmation record has been found in the TCS repository.
3415 || If not found then receipt reversal also need not hit the repository.
3416 */
3417 IF CUR_GET_PARENT_TRX%FOUND THEN
3418 CLOSE cur_get_parent_trx ;
3419 /*commented by csahoo for bug# 6401388
3420 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3421 pv_log_msg => ' Parent transaction found, Before call to copy_references '
3422 );*/
3423 copy_references ( p_parent_transaction_id => ln_parent_transaction_id ,
3424 p_new_document_id => ln_new_document_id ,
3425 p_new_document_type => p_document_type ,
3426 p_new_document_date => ld_new_document_date ,
3427 p_apportion_factor => -1 ,/* As reversal cannot be partial */
3428 p_process_flag => lv_process_flag ,
3429 p_process_message => lv_process_message
3430 );
3431
3432 /*commented by csahoo for bug# 6401388
3433 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3434 pv_log_msg => ' returned from call to copy_references lv_process_flag -> '||lv_process_flag
3435 );*/
3436 IF lv_process_flag = jai_constants.expected_error OR ---------A2
3437 lv_process_flag = jai_constants.unexpected_error OR
3438 lv_process_flag = jai_constants.not_applicable
3439 THEN
3440 /*
3441 || As Returned status is an error/not applicable hence:-
3442 || Set out variables p_process_flag and p_process_message accordingly
3443 */
3444 --call to debug package
3445 /*commented by csahoo for bug# 6401388
3446 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3450 );*/
3447 pv_log_msg => ' Error in processing copy references ' ||fnd_global.local_chr(10)
3448 ||',lv_process_flag -> '||lv_process_flag ||fnd_global.local_chr(10)
3449 ||',lv_process_message -> '||lv_process_message
3451 p_process_flag := lv_process_flag ;
3452 p_process_message := lv_process_message ;
3453 return;
3454 END IF; ---------A2
3455 ELSE
3456 /*
3457 ||Base document not found hence skip the document
3458 */
3459 p_process_flag := jai_constants.not_applicable;
3460 END IF; /* Parent transaction found*/
3461 /*commented by csahoo for bug# 6401388
3462 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3463 pv_log_msg => '**************** PROCEDURE PROCESS_UNAPP_RCPT_REV SUCCESSFULLY COMPLETED ****************'
3464 );
3465 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
3466 END process_unapp_rcpt_rev ;
3467
3468
3469 procedure insert_repository_references ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE DEFAULT NULL ,
3470 p_transaction_id IN JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE ,
3471 p_source_ref_document_id IN JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_ID%TYPE DEFAULT NULL ,
3472 p_source_ref_document_type IN JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_TYPE%TYPE ,
3473 p_app_from_document_id IN JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_ID%TYPE DEFAULT NULL ,
3474 p_app_from_document_type IN JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_TYPE%TYPE DEFAULT NULL ,
3475 p_app_to_document_id IN JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_ID%TYPE DEFAULT NULL ,
3476 p_app_to_document_type IN JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_TYPE%TYPE DEFAULT NULL ,
3477 p_parent_transaction_id IN JAI_RGM_REFS_ALL.PARENT_TRANSACTION_ID%TYPE DEFAULT NULL ,
3478 p_org_tan_no IN JAI_RGM_REFS_ALL.ORG_TAN_NO%TYPE DEFAULT NULL ,
3479 p_document_id IN NUMBER ,
3480 p_document_type IN VARCHAR2 ,
3481 p_document_line_id IN NUMBER ,
3482 p_document_date IN DATE ,
3483 p_table_name IN VARCHAR2 ,
3484 p_line_amount IN NUMBER ,
3485 p_document_amount IN NUMBER ,
3486 p_org_id IN NUMBER ,
3487 p_organization_id IN NUMBER ,
3488 p_party_id IN NUMBER ,
3489 p_party_site_id IN NUMBER ,
3490 p_item_classification IN VARCHAR2 ,
3491 p_trx_ref_id OUT NOCOPY JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ,
3492 p_process_flag OUT NOCOPY VARCHAR2 ,
3493 p_process_message OUT NOCOPY VARCHAR2
3494 )
3495
3496 IS
3497
3498 /****
3499 ||Get the primary key
3500 || for the table jai_rgm_refs_all
3501 *****/
3502 ln_reg_id NUMBER;
3503 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
3504 lv_org_tan_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
3505 ln_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ;
3506 lv_process_flag VARCHAR2(2) ;
3507 lv_process_message VARCHAR2(2000) ;
3508 ln_user_id JAI_RGM_REFS_ALL.CREATED_BY%TYPE ;
3509 ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
3510 ln_fin_year JAI_AP_TDS_YEARS.FIN_YEAR%TYPE ;
3511 ln_source_ref_document_id JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_ID%TYPE ;
3515
3512 ln_source_ref_document_type JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_TYPE%TYPE ;
3513 ln_threshold_slab_id JAI_RGM_REFS_ALL.THRESHOLD_SLAB_ID%TYPE ;
3514 BEGIN
3516 /*################################################################################################################
3517 || Initialize the variables
3518 ################################################################################################################*/
3519 lv_member_name := 'INSERT_REPOSITORY_REFERENCES';
3520 set_debug_context;
3521 /*commented by csahoo for bug# 6401388
3522 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
3523 , pn_reg_id => ln_reg_id
3524 );*/
3525
3526 /*commented by csahoo for bug# 6401388
3527 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3528 pv_log_msg => ' PARAMETERS VALUES PASSED TO INSERT_REPOSITORY_REFERENCES : - ' ||fnd_global.local_chr(10)
3529 ||', p_regime_id -> '||p_regime_id ||fnd_global.local_chr(10)
3530 ||', p_transaction_id -> '||p_transaction_id ||fnd_global.local_chr(10)
3531 ||', p_source_ref_document_id -> '||p_source_ref_document_id ||fnd_global.local_chr(10)
3532 ||', p_source_ref_document_type -> '||p_source_ref_document_type ||fnd_global.local_chr(10)
3533 ||', p_app_from_document_id -> '||p_app_from_document_id ||fnd_global.local_chr(10)
3534 ||', p_app_from_document_type -> '||p_app_from_document_type ||fnd_global.local_chr(10)
3535 ||', p_app_to_document_id -> '||p_app_to_document_id ||fnd_global.local_chr(10)
3536 ||', p_app_to_document_type -> '||p_app_to_document_type ||fnd_global.local_chr(10)
3537 ||', p_parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
3538 ||', p_org_tan_no -> '||p_org_tan_no ||fnd_global.local_chr(10)
3539 ||', p_document_id -> '||p_document_id ||fnd_global.local_chr(10)
3540 ||', p_document_type -> '||p_document_type ||fnd_global.local_chr(10)
3541 ||', p_document_line_id -> '||p_document_line_id ||fnd_global.local_chr(10)
3542 ||', p_document_date -> '||p_document_date ||fnd_global.local_chr(10)
3543 ||', p_table_name -> '||p_table_name ||fnd_global.local_chr(10)
3544 ||', p_line_amount -> '||p_line_amount ||fnd_global.local_chr(10)
3545 ||', p_document_amount -> '||p_document_amount ||fnd_global.local_chr(10)
3546 ||', p_org_id -> '||p_org_id ||fnd_global.local_chr(10)
3547 ||', p_organization_id -> '||p_organization_id ||fnd_global.local_chr(10)
3548 ||', p_party_id -> '||p_party_id ||fnd_global.local_chr(10)
3549 ||', p_party_site_id -> '||p_party_site_id ||fnd_global.local_chr(10)
3550 ||', p_item_classification -> '||p_item_classification ||fnd_global.local_chr(10)
3551 ||', p_trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
3552 );*/
3553
3554
3555 lv_process_flag := jai_constants.successful ;
3556 lv_process_message := null ;
3557
3558 p_process_flag := lv_process_flag ;
3559 p_process_message := lv_process_message ;
3560
3561 ln_user_id := fnd_global.user_id ;
3562 ln_login_id := fnd_global.login_id ;
3563
3564 OPEN cur_get_trx_ref_id ;
3565 FETCH cur_get_trx_ref_id INTO ln_trx_ref_id ;
3566 CLOSE cur_get_trx_ref_id ;
3567
3568 /**********
3569 || IF source_ref_document_id and source_ref_document_type are null then they should be same as
3570 || trx_ref_id.
3571 || The value for source_ref_document_id and source_ref_document_type would be different from trx_ref_id only in case of reversal,application and unapplication
3572 || in which case it would be same as the corresponding source receipt or invoice
3573 ************/
3574 p_trx_ref_id := ln_trx_ref_id ;
3575
3576 ln_source_ref_document_id := NVL(p_source_ref_document_id,p_document_id) ;
3577
3578 OPEN get_tcs_fin_year( cp_org_id => p_org_id ,
3579 cp_trx_date => p_document_date
3580 );
3581
3582 FETCH get_tcs_fin_year INTO ln_fin_year;
3583 CLOSE get_tcs_fin_year;
3584
3585 /*********
3586 || Get the regime_id and org_tan_no in case the p_regime_id or p_org_tan_no is null
3587 *********/
3588 IF p_regime_id IS NULL OR
3589 p_org_tan_no IS NULL
3590 THEN
3591 /*commented by csahoo for bug# 6401388
3592 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3596 cp_attribute_code => jai_constants.rgm_attr_code_org_tan ,
3593 pv_log_msg => ' Derive regime values as they are null '
3594 );*/
3595 OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.tcs_regime ,
3597 cp_organization_id => p_organization_id
3598 ) ;
3599 FETCH c_get_rgm_attribute INTO ln_regime_id, lv_org_tan_no ;
3600 IF C_GET_RGM_ATTRIBUTE%NOTFOUND THEN
3601 CLOSE c_get_rgm_attribute;
3602 p_process_flag := jai_constants.expected_error;
3603 p_process_message := 'Org Tan Number needs to be defined for the TCS regime ';
3604 return;
3605 END IF;
3606 CLOSE c_get_rgm_attribute;
3607
3608 /*commented by csahoo for bug# 6401388
3609 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3610 pv_log_msg => 'Regime values are '
3611 ||', ln_regime_id -> '||ln_regime_id
3612 ||', lv_org_tan_no -> '||lv_org_tan_no
3613 );*/
3614
3615 ELSE
3616 ln_regime_id := p_regime_id ;
3617 lv_org_tan_no := p_org_tan_no ;
3618 END IF;
3619
3620 /*commented by csahoo for bug# 6401388
3621 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3622 pv_log_msg => 'Regime values are '
3623 ||', ln_regime_id -> '||ln_regime_id
3624 ||', lv_org_tan_no -> '||lv_org_tan_no
3625 );*/
3626
3627 /*################################################################################################################
3628 || INSERT THE DOCUMENT RECORD INTO THE TCS REPOSITORY
3629 ################################################################################################################*/
3630
3631
3632 /*commented by csahoo for bug# 6401388
3633 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3634 pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
3635 ||', trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
3636 ||', regime_id -> '||ln_regime_id ||fnd_global.local_chr(10)
3637 ||', transaction_id -> '||p_transaction_id ||fnd_global.local_chr(10)
3638 ||', source_ref_document_id -> '||ln_source_ref_document_id ||fnd_global.local_chr(10)
3639 ||', source_ref_document_type -> '||p_source_ref_document_type ||fnd_global.local_chr(10)
3640 ||', app_from_document_id -> '||p_app_from_document_id ||fnd_global.local_chr(10)
3641 ||', app_from_document_ty -> '||p_app_from_document_type ||fnd_global.local_chr(10)
3642 ||', app_to_document_id -> '||p_app_to_document_id ||fnd_global.local_chr(10)
3643 ||', app_to_document_type -> '||p_app_to_document_type ||fnd_global.local_chr(10)
3644 ||', parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
3645 ||', org_tan_no -> '||lv_org_tan_no ||fnd_global.local_chr(10)
3646 ||', source_document_id -> '||p_document_id ||fnd_global.local_chr(10)
3647 ||', source_document_line -> '||p_document_line_id ||fnd_global.local_chr(10)
3648 ||', source_document_type -> '||p_document_type ||fnd_global.local_chr(10)
3649 ||', source_document_date -> '||p_document_date ||fnd_global.local_chr(10)
3650 ||', source_table_name -> '||p_table_name ||fnd_global.local_chr(10)
3651 ||', line_amt -> '||p_line_amount ||fnd_global.local_chr(10)
3652 ||', source_document_amt -> '||p_document_amount ||fnd_global.local_chr(10)
3653 ||', total_tax_amt -> '||NULL ||fnd_global.local_chr(10)
3654 ||', party_id -> '||p_party_id ||fnd_global.local_chr(10)
3655 ||', party_type -> '||jai_constants.party_type_customer ||fnd_global.local_chr(10)
3656 ||', party_site_id -> '||p_party_site_id ||fnd_global.local_chr(10)
3657 ||', item_classification -> '||p_item_classification ||fnd_global.local_chr(10)
3658 ||', org_id -> '||p_org_id ||fnd_global.local_chr(10)
3659 ||', organization_id -> '||p_organization_id ||fnd_global.local_chr(10)
3663 ||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
3660 ||', fin_year -> '||ln_fin_year ||fnd_global.local_chr(10)
3661 ||', threshold_slab_id -> '||ln_threshold_slab_id ||fnd_global.local_chr(10)
3662 ||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
3664 ||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
3665 ||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
3666 ||', last_update_login -> '||ln_login_id ||fnd_global.local_chr(10)
3667 ||', settlement_id -> '||NULL ||fnd_global.local_chr(10)
3668 ||', certificate_id -> '||NULL
3669 );*/
3670
3671 INSERT into jai_rgm_refs_all (
3672 trx_ref_id ,
3673 regime_id ,
3674 transaction_id ,
3675 source_ref_document_id ,
3676 source_ref_document_type ,
3677 app_from_document_id ,
3678 app_from_document_type ,
3679 app_to_document_id ,
3680 app_to_document_type ,
3681 parent_transaction_id ,
3682 org_tan_no ,
3683 source_document_id ,
3684 source_document_line_id ,
3685 source_document_type ,
3686 source_document_date ,
3687 source_table_name ,
3688 line_amt ,
3689 source_document_amt ,
3690 total_tax_amt ,
3691 party_id ,
3692 party_type ,
3693 party_site_id ,
3694 item_classification ,
3695 org_id ,
3696 organization_id ,
3697 fin_year ,
3698 threshold_slab_id ,
3699 created_by ,
3700 creation_date ,
3701 last_updated_by ,
3702 last_update_date ,
3703 last_update_login ,
3704 settlement_id ,
3705 certificate_id
3706 )
3707 VALUES (
3708 p_trx_ref_id ,
3709 ln_regime_id ,
3710 p_transaction_id ,
3711 ln_source_ref_document_id ,
3712 p_source_ref_document_type ,
3713 p_app_from_document_id ,
3714 p_app_from_document_type ,
3715 p_app_to_document_id ,
3716 p_app_to_document_type ,
3717 p_parent_transaction_id ,
3718 lv_org_tan_no ,
3719 p_document_id ,
3720 p_document_line_id ,
3721 p_document_type ,
3722 p_document_date ,
3723 p_table_name ,
3724 p_line_amount ,
3725 p_document_amount ,
3726 NULL ,
3730 p_item_classification ,
3727 p_party_id ,
3728 jai_constants.party_type_customer ,
3729 p_party_site_id ,
3731 p_org_id ,
3732 p_organization_id ,
3733 ln_fin_year ,
3734 ln_threshold_slab_id ,
3735 ln_user_id ,
3736 sysdate ,
3737 ln_user_id ,
3738 sysdate ,
3739 ln_login_id ,
3740 NULL ,
3741 NULL
3742 );
3743
3744 /*commented by csahoo for bug# 6401388
3745 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
3746 END insert_repository_references;
3747
3748 procedure insert_repository_taxes (
3749 p_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ,
3750 p_tax_id JAI_RGM_TAXES.TAX_ID%TYPE ,
3751 p_tax_type JAI_RGM_TAXES.TAX_TYPE%TYPE ,
3752 p_tax_rate JAI_RGM_TAXES.TAX_RATE%TYPE ,
3753 p_tax_amount JAI_RGM_TAXES.TAX_AMT%TYPE ,
3754 p_func_tax_amount JAI_RGM_TAXES.FUNC_TAX_AMT%TYPE ,
3755 p_tax_modified_by JAI_RGM_TAXES.TAX_MODIFIED_BY%TYPE DEFAULT NULL ,
3756 p_currency_code JAI_RGM_TAXES.CURRENCY_CODE%TYPE ,
3757 p_process_flag OUT NOCOPY VARCHAR2 ,
3758 p_process_message OUT NOCOPY VARCHAR2
3759 )
3760
3761
3762 IS
3763 ln_reg_id NUMBER;
3764 /****
3765 || Get the orig_tax_percenatge for the tax_id
3766 || and determine the exemption flag value
3767 *****/
3768 CURSOR cur_get_exemption_value
3769 IS
3770 SELECT
3771 orig_tax_percentage
3772 FROM
3773 JAI_CMN_TAXES_ALL
3774 WHERE
3775 tax_id = p_tax_id;
3776
3777 ln_tax_det_id JAI_RGM_TAXES.TAX_DET_ID%TYPE ;
3778 lv_process_flag VARCHAR2(2) ;
3779 lv_process_message VARCHAR2(2000) ;
3780 ln_user_id JAI_RGM_TAXES.CREATED_BY%TYPE ;
3781 ln_login_id JAI_RGM_TAXES.LAST_UPDATE_LOGIN%TYPE ;
3782 ln_orig_tax_rate JAI_CMN_TAXES_ALL.ORIG_TAX_PERCENTAGE%TYPE ;
3783 ln_exempted_flag JAI_RGM_TAXES.EXEMPTED_FLAG%TYPE ;
3784 ln_tax_modified_by JAI_RGM_TAXES.TAX_MODIFIED_BY%TYPE ;
3785 BEGIN
3786
3787
3788
3789
3790 /***************************************************************************************************
3791 -- #
3792 -- # Change History -
3793
3794
3795 1. 01/02/2007 CSahoo for bug#5631784. File Version 120.0
3796 Forward Porting of 11i BUG#4742259 (TAX COLLECTION AT SOURCE IN RECEIVABLES)
3797
3798 2. 15/06/2007 brahtod, bug#6132484, File Version 120.1
3799 Issue: ERROR WHILE TRYING TO APPLY INVOICE TO CASH RECEIPT .
3800 Fix: cursor cur_get_app_to_det is changed in process_application to fetch bill_to_site_use_id
3801
3802 3. 19/-6/2007 sacsethi , bug 6137956 File version 120.2
3803
3804 Problem - In Auto Invoice master program , Transaction number created for TCS Type of receipt method was
3805 not coming
3806
3807 Solution -In Table RA_CUSTOMER_TRX_LINES_ALL , Org_id column was null
3808 4. 10-Sep-2007 CSahoo for bug#6401388, File Version120.4.12000000.7
3809 commented the code related to jai_cmn_debug_context_pkg package.
3810 removed all the debug messages meant for debugging.
3811
3812 *******************************************************************************************************/
3813
3814 /*################################################################################################################
3815 || Initialize the variables
3816 ################################################################################################################*/
3817 lv_member_name := 'INSERT_REPOSITORY_TAXES';
3818 set_debug_context;
3819 /*commented by csahoo for bug# 6401388
3820 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
3821 , pn_reg_id => ln_reg_id
3822 );*/
3823
3824 /*commented by csahoo for bug# 6401388
3825 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3826 pv_log_msg => ' PARAMETERS VALUES PASSED TO INSERT_REPOSITORY_TAXES : - '||fnd_global.local_chr(10)
3830 ||', p_tax_rate -> '||p_tax_rate ||fnd_global.local_chr(10)
3827 ||', p_trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
3828 ||', p_tax_id -> '||p_tax_id ||fnd_global.local_chr(10)
3829 ||', p_tax_type -> '||p_tax_type ||fnd_global.local_chr(10)
3831 ||', p_tax_amount -> '||p_tax_amount ||fnd_global.local_chr(10)
3832 ||', p_func_tax_amount -> '||p_func_tax_amount ||fnd_global.local_chr(10)
3833 ||', p_tax_modified_by -> '||p_tax_modified_by ||fnd_global.local_chr(10)
3834 ||', p_currency_code -> '||p_currency_code ||fnd_global.local_chr(10)
3835 ||', p_process_flag -> '||p_process_flag ||fnd_global.local_chr(10)
3836 ||', p_process_message -> '||p_process_message ||fnd_global.local_chr(10)
3837 );*/
3838
3839
3840 lv_process_flag := jai_constants.successful ;
3841 lv_process_message := null ;
3842
3843 p_process_flag := lv_process_flag ;
3844 p_process_message := lv_process_message ;
3845
3846 ln_user_id := fnd_global.user_id ;
3847 ln_login_id := fnd_global.login_id ;
3848
3849 /*******
3850 || Get the tax_det_id - primary key for
3851 || the table jai_rgm_taxes
3852 *******/
3853 OPEN cur_get_tax_det_id ;
3854 FETCH cur_get_tax_det_id INTO ln_tax_det_id;
3855 CLOSE cur_get_tax_det_id;
3856
3857 /*################################################################################################################
3858 || DETERMINE THE VALUE FOR TAX EXEMPTION FLAG
3859 ################################################################################################################*/
3860
3861 /*******
3862 || Get the orig_ta_rate of the tax_id
3863 || the table jai_rgm_taxes
3864 *******/
3865 OPEN cur_get_exemption_value;
3866 FETCH cur_get_exemption_value INTO ln_orig_tax_rate;
3867 CLOSE cur_get_exemption_value;
3868
3869 /********************************************************************************************
3870 || Determine the tax exemption flag - this value would classify a tax as being of
3871 || Standard Rate ('SR'), Lower Rate ('LR') or Zero Rate ('ZR').
3872 || TCS reports would group by this and query
3873 ||=====================================================================================
3874 || || RATE || ORIG_TAX_PERCENTAGE|| Exempted_flag ||
3875 || ||-------||--------------------||---------------||
3876 || Standard Rate || 10 || 10 or Null || 'SR' ||
3877 || Lower Rate || 5 || 10 || 'LR' ||
3878 || Zero Rate || 0 || Null or not null || 'ZR' ||
3879 ||=====================================================================================
3880 **********************************************************************************************/
3881 IF ln_orig_tax_rate IS NULL OR
3882 ln_orig_tax_rate = p_tax_rate
3883 THEN
3884
3885 ln_exempted_flag := jai_constants.tax_exmpt_flag_std_rate ;
3886
3887 ELSIF ln_orig_tax_rate > p_tax_rate THEN /*rchandan for bug#4742259*/
3888
3889 ln_exempted_flag := jai_constants.tax_exmpt_flag_lower_rate ;
3890
3891 ELSIF p_tax_rate = 0 THEN
3892 ln_exempted_flag := jai_constants.tax_exmpt_flag_zero_rate ;
3893 END IF;
3894
3895
3896
3897 /*
3898 || Determine the Tax modified by flag flag in case it is null
3899 */
3900 ln_tax_modified_by := nvl(p_tax_modified_by ,jai_constants.tax_modified_by_system );
3901
3902 /*commented by csahoo for bug# 6401388
3903 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3904 pv_log_msg => ' VALUES insert into JAI_RGM_TAXES are : - ' ||fnd_global.local_chr(10)
3905 ||', tax_det_id -> '||ln_tax_det_id ||fnd_global.local_chr(10)
3906 ||', trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
3907 ||', tax_type -> '||p_tax_type ||fnd_global.local_chr(10)
3908 ||', tax_amt -> '||round(nvl(p_tax_amount,0)) ||fnd_global.local_chr(10)
3909 ||', tax_id -> '||p_tax_id ||fnd_global.local_chr(10)
3910 ||', func_tax_amt -> '||round(nvl(p_func_tax_amount,0)) ||fnd_global.local_chr(10)
3911 ||', currency_code -> '||p_currency_code ||fnd_global.local_chr(10)
3912 ||', exempted_flag -> '||ln_exempted_flag ||fnd_global.local_chr(10)
3913 ||', tax_modified_by -> '||ln_tax_modified_by ||fnd_global.local_chr(10)
3914 ||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
3918 ||', last_update_login -> '||ln_login_id
3915 ||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
3916 ||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
3917 ||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
3919 );*/
3920
3921
3922 INSERT into jai_rgm_taxes (
3923 tax_det_id ,
3924 trx_ref_id ,
3925 tax_type ,
3926 tax_amt ,
3927 tax_id ,
3928 tax_rate ,
3929 func_tax_amt ,
3930 currency_code ,
3931 tax_modified_by ,
3932 exempted_flag ,
3933 created_by ,
3934 creation_date ,
3935 last_updated_by ,
3936 last_update_date ,
3937 last_update_login
3938 )
3939 VALUES (
3940 ln_tax_det_id ,
3941 p_trx_ref_id ,
3942 p_tax_type ,
3943 round(nvl(p_tax_amount,0)) ,
3944 p_tax_id ,
3945 p_tax_rate ,
3946 round(nvl(p_func_tax_amount,0)) ,
3947 p_currency_code ,
3948 ln_tax_modified_by ,
3949 ln_exempted_flag ,
3950 ln_user_id ,
3951 sysdate ,
3952 ln_user_id ,
3953 sysdate ,
3954 ln_login_id
3955 );
3956
3957 /*commented by csahoo for bug# 6401388
3958 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3959 pv_log_msg => ' Insert successful'
3960 );*/
3961 UPDATE
3962 jai_rgm_refs_all
3963 SET
3964 total_tax_amt = nvl(total_tax_amt,0) + nvl(p_tax_amount,0)
3965 WHERE
3966 trx_ref_id = p_trx_ref_id;
3967
3968 /*commented by csahoo for bug# 6401388
3969 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3970 pv_log_msg => ' jai_rgm_refs_all.total_tax_amt successfully updated '
3971 );*/
3972 /*commented by csahoo for bug# 6401388
3973 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3974 pv_log_msg => '**************** END OF INSERT_REPOSITORY_TAXES ****************'
3975 );
3976 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
3977 END insert_repository_taxes ;
3978
3979
3980 PROCEDURE copy_taxes_from_source ( p_source_document_type IN JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ,
3981 p_source_document_id IN JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ,
3982 p_source_document_line_id IN JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE DEFAULT NULL ,
3983 p_apportion_factor IN NUMBER DEFAULT NULL ,
3984 p_trx_ref_id IN JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ,
3985 p_process_flag OUT NOCOPY VARCHAR2 ,
3986 p_process_message OUT NOCOPY VARCHAR2
3987 )
3988 IS
3989
3990 ln_reg_id NUMBER;
3991 /*****
3992 ||Get the Invoice tax details
3993 ******/
3994 CURSOR cur_get_inv_taxes ( cp_customer_trx_id JAI_AR_TRX_LINES.CUSTOMER_TRX_ID%TYPE ,
3995 cp_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE
3996 )
3997 IS
3998 SELECT
3999 jrcttl.customer_trx_line_id ,
4000 jrcttl.tax_id ,
4001 jrcttl.tax_rate ,
4002 jtc.tax_type ,
4003 jrcttl.tax_amount ,
4004 jrcttl.func_tax_amount ,
4005 jrct.invoice_currency_code currency_code
4006 FROM
4010 JAI_CMN_TAXES_ALL jtc ,
4007 JAI_AR_TRXS jrct ,
4008 JAI_AR_TRX_LINES jrctl ,
4009 JAI_AR_TRX_TAX_LINES jrcttl ,
4011 jai_regime_tax_types_v jrttv
4012 WHERE
4013 jrct.customer_trx_id = cp_customer_trx_id
4014 AND jrct.customer_trx_id = jrctl.customer_trx_id
4015 AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
4016 AND jrctl.customer_trx_line_id = nvl( cp_customer_trx_line_id , jrctl.customer_trx_line_id )
4017 AND jrcttl.tax_id = jtc.tax_id
4018 AND jrttv.tax_type = jtc.tax_type
4019 AND jrttv.regime_code = jai_constants.tcs_regime;
4020
4021
4022 /*****
4023 ||Get the receipt tax details
4024 *****/
4025 CURSOR cur_get_rcpt_taxes (cp_source_doc_id jai_cmn_document_taxes.SOURCE_DOC_ID%TYPE )
4026 IS
4027 SELECT
4028 jdt.tax_id ,
4029 jdt.tax_rate ,
4030 jdt.tax_type ,
4031 jdt.tax_amt ,
4032 jdt.func_tax_amt ,
4033 jdt.currency_code
4034 FROM
4035 jai_cmn_document_taxes jdt ,
4036 jai_regime_tax_types_v jrttv
4037 WHERE
4038 jdt.tax_type = jrttv.tax_type
4039 AND jdt.source_doc_id = cp_source_doc_id
4040 AND jrttv.regime_code = jai_constants.tcs_regime
4041 AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
4042
4043 lv_process_flag VARCHAR2(2) ;
4044 lv_process_message VARCHAR2(2000) ;
4045 ln_apportion_factor NUMBER ;
4046 BEGIN
4047 /*########################################################################################################
4048 || VARIABLES INITIALIZATION
4049 ########################################################################################################*/
4050 lv_member_name := 'COPY_TAXES_FROM_SOURCE';
4051 set_debug_context;
4052 /*commented by csahoo for bug# 6401388
4053 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
4054 , pn_reg_id => ln_reg_id
4055 );*/
4056
4057 /*commented by csahoo for bug# 6401388
4058 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4059 pv_log_msg => ' PARAMETERS VALUES PASSED TO COPY_TAXES_FROM_SOURCE : - ' ||fnd_global.local_chr(10)
4060 ||', p_source_document_type -> '||p_source_document_type ||fnd_global.local_chr(10)
4061 ||', p_source_document_id -> '||p_source_document_id ||fnd_global.local_chr(10)
4062 ||', p_source_document_line_id -> '||p_source_document_line_id ||fnd_global.local_chr(10)
4063 ||', p_apportion_factor -> '||p_apportion_factor ||fnd_global.local_chr(10)
4064 ||', p_trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
4065 );*/
4066
4067 lv_process_flag := jai_constants.successful ;
4068 lv_process_message := null ;
4069
4070 p_process_flag := lv_process_flag ;
4071 p_process_message := lv_process_message ;
4072
4073 ln_apportion_factor:= nvl(p_apportion_factor,1);
4074
4075
4076 /*########################################################################################################
4077 || Default taxes from Invoice
4078 ########################################################################################################*/
4079
4080
4081 IF p_source_document_type IN ( --------------------------A1
4082 jai_constants.trx_type_inv_comp , /* From Invoice completion */
4083 jai_constants.trx_type_inv_incomp , /* From Invoice Incompletion */
4084 jai_constants.ar_invoice_type_inv , /* From Application */
4085 jai_constants.ar_doc_type_dm , /* From Application */
4086 jai_constants.ar_invoice_type_cm /* From Application */
4087 )
4088 THEN
4089 /***
4090 || Source is INVOICE/DM/CM
4091 || Loop through each tax line of the invoice to hit
4092 || the TCS tax repository table jai_rgm_taxes
4093 ***/
4094
4095 /*commented by csahoo for bug# 6401388
4096 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4097 pv_log_msg => ' copy TAXES From JAI_AR_TRX_TAX_LINES - p_source_document_type -> '||p_source_document_type
4098 );*/
4099 FOR rec_cur_get_inv_taxes IN cur_get_inv_taxes ( cp_customer_trx_id => p_source_document_id ,
4100 cp_customer_trx_line_id => p_source_document_line_id
4101 )
4102 LOOP
4103
4104 insert_repository_taxes (
4105 p_trx_ref_id => p_trx_ref_id ,
4106 p_tax_id => rec_cur_get_inv_taxes.tax_id ,
4110 p_func_tax_amount => rec_cur_get_inv_taxes.func_tax_amount * ln_apportion_factor ,
4107 p_tax_type => rec_cur_get_inv_taxes.tax_type ,
4108 p_tax_rate => rec_cur_get_inv_taxes.tax_rate ,
4109 p_tax_amount => rec_cur_get_inv_taxes.tax_amount * ln_apportion_factor ,
4111 p_currency_code => rec_cur_get_inv_taxes.currency_code ,
4112 p_process_flag => lv_process_flag ,
4113 p_process_message => lv_process_message
4114 );
4115
4116 IF lv_process_flag = jai_constants.expected_error OR ---------A2
4117 lv_process_flag = jai_constants.unexpected_error OR
4118 lv_process_flag = jai_constants.not_applicable
4119 THEN
4120 /*
4121 || As Returned status is an error/not applicable hence:-
4122 || Set out variables p_process_flag and p_process_message accordingly
4123 */
4124 --call to debug package
4125 /*commented by csahoo for bug# 6401388
4126 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4127 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
4128 );*/
4129
4130 p_process_flag := lv_process_flag ;
4131 p_process_message := lv_process_message ;
4132 return;
4133 END IF; ---------A2
4134 END LOOP;
4135
4136 ELSIF p_source_document_type = jai_constants.ar_cash_tax_confirmed THEN
4137 /*
4138 ||Source is receipt
4139 */
4140 /*commented by csahoo for bug# 6401388
4141 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4142 pv_log_msg => ' copy TAXES From jai_cmn_document_taxes - p_source_document_id -> '||p_source_document_id
4143 ||', p_source_document_type -> '||p_source_document_type
4144 );*/
4145 FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes (cp_source_doc_id => p_source_document_id )
4146 LOOP
4147
4148 insert_repository_taxes (
4149 p_trx_ref_id => p_trx_ref_id ,
4150 p_tax_id => rec_cur_get_rcpt_taxes.tax_id ,
4151 p_tax_type => rec_cur_get_rcpt_taxes.tax_type ,
4152 p_tax_rate => rec_cur_get_rcpt_taxes.tax_rate ,
4153 p_tax_amount => rec_cur_get_rcpt_taxes.tax_amt * ln_apportion_factor ,
4154 p_func_tax_amount => rec_cur_get_rcpt_taxes.func_tax_amt * ln_apportion_factor ,
4155 p_currency_code => rec_cur_get_rcpt_taxes.currency_code ,
4156 p_process_flag => lv_process_flag ,
4157 p_process_message => lv_process_message
4158 );
4159
4160 IF lv_process_flag = jai_constants.expected_error OR ---------A3
4161 lv_process_flag = jai_constants.unexpected_error OR
4162 lv_process_flag = jai_constants.not_applicable
4163 THEN
4164 /*
4165 || As Returned status is an error/not applicable hence:-
4166 || Set out variables p_process_flag and p_process_message accordingly
4167 */
4168 --call to debug package
4169 /*commented by csahoo for bug# 6401388
4170 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4171 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
4172 );*/
4173
4174 p_process_flag := lv_process_flag ;
4175 p_process_message := lv_process_message ;
4176 return;
4177 END IF; ---------A3
4178 END LOOP;
4179
4180 END IF; --------------------------A1
4181
4182 /*commented by csahoo for bug# 6401388
4183 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4184 pv_log_msg => '****************COPY_TAXES_FROM_SOURCE ENDS SUCCESSFULLY ****************'
4185 );
4186 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
4187 END copy_taxes_from_source;
4188
4189 PROCEDURE copy_references ( p_parent_transaction_id IN JAI_RGM_REFS_ALL.PARENT_TRANSACTION_ID%TYPE DEFAULT NULL ,
4190 p_new_document_id IN JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_ID%TYPE ,
4191 p_new_document_type IN JAI_RGM_REFS_ALL.SOURCE_DOCUMENT_TYPE%TYPE ,
4192 p_new_document_date IN DATE ,
4196 )
4193 p_apportion_factor IN NUMBER DEFAULT 1 ,
4194 p_process_flag OUT NOCOPY VARCHAR2 ,
4195 p_process_message OUT NOCOPY VARCHAR2
4197 IS
4198
4199 ln_reg_id NUMBER;
4200 CURSOR cur_get_refs
4201 IS
4202 SELECT
4203 *
4204 FROM
4205 jai_rgm_refs_all
4206 WHERE
4207 transaction_id = p_parent_transaction_id;
4208
4209 CURSOR cur_get_rgm_taxes ( cp_trx_ref_id JAI_RGM_TAXES.TRX_REF_ID%TYPE )
4210 IS
4211 SELECT
4212 *
4213 FROM
4214 jai_rgm_taxes
4215 WHERE
4216 trx_ref_id = cp_trx_ref_id;
4217
4218 rec_cur_get_refs CUR_GET_REFS%ROWTYPE ;
4219 ln_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ;
4220 ln_tax_det_id JAI_RGM_TAXES.TAX_DET_ID%TYPE ;
4221 ln_apportion_ratio NUMBER ;
4222 ln_user_id JAI_RGM_REFS_ALL.CREATED_BY%TYPE ;
4223 ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
4224 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
4225 lv_org_tan_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
4226 ln_threshold_slab_id JAI_RGM_REFS_ALL.THRESHOLD_SLAB_ID%TYPE ;
4227 lv_process_flag VARCHAR2(2) ;
4228 lv_process_message VARCHAR2(4000) ;
4229 BEGIN
4230
4231 /*################################################################################################################
4232 || Initialize the variables
4233 ################################################################################################################*/
4234
4235 lv_member_name := 'COPY_REFERENCES';
4236 set_debug_context;
4237 /*commented by csahoo for bug# 6401388
4238 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
4239 , pn_reg_id => ln_reg_id
4240 );*/
4241
4242 /*commented by csahoo for bug# 6401388
4243 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4244 pv_log_msg => ' PARAMETERS VALUES PASSED TO COPY_REFERENCES : - ' ||fnd_global.local_chr(10)
4245 ||', p_parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
4246 ||', p_new_document_id -> '||p_new_document_id ||fnd_global.local_chr(10)
4247 ||', p_new_document_type -> '||p_new_document_type ||fnd_global.local_chr(10)
4248 ||', p_new_document_date -> '||p_new_document_date ||fnd_global.local_chr(10)
4249 ||', p_apportion_factor -> '||p_apportion_factor ||fnd_global.local_chr(10)
4250 );*/
4251
4252 lv_process_flag := jai_constants.successful ;
4253 lv_process_message := null ;
4254
4255 p_process_flag := lv_process_flag ;
4256 p_process_message := lv_process_message ;
4257
4258 ln_user_id := fnd_global.user_id ;
4259 ln_login_id := fnd_global.login_id ;
4260
4261
4262 /*########################################################################################################
4263 || POPULATE JAI_RGM_REFS_ALL ---- PART -2
4264 ########################################################################################################*/
4265
4266 /*
4267 ||Get the sequence generated unique key for the transaction
4268 */
4269 OPEN cur_get_transaction_id ;
4270 FETCH cur_get_transaction_id INTO ln_transaction_id ;
4271 CLOSE cur_get_transaction_id ;
4272
4273 FOR rec_cur_get_refs IN cur_get_refs
4274 LOOP
4275
4276 /*
4277 ||Header needs to be copied from source
4278 */
4279 OPEN cur_get_trx_ref_id ;
4280 FETCH cur_get_trx_ref_id INTO ln_trx_ref_id ;
4281 CLOSE cur_get_trx_ref_id ;
4282
4283
4284 /*commented by csahoo for bug# 6401388
4285 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4286 pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
4287 ||', regime_id -> '||rec_cur_get_refs.regime_id ||fnd_global.local_chr(10)
4288 ||', trx_ref_id -> '||ln_trx_ref_id ||fnd_global.local_chr(10)
4289 ||', transaction_id -> '||ln_transaction_id ||fnd_global.local_chr(10)
4290 ||', parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
4291 ||', org_tan_no -> '||rec_cur_get_refs.org_tan_no ||fnd_global.local_chr(10)
4292 ||', source_document_id -> '||p_new_document_id ||fnd_global.local_chr(10)
4296 ||', source_table_name -> '||rec_cur_get_refs.source_table_name ||fnd_global.local_chr(10)
4293 ||', source_document_line_id -> '||p_new_document_id ||fnd_global.local_chr(10)
4294 ||', source_document_type -> '||p_new_document_type ||fnd_global.local_chr(10)
4295 ||', source_document_date -> '||p_new_document_date ||fnd_global.local_chr(10)
4297 ||', line_amt -> '||p_apportion_factor * rec_cur_get_refs.line_amt ||fnd_global.local_chr(10)
4298 ||', source_document_amt -> '||p_apportion_factor * rec_cur_get_refs.source_document_amt ||fnd_global.local_chr(10)
4299 ||', total_tax_amt -> '||p_apportion_factor * rec_cur_get_refs.total_tax_amt ||fnd_global.local_chr(10)
4300 ||', source_ref_document_id -> '||rec_cur_get_refs.source_ref_document_id ||fnd_global.local_chr(10)
4301 ||', source_ref_document_type -> '||rec_cur_get_refs.source_ref_document_type ||fnd_global.local_chr(10)
4302 ||', app_from_document_id -> '||rec_cur_get_refs.app_from_document_id ||fnd_global.local_chr(10)
4303 ||', app_from_document_type -> '||rec_cur_get_refs.app_from_document_type ||fnd_global.local_chr(10)
4304 ||', app_to_document_id -> '||rec_cur_get_refs.app_to_document_id ||fnd_global.local_chr(10)
4305 ||', app_to_document_type -> '||rec_cur_get_refs.app_to_document_type ||fnd_global.local_chr(10)
4306 ||', party_id -> '||rec_cur_get_refs.party_id ||fnd_global.local_chr(10)
4307 ||', party_type -> '||rec_cur_get_refs.party_type ||fnd_global.local_chr(10)
4308 ||', party_site_id -> '||rec_cur_get_refs.party_site_id ||fnd_global.local_chr(10)
4309 ||', item_classification -> '||rec_cur_get_refs.item_classification ||fnd_global.local_chr(10)
4310 ||', org_id -> '||rec_cur_get_refs.org_id ||fnd_global.local_chr(10)
4311 ||', organization_id -> '||rec_cur_get_refs.organization_id ||fnd_global.local_chr(10)
4312 ||', fin_year -> '||rec_cur_get_refs.fin_year ||fnd_global.local_chr(10)
4313 ||', threshold_slab_id -> '||NULL ||fnd_global.local_chr(10)
4314 ||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
4315 ||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
4316 ||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
4317 ||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
4318 ||', last_update_login -> '||ln_login_id ||fnd_global.local_chr(10)
4319 ||', settlement_id -> '||NULL
4320 ||', certificate_id -> '||NULL
4321 );*/
4322
4323 INSERT into jai_rgm_refs_all (
4324 trx_ref_id ,
4325 regime_id ,
4326 transaction_id ,
4327 parent_transaction_id ,
4328 org_tan_no ,
4329 source_document_id ,
4330 source_document_line_id ,
4331 source_document_type ,
4332 source_document_date ,
4333 source_table_name ,
4334 line_amt ,
4335 source_document_amt ,
4336 total_tax_amt ,
4340 app_from_document_type ,
4337 source_ref_document_id ,
4338 source_ref_document_type ,
4339 app_from_document_id ,
4341 app_to_document_id ,
4342 app_to_document_type ,
4343 party_id ,
4344 party_type ,
4345 party_site_id ,
4346 item_classification ,
4347 org_id ,
4348 organization_id ,
4349 fin_year ,
4350 threshold_slab_id ,
4351 created_by ,
4352 creation_date ,
4353 last_updated_by ,
4354 last_update_date ,
4355 last_update_login ,
4356 settlement_id ,
4357 certificate_id
4358 )
4359 VALUES (
4360 ln_trx_ref_id ,
4361 rec_cur_get_refs.regime_id ,
4362 ln_transaction_id ,
4363 p_parent_transaction_id ,
4364 rec_cur_get_refs.org_tan_no ,
4365 p_new_document_id ,
4366 p_new_document_id ,
4367 p_new_document_type ,
4368 p_new_document_date ,
4369 rec_cur_get_refs.source_table_name ,
4370 p_apportion_factor * rec_cur_get_refs.line_amt ,
4371 p_apportion_factor * rec_cur_get_refs.source_document_amt ,
4372 p_apportion_factor * rec_cur_get_refs.total_tax_amt ,
4373 rec_cur_get_refs.source_ref_document_id ,
4374 rec_cur_get_refs.source_ref_document_type ,
4375 rec_cur_get_refs.app_from_document_id ,
4376 rec_cur_get_refs.app_from_document_type ,
4377 rec_cur_get_refs.app_to_document_id ,
4378 rec_cur_get_refs.app_to_document_type ,
4379 rec_cur_get_refs.party_id ,
4380 rec_cur_get_refs.party_type ,
4381 rec_cur_get_refs.party_site_id ,
4382 rec_cur_get_refs.item_classification ,
4383 rec_cur_get_refs.org_id ,
4384 rec_cur_get_refs.organization_id ,
4385 rec_cur_get_refs.fin_year ,
4386 NULL ,
4387 ln_user_id ,
4388 sysdate ,
4389 ln_user_id ,
4390 sysdate ,
4391 ln_login_id ,
4395
4392 NULL ,
4393 NULL
4394 );
4396 /*commented by csahoo for bug# 6401388
4397 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4398 pv_log_msg => ' After Insert into jai_rgm_refs_all'
4399 );*/
4400
4401 /*
4402 ||Taxes needs to be copied from source
4403 */
4404 FOR rec_cur_get_rgm_taxes IN cur_get_rgm_taxes ( cp_trx_ref_id => rec_cur_get_refs.trx_ref_id )
4405 LOOP
4406 /*******
4407 || Get the tax_det_id - primary key for
4408 || the table jai_rgm_taxes
4409 *******/
4410 OPEN cur_get_tax_det_id ;
4411 FETCH cur_get_tax_det_id INTO ln_tax_det_id;
4412 CLOSE cur_get_tax_det_id;
4413
4414 /*commented by csahoo for bug# 6401388
4415 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4416 pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
4417 ||', tax_det_id -> '||ln_tax_det_id ||fnd_global.local_chr(10)
4418 ||', trx_ref_id -> '||ln_trx_ref_id ||fnd_global.local_chr(10)
4419 ||', tax_type -> '||rec_cur_get_rgm_taxes.tax_type ||fnd_global.local_chr(10)
4420 ||', tax_amt -> '||round( p_apportion_factor * rec_cur_get_rgm_taxes.tax_amt ) ||fnd_global.local_chr(10)
4421 ||', tax_id -> '||rec_cur_get_rgm_taxes.tax_id ||fnd_global.local_chr(10)
4422 ||', func_tax_amt -> '||round(p_apportion_factor * rec_cur_get_rgm_taxes.func_tax_amt ) ||fnd_global.local_chr(10)
4423 ||', currency_code -> '||rec_cur_get_rgm_taxes.currency_code ||fnd_global.local_chr(10)
4424 ||', exempted_flag -> '||rec_cur_get_rgm_taxes.exempted_flag ||fnd_global.local_chr(10)
4425 ||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
4426 ||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
4427 ||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
4428 ||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
4429 ||', last_update_login -> '||ln_user_id
4430 );*/
4431
4432 INSERT into jai_rgm_taxes (
4433 tax_det_id ,
4434 trx_ref_id ,
4435 tax_type ,
4436 tax_amt ,
4437 tax_id ,
4438 tax_rate ,
4439 func_tax_amt ,
4440 currency_code ,
4441 tax_modified_by ,
4442 exempted_flag ,
4443 created_by ,
4444 creation_date ,
4445 last_updated_by ,
4446 last_update_date ,
4447 last_update_login
4448 )
4449 VALUES (
4450 ln_tax_det_id ,
4451 ln_trx_ref_id ,
4452 rec_cur_get_rgm_taxes.tax_type ,
4453 round( p_apportion_factor * rec_cur_get_rgm_taxes.tax_amt ) ,
4454 rec_cur_get_rgm_taxes.tax_id ,
4455 rec_cur_get_rgm_taxes.tax_rate ,
4456 round(p_apportion_factor * rec_cur_get_rgm_taxes.func_tax_amt ) ,
4457 rec_cur_get_rgm_taxes.currency_code ,
4461 sysdate ,
4458 rec_cur_get_rgm_taxes.tax_modified_by ,
4459 rec_cur_get_rgm_taxes.exempted_flag ,
4460 ln_user_id ,
4462 ln_user_id ,
4463 sysdate ,
4464 ln_user_id
4465 );
4466
4467
4468
4469 /*commented by csahoo for bug# 6401388
4470 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4471 pv_log_msg => ' After Insert into jai_rgm_taxes '
4472 );*/
4473 END LOOP; /* End of tax population */
4474 END LOOP; /*End of jai_rgm_refs_all population */
4475
4476 /*commented by csahoo for bug# 6401388
4477 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4478 pv_log_msg => '**************** END OF COPY_REFERENCES ****************'
4479 );
4480
4481 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
4482 END copy_references;
4483
4484
4485 PROCEDURE update_item_gen_docs ( p_trx_number IN RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE ,
4486 p_customer_trx_id IN RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE ,
4487 p_complete_flag IN RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG%TYPE ,
4488 p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
4489 p_process_flag OUT NOCOPY VARCHAR2 ,
4490 p_process_message OUT NOCOPY VARCHAR2
4491 )
4492 IS
4493 ln_reg_id NUMBER ;
4494
4495 CURSOR cur_upd_gen_docs
4496 IS
4497 SELECT
4498 jrigd.rowid ,
4499 jrigd.generated_doc_id
4500 FROM
4501 jai_rgm_item_gen_docs jrigd,
4502 jai_rgm_refs_all jrra
4503 WHERE
4504 jrigd.generated_doc_trx_number = p_trx_number
4505 AND jrra.transaction_id = jrigd.transaction_id
4506 AND jrra.org_id = p_org_id
4507 FOR UPDATE OF jrigd.generated_doc_id NOWAIT;
4508
4509 lv_rowid ROWID ;
4510 ln_generated_doc_id JAI_RGM_ITEM_GEN_DOCS.GENERATED_DOC_ID%TYPE ;
4511 ln_user_id JAI_RGM_REFS_ALL.CREATED_BY%TYPE ;
4512 ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
4513
4514 BEGIN
4515
4516 /*################################################################################################################
4517 || Initialize the variables
4518 ################################################################################################################*/
4519
4520 lv_member_name := 'UPDATE_ITEM_GEN_DOCS';
4521 set_debug_context;
4522 /*commented by csahoo for bug# 6401388
4523 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
4524 pn_reg_id => ln_reg_id
4525 );
4526
4527 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4528 pv_log_msg => 'VALUES PASSED TO UPDATE_ITEM_GEN_DOCS ARE :- ' ||fnd_global.local_chr(10)
4529 ||', p_trx_number -> '||p_trx_number ||fnd_global.local_chr(10)
4530 ||', p_customer_trx_id -> '||p_customer_trx_id ||fnd_global.local_chr(10)
4531 ||', p_complete_flag -> '||p_complete_flag ||fnd_global.local_chr(10)
4532 ||', p_org_id -> '||p_org_id
4533 );*/
4534
4535 p_process_flag := jai_constants.successful ;
4536 p_process_message := null ;
4537
4538 ln_user_id := fnd_global.user_id ;
4539 ln_login_id := fnd_global.login_id ;
4540
4541 /*################################################################################################################
4542 || UPDATE THE TABLE JAI_RGM_ITEM_GEN_DOCS
4543 ################################################################################################################*/
4544
4545 OPEN cur_upd_gen_docs ;
4546 FETCH cur_upd_gen_docs INTO lv_rowid, ln_generated_doc_id;
4547
4548
4549 IF cur_upd_gen_docs%FOUND THEN
4550 IF p_complete_flag = jai_constants.yes THEN
4551 /*
4552 || Complete flag is 'Y', Invoice is getting COMPLETED
4553 || Set the generated_doc_id to null in case it is not null
4554 */
4555
4556 ln_generated_doc_id := p_customer_trx_id;
4557 ELSE
4558 /*
4559 || Complete flag is 'N', Invoice is getting INCOMPLETED
4560 */
4561 IF ln_generated_doc_id IS NOT NULL THEN
4562 /*
4563 || Set the generated_doc_id to null in case it is not null
4564 */
4565 ln_generated_doc_id := NULL;
4566 ELSE
4567 /*
4571 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4568 ||Do nothing if the generated_doc_id is null and invoice is getting incompleted
4569 */
4570 /*commented by csahoo for bug# 6401388
4572 pv_log_msg => 'Skip the TCS ITEM GENDOCS update as :- ' ||fnd_global.local_chr(10)
4573 ||', p_complete_flag -> '||p_complete_flag ||fnd_global.local_chr(10)
4574 ||', ln_generated_doc_id -> '||ln_generated_doc_id
4575 );*/
4576 p_process_flag := jai_constants.not_applicable;
4577 return;
4578 END IF;
4579 END IF;
4580
4581 /*commented by csahoo for bug# 6401388
4582 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4583 pv_log_msg => 'valid transaction record found in table jai_rgm_item_gen_docs. Updating the table jai_rgm_item_gen_docs with ' ||fnd_global.local_chr(10)
4584 ||', generated_doc_id -> '||ln_generated_doc_id
4585 );*/
4586 UPDATE jai_rgm_item_gen_docs
4587 SET
4588 generated_doc_id = ln_generated_doc_id ,
4589 last_updated_by = ln_user_id ,
4590 last_update_date = sysdate ,
4591 last_update_login = ln_login_id
4592 WHERE
4593 rowid = lv_rowid;
4594 END IF;
4595 CLOSE cur_upd_gen_docs;
4596
4597 /*commented by csahoo for bug# 6401388
4598 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4599 pv_log_msg => '**************** END OF UPDATE_ITEM_GEN_DOCS ****************'
4600 );
4601
4602 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
4603
4604 END update_item_gen_docs;
4605
4606
4607 PROCEDURE generate_document (
4608 p_rgm_ref IN JAI_RGM_REFS_ALL%ROWTYPE ,
4609 p_total_tax_amt IN NUMBER ,
4610 p_process_flag OUT NOCOPY VARCHAR2 ,
4611 p_process_message OUT NOCOPY VARCHAR2
4612 )
4613 IS
4614 ln_reg_id NUMBER ;
4615 ln_msg_count NUMBER ;
4616 lv_msg_data VARCHAR2(2000) ;
4617 ln_customer_trx_id NUMBER ;
4618 lv_return_status VARCHAR2(80) ;
4619
4620 /*
4621 ||Customer would create a batch source with name TCS Debit Memo And TCS Credit Memo
4622 */
4623 CURSOR cur_get_batch_source ( cp_org_id JAI_RGM_REFS_ALL.ORG_ID%TYPE ,
4624 cp_name RA_BATCH_SOURCES_ALL.NAME%TYPE
4625 )
4626 IS
4627 SELECT
4628 bsa.batch_source_id ,
4629 bsa.default_inv_trx_type ,
4630 rctt.type ,
4631 rctt.name ,
4632 rctt.default_term ,
4633 rctt.gl_id_rec ,
4634 rctt.creation_sign
4635 FROM
4636 ra_batch_sources_all bsa ,
4637 ra_cust_trx_types_all rctt
4638 WHERE
4639 bsa.default_inv_trx_type = rctt.cust_trx_type_id
4640 AND bsa.org_id = rctt.org_id
4641 AND bsa.org_id = cp_org_id
4642 AND bsa.name = cp_name ;
4643
4644 CURSOR cur_get_part_det ( cp_party_id JAI_RGM_REFS_ALL.PARTY_ID%TYPE ,
4645 cp_party_site_id JAI_RGM_REFS_ALL.PARTY_SITE_ID%TYPE
4646 )
4647 IS
4648 SELECT
4649 hzcas.cust_acct_site_id bill_to_address_id
4650 FROM
4651 hz_cust_accounts hca ,
4652 hz_cust_acct_sites_all hzcas ,
4653 hz_cust_site_uses_all hzcsu
4654 WHERE
4655 hca.cust_account_id = hzcas.cust_account_id
4656 AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
4657 AND hzcsu.site_use_code = jai_constants.site_use_bill_to
4658 AND hca.cust_account_id = cp_party_id
4659 AND hzcsu.site_use_id = cp_party_site_id ;-- site_use_id is the party_site_id ;
4660
4661 CURSOR cur_get_sob ( cp_org_id jai_rgm_refs_all.org_id%TYPE )
4662 IS
4663 SELECT
4664 set_of_books_id
4665 FROM
4666 hr_operating_units
4667 WHERE
4668 organization_id = cp_org_id ;
4669
4670 lv_batch_src_dm JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
4671 lv_batch_src_cm JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
4672 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
4673 lv_batch_src_name JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE ;
4674 ln_term_id RA_CUST_TRX_TYPES_ALL.DEFAULT_TERM%TYPE ;
4675 ln_bill_to_address_id hz_cust_acct_sites_all.cust_acct_site_id%type ;
4676 lv_trx_number RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE ;
4677 ln_ccid_tax_type_tcs NUMBER ;
4678 rec_cur_get_batch_source CUR_GET_BATCH_SOURCE%ROWTYPE ;
4679 lv_set_of_books_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE ;
4683 lv_process_message VARCHAR2(4000) ;
4680 ln_amount NUMBER ;
4681 ln_user_id JAI_RGM_REFS_ALL.CREATED_BY%TYPE ;
4682 ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
4684
4685 BEGIN
4686
4687 /*################################################################################################################
4688 || Initialize the variables
4689 ################################################################################################################*/
4690
4691 lv_member_name := 'GENERATE_DOCUMENT';
4692 set_debug_context;
4693 /*commented by csahoo for bug# 6401388
4694 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
4695 pn_reg_id => ln_reg_id
4696 );
4697
4698 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4699 pv_log_msg => 'Parameter passed to GENERATE_DOCUMENT are -> ' ||fnd_global.local_chr(10)
4700 ||', transaction_id -> '||p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
4701 ||', p_total_tax_amt -> '||p_total_tax_amt ||fnd_global.local_chr(10)
4702 ||', source_document_type is -> '||p_rgm_ref.source_document_type ||fnd_global.local_chr(10)
4703 ||', org_id -> '||p_rgm_ref.org_id
4704 );*/
4705 lv_process_message:= null ;
4706 p_process_flag := jai_constants.successful ;
4707 p_process_message := lv_process_message ;
4708
4709 ln_user_id := fnd_global.user_id ;
4710 ln_login_id := fnd_global.login_id ;
4711
4712 /*################################################################################################################
4713 ||Skip the transaction if p_total_tax_amt is 0
4714 ################################################################################################################*/
4715 IF nvl(p_total_tax_amt,0) = 0 THEN
4716 /*commented by csahoo for bug# 6401388
4717 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4718 pv_log_msg => 'As transaction total_tax_amt is -> '||p_total_tax_amt||' hence skipping the transaction.'
4719 );*/
4720 p_process_flag := jai_constants.not_applicable ;
4721 return;
4722 END IF;
4723
4724 /*################################################################################################################
4725 ||Get batch source information
4726 ################################################################################################################*/
4727
4728 /*commented by csahoo for bug# 6401388
4729 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4730 pv_log_msg => ' Deriving the batch source information '
4731 );*/
4732
4733 /*
4734 ||Get the value for the Batch Source Debit Memo
4735 */
4736 OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.tcs_regime ,
4737 cp_attribute_code => jai_constants.batch_src_dm ,
4738 cp_organization_id => p_rgm_ref.organization_id
4739 );
4740 FETCH c_get_rgm_attribute INTO ln_regime_id ,lv_batch_src_dm;
4741 CLOSE c_get_rgm_attribute ;
4742
4743
4744 /*
4745 ||Get the value for the Batch Source Credit Memo
4746 */
4747 OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.tcs_regime ,
4748 cp_attribute_code => jai_constants.batch_src_cm ,
4749 cp_organization_id => p_rgm_ref.organization_id
4750 );
4751 FETCH c_get_rgm_attribute INTO ln_regime_id ,lv_batch_src_cm;
4752 CLOSE c_get_rgm_attribute ;
4753
4754
4755 /*################################################################################################################
4756 || Derive the batch source name based on the document type
4757 ################################################################################################################*/
4758
4759 /*commented by csahoo for bug# 6401388
4760 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4761 pv_log_msg => ' Deriving the batch source name based on the document type'
4762 );*/
4763
4764 IF p_rgm_ref.source_document_type in ( jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
4765 jai_constants.trx_type_rct_unapp , /* Receipt unapplication*/
4766 jai_constants.trx_type_cm_app /* CM application*/
4767 )
4768 THEN
4769 lv_batch_src_name := lv_batch_src_dm; /* TCS Debit Memo */
4770 lv_trx_number := jai_constants.tcs_dm_prefix; --'TCS-DM' ;
4771
4772 ELSIF p_rgm_ref.source_document_type in ( jai_constants.trx_type_rct_app ,
4773 jai_constants.trx_type_rct_rvs ,
4777 THEN
4774 jai_constants.trx_type_cm_unapp
4775 )
4776
4778 lv_batch_src_name := lv_batch_src_cm; /* TCS Credit Memo */
4779 lv_trx_number := jai_constants.tcs_cm_prefix; --'TCS-CM';
4780
4781 ELSIF p_rgm_ref.source_document_type = jai_constants.tcs_event_surcharge THEN
4782 /*
4783 ||Document generation is invoked by surcharge. Document type will be derrived from the sign of the document amount.
4784 ||If sign is +VE then it should be a Debit Memo, otherwise it should be a Credit Memo
4785 */
4786
4787 IF sign (p_total_tax_amt) = -1 THEN
4788
4789 /* Credit Memo */
4790 lv_batch_src_name := lv_batch_src_cm;
4791 lv_trx_number := jai_constants.tcs_cm_prefix; --'TCS-CM';
4792 ELSIF sign (p_total_tax_amt) = 1 THEN
4793
4794 /* Debit Memo */
4795 lv_batch_src_name := lv_batch_src_dm;
4796 lv_trx_number := jai_constants.tcs_dm_prefix; --'TCS-DM'
4797 END IF;
4798 ELSE
4799 /*
4800 ||Skip the transaction
4801 */
4802 /*commented by csahoo for bug# 6401388
4803 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4804 pv_log_msg => 'Skip the transaction '
4805 );*/
4806 p_process_flag := jai_constants.not_applicable;
4807 p_process_message := null;
4808 return ;
4809 END IF;
4810
4811 /*################################################################################################################
4812 ||VALIDATE BATCH SOURCES FOR TCS
4813 ################################################################################################################*/
4814
4815 /*
4816 || Error out if the batch source name is null i.e regime party setup for
4817 */
4818 IF lv_batch_src_name IS NULL THEN
4819 /*commented by csahoo for bug# 6401388
4820 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4821 pv_log_msg => 'Batch source definition has not be defined for the inventory organization '||p_rgm_ref.organization_id
4822 );*/
4823
4824 p_process_flag := jai_constants.expected_error;
4825 lv_process_message := 'Batch source definition has not be defined for the inventory organization '||p_rgm_ref.organization_id;
4826 p_process_message := lv_process_message ;
4827 return;
4828 END IF;
4829
4830
4831 /*commented by csahoo for bug# 6401388
4832 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4833 pv_log_msg => 'Document Type is lv_batch_src_name -> '||lv_batch_src_name
4834 );*/
4835
4836 OPEN cur_get_batch_source ( cp_org_id => p_rgm_ref.org_id ,
4837 cp_name => lv_batch_src_name
4838 );
4839
4840 FETCH cur_get_batch_source INTO rec_cur_get_batch_source;
4841 IF cur_get_batch_source%NOTFOUND THEN
4842 /*commented by csahoo for bug# 6401388
4843 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4844 pv_log_msg => 'TCS batch source has not been defined '
4845 );*/
4846 CLOSE cur_get_batch_source ;
4847 p_process_flag := jai_constants.expected_error;
4848 lv_process_message := 'TCS batch source has not been defined for '||lv_batch_src_name ||'. Cannot process transaction ';
4849 p_process_message := lv_process_message ;
4850 return;
4851 END IF;
4852 CLOSE cur_get_batch_source ;
4853
4854 /*commented by csahoo for bug# 6401388
4855 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4856 pv_log_msg => 'batch source details are:- ' ||fnd_global.local_chr(10)
4857 ||', lv_batch_src_name -> '||lv_batch_src_name ||fnd_global.local_chr(10)
4858 ||', rec_cur_get_batch_source.batch_source_id -> '||rec_cur_get_batch_source.batch_source_id ||fnd_global.local_chr(10)
4859 ||', rec_cur_get_batch_source.default_inv_trx_type -> '||rec_cur_get_batch_source.default_inv_trx_type ||fnd_global.local_chr(10)
4860 ||', rec_cur_get_batch_source.type -> '||rec_cur_get_batch_source.type ||fnd_global.local_chr(10)
4861 ||', rec_cur_get_batch_source.name -> '||rec_cur_get_batch_source.name ||fnd_global.local_chr(10)
4862 ||', rec_cur_get_batch_source.creation_sign -> '||rec_cur_get_batch_source.creation_sign
4863 );*/
4864 /*################################################################################################################
4865 || DERIVE THE TERM FOR DM'S ONLY
4866 ################################################################################################################*/
4867
4868 IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
4869 ln_term_id := rec_cur_get_batch_source.default_term;
4870 /*
4871 || Throw an error if the term has not been defined for the debit memo Transaction type .
4872 || This check is not required in case of credit memo
4873 */
4874 IF ln_term_id IS NULL THEN
4875 /*commented by csahoo for bug# 6401388
4879 p_process_flag := jai_constants.expected_error;
4876 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4877 pv_log_msg => 'Error:- Default term is not defined in the transction type -> '||rec_cur_get_batch_source.name
4878 );*/
4880 lv_process_message := 'Cannot process transaction. A default term needs to be defined FOR the Transaction TYPE '||rec_cur_get_batch_source.name ;
4881 p_process_message := lv_process_message ;
4882 return;
4883 END IF;
4884 END IF;
4885
4886 /*################################################################################################################
4887 || DERIVE THE SOB
4888 ################################################################################################################*/
4889
4890 OPEN cur_get_sob ( cp_org_id => p_rgm_ref.org_id );
4891 FETCH cur_get_sob INTO lv_set_of_books_id;
4892 CLOSE cur_get_sob ;
4893 /*
4894 ||Throw an error if the Set of books has not been defined
4895 */
4896 IF lv_set_of_books_id IS NULL THEN
4897 /*commented by csahoo for bug# 6401388
4898 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4899 pv_log_msg => 'Error:- Set of books not defined for org_id -> '||p_rgm_ref.org_id
4900 );*/
4901
4902 p_process_flag := jai_constants.expected_error;
4903 lv_process_message := 'Set of books not defined for the org id.';
4904 p_process_message := lv_process_message ;
4905 return;
4906 END IF;
4907
4908
4909 /*################################################################################################################
4910 || DERIVE THE ADDRESS
4911 ################################################################################################################*/
4912
4913 OPEN cur_get_part_det ( cp_party_id => p_rgm_ref.party_id ,
4914 cp_party_site_id => p_rgm_ref.party_site_id
4915 );
4916 FETCH cur_get_part_det INTO ln_bill_to_address_id;
4917 CLOSE cur_get_part_det ;
4918 /*
4919 ||Throw an error if the bill to address has not been defined
4920 */
4921
4922 IF ln_bill_to_address_id IS NULL THEN
4923
4924 /*commented by csahoo for bug# 6401388
4925 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4926 pv_log_msg => 'Error:- Bill to address not defined for the customer id -> '||p_rgm_ref.party_id
4927 ||' customer site -> '||p_rgm_ref.party_site_id
4928 );*/
4929
4930 p_process_flag := jai_constants.expected_error;
4931 lv_process_message := 'Bill to address not defined for the customer id -> '||p_rgm_ref.party_id ||' customer site -> '||p_rgm_ref.party_site_id;
4932 p_process_message := lv_process_message ;
4933 return;
4934 END IF;
4935
4936 lv_trx_number := lv_trx_number||p_rgm_ref.transaction_id ;
4937
4938 /*################################################################################################################
4939 || DERIVE THE SIGN OF THE APPLICATION AND SECONDARY DOCUMENT VALUE
4940 ################################################################################################################*/
4941
4942 /*
4943 ||Amount is :-
4944 || 1.+ve if the creation sign of the document is positive
4945 || 1.-ve if the creation sign of the document is -ve
4946 || If the sign is any sign then for a DM create a +ve amount and CM would ve created with a -ve amount
4947 */
4948 IF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_positive THEN
4949 ln_amount := abs(p_total_tax_amt) * 1;
4950
4951 ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_negative THEN
4952 ln_amount := abs(p_total_tax_amt) * -1;
4953
4954 ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_any THEN
4955
4956 IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
4957 ln_amount := abs(p_total_tax_amt) ;
4958
4959 ELSIF rec_cur_get_batch_source.type = jai_constants.ar_invoice_type_cm THEN
4960 ln_amount := abs(p_total_tax_amt) * -1;
4961 END IF;
4962 END IF;
4963
4964
4965 /*################################################################################################################
4966 || INSERT INTO RA_INTERFACE_LINES_ALL TABLE
4967 ################################################################################################################*/
4968
4969 /*commented by csahoo for bug# 6401388
4970 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
4971 pv_log_msg => 'Before inserting into the interface tables, Values are :- ' || fnd_global.local_chr(10)
4972 ||', interface_line_id -> '|| p_rgm_ref.transaction_id || fnd_global.local_chr(10)
4973 ||', i/p tax amount -> '|| p_total_tax_amt || fnd_global.local_chr(10)
4974 ||', document creation amount -> '|| ln_amount || fnd_global.local_chr(10)
4975 ||', description -> '|| lv_batch_src_name || fnd_global.local_chr(10)
4979 ||', trx_date -> '|| p_rgm_ref.source_document_date || fnd_global.local_chr(10)
4976 ||', orig_system_bill_customer_id -> '|| p_rgm_ref.party_id || fnd_global.local_chr(10)
4977 ||', orig_system_bill_address_id -> '|| ln_bill_to_address_id || fnd_global.local_chr(10)
4978 ||', set_of_books_id -> '|| lv_set_of_books_id || fnd_global.local_chr(10)
4980 ||', trx_number -> '|| lv_trx_number || fnd_global.local_chr(10)
4981 ||', batch_source_name -> '|| lv_batch_src_name || fnd_global.local_chr(10)
4982 ||', cust_trx_type_name -> '|| rec_cur_get_batch_source.name || fnd_global.local_chr(10)
4983 ||', line_type -> '|| jai_constants.line_type_line || fnd_global.local_chr(10)
4984 ||', conversion_rate -> '|| 1 || fnd_global.local_chr(10)
4985 ||', conversion_type -> '|| jai_constants.conversion_type_user || fnd_global.local_chr(10)
4986 ||', interface_line_context -> '|| lv_batch_src_name || fnd_global.local_chr(10)
4987 ||', interface_line_attribute2 -> '|| p_rgm_ref.transaction_id || fnd_global.local_chr(10)
4988 ||', currency_code -> '|| jai_constants.func_curr || fnd_global.local_chr(10)
4989 ||', primary_salesrep_id -> '|| -3 || fnd_global.local_chr(10)
4990 ||', tax_code -> '|| jai_constants.tax_code_localization || fnd_global.local_chr(10)
4991 ||', term_id -> '|| ln_term_id || fnd_global.local_chr(10)
4992 ||', warehouse_id -> '|| p_rgm_ref.organization_id || fnd_global.local_chr(10)
4993 ||', quantity -> '|| 1 || fnd_global.local_chr(10)
4994 ||', unit_selling_price -> '|| ln_amount || fnd_global.local_chr(10)
4995 ||', created_by -> '|| ln_user_id || fnd_global.local_chr(10)
4996 ||', creation_date -> '|| sysdate || fnd_global.local_chr(10)
4997 ||', last_updated_by -> '|| ln_user_id || fnd_global.local_chr(10)
4998 ||', last_update_date -> '|| sysdate || fnd_global.local_chr(10)
4999 ||', last_update_login -> '|| ln_login_id || fnd_global.local_chr(10)
5000 );*/
5001 INSERT INTO ra_interface_lines_all
5002 (
5003 interface_line_id ,
5004 amount ,
5005 description ,
5006 orig_system_bill_customer_id ,
5007 orig_system_bill_address_id ,
5008 set_of_books_id ,
5009 trx_date ,
5010 trx_number ,
5011 batch_source_name ,
5012 cust_trx_type_name ,
5013 line_type ,
5014 conversion_rate ,
5015 conversion_type ,
5016 interface_line_context ,
5017 interface_line_attribute2 ,
5018 currency_code ,
5019 primary_salesrep_id ,
5020 tax_code ,
5021 term_id ,
5022 warehouse_id ,
5023 org_id , -- Date 19-jun-2007 by sacsethi for bug 6137956
5024 quantity ,
5025 unit_selling_price ,
5026 created_by ,
5027 creation_date ,
5028 last_updated_by ,
5029 last_update_date ,
5030 last_update_login
5031 )
5032 VALUES (
5036 p_rgm_ref.party_id ,
5033 p_rgm_ref.transaction_id ,
5034 ln_amount ,
5035 lv_batch_src_name ,
5037 ln_bill_to_address_id ,
5038 lv_set_of_books_id ,
5039 p_rgm_ref.source_document_date ,
5040 lv_trx_number ,
5041 lv_batch_src_name ,
5042 rec_cur_get_batch_source.name ,
5043 jai_constants.line_type_line ,
5044 1 ,
5045 jai_constants.conversion_type_user ,
5046 lv_batch_src_name ,
5047 p_rgm_ref.transaction_id ,
5048 jai_constants.func_curr ,
5049 -3 ,
5050 jai_constants.tax_code_localization ,
5051 ln_term_id ,
5052 p_rgm_ref.organization_id ,
5053 p_rgm_ref.org_id , -- Date 19-jun-2007 by sacsethi for bug 6137956
5054 1 ,
5055 ln_amount ,
5056 ln_user_id ,
5057 sysdate ,
5058 ln_user_id ,
5059 sysdate ,
5060 ln_login_id
5061 );
5062
5063 /*commented by csahoo for bug# 6401388
5064 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5065 pv_log_msg => 'After Insert statement of the interface tables '
5066 );*/
5067
5068
5069 /*********************************************************************************************************
5070 || Get the code combination id from the Organization/Regime Registration setup
5071 || by calling the function jai_cmn_rgm_recording_pkg.get_account
5072 *********************************************************************************************************/
5073
5074 ln_ccid_tax_type_tcs := jai_cmn_rgm_recording_pkg.get_account (
5075 p_regime_id => p_rgm_ref.regime_id ,
5076 p_organization_type => jai_constants.orgn_type_io ,
5077 p_organization_id => p_rgm_ref.organization_id ,
5078 p_location_id => null ,
5079 p_tax_type => jai_constants.tax_type_tcs ,
5080 p_account_name => jai_constants.liability
5081 );
5082 IF ln_ccid_tax_type_tcs IS NULL THEN
5083 /**********************************************************************************************************
5084 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
5085 || This is an error condition and the current processing has to be stopped
5086 **********************************************************************************************************/
5087 /*commented by csahoo for bug# 6401388
5088 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5089 pv_log_msg => 'Invalid code combination of TCS tax Accounting'
5090 );*/
5091 p_process_flag := jai_constants.expected_error;
5092 lv_process_message := 'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup';
5093 p_process_message := lv_process_message ;
5094 rollback;
5095 return;
5096 END IF;
5097
5098 /*commented by csahoo for bug# 6401388
5099 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5100 pv_log_msg => 'Inserting values into ra_interface_distributions_all for REV row:- '||fnd_global.local_chr(10)
5101 ||', interface_line_id -> '|| p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
5102 ||', interface_line_context -> '|| lv_batch_src_name ||fnd_global.local_chr(10)
5103 ||', interface_line_attribute2 -> '|| p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
5104 ||', account_class -> '|| jai_constants.account_class_rev ||fnd_global.local_chr(10)
5105 ||', amount -> '|| ln_amount ||fnd_global.local_chr(10)
5106 ||', code_combination_id -> '|| ln_ccid_tax_type_tcs ||fnd_global.local_chr(10)
5107 ||', acctd_amount -> '|| ln_amount ||fnd_global.local_chr(10)
5111 ||', last_update_date -> '|| sysdate ||fnd_global.local_chr(10)
5108 ||', created_by -> '|| ln_user_id ||fnd_global.local_chr(10)
5109 ||', creation_date -> '|| sysdate ||fnd_global.local_chr(10)
5110 ||', last_updated_by -> '|| ln_user_id ||fnd_global.local_chr(10)
5112 ||', last_update_login -> '|| ln_login_id ||fnd_global.local_chr(10)
5113 ||', org_id -> '|| p_rgm_ref.org_id
5114 );*/
5115
5116 INSERT INTO ra_interface_distributions_all
5117 (
5118 interface_line_id ,
5119 interface_line_context ,
5120 interface_line_attribute2 ,
5121 account_class ,
5122 amount ,
5123 code_combination_id ,
5124 acctd_amount ,
5125 created_by ,
5126 creation_date ,
5127 last_updated_by ,
5128 last_update_date ,
5129 last_update_login ,
5130 org_id
5131 )
5132 Values (
5133 p_rgm_ref.transaction_id ,
5134 lv_batch_src_name ,
5135 p_rgm_ref.transaction_id ,
5136 jai_constants.account_class_rev ,
5137 ln_amount ,
5138 ln_ccid_tax_type_tcs ,
5139 ln_amount ,
5140 ln_user_id ,
5141 sysdate ,
5142 ln_user_id ,
5143 sysdate ,
5144 ln_login_id ,
5145 p_rgm_ref.org_id
5146 );
5147
5148
5149
5150 /*commented by csahoo for bug# 6401388
5151 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5152 pv_log_msg => 'Inserting values into jai_rgm_item_gen_docs :- ' ||fnd_global.local_chr(10)
5153 ||', transaction_id -> '||p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
5154 ||', source_document_id -> '||p_rgm_ref.source_document_id ||fnd_global.local_chr(10)
5155 ||', source_document_type -> '||p_rgm_ref.source_document_type ||fnd_global.local_chr(10)
5156 ||', item_classification -> '||p_rgm_ref.item_classification ||fnd_global.local_chr(10)
5157 ||', generated_doc_trx_number -> '||lv_trx_number ||fnd_global.local_chr(10)
5158 ||', generated_doc_id -> '||ln_customer_trx_id ||fnd_global.local_chr(10)
5159 ||', generated_doc_type -> '||rec_cur_get_batch_source.type ||fnd_global.local_chr(10)
5160 ||', generated_doc_amt -> '||ln_amount
5161 );*/
5162
5163 /*################################################################################################################
5164 || INSERT INTO JAI_RGM_ITEM_GEN_DOCS TABLE
5165 ################################################################################################################*/
5166
5167 INSERT INTO jai_rgm_item_gen_docs ( transaction_id ,
5168 source_document_id ,
5169 source_document_type ,
5170 item_classification ,
5171 generated_doc_trx_number ,
5172 generated_doc_id ,
5173 generated_doc_type ,
5174 generated_doc_amt ,
5175 created_by ,
5176 creation_date ,
5180 )
5177 last_updated_by ,
5178 last_update_date ,
5179 last_update_login
5181 VALUES ( p_rgm_ref.transaction_id ,
5182 p_rgm_ref.source_document_id ,
5183 p_rgm_ref.source_document_type ,
5184 p_rgm_ref.item_classification ,
5185 lv_trx_number ,
5186 ln_customer_trx_id ,
5187 rec_cur_get_batch_source.type ,
5188 ln_amount ,
5189 ln_user_id ,
5190 sysdate ,
5191 ln_user_id ,
5192 sysdate ,
5193 ln_login_id
5194 );
5195
5196 /*commented by csahoo for bug# 6401388
5197 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5198 pv_log_msg => 'Data successfully inserted into jai_rgm_item_gen_docs'
5199 );
5200
5201
5202 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
5203 EXCEPTION
5204 WHEN OTHERS THEN
5205 p_process_flag := jai_constants.unexpected_error;
5206 p_process_message := ' Unexpected error occured while processing jai_ar_tcs_rep_pkg.generate_document'||substr(SQLERRM,1,300) ;
5207 /*commented by csahoo for bug# 6401388
5208 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5209 pv_log_msg => 'Unexpected error occured while processing jai_ar_tcs_rep_pkg.generate_document -> '||substr(SQLERRM,1,300)
5210 );*/
5211
5212 END generate_document;
5213
5214 PROCEDURE process_transactions (
5215 p_event IN VARCHAR2 ,
5216 p_document_type IN VARCHAR2 Default Null ,
5217 p_ooh IN OE_ORDER_HEADERS_ALL%ROWTYPE Default Null ,
5218 p_ract IN RA_CUSTOMER_TRX_ALL%ROWTYPE Default Null ,
5219 p_acra IN AR_CASH_RECEIPTS_ALL%ROWTYPE Default Null ,
5220 p_araa IN AR_RECEIVABLE_APPLICATIONS_ALL%ROWTYPE Default Null ,
5221 p_process_flag OUT NOCOPY VARCHAR2 ,
5222 p_process_message OUT NOCOPY VARCHAR2
5223 )
5224 IS
5225
5226 ln_reg_id NUMBER;
5227 /* **************************************************************************
5228 Creation Date : 09-Sep-2006
5229 Created By : Aiyer
5230 Bug Number : 4742259
5231 Purpose : Validate and insert the TCS repository with appropriate transaction based entries
5232 Called From :
5233 Parameter Description :
5234 p_document_id - Unique identifier of the document:-
5235 1. customer_trx_id - Invoice/Credit Memo identifier
5236 2. cash_receipt_id - cash receipt Identifier
5237 3. ar_receivable_applications_id - Unique identifier for a Cash receipt /Credit Memo application to an Invoice/DM
5238
5239 p_document_type - Indicates the type of document eg
5240 1. INVOICE_COMPLETION - Invoice Completion
5241 2. CASH_TAX_CONFIRMED - Cash Receipt tax Confirmation
5242 3. CREDIT_MEMO_APPLICATION - CM application to Invoice
5243 4. CREDIT_MEMO_UNAPPLICATION - CM Invoice Unapplication
5244 5. RECEIPT_APPLICATION - Cash receipt application to Invoice
5245 6. RECEIPT_UNAPPLICATION - Cash receipt unapplication to Invoice
5246 7. RECEIPT_REVERSAL - Cash receipt reversal
5247
5248 p_process_flag
5249 p_process_message
5250 CHANGE HISTORY:
5251 S.No Date Author and Details
5252 ========================================
5253 1. 01-AUG-2008 JMEENA for bug#7277211
5254 Created new procedure process_sales_order and added code to call process_sales_order when p_event is BOOKED
5255
5256 ***************************************************************************/
5257
5258
5259 CURSOR cur_get_refs (cp_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE )
5260 IS
5261 SELECT
5262 *
5263 FROM
5264 jai_rgm_refs_all ref
5265 WHERE
5266 transaction_id = cp_transaction_id;
5267
5271 SELECT
5268
5269 CURSOR cur_get_total_tax (cp_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE )
5270 IS
5272 sum(nvl(jrt.func_tax_amt,0)) total_tax_amount
5273 FROM
5274 jai_rgm_refs_all jrra,
5275 jai_rgm_taxes jrt
5276 WHERE
5277 jrra.trx_ref_id = jrt.trx_ref_id
5278 AND jrra.transaction_id = cp_transaction_id;
5279
5280 rec_cur_get_refs CUR_GET_REFS%ROWTYPE ;
5281 ln_tax_tot_amt NUMBER ;
5282 lv_document_type VARCHAR2(100) ;
5283 lv_item_classification JAI_RGM_REFS_ALL.ITEM_CLASSIFICATION%TYPE ;
5284 lv_process_flag VARCHAR2(2) ;
5285 lv_process_message VARCHAR2(2000) ;
5286
5287 /* Added by JMEENA for TCS issue bug#7277211*/
5288 PROCEDURE process_sales_order (p_ooh IN OE_ORDER_HEADERS_ALL%ROWTYPE ,
5289 p_process_flag OUT NOCOPY VARCHAR2 ,
5290 p_process_message OUT NOCOPY VARCHAR2
5291 )
5292 IS
5293 /* **************************************************************************
5294 Creation Date : 01-Aug-2008
5295 Created By : JMEENA
5296 Bug Number : 7277211
5297 Purpose : Insert the record in jai_rgm_thresholds while booking the sales order
5298 Called From : PROCESS_TRANSACTION when p_event is BOOKED (sales order booked)
5299 CHANGE HISTORY:
5300 S.No Date Author and Details
5301
5302 **************************************************************************/
5303
5304 cursor c_get_customer_pan (cp_customer_id JAI_CMN_CUS_ADDRESSES.customer_id%type)
5305 IS
5306 select pan_no
5307 from JAI_CMN_CUS_ADDRESSES
5308 where customer_id = cp_customer_id
5309 and confirm_pan = jai_constants.yes;
5310
5311 cursor c_get_cust_typ_lkup_code(cp_customer_id JAI_CMN_CUS_ADDRESSES.customer_id%type)
5312 IS
5313 select tcs_customer_type
5314 from JAI_CMN_CUS_ADDRESSES
5315 where customer_id = cp_customer_id
5316 AND tcs_customer_type IS NOT NULL;
5317
5318 cursor c_get_threshold_slab ( cp_regime_id jai_rgm_thresholds.regime_id%type,
5319 cp_customer_type_lkup_code JAI_CMN_CUS_ADDRESSES.tcs_customer_type%type,
5320 cp_source_trx_date DATE
5321 )
5322 IS
5323 select
5324 thslbs.threshold_slab_id
5325 from
5326 jai_ap_tds_thhold_slabs thslbs
5327 ,jai_ap_tds_thhold_types thtyps
5328 ,jai_ap_tds_thhold_hdrs thhdrs
5329 where
5330 thslbs.threshold_type_id = thtyps.threshold_type_id
5331 and thtyps.threshold_hdr_id = thhdrs.threshold_hdr_id
5332 and thhdrs.regime_id = cp_regime_id
5333 and thtyps.threshold_type = jai_constants.thhold_typ_cumulative
5334 and thhdrs.customer_type_lookup_code = cp_customer_type_lkup_code
5335 and trunc(cp_source_trx_date) between thtyps.from_date
5336 and nvl(thtyps.to_date, trunc(cp_source_trx_date))
5337 and NVL(thslbs.from_amount,0) = 0;
5338
5339
5340 cursor get_jai_rgm_thresholds_count ( cp_fin_year jai_rgm_thresholds.fin_year%type,
5341 cp_org_tan_no jai_rgm_thresholds.org_tan_no%type,
5342 cp_party_type jai_rgm_thresholds.party_type%type,
5343 cp_party_id jai_rgm_thresholds.party_id%type,
5344 cp_regime_id jai_rgm_thresholds.regime_id%type
5345 )
5346 IS
5347 select count(*)
5348 from jai_rgm_thresholds
5349 where fin_year = cp_fin_year
5350 and org_tan_no = cp_org_tan_no
5351 and party_type = cp_party_type
5352 and party_id = cp_party_id
5353 and regime_id = cp_regime_id;
5354
5355 lx_row_id rowid;
5356 ln_regime_id jai_rgm_thresholds.regime_id%type;
5357 ln_org_tan_no jai_rgm_thresholds.org_tan_no%type;
5358 ln_party_id jai_rgm_thresholds.party_id%type;
5359 ln_party_type jai_rgm_thresholds.party_type%type;
5360 ln_fin_year jai_rgm_thresholds.fin_year%type;
5361 ln_party_pan_no JAI_CMN_CUS_ADDRESSES.pan_no%type;
5362 ln_customer_type_lkup_code JAI_CMN_CUS_ADDRESSES.tcs_customer_type%type;
5363 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type default NULL;
5364 lr_hdr_record jai_rgm_thresholds%rowtype;
5365 ln_user_id fnd_user.user_id%type := fnd_global.user_id;
5366 ln_login_id fnd_logins.login_id%type := fnd_global.login_id;
5367 ln_count NUMBER;
5368 ln_threshold_id jai_rgm_thresholds.threshold_id%type default NULL;
5369 BEGIN
5370
5371 OPEN get_tcs_fin_year( cp_org_id => p_ooh.org_id ,
5372 cp_trx_date => p_ooh.creation_date
5373 );
5374
5375 FETCH get_tcs_fin_year INTO ln_fin_year;
5376 CLOSE get_tcs_fin_year;
5377
5378 OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.tcs_regime ,
5379 cp_attribute_code => jai_constants.rgm_attr_code_org_tan ,
5380 cp_organization_id => p_ooh.ship_from_org_id
5381 ) ;
5385 p_process_flag := jai_constants.expected_error;
5382 FETCH c_get_rgm_attribute INTO ln_regime_id, ln_org_tan_no ;
5383 IF C_GET_RGM_ATTRIBUTE%NOTFOUND THEN
5384 CLOSE c_get_rgm_attribute;
5386 p_process_message := 'Org Tan Number needs to be defined for the TCS regime ';
5387 return;
5388 END IF;
5389 CLOSE c_get_rgm_attribute;
5390
5391 OPEN c_get_customer_pan (cp_customer_id => p_ooh.sold_to_org_id );
5392 FETCH c_get_customer_pan INTO ln_party_pan_no;
5393
5394 IF c_get_customer_pan%NOTFOUND THEN
5395 CLOSE c_get_customer_pan;
5396 p_process_flag := jai_constants.expected_error;
5397 p_process_message := 'Party pan no is not available for this party';
5398 RETURN;
5399 END IF;
5400 CLOSE c_get_customer_pan;
5401
5402
5403
5404 OPEN c_get_cust_typ_lkup_code (cp_customer_id => p_ooh.sold_to_org_id );
5405 FETCH c_get_cust_typ_lkup_code INTO ln_customer_type_lkup_code;
5406
5407 IF c_get_cust_typ_lkup_code%NOTFOUND THEN
5408 CLOSE c_get_cust_typ_lkup_code;
5409 p_process_flag := jai_constants.expected_error;
5410 p_process_message := 'Customer type lookup code is not available for this party';
5411 RETURN;
5412 END IF;
5413 CLOSE c_get_cust_typ_lkup_code;
5414
5415 OPEN c_get_threshold_slab (cp_regime_id => ln_regime_id,
5416 cp_customer_type_lkup_code => ln_customer_type_lkup_code,
5417 cp_source_trx_date => p_ooh.creation_date
5418
5419 );
5420 FETCH c_get_threshold_slab INTO ln_threshold_slab_id;
5421 CLOSE c_get_threshold_slab;
5422
5423 ln_count :=0;
5424
5425 OPEN get_jai_rgm_thresholds_count ( cp_fin_year => ln_fin_year,
5426 cp_org_tan_no => ln_org_tan_no,
5427 cp_party_type => jai_constants.party_type_customer,
5428 cp_party_id => p_ooh.sold_to_org_id ,
5429 cp_regime_id => ln_regime_id
5430 );
5431 FETCH get_jai_rgm_thresholds_count INTO ln_count;
5432 CLOSE get_jai_rgm_thresholds_count;
5433
5434 lr_hdr_record.threshold_id := ln_threshold_id ;
5435 lr_hdr_record.regime_id := ln_regime_id;
5436 lr_hdr_record.org_tan_no := ln_org_tan_no ;
5437 lr_hdr_record.party_id := p_ooh.sold_to_org_id;
5438 lr_hdr_record.party_type := jai_constants.party_type_customer ;
5439 lr_hdr_record.threshold_slab_id := ln_threshold_slab_id ;
5440 lr_hdr_record.fin_year := ln_fin_year ;
5441 lr_hdr_record.total_threshold_amt := null ;
5442 lr_hdr_record.total_threshold_base_amt := null ;
5443 lr_hdr_record.creation_date := sysdate ;
5444 lr_hdr_record.created_by := ln_user_id ;
5445 lr_hdr_record.last_update_date := sysdate ;
5446 lr_hdr_record.last_updated_by := ln_user_id ;
5447 lr_hdr_record.last_update_login := ln_login_id ;
5448 lr_hdr_record.party_pan_no :=ln_party_pan_no;
5449 --Insert in jai_rgm_thresholds only if records does not exists.
5450 IF NVL(ln_count,0) = 0 THEN
5451
5452 jai_rgm_thhold_proc_pkg.insert_threshold_hdr ( p_record => lr_hdr_record
5453 , p_threshold_id => ln_threshold_id
5454 , p_row_id => lx_row_id
5455 );
5456 END IF;
5457
5458 END process_sales_order;
5459 ---End of process_sales_order bug#7277211
5460
5461 BEGIN
5462
5463 /*########################################################################################################
5464 || VARIABLES INITIALIZATION
5465 ########################################################################################################*/
5466
5467 /** Register procedure for debuging */
5468
5469 lv_member_name := 'PROCESS_TRANSACTIONS';
5470 set_debug_context;
5471 /*commented by csahoo for bug# 6401388
5472 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
5473 pn_reg_id => ln_reg_id
5474 );*/
5475 lv_process_flag := jai_constants.successful ;
5476 lv_process_message := null ;
5477
5478 p_process_flag := lv_process_flag ;
5479 p_process_message := lv_process_message ;
5480 ln_event := p_event ;
5481
5482
5483 /*commented by csahoo for bug# 6401388
5484 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5485 pv_log_msg => '******************Start of JAI_AR_TC_SREP_PKG.PROCESS_TRANSACTIONS***************, Event is '||p_event
5486 );*/
5487
5488
5489 /*########################################################################################################
5490 || PROCESS COMPLETED INVOICES ( DEBIT MEMO'S ALSO INCLUDED)
5491 ########################################################################################################*/
5492 IF p_event = jai_constants.order_booked OR
5493 p_event = jai_constants.wsh_ship_confirm
5494 THEN ---------A1
5495 /*commented by csahoo for bug# 6401388
5496 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5500 p_process_flag => lv_process_flag ,
5497 pv_log_msg => '******************Before call to VALIDATE_SALES_ORDER/SHIP CONFIRM ***************, lv_document_type '||lv_document_type
5498 );*/
5499 validate_sales_order ( p_ooh => p_ooh ,
5501 p_process_message => lv_process_message
5502 );
5503
5504 /*commented by csahoo for bug# 6401388
5505 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5506 pv_log_msg => '******************Returned from call to VALIDATE_SALES_ORDER/SHIP CONFIRM ***************, lv_process_flag '||lv_process_flag
5507 );*/
5508
5509 IF lv_process_flag = jai_constants.not_applicable THEN
5510 /*commented by csahoo for bug# 6401388
5511 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5512 pv_log_msg => 'Skip the transaction'
5513 );*/
5514 return;
5515 END IF;
5516
5517 IF lv_process_flag = jai_constants.expected_error OR ---------A2
5518 lv_process_flag = jai_constants.unexpected_error
5519 THEN
5520 /*
5521 || As Returned status is an error/not applicable hence:-
5522 || Set out variables p_process_flag and p_process_message accordingly
5523 */
5524 --call to debug package
5525 /*commented by csahoo for bug# 6401388
5526 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5527 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5528 );*/
5529
5530 p_process_flag := lv_process_flag ;
5531 p_process_message := lv_process_message ;
5532 return;
5533 END IF; --------A1
5534
5535 --Added by JMEENA for bug#7277211
5536 IF p_event = jai_constants.order_booked THEN
5537 process_sales_order ( p_ooh => p_ooh ,
5538 p_process_flag => lv_process_flag ,
5539 p_process_message => lv_process_message
5540 );
5541
5542 IF lv_process_flag = jai_constants.expected_error OR ---------A2
5543 lv_process_flag = jai_constants.unexpected_error
5544 THEN
5545 p_process_flag := lv_process_flag ;
5546 p_process_message := lv_process_message ;
5547 return;
5548 END IF;
5549 END IF;
5550 --End for bug#7277211
5551
5552 END IF;
5553
5554 /*########################################################################################################
5555 || PROCESS COMPLETED INVOICES ( DEBIT MEMO'S ALSO INCLUDED)
5556 ########################################################################################################*/
5557 IF p_event = jai_constants.trx_event_completion THEN ---------B1
5558
5559 /*commented by csahoo for bug# 6401388
5560 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5561 pv_log_msg => '******************Before call to VALIDATE_INVOICE ***************, lv_document_type '||lv_document_type
5562 );*/
5563 validate_invoice ( p_ract => p_ract ,
5564 p_document_type => lv_document_type ,
5565 p_process_flag => lv_process_flag ,
5566 p_process_message => lv_process_message
5567 );
5568
5569 /*commented by csahoo for bug# 6401388
5570 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5571 pv_log_msg => '******************Returned from call to VALIDATE_INVOICE ***************, lv_process_flag '||lv_process_flag
5572 );*/
5573 IF lv_process_flag = jai_constants.not_applicable THEN
5574 /*commented by csahoo for bug# 6401388
5575 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5576 pv_log_msg => 'Skip the transaction'
5577 );*/
5578 return;
5579 END IF;
5580
5581 IF lv_process_flag = jai_constants.expected_error OR ---------B2
5582 lv_process_flag = jai_constants.unexpected_error
5583 THEN
5584 /*
5585 || As Returned status is an error/not applicable hence:-
5586 || Set out variables p_process_flag and p_process_message accordingly
5587 */
5588 --call to debug package
5589 /*commented by csahoo for bug# 6401388
5590 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5591 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5592 );*/
5593
5594 p_process_flag := lv_process_flag ;
5595 p_process_message := lv_process_message ;
5596 return;
5597 END IF; ---------B2
5598
5599 process_invoices ( p_ract => p_ract ,
5600 p_document_type => lv_document_type ,
5601 p_process_flag => lv_process_flag ,
5602 p_process_message => lv_process_message
5603 );
5604
5605 IF lv_process_flag = jai_constants.not_applicable THEN
5609 );*/
5606 /*commented by csahoo for bug# 6401388
5607 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5608 pv_log_msg => 'Skip the transaction'
5610
5611 return;
5612 END IF;
5613
5614
5615 IF lv_process_flag = jai_constants.expected_error OR ---------B3
5616 lv_process_flag = jai_constants.unexpected_error
5617 THEN
5618 /*
5619 || As Returned status is an error/not applicable hence:-
5620 || Set out variables p_process_flag and p_process_message accordingly
5621 */
5622 --call to debug package
5623 /*commented by csahoo for bug# 6401388
5624 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5625 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5626 );*/
5627
5628 p_process_flag := lv_process_flag ;
5629 p_process_message := lv_process_message ;
5630 return;
5631 END IF; ---------B3
5632 END IF; ---------B1
5633
5634 /*########################################################################################################
5635 || PROCESS CONFIRMED RECEIPTS HAVING TCS APPLICABILITY
5636 ########################################################################################################*/
5637
5638 IF p_event IN ( jai_constants.ar_cash_tax_confirmed , ---------C1
5639 jai_constants.trx_type_rct_rvs
5640 )
5641 THEN
5642 lv_document_type := p_event;
5643
5644 /*commented by csahoo for bug# 6401388
5645 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5646 pv_log_msg => ' Before call to validate_receipts : - p_event -> '||p_event
5647 );*/
5648 validate_receipts ( p_acra => p_acra ,
5649 p_document_type => lv_document_type ,
5650 p_process_flag => lv_process_flag ,
5651 p_process_message => lv_process_message
5652 );
5653
5654 /*commented by csahoo for bug# 6401388
5655 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5656 pv_log_msg => ' Returned from validate_receipts lv_process_flag: '||lv_process_flag
5657 );*/
5658 IF lv_process_flag = jai_constants.not_applicable THEN
5659 /*commented by csahoo for bug# 6401388
5660 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5661 pv_log_msg => 'Skip the transaction'
5662 );*/
5663
5664 return;
5665 END IF;
5666
5667 IF lv_process_flag = jai_constants.expected_error OR ---------C2
5668 lv_process_flag = jai_constants.unexpected_error
5669 THEN
5670 /*
5671 || As Returned status is an error/not applicable hence:-
5672 || Set out variables p_process_flag and p_process_message accordingly
5673 */
5674 --call to debug package
5675 /*commented by csahoo for bug# 6401388
5676 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5677 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5678 );*/
5679
5680 p_process_flag := lv_process_flag ;
5681 p_process_message := lv_process_message ;
5682 return;
5683 END IF; ---------C2
5684 /*commented by csahoo for bug# 6401388
5685 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5686 pv_log_msg => ' Before call to process_receipts : - p_event -> '||p_event
5687 );*/
5688
5689 process_receipts ( p_acra => p_acra ,
5690 p_document_type => p_event ,
5691 p_process_flag => lv_process_flag ,
5692 p_process_message => lv_process_message
5693 );
5694 /*commented by csahoo for bug# 6401388
5695 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5696 pv_log_msg => ' Returned from process_receipts lv_process_flag: '||lv_process_flag
5697 );*/
5698
5699 IF lv_process_flag = jai_constants.not_applicable THEN
5700 /*commented by csahoo for bug# 6401388
5701 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5702 pv_log_msg => 'Skip the transaction'
5703 );*/
5704
5705 return;
5706 END IF;
5707
5708 IF lv_process_flag = jai_constants.expected_error OR ---------C3
5709 lv_process_flag = jai_constants.unexpected_error
5710 THEN
5711 /*
5712 || As Returned status is an error/not applicable hence:-
5713 || Set out variables p_process_flag and p_process_message accordingly
5714 */
5715 --call to debug package
5716 /*commented by csahoo for bug# 6401388
5717 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5721 p_process_flag := lv_process_flag ;
5718 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5719 );*/
5720
5722 p_process_message := lv_process_message ;
5723 return;
5724 END IF; ---------C3
5725 END IF; ---------C1
5726
5727 /*########################################################################################################
5728 || PROCESS ALL RECEIPT AND CREDIT MEMO APPLICATIONS/UNAPPLICATIONS
5729 ########################################################################################################*/
5730
5731 IF p_event IN ( jai_constants.ar_cash , ---------D1
5732 jai_constants.ar_invoice_type_cm
5733 )
5734 THEN
5735
5736 /***********
5737 ||Validate application and unapplications
5738 ***********/
5739 /*commented by csahoo for bug# 6401388
5740 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5741 pv_log_msg => ' Before call to validate_app_unapp : - p_event -> '||p_event
5742 );*/
5743
5744 validate_app_unapp (
5745 p_araa => p_araa ,
5746 p_document_type => lv_document_type ,
5747 p_item_classification => lv_item_classification ,
5748 p_process_flag => lv_process_flag ,
5749 p_process_message => lv_process_message
5750 );
5751
5752 /*commented by csahoo for bug# 6401388
5753 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5754 pv_log_msg => ' Returned from validate_app_unapp lv_process_flag: '||lv_process_flag
5755 );*/
5756
5757 IF lv_process_flag = jai_constants.not_applicable THEN
5758 /*commented by csahoo for bug# 6401388
5759 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5760 pv_log_msg => 'Skip the transaction'
5761 );*/
5762 return;
5763 END IF;
5764
5765
5766 IF lv_process_flag = jai_constants.expected_error OR ---------D2
5767 lv_process_flag = jai_constants.unexpected_error
5768 THEN
5769 /*
5770 || As Returned status is an error/not applicable hence:-
5771 || Set out variables p_process_flag and p_process_message accordingly
5772 */
5773 --call to debug package
5774 /*commented by csahoo for bug# 6401388
5775 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5776 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5777 );*/
5778
5779 p_process_flag := lv_process_flag ;
5780 p_process_message := lv_process_message ;
5781 return;
5782 END IF; ---------D2
5783
5784
5785 IF lv_document_type IN ( jai_constants.trx_type_rct_app, ---------D3
5786 jai_constants.trx_type_cm_app
5787 )
5788 THEN
5789 /*commented by csahoo for bug# 6401388
5790 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5791 pv_log_msg => ' Before call to process_applications : - p_event -> '||p_event
5792 );*/
5793
5794 process_applications ( p_araa => p_araa ,
5795 p_document_type => lv_document_type ,
5796 p_item_classification => lv_item_classification ,
5797 p_process_flag => lv_process_flag ,
5798 p_process_message => lv_process_message
5799 );
5800 /*commented by csahoo for bug# 6401388
5801 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5802 pv_log_msg => ' Returned from process_applications lv_process_flag: '||lv_process_flag
5803 );*/
5804
5805 ELSIF lv_document_type IN ( jai_constants.trx_type_rct_unapp, ---------D3
5806 jai_constants.trx_type_cm_unapp
5807 )
5808 THEN
5809 /*commented by csahoo for bug# 6401388
5810 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5811 pv_log_msg => ' Before call to process_unapp_rcpt_rev : - p_event -> '||p_event
5812 );*/
5813
5814 process_unapp_rcpt_rev ( p_araa => p_araa ,
5815 p_acra => p_acra ,
5816 p_document_type => lv_document_type ,
5817 p_process_flag => lv_process_flag ,
5818 p_process_message => lv_process_message
5819 );
5820
5824 );*/
5821 /*commented by csahoo for bug# 6401388
5822 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5823 pv_log_msg => ' Returned from process_unapp_rcpt_rev lv_process_flag: '||lv_process_flag
5825
5826 END IF; ---------D3
5827
5828 IF lv_process_flag = jai_constants.not_applicable THEN ---------D4
5829 /*commented by csahoo for bug# 6401388
5830 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5831 pv_log_msg => 'Skip the transaction'
5832 );*/
5833
5834 return;
5835 END IF; ---------D4
5836
5837 IF lv_process_flag = jai_constants.expected_error OR ---------D2
5838 lv_process_flag = jai_constants.unexpected_error
5839 THEN
5840 /*
5841 || As Returned status is an error/not applicable hence:-
5842 || Set out variables p_process_flag and p_process_message accordingly
5843 */
5844 --call to debug package
5845 /*commented by csahoo for bug# 6401388
5846 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5847 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5848 );*/
5849
5850 p_process_flag := lv_process_flag ;
5851 p_process_message := lv_process_message ;
5852 return;
5853 END IF; ---------D2
5857 /*########################################################################################################
5854 END IF; ---------D1
5855
5856
5858 || CALL PROCEDURE TO GENERATE DOCUMENTS FOR ALL EVENTS EXCEPT INVOICE/DEBIT_MEMO/CREDIT_MEMO_APPLICATION
5859 ########################################################################################################*/
5860
5861 IF p_event IN ( jai_constants.ar_cash_tax_confirmed , ---------C1
5862 jai_constants.trx_type_rct_rvs ,
5863 jai_constants.ar_cash , ---------D1
5864 jai_constants.ar_invoice_type_cm
5865 )
5866 THEN
5867 /*commented by csahoo for bug# 6401388
5868 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5869 pv_log_msg => ' Before call to generate_document : - p_event -> '||p_event
5870 );*/
5871
5872
5873 OPEN cur_get_refs (cp_transaction_id => ln_transaction_id );
5874 FETCH cur_get_refs INTO rec_cur_get_refs;
5875 CLOSE cur_get_refs ;
5876
5877 OPEN cur_get_total_tax (cp_transaction_id => ln_transaction_id );
5878 FETCH cur_get_total_tax INTO ln_tax_tot_amt;
5879 CLOSE cur_get_total_tax ;
5880
5881 generate_document ( p_rgm_ref => rec_cur_get_refs ,
5882 p_total_tax_amt => ln_tax_tot_amt ,
5883 --p_transaction_id => ln_transaction_id ,
5884 p_process_flag => lv_process_flag ,
5885 p_process_message => lv_process_message
5886 );
5887
5888 /*commented by csahoo for bug# 6401388
5889 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5890 pv_log_msg => ' Returned from generate_document lv_process_flag: '||lv_process_flag
5891 );*/
5892
5893 IF lv_process_flag = jai_constants.not_applicable THEN ---------D4
5894 /*commented by csahoo for bug# 6401388
5895 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5896 pv_log_msg => 'Skip the transaction'
5897 );*/
5898
5899 return;
5900 END IF; ---------D4
5901
5902 IF lv_process_flag = jai_constants.expected_error OR ---------D2
5903 lv_process_flag = jai_constants.unexpected_error
5904 THEN
5905 /*
5906 || As Returned status is an error/not applicable hence:-
5907 || Set out variables p_process_flag and p_process_message accordingly
5908 */
5909 --call to debug package
5910 /*commented by csahoo for bug# 6401388
5911 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5912 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5913 );*/
5914
5915 p_process_flag := lv_process_flag ;
5916 p_process_message := lv_process_message ;
5917 return;
5918 END IF; ---------D2
5919 END IF; ---------D1
5920
5921 /*########################################################################################################
5922 || CALL FOR SURCHARGE PROCESSING
5923 ########################################################################################################*/
5924
5925 /*
5926 ||Call to surcharge package to update the threshold level accordingly
5927 */
5928 IF ln_transaction_id IS NOT NULL THEN
5929
5930 /*commented by csahoo for bug# 6401388
5931 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5932 pv_log_msg => ' Before call to jai_rgm_thhold_proc_pkg.maintain_threshold : - p_event -> '||p_event
5933 ||', ln_transaction_id -> '||ln_transaction_id
5934 );*/
5935
5936 jai_rgm_thhold_proc_pkg.maintain_threshold ( p_transaction_id => ln_transaction_id ,
5937 p_process_flag => lv_process_flag ,
5938 p_process_message => lv_process_message
5939 );
5940
5941 /*commented by csahoo for bug# 6401388
5942 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5943 pv_log_msg => ' Returned from jai_rgm_thhold_proc_pkg.maintain_threshold lv_process_flag: '||lv_process_flag
5944 );*/
5945
5946 IF lv_process_flag = jai_constants.not_applicable THEN
5947 /*commented by csahoo for bug# 6401388
5948 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5949 pv_log_msg => 'Skip the transaction'
5950 );*/
5951
5952 return;
5953 END IF;
5954
5955 IF lv_process_flag = jai_constants.expected_error OR ---------D2
5956 lv_process_flag = jai_constants.unexpected_error
5957 THEN
5958 /*
5959 || As Returned status is an error/not applicable hence:-
5960 || Set out variables p_process_flag and p_process_message accordingly
5961 */
5962 --call to debug package
5963 /*commented by csahoo for bug# 6401388
5964 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5965 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
5969 p_process_message := lv_process_message ;
5966 );*/
5967
5968 p_process_flag := lv_process_flag ;
5970 return;
5971 END IF; ---------D2
5972 END IF;
5973
5974 /*commented by csahoo for bug# 6401388
5975 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
5976 pv_log_msg => '**************** END OF PROCESS TRANSACTION ****************'
5977 );
5978
5979
5980 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
5981 EXCEPTION
5982 WHEN OTHERS THEN
5983 p_process_flag := jai_constants.unexpected_error;
5984 p_process_message := lv_context||' Unexpected error occured while processing jai_ar_tcs_rep_pkg.process_transactions'||SQLERRM ;
5985 /*commented by csahoo for bug# 6401388
5986 jai_cmn_debug_contexts_pkg.print( pn_reg_id => ln_reg_id ,
5987 pv_log_msg => p_process_message ,
5988 pn_statement_level => jai_cmn_debug_contexts_pkg.summary
5989 );
5990
5991 jai_cmn_debug_contexts_pkg.print_stack;*/
5992
5993 END process_transactions ;
5994
5995 PROCEDURE update_pan_for_tcs ( p_return_code OUT NOCOPY VARCHAR2 ,
5996 p_errbuf OUT NOCOPY VARCHAR2 ,
5997 p_party_id IN JAI_RGM_REFS_ALL.PARTY_ID%TYPE ,
5998 p_old_pan_no IN JAI_CMN_CUS_ADDRESSES.PAN_NO%TYPE ,
5999 p_new_pan_no IN JAI_CMN_CUS_ADDRESSES.PAN_NO%TYPE
6000
6001 )
6002 AS
6003 ln_reg_id NUMBER ;
6004
6005 ln_request_id NUMBER ;
6006 BEGIN
6007
6008 /*################################################################################################################
6009 || Initialize the variables
6010 ################################################################################################################*/
6011
6012 lv_member_name := 'UPDATE_PAN_FOR_TCS';
6013 set_debug_context;
6014 /*commented by csahoo for bug# 6401388
6015 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context ,
6016 pn_reg_id => ln_reg_id
6017 );
6018
6019 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
6020 pv_log_msg => 'Parameter passed to update_pan_for_tcs are -> ' ||fnd_global.local_chr(10)
6021 ||', p_party_id -> '||p_party_id ||fnd_global.local_chr(10)
6022 ||', p_old_pan_no -> '||p_old_pan_no ||fnd_global.local_chr(10)
6023 ||', p_new_pan_no -> '||p_new_pan_no
6024 );*/
6025
6026 p_return_code := 0 ;
6027 p_errbuf := Null ;
6028 ln_request_id := fnd_global.conc_request_id;
6029
6030
6031 /*commented by csahoo for bug# 6401388
6032 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
6033 pv_log_msg => 'Updating the table JAI_CMN_CUS_ADDRESSES'
6034 );*/
6035
6036 /*
6037 ||Update the JAI_CMN_CUS_ADDRESSES table . Set the Old pan number with the new pan number as specified in the input.
6038 */
6039 UPDATE
6040 JAI_CMN_CUS_ADDRESSES
6041 SET
6042 pan_no = p_new_pan_no
6043 WHERE
6044 customer_id = p_party_id
6045 AND pan_no = p_old_pan_no ;
6046
6047 /*commented by csahoo for bug# 6401388
6048 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
6049 pv_log_msg => 'Update to table JAI_CMN_CUS_ADDRESSES successful. Now updating the table jai_rgm_thresholds '
6050 );*/
6051
6052
6053 /*
6054 ||Update the JAI_CMN_CUS_ADDRESSES table . Set the Old pan number with the new pan number as specified in the input.
6055 */
6056 UPDATE
6057 jai_rgm_thresholds
6058 SET
6059 party_pan_no = p_new_pan_no
6060 WHERE
6061 party_id = p_party_id
6062 AND party_pan_no = p_old_pan_no ;
6063
6064
6065 /*commented by csahoo for bug# 6401388
6066 jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
6067 pv_log_msg => '**************** UPDATE_PAN_FOR_TCS SUCCESSFULLY COMPLETED ****************'
6068 );
6069 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);*/
6070
6071 EXCEPTION
6072 WHEN OTHERS THEN
6073 p_return_code := 2;
6074 p_errbuf := 'Unexpected error in the jai_ar_tcs_rep_pkg.update_pan_for_tcs '||substr(sqlerrm,1,300);
6075
6076 END update_pan_for_tcs;
6077 END jai_ar_tcs_rep_pkg;