eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ISC_DBI_MSC_OBJECTS_C dependencies on ISC_DBI_TMP_PLANS

Line 262: FROM isc_dbi_tmp_plans tmp

258: l_plan_id NUMBER;
259:
260: CURSOR Obsolete_Plans IS
261: SELECT tmp.plan_id
262: FROM isc_dbi_tmp_plans tmp
263: WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
264:
265: BEGIN
266:

Line 270: -- Insert the plans need to be collected into ISC_DBI_TMP_PLANS

266:
267: l_count := 0;
268:
269: -- ------------------------------------------------------------
270: -- Insert the plans need to be collected into ISC_DBI_TMP_PLANS
271: -- ------------------------------------------------------------
272:
273: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
274: FII_UTIL.Start_Timer;

Line 276: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';

272:
273: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
274: FII_UTIL.Start_Timer;
275:
276: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
277: EXECUTE IMMEDIATE l_trunc_stmt;
278:
279: FII_UTIL.Stop_Timer;
280: FII_UTIL.Print_Timer('Truncated the temp table in');

Line 289: l_stmt := 'INSERT INTO isc_dbi_tmp_plans (' ||

285:
286: BIS_COLLECTION_UTILITIES.put_line('Begin to load into the temp table.');
287: FII_UTIL.Start_Timer;
288:
289: l_stmt := 'INSERT INTO isc_dbi_tmp_plans (' ||
290: 'PLAN_ID, PLAN_NAME, OLD_DATA_START_DATE, DATA_START_DATE, ' ||
291: 'INSTANCE_ID, PLAN_USAGE) ' ||
292: 'SELECT setup.plan_id, setup.plan_name, p.data_start_date, setup.data_start_date, inst.instance_id, '||
293: 'sum(plan_usage) '||

Line 321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');

317: FII_UTIL.Print_Timer('Retrieved '|| l_count || ' plans from setup tables in');
318: BIS_COLLECTION_UTILITIES.Put_Line(' ');
319:
320: BIS_COLLECTION_UTILITIES.Put_Line(' ');
321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322: FII_UTIL.Start_Timer;
323:
324: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
325: TABNAME => 'ISC_DBI_TMP_PLANS');

Line 325: TABNAME => 'ISC_DBI_TMP_PLANS');

321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322: FII_UTIL.Start_Timer;
323:
324: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
325: TABNAME => 'ISC_DBI_TMP_PLANS');
326:
327: FII_UTIL.Stop_Timer;
328: FII_UTIL.Print_Timer('Analyzed table ISC_DBI_TMP_PLANS in ');
329:

Line 328: FII_UTIL.Print_Timer('Analyzed table ISC_DBI_TMP_PLANS in ');

324: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
325: TABNAME => 'ISC_DBI_TMP_PLANS');
326:
327: FII_UTIL.Stop_Timer;
328: FII_UTIL.Print_Timer('Analyzed table ISC_DBI_TMP_PLANS in ');
329:
330: -- Clean up the obsolete planned data
331:
332: l_count := 0;

Line 348: FROM isc_dbi_tmp_plans tmp

344:
345: /*
346: SELECT count(*)
347: INTO l_count
348: FROM isc_dbi_tmp_plans tmp
349: WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
350: */
351:
352: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' plans need to be collected.');

Line 382: SELECT plan_id FROM isc_dbi_tmp_plans tmp

378: part_value_exists EXCEPTION;
379: PRAGMA EXCEPTION_INIT(part_value_exists, -14312);
380:
381: CURSOR Plan_List IS
382: SELECT plan_id FROM isc_dbi_tmp_plans tmp
383: WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
384:
385: BEGIN
386:

Line 546: 'FROM isc_dbi_tmp_plans tmp,msc_plans' || g_db_link || ' p '||

542: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
543: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
544: 'null PEGGING_ID,null END_PEGGING_ID,'||
545: 'p.created_by,p.creation_date,p.last_updated_by,p.last_update_date,p.last_update_login,1 union_flag '||
546: 'FROM isc_dbi_tmp_plans tmp,msc_plans' || g_db_link || ' p '||
547: 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
548: 'AND tmp.plan_name = p.compile_designator '||
549: 'AND tmp.instance_id = p.sr_instance_id UNION ALL ' ||
550: 'SELECT /*+ DRIVING_SITE (po) */ po.plan_id,po.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||

Line 576: 'FROM isc_dbi_tmp_plans tmp,msc_plan_organizations' || g_db_link || ' po '||

572: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
573: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
574: 'null PEGGING_ID,null END_PEGGING_ID,'||
575: 'po.created_by,po.creation_date,po.last_updated_by,po.last_update_date,po.last_update_login,2 union_flag '||
576: 'FROM isc_dbi_tmp_plans tmp,msc_plan_organizations' || g_db_link || ' po '||
577: 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
578: 'AND tmp.plan_id = po.plan_id '||
579: 'AND tmp.instance_id = po.sr_instance_id UNION ALL '||
580: 'SELECT /*+ DRIVING_SITE (pb) */ pb.plan_id,pb.organization_id,null SR_INVENTORY_ITEM_ID,null UOM_CODE,'||

Line 606: 'FROM isc_dbi_tmp_plans tmp,msc_plan_buckets' || g_db_link || ' pb '||

602: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
603: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
604: 'null PEGGING_ID,null END_PEGGING_ID,'||
605: 'pb.created_by,pb.creation_date,pb.last_updated_by,pb.last_update_date,pb.last_update_login,3 union_flag '||
606: 'FROM isc_dbi_tmp_plans tmp,msc_plan_buckets' || g_db_link || ' pb '||
607: 'WHERE nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
608: 'AND tmp.plan_id = pb.plan_id '||
609: 'AND tmp.instance_id = pb.sr_instance_id UNION ALL '||
610: 'SELECT /*+ DRIVING_SITE (s) parallel(it) parallel(s) parallel(r1) parallel(its2) parallel(tp) */ s.plan_id,s.organization_id,it.sr_inventory_item_id,it.uom_code,'||

Line 636: 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link ||' it,'||

632: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
633: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
634: 'null PEGGING_ID,null END_PEGGING_ID,'||
635: 's.created_by,s.creation_date,s.last_updated_by,s.last_update_date,s.last_update_login,4 union_flag '||
636: 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link ||' it,'||
637: 'msc_routings' || g_db_link || ' r1,msc_item_suppliers' || g_db_link || ' its2,'||
638: 'msc_tp_id_lid' || g_db_link || ' tp '||
639: 'WHERE tmp.plan_id = s.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
640: 'AND tmp.instance_id = s.sr_instance_id '||

Line 684: 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link || ' it,'||

680: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
681: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
682: 'null PEGGING_ID,null END_PEGGING_ID,'||
683: 's.created_by,s.creation_date,s.last_updated_by,s.last_update_date,s.last_update_login,4 union_flag '||
684: 'FROM isc_dbi_tmp_plans tmp,msc_supplies'|| g_db_link || ' s,msc_system_items'|| g_db_link || ' it,'||
685: 'msc_process_effectivity'|| g_db_link || ' process,msc_routings'|| g_db_link || ' r2,'||
686: 'msc_item_suppliers'|| g_db_link || ' its1,msc_tp_id_lid'|| g_db_link || ' tp '||
687: 'WHERE tmp.plan_id = s.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
688: 'AND tmp.instance_id = s.sr_instance_id '||

Line 735: 'FROM isc_dbi_tmp_plans tmp,msc_bis_inv_detail' ||g_db_link|| ' inv,msc_system_items' ||g_db_link|| ' it '||

731: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
732: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
733: 'null PEGGING_ID,null END_PEGGING_ID,'||
734: 'inv.created_by,inv.creation_date,inv.last_updated_by,inv.last_update_date,inv.last_update_login,5 union_flag '||
735: 'FROM isc_dbi_tmp_plans tmp,msc_bis_inv_detail' ||g_db_link|| ' inv,msc_system_items' ||g_db_link|| ' it '||
736: 'WHERE tmp.plan_id = inv.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
737: 'AND tmp.instance_id = inv.sr_instance_id AND nvl(inv.period_type,0)=0 '||
738: 'AND inv.plan_id = it.plan_id '||
739: 'AND inv.inventory_item_id = it.inventory_item_id '||

Line 770: 'FROM isc_dbi_tmp_plans tmp,msc_bis_res_summary' || g_db_link|| ' res,msc_trading_partners' || g_db_link||' org '||

766: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
767: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
768: 'null PEGGING_ID,null END_PEGGING_ID,'||
769: 'res.created_by,res.creation_date,res.last_updated_by,res.last_update_date,res.last_update_login,6 union_flag '||
770: 'FROM isc_dbi_tmp_plans tmp,msc_bis_res_summary' || g_db_link|| ' res,msc_trading_partners' || g_db_link||' org '||
771: 'WHERE tmp.plan_id = res.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
772: 'AND tmp.instance_id = res.sr_instance_id '||
773: 'AND res.organization_id = org.sr_tp_id '||
774: 'AND org.partner_type = 3 '||

Line 803: 'FROM isc_dbi_tmp_plans tmp,msc_exception_details'|| g_db_link||' ex,msc_system_items'||g_db_link||' it,'||

799: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
800: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
801: 'null PEGGING_ID,null END_PEGGING_ID,'||
802: 'ex.created_by,ex.creation_date,ex.last_updated_by,ex.last_update_date,ex.last_update_login,7 union_flag '||
803: 'FROM isc_dbi_tmp_plans tmp,msc_exception_details'|| g_db_link||' ex,msc_system_items'||g_db_link||' it,'||
804: 'MSC_TP_ID_LID'|| g_db_link||' tp,MSC_TP_SITE_ID_LID'|| g_db_link||' tp_site,MSC_TRADING_PARTNERS'|| g_db_link||' org '||
805: 'WHERE tmp.plan_id = ex.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
806: 'AND tmp.instance_id = ex.sr_instance_id '||
807: 'AND ex.sr_instance_id = org.sr_instance_id '||

Line 846: 'FROM isc_dbi_tmp_plans tmp,msc_demands' ||g_db_link|| ' d,msc_system_items'||g_db_link|| ' it '||

842: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
843: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
844: 'null PEGGING_ID,null END_PEGGING_ID,'||
845: 'd.created_by,d.creation_date,d.last_updated_by,d.last_update_date,d.last_update_login,8 union_flag '||
846: 'FROM isc_dbi_tmp_plans tmp,msc_demands' ||g_db_link|| ' d,msc_system_items'||g_db_link|| ' it '||
847: 'WHERE tmp.plan_id = d.plan_id AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
848: 'AND tmp.instance_id = d.sr_instance_id '||
849: 'AND d.plan_id = it.plan_id '||
850: 'AND d.inventory_item_id = it.inventory_item_id '||

Line 907: 'FROM isc_dbi_tmp_plans tmp,msc_full_pegging' ||g_db_link|| ' pg '||

903: 'null YEAR_START_DATE,null QUARTER_START_DATE,null PERIOD_TYPE,null PERIOD_YEAR,'||
904: 'null PERIOD_NUM,null QUARTER_NUM,null ENTERED_PERIOD_NAME,null ADJUSTMENT_PERIOD_FLAG,'||
905: 'pg.pegging_id,pg.end_pegging_id,'||
906: 'pg.created_by,pg.creation_date,pg.last_updated_by,pg.last_update_date,pg.last_update_login,10 union_flag '||
907: 'FROM isc_dbi_tmp_plans tmp,msc_full_pegging' ||g_db_link|| ' pg '||
908: 'WHERE tmp.plan_id = pg.plan_id '||
909: 'AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
910: 'AND tmp.instance_id = pg.sr_instance_id';
911:

Line 927: UPDATE isc_dbi_tmp_plans tmp SET constrained_flag = (select constrained_flag from isc_dbi_plans p where p.plan_id = tmp.plan_id);

923: FII_UTIL.Stop_Timer;
924: FII_UTIL.Print_Timer('Loaded the base tables in');
925: BIS_COLLECTION_UTILITIES.Put_Line(' ');
926:
927: UPDATE isc_dbi_tmp_plans tmp SET constrained_flag = (select constrained_flag from isc_dbi_plans p where p.plan_id = tmp.plan_id);
928: COMMIT;
929:
930: FII_UTIL.Start_Timer;
931:

Line 966: isc_dbi_tmp_plans tmp

962: fii_currency.get_global_rate_primary(gsb.currency_code, g_snapshot_date) RATE,
963: fii_currency.get_global_rate_secondary(gsb.currency_code, g_snapshot_date) RATE2
964: FROM (SELECT distinct organization_id
965: FROM isc_dbi_plan_organizations ido,
966: isc_dbi_tmp_plans tmp
967: WHERE bitand(tmp.plan_usage, 2) = 2
968: AND ido.plan_id = tmp.plan_id) org,
969: GL_SETS_OF_BOOKS gsb,
970: HR_ORGANIZATION_INFORMATION hoi

Line 1012: FROM isc_dbi_plans p, isc_dbi_tmp_plans tmp

1008: FII_UTIL.Start_Timer;
1009:
1010: SELECT min(p.data_start_date),max(p.cutoff_date)
1011: INTO l_min, l_max
1012: FROM isc_dbi_plans p, isc_dbi_tmp_plans tmp
1013: WHERE p.plan_id = tmp.plan_id;
1014:
1015: FII_UTIL.Stop_Timer;
1016: FII_UTIL.Print_Timer('Retrieved the min and max date in ');

Line 1059: isc_dbi_tmp_plans tmp,

1055:
1056: CURSOR Dangling_Items IS
1057: SELECT distinct s.sr_inventory_item_id, s.organization_id
1058: FROM isc_dbi_supplies_f s,
1059: isc_dbi_tmp_plans tmp,
1060: eni_oltp_item_star item
1061: WHERE s.plan_id = tmp.plan_id
1062: AND s.sr_inventory_item_id = item.inventory_item_id(+)
1063: AND s.organization_id = item.organization_id(+)

Line 1068: isc_dbi_tmp_plans tmp,

1064: AND item.inventory_item_id IS NULL
1065: UNION
1066: SELECT distinct d.sr_inventory_item_id, d.organization_id
1067: FROM isc_dbi_demands_f d,
1068: isc_dbi_tmp_plans tmp,
1069: eni_oltp_item_star item
1070: WHERE d.plan_id = tmp.plan_id
1071: AND d.sr_inventory_item_id = item.inventory_item_id(+)
1072: AND d.organization_id = item.organization_id(+)

Line 1077: isc_dbi_tmp_plans tmp,

1073: AND item.inventory_item_id IS NULL
1074: UNION
1075: SELECT distinct d.sr_inventory_item_id, d.organization_id
1076: FROM isc_dbi_inv_detail_f d,
1077: isc_dbi_tmp_plans tmp,
1078: eni_oltp_item_star item
1079: WHERE d.plan_id = tmp.plan_id
1080: AND d.sr_inventory_item_id = item.inventory_item_id(+)
1081: AND d.organization_id = item.organization_id(+)

Line 1273: WHERE plan_id IN (select plan_id from isc_dbi_tmp_plans tmp);

1269: return(-1);
1270: END IF;
1271:
1272: UPDATE isc_dbi_plans SET complete_flag = 'Y'
1273: WHERE plan_id IN (select plan_id from isc_dbi_tmp_plans tmp);
1274: COMMIT;
1275:
1276: RETURN(1);
1277:

Line 1460: FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_snapshots p

1456: -- item level, change to range partition
1457:
1458: CURSOR Delete_List IS
1459: SELECT p.snapshot_id
1460: FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_snapshots p
1461: WHERE bitand(tmp.plan_usage, 2) = 2
1462: AND tmp.plan_id = p.plan_id
1463: AND trunc(tmp.data_start_date) = trunc(p.data_start_date);
1464:

Line 1467: FROM isc_dbi_tmp_plans tmp

1463: AND trunc(tmp.data_start_date) = trunc(p.data_start_date);
1464:
1465: CURSOR Snapshot_List IS
1466: SELECT snapshot_id
1467: FROM isc_dbi_tmp_plans tmp
1468: WHERE bitand(plan_usage, 2) = 2
1469: ORDER BY snapshot_id;
1470:
1471: l_delete_id NUMBER;

Line 1525: UPDATE isc_dbi_tmp_plans SET snapshot_id = isc_dbi_msc_objects_s.nextval WHERE bitand(plan_usage, 2) = 2;

1521: END LOOP;
1522:
1523: END IF;
1524:
1525: UPDATE isc_dbi_tmp_plans SET snapshot_id = isc_dbi_msc_objects_s.nextval WHERE bitand(plan_usage, 2) = 2;
1526: l_count := SQL%ROWCOUNT;
1527: COMMIT;
1528:
1529: BIS_COLLECTION_UTILITIES.Put_Line(' ');

Line 1589: FROM isc_dbi_tmp_plans tmp,

1585: SNAPSHOT_DATE)
1586: SELECT tmp.snapshot_id, ip.plan_id, ip.organization_id, ip.compile_designator, ip.constrained_flag,
1587: ip.curr_plan_type, ip.cutoff_date, ip.data_start_date,
1588: ip.description, count(*), g_snapshot_date
1589: FROM isc_dbi_tmp_plans tmp,
1590: isc_dbi_plans ip,
1591: isc_dbi_plan_organizations ipo
1592: WHERE tmp.plan_id = ip.plan_id
1593: AND bitand(tmp.plan_usage, 2) = 2

Line 1615: FROM isc_dbi_tmp_plans tmp,

1611: INTO isc_dbi_plan_org_snapshots(
1612: SNAPSHOT_ID,
1613: ORGANIZATION_ID)
1614: SELECT tmp.snapshot_id, ipo.organization_id
1615: FROM isc_dbi_tmp_plans tmp,
1616: isc_dbi_plan_organizations ipo
1617: WHERE tmp.plan_id = ipo.plan_id
1618: AND bitand(tmp.plan_usage, 2) = 2;
1619:

Line 1657: FROM isc_dbi_tmp_plans tmp,

1653: sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))) PURCHASING_COST,
1654: sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))*curr.rate) PURCHASING_COST_G,
1655: sum(nvl(ids.new_order_quantity,0)*nvl(ids.item_price,nvl(ids.standard_cost,0))*curr.rate2) PURCHASING_COST_G1,
1656: ids.uom_code UOM_CODE
1657: FROM isc_dbi_tmp_plans tmp,
1658: isc_dbi_supplies_f ids,
1659: isc_dbi_plan_curr_rates curr,
1660: fii_time_day time
1661: WHERE tmp.plan_id = ids.plan_id

Line 1761: FROM isc_dbi_tmp_plans tmp,

1757: sum(1) over (partition by tmp.snapshot_id, id.demand_id) CNT,
1758: sum(decode(reason.reason_type, null, 0, 1)) over (partition by tmp.snapshot_id, id.demand_id) REASON_CNT,
1759: avg(decode(reason.reason_type, null, 0, 1)) LATE_LINES,
1760: id.uom_code
1761: FROM isc_dbi_tmp_plans tmp,
1762: isc_dbi_demands_f id,
1763: (SELECT /*+ ordered */ peg1.plan_id, peg1.demand_id,
1764: r.organization_type, r.sr_inventory_item_id, r.sr_supplier_id, r.sr_supplier_site_id,
1765: r.organization_id, r.resource_id, r.department_id,

Line 1767: FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1

1763: (SELECT /*+ ordered */ peg1.plan_id, peg1.demand_id,
1764: r.organization_type, r.sr_inventory_item_id, r.sr_supplier_id, r.sr_supplier_site_id,
1765: r.organization_id, r.resource_id, r.department_id,
1766: decode(r.exception_type, 23, 2, 1) reason_type
1767: FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1
1768: WHERE bitand(p.plan_usage, 2) = 2
1769: AND p.plan_id = r.plan_id
1770: AND p.constrained_flag = 2
1771: AND r.exception_type in (15, 16, 23, 42)

Line 1869: FROM isc_dbi_tmp_plans tmp,

1865: sum(1) over (partition by tmp.snapshot_id, id.demand_id) CNT,
1866: sum(decode(iex.number1, null, 0, 1)) over (partition by tmp.snapshot_id, id.demand_id) REASON_CNT,
1867: avg(CASE WHEN iex.late_lines >= 1 THEN 1 ELSE 0 END) LATE_LINES,
1868: id.uom_code
1869: FROM isc_dbi_tmp_plans tmp,
1870: isc_dbi_demands_f id,
1871: (SELECT plan_id, number1, sum(decode(exception_type, 13, 1, 14, 1, 24, 1, 26, 1, 0)) LATE_LINES
1872: FROM isc_dbi_exception_details_f ex
1873: WHERE ex.exception_type in (13, 14, 24, 26, 52) AND ex.number1 is not null

Line 1879: FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1, isc_dbi_full_pegging_f peg

1875: (SELECT /*+ ordered */ peg1.plan_id, peg.demand_id,
1876: r.organization_type, r.sr_inventory_item_id, r.sr_supplier_id, r.sr_supplier_site_id,
1877: r.organization_id, r.resource_id, r.department_id,
1878: decode(r.exception_type,36,2,53,2,58,2,60,2,63,2,40,3,55,3,56,3,61,3,1) reason_type
1879: FROM isc_dbi_tmp_plans p, isc_dbi_exception_details_f r, isc_dbi_full_pegging_f peg1, isc_dbi_full_pegging_f peg
1880: WHERE bitand(p.plan_usage, 2) = 2
1881: AND p.constrained_flag = 1
1882: AND p.plan_id = r.plan_id
1883: AND r.exception_type in (9, 36, 37, 40, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 66, 67)

Line 1995: FROM isc_dbi_tmp_plans tmp,

1991: 0 REV_SHORTFALL,
1992: 0 TOTAL_LINES,
1993: 1 UNION1_FLAG,
1994: 0 UNION2_FLAG
1995: FROM isc_dbi_tmp_plans tmp,
1996: isc_dbi_inv_detail_f iinv,
1997: isc_dbi_periods per,
1998: fii_time_day time1
1999: WHERE tmp.plan_id = iinv.plan_id

Line 2015: FROM isc_dbi_tmp_plans tmp,

2011: 0 CARRYING_COST, 0 CARRYING_COST_G, 0 CARRYING_COST_G1, 0 COST_SHORTFALL, 0 COST_SHORTFALL_G, 0 COST_SHORTFALL_G1, 0 INVENTORY_COST, 0 INVENTORY_COST_G, 0 INVENTORY_COST_G1,
2012: 0 MDS_COST, 0 MDS_COST_G, 0 MDS_COST_G1, 0 MDS_PRICE, 0 MDS_PRICE_G, 0 MDS_PRICE_G1, 0 MDS_QUANTITY, 0 PRODUCTION_COST, 0 PRODUCTION_COST_G, 0 PRODUCTION_COST_G1,
2013: purchasing_cost PURCHASING_COST, purchasing_cost_g PURCHASING_COST_G, purchasing_cost_g1 PURCHASING_COST_G1,
2014: 0 LATE_LINES, 0 REV_SHORTFALL, 0 REV_SHORTFALL_G, 0 REV_SHORTFALL_G1, 0 TOTAL_LINES, 1 UNION1_FLAG, 0 UNION2_FLAG
2015: FROM isc_dbi_tmp_plans tmp,
2016: isc_dbi_supplies_snapshots sup
2017: WHERE tmp.snapshot_id = sup.snapshot_id
2018: AND bitand(tmp.plan_usage, 2) = 2
2019: UNION ALL

Line 2027: FROM isc_dbi_tmp_plans tmp,

2023: rev_temp MDS_PRICE, rev_temp_g MDS_PRICE_G, rev_temp_g1 MDS_PRICE_G1, 0 MDS_QUANTITY,
2024: 0 PRODUCTION_COST, 0 PRODUCTION_COST_G, 0 PRODUCTION_COST_G1, 0 PURCHASING_COST, 0 PURCHASING_COST_G, 0 PURCHASING_COST_G1, late_lines_temp LATE_LINES,
2025: sh.rev_shortfall REV_SHORTFALL, sh.rev_shortfall_G REV_SHORTFALL_G, sh.rev_shortfall_G1 REV_SHORTFALL_G1, total_lines_temp TOTAL_LINES,
2026: 1 UNION1_FLAG, 1 UNION2_FLAG
2027: FROM isc_dbi_tmp_plans tmp,
2028: isc_dbi_shortfall_snapshots sh
2029: WHERE tmp.snapshot_id = sh.snapshot_id
2030: AND bitand(tmp.plan_usage, 2) = 2)
2031: GROUP BY snapshot_id, organization_id, sr_inventory_item_id, start_date, period_type_id, uom_code;

Line 2067: FROM isc_dbi_tmp_plans tmp,

2063: ires.organization_type ORGANIZATION_TYPE,
2064: ires.resource_id,
2065: sum(nvl(required_hours,0)/(per.end_date-per.start_date+1)) REQUIRED_HOURS,
2066: sum(nvl(available_hours,0)/(per.end_date-per.start_date+1)) AVAILABLE_HOURS
2067: FROM isc_dbi_tmp_plans tmp,
2068: isc_dbi_res_summary_f ires,
2069: isc_dbi_periods per,
2070: fii_time_day time
2071: WHERE tmp.plan_id = ires.plan_id

Line 2142: FROM isc_dbi_tmp_plans tmp

2138:
2139: /*
2140: CURSOR Plan_List IS
2141: SELECT plan_name
2142: FROM isc_dbi_tmp_plans tmp
2143: WHERE bitand(plan_usage, 2) = 2;
2144: */
2145: CURSOR Plan_List IS
2146: SELECT tmp.plan_name

Line 2147: FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_schedules s

2143: WHERE bitand(plan_usage, 2) = 2;
2144: */
2145: CURSOR Plan_List IS
2146: SELECT tmp.plan_name
2147: FROM isc_dbi_tmp_plans tmp, isc_dbi_plan_schedules s
2148: WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4)
2149: AND tmp.plan_name = s.plan_name
2150: AND s.frequency <> 'ONCE';
2151:

Line 2165: AND plan_name IN (select plan_name from isc_dbi_tmp_plans tmp

2161:
2162: DELETE from isc_dbi_plan_schedules
2163: WHERE frequency = 'ONCE'
2164: AND next_collection_date <= g_snapshot_date
2165: AND plan_name IN (select plan_name from isc_dbi_tmp_plans tmp
2166: WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4));
2167:
2168: UPDATE isc_dbi_plan_schedules
2169: SET last_collected_date = g_snapshot_date

Line 2170: WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp

2166: WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4));
2167:
2168: UPDATE isc_dbi_plan_schedules
2169: SET last_collected_date = g_snapshot_date
2170: WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp
2171: WHERE (bitand(tmp.plan_usage, 2) = 2 or bitand(tmp.plan_usage, 4) = 4));
2172:
2173: -- API to populate the next collection date
2174:

Line 2199: -- WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp WHERE bitand(tmp.plan_usage, 2) = 2);

2195: CLOSE Plan_List;
2196:
2197: -- UPDATE isc_dbi_plan_schedules
2198: -- SET next_collection_date = ISC_DBI_PLAN_SETUP_UTIL_PKG.get_next_collection_date(plan_name)
2199: -- WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp WHERE bitand(tmp.plan_usage, 2) = 2);
2200:
2201: FII_UTIL.Stop_Timer;
2202: FII_UTIL.Print_Timer('Updated the setup tables in');
2203: BIS_COLLECTION_UTILITIES.Put_Line(' ');

Line 2207: -- Delete ISC_DBI_TMP_PLANS

2203: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2204:
2205: /*
2206: -- ------------------------
2207: -- Delete ISC_DBI_TMP_PLANS
2208: -- ------------------------
2209:
2210: BIS_COLLECTION_UTILITIES.put_line(' ');
2211: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');

Line 2214: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';

2210: BIS_COLLECTION_UTILITIES.put_line(' ');
2211: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
2212: FII_UTIL.Start_Timer;
2213:
2214: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
2215: EXECUTE IMMEDIATE l_trunc_stmt;
2216:
2217: FII_UTIL.Stop_Timer;
2218: FII_UTIL.Print_Timer('Truncated the temp table in');