1 package body wms_wp_planning_criteria_pkg as
2 /* $Header: WMSWPTPB.pls 120.2.12010000.2 2009/08/03 08:37:19 ssrikaku noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PLANNING_CRITERIA_ID in NUMBER,
6 X_LABOR_SETUP_MODE in VARCHAR2,
7 X_PLANNING_METHOD in VARCHAR2,
8 X_TYPE in VARCHAR2,
9 X_BACKORDER_FLAG in VARCHAR2,
10 X_REJECT_ORDER_LINE_FLAG in VARCHAR2,
11 X_REJECT_ALL_LINES_SHIPSET_FLA in VARCHAR2,
12 X_REJECT_ALL_LINES_MODEL_FLAG in VARCHAR2,
13 X_REJECT_ORDER_FLAG in VARCHAR2,
14 X_RESERVE_STOCK_FLAG in VARCHAR2,
15 X_AUTO_CREATE_DELIVERIES_FLAG in VARCHAR2,
16 X_CREDIT_CHECK_HOLD_FLAG in VARCHAR2,
17 X_PICKING_SUBINVENTORY in VARCHAR2,
18 X_DESTINATION_SUBINVENTORY in VARCHAR2,
19 X_BULK_LABOR_PLANNING_FLAG in VARCHAR2,
20 X_TIME_UOM in VARCHAR2,
21 X_DEPARTMENT_CODE in VARCHAR2,
22 X_DEPARTMENT_ID in NUMBER,
23 X_ENABLE_LABOR_PLANNING in VARCHAR2,
24 X_CROSSDOCK_CRITERIA in VARCHAR2,
25 X_CROSSDOCK_CRITERIA_ID in NUMBER,
26 X_ALLOCATION_METHOD in VARCHAR2,
27 X_PLANNING_CRITERIA in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 cursor C is select ROWID from WMS_WP_PLANNING_CRITERIA_B
35 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID
36 ;
37
38 l_curvar C%rowtype;
39
40 begin
41 insert into WMS_WP_PLANNING_CRITERIA_B (
42 LABOR_SETUP_MODE,
43 PLANNING_CRITERIA_ID,
44 PLANNING_METHOD,
45 TYPE,
46 BACKORDER_FLAG,
47 REJECT_ORDER_LINE_FLAG,
48 REJECT_ALL_LINES_SHIPSET_FLAG,
49 REJECT_ALL_LINES_MODEL_FLAG,
50 REJECT_ORDER_FLAG,
51 RESERVE_STOCK_FLAG,
52 AUTO_CREATE_DELIVERIES_FLAG,
53 CREDIT_CHECK_HOLD_FLAG,
54 PICKING_SUBINVENTORY,
55 DESTINATION_SUBINVENTORY,
56 BULK_LABOR_PLANNING_FLAG,
57 TIME_UOM,
58 DEPARTMENT_CODE,
59 DEPARTMENT_ID,
60 ENABLE_LABOR_PLANNING,
61 CROSSDOCK_CRITERIA,
62 CROSSDOCK_CRITERIA_ID,
63 ALLOCATION_METHOD,
64 CREATION_DATE,
65 CREATED_BY,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_LOGIN
69 ) values (
70 X_LABOR_SETUP_MODE,
71 X_PLANNING_CRITERIA_ID,
72 X_PLANNING_METHOD,
73 X_TYPE,
74 X_BACKORDER_FLAG,
75 X_REJECT_ORDER_LINE_FLAG,
76 X_REJECT_ALL_LINES_SHIPSET_FLA,
77 X_REJECT_ALL_LINES_MODEL_FLAG,
78 X_REJECT_ORDER_FLAG,
79 X_RESERVE_STOCK_FLAG,
80 X_AUTO_CREATE_DELIVERIES_FLAG,
81 X_CREDIT_CHECK_HOLD_FLAG,
82 X_PICKING_SUBINVENTORY,
83 X_DESTINATION_SUBINVENTORY,
84 X_BULK_LABOR_PLANNING_FLAG,
85 X_TIME_UOM,
86 X_DEPARTMENT_CODE,
87 X_DEPARTMENT_ID,
88 X_ENABLE_LABOR_PLANNING,
89 X_CROSSDOCK_CRITERIA,
90 X_CROSSDOCK_CRITERIA_ID,
91 X_ALLOCATION_METHOD,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_LOGIN
97 );
98
99 insert into WMS_WP_PLANNING_CRITERIA_TL (
100 PLANNING_CRITERIA_ID,
101 PLANNING_CRITERIA,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN,
107 LANGUAGE,
108 SOURCE_LANG
109 ) select
110 X_PLANNING_CRITERIA_ID,
111 X_PLANNING_CRITERIA,
112 X_CREATED_BY,
113 X_CREATION_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATE_LOGIN,
117 L.LANGUAGE_CODE,
118 userenv('LANG')
119 from FND_LANGUAGES L
120 where L.INSTALLED_FLAG in ('I', 'B')
121 and not exists
122 (select NULL
123 from WMS_WP_PLANNING_CRITERIA_TL T
124 where T.PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID
125 and T.LANGUAGE = L.LANGUAGE_CODE);
126
127 open c;
128 fetch c into l_curvar;
129 if (c%notfound) then
130 close c;
131 raise no_data_found;
132 end if;
133 close c;
134
135 end INSERT_ROW;
136
137 procedure LOCK_ROW (
138 X_PLANNING_CRITERIA_ID in NUMBER,
139 X_LABOR_SETUP_MODE in VARCHAR2,
140 X_PLANNING_METHOD in VARCHAR2,
141 X_TYPE in VARCHAR2,
142 X_BACKORDER_FLAG in VARCHAR2,
143 X_REJECT_ORDER_LINE_FLAG in VARCHAR2,
144 X_REJECT_ALL_LINES_SHIPSET_FLA in VARCHAR2,
145 X_REJECT_ALL_LINES_MODEL_FLAG in VARCHAR2,
146 X_REJECT_ORDER_FLAG in VARCHAR2,
147 X_RESERVE_STOCK_FLAG in VARCHAR2,
148 X_AUTO_CREATE_DELIVERIES_FLAG in VARCHAR2,
149 X_CREDIT_CHECK_HOLD_FLAG in VARCHAR2,
150 X_PICKING_SUBINVENTORY in VARCHAR2,
151 X_DESTINATION_SUBINVENTORY in VARCHAR2,
152 X_BULK_LABOR_PLANNING_FLAG in VARCHAR2,
153 X_TIME_UOM in VARCHAR2,
154 X_DEPARTMENT_CODE in VARCHAR2,
155 X_DEPARTMENT_ID in NUMBER,
156 X_ENABLE_LABOR_PLANNING in VARCHAR2,
157 X_CROSSDOCK_CRITERIA in VARCHAR2,
158 X_CROSSDOCK_CRITERIA_ID in NUMBER,
159 X_ALLOCATION_METHOD in VARCHAR2,
160 X_PLANNING_CRITERIA in VARCHAR2
161 ) is
162 cursor c is select
163 LABOR_SETUP_MODE,
164 PLANNING_METHOD,
165 TYPE,
166 BACKORDER_FLAG,
167 REJECT_ORDER_LINE_FLAG,
168 REJECT_ALL_LINES_SHIPSET_FLAG,
169 REJECT_ALL_LINES_MODEL_FLAG,
170 REJECT_ORDER_FLAG,
171 RESERVE_STOCK_FLAG,
172 AUTO_CREATE_DELIVERIES_FLAG,
173 CREDIT_CHECK_HOLD_FLAG,
174 PICKING_SUBINVENTORY,
175 DESTINATION_SUBINVENTORY,
176 BULK_LABOR_PLANNING_FLAG,
177 TIME_UOM,
178 DEPARTMENT_CODE,
179 DEPARTMENT_ID,
180 ENABLE_LABOR_PLANNING,
181 CROSSDOCK_CRITERIA,
182 CROSSDOCK_CRITERIA_ID,
183 ALLOCATION_METHOD
184 from WMS_WP_PLANNING_CRITERIA_B
185 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID
186 for update of PLANNING_CRITERIA_ID nowait;
187 recinfo c%rowtype;
188
189 cursor c1 is select
190 PLANNING_CRITERIA,
191 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
192 from WMS_WP_PLANNING_CRITERIA_TL
193 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
195 for update of PLANNING_CRITERIA_ID nowait;
196 begin
197 open c;
198 fetch c into recinfo;
199 if (c%notfound) then
200 close c;
201 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
202 app_exception.raise_exception;
203 end if;
204 close c;
205 if ( ((recinfo.LABOR_SETUP_MODE = X_LABOR_SETUP_MODE)
206 OR ((recinfo.LABOR_SETUP_MODE is null) AND (X_LABOR_SETUP_MODE is null)))
207 AND ((recinfo.PLANNING_METHOD = X_PLANNING_METHOD)
208 OR ((recinfo.PLANNING_METHOD is null) AND (X_PLANNING_METHOD is null)))
209 AND ((recinfo.TYPE = X_TYPE)
210 OR ((recinfo.TYPE is null) AND (X_TYPE is null)))
211 AND ((recinfo.BACKORDER_FLAG = X_BACKORDER_FLAG)
212 OR ((recinfo.BACKORDER_FLAG is null) AND (X_BACKORDER_FLAG is null)))
213 AND ((recinfo.REJECT_ORDER_LINE_FLAG = X_REJECT_ORDER_LINE_FLAG)
214 OR ((recinfo.REJECT_ORDER_LINE_FLAG is null) AND (X_REJECT_ORDER_LINE_FLAG is null)))
215 AND ((recinfo.REJECT_ALL_LINES_SHIPSET_FLAG = X_REJECT_ALL_LINES_SHIPSET_FLA)
216 OR ((recinfo.REJECT_ALL_LINES_SHIPSET_FLAG is null) AND (X_REJECT_ALL_LINES_SHIPSET_FLA is null)))
217 AND ((recinfo.REJECT_ALL_LINES_MODEL_FLAG = X_REJECT_ALL_LINES_MODEL_FLAG)
218 OR ((recinfo.REJECT_ALL_LINES_MODEL_FLAG is null) AND (X_REJECT_ALL_LINES_MODEL_FLAG is null)))
219 AND ((recinfo.REJECT_ORDER_FLAG = X_REJECT_ORDER_FLAG)
220 OR ((recinfo.REJECT_ORDER_FLAG is null) AND (X_REJECT_ORDER_FLAG is null)))
221 AND ((recinfo.RESERVE_STOCK_FLAG = X_RESERVE_STOCK_FLAG)
222 OR ((recinfo.RESERVE_STOCK_FLAG is null) AND (X_RESERVE_STOCK_FLAG is null)))
223 AND ((recinfo.AUTO_CREATE_DELIVERIES_FLAG = X_AUTO_CREATE_DELIVERIES_FLAG)
224 OR ((recinfo.AUTO_CREATE_DELIVERIES_FLAG is null) AND (X_AUTO_CREATE_DELIVERIES_FLAG is null)))
225 AND ((recinfo.CREDIT_CHECK_HOLD_FLAG = X_CREDIT_CHECK_HOLD_FLAG)
226 OR ((recinfo.CREDIT_CHECK_HOLD_FLAG is null) AND (X_CREDIT_CHECK_HOLD_FLAG is null)))
227 AND ((recinfo.PICKING_SUBINVENTORY = X_PICKING_SUBINVENTORY)
228 OR ((recinfo.PICKING_SUBINVENTORY is null) AND (X_PICKING_SUBINVENTORY is null)))
229 AND ((recinfo.DESTINATION_SUBINVENTORY = X_DESTINATION_SUBINVENTORY)
230 OR ((recinfo.DESTINATION_SUBINVENTORY is null) AND (X_DESTINATION_SUBINVENTORY is null)))
231 AND ((recinfo.BULK_LABOR_PLANNING_FLAG = X_BULK_LABOR_PLANNING_FLAG)
232 OR ((recinfo.BULK_LABOR_PLANNING_FLAG is null) AND (X_BULK_LABOR_PLANNING_FLAG is null)))
233 AND ((recinfo.TIME_UOM = X_TIME_UOM)
234 OR ((recinfo.TIME_UOM is null) AND (X_TIME_UOM is null)))
235 AND ((recinfo.DEPARTMENT_CODE = X_DEPARTMENT_CODE)
236 OR ((recinfo.DEPARTMENT_CODE is null) AND (X_DEPARTMENT_CODE is null)))
237 AND ((recinfo.DEPARTMENT_ID = X_DEPARTMENT_ID)
238 OR ((recinfo.DEPARTMENT_ID is null) AND (X_DEPARTMENT_ID is null)))
239 AND ((recinfo.ENABLE_LABOR_PLANNING = X_ENABLE_LABOR_PLANNING)
240 OR ((recinfo.ENABLE_LABOR_PLANNING is null) AND (X_ENABLE_LABOR_PLANNING is null)))
241 AND ((recinfo.CROSSDOCK_CRITERIA = X_CROSSDOCK_CRITERIA)
242 OR ((recinfo.CROSSDOCK_CRITERIA is null) AND (X_CROSSDOCK_CRITERIA is null)))
243 AND ((recinfo.CROSSDOCK_CRITERIA_ID = X_CROSSDOCK_CRITERIA_ID)
244 OR ((recinfo.CROSSDOCK_CRITERIA_ID is null) AND (X_CROSSDOCK_CRITERIA_ID is null)))
245 AND ((recinfo.ALLOCATION_METHOD = X_ALLOCATION_METHOD)
246 OR ((recinfo.ALLOCATION_METHOD is null) AND (X_ALLOCATION_METHOD is null)))
247 ) then
248 null;
249 else
250 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
251 app_exception.raise_exception;
252 end if;
253
254 for tlinfo in c1 loop
255 if (tlinfo.BASELANG = 'Y') then
256 if ( ((tlinfo.PLANNING_CRITERIA = X_PLANNING_CRITERIA)
257 OR ((tlinfo.PLANNING_CRITERIA is null) AND (X_PLANNING_CRITERIA is null)))
258 ) then
259 null;
260 else
261 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
262 app_exception.raise_exception;
263 end if;
264 end if;
265 end loop;
266 return;
267 end LOCK_ROW;
268
269 procedure UPDATE_ROW (
270 X_PLANNING_CRITERIA_ID in NUMBER,
271 X_LABOR_SETUP_MODE in VARCHAR2,
272 X_PLANNING_METHOD in VARCHAR2,
273 X_TYPE in VARCHAR2,
274 X_BACKORDER_FLAG in VARCHAR2,
275 X_REJECT_ORDER_LINE_FLAG in VARCHAR2,
276 X_REJECT_ALL_LINES_SHIPSET_FLA in VARCHAR2,
277 X_REJECT_ALL_LINES_MODEL_FLAG in VARCHAR2,
278 X_REJECT_ORDER_FLAG in VARCHAR2,
279 X_RESERVE_STOCK_FLAG in VARCHAR2,
280 X_AUTO_CREATE_DELIVERIES_FLAG in VARCHAR2,
281 X_CREDIT_CHECK_HOLD_FLAG in VARCHAR2,
282 X_PICKING_SUBINVENTORY in VARCHAR2,
283 X_DESTINATION_SUBINVENTORY in VARCHAR2,
284 X_BULK_LABOR_PLANNING_FLAG in VARCHAR2,
285 X_TIME_UOM in VARCHAR2,
286 X_DEPARTMENT_CODE in VARCHAR2,
287 X_DEPARTMENT_ID in NUMBER,
288 X_ENABLE_LABOR_PLANNING in VARCHAR2,
289 X_CROSSDOCK_CRITERIA in VARCHAR2,
290 X_CROSSDOCK_CRITERIA_ID in NUMBER,
291 X_ALLOCATION_METHOD in VARCHAR2,
292 X_PLANNING_CRITERIA in VARCHAR2,
293 X_LAST_UPDATE_DATE in DATE,
294 X_LAST_UPDATED_BY in NUMBER,
295 X_LAST_UPDATE_LOGIN in NUMBER
296 ) is
297 begin
298 update WMS_WP_PLANNING_CRITERIA_B set
299 LABOR_SETUP_MODE = X_LABOR_SETUP_MODE,
300 PLANNING_METHOD = X_PLANNING_METHOD,
301 TYPE = X_TYPE,
302 BACKORDER_FLAG = X_BACKORDER_FLAG,
303 REJECT_ORDER_LINE_FLAG = X_REJECT_ORDER_LINE_FLAG,
304 REJECT_ALL_LINES_SHIPSET_FLAG = X_REJECT_ALL_LINES_SHIPSET_FLA,
305 REJECT_ALL_LINES_MODEL_FLAG = X_REJECT_ALL_LINES_MODEL_FLAG,
306 REJECT_ORDER_FLAG = X_REJECT_ORDER_FLAG,
307 RESERVE_STOCK_FLAG = X_RESERVE_STOCK_FLAG,
308 AUTO_CREATE_DELIVERIES_FLAG = X_AUTO_CREATE_DELIVERIES_FLAG,
309 CREDIT_CHECK_HOLD_FLAG = X_CREDIT_CHECK_HOLD_FLAG,
310 PICKING_SUBINVENTORY = X_PICKING_SUBINVENTORY,
311 DESTINATION_SUBINVENTORY = X_DESTINATION_SUBINVENTORY,
312 BULK_LABOR_PLANNING_FLAG = X_BULK_LABOR_PLANNING_FLAG,
313 TIME_UOM = X_TIME_UOM,
314 DEPARTMENT_CODE = X_DEPARTMENT_CODE,
315 DEPARTMENT_ID = X_DEPARTMENT_ID,
316 ENABLE_LABOR_PLANNING = X_ENABLE_LABOR_PLANNING,
317 CROSSDOCK_CRITERIA = X_CROSSDOCK_CRITERIA,
318 CROSSDOCK_CRITERIA_ID = X_CROSSDOCK_CRITERIA_ID,
319 ALLOCATION_METHOD = X_ALLOCATION_METHOD,
320 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
321 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
322 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
323 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID;
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328
329 update WMS_WP_PLANNING_CRITERIA_TL set
330 PLANNING_CRITERIA = X_PLANNING_CRITERIA,
331 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
332 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
333 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
334 SOURCE_LANG = userenv('LANG')
335 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID
336 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
337
338 if (sql%notfound) then
339 raise no_data_found;
340 end if;
341 end UPDATE_ROW;
342
343 procedure DELETE_ROW (
344 X_PLANNING_CRITERIA_ID in NUMBER
345 ) is
346 begin
347 delete from WMS_WP_PLANNING_CRITERIA_TL
348 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID;
349
350 if (sql%notfound) then
351 raise no_data_found;
352 end if;
353
354 delete from WMS_WP_PLANNING_CRITERIA_B
355 where PLANNING_CRITERIA_ID = X_PLANNING_CRITERIA_ID;
356
357 if (sql%notfound) then
358 raise no_data_found;
359 end if;
360 end DELETE_ROW;
361
362 procedure ADD_LANGUAGE
363 is
364 begin
365 delete from WMS_WP_PLANNING_CRITERIA_TL T
366 where not exists
367 (select NULL
368 from WMS_WP_PLANNING_CRITERIA_B B
369 where B.PLANNING_CRITERIA_ID = T.PLANNING_CRITERIA_ID
370 );
371
372 update WMS_WP_PLANNING_CRITERIA_TL T set (
373 PLANNING_CRITERIA
374 ) = (select
375 B.PLANNING_CRITERIA
376 from WMS_WP_PLANNING_CRITERIA_TL B
377 where B.PLANNING_CRITERIA_ID = T.PLANNING_CRITERIA_ID
378 and B.LANGUAGE = T.SOURCE_LANG)
379 where (
380 T.PLANNING_CRITERIA_ID,
381 T.LANGUAGE
382 ) in (select
383 SUBT.PLANNING_CRITERIA_ID,
384 SUBT.LANGUAGE
385 from WMS_WP_PLANNING_CRITERIA_TL SUBB, WMS_WP_PLANNING_CRITERIA_TL SUBT
386 where SUBB.PLANNING_CRITERIA_ID = SUBT.PLANNING_CRITERIA_ID
387 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
388 and (SUBB.PLANNING_CRITERIA <> SUBT.PLANNING_CRITERIA
389 or (SUBB.PLANNING_CRITERIA is null and SUBT.PLANNING_CRITERIA is not null)
390 or (SUBB.PLANNING_CRITERIA is not null and SUBT.PLANNING_CRITERIA is null)
391 ));
392
393 insert into WMS_WP_PLANNING_CRITERIA_TL (
394 PLANNING_CRITERIA_ID,
395 PLANNING_CRITERIA,
396 CREATED_BY,
397 CREATION_DATE,
398 LAST_UPDATED_BY,
399 LAST_UPDATE_DATE,
400 LAST_UPDATE_LOGIN,
401 LANGUAGE,
402 SOURCE_LANG
403 ) select /*+ ORDERED */
404 B.PLANNING_CRITERIA_ID,
405 B.PLANNING_CRITERIA,
409 B.LAST_UPDATE_DATE,
406 B.CREATED_BY,
407 B.CREATION_DATE,
408 B.LAST_UPDATED_BY,
410 B.LAST_UPDATE_LOGIN,
411 L.LANGUAGE_CODE,
412 B.SOURCE_LANG
413 from WMS_WP_PLANNING_CRITERIA_TL B, FND_LANGUAGES L
414 where L.INSTALLED_FLAG in ('I', 'B')
415 and B.LANGUAGE = userenv('LANG')
416 and not exists
417 (select NULL
418 from WMS_WP_PLANNING_CRITERIA_TL T
419 where T.PLANNING_CRITERIA_ID = B.PLANNING_CRITERIA_ID
420 and T.LANGUAGE = L.LANGUAGE_CODE);
421 end ADD_LANGUAGE;
422
423 end WMS_WP_PLANNING_CRITERIA_PKG;
[Home] [Help]