[Home] [Help]
PACKAGE BODY: APPS.MSD_DEMAND_PLAN_PKG
Source
1 PACKAGE BODY msd_demand_plan_pkg AS
2 /* $Header: msddplnb.pls 120.1 2006/03/31 08:24:53 brampall noship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in VARCHAR2,
7 P_OWNER in VARCHAR2,
8 P_DESCRIPTION in VARCHAR2,
9 P_BASE_UOM in VARCHAR2,
10 P_LOWEST_PERIOD_TYPE in VARCHAR2,
11 P_LAST_UPDATE_DATE in VARCHAR2,
12 P_VALID_FLAG in VARCHAR2,
13 P_ENABLE_FCST_EXPLOSION in VARCHAR2,
14 P_ROUNDOFF_THREASHOLD in VARCHAR2,
15 P_ROUNDOFF_DECIMAL_PLACES in VARCHAR2,
16 P_AMT_THRESHOLD in VARCHAR2,
17 P_AMT_DECIMAL_PLACES in VARCHAR2,
18 P_G_MIN_TIM_LVL_ID in VARCHAR2,
19 P_M_MIN_TIM_LVL_ID in VARCHAR2,
20 P_F_MIN_TIM_LVL_ID in VARCHAR2,
21 P_C_MIN_TIM_LVL_ID in VARCHAR2,
22 P_USE_ORG_SPECIFIC_BOM_FLAG in VARCHAR2,
23 P_TEMPLATE_FLAG in VARCHAR2,
24 P_ORGANIZATION_ID in VARCHAR2,
25 P_SR_INSTANCE_ID in VARCHAR2,
26 P_PLAN_TYPE in VARCHAR2,
27 P_DEFAULT_TEMPLATE in VARCHAR2,
28 P_STRIPE_STREAM_NAME in VARCHAR2,
29 P_CUSTOM_MODE in VARCHAR2)
30 IS
31
32 l_demand_plan_id number;
33 f_luby number; -- entity owner in file
34 f_ludate date; -- entity update date in file
35 db_luby number; -- entity owner in db
36 db_ludate date; -- entity update date in db
37
38
39 BEGIN
40
41
42 -- Translate owner to file_last_updated_by
43 f_luby := fnd_load_util.owner_id(P_OWNER);
44
45 -- Translate char last_update_date to date
46 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
47 begin
48 select LAST_UPDATED_BY, LAST_UPDATE_DATE
49 into db_luby, db_ludate
50 from msd_demand_plans
51 where DEMAND_PLAN_NAME = p_demand_plan_name;
52
53 -- Test for customization and version
54 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
55 db_ludate, P_CUSTOM_MODE)) then
56 -- Update existing row
57 update msd_demand_plans
58 set organization_id = p_organization_id,
59 sr_instance_id = p_sr_instance_id,
60 plan_type = p_plan_type,
61 default_template = p_default_template,
62 description = p_description ,
63 base_uom = p_base_uom ,
64 lowest_period_type = p_lowest_period_type ,
65 valid_flag = p_valid_flag,
66 enable_fcst_explosion = p_enable_fcst_explosion,
67 roundoff_threashold = p_roundoff_threashold ,
68 roundoff_decimal_places = p_roundoff_decimal_places,
69 amt_threshold = p_amt_threshold ,
70 amt_decimal_places = p_amt_decimal_places ,
71 g_min_tim_lvl_id = p_g_min_tim_lvl_id,
72 m_min_tim_lvl_id = p_m_min_tim_lvl_id,
73 f_min_tim_lvl_id = p_f_min_tim_lvl_id,
74 c_min_tim_lvl_id = p_c_min_tim_lvl_id,
75 use_org_specific_bom_flag = p_use_org_specific_bom_flag,
76 template_flag = p_template_flag ,
77 stripe_stream_name = p_stripe_stream_name,
78 last_update_date = f_ludate ,
79 last_updated_by = f_luby ,
80 last_update_login = 0
81 where demand_plan_name = p_demand_plan_name;
82
83 begin
84 -- translate values to IDs
85 select DEMAND_PLAN_ID
86 into l_demand_plan_id
87 from MSD_DEMAND_PLANS
88 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
89
90 -- Update existing row
91 update msd_demand_plans_tl
92 set description = p_description,
93 last_update_date = f_ludate,
94 last_updated_by = f_luby,
95 last_update_login = 0,
96 source_lang = userenv('LANG')
97 where demand_plan_id = l_demand_plan_id
98 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
99
100 exception
101 when no_data_found then
102
103 -- Record doesn't exist - insert in all cases
104 insert into msd_demand_plans_tl
105 (
106 demand_plan_id,
107 description,
108 language,
109 source_lang,
110 created_by,
111 creation_date,
112 last_updated_by,
113 last_update_date ,
114 last_update_login
115 )
116 Select
117 l_demand_plan_id,
118 p_description,
119 l.language_code,
120 userenv('LANG'),
121 f_luby,
122 f_ludate,
123 f_luby,
124 f_ludate,
125 0
126 from fnd_languages l
127 where l.installed_flag in ('I','B');
128
129 end;
130
131 end if;
132 exception
133 when no_data_found then
134
135 select msd_demand_plan_s.nextval into l_demand_plan_id from dual;
136
137 -- Record doesn't exist - insert in all cases
138 insert into msd_demand_plans
139 (DEMAND_PLAN_ID ,
140 TEMPLATE_ID,
141 DEMAND_PLAN_NAME ,
142 DESCRIPTION ,
143 BASE_UOM ,
144 LOWEST_PERIOD_TYPE ,
145 VALID_FLAG ,
146 ENABLE_FCST_EXPLOSION,
147 ROUNDOFF_THREASHOLD ,
148 ROUNDOFF_DECIMAL_PLACES ,
149 AMT_THRESHOLD ,
150 AMT_DECIMAL_PLACES ,
151 G_MIN_TIM_LVL_ID,
152 M_MIN_TIM_LVL_ID,
153 F_MIN_TIM_LVL_ID,
154 C_MIN_TIM_LVL_ID,
155 USE_ORG_SPECIFIC_BOM_FLAG,
156 TEMPLATE_FLAG ,
157 ORGANIZATION_ID,
158 SR_INSTANCE_ID ,
159 PLAN_TYPE ,
160 DEFAULT_TEMPLATE ,
161 STRIPE_STREAM_NAME,
162 CREATION_DATE ,
163 CREATED_BY ,
164 LAST_UPDATE_DATE ,
165 LAST_UPDATED_BY ,
166 LAST_UPDATE_LOGIN
167 )
168 values
169 (l_demand_plan_id ,
170 l_demand_plan_id ,
171 P_DEMAND_PLAN_NAME ,
172 P_DESCRIPTION ,
173 P_BASE_UOM ,
174 P_LOWEST_PERIOD_TYPE ,
175 P_VALID_FLAG ,
176 P_ENABLE_FCST_EXPLOSION,
177 P_ROUNDOFF_THREASHOLD ,
178 P_ROUNDOFF_DECIMAL_PLACES ,
179 P_AMT_THRESHOLD ,
180 P_AMT_DECIMAL_PLACES ,
181 P_G_MIN_TIM_LVL_ID,
182 P_M_MIN_TIM_LVL_ID,
183 P_F_MIN_TIM_LVL_ID,
184 P_C_MIN_TIM_LVL_ID,
185 P_USE_ORG_SPECIFIC_BOM_FLAG,
186 P_TEMPLATE_FLAG ,
187 P_ORGANIZATION_ID,
188 P_SR_INSTANCE_ID ,
189 P_PLAN_TYPE ,
190 P_DEFAULT_TEMPLATE ,
191 P_STRIPE_STREAM_NAME ,
192 f_ludate ,
193 f_luby ,
194 f_ludate ,
195 f_luby ,
196 0
197 );
198
199 insert into msd_demand_plans_tl
200 (
201 demand_plan_id,
202 description,
203 language,
204 source_lang,
205 created_by,
206 creation_date,
207 last_updated_by,
208 last_update_date ,
209 last_update_login
210 )
211 Select
212 l_demand_plan_id,
213 p_description,
214 l.language_code,
215 userenv('LANG'),
216 f_luby,
217 f_ludate,
218 f_luby,
219 f_ludate,
220 0
221 from fnd_languages l
222 where l.installed_flag in ('I','B');
223
224 end;
225
226 END LOAD_ROW;
227
228
229 PROCEDURE TRANSLATE_ROW(P_DEMAND_PLAN_NAME in varchar2,
230 P_DESCRIPTION in varchar2,
231 P_OWNER in varchar2)
232 IS
233
234 f_luby number; -- entity owner in file
235
236 BEGIN
237
238 -- Translate owner to file_last_updated_by
239 f_luby := fnd_load_util.owner_id(P_OWNER);
240
241
242 update msd_demand_plans_tl set
243 description = p_description,
244 LAST_UPDATE_DATE = sysdate,
245 LAST_UPDATED_BY = f_luby,
246 LAST_UPDATE_LOGIN = 0,
247 SOURCE_LANG = userenv('LANG')
248 where
249 demand_plan_id = (select demand_plan_id from msd_demand_plans where demand_plan_name = p_demand_plan_name)
250 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
251 END;
252
253 Procedure ADD_LANGUAGE
254 is
255 begin
256 delete from MSD_DEMAND_PLANS_TL T
257 where not exists
258 (select NULL
259 from MSD_DEMAND_PLANS B
260 where B.DEMAND_PLAN_ID = T.DEMAND_PLAN_ID
261 );
262
263 update MSD_DEMAND_PLANS_TL T set (
264 DESCRIPTION
265 ) = (select
266 B.DESCRIPTION
267 from MSD_DEMAND_PLANS_TL B
268 where B.DEMAND_PLAN_ID = T.DEMAND_PLAN_ID
269 and B.LANGUAGE = T.SOURCE_LANG)
270 where (
271 T.DEMAND_PLAN_ID,
272 T.LANGUAGE
273 ) in (select
274 SUBT.DEMAND_PLAN_ID,
275 SUBT.LANGUAGE
276 from MSD_DEMAND_PLANS_TL SUBB, MSD_DEMAND_PLANS_TL SUBT
277 where SUBB.DEMAND_PLAN_ID = SUBT.DEMAND_PLAN_ID
278 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
279 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
280 ));
281
282 insert into MSD_DEMAND_PLANS_TL (
283 DEMAND_PLAN_ID,
284 DESCRIPTION,
285 CREATION_DATE,
286 CREATED_BY,
287 LAST_UPDATE_DATE,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_LOGIN,
290 REQUEST_ID,
291 PROGRAM_APPLICATION_ID,
292 PROGRAM_ID,
293 PROGRAM_UPDATE_DATE,
294 LANGUAGE,
295 SOURCE_LANG
296 ) select
297 B.DEMAND_PLAN_ID,
298 B.DESCRIPTION,
299 B.CREATION_DATE,
300 B.CREATED_BY,
301 B.LAST_UPDATE_DATE,
302 B.LAST_UPDATED_BY,
303 B.LAST_UPDATE_LOGIN,
304 B.REQUEST_ID,
305 B.PROGRAM_APPLICATION_ID,
306 B.PROGRAM_ID,
307 B.PROGRAM_UPDATE_DATE,
308 L.LANGUAGE_CODE,
309 B.SOURCE_LANG
310 from MSD_DEMAND_PLANS_TL B, FND_LANGUAGES L
311 where L.INSTALLED_FLAG in ('I', 'B')
312 and B.LANGUAGE = userenv('LANG')
313 and not exists
314 (select NULL
315 from MSD_DEMAND_PLANS_TL T
316 where T.DEMAND_PLAN_ID = B.DEMAND_PLAN_ID
317 and T.LANGUAGE = L.LANGUAGE_CODE);
318
319 End ADD_LANGUAGE;
320
321 END msd_demand_plan_pkg ;