[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RGM_VAT_ACCNT_PKG
Source
1 PACKAGE BODY jai_cmn_rgm_vat_accnt_pkg
2 /* $Header: jai_cmn_rgm_vat.plb 120.20.12020000.3 2013/01/11 07:06:40 anupgupt ship $ */
3 /*****************************************************************************************************************************************************************
4 Created By : aiyer
5 Created Date : 17-Mar-2005
6 Enhancement Bug : 4247989
7 Purpose : Process the VAT Tax AR records (Invoice,Debit Memo and Credit memo) and populate the jai_rgms_trx_records and gl_interface appropriately.
8
9 Dependency Due To The Current Bug :
10 This object has been newly created with as a part of the VAT enhancement.
11 Needs to be always released along with the bug 4247989.Lot of Datamodel changes in this enhancement.
12 For details refer base bug 4247989
13
14 116.2 08-Jun-2005 Version 116.2 jai_cmn_rgm_vat -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
15 as required for CASE COMPLAINCE.
16
17 13-Jun-2005 File Version: 116.3
18 Ramananda for bug#4428980. Removal of SQL LITERALs is done
19
20 7 24/04/2007 Vijay Shankar for Bug#6012570 (5876390), Version:120.2 (115.8 )
21 Forward Porting to R12:
22 Modified the code to hit repository + GL for Projects Billing
23 Modified the main cursor cur_get_deliveries and related code to handle projects billing
24
25 3 14-jun-2007 sacsethi for bug 6072461 file version 120.3
26
27 This bug is used to fp 11i bug 5183031 for vat reveresal
28 Problem - Vat Reversal Enhancement not forward ported
29 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
30
31 Changed the two queries to include VAT REVERSAL tax type
32 Changed the logic to charge recovery and expense accounts if the tax type is VAT REVERSAL
33
34 4 28-jun-2007 ssumaith - bug#6147385
35
36 changed the width of the variable lv_source from 30 to 100
37
38 5. 01-08-2007 rchandan for bug#6030615 , Version 120.5
39 Issue : Inter org Forward porting
40
41 6. 21-aug-2007 vkaranam for bug#6030615 ,File version 120.6
42 Changes are done as part of the performance issue.
43
44 7. 27-Jul-2009 VUMAASHA for bug 8657720
45 Modified ln_credit_amount to ln_debit_amount for VAT reversal scenario in case of OM, Manual AR and interorg transactions.
46
47 8. 15-nov-2010 vkaranam for bug#10189258
48 Issue:
49 NEGATIVE INVOICE (HAVING FUNCTIONALITY OF CM) DOES NOT PASS CORRECT ACCOUNTING
50 FIX:
51 done with the code changes in jai_cmn_rgm_vat_accnt_pkg.process_order_invoice
52 For -ve invoice the following entries will be generated
53 Dr VAT Liability +ive Amount
54 Cr VAT Interim Liability +ive Amount
55 9. 09-jan-2012 vkaranam for bug#13495960
56 Issue:
57 VAT accounting and repository update are not happening on the same
58 date for Invoices created with GL date as back date.
59 Fix:
60 VAT accounting and repository update shall happen on the gl_date.
61 While calling the insert_repository_entry ,p_transaction_date is passed as the gl date.
62
63 Future Dependencies For the release Of this Object:-
64 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
65 A datamodel change )
66
67 ----------------------------------------------------------------------------------------------------------------------------------------------------
68 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
69 Of File On Bug/Patchset
70 jai_cmn_rgm_vat_accnt_pkg_pkg_b.sql
71 ----------------------------------------------------------------------------------------------------------------------------------------------------
72 115.0 4247989 IN60106 + Aiyer 17-Mar-2005 4146708 is the release bug for SERVICE/CESS
73 4146708 + enhancement.
74 4245089 4245089 - Base bug for VAT Enhancement.
75
76 ----------------------------------------------------------------------------------------------------------------------------------------------------
77
78 7. 19-Sep-2007 Bug 5739005. Added by vkantamn ,Version 120.7
79 Included source, source_trx_type and source_table_name in the
80 inner query of the cursor cur_get_deliveries and
81 cur_get_man_ar_inv_taxes for performance issue
82 reported.
83 It increased the performance
84 as these columns have index defined on them in
85 jai_rgm_trx_records.
86
87 8. 02-Apr-2010 Allen Yang modified for bug 9485355 (12.1.3 non-shippable Enhancement)
88 added parameter p_order_line_id in procedure process_order_invoice and modified
89 procedure process_order_invoice to process non-shippable lines.
90 Version 120.11.12010000.3
91
92 9. 2010/04/14 Bo Li For bug9305067
93 The procedure jai_cmn_rgm_recording_pkg.insert_vat_repository_entry has been called
94 in the package JAI_CMN_VAT_ACCNT_PKG. Because of the change of the jai_cmn_rgm_recording_pkg.insert_vat_repository_entry,
95 the package JAI_CMN_VAT_ACCNT_PKG should be modified.
96 The attribute column of JAI_RGM_TRX_RECORDS are also used. To follow the development standard, they should be
97 replaced by new columns with new meaningful ones
98
99 10. 20-Apr-2010 Allen Yang modified for bug 9602968
100 Modified procedure definition of process_order_invoice added 'DEFAULT NULL' for p_order_line_id
101 Version 120.11.12010000.4
102
103 11. 09-JUN-2010 Bo Li modified for bug#9766552
104 Issue - Account_name column in table jai_rgm_trx_records is null for VAT tax of
105 non-shippable RMA.
106 Fix - Give the account name "RECOVERY" for the transaction type "Credit Memo"
107
108 12. 25-Feb-2011 Added by Zhiwei for Open Interface ER bug#11683927
109 Issue: In Open Interface ER, in 'External' source,
110 The program should not generate VAT Accounting, and VAT repository should not be updated.
111 Fixed: Check the source for 'External', in this case, exclude the code for VAT Accounting
112 and repository update.
113 12. 30-Mar-2011 Added by Xiao for bug#11936390
114 Fixed: For 'External', accounting for exclusive tax in vat repository should be:
115 Dr: Receivable, Cr: User provide accounting
116 13. 11-Jan-2013 for bug 16058504 by anupgupt
117 Issue - PERFORMANCE ISSUE ON INDIA - VAT INVOICE GENERATION / ACCOUNTING
118 Fix - optimized cursor cur_get_deliveries query
119 *****************************************************************************************************************************************************************/
120 AS
121 /* */
122
123 PROCEDURE record_debug_messages ( p_message VARCHAR2 )
124 /**************************************************************************
125 Created By : aiyer
126 Created Date : 17-Mar-2005
127 Enhancement Bug : 4247989
128 Purpose : write debug messages into the request log
129 Called From :
130 ***************************************************************************/
131 AS
132
133 BEGIN
134 IF nvl(upper(p_record_debug),'N') = 'Y' THEN
135 fnd_file.put_line(fnd_file.LOG,p_message);
136 END IF;
137 END record_debug_messages;
138
139 PROCEDURE process_order_invoice
140 (
141 p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
142 p_source IN VARCHAR2 ,
143 p_organization_id IN JAI_OM_WSH_LINES_ALL.ORGANIZATION_id%TYPE ,
144 p_location_id IN JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE ,
145 p_delivery_id IN JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE ,
146 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
147 -- 20-Apr-2010, add 'DEFAULT NULL' by Allen Yang for bug 9602968, begin
148 p_order_line_id IN JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE DEFAULT NULL ,
149 -- 20-Apr-2010, add 'DEFAULT NULL' by Allen Yang for bug 9602968, end
150 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
151 p_customer_trx_id IN RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE ,
152 p_transaction_type IN RA_CUST_TRX_TYPES.TYPE%TYPE, -- DEFAULT 'INV' /* This parameter is used only for AR Accounting */ File.Sql.35 by Brathod
153 p_vat_invoice_no IN JAI_OM_WSH_LINES_ALL.VAT_INVOICE_NO%TYPE ,
154 p_default_invoice_date IN DATE ,
155 p_batch_id IN NUMBER ,
156 p_called_from IN VARCHAR2 ,
157 p_debug IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
158 p_process_flag OUT NOCOPY VARCHAR2 ,
159 p_process_message OUT NOCOPY VARCHAR2
160 )
161 /****************************************************************************************************
162 Created By : aiyer
163 Created Date : 17-Mar-2005
164 Enhancement Bug : 4247989
165 Purpose : Process the VAT Tax AR records (Invoice,Debit Memo and Credit memo)
166 and populate the jai_rgms_trx_records and gl_interface appropriately.
167
168 Called From : India VAT Invoice Number/Accounting Concurrent Program:-
169 =====================================================
170 Procedure ja_.process
171
172 AR Invoice Completion:-
173 =======================
174 Trigger ja_in_loc_ar_hdr_update_trg for Invoice and Debit Memo
175 Trigger ja_in_loc_ar_hdr_update_trg_vat for Credit Memo
176
177 Changed History:
178
179 1. 09-APR-2008 JMEENA for bug#6944839 File Version 120.1.12000000.4
180 Modified the cursor cur_get_man_ar_inv_taxes, Changed jrttv1.tax_type to jrttv1.regime_code in where clause.
184 3. 20-Apr-2010 Allen Yang modified for bug 9602968
181 2. 02-Apr-2010 Allen Yang modified for bug 9485355 (12.1.3 non-shippable Enhancement)
182 added parameter p_order_line_id in procedure process_order_invoice and modified
183 procedure process_order_invoice to process non-shippable lines.
185 add 'DEFAULT NULL' for parameter p_order_line_id.
186 ***************************************************************************************************/
187 AS
188
189 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
190 ln_liab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
191 ln_intliab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
192 ln_charge_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
193 ln_balancing_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
194
195 lv_process_flag VARCHAR2(2) ;
196 lv_process_message VARCHAR2(1996) ;
197 ln_debit_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
198 ln_credit_amount JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE ;
199
200 --Date 14/06/2007 by sacsethi for bug 6072461 (for VAT Reversal)
201 ln_recov_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
202 ln_expense_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
203 lc_account_name VARCHAR2(50);
204 -- End 6072461
205 /*added the below cursors for performance issue,for bug#6030615*/
206 --start
207 CURSOR c_chk_rgm_trxs(cp_transaction_header_id in number,cp_transaction_temp_id in number,cp_tax_id in number) IS
208 SELECT 1
209 FROM
210 jai_rgm_trx_records jrtr
211 WHERE
212 jrtr.trx_reference1 = cp_transaction_header_id AND -- Modifiied By Bo Li for replaceing the attribute1 with trx_reference1
213 jrtr.source_document_id = cp_transaction_temp_id AND
214 jrtr.reference_id = cp_tax_id AND
215 jrtr.organization_id = p_organization_id AND
216 jrtr.location_id = p_location_id;
217 ln_rgm_cnt number;
218
219 --end
220 /*added by rchandan for bug #6030615*/
221 /*
222 ||Fetch the information from jai_mtl_trxs and jai_cmn_document_taxes
223 */
224 CURSOR cur_get_mtltxns
225 IS
226 SELECT
227 jtc.tax_type,
228 jtc.tax_rate,
229 jtc.tax_id,
230 jmt.transaction_temp_id,
231 jmt.transaction_header_id,
232 jmt.vat_assessable_value,
233 jmt.creation_date,
234 jcdt.tax_amt
235 FROM
236 jai_mtl_trxs jmt,
237 jai_cmn_document_taxes jcdt,
238 jai_cmn_taxes_all jtc,
239 jai_rgm_registrations jrg,
240 jai_rgm_definitions jrr
241 WHERE
242 jmt.from_organization = p_organization_id
243 -- AND jmt.location_id = p_location_id
244 AND jmt.transaction_header_id = p_delivery_id
245 AND jmt.transaction_header_id=jcdt.source_doc_id
246 AND jmt.transaction_temp_id=jcdt.source_doc_line_id
247 AND jcdt.tax_id=jtc.tax_id
248 AND jtc.tax_type= jrg.attribute_code
249 AND jrr.regime_code = jai_constants.vat_regime
250 AND jrg.regime_id = jrr.regime_id
251 AND jrg.registration_type = 'TAX_TYPES' ;
252
253
254 /*
255 || Fetch the delivery information from JAI_OM_WSH_LINES_ALL and JAI_OM_WSH_LINE_TAXES
256 */
257 /* Bug 5739005. Added by vkantamn
258 * Included source, source_trx_type and source_table_name in the inner
259 * query for performance issue reported.
260 */
261 CURSOR cur_get_deliveries
262 ( cp_source IN jai_rgm_trx_records.source%TYPE,
263 cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
264 cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
265 )
266 IS
267 SELECT /*+ ordered use_concat */ -- for bug 16058504 by anupgupt
268 jspl.delivery_id ,
269 jspl.delivery_detail_id ,
270 jspl.vat_assessable_value ,
271 nvl(jspl.vat_exemption_flag,'N') vat_exemption_flag ,
272 jspl.order_line_id ,
273 jsptl.tax_id ,
274 jsptl.tax_rate ,
275 /* Bug# 6012570 (5876390) jsptl.tax_amount , */
276 jsptl.func_tax_amount ,
277 jsptl.creation_date ,
278 jtc.tax_type
279 FROM
280 JAI_OM_WSH_LINES_ALL jspl ,
281 JAI_OM_WSH_LINE_TAXES jsptl ,
282 JAI_CMN_TAXES_ALL jtc ,
283 ( --Date 14/06/2007 by sacsethi for bug 6072461 , View is replaced by subquery with vat reversal
284 SELECT jrttv1.tax_type tax_type
285 FROM jai_regime_tax_types_v jrttv1
286 WHERE jrttv1.regime_code = jai_constants.vat_regime --Modified by JMEENA from jrttv1.tax_type to jrttv1.regime_code for bug#6944839
287 UNION
288 SELECT 'VAT REVERSAL' tax_type FROM DUAL
289 ) jrttv
290 WHERE
291 jspl.organization_id = p_organization_id AND
292 jspl.location_id = p_location_id AND
293 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
297 (jspl.order_line_id = p_order_line_id AND jspl.order_line_id = jsptl.order_line_id)) AND
294 -- jspl.delivery_id = p_delivery_id AND
295 -- jspl.delivery_detail_id = jsptl.delivery_detail_id AND
296 ((jspl.delivery_id = p_delivery_id AND jspl.delivery_detail_id = jsptl.delivery_detail_id) OR
298 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
299 jsptl.tax_id = jtc.tax_id AND
300 jtc.tax_type = jrttv.tax_type AND
301 -- jrttv.regime_code = jai_constants.vat_regime AND --Date 14/06/2007 by sacsethi for bug 6072461
302 NOT EXISTS
303 -- start for bug 16058504 by anupgupt
304 (SELECT 1
305 FROM jai_rgm_trx_records jrtr
306 WHERE jrtr.source = cp_source
307 AND jrtr.source_trx_type = cp_source_trx_type
308 AND jrtr.organization_id = p_organization_id
309 AND jrtr.location_id = p_location_id
310 AND jrtr.source_table_name = cp_source_table_name
311 AND jrtr.trx_reference1 = jspl.delivery_id
312 AND jrtr.source_document_id = jspl.delivery_detail_id
313 AND jrtr.reference_id = jsptl.tax_id
314 UNION ALL
315 SELECT 1
316 FROM jai_rgm_trx_records jrtr
317 WHERE jrtr.source = cp_source
318 AND jrtr.source_trx_type = cp_source_trx_type
319 AND jrtr.organization_id = p_organization_id
320 AND jrtr.location_id = p_location_id
321 AND jrtr.source_table_name = cp_source_table_name
322 AND jrtr.trx_reference2 = jspl.order_line_id
323 AND jrtr.source_document_id = jspl.order_line_id
324 AND jrtr.reference_id = jsptl.tax_id
325 )
326 /* ( SELECT 1
327 FROM jai_rgm_trx_records jrtr
328 WHERE
329 -- Bug 5739005. Added by vkantamn
330 jrtr.source = cp_source AND
331 jrtr.source_trx_type = cp_source_trx_type AND
332 jrtr.organization_id = p_organization_id AND
333 jrtr.location_id = p_location_id AND
334 jrtr.source_table_name = cp_source_table_name AND
335 -- End for bug 5739005.
336 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
337 -- jrtr.attribute1 = jspl.delivery_id AND
338 -- jrtr.source_document_id = jspl.delivery_detail_id AND
339 -- Modifiied By Bo Li for replaceing the attribute1, attribute2 with trx_reference1, reference2 Begin
340 ((jrtr.trx_reference1 = jspl.delivery_id AND jrtr.source_document_id = jspl.delivery_detail_id) OR
341 (jrtr.trx_reference2 = jspl.order_line_id AND jrtr.source_document_id = jspl.order_line_id)) AND
342 -- Modifiied By Bo Li for replaceing the attribute1, attribute2 with trx_reference1, reference2 Begin
343 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
344 jrtr.reference_id = jsptl.tax_id
345 )*/
346 -- end for bug 16058504 by anupgupt
347 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
348 -- AND cp_source = jai_constants.source_wsh /* Bug# 6012570 (5876390) */
349 AND (cp_source = jai_constants.source_wsh OR cp_source = jai_constants.source_nsh)
350 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
351
352 /* start. bug#Bug# 6012570 (5876390). added the union condition */
353 UNION
354 SELECT
355 jpdi.draft_invoice_id ,
356 null delivery_detail_id ,
357 sum(jpdil.line_amt) vat_assessable_value ,
358 'N' vat_exemption_flag ,
359 null order_line_id ,
360 jcdt.tax_id tax_id ,
361 jcdt.tax_rate tax_rate ,
362 /* Bug# 6012570 (5876390) null tax_amount , */
363 sum(jcdt.func_tax_amt) func_tax_amount ,
364 max(jpdi.last_update_date) creation_date ,
365 jcdt.tax_type tax_type
366 FROM
367 jai_pa_draft_invoices jpdi ,
368 jai_pa_draft_invoice_lines jpdil ,
369 jai_cmn_document_taxes jcdt ,
370 (
371 SELECT jrttv1.tax_type tax_type
372 FROM jai_regime_tax_types_v jrttv1
373 WHERE jrttv1.regime_code = jai_constants.vat_regime
374 UNION
375 SELECT 'VAT REVERSAL' tax_type FROM DUAL
376 ) jrttv
377 WHERE cp_source = jai_pa_billing_pkg.gv_source_projects
378 AND jpdi.draft_invoice_id = jpdil.draft_invoice_id
379 AND jpdil.draft_invoice_line_id = jcdt.source_doc_line_id
380 AND jcdt.source_doc_id = jpdi.draft_invoice_id
381 AND jcdt.source_doc_type = jai_pa_billing_pkg.gv_source_projects
382 AND jcdt.tax_type = jrttv.tax_type
383 AND jpdi.draft_invoice_id = p_delivery_id
384 GROUP BY jpdi.draft_invoice_id, jcdt.tax_type, jcdt.tax_id, jcdt.tax_rate;
385 /* end bug# 6012570 (5876390) */
386 /*
387 || Fetch the invoice information from ja_in_ra_customer_trx_tax_lines so_picking_lines and JAI_OM_WSH_LINE_TAXES
391 * query for performance issue reported.
388 */
389 /* Bug 5739005. Added by vkantamn
390 * Included source, source_trx_type and source_table_name in the inner
392 */
393 CURSOR cur_get_man_ar_inv_taxes
394 ( cp_source IN jai_rgm_trx_records.source%TYPE,
395 cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
396 cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE
397 )
398 IS
399 SELECT
400 jctl.customer_trx_id ,
401 jctl.vat_assessable_value ,
402 nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
403 jcttl.customer_trx_line_id ,
404 jcttl.tax_id ,
405 jcttl.link_to_cust_trx_line_id ,
406 jcttl.func_tax_amount ,
407 jcttl.creation_date ,
408 jtc.tax_type ,
409 jcttl.tax_rate ,
410 jcttl.tax_line_no -- Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011
411 FROM
412 JAI_AR_TRX_LINES jctl ,
413 JAI_AR_TRX_TAX_LINES jcttl ,
414 JAI_CMN_TAXES_ALL jtc ,
415 ( --Date 14/06/2007 by sacsethi for bug 6072461 , View is replaced by subquery with vat reversal
416 SELECT jrttv1.tax_type tax_type
417 FROM jai_regime_tax_types_v jrttv1
418 WHERE jrttv1.regime_code = jai_constants.vat_regime
419 UNION
420 SELECT 'VAT REVERSAL' tax_type
421 FROM DUAL
422 ) jrttv
423 WHERE
424 jctl.customer_trx_id = p_customer_trx_id AND
425 jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
426 jcttl.tax_id = jtc.tax_id AND
427 jtc.tax_type = jrttv.tax_type AND
428 NOT EXISTS ( SELECT
429 1
430 FROM
431 jai_rgm_trx_records jrtr
432 WHERE
433 -- Bug 5739005. Added by vkantamn
434 jrtr.source = cp_source AND
435 jrtr.source_trx_type = cp_source_trx_type AND
436 jrtr.organization_id = p_organization_id AND
437 jrtr.location_id = p_location_id AND
438 jrtr.source_table_name = cp_source_table_name AND
439 -- End for bug 5739005
440 jrtr.trx_reference1 = p_customer_trx_id AND -- Modifiied By Bo Li for replaceing the attribute1 with trx_reference1
441 jrtr.source_document_id = jcttl.customer_trx_line_id AND
442 jrtr.reference_id = jcttl.tax_id
443 ) ;
444
445
446 /* Following variables added for Projects Billing. Bug# 6012570 (5876390) */
447 lv_source_trx_type jai_rgm_trx_records.source_trx_type%type;
448 lv_source_table_name VARCHAR2(30);
449 lv_called_from VARCHAR2(100); /*ssumaith - changed the width to 100 from 30 */
450 lv_attribute_context VARCHAR2(30);
451 ln_source_id NUMBER(15);
452
453 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
454 --------------------------------------------------------------------------------
455 CURSOR get_external_flag_cur IS
456 SELECT COUNT(1)
457 FROM jai_ar_trx_lines lines
458 , jai_interface_lines_all intfs
459 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
460 AND lines.customer_trx_id = p_customer_trx_id
461 AND lines.interface_flag = 'Y'
462 AND intfs.taxable_event = 'EXTERNAL';
463
464 --Add cursor get_receivable_ac_id_cur by Xiao for bug#11936390.
465 CURSOR get_receivable_ac_id_cur(pn_customer_trx_id NUMBER) IS
466 SELECT DISTINCT(code_combination_id)
467 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
468 WHERE customer_trx_id = pn_customer_trx_id
469 AND account_class = 'REC'
470 AND latest_rec_flag ='Y';
471
472 ln_external_flag NUMBER;
473 --------------------------------------------------------------------------------
474 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
475
476 BEGIN
477
478 record_debug_messages ('**********************1- START OF jai_cmn_rgm_processing_pkg.PROCESS_ORDER_INVOICE-P_DELIVERY ID ->'||p_delivery_id||' **********************');
479
480 record_debug_messages ('2- Input parameters passed are p_called_from ->'||p_called_from
481 ||', p_regime_id -> ' || p_regime_id
482 ||', p_source -> ' || p_source
483 ||', p_organization_id -> ' || p_organization_id
484 ||', p_location_id -> ' || p_location_id
485 ||', p_delivery_id -> ' || p_delivery_id
486 ||', p_customer_trx_id -> ' || p_customer_trx_id
487 ||', p_transaction_type-> ' || p_transaction_type
488 ||', p_vat_invoice_no ->' || p_vat_invoice_no
492
489 ||', p_batch_id -> ' || p_batch_id
490
491 );
493
494 /******************************************************************************************
495 ||Variable Initialization
496 ******************************************************************************************/
497
498 lv_process_flag := jai_constants.successful ;
499 lv_process_message := null ;
500
501 p_process_flag := lv_process_flag ;
502 p_process_message := lv_process_message ;
503
504 p_record_debug := p_debug ;
505 ln_debit_amount := NULL ;
506 ln_credit_amount := NULL ;
507
508
509
510 /******************************************************************************************
511 || Validate input parameters
512 ******************************************************************************************/
513
514 IF p_regime_id IS NULL THEN
515 record_debug_messages('2.1 REGIME ID cannot be null');
516 p_process_flag := jai_constants.expected_error;
517 p_process_message := 'Invalid REGIME';
518 return;
519 END IF;
520
521 /*
522 || Validate that organization and location cannot be null
523 */
524
525 IF p_organization_id IS NULL OR
526 p_location_id IS NULL
527 THEN
528 record_debug_messages('3 Organization_id or Location_id cannot be null. Please provide a valid value for these fields');
529 p_process_flag := jai_constants.expected_error;
530 p_process_message := 'Organization_id or Location_id cannot be null. Please provide a valid value for these fields';
531 return;
532 END IF;
533
534
535 /*
536 || Validate that organization and location cannot be null
537 */
538 IF p_delivery_id IS NULL AND
539 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
540 p_order_line_id IS NULL AND
541 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
542 p_customer_trx_id IS NULL
543 THEN
544 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
545 record_debug_messages(' 4 Delivery ID/Order Line ID/Customer_trx_id both cannot be null. Please provide a valid value for either one of the three');
546 -- record_debug_messages(' 4 Delivery ID/Customer_trx_id both cannot be null. Please provide a valid value for either one of the two');
547 p_process_flag := jai_constants.expected_error;
548 -- p_process_message := 'Delivery ID/Customer_trx_id both cannot be null. Please provide a valid value for either one of the two';
549 p_process_message := 'Delivery ID/Order Line ID/Customer_trx_id both cannot be null. Please provide a valid value for either one of the three';
550 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
551 return;
552 END IF;
553
554 /*
555 || Validate vat invoice number is passed
556 || Only in case of a CM the vat invoice number can be null (i.e called from JA_IN_LOC_AR_HDR_UPD_TRG_VAT )in all other
557 || cases if found as null then stop processing
558 */
559
560 --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011, begin
561 ---------------------------------------------------------------------------------
562 OPEN get_external_flag_cur;
563 FETCH get_external_flag_cur INTO ln_external_flag;
564 CLOSE get_external_flag_cur;
565
566 --For 'Standard' event or manual AR trasnaction, need to check VAT invoice no. existence.
567 --while in 'External' event for ln_external_flag > 0, do not need to check the same.
568 IF nvl(ln_external_flag, 0) = 0 THEN --Add nvl by Xiao for bug#11936390
569 ---------------------------------------------------------------------------------
570 --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011, end
571 IF p_vat_invoice_no IS NULL AND
572 nvl(p_called_from,'####') <> 'JA_IN_LOC_AR_HDR_UPD_TRG_VAT'
573 AND nvl(p_transaction_type ,'####') <> 'DRAFT_INVOICE_CM' /* bug#6012570 (5876390) introduced for Projects Billing Implementation */
574 THEN
575 record_debug_messages('5 Vat Invoice Number cannot be null. Please provide a valid VAT Invoice Number');
576 p_process_flag := jai_constants.expected_error;
577 p_process_message := nvl(p_transaction_type ,'####') /* bug#6012570 (5876390) */
578 || 'Vat Invoice Number cannot be null. Please provide a valid VAT Invoice Number';
579 return;
580 END IF;
581 END IF ; --Added by Xiao Lv for Open Interface ER bug#11683927
582
583
584 /******************************************************************************************
585 || Process the Deliver (In case of OM) and the Manual Invoice (in case of AR) and pass suitable
586 || accounting entries.
587 ******************************************************************************************/
588
589 IF upper(p_source) in (
590 upper(jai_constants.source_wsh)
591 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
592 ,upper(jai_constants.source_nsh)
593 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
594 , jai_pa_billing_pkg.gv_source_projects /* Bug# 6012570 (5876390) */
595 )
596 THEN
597
598 if jai_pa_billing_pkg.gv_debug then
602 /* Start Bug# 6012570 (5876390) */
599 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '3 rgm_om_ar_vat_accnt_pkg.process_order_invoice . Psource:'||p_source);
600 end if;
601
603 if upper(p_source) = upper(jai_constants.source_wsh) then
604 record_debug_messages ('6 Delivery PROCESSING');
605 lv_source_trx_type := jai_constants.source_ttype_delivery;
606 lv_source_table_name := jai_constants.tname_dlry_dtl;
607 lv_called_from := jai_constants.vat_repo_call_from_om_ar;
608 lv_attribute_context := jai_constants.contxt_delivery;
609
610 elsif p_source = jai_pa_billing_pkg.gv_source_projects then
611 record_debug_messages ('6 Projects Draft Invoice PROCESSING');
612 lv_source_trx_type := jai_pa_billing_pkg.gv_trx_type_draft_invoice; -- 'DRAFT_INVOICE';
613 lv_source_table_name := jai_pa_billing_pkg.gv_draft_invoice_table; -- 'JAI_PA_DRAFT_INVOICES';
614 lv_called_from := p_called_from;
615 lv_attribute_context := jai_pa_billing_pkg.gv_draft_invoice_release; -- 'DRAFT_INVOICE_RELEASE';
616 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
617 elsif p_source = upper(jai_constants.source_nsh) then
618 record_debug_messages ('6 Non-Shippable Item PROCESSING');
619 lv_source_trx_type := jai_constants.source_ttype_non_shippable;
620 lv_source_table_name := jai_constants.tname_order_lines_all;
621 lv_called_from := jai_constants.vat_repo_call_from_om_ar;
622 lv_attribute_context := jai_constants.contxt_non_shippable;
623 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
624 end if;
625
626 /* End Bug# 6012570 (5876390) */
627
628
629 /*
630 || Process Orders - OM side processing
631 */
632 /* Bug# 6012570 (5876390) FOR rec_cur_get_deliveries IN cur_get_deliveries */
633 /* Bug 5739005 vkantamn --Parameters needed in cur_get_deliveries */
634 FOR rec_cur_get_deliveries IN cur_get_deliveries(p_source, lv_source_trx_type, lv_source_table_name)
635 LOOP
636
637 if jai_pa_billing_pkg.gv_debug then
638 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '4 rgm_om_ar_vat_accnt_pkg.process_order_invoice. Loop TaxId:'||rec_cur_get_deliveries.tax_id);
639 end if;
640
641 record_debug_messages (' ************7 PROCESSING Delivery id -> '|| p_delivery_id||'Delivery Details ID -> '||rec_cur_get_deliveries.delivery_detail_id||' ************' );
642 /*******************************
643 ||Variable Initialization
644 *******************************/
645 ln_liab_acct_ccid := null;
646 ln_intliab_acct_ccid := null;
647 ln_debit_amount := rec_cur_get_deliveries.func_tax_amount;
648
649 /* Start - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
650 ln_credit_amount := null;
651 ln_debit_amount := null;
652 ln_recov_acct_ccid := null;
653 ln_expense_acct_ccid := null;
654 ln_charge_ac_id := null;
655 ln_balancing_ac_id := null;
656 lc_account_name := null;
657 /* End - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
658
659 /* Start Bug# 6012570 (5876390) */
660 if upper(p_source) = upper(jai_constants.source_wsh) then
661 ln_source_id := rec_cur_get_deliveries.delivery_detail_id;
662
663 elsif p_source = jai_pa_billing_pkg.gv_source_projects then
664 ln_source_id := rec_cur_get_deliveries.delivery_id; /* this is draft_invoice_id */
665 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
666 elsif p_source = upper(jai_constants.source_nsh) then
667 ln_source_id := rec_cur_get_deliveries.order_line_id;
668 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
669 end if;
670 /* End Bug# 6012570 (5876390) */
671
672 record_debug_messages ('8 Variables Initialised');
673
674
675 IF ( rec_cur_get_deliveries.tax_type = 'VAT REVERSAL' ) THEN
676 /* Start - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
677 /*******************************
678 ||Get the code combination id
679 ||for the "RECOVERY ACCOUNT"
680 *******************************/
681 ln_recov_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
682 p_regime_id => p_regime_id ,
683 p_organization_type => jai_constants.orgn_type_io ,
684 p_organization_id => p_organization_id ,
685 p_location_id => p_location_id ,
686 p_tax_type => rec_cur_get_deliveries.tax_type ,
687 p_account_name => jai_constants.recovery
688 ) ;
689
690 /*******************************
691 || Get the code combination id
692 || for the "EXPENSE ACCOUNT"
693 *******************************/
694 ln_expense_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
695 p_regime_id => p_regime_id ,
696 p_organization_type => jai_constants.orgn_type_io ,
697 p_organization_id => p_organization_id ,
701 ) ;
698 p_location_id => p_location_id ,
699 p_tax_type => rec_cur_get_deliveries.tax_type ,
700 p_account_name => jai_constants.expense
702 lc_account_name := jai_constants.recovery;
703 ln_charge_ac_id := ln_recov_acct_ccid;
704 ln_balancing_ac_id := ln_expense_acct_ccid;
705 ln_debit_amount := rec_cur_get_deliveries.func_tax_amount; /* Modified ln_credit_amount to ln_debit_amount for bug 8657720 by vumaasha */
706
707 IF ln_charge_ac_id IS NULL OR
708 ln_balancing_ac_id IS NULL
709 THEN
710 record_debug_messages('9 VAT delivery accounting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT Reversal');
711 p_process_flag := jai_constants.expected_error;
712 p_process_message := 'VAT delivery accounting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT reversal';
713 return;
714 END IF;
715 /* End - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
716 ELSE
717
718 /*******************************
719 ||Get the code combination id
720 ||for the "LIABILITY ACCOUNT"
721 *******************************/
722 ln_liab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
723 p_regime_id => p_regime_id ,
724 p_organization_type => jai_constants.orgn_type_io ,
725 p_organization_id => p_organization_id ,
726 p_location_id => p_location_id ,
727 p_tax_type => rec_cur_get_deliveries.tax_type ,
728 p_account_name => jai_constants.liability
729 ) ;
730
731 /*******************************
732 || Get the code combination id
733 || for the "INTERIM LIABILITY ACCOUNT"
734 *******************************/
735 ln_intliab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
736 p_regime_id => p_regime_id ,
737 p_organization_type => jai_constants.orgn_type_io ,
738 p_organization_id => p_organization_id ,
739 p_location_id => p_location_id ,
740 p_tax_type => rec_cur_get_deliveries.tax_type ,
741 p_account_name => jai_constants.liability_interim
742 ) ;
743
744
745 /* Start - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
746 lc_account_name := jai_constants.liability;
747 ln_charge_ac_id := ln_liab_acct_ccid;
748 ln_balancing_ac_id := ln_intliab_acct_ccid;
749 ln_debit_amount := rec_cur_get_deliveries.func_tax_amount;
750 IF ln_charge_ac_id IS NULL OR
751 ln_balancing_ac_id IS NULL
752 THEN
753 record_debug_messages('9 VAT delivery accounting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime');
754 p_process_flag := jai_constants.expected_error;
755 p_process_message := 'VAT delivery accounting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
756 return;
757 END IF;
758 END IF;
759
760 --Date 14/06/2007 by sacsethi for bug 6072461
761 -- FOR VAT REVERSAL
762
763 /*
764 || Validate that if any one of the liability account or interim liability account is not defined then error our
765 */
766 /*
767 IF ln_liab_acct_ccid IS NULL OR
768 ln_intliab_acct_ccid IS NULL
769 THEN
770 record_debug_messages('9 VAT delivery accouting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime');
771 p_process_flag := jai_constants.expected_error;
772 p_process_message := 'VAT delivery accouting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
773 return;
774 END IF;
775 END IF ;
776 */
777
778 record_debug_messages ('10 Processing the delivery, parameters are delivery_id -> ' || rec_cur_get_deliveries.delivery_id
779 ||', source_document_type_id i.e delivery_detail_id -> ' || rec_cur_get_deliveries.delivery_detail_id
780 ||', rec_cur_get_deliveries.tax_type -> ' || rec_cur_get_deliveries.tax_type
781 ||', p_organization_id -> ' || p_organization_id
782 ||', p_location_id -> ' || p_location_id
786 ||', p_charge_account_id-> ' || ln_liab_acct_ccid
783 ||', vat_exemption_flag -> ' || rec_cur_get_deliveries.vat_exemption_flag
784 ||', pn_assessable_value ->' || rec_cur_get_deliveries.vat_assessable_value
785 ||', account_name -> ' || jai_constants.liability
787 ||', p_balancing_account_id-> ' || ln_intliab_acct_ccid
788 ||',ln_debit_amount -> ' || ln_debit_amount
789 ||',ln_credit_amount -> ' || ln_credit_amount
790 ||', p_amount-> ' || rec_cur_get_deliveries.func_tax_amount
791 ||', p_trx_amount-> ' || rec_cur_get_deliveries.func_tax_amount
792 ||', p_tax_rate -> ' || rec_cur_get_deliveries.tax_rate
793 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_deliveries.tax_id
794 ||', p_inv_organization_id -> ' || p_organization_id
795 ||', p_attribute1 i.e delivery_id -> ' || rec_cur_get_deliveries.delivery_id
796 ||', p_attribute2 i.e order_line_id -> ' || rec_cur_get_deliveries.order_line_id
797 ||', p_attribute_context -> ' || jai_constants.contxt_delivery
798 );
799
800 record_debug_messages ('11 Before call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
801
802
803 if jai_pa_billing_pkg.gv_debug then
804 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '6 rgm_om_ar_vat_accnt_pkg.process_order_invoice. Bef jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
805 end if;
806
807 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
808 pn_repository_id => ln_repository_id ,
809 pn_regime_id => p_regime_id ,
810 pv_tax_type => rec_cur_get_deliveries.tax_type ,
811 pv_organization_type => jai_constants.orgn_type_io ,
812 pn_organization_id => p_organization_id ,
813 pn_location_id => p_location_id ,
814 pv_source => p_source ,
815 pv_source_trx_type => lv_source_trx_type, /* Bug# 6012570 (5876390) jai_constants.source_ttype_delivery , */
816 pv_source_table_name => lv_source_table_name, /* Bug# 6012570 (5876390) jai_constants.tname_dlry_dtl , */
817 pn_source_id => ln_source_id, /* Bug# 6012570 (5876390) rec_cur_get_deliveries.delivery_detail_id , */
818 pd_transaction_date => rec_cur_get_deliveries.creation_date ,
819 /* Date 14/06/2007 by sacsethi for bug 6072461
820 pv_account_name => jai_constants.liability ,
821 pn_charge_account_id => ln_liab_acct_ccid ,
822 pn_balancing_account_id => ln_intliab_acct_ccid ,
823 Changes in account name , charge account id and balancing account id */
824 pv_account_name => lc_account_name ,
825 pn_charge_account_id => ln_charge_ac_id ,
826 pn_balancing_account_id => ln_balancing_ac_id ,
827 pn_credit_amount => ln_credit_amount ,
828 pn_debit_amount => ln_debit_amount ,
829 pn_assessable_value => rec_cur_get_deliveries.vat_assessable_value ,
830 pn_tax_rate => rec_cur_get_deliveries.tax_rate ,
831 pn_reference_id => rec_cur_get_deliveries.tax_id ,
832 pn_batch_id => p_batch_id ,
833 pn_inv_organization_id => p_organization_id ,
834 pv_invoice_no => p_vat_invoice_no ,
835 pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_deliveries.creation_date) ,
836 pv_called_from => lv_called_from, /* Bug# 6012570 (5876390) jai_constants.vat_repo_call_from_om_ar , */
837 pv_process_flag => lv_process_flag ,
838 pv_process_message => lv_process_message ,
839 --Modified by Bo Li for replacing old attribtue columns with new ones Begin
840 -----------------------------------------------------------------------------------------------------------------
844 pv_trx_reference3 => NULL ,
841 pv_trx_reference_context => lv_attribute_context, /* Bug# 6012570 (5876390) jai_constants.contxt_delivery , */
842 pv_trx_reference1 => rec_cur_get_deliveries.delivery_id ,
843 pv_trx_reference2 => rec_cur_get_deliveries.order_line_id ,
845 pv_trx_reference4 => NULL ,
846 pv_trx_reference5 => NULL
847 -----------------------------------------------------------------------------------------------------------------
848 --Modified by Bo Li for replacing old attribtue columns with new ones End
849 );
850
851 if jai_pa_billing_pkg.gv_debug then
852 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '7 rgm_om_ar_vat_accnt_pkg.process_order_invoice. After callto insert_vat_repository_entry'
853 ||', ln_repository_id:'||ln_repository_id
854 ||', lv_process_flag:'||lv_process_flag
855 ||', lv_process_message:'||lv_process_message
856 );
857 end if;
858
859 IF lv_process_flag = jai_constants.expected_error OR
860 lv_process_flag = jai_constants.unexpected_error
861 THEN
862 /*
863 || As Returned status is an error hence:-
864 ||1. Delivery processing should be terminated,Rollback the insert and exit Loop
865 ||2. Set out variables p_process_flag and p_process_message accordingly
866 ||3. Return from the procedure
867 */
868 record_debug_messages(' 12 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
869 ||', lv_process_message' || lv_process_message
870 ||', Delivery id - ' || rec_cur_get_deliveries.delivery_id
871 ||', Delivery_details_id -> '|| rec_cur_get_deliveries.delivery_detail_id
872 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
873 ||', Order_Line_Id -> ' || rec_cur_get_deliveries.order_line_id
874 -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
875 ||', Tax_amount -> ' || rec_cur_get_deliveries.func_tax_amount
876 ||', Tax_id -> ' || rec_cur_get_deliveries.tax_id
877 ||', Tax_type -> ' || rec_cur_get_deliveries.tax_type
878 );
879 p_process_flag := lv_process_flag ;
880 p_process_message := lv_process_message ;
881 return;
882 END IF;
883
884 record_debug_messages ('13 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry and ');
885
886 IF rec_cur_get_deliveries.vat_exemption_flag = 'N' THEN
887 record_debug_messages ('13.1 before call to jai_cmn_rgm_recording_pkg.do_vat_accounting');
888
889
890 record_debug_messages ('14 Processing the delivery,parameters are delivery_id -> ' || rec_cur_get_deliveries.delivery_id
891 ||', source_document_type_id i.e delivery_detail_id -> ' || rec_cur_get_deliveries.delivery_detail_id
892 ||', rec_cur_get_deliveries.tax_type -> ' || rec_cur_get_deliveries.tax_type
893 ||', p_organization_id -> ' || p_organization_id
894 ||', p_location_id -> ' || p_location_id
895 ||', vat_exemption_flag -> ' || rec_cur_get_deliveries.vat_exemption_flag
896 ||', pn_assessable_value ->' || rec_cur_get_deliveries.vat_assessable_value
897 ||', account_name -> ' || jai_constants.liability
898 ||', p_charge_account_id-> ' || ln_liab_acct_ccid
899 ||', p_balancing_account_id-> ' || ln_intliab_acct_ccid
900 ||', ln_debit_amount -> ' || ln_debit_amount
901 ||', ln_credit_amount -> ' || ln_credit_amount
902 ||', p_amount-> ' || rec_cur_get_deliveries.func_tax_amount
903 ||', p_trx_amount-> ' || rec_cur_get_deliveries.func_tax_amount
904 ||', p_tax_rate -> ' || rec_cur_get_deliveries.tax_rate
905 ||', accounting_date -> ' || nvl(p_default_invoice_date,rec_cur_get_deliveries.creation_date)
906 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_deliveries.tax_id
907 ||', p_inv_organization_id -> ' || p_organization_id
908 ||', p_attribute1 i.e delivery_id -> ' || rec_cur_get_deliveries.delivery_id
909 ||', p_attribute2 i.e order_line_id -> ' || rec_cur_get_deliveries.order_line_id
910 ||', p_attribute_context -> ' || jai_constants.contxt_delivery
911 );
912
913
914 if jai_pa_billing_pkg.gv_debug then
918 jai_cmn_rgm_recording_pkg.do_vat_accounting (
915 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '9 rgm_om_ar_vat_accnt_pkg.process_order_invoice. Before callto jai_cmn_rgm_recording_pkg.do_vat_accounting.');
916 end if;
917
919 pn_regime_id => p_regime_id ,
920 pn_repository_id => ln_repository_id ,
921 pv_organization_type => jai_constants.orgn_type_io ,
922 pn_organization_id => p_organization_id ,
923 /*Check with support whether this should be transaction date or sysdate */
924 pd_accounting_date => nvl(p_default_invoice_date,rec_cur_get_deliveries.creation_date) ,
925 pd_transaction_date => rec_cur_get_deliveries.creation_date ,
926 /* --Date 14/06/2007 by sacsethi for bug 6072461
927 pn_credit_amount => ln_debit_amount ,
928 pn_debit_amount => ln_debit_amount ,
929 pn_credit_ccid => ln_liab_acct_ccid ,
930 pn_debit_ccid => ln_intliab_acct_ccid ,
931 */ pn_credit_amount => nvl(ln_debit_amount,ln_credit_amount) ,
932 pn_debit_amount => nvl(ln_debit_amount,ln_credit_amount) ,
933 pn_credit_ccid => ln_charge_ac_id ,
934 pn_debit_ccid => ln_balancing_ac_id ,
935 pv_called_from => lv_called_from, /* Bug# 6012570 (5876390) jai_constants.vat_repo_call_from_om_ar , */
936 pv_process_flag => lv_process_flag ,
937 pv_process_message => lv_process_message ,
938 pv_tax_type => rec_cur_get_deliveries.tax_type ,
939 pv_source => p_source ,
940 pv_source_trx_type => lv_source_trx_type, /* Bug# 6012570 (5876390) jai_constants.source_ttype_delivery ,*/
941 pv_source_table_name => lv_source_table_name, /* Bug# 6012570 (5876390) jai_constants.tname_dlry_dtl ,*/
942 pn_source_id => ln_source_id, /* Bug# 6012570 (5876390) rec_cur_get_deliveries.delivery_detail_id ,*/
943 pv_reference_name => /*jai_constants.JAI_CMN_TAXES_ALL*/'JA_IN_TAX_CODES' ,
944 pn_reference_id => rec_cur_get_deliveries.tax_id
945 );
946
947 if jai_pa_billing_pkg.gv_debug then
948 jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '9 rgm_om_ar_vat_accnt_pkg.process_order_invoice. After callto do_vat_accounting.'
949 ||', lv_process_flag:'||lv_process_flag
950 ||', lv_process_message:'||lv_process_message
951 );
952 end if;
953
954
955 IF lv_process_flag = jai_constants.expected_error OR
956 lv_process_flag = jai_constants.unexpected_error
957 THEN
958 /*
959 || As Returned status is an error hence:-
960 ||1. Delivery processing should be terminated,Rollback the insert and exit Loop
961 ||2. Set out variables p_process_flag and p_process_message accordingly
962 ||3. Return from the procedure
963 */
964 record_debug_messages(' 15 Error in call to jai_cmn_rgm_recording_pkg.do_vat_accounting - lv_process_flag '||lv_process_flag
965 ||', lv_process_message' || lv_process_message
966 ||', Delivery id - ' || rec_cur_get_deliveries.delivery_id
967 ||', Delivery_details_id -> '|| rec_cur_get_deliveries.delivery_detail_id
968 ||', Tax_amount -> ' || rec_cur_get_deliveries.func_tax_amount
969 ||', Tax_id -> ' || rec_cur_get_deliveries.tax_id
970 ||', Tax_type -> ' || rec_cur_get_deliveries.tax_type
971 );
972 p_process_flag := lv_process_flag ;
973 p_process_message := lv_process_message ;
974 return;
975 END IF;
976
977 record_debug_messages ('16 Returned from jai_cmn_rgm_recording_pkg.do_vat_accounting');
978 END IF; /* End IF of Vat Exemption Flag*/
979
980 END LOOP;
981
982 /*added by rchandan for bug#6030615*/
983 ELSIF p_source='INTERORG_XFER' THEN
984 --jai_constants.source_intxfer
985 /*
986 || Process Interorg Transfers - INV side processing
987 */
988 ln_rgm_cnt := 0;
989 FOR rec_cur_get_mtl_txns IN cur_get_mtltxns
990 LOOP
991 open c_chk_rgm_trxs(rec_cur_get_mtl_txns.transaction_header_id,rec_cur_get_mtl_txns.transaction_temp_id,rec_cur_get_mtl_txns.tax_id);
992 fetch c_chk_rgm_trxs into ln_rgm_cnt;
993 close c_chk_rgm_trxs;
994
995 if nvl(ln_rgm_cnt,0) = 0 then
996 record_debug_messages (' ************7 PROCESSING Delivery id -> '|| p_delivery_id||'Delivery Details ID -> '||rec_cur_get_mtl_txns.transaction_temp_id||' ************' );
997 /*******************************
1001 ln_intliab_acct_ccid := null;
998 ||Variable Initialization
999 *******************************/
1000 ln_liab_acct_ccid := null;
1002 ln_credit_amount := null;
1003 ln_debit_amount := null;
1004 ln_recov_acct_ccid := null;
1005 ln_expense_acct_ccid := null;
1006 ln_charge_ac_id := null;
1007 ln_balancing_ac_id:= null;
1008 lc_account_name := null;
1009 record_debug_messages ('8 Variables Initialised');
1010 IF ( rec_cur_get_mtl_txns.tax_type = 'VAT REVERSAL' ) THEN
1011 /*******************************
1012 ||Get the code combination id
1013 ||for the "RECOVERY ACCOUNT"
1014 *******************************/
1015 ln_recov_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1016 p_regime_id => p_regime_id ,
1017 p_organization_type => jai_constants.orgn_type_io ,
1018 p_organization_id => p_organization_id ,
1019 p_location_id => p_location_id ,
1020 p_tax_type => rec_cur_get_mtl_txns.tax_type ,
1021 p_account_name => jai_constants.recovery
1022 ) ;
1023 /*******************************
1024 || Get the code combination id
1025 || for the "EXPENSE ACCOUNT"
1026 *******************************/
1027 ln_expense_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1028 p_regime_id => p_regime_id ,
1029 p_organization_type => jai_constants.orgn_type_io ,
1030 p_organization_id => p_organization_id ,
1031 p_location_id => p_location_id ,
1032 p_tax_type => rec_cur_get_mtl_txns.tax_type ,
1033 p_account_name => jai_constants.expense
1034 ) ;
1035 lc_account_name := jai_constants.recovery;
1036 ln_charge_ac_id := ln_recov_acct_ccid;
1037 ln_balancing_ac_id := ln_expense_acct_ccid;
1038 ln_debit_amount := rec_cur_get_mtl_txns.tax_amt; /* Modified ln_credit_amount to ln_debit_amount for bug 8657720 by vumaasha */
1039
1040 IF ln_charge_ac_id IS NULL OR
1041 ln_balancing_ac_id IS NULL
1042 THEN
1043 record_debug_messages('9 VAT delivery accounting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT Reversal');
1044 p_process_flag := jai_constants.expected_error;
1045 p_process_message := 'VAT delivery accounting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT reversal';
1046 return;
1047 END IF;
1048 ELSE
1049 /*******************************
1050 ||Get the code combination id
1051 ||for the "LIABILITY ACCOUNT"
1052 *******************************/
1053 ln_liab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1054 p_regime_id => p_regime_id ,
1055 p_organization_type => jai_constants.orgn_type_io ,
1056 p_organization_id => p_organization_id ,
1057 p_location_id => p_location_id ,
1058 p_tax_type => rec_cur_get_mtl_txns.tax_type ,
1059 p_account_name => jai_constants.liability
1060 ) ;
1061 /*******************************
1062 || Get the code combination id
1063 || for the "INTERIM LIABILITY ACCOUNT"
1064 *******************************/
1065 ln_intliab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1066 p_regime_id => p_regime_id ,
1067 p_organization_type => jai_constants.orgn_type_io ,
1068 p_organization_id => p_organization_id ,
1069 p_location_id => p_location_id ,
1070 p_tax_type => rec_cur_get_mtl_txns.tax_type ,
1071 p_account_name => jai_constants.liability_interim
1072 ) ;
1073 lc_account_name := jai_constants.liability;
1074 ln_charge_ac_id := ln_liab_acct_ccid;
1075 ln_balancing_ac_id := ln_intliab_acct_ccid;
1076 ln_debit_amount := rec_cur_get_mtl_txns.tax_amt;
1077 IF ln_charge_ac_id IS NULL OR
1078 ln_balancing_ac_id IS NULL
1079 THEN
1080 record_debug_messages('9 VAT delivery accounting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime');
1081 p_process_flag := jai_constants.expected_error;
1082 p_process_message := 'VAT delivery accounting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
1083 return;
1084 END IF;
1085 END IF;
1086 record_debug_messages ('10 Processing the delivery, parameters are delivery_id -> ' || rec_cur_get_mtl_txns.transaction_header_id
1087 ||', source_document_type_id i.e delivery_detail_id -> ' || rec_cur_get_mtl_txns.transaction_temp_id
1091 ||', vat_exemption_flag -> ' || null
1088 ||', rec_cur_get_deliveries.tax_type -> ' || rec_cur_get_mtl_txns.tax_type
1089 ||', p_organization_id -> ' || p_organization_id
1090 ||', p_location_id -> ' || p_location_id
1092 ||', pn_assessable_value ->' || rec_cur_get_mtl_txns.vat_assessable_value
1093 ||', account_name -> ' || lc_account_name
1094 ||', p_charge_account_id-> ' || ln_charge_ac_id
1095 ||', p_balancing_account_id-> ' || ln_balancing_ac_id
1096 ||',ln_debit_amount -> ' || ln_debit_amount
1097 ||',ln_credit_amount -> ' || ln_credit_amount
1098 ||', p_amount-> ' || rec_cur_get_mtl_txns.tax_amt
1099 ||', p_trx_amount-> ' || rec_cur_get_mtl_txns.tax_amt
1100 ||', p_tax_rate -> ' || rec_cur_get_mtl_txns.tax_rate
1101 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_mtl_txns.tax_id
1102 ||', p_inv_organization_id -> ' || p_organization_id
1103 ||', p_attribute1 i.e delivery_id -> ' || rec_cur_get_mtl_txns.transaction_header_id
1104 ||', p_attribute2 i.e order_line_id -> ' || null
1105 ||', p_attribute_context -> ' || jai_constants.contxt_delivery
1106 );
1107 record_debug_messages ('11 Before call to jai_cmn_rgm_recording_pkg.insert_repository_entry');
1108 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
1109 pn_repository_id => ln_repository_id ,
1110 pn_regime_id => p_regime_id ,
1111 pv_tax_type => rec_cur_get_mtl_txns.tax_type ,
1112 pv_organization_type => jai_constants.orgn_type_io ,
1113 pn_organization_id => p_organization_id ,
1114 pn_location_id => p_location_id ,
1115 pv_source => p_source ,
1116 pv_source_trx_type => jai_constants.source_ttype_delivery ,
1117 pv_source_table_name => jai_constants.tname_dlry_dtl ,
1118 pn_source_id => rec_cur_get_mtl_txns.transaction_temp_id ,
1119 pd_transaction_date => rec_cur_get_mtl_txns.creation_date ,
1120 pv_account_name => lc_account_name ,
1121 pn_charge_account_id => ln_charge_ac_id ,
1122 pn_balancing_account_id => ln_balancing_ac_id ,
1123 pn_credit_amount => LN_CREDIT_AMOUNT ,
1124 pn_debit_amount => ln_debit_amount ,
1125 pn_assessable_value => rec_cur_get_mtl_txns.vat_assessable_value ,
1126 pn_tax_rate => rec_cur_get_mtl_txns.tax_rate ,
1127 pn_reference_id => rec_cur_get_mtl_txns.tax_id ,
1128 pn_batch_id => p_batch_id ,
1129 pn_inv_organization_id => p_organization_id ,
1130 pv_invoice_no => p_vat_invoice_no ,
1131 pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_mtl_txns.creation_date) ,
1132 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
1133 pv_process_flag => lv_process_flag ,
1134 pv_process_message => lv_process_message ,
1135 --Modified by Bo Li for replacing old attribtue columns with new ones Begin
1136 -----------------------------------------------------------------------------------------------------------------
1137 pv_trx_reference_context => jai_constants.contxt_delivery ,
1138 pv_trx_reference1 => rec_cur_get_mtl_txns.transaction_header_id ,
1139 pv_trx_reference2 => NULL ,
1143 -----------------------------------------------------------------------------------------------------------------
1140 pv_trx_reference3 => NULL ,
1141 pv_trx_reference4 => NULL ,
1142 pv_trx_reference5 => NULL
1144 --Modified by Bo Li for replacing old attribtue columns with new ones End
1145 );
1146 IF lv_process_flag = jai_constants.expected_error OR
1147 lv_process_flag = jai_constants.unexpected_error
1148 THEN
1149 /*
1150 || As Returned status is an error hence:-
1151 ||1. Delivery processing should be terminated,Rollback the insert and exit Loop
1152 ||2. Set out variables p_process_flag and p_process_message accordingly
1153 ||3. Return from the procedure
1154 */
1155 record_debug_messages(' 12 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
1156 ||', lv_process_message' || lv_process_message
1157 ||', Delivery id - ' ||rec_cur_get_mtl_txns.transaction_header_id
1158 ||', Delivery_details_id -> '|| rec_cur_get_mtl_txns.transaction_temp_id
1159 ||', Tax_amount -> ' || rec_cur_get_mtl_txns.tax_amt
1160 ||', Tax_id -> ' || rec_cur_get_mtl_txns.tax_id
1161 ||', Tax_type -> ' || rec_cur_get_mtl_txns.tax_type
1162 );
1163 p_process_flag := lv_process_flag ;
1164 p_process_message := lv_process_message ;
1165 return;
1166 END IF;
1167 record_debug_messages ('13 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry and ');
1168 record_debug_messages ('13.1 before call to jai_cmn_rgm_recording_pkg.do_vat_accounting');
1169 record_debug_messages ('14 Processing the delivery,parameters are delivery_id -> ' || rec_cur_get_mtl_txns.transaction_header_id
1170 ||', source_document_type_id i.e delivery_detail_id -> ' || rec_cur_get_mtl_txns.transaction_temp_id
1171 ||', rec_cur_get_deliveries.tax_type -> ' || rec_cur_get_mtl_txns.tax_type
1172 ||', p_organization_id -> ' || p_organization_id
1173 ||', p_location_id -> ' || p_location_id
1174 ||', vat_exemption_flag -> ' || 'N'
1175 ||', account_name -> ' || lc_account_name
1176 ||', p_charge_account_id-> ' || ln_charge_ac_id
1177 ||', p_balancing_account_id-> ' || ln_balancing_ac_id
1178 ||', ln_debit_amount -> ' || ln_debit_amount
1179 ||', ln_credit_amount -> ' || ln_credit_amount
1180 ||', p_amount-> ' || rec_cur_get_mtl_txns.tax_amt
1181 ||', p_trx_amount-> ' || rec_cur_get_mtl_txns.tax_amt
1182 ||', p_tax_rate -> ' || rec_cur_get_mtl_txns.tax_rate
1183 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_mtl_txns.tax_id
1184 ||', p_inv_organization_id -> ' || p_organization_id
1185 ||', p_attribute1 i.e delivery_id -> ' || rec_cur_get_mtl_txns.transaction_header_id
1186 ||', p_attribute2 i.e order_line_id -> ' || null
1187 ||', p_attribute_context -> ' || jai_constants.contxt_delivery
1188 );
1189 /*commented by vasavi*/
1190 /*
1191 jai_cmn_rgm_recording_pkg.do_vat_accounting (
1192 pn_regime_id => p_regime_id ,
1193 pn_repository_id => ln_repository_id ,
1194 pv_organization_type => jai_constants.orgn_type_io ,
1195 pn_organization_id => p_organization_id ,
1196 pd_accounting_date => nvl(p_default_invoice_date,rec_cur_get_mtl_txns.creation_date) ,
1197 pd_transaction_date => rec_cur_get_mtl_txns.creation_date ,
1198 pn_credit_amount => nvl(ln_debit_amount,ln_credit_amount) ,
1199 pn_debit_amount => nvl(ln_debit_amount,ln_credit_amount) ,
1200 pn_credit_ccid => ln_charge_ac_id ,
1201 pn_debit_ccid => ln_balancing_ac_id ,
1202 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
1203 pv_process_flag => lv_process_flag ,
1204 pv_process_message => lv_process_message ,
1205 pv_tax_type => rec_cur_get_mtl_txns.tax_type ,
1206 pv_source => p_source ,
1210 pv_reference_name => 'JA_IN_TAX_CODES' ,
1207 pv_source_trx_type => jai_constants.source_ttype_delivery ,
1208 pv_source_table_name => jai_constants.tname_dlry_dtl ,
1209 pn_source_id => rec_cur_get_mtl_txns.transaction_temp_id ,
1211 pn_reference_id => rec_cur_get_mtl_txns.tax_id
1212 );
1213 IF lv_process_flag = jai_constants.expected_error OR
1214 lv_process_flag = jai_constants.unexpected_error
1215 THEN
1216 record_debug_messages(' 15 Error in call to jai_cmn_rgm_recording_pkg.do_vat_accounting - lv_process_flag '||lv_process_flag
1217 ||', lv_process_message' || lv_process_message
1218 ||', Delivery id - ' || rec_cur_get_mtl_txns.transaction_header_id
1219 ||', Delivery_details_id -> '|| rec_cur_get_mtl_txns.transaction_temp_id
1220 ||', Tax_amount -> ' || rec_cur_get_mtl_txns.tax_amt
1221 ||', Tax_id -> ' || rec_cur_get_mtl_txns.tax_id
1222 ||', Tax_type -> ' || rec_cur_get_mtl_txns.tax_type
1223 );
1224 p_process_flag := lv_process_flag ;
1225 p_process_message := lv_process_message ;
1226 return;
1227 END IF;
1228 record_debug_messages ('16 Returned from jai_cmn_rgm_recording_pkg.do_vat_accounting');
1229 --END IF;
1230 */
1231 end if;---ln_rgm_cnt if
1232 END LOOP;
1233
1234 ELSIF upper(p_source) = upper(jai_constants.source_ar) THEN
1235
1236 record_debug_messages ('17 Manual AR processing for customer_trx_id -> '||p_customer_trx_id);
1237 /*
1238 || Process Invoices - AR side processing
1239 */
1240 /* Bug 5739005. Added by vkantamn
1241 * Included parameters for the cursor.
1242 */
1243 FOR rec_cur_get_man_ar_inv_taxes IN cur_get_man_ar_inv_taxes(upper(p_source),jai_constants.source_ttype_man_ar_inv,jai_constants.tname_cus_trx_lines)
1244 LOOP
1245 record_debug_messages (' ************18 PROCESSING customer_trx_id -> '|| p_customer_trx_id
1246 ||'link_to_cust_trx_line_id -> '||rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1247 ||'customer_trx_line_id ->'||rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
1248 ||' ************' );
1249
1250 /*******************************
1251 ||Variable Initialization
1252 *******************************/
1253 ln_liab_acct_ccid := null;
1254 ln_intliab_acct_ccid := null;
1255 ln_charge_ac_id := null;
1256 ln_balancing_ac_id := null;
1257
1258 /* Start - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
1259 ln_credit_amount := null;
1260 ln_debit_amount := null;
1261 ln_recov_acct_ccid := null;
1262 ln_expense_acct_ccid := null;
1263 lc_account_name := null;
1264 /* End - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
1265
1266 record_debug_messages ('19 Variables Initialised');
1267
1268 IF ( rec_cur_get_man_ar_inv_taxes.tax_type = 'VAT REVERSAL' ) THEN
1269 /* Start - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
1270 /*******************************
1271 ||Get the code combination id
1272 ||for the "RECOVERY ACCOUNT"
1273 *******************************/
1274 ln_recov_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1275 p_regime_id => p_regime_id ,
1276 p_organization_type => jai_constants.orgn_type_io ,
1277 p_organization_id => p_organization_id ,
1278 p_location_id => p_location_id ,
1279 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type,
1280 p_account_name => jai_constants.recovery
1281 ) ;
1282
1283 /*******************************
1284 || Get the code combination id
1285 || for the "EXPENSE ACCOUNT"
1286 *******************************/
1287 ln_expense_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1288 p_regime_id => p_regime_id ,
1289 p_organization_type => jai_constants.orgn_type_io ,
1290 p_organization_id => p_organization_id ,
1291 p_location_id => p_location_id ,
1292 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type,
1293 p_account_name => jai_constants.expense
1294 ) ;
1295 IF ln_recov_acct_ccid IS NULL OR
1296 ln_expense_acct_ccid IS NULL
1297 THEN
1298 record_debug_messages('20 VAT receivables accouting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT Reversal');
1299 p_process_flag := jai_constants.expected_error;
1303 /* End - sacsethi, Added w.r.t BUG#6072461 ( for VAT Reversal)*/
1300 p_process_message := 'VAT receivables accouting entries cannot be passed. Please set up the Recovery account and the Expense account for VAT Reversal';
1301 return;
1302 END IF;
1304 ELSE
1305
1306 /*******************************
1307 ||Get the code combination id
1308 ||for the "LIABILITY ACCOUNT"
1309 *******************************/
1310 ln_liab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1311 p_regime_id => p_regime_id ,
1312 p_organization_type => jai_constants.orgn_type_io ,
1313 p_organization_id => p_organization_id ,
1314 p_location_id => p_location_id ,
1315 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
1316 p_account_name => jai_constants.liability
1317 ) ;
1318
1319 /*******************************
1320 || Get the code combination id
1321 || for the "INTERIM LIABILITY ACCOUNT"
1322 *******************************/
1323 ln_intliab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
1324 p_regime_id => p_regime_id ,
1325 p_organization_type => jai_constants.orgn_type_io ,
1326 p_organization_id => p_organization_id ,
1327 p_location_id => p_location_id ,
1328 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
1329 p_account_name => jai_constants.liability_interim
1330 ) ;
1331
1332
1333 /*
1334 || Validate that if any one of the liability account or interim liability account is not defined then error our
1335 */
1336 IF ln_liab_acct_ccid IS NULL OR
1337 ln_intliab_acct_ccid IS NULL
1338 THEN
1339 record_debug_messages('20 VAT receivable accouting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime');
1340 p_process_flag := jai_constants.expected_error;
1341 p_process_message := 'VAT receivable accouting entries cannot be passed. Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
1342 return;
1343 END IF;
1344 END IF;
1345
1346 /*********************************************************************************************************************************
1347 || Population of Credit and debit amounts and CCID in case Invoice and Credit Memo
1348 +=============================================================================================================================+
1349 || ||||<---------------VAT REPOSITORY ENTRY--------------->||||<-------------GL INTERFACE---------------->
1350 ||================ ||||====================================================||||==================================================
1351 ||Transaction Type ||||CHARGE A/C || BALANCING A/C || CR || DR ||||Slno || Account ID || CR || DR ||
1352 ||================ ||||============ ||=================||=======||=======||||=====||===============||=======||=======||
1353 || Invoice/ |||| Liab A/C || Int Liab A/C || 0 || 100 ||||1. || Liab A/C || 100 || 0 ||
1354 || Debit Memo |||| || || || ||||2. || Int Liab A/C || 0 || 100 ||
1355 || |||| || || || |||| || || || ||
1356 ||=================||||===============||=================||=======||=======||||=====||===============||=======||=======||
1357 || |||| || || || |||| || || || ||
1358 || Credit Memo |||| Int Liab A/C || Liab A/C || 100 || 0 ||||1. || Int Liab A/C || 100 || 0 ||
1359 || |||| || || || ||||2. || Liab A/C || 0 || 100 ||
1360 +==============================================================================================================================+
1361
1362 *********************************************************************************************************************************/
1363
1364
1365 IF p_transaction_type IN ('INV','DM') THEN
1366 /* Start - sacsethi, Added w.r.t 6072461 ( for VAT Reversal)*/
1367 IF ( rec_cur_get_man_ar_inv_taxes.tax_type = 'VAT REVERSAL' ) THEN
1368 lc_account_name := jai_constants.recovery ;
1369 ln_charge_ac_id := ln_recov_acct_ccid ;
1370 ln_balancing_ac_id := ln_expense_acct_ccid ;
1371 ln_credit_amount := null ;
1372 ln_debit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ; /* modified for bug 8657720 by vumaasha */
1373
1377 if sign(rec_cur_get_man_ar_inv_taxes.func_tax_amount)<0
1374 ELSE
1375 lc_account_name := jai_constants.liability ;
1376 --start additions for bug#10189258
1378 then
1379 ln_charge_ac_id := ln_intliab_acct_ccid ;
1380 ln_balancing_ac_id := ln_liab_acct_ccid ;
1381 ln_debit_amount := null ;
1382 ln_credit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ;
1383 else
1384 ln_charge_ac_id := ln_liab_acct_ccid ;
1385 ln_balancing_ac_id := ln_intliab_acct_ccid ;
1386 ln_debit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ;
1387 ln_credit_amount := null ;
1388 END IF;
1389 --end additions for bug#10189258
1390 END IF;
1391
1392
1393 ELSIF p_transaction_type = 'CM' THEN
1394 lc_account_name := jai_constants.recovery ; -- Added By Bo Li for Bug#9766552 on 2010-06-09
1395 ln_charge_ac_id := ln_intliab_acct_ccid ;
1396 ln_balancing_ac_id := ln_liab_acct_ccid ;
1397 ln_debit_amount := null ;
1398 ln_credit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ;
1399 END IF;
1400
1401
1402
1403 record_debug_messages ('21 Processing the manual_ar_invoice , Parameters passed are for the customer_trx_id -> ' || p_customer_trx_id
1404 ||', line.customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1405 ||', source_document_type_id tax.customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
1406 ||', rec_cur_get_man_ar_inv_taxes.tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
1407 ||', p_organization_id -> ' || p_organization_id
1408 ||', p_location_id -> ' || p_location_id
1409 ||', vat_exemption_flag -> ' || rec_cur_get_man_ar_inv_taxes.vat_exemption_flag
1410 ||', pn_assessable_value ->' || rec_cur_get_man_ar_inv_taxes.vat_assessable_value
1411 ||', account_name -> ' || jai_constants.liability
1412 ||', p_charge_account_id-> ' || ln_charge_ac_id
1413 ||', p_balancing_account_id-> ' || ln_balancing_ac_id
1414 ||', ln_debit_amount -> ' || ln_debit_amount
1415 ||', ln_credit_amount -> ' || ln_credit_amount
1416 ||', p_amount-> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1417 ||', p_trx_amount-> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1418 ||', p_tax_rate -> ' || rec_cur_get_man_ar_inv_taxes.tax_rate
1419 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
1420 ||', p_inv_organization_id -> ' || p_organization_id
1421 ||', p_attribute1 i.e customer_trx_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_id
1422 ||', p_attribute2 i.e link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1423 ||', p_attribute_context -> ' || jai_constants.contxt_manual_ar
1424 );
1425
1426
1427 record_debug_messages ('22 Before call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
1428
1429 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1430 ----------------------------------------------------------------------------------------
1431 --For 'External' event, VAT repository do not need to be updated.
1432 --For 'External' event, Cr: ccid will be fetched from JAI Interface table, Dr: Receivable account.
1433 IF ln_external_flag > 0 THEN
1434 ln_charge_ac_id := jai_open_api_pkg.get_external_dist_account_ccid(
1435 rec_cur_get_man_ar_inv_taxes.customer_trx_id
1436 , rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1437 , rec_cur_get_man_ar_inv_taxes.tax_line_no
1438 );
1439 --Add by Xiao for bug#11936390
1440 OPEN get_receivable_ac_id_cur(rec_cur_get_man_ar_inv_taxes.customer_trx_id);
1441 FETCH get_receivable_ac_id_cur INTO ln_balancing_ac_id;
1442 CLOSE get_receivable_ac_id_cur;
1443 ELSE
1444 --Only for 'Standard' event in Open Interface, or manual AR transaction,
1445 --VAT repository should be updated.
1449 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
1446 ----------------------------------------------------------------------------------------
1447 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
1448
1450 pn_repository_id => ln_repository_id ,
1451 pn_regime_id => p_regime_id ,
1452 pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
1453 pv_organization_type => jai_constants.orgn_type_io ,
1454 pn_organization_id => p_organization_id ,
1455 pn_location_id => p_location_id ,
1456 pv_source => p_source ,
1457 pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
1458 pv_source_table_name => jai_constants.tname_cus_trx_lines ,
1459 pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
1460 -- pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
1461 /*commented and added the below for bug#13495960*/
1462 pd_transaction_date => nvl(p_default_invoice_date,rec_cur_get_man_ar_inv_taxes.creation_date) ,
1463 pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
1464 pn_charge_account_id => ln_charge_ac_id ,
1465 pn_balancing_account_id => ln_balancing_ac_id ,
1466 pn_credit_amount => ln_credit_amount ,
1467 pn_debit_amount => ln_debit_amount ,
1468 pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
1469 pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
1470 pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
1471 pn_batch_id => p_batch_id ,
1472 pn_inv_organization_id => p_organization_id ,
1473 pv_invoice_no => p_vat_invoice_no ,
1474 pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_man_ar_inv_taxes.creation_date) ,
1475 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
1476 pv_process_flag => lv_process_flag ,
1477 pv_process_message => lv_process_message ,
1478 --Modified by Bo Li for replacing old attribtue columns with new ones Begin
1479 -----------------------------------------------------------------------------------------------------------------
1480 pv_trx_reference_context => jai_constants.contxt_manual_ar ,
1481 pv_trx_reference1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
1482 pv_trx_reference2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
1483 pv_trx_reference3 => NULL ,
1484 pv_trx_reference4 => NULL ,
1485 pv_trx_reference5 => NULL
1486 -----------------------------------------------------------------------------------------------------------------
1490
1487 --Modified by Bo Li for replacing old attribtue columns with new ones End
1488 );
1489
1491
1492 IF lv_process_flag = jai_constants.expected_error OR
1493 lv_process_flag = jai_constants.unexpected_error
1494 THEN
1495 /*
1496 || As Returned status is an error hence:-
1497 ||1. Delivery processing should be terminated,Rollback the insert and exit Loop
1498 ||2. Set out variables p_process_flag and p_process_message accordingly
1499 ||3. Return from the procedure
1500 */
1501 record_debug_messages(' 23 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
1502 ||', lv_process_message' || lv_process_message
1503 ||', customer_trx_id - ' || p_customer_trx_id
1504 ||', customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
1505 ||', link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1506 ||', Tax_amount -> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1507 ||', Tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
1508 ||', Tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
1509 );
1510 p_process_flag := lv_process_flag ;
1511 p_process_message := lv_process_message ;
1512 return;
1513 END IF;
1514
1515 record_debug_messages (' 24 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
1516 END IF;--Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011.
1517 IF rec_cur_get_man_ar_inv_taxes.vat_exemption_flag = 'N' THEN
1518
1519 record_debug_messages ('25 Parameters passed to jai_cmn_rgm_recording_pkg.do_vat_accounting, customer_trx_id -> ' || p_customer_trx_id
1520 ||', line.customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1521 ||', source_document_type_id tax.customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
1522 ||', rec_cur_get_man_ar_inv_taxes.tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
1523 ||', p_organization_id -> ' || p_organization_id
1524 ||', p_location_id -> ' || p_location_id
1525 ||', vat_exemption_flag -> ' || rec_cur_get_man_ar_inv_taxes.vat_exemption_flag
1526 ||', pn_assessable_value ->' || rec_cur_get_man_ar_inv_taxes.vat_assessable_value
1527 ||', account_name -> ' || jai_constants.liability
1528 ||', pn_credit_ccid-> ' || ln_charge_ac_id
1529 ||', pn_debit_ccid -> ' || ln_balancing_ac_id
1530 ||', ln_debit_amount -> ' || ln_debit_amount
1531 ||', ln_credit_amount -> ' || ln_credit_amount
1532 ||', p_amount-> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1533 ||', p_trx_amount-> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1534 ||', p_tax_rate -> ' || rec_cur_get_man_ar_inv_taxes.tax_rate
1535 ||', p_reference_id i.e tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
1536 ||', p_inv_organization_id -> ' || p_organization_id
1537 ||', p_attribute1 i.e customer_trx_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_id
1538 ||', p_attribute2 i.e link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1539 ||', p_attribute_context -> ' || jai_constants.contxt_manual_ar
1540 );
1541
1542 IF --nvl(rec_cur_get_man_ar_inv_taxes.inclusive_tax_flag, 'N') = 'Y' AND
1543 ln_external_flag > 0 THEN --Add by Xiao for bug#11936390.
1544 -- For 'External' event, inclusive/exclusive tax will not generate accounting in gl interface.
1545 NULL;
1546 ELSE
1547 jai_cmn_rgm_recording_pkg.do_vat_accounting (
1548 pn_regime_id => p_regime_id ,
1549 pn_repository_id => ln_repository_id ,
1550 pv_organization_type => jai_constants.orgn_type_io ,
1551 pn_organization_id => p_organization_id ,
1552 /*Check with support whether this should be transaction date or sysdate */
1553 pd_accounting_date => nvl(p_default_invoice_date,rec_cur_get_man_ar_inv_taxes.creation_date) ,
1554 pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
1555 pn_credit_amount => nvl(ln_credit_amount,ln_debit_amount) ,
1556 pn_debit_amount => nvl(ln_debit_amount,ln_credit_amount) ,
1557 pn_credit_ccid => ln_charge_ac_id ,
1558 pn_debit_ccid => ln_balancing_ac_id ,
1559 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
1560 pv_process_flag => lv_process_flag ,
1561 pv_process_message => lv_process_message ,
1562 pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
1563 pv_source => p_source ,
1564 pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
1565 pv_source_table_name => jai_constants.tname_cus_trx_lines ,
1566 pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
1567 pv_reference_name => /*jai_constants.JAI_CMN_TAXES_ALL*/ 'JA_IN_TAX_CODES' ,
1568 pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id
1569 );
1570
1571 IF lv_process_flag = jai_constants.expected_error OR
1572 lv_process_flag = jai_constants.unexpected_error
1573 THEN
1574 /*
1575 || As Returned status is an error hence:-
1576 ||1. Delivery processing should be terminated,Rollback the insert and exit Loop
1577 ||2. Set out variables p_process_flag and p_process_message accordingly
1578 ||3. Return from the procedure
1579 */
1580 record_debug_messages(' 26 Error in call to jai_cmn_rgm_recording_pkg.do_vat_accounting - lv_process_flag '||lv_process_flag
1581 ||', lv_process_message' || lv_process_message
1582 ||', customer_trx_id - ' || p_customer_trx_id
1583 ||', customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
1584 ||', link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
1585 ||', Tax_amount -> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
1586 ||', Tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
1587 ||', Tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
1588 );
1589 p_process_flag := lv_process_flag ;
1590 p_process_message := lv_process_message ;
1591 return;
1592 END IF;
1593 END IF; --Add by Xiao for bug#11936390.
1594
1595 record_debug_messages ('27 Returned from jai_cmn_rgm_recording_pkg.do_vat_accounting');
1596 END IF; /* ENd if OF VAT Exemption = 'N'*/
1597
1598 END LOOP;
1599
1600 END IF;
1601
1602 record_debug_messages ('28 ********************************END OF PROCESS_ORDER_INVOICE********************************');
1603
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 record_debug_messages (' 29 In Exception Section - SQLERRM ->'||substr(sqlerrm,1,300) );
1607 p_process_flag := jai_constants.unexpected_error ;
1608 p_process_message := 'Unexpeced error occured in procedure process_order_invoice for document_id -> '||
1609 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
1610 -- nvl(p_delivery_id,p_customer_trx_id)
1611 nvl(p_delivery_id, NVL(p_order_line_id, p_customer_trx_id))
1612 -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
1613 ||substr(SQLERRM,1,300);
1614
1615 END process_order_invoice;
1616
1617 END jai_cmn_rgm_vat_accnt_pkg;