1 PACKAGE BODY jai_cmn_rgm_recording_pkg AS
2 /* $Header: jai_cmn_rgm_rec.plb 120.14 2008/01/25 14:46:28 rchandan 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
123 Future Dependencies For the release Of this Object:-
124 (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/
125 A datamodel change )
126 jai_rgm_trx_recording_pkg_b.sql
127 ----------------------------------------------------------------------------------------------------------------------------------------------------
128 Current Version Current Bug Dependent Files Version Author Date Remarks
129 Of File On Bug/Patchset Dependent On
130
131
132 115.1 4204880 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
133
134 jai_rgm_process_ar_taxes_pkg_s.sql 115.1 aiyer 21-feb-05 signature change in parameters.
135 jai_rgm_process_ar_taxes_pkg_b.sql 115.1 aiyer 21-feb-05 signature change in parameters.
136
137 115.3 4245089 IN60106 + 4146708 ja_in_alter_table_4204880.sql 115.0 ssumaith 20-feb-05 New column added to the table.
138 + 4204880
139
140
141
142
143 10. 01-MAR-2007 SSAWANT , File version 120.5
144 Forward porting the change in 11.5 bug 5642053 to R12 bug no 5662296.
145
146 Issue : PROCESSING SETTLEMENT (INDIA LOCAL) ON THE CURRENT DATE AND AT ORG LEVEL
147 Fix : Previously whenever transaction_date was less than or equal to last_settlement_date
148 it was modified to last_settlement_date + 1. Now this would be done only if
149 transaction_date is less than last_settlement_date as transactions can be
150 done on last_settlement_date.
151 11. 03-MAR-07 bduvarag, File version 120.5
152 Forward porting the change in 11.5 bug 5051541 to R12 bug
153
154
155 12 25-April-2007 ssawant for bug 5879769 ,File version 120.6
156 Forward porting of
157 ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION from 11.5( bug no 5694855) to R12 (bug no 5879769).
158 Fix : A new parameter p_service_type_code is added to insert_repository procedure.
159 This is used to insert into jai_rgm_trx_records.
160 A new column repository_id is added to jai_sla_entries and so the insert statement
161 is modified to insert the repository id
162 The procedure get_account is modified to return the account if the regime is SERVICE, Org Type is IO and the
163 tax is not of EXCISE or EXCISE CESS types
164 13 02/05/2006 vkaranam bug#5989740 - File version 120.8
165
166 Forward porting of 115 bug #5907436
167 ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
168
169 additional cess of 1% on all taxes to be levied to fund secondary education and higher
170 education .
171
172 Code Changes - Cursor c_orgn_sh_cess_account is added to get code_combination_id for secondary and higher cess types .
173
174 14. 07/06/2007 sacsethi for bug 6109941
175 R12RUP03-ST1: CODE REVIEW COMMENTS FOR ENHANCEMENTS
176
177 Problem - when we trying to get code combination id for discount in AP , then we were passing
178 organization id but we defined code conbination id at OU Level in AP ,
179 wheich was resulting in error
180
181 Solution - 1. Now passing ln_org_id instead of organziation_id for discounts .
182 2. procedure post_accounting is changed to return if both credit and debit amount is zero
183 instead of generating oracle error.
184
185 15. 27/06/2007 CSahoo for bug#6155839, File Version 120.11
186 added the lv_source_name variable to get the service tax source or vat source depending on the value of the regime.
187
188 16. 07/12/2007 ssumaith - bug# 6664855 - file version - 120.3.12000000.5.
189 Issue :-
190 When service tax distribution is done between two inventory organizations,
191 it was causing the unbalances gl entries.
192 Reason being - organziation id was inserted in the reference1 column of gl_interface table.
193 The organization id was entered as source orgn id for source org entries and destination orgn id
194 for des orgn entries as a result, there was only debits or credits for one orgn because referenc1 column
195 is also in the used in the grouping logic.
196
197 Fix:
198
199 This was a forward port issue of the R11i bug# 5410587.
200 It has been forward ported.
201 Changes done are to pass the combination of source org and destination org into reference1 column.
202
203 ----------------------------------------------------------------------------------------------------------------------------*/
204
205
206 /* ~~~~~~~~~~~~~~~ Start of Repository Entry ~~~~~~~~~~~~~~~~~ */
207 PROCEDURE insert_repository_entry(
208 p_repository_id OUT NOCOPY NUMBER,
209 p_regime_id IN NUMBER,
210 p_tax_type IN VARCHAR2,
211 p_organization_type IN VARCHAR2,
212 p_organization_id IN NUMBER,
213 p_location_id IN NUMBER,
214 p_source IN VARCHAR2,
215 p_source_trx_type IN VARCHAR2,
216 p_source_table_name IN VARCHAR2,
217 p_source_document_id IN NUMBER,
218 p_transaction_date IN DATE,
219 p_account_name IN VARCHAR2,
220 p_charge_account_id IN NUMBER,
221 p_balancing_account_id IN NUMBER,
222 p_amount IN OUT NOCOPY NUMBER, -- Recovered/Liable Service Tax Amount in INR Currency i.e functional
223 p_assessable_value IN NUMBER,
224 p_tax_rate IN NUMBER,
225 p_reference_id IN NUMBER,
226 p_batch_id IN NUMBER,
227 p_called_from IN VARCHAR2,
228 p_process_flag OUT NOCOPY VARCHAR2,
229 p_process_message OUT NOCOPY VARCHAR2,
230 p_discounted_amount IN OUT NOCOPY NUMBER,
231 p_inv_organization_id IN NUMBER DEFAULT NULL,
232 p_settlement_id IN NUMBER DEFAULT NULL,
233 -- Following all parameters are required for GL Accounting if p_balancing_account_id value is not passed to this procedure call
234 p_accntg_required_flag IN VARCHAR2, -- DEFAULT jai_constants.yes File.Sql.35 by Brathod
235 p_accounting_date IN DATE , -- DEFAULT sysdate File.Sql.35 by Brathod
236 p_balancing_orgn_type IN VARCHAR2 DEFAULT NULL,
237 p_balancing_orgn_id IN NUMBER DEFAULT NULL,
238 p_balancing_location_id IN NUMBER DEFAULT NULL,
239 p_balancing_tax_type IN VARCHAR2 DEFAULT NULL,
240 p_balancing_accnt_name IN VARCHAR2 DEFAULT NULL,
241 p_currency_code IN VARCHAR2 , -- DEFAULT jai_constants.func_curr File.Sql.35 by Brathod
242 p_curr_conv_date IN VARCHAR2 DEFAULT NULL,
243 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
244 p_curr_conv_rate IN VARCHAR2 DEFAULT NULL,
245 p_trx_amount IN NUMBER DEFAULT NULL, -- recovered/liable service tax amount in foreign currency
246 p_attribute_context IN VARCHAR2 DEFAULT NULL,
247 p_attribute1 IN VARCHAR2 DEFAULT NULL,
248 p_attribute2 IN VARCHAR2 DEFAULT NULL,
249 p_attribute3 IN VARCHAR2 DEFAULT NULL,
250 p_attribute4 IN VARCHAR2 DEFAULT NULL,
251 p_attribute5 IN VARCHAR2 DEFAULT NULL,
252 p_service_type_code IN VARCHAR2 DEFAULT NULL/* added by ssawant for bug 5989740 */
253 ) IS
254
255 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
256 ln_credit NUMBER;
257 ln_debit NUMBER;
258 ln_trx_credit NUMBER;
259 ln_trx_debit NUMBER;
260
261 lv_register_entry_type VARCHAR2(2);
262 lv_account_name JAI_RGM_TRX_RECORDS.account_name%TYPE;
263 ln_charge_account_id JAI_RGM_TRX_RECORDS.charge_account_id%TYPE;
264 lv_charge_entry_type VARCHAR2(2);
265 lv_period_name GL_PERIODS.period_name%TYPE;
266
267 lv_balancing_tax_type JAI_RGM_TRX_RECORDS.tax_type%TYPE;
268 lv_balancing_orgn_type JAI_RGM_TRX_RECORDS.organization_type%TYPE;
269 ln_balancing_orgn_id JAI_RGM_TRX_RECORDS.organization_id%TYPE;
270 ln_balancing_location_id JAI_RGM_TRX_RECORDS.location_id%TYPE;
271 lv_balancing_accnt_name JAI_RGM_TRX_RECORDS.account_name%TYPE;
272 ln_balancing_account_id JAI_RGM_TRX_RECORDS.charge_account_id%TYPE;
273 lv_balancing_entry_type VARCHAR2(2);
274 lv_bal_entry_period_name GL_PERIODS.period_name%TYPE;
275
276 ln_trx_tax_amount NUMBER;
277 lv_reference_name VARCHAR2(30);
278 ln_reference_id NUMBER;
279 lv_statement VARCHAR2(4);
280
281 ln_discount_ccid NUMBER(15);
282 ln_disc_credit NUMBER;
283 ln_disc_debit NUMBER;
284
285 lv_codepath VARCHAR2(500); -- := '' File.Sql.35 by Brathod
286 ln_trx_amount NUMBER;
287 ld_transaction_date DATE;
288 ld_last_settlement_date DATE;
289
290 ln_earned_discount NUMBER;
291 ln_earned_disc_accnt NUMBER;
292 ln_unearned_discount NUMBER;
293 ln_unearned_disc_accnt NUMBER;
294
295 /*
296 Following cursor added by ssumaith for fetching the primary registration number of the regime
297 It will be inserted into jai_Rgm_trx_records table.
298 Bug# 4204880
299 */
300
301 CURSOR c_primary_regno( p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE ) IS --rchandan for bug#4428980
302 SELECT attribute_value
303 FROM JAI_RGM_ORG_REGNS_V
304 WHERE regime_id = p_regime_id
305 AND organization_id = p_organization_id
306 AND organization_type = p_organization_type
307 AND registration_type = jai_constants.regn_type_others
308 AND attribute_Type_code = p_att_type_code; --rchandan for bug#4428980
309
310 lv_primary_regime_regno JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
311
312
313 /*Cursor added by ssawant for bug 5989740 */
314 CURSOR cur_fetch_ou(cp_organization_id NUMBER)
315 IS
316 SELECT org_information3
317 FROM hr_organization_information
318 WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
319 AND organization_id = cp_organization_id;
320
321 ln_org_id NUMBER; /* added by ssawant for bug 5989740 */
322
323 BEGIN
324
325 lv_statement := '0';
326 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'Insert_Repository_Entry', 'START');
327
328 OPEN c_regime_code(p_regime_id);
329 FETCH c_regime_code INTO lv_regime_code;
330 CLOSE c_regime_code;
331
332 /* added by ssawant for bug 5989740 */
333 OPEN cur_fetch_ou(p_organization_id);
334 FETCH cur_fetch_ou INTO ln_org_id;
335 CLOSE cur_fetch_ou;
336
337 lv_statement := '1';
338 -- REGIME Validation
339 IF lv_regime_code <> jai_constants.service_regime THEN
340 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
341 p_process_flag := jai_constants.expected_error;
342 p_process_message := 'Transactions other than SERVICE regime are not supported';
343 FND_FILE.put_line( FND_FILE.log, p_process_message);
344 fnd_file.put_line(fnd_file.log,p_process_message);
345 RETURN;
346 END IF;
347
348 -- Rounding of Service Tax that is hitting repository
349 p_amount := round(p_amount, jai_constants.service_rgm_rnd_factor);
350 p_discounted_amount := round(p_discounted_amount, jai_constants.service_rgm_rnd_factor);
351 IF p_trx_amount = 0 OR p_trx_amount IS NULL THEN
352 ln_trx_amount := NULL;
353 ELSE
354 ln_trx_amount := round(p_trx_amount, jai_constants.service_rgm_rnd_factor);
355 END IF;
356
357 lv_statement := '2';
358 IF p_source IN (jai_constants.source_settle_in, jai_constants.source_settle_out) THEN
359 ld_transaction_date := p_transaction_date;
360 lv_codepath := jai_general_pkg.plot_codepath(2.1, lv_codepath);
361
362 ELSE
363 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 */
364 IF ld_last_settlement_date > p_transaction_date THEN /*for bug 5662296 ,org_settlement. Replaced >= with >*/
365 ld_transaction_date := ld_last_settlement_date + 1;
366 lv_codepath := jai_general_pkg.plot_codepath(2.2, lv_codepath);
367 ELSIF ld_last_settlement_date IS NULL or ld_last_settlement_date <= p_transaction_date THEN /* for bug 5662296 , org_settlement. Replaced < with <=*/
368 ld_transaction_date := p_transaction_date;
369 lv_codepath := jai_general_pkg.plot_codepath(2.3, lv_codepath);
370 END IF;
371 END IF;
372
373 lv_statement := '2.1';
374 -- ~~~~~~~~~~~~~~~~~~~~~~~ Start of Repository Entry ~~~~~~~~~~~~~~~~~~~
375
376 IF p_source = jai_constants.source_ap THEN
377 lv_statement := '3';
378 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
379 lv_register_entry_type := jai_constants.credit;
380 lv_account_name := jai_constants.recovery;
381 lv_charge_entry_type := jai_constants.debit;
382 lv_balancing_accnt_name := jai_constants.recovery_interim;
383 lv_balancing_entry_type := jai_constants.credit;
384
385 ELSIF p_source = jai_constants.source_ar THEN
386 lv_statement := '4';
387 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath);
388 lv_register_entry_type := jai_constants.debit;
389 lv_account_name := jai_constants.liability;
390 lv_charge_entry_type := jai_constants.credit;
391 lv_balancing_accnt_name := jai_constants.liability_interim;
392 lv_balancing_entry_type := jai_constants.debit;
393
394 ELSIF p_source = jai_constants.source_manual_entry THEN
395 /*No need to Set Balancing Account Name as there is no need to derive balancing account because User ENTERs it in MANUAL
396 ENTRY Form*/
397 lv_statement := '5';
398 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath);
399 lv_account_name := p_account_name;
400 IF lv_account_name IN (jai_constants.recovery, jai_constants.recovery_interim) THEN
401 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath);
402 lv_register_entry_type := jai_constants.credit;
403 lv_charge_entry_type := jai_constants.debit;
404 lv_balancing_entry_type := jai_constants.credit;
405 ELSIF lv_account_name IN (jai_constants.liability, jai_constants.liability_interim) THEN
406 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath);
407 lv_register_entry_type := jai_constants.debit;
408 lv_charge_entry_type := jai_constants.credit;
409 lv_balancing_entry_type := jai_constants.debit;
410 END IF;
411
412 /* Incase of Distributions and settlements, we hit only recovery account and decrease/increase
413 repository amounts as per _OUT/_IN trxns*/
414 ELSIF p_source IN (jai_constants.service_src_distribute_out, jai_constants.source_settle_out) THEN
415 lv_statement := '6';
416 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
417 lv_register_entry_type := jai_constants.debit;
418 /* following is changed as per Shekhars finding. this is because incase of distributions and settlements,
419 we should hit only recovery accounts*/
420 lv_account_name := jai_constants.recovery; -- jai_constants.liability; This is changed as per Shekhars finding
421 lv_charge_entry_type := jai_constants.credit;
422 lv_balancing_accnt_name := jai_constants.recovery;
423 lv_balancing_entry_type := jai_constants.debit;
424
425 ELSIF p_source IN (jai_constants.service_src_distribute_in, jai_constants.source_settle_in) THEN
426 lv_statement := '7';
427 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath);
428 lv_register_entry_type := jai_constants.credit;
429 lv_account_name := jai_constants.recovery;
430 lv_charge_entry_type := jai_constants.debit;
431 lv_balancing_entry_type := jai_constants.credit;
432 lv_balancing_accnt_name := jai_constants.recovery; -- jai_constants.liability;
433 END IF;
434
435 IF lv_register_entry_type = jai_constants.debit THEN
436 lv_statement := '8';
437 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath);
438 ln_debit := p_amount;
439 ln_credit := NULL;
440 ln_trx_debit := nvl(ln_trx_amount, p_amount);
441 ln_trx_credit := null;
442 ELSE
443 lv_statement := '9';
444 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath);
445 ln_debit := NULL;
446 ln_credit := p_amount;
447 ln_trx_debit := null;
448 ln_trx_credit := nvl(ln_trx_amount, p_amount);
449 END IF;
450
451 lv_statement := '13';
452 IF p_charge_account_id IS NULL THEN
453 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath);
454 ln_charge_account_id := get_account(
455 p_regime_id => p_regime_id,
456 p_organization_type => p_organization_type,
457 p_organization_id => p_organization_id,
458 p_location_id => p_location_id,
459 p_tax_type => p_tax_type,
460 p_account_name => lv_account_name
461 );
462 ELSE
463 lv_statement := '14';
464 ln_charge_account_id := p_charge_account_id;
465 END IF;
466
467 lv_statement := '9.1';
468 lv_balancing_orgn_type := p_balancing_orgn_type;
469 ln_balancing_orgn_id := p_balancing_orgn_id;
470 ln_balancing_location_id := p_balancing_location_id;
471 lv_balancing_tax_type := p_balancing_tax_type;
472 lv_balancing_accnt_name := nvl(p_balancing_accnt_name, lv_balancing_accnt_name);
473
474 lv_statement := '12';
475 IF ln_balancing_orgn_id IS NULL THEN
476 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath);
477 lv_balancing_orgn_type := p_organization_type;
478 ln_balancing_orgn_id := p_organization_id;
479 ln_balancing_location_id := p_location_id;
480 lv_balancing_tax_type := p_tax_type;
481 END IF;
482
483 lv_statement := '17';
484 IF p_balancing_account_id IS NULL THEN -- AND lv_balancing_accnt_name IS NOT NULL THEN
485 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
486 ln_balancing_account_id := get_account(
487 p_regime_id => p_regime_id,
488 p_organization_type => lv_balancing_orgn_type,
489 p_organization_id => ln_balancing_orgn_id,
490 p_location_id => ln_balancing_location_id,
491 p_tax_type => lv_balancing_tax_type,
492 p_account_name => lv_balancing_accnt_name
493 );
494 ELSE
495 ln_balancing_account_id := p_balancing_account_id;
496 END IF;
497
498 lv_statement := '10';
499 /*
500 Following cursor added by ssumaith to get the primary registration number - bug# 4204880
501 Added the column regime_primary_regno in the insert column list of the table jai_rgm_trx_records table.
502 */
503 OPEN c_primary_regno('PRIMARY'); --rchandan for bug#4428980
504 FETCH c_primary_regno into lv_primary_regime_regno;
505 CLOSE c_primary_regno;
506
507 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath);
508 INSERT INTO jai_rgm_trx_records(
509 repository_id, regime_code, tax_type, source,
510 source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
511 settled_amount, settled_flag, settlement_id, organization_type,
512 organization_id, location_id, account_name, charge_account_id, balancing_account_id,
513 reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
514 trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
515 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
516 attribute_context, attribute1, attribute2, attribute3, attribute4, attribute5
517 , inv_organization_id, regime_primary_regno ,service_type_code /* added by ssawant for bug 5879769 */
518 ) VALUES (
519 jai_rgm_trx_records_s.nextval, lv_regime_code, p_tax_type, p_source,
520 p_source_document_id, p_source_table_name, ld_transaction_date, ln_debit, ln_credit,
521 null, null, p_settlement_id, p_organization_type,
522 p_organization_id, p_location_id, p_account_name, ln_charge_account_id, ln_balancing_account_id,
523 p_reference_id, p_source_trx_type, p_tax_rate, p_assessable_value, p_batch_id,
524 p_currency_code, p_curr_conv_date, p_curr_conv_rate, ln_trx_credit, ln_trx_debit,
525 sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
526 p_attribute_context, p_attribute1, p_attribute2, p_attribute3, p_attribute4, p_attribute5
527 , p_inv_organization_id , lv_primary_regime_regno ,p_service_type_code /* added by ssawant for bug 5879769 */
528 ) RETURNING repository_id INTO p_repository_id;
529
530 -- ~~~~~~~~~~~~~~~~~~~~~~~ Accounting of Recovered/Liable Service Tax ~~~~~~~~~~~~~~~~~~~~~
531
532 lv_statement := '11';
533 IF p_accntg_required_flag = jai_constants.yes THEN
534
535 lv_statement := '15';
536 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath);
537 IF ln_charge_account_id IS NULL THEN
538 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
539 p_process_flag := jai_constants.expected_error;
540 p_process_message := 'Charge Account('||lv_account_name||') not defined for tax type '||p_tax_type;
541 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
542 GOTO end_of_repository_entry;
543 END IF;
544
545 lv_statement := '16';
546 IF g_debug='Y' THEN
547 fnd_file.put_line(fnd_file.log,'pkg2. rgm_id:'||p_regime_id||',OrgType:'||lv_balancing_orgn_type
548 ||',Oid:'||ln_balancing_orgn_id||',locid:'||ln_balancing_location_id
549 ||',txty:'||lv_balancing_tax_type||',actName:'||lv_balancing_accnt_name
550 );
551 END IF;
552
553 lv_statement := '18';
554 IF ln_balancing_account_id IS NULL THEN
555 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
556 p_process_flag := jai_constants.expected_error;
557 p_process_message := 'Balancing Account('||lv_balancing_accnt_name||') not defined for tax type '||lv_balancing_tax_type;
558 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
559 GOTO end_of_repository_entry;
560 END IF;
561
562 ln_reference_id := p_reference_id;
563 IF p_source IN ( jai_constants.source_ap, jai_constants.source_ar) THEN
564 lv_reference_name := jai_constants.rgm_trx_refs;
565 END IF;
566
567 lv_statement := '19';
568 -- INITIAL_ENTRY
569 IF lv_charge_entry_type = jai_constants.debit THEN
570 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath);
571 ln_debit := p_amount;
572 ln_credit := NULL;
573 ln_trx_debit := nvl(ln_trx_amount, p_amount);
574 ln_trx_credit := null;
575 ELSE
576 ln_debit := NULL;
577 ln_credit := p_amount;
578 ln_trx_debit := null;
579 ln_trx_credit := nvl(ln_trx_amount, p_amount);
580 END IF;
581
582 lv_statement := '20';
583 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
584 -- make a call to post_accounting procedure
585 post_accounting(
586 p_regime_code => lv_regime_code,
587 p_tax_type => p_tax_type,
588 p_organization_type => p_organization_type,
589 p_organization_id => p_organization_id,
590 p_source => p_source,
591 p_source_trx_type => p_source_trx_type,
592 p_source_table_name => p_source_table_name,
593 p_source_document_id => p_source_document_id,
594 p_code_combination_id => ln_charge_account_id,
595 p_entered_cr => ln_trx_credit,
596 p_entered_dr => ln_trx_debit,
597 p_accounted_cr => ln_credit,
598 p_accounted_dr => ln_debit,
599 p_accounting_date => p_accounting_date,
600 p_transaction_date => ld_transaction_date,
601 p_calling_object => p_called_from,
602 p_repository_name => jai_constants.repository_name,
603 p_repository_id => p_repository_id,
604 p_reference_name => lv_reference_name,
605 p_reference_id => ln_reference_id,
606 p_currency_code => p_currency_code,
607 p_curr_conv_date => p_curr_conv_date,
608 p_curr_conv_type => p_curr_conv_type,
609 p_curr_conv_rate => p_curr_conv_rate
610 );
611
612 /* START of DISCOUNT ACCOUNTING */
613 IF nvl(p_discounted_amount, 0) <> 0 THEN
614 -- Discount related code needs to be added here
615 lv_statement := '20.1';
616 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath);
617
618 IF p_source = jai_constants.source_ar THEN
619
620 lv_codepath := jai_general_pkg.plot_codepath(21.1, lv_codepath);
621 jai_ar_rgm_processing_pkg.get_ar_tax_disc_accnt (
622 p_receivable_application_id => p_source_document_id,
623 p_org_id => ln_org_id,/* added by ssawant for bug 5879769 */
624 p_total_disc_amount => p_discounted_amount, /* added by ssumaith - for bug# 4193633*/
625 p_tax_ediscounted => ln_earned_discount,
626 p_earned_disc_ccid => ln_earned_disc_accnt,
627 p_tax_uediscounted => ln_unearned_discount,
628 p_unearned_disc_ccid => ln_unearned_disc_accnt,
629 p_process_flag => p_process_flag,
630 p_process_message => p_process_message
631 );
632
633 IF p_process_flag IN (jai_constants.expected_error, jai_constants.unexpected_error) THEN
634 lv_codepath := jai_general_pkg.plot_codepath(21.2, lv_codepath);
635 -- some problem in the above call
636 RETURN;
637 ELSIF nvl(ln_earned_discount, 0) + nvl(ln_unearned_discount, 0) <> NVL(p_discounted_amount,0) THEN
638 lv_codepath := jai_general_pkg.plot_codepath(21.3, lv_codepath);
639 p_process_flag := jai_constants.expected_error;
640 p_process_message := 'There is a discrepency in earned + unearned = discounted';
641 RETURN;
642 END IF;
643
644 --- following will be used for first accounting entry incase of AR Receipt Application
645 IF nvl(ln_earned_discount,0) <> 0 THEN
646 ln_discount_ccid := ln_earned_disc_accnt;
647 IF lv_charge_entry_type = jai_constants.debit THEN
648 lv_codepath := jai_general_pkg.plot_codepath(21.4, lv_codepath);
649 ln_disc_credit := null;
650 ln_disc_debit := ln_earned_discount;
651 ELSE
652 lv_codepath := jai_general_pkg.plot_codepath(21.5, lv_codepath);
653 ln_disc_credit := ln_earned_discount;
654 ln_disc_debit := null;
655 END IF;
656
657 ELSE
658 ln_disc_credit := null;
659 ln_disc_debit := null;
660 END IF;
661
662 /* following else will be executed for AP Transactions only */
663 ELSE
664
665 ln_discount_ccid := get_account(
666 p_regime_id => null,
667 p_organization_type => p_organization_type,
668 p_organization_id => ln_org_id , -- Date 07/06/2007 by sacsethi for bug 6109941 - changed organization_id to org_id ( ou_level )
669 p_location_id => null,
670 p_tax_type => null,
671 p_account_name => jai_cmn_rgm_recording_pkg.ap_discount_accnt
672 );
673
674 IF ln_discount_ccid IS NULL THEN
675 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
676 p_process_flag := jai_constants.expected_error;
677 p_process_message := 'Discount Account is not defined in '||p_source;
678 FND_FILE.put_line( FND_FILE.log, p_process_message); fnd_file.put_line(fnd_file.log,p_process_message);
679 FND_FILE.put_line( FND_FILE.log, ln_org_id);
680 GOTO end_of_repository_entry;
681 END IF;
682
683 IF lv_charge_entry_type = jai_constants.debit THEN
684 ln_disc_credit := null;
685 ln_disc_debit := p_discounted_amount;
686 ELSE
687 ln_disc_debit := null;
688 ln_disc_credit := p_discounted_amount;
689 END IF;
690
691 END IF;
692
693 IF ln_disc_debit IS NOT NULL OR ln_disc_credit IS NOT NULL THEN
694 lv_codepath := jai_general_pkg.plot_codepath(21.6, lv_codepath);
695 -- make a call to post_accounting procedure
696 post_accounting(
697 p_regime_code => lv_regime_code,
698 p_tax_type => p_tax_type,
699 p_organization_type => p_organization_type,
700 p_organization_id => p_organization_id,
701 p_source => p_source,
702 p_source_trx_type => p_source_trx_type,
703 p_source_table_name => p_source_table_name,
704 p_source_document_id => p_source_document_id,
705 p_code_combination_id => ln_discount_ccid,
706 p_entered_cr => ln_disc_credit,
707 p_entered_dr => ln_disc_debit,
708 p_accounted_cr => ln_disc_credit,
709 p_accounted_dr => ln_disc_debit,
710 p_accounting_date => p_accounting_date,
711 p_transaction_date => ld_transaction_date,
712 p_calling_object => p_called_from,
713 p_repository_name => jai_constants.repository_name,
714 p_repository_id => p_repository_id,
715 p_reference_name => lv_reference_name,
716 p_reference_id => ln_reference_id,
717 p_currency_code => p_currency_code,
718 p_curr_conv_date => p_curr_conv_date,
719 p_curr_conv_type => p_curr_conv_type,
720 p_curr_conv_rate => p_curr_conv_rate
721 );
722
723 END IF;
724
725 -- following entry will happen only in case of AR Transactions
726 IF nvl(ln_unearned_discount,0) <> 0 THEN
727 lv_codepath := jai_general_pkg.plot_codepath(21.7, lv_codepath);
728 ln_discount_ccid := ln_unearned_disc_accnt;
729 IF lv_charge_entry_type = jai_constants.debit THEN
730 ln_disc_credit := null;
731 ln_disc_debit := ln_unearned_discount;
732 ELSE
733 ln_disc_credit := ln_unearned_discount;
734 ln_disc_debit := null;
735 END IF;
736
737 post_accounting(
738 p_regime_code => lv_regime_code,
739 p_tax_type => p_tax_type,
740 p_organization_type => p_organization_type,
741 p_organization_id => p_organization_id,
742 p_source => p_source,
743 p_source_trx_type => p_source_trx_type,
744 p_source_table_name => p_source_table_name,
745 p_source_document_id => p_source_document_id,
746 p_code_combination_id => ln_discount_ccid,
747 p_entered_cr => ln_disc_credit,
748 p_entered_dr => ln_disc_debit,
749 p_accounted_cr => ln_disc_credit,
750 p_accounted_dr => ln_disc_debit,
751 p_accounting_date => p_accounting_date,
752 p_transaction_date => ld_transaction_date,
753 p_calling_object => p_called_from,
754 p_repository_name => jai_constants.repository_name,
755 p_repository_id => p_repository_id,
756 p_reference_name => lv_reference_name,
757 p_reference_id => ln_reference_id,
758 p_currency_code => p_currency_code,
759 p_curr_conv_date => p_curr_conv_date,
760 p_curr_conv_type => p_curr_conv_type,
761 p_curr_conv_rate => p_curr_conv_rate
762 );
763
764 END IF;
765
766 lv_codepath := jai_general_pkg.plot_codepath(21.8, lv_codepath);
767 END IF;
768 /* END of DISCOUNT ACCOUNTING */
769
770
771 lv_statement := '21';
772 -- BALANCING_ENTRY
773 IF lv_balancing_entry_type = jai_constants.debit THEN
774 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath);
775 ln_debit := p_amount + nvl(p_discounted_amount,0);
776 ln_credit := NULL;
777 ln_trx_debit := nvl(ln_trx_amount, p_amount)+ nvl(p_discounted_amount,0);
778 ln_trx_credit := null;
779 ELSE
780 ln_debit := NULL;
781 ln_credit := p_amount+ nvl(p_discounted_amount,0);
782 ln_trx_debit := null;
783 ln_trx_credit := nvl(ln_trx_amount, p_amount)+ nvl(p_discounted_amount,0);
784 END IF;
785
786 lv_statement := '22';
787 -- make a call to post_accounting procedure
788 post_accounting(
789 p_regime_code => lv_regime_code,
790 p_tax_type => p_tax_type,
791 p_organization_type => lv_balancing_orgn_type,
792 p_organization_id => ln_balancing_orgn_id,
793 p_source => p_source,
794 p_source_trx_type => p_source_trx_type,
795 p_source_table_name => p_source_table_name,
796 p_source_document_id => p_source_document_id,
797 p_code_combination_id => ln_balancing_account_id,
798 p_entered_cr => ln_trx_credit, -- TRANSACTION_CURR
799 p_entered_dr => ln_trx_debit,
800 p_accounted_cr => ln_credit, -- FUNC_CURR
801 p_accounted_dr => ln_debit,
802 p_accounting_date => p_accounting_date,
803 p_transaction_date => ld_transaction_date,
804 p_calling_object => p_called_from,
805 p_repository_name => jai_constants.repository_name,
806 p_repository_id => p_repository_id,
807 p_reference_name => lv_reference_name,
808 p_reference_id => ln_reference_id,
809 p_currency_code => p_currency_code,
810 p_curr_conv_date => p_curr_conv_date,
811 p_curr_conv_type => p_curr_conv_type,
812 p_curr_conv_rate => p_curr_conv_rate
813 );
814
815 END IF;
816
817 lv_statement := '23';
818 p_process_flag := jai_constants.successful;
819 p_process_message := 'Successful';
820
821 lv_statement := '24';
822 <<end_of_repository_entry>>
823 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath, 'Insert_Repository_entry', 'END');
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 p_process_flag := jai_constants.unexpected_error;
828 p_process_message := 'Repository Error(Stmt:'||lv_statement||') Occured:'||SQLERRM;
829 lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
830 Fnd_file.put_line( fnd_file.log, 'Error in Insert_Repository_entry. Codepath:'||lv_codepath);
831
832 END insert_repository_entry;
833
834 /* ~~~~~~~~~~~~~~~ Start of Accounting Entry Procedure ~~~~~~~~~~~~~~~~~ */
835 PROCEDURE post_accounting(
836 p_regime_code IN VARCHAR2,
837 p_tax_type IN VARCHAR2,
838 p_organization_type IN VARCHAR2,
839 p_organization_id IN NUMBER,
840 p_source IN VARCHAR2,
841 p_source_trx_type IN VARCHAR2,
842 p_source_table_name IN VARCHAR2,
843 p_source_document_id IN NUMBER,
844 p_code_combination_id IN NUMBER,
845 -- Transaction Currency Amount
846 p_entered_cr IN NUMBER,
847 p_entered_dr IN NUMBER,
848 -- Functional Currency Amount
849 p_accounted_cr IN NUMBER,
850 p_accounted_dr IN NUMBER,
851 p_accounting_date IN DATE,
852 p_transaction_date IN DATE,
853 p_calling_object IN VARCHAR2,
854 p_repository_name IN VARCHAR2 DEFAULT NULL,
855 p_repository_id IN NUMBER DEFAULT NULL,
856 p_reference_name IN VARCHAR2 DEFAULT NULL,
857 p_reference_id IN NUMBER DEFAULT NULL,
858 p_currency_code IN VARCHAR2 DEFAULT NULL,
859 p_curr_conv_date IN DATE DEFAULT NULL,
860 p_curr_conv_type IN VARCHAR2 DEFAULT NULL,
861 p_curr_conv_rate IN NUMBER DEFAULT NULL
862 ) IS
863
864 /* Added by Ramananda for bug#4407165 */
865 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.post_accounting';
866
867 lv_reference10 GL_INTERFACE.reference10%type;
868 lv_reference23 GL_INTERFACE.reference23%type;
869 lv_reference24 GL_INTERFACE.reference24%type;
870 lv_reference25 GL_INTERFACE.reference25%type;
871 lv_reference26 GL_INTERFACE.reference26%type;
872
873 ld_accounting_date DATE;
874 lv_message VARCHAR2(100);
875
876 lv_period_name GL_PERIODS.period_name%TYPE;
877 ln_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
878 ln_currency_precision FND_CURRENCIES.precision%TYPE;
879
880 ln_entered_dr NUMBER;
881 ln_entered_cr NUMBER;
882 ln_accounted_dr NUMBER;
883 ln_accounted_cr NUMBER;
884
885 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
886 lv_reference_name VARCHAR2(30);
887 ln_reference_id NUMBER(15);
888 lv_gl_je_category varchar2(30); --File.Sql.35 Cbabu jai_constants.je_category_rg_entry%type;
889 lv_status gl_interface.status%TYPE ; --rchandan for bug#4428980
890 lv_source_name VARCHAR2(30); -- modified by csahoo for bug#6155839
891 /*Begin-Added the following by kunkumar for forward porting 5051541 to R12*/
892
893
894 lv_organization_code mtl_parameters.organization_code%TYPE;
895
896 CURSOR c_organization_code(cp_organization_id IN NUMBER)
897 IS
898 SELECT organization_code
899 FROM mtl_parameters
900 WHERE organization_id = cp_organization_id;
901
902 -- added, ssumaith for Bug 6664855
903
904 Cursor c_get_source_info(cp_transfer_id NUMBER)
905 IS
906 select party_id
907 from jai_rgm_dis_src_hdrs
908 where transfer_id = cp_transfer_id ;
909
910
911 Cursor c_get_dest_info(cp_transfer_id NUMBER)
912 IS
913 select destination_party_id
914 from jai_rgm_dis_des_hdrs
915 where transfer_id = cp_transfer_id ;
916
917 Cursor c_get_source(cp_repository_id NUMBER)
918 IS
919 select source
920 from jai_rgm_trx_records
921 where repository_id = cp_repository_id ;
922
923
924 lv_source jai_rgm_trx_records.source%TYPE ;
925 lv_src_party_id jai_rgm_dis_src_hdrs.party_id%TYPE ;
926 lv_reference1 gl_interface.reference1%TYPE ;
927 ln_loop_cnt NUMBER;
928
929 -- end, ssumaith for Bug 6664855
930
931 BEGIN
932 --Bug 5051541 bduvarag
933 jai_cmn_utils_pkg.print_log('6395039.log', 'Start of post_accounting');
934
935 -- added, ssumaith for Bug 6664855
936 lv_reference1 := null ;
937
938 OPEN c_get_source(p_repository_id) ;
939 FETCH c_get_source INTO lv_source ;
940 CLOSE c_get_source ;
941
942 /* Reference column should be populated with same value for a set of Journals that are passed as part
943 of a transaction, else Journal Import will fail with EUXX error.
944 Prior to this fix, incase of Distribution of duty from one orgn. to other, reference1 is getting populated
945 with different values for different Journal. Hence the following logic of the IF condition is used to derive the
946 value for reference1 for all the Journals that are part of the Distribution (Service tax or any duty distribution
947 */
948
949 IF lv_source IN (jai_constants.service_src_distribute_in, jai_constants.service_src_distribute_out,
950 'DISTRIBUTE_IN', 'DISTRIBUTE_OUT')
951 THEN
952 lv_reference1 := '' ;
953
954 OPEN c_get_source_info(p_source_document_id) ;
955 FETCH c_get_source_info INTO lv_src_party_id ;
956 CLOSE c_get_source_info ;
957
958 lv_reference1 := to_char(lv_src_party_id)||'->';
959 ln_loop_cnt := 1;
960 FOR rec IN c_get_dest_info(p_source_document_id)
961 LOOP
962 if ln_loop_cnt > 1 then
963 lv_reference1 := lv_reference1 || ',';
964 end if;
965 lv_reference1 := lv_reference1 || to_char(rec.destination_party_id);
966 ln_loop_cnt := ln_loop_cnt + 1;
967 END LOOP ;
968
969 ELSE
970 OPEN c_organization_code(p_organization_id);
971 FETCH c_organization_code INTO lv_organization_code;
972 CLOSE c_organization_code;
973 lv_reference1 := lv_organization_code ;
974 END IF ;
975
976 -- ended, ssumaith for Bug 6664855
977
978 /* following condition introduced for VAT Impl. Vijay Shankar for Bug#4250236(4245089) */
979 IF p_regime_code = jai_constants.service_regime THEN
980 lv_reference10 := 'Service Tax Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~');
981 lv_source_name := jai_constants.service_tax_source; -- added by csahoo for bug#6155839
982 ELSIF p_regime_code = jai_constants.vat_regime THEN
983 lv_reference10 := 'VAT Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~');
984 lv_source_name := jai_constants.vat_source; -- modified by csahoo for bug#6155839
985 jai_cmn_utils_pkg.print_log('6395039.log', lv_reference10);
986 END IF;
987
988 ld_accounting_date := nvl( trunc(p_accounting_date), trunc(sysdate) );
989
990 IF p_code_combination_id IS NULL THEN
991 lv_message := 'Account not given';
992 RAISE_APPLICATION_ERROR( -20011, lv_message);
993 END IF;
994
995 ln_currency_precision := jai_general_pkg.get_currency_precision(null); -- CURRENCY is INR
996
997 -- Use of Currency Precision to round off the values when posting to GL is mandatory thing
998 ln_entered_dr := round(p_entered_dr, ln_currency_precision);
999 ln_entered_cr := round(p_entered_cr, ln_currency_precision);
1000 ln_accounted_dr := round(p_accounted_dr, ln_currency_precision);
1001 ln_accounted_cr := round(p_accounted_cr, ln_currency_precision);
1002
1003 IF ( nvl(ln_entered_dr, 0) = 0 AND nvl(ln_entered_cr,0) = 0
1004 OR nvl(ln_accounted_dr, 0) = 0 AND nvl(ln_accounted_cr,0) = 0 )
1005 THEN
1006
1007 -- Date 07-jun-2007 by sacsethi for bug 6109941
1008 -- Previously we were generating raise application error which is changed
1009 -- to information level ....
1010
1011 FND_FILE.put_line( FND_FILE.log, 'Accounting not done as Both Credit and Debit are Zero ');
1012 RETURN ;
1013
1014 END IF;
1015 jai_cmn_utils_pkg.print_log('6395039.log', 'before call to get_period_name');
1016 get_period_name(
1017 p_organization_type => p_organization_type,
1018 p_organization_id => p_organization_id,
1019 p_accounting_date => ld_accounting_date,
1020 p_period_name => lv_period_name,
1021 p_sob_id => ln_sob_id
1022 );
1023 jai_cmn_utils_pkg.print_log('6395039.log', 'after call to get_period_name');
1024
1025 /* following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1026 lv_gl_je_category := jai_constants.je_category_rg_entry;
1027 lv_status := 'NEW';--rchandan for bug#4428980
1028 jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot gl_interface');
1029 INSERT INTO gl_interface (
1030 status, set_of_books_id, user_je_source_name, user_je_category_name,
1031 accounting_date, currency_code, date_created, created_by,
1032 actual_flag, entered_cr, entered_dr, accounted_cr, accounted_dr, transaction_date,
1033 code_combination_id, currency_conversion_date, user_currency_conversion_type, currency_conversion_rate,
1034 reference10, reference22, reference23, reference1,
1035 reference24, reference25, reference26, reference27
1036 ) VALUES (
1037 lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
1038 ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
1039 'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
1040 p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
1041 lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
1042 -- commented lv_organization_code and passed refererence1 ssumaith bug#6664855
1043 --Bug 5051541 kunkumar
1044 p_source_table_name, p_source_document_id, p_repository_name, p_organization_id
1045 );
1046 jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot gl_interface');
1047 IF p_reference_id IS NOT NULL OR p_reference_name IS NOT NULL THEN
1048 lv_reference_name := p_reference_name;
1049 ln_reference_id := p_reference_id;
1050 ELSE
1051 lv_reference_name := p_repository_name;
1052 ln_reference_id := p_repository_id;
1053 END IF;
1054 jai_cmn_utils_pkg.print_log('6395039.log', 'before insert inot JAI_CMN_JOURNAL_ENTRIES');
1055 INSERT INTO JAI_CMN_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
1056 regime_code, organization_id, set_of_books_id, tax_type, period_name,
1057 code_combination_id, accounted_dr, accounted_cr, transaction_date,
1058 source, source_table_name, source_trx_id, reference_name, reference_id, repository_id,/* added by ssawant for bug 5879769 */
1059 currency_code, curr_conv_rate, creation_date, created_by, last_update_date, last_updated_by, last_update_login
1060 ) VALUES ( JAI_CMN_JOURNAL_ENTRIES_S.nextval,
1061 p_regime_code, p_organization_id, ln_sob_id, p_tax_type, lv_period_name,
1062 p_code_combination_id, ln_accounted_dr, ln_accounted_cr, p_transaction_date,
1063 p_source, p_source_table_name, p_source_document_id, p_reference_name, p_reference_id,p_repository_id,/* added by ssawant for bug 5879769 */
1064 p_currency_code, p_curr_conv_rate, sysdate, FND_GLOBAL.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
1065 );
1066
1067 jai_cmn_utils_pkg.print_log('6395039.log', 'after insert inot JAI_CMN_JOURNAL_ENTRIES');
1068 /* Added by Ramananda for bug#4407165 */
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1072 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1073 jai_cmn_utils_pkg.print_log('6395039.log', 'exception occured'||sqlerrm);
1074 app_exception.raise_exception;
1075
1076 END post_accounting;
1077
1078 PROCEDURE insert_reference(
1079 p_reference_id OUT NOCOPY NUMBER,
1080 p_organization_id IN NUMBER, /* Operating Unit */
1081 p_source IN VARCHAR2,
1082 p_invoice_id IN NUMBER,
1083 p_line_id IN NUMBER,
1084 p_tax_type IN VARCHAR2,
1085 p_tax_id IN NUMBER,
1086 p_tax_rate IN NUMBER,
1087 p_recoverable_ptg IN NUMBER,
1088 p_party_type IN VARCHAR2,
1089 p_party_id IN NUMBER,
1090 p_party_site_id IN NUMBER,
1091 p_trx_tax_amount IN NUMBER,
1092 p_trx_currency IN VARCHAR2,
1093 p_curr_conv_date IN DATE,
1094 p_curr_conv_rate IN NUMBER,
1095 p_tax_amount IN NUMBER,
1096 p_recoverable_amount IN NUMBER,
1097 p_recovered_amount IN NUMBER,
1098 p_item_line_id IN NUMBER,
1099 p_item_id IN NUMBER,
1100 p_taxable_basis IN NUMBER,
1101 p_parent_reference_id IN NUMBER,
1102 p_reversal_flag IN VARCHAR2,
1103 p_batch_id IN NUMBER,
1104 p_process_flag OUT NOCOPY VARCHAR2,
1105 p_process_message OUT NOCOPY VARCHAR2
1106 /* Location_Id Required for VAT??? */
1107 ) IS
1108
1109 BEGIN
1110
1111 INSERT INTO jai_rgm_trx_refs(
1112 reference_id,
1113 organization_id,
1114 source,
1115 invoice_id,
1116 line_id,
1117 tax_type,
1118 tax_id,
1119 tax_rate,
1120 recoverable_ptg,
1121 trx_tax_amount,
1122 trx_currency,
1123 curr_conv_date,
1124 curr_conv_rate,
1125 tax_amount,
1126 recoverable_amount,
1127 recovered_amount,
1128 taxable_basis,
1129 party_type,
1130 party_id,
1131 party_site_id,
1132 item_line_id,
1133 item_id,
1134 parent_reference_id,
1135 reversal_flag,
1136 batch_id,
1137 creation_date,
1138 created_by,
1139 last_update_date,
1140 last_updated_by,
1141 last_update_login
1142 ) VALUES (
1143 jai_rgm_trx_refs_s.nextval,
1144 p_organization_id,
1145 p_source,
1146 p_invoice_id,
1147 p_line_id,
1148 p_tax_type,
1149 p_tax_id,
1150 p_tax_rate,
1151 p_recoverable_ptg,
1152 p_trx_tax_amount,
1153 p_trx_currency,
1154 p_curr_conv_date,
1155 p_curr_conv_rate,
1156 p_tax_amount,
1157 p_recoverable_amount,
1158 p_recovered_amount,
1159 p_taxable_basis,
1160 p_party_type,
1161 p_party_id,
1162 p_party_site_id,
1163 p_item_line_id,
1164 p_item_id,
1165 p_parent_reference_id,
1166 p_reversal_flag,
1167 p_batch_id,
1168 sysdate,
1169 fnd_global.user_id,
1170 sysdate,
1171 fnd_global.user_id,
1172 fnd_global.login_id
1173 ) RETURNING reference_id INTO p_reference_id;
1174
1175 p_process_flag := jai_constants.successful;
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 p_process_flag := jai_constants.unexpected_error;
1180 p_process_message := 'jai_cmn_rgm_recording_pkg.insert_reference failed with error - '||SQLERRM;
1181 fnd_file.put_line( fnd_file.log, p_process_message);
1182 END insert_reference;
1183
1184 FUNCTION get_account(
1185 p_regime_id IN NUMBER,
1186 p_organization_type IN VARCHAR2,
1187 p_organization_id IN NUMBER,
1188 p_location_id IN NUMBER,
1189 p_tax_type IN VARCHAR2,
1190 p_account_name IN VARCHAR2
1191 ) RETURN NUMBER IS
1192
1193 /* Added by Ramananda for bug#4407165 */
1194 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_account';
1195
1196 CURSOR c_orgn_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1197 SELECT decode(cp_register_type,
1198 jai_constants.register_type_a, modvat_rm_account_id,
1199 jai_constants.register_type_c, modvat_cg_account_id,
1200 jai_constants.register_type_pla, modvat_pla_account_id
1201 )
1202 FROM JAI_CMN_INVENTORY_ORGS a
1203 WHERE organization_id = cp_organization_id
1204 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1205 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1206 );
1207
1208 /*
1209 || Cursor added by Ramananda
1210 || Start of Bug#4546114
1211 */
1212 CURSOR c_orgn_cess_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1213 SELECT decode(cp_register_type,
1214 jai_constants.register_type_a, excise_edu_cess_rm_account ,
1215 jai_constants.register_type_c, excise_edu_cess_cg_account ,
1216 jai_constants.register_type_pla, modvat_pla_account_id
1217 )
1218 FROM JAI_CMN_INVENTORY_ORGS a
1219 WHERE organization_id = cp_organization_id
1220 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1221 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1222 );
1223 /*
1224 || End of Bug#4546114
1225 */
1226
1227 /*cursor added by vkaranam for bug #5989740*/
1228 -- start 5989740
1229
1230
1231
1232 CURSOR c_orgn_sh_cess_account(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
1233 SELECT decode(cp_register_type,
1234 jai_constants.register_type_a, SH_CESS_RM_ACCOUNT ,
1235 jai_constants.register_type_c, SH_CESS_CG_ACCOUNT_ID ,
1236 jai_constants.register_type_pla, modvat_pla_account_id
1237 )
1238 FROM JAI_CMN_INVENTORY_ORGS a
1239 WHERE organization_id = cp_organization_id
1240 AND ( (cp_location_id IS NOT NULL AND a.location_id = cp_location_id)
1241 OR (cp_location_id IS NULL AND (a.location_id IS NULL OR a.location_id = 0))
1242 );
1243 -- end 5989740
1244
1245
1246 CURSOR c_orgn_tax_type_account(cp_regime_id IN NUMBER,
1247 cp_organization_type IN VARCHAR2, cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1248 cp_tax_type IN VARCHAR2, cp_account_name IN VARCHAR2) IS
1249 SELECT to_number(accnts.attribute_value)
1250 FROM JAI_RGM_REGISTRATIONS tax_types, JAI_RGM_ORG_REGNS_V accnts
1251 WHERE tax_types.regime_id = cp_regime_id
1252 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1253 AND tax_types.attribute_code = cp_tax_type
1254 AND accnts.regime_id = tax_types.regime_id
1255 AND accnts.registration_type = jai_constants.regn_type_accounts
1256 AND accnts.parent_registration_id = tax_types.registration_id
1257 AND accnts.attribute_code = cp_account_name
1258 AND accnts.organization_type = cp_organization_type
1259 AND accnts.organization_id = cp_organization_id
1260 AND (cp_location_id IS NULL OR location_id = cp_location_id);
1261
1262
1263 /*Cursor added by ssawant for bug 5879769 */
1264 CURSOR c_orgn_tax_type_account_ou
1265 ( cp_regime_id IN NUMBER,
1266 cp_organization_type IN VARCHAR2,
1267 cp_organization_id IN NUMBER,
1268 cp_location_id IN NUMBER,
1269 cp_tax_type IN VARCHAR2,
1270 cp_account_name IN VARCHAR2
1271 )
1272 IS
1273 SELECT to_number(accnts.attribute_value)
1274 FROM JAI_RGM_REGISTRATIONS tax_types,
1275 jai_rgm_parties jrp ,
1276 JAI_RGM_REGISTRATIONS accnts
1277 WHERE tax_types.regime_id = cp_regime_id
1278 AND jrp.regime_id = -accnts.regime_id
1279 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1280 AND tax_types.attribute_code = cp_tax_type
1281 AND accnts.regime_id = tax_types.regime_id
1282 AND accnts.registration_type = jai_constants.regn_type_accounts
1283 AND accnts.parent_registration_id = tax_types.registration_id
1284 AND accnts.attribute_code = cp_account_name
1285 AND jrp.organization_type = cp_organization_type
1286 AND jrp.organization_id = cp_organization_id ;
1287
1288
1289 CURSOR c_operating_unit_of_inv_org(cp_organization_id IN NUMBER) IS
1290 SELECT to_number(operating_unit) org_id
1291 FROM org_organization_definitions
1292 WHERE organization_id = cp_organization_id;
1293
1294 CURSOR c_ap_system_parameters(cp_org_id IN NUMBER) IS
1295 SELECT disc_taken_code_combination_id
1296 FROM ap_system_parameters_all
1297 WHERE org_id = cp_org_id;
1298
1299 ln_code_combination_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1300
1301 lv_organization_type VARCHAR2(2);
1302 ln_organization_id NUMBER;
1303
1304 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1305 lv_excise_cess JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE; /* Added by Ramananda - bug# 4546114*/
1306
1307 BEGIN
1308
1309 lv_excise_cess := 'EXCISE-CESS'; /* Added by Ramananda - bug# 4546114*/
1310
1311 /* following code is used to get the Discount Account in case of Payables */
1312 IF p_account_name = jai_cmn_rgm_recording_pkg.ap_discount_accnt THEN
1313 OPEN c_ap_system_parameters(p_organization_id);
1314 FETCH c_ap_system_parameters INTO ln_code_combination_id;
1315 CLOSE c_ap_system_parameters;
1316
1317 GOTO end_of_function;
1318 END IF;
1319
1320 OPEN c_regime_code(p_regime_id);
1321 FETCH c_regime_code INTO lv_regime_code;
1322 CLOSE c_regime_code;
1323
1324 IF lv_regime_code = jai_constants.service_regime
1325 AND p_location_id IS NULL AND p_organization_type = jai_constants.orgn_type_io
1326 THEN
1327 lv_organization_type := jai_constants.orgn_type_ou;
1328
1329 OPEN c_operating_unit_of_inv_org(p_organization_id);
1330 FETCH c_operating_unit_of_inv_org INTO ln_organization_id;
1331 CLOSE c_operating_unit_of_inv_org;
1332
1333 ELSE
1334 lv_organization_type := p_organization_type;
1335 ln_organization_id := p_organization_id;
1336 END IF;
1337
1338 IF lv_regime_code = jai_constants.service_regime
1339 AND lv_organization_type = jai_constants.orgn_type_io
1340 THEN
1341
1342 IF upper(p_tax_type) = UPPER(jai_constants.tax_type_excise) THEN /* IF condition added by Ramananda - bug#4546114 */
1343 OPEN c_orgn_account(ln_organization_id, p_location_id, p_account_name);
1344 FETCH c_orgn_account INTO ln_code_combination_id;
1345 CLOSE c_orgn_account;
1346
1347 IF ln_code_combination_id IS NULL THEN
1348 OPEN c_orgn_account(ln_organization_id, NULL, p_account_name);
1349 FETCH c_orgn_account INTO ln_code_combination_id;
1350 CLOSE c_orgn_account;
1351 END IF;
1352
1353 -- END IF ; /* END IF is commented and is replaced by elsif by ssawant for bug 5879769 */
1354
1355 /*
1356 || Following IF condition and the cursor in it added by Ramananda
1357 || Start of Bug#4546114
1358 */
1359 ELSIF upper(p_tax_type) IN (lv_excise_cess ,
1360 jai_constants.tax_type_exc_edu_cess,
1361 jai_constants.tax_type_cvd_edu_cess ,
1362 jai_constants.tax_type_customs_edu_cess
1363 ) THEN
1364
1365 OPEN c_orgn_cess_account(ln_organization_id, p_location_id, p_account_name);
1366 FETCH c_orgn_cess_account INTO ln_code_combination_id;
1367 CLOSE c_orgn_cess_account;
1368
1369 IF ln_code_combination_id IS NULL THEN
1370 OPEN c_orgn_cess_account(ln_organization_id, NULL, p_account_name);
1371 FETCH c_orgn_cess_account INTO ln_code_combination_id;
1372 CLOSE c_orgn_Cess_account;
1373 END IF;
1374 --Date 05/03/2007 by vkaranam for bug#5989740
1375 -- start 5989740
1376 ELSIF upper(p_tax_type) IN (jai_constants.tax_type_sh_exc_edu_cess ,
1377 jai_constants.tax_type_sh_cvd_edu_cess ,
1378 jai_constants.tax_type_sh_customs_edu_Cess
1379 ) THEN
1380
1381 OPEN c_orgn_sh_cess_account(ln_organization_id, p_location_id, p_account_name);
1382 FETCH c_orgn_sh_cess_account INTO ln_code_combination_id;
1383 CLOSE c_orgn_sh_cess_account;
1384
1385 IF ln_code_combination_id IS NULL THEN
1386 OPEN c_orgn_sh_cess_account(ln_organization_id, NULL, p_account_name);
1387 FETCH c_orgn_sh_cess_account INTO ln_code_combination_id;
1388 CLOSE c_orgn_sh_cess_account;
1389 END IF;
1390 -- end 5989740
1391 ELSE /* added by ssawant for bug 5879769 . This condition is newly added so that the Accoutn would be returned
1392 if regime SERVICE,org is IO and Taxes are of Service Type*/
1393
1394 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1395 p_location_id, p_tax_type, p_account_name);
1396 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1397 CLOSE c_orgn_tax_type_account;
1398 END IF;
1399 /*
1400 || End of Bug#4546114
1401 */
1402 ELSIF lv_regime_code = jai_constants.vat_regime
1403 AND lv_organization_type = jai_constants.orgn_type_io
1404 THEN
1405 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1406 p_location_id, p_tax_type, p_account_name);
1407 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1408 CLOSE c_orgn_tax_type_account;
1409
1410 /*Added by CSahoo Bug# 5631784*/
1411 ELSIF lv_regime_code = jai_constants.tcs_regime THEN
1412
1413 OPEN c_orgn_tax_type_account(p_regime_id, lv_organization_type, ln_organization_id,
1414 p_location_id, p_tax_type, p_account_name);
1415 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1416 CLOSE c_orgn_tax_type_account;
1417
1418 ELSIF lv_organization_type = jai_constants.orgn_type_ou THEN
1419
1420 /* added by ssawant for bug 5879769 */
1421 IF lv_regime_code = jai_constants.service_regime THEN
1422
1423 OPEN c_orgn_tax_type_account_ou(p_regime_id, jai_constants.orgn_type_ou, ln_organization_id,
1424 null, p_tax_type, p_account_name);
1425 FETCH c_orgn_tax_type_account_ou INTO ln_code_combination_id;
1426 CLOSE c_orgn_tax_type_account_ou;
1427 ELSE
1428 OPEN c_orgn_tax_type_account(p_regime_id, jai_constants.orgn_type_ou, ln_organization_id,
1429 null, p_tax_type, p_account_name);
1430 FETCH c_orgn_tax_type_account INTO ln_code_combination_id;
1431 CLOSE c_orgn_tax_type_account;
1432 END IF;
1433
1434 END IF;
1435
1436 <<end_of_function>>
1437
1438 RETURN ln_code_combination_id;
1439
1440
1441 /* Added by Ramananda for bug#4407165 */
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1445 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1446 app_exception.raise_exception;
1447
1448 END get_account;
1449
1450 PROCEDURE get_period_name(
1451 p_organization_type IN VARCHAR2,
1452 p_organization_id IN NUMBER,
1453 p_accounting_date IN OUT NOCOPY DATE,
1454 p_period_name OUT NOCOPY VARCHAR2,
1455 p_sob_id OUT NOCOPY NUMBER
1456 ) IS
1457
1458 /* Added by Ramananda for bug#4407165 */
1459 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_period_name';
1460 /* Bug 5243532. Added by Lakshmi Gopalsami
1461 Removed the cursors c_ou_sob_id and c_io_sob_id
1462 which is referring to hr_operating_units
1463 and org_organization_definitions respectively.
1464 Implemented the same using caching logic.
1465 */
1466 CURSOR c_period_dtl(cp_sob_id IN NUMBER, cp_accounting_date IN DATE) IS
1467 SELECT period_name, start_date, end_date, closing_status
1468 FROM gl_period_statuses
1469 WHERE application_id = jai_constants.gl_application_id
1470 AND set_of_books_id = cp_sob_id
1471 AND cp_accounting_date BETWEEN start_date AND end_date;
1472
1473 r_period_dtl c_period_dtl%ROWTYPE;
1474 ln_sob_id NUMBER;
1475 ld_accounting_date DATE;
1476
1477 /* Bug 5243532. Added by Lakshmi Gopalsami
1478 Defined variable for implementing caching logic
1479 */
1480 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1481 BEGIN
1482
1483 -- CHK we need to see whether the accounting date that is being used belong to a open period or not
1484 -- GL_PERIOD_STATUSES has CLOSING_STATUS column that tells whether the the period is closed or not for each APPLICATION
1485
1486 -- Validation of whether the accounting date falls under an open period or not, if not, then we populate the first date of period
1487 /* Bug 5243532. Added by Lakshmi Gopalsami
1488 Removed the logic which is referring to hr_operating_units
1489 and org_organization_definitions for getting SOB and
1490 implemented the same using caching logic.
1491 */
1492 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1493 (p_org_id => p_organization_id );
1494
1495 ln_sob_id := l_func_curr_det.ledger_id;
1496
1497 OPEN c_period_dtl(ln_sob_id, p_accounting_date);
1498 FETCH c_period_dtl INTO r_period_dtl;
1499 CLOSE c_period_dtl;
1500 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);
1501
1502 IF r_period_dtl.closing_status IN ('O','F') THEN
1503 p_sob_id := ln_sob_id;
1504 p_period_name := r_period_dtl.period_name;
1505 ELSE
1506
1507 FOR period IN ( SELECT period_name, start_date, end_date, closing_status
1508 FROM gl_period_statuses
1509 WHERE application_id = jai_constants.gl_application_id
1510 AND set_of_books_id = ln_sob_id
1511 AND start_date > p_accounting_date
1512 ORDER BY period_year, period_num
1513 )
1514 LOOP
1515 IF period.closing_status IN('O','F') THEN
1516 p_sob_id := ln_sob_id;
1517 p_period_name := period.period_name;
1518 ld_accounting_date := period.start_date;
1519 jai_cmn_utils_pkg.print_log('6395039.log', 'IN get_period_name : in IF Block');
1520 exit;
1521 END IF;
1522 END LOOP;
1523
1524 IF g_debug='Y' THEN
1525 fnd_file.put_line(fnd_file.log,'GL Period is closed for Accounting Date:'||to_char(p_accounting_date)
1526 ||'. Hence passing with Entries for '||to_char(ld_accounting_date)
1527 );
1528 END IF;
1529
1530 p_accounting_date := ld_accounting_date;
1531
1532 END IF;
1533
1534 /* Added by Ramananda for bug#4407165 */
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 p_period_name := null;
1538 p_sob_id := null;
1539 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1540 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1541 app_exception.raise_exception;
1542
1543 END get_period_name;
1544
1545
1546 PROCEDURE get_accounting_dtls(
1547 p_source IN VARCHAR2,
1548 p_src_trx_type IN VARCHAR2,
1549 p_organization_type IN VARCHAR2,
1550 p_account_name OUT NOCOPY VARCHAR2,
1551 p_account_entry_type OUT NOCOPY VARCHAR2
1552 ) IS
1553
1554 /* Added by Ramananda for bug#4407165 */
1555 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_recording_pkg.get_accounting_dtls';
1556
1557 BEGIN
1558
1559 -- following IF - ELSIF is valid for Organization Type OU w.r.t account_name and entry_type
1560 -- where as for Organization Type IO, it is valid only for entry_type
1561 IF p_source = jai_constants.source_receive THEN
1562 p_account_name := jai_constants.recovery_interim;
1563 p_account_entry_type := jai_constants.debit;
1564
1565 ELSIF p_source = jai_constants.source_rtv THEN
1566 p_account_name := jai_constants.liability_interim;
1567 p_account_entry_type := jai_constants.credit;
1568
1569 ELSIF p_source = jai_constants.source_ap THEN
1570 p_account_name := jai_constants.recovery;
1571 p_account_entry_type := jai_constants.debit;
1572 ELSIF p_source = jai_constants.source_ar THEN
1573 p_account_name := jai_constants.liability;
1574 p_account_entry_type := jai_constants.credit;
1575
1576 ELSIF p_source = jai_constants.source_manual_entry THEN
1577 --lv_account_name := p_account_name;
1578 IF p_src_trx_type IN (jai_constants.recovery, jai_constants.recovery_interim) THEN
1579 p_account_entry_type := jai_constants.debit;
1580 ELSIF p_src_trx_type IN (jai_constants.liability, jai_constants.liability_interim) THEN
1581 p_account_entry_type := jai_constants.credit;
1582 END IF;
1583
1584 ELSIF p_source IN (jai_constants.service_src_distribute_out, jai_constants.source_settle_out) THEN
1585 p_account_name := jai_constants.liability;
1586 p_account_entry_type := jai_constants.credit;
1587
1588 ELSIF p_source IN (jai_constants.service_src_distribute_in, jai_constants.source_settle_in) THEN
1589 p_account_name := jai_constants.recovery;
1590 p_account_entry_type := jai_constants.debit;
1591 END IF;
1592
1593 IF p_organization_type = jai_constants.orgn_type_io THEN
1594 p_account_name := jai_constants.register_type_a;
1595 END IF;
1596
1597
1598 /* Added by Ramananda for bug#4407165 */
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 p_account_name := null;
1602 p_account_entry_type := null;
1603 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1604 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1605 app_exception.raise_exception;
1606
1607 END get_accounting_dtls;
1608
1609 ---------------------------- UPDATE_RECOVERED_AMOUNT ---------------------------
1610 PROCEDURE update_reference(
1611 p_source IN VARCHAR2,
1612 p_reference_id IN NUMBER,
1613 p_recovered_amount IN NUMBER,
1614 p_discounted_amount IN NUMBER DEFAULT NULL,
1615 p_process_flag OUT NOCOPY VARCHAR2,
1616 p_process_message OUT NOCOPY VARCHAR2
1617 ) IS
1618
1619 lv_statement VARCHAR2(2); -- := '1' File.Sql.35 by Brathod
1620 BEGIN
1621 lv_statement :='1' ; -- File.Sql.35 by Brathod
1622 UPDATE jai_rgm_trx_refs
1623 SET recovered_amount = nvl(recovered_amount,0) + nvl(p_recovered_amount, 0),
1624 discounted_amount = nvl(discounted_amount,0) + nvl(p_discounted_amount,0),
1625 -- recoverable_amount = recoverable_amount - nvl(p_amount, 0),
1626 last_update_date = sysdate,
1627 last_updated_by = fnd_global.user_id
1628 WHERE reference_id = p_reference_id;
1629
1630 lv_statement := '2';
1631 p_process_flag := jai_constants.successful;
1632
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 p_process_flag := jai_constants.unexpected_error;
1636 p_process_message := 'jai_cmn_rgm_recording_pkg.update_reference (Stmt'||lv_statement||') Error Occured:'||SQLERRM;
1637 END update_reference;
1638
1639
1640 /* following procedure added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1641 PROCEDURE insert_vat_repository_entry(
1642 pn_repository_id OUT NOCOPY NUMBER,
1643 pn_regime_id IN NUMBER,
1644 pv_tax_type IN VARCHAR2,
1645 pv_organization_type IN VARCHAR2,
1646 pn_organization_id IN NUMBER,
1647 pn_location_id IN NUMBER,
1648 pv_source IN VARCHAR2,
1649 pv_source_trx_type IN VARCHAR2,
1650 pv_source_table_name IN VARCHAR2,
1651 pn_source_id IN NUMBER,
1652 pd_transaction_date IN DATE,
1653 pv_account_name IN VARCHAR2,
1654 pn_charge_account_id IN NUMBER,
1655 pn_balancing_account_id IN NUMBER,
1656 pn_credit_amount IN OUT NOCOPY NUMBER,
1657 pn_debit_amount IN OUT NOCOPY NUMBER,
1658 pn_assessable_value IN NUMBER,
1659 pn_tax_rate IN NUMBER,
1660 pn_reference_id IN NUMBER,
1661 pn_batch_id IN NUMBER,
1662 pn_inv_organization_id IN NUMBER,
1663 pv_invoice_no IN VARCHAR2, /* this holds either generated VAT Invoice Number or Vendor Inovice Number */
1664 pd_invoice_date IN DATE, /* this holds VAT Invoice Date or Vendor VAT Inovice Date */
1665 pv_called_from IN VARCHAR2,
1666 pv_process_flag OUT NOCOPY VARCHAR2,
1667 pv_process_message OUT NOCOPY VARCHAR2,
1668 pv_attribute_context IN VARCHAR2 DEFAULT NULL,
1669 pv_attribute1 IN VARCHAR2 DEFAULT NULL,
1670 pv_attribute2 IN VARCHAR2 DEFAULT NULL,
1671 pv_attribute3 IN VARCHAR2 DEFAULT NULL,
1672 pv_attribute4 IN VARCHAR2 DEFAULT NULL,
1673 pv_attribute5 IN VARCHAR2 DEFAULT NULL
1674 ) IS
1675
1676 CURSOR c_primary_regno(cp_regime_id IN NUMBER, cp_orgn_type in varchar2,
1677 cp_orgn_id in number, cp_location_id in number,
1678 p_att_type_code jai_rgm_registrations.attribute_Type_code%TYPE) IS --rchandan for bug#4428980
1679 SELECT attribute_value
1680 FROM JAI_RGM_ORG_REGNS_V
1681 WHERE regime_id = cp_regime_id
1682 AND organization_type = cp_orgn_type
1683 AND organization_id = cp_orgn_id
1684 and (cp_location_id is null or location_id = cp_location_id)
1685 AND registration_type = jai_constants.regn_type_others
1686 AND attribute_type_code = p_att_type_code;
1687
1688 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1689 ld_transaction_date DATE;
1690 lv_primary_regime_regno jai_rgm_trx_records.regime_primary_regno%TYPE;
1691
1692 lv_statement_id VARCHAR2(3);
1693 BEGIN
1694
1695 lv_statement_id := '1';
1696 OPEN c_regime_code(pn_regime_id);
1697 FETCH c_regime_code INTO lv_regime_code;
1698 CLOSE c_regime_code;
1699
1700 lv_statement_id := '2';
1701 IF pd_transaction_date IS NOT NULL THEN
1702 ld_transaction_date := pd_transaction_date;
1703 ELSE
1704 ld_transaction_date := trunc(sysdate);
1705 END IF;
1706
1707 lv_statement_id := '3';
1708 OPEN c_primary_regno(pn_regime_id, pv_organization_type, pn_organization_id, pn_location_id,'PRIMARY'); --rchandan for bug#4428980
1709 FETCH c_primary_regno INTO lv_primary_regime_regno;
1710 CLOSE c_primary_regno;
1711
1712 lv_statement_id := '4';
1713 /* Rounding */
1714 pn_credit_amount := round( pn_credit_amount, jai_constants.vat_rgm_rnd_factor);
1715 pn_debit_amount := round( pn_debit_amount, jai_constants.vat_rgm_rnd_factor);
1716
1717 lv_statement_id := '5';
1718 INSERT INTO jai_rgm_trx_records(
1719 repository_id, regime_code, tax_type, source,
1720 source_document_id, source_table_name, transaction_date, debit_amount, credit_amount,
1721 settled_amount, settled_flag, settlement_id, organization_type,
1722 organization_id, location_id, account_name, charge_account_id, balancing_account_id,
1723 reference_id, source_trx_type, tax_rate, assessable_value, batch_id,
1724 trx_currency, curr_conv_date, curr_conv_rate, trx_credit_amount, trx_debit_amount,
1725 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
1726 attribute_context, attribute1, attribute2, attribute3, attribute4, attribute5
1727 , inv_organization_id, regime_primary_regno, invoice_no, invoice_date
1728 ) VALUES (
1729 jai_rgm_trx_records_s.nextval, lv_regime_code, pv_tax_type, pv_source,
1730 pn_source_id, pv_source_table_name, ld_transaction_date, pn_debit_amount, pn_credit_amount,
1731 null, null, null, pv_organization_type,
1732 pn_organization_id, pn_location_id, pv_account_name, pn_charge_account_id, pn_balancing_account_id,
1733 pn_reference_id, pv_source_trx_type, pn_tax_rate, pn_assessable_value, pn_batch_id,
1734 jai_constants.func_curr, null, null, pn_credit_amount, pn_debit_amount,
1735 sysdate, FND_GLOBAL.user_id, sysdate, FND_GLOBAL.user_id, fnd_global.login_id,
1736 pv_attribute_context, pv_attribute1, pv_attribute2, pv_attribute3, pv_attribute4, pv_attribute5
1737 , pn_inv_organization_id, lv_primary_regime_regno, pv_invoice_no, pd_invoice_date
1738 ) RETURNING repository_id INTO pn_repository_id;
1739
1740 pv_process_flag := jai_constants.successful;
1741 lv_statement_id := '6';
1742
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 pv_process_flag := jai_constants.unexpected_error;
1746 pv_process_message := 'insert_vat_repository_entry Error(Stmt:'||lv_statement_id||') Occured:'||SQLERRM;
1747 --lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
1748 Fnd_file.put_line( fnd_file.log, 'Error in insert_vat_repository_entry. Stmt:'||lv_statement_id);
1749 END insert_vat_repository_entry;
1750
1751
1752 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1753 /* Two GL Entries are passed if both pn_credit_ccid and pn_debit_ccid are given as inputs to this procedure. Relevant amounts
1754 are taken while passing inserting into GL
1755 Incase a single entry needs to be passed, then pass the relevant ccid and amount
1756 */
1757 PROCEDURE do_vat_accounting(
1758 pn_regime_id IN NUMBER,
1759 pn_repository_id IN NUMBER,
1760 pv_organization_type IN VARCHAR2,
1761 pn_organization_id IN NUMBER,
1762 pd_accounting_date IN DATE,
1763 pd_transaction_date IN DATE,
1764 pn_credit_amount IN NUMBER,
1765 pn_debit_amount IN NUMBER,
1766 pn_credit_ccid IN NUMBER,
1767 pn_debit_ccid IN NUMBER,
1768 pv_called_from IN VARCHAR2,
1769 pv_process_flag OUT NOCOPY VARCHAR2,
1770 pv_process_message OUT NOCOPY VARCHAR2,
1771 pv_tax_type IN VARCHAR2 DEFAULT NULL,
1772 pv_source IN VARCHAR2 DEFAULT NULL,
1773 pv_source_trx_type IN VARCHAR2 DEFAULT NULL,
1774 pv_source_table_name IN VARCHAR2 DEFAULT NULL,
1775 pn_source_id IN NUMBER DEFAULT NULL,
1776 pv_reference_name IN VARCHAR2 DEFAULT NULL,
1777 pn_reference_id IN NUMBER DEFAULT NULL
1778 ) IS
1779
1780 r_repo_dtl c_repository_dtl%ROWTYPE;
1781
1782 lv_regime_code JAI_RGM_DEFINITIONS.regime_code%TYPE;
1783 lv_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE;
1784 ln_credit NUMBER;
1785 ln_debit NUMBER;
1786 ln_accounted_credit NUMBER;
1787 ln_accounted_debit NUMBER;
1788 ld_accounting_date DATE;
1789 ld_transaction_date DATE;
1790
1791 lv_source JAI_RGM_TRX_RECORDS.source%TYPE;
1792 lv_source_trx_type JAI_RGM_TRX_RECORDS.source_trx_type%TYPE;
1793 lv_source_table_name JAI_RGM_TRX_RECORDS.source_table_name%TYPE;
1794 ln_source_id JAI_RGM_TRX_RECORDS.source_document_id%TYPE;
1795 ln_repository_id JAI_RGM_TRX_RECORDS.repository_id%TYPE;
1796 lv_repository_name VARCHAR2(30);
1797
1798 lv_statement_id VARCHAR2(3);
1799
1800 BEGIN
1801
1802 lv_statement_id := '1';
1803 jai_cmn_utils_pkg.print_log('6395039.log','START '||lv_statement_id);
1804
1805 IF pn_repository_id IS NULL THEN
1806
1807 lv_statement_id := '2';
1808 OPEN c_regime_code(pn_regime_id);
1809 FETCH c_regime_code INTO lv_regime_code;
1810 CLOSE c_regime_code;
1811 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id||' lv_regime_code '||lv_regime_code);
1812
1813 lv_tax_type := pv_tax_type;
1814 lv_source := pv_source;
1815 lv_source_trx_type := pv_source_trx_type;
1816 lv_source_table_name := pv_source_table_name;
1817 ln_source_id := pn_source_id;
1818 lv_repository_name := pv_reference_name;
1819 ln_repository_id := pn_reference_id;
1820
1821 ELSE
1822
1823 lv_statement_id := '3';
1824 OPEN c_repository_dtl(pn_repository_id);
1825 FETCH c_repository_dtl INTO r_repo_dtl;
1826 CLOSE c_repository_dtl;
1827 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1828
1829 lv_regime_code := r_repo_dtl.regime_code;
1830 lv_tax_type := r_repo_dtl.tax_type;
1831 lv_source := r_repo_dtl.source;
1832 lv_source_trx_type := r_repo_dtl.source_trx_type;
1833 lv_source_table_name := r_repo_dtl.source_table_name;
1834 ln_source_id := r_repo_dtl.source_document_id;
1835 lv_repository_name := jai_constants.repository_name;
1836 ln_repository_id := pn_repository_id;
1837 END IF;
1838
1839 lv_statement_id := '4';
1840 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1841 IF pd_transaction_date IS NULL THEN
1842 ld_transaction_date := trunc(sysdate);
1843 ELSE
1844 ld_transaction_date := pd_transaction_date;
1845 END IF;
1846
1847 lv_statement_id := '5';
1848 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1849 IF pd_accounting_date IS NULL THEN
1850 ld_accounting_date := ld_transaction_date;
1851 ELSE
1852 ld_accounting_date := pd_accounting_date;
1853 END IF;
1854
1855 lv_statement_id := '6';
1856 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1857 IF pn_credit_ccid IS NOT NULL AND pn_credit_amount <> 0 THEN
1858
1859 lv_statement_id := '7';
1860 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1861 ln_credit := pn_credit_amount;
1862 ln_debit := null;
1863 ln_accounted_credit := pn_credit_amount;
1864 ln_accounted_debit := null;
1865
1866 lv_statement_id := '8';
1867 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1868 post_accounting(
1869 p_regime_code => lv_regime_code,
1870 p_tax_type => lv_tax_type,
1871 p_organization_type => pv_organization_type,
1872 p_organization_id => pn_organization_id,
1873 p_source => lv_source,
1874 p_source_trx_type => lv_source_trx_type,
1875 p_source_table_name => lv_source_table_name,
1876 p_source_document_id => ln_source_id,
1877 p_code_combination_id => pn_credit_ccid,
1878 p_entered_cr => ln_credit,
1879 p_entered_dr => ln_debit,
1880 p_accounted_cr => ln_accounted_credit,
1881 p_accounted_dr => ln_accounted_debit,
1882 p_accounting_date => ld_accounting_date,
1883 p_transaction_date => ld_transaction_date,
1884 p_calling_object => pv_called_from,
1885 p_repository_name => lv_repository_name,
1886 p_repository_id => ln_repository_id,
1887 p_reference_name => null, --lv_reference_name,
1888 p_reference_id => null, --ln_reference_id,
1889 p_currency_code => jai_constants.func_curr, --p_currency_code,
1890 p_curr_conv_date => null, --p_curr_conv_date,
1891 p_curr_conv_type => null, -- p_curr_conv_type,
1892 p_curr_conv_rate => null --p_curr_conv_rate
1893 );
1894
1895 END IF;
1896
1897 lv_statement_id := '9';
1898 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1899 IF pn_debit_ccid IS NOT NULL AND pn_debit_amount <> 0 THEN
1900
1901 lv_statement_id := '10';
1902 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1903 ln_debit := pn_debit_amount;
1904 ln_credit := null;
1905 ln_accounted_debit := pn_debit_amount;
1906 ln_accounted_credit := null;
1907
1908 lv_statement_id := '11';
1909 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1910 post_accounting(
1911 p_regime_code => lv_regime_code,
1912 p_tax_type => lv_tax_type,
1913 p_organization_type => pv_organization_type,
1914 p_organization_id => pn_organization_id,
1915 p_source => lv_source,
1916 p_source_trx_type => lv_source_trx_type,
1917 p_source_table_name => lv_source_table_name,
1918 p_source_document_id => ln_source_id,
1919 p_code_combination_id => pn_debit_ccid,
1920 p_entered_cr => ln_credit,
1921 p_entered_dr => ln_debit,
1922 p_accounted_cr => ln_accounted_credit,
1923 p_accounted_dr => ln_accounted_debit,
1924 p_accounting_date => ld_accounting_date,
1925 p_transaction_date => ld_transaction_date,
1926 p_calling_object => pv_called_from,
1927 p_repository_name => lv_repository_name,
1928 p_repository_id => ln_repository_id,
1929 p_reference_name => null, --lv_reference_name,
1930 p_reference_id => null, --ln_reference_id,
1931 p_currency_code => jai_constants.func_curr, --p_currency_code,
1932 p_curr_conv_date => null, --p_curr_conv_date,
1933 p_curr_conv_type => null, -- p_curr_conv_type,
1934 p_curr_conv_rate => null --p_curr_conv_rate
1935 );
1936
1937 END IF;
1938
1939 pv_process_flag := jai_constants.successful;
1940 lv_statement_id := '15';
1941 jai_cmn_utils_pkg.print_log('6395039.log',lv_statement_id);
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 pv_process_flag := jai_constants.unexpected_error;
1946 pv_process_message := 'doVatAccounting Error(Stmt:'||lv_statement_id||') Occured:'||SQLERRM;
1947 --lv_codepath := jai_general_pkg.plot_codepath(-999, lv_codepath);
1948 jai_cmn_utils_pkg.print_log('6395039.log', 'Error in doVatAccounting. Stmt:'||lv_statement_id);
1949 Fnd_file.put_line( fnd_file.log, 'Error in doVatAccounting. Stmt:'||lv_statement_id);
1950
1951 END do_vat_accounting;
1952
1953 /* following function added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1954 FUNCTION get_rgm_attribute_value(
1955 pv_regime_code IN VARCHAR2,
1956 pv_organization_type IN VARCHAR2,
1957 pn_organization_id IN NUMBER,
1958 pn_location_id IN NUMBER,
1959 pv_registration_type IN VARCHAR2,
1960 pv_attribute_type_code IN VARCHAR2,
1961 pv_attribute_code IN VARCHAR2
1962 ) RETURN VARCHAR2 IS
1963
1964 /* Test Code
1965 select get_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', 'PRIMARY', null) from dual;
1966 select jai_rgm_trx_recording_pkgget_rgm_attribute_value('VAT', 'IO', 2832, 10023, 'OTHERS', null, 'SAME_INVOICE_NO') from dual;
1967
1968 pv_organization_type, pn_organization_id, pn_location_id, pv_registration_type, pv_attribute_type_code, pv_attribute_code
1969 )
1970 */
1971
1972 CURSOR c_attribute_value(cp_regime_code IN varchar2,
1973 cp_orgn_type in varchar2, cp_orgn_id in number, cp_location_id in number,
1974 cp_registration_type in varchar2, cp_attribute_type_code in varchar2, cp_attribute_code in varchar2) IS
1975 SELECT attribute_value
1976 FROM JAI_RGM_ORG_REGNS_V
1977 WHERE regime_code = cp_regime_code
1978 AND organization_type = cp_orgn_type
1979 AND organization_id = cp_orgn_id
1980 and (cp_location_id is null or location_id = cp_location_id)
1981 AND registration_type = cp_registration_type
1982 AND ( (cp_attribute_code IS NOT NULL AND attribute_code = cp_attribute_code)
1983 or (cp_attribute_code IS NULL AND attribute_type_code = cp_attribute_type_code)
1984 );
1985
1986 lv_attribute_code JAI_RGM_ORG_REGNS_V.attribute_code%type;
1987 lv_attribute_value JAI_RGM_ORG_REGNS_V.attribute_value%type;
1988
1989 ln_fetch_cnt NUMBER;
1990
1991 BEGIN
1992
1993 IF pv_attribute_type_code = 'PRIMARY' THEN
1994 lv_attribute_code := NULL;
1995 ELSE
1996 lv_attribute_code := pv_attribute_code;
1997 END IF;
1998
1999 OPEN c_attribute_value(pv_regime_code, pv_organization_type, pn_organization_id,
2000 pn_location_id, pv_registration_type, pv_attribute_type_code, lv_attribute_code);
2001 FETCH c_attribute_value INTO lv_attribute_value;
2002 ln_fetch_cnt := SQL%ROWCOUNT;
2003 CLOSE c_attribute_value;
2004
2005 RETURN lv_attribute_value;
2006
2007 END get_rgm_attribute_value;
2008
2009 END jai_cmn_rgm_recording_pkg;
[Home] [Help]