[Home] [Help]
PACKAGE BODY: APPS.WMS_TASK_SKIPPED
Source
1 PACKAGE BODY wms_task_skipped AS
2 --/* $Header: WMSSKIPB.pls 120.2 2006/10/06 19:55:27 mchemban noship $ */
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 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
50 BEGIN
51 l_progress := '10';
52 SAVEPOINT sp_skip_task_adjustments;
53 l_emp_id := p_sign_on_emp_id ;
54 l_org_id := p_sign_on_org_id;
55 l_task_id := p_task_id;
56 l_last_updated_by := FND_GLOBAL.user_id; ---*************** AFSCGBLS.pls
57
58 IF (l_debug = 1) THEN
59 mydebug('WMSSKIPB: value of EMP_ID '|| l_emp_id );
60 mydebug('WMSSKIPB: value of ORG_ID '|| l_org_id );
61 mydebug('WMSSKIPB: value of Transaction_temp_id '|| l_task_id );
62 mydebug('WMSSKIPB: value of last_ypdated_by '|| l_last_updated_by );
63 END IF;
64
65 l_progress := '20';
66
67 SELECT wms_exceptions_s.NEXTVAL INTO l_sequence from dual;
68
69 IF (l_debug = 1) THEN
70 mydebug('l_sequence: ' || l_sequence);
71 END IF;
72
73 BEGIN
74 /* Bug#5563901.Replaced wms_dispatchable_tasks_v by a query from MMTT and MCCE */
75 SELECT t.inventory_item_id, w.created_by
76 INTO l_inventory_item_id, l_created_by
77 FROM wms_dispatched_tasks w ,
78 (SELECT mmtt.transaction_temp_id task_id, mmtt.wms_task_type wms_task_type_id,
79 mmtt.inventory_item_id
80 FROM mtl_material_transactions_temp mmtt
81 WHERE mmtt.transaction_temp_id = l_task_id
82 AND mmtt.wms_task_type IS NOT NULL AND mmtt.transaction_status = 2
83 UNION ALL
84 SELECT mcce.cycle_count_entry_id task_id, 3 wms_task_type_id,mcce.inventory_item_id
85 FROM mtl_cycle_count_entries mcce
86 WHERE mcce.cycle_count_entry_id=l_task_id
87 AND entry_status_code IN (1,3) AND NVL(export_flag, 2) = 2 ) t
88 WHERE t.task_id = l_task_id
89 AND t.wms_task_type_id = p_wms_task_type
90 AND t.task_id = w.transaction_temp_id
91 AND t.wms_task_type_id = w.task_type;
92
93 EXCEPTION
94 WHEN no_data_found THEN
95 IF (l_debug = 1) THEN
96 mydebug('No WDT line for that MMTT/MCCE');
97 END IF;
98 RAISE FND_API.g_exc_error;
99 END;
100 l_progress := '30';
101 IF (l_debug = 1) THEN
102 mydebug('WMSSKIPB: value of l_inventory_item_id ' || l_inventory_item_id);
103 mydebug('Before inserting into WMS_exception');
104 END IF;
105
106 INSERT INTO wms_exceptions(
107 TASK_ID,
108 SEQUENCE_NUMBER,
109 ORGANIZATION_ID,
110 INVENTORY_ITEM_ID,
111 PERSON_ID,
112 EFFECTIVE_START_DATE,
113 EFFECTIVE_END_DATE ,
114 DISCREPANCY_TYPE,
115 LOT_NUMBER,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 CREATION_DATE,
119 created_by,
120 wms_task_type
121 )
122 VALUES( l_task_id,
123 l_sequence,
124 l_org_id,
125 l_inventory_item_id,
126 l_emp_id,
127 Sysdate,
128 Sysdate,
129 1,
130 -999,
131 Sysdate,
132 l_last_updated_by,
133 Sysdate,
134 l_created_by,
135 p_wms_task_type
136 );
137
138 IF (l_debug = 1) THEN
139 mydebug('After inserting into WMS_exception');
140 END IF;
141 l_progress := '40';
142
143 IF (l_debug = 1) THEN
144 mydebug('Before inserting into WMS_skip_task_exceptions');
145 END IF;
146
147 INSERT INTO wms_skip_task_exceptions(
148 TASK_ID,
149 SEQUENCE_NUMBER,
150 ORGANIZATION_ID,
151 INVENTORY_ITEM_ID,
152 PERSON_ID,
153 EFFECTIVE_START_DATE,
154 EFFECTIVE_END_DATE ,
155 DISCREPANCY_TYPE,
156 LOT_NUMBER,
157 LAST_UPDATE_DATE,
158 LAST_UPDATED_BY,
159 CREATION_DATE,
160 created_by,
161 wms_task_type
162 )
163 VALUES( l_task_id,
164 l_sequence,
165 l_org_id,
166 l_inventory_item_id,
167 l_emp_id,
168 Sysdate,
169 Sysdate,
170 3,
171 -999,
172 Sysdate,
173 l_last_updated_by,
174 Sysdate,
175 l_created_by,
176 p_wms_task_type
177 );
178
179 IF (l_debug = 1) THEN
180 mydebug('After inserting into WMS_skip_task_exceptions');
181 mydebug('Before Deleting from wms_dispatched_task');
182 END IF;
183 l_progress := '50';
184
185 DELETE FROM wms_dispatched_tasks
186 WHERE transaction_temp_id = l_task_id AND
187 task_type = p_wms_task_type;
188 IF (l_debug = 1) THEN
189 mydebug('After Deleting from wms_dispatched_task');
190 END IF;
191 l_progress := '60';
192
193 x_return_status:=FND_API.g_ret_sts_success;
194
195 COMMIT;
196 IF (l_debug = 1) THEN
197 mydebug(x_return_status);
198 END IF;
199
200 EXCEPTION
201
202 WHEN FND_API.G_EXC_ERROR THEN
203 ROLLBACK TO sp_skip_task_adjustments;
204 x_return_status:=FND_API.G_RET_STS_ERROR;
205 fnd_msg_pub.count_and_get
206 ( p_count => x_msg_count
207 , p_data => x_msg_data
208 );
209
210 WHEN OTHERS THEN
211 ROLLBACK TO sp_skip_task_adjustments;
212 x_return_status:=FND_API.g_ret_sts_unexp_error;
213 fnd_msg_pub.count_and_get
214 ( p_count => x_msg_count,
215 p_data => x_msg_data
216 );
217
218 IF SQLCODE IS NOT NULL THEN
219 FND_MESSAGE.set_name('WMS', 'WMS_SKIP_TASK_ERROR');
220 fnd_message.set_token ('SQL_CODE',SQLCODE);
221 fnd_msg_pub.ADD;
222 END IF;
223
224
225 END skip_task_adjustments;
226
227
228
229
230 END wms_task_skipped;