1 PACKAGE BODY WMS_SELECTION_CRITERIA_TXN_PKG AS
2 /* $Header: WMSSCTXB.pls 120.1 2005/05/27 01:43:41 appldev $ */
3 --
4 PROCEDURE INSERT_ROW (
5 X_STG_ASSIGNMENT_ID IN NUMBER
6 ,X_SEQUENCE_NUMBER IN NUMBER
7 ,X_RULE_TYPE_CODE IN NUMBER
8 ,X_RETURN_TYPE_CODE IN VARCHAR2
9 ,X_RETURN_TYPE_ID IN NUMBER
10 ,X_ENABLED_FLAG IN VARCHAR2
11 ,X_DATE_TYPE_CODE IN VARCHAR2
12 ,X_DATE_TYPE_FROM IN NUMBER
13 ,X_DATE_TYPE_TO IN NUMBER
14 ,X_DATE_TYPE_LOOKUP_TYPE IN VARCHAR2
15 ,X_EFFECTIVE_FROM IN DATE
16 ,X_EFFECTIVE_TO IN DATE
17 ,X_FROM_ORGANIZATION_ID IN NUMBER
18 ,X_FROM_SUBINVENTORY_NAME IN VARCHAR2
19 ,X_TO_ORGANIZATION_ID IN NUMBER
20 ,X_TO_SUBINVENTORY_NAME IN VARCHAR2
21 ,X_CUSTOMER_ID IN NUMBER
22 ,X_FREIGHT_CODE IN VARCHAR2
23 ,X_INVENTORY_ITEM_ID IN NUMBER
24 ,X_ITEM_TYPE IN VARCHAR2
25 ,X_ASSIGNMENT_GROUP_ID IN NUMBER
26 ,X_ABC_CLASS_ID IN NUMBER
27 ,X_CATEGORY_SET_ID IN NUMBER
28 ,X_CATEGORY_ID IN NUMBER
29 ,X_ORDER_TYPE_ID IN NUMBER
30 ,X_VENDOR_ID IN NUMBER
31 ,X_PROJECT_ID IN NUMBER
32 ,X_TASK_ID IN NUMBER
33 ,X_USER_ID IN NUMBER
34 ,X_TRANSACTION_ACTION_ID IN NUMBER
35 ,X_REASON_ID IN NUMBER
36 ,X_TRANSACTION_SOURCE_TYPE_ID IN NUMBER
37 ,X_TRANSACTION_TYPE_ID IN NUMBER
38 ,X_UOM_CODE IN VARCHAR2
39 ,X_UOM_CLASS IN VARCHAR2
40 ,X_LOCATION_ID IN NUMBER
41 ,X_LAST_UPDATED_BY IN NUMBER
42 ,X_LAST_UPDATE_DATE IN DATE
43 ,X_CREATED_BY IN NUMBER
44 ,X_CREATION_DATE IN DATE
45 ,X_LAST_UPDATE_LOGIN IN NUMBER
46 ) IS
47 x_seq NUMBER;
48
49
50 cursor c is select stg_assignment_id from wms_selection_criteria_txn
51 where sequence_number = x_sequence_number
52 and rule_type_code = x_rule_type_code
53 and return_type_code = x_return_type_code
54 and return_type_id = x_return_type_id;
55
56
57 begin
58
59 insert into wms_selection_criteria_txn (
60 stg_assignment_id
61 ,sequence_number
62 ,rule_type_code
63 ,return_type_code
64 ,return_type_id
65 ,enabled_flag
66 ,date_type_code
67 ,date_type_from
68 ,date_type_to
69 ,date_type_lookup_type
70 ,effective_from
71 ,effective_to
72 ,from_organization_id
73 ,from_subinventory_name
74 ,to_organization_id
75 ,to_subinventory_name
76 ,customer_id
77 ,freight_code
78 ,inventory_item_id
79 ,item_type
80 ,assignment_group_id
81 ,abc_class_id
82 ,category_set_id
83 ,category_id
84 ,order_type_id
85 ,vendor_id
86 ,project_id
87 ,task_id
88 ,user_id
89 ,transaction_action_id
90 ,reason_id
91 ,transaction_source_type_id
92 ,transaction_type_id
93 ,uom_code
94 ,uom_class
95 ,location_id
96 ,last_updated_by
97 ,last_update_date
98 ,created_by
99 ,creation_date
100 ,last_update_login
101 ) values (
102 x_stg_assignment_id
103 ,x_sequence_number
104 ,x_rule_type_code
105 ,x_return_type_code
106 ,x_return_type_id
107 ,x_enabled_flag
108 ,x_date_type_code
109 ,x_date_type_from
110 ,x_date_type_to
111 ,x_date_type_lookup_type
112 ,x_effective_from
113 ,x_effective_to
114 ,x_from_organization_id
115 ,x_from_subinventory_name
116 ,x_to_organization_id
117 ,x_to_subinventory_name
118 ,x_customer_id
119 ,x_freight_code
120 ,x_inventory_item_id
121 ,x_item_type
122 ,x_assignment_group_id
123 ,x_abc_class_id
124 ,x_category_set_id
125 ,x_category_id
126 ,x_order_type_id
127 ,x_vendor_id
128 ,x_project_id
129 ,x_task_id
130 ,x_user_id
131 ,x_transaction_action_id
132 ,x_reason_id
133 ,x_transaction_source_type_id
134 ,x_transaction_type_id
135 ,x_uom_code
136 ,x_uom_class
137 ,x_location_id
138 ,x_last_updated_by
139 ,x_last_update_date
140 ,x_created_by
141 ,x_creation_date
142 ,x_last_update_login );
143 open c;
144
145 fetch c into x_seq;
146 if (c%notfound) then
147 close c;
148 raise no_data_found;
149 end if;
150 close c;
151 --- Bug 4038209
152 x_seq := 0;
153 SELECT COUNT( WSCT.sequence_number )
154 INTO x_seq
155 FROM wms_selection_criteria_txn WSCT
156 WHERE WSCT.from_organization_id = x_from_organization_id
157 AND WSCT.sequence_number = x_Sequence_number
158 AND WSCT.rule_type_code = x_rule_type_code ;
159
160 if x_seq > 1 then
161 FND_MESSAGE.Set_Name('WMS', 'WMS_UNIQUE_STRA_ASSIGNMENT');
162 app_exception.raise_exception;
163 end if;
164 --- End of Bug 4038209
165 end insert_row;
166
167
168
169 procedure lock_row (
170 x_stg_assignment_id IN NUMBER
171 ,x_sequence_number IN NUMBER
172 ,x_rule_type_code IN NUMBER
173 ,x_return_type_code IN VARCHAR2
174 ,x_return_type_id IN NUMBER
175 ,x_enabled_flag IN VARCHAR2
176 ,x_date_type_code IN VARCHAR2
177 ,x_date_type_from IN NUMBER
178 ,x_date_type_to IN NUMBER
179 ,x_date_type_lookup_type IN VARCHAR2
180 ,x_effective_from IN DATE
181 ,x_effective_to IN DATE
182 ,x_from_organization_id IN NUMBER
183 ,x_from_subinventory_name IN VARCHAR2
184 ,x_to_organization_id IN NUMBER
185 ,x_to_subinventory_name IN VARCHAR2
186 ,x_customer_id IN NUMBER
187 ,x_freight_code IN VARCHAR2
188 ,x_inventory_item_id IN NUMBER
189 ,x_item_type IN VARCHAR2
190 ,x_assignment_group_id IN NUMBER
191 ,x_abc_class_id IN NUMBER
192 ,x_category_set_id IN NUMBER
193 ,x_category_id IN NUMBER
194 ,x_order_type_id IN NUMBER
195 ,x_vendor_id IN NUMBER
196 ,x_project_id IN NUMBER
197 ,x_task_id IN NUMBER
198 ,x_user_id IN NUMBER
199 ,x_transaction_action_id IN NUMBER
200 ,x_reason_id IN NUMBER
201 ,x_transaction_source_type_id IN NUMBER
202 ,x_transaction_type_id IN NUMBER
203 ,x_uom_code IN VARCHAR2
204 ,x_uom_class IN VARCHAR2
205 ,X_LOCATION_ID IN NUMBER
206 ) is
207 cursor c is select
208 stg_assignment_id
209 ,sequence_number
210 ,rule_type_code
211 ,return_type_code
212 ,return_type_id
213 ,enabled_flag
214 ,date_type_code
215 ,date_type_from
216 ,date_type_to
217 ,date_type_lookup_type
218 ,effective_from
219 ,effective_to
220 ,from_organization_id
221 ,from_subinventory_name
222 ,to_organization_id
223 ,to_subinventory_name
224 ,customer_id
225 ,freight_code
226 ,inventory_item_id
227 ,item_type
228 ,assignment_group_id
229 ,abc_class_id
230 ,category_set_id
231 ,category_id
232 ,order_type_id
233 ,vendor_id
234 ,project_id
235 ,task_id
236 ,user_id
237 ,transaction_action_id
238 ,reason_id
239 ,transaction_source_type_id
240 ,transaction_type_id
241 ,uom_code
242 ,uom_class
243 ,location_id
244 from wms_selection_criteria_txn
245 where stg_assignment_id = x_stg_assignment_id
246 for update of stg_assignment_id NOWAIT;
247
248 recinfo c%rowtype;
249
250 begin
251 open c;
252 fetch c into recinfo;
253 if (c%notfound) then
254 close c;
255 fnd_message.set_name('fnd', 'form_record_deleted');
256 app_exception.raise_exception;
257 end if;
258 close c;
259 if ( (recinfo.sequence_number = x_sequence_number)
260 and (recinfo.stg_assignment_id = x_stg_assignment_id )
261 and ((recinfo.rule_type_code = x_rule_type_code)
262 or ((recinfo.rule_type_code is NULL)
263 and (x_rule_type_code is NULL)))
264 and ((recinfo.return_type_code = x_return_type_code)
265 or (recinfo.return_type_code is NULL)
266 and (x_return_type_code is NULL)))
267 and ((recinfo.return_type_id = x_return_type_id)
268 or ((recinfo.return_type_id is NULL)
269 and (x_return_type_id is NULL)))
270 and ((recinfo.enabled_flag = x_enabled_flag)
271 or ((recinfo.enabled_flag is NULL)
272 and (x_enabled_flag is NULL)))
273 and ((recinfo.date_type_code = x_date_type_code)
274 or ((recinfo.date_type_code is NULL)
275 and (x_date_type_code is NULL)))
276 and ((recinfo.date_type_from = x_date_type_from )
277 or ((recinfo.date_type_from is NULL
278 and (x_date_type_from is NULL)))
279 and ((recinfo.date_type_to = x_date_type_to )
280 or ((recinfo.date_type_to is NULL)
281 and (x_date_type_to is NULL )))
282 and ((recinfo.date_type_lookup_type = x_date_type_lookup_type )
283 or ((recinfo.date_type_lookup_type is NULL)
284 and (x_date_type_lookup_type is NULL)))
285 and ((recinfo.effective_from = x_effective_from )
286 or ((recinfo.effective_from is NULL)
287 and ( x_effective_from is NULL )))
288 and ((recinfo.effective_to = x_effective_to )
289 or ((recinfo.effective_to is NULL )
290 and ( x_effective_to is NULL )))
291 and ((recinfo.from_organization_id = x_from_organization_id )
292 or ((recinfo.from_organization_id is NULL )
293 and (x_from_organization_id is NULL )))
294 and ((recinfo.from_subinventory_name = x_from_subinventory_name )
295 or ((recinfo.from_subinventory_name is NULL )
296 and (x_from_subinventory_name is NULL )))
297 and ((recinfo.to_organization_id = x_to_organization_id )
298 or ((recinfo.to_organization_id is NULL )
299 and (x_to_organization_id is NULL )))
300 and ((recinfo.to_subinventory_name = x_to_subinventory_name)
301 or ((recinfo.to_subinventory_name is NULL )
302 and ( x_to_subinventory_name is NULL )))
303 and ((recinfo.customer_id = x_customer_id )
304 or ((recinfo.customer_id is NULL )
305 and (x_customer_id is NULL )))
306 and ((recinfo.freight_code = x_freight_code )
307 or ((recinfo.freight_code is NULL )
308 and (x_freight_code is NULL )))
309 and ((recinfo.inventory_item_id = x_inventory_item_id )
310 or ((recinfo.inventory_item_id is NULL )
311 and (x_inventory_item_id is NULL )))
312 and ((recinfo.item_type = x_item_type)
313 or ((recinfo.item_type is NULL )
314 and (x_item_type is NULL )))
315 and ((recinfo.assignment_group_id = x_assignment_group_id)
316 or ((recinfo.assignment_group_id is NULL )
317 and (x_assignment_group_id is NULL )))
318 and ((recinfo.abc_class_id = x_abc_class_id )
319 or ((recinfo.abc_class_id is NULL )
320 and (x_abc_class_id is NULL )))
321 and ((recinfo.category_set_id = x_category_set_id)
322 or ((recinfo.category_set_id is NULL )
323 and ( x_category_set_id is NULL )))
324 and ((recinfo.category_id = x_category_id)
325 or ((recinfo.category_id is NULL )
326 and ( x_category_id is NULL )))
327 and ((recinfo.order_type_id = x_order_type_id)
328 or ((recinfo.order_type_id is NULL )
329 and ( x_order_type_id is NULL )))
330 and ((recinfo.vendor_id = x_vendor_id)
331 or ((recinfo.vendor_id is NULL )
332 and ( x_vendor_id is NULL )))
333 and ((recinfo.project_id = x_project_id )
334 or ((recinfo.project_id is NULL )
335 and (x_project_id is NULL )))
336 and ((recinfo.task_id = x_task_id )
337 or ((recinfo.task_id is NULL )
338 and ( x_task_id is NULL )))
339 and ((recinfo.user_id = x_user_id)
340 or ((recinfo.user_id is NULL )
341 and (x_user_id is NULL )))
342 and ((recinfo.transaction_action_id = x_transaction_action_id)
343 or ((recinfo.transaction_action_id is NULL )
344 and ( x_transaction_action_id is NULL )))
345 and ((recinfo.reason_id = x_reason_id )
346 or ((recinfo.reason_id is NULL )
347 and ( x_reason_id is NULL )))
348 and ((recinfo.transaction_source_type_id =x_transaction_source_type_id )
349 or ((recinfo.transaction_source_type_id is NULL )
350 and (x_transaction_source_type_id is NULL )))
351 and ((recinfo.transaction_type_id = x_transaction_type_id )
352 or ((recinfo.transaction_type_id is NULL )
353 and (x_transaction_type_id is NULL )))
354 and ((recinfo.uom_code = x_uom_code )
355 or ((recinfo.uom_code is NULL )
356 and (x_uom_code is NULL )))
357 and ((recinfo.location_id = x_location_id )
358 or ((recinfo.location_id is NULL )
359 and (x_location_id is NULL )))
360 ) then
361 null;
362 else
363 fnd_message.set_name('fnd','form_record_changed');
364 app_exception.raise_exception;
365 end if;
366 end lock_row;
367
368 procedure update_row (
372 ,x_return_type_code in varchar2
369 x_stg_assignment_id in number
370 ,x_sequence_number in number
371 ,x_rule_type_code in number
373 ,x_return_type_id in number
374 ,x_enabled_flag in varchar2
375 ,x_date_type_code in varchar2
376 ,x_date_type_from in number
377 ,x_date_type_to in number
378 ,x_date_type_lookup_type in varchar2
379 ,x_effective_from in date
380 ,x_effective_to in date
381 ,x_from_organization_id in number
382 ,x_from_subinventory_name in varchar2
383 ,x_to_organization_id in number
384 ,x_to_subinventory_name in varchar2
385 ,x_customer_id in number
386 ,x_freight_code in varchar2
387 ,x_inventory_item_id in number
388 ,x_item_type in varchar2
389 ,x_assignment_group_id in number
390 ,x_abc_class_id in number
391 ,x_category_set_id in number
392 ,x_category_id in number
393 ,x_order_type_id in number
394 ,x_vendor_id in number
395 ,x_project_id in number
396 ,x_task_id in number
397 ,x_user_id in number
398 ,x_transaction_action_id in number
399 ,x_reason_id in number
400 ,x_transaction_source_type_id in number
401 ,x_transaction_type_id in number
402 ,x_uom_code in varchar2
403 ,x_uom_class in varchar2
404 ,X_LOCATION_ID IN NUMBER
405 ,x_last_updated_by in number
406 ,x_last_update_date in date
407 ,x_last_update_login in number
408 ) is
409
410 begin
411 -- if (stg_assignment_id is not null) then
412 update wms_selection_criteria_txn set
413 sequence_number = x_sequence_number
414 ,rule_type_code = x_rule_type_code
415 ,return_type_code = x_return_type_code
416 ,return_type_id = x_return_type_id
417 ,enabled_flag = x_enabled_flag
418 ,date_type_code = x_date_type_code
419 ,date_type_from = x_date_type_from
420 ,date_type_to = x_date_type_to
421 ,date_type_lookup_type = x_date_type_lookup_type
422 ,effective_from = x_effective_from
423 ,effective_to = x_effective_to
424 ,from_organization_id = x_from_organization_id
425 ,from_subinventory_name = x_from_subinventory_name
426 ,to_organization_id = x_to_organization_id
427 ,to_subinventory_name = x_to_subinventory_name
428 ,customer_id = x_customer_id
429 ,freight_code = x_freight_code
430 ,inventory_item_id = x_inventory_item_id
431 ,item_type = x_item_type
432 ,assignment_group_id = x_assignment_group_id
433 ,abc_class_id = x_abc_class_id
434 ,category_set_id = x_category_set_id
435 ,category_id = x_category_id
436 ,order_type_id = x_order_type_id
437 ,vendor_id = x_vendor_id
438 ,project_id = x_project_id
439 ,task_id = x_task_id
440 ,user_id = x_user_id
441 ,transaction_action_id = x_transaction_action_id
442 ,reason_id = x_reason_id
443 ,transaction_source_type_id = x_transaction_source_type_id
444 ,transaction_type_id = x_transaction_type_id
445 ,uom_code = x_uom_code
446 ,uom_class = x_uom_class
447 ,location_id = x_location_id
448 ,last_updated_by = x_last_updated_by
449 ,last_update_date = x_last_update_date
450 ,last_update_login = x_last_update_login
451 where stg_assignment_id = x_stg_assignment_id;
452 -- end if;
453
454 if (sql%notfound) then
455 raise no_data_found;
456 end if;
457 end update_row;--
458 procedure delete_row (
459 X_STG_ASSIGNMENT_ID IN NUMBER
460 )is
461 begin
462
463 delete wms_selection_criteria_txn
464 where stg_assignment_id = x_stg_assignment_id;
465
466
467 /*if (sql%notfound) then
468 raise no_data_found;
469 end if; */
470 end delete_row;
471
472
473 END WMS_SELECTION_CRITERIA_TXN_PKG;
[Home] [Help]