1 PACKAGE BODY wip_move_validator AS
2 /* $Header: wipmovvb.pls 120.12 2007/09/17 21:19:06 kboonyap ship $ */
3 /*********************************************
4 * declare global variables for this package *
5 *********************************************/
6 g_group_id NUMBER;
7 enums txnID_list;
8
9 -- error handling procedure
10 PROCEDURE add_error(p_txn_id IN NUMBER,
11 p_err_col IN VARCHAR2,
12 p_err_msg IN VARCHAR2) IS
13 error_record request_error;
14 BEGIN
15 -- create error record
16 error_record.transaction_id := p_txn_id;
17 error_record.error_column := p_err_col;
18 error_record.error_message := substrb(p_err_msg,1,240);
19
20 -- add error record to error table (current_errors)
21 current_errors(current_errors.count + 1) := error_record;
22 END add_error;
23
24 -- error handling procedure
25 PROCEDURE add_error(p_txn_ids IN txnID_list,
26 p_err_col IN VARCHAR2,
27 p_err_msg IN VARCHAR2) IS
28
29 error_record request_error;
30
31 BEGIN
32 /* Bug#3123422 - Moved the invariable statements out of the loop
33 to optimize the code */
34 error_record.error_column := p_err_col;
35 error_record.error_message := substrb(p_err_msg,1,240);
36
37 FOR i IN 1..p_txn_ids.count LOOP
38 -- create error record
39 error_record.transaction_id := p_txn_ids(i);
40 -- add error record to error table (current_errors)
41 current_errors(current_errors.count + 1) := error_record;
42 END LOOP;
43 END add_error;
44
45 Procedure load_errors IS
46
47 n_errors NUMBER;
48 error_no NUMBER := 1;
49
50 BEGIN
51
52 n_errors := current_errors.count;
53
54 WHILE (error_no <= n_errors) LOOP
55
56 INSERT INTO wip_txn_interface_errors(
57 transaction_id,
58 error_message,
59 error_column,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login,
65 request_id,
66 program_application_id,
67 program_id,
68 program_update_date
69 )
70 SELECT current_errors(error_no).transaction_id, -- transaction_id
71 current_errors(error_no).error_message, -- error_message
72 current_errors(error_no).error_column, -- error_column
73 SYSDATE, -- last_update_date
74 NVL(last_updated_by, -1),
75 SYSDATE, -- creation_date
76 NVL(created_by, -1),
77 last_update_login,
78 request_id,
79 program_application_id,
80 program_id,
81 program_update_date
82 FROM wip_move_txn_interface
83 WHERE transaction_id = current_errors(error_no).transaction_id
84 AND group_id = g_group_id;
85
86 error_no := error_no + 1;
87 END LOOP;
88
89 -- cleare error table
90 current_errors.delete ;
91
92 END load_errors;
93 -- end error handling procedure
94
95 -- validate organization_id. The caller have an option to provide either
96 -- organization_id or organization_code. If the caller pass organization_id,
97 -- the id need to be valid. If the caller pass organization_code, we will
98 -- derive the organization_id. If the caller pass both, both value must be
99 -- consistent to each other.
100
101 /* Bug#2956953 - This procedure will be called only from wip move manager.
102 Call to this procedure from wip move worker code is commented
103 - Changes done as part of Wip Move Sequencing Project */
104
105 PROCEDURE organization_id(p_count_of_errored OUT NOCOPY NUMBER) IS
106 BEGIN
107 -- Derive ORGANIZATIOIN_ID if user provided only ORGANIZATION_CODE
108 UPDATE wip_move_txn_interface wmti
109 SET wmti.organization_id =
110 (SELECT mp.organization_id
111 FROM mtl_parameters mp
112 WHERE mp.organization_code = UPPER(wmti.organization_code))
113 WHERE wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
114 AND wmti.process_status = WIP_CONSTANTS.PENDING
115 AND wmti.organization_id IS NULL
116 AND wmti.organization_code IS NOT NULL;
117
118 -- reset enums table
119 enums.delete;
120
121 -- If cannot derive ORGANIZATION_ID or ORGANIZATION_ID not corresponding to
122 -- ORGANIZATION_CODE provided, error out.
123 UPDATE wip_move_txn_interface wmti
124 SET wmti.process_status = WIP_CONSTANTS.ERROR
125 WHERE wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
126 AND wmti.process_status = WIP_CONSTANTS.PENDING
127 AND ((wmti.organization_id IS NULL) -- cannot derive ORGANIZATION_ID
128 OR
129 (NOT EXISTS
130 (SELECT 'X'
131 FROM mtl_parameters mp
132 WHERE mp.organization_code = UPPER(NVL(wmti.organization_code,
133 mp.organization_code))
134 AND mp.organization_id = wmti.organization_id
135 )
136 ))
137 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
138
139 p_count_of_errored := sql%rowcount;
140
141 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
142 fnd_message.set_token('ENTITY1', 'ORGANIZATION_ID');
143 fnd_message.set_token('ENTITY2', 'ORGANIZATION_CODE');
144 add_error(p_txn_ids => enums,
145 p_err_col => 'ORGANIZATION_ID/CODE',
146 p_err_msg => fnd_message.get);
147
148 load_errors;
149
150 END organization_id;
151
152 -- validate wip_entity_id against wip_entities table. The caller have an
153 -- option to provide either wip_entity_id or wip_entity_name. If the caller
154 -- pass wip_entity_id, the id need to be valid. If the caller pass
155 -- wip_entity_name, we will derive the wip_entity_id. If the caller pass both,
156 -- both value must be consistent to each other. Moreover, the wip_entity_id
157 -- provided must have status that allow move transaction(3,4)
158 -- The wip_entity_id provided also need to have assembly associated with it.
159
160 -- Also validate line_id against wip_lines table. The caller have an option to
161 -- provide either line_id or line_code. If the caller pass line_id, the id
162 -- need to be valid. If the caller pass line_code, we will derive the
163 -- line_id. If the caller pass both, both value must be consistent to
164 -- each other. Only validate these values if the transaction type is
165 -- repetitive. If line_id and line_code are null, the caller need to pass
166 -- repetitive_schedule_id.
167 -- this routine will also derive the first transactable schedule if the
168 -- caller do not provide repetitive schedule id for repetitive transaction
169
170 -- Also Check that the job is not serilized. We do not support serialized
171 -- transaction for background.
172 PROCEDURE wip_entity_id IS
173 BEGIN
174 -- Derive WIP_ENTITY_ID if user provided only WIP_ENTITY_NAME
175 UPDATE wip_move_txn_interface wmti
176 SET wmti.wip_entity_id =
177 (SELECT we.wip_entity_id
178 FROM wip_entities we
179 WHERE we.wip_entity_name = wmti.wip_entity_name
180 AND we.organization_id = wmti.organization_id
181 AND entity_type IN (WIP_CONSTANTS.DISCRETE,
182 WIP_CONSTANTS.REPETITIVE,
183 WIP_CONSTANTS.LOTBASED))
184 WHERE wmti.group_id = g_group_id
185 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
186 AND wmti.process_status = WIP_CONSTANTS.RUNNING
187 AND wmti.wip_entity_id IS NULL
188 AND wmti.wip_entity_name IS NOT NULL;
189
190 -- reset enums table
191 enums.delete;
192 -- If unable to derive WIP_ENTITY_ID or WIP_ENTITY_ID not conresponding
193 -- to WIP_ENTITY_NAME specified, error out.
194 UPDATE wip_move_txn_interface wmti
195 SET wmti.process_status = WIP_CONSTANTS.ERROR
196 WHERE wmti.group_id = g_group_id
197 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
198 AND wmti.process_status = WIP_CONSTANTS.RUNNING
199 AND ((wmti.wip_entity_id IS NULL) -- cannot derive WIP_ENTITY_ID
200 OR
201 (NOT EXISTS
202 (SELECT 'X'
203 FROM wip_entities we
204 WHERE we.wip_entity_name = NVL(wmti.wip_entity_name,
205 we.wip_entity_name)
206 AND we.wip_entity_id = wmti.wip_entity_id
207 AND we.organization_id = wmti.organization_id
208 AND entity_type IN (WIP_CONSTANTS.DISCRETE,
209 WIP_CONSTANTS.REPETITIVE,
210 WIP_CONSTANTS.LOTBASED)
211 )
212 ))
213 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
214
215 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
216 fnd_message.set_token('ENTITY', 'WIP_ENTITY_ID - WIP_ENTITY_NAME');
217 add_error(p_txn_ids => enums,
218 p_err_col => 'WIP_ENTITY_ID/NAME',
219 p_err_msg => fnd_message.get);
220
221 -- Derive ENTITY_TYPE and PRIMARY_ITEM_ID from WIP_ENTITY_ID
222 UPDATE wip_move_txn_interface wmti
223 SET (wmti.entity_type, wmti.primary_item_id) =
224 (SELECT we.entity_type,
225 we.primary_item_id
226 FROM wip_entities we
227 WHERE we.wip_entity_id = wmti.wip_entity_id)
228 WHERE wmti.group_id = g_group_id
229 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
230 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
231
232 -- reset enums table
233 enums.delete;
234 -- If non-standard job and no assembly defined, error out
235 UPDATE wip_move_txn_interface wmti
236 SET wmti.process_status = WIP_CONSTANTS.ERROR
237 WHERE wmti.group_id = g_group_id
238 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
239 AND wmti.process_status = WIP_CONSTANTS.RUNNING
240 AND wmti.primary_item_id IS NULL
241 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
242
243 fnd_message.set_name('WIP', 'WIP_NO_ASSY_NO_TXN');
244 add_error(p_txn_ids => enums,
245 p_err_col => 'WIP_ENTITY_ID/NAME',
246 p_err_msg => fnd_message.get);
247
248 /************************
249 * Start Repetitive Check
250 ************************/
251 -- Derive LINE_ID if user provided only LINE_CODE.
252 UPDATE wip_move_txn_interface wmti
253 SET wmti.line_id =
254 (SELECT wl.line_id
255 FROM wip_lines wl
256 WHERE wl.line_code = wmti.line_code
257 AND wl.organization_id = wmti.organization_id
258 AND NVL(wl.disable_date, SYSDATE) >= SYSDATE)
259 WHERE wmti.group_id = g_group_id
260 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
261 AND wmti.process_status = WIP_CONSTANTS.RUNNING
262 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
263 AND wmti.line_id IS NULL
264 AND wmti.line_code IS NOT NULL;
265
266 -- reset enums table
267 enums.delete;
268 -- If unable to derive LINE_ID or LINE_ID not conresponding to LINE_CODE
269 -- specified, error out.
270 UPDATE wip_move_txn_interface wmti
271 SET wmti.process_status = WIP_CONSTANTS.ERROR
272 WHERE wmti.group_id = g_group_id
273 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
274 AND wmti.process_status = WIP_CONSTANTS.RUNNING
275 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
276 AND ((wmti.line_id IS NULL)
277 OR
278 (NOT EXISTS
279 (SELECT 'X'
280 FROM wip_lines wl
281 WHERE wl.line_code = NVL(wmti.line_code, wl.line_code)
282 AND wl.line_id = wmti.line_id
283 AND wl.organization_id = wmti.organization_id
284 AND NVL(wl.disable_date, SYSDATE) >= SYSDATE
285 )
286 ))
287 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
288
289 fnd_message.set_name('WIP', 'WIP_INVALID_LINE');
290 add_error(p_txn_ids => enums,
291 p_err_col => 'LINE_ID/CODE',
292 p_err_msg => fnd_message.get);
293
294 -- derive the first transactable schedule if REPETITIVE_SCHEDULE_ID is null
295 UPDATE wip_move_txn_interface wmti
296 SET wmti.repetitive_schedule_id =
297 (SELECT wrs1.repetitive_schedule_id
298 FROM wip_repetitive_schedules wrs1
299 WHERE wrs1.wip_entity_id = wmti.wip_entity_id
300 AND wrs1.organization_id = wmti.organization_id
301 AND wrs1.line_id = wmti.line_id
302 AND wrs1.status_type IN (WIP_CONSTANTS.RELEASED,
303 WIP_CONSTANTS.COMP_CHRG)
304 AND wrs1.first_unit_start_date =
305 (SELECT MIN(wrs2.first_unit_start_date)
306 FROM wip_repetitive_schedules wrs2
307 WHERE wrs2.wip_entity_id = wmti.wip_entity_id
308 AND wrs2.organization_id = wmti.organization_id
309 AND wrs2.line_id = wmti.line_id
310 AND wrs2.status_type IN (WIP_CONSTANTS.RELEASED,
311 WIP_CONSTANTS.COMP_CHRG)))
312 WHERE wmti.group_id = g_group_id
313 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
314 AND wmti.process_status = WIP_CONSTANTS.RUNNING
315 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
316 AND wmti.repetitive_schedule_id IS NULL;
317
318 -- reset enums table
319 enums.delete;
320 -- By this time, all repetive transaction should have REPETITIVE_SCHEDULE_ID
321 -- Otherwise, error out
322 UPDATE wip_move_txn_interface wmti
323 SET wmti.process_status = WIP_CONSTANTS.ERROR
324 WHERE wmti.group_id = g_group_id
325 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
326 AND wmti.process_status = WIP_CONSTANTS.RUNNING
327 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
328 AND wmti.repetitive_schedule_id IS NULL
329 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
330
331 fnd_message.set_name('WIP', 'WIP_INVALID_LINE');
332 add_error(p_txn_ids => enums,
333 p_err_col => 'LINE_ID/CODE',
334 p_err_msg => fnd_message.get);
335
336 /************************
337 * End Repetitive Check
338 ************************/
339
340 /************************
341 * Start Discrete Check
342 ************************/
343 -- reset enums table
344 enums.delete;
345 -- For Discrete and Lotbased, user should not provide these 3 values
346 UPDATE wip_move_txn_interface wmti
347 SET wmti.process_status = WIP_CONSTANTS.ERROR
348 WHERE wmti.group_id = g_group_id
349 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
350 AND wmti.process_status = WIP_CONSTANTS.RUNNING
351 AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
352 WIP_CONSTANTS.LOTBASED)
353 AND (wmti.line_id IS NOT NULL OR
354 wmti.line_code IS NOT NULL OR
355 wmti.repetitive_schedule_id IS NOT NULL)
356 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
357
358 fnd_message.set_name('WIP', 'WIP_NULL_LINE_ID');
359 add_error(p_txn_ids => enums,
360 p_err_col => 'WIP_ENTITY_ID/NAME',
361 p_err_msg => fnd_message.get);
362
363 /************************
364 * End Discrete Check
365 ************************/
366 -- reset enums table
367 enums.delete;
368 -- Check job status not either Complete or Release, error out.
369 UPDATE wip_move_txn_interface wmti
370 SET wmti.process_status = WIP_CONSTANTS.ERROR
371 WHERE wmti.group_id = g_group_id
372 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
373 AND wmti.process_status = WIP_CONSTANTS.RUNNING
374 AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
375 AND NOT EXISTS
376 (SELECT 'X'
377 FROM wip_repetitive_schedules wrs
381 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
378 WHERE wrs.wip_entity_id = wmti.wip_entity_id
379 AND wrs.organization_id = wmti.organization_id
380 AND wrs.line_id = wmti.line_id
382 WIP_CONSTANTS.COMP_CHRG)))
383 OR
384 (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
385 WIP_CONSTANTS.LOTBASED)
386 AND NOT EXISTS
387 (SELECT 'X'
388 FROM wip_discrete_jobs wdj
389 WHERE wdj.wip_entity_id = wmti.wip_entity_id
390 AND wdj.organization_id = wmti.organization_id
391 AND wdj.status_type IN (WIP_CONSTANTS.RELEASED,
392 WIP_CONSTANTS.COMP_CHRG))))
393 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
394
395 fnd_message.set_name('WIP', 'WIP_NO_CHARGES_ALLOWED');
396 add_error(p_txn_ids => enums,
397 p_err_col => 'WIP_ENTITY_ID/NAME',
398 p_err_msg => fnd_message.get);
399
400 -- reset enums table
401 enums.delete;
402 -- If job/schedule specified has no routing, error out
403 UPDATE wip_move_txn_interface wmti
404 SET wmti.process_status = WIP_CONSTANTS.ERROR
405 WHERE wmti.group_id = g_group_id
406 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
407 AND wmti.process_status = WIP_CONSTANTS.RUNNING
408 AND NOT EXISTS
409 (SELECT 'X'
410 FROM wip_operations wo
411 WHERE wo.wip_entity_id = wmti.wip_entity_id
412 AND wo.organization_id = wmti.organization_id
413 AND NVL(wo.repetitive_schedule_id, -1) =
414 NVL(wmti.repetitive_schedule_id, -1))
415 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
416
417 fnd_message.set_name('WIP', 'WIP_ROUTING_NOT_FOUND');
418 fnd_message.set_token('ROUTINE', 'for Job/Schedule specified');
419 add_error(p_txn_ids => enums,
420 p_err_col => 'WIP_ENTITY_ID/NAME',
421 p_err_msg => fnd_message.get);
422
423 END wip_entity_id;
424
425 -- validate transaction_type. If the callers did not provide this info,
426 -- default to regular move. We do not support easy completion/return for
427 -- both discrete and repetitive if the assembly is under serial control.
428 -- We allow easy completion/return if the assembly is under lot control,
429 -- but the caller need to provide lot information when define a job.
430 -- However, we support only discrete and lotbased for this feature.
431 -- For repetitive, if the assembly is under lot control, it will error out.
432 PROCEDURE transaction_type IS
433 BEGIN
434 -- Default TRANSACTION_TYPE to Move if users do not provide one
435 UPDATE wip_move_txn_interface wmti
436 SET wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
437 WHERE wmti.group_id = g_group_id
438 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
439 AND wmti.process_status = WIP_CONSTANTS.RUNNING
440 AND wmti.transaction_type IS NULL;
441
442 -- reset enums table
443 enums.delete;
444 -- Errot out, if transaction type not either Move or EZ Complete or
445 -- EZ Return
446 UPDATE wip_move_txn_interface wmti
447 SET wmti.process_status = WIP_CONSTANTS.ERROR
448 WHERE wmti.group_id = g_group_id
449 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
450 AND wmti.process_status = WIP_CONSTANTS.RUNNING
451 AND wmti.transaction_type NOT IN (WIP_CONSTANTS.MOVE_TXN,
452 WIP_CONSTANTS.COMP_TXN,
453 WIP_CONSTANTS.RET_TXN)
454 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
455
456 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
457 fnd_message.set_token('ENTITY', 'TRANSACTION_TYPE');
458 add_error(p_txn_ids => enums,
459 p_err_col => 'TRANSACTION_TYPE',
460 p_err_msg => fnd_message.get);
461
462 -- reset enums table
463 enums.delete;
464 -- Error out if easy completion/return and the assembly is under serial
465 -- control because we cannot gather or derive serial number for background
466 -- txns
467 UPDATE wip_move_txn_interface wmti
468 SET wmti.process_status = WIP_CONSTANTS.ERROR
469 WHERE wmti.group_id = g_group_id
470 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
471 AND wmti.process_status = WIP_CONSTANTS.RUNNING
472 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
473 WIP_CONSTANTS.RET_TXN)
474 AND EXISTS
475 (SELECT 'X'
476 FROM mtl_system_items msi,
477 wip_discrete_jobs wdj
478 WHERE wdj.wip_entity_id = wmti.wip_entity_id
479 AND msi.inventory_item_id = wmti.primary_item_id
480 AND msi.organization_id = wmti.organization_id
481 AND wdj.serialization_start_op IS NULL
482 AND msi.serial_number_control_code IN (WIP_CONSTANTS.FULL_SN,
483 WIP_CONSTANTS.DYN_RCV_SN))
484 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
485
486 fnd_message.set_name('WIP', 'WIP_EZ_NO_SERIAL_CONTROL2');
487 add_error(p_txn_ids => enums,
488 p_err_col => 'TRANSACTION_TYPE',
489 p_err_msg => fnd_message.get);
490
491 -- reset enums table
492 enums.delete;
496 SET wmti.process_status = WIP_CONSTANTS.ERROR
493 -- Error out if easy completion/return and no default completion subinventory
494 -- locator defined for both Discrete and Repetitive Schedule
495 UPDATE wip_move_txn_interface wmti
497 WHERE wmti.group_id = g_group_id
498 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
499 AND wmti.process_status = WIP_CONSTANTS.RUNNING
500 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
501 WIP_CONSTANTS.RET_TXN)
502 AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
503 AND EXISTS
504 (SELECT 'X'
505 FROM wip_repetitive_items wri
506 WHERE wri.wip_entity_id = wmti.wip_entity_id
507 AND wri.organization_id = wmti.organization_id
508 AND wri.line_id = wmti.line_id
509 AND wri.completion_subinventory IS NULL))
510 OR
511 (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
512 WIP_CONSTANTS.LOTBASED)
513 AND EXISTS
514 (SELECT 'X'
515 FROM wip_discrete_jobs wdj
516 WHERE wdj.wip_entity_id = wmti.wip_entity_id
517 AND wdj.organization_id = wmti.organization_id
518 AND wdj.completion_subinventory IS NULL)))
519 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
520
521 fnd_message.set_name('WIP', 'WIP_EZ_NO_SUBINV_DEFAULT2');
522 add_error(p_txn_ids => enums,
523 p_err_col => 'TRANSACTION_TYPE',
524 p_err_msg => fnd_message.get);
525
526 -- reset enums table
527 enums.delete;
528 -- Error out if item revision does not exist as a BOM revision
529 UPDATE wip_move_txn_interface wmti
530 SET wmti.process_status = WIP_CONSTANTS.ERROR
531 WHERE wmti.group_id = g_group_id
532 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
533 AND wmti.process_status = WIP_CONSTANTS.RUNNING
534 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
535 WIP_CONSTANTS.RET_TXN)
536 AND EXISTS
537 (SELECT 'X'
538 FROM mtl_system_items msi
539 WHERE msi.inventory_item_id = wmti.primary_item_id
540 AND msi.organization_id = wmti.organization_id
541 AND msi.revision_qty_control_code =
542 WIP_CONSTANTS.REVISION_CONTROLLED)
543 AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
544 AND NOT EXISTS
545 (SELECT 'X'
546 FROM wip_repetitive_schedules wrs,
547 mtl_item_revisions mir
548 WHERE wrs.organization_id = wmti.organization_id
549 AND wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
550 AND mir.organization_id = wmti.organization_id
551 AND mir.inventory_item_id = wmti.primary_item_id
552 -- Fixed bug 2387630
553 AND (wrs.bom_revision IS NULL OR
554 mir.revision = wrs.bom_revision)))
555 OR
556 (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
557 WIP_CONSTANTS.LOTBASED)
558 AND NOT EXISTS
559 (SELECT 'X'
560 FROM wip_discrete_jobs wdj,
561 mtl_item_revisions mir
562 WHERE wdj.organization_id = wmti.organization_id
563 AND wdj.wip_entity_id = wmti.wip_entity_id
564 AND mir.organization_id = wmti.organization_id
565 AND mir.inventory_item_id = wmti.primary_item_id
566 -- Fixed bug 2387630
567 AND (wdj.bom_revision IS NULL OR
568 mir.revision = wdj.bom_revision))))
569 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
570
571 fnd_message.set_name('WIP', 'WIP_BOM_ITEM_REVISION');
572 add_error(p_txn_ids => enums,
573 p_err_col => 'TRANSACTION_TYPE',
574 p_err_msg => fnd_message.get);
575
576 -- reset enums table
577 enums.delete;
578 -- Error out if easy completion /return for repetitive schedule
579 -- and the assembly is under lot control
580 UPDATE wip_move_txn_interface wmti
581 SET wmti.process_status = WIP_CONSTANTS.ERROR
582 WHERE wmti.group_id = g_group_id
583 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
584 AND wmti.process_status = WIP_CONSTANTS.RUNNING
585 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
586 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
587 WIP_CONSTANTS.RET_TXN)
588 AND EXISTS
589 (SELECT 'X'
590 FROM mtl_system_items msi
591 WHERE msi.inventory_item_id = wmti.primary_item_id
592 AND msi.organization_id = wmti.organization_id
593 AND msi.lot_control_code = WIP_CONSTANTS.LOT)
594 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
595
596 fnd_message.set_name('WIP', 'WIP_EZ_NO_REP_LOT_CONTROL2');
597 add_error(p_txn_ids => enums,
598 p_err_col => 'TRANSACTION_TYPE',
599 p_err_msg => fnd_message.get);
600
601 -- reset enums table
602 enums.delete;
603 -- Error out if easy completion /return for Discrete job and the assembly
604 -- is under lot control and there is no default completion lot defined
605 UPDATE wip_move_txn_interface wmti
606 SET wmti.process_status = WIP_CONSTANTS.ERROR
607 WHERE wmti.group_id = g_group_id
611 WIP_CONSTANTS.LOTBASED)
608 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
609 AND wmti.process_status = WIP_CONSTANTS.RUNNING
610 AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
612 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
613 WIP_CONSTANTS.RET_TXN)
614 AND EXISTS
615 (SELECT 'X'
616 FROM mtl_system_items msi
617 WHERE msi.inventory_item_id = wmti.primary_item_id
618 AND msi.organization_id = wmti.organization_id
619 AND msi.lot_control_code = WIP_CONSTANTS.LOT)
620 AND EXISTS
621 (SELECT 'X'
622 FROM wip_discrete_jobs wdj
623 WHERE wdj.organization_id = wmti.organization_id
624 AND wdj.wip_entity_id = wmti.wip_entity_id
625 AND wdj.lot_number IS NULL)
626 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
627
628 fnd_message.set_name('WIP', 'WIP_EZ_NO_JOB_LOT_DEFAULT2');
629 add_error(p_txn_ids => enums,
630 p_err_col => 'TRANSACTION_TYPE',
631 p_err_msg => fnd_message.get);
632
633 -- reset enums table
634 enums.delete;
635 -- Error out if easy completion to the new lot number and and either this
636 -- item or this item category requires "Lot Attributes".
637 UPDATE wip_move_txn_interface wmti
638 SET wmti.process_status = WIP_CONSTANTS.ERROR
639 WHERE wmti.group_id = g_group_id
640 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
641 AND wmti.process_status = WIP_CONSTANTS.RUNNING
642 AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
643 WIP_CONSTANTS.LOTBASED)
644 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
645 AND EXISTS -- lot control
646 (SELECT 'X'
647 FROM mtl_system_items msi
648 WHERE msi.inventory_item_id = wmti.primary_item_id
649 AND msi.organization_id = wmti.organization_id
650 AND msi.lot_control_code = WIP_CONSTANTS.LOT)
651 -- This is the first time to complete this assembly to this lot number
652 AND NOT EXISTS
653 (SELECT 'X'
654 FROM mtl_lot_numbers mln,
655 wip_discrete_jobs wdj
656 WHERE wdj.wip_entity_id = wmti.wip_entity_id
657 AND wdj.organization_id = wmti.organization_id
658 AND mln.inventory_item_id = wmti.primary_item_id
659 AND mln.organization_id = wmti.organization_id
660 AND mln.lot_number = wdj.lot_number)
661 -- This item or item category requires lot attributes
662 AND 2 = inv_lot_sel_attr.is_enabled(
663 'Lot Attributes', -- p_flex_name
664 wmti.organization_id, -- p_organization_id
665 wmti.primary_item_id) -- p_inventory_item_id
666 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
667
668 fnd_message.set_name('WIP', 'WIP_LOT_ATTR_NOT_ALLOW');
669 add_error(p_txn_ids => enums,
670 p_err_col => 'TRANSACTION_TYPE',
671 p_err_msg => fnd_message.get);
672
673 -- reset enums table
674 enums.delete;
675 -- Error out if easy completion to the new lot number and lot expiration date
676 -- was set to user-defined
677 UPDATE wip_move_txn_interface wmti
678 SET wmti.process_status = WIP_CONSTANTS.ERROR
679 WHERE wmti.group_id = g_group_id
680 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
681 AND wmti.process_status = WIP_CONSTANTS.RUNNING
682 AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
683 WIP_CONSTANTS.LOTBASED)
684 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
685 AND EXISTS -- lot control and expiration date is user-defined
686 (SELECT 'X'
687 FROM mtl_system_items msi
688 WHERE msi.inventory_item_id = wmti.primary_item_id
689 AND msi.organization_id = wmti.organization_id
690 AND msi.lot_control_code = WIP_CONSTANTS.LOT
691 AND msi.shelf_life_code = WIP_CONSTANTS.USER_DEFINED_EXP)
692 -- This is the first time to complete this assembly to this lot number
693 AND NOT EXISTS
694 (SELECT 'X'
695 FROM mtl_lot_numbers mln,
696 wip_discrete_jobs wdj
697 WHERE wdj.wip_entity_id = wmti.wip_entity_id
698 AND wdj.organization_id = wmti.organization_id
699 AND mln.inventory_item_id = wmti.primary_item_id
700 AND mln.organization_id = wmti.organization_id
701 AND mln.lot_number = wdj.lot_number)
702 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
703
704 fnd_message.set_name('WIP', 'WIP_USER_DEF_EXP_NOT_ALLOW');
705 add_error(p_txn_ids => enums,
706 p_err_col => 'TRANSACTION_TYPE',
707 p_err_msg => fnd_message.get);
708
709 END transaction_type;
710
711 -- validate transaction_date. Transaction date must be less than or equal
712 -- to SYSDATE, and greater than or equal to released date.
713 PROCEDURE transaction_date IS
714 BEGIN
715 -- reset enums table
716 enums.delete;
717 -- Error out if TRANSACTION_DATE is the future date
718 UPDATE wip_move_txn_interface wmti
719 SET wmti.process_status = WIP_CONSTANTS.ERROR
720 WHERE wmti.group_id = g_group_id
721 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
722 AND wmti.process_status = WIP_CONSTANTS.RUNNING
723 AND wmti.transaction_date > SYSDATE
727 add_error(p_txn_ids => enums,
724 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
725
726 fnd_message.set_name('WIP', 'WIP_NO_FORWARD_DATING');
728 p_err_col => 'TRANSACTION_DATE',
729 p_err_msg => fnd_message.get);
730
731 /* Fix for bug 5685099 : Validate if TRANSACTION_DATE falls in open accounting period. */
732 -- reset enums table
733 enums.delete;
734 -- Error out if TRANSACTION_DATE does not fall in open period
735 UPDATE wip_move_txn_interface wmti
736 SET wmti.process_status = WIP_CONSTANTS.ERROR
737 WHERE wmti.group_id = g_group_id
738 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
739 AND wmti.process_status = WIP_CONSTANTS.RUNNING
740 AND NOT EXISTS
741 (SELECT 'X'
742 FROM ORG_ACCT_PERIODS OAP
743 WHERE OAP.ORGANIZATION_ID = WMTI.ORGANIZATION_ID
744 AND OAP.PERIOD_CLOSE_DATE IS NULL
745 AND OAP.OPEN_FLAG = 'Y'
746 AND TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
747 WMTI.TRANSACTION_DATE, -- p_trxn_date
748 WMTI.ORGANIZATION_ID -- p_inv_org_id
749 ))
750 BETWEEN OAP.PERIOD_START_DATE AND OAP.SCHEDULE_CLOSE_DATE)
751 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
752
753 fnd_message.set_name('WIP', 'WIP_DATE_IN_OPEN_PERIOD');
754 add_error(p_txn_ids => enums,
755 p_err_col => 'TRANSACTION_DATE',
756 p_err_msg => fnd_message.get);
757
758 /* end fix for bug 5685099 */
759
760 -- reset enums table
761 enums.delete;
762 -- Error out if TRANSACTION_DATE is before released date
763 UPDATE wip_move_txn_interface wmti
764 SET wmti.process_status = WIP_CONSTANTS.ERROR
765 WHERE wmti.group_id = g_group_id
766 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
767 AND wmti.process_status = WIP_CONSTANTS.RUNNING
768 AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
769 AND EXISTS
770 (SELECT 'X'
771 FROM wip_repetitive_schedules wrs
772 WHERE wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
773 AND wrs.organization_id = wmti.organization_id
774 AND wrs.date_released > wmti.transaction_date))
775 OR
776 (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
777 WIP_CONSTANTS.LOTBASED)
778 AND EXISTS
779 (SELECT 'X'
780 FROM wip_discrete_jobs wdj
781 WHERE wdj.wip_entity_id = wmti.wip_entity_id
782 AND wdj.organization_id = wmti.organization_id
783 AND wdj.date_released > wmti.transaction_date)))
784 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
785
786 fnd_message.set_name('WIP', 'WIP_RELEASE_DATE');
787 add_error(p_txn_ids => enums,
788 p_err_col => 'TRANSACTION_DATE',
789 p_err_msg => fnd_message.get);
790
791 -- Derive ACCT_PERIOD_ID from TRANSACTION_DATE
792 UPDATE wip_move_txn_interface wmti
793 SET wmti.acct_period_id =
794 (SELECT oap.acct_period_id
795 FROM org_acct_periods oap
796 WHERE oap.organization_id = wmti.organization_id
797 -- modified the statement below for timezone project in J
798 AND TRUNC(inv_le_timezone_pub.get_le_day_for_inv_org(
799 wmti.transaction_date, -- p_trxn_date
800 wmti.organization_id -- p_inv_org_id
801 )) BETWEEN
802 oap.period_start_date AND oap.schedule_close_date)
803 WHERE wmti.group_id = g_group_id
804 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
805 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
806
807 -- reset enums table
808 enums.delete;
809 -- Error out if there is no open accout period for the TRANSACTION_DATE
810 -- specified or there is no WIP_PERIOD_BALANCES
811 UPDATE wip_move_txn_interface wmti
812 SET wmti.process_status = WIP_CONSTANTS.ERROR
813 WHERE wmti.group_id = g_group_id
814 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
815 AND wmti.process_status = WIP_CONSTANTS.RUNNING
816 AND (wmti.acct_period_id IS NULL
817 OR
818 NOT EXISTS
819 (SELECT 'X'
820 FROM wip_period_balances wpb
821 WHERE wpb.acct_period_id = wmti.acct_period_id
822 AND wpb.wip_entity_id = wmti.wip_entity_id
823 AND wpb.organization_id = wmti.organization_id
824 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
825 WIP_CONSTANTS.LOTBASED)
826 OR (wmti.entity_type = WIP_CONSTANTS.REPETITIVE
827 AND repetitive_schedule_id IN
828 (SELECT wrs.repetitive_schedule_id
829 FROM wip_repetitive_schedules wrs
830 WHERE wrs.wip_entity_id = wmti.wip_entity_id
831 AND wrs.organization_id = wmti.organization_id
832 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
833 WIP_CONSTANTS.COMP_CHRG))))))
834 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
835
836 fnd_message.set_name('WIP', 'WIP_NO_BALANCE');
837 add_error(p_txn_ids => enums,
838 p_err_col => 'TRANSACTION_DATE',
839 p_err_msg => fnd_message.get);
840
844 -- For easy return transaction, from operation must be the last operation.
841 END transaction_date;
842
843 -- validate fm_operation_seq_num. From operation must be a valid operation.
845 -- Callers always need to pass this value except for Return transactions.
846 -- If callers do not provide this info and it is return transaction, just
847 -- default fm_operation to last_op
848 PROCEDURE fm_operation IS
849 l_last_op NUMBER;
850 BEGIN
851 -- Set FM_OPERATION_SEQ_NUM to last_operation if TRANSACTION_TYPE is
852 -- EZ Return and FM_OPERATION_SEQ_NUM is null
853 UPDATE wip_move_txn_interface wmti
854 SET wmti.fm_operation_seq_num =
855 (SELECT wo.operation_seq_num
856 FROM wip_operations wo
857 WHERE wo.wip_entity_id = wmti.wip_entity_id
858 AND wo.organization_id = wmti.organization_id
859 AND NVL(wo.repetitive_schedule_id, -1) =
860 NVL(wmti.repetitive_schedule_id, -1)
861 AND wo.next_operation_seq_num IS NULL)
862 WHERE wmti.group_id = g_group_id
863 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
864 AND wmti.process_status = WIP_CONSTANTS.RUNNING
865 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
866 AND wmti.fm_operation_seq_num IS NULL;
867
868 -- reset enums table
869 enums.delete;
870 -- Error out if FM_OPERATION_SEQ_NUM is null or FM_OPERATION_SEQ_NUM
871 -- is invalid
872 UPDATE wip_move_txn_interface wmti
873 SET wmti.process_status = WIP_CONSTANTS.ERROR
874 WHERE wmti.group_id = g_group_id
875 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
876 AND wmti.process_status = WIP_CONSTANTS.RUNNING
877 AND (wmti.fm_operation_seq_num IS NULL
878 OR
879 (NOT EXISTS
880 (SELECT 'X'
881 FROM wip_operations wo
882 WHERE wo.wip_entity_id = wmti.wip_entity_id
883 AND wo.organization_id = wmti.organization_id
884 AND wo.operation_seq_num = wmti.fm_operation_seq_num
885 AND NVL(wo.repetitive_schedule_id, -1) =
886 NVL(wmti.repetitive_schedule_id, -1))))
887 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
888
889 fnd_message.set_name('WIP', 'WIP_INVALID_OPERATION');
890 add_error(p_txn_ids => enums,
891 p_err_col => 'FM_OPERATION_SEQ_NUM',
892 p_err_msg => fnd_message.get);
893
894 -- reset enums table
895 enums.delete;
896 -- Error out if TRANSACTION_TYPE is EZ Return and FM_OPERATION_SEQ_NUM
897 -- is not equal to the last operation.
898 UPDATE wip_move_txn_interface wmti
899 SET wmti.process_status = WIP_CONSTANTS.ERROR
900 WHERE wmti.group_id = g_group_id
901 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
902 AND wmti.process_status = WIP_CONSTANTS.RUNNING
903 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
904 AND wmti.fm_operation_seq_num <>
905 (SELECT wo.operation_seq_num
906 FROM wip_operations wo
907 WHERE wo.wip_entity_id = wmti.wip_entity_id
908 AND wo.organization_id = wmti.organization_id
909 AND NVL(wo.repetitive_schedule_id, -1) =
910 NVL(wmti.repetitive_schedule_id, -1)
911 AND wo.next_operation_seq_num IS NULL)
912 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
913
914 fnd_message.set_name('WIP', 'WIP_EZ_FM_LAST_OP');
915 add_error(p_txn_ids => enums,
916 p_err_col => 'FM_OPERATION_SEQ_NUM',
917 p_err_msg => fnd_message.get);
918 END fm_operation;
919
920 -- validate fm_intraoperation_step_type. From step must be valid.
921 -- If easy return transaction, from step must be "To move". If easy complete,
922 -- from step cannot be "To move" when from operation is the last operation.
923 -- You cannot move out of an operaion/step that has a No Move shop floor status
924 -- attached. If callers do not provide this info and it is return transaction,
925 -- just default fm_step to to move
926 PROCEDURE fm_step IS
927 BEGIN
928 -- Set FM_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
929 -- EZ Return and FM_INTRAOPERATION_STEP_TYPE is null
930 UPDATE wip_move_txn_interface wmti
931 SET wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
932 WHERE wmti.group_id = g_group_id
933 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
934 AND wmti.process_status = WIP_CONSTANTS.RUNNING
935 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
936 AND wmti.fm_intraoperation_step_type IS NULL;
937
938 -- reset enums table
939 enums.delete;
940 -- Error out if FM_INTRAOPERATION_STEP_TYPE is null or invalid
941 UPDATE wip_move_txn_interface wmti
942 SET wmti.process_status = WIP_CONSTANTS.ERROR
943 WHERE wmti.group_id = g_group_id
944 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
945 AND wmti.process_status = WIP_CONSTANTS.RUNNING
946 AND (wmti.fm_intraoperation_step_type IS NULL
947 OR
948 (NOT EXISTS
949 (SELECT 'X'
950 FROM wip_valid_intraoperation_steps wvis,
951 wip_operations wo
952 WHERE wvis.organization_id = wmti.organization_id
953 AND wvis.step_lookup_type = wmti.fm_intraoperation_step_type
954 AND wo.organization_id = wmti.organization_id
958 NVL(wmti.repetitive_schedule_id, -1)
955 AND wo.wip_entity_id = wmti.wip_entity_id
956 AND wo.operation_seq_num = wmti.fm_operation_seq_num
957 AND NVL(wo.repetitive_schedule_id, -1) =
959 AND ((wvis.record_creator = 'USER' OR
960 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
961 OR
962 (wvis.record_creator = 'SYSTEM' AND
963 wo.next_operation_seq_num IS NULL)))))
964 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
965
966 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
967 fnd_message.set_token('ENTITY', 'FM_INTRAOPERATION_STEP_TYPE');
968 add_error(p_txn_ids => enums,
969 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
970 p_err_msg => fnd_message.get);
971
972 -- reset enums table
973 enums.delete;
974 -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
975 -- no move shop floor status attached
976 UPDATE wip_move_txn_interface wmti
977 SET wmti.process_status = WIP_CONSTANTS.ERROR
978 WHERE wmti.group_id = g_group_id
979 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
980 AND wmti.process_status = WIP_CONSTANTS.RUNNING
981 AND EXISTS
982 (SELECT 'X'
983 FROM wip_shop_floor_status_codes wsc,
984 wip_shop_floor_statuses ws
985 WHERE wsc.organization_id = wmti.organization_id
986 AND ws.organization_id = wmti.organization_id
987 AND ws.wip_entity_id = wmti.wip_entity_id
988 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
989 AND ws.operation_seq_num = wmti.fm_operation_seq_num
990 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
991 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
992 AND wsc.status_move_flag = WIP_CONSTANTS.NO
993 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
994 AND (wmti.source_code IS NULL OR
995 wmti.source_code <> 'RCV' OR
996 (wmti.source_code = 'RCV' AND
997 NOT EXISTS
998 (SELECT 'X'
999 FROM wip_parameters wp
1000 WHERE wp.organization_id = wmti.organization_id
1001 AND wp.osp_shop_floor_status =
1002 wsc.shop_floor_status_code))))
1003 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1004
1005 fnd_message.set_name('WIP', 'WIP_STATUS_NO_TXN1');
1006 add_error(p_txn_ids => enums,
1007 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1008 p_err_msg => fnd_message.get);
1009
1010 -- reset enums table
1011 enums.delete;
1012 -- Error out if users try to perform easy completion from Tomove of the
1013 -- last operation
1014 UPDATE wip_move_txn_interface wmti
1015 SET wmti.process_status = WIP_CONSTANTS.ERROR
1016 WHERE wmti.group_id = g_group_id
1017 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1018 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1019 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1020 AND wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1021 AND wmti.fm_operation_seq_num =
1022 (SELECT wo.operation_seq_num
1023 FROM wip_operations wo
1024 WHERE wo.wip_entity_id = wmti.wip_entity_id
1025 AND wo.organization_id = wmti.organization_id
1026 AND NVL(wo.repetitive_schedule_id, -1) =
1027 NVL(wmti.repetitive_schedule_id, -1)
1028 AND wo.next_operation_seq_num IS NULL)
1029 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1030
1031 fnd_message.set_name('WIP', 'WIP_EZ_NO_CMP_LAST_OP2');
1032 add_error(p_txn_ids => enums,
1033 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1034 p_err_msg => fnd_message.get);
1035
1036 -- reset enums table
1037 enums.delete;
1038 -- Error out if TRANSACTION_TYPE is EZ Return and
1039 -- FM_INTRAOPERATION_STEP_TYPE not equal to Tomove
1040 UPDATE wip_move_txn_interface wmti
1041 SET wmti.process_status = WIP_CONSTANTS.ERROR
1042 WHERE wmti.group_id = g_group_id
1043 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1044 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1045 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
1046 AND wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
1047 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1048
1049 fnd_message.set_name('WIP', 'WIP_EZ_FM_LAST_STEP');
1050 add_error(p_txn_ids => enums,
1051 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1052 p_err_msg => fnd_message.get);
1053 END fm_step;
1054
1055 -- validate to_operation_seq_num. To operation must be a valid operation.
1056 -- For easy complete transaction, To operation must be the last operation.
1057 -- Callers always need to pass this value except for Easy complete
1058 -- transactions. If callers do not provide this info and it is complete
1059 -- transaction, just default to_operation to last_op
1060 PROCEDURE to_operation IS
1061 BEGIN
1062 -- Set TO_OPERATION_SEQ_NUM to last_operation if TRANSACTION_TYPE is
1063 -- EZ Completion and TO_OPERATION_SEQ_NUM is null
1064 UPDATE wip_move_txn_interface wmti
1065 SET wmti.to_operation_seq_num =
1066 (SELECT wo.operation_seq_num
1070 AND NVL(wo.repetitive_schedule_id, -1) =
1067 FROM wip_operations wo
1068 WHERE wo.wip_entity_id = wmti.wip_entity_id
1069 AND wo.organization_id = wmti.organization_id
1071 NVL(wmti.repetitive_schedule_id, -1)
1072 AND wo.next_operation_seq_num IS NULL)
1073 WHERE wmti.group_id = g_group_id
1074 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1075 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1076 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1077 AND wmti.to_operation_seq_num IS NULL;
1078
1079 /*Bug 4421485->Even for plain moves we will derive to_operation as
1080 next count point operation */
1081 UPDATE wip_move_txn_interface wmti
1082 SET wmti.to_operation_seq_num =
1083 (SELECT MIN(wo.operation_seq_num)
1084 FROM wip_operations wo
1085 WHERE wo.organization_id = wmti.organization_id
1086 AND wo.wip_entity_id = wmti.wip_entity_id
1087 AND NVL(wo.repetitive_schedule_id, -1) =
1088 NVL(wmti.repetitive_schedule_id, -1)
1089 AND wo.operation_seq_num > wmti.fm_operation_seq_num
1090 AND wo.count_point_type = 1)
1091 WHERE wmti.group_id = g_group_id
1092 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1093 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1094 AND wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
1095 AND wmti.to_operation_seq_num IS NULL;
1096
1097 -- reset enums table
1098 enums.delete;
1099 -- Error out if TO_OPERATION_SEQ_NUM is null or TO_OPERATION_SEQ_NUM
1100 -- is invalid
1101 UPDATE wip_move_txn_interface wmti
1102 SET wmti.process_status = WIP_CONSTANTS.ERROR
1103 WHERE wmti.group_id = g_group_id
1104 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1105 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1106 AND (wmti.to_operation_seq_num IS NULL
1107 OR
1108 (NOT EXISTS
1109 (SELECT 'X'
1110 FROM wip_operations wo
1111 WHERE wo.wip_entity_id = wmti.wip_entity_id
1112 AND wo.organization_id = wmti.organization_id
1113 AND wo.operation_seq_num = wmti.to_operation_seq_num
1114 AND NVL(wo.repetitive_schedule_id, -1) =
1115 NVL(wmti.repetitive_schedule_id, -1))))
1116 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1117
1118 fnd_message.set_name('WIP', 'WIP_INVALID_OPERATION');
1119 add_error(p_txn_ids => enums,
1120 p_err_col => 'TO_OPERATION_SEQ_NUM',
1121 p_err_msg => fnd_message.get);
1122
1123 -- reset enums table
1124 enums.delete;
1125 -- Error out if TRANSACTION_TYPE is EZ Ccmplete and TO_OPERATION_SEQ_NUM
1126 -- is not equal to the last operation.
1127 UPDATE wip_move_txn_interface wmti
1128 SET wmti.process_status = WIP_CONSTANTS.ERROR
1129 WHERE wmti.group_id = g_group_id
1130 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1131 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1132 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1133 AND wmti.to_operation_seq_num <>
1134 (SELECT wo.operation_seq_num
1135 FROM wip_operations wo
1136 WHERE wo.wip_entity_id = wmti.wip_entity_id
1137 AND wo.organization_id = wmti.organization_id
1138 AND NVL(wo.repetitive_schedule_id, -1) =
1139 NVL(wmti.repetitive_schedule_id, -1)
1140 AND wo.next_operation_seq_num IS NULL)
1141 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1142
1143 fnd_message.set_name('WIP', 'WIP_EZ_TO_LAST_OP');
1144 add_error(p_txn_ids => enums,
1145 p_err_col => 'TO_OPERATION_SEQ_NUM',
1146 p_err_msg => fnd_message.get);
1147 END to_operation;
1148
1149 -- validate to_intraoperation_step_type. To step must be valid.
1150 -- If easy complete transaction, to step must be "To move". If easy return,
1151 -- to step cannot be "To move" when to operation is the last operation.
1152 -- If callers do not provide this info and it is complete transaction,
1153 -- just default to_step to to move
1154 PROCEDURE to_step IS
1155 BEGIN
1156 -- Set TO_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
1157 -- EZ Complete and TO_INTRAOPERATION_STEP_TYPE is null
1158 UPDATE wip_move_txn_interface wmti
1159 /*Bug Bug 4421485*/
1160 SET wmti.to_intraoperation_step_type =
1161 DECODE(wmti.transaction_type,
1162 WIP_CONSTANTS.COMP_TXN,WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.QUEUE)
1163 WHERE wmti.group_id = g_group_id
1164 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1165 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1166 /*Bug Bug 4421485*/
1167 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
1168 WIP_CONSTANTS.MOVE_TXN)
1169 AND wmti.to_intraoperation_step_type IS NULL;
1170
1171 -- reset enums table
1172 enums.delete;
1173 -- Error out if TO_INTRAOPERATION_STEP_TYPE is null or invalid
1174 UPDATE wip_move_txn_interface wmti
1175 SET wmti.process_status = WIP_CONSTANTS.ERROR
1176 WHERE wmti.group_id = g_group_id
1177 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1178 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1179 AND (wmti.to_intraoperation_step_type IS NULL
1180 OR
1181 (NOT EXISTS
1182 (SELECT 'X'
1186 AND wvis.step_lookup_type = wmti.to_intraoperation_step_type
1183 FROM wip_valid_intraoperation_steps wvis,
1184 wip_operations wo
1185 WHERE wvis.organization_id = wmti.organization_id
1187 AND wo.organization_id = wmti.organization_id
1188 AND wo.wip_entity_id = wmti.wip_entity_id
1189 AND wo.operation_seq_num = wmti.to_operation_seq_num
1190 AND NVL(wo.repetitive_schedule_id, -1) =
1191 NVL(wmti.repetitive_schedule_id, -1)
1192 -- Fixed bug 5059521. Since OSFM build routing as it goes, we cannot rely on
1193 -- wo.next_operation_seq_num IS NULL to determine that it is the last
1194 -- operation or not.
1195 AND (((wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
1196 WIP_CONSTANTS.REPETITIVE)
1197 OR
1198 (wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
1199 wmti.transaction_type = WIP_CONSTANTS.COMP_TXN))
1200 AND
1201 ((wvis.record_creator = 'USER' OR
1202 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
1203 OR
1204 (wvis.record_creator = 'SYSTEM' AND
1205 wo.next_operation_seq_num IS NULL)))
1206 OR
1207 (wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
1208 wmti.transaction_type <> WIP_CONSTANTS.COMP_TXN AND
1209 (wvis.record_creator = 'USER' OR
1210 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE))))))
1211
1212 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1213
1214 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1215 fnd_message.set_token('ENTITY', 'TO_INTRAOPERATION_STEP_TYPE');
1216 add_error(p_txn_ids => enums,
1217 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1218 p_err_msg => fnd_message.get);
1219
1220 -- reset enums table
1221 enums.delete;
1222 -- Error out if users try to move to the same operation and step as the
1223 -- FM_OPERATION_SEQ_NUM and FM_INTRAOPERATION_STEP_TYPE
1224 UPDATE wip_move_txn_interface wmti
1225 SET wmti.process_status = WIP_CONSTANTS.ERROR
1226 WHERE wmti.group_id = g_group_id
1227 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1228 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1229 AND wmti.fm_operation_seq_num = wmti.to_operation_seq_num
1230 AND wmti.fm_intraoperation_step_type = wmti.to_intraoperation_step_type
1231 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1232
1233 fnd_message.set_name('WIP', 'WIP_SAME_OP_AND_STEP');
1234 add_error(p_txn_ids => enums,
1235 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1236 p_err_msg => fnd_message.get);
1237
1238 -- reset enums table
1239 enums.delete;
1240 -- Error out if TRANSACTION_TYPE is EZ Complete and
1241 -- TO_INTRAOPERATION_STEP_TYPE not equal to Tomove
1242 UPDATE wip_move_txn_interface wmti
1243 SET wmti.process_status = WIP_CONSTANTS.ERROR
1244 WHERE wmti.group_id = g_group_id
1245 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1246 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1247 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1248 AND wmti.to_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
1249 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1250
1251 fnd_message.set_name('WIP', 'WIP_EZ_TO_LAST_STEP');
1252 add_error(p_txn_ids => enums,
1253 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1254 p_err_msg => fnd_message.get);
1255
1256 -- reset enums table
1257 enums.delete;
1258 -- Error out if user try to easy complete job/schedule that has No Move shop
1259 -- floor status attached to Tomove of the last operation
1260 UPDATE wip_move_txn_interface wmti
1261 SET wmti.process_status = WIP_CONSTANTS.ERROR
1262 WHERE wmti.group_id = g_group_id
1263 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1264 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1265 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1266 AND EXISTS
1267 (SELECT 'X'
1268 FROM wip_shop_floor_status_codes wsc,
1269 wip_shop_floor_statuses ws
1270 WHERE wsc.organization_id = wmti.organization_id
1271 AND ws.organization_id = wmti.organization_id
1272 AND ws.wip_entity_id = wmti.wip_entity_id
1273 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
1274 AND ws.operation_seq_num = wmti.to_operation_seq_num
1275 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1276 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
1277 AND wsc.status_move_flag = WIP_CONSTANTS.NO
1278 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
1279 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1280
1281 fnd_message.set_name('WIP', 'WIP_STATUS_NO_TXN2');
1282 add_error(p_txn_ids => enums,
1283 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1284 p_err_msg => fnd_message.get);
1285
1286 -- reset enums table
1287 enums.delete;
1288 -- Error out if wip_parameter do not allow move over no_move shop floor
1289 -- status, and there are no_move status in between
1290 UPDATE wip_move_txn_interface wmti
1294 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1291 SET wmti.process_status = WIP_CONSTANTS.ERROR
1292 WHERE wmti.group_id = g_group_id
1293 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1295 AND wip_sf_status.count_no_move_statuses(
1296 wmti.organization_id, -- p_org_id
1297 wmti.wip_entity_id, -- p_wip_id
1298 wmti.line_id, -- p_line_id
1299 wmti.repetitive_schedule_id, -- p_sched_id
1300 wmti.fm_operation_seq_num, -- p_fm_op
1301 wmti.fm_intraoperation_step_type, -- p_fm_step
1302 wmti.to_operation_seq_num, -- p_to_op
1303 wmti.to_intraoperation_step_type, -- p_to_step
1304 -- Fixed bug 2121222
1305 wmti.source_code) > 0 -- p_source_code
1306 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1307
1308 fnd_message.set_name ('WIP', 'WIP_NO_MOVE_SF_STATUS_BETWEEN');
1309 add_error(p_txn_ids => enums,
1310 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1311 p_err_msg => fnd_message.get);
1312 END to_step;
1313
1314 -- transaction_quantity must be positive
1315 PROCEDURE transaction_qty IS
1316 BEGIN
1317 -- reset enums table
1318 enums.delete;
1319
1320 -- Error out if TRANSACTION_QUANTITY is negative or zero
1321 UPDATE wip_move_txn_interface wmti
1322 SET wmti.process_status = WIP_CONSTANTS.ERROR
1323 WHERE wmti.group_id = g_group_id
1324 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1325 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1326 AND wmti.transaction_quantity <= 0
1327 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1328
1329 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1330 fnd_message.set_token('ENTITY1', 'TRANSACTION_QUANTITY');
1331 fnd_message.set_token('ENTITY2', 'zero');
1332 add_error(p_txn_ids => enums,
1333 p_err_col => 'TRANSACTION_QUANTITY',
1334 p_err_msg => fnd_message.get);
1335
1336 END transaction_qty;
1337
1338
1339 -- transaction_uom must be defined
1340 PROCEDURE transaction_uom IS
1341 BEGIN
1342 -- reset enums table
1343 enums.delete;
1344 -- Error out if TRANSACTION_UOM is invalid
1345 UPDATE wip_move_txn_interface wmti
1346 SET wmti.process_status = WIP_CONSTANTS.ERROR
1347 WHERE wmti.group_id = g_group_id
1348 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1349 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1350 AND NOT EXISTS
1351 (SELECT 'X'
1352 FROM mtl_item_uoms_view miuv
1353 WHERE miuv.organization_id = wmti.organization_id
1354 AND miuv.inventory_item_id = wmti.primary_item_id
1355 AND miuv.uom_code = wmti.transaction_uom)
1356 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1357
1358 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1359 fnd_message.set_token('ENTITY', 'TRANSACTION_UOM');
1360 add_error(p_txn_ids => enums,
1361 p_err_col => 'TRANSACTION_UOM',
1362 p_err_msg => fnd_message.get);
1363
1364 END transaction_uom;
1365
1366 -- validate overcompletion_transaction_qty. This is an optional info.
1367 -- The caller need to provide this only for overmove/overcompletion txns.
1368 -- However, we do not allow overreturn, and over move for backward move.
1369 -- We also not allow overmove/overcomplete from scrap or reject step.
1370 -- This value cannot be zero or negative either.
1371 PROCEDURE ocpl_txn_qty IS
1372 BEGIN
1373 -- reset enums table
1374 enums.delete;
1375
1376 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is negative or zero
1377 UPDATE wip_move_txn_interface wmti
1378 SET wmti.process_status = WIP_CONSTANTS.ERROR
1379 WHERE wmti.group_id = g_group_id
1380 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1381 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1382 AND wmti.overcompletion_transaction_qty IS NOT NULL
1383 AND wmti.overcompletion_transaction_qty <= 0
1384 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1385
1386 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1387 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_TRANSACTION_QTY');
1388 fnd_message.set_token('ENTITY2', 'zero');
1389 add_error(p_txn_ids => enums,
1390 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1391 p_err_msg => fnd_message.get);
1392
1393 -- reset enums table
1394 enums.delete;
1395 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is greater than
1396 -- TRANSACTION_QUANTITY
1397 UPDATE wip_move_txn_interface wmti
1398 SET wmti.process_status = WIP_CONSTANTS.ERROR
1399 WHERE wmti.group_id = g_group_id
1400 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1401 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1402 AND wmti.overcompletion_transaction_qty IS NOT NULL
1403 AND wmti.overcompletion_transaction_qty > wmti.transaction_quantity
1404 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1405
1406 fnd_message.set_name('MFG', 'MFG_GREATER_OR_EQUAL');
1407 fnd_message.set_token('ENTITY1', 'TRANSACTION_QUANTITY');
1408 fnd_message.set_token('ENTITY2', 'OVERCOMPLETION_TRANSACTION_QTY');
1409 add_error(p_txn_ids => enums,
1410 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1411 p_err_msg => fnd_message.get);
1412
1413 -- reset enums table
1414 enums.delete;
1415 -- Error out if user try to do over Return
1419 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1416 UPDATE wip_move_txn_interface wmti
1417 SET wmti.process_status = WIP_CONSTANTS.ERROR
1418 WHERE wmti.group_id = g_group_id
1420 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1421 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
1422 AND wmti.overcompletion_transaction_qty IS NOT NULL
1423 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1424
1425 fnd_message.set_name('WIP', 'WIP_NO_OC_RET');
1426 add_error(p_txn_ids => enums,
1427 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1428 p_err_msg => fnd_message.get);
1429
1430 -- reset enums table
1431 enums.delete;
1432 -- Error out if user try to do over Return from Scrap/Return from Reject
1433 UPDATE wip_move_txn_interface wmti
1434 SET wmti.process_status = WIP_CONSTANTS.ERROR
1435 WHERE wmti.group_id = g_group_id
1436 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1437 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1438 AND wmti.fm_intraoperation_step_type IN (WIP_CONSTANTS.SCRAP,
1439 WIP_CONSTANTS.REJECT)
1440 AND wmti.overcompletion_transaction_qty IS NOT NULL
1441 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1442
1443 fnd_message.set_name('WIP', 'WIP_NO_OC_SCR_REJ');
1444 add_error(p_txn_ids => enums,
1445 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1446 p_err_msg => fnd_message.get);
1447
1448 -- reset enums table
1449 enums.delete;
1450 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is specified for backward
1451 -- move txns
1452 UPDATE wip_move_txn_interface wmti
1453 SET wmti.process_status = WIP_CONSTANTS.ERROR
1454 WHERE wmti.group_id = g_group_id
1455 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1456 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1457 AND (wmti.to_operation_seq_num < wmti.fm_operation_seq_num OR
1458 (wmti.to_operation_seq_num = wmti.fm_operation_seq_num AND
1459 wmti.to_intraoperation_step_type <
1460 wmti.fm_intraoperation_step_type))
1461 AND wmti.overcompletion_transaction_qty IS NOT NULL
1462 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1463
1464 fnd_message.set_name('WIP', 'WIP_NO_OC_REV_MOVE');
1465 add_error(p_txn_ids => enums,
1466 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1467 p_err_msg => fnd_message.get);
1468
1469 END ocpl_txn_qty;
1470
1471 -- validate transaction_id against the one in WIP_MOVE_TRANSACTIONS, and
1472 -- WIP_MOVE_TXN_INTERFACE. This value need to be unique.
1473 PROCEDURE transaction_id IS
1474 l_errMsg VARCHAR2(240);
1475 BEGIN
1476 -- Generate TRANSACTION_ID if user does not provide this value
1477 UPDATE wip_move_txn_interface wmti
1478 SET wmti.transaction_id = wip_transactions_s.nextval
1479 WHERE wmti.group_id = g_group_id
1480 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1481 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1482 AND wmti.transaction_id IS NULL;
1483
1484 -- Set Error Message
1485 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1486 fnd_message.set_token('ENTITY', 'TRANSACTION_ID');
1487 l_errMsg := substrb(fnd_message.get, 1, 240);
1488
1489 INSERT INTO wip_txn_interface_errors(
1490 transaction_id,
1491 error_message,
1492 error_column,
1493 last_update_date,
1494 last_updated_by,
1495 creation_date,
1496 created_by,
1497 last_update_login,
1498 request_id,
1499 program_application_id,
1500 program_id,
1501 program_update_date
1502 )
1503 SELECT wmti1.transaction_id, -- transaction_id
1504 l_errMsg, -- error_message
1505 'TRANSACTION_ID', -- error_column
1506 SYSDATE, -- last_update_date
1507 NVL(wmti1.last_updated_by, -1),
1508 SYSDATE, -- creation_date
1509 NVL(wmti1.created_by, -1),
1510 wmti1.last_update_login,
1511 wmti1.request_id,
1512 wmti1.program_application_id,
1513 wmti1.program_id,
1514 wmti1.program_update_date
1515 FROM wip_move_txn_interface wmti1
1516 WHERE wmti1.group_id = g_group_id
1517 AND wmti1.process_phase = WIP_CONSTANTS.MOVE_VAL
1518 AND wmti1.process_status = WIP_CONSTANTS.RUNNING
1519 AND (EXISTS
1520 (SELECT 'X'
1521 FROM wip_move_transactions wmt
1522 WHERE wmt.transaction_id = wmti1.transaction_id)
1523 OR
1524 (1 <>
1525 (SELECT count(*)
1526 FROM wip_move_txn_interface wmti2
1527 WHERE wmti2.transaction_id = wmti1.transaction_id)));
1528
1529 END transaction_id;
1530
1531 -- derive primary_quantity from transaction_quantity and transaction_uom.
1532 -- you cannot easy return more than available quantity and organization do
1533 -- not allow negative balance
1534 PROCEDURE primary_qty IS
1535
1536 CURSOR c_availQty IS
1537 SELECT wmti.transaction_id txn_id,
1538 wmti.organization_id org_id,
1539 wmti.primary_item_id item_id,
1540 wmti.primary_quantity primary_qty,
1541 DECODE(msik.serial_number_control_code,
1545 DECODE(msik.lot_control_code,
1542 WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
1543 WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
1544 fnd_api.g_false) is_ser_ctrl,
1546 WIP_CONSTANTS.LOT, fnd_api.g_true,
1547 fnd_api.g_false) is_lot_ctrl,
1548 DECODE(msik.revision_qty_control_code,
1549 WIP_CONSTANTS.REV, fnd_api.g_true,
1550 fnd_api.g_false) is_rev_ctrl,
1551 DECODE(msik.revision_qty_control_code, -- revision
1552 WIP_CONSTANTS.REV, NVL(wdj.bom_revision,
1553 bom_revisions.get_item_revision_fn
1554 ('EXCLUDE_OPEN_HOLD', -- eco_status
1555 'ALL', -- examine_type
1556 wmti.organization_id, -- org_id
1557 wmti.primary_item_id, -- item_id
1558 wmti.transaction_date -- rev_date
1559 )),
1560 NULL) revision, -- revision
1561 wdj.lot_number lot,
1562 wmti.transaction_date txn_date,
1563 wdj.completion_subinventory subinv,
1564 wdj.completion_locator_id locID,
1565 mp.negative_inv_receipt_code negative_allow,
1566 msik.concatenated_segments assembly_name
1567 FROM wip_discrete_jobs wdj,
1568 mtl_system_items_kfv msik,
1569 mtl_parameters mp,
1570 wip_move_txn_interface wmti
1571 WHERE wdj.primary_item_id = msik.inventory_item_id
1572 AND wdj.organization_id = msik.organization_id
1573 AND wdj.organization_id = mp.organization_id
1574 AND wmti.wip_entity_id = wdj.wip_entity_id
1575 AND wmti.organization_id = wdj.organization_id
1576 AND wmti.entity_type <> WIP_CONSTANTS.REPETITIVE
1577 AND wmti.group_id = g_group_id
1578 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1579 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1580 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
1581
1582
1583 CURSOR c_repAvailQty IS
1584 SELECT wmti.transaction_id txn_id,
1585 wmti.organization_id org_id,
1586 wmti.primary_item_id item_id,
1587 wmti.primary_quantity primary_qty,
1588 DECODE(msik.serial_number_control_code,
1589 WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
1590 WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
1591 fnd_api.g_false) is_ser_ctrl,
1592 DECODE(msik.lot_control_code,
1593 WIP_CONSTANTS.LOT, fnd_api.g_true,
1594 fnd_api.g_false) is_lot_ctrl,
1595 DECODE(msik.revision_qty_control_code,
1596 WIP_CONSTANTS.REV, fnd_api.g_true,
1597 fnd_api.g_false) is_rev_ctrl,
1598 DECODE(msik.revision_qty_control_code, -- revision
1599 WIP_CONSTANTS.REV, NVL(wrs.bom_revision,
1600 bom_revisions.get_item_revision_fn
1601 ('EXCLUDE_OPEN_HOLD', -- eco_status
1602 'ALL', -- examine_type
1603 wmti.organization_id, -- org_id
1604 wmti.primary_item_id, -- item_id
1605 wmti.transaction_date -- rev_date
1606 )),
1607 NULL) revision, -- revision
1608 NULL lot,
1609 wmti.transaction_date txn_date,
1610 wri.completion_subinventory subinv,
1611 wri.completion_locator_id locID,
1612 mp.negative_inv_receipt_code negative_allow,
1613 msik.concatenated_segments assembly_name
1614 FROM wip_repetitive_schedules wrs,
1615 wip_repetitive_items wri,
1616 mtl_system_items_kfv msik,
1617 mtl_parameters mp,
1618 wip_move_txn_interface wmti
1619 WHERE wmti.primary_item_id = msik.inventory_item_id
1620 AND wmti.organization_id = msik.organization_id
1621 AND wmti.organization_id = mp.organization_id
1622 AND wrs.wip_entity_id = wmti.wip_entity_id
1623 AND wrs.organization_id = wmti.organization_id
1624 AND wrs.line_id = wmti.line_id
1625 AND wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
1626 AND wri.organization_id = wmti.organization_id
1627 AND wri.wip_entity_id = wmti.wip_entity_id
1628 AND wri.line_id = wmti.line_id
1629 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
1630 AND wmti.group_id = g_group_id
1631 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1632 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1633 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
1634
1635 l_availQty c_availQty%ROWTYPE;
1636 l_repAvailQty c_repAvailQty%ROWTYPE;
1637 l_returnStatus VARCHAR2(1);
1638 l_qoh NUMBER;
1639 l_rqoh NUMBER;
1640 l_qr NUMBER;
1641 l_qs NUMBER;
1642 l_att NUMBER;
1643 l_atr NUMBER;
1644 l_errMsg VARCHAR2(240);
1645 l_msg_count NUMBER;
1646 l_msg_data VARCHAR2(2000);
1647 BEGIN
1648 -- Derive PRIMARY_QUANTITY from TRANSACTION_QUANTITY and TRANSACTION_UOM
1649 -- if PRIMARY_QUANTITY is null
1650
1651 /** Bug fix 5000113. primary_quantity should be updated in sync with
1652 * transaction_quantity, and not just when primary_quantity is null.
1653 */
1654
1655 UPDATE wip_move_txn_interface wmti
1656 SET wmti.primary_quantity =
1657 (SELECT ROUND(wmti.transaction_quantity * mucv.conversion_rate,
1661 AND mucv.inventory_item_id = wmti.primary_item_id
1658 WIP_CONSTANTS.INV_MAX_PRECISION)
1659 FROM mtl_uom_conversions_view mucv
1660 WHERE mucv.organization_id = wmti.organization_id
1662 AND mucv.uom_code = wmti.transaction_uom)
1663 WHERE wmti.group_id = g_group_id
1664 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1665 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
1666 --AND wmti.primary_quantity IS NULL;
1667
1668 /* End of bug fix 5000113.
1669
1670 -- Set Error Message
1671 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1672 fnd_message.set_token('ENTITY1', 'PRIMARY_QUANTITY');
1673 fnd_message.set_token('ENTITY2', 'zero');
1674 l_errMsg := substrb(fnd_message.get, 1, 240);
1675
1676 -- Error out if PRIMARY_QUANTITY is zero
1677 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
1678 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
1679 -- continue validating other values.
1680 INSERT INTO wip_txn_interface_errors(
1681 transaction_id,
1682 error_message,
1683 error_column,
1684 last_update_date,
1685 last_updated_by,
1686 creation_date,
1687 created_by,
1688 last_update_login,
1689 request_id,
1690 program_application_id,
1691 program_id,
1692 program_update_date
1693 )
1694 SELECT wmti.transaction_id, -- transaction_id
1695 l_errMsg, -- error_message
1696 'PRIMARY_QUANTITY', -- error_column
1697 SYSDATE, -- last_update_date
1698 NVL(wmti.last_updated_by, -1),
1699 SYSDATE, -- creation_date
1700 NVL(wmti.created_by, -1),
1701 wmti.last_update_login,
1702 wmti.request_id,
1703 wmti.program_application_id,
1704 wmti.program_id,
1705 wmti.program_update_date
1706 FROM wip_move_txn_interface wmti
1707 WHERE wmti.group_id = g_group_id
1708 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1709 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1710 AND wmti.primary_quantity = 0;
1711
1712 -- Set Error Message
1713 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
1714 fnd_message.set_token('ENTITY1', 'PRIMARY_QUANTITY');
1715 fnd_message.set_token('ENTITY2', 'TRANSACTION_QUANTITY');
1716 l_errMsg := substrb(fnd_message.get, 1, 240);
1717
1718 -- Error out if PRIMARY_QUANTITY is not consistent with TRANSACTION_QUANTITY
1719 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
1720 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
1721 -- continue validating other values.
1722 INSERT INTO wip_txn_interface_errors(
1723 transaction_id,
1724 error_message,
1725 error_column,
1726 last_update_date,
1727 last_updated_by,
1728 creation_date,
1729 created_by,
1730 last_update_login,
1731 request_id,
1732 program_application_id,
1733 program_id,
1734 program_update_date
1735 )
1736 SELECT wmti.transaction_id, -- transaction_id
1737 l_errMsg, -- error_message
1738 'PRIMARY_QUANTITY', -- error_column
1739 SYSDATE, -- last_update_date
1740 NVL(wmti.last_updated_by, -1),
1741 SYSDATE, -- creation_date
1742 NVL(wmti.created_by, -1),
1743 wmti.last_update_login,
1744 wmti.request_id,
1745 wmti.program_application_id,
1746 wmti.program_id,
1747 wmti.program_update_date
1748 FROM wip_move_txn_interface wmti,
1749 mtl_uom_conversions_view mucv
1750 WHERE mucv.organization_id = wmti.organization_id
1751 AND mucv.inventory_item_id = wmti.primary_item_id
1752 AND mucv.uom_code = wmti.transaction_uom
1753 AND wmti.group_id = g_group_id
1754 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1755 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1756 -- Fixed bug 4900010. Round both transaction_quantity and
1757 -- primary_quantity before making comparison.
1758 AND ROUND(wmti.transaction_quantity * mucv.conversion_rate,
1759 WIP_CONSTANTS.INV_MAX_PRECISION) <>
1760 ROUND(wmti.primary_quantity, WIP_CONSTANTS.INV_MAX_PRECISION);
1761
1762 -- Set Error Message
1763 /* Fix for Bug#4192541. Removed following check as this is only warning
1764 condition
1765 */
1766
1767 /*
1768 fnd_message.set_name('WIP', 'WIP_MIN_XFER_QTY');
1769 l_errMsg := substrb(fnd_message.get, 1, 240);
1770
1771 -- Error out if PRIMARY_QUANTITY less than MININUM_TRANSFER_QUANTITY
1772 -- defined at FM_OPERATION_SEQ_NUM and transactions are not Scrap/Reject
1773 INSERT INTO wip_txn_interface_errors(
1774 transaction_id,
1775 error_message,
1776 error_column,
1777 last_update_date,
1778 last_updated_by,
1779 creation_date,
1780 created_by,
1781 last_update_login,
1782 request_id,
1783 program_application_id,
1784 program_id,
1785 program_update_date
1786 )
1790 SYSDATE, -- last_update_date
1787 SELECT wmti.transaction_id, -- transaction_id
1788 l_errMsg, -- error_message
1789 'PRIMARY_QUANTITY', -- error_column
1791 NVL(wmti.last_updated_by, -1),
1792 SYSDATE, -- creation_date
1793 NVL(wmti.created_by, -1),
1794 wmti.last_update_login,
1795 wmti.request_id,
1796 wmti.program_application_id,
1797 wmti.program_id,
1798 wmti.program_update_date
1799 FROM wip_move_txn_interface wmti,
1800 wip_operations wo
1801 WHERE wo.organization_id = wmti.organization_id
1802 AND wo.wip_entity_id = wmti.wip_entity_id
1803 AND NVL(wo.repetitive_schedule_id, -1) =
1804 NVL(wmti.repetitive_schedule_id, -1)
1805 AND wo.operation_seq_num = wmti.fm_operation_seq_num
1806 AND wmti.fm_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
1807 WIP_CONSTANTS.REJECT)
1808 AND wmti.to_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
1809 WIP_CONSTANTS.REJECT)
1810 AND wmti.group_id = g_group_id
1811 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1812 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1813 AND wo.minimum_transfer_quantity > wmti.primary_quantity;
1814
1815
1816 */
1817 -- Set Error Message
1818 fnd_message.set_name ('INV', 'INV_NO_NEG_BALANCES');
1819 l_errMsg := substrb(fnd_message.get, 1, 240);
1820
1821 -- User cannot do easy return more than available quantity if
1822 -- organization do not allow negative balance. (Discrete/OSFM)
1823 FOR l_availQty IN c_availQty
1824 LOOP
1825 inv_quantity_tree_pub.query_quantities(
1826 p_api_version_number => 1.0,
1827 p_init_msg_lst => 'T',
1828 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
1829 p_organization_id => l_availQty.org_id,
1830 p_inventory_item_id => l_availQty.item_id,
1831 p_tree_mode => inv_quantity_tree_pvt.g_loose_only_mode,
1832 p_is_revision_control => fnd_api.to_boolean(l_availQty.is_rev_ctrl),
1833 p_is_lot_control => fnd_api.to_boolean(l_availQty.is_lot_ctrl),
1834 p_is_serial_control => fnd_api.to_boolean(l_availQty.is_ser_ctrl),
1835 p_demand_source_type_id => 5, -- WIP
1836 p_revision => l_availQty.revision,
1837 p_lot_number => l_availQty.lot,
1838 p_lot_expiration_date => l_availQty.txn_date,
1839 p_subinventory_code => l_availQty.subinv,
1840 p_locator_id => l_availQty.locID,
1841 x_return_status => l_returnStatus,
1842 x_msg_count => l_msg_count,
1843 x_msg_data => l_msg_data,
1844 x_qoh => l_qoh,
1845 x_rqoh => l_rqoh,
1846 x_qr => l_qr,
1847 x_qs => l_qs,
1848 x_att => l_att,
1849 x_atr => l_atr);
1850
1851 IF(l_returnStatus <> 'S')THEN
1852 add_error(p_txn_id => l_availQty.txn_id,
1853 p_err_col => 'PRIMARY_QUANTITY',
1854 p_err_msg => l_msg_data);
1855 ELSE
1856 IF(l_availQty.negative_allow = WIP_CONSTANTS.NO AND
1857 l_att < l_availQty.primary_qty) THEN
1858 add_error(p_txn_id => l_availQty.txn_id,
1859 p_err_col => 'PRIMARY_QUANTITY',
1860 p_err_msg => l_availQty.assembly_name||':'||l_errMsg);
1861 END IF;
1862 END IF;
1863 END LOOP; -- Only for EZ Return transactions (Discrete/OSFM)
1864
1865 -- User cannot do easy return more than available quantity if
1866 -- organization do not allow negative balance (Repetitive)
1867 FOR l_repAvailQty IN c_repAvailQty
1868 LOOP
1869 inv_quantity_tree_pub.query_quantities(
1870 p_api_version_number => 1.0,
1871 p_init_msg_lst => 'T',
1872 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
1873 p_organization_id => l_repAvailQty.org_id,
1874 p_inventory_item_id => l_repAvailQty.item_id,
1875 p_tree_mode => inv_quantity_tree_pvt.g_loose_only_mode,
1876 p_is_revision_control => fnd_api.to_boolean(l_repAvailQty.is_rev_ctrl),
1877 p_is_lot_control => fnd_api.to_boolean(l_repAvailQty.is_lot_ctrl),
1878 p_is_serial_control => fnd_api.to_boolean(l_repAvailQty.is_ser_ctrl),
1879 p_demand_source_type_id => 5, -- WIP
1880 p_revision => l_repAvailQty.revision,
1881 p_lot_number => l_repAvailQty.lot,
1882 p_lot_expiration_date => l_repAvailQty.txn_date,
1883 p_subinventory_code => l_repAvailQty.subinv,
1884 p_locator_id => l_repAvailQty.locID,
1885 x_return_status => l_returnStatus,
1886 x_msg_count => l_msg_count,
1887 x_msg_data => l_msg_data,
1888 x_qoh => l_qoh,
1889 x_rqoh => l_rqoh,
1890 x_qr => l_qr,
1891 x_qs => l_qs,
1892 x_att => l_att,
1893 x_atr => l_atr);
1894
1895 IF(l_returnStatus <> 'S')THEN
1896 add_error(p_txn_id => l_repAvailQty.txn_id,
1900 IF(l_repAvailQty.negative_allow = WIP_CONSTANTS.NO AND
1897 p_err_col => 'PRIMARY_QUANTITY',
1898 p_err_msg => l_msg_data);
1899 ELSE
1901 l_att < l_repAvailQty.primary_qty) THEN
1902 add_error(p_txn_id => l_repAvailQty.txn_id,
1903 p_err_col => 'PRIMARY_QUANTITY',
1904 p_err_msg => l_repAvailQty.assembly_name||':'||l_errMsg);
1905 END IF;
1906 END IF;
1907 END LOOP; -- Only for EZ Return transactions(Repetitive)
1908 END primary_qty;
1909
1910 -- derive primary_uom from primary_item_id
1911 PROCEDURE primary_uom IS
1912 l_errMsg VARCHAR2(240);
1913 BEGIN
1914 -- Derive PRIMARY_UOM from PRIMARY_ITEM_ID provided if PRIMARY_UOM is null
1915 UPDATE wip_move_txn_interface wmti
1916 SET wmti.primary_uom =
1917 (SELECT msi.primary_uom_code
1918 FROM mtl_system_items msi
1919 WHERE msi.organization_id = wmti.organization_id
1920 AND msi.inventory_item_id = wmti.primary_item_id)
1921 WHERE wmti.group_id = g_group_id
1922 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1923 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1924 AND wmti.primary_uom IS NULL;
1925
1926 -- Set Error Message
1927 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
1928 fnd_message.set_token('ENTITY1', 'PRIMARY_UOM');
1929 fnd_message.set_token('ENTITY2', 'PRIMARY_ITEM_ID');
1930 l_errMsg := substrb(fnd_message.get, 1, 240);
1931
1932 -- If caller provide PRIMARY_UOM, it must be consistent with
1933 -- primary_item_id provided
1934 INSERT INTO wip_txn_interface_errors(
1935 transaction_id,
1936 error_message,
1937 error_column,
1938 last_update_date,
1939 last_updated_by,
1940 creation_date,
1941 created_by,
1942 last_update_login,
1943 request_id,
1944 program_application_id,
1945 program_id,
1946 program_update_date
1947 )
1948 SELECT wmti.transaction_id, -- transaction_id
1949 l_errMsg, -- error_message
1950 'PRIMARY_UOM', -- error_column
1951 SYSDATE, -- last_update_date
1952 NVL(wmti.last_updated_by, -1),
1953 SYSDATE, -- creation_date
1954 NVL(wmti.created_by, -1),
1955 wmti.last_update_login,
1956 wmti.request_id,
1957 wmti.program_application_id,
1958 wmti.program_id,
1959 wmti.program_update_date
1960 FROM wip_move_txn_interface wmti,
1961 mtl_system_items msi
1962 WHERE msi.organization_id = wmti.organization_id
1963 AND msi.inventory_item_id = wmti.primary_item_id
1964 AND wmti.group_id = g_group_id
1965 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1966 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1967 AND msi.primary_uom_code <> wmti.primary_uom;
1968
1969 END primary_uom;
1970
1971 -- derive overcomplete_primary_quantity from overcomplete_transaction_quantity
1972 -- and transaction_uom provided.
1973 PROCEDURE ocpl_primary_qty IS
1974 l_errMsg VARCHAR2(240);
1975 BEGIN
1976 -- Derive OVERCOMPLETE_PRIMARY_QUANTITY from
1977 -- OVERCOMPLETE_TRANSACTION_QUANTITY and TRANSACTION_UOM provided.
1978
1979 /** Bug fix 5000113. overcompletion_primary_qty should be updated in sync with
1980 * transaction_quantity, and not just when overcompletion_quantity is null.
1981 */
1982
1983 UPDATE wip_move_txn_interface wmti
1984 SET wmti.overcompletion_primary_qty =
1985 (SELECT ROUND(wmti.overcompletion_transaction_qty *
1986 mucv.conversion_rate, WIP_CONSTANTS.INV_MAX_PRECISION)
1987 FROM mtl_uom_conversions_view mucv
1988 WHERE mucv.organization_id = wmti.organization_id
1989 AND mucv.inventory_item_id = wmti.primary_item_id
1990 AND mucv.uom_code = wmti.transaction_uom)
1991 WHERE wmti.group_id = g_group_id
1992 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1993 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1994 AND wmti.overcompletion_transaction_qty IS NOT NULL;
1995 --AND wmti.overcompletion_primary_qty IS NULL;
1996
1997 -- End of bug fix 5000113.
1998
1999 -- Set Error Message
2000 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
2001 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_PRIMARY_QTY');
2002 fnd_message.set_token('ENTITY2', 'zero');
2003 l_errMsg := substrb(fnd_message.get, 1, 240);
2004
2005 -- Error out if OVERCOMPLETION_PRIMARY_QTY is zero
2006 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
2007 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
2008 -- continue validating other values.
2009 INSERT INTO wip_txn_interface_errors(
2010 transaction_id,
2011 error_message,
2012 error_column,
2013 last_update_date,
2014 last_updated_by,
2015 creation_date,
2016 created_by,
2017 last_update_login,
2018 request_id,
2019 program_application_id,
2020 program_id,
2021 program_update_date
2022 )
2023 SELECT wmti.transaction_id, -- transaction_id
2024 l_errMsg, -- error_message
2025 'OVERCOMPLETION_PRIMARY_QTY', -- error_column
2029 NVL(wmti.created_by, -1),
2026 SYSDATE, -- last_update_date
2027 NVL(wmti.last_updated_by, -1),
2028 SYSDATE, -- creation_date
2030 wmti.last_update_login,
2031 wmti.request_id,
2032 wmti.program_application_id,
2033 wmti.program_id,
2034 wmti.program_update_date
2035 FROM wip_move_txn_interface wmti
2036 WHERE wmti.group_id = g_group_id
2037 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2038 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2039 AND wmti.overcompletion_primary_qty = 0;
2040
2041 -- Set Error Message
2042 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2043 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_PRIMARY_QTY');
2044 fnd_message.set_token('ENTITY2', 'OVERCOMPLETION_TRANSACTION_QTY');
2045 l_errMsg := substrb(fnd_message.get, 1, 240);
2046
2047 -- If caller provide this info, it must be consistent with
2048 -- overcompletion_transaction_qty provided
2049 INSERT INTO wip_txn_interface_errors(
2050 transaction_id,
2051 error_message,
2052 error_column,
2053 last_update_date,
2054 last_updated_by,
2055 creation_date,
2056 created_by,
2057 last_update_login,
2058 request_id,
2059 program_application_id,
2060 program_id,
2061 program_update_date
2062 )
2063 SELECT wmti.transaction_id, -- transaction_id
2064 l_errMsg, -- error_message
2065 'OVERCOMPLETION_PRIMARY_QTY', -- error_column
2066 SYSDATE, -- last_update_date
2067 NVL(wmti.last_updated_by, -1),
2068 SYSDATE, -- creation_date
2069 NVL(wmti.created_by, -1),
2070 wmti.last_update_login,
2071 wmti.request_id,
2072 wmti.program_application_id,
2073 wmti.program_id,
2074 wmti.program_update_date
2075 FROM wip_move_txn_interface wmti,
2076 mtl_uom_conversions_view mucv
2077 WHERE mucv.organization_id = wmti.organization_id
2078 AND mucv.inventory_item_id = wmti.primary_item_id
2079 AND mucv.uom_code = wmti.transaction_uom
2080 AND wmti.group_id = g_group_id
2081 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2082 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2083 AND wmti.overcompletion_transaction_qty IS NOT NULL
2084 -- Fixed bug 4900010. Round both transaction_quantity and
2085 -- primary_quantity before making comparison.
2086 AND ROUND(wmti.overcompletion_transaction_qty * mucv.conversion_rate,
2087 WIP_CONSTANTS.INV_MAX_PRECISION) <>
2088 ROUND(wmti.overcompletion_primary_qty,
2089 WIP_CONSTANTS.INV_MAX_PRECISION);
2090 END ocpl_primary_qty;
2091
2092 -- This value must be null. The move processor will be the one who insert
2093 -- child record and link it with parent record
2094 PROCEDURE ocpl_txn_id IS
2095 l_errMsg VARCHAR2(240);
2096 BEGIN
2097 -- Set Error Message
2098 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
2099 fnd_message.set_token('ENTITY', 'OVERCOMPLETION_TRANSACTION_ID');
2100 l_errMsg := substrb(fnd_message.get, 1, 240);
2101
2102 -- This value must be null because New Move Processor will be the one
2103 -- who insert child record and populate this value
2104 INSERT INTO wip_txn_interface_errors(
2105 transaction_id,
2106 error_message,
2107 error_column,
2108 last_update_date,
2109 last_updated_by,
2110 creation_date,
2111 created_by,
2112 last_update_login,
2113 request_id,
2114 program_application_id,
2115 program_id,
2116 program_update_date
2117 )
2118 SELECT wmti.transaction_id, -- transaction_id
2119 l_errMsg, -- error_message
2120 'OVERCOMPLETION_TRANSACTION_ID', -- error_column
2121 SYSDATE, -- last_update_date
2122 NVL(wmti.last_updated_by, -1),
2123 SYSDATE, -- creation_date
2124 NVL(wmti.created_by, -1),
2125 wmti.last_update_login,
2126 wmti.request_id,
2127 wmti.program_application_id,
2128 wmti.program_id,
2129 wmti.program_update_date
2130 FROM wip_move_txn_interface wmti
2131 WHERE wmti.group_id = g_group_id
2132 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2133 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2134 AND wmti.overcompletion_transaction_id IS NOT NULL;
2135
2136 END ocpl_txn_id;
2137
2138 -- This is an optional info. However, if the caller provided some values,
2139 -- it must be valid. If the caller pass reason_name, we will derive the
2140 -- reason_id. If the caller pass both, both value must be consistent to
2141 -- each other.
2142 PROCEDURE reason_id IS
2143 l_errMsg VARCHAR2(240);
2144 BEGIN
2145 -- Derive REASON_ID from REASON_NAME provided
2146 UPDATE wip_move_txn_interface wmti
2147 SET wmti.reason_id =
2148 (SELECT mtr.reason_id
2149 FROM mtl_transaction_reasons mtr
2150 WHERE mtr.reason_name = wmti.reason_name
2151 AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE)
2155 AND wmti.reason_id IS NULL
2152 WHERE wmti.group_id = g_group_id
2153 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2154 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2156 AND wmti.reason_name IS NOT NULL;
2157
2158 -- Set Error Message
2159 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2160 fnd_message.set_token('ENTITY1', 'REASON_ID');
2161 fnd_message.set_token('ENTITY2', 'REASON_NAME');
2162 l_errMsg := substrb(fnd_message.get, 1, 240);
2163
2164 -- If caller provide REASON_ID, it must be consistent with
2165 -- REASON_NAME provided
2166 INSERT INTO wip_txn_interface_errors(
2167 transaction_id,
2168 error_message,
2169 error_column,
2170 last_update_date,
2171 last_updated_by,
2172 creation_date,
2173 created_by,
2174 last_update_login,
2175 request_id,
2176 program_application_id,
2177 program_id,
2178 program_update_date
2179 )
2180 SELECT wmti.transaction_id, -- transaction_id
2181 l_errMsg, -- error_message
2182 'REASON_ID/NAME', -- error_column
2183 SYSDATE, -- last_update_date
2184 NVL(wmti.last_updated_by, -1),
2185 SYSDATE, -- creation_date
2186 NVL(wmti.created_by, -1),
2187 wmti.last_update_login,
2188 wmti.request_id,
2189 wmti.program_application_id,
2190 wmti.program_id,
2191 wmti.program_update_date
2192 FROM wip_move_txn_interface wmti
2193 WHERE wmti.group_id = g_group_id
2194 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2195 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2196 AND (wmti.reason_id IS NOT NULL OR wmti.reason_name IS NOT NULL)
2197 AND NOT EXISTS
2198 (SELECT 'X'
2199 FROM mtl_transaction_reasons mtr
2200 WHERE mtr.reason_id = NVL(wmti.reason_id, mtr.reason_id)
2201 AND mtr.reason_name = NVL(wmti.reason_name, mtr.reason_name)
2202 AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE);
2203
2204 END reason_id;
2205
2206 -- validate scrap_account_id. This value can be either required or optional
2207 -- info for the discrete and repetitive scrap transaction. It depends on the
2208 -- value setup in WIP_PARAMETERS. However it is always an optional info for
2209 -- OSFM txns. If the caller provided this info, it must be valid account_id
2210 PROCEDURE scrap_account_id IS
2211 l_scrap_flag NUMBER;
2212 l_errMsg VARCHAR2(240);
2213 BEGIN
2214 -- Set Error Message
2215 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
2216 fnd_message.set_token('ENTITY', 'SCRAP_ACCOUNT_ID');
2217 l_errMsg := substrb(fnd_message.get, 1, 240);
2218
2219 INSERT INTO wip_txn_interface_errors(
2220 transaction_id,
2221 error_message,
2222 error_column,
2223 last_update_date,
2224 last_updated_by,
2225 creation_date,
2226 created_by,
2227 last_update_login,
2228 request_id,
2229 program_application_id,
2230 program_id,
2231 program_update_date
2232 )
2233 SELECT wmti.transaction_id, -- transaction_id
2234 l_errMsg, -- error_message
2235 'SCRAP_ACCOUNT_ID', -- error_column
2236 SYSDATE, -- last_update_date
2237 NVL(wmti.last_updated_by, -1),
2238 SYSDATE, -- creation_date
2239 NVL(wmti.created_by, -1),
2240 wmti.last_update_login,
2241 wmti.request_id,
2242 wmti.program_application_id,
2243 wmti.program_id,
2244 wmti.program_update_date
2245 FROM wip_move_txn_interface wmti,
2246 wip_parameters wp
2247 WHERE wp.organization_id = wmti.organization_id
2248 AND wmti.group_id = g_group_id
2249 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2250 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2251 AND (wmti.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP OR
2252 wmti.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP)
2253 AND ((wmti.scrap_account_id IS NULL
2254 AND wp.mandatory_scrap_flag = WIP_CONSTANTS.YES
2255 AND wmti.entity_type NOT IN(WIP_CONSTANTS.LOTBASED,
2256 WIP_CONSTANTS.CLOSED_OSFM))
2257 OR
2258 (wmti.scrap_account_id IS NOT NULL AND
2259 NOT EXISTS
2260 (SELECT 'X'
2261 FROM hr_organization_information hoi,
2262 gl_sets_of_books gsob,
2263 gl_code_combinations gcc
2264 WHERE gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
2265 and gsob.set_of_books_id =
2266 to_number(decode(rtrim(translate(
2267 hoi.org_information1,'0123456789',' ')),
2268 null, hoi.org_information1,
2269 -99999))
2270 and (hoi.org_information_context || '') =
2271 'Accounting Information'
2272 AND hoi.organization_id = wmti.organization_id
2273 AND gcc.code_combination_id = wmti.scrap_account_id
2277 AND TRUNC(wmti.transaction_date) BETWEEN
2274 AND gcc.detail_posting_allowed_flag = 'Y'
2275 AND gcc.summary_flag = 'N'
2276 and gcc.enabled_flag = 'Y'
2278 NVL(gcc.start_date_active,
2279 TRUNC(wmti.transaction_date))
2280 AND NVL(gcc.end_date_active,
2281 TRUNC(wmti.transaction_date)))));
2282
2283 END scrap_account_id;
2284
2285 -- validate last_updated_by against fnd_user table. The caller have an option
2286 -- to provide either last_updated_by or last_updated_by_name. If the caller
2287 -- pass last_updated_by, the id need to be valid. If the caller pass
2288 -- last_updated_by_name, we will derive the ID. If the caller pass both
2289 -- both value must be consistent to each other.
2290 PROCEDURE last_updated_by IS
2291 l_errMsg VARCHAR2(240);
2292 BEGIN
2293 -- Derive LAST_UPDATED_BY if user provided only LAST_UPDATED_BY_NAME
2294 UPDATE wip_move_txn_interface wmti
2295 SET wmti.last_updated_by =
2296 (SELECT fu.user_id
2297 FROM fnd_user fu
2298 WHERE fu.user_name = wmti.last_updated_by_name
2299 AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
2300 WHERE wmti.group_id = g_group_id
2301 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2302 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2303 AND wmti.last_updated_by IS NULL
2304 AND wmti.last_updated_by_name IS NOT NULL;
2305
2306 -- Set Error Message
2307 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2308 fnd_message.set_token('ENTITY1', 'LAST_UPDATED_BY');
2309 fnd_message.set_token('ENTITY2', 'LAST_UPDATED_BY_NAME');
2310 l_errMsg := substrb(fnd_message.get, 1, 240);
2311
2312 -- Error out if LAST_UPDATED_BY is not consistent with LAST_UPDATED_BY_NAME
2313 INSERT INTO wip_txn_interface_errors(
2314 transaction_id,
2315 error_message,
2316 error_column,
2317 last_update_date,
2318 last_updated_by,
2319 creation_date,
2320 created_by,
2321 last_update_login,
2322 request_id,
2323 program_application_id,
2324 program_id,
2325 program_update_date
2326 )
2327 SELECT wmti.transaction_id, -- transaction_id
2328 l_errMsg, -- error_message
2329 'LAST_UPDATED_BY/BY_NAME', -- error_column
2330 SYSDATE, -- last_update_date
2331 NVL(wmti.last_updated_by, -1),
2332 SYSDATE, -- creation_date
2333 NVL(wmti.created_by, -1),
2334 wmti.last_update_login,
2335 wmti.request_id,
2336 wmti.program_application_id,
2337 wmti.program_id,
2338 wmti.program_update_date
2339 FROM wip_move_txn_interface wmti
2340 WHERE wmti.group_id = g_group_id
2341 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2342 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2343 AND wmti.last_updated_by IS NULL; -- cannot derive LAST_UPDATED_BY
2344
2345 END last_updated_by;
2346
2347 -- validate created_by against fnd_user table. The caller have an option
2348 -- to provide either created_by or created_by_name. If the caller
2349 -- pass created_by, the id need to be valid. If the caller pass
2350 -- created_by_name, we will derive the ID. If the caller pass both
2351 -- both value must be consistent to each other.
2352 PROCEDURE created_by IS
2353 l_errMsg VARCHAR2(240);
2354 BEGIN
2355 -- Derive CREATED_BY if user provided only CREATED_BY_NAME
2356 UPDATE wip_move_txn_interface wmti
2357 SET wmti.created_by =
2358 (SELECT fu.user_id
2359 FROM fnd_user fu
2360 WHERE fu.user_name = wmti.created_by_name
2361 AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
2362 WHERE wmti.group_id = g_group_id
2363 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2364 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2365 AND wmti.created_by IS NULL
2366 AND wmti.created_by_name IS NOT NULL;
2367
2368 -- Set Error Message
2369 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2370 fnd_message.set_token('ENTITY1', 'CREATED_BY');
2371 fnd_message.set_token('ENTITY2', 'CREATED_BY_NAME');
2372 l_errMsg := substrb(fnd_message.get, 1, 240);
2373
2374 -- Error out if CREATED_BY is not consistent with CREATED_BY_NAME
2375 INSERT INTO wip_txn_interface_errors(
2376 transaction_id,
2377 error_message,
2378 error_column,
2379 last_update_date,
2380 last_updated_by,
2381 creation_date,
2382 created_by,
2383 last_update_login,
2384 request_id,
2385 program_application_id,
2386 program_id,
2387 program_update_date
2388 )
2389 SELECT wmti.transaction_id, -- transaction_id
2390 l_errMsg, -- error_message
2391 'CREATED_BY/BY_NAME', -- error_column
2392 SYSDATE, -- last_update_date
2393 NVL(wmti.last_updated_by, -1),
2394 SYSDATE, -- creation_date
2395 NVL(wmti.created_by, -1),
2396 wmti.last_update_login,
2397 wmti.request_id,
2398 wmti.program_application_id,
2402 WHERE wmti.group_id = g_group_id
2399 wmti.program_id,
2400 wmti.program_update_date
2401 FROM wip_move_txn_interface wmti
2403 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2404 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2405 AND ((wmti.created_by IS NULL) -- cannot derive LAST_UPDATED_BY
2406 OR
2407 (NOT EXISTS
2408 (SELECT 'X'
2409 FROM fnd_user fu
2410 WHERE fu.user_name = NVL(wmti.created_by_name,
2411 fu.user_name)
2412 AND fu.user_id = wmti.created_by
2413 AND SYSDATE BETWEEN fu.start_date AND
2414 NVL(fu.end_date, SYSDATE))));
2415 END created_by;
2416
2417 -- This procedure is used to validate osp transactions. User cannot move into
2418 -- a queue of OSP operation unless the department associated to that operation
2419 -- has a location for PO_RECEIVE. For PO_MOVE the department associated with
2420 -- the next operation after to_op must have location. If to_op is the last op
2421 -- , the department associated to that operation must have location.
2422 -- The user must be a valid employee to perform osp transactions.
2423 PROCEDURE osp_validation IS
2424 l_errMsg VARCHAR2(240);
2425 BEGIN
2426 -- Set Error Message
2427 fnd_message.set_name('WIP', 'WIP_PO_MOVE_LOCATION');
2428 l_errMsg := substrb(fnd_message.get, 1, 240);
2429
2430 -- Error out if user try to move into a queue of OSP operation and the
2431 -- department associated to that operation does not have a location
2432 -- for PO_RECEIVE. For PO_MOVE the department associated with
2433 -- the next operation after to_op must have location. If to_op is the last op
2434 -- the department associated to that operation must have location.
2435 INSERT INTO wip_txn_interface_errors(
2436 transaction_id,
2437 error_message,
2438 error_column,
2439 last_update_date,
2440 last_updated_by,
2441 creation_date,
2442 created_by,
2443 last_update_login,
2444 request_id,
2445 program_application_id,
2446 program_id,
2447 program_update_date
2448 )
2449 SELECT wmti.transaction_id, -- transaction_id
2450 l_errMsg, -- error_message
2451 'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
2452 SYSDATE, -- last_update_date
2453 NVL(wmti.last_updated_by, -1),
2454 SYSDATE, -- creation_date
2455 NVL(wmti.created_by, -1),
2456 wmti.last_update_login,
2457 wmti.request_id,
2458 wmti.program_application_id,
2459 wmti.program_id,
2460 wmti.program_update_date
2461 FROM wip_move_txn_interface wmti
2462 WHERE wmti.group_id = g_group_id
2463 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2464 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2465 AND EXISTS
2466 (SELECT 'X'
2467 FROM bom_departments bd,
2468 wip_operation_resources wor,
2469 wip_operations wo1,
2470 wip_operations wo2
2471 WHERE wor.organization_id = wmti.organization_id
2472 AND wor.wip_entity_id = wmti.wip_entity_id
2473 AND wor.operation_seq_num = wmti.to_operation_seq_num
2474 AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
2475 AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
2476 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2477 WIP_CONSTANTS.LOTBASED)
2478 OR
2479 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2480 wor.repetitive_schedule_id IN
2481 (SELECT wrs.repetitive_schedule_id
2482 FROM wip_repetitive_schedules wrs
2483 WHERE wrs.wip_entity_id = wmti.wip_entity_id
2484 AND wrs.organization_id = wmti.organization_id
2485 AND wrs.line_id = wmti.line_id
2486 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
2487 WIP_CONSTANTS.COMP_CHRG))))
2488 AND wo1.organization_id = wor.organization_id
2489 AND wo1.wip_entity_id = wor.wip_entity_id
2490 AND NVL(wo1.repetitive_schedule_id,-1) =
2491 NVL(wor.repetitive_schedule_id,-1)
2492 AND wo1.operation_seq_num = wor.operation_seq_num
2493 AND wo2.organization_id = wo1.organization_id
2494 AND wo2.wip_entity_id = wo1.wip_entity_id
2495 AND NVL(wo2.repetitive_schedule_id,-1) =
2496 NVL(wo1.repetitive_schedule_id,-1)
2497 AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
2498 wo2.operation_seq_num = wor.operation_seq_num)
2499 OR
2500 (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
2501 ((wo1.next_operation_seq_num IS NOT NULL AND
2502 wo1.next_operation_seq_num = wo2.operation_seq_num)
2503 OR
2504 (wo1.next_operation_seq_num IS NULL AND
2505 wo2.operation_seq_num = wor.operation_seq_num))))
2506 AND bd.organization_id = wmti.organization_id
2510 -- Set Error Message
2507 AND wo2.department_id = bd.department_id
2508 AND bd.location_id IS NULL);
2509
2511 fnd_message.set_name('WIP', 'WIP_VALID_EMPLOYEE');
2512 l_errMsg := substrb(fnd_message.get, 1, 240);
2513
2514 -- Error out if the user who try to do OSP transaction is not an employee
2515 INSERT INTO wip_txn_interface_errors(
2516 transaction_id,
2517 error_message,
2518 error_column,
2519 last_update_date,
2520 last_updated_by,
2521 creation_date,
2522 created_by,
2523 last_update_login,
2524 request_id,
2525 program_application_id,
2526 program_id,
2527 program_update_date
2528 )
2529 SELECT wmti.transaction_id, -- transaction_id
2530 l_errMsg, -- error_message
2531 'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
2532 SYSDATE, -- last_update_date
2533 NVL(wmti.last_updated_by, -1),
2534 SYSDATE, -- creation_date
2535 NVL(wmti.created_by, -1),
2536 wmti.last_update_login,
2537 wmti.request_id,
2538 wmti.program_application_id,
2539 wmti.program_id,
2540 wmti.program_update_date
2541 FROM wip_move_txn_interface wmti
2542 WHERE wmti.group_id = g_group_id
2543 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2544 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2545 AND EXISTS
2546 (SELECT 'Outside processing resources exist'
2547 FROM wip_operation_resources wor
2548 WHERE wor.organization_id = wmti.organization_id
2549 AND wor.wip_entity_id = wmti.wip_entity_id
2550 AND wor.operation_seq_num = wmti.to_operation_seq_num
2551 AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
2552 AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
2553 AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
2554 WIP_CONSTANTS.PO_MOVE)
2555 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2556 WIP_CONSTANTS.LOTBASED)
2557 OR
2558 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2559 wor.repetitive_schedule_id IN
2560 (SELECT wrs.repetitive_schedule_id
2561 FROM wip_repetitive_schedules wrs
2562 WHERE wrs.organization_id = wmti.organization_id
2563 AND wrs.wip_entity_id = wmti.wip_entity_id
2564 AND wrs.line_id = wmti.line_id
2565 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
2566 WIP_CONSTANTS.COMP_CHRG)))))
2567 AND NOT EXISTS
2568 (SELECT 'Current user is an employee'
2569 FROM fnd_user fu,
2570 per_people_f ppf
2571 WHERE fu.user_id = wmti.created_by
2572 AND fu.employee_id = ppf.person_id);
2573
2574 END osp_validation;
2575
2576 -- validate serial related information. This validation is only useful if
2577 -- user try to do background serialized txns.
2578 PROCEDURE serial_validation IS
2579 BEGIN
2580 -- reset enums table
2581 enums.delete;
2582 -- Users cannot move cross 'Queue' of serialization start op. User need to
2583 -- move 2 step. The first time move to Queue of serialization start op, then
2584 -- serial move. For backward move, do serial move first.
2585 UPDATE wip_move_txn_interface wmti
2586 SET wmti.process_status = WIP_CONSTANTS.ERROR
2587 WHERE wmti.group_id = g_group_id
2588 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2589 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2590 AND EXISTS -- serialized job
2591 (SELECT 'X'
2592 FROM wip_discrete_jobs wdj
2593 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2594 AND wdj.serialization_start_op IS NOT NULL
2595 AND (-- Forward move
2596 (wmti.fm_operation_seq_num < wdj.serialization_start_op AND
2597 (wmti.to_operation_seq_num > wdj.serialization_start_op
2598 OR
2599 (wmti.to_operation_seq_num = wdj.serialization_start_op AND
2600 wmti.to_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))
2601 OR
2602 -- Backward move
2603 (wmti.to_operation_seq_num < wdj.serialization_start_op AND
2604 (wmti.fm_operation_seq_num > wdj.serialization_start_op
2605 OR
2606 (wmti.fm_operation_seq_num = wdj.serialization_start_op AND
2607 wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))))
2608 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2609
2610 fnd_message.set_name('WIP', 'WIP_MOVE_CROSS_START_OP');
2611 add_error(p_txn_ids => enums,
2612 p_err_col => 'FM/TO_STEP, FM/TO_OP',
2613 p_err_msg => fnd_message.get);
2614
2615 -- Comment out the validation below because Express Move can be done for more
2616 -- then one quantity. Moreover, this validation was already done through the
2617 -- UI(mobile and MES), and we do not support serilized move in the background.
2618 /*
2619 -- reset enums table
2620 enums.delete;
2624 UPDATE wip_move_txn_interface wmti
2621 -- If user try to do serialized transaction, primary_quantity must be 1.
2622 -- This validation is only for serialized discrete job. For serialized
2623 -- OSFM job, primary_quantity can be more than 1.
2625 SET wmti.process_status = WIP_CONSTANTS.ERROR
2626 WHERE wmti.group_id = g_group_id
2627 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2628 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2629 AND EXISTS -- serialized discrete job and serialized transaction
2630 (SELECT 'X'
2631 FROM wip_discrete_jobs wdj,
2632 wip_entities we
2633 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2634 AND wdj.wip_entity_id = we.wip_entity_id
2635 AND we.entity_type = WIP_CONSTANTS.DISCRETE
2636 AND wdj.serialization_start_op IS NOT NULL
2637 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2638 AND wmti.to_operation_seq_num >= wdj.serialization_start_op
2639 AND wmti.primary_quantity <> 1)
2640 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2641
2642 fnd_message.set_name('WIP', 'WIP_INVALID_SERIAL_QTY');
2643 add_error(p_txn_ids => enums,
2644 p_err_col => 'TRANSACTION/PRIMARY_QUANTITY',
2645 p_err_msg => fnd_message.get);
2646 */
2647
2648 -- reset enums table
2649 enums.delete;
2650 -- if user provide serial number information for non-serialized job, or
2651 -- serialized job with non-serialized move, error out.
2652 UPDATE wip_move_txn_interface wmti
2653 SET wmti.process_status = WIP_CONSTANTS.ERROR
2654 WHERE wmti.group_id = g_group_id
2655 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2656 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2657 AND EXISTS -- regular job
2658 (SELECT 'X'
2659 FROM wip_discrete_jobs wdj
2660 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2661 AND (wdj.serialization_start_op IS NULL -- non-serialized job
2662 OR -- serialized job with non-serialized move
2663 (wdj.serialization_start_op IS NOT NULL
2664 AND
2665 (wmti.fm_operation_seq_num < wdj.serialization_start_op OR
2666 (wmti.fm_operation_seq_num = wdj.serialization_start_op AND
2667 wmti.fm_intraoperation_step_type = WIP_CONSTANTS.QUEUE))
2668 AND
2669 (wmti.to_operation_seq_num < wdj.serialization_start_op OR
2670 (wmti.to_operation_seq_num = wdj.serialization_start_op AND
2671 wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE))))
2672 )
2673 AND EXISTS
2674 (SELECT 'X'
2675 FROM wip_serial_move_interface wsmi
2676 WHERE wsmi.transaction_id = wmti.transaction_id)
2677 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2678
2679 fnd_message.set_name('WIP', 'WIP_SERIAL_INFO_NOT_ALLOW');
2680 add_error(p_txn_ids => enums,
2681 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2682 p_err_msg => fnd_message.get);
2683
2684
2685 -- reset enums table
2686 enums.delete;
2687 -- if user try to do serialized transaction, number of serial records must be
2688 -- equal to wmti.primary_quantity
2689 UPDATE wip_move_txn_interface wmti
2690 SET wmti.process_status = WIP_CONSTANTS.ERROR
2691 WHERE wmti.group_id = g_group_id
2692 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2693 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2694 AND EXISTS -- serialized job and serialized transaction
2695 (SELECT 'X'
2696 FROM wip_discrete_jobs wdj
2697 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2698 AND wdj.serialization_start_op IS NOT NULL
2699 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2700 AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
2701 AND wmti.primary_quantity <>
2702 (SELECT COUNT(*)
2703 FROM wip_serial_move_interface wsmi,
2704 mtl_serial_numbers msn
2705 WHERE wsmi.transaction_id = wmti.transaction_id
2706 AND wsmi.assembly_serial_number = msn.serial_number
2707 AND wmti.organization_id = msn.current_organization_id
2708 AND wmti.primary_item_id = msn.inventory_item_id
2709 AND msn.wip_entity_id IS NOT NULL
2710 AND msn.wip_entity_id = wmti.wip_entity_id)
2711 AND wmti.primary_quantity <>
2712 (SELECT COUNT(*)
2713 FROM wip_serial_move_interface wsmi,
2714 wip_entities we,
2715 mtl_serial_numbers msn,
2716 mtl_object_genealogy mog
2717 WHERE wsmi.transaction_id = wmti.transaction_id
2718 AND wsmi.assembly_serial_number = msn.serial_number
2719 AND wmti.organization_id = msn.current_organization_id
2720 AND wmti.primary_item_id = msn.inventory_item_id
2721 AND msn.current_status = WIP_CONSTANTS.IN_STORES
2722 AND wmti.wip_entity_id = we.wip_entity_id
2723 AND ((mog.genealogy_origin = 1 AND
2724 mog.parent_object_id = we.gen_object_id AND
2725 mog.object_id = msn.gen_object_id)
2726 OR
2727 (mog.genealogy_origin = 2 AND
2728 mog.parent_object_id = msn.gen_object_id AND
2729 mog.object_id = we.gen_object_id))
2733 fnd_message.set_name('WIP', 'WIP_SERIAL_QTY_MISSMATCH');
2730 AND mog.end_date_active IS NULL)
2731 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2732
2734 add_error(p_txn_ids => enums,
2735 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2736 p_err_msg => fnd_message.get);
2737
2738 -- reset enums table
2739 enums.delete;
2740 -- if user try to do serialized transaction, the status of the serial
2741 -- must correspond to the transaction type.
2742 UPDATE wip_move_txn_interface wmti
2743 SET wmti.process_status = WIP_CONSTANTS.ERROR
2744 WHERE wmti.group_id = g_group_id
2745 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2746 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2747 AND EXISTS -- serialized job and serialized transaction
2748 (SELECT 'X'
2749 FROM wip_discrete_jobs wdj
2750 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2751 AND wdj.serialization_start_op IS NOT NULL
2752 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2753 AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
2754 AND NOT EXISTS
2755 (SELECT 'X'
2756 FROM wip_serial_move_interface wsmi,
2757 mtl_serial_numbers msn
2758 WHERE wsmi.transaction_id = wmti.transaction_id
2759 AND wsmi.assembly_serial_number = msn.serial_number
2760 AND wmti.organization_id = msn.current_organization_id
2761 AND wmti.primary_item_id = msn.inventory_item_id
2762 AND msn.line_mark_id IS NULL
2763 AND ((wmti.transaction_type = WIP_CONSTANTS.RET_TXN AND
2764 msn.group_mark_id IS NULL AND
2765 msn.wip_entity_id IS NULL AND
2766 msn.current_status = WIP_CONSTANTS.IN_STORES)
2767 OR
2768 (wmti.transaction_type IN (WIP_CONSTANTS.MOVE_TXN,
2769 WIP_CONSTANTS.COMP_TXN) AND
2770 msn.group_mark_id IS NOT NULL AND
2771 msn.wip_entity_id IS NOT NULL AND
2772 wmti.wip_entity_id = msn.wip_entity_id AND
2773 -- Define but not use or Issue out of store.
2774 msn.current_status IN (WIP_CONSTANTS.DEF_NOT_USED,
2775 WIP_CONSTANTS.OUT_OF_STORES))))
2776 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2777
2778 fnd_message.set_name('WIP', 'WIP_INVALID_SERIAL_STATUS');
2779 add_error(p_txn_ids => enums,
2780 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2781 p_err_msg => fnd_message.get);
2782
2783 END serial_validation;
2784
2785 -- If there are some errors occur, this routine will set
2786 -- PROCESS_STATUS to WIP_CONSTANTS.ERROR. Then it will insert all the errors
2787 -- into WIP_TXN_INTERFACE_ERRORS
2788 PROCEDURE update_interface_tbl IS
2789 BEGIN
2790 -- there are some errors occur, so set the process_status to error so that
2791 -- move processor will not pick up this record
2792 UPDATE wip_move_txn_interface wmti
2793 SET wmti.process_status = WIP_CONSTANTS.ERROR
2794 WHERE wmti.group_id = g_group_id
2795 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2796 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2797 AND EXISTS
2798 (SELECT 'X'
2799 FROM wip_txn_interface_errors wtie
2800 WHERE wtie.transaction_id = wmti.transaction_id);
2801
2802 -- insert error message to WIP_TXN_INTERFACE_ERRORS, and clear error table
2803 load_errors;
2804 END update_interface_tbl;
2805
2806 /* Fixed bug 5056289. Added more validation for assembly to prevent the whole
2807 batch error out when assembly fail inventory validation.
2808 */
2809 -- Validate assembly related information to prevent the whole batch failing
2810 -- if there is something wrong with the assembly like assembly is not
2811 -- transactable, or assembly is not an inventory item. This check is only for
2812 -- EZ Completion and EZ Return.
2813 PROCEDURE assembly_validation IS
2814 BEGIN
2815 -- reset enums table
2816 enums.delete;
2817 -- Users cannot do EZ Completion/EZ Return if an assembly is not transactable
2818 -- or an assembly is not an inventory item.
2819 UPDATE wip_move_txn_interface wmti
2820 SET wmti.process_status = WIP_CONSTANTS.ERROR
2821 WHERE wmti.group_id = g_group_id
2822 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2823 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2824 AND wmti.transaction_type IN (WIP_CONSTANTS.RET_TXN,
2825 WIP_CONSTANTS.COMP_TXN)
2826 AND EXISTS -- Item flag was not set properly.
2827 (SELECT 'X'
2828 FROM mtl_system_items msi
2829 WHERE msi.inventory_item_id = wmti.primary_item_id
2830 AND msi.organization_id = wmti.organization_id
2831 AND (msi.inventory_item_flag = 'N' OR
2832 msi.mtl_transactions_enabled_flag = 'N'))
2833 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2834
2835 fnd_message.set_name('INV', 'INV_INT_ITMEXP');
2836 add_error(p_txn_ids => enums,
2837 p_err_col => 'PRIMARY_ITEM_ID',
2838 p_err_msg => fnd_message.get);
2839
2840 END assembly_validation;
2841
2842
2843 -- If pass all the validation, and there is no error, this routine will
2844 -- derive all the rest info (fm_operation_code, fm_department_id,
2848 -- update_interface_tbl
2845 -- fm_department_code, to_operation_code, to_department_id, to_department_code)
2846 -- , then update PROCESS_PHASE to WIP_CONSTANTS.MOVE_PROC. This routine
2847 -- should be called after we called all the validation code and
2849 PROCEDURE derive IS
2850 l_PrevOpSeq NUMBER;
2851 l_NextOpSeq NUMBER;
2852 l_OpExists BOOLEAN;
2853 BEGIN
2854
2855 UPDATE wip_move_txn_interface wmti
2856 SET (wmti.fm_operation_code,
2857 wmti.fm_department_id,
2858 wmti.fm_department_code,
2859 wmti.to_operation_code,
2860 wmti.to_department_id,
2861 wmti.to_department_code,
2862 wmti.process_phase) =
2863 (SELECT bso1.operation_code,
2864 wo1.department_id,
2865 bd1.department_code,
2866 bso2.operation_code,
2867 wo2.department_id,
2868 bd2.department_code,
2869 WIP_CONSTANTS.MOVE_PROC
2870 FROM bom_standard_operations bso1,
2871 bom_standard_operations bso2,
2872 bom_departments bd1,
2873 bom_departments bd2,
2874 wip_operations wo1,
2875 wip_operations wo2
2876 WHERE wo1.organization_id = wmti.organization_id
2877 AND wo1.wip_entity_id = wmti.wip_entity_id
2878 AND wo1.operation_seq_num = wmti.fm_operation_seq_num
2879 AND wo2.organization_id = wmti.organization_id
2880 AND wo2.wip_entity_id = wmti.wip_entity_id
2881 AND wo2.operation_seq_num = wmti.to_operation_seq_num
2882 /* Standard operation ID is optional, so we should use outer join */
2883 AND bso1.standard_operation_id(+) = wo1.standard_operation_id
2884 AND bso2.standard_operation_id(+) = wo2.standard_operation_id
2885 AND wo1.department_id = bd1.department_id
2886 AND wo2.department_id = bd2.department_id
2887 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2888 WIP_CONSTANTS.LOTBASED)
2889 OR
2890 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2891 wo1.repetitive_schedule_id = wmti.repetitive_schedule_id AND
2892 wo2.repetitive_schedule_id = wmti.repetitive_schedule_id)))
2893 WHERE wmti.group_id = g_group_id
2894 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2895 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
2896
2897 END derive;
2898
2899 PROCEDURE validate(p_group_id IN NUMBER,
2900 p_initMsgList IN VARCHAR2) IS
2901 l_params wip_logger.param_tbl_t;
2902 l_returnStatus VARCHAR2(1);
2903 l_logLevel NUMBER ;
2904
2905 BEGIN
2906 l_logLevel := fnd_log.g_current_runtime_level;
2907 IF(fnd_api.to_boolean(p_initMsgList)) THEN
2908 fnd_msg_pub.initialize;
2909 END IF;
2910
2911 -- write parameter value to log file
2912 if (l_logLevel <= wip_constants.trace_logging) then
2913 l_params(1).paramName := 'p_group_id';
2914 l_params(1).paramValue := p_group_id;
2915 wip_logger.entryPoint(p_procName => 'wip_move_validator.validate',
2916 p_params => l_params,
2917 x_returnStatus => l_returnStatus);
2918 end if;
2919
2920 -- reset global_variable everytime this routine is called
2921 g_group_id := p_group_id;
2922 enums.delete;
2923 -- Call last_updatd_by and created_by first even if it is a low priority
2924 -- validation because we want to insert last_updated_by and created_by
2925 -- into WIP_TXN_INTERFACE_ERRORS
2926 last_updated_by;
2927 created_by;
2928 /*****************************
2929 * Start critical validation *
2930 *****************************/
2931 -- If any of the procedure below error out, set WMTI.PROCESS_STATUS to
2932 -- Error and stop validation.
2933 /* Bug#2956953 - commented call to organization_id procedure as the validation
2934 for organization_id/organization_code are called from wip move manager code
2935 - Changes done as part of the Wip Move Sequencing Project */
2936 -- organization_id;
2937 wip_entity_id;
2938 transaction_type;
2939 transaction_date;
2940 fm_operation;
2941 fm_step;
2942 to_operation;
2943 to_step;
2944 transaction_qty;
2945 transaction_uom;
2946 ocpl_txn_qty;
2947 /*****************************
2948 * End critical validation *
2949 *****************************/
2950
2951 /*********************************
2952 * Start low priority validation *
2953 *********************************/
2954 -- If any of the procedure below error out, continue validating other
2955 -- low priority validation because we support multiple error message
2956 -- for one record.
2957 transaction_id;
2958 primary_qty;
2959 primary_uom;
2960 ocpl_primary_qty;
2961 ocpl_txn_id;
2962 reason_id;
2963 scrap_account_id;
2964
2965 -- need to call this routine before osp_validation because use
2966 -- created_by as a user_id to validate OSP
2967 osp_validation;
2968 serial_validation;
2969 /* Fixed bug 5056289. */
2970 -- Add more validation for assembly to prevent the whole batch failing if
2971 -- there is something wrong with the assembly. This check is only for
2972 -- EZ Completion and EZ Return.
2973 assembly_validation;
2977 -- set WMTI.PROCESS_STATUS to error if there is an error from any
2974 /*******************************
2975 * End low priority validation *
2976 *******************************/
2978 -- validation and insert error message into WIP_TXN_INTERFACE_ERRORS
2979 update_interface_tbl;
2980 -- derive the rest nessary info
2981 derive;
2982 -- write to the log file
2983 if (l_logLevel <= wip_constants.trace_logging) then
2984 wip_logger.exitPoint(p_procName => 'wip_move_validator.validate',
2985 p_procReturnStatus => fnd_api.g_ret_sts_success,
2986 p_msg => 'procedure complete',
2987 x_returnStatus => l_returnStatus);
2988 end if;
2989 EXCEPTION
2990 WHEN others THEN
2991 if (l_logLevel <= wip_constants.trace_logging) then
2992 wip_logger.exitPoint(p_procName => 'wip_move_validator.validate',
2993 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
2994 p_msg => 'Unexpected Errors: ' || SQLERRM,
2995 x_returnStatus => l_returnStatus);
2996 end if;
2997
2998 END validate;
2999
3000 PROCEDURE get_move_txn_type(p_move_id IN NUMBER,
3001 p_org_id IN NUMBER DEFAULT NULL,
3002 p_wip_entity_id IN NUMBER DEFAULT NULL,
3003 p_assm_item_id IN NUMBER DEFAULT NULL,
3004 p_txn_type OUT NOCOPY VARCHAR2)
3005 IS
3006 BEGIN
3007 p_txn_type := move_txn_type(p_move_id => p_move_id,
3008 p_org_id => p_org_id,
3009 p_wip_entity_id => p_wip_entity_id,
3010 p_assm_item_id => p_assm_item_id);
3011 END get_move_txn_type;
3012
3013
3014 FUNCTION move_txn_type(p_move_id IN NUMBER,
3015 p_org_id IN NUMBER DEFAULT NULL,
3016 p_wip_entity_id IN NUMBER DEFAULT NULL,
3017 p_assm_item_id IN NUMBER DEFAULT NULL) return VARCHAR2
3018 IS
3019 p_txn_type VARCHAR2(80);
3020 l_org_id NUMBER ;
3021 l_wip_entity_id NUMBER ;
3022 l_assm_item_id NUMBER ;
3023 BEGIN
3024 l_org_id := p_org_id;
3025 l_wip_entity_id := p_wip_entity_id;
3026 l_assm_item_id := p_assm_item_id;
3027
3028
3029 if (l_org_id is NULL or l_wip_entity_id is NULL) then
3030 select organization_id,
3031 wip_entity_id
3032 into l_org_id,
3033 l_wip_entity_id
3034 from wip_move_transactions
3035 where transaction_id = p_move_id;
3036 end if;
3037
3038 if (l_assm_item_id is NULL) then
3039 select wdj.primary_item_id
3040 into l_assm_item_id
3041 from wip_discrete_jobs wdj
3042 where wdj.organization_id = l_org_id
3043 and wdj.wip_entity_id = l_wip_entity_id;
3044 end if;
3045
3046 begin
3047 -- Should have at most one match
3048 select distinct lu.meaning
3049 into p_txn_type
3050 from mfg_lookups lu,
3051 mtl_material_transactions mmt
3052 where mmt.move_transaction_id = p_move_id
3053 and mmt.organization_id = l_org_id
3054 and mmt.transaction_source_id = l_wip_entity_id
3055 and mmt.inventory_item_id = l_assm_item_id
3056 and mmt.transaction_type_id in (wip_constants.CPLASSY_TYPE, wip_constants.RETASSY_TYPE)
3057 and lu.lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
3058 and lu.lookup_code = decode(mmt.transaction_type_id, wip_constants.CPLASSY_TYPE, wip_constants.comp_txn, wip_constants.RETASSY_TYPE, wip_constants.ret_txn);
3059 exception
3060 -- no inv txn involved; just a plain move txn
3061 when no_data_found then
3062 select meaning
3063 into p_txn_type
3064 from mfg_lookups
3065 where lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
3066 and lookup_code = wip_constants.move_txn;
3067 when others then
3068 p_txn_type := -1;
3069 end;
3070
3071 return p_txn_type;
3072 END move_txn_type;
3073
3074 PROCEDURE validateOATxn(p_group_id IN NUMBER) IS
3075 l_params wip_logger.param_tbl_t;
3076 l_returnStatus VARCHAR2(1);
3077 l_logLevel NUMBER ;
3078
3079 BEGIN
3080 l_logLevel := fnd_log.g_current_runtime_level;
3081
3082 -- write parameter value to log file
3083 if (l_logLevel <= wip_constants.trace_logging) then
3084 l_params(1).paramName := 'p_group_id';
3085 l_params(1).paramValue := p_group_id;
3086 wip_logger.entryPoint(p_procName => 'wip_move_validator.validateOATxn',
3087 p_params => l_params,
3088 x_returnStatus => l_returnStatus);
3089 end if;
3090
3091 -- reset global_variable everytime this routine is called
3092 g_group_id := p_group_id;
3093 enums.delete;
3094
3095 -- Derive ACCT_PERIOD_ID from TRANSACTION_DATE
3096 UPDATE wip_move_txn_interface wmti
3097 SET wmti.acct_period_id =
3098 (SELECT oap.acct_period_id
3099 FROM org_acct_periods oap
3100 WHERE oap.organization_id = wmti.organization_id
3101 -- modified the statement below for timezone project in J
3102 AND TRUNC(inv_le_timezone_pub.get_le_day_for_inv_org(
3103 wmti.transaction_date, -- p_trxn_date
3107 WHERE wmti.group_id = g_group_id
3104 wmti.organization_id -- p_inv_org_id
3105 )) BETWEEN
3106 oap.period_start_date AND oap.schedule_close_date)
3108 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3109 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
3110
3111 -- reset enums table
3112 enums.delete;
3113 -- Error out if there is no open accout period for the TRANSACTION_DATE
3114 -- specified or there is no WIP_PERIOD_BALANCES
3115 UPDATE wip_move_txn_interface wmti
3116 SET wmti.process_status = WIP_CONSTANTS.ERROR
3117 WHERE wmti.group_id = g_group_id
3118 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3119 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3120 AND (wmti.acct_period_id IS NULL
3121 OR
3122 NOT EXISTS
3123 (SELECT 'X'
3124 FROM wip_period_balances wpb
3125 WHERE wpb.acct_period_id = wmti.acct_period_id
3126 AND wpb.wip_entity_id = wmti.wip_entity_id
3127 AND wpb.organization_id = wmti.organization_id))
3128 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3129
3130 fnd_message.set_name('WIP', 'WIP_NO_BALANCE');
3131 add_error(p_txn_ids => enums,
3132 p_err_col => 'TRANSACTION_DATE',
3133 p_err_msg => fnd_message.get);
3134
3135 -- Fixed bug 5310474
3136 -- reset enums table
3137 enums.delete;
3138 -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
3139 -- no move shop floor status attached
3140 UPDATE wip_move_txn_interface wmti
3141 SET wmti.process_status = WIP_CONSTANTS.ERROR
3142 WHERE wmti.group_id = g_group_id
3143 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3144 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3145 AND EXISTS
3146 (SELECT 'X'
3147 FROM wip_shop_floor_status_codes wsc,
3148 wip_shop_floor_statuses ws
3149 WHERE wsc.organization_id = wmti.organization_id
3150 AND ws.organization_id = wmti.organization_id
3151 AND ws.wip_entity_id = wmti.wip_entity_id
3152 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3153 AND ws.operation_seq_num = wmti.fm_operation_seq_num
3154 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
3155 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3156 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3157 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
3158 AND (wmti.source_code IS NULL OR
3159 wmti.source_code <> 'RCV' OR
3160 (wmti.source_code = 'RCV' AND
3161 NOT EXISTS
3162 (SELECT 'X'
3163 FROM wip_parameters wp
3164 WHERE wp.organization_id = wmti.organization_id
3165 AND wp.osp_shop_floor_status =
3166 wsc.shop_floor_status_code))))
3167 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3168
3169 fnd_message.set_name('WIP', 'WIP_STATUS_NO_TXN1');
3170 add_error(p_txn_ids => enums,
3171 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
3172 p_err_msg => fnd_message.get);
3173
3174 -- reset enums table
3175 enums.delete;
3176 -- Error out if user try to easy complete job/schedule that has No Move shop
3177 -- floor status attached to Tomove of the last operation
3178 UPDATE wip_move_txn_interface wmti
3179 SET wmti.process_status = WIP_CONSTANTS.ERROR
3180 WHERE wmti.group_id = g_group_id
3181 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3182 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3183 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
3184 AND EXISTS
3185 (SELECT 'X'
3186 FROM wip_shop_floor_status_codes wsc,
3187 wip_shop_floor_statuses ws
3188 WHERE wsc.organization_id = wmti.organization_id
3189 AND ws.organization_id = wmti.organization_id
3190 AND ws.wip_entity_id = wmti.wip_entity_id
3191 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3192 AND ws.operation_seq_num = wmti.to_operation_seq_num
3193 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
3194 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3195 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3196 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
3197 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3198
3199 fnd_message.set_name('WIP', 'WIP_STATUS_NO_TXN2');
3200 add_error(p_txn_ids => enums,
3201 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
3202 p_err_msg => fnd_message.get);
3203 -- End of fix for bug 5310474
3204
3205 -- Validate whether there is no move shopfloor status in between or not.
3206 -- reset enums table
3207 enums.delete;
3208 -- Error out if wip_parameter do not allow move over no_move shop floor
3209 -- status, and there are no_move status in between
3210 UPDATE wip_move_txn_interface wmti
3211 SET wmti.process_status = WIP_CONSTANTS.ERROR
3212 WHERE wmti.group_id = g_group_id
3213 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3214 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3215 AND wip_sf_status.count_no_move_statuses(
3216 wmti.organization_id, -- p_org_id
3217 wmti.wip_entity_id, -- p_wip_id
3218 wmti.line_id, -- p_line_id
3219 wmti.repetitive_schedule_id, -- p_sched_id
3220 wmti.fm_operation_seq_num, -- p_fm_op
3221 wmti.fm_intraoperation_step_type, -- p_fm_step
3222 wmti.to_operation_seq_num, -- p_to_op
3223 wmti.to_intraoperation_step_type, -- p_to_step
3224 -- Fixed bug 2121222
3225 wmti.source_code) > 0 -- p_source_code
3226 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3227
3228 fnd_message.set_name ('WIP', 'WIP_NO_MOVE_SF_STATUS_BETWEEN');
3229 add_error(p_txn_ids => enums,
3230 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
3231 p_err_msg => fnd_message.get);
3232
3233 -- Do OSP related validation.
3234 osp_validation;
3235
3236 -- Set WMTI.PROCESS_STATUS to error if there is an error from any
3237 -- validation and insert error message into WIP_TXN_INTERFACE_ERRORS
3238 update_interface_tbl;
3239
3240 -- Set WMTI.PROCESS_PHASE to WIP_CONSTANTS.MOVE_PROC so that move processing
3241 -- code can process these records.
3242 UPDATE wip_move_txn_interface wmti
3243 SET process_phase = WIP_CONSTANTS.MOVE_PROC
3244 WHERE wmti.group_id = g_group_id
3245 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3246 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
3247
3248 -- write to the log file
3249 if (l_logLevel <= wip_constants.trace_logging) then
3250 wip_logger.exitPoint(p_procName => 'wip_move_validator.validateOATxn',
3251 p_procReturnStatus => fnd_api.g_ret_sts_success,
3252 p_msg => 'procedure complete',
3253 x_returnStatus => l_returnStatus);
3254 end if;
3255 EXCEPTION
3256 WHEN others THEN
3257 if (l_logLevel <= wip_constants.trace_logging) then
3258 wip_logger.exitPoint(p_procName => 'wip_move_validator.validateOATxn',
3259 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
3260 p_msg => 'Unexpected Errors: ' || SQLERRM,
3261 x_returnStatus => l_returnStatus);
3262 end if;
3263
3264 END validateOATxn;
3265
3266 END wip_move_validator;
[Home] [Help]