1 PACKAGE BODY jai_cmn_rgm_recording_pkg AS
2 /* $Header: jai_cmn_rgm_rec.plb 120.37.12020000.4 2013/01/24 05:02:51 zxin ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY for FILENAME: jai_rgm_trx_recording_pkg_b.sql
6 S.No dd/mm/yyyy Author and Details
7 ------------------------------------------------------------------------------------------------------------------------------
8 1 15/12/2004 Vijay Shankar for Bug# 4068823, Version:115.0
9
10 Coded for recording Service Tax into repository and related Accounting into GL
11
12 - INSERT_REPOSITORY_ENTRY : Based on the input source, this procedure derives the type of entry that has to be made into
13 the repository. Also accounting entries related to repository entry are passed only if p_accntg_required_flag parameter
14 is 'Y'. This also passes discount accounting if the input parameter p_discounted_amount has a value
15
16 - GET_ACCOUNT : Returns the CODE_COMBINATION_ID related to the inputs passed. This returns values from regime setup
17 incase of service tax and from Organization Addl. info incase of inventory organization and from Base Setup incase of
18 AP discounts
19
20 - GET_PERIOD_NAME : Returns the period_name for which the entry is being made. This also returns the accounting_date as
21 first_date of next open period, if the period corresponding to input accounting date is closed
22
23 - POST_ACCOUNTING : Inserts an Entry into GL_INTERFACE and Localization Subledger for the inputs passed to the call
24
25 - INSERT_REFERENCE : Called from AP and AR Processing to insert data related to related Invoices
26
27 - UPDATE_REFERENCE : Called from AP and AR Processing to update revocovered and discounted amounts for the invoice
28
29
30 2. Bug# 4193633 - Aiyer - 15-feb-2005
31
32 Issue
33 The tax earned and unearned discount are not getting apportioned properly of service type of taxes and hence the India - Service Tax concurrent
34 ends up in a warning for records with these issues
35
36 Reason:-
37 In case of invoices having Service taxes and other type of taxes, the tax earned and unearned discounts should be approtioned across all the type of taxes
38 (Both Service and Non Service).
39 This apportionment logic was not present initially. This needs to be added
40
41 Fix: -
42 Modified the procedure. Did the following :-
43 1. Added a extra parameter p_total_disc_amount to the procedure.
44 2. used this parameter to apportion the tax earned discount amount and tax unearned discount amount
45
46 Dependency :-
47 In this procedure the added parameter is added to the procedure and hence causes a dependency issue.
48
49 The following objects should be sent together
50
51 1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
52 2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
53 3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
54 4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
55
56
57 3. Bug# 4204880 - ssumaith - 20-feb-2005 - File version 115.2
58
59 A new column has been added into the table jai_Rgm_trx_Records called regime_primary_regno and it has been
60 included in the insert column list in the table jai_Rgm_trx_Records
61 A cursor has been added to fetch the primary registration number.
62
63 Dependency :-
64 High . A new column has been added into the jai_rgm_trx_records table which also needs data to be populated
65 If this file is sent alone , it will cause a dependency issue.
66 Need to ensure that the new column needs to be part of the table.
67
68
69 4 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.3
70 .added two new procedure insert_vat_repository_entry and do_vat_accounting, a function get_rgm_attribute_value
71 as part of VAT Impl.
72 .user_je_category_name that is populated into GL_INTERFACE as jai_constants.je_category_rg_entry ('Register India')
73
74 * This is a Dependant Bug for future Versions of the Object *
75
76 5. 08-Jun-2005 File Version 116.2 Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
77 as required for CASE COMPLAINCE.
78
79 6. 14-Jun-2005 rchandan for bug#4428980, Version 116.3
80 Modified the object to remove literals from DML statements and CURSORS.
81
82 7. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
83 GL Sources and GL Categories got changed. Refer bug for the details
84
85
86
87 DEPENDANCY:
88 -----------
89 IN60106 + 4239736 + 4245089
90
91
92 COMMON API that will be called from DIFFERENT Transactions of the Regime
93 This will call APIS to insert data into regime repository and GL Tables
94 Transactions that are calling this procedure are
95
96 1) AP Invoice Payments
97 2) AR Receipt applications onto Invoice
98 3) Service Tax Manual Entry form
99 4) Settlement Process
100 5) Distribution Process
101
102 8. 11-Aug-2005 Ramananda for Bug#4546114. File Version 120.2
103 In case of distribution from IO to OU , the accounting for cess transferred is hitting
104 the cenvat RM or Cenvat CG account instead of the cess account.
105
106 After this fix, the accounts that will be hit are the cenvat rm a/c / cenvat cg a/c
107 for the excise amt and the edu cess rm a/c / edu cess cg a/c for the cess amt.
108
109 Dependency due to this fix:
110 None
111
112 9. 30-JAN-2007 CSahoo for bug#5631784. File Version 120.4
113 Forward Porting of Bug#4742259 (TCS solution)
114 Function get_account is modified to give the accounts for TCS regime also.
115
116
117 10. 16/04/2007 kunkumar for forward porting to R12 bugnos 5003538 5051541 and 4543358
118
119
120 11. 14-05-2007 ssawant for bug 5879769,
121 Objects was not compiling. so changes are done to make it compiling.
122 12. 18-may-2009 vkaranam for bug#7010029 120.14.12010000.7/120.20
123 Issue: VAT ACCOUNTING ENTRIES FOR AR INVOICE GENERATED IN FUTRE PERIOD
124 Fix: Modified the cursor c_period_dtl in the procedure get_period_name.
125 Added the following AND condition
126 AND closing_status in ('O','F')
127 Added a order by clause also.
128 13. 05-Feb-2009 CSahoo for bug#9350172
129 ISSUE: FPBUG:CAN NOT ADD TAXES SUCCESSFULLY IN ENTER TXN INDIA LOCALIZATION FORM
130 FIX: Added an input parameter pn_settlement_id to the procedure insert_vat_repository_entry
131 This parameter pn_settlement_id is used to populate the settlement_id in the table
132 jai_rgm_trx_records.
133
134 14. 4-Apr-2010 Bo Li for Bug9305067
135 Modify the procedure insert_repository_entry and insert_vat_repository_entry.
136 Replace the attribute parameters with new meaningful parameters
137
138 15. 18-Apr-2012 mmurtuza for bug 12641455
139 Description: FOR TCS SETTLEMENT ORGANIZATION AND LOCATION FIELD IS DISABLED IN SETTLEMENT FORM
140 Fix: Added code for TCS in post_accounting procedure
141
142
143 Future Dependencies For the release Of this Object:-
144 (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/
145 A datamodel change )
146 jai_rgm_trx_recording_pkg_b.sql
147 ----------------------------------------------------------------------------------------------------------------------------------------------------
148 Current Version Current Bug Dependent Files Version Author Date Remarks
149 Of File On Bug/Patchset Dependent On
150
151
152 115.1 4204880 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
153
154 jai_rgm_process_ar_taxes_pkg_s.sql 115.1 aiyer 21-feb-05 signature change in parameters.
155 jai_rgm_process_ar_taxes_pkg_b.sql 115.1 aiyer 21-feb-05 signature change in parameters.
156
157 115.3 4245089 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
158 + 4204880
159
160
161
162
163 10. 01-MAR-2007 SSAWANT , File version 120.5
164 Forward porting the change in 11.5 bug 5642053 to R12 bug no 5662296.
165
166 Issue : PROCESSING SETTLEMENT (INDIA LOCAL) ON THE CURRENT DATE AND AT ORG LEVEL
167 Fix : Previously whenever transaction_date was less than or equal to last_settlement_date
168 it was modified to last_settlement_date + 1. Now this would be done only if
169 transaction_date is less than last_settlement_date as transactions can be
170 done on last_settlement_date.
171 11. 03-MAR-07 bduvarag, File version 120.5
172 Forward porting the change in 11.5 bug 5051541 to R12 bug
173
174
175 12 25-April-2007 ssawant for bug 5879769 ,File version 120.6
176 Forward porting of
177 ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION from 11.5( bug no 5694855) to R12 (bug no 5879769).
178 Fix : A new parameter p_service_type_code is added to insert_repository procedure.
179 This is used to insert into jai_rgm_trx_records.
180 A new column repository_id is added to jai_sla_entries and so the insert statement
181 is modified to insert the repository id
182 The procedure get_account is modified to return the account if the regime is SERVICE, Org Type is IO and the
183 tax is not of EXCISE or EXCISE CESS types
184 13 02/05/2006 vkaranam bug#5989740 - File version 120.8
185
186 Forward porting of 115 bug #5907436
187 ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
188
189 additional cess of 1% on all taxes to be levied to fund secondary education and higher
190 education .
191
192 Code Changes - Cursor c_orgn_sh_cess_account is added to get code_combination_id for secondary and higher cess types .
193
194 14. 07/06/2007 sacsethi for bug 6109941
195 R12RUP03-ST1: CODE REVIEW COMMENTS FOR ENHANCEMENTS
196
197 Problem - when we trying to get code combination id for discount in AP , then we were passing
198 organization id but we defined code conbination id at OU Level in AP ,
199 wheich was resulting in error
200
201 Solution - 1. Now passing ln_org_id instead of organziation_id for discounts .
202 2. procedure post_accounting is changed to return if both credit and debit amount is zero
203 instead of generating oracle error.
204
205 15. 27/06/2007 CSahoo for bug#6155839, File Version 120.11
206 added the lv_source_name variable to get the service tax source or vat source depending on the value of the regime.
207
208 16. 07/12/2007 ssumaith - bug# 6664855 - file version - 120.3.12000000.5.
209 Issue :-
210 When service tax distribution is done between two inventory organizations,
211 it was causing the unbalances gl entries.
212 Reason being - organziation id was inserted in the reference1 column of gl_interface table.
213 The organization id was entered as source orgn id for source org entries and destination orgn id
214 for des orgn entries as a result, there was only debits or credits for one orgn because referenc1 column
215 is also in the used in the grouping logic.
216
217 Fix:
218
219 This was a forward port issue of the R11i bug# 5410587.
220 It has been forward ported.
221 Changes done are to pass the combination of source org and destination org into reference1 column.
222
223 17. 06-Dec-2009 Bug 7692977 File version 120.14.12010000.3 / 120.16
224 Issue - Duplicate accounting entries are created for service tax distribution.
225 Cause - During distribution, the Dr entry due to SERVICE_DISTRIBUTE_IN will be
226 balanced by the Cr entry due to SERVICE_DISTRIBUTE_OUT. But balancing
227 entries are separately passed for each of these, therefore creating
228 duplicate accounting.
229 Fix - Stopped balancing entry to be passed when the source is SERVICE_DISTRIBUTE_IN or
230 SERVICE_DISTRIBUTE_OUT. Also reverted the changes done for bug 7525691 earlier
231 because it is causing dependency. The same fix can be done without causing the
232 dependency. Refer bug for more details.
233
234 18. 18-Mar-2009 Bug 7525691 File version 120.14.12010000.4/120.17
235 Issue - 1. Both credit and debit entries passed during service tax settlement hit
236 the same account (recovery).
237 2. Duplicate accounting entries are generated during settlement.
238 Fix - This is forward port for bug 7518230. Details:
239 1. Debit entry (source is settle_in) should hit the liability account.
240 Credit entry (source is settle_out) should hit the recovery account.
241 2. Balancing entries should not be passed when source is settle_in or settle_out
242 Along this, bug 8329634 is also fixed. After this fix, balancing entry will not
243 be passed when
244 - source is SETTLE_IN or SETTLE_OUT
245 - source is SERVICE_DISTRIBUTE_IN or SERVICE_DISTRIBUTE_OUT, with
246 distribution type as Service to Service.
247
248 19. 17-May-2009 Bug 7522584
249 Issue : Service Tax entered in foreign currency for AR Invoice is not converted to Functional Currency
250 Fix: Modified the code in the proc insert_repository_entry. Added a multipier to the discount amount
251 so as to calulate the discount amount in functional currency.
252
253 20. 22-May-2009 Bug 8294236
254 Issue: Service Tax Transaction created Fr Exchange Balances on Tax Accounts after Settlement
255 Fix: Created a new procedure exc_gain_loss_accounting for creating the accounting
256 entries for foreign exchange gain or loss amount.
257
258 21. 20-Jul-2010 Bug 9883352
259 Description: Service Tax Processing fails for Bill Only SO. Code Combination ID is not
260 fetched from Regime Registration
261 Fix: Bill Only Sale Orders do not have Shipment. Location ID is passed to Function get_account
262 from the Shipment. get_account function changes Organization Type to OU if Location ID
263 is NULL. But Regime Registration Setup for Service Tax is always based on IO and not on OU
264 Hence removing the piece of code which changes the Organization type.
265
266 22. 24-May-2011 Xiao for pot change, reg bug#12533434.
267 Add parameter p_accrual_basis for procedure insert_repository_entry, and it indicates accrual basis.
268 If accrual basis, do not generate accounting.
269
270 23. 29-May-2011 Xiao for POT change, reg bug#12533434.
271 Fix: Rollback code to 120.14.12010000.11. Use parameter p_accntg_required_flag as no.
272 Add parameter p_accrual_basis.
273 Add accounting derive logic for cancellation event in procedure insert_repository_entry.
274
275 24. 23-Jun-2011 Xiao for bug#12598609.
276 Issue: Parent document details not shown in the journal description
277 Fixed: Attach invoice number to variable lv_reference10 when post_accounting, so that parent document
278 details can be shown in journal description.
279 Add AUTONOMOUS_TRANSACTION PROCEDURE get_inv_num_std to get invoice num, invoice type.
280
281 25. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
282 Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
283
284 26. 14-dec-2011 Qiong for Bug 13456083 - PRE POT INVOICE UPDATE REPOSITORY INCORRECT
285
286 27. 23-Jan-2012 amandali for bug 13430127
287 Issue:ACCOUNTING ENTRIES ARE NOT VISIBLE IN SERVICE TAX REPOSITORY
288 Fix:in post_Accounting, while inserting into jai_cmn_journal_entries, the reference_name is now
289 populated with source transaction type instead of source table name as we do not have repository_id
290 reference in jai_cmn_journal_entries populated post POT
291 15. 20-jan-2012 vkaranam for bug#13593694
292 Issue:
293 Service tax distribution out is updating the repost
294 Fix :
295 For Distribute_out transaction type
296 jai_rgm_trx_records.trx_credit_amount has to hit with the -ve distribution
297
298 changes are done in insert_repository_entry procedure for "SERVICE_dISTRIBUTE_OUT" transaction.
299
300 ----------------------------------------------------------------------------------------------------------------------------*/
301
302
303 /* ~~~~~~~~~~~~~~~ Start of Repository Entry ~~~~~~~~~~~~~~~~~ */
304 PROCEDURE insert_repository_entry(
305 p_repository_id OUT NOCOPY NUMBER,
306 p_regime_id IN NUMBER,
307 p_tax_type IN VARCHAR2,
308 p_organization_type IN VARCHAR2,
309 p_organization_id IN NUMBER,
310 p_location_id IN NUMBER,
311 p_source IN VARCHAR2,
312 p_source_trx_type IN VARCHAR2,
313 p_source_table_name IN VARCHAR2,
314 p_source_document_id IN NUMBER,
315 p_transaction_date IN DATE,
316 p_account_name IN VARCHAR2,
317 p_charge_account_id IN NUMBER,
318 p_balancing_account_id IN NUMBER,
319 p_amount IN OUT NOCOPY NUMBER, -- Recovered/Liable Service Tax Amount in INR Currency i.e functional
320 p_assessable_value IN NUMBER,
321 p_tax_rate IN NUMBER,
322 p_reference_id IN NUMBER,
323 p_batch_id IN NUMBER,
324 p_called_from IN VARCHAR2,
325 p_process_flag OUT NOCOPY VARCHAR2,
326 p_process_message OUT NOCOPY VARCHAR2,
327 p_discounted_amount IN OUT NOCOPY NUMBER,
328 p_inv_organization_id IN NUMBER DEFAULT NULL,
329 p_settlement_id IN NUMBER DEFAULT NULL,
330 -- Following all parameters are required for GL Accounting if p_balancing_account_id value is not passed to this procedure call
331 p_accntg_required_flag IN VARCHAR2, -- DEFAULT jai_constants.yes File.Sql.35 by Brathod
332 p_accounting_date IN DATE , -- DEFAULT sysdate File.Sql.35 by Brathod
333 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
334 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
335 p_balancing_location_id IN NUMBER DEFAULT NULL,
336 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
337 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
338 p_currency_code IN VARCHAR2 , -- DEFAULT jai_constants.func_curr File.Sql.35 by Brathod
339 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
340 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
341 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL,
342 p_trx_amount IN NUMBER DEFAULT NULL, -- recovered/liable service tax amount in foreign currency
343 --Added by Bo Li for Bug9305067 BEGIN
344 ------------------------------------------------------------
345 p_trx_reference_context IN VARCHAR2 DEFAULT NULL,
346 p_trx_reference1 IN VARCHAR2 DEFAULT NULL,
347 p_trx_reference2 IN VARCHAR2 DEFAULT NULL,
348 p_trx_reference3 IN VARCHAR2 DEFAULT NULL,
349 p_trx_reference4 IN VARCHAR2 DEFAULT NULL,
350 p_trx_reference5 IN VARCHAR2 DEFAULT NULL,
351 ------------------------------------------------------------
352 --Added by Bo Li for Bug9305067 End
353
354 p_service_type_code IN VARCHAR2 DEFAULT NULL, /* added by ssawant for bug 5989740 */
355 p_distribution_type IN VARCHAR2 DEFAULT NULL,
356 p_accrual_basis IN VARCHAR2 DEFAULT NULL, --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
357 p_invoice_no IN VARCHAR2 DEFAULT NULL --Added by Qiong for advanced receipts bug#13361952
358 ) IS
359
360 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
361 ln_credit NUMBER;
362 ln_debit NUMBER;
363 ln_trx_credit NUMBER;
364 ln_trx_debit NUMBER;
365
366 lv_register_entry_type VARCHAR2(2);
367 lv_account_name JAI_RGM_TRX_RECORDS.account_name%TYPE;
368 ln_charge_account_id JAI_RGM_TRX_RECORDS.charge_account_id%TYPE;
369 lv_charge_entry_type VARCHAR2(2);
370 lv_period_name GL_PERIODS.period_name%TYPE;
371
372 lv_balancing_tax_type JAI_RGM_TRX_RECORDS.tax_type%TYPE;
373 lv_balancing_orgn_type JAI_RGM_TRX_RECORDS.organization_type%TYPE;
374 ln_balancing_orgn_id JAI_RGM_TRX_RECORDS.organization_id%TYPE;
375 ln_balancing_location_id JAI_RGM_TRX_RECORDS.location_id%TYPE;
376 lv_balancing_accnt_name JAI_RGM_TRX_RECORDS.account_name%TYPE;
377 ln_balancing_account_id JAI_RGM_TRX_RECORDS.charge_account_id%TYPE;
378 lv_balancing_entry_type VARCHAR2(2);
379 lv_bal_entry_period_name GL_PERIODS.period_name%TYPE;
380
381 ln_trx_tax_amount NUMBER;
382 lv_reference_name VARCHAR2(30);
383 ln_reference_id NUMBER;
384 lv_statement VARCHAR2(4);
385
386 ln_discount_ccid NUMBER(15);
387 ln_disc_credit NUMBER;
388 ln_disc_debit NUMBER;
389
390 lv_codepath VARCHAR2(500); -- := '' File.Sql.35 by Brathod
391 ln_trx_amount NUMBER;
392 ld_transaction_date DATE;
393 ld_last_settlement_date DATE;
394
395 ln_earned_discount NUMBER;
396 ln_earned_disc_accnt NUMBER;
397 ln_unearned_discount NUMBER;
398 ln_unearned_disc_accnt NUMBER;
399
400 /*
401 Following cursor added by ssumaith for fetching the primary registration number of the regime
402 It will be inserted into jai_Rgm_trx_records table.
403 Bug# 4204880
404 */
405
406 CURSOR c_primary_regno( p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE ) IS --rchandan for bug#4428980
407 SELECT attribute_value
408 FROM JAI_RGM_ORG_REGNS_V
409 WHERE regime_id = p_regime_id
410 AND organization_id = p_organization_id
411 AND organization_type = p_organization_type
412 AND registration_type = jai_constants.regn_type_others
413 AND attribute_Type_code = p_att_type_code; --rchandan for bug#4428980
414
415 lv_primary_regime_regno JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
416
417
418 /*Cursor added by ssawant for bug 5989740 */
419 CURSOR cur_fetch_ou(cp_organization_id NUMBER)
420 IS
421 SELECT org_information3
422 FROM hr_organization_information
423 WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
424 AND organization_id = cp_organization_id;
425
426 ln_org_id NUMBER; /* added by ssawant for bug 5989740 */
427
428 /*Added for POT for CM/DM issue begin*/
429 CURSOR cur_invoice_type(cp_invoice_distribution_id NUMBER) IS
430 SELECT apa.invoice_type_lookup_code
431 FROM ap_invoices_all apa
432 , ap_invoice_distributions_all ada
433 WHERE apa.invoice_id = ada.invoice_id
434 AND ada.invoice_distribution_id = cp_invoice_distribution_id;
435
436 --Added by Qiong for bug#13456083 begin
437 ----------------------------------------------
438 CURSOR cur_payment_type(cp_invoice_payment_id NUMBER) IS
439 SELECT apa.invoice_type_lookup_code
440 FROM ap_invoices_all apa
441 , ap_invoice_payments_all ada
442 WHERE apa.invoice_id = ada.invoice_id
443 AND ada.invoice_payment_id = cp_invoice_payment_id;
444 ------------------------------------------------
445 --Added by Qiong for bug#13456083 end
446
447 lv_invoice_type ap_invoices_all.invoice_type_lookup_code%TYPE;
448 /*Added for POT for CM/DM issue end*/
449 --Added by Wenqiong for Bug 14253668 begin
450 ----------------------------------------------
451 CURSOR check_trx_type (cp_customer_trx_line_id NUMBER)IS
452 SELECT trx_types.type
453 FROM
454 jai_ar_trx_tax_lines jattl --Added by Qiong for bug14253668
455 , ra_customer_trx_lines_all trxl
456 , ra_customer_trx_all trx
457 , ra_cust_trx_types_all trx_types
458 WHERE trxl.customer_trx_id = trx.customer_trx_id
459 AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
460 AND jattl.link_to_cust_trx_line_id = trxl.customer_trx_line_id --Added by Qiong for bug14253668
461 AND jattl.customer_trx_line_id = cp_customer_trx_line_id; --Added by Qiong for bug142536
462
463 lv_ar_trx_type ra_cust_trx_types_all.type%TYPE;
464 ----------------------------------------------
465 --Added by Wenqiong for Bug 14253668 end
466 BEGIN
467
468 lv_statement := '0';
469 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'Insert_Repository_Entry', 'START');
470
471 OPEN c_regime_code(p_regime_id);
472 FETCH c_regime_code INTO lv_regime_code;
473 CLOSE c_regime_code;
474
475 /* added by ssawant for bug 5989740 */
476 OPEN cur_fetch_ou(p_organization_id);
477 FETCH cur_fetch_ou INTO ln_org_id;
478 CLOSE cur_fetch_ou;
479
480 lv_statement := '1';
481 -- REGIME Validation
482 IF lv_regime_code <> jai_constants.service_regime THEN
483 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
484 p_process_flag := jai_constants.expected_error;
485 p_process_message := 'Transactions other than SERVICE regime are not supported';
486 FND_FILE.put_line( FND_FILE.log, p_process_message);
487 fnd_file.put_line(fnd_file.log,p_process_message);
488 RETURN;
489 END IF;
490
491 -- Rounding of Service Tax that is hitting repository
492 p_amount := round(p_amount, jai_constants.service_rgm_rnd_factor);
493 p_discounted_amount := round(p_discounted_amount, jai_constants.service_rgm_rnd_factor);
494 IF p_trx_amount = 0 OR p_trx_amount IS NULL THEN
495 ln_trx_amount := NULL;
496 ELSE
497 ln_trx_amount := round(p_trx_amount, jai_constants.service_rgm_rnd_factor);
498 END IF;
499
500 lv_statement := '2';
501 IF p_source IN (jai_constants.source_settle_in, jai_constants.source_settle_out) THEN
502 ld_transaction_date := p_transaction_date;
503 lv_codepath := jai_general_pkg.plot_codepath(2.1, lv_codepath);
504
505 ELSE
506 ld_last_settlement_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(pn_regime_id => p_regime_id,pn_org_id => p_organization_id,pn_location_id => p_location_id);/* added location id by ssawant for bug 5989740 */
507 IF ld_last_settlement_date > p_transaction_date THEN /*for bug 5662296 ,org_settlement. Replaced >= with >*/
508 ld_transaction_date := ld_last_settlement_date + 1;
509 lv_codepath := jai_general_pkg.plot_codepath(2.2, lv_codepath);
510 ELSIF ld_last_settlement_date IS NULL or ld_last_settlement_date <= p_transaction_date THEN /* for bug 5662296 , org_settlement. Replaced < with <=*/
511 ld_transaction_date := p_transaction_date;
512 lv_codepath := jai_general_pkg.plot_codepath(2.3, lv_codepath);
513 END IF;
514 END IF;
515
516 lv_statement := '2.1';
517 -- ~~~~~~~~~~~~~~~~~~~~~~~ Start of Repository Entry ~~~~~~~~~~~~~~~~~~~
518 --Added by Wenqiong for Bug 14253668 begin
519 ----------------------------------------------
520 IF p_source = jai_constants.source_ar THEN
521 OPEN check_trx_type(p_source_document_id);
522 FETCH check_trx_type INTO lv_ar_trx_type;
523 CLOSE check_trx_type;
524 END IF;
525 ----------------------------------------------
526 --Added by Wenqiong for Bug 14253668 end
527
528 /*Added for POT for CM/DM issue begin*/
529 IF p_source = jai_constants.source_ap THEN
530 if p_source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL' then
531 OPEN cur_invoice_type(p_source_document_id);
532 FETCH cur_invoice_type INTO lv_invoice_type;
533 CLOSE cur_invoice_type;
534 --Added by Qiong for bug#13456083 begin
535 ----------------------------------------------
536 elsif p_source_table_name = 'AP_INVOICE_PAYMENTS_ALL' then
537 OPEN cur_payment_type(p_source_document_id);
538 FETCH cur_payment_type INTO lv_invoice_type;
539 CLOSE cur_payment_type;
540 end if;
541 ----------------------------------------------
542 --Added by Qiong for bug#13456083 end
543 END IF;
544 /*Added for POT for CM/DM issue begin*/
545 IF p_source = jai_constants.source_ap AND lv_invoice_type NOT IN ('DEBIT', 'CREDIT') --Updated for POT for CM/DM issue
546 --Added by Qiong for reverse charge bug#16001407
547 AND p_source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING')
548 OR ( nvl(lv_ar_trx_type,'#') = jai_constants.ar_invoice_type_cm)--Added by Qiong for reverse charge bug#16001407
549 THEN--Updated for POT for CM/DM issue
550 lv_statement := '3';
551 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
552 lv_register_entry_type := jai_constants.credit;
553 lv_account_name := jai_constants.recovery;
554 lv_charge_entry_type := jai_constants.debit;
555 lv_balancing_accnt_name := jai_constants.recovery_interim;
556 lv_balancing_entry_type := jai_constants.credit;
557 --Added by Qiong for bug14253668 begin
558 IF nvl(lv_ar_trx_type,'#') = jai_constants.ar_invoice_type_cm THEN
559 p_amount := abs(p_amount);
560 ln_trx_amount := abs(ln_trx_amount);
561 END IF;
562 --Added by Qiong for bug14253668 end
563
564 ELSIF p_source = jai_constants.source_ar OR lv_invoice_type IN ('DEBIT', 'CREDIT') --Updated for POT for CM/DM issue
565 AND nvl(lv_ar_trx_type,'#') <> jai_constants.ar_invoice_type_cm --Added by Qiong for reverse charge bug#16001407
566 --Added by Qiong for reverse charge bug#16001407
567 OR p_source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING') THEN
568 lv_statement := '4';
569 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
570 lv_register_entry_type := jai_constants.debit;
571 lv_account_name := jai_constants.liability;
572 lv_charge_entry_type := jai_constants.credit;
573 lv_balancing_accnt_name := jai_constants.liability_interim;
574 lv_balancing_entry_type := jai_constants.debit;
575
576 --Updated for POT for CM/DM issue begin
577 IF lv_invoice_type IN ('DEBIT', 'CREDIT') THEN
578 p_amount := abs(p_amount);
579 ln_trx_amount := abs(ln_trx_amount);--Added by qiong.liu 2011.09.28
580 END IF;
581 --Updated for POT for CM/DM issue end
582 ELSIF p_source = jai_constants.source_manual_entry THEN
583 /*No need to Set Balancing Account Name as there is no need to derive balancing account because User ENTERs it in MANUAL
584 ENTRY Form*/
585 lv_statement := '5';
586 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
587 lv_account_name := p_account_name;
588 IF lv_account_name IN (jai_constants.recovery, jai_constants.recovery_interim) THEN
589 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
590 lv_register_entry_type := jai_constants.credit;
591 lv_charge_entry_type := jai_constants.debit;
592 lv_balancing_entry_type := jai_constants.credit;
593 ELSIF lv_account_name IN (jai_constants.liability, jai_constants.liability_interim) THEN
594 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
595 lv_register_entry_type := jai_constants.debit;
596 lv_charge_entry_type := jai_constants.credit;
597 lv_balancing_entry_type := jai_constants.debit;
598 END IF;
599
600 /* Incase of Distributions and settlements, we hit only recovery account and decrease/increase
601 repository amounts as per _OUT/_IN trxns*/
602 ELSIF p_source IN (jai_constants.service_src_distribute_out, jai_constants.source_settle_out) THEN
603 lv_statement := '6';
604 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
605 lv_register_entry_type := jai_constants.debit;
606 /* following is changed as per Shekhars finding. this is because incase of distributions and settlements,
607 we should hit only recovery accounts*/
608 lv_account_name := jai_constants.recovery; -- jai_constants.liability; This is changed as per Shekhars finding
609 lv_charge_entry_type := jai_constants.credit;
610 lv_balancing_accnt_name := jai_constants.recovery;
611 lv_balancing_entry_type := jai_constants.debit;
612 -- IF (p_source = jai_constants.source_settle_out) THEN
613 -- lv_balancing_accnt_name := jai_constants.liability;
614 -- END IF; /*Added by nprashar for bug 7525691*/
615
616 ELSIF p_source IN (jai_constants.service_src_distribute_in, jai_constants.source_settle_in) THEN
617 lv_statement := '7';
618 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
619 lv_register_entry_type := jai_constants.credit;
620 lv_account_name := jai_constants.recovery;
621 lv_charge_entry_type := jai_constants.debit;
622 lv_balancing_entry_type := jai_constants.credit;
623 lv_balancing_accnt_name := jai_constants.recovery; -- jai_constants.liability;
624 /*bug 7525691*/
625 IF (p_source = jai_constants.source_settle_in) THEN
626 lv_account_name := jai_constants.liability;
627 END IF;
628 /*end bug 7525691*/
629
630 --Added by Qiong for reverse charge bug#16001407
631 ELSIF p_source = jai_constants.source_ap AND p_source_trx_type = 'INVOICE_SETTLEMENT' THEN
632 lv_account_name := jai_constants.recovery;
633 lv_statement := '7.1';
634 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
635 ln_debit := NULL;
636 ln_credit := p_amount;
637 ln_trx_debit := NULL;
638 ln_trx_credit := nvl(ln_trx_amount, p_amount);
639 lv_balancing_accnt_name := 'REVERSE_CHARGE';
640 END IF;
641 --Added by Qiong for reverse charge bug#16001407
642
643 IF lv_register_entry_type = jai_constants.debit THEN
644 lv_statement := '8';
645 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
646 ln_debit := p_amount;
647 ln_credit := NULL;
648 ln_trx_debit := nvl(ln_trx_amount, p_amount);
649 ln_trx_credit := null;
650 ELSE
651 lv_statement := '9';
652 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
653 ln_debit := NULL;
654 ln_credit := p_amount;
655 ln_trx_debit := null;
656 ln_trx_credit := nvl(ln_trx_amount, p_amount);
657 END IF;
658 --start additions for bug#13593694
659 if p_source IN (jai_constants.service_src_distribute_out)
660 then
661 lv_statement := '8.1';
662 lv_codepath := jai_general_pkg.plot_codepath(10.1, lv_codepath);
663 ln_debit := NULL;
664 ln_credit := -p_amount;
665 ln_trx_debit := NULL;
666 ln_trx_credit := -nvl(ln_trx_amount, p_amount);
667 end if;
668 --end additions for bug#13593694
669 --Add by Xiao for POT change, reg bug#12533434 on 28-May-2011, begin
670 --------------------------------------------------------------------------
671 IF p_accrual_basis = 'Y' THEN
672 IF p_source = jai_constants.source_ap THEN
673 IF p_account_name IS NOT NULL THEN --Cancellation
674 lv_account_name := p_account_name;
675 IF p_account_name = jai_constants.recovery THEN
676 lv_statement := '10';
677 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
678 ln_debit := NULL;
679 ln_credit := -p_amount;
680 ln_trx_debit := NULL;
681 ln_trx_credit := nvl(-ln_trx_amount, -p_amount);
682 lv_balancing_accnt_name := jai_constants.recovery_interim;
683 ELSIF p_account_name = jai_constants.liability THEN
684 lv_statement := '11';
685 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
686 ln_debit := p_amount;
687 ln_credit := NULL;
688 ln_trx_debit := nvl(ln_trx_amount, p_amount);
689 ln_trx_credit := NULL;
690 lv_balancing_accnt_name := jai_constants.liability_interim;
691 END IF;
692 END IF;
693 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
694 ----------------------------------------------------------------------------------------------
695 ELSIF p_source = 'AP_REVERSAL' THEN
696
697 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 begin
698 --Changed by Qiong for POT reversal begin
699 ------------------------------------------------------------------
700 lv_account_name := jai_cmn_rgm_processing_pkg.get_reversal_account_name(
701 p_regime_id => p_regime_id
702 , p_organization_type => jai_constants.service_tax_orgn_type
703 , p_organization_id => p_organization_id
704 , p_location_id => p_location_id);
705 --lv_account_name := jai_constants.recovery;
706 lv_statement := '12';
707 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
708 IF lv_account_name = jai_constants.recovery THEN
709 ln_debit := NULL;
710 ln_credit := -p_amount;
711 ln_trx_debit := NULL;
712 ln_trx_credit := -nvl(ln_trx_amount, p_amount);
713 ELSIF lv_account_name = jai_constants.liability THEN
714 ln_debit := p_amount;
715 ln_credit := NULL;
716 ln_trx_debit := nvl(ln_trx_amount, p_amount);
717 ln_trx_credit := NULL;
718 ELSE
719 ln_debit := NULL;
720 ln_credit := -p_amount;
721 ln_trx_debit := NULL;
722 ln_trx_credit := -nvl(ln_trx_amount, p_amount);
723 END IF;
724 -------------------------------------------------------------------
725 --Changed by Qiong for POT reversal End
726 -- code ported from BL12.1.3 by zhiwei.xin on 23-JAN-2013 end.
727
728 lv_balancing_accnt_name := jai_constants.recovery_interim;
729 ELSIF p_source = 'AP_CLAIM' THEN
730 lv_account_name := jai_constants.recovery;
731 lv_statement := '12';
732 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
733 ln_debit := NULL;
734 ln_credit := p_amount;
735 ln_trx_debit := NULL;
736 ln_trx_credit := nvl(ln_trx_amount, p_amount);
737 lv_balancing_accnt_name := jai_constants.recovery_interim;
738 ELSIF p_source = 'AR_REVERSAL' THEN
739 lv_account_name := jai_constants.liability;
740 lv_statement := '12';
741 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
742 ln_debit := -p_amount;
743 ln_credit := NULL;
744 ln_trx_debit := -nvl(ln_trx_amount, p_amount);
745 ln_trx_credit := NULL;
746 lv_balancing_accnt_name := jai_constants.reversal;--should be jai_constants.service_reversed
747 ELSIF p_source = 'AR_CLAIM' THEN
748 lv_account_name := jai_constants.liability;
749 lv_statement := '12';
750 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
751 ln_debit := p_amount;
752 ln_credit := NULL;
753 ln_trx_debit := nvl(ln_trx_amount, p_amount);
754 ln_trx_credit := NULL;
755 lv_balancing_accnt_name := jai_constants.reversal;--should be jai_constants.service_reversed
756 ----------------------------------------------------------------------------------------------
757 --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
758 END IF;
759 END IF;
760 --------------------------------------------------------------------------
761 --Add by Xiao for POT change, reg bug#12533434 on 28-May-2011, end
762 lv_statement := '13';
763 IF p_charge_account_id IS NULL THEN
764 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
765 ln_charge_account_id := get_account(
766 p_regime_id => p_regime_id,
767 p_organization_type => p_organization_type,
768 p_organization_id => p_organization_id,
769 p_location_id => p_location_id,
770 p_tax_type => p_tax_type,
771 p_account_name => lv_account_name
772 );
773 ELSE
774 lv_statement := '14';
775 ln_charge_account_id := p_charge_account_id;
776 END IF;
777
778 lv_statement := '9.1';
779 lv_balancing_orgn_type := p_balancing_orgn_type;
780 ln_balancing_orgn_id := p_balancing_orgn_id;
781 ln_balancing_location_id := p_balancing_location_id;
782 lv_balancing_tax_type := p_balancing_tax_type;
783 lv_balancing_accnt_name := nvl(p_balancing_accnt_name, lv_balancing_accnt_name);
784
785 lv_statement := '12';
786 IF ln_balancing_orgn_id IS NULL THEN
787 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
788 lv_balancing_orgn_type := p_organization_type;
789 ln_balancing_orgn_id := p_organization_id;
790 ln_balancing_location_id := p_location_id;
791 lv_balancing_tax_type := p_tax_type;
792 END IF;
793
794 lv_statement := '17';
795 IF p_balancing_account_id IS NULL THEN -- AND lv_balancing_accnt_name IS NOT NULL THEN
796 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
797 ln_balancing_account_id := get_account(
798 p_regime_id => p_regime_id,
799 p_organization_type => lv_balancing_orgn_type,
800 p_organization_id => ln_balancing_orgn_id,
801 p_location_id => ln_balancing_location_id,
802 p_tax_type => lv_balancing_tax_type,
803 p_account_name => lv_balancing_accnt_name
804 );
805 ELSE
806 ln_balancing_account_id := p_balancing_account_id;
807 END IF;
808
809 lv_statement := '10';
810
811 /*
812 Following cursor added by ssumaith to get the primary registration number - bug# 4204880
813 Added the column regime_primary_regno in the insert column list of the table jai_rgm_trx_records table.
814 */
815 OPEN c_primary_regno('PRIMARY'); --rchandan for bug#4428980
816 FETCH c_primary_regno into lv_primary_regime_regno;
817 CLOSE c_primary_regno;
818
819 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
820 INSERT INTO jai_rgm_trx_records(
821 repository_id, regime_code, tax_type, source,
822 source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
823 settled_amount, settled_flag, settlement_id, organization_type,
824 organization_id, location_id, account_name, charge_account_id, balancing_account_id,
825 reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
826 trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
827 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
828 trx_reference_context, trx_reference1, trx_reference2, trx_reference3, trx_reference4, trx_reference5
829 , inv_organization_id, regime_primary_regno ,service_type_code /* added by ssawant for bug 5879769 */
830 , invoice_no --Add by Qiong for Advanced Receipts
831 ) VALUES (
832 jai_rgm_trx_records_s.nextval, lv_regime_code, p_tax_type, p_source,
833 p_source_document_id, p_source_table_name, ld_transaction_date, ln_debit, ln_credit,
834 null, null, p_settlement_id, p_organization_type,
835 p_organization_id, p_location_id, lv_account_name,--Modified by Xiao from p_account_name for POT change, reg bug#12533434
836 ln_charge_account_id, ln_balancing_account_id,
837 p_reference_id, p_source_trx_type, p_tax_rate, p_assessable_value, p_batch_id,
838 p_currency_code, p_curr_conv_date, p_curr_conv_rate, ln_trx_credit, ln_trx_debit,
839 sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
840 p_trx_reference_context, p_trx_reference1, p_trx_reference2, p_trx_reference3, p_trx_reference4, p_trx_reference5
841 , p_inv_organization_id , lv_primary_regime_regno ,p_service_type_code /* added by ssawant for bug 5879769 */
842 ,p_invoice_no --Added by Qiong for Advanced receipts
843 ) RETURNING repository_id INTO p_repository_id;
844
845 -- ~~~~~~~~~~~~~~~~~~~~~~~ Accounting of Recovered/Liable Service Tax ~~~~~~~~~~~~~~~~~~~~~
846
847 lv_statement := '11';
848 IF p_accntg_required_flag = jai_constants.yes THEN
849
850 lv_statement := '15';
851 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
852 IF ln_charge_account_id IS NULL THEN
853 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
854 p_process_flag := jai_constants.expected_error;
855 p_process_message := 'Charge Account('||lv_account_name||') not defined for tax type '||p_tax_type;
856 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
857 GOTO end_of_repository_entry;
858 END IF;
859
860 lv_statement := '16';
861 IF g_debug='Y' THEN
862 fnd_file.put_line(fnd_file.log,'pkg2. rgm_id:'||p_regime_id||',OrgType:'||lv_balancing_orgn_type
863 ||',Oid:'||ln_balancing_orgn_id||',locid:'||ln_balancing_location_id
864 ||',txty:'||lv_balancing_tax_type||',actName:'||lv_balancing_accnt_name
865 );
866 END IF;
867
868 lv_statement := '18';
869 IF ln_balancing_account_id IS NULL THEN
870 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
871 p_process_flag := jai_constants.expected_error;
872 p_process_message := 'Balancing Account('||lv_balancing_accnt_name||') not defined for tax type '||lv_balancing_tax_type;
873 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
874 GOTO end_of_repository_entry;
875 END IF;
876
877 ln_reference_id := p_reference_id;
878 IF p_source IN ( jai_constants.source_ap, jai_constants.source_ar) THEN
879 lv_reference_name := jai_constants.rgm_trx_refs;
880 END IF;
881
882 lv_statement := '19';
883 -- INITIAL_ENTRY
884 IF lv_charge_entry_type = jai_constants.debit THEN
885 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
886 ln_debit := p_amount;
887 ln_credit := NULL;
888 ln_trx_debit := nvl(ln_trx_amount, p_amount);
889 ln_trx_credit := null;
890 ELSE
891 ln_debit := NULL;
892 ln_credit := p_amount;
893 ln_trx_debit := null;
894 ln_trx_credit := nvl(ln_trx_amount, p_amount);
895 END IF;
896
897 lv_statement := '20';
898 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
899 -- make a call to post_accounting procedure
900 post_accounting(
901 p_regime_code => lv_regime_code,
902 p_tax_type => p_tax_type,
903 p_organization_type => p_organization_type,
904 p_organization_id => p_organization_id,
905 p_source => p_source,
906 p_source_trx_type => p_source_trx_type,
907 p_source_table_name => p_source_table_name,
908 p_source_document_id => p_source_document_id,
909 p_code_combination_id => ln_charge_account_id,
910 p_entered_cr => ln_trx_credit,
911 p_entered_dr => ln_trx_debit,
912 p_accounted_cr => ln_credit,
913 p_accounted_dr => ln_debit,
914 p_accounting_date => p_accounting_date,
915 p_transaction_date => ld_transaction_date,
916 p_calling_object => p_called_from,
917 p_repository_name => jai_constants.repository_name,
918 p_repository_id => p_repository_id,
919 p_reference_name => lv_reference_name,
920 p_reference_id => ln_reference_id,
921 p_currency_code => p_currency_code,
922 p_curr_conv_date => p_curr_conv_date,
923 p_curr_conv_type => p_curr_conv_type,
924 p_curr_conv_rate => p_curr_conv_rate
925 );
926
927 /* START of DISCOUNT ACCOUNTING */
928 IF nvl(p_discounted_amount, 0) <> 0 THEN
929 -- Discount related code needs to be added here
930 lv_statement := '20.1';
931 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
932
933 IF p_source = jai_constants.source_ar THEN
934
935 lv_codepath := jai_general_pkg.plot_codepath(21.1, lv_codepath);
936 jai_ar_rgm_processing_pkg.get_ar_tax_disc_accnt (
937 p_receivable_application_id => p_source_document_id,
938 p_org_id => ln_org_id,/* added by ssawant for bug 5879769 */
939 p_total_disc_amount => p_discounted_amount, /* added by ssumaith - for bug# 4193633*/
940 p_tax_ediscounted => ln_earned_discount,
941 p_earned_disc_ccid => ln_earned_disc_accnt,
942 p_tax_uediscounted => ln_unearned_discount,
943 p_unearned_disc_ccid => ln_unearned_disc_accnt,
944 p_process_flag => p_process_flag,
945 p_process_message => p_process_message
946 );
947
948 IF p_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
949 lv_codepath := jai_general_pkg.plot_codepath(21.2, lv_codepath);
950 -- some problem in the above call
951 RETURN;
952 ELSIF nvl(ln_earned_discount, 0) + nvl(ln_unearned_discount, 0) <> NVL(p_discounted_amount,0) THEN
953 lv_codepath := jai_general_pkg.plot_codepath(21.3, lv_codepath);
954 p_process_flag := jai_constants.expected_error;
955 p_process_message := 'There is a discrepency in earned + unearned = discounted';
956 RETURN;
957 END IF;
958
959 --- following will be used for first accounting entry incase of AR Receipt Application
960 IF nvl(ln_earned_discount,0) <> 0 THEN
961 ln_discount_ccid := ln_earned_disc_accnt;
962 IF lv_charge_entry_type = jai_constants.debit THEN
963 lv_codepath := jai_general_pkg.plot_codepath(21.4, lv_codepath);
964 ln_disc_credit := null;
965 ln_disc_debit := ln_earned_discount;
966 ELSE
967 lv_codepath := jai_general_pkg.plot_codepath(21.5, lv_codepath);
968 ln_disc_credit := ln_earned_discount;
969 ln_disc_debit := null;
970 END IF;
971
972 ELSE
973 ln_disc_credit := null;
974 ln_disc_debit := null;
975 END IF;
976
977 /* following else will be executed for AP Transactions only */
978 ELSE
979
980 ln_discount_ccid := get_account(
981 p_regime_id => null,
982 p_organization_type => p_organization_type,
983 p_organization_id => ln_org_id , -- Date 07/06/2007 by sacsethi for bug 6109941 - changed organization_id to org_id ( ou_level )
984 p_location_id => null,
985 p_tax_type => null,
986 p_account_name => jai_cmn_rgm_recording_pkg.ap_discount_accnt
987 );
988
989 IF ln_discount_ccid IS NULL THEN
990 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
991 p_process_flag := jai_constants.expected_error;
992 p_process_message := 'Discount Account is not defined in '||p_source;
993 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
994 FND_FILE.put_line( FND_FILE.log, ln_org_id);
995 GOTO end_of_repository_entry;
996 END IF;
997
998 IF lv_charge_entry_type = jai_constants.debit THEN
999 ln_disc_credit := null;
1000 ln_disc_debit := p_discounted_amount;
1001 ELSE
1002 ln_disc_debit := null;
1003 ln_disc_credit := p_discounted_amount;
1004 END IF;
1005
1006 END IF;
1007
1008 IF ln_disc_debit IS NOT NULL OR ln_disc_credit IS NOT NULL THEN
1009 lv_codepath := jai_general_pkg.plot_codepath(21.6, lv_codepath);
1010 -- make a call to post_accounting procedure
1011 post_accounting(
1012 p_regime_code => lv_regime_code,
1013 p_tax_type => p_tax_type,
1014 p_organization_type => p_organization_type,
1015 p_organization_id => p_organization_id,
1016 p_source => p_source,
1017 p_source_trx_type => p_source_trx_type,
1018 p_source_table_name => p_source_table_name,
1019 p_source_document_id => p_source_document_id,
1020 p_code_combination_id => ln_discount_ccid,
1021 p_entered_cr => ln_disc_credit,
1022 p_entered_dr => ln_disc_debit,
1023 -- Added nvl(p_curr_conv_rate, 1) for Bug 7522584
1024 p_accounted_cr => ln_disc_credit * nvl(p_curr_conv_rate, 1),
1025 p_accounted_dr => ln_disc_debit * nvl(p_curr_conv_rate, 1),
1026 p_accounting_date => p_accounting_date,
1027 p_transaction_date => ld_transaction_date,
1028 p_calling_object => p_called_from,
1029 p_repository_name => jai_constants.repository_name,
1030 p_repository_id => p_repository_id,
1031 p_reference_name => lv_reference_name,
1032 p_reference_id => ln_reference_id,
1033 p_currency_code => p_currency_code,
1034 p_curr_conv_date => p_curr_conv_date,
1035 p_curr_conv_type => p_curr_conv_type,
1036 p_curr_conv_rate => p_curr_conv_rate
1037 );
1038
1039 END IF;
1040
1041 -- following entry will happen only in case of AR Transactions
1042 IF nvl(ln_unearned_discount,0) <> 0 THEN
1043 lv_codepath := jai_general_pkg.plot_codepath(21.7, lv_codepath);
1044 ln_discount_ccid := ln_unearned_disc_accnt;
1045 IF lv_charge_entry_type = jai_constants.debit THEN
1046 ln_disc_credit := null;
1047 ln_disc_debit := ln_unearned_discount;
1048 ELSE
1049 ln_disc_credit := ln_unearned_discount;
1050 ln_disc_debit := null;
1051 END IF;
1052
1053 post_accounting(
1054 p_regime_code => lv_regime_code,
1055 p_tax_type => p_tax_type,
1056 p_organization_type => p_organization_type,
1057 p_organization_id => p_organization_id,
1058 p_source => p_source,
1059 p_source_trx_type => p_source_trx_type,
1060 p_source_table_name => p_source_table_name,
1061 p_source_document_id => p_source_document_id,
1062 p_code_combination_id => ln_discount_ccid,
1063 p_entered_cr => ln_disc_credit,
1064 p_entered_dr => ln_disc_debit,
1065 -- Added nvl(p_curr_conv_rate, 1) for Bug 7522584
1066 p_accounted_cr => ln_disc_credit * nvl(p_curr_conv_rate, 1),
1067 p_accounted_dr => ln_disc_debit * nvl(p_curr_conv_rate, 1),
1068 p_accounting_date => p_accounting_date,
1069 p_transaction_date => ld_transaction_date,
1070 p_calling_object => p_called_from,
1071 p_repository_name => jai_constants.repository_name,
1072 p_repository_id => p_repository_id,
1073 p_reference_name => lv_reference_name,
1074 p_reference_id => ln_reference_id,
1075 p_currency_code => p_currency_code,
1076 p_curr_conv_date => p_curr_conv_date,
1077 p_curr_conv_type => p_curr_conv_type,
1078 p_curr_conv_rate => p_curr_conv_rate
1079 );
1080
1081 END IF;
1082
1083 lv_codepath := jai_general_pkg.plot_codepath(21.8, lv_codepath);
1084 END IF;
1085 /* END of DISCOUNT ACCOUNTING */
1086
1087
1088 lv_statement := '21';
1089 /*bug 7525691 - Service Tax Settlement creates duplicate entries.
1090 Balancing entry should not be passed in following cases:
1091 1. Settlement Transactions
1092 2. Distribution Transactions with distribution type Service->Service
1093 Modified the condition earlier added for distribution to consider distribution type
1094 also - observation from 11i bug 8315191 (R12 bug 8329634 gets fixed due to this)*/
1095 IF (p_source NOT IN (jai_constants.source_settle_in, jai_constants.source_settle_out))
1096 AND (p_source NOT IN (jai_constants.service_src_distribute_out, jai_constants.service_src_distribute_in)
1097 OR nvl(p_distribution_type,'X')<>'S-S')THEN
1098 -- BALANCING_ENTRY
1099 IF lv_balancing_entry_type = jai_constants.debit THEN
1100 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
1101 ln_debit := p_amount + (nvl(p_discounted_amount,0) * nvl(p_curr_conv_rate, 1));
1102 -- Added p_curr_conv_rate for Bug 7522584
1103 ln_credit := NULL;
1104 ln_trx_debit := nvl(ln_trx_amount, p_amount)+ nvl(p_discounted_amount,0);
1105 ln_trx_credit := null;
1106 ELSE
1107 ln_debit := NULL;
1108 ln_credit := p_amount + (nvl(p_discounted_amount,0) * nvl(p_curr_conv_rate, 1));
1109 -- Added p_curr_conv_rate for Bug 7522584
1110 ln_trx_debit := null;
1111 ln_trx_credit := nvl(ln_trx_amount, p_amount)+ nvl(p_discounted_amount,0);
1112 END IF;
1113
1114 lv_statement := '22';
1115 -- make a call to post_accounting procedure
1116 post_accounting(
1117 p_regime_code => lv_regime_code,
1118 p_tax_type => p_tax_type,
1119 p_organization_type => lv_balancing_orgn_type,
1120 p_organization_id => ln_balancing_orgn_id,
1121 p_source => p_source,
1122 p_source_trx_type => p_source_trx_type,
1123 p_source_table_name => p_source_table_name,
1124 p_source_document_id => p_source_document_id,
1125 p_code_combination_id => ln_balancing_account_id,
1126 p_entered_cr => ln_trx_credit, -- TRANSACTION_CURR
1127 p_entered_dr => ln_trx_debit,
1128 p_accounted_cr => ln_credit, -- FUNC_CURR
1129 p_accounted_dr => ln_debit,
1130 p_accounting_date => p_accounting_date,
1131 p_transaction_date => ld_transaction_date,
1132 p_calling_object => p_called_from,
1133 p_repository_name => jai_constants.repository_name,
1134 p_repository_id => p_repository_id,
1135 p_reference_name => lv_reference_name,
1136 p_reference_id => ln_reference_id,
1137 p_currency_code => p_currency_code,
1138 p_curr_conv_date => p_curr_conv_date,
1139 p_curr_conv_type => p_curr_conv_type,
1140 p_curr_conv_rate => p_curr_conv_rate
1141 );
1142
1143 END IF; --bug 7692977
1144 END IF;
1145 lv_statement := '23';
1146 p_process_flag := jai_constants.successful;
1147 p_process_message := 'Successful';
1148
1149 lv_statement := '24';
1150 <<end_of_repository_entry>>
1151 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath, 'Insert_Repository_entry', 'END');
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 p_process_flag := jai_constants.unexpected_error;
1156 p_process_message := 'Repository Error(Stmt:'||lv_statement||') Occured:'||SQLERRM;
1157 lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
1158 Fnd_file.put_line( fnd_file.log, 'Error in Insert_Repository_entry. Codepath:'||lv_codepath);
1159
1160 END insert_repository_entry;
1161
1162 --Add by Xiao for bug#12598609, begin
1163 PROCEDURE get_inv_num_std ( pv_invoice_num IN OUT NOCOPY VARCHAR2
1164 , pv_invoice_type IN OUT NOCOPY VARCHAR2
1165 , pn_invoice_distribution_id IN NUMBER) IS
1166 PRAGMA AUTONOMOUS_TRANSACTION;
1167 CURSOR get_inv_num_std IS
1168 SELECT aia.invoice_num
1169 , aia.invoice_type_lookup_code
1170 FROM ap_invoices_all aia
1171 , ap_invoice_distributions_all aida
1172 WHERE aia.invoice_id = aida.invoice_id
1173 AND aida.invoice_distribution_id = pn_invoice_distribution_id;
1174 BEGIN
1175
1176 OPEN get_inv_num_std ;
1177 FETCH get_inv_num_std INTO pv_invoice_num, pv_invoice_type;
1178 CLOSE get_inv_num_std;
1179
1180 COMMIT;
1181 END get_inv_num_std;
1182 --Add by Xiao for bug#12598609, end
1183
1184 /* ~~~~~~~~~~~~~~~ Start of Accounting Entry Procedure ~~~~~~~~~~~~~~~~~ */
1185 PROCEDURE post_accounting(
1186 p_regime_code IN VARCHAR2,
1187 p_tax_type IN VARCHAR2,
1188 p_organization_type IN VARCHAR2,
1189 p_organization_id IN NUMBER,
1190 p_source IN VARCHAR2,
1191 p_source_trx_type IN VARCHAR2,
1192 p_source_table_name IN VARCHAR2,
1193 p_source_document_id IN NUMBER,
1194 p_code_combination_id IN NUMBER,
1195 -- Transaction Currency Amount
1196 p_entered_cr IN NUMBER,
1197 p_entered_dr IN NUMBER,
1198 -- Functional Currency Amount
1199 p_accounted_cr IN NUMBER,
1200 p_accounted_dr IN NUMBER,
1201 p_accounting_date IN DATE,
1202 p_transaction_date IN DATE,
1203 p_calling_object IN VARCHAR2,
1204 p_repository_name IN VARCHAR2 DEFAULT NULL,
1205 p_repository_id IN NUMBER DEFAULT NULL,
1206 p_reference_name IN VARCHAR2 DEFAULT NULL,
1207 p_reference_id IN NUMBER DEFAULT NULL,
1208 p_currency_code IN VARCHAR2 DEFAULT NULL,
1209 p_curr_conv_date IN DATE DEFAULT NULL,
1210 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
1211 p_curr_conv_rate IN NUMBER DEFAULT NULL,
1212 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1213 p_attribute2 IN VARCHAR2 DEFAULT NULL
1214 ) IS
1215
1216 /* Added by Ramananda for bug#4407165 */
1217 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.post_accounting';
1218
1219 lv_reference10 GL_INTERFACE.reference10%type;
1220 lv_reference23 GL_INTERFACE.reference23%type;
1221 lv_reference24 GL_INTERFACE.reference24%type;
1222 lv_reference25 GL_INTERFACE.reference25%type;
1223 lv_reference26 GL_INTERFACE.reference26%type;
1224
1225 ld_accounting_date DATE;
1226 lv_message VARCHAR2(100);
1227
1228 lv_period_name GL_PERIODS.period_name%TYPE;
1229 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
1230 ln_currency_precision FND_CURRENCIES.precision%TYPE;
1231
1232 ln_entered_dr NUMBER;
1233 ln_entered_cr NUMBER;
1234 ln_accounted_dr NUMBER;
1235 ln_accounted_cr NUMBER;
1236
1237 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1238 lv_reference_name VARCHAR2(30);
1239 ln_reference_id NUMBER(15);
1240 lv_gl_je_category varchar2(30); --File.Sql.35 Cbabu jai_constants.je_category_rg_entry%type;
1241 lv_status gl_interface.status%TYPE ; --rchandan for bug#4428980
1242 lv_source_name VARCHAR2(30); -- modified by csahoo for bug#6155839
1243 /*Begin-Added the following by kunkumar for forward porting 5051541 to R12*/
1244
1245
1246 lv_organization_code mtl_parameters.organization_code%TYPE;
1247
1248 CURSOR c_organization_code(cp_organization_id IN NUMBER)
1249 IS
1250 SELECT organization_code
1251 FROM mtl_parameters
1252 WHERE organization_id = cp_organization_id;
1253
1254 -- added, ssumaith for Bug 6664855
1255
1256 Cursor c_get_source_info(cp_transfer_id NUMBER)
1257 IS
1258 select party_id
1259 from jai_rgm_dis_src_hdrs
1260 where transfer_id = cp_transfer_id ;
1261
1262
1263 Cursor c_get_dest_info(cp_transfer_id NUMBER)
1264 IS
1265 select destination_party_id
1266 from jai_rgm_dis_des_hdrs
1267 where transfer_id = cp_transfer_id ;
1268
1269 Cursor c_get_source(cp_repository_id NUMBER)
1270 IS
1271 select source
1272 from jai_rgm_trx_records
1273 where repository_id = cp_repository_id ;
1274
1275 --Add by Xiao for bug#12598609, begin
1276
1277 CURSOR get_inv_num_pay IS
1278 SELECT aia.invoice_num
1279 , aia.invoice_type_lookup_code
1280 FROM ap_invoices_all aia
1281 , ap_invoice_payments_all aipa
1282 WHERE aia.invoice_id = aipa.invoice_id
1283 AND aipa.invoice_payment_id = p_source_document_id;
1284
1285 lv_invoice_num ap_invoices_all.invoice_num%TYPE;
1286 lv_invoice_type ap_invoices_all.invoice_type_lookup_code%TYPE;
1287 lv_invoice_num_str VARCHAR2(300);
1288
1289 --Add by Xiao for bug#12598609, end
1290
1291 --Added by Wenqiong begin
1292 CURSOR get_receipt_num (c_cash_receipt_id NUMBER)IS
1293 SELECT receipt_number
1294 FROM AR_CASH_RECEIPTS_ALL
1295 WHERE cash_receipt_id = c_cash_receipt_id;
1296
1297 CURSOR get_ar_receipt_id IS
1298 SELECT cash_receipt_id, APPLIED_CUSTOMER_TRX_ID
1299 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1300 WHERE RECEIVABLE_APPLICATION_ID = p_source_document_id;
1301
1302 CURSOR get_ar_trx_num(c_customer_trx_id NUMBER) IS
1303 SELECT trx_number
1304 FROM RA_customer_trx_all
1305 WHERE customer_trx_id = c_customer_trx_id;
1306 ln_cash_receipt_id NUMBER;
1307 ln_customer_trx_id NUMBER;
1308 --Added by Wenqiong end
1309
1310 lv_source jai_rgm_trx_records.source%TYPE ;
1311 lv_src_party_id jai_rgm_dis_src_hdrs.party_id%TYPE ;
1312 lv_reference1 gl_interface.reference1%TYPE ;
1313 ln_loop_cnt NUMBER;
1314
1315 -- end, ssumaith for Bug 6664855
1316
1317 BEGIN
1318 --Bug 5051541 bduvarag
1319 jai_cmn_utils_pkg.print_log('6395039.log', 'Start of post_accounting');
1320 -- added, ssumaith for Bug 6664855
1321 lv_reference1 := null ;
1322
1323 OPEN c_get_source(p_repository_id) ;
1324 FETCH c_get_source INTO lv_source ;
1325 CLOSE c_get_source ;
1326
1327 /* Reference column should be populated with same value for a set of Journals that are passed as part
1328 of a transaction, else Journal Import will fail with EUXX error.
1329 Prior to this fix, incase of Distribution of duty from one orgn. to other, reference1 is getting populated
1330 with different values for different Journal. Hence the following logic of the IF condition is used to derive the
1331 value for reference1 for all the Journals that are part of the Distribution (Service tax or any duty distribution
1332 */
1333
1334 IF lv_source IN (jai_constants.service_src_distribute_in, jai_constants.service_src_distribute_out,
1335 'DISTRIBUTE_IN', 'DISTRIBUTE_OUT')
1336 THEN
1337 lv_reference1 := '' ;
1338
1339 OPEN c_get_source_info(p_source_document_id) ;
1340 FETCH c_get_source_info INTO lv_src_party_id ;
1341 CLOSE c_get_source_info ;
1342
1343 lv_reference1 := to_char(lv_src_party_id)||'->';
1344 ln_loop_cnt := 1;
1345 FOR rec IN c_get_dest_info(p_source_document_id)
1346 LOOP
1347 if ln_loop_cnt > 1 then
1348 lv_reference1 := lv_reference1 || ',';
1349 end if;
1350 lv_reference1 := lv_reference1 || to_char(rec.destination_party_id);
1351 ln_loop_cnt := ln_loop_cnt + 1;
1352 END LOOP ;
1353 --added the elsif condition for bug#13788285
1354 /*in case of settlement entries i.e SETTLE_IN,SETTLE_OUT transactions,reference1 shall be
1355 populated with the settlement id instead of the organization id to avoid EU02 error*/
1356 ELSIF p_source_table_name='JAI_RGM_SETTLEMENTS' THEN
1357
1358 lv_reference1 := p_source_document_id;
1359
1360 --end additions for bug#13788285
1361 ELSE
1362 OPEN c_organization_code(p_organization_id);
1363 FETCH c_organization_code INTO lv_organization_code;
1364 CLOSE c_organization_code;
1365 lv_reference1 := lv_organization_code ;
1366 END IF ;
1367
1368 -- ended, ssumaith for Bug 6664855
1369
1370 --Add by Xiao for bug#12598609, begin
1371 IF p_source_table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL' THEN
1372 get_inv_num_std(lv_invoice_num, lv_invoice_type, p_source_document_id);
1373 ELSIF p_source_table_name = 'AP_INVOICE_PAYMENTS_ALL' THEN
1374 OPEN get_inv_num_pay;
1375 FETCH get_inv_num_pay INTO lv_invoice_num, lv_invoice_type;
1376 CLOSE get_inv_num_pay;
1377 --Added by Wenqiong
1378
1379 ELSIF p_source_table_name = 'AR_CASH_RECEIPTS_ALL' THEN
1380 /*OPEN get_receipt_num(p_source_document_id);
1381 FETCH get_receipt_num INTO lv_invoice_num;
1382 CLOSE get_receipt_num;*/
1383 lv_invoice_num := p_attribute1;
1384 lv_invoice_num_str := 'Receipt Number: '||lv_invoice_num;
1385 ELSIF p_source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL' THEN
1386 /* OPEN get_ar_receipt_id;
1387 FETCH get_ar_receipt_id INTO ln_cash_receipt_id,ln_customer_trx_id;
1388 CLOSE get_ar_receipt_id;*/
1389
1390 OPEN get_receipt_num(to_number(p_attribute1));
1391 FETCH get_receipt_num INTO lv_invoice_num;
1392 CLOSE get_receipt_num;
1393 lv_invoice_num_str := 'Receipt Number: '||lv_invoice_num;
1394 OPEN get_ar_trx_num(to_number(p_attribute2));
1395 FETCH get_ar_trx_num INTO lv_invoice_num;
1396 CLOSE get_ar_trx_num;
1397 lv_invoice_num_str := lv_invoice_num_str ||', AR Invoice Number: '|| lv_invoice_num;
1398 --Added by Wenqiong
1399 END IF;
1400 IF lv_invoice_type = 'STANDARD' THEN
1401 lv_invoice_num_str := 'Invoice Number: ' || lv_invoice_num;
1402 ELSIF lv_invoice_type = 'DEBIT' THEN
1403 lv_invoice_num_str := 'Debit Memo Number: ' || lv_invoice_num;
1404 ELSIF lv_invoice_type = 'CREDIT' THEN
1405 lv_invoice_num_str := 'Credit Memo Number: ' || lv_invoice_num;
1406 END IF;
1407 --Add by Xiao for bug#12598609, end
1408
1409 /* following condition introduced for VAT Impl. Vijay Shankar for Bug#4250236(4245089) */
1410 IF p_regime_code = jai_constants.service_regime THEN
1411 lv_reference10 := 'Service Tax Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~')
1412 || '. ' ||lv_invoice_num_str; --Add by Xiao for bug#12598609
1413 lv_source_name := jai_constants.service_tax_source; -- added by csahoo for bug#6155839
1414 ELSIF p_regime_code = jai_constants.vat_regime THEN
1415 lv_reference10 := 'VAT Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~')
1416 || '. ' ||lv_invoice_num_str; --Add by Xiao for bug#12598609
1417 lv_source_name := jai_constants.vat_source; -- modified by csahoo for bug#6155839
1418 jai_cmn_utils_pkg.print_log('6395039.log', lv_reference10);
1419
1420 /*Added code for TCS for bug 12641455 by mmurtuza*/
1421 ELSIF p_regime_code = jai_constants.tcs_regime THEN
1422 /*For bug 12641455. Changed Service Tax to TCS in lv_reference10*/
1423 lv_reference10 := 'TCS Accounting for ' || p_source || '. Transaction Type:' || nvl(p_source_trx_type, '~~') || '. ' || lv_invoice_num_str;
1424 lv_source_name := jai_constants.tcs_source;
1425
1426 END IF;
1427
1428 ld_accounting_date := nvl( trunc(p_accounting_date), trunc(sysdate) );
1429
1430 IF p_code_combination_id IS NULL THEN
1431 lv_message := 'Account not given';
1432 RAISE_APPLICATION_ERROR( -20011, lv_message);
1433 END IF;
1434
1435 ln_currency_precision := jai_general_pkg.get_currency_precision(null); -- CURRENCY is INR
1436
1437 -- Use of Currency Precision to round off the values when posting to GL is mandatory thing
1438 ln_entered_dr := round(p_entered_dr, ln_currency_precision);
1439 ln_entered_cr := round(p_entered_cr, ln_currency_precision);
1440 ln_accounted_dr := round(p_accounted_dr, ln_currency_precision);
1441 ln_accounted_cr := round(p_accounted_cr, ln_currency_precision);
1442
1443 IF ( nvl(ln_entered_dr, 0) = 0 AND nvl(ln_entered_cr,0) = 0
1444 OR nvl(ln_accounted_dr, 0) = 0 AND nvl(ln_accounted_cr,0) = 0 )
1445 THEN
1446
1447 -- Date 07-jun-2007 by sacsethi for bug 6109941
1448 -- Previously we were generating raise application error which is changed
1449 -- to information level ....
1450
1451 FND_FILE.put_line( FND_FILE.log, 'Accounting not done as Both Credit and Debit are Zero ');
1452 RETURN ;
1453
1454 END IF;
1455 jai_cmn_utils_pkg.print_log('6395039.log', 'before call to get_period_name');
1456
1457 get_period_name(
1458 p_organization_type => p_organization_type,
1459 p_organization_id => p_organization_id,
1460 p_accounting_date => ld_accounting_date,
1461 p_period_name => lv_period_name,
1462 p_sob_id => ln_sob_id
1463 );
1464
1465 jai_cmn_utils_pkg.print_log('6395039.log', 'after call to get_period_name');
1466
1467 /* following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1468 lv_gl_je_category := jai_constants.je_category_rg_entry;
1469 lv_status := 'NEW';--rchandan for bug#4428980
1470 jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot gl_interface');
1471
1472 INSERT INTO gl_interface (
1473 status, set_of_books_id, user_je_source_name, user_je_category_name,
1474 accounting_date, currency_code, date_created, created_by,
1475 actual_flag, entered_cr, entered_dr, accounted_cr, accounted_dr, transaction_date,
1476 code_combination_id, currency_conversion_date, user_currency_conversion_type, currency_conversion_rate,
1477 reference10, reference22, reference23, reference1,
1478 reference24, reference25, reference26, reference27
1479 ) VALUES (
1480 lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
1481 ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
1482 'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
1483 p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
1484 lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
1485 -- commented lv_organization_code and passed refererence1 ssumaith bug#6664855
1486 --Bug 5051541 kunkumar
1487 p_source_table_name, p_source_document_id, p_repository_name, p_organization_id
1488 );
1489
1490 jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot gl_interface');
1491 IF p_reference_id IS NOT NULL OR p_reference_name IS NOT NULL THEN
1492 lv_reference_name := p_reference_name;
1493 ln_reference_id := p_reference_id;
1494 ELSE
1495 lv_reference_name := p_repository_name;
1496 ln_reference_id := p_repository_id;
1497 END IF;
1498 jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot JAI_CMN_JOURNAL_ENTRIES');
1499
1500 INSERT INTO JAI_CMN_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
1501 regime_code, organization_id, set_of_books_id, tax_type, period_name,
1502 code_combination_id, accounted_dr, accounted_cr, transaction_date,
1503 source, source_table_name, source_trx_id, reference_name, reference_id, repository_id,/* added by ssawant for bug 5879769 */
1504 currency_code, curr_conv_rate, creation_date, created_by, last_update_date, last_updated_by, last_update_login
1505 ) VALUES ( JAI_CMN_JOURNAL_ENTRIES_S.nextval,
1506 p_regime_code, p_organization_id, ln_sob_id, p_tax_type, lv_period_name,
1507 p_code_combination_id, ln_accounted_dr, ln_accounted_cr, p_transaction_date,
1508 p_source, p_source_table_name, p_source_document_id, p_source_trx_type /* p_reference_name- Bug 13430127 */, p_reference_id,p_repository_id,/* added by ssawant for bug 5879769 */
1509 p_currency_code, p_curr_conv_rate, sysdate, FND_GLOBAL.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
1510 );
1511 jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot JAI_CMN_JOURNAL_ENTRIES');
1512 /* Added by Ramananda for bug#4407165 */
1513
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1517 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1518 jai_cmn_utils_pkg.print_log('6395039.log', 'exception occured'||sqlerrm);
1519 app_exception.raise_exception;
1520
1521 END post_accounting;
1522
1523 PROCEDURE insert_reference(
1524 p_reference_id OUT NOCOPY NUMBER,
1525 p_organization_id IN NUMBER, /* Operating Unit */
1526 p_source IN VARCHAR2,
1527 p_invoice_id IN NUMBER,
1528 p_line_id IN NUMBER,
1529 p_tax_type IN VARCHAR2,
1530 p_tax_id IN NUMBER,
1531 p_tax_rate IN NUMBER,
1532 p_recoverable_ptg IN NUMBER,
1533 p_party_type IN VARCHAR2,
1534 p_party_id IN NUMBER,
1535 p_party_site_id IN NUMBER,
1536 p_trx_tax_amount IN NUMBER,
1537 p_trx_currency IN VARCHAR2,
1538 p_curr_conv_date IN DATE,
1539 p_curr_conv_rate IN NUMBER,
1540 p_tax_amount IN NUMBER,
1541 p_recoverable_amount IN NUMBER,
1542 p_recovered_amount IN NUMBER,
1543 p_item_line_id IN NUMBER,
1544 p_item_id IN NUMBER,
1545 p_taxable_basis IN NUMBER,
1546 p_parent_reference_id IN NUMBER,
1547 p_reversal_flag IN VARCHAR2,
1548 p_batch_id IN NUMBER,
1549 p_process_flag OUT NOCOPY VARCHAR2,
1550 p_process_message OUT NOCOPY VARCHAR2
1551 /* Location_Id Required for VAT??? */
1552 ) IS
1553
1554 BEGIN
1555
1556 INSERT INTO jai_rgm_trx_refs(
1557 reference_id,
1558 organization_id,
1559 source,
1560 invoice_id,
1561 line_id,
1562 tax_type,
1563 tax_id,
1564 tax_rate,
1565 recoverable_ptg,
1566 trx_tax_amount,
1567 trx_currency,
1568 curr_conv_date,
1569 curr_conv_rate,
1570 tax_amount,
1571 recoverable_amount,
1572 recovered_amount,
1573 taxable_basis,
1574 party_type,
1575 party_id,
1576 party_site_id,
1577 item_line_id,
1578 item_id,
1579 parent_reference_id,
1580 reversal_flag,
1581 batch_id,
1582 creation_date,
1583 created_by,
1584 last_update_date,
1585 last_updated_by,
1586 last_update_login
1587 ) VALUES (
1588 jai_rgm_trx_refs_s.nextval,
1589 p_organization_id,
1590 p_source,
1591 p_invoice_id,
1592 p_line_id,
1593 p_tax_type,
1594 p_tax_id,
1595 p_tax_rate,
1596 p_recoverable_ptg,
1597 p_trx_tax_amount,
1598 p_trx_currency,
1599 p_curr_conv_date,
1600 p_curr_conv_rate,
1601 p_tax_amount,
1602 p_recoverable_amount,
1603 p_recovered_amount,
1604 p_taxable_basis,
1605 p_party_type,
1606 p_party_id,
1607 p_party_site_id,
1608 p_item_line_id,
1609 p_item_id,
1610 p_parent_reference_id,
1611 p_reversal_flag,
1612 p_batch_id,
1613 sysdate,
1614 fnd_global.user_id,
1615 sysdate,
1616 fnd_global.user_id,
1617 fnd_global.login_id
1618 ) RETURNING reference_id INTO p_reference_id;
1619
1620 p_process_flag := jai_constants.successful;
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 p_process_flag := jai_constants.unexpected_error;
1625 p_process_message := 'jai_cmn_rgm_recording_pkg.insert_reference failed with error - '||SQLERRM;
1626 fnd_file.put_line( fnd_file.log, p_process_message);
1627 END insert_reference;
1628
1629 FUNCTION get_account(
1630 p_regime_id IN NUMBER,
1631 p_organization_type IN VARCHAR2,
1632 p_organization_id IN NUMBER,
1633 p_location_id IN NUMBER,
1634 p_tax_type IN VARCHAR2,
1635 p_account_name IN VARCHAR2
1636 ) RETURN NUMBER IS
1637
1638 /* Added by Ramananda for bug#4407165 */
1639 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_account';
1640
1641 CURSOR c_orgn_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1642 SELECT decode(cp_register_type,
1643 jai_constants.register_type_a, modvat_rm_account_id,
1644 jai_constants.register_type_c, modvat_cg_account_id,
1645 jai_constants.register_type_pla, modvat_pla_account_id
1646 )
1647 FROM JAI_CMN_INVENTORY_ORGS a
1648 WHERE organization_id = cp_organization_id
1649 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1650 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1651 );
1652
1653 /*
1654 || Cursor added by Ramananda
1655 || Start of Bug#4546114
1656 */
1657 CURSOR c_orgn_cess_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1658 SELECT decode(cp_register_type,
1659 jai_constants.register_type_a, excise_edu_cess_rm_account ,
1660 jai_constants.register_type_c, excise_edu_cess_cg_account ,
1661 jai_constants.register_type_pla, modvat_pla_account_id
1662 )
1663 FROM JAI_CMN_INVENTORY_ORGS a
1664 WHERE organization_id = cp_organization_id
1665 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1666 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1667 );
1668 /*
1669 || End of Bug#4546114
1670 */
1671
1672 /*cursor added by vkaranam for bug #5989740*/
1673 -- start 5989740
1674
1675
1676
1677 CURSOR c_orgn_sh_cess_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1678 SELECT decode(cp_register_type,
1679 jai_constants.register_type_a, SH_CESS_RM_ACCOUNT ,
1680 jai_constants.register_type_c, SH_CESS_CG_ACCOUNT_ID ,
1681 jai_constants.register_type_pla, modvat_pla_account_id
1682 )
1683 FROM JAI_CMN_INVENTORY_ORGS a
1684 WHERE organization_id = cp_organization_id
1685 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1686 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1687 );
1688 -- end 5989740
1689
1690
1691 CURSOR c_orgn_tax_type_account(cp_regime_id IN NUMBER,
1692 cp_organization_type IN VARCHAR2, cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1693 cp_tax_type IN VARCHAR2, cp_account_name IN VARCHAR2) IS
1694 SELECT to_number(accnts.attribute_value)
1695 FROM JAI_RGM_REGISTRATIONS tax_types, JAI_RGM_ORG_REGNS_V accnts
1696 WHERE tax_types.regime_id = cp_regime_id
1697 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1698 AND tax_types.attribute_code = cp_tax_type
1699 AND accnts.regime_id = tax_types.regime_id
1700 AND accnts.registration_type = jai_constants.regn_type_accounts
1701 AND accnts.parent_registration_id = tax_types.registration_id
1702 AND accnts.attribute_code = cp_account_name
1703 AND accnts.organization_type = cp_organization_type
1704 AND accnts.organization_id = cp_organization_id
1705 AND (cp_location_id IS NULL OR location_id = cp_location_id);
1706
1707
1708 /*Cursor added by ssawant for bug 5879769 */
1709 CURSOR c_orgn_tax_type_account_ou
1710 ( cp_regime_id IN NUMBER,
1711 cp_organization_type IN VARCHAR2,
1712 cp_organization_id IN NUMBER,
1713 cp_location_id IN NUMBER,
1714 cp_tax_type IN VARCHAR2,
1715 cp_account_name IN VARCHAR2
1716 )
1717 IS
1718 SELECT to_number(accnts.attribute_value)
1719 FROM JAI_RGM_REGISTRATIONS tax_types,
1720 jai_rgm_parties jrp ,
1721 JAI_RGM_REGISTRATIONS accnts
1722 WHERE tax_types.regime_id = cp_regime_id
1723 AND jrp.regime_id = -accnts.regime_id
1724 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1725 AND tax_types.attribute_code = cp_tax_type
1726 AND accnts.regime_id = tax_types.regime_id
1727 AND accnts.registration_type = jai_constants.regn_type_accounts
1728 AND accnts.parent_registration_id = tax_types.registration_id
1729 AND accnts.attribute_code = cp_account_name
1730 AND jrp.organization_type = cp_organization_type
1731 AND jrp.organization_id = cp_organization_id ;
1732
1733
1734 CURSOR c_operating_unit_of_inv_org(cp_organization_id IN NUMBER) IS
1735 SELECT to_number(operating_unit) org_id
1736 FROM org_organization_definitions
1737 WHERE organization_id = cp_organization_id;
1738
1739 CURSOR c_ap_system_parameters(cp_org_id IN NUMBER) IS
1740 SELECT disc_taken_code_combination_id
1741 FROM ap_system_parameters_all
1742 WHERE org_id = cp_org_id;
1743
1744 ln_code_combination_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1745
1746 lv_organization_type VARCHAR2(2);
1747 ln_organization_id NUMBER;
1748
1749 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1750 lv_excise_cess JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE; /* Added by Ramananda - bug# 4546114*/
1751
1752 BEGIN
1753
1754 lv_excise_cess := 'EXCISE-CESS'; /* Added by Ramananda - bug# 4546114*/
1755
1756 /* following code is used to get the Discount Account in case of Payables */
1757 IF p_account_name = jai_cmn_rgm_recording_pkg.ap_discount_accnt THEN
1758 OPEN c_ap_system_parameters(p_organization_id);
1759 FETCH c_ap_system_parameters INTO ln_code_combination_id;
1760 CLOSE c_ap_system_parameters;
1761
1762 GOTO end_of_function;
1763 END IF;
1764
1765 OPEN c_regime_code(p_regime_id);
1766 FETCH c_regime_code INTO lv_regime_code;
1767 CLOSE c_regime_code;
1768
1769 /*Bug 9883352 - Service Tax Regime Registration is always on IO and not on OU. Hence commenting the below*/
1770 /*
1771 IF lv_regime_code = jai_constants.service_regime
1772 AND p_location_id IS NULL AND p_organization_type = jai_constants.orgn_type_io
1773 THEN
1774 lv_organization_type := jai_constants.orgn_type_ou;
1775
1776 OPEN c_operating_unit_of_inv_org(p_organization_id);
1777 FETCH c_operating_unit_of_inv_org INTO ln_organization_id;
1778 CLOSE c_operating_unit_of_inv_org;
1779
1780 ELSE
1781 lv_organization_type := p_organization_type;
1782 ln_organization_id := p_organization_id;
1783 END IF;
1784 */
1785 /*Bug 9883352 - Assigning Organization Type and ID without the above checks*/
1786 lv_organization_type := p_organization_type;
1787 ln_organization_id := p_organization_id;
1788 IF lv_regime_code = jai_constants.service_regime
1789 AND lv_organization_type = jai_constants.orgn_type_io
1790 THEN
1791 IF upper(p_tax_type) = UPPER(jai_constants.tax_type_excise) THEN /* IF condition added by Ramananda - bug#4546114 */
1792 OPEN c_orgn_account(ln_organization_id, p_location_id, p_account_name);
1793 FETCH c_orgn_account INTO ln_code_combination_id;
1794 CLOSE c_orgn_account;
1795
1796 IF ln_code_combination_id IS NULL THEN
1797 OPEN c_orgn_account(ln_organization_id, NULL, p_account_name);
1798 FETCH c_orgn_account INTO ln_code_combination_id;
1799 CLOSE c_orgn_account;
1800 END IF;
1801
1802 -- END IF ; /* END IF is commented and is replaced by elsif by ssawant for bug 5879769 */
1803
1804 /*
1805 || Following IF condition and the cursor in it added by Ramananda
1806 || Start of Bug#4546114
1807 */
1808 ELSIF upper(p_tax_type) IN (lv_excise_cess ,
1809 jai_constants.tax_type_exc_edu_cess,
1810 jai_constants.tax_type_cvd_edu_cess ,
1811 jai_constants.tax_type_customs_edu_cess
1812 ) THEN
1813
1814 OPEN c_orgn_cess_account(ln_organization_id, p_location_id, p_account_name);
1815 FETCH c_orgn_cess_account INTO ln_code_combination_id;
1816 CLOSE c_orgn_cess_account;
1817
1818 IF ln_code_combination_id IS NULL THEN
1819 OPEN c_orgn_cess_account(ln_organization_id, NULL, p_account_name);
1820 FETCH c_orgn_cess_account INTO ln_code_combination_id;
1821 CLOSE c_orgn_Cess_account;
1822 END IF;
1823 --Date 05/03/2007 by vkaranam for bug#5989740
1824 -- start 5989740
1825 ELSIF upper(p_tax_type) IN (jai_constants.tax_type_sh_exc_edu_cess ,
1826 jai_constants.tax_type_sh_cvd_edu_cess ,
1827 jai_constants.tax_type_sh_customs_edu_Cess
1828 ) THEN
1829
1830 OPEN c_orgn_sh_cess_account(ln_organization_id, p_location_id, p_account_name);
1831 FETCH c_orgn_sh_cess_account INTO ln_code_combination_id;
1832 CLOSE c_orgn_sh_cess_account;
1833
1834 IF ln_code_combination_id IS NULL THEN
1835 OPEN c_orgn_sh_cess_account(ln_organization_id, NULL, p_account_name);
1836 FETCH c_orgn_sh_cess_account INTO ln_code_combination_id;
1837 CLOSE c_orgn_sh_cess_account;
1838 END IF;
1839 -- end 5989740
1840 ELSE /* added by ssawant for bug 5879769 . This condition is newly added so that the Accoutn would be returned
1841 if regime SERVICE,org is IO and Taxes are of Service Type*/
1842
1843 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1844 p_location_id, p_tax_type, p_account_name);
1845 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1846 CLOSE c_orgn_tax_type_account;
1847
1848 END IF;
1849 /*
1850 || End of Bug#4546114
1851 */
1852 ELSIF lv_regime_code = jai_constants.vat_regime
1853 AND lv_organization_type = jai_constants.orgn_type_io
1854 THEN
1855 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1856 p_location_id, p_tax_type, p_account_name);
1857 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1858 CLOSE c_orgn_tax_type_account;
1859
1860 /*Added by CSahoo Bug# 5631784*/
1861 ELSIF lv_regime_code = jai_constants.tcs_regime THEN
1862
1863 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1864 p_location_id, p_tax_type, p_account_name);
1865 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1866 CLOSE c_orgn_tax_type_account;
1867
1868 ELSIF lv_organization_type = jai_constants.orgn_type_ou THEN
1869
1870 /* added by ssawant for bug 5879769 */
1871 IF lv_regime_code = jai_constants.service_regime THEN
1872
1873 OPEN c_orgn_tax_type_account_ou(p_regime_id, jai_constants.orgn_type_ou, ln_organization_id,
1874 null, p_tax_type, p_account_name);
1875 FETCH c_orgn_tax_type_account_ou INTO ln_code_combination_id;
1876 CLOSE c_orgn_tax_type_account_ou;
1877 ELSE
1878 OPEN c_orgn_tax_type_account(p_regime_id, jai_constants.orgn_type_ou, ln_organization_id,
1879 null, p_tax_type, p_account_name);
1880 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1881 CLOSE c_orgn_tax_type_account;
1882 END IF;
1883
1884 END IF;
1885
1886 <<end_of_function>>
1887
1888 RETURN ln_code_combination_id;
1889
1890
1891 /* Added by Ramananda for bug#4407165 */
1892 EXCEPTION
1893 WHEN OTHERS THEN
1894 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1895 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1896 app_exception.raise_exception;
1897
1898 END get_account;
1899
1900 PROCEDURE get_period_name(
1901 p_organization_type IN VARCHAR2,
1902 p_organization_id IN NUMBER,
1903 p_accounting_date IN OUT NOCOPY DATE,
1904 p_period_name OUT NOCOPY VARCHAR2,
1905 p_sob_id OUT NOCOPY NUMBER
1906 ) IS
1907
1908 /* Added by Ramananda for bug#4407165 */
1909 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_period_name';
1910 /* Bug 5243532. Added by Lakshmi Gopalsami
1911 Removed the cursors c_ou_sob_id and c_io_sob_id
1912 which is referring to hr_operating_units
1913 and org_organization_definitions respectively.
1914 Implemented the same using caching logic.
1915 */
1916 CURSOR c_period_dtl(cp_sob_id IN NUMBER, cp_accounting_date IN DATE) IS
1917 SELECT period_name, start_date, end_date, closing_status
1918 FROM gl_period_statuses
1919 WHERE application_id = jai_constants.gl_application_id
1920 AND set_of_books_id = cp_sob_id
1921 AND closing_status IN ('O','F') -- added for bug#7010029
1922 AND cp_accounting_date BETWEEN start_date AND end_date
1923 ORDER BY period_year, period_num; -- added for bug#7010029
1924
1925 r_period_dtl c_period_dtl%ROWTYPE;
1926 ln_sob_id NUMBER;
1927 ld_accounting_date DATE;
1928
1929 /* Bug 5243532. Added by Lakshmi Gopalsami
1930 Defined variable for implementing caching logic
1931 */
1932 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1933 BEGIN
1934
1935 -- CHK we need to see whether the accounting date that is being used belong to a open period or not
1936 -- GL_PERIOD_STATUSES has CLOSING_STATUS column that tells whether the the period is closed or not for each APPLICATION
1937
1938 -- Validation of whether the accounting date falls under an open period or not, if not, then we populate the first date of period
1939 /* Bug 5243532. Added by Lakshmi Gopalsami
1940 Removed the logic which is referring to hr_operating_units
1941 and org_organization_definitions for getting SOB and
1942 implemented the same using caching logic.
1943 */
1944 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1945 (p_org_id => p_organization_id );
1946
1947 ln_sob_id := l_func_curr_det.ledger_id;
1948
1949 OPEN c_period_dtl(ln_sob_id, p_accounting_date);
1950 FETCH c_period_dtl INTO r_period_dtl;
1951 CLOSE c_period_dtl;
1952 jai_cmn_utils_pkg.print_log('6395039.log', 'IN get_period_name : ln_sob_id '||ln_sob_id||' r_period_dtl.period_name '||r_period_dtl.period_name);
1953
1954 IF r_period_dtl.closing_status IN ('O','F') THEN
1955 p_sob_id := ln_sob_id;
1956 p_period_name := r_period_dtl.period_name;
1957 ELSE
1958
1959 FOR period IN ( SELECT period_name, start_date, end_date, closing_status
1960 FROM gl_period_statuses
1961 WHERE application_id = jai_constants.gl_application_id
1962 AND set_of_books_id = ln_sob_id
1963 AND start_date > p_accounting_date
1964 ORDER BY period_year, period_num
1965 )
1966 LOOP
1967 IF period.closing_status IN('O','F') THEN
1968 p_sob_id := ln_sob_id;
1969 p_period_name := period.period_name;
1970 ld_accounting_date := period.start_date;
1971 jai_cmn_utils_pkg.print_log('6395039.log', 'IN get_period_name : in IF Block');
1972 exit;
1973 END IF;
1974 END LOOP;
1975
1976 IF g_debug='Y' THEN
1977 fnd_file.put_line(fnd_file.log,'GL Period is closed for Accounting Date:'||to_char(p_accounting_date)
1978 ||'. Hence passing with Entries for '||to_char(ld_accounting_date)
1979 );
1980 END IF;
1981
1982 p_accounting_date := ld_accounting_date;
1983
1984 END IF;
1985
1986 /* Added by Ramananda for bug#4407165 */
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 p_period_name := null;
1990 p_sob_id := null;
1991 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1992 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1993 app_exception.raise_exception;
1994
1995 END get_period_name;
1996
1997
1998 PROCEDURE get_accounting_dtls(
1999 p_source IN VARCHAR2,
2000 p_src_trx_type IN VARCHAR2,
2001 p_organization_type IN VARCHAR2,
2002 p_account_name OUT NOCOPY VARCHAR2,
2003 p_account_entry_type OUT NOCOPY VARCHAR2
2004 ) IS
2005
2006 /* Added by Ramananda for bug#4407165 */
2007 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_accounting_dtls';
2008
2009 BEGIN
2010
2011 -- following IF - ELSIF is valid for Organization Type OU w.r.t account_name and entry_type
2012 -- where as for Organization Type IO, it is valid only for entry_type
2013 IF p_source = jai_constants.source_receive THEN
2014 p_account_name := jai_constants.recovery_interim;
2015 p_account_entry_type := jai_constants.debit;
2016
2017 ELSIF p_source = jai_constants.source_rtv THEN
2018 p_account_name := jai_constants.liability_interim;
2019 p_account_entry_type := jai_constants.credit;
2020
2021 ELSIF p_source = jai_constants.source_ap THEN
2022 p_account_name := jai_constants.recovery;
2023 p_account_entry_type := jai_constants.debit;
2024 ELSIF p_source = jai_constants.source_ar THEN
2025 p_account_name := jai_constants.liability;
2026 p_account_entry_type := jai_constants.credit;
2027
2028 ELSIF p_source = jai_constants.source_manual_entry THEN
2029 --lv_account_name := p_account_name;
2030 IF p_src_trx_type IN (jai_constants.recovery, jai_constants.recovery_interim) THEN
2031 p_account_entry_type := jai_constants.debit;
2032 ELSIF p_src_trx_type IN (jai_constants.liability, jai_constants.liability_interim) THEN
2033 p_account_entry_type := jai_constants.credit;
2034 END IF;
2035
2036 ELSIF p_source IN (jai_constants.service_src_distribute_out, jai_constants.source_settle_out) THEN
2037 p_account_name := jai_constants.liability;
2038 p_account_entry_type := jai_constants.credit;
2039
2040 ELSIF p_source IN (jai_constants.service_src_distribute_in, jai_constants.source_settle_in) THEN
2041 p_account_name := jai_constants.recovery;
2042 p_account_entry_type := jai_constants.debit;
2043 END IF;
2044
2045 IF p_organization_type = jai_constants.orgn_type_io THEN
2046 p_account_name := jai_constants.register_type_a;
2047 END IF;
2048
2049
2050 /* Added by Ramananda for bug#4407165 */
2051 EXCEPTION
2052 WHEN OTHERS THEN
2053 p_account_name := null;
2054 p_account_entry_type := null;
2055 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
2056 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
2057 app_exception.raise_exception;
2058
2059 END get_accounting_dtls;
2060
2061 ---------------------------- UPDATE_RECOVERED_AMOUNT ---------------------------
2062 PROCEDURE update_reference(
2063 p_source IN VARCHAR2,
2064 p_reference_id IN NUMBER,
2065 p_recovered_amount IN NUMBER,
2066 p_discounted_amount IN NUMBER DEFAULT NULL,
2067 p_process_flag OUT NOCOPY VARCHAR2,
2068 p_process_message OUT NOCOPY VARCHAR2
2069 ) IS
2070
2071 lv_statement VARCHAR2(2); -- := '1' File.Sql.35 by Brathod
2072 BEGIN
2073 lv_statement :='1' ; -- File.Sql.35 by Brathod
2074 UPDATE jai_rgm_trx_refs
2075 SET recovered_amount = nvl(recovered_amount,0) + nvl(p_recovered_amount, 0),
2076 discounted_amount = nvl(discounted_amount,0) + nvl(p_discounted_amount,0),
2077 -- recoverable_amount = recoverable_amount - nvl(p_amount, 0),
2078 last_update_date = sysdate,
2079 last_updated_by = fnd_global.user_id
2080 WHERE reference_id = p_reference_id;
2081
2082 lv_statement := '2';
2083 p_process_flag := jai_constants.successful;
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 p_process_flag := jai_constants.unexpected_error;
2088 p_process_message := 'jai_cmn_rgm_recording_pkg.update_reference (Stmt'||lv_statement||') Error Occured:'||SQLERRM;
2089 END update_reference;
2090
2091
2092 /* following procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2093 PROCEDURE insert_vat_repository_entry(
2094 pn_repository_id OUT NOCOPY NUMBER,
2095 pn_regime_id IN NUMBER,
2096 pv_tax_type IN VARCHAR2,
2097 pv_organization_type IN VARCHAR2,
2098 pn_organization_id IN NUMBER,
2099 pn_location_id IN NUMBER,
2100 pv_source IN VARCHAR2,
2101 pv_source_trx_type IN VARCHAR2,
2102 pv_source_table_name IN VARCHAR2,
2103 pn_source_id IN NUMBER,
2104 pd_transaction_date IN DATE,
2105 pv_account_name IN VARCHAR2,
2106 pn_charge_account_id IN NUMBER,
2107 pn_balancing_account_id IN NUMBER,
2108 pn_credit_amount IN OUT NOCOPY NUMBER,
2109 pn_debit_amount IN OUT NOCOPY NUMBER,
2110 pn_assessable_value IN NUMBER,
2111 pn_tax_rate IN NUMBER,
2112 pn_reference_id IN NUMBER,
2113 pn_batch_id IN NUMBER,
2114 pn_inv_organization_id IN NUMBER,
2115 pv_invoice_no IN VARCHAR2, /* this holds either generated VAT Invoice Number or Vendor Inovice Number */
2116 pd_invoice_date IN DATE, /* this holds VAT Invoice Date or Vendor VAT Inovice Date */
2117 pv_called_from IN VARCHAR2,
2118 pv_process_flag OUT NOCOPY VARCHAR2,
2119 pv_process_message OUT NOCOPY VARCHAR2,
2120 --Added by Bo Li for Bug9305067 BEGIN
2121 -------------------------------------------------------------
2122 pv_trx_reference_context IN VARCHAR2 DEFAULT NULL,
2123 pv_trx_reference1 IN VARCHAR2 DEFAULT NULL,
2124 pv_trx_reference2 IN VARCHAR2 DEFAULT NULL,
2125 pv_trx_reference3 IN VARCHAR2 DEFAULT NULL,
2126 pv_trx_reference4 IN VARCHAR2 DEFAULT NULL,
2127 pv_trx_reference5 IN VARCHAR2 DEFAULT NULL,
2128 ------------------------------------------------------------------
2129 --Added by Bo Li for Bug9305067 END
2130 pn_settlement_id IN NUMBER DEFAULT NULL --added for bug#9350172
2131 ) IS
2132
2133 CURSOR c_primary_regno(cp_regime_id IN NUMBER, cp_orgn_type in varchar2,
2134 cp_orgn_id in number, cp_location_id in number,
2135 p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE) IS --rchandan for bug#4428980
2136 SELECT attribute_value
2137 FROM JAI_RGM_ORG_REGNS_V
2138 WHERE regime_id = cp_regime_id
2139 AND organization_type = cp_orgn_type
2140 AND organization_id = cp_orgn_id
2141 and (cp_location_id is null or location_id = cp_location_id)
2142 AND registration_type = jai_constants.regn_type_others
2143 AND attribute_type_code = p_att_type_code;
2144
2145 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
2146 ld_transaction_date DATE;
2147 lv_primary_regime_regno jai_rgm_trx_records.regime_primary_regno%TYPE;
2148
2149 lv_statement_id VARCHAR2(3);
2150 BEGIN
2151
2152 lv_statement_id := '1';
2153 OPEN c_regime_code(pn_regime_id);
2154 FETCH c_regime_code INTO lv_regime_code;
2155 CLOSE c_regime_code;
2156
2157 lv_statement_id := '2';
2158 IF pd_transaction_date IS NOT NULL THEN
2159 ld_transaction_date := pd_transaction_date;
2160 ELSE
2161 ld_transaction_date := trunc(sysdate);
2162 END IF;
2163
2164 lv_statement_id := '3';
2165 OPEN c_primary_regno(pn_regime_id, pv_organization_type, pn_organization_id, pn_location_id,'PRIMARY'); --rchandan for bug#4428980
2166 FETCH c_primary_regno INTO lv_primary_regime_regno;
2167 CLOSE c_primary_regno;
2168
2169 lv_statement_id := '4';
2170 /* Rounding */
2171 pn_credit_amount := round( pn_credit_amount, jai_constants.vat_rgm_rnd_factor);
2172 pn_debit_amount := round( pn_debit_amount, jai_constants.vat_rgm_rnd_factor);
2173
2174 lv_statement_id := '5';
2175 INSERT INTO jai_rgm_trx_records(
2176 repository_id, regime_code, tax_type, source,
2177 source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
2178 settled_amount, settled_flag, settlement_id, organization_type,
2179 organization_id, location_id, account_name, charge_account_id, balancing_account_id,
2180 reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
2181 trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
2182 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
2183 trx_reference_context, trx_reference1, trx_reference2, trx_reference3, trx_reference4, trx_reference5
2184 , inv_organization_id, regime_primary_regno, invoice_no, invoice_date
2185 ) VALUES (
2186 jai_rgm_trx_records_s.nextval, lv_regime_code, pv_tax_type, pv_source,
2187 pn_source_id, pv_source_table_name, ld_transaction_date, pn_debit_amount, pn_credit_amount,
2188 null, null, pn_settlement_id, pv_organization_type, --added pn_settlement_id for bug#9350172
2189 pn_organization_id, pn_location_id, pv_account_name, pn_charge_account_id, pn_balancing_account_id,
2190 pn_reference_id, pv_source_trx_type, pn_tax_rate, pn_assessable_value, pn_batch_id,
2191 jai_constants.func_curr, null, null, pn_credit_amount, pn_debit_amount,
2192 sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
2193 pv_trx_reference_context, pv_trx_reference1, pv_trx_reference2, pv_trx_reference3, pv_trx_reference4, pv_trx_reference5
2194 , pn_inv_organization_id, lv_primary_regime_regno, pv_invoice_no, pd_invoice_date
2195 ) RETURNING repository_id INTO pn_repository_id;
2196
2197 pv_process_flag := jai_constants.successful;
2198 lv_statement_id := '6';
2199
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 pv_process_flag := jai_constants.unexpected_error;
2203 pv_process_message := 'insert_vat_repository_entry Error(Stmt:'||lv_statement_id||') Occured:'||SQLERRM;
2204 --lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
2205 Fnd_file.put_line( fnd_file.log, 'Error in insert_vat_repository_entry. Stmt:'||lv_statement_id);
2206 END insert_vat_repository_entry;
2207
2208
2209 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2210 /* Two GL Entries are passed if both pn_credit_ccid and pn_debit_ccid are given as inputs to this procedure. Relevant amounts
2211 are taken while passing inserting into GL
2212 Incase a single entry needs to be passed, then pass the relevant ccid and amount
2213 */
2214 PROCEDURE do_vat_accounting(
2215 pn_regime_id IN NUMBER,
2216 pn_repository_id IN NUMBER,
2217 pv_organization_type IN VARCHAR2,
2218 pn_organization_id IN NUMBER,
2219 pd_accounting_date IN DATE,
2220 pd_transaction_date IN DATE,
2221 pn_credit_amount IN NUMBER,
2222 pn_debit_amount IN NUMBER,
2223 pn_credit_ccid IN NUMBER,
2224 pn_debit_ccid IN NUMBER,
2225 pv_called_from IN VARCHAR2,
2226 pv_process_flag OUT NOCOPY VARCHAR2,
2227 pv_process_message OUT NOCOPY VARCHAR2,
2228 pv_tax_type IN VARCHAR2 DEFAULT NULL,
2229 pv_source IN VARCHAR2 DEFAULT NULL,
2230 pv_source_trx_type IN VARCHAR2 DEFAULT NULL,
2231 pv_source_table_name IN VARCHAR2 DEFAULT NULL,
2232 pn_source_id IN NUMBER DEFAULT NULL,
2233 pv_reference_name IN VARCHAR2 DEFAULT NULL,
2234 pn_reference_id IN NUMBER DEFAULT NULL
2235 ) IS
2236
2237 r_repo_dtl c_repository_dtl%ROWTYPE;
2238
2239 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
2240 lv_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE;
2241 ln_credit NUMBER;
2242 ln_debit NUMBER;
2243 ln_accounted_credit NUMBER;
2244 ln_accounted_debit NUMBER;
2245 ld_accounting_date DATE;
2246 ld_transaction_date DATE;
2247
2248 lv_source JAI_RGM_TRX_RECORDS.source%TYPE;
2249 lv_source_trx_type JAI_RGM_TRX_RECORDS.source_trx_type%TYPE;
2250 lv_source_table_name JAI_RGM_TRX_RECORDS.source_table_name%TYPE;
2251 ln_source_id JAI_RGM_TRX_RECORDS.source_document_id%TYPE;
2252 ln_repository_id JAI_RGM_TRX_RECORDS.repository_id%TYPE;
2253 lv_repository_name VARCHAR2(30);
2254
2255 lv_statement_id VARCHAR2(3);
2256
2257 BEGIN
2258
2259 lv_statement_id := '1';
2260 jai_cmn_utils_pkg.print_log('6395039.log','START '||lv_statement_id);
2261
2262 IF pn_repository_id IS NULL THEN
2263
2264 lv_statement_id := '2';
2265 OPEN c_regime_code(pn_regime_id);
2266 FETCH c_regime_code INTO lv_regime_code;
2267 CLOSE c_regime_code;
2268 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id||' lv_regime_code '||lv_regime_code);
2269
2270 lv_tax_type := pv_tax_type;
2271 lv_source := pv_source;
2272 lv_source_trx_type := pv_source_trx_type;
2273 lv_source_table_name := pv_source_table_name;
2274 ln_source_id := pn_source_id;
2275 lv_repository_name := pv_reference_name;
2276 ln_repository_id := pn_reference_id;
2277
2278 ELSE
2279
2280 lv_statement_id := '3';
2281 OPEN c_repository_dtl(pn_repository_id);
2282 FETCH c_repository_dtl INTO r_repo_dtl;
2283 CLOSE c_repository_dtl;
2284 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2285
2286 lv_regime_code := r_repo_dtl.regime_code;
2287 lv_tax_type := r_repo_dtl.tax_type;
2288 lv_source := r_repo_dtl.source;
2289 lv_source_trx_type := r_repo_dtl.source_trx_type;
2290 lv_source_table_name := r_repo_dtl.source_table_name;
2291 ln_source_id := r_repo_dtl.source_document_id;
2292 lv_repository_name := jai_constants.repository_name;
2293 ln_repository_id := pn_repository_id;
2294 END IF;
2295
2296 lv_statement_id := '4';
2297 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2298 IF pd_transaction_date IS NULL THEN
2299 ld_transaction_date := trunc(sysdate);
2300 ELSE
2301 ld_transaction_date := pd_transaction_date;
2302 END IF;
2303
2304 lv_statement_id := '5';
2305 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2306 IF pd_accounting_date IS NULL THEN
2307 ld_accounting_date := ld_transaction_date;
2308 ELSE
2309 ld_accounting_date := pd_accounting_date;
2310 END IF;
2311
2312 lv_statement_id := '6';
2313 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2314 IF pn_credit_ccid IS NOT NULL AND pn_credit_amount <> 0 THEN
2315
2316 lv_statement_id := '7';
2317 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2318 ln_credit := pn_credit_amount;
2319 ln_debit := null;
2320 ln_accounted_credit := pn_credit_amount;
2321 ln_accounted_debit := null;
2322
2323 lv_statement_id := '8';
2324 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2325 post_accounting(
2326 p_regime_code => lv_regime_code,
2327 p_tax_type => lv_tax_type,
2328 p_organization_type => pv_organization_type,
2329 p_organization_id => pn_organization_id,
2330 p_source => lv_source,
2331 p_source_trx_type => lv_source_trx_type,
2332 p_source_table_name => lv_source_table_name,
2333 p_source_document_id => ln_source_id,
2334 p_code_combination_id => pn_credit_ccid,
2335 p_entered_cr => ln_credit,
2336 p_entered_dr => ln_debit,
2337 p_accounted_cr => ln_accounted_credit,
2338 p_accounted_dr => ln_accounted_debit,
2339 p_accounting_date => ld_accounting_date,
2340 p_transaction_date => ld_transaction_date,
2341 p_calling_object => pv_called_from,
2342 p_repository_name => lv_repository_name,
2343 p_repository_id => ln_repository_id,
2344 p_reference_name => null, --lv_reference_name,
2345 p_reference_id => null, --ln_reference_id,
2346 p_currency_code => jai_constants.func_curr, --p_currency_code,
2347 p_curr_conv_date => null, --p_curr_conv_date,
2348 p_curr_conv_type => null, -- p_curr_conv_type,
2349 p_curr_conv_rate => null --p_curr_conv_rate
2350 );
2351
2352 END IF;
2353
2354 lv_statement_id := '9';
2355 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2356 IF pn_debit_ccid IS NOT NULL AND pn_debit_amount <> 0 THEN
2357
2358 lv_statement_id := '10';
2359 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2360 ln_debit := pn_debit_amount;
2361 ln_credit := null;
2362 ln_accounted_debit := pn_debit_amount;
2363 ln_accounted_credit := null;
2364
2365 lv_statement_id := '11';
2366 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2367 post_accounting(
2368 p_regime_code => lv_regime_code,
2369 p_tax_type => lv_tax_type,
2370 p_organization_type => pv_organization_type,
2371 p_organization_id => pn_organization_id,
2372 p_source => lv_source,
2373 p_source_trx_type => lv_source_trx_type,
2374 p_source_table_name => lv_source_table_name,
2375 p_source_document_id => ln_source_id,
2376 p_code_combination_id => pn_debit_ccid,
2377 p_entered_cr => ln_credit,
2378 p_entered_dr => ln_debit,
2379 p_accounted_cr => ln_accounted_credit,
2380 p_accounted_dr => ln_accounted_debit,
2381 p_accounting_date => ld_accounting_date,
2382 p_transaction_date => ld_transaction_date,
2383 p_calling_object => pv_called_from,
2384 p_repository_name => lv_repository_name,
2385 p_repository_id => ln_repository_id,
2386 p_reference_name => null, --lv_reference_name,
2387 p_reference_id => null, --ln_reference_id,
2388 p_currency_code => jai_constants.func_curr, --p_currency_code,
2389 p_curr_conv_date => null, --p_curr_conv_date,
2390 p_curr_conv_type => null, -- p_curr_conv_type,
2391 p_curr_conv_rate => null --p_curr_conv_rate
2392 );
2393
2394 END IF;
2395
2396 pv_process_flag := jai_constants.successful;
2397 lv_statement_id := '15';
2398 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
2399
2400 EXCEPTION
2401 WHEN OTHERS THEN
2402 pv_process_flag := jai_constants.unexpected_error;
2403 pv_process_message := 'doVatAccounting Error(Stmt:'||lv_statement_id||') Occured:'||SQLERRM;
2404 --lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
2405 jai_cmn_utils_pkg.print_log('6395039.log', 'Error in doVatAccounting. Stmt:'||lv_statement_id);
2406 Fnd_file.put_line( fnd_file.log, 'Error in doVatAccounting. Stmt:'||lv_statement_id);
2407
2408 END do_vat_accounting;
2409
2410 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2411 FUNCTION get_rgm_attribute_value(
2412 pv_regime_code IN VARCHAR2,
2413 pv_organization_type IN VARCHAR2,
2414 pn_organization_id IN NUMBER,
2415 pn_location_id IN NUMBER,
2416 pv_registration_type IN VARCHAR2,
2417 pv_attribute_type_code IN VARCHAR2,
2418 pv_attribute_code IN VARCHAR2
2419 ) RETURN VARCHAR2 IS
2420
2421 /* Test Code
2422 select get_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', 'PRIMARY', null) from dual;
2423 select jai_rgm_trx_recording_pkgget_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', null, 'SAME_INVOICE_NO') from dual;
2424
2425 pv_organization_type, pn_organization_id, pn_location_id, pv_registration_type, pv_attribute_type_code, pv_attribute_code
2426 )
2427 */
2428
2429 CURSOR c_attribute_value (cp_regime_code IN varchar2,
2430 cp_orgn_type in varchar2, cp_orgn_id in number, cp_location_id in number,
2431 cp_registration_type in varchar2, cp_attribute_type_code in varchar2, cp_attribute_code in varchar2) IS
2432 SELECT attribute_value
2433 FROM JAI_RGM_ORG_REGNS_V
2434 WHERE regime_code = cp_regime_code
2435 AND organization_type = cp_orgn_type
2436 AND organization_id = cp_orgn_id
2437 and (cp_location_id is null or location_id = cp_location_id)
2438 AND registration_type = cp_registration_type
2439 AND ( (cp_attribute_code IS NOT NULL AND attribute_code = cp_attribute_code)
2440 or (cp_attribute_code IS NULL AND attribute_type_code = cp_attribute_type_code)
2441 );
2442
2443 lv_attribute_code JAI_RGM_ORG_REGNS_V.attribute_code%type;
2444 lv_attribute_value JAI_RGM_ORG_REGNS_V.attribute_value%type;
2445
2446 ln_fetch_cnt NUMBER;
2447
2448 BEGIN
2449
2450 IF pv_attribute_type_code = 'PRIMARY' THEN
2451 lv_attribute_code := NULL;
2452 ELSE
2453 lv_attribute_code := pv_attribute_code;
2454 END IF;
2455
2456 OPEN c_attribute_value(pv_regime_code, pv_organization_type, pn_organization_id,
2457 pn_location_id, pv_registration_type, pv_attribute_type_code, lv_attribute_code);
2458 FETCH c_attribute_value INTO lv_attribute_value;
2459 ln_fetch_cnt := SQL%ROWCOUNT;
2460 CLOSE c_attribute_value;
2461
2462 RETURN lv_attribute_value;
2463
2464 END get_rgm_attribute_value;
2465
2466 --added the procedure for Bug 8294236
2467
2468 PROCEDURE exc_gain_loss_accounting(
2469 p_repository_id IN NUMBER,
2470 p_regime_id IN NUMBER,
2471 p_tax_type IN VARCHAR2,
2472 p_organization_type IN VARCHAR2,
2473 p_organization_id IN NUMBER,
2474 p_location_id IN NUMBER,
2475 p_source IN VARCHAR2,
2476 p_source_trx_type IN VARCHAR2,
2477 p_source_table_name IN VARCHAR2,
2478 p_source_document_id IN NUMBER,
2479 p_transaction_date IN DATE,
2480 p_account_name IN VARCHAR2,
2481 p_charge_account_id IN NUMBER,
2482 p_balancing_account_id IN NUMBER,
2483 p_exc_gain_loss_amt IN OUT NOCOPY NUMBER,
2484 p_reference_id IN NUMBER,
2485 p_called_from IN VARCHAR2,
2486 p_process_flag OUT NOCOPY VARCHAR2,
2487 p_process_message OUT NOCOPY VARCHAR2,
2488 p_accounting_date IN DATE DEFAULT sysdate,
2489 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
2490 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
2491 p_balancing_location_id IN NUMBER DEFAULT NULL,
2492 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
2493 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
2494 p_currency_code IN VARCHAR2 DEFAULT jai_constants.func_curr,
2495 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
2496 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
2497 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL
2498 ) IS
2499
2500 CURSOR cur_fetch_ou(cp_organization_id NUMBER)
2501 IS
2502 SELECT org_information3
2503 FROM hr_organization_information
2504 WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
2505 AND organization_id = cp_organization_id;
2506
2507 CURSOR cur_get_exc_gain_acc_ar (p_org_id NUMBER)
2508 IS
2509 SELECT code_combination_id_gain,
2510 code_combination_id_loss
2511 FROM AR_SYSTEM_PARAMETERS_ALL
2512 WHERE org_id = p_org_id;
2513
2514 CURSOR cur_get_exc_gain_acc_ap (p_org_id NUMBER)
2515 IS
2516 SELECT gain_code_combination_id,
2517 loss_code_combination_id
2518 FROM AP_SYSTEM_PARAMETERS_ALL
2519 WHERE org_id = p_org_id;
2520
2521 ln_exc_gain_ccid NUMBER;
2522 ln_exc_loss_ccid NUMBER;
2523 ln_exc_ccid NUMBER;
2524 ln_debit_amt NUMBER;
2525 ln_credit_amt NUMBER;
2526 ld_transaction_date DATE;
2527 ld_last_settlement_date DATE;
2528 lv_regime_code VARCHAR2(10);
2529 ln_org_id NUMBER;
2530 ln_bal_org_id NUMBER;
2531 lv_codepath VARCHAR2(500) := '';
2532 lv_balancing_accnt_name JAI_RGM_TRX_RECORDS.account_name%TYPE;
2533 lv_charge_entry_type VARCHAR2(2);
2534 lv_balancing_entry_type VARCHAR2(2);
2535 ln_charge_account_id NUMBER;
2536
2537
2538 BEGIN
2539 FND_FILE.put_line( fnd_file.log, 'In jai_rgm_trx_recording_pkg.exc_gain_loss_accounting procedure');
2540 OPEN c_regime_code(p_regime_id);
2541 FETCH c_regime_code INTO lv_regime_code;
2542 CLOSE c_regime_code;
2543
2544 OPEN cur_fetch_ou(p_organization_id);
2545 FETCH cur_fetch_ou INTO ln_org_id;
2546 CLOSE cur_fetch_ou;
2547
2548 OPEN cur_fetch_ou(p_balancing_orgn_id);
2549 FETCH cur_fetch_ou INTO ln_bal_org_id;
2550 CLOSE cur_fetch_ou;
2551
2552 IF lv_regime_code <> jai_constants.service_regime THEN
2553 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
2554 p_process_flag := jai_constants.expected_error;
2555 p_process_message := 'Transactions other than SERVICE regime are not supported';
2556 FND_FILE.put_line( fnd_file.log, p_process_message);
2557 RETURN;
2558 END IF;
2559
2560 ld_last_settlement_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(pn_regime_id => p_regime_id,
2561 pn_org_id => p_organization_id,
2562 pn_location_id => p_location_id);
2563 IF ld_last_settlement_date > p_transaction_date THEN
2564 ld_transaction_date := ld_last_settlement_date + 1;
2565 lv_codepath := jai_general_pkg.plot_codepath(2.2, lv_codepath);
2566 ELSIF ld_last_settlement_date IS NULL or ld_last_settlement_date <= p_transaction_date THEN
2567 ld_transaction_date := p_transaction_date;
2568 lv_codepath := jai_general_pkg.plot_codepath(2.3, lv_codepath);
2569 END IF;
2570 FND_FILE.put_line( fnd_file.log, 'p_source '|| p_source||' p_exc_gain_loss_amt '|| p_exc_gain_loss_amt);
2571 IF p_source = jai_constants.source_ar THEN
2572
2573 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
2574
2575 lv_balancing_accnt_name := jai_constants.liability_interim;
2576
2577 OPEN cur_get_exc_gain_acc_ar(ln_org_id);
2578 FETCH cur_get_exc_gain_acc_ar INTO ln_exc_gain_ccid, ln_exc_loss_ccid;
2579 CLOSE cur_get_exc_gain_acc_ar;
2580 IF p_exc_gain_loss_amt > 0 THEN
2581 ln_exc_ccid := nvl( ln_exc_gain_ccid, ln_exc_loss_ccid);
2582 lv_charge_entry_type := jai_constants.debit;
2583 lv_balancing_entry_type := jai_constants.credit;
2584
2585 ELSE
2586 ln_exc_ccid := nvl( ln_exc_loss_ccid, ln_exc_gain_ccid);
2587 lv_charge_entry_type := jai_constants.credit;
2588 lv_balancing_entry_type := jai_constants.debit;
2589 END IF;
2590 ELSIF p_source = jai_constants.source_ap THEN
2591
2592 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
2593
2594 lv_balancing_accnt_name := jai_constants.recovery_interim;
2595
2596
2597 OPEN cur_get_exc_gain_acc_ap(ln_org_id);
2598 FETCH cur_get_exc_gain_acc_ap INTO ln_exc_gain_ccid, ln_exc_loss_ccid;
2599 CLOSE cur_get_exc_gain_acc_ap;
2600 IF p_exc_gain_loss_amt > 0 THEN
2601 ln_exc_ccid := nvl( ln_exc_loss_ccid, ln_exc_gain_ccid);
2602 lv_charge_entry_type := jai_constants.credit;
2603 lv_balancing_entry_type := jai_constants.debit;
2604 ELSE
2605 ln_exc_ccid := nvl( ln_exc_gain_ccid, ln_exc_loss_ccid);
2606 lv_charge_entry_type := jai_constants.debit;
2607 lv_balancing_entry_type := jai_constants.credit;
2608 END IF;
2609 END IF;
2610
2611 FND_FILE.put_line( fnd_file.log, 'ln_exc_ccid '|| ln_exc_ccid ||' lv_charge_entry_type '|| lv_charge_entry_type
2612 ||' lv_balancing_entry_type '|| lv_balancing_entry_type);
2613
2614 IF ln_exc_ccid IS NULL THEN
2615 lv_codepath := jai_general_pkg.plot_codepath(21.10, lv_codepath);
2616 p_process_flag := jai_constants.expected_error;
2617 p_process_message := 'Foreign Exchange Gain or Loss Account is not defined in '||p_source;
2618 FND_FILE.put_line( fnd_file.log, p_process_message);
2619 RETURN;
2620 END IF;
2621
2622 IF lv_charge_entry_type = jai_constants.debit THEN
2623 ln_debit_amt := abs(p_exc_gain_loss_amt);
2624 ln_credit_amt := NULL;
2625 ELSE
2626 ln_debit_amt := NULL;
2627 ln_credit_amt := abs(p_exc_gain_loss_amt);
2628 END IF;
2629
2630 FND_FILE.put_line( fnd_file.log,' ln_debit_amt '|| ln_debit_amt ||' ln_credit_amt '|| ln_credit_amt);
2631
2632 -- validate_negative_dr_cr(ln_debit_amt,ln_credit_amt );
2633
2634 post_accounting(
2635 p_regime_code => lv_regime_code,
2636 p_tax_type => p_tax_type,
2637 p_organization_type => p_organization_type,
2638 p_organization_id => p_organization_id,
2639 p_source => p_source,
2640 p_source_trx_type => p_source_trx_type,
2641 p_source_table_name => p_source_table_name,
2642 p_source_document_id => p_source_document_id,
2643 p_code_combination_id => ln_exc_ccid,
2644 p_entered_cr => ln_credit_amt,
2645 p_entered_dr => ln_debit_amt,
2646 p_accounted_cr => ln_credit_amt,
2647 p_accounted_dr => ln_debit_amt,
2648 p_accounting_date => p_accounting_date,
2649 p_transaction_date => ld_transaction_date,
2650 p_calling_object => p_called_from,
2651 p_repository_name => jai_constants.repository_name,
2652 p_repository_id => p_repository_id,
2653 p_reference_name => jai_constants.rgm_trx_refs,
2654 p_reference_id => p_reference_id,
2655 p_currency_code => p_currency_code,
2656 p_curr_conv_date => p_curr_conv_date,
2657 p_curr_conv_type => p_curr_conv_type,
2658 p_curr_conv_rate => p_curr_conv_rate
2659 );
2660
2661 ln_charge_account_id := get_account(
2662 p_regime_id => p_regime_id,
2663 p_organization_type => p_organization_type,
2664 p_organization_id => p_organization_id,
2665 p_location_id => p_location_id,
2666 p_tax_type => p_tax_type,
2667 p_account_name => lv_balancing_accnt_name
2668 );
2669
2670 IF ln_charge_account_id IS NULL THEN
2671 lv_codepath := jai_general_pkg.plot_codepath(21.10, lv_codepath);
2672 p_process_flag := jai_constants.expected_error;
2673 p_process_message := lv_balancing_accnt_name||' Account is not defined in '||p_source;
2674 FND_FILE.put_line( fnd_file.log, p_process_message);
2675 RETURN;
2676 END IF;
2677
2678 IF lv_charge_entry_type = jai_constants.debit THEN
2679 ln_credit_amt := abs(p_exc_gain_loss_amt);
2680 ln_debit_amt := NULL;
2681 ELSE
2682 ln_credit_amt := NULL;
2683 ln_debit_amt := abs(p_exc_gain_loss_amt);
2684 END IF;
2685 FND_FILE.put_line( fnd_file.log,'1. ln_debit_amt '|| ln_debit_amt ||' ln_credit_amt '|| ln_credit_amt);
2686 post_accounting(
2687 p_regime_code => lv_regime_code,
2688 p_tax_type => p_tax_type,
2689 p_organization_type => p_organization_type,
2690 p_organization_id => p_organization_id,
2691 p_source => p_source,
2692 p_source_trx_type => p_source_trx_type,
2693 p_source_table_name => p_source_table_name,
2694 p_source_document_id => p_source_document_id,
2695 p_code_combination_id => ln_charge_account_id,
2696 p_entered_cr => ln_credit_amt,
2697 p_entered_dr => ln_debit_amt,
2698 p_accounted_cr => ln_credit_amt,
2699 p_accounted_dr => ln_debit_amt,
2700 p_accounting_date => p_accounting_date,
2701 p_transaction_date => ld_transaction_date,
2702 p_calling_object => p_called_from,
2703 p_repository_name => jai_constants.repository_name,
2704 p_repository_id => p_repository_id,
2705 p_reference_name => jai_constants.rgm_trx_refs,
2706 p_reference_id => p_reference_id,
2707 p_currency_code => p_currency_code,
2708 p_curr_conv_date => p_curr_conv_date,
2709 p_curr_conv_type => p_curr_conv_type,
2710 p_curr_conv_rate => p_curr_conv_rate
2711 );
2712
2713 p_process_flag := jai_constants.successful;
2714 p_process_message := 'Successful';
2715
2716 FND_FILE.put_line( fnd_file.log, 'End of jai_rgm_trx_recording_pkg.exc_gain_loss_accounting procedure');
2717
2718 END exc_gain_loss_accounting;
2719
2720 END jai_cmn_rgm_recording_pkg;
[Home] [Help]