[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_PARAMETER_PKG
Source
1 PACKAGE BODY msd_dp_parameter_pkg AS
2 /* $Header: msddpipb.pls 120.1 2006/03/31 07:10:58 brampall noship $ */
3
4 /* Public Procedures */
5
6
7 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in VARCHAR2,
8 P_PARAMETER_TYPE in VARCHAR2,
9 P_PARAMETER_NAME in VARCHAR2,
10 P_OWNER in VARCHAR2,
11 P_START_DATE in VARCHAR2,
12 P_END_DATE in VARCHAR2,
13 P_INPUT_SCENARIO in VARCHAR2,
14 P_FORECAST_DATE_USED in VARCHAR2,
15 P_FORECAST_BASED_ON in VARCHAR2,
16 P_QUANTITY_USED in VARCHAR2,
17 P_AMOUNT_USED in VARCHAR2,
18 P_FORECAST_USED in varchar2,
19 P_PERIOD_TYPE in varchar2,
20 P_FACT_TYPE in varchar2,
21 P_VIEW_NAME in varchar2,
22 P_ALLO_AGG_BASIS_STREAM_ID in varchar2,
23 P_NUMBER_OF_PERIOD in varchar2,
24 P_EXCLUDE_FROM_ROLLING_CYCLE in varchar2,
25 P_ROUNDING_FLAG in varchar2,
26 P_DELETEABLE_FLAG in varchar2,
27 P_LAST_UPDATE_DATE in varchar2,
28 P_CAPACITY_USAGE_RATIO in VARCHAR2,
29 P_SUPPLY_PLAN_FLAG in VARCHAR2,
30 P_ENABLE_NONSEED_FLAG in VARCHAR2,
31 P_PRICE_LIST_NAME in VARCHAR2,
32 P_CUSTOM_MODE in VARCHAR2,
33 P_STREAM_TYPE in VARCHAR2,
34 P_EQUATION in VARCHAR2,
35 P_CALCULATED_ORDER in VARCHAR2,
36 P_POST_CALCULATION in VARCHAR2,
37 P_ARCHIVED_FOR_PARAMETER in VARCHAR2
38 )
39 is
40
41 l_demand_plan_id number;
42 l_scenario_id number;
43 f_luby number; -- entity owner in file
44 f_ludate date; -- entity update date in file
45 db_luby number; -- entity owner in db
46 db_ludate date; -- entity update date in db
47
48 BEGIN
49
50 -- translate values to IDs
51 select DEMAND_PLAN_ID
52 into l_demand_plan_id
53 from MSD_DEMAND_PLANS
54 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
55
56 begin
57
58 select SCENARIO_ID
59 into l_scenario_id
60 from MSD_DP_SCENARIOS
61 where demand_plan_id = l_demand_plan_id
62 and scenario_name = P_INPUT_SCENARIO;
63
64 exception
65 when no_data_found then
66 null;
67 end;
68
69 -- Translate owner to file_last_updated_by
70 f_luby := fnd_load_util.owner_id(P_OWNER);
71
72 -- Translate char last_update_date to date
73 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
74 begin
75 select LAST_UPDATED_BY, LAST_UPDATE_DATE
76 into db_luby, db_ludate
77 from msd_dp_parameters
78 where DEMAND_PLAN_ID = l_demand_plan_id
79 and PARAMETER_TYPE = P_PARAMETER_TYPE
80 and (PARAMETER_NAME is null
81 or PARAMETER_NAME = P_PARAMETER_NAME);
82
83 -- Test for customization and version
84 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
85 db_ludate, P_CUSTOM_MODE)) then
86 -- Update existing row
87 update msd_dp_parameters
88 set capacity_usage_ratio = p_capacity_usage_ratio ,
89 supply_plan_flag = p_supply_plan_flag ,
90 last_update_date = f_ludate ,
91 start_date = to_date(P_START_DATE, 'YYYY/MM/DD'),
92 end_date = to_date(P_END_DATE, 'YYYY/MM/DD'),
93 input_scenario_id = l_scenario_id ,
94 forecast_date_used = P_FORECAST_DATE_USED,
95 forecast_based_on = P_FORECAST_BASED_ON ,
96 quantity_used = P_QUANTITY_USED ,
97 amount_used = P_AMOUNT_USED ,
98 forecast_used = P_FORECAST_USED ,
99 period_type = P_PERIOD_TYPE ,
100 fact_type = P_FACT_TYPE ,
101 view_name = P_VIEW_NAME ,
102 allo_agg_basis_stream_id = P_ALLO_AGG_BASIS_STREAM_ID ,
103 number_of_period = P_NUMBER_OF_PERIOD ,
104 exclude_from_rolling_cycle = P_EXCLUDE_FROM_ROLLING_CYCLE ,
105 rounding_flag = P_ROUNDING_FLAG ,
106 deleteable_flag = P_DELETEABLE_FLAG ,
107 enable_nonseed_flag = P_ENABLE_NONSEED_FLAG ,
108 price_list_name = P_PRICE_LIST_NAME ,
109 last_updated_by = f_luby ,
110 last_update_login = 0 ,
111 stream_type = P_STREAM_TYPE,
112 equation = P_EQUATION,
113 calculated_order = P_CALCULATED_ORDER,
114 post_calculation = P_POST_CALCULATION,
115 archived_for_parameter = P_ARCHIVED_FOR_PARAMETER
116 where demand_plan_id = l_demand_plan_id
117 and parameter_type = p_parameter_type
118 and (parameter_name is null
119 or parameter_name = p_parameter_name);
120
121 end if;
122 exception
123 when no_data_found then
124 -- Record doesn't exist - insert in all cases
125 insert into msd_dp_parameters
126 (DEMAND_PLAN_ID
127 ,PARAMETER_ID
128 ,PARAMETER_TYPE
129 ,PARAMETER_NAME
130 ,START_DATE
131 ,END_DATE
132 ,INPUT_SCENARIO_ID
133 ,FORECAST_DATE_USED
134 ,FORECAST_BASED_ON
135 ,QUANTITY_USED
136 ,AMOUNT_USED
137 ,FORECAST_USED
138 ,PERIOD_TYPE
139 ,FACT_TYPE
140 ,VIEW_NAME
141 ,ALLO_AGG_BASIS_STREAM_ID
142 ,NUMBER_OF_PERIOD
143 ,EXCLUDE_FROM_ROLLING_CYCLE
144 ,ROUNDING_FLAG ,
145 CAPACITY_USAGE_RATIO ,
146 SUPPLY_PLAN_FLAG ,
147 CREATION_DATE ,
148 CREATED_BY ,
149 LAST_UPDATE_DATE ,
150 LAST_UPDATED_BY ,
151 LAST_UPDATE_LOGIN ,
152 ENABLE_NONSEED_FLAG ,
153 PRICE_LIST_NAME,
154 DELETEABLE_FLAG,
155 STREAM_TYPE,
156 EQUATION,
157 CALCULATED_ORDER,
158 POST_CALCULATION,
159 ARCHIVED_FOR_PARAMETER
160 )
161 values
162 (l_demand_plan_id
163 ,msd_dp_parameters_s.nextval
164 ,P_PARAMETER_TYPE
165 ,P_PARAMETER_NAME
166 ,to_date(P_START_DATE, 'YYYY/MM/DD')
167 ,to_date(P_END_DATE, 'YYYY/MM/DD')
168 ,l_scenario_id
169 ,P_FORECAST_DATE_USED
170 ,P_FORECAST_BASED_ON
171 ,P_QUANTITY_USED
172 ,P_AMOUNT_USED
173 ,P_FORECAST_USED
174 ,P_PERIOD_TYPE
175 ,P_FACT_TYPE
176 ,P_VIEW_NAME
177 ,P_ALLO_AGG_BASIS_STREAM_ID
178 ,P_NUMBER_OF_PERIOD
179 ,P_EXCLUDE_FROM_ROLLING_CYCLE
180 ,P_ROUNDING_FLAG ,
181 P_CAPACITY_USAGE_RATIO ,
182 P_SUPPLY_PLAN_FLAG ,
183 f_ludate ,
184 f_luby ,
185 f_ludate ,
186 f_luby ,
187 0 ,
188 P_ENABLE_NONSEED_FLAG ,
189 P_PRICE_LIST_NAME ,
190 P_DELETEABLE_FLAG,
191 P_STREAM_TYPE,
192 P_EQUATION,
193 P_CALCULATED_ORDER,
194 P_POST_CALCULATION,
195 P_ARCHIVED_FOR_PARAMETER
196 );
197 end;
198
199 END;
200
201
202 END msd_dp_parameter_pkg ;