1 PACKAGE BODY jai_rcv_opm_costing_pkg AS
2 /* $Header: jai_rcv_opm_cst.plb 120.17 2011/07/13 05:32:34 nprashar noship $ */
3 PROCEDURE opm_cost_adjust( errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY NUMBER,
5 p_organization_id IN NUMBER ,
6 p_start_date IN VARCHAR2 ,
7 p_end_date IN VARCHAR2 )
8 IS
9 /*------------------------------------------------------------------------------------------------------------
10 CHANGE HISTORY
11 ------------------------------------------------------------------------------------------------------------
12 Sl.No. Date Developer BugNo Remarks
13 ------------------------------------------------------------------------------------------------------------
14 1. 21-JUL-2008 JMEENA 7268999 Added the code to convert the ln_costing_amount to base currency before updating the tables.
15
16 2. 23-MAR-2009 VUMAASHA 7655123 In mtl_material_transactions the cost is always recorded in functional currency.
17 The Variable ln_costing_amt_func_curr is added for store the costing
18 amount in functional currency.
19 3. 26-May-2009 Bug 8505362 File version 120.0.12000000.10 / 120.5.12010000.4 / 120.8
20 Issue - Costing update for non-recoverable taxes is wrong when transaction UOM is
21 different from primary UOM.
22 Cause - In MMT table, the cost is to be updated in terms of rate / quantity in primary UOM. But
23 the code updates it with rate / quantity in transaction UOM.
24 Fix - When calculating the amount to update the MMT table, used rcv_transactions.primary_quantity
25 instead of jai_rcv_transactions.quantity.
26
27 4. 01-OCT-2009 Bug 8966461 File version 120.0.12000000.11 / 120.5.12010000.5 / 120.9
28 Issue - Costing update is wrong for CORRECT transactions with negative
29 quantity.
30 Fix - Divided the costing amount variables by quantity instead of
31 ABS(quantity). As the quantity is negative, costing amount should
32 be positive for the accouting to be correct. For negative quantity,
33 apportion factor will also be negative, and needs to be divided by
34 value with same sign.
35
36 5. 14-Oct-2009 Bug 8894858 File version 120.0.12000000.12 / 120.5.12010000.6 / 120.10
37 Issue - Costing update is wrong when the uom for RECEIVE transaction is
38 is different from the source document (PO) uom.
39 Fix - Used rcv_transactions.source_doc_quantity instead of rcv_transactions.quantity
40 to calculate the costing amount to be added to po_unit_price.
41 Also incorporated the changes for bug 8830292 - the program has to pick
42 the transactions based on the transaction date, not the date of receipt.
43
44 6. 2-Sep-2010 Bug 9896814 Issue:INDIA LOCAL TAX IMPACT IS INCORRECT ON ACTUAL COST OF THE ITEM IN CASE OF RETURN
45 Fix Details:Removed lv_rtr_trx from the IN list of jrt.transaction_type in cursor cur_rcv_costing_records.
46 vkaranam for bug#12329209
47 issue: TAX ACCOUNTING AT DELIVERY HITTING EXPENSE A/C AS WELL AS INVENTORY VALUATION
48 fix : added the below condition in cursor cur_rcv_costing_records
49 /*
50 AND NOT (jrt.destination_type_code = 'EXPENSE' or jrt.INV_ASSET_FLAG='N' OR
51 JRT.INV_ITEM_FLAG='N' OR JRT.BASE_aSSET_INVENTORY=2) */
52
53 ------------------------------------------------------------------------------------------------------------*/
54 ld_start_date DATE;
55 ld_end_date DATE;
56
60
57 lv_deliver_trx CONSTANT VARCHAR2(30) := 'DELIVER';
58 lv_correct_trx CONSTANT VARCHAR2(30) := 'CORRECT';
59 lv_rtr_trx CONSTANT VARCHAR2(30) := 'RETURN TO RECEIVING';
61 CURSOR cur_receipt_records(cp_start_date DATE, cp_end_date DATE)
62 IS
63 SELECT *
64 FROM rcv_shipment_lines rsl
65 WHERE ( p_organization_id IS NULL OR to_organization_id = p_organization_id )
66 /*even though to_organization_id is NULL ALLOWED, it is always populated*/
67 --AND trunc(creation_date) between cp_start_date AND cp_end_date /*bug 8830292*/
68 AND to_organization_id IN ( SELECT jcio.organization_id
69 FROM jai_cmn_inventory_orgs jcio,
70 mtl_parameters mtl
71 WHERE mtl.organization_id = jcio.organization_id
72 AND mtl.process_enabled_flag = 'Y'
73 )
74 AND EXISTS ( SELECT 1
75 FROM jai_rcv_transactions jrt
76 WHERE jrt.shipment_header_id = rsl.shipment_header_id
77 AND jrt.shipment_line_id = rsl.shipment_line_id
78 AND jrt.transaction_type = lv_deliver_trx
79 AND trunc(jrt.transaction_date) between cp_start_date and cp_end_date /*bug 8830292*/
80 AND EXISTS ( Select 1 from MTL_SYSTEM_ITEMS_B msib where msib.inventory_item_id = jrt.inventory_item_id
81 and msib.process_costing_enabled_flag ='Y') --Added by nprashar for bug # 10272657
82 );
83
84 /*cursor modified for bug 8505362, to include the quanity in primary UOM*/
85 CURSOR cur_rcv_costing_records(cp_shipment_line_id IN NUMBER )
86 IS
87 SELECT rt.transaction_id, rt.quantity, rt.primary_quantity, jrt.currency_conversion_rate, jrt.transaction_type, rt.source_doc_quantity /*bug 8894858*/
88 FROM rcv_transactions rt, jai_rcv_transactions jrt
89 WHERE rt.transaction_id = jrt.transaction_id
90 AND jrt.shipment_line_id = cp_shipment_line_id
91 AND ( (jrt.transaction_type IN (lv_rtr_trx ,lv_deliver_trx)) /*Reverted the chnage performed by bug # 9896814 through bug #12738762*/
92 OR (jrt.transaction_type = lv_correct_trx AND
93 jrt.parent_transaction_type IN (lv_rtr_trx,lv_deliver_trx)) )
94 AND NOT (jrt.destination_type_code = 'EXPENSE' or jrt.INV_ASSET_FLAG='N' OR JRT.INV_ITEM_FLAG='N' OR JRT.BASE_aSSET_INVENTORY=2) /*added for bug#12329209*/
95 AND nvl(jrt.opm_costing_flag,'N') = 'N';
96
97 CURSOR cur_receipt_num(cp_shipment_line_id NUMBER)
98 IS
99 SELECT receipt_num
100 FROM jai_rcv_lines
101 WHERE shipment_line_id = cp_shipment_line_id;
102
103 CURSOR cur_orgn_name(cp_orgn_id NUMBER)
104 IS
105 SELECT name
106 FROM hr_all_organization_units
107 WHERE organization_id = cp_orgn_id;
108
109 CURSOR cur_sob_currency(cp_cob_id NUMBER)
110 IS
111 SELECT currency_code
112 FROM gl_sets_of_books
113 WHERE set_of_books_id = cp_cob_id ;
114
115 ln_modvat_amount NUMBER;
116 ln_non_modvat_amount NUMBER;
117 ln_other_modvat_amount NUMBER;
118 lv_process_message VARCHAR2(4000);
119 lv_process_status VARCHAR2(100);
120 lv_codepath VARCHAR2(4000);
121 ln_costing_amount NUMBER;
122 ln_costing_amt_func_curr NUMBER; /* Added for the bug 7655123 */
123 lv_include_cenvat_in_costing VARCHAR2(1);
124 ln_apportion_factor NUMBER;
125 lv_receipt_num VARCHAR2(30);
126 lv_orgn_name VARCHAR2(240);
127 ln_receipt_count NUMBER;
128 lv_receipt_processed VARCHAR2(1);
129 ln_sob_id NUMBER;
130 lv_sob_currency VARCHAR2(15);
131
132 BEGIN
133 retcode := 0;
134 ln_sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
135
136 OPEN cur_sob_currency(ln_sob_id);
137 FETCH cur_sob_currency INTO lv_sob_currency;
138 CLOSE cur_sob_currency;
139
140 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done( p_Owner => 'JA',
141 p_Event_Name => 'JAI_EXISTENCE_OF_TABLES' ) = FALSE )
142 OR lv_sob_currency <> 'INR' THEN
143
144 fnd_file.put_line(fnd_file.output,'This report is for Financials for India Customers only' );
145 return;
146
147 END IF;
148
149
150 ld_start_date := fnd_date.canonical_to_date(p_start_date);
151 ld_end_date := fnd_date.canonical_to_date(p_end_date);
152
153 IF p_organization_id IS NOT NULL THEN
154
155 OPEN cur_orgn_name(p_organization_id);
156 FETCH cur_orgn_name INTO lv_orgn_name;
157 CLOSE cur_orgn_name;
158
159 END IF;
160
161 fnd_file.put_line(fnd_file.output,'Concurrent Program Name: India - Program to Update OPM Cost' );
162 fnd_file.new_line(fnd_file.output,1);
163 fnd_file.put_line(fnd_file.output,'Parameters :' );
164 fnd_file.new_line(fnd_file.output,1);
165 fnd_file.put_line(fnd_file.output,' Organization Name :'||lv_orgn_name );
166 fnd_file.put_line(fnd_file.output,' Receipt Start Date :'||ld_start_date );
167 fnd_file.put_line(fnd_file.output,' Receipt End Date :'||ld_end_date );
168
169 ln_receipt_count := 0;
170
171 fnd_file.new_line(fnd_file.output,6);
172
173 fnd_file.put_line(fnd_file.output,'**********************************OPM Cost Update Begins**********************************' );
174
175 FOR receipt_rec IN cur_receipt_records(ld_start_date,ld_end_date) LOOP
176
177 fnd_file.new_line(fnd_file.output,2);
178
179 OPEN cur_receipt_num(receipt_rec.shipment_line_id);
180 FETCH cur_receipt_num INTO lv_receipt_num;
181 CLOSE cur_receipt_num;
182
186 FETCH cur_orgn_name INTO lv_orgn_name;
183 IF p_organization_id IS NULL THEN
184
185 OPEN cur_orgn_name(receipt_rec.to_organization_id);
187 CLOSE cur_orgn_name;
188
189 END IF;
190
191 lv_receipt_processed := 'N';
192
193 fnd_file.new_line(fnd_file.output,2);
194 fnd_file.put_line(fnd_file.output,'Receipt Number :' ||lv_receipt_num );
195 fnd_file.put_line(fnd_file.output,'Organization Name :' ||lv_orgn_name );
196
197
198 FOR rcv_costing_records IN cur_rcv_costing_records(receipt_rec.shipment_line_id) LOOP
199
200 jai_rcv_deliver_rtr_pkg.get_tax_amount_breakup
201 (
202 p_shipment_line_id => receipt_rec.shipment_line_id,
203 p_transaction_id => rcv_costing_records.transaction_id,
204 p_curr_conv_rate => rcv_costing_records.currency_conversion_rate,
205 p_excise_amount => ln_modvat_amount,
206 p_non_modvat_amount => ln_non_modvat_amount ,
207 p_other_modvat_amount => ln_other_modvat_amount ,
208 p_process_message => lv_process_message,
209 p_process_status => lv_process_status,
210 p_codepath => lv_codepath
211 );
212
213 if lv_process_status in ('E', 'X') THEN
214 raise_application_error(-20120,'Error while fetching the costing amount :'||lv_process_message);
215 end if;
216
217 lv_include_cenvat_in_costing := jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing
218 (
219 p_transaction_id => rcv_costing_records.transaction_id,
220 p_process_message => lv_process_message,
221 p_process_status => lv_process_status,
222 p_codepath => lv_codepath
223 );
224
225 if lv_process_status in ('E', 'X') THEN
226 raise_application_error(-20120,'Error while fetching include cenvat in costing flag:'||lv_process_message);
227 end if;
228
229 if lv_include_cenvat_in_costing ='Y' then
230 ln_costing_amount := nvl(ln_non_modvat_amount,0) + nvl(ln_modvat_amount,0);
231 else
232 ln_costing_amount := nvl(ln_non_modvat_amount,0);
233 end if;
234 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
235 ( p_transaction_id => rcv_costing_records.transaction_id);
236
237
238
239 /* added by VUMAASHA for bug 7655123 */
240 ln_costing_amt_func_curr := ln_costing_amount;
241 /*bug 8505362 - costing amount for func. currency should be calculated per unit in the primary UOM*/
242 /*ABS removed for bug 8966461*/
243 ln_costing_amt_func_curr := round((ln_costing_amt_func_curr * ln_apportion_factor)/rcv_costing_records.primary_quantity,5);
244 /* end for bug 7655123 */
245
246 --Added by JMEENA for bug#7268999
247 fnd_file.put_line(fnd_file.output,'ln_costing_amount before conversion:'||ln_costing_amount );
248 ln_costing_amount:= ln_costing_amount/NVL(rcv_costing_records.currency_conversion_rate,1);
249 fnd_file.put_line(fnd_file.output,'ln_costing_amount After conversion:'||ln_costing_amount );
250 --End of bug#7268999
251
252 /*bug 8966461 - negative quantities will lead to apportion factor becoming negative.
253 * Therefore, if we divide by ABS(quantity) then costing amount will be negative.
254 * Since actual accounting will be passed using quantity*cost, the costing
255 * amount should be positive, as long as the tax amount is positive.*/
256
257 ln_costing_amount := round((ln_costing_amount * ln_apportion_factor)/(Case when nvl(rcv_costing_records.source_doc_quantity,0) = 0 Then rcv_costing_records.quantity else rcv_costing_records.source_doc_quantity end ),5); /*bug 8894858*/
258 /*Used quantity column instead of source_doc_quantity for bug # 9475031*/
259
260 IF ln_costing_amount IS NOT NULL THEN
261
262 If rcv_costing_records.transaction_type = 'DELIVER' then /*Added by nprashar for bug # 12738762 */
263 UPDATE rcv_transactions
264 SET po_unit_price = po_unit_price + ln_costing_amount
265 WHERE transaction_id = rcv_costing_records.transaction_id;
266 End If;
267
268 UPDATE mtl_material_transactions
269 SET transaction_cost = transaction_cost + ln_costing_amt_func_curr /* Modified for bug 7655123 */
270 WHERE rcv_transaction_id = rcv_costing_records.transaction_id;
271
272 UPDATE jai_rcv_transactions
273 SET opm_costing_flag = 'Y',
274 opm_costing_amount = ln_costing_amount
275 WHERE transaction_id = rcv_costing_records.transaction_id;
276
277 END IF;
278
279 lv_receipt_processed := 'Y';
280 fnd_file.new_line(fnd_file.output,1);
281 fnd_file.put_line(fnd_file.output,'Transaction Type :' ||rcv_costing_records.transaction_type );
282 fnd_file.put_line(fnd_file.output,'Transaction Quantity :' ||rcv_costing_records.quantity );
283 fnd_file.put_line(fnd_file.output,'Non recoverable taxes per unit:' ||ln_costing_amount );
284 fnd_file.put_line(fnd_file.output,'Cost Update Successful' );
285 END LOOP;
286
287 IF lv_receipt_processed = 'N' THEN
288
289 fnd_file.new_line(fnd_file.output,1);
290 fnd_file.put_line(fnd_file.output,'The Receipt was already Cost updated');
291
292 END IF;
293
294 ln_receipt_count := ln_receipt_count + 1;
295
296 END LOOP;
297 fnd_file.new_line(fnd_file.output,2);
298 fnd_file.put_line(fnd_file.output,'Total number of receipts processed :' ||ln_receipt_count);
299 fnd_file.new_line(fnd_file.output,2);
300
301 fnd_file.put_line(fnd_file.output,'**********************************OPM Cost Update Ends**********************************' );
302 COMMIT;
303 EXCEPTION
304 WHEN OTHERS THEN
305 retcode := 2;
306 errbuf := SUBSTR(SQLERRM,1,200);
307 fnd_file.put_line(fnd_file.log,'Error while processing receipt :'||lv_receipt_num);
308 ROLLBACK;
309 END opm_cost_adjust;
310
311 END jai_rcv_opm_costing_pkg;
[Home] [Help]