[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_ACCOUNTING_PKG
Source
1 PACKAGE BODY jai_rcv_accounting_pkg AS
2 /* $Header: jai_rcv_accnt.plb 120.6.12010000.10 2010/03/05 07:13:10 vkaranam ship $ */
3
4 /*----------------------------------------------------------------------------------------------------------------------------
5 CHANGE HISTORY for FILENAME: jai_rcv_accounting_pkg.sql
6 S.No dd/mm/yyyy Author and Details
7 ------------------------------------------------------------------------------------------------------------------------------
8 1 07/08/2004 Nagaraj.s for Bug# 3496408, Version:115.0
9 This Package is coded to handle all Accounting Entries for all the Receiving/Return Transactions and Cenvat Entries
10
11 2 16/09/2004 Sanjikum for bug # 3889243 File Version : 115.1
12 - Added 2 new variables - r_rcv_transactions, ln_accounting_line_type
13 - Added cursor - cur_trans_type, to get the transaction type
14 - Assigned the value to ln_accounting_line_type, on the basis of transaction type and
15 Debit_credit_flag
16 - While inserting into mtl_transaction_accounts, the value of column accounting_line_type
17 is changed from hadrcoded 1 to ln_accounting_line_type
18 - In the Begin of the Procedure mta_entry, Changed the condition from
19 "if NVL(ln_tax_amount, 0) = 0 then " to "if NVL(p_tax_amount, 0) = 0 then"
20
21 3 10/10/2004 Vijay Shankar for Bug#3899897 (3927371), Version:115.2
22 During Average Costing, Instead of populating MTL_MATERIAL_TRANSACTIONS_TEMP table we stated populating
23 MTL_TRANSACTIONS_INTERFACE and MTL_TXN_COST_DET_INTERFACE. This new route is followed to remove the incosistancy in
24 the way the costing happens. This is porting of Bug#3841831
25 New Internal Package Procedure MTI_ENTRY is introduced with this fix. Procedure name MMTT_ENTRY is modified as AVERAGE_COSTING
26
27 4 08/11/2004 Vijay Shankar for Bug#3949487, Version:115.3
28 Duplicate Check in process_transaction is modified to use CR, DR filter. This is to pass both CR and DR
29 entries if Inventory receiving and AP Acrual account refers to same account_id. Previously its passing
30 only one CR or DR entry if this is the case
31
32 5 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.4
33 added two parameters(reference_name, reference_id) in process_transaction procedure as part of VAT Implementation
34 to enhance the duplicate check. Modified the duplicate check cursor to use the two new input parameters that
35 are added
36
37 6 01/04/2005 Sanjikum for Bug#4257065, Version 115.5
38 Reason/problem
39 --------------
40 As ln_entered_cr and ln_entered_dr are rounded to the currency precision, before calling the procedure rcv_transactions_update.
41 So in the Procedure rcv_transactions_update, po_unit_price is rounded to the precision of the currency
42
43 Fix
44 ---
45 In the Procedure Process_transaction, while calling procedure rcv_transactions_update, passed the value of parameter
46 p_costing_amount as ROUND(NVL(p_entered_cr, p_entered_dr),5), instead of NVL(ln_entered_cr, ln_entered_dr)
47
48 7 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
49 as required for CASE COMPLAINCE.
50
51 8. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
52 Removal of SQL LITERALs is done.
53
54 9. 7-Jul-2005 rchandan for bug#4473022. File Version: 116.4
55 Modified the object as part of SLA impact uptake. The procedure mta_entry has changed
56 to replace an insert into mtl_transaction_accounts with a call to gl_entry.
57 10. 28/07/2005 Ramananda for Bug#4522484. File Version: 120.2
58 Issue:-
59 Due to a PO Receipt Delivery transaction,India Localization does an the average cost update value change transaction.
60 The tax amounts are getting prorated and all the costing elements get updated.
61 The correct behaviour would be to pass on all the tax amounts to the Material and Material Overhead costing element.
62
63 Fix:-
64 The issue has been resolved by making an insert into the the mctcdi with a value change of 0 for the cost elements which
65 are present in the CLCD (cst_layer_cost_details) but not in mctcdi.
66
67 Dependency Due to this Bug:-
68 Functional dependency with procedure jai_rcv_accounting_pkg.mti_entry version 120.2
69
70 Dependancy:
71 -----------
72 IN60105D2 + 3496408
73 IN60106 + 3940588 + 4245089
74
75 11.13-FEB-2007 Vkaranam for bug #5186391,File version 120.5
76 Forward Port changes for the base bug #4738650(Over Heads Are Still Loaded For Average When Overheads Are Not Defined In System).
77 Changes are done in the cursor c_fetch_count_overheads.
78
79 12. 03-oct-2008 vkaranam for bug#5228227,File version 120.6.12010000.1/120.7
80 Forward ported the changes done in 115 bug#4994774
81
82 13. 29-Apr-2008 CSahoo for bug#8449597, File Version 120.6.12010000.6
83 Modified the cursor c_cost_group. Added an and clause in the cursor.
84 14. 01-jul-2009 vkaranam for bug#8649408,File version 120.6.12010000.8/120.10
85 Forward ported the changes done in 115 bug#8547858
86
87 13. 05-mar-2010 vkaranam for bug#9441529
88 issue:
89 Material overhead is not adding to the nonrecoverable tax potion,due to which the
90 same is not getting costed.
91 Fix:
92 Forwardported the changes done in 115 bug 5737092
93
94
95
96
97 ----------------------------------------------------------------------------------------------------------------------------*/
98
99 -- This is an Internal Package procedure that simply inserts data into MTI Gateway based on the parameters passed to this procedure
100 PROCEDURE mti_entry(
101 p_txn_header_id IN OUT NOCOPY NUMBER,
102 p_item_id IN NUMBER,
103 p_organization_id IN NUMBER,
104 p_uom_code IN VARCHAR2,
105 p_transaction_date IN DATE,
106 p_transaction_type_id IN NUMBER,
107 p_transaction_source_type_id IN NUMBER,
108 p_transaction_id IN NUMBER,
109 p_cost_group_id IN NUMBER,
110 p_receiving_account_id IN NUMBER,
111 p_absorption_account_id IN NUMBER,
112 p_value_change IN NUMBER,
113 p_new_cost IN NUMBER,
114 p_usage_rate_or_amount IN NUMBER,
115 p_overhead_exists IN VARCHAR2, -- Added by Ramananda for the bug 4522484
116 p_transaction_action_id IN NUMBER -- Vkaranam for bug#5228227
117 ) IS
118
119 ln_txn_interface_id NUMBER;
120
121 -- Default Values
122 lv_transaction_source_name VARCHAR2(30); --File.Sql.35 Cbabu := 'Avg Cost Update Conversion';
123 lv_source_code VARCHAR2(30); --File.Sql.35 Cbabu := 'Localization-Value Change';
124 ln_src_line_id NUMBER ; --File.Sql.35 Cbabu := -1;
125 ln_src_header_id NUMBER ; --File.Sql.35 Cbabu := -1;
126 ln_process_flag NUMBER ; --File.Sql.35 Cbabu := 1;
127 ln_transaction_mode NUMBER ; --File.Sql.35 Cbabu := 3;
128 ln_quantity NUMBER ; --File.Sql.35 Cbabu := 0;
129 ln_lock_flag NUMBER ; --File.Sql.35 Cbabu := 2; -- No Lock
130 ln_material_cost_element_id NUMBER ; --File.Sql.35 Cbabu := 1; -- Material
131 ln_overhead_cost_element_id NUMBER ; --File.Sql.35 Cbabu := 2; -- Material
132 ln_level_type NUMBER ; --File.Sql.35 Cbabu := 1; -- This Level
133 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_rcv_accnt_pkg.mti_entry';
134
135 BEGIN
136 --File.Sql.35 Cbabu
137 lv_transaction_source_name := 'Avg Cost Update Conversion';
138 lv_source_code := 'Localization-Value Change';
139 ln_src_line_id := -1;
140 ln_src_header_id := -1;
141 ln_process_flag := 1;
142 ln_transaction_mode := 3;
143 ln_quantity := 0;
144 ln_lock_flag := 2; -- No Lock
145 ln_material_cost_element_id := 1; -- Material
146 ln_overhead_cost_element_id := 2; -- Material
147 ln_level_type := 1; -- This Level
148
149 -- Material Over head account is defaulted put into Absorption account.
150 INSERT INTO mtl_transactions_interface
151 (
152 source_code ,
153 source_line_id ,
154 source_header_id ,
155 process_flag ,
156 transaction_mode ,
157 transaction_interface_id ,
158 transaction_header_id ,
159 inventory_item_id ,
160 organization_id ,
161 revision ,
162 transaction_quantity ,
163 transaction_uom ,
164 transaction_date ,
165 transaction_source_name ,
166 transaction_type_id ,
167 transaction_source_type_Id , --PVI
168 rcv_transaction_id ,
169 transaction_reference , -- rcv_transaction Id.
170 last_update_date ,
171 last_updated_by ,
172 creation_date ,
173 created_by ,
174 cost_group_id ,
175 material_account ,
176 material_overhead_account , --overhead absorption account
177 resource_account ,
178 overhead_account ,
179 outside_processing_account ,
180 lock_flag ,
181 transaction_action_id -- Vkaranam for bug#5228227
182 )
183 VALUES (
184 lv_source_code ,
185 ln_src_line_id ,
186 ln_src_header_id ,
187 ln_process_flag ,
188 ln_transaction_mode ,
189 mtl_material_transactions_s.nextval ,
190 decode( p_txn_header_id, null ,
191 mtl_material_transactions_s.currval ,
192 p_txn_header_id
193 ) ,
194 p_item_id ,
195 p_organization_id ,
196 null ,
197 ln_quantity , -- No Qty
198 p_uom_code ,
199 p_transaction_date ,
200 lv_transaction_source_name ,
201 p_transaction_type_id , -- Avg Cost Update
202 p_transaction_source_type_id , -- Inventory
203 p_transaction_id ,
204 to_char(p_transaction_id) ,
205 sysdate ,
206 fnd_global.user_id ,
207 sysdate ,
208 fnd_global.user_id ,
209 p_cost_group_id ,
210 p_receiving_account_id ,
211 p_absorption_account_id ,
212 p_receiving_account_id ,
213 p_receiving_account_id ,
214 p_receiving_account_id ,
215 ln_lock_flag ,
216 p_transaction_action_id -- Vkaranam for bug#5228227
217 )
218 RETURNING transaction_interface_id ,
219 transaction_header_id
220 INTO ln_txn_interface_id ,
221 p_txn_header_id ;
222
223 INSERT INTO mtl_txn_cost_det_interface
224 (
225 transaction_interface_id ,
226 last_update_date ,
227 last_updated_by ,
228 creation_date ,
229 created_by ,
230 organization_id ,
231 cost_element_id ,
232 level_type ,
233 value_change
234 )
235 VALUES
236 (
237 ln_txn_interface_id ,
238 sysdate ,
239 fnd_global.user_id ,
240 sysdate ,
241 fnd_global.user_id ,
242 p_organization_id ,
243 ln_material_cost_element_id ,
244 ln_level_type ,
245 p_value_change
246 );
247
248 /*
249 ||Start of bug 4522484
250 ||Added the condition p_overhead_exists
251 ||so that the insert gets executed only for Overhead elements
252 */
253 IF nvl(p_overhead_exists,'NO') = 'YES' THEN
254 INSERT INTO mtl_txn_cost_det_interface
255 (
256 transaction_interface_id ,
257 last_update_date ,
258 last_updated_by ,
259 creation_date ,
260 created_by ,
261 organization_id ,
262 cost_element_id ,
263 level_type ,
264 value_change
265 )
266 VALUES
267 (
268 ln_txn_interface_id ,
269 sysdate ,
270 fnd_global.user_id ,
271 sysdate ,
272 fnd_global.user_id ,
273 p_organization_id ,
274 ln_overhead_cost_element_id ,
275 ln_level_type ,
276 (p_new_cost * p_usage_rate_or_amount)
277 );
278 END IF;
279
280 /*
281 || Added by Ramananda for bug 4522484
282 || Cost elements which are present in the CLCD (cst_layer_cost_details)
283 || should be inserted into MCTCDI with a value change of 0 .
284 */
285
286 INSERT INTO mtl_txn_cost_det_interface
287 (
288 transaction_interface_id,
289 last_update_date,
290 last_updated_by,
291 creation_date,
292 created_by,
293 organization_id,
294 cost_element_id,
295 level_type,
296 value_change
297 )
298 (SELECT
299 ln_txn_interface_id ,
300 sysdate ,
301 fnd_global.user_id ,
302 sysdate ,
303 fnd_global.user_id ,
304 p_organization_id ,
305 clcd.cost_element_id ,
306 clcd.level_type ,
307 0
308 FROM
309 cst_layer_cost_details clcd,
310 cst_quantity_layers cql
311 WHERE
312 cql.organization_id = p_organization_id
313 and cql.inventory_item_id = p_item_id
314 and cql.cost_group_id = p_cost_group_id
315 and clcd.layer_id = cql.layer_id
316 and (clcd.cost_element_id,clcd.level_type) NOT IN
317 ( SELECT
318 mctcd1.cost_element_id,
319 mctcd1.level_type
320 FROM
321 mtl_txn_cost_det_interface mctcd1
322 WHERE
323 mctcd1.transaction_interface_id = ln_txn_interface_id
324 )
325 );
326 /*
327 ||End of bug 4522484
328 */
329 EXCEPTION
330 WHEN OTHERS THEN
331 p_txn_header_id := null;
332 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
333 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
334 app_exception.raise_exception;
335 END mti_entry;
336
337 PROCEDURE process_transaction
338 (
339
340 p_transaction_id in number,
341 p_acct_type in varchar2,
342 p_acct_nature in varchar2,
343 p_source_name in varchar2,
344 p_category_name in varchar2,
345 p_code_combination_id in number,
346 p_entered_dr in number,
347 p_entered_cr in number,
348 p_currency_code in varchar2,
349 p_accounting_date in date,
350 p_reference_10 in varchar2,
351 p_reference_23 in varchar2,
352 p_reference_24 in varchar2,
353 p_reference_25 in varchar2,
354 p_reference_26 in varchar2,
355 p_destination in varchar2,
356 p_simulate_flag in varchar2,
357 p_codepath in OUT NOCOPY varchar2,
358 p_process_message OUT NOCOPY varchar2,
359 p_process_status OUT NOCOPY varchar2,
360 /* two parameters added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
361 p_reference_name in varchar2 DEFAULT NULL,
362 p_reference_id in number DEFAULT NULL
363
364 ) IS
365
366
367 cursor c_acct_check(cp_transaction_id number ,cp_account_nature varchar2, cp_ccid number,
368 cp_reference_name varchar2, cp_reference_id number) is
369 select count(transaction_id)
370 from JAI_RCV_JOURNAL_ENTRIES
371 where transaction_id = cp_transaction_id
372 and acct_nature = cp_account_nature
373 and code_combination_id = cp_ccid
374 /* following reference columns condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
375 and ( (cp_reference_name is null and reference_name is null)
376 or (cp_reference_name is not null
377 and reference_name = cp_reference_name)
378 )
379 and ( (cp_reference_id is null and reference_id is null)
380 or (cp_reference_id is not null
381 and reference_id = cp_reference_id)
382 )
383 and ((p_entered_cr <> 0 AND entered_cr <>0) OR (p_entered_dr <> 0 AND entered_dr <>0)); -- Bug#3949487
384
385 /* Need to confirm these 2 Queries as these can be merged */
386
387 /* Bug 5243532. Added by Lakshmi Gopalsami
388 * Removed cursor c_fetch_org_information and removed
389 * org_organization_definitions from the cursor c_period_name
390 * and passed set_of_books_id to the cursor. Also removed
391 * gl_sets_of_books and included gl_ledgers.
392 */
393
394 cursor c_period_name(cp_set_of_books_id in number, cp_accounting_date in date) IS
395 select gd.period_name
396 FROM gl_ledgers gle, gl_periods gd
397 where gle.ledger_id = cp_set_of_books_id
398 and gd.period_set_name = gle.period_set_name
399 and cp_accounting_date between gd.start_date and gd.end_date
400 and gd.adjustment_period_flag = 'N';
401
402 /*Added by nprashar for FP bug # 9304844*/
403 cursor c_get_gl_period_status(cp_set_of_books_id number,cp_accounting_date date) IS /* added for bug 7007523 */
404 select closing_status from gl_period_statuses where
405 set_of_books_id=cp_set_of_books_id
406 and application_id=101 /*seeded application id for GL*/
407 and trunc(cp_accounting_date) between start_date and end_date; /*Added the Trunc clause for bug # 9288398*/
408
409 /*Added by nprashar for FP bug # 9304844*/
410 cursor c_new_accounting_date(cp_set_of_books_id number) IS /* added for bug 7007523 */
411 select start_date from gl_period_statuses
412 where set_of_books_id=cp_set_of_books_id
413 and application_id=101 /*seeded application id for GL*/
414 and closing_status IN ('O','F')
415 and start_date > p_accounting_date
416 order by period_year asc,period_num asc,closing_status desc;
417
418 /*Added by nprashar for FP bug # 9304844*/
419 lv_show_status gl_period_statuses.closing_status%TYPE; /* added for bug 7007523 */
420 lv_new_accounting_date date; /* added for bug 7007523 */
421 lv_accounting_date date; /* added for bug 7007523 */
422
423
424 /* Record Declarations */
425 r_trx c_trx%ROWTYPE;
426 r_base_trx c_base_trx%ROWTYPE;
427
428 ln_acct_count number; --File.Sql.35 Cbabu := 0 ;
429 ln_set_of_books_id org_organization_definitions.set_of_books_id%type;
430 ln_precision number;
431 ln_entered_cr number;
432 ln_entered_dr number;
433 ln_user_id fnd_user.user_id%type; --File.Sql.35 Cbabu := fnd_global.user_id;
434
435 lv_organization_code org_organization_definitions.organization_code%type;
436 lv_period_name gl_periods.period_name%type;
437 lv_debit_credit_flag varchar2(1);
438 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
439
440 ld_sysdate date; --File.Sql.35 Cbabu := SYSDATE;
441
442 /* Bug 5243532. Added by Lakshmi Gopalsami
443 * Defined variable for implementing caching logic.
444 */
445 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
446 -- End for bug 5243532
447 BEGIN
448
449 lv_debug := 'Y';
450 ld_sysdate := SYSDATE;
451 ln_acct_count := 0 ;
452 ln_user_id := fnd_global.user_id;
453
454 /* Multiple Accounting Entry Checks. Needs to be changed for CENVAT */
455 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 1');
456 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.process_transaction', 'START'); /* 1 */
457 lv_accounting_date:=p_accounting_date; /*Added by nprashar for FP bug # 9304844*/
458
459 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 2');
460
461 open c_acct_check(p_transaction_id, p_acct_nature, p_code_combination_id, p_reference_name, p_reference_id);
462 fetch c_acct_check into ln_acct_count;
463 close c_acct_check;
464
465 if lv_debug ='Y' then
466 fnd_file.put_line( fnd_file.log, '1.0 ln_acct_count -> ' || ln_acct_count ||' p_simulate_flag -> ' || p_simulate_flag );
467 end if;
468
469 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 3');
470 /**bug#8649408
471 if ln_acct_count > 0 and p_simulate_flag ='N' then
472 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2
473 p_process_status := 'X';
474 p_process_message := 'Accounting Entries are already passed for Transaction' ; --After Review
475 goto exit_from_procedure;
476 end if;
477 **//*8649408*/
478
479 /* Fetch all the information from JAI_RCV_TRANSACTIONS */
480 open c_trx(p_transaction_id);
481 fetch c_trx into r_trx;
482 close c_trx;
483
484 open c_base_trx(p_transaction_id);
485 fetch c_base_trx into r_base_trx;
486 close c_base_trx;
487
488 /* Bug 5243532. Added by Lakshmi Gopalsami
489 * Removed cursor c_fetch_org_information and
490 * implemented caching logic.
491 */
492
493 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
494 (p_org_id => r_trx.organization_id);
495 lv_organization_code := l_func_curr_det.organization_code;
496 ln_set_of_books_id := l_func_curr_det.ledger_id;
497
498 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
499
500 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 4');
501
502 /* added for bug 7007523 */
503 /*If accouting date is in a closed gl period, the startdate of the latest open period is used as accounting date */
504
505 /*Added by nprashar for FP bug # 9304844*/
506 OPEN c_get_gl_period_status(ln_set_of_books_id,lv_accounting_date);/* modified p_accouting_date to lv_accounting_date for bug 7007523 */
507 FETCH c_get_gl_period_status into lv_show_status;
508 CLOSE c_get_gl_period_status;
509
510 /*Added by nprashar for FP bug # 9304844*/
511 IF upper(lv_show_status)='C' THEN
512 OPEN c_new_accounting_date(ln_set_of_books_id);
513 FETCH c_new_accounting_date INTO lv_new_accounting_date;
514 CLOSE c_new_accounting_date;
515 lv_accounting_date:=lv_new_accounting_date;
516 END IF;
517 /* end - added for bug 7007523 */
518
519
520 /* Fetch Period Information */
521
522 /* Bug 5243532. Added by Lakshmi Gopalsami
523 * Passed set_of_books_id instead of r_trx.organization_id
524 * as org_organization_definitions has been removed
525 * from the cursor c_period_name,
526 */
527 /*Added by nprashar for FP bug # 9304844*/
528 open c_period_name(ln_set_of_books_id, lv_accounting_date); --Commented p_accounting_date added lv_accounting_date by nprashar;
529 fetch c_period_name into lv_period_name;
530 close c_period_name;
531
532 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
533 ln_precision := jai_general_pkg.get_currency_precision(r_trx.organization_id);
534
535 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 5');
536
537 ln_entered_cr := ROUND(p_entered_cr,ln_precision);
538 ln_entered_dr := ROUND(p_entered_dr,ln_precision);
539
540 if lv_debug ='Y' then
541 fnd_file.put_line( fnd_file.log, '1.3 ln_entered_cr -> ' || ln_entered_cr ||' ln_entered_dr -> '|| ln_entered_dr );
542 end if;
543
544 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> ---6');
545
546 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
547 jai_rcv_journal_pkg.insert_row
548 (
549 p_organization_id => r_trx.organization_id,
550 p_organization_code => lv_organization_code,
551 p_receipt_num => r_trx.receipt_num,
552 p_transaction_id => p_transaction_id,
553 p_transaction_date => r_trx.transaction_date,
554 p_shipment_line_id => r_trx.shipment_line_id,
555 p_acct_type => p_acct_type,
556 p_acct_nature => p_acct_nature,
557 p_source_name => p_source_name,
558 p_category_name => p_category_name,
559 p_code_combination_id => p_code_combination_id,
560 p_entered_dr => ln_entered_dr,
561 p_entered_cr => ln_entered_cr,
562 p_transaction_type => r_trx.transaction_type,
563 p_period_name => lv_period_name,
564 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
565 p_currency_conversion_type => NULL,
566 p_currency_conversion_date => NULL,
567 p_currency_conversion_rate => NULL,
568 p_simulate_flag => p_simulate_flag,
569 p_process_status => p_process_status,
570 p_process_message => p_process_message,
571 /* following two parameters added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
572 p_reference_name => p_reference_name,
573 p_reference_id => p_reference_id
574 );
575
576 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
577 if p_process_status IN ('E', 'X') then
578 goto exit_from_procedure;
579 end if;
580 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 7');
581
582 /*This is to Ensure that in case of simulate flag ='Y',
583 No Accounting, costing and sub ledger tables are affected */
584
585 if p_simulate_flag ='Y' then
586 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
587 goto exit_from_procedure;
588 end if;
589
590 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 8');
591 /*
592 The Destination is sent from the respective procedures depending on the Accounting/Costing that
593 needs to happen.
594 Following are the p_destination values and its target tables.
595 |------------------------|-------------------------------------------------------|
596 |Destination Type | Destination value |
597 |------------------------|-------------------------------------------------------|
598 | G | GL Interface |
599 | A1 | Average Costing - Inventory receiving Entry |
600 | S | Standard Costing |
601 | O1 | OPM Costing |
602 |------------------------|-------------------------------------------------------|
603 */
604
605 if p_destination ='G' then --GL Interface Entries
606
607 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
608
609 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 9');
610 gl_entry
611 (
612 p_organization_id => r_trx.organization_id,
613 p_organization_code => lv_organization_code,
614 p_set_of_books_id => ln_set_of_books_id,
615 p_credit_amount => ln_entered_cr,
616 p_debit_amount => ln_entered_dr,
617 p_cc_id => p_code_combination_id,
618 p_je_source_name => p_source_name,
619 p_je_category_name => p_category_name,
620 p_created_by => ln_user_id,
621 p_accounting_date => lv_accounting_date, --Replaced the call of p_accounting_date by nprashar /*Added by nprashar for FP bug # 9304844*/
622 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
623 p_currency_conversion_date => NULL,
624 p_currency_conversion_type => NULL,
625 p_currency_conversion_rate => NULL,
626 p_reference_10 => p_reference_10,
627 p_reference_23 => p_reference_23,
628 p_reference_24 => p_reference_24,
629 p_reference_25 => p_reference_25,
630 p_reference_26 => p_reference_26,
631 p_process_message => p_process_message,
632 p_process_status => p_process_status,
633 p_codepath => p_codepath
634 );
635
636 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
637 if p_process_status IN ('E', 'X') then
638 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
639 goto exit_from_procedure;
640 end if;
641
642
643
644 elsif p_destination = 'A1' then /*Average Costing Receiving Inspection Account Entry */
645
646 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
647
648 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 10');
649
650 average_costing
651 (
652 p_receiving_account_id => p_code_combination_id,
653 p_new_cost => NVL(ln_entered_cr, ln_entered_dr),
654 p_organization_id => r_trx.organization_id,
655 p_item_id => r_trx.inventory_item_id,
656 p_shipment_line_id => r_trx.shipment_line_id,
657 p_transaction_uom => r_trx.uom_code,
658 p_transaction_date => r_trx.transaction_date,
659 p_subinventory => r_base_trx.subinventory,
660 p_func_currency => jai_rcv_trx_processing_pkg.gv_func_curr,
661 p_transaction_id => p_transaction_id,
662 p_process_message => p_process_message,
663 p_process_status => p_process_status,
664 p_codepath => p_codepath
665 );
666 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
667
668 if p_process_status IN ('E', 'X') then
669 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
670 goto exit_from_procedure;
671 end if;
672
673 ELSIF p_destination ='S' then /* Indicates Standard Costing */
674
675 /*Logic for Setting Debit Credit Flag
676 ===================================================
677 Amount > 0 Debit Credit Flag
678 Credit Amount N
679 Debit Amount Y
680 */
681 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 11');
682
683 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
684 if ln_entered_cr <> 0 then
685 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
686 lv_debit_credit_flag :='N';
687 elsif ln_entered_dr <> 0 then
688 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
689 lv_debit_credit_flag :='Y';
690 end if;
691
692 if lv_debug ='Y' then
693 fnd_file.put_line( fnd_file.log, '1.7 Before the call to mta_entry Procedure ' || 'lv_debit_credit_flag ' || lv_debit_credit_flag);
694 end if;
695
696 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
697 mta_entry
698 (
699 p_transaction_id => p_transaction_id,
700 p_reference_account => p_code_combination_id,
701 p_debit_credit_flag => lv_debit_credit_flag,
702 p_tax_amount => NVL(ln_entered_cr, ln_entered_dr),
703 p_transaction_date => r_trx.transaction_date,
704 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
705 p_currency_conversion_date => NULL,
706 p_currency_conversion_type => NULL,
707 p_currency_conversion_rate => NULL,
708 p_reference_23 => p_reference_23,
709 p_reference_24 => p_reference_24,
710 p_reference_26 => p_reference_26,
711 p_process_message => p_process_message,
712 p_process_status => p_process_status,
713 p_codepath => p_codepath,
714 p_source_name => p_source_name,
715 p_category_name => p_category_name,
716 p_accounting_date => lv_accounting_date --Replaced the call of p_accounting_date by nprashar /*Added by nprashar for FP bug # 9304844*/
717 );
718
719 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
720
721 if p_process_status IN ('E', 'X') then
722 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
723 goto exit_from_procedure;
724 end if;
725
726 /*Bug 7581494 Porting fix made via 6905807 to 12.1 branch and Mainline*/
727 /*
728 ELSIF p_destination ='O1' then -- OPM Costing Entry
729
730 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); -- 20
731
732 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 12');
733
734 rcv_transactions_update
735 (
736 p_transaction_id => p_transaction_id,
737 p_costing_amount => ROUND(NVL(p_entered_cr, p_entered_dr),5), --added by Sanjikum for Bug #4257065
738 --p_costing_amount => NVL(ln_entered_cr, ln_entered_dr), --commented by Sanjikum for Bug #4257065
739 --This was now rounded to 5 decimal places, as PO_UNIT_PRICE should be rounded to 5 places
740 p_process_message => p_process_message,
741 p_process_status => p_process_status,
742 p_codepath => p_codepath
743 ) ;
744
745 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); -- 21
746
747 if p_process_status IN ('E', 'X') then
748 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); -- 22
749 goto exit_from_procedure;
750 end if;
751 */
752
753 end if; /*End if for p_destination */
754 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
755
756 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 13');
757
758 /*This check ensures that this is called only in the following transaction types
759 1. RECEIVE
760 2. RETURN TO VENDOR
761 3. CORRECT TO RECEIVE
762 4. CORRECT TO RTV
763 And this is called from JA_in_RECEIVE_RTR_PKG. */
764
765 if lv_debug ='Y' then
766 fnd_file.put_line( fnd_file.log, '2.0 p_acct_nature - > ' || p_acct_nature );
767 end if;
768
769 if p_acct_nature = 'Receiving' then
770
771 p_codepath := jai_general_pkg.plot_codepath(24, p_codepath); /* 24 */
772
773 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 14');
774
775 rcv_receiving_sub_ledger_entry
776 (
777 p_transaction_id => p_transaction_id,
778 p_organization_id => r_trx.organization_id,
779 p_set_of_books_id => ln_set_of_books_id,
780 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
781 p_credit_amount => ln_entered_cr,
782 p_debit_amount => ln_entered_dr,
783 p_cc_id => p_code_combination_id,
784 p_shipment_line_id => r_trx.shipment_line_id,
785 p_item_id => r_trx.inventory_item_id,
786 p_source_document_code => r_base_trx.source_document_code,
787 p_po_line_location_id => r_base_trx.po_line_location_id,
788 p_requisition_line_id => r_base_trx.requisition_line_id,
789 p_accounting_date => lv_accounting_date, --Replaced the call of p_accounting_date by nprashar /*Added by nprashar for FP bug # 9304844*/
790 p_currency_conversion_date => NULL,
791 p_currency_conversion_type => NULL,
792 p_currency_conversion_rate => NULL,
793 p_process_message => p_process_message,
794 p_process_status => p_process_status,
795 p_codepath => p_codepath
796 );
797
798 p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
799
800 if p_process_status IN ('E', 'X') then
801 p_codepath := jai_general_pkg.plot_codepath(26, p_codepath); /* 26 */
802 goto exit_from_procedure;
803 end if;
804
805 p_codepath := jai_general_pkg.plot_codepath(27, p_codepath); /* 27 */
806
807 end if; /* End if for p_acct_nature */
808
809 << exit_from_procedure >>
810 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 15');
811
812 if p_process_status = 'Y' then
813 p_process_message := 'The Accounting / Costing Entries are passed successfully ' ;
814 end if;
815
816 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath, NULL, 'END'); /* 28 */
817
818 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 16');
819
820 exception
821
822 when others then
823 p_process_status := 'E';
824 fnd_file.put_line(FND_FILE.LOG, ' <jai_rcv_accnt.plb> --- 17');
825 p_process_message := 'RECEIPT_ACCOUNTING_PKG.process_transaction:' || SQLERRM;
826 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
827 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, NULL, 'END'); /* 29 */
828 return;
829
830 end process_transaction;
831
832 /*--------------------------------------------------------------------------------------------------*/
833 PROCEDURE gl_entry
834 (
835 p_organization_id in number,
836 p_organization_code in varchar2,
837 p_set_of_books_id in number,
838 p_credit_amount in number,
839 p_debit_amount in number,
840 p_cc_id in number,
841 p_je_source_name in varchar2,
842 p_je_category_name in varchar2,
843 p_created_by in number,
844 p_accounting_date in date default null,
845 p_currency_code in varchar2,
846 p_currency_conversion_date in date default null,
847 p_currency_conversion_type in varchar2 default null,
848 p_currency_conversion_rate in number default null,
849 p_reference_10 in varchar2 default null,
850 p_reference_23 in varchar2 default null,
851 p_reference_24 in varchar2 default null,
852 p_reference_25 in varchar2 default null,
853 p_reference_26 in varchar2 default null ,
854 p_process_message OUT NOCOPY varchar2,
855 p_process_status OUT NOCOPY varchar2,
856 p_codepath in OUT NOCOPY varchar2
857 ) IS
858
859
860 lv_reference_entry gl_interface.reference22%type; --File.Sql.35 Cbabu := 'India Localization Entry';
861 lv_reference_10 gl_interface.reference10%type;
862 lv_reference_23 gl_interface.reference23%type;
863 lv_reference_24 gl_interface.reference24%type;
864 lv_reference_25 gl_interface.reference25%type;
865 lv_reference_26 gl_interface.reference26%type;
866 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
867
868 ln_user_id fnd_user.user_id%type; --File.Sql.35 Cbabu := fnd_global.user_id;
869
870 ld_sysdate date; --File.Sql.35 Cbabu := SYSDATE;
871 ld_accounting_date date;
872
873 cursor c_trunc_references is
874 select
875 substr(lv_reference_10,1,240),
876 substr(p_reference_23,1,240),
877 substr(p_reference_24,1,240),
878 substr(p_reference_25,1,240),
879 substr(p_reference_26,1,240)
880 from dual;
881
882 lv_status gl_interface.status%type;
883 BEGIN
884
885 lv_reference_entry := 'India Localization Entry';
886 lv_debug := jai_constants.yes;
887 ln_user_id := fnd_global.user_id;
888 ld_sysdate := SYSDATE;
889
890 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.gl_entry', 'START'); /* 1 */
891 lv_reference_10 := substr(p_reference_10 || ' for the Organization code ' || p_organization_code,1,240);
892
893 /*This is introduced to ensure that if the reference values goes beyond the specified width,
894 then the value would be restriced to an width of 240 so that exception would not occur.*/
895 open c_trunc_references;
896 fetch c_trunc_references
897 into lv_reference_10, lv_reference_23, lv_reference_24, lv_reference_25, lv_reference_26;
898 close c_trunc_references;
899
900
901 if p_accounting_date is null then
902 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
903 ld_accounting_date := sysdate;
904 else
905 ld_accounting_date := p_accounting_date;
906 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
907 end if;
908
909 ld_accounting_date := trunc(ld_accounting_date);
910
911 if p_cc_id is NULL then
912 p_process_status := 'E';
913 p_process_message := 'Account not given';
914 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
915 goto exit_from_procedure;
916 end if;
917
918 if NVL(p_credit_amount, 0) = 0 and NVL(p_debit_amount,0) = 0 then
919 p_process_status := 'E';
920 p_process_message := 'Both Credit and Debit are Zero';
921 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
922 goto exit_from_procedure;
923 end if;
924
925 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
926
927 lv_status := 'NEW' ;
928 insert into gl_interface
929 (
930 status,
931 set_of_books_id,
932 user_je_source_name,
933 user_je_category_name,
934 accounting_date,
935 currency_code,
936 date_created,
937 created_by,
938 actual_flag,
939 entered_cr,
940 entered_dr,
941 transaction_date,
942 code_combination_id,
943 currency_conversion_date,
944 user_currency_conversion_type,
945 currency_conversion_rate,
946 reference1,
947 reference10,
948 reference22,
949 reference23,
950 reference24,
951 reference25,
952 reference26,
953 reference27
954 )
955 VALUES
956 (
957 lv_status , --'NEW',
958 p_set_of_books_id,
959 p_je_source_name,
960 p_je_category_name,
961 ld_accounting_date,
962 p_currency_code,
963 sysdate,
964 p_created_by,
965 'A',
966 p_credit_amount,
967 p_debit_amount,
968 sysdate,
969 p_cc_id,
970 p_currency_conversion_date,
971 p_currency_conversion_type,
972 p_currency_conversion_rate,
973 p_organization_code,
974 lv_reference_10,
975 lv_reference_entry,
976 lv_reference_23,
977 lv_reference_24,
978 lv_reference_26,
979 lv_reference_25,
980 to_char(p_organization_id)
981 );
982
983 << exit_from_procedure >>
984 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath, NULL, 'END'); /* 8 */
985 return;
986
987 EXCEPTION
988
989 WHEN OTHERS then
990 p_process_status := 'E';
991 p_process_message := 'RECEIPT_ACCOUNTING_PKG.gl_entry:' || SQLERRM;
992 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
993 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, NULL, 'END'); /* 9 */
994 RETURN;
995
996 end gl_entry;
997
998 /*--------------------------------------------------------------------------------------------------*/
999 PROCEDURE average_costing
1000 (
1001 p_receiving_account_id in number,
1002 p_new_cost in number,
1003 p_organization_id in number,
1004 p_item_id in number,
1005 p_shipment_line_id in number,
1006 p_transaction_uom in varchar2,
1007 p_transaction_date in date,
1008 p_subinventory in varchar2,
1009 p_func_currency in varchar2,
1010 p_transaction_id in number default null,
1011 p_process_message OUT NOCOPY varchar2,
1012 p_process_status OUT NOCOPY varchar2,
1013 p_codepath in OUT NOCOPY varchar2
1014 ) IS
1015
1016 ln_txn_header_id NUMBER;
1017 ln_costing_group_id pjm_project_parameters.costing_group_id%TYPE;
1018 ln_user_id fnd_user.user_id%type; --File.Sql.35 Cbabu := fnd_global.user_id;
1019
1020 lv_error_msg VARCHAR2(1996);
1021 lv_process_mode VARCHAR2(100);
1022 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
1023
1024 ln_retval NUMBER;
1025 lv_return_status VARCHAR2(2);
1026 lv_msg_data VARCHAR2(300);
1027 ln_msg_cnt NUMBER;
1028 ln_trans_count NUMBER;
1029 ln_overhead_cnt NUMBER;
1030 ln_loop_count NUMBER;
1031 ln_value_change NUMBER;
1032 lv_result BOOLEAN;
1033
1034 lv_transaction_type_name VARCHAR2(30); --File.Sql.35 Cbabu := 'Average cost update';
1035 ln_transaction_type_id MTL_TRANSACTION_TYPES.transaction_type_id%TYPE;
1036 ln_transaction_source_type_id MTL_TRANSACTION_TYPES.transaction_source_type_id%TYPE;
1037 ln_transaction_action_id MTL_TRANSACTION_TYPES.transaction_action_id%TYPE;
1038
1039 CURSOR c_trx_type_dtls(cp_transaction_type_name IN VARCHAR2) IS
1040 SELECT transaction_type_id, transaction_source_type_id, transaction_action_id
1041 FROM mtl_transaction_types
1042 WHERE transaction_type_name = cp_transaction_type_name;
1043
1044 CURSOR c_account_period_id(cp_organization_id IN NUMBER, cp_transaction_date IN DATE) IS
1045 SELECT acct_period_id
1046 FROM org_acct_periods
1047 WHERE period_close_date is null
1048 AND organization_id = cp_organization_id
1049 AND trunc(schedule_close_date) >= trunc(nvl(cp_transaction_date,sysdate))
1050 AND trunc(period_start_date) <= trunc(nvl(cp_transaction_date,sysdate));
1051
1052 CURSOR c_fetch_count_overheads(cp_organization_id in number, cp_item_id in number) IS
1053 /*
1054 || added, vkaranam for Bug 5186391
1055 */
1056 /*commented by vkaranam for bug#9441529
1057 select 1
1058 from CST_ITEM_OVERHEAD_DEFAULTS_V
1059 where organization_id = cp_organization_id
1060 and
1061 ( item_type = 3 -- All items
1062 OR
1063 item_type = (select planning_make_buy_code
1064 from mtl_system_items_fvl a
1065 where organization_id = cp_organization_id
1066 and inventory_item_id = cp_item_id
1067 )
1068 )
1069 and basis_type = 5 ;
1070 /*
1071 Commented by vkaranam for Bug 5186391 */
1072 --added by vkaranam for bug#9441529
1073 SELECT count(1)
1074 FROM cst_item_cost_details
1075 WHERE inventory_item_id = cp_item_id
1076 AND organization_id = cp_organization_id
1077 AND cost_element_id = 2 --Indicates Material OverHead
1078 AND basis_type = 5 --Total Value Basis
1079 AND cost_type_id = (SELECT avg_rates_cost_type_id
1080 FROM mtl_parameters
1081 WHERE organization_id = cp_organization_id
1082 );
1083
1084 CURSOR c_fetch_overhead_rate(cp_organization_id in number, cp_item_id in number) IS
1085 SELECT a.resource_id, a.usage_rate_or_amount, b.absorption_account
1086 FROM cst_item_cost_details a, bom_resources b
1087 WHERE a.resource_id = b.resource_id
1088 AND a.organization_id = cp_organization_id
1089 AND a.inventory_item_id = cp_item_id
1090 AND a.cost_element_id = 2 --Indicates Material OverHead
1091 AND a.basis_type = 5 --Total Value Basis
1092 AND a.cost_type_id = (SELECT c.avg_rates_cost_type_id
1093 FROM mtl_parameters c
1094 WHERE c.organization_id = cp_organization_id
1095 )
1096 ORDER BY a.resource_id;
1097
1098 CURSOR c_cost_group(cp_transaction_id IN NUMBER,
1099 cp_organization_id IN NUMBER) --added for bug#8449597
1100 IS
1101 SELECT costing_group_id
1102 FROM pjm_project_parameters
1103 WHERE project_id IN ( SELECT project_id
1104 FROM po_distributions_all
1105 WHERE po_distribution_id IN (SELECT po_distribution_id
1106 FROM rcv_transactions
1107 WHERE transaction_id = cp_transaction_id
1108 )
1109 )
1110 AND organization_id = cp_organization_id; --added for bug#8449597
1111
1112 cursor c_get_accounts(cp_organization_id IN NUMBER) is
1113 select mp.default_cost_group_id
1114 from mtl_parameters mp
1115 where mp.organization_id = cp_organization_id
1116 and mp.primary_cost_method = 2; --Average
1117
1118 r_get_accounts c_get_accounts%ROWTYPE;
1119
1120 BEGIN
1121
1122 ln_user_id := fnd_global.user_id;
1123 lv_debug := 'Y';
1124 lv_transaction_type_name := 'Average cost update';
1125
1126 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.average_costing', 'START'); /* 1 */
1127
1128 open c_cost_group(p_transaction_id, p_organization_id); --added the parameter p_organization_id for bug#8449597
1129 fetch c_cost_group into ln_costing_group_id;
1130 close c_cost_group;
1131
1132 if lv_debug='Y' THEN
1133 fnd_file.put_line(fnd_file.log, 'ln_costing_group_id:' || ln_costing_group_id);
1134 end if;
1135
1136 -- if cost group is 1, then it means it is Common Cost group which should not be populated
1137 -- so that costing happens at Organization Level instead of CostGroup level
1138 -- Vijay Shankar for Bug#3747390
1139 if ln_costing_group_id = 1 then
1140 ln_costing_group_id := null;
1141 end if;
1142
1143 --If cost group id is 1, then the accounts are picked up from the Organization level
1144 --and hence the Organization Level cost group id needs to be picked up.
1145 if ln_costing_group_id is null then
1146 open c_get_accounts(p_organization_id);
1147 fetch c_get_accounts into r_get_accounts;
1148 close c_get_accounts;
1149
1150 ln_costing_group_id := r_get_accounts.default_cost_group_id;
1151 end if;
1152
1153 OPEN c_trx_type_dtls(lv_transaction_type_name);
1154 FETCH c_trx_type_dtls INTO ln_transaction_type_id, ln_transaction_source_type_id, ln_transaction_action_id;
1155 CLOSE c_trx_type_dtls;
1156
1157 open c_fetch_count_overheads(p_organization_id, p_item_id);
1158 fetch c_fetch_count_overheads into ln_overhead_cnt;
1159 close c_fetch_count_overheads;
1160
1161 if lv_debug='Y' then
1162 fnd_file.put_line(fnd_file.log, 'v_transaction_type_id:' || ln_transaction_type_id
1163 ||', v_transaction_source_type_id:' || ln_transaction_source_type_id
1164 ||', v_transaction_action_id:' || ln_transaction_action_id
1165 ||', p_transaction_uom:' || p_transaction_uom
1166 ||', Transaction Id:' || p_transaction_id ||', ln_overhead_cnt:' || ln_overhead_cnt
1167 );
1168 end if;
1169
1170 -- Normal Clients route
1171 if nvl(ln_overhead_cnt,0) = 0 then --vkaranam for bug 5186391
1172
1173 mti_entry(
1174 p_txn_header_id => ln_txn_header_id,
1175 p_item_id => p_item_id,
1176 p_organization_id => p_organization_id,
1177 p_uom_code => p_transaction_uom,
1178 p_transaction_date => p_transaction_date,
1179 p_transaction_type_id => ln_transaction_type_id,
1180 p_transaction_source_type_id => ln_transaction_source_type_id,
1181 p_transaction_id => p_transaction_id,
1182 p_cost_group_id => ln_costing_group_id,
1183 p_receiving_account_id => p_receiving_account_id,
1184 p_absorption_account_id => p_receiving_account_id,
1185 p_value_change => p_new_cost,
1186 p_new_cost => p_new_cost,
1187 p_usage_rate_or_amount => 0 ,
1188 p_overhead_exists => 'NO' , --Added by Ramananda for the bug 4522484
1189 p_transaction_action_id => ln_transaction_action_id -- Vkaranam for bug#5228227
1190 );
1191
1192 -- if Overheads Exist
1193 else
1194
1195 ln_loop_count := 0;
1196
1197 for c_fetch_records in c_fetch_overhead_rate(p_organization_id, p_item_id)
1198 loop
1199
1200 if ln_loop_count = 0 then
1201 ln_value_change := p_new_cost;
1202 else
1203 ln_value_change := 0;
1204 end if;
1205
1206 mti_entry(
1207 p_txn_header_id => ln_txn_header_id,
1208 p_item_id => p_item_id,
1209 p_organization_id => p_organization_id,
1210 p_uom_code => p_transaction_uom,
1211 p_transaction_date => p_transaction_date,
1212 p_transaction_type_id => ln_transaction_type_id,
1213 p_transaction_source_type_id => ln_transaction_source_type_id,
1214 p_transaction_id => p_transaction_id,
1215 p_cost_group_id => ln_costing_group_id,
1216 p_receiving_account_id => p_receiving_account_id,
1217 p_absorption_account_id => c_fetch_records.absorption_account,
1218 p_value_change => ln_value_change,
1219 p_new_cost => p_new_cost,
1220 p_usage_rate_or_amount => c_fetch_records.usage_rate_or_amount,
1221 p_overhead_exists => 'YES' , --Added by Ramananda for the bug 4522484
1222 p_transaction_action_id => ln_transaction_action_id -- Vkaranam for bug#5228227
1223 );
1224
1225
1226 ln_loop_count := ln_loop_count + 1;
1227
1228 end loop; --end loop for c_fetch_records
1229
1230 end if; --end if for ln_overhead_cnt= 0
1231
1232
1233 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
1234 lv_process_mode := FND_PROFILE.value('TRANSACTION_PROCESS_MODE');
1235
1236 --Indicates Online Mode
1237 if lv_process_mode = '1' then
1238
1239 if p_shipment_line_id is null then
1240 lv_result := FND_SUBMIT.set_mode(TRUE); /*Modified FND call to FND_SUBMIT API - Bug 8504135*/
1241 end if;
1242
1243 --API which populates data into MMT from the data populated earlier in MMTT, so that
1244 --Localization Taxes get added immediately. This API is suggested by the Base Inventory team.
1245 ln_retval := inv_txn_manager_pub.process_transactions (
1246 p_api_version => 1,
1247 p_init_msg_list => fnd_api.g_false ,
1248 p_commit => fnd_api.g_false ,
1249 p_validation_level => fnd_api.g_valid_level_full ,
1250 x_return_status => lv_return_status,
1251 x_msg_count => ln_msg_cnt,
1252 x_msg_data => lv_msg_data,
1253 x_trans_count => ln_trans_count,
1254 p_table => 1,
1255 p_header_id => ln_txn_header_id
1256 );
1257 end if;
1258
1259 << exit_from_procedure >>
1260 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, null, 'END');
1261
1262 EXCEPTION
1263 WHEN OTHERS then
1264 p_process_status := 'E';
1265 p_process_message := 'RECEIPT_ACCOUNTING_PKG.average_costing:' || SQLERRM;
1266 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
1267 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END');
1268 RETURN;
1269
1270 end average_costing;
1271
1272 /*------------------------------------------------------------------------------------------------*/
1273 PROCEDURE rcv_receiving_sub_ledger_entry
1274 (
1275 p_transaction_id in number,
1276 p_organization_id in number,
1277 p_set_of_books_id in number,
1278 p_currency_code in varchar2,
1279 p_credit_amount in number,
1280 p_debit_amount in number,
1281 p_cc_id in number,
1282 p_shipment_line_id in number,
1283 p_item_id in number,
1284 p_source_document_code in varchar2,
1285 p_po_line_location_id in number,
1286 p_requisition_line_id in number,
1287 p_accounting_date in date default null,
1288 p_currency_conversion_date in date default null,
1289 p_currency_conversion_type in varchar2 default null,
1290 p_currency_conversion_rate in number default null,
1291 p_process_message OUT NOCOPY varchar2,
1292 p_process_status OUT NOCOPY varchar2,
1293 p_codepath in OUT NOCOPY varchar2
1294 ) IS
1295
1296 /* Variable Definitions */
1297 ln_unit_price number;
1298 ln_amount number;
1299 ln_user_id fnd_user.user_id%type; --File.Sql.35 Cbabu := fnd_global.user_id;
1300
1301 ld_accounting_date JAI_RCV_SUBLED_ENTRIES.accounting_date%type;
1302 ld_sysdate date; --File.Sql.35 Cbabu := SYSDATE;
1303
1304 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
1305
1306 /* Cursor Definitions */
1307 cursor c_base_sub_ledger_details(cp_transaction_id number) IS
1308 select *
1309 from rcv_receiving_sub_ledger
1310 where rcv_transaction_id = cp_transaction_id
1311 and rownum = 1;
1312
1313 cursor c_fetch_price_override(cp_po_line_location_id number) IS
1314 select price_override
1315 from po_line_locations_all
1316 where line_location_id = cp_po_line_location_id;
1317
1318 cursor c_fetch_list_price(cp_organization_id number, cp_item_id number) IS
1319 select list_price_per_unit
1320 from mtl_system_items
1321 where inventory_item_id = cp_item_id
1322 and organization_id = cp_organization_id;
1323
1324 cursor c_fetch_unit_price(cp_requisition_line_id number) IS
1325 select unit_price
1326 from po_requisition_lines_all
1327 where requisition_line_id = cp_requisition_line_id;
1328
1329 /*Record Definitions */
1330 r_base_subledger_details c_base_sub_ledger_details%rowtype;
1331
1332 BEGin
1333
1334 lv_debug := 'Y';
1335 ln_user_id := fnd_global.user_id;
1336 ld_sysdate := SYSDATE;
1337
1338 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.rcv_receiving_sub_ledger_entry', 'START'); /* 1 */
1339
1340 if NVL(p_credit_amount, 0) = 0 AND NVL(p_debit_amount, 0) = 0 then
1341 fnd_file.put_line( fnd_file.log, 'Both Credit and Debit are 0. So, returning back');
1342 GOTO exit_from_procedure;
1343 end if;
1344
1345 open c_base_sub_ledger_details(p_transaction_id);
1346 fetch c_base_sub_ledger_details into r_base_subledger_details;
1347
1348 if c_base_sub_ledger_details%notfound then
1349 close c_base_sub_ledger_details;
1350
1351 fnd_file.put_line( fnd_file.log, 'Base Entry in rcv_receiving_sub_ledger not found. Hence returning back');
1352 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath, null, 'END'); /* 2 */
1353 goto exit_from_procedure;
1354 end if;
1355
1356 close c_base_sub_ledger_details;
1357
1358 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1359
1360 if p_source_document_code='PO' then
1361
1362 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1363 open c_fetch_price_override(p_po_line_location_id);
1364 fetch c_fetch_price_override into ln_unit_price;
1365 close c_fetch_price_override;
1366
1367 ELSIF p_source_document_code='INVENTORY' then
1368
1369 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1370 open c_fetch_list_price(p_organization_id, p_item_id);
1371 fetch c_fetch_list_price into ln_unit_price;
1372 close c_fetch_list_price;
1373
1374 ELSIF p_source_document_code = 'REQ' then
1375
1376 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1377 open c_fetch_unit_price(p_requisition_line_id);
1378 fetch c_fetch_unit_price into ln_unit_price;
1379 close c_fetch_unit_price;
1380
1381 end if;
1382
1383 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1384 ln_amount := NVL(p_credit_amount,p_debit_amount);
1385
1386 if ln_amount is not NULL and nvl(ln_unit_price,0) <> 0 then
1387 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1388 ln_amount := ln_amount / ln_unit_price;
1389 end if;
1390
1391 if p_accounting_date is NULL then
1392 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1393 ld_accounting_date := TRUNC(ld_sysdate);
1394 ELSE
1395 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1396 ld_accounting_date := TRUNC(p_accounting_date);
1397 end if;
1398
1399 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1400 insert into JAI_RCV_SUBLED_ENTRIES
1401 (SUBLED_ENTRY_ID,
1402 rcv_transaction_id,
1403 set_of_books_id,
1404 je_source_name,
1405 je_category_name,
1406 accounting_date,
1407 currency_code,
1408 date_created_in_gl,
1409 entered_cr,
1410 entered_dr,
1411 transaction_date,
1412 code_combination_id,
1413 currency_conversion_date,
1414 user_currency_conversion_type,
1415 currency_conversion_rate,
1416 actual_flag,
1417 period_name,
1418 chart_of_accounts_id,
1419 functional_currency_code,
1420 je_batch_name,
1421 je_batch_description,
1422 je_header_name,
1423 je_line_description,
1424 reference1,
1425 reference2,
1426 reference3,
1427 reference4,
1428 source_doc_quantity,
1429 created_by,
1430 creation_date,
1431 last_update_date,
1432 last_updated_by,
1433 last_update_login,
1434 from_type,
1435 program_application_id
1436 )
1437 VALUES ( JAI_RCV_SUBLED_ENTRIES_S.nextval,
1438 p_transaction_id,
1439 p_set_of_books_id,
1440 r_base_subledger_details.je_source_name,
1441 r_base_subledger_details.je_category_name,
1442 ld_accounting_date,
1443 p_currency_code,
1444 ld_sysdate,
1445 p_credit_amount,
1446 p_debit_amount,
1447 ld_accounting_date,
1448 p_cc_id,
1449 p_currency_conversion_date,
1450 p_currency_conversion_type,
1451 p_currency_conversion_rate,
1452 r_base_subledger_details.actual_flag,
1453 r_base_subledger_details.period_name,
1454 r_base_subledger_details.chart_of_accounts_id,
1455 r_base_subledger_details.functional_currency_code,
1456 r_base_subledger_details.je_batch_name,
1457 r_base_subledger_details.je_batch_description,
1458 r_base_subledger_details.je_header_name,
1459 r_base_subledger_details.je_line_description,
1460 r_base_subledger_details.reference1,
1461 r_base_subledger_details.reference2,
1462 r_base_subledger_details.reference3,
1463 r_base_subledger_details.reference4,
1464 ln_amount,
1465 ln_user_id,
1466 ld_sysdate,
1467 ld_sysdate,
1468 ln_user_id,
1469 ln_user_id,
1470 'L',
1471 fnd_profile.value('PROG_APPL_ID')
1472 );
1473 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1474
1475 << exit_from_procedure >>
1476 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1477
1478 EXCEPTION
1479
1480 WHEN OTHERS then
1481 p_process_status := 'E';
1482 p_process_message := 'RECEIPT_ACCOUNTING_PKG.rcv_receiving_sub_ledger_entry:' || SQLERRM;
1483 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
1484 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 14 */
1485 RETURN;
1486
1487 end rcv_receiving_sub_ledger_entry;
1488
1489 /*------------------------------------------------------------------------------------------------*/
1490 PROCEDURE mta_entry
1491 (
1492 p_transaction_id in number,
1493 p_reference_account in number,
1494 p_debit_credit_flag in varchar2,
1495 p_tax_amount in number,
1496 p_transaction_date in date default null,
1497 p_currency_code in varchar2 default null,
1498 p_currency_conversion_date in date default null,
1499 p_currency_conversion_type in varchar2 default null,
1500 p_currency_conversion_rate in number default null,
1501 p_source_name in varchar2 default null, /*rchandan for bug#4473022 Start*/
1502 p_category_name in VARCHAR2 default null,
1503 p_accounting_date in DATE default null,
1504 p_reference_23 in varchar2 default null,
1505 p_reference_24 in varchar2 default null,
1506 p_reference_26 in varchar2 default null,/*rchandan for bug#4473022 End*/
1507 p_process_message OUT NOCOPY varchar2,
1508 p_process_status OUT NOCOPY varchar2,
1509 p_codepath in OUT NOCOPY varchar2
1510 ) IS
1511
1512 -- ln_primary_quantity number;
1513 --ln_tax_amount number;
1514 ln_user_id fnd_user.user_id%type; --File.Sql.35 Cbabu := fnd_global.user_id;
1515
1516 --ld_transaction_date date;
1517 ld_sysdate date; --File.Sql.35 Cbabu := SYSDATE;
1518
1519 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
1520 ln_entered_cr NUMBER ;--rchandan for bug#4473022
1521 ln_entered_dr NUMBER ;--rchandan for bug#4473022
1522 lv_reference_10_desc1 VARCHAR2(75);--rchandan for bug#4473022
1523 lv_reference_10_desc2 VARCHAR2(30); --rchandan for bug#4473022
1524 lv_reference_10_desc gl_interface.reference10%type;--rchandan for bug#4473022
1525
1526
1527 --ln_accounting_line_type mtl_transaction_accounts.accounting_line_type%TYPE; --Added by Sanjikum for Bug#3889243
1528
1529 cursor c_fetch_mmt_details(cp_transaction_id number) IS
1530 select *
1531 from mtl_material_transactions mmt
1532 where mmt.rcv_transaction_id = cp_transaction_id;
1533
1534 /* Bug 5243532. Added by Lakshmi Gopalsami
1535 * Removed cursor c_fetch_org_information and variable r_org_dtls
1536 * and implemented caching logic.
1537 */
1538
1539 r_mmt_details c_fetch_mmt_details%rowtype;
1540
1541 --Added the cursor by Sanjikum for Bug#3889243
1542 /*
1543 CURSOR cur_trans_type(cp_transaction_id rcv_transactions.transaction_id%type) IS
1544 SELECT *
1545 FROM rcv_transactions
1546 WHERE transaction_id = cp_transaction_id;
1547 r_rcv_transactions cur_trans_type%ROWTYPE;
1548 *//*commented by rchandan for bug#4473022 */
1549 r_trx c_trx%rowtype;-- rchandan for bug#4473022
1550
1551 /* Bug 5243532. Added by Lakshmi Gopalsami
1552 * Defined variable for implementing caching logic.
1553 */
1554 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1555 lv_org_code org_organization_definitions.organization_code%TYPE;
1556 ln_set_of_books_id gl_ledgers.ledger_id%TYPE;
1557 -- End for bug 5243532
1558
1559
1560 BEGin
1561
1562 lv_debug := 'Y';
1563 ln_user_id := fnd_global.user_id;
1564 ld_sysdate := SYSDATE;
1565 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';--rchandan for bug#4473022
1566 lv_reference_10_desc2 := ' For the Transaction Type ';--rchandan for bug#4473022
1567
1568 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.mta_entry', 'START'); /* 1 */
1569
1570 if NVL(p_tax_amount, 0) = 0 then
1571 fnd_file.put_line( fnd_file.log, 'Tax amount is 0. So, returning back');
1572 GOTO exit_from_procedure;
1573 end if;
1574
1575 open c_fetch_mmt_details(p_transaction_id);
1576 fetch c_fetch_mmt_details into r_mmt_details;
1577 close c_fetch_mmt_details;
1578
1579 if lv_debug ='Y' then
1580 fnd_file.put_line( fnd_file.log, '5.1 r_mmt_details.primary_quantity ' || r_mmt_details.primary_quantity ||' '||r_mmt_details.transaction_id);
1581 end if;
1582 /* Bug 5243532. Added by Lakshmi Gopalsami
1583 * Removed the commented codes.
1584 */
1585 open c_trx(p_transaction_id);-- rchandan for bug#4473022
1586 fetch c_trx into r_trx;
1587 close c_trx;
1588 /* Bug 5243532. Added by Lakshmi Gopalsami
1589 * Removed cursor c_fetch_org_information and
1590 * implemented caching logic.
1591 */
1592
1593 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1594 (p_org_id => r_trx.organization_id);
1595 lv_org_code := l_func_curr_det.organization_code;
1596 ln_set_of_books_id := l_func_curr_det.ledger_id;
1597
1598 /* Bug 5243532. Added by Lakshmi Gopalsami
1599 * Removed the commented codes.
1600 */
1601 IF p_debit_credit_flag = 'N' THEN /* rchandan for bug#4473022 start*/
1602 ln_entered_cr := p_tax_amount;
1603 ln_entered_dr := null;
1604 ELSIF p_debit_credit_flag = 'Y' THEN
1605 ln_entered_cr := null;
1606 ln_entered_dr := p_tax_amount;
1607 END IF;
1608 lv_reference_10_desc := lv_reference_10_desc1 || r_trx.receipt_num ||lv_reference_10_desc2 ||r_trx.transaction_type ||' of Parent Trx Type ' || r_trx.parent_transaction_type;
1609 gl_entry
1610 ( /* Bug 5243532. Added by Lakshmi Gopalsami
1611 * Changed the parameter to lv_org_code and ln_set_of_books_id
1612 * instead of r_org_dtls
1613 */
1614 p_organization_id => r_trx.organization_id,
1615 p_organization_code => lv_org_code,
1616 p_set_of_books_id => ln_set_of_books_id,
1617 p_credit_amount => ln_entered_cr,
1618 p_debit_amount => ln_entered_dr,
1619 p_cc_id => p_reference_account,
1620 p_je_source_name => p_source_name,
1621 p_je_category_name => p_category_name,
1622 p_created_by => ln_user_id,
1623 p_accounting_date => p_accounting_date,
1624 p_currency_code => p_currency_code,
1625 p_currency_conversion_date => p_currency_conversion_date,
1626 p_currency_conversion_type => p_currency_conversion_type,
1627 p_currency_conversion_rate => p_currency_conversion_rate,
1628 p_reference_10 => lv_reference_10_desc,
1629 p_reference_23 => p_reference_23,
1630 p_reference_24 => p_reference_24,
1631 p_reference_25 => r_mmt_details.transaction_id,
1632 p_reference_26 => p_reference_26,
1633 p_process_message => p_process_message,
1634 p_process_status => p_process_status,
1635 p_codepath => p_codepath
1636 );
1637 if p_process_status IN ('E', 'X') then
1638 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1639 goto exit_from_procedure;
1640 end if; /* rchandan for bug#4473022 end*/
1641
1642 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1643
1644 << exit_from_procedure >>
1645 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath, null, 'END'); /* 10 */
1646
1647 EXCEPTION
1648 WHEN OTHERS then
1649 p_process_status := 'E';
1650 p_process_message := 'RECEIPT_ACCOUNTING_PKG.mta_entry:' || SQLERRM;
1651 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
1652 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 11 */
1653 RETURN;
1654 end mta_entry;
1655
1656 /*------------------------------------------------------------------------------------------------*/
1657 PROCEDURE rcv_transactions_update
1658 (
1659 p_transaction_id in number,
1660 p_costing_amount in number,
1661 p_process_message OUT NOCOPY varchar2,
1662 p_process_status OUT NOCOPY varchar2,
1663 p_codepath in OUT NOCOPY varchar2
1664 ) IS
1665
1666 BEGIN
1667
1668 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.rcv_transactions_update', 'START'); /* 1 */
1669
1670 UPDATE rcv_transactions
1671 SET po_unit_price = nvl(po_unit_price,0) + nvl(p_costing_amount,0)
1672 WHERE transaction_id = p_transaction_id;
1673
1674 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath, null, 'END'); /* 3 */
1675
1676 EXCEPTION
1677 WHEN OTHERS then
1678 p_process_status := 'E';
1679 p_process_message := 'RECEIPT_ACCOUNTING_PKG.rcv_transactions_update:' || SQLERRM;
1680 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
1681 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 4 */
1682 RETURN;
1683
1684 end rcv_transactions_update;
1685
1686 end jai_rcv_accounting_pkg;