[Home] [Help]
947: || x_start_time || ' , '
948: || x_end_time || ' , ';
949:
950: x_from_clause := ' FROM ' || x_schema || '.' || x_view_name || ' exp, ' ||
951: ' msc_system_items msi, ';
952:
953: /* Get the levels at which forecast has been exported
954: * Expected Levels -
955: * 1. Item AND/OR Product Family
1522: sr_zone_id,
1523: demand_class,
1524: start_time,
1525: end_time) entries,
1526: msc_system_items msi
1527: WHERE msi.plan_id = -1
1528: AND msi.sr_instance_id = entries.sr_instance_id
1529: AND msi.organization_id = entries.sr_organization_id
1530: AND msi.item_name = entries.pf_name;
1605: demand_class,
1606: start_time,
1607: end_time) entries,
1608: msc_apps_instances mai,
1609: msc_system_items msi
1610: WHERE mai.instance_id = entries.sr_instance_id
1611: AND msi.plan_id = -1
1612: AND msi.sr_instance_id = mai.instance_id
1613: AND msi.organization_id = mai.validation_org_id
1932: x_select_clause := x_select_clause || x_start_time || ' , '
1933: || x_end_time || ' , ';
1934:
1935: x_from_clause := ' FROM ' || x_schema || '.' || x_view_name || ' exp, ' ||
1936: ' msc_system_items msi, ';
1937:
1938: /* Get the levels at which planning percentages are being exported
1939: * Expected Levels -
1940: * 1. Item (Mandatory)
1986: THEN
1987: x_from_clause := x_from_clause || ' msc_trading_partners mtp_org, ';
1988: END IF;
1989:
1990: x_from_clause := x_from_clause || ' msc_system_items pitem, '
1991: || ' msc_boms mb, '
1992: || ' msc_bom_components mbc, ';
1993:
1994: IF (x_res_type = 1)
2910: 'mbc.component_sequence_id, ' ||
2911: 'mb.bill_sequence_id, ' ||
2912: 'pitem.inventory_item_id, ' ||
2913: ':3,:4, 1, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID ';
2914: x_from_clause := 'FROM msc_system_items msi, ' ||
2915: case when x_is_global_fcst = 2 then ' msc_trading_partners mtp_org, ' end ||
2916: ' msc_system_items pitem, msc_boms mb, msc_bom_components mbc, msc_apps_instances mai ';
2917:
2918: IF (x_is_global_fcst = 2) THEN
2912: 'pitem.inventory_item_id, ' ||
2913: ':3,:4, 1, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID ';
2914: x_from_clause := 'FROM msc_system_items msi, ' ||
2915: case when x_is_global_fcst = 2 then ' msc_trading_partners mtp_org, ' end ||
2916: ' msc_system_items pitem, msc_boms mb, msc_bom_components mbc, msc_apps_instances mai ';
2917:
2918: IF (x_is_global_fcst = 2) THEN
2919: x_where_clause := 'WHERE mtp_org.partner_type = 3 ' ||
2920: ' AND :5 = mtp_org.organization_code ' ||
3527: || ' avg(exp.acry_mape_spf_outsamp)*100 acry_mape_spf_outsamp, '
3528: || ' avg(exp.spf_fore_vol)*100 spf_fore_vol, '
3529: || ' avg(exp.spf_glob_prop) spf_glob_prop '
3530: || ' FROM ' || x_schema || '.' || x_view_name
3531: || ' exp, msc_system_items msi, msc_trading_partners mtp_org ';
3532:
3533: x_where_clause := ' WHERE mtp_org.partner_type = 3 ' ||
3534: ' AND exp.LEVEL2 = mtp_org.organization_code ' ||
3535: ' AND msi.plan_id = -1 ' ||
4882: || x_fnd_user_id || ' CREATED_BY, '
4883: || ' fnd_global.login_id ' ;
4884:
4885: x_from_clause := ' FROM ' || x_dem_schema || '.' || x_view_name || ' exp, '
4886: || ' msc_system_items msi, '
4887: || ' (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_MAINTENANCE_SOURCE_TYPE'') lkup_MT '
4888: || ',(select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_PLANNING_VISIT_TYPE'') lkup_vt ';
4889:
4890: IF (x_AGA2_level IS NOT NULL) THEN
5143:
5144: EXECUTE IMMEDIATE ' UPDATE MSD_DP_SCN_ENTRIES_DENORM d '
5145: || ' SET forecast_error = nvl((SELECT 100 * ' || x_fcst_acry_column
5146: || ' FROM msc_trading_partners mtp, '
5147: || ' msc_system_items msi, '
5148: || x_schema || '.T_EP_ITEM tei, '
5149: || x_schema || '.T_EP_ORGANIZATION teo, '
5150: || x_schema || '.MDP_MATRIX mm '
5151: || ' WHERE mtp.partner_type = 3 '