[Home] [Help]
PACKAGE BODY: APPS.POA_PORTAL_POPULATE_C
Source
1 Package BODY POA_PORTAL_POPULATE_C AS
2 /* $Header: poaporsb.pls 120.0 2005/06/01 15:18:56 appldev noship $ */
3
4 procedure populate_poa (Errbuf in out NOCOPY Varchar2,
5 Retcode in out NOCOPY Varchar2) IS
6
7 cycq_start DATE :=NULL;
8 cycq_end DATE :=NULL;
9 lycq_start DATE :=NULL;
10 lycq_end DATE :=NULL;
11 l_number1 NUMBER;
12 l_number2 NUMBER;
13 l_number3 NUMBER;
14 l_number4 NUMBER;
15 l_number5 NUMBER;
16 l_number6 NUMBER;
17 success VARCHAR2(50);
18
19 BEGIN
20 truncate_tables(1, success);
21 --dbms_output.put_line('Tables were truncated = ' || success);
22
23 cycq_start := FII_TIME_WH_API.ent_cycq_start;
24 cycq_end := fii_time_wh_api.today;
25
26 lycq_start := FII_TIME_WH_API.ent_lycq_start;
27 lycq_end := FII_TIME_WH_API.ent_lycq_today1;
28
29 insert_rows_pd(cycq_start, cycq_end, 'C', l_number1, success);
30 --dbms_output.put_line(
31 -- 'PD: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number1 || ':' || success);
32
33 insert_rows_pd(lycq_start, lycq_end, 'L', l_number2, success);
34 --dbms_output.put_line(
35 -- 'PD: ' || lycq_start || ' to ' || lycq_end || ': Rows= ' || l_number2 || ':' || success);
36
37 insert_rows_sp(cycq_start, cycq_end, l_number3, success);
38 --dbms_output.put_line(
39 -- 'SP: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number3 || ':' || success);
40
41 insert_rows_sr(cycq_start, cycq_end, l_number4, success);
42 --dbms_output.put_line(
43 -- 'SR: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number1 || ':' || success);
44
45 insert_rows_cm(cycq_start, cycq_end, l_number5, success);
46 --dbms_output.put_line(
47 -- 'CM: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number5 || ':' || success);
48
49 insert_rows_rcv(cycq_start, cycq_end, l_number6, success);
50 --dbms_output.put_line(
51 -- 'CM: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number6 || ':' || success);
52
53 Errbuf := 'PD_C: '||l_number1 || '; PD_L: '||l_number2 ||
54 '; SP: '||l_number3 || ': SR: ' ||l_number4 ||
55 '; CM: '||l_number5 || ': RCV: ' ||l_number6;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 Errbuf := 'Error in main: ' || SQLERRM;
60 Raise;
61
62 end populate_poa;
63
64
65 PROCEDURE truncate_tables (p_type IN NUMBER,
66 success OUT NOCOPY VARCHAR2) IS
67
68 l_poa_schema VARCHAR2(30);
69 l_stmt VARCHAR2(200);
70 l_status VARCHAR2(30);
71 l_industry VARCHAR2(30);
72
73 BEGIN
74
75 IF (p_type = 1) THEN
76
77 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
78 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_PDIST';
79 EXECUTE IMMEDIATE l_stmt;
80 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_PDIST1';
81 EXECUTE IMMEDIATE l_stmt;
82 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_PDIST2';
83 EXECUTE IMMEDIATE l_stmt;
84 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_SPERF';
85 EXECUTE IMMEDIATE l_stmt;
86 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_RISK_IND';
87 EXECUTE IMMEDIATE l_stmt;
88 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_RISK_SUMMARY';
89 EXECUTE IMMEDIATE l_stmt;
90 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_SUP_RISK';
91 EXECUTE IMMEDIATE l_stmt;
92 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_PORTAL_RCPT_SUM';
93 EXECUTE IMMEDIATE l_stmt;
94 END IF;
95
96 END IF;
97
98 IF (p_type = 2) THEN
99 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry,
100 l_poa_schema)) THEN
101 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||
102 '.POA_PORTAL_FII_SUMMARY';
103 EXECUTE IMMEDIATE l_stmt;
104 END IF;
105
106 END IF;
107
108 success := 'ok';
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 success := 'Error in truncate: ' || SQLERRM;
113 RAISE;
114 end truncate_tables;
115
116
117 PROCEDURE insert_rows_sp( p_start IN DATE,
118 p_end IN DATE,
119 p_count OUT NOCOPY NUMBER,
120 success OUT NOCOPY varchar2) IS
121 BEGIN
122
123 INSERT into POA_PORTAL_SPERF (
124 NUM_LATE_RCPT,
125 NUM_RCPT_LINES,
126 AVG_Rcpt_Late_Days,
127 Max_Days_Lt,
128 Late_Rcpt_Amount,
129 Supplier_PK_Key,
130 Supplier_Name,
131 OPERATING_UNIT_PK_KEY,
132 OPERATING_UNIT_NAME)
133 SELECT 0,
134 count(rcv.rcv_txn_pk),
135 0, 0, 0,
136 tp.tprt_trade_partner_pk_key,
137 tp.tprt_name,
138 org.oper_operating_unit_pk_key,
139 org.oper_name
140 FROM poa_edw_rcv_txns_f rcv,
141 edw_organization_m org,
142 edw_trd_partner_m tp,
143 edw_lookup_m lku,
144 edw_time_m time
145 WHERE rcv.RCV_DEL_TO_ORG_FK_KEY = org.ORGA_ORGANIZATION_PK_KEY
146 and rcv.Supplier_Site_FK_Key = tp.TPLO_TPARTNER_LOC_PK_KEY
147 and rcv.TXN_CREAT_FK_KEY = time.CDAY_CAL_DAY_PK_KEY
148 and rcv.txn_type_fk_key = lku.lucd_lookup_code_pk_key
149 and lku.LUCD_LOOKUP_CODE = 'RECEIVE'
150 and time.CDAY_CALENDAR_DATE between FII_TIME_WH_API.ent_cycq_start
151 and FII_TIME_WH_API.today
152 GROUP BY org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
153 tp.TPRT_Trade_Partner_PK_Key, tp.TPRT_NAME
154 UNION ALL
155 SELECT count(sp.num_late_receipt),
156 0,
157 avg(FII_TIME_WH_API.today-time.CDAY_CALENDAR_DATE),
158 max(FII_TIME_WH_API.today-time.CDAY_CALENDAR_DATE),
159 sum((sp.Qty_Ordered_B-sp.Qty_Received_B)*sp.Price_G),
160 tp.tprt_trade_partner_pk_key,
161 tp.tprt_name,
162 org.oper_operating_unit_pk_key,
163 org.oper_name
164 FROM poa_edw_sup_perf_f sp,
165 edw_organization_m org,
166 edw_trd_partner_m tp,
167 edw_time_m time
168 WHERE ((sp.Qty_Ordered_B-sp.Qty_Received_B) > 0)
169 and sp.SHIP_TO_ORG_FK_KEY = org.ORGA_ORGANIZATION_PK_KEY
170 and sp.Supplier_Site_FK_Key = tp.TPLO_TPARTNER_LOC_PK_KEY
171 and sp.Promised_Date_FK_Key = time.CDAY_CAL_DAY_PK_KEY
172 and time.CDAY_CALENDAR_DATE < FII_TIME_WH_API.today
173 GROUP BY org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
174 tp.TPRT_Trade_Partner_PK_Key, tp.TPRT_NAME;
175
176 p_count := sql%rowcount;
177
178 success := 'ok';
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 success := 'Error in insert_sp: ' || SQLERRM;
183 RAISE;
184 end insert_rows_sp;
185
186
187 PROCEDURE populate_poa_fii ( Errbuf in out NOCOPY Varchar2,
188 Retcode in out NOCOPY Varchar2) IS
189
190 cycq_start DATE :=NULL;
191 cycq_end DATE :=NULL;
192 l_number NUMBER;
193 success VARCHAR2(50);
194
195 BEGIN
196 truncate_tables(2, success);
197 --dbms_output.put_line('Tables were truncated = ' || success);
198
199 cycq_start := FII_TIME_WH_API.ent_cycq_start;
200 cycq_end := fii_time_wh_api.today;
201
202 insert_rows_cross(cycq_start, cycq_end, l_number, success);
203 --dbms_output.put_line(
204 -- 'CR: ' || cycq_start || ' to ' || cycq_end || ': Rows= ' || l_number || ':' || success);
205
206 Errbuf := 'POA_FII: '|| l_number;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 Errbuf := 'Error in main: ' || SQLERRM;
211 Raise;
212
213 end populate_poa_fii;
214
215
216 PROCEDURE insert_rows_cross( p_start IN DATE,
217 p_end IN DATE,
218 p_count OUT NOCOPY NUMBER,
219 success OUT NOCOPY varchar2) IS
220 BEGIN
221
222 INSERT INTO POA_PORTAL_FII_SUMMARY(
223 OPERATING_UNIT_PK_KEY ,
224 OPERATING_UNIT_NAME ,
225 LATE_RECEIPTS ,
226 LATE_RECEIPTS_AVG_AGE ,
227 RECEIPT_LINES_COUNT ,
228 PO_LINE_COUNT ,
229 PO_CYCLE_TIME_AVG ,
230 INVOICE_LINES_COUNT ,
231 PAYMENT_LINES_COUNT ,
232 OPEN_PAYMENTS ,
233 OPEN_PAY_AGE ,
234 CNT_LATE_SUPPLIER_CONF ,
235 AVG_LATE_SUP_CONF_AGE)
236 SELECT ou_pk_key,
237 ou_name,
238 sum(late_receipts),
239 avg(Late_Receipts_Avg_Age),
240 sum(receipt_lines),
241 sum(po_lines),
242 sum(cycle_time),
243 sum(invoice_lines),
244 sum(payment_lines),
245 sum(open_payments),
246 sum(open_pay_age),
247 sum(late_supplier_conf),
248 sum(late_conf_age)
249 FROM (select operating_unit_pk_key ou_pk_key,
250 operating_unit_name ou_name,
251 sum(num_late_rcpt) late_receipts,
252 avg(AVG_Rcpt_Late_Days) Late_Receipts_Avg_Age,
253 sum(num_rcpt_lines) receipt_lines,
254 0 po_lines,
255 0 cycle_time,
256 0 invoice_lines,
257 0 payment_lines,
258 0 open_payments,
259 0 open_pay_age,
260 0 late_supplier_conf,
261 0 late_conf_age
262 from poa_portal_sperf
263 group by operating_unit_pk_key,
264 operating_unit_name
265 UNION ALL
266 select operating_unit_pk_key,
267 operating_unit_name,
268 0,
269 0,
270 0,
271 sum(cnt_po_lines),
272 decode(sum(weight_avg_cycle),0,0,
273 sum(avg_cycle_time*weight_avg_cycle)/
274 sum(weight_avg_cycle)),
275 0,
276 0,
277 0,
278 0,
279 0,
280 0
281 from poa_portal_pdist
282 where Quarter = 'C'
283 group by operating_unit_pk_key,
284 operating_unit_name
285 UNION ALL
286 select operating_unit_pk_key,
287 operating_unit_name,
288 0,
289 0,
290 0,
291 0,
292 0,
293 0,
294 0,
295 0,
296 0,
297 sum(cnt_late_supplier_conf),
298 avg(AVG_LATE_SUP_CONF_AGE)
299 from poa_portal_pdist
300 where Quarter = 'C'
301 and AVG_LATE_SUP_CONF_AGE <> 0
302 group by operating_unit_pk_key,
303 operating_unit_name
304 UNION ALL
305 select operating_unit_pk_key,
306 operating_unit_name,
307 0,
308 0,
309 0,
310 0,
311 0,
312 sum(inv_lines_count) invoice_lines,
313 sum(inv_payment_count) payment_lines,
314 0,
315 0,
316 0,
317 0
318 from fii_ap_op_indicator_summary
319 group by operating_unit_pk_key,
320 operating_unit_name
321 UNION ALL
322 select operating_unit_pk_key,
323 operating_unit_name,
324 0,
325 0,
326 0,
327 0,
328 0,
329 0,
330 0,
331 count(sch_payment_id) open_payments,
332 round(avg(fii_time_wh_api.today-payment_due_date)) open_pay_age,
333 0,
334 0
335 from fii_ap_trans_backlog_summary
336 group by operating_unit_pk_key,
337 operating_unit_name)
338 GROUP BY ou_name, ou_pk_key;
339
340 p_count := sql%rowcount;
341
342 success := 'ok';
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 success := 'Error in insert_pd: ' || SQLERRM;
347 RAISE;
348 end insert_rows_cross;
349
350
351 PROCEDURE insert_rows_pd( p_start IN DATE,
352 p_end IN DATE,
353 p_quarter IN VARCHAR2,
354 p_count OUT NOCOPY NUMBER,
355 success OUT NOCOPY varchar2) IS
356 BEGIN
357
358 insert into POA_PORTAL_PDIST (
359 SUM_PURCHASES,
360 SUM_LEAKAGE,
361 SUM_NON_CONTRACT,
362 SUM_CONTRACT,
363 OPERATING_UNIT_PK_KEY,
364 OPERATING_UNIT_NAME,
365 ORGANIZATION_PK_KEY,
366 ORGANIZATION_NAME,
367 CATEGORY_PK_KEY,
368 CATEGORY_NAME,
369 SUPPLIER_PK_KEY,
370 SUPPLIER_NAME,
371 AVG_CYCLE_TIME,
372 CNT_PO_LINES,
373 CNT_PO_HEADERS,
374 SUM_LATE_PURCHASES,
375 CNT_LATE_SUPPLIER_CONF,
376 AVG_LATE_SUP_CONF_AGE,
377 WEIGHT_AVG_CYCLE,
378 WEIGHT_AVG_CONF,
379 POS_POTENTIAL_SVG,
380 NEG_POTENTIAL_SVG,
381 QUARTER,
382 ITEM_PURCHASABLE_FLAG)
383 select sum(pod.amt_purchased_g),
384 sum(pod.amt_leakage_g),
385 sum(pod.amt_noncontract_g),
386 sum(pod.amt_contract_g),
387 org.oper_operating_unit_pk_key,
388 org.oper_name,
389 org.Orga_Organization_PK_Key,
390 org.Orga_Name,
391 item.ci11_category_pk_key,
392 item.ci11_name,
393 tp.tprt_trade_partner_pk_key,
394 tp.tprt_name,
395 avg(pod.po_creation_cycle_time),
396 count(distinct po_line_id || '-' || po_app_date_fk_key),
397 count(distinct po_header_id || '-' || po_app_date_fk_key),
398 0,
399 0,
400 0,
401 count(*),
402 sum(decode(sign(time2.CDAY_CALENDAR_DATE-
403 time3.CDAY_CALENDAR_DATE),1,1,0)),
404 sum(greatest(pod.Potential_Svg_G,0)),
405 sum(least(pod.Potential_Svg_G,0)),
406 p_quarter,
407 item.IORG_Purchaseable_Flag
408 from poa_edw_po_dist_f pod,
409 edw_items_m item,
410 edw_organization_m org,
411 edw_trd_partner_m tp,
412 edw_time_m time1,
413 edw_time_m time2,
414 edw_time_m time3
415 where pod.ITEM_FK_KEY = item.IREV_ITEM_REVISION_PK_KEY
416 and pod.SHIP_TO_ORG_FK_KEY = org.ORGA_ORGANIZATION_PK_KEY
417 and pod.SUPPLIER_SITE_FK_KEY = tp.TPLO_TPARTNER_LOC_PK_KEY
418 and pod.PO_APP_DATE_FK_KEY = time1.CDAY_CAL_DAY_PK_KEY
419 and time1.CDAY_CALENDAR_DATE between p_start and p_end
420 and pod.PO_ACCEPT_DATE_FK_KEY = time2.CDAY_CAL_DAY_PK_KEY
421 and pod.ACCPT_DUE_DATE_FK_KEY = time3.CDAY_CAL_DAY_PK_KEY
422 group by org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
423 org.Orga_Organization_PK_Key, org.Orga_Name,
424 item.Ci11_Category_PK_Key, item.CI11_NAME,
425 tp.TPRT_Trade_Partner_PK_Key, tp.TPRT_NAME,
426 item.IORG_Purchaseable_Flag
427 UNION ALL
428 select 0, 0, 0, 0,
429 org.oper_operating_unit_pk_key,
430 org.oper_name,
431 org.Orga_Organization_PK_Key,
432 org.Orga_Name,
433 item.ci11_category_pk_key,
434 item.ci11_name,
435 tp.tprt_trade_partner_pk_key,
436 tp.tprt_name,
437 0, 0, 0,
438 sum(pod.amt_purchased_g),
439 count(*),
440 avg(fii_time_wh_api.today-time2.CDAY_CALENDAR_DATE),
441 0, 0, 0, 0,
442 p_quarter,
443 item.IORG_Purchaseable_Flag
444 from poa_edw_po_dist_f pod,
445 edw_items_m item,
446 edw_organization_m org,
450 where pod.ITEM_FK_KEY = item.IREV_ITEM_REVISION_PK_KEY
447 edw_trd_partner_m tp,
448 edw_time_m time1,
449 edw_time_m time2
451 and pod.SHIP_TO_ORG_FK_KEY = org.ORGA_ORGANIZATION_PK_KEY
452 and pod.SUPPLIER_SITE_FK_KEY = tp.TPLO_TPARTNER_LOC_PK_KEY
453 and pod.PO_APP_DATE_FK_KEY = time1.CDAY_CAL_DAY_PK_KEY
454 and time1.CDAY_CALENDAR_DATE between p_start and p_end
455 and pod.PO_ACCEPT_DATE_FK_KEY = 0
456 and pod.ACCPT_DUE_DATE_FK_KEY <> 0
457 and pod.ACCPT_DUE_DATE_FK_KEY = time2.CDAY_CAL_DAY_PK_KEY
458 and time2.CDAY_CALENDAR_DATE < fii_time_wh_api.today
459 group by org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
460 org.Orga_Organization_PK_Key, org.Orga_Name,
461 item.Ci11_Category_PK_Key, item.CI11_NAME,
462 tp.TPRT_Trade_Partner_PK_Key, tp.TPRT_NAME,
463 item.IORG_Purchaseable_Flag;
464
465 p_count := sql%rowcount;
466
467 insert into POA_PORTAL_PDIST1 (
468 SUM_PURCHASES,
469 SUM_LEAKAGE,
470 SUM_NON_CONTRACT,
471 SUM_CONTRACT,
472 CATEGORY_PK_KEY,
473 CATEGORY_NAME,
474 ITEM_PK_KEY,
475 ITEM_NAME,
476 AVG_CYCLE_TIME,
477 CNT_PO_LINES,
478 CNT_PO_HEADERS,
479 CNT_LATE_SUPPLIER_CONF,
480 AVG_LATE_SUP_CONF_AGE,
481 WEIGHT_AVG_CYCLE,
482 WEIGHT_AVG_CONF,
483 QUARTER,
484 ITEM_PURCHASABLE_FLAG)
485 select sum(pod.amt_purchased_g),
486 sum(pod.amt_leakage_g),
487 sum(pod.amt_noncontract_g),
488 sum(pod.amt_contract_g),
489 item.ci11_category_pk_key,
490 item.ci11_name,
491 item.Item_Item_Number_PK_Key,
492 item.ITEM_ITEM_NAME,
493 avg(pod.po_creation_cycle_time),
494 count(distinct po_line_id || '-' || po_app_date_fk_key),
495 count(distinct po_header_id || '-' || po_app_date_fk_key),
496 sum(decode(sign(time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE),
497 1,1,0)),
498 decode(sum(decode(sign(time2.CDAY_CALENDAR_DATE-
499 time3.CDAY_CALENDAR_DATE),1,1,0)),
500 0,0,
501 sum(decode(sign(time2.CDAY_CALENDAR_DATE-
502 time3.CDAY_CALENDAR_DATE),1,
503 time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE,0)) /
504 sum(decode(sign(time2.CDAY_CALENDAR_DATE-
505 time3.CDAY_CALENDAR_DATE),1,1,0))),
506 count(*),
507 sum(decode(sign(time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE),
508 1,1,0)),
509 p_quarter,
510 item.IORG_Purchaseable_Flag
511 from poa_edw_po_dist_f pod,
512 edw_items_m item,
513 edw_time_m time1,
514 edw_time_m time2,
515 edw_time_m time3
516 where pod.ITEM_FK_KEY = item.IREV_ITEM_REVISION_PK_KEY
517 and pod.PO_APP_DATE_FK_KEY = time1.CDAY_CAL_DAY_PK_KEY
518 and time1.CDAY_CALENDAR_DATE between p_start and p_end
519 and pod.PO_ACCEPT_DATE_FK_KEY = time2.CDAY_CAL_DAY_PK_KEY
520 and pod.ACCPT_DUE_DATE_FK_KEY = time3.CDAY_CAL_DAY_PK_KEY
521 group by item.Ci11_Category_PK_Key, item.CI11_NAME,
522 item.Item_Item_Number_PK_Key, item.ITEM_ITEM_NAME,
523 item.IORG_Purchaseable_Flag;
524
525 p_count := p_count + sql%rowcount;
526
527 insert into POA_PORTAL_PDIST2 (
528 SUM_PURCHASES,
529 SUM_LEAKAGE,
530 SUM_NON_CONTRACT,
531 SUPPLIER_PK_KEY,
532 SUPPLIER_NAME,
533 SUPPLIER_SITE_PK_KEY,
534 SUPPLIER_SITE_NAME,
535 AVG_CYCLE_TIME,
536 CNT_PO_LINES,
537 CNT_PO_HEADERS,
538 CNT_LATE_SUPPLIER_CONF,
539 AVG_LATE_SUP_CONF_AGE,
540 WEIGHT_AVG_CYCLE,
541 WEIGHT_AVG_CONF,
542 QUARTER,
543 ITEM_PURCHASABLE_FLAG)
544 select sum(pod.amt_purchased_g),
545 sum(pod.amt_leakage_g),
546 sum(pod.amt_noncontract_g),
547 tp.tprt_trade_partner_pk_key,
548 tp.tprt_name,
549 tp.TPLO_TPartner_Loc_PK_Key,
550 tp.TPLO_NAME,
551 avg(pod.po_creation_cycle_time),
552 count(distinct po_line_id || '-' || po_app_date_fk_key),
553 count(distinct po_header_id || '-' || po_app_date_fk_key),
554 sum(decode(sign(time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE),
555 1,1,0)),
556 decode(sum(decode(sign(time2.CDAY_CALENDAR_DATE-
557 time3.CDAY_CALENDAR_DATE),1,1,0)),
558 0,0,
559 sum(decode(sign(time2.CDAY_CALENDAR_DATE-
560 time3.CDAY_CALENDAR_DATE),1,
561 time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE,0)) /
562 sum(decode(sign(time2.CDAY_CALENDAR_DATE-
563 time3.CDAY_CALENDAR_DATE),1,1,0))),
564 count(*),
565 sum(decode(sign(time2.CDAY_CALENDAR_DATE-time3.CDAY_CALENDAR_DATE),
566 1,1,0)),
567 p_quarter,
568 item.IORG_Purchaseable_Flag
569 from poa_edw_po_dist_f pod,
570 edw_trd_partner_m tp,
571 edw_time_m time1,
575 where pod.SUPPLIER_SITE_FK_KEY = tp.TPLO_TPARTNER_LOC_PK_KEY
572 edw_time_m time2,
573 edw_time_m time3,
574 edw_items_m item
576 and pod.ITEM_FK_KEY = item.IREV_ITEM_REVISION_PK_KEY
577 and pod.PO_APP_DATE_FK_KEY = time1.CDAY_CAL_DAY_PK_KEY
578 and time1.CDAY_CALENDAR_DATE between p_start and p_end
579 and pod.PO_ACCEPT_DATE_FK_KEY = time2.CDAY_CAL_DAY_PK_KEY
580 and pod.ACCPT_DUE_DATE_FK_KEY = time3.CDAY_CAL_DAY_PK_KEY
581 group by tp.tprt_trade_partner_pk_key, tp.tprt_name,
582 tp.TPLO_TPartner_Loc_PK_Key, tp.TPLO_NAME,
583 item.IORG_Purchaseable_Flag;
584 success := 'ok';
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 success := 'Error in insert_pd: ' || SQLERRM;
589 RAISE;
590 end insert_rows_pd;
591
592
593 PROCEDURE insert_rows_sr( p_start IN DATE,
594 p_end IN DATE,
595 p_count OUT NOCOPY NUMBER,
596 success OUT NOCOPY varchar2) IS
597 BEGIN
598 insert into POA_PORTAL_RISK_SUMMARY (
599 Status,
600 Risk_Category,
601 Supplier_PK_Key,
602 Supplier_Name,
603 Total_Range_Low,
604 Total_Range_High,
605 Price_Range_Low,
606 Price_Range_High,
607 Quality_Range_Low,
608 Quality_Range_High,
609 Delivery_Range_Low,
610 Delivery_Range_High,
611 Service_Range_Low,
612 Service_Range_High,
613 Total_Score,
614 Price_Score,
615 Quality_Score,
616 Delivery_Score,
617 Service_Score,
618 Purchase_Amount)
619 select 'Problem Suppliers', 1,
620 Supplier.tprt_trade_partner_pk_key,
621 Supplier.tprt_name,
622 POA_PORTAL_SUP_RISK_IND.get_range1_low(1),
623 POA_PORTAL_SUP_RISK_IND.get_range1_high(1),
624 POA_PORTAL_SUP_RISK_IND.get_range1_low(2),
625 POA_PORTAL_SUP_RISK_IND.get_range1_high(2),
626 POA_PORTAL_SUP_RISK_IND.get_range1_low(3),
627 POA_PORTAL_SUP_RISK_IND.get_range1_high(3),
628 POA_PORTAL_SUP_RISK_IND.get_range1_low(4),
629 POA_PORTAL_SUP_RISK_IND.get_range1_high(4),
630 POA_PORTAL_SUP_RISK_IND.get_range1_low(5),
631 POA_PORTAL_SUP_RISK_IND.get_range1_high(5),
632 decode(sign(round(avg(Total_Score)) -
633 POA_PORTAL_SUP_RISK_IND.get_range1_low(1)),
634 -1,-999,
635 0, avg(Total_Score),
636 1, decode(sign(round(avg(Total_Score)) -
637 POA_PORTAL_SUP_RISK_IND.get_range1_high(1)),
638 -1, avg(Total_Score),
639 0, avg(Total_Score), -999)),
640 decode(sign(round(avg(Price_Score)) -
641 POA_PORTAL_SUP_RISK_IND.get_range1_low(2)),
642 -1,-999,
643 0, avg(Price_Score),
644 1, decode(sign(round(avg(Price_Score)) -
645 POA_PORTAL_SUP_RISK_IND.get_range1_high(2)),
646 -1, avg(Price_Score),
647 0, avg(Price_Score), -999)),
648 decode(sign(round(avg(Quality_Score)) -
649 POA_PORTAL_SUP_RISK_IND.get_range1_low(3)),
650 -1,-999,
651 0, avg(Quality_Score),
652 1, decode(sign(round(avg(Quality_Score)) -
653 POA_PORTAL_SUP_RISK_IND.get_range1_high(3)),
654 -1, avg(Quality_Score),
655 0, avg(Quality_Score), -999)),
656 decode(sign(round(avg(Delivery_Score)) -
657 POA_PORTAL_SUP_RISK_IND.get_range1_low(4)),
658 -1,-999,
659 0, avg(Delivery_Score),
660 1, decode(sign(round(avg(Delivery_Score)) -
661 POA_PORTAL_SUP_RISK_IND.get_range1_high(4)),
662 -1, avg(Delivery_Score),
663 0, avg(Delivery_Score), -999)),
664 decode(sign(round(avg(Survey_Score)) -
665 POA_PORTAL_SUP_RISK_IND.get_range1_low(5)),
666 -1,-999,
667 0, avg(Survey_Score),
668 1, decode(sign(round(avg(Survey_Score)) -
669 POA_PORTAL_SUP_RISK_IND.get_range1_high(5)),
670 -1, avg(Survey_Score),
671 0, avg(Survey_Score), -999)),
672 sum(pod.amt_purchased_g)
673 from poa_edw_po_dist_f pod,
674 (select Supplier_Site,
675 avg(NVL(Price_Score,25) +
676 NVL(Quality_Score,25) +
677 NVL(Delivery_Score,25) +
678 NVL(Survey_Score,25)) Total_Score,
679 avg(NVL(Price_Score,25)) Price_Score,
680 avg(NVL(Quality_Score,25)) Quality_Score,
681 avg(NVL(Delivery_Score,25)) Delivery_Score,
682 avg(NVL(Survey_Score,25)) Survey_Score from
683 POA_REP_SUP_SCORE_V
684 group by Supplier_Site) Cstm_Msr,
685 EDW_TRD_Partner_M Supplier
686 where pod.SUPPLIER_SITE_FK_KEY = Supplier.TPLO_TPartner_Loc_PK_Key
690
687 and pod.SUPPLIER_SITE_FK_KEY = Cstm_Msr.Supplier_Site
688 group by Supplier.tprt_trade_partner_pk_key,
689 Supplier.tprt_name;
691 p_count := sql%rowcount;
692
693 insert into POA_PORTAL_RISK_SUMMARY (
694 Status,
695 Risk_Category,
696 Supplier_PK_Key,
697 Supplier_Name,
698 Total_Range_Low,
699 Total_Range_High,
700 Price_Range_Low,
701 Price_Range_High,
702 Quality_Range_Low,
703 Quality_Range_High,
704 Delivery_Range_Low,
705 Delivery_Range_High,
706 Service_Range_Low,
707 Service_Range_High,
708 Total_Score,
709 Price_Score,
710 Quality_Score,
711 Delivery_Score,
712 Service_Score,
713 Purchase_Amount)
714 select 'At Risk Suppliers', 2,
715 Supplier.tprt_trade_partner_pk_key,
716 Supplier.tprt_name,
717 POA_PORTAL_SUP_RISK_IND.get_range2_low(1),
718 POA_PORTAL_SUP_RISK_IND.get_range2_high(1),
719 POA_PORTAL_SUP_RISK_IND.get_range2_low(2),
720 POA_PORTAL_SUP_RISK_IND.get_range2_high(2),
721 POA_PORTAL_SUP_RISK_IND.get_range2_low(3),
722 POA_PORTAL_SUP_RISK_IND.get_range2_high(3),
723 POA_PORTAL_SUP_RISK_IND.get_range2_low(4),
724 POA_PORTAL_SUP_RISK_IND.get_range2_high(4),
725 POA_PORTAL_SUP_RISK_IND.get_range2_low(5),
726 POA_PORTAL_SUP_RISK_IND.get_range2_high(5),
727 decode(sign(round(avg(Total_Score)) -
728 POA_PORTAL_SUP_RISK_IND.get_range2_low(1)),
729 -1,-999,
730 0, avg(Total_Score),
731 1, decode(sign(round(avg(Total_Score)) -
732 POA_PORTAL_SUP_RISK_IND.get_range2_high(1)),
733 -1, avg(Total_Score),
734 0, avg(Total_Score), -999)),
735 decode(sign(round(avg(Price_Score)) -
736 POA_PORTAL_SUP_RISK_IND.get_range2_low(2)),
737 -1,-999,
738 0, avg(Price_Score),
739 1, decode(sign(round(avg(Price_Score)) -
740 POA_PORTAL_SUP_RISK_IND.get_range2_high(2)),
741 -1, avg(Price_Score),
742 0, avg(Price_Score), -999)),
743 decode(sign(round(avg(Quality_Score)) -
744 POA_PORTAL_SUP_RISK_IND.get_range2_low(3)),
745 -1,-999,
746 0, avg(Quality_Score),
747 1, decode(sign(round(avg(Quality_Score)) -
748 POA_PORTAL_SUP_RISK_IND.get_range2_high(3)),
749 -1, avg(Quality_Score),
750 0, avg(Quality_Score), -999)),
751 decode(sign(round(avg(Delivery_Score)) -
752 POA_PORTAL_SUP_RISK_IND.get_range2_low(4)),
753 -1,-999,
754 0, avg(Delivery_Score),
755 1, decode(sign(round(avg(Delivery_Score)) -
756 POA_PORTAL_SUP_RISK_IND.get_range2_high(4)),
757 -1, avg(Delivery_Score),
758 0, avg(Delivery_Score), -999)),
759 decode(sign(round(avg(Survey_Score)) -
760 POA_PORTAL_SUP_RISK_IND.get_range2_low(5)),
761 -1,-999,
762 0, avg(Survey_Score),
763 1, decode(sign(round(avg(Survey_Score)) -
764 POA_PORTAL_SUP_RISK_IND.get_range2_high(5)),
765 -1, avg(Survey_Score),
766 0, avg(Survey_Score), -999)),
767 sum(pod.amt_purchased_g)
768 from poa_edw_po_dist_f pod,
769 (select Supplier_Site,
770 avg(NVL(Price_Score,25) +
771 NVL(Quality_Score,25) +
772 NVL(Delivery_Score,25) +
773 NVL(Survey_Score,25)) Total_Score,
774 avg(NVL(Price_Score,25)) Price_Score,
775 avg(NVL(Quality_Score,25)) Quality_Score,
776 avg(NVL(Delivery_Score,25)) Delivery_Score,
777 avg(NVL(Survey_Score,25)) Survey_Score from
778 POA_REP_SUP_SCORE_V
779 group by Supplier_Site) Cstm_Msr,
780 EDW_TRD_Partner_M Supplier
781 where pod.SUPPLIER_SITE_FK_KEY = Supplier.TPLO_TPartner_Loc_PK_Key
782 and pod.SUPPLIER_SITE_FK_KEY = Cstm_Msr.Supplier_Site
783 group by Supplier.tprt_trade_partner_pk_key,
784 Supplier.tprt_name;
785
786 p_count := p_count + sql%rowcount;
787
788 insert into POA_PORTAL_RISK_SUMMARY (
789 Status,
790 Risk_Category,
791 Supplier_PK_Key,
792 Supplier_Name,
793 Total_Range_Low,
794 Total_Range_High,
795 Price_Range_Low,
796 Price_Range_High,
797 Quality_Range_Low,
798 Quality_Range_High,
799 Delivery_Range_Low,
800 Delivery_Range_High,
801 Service_Range_Low,
802 Service_Range_High,
803 Total_Score,
804 Price_Score,
805 Quality_Score,
806 Delivery_Score,
807 Service_Score,
808 Purchase_Amount)
809 select 'Good Suppliers', 3,
813 POA_PORTAL_SUP_RISK_IND.get_range3_high(1),
810 Supplier.tprt_trade_partner_pk_key,
811 Supplier.tprt_name,
812 POA_PORTAL_SUP_RISK_IND.get_range3_low(1),
814 POA_PORTAL_SUP_RISK_IND.get_range3_low(2),
815 POA_PORTAL_SUP_RISK_IND.get_range3_high(2),
816 POA_PORTAL_SUP_RISK_IND.get_range3_low(3),
817 POA_PORTAL_SUP_RISK_IND.get_range3_high(3),
818 POA_PORTAL_SUP_RISK_IND.get_range3_low(4),
819 POA_PORTAL_SUP_RISK_IND.get_range3_high(4),
820 POA_PORTAL_SUP_RISK_IND.get_range3_low(5),
821 POA_PORTAL_SUP_RISK_IND.get_range3_high(5),
822 decode(sign(round(avg(Total_Score)) -
823 POA_PORTAL_SUP_RISK_IND.get_range3_low(1)),
824 -1,-999,
825 0, avg(Total_Score),
826 1, decode(sign(round(avg(Total_Score)) -
827 POA_PORTAL_SUP_RISK_IND.get_range3_high(1)),
828 -1, avg(Total_Score),
829 0, avg(Total_Score), -999)),
830 decode(sign(round(avg(Price_Score)) -
831 POA_PORTAL_SUP_RISK_IND.get_range3_low(2)),
832 -1,-999,
833 0, avg(Price_Score),
834 1, decode(sign(round(avg(Price_Score)) -
835 POA_PORTAL_SUP_RISK_IND.get_range3_high(2)),
836 -1, avg(Price_Score),
837 0, avg(Price_Score), -999)),
838 decode(sign(round(avg(Quality_Score)) -
839 POA_PORTAL_SUP_RISK_IND.get_range3_low(3)),
840 -1,-999,
841 0, avg(Quality_Score),
842 1, decode(sign(round(avg(Quality_Score)) -
843 POA_PORTAL_SUP_RISK_IND.get_range3_high(3)),
844 -1, avg(Quality_Score),
845 0, avg(Quality_Score), -999)),
846 decode(sign(round(avg(Delivery_Score)) -
847 POA_PORTAL_SUP_RISK_IND.get_range3_low(4)),
848 -1,-999,
849 0, avg(Delivery_Score),
850 1, decode(sign(round(avg(Delivery_Score)) -
851 POA_PORTAL_SUP_RISK_IND.get_range3_high(4)),
852 -1, avg(Delivery_Score),
853 0, avg(Delivery_Score), -999)),
854 decode(sign(round(avg(Survey_Score)) -
855 POA_PORTAL_SUP_RISK_IND.get_range3_low(5)),
856 -1,-999,
857 0, avg(Survey_Score),
858 1, decode(sign(round(avg(Survey_Score)) -
859 POA_PORTAL_SUP_RISK_IND.get_range3_high(5)),
860 -1, avg(Survey_Score),
861 0, avg(Survey_Score), -999)),
862 sum(pod.amt_purchased_g)
863 from poa_edw_po_dist_f pod,
864 (select Supplier_Site,
865 avg(NVL(Price_Score,25) +
866 NVL(Quality_Score,25) +
867 NVL(Delivery_Score,25) +
868 NVL(Survey_Score,25)) Total_Score,
869 avg(NVL(Price_Score,25)) Price_Score,
870 avg(NVL(Quality_Score,25)) Quality_Score,
871 avg(NVL(Delivery_Score,25)) Delivery_Score,
872 avg(NVL(Survey_Score,25)) Survey_Score from
873 POA_REP_SUP_SCORE_V
874 group by Supplier_Site) Cstm_Msr,
875 EDW_TRD_Partner_M Supplier
876 where pod.SUPPLIER_SITE_FK_KEY = Supplier.TPLO_TPartner_Loc_PK_Key
877 and pod.SUPPLIER_SITE_FK_KEY = Cstm_Msr.Supplier_Site
878 group by Supplier.tprt_trade_partner_pk_key,
879 Supplier.tprt_name;
880
881 p_count := p_count + sql%rowcount;
882
883 insert into POA_PORTAL_RISK_IND (
884 Status,
885 Aggregate,
886 Price,
887 Quality,
888 Delivery,
889 Service,
890 Risk_Category)
891 select 'Problem Suppliers',
892 count(distinct Supplier_PK_key), 0,0,0,0,1
893 from POA_PORTAL_RISK_SUMMARY
894 where (Total_Score <> -999 and
895 Risk_Category = 1);
896
897 p_count := p_count + sql%rowcount;
898
899 insert into POA_PORTAL_RISK_IND (
900 Status,
901 Aggregate,
902 Price,
903 Quality,
904 Delivery,
905 Service,
906 Risk_Category)
907 select 'Problem Suppliers',
908 0, count(distinct Supplier_PK_key), 0,0,0,1
909 from POA_PORTAL_RISK_SUMMARY
910 where (Price_Score <> -999 and
911 Risk_Category = 1);
912
913 p_count := p_count + sql%rowcount;
914
915 insert into POA_PORTAL_RISK_IND (
916 Status,
917 Aggregate,
918 Price,
919 Quality,
920 Delivery,
921 Service,
922 Risk_Category)
923 select 'Problem Suppliers',
924 0, 0, count(distinct Supplier_PK_key), 0,0,1
925 from POA_PORTAL_RISK_SUMMARY
929 p_count := p_count + sql%rowcount;
926 where (Quality_Score <> -999 and
927 Risk_Category = 1);
928
930
931 insert into POA_PORTAL_RISK_IND (
932 Status,
933 Aggregate,
934 Price,
935 Quality,
936 Delivery,
937 Service,
938 Risk_Category)
939 select 'Problem Suppliers',
940 0, 0, 0, count(distinct Supplier_PK_key), 0,1
941 from POA_PORTAL_RISK_SUMMARY
942 where (Delivery_Score <> -999 and
943 Risk_Category = 1);
944
945 p_count := p_count + sql%rowcount;
946
947 insert into POA_PORTAL_RISK_IND (
948 Status,
949 Aggregate,
950 Price,
951 Quality,
952 Delivery,
953 Service,
954 Risk_Category)
955 select 'Problem Suppliers',
956 0, 0, 0, 0, count(distinct Supplier_PK_key),1
957 from POA_PORTAL_RISK_SUMMARY
958 where (Service_Score <> -999 and
959 Risk_Category = 1);
960
961 p_count := p_count + sql%rowcount;
962
963 insert into POA_PORTAL_RISK_IND (
964 Status,
965 Aggregate,
966 Price,
967 Quality,
968 Delivery,
969 Service,
970 Risk_Category)
971 select 'At Risk Suppliers',
972 count(distinct Supplier_PK_key), 0,0,0,0,2
973 from POA_PORTAL_RISK_SUMMARY
974 where (Total_Score <> -999 and
975 Risk_Category = 2);
976
977 p_count := p_count + sql%rowcount;
978
979 insert into POA_PORTAL_RISK_IND (
980 Status,
981 Aggregate,
982 Price,
983 Quality,
984 Delivery,
985 Service,
986 Risk_Category)
987 select 'At Risk Suppliers',
988 0, count(distinct Supplier_PK_key), 0,0,0,2
989 from POA_PORTAL_RISK_SUMMARY
990 where (Price_Score <> -999 and
991 Risk_Category = 2);
992
993 p_count := p_count + sql%rowcount;
994
995 insert into POA_PORTAL_RISK_IND (
996 Status,
997 Aggregate,
998 Price,
999 Quality,
1000 Delivery,
1001 Service,
1002 Risk_Category)
1003 select 'At Risk Suppliers',
1004 0, 0, count(distinct Supplier_PK_key), 0,0,2
1005 from POA_PORTAL_RISK_SUMMARY
1006 where (Quality_Score <> -999 and
1007 Risk_Category = 2);
1008
1009 p_count := p_count + sql%rowcount;
1010
1011 insert into POA_PORTAL_RISK_IND (
1012 Status,
1013 Aggregate,
1014 Price,
1015 Quality,
1016 Delivery,
1017 Service,
1018 Risk_Category)
1019 select 'At Risk Suppliers',
1020 0, 0, 0, count(distinct Supplier_PK_key), 0,2
1021 from POA_PORTAL_RISK_SUMMARY
1022 where (Delivery_Score <> -999 and
1023 Risk_Category = 2);
1024
1025 p_count := p_count + sql%rowcount;
1026
1027 insert into POA_PORTAL_RISK_IND (
1028 Status,
1029 Aggregate,
1030 Price,
1031 Quality,
1032 Delivery,
1033 Service,
1034 Risk_Category)
1035 select 'At Risk Suppliers',
1036 0, 0, 0, 0, count(distinct Supplier_PK_key),2
1037 from POA_PORTAL_RISK_SUMMARY
1038 where (Service_Score <> -999 and
1039 Risk_Category = 2);
1040
1041 p_count := p_count + sql%rowcount;
1042
1043 insert into POA_PORTAL_RISK_IND (
1044 Status,
1045 Aggregate,
1046 Price,
1047 Quality,
1048 Delivery,
1049 Service,
1050 Risk_Category)
1051 select 'Good Suppliers',
1052 count(distinct Supplier_PK_key), 0,0,0,0,3
1053 from POA_PORTAL_RISK_SUMMARY
1054 where (Total_Score <> -999 and
1055 Risk_Category = 3);
1056
1057 p_count := p_count + sql%rowcount;
1058
1059 insert into POA_PORTAL_RISK_IND (
1060 Status,
1061 Aggregate,
1062 Price,
1063 Quality,
1064 Delivery,
1065 Service,
1066 Risk_Category)
1067 select 'Good Suppliers',
1068 0, count(distinct Supplier_PK_key), 0,0,0,3
1069 from POA_PORTAL_RISK_SUMMARY
1070 where (Price_Score <> -999 and
1071 Risk_Category = 3);
1072
1073 p_count := p_count + sql%rowcount;
1074
1075 insert into POA_PORTAL_RISK_IND (
1076 Status,
1077 Aggregate,
1078 Price,
1079 Quality,
1080 Delivery,
1081 Service,
1082 Risk_Category)
1083 select 'Good Suppliers',
1084 0, 0, count(distinct Supplier_PK_key), 0,0,3
1085 from POA_PORTAL_RISK_SUMMARY
1086 where (Quality_Score <> -999 and
1087 Risk_Category = 3);
1088
1089 p_count := p_count + sql%rowcount;
1090
1091 insert into POA_PORTAL_RISK_IND (
1092 Status,
1093 Aggregate,
1094 Price,
1095 Quality,
1096 Delivery,
1097 Service,
1101 from POA_PORTAL_RISK_SUMMARY
1098 Risk_Category)
1099 select 'Good Suppliers',
1100 0, 0, 0, count(distinct Supplier_PK_key), 0,3
1102 where (Delivery_Score <> -999 and
1103 Risk_Category = 3);
1104
1105 p_count := p_count + sql%rowcount;
1106
1107 insert into POA_PORTAL_RISK_IND (
1108 Status,
1109 Aggregate,
1110 Price,
1111 Quality,
1112 Delivery,
1113 Service,
1114 Risk_Category)
1115 select 'Good Suppliers',
1116 0, 0, 0, 0, count(distinct Supplier_PK_key),3
1117 from POA_PORTAL_RISK_SUMMARY
1118 where (Service_Score <> -999 and
1119 Risk_Category = 3);
1120
1121 p_count := p_count + sql%rowcount;
1122 success := 'ok';
1123
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 success := 'Error in insert_sr: ' || SQLERRM;
1127 RAISE;
1128 end insert_rows_sr;
1129
1130 PROCEDURE insert_rows_cm( p_start IN DATE,
1131 p_end IN DATE,
1132 p_count OUT NOCOPY NUMBER,
1133 success OUT NOCOPY varchar2) IS
1134 BEGIN
1135
1136 insert into POA_PORTAL_SUP_RISK (
1137 Supplier_PK_Key,
1138 Supplier_Name,
1139 Supplier_Site_PK_Key,
1140 Supplier_Site_Name,
1141 Operating_Unit_PK_Key,
1142 Operating_Unit_Name,
1143 Price_Score,
1144 Quality_Score,
1145 Delivery_Score,
1146 Service_Score)
1147 select Supplier.tprt_trade_partner_pk_key,
1148 Supplier.tprt_name,
1149 Supplier.TPLO_TPartner_Loc_PK_Key,
1150 Supplier.TPLO_NAME,
1151 Organization.oper_operating_unit_pk_key,
1152 Organization.oper_name,
1153 avg(NVL(Price_Score, 25)),
1154 avg(NVL(Delivery_Score, 25)),
1155 avg(NVL(Quality_Score, 25)),
1156 avg(NVL(Survey_Score, 25))
1157 from POA_REP_CSTM_MSR_V Cstm_Msr,
1158 EDW_Organization_M Organization,
1159 EDW_TRD_Partner_M Supplier,
1160 EDW_Time_M Time
1161 where
1162 Cstm_Msr.Ship_To_Org_FK_Key = Organization.Orga_Organization_PK_Key and
1163 Cstm_Msr.Supplier_Site_FK_Key = Supplier.TPLO_TPartner_Loc_PK_Key and
1164 Cstm_Msr.Eval_Date_FK_Key = Time.CDay_Cal_Day_PK_Key and
1165 Time.CDAY_CALENDAR_DATE between p_start and p_end
1166 group by Supplier.tprt_trade_partner_pk_key,
1167 Supplier.tprt_name,
1168 Supplier.TPLO_TPartner_Loc_PK_Key,
1169 Supplier.TPLO_NAME,
1170 Organization.oper_operating_unit_pk_key,
1171 Organization.oper_name;
1172
1173 p_count := sql%rowcount;
1174
1175 success := 'ok';
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 success := 'Error in insert_pd: ' || SQLERRM;
1180 RAISE;
1181 end insert_rows_cm;
1182
1183 PROCEDURE insert_rows_rcv( p_start IN DATE,
1184 p_end IN DATE,
1185 p_count OUT NOCOPY NUMBER,
1186 success OUT NOCOPY varchar2) IS
1187 BEGIN
1188
1189 INSERT into POA_PORTAL_RCPT_SUM (
1190 Operating_Unit_PK_Key,
1191 Operating_Unit_Name,
1192 Organization_PK_Key,
1193 Organization_Name,
1194 No_of_Lines,
1195 No_of_Headers,
1196 Total_Amount,
1197 Corrections,
1198 Corrections_Percent,
1199 Vendor_Returns_No,
1200 Vendor_Returns_Percent)
1201 SELECT org.oper_operating_unit_pk_key,
1202 org.oper_name,
1203 org.Orga_Organization_PK_Key,
1204 org.Orga_Name,
1205 to_number(NULL),
1206 to_number(NULL),
1207 sum(sp.amt_purchased_g),
1208 to_number(NULL),
1209 to_number(NULL),
1210 to_number(NULL),
1211 to_number(NULL)
1212 FROM poa_edw_sup_perf_f sp,
1213 edw_organization_m org,
1214 edw_time_m time
1215 WHERE sp.SHIP_TO_ORG_FK_KEY = org.ORGA_ORGANIZATION_PK_KEY
1216 and sp.DATE_DIM_FK_KEY = time.CDAY_CAL_DAY_PK_KEY
1217 and sp.NUM_RECEIPT_LINES <> 0
1218 and time.CDAY_CALENDAR_DATE between FII_TIME_WH_API.ent_cycq_start
1219 and FII_TIME_WH_API.today
1220 GROUP BY org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
1221 org.Orga_Organization_PK_Key, org.Orga_Name
1222 UNION ALL
1223 SELECT org.oper_operating_unit_pk_key,
1224 org.oper_name,
1225 org.Orga_Organization_PK_Key,
1226 org.Orga_Name,
1227 sum(DECODE(lookup.LUCD_Lookup_code, 'RECEIVE', 1, 0)),
1228 count(distinct DECODE(lookup.LUCD_LOOKUP_CODE, 'RECEIVE', rcv.receipt_num_inst, null)),
1229 to_number(NULL),
1230 count(distinct DECODE(lookup.LUCD_Lookup_Code, 'CORRECT', rcv.receipt_num_inst, null)),
1231 to_number(NULL),
1232 count(distinct DECODE(lookup.LUCD_Lookup_Code, 'RETURN TO VENDOR', rcv.receipt_num_inst, null)),
1233 to_number(NULL)
1234 FROM POA_EDW_RCV_TXNS_F Rcv,
1235 edw_organization_m org,
1236 EDW_Lookup_M lookup,
1237 edw_time_m time
1238 WHERE rcv.RCV_Del_To_Org_FK_Key = org.ORGA_ORGANIZATION_PK_KEY
1239 and rcv.TXn_Type_FK_Key = lookup.lucd_lookup_Code_PK_Key
1240 and rcv.TXN_Creat_FK_Key = time.CDAY_CAL_DAY_PK_KEY
1241 and time.CDAY_CALENDAR_DATE between FII_TIME_WH_API.ent_cycq_start
1242 and FII_TIME_WH_API.today
1243 GROUP BY org.Oper_Operating_Unit_PK_Key, org.OPER_NAME,
1244 org.Orga_Organization_PK_Key, org.Orga_Name;
1245
1246 p_count := sql%rowcount;
1247
1248 success := 'ok';
1249
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 success := 'Error in insert_rcv: ' || SQLERRM;
1253 RAISE;
1254 end insert_rows_rcv;
1255
1256 END POA_PORTAL_POPULATE_C;