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