[Home] [Help]
PACKAGE BODY: APPS.WMS_TASK_SKIPPED
Source
1 PACKAGE BODY wms_task_skipped AS
2 --/* $Header: WMSSKIPB.pls 120.5 2011/08/16 21:24:49 sahmahes ship $ */
3
4
5 PROCEDURE mydebug(msg in varchar2)
6 IS
7 l_msg VARCHAR2(5100);
8 l_ts VARCHAR2(30);
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
12 l_msg:=l_ts||' '||msg;
13
14
15 inv_mobile_helper_functions.tracelog
16 (p_err_msg => l_msg,
17 p_module => 'wms_skip_task',
18 p_level => 4);
19
20 -- dbms_output.put_line(l_msg);
21
22 null;
23 END;
24
25
26
27 PROCEDURE skip_task_adjustments
28 (x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 p_sign_on_emp_id IN NUMBER,
32 p_sign_on_org_id IN NUMBER,
33 p_task_id IN NUMBER,
34 p_wms_task_type IN NUMBER)
35
36
37
38 IS
39 PRAGMA AUTONOMOUS_TRANSACTION;
40 l_emp_id NUMBER;
41 l_org_id NUMBER;
42 l_task_id NUMBER;
43 l_sequence NUMBER;
44 l_inventory_item_id NUMBER;
45 l_last_updated_by wms_dispatched_tasks.last_updated_by%TYPE;
46 l_created_by wms_dispatched_tasks.created_by%TYPE;
47 l_progress VARCHAR2(10);
48
49 --BUG 12769978 begin
50 l_lot_control_code NUMBER;
51 l_serial_control_code NUMBER;
52 l_serial_allocated_flag VARCHAR2(1);
53 l_serial_transaction_temp_id NUMBER;
54 --BUG 12769978 end
55
56 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
57 BEGIN
58 l_progress := '10';
59 SAVEPOINT sp_skip_task_adjustments;
60 l_emp_id := p_sign_on_emp_id ;
61 l_org_id := p_sign_on_org_id;
62 l_task_id := p_task_id;
63 l_last_updated_by := FND_GLOBAL.user_id; ---*************** AFSCGBLS.pls
64
65 IF (l_debug = 1) THEN
66 mydebug('WMSSKIPB: value of EMP_ID '|| l_emp_id );
67 mydebug('WMSSKIPB: value of ORG_ID '|| l_org_id );
68 mydebug('WMSSKIPB: value of Transaction_temp_id '|| l_task_id );
69 mydebug('WMSSKIPB: value of last_ypdated_by '|| l_last_updated_by );
70 mydebug('WMSSKIPB: value of p_wms_task_type :'|| p_wms_task_type );
71 END IF;
72
73 l_progress := '20';
74
75 SELECT wms_exceptions_s.NEXTVAL INTO l_sequence from dual;
76
77 IF (l_debug = 1) THEN
78 mydebug('l_sequence: ' || l_sequence);
79 END IF;
80
81 BEGIN
82 /* Bug#5563901.Replaced wms_dispatchable_tasks_v by a query from MMTT and MCCE */
83 SELECT t.inventory_item_id, w.created_by
84 INTO l_inventory_item_id, l_created_by
85 FROM wms_dispatched_tasks w ,
86 (SELECT mmtt.transaction_temp_id task_id, mmtt.wms_task_type wms_task_type_id,
87 mmtt.inventory_item_id
88 FROM mtl_material_transactions_temp mmtt
89 WHERE mmtt.transaction_temp_id = l_task_id
90 AND mmtt.wms_task_type IS NOT NULL AND mmtt.transaction_status = 2
91 UNION ALL
92 SELECT mcce.cycle_count_entry_id task_id, 3 wms_task_type_id,mcce.inventory_item_id
93 FROM mtl_cycle_count_entries mcce
94 WHERE mcce.cycle_count_entry_id=l_task_id
95 AND entry_status_code IN (1,3) AND NVL(export_flag, 2) = 2 ) t
96 WHERE t.task_id = l_task_id
97 AND t.wms_task_type_id = p_wms_task_type
98 AND t.task_id = w.transaction_temp_id
99 AND t.wms_task_type_id = w.task_type;
100
101 EXCEPTION
102 WHEN no_data_found THEN
103 IF (l_debug = 1) THEN
104 mydebug('No WDT line for that MMTT/MCCE');
105 END IF;
106 RAISE FND_API.g_exc_error;
107 END;
108
109 l_progress := '30';
110 IF (l_debug = 1) THEN
111 mydebug('WMSSKIPB: value of l_inventory_item_id ' || l_inventory_item_id);
112 mydebug('Before inserting into WMS_exception');
113 END IF;
114
115 INSERT INTO wms_exceptions(
116 TASK_ID,
117 SEQUENCE_NUMBER,
118 ORGANIZATION_ID,
119 INVENTORY_ITEM_ID,
120 PERSON_ID,
121 EFFECTIVE_START_DATE,
122 EFFECTIVE_END_DATE ,
123 DISCREPANCY_TYPE,
124 LOT_NUMBER,
125 LAST_UPDATE_DATE,
126 LAST_UPDATED_BY,
127 CREATION_DATE,
128 created_by,
129 wms_task_type
130 )
131 VALUES( l_task_id,
132 l_sequence,
133 l_org_id,
134 l_inventory_item_id,
135 l_emp_id,
136 Sysdate,
137 Sysdate,
138 1,
139 -999,
140 Sysdate,
141 l_last_updated_by,
142 Sysdate,
143 l_created_by,
144 p_wms_task_type
145 );
146
147 IF (l_debug = 1) THEN
148 mydebug('After inserting into WMS_exception');
149 END IF;
150 l_progress := '40';
151
152 IF (l_debug = 1) THEN
153 mydebug('Before inserting into WMS_skip_task_exceptions');
154 END IF;
155
156 INSERT INTO wms_skip_task_exceptions(
157 TASK_ID,
158 SEQUENCE_NUMBER,
159 ORGANIZATION_ID,
160 INVENTORY_ITEM_ID,
161 PERSON_ID,
162 EFFECTIVE_START_DATE,
163 EFFECTIVE_END_DATE ,
164 DISCREPANCY_TYPE,
165 LOT_NUMBER,
166 LAST_UPDATE_DATE,
167 LAST_UPDATED_BY,
168 CREATION_DATE,
169 created_by,
170 wms_task_type
171 )
172 VALUES( l_task_id,
173 l_sequence,
174 l_org_id,
175 l_inventory_item_id,
176 l_emp_id,
177 Sysdate,
178 Sysdate,
179 3,
180 -999,
181 Sysdate,
182 l_last_updated_by,
183 Sysdate,
184 l_created_by,
185 p_wms_task_type
186 );
187
188 IF (l_debug = 1) THEN
189 mydebug('After inserting into WMS_skip_task_exceptions');
190 mydebug('Before Deleting from wms_dispatched_task');
191 END IF;
192 l_progress := '50';
193
194 DELETE FROM wms_dispatched_tasks
195 WHERE transaction_temp_id = l_task_id AND
196 task_type = p_wms_task_type;
197 IF (l_debug = 1) THEN
198 mydebug('After Deleting from wms_dispatched_task');
199 END IF;
200 l_progress := '60';
201
202 --12769978 begin
203 IF (p_wms_task_type <> 3 ) THEN --12869113, do this for non-cycle count tasks
204 IF (l_debug = 1) THEN
205 mydebug('Skip task in case of Lot Substitution for serial items.');
206 mydebug('Before Deleting MSNT and marking group mark id as null for task_id '|| l_task_id);
207 END IF;
208
209 BEGIN
210 select item_lot_control_code, item_serial_control_code, NVL(serial_allocated_flag , 'N')
211 into l_lot_control_code, l_serial_control_code, l_serial_allocated_flag
212 from mtl_material_transactions_temp
213 where transaction_temp_id = l_task_id and organization_id = l_org_id;
214
215 select serial_transaction_temp_id
216 into l_serial_transaction_temp_id
217 from mtl_transaction_lots_temp
218 where transaction_temp_id = l_task_id;
219
220 EXCEPTION
221 WHEN no_data_found THEN --BUG12871057
222 IF (l_debug = 1) THEN
223 mydebug('In case of just plain serial controlled items no MTLT would not be present');
224 END IF;
225 WHEN OTHERS THEN
226 IF (l_debug = 1) THEN
227 mydebug('Some Other Exception occured in skip_adjustments');
228 END IF;
229 END;
230
231 if (l_serial_allocated_flag = 'N' AND l_serial_control_code in (2,5)) then
232
233 UPDATE MTL_SERIAL_NUMBERS
234 SET group_mark_id = null
235 ,last_update_date= SYSDATE
236 ,last_updated_by = l_last_updated_by
237 WHERE serial_number IN
238 (SELECT serial_number
239 FROM mtl_serial_numbers msn
240 ,mtl_serial_numbers_temp msnt
241 WHERE msn.serial_number = msnt.fm_serial_number
242 AND msnt.transaction_temp_id in (l_task_id, l_serial_transaction_temp_id))
243 and current_organization_id = l_org_id;
244
245 IF SQL%NOTFOUND THEN
246 IF (l_debug = 1) THEN
247 mydebug('wms_task_skipped:: No MSN record found.');
248 END IF;
249
250 ELSE
251 delete mtl_serial_numbers_temp
252 where transaction_temp_id in (l_task_id, l_serial_transaction_temp_id);
253
254 IF SQL%NOTFOUND THEN
255 IF (l_debug =1 ) then
256 mydebug('wms_task_skipped:: No MSNT found to delete anything');
257 END IF;
258 END IF;
259 END IF;
260 end if;
261 --12769978 End
262 END IF;
263 x_return_status:=FND_API.g_ret_sts_success;
264
265 IF (l_debug =1 ) then
266 mydebug('wms_task_skipped:: Successfully done with skip_task_adjustments');
267 END IF;
268
269
270 COMMIT;
271 IF (l_debug = 1) THEN
272 mydebug(x_return_status);
273 END IF;
274
275 EXCEPTION
276
277 WHEN FND_API.G_EXC_ERROR THEN
278 ROLLBACK TO sp_skip_task_adjustments;
279 x_return_status:=FND_API.G_RET_STS_ERROR;
280 fnd_msg_pub.count_and_get
281 ( p_count => x_msg_count
282 , p_data => x_msg_data
283 );
284
285 WHEN OTHERS THEN
286 ROLLBACK TO sp_skip_task_adjustments;
287 x_return_status:=FND_API.g_ret_sts_unexp_error;
288 fnd_msg_pub.count_and_get
289 ( p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292
293 IF SQLCODE IS NOT NULL THEN
294 FND_MESSAGE.set_name('WMS', 'WMS_SKIP_TASK_ERROR');
295 fnd_message.set_token ('SQL_CODE',SQLCODE);
296 fnd_msg_pub.ADD;
297 END IF;
298
299
300 END skip_task_adjustments;
301
302
303
304
305 END wms_task_skipped;