[Home] [Help]
18: g_rebuild_snapshot_index VARCHAR2(1) := 'N';
19:
20: TYPE TableList IS TABLE OF VARCHAR2(80);
21: g_small_bases TableList := TableList('ISC_DBI_PLAN_ORGANIZATIONS', 'ISC_DBI_PLAN_BUCKETS', 'ISC_DBI_PLANS');
22: g_large_bases TableList := TableList('ISC_DBI_SUPPLIES_F','ISC_DBI_INV_DETAIL_F','ISC_DBI_RES_SUMMARY_F',
23: 'ISC_DBI_EXCEPTION_DETAILS_F','ISC_DBI_DEMANDS_F',
24: 'ISC_DBI_FULL_PEGGING_F');
25:
26: g_small_snapshots TableList := TableList('ISC_DBI_PLAN_ORG_SNAPSHOTS', 'ISC_DBI_PLAN_SNAPSHOTS');
441: 'VALUES(plan_id,organization_id,bkt_end_date,bkt_start_date,'||
442: 'bucket_index,bucket_type,curr_flag,days_in_bkt,'||
443: 'created_by,creation_date,last_updated_by,last_update_date,last_update_login) '||
444: 'WHEN union_flag = 4 THEN '||
445: 'INTO isc_dbi_supplies_f ( '||
446: 'PLAN_ID,TRANSACTION_ID,ORGANIZATION_ID,TIME_NEW_SCH_DATE_ID,SOURCE_ORGANIZATION_ID,'||
447: 'SOURCE_SR_INSTANCE_ID,SOURCE_SUPPLIER_ID,SOURCE_SUPPLIER_SITE_ID,SR_INSTANCE_ID,SR_INVENTORY_ITEM_ID,'||
448: 'SR_SUPPLIER_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,BOM_ITEM_TYPE,DISPOSITION_STATUS_TYPE,'||
449: 'IN_SOURCE_PLAN,ITEM_PRICE,NEW_ORDER_QUANTITY,NEW_PROCESSING_DAYS,NEW_SCHEDULE_DATE,ORDER_TYPE,'||
1054: FUNCTION IDENTIFY_DANGLING_ITEM RETURN NUMBER IS
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(+)
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
1662: AND bitand(tmp.plan_usage, 2) = 2