[Home] [Help]
53: Cursor c_Period_Size Is
54: Select HISTORY_PERIODS,
55: PERIOD_SIZE,
56: cfrb.FORECAST_RULE_ID
57: From CSP_PLANNING_PARAMETERS cpp,
58: CSP_FORECAST_RULES_B cfrb
59: Where cpp.ORGANIZATION_ID IS NULL
60: And cpp.SECONDARY_INVENTORY IS NULL
61: And cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
210: Cursor c_recommend_method is
211: Select sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,1)),
212: sum(decode(cpp.recommend_method,'PNORM',1,'USAGE_PNORM',1,0)),
213: sum(decode(cpp.recommend_method,'TNORM',1,'USAGE_TNORM',1,0))
214: from csp_planning_parameters cpp
215: where level_id like p_level_id || '%'
216: and node_type in ('ORGANIZATION_WH','SUBINVENTORY');
217:
218:
256: Else
257: Delete from csp_usage_headers cuh
258: Where (cuh.organization_id,cuh.secondary_inventory) in
259: (select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
260: from csp_planning_parameters cpp
261: Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
262: End If;
263: commit;
264: If p_Level_Id = '1' Then
268: Delete from csp_usage_histories cuh
269: Where history_data_type in (2,5)
270: And (cuh.organization_id,cuh.subinventory_code) in
271: (select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
272: from csp_planning_parameters cpp
273: Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
274: End If;
275: commit;
276: -- Standard Start of API savepoint
428: fnd_global.user_id,
429: sysdate,
430: fnd_global.user_id,
431: fnd_global.conc_login_id
432: From CSP_PLANNING_PARAMETERS cpp,
433: MTL_ITEM_SUB_INVENTORIES mis,
434: CST_ITEM_COSTS cic,
435: MTL_PARAMETERS mp
436: where cpp.node_type = 'SUBINVENTORY'
485: fnd_global.user_id,
486: sysdate,
487: fnd_global.user_id,
488: fnd_global.conc_login_id
489: From CSP_PLANNING_PARAMETERS cpp,
490: MTL_SYSTEM_ITEMS msi,
491: CST_ITEM_COSTS cic,
492: MTL_PARAMETERS mp
493: where cpp.node_type = 'ORGANIZATION_WH'
562: fnd_global.user_id,
563: sysdate,
564: fnd_global.user_id,
565: fnd_global.conc_login_id
566: from csp_planning_parameters cpp,
567: csp_usage_headers cuh,
568: cst_item_costs cic,
569: mtl_parameters mp,
570: mtl_system_items_b msib,
626: update csp_usage_headers cuh
627: set process_status = 'E'
628: Where (cuh.inventory_item_id,cuh.organization_id,cuh.secondary_inventory) in
629: (select mic.inventory_item_id,mic.organization_id,nvl(cpp.secondary_inventory,'-')
630: from csp_planning_parameters cpp,
631: mtl_item_categories mic
632: where cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
633: and mic.organization_id = cpp.organization_id
634: and mic.category_set_id = cpp.category_set_id
654: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
655: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
656: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
657: from CSP_USAGE_HEADERS cuh,
658: CSP_PLANNING_PARAMETERS cpp,
659: CSP_SAFETY_FACTORS csf
660: Where cuh.header_data_type = 1
661: And cuh.process_status = 'O'
662: And cpp.organization_id = cuh.organization_id
692: DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
693: DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
694: DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
695: from CSP_USAGE_HEADERS cuh,
696: CSP_PLANNING_PARAMETERS cpp,
697: CSP_SAFETY_FACTORS csf
698: Where cuh.header_data_type = 4
699: And cuh.process_status = 'O'
700: And cpp.organization_id = cuh.organization_id
745: cuh_fcst.subinventory_code,
746: cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
747: cfrb.forecast_periods
748: from csp_usage_histories cuh_fcst,
749: csp_planning_parameters cpp,
750: csp_forecast_rules_b cfrb,
751: csp_usage_histories cuh_usg
752: where cuh_fcst.history_data_type = 5
753: and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
790: cuh_fcst.organization_id,
791: cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
792: cfrb.forecast_periods
793: from csp_usage_histories cuh_fcst,
794: csp_planning_parameters cpp,
795: csp_forecast_rules_b cfrb,
796: csp_usage_orgs cuh_usg
797: where cuh_fcst.history_data_type = 5
798: and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
1157: select cuh.organization_id, cuh.inventory_item_id,
1158: cuh.period_start_date,
1159: sum(cuh.quantity)
1160: from csp_usage_histories cuh ,
1161: csp_planning_parameters cpp
1162: where cuh.History_Data_type = 1
1163: and cpp.organization_id = cuh.organization_id
1164: and cpp.secondary_inventory is null
1165: and cpp.organization_type = 'W'
1279: cfrb.WEIGHTED_AVG_PERIOD10,
1280: cfrb.WEIGHTED_AVG_PERIOD11,
1281: cfrb.WEIGHTED_AVG_PERIOD12,
1282: cpp.RECOMMEND_METHOD
1283: From CSP_PLANNING_PARAMETERS cpp,
1284: CSP_USAGE_HISTORIES cuh,
1285: CSP_SUPERSEDE_ITEMS csi,
1286: CSP_FORECAST_RULES_B cfrb
1287: Where cpp.level_id like P_Level_Id || '%'
1355: cfrb.WEIGHTED_AVG_PERIOD10,
1356: cfrb.WEIGHTED_AVG_PERIOD11,
1357: cfrb.WEIGHTED_AVG_PERIOD12,
1358: cpp.RECOMMEND_METHOD
1359: From CSP_PLANNING_PARAMETERS cpp,
1360: CSP_USAGE_ORGS cuom,
1361: CSP_SUPERSEDE_ITEMS csi,
1362: CSP_FORECAST_RULES_B cfrb
1363: Where cpp.level_id like P_Level_Id || '%'
2092: IS
2093:
2094: cursor c_parameters(p_level_id varchar2) is
2095: select node_type
2096: from csp_planning_parameters
2097: where level_id = p_level_id;
2098:
2099: l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2100: l_api_version_number constant number := 1.0;
2331: IS
2332:
2333: Cursor c_Period_Size Is
2334: Select MAX(HISTORY_PERIODS * PERIOD_SIZE) PERIOD_SIZE
2335: From CSP_PLANNING_PARAMETERS cpp,
2336: CSP_FORECAST_RULES_B cfrb
2337: Where cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
2338:
2339:
2795: cuh.INVENTORY_ITEM_ID,
2796: cuh.ORGANIZATION_ID,
2797: cuh.SECONDARY_INVENTORY
2798: From csp_usage_headers cuh,
2799: csp_planning_parameters cpp,
2800: mtl_item_sub_inventories misi,
2801: csp_business_rules_b cbrb
2802: Where header_data_type = 1
2803: and process_status = 'O'
2835: cuh.organization_id,
2836: cuh.recommended_min_quantity,
2837: cuh.recommended_max_quantity
2838: from CSP_USAGE_HEADERS cuh,
2839: CSP_PLANNING_PARAMETERS cpp,
2840: MTL_SYSTEM_ITEMS_B msib,
2841: CSP_BUSINESS_RULES_B cbrb
2842: Where msib.INVENTORY_ITEM_ID = cuh.INVENTORY_ITEM_ID
2843: And msib.ORGANIZATION_ID = cuh.ORGANIZATION_ID
2880: cuh.secondary_inventory,
2881: cuh.recommended_min_quantity,
2882: cuh.recommended_max_quantity
2883: From csp_usage_headers cuh,
2884: csp_planning_parameters cpp,
2885: mtl_item_sub_inventories misi,
2886: CSP.csp_business_rules_b cbrb
2887: Where cuh.planning_parameters_id = cpp.planning_parameters_id
2888: and misi.organization_id(+) = cuh.organization_id
3054: cuh.inventory_item_id,
3055: sum(cuh.quantity),
3056: cuh.period_start_date,
3057: substr(cpp.level_id,1,instr(cpp.level_id,'.',-1,1) - 1)
3058: from csp_planning_parameters cpp,
3059: csp_usage_histories cuh
3060: Where cpp.node_type = 'SUBINVENTORY'
3061: and cuh.history_data_type = 1
3062: and cuh.organization_id = cpp.organization_id
3143: cuh.lead_time,
3144: cpp.edq_factor,
3145: cpp.service_level
3146: from csp_usage_headers cuh,
3147: csp_planning_parameters cpp
3148: where cuh.inventory_item_id = p_item_id
3149: and cuh.organization_id = p_org_id
3150: and cuh.secondary_inventory = '-'
3151: and cuh.header_data_type = 4