[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_DELIVER_RTR_PKG
Source
1 PACKAGE BODY jai_rcv_deliver_rtr_pkg AS
2 /* $Header: jai_rcv_del_rtr.plb 120.15.12020000.2 2012/07/15 10:46:19 nkodakan ship $ */
3 /*----------------------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY for FILENAME: jai_rcv_deliver_rtr_pkg.sql
5 S.No dd/mm/yyyy Author and Details
6 ------------------------------------------------------------------------------------------------------------------------------
7 1 26/07/2004 Nagaraj.s for Bug# 3496408, Version:115.0
8 This Package is coded for Corrections Enhancement to pass Deliver and RTR Entries.
9
10 2 26/10/2004 Vijay Shankar for Bug#3949518 (3927371), Version:115.1
11 Modified the p_subinventory_code parameter passed to Average_Costing and Expense_Accounting procedure to use
12 DELIVER Subinventory incase RTR subinventory is NULL
13
14 3 10/11/2004 Vijay Shankar for Bug#4003518, Version:115.2
15 Modified the PROCESS_TRANSACTION definition to DEFAULT 'N' for p_simulate parameter. without this, its not a
16 problem in Oracle8i, however it is problem in 9i and thus the bugfix
17
18 4 12/12/2004 Vijay Shankar for Bug#4038034 (4038024), FileVersion:115.3
19 Redundant piece of code checking related to tax_amount calculation in commented as these are already taken
20 care in INCLUDE_CENVAT_IN_COSTING function and this being used in the check. This redundant piece of code is
21 causing the issue specified in the bug
22
23 5 23/12/2004 Vijay Shankar for Bug#4071458, FileVersion:115.4
24 Modified an IF condition, so that the procedure process_transaction will not error out if ln_total is 0. check
25 is modified to fail only if both ln_total and ln_non_modvat_amount are 0.
26
27 6 15/12/2004 Vijay Shankar for Bug#4068823, 3940588, FileVersion:115.5
28 Following are the changes made for the purpose of Service Tax and Education Cess Enhancements
29 - Added two new parameters p_process_special_Reason and amount in process_transaction to implement the functionality
30 of Deferred Claim for RECEIPTS DEPLUG of existing code. If these parameters were passed with values related to
31 Cenvat Unclaim, then process_special_amount is taken as ln_total instead of calculating it from transaction
32 and proceed further to do either of Costing or Expensing
33
34 - Uncommented the call to OPM_COSTING to support OPM Receipts Functionality also
35 - INCLUDE_CENVAT_IN_COSTING is modified to include some more checks to return a value to caller
36 - Modfied the Main Cursor in DELIVER_RTR_RECO_NONEXCISE procedure not to pass INDIVIDUAL accounting for taxes
37 related to 'Service Tax India' and 'SERVICE_EDUCATION_CESS' tax types
38 - Modified the procedures expense_accounting, average_costing, standard_costing, opm_costing to include parameters
39 p_process_special_Reason in the signature. this is used to pass a different value for JAI_RCV_JOURNAL_ENTRIES.acct_nature,
40 for accounting_entries identification
41
42
43 7 23/02/2005 Vijay Shankar for Bug#4179823, FileVersion:115.6
44 Modified an IF condition in include_cenvat_in_costing function to allow FGIN items in case of RMA Receipts.
45 Previously it is allowing for ISO receipts only incase of FGIN items which is wrong
46
47 8 17/03/2005 Vijay Shankar for Bug#4229164, FileVersion:115.7
48 Modified the code in jai_rcv_deliver_rtr_pkg.opm_costing procedure to consider currency rate also which is passed as parameter to the procedure.
49 - Added a new parameter p_currency_conversion_rate to jai_rcv_deliver_rtr_pkg.opm_costing procedure
50 - started passing the value to newly added parameter by fetching the value from JAI_RCV_TRANSACTIONS table
51
52 9 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.8
53 modified the procedures to support the functinoality of "VAT UNCLAIM" in similar lines to "CENVAT UNCLAIM".
54 This would be called from jai_rcv_rgm_claims_pkg incase of VAT NOCLAIM selected by user for a receipt line incase
55 DELIVER/RTR or related CORRECTs happened
56
57 10 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
58 Code is modified due to the Impact of Receiving Transactions DFF Elimination
59
60 * High Dependancy for future Versions of this object *
61
62 11 08-Jun-2005 Version 116.2 jai_rcv_del_rtr -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
63 as required for CASE COMPLAINCE.
64
65 12. 13-Jun-2005 File Version: 116.3
66 Ramananda for bug#4428980. Removal of SQL LITERALs is done
67
68 13. 7-Jul-2005 File Version: 116.4
69 rchandan for bug#4473022. Modified the object as part of SLA impact uptake.
70 While calling jai_rcv_accounting_pkg.process_transaction apropriate values are passed for
71 reference parameters instead of NULL.
72
73
74 14. 01/11/2006 SACSETHI for bug 5228046, File version 120.3
75 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
76 This bug has datamodel and spec changes.
77
78 15. 27/Apr/2007 CSahoo for bug#5989740, File Version 120.4
79 Forward Porting of 11i bug#5907436
80 handling secondary and higher education cess
81 added the sh cess types.
82
83 16. 20-Nov-2008 Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch
84 Bug 6681800 not yet ported to 12.1
85
86 17. 18-JAN-2010 JMEENA for bug#9233826
87 In the procedure get_tax_amount_breakup modified the calculation of ln_non_modvat_amount for inclusive tax.
88
89 18. 22-Dec-2011 Bug 13514510
90 Issue - Cenvat claim is not supported for ISO receipt of FGIN/FGEX items.
91 Fix - Added 'REQ' to the list of source document types which are allowed for cenvat claim when item class is FGIN/FGEX.
92
93 19. 20-Jan-2012 Bug 13494816
94 Issue - ROUNDING DIFFERENCE IN RETURN TO VENDOR ACCOUNTING
95 Fix Details : Changes are done in get_tax_amount_breakup procedure to
96 round the excise taxes with the rounding factor available in cmn taxes
97 table
98
99
100 DEPENDANCY:
101 -----------
102 IN60105D2 + 3496408
103 IN60106 + 4239736 + 4245089 + 4346453
104
105 16. 28-NOV-2007 Added by Jia Li for India tax inclusive
106 17. 19-Mar-2008 Modified by Jia Li for Bug#6877290
107 Issue: UNIT COST CALCULATE IS INCORRECT IN AVG ORGANIZATION
108 Fixed: Modified procedure get_tax_amount_breakup,
109 change modvat_amount and non_modvat_amount calculate position,
110 moved tax_amount calculate into inclusive_flag clause
111
112 18. 06-04-2009 FP 12.0: 7539200:RECEIVING AND DELIVERY ACC VISIBLE FROM LOCALISATION SCREEN
113 Fix details: Commented the code which inserts accounting
114 entries in jai_rcv_journal_entries for OPM costing
115
116 19. 15-Apr_2010 Bo Li For bug9305067 Replace the old attribute_category columns for JAI_RCV_TRANSACTIONS
117 with new meaningful one
118 26-nov-2010 Bug 10335708
119 Description : Cenvat credit should be claimable, even if the items are delivered
120 to EXPENSE (not tracked as Inventory). Also, the quantity register should be updated
121 for Issue as soon as item is delivered (or cenvat claimed, whichever happens later).
122 To address this requirement, following changes are done in this package:
123 1. Added procedure pr_issue_expense_delivery (and a private procedure pr_issue_auto_trans).
124 2. Modified include_cenvat_in_costing function so that it will not return Y if the
125 destination is EXPENSE and item class is RM, CG or CC.
126 3. Called the pr_issue_expense_delivery procedure from process_transaction to create
127 Issue entry in quantity register.
128 ----------------------------------------------------------------------------------------------------------------------------*/
129
130 PROCEDURE process_transaction
131 (
132 p_transaction_id IN NUMBER,
133 p_simulate IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
134 p_codepath IN OUT NOCOPY VARCHAR2,
135 p_process_message OUT NOCOPY VARCHAR2,
136 p_process_status OUT NOCOPY VARCHAR2,
137 -- Vijay Shankar for Bug#4068823. RECEIPTS DELUG
138 p_process_special_source IN VARCHAR2 DEFAULT NULL,
139 p_process_special_amount IN NUMBER DEFAULT NULL
140 ) is
141
142 /* Cursor Definitions */
143 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
144 SELECT *
145 FROM JAI_RCV_TRANSACTIONS
146 WHERE transaction_id = cp_transaction_id;
147
148 CURSOR c_base_line_dtls(cp_transaction_id IN NUMBER) IS
149 SELECT quantity, unit_of_measure, source_doc_unit_of_measure, source_doc_quantity
150 from rcv_transactions
151 where transaction_id = cp_transaction_id;
152
153 CURSOR c_rcv_trx(cp_transaction_id IN NUMBER) IS
154 SELECT *
155 FROM rcv_transactions
156 where transaction_id = cp_transaction_id;
157
158 CURSOR c_mtl_trx(cp_organization_id IN NUMBER) IS
159 /* Bug 4941701. Added by Lakshmi Gopalsami
160 For performance fix. SQL id - 14829562
161 Changed the reference mtl_parameters from mtl_parameters_view
162 and selected process_enabled_flag in the cursor. */
163 SELECT process_enabled_flag
164 FROM mtl_parameters
165 WHERE Organization_id = cp_organization_id;
166
167 /* Record Declarations */
168 r_trx c_trx%rowtype;
169 r_base_line_dtls c_base_line_dtls%rowtype;
170 r_rcv_trx c_rcv_trx%rowtype;
171 r_rcv_dlry_trx c_rcv_trx%rowtype; -- Bug#3949518 (3927371)
172 r_mtl_trx c_mtl_trx%rowtype;
173 r_dlry_trx c_trx%rowtype;
174
175
176 /* Variable Declarations */
177 lv_procedure_name VARCHAR2(60); --File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.process_transaction';
178 -- lv_register_type VARCHAR2(1); --Either A or C.
179 lv_opm_organization_flag mtl_parameters_view.process_enabled_flag%type;
180 lv_statement_id VARCHAR2(4);
181 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
182 lv_accounting_type VARCHAR2(30);
183 lv_include_cenvat_in_costing VARCHAR2(1);
184 lv_destination_type rcv_transactions.destination_type_code%type;
185
186
187 /* Number Declarations */
188 ln_apportion_factor NUMBER; --File.Sql.35 Cbabu := 1; -- default value added by Vijay Shankar for Bug#4068823 for RECEIPTS DEPLUG
189 ln_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
190 ln_non_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
191 ln_other_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
192 ln_total NUMBER; --File.Sql.35 Cbabu := 0;
193 ln_opm_total NUMBER; --File.Sql.35 Cbabu := 0;
194 ln_receiving_account_id rcv_parameters.receiving_account_id%type;
195 ln_dlry_trx_id JAI_RCV_TRANSACTIONS.transaction_id%type;
196
197 ln_receive_trx_id JAI_RCV_TRANSACTIONS.transaction_id%type;
198 lv_temp VARCHAR2(50);
199
200 lv_cenvat_costed_flag VARCHAR2(15);
201
202 BEGIN
203 lv_procedure_name := 'jai_rcv_deliver_rtr_pkg.process_transaction';
204 lv_debug := jai_constants.yes;
205 ln_apportion_factor := 1; -- default value added by Vijay Shankar for Bug#4068823 for RECEIPTS DEPLUG
206 ln_modvat_amount := 0;
207 ln_non_modvat_amount := 0;
208 ln_other_modvat_amount := 0;
209 ln_total := 0;
210 ln_opm_total := 0;
211
212 -- this is to identify the path in SQL TRACE file if any problem occured
213 SELECT 'jai_rcv_deliver_rtr_pkg-'||p_transaction_id INTO lv_temp FROM DUAL;
214
215 FND_FILE.put_line( FND_FILE.log, '~~~~~~ Start of jai_rcv_deliver_rtr_pkg.process_transaction. Time:'||to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss'));
216
217 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.process_transaction', 'START'); /* 1 */
218
219 /* Fetch all the information from JAI_RCV_TRANSACTIONS */
220 OPEN c_trx(p_transaction_id);
221 FETCH c_trx INTO r_trx;
222 CLOSE c_trx;
223
224 lv_statement_id := '1';
225 ln_receive_trx_id := r_trx.tax_transaction_id;
226
227 /* Vijay Shankar for Bug#4068823
228 jai_rcv_trx_processing_pkg.get_ancestor_id
229 (
230 p_transaction_id => r_trx.transaction_id,
231 p_shipment_line_id => r_trx.shipment_line_id,
232 p_required_trx_type => 'RECEIVE'
233 );
234 */
235
236 /* Fetch all the information from rcv_transactions for transaction type RECEIVE*/
237 OPEN c_base_line_dtls(ln_receive_trx_id);
238 FETCH c_base_line_dtls INTO r_base_line_dtls;
239 CLOSE c_base_line_dtls;
240
241 lv_statement_id := '2';
242 if r_base_line_dtls.quantity = 0 THEN
243 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
244 p_process_status := 'E';
245 p_process_message := 'The Quantity in rcv_transactions for RECEIVE line is Zero';
246 goto exit_from_procedure;
247 end if;
248
249 --p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
250 --lv_statement_id := '2.1';
251
252 /* Fetch the information of certain columns which are not present in JAI_RCV_TRANSACTIONS */
253 OPEN c_rcv_trx(p_transaction_id);
254 FETCH c_rcv_trx into r_rcv_trx;
255 CLOSE c_rcv_trx;
256
257 lv_statement_id := '3';
258 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
259
260 /*bug 10335708 - Production Issue transaction for the quantity register when
261 cenvat is claimed and destination is to expense.*/
262 if -- (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type = 'DELIVER') or Vijay Shankar for Bug#4038034
263 r_trx.transaction_type = 'DELIVER'
264 then
265 p_codepath := jai_general_pkg.plot_codepath(5.1, p_codepath);
266 lv_destination_type := r_trx.destination_type_code;
267
268 elsif -- 'DELIVER' in the following if elsif condition is added by Vijay Shankar for Bug#4038034
269 (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')) or
270 r_trx.transaction_type = 'RETURN TO RECEIVING'
271 then
272 p_codepath := jai_general_pkg.plot_codepath(5.2, p_codepath);
273 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
274 (
275 r_trx.transaction_id,
276 r_trx.shipment_line_id,
277 'DELIVER'
278 );
279
280 open c_trx(ln_dlry_trx_id);
281 fetch c_trx into r_dlry_trx;
282 close c_trx;
283
284 lv_destination_type := r_dlry_trx.destination_type_code;
285
286 OPEN c_rcv_trx(ln_dlry_trx_id);
287 FETCH c_rcv_trx into r_rcv_dlry_trx;
288 CLOSE c_rcv_trx;
289
290 end if;
291 p_codepath := jai_general_pkg.plot_codepath(5.3, p_codepath);
292 IF lv_destination_type = 'EXPENSE'
293 THEN
294 p_codepath := jai_general_pkg.plot_codepath(5.4, p_codepath);
295 pr_issue_expense_delivery
296 ( pn_transaction_id => r_trx.transaction_id,
297 pv_process_message => p_process_message,
298 pv_process_status => p_process_status,
299 p_codepath => p_codepath
300 );
301 END IF;
302
303 /*end bug 10335708*/
304
305
306 /* Fetch the Receiving Account Id */
307 ln_receiving_account_id := receiving_account
308 (
309 p_organization_id => r_trx.organization_id,
310 p_process_message => p_process_message,
311 p_process_status => p_process_status,
312 p_codepath => p_codepath
313 );
314
315 if p_process_status in ('E', 'X') THEN
316 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
317 goto exit_from_procedure;
318 end if;
319
320 lv_statement_id := '4';
321
322 if (r_trx.transaction_type ='CORRECT' and r_trx.parent_transaction_type = 'DELIVER')
323 or r_trx.transaction_type = 'DELIVER'
324 then
325 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
326 lv_accounting_type := 'REGULAR';
327 elsif (r_trx.transaction_type ='CORRECT' and r_trx.parent_transaction_type = 'RETURN TO RECEIVING')
328 or r_trx.transaction_type = 'RETURN TO RECEIVING'
329 then
330 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
331 lv_accounting_type := 'REVERSAL';
332 end if;
333
334 /* following condition added by Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
335 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl.
336 */
337 if nvl(p_process_special_source, 'XX') NOT IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim) then
338
339 -- following gets executed only for NORMAL DELIVER and RTR transactions and not for UNCLAIM Processing till <<start_of_actual_processing>>
340 lv_statement_id := '5';
341 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
342
343 /* Apportion Factor */
344 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
345 (p_transaction_id => r_trx.transaction_id);
346
347 if lv_debug ='Y' then
348 fnd_file.put_line( fnd_file.log, '1.1 ln_apportion_factor ' || ln_apportion_factor );
349 end if;
350
351 /*Step 1 : Call Individual Tax Entries As this should happen in any case if
352 Recoverable Taxes other than Excise Exist */
353 deliver_rtr_reco_nonexcise
354 (
355 p_transaction_id => r_trx.transaction_id,
356 p_transaction_date => r_trx.transaction_date,
357 p_organization_id => r_trx.organization_id,
358 p_transaction_type => r_trx.transaction_type,
359 p_parent_transaction_type => r_trx.parent_transaction_type,
360 p_receipt_num => r_trx.receipt_num,
361 p_shipment_line_id => r_trx.shipment_line_id,
362 p_currency_conversion_rate => r_trx.currency_conversion_rate,
363 p_apportion_factor => ln_apportion_factor,
364 p_receiving_account_id => ln_receiving_account_id,
365 p_accounting_type => lv_accounting_type,
366 p_simulate => p_simulate,
367 p_process_message => p_process_message,
368 p_process_status => p_process_status,
369 p_codepath => p_codepath
370 );
371
372 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
373 if p_process_status IN ('E', 'X') THEN
374 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
375 goto exit_from_procedure;
376 end if;
377
378 /* Get Register Type */
379 -- lv_register_type := jai_general_pkg.get_rg_register_type(p_item_class => r_trx.item_class);
380
381 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
382 get_tax_amount_breakup
383 (
384 p_shipment_line_id => r_trx.shipment_line_id,
385 p_transaction_id => r_trx.transaction_id,
386 p_curr_conv_rate => r_trx.currency_conversion_rate,
387 p_excise_amount => ln_modvat_amount,
388 p_non_modvat_amount => ln_non_modvat_amount ,
389 p_other_modvat_amount => ln_other_modvat_amount ,
390 p_process_message => p_process_message,
391 p_process_status => p_process_status,
392 p_codepath => p_codepath
393 );
394
395 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
396 if p_process_status in ('E', 'X') THEN
397 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
398 goto exit_from_procedure;
399 end if;
400
401 lv_include_cenvat_in_costing := include_cenvat_in_costing
402 (
403 p_transaction_id => p_transaction_id,
404 p_process_message => p_process_message,
405 p_process_status => p_process_status,
406 p_codepath => p_codepath
407 );
408
409 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
410 if p_process_status in ('E', 'X') THEN
411 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
412 goto exit_from_procedure;
413 end if;
414
415 /* Logic to arrive at the Total for which Costing or Expense Accounting has to be done */
416 if lv_include_cenvat_in_costing ='Y' then
417 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
418 ln_total := nvl(ln_non_modvat_amount,0) + nvl(ln_modvat_amount,0);
419 else
420 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
421 ln_total := nvl(ln_non_modvat_amount,0);
422 end if;
423
424 end if; -- end of p_process_special_source not in ( jai_constants.cenvat_noclaim ...
425
426 /* following condition added by Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
427 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
428 IF p_process_special_source IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim) THEN
429 ln_total := p_process_special_amount;
430 p_codepath := jai_general_pkg.plot_codepath(18.1, p_codepath);
431 ELSE
432 ln_total := nvl(ln_total,0); --In case the Total is Null.
433 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
434 END IF;
435
436 if lv_debug='Y' THEN
437 fnd_file.put_line( fnd_file.log, ' 1.3 ln_modvat_amount ='|| ln_modvat_amount
438 ||', ln_non_modvat_amount =' || ln_non_modvat_amount
439 ||', ln_other_modvat_amount ='|| ln_other_modvat_amount
440 ||', ln_total ='|| ln_total);
441 end if;
442
443 if ln_total = 0 then
444 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
445 -- Vijay Shankar for Bug#4071458
446 -- following if condition added to return successfully so that Individual Accounting happens without Costing/Expense Accounting
447 if ln_other_modvat_amount <> 0 then
448 p_process_status := 'Y';
449 else
450
451 p_process_status := 'X';
452 p_process_message := 'Non cenvatable/recoverable taxes doesnot exist. As a result, no Accounting/Costing';
453 end if;
454
455 goto exit_from_procedure;
456 else
457 /*Proportionate the Total with the Quantity of this transaction */
458 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
459 ln_opm_total := ln_total; /*This Total is required as in OPM Organization, costing would be based on source doc quantity */
460 ln_total := ln_total * ln_apportion_factor; /*This Amount would be the one which would be used for costing */
461 end if;
462
463 /* Logic to arrive at the Total for which Costing or Expense Accounting Ends here */
464 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
465 open c_mtl_trx(r_trx.organization_id);
466 fetch c_mtl_trx into r_mtl_trx;
467 close c_mtl_trx;
468
469 /* Following Hierarchy is followed for Deciding on what has to happen
470 1. Expense Routing
471 2. OPM Costing
472 3. Average Costing
473 4. Standard Costing.
474 */
475
476 if -- (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type = 'DELIVER') or Vijay Shankar for Bug#4038034
477 r_trx.transaction_type = 'DELIVER'
478 then
479
480 lv_destination_type := r_trx.destination_type_code;
481
482 elsif -- 'DELIVER' in the following if elsif condition is added by Vijay Shankar for Bug#4038034
483 (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')) or
484 r_trx.transaction_type = 'RETURN TO RECEIVING'
485 then
486
487 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
488 (
489 r_trx.transaction_id,
490 r_trx.shipment_line_id,
491 'DELIVER'
492 );
493
494 open c_trx(ln_dlry_trx_id);
495 fetch c_trx into r_dlry_trx;
496 close c_trx;
497
498 lv_destination_type := r_dlry_trx.destination_type_code;
499
500 OPEN c_rcv_trx(ln_dlry_trx_id);
501 FETCH c_rcv_trx into r_rcv_dlry_trx;
502 CLOSE c_rcv_trx;
503
504 end if;
505
506 if lv_destination_type ='EXPENSE'
507 or r_trx.inv_asset_flag ='N'
508 or r_trx.inv_item_flag ='N'
509 or r_trx.base_asset_inventory = 2
510 then
511
512 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
513
514 expense_accounting
515 (
516 p_transaction_id => r_trx.transaction_id,
517 p_transaction_date => r_trx.transaction_date,
518 p_organization_id => r_trx.organization_id,
519 p_transaction_type => r_trx.transaction_type,
520 p_parent_transaction_type => r_trx.parent_transaction_type,
521 p_receipt_num => r_trx.receipt_num,
522 p_shipment_line_id => r_trx.shipment_line_id,
523 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
524 p_accounted_amount => ln_total,
525 p_receiving_account_id => ln_receiving_account_id,
526 p_source_document_code => r_rcv_trx.source_document_code,
527 p_po_distribution_id => r_rcv_trx.po_distribution_id,
528 p_po_line_location_id => r_rcv_trx.po_line_location_id,
529 p_inventory_item_id => r_trx.inventory_item_id,
530 p_accounting_type => lv_accounting_type,
531 p_simulate => p_simulate,
532 p_process_message => p_process_message,
533 p_process_status => p_process_status,
534 p_codepath => p_codepath,
535 p_process_special_source => p_process_special_source
536 );
537
538 p_codepath := jai_general_pkg.plot_codepath(24, p_codepath); /* 24 */
539
540 if p_process_status in ('E', 'X') THEN
541 p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
542 goto exit_from_procedure;
543 end if;
544
545 elsif nvl(r_mtl_trx.process_enabled_flag,'N') ='Y' then /* OPM Costing Route */
546
547 /* In case of OPM Organizations, No Costing Impact is present in case of RTR Transactions */
548
549
550 if (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type ='RETURN TO RECEIVING')
551 or r_trx.transaction_type ='RETURN TO RECEIVING'
552 then
553 p_codepath := jai_general_pkg.plot_codepath(26, p_codepath); -- 26
554 goto exit_from_procedure;
555 end if;
556
557 --commented as opm is not taken part of this enhancement.
558 -- Call to the following procedure is openedup by Vijay Shankar for Bug#4068823 for RECEIPT DEPLUG
559 opm_costing (
560 p_transaction_id => r_trx.transaction_id,
561 p_transaction_date => r_trx.transaction_date,
562 p_organization_id => r_trx.organization_id,
563 p_costing_amount => ln_opm_total, --To be checked /* INR Total */
564 p_receiving_account_id => ln_receiving_account_id,
565 p_rcv_unit_of_measure => r_base_line_dtls.unit_of_measure, --Indicates UOM of RECEIVE Line
566 p_rcv_source_unit_of_measure => r_base_line_dtls.source_doc_unit_of_measure, --Indicates Source UOM of RECEIVE Line
567 p_rcv_quantity => r_base_line_dtls.quantity, -- Indicates Quantity of RECEIVE Line
568 p_source_doc_quantity => r_base_line_dtls.source_doc_quantity, -- Indicates Source doc Quantity of RECEIVE Line
569 p_source_document_code => r_rcv_trx.source_document_code,
570 p_po_distribution_id => r_rcv_trx.po_distribution_id,
571 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
572 p_simulate => p_simulate,
573 p_process_message => p_process_message,
574 p_process_status => p_process_status,
575 p_codepath => p_codepath,
576 p_process_special_source => p_process_special_source,
577 /* following parameter added by Vijay Shankar for Bug#4229164 */
578 p_currency_conversion_rate => nvl(r_trx.currency_conversion_rate, 1)
579 );
580
581 if p_process_status in ('E', 'X') THEN
582 goto exit_from_procedure;
583 end if;
584
585 elsif r_trx.costing_method = 2 then
586
587 p_codepath := jai_general_pkg.plot_codepath(27, p_codepath); /* 27 */
588 average_costing
589 (
590 p_transaction_id => r_trx.transaction_id,
591 p_transaction_date => r_trx.transaction_date,
592 p_organization_id => r_trx.organization_id,
593 p_parent_transaction_type => r_trx.parent_transaction_type,
594 p_transaction_type => r_trx.transaction_type,
595 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
596 p_costing_amount => ln_total,
597 p_receiving_account_id => ln_receiving_account_id,
598 p_source_document_code => r_rcv_trx.source_document_code,
599 p_po_distribution_id => r_rcv_trx.po_distribution_id,
600 p_unit_of_measure => r_rcv_trx.unit_of_measure,
601 p_inventory_item_id => r_trx.inventory_item_id,
602 p_accounting_type => lv_accounting_type,
603 p_simulate => p_simulate,
604 p_process_message => p_process_message,
605 p_process_status => p_process_status,
606 p_codepath => p_codepath,
607 p_process_special_source => p_process_special_source
608 );
609
610 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath); /* 28 */
611 if p_process_status in ('E', 'X') THEN
612 p_codepath := jai_general_pkg.plot_codepath(29, p_codepath); /* 29 */
613 goto exit_from_procedure;
614 end if;
615
616
617 elsif r_trx.costing_method = 1 then
618
619 p_codepath := jai_general_pkg.plot_codepath(30, p_codepath); /* 30 */
620
621 standard_costing
622 (
623 p_transaction_id => r_trx.transaction_id,
624 p_transaction_date => r_trx.transaction_date,
625 p_organization_id => r_trx.organization_id,
626 p_parent_transaction_type => r_trx.parent_transaction_type,
627 p_transaction_type => r_trx.transaction_type,
628 p_costing_amount => ln_total,
629 p_receiving_account_id => ln_receiving_account_id,
630 p_accounting_type => lv_accounting_type,
631 p_simulate => p_simulate,
632 p_process_message => p_process_message,
633 p_process_status => p_process_status,
634 p_codepath => p_codepath,
635 p_process_special_source => p_process_special_source
636 );
637
638 p_codepath := jai_general_pkg.plot_codepath(31, p_codepath); /* 31 */
639 if p_process_status in ('E', 'X') THEN
640 p_codepath := jai_general_pkg.plot_codepath(32, p_codepath); /* 32 */
641 goto exit_from_procedure;
642 end if;
643
644 end if; --r_mtl_trx.process_enabled_flag
645
646 /* following was coded to support UNCLAIM functionality during RECEIPTS DEPLUG. Vijay Shankar for Bug#4068823
647 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
648 IF lv_include_cenvat_in_costing = 'Y'
649 or p_process_special_source IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim)
650 THEN
651 p_codepath := jai_general_pkg.plot_codepath(33, p_codepath);
652 lv_cenvat_costed_flag := jai_constants.yes;
653 ELSE
654 p_codepath := jai_general_pkg.plot_codepath(34, p_codepath);
655 lv_cenvat_costed_flag := jai_constants.no;
656 END IF;
657
658 IF r_trx.cenvat_costed_flag IS NULL --Modified by Bo Li for replacing the attribute2 with cenvat_costed_flag
659 OR (/*r_trx.attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag
660 AND*/nvl(r_trx.cenvat_costed_flag,jai_constants.no) <> jai_constants.yes)--Modified by Bo Li for replacing the attribute2 with cenvat_costed_flg
661 THEN
662
663 p_codepath := jai_general_pkg.plot_codepath(35, p_codepath);
664
665 --Modified by Bo Li for replacing the update_attributes with update_cenvat_costed_flag Begin
666 --------------------------------------------------------------------------------------------
667 /*jai_rcv_transactions_pkg.update_attributes(
668 p_transaction_id => p_transaction_id,
669 p_attribute1 => jai_rcv_deliver_rtr_pkg.cenvat_costed_flag,
670 p_attribute2 => lv_cenvat_costed_flag
671 );*/
672
673 jai_rcv_transactions_pkg.update_cenvat_costed_flag(
674 p_transaction_id => p_transaction_id,
675 p_cenvat_costed_flag => lv_cenvat_costed_flag
676 );
677 --------------------------------------------------------------------------------------------
678 --Modified by Bo Li for replacing the update_attributes with update_cenvat_costed_flag End
679 END IF;
680
681 -- Process is Successful. Now the PROCESS_FLAG can be set to 'Y'
682 p_process_status := 'Y';
683
684 << exit_from_procedure >>
685 p_codepath := jai_general_pkg.plot_codepath(40, p_codepath, null, 'END'); /* 33 */
686 return;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 p_process_status := 'E';
691 p_process_message := 'DELIVER_RTR_PKG.process_transaction:' || sqlerrm;
692 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
693 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 34 */
694 return;
695 END process_transaction;
696
697 /* ------------------------------------------------start of deliver_rtr_reco_nonexcise------------*/
698 PROCEDURE deliver_rtr_reco_nonexcise
699 (
700 p_transaction_id IN NUMBER,
701 p_transaction_date IN DATE,
702 p_organization_id IN NUMBER,
703 p_transaction_type IN VARCHAR2,
704 p_parent_transaction_type IN VARCHAR2,
705 p_receipt_num IN VARCHAR2,
706 p_shipment_line_id IN NUMBER,
707 p_currency_conversion_rate IN NUMBER,
708 p_apportion_factor IN NUMBER,
709 p_receiving_account_id IN NUMBER,
710 p_accounting_type IN VARCHAR2,
711 p_simulate IN VARCHAR2,
712 p_process_message OUT NOCOPY VARCHAR2,
713 p_process_status OUT NOCOPY VARCHAR2,
714 p_codepath IN OUT NOCOPY VARCHAR2
715 ) is
716
717 /*
718 Accounting Entries which happen in this scenario are
719 |------------------------------------------------------------------------------------------------
720 | Transaction | | | |
721 | Type | Amount | Credit | Debit |
722 | ==============|====================== |===========================|===========================|
723 | DELIVER | Total | Inv.Receiving | |
724 | DELIVER | Individual Tax Amt | | Individual Tax Accounts |
725 | RTR | Total | | Inv.Receiving |
726 | RTR | Individual Tax Amt | Individual Tax Accounts | |
727 | ============= | ======================|===========================|===========================|
728 ----------------------------------------------------------------------------------------------
729 */
730
731 /* Character Variable Declarations */
732 lv_reference_10_desc1 VARCHAR2(75);--File.Sql.35 Cbabu := 'India Local Receiving Entry for the Receipt Number ';
733 lv_reference_10_desc2 VARCHAR2(30);--File.Sql.35 Cbabu := ' For the Transaction Type ';
734 lv_reference_10_desc gl_interface.reference10%type;
735 lv_account_nature VARCHAR2(30);--File.Sql.35 Cbabu := 'Individual Tax';
736 lv_reference23 gl_interface.reference23%type;--File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise';
737 lv_source VARCHAR2(100);--File.Sql.35 Cbabu := 'Purchasing India';
738 lv_category VARCHAR2(100);--File.Sql.35 Cbabu := 'Receiving India';
739 lv_reference24 gl_interface.reference24%type;--File.Sql.35 Cbabu := 'rcv_transactions';
740 lv_reference25 gl_interface.reference25%type;--File.Sql.35 Cbabu := 'transaction_id';
741
742 ln_individual_tax_amount number;--File.Sql.35 Cbabu := 0;
743 ln_rec_account_tax_amount number;--File.Sql.35 Cbabu := 0;
744 ln_credit_amount number;
745 ln_debit_amount number;
746
747 BEGIN
748 --File.Sql.35 Cbabu
749 lv_account_nature := 'Individual Tax';
750 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';
751 lv_reference_10_desc2 := ' For the Transaction Type ';
752 lv_reference23 := 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise';
753 lv_source := 'Purchasing India';
754 lv_category := 'Receiving India';
755 lv_reference24 := 'rcv_transactions';
756 lv_reference25 := 'transaction_id';
757 ln_individual_tax_amount := 0;
758 ln_rec_account_tax_amount := 0;
759
760 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise', 'START'); /* 1 */
761
762 if p_transaction_type = 'CORRECT' then
763 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
764 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ||' of Type ' || p_parent_transaction_type;
765 else
766 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
767 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ;
768 end if;
769
770
771 For tax_rec IN
772 (
773 SELECT
774 sum(
775 rtl.tax_amount * (NVL(jtc.mod_cr_percentage, 0)/100)
776 * decode(nvl(rtl.currency, jai_rcv_trx_processing_pkg.gv_func_curr), jai_rcv_trx_processing_pkg.gv_func_curr, 1, p_currency_conversion_rate)
777 ) tax_amount,
778 jtc.tax_account_id
779 FROM JAI_RCV_LINE_TAXES rtl,
780 JAI_CMN_TAXES_ALL jtc
781 WHERE jtc.tax_id = rtl.tax_id
782 AND shipment_line_id = p_shipment_line_id
783 AND upper(rtl.tax_type) NOT IN ( 'EXCISE', 'ADDL. EXCISE',
784 'OTHER EXCISE', 'CVD','TDS', 'MODVAT RECOVERY',
785 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
786 jai_constants.tax_type_exc_edu_cess,
787 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
788 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
789 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
790 jai_constants.tax_type_boe_other1,
791 jai_constants.tax_type_boe_other2,
792 jai_constants.tax_type_boe_other3,
793 jai_constants.tax_type_boe_other4,
794 jai_constants.tax_type_boe_other5)
795 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
796
797 -- following condition added by Vijay Shankar for Bug#4068823. Service Tax Enhancement
798 -- this is added to Stop Recovery Service Tax Accounting, as this will be done during RECEIVE trx or
799 -- during Payables Invoice/Payment depending on transaction parameters
800 AND rtl.tax_type NOT IN (select attribute_code from JAI_RGM_REGISTRATIONS aa, JAI_RGM_DEFINITIONS bb
801 where aa.regime_id = bb.regime_id
802 /* vat_regime is included in the following clause by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
803 and bb.regime_code IN (jai_constants.service_regime, jai_constants.vat_regime)
804 and aa.registration_type = jai_constants.regn_type_tax_types )
805 AND NVL(rtl.modvat_flag, 'N') = 'Y'
806 GROUP BY jtc.tax_account_id
807 )
808 LOOP
809
810 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
811
812 if tax_rec.tax_account_id is null then
813 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
814 p_process_status := 'E';
815 p_process_message := 'The Tax Account is not found for this Tax : ';
816 goto exit_from_procedure;
817 end if;
818
819 ln_individual_tax_amount := NVL(tax_rec.tax_amount,0) * nvl(p_apportion_factor,0);
820 ln_rec_account_tax_amount := nvl(ln_rec_account_tax_amount,0) + nvl(ln_individual_tax_amount,0);
821
822 if ln_individual_tax_amount <> 0 then
823
824 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
825 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
826 p_transaction_type = 'DELIVER' then /* DELIVER scenario */
827
828 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
829 ln_credit_amount := NULL;
830 ln_debit_amount := ln_individual_tax_amount;
831
832 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
833 p_transaction_type = 'RETURN TO RECEIVING' then /* RTR scenario */
834
835 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
836 ln_credit_amount := ln_individual_tax_amount;
837 ln_debit_amount := NULL;
838
839 end if;
840
841 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
842 jai_rcv_accounting_pkg.process_transaction
843 (
844 p_transaction_id => p_transaction_id,
845 p_acct_type => p_accounting_type,
846 p_acct_nature => lv_account_nature,
847 p_source_name => lv_source,
848 p_category_name => lv_category,
849 p_code_combination_id => tax_rec.tax_account_id,
850 p_entered_dr => ln_debit_amount,
851 p_entered_cr => ln_credit_amount,
852 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
853 p_accounting_date => p_transaction_date,
854 p_reference_10 => lv_reference_10_desc, --Reference10
855 p_reference_23 => lv_reference23,
856 p_reference_24 => lv_reference24,
857 p_reference_25 => lv_reference25,
858 p_reference_26 => to_char(p_transaction_id),
859 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
860 p_simulate_flag => p_simulate,
861 p_codepath => p_codepath,
862 p_process_message => p_process_message,
863 p_process_status => p_process_status
864 );
865
866 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
867 if p_process_status IN ('E', 'X') then
868 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
869 goto exit_from_procedure;
870 end if;
871
872 end if; --end if for ln_individual_tax_amount <> 0
873 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
874 end loop; --End Loop for Tax Rec.
875
876 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
877 if ln_rec_account_tax_amount <> 0 then
878
879 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
880 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
881 p_transaction_type = 'DELIVER' then /* DELIVER scenario */
882
883 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
884 ln_credit_amount := ln_rec_account_tax_amount;
885 ln_debit_amount := NULL;
886
887 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
888 p_transaction_type = 'RETURN TO RECEIVING' then /* RTR scenario */
889
890 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
891 ln_credit_amount := NULL;
892 ln_debit_amount := ln_rec_account_tax_amount;
893
894 end if;
895
896
897 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
898 jai_rcv_accounting_pkg.process_transaction
899 (
900 p_transaction_id => p_transaction_id,
901 p_acct_type => p_accounting_type,
902 p_acct_nature => lv_account_nature,
903 p_source_name => lv_source,
904 p_category_name => lv_category,
905 p_code_combination_id => p_receiving_account_id,
906 p_entered_dr => ln_debit_amount,
907 p_entered_cr => ln_credit_amount,
908 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
909 p_accounting_date => p_transaction_date,
910 p_reference_10 => lv_reference_10_desc, --Reference10
911 p_reference_23 => lv_reference23,
912 p_reference_24 => lv_reference24,
913 p_reference_25 => lv_reference25,
914 p_reference_26 => to_char(p_transaction_id),
915 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
916 p_simulate_flag => p_simulate,
917 p_codepath => p_codepath,--lv_reference23, changed by zheng peng for bug 9457006
918 p_process_message => p_process_message,
919 p_process_status => p_process_status
920 );
921
922 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
923 if p_process_status in ('E', 'X') then
924 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
925 goto exit_from_procedure;
926 end if;
927 end if; --end if for ln_rec_account_tax_amount <> 0
928
929 << exit_from_procedure >>
930 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath, null, 'END'); /* 19 */
931 return;
932
933 EXCEPTION
934 WHEN OTHERS THEN
935 p_process_status := 'E';
936 p_process_message := 'DELIVER_RTR_PKG.deliver_rtr_reco_nonexcise:' || sqlerrm;
937 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
938 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 20 */
939 return;
940 END deliver_rtr_reco_nonexcise;
941
942 /*----------------------------------------------------------------------------------------*/
943 PROCEDURE get_tax_amount_breakup
944 (
945 p_shipment_line_id IN NUMBER,
946 p_transaction_id IN NUMBER,
947 p_curr_conv_rate IN NUMBER,
948 p_excise_amount OUT NOCOPY NUMBER,
949 p_non_modvat_amount OUT NOCOPY NUMBER,
950 p_other_modvat_amount OUT NOCOPY NUMBER,
951 p_process_message OUT NOCOPY VARCHAR2,
952 p_process_status OUT NOCOPY VARCHAR2,
953 p_codepath IN OUT NOCOPY VARCHAR2
954 ) IS
955
956 ln_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
957 ln_non_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
958 ln_other_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
959 ln_conv_factor NUMBER;
960 lv_tax_modvat_flag JAI_RCV_LINE_TAXES.modvat_flag%type;
961 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
962 ln_tax_amount NUMBER ; /* Added for bug #13494816 */
963
964 cursor c_ja_in_rcv_transactions(cp_transaction_id number) is
965 select item_trading_flag,organization_type,excise_in_trading,item_excisable
966 from JAI_RCV_TRANSACTIONS
967 where transaction_id = cp_transaction_id;
968
969 r_ja_in_rcv_transactions c_ja_in_rcv_transactions%rowtype;
970
971 BEGIN
972
973 --File.Sql.35 Cbabu
974 ln_modvat_amount := 0;
975 ln_non_modvat_amount := 0;
976 ln_other_modvat_amount := 0;
977 lv_debug := 'Y';
978
979 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.get_tax_amount_breakup' , 'START'); /* 1 */
980
981 OPEN c_ja_in_rcv_transactions(p_transaction_id);
982 FETCH c_ja_in_rcv_transactions into r_ja_in_rcv_transactions;
983 CLOSE c_ja_in_rcv_transactions;
984
985 FOR tax_rec IN
986 (
987 SELECT
988 rtl.tax_type,
989 nvl(rtl.tax_amount, 0) tax_amount,
990 nvl(rtl.modvat_flag, 'N') modvat_flag,
991 nvl(jtc.inclusive_tax_flag, 'N') inclusive_tax_flag, -- Added by Jia Li for India tax inclusive on 2007/11/28
992 nvl(rtl.currency, 'INR') currency,
993 nvl(jtc.mod_cr_percentage, 0) mod_cr_percentage,
994 nvl(jtc.rounding_factor, 0) rounding_factor --Added for bug #13494816
995 FROM
996 JAI_RCV_LINE_TAXES rtl,
997 JAI_CMN_TAXES_ALL jtc
998 WHERE
999 shipment_line_id = p_shipment_line_id
1000 AND jtc.tax_id = rtl.tax_id
1001
1002 )
1003 LOOP
1004
1005 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2*/
1006 if tax_rec.currency <> jai_rcv_trx_processing_pkg.gv_func_curr THEN
1007 ln_conv_factor := NVL(p_curr_conv_rate, 1);
1008 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3*/
1009 ELSE
1010 ln_conv_factor := 1;
1011 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4*/
1012 end if;
1013
1014 /*
1015 Comparison below is due to the case where Excise in RG23D ='Y', Tax_Rec.Modvat_Flag will be N
1016 as Item Modvat Flag in the setup is No.
1017 As a result of this, Excise should not be added to Item cost and hence deciding the Modvat
1018 Amount solely upon the Modvat Flag in JAI_RCV_LINE_TAXES is wrong.
1019 Hence, a variable ( lv_tax_modvat_flag) is first set based on the above permutations
1020 and then a decision of whether the Excise needs to be added or not is done based on this flag.
1021 */
1022
1023 if tax_rec.modvat_flag = 'Y'
1024 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1025 'TDS', 'MODVAT RECOVERY',
1026 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1027 jai_constants.tax_type_exc_edu_cess,
1028 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1029 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
1030 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
1031 jai_constants.tax_type_boe_other1,
1032 jai_constants.tax_type_boe_other2,
1033 jai_constants.tax_type_boe_other3,
1034 jai_constants.tax_type_boe_other4,
1035 jai_constants.tax_type_boe_other5)
1036 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
1037
1038 then
1039
1040 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1041 lv_tax_modvat_flag := 'Y';
1042
1043 elsif upper(tax_rec.modvat_flag) = 'Y'
1044 and tax_rec.tax_type NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1045 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1046 jai_constants.tax_type_exc_edu_cess,
1047 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1048 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
1049 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
1050 jai_constants.tax_type_boe_other1,
1051 jai_constants.tax_type_boe_other2,
1052 jai_constants.tax_type_boe_other3,
1053 jai_constants.tax_type_boe_other4,
1054 jai_constants.tax_type_boe_other5)
1055 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
1056 then
1057
1058 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1059 lv_tax_modvat_flag := 'Y';
1060
1061 elsif tax_rec.modvat_flag = 'N'
1062 and r_ja_in_rcv_transactions.item_trading_flag = 'Y' /* Excise IN RG23D scenario */
1063 and r_ja_in_rcv_transactions.excise_in_trading = 'Y'
1064 and r_ja_in_rcv_transactions.item_excisable = 'Y'
1065 and r_ja_in_rcv_transactions.organization_type = 'T'
1066 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1067 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1068 jai_constants.tax_type_exc_edu_cess,
1069 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1070 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
1071 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
1072 jai_constants.tax_type_boe_other1,
1073 jai_constants.tax_type_boe_other2,
1074 jai_constants.tax_type_boe_other3,
1075 jai_constants.tax_type_boe_other4,
1076 jai_constants.tax_type_boe_other5)
1077 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
1078 then
1079
1080 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1081 lv_tax_modvat_flag := 'Y';
1082
1083 else
1084 lv_tax_modvat_flag := 'N';
1085
1086 end if; --tax_rec.modvat_flag = 'Y'
1087
1088 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1089
1090 /* Added for bug #13494816 *//* Start*/
1091 ln_tax_amount := 0;
1092
1093 IF tax_rec.tax_type IN (jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other,jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess)
1094 THEN
1095 fnd_file.put_line( fnd_file.log, 'tax breakup tax_rec.tax_amount :'|| tax_rec.tax_amount||' tax_rec.rounding_factor:'||tax_rec.rounding_factor);
1096 ln_tax_amount := Round(tax_rec.tax_amount,Nvl(tax_rec.rounding_factor,0)); -- not to be rounded
1097 fnd_file.put_line( fnd_file.log, ' tax breakup ln_tax_amount :'|| ln_tax_amount);
1098 ELSE
1099 ln_tax_amount := tax_rec.tax_amount;
1100 END IF;
1101 /* Added for bug #13494816 *//* End*/
1102
1103 if upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
1104
1105 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1106
1107 if lv_tax_modvat_flag = 'Y'
1108 and upper(tax_rec.tax_type) IN ( 'EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1109 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1110 jai_constants.tax_type_exc_edu_cess,
1111 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1112 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
1113 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
1114 jai_constants.tax_type_boe_other1,
1115 jai_constants.tax_type_boe_other2,
1116 jai_constants.tax_type_boe_other3,
1117 jai_constants.tax_type_boe_other4,
1118 jai_constants.tax_type_boe_other5)
1119 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
1120
1121 then
1122
1123 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1124 ln_modvat_amount := ln_modvat_amount + ln_tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor; /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1125
1126 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
1127 -- TD15-Changed Standard and Average Costing
1128 -- recoverable tax is inclusive, its costing effect needs to be negated
1129 -- Modified by Jia Li for Bug#6877290
1130 ----------------------------------------------------------------------
1131 IF ( tax_rec.inclusive_tax_flag = 'Y' )
1132 THEN
1133 --ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1); --Commented for bug#9233826
1134 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * ( tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1); -- Added for bug#9233826 by JMEENA /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1135 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
1136 THEN
1137 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor;/* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1138 END IF; --tax_rec.inclusive_tax_flag = 'Y'
1139 -----------------------------------------------------------------------
1140 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1141
1142 elsif lv_tax_modvat_flag = 'Y'
1143 and upper(tax_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1144 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1145 jai_constants.tax_type_exc_edu_cess,
1146 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1147 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
1148 --Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
1149 jai_constants.tax_type_boe_other1,
1150 jai_constants.tax_type_boe_other2,
1151 jai_constants.tax_type_boe_other3,
1152 jai_constants.tax_type_boe_other4,
1153 jai_constants.tax_type_boe_other5)
1154 --Added by Wenqiong for the bug12645490 on 22/06/2011 End.
1155
1156 then
1157
1158 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1159 ln_other_modvat_amount := ln_other_modvat_amount + ln_tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor; /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1160
1161 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
1162 -- TD15-Changed Standard and Average Costing
1163 -- recoverable tax is inclusive, its costing effect needs to be negated
1164 -- Modified by Jia Li for Bug#6877290
1165 ----------------------------------------------------------------------
1166 IF ( tax_rec.inclusive_tax_flag = 'Y' )
1167 THEN
1168 -- ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1); --Commented for bug#9233826
1169 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1); -- Added for bug#9233826 by JMEENA /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1170 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
1171 THEN
1172 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor; /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1173 END IF; --tax_rec.inclusive_tax_flag = 'Y'
1174 ----------------------------------------------------------------------
1175 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1176
1177 ELSIF lv_tax_modvat_flag ='N' and upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
1178
1179 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1180
1181 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
1182 -- TD15-Changed Standard and Average Costing
1183 -- non-recoverable tax is inclusive, its costing should not be considered as it is already costed.
1184 -- Modified by Jia Li for Bug#6877290
1185 ----------------------------------------------------------------------
1186 IF ( tax_rec.inclusive_tax_flag = 'Y' )
1187 THEN
1188 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * ln_conv_factor * 0; /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1189 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
1190 THEN
1191 ln_non_modvat_amount := ln_non_modvat_amount + ln_tax_amount * ln_conv_factor; /* Replaced ln_tax_amount in place of tax_rec.tax_amount for bug #13494816 */
1192 END IF; --tax_rec.inclusive_tax_flag = 'Y'
1193 ----------------------------------------------------------------------
1194 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1195
1196 end if; /* tax_rec.modvat_flag*/
1197 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
1198
1199 end if; /*tax_rec.tax_type NOT IN ('TDS', 'Modvat Recovery')*/
1200 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1201
1202 END LOOP;
1203
1204 /* Resseting the Out variables */
1205 p_excise_amount := ln_modvat_amount;
1206 p_non_modvat_amount := ln_non_modvat_amount;
1207 p_other_modvat_amount := ln_other_modvat_amount;
1208
1209 << exit_from_procedure >>
1210 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath, null, 'END'); /* 15 */
1211 return;
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 p_process_status := 'E';
1216 p_process_message := 'DELIVER_RTR_PKG.get_tax_amount_breakup:' || sqlerrm;
1217 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1218 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 16 */
1219 return;
1220
1221 END get_tax_amount_breakup;
1222
1223 /* ------------------------------------------------start of get_tax_amount_breakup------------*/
1224 PROCEDURE opm_costing
1225 (
1226 p_transaction_id IN NUMBER,
1227 p_transaction_date IN DATE,
1228 p_organization_id IN NUMBER,
1229 p_costing_amount IN NUMBER,
1230 p_receiving_account_id IN NUMBER,
1231 p_rcv_unit_of_measure IN VARCHAR2, /*Indicates UOM of RECEIVE Line */
1232 p_rcv_source_unit_of_measure IN VARCHAR2, /*Indicates Source UOM of RECEIVE Line */
1233 p_rcv_quantity IN NUMBER, /*Indicates Quantity of RECEIVE Line */
1234 p_source_doc_quantity IN NUMBER, /*Indicates Source doc Quantity of RECEIVE Line */
1235 p_source_document_code IN VARCHAR2,
1236 p_po_distribution_id IN NUMBER,
1237 p_subinventory_code IN VARCHAR2,
1238 p_simulate IN VARCHAR2,
1239 p_process_message OUT NOCOPY VARCHAR2,
1240 p_process_status OUT NOCOPY VARCHAR2,
1241 p_codepath IN OUT NOCOPY VARCHAR2,
1242 p_process_special_source IN VARCHAR2,
1243 /* following parameter added by Vijay Shankar for Bug#4229164 */
1244 p_currency_conversion_rate IN NUMBER
1245 ) IS
1246
1247 ln_rcv_quantity rcv_transactions.quantity%TYPE;
1248 ln_material_account_id mtl_secondary_inventories.material_account%type;
1249 ln_costing_amount NUMBER;
1250
1251 lv_accounting_type varchar2(30); --File.Sql.35 Cbabu := 'REGULAR'; /*Hard coded as Localization does not do anything in case of a RTR Transaction */
1252 lv_source varchar2(30); --File.Sql.35 Cbabu := 'Inventory India';
1253 lv_category varchar2(30); --File.Sql.35 Cbabu := 'MTL';
1254 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'OPM Costing';
1255 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
1256
1257 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch */
1258 ln_opm_costing_amount NUMBER;
1259 ln_apportion_factor NUMBER;
1260 -- End Bug 7581494
1261
1262 /*
1263 Accounting Entries which happen in this scenario are
1264 |---------------|-----------------------|---------------------------|--------------------------|
1265 | Transaction | | | |
1266 | Type | Amount | Credit | Debit |
1267 | ==============|====================== |===========================|==========================|
1268 | DELIVER | Total | Inv.Receiving | |
1269 | DELIVER | Total | | Material Account |
1270 ----------------|-----------------------|---------------------------|--------------------------|
1271
1272 Only JAI_RCV_JOURNAL_ENTRIES is recorded with above entries But RCV_TRANSACTIONS will be updated only
1273 once.
1274 */
1275
1276 BEGIN
1277 lv_accounting_type := 'REGULAR';
1278 lv_source := 'Inventory India';
1279 lv_category := 'MTL';
1280 lv_account_nature := 'OPM Costing';
1281 lv_debug := 'Y';
1282
1283 /* This comparison is for Evaluating the Quantity */
1284 /* Meaning of this comparison
1285 if the Unit Of Measure is changed while RECEIVING then
1286 source doc quantity of RCV_TRANSACTIONS needs to be picked up
1287 otherwise
1288 the Quantity of RCV_TRANSACTIONS can be picked up.
1289 */
1290
1291 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.opm_costing', 'START'); /* 1 */
1292
1293 -- This procedure should not be used. So returning back
1294 -- GOTO exit_from_procedure;
1295
1296 if p_rcv_unit_of_measure <> p_rcv_source_unit_of_measure then
1297 ln_rcv_quantity := p_source_doc_quantity;
1298 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1299 ELSE
1300 ln_rcv_quantity := p_rcv_quantity;
1301 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1302 end if;
1303
1304 if nvl(ln_rcv_quantity,0) = 0 THEN
1305 p_process_status := 'E';
1306 p_process_message := 'The Quantity in RECEIVE line is Zero ';
1307 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1308 goto exit_from_procedure;
1309 end if;
1310
1311 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1312 ln_material_account_id := material_account
1313 (
1314 p_organization_id => p_organization_id,
1315 p_source_document_code => p_source_document_code,
1316 p_po_distribution_id => p_po_distribution_id,
1317 p_subinventory => p_subinventory_code,
1318 p_process_message => p_process_message,
1319 p_process_status => p_process_status,
1320 p_codepath => p_codepath
1321 );
1322
1323 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1324
1325 if p_process_status in ('E', 'X') then
1326 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1327 goto exit_from_procedure;
1328 end if;
1329
1330 -- ln_costing_amount := p_costing_amount / ln_rcv_quantity;
1331 /* above cost calculation is modified as belowe by Vijay Shankar for Bug#4229164 */
1332
1333 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch */
1334 ln_costing_amount := p_costing_amount / p_currency_conversion_rate;
1335 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
1336 ( p_transaction_id => p_transaction_id);
1337 ln_opm_costing_amount := p_costing_amount * ln_apportion_factor ;
1338
1339 /* End Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch.*/
1340 if lv_debug ='Y' then
1341 fnd_file.put_line(fnd_file.log, 'OPM Costing. ln_apportion_factor:'||ln_apportion_factor
1342 ||', ln_opm_costing_amount:'||ln_opm_costing_amount
1343 ||', pCostAmt:'||p_costing_amount
1344 ||', Qty:'||ln_rcv_quantity
1345 ||', FinalCostAmt:'||ln_costing_amount
1346 ||', OPMCostAmt:'||ln_opm_costing_amount
1347 );
1348 end if;
1349
1350 /* Destination in this case is O1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1351 and also rcv_transactions would be updated */
1352 /* Credit Inventory Receiving Account */
1353 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1354
1355
1356 /*
1357
1358 commented for bug 7681614 by vumaasha
1359
1360
1361 jai_rcv_accounting_pkg.process_transaction
1362 (
1363 p_transaction_id => p_transaction_id,
1364 p_acct_type => lv_accounting_type,
1365 p_acct_nature => lv_account_nature,
1366 p_source_name => lv_source,
1367 p_category_name => lv_category,
1368 p_code_combination_id => p_receiving_account_id,
1369 p_entered_dr => NULL,
1370 p_entered_cr => ln_opm_costing_amount,
1371 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1372 p_accounting_date => p_transaction_date,
1373 p_reference_10 => NULL,
1374 p_reference_23 => NULL,
1375 p_reference_24 => NULL,
1376 p_reference_25 => NULL,
1377 p_reference_26 => NULL,
1378 p_destination => 'O1',
1379 p_simulate_flag => p_simulate,
1380 p_codepath => p_codepath,
1381 p_process_message => p_process_message,
1382 p_process_status => p_process_status
1383 );
1384
1385 end of comment for bug 7681614 by vumaasha
1386 */
1387
1388 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1389
1390 if p_process_status in ('E', 'X') then
1391 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1392 goto exit_from_procedure;
1393 end if;
1394
1395 /* Debit Material Account
1396 Destination in this case is O1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1397 and also rcv_transactions would be updated */
1398
1399 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1400
1401 /*
1402
1403 commented for bug 7681614 by vumaasha
1404
1405
1406 jai_rcv_accounting_pkg.process_transaction
1407 (
1408 p_transaction_id => p_transaction_id,
1409 p_acct_type => lv_accounting_type,
1410 p_acct_nature => lv_account_nature,
1411 p_source_name => lv_source,
1412 p_category_name => lv_category,
1413 p_code_combination_id => ln_material_account_id,
1414 p_entered_dr => ln_opm_costing_amount,
1415 p_entered_cr => NULL,
1416 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1417 p_accounting_date => p_transaction_date,
1418 p_reference_10 => NULL,
1419 p_reference_23 => NULL,
1420 p_reference_24 => NULL,
1421 p_reference_25 => NULL,
1422 p_reference_26 => NULL,
1423 p_destination => 'O2',
1424 p_simulate_flag => p_simulate,
1425 p_codepath => p_codepath,
1426 p_process_message => p_process_message,
1427 p_process_status => p_process_status
1428 );
1429
1430 end of comment for bug 7681614 by vumaasha
1431
1432 */
1433
1434 if p_process_status in ('E', 'X') then
1435 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1436 goto exit_from_procedure;
1437 end if;
1438
1439 << exit_from_procedure >>
1440 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1441 return;
1442
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 p_process_status := 'E';
1446 p_process_message := 'DELIVER_RTR_PKG.opm_costing:' || sqlerrm;
1447 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1448 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 14 */
1449 return;
1450 END opm_costing;
1451
1452 /*---------------------------------------------------------------------------------------*/
1453 PROCEDURE expense_accounting
1454 (
1455 p_transaction_id IN NUMBER,
1456 p_transaction_date IN DATE,
1457 p_organization_id IN NUMBER,
1458 p_transaction_type IN VARCHAR2,
1459 p_parent_transaction_type IN VARCHAR2,
1460 p_receipt_num IN VARCHAR2,
1461 p_shipment_line_id IN NUMBER,
1462 p_subinventory_code IN VARCHAR2,
1463 p_accounted_amount IN NUMBER,
1464 p_receiving_account_id IN NUMBER,
1465 p_source_document_code IN VARCHAR2,
1466 p_po_distribution_id IN NUMBER,
1467 p_po_line_location_id IN NUMBER,
1468 p_inventory_item_id IN NUMBER,
1469 p_accounting_type IN VARCHAR2,
1470 p_simulate IN VARCHAR2,
1471 p_process_message OUT NOCOPY VARCHAR2,
1472 p_process_status OUT NOCOPY VARCHAR2,
1473 p_codepath IN OUT NOCOPY VARCHAR2,
1474 p_process_special_source IN VARCHAR2
1475 ) IS
1476
1477 /* This Procedure is meant for Expense Accounting Entries
1478 Accounting Entries in this context are:
1479
1480 |------------------------------------------------------------------------------------------------
1481 | Transaction | | | |
1482 | Type | Amount | Credit | Debit |
1483 | ==============|====================== |===========================|===========================|
1484 | DELIVER | Total | Inv.Receiving | |
1485 | DELIVER | Total | | Expense Account |
1486 -------------------------------------------------------------------------------------------------
1487 | RTR | Total | Expense Account | |
1488 | RTR | Total | | Inv.Receiving |
1489 -------------------------------------------------------------------------------------------------
1490
1491 */
1492
1493 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'Expense Accounting';
1494 lv_source VARCHAR2(100); --File.Sql.35 Cbabu := 'Purchasing India';
1495 lv_category VARCHAR2(100); --File.Sql.35 Cbabu := 'Receiving India';
1496 lv_reference23 gl_interface.reference23%type; --File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.expense_accounting';
1497 lv_reference24 gl_interface.reference24%type; --File.Sql.35 Cbabu := 'rcv_transactions';
1498 lv_reference25 gl_interface.reference25%type; --File.Sql.35 Cbabu := 'transaction_id';
1499 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
1500 lv_reference_10_desc1 VARCHAR2(75); --File.Sql.35 Cbabu := 'India Local Receiving Entry for the Receipt Number ';
1501 lv_reference_10_desc2 VARCHAR2(30); --File.Sql.35 Cbabu := ' For the Transaction Type ';
1502 lv_reference_10_desc gl_interface.reference10%type;
1503
1504 ln_credit_amount NUMBER;
1505 ln_debit_amount NUMBER;
1506 ln_expense_account mtl_secondary_inventories.expense_account%type;
1507
1508
1509 BEGIN
1510 --File.Sql.35 Cbabu
1511 lv_account_nature := 'Expense Accounting';
1512 lv_source := 'Purchasing India';
1513 lv_category := 'Receiving India';
1514 lv_reference23 := 'jai_rcv_deliver_rtr_pkg.expense_accounting';
1515 lv_reference24 := 'rcv_transactions';
1516 lv_reference25 := 'transaction_id';
1517 lv_debug := 'Y';
1518 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';
1519 lv_reference_10_desc2 := ' For the Transaction Type ';
1520
1521 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.expense_accounting', 'START'); /* 1 */
1522
1523 /* Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
1524 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1525 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1526 lv_account_nature := 'Cenvat Unclaim Expense';
1527 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1528 lv_account_nature := 'VAT Unclaim Expense';
1529 END IF;
1530
1531 if p_transaction_type='CORRECT' then
1532 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1533 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ||' of Type ' || p_parent_transaction_type;
1534 else
1535 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1536 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ;
1537 end if;
1538
1539
1540 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1541 ln_expense_account := expense_account
1542 (
1543 p_transaction_id => p_transaction_id,
1544 p_organization_id => p_organization_id,
1545 p_subinventory_code => p_subinventory_code,
1546 p_po_distribution_id => p_po_distribution_id,
1547 p_po_line_location_id => p_po_line_location_id,
1548 p_item_id => p_inventory_item_id,
1549 p_process_message => p_process_message,
1550 p_process_status => p_process_status,
1551 p_codepath => p_codepath
1552 );
1553 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1554
1555 if p_process_status IN ('E', 'X') THEN
1556 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, NULL, 'END'); /* 5 */
1557 goto exit_from_procedure;
1558 end if;
1559
1560 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1561 p_transaction_type = 'DELIVER' then
1562
1563 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1564 ln_debit_amount := NULL;
1565 ln_credit_amount := p_accounted_amount;
1566
1567 ELSIF (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1568 p_transaction_type = 'RETURN TO RECEIVING' then
1569
1570 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1571 ln_debit_amount := p_accounted_amount;
1572 ln_credit_amount := NULL;
1573
1574 end if;
1575
1576 /* Inventory Receiving Account */
1577 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1578 jai_rcv_accounting_pkg.process_transaction
1579 (
1580 p_transaction_id => p_transaction_id,
1581 p_acct_type => p_accounting_type,
1582 p_acct_nature => lv_account_nature,
1583 p_source_name => lv_source,
1584 p_category_name => lv_category,
1585 p_code_combination_id => p_receiving_account_id,
1586 p_entered_dr => ln_debit_amount,
1587 p_entered_cr => ln_credit_amount,
1588 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1589 p_accounting_date => p_transaction_date,
1590 p_reference_10 => lv_reference_10_desc || p_receipt_num ||lv_reference_10_desc1 ||p_transaction_type, --Reference10
1591 p_reference_23 => lv_reference23,
1592 p_reference_24 => lv_reference24,
1593 p_reference_25 => lv_reference25,
1594 p_reference_26 => to_char(p_transaction_id),
1595 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
1596 p_simulate_flag => p_simulate,
1597 p_codepath => p_codepath,
1598 p_process_message => p_process_message,
1599 p_process_status => p_process_status
1600 );
1601 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1602
1603 if p_process_status in ('E', 'X') then
1604 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1605 goto exit_from_procedure;
1606 end if;
1607
1608 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type ='DELIVER') or
1609 p_transaction_type ='DELIVER' then
1610
1611 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1612 ln_debit_amount := p_accounted_amount;
1613 ln_credit_amount := NULL;
1614
1615 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1616 p_transaction_type = 'RETURN TO RECEIVING' then
1617
1618 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1619 ln_debit_amount := NULL;
1620 ln_credit_amount := p_accounted_amount;
1621 end if;
1622
1623 /* Expense Account */
1624 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
1625 jai_rcv_accounting_pkg.process_transaction
1626 (
1627 p_transaction_id => p_transaction_id,
1628 p_acct_type => p_accounting_type,
1629 p_acct_nature => lv_account_nature,
1630 p_source_name => lv_source,
1631 p_category_name => lv_category,
1632 p_code_combination_id => ln_expense_account,
1633 p_entered_dr => ln_debit_amount,
1634 p_entered_cr => ln_credit_amount,
1635 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1636 p_accounting_date => p_transaction_date,
1637 p_reference_10 => lv_reference_10_desc || p_receipt_num ||lv_reference_10_desc1 ||p_transaction_type, --Reference10
1638 p_reference_23 => lv_reference23,
1639 p_reference_24 => lv_reference24,
1640 p_reference_25 => lv_reference25,
1641 p_reference_26 => to_char(p_transaction_id),
1642 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
1643 p_simulate_flag => p_simulate,
1644 p_codepath => p_codepath,
1645 p_process_message => p_process_message,
1646 p_process_status => p_process_status
1647 );
1648 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1649
1650 if p_process_status in ('E', 'X') then
1651 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
1652 goto exit_from_procedure;
1653 end if;
1654
1655 << exit_from_procedure >>
1656 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath, null, 'END'); /* 16 */
1657 return;
1658
1659 EXCEPTION
1660 WHEN OTHERS THEN
1661 p_process_status := 'E';
1662 p_process_message := 'DELIVER_RTR_PKG.expense_accounting:' || sqlerrm ;
1663 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1664 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 17 */
1665
1666 END expense_accounting;
1667
1668 /* ---------------------------------------------start of average costing procedure --------------*/
1669 PROCEDURE average_costing
1670 (
1671 p_transaction_id IN NUMBER,
1672 p_transaction_date IN DATE,
1673 p_organization_id IN NUMBER,
1674 p_parent_transaction_type IN VARCHAR2,
1675 p_transaction_type IN VARCHAR2,
1676 p_subinventory_code IN VARCHAR2,
1677 p_costing_amount IN NUMBER,
1678 p_receiving_account_id IN NUMBER,
1679 p_source_document_code IN VARCHAR2,
1680 p_po_distribution_id IN NUMBER,
1681 p_unit_of_measure IN VARCHAR2,
1682 p_inventory_item_id IN NUMBER,
1683 p_accounting_type IN VARCHAR2,
1684 p_simulate IN VARCHAR2,
1685 p_process_message OUT NOCOPY VARCHAR2,
1686 p_process_status OUT NOCOPY VARCHAR2,
1687 p_codepath IN OUT NOCOPY VARCHAR2,
1688 p_process_special_source IN VARCHAR2
1689 ) is
1690
1691 ln_material_account_id mtl_secondary_inventories.material_account%type;
1692 ln_costing_amount number;
1693
1694 lv_source varchar2(30); --File.Sql.35 Cbabu := 'Inventory India';
1695 lv_category varchar2(30); --File.Sql.35 Cbabu := 'MTL';
1696 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
1697 lv_uom_code mtl_units_of_measure.unit_of_measure % TYPE;
1698 lv_account_nature varchar2(30); --File.Sql.35 Cbabu := 'Average Costing';
1699
1700 /*
1701 This Procedure is meant for Costing Entries in case of a Average costing Organization
1702 with the destination type being Inventory.
1703
1704 The Value change part is sent to MMTT and hence only 1 row is populated into MMTT
1705 with the Account being Inv. Receiving Always.
1706
1707 Transaction Type | Amount | Account
1708 =====================|===================|==========================================
1709 DELIVER | Costing | Inv.Receiving
1710 RETURN TO RECEIVING | -Costing | Inv.Receiving
1711 =====================|===================|==========================================
1712
1713 The Entry recorded in JAI_RCV_JOURNAL_ENTRIES is :
1714
1715 Transaction Type Amount Credit Debit
1716 |===================|=================|====================|=======================|
1717 |DELIVER | Costing | Inv. Receiving | |
1718 |DELIVER | Costing | | Material Account |
1719 ------------------------------------------------------------------------------------
1720 |RTR | -Costing | Inv. Receiving | |
1721 |RTR | -Costing | | Material Account |
1722 |===================|=================|====================|=======================|
1723 */
1724
1725 BEGIN
1726 --File.Sql.35 Cbabu
1727 lv_source := 'Inventory India';
1728 lv_category := 'MTL';
1729 lv_debug := 'Y';
1730 lv_account_nature := 'Average Costing';
1731
1732 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.average_costing', 'START'); /* 1 */
1733
1734 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1735 lv_account_nature := 'Unclaim Average Costing';
1736
1737 /* elsif added for vat_noclaim by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1738 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1739 lv_account_nature := 'VAT Unclaim Average Costing';
1740 END IF;
1741
1742 /* Fetch the Material Account Id */
1743 ln_material_account_id := material_account
1744 (
1745 p_organization_id => p_organization_id,
1746 p_source_document_code => p_source_document_code,
1747 p_po_distribution_id => p_po_distribution_id,
1748 p_subinventory => p_subinventory_code,
1749 p_process_message => p_process_message,
1750 p_process_status => p_process_status,
1751 p_codepath => p_codepath
1752 );
1753 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1754
1755 if p_process_status in ('E', 'X') then
1756 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1757 goto exit_from_procedure;
1758 end if;
1759
1760 lv_uom_code := jai_general_pkg.get_uom_code(p_uom => p_unit_of_measure);
1761
1762
1763 if lv_uom_code IS NULL THEN
1764
1765 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1766 lv_uom_code := jai_general_pkg.get_primary_uom_code
1767 (
1768 p_organization_id => p_organization_id,
1769 p_inventory_item_id => p_inventory_item_id
1770 );
1771 end if;
1772
1773 if lv_debug='Y' THEN
1774 fnd_file.put_line( fnd_file.log, ' 3.3 '|| ' p_unit_of_measure -> ' || p_unit_of_measure || ' lv_uom_code -> ' || lv_uom_code);
1775 end if;
1776
1777 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1778 p_transaction_type = 'DELIVER' then
1779
1780 ln_costing_amount := p_costing_amount;
1781 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1782
1783 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1784 p_transaction_type = 'RETURN TO RECEIVING' then
1785
1786 ln_costing_amount := -p_costing_amount;
1787 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1788
1789 end if;
1790
1791 /* Destination in this case is A1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1792 and also MMTT would be updated */
1793
1794 /* Inventory Receiving Account */
1795 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1796 jai_rcv_accounting_pkg.process_transaction
1797 (
1798 p_transaction_id => p_transaction_id,
1799 p_acct_type => p_accounting_type,
1800 p_acct_nature => lv_account_nature,
1801 p_source_name => lv_source,
1802 p_category_name => lv_category,
1803 p_code_combination_id => p_receiving_account_id,
1804 p_entered_dr => NULL, /* This should never be changed to Zero */
1805 p_entered_cr => ln_costing_amount,
1806 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1807 p_accounting_date => p_transaction_date,
1808 p_reference_10 => NULL,
1809 p_reference_23 => NULL,
1810 p_reference_24 => NULL,
1811 p_reference_25 => NULL,
1812 p_reference_26 => NULL,
1813 p_destination => 'A1', /*Indicates Average Costing Entry */
1814 p_simulate_flag => p_simulate,
1815 p_codepath => p_codepath,
1816 p_process_message => p_process_message,
1817 p_process_status => p_process_status
1818 );
1819 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1820
1821 if p_process_status in ('E', 'X') then
1822 goto exit_from_procedure;
1823 end if;
1824
1825 /* Debit Material Account
1826 /* Destination in this case is A2, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1827 and also MMTT would be updated */
1828
1829 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1830 jai_rcv_accounting_pkg.process_transaction
1831 (
1832 p_transaction_id => p_transaction_id,
1833 p_acct_type => p_accounting_type,
1834 p_acct_nature => lv_account_nature,
1835 p_source_name => lv_source,
1836 p_category_name => lv_category,
1837 p_code_combination_id => ln_material_account_id,
1838 p_entered_dr => ln_costing_amount,
1839 p_entered_cr => NULL, /* This should never be changed to Zero */
1840 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1841 p_accounting_date => p_transaction_date,
1842 p_reference_10 => NULL,
1843 p_reference_23 => NULL,
1844 p_reference_24 => NULL,
1845 p_reference_25 => NULL,
1846 p_reference_26 => NULL,
1847 p_destination => 'A2', /*Indicates Average Costing Entry */
1848 p_simulate_flag => p_simulate,
1849 p_codepath => p_codepath,
1850 p_process_message => p_process_message,
1851 p_process_status => p_process_status
1852 );
1853
1854 if p_process_status in ('E', 'X') then
1855 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1856 goto exit_from_procedure;
1857 end if;
1858
1859 << exit_from_procedure >>
1860 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath, null, 'END'); /* 11 */
1861 return;
1862
1863 exception
1864 WHEN OTHERS THEN
1865 p_process_status := 'E';
1866 p_process_message := 'DELIVER_RTR_PKG.average_costing:' || sqlerrm ;
1867 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1868 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 12 */
1869
1870 END average_costing;
1871
1872 /*----------------------------start of standard costing-------------------------------------------------------*/
1873
1874 PROCEDURE standard_costing
1875 (
1876 p_transaction_id IN NUMBER,
1877 p_transaction_date IN DATE,
1878 p_organization_id IN NUMBER,
1879 p_parent_transaction_type IN VARCHAR2,
1880 p_transaction_type IN VARCHAR2,
1881 p_costing_amount IN NUMBER,
1882 p_receiving_account_id IN NUMBER,
1883 p_accounting_type IN VARCHAR2,
1884 p_simulate IN VARCHAR2,
1885 p_process_message OUT NOCOPY VARCHAR2,
1886 p_process_status OUT NOCOPY VARCHAR2,
1887 p_codepath IN OUT NOCOPY VARCHAR2,
1888 p_process_special_source IN VARCHAR2
1889 ) is
1890
1891 /*
1892 This Procedure is meant for Costing Entries in case of a Standard costing Organization
1893
1894
1895 The Costing Amount is populated into MTA.
1896
1897 Transaction Type | Amount | Account
1898 =====================|===================|==========================================
1899 DELIVER | Negative Amount | Inv.Receiving
1900 DELIVER | Positive Amount | PPV Account
1901 -----------------------------------------------------------------------------------
1902 RETURN TO RECEIVING | Positive Amount | Inv.Receiving
1903 RETURN TO RECEIVING | Negative Amount | PPV Account
1904 =====================|===================|==========================================
1905
1906 The Entry recorded in JAI_RCV_JOURNAL_ENTRIES is :
1907
1908 Transaction Type Amount Credit Debit
1909 ===================|=================|====================|=======================|
1910 DELIVER | Costing Amount | Inv.Receiving | |
1911 DELIVER | Costing Amount | | PPV Account |
1912 -------------------------------------|--------------------|-----------------------|
1913 RTR | Costing Amount | | Inv.Receiving |
1914 RTR | Costing Amount | PPV Account | |
1915 ===================|=================|====================|=======================|
1916
1917 */
1918
1919 ln_ppv_account_id mtl_parameters.purchase_price_var_account%type;
1920 ln_credit_amount NUMBER;
1921 ln_debit_amount NUMBER;
1922
1923 lv_source VARCHAR2(30); --File.Sql.35 Cbabu := 'Inventory India';
1924 lv_category VARCHAR2(30); --File.Sql.35 Cbabu := 'MTL';
1925 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'Standard Costing';
1926 --lv_debug varchar2(1) := 'Y';
1927
1928 --lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
1929 lv_reference_10_desc1 VARCHAR2(75);--rchandan for bug#4473022
1930 lv_reference_10_desc2 VARCHAR2(30); --rchandan for bug#4473022
1931 lv_reference_10_desc gl_interface.reference10%type;--rchandan for bug#4473022
1932
1933 BEGIN
1934
1935 lv_source := 'Inventory India';
1936 lv_category := 'MTL';
1937 lv_account_nature := 'Standard Costing';
1938 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';--rchandan for bug#4473022
1939 lv_reference_10_desc2 := ' For the Transaction Type ';--rchandan for bug#4473022
1940
1941 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.standard_costing', 'START'); /* 1 */
1942
1943 -- Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
1944 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1945 lv_account_nature := 'Unclaim Standard Costing';
1946
1947 /* elsif added for vat_noclaim by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1948 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1949 lv_account_nature := 'VAT Unclaim Standard Costing';
1950 END IF;
1951
1952 ln_ppv_account_id := ppv_account
1953 (
1954 p_organization_id => p_organization_id,
1955 p_process_message => p_process_message,
1956 p_process_status => p_process_status,
1957 p_codepath => p_codepath
1958 );
1959 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1960
1961 if p_process_status in ('E', 'X') then
1962 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1963 goto exit_from_procedure;
1964 end if;
1965
1966 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1967 p_transaction_type = 'DELIVER' then
1968
1969 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1970 ln_credit_amount := p_costing_amount;
1971 ln_debit_amount := NULL;
1972
1973 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1974 p_transaction_type = 'RETURN TO RECEIVING' then
1975
1976 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1977 ln_credit_amount := NULL;
1978 ln_debit_amount := p_costing_amount;
1979
1980 end if;
1981
1982 /* Receiving Inspection Account */
1983 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1984 jai_rcv_accounting_pkg.process_transaction
1985 (
1986 p_transaction_id => p_transaction_id,
1987 p_acct_type => p_accounting_type,
1988 p_acct_nature => lv_account_nature,
1989 p_source_name => lv_source,
1990 p_category_name => lv_category,
1991 p_code_combination_id => p_receiving_account_id,
1992 p_entered_dr => ln_debit_amount,
1993 p_entered_cr => ln_credit_amount,
1994 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1995 p_accounting_date => p_transaction_date,
1996 p_reference_10 => NULL,
1997 p_reference_23 => 'jai_rcv_deliver_rtr_pkg.standard_costing', --rchandan for bug#4473022
1998 p_reference_24 => 'rcv_transactions', --rchandan for bug#4473022
1999 p_reference_25 => NULL,
2000 p_reference_26 => to_char(p_transaction_id),
2001 p_destination => 'S', /*Indicates Standard Costing. */
2002 p_simulate_flag => p_simulate,
2003 p_codepath => p_codepath,
2004 p_process_message => p_process_message,
2005 p_process_status => p_process_status
2006 );
2007
2008 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
2009 if p_process_status in ('E', 'X') then
2010 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
2011 goto exit_from_procedure;
2012 end if;
2013
2014 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
2015 p_transaction_type = 'DELIVER' then
2016
2017 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
2018 ln_credit_amount := NULL;
2019 ln_debit_amount := p_costing_amount;
2020
2021 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
2022 p_transaction_type = 'RETURN TO RECEIVING' then
2023
2024 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
2025 ln_credit_amount := p_costing_amount;
2026 ln_debit_amount := NULL;
2027
2028 end if;
2029
2030 /* PPV Account */
2031 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
2032 jai_rcv_accounting_pkg.process_transaction
2033 (
2034 p_transaction_id => p_transaction_id,
2035 p_acct_type => p_accounting_type,
2036 p_acct_nature => lv_account_nature,
2037 p_source_name => lv_source,
2038 p_category_name => lv_category,
2039 p_code_combination_id => ln_ppv_account_id,
2040 p_entered_dr => ln_debit_amount,
2041 p_entered_cr => ln_credit_amount,
2042 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
2043 p_accounting_date => p_transaction_date,
2044 p_reference_10 => NULL,
2045 p_reference_23 => 'jai_rcv_deliver_rtr_pkg.standard_costing', --rchandan for bug#4473022
2046 p_reference_24 => 'rcv_transactions', --rchandan for bug#4473022
2047 p_reference_25 => NULL,
2048 p_reference_26 => to_char(p_transaction_id),
2049 p_destination => 'S', /*Indicates Standard Costing. */
2050 p_simulate_flag => p_simulate,
2051 p_codepath => p_codepath,
2052 p_process_message => p_process_message,
2053 p_process_status => p_process_status
2054 );
2055
2056 if p_process_status in ('E', 'X') then
2057 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
2058 goto exit_from_procedure;
2059 end if;
2060
2061 << exit_from_procedure >>
2062 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
2063 return;
2064
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 p_process_status := 'E';
2068 p_process_message := 'DELIVER_RTR_PKG.standard_costing:' || sqlerrm ;
2069 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
2070 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 14 */
2071
2072 END standard_costing;
2073
2074
2075 /* ------------------------------------start of receiving_account-----------------------*/
2076
2077 FUNCTION receiving_account
2078 (
2079 p_organization_id IN NUMBER,
2080 p_process_message OUT NOCOPY VARCHAR2,
2081 p_process_status OUT NOCOPY VARCHAR2,
2082 p_codepath IN OUT NOCOPY VARCHAR2
2083 )
2084 return number is
2085
2086 CURSOR c_receiving_account(cp_organization_id number) IS
2087 SELECT receiving_account_id
2088 FROM rcv_parameters
2089 WHERE organization_id = cp_organization_id;
2090
2091 ln_receiving_account_id rcv_parameters.receiving_account_id%type;
2092 --lv_debug varchar2(1) := 'Y';
2093
2094 BEGIN
2095
2096 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.receiving_account', 'START'); /* 1 */
2097 open c_receiving_account(p_organization_id);
2098 fetch c_receiving_account into ln_receiving_account_id;
2099 close c_receiving_account;
2100
2101 if ln_receiving_account_id is null then
2102 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath, null, 'END'); /* 2 */
2103 p_process_status :='E';
2104 p_process_message :='Receiving Account Not Defined';
2105 RETURN null;
2106 -- raise no_receiving_account;
2107 else
2108 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath, null, 'END'); /* 3 */
2109 return(ln_receiving_account_id);
2110 end if;
2111
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 p_process_status := 'E';
2115 p_process_message := 'DELIVER_RTR_PKG.receiving_account:' || SQLERRM;
2116 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
2117 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 5 */
2118 return null;
2119
2120 END receiving_account;
2121
2122 /*----------------------------------------------------------------------*/
2123 FUNCTION material_account
2124 (
2125 p_organization_id IN NUMBER,
2126 p_source_document_code IN VARCHAR2,
2127 p_po_distribution_id IN NUMBER,
2128 p_subinventory IN VARCHAR2,
2129 p_process_message OUT NOCOPY VARCHAR2,
2130 p_process_status OUT NOCOPY VARCHAR2,
2131 p_codepath IN OUT NOCOPY VARCHAR2
2132 )
2133 RETURN NUMBER IS
2134
2135 CURSOR c_costing_group_id(cp_po_distribution_id number) is
2136 SELECT costing_group_id
2137 FROM pjm_project_parameters
2138 WHERE project_id in
2139 (select project_id
2140 from po_distributions_all
2141 where po_distribution_id =cp_po_distribution_id
2142 );
2143
2144 /* cursor to get the material account pertaining to the cost group passed */
2145 CURSOR c_material_account_cg(cp_cost_group_id number) is
2146 SELECT material_account
2147 FROM cst_cost_group_accounts
2148 WHERE cost_group_id = cp_cost_group_id;
2149
2150 /* cursor to get the material account */
2151 CURSOR c_material_account(cp_organization_id number , cp_subinventory varchar2) is
2152 SELECT material_account
2153 FROM mtl_secondary_inventories
2154 WHERE organization_id = cp_organization_id
2155 AND secondary_inventory_name = cp_subinventory;
2156
2157
2158 ln_material_account_id mtl_secondary_inventories.material_account%type;
2159 ln_costing_group_id pjm_project_parameters.costing_group_id%type;
2160
2161 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
2162
2163 BEGIN
2164
2165 lv_debug := 'Y';
2166 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.material_account', 'START'); /* 1 */
2167
2168 if p_source_document_code = 'PO' then
2169
2170 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2171 open c_costing_group_id(p_po_distribution_id);
2172 fetch c_costing_group_id into ln_costing_group_id;
2173 close c_costing_group_id;
2174
2175
2176 open c_material_account_cg(ln_costing_group_id);
2177 fetch c_material_account_cg into ln_material_account_id;
2178 close c_material_account_cg;
2179
2180 if lv_debug='Y' THEN
2181 fnd_file.put_line( fnd_file.log, '4_2.1 costing group' || ln_costing_group_id);
2182 fnd_file.put_line( fnd_file.log, '4_2.2 material acct of costing group' || ln_material_account_id);
2183 end if;
2184
2185 end if;
2186
2187 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2188 if ln_material_account_id is null then
2189
2190 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2191 open c_material_account(p_organization_id,p_subinventory);
2192 fetch c_material_account into ln_material_account_id;
2193 close c_material_account;
2194
2195 end if;
2196
2197 if ln_material_account_id is null then
2198 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, null, 'END'); /* 5 */
2199 p_process_status :='E';
2200 p_process_message :='Material Account Not Defined';
2201 return null;
2202 --raise no_material_account;
2203 else
2204 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath, null, 'END'); /* 6 */
2205 return(ln_material_account_id);
2206 end if;
2207
2208
2209 EXCEPTION
2210
2211 WHEN OTHERS THEN
2212 p_process_status :='E';
2213 p_process_message :='DELIVER_RTR_PKG.material_account:' || SQLERRM;
2214 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
2215 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 8 */
2216 return null;
2217
2218 END material_account ;
2219
2220 /*---------------------------------Start of Expense_Account----------------------------*/
2221 FUNCTION expense_account
2222 (
2223 p_transaction_id IN NUMBER,
2224 p_organization_id IN NUMBER,
2225 p_subinventory_code IN VARCHAR2,
2226 p_po_distribution_id IN NUMBER,
2227 p_po_line_location_id IN NUMBER,
2228 p_item_id IN NUMBER,
2229 p_process_message OUT NOCOPY VARCHAR2,
2230 p_process_status OUT NOCOPY VARCHAR2,
2231 p_codepath IN OUT NOCOPY VARCHAR2
2232 )
2233 RETURN NUMBER IS
2234
2235 ln_expense_account mtl_secondary_inventories.expense_account%type;
2236
2237 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
2238
2239 cursor c_fetch_expense_acct(cp_organization_id in number, cp_subinventory_code in varchar2) IS
2240 SELECT expense_account
2241 FROM mtl_secondary_inventories
2242 WHERE organization_id = cp_organization_id
2243 AND secondary_inventory_name = cp_subinventory_code;
2244
2245 cursor c_fetch_expense_acct1(cp_po_distribution_id in number) IS
2246 SELECT code_combination_id
2247 FROM po_distributions_all
2248 WHERE po_distribution_id = cp_po_distribution_id;
2249
2250 CURSOR c_fetch_expense_acct2(cp_po_line_location_id in number) IS
2251 SELECT code_combination_id
2252 FROM po_distributions_all
2253 WHERE line_location_id = cp_po_line_location_id
2254 AND creation_date IN
2255 (SELECT max(creation_date)
2256 FROM po_distributions_all
2257 WHERE line_location_id = cp_po_line_location_id
2258 );
2259
2260 CURSOR c_fetch_expense_acct3(cp_organization_id in number, cp_item_id in number) IS
2261 SELECT expense_account
2262 FROM mtl_system_items
2263 WHERE organization_id = cp_organization_id
2264 AND inventory_item_id = cp_item_id;
2265
2266 BEGIN
2267
2268 lv_debug := 'Y';
2269
2270 /* In case of a Expense Route Data is populated into rcv_receiving_sub_ledger
2271 and hence CCID can be picked up from here instead of looking into various
2272 other possibilities
2273 */
2274
2275 /* To be checked whether this can be done in a different way */
2276 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.expense_account', 'START'); /* 1 */
2277
2278 open c_fetch_expense_acct(p_organization_id, p_subinventory_code);
2279 fetch c_fetch_expense_acct into ln_expense_account;
2280 close c_fetch_expense_acct;
2281
2282 if ln_expense_account is null then
2283
2284 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2285 if p_po_distribution_id is not null then
2286
2287 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2288 open c_fetch_expense_acct1(p_po_distribution_id);
2289 fetch c_fetch_expense_acct1 into ln_expense_account;
2290 close c_fetch_expense_acct1;
2291
2292 elsif p_po_line_location_id is not null then
2293
2294 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2295 open c_fetch_expense_acct2(p_po_line_location_id);
2296 fetch c_fetch_expense_acct2 into ln_expense_account;
2297 close c_fetch_expense_acct2;
2298
2299 end if;
2300 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
2301
2302 end if; --end if for ln_expense_account.
2303
2304 if ln_expense_account is null then
2305
2306 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
2307 open c_fetch_expense_acct3(p_organization_id, p_item_id);
2308 fetch c_fetch_expense_acct3 into ln_expense_account;
2309 close c_fetch_expense_acct3;
2310
2311 end if;
2312
2313
2314 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
2315 if ln_expense_account is null then
2316 p_process_status := 'E';
2317 p_process_message := 'Expense Account is Not Found ';
2318 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath, null, 'END'); /* 8 */
2319 return null;
2320 end if;
2321
2322 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, null, 'END'); /* 9 */
2323
2324 return(ln_expense_account);
2325
2326
2327 EXCEPTION
2328 WHEN OTHERS THEN
2329 p_process_status := 'E';
2330 p_process_message := 'DELIVER_RTR_PKG.expense_account:' || SQLERRM;
2331 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
2332 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 10 */
2333 return null;
2334 END expense_account;
2335
2336 /*----------------------------------------------------------------------------------------*/
2337
2338 FUNCTION ppv_account
2339 (
2340 p_organization_id IN NUMBER,
2341 p_process_message OUT NOCOPY VARCHAR2,
2342 p_process_status OUT NOCOPY VARCHAR2,
2343 p_codepath IN OUT NOCOPY VARCHAR2
2344 )
2345 return number is
2346
2347 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
2348 cursor c_ppv_account(cp_organization_id IN NUMBER) is
2349 SELECT purchase_price_var_account
2350 FROM mtl_parameters
2351 WHERE organization_id = cp_organization_id;
2352
2353 ln_ppv_account_id NUMBER;
2354
2355
2356 BEGIN
2357
2358 lv_debug := 'Y';
2359
2360 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.ppv_account', 'START'); /* 1 */
2361 open c_ppv_account(p_organization_id);
2362 fetch c_ppv_account into ln_ppv_account_id;
2363 close c_ppv_account;
2364
2365 if ln_ppv_account_id is null then
2366 p_process_status := 'E';
2367 p_process_message := 'The Purchase Price Variance Account is not found ' ;
2368 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath, null, 'END'); /* 2 */
2369 return null;
2370 ELSE
2371 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2372 return(ln_ppv_account_id);
2373 end if;
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 p_process_status := 'E';
2378 p_process_message := 'DELIVER_RTR_PKG.ppv_account:' || SQLERRM ;
2379 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
2380 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 4 */
2381 return null;
2382 END ppv_account;
2383
2384 /*----------------------------------------------------------------------------------------*/
2385 function include_cenvat_in_costing
2386 (
2387 p_transaction_id IN NUMBER,
2388 p_process_message OUT NOCOPY VARCHAR2,
2389 p_process_status OUT NOCOPY VARCHAR2,
2390 p_codepath IN OUT NOCOPY VARCHAR2
2391 )
2392 return varchar2 is
2393
2394 lv_ret_value VARCHAR2(30);
2395
2396 lv_destination_type varchar2(30);
2397 lv_transaction_type varchar2(30);
2398 lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%type;
2399 lv_debug varchar2(1);--File.Sql.35 Cbabu := 'Y';
2400 lv_include_cenvat_in_costing varchar2(1);
2401
2402 ln_dlry_trx_id NUMBER;
2403
2404 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER) IS
2405 SELECT nvl(unclaim_cenvat_flag, jai_constants.no) unclaim_cenvat_flag,
2406 nvl(non_bonded_delivery_flag, jai_constants.no) non_bonded_delivery_flag,
2407 nvl(cenvat_claimed_amt, 0) cenvat_claimed_amt
2408 FROM JAI_RCV_CENVAT_CLAIMS
2409 WHERE transaction_id = cp_transaction_id;
2410
2411 CURSOR c_trx(cp_transaction_id number) is
2412 SELECT *
2413 FROM JAI_RCV_TRANSACTIONS
2414 WHERE transaction_id = cp_transaction_id;
2415
2416 r_trx c_trx%ROWTYPE;
2417 r_dlry_trx c_trx%ROWTYPE;
2418 r_base_trx c_base_trx%ROWTYPE;
2419
2420 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
2421
2422 begin
2423
2424 lv_debug := 'Y';
2425 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing', 'START'); /* 1 */
2426
2427 OPEN c_trx(p_transaction_id);
2428 FETCH c_trx INTO r_trx;
2429 CLOSE c_trx;
2430
2431 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id);
2432 FETCH c_receipt_cenvat_dtl INTO r_receipt_cenvat_dtl;
2433 CLOSE c_receipt_cenvat_dtl;
2434
2435 IF r_receipt_cenvat_dtl.unclaim_cenvat_flag = 'Y' THEN
2436 p_codepath := jai_general_pkg.plot_codepath(1.1, p_codepath); /* 12 */
2437 lv_include_cenvat_in_costing :='Y';
2438 GOTO end_of_procedure;
2439
2440 /* following is incorporated as part of non bonded delivery functionaliy
2441 if the condition is satisfied, then it means receipt line is not claimed and a non bonded delivery is done
2442 In this case Cenvat has to be costed.
2443 If non bonded flag is set after Claim Cenvat is done, then we need to pass/reverse the rg entries that are passed during
2444 RECEIVE for whatever applicable transactions
2445 */
2446 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_amt = 0
2447 AND r_receipt_cenvat_dtl.non_bonded_delivery_flag = 'Y'
2448 THEN
2449 p_codepath := jai_general_pkg.plot_codepath(1.2, p_codepath); /* 12 */
2450 lv_include_cenvat_in_costing :='Y';
2451 GOTO end_of_procedure;
2452
2453 END IF;
2454
2455 IF r_trx.transaction_type = 'CORRECT' THEN
2456 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2457 lv_transaction_type := r_trx.parent_transaction_type;
2458 ELSE
2459 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2460 lv_transaction_type := r_trx.transaction_type;
2461 END IF;
2462
2463 IF lv_transaction_type NOT IN ( 'DELIVER', 'RETURN TO RECEIVING') THEN
2464 /* this procedure is not valid for the transaction being processed */
2465 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2466 RETURN 'X';
2467 END IF;
2468
2469 IF lv_transaction_type = 'RETURN TO RECEIVING' THEN
2470
2471 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2472 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
2473 ( r_trx.transaction_id, r_trx.shipment_line_id, 'DELIVER');
2474
2475 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
2476 OPEN c_trx(ln_dlry_trx_id);
2477 FETCH c_trx INTO r_dlry_trx;
2478 CLOSE c_trx;
2479
2480 lv_destination_type := r_dlry_trx.destination_type_code;
2481 lv_loc_subinv_type := nvl(r_dlry_trx.loc_subinv_type, 'X');
2482
2483
2484 ELSE --DELIVER scenario.
2485
2486 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
2487 lv_destination_type := r_trx.destination_type_code;
2488 lv_loc_subinv_type := nvl(r_trx.loc_subinv_type, 'X');
2489
2490 END IF; --End if for RETURN TO RECEIVING
2491
2492 OPEN c_base_trx(p_transaction_id);
2493 FETCH c_base_trx INTO r_base_trx;
2494 CLOSE c_base_trx;
2495
2496 if r_trx.organization_type = 'M' THEN
2497
2498 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
2499 if r_trx.item_cenvatable = 'N' THEN
2500
2501 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
2502 lv_include_cenvat_in_costing :='Y';
2503
2504 elsif r_trx.item_class in ('OTIN','OTEX') then
2505
2506 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
2507 lv_include_cenvat_in_costing :='Y';
2508
2509 /* modified the following condition by vijay Shankar for Bug#4179823
2510 elsif r_base_trx.source_document_code <> 'REQ' and r_trx.item_class in ('FGIN', 'FGEX') then */
2511 elsif r_base_trx.source_document_code NOT IN ('REQ', 'RMA') and r_trx.item_class in ('FGIN', 'FGEX') --added for bug #13514510
2512 and r_trx.organization_type = 'M'
2513 then
2514 p_codepath := jai_general_pkg.plot_codepath(9.1, p_codepath);
2515 lv_include_cenvat_in_costing :='Y';
2516
2517 elsif lv_destination_type = 'INVENTORY' THEN
2518
2519 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
2520 if r_trx.base_asset_inventory = 2 then
2521
2522 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
2523 if r_trx.item_class not in ('CGIN','CGEX') then
2524
2525 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
2526 lv_include_cenvat_in_costing :='Y';
2527
2528 elsif r_trx.item_class in ('CGIN','CGEX') then
2529
2530 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
2531 if lv_loc_subinv_type IN ('X','N') then
2532 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
2533 lv_include_cenvat_in_costing :='Y';
2534 end if;
2535
2536 end if; --end if for r_trx.item_class
2537
2538 elsif r_trx.base_asset_inventory = 1 then
2539
2540 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
2541 if lv_loc_subinv_type IN ('X','N') then
2542 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
2543 lv_include_cenvat_in_costing :='Y';
2544 end if;
2545
2546 end if; --end if for r_trx.base_asset_inventory
2547
2548 elsif lv_destination_type = 'EXPENSE' THEN
2549
2550 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
2551 if r_trx.item_class not in ('CGIN','CGEX','RMIN','RMEX','CCIN','CCEX') THEN /*RMIN,RMEX,CCIN,CCEX added for bug 10335708*/
2552 lv_include_cenvat_in_costing :='Y';
2553 end if;
2554 else
2555 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
2556 lv_include_cenvat_in_costing :='X';
2557 end if; --end if for r_trx.item_cenvatable='N'
2558
2559 elsif r_trx.organization_type = 'T' THEN
2560
2561 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
2562 if r_trx.item_trading_flag <> 'Y'
2563 or r_trx.item_excisable <> 'Y'
2564 or r_trx.excise_in_trading <> 'Y'
2565 then
2566 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
2567 lv_include_cenvat_in_costing :='Y';
2568 end if;
2569
2570 else
2571
2572 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
2573 lv_include_cenvat_in_costing :='X';
2574
2575 end if; --r_trx.organization_type = 'M'
2576
2577 <<end_of_procedure>>
2578
2579 if lv_include_cenvat_in_costing is null then
2580 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
2581 lv_include_cenvat_in_costing :='N';
2582 end if;
2583
2584 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath, null, 'END'); /* 23 */
2585 lv_ret_value := lv_include_cenvat_in_costing;
2586
2587 return lv_ret_value;
2588
2589 exception
2590 when others then
2591 p_process_status := 'E';
2592 p_process_message := 'DELIVER_RTR_PKG.include_cenvat_in_costing:' || SQLERRM ;
2593 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
2594 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 24 */
2595 return null;
2596 end include_cenvat_in_costing;
2597 /*procedures added for bug 10335708*/
2598
2599 PROCEDURE pr_issue_auto_trans
2600 (r_trx IN jai_rcv_transactions%ROWTYPE,
2601 pn_transaction_id IN NUMBER,
2602 p_rg23_entry IN NUMBER,
2603 p_rg_i_entry IN NUMBER,
2604 p_process_status OUT NOCOPY VARCHAR2,
2605 p_process_message OUT NOCOPY VARCHAR2)
2606 IS
2607 PRAGMA autonomous_transaction;
2608 ln_register_id NUMBER;
2609 lv_transaction_type jai_rcv_transactions.transaction_type%TYPE;
2610 ln_quantity NUMBER;
2611 lv_slno NUMBER;
2612
2613 BEGIN
2614
2615 IF r_trx.transaction_type = 'CORRECT'
2616 THEN
2617 --p_codepath := jai_general_pkg.plot_codepath(1.1, p_codepath);
2618 lv_transaction_type := r_trx.parent_transaction_type;
2619 ELSE
2620 --p_codepath := jai_general_pkg.plot_codepath(1.2, p_codepath);
2621 lv_transaction_type := r_trx.transaction_type;
2622 END IF;
2623 --p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
2624
2625 IF lv_transaction_type = 'DELIVER'
2626 THEN
2627 --p_codepath := jai_general_pkg.plot_codepath(2.1, p_codepath);
2628 ln_quantity := -1*r_trx.quantity;
2629 ELSE
2630 --p_codepath := jai_general_pkg.plot_codepath(2.2, p_codepath);
2631 ln_quantity := r_trx.quantity;
2632 END IF;
2633 --p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
2634
2635 IF Nvl(p_rg23_entry,0)=1
2636 THEN
2637 jai_cmn_rg_23ac_i_trxs_PKG.insert_row(
2638 p_register_id => ln_register_id,
2639 p_inventory_item_id => r_trx.inventory_item_id,
2640 p_organization_id => r_trx.organization_id,
2641 p_quantity_received => ln_quantity,
2642 p_receipt_id => r_trx.transaction_id,
2643 p_transaction_type => lv_transaction_type,
2644 p_receipt_date => r_trx.transaction_date, -- Why cant this be ShipmentHeader.Receipt_date
2645 p_po_header_id => NULL,
2646 p_po_header_date => NULL,
2647 p_po_line_id => NULL,
2648 p_po_line_location_id => NULL,
2649 p_vendor_id => NULL,
2650 p_vendor_site_id => NULL,
2651 p_customer_id => NULL,
2652 p_customer_site_id => NULL,
2653 p_goods_issue_id => NULL,
2654 p_goods_issue_date => NULL,
2655 p_goods_issue_quantity => NULL,
2656 p_sales_invoice_id => NULL,
2657 p_sales_invoice_date => NULL,
2658 p_sales_invoice_quantity => NULL,
2659 p_excise_invoice_id => NULL,
2660 p_excise_invoice_date => NULL,
2661 p_oth_receipt_quantity => NULL,
2662 p_oth_receipt_id => NULL,
2663 p_oth_receipt_date => NULL,
2664 p_register_type => jai_general_pkg.get_rg_register_type(p_item_class => r_trx.item_class),
2665 p_identification_no => NULL,
2666 p_identification_mark => NULL,
2667 p_brand_name => NULL,
2668 p_date_of_verification => NULL,
2669 p_date_of_installation => NULL,
2670 p_date_of_commission => NULL,
2671 p_regiser_id_part_ii => NULL,
2672 p_place_of_install => NULL,
2673 p_remarks => NULL,
2674 p_location_id => r_trx.location_id,
2675 p_transaction_uom_code => r_trx.uom_code,
2676 p_transaction_date => r_trx.transaction_date,
2677 p_basic_ed => NULL,
2678 p_additional_ed => NULL,
2679 p_additional_cvd => NULL,
2680 p_other_ed => NULL,
2681 p_charge_account_id => NULL,
2682 p_transaction_source => NULL,
2683 p_called_from => 'ja_in_deliver_rtr_pkg.pr_issue_expense_delivery',
2684 p_simulate_flag => 'N',
2685 p_process_status => p_process_status,
2686 p_process_message => p_process_message
2687 );
2688 ELSIF Nvl(p_rg_i_entry,0)=1
2689 THEN
2690 --p_codepath := jai_general_pkg.plot_codepath(6.3, p_codepath);
2691 jai_cmn_rg_i_trxs_pkg.create_rg1_entry(
2692 p_register_id => ln_register_id,
2693 p_register_id_part_ii => null,
2694 p_fin_year => jai_general_pkg.get_fin_year(p_organization_id => r_trx.organization_id),
2695 p_slno => lv_slno,
2696 p_transaction_id => null,
2697 p_organization_id => r_trx.organization_id,
2698 p_location_id => r_trx.location_id,
2699 p_transaction_date => r_trx.transaction_date,
2700 p_inventory_item_id => r_trx.inventory_item_id,
2701 p_transaction_type => 'PI',
2702 p_ref_doc_id => to_char(pn_transaction_id),
2703 p_quantity => r_trx.quantity,
2704 p_transaction_uom_code => r_trx.uom_code,
2705 p_issue_type => 'HU',
2706 p_excise_duty_amount => null,
2707 p_excise_invoice_number => null,
2708 p_excise_invoice_date => null,
2709 p_payment_register => null,
2710 p_charge_account_id => NULL,
2711 p_range_no => NULL,
2712 p_division_no => NULL,
2713 p_remarks => null,
2714 p_basic_ed => NULL,
2715 p_additional_ed => NULL,
2716 p_other_ed => NULL,
2717 p_assessable_value => null,
2718 p_excise_duty_rate => NULL,
2719 p_vendor_id => NULL,
2720 p_vendor_site_id => NULL,
2721 p_customer_id => NULL,
2722 p_customer_site_id => NULL,
2723 p_creation_date => SYSDATE,
2724 p_created_by => fnd_global.user_id,
2725 p_last_update_date => SYSDATE,
2726 p_last_updated_by => fnd_global.user_id,
2727 p_last_update_login => fnd_global.login_id,
2728 p_called_from => 'RECEIPTS',
2729 p_cess_amount => NULL ,
2730 p_sh_cess_amount => NULL
2731
2732 );
2733 -- p_codepath := jai_general_pkg.plot_codepath(6.4, p_codepath);
2734 END IF;
2735
2736 COMMIT;
2737 EXCEPTION
2738 WHEN OTHERS THEN
2739 p_process_status := 'E';
2740 p_process_message := 'Error when making issue entry in the quantity register: '||SQLERRM;
2741 END pr_issue_auto_trans;
2742
2743 PROCEDURE pr_issue_expense_delivery
2744 (pn_transaction_id IN NUMBER,
2745 pv_process_message OUT NOCOPY VARCHAR2,
2746 pv_process_status OUT NOCOPY VARCHAR2,
2747 p_codepath IN OUT NOCOPY VARCHAR2)
2748 IS
2749
2750 CURSOR c_trx IS
2751 SELECT *
2752 FROM jai_rcv_transactions
2753 WHERE transaction_id = pn_transaction_id;
2754
2755 CURSOR c_rg23_i_entry (cp_transaction_id NUMBER) IS
2756 SELECT 1
2757 FROM jai_cmn_rg_23ac_i_trxs
2758 WHERE receipt_ref = cp_transaction_id
2759 AND receipt_ref IS NOT NULL
2760 AND NOT EXISTS (SELECT 1 FROM jai_cmn_rg_23ac_i_trxs WHERE receipt_ref = pn_transaction_id AND receipt_ref IS NOT NULL);
2761
2762 CURSOR c_rg_i_entry (cp_transaction_id NUMBER, cp_item_id NUMBER, cp_orgn_id NUMBER, cp_location_id NUMBER) IS
2763 SELECT 1
2764 FROM jai_cmn_rg_i_trxs
2765 WHERE ref_doc_no = To_Char(cp_transaction_id)
2766 AND inventory_item_id = cp_item_id
2767 AND organization_id = cp_orgn_id
2768 AND location_id = cp_location_id
2769 AND NOT EXISTS (SELECT 1
2770 FROM jai_cmn_rg_i_trxs
2771 WHERE ref_doc_no = To_Char(pn_transaction_id)
2772 AND organization_id = cp_orgn_id
2773 AND location_id = cp_location_id
2774 AND inventory_item_id = cp_item_id);
2775
2776 ln_rg23_i NUMBER;
2777 ln_rg_i NUMBER;
2778 r_trx c_trx%ROWTYPE;
2779 ln_rcv_trx_id NUMBER;
2780
2781 BEGIN
2782 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'ja_in_deliver_rtr_pkg.pr_issue_expense_delivery', 'START');
2783 ln_rg23_i := 0;
2784 ln_rg_i := 0;
2785
2786 OPEN c_trx;
2787 FETCH c_trx INTO r_trx;
2788 CLOSE c_trx;
2789
2790 ln_rcv_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
2791 (
2792 r_trx.transaction_id,
2793 r_trx.shipment_line_id,
2794 'RECEIVE'
2795 );
2796 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
2797 OPEN c_rg23_i_entry(ln_rcv_trx_id);
2798 FETCH c_rg23_i_entry INTO ln_rg23_i;
2799 CLOSE c_rg23_i_entry;
2800 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath);
2801 IF Nvl(ln_rg23_i,0) = 0
2802 THEN
2803 p_codepath := jai_general_pkg.plot_codepath(5.1, p_codepath);
2804 OPEN c_rg_i_entry(ln_rcv_trx_id, r_trx.inventory_item_id, r_trx.organization_id, r_trx.location_id);
2805 FETCH c_rg_i_entry INTO ln_rg_i;
2806 CLOSE c_rg_i_entry;
2807 p_codepath := jai_general_pkg.plot_codepath(5.2, p_codepath);
2808 END IF;
2809 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath);
2810
2811 pr_issue_auto_trans(r_trx, pn_transaction_id, ln_rg23_i, ln_rg_i, pv_process_status, pv_process_message);
2812
2813 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath, NULL, 'END');
2814
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817 pv_process_status := 'E';
2818 pv_process_message := 'Error when making issue entry in the quantity register: '||SQLERRM;
2819 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, NULL, 'END');
2820 END pr_issue_expense_delivery;
2821
2822
2823 END jai_rcv_deliver_rtr_pkg;