[Home] [Help]
1213: dept_res.disable_date, --**
1214: org.calendar_code, --**
1215: org.calendar_exception_set_id --**
1216: FROM msc_trading_partners org,
1217: msc_department_resources dept_res
1218: WHERE dept_res.owning_department_id = dept_res.department_id
1219: AND dept_res.plan_id = -1
1220: AND dept_res.resource_id <> -1
1221: AND dept_res.aggregate_resource_flag <> 1 -- if it's not aggregate
1240: org.calendar_code, --**
1241: org.calendar_exception_set_id --**
1242: FROM msc_trading_partners org,
1243: msc_resource_changes chg,
1244: msc_department_resources dept_res
1245: WHERE chg.department_id = dept_res.department_id
1246: AND chg.resource_id = dept_res.resource_id
1247: AND chg.sr_instance_id = dept_res.sr_instance_id
1248: AND chg.refresh_number = arg_refresh_number
1430: delete from msc_net_resource_avail
1431: where rowid in (select res.rowid
1432: from msc_net_resource_avail res,
1433: msc_resource_changes chg,
1434: msc_department_resources dept
1435: where res.organization_id = arg_organization_id
1436: and res.sr_instance_id = arg_sr_instance_id
1437: and res.plan_id = -1
1438: and res.department_id = chg.department_id
1579: if arg_refresh_flag = 2 then
1580: v_stmt := 110;
1581: delete from msc_net_resource_avail
1582: where rowid in (select res.rowid
1583: from msc_net_resource_avail res, msc_department_resources line
1584: where res.organization_id = line.organization_id
1585: and res.sr_instance_id = line.sr_instance_id
1586: and res.department_id = line.department_id
1587: and res.resource_id = -1
1592: and line.sr_instance_id = arg_sr_instance_id ) ;
1593: end if;
1594:
1595: /* 2201418 - Added hints to improve performance. Also defined a new index
1596: on msc_department_resources (line_flag, plan_id, sr_instance_id,
1597: organization_id) */
1598:
1599: INSERT into MSC_net_resource_avail(
1600: transaction_id,
1644: ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1645: ,SYSDATE /* PROGRAM_UPDATE_DATE */
1646: ,arg_refresh_number
1647: FROM msc_calendar_dates dates,
1648: msc_department_resources line,
1649: msc_trading_partners org
1650: WHERE line.organization_id = arg_organization_id
1651: AND line.sr_instance_id = arg_sr_instance_id
1652: AND line.line_flag = 1
1712: ,FND_GLOBAL.CONC_PROGRAM_ID /*PROGRAM_ID */
1713: ,SYSDATE /* PROGRAM_UPDATE_DATE */
1714: ,arg_refresh_number
1715: FROM
1716: msc_department_resources line,
1717: msc_trading_partners org
1718: WHERE line.organization_id = arg_organization_id
1719: AND line.sr_instance_id = arg_sr_instance_id
1720: AND line.line_flag = 1
2716: lv_plan_id msc_plans.plan_id%TYPE;
2717: lv_INSTANCE_ID MSC_APPS_INSTANCES.instance_id%TYPE;
2718: LV_DATA_START_DATE MSC_PLANS.DATA_START_DATE%TYPE;
2719: lv_PLAN_COMPLETION_DATE MSC_PLANS.PLAN_COMPLETION_DATE%TYPE;
2720: lv_UOM MSC_DEPARTMENT_RESOURCES.UNIT_OF_MEASURE%TYPE;
2721: v_dblink MSC_APPS_INSTANCES.M2A_DBLINK%TYPE;
2722: LV_SOURCE_APPLICATION VARCHAR2 (3);
2723: v_current_date MSC_NET_RESOURCE_AVAIL.LAST_UPDATE_DATE%TYPE;
2724: v_current_user MSC_NET_RESOURCE_AVAIL.LAST_UPDATED_BY%TYPE;
2899: || ' MNRA.plan_id, '
2900: || '''' || lv_DATA_START_DATE || ''','
2901: || '''' || lv_plan_name || ''','
2902: || ' MNRA.SIMULATION_SET_ID '
2903: || ' From MSC_NET_RESOURCE_AVAIL MNRA,MSC_DEPARTMENT_RESOURCES MDR '
2904: || ' WHERE MNRA.PLAN_ID = MDR.PLAN_ID '
2905: || ' AND MNRA.ORGANIZATION_ID = MDR.ORGANIZATION_ID '
2906: || ' AND MNRA.SR_INSTANCE_ID = MDR.SR_INSTANCE_ID '
2907: || ' AND MNRA.RESOURCE_ID = MDR.RESOURCE_ID '