[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.6.12010000.2 2008/11/20 11:15:22 mbremkum 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 DEPENDANCY:
87 -----------
88 IN60105D2 + 3496408
89 IN60106 + 4239736 + 4245089 + 4346453
90
91 16. 28-NOV-2007 Added by Jia Li for India tax inclusive
92 17. 19-Mar-2008 Modified by Jia Li for Bug#6877290
93 Issue: UNIT COST CALCULATE IS INCORRECT IN AVG ORGANIZATION
94 Fixed: Modified procedure get_tax_amount_breakup,
95 change modvat_amount and non_modvat_amount calculate position,
96 moved tax_amount calculate into inclusive_flag clause
97 ----------------------------------------------------------------------------------------------------------------------------*/
98
99 PROCEDURE process_transaction
100 (
101 p_transaction_id IN NUMBER,
102 p_simulate IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
103 p_codepath IN OUT NOCOPY VARCHAR2,
104 p_process_message OUT NOCOPY VARCHAR2,
105 p_process_status OUT NOCOPY VARCHAR2,
106 -- Vijay Shankar for Bug#4068823. RECEIPTS DELUG
107 p_process_special_source IN VARCHAR2 DEFAULT NULL,
108 p_process_special_amount IN NUMBER DEFAULT NULL
109 ) is
110
111 /* Cursor Definitions */
112 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
113 SELECT *
114 FROM JAI_RCV_TRANSACTIONS
115 WHERE transaction_id = cp_transaction_id;
116
117 CURSOR c_base_line_dtls(cp_transaction_id IN NUMBER) IS
118 SELECT quantity, unit_of_measure, source_doc_unit_of_measure, source_doc_quantity
119 from rcv_transactions
120 where transaction_id = cp_transaction_id;
121
122 CURSOR c_rcv_trx(cp_transaction_id IN NUMBER) IS
123 SELECT *
124 FROM rcv_transactions
125 where transaction_id = cp_transaction_id;
126
127 CURSOR c_mtl_trx(cp_organization_id IN NUMBER) IS
128 /* Bug 4941701. Added by Lakshmi Gopalsami
129 For performance fix. SQL id - 14829562
130 Changed the reference mtl_parameters from mtl_parameters_view
131 and selected process_enabled_flag in the cursor. */
132 SELECT process_enabled_flag
133 FROM mtl_parameters
134 WHERE Organization_id = cp_organization_id;
135
136 /* Record Declarations */
137 r_trx c_trx%rowtype;
138 r_base_line_dtls c_base_line_dtls%rowtype;
139 r_rcv_trx c_rcv_trx%rowtype;
140 r_rcv_dlry_trx c_rcv_trx%rowtype; -- Bug#3949518 (3927371)
141 r_mtl_trx c_mtl_trx%rowtype;
142 r_dlry_trx c_trx%rowtype;
143
144
145 /* Variable Declarations */
146 lv_procedure_name VARCHAR2(60); --File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.process_transaction';
147 -- lv_register_type VARCHAR2(1); --Either A or C.
148 lv_opm_organization_flag mtl_parameters_view.process_enabled_flag%type;
149 lv_statement_id VARCHAR2(4);
150 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
151 lv_accounting_type VARCHAR2(30);
152 lv_include_cenvat_in_costing VARCHAR2(1);
153 lv_destination_type rcv_transactions.destination_type_code%type;
154
155
156 /* Number Declarations */
157 ln_apportion_factor NUMBER; --File.Sql.35 Cbabu := 1; -- default value added by Vijay Shankar for Bug#4068823 for RECEIPTS DEPLUG
158 ln_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
159 ln_non_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
160 ln_other_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
161 ln_total NUMBER; --File.Sql.35 Cbabu := 0;
162 ln_opm_total NUMBER; --File.Sql.35 Cbabu := 0;
163 ln_receiving_account_id rcv_parameters.receiving_account_id%type;
164 ln_dlry_trx_id JAI_RCV_TRANSACTIONS.transaction_id%type;
165
166 ln_receive_trx_id JAI_RCV_TRANSACTIONS.transaction_id%type;
167 lv_temp VARCHAR2(50);
168
169 lv_cenvat_costed_flag VARCHAR2(15);
170
171 BEGIN
172 lv_procedure_name := 'jai_rcv_deliver_rtr_pkg.process_transaction';
173 lv_debug := jai_constants.yes;
174 ln_apportion_factor := 1; -- default value added by Vijay Shankar for Bug#4068823 for RECEIPTS DEPLUG
175 ln_modvat_amount := 0;
176 ln_non_modvat_amount := 0;
177 ln_other_modvat_amount := 0;
178 ln_total := 0;
179 ln_opm_total := 0;
180
181 -- this is to identify the path in SQL TRACE file if any problem occured
182 SELECT 'jai_rcv_deliver_rtr_pkg-'||p_transaction_id INTO lv_temp FROM DUAL;
183
184 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'));
185
186 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.process_transaction', 'START'); /* 1 */
187
188 /* Fetch all the information from JAI_RCV_TRANSACTIONS */
189 OPEN c_trx(p_transaction_id);
190 FETCH c_trx INTO r_trx;
191 CLOSE c_trx;
192
193 lv_statement_id := '1';
194 ln_receive_trx_id := r_trx.tax_transaction_id;
195
196 /* Vijay Shankar for Bug#4068823
197 jai_rcv_trx_processing_pkg.get_ancestor_id
198 (
199 p_transaction_id => r_trx.transaction_id,
200 p_shipment_line_id => r_trx.shipment_line_id,
201 p_required_trx_type => 'RECEIVE'
202 );
203 */
204
205 /* Fetch all the information from rcv_transactions for transaction type RECEIVE*/
206 OPEN c_base_line_dtls(ln_receive_trx_id);
207 FETCH c_base_line_dtls INTO r_base_line_dtls;
208 CLOSE c_base_line_dtls;
209
210 lv_statement_id := '2';
211 if r_base_line_dtls.quantity = 0 THEN
212 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
213 p_process_status := 'E';
214 p_process_message := 'The Quantity in rcv_transactions for RECEIVE line is Zero';
215 goto exit_from_procedure;
216 end if;
217
218 --p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
219 --lv_statement_id := '2.1';
220
221 /* Fetch the information of certain columns which are not present in JAI_RCV_TRANSACTIONS */
222 OPEN c_rcv_trx(p_transaction_id);
223 FETCH c_rcv_trx into r_rcv_trx;
224 CLOSE c_rcv_trx;
225
226 lv_statement_id := '3';
227 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
228
229 /* Fetch the Receiving Account Id */
230 ln_receiving_account_id := receiving_account
231 (
232 p_organization_id => r_trx.organization_id,
233 p_process_message => p_process_message,
234 p_process_status => p_process_status,
235 p_codepath => p_codepath
236 );
237
238 if p_process_status in ('E', 'X') THEN
239 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
240 goto exit_from_procedure;
241 end if;
242
243 lv_statement_id := '4';
244
245 if (r_trx.transaction_type ='CORRECT' and r_trx.parent_transaction_type = 'DELIVER')
246 or r_trx.transaction_type = 'DELIVER'
247 then
248 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
249 lv_accounting_type := 'REGULAR';
250 elsif (r_trx.transaction_type ='CORRECT' and r_trx.parent_transaction_type = 'RETURN TO RECEIVING')
251 or r_trx.transaction_type = 'RETURN TO RECEIVING'
252 then
253 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
254 lv_accounting_type := 'REVERSAL';
255 end if;
256
257 /* following condition added by Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
258 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl.
259 */
260 if nvl(p_process_special_source, 'XX') NOT IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim) then
261
262 -- following gets executed only for NORMAL DELIVER and RTR transactions and not for UNCLAIM Processing till <<start_of_actual_processing>>
263 lv_statement_id := '5';
264 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
265
266 /* Apportion Factor */
267 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
268 (p_transaction_id => r_trx.transaction_id);
269
270 if lv_debug ='Y' then
271 fnd_file.put_line( fnd_file.log, '1.1 ln_apportion_factor ' || ln_apportion_factor );
272 end if;
273
274 /*Step 1 : Call Individual Tax Entries As this should happen in any case if
275 Recoverable Taxes other than Excise Exist */
276 deliver_rtr_reco_nonexcise
277 (
278 p_transaction_id => r_trx.transaction_id,
279 p_transaction_date => r_trx.transaction_date,
280 p_organization_id => r_trx.organization_id,
281 p_transaction_type => r_trx.transaction_type,
282 p_parent_transaction_type => r_trx.parent_transaction_type,
283 p_receipt_num => r_trx.receipt_num,
284 p_shipment_line_id => r_trx.shipment_line_id,
285 p_currency_conversion_rate => r_trx.currency_conversion_rate,
286 p_apportion_factor => ln_apportion_factor,
287 p_receiving_account_id => ln_receiving_account_id,
288 p_accounting_type => lv_accounting_type,
289 p_simulate => p_simulate,
290 p_process_message => p_process_message,
291 p_process_status => p_process_status,
292 p_codepath => p_codepath
293 );
294
295 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
296 if p_process_status IN ('E', 'X') THEN
297 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
298 goto exit_from_procedure;
299 end if;
300
301 /* Get Register Type */
302 -- lv_register_type := jai_general_pkg.get_rg_register_type(p_item_class => r_trx.item_class);
303
304 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
305 get_tax_amount_breakup
306 (
307 p_shipment_line_id => r_trx.shipment_line_id,
308 p_transaction_id => r_trx.transaction_id,
309 p_curr_conv_rate => r_trx.currency_conversion_rate,
310 p_excise_amount => ln_modvat_amount,
311 p_non_modvat_amount => ln_non_modvat_amount ,
312 p_other_modvat_amount => ln_other_modvat_amount ,
313 p_process_message => p_process_message,
314 p_process_status => p_process_status,
315 p_codepath => p_codepath
316 );
317
318 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
319 if p_process_status in ('E', 'X') THEN
320 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
321 goto exit_from_procedure;
322 end if;
323
324 lv_include_cenvat_in_costing := include_cenvat_in_costing
325 (
326 p_transaction_id => p_transaction_id,
327 p_process_message => p_process_message,
328 p_process_status => p_process_status,
329 p_codepath => p_codepath
330 );
331
332 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
333 if p_process_status in ('E', 'X') THEN
334 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
335 goto exit_from_procedure;
336 end if;
337
338 /* Logic to arrive at the Total for which Costing or Expense Accounting has to be done */
339 if lv_include_cenvat_in_costing ='Y' then
340 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
341 ln_total := nvl(ln_non_modvat_amount,0) + nvl(ln_modvat_amount,0);
342 else
343 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
344 ln_total := nvl(ln_non_modvat_amount,0);
345 end if;
346
347 end if; -- end of p_process_special_source not in ( jai_constants.cenvat_noclaim ...
348
349 /* following condition added by Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
350 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
351 IF p_process_special_source IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim) THEN
352 ln_total := p_process_special_amount;
353 p_codepath := jai_general_pkg.plot_codepath(18.1, p_codepath);
354 ELSE
355 ln_total := nvl(ln_total,0); --In case the Total is Null.
356 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
357 END IF;
358
359 if lv_debug='Y' THEN
360 fnd_file.put_line( fnd_file.log, ' 1.3 ln_modvat_amount ='|| ln_modvat_amount
361 ||', ln_non_modvat_amount =' || ln_non_modvat_amount
362 ||', ln_other_modvat_amount ='|| ln_other_modvat_amount
363 ||', ln_total ='|| ln_total);
364 end if;
365
366 if ln_total = 0 then
367 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
368 -- Vijay Shankar for Bug#4071458
369 -- following if condition added to return successfully so that Individual Accounting happens without Costing/Expense Accounting
370 if ln_other_modvat_amount <> 0 then
371 p_process_status := 'Y';
372 else
373
374 p_process_status := 'X';
375 p_process_message := 'Non cenvatable/recoverable taxes doesnot exist. As a result, no Accounting/Costing';
376 end if;
377
378 goto exit_from_procedure;
379 else
380 /*Proportionate the Total with the Quantity of this transaction */
381 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
382 ln_opm_total := ln_total; /*This Total is required as in OPM Organization, costing would be based on source doc quantity */
383 ln_total := ln_total * ln_apportion_factor; /*This Amount would be the one which would be used for costing */
384 end if;
385
386 /* Logic to arrive at the Total for which Costing or Expense Accounting Ends here */
387 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
388 open c_mtl_trx(r_trx.organization_id);
389 fetch c_mtl_trx into r_mtl_trx;
390 close c_mtl_trx;
391
392 /* Following Hierarchy is followed for Deciding on what has to happen
393 1. Expense Routing
394 2. OPM Costing
395 3. Average Costing
396 4. Standard Costing.
397 */
398
399 if -- (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type = 'DELIVER') or Vijay Shankar for Bug#4038034
400 r_trx.transaction_type = 'DELIVER'
401 then
402
403 lv_destination_type := r_trx.destination_type_code;
404
405 elsif -- 'DELIVER' in the following if elsif condition is added by Vijay Shankar for Bug#4038034
406 (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')) or
407 r_trx.transaction_type = 'RETURN TO RECEIVING'
408 then
409
410 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
411 (
412 r_trx.transaction_id,
413 r_trx.shipment_line_id,
414 'DELIVER'
415 );
416
417 open c_trx(ln_dlry_trx_id);
418 fetch c_trx into r_dlry_trx;
419 close c_trx;
420
421 lv_destination_type := r_dlry_trx.destination_type_code;
422
423 OPEN c_rcv_trx(ln_dlry_trx_id);
424 FETCH c_rcv_trx into r_rcv_dlry_trx;
425 CLOSE c_rcv_trx;
426
427 end if;
428
429 if lv_destination_type ='EXPENSE'
430 or r_trx.inv_asset_flag ='N'
431 or r_trx.inv_item_flag ='N'
432 or r_trx.base_asset_inventory = 2
433 then
434
435 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
436
437 expense_accounting
438 (
439 p_transaction_id => r_trx.transaction_id,
440 p_transaction_date => r_trx.transaction_date,
441 p_organization_id => r_trx.organization_id,
442 p_transaction_type => r_trx.transaction_type,
443 p_parent_transaction_type => r_trx.parent_transaction_type,
444 p_receipt_num => r_trx.receipt_num,
445 p_shipment_line_id => r_trx.shipment_line_id,
446 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
447 p_accounted_amount => ln_total,
448 p_receiving_account_id => ln_receiving_account_id,
449 p_source_document_code => r_rcv_trx.source_document_code,
450 p_po_distribution_id => r_rcv_trx.po_distribution_id,
451 p_po_line_location_id => r_rcv_trx.po_line_location_id,
452 p_inventory_item_id => r_trx.inventory_item_id,
453 p_accounting_type => lv_accounting_type,
454 p_simulate => p_simulate,
455 p_process_message => p_process_message,
456 p_process_status => p_process_status,
457 p_codepath => p_codepath,
458 p_process_special_source => p_process_special_source
459 );
460
461 p_codepath := jai_general_pkg.plot_codepath(24, p_codepath); /* 24 */
462
463 if p_process_status in ('E', 'X') THEN
464 p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
465 goto exit_from_procedure;
466 end if;
467
468 elsif nvl(r_mtl_trx.process_enabled_flag,'N') ='Y' then /* OPM Costing Route */
469
470 /* In case of OPM Organizations, No Costing Impact is present in case of RTR Transactions */
471
472
473 if (r_trx.transaction_type ='CORRECT' AND r_trx.parent_transaction_type ='RETURN TO RECEIVING')
474 or r_trx.transaction_type ='RETURN TO RECEIVING'
475 then
476 p_codepath := jai_general_pkg.plot_codepath(26, p_codepath); -- 26
477 goto exit_from_procedure;
478 end if;
479
480 --commented as opm is not taken part of this enhancement.
481 -- Call to the following procedure is openedup by Vijay Shankar for Bug#4068823 for RECEIPT DEPLUG
482 opm_costing (
483 p_transaction_id => r_trx.transaction_id,
484 p_transaction_date => r_trx.transaction_date,
485 p_organization_id => r_trx.organization_id,
486 p_costing_amount => ln_opm_total, --To be checked /* INR Total */
487 p_receiving_account_id => ln_receiving_account_id,
488 p_rcv_unit_of_measure => r_base_line_dtls.unit_of_measure, --Indicates UOM of RECEIVE Line
489 p_rcv_source_unit_of_measure => r_base_line_dtls.source_doc_unit_of_measure, --Indicates Source UOM of RECEIVE Line
490 p_rcv_quantity => r_base_line_dtls.quantity, -- Indicates Quantity of RECEIVE Line
491 p_source_doc_quantity => r_base_line_dtls.source_doc_quantity, -- Indicates Source doc Quantity of RECEIVE Line
492 p_source_document_code => r_rcv_trx.source_document_code,
493 p_po_distribution_id => r_rcv_trx.po_distribution_id,
494 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
495 p_simulate => p_simulate,
496 p_process_message => p_process_message,
497 p_process_status => p_process_status,
498 p_codepath => p_codepath,
499 p_process_special_source => p_process_special_source,
500 /* following parameter added by Vijay Shankar for Bug#4229164 */
501 p_currency_conversion_rate => nvl(r_trx.currency_conversion_rate, 1)
502 );
503
504 if p_process_status in ('E', 'X') THEN
505 goto exit_from_procedure;
506 end if;
507
508 elsif r_trx.costing_method = 2 then
509
510 p_codepath := jai_general_pkg.plot_codepath(27, p_codepath); /* 27 */
511 average_costing
512 (
513 p_transaction_id => r_trx.transaction_id,
514 p_transaction_date => r_trx.transaction_date,
515 p_organization_id => r_trx.organization_id,
516 p_parent_transaction_type => r_trx.parent_transaction_type,
517 p_transaction_type => r_trx.transaction_type,
518 p_subinventory_code => nvl(r_rcv_trx.subinventory, r_rcv_dlry_trx.subinventory), -- Bug#3949518 (3927371)
519 p_costing_amount => ln_total,
520 p_receiving_account_id => ln_receiving_account_id,
521 p_source_document_code => r_rcv_trx.source_document_code,
522 p_po_distribution_id => r_rcv_trx.po_distribution_id,
523 p_unit_of_measure => r_rcv_trx.unit_of_measure,
524 p_inventory_item_id => r_trx.inventory_item_id,
525 p_accounting_type => lv_accounting_type,
526 p_simulate => p_simulate,
527 p_process_message => p_process_message,
528 p_process_status => p_process_status,
529 p_codepath => p_codepath,
530 p_process_special_source => p_process_special_source
531 );
532
533 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath); /* 28 */
534 if p_process_status in ('E', 'X') THEN
535 p_codepath := jai_general_pkg.plot_codepath(29, p_codepath); /* 29 */
536 goto exit_from_procedure;
537 end if;
538
539
540 elsif r_trx.costing_method = 1 then
541
542 p_codepath := jai_general_pkg.plot_codepath(30, p_codepath); /* 30 */
543
544 standard_costing
545 (
546 p_transaction_id => r_trx.transaction_id,
547 p_transaction_date => r_trx.transaction_date,
548 p_organization_id => r_trx.organization_id,
549 p_parent_transaction_type => r_trx.parent_transaction_type,
550 p_transaction_type => r_trx.transaction_type,
551 p_costing_amount => ln_total,
552 p_receiving_account_id => ln_receiving_account_id,
553 p_accounting_type => lv_accounting_type,
554 p_simulate => p_simulate,
555 p_process_message => p_process_message,
556 p_process_status => p_process_status,
557 p_codepath => p_codepath,
558 p_process_special_source => p_process_special_source
559 );
560
561 p_codepath := jai_general_pkg.plot_codepath(31, p_codepath); /* 31 */
562 if p_process_status in ('E', 'X') THEN
563 p_codepath := jai_general_pkg.plot_codepath(32, p_codepath); /* 32 */
564 goto exit_from_procedure;
565 end if;
566
567 end if; --r_mtl_trx.process_enabled_flag
568
569 /* following was coded to support UNCLAIM functionality during RECEIPTS DEPLUG. Vijay Shankar for Bug#4068823
570 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
571 IF lv_include_cenvat_in_costing = 'Y'
572 or p_process_special_source IN ( jai_constants.cenvat_noclaim, jai_constants.vat_noclaim)
573 THEN
574 p_codepath := jai_general_pkg.plot_codepath(33, p_codepath);
575 lv_cenvat_costed_flag := jai_constants.yes;
576 ELSE
577 p_codepath := jai_general_pkg.plot_codepath(34, p_codepath);
578 lv_cenvat_costed_flag := jai_constants.no;
579 END IF;
580
581 IF r_trx.attribute1 IS NULL
582 OR (r_trx.attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag
583 AND nvl(r_trx.attribute1,jai_constants.no) <> jai_constants.yes)
584 THEN
585
586 p_codepath := jai_general_pkg.plot_codepath(35, p_codepath);
587 jai_rcv_transactions_pkg.update_attributes(
588 p_transaction_id => p_transaction_id,
589 p_attribute1 => jai_rcv_deliver_rtr_pkg.cenvat_costed_flag,
590 p_attribute2 => lv_cenvat_costed_flag
591 );
592 END IF;
593
594 -- Process is Successful. Now the PROCESS_FLAG can be set to 'Y'
595 p_process_status := 'Y';
596
597 << exit_from_procedure >>
598 p_codepath := jai_general_pkg.plot_codepath(40, p_codepath, null, 'END'); /* 33 */
599 return;
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 p_process_status := 'E';
604 p_process_message := 'DELIVER_RTR_PKG.process_transaction:' || sqlerrm;
605 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
606 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 34 */
607 return;
608 END process_transaction;
609
610 /* ------------------------------------------------start of deliver_rtr_reco_nonexcise------------*/
611 PROCEDURE deliver_rtr_reco_nonexcise
612 (
613 p_transaction_id IN NUMBER,
614 p_transaction_date IN DATE,
615 p_organization_id IN NUMBER,
616 p_transaction_type IN VARCHAR2,
617 p_parent_transaction_type IN VARCHAR2,
618 p_receipt_num IN VARCHAR2,
619 p_shipment_line_id IN NUMBER,
620 p_currency_conversion_rate IN NUMBER,
621 p_apportion_factor IN NUMBER,
622 p_receiving_account_id IN NUMBER,
623 p_accounting_type IN VARCHAR2,
624 p_simulate IN VARCHAR2,
625 p_process_message OUT NOCOPY VARCHAR2,
626 p_process_status OUT NOCOPY VARCHAR2,
627 p_codepath IN OUT NOCOPY VARCHAR2
628 ) is
629
630 /*
631 Accounting Entries which happen in this scenario are
632 |------------------------------------------------------------------------------------------------
633 | Transaction | | | |
634 | Type | Amount | Credit | Debit |
635 | ==============|====================== |===========================|===========================|
636 | DELIVER | Total | Inv.Receiving | |
637 | DELIVER | Individual Tax Amt | | Individual Tax Accounts |
638 | RTR | Total | | Inv.Receiving |
639 | RTR | Individual Tax Amt | Individual Tax Accounts | |
640 | ============= | ======================|===========================|===========================|
641 ----------------------------------------------------------------------------------------------
642 */
643
644 /* Character Variable Declarations */
645 lv_reference_10_desc1 VARCHAR2(75);--File.Sql.35 Cbabu := 'India Local Receiving Entry for the Receipt Number ';
646 lv_reference_10_desc2 VARCHAR2(30);--File.Sql.35 Cbabu := ' For the Transaction Type ';
647 lv_reference_10_desc gl_interface.reference10%type;
648 lv_account_nature VARCHAR2(30);--File.Sql.35 Cbabu := 'Individual Tax';
649 lv_reference23 gl_interface.reference23%type;--File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise';
650 lv_source VARCHAR2(100);--File.Sql.35 Cbabu := 'Purchasing India';
651 lv_category VARCHAR2(100);--File.Sql.35 Cbabu := 'Receiving India';
652 lv_reference24 gl_interface.reference24%type;--File.Sql.35 Cbabu := 'rcv_transactions';
653 lv_reference25 gl_interface.reference25%type;--File.Sql.35 Cbabu := 'transaction_id';
654
655 ln_individual_tax_amount number;--File.Sql.35 Cbabu := 0;
656 ln_rec_account_tax_amount number;--File.Sql.35 Cbabu := 0;
657 ln_credit_amount number;
658 ln_debit_amount number;
659
660 BEGIN
661 --File.Sql.35 Cbabu
662 lv_account_nature := 'Individual Tax';
663 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';
664 lv_reference_10_desc2 := ' For the Transaction Type ';
665 lv_reference23 := 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise';
666 lv_source := 'Purchasing India';
667 lv_category := 'Receiving India';
668 lv_reference24 := 'rcv_transactions';
669 lv_reference25 := 'transaction_id';
670 ln_individual_tax_amount := 0;
671 ln_rec_account_tax_amount := 0;
672
673 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise', 'START'); /* 1 */
674
675 if p_transaction_type = 'CORRECT' then
676 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
677 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ||' of Type ' || p_parent_transaction_type;
678 else
679 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
680 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ;
681 end if;
682
683
684 For tax_rec IN
685 (
686 SELECT
687 sum(
688 rtl.tax_amount * (NVL(jtc.mod_cr_percentage, 0)/100)
689 * 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)
690 ) tax_amount,
691 jtc.tax_account_id
692 FROM JAI_RCV_LINE_TAXES rtl,
693 JAI_CMN_TAXES_ALL jtc
694 WHERE jtc.tax_id = rtl.tax_id
695 AND shipment_line_id = p_shipment_line_id
696 AND upper(rtl.tax_type) NOT IN ( 'EXCISE', 'ADDL. EXCISE',
697 'OTHER EXCISE', 'CVD','TDS', 'MODVAT RECOVERY',
698 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
699 jai_constants.tax_type_exc_edu_cess,
700 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
701 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
702 -- following condition added by Vijay Shankar for Bug#4068823. Service Tax Enhancement
703 -- this is added to Stop Recovery Service Tax Accounting, as this will be done during RECEIVE trx or
704 -- during Payables Invoice/Payment depending on transaction parameters
705 AND rtl.tax_type NOT IN (select attribute_code from JAI_RGM_REGISTRATIONS aa, JAI_RGM_DEFINITIONS bb
706 where aa.regime_id = bb.regime_id
707 /* vat_regime is included in the following clause by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
708 and bb.regime_code IN (jai_constants.service_regime, jai_constants.vat_regime)
709 and aa.registration_type = jai_constants.regn_type_tax_types )
710 AND NVL(rtl.modvat_flag, 'N') = 'Y'
711 GROUP BY jtc.tax_account_id
712 )
713 LOOP
714
715 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
716
717 if tax_rec.tax_account_id is null then
718 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
719 p_process_status := 'E';
720 p_process_message := 'The Tax Account is not found for this Tax : ';
721 goto exit_from_procedure;
722 end if;
723
724 ln_individual_tax_amount := NVL(tax_rec.tax_amount,0) * nvl(p_apportion_factor,0);
725 ln_rec_account_tax_amount := nvl(ln_rec_account_tax_amount,0) + nvl(ln_individual_tax_amount,0);
726
727 if ln_individual_tax_amount <> 0 then
728
729 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
730 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
731 p_transaction_type = 'DELIVER' then /* DELIVER scenario */
732
733 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
734 ln_credit_amount := NULL;
735 ln_debit_amount := ln_individual_tax_amount;
736
737 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
738 p_transaction_type = 'RETURN TO RECEIVING' then /* RTR scenario */
739
740 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
741 ln_credit_amount := ln_individual_tax_amount;
742 ln_debit_amount := NULL;
743
744 end if;
745
746 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
747 jai_rcv_accounting_pkg.process_transaction
748 (
749 p_transaction_id => p_transaction_id,
750 p_acct_type => p_accounting_type,
751 p_acct_nature => lv_account_nature,
752 p_source_name => lv_source,
753 p_category_name => lv_category,
754 p_code_combination_id => tax_rec.tax_account_id,
755 p_entered_dr => ln_debit_amount,
756 p_entered_cr => ln_credit_amount,
757 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
758 p_accounting_date => p_transaction_date,
759 p_reference_10 => lv_reference_10_desc, --Reference10
760 p_reference_23 => lv_reference23,
761 p_reference_24 => lv_reference24,
762 p_reference_25 => lv_reference25,
763 p_reference_26 => to_char(p_transaction_id),
764 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
765 p_simulate_flag => p_simulate,
766 p_codepath => p_codepath,
767 p_process_message => p_process_message,
768 p_process_status => p_process_status
769 );
770
771 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
772 if p_process_status IN ('E', 'X') then
773 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
774 goto exit_from_procedure;
775 end if;
776
777 end if; --end if for ln_individual_tax_amount <> 0
778 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
779 end loop; --End Loop for Tax Rec.
780
781 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
782 if ln_rec_account_tax_amount <> 0 then
783
784 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
785 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
786 p_transaction_type = 'DELIVER' then /* DELIVER scenario */
787
788 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
789 ln_credit_amount := ln_rec_account_tax_amount;
790 ln_debit_amount := NULL;
791
792 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
793 p_transaction_type = 'RETURN TO RECEIVING' then /* RTR scenario */
794
795 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
796 ln_credit_amount := NULL;
797 ln_debit_amount := ln_rec_account_tax_amount;
798
799 end if;
800
801
802 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
803 jai_rcv_accounting_pkg.process_transaction
804 (
805 p_transaction_id => p_transaction_id,
806 p_acct_type => p_accounting_type,
807 p_acct_nature => lv_account_nature,
808 p_source_name => lv_source,
809 p_category_name => lv_category,
810 p_code_combination_id => p_receiving_account_id,
811 p_entered_dr => ln_debit_amount,
812 p_entered_cr => ln_credit_amount,
813 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
814 p_accounting_date => p_transaction_date,
815 p_reference_10 => lv_reference_10_desc, --Reference10
816 p_reference_23 => lv_reference23,
817 p_reference_24 => lv_reference24,
818 p_reference_25 => lv_reference25,
819 p_reference_26 => to_char(p_transaction_id),
820 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
821 p_simulate_flag => p_simulate,
822 p_codepath => lv_reference23,
823 p_process_message => p_process_message,
824 p_process_status => p_process_status
825 );
826
827 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
828 if p_process_status in ('E', 'X') then
829 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
830 goto exit_from_procedure;
831 end if;
832 end if; --end if for ln_rec_account_tax_amount <> 0
833
834 << exit_from_procedure >>
835 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath, null, 'END'); /* 19 */
836 return;
837
838 EXCEPTION
839 WHEN OTHERS THEN
840 p_process_status := 'E';
841 p_process_message := 'DELIVER_RTR_PKG.deliver_rtr_reco_nonexcise:' || sqlerrm;
842 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
843 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 20 */
844 return;
845 END deliver_rtr_reco_nonexcise;
846
847 /*----------------------------------------------------------------------------------------*/
848 PROCEDURE get_tax_amount_breakup
849 (
850 p_shipment_line_id IN NUMBER,
851 p_transaction_id IN NUMBER,
852 p_curr_conv_rate IN NUMBER,
853 p_excise_amount OUT NOCOPY NUMBER,
854 p_non_modvat_amount OUT NOCOPY NUMBER,
855 p_other_modvat_amount OUT NOCOPY NUMBER,
856 p_process_message OUT NOCOPY VARCHAR2,
857 p_process_status OUT NOCOPY VARCHAR2,
858 p_codepath IN OUT NOCOPY VARCHAR2
859 ) IS
860
861 ln_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
862 ln_non_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
863 ln_other_modvat_amount NUMBER; --File.Sql.35 Cbabu := 0;
864 ln_conv_factor NUMBER;
865 lv_tax_modvat_flag JAI_RCV_LINE_TAXES.modvat_flag%type;
866 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
867
868 cursor c_ja_in_rcv_transactions(cp_transaction_id number) is
869 select item_trading_flag,organization_type,excise_in_trading,item_excisable
870 from JAI_RCV_TRANSACTIONS
871 where transaction_id = cp_transaction_id;
872
873 r_ja_in_rcv_transactions c_ja_in_rcv_transactions%rowtype;
874
875 BEGIN
876
877 --File.Sql.35 Cbabu
878 ln_modvat_amount := 0;
879 ln_non_modvat_amount := 0;
880 ln_other_modvat_amount := 0;
881 lv_debug := 'Y';
882
883 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.get_tax_amount_breakup' , 'START'); /* 1 */
884
885 OPEN c_ja_in_rcv_transactions(p_transaction_id);
886 FETCH c_ja_in_rcv_transactions into r_ja_in_rcv_transactions;
887 CLOSE c_ja_in_rcv_transactions;
888
889 FOR tax_rec IN
890 (
891 SELECT
892 rtl.tax_type,
893 nvl(rtl.tax_amount, 0) tax_amount,
894 nvl(rtl.modvat_flag, 'N') modvat_flag,
895 nvl(jtc.inclusive_tax_flag, 'N') inclusive_tax_flag, -- Added by Jia Li for India tax inclusive on 2007/11/28
896 nvl(rtl.currency, 'INR') currency,
897 nvl(jtc.mod_cr_percentage, 0) mod_cr_percentage
898 FROM
899 JAI_RCV_LINE_TAXES rtl,
900 JAI_CMN_TAXES_ALL jtc
901 WHERE
902 shipment_line_id = p_shipment_line_id
903 AND jtc.tax_id = rtl.tax_id
904
905 )
906 LOOP
907
908 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2*/
909 if tax_rec.currency <> jai_rcv_trx_processing_pkg.gv_func_curr THEN
910 ln_conv_factor := NVL(p_curr_conv_rate, 1);
911 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3*/
912 ELSE
913 ln_conv_factor := 1;
914 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4*/
915 end if;
916
917 /*
918 Comparison below is due to the case where Excise in RG23D ='Y', Tax_Rec.Modvat_Flag will be N
919 as Item Modvat Flag in the setup is No.
920 As a result of this, Excise should not be added to Item cost and hence deciding the Modvat
921 Amount solely upon the Modvat Flag in JAI_RCV_LINE_TAXES is wrong.
922 Hence, a variable ( lv_tax_modvat_flag) is first set based on the above permutations
923 and then a decision of whether the Excise needs to be added or not is done based on this flag.
924 */
925
926 if tax_rec.modvat_flag = 'Y'
927 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
928 'TDS', 'MODVAT RECOVERY',
929 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
930 jai_constants.tax_type_exc_edu_cess,
931 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
932 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
933 then
934
935 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
936 lv_tax_modvat_flag := 'Y';
937
938 elsif upper(tax_rec.modvat_flag) = 'Y'
939 and tax_rec.tax_type NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
940 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
941 jai_constants.tax_type_exc_edu_cess,
942 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
943 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
944 then
945
946 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
947 lv_tax_modvat_flag := 'Y';
948
949 elsif tax_rec.modvat_flag = 'N'
950 and r_ja_in_rcv_transactions.item_trading_flag = 'Y' /* Excise IN RG23D scenario */
951 and r_ja_in_rcv_transactions.excise_in_trading = 'Y'
952 and r_ja_in_rcv_transactions.item_excisable = 'Y'
953 and r_ja_in_rcv_transactions.organization_type = 'T'
954 and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
955 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
956 jai_constants.tax_type_exc_edu_cess,
957 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
958 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
959 then
960
961 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
962 lv_tax_modvat_flag := 'Y';
963
964 else
965 lv_tax_modvat_flag := 'N';
966
967 end if; --tax_rec.modvat_flag = 'Y'
968
969 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
970
971 if upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
972
973 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
974
975 if lv_tax_modvat_flag = 'Y'
976 and upper(tax_rec.tax_type) IN ( 'EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
977 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
978 jai_constants.tax_type_exc_edu_cess,
979 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
980 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
981 then
982
983 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
984 ln_modvat_amount := ln_modvat_amount + tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor;
985
986 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
987 -- TD15-Changed Standard and Average Costing
988 -- recoverable tax is inclusive, its costing effect needs to be negated
989 -- Modified by Jia Li for Bug#6877290
990 ----------------------------------------------------------------------
991 IF ( tax_rec.inclusive_tax_flag = 'Y' )
992 THEN
993 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1);
994 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
995 THEN
996 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor;
997 END IF; --tax_rec.inclusive_tax_flag = 'Y'
998 -----------------------------------------------------------------------
999 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1000
1001 elsif lv_tax_modvat_flag = 'Y'
1002 and upper(tax_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1003 jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
1004 jai_constants.tax_type_exc_edu_cess,
1005 jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
1006 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) -- added by csahoo for bug#5989740
1007 then
1008
1009 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1010 ln_other_modvat_amount := ln_other_modvat_amount + tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_conv_factor;
1011
1012 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
1013 -- TD15-Changed Standard and Average Costing
1014 -- recoverable tax is inclusive, its costing effect needs to be negated
1015 -- Modified by Jia Li for Bug#6877290
1016 ----------------------------------------------------------------------
1017 IF ( tax_rec.inclusive_tax_flag = 'Y' )
1018 THEN
1019 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor * (-1);
1020 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
1021 THEN
1022 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_conv_factor;
1023 END IF; --tax_rec.inclusive_tax_flag = 'Y'
1024 ----------------------------------------------------------------------
1025 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1026
1027 ELSIF lv_tax_modvat_flag ='N' and upper(tax_rec.tax_type) NOT IN ('TDS', 'MODVAT RECOVERY') THEN
1028
1029 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1030
1031 -- Added by Jia Li for India tax inclusive 2007/11/28, Begin
1032 -- TD15-Changed Standard and Average Costing
1033 -- non-recoverable tax is inclusive, its costing should not be considered as it is already costed.
1034 -- Modified by Jia Li for Bug#6877290
1035 ----------------------------------------------------------------------
1036 IF ( tax_rec.inclusive_tax_flag = 'Y' )
1037 THEN
1038 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * ln_conv_factor * 0;
1039 ELSIF ( tax_rec.inclusive_tax_flag = 'N' )
1040 THEN
1041 ln_non_modvat_amount := ln_non_modvat_amount + tax_rec.tax_amount * ln_conv_factor;
1042 END IF; --tax_rec.inclusive_tax_flag = 'Y'
1043 ----------------------------------------------------------------------
1044 -- Added by Jia Li for India tax inclusive 2007/11/28, End
1045
1046 end if; /* tax_rec.modvat_flag*/
1047 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
1048
1049 end if; /*tax_rec.tax_type NOT IN ('TDS', 'Modvat Recovery')*/
1050 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1051
1052 END LOOP;
1053
1054 /* Resseting the Out variables */
1055 p_excise_amount := ln_modvat_amount;
1056 p_non_modvat_amount := ln_non_modvat_amount;
1057 p_other_modvat_amount := ln_other_modvat_amount;
1058
1059 << exit_from_procedure >>
1060 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath, null, 'END'); /* 15 */
1061 return;
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 p_process_status := 'E';
1066 p_process_message := 'DELIVER_RTR_PKG.get_tax_amount_breakup:' || sqlerrm;
1067 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1068 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 16 */
1069 return;
1070
1071 END get_tax_amount_breakup;
1072
1073 /* ------------------------------------------------start of get_tax_amount_breakup------------*/
1074 PROCEDURE opm_costing
1075 (
1076 p_transaction_id IN NUMBER,
1077 p_transaction_date IN DATE,
1078 p_organization_id IN NUMBER,
1079 p_costing_amount IN NUMBER,
1080 p_receiving_account_id IN NUMBER,
1081 p_rcv_unit_of_measure IN VARCHAR2, /*Indicates UOM of RECEIVE Line */
1082 p_rcv_source_unit_of_measure IN VARCHAR2, /*Indicates Source UOM of RECEIVE Line */
1083 p_rcv_quantity IN NUMBER, /*Indicates Quantity of RECEIVE Line */
1084 p_source_doc_quantity IN NUMBER, /*Indicates Source doc Quantity of RECEIVE Line */
1085 p_source_document_code IN VARCHAR2,
1086 p_po_distribution_id IN NUMBER,
1087 p_subinventory_code IN VARCHAR2,
1088 p_simulate IN VARCHAR2,
1089 p_process_message OUT NOCOPY VARCHAR2,
1090 p_process_status OUT NOCOPY VARCHAR2,
1091 p_codepath IN OUT NOCOPY VARCHAR2,
1092 p_process_special_source IN VARCHAR2,
1093 /* following parameter added by Vijay Shankar for Bug#4229164 */
1094 p_currency_conversion_rate IN NUMBER
1095 ) IS
1096
1097 ln_rcv_quantity rcv_transactions.quantity%TYPE;
1098 ln_material_account_id mtl_secondary_inventories.material_account%type;
1099 ln_costing_amount NUMBER;
1100
1101 lv_accounting_type varchar2(30); --File.Sql.35 Cbabu := 'REGULAR'; /*Hard coded as Localization does not do anything in case of a RTR Transaction */
1102 lv_source varchar2(30); --File.Sql.35 Cbabu := 'Inventory India';
1103 lv_category varchar2(30); --File.Sql.35 Cbabu := 'MTL';
1104 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'OPM Costing';
1105 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
1106
1107 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch */
1108 ln_opm_costing_amount NUMBER;
1109 ln_apportion_factor NUMBER;
1110 -- End Bug 7581494
1111
1112 /*
1113 Accounting Entries which happen in this scenario are
1114 |---------------|-----------------------|---------------------------|--------------------------|
1115 | Transaction | | | |
1116 | Type | Amount | Credit | Debit |
1117 | ==============|====================== |===========================|==========================|
1118 | DELIVER | Total | Inv.Receiving | |
1119 | DELIVER | Total | | Material Account |
1120 ----------------|-----------------------|---------------------------|--------------------------|
1121
1122 Only JAI_RCV_JOURNAL_ENTRIES is recorded with above entries But RCV_TRANSACTIONS will be updated only
1123 once.
1124 */
1125
1126 BEGIN
1127 lv_accounting_type := 'REGULAR';
1128 lv_source := 'Inventory India';
1129 lv_category := 'MTL';
1130 lv_account_nature := 'OPM Costing';
1131 lv_debug := 'Y';
1132
1133 /* This comparison is for Evaluating the Quantity */
1134 /* Meaning of this comparison
1135 if the Unit Of Measure is changed while RECEIVING then
1136 source doc quantity of RCV_TRANSACTIONS needs to be picked up
1137 otherwise
1138 the Quantity of RCV_TRANSACTIONS can be picked up.
1139 */
1140
1141 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.opm_costing', 'START'); /* 1 */
1142
1143 -- This procedure should not be used. So returning back
1144 -- GOTO exit_from_procedure;
1145
1146 if p_rcv_unit_of_measure <> p_rcv_source_unit_of_measure then
1147 ln_rcv_quantity := p_source_doc_quantity;
1148 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1149 ELSE
1150 ln_rcv_quantity := p_rcv_quantity;
1151 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1152 end if;
1153
1154 if nvl(ln_rcv_quantity,0) = 0 THEN
1155 p_process_status := 'E';
1156 p_process_message := 'The Quantity in RECEIVE line is Zero ';
1157 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1158 goto exit_from_procedure;
1159 end if;
1160
1161 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1162 ln_material_account_id := material_account
1163 (
1164 p_organization_id => p_organization_id,
1165 p_source_document_code => p_source_document_code,
1166 p_po_distribution_id => p_po_distribution_id,
1167 p_subinventory => p_subinventory_code,
1168 p_process_message => p_process_message,
1169 p_process_status => p_process_status,
1170 p_codepath => p_codepath
1171 );
1172
1173 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1174
1175 if p_process_status in ('E', 'X') then
1176 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1177 goto exit_from_procedure;
1178 end if;
1179
1180 -- ln_costing_amount := p_costing_amount / ln_rcv_quantity;
1181 /* above cost calculation is modified as belowe by Vijay Shankar for Bug#4229164 */
1182
1183 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch */
1184 ln_costing_amount := p_costing_amount / p_currency_conversion_rate;
1185 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
1186 ( p_transaction_id => p_transaction_id);
1187 ln_opm_costing_amount := p_costing_amount * ln_apportion_factor ;
1188
1189 /* End Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch.*/
1190 if lv_debug ='Y' then
1191 fnd_file.put_line(fnd_file.log, 'OPM Costing. ln_apportion_factor:'||ln_apportion_factor
1192 ||', ln_opm_costing_amount:'||ln_opm_costing_amount
1193 ||', pCostAmt:'||p_costing_amount
1194 ||', Qty:'||ln_rcv_quantity
1195 ||', FinalCostAmt:'||ln_costing_amount
1196 ||', OPMCostAmt:'||ln_opm_costing_amount
1197 );
1198 end if;
1199
1200 /* Destination in this case is O1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1201 and also rcv_transactions would be updated */
1202 /* Credit Inventory Receiving Account */
1203 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1204 jai_rcv_accounting_pkg.process_transaction
1205 (
1206 p_transaction_id => p_transaction_id,
1207 p_acct_type => lv_accounting_type,
1208 p_acct_nature => lv_account_nature,
1209 p_source_name => lv_source,
1210 p_category_name => lv_category,
1211 p_code_combination_id => p_receiving_account_id,
1212 p_entered_dr => NULL, /* This should never be changed to Zero */
1213 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch
1214 * Changed from ln_costing_amount to ln_opm_costing_amount.
1215 */
1216 p_entered_cr => ln_opm_costing_amount,
1217 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1218 p_accounting_date => p_transaction_date,
1219 p_reference_10 => NULL,
1220 p_reference_23 => NULL,
1221 p_reference_24 => NULL,
1222 p_reference_25 => NULL,
1223 p_reference_26 => NULL,
1224 p_destination => 'O1', /*Indicates OPM Costing Entry */
1225 p_simulate_flag => p_simulate,
1226 p_codepath => p_codepath,
1227 p_process_message => p_process_message,
1228 p_process_status => p_process_status
1229 );
1230
1231 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1232
1233 if p_process_status in ('E', 'X') then
1234 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1235 goto exit_from_procedure;
1236 end if;
1237
1238 /* Debit Material Account
1239 Destination in this case is O1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1240 and also rcv_transactions would be updated */
1241
1242 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1243 jai_rcv_accounting_pkg.process_transaction
1244 (
1245 p_transaction_id => p_transaction_id,
1246 p_acct_type => lv_accounting_type,
1247 p_acct_nature => lv_account_nature,
1248 p_source_name => lv_source,
1249 p_category_name => lv_category,
1250 p_code_combination_id => ln_material_account_id,
1251 /* Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch
1252 * Changed from ln_costing_amount to ln_opm_costing_amount.
1253 */
1254 p_entered_dr => ln_opm_costing_amount,
1255 p_entered_cr => NULL, /* This should never be changed to Zero */
1256 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1257 p_accounting_date => p_transaction_date,
1258 p_reference_10 => NULL,
1259 p_reference_23 => NULL,
1260 p_reference_24 => NULL,
1261 p_reference_25 => NULL,
1262 p_reference_26 => NULL,
1263 p_destination => 'O2', /*Indicates OPM Costing Entry */
1264 p_simulate_flag => p_simulate,
1265 p_codepath => p_codepath,
1266 p_process_message => p_process_message,
1267 p_process_status => p_process_status
1268 );
1269
1270 if p_process_status in ('E', 'X') then
1271 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1272 goto exit_from_procedure;
1273 end if;
1274
1275 << exit_from_procedure >>
1276 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1277 return;
1278
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 p_process_status := 'E';
1282 p_process_message := 'DELIVER_RTR_PKG.opm_costing:' || sqlerrm;
1283 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1284 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 14 */
1285 return;
1286 END opm_costing;
1287
1288 /*---------------------------------------------------------------------------------------*/
1289 PROCEDURE expense_accounting
1290 (
1291 p_transaction_id IN NUMBER,
1292 p_transaction_date IN DATE,
1293 p_organization_id IN NUMBER,
1294 p_transaction_type IN VARCHAR2,
1295 p_parent_transaction_type IN VARCHAR2,
1296 p_receipt_num IN VARCHAR2,
1297 p_shipment_line_id IN NUMBER,
1298 p_subinventory_code IN VARCHAR2,
1299 p_accounted_amount IN NUMBER,
1300 p_receiving_account_id IN NUMBER,
1301 p_source_document_code IN VARCHAR2,
1302 p_po_distribution_id IN NUMBER,
1303 p_po_line_location_id IN NUMBER,
1304 p_inventory_item_id IN NUMBER,
1305 p_accounting_type IN VARCHAR2,
1306 p_simulate IN VARCHAR2,
1307 p_process_message OUT NOCOPY VARCHAR2,
1308 p_process_status OUT NOCOPY VARCHAR2,
1309 p_codepath IN OUT NOCOPY VARCHAR2,
1310 p_process_special_source IN VARCHAR2
1311 ) IS
1312
1313 /* This Procedure is meant for Expense Accounting Entries
1314 Accounting Entries in this context are:
1315
1316 |------------------------------------------------------------------------------------------------
1317 | Transaction | | | |
1318 | Type | Amount | Credit | Debit |
1319 | ==============|====================== |===========================|===========================|
1320 | DELIVER | Total | Inv.Receiving | |
1321 | DELIVER | Total | | Expense Account |
1322 -------------------------------------------------------------------------------------------------
1323 | RTR | Total | Expense Account | |
1324 | RTR | Total | | Inv.Receiving |
1325 -------------------------------------------------------------------------------------------------
1326
1327 */
1328
1329 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'Expense Accounting';
1330 lv_source VARCHAR2(100); --File.Sql.35 Cbabu := 'Purchasing India';
1331 lv_category VARCHAR2(100); --File.Sql.35 Cbabu := 'Receiving India';
1332 lv_reference23 gl_interface.reference23%type; --File.Sql.35 Cbabu := 'jai_rcv_deliver_rtr_pkg.expense_accounting';
1333 lv_reference24 gl_interface.reference24%type; --File.Sql.35 Cbabu := 'rcv_transactions';
1334 lv_reference25 gl_interface.reference25%type; --File.Sql.35 Cbabu := 'transaction_id';
1335 lv_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'Y';
1336 lv_reference_10_desc1 VARCHAR2(75); --File.Sql.35 Cbabu := 'India Local Receiving Entry for the Receipt Number ';
1337 lv_reference_10_desc2 VARCHAR2(30); --File.Sql.35 Cbabu := ' For the Transaction Type ';
1338 lv_reference_10_desc gl_interface.reference10%type;
1339
1340 ln_credit_amount NUMBER;
1341 ln_debit_amount NUMBER;
1342 ln_expense_account mtl_secondary_inventories.expense_account%type;
1343
1344
1345 BEGIN
1346 --File.Sql.35 Cbabu
1347 lv_account_nature := 'Expense Accounting';
1348 lv_source := 'Purchasing India';
1349 lv_category := 'Receiving India';
1350 lv_reference23 := 'jai_rcv_deliver_rtr_pkg.expense_accounting';
1351 lv_reference24 := 'rcv_transactions';
1352 lv_reference25 := 'transaction_id';
1353 lv_debug := 'Y';
1354 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';
1355 lv_reference_10_desc2 := ' For the Transaction Type ';
1356
1357 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.expense_accounting', 'START'); /* 1 */
1358
1359 /* Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
1360 vat_noclaim added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1361 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1362 lv_account_nature := 'Cenvat Unclaim Expense';
1363 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1364 lv_account_nature := 'VAT Unclaim Expense';
1365 END IF;
1366
1367 if p_transaction_type='CORRECT' then
1368 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1369 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ||' of Type ' || p_parent_transaction_type;
1370 else
1371 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1372 lv_reference_10_desc := lv_reference_10_desc1 || p_receipt_num ||lv_reference_10_desc2 ||p_transaction_type ;
1373 end if;
1374
1375
1376 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1377 ln_expense_account := expense_account
1378 (
1379 p_transaction_id => p_transaction_id,
1380 p_organization_id => p_organization_id,
1381 p_subinventory_code => p_subinventory_code,
1382 p_po_distribution_id => p_po_distribution_id,
1383 p_po_line_location_id => p_po_line_location_id,
1384 p_item_id => p_inventory_item_id,
1385 p_process_message => p_process_message,
1386 p_process_status => p_process_status,
1387 p_codepath => p_codepath
1388 );
1389 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1390
1391 if p_process_status IN ('E', 'X') THEN
1392 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, NULL, 'END'); /* 5 */
1393 goto exit_from_procedure;
1394 end if;
1395
1396 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1397 p_transaction_type = 'DELIVER' then
1398
1399 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1400 ln_debit_amount := NULL;
1401 ln_credit_amount := p_accounted_amount;
1402
1403 ELSIF (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1404 p_transaction_type = 'RETURN TO RECEIVING' then
1405
1406 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1407 ln_debit_amount := p_accounted_amount;
1408 ln_credit_amount := NULL;
1409
1410 end if;
1411
1412 /* Inventory Receiving Account */
1413 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1414 jai_rcv_accounting_pkg.process_transaction
1415 (
1416 p_transaction_id => p_transaction_id,
1417 p_acct_type => p_accounting_type,
1418 p_acct_nature => lv_account_nature,
1419 p_source_name => lv_source,
1420 p_category_name => lv_category,
1421 p_code_combination_id => p_receiving_account_id,
1422 p_entered_dr => ln_debit_amount,
1423 p_entered_cr => ln_credit_amount,
1424 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1425 p_accounting_date => p_transaction_date,
1426 p_reference_10 => lv_reference_10_desc || p_receipt_num ||lv_reference_10_desc1 ||p_transaction_type, --Reference10
1427 p_reference_23 => lv_reference23,
1428 p_reference_24 => lv_reference24,
1429 p_reference_25 => lv_reference25,
1430 p_reference_26 => to_char(p_transaction_id),
1431 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
1432 p_simulate_flag => p_simulate,
1433 p_codepath => p_codepath,
1434 p_process_message => p_process_message,
1435 p_process_status => p_process_status
1436 );
1437 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1438
1439 if p_process_status in ('E', 'X') then
1440 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1441 goto exit_from_procedure;
1442 end if;
1443
1444 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type ='DELIVER') or
1445 p_transaction_type ='DELIVER' then
1446
1447 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1448 ln_debit_amount := p_accounted_amount;
1449 ln_credit_amount := NULL;
1450
1451 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1452 p_transaction_type = 'RETURN TO RECEIVING' then
1453
1454 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1455 ln_debit_amount := NULL;
1456 ln_credit_amount := p_accounted_amount;
1457 end if;
1458
1459 /* Expense Account */
1460 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
1461 jai_rcv_accounting_pkg.process_transaction
1462 (
1463 p_transaction_id => p_transaction_id,
1464 p_acct_type => p_accounting_type,
1465 p_acct_nature => lv_account_nature,
1466 p_source_name => lv_source,
1467 p_category_name => lv_category,
1468 p_code_combination_id => ln_expense_account,
1469 p_entered_dr => ln_debit_amount,
1470 p_entered_cr => ln_credit_amount,
1471 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1472 p_accounting_date => p_transaction_date,
1473 p_reference_10 => lv_reference_10_desc || p_receipt_num ||lv_reference_10_desc1 ||p_transaction_type, --Reference10
1474 p_reference_23 => lv_reference23,
1475 p_reference_24 => lv_reference24,
1476 p_reference_25 => lv_reference25,
1477 p_reference_26 => to_char(p_transaction_id),
1478 p_destination => 'G', /*Indicates that GL Interface needs to be hit */
1479 p_simulate_flag => p_simulate,
1480 p_codepath => p_codepath,
1481 p_process_message => p_process_message,
1482 p_process_status => p_process_status
1483 );
1484 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1485
1486 if p_process_status in ('E', 'X') then
1487 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
1488 goto exit_from_procedure;
1489 end if;
1490
1491 << exit_from_procedure >>
1492 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath, null, 'END'); /* 16 */
1493 return;
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 p_process_status := 'E';
1498 p_process_message := 'DELIVER_RTR_PKG.expense_accounting:' || sqlerrm ;
1499 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1500 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 17 */
1501
1502 END expense_accounting;
1503
1504 /* ---------------------------------------------start of average costing procedure --------------*/
1505 PROCEDURE average_costing
1506 (
1507 p_transaction_id IN NUMBER,
1508 p_transaction_date IN DATE,
1509 p_organization_id IN NUMBER,
1510 p_parent_transaction_type IN VARCHAR2,
1511 p_transaction_type IN VARCHAR2,
1512 p_subinventory_code IN VARCHAR2,
1513 p_costing_amount IN NUMBER,
1514 p_receiving_account_id IN NUMBER,
1515 p_source_document_code IN VARCHAR2,
1516 p_po_distribution_id IN NUMBER,
1517 p_unit_of_measure IN VARCHAR2,
1518 p_inventory_item_id IN NUMBER,
1519 p_accounting_type IN VARCHAR2,
1520 p_simulate IN VARCHAR2,
1521 p_process_message OUT NOCOPY VARCHAR2,
1522 p_process_status OUT NOCOPY VARCHAR2,
1523 p_codepath IN OUT NOCOPY VARCHAR2,
1524 p_process_special_source IN VARCHAR2
1525 ) is
1526
1527 ln_material_account_id mtl_secondary_inventories.material_account%type;
1528 ln_costing_amount number;
1529
1530 lv_source varchar2(30); --File.Sql.35 Cbabu := 'Inventory India';
1531 lv_category varchar2(30); --File.Sql.35 Cbabu := 'MTL';
1532 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
1533 lv_uom_code mtl_units_of_measure.unit_of_measure % TYPE;
1534 lv_account_nature varchar2(30); --File.Sql.35 Cbabu := 'Average Costing';
1535
1536 /*
1537 This Procedure is meant for Costing Entries in case of a Average costing Organization
1538 with the destination type being Inventory.
1539
1540 The Value change part is sent to MMTT and hence only 1 row is populated into MMTT
1541 with the Account being Inv. Receiving Always.
1542
1543 Transaction Type | Amount | Account
1544 =====================|===================|==========================================
1545 DELIVER | Costing | Inv.Receiving
1546 RETURN TO RECEIVING | -Costing | Inv.Receiving
1547 =====================|===================|==========================================
1548
1549 The Entry recorded in JAI_RCV_JOURNAL_ENTRIES is :
1550
1551 Transaction Type Amount Credit Debit
1552 |===================|=================|====================|=======================|
1553 |DELIVER | Costing | Inv. Receiving | |
1554 |DELIVER | Costing | | Material Account |
1555 ------------------------------------------------------------------------------------
1556 |RTR | -Costing | Inv. Receiving | |
1557 |RTR | -Costing | | Material Account |
1558 |===================|=================|====================|=======================|
1559 */
1560
1561 BEGIN
1562 --File.Sql.35 Cbabu
1563 lv_source := 'Inventory India';
1564 lv_category := 'MTL';
1565 lv_debug := 'Y';
1566 lv_account_nature := 'Average Costing';
1567
1568 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.average_costing', 'START'); /* 1 */
1569
1570 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1571 lv_account_nature := 'Unclaim Average Costing';
1572
1573 /* elsif added for vat_noclaim by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1574 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1575 lv_account_nature := 'VAT Unclaim Average Costing';
1576 END IF;
1577
1578 /* Fetch the Material Account Id */
1579 ln_material_account_id := material_account
1580 (
1581 p_organization_id => p_organization_id,
1582 p_source_document_code => p_source_document_code,
1583 p_po_distribution_id => p_po_distribution_id,
1584 p_subinventory => p_subinventory_code,
1585 p_process_message => p_process_message,
1586 p_process_status => p_process_status,
1587 p_codepath => p_codepath
1588 );
1589 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1590
1591 if p_process_status in ('E', 'X') then
1592 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1593 goto exit_from_procedure;
1594 end if;
1595
1596 lv_uom_code := jai_general_pkg.get_uom_code(p_uom => p_unit_of_measure);
1597
1598
1599 if lv_uom_code IS NULL THEN
1600
1601 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1602 lv_uom_code := jai_general_pkg.get_primary_uom_code
1603 (
1604 p_organization_id => p_organization_id,
1605 p_inventory_item_id => p_inventory_item_id
1606 );
1607 end if;
1608
1609 if lv_debug='Y' THEN
1610 fnd_file.put_line( fnd_file.log, ' 3.3 '|| ' p_unit_of_measure -> ' || p_unit_of_measure || ' lv_uom_code -> ' || lv_uom_code);
1611 end if;
1612
1613 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1614 p_transaction_type = 'DELIVER' then
1615
1616 ln_costing_amount := p_costing_amount;
1617 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1618
1619 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1620 p_transaction_type = 'RETURN TO RECEIVING' then
1621
1622 ln_costing_amount := -p_costing_amount;
1623 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1624
1625 end if;
1626
1627 /* Destination in this case is A1, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1628 and also MMTT would be updated */
1629
1630 /* Inventory Receiving Account */
1631 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1632 jai_rcv_accounting_pkg.process_transaction
1633 (
1634 p_transaction_id => p_transaction_id,
1635 p_acct_type => p_accounting_type,
1636 p_acct_nature => lv_account_nature,
1637 p_source_name => lv_source,
1638 p_category_name => lv_category,
1639 p_code_combination_id => p_receiving_account_id,
1640 p_entered_dr => NULL, /* This should never be changed to Zero */
1641 p_entered_cr => ln_costing_amount,
1642 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1643 p_accounting_date => p_transaction_date,
1644 p_reference_10 => NULL,
1645 p_reference_23 => NULL,
1646 p_reference_24 => NULL,
1647 p_reference_25 => NULL,
1648 p_reference_26 => NULL,
1649 p_destination => 'A1', /*Indicates Average Costing Entry */
1650 p_simulate_flag => p_simulate,
1651 p_codepath => p_codepath,
1652 p_process_message => p_process_message,
1653 p_process_status => p_process_status
1654 );
1655 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1656
1657 if p_process_status in ('E', 'X') then
1658 goto exit_from_procedure;
1659 end if;
1660
1661 /* Debit Material Account
1662 /* Destination in this case is A2, which indicates that the JAI_RCV_JOURNAL_ENTRIES would be hit
1663 and also MMTT would be updated */
1664
1665 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1666 jai_rcv_accounting_pkg.process_transaction
1667 (
1668 p_transaction_id => p_transaction_id,
1669 p_acct_type => p_accounting_type,
1670 p_acct_nature => lv_account_nature,
1671 p_source_name => lv_source,
1672 p_category_name => lv_category,
1673 p_code_combination_id => ln_material_account_id,
1674 p_entered_dr => ln_costing_amount,
1675 p_entered_cr => NULL, /* This should never be changed to Zero */
1676 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1677 p_accounting_date => p_transaction_date,
1678 p_reference_10 => NULL,
1679 p_reference_23 => NULL,
1680 p_reference_24 => NULL,
1681 p_reference_25 => NULL,
1682 p_reference_26 => NULL,
1683 p_destination => 'A2', /*Indicates Average Costing Entry */
1684 p_simulate_flag => p_simulate,
1685 p_codepath => p_codepath,
1686 p_process_message => p_process_message,
1687 p_process_status => p_process_status
1688 );
1689
1690 if p_process_status in ('E', 'X') then
1691 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1692 goto exit_from_procedure;
1693 end if;
1694
1695 << exit_from_procedure >>
1696 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath, null, 'END'); /* 11 */
1697 return;
1698
1699 exception
1700 WHEN OTHERS THEN
1701 p_process_status := 'E';
1702 p_process_message := 'DELIVER_RTR_PKG.average_costing:' || sqlerrm ;
1703 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1704 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 12 */
1705
1706 END average_costing;
1707
1708 /*----------------------------start of standard costing-------------------------------------------------------*/
1709
1710 PROCEDURE standard_costing
1711 (
1712 p_transaction_id IN NUMBER,
1713 p_transaction_date IN DATE,
1714 p_organization_id IN NUMBER,
1715 p_parent_transaction_type IN VARCHAR2,
1716 p_transaction_type IN VARCHAR2,
1717 p_costing_amount IN NUMBER,
1718 p_receiving_account_id IN NUMBER,
1719 p_accounting_type IN VARCHAR2,
1720 p_simulate IN VARCHAR2,
1721 p_process_message OUT NOCOPY VARCHAR2,
1722 p_process_status OUT NOCOPY VARCHAR2,
1723 p_codepath IN OUT NOCOPY VARCHAR2,
1724 p_process_special_source IN VARCHAR2
1725 ) is
1726
1727 /*
1728 This Procedure is meant for Costing Entries in case of a Standard costing Organization
1729
1730
1731 The Costing Amount is populated into MTA.
1732
1733 Transaction Type | Amount | Account
1734 =====================|===================|==========================================
1735 DELIVER | Negative Amount | Inv.Receiving
1736 DELIVER | Positive Amount | PPV Account
1737 -----------------------------------------------------------------------------------
1738 RETURN TO RECEIVING | Positive Amount | Inv.Receiving
1739 RETURN TO RECEIVING | Negative Amount | PPV Account
1740 =====================|===================|==========================================
1741
1742 The Entry recorded in JAI_RCV_JOURNAL_ENTRIES is :
1743
1744 Transaction Type Amount Credit Debit
1745 ===================|=================|====================|=======================|
1746 DELIVER | Costing Amount | Inv.Receiving | |
1747 DELIVER | Costing Amount | | PPV Account |
1748 -------------------------------------|--------------------|-----------------------|
1749 RTR | Costing Amount | | Inv.Receiving |
1750 RTR | Costing Amount | PPV Account | |
1751 ===================|=================|====================|=======================|
1752
1753 */
1754
1755 ln_ppv_account_id mtl_parameters.purchase_price_var_account%type;
1756 ln_credit_amount NUMBER;
1757 ln_debit_amount NUMBER;
1758
1759 lv_source VARCHAR2(30); --File.Sql.35 Cbabu := 'Inventory India';
1760 lv_category VARCHAR2(30); --File.Sql.35 Cbabu := 'MTL';
1761 lv_account_nature VARCHAR2(30); --File.Sql.35 Cbabu := 'Standard Costing';
1762 --lv_debug varchar2(1) := 'Y';
1763
1764 --lv_transaction_type JAI_RCV_TRANSACTIONS.transaction_type%TYPE;
1765 lv_reference_10_desc1 VARCHAR2(75);--rchandan for bug#4473022
1766 lv_reference_10_desc2 VARCHAR2(30); --rchandan for bug#4473022
1767 lv_reference_10_desc gl_interface.reference10%type;--rchandan for bug#4473022
1768
1769 BEGIN
1770
1771 lv_source := 'Inventory India';
1772 lv_category := 'MTL';
1773 lv_account_nature := 'Standard Costing';
1774 lv_reference_10_desc1 := 'India Local Receiving Entry for the Receipt Number ';--rchandan for bug#4473022
1775 lv_reference_10_desc2 := ' For the Transaction Type ';--rchandan for bug#4473022
1776
1777 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.standard_costing', 'START'); /* 1 */
1778
1779 -- Vijay Shankar for Bug#4068823. RECEIPTS DEPLUG
1780 IF p_process_special_source = jai_constants.cenvat_noclaim THEN
1781 lv_account_nature := 'Unclaim Standard Costing';
1782
1783 /* elsif added for vat_noclaim by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1784 ELSIF p_process_special_source = jai_constants.vat_noclaim THEN
1785 lv_account_nature := 'VAT Unclaim Standard Costing';
1786 END IF;
1787
1788 ln_ppv_account_id := ppv_account
1789 (
1790 p_organization_id => p_organization_id,
1791 p_process_message => p_process_message,
1792 p_process_status => p_process_status,
1793 p_codepath => p_codepath
1794 );
1795 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1796
1797 if p_process_status in ('E', 'X') then
1798 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1799 goto exit_from_procedure;
1800 end if;
1801
1802 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1803 p_transaction_type = 'DELIVER' then
1804
1805 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1806 ln_credit_amount := p_costing_amount;
1807 ln_debit_amount := NULL;
1808
1809 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1810 p_transaction_type = 'RETURN TO RECEIVING' then
1811
1812 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1813 ln_credit_amount := NULL;
1814 ln_debit_amount := p_costing_amount;
1815
1816 end if;
1817
1818 /* Receiving Inspection Account */
1819 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1820 jai_rcv_accounting_pkg.process_transaction
1821 (
1822 p_transaction_id => p_transaction_id,
1823 p_acct_type => p_accounting_type,
1824 p_acct_nature => lv_account_nature,
1825 p_source_name => lv_source,
1826 p_category_name => lv_category,
1827 p_code_combination_id => p_receiving_account_id,
1828 p_entered_dr => ln_debit_amount,
1829 p_entered_cr => ln_credit_amount,
1830 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1831 p_accounting_date => p_transaction_date,
1832 p_reference_10 => NULL,
1833 p_reference_23 => 'jai_rcv_deliver_rtr_pkg.standard_costing', --rchandan for bug#4473022
1834 p_reference_24 => 'rcv_transactions', --rchandan for bug#4473022
1835 p_reference_25 => NULL,
1836 p_reference_26 => to_char(p_transaction_id),
1837 p_destination => 'S', /*Indicates Standard Costing. */
1838 p_simulate_flag => p_simulate,
1839 p_codepath => p_codepath,
1840 p_process_message => p_process_message,
1841 p_process_status => p_process_status
1842 );
1843
1844 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1845 if p_process_status in ('E', 'X') then
1846 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1847 goto exit_from_procedure;
1848 end if;
1849
1850 if (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'DELIVER') or
1851 p_transaction_type = 'DELIVER' then
1852
1853 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1854 ln_credit_amount := NULL;
1855 ln_debit_amount := p_costing_amount;
1856
1857 elsif (p_transaction_type ='CORRECT' AND p_parent_transaction_type = 'RETURN TO RECEIVING') or
1858 p_transaction_type = 'RETURN TO RECEIVING' then
1859
1860 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1861 ln_credit_amount := p_costing_amount;
1862 ln_debit_amount := NULL;
1863
1864 end if;
1865
1866 /* PPV Account */
1867 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1868 jai_rcv_accounting_pkg.process_transaction
1869 (
1870 p_transaction_id => p_transaction_id,
1871 p_acct_type => p_accounting_type,
1872 p_acct_nature => lv_account_nature,
1873 p_source_name => lv_source,
1874 p_category_name => lv_category,
1875 p_code_combination_id => ln_ppv_account_id,
1876 p_entered_dr => ln_debit_amount,
1877 p_entered_cr => ln_credit_amount,
1878 p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
1879 p_accounting_date => p_transaction_date,
1880 p_reference_10 => NULL,
1881 p_reference_23 => 'jai_rcv_deliver_rtr_pkg.standard_costing', --rchandan for bug#4473022
1882 p_reference_24 => 'rcv_transactions', --rchandan for bug#4473022
1883 p_reference_25 => NULL,
1884 p_reference_26 => to_char(p_transaction_id),
1885 p_destination => 'S', /*Indicates Standard Costing. */
1886 p_simulate_flag => p_simulate,
1887 p_codepath => p_codepath,
1888 p_process_message => p_process_message,
1889 p_process_status => p_process_status
1890 );
1891
1892 if p_process_status in ('E', 'X') then
1893 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1894 goto exit_from_procedure;
1895 end if;
1896
1897 << exit_from_procedure >>
1898 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1899 return;
1900
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 p_process_status := 'E';
1904 p_process_message := 'DELIVER_RTR_PKG.standard_costing:' || sqlerrm ;
1905 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
1906 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 14 */
1907
1908 END standard_costing;
1909
1910
1911 /* ------------------------------------start of receiving_account-----------------------*/
1912
1913 FUNCTION receiving_account
1914 (
1915 p_organization_id IN NUMBER,
1916 p_process_message OUT NOCOPY VARCHAR2,
1917 p_process_status OUT NOCOPY VARCHAR2,
1918 p_codepath IN OUT NOCOPY VARCHAR2
1919 )
1920 return number is
1921
1922 CURSOR c_receiving_account(cp_organization_id number) IS
1923 SELECT receiving_account_id
1924 FROM rcv_parameters
1925 WHERE organization_id = cp_organization_id;
1926
1927 ln_receiving_account_id rcv_parameters.receiving_account_id%type;
1928 --lv_debug varchar2(1) := 'Y';
1929
1930 BEGIN
1931
1932 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.receiving_account', 'START'); /* 1 */
1933 open c_receiving_account(p_organization_id);
1934 fetch c_receiving_account into ln_receiving_account_id;
1935 close c_receiving_account;
1936
1937 if ln_receiving_account_id is null then
1938 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath, null, 'END'); /* 2 */
1939 p_process_status :='E';
1940 p_process_message :='Receiving Account Not Defined';
1941 RETURN null;
1942 -- raise no_receiving_account;
1943 else
1944 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath, null, 'END'); /* 3 */
1945 return(ln_receiving_account_id);
1946 end if;
1947
1948 EXCEPTION
1949 WHEN OTHERS THEN
1950 p_process_status := 'E';
1951 p_process_message := 'DELIVER_RTR_PKG.receiving_account:' || SQLERRM;
1952 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
1953 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 5 */
1954 return null;
1955
1956 END receiving_account;
1957
1958 /*----------------------------------------------------------------------*/
1959 FUNCTION material_account
1960 (
1961 p_organization_id IN NUMBER,
1962 p_source_document_code IN VARCHAR2,
1963 p_po_distribution_id IN NUMBER,
1964 p_subinventory IN VARCHAR2,
1965 p_process_message OUT NOCOPY VARCHAR2,
1966 p_process_status OUT NOCOPY VARCHAR2,
1967 p_codepath IN OUT NOCOPY VARCHAR2
1968 )
1969 RETURN NUMBER IS
1970
1971 CURSOR c_costing_group_id(cp_po_distribution_id number) is
1972 SELECT costing_group_id
1973 FROM pjm_project_parameters
1974 WHERE project_id in
1975 (select project_id
1976 from po_distributions_all
1977 where po_distribution_id =cp_po_distribution_id
1978 );
1979
1980 /* cursor to get the material account pertaining to the cost group passed */
1981 CURSOR c_material_account_cg(cp_cost_group_id number) is
1982 SELECT material_account
1983 FROM cst_cost_group_accounts
1984 WHERE cost_group_id = cp_cost_group_id;
1985
1986 /* cursor to get the material account */
1987 CURSOR c_material_account(cp_organization_id number , cp_subinventory varchar2) is
1988 SELECT material_account
1989 FROM mtl_secondary_inventories
1990 WHERE organization_id = cp_organization_id
1991 AND secondary_inventory_name = cp_subinventory;
1992
1993
1994 ln_material_account_id mtl_secondary_inventories.material_account%type;
1995 ln_costing_group_id pjm_project_parameters.costing_group_id%type;
1996
1997 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
1998
1999 BEGIN
2000
2001 lv_debug := 'Y';
2002 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.material_account', 'START'); /* 1 */
2003
2004 if p_source_document_code = 'PO' then
2005
2006 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2007 open c_costing_group_id(p_po_distribution_id);
2008 fetch c_costing_group_id into ln_costing_group_id;
2009 close c_costing_group_id;
2010
2011
2012 open c_material_account_cg(ln_costing_group_id);
2013 fetch c_material_account_cg into ln_material_account_id;
2014 close c_material_account_cg;
2015
2016 if lv_debug='Y' THEN
2017 fnd_file.put_line( fnd_file.log, '4_2.1 costing group' || ln_costing_group_id);
2018 fnd_file.put_line( fnd_file.log, '4_2.2 material acct of costing group' || ln_material_account_id);
2019 end if;
2020
2021 end if;
2022
2023 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2024 if ln_material_account_id is null then
2025
2026 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2027 open c_material_account(p_organization_id,p_subinventory);
2028 fetch c_material_account into ln_material_account_id;
2029 close c_material_account;
2030
2031 end if;
2032
2033 if ln_material_account_id is null then
2034 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, null, 'END'); /* 5 */
2035 p_process_status :='E';
2036 p_process_message :='Material Account Not Defined';
2037 return null;
2038 --raise no_material_account;
2039 else
2040 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath, null, 'END'); /* 6 */
2041 return(ln_material_account_id);
2042 end if;
2043
2044
2045 EXCEPTION
2046
2047 WHEN OTHERS THEN
2048 p_process_status :='E';
2049 p_process_message :='DELIVER_RTR_PKG.material_account:' || SQLERRM;
2050 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
2051 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 8 */
2052 return null;
2053
2054 END material_account ;
2055
2056 /*---------------------------------Start of Expense_Account----------------------------*/
2057 FUNCTION expense_account
2058 (
2059 p_transaction_id IN NUMBER,
2060 p_organization_id IN NUMBER,
2061 p_subinventory_code IN VARCHAR2,
2062 p_po_distribution_id IN NUMBER,
2063 p_po_line_location_id IN NUMBER,
2064 p_item_id IN NUMBER,
2065 p_process_message OUT NOCOPY VARCHAR2,
2066 p_process_status OUT NOCOPY VARCHAR2,
2067 p_codepath IN OUT NOCOPY VARCHAR2
2068 )
2069 RETURN NUMBER IS
2070
2071 ln_expense_account mtl_secondary_inventories.expense_account%type;
2072
2073 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
2074
2075 cursor c_fetch_expense_acct(cp_organization_id in number, cp_subinventory_code in varchar2) IS
2076 SELECT expense_account
2077 FROM mtl_secondary_inventories
2078 WHERE organization_id = cp_organization_id
2079 AND secondary_inventory_name = cp_subinventory_code;
2080
2081 cursor c_fetch_expense_acct1(cp_po_distribution_id in number) IS
2082 SELECT code_combination_id
2083 FROM po_distributions_all
2084 WHERE po_distribution_id = cp_po_distribution_id;
2085
2086 CURSOR c_fetch_expense_acct2(cp_po_line_location_id in number) IS
2087 SELECT code_combination_id
2088 FROM po_distributions_all
2089 WHERE line_location_id = cp_po_line_location_id
2090 AND creation_date IN
2091 (SELECT max(creation_date)
2092 FROM po_distributions_all
2093 WHERE line_location_id = cp_po_line_location_id
2094 );
2095
2096 CURSOR c_fetch_expense_acct3(cp_organization_id in number, cp_item_id in number) IS
2097 SELECT expense_account
2098 FROM mtl_system_items
2099 WHERE organization_id = cp_organization_id
2100 AND inventory_item_id = cp_item_id;
2101
2102 BEGIN
2103
2104 lv_debug := 'Y';
2105
2106 /* In case of a Expense Route Data is populated into rcv_receiving_sub_ledger
2107 and hence CCID can be picked up from here instead of looking into various
2108 other possibilities
2109 */
2110
2111 /* To be checked whether this can be done in a different way */
2112 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.expense_account', 'START'); /* 1 */
2113
2114 open c_fetch_expense_acct(p_organization_id, p_subinventory_code);
2115 fetch c_fetch_expense_acct into ln_expense_account;
2116 close c_fetch_expense_acct;
2117
2118 if ln_expense_account is null then
2119
2120 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2121 if p_po_distribution_id is not null then
2122
2123 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2124 open c_fetch_expense_acct1(p_po_distribution_id);
2125 fetch c_fetch_expense_acct1 into ln_expense_account;
2126 close c_fetch_expense_acct1;
2127
2128 elsif p_po_line_location_id is not null then
2129
2130 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2131 open c_fetch_expense_acct2(p_po_line_location_id);
2132 fetch c_fetch_expense_acct2 into ln_expense_account;
2133 close c_fetch_expense_acct2;
2134
2135 end if;
2136 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
2137
2138 end if; --end if for ln_expense_account.
2139
2140 if ln_expense_account is null then
2141
2142 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
2143 open c_fetch_expense_acct3(p_organization_id, p_item_id);
2144 fetch c_fetch_expense_acct3 into ln_expense_account;
2145 close c_fetch_expense_acct3;
2146
2147 end if;
2148
2149
2150 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
2151 if ln_expense_account is null then
2152 p_process_status := 'E';
2153 p_process_message := 'Expense Account is Not Found ';
2154 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath, null, 'END'); /* 8 */
2155 return null;
2156 end if;
2157
2158 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, null, 'END'); /* 9 */
2159
2160 return(ln_expense_account);
2161
2162
2163 EXCEPTION
2164 WHEN OTHERS THEN
2165 p_process_status := 'E';
2166 p_process_message := 'DELIVER_RTR_PKG.expense_account:' || SQLERRM;
2167 FND_FILE.put_line( FND_FILE.log, 'Error in '||p_process_message);
2168 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 10 */
2169 return null;
2170 END expense_account;
2171
2172 /*----------------------------------------------------------------------------------------*/
2173
2174 FUNCTION ppv_account
2175 (
2176 p_organization_id IN NUMBER,
2177 p_process_message OUT NOCOPY VARCHAR2,
2178 p_process_status OUT NOCOPY VARCHAR2,
2179 p_codepath IN OUT NOCOPY VARCHAR2
2180 )
2181 return number is
2182
2183 lv_debug varchar2(1); --File.Sql.35 Cbabu := 'Y';
2184 cursor c_ppv_account(cp_organization_id IN NUMBER) is
2185 SELECT purchase_price_var_account
2186 FROM mtl_parameters
2187 WHERE organization_id = cp_organization_id;
2188
2189 ln_ppv_account_id NUMBER;
2190
2191
2192 BEGIN
2193
2194 lv_debug := 'Y';
2195
2196 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.ppv_account', 'START'); /* 1 */
2197 open c_ppv_account(p_organization_id);
2198 fetch c_ppv_account into ln_ppv_account_id;
2199 close c_ppv_account;
2200
2201 if ln_ppv_account_id is null then
2202 p_process_status := 'E';
2203 p_process_message := 'The Purchase Price Variance Account is not found ' ;
2204 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath, null, 'END'); /* 2 */
2205 return null;
2206 ELSE
2207 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2208 return(ln_ppv_account_id);
2209 end if;
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 p_process_status := 'E';
2214 p_process_message := 'DELIVER_RTR_PKG.ppv_account:' || SQLERRM ;
2215 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
2216 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 4 */
2217 return null;
2218 END ppv_account;
2219
2220 /*----------------------------------------------------------------------------------------*/
2221 function include_cenvat_in_costing
2222 (
2223 p_transaction_id IN NUMBER,
2224 p_process_message OUT NOCOPY VARCHAR2,
2225 p_process_status OUT NOCOPY VARCHAR2,
2226 p_codepath IN OUT NOCOPY VARCHAR2
2227 )
2228 return varchar2 is
2229
2230 lv_ret_value VARCHAR2(30);
2231
2232 lv_destination_type varchar2(30);
2233 lv_transaction_type varchar2(30);
2234 lv_loc_subinv_type JAI_RCV_TRANSACTIONS.loc_subinv_type%type;
2235 lv_debug varchar2(1);--File.Sql.35 Cbabu := 'Y';
2236 lv_include_cenvat_in_costing varchar2(1);
2237
2238 ln_dlry_trx_id NUMBER;
2239
2240 CURSOR c_receipt_cenvat_dtl(cp_transaction_id IN NUMBER) IS
2241 SELECT nvl(unclaim_cenvat_flag, jai_constants.no) unclaim_cenvat_flag,
2242 nvl(non_bonded_delivery_flag, jai_constants.no) non_bonded_delivery_flag,
2243 nvl(cenvat_claimed_amt, 0) cenvat_claimed_amt
2244 FROM JAI_RCV_CENVAT_CLAIMS
2245 WHERE transaction_id = cp_transaction_id;
2246
2247 CURSOR c_trx(cp_transaction_id number) is
2248 SELECT *
2249 FROM JAI_RCV_TRANSACTIONS
2250 WHERE transaction_id = cp_transaction_id;
2251
2252 r_trx c_trx%ROWTYPE;
2253 r_dlry_trx c_trx%ROWTYPE;
2254 r_base_trx c_base_trx%ROWTYPE;
2255
2256 r_receipt_cenvat_dtl c_receipt_cenvat_dtl%ROWTYPE;
2257
2258 begin
2259
2260 lv_debug := 'Y';
2261 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing', 'START'); /* 1 */
2262
2263 OPEN c_trx(p_transaction_id);
2264 FETCH c_trx INTO r_trx;
2265 CLOSE c_trx;
2266
2267 OPEN c_receipt_cenvat_dtl(r_trx.tax_transaction_id);
2268 FETCH c_receipt_cenvat_dtl INTO r_receipt_cenvat_dtl;
2269 CLOSE c_receipt_cenvat_dtl;
2270
2271 IF r_receipt_cenvat_dtl.unclaim_cenvat_flag = 'Y' THEN
2272 p_codepath := jai_general_pkg.plot_codepath(1.1, p_codepath); /* 12 */
2273 lv_include_cenvat_in_costing :='Y';
2274 GOTO end_of_procedure;
2275
2276 /* following is incorporated as part of non bonded delivery functionaliy
2277 if the condition is satisfied, then it means receipt line is not claimed and a non bonded delivery is done
2278 In this case Cenvat has to be costed.
2279 If non bonded flag is set after Claim Cenvat is done, then we need to pass/reverse the rg entries that are passed during
2280 RECEIVE for whatever applicable transactions
2281 */
2282 ELSIF r_receipt_cenvat_dtl.cenvat_claimed_amt = 0
2283 AND r_receipt_cenvat_dtl.non_bonded_delivery_flag = 'Y'
2284 THEN
2285 p_codepath := jai_general_pkg.plot_codepath(1.2, p_codepath); /* 12 */
2286 lv_include_cenvat_in_costing :='Y';
2287 GOTO end_of_procedure;
2288
2289 END IF;
2290
2291 IF r_trx.transaction_type = 'CORRECT' THEN
2292 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
2293 lv_transaction_type := r_trx.parent_transaction_type;
2294 ELSE
2295 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2296 lv_transaction_type := r_trx.transaction_type;
2297 END IF;
2298
2299 IF lv_transaction_type NOT IN ( 'DELIVER', 'RETURN TO RECEIVING') THEN
2300 /* this procedure is not valid for the transaction being processed */
2301 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
2302 RETURN 'X';
2303 END IF;
2304
2305 IF lv_transaction_type = 'RETURN TO RECEIVING' THEN
2306
2307 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2308 ln_dlry_trx_id := jai_rcv_trx_processing_pkg.get_ancestor_id
2309 ( r_trx.transaction_id, r_trx.shipment_line_id, 'DELIVER');
2310
2311 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
2312 OPEN c_trx(ln_dlry_trx_id);
2313 FETCH c_trx INTO r_dlry_trx;
2314 CLOSE c_trx;
2315
2316 lv_destination_type := r_dlry_trx.destination_type_code;
2317 lv_loc_subinv_type := nvl(r_dlry_trx.loc_subinv_type, 'X');
2318
2319
2320 ELSE --DELIVER scenario.
2321
2322 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
2323 lv_destination_type := r_trx.destination_type_code;
2324 lv_loc_subinv_type := nvl(r_trx.loc_subinv_type, 'X');
2325
2326 END IF; --End if for RETURN TO RECEIVING
2327
2328 OPEN c_base_trx(p_transaction_id);
2329 FETCH c_base_trx INTO r_base_trx;
2330 CLOSE c_base_trx;
2331
2332 if r_trx.organization_type = 'M' THEN
2333
2334 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath);
2335 if r_trx.item_cenvatable = 'N' THEN
2336
2337 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath);
2338 lv_include_cenvat_in_costing :='Y';
2339
2340 elsif r_trx.item_class in ('OTIN','OTEX') then
2341
2342 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath);
2343 lv_include_cenvat_in_costing :='Y';
2344
2345 /* modified the following condition by vijay Shankar for Bug#4179823
2346 elsif r_base_trx.source_document_code <> 'REQ' and r_trx.item_class in ('FGIN', 'FGEX') then */
2347 elsif r_base_trx.source_document_code <> 'RMA' and r_trx.item_class in ('FGIN', 'FGEX')
2348 and r_trx.organization_type = 'M'
2349 then
2350 p_codepath := jai_general_pkg.plot_codepath(9.1, p_codepath);
2351 lv_include_cenvat_in_costing :='Y';
2352
2353 elsif lv_destination_type = 'INVENTORY' THEN
2354
2355 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
2356 if r_trx.base_asset_inventory = 2 then
2357
2358 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
2359 if r_trx.item_class not in ('CGIN','CGEX') then
2360
2361 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
2362 lv_include_cenvat_in_costing :='Y';
2363
2364 elsif r_trx.item_class in ('CGIN','CGEX') then
2365
2366 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
2367 if lv_loc_subinv_type IN ('X','N') then
2368 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
2369 lv_include_cenvat_in_costing :='Y';
2370 end if;
2371
2372 end if; --end if for r_trx.item_class
2373
2374 elsif r_trx.base_asset_inventory = 1 then
2375
2376 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
2377 if lv_loc_subinv_type IN ('X','N') then
2378 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
2379 lv_include_cenvat_in_costing :='Y';
2380 end if;
2381
2382 end if; --end if for r_trx.base_asset_inventory
2383
2384 elsif lv_destination_type = 'EXPENSE' THEN
2385
2386 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
2387 if r_trx.item_class not in ('CGIN','CGEX') then
2388 lv_include_cenvat_in_costing :='Y';
2389 end if;
2390 else
2391 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
2392 lv_include_cenvat_in_costing :='X';
2393 end if; --end if for r_trx.item_cenvatable='N'
2394
2395 elsif r_trx.organization_type = 'T' THEN
2396
2397 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
2398 if r_trx.item_trading_flag <> 'Y'
2399 or r_trx.item_excisable <> 'Y'
2400 or r_trx.excise_in_trading <> 'Y'
2401 then
2402 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
2403 lv_include_cenvat_in_costing :='Y';
2404 end if;
2405
2406 else
2407
2408 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
2409 lv_include_cenvat_in_costing :='X';
2410
2411 end if; --r_trx.organization_type = 'M'
2412
2413 <<end_of_procedure>>
2414
2415 if lv_include_cenvat_in_costing is null then
2416 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
2417 lv_include_cenvat_in_costing :='N';
2418 end if;
2419
2420 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath, null, 'END'); /* 23 */
2421 lv_ret_value := lv_include_cenvat_in_costing;
2422
2423 return lv_ret_value;
2424
2425 exception
2426 when others then
2427 p_process_status := 'E';
2428 p_process_message := 'DELIVER_RTR_PKG.include_cenvat_in_costing:' || SQLERRM ;
2429 fnd_file.put_line( fnd_file.log, 'Error in '||p_process_message);
2430 p_codepath := jai_general_pkg.plot_codepath(999, p_codepath, null, 'END'); /* 24 */
2431 return null;
2432 end include_cenvat_in_costing;
2433
2434 END jai_rcv_deliver_rtr_pkg;