[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_org_mv 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)
1259: cfrb.WEIGHTED_AVG_PERIOD10,
1260: cfrb.WEIGHTED_AVG_PERIOD11,
1261: cfrb.WEIGHTED_AVG_PERIOD12,
1262: cpp.RECOMMEND_METHOD
1263: From CSP_PLANNING_PARAMETERS cpp,
1264: CSP_USAGE_HISTORIES cuh,
1265: CSP_SUPERSEDE_ITEMS csi,
1266: CSP_FORECAST_RULES_B cfrb
1267: Where cpp.level_id like P_Level_Id || '%'
1335: cfrb.WEIGHTED_AVG_PERIOD10,
1336: cfrb.WEIGHTED_AVG_PERIOD11,
1337: cfrb.WEIGHTED_AVG_PERIOD12,
1338: cpp.RECOMMEND_METHOD
1339: From CSP_PLANNING_PARAMETERS cpp,
1340: CSP_USAGE_ORG_MV cuom,
1341: CSP_SUPERSEDE_ITEMS csi,
1342: CSP_FORECAST_RULES_B cfrb
1343: Where cpp.level_id like P_Level_Id || '%'
2072: IS
2073:
2074: cursor c_parameters(p_level_id varchar2) is
2075: select node_type
2076: from csp_planning_parameters
2077: where level_id = p_level_id;
2078:
2079: l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2080: l_api_version_number constant number := 1.0;
2311: IS
2312:
2313: Cursor c_Period_Size Is
2314: Select MAX(HISTORY_PERIODS * PERIOD_SIZE) PERIOD_SIZE
2315: From CSP_PLANNING_PARAMETERS cpp,
2316: CSP_FORECAST_RULES_B cfrb
2317: Where cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
2318:
2319:
2775: cuh.INVENTORY_ITEM_ID,
2776: cuh.ORGANIZATION_ID,
2777: cuh.SECONDARY_INVENTORY
2778: From csp_usage_headers cuh,
2779: csp_planning_parameters cpp,
2780: mtl_item_sub_inventories misi,
2781: csp_business_rules_b cbrb
2782: Where header_data_type = 1
2783: and process_status = 'O'
2815: cuh.organization_id,
2816: cuh.recommended_min_quantity,
2817: cuh.recommended_max_quantity
2818: from CSP_USAGE_HEADERS cuh,
2819: CSP_PLANNING_PARAMETERS cpp,
2820: MTL_SYSTEM_ITEMS_B msib,
2821: CSP_BUSINESS_RULES_B cbrb
2822: Where msib.INVENTORY_ITEM_ID = cuh.INVENTORY_ITEM_ID
2823: And msib.ORGANIZATION_ID = cuh.ORGANIZATION_ID
2860: cuh.secondary_inventory,
2861: cuh.recommended_min_quantity,
2862: cuh.recommended_max_quantity
2863: From csp_usage_headers cuh,
2864: csp_planning_parameters cpp,
2865: mtl_item_sub_inventories misi,
2866: CSP.csp_business_rules_b cbrb
2867: Where cuh.planning_parameters_id = cpp.planning_parameters_id
2868: and misi.organization_id(+) = cuh.organization_id
3096: cuh.lead_time,
3097: cpp.edq_factor,
3098: cpp.service_level
3099: from csp_usage_headers cuh,
3100: csp_planning_parameters cpp
3101: where cuh.inventory_item_id = p_item_id
3102: and cuh.organization_id = p_org_id
3103: and cuh.secondary_inventory = '-'
3104: and cuh.header_data_type = 4