[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BOOK_SUM2_F_C
Source
1 PACKAGE BODY ISC_DBI_BOOK_SUM2_F_C AS
2 /* $Header: ISCSCF7B.pls 120.6 2006/06/07 22:58:58 scheung noship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(200) := NULL;
6 g_row_count NUMBER := 0;
7 g_push_from_date DATE := NULL;
8 g_push_to_date DATE := NULL;
9 g_batch_size NUMBER;
10 g_degree NUMBER :=1;
11 g_global_currency VARCHAR2(15);
12 g_global_rate_type VARCHAR2(15);
13 g_sec_global_currency VARCHAR2(15);
14 g_sec_global_rate_type VARCHAR2(15);
15 g_treasury_rate_type VARCHAR2(80);
16 g_global_start_date DATE;
17 g_incre_start_date DATE;
18 g_load_mode VARCHAR2(30);
19 g_warning NUMBER := 0;
20
21 -- ----------------------------------
22 -- FUNCTION GET_CUST_PRODUCT_LINE_ID
23 -- ----------------------------------
24
25 FUNCTION GET_CUST_PRODUCT_LINE_ID(p_sold_to_org_id IN NUMBER,
26 p_service_reference_line_id IN NUMBER) RETURN NUMBER IS
27
28 l_return_status VARCHAR2(1);
29 l_order_line_id NUMBER;
30
31 BEGIN
32
33 OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
34 (x_return_status => l_return_status,
35 p_reference_line_id => p_service_reference_line_id,
36 p_customer_id => p_sold_to_org_id,
37 x_cust_product_line_id => l_order_line_id);
38
39 RETURN(l_order_line_id);
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 RETURN (NULL);
44
45 END get_cust_product_line_id;
46
47
48 -- -----------------
49 -- UPDATE_SALES_FACT
50 -- -----------------
51 -- =====================================================================
52 -- ====== START OF INCREMENTAL COLLECTION FOR SALES CREDITS FACT =======
53 -- =====================================================================
54
55 FUNCTION UPDATE_SALES_FACT RETURN NUMBER IS
56
57 l_isc_schema VARCHAR2(30);
58 l_status VARCHAR2(30);
59 l_industry VARCHAR2(30);
60 l_stmt VARCHAR2(32000);
61
62 BEGIN
63
64
65 /* Insert into ISC_TMP_BOOK_SUM2 all the orders lines for orders having at least 1 line that is present in ISC_TMP_BOOK_SUM2 */
66 FII_UTIL.Start_Timer;
67 INSERT INTO isc_tmp_book_sum2 (pk1)
68 SELECT f.line_id
69 FROM isc_book_sum2_f f
70 WHERE f.header_id IN (SELECT fact.header_id
71 FROM isc_sales_credits_f fact,
72 isc_tmp_book_sum2 tmp
73 WHERE fact.line_id = tmp.pk1
74 )
75 AND NOT EXISTS (SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
76
77 FII_UTIL.Stop_Timer;
78 FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (updated lines) in');
79
80 /* Insert into ISC_TMP_BOOK_SUM2 order lines from ISC_SALES_CREDITS_F to be recollected because their sales credit have been deleted from OE_SALES_CREDITS */
81 FII_UTIL.Start_Timer;
82 INSERT INTO isc_tmp_book_sum2 (pk1)
83 SELECT f.line_id
84 FROM isc_book_sum2_f f
85 WHERE header_id IN (SELECT fact.header_id
86 FROM isc_sales_credits_f fact
87 WHERE NOT EXISTS (SELECT 1 FROM oe_sales_credits WHERE sales_credit_id = fact.sales_credit_id)
88 )
89 AND NOT EXISTS (SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
90
91 FII_UTIL.Stop_Timer;
92 FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (lines deleted from oe_sc..) in');
93
94 COMMIT;
95
96
97 /* Insert into ISC_TMP_BOOK_SUM2 "SERVICE" order lines referencing parent service identified in #1 and #2 */
98 -- SERVICE rows whose ORDER or CUSTOMER_PRODUCT parent line_id has been updated
99 FII_UTIL.Start_Timer;
100 INSERT INTO ISC_TMP_BOOK_SUM2 (pk1)
101 SELECT f.line_id
102 FROM isc_book_sum2_f f
103 WHERE f.header_id IN
104 (SELECT fact.header_id
105 FROM isc_tmp_book_sum2 tmp,
106 isc_book_sum2_f fact
107 WHERE tmp.pk1 = fact.service_parent_line_id
108 AND EXISTS (SELECT 1 FROM isc_book_sum2_f WHERE line_id = fact.service_parent_line_id))
109 AND NOT EXISTS( SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
110
111 FII_UTIL.Stop_Timer;
112 FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (service_parent_line_id...) in');
113
114 COMMIT;
115
116 -- Delete from ISC_SALES_CREDITS_F order lines that will be recollected --
117 FII_UTIL.Start_Timer;
118 DELETE FROM isc_sales_credits_f
119 WHERE line_id in (SELECT pk1 from ISC_TMP_BOOK_SUM2);
120
121 FII_UTIL.Stop_Timer;
122 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' rows from ISC_SALES_CREDITS_F in');
123 COMMIT;
124 /* Insert into ISC_SALES_CREDITS_F */
125 BIS_COLLECTION_UTILITIES.put_line(' ');
126 BIS_COLLECTION_UTILITIES.put_line('Inserting data into sales fact table');
127 FII_UTIL.Start_Timer;
128
129 INSERT /*+ APPEND PARALLEL(f) */ INTO isc_sales_credits_f f
130 with s as (
131 select /*+ ordered use_hash(sc) parallel(sc) parallel(sr)
132 pq_distribute(sr,hash,hash) */
133 sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
134 sc.salesrep_id, sc.header_id, sc.line_id, sr.resource_id,
135 sr.org_id, sc.sales_group_id group_id, sc.created_by, sc.creation_date,
136 sc.last_updated_by, sc.last_update_date, sc.last_update_login
137 from oe_sales_credit_types sc_typ,
138 oe_sales_credits sc,
139 jtf_rs_salesreps sr
140 where sc.sales_group_id is not null
141 and sc.salesrep_id = sr.salesrep_id
142 and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
143 and sc_typ.quota_flag = 'Y'
144 union all
145 select /*+ ordered use_hash(sc) parallel(sc) parallel(sg)
146 pq_distribute(sg,hash,hash) */
147 sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
148 sc.salesrep_id, sc.header_id, sc.line_id, sg.resource_id,
149 sg.org_id, sg.group_id, sc.created_by, sc.creation_date,
150 sc.last_updated_by, sc.last_update_date, sc.last_update_login
151 from oe_sales_credit_types sc_typ,
152 oe_sales_credits sc,
153 jtf_rs_srp_groups sg
154 where sc.sales_group_id is null
155 and sc.salesrep_id = sg.salesrep_id
156 and sc.last_update_date between sg.start_date and sg.end_date
157 and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
158 and sc_typ.quota_flag = 'Y')
159 SELECT pk, sales_credit_id, resource_id, group_id, header_id, line_id,
160 percent, sales_credit_type_id, created_by, creation_date,
161 last_updated_by, last_update_date, last_update_login
162 FROM (SELECT pk, sales_credit_id, resource_id, group_id, header_id, line_id,
163 percent, sales_credit_type_id, created_by, creation_date,
164 last_updated_by, last_update_date, last_update_login,
165 rank() over (partition by line_id order by rnk) low_rnk
166 FROM (SELECT /*+ parallel(s) */
167 'DIRECT-'||s.sales_credit_id pk,
168 s.sales_credit_id, s.group_id, t5.header_id, t5.line_id,
169 1 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
170 s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
171 FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t5, s
172 WHERE tmp.pk1 = t5.line_id
173 AND s.org_id = t5.org_ou_id
174 AND s.line_id = t5.line_id
175 UNION ALL
176 SELECT /*+ parallel(s) parallel(t7a) use_hash(s) pq_distribute(s,hash,hash) */
177 'SERVICE_PARENT-'||t7a.line_id||'-'||s.sales_credit_id pk,
178 s.sales_credit_id, s.group_id, t7a.header_id, t7a.line_id,
179 2 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
180 s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
181 FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7a, s
182 WHERE tmp.pk1 = t7a.line_id
183 AND s.org_id = t7a.org_ou_id
184 AND s.line_id = t7a.service_parent_line_id
185 AND t7a.item_type_code = 'SERVICE'
186 UNION ALL
187 SELECT /*+ parallel(s) parallel(t7b2) use_hash(s) pq_distribute(s,hash,hash)
188 parallel(t7b1) use_hash(t7b1) pq_distribute(t7b1,hash,hash) */
189 'SERVICE_PARENT_TOPMODEL-'||t7b2.line_id||'-'||s.sales_credit_id pk,
190 s.sales_credit_id, s.group_id group_id, t7b2.header_id, t7b2.line_id,
191 3 rnk, s.resource_id,
192 s.percent, s.sales_credit_type_id, s.created_by,
193 s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
194 FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7b1, isc_book_sum2_f t7b2, s
195 WHERE tmp.pk1 = t7b2.line_id
196 AND t7b2.item_type_code = 'SERVICE'
197 AND t7b1.line_id = t7b2.service_parent_line_id
198 AND s.line_id = t7b1.top_model_line_id
199 AND s.org_id = t7b1.org_ou_id
200 UNION ALL
201 SELECT /*+ ordered parallel(s) parallel(t7b1) use_hash(s) pq_distribute(s,hash,hash) */
202 'TOPMODEL-'||t7b1.line_id||'-'||s.sales_credit_id pk,
203 s.sales_credit_id, s.group_id, t7b1.header_id, t7b1.line_id,
204 4 rnk, s.resource_id,
205 s.percent, s.sales_credit_type_id, s.created_by,
206 s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
207 FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7b1, s
208 WHERE tmp.pk1 = t7b1.line_id
209 AND s.line_id = t7b1.top_model_line_id
210 AND s.org_id = t7b1.org_ou_id
211 UNION ALL
212 SELECT /*+ ordered parallel(s) parallel(t11) use_hash(s) pq_distribute(s,hash,hash) */
213 'HEADER-'||t11.line_id||'-'||s.sales_credit_id pk,
214 s.sales_credit_id, s.group_id, t11.header_id, t11.line_id,
215 5 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
216 s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
217 FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t11, s
218 WHERE tmp.pk1 = t11.line_id
219 AND s.line_id IS NULL
220 AND s.org_id = t11.org_ou_id
221 AND s.header_id = t11.header_id))
222 WHERE low_rnk = 1;
223
224 FII_UTIL.Stop_Timer;
225 FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into the sales fact table in');
226 COMMIT;
227
228 RETURN(1);
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 g_errbuf := 'Error in function Update Sales Fact: '||sqlerrm;
233 RETURN(-1);
234
235 END update_sales_fact;
236 -- ===================================================================
237 -- ====== END OF INCREMENTAL COLLECTION FOR SALES CREDITS FACT ======
238 -- ===================================================================
239
240
241 -- --------------
242 -- TRUNCATE_TABLE
243 -- --------------
244
245 FUNCTION TRUNCATE_TABLE(table_name IN VARCHAR2) RETURN NUMBER IS
246
247 l_isc_schema VARCHAR2(30);
248 l_stmt VARCHAR2(200);
249 l_status VARCHAR2(30);
250 l_industry VARCHAR2(30);
251
252 BEGIN
253
254 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_isc_schema)) THEN
255 l_stmt := 'TRUNCATE TABLE ' || l_isc_schema ||'.'||table_name;
256 EXECUTE IMMEDIATE l_stmt;
257 END IF;
258
259 RETURN(1);
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 g_errbuf := 'Error in function Truncate_Table : '||sqlerrm;
264 RETURN(-1);
265
266 END truncate_table;
267
268 -- -----------
269 -- CHECK_SETUP
270 -- -----------
271
272 FUNCTION CHECK_SETUP RETURN NUMBER IS
273
274 l_list dbms_sql.varchar2_table;
275 l_sec_curr_def VARCHAR2(1);
276
277 BEGIN
278
279 l_list(1) := 'BIS_GLOBAL_START_DATE';
280 IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN
281 g_errbuf := 'Collection aborted because the global start date has not been set up.';
282 return(-1);
283 END IF;
284
285 IF (nvl(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'),'N') <> 'Y') THEN
286 g_errbuf := 'Collection aborted because the profile option (OM: DBI Installation) has not been set to Y.';
287 return(-1);
288 END IF;
289
290 l_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
291 IF (l_sec_curr_def = 'E') THEN
292 g_errbuf := 'Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.';
293 return(-1);
294 END IF;
295
296 g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
297 BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
298
299 g_global_start_date := bis_common_parameters.get_global_start_date;
300 BIS_COLLECTION_UTILITIES.put_line('The global start date is ' || g_global_start_date);
301
302 g_global_currency := bis_common_parameters.get_currency_code;
303 BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
304
305 g_global_rate_type := bis_common_parameters.get_rate_type;
306 BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
307
308 g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
309 BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
310
311 g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
312 BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
313
314 g_treasury_rate_type := bis_common_parameters.get_treasury_rate_type;
315 IF (g_treasury_rate_type IS NULL) THEN
316 g_treasury_rate_type := g_global_rate_type;
317 BIS_COLLECTION_UTILITIES.put_line('The treasury rate type is not set up. Use primary rate type instead.');
318 END IF;
319 BIS_COLLECTION_UTILITIES.put_line('The treasury rate type is ' || g_treasury_rate_type);
320
321 -- g_degree := bis_common_parameters.get_degree_of_parallelism;
322 -- BIS_COLLECTION_UTILITIES.put_line('The degree of parallelism is ' || g_degree);
323
324 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
325 FII_UTIL.Start_Timer;
326
327 IF (truncate_table('ISC_DBI_CHANGE_LOG') = -1) THEN
328 return(-1);
329 END IF;
330
331 IF (truncate_table('ISC_TMP_BOOK_SUM2') = -1) THEN
332 return(-1);
333 END IF;
334
335 IF (truncate_table('ISC_CURR_BOOK_SUM2') = -1) THEN
336 return(-1);
337 END IF;
338
339 IF (truncate_table('ISC_SERVICE_BOOK_SUM2') = -1) THEN
340 return(-1);
341 END IF;
342
343 FII_UTIL.Stop_Timer;
344 FII_UTIL.Print_Timer('Truncated the temp table in');
345 BIS_COLLECTION_UTILITIES.Put_Line(' ');
346
347 RETURN(1);
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 g_errbuf := 'Error in function CHECK_SETUP : '||sqlerrm;
352 RETURN(-1);
353
354 END check_setup;
355
356 -- --------------------
357 -- IDENTIFY_CHANGE_INIT
358 -- --------------------
359
360 FUNCTION IDENTIFY_CHANGE_INIT RETURN NUMBER IS
361
362 l_count NUMBER;
363 l_stmt VARCHAR2(8000);
364 l_from_date VARCHAR2(30);
365 l_to_date VARCHAR2(30);
366
367 BEGIN
368
369 l_count := 0;
370 l_from_date := to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS');
371 l_to_date := to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS');
372
373 -- --------------------------------------------------------
374 -- Populate line_id into isc_tmp_book_sum2 table
375 -- VIEW_TYPE: 0 - NON CTO items
376 -- 1 - ATO items
377 -- 2 - PTO top model/KIT (nonshippable)
378 -- 3 - PTO top model/KIT (shippable)
379 -- 4 - others
380 -- --------------------------------------------------------
381
382 BIS_COLLECTION_UTILITIES.put_line('Identifying Booked orders lines');
383 FII_UTIL.Start_Timer;
384
385
386 l_stmt := 'INSERT /*+ APPEND PARALLEL(F) */ '||
387 'INTO isc_tmp_book_sum2 F('||
388 'PK1,'||
389 'VIEW_TYPE,'||
390 'LOG_ROWID,'||
391 'BATCH_ID,'||
392 'CURR_CONV_DATE,'||
393 'FROM_CURRENCY,'||
394 'TO_CURRENCY1,'||
395 'RATE_TYPE, RATE1,'||
396 'TO_CURRENCY2, '||
397 'TO_CURRENCY3, '||
398 'TO_CURRENCY4, '||
399 'INV_OU_ID,'||
400 'MASTER_ORG_ID,'||
401 'INVENTORY_ITEM_ID,'||
402 'ITEM_INV_ORG_ID,'||
403 'TIME_BOOKED_DATE_ID,'||
404 'TIME_SHIPPED_DATE_ID,'||
405 'TIME_FULFILLED_DATE_ID,'||
406 'TIME_SCHEDULE_DATE_ID,'||
407 'TOP_MODEL_LINE_ID,'||
408 'ORDER_QUANTITY_UOM,'||
409 'INV_UOM_CODE,'||
410 'INV_UOM_RATE,'||
411 'ORDER_NUMBER,'||
412 'HEADER_ID,'||
413 'LINE_NUMBER,'||
414 'SERVICE_REFERENCE_TYPE_CODE,'||
415 'SOLD_TO_ORG_ID,'||
416 'SERVICE_REFERENCE_LINE_ID,'||
417 'FREIGHT_CHARGE,'||
418 'FREIGHT_COST)' ||
419 ' SELECT /*+ USE_HASH(h,l,opa,aspa,gsb,hoi,ospa,gsb1,item) PARALLEL(h) PARALLEL(l) PARALLEL(opa) PARALLEL(aspa) PARALLEL(gsb) PARALLEL(hoi) PARALLEL(ospa) PARALLEL(gsb1) PARALLEL(item) */ '||
420 ' l.line_id PK1,'||
421 ' decode(l.top_model_line_id,'||
422 ' null, 0,'||
423 ' decode(ato_line_id,'||
424 ' null, decode(item_type_code,'||
425 ' ''MODEL'', 3,'||
426 ' ''KIT'', 3, 4), 1)) VIEW_TYPE,'||
427 ' null LOG_ROWID,'||
428 ' null BATCH_ID,'||
429 ' decode(upper(h.conversion_type_code),'||
430 ' ''USER'', h.conversion_rate_date,'||
431 ' h.booked_date) CURR_CONV_DATE,'||
432 ' h.transactional_curr_code FROM_CURRENCY,'||
433 ' gsb.currency_code TO_CURRENCY1,'||
434 ' nvl(h.conversion_type_code,'''||
435 g_treasury_rate_type ||''') RATE_TYPE,'||
436 ' decode(upper(h.conversion_type_code),'||
437 ' ''USER'', h.conversion_rate,null) RATE1,'''||
438 g_global_currency || ''' TO_CURRENCY2,'||
439 ' gsb1.currency_code TO_CURRENCY3,'''||
440 g_sec_global_currency || ''' TO_CURRENCY4,'||
441 ' to_number(hoi.org_information3) INV_OU_ID,'||
442 ' ospa.parameter_value MASTER_ORG_ID,'||
443 ' l.inventory_item_id INVENTORY_ITEM_ID,'||
444 ' nvl(l.ship_from_org_id,ospa.parameter_value) ITEM_INV_ORG_ID,'||
445 ' trunc(nvl(l.order_firmed_date, h.booked_date)) TIME_BOOKED_DATE_ID,'||
446 ' trunc(l.actual_shipment_date) TIME_SHIPPED_DATE_ID,'||
447 ' trunc(nvl(l.actual_fulfillment_date, l.fulfillment_date)) TIME_FULFILLED_DATE_ID,'||
448 ' trunc(l.schedule_ship_date) TIME_SCHEDULE_DATE_ID,'||
449 ' l.top_model_line_id TOP_MODEL_LINE_ID,'||
450 ' l.order_quantity_uom ORDER_QUANTITY_UOM,'||
451 ' item.primary_uom_code INV_UOM_CODE,'||
452 ' decode(l.order_quantity_uom, item.primary_uom_code,1,'||
453 ' INV_CONVERT.inv_um_convert('||
454 ' l.inventory_item_id,NULL,1,'||
455 ' l.order_quantity_uom,'||
456 ' item.primary_uom_code,'||
457 ' NULL, NULL)) INV_UOM_RATE,'||
458 ' h.order_number ORDER_NUMBER,'||
459 ' h.header_id HEADER_ID,'||
460 ' l.line_number ||''.''||'||
461 ' l.shipment_number||decode(l.service_number,'''','||
462 ' decode(l.component_number,'''','||
463 ' decode(l.option_number,'''','''',''.''),''.''),''.'') ||'||
464 ' l.option_number||decode(l.service_number,'''','||
465 ' decode(l.component_number,'''','''',''.''),''.'') ||'||
466 ' l.component_number||decode(l.service_number,'''','''',''.'')||'||
467 ' l.service_number LINE_NUMBER,'||
468 ' l.service_reference_type_code SERVICE_REFERENCE_TYPE_CODE,'||
469 ' l.sold_to_org_id SOLD_TO_ORG_ID,'||
470 ' l.service_reference_line_id SERVICE_REFERENCE_LINE_ID,'||
471 ' nvl(opa.charge_adjamt,0)*l.ordered_quantity + nvl(opa.charge_operand,0) FREIGHT_CHARGE,'||
472 ' opa.cost FREIGHT_COST'||
473 ' FROM OE_ORDER_HEADERS_ALL h,'||
474 ' OE_ORDER_LINES_ALL l,'||
475 ' AR_SYSTEM_PARAMETERS_ALL aspa,'||
476 ' GL_SETS_OF_BOOKS gsb,'||
477 ' HR_ORGANIZATION_INFORMATION hoi,'||
478 ' OE_SYS_PARAMETERS_ALL ospa,'||
479 ' GL_SETS_OF_BOOKS gsb1,'||
480 ' MTL_SYSTEM_ITEMS_B item, '||
481 ' (select p.line_id, sum(decode(p.list_line_type_code, ''COST'', '||
482 ' p.adjusted_amount, null)) cost, '||
483 ' sum(decode(p.list_line_type_code, ''FREIGHT_CHARGE'', '||
484 ' decode(nvl(p.applied_flag, ''Y''), ''Y'', '||
485 ' decode(p.arithmetic_operator, ''LUMPSUM'', p.operand, null), '||
486 ' null), null)) charge_operand, '||
487 ' sum(decode(p.list_line_type_code, ''FREIGHT_CHARGE'', '||
488 ' decode(nvl(p.applied_flag, ''Y''), ''Y'', '||
489 ' decode(p.arithmetic_operator, ''LUMPSUM'', null, p.adjusted_amount), '||
490 ' null), null)) charge_adjamt '||
491 ' from oe_price_adjustments p '||
492 ' where p.line_id is not null '||
493 ' and p.charge_type_code in (''FTECHARGE'', ''FTEPRICE'') '||
494 ' group by p.line_id) opa '||
495 ' WHERE nvl(l.order_firmed_date, h.booked_date) >= to_date('''|| l_from_date || ''',''MM/DD/YYYY HH24:MI:SS'')'||
496 ' AND l.header_id = h.header_id'||
497 ' AND l.line_id = opa.line_id (+) '||
498 ' AND h.org_id = aspa.org_id'||
499 ' AND aspa.set_of_books_id = gsb.set_of_books_id'||
500 ' AND h.booked_flag = ''Y'''||
501 ' AND h.booked_date IS NOT NULL'||
502 ' AND hoi.org_information_context =''Accounting Information'''||
503 ' AND h.org_id = ospa.org_id'||
504 ' AND ospa.parameter_code = ''MASTER_ORGANIZATION_ID'''||
505 ' AND hoi.organization_id = nvl(l.ship_from_org_id, ospa.parameter_value)'||
506 ' AND hoi.org_information1 = to_char(gsb1.set_of_books_id)'||
507 ' AND l.inventory_item_id = item.inventory_item_id'||
508 ' AND nvl(l.ship_from_org_id, ospa.parameter_value) = item.organization_id';
509
510 EXECUTE IMMEDIATE l_stmt;
511
512 l_count := l_count + sql%rowcount;
513 FII_UTIL.Stop_Timer;
514 FII_UTIL.Print_Timer('Identified '||l_count||' records in');
515 COMMIT;
516
517
518 FII_UTIL.Start_Timer;
519
520 INSERT /*+ APPEND */
521 INTO isc_curr_book_sum2 F(
522 FROM_CURRENCY,
523 TO_CURRENCY1,
524 TO_CURRENCY3,
525 CONVERSION_DATE,
526 CONVERSION_TYPE,
527 RATE1,
528 RATE2,
529 RATE3,
530 RATE4)
531 SELECT from_currency, to_currency1, to_currency3, time_booked_date_id CONVERSION_DATE, rate_type CONVERSION_TYPE,
532 decode(from_currency, to_currency1, 1,
533 fii_currency.get_rate(from_currency, to_currency1, time_booked_date_id, rate_type)) RATE1,
534 decode(from_currency, g_global_currency, 1,
535 fii_currency.get_global_rate_primary(to_currency3, time_booked_date_id)) RATE2,
536 decode(from_currency, to_currency3, 1,
537 fii_currency.get_rate(from_currency, to_currency3, time_booked_date_id, g_global_rate_type)) RATE3,
538 decode(from_currency, g_sec_global_currency, 1,
539 fii_currency.get_global_rate_secondary(to_currency3, time_booked_date_id)) RATE4
540 FROM (SELECT /*+ PARALLEL(tmp) */ distinct from_currency, to_currency1, to_currency3, time_booked_date_id, rate_type
541 FROM isc_tmp_book_sum2 tmp);
542
543 FII_UTIL.Stop_Timer;
544 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
545 COMMIT;
546
547 FII_UTIL.Start_Timer;
548
549 INSERT /*+ APPEND */
550 INTO isc_service_book_sum2 F(
551 LINE_ID,
552 SERVICE_PARENT_LINE_ID)
553 SELECT pk1, ISC_DBI_BOOK_SUM2_F_C.get_cust_product_line_id(tmp.sold_to_org_id,tmp.service_reference_line_id)
554 FROM isc_tmp_book_sum2 tmp
555 WHERE service_reference_type_code = 'CUSTOMER_PRODUCT';
556
557 FII_UTIL.Stop_Timer;
558 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' customer product line in');
559 COMMIT;
560
561 RETURN(l_count);
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 g_errbuf := 'Error in Function IDENTIFY_CHANGE_INIT : '||sqlerrm;
566 g_retcode := sqlcode;
567 RETURN(-1);
568 END;
569
570 -- --------------------
571 -- DELETE_DBI_BASE
572 -- --------------------
573
574 FUNCTION DELETE_DBI_BASE RETURN NUMBER IS
575
576 l_count NUMBER := 0;
577
578 BEGIN
579
580 BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the base summary');
581 FII_UTIL.Start_Timer;
582
583 DELETE FROM isc_book_sum2_f
584 WHERE line_id IN (select pk1
585 from isc_tmp_book_sum2
586 where view_type = -1)
587 AND fulfilled_flag = 'N';
588
589 FII_UTIL.Stop_Timer;
590 FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' changed records in');
591 COMMIT;
592
593
594 /* Delete ont_dbi_change_log at the end */
595
596 -- BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from OM log table');
597 -- FII_UTIL.Start_Timer;
598
599 -- DELETE FROM ont_dbi_change_log
600 -- WHERE rowid IN (select log_rowid
601 -- from isc_tmp_book_sum2
602 -- where view_type = -1);
603
604 -- FII_UTIL.Stop_Timer;
605 -- FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' changed records in');
606 -- COMMIT;
607
608 BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from the temp table');
609 FII_UTIL.Start_Timer;
610
611 DELETE FROM isc_tmp_book_sum2
612 WHERE view_type = -1;
613 l_count := l_count + sql%rowcount;
614 COMMIT;
615
616 FII_UTIL.Stop_Timer;
617 FII_UTIL.Print_Timer('Deleted '||l_count||' changed records in');
618
619 RETURN(l_count);
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 g_errbuf := 'Error in Function DELETE_DBI_BASE : '||sqlerrm;
624 g_retcode := sqlcode;
625 RETURN(-1);
626 END;
627
628 -- --------------------
629 -- IDENTIFY_CHANGE_ICRL
630 -- --------------------
631
632 FUNCTION IDENTIFY_CHANGE_ICRL RETURN NUMBER IS
633
634 l_count NUMBER;
635 l_delete_count NUMBER := 0;
636 l_status VARCHAR2(30);
637 l_industry VARCHAR2(30);
638 l_schema VARCHAR2(30);
639
640 BEGIN
641
642 l_count := 0;
643
644 -- --------------------------------------------------------
645 -- Populate rowid into isc_tmp_book_sum2 table based
646 -- on order booked date
647 -- VIEW_TYPE: -1 - Deletion
648 -- 0 - NON CTO items
649 -- 1 - ATO items
650 -- 2 - PTO top model/KIT (nonshippable)
651 -- 3 - PTO top model/KIT (shippable)
652 -- 4 - others
653 -- --------------------------------------------------------
654
655 FII_UTIL.Start_Timer;
656
657 INSERT INTO isc_dbi_change_log (LINE_ID, HEADER_ID, LOG_ROWID, LAST_UPDATE_DATE)
658 SELECT line_id LINE_ID, header_id HEADER_ID, rowid LOG_ROWID, last_update_date LAST_UPDATE_DATE
659 FROM ont_dbi_change_log;
660
661 FII_UTIL.Stop_Timer;
662 FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_CHANGE_LOG');
663
664 COMMIT;
665
666
667 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_schema)) THEN
668 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
669 TABNAME => 'ISC_DBI_CHANGE_LOG');
670 END IF;
671
672
673 /* Make a copy of ont_dbi_change_log, no need to delete the duplication before the insert statement. */
674
675 -- DELETE FROM ont_dbi_change_log d1
676 -- WHERE EXISTS (SELECT 1 FROM ont_dbi_change_log d2
677 -- WHERE d2.rowid < d1.rowid
678 -- AND d2.last_update_date = d1.last_update_date
679 -- AND d2.line_id = d1.line_id);
680
681 -- DELETE FROM ont_dbi_change_log
682 -- WHERE (line_id, last_update_date) NOT IN (SELECT line_id, max(last_update_date)
683 -- FROM ont_dbi_change_log
684 -- GROUP BY line_id);
685 -- COMMIT;
686
687 FII_UTIL.Start_Timer;
688
689 INSERT INTO isc_tmp_book_sum2(pk1, view_type)
690 SELECT distinct line_id, -1
691 FROM isc_dbi_change_log log
692 WHERE NOT EXISTS (select '1'
693 from oe_order_lines_all l
694 where l.line_id = log.line_id);
695 l_delete_count := sql%rowcount;
696
697 FII_UTIL.Stop_Timer;
698 FII_UTIL.Print_Timer('Identified '|| l_delete_count || ' deleted lines in');
699 COMMIT;
700
701 IF l_delete_count > 0 THEN
702 BIS_COLLECTION_UTILITIES.Put_Line(' ');
703 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_TMP_BOOK_SUM2');
704 FII_UTIL.Start_Timer;
705 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_schema)) THEN
706 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
707 TABNAME => 'ISC_TMP_BOOK_SUM2');
708 END IF;
709 FII_UTIL.Stop_Timer;
710 FII_UTIL.Print_Timer('Analyzed table ISC_TMP_BOOK_SUM2 in ');
711
712 l_delete_count := DELETE_DBI_BASE;
713 IF (l_delete_count = -1) THEN
714 return -1;
715 END IF;
716 END IF;
717
718 FII_UTIL.Start_Timer;
719
720 INSERT
721 INTO isc_tmp_book_sum2(
722 PK1,
723 VIEW_TYPE,
724 -- LOG_ROWID,
725 BATCH_ID,
726 CURR_CONV_DATE,
727 FROM_CURRENCY,
728 TO_CURRENCY1,
729 RATE_TYPE,
730 RATE1,
731 TO_CURRENCY2,
732 TO_CURRENCY3,
733 TO_CURRENCY4,
734 INV_OU_ID,
735 MASTER_ORG_ID,
736 INVENTORY_ITEM_ID,
737 ITEM_INV_ORG_ID,
738 TIME_BOOKED_DATE_ID,
739 TIME_SHIPPED_DATE_ID,
740 TIME_FULFILLED_DATE_ID,
741 TIME_SCHEDULE_DATE_ID,
742 TOP_MODEL_LINE_ID,
743 ORDER_QUANTITY_UOM,
744 INV_UOM_CODE,
745 INV_UOM_RATE,
746 ORDER_NUMBER,
747 HEADER_ID,
748 LINE_NUMBER,
749 SERVICE_REFERENCE_TYPE_CODE,
750 SOLD_TO_ORG_ID,
751 SERVICE_REFERENCE_LINE_ID,
752 FREIGHT_CHARGE,
753 FREIGHT_COST)
754 SELECT /*+ leading(log) use_hash(hoi,gsb,gsb1) */ l.line_id,
755 decode(l.top_model_line_id,
756 null, 0,
757 decode(ato_line_id,
758 null, decode(item_type_code,
759 'MODEL', 3, 'KIT', 3, 4),1)) VIEW_TYPE,
760 -- log.rowid LOG_ROWID,
761 null BATCH_ID,
762 decode(upper(h.conversion_type_code),
763 'USER', h.conversion_rate_date,
764 h.booked_date) CURR_CONV_DATE,
765 h.transactional_curr_code FROM_CURRENCY,
766 gsb.currency_code TO_CURRENCY1,
767 nvl(h.conversion_type_code,
768 g_treasury_rate_type) RATE_TYPE,
769 decode(upper(h.conversion_type_code),
770 'USER', h.conversion_rate,null) RATE1,
771 g_global_currency TO_CURRENCY2,
772 gsb1.currency_code TO_CURRENCY3,
773 g_sec_global_currency TO_CURRENCY4,
774 to_number(hoi.org_information3) INV_OU_ID,
775 ospa.parameter_value MASTER_ORG_ID,
776 l.inventory_item_id INVENTORY_ITEM_ID,
777 nvl(l.ship_from_org_id, ospa.parameter_value) ITEM_INV_ORG_ID,
778 trunc(nvl(l.order_firmed_date, h.booked_date)) TIME_BOOKED_DATE_ID,
779 trunc(l.actual_shipment_date) TIME_SHIPPED_DATE_ID,
780 trunc(nvl(l.actual_fulfillment_date, l.fulfillment_date)) TIME_FULFILLED_DATE_ID,
781 trunc(l.schedule_ship_date) TIME_SCHEDULE_DATE_ID,
782 l.top_model_line_id TOP_MODEL_LINE_ID,
783 l.order_quantity_uom ORDER_QUANTITY_UOM,
784 item.primary_uom_code INV_UOM_CODE,
785 decode(l.order_quantity_uom, item.primary_uom_code,1,
786 INV_CONVERT.inv_um_convert(
787 l.inventory_item_id,NULL,1,
788 l.order_quantity_uom,
789 item.primary_uom_code,
790 NULL, NULL)) INV_UOM_RATE,
791 h.order_number ORDER_NUMBER,
792 h.header_id HEADER_ID,
793 l.line_number ||'.'||
794 l.shipment_number||decode(l.service_number,'',
795 decode(l.component_number,'',
796 decode(l.option_number,'','','.'),'.'),'.') ||
797 l.option_number||decode(l.service_number,'',
798 decode(l.component_number,'','','.'),'.') ||
799 l.component_number||decode(l.service_number,'','','.')||
800 l.service_number LINE_NUMBER,
801 l.service_reference_type_code SERVICE_REFERENCE_TYPE_CODE,
802 l.sold_to_org_id SOLD_TO_ORG_ID,
803 l.service_reference_line_id SERVICE_REFERENCE_LINE_ID,
804 nvl(opa.charge_operand,0) + nvl(opa.charge_adjamt,0)*l.ordered_quantity FREIGHT_CHARGE,
805 opa.cost FREIGHT_COST
806 FROM (select p.line_id, sum(decode(p.list_line_type_code, 'COST',
807 p.adjusted_amount, null)) cost,
808 sum(decode(p.list_line_type_code, 'FREIGHT_CHARGE', decode(nvl(p.applied_flag, 'Y'), 'Y', decode(p.arithmetic_operator, 'LUMPSUM', p.operand, null), null), null)) charge_operand,
809 sum(decode(p.list_line_type_code, 'FREIGHT_CHARGE', decode(nvl(p.applied_flag, 'Y'), 'Y', decode(p.arithmetic_operator, 'LUMPSUM', null, p.adjusted_amount), null), null)) charge_adjamt
810 from oe_price_adjustments p,
811 (select /*+ no_merge cardinality (log, 1000)*/ distinct line_id from isc_dbi_change_log log) log1
812 where p.line_id = log1.line_id
813 and p.line_id is not null
814 and p.charge_type_code in ('FTEPRICE', 'FTECHARGE')
815 group by p.line_id) opa,
816 (select /*+ no_merge cardinality (ilog, 1000)*/ distinct line_id, header_id from isc_dbi_change_log ilog) log,
817 oe_order_lines_all l,
818 OE_ORDER_HEADERS_ALL h,
819 AR_SYSTEM_PARAMETERS_ALL aspa,
820 GL_SETS_OF_BOOKS gsb,
821 HR_ORGANIZATION_INFORMATION hoi,
822 OE_SYS_PARAMETERS_ALL ospa,
823 GL_SETS_OF_BOOKS gsb1,
824 MTL_SYSTEM_ITEMS_B item
825 WHERE log.line_id = l.line_id
826 -- AND log.last_update_date < g_incre_start_date
827 AND l.header_id = h.header_id
828 AND l.line_id = opa.line_id (+)
829 AND h.org_id = aspa.org_id
830 AND aspa.set_of_books_id = gsb.set_of_books_id
831 AND h.booked_flag = 'Y'
832 AND h.booked_date IS NOT NULL
833 AND nvl(l.order_firmed_date, h.booked_date) >= g_global_start_date
834 AND hoi.org_information_context ='Accounting Information'
835 AND h.org_id = ospa.org_id
836 AND ospa.parameter_code = 'MASTER_ORGANIZATION_ID'
837 AND hoi.organization_id = nvl(l.ship_from_org_id, ospa.parameter_value)
838 AND hoi.org_information1 = to_char(gsb1.set_of_books_id)
839 AND l.inventory_item_id = item.inventory_item_id
840 AND nvl(l.ship_from_org_id, ospa.parameter_value) = item.organization_id;
841
842 FII_UTIL.Stop_Timer;
843 FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' lines in');
844 COMMIT;
845
846
847
848 -- In DBI5.0, we determine if a PTO/KIT top model is shippable by scanning through all it's child lines
849 -- Therefore, in the incremental load, we need to capture the top model line if the child line is changed
850 -- For DBI6.0, we do not support this logic anymore, so comment it out to improve performance
851
852 -- FII_UTIL.Start_Timer;
853
854 -- INSERT
855 -- INTO isc_tmp_book_sum2(
856 -- PK1,
857 -- VIEW_TYPE,
858 -- LOG_ROWID,
859 -- BATCH_ID,
860 -- CURR_CONV_DATE,
861 -- FROM_CURRENCY,
862 -- TO_CURRENCY1,
863 -- RATE_TYPE,
864 -- RATE1,
865 -- TO_CURRENCY2,
866 -- TO_CURRENCY3,
867 -- INV_OU_ID,
868 -- MASTER_ORG_ID,
869 -- INVENTORY_ITEM_ID,
870 -- ITEM_INV_ORG_ID,
871 -- TIME_BOOKED_DATE_ID,
872 -- TIME_SHIPPED_DATE_ID,
873 -- TIME_FULFILLED_DATE_ID,
874 -- TIME_SCHEDULE_DATE_ID,
875 -- TOP_MODEL_LINE_ID,
876 -- ORDER_QUANTITY_UOM,
877 -- INV_UOM_CODE,
878 -- INV_UOM_RATE,
879 -- ORDER_NUMBER,
880 -- HEADER_ID,
881 -- LINE_NUMBER)
882 -- SELECT /*+ leading(log) */ pl.line_id,
883 -- decode(pl.top_model_line_id,
884 -- null, 0,
885 -- decode(pl.ato_line_id,
886 -- null, decode(pl.item_type_code,
887 -- 'MODEL', 3, 'KIT', 3, 4),1)) VIEW_TYPE,
888 -- null,
889 -- null,
890 -- decode(upper(h.conversion_type_code),
891 -- 'USER', h.conversion_rate_date,
892 -- h.booked_date) CURR_CONV_DATE,
893 -- h.transactional_curr_code FROM_CURRENCY,
894 -- gsb.currency_code TO_CURRENCY1,
895 -- nvl(h.conversion_type_code,
896 -- g_global_rate_type) RATE_TYPE,
897 -- decode(upper(h.conversion_type_code),
898 -- 'USER', h.conversion_rate,null) RATE1,
899 -- g_global_currency TO_CURRENCY2,
900 -- gsb1.currency_code TO_CURRENCY3,
901 -- to_number(hoi.org_information3) INV_OU_ID,
902 -- ospa.master_organization_id MASTER_ORG_ID,
903 -- pl.inventory_item_id INVENTORY_ITEM_ID,
904 -- nvl(pl.ship_from_org_id, ospa.master_organization_id) ITEM_INV_ORG_ID,
905 -- trunc(h.booked_date) TIME_BOOKED_DATE_ID,
906 -- trunc(pl.actual_shipment_date) TIME_SHIPPED_DATE_ID,
907 -- trunc(pl.fulfillment_date) TIME_FULFILLED_DATE_ID,
908 -- trunc(pl.schedule_ship_date) TIME_SCHEDULE_DATE_ID,
909 -- pl.top_model_line_id TOP_MODEL_LINE_ID,
910 -- pl.order_quantity_uom ORDER_QUANTITY_UOM,
911 -- item.primary_uom_code INV_UOM_CODE,
912 -- decode(pl.order_quantity_uom, item.primary_uom_code,1,
913 -- INV_CONVERT.inv_um_convert(
914 -- pl.inventory_item_id,NULL,1,
915 -- pl.order_quantity_uom,
916 -- item.primary_uom_code,
917 -- NULL, NULL)) INV_UOM_RATE,
918 -- h.order_number ORDER_NUMBER,
919 -- h.header_id HEADER_ID,
920 -- pl.line_number ||'.'||
921 -- pl.shipment_number||decode(pl.service_number,'',
922 -- decode(pl.component_number,'',
923 -- decode(pl.option_number,'','','.'),'.'),'.') ||
924 -- pl.option_number||decode(pl.service_number,'',
925 -- decode(pl.component_number,'','','.'),'.') ||
926 -- pl.component_number||decode(pl.service_number,'','','.')||
927 -- pl.service_number LINE_NUMBER
928 -- FROM (SELECT distinct top_model_line_id FROM isc_tmp_book_sum2) log,
929 -- oe_order_lines_all pl,
930 -- OE_ORDER_HEADERS_ALL h,
931 -- AR_SYSTEM_PARAMETERS_ALL aspa,
932 -- GL_SETS_OF_BOOKS gsb,
933 -- HR_ORGANIZATION_INFORMATION hoi,
934 -- OE_SYSTEM_PARAMETERS_ALL ospa,
935 -- GL_SETS_OF_BOOKS gsb1,
936 -- MTL_SYSTEM_ITEMS_B item
937 -- WHERE log.top_model_line_id = pl.top_model_line_id
938 -- AND not exists (select '1' from isc_tmp_book_sum2 tmp where tmp.pk1 = pl.line_id)
939 -- AND pl.header_id = h.header_id
940 -- AND h.org_id = aspa.org_id
941 -- AND aspa.set_of_books_id = gsb.set_of_books_id
942 -- AND h.booked_flag = 'Y'
943 -- AND h.booked_date IS NOT NULL
944 -- AND hoi.org_information_context ='Accounting Information'
945 -- AND h.org_id = ospa.org_id
946 -- AND hoi.organization_id = nvl(pl.ship_from_org_id, ospa.master_organization_id)
947 -- AND hoi.org_information1 = to_char(gsb1.set_of_books_id)
948 -- AND pl.inventory_item_id = item.inventory_item_id
949 -- AND nvl(pl.ship_from_org_id, ospa.master_organization_id) = item.organization_id;
950
951 -- FII_UTIL.Stop_Timer;
952 -- FII_UTIL.Print_Timer('Identified '|| sql%rowcount || ' top model lines in');
953 -- COMMIT;
954
955 FII_UTIL.Start_Timer;
956
957 INSERT
958 INTO isc_curr_book_sum2 F(
959 FROM_CURRENCY,
960 TO_CURRENCY1,
961 TO_CURRENCY3,
962 CONVERSION_DATE,
963 CONVERSION_TYPE,
964 RATE1,
965 RATE2,
966 RATE3,
967 RATE4)
968 SELECT from_currency, to_currency1, to_currency3, time_booked_date_id CONVERSION_DATE, rate_type CONVERSION_TYPE,
969 decode(from_currency, to_currency1, 1,
970 fii_currency.get_rate(from_currency, to_currency1, time_booked_date_id, rate_type)) RATE1,
971 decode(from_currency, g_global_currency, 1,
972 fii_currency.get_global_rate_primary(to_currency3, time_booked_date_id)) RATE2,
973 decode(from_currency, to_currency3, 1,
974 fii_currency.get_rate(from_currency, to_currency3, time_booked_date_id, g_global_rate_type)) RATE3,
975 decode(from_currency, g_sec_global_currency, 1,
976 fii_currency.get_global_rate_secondary(to_currency3, time_booked_date_id)) RATE4
977 FROM (SELECT distinct from_currency, to_currency1, to_currency3, time_booked_date_id, rate_type
978 FROM isc_tmp_book_sum2);
979
980 FII_UTIL.Stop_Timer;
981 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' currency rates in');
982 COMMIT;
983
984
985
986 FII_UTIL.Start_Timer;
987
988 INSERT
989 INTO isc_service_book_sum2 F(
990 LINE_ID,
991 SERVICE_PARENT_LINE_ID)
992 SELECT pk1, ISC_DBI_BOOK_SUM2_F_C.get_cust_product_line_id(tmp.sold_to_org_id,tmp.service_reference_line_id)
993 FROM isc_tmp_book_sum2 tmp
994 WHERE service_reference_type_code = 'CUSTOMER_PRODUCT';
995
996 FII_UTIL.Stop_Timer;
997 FII_UTIL.Print_Timer('Retrieved '||sql%rowcount||' customer product line in');
998 COMMIT;
999
1000
1001
1002
1003
1004 FII_UTIL.Start_Timer;
1005
1006
1007
1008
1009 UPDATE isc_tmp_book_sum2 SET batch_id = ceil(rownum/g_batch_size);
1010 l_count := sql%rowcount;
1011 COMMIT;
1012
1013
1014
1015 FII_UTIL.Stop_Timer;
1016 FII_UTIL.Print_Timer('Updated the batch id for '|| l_count || ' rows in');
1017
1018 RETURN(l_count);
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 g_errbuf := 'Error in Function IDENTIFY_CHANGE_ICRL : '||sqlerrm;
1023 g_retcode := sqlcode;
1024 RETURN(-1);
1025 END;
1026
1027 -- ---------------------
1028 -- CHECK_TIME_CONTINUITY
1029 -- ---------------------
1030
1031 FUNCTION CHECK_TIME_CONTINUITY RETURN NUMBER IS
1032
1033 l_min_booked_date DATE;
1034 l_max_booked_date DATE;
1035 l_min_shipped_date DATE;
1036 l_max_shipped_date DATE;
1037 l_min_ful_date DATE;
1038 l_max_ful_date DATE;
1039 l_min_sche_date DATE;
1040 l_max_sche_date DATE;
1041 l_min_1 DATE;
1042 l_max_1 DATE;
1043 l_min_2 DATE;
1044 l_max_2 DATE;
1045 l_is_missing BOOLEAN := TRUE;
1046 l_time_min DATE;
1047 l_time_max DATE;
1048 l_profile_option VARCHAR2(100);
1049 l_dangling NUMBER := 0;
1050
1051 -- cursor
1052
1053 CURSOR Lines_Missing_Date_1 IS
1054 SELECT order_number,
1055 line_number,
1056 header_id,
1057 pk1 line_id,
1058 to_char(time_booked_date_id, 'MM/DD/YYYY') time_booked_date_id,
1059 to_char(time_fulfilled_date_id, 'MM/DD/YYYY') time_fulfilled_date_id,
1060 to_char(time_shipped_date_id,'MM/DD/YYYY') time_shipped_date_id
1061 FROM isc_tmp_book_sum2
1062 WHERE (least(time_booked_date_id, nvl(time_fulfilled_date_id,time_booked_date_id), nvl(time_shipped_date_id,time_booked_date_id)) < l_time_min
1063 OR greatest(time_booked_date_id, nvl(time_fulfilled_date_id,time_booked_date_id),nvl(time_shipped_date_id,time_booked_date_id)) > l_time_max);
1064
1065 CURSOR Lines_Missing_Date_2 IS
1066 SELECT order_number,
1067 line_number,
1068 header_id,
1069 pk1 line_id,
1070 to_char(time_schedule_date_id,'MM/DD/YYYY') time_schedule_date_id
1071 FROM isc_tmp_book_sum2
1072 WHERE (nvl(time_schedule_date_id, time_booked_date_id) < l_time_min
1073 OR nvl(time_schedule_date_id, time_booked_date_id) > l_time_max);
1074
1075 l_line_1 LINES_MISSING_DATE_1%ROWTYPE;
1076 l_line_2 LINES_MISSING_DATE_2%ROWTYPE;
1077
1078 BEGIN
1079
1080 FII_UTIL.Start_Timer;
1081
1082 IF (g_load_mode = 'INITIAL') THEN
1083
1084 BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the initial load');
1085 SELECT /*+ PARALLEL(tmp) */ min(time_booked_date_id), max(time_booked_date_id),
1086 min(time_shipped_date_id), max(time_shipped_date_id),
1087 min(time_fulfilled_date_id), max(time_fulfilled_date_id),
1088 min(time_schedule_date_id), max(time_schedule_date_id)
1089 INTO l_min_booked_date, l_max_booked_date,
1090 l_min_shipped_date, l_max_shipped_date,
1091 l_min_ful_date, l_max_ful_date,
1092 l_min_sche_date, l_max_sche_date
1093 FROM isc_tmp_book_sum2 tmp;
1094
1095 ELSIF (g_load_mode = 'INCREMENTAL') THEN
1096
1097 BIS_COLLECTION_UTILITIES.Put_Line('Begin to retrieve the time boundary for the incremental load');
1098 SELECT min(time_booked_date_id), max(time_booked_date_id),
1099 min(time_shipped_date_id), max(time_shipped_date_id),
1100 min(time_fulfilled_date_id), max(time_fulfilled_date_id),
1101 min(time_schedule_date_id), max(time_schedule_date_id)
1102 INTO l_min_booked_date, l_max_booked_date,
1103 l_min_shipped_date, l_max_shipped_date,
1104 l_min_ful_date, l_max_ful_date,
1105 l_min_sche_date, l_max_sche_date
1106 FROM isc_tmp_book_sum2 tmp;
1107
1108 END IF;
1109
1110 l_min_1 := least(l_min_booked_date, nvl(l_min_shipped_date,l_min_booked_date), nvl(l_min_ful_date,l_min_booked_date));
1111 l_max_1 := greatest(l_max_booked_date, nvl(l_max_shipped_date,l_max_booked_date), nvl(l_max_ful_date, l_max_booked_date));
1112 l_min_2 := nvl(l_min_sche_date, l_min_booked_date);
1113 l_max_2 := nvl(l_max_sche_date, l_max_booked_date);
1114
1115 FII_UTIL.Stop_Timer;
1116 FII_UTIL.Print_Timer('Retrieved the time boundary in ');
1117
1118
1119 FII_UTIL.Start_Timer;
1120
1121 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1122 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1123 FII_TIME_API.check_missing_date(l_min_1, l_max_1, l_is_missing);
1124
1125
1126 IF (l_is_missing) THEN
1127 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1128 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1129
1130 SELECT min(report_date), max(report_date)
1131 INTO l_time_min, l_time_max
1132 FROM fii_time_day;
1133
1134 OPEN lines_missing_date_1;
1135 FETCH lines_missing_date_1 INTO l_line_1;
1136 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1137 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1138 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_ORDER_NUMBER'),18,' ')
1139 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_NUMBER'),18,' ')
1140 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_ID'),18,' ')
1141 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_BOOKED_DATE'),15,' ')
1142 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_FULFILLED_DATE'),19,' ')
1143 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_SHIPPED_DATE'),16,' '));
1144 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------ - --------------- - ------------------- - ----------------');
1145
1146 WHILE LINES_MISSING_DATE_1%FOUND LOOP
1147 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line_1.order_number,18,' ')
1148 ||' - '||RPAD(l_line_1.line_number,18,' ')
1149 ||' - '||RPAD(l_line_1.line_id,18,' ')
1150 ||' - '||RPAD(l_line_1.time_booked_date_id,15,' ')
1151 ||' - '||RPAD(nvl(l_line_1.time_fulfilled_date_id,' '),19,' ')
1152 ||' - '||RPAD(nvl(l_line_1.time_shipped_date_id,' '),16,' '));
1153 FETCH Lines_Missing_Date_1 INTO l_line_1;
1154 END LOOP;
1155 CLOSE LINES_MISSING_DATE_1;
1156 BIS_COLLECTION_UTILITIES.Put_Line_Out('+---------------------------------------------------------------------------------------------------------------------+');
1157 l_dangling := 1;
1158
1159 END IF;
1160
1161 l_profile_option := nvl(fnd_profile.value('ISC_DBI_SCH_SHP_DATE_DNGL_CHK'),'ERROR');
1162
1163 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1164 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1165 FII_TIME_API.check_missing_date(l_min_2, l_max_2, l_is_missing);
1166
1167
1168 IF (l_is_missing) THEN
1169
1170 if (l_profile_option = 'ERROR') then
1171 if (l_dangling = 0) then
1172 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1173 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1174
1175 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1176 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1177 l_dangling := 1;
1178 end if;
1179 else
1180 g_warning := 1;
1181 end if;
1182
1183 SELECT min(report_date), max(report_date)
1184 INTO l_time_min, l_time_max
1185 FROM fii_time_day;
1186
1187 OPEN lines_missing_date_2;
1188 FETCH lines_missing_date_2 INTO l_line_2;
1189 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_ORDER_NUMBER'),18,' ')
1190 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_NUMBER'),18,' ')
1191 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_ID'),18,' ')
1192 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_SCHEDULE_SHIP_DATE'),24,' '));
1193 BIS_COLLECTION_UTILITIES.Put_Line_Out('------------------ - ------------------ - ------------------ - ------------------------');
1194
1195 WHILE LINES_MISSING_DATE_2%FOUND LOOP
1196 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_line_2.order_number,18,' ')
1197 ||' - '||RPAD(l_line_2.line_number,18,' ')
1198 ||' - '||RPAD(l_line_2.line_id,18,' ')
1199 ||' - '||RPAD(l_line_2.time_schedule_date_id,24,' '));
1200 FETCH Lines_Missing_Date_2 INTO l_line_2;
1201 END LOOP;
1202 CLOSE LINES_MISSING_DATE_2;
1203 BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------------------------------------------------------+');
1204
1205
1206 END IF;
1207
1208 if (l_dangling = 1) then
1209 return (-999);
1210 elsif (g_warning = 1) then
1211 return (1);
1212 else
1213 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1214 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1215 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1216 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1217
1218 FII_UTIL.Stop_Timer;
1219 FII_UTIL.Print_Timer('Completed time continuity check in');
1220
1221 RETURN(1);
1222 end if;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY : '||sqlerrm;
1227 g_retcode := sqlcode;
1228 RETURN(-1);
1229 END;
1230
1231 -- ----------------------------------------
1232 -- Identify Dangling Key for Item Dimension
1233 -- ----------------------------------------
1234
1235 FUNCTION IDENTIFY_DANGLING_ITEM RETURN NUMBER IS
1236
1237 CURSOR Dangling_Items_Init IS
1238 SELECT /*+ PARALLEL(tmp) PARALLEL(item) */ distinct tmp.inventory_item_id, tmp.item_inv_org_id
1239 FROM isc_tmp_book_sum2 tmp,
1240 eni_oltp_item_star item
1241 WHERE tmp.inventory_item_id = item.inventory_item_id(+)
1242 AND tmp.item_inv_org_id = item.organization_id(+)
1243 AND item.organization_id IS NULL;
1244
1245 CURSOR Dangling_Items_Incre IS
1246 SELECT distinct tmp.inventory_item_id, tmp.item_inv_org_id
1247 FROM isc_tmp_book_sum2 tmp,
1248 eni_oltp_item_star item
1249 WHERE tmp.inventory_item_id = item.inventory_item_id(+)
1250 AND tmp.item_inv_org_id = item.organization_id(+)
1251 AND item.organization_id IS NULL;
1252
1253 l_item NUMBER;
1254 l_org NUMBER;
1255 l_total NUMBER;
1256
1257 BEGIN
1258
1259 l_total := 0;
1260
1261 IF (g_load_mode = 'INITIAL') THEN
1262 OPEN dangling_items_init;
1263 FETCH dangling_items_init INTO l_item, l_org;
1264
1265 IF dangling_items_init%ROWCOUNT <> 0 THEN
1266 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1267 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1268
1269 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1270 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1271 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1272 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1273 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1274 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1275
1276 WHILE Dangling_Items_Init%FOUND LOOP
1277 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1278 FETCH Dangling_Items_Init INTO l_item, l_org;
1279 END LOOP;
1280 BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1281 ELSE
1282 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1283 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1284 BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1285 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1286 END IF;
1287 l_total := Dangling_Items_Init%ROWCOUNT;
1288 CLOSE Dangling_Items_Init;
1289
1290 ELSIF (g_load_mode = 'INCREMENTAL') THEN
1291 OPEN dangling_items_incre;
1292 FETCH dangling_items_incre INTO l_item, l_org;
1293
1294 IF dangling_items_incre%ROWCOUNT <> 0 THEN
1295 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1296 BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1297
1298 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1299 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1300 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1301 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1302 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_INV_ITEM_ID'),23,' ')||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_ORG_ID'),20,' '));
1303 BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1304
1305 WHILE Dangling_Items_Incre%FOUND LOOP
1306 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,23,' ')||' - '||RPAD(l_org,20,' '));
1307 FETCH Dangling_Items_Incre INTO l_item, l_org;
1308 END LOOP;
1309 BIS_COLLECTION_UTILITIES.Put_Line_Out('+--------------------------------------------+');
1310 ELSE
1311 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1312 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1313 BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1314 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1315 END IF;
1316 l_total := Dangling_Items_Incre%ROWCOUNT;
1317 CLOSE Dangling_Items_Incre;
1318 END IF;
1319
1320 RETURN(l_total);
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 g_errbuf := 'Error in Function IDENTIFY_DANGLING_ITEM : '||sqlerrm;
1325 g_retcode := sqlcode;
1326 RETURN(-1);
1327 END;
1328
1329 -- -----------------------------------
1330 -- Reporting of the missing currencies
1331 -- -----------------------------------
1332
1333 FUNCTION REPORT_MISSING_RATE RETURN NUMBER IS
1334
1335 l_sec_curr_def VARCHAR2(1);
1336
1337
1338
1339
1340 CURSOR Missing_Currency_Conversion IS
1341 SELECT distinct decode(rate1, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1342 from_currency,
1343 to_currency1 TO_CURRENCY,
1344 conversion_type RATE_TYPE,
1345 decode(rate1, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1346 FROM isc_curr_book_sum2 tmp
1347 WHERE rate1 < 0
1348 AND upper(conversion_type) <> 'USER'
1349 UNION
1350 SELECT distinct decode(rate2, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1351 to_currency3 FROM_CURRENCY,
1352 g_global_currency TO_CURRENCY,
1353 g_global_rate_type RATE_TYPE,
1354 decode(rate2, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1355 FROM isc_curr_book_sum2 tmp
1356 WHERE rate2 < 0
1357 UNION
1358 SELECT distinct decode(rate3, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1359 from_currency,
1360 to_currency3,
1361 g_global_rate_type RATE_TYPE,
1362 decode(rate3, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1363 FROM isc_curr_book_sum2 tmp
1364 WHERE rate3 < 0
1365 UNION
1366 SELECT distinct decode(rate4, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
1367 to_currency3 FROM_CURRENCY,
1368 g_sec_global_currency TO_CURRENCY,
1369 g_sec_global_rate_type RATE_TYPE,
1370 decode(rate4, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
1371 FROM isc_curr_book_sum2 tmp
1372 WHERE rate4 < 0
1373 AND l_sec_curr_def = 'Y';
1374
1375 l_record Missing_Currency_Conversion%ROWTYPE;
1376 l_total NUMBER := 0;
1377
1378 BEGIN
1379
1380 l_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
1381
1382
1383 OPEN Missing_Currency_Conversion;
1384 FETCH Missing_Currency_Conversion INTO l_record;
1385
1386 IF Missing_Currency_Conversion%ROWCOUNT <> 0
1387 THEN
1388 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1389 BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1390
1391 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1392 WHILE Missing_Currency_Conversion%FOUND LOOP
1393 l_total := l_total + 1;
1394 BIS_COLLECTION_UTILITIES.writeMissingRate(
1395 l_record.rate_type,
1396 l_record.from_currency,
1397 l_record.to_currency,
1398 l_record.curr_conv_date);
1399 FETCH Missing_Currency_Conversion INTO l_record;
1400 END LOOP;
1401 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1402 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1403
1404 ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1405 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1406 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1407 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1408 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1409 END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1410
1411 CLOSE Missing_Currency_Conversion;
1412
1413
1414
1415 RETURN(l_total);
1416
1417 EXCEPTION
1418 WHEN OTHERS THEN
1419
1420 g_errbuf := 'Error in Function REPORT_MISSING_RATE : '||sqlerrm;
1421 g_retcode := sqlcode;
1422 RETURN(-1);
1423 END;
1424
1425 -- ---------------------------------------------
1426 -- Reporting of the Missing UOM Conversion Rates
1427 -- ---------------------------------------------
1428
1429 FUNCTION REPORT_MISSING_UOM_RATE RETURN NUMBER IS
1430
1431 CURSOR Missing_UOM_Conversion IS
1432 SELECT distinct inventory_item_id,
1433 order_quantity_uom from_unit,
1434 inv_uom_code to_unit
1435 FROM ISC_TMP_BOOK_SUM2
1436 WHERE inv_uom_rate = -99999;
1437
1438 CURSOR Missing_Transaction_UOM IS
1439 SELECT order_number,
1440 line_number,
1441 header_id,
1442 pk1 line_id
1443 FROM ISC_TMP_BOOK_SUM2
1444 WHERE order_quantity_uom IS NULL;
1445
1446 l_record Missing_UOM_Conversion%ROWTYPE;
1447 l_uom_record Missing_Transaction_UOM%ROWTYPE;
1448 l_total NUMBER := 0;
1449
1450 BEGIN
1451
1452 OPEN Missing_UOM_Conversion;
1453 FETCH Missing_UOM_Conversion INTO l_record;
1454
1455 IF Missing_UOM_Conversion%ROWCOUNT <> 0 THEN
1456 BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing UOM conversion rates.');
1457 BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_UOM_NO_LOAD'));
1458
1459 BIS_COLLECTION_UTILITIES.writeMissingUOMHeader;
1460 WHILE Missing_UOM_Conversion%FOUND LOOP
1461 l_total := l_total + 1;
1462
1463 BIS_COLLECTION_UTILITIES.writeMissingUOM(
1464 nvl(l_record.from_unit,' '),
1465 nvl(l_record.to_unit,' '),
1466 l_record.inventory_item_id);
1467
1468 FETCH Missing_UOM_Conversion INTO l_record;
1469 END LOOP;
1470
1471 OPEN Missing_Transaction_UOM;
1472 FETCH Missing_Transaction_UOM INTO l_uom_record;
1473
1474 IF Missing_Transaction_UOM%ROWCOUNT <> 0
1475 THEN
1476
1477 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1478 BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_UOM_NO_LOAD'));
1479 BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1480 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(fnd_message.get_string('ISC','ISC_DBI_ORDER_NUMBER'),16,' ')
1481 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_NUMBER'),18,' ')
1482 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_HEADER_ID'),17,' ')
1483 ||' - '||RPAD(fnd_message.get_string('ISC','ISC_DBI_LINE_ID'),17,' '));
1484
1485 BIS_COLLECTION_UTILITIES.Put_Line_Out('---------------- - ------------------ - ----------------- - -----------------');
1486
1487 WHILE Missing_Transaction_UOM%FOUND LOOP
1488 BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_uom_record.order_number,16,' ')
1489 ||' - '||RPAD(l_uom_record.line_number,18,' ')
1490 ||' - '||RPAD(l_uom_record.header_id,17,' ')
1491 ||' - '||RPAD(l_uom_record.line_id,17,' '));
1492
1493 FETCH Missing_Transaction_UOM INTO l_uom_record;
1494 END LOOP;
1495 BIS_COLLECTION_UTILITIES.Put_Line_Out('+---------------------------------------------------------------------------+');
1496 END IF;
1497
1498 CLOSE Missing_Transaction_UOM;
1499
1500 ELSE -- Missing_UOM_Conversion%ROWCOUNT = 0
1501 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1502 BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING UOM CONVERSION RATE ');
1503 BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1504 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1505 END IF; -- Missing_UOM_Conversion%ROWCOUNT <> 0
1506
1507 CLOSE Missing_UOM_Conversion;
1508
1509 RETURN(l_total);
1510
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 g_errbuf := 'Error in Function REPORT_MISSING_UOM_RATE : '||sqlerrm;
1514 g_retcode := sqlcode;
1515 RETURN(-1);
1516 END;
1517
1518 -- --------------
1519 -- DANGLING_CHECK
1520 -- --------------
1521
1522 FUNCTION DANGLING_CHECK RETURN NUMBER IS
1523
1524 l_time_dangling NUMBER := 0;
1525 l_item_count NUMBER := 0;
1526 l_miss_conv NUMBER := 0;
1527 l_miss_uom NUMBER := 0;
1528 l_dangling NUMBER := 0;
1529
1530 BEGIN
1531
1532
1533 -- ----------------------------------------------------------
1534 -- Identify Missing Currency Rate from ISC_TMP_BOOK_SUM2
1535 -- When there is missing rate, exit the collection with error
1536 -- ----------------------------------------------------------
1537
1538 BIS_COLLECTION_UTILITIES.put_line(' ');
1539 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1540 FII_UTIL.Start_Timer;
1541
1542
1543 l_miss_conv := REPORT_MISSING_RATE;
1544
1545 FII_UTIL.Stop_Timer;
1546 FII_UTIL.Print_Timer('Completed missing currency check in');
1547
1548 IF (l_miss_conv = -1) THEN
1549 return(-1);
1550 ELSIF (l_miss_conv > 0) THEN
1551 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
1552 l_dangling := -999;
1553 END IF;
1554
1555
1556 -- --------------------------------------------------------------
1557 -- Identify Missing UOM Rate from ISC_TMP_BOOK_SUM2
1558 -- When there is missing UOM rate, exit the collection with error
1559 -- --------------------------------------------------------------
1560
1561 BIS_COLLECTION_UTILITIES.put_line(' ');
1562 BIS_COLLECTION_UTILITIES.put_line('Identifying the missing UOM conversion rates');
1563 FII_UTIL.Start_Timer;
1564
1565 l_miss_uom := REPORT_MISSING_UOM_RATE;
1566
1567 FII_UTIL.Stop_Timer;
1568 FII_UTIL.Print_Timer('Completed missing UOM check in');
1569
1570 IF (l_miss_uom = -1) THEN
1571 return(-1);
1572 ELSIF (l_miss_uom > 0) THEN
1573 g_errbuf := g_errbuf || 'Collection aborted due to missing UOM conversion rates. ';
1574 l_dangling := -999;
1575 END IF;
1576
1577
1578
1579 -- ---------------------
1580 -- CHECK_TIME_CONTINUITY
1581 -- ---------------------
1582
1583 BIS_COLLECTION_UTILITIES.Put_Line(' ');
1584 BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1585
1586 l_time_dangling := check_time_continuity;
1587
1588 IF (l_time_dangling = -1) THEN
1589 return(-1);
1590 ELSIF (l_time_dangling = -999) THEN
1591 g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
1592 l_dangling := -999;
1593 END IF;
1594
1595
1596
1597 -- -------------------------------------
1598 -- Check Dangling Key for Item Dimension
1599 -- -------------------------------------
1600
1601 BIS_COLLECTION_UTILITIES.put_line(' ');
1602 BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1603
1604 FII_UTIL.Start_Timer;
1605
1606 l_item_count := IDENTIFY_DANGLING_ITEM;
1607
1608 FII_UTIL.Stop_Timer;
1609 FII_UTIL.Print_Timer('Identified '||l_item_count||' dangling items in');
1610
1611 IF (l_item_count = -1)
1612 THEN return(-1);
1613 ELSIF (l_item_count > 0) THEN
1614 g_errbuf := g_errbuf || 'Collection aborted due to dangling items. ';
1615 l_dangling := -999;
1616 END IF;
1617
1618
1619 IF (l_dangling = -999) THEN
1620 return(-1);
1621 END IF;
1622
1623 RETURN(1);
1624
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 g_errbuf := 'Error in Function DANGLING_CHECK : '||sqlerrm;
1628 g_retcode := sqlcode;
1629 RETURN(-1);
1630
1631 END dangling_check;
1632
1633
1634 -- -----------
1635 -- INSERT_FACT
1636 -- -----------
1637
1638 FUNCTION INSERT_FACT RETURN NUMBER IS
1639
1640 l_total NUMBER;
1641
1642 BEGIN
1643
1644 l_total := 0;
1645
1646
1647
1648 INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_BOOK_SUM2_F F
1649 (line_id,
1650 item_id,
1651 inv_org_id,
1652 inv_ou_id,
1653 org_ou_id,
1654 customer_id,
1655 sales_channel_id,
1656 return_reason_id,
1657 order_category_id,
1658 order_source_id,
1659 order_type_id,
1660 ship_to_org_id,
1661 sold_to_org_id,
1662 time_act_ship_date_id,
1663 time_booked_date_id,
1664 time_shipped_date_id,
1665 time_fulfilled_date_id,
1666 time_schedule_date_id,
1667 time_ordered_date_id,
1668 time_promise_date_id,
1669 time_request_date_id,
1670 currency_func_id,
1671 curr_wh_func_id,
1672 inventory_item_id,
1673 item_inv_org_id,
1674 top_model_item_id,
1675 top_model_org_id,
1676 actual_shipment_date,
1677 booked_date,
1678 shipped_date,
1679 fulfilled_date,
1680 schedule_ship_date,
1681 ordered_date,
1682 promise_date,
1683 request_date,
1684 ordered_quantity,
1685 header_id,
1686 h_marketing_source_code_id,
1687 invoice_to_org_id,
1688 marketing_source_code_id,
1689 open_flag,
1690 order_date_type_code,
1691 order_number,
1692 order_quantity_uom,
1693 shippable_flag,
1694 fulfilled_flag,
1695 line_category_code,
1696 line_item_type,
1697 line_number,
1698 item_type_code,
1699 ato_line_id,
1700 count_pdue_line,
1701 count_ship_line,
1702 flow_status_code,
1703 inv_uom_code,
1704 top_model_line_id,
1705 unit_list_price,
1706 unit_selling_price,
1707 service_parent_line_id,
1708 booked_amt_g,
1709 invoiced_amt_g,
1710 shipped_amt_g,
1711 fulfilled_amt_g,
1712 booked_list_amt_g,
1713 booked_amt_f,
1714 invoiced_amt_f,
1715 shipped_amt_f,
1716 fulfilled_amt_f,
1717 booked_list_amt_f,
1718 booked_amt_f1,
1719 invoiced_amt_f1,
1720 shipped_amt_f1,
1721 fulfilled_amt_f1,
1722 booked_list_amt_f1,
1723 booked_qty_inv,
1724 invoiced_qty_inv,
1725 shipped_qty_inv,
1726 fulfilled_qty_inv,
1727 created_by,
1728 last_update_login,
1729 creation_date,
1730 last_updated_by,
1731 last_update_date,
1732 ship_to_party_id,
1733 booked_amt_g1,
1734 invoiced_amt_g1,
1735 shipped_amt_g1,
1736 fulfilled_amt_g1,
1737 booked_list_amt_g1,
1738 freight_charge,
1739 freight_charge_f,
1740 freight_charge_g,
1741 freight_charge_g1,
1742 freight_cost,
1743 freight_cost_f,
1744 freight_cost_g,
1745 freight_cost_g1,
1746 charge_periodicity_code,
1747 blanket_number,
1748 blanket_line_number)
1749 SELECT /*+ PARALLEL(v)*/ v.line_id,
1750 v.item_id,
1751 v.inv_org_id,
1752 v.inv_ou_id,
1753 v.org_ou_id,
1754 v.customer_id,
1755 v.sales_channel_id,
1756 v.return_reason_id,
1757 v.order_category_id,
1758 v.order_source_id,
1759 v.order_type_id,
1760 v.ship_to_org_id,
1761 v.sold_to_org_id,
1762 v.time_act_ship_date_id,
1763 v.time_booked_date_id,
1764 v.time_shipped_date_id,
1765 v.time_fulfilled_date_id,
1766 v.time_schedule_date_id,
1767 v.time_ordered_date_id,
1768 v.time_promise_date_id,
1769 v.time_request_date_id,
1770 v.currency_func_id,
1771 v.curr_wh_func_id,
1772 v.inventory_item_id,
1773 v.item_inv_org_id,
1774 v.top_model_item_id,
1775 v.top_model_org_id,
1776 v.actual_shipment_date,
1777 v.booked_date,
1778 v.shipped_date,
1779 v.fulfilled_date,
1780 v.schedule_ship_date,
1781 v.ordered_date,
1782 v.promise_date,
1783 v.request_date,
1784 v.ordered_quantity,
1785 v.header_id,
1786 v.h_marketing_source_code_id,
1787 v.invoice_to_org_id,
1788 v.marketing_source_code_id,
1789 v.open_flag,
1790 v.order_date_type_code,
1791 v.order_number,
1792 v.order_quantity_uom,
1793 v.shippable_flag,
1794 v.fulfilled_flag,
1795 v.line_category_code,
1796 v.line_item_type,
1797 v.line_number,
1798 v.item_type_code,
1799 v.ato_line_id,
1800 v.count_pdue_line,
1801 v.count_ship_line,
1802 v.flow_status_code,
1803 v.inv_uom_code,
1804 v.top_model_line_id,
1805 v.unit_list_price,
1806 v.unit_selling_price,
1807 v.service_parent_line_id,
1808 v.booked_amt_g,
1809 v.invoiced_amt_g,
1810 v.shipped_amt_g,
1811 v.fulfilled_amt_g,
1812 v.booked_list_amt_g,
1813 v.booked_amt_f,
1814 v.invoiced_amt_f,
1815 v.shipped_amt_f,
1816 v.fulfilled_amt_f,
1817 v.booked_list_amt_f,
1818 v.booked_amt_f1,
1819 v.invoiced_amt_f1,
1820 v.shipped_amt_f1,
1821 v.fulfilled_amt_f1,
1822 v.booked_list_amt_f1,
1823 v.booked_qty_inv,
1824 v.invoiced_qty_inv,
1825 v.shipped_qty_inv,
1826 v.fulfilled_qty_inv,
1827 v.created_by,
1828 v.last_update_login,
1829 v.creation_date,
1830 v.last_updated_by,
1831 v.last_update_date,
1832 v.ship_to_party_id,
1833 v.booked_amt_g1,
1834 v.invoiced_amt_g1,
1835 v.shipped_amt_g1,
1836 v.fulfilled_amt_g1,
1837 v.booked_list_amt_g1,
1838 v.freight_charge,
1839 v.freight_charge_f,
1840 v.freight_charge_g,
1841 v.freight_charge_g1,
1842 v.freight_cost,
1843 v.freight_cost_f,
1844 v.freight_cost_g,
1845 v.freight_cost_g1,
1846 v.charge_periodicity_code,
1847 v.blanket_number,
1848 v.blanket_line_number
1849 FROM ISCBV_BOOK_SUM2_FCV v;
1850
1851 l_total := sql%rowcount;
1852 COMMIT;
1853
1854 RETURN(l_total);
1855
1856 EXCEPTION
1857 WHEN OTHERS THEN
1858 g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
1859 g_retcode := sqlcode;
1860 RETURN(-1);
1861
1862 END insert_fact;
1863
1864 -- -----------
1865 -- MERGE_FACT
1866 -- -----------
1867
1868 FUNCTION MERGE_FACT(p_batch number) RETURN NUMBER IS
1869
1870 l_count NUMBER;
1871 l_total NUMBER;
1872 l_max_batch NUMBER;
1873 l_date DATE;
1874
1875 BEGIN
1876
1877
1878 l_total := 0;
1879 l_date := to_date('01/01/0001','DD/MM/YYYY');
1880
1881 FOR v_batch_id IN 1..p_batch
1882 LOOP
1883 FII_UTIL.Start_Timer;
1884 BIS_COLLECTION_UTILITIES.put_line('Merging batch '||v_batch_id);
1885
1886 l_count := 0;
1887
1888
1889 MERGE INTO ISC_BOOK_SUM2_F f
1890 USING
1891 (select new.* from ISCBV_BOOK_SUM2_FCV new, ISC_BOOK_SUM2_F old
1892 where new.line_id = old.line_id(+)
1893 and new.batch_id = v_batch_id
1894 and (old.line_id is null
1895 or new.customer_id <> old.customer_id
1896 or new.item_inv_org_id <> old.item_inv_org_id
1897 or new.inv_ou_id <> old.inv_ou_id
1898 or new.fulfilled_flag <> old.fulfilled_flag
1899 or new.open_flag <> old.open_flag
1900 or new.sales_channel_id <> old.sales_channel_id
1901 or new.return_reason_id <> old.return_reason_id
1902 or new.order_source_id <> old.order_source_id
1903 or new.booked_amt_f <> old.booked_amt_f
1904 or new.booked_amt_f1 <> old.booked_amt_f1
1905 or new.booked_amt_g <> old.booked_amt_g
1906 or new.booked_amt_g1 <> old.booked_amt_g1
1907 or new.freight_charge <> old.freight_charge
1908 or new.freight_charge_f <> old.freight_charge_f
1909 or new.freight_charge_g <> old.freight_charge_g
1910 or new.freight_charge_g1 <> old.freight_charge_g1
1911 or new.freight_cost <> old.freight_cost
1912 or new.freight_cost_f <> old.freight_cost_f
1913 or new.freight_cost_g <> old.freight_cost_g
1914 or new.freight_cost_g1 <> old.freight_cost_g1
1915 or new.booked_qty_inv <> old.booked_qty_inv
1916 or new.fulfilled_amt_f <> old.fulfilled_amt_f
1917 or new.fulfilled_amt_f1 <> old.fulfilled_amt_f1
1918 or new.fulfilled_amt_g <> old.fulfilled_amt_g
1919 or new.fulfilled_amt_g1 <> old.fulfilled_amt_g1
1920 or new.fulfilled_qty_inv <> old.fulfilled_qty_inv
1921 or new.invoiced_amt_f <> old.invoiced_amt_f
1922 or new.invoiced_amt_f1 <> old.invoiced_amt_f1
1923 or new.invoiced_amt_g <> old.invoiced_amt_g
1924 or new.invoiced_amt_g1 <> old.invoiced_amt_g1
1925 or new.invoiced_qty_inv <> old.invoiced_qty_inv
1926 or new.shipped_amt_f <> old.shipped_amt_f
1927 or new.shipped_amt_f1 <> old.shipped_amt_f1
1928 or new.shipped_amt_g <> old.shipped_amt_g
1929 or new.shipped_amt_g1 <> old.shipped_amt_g1
1930 or new.shipped_qty_inv <> old.shipped_qty_inv
1931 or new.org_ou_id <> old.org_ou_id
1932 or new.booked_date <> old.booked_date
1933 or new.inventory_item_id <> old.inventory_item_id
1934 or new.order_number <> old.order_number
1935 or new.line_number <> old.line_number
1936 or new.line_category_code <> old.line_category_code
1937 or new.currency_func_id <> old.currency_func_id
1938 or new.curr_wh_func_id <> old.curr_wh_func_id
1939 or new.order_quantity_uom <> old.order_quantity_uom
1940 or new.inv_uom_code <> old.inv_uom_code
1941 or new.ship_to_party_id <> old.ship_to_party_id
1942 or nvl(new.actual_shipment_date,l_date) <> nvl(old.actual_shipment_date, l_date)
1943 or nvl(new.fulfilled_date, l_date) <> nvl(old.fulfilled_date, l_date)
1944 or nvl(new.promise_date, l_date) <> nvl(old.promise_date, l_date)
1945 or nvl(new.request_date, l_date) <> nvl(old.request_date, l_date)
1946 or nvl(new.schedule_ship_date, l_date) <> nvl(old.schedule_ship_date, l_date)
1947 or nvl(new.service_parent_line_id, -1) <> nvl(old.service_parent_line_id, -1)
1948 or nvl(new.invoice_to_org_id, -1) <> nvl(old.invoice_to_org_id, -1)
1949 or nvl(new.ordered_date, l_date) <> nvl(old.ordered_date, l_date)
1950 or nvl(new.ordered_quantity, 0) <> nvl(old.ordered_quantity, 0)
1951 or nvl(new.unit_selling_price, 0) <> nvl(old.unit_selling_price, 0)
1952 or nvl(new.blanket_number,0) <> nvl(old.blanket_number, 0)
1953 or nvl(new.blanket_line_number,0) <> nvl(old.blanket_line_number, 0)
1954 or nvl(new.charge_periodicity_code,'na') <> nvl(old.charge_periodicity_code,'na')
1955 or nvl(new.flow_status_code, 'na') <> nvl(old.flow_status_code, 'na')
1956 or nvl(new.h_marketing_source_code_id, -1) <> nvl(old.h_marketing_source_code_id, -1)
1957 or nvl(new.marketing_source_code_id, -1) <> nvl(old.marketing_source_code_id, -1)
1958 or nvl(new.item_type_code, 'na') <> nvl(old.item_type_code, 'na')
1959 or nvl(new.order_date_type_code, 'na') <> nvl(old.order_date_type_code, 'na')
1960 or nvl(new.shippable_flag, 'na') <> nvl(old.shippable_flag, 'na')
1961 or nvl(new.unit_list_price, 0) <> nvl(old.unit_list_price, 0)
1962 or nvl(new.order_type_id, -1) <> nvl(old.order_type_id, -1)
1963 or nvl(new.sold_to_org_id, -1) <> nvl(old.sold_to_org_id, -1)
1964 or nvl(new.ship_to_org_id, -1) <> nvl(old.ship_to_org_id, -1)
1965 or nvl(new.ato_line_id, -1) <> nvl(old.ato_line_id, -1)
1966 or nvl(new.top_model_line_id, -1) <> nvl(old.top_model_line_id, -1)
1967 or nvl(new.item_id, -1) <> nvl(old.item_id, -1)
1968 or nvl(new.inv_org_id, -1) <> nvl(old.inv_org_id, -1)
1969 or nvl(new.top_model_item_id, -1) <> nvl(old.top_model_item_id, -1)
1970 or nvl(new.top_model_org_id, -1) <> nvl(old.top_model_org_id, -1))) v
1971 ON (f.line_id = v.line_id)
1972 WHEN MATCHED THEN UPDATE SET
1973 f.item_id = v.item_id,
1974 f.inv_org_id = v.inv_org_id,
1975 f.inv_ou_id = v.inv_ou_id,
1976 f.org_ou_id = v.org_ou_id,
1977 f.customer_id = v.customer_id,
1978 f.sales_channel_id = v.sales_channel_id,
1979 f.return_reason_id = v.return_reason_id,
1980 f.order_category_id = v.order_category_id,
1981 f.order_source_id = v.order_source_id,
1982 f.order_type_id = v.order_type_id,
1983 f.ship_to_org_id = v.ship_to_org_id,
1984 f.sold_to_org_id = v.sold_to_org_id,
1985 f.time_act_ship_date_id = v.time_act_ship_date_id,
1986 f.time_booked_date_id = v.time_booked_date_id,
1987 f.time_shipped_date_id = v.time_shipped_date_id,
1988 f.time_fulfilled_date_id = v.time_fulfilled_date_id,
1989 f.time_schedule_date_id = v.time_schedule_date_id,
1990 f.time_ordered_date_id = v.time_ordered_date_id,
1991 f.time_promise_date_id = v.time_promise_date_id,
1992 f.time_request_date_id = v.time_request_date_id,
1993 f.currency_func_id = v.currency_func_id,
1994 f.curr_wh_func_id = v.curr_wh_func_id,
1995 f.inventory_item_id = v.inventory_item_id,
1996 f.item_inv_org_id = v.item_inv_org_id,
1997 f.top_model_item_id = v.top_model_item_id,
1998 f.top_model_org_id = v.top_model_org_id,
1999 f.actual_shipment_date = v.actual_shipment_date,
2000 f.booked_date = v.booked_date,
2001 f.shipped_date = v.shipped_date,
2002 f.fulfilled_date = v.fulfilled_date,
2003 f.schedule_ship_date = v.schedule_ship_date,
2004 f.ordered_date = v.ordered_date,
2005 f.promise_date = v.promise_date,
2006 f.request_date = v.request_date,
2007 f.ordered_quantity = v.ordered_quantity,
2008 f.header_id = v.header_id,
2009 f.h_marketing_source_code_id = v.h_marketing_source_code_id,
2010 f.invoice_to_org_id = v.invoice_to_org_id,
2011 f.marketing_source_code_id = v.marketing_source_code_id,
2012 f.open_flag = v.open_flag,
2013 f.order_date_type_code = v.order_date_type_code,
2014 f.order_number = v.order_number,
2015 f.order_quantity_uom = v.order_quantity_uom,
2016 f.shippable_flag = v.shippable_flag,
2017 f.fulfilled_flag = v.fulfilled_flag,
2018 f.line_category_code = v.line_category_code,
2019 f.line_item_type = v.line_item_type,
2020 f.line_number = v.line_number,
2021 f.item_type_code = v.item_type_code,
2022 f.ato_line_id = v.ato_line_id,
2023 f.count_pdue_line = v.count_pdue_line,
2024 f.count_ship_line = v.count_ship_line,
2025 f.flow_status_code = v.flow_status_code,
2026 f.inv_uom_code = v.inv_uom_code,
2027 f.top_model_line_id = v.top_model_line_id,
2028 f.unit_list_price = v.unit_list_price,
2029 f.unit_selling_price = v.unit_selling_price,
2030 f.service_parent_line_id = v.service_parent_line_id,
2031 f.booked_amt_g = v.booked_amt_g,
2032 f.invoiced_amt_g = v.invoiced_amt_g,
2033 f.shipped_amt_g = v.shipped_amt_g,
2034 f.fulfilled_amt_g = v.fulfilled_amt_g,
2035 f.booked_list_amt_g = v.booked_list_amt_g,
2036 f.booked_amt_f= v.booked_amt_f,
2037 f.invoiced_amt_f= v.invoiced_amt_f,
2038 f.shipped_amt_f = v.shipped_amt_f,
2039 f.fulfilled_amt_f = v.fulfilled_amt_f,
2040 f.booked_list_amt_f = v.booked_list_amt_f,
2041 f.booked_amt_f1= v.booked_amt_f1,
2042 f.invoiced_amt_f1= v.invoiced_amt_f1,
2043 f.shipped_amt_f1 = v.shipped_amt_f1,
2044 f.fulfilled_amt_f1 = v.fulfilled_amt_f1,
2045 f.booked_list_amt_f1 = v.booked_list_amt_f1,
2046 f.booked_qty_inv = v.booked_qty_inv,
2047 f.invoiced_qty_inv = v.invoiced_qty_inv,
2048 f.shipped_qty_inv = v.shipped_qty_inv,
2049 f.fulfilled_qty_inv = v.fulfilled_qty_inv,
2050 f.created_by = v.created_by,
2051 f.last_update_login = v.last_update_login,
2052 f.creation_date = v.creation_date,
2053 f.last_updated_by = v.last_updated_by,
2054 f.last_update_date = v.last_update_date,
2055 f.ship_to_party_id = v.ship_to_party_id,
2056 f.booked_amt_g1 = v.booked_amt_g1,
2057 f.invoiced_amt_g1 = v.invoiced_amt_g1,
2058 f.shipped_amt_g1 = v.shipped_amt_g1,
2059 f.fulfilled_amt_g1 = v.fulfilled_amt_g1,
2060 f.booked_list_amt_g1 = v.booked_list_amt_g1,
2061 f.freight_charge = v.freight_charge,
2062 f.freight_charge_f = v.freight_charge_f,
2063 f.freight_charge_g = v.freight_charge_g,
2064 f.freight_charge_g1 = v.freight_charge_g1,
2065 f.freight_cost = v.freight_cost,
2066 f.freight_cost_f = v.freight_cost_f,
2067 f.freight_cost_g = v.freight_cost_g,
2068 f.freight_cost_g1 = v.freight_cost_g1,
2069 f.charge_periodicity_code = v.charge_periodicity_code,
2070 f.blanket_number = v.blanket_number,
2071 f.blanket_line_number = v.blanket_line_number
2072 WHEN NOT MATCHED THEN INSERT(
2073 f.line_id,
2074 f.item_id,
2075 f.inv_org_id,
2076 f.inv_ou_id,
2077 f.org_ou_id,
2078 f.customer_id,
2079 f.sales_channel_id,
2080 f.return_reason_id,
2081 f.order_category_id,
2082 f.order_source_id,
2083 f.order_type_id,
2084 f.ship_to_org_id,
2085 f.sold_to_org_id,
2086 f.time_act_ship_date_id,
2087 f.time_booked_date_id,
2088 f.time_shipped_date_id,
2089 f.time_fulfilled_date_id,
2090 f.time_schedule_date_id,
2091 f.time_ordered_date_id,
2092 f.time_promise_date_id,
2093 f.time_request_date_id,
2094 f.currency_func_id,
2095 f.curr_wh_func_id,
2096 f.inventory_item_id,
2097 f.item_inv_org_id,
2098 f.top_model_item_id,
2099 f.top_model_org_id,
2100 f.actual_shipment_date,
2101 f.booked_date,
2102 f.shipped_date,
2103 f.fulfilled_date,
2104 f.schedule_ship_date,
2105 f.ordered_date,
2106 f.promise_date,
2107 f.request_date,
2108 f.ordered_quantity,
2109 f.header_id,
2110 f.h_marketing_source_code_id,
2111 f.invoice_to_org_id,
2112 f.marketing_source_code_id,
2113 f.open_flag,
2114 f.order_date_type_code,
2115 f.order_number,
2116 f.order_quantity_uom,
2117 f.shippable_flag,
2118 f.fulfilled_flag,
2119 f.line_category_code,
2120 f.line_item_type,
2121 f.line_number,
2122 f.item_type_code,
2123 f.ato_line_id,
2124 f.count_pdue_line,
2125 f.count_ship_line,
2126 f.flow_status_code,
2127 f.inv_uom_code,
2128 f.top_model_line_id,
2129 f.unit_list_price,
2130 f.unit_selling_price,
2131 f.service_parent_line_id,
2132 f.booked_amt_g,
2133 f.invoiced_amt_g,
2134 f.shipped_amt_g,
2135 f.fulfilled_amt_g,
2136 f.booked_list_amt_g,
2137 f.booked_amt_f,
2138 f.invoiced_amt_f,
2139 f.shipped_amt_f,
2140 f.fulfilled_amt_f,
2141 f.booked_list_amt_f,
2142 f.booked_amt_f1,
2143 f.invoiced_amt_f1,
2144 f.shipped_amt_f1,
2145 f.fulfilled_amt_f1,
2146 f.booked_list_amt_f1,
2147 f.booked_qty_inv,
2148 f.invoiced_qty_inv,
2149 f.shipped_qty_inv,
2150 f.fulfilled_qty_inv,
2151 f.created_by,
2152 f.last_update_login,
2153 f.creation_date,
2154 f.last_updated_by,
2155 f.last_update_date,
2156 f.ship_to_party_id,
2157 f.booked_amt_g1,
2158 f.invoiced_amt_g1,
2159 f.shipped_amt_g1,
2160 f.fulfilled_amt_g1,
2161 f.booked_list_amt_g1,
2162 f.freight_charge,
2163 f.freight_charge_f,
2164 f.freight_charge_g,
2165 f.freight_charge_g1,
2166 f.freight_cost,
2167 f.freight_cost_f,
2168 f.freight_cost_g,
2169 f.freight_cost_g1,
2170 f.charge_periodicity_code,
2171 f.blanket_number,
2172 f.blanket_line_number)
2173 VALUES (
2174 v.line_id,
2175 v.item_id,
2176 v.inv_org_id,
2177 v.inv_ou_id,
2178 v.org_ou_id,
2179 v.customer_id,
2180 v.sales_channel_id,
2181 v.return_reason_id,
2182 v.order_category_id,
2183 v.order_source_id,
2184 v.order_type_id,
2185 v.ship_to_org_id,
2186 v.sold_to_org_id,
2187 v.time_act_ship_date_id,
2188 v.time_booked_date_id,
2189 v.time_shipped_date_id,
2190 v.time_fulfilled_date_id,
2191 v.time_schedule_date_id,
2192 v.time_ordered_date_id,
2193 v.time_promise_date_id,
2194 v.time_request_date_id,
2195 v.currency_func_id,
2196 v.curr_wh_func_id,
2197 v.inventory_item_id,
2198 v.item_inv_org_id,
2199 v.top_model_item_id,
2200 v.top_model_org_id,
2201 v.actual_shipment_date,
2202 v.booked_date,
2203 v.shipped_date,
2204 v.fulfilled_date,
2205 v.schedule_ship_date,
2206 v.ordered_date,
2207 v.promise_date,
2208 v.request_date,
2209 v.ordered_quantity,
2210 v.header_id,
2211 v.h_marketing_source_code_id,
2212 v.invoice_to_org_id,
2213 v.marketing_source_code_id,
2214 v.open_flag,
2215 v.order_date_type_code,
2216 v.order_number,
2217 v.order_quantity_uom,
2218 v.shippable_flag,
2219 v.fulfilled_flag,
2220 v.line_category_code,
2221 v.line_item_type,
2222 v.line_number,
2223 v.item_type_code,
2224 v.ato_line_id,
2225 v.count_pdue_line,
2226 v.count_ship_line,
2227 v.flow_status_code,
2228 v.inv_uom_code,
2229 v.top_model_line_id,
2230 v.unit_list_price,
2231 v.unit_selling_price,
2232 v.service_parent_line_id,
2233 v.booked_amt_g,
2234 v.invoiced_amt_g,
2235 v.shipped_amt_g,
2236 v.fulfilled_amt_g,
2237 v.booked_list_amt_g,
2238 v.booked_amt_f,
2239 v.invoiced_amt_f,
2240 v.shipped_amt_f,
2241 v.fulfilled_amt_f,
2242 v.booked_list_amt_f,
2243 v.booked_amt_f1,
2244 v.invoiced_amt_f1,
2245 v.shipped_amt_f1,
2246 v.fulfilled_amt_f1,
2247 v.booked_list_amt_f1,
2248 v.booked_qty_inv,
2249 v.invoiced_qty_inv,
2250 v.shipped_qty_inv,
2251 v.fulfilled_qty_inv,
2252 v.created_by,
2253 v.last_update_login,
2254 v.creation_date,
2255 v.last_updated_by,
2256 v.last_update_date,
2257 v.ship_to_party_id,
2258 v.booked_amt_g1,
2259 v.invoiced_amt_g1,
2260 v.shipped_amt_g1,
2261 v.fulfilled_amt_g1,
2262 v.booked_list_amt_g1,
2263 v.freight_charge,
2264 v.freight_charge_f,
2265 v.freight_charge_g,
2266 v.freight_charge_g1,
2267 v.freight_cost,
2268 v.freight_cost_f,
2269 v.freight_cost_g,
2270 v.freight_cost_g1,
2271 v.charge_periodicity_code,
2272 v.blanket_number,
2273 v.blanket_line_number);
2274
2275 l_count := sql%rowcount;
2276 l_total := l_total + l_count;
2277 COMMIT;
2278 FII_UTIL.Stop_Timer;
2279 FII_UTIL.Print_Timer('Merged '||l_count|| ' rows in ');
2280
2281 END LOOP;
2282
2283 RETURN(l_total);
2284
2285 EXCEPTION
2286 WHEN OTHERS THEN
2287 g_errbuf := 'Error in Function MERGE_FACT : '||sqlerrm;
2288 g_retcode := sqlcode;
2289 RETURN(-1);
2290
2291 END;
2292
2293 FUNCTION WRAPUP RETURN NUMBER IS
2294
2295 BEGIN
2296
2297 -- ------------------------
2298 -- Delete ISC_TMP_BOOK_SUM2
2299 -- ------------------------
2300
2301 BIS_COLLECTION_UTILITIES.put_line(' ');
2302 BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
2303 FII_UTIL.Start_Timer;
2304
2305 IF (truncate_table('ISC_DBI_CHANGE_LOG') = -1) THEN
2306 return(-1);
2307 END IF;
2308
2309 IF (truncate_table('ISC_TMP_BOOK_SUM2') = -1) THEN
2310 return(-1);
2311 END IF;
2312
2313 IF (truncate_table('ISC_CURR_BOOK_SUM2') = -1) THEN
2314 return(-1);
2315 END IF;
2316
2317 IF (truncate_table('ISC_SERVICE_BOOK_SUM2') = -1) THEN
2318 return(-1);
2319 END IF;
2320
2321 FII_UTIL.Stop_Timer;
2322 FII_UTIL.Print_Timer('Truncated the temp table in');
2323
2324 -- ----------------------------------------------
2325 -- No exception raised so far. Successful. Call
2326 -- Wrapup to commit and insert messages into logs
2327 -- ----------------------------------------------
2328
2329 BIS_COLLECTION_UTILITIES.WRAPUP(
2330 TRUE,
2331 g_row_count,
2332 NULL,
2333 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date,
2334 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date
2335 );
2336
2337 RETURN (1);
2338
2339 EXCEPTION
2340 WHEN OTHERS THEN
2341 g_errbuf := 'Error in Function WRAPUP : '||sqlerrm;
2342 g_retcode := sqlcode;
2343 RETURN(-1);
2344 END wrapup;
2345
2346 ---------------------
2347 -- Public Procedures
2348 ---------------------
2349
2350 Procedure load_fact(errbuf IN OUT NOCOPY VARCHAR2,
2351 retcode IN OUT NOCOPY VARCHAR2) IS
2352
2353 l_failure EXCEPTION;
2354 l_start DATE;
2355 l_end DATE;
2356 l_period_from DATE;
2357 l_period_to DATE;
2358
2359 l_row_count NUMBER;
2360 l_schema VARCHAR2(30);
2361 l_status VARCHAR2(30);
2362 l_industry VARCHAR2(30);
2363
2364 l_ont_schema VARCHAR2(30);
2365 l_stmt VARCHAR2(2000);
2366
2367 BEGIN
2368 errbuf := NULL;
2369 retcode := '0';
2370 g_load_mode := 'INITIAL';
2371
2372 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2373 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load of the base summary ');
2374
2375 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_BOOK_SUM2_F')) THEN
2376 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2377 return;
2378 END IF;
2379
2380 IF (CHECK_SETUP = -1)
2381 THEN RAISE l_failure;
2382 END IF;
2383
2384 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date := g_global_start_date;
2385 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date := sysdate;
2386
2387 BIS_COLLECTION_UTILITIES.put_line( 'The collection date range is from '||
2388 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
2389 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
2390 BIS_COLLECTION_UTILITIES.put_line(' ');
2391
2392 EXECUTE IMMEDIATE 'alter session set hash_area_size=104857600';
2393 EXECUTE IMMEDIATE 'alter session set sort_area_size=104857600';
2394
2395 -- --------------------------------------------
2396 -- Identify Change for Booked Orders Lines
2397 -- --------------------------------------------
2398
2399 IF (FND_INSTALLATION.GET_APP_INFO('ONT', l_status, l_industry, l_ont_schema)) THEN
2400 l_stmt := 'TRUNCATE TABLE ' || l_ont_schema ||'.ONT_DBI_CHANGE_LOG';
2401 EXECUTE IMMEDIATE l_stmt;
2402 END IF;
2403
2404 l_row_count := IDENTIFY_CHANGE_INIT;
2405
2406
2407
2408 IF (l_row_count = -1)
2409 THEN RAISE l_failure;
2410 ELSIF (l_row_count = 0) THEN
2411
2412 -- Fix bug 4150188
2413 BIS_COLLECTION_UTILITIES.put_line(' ');
2414 BIS_COLLECTION_UTILITIES.put_line('Truncating the fact table');
2415 FII_UTIL.Start_Timer;
2416
2417 IF (truncate_table('ISC_BOOK_SUM2_F') = -1) THEN
2418 RAISE l_failure;
2419 END IF;
2420
2421 FII_UTIL.Stop_Timer;
2422 FII_UTIL.Print_Timer('Truncated the fact table in');
2423
2424 g_row_count := 0;
2425
2426 ELSE
2427 -- --------------
2428 -- Analyze tables
2429 -- --------------
2430
2431 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2432 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_TMP_BOOK_SUM2');
2433 FII_UTIL.Start_Timer;
2434
2435 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_schema)) THEN
2436 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2437 TABNAME => 'ISC_TMP_BOOK_SUM2');
2438 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2439 TABNAME => 'ISC_CURR_BOOK_SUM2');
2440 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2441 TABNAME => 'ISC_SERVICE_BOOK_SUM2');
2442 END IF;
2443
2444 FII_UTIL.Stop_Timer;
2445 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
2446
2447
2448
2449 -- In DBI5.0, we determine if a PTO/KIT top model is shippable by scanning through all it's child lines
2450 -- For DBI6.0, we do not support this logic anymore, so comment it out to improve performance
2451
2452 -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
2453 -- BIS_COLLECTION_UTILITIES.Put_Line('Identifying non-shippable CTO lines');
2454 -- FII_UTIL.Start_Timer;
2455
2456 -- UPDATE /*+ PARALLEL(F) */ isc_tmp_book_sum2 F
2457 -- SET view_type = 2
2458 -- WHERE view_type = 3
2459 -- AND PK1 is not null
2460 -- AND pk1 NOT IN (select /*+ hash_aj parallel(l) */ top_model_line_id
2461 -- from oe_order_lines_all l
2462 -- where l.shippable_flag = 'Y'
2463 -- and top_model_line_id is not null);
2464 -- COMMIT;
2465
2466 -- FII_UTIL.Stop_Timer;
2467 -- FII_UTIL.Print_Timer('Identified non-shippable CTO lines in ');
2468
2469
2470 IF (DANGLING_CHECK = -1) THEN
2471 RAISE l_failure;
2472 END IF;
2473
2474
2475 -- --------------------------------------------
2476 -- Truncate Sum2 table if it is an initial load
2477 -- --------------------------------------------
2478
2479 BIS_COLLECTION_UTILITIES.put_line(' ');
2480 BIS_COLLECTION_UTILITIES.put_line('Truncating the fact table');
2481 FII_UTIL.Start_Timer;
2482
2483 IF (truncate_table('ISC_BOOK_SUM2_F') = -1) THEN
2484 RAISE l_failure;
2485 END IF;
2486
2487 FII_UTIL.Stop_Timer;
2488 FII_UTIL.Print_Timer('Truncated the fact table in');
2489
2490 -- --------------------------------------------
2491 -- Insert data into Sum2 table
2492 -- --------------------------------------------
2493
2494 BIS_COLLECTION_UTILITIES.put_line(' ');
2495 BIS_COLLECTION_UTILITIES.put_line('Inserting data into fact table');
2496 FII_UTIL.Start_Timer;
2497
2498 g_row_count := Insert_fact;
2499
2500 FII_UTIL.Stop_Timer;
2501 FII_UTIL.Print_Timer('Inserted '||nvl(g_row_count,0)||' rows into the fact table in');
2502
2503 IF (g_row_count = -1) THEN
2504 RAISE l_failure;
2505 END IF;
2506
2507 END IF;
2508
2509 IF (WRAPUP = -1) THEN
2510 RAISE l_failure;
2511 END IF;
2512
2513 IF (g_warning = 1) then
2514 retcode := '1';
2515 END IF;
2516
2517
2518 EXCEPTION
2519
2520 WHEN L_FAILURE THEN
2521 ROLLBACK;
2522 BIS_COLLECTION_UTILITIES.put_line(' ');
2523 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2524 retcode := -1;
2525 errbuf := g_errbuf;
2526
2527 BIS_COLLECTION_UTILITIES.WRAPUP(
2528 FALSE,
2529 g_row_count,
2530 g_errbuf,
2531 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date,
2532 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date
2533 );
2534
2535 WHEN OTHERS THEN
2536 ROLLBACK;
2537 g_errbuf := sqlerrm ||' - '||sqlcode;
2538 BIS_COLLECTION_UTILITIES.put_line(' ');
2539 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2540 retcode := -1;
2541 errbuf := g_errbuf;
2542
2543 BIS_COLLECTION_UTILITIES.WRAPUP(
2544 FALSE,
2545 g_row_count,
2546 g_errbuf,
2547 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date,
2548 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date
2549 );
2550
2551 END load_fact;
2552
2553 Procedure update_fact(errbuf IN OUT NOCOPY VARCHAR2,
2554 retcode IN OUT NOCOPY VARCHAR2) IS
2555
2556 l_failure EXCEPTION;
2557 l_start DATE;
2558 l_end DATE;
2559 l_period_from DATE;
2560 l_period_to DATE;
2561 l_row_count NUMBER := 0;
2562 l_delete_count NUMBER := 0;
2563 l_schema VARCHAR2(30);
2564 l_status VARCHAR2(30);
2565 l_industry VARCHAR2(30);
2566 l_sc_page_implemented NUMBER := 0;
2567
2568 BEGIN
2569 errbuf := NULL;
2570 retcode := '0';
2571 g_load_mode := 'INCREMENTAL';
2572
2573 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2574 BIS_COLLECTION_UTILITIES.put_line('Begin the ' || g_load_mode || ' load of the base summary ');
2575
2576 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_BOOK_SUM2_F')) THEN
2577 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2578 return;
2579 END IF;
2580
2581 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('ISC_BOOK_SUM2_F', l_start, l_end, l_period_from, l_period_to);
2582 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date := l_period_to;
2583 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date := sysdate;
2584
2585 IF (CHECK_SETUP = -1)
2586 THEN RAISE l_failure;
2587 END IF;
2588
2589 -- --------------------------------------------
2590 -- Identify Change for Booked Orders Lines
2591 -- --------------------------------------------
2592
2593 BIS_COLLECTION_UTILITIES.put_line('Identifying changed Booked orders lines');
2594
2595
2596 g_incre_start_date := sysdate;
2597 BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
2598 l_row_count := IDENTIFY_CHANGE_ICRL;
2599
2600
2601
2602 IF (l_row_count = -1) THEN
2603 RAISE l_failure;
2604 ELSIF (l_row_count = 0) THEN
2605 g_row_count := 0;
2606 ELSE
2607 -- --------------
2608 -- Analyze tables
2609 -- --------------
2610
2611 BIS_COLLECTION_UTILITIES.Put_Line(' ');
2612 BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_TMP_BOOK_SUM2');
2613 FII_UTIL.Start_Timer;
2614
2615 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_schema)) THEN
2616 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2617 TABNAME => 'ISC_TMP_BOOK_SUM2');
2618 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2619 TABNAME => 'ISC_CURR_BOOK_SUM2');
2620 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
2621 TABNAME => 'ISC_SERVICE_BOOK_SUM2');
2622 END IF;
2623
2624 FII_UTIL.Stop_Timer;
2625 FII_UTIL.Print_Timer('Analyzed the temp tables in ');
2626
2627
2628
2629
2630 -- In DBI5.0, we determine if a PTO/KIT top model is shippable by scanning through all it's child lines
2631 -- For DBI6.0, we do not support this logic anymore, so comment it out to improve performance
2632
2633 -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
2634 -- BIS_COLLECTION_UTILITIES.Put_Line('Identifying non-shippable CTO lines');
2635 -- FII_UTIL.Start_Timer;
2636
2637 -- UPDATE /*+ PARALLEL(F) */ isc_tmp_book_sum2 F
2638 -- SET view_type = 2
2639 -- WHERE view_type = 3
2640 -- AND PK1 is not null
2641 -- AND pk1 NOT IN (select /*+ hash_aj parallel(l) */ top_model_line_id
2642 -- from oe_order_lines_all l
2643 -- where l.shippable_flag = 'Y'
2644 -- and top_model_line_id is not null);
2645
2646 -- COMMIT;
2647
2648 -- FII_UTIL.Stop_Timer;
2649 -- FII_UTIL.Print_Timer('Identified non-shippable CTO lines in ');
2650
2651
2652
2653
2654
2655
2656 -- ---------------------
2657 -- Dangling Checking
2658 -- ---------------------
2659
2660
2661 IF (DANGLING_CHECK = -1) THEN
2662 RAISE l_failure;
2663 END IF;
2664
2665
2666 -- --------------------------------------------
2667 -- Merge data into Sum2 table
2668 -- --------------------------------------------
2669
2670 BIS_COLLECTION_UTILITIES.put_line(' ');
2671 BIS_COLLECTION_UTILITIES.put_line('Merging data to fact table');
2672
2673
2674 g_row_count := Merge_fact(ceil(l_row_count/g_batch_size));
2675
2676 BIS_COLLECTION_UTILITIES.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact table');
2677
2678 IF (g_row_count = -1) THEN
2679 RAISE l_failure;
2680 END IF;
2681
2682
2683 -- ------------------------------------
2684 -- Sales Credits INCREMENTAL collection
2685 -- ------------------------------------
2686
2687 BIS_COLLECTION_UTILITIES.put_line('');
2688 BIS_COLLECTION_UTILITIES.put_line('');
2689 BIS_COLLECTION_UTILITIES.put_line('+--------------------------------------------+');
2690 BIS_COLLECTION_UTILITIES.put_line('Entering function Update_Sales_Fact.');
2691
2692
2693
2694 /* start of Sales Credits fact incremental collection */
2695
2696
2697
2698
2699
2700 SELECT nvl(implementation_flag,0)
2701 INTO l_sc_page_implemented
2702 FROM (SELECT sum(decode(implementation_flag,'Y',1,0)) implementation_flag
2703 FROM bis_obj_properties
2704 WHERE object_name IN (SELECT distinct bis.object_name
2705 FROM BIS_OBJ_DEPENDENCY bis,
2706 (SELECT object_name
2707 FROM bis_obj_dependency
2708 START WITH depend_object_name = 'ISC_SALES_CREDITS_F'
2709 CONNECT BY PRIOR object_name = depend_object_name
2710 ORDER BY 1) inline
2711 WHERE bis.object_name = inline.object_name
2712 AND bis.object_type = 'PAGE'));
2713
2714 IF l_sc_page_implemented = 0
2715 THEN
2716 NULL; -- no page using sales credits fact has been implemented, skip collection
2717 BIS_COLLECTION_UTILITIES.put_line('No implemented page is based on the Sales Credits fact.');
2718 BIS_COLLECTION_UTILITIES.put_line('Skipping the collection of Sales Credits fact.');
2719 ELSE
2720 BIS_COLLECTION_UTILITIES.put_line('Identified implemented pages using the Sales Credits fact.');
2721 BIS_COLLECTION_UTILITIES.put_line('Starting the Incremental collection of Sales Credits fact.');
2722
2723 IF (update_sales_fact = -1) -- call of the sc_f incremental collection function
2724 THEN -- coll of sales credits fact errored out
2725 g_row_count := -1;
2726 BIS_COLLECTION_UTILITIES.put_line('Incremental collection of Sales Credits fact failed.');
2727 RAISE l_failure;
2728 ELSE
2729 BIS_COLLECTION_UTILITIES.put_line('Incremental collection of Sales Fact finished.');
2730 END IF;
2731
2732 END IF;
2733 BIS_COLLECTION_UTILITIES.put_line('Exiting function Update_Sales_Fact.');
2734 BIS_COLLECTION_UTILITIES.put_line('+--------------------------------------------+');
2735
2736
2737
2738
2739 /* end of Sales Credits fact incremental collection*/
2740
2741
2742
2743 END IF;
2744
2745 -- -------------------------------------------------
2746 -- Delete rows from ONT_DBI_CHANGE_LOG base on rowid
2747 -- -------------------------------------------------
2748
2749 BIS_COLLECTION_UTILITIES.put_line('Deleting rows from OM log table');
2750 FII_UTIL.Start_Timer;
2751
2752
2753 DELETE FROM ONT_DBI_CHANGE_LOG
2754 WHERE rowid IN (select log_rowid from isc_dbi_change_log)
2755 AND last_update_date < g_incre_start_date;
2756
2757 FII_UTIL.Stop_Timer;
2758 FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from OM log table in');
2759 COMMIT;
2760
2761
2762 IF (WRAPUP = -1) THEN
2763 RAISE l_failure;
2764 END IF;
2765
2766 IF (g_warning = 1) then
2767 retcode := '1';
2768 END IF;
2769
2770
2771
2772
2773 EXCEPTION
2774
2775 WHEN L_FAILURE THEN
2776 ROLLBACK;
2777 BIS_COLLECTION_UTILITIES.put_line(' ');
2778 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2779 retcode := -1;
2780 errbuf := g_errbuf;
2781
2782 BIS_COLLECTION_UTILITIES.WRAPUP(
2783 FALSE,
2784 g_row_count,
2785 g_errbuf,
2786 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date,
2787 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date
2788 );
2789
2790 WHEN OTHERS THEN
2791 ROLLBACK;
2792 g_errbuf := sqlerrm ||' - '||sqlcode;
2793 BIS_COLLECTION_UTILITIES.put_line(' ');
2794 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2795 retcode := -1;
2796 errbuf := g_errbuf;
2797
2798 BIS_COLLECTION_UTILITIES.WRAPUP(
2799 FALSE,
2800 g_row_count,
2801 g_errbuf,
2802 ISC_DBI_BOOK_SUM2_F_C.g_push_from_date,
2803 ISC_DBI_BOOK_SUM2_F_C.g_push_to_date
2804 );
2805
2806 END update_fact;
2807
2808 -- ------------------------------------
2809 -- Sales Credits INITIAL collection
2810 -- ------------------------------------
2811
2812 Procedure load_sales_fact(errbuf IN OUT NOCOPY VARCHAR2,
2813 retcode IN OUT NOCOPY VARCHAR2) IS
2814
2815 l_isc_schema VARCHAR2(30);
2816 l_status VARCHAR2(30);
2817 l_industry VARCHAR2(30);
2818 l_stmt VARCHAR2(32000);
2819 l_failure EXCEPTION;
2820
2821 BEGIN
2822
2823 BIS_COLLECTION_UTILITIES.put_line(' ');
2824
2825 IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_SALES_CREDITS_F')) THEN
2826 RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2827 return;
2828 END IF;
2829
2830
2831 BIS_COLLECTION_UTILITIES.put_line('Truncating the sales fact table');
2832 FII_UTIL.Start_Timer;
2833
2834 IF (truncate_table('ISC_SALES_CREDITS_F') = -1) THEN
2835 RAISE l_failure;
2836 END IF;
2837
2838 FII_UTIL.Stop_Timer;
2839 FII_UTIL.Print_Timer('Truncated the sales fact table in');
2840
2841
2842 /* Insert into ISC_SALES_CREDITS_F */
2843 BIS_COLLECTION_UTILITIES.put_line(' ');
2844 BIS_COLLECTION_UTILITIES.put_line('Inserting data into sales fact table');
2845 FII_UTIL.Start_Timer;
2846
2847 insert /*+ append parallel(f) */ into isc_sales_credits_f f
2848 with s as (
2849 select /*+ ordered use_hash(sc) parallel(sc) parallel(sr)
2850 pq_distribute(sr,hash,hash) */
2851 sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
2852 sc.salesrep_id, sc.header_id, sc.line_id, sr.resource_id,
2853 sr.org_id, sc.sales_group_id group_id, sc.created_by, sc.creation_date,
2854 sc.last_updated_by, sc.last_update_date, sc.last_update_login
2855 from oe_sales_credit_types sc_typ,
2856 oe_sales_credits sc,
2857 jtf_rs_salesreps sr
2858 where sc.sales_group_id is not null
2859 and sc.salesrep_id = sr.salesrep_id
2860 and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
2861 and sc_typ.quota_flag = 'Y'
2862 union all
2863 select /*+ ordered use_hash(sc) parallel(sc) parallel(sg)
2864 pq_distribute(sg,hash,hash) */
2865 sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
2866 sc.salesrep_id, sc.header_id, sc.line_id, sg.resource_id,
2867 sg.org_id, sg.group_id, sc.created_by, sc.creation_date,
2868 sc.last_updated_by, sc.last_update_date, sc.last_update_login
2869 from oe_sales_credit_types sc_typ,
2870 oe_sales_credits sc,
2871 jtf_rs_srp_groups sg
2872 where sc.sales_group_id is null
2873 and sc.salesrep_id = sg.salesrep_id
2874 and sc.last_update_date between sg.start_date and sg.end_date
2875 and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
2876 and sc_typ.quota_flag = 'Y')
2877 select pk, sales_credit_id, resource_id, group_id, header_id, line_id,
2878 percent, sales_credit_type_id, created_by, creation_date,
2879 last_updated_by, last_update_date, last_update_login
2880 from (
2881 select pk, sales_credit_id, resource_id, group_id, header_id, line_id,
2882 percent, sales_credit_type_id, created_by, creation_date,
2883 last_updated_by, last_update_date, last_update_login,
2884 rank() over (partition by line_id order by rnk) low_rnk
2885 from (
2886 select /*+ parallel(s) */
2887 'DIRECT-'||s.sales_credit_id pk, s.sales_credit_id, s.group_id,
2888 t5.header_id, t5.line_id, 1 rnk, s.resource_id, s.percent,
2889 s.sales_credit_type_id, s.created_by, s.creation_date,
2890 s.last_updated_by, s.last_update_date, s.last_update_login
2891 from isc_book_sum2_f t5, s
2892 where s.line_id = t5.line_id
2893 and s.org_id = t5.org_ou_id
2894 union all
2895 select /*+ parallel(s) parallel(t7a) use_hash(s) pq_distribute(s,hash,hash) */
2896 'SERVICE_PARENT-'||t7a.line_id||'-'||s.sales_credit_id pk,
2897 s.sales_credit_id, s.group_id, t7a.header_id, t7a.line_id, 2 rnk,
2898 s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
2899 s.creation_date, s.last_updated_by, s.last_update_date,
2900 s.last_update_login
2901 from isc_book_sum2_f t7a, s
2902 where s.line_id = t7a.service_parent_line_id
2903 and s.org_id = t7a.org_ou_id
2904 and t7a.item_type_code = 'SERVICE'
2905 union all
2906 select /*+ parallel(s) parallel(t7b2) use_hash(s) pq_distribute(s,hash,hash)
2907 parallel(t7b1) use_hash(t7b1) pq_distribute(t7b1,hash,hash) */
2908 'SERVICE_PARENT_TOPMODEL-'||t7b2.line_id||'-'||s.sales_credit_id pk,
2909 s.sales_credit_id, s.group_id, t7b2.header_id, t7b2.line_id, 3 rnk,
2910 s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
2911 s.creation_date, s.last_updated_by, s.last_update_date,
2912 s.last_update_login
2913 from isc_book_sum2_f t7b2, isc_book_sum2_f t7b1, s
2914 where s.line_id = t7b1.top_model_line_id
2915 and s.org_id = t7b1.org_ou_id
2916 and t7b1.line_id = t7b2.service_parent_line_id
2917 and t7b2.item_type_code = 'SERVICE'
2918 union all
2919 select /*+ ordered parallel(s) parallel(t7b1) use_hash(s) pq_distribute(s,hash,hash) */
2920 'TOPMODEL-'||t7b1.line_id||'-'||s.sales_credit_id pk,
2921 s.sales_credit_id, s.group_id, t7b1.header_id, t7b1.line_id, 4 rnk,
2922 s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
2923 s.creation_date, s.last_updated_by, s.last_update_date,
2924 s.last_update_login
2925 from isc_book_sum2_f t7b1, s
2926 where s.line_id = t7b1.top_model_line_id
2927 and s.org_id = t7b1.org_ou_id
2928 union all
2929 select /*+ ordered parallel(s) parallel(t11) use_hash(s) pq_distribute(s,hash,hash) */
2930 'HEADER-'||t11.line_id||'-'||s.sales_credit_id pk,
2931 s.sales_credit_id, s.group_id, t11.header_id, t11.line_id, 5 rnk,
2932 s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
2933 s.creation_date, s.last_updated_by, s.last_update_date,
2934 s.last_update_login
2935 from isc_book_sum2_f t11, s
2936 where s.line_id is null
2937 and s.org_id = t11.org_ou_id
2938 and s.header_id = t11.header_id))
2939 where low_rnk = 1;
2940
2941 FII_UTIL.Stop_Timer;
2942 g_row_count := sql%rowcount;
2943 FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows into the sales fact table in');
2944 COMMIT;
2945
2946 -- ----------------------------------------------
2947 -- No exception raised so far. Successful. Call
2948 -- Wrapup to commit and insert messages into logs
2949 -- ----------------------------------------------
2950
2951 BIS_COLLECTION_UTILITIES.WRAPUP(
2952 TRUE,
2953 g_row_count,
2954 NULL,
2955 NULL,
2956 NULL
2957 );
2958
2959 EXCEPTION
2960
2961 WHEN L_FAILURE THEN
2962 ROLLBACK;
2963 BIS_COLLECTION_UTILITIES.put_line(' ');
2964 BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2965 retcode := -1;
2966 errbuf := g_errbuf;
2967
2968 BIS_COLLECTION_UTILITIES.WRAPUP(
2969 FALSE,
2970 g_row_count,
2971 g_errbuf,
2972 NULL,
2973 NULL
2974 );
2975
2976 WHEN OTHERS THEN
2977 ROLLBACK;
2978 g_errbuf := sqlerrm ||' - '||sqlcode;
2979 BIS_COLLECTION_UTILITIES.put_line(' ');
2980 BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2981 retcode := -1;
2982 errbuf := g_errbuf;
2983
2984 BIS_COLLECTION_UTILITIES.WRAPUP(
2985 FALSE,
2986 g_row_count,
2987 g_errbuf,
2988 NULL,
2989 NULL
2990 );
2991
2992 END load_sales_fact;
2993
2994 Procedure update_sales_fact_dummy(errbuf IN OUT NOCOPY VARCHAR2,
2995 retcode IN OUT NOCOPY VARCHAR2) IS
2996 BEGIN
2997 null;
2998 END update_sales_fact_dummy;
2999
3000 END ISC_DBI_BOOK_SUM2_F_C;
3001