[Home] [Help]
PACKAGE BODY: APPS.OZF_REFRESH_SALES_PVT
Source
1 PACKAGE BODY ozf_refresh_sales_pvt AS
2 /*$Header: ozfvrfsb.pls 120.2 2006/08/04 08:57:35 mgudivak noship $*/
3
4 Function get_primary_uom(p_id in number)
5 return varchar2
6 is
7 l_uom varchar2(30);
8 begin
9 select primary_uom_code into l_uom
10 from mtl_system_items
11 where inventory_item_id = p_id
12 and rownum =1;
13 return l_uom;
14 EXCEPTION
15 when others then
16 return ('LLLPP');
17 END get_primary_uom;
18
19 Function get_party_id(p_id in number)
20 return number
21 is
22 l_party_id number;
23 begin
24 SELECT max(a.party_id) into l_party_id
25 FROM hz_cust_accounts a
26 WHERE a.cust_account_id = p_id;
27 return l_party_id;
28 EXCEPTION
29 when others then
30 return (-99999);
31 END get_party_id;
32
33
34 Function get_party_site_id(p_id in number)
35 return number
36 is
37 l_party_site_id number;
38 begin
39 SELECT max(a.party_site_id) into l_party_site_id
40 FROM hz_cust_acct_sites_all a,
41 hz_cust_site_uses_all b
42 WHERE b.site_use_id = p_id
43 AND b.cust_acct_site_id = a.cust_acct_site_id;
44 return l_party_site_id;
45 EXCEPTION
46 when others then
47 return (-99999);
48 END get_party_site_id;
49
50 PROCEDURE full_load( x_return_status OUT NOCOPY VARCHAR2 ) AS
51
52 l_api_name CONSTANT VARCHAR2(30) := 'full_load';
53 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
54 l_return_status VARCHAR2(1);
55
56 l_global_start_date DATE := TO_DATE(FND_PROFILE.VALUE('OZF_TP_GLOBAL_START_DATE'), 'MM/DD/YYYY');
57 l_common_uom VARCHAR2(30) := FND_PROFILE.VALUE('OZF_TP_COMMON_UOM');
58 l_common_currency_code VARCHAR2(30) := FND_PROFILE.VALUE('OZF_TP_COMMON_CURRENCY');
59 l_curr_conv_type VARCHAR2(30) := FND_PROFILE.VALUE('OZF_CURR_CONVERSION_TYPE');
60
61 l_profile_option_name VARCHAR2(80);
62 l_user_profile_option_name VARCHAR2(240);
63
64 CURSOR prf_name_csr IS
65 SELECT user_profile_option_name
66 FROM fnd_profile_options_vl
67 WHERE profile_option_name = l_profile_option_name;
68
69 CURSOR error_csr IS
70 SELECT distinct
71 transaction_date,
72 uom_code,
73 currency_code,
74 common_uom_code,
75 common_currency_code,
76 common_quantity,
77 common_amount
78 FROM ozf_sales_transactions_all
79 WHERE error_flag = 'Y'
80 AND SOURCE_CODE = 'OM';
81
82 l_mesg VARCHAR2(2000);
83 l_prof_check VARCHAR2(1) := 'T';
84
85 BEGIN
86
87 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
88
89 SAVEPOINT full_load;
90
91 x_return_status := FND_API.g_ret_sts_success;
92
93 -- Check for Profile Values
94
95 ozf_utility_pvt.write_conc_log('-- l_global_start_date is : ' || l_global_start_date ) ;
96 ozf_utility_pvt.write_conc_log('-- l_common_uom is : ' || l_common_uom ) ;
97 ozf_utility_pvt.write_conc_log('-- l_common_currency_code is : ' || l_common_currency_code ) ;
98 ozf_utility_pvt.write_conc_log('-- l_curr_conv_type is : ' || l_curr_conv_type ) ;
99
100 IF l_global_start_date IS NULL
101 THEN
102 l_prof_check := 'N';
103 l_profile_option_name := 'OZF_TP_GLOBAL_START_DATE';
104
105 OPEN prf_name_csr;
106 FETCH prf_name_csr INTO l_user_profile_option_name;
107 CLOSE prf_name_csr;
108
109 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_PROFILE_VALUE');
110 FND_MESSAGE.Set_Token('PROFILE_NAME', l_user_profile_option_name);
111 l_mesg := FND_MESSAGE.get;
112
113 ozf_utility_pvt.write_conc_log(l_mesg);
114 END IF;
115
116
117 IF l_common_uom IS NULL
118 THEN
119 l_prof_check := 'N';
120 l_profile_option_name := 'OZF_TP_COMMON_UOM';
121
122 OPEN prf_name_csr;
123 FETCH prf_name_csr INTO l_user_profile_option_name;
124 CLOSE prf_name_csr;
125
126 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_PROFILE_VALUE');
127 FND_MESSAGE.Set_Token('PROFILE_NAME', l_user_profile_option_name);
128 l_mesg := FND_MESSAGE.get;
129
130 ozf_utility_pvt.write_conc_log(l_mesg);
131 END IF;
132
133 IF l_common_currency_code IS NULL
134 THEN
135 l_prof_check := 'N';
136 l_profile_option_name := 'OZF_TP_COMMON_CURRENCY';
137
138 OPEN prf_name_csr;
139 FETCH prf_name_csr INTO l_user_profile_option_name;
140 CLOSE prf_name_csr;
141
142 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_PROFILE_VALUE');
143 FND_MESSAGE.Set_Token('PROFILE_NAME', l_user_profile_option_name);
144 l_mesg := FND_MESSAGE.Get;
145
146 ozf_utility_pvt.write_conc_log(l_mesg);
147 END IF;
148
149 IF l_curr_conv_type IS NULL
150 THEN
151 l_prof_check := 'N';
152 l_profile_option_name := 'OZF_CURR_CONVERSION_TYPE';
153
154 OPEN prf_name_csr;
155 FETCH prf_name_csr INTO l_user_profile_option_name;
156 CLOSE prf_name_csr;
157
158 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_PROFILE_VALUE');
159 FND_MESSAGE.Set_Token('PROFILE_NAME', l_user_profile_option_name);
160 l_mesg := FND_MESSAGE.Get;
161
162 ozf_utility_pvt.write_conc_log(l_mesg);
163 END IF;
164
165 IF l_prof_check = 'N'
166 THEN
167 RAISE FND_API.g_exc_error;
168 END IF;
169
170 -- Process any error from the previous run
171
172 ozf_utility_pvt.write_conc_log(' -- Updating errors from previous run., if any ');
173
174 UPDATE ozf_sales_transactions_all
175 SET common_amount = gl_currency_api.convert_amount_sql(currency_code,
176 common_currency_code,
177 transaction_date,
178 l_curr_conv_type,
179 amount) ,
180 common_quantity = inv_convert.inv_um_convert(inventory_item_id,
181 NULL,
182 quantity,
183 uom_code,
184 common_uom_code,
185 NULL,
186 NULL) ,
187 error_flag = DECODE(sign(inv_convert.inv_um_convert( inventory_item_id,
188 NULL,
189 quantity,
190 uom_code,
191 common_uom_code,
192 NULL,
193 NULL)
194 ), -1, 'Y',
195 DECODE(
196 sign(gl_currency_api.convert_amount_sql(currency_code,
197 common_currency_code,
198 transaction_date,
199 l_curr_conv_type,
200 amount)
201 ),-1,'Y','N'
202 )
203 )
204 WHERE source_code = 'OM'
205 AND error_flag = 'Y';
206
207
208 ozf_utility_pvt.write_conc_log(' -- Inserting New transaction ');
209
210 -- Insert
211 INSERT INTO ozf_sales_transactions_all(
212 SALES_TRANSACTION_ID ,
213 OBJECT_VERSION_NUMBER ,
214 LAST_UPDATE_DATE ,
215 LAST_UPDATED_BY ,
216 CREATION_DATE ,
217 REQUEST_ID ,
218 CREATED_BY ,
219 CREATED_FROM ,
220 LAST_UPDATE_LOGIN ,
221 PROGRAM_APPLICATION_ID ,
222 PROGRAM_UPDATE_DATE ,
223 PROGRAM_ID ,
224 SOLD_TO_CUST_ACCOUNT_ID ,
225 BILL_TO_SITE_USE_ID ,
226 SHIP_TO_SITE_USE_ID ,
227 TRANSACTION_DATE,
228 QUANTITY ,
229 UOM_CODE ,
230 AMOUNT ,
231 CURRENCY_CODE ,
232 INVENTORY_ITEM_ID ,
233 PRIMARY_QUANTITY ,
234 PRIMARY_UOM_CODE ,
235 AVAILABLE_PRIMARY_QUANTITY ,
236 COMMON_QUANTITY ,
237 COMMON_UOM_CODE ,
238 COMMON_CURRENCY_CODE ,
239 COMMON_AMOUNT ,
240 ERROR_FLAG,
241 HEADER_ID ,
242 LINE_ID ,
243 ORG_ID,
244 SOURCE_CODE,
245 TRANSFER_TYPE,
246 SOLD_TO_PARTY_ID,
247 SOLD_TO_PARTY_SITE_ID
248 )
249 SELECT ozf_sales_transactions_all_s.nextval,
250 1,
251 SYSDATE,
252 FND_GLOBAL.user_id,
253 SYSDATE,
254 -1,
255 FND_GLOBAL.user_id,
256 'OZFVRFSB',
257 -1,
258 NULL, --PROGRAM_APPLICATION_ID
259 NULL, --PROGRAM_UPDATE_DATE
260 NULL, --PROGRAM_ID
261 ln.sold_to_org_id, --SOLD_TO_CUST_ACCOUNT_ID ,
262 ln.invoice_to_org_id, --BILL_TO_SITE_USE_ID ,
263 ln.ship_to_org_id, --SHIP_TO_SITE_USE_ID ,
264 NVL(TRUNC(ln.actual_shipment_date),TRUNC(ln.request_date)),
265 /* 4590570
266 DECODE(ln.line_category_code,
267 'ORDER', TRUNC(ln.actual_shipment_date),
268 'RETURN', TRUNC(rln.actual_shipment_date)
269 ), -- TRANSACTION_DATE
270 */
271 NVL(ln.shipped_quantity,ln.ordered_quantity), --QUANTITY ,
272 ln.order_quantity_uom, --UOM ,
273 ln.unit_selling_price* NVL(ln.shipped_quantity,ln.ordered_quantity), --AMOUNT ,
274 hdr.transactional_curr_code, --CURRENCY_CODE ,
275 ln.inventory_item_id, --INVENTORY_ITEM_ID ,
276 inv_convert.inv_um_convert(ln.inventory_item_id,
277 NULL,
278 NVL(ln.shipped_quantity,ln.ordered_quantity),
279 ln.order_quantity_uom,
280 get_primary_uom(to_number(ln.inventory_item_id)),
281 NULL,
282 NULL),--PRIMARY_QUANTITY ,
283 get_primary_uom(to_number(ln.inventory_item_id)), --PRIMARY_UOM ,
284 inv_convert.inv_um_convert(ln.inventory_item_id,
285 NULL,
286 NVL(ln.shipped_quantity,ln.ordered_quantity),
287 ln.order_quantity_uom,
288 get_primary_uom(to_number(ln.inventory_item_id)),
289 NULL,
290 NULL), --AVAILABLE_PRIMARY_QUANTITY ,
291 inv_convert.inv_um_convert(ln.inventory_item_id,
292 NULL,
293 NVL(ln.shipped_quantity,ln.ordered_quantity),
294 ln.order_quantity_uom,
295 l_common_uom,
296 NULL,
297 NULL), --COMMON_QUANTITY ,
298 l_common_uom, --COMMON_UOM ,
299 l_common_currency_code, --COMMON_CURRENCY_CODE ,
300 gl_currency_api.convert_amount_sql(hdr.transactional_curr_code,
301 l_common_currency_code,
302 NVL(ln.actual_shipment_date,ln.request_date),
303 l_curr_conv_type,
304 ln.unit_selling_price*( NVL(ln.shipped_quantity,ln.ordered_quantity))
305 ) , --COMMON_AMOUNT ,
306 DECODE(sign(inv_convert.inv_um_convert(ln.inventory_item_id,
307 NULL,
308 NVL(ln.shipped_quantity,ln.ordered_quantity),
309 ln.order_quantity_uom,
310 l_common_uom,
311 NULL,
312 NULL)
313 ), -1, 'Y',
314 DECODE(
315 sign(gl_currency_api.convert_amount_sql(hdr.transactional_curr_code,
316 l_common_currency_code,
317 NVL(ln.actual_shipment_date,ln.request_date),
318 l_curr_conv_type,
319 (ln.unit_selling_price*NVL(ln.shipped_quantity,ln.ordered_quantity)))
320 ),-1,'Y','N'
321 )
322 ), -- ERROR_FLAG
323 ln.header_id, --HEADER_ID ,
324 ln.line_id, --LINE_ID ,
325 ln.org_id, --ORG_ID,
326 'OM', --SOURCE_CODE
327 DECODE(ln.line_category_code,
328 'ORDER', 'IN',
329 'RETURN', 'OUT')
330 , get_party_id(ln.sold_to_org_id) party_id
331 , get_party_site_id(ln.invoice_to_org_id) SOLD_TO_PARTY_SITE_ID
332 FROM oe_order_headers_all hdr,
333 oe_order_lines_all ln
334 WHERE ln.open_flag = 'N'
335 AND ln.cancelled_flag = 'N'
336 AND ln.header_id = hdr.header_id
337 AND NVL(ln.actual_shipment_date,ln.request_date) > l_global_start_date ;
338
339 /* Bug 5371613
340 Incremental load is not done by Funds Accrual Engine
341 This program will now be run only in full refresh mode for the first time
342 AND NOT EXISTS ( SELECT 1
343 FROM ozf_sales_transactions_all trx
344 WHERE trx.line_id = ln.line_id
345 AND source_code = 'OM' );
346 */
347
348 -- Log error messages here
349 /*
350 Get all records from ozf_sales_transactions_all with error_flag = 'Y'
351 converted_quantity = -9999 or converted_amount = -1
352 */
353
354 ozf_utility_pvt.write_conc_log(' -- Currency and UOM conversion Errors -- ');
355
356 FOR err IN error_csr
357 LOOP
358 IF err.common_quantity < 0
359 THEN
360 -- UOM and COMMON_UOM conversion
361 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_CONVERSIONS');
362 FND_MESSAGE.Set_Token('TYPE', 'UOM');
363 FND_MESSAGE.Set_Token('FROM_VALUE', err.uom_code);
364 FND_MESSAGE.Set_Token('TO_VALUE', err.common_uom_code);
365 FND_MESSAGE.Set_Token('DATE', err.transaction_date);
366 l_mesg := FND_MESSAGE.Get;
367
368 ozf_utility_pvt.write_conc_log(l_mesg);
369
370 END IF;
371
372 IF err.common_amount < 0
373 THEN
374 -- CURRENCY_CODE and COMMON_CURRENCY_CODE conversion
375 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_CONVERSIONS');
376 FND_MESSAGE.Set_Token('TYPE', 'CURRENCY');
377 FND_MESSAGE.Set_Token('FROM_VALUE', err.currency_code);
378 FND_MESSAGE.Set_Token('TO_VALUE', err.common_currency_code);
379 FND_MESSAGE.Set_Token('DATE', err.transaction_date);
380 l_mesg := FND_MESSAGE.Get;
381
382 ozf_utility_pvt.write_conc_log(l_mesg);
383
384 END IF;
385
386 END LOOP;
387
388 --
389 -- End full load logic
390 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
391
392 EXCEPTION
393 WHEN FND_API.G_EXC_ERROR THEN
394 x_return_status := FND_API.g_ret_sts_error ;
395
396 WHEN OTHERS THEN
397 x_return_status := FND_API.g_ret_sts_unexp_error ;
398 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' Unexpected Error ' );
399 ozf_utility_pvt.write_conc_log(sqlerrm(sqlcode) );
400
401 END full_load;
402
403
404 /*
405 PROCEDURE order_sales_sumry_mv_refresh
406 (ERRBUF OUT NOCOPY VARCHAR2
407 ,RETCODE OUT NOCOPY NUMBER)
408 IS
409 BEGIN
410
411 ozf_utility_pvt.write_conc_log(' -- Begin Materialized view refresh -- ');
412
413 DBMS_MVIEW.REFRESH(
414 list => 'ORDER' ,
415 method => '?'
416 );
417
418 ozf_utility_pvt.write_conc_log(' -- End Materialized view refresh -- ');
419 --------------------------------------------------------
420 -- Gather statistics for the use of cost-based optimizer
421 --------------------------------------------------------
422
423 ozf_utility_pvt.write_conc_log(' -- Begin FND_STATS API to gather table statstics -- ');
424
425 fnd_stats.gather_table_stats (ownname=>'APPS', tabname=>'OZF_ORDER_SALES_SUMRY_MV');
426
427 ozf_utility_pvt.write_conc_log(' -- End FND_STATS API to gather table statstics -- ');
428
429 EXCEPTION
430
431 WHEN FND_API.G_EXC_ERROR THEN
432 Retcode := -2;
433
434 WHEN OTHERS THEN
435 Errbuf:= sqlerrm;
436 Retcode:=sqlcode;
437 ozf_utility_pvt.write_conc_log(Retcode||':'||Errbuf);
438
439 END order_sales_sumry_mv_refresh;
440 */
441
442 PROCEDURE load (
443 ERRBUF OUT NOCOPY VARCHAR2,
444 RETCODE OUT NOCOPY NUMBER,
445 p_increment_mode IN VARCHAR2 DEFAULT NULL)
446 IS
447 v_error_code NUMBER;
448 v_error_text VARCHAR2(1500);
449 l_user_id NUMBER := FND_GLOBAL.USER_ID();
450 l_api_version CONSTANT NUMBER := 1.0;
451 l_api_name CONSTANT VARCHAR2(30) := 'ozf_refresh_order_sales_pkg';
452 x_msg_count NUMBER;
453 x_msg_data VARCHAR2(240);
454 x_return_status VARCHAR2(1) ;
455 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
456
457 BEGIN
458 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' (-)');
459
460 -- Initialize API return status to SUCCESS
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 IF (NVL(p_increment_mode,'N') = 'N')
464 THEN
465 DELETE FROM ozf_sales_transactions_all
466 WHERE source_code = 'OM';
467 END IF;
468
469 COMMIT;
470
471 full_load( x_return_status );
472
473 IF x_return_status = FND_API.g_ret_sts_error
474 THEN
475 RAISE FND_API.g_exc_error;
476 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
477 RAISE FND_API.g_exc_unexpected_error;
478 END IF;
479
480 ozf_utility_pvt.write_conc_log(' -- Commiting Transactions before MV Refresh ');
481
482 COMMIT;
483
484 ozf_utility_pvt.write_conc_log(' -- Committed !! ');
485 --
486 -- Refresh the MVS here
487 --
488
489 ozf_utility_pvt.write_conc_log(' -- Begin MV Refresh -- ');
490
491 ozf_refresh_view_pvt.load(ERRBUF, RETCODE, 'ORDER');
492 ozf_refresh_view_pvt.load(ERRBUF, RETCODE, 'INVENTORY');
493
494 ozf_utility_pvt.write_conc_log(' -- End MV Refresh -- ');
495
496 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' (+)');
497
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500 x_return_status := FND_API.g_ret_sts_error ;
501 ERRBUF := x_msg_data;
502 RETCODE := 2;
503 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Expected Error');
504
505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506 x_return_status := FND_API.g_ret_sts_unexp_error ;
507 ERRBUF := sqlerrm(sqlcode);
508 RETCODE := 2;
509 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Unexpected Error');
510
511 WHEN OTHERS THEN
512 x_return_status := FND_API.g_ret_sts_unexp_error ;
513 ERRBUF := sqlerrm(sqlcode);
514 RETCODE := 2;
515 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Others');
516
517 END load;
518
519
520 END ozf_refresh_sales_pvt;