[Home] [Help]
PACKAGE BODY: APPS.MST_COPY_PLAN_OPTIONS
Source
1 PACKAGE BODY MST_COPY_PLAN_OPTIONS AS
2 /* $Header: MSTCPPOB.pls 120.0 2005/05/26 17:37:29 appldev noship $ */
3
4 Type tname_type is table of Varchar2(30);
5
6
7 PROCEDURE copy_plan_options(
8 p_source_plan_id IN number,
9 p_dest_plan_name IN varchar2,
10 p_dest_plan_desc IN varchar2,
11 p_plan_dates_source IN number,
12 p_dest_start_date IN date DEFAULT NULL,
13 p_dest_end_date IN date DEFAULT NULL) IS
14
15
16 v_dest_plan_id number;
17 v_dest_start_date date;
18 v_dest_end_date date;
19 v_statement varchar2(20000);
20 l_return_status varchar2(10);
21 l_POOLING_FLAG number;
22
23 l_user_id number;
24 l_plan_id number;
25 BEGIN
26
27 -- Possible values for p_plan_dates_source
28 -- 1 Use plan dates from default plan options template
29 -- 2 Use plan dates from the source plan
30 -- 3 User user specified plan dates
31
32 l_user_id := fnd_global.user_id;
33
34 IF NVL(fnd_profile.value('MST_MTO_LICENSED'),'N') = 'Y' Then
35 l_POOLING_FLAG := 1;
36 ELSE
37 l_POOLING_FLAG := 2;
38 END IF;
39
40 if (p_plan_dates_source = 1) then
41 begin
42 select plan_id
43 into l_plan_id
44 from mst_plans
45 where plan_id = -1-l_user_id;
46 exception
47 when others then
48 l_plan_id := -1;
49 end;
50
51 select (sysdate + nvl(system_date_offset, 0)) start_date_new,
52 (sysdate + nvl(nvl(system_date_offset,0) + plan_days, 1)) cutoff_date_new
53 into v_dest_start_date, v_dest_end_date
54 from mst_plans
55 where plan_id = l_plan_id;
56
57 elsif (p_plan_dates_source = 2) then
58 select start_date, cutoff_date
59 into v_dest_start_date, v_dest_end_date
60 from mst_plans
61 where plan_id = p_source_plan_id;
62 elsif (p_plan_dates_source = 3) then
63 v_dest_start_date := p_dest_start_date;
64 v_dest_end_date := p_dest_end_date;
65 else
66 v_dest_start_date := p_dest_start_date;
67 v_dest_end_date := p_dest_end_date;
68 end if;
69
70
71 select mst_plans_s.nextval
72 into v_dest_plan_id
73 from dual;
74
75 v_statement :=
76 'INSERT INTO MST_PLANS(' ||
77 'plan_id, ' ||
78 'plan_type, ' ||
79 'compile_designator, ' ||
80 'description, ' ||
81 'start_date, ' ||
82 'cutoff_date, ' ||
83 'OUTSIDE_PROCESSING_FLAG, ' ||
84 'DROP_SHIPMENT_FLAG, ' ||
85 'SHIP_FROM_SUPPLIER_FLAG, ' ||
86 'SHIP_TO_CUSTOMER_FLAG, ' ||
87 'RETURN_TO_SUPPLIER_FLAG, ' ||
88 'RETURN_FROM_CUSTOMER_FLAG, ' ||
89 'INT_INBOUND_ALL_FLAG, ' ||
90 'INT_OUTBOUND_ALL_FLAG, ' ||
91 'EXT_OUTBOUND_ALL_FLAG, ' ||
92 'EXT_INBOUND_ALL_FLAG, ' ||
93 'OPTIMIZATION_STRATEGY_FLAG, ' ||
94 'POOLING_FLAG, ' ||
95 'MULTI_STOP_TL_FLAG, ' ||
96 'CONTINUOUS_MOVE_FLAG, ' ||
97 'OPTIMIZED_FLAG, ' ||
98 'PALLETIZE_FLAG, ' ||
99 'TARGET_TL_UTILIZATION, ' ||
100 'MIN_TL_UTILIZATION, ' ||
101 'LOCAL_POOLING_SHIPMENT_SIZE, ' ||
102 'LOCAL_POOLING_RADIUS, ' ||
103 'STOP_NEIGHBORHOOD_RADIUS, ' ||
104 'GENERAL_POOLING_RADIUS' ||
105 ',POOL_POINT_COUNT' ||
106 ',LOADING_UNLOADING_CHARGE' ||
107 ',LAYOVER_CHARGES' ||
108 ',ORIGIN_DESTINATION_TL_CHRGS' ||
109 ',OTHER_TL_CHARGES' ||
110 ',OTHER_LTL_CHARGES' ||
111 ',OTHER_PARCEL_CHARGES' ||
112 ',LTL_DISCOUNT' ||
113 ',PARCEL_DISCOUNT' ||
114 ',AUTO_RELEASE' ||
115 ',AUTO_REL_RULE_SET_ID' ||
116 ',COMMITMENT_SET_ID' ||
117 ',CURRENCY_UOM' ||
118 ',DISTANCE_UOM' ||
119 ',TIME_UOM' ||
120 ',VOLUME_UOM' ||
121 ',WEIGHT_UOM' ||
122 ',CREATED_BY' ||
123 ',CREATION_DATE' ||
124 ',LAST_UPDATED_BY' ||
125 ',LAST_UPDATE_DATE' ||
126 ',LAST_UPDATE_LOGIN' ||
127 ',DIMENSION_UOM' ||
128 ',MAXIMUM_EMPTY_LEG_LENGTH) ' ||
129 'select ' ||
130 ':v_dest_plan_id, ' ||
131 'plan_type, ' ||
132 ':p_dest_plan_name, ' ||
133 ':p_dest_plan_desc, ' ||
134 ':v_dest_start_date, ' ||
135 ':v_dest_end_date, ' ||
136 'OUTSIDE_PROCESSING_FLAG, ' ||
137 'DROP_SHIPMENT_FLAG, ' ||
138 'SHIP_FROM_SUPPLIER_FLAG, ' ||
139 'SHIP_TO_CUSTOMER_FLAG, ' ||
140 'RETURN_TO_SUPPLIER_FLAG, ' ||
141 'RETURN_FROM_CUSTOMER_FLAG, ' ||
142 'INT_INBOUND_ALL_FLAG, ' ||
143 'INT_OUTBOUND_ALL_FLAG, ' ||
144 'EXT_OUTBOUND_ALL_FLAG, ' ||
145 'EXT_INBOUND_ALL_FLAG, ' ||
146 'OPTIMIZATION_STRATEGY_FLAG, ' ||
147 l_POOLING_FLAG||',' ||
148 'MULTI_STOP_TL_FLAG, ' ||
149 'CONTINUOUS_MOVE_FLAG, ' ||
150 'OPTIMIZED_FLAG, ' ||
151 'PALLETIZE_FLAG, ' ||
152 'TARGET_TL_UTILIZATION, ' ||
153 'MIN_TL_UTILIZATION, ' ||
154 'LOCAL_POOLING_SHIPMENT_SIZE, ' ||
155 'LOCAL_POOLING_RADIUS, ' ||
156 'STOP_NEIGHBORHOOD_RADIUS, ' ||
157 'GENERAL_POOLING_RADIUS' ||
158 ',POOL_POINT_COUNT' ||
159 ',LOADING_UNLOADING_CHARGE' ||
160 ',LAYOVER_CHARGES' ||
161 ',ORIGIN_DESTINATION_TL_CHRGS' ||
162 ',OTHER_TL_CHARGES' ||
163 ',OTHER_LTL_CHARGES' ||
164 ',OTHER_PARCEL_CHARGES' ||
165 ',LTL_DISCOUNT' ||
166 ',PARCEL_DISCOUNT' ||
167 ',AUTO_RELEASE' ||
168 ',AUTO_REL_RULE_SET_ID' ||
169 ',COMMITMENT_SET_ID' ||
170 ',CURRENCY_UOM' ||
171 ',DISTANCE_UOM' ||
172 ',TIME_UOM' ||
173 ',VOLUME_UOM' ||
174 ',WEIGHT_UOM' ||
175 ',:v_user_id' ||
176 ',:v_sysdate' ||
177 ',:v_user_id' ||
178 ',:v_sysdate' ||
179 ',:v_login_id' ||
180 ',DIMENSION_UOM' ||
181 ',MAXIMUM_EMPTY_LEG_LENGTH ' ||
182 'from MST_PLANS ' ||
183 'where plan_id = :p_source_plan_id';
184
185
186 EXECUTE IMMEDIATE v_statement USING v_dest_plan_id, p_dest_plan_name,
187 p_dest_plan_desc, v_dest_start_date, v_dest_end_date, fnd_global.user_id,
188 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
189 p_source_plan_id;
190
191 v_statement :=
192 'INSERT INTO MST_PLAN_FACILITIES(' ||
193 'plan_id, ' ||
194 'FACILITY_ID, ' ||
195 'INT_INBOUND_FLAG, ' ||
196 'INT_OUTBOUND_FLAG, ' ||
197 'OUT_INBOUND_FLAG, ' ||
198 'OUT_OUTBOUND_FLAG, '||
199 'CREATED_BY, ' ||
200 'CREATION_DATE, ' ||
201 'LAST_UPDATED_BY, ' ||
202 'LAST_UPDATE_DATE, ' ||
203 'LAST_UPDATE_LOGIN ) ' ||
204 'select ' ||
205 ':v_dest_plan_id, ' ||
206 'FACILITY_ID, ' ||
207 'INT_INBOUND_FLAG, ' ||
208 'INT_OUTBOUND_FLAG, ' ||
209 'OUT_INBOUND_FLAG, ' ||
210 'OUT_OUTBOUND_FLAG, ' ||
211 ':v_user_id, ' ||
212 ':v_sysdate, ' ||
213 ':v_user_id, ' ||
214 ':v_sysdate, ' ||
215 ':v_login_id ' ||
216 'from MST_PLAN_FACILITIES ' ||
217 'where plan_id = :p_source_plan_id';
218
219 EXECUTE IMMEDIATE v_statement USING v_dest_plan_id, fnd_global.user_id,
220 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
221 p_source_plan_id;
222
223 v_statement :=
224 ' INSERT INTO MST_PLAN_CONSTRAINT_RULES(' ||
225 'plan_id, ' ||
226 'CONSTRAINT_CODE, ' ||
227 'TYPE, ' ||
228 'PENALTY_FUNCTION_TYPE, ' ||
229 'CREATED_BY, ' ||
230 'CREATION_DATE, ' ||
231 'LAST_UPDATED_BY, ' ||
232 'LAST_UPDATE_DATE, ' ||
233 'last_update_login) ' ||
234 'select ' ||
235 ':v_dest_plan_id, ' ||
236 'CONSTRAINT_CODE, ' ||
237 'TYPE, ' ||
238 'PENALTY_FUNCTION_TYPE, ' ||
239 ':v_user_id, ' ||
240 ':v_sysdate, ' ||
241 ':v_user_id, ' ||
242 ':v_sysdate, ' ||
243 ':v_login_id ' ||
244 'from mst_plan_constraint_rules ' ||
245 'where plan_id = :p_source_plan_id';
246
247 EXECUTE IMMEDIATE v_statement USING v_dest_plan_id, fnd_global.user_id,
248 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
249 p_source_plan_id;
250
251
252 v_statement :=
253 'INSERT INTO MST_PLAN_PENALTY_BREAKS( ' ||
254 'plan_id, ' ||
255 'CONSTRAINT_CODE, ' ||
256 'LOW_RANGE, ' ||
257 'HIGH_RANGE, ' ||
258 'PENALTY_VALUE, ' ||
259 'PENALTY_RATE, ' ||
260 'CREATED_BY, ' ||
261 'CREATION_DATE, ' ||
262 'LAST_UPDATED_BY, ' ||
263 'LAST_UPDATE_DATE, ' ||
264 'LAST_UPDATE_LOGIN ) ' ||
265 'select ' ||
266 ':v_dest_plan_id, ' ||
267 'CONSTRAINT_CODE, ' ||
268 'LOW_RANGE, ' ||
269 'HIGH_RANGE, ' ||
270 'PENALTY_VALUE, ' ||
271 'PENALTY_RATE, ' ||
272 ':v_user_id, ' ||
273 ':v_sysdate, ' ||
274 ':v_user_id, ' ||
275 ':v_sysdate, ' ||
276 ':v_login_id ' ||
277 'from mst_plan_penalty_breaks ' ||
278 'where plan_id = :p_source_plan_id ';
279
280 EXECUTE IMMEDIATE v_statement USING v_dest_plan_id, fnd_global.user_id,
281 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
282 p_source_plan_id;
283
284 commit;
285
286 EXCEPTION
287 when no_data_found
288 then raise_application_error(-20000,'no data found');
289 when others then
290
291 raise_application_error(-20000,sqlerrm||':'||v_statement||
292 'p_source_plan_id' || p_source_plan_id||' ' ||
293 'p_dest_plan_name' ||p_dest_plan_name||' ' ||
294 'p_dest_plan_desc' ||p_dest_plan_desc||' ' ||
295 'p_plan_dates_source' ||p_plan_dates_source||' ' ||
296 'p_dest_start_date' ||p_dest_start_date||' ' ||
297 'p_dest_end_date' ||p_dest_end_date);
298
299 END copy_plan_options;
300
301
302 PROCEDURE copy_default_plan_options(p_plan_id NUMBER, p_created_by NUMBER) IS
303 v_statement varchar2(20000);
304 l_return_status varchar2(10);
305
306 BEGIN
307
308 v_statement :=
309 'INSERT INTO MST_PLAN_FACILITIES(' ||
310 'plan_id, ' ||
311 'FACILITY_ID, ' ||
312 'INT_INBOUND_FLAG, ' ||
313 'INT_OUTBOUND_FLAG, ' ||
314 'OUT_INBOUND_FLAG, ' ||
315 'OUT_OUTBOUND_FLAG, '||
316 'CREATED_BY, ' ||
317 'CREATION_DATE, ' ||
318 'LAST_UPDATED_BY, ' ||
319 'LAST_UPDATE_DATE, ' ||
320 'LAST_UPDATE_LOGIN ) ' ||
321 'select ' ||
322 ':v_plan_id, ' ||
323 'FACILITY_ID, ' ||
324 'INT_INBOUND_FLAG, ' ||
325 'INT_OUTBOUND_FLAG, ' ||
326 'OUT_INBOUND_FLAG, ' ||
327 'OUT_OUTBOUND_FLAG, ' ||
328 ':v_user_id, ' ||
329 ':v_sysdate, ' ||
330 ':v_user_id, ' ||
331 ':v_sysdate, ' ||
332 ':v_login_id ' ||
333 'from MST_PLAN_FACILITIES ' ||
334 'where plan_id = -1 and created_by = :p_created_by';
335
336 EXECUTE IMMEDIATE v_statement USING p_plan_id, fnd_global.user_id,
337 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
338 p_created_by;
339
340 v_statement :=
341 ' INSERT INTO MST_PLAN_CONSTRAINT_RULES(' ||
342 'plan_id, ' ||
343 'CONSTRAINT_CODE, ' ||
344 'TYPE, ' ||
345 'PENALTY_FUNCTION_TYPE, ' ||
346 'CREATED_BY, ' ||
347 'CREATION_DATE, ' ||
348 'LAST_UPDATED_BY, ' ||
349 'LAST_UPDATE_DATE, ' ||
350 'last_update_login) ' ||
351 'select ' ||
352 ':v_dest_plan_id, ' ||
353 'CONSTRAINT_CODE, ' ||
354 'TYPE, ' ||
355 'PENALTY_FUNCTION_TYPE, ' ||
356 ':v_user_id, ' ||
357 ':v_sysdate, ' ||
358 ':v_user_id, ' ||
359 ':v_sysdate, ' ||
360 ':v_login_id ' ||
361 'from mst_plan_constraint_rules ' ||
362 'where plan_id = -1 and created_by = :p_created_by';
363
364 EXECUTE IMMEDIATE v_statement USING p_plan_id, fnd_global.user_id,
365 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
366 p_created_by;
367
368
369 v_statement :=
370 'INSERT INTO MST_PLAN_PENALTY_BREAKS( ' ||
371 'plan_id, ' ||
372 'CONSTRAINT_CODE, ' ||
373 'LOW_RANGE, ' ||
374 'HIGH_RANGE, ' ||
375 'PENALTY_VALUE, ' ||
376 'PENALTY_RATE, ' ||
377 'CREATED_BY, ' ||
378 'CREATION_DATE, ' ||
379 'LAST_UPDATED_BY, ' ||
380 'LAST_UPDATE_DATE, ' ||
381 'LAST_UPDATE_LOGIN ) ' ||
382 'select ' ||
383 ':v_dest_plan_id, ' ||
384 'CONSTRAINT_CODE, ' ||
385 'LOW_RANGE, ' ||
386 'HIGH_RANGE, ' ||
387 'PENALTY_VALUE, ' ||
388 'PENALTY_RATE, ' ||
389 ':v_user_id, ' ||
390 ':v_sysdate, ' ||
391 ':v_user_id, ' ||
392 ':v_sysdate, ' ||
393 ':v_login_id ' ||
394 'from mst_plan_penalty_breaks ' ||
395 'where plan_id = -1 and created_by = :p_created_by';
396
397 EXECUTE IMMEDIATE v_statement USING p_plan_id, fnd_global.user_id,
398 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
399 p_created_by;
400
401 commit;
402
403 EXCEPTION
404 when no_data_found
405 then raise_application_error(-20000,'no data found');
406 when others then
407
408 raise_application_error(-20000,sqlerrm||':'||v_statement||
409 'p_plan_id ' || p_plan_id||' ' ||
410 'p_created_by ' ||p_created_by);
411
412
413 END copy_default_plan_options;
414
415
416 end mst_copy_plan_options;