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