[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_OPM_COSTING_PKG
Source
1 PACKAGE BODY jai_rcv_opm_costing_pkg AS
2 /* $Header: jai_rcv_opm_cst.plb 120.5.12010000.1 2008/11/21 04:58:09 mbremkum 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 ------------------------------------------------------------------------------------------------------------*/
17 ld_start_date DATE;
18 ld_end_date DATE;
19
20 lv_deliver_trx CONSTANT VARCHAR2(30) := 'DELIVER';
21 lv_correct_trx CONSTANT VARCHAR2(30) := 'CORRECT';
22 lv_rtr_trx CONSTANT VARCHAR2(30) := 'RETURN TO RECEIVING';
23
24 CURSOR cur_receipt_records(cp_start_date DATE, cp_end_date DATE)
25 IS
26 SELECT *
27 FROM rcv_shipment_lines rsl
28 WHERE ( p_organization_id IS NULL OR to_organization_id = p_organization_id )
29 /*even though to_organization_id is NULL ALLOWED, it is always populated*/
30 AND trunc(creation_date) between cp_start_date AND cp_end_date
31 AND to_organization_id IN ( SELECT jcio.organization_id
32 FROM jai_cmn_inventory_orgs jcio,
33 mtl_parameters mtl
34 WHERE mtl.organization_id = jcio.organization_id
35 AND mtl.process_enabled_flag = 'Y'
36 )
37 AND EXISTS ( SELECT 1
38 FROM jai_rcv_transactions
39 WHERE shipment_header_id = rsl.shipment_header_id
40 AND shipment_line_id = rsl.shipment_line_id
41 AND transaction_type = lv_deliver_trx
42 );
43
44 CURSOR cur_rcv_costing_records(cp_shipment_line_id IN NUMBER )
45 IS
46 SELECT *
47 FROM jai_rcv_transactions jrcv
48 WHERE shipment_line_id = cp_shipment_line_id
49 AND ( (transaction_type IN (lv_deliver_trx,lv_rtr_trx))
50 OR (transaction_type = lv_correct_trx AND
51 parent_transaction_type IN (lv_rtr_trx,lv_deliver_trx)) )
52 AND nvl(opm_costing_flag,'N') = 'N';
53
54 CURSOR cur_receipt_num(cp_shipment_line_id NUMBER)
55 IS
56 SELECT receipt_num
57 FROM jai_rcv_lines
58 WHERE shipment_line_id = cp_shipment_line_id;
59
60 CURSOR cur_orgn_name(cp_orgn_id NUMBER)
61 IS
62 SELECT name
63 FROM hr_all_organization_units
64 WHERE organization_id = cp_orgn_id;
65
66 CURSOR cur_sob_currency(cp_cob_id NUMBER)
67 IS
68 SELECT currency_code
69 FROM gl_sets_of_books
70 WHERE set_of_books_id = cp_cob_id ;
71
72 ln_modvat_amount NUMBER;
73 ln_non_modvat_amount NUMBER;
74 ln_other_modvat_amount NUMBER;
75 lv_process_message VARCHAR2(4000);
76 lv_process_status VARCHAR2(100);
77 lv_codepath VARCHAR2(4000);
78 ln_costing_amount NUMBER;
79 lv_include_cenvat_in_costing VARCHAR2(1);
80 ln_apportion_factor NUMBER;
81 lv_receipt_num VARCHAR2(30);
82 lv_orgn_name VARCHAR2(240);
83 ln_receipt_count NUMBER;
84 lv_receipt_processed VARCHAR2(1);
85 ln_sob_id NUMBER;
86 lv_sob_currency VARCHAR2(15);
87 BEGIN
88 retcode := 0;
89 ln_sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
90
91 OPEN cur_sob_currency(ln_sob_id);
92 FETCH cur_sob_currency INTO lv_sob_currency;
93 CLOSE cur_sob_currency;
94
95 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done( p_Owner => 'JA',
96 p_Event_Name => 'JAI_EXISTENCE_OF_TABLES' ) = FALSE )
97 OR lv_sob_currency <> 'INR' THEN
98
99 fnd_file.put_line(fnd_file.output,'This report is for Financials for India Customers only' );
100 return;
101
102 END IF;
103
104
105 ld_start_date := fnd_date.canonical_to_date(p_start_date);
106 ld_end_date := fnd_date.canonical_to_date(p_end_date);
107
108 IF p_organization_id IS NOT NULL THEN
109
110 OPEN cur_orgn_name(p_organization_id);
111 FETCH cur_orgn_name INTO lv_orgn_name;
112 CLOSE cur_orgn_name;
113
114 END IF;
115
116 fnd_file.put_line(fnd_file.output,'Concurrent Program Name: India - Program to Update OPM Cost' );
117 fnd_file.new_line(fnd_file.output,1);
118 fnd_file.put_line(fnd_file.output,'Parameters :' );
119 fnd_file.new_line(fnd_file.output,1);
120 fnd_file.put_line(fnd_file.output,' Organization Name :'||lv_orgn_name );
121 fnd_file.put_line(fnd_file.output,' Receipt Start Date :'||ld_start_date );
122 fnd_file.put_line(fnd_file.output,' Receipt End Date :'||ld_end_date );
123
124 ln_receipt_count := 0;
125
126 fnd_file.new_line(fnd_file.output,6);
127
128 fnd_file.put_line(fnd_file.output,'**********************************OPM Cost Update Begins**********************************' );
129
130 FOR receipt_rec IN cur_receipt_records(ld_start_date,ld_end_date) LOOP
131
132 fnd_file.new_line(fnd_file.output,2);
133
134 OPEN cur_receipt_num(receipt_rec.shipment_line_id);
135 FETCH cur_receipt_num INTO lv_receipt_num;
136 CLOSE cur_receipt_num;
137
138 IF p_organization_id IS NULL THEN
139
140 OPEN cur_orgn_name(receipt_rec.to_organization_id);
141 FETCH cur_orgn_name INTO lv_orgn_name;
142 CLOSE cur_orgn_name;
143
144 END IF;
145
146 lv_receipt_processed := 'N';
147
148 fnd_file.new_line(fnd_file.output,2);
149 fnd_file.put_line(fnd_file.output,'Receipt Number :' ||lv_receipt_num );
150 fnd_file.put_line(fnd_file.output,'Organization Name :' ||lv_orgn_name );
151
152
153 FOR rcv_costing_records IN cur_rcv_costing_records(receipt_rec.shipment_line_id) LOOP
154
155 jai_rcv_deliver_rtr_pkg.get_tax_amount_breakup
156 (
157 p_shipment_line_id => receipt_rec.shipment_line_id,
158 p_transaction_id => rcv_costing_records.transaction_id,
159 p_curr_conv_rate => rcv_costing_records.currency_conversion_rate,
160 p_excise_amount => ln_modvat_amount,
161 p_non_modvat_amount => ln_non_modvat_amount ,
162 p_other_modvat_amount => ln_other_modvat_amount ,
163 p_process_message => lv_process_message,
164 p_process_status => lv_process_status,
165 p_codepath => lv_codepath
166 );
167
168 if lv_process_status in ('E', 'X') THEN
169 raise_application_error(-20120,'Error while fetching the costing amount :'||lv_process_message);
170 end if;
171
172 lv_include_cenvat_in_costing := jai_rcv_deliver_rtr_pkg.include_cenvat_in_costing
173 (
174 p_transaction_id => rcv_costing_records.transaction_id,
175 p_process_message => lv_process_message,
176 p_process_status => lv_process_status,
177 p_codepath => lv_codepath
178 );
179
180 if lv_process_status in ('E', 'X') THEN
181 raise_application_error(-20120,'Error while fetching include cenvat in costing flag:'||lv_process_message);
182 end if;
183
184 if lv_include_cenvat_in_costing ='Y' then
185 ln_costing_amount := nvl(ln_non_modvat_amount,0) + nvl(ln_modvat_amount,0);
186 else
187 ln_costing_amount := nvl(ln_non_modvat_amount,0);
188 end if;
189
190 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor
191 ( p_transaction_id => rcv_costing_records.transaction_id);
192 --Added by JMEENA for bug#7268999
193 fnd_file.put_line(fnd_file.output,'ln_costing_amount before conversion:'||ln_costing_amount );
194 ln_costing_amount:= ln_costing_amount/NVL(rcv_costing_records.currency_conversion_rate,1);
195 fnd_file.put_line(fnd_file.output,'ln_costing_amount After conversion:'||ln_costing_amount );
196 --End of bug#7268999
197
198 ln_costing_amount := round((ln_costing_amount * ln_apportion_factor)/ABS(rcv_costing_records.quantity),5);
199
200 IF ln_costing_amount IS NOT NULL THEN
201
202 UPDATE rcv_transactions
203 SET po_unit_price = po_unit_price + ln_costing_amount
204 WHERE transaction_id = rcv_costing_records.transaction_id;
205
206 UPDATE mtl_material_transactions
207 SET transaction_cost = transaction_cost + ln_costing_amount
208 WHERE rcv_transaction_id = rcv_costing_records.transaction_id;
209
210 UPDATE jai_rcv_transactions
211 SET opm_costing_flag = 'Y',
212 opm_costing_amount = ln_costing_amount
213 WHERE transaction_id = rcv_costing_records.transaction_id;
214
215 END IF;
216
217 lv_receipt_processed := 'Y';
218 fnd_file.new_line(fnd_file.output,1);
219 fnd_file.put_line(fnd_file.output,'Transaction Type :' ||rcv_costing_records.transaction_type );
220 fnd_file.put_line(fnd_file.output,'Transaction Quantity :' ||rcv_costing_records.quantity );
221 fnd_file.put_line(fnd_file.output,'Non recoverable taxes per unit:' ||ln_costing_amount );
222 fnd_file.put_line(fnd_file.output,'Cost Update Successful' );
223 END LOOP;
224
225 IF lv_receipt_processed = 'N' THEN
226
227 fnd_file.new_line(fnd_file.output,1);
228 fnd_file.put_line(fnd_file.output,'The Receipt was already Cost updated');
229
230 END IF;
231
232 ln_receipt_count := ln_receipt_count + 1;
233
234 END LOOP;
235 fnd_file.new_line(fnd_file.output,2);
236 fnd_file.put_line(fnd_file.output,'Total number of receipts processed :' ||ln_receipt_count);
237 fnd_file.new_line(fnd_file.output,2);
238
239 fnd_file.put_line(fnd_file.output,'**********************************OPM Cost Update Ends**********************************' );
240 COMMIT;
241 EXCEPTION
242 WHEN OTHERS THEN
243 retcode := 2;
244 errbuf := SUBSTR(SQLERRM,1,200);
245 fnd_file.put_line(fnd_file.log,'Error while processing receipt :'||lv_receipt_num);
246 ROLLBACK;
247 END opm_cost_adjust;
248
249 END jai_rcv_opm_costing_pkg;