[Home] [Help]
PACKAGE BODY: APPS.WIP_BATCH_MOVE
Source
1 PACKAGE BODY wip_batch_move AS
2 /* $Header: wipbmovb.pls 120.7.12010000.2 2008/09/09 22:48:02 ntangjee ship $*/
3
4 ---------------
5 --private types
6 ---------------
7 TYPE move_record_pvt IS RECORD(wip_entity_id NUMBER,
8 wip_entity_name VARCHAR2(240),
9 fm_operation_seq_num NUMBER,
10 fm_operation_code VARCHAR2(4),
11 fm_department_id NUMBER,
12 fm_department_code VARCHAR2(10),
13 fm_intraoperation_step_type NUMBER,
14 fm_intraoperation_step VARCHAR2(80),
15 to_operation_seq_num NUMBER,
16 to_operation_code VARCHAR2(4),
17 to_department_id NUMBER,
18 to_department_code VARCHAR2(10),
19 to_intraoperation_step_type NUMBER,
20 to_intraoperation_step VARCHAR2(80),
21 primary_item_id NUMBER,
22 primary_item_name VARCHAR2(40),
23 -- primary_item_category VARCHAR2(30),
24 transaction_quantity NUMBER,
25 transaction_uom VARCHAR2(3),
26 -- reason_id NUMBER,
27 -- reason_name VARCHAR2(30),
28 transaction_type NUMBER,
29 project_id NUMBER,
30 project_number VARCHAR2(25),
31 task_id NUMBER,
32 task_number VARCHAR2(25),
33 bom_revision VARCHAR2(3),
34 scrap_account_id NUMBER);
35
36 TYPE move_table_pvt IS TABLE OF move_record_pvt INDEX BY binary_integer;
37
38 TYPE error_record_pvt IS RECORD(job_name VARCHAR2(240),
39 op_seq_num NUMBER,
40 error_text VARCHAR2(1000));
41
42 TYPE error_table_pvt IS TABLE OF error_record_pvt INDEX BY binary_integer;
43
44 ---------------------
45 -- globale variables
46 ---------------------
47 error_lists error_table_pvt;
48 queue_meaning VARCHAR2(80);
49 run_meaning VARCHAR2(80);
50 tomove_meaning VARCHAR2(80);
51 reject_meaning VARCHAR2(80);
52 scrap_meaning VARCHAR2(80);
53 move_txn_meaning VARCHAR2(80);
54 ---------------------
55 -- functions
56 ---------------------
57 FUNCTION get_step_meaning(p_step_type NUMBER) RETURN VARCHAR2 IS
58
59 BEGIN
60 IF(p_step_type = wip_constants.queue)THEN
61 RETURN queue_meaning;
62 ELSIF(p_step_type = wip_constants.run)THEN
63 RETURN run_meaning;
64 ELSIF(p_step_type = wip_constants.tomove)THEN
65 RETURN tomove_meaning;
66 ELSIF(p_step_type = wip_constants.reject)THEN
67 RETURN reject_meaning;
68 ELSIF(p_step_type = wip_constants.scrap)THEN
69 RETURN scrap_meaning;
70 END IF;
71 END get_step_meaning;
72
73 ---------------------
74 -- procedures
75 ---------------------
76
77 -- error handling procedure
78 PROCEDURE add_error(p_job_name IN VARCHAR2,
79 p_op_seq_num IN NUMBER,
80 p_error_text IN VARCHAR2) IS
81
82 error_record error_record_pvt;
83
84 BEGIN
85 error_record.job_name := p_job_name;
86 error_record.op_seq_num := p_op_seq_num;
87 error_record.error_text := p_error_text;
88 error_lists(error_lists.count + 1) := error_record;
89 END add_error;
90
91 PROCEDURE derive_move(p_org_id IN NUMBER,
92 p_wip_entity_id IN NUMBER,
93 p_wip_entity_name IN VARCHAR2,
94 p_fm_op_seq IN NUMBER,
95 p_move_qty IN NUMBER,
96 p_default_step_type IN NUMBER,
97 p_fm_step_type IN NUMBER,
98 x_move_table_pvt IN OUT NOCOPY wip_batch_move.move_table_pvt,
99 x_return_status OUT NOCOPY VARCHAR2) IS
100
101 CURSOR c_move_info IS
102 SELECT p_wip_entity_id wip_entity_id,
103 p_wip_entity_name wip_entity_name,
104 p_fm_op_seq fm_op_seq,
105 bso1.operation_code fm_op_code,
106 wo1.department_id fm_dept_id,
107 bd1.department_code fm_dept_code,
108 p_fm_step_type fm_step_type,
109 wo2.operation_seq_num to_op_seq,
110 bso2.operation_code to_op_code,
111 wo2.department_id to_dept_id,
112 bd2.department_code to_dept_code,
113 p_default_step_type to_step_type,
114 wdj.primary_item_id item_id,
115 msik.concatenated_segments item_name,
116 p_move_qty txn_qty,
117 msik.primary_uom_code txn_uom,
118 WIP_CONSTANTS.MOVE_TXN txn_type,
119 wdj.project_id project_id,
120 pjm_project.all_proj_idtonum(wdj.project_id) project_number,
121 wdj.task_id task_id,
122 pjm_project.all_task_idtonum(wdj.task_id) task_number,
123 wdj.bom_revision bom_revision
124 FROM wip_discrete_jobs wdj,
125 wip_operations wo1,
126 wip_operations wo2,
127 mtl_system_items_kfv msik,
128 bom_standard_operations bso1,
129 bom_standard_operations bso2,
130 bom_departments bd1,
131 bom_departments bd2
132 WHERE wo1.wip_entity_id = wdj.wip_entity_id
133 AND wo1.organization_id = wdj.organization_id
134 AND wo1.operation_seq_num = p_fm_op_seq
135 AND wo1.standard_operation_id = bso1.standard_operation_id(+)
136 AND wo1.department_id = bd1.department_id
137 AND wo2.wip_entity_id = wdj.wip_entity_id
138 AND wo2.organization_id = wdj.organization_id
139 AND wo2.operation_seq_num =
140 (SELECT min(wo3.operation_seq_num)
141 FROM wip_operations wo3
142 WHERE wo3.wip_entity_id = p_wip_entity_id
143 AND wo3.organization_id = p_org_id
144 AND ((wo1.next_operation_seq_num IS NOT NULL AND
145 wo3.operation_seq_num > wo1.operation_seq_num) OR
146 (wo1.next_operation_seq_num IS NULL AND
147 wo3.operation_seq_num >= wo1.operation_seq_num))
148 AND wo3.count_point_type = WIP_CONSTANTS.YES_AUTO)
149 AND wo2.standard_operation_id = bso2.standard_operation_id(+)
150 AND wo2.department_id = bd2.department_id
151 AND wdj.primary_item_id = msik.inventory_item_id
152 AND wdj.organization_id = msik.organization_id
153 AND wdj.wip_entity_id = p_wip_entity_id
154 AND wdj.organization_id = p_org_id;
155
156 l_log_level NUMBER := fnd_log.g_current_runtime_level;
157 l_error_msg VARCHAR2(240);
158 l_process_phase VARCHAR2(3);
159 l_return_status VARCHAR(1);
160 l_move_info c_move_info%ROWTYPE;
161 l_move_record move_record_pvt;
162 l_params wip_logger.param_tbl_t;
163 BEGIN
164 l_process_phase := '1';
165 -- write parameter value to log file
166 IF (l_log_level <= wip_constants.trace_logging) THEN
167 l_params(1).paramName := 'p_org_id';
168 l_params(1).paramValue := p_org_id;
169 l_params(2).paramName := 'p_wip_entity_id';
170 l_params(2).paramValue := p_wip_entity_id;
171 l_params(3).paramName := 'p_wip_entity_name';
172 l_params(3).paramValue := p_wip_entity_name;
173 l_params(4).paramName := 'p_fm_op_seq';
174 l_params(4).paramValue := p_fm_op_seq;
175 l_params(5).paramName := 'p_move_qty';
176 l_params(5).paramValue := p_move_qty;
177 l_params(6).paramName := 'p_default_step_type';
178 l_params(6).paramValue := p_default_step_type;
179 l_params(7).paramName := 'p_fm_step_type';
180 l_params(7).paramValue := p_fm_step_type;
181
182 wip_logger.entryPoint(p_procName => 'wip_batch_move.derive_move',
183 p_params => l_params,
184 x_returnStatus => l_return_status);
185 END IF;
186
187 l_process_phase := '2';
188 -- Derive move information
189 FOR l_move_info IN c_move_info LOOP
190 -- There will be only 1 record found in the cursor.
191 l_move_record.wip_entity_id := l_move_info.wip_entity_id;
192 l_move_record.wip_entity_name := l_move_info.wip_entity_name;
193 l_move_record.fm_operation_seq_num := l_move_info.fm_op_seq;
194 l_move_record.fm_operation_code := l_move_info.fm_op_code;
195 l_move_record.fm_department_id := l_move_info.fm_dept_id;
196 l_move_record.fm_department_code := l_move_info.fm_dept_code;
197 l_move_record.fm_intraoperation_step_type := l_move_info.fm_step_type;
198 l_move_record.fm_intraoperation_step := get_step_meaning(l_move_info.fm_step_type);
199
200 IF(p_default_step_type > p_fm_step_type) THEN
201 -- If default step is greater than currect step, set to op to from op.
202 l_move_record.to_operation_seq_num := l_move_info.fm_op_seq;
203 ELSE
204 l_move_record.to_operation_seq_num := l_move_info.to_op_seq;
205 END IF;
206 l_move_record.to_operation_code := l_move_info.to_op_code;
207 l_move_record.to_department_id := l_move_info.to_dept_id;
208 l_move_record.to_department_code := l_move_info.to_dept_code;
209 IF(l_move_info.fm_op_seq = l_move_info.to_op_seq AND
210 p_default_step_type <= p_fm_step_type) THEN
211 -- If it is the last operation and default step is less than or equal to
212 -- current step, set to_step to 'To Move'
213 l_move_record.to_intraoperation_step_type := WIP_CONSTANTS.TOMOVE;
214 ELSE
215 l_move_record.to_intraoperation_step_type := l_move_info.to_step_type;
216 END IF;
217 l_move_record.to_intraoperation_step := get_step_meaning(l_move_info.to_step_type);
218 l_move_record.primary_item_id := l_move_info.item_id;
219 l_move_record.primary_item_name := l_move_info.item_name;
220 l_move_record.transaction_quantity := l_move_info.txn_qty;
221 l_move_record.transaction_uom := l_move_info.txn_uom;
222 l_move_record.transaction_type := l_move_info.txn_type;
223 l_move_record.project_id := l_move_info.project_id;
224 l_move_record.project_number := l_move_info.project_number;
225 l_move_record.task_id := l_move_info.task_id;
226 l_move_record.task_number := l_move_info.task_number;
227 l_move_record.bom_revision := l_move_info.bom_revision;
228 x_move_table_pvt(x_move_table_pvt.count + 1) := l_move_record;
229 END LOOP;
230
231 x_return_status := fnd_api.g_ret_sts_success;
232
233 -- write to the log file
234 IF (l_log_level <= wip_constants.trace_logging) THEN
235 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_move',
236 p_procReturnStatus => x_return_status,
237 p_msg => 'procedure complete',
238 x_returnStatus => l_return_status);
239 END IF;
240 EXCEPTION
241 WHEN others THEN
242 x_return_status := fnd_api.g_ret_sts_error;
243 IF(c_move_info%ISOPEN) THEN
244 CLOSE c_move_info;
245 END IF;
246 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
247 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
248
249 IF (l_log_level <= wip_constants.trace_logging) THEN
250 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_move',
251 p_procReturnStatus => x_return_status,
252 p_msg => l_error_msg,
253 x_returnStatus => l_return_status);
254 END IF;
255 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
256 fnd_message.set_token('MESSAGE', l_error_msg);
257 fnd_msg_pub.add;
258
259 END derive_move;
260
261 PROCEDURE derive_scrap(p_org_id IN NUMBER,
262 p_wip_entity_id IN NUMBER,
263 p_wip_entity_name IN VARCHAR2,
264 p_fm_op_seq IN NUMBER,
265 p_scrap_qty IN NUMBER,
266 p_require_scrap_acct IN NUMBER,
267 p_default_scrap_acct_id IN NUMBER,
268 p_default_step_type IN NUMBER,
269 p_fm_step_type IN NUMBER,
270 p_resp_key IN VARCHAR2,
271 x_move_table_pvt IN OUT NOCOPY wip_batch_move.move_table_pvt,
272 x_return_status OUT NOCOPY VARCHAR2) IS
273
274 CURSOR c_scrap_info IS
275 SELECT p_wip_entity_id wip_entity_id,
276 p_wip_entity_name wip_entity_name,
277 p_fm_op_seq fm_op_seq,
278 bso1.operation_code fm_op_code,
279 wo1.department_id fm_dept_id,
280 bd1.department_code fm_dept_code,
281 p_fm_step_type fm_step_type,
282 wo2.operation_seq_num to_op_seq,
283 bso2.operation_code to_op_code,
284 wo2.department_id to_dept_id,
285 bd2.department_code to_dept_code,
286 WIP_CONSTANTS.SCRAP to_step_type,
287 wdj.primary_item_id item_id,
288 msik.concatenated_segments item_name,
289 p_scrap_qty txn_qty,
290 msik.primary_uom_code txn_uom,
291 WIP_CONSTANTS.MOVE_TXN txn_type,
292 wdj.project_id project_id,
293 pjm_project.all_proj_idtonum(wdj.project_id) project_number,
294 wdj.task_id task_id,
295 pjm_project.all_task_idtonum(wdj.task_id) task_number,
296 wdj.bom_revision bom_revision,
297 p_default_scrap_acct_id scrap_acct_id
298 FROM wip_discrete_jobs wdj,
299 wip_operations wo1,
300 wip_operations wo2,
301 mtl_system_items_kfv msik,
302 bom_standard_operations bso1,
303 bom_standard_operations bso2,
304 bom_departments bd1,
305 bom_departments bd2
306 WHERE wo1.wip_entity_id = wdj.wip_entity_id
307 AND wo1.organization_id = wdj.organization_id
308 AND wo1.operation_seq_num = p_fm_op_seq
309 AND wo1.standard_operation_id = bso1.standard_operation_id(+)
310 AND wo1.department_id = bd1.department_id
311 AND wo2.wip_entity_id = wdj.wip_entity_id
312 AND wo2.organization_id = wdj.organization_id
313 AND wo2.operation_seq_num =
314 (SELECT min(wo3.operation_seq_num)
315 FROM wip_operations wo3
316 WHERE wo3.wip_entity_id = p_wip_entity_id
317 AND wo3.organization_id = p_org_id
318 AND ((wo1.next_operation_seq_num IS NOT NULL AND
319 wo3.operation_seq_num > wo1.operation_seq_num) OR
320 (wo1.next_operation_seq_num IS NULL AND
321 wo3.operation_seq_num >= wo1.operation_seq_num))
322 AND wo3.count_point_type = WIP_CONSTANTS.YES_AUTO)
323 AND wo2.standard_operation_id = bso2.standard_operation_id(+)
324 AND wo2.department_id = bd2.department_id
325 AND wdj.primary_item_id = msik.inventory_item_id
326 AND wdj.organization_id = msik.organization_id
327 AND wdj.wip_entity_id = p_wip_entity_id
328 AND wdj.organization_id = p_org_id;
329
330 l_level_id NUMBER;
331 l_log_level NUMBER := fnd_log.g_current_runtime_level;
332 l_default_scrap_acct_id VARCHAR2(30);
333 l_error_msg VARCHAR2(240);
334 l_process_phase VARCHAR2(3);
335 l_return_status VARCHAR2(1);
336 l_scrap_info c_scrap_info%ROWTYPE;
337 l_scrap_record move_record_pvt;
338 l_params wip_logger.param_tbl_t;
339 BEGIN
340 l_process_phase := '1';
341 -- write parameter value to log file
342 IF (l_log_level <= wip_constants.trace_logging) THEN
343 l_params(1).paramName := 'p_org_id';
344 l_params(1).paramValue := p_org_id;
345 l_params(2).paramName := 'p_wip_entity_id';
346 l_params(2).paramValue := p_wip_entity_id;
347 l_params(3).paramName := 'p_wip_entity_name';
348 l_params(3).paramValue := p_wip_entity_name;
349 l_params(4).paramName := 'p_fm_op_seq';
350 l_params(4).paramValue := p_fm_op_seq;
351 l_params(5).paramName := 'p_scrap_qty';
352 l_params(5).paramValue := p_scrap_qty;
353 l_params(6).paramName := 'p_require_scrap_acct';
354 l_params(6).paramValue := p_require_scrap_acct;
355 l_params(7).paramName := 'p_default_scrap_acct_id';
356 l_params(7).paramValue := p_default_scrap_acct_id;
357 l_params(8).paramName := 'p_default_step_type';
358 l_params(8).paramValue := p_default_step_type;
359 l_params(9).paramName := 'p_fm_step_type';
360 l_params(9).paramValue := p_fm_step_type;
361 l_params(10).paramName := 'p_resp_key';
362 l_params(10).paramValue := p_resp_key;
363
364 wip_logger.entryPoint(p_procName => 'wip_batch_move.derive_scrap',
365 p_params => l_params,
366 x_returnStatus => l_return_status);
367 END IF;
368
369 l_process_phase := '2';
370 -- Derive move information
371 FOR l_scrap_info IN c_scrap_info LOOP
372 -- There will be only 1 record found in the cursor.
373 l_scrap_record.wip_entity_id := l_scrap_info.wip_entity_id;
374 l_scrap_record.wip_entity_name := l_scrap_info.wip_entity_name;
375 l_scrap_record.fm_operation_seq_num := l_scrap_info.fm_op_seq;
376 l_scrap_record.fm_operation_code := l_scrap_info.fm_op_code;
377 l_scrap_record.fm_department_id := l_scrap_info.fm_dept_id;
378 l_scrap_record.fm_department_code := l_scrap_info.fm_dept_code;
379 l_scrap_record.fm_intraoperation_step_type := l_scrap_info.fm_step_type;
380 l_scrap_record.fm_intraoperation_step := get_step_meaning(l_scrap_info.fm_step_type);
381 IF(p_fm_step_type = WIP_CONSTANTS.TOMOVE AND
382 p_default_step_type = WIP_CONSTANTS.TOMOVE) THEN
383 -- If user move from 10TM to 20TM, we will scrap at operation 20.
384 l_scrap_record.to_operation_seq_num := l_scrap_info.to_op_seq;
385 l_scrap_record.to_operation_code := l_scrap_info.to_op_code;
386 l_scrap_record.to_department_id := l_scrap_info.to_dept_id;
387 l_scrap_record.to_department_code := l_scrap_info.to_dept_code;
388 IF(p_require_scrap_acct = WIP_CONSTANTS.YES) THEN
389 l_process_phase := '3';
390 l_level_id := wip_ws_util.get_preference_level_id(
391 p_pref_id => 10, -- Default Scrap Account
392 p_resp_key => p_resp_key,
393 p_org_id => p_org_id,
394 p_dept_id => l_scrap_info.to_dept_id);
395 l_process_phase := '4';
396 l_default_scrap_acct_id := wip_ws_util.get_preference_value_code(
397 p_pref_id => 10, -- Default Scrap Account
398 p_level_id => l_level_id);
399 l_process_phase := '5';
400 IF(l_default_scrap_acct_id IS NULL) THEN
401 fnd_message.set_name('WIP','WIP_NO_SCRAP_ACCT_NO_BATCH');
402 fnd_msg_pub.add;
403 l_error_msg := 'No default scrap accout defined.';
404 raise fnd_api.g_exc_unexpected_error;
405 ELSE
406 l_scrap_record.scrap_account_id := to_number(l_default_scrap_acct_id);
407 END IF;-- default scrap is null
408 END IF;
409 ELSE
410 -- Most of the time we can scrap at from operation.
411 l_scrap_record.to_operation_seq_num := l_scrap_info.fm_op_seq;
412 l_scrap_record.to_operation_code := l_scrap_info.fm_op_code;
413 l_scrap_record.to_department_id := l_scrap_info.fm_dept_id;
414 l_scrap_record.to_department_code := l_scrap_info.fm_dept_code;
415 IF(p_require_scrap_acct = WIP_CONSTANTS.YES) THEN
416 l_scrap_record.scrap_account_id := l_scrap_info.scrap_acct_id;
417 END IF;
418 END IF;
419 l_scrap_record.to_intraoperation_step_type := l_scrap_info.to_step_type;
420 l_scrap_record.to_intraoperation_step := get_step_meaning(l_scrap_info.to_step_type);
421 l_scrap_record.primary_item_id := l_scrap_info.item_id;
422 l_scrap_record.primary_item_name := l_scrap_info.item_name;
423 l_scrap_record.transaction_quantity := l_scrap_info.txn_qty;
424 l_scrap_record.transaction_uom := l_scrap_info.txn_uom;
425 l_scrap_record.transaction_type := l_scrap_info.txn_type;
426 l_scrap_record.project_id := l_scrap_info.project_id;
427 l_scrap_record.project_number := l_scrap_info.project_number;
428 l_scrap_record.task_id := l_scrap_info.task_id;
429 l_scrap_record.task_number := l_scrap_info.task_number;
430 l_scrap_record.bom_revision := l_scrap_info.bom_revision;
431 x_move_table_pvt(x_move_table_pvt.count + 1) := l_scrap_record;
432 END LOOP;
433
434 x_return_status := fnd_api.g_ret_sts_success;
435
436 -- write to the log file
437 IF (l_log_level <= wip_constants.trace_logging) THEN
438 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_scrap',
439 p_procReturnStatus => x_return_status,
440 p_msg => 'procedure complete',
441 x_returnStatus => l_return_status);
442 END IF;
443 EXCEPTION
444 WHEN fnd_api.g_exc_unexpected_error THEN
445 x_return_status := fnd_api.g_ret_sts_error;
446 IF(c_scrap_info%ISOPEN) THEN
447 CLOSE c_scrap_info;
448 END IF;
449 IF (l_log_level <= wip_constants.trace_logging) THEN
450 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_scrap',
451 p_procReturnStatus => x_return_status,
452 p_msg => l_error_msg,
453 x_returnStatus => l_return_status);
454 END IF;
455 WHEN others THEN
456 x_return_status := fnd_api.g_ret_sts_error;
457 IF(c_scrap_info%ISOPEN) THEN
458 CLOSE c_scrap_info;
459 END IF;
460 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
461 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
462
463 IF (l_log_level <= wip_constants.trace_logging) THEN
464 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_scrap',
465 p_procReturnStatus => x_return_status,
466 p_msg => l_error_msg,
467 x_returnStatus => l_return_status);
468 END IF;
469 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
470 fnd_message.set_token('MESSAGE', l_error_msg);
471 fnd_msg_pub.add;
472
473 END derive_scrap;
474
475 PROCEDURE derive_row(p_org_id IN NUMBER,
476 p_wip_entity_id IN NUMBER,
477 p_wip_entity_name IN VARCHAR2,
478 p_fm_op_seq IN NUMBER,
479 p_move_qty IN NUMBER,
480 p_scrap_qty IN NUMBER,
481 p_require_scrap_acct IN NUMBER,
482 p_default_scrap_acct_id IN NUMBER,
483 p_default_step_type IN NUMBER,
484 p_fm_step_type IN NUMBER,
485 p_resp_key IN VARCHAR2,
486 x_move_table_pvt OUT NOCOPY wip_batch_move.move_table_pvt,
487 x_return_status OUT NOCOPY VARCHAR2) IS
488
489 l_log_level NUMBER := fnd_log.g_current_runtime_level;
490 l_error_msg VARCHAR2(240);
491 l_process_phase VARCHAR2(3);
492 l_return_status VARCHAR2(1);
493 l_params wip_logger.param_tbl_t;
494 BEGIN
495 l_process_phase := '1';
496 -- write parameter value to log file
497 IF (l_log_level <= wip_constants.trace_logging) THEN
498 l_params(1).paramName := 'p_org_id';
499 l_params(1).paramValue := p_org_id;
500 l_params(2).paramName := 'p_wip_entity_id';
501 l_params(2).paramValue := p_wip_entity_id;
502 l_params(3).paramName := 'p_wip_entity_name';
503 l_params(3).paramValue := p_wip_entity_name;
504 l_params(4).paramName := 'p_fm_op_seq';
505 l_params(4).paramValue := p_fm_op_seq;
506 l_params(5).paramName := 'p_move_qty';
507 l_params(5).paramValue := p_move_qty;
508 l_params(6).paramName := 'p_scrap_qty';
509 l_params(6).paramValue := p_scrap_qty;
510 l_params(7).paramName := 'p_require_scrap_acct';
511 l_params(7).paramValue := p_require_scrap_acct;
512 l_params(8).paramName := 'p_default_scrap_acct_id';
513 l_params(8).paramValue := p_default_scrap_acct_id;
514 l_params(9).paramName := 'p_default_step_type';
515 l_params(9).paramValue := p_default_step_type;
516 l_params(10).paramName := 'p_fm_step_type';
517 l_params(10).paramValue := p_fm_step_type;
518 l_params(11).paramName := 'p_resp_key';
519 l_params(11).paramValue := p_resp_key;
520
521 wip_logger.entryPoint(p_procName => 'wip_batch_move.derive_row',
522 p_params => l_params,
523 x_returnStatus => l_return_status);
524 END IF;
525 l_process_phase := '2';
526 IF(p_move_qty > 0) THEN
527 -- Derive move transaction information.
528 derive_move(p_org_id => p_org_id,
529 p_wip_entity_id => p_wip_entity_id,
530 p_wip_entity_name => p_wip_entity_name,
531 p_fm_op_seq => p_fm_op_seq,
532 p_move_qty => p_move_qty,
533 p_default_step_type => p_default_step_type,
534 p_fm_step_type => p_fm_step_type,
535 x_move_table_pvt => x_move_table_pvt,
536 x_return_status => x_return_status);
537
538 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
539 l_error_msg := 'wip_batch_move.derive_move failed';
540 raise fnd_api.g_exc_unexpected_error;
541 END IF;
542 END IF;
543 l_process_phase := '3';
544 IF(p_scrap_qty > 0) THEN
545 -- Derive move transaction information.
546 derive_scrap(p_org_id => p_org_id,
547 p_wip_entity_id => p_wip_entity_id,
548 p_wip_entity_name => p_wip_entity_name,
549 p_fm_op_seq => p_fm_op_seq,
550 p_scrap_qty => p_scrap_qty,
551 p_require_scrap_acct => p_require_scrap_acct,
552 p_default_scrap_acct_id => p_default_scrap_acct_id,
553 p_default_step_type => p_default_step_type,
554 p_fm_step_type => p_fm_step_type,
555 p_resp_key => p_resp_key,
556 x_move_table_pvt => x_move_table_pvt,
557 x_return_status => x_return_status);
558
559 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
560 l_error_msg := 'wip_batch_move.derive_scrap failed';
561 raise fnd_api.g_exc_unexpected_error;
562 END IF;
563 END IF;
564
565 x_return_status := fnd_api.g_ret_sts_success;
566 -- write to the log file
567 IF (l_log_level <= wip_constants.trace_logging) THEN
568 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_row',
569 p_procReturnStatus => x_return_status,
570 p_msg => 'procedure complete',
571 x_returnStatus => l_return_status);
572 END IF;
573
574 EXCEPTION
575 WHEN fnd_api.g_exc_unexpected_error THEN
576 x_return_status := fnd_api.g_ret_sts_error;
577 IF (l_log_level <= wip_constants.trace_logging) THEN
578 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_row',
579 p_procReturnStatus => x_return_status,
580 p_msg => l_error_msg,
581 x_returnStatus => l_return_status);
582 END IF;
583
584 WHEN others THEN
585 x_return_status := fnd_api.g_ret_sts_error;
586 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
587 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
588
589 IF (l_log_level <= wip_constants.trace_logging) THEN
590 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_row',
591 p_procReturnStatus => x_return_status,
592 p_msg => l_error_msg,
593 x_returnStatus => l_return_status);
594 END IF;
595 END derive_row;
596
597 PROCEDURE get_preferences(p_resp_key IN VARCHAR2,
598 p_org_id IN NUMBER,
599 p_dept_id IN NUMBER,
600 x_default_step_type OUT NOCOPY NUMBER,
601 x_default_scrap_acct_id OUT NOCOPY NUMBER,
602 x_return_status OUT NOCOPY VARCHAR2) IS
603
604 l_level_id NUMBER;
605 l_log_level NUMBER := fnd_log.g_current_runtime_level;
606 l_default_scrap_acct_id VARCHAR2(30);
607 l_default_step_type VARCHAR2(1);
608 l_error_msg VARCHAR2(240);
609 l_process_phase VARCHAR2(3);
610 l_return_status VARCHAR2(1);
611 l_params wip_logger.param_tbl_t;
612 BEGIN
613 l_process_phase := '1';
614 -- write parameter value to log file
615 IF (l_log_level <= wip_constants.trace_logging) THEN
616 l_params(1).paramName := 'p_resp_key';
617 l_params(1).paramValue := p_resp_key;
618 l_params(2).paramName := 'p_org_id';
619 l_params(2).paramValue := p_org_id;
620 l_params(3).paramName := 'p_dept_id';
621 l_params(3).paramValue := p_dept_id;
622
623 wip_logger.entryPoint(p_procName => 'wip_batch_move.get_preferences',
624 p_params => l_params,
625 x_returnStatus => l_return_status);
626 END IF;
627 l_process_phase := '2';
628 l_level_id := wip_ws_util.get_preference_level_id(
629 p_pref_id => 9, -- Default Intra Op Step
630 p_resp_key => p_resp_key,
631 p_org_id => p_org_id,
632 p_dept_id => p_dept_id);
633 l_process_phase := '3';
634 l_default_step_type := wip_ws_util.get_preference_value_code(
635 p_pref_id => 9, -- Default Intra Op Step
636 p_level_id => l_level_id);
637 l_process_phase := '4';
638 x_default_step_type := to_number(l_default_step_type);
639 l_process_phase := '5';
640 l_level_id := wip_ws_util.get_preference_level_id(
641 p_pref_id => 10, -- Default Scrap Account
642 p_resp_key => p_resp_key,
643 p_org_id => p_org_id,
644 p_dept_id => p_dept_id);
645 l_process_phase := '6';
646 l_default_scrap_acct_id := wip_ws_util.get_preference_value_code(
647 p_pref_id => 10, -- Default Scrap Account
648 p_level_id => l_level_id);
649 l_process_phase := '7';
650 x_default_scrap_acct_id := to_number(l_default_scrap_acct_id);
651
652 x_return_status := fnd_api.g_ret_sts_success;
653
654 -- write to the log file
655 IF (l_log_level <= wip_constants.trace_logging) THEN
656 wip_logger.exitPoint(p_procName => 'wip_batch_move.get_preferences',
657 p_procReturnStatus => x_return_status,
658 p_msg => 'procedure complete',
659 x_returnStatus => l_return_status);
660 END IF;
661 EXCEPTION
662 WHEN others THEN
663 x_return_status := fnd_api.g_ret_sts_error;
664 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
665 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
666
667 IF (l_log_level <= wip_constants.trace_logging) THEN
668 wip_logger.exitPoint(p_procName => 'wip_batch_move.get_preferences',
669 p_procReturnStatus => x_return_status,
670 p_msg => l_error_msg,
671 x_returnStatus => l_return_status);
672 END IF;
673 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
674 fnd_message.set_token('MESSAGE', l_error_msg);
675 fnd_msg_pub.add;
676
677 END get_preferences;
678
679 PROCEDURE initialize_lookups IS
680
681 CURSOR c_step_meaning IS
682 SELECT lookup_code step,
683 meaning
684 FROM mfg_lookups
685 WHERE lookup_type='WIP_INTRAOPERATION_STEP';
686
687 l_step_meaning c_step_meaning%ROWTYPE;
688
689 BEGIN
690 -- Put step lookup meaning in to global variables
691 FOR l_step_meaning IN c_step_meaning LOOP
692 IF(l_step_meaning.step = wip_constants.queue)THEN
693 queue_meaning := l_step_meaning.meaning;
694 ELSIF(l_step_meaning.step = wip_constants.run)THEN
695 run_meaning := l_step_meaning.meaning;
696 ELSIF(l_step_meaning.step = wip_constants.tomove)THEN
697 tomove_meaning := l_step_meaning.meaning;
698 ELSIF(l_step_meaning.step = wip_constants.reject)THEN
699 reject_meaning := l_step_meaning.meaning;
700 ELSIF(l_step_meaning.step = wip_constants.scrap)THEN
701 scrap_meaning := l_step_meaning.meaning;
702 END IF;
703 END LOOP;
704 -- Get meaning of move transaction type
705 SELECT meaning
706 INTO move_txn_meaning
707 FROM mfg_lookups
708 WHERE lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
709 AND lookup_code = wip_constants.move_txn;
710 EXCEPTION
711 WHEN others THEN
712 IF(c_step_meaning%ISOPEN) THEN
713 CLOSE c_step_meaning;
714 END IF;
715 END initialize_lookups;
716
717 PROCEDURE insert_move_records(p_org_id IN NUMBER,
718 p_employee_id IN NUMBER,
719 p_move_table_pvt IN wip_batch_move.move_table_pvt,
720 p_assy_serial IN VARCHAR2,
721 x_group_id OUT NOCOPY NUMBER,
722 x_return_status OUT NOCOPY VARCHAR2) IS
723
724 l_log_level NUMBER := fnd_log.g_current_runtime_level;
725 l_total_row NUMBER;
726 l_error_msg VARCHAR2(240);
727 l_process_phase VARCHAR2(3);
728 l_return_status VARCHAR2(1);
729 l_params wip_logger.param_tbl_t;
730 l_txn_id NUMBER;
731 BEGIN
732 l_process_phase := '1';
733 -- write parameter value to log file
734 IF (l_log_level <= wip_constants.trace_logging) THEN
735 l_params(1).paramName := 'p_org_id';
736 l_params(1).paramValue := p_org_id;
737 l_params(2).paramName := 'p_employee_id';
738 l_params(2).paramValue := p_employee_id;
739 l_params(3).paramName := 'p_assy_serial';
740 l_params(3).paramValue := p_assy_serial;
741
742 wip_logger.entryPoint(p_procName => 'wip_batch_move.insert_move_records',
743 p_params => l_params,
744 x_returnStatus => l_return_status);
745 END IF;
746 l_process_phase := '2';
747
748 SELECT wip_transactions_s.nextval
749 INTO x_group_id
750 FROM dual;
751
752 l_total_row := p_move_table_pvt.count;
753 FOR i IN 1..l_total_row LOOP
754 INSERT INTO wip_move_txn_interface(
755 group_id,
756 transaction_id,
757 last_update_date,
758 last_updated_by,
759 last_updated_by_name,
760 creation_date,
761 created_by,
762 created_by_name,
763 last_update_login,
764 request_id,
765 program_application_id,
766 program_id,
767 program_update_date,
768 kanban_card_id,
769 source_code,
770 source_line_id,
771 process_phase,
772 process_status,
773 transaction_type,
774 organization_id,
775 organization_code,
776 wip_entity_id,
777 wip_entity_name,
778 entity_type,
779 primary_item_id,
780 line_id,
781 line_code,
782 repetitive_schedule_id,
783 transaction_date,
784 acct_period_id,
785 fm_operation_seq_num,
786 fm_operation_code,
787 fm_department_id,
788 fm_department_code,
789 fm_intraoperation_step_type,
790 to_operation_seq_num,
791 to_operation_code,
792 to_department_id,
793 to_department_code,
794 to_intraoperation_step_type,
795 transaction_quantity,
796 transaction_uom,
797 primary_quantity,
798 primary_uom,
799 scrap_account_id,
800 reason_id,
801 reason_name,
802 reference,
803 qa_collection_id,
804 overcompletion_transaction_qty,
805 overcompletion_primary_qty,
806 overcompletion_transaction_id,
807 employee_id)
808 VALUES(
809 x_group_id, -- group_id
810 wip_transactions_s.nextval, -- transaction_id
811 SYSDATE, -- last_update_date
812 fnd_global.user_id, -- last_updated_by
813 NULL, -- last_updated_by_name
814 SYSDATE, -- creation_date
815 fnd_global.user_id, -- created_by
816 NULL, -- created_by_name
817 fnd_global.conc_login_id, -- last_update_login
818 NULL, -- request_id
819 NULL, -- program_application_id
820 NULL, -- program_id
821 NULL, -- program_update_date
822 NULL, -- kanban_card_id
823 NULL, -- source_code
824 NULL, -- source_line_id
825 WIP_CONSTANTS.MOVE_VAL, -- process_phase
826 WIP_CONSTANTS.RUNNING, -- process_status
827 WIP_CONSTANTS.MOVE_TXN, -- transaction_type
828 p_org_id, -- organization_id
829 NULL, -- organization_code
830 p_move_table_pvt(i).wip_entity_id,
831 p_move_table_pvt(i).wip_entity_name,
832 WIP_CONSTANTS.DISCRETE, -- entity_type
833 p_move_table_pvt(i).primary_item_id,
834 NULL, -- line_id
835 NULL, -- line_code
836 NULL, -- repetitive_schedule_id
837 SYSDATE, -- transaction_date
838 NULL, -- acct_period_id
839 p_move_table_pvt(i).fm_operation_seq_num,
840 p_move_table_pvt(i).fm_operation_code,
841 p_move_table_pvt(i).fm_department_id,
842 p_move_table_pvt(i).fm_department_code,
843 p_move_table_pvt(i).fm_intraoperation_step_type,
844 p_move_table_pvt(i).to_operation_seq_num,
845 p_move_table_pvt(i).to_operation_code,
846 p_move_table_pvt(i).to_department_id,
847 p_move_table_pvt(i).to_department_code,
848 p_move_table_pvt(i).to_intraoperation_step_type,
849 p_move_table_pvt(i).transaction_quantity,
850 p_move_table_pvt(i).transaction_uom,
851 NULL, -- primary_quantity
852 NULL, -- primaty_uom
853 p_move_table_pvt(i).scrap_account_id,
854 NULL, -- reason_id
855 NULL, -- reason_name
856 NULL, -- reference
857 NULL, -- qa_collection_id
858 NULL, -- overcompletion_transaction_qty
859 NULL, -- overcompletion_primary_qty
860 NULL, -- overcompletion_transaction_id
861 p_employee_id)
862 RETURNING transaction_id INTO l_txn_id;
863 END LOOP;
864
865 l_process_phase := '3';
866
867 l_process_phase := '4';
868 IF(p_assy_serial IS NOT NULL) THEN
869 -- If serialized express move from search serial, quantity is always 1.
870 -- Need to insert record into WIP_SERIAL_MOVE_INTERFACE
871 IF(wma_move.insertSerial(groupID => x_group_id,
872 transactionID => l_txn_id,
873 serialNumber => p_assy_serial,
874 errMessage => l_error_msg) = FALSE) THEN
875 -- insert statement error out
876 RAISE fnd_api.g_exc_unexpected_error;
877 END IF;
878 ELSE
879 -- If serialized express move from dispatch list or search job, quantity
880 -- can be more than one. Need to insert serial records into wsmi.
881 INSERT INTO wip_serial_move_interface
882 (transaction_id,
883 assembly_serial_number,
884 creation_date,
885 created_by,
886 created_by_name,
887 last_update_date,
888 last_updated_by,
889 last_updated_by_name,
890 last_update_login,
891 request_id,
892 program_application_id,
893 program_id,
894 program_update_date
895 )
896 SELECT wmti.transaction_id,
897 msn.serial_number,
898 wmti.creation_date,
899 wmti.created_by,
900 wmti.created_by_name,
901 wmti.last_update_date,
902 wmti.last_updated_by,
903 wmti.last_updated_by_name,
904 wmti.last_update_login,
905 wmti.request_id,
906 wmti.program_application_id,
907 wmti.program_id,
908 wmti.program_update_date
909 FROM wip_move_txn_interface wmti,
910 mtl_serial_numbers msn,
911 wip_discrete_jobs wdj
912 WHERE wmti.transaction_id = l_txn_id
913 AND wmti.group_id = x_group_id
914 AND wmti.organization_id = wdj.organization_id
915 AND wmti.wip_entity_id = wdj.wip_entity_id
916 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
917 AND msn.wip_entity_id = wmti.wip_entity_id
918 AND (msn.operation_seq_num IS NULL OR
919 msn.operation_seq_num = wmti.fm_operation_seq_num)
920 AND (msn.intraoperation_step_type IS NULL OR
921 msn.intraoperation_step_type=
922 wmti.fm_intraoperation_step_type)
923 AND rownum <= wmti.transaction_quantity
924 ORDER BY msn.serial_number;
925
926 END IF;
927
928 x_return_status := fnd_api.g_ret_sts_success;
929 -- write to the log file
930 IF (l_log_level <= wip_constants.trace_logging) THEN
931 wip_logger.exitPoint(p_procName => 'wip_batch_move.insert_move_records',
932 p_procReturnStatus => x_return_status,
933 p_msg => 'procedure complete',
934 x_returnStatus => l_return_status);
935 END IF;
936
937 EXCEPTION
938 WHEN others THEN
939 x_return_status := fnd_api.g_ret_sts_error;
940 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
941 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
942
943 IF (l_log_level <= wip_constants.trace_logging) THEN
944 wip_logger.exitPoint(p_procName => 'wip_batch_move.insert_move_records',
945 p_procReturnStatus => x_return_status,
946 p_msg => l_error_msg,
947 x_returnStatus => l_return_status);
948 END IF;
949 END insert_move_records;
950
951 Procedure load_errors IS
952
953 total_errors NUMBER;
954 error_no NUMBER := 1;
955
956 BEGIN
957
958 total_errors := error_lists.count;
959 WHILE (error_no <= total_errors) LOOP
960 fnd_message.set_name('WIP', 'WIP_BATCH_MOVE_ERROR');
961 fnd_message.set_token('JOB', error_lists(error_no).job_name);
962 fnd_message.set_token('OPERATION', error_lists(error_no).op_seq_num);
963 fnd_message.set_token('ERROR',error_lists(error_no).error_text);
964 fnd_msg_pub.add;
965 error_no := error_no + 1;
966 END LOOP;
967
968 -- cleare error table
969 error_lists.delete ;
970
971 END load_errors;
972
973 PROCEDURE process_move_records(p_group_id IN NUMBER,
974 x_return_status OUT NOCOPY VARCHAR2) IS
975
976 CURSOR c_errors IS
977 SELECT wtie.error_column,
978 wtie.error_message
979 FROM wip_txn_interface_errors wtie,
980 wip_move_txn_interface wmti
981 WHERE wtie.transaction_id = wmti.transaction_id
982 AND wmti.group_id = p_group_id;
983
984 CURSOR c_move_intf_records IS
985 SELECT wmti.wip_entity_id wip_id,
986 wmti.fm_operation_seq_num fm_op,
987 wmti.to_operation_seq_num to_op
988 FROM wip_move_txn_interface wmti
989 WHERE wmti.group_id = p_group_id
990 ORDER BY wmti.transaction_id;
991
992 l_log_level NUMBER := fnd_log.g_current_runtime_level;
993 l_error_msg VARCHAR2(1000);
994 l_error_text VARCHAR2(2000);
995 l_process_phase VARCHAR2(3);
996 l_return_status VARCHAR2(1);
997 l_errors c_errors%ROWTYPE;
998 l_move_intf_records c_move_intf_records%ROWTYPE;
999 l_params wip_logger.param_tbl_t;
1000
1001 BEGIN
1002 l_process_phase := '1';
1003 -- write parameter value to log file
1004 IF (l_log_level <= wip_constants.trace_logging) THEN
1005 l_params(1).paramName := 'p_group_id';
1006 l_params(1).paramValue := p_group_id;
1007
1008 wip_logger.entryPoint(p_procName => 'wip_batch_move.process_move_records',
1009 p_params => l_params,
1010 x_returnStatus => l_return_status);
1011 END IF;
1012 l_process_phase := '2';
1013
1014 OPEN c_move_intf_records;
1015 FETCH c_move_intf_records INTO l_move_intf_records;
1016 CLOSE c_move_intf_records;
1017 l_process_phase := '3';
1018
1019 wip_movProc_priv.processIntf(p_group_id => p_group_id,
1020 p_child_txn_id => -1,
1021 p_mtl_header_id => -1,
1022 p_proc_phase => WIP_CONSTANTS.MOVE_VAL,
1023 p_time_out => 0,
1024 p_move_mode => WIP_CONSTANTS.BACKGROUND,
1025 p_bf_mode => WIP_CONSTANTS.ONLINE,
1026 p_mtl_mode => WIP_CONSTANTS.ONLINE,
1027 p_endDebug => fnd_api.g_false,
1028 p_initMsgList => fnd_api.g_true,
1029 p_insertAssy => fnd_api.g_true,
1030 p_do_backflush => fnd_api.g_true,
1031 x_returnStatus => x_return_status);
1032
1033 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1034 l_process_phase := '4';
1035 l_error_msg := 'wip_movProc_priv.processIntf failed';
1036 raise fnd_api.g_exc_unexpected_error;
1037 ELSE
1038 l_process_phase := '5';
1039 -- If move success, call time entry API to clock off operator if there
1040 -- is no quantity left at the operation.
1041 wip_ws_time_entry.process_time_records_move(
1042 p_wip_entity_id => l_move_intf_records.wip_id,
1043 p_from_op => l_move_intf_records.fm_op,
1044 p_to_op => l_move_intf_records.to_op);
1045 l_process_phase := '6';
1046 END IF;
1047
1048 -- write to the log file
1049 IF (l_log_level <= wip_constants.trace_logging) THEN
1050 wip_logger.exitPoint(p_procName => 'wip_batch_move.process_move_records',
1051 p_procReturnStatus => x_return_status,
1052 p_msg => 'procedure complete',
1053 x_returnStatus => l_return_status);
1054 END IF;
1055
1056 EXCEPTION
1057 WHEN fnd_api.g_exc_unexpected_error THEN
1058 x_return_status := fnd_api.g_ret_sts_error;
1059 IF(c_errors%ISOPEN) THEN
1060 CLOSE c_errors;
1061 END IF;
1062 IF(c_move_intf_records%ISOPEN) THEN
1063 CLOSE c_move_intf_records;
1064 END IF;
1065 FOR l_errors IN c_errors LOOP
1066 l_error_text := l_error_text || l_errors.error_column ||':' ||
1067 l_errors.error_message || '; ';
1068 END LOOP;
1069
1070 IF (l_log_level <= wip_constants.trace_logging) THEN
1071 wip_logger.exitPoint(p_procName => 'wip_batch_move.process_move_records',
1072 p_procReturnStatus => x_return_status,
1073 p_msg => 'wip_movProc_grp.processInterface failed',
1074 x_returnStatus => l_return_status);
1075 END IF;
1076 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1077 fnd_message.set_token('MESSAGE', l_error_text);
1078 fnd_msg_pub.add;
1079 WHEN others THEN
1080 x_return_status := fnd_api.g_ret_sts_error;
1081 IF(c_errors%ISOPEN) THEN
1082 CLOSE c_errors;
1083 END IF;
1084 IF(c_move_intf_records%ISOPEN) THEN
1085 CLOSE c_move_intf_records;
1086 END IF;
1087 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
1088 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1089
1090 IF (l_log_level <= wip_constants.trace_logging) THEN
1091 wip_logger.exitPoint(p_procName => 'wip_batch_move.process_move_records',
1092 p_procReturnStatus => x_return_status,
1093 p_msg => l_error_msg,
1094 x_returnStatus => l_return_status);
1095 END IF;
1096 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1097 fnd_message.set_token('MESSAGE', l_error_msg);
1098 fnd_msg_pub.add;
1099 END process_move_records;
1100
1101 PROCEDURE quality_require(p_org_id IN NUMBER,
1102 p_move_tbl IN wip_batch_move.move_table_pvt,
1103 x_quality_require OUT NOCOPY VARCHAR2,
1104 x_plan_names OUT NOCOPY VARCHAR2,
1105 x_return_status OUT NOCOPY VARCHAR2)IS
1106
1107 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1108 l_total_row NUMBER;
1109 l_commit_allow VARCHAR2(1);
1110 l_context_values VARCHAR2(10000);
1111 l_error_msg VARCHAR2(240);
1112 l_plan_txn_ids VARCHAR2(10000);
1113 l_plan_ids VARCHAR2(10000);
1114 l_quality_plan_exist VARCHAR2(1);
1115 l_return_status VARCHAR2(1);
1116 l_params wip_logger.param_tbl_t;
1117 BEGIN
1118 -- write parameter value to log file
1119 IF (l_log_level <= wip_constants.trace_logging) THEN
1120 l_params(1).paramName := 'p_org_id';
1121 l_params(1).paramValue := p_org_id;
1122
1123 wip_logger.entryPoint(p_procName => 'wip_batch_move.quality_require',
1124 p_params => l_params,
1125 x_returnStatus => l_return_status);
1126 END IF;
1127 x_quality_require := fnd_api.g_false;
1128 l_total_row := p_move_tbl.count;
1129
1130 FOR i IN 1..l_total_row LOOP
1131 -- Build l_context_values to pass to quality API.
1132 l_context_values :=
1133 qa_ss_const.department||'='||p_move_tbl(i).fm_department_code||'@'||
1134 qa_ss_const.quantity||'='||p_move_tbl(i).transaction_quantity||'@'||
1135 qa_ss_const.item||'='||p_move_tbl(i).primary_item_name||'@'||
1136 -- Pass empty string to item category as Bryan suggested.
1137 qa_ss_const.item_category||'=@'||
1138 qa_ss_const.uom||'='||p_move_tbl(i).transaction_uom||'@'||
1139 qa_ss_const.reason_code||'=@'||
1140 qa_ss_const.job_name||'='||p_move_tbl(i).wip_entity_name||'@'||
1141 qa_ss_const.production_line||'=@'||
1142 qa_ss_const.to_op_seq_num||'='||p_move_tbl(i).to_operation_seq_num||'@'||
1143 qa_ss_const.from_op_seq_num||'='||p_move_tbl(i).fm_operation_seq_num||'@'||
1144 qa_ss_const.to_intraoperation_step||'='||p_move_tbl(i).to_intraoperation_step||'@'||
1145 qa_ss_const.from_intraoperation_step||'='||p_move_tbl(i).fm_intraoperation_step||'@'||
1146 qa_ss_const.sales_order||'=@'||
1147 qa_ss_const.operation_code||'='||p_move_tbl(i).fm_operation_code||'@'||
1148 qa_ss_const.transaction_type||'='||move_txn_meaning||'=@'||
1149 qa_ss_const.to_department||'='||p_move_tbl(i).to_department_code||'@'||
1150 qa_ss_const.to_operation_code||'='||p_move_tbl(i).to_operation_code||'@'||
1151 qa_ss_const.project_number||'='||p_move_tbl(i).project_number||'@'||
1152 qa_ss_const.task_number||'='||p_move_tbl(i).task_number||'@'||
1153 qa_ss_const.bom_revision||'='||p_move_tbl(i).bom_revision;
1154
1155 IF (l_log_level <= wip_constants.trace_logging) THEN
1156 wip_logger.log(p_msg => 'l_context_values='||l_context_values,
1157 x_returnStatus => l_return_status);
1158 END IF;
1159
1160 BEGIN
1161 -- Check whether qualtiy collection plan exist or not
1162 l_quality_plan_exist := qa_txn_grp.evaluate_triggers(
1163 p_txn_number => qa_ss_const.wip_move_txn,
1164 p_org_id => p_org_id,
1165 p_context_values => l_context_values,
1166 x_plan_txn_ids => l_plan_txn_ids);
1167 IF(l_quality_plan_exist = fnd_api.g_false)THEN
1168 -- If no collection plan exist, there is no need to call
1169 -- qa_txn_grp.commit_allowed().
1170 raise fnd_api.g_exc_unexpected_error;
1171 END IF;
1172 -- If quality collection plan exist, we have to check whether it is
1173 -- mandatory or not.
1174 -- Fixed bug 5335024.Call is_commit_allowed() instead of commit_allowed()
1175 -- because is_commit_allowed will also check child quality plan.
1176 -- Moreover, is_commit_allowed will also return quality plan name.
1177 l_commit_allow := qa_txn_grp.is_commit_allowed(
1178 p_txn_number => qa_ss_const.wip_move_txn,
1179 p_org_id => p_org_id,
1180 p_plan_txn_ids => l_plan_txn_ids,
1181 --Pass 0 as Bryan suggested.
1182 p_collection_id => 0,
1183 x_plan_names => x_plan_names);
1184
1185 IF(l_commit_allow = fnd_api.g_false)THEN
1186 -- If quality plan is mandatory, no need to do more check.
1187 x_quality_require := fnd_api.g_true;
1188 GOTO end_quality_check;
1189 END IF;
1190 EXCEPTION
1191 WHEN fnd_api.g_exc_unexpected_error THEN
1192 -- This is not a real error, so we do not have to do anything.
1193 NULL;
1194 WHEN others THEN
1195 l_error_msg := ' unexpected error: ' || SQLERRM || 'SQLCODE = ' ||
1196 SQLCODE;
1197 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1198 fnd_message.set_token('MESSAGE', l_error_msg);
1199 fnd_msg_pub.add;
1200
1201 IF (l_log_level <= wip_constants.trace_logging) THEN
1202 wip_logger.log(p_msg => l_error_msg,
1203 x_returnStatus => l_return_status);
1204 END IF;
1205 END;
1206 END LOOP;
1207
1208 -- write to the log file
1209 IF (l_log_level <= wip_constants.trace_logging) THEN
1210 wip_logger.exitPoint(p_procName => 'wip_batch_move.quality_require',
1211 p_procReturnStatus => fnd_api.g_ret_sts_success,
1212 p_msg => 'procedure complete',
1213 x_returnStatus => l_return_status);
1214 END IF;
1215 <<end_quality_check>>
1216 x_return_status := fnd_api.g_ret_sts_success;
1217
1218 -- write to the log file
1219 IF (l_log_level <= wip_constants.trace_logging) THEN
1220 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_move',
1221 p_procReturnStatus => x_return_status,
1222 p_msg => 'procedure complete',
1223 x_returnStatus => l_return_status);
1224 END IF;
1225 EXCEPTION
1226 WHEN others THEN
1227 x_return_status := fnd_api.g_ret_sts_error;
1228 l_error_msg := ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1229
1230 IF (l_log_level <= wip_constants.trace_logging) THEN
1231 wip_logger.exitPoint(p_procName => 'wip_batch_move.derive_move',
1232 p_procReturnStatus => x_return_status,
1233 p_msg => l_error_msg,
1234 x_returnStatus => l_return_status);
1235 END IF;
1236 END quality_require;
1237
1238 PROCEDURE validate_batch(p_default_step_type IN NUMBER,
1239 x_return_status OUT NOCOPY VARCHAR2) IS
1240
1241 l_dff_required BOOLEAN;
1242 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1243 l_error_msg VARCHAR2(240);
1244 l_process_phase VARCHAR2(3);
1245 l_return_status VARCHAR2(1);
1246 l_params wip_logger.param_tbl_t;
1247 BEGIN
1248 l_process_phase := '1';
1249 -- write parameter value to log file
1250 IF (l_log_level <= wip_constants.trace_logging) THEN
1251 l_params(1).paramName := 'p_default_step_type';
1252 l_params(1).paramValue := p_default_step_type;
1253
1254 wip_logger.entryPoint(p_procName => 'wip_batch_move.validate_batch',
1255 p_params => l_params,
1256 x_returnStatus => l_return_status);
1257 END IF;
1258 l_process_phase := '2';
1259 IF(p_default_step_type IS NULL) THEN
1260 -- If no default step defined error out because we do not know what step
1261 -- we should move assembly to.
1262 fnd_message.set_name('WIP','WIP_NO_DEFAULT_STEP_NO_BATCH');
1263 fnd_msg_pub.add;
1264 l_error_msg := 'No default intraoperation step defined.';
1265 raise fnd_api.g_exc_unexpected_error;
1266 END IF;
1267
1268 l_process_phase := '3';
1269 l_dff_required := fnd_flex_apis.is_descr_required(
1270 x_application_id => 706, -- WIP
1271 x_desc_flex_name => 'WIP_MOVE_TRANSACTIONS');
1272
1273 IF(l_dff_required) THEN
1274 -- If DFF required for this transaction, error out because user cannot
1275 -- provide DFF information for this type of transaction.
1276 fnd_message.set_name('WIP','WIP_DFF_REQUIRE_NO_BATCH');
1277 fnd_msg_pub.add;
1278 l_error_msg := 'DFF is mandatory.';
1279 raise fnd_api.g_exc_unexpected_error;
1280 END IF;
1281
1282 x_return_status := fnd_api.g_ret_sts_success;
1283 -- write to the log file
1284 IF (l_log_level <= wip_constants.trace_logging) THEN
1285 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_batch',
1286 p_procReturnStatus => x_return_status,
1287 p_msg => 'procedure complete',
1288 x_returnStatus => l_return_status);
1289 END IF;
1290 EXCEPTION
1291 WHEN fnd_api.g_exc_unexpected_error THEN
1292 x_return_status := fnd_api.g_ret_sts_error;
1293
1294 IF (l_log_level <= wip_constants.trace_logging) THEN
1295 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_batch',
1296 p_procReturnStatus => x_return_status,
1297 p_msg => l_error_msg,
1298 x_returnStatus => l_return_status);
1299 END IF;
1300
1301 WHEN others THEN
1302 x_return_status := fnd_api.g_ret_sts_error;
1303 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
1304 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1305
1306 IF (l_log_level <= wip_constants.trace_logging) THEN
1307 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_batch',
1308 p_procReturnStatus => x_return_status,
1309 p_msg => l_error_msg,
1310 x_returnStatus => l_return_status);
1311 END IF;
1312 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1313 fnd_message.set_token('MESSAGE', l_error_msg);
1314 fnd_msg_pub.add;
1315
1316 END validate_batch;
1317
1318 PROCEDURE validate_row(p_org_id IN NUMBER,
1319 p_wip_entity_id IN NUMBER,
1320 p_wip_entity_name IN VARCHAR2,
1321 p_op_seq IN NUMBER,
1322 p_move_qty IN NUMBER,
1323 p_scrap_qty IN NUMBER,
1324 p_default_step_type IN NUMBER,
1325 p_default_scrap_acct_id IN NUMBER,
1326 p_resp_key IN VARCHAR2,
1327 p_assy_serial IN VARCHAR2,
1328 x_move_table_pvt OUT NOCOPY wip_batch_move.move_table_pvt,
1329 x_return_status OUT NOCOPY VARCHAR2) IS
1330
1331 l_available_qty NUMBER;
1332 l_fm_step_type NUMBER;
1333 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1334 l_queue_qty NUMBER;
1335 l_run_qty NUMBER;
1336 l_to_move_qty NUMBER;
1337 l_require_scrap_acct NUMBER;
1338 l_error_msg VARCHAR2(240);
1339 l_process_phase VARCHAR2(3);
1340 l_quality_require VARCHAR2(1);
1341 l_plan_names VARCHAR2(4000);
1342 l_return_status VARCHAR2(1);
1343 l_params wip_logger.param_tbl_t;
1344 BEGIN
1345 l_process_phase := '1';
1346 -- write parameter value to log file
1347 IF (l_log_level <= wip_constants.trace_logging) THEN
1348 l_params(1).paramName := 'p_org_id';
1349 l_params(1).paramValue := p_org_id;
1350 l_params(2).paramName := 'p_wip_entity_id';
1351 l_params(2).paramValue := p_wip_entity_id;
1352 l_params(3).paramName := 'p_wip_entity_name';
1353 l_params(3).paramValue := p_wip_entity_name;
1354 l_params(4).paramName := 'p_op_seq';
1355 l_params(4).paramValue := p_op_seq;
1356 l_params(5).paramName := 'p_move_qty';
1357 l_params(5).paramValue := p_move_qty;
1358 l_params(6).paramName := 'p_scrap_qty';
1359 l_params(6).paramValue := p_scrap_qty;
1360 l_params(7).paramName := 'p_default_step_type';
1361 l_params(7).paramValue := p_default_step_type;
1362 l_params(8).paramName := 'p_default_scrap_acct_id';
1363 l_params(8).paramValue := p_default_scrap_acct_id;
1364 l_params(9).paramName := 'p_resp_key';
1365 l_params(9).paramValue := p_resp_key;
1366 l_params(10).paramName := 'p_assy_serial';
1367 l_params(10).paramValue := p_assy_serial;
1368 wip_logger.entryPoint(p_procName => 'wip_batch_move.validate_row',
1369 p_params => l_params,
1370 x_returnStatus => l_return_status);
1371 END IF;
1372
1373 l_process_phase := '2';
1374 SELECT quantity_in_queue,
1375 quantity_running,
1376 quantity_waiting_to_move,
1377 quantity_in_queue + quantity_running + quantity_waiting_to_move
1378 INTO l_queue_qty,
1379 l_run_qty,
1380 l_to_move_qty,
1381 l_available_qty
1382 FROM wip_operations
1383 WHERE organization_id = p_org_id
1384 AND wip_entity_id = p_wip_entity_id
1385 AND operation_seq_num = p_op_seq;
1386
1387 -- If express move from search serial page, we should skip quantity split
1388 -- validation because we know exactly what serial user want to move even if
1389 -- quantity is splitted.
1390 IF(p_assy_serial IS NULL) THEN
1391 l_process_phase := '2.1';
1392 -- Quantity cannot split between queue, run and to move of the from
1393 -- operation because user cannot provide this information for batch move.
1394 IF(l_queue_qty <> l_available_qty AND
1395 l_run_qty <> l_available_qty AND
1396 l_to_move_qty <> l_available_qty) THEN
1397 fnd_message.set_name('WIP','WIP_QTY_SPLIT_NO_BATCH');
1398 fnd_msg_pub.add;
1399 l_error_msg := 'Quantity split at from operation.';
1400 raise fnd_api.g_exc_unexpected_error;
1401 END IF;
1402
1403 -- If quantity not split, derive from step.
1404 IF(l_queue_qty = l_available_qty)THEN
1405 l_fm_step_type := WIP_CONSTANTS.QUEUE;
1406 ELSIF(l_run_qty = l_available_qty) THEN
1407 l_fm_step_type := WIP_CONSTANTS.RUN;
1408 ELSIF(l_to_move_qty = l_available_qty) THEN
1409 l_fm_step_type := WIP_CONSTANTS.TOMOVE;
1410 END IF;
1411 ELSE -- Express move from search serial page, quantity can be splitted.
1412 l_process_phase := '2.2';
1413 -- From_step is the current location of the serial.
1414 SELECT nvl(msn.intraoperation_step_type, WIP_CONSTANTS.QUEUE)
1415 INTO l_fm_step_type
1416 FROM mtl_serial_numbers msn,
1417 wip_discrete_jobs wdj
1418 WHERE wdj.organization_id = p_org_id
1419 AND wdj.wip_entity_id = p_wip_entity_id
1420 AND msn.inventory_item_id = wdj.primary_item_id
1421 AND msn.serial_number = p_assy_serial;
1422 END IF; -- If not express move from search serial page.
1423
1424 l_process_phase := '3';
1425 -- Quantity to move pluse quantity to scrap must be less than or equal to
1426 -- available quantity because we will not support overmove for batch move.
1427 IF(p_move_qty + p_scrap_qty > l_available_qty) THEN
1428 fnd_message.set_name('WIP','WIP_NOT_ENOUGH_QTY_FOR_BATCH');
1429 fnd_msg_pub.add;
1430 l_error_msg := 'Transaction quantity is greater than available quantity.';
1431 raise fnd_api.g_exc_unexpected_error;
1432 END IF;
1433
1434 l_process_phase := '4';
1435 SELECT mandatory_scrap_flag
1436 INTO l_require_scrap_acct
1437 FROM wip_parameters
1438 WHERE organization_id = p_org_id;
1439 -- If user provide scrap quantity and organization require scrap account,
1440 -- preference "Default Scrap Account" must be set.
1441 IF(p_scrap_qty > 0 AND
1442 l_require_scrap_acct = WIP_CONSTANTS.YES AND
1443 p_default_scrap_acct_id IS NULL) THEN
1444 fnd_message.set_name('WIP','WIP_NO_SCRAP_ACCT_NO_BATCH');
1445 fnd_msg_pub.add;
1446 l_error_msg := 'No default scrap accout defined.';
1447 raise fnd_api.g_exc_unexpected_error;
1448 END IF;
1449 l_process_phase := '5';
1450 -- Derive move/scrap information
1451 derive_row(p_org_id => p_org_id,
1452 p_wip_entity_id => p_wip_entity_id,
1453 p_wip_entity_name => p_wip_entity_name,
1454 p_fm_op_seq => p_op_seq,
1455 p_move_qty => p_move_qty,
1456 p_scrap_qty => p_scrap_qty,
1457 p_require_scrap_acct => l_require_scrap_acct,
1458 p_default_scrap_acct_id => p_default_scrap_acct_id,
1459 p_default_step_type => p_default_step_type,
1460 p_fm_step_type => l_fm_step_type,
1461 p_resp_key => p_resp_key,
1462 x_move_table_pvt => x_move_table_pvt,
1463 x_return_status => x_return_status);
1464
1465 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1466 l_error_msg := 'wip_batch_move.derive_row failed';
1467 raise fnd_api.g_exc_unexpected_error;
1468 END IF;
1469 l_process_phase := '6';
1470 -- Check whether quality collection is mandatory or not.
1471 quality_require(p_org_id => p_org_id,
1472 p_move_tbl => x_move_table_pvt,
1473 x_quality_require => l_quality_require,
1474 x_plan_names => l_plan_names,
1475 x_return_status => x_return_status);
1476
1477 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1478 l_error_msg := 'wip_batch_move.quality_require failed';
1479 raise fnd_api.g_exc_unexpected_error;
1480 ELSE -- If success, check whether quality is mandatory or not.
1481 IF(l_quality_require = fnd_api.g_true) THEN
1482 -- Fixed bug 5335024. Change error message from a generic error message
1483 -- WIP_QUALITY_REQUIRE_NO_BATCH to QA_TXN_INCOMPLETE which contain plan
1484 -- name.
1485 fnd_message.set_name('QA','QA_TXN_INCOMPLETE');
1486 fnd_message.set_token('PLANS1', l_plan_names);
1487 fnd_msg_pub.add;
1488 l_error_msg := 'Quality collection is mandatory.';
1489 raise fnd_api.g_exc_unexpected_error;
1490 END IF;
1491 END IF;
1492
1493 x_return_status := fnd_api.g_ret_sts_success;
1494
1495 -- write to the log file
1496 IF (l_log_level <= wip_constants.trace_logging) THEN
1497 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_row',
1498 p_procReturnStatus => x_return_status,
1499 p_msg => 'procedure complete',
1500 x_returnStatus => l_return_status);
1501 END IF;
1502 EXCEPTION
1503 WHEN fnd_api.g_exc_unexpected_error THEN
1504 x_return_status := fnd_api.g_ret_sts_error;
1505 IF (l_log_level <= wip_constants.trace_logging) THEN
1506 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_row',
1507 p_procReturnStatus => x_return_status,
1508 p_msg => l_error_msg,
1509 x_returnStatus => l_return_status);
1510 END IF;
1511
1512 WHEN others THEN
1513 x_return_status := fnd_api.g_ret_sts_error;
1514 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
1515 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1516
1517 IF (l_log_level <= wip_constants.trace_logging) THEN
1518 wip_logger.exitPoint(p_procName => 'wip_batch_move.validate_row',
1519 p_procReturnStatus => x_return_status,
1520 p_msg => l_error_msg,
1521 x_returnStatus => l_return_status);
1522 END IF;
1523 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1524 fnd_message.set_token('MESSAGE', l_error_msg);
1525 fnd_msg_pub.add;
1526 END validate_row;
1527
1528 PROCEDURE process(p_move_table IN wip_batch_move.move_table,
1529 p_resp_key IN VARCHAR2,
1530 p_org_id IN NUMBER,
1531 p_dept_id IN NUMBER,
1532 p_employee_id IN NUMBER,
1533 x_return_status OUT NOCOPY VARCHAR2) IS
1534
1535 l_default_scrap_acct_id NUMBER;
1536 l_default_step_type NUMBER;
1537 l_error_row NUMBER := 0;
1538 l_group_id NUMBER;
1539 l_log_level NUMBER := fnd_log.g_current_runtime_level;
1540 l_total_row NUMBER;
1541 l_error_msg VARCHAR2(240);
1542 l_error_text VARCHAR2(2000);
1543 l_return_status VARCHAR2(1);
1544 l_move_table_pvt wip_batch_move.move_table_pvt;
1545 l_params wip_logger.param_tbl_t;
1546 BEGIN
1547 -- write parameter value to log file
1548 IF (l_log_level <= wip_constants.trace_logging) THEN
1549 l_params(1).paramName := 'p_resp_key';
1550 l_params(1).paramValue := p_resp_key;
1551 l_params(2).paramName := 'p_org_id';
1552 l_params(2).paramValue := p_org_id;
1553 l_params(3).paramName := 'p_dept_id';
1554 l_params(3).paramValue := p_dept_id;
1555 l_params(4).paramName := 'p_employee_id';
1556 l_params(4).paramValue := p_employee_id;
1557 wip_logger.entryPoint(p_procName => 'wip_batch_move.process',
1558 p_params => l_params,
1559 x_returnStatus => l_return_status);
1560 END IF;
1561
1562 -- Initialize message stack
1563 fnd_msg_pub.initialize;
1564 -- Get neccessary lookups from the database
1565 initialize_lookups;
1566 -- Get preferences required to perform batch move transactions.
1567 get_preferences(p_resp_key => p_resp_key,
1568 p_org_id => p_org_id,
1569 p_dept_id => p_dept_id,
1570 x_default_step_type => l_default_step_type,
1571 x_default_scrap_acct_id => l_default_scrap_acct_id,
1572 x_return_status => x_return_status);
1573
1574 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1575 l_error_msg := 'wip_batch_move.get_preferences failed';
1576 raise fnd_api.g_exc_unexpected_error;
1577 END IF;
1578 -- Perform generic validation for the whole batch.
1579 validate_batch(p_default_step_type => l_default_step_type,
1580 x_return_status => x_return_status);
1581
1582 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1583 l_error_msg := 'wip_batch_move.validate_batch failed';
1584 raise fnd_api.g_exc_unexpected_error;
1585 END IF;
1586
1587 l_total_row := p_move_table.count;
1588
1589 FOR i IN 1..l_total_row LOOP
1590 BEGIN
1591 SAVEPOINT s_batch_move1;
1592 -- Perform row specific validation.
1593 validate_row(p_org_id => p_org_id,
1594 p_wip_entity_id => p_move_table(i).wip_entity_id,
1595 p_wip_entity_name => p_move_table(i).wip_entity_name,
1596 p_op_seq => p_move_table(i).op_seq,
1597 p_move_qty => p_move_table(i).move_qty,
1598 p_scrap_qty => p_move_table(i).scrap_qty,
1599 p_default_step_type => l_default_step_type,
1600 p_default_scrap_acct_id => l_default_scrap_acct_id,
1601 p_resp_key => p_resp_key,
1602 p_assy_serial => p_move_table(i).assy_serial,
1603 x_move_table_pvt => l_move_table_pvt,
1604 x_return_status => x_return_status);
1605
1606 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1607 l_error_msg := 'wip_batch_move.validate_row failed';
1608 raise fnd_api.g_exc_unexpected_error;
1609 END IF;
1610 -- Insert move/scrap record into WMTI
1611 insert_move_records(p_org_id => p_org_id,
1612 p_employee_id => p_employee_id,
1613 p_move_table_pvt => l_move_table_pvt,
1614 p_assy_serial => p_move_table(i).assy_serial,
1615 x_group_id => l_group_id,
1616 x_return_status => x_return_status);
1617
1618 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1619 l_error_msg := 'wip_batch_move.insert_move_records failed';
1620 raise fnd_api.g_exc_unexpected_error;
1621 END IF;
1622 -- Clear all move information.
1623 l_move_table_pvt.delete;
1624
1625 -- Process move records
1626 process_move_records(p_group_id => l_group_id,
1627 x_return_status => x_return_status);
1628
1629 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
1630 l_error_msg := 'wip_batch_move.process_move_records failed';
1631 raise fnd_api.g_exc_unexpected_error;
1632 ELSE
1633 -- Initialize message stack to clear "Txn Success" inventory put in
1634 -- the stack.
1635 fnd_msg_pub.initialize;
1636 COMMIT;
1637 END IF;
1638
1639 EXCEPTION
1640 WHEN fnd_api.g_exc_unexpected_error THEN
1641 ROLLBACK TO SAVEPOINT s_batch_move1;
1642 -- Put a useful error message in the stack to display back to the user.
1643 wip_utilities.get_message_stack(p_msg => l_error_text);
1644 add_error(p_job_name => p_move_table(i).wip_entity_name,
1645 p_op_seq_num => p_move_table(i).op_seq,
1646 p_error_text => l_error_text);
1647 l_error_row := l_error_row + 1;
1648 l_error_msg := 'row = ' || i || ' : ' || l_error_msg;
1649 IF (l_log_level <= wip_constants.trace_logging) THEN
1650 wip_logger.log(p_msg => l_error_msg,
1651 x_returnStatus => l_return_status);
1652 END IF;
1653
1654 WHEN others THEN
1655 ROLLBACK TO SAVEPOINT s_batch_move1;
1656 -- Put a useful error message in the stack to display back to the user.
1657 wip_utilities.get_message_stack(p_msg => l_error_text);
1658 add_error(p_job_name => p_move_table(i).wip_entity_name,
1659 p_op_seq_num => p_move_table(i).op_seq,
1660 p_error_text => l_error_text);
1661 l_error_row := l_error_row + 1;
1662 l_error_msg := 'row = ' || i || ' : ' ||
1663 ' unexpected error: ' || SQLERRM || 'SQLCODE = ' ||
1664 SQLCODE;
1665 IF (l_log_level <= wip_constants.trace_logging) THEN
1666 wip_logger.log(p_msg => l_error_msg,
1667 x_returnStatus => l_return_status);
1668 END IF;
1669 END;
1670 END LOOP;
1671
1672 IF(l_error_row = 0) THEN
1673 x_return_status := fnd_api.g_ret_sts_success;
1674 ELSE
1675 -- Put all error message to message stack.
1676 load_errors;
1677 x_return_status := fnd_api.g_ret_sts_error;
1678 END IF;
1679 -- Write to the log file.
1680 IF (l_log_level <= wip_constants.trace_logging) THEN
1681 wip_logger.exitPoint(p_procName => 'wip_batch_move.process',
1682 p_procReturnStatus => x_return_status,
1683 p_msg => 'procedure complete',
1684 x_returnStatus => l_return_status);
1685 wip_logger.cleanUp(x_returnStatus => l_return_status);
1686 END IF;
1687 EXCEPTION
1688 WHEN fnd_api.g_exc_unexpected_error THEN
1689 x_return_status := fnd_api.g_ret_sts_error;
1690 IF (l_log_level <= wip_constants.trace_logging) THEN
1691 wip_logger.exitPoint(p_procName => 'wip_batch_move.process',
1692 p_procReturnStatus => x_return_status,
1693 p_msg => l_error_msg,
1694 x_returnStatus => l_return_status);
1695 wip_logger.cleanUp(x_returnStatus => l_return_status);
1696 END IF;
1697
1698 WHEN others THEN
1699 x_return_status := fnd_api.g_ret_sts_error;
1700 l_error_msg := ' unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1701
1702 IF (l_log_level <= wip_constants.trace_logging) THEN
1703 wip_logger.exitPoint(p_procName => 'wip_batch_move.process',
1704 p_procReturnStatus => x_return_status,
1705 p_msg => l_error_msg,
1706 x_returnStatus => l_return_status);
1707 wip_logger.cleanUp(x_returnStatus => l_return_status);
1708 END IF;
1709 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1710 fnd_message.set_token('MESSAGE', l_error_msg);
1711 fnd_msg_pub.add;
1712 END process;
1713 END wip_batch_move;