1 PACKAGE BODY wip_move_validator AS
2 /* $Header: wipmovvb.pls 120.14.12020000.2 2012/07/18 18:12:35 ankohli 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 = wmti.organization_code) --Fix by bug 9220479
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 = NVL(wmti.organization_code,
133 mp.organization_code)--Fix by bug 9220479
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
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
381 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
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;
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
496 SET wmti.process_status = WIP_CONSTANTS.ERROR
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
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,
611 WIP_CONSTANTS.LOTBASED)
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
724 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
725
726 fnd_message.set_name('WIP', 'WIP_NO_FORWARD_DATING');
727 add_error(p_txn_ids => enums,
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
841 END transaction_date;
842
843 -- validate fm_operation_seq_num. From operation must be a valid operation.
844 -- For easy return transaction, from operation must be the last 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 l_sf_status VARCHAR2(100); /*Fix Bug 9758848*/
928 BEGIN
929 -- Set FM_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
930 -- EZ Return and FM_INTRAOPERATION_STEP_TYPE is null
931 UPDATE wip_move_txn_interface wmti
932 SET wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
933 WHERE wmti.group_id = g_group_id
934 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
935 AND wmti.process_status = WIP_CONSTANTS.RUNNING
936 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
937 AND wmti.fm_intraoperation_step_type IS NULL;
938
939 -- reset enums table
940 enums.delete;
941 -- Error out if FM_INTRAOPERATION_STEP_TYPE is null or invalid
942 UPDATE wip_move_txn_interface wmti
943 SET wmti.process_status = WIP_CONSTANTS.ERROR
944 WHERE wmti.group_id = g_group_id
945 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
946 AND wmti.process_status = WIP_CONSTANTS.RUNNING
947 AND (wmti.fm_intraoperation_step_type IS NULL
948 OR
949 (NOT EXISTS
950 (SELECT 'X'
951 FROM wip_valid_intraoperation_steps wvis,
952 wip_operations wo
953 WHERE wvis.organization_id = wmti.organization_id
954 AND wvis.step_lookup_type = wmti.fm_intraoperation_step_type
955 AND wo.organization_id = wmti.organization_id
956 AND wo.wip_entity_id = wmti.wip_entity_id
957 AND wo.operation_seq_num = wmti.fm_operation_seq_num
958 AND NVL(wo.repetitive_schedule_id, -1) =
959 NVL(wmti.repetitive_schedule_id, -1)
960 AND ((wvis.record_creator = 'USER' OR
961 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
962 OR
963 (wvis.record_creator = 'SYSTEM' AND
964 wo.next_operation_seq_num IS NULL)))))
965 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
966
967 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
968 fnd_message.set_token('ENTITY', 'FM_INTRAOPERATION_STEP_TYPE');
969 add_error(p_txn_ids => enums,
970 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
971 p_err_msg => fnd_message.get);
972
973 -- reset enums table
974 enums.delete;
975 -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
976 -- no move shop floor status attached
977 UPDATE wip_move_txn_interface wmti
978 SET wmti.process_status = WIP_CONSTANTS.ERROR
979 WHERE wmti.group_id = g_group_id
980 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
981 AND wmti.process_status = WIP_CONSTANTS.RUNNING
982 AND EXISTS
983 (SELECT 'X'
984 FROM wip_shop_floor_status_codes wsc,
985 wip_shop_floor_statuses ws
986 WHERE wsc.organization_id = wmti.organization_id
987 AND ws.organization_id = wmti.organization_id
988 AND ws.wip_entity_id = wmti.wip_entity_id
989 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
990 AND ws.operation_seq_num = wmti.fm_operation_seq_num
991 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
992 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
993 AND wsc.status_move_flag = WIP_CONSTANTS.NO
994 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
995 AND (wmti.source_code IS NULL OR
996 wmti.source_code <> 'RCV' OR
997 (wmti.source_code = 'RCV' AND
998 NOT EXISTS
999 (SELECT 'X'
1000 FROM wip_parameters wp
1001 WHERE wp.organization_id = wmti.organization_id
1002 AND wp.osp_shop_floor_status =
1003 wsc.shop_floor_status_code))))
1004 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1005
1006 /* Added the following for SUN enhancement in Bug 9758848*/
1007 if enums.count > 0 then
1008 for i in 1..enums.count loop
1009 SELECT ws.shop_floor_status_code
1010 into l_sf_status
1011 FROM wip_shop_floor_status_codes wsc,
1012 wip_shop_floor_statuses ws,
1013 wip_move_txn_interface wmti
1014 WHERE wsc.organization_id = wmti.organization_id
1015 AND ws.organization_id = wmti.organization_id
1016 AND ws.wip_entity_id = wmti.wip_entity_id
1017 AND wmti.transaction_id = enums(i)
1018 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
1019 AND ws.operation_seq_num = wmti.fm_operation_seq_num
1020 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
1021 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
1022 AND wsc.status_move_flag = WIP_CONSTANTS.NO
1023 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
1024 AND rownum = 1;
1025
1026 fnd_message.set_name('WIP', 'WIP_SF_STATUS_NO_TXN1');
1027 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
1028 add_error(p_txn_id => enums(i),
1029 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1030 p_err_msg => fnd_message.get);
1031 end loop;
1032 end if;
1033
1034 -- reset enums table
1035 enums.delete;
1036 -- Error out if users try to perform easy completion from Tomove of the
1037 -- last operation
1038 UPDATE wip_move_txn_interface wmti
1039 SET wmti.process_status = WIP_CONSTANTS.ERROR
1040 WHERE wmti.group_id = g_group_id
1041 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1042 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1043 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1044 AND wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1045 AND wmti.fm_operation_seq_num =
1046 (SELECT wo.operation_seq_num
1047 FROM wip_operations wo
1048 WHERE wo.wip_entity_id = wmti.wip_entity_id
1049 AND wo.organization_id = wmti.organization_id
1050 AND NVL(wo.repetitive_schedule_id, -1) =
1051 NVL(wmti.repetitive_schedule_id, -1)
1052 AND wo.next_operation_seq_num IS NULL)
1053 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1054
1055 fnd_message.set_name('WIP', 'WIP_EZ_NO_CMP_LAST_OP2');
1056 add_error(p_txn_ids => enums,
1057 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1058 p_err_msg => fnd_message.get);
1059
1060 -- reset enums table
1061 enums.delete;
1062 -- Error out if TRANSACTION_TYPE is EZ Return and
1063 -- FM_INTRAOPERATION_STEP_TYPE not equal to Tomove
1064 UPDATE wip_move_txn_interface wmti
1065 SET wmti.process_status = WIP_CONSTANTS.ERROR
1066 WHERE wmti.group_id = g_group_id
1067 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1068 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1069 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
1070 AND wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
1071 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1072
1073 fnd_message.set_name('WIP', 'WIP_EZ_FM_LAST_STEP');
1074 add_error(p_txn_ids => enums,
1075 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
1076 p_err_msg => fnd_message.get);
1077 END fm_step;
1078
1079 -- validate to_operation_seq_num. To operation must be a valid operation.
1080 -- For easy complete transaction, To operation must be the last operation.
1081 -- Callers always need to pass this value except for Easy complete
1082 -- transactions. If callers do not provide this info and it is complete
1083 -- transaction, just default to_operation to last_op
1084 PROCEDURE to_operation IS
1085 BEGIN
1086 -- Set TO_OPERATION_SEQ_NUM to last_operation if TRANSACTION_TYPE is
1087 -- EZ Completion and TO_OPERATION_SEQ_NUM is null
1088 UPDATE wip_move_txn_interface wmti
1089 SET wmti.to_operation_seq_num =
1090 (SELECT wo.operation_seq_num
1091 FROM wip_operations wo
1092 WHERE wo.wip_entity_id = wmti.wip_entity_id
1093 AND wo.organization_id = wmti.organization_id
1094 AND NVL(wo.repetitive_schedule_id, -1) =
1095 NVL(wmti.repetitive_schedule_id, -1)
1096 AND wo.next_operation_seq_num IS NULL)
1097 WHERE wmti.group_id = g_group_id
1098 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1099 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1100 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1101 AND wmti.to_operation_seq_num IS NULL;
1102
1103 /*Bug 4421485->Even for plain moves we will derive to_operation as
1104 next count point operation */
1105 UPDATE wip_move_txn_interface wmti
1106 SET wmti.to_operation_seq_num =
1107 (SELECT MIN(wo.operation_seq_num)
1108 FROM wip_operations wo
1109 WHERE wo.organization_id = wmti.organization_id
1110 AND wo.wip_entity_id = wmti.wip_entity_id
1111 AND NVL(wo.repetitive_schedule_id, -1) =
1112 NVL(wmti.repetitive_schedule_id, -1)
1113 AND wo.operation_seq_num > wmti.fm_operation_seq_num
1114 AND wo.count_point_type = 1)
1115 WHERE wmti.group_id = g_group_id
1116 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1117 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1118 AND wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
1119 AND wmti.to_operation_seq_num IS NULL;
1120
1121 -- reset enums table
1122 enums.delete;
1123 -- Error out if TO_OPERATION_SEQ_NUM is null or TO_OPERATION_SEQ_NUM
1124 -- is invalid
1125 UPDATE wip_move_txn_interface wmti
1126 SET wmti.process_status = WIP_CONSTANTS.ERROR
1127 WHERE wmti.group_id = g_group_id
1128 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1129 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1130 AND (wmti.to_operation_seq_num IS NULL
1131 OR
1132 (NOT EXISTS
1133 (SELECT 'X'
1134 FROM wip_operations wo
1135 WHERE wo.wip_entity_id = wmti.wip_entity_id
1136 AND wo.organization_id = wmti.organization_id
1137 AND wo.operation_seq_num = wmti.to_operation_seq_num
1138 AND NVL(wo.repetitive_schedule_id, -1) =
1139 NVL(wmti.repetitive_schedule_id, -1))))
1140 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1141
1142 fnd_message.set_name('WIP', 'WIP_INVALID_OPERATION');
1143 add_error(p_txn_ids => enums,
1144 p_err_col => 'TO_OPERATION_SEQ_NUM',
1145 p_err_msg => fnd_message.get);
1146
1147 -- reset enums table
1148 enums.delete;
1149 -- Error out if TRANSACTION_TYPE is EZ Ccmplete and TO_OPERATION_SEQ_NUM
1150 -- is not equal to the last operation.
1151 UPDATE wip_move_txn_interface wmti
1152 SET wmti.process_status = WIP_CONSTANTS.ERROR
1153 WHERE wmti.group_id = g_group_id
1154 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1155 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1156 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1157 AND wmti.to_operation_seq_num <>
1158 (SELECT wo.operation_seq_num
1159 FROM wip_operations wo
1160 WHERE wo.wip_entity_id = wmti.wip_entity_id
1161 AND wo.organization_id = wmti.organization_id
1162 AND NVL(wo.repetitive_schedule_id, -1) =
1163 NVL(wmti.repetitive_schedule_id, -1)
1164 AND wo.next_operation_seq_num IS NULL)
1165 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1166
1167 fnd_message.set_name('WIP', 'WIP_EZ_TO_LAST_OP');
1168 add_error(p_txn_ids => enums,
1169 p_err_col => 'TO_OPERATION_SEQ_NUM',
1170 p_err_msg => fnd_message.get);
1171 END to_operation;
1172
1173 -- validate to_intraoperation_step_type. To step must be valid.
1174 -- If easy complete transaction, to step must be "To move". If easy return,
1175 -- to step cannot be "To move" when to operation is the last operation.
1176 -- If callers do not provide this info and it is complete transaction,
1177 -- just default to_step to to move
1178 PROCEDURE to_step IS
1179 l_sf_status VARCHAR2(100); /*Fix Bug 9758848*/
1180 BEGIN
1181 -- Set TO_INTRAOPERATION_STEP_TYPE to Tomove if TRANSACTION_TYPE is
1182 -- EZ Complete and TO_INTRAOPERATION_STEP_TYPE is null
1183 UPDATE wip_move_txn_interface wmti
1184 /*Bug Bug 4421485*/
1185 SET wmti.to_intraoperation_step_type =
1186 DECODE(wmti.transaction_type,
1187 WIP_CONSTANTS.COMP_TXN,WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.QUEUE)
1188 WHERE wmti.group_id = g_group_id
1189 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1190 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1191 /*Bug Bug 4421485*/
1192 AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
1193 WIP_CONSTANTS.MOVE_TXN)
1194 AND wmti.to_intraoperation_step_type IS NULL;
1195
1196 -- reset enums table
1197 enums.delete;
1198 -- Error out if TO_INTRAOPERATION_STEP_TYPE is null or invalid
1199 UPDATE wip_move_txn_interface wmti
1200 SET wmti.process_status = WIP_CONSTANTS.ERROR
1201 WHERE wmti.group_id = g_group_id
1202 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1203 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1204 AND (wmti.to_intraoperation_step_type IS NULL
1205 OR
1206 (NOT EXISTS
1207 (SELECT 'X'
1208 FROM wip_valid_intraoperation_steps wvis,
1209 wip_operations wo
1210 WHERE wvis.organization_id = wmti.organization_id
1211 AND wvis.step_lookup_type = wmti.to_intraoperation_step_type
1212 AND wo.organization_id = wmti.organization_id
1213 AND wo.wip_entity_id = wmti.wip_entity_id
1214 AND wo.operation_seq_num = wmti.to_operation_seq_num
1215 AND NVL(wo.repetitive_schedule_id, -1) =
1216 NVL(wmti.repetitive_schedule_id, -1)
1217 -- Fixed bug 5059521. Since OSFM build routing as it goes, we cannot rely on
1218 -- wo.next_operation_seq_num IS NULL to determine that it is the last
1219 -- operation or not.
1220 AND (((wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
1221 WIP_CONSTANTS.REPETITIVE)
1222 OR
1223 (wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
1224 wmti.transaction_type = WIP_CONSTANTS.COMP_TXN))
1225 AND
1226 ((wvis.record_creator = 'USER' OR
1227 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
1228 OR
1229 (wvis.record_creator = 'SYSTEM' AND
1230 wo.next_operation_seq_num IS NULL)))
1231 OR
1232 (wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
1233 wmti.transaction_type <> WIP_CONSTANTS.COMP_TXN AND
1234 (wvis.record_creator = 'USER' OR
1235 wvis.step_lookup_type = WIP_CONSTANTS.QUEUE))))))
1236
1237 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1238
1239 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1240 fnd_message.set_token('ENTITY', 'TO_INTRAOPERATION_STEP_TYPE');
1241 add_error(p_txn_ids => enums,
1242 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1243 p_err_msg => fnd_message.get);
1244
1245 -- reset enums table
1246 enums.delete;
1247 -- Error out if users try to move to the same operation and step as the
1248 -- FM_OPERATION_SEQ_NUM and FM_INTRAOPERATION_STEP_TYPE
1249 UPDATE wip_move_txn_interface wmti
1250 SET wmti.process_status = WIP_CONSTANTS.ERROR
1251 WHERE wmti.group_id = g_group_id
1252 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1253 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1254 AND wmti.fm_operation_seq_num = wmti.to_operation_seq_num
1255 AND wmti.fm_intraoperation_step_type = wmti.to_intraoperation_step_type
1256 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1257
1258 fnd_message.set_name('WIP', 'WIP_SAME_OP_AND_STEP');
1259 add_error(p_txn_ids => enums,
1260 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1261 p_err_msg => fnd_message.get);
1262
1263 -- reset enums table
1264 enums.delete;
1265 -- Error out if TRANSACTION_TYPE is EZ Complete and
1266 -- TO_INTRAOPERATION_STEP_TYPE not equal to Tomove
1267 UPDATE wip_move_txn_interface wmti
1268 SET wmti.process_status = WIP_CONSTANTS.ERROR
1269 WHERE wmti.group_id = g_group_id
1270 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1271 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1272 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1273 AND wmti.to_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
1274 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1275
1276 fnd_message.set_name('WIP', 'WIP_EZ_TO_LAST_STEP');
1277 add_error(p_txn_ids => enums,
1278 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1279 p_err_msg => fnd_message.get);
1280
1281 -- reset enums table
1282 enums.delete;
1283 -- Error out if user try to easy complete job/schedule that has No Move shop
1284 -- floor status attached to Tomove of the last operation
1285 UPDATE wip_move_txn_interface wmti
1286 SET wmti.process_status = WIP_CONSTANTS.ERROR
1287 WHERE wmti.group_id = g_group_id
1288 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1289 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1290 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
1291 AND EXISTS
1292 (SELECT 'X'
1293 FROM wip_shop_floor_status_codes wsc,
1294 wip_shop_floor_statuses ws
1295 WHERE wsc.organization_id = wmti.organization_id
1296 AND ws.organization_id = wmti.organization_id
1297 AND ws.wip_entity_id = wmti.wip_entity_id
1298 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
1299 AND ws.operation_seq_num = wmti.to_operation_seq_num
1300 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1301 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
1302 AND wsc.status_move_flag = WIP_CONSTANTS.NO
1303 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
1304 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1305
1306 /* Added the following for SUN enhancement in Bug 9758848*/
1307 if enums.count > 0 then
1308 for i in 1..enums.count loop
1309 SELECT ws.shop_floor_status_code
1310 INTO l_sf_status
1311 FROM wip_shop_floor_status_codes wsc,
1312 wip_shop_floor_statuses ws,
1313 wip_move_txn_interface wmti
1314 WHERE wsc.organization_id = wmti.organization_id
1315 AND ws.organization_id = wmti.organization_id
1316 AND ws.wip_entity_id = wmti.wip_entity_id
1317 AND wmti.transaction_id = enums(i)
1318 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
1319 AND ws.operation_seq_num = wmti.to_operation_seq_num
1320 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
1321 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
1322 AND wsc.status_move_flag = WIP_CONSTANTS.NO
1323 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
1324 and rownum = 1;
1325
1326 fnd_message.set_name('WIP', 'WIP_SF_STATUS_NO_TXN2');
1327 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
1328 add_error(p_txn_id => enums(i),
1329 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1330 p_err_msg => fnd_message.get);
1331 end loop;
1332 end if;
1333
1334 -- reset enums table
1335 enums.delete;
1336 -- Error out if wip_parameter do not allow move over no_move shop floor
1337 -- status, and there are no_move status in between
1338 UPDATE wip_move_txn_interface wmti
1339 SET wmti.process_status = WIP_CONSTANTS.ERROR
1340 WHERE wmti.group_id = g_group_id
1341 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1342 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1343 AND wip_sf_status.count_no_move_statuses(
1344 wmti.organization_id, -- p_org_id
1345 wmti.wip_entity_id, -- p_wip_id
1346 wmti.line_id, -- p_line_id
1347 wmti.repetitive_schedule_id, -- p_sched_id
1348 wmti.fm_operation_seq_num, -- p_fm_op
1349 wmti.fm_intraoperation_step_type, -- p_fm_step
1350 wmti.to_operation_seq_num, -- p_to_op
1351 wmti.to_intraoperation_step_type, -- p_to_step
1352 -- Fixed bug 2121222
1353 wmti.source_code) > 0 -- p_source_code
1354 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1355
1356 /* Added the following for SUN enhancement in Bug 9758848*/
1357 if enums.count > 0 then
1358 for i in 1..enums.count loop
1359 select wip_sf_status.get_no_move_status(
1360 wmti.organization_id, -- p_org_id
1361 wmti.wip_entity_id, -- p_wip_id
1362 wmti.line_id, -- p_line_id
1363 wmti.repetitive_schedule_id, -- p_sched_id
1364 wmti.fm_operation_seq_num, -- p_fm_op
1365 wmti.fm_intraoperation_step_type, -- p_fm_step
1366 wmti.to_operation_seq_num, -- p_to_op
1367 wmti.to_intraoperation_step_type, -- p_to_step
1368 wmti.source_code)
1369 into l_sf_status
1370 from wip_move_txn_interface wmti
1371 where wmti.transaction_id = enums(i)
1372 and wmti.process_status = WIP_CONSTANTS.ERROR;
1373 fnd_message.set_name ('WIP', 'WIP_NO_MOVE_STATUS_BETWEEN');
1374 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
1375 add_error(p_txn_id => enums(i),
1376 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
1377 p_err_msg => fnd_message.get);
1378 end loop;
1379 end if;
1380 END to_step;
1381
1382 -- transaction_quantity must be positive
1383 PROCEDURE transaction_qty IS
1384 BEGIN
1385 -- reset enums table
1386 enums.delete;
1387
1388 -- Error out if TRANSACTION_QUANTITY is negative or zero
1389 UPDATE wip_move_txn_interface wmti
1390 SET wmti.process_status = WIP_CONSTANTS.ERROR
1391 WHERE wmti.group_id = g_group_id
1392 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1393 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1394 AND wmti.transaction_quantity <= 0
1395 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1396
1397 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1398 fnd_message.set_token('ENTITY1', 'TRANSACTION_QUANTITY');
1399 fnd_message.set_token('ENTITY2', 'zero');
1400 add_error(p_txn_ids => enums,
1401 p_err_col => 'TRANSACTION_QUANTITY',
1402 p_err_msg => fnd_message.get);
1403
1404 END transaction_qty;
1405
1406
1407 -- transaction_uom must be defined
1408 PROCEDURE transaction_uom IS
1409 BEGIN
1410 -- reset enums table
1411 enums.delete;
1412 -- Error out if TRANSACTION_UOM is invalid
1413 UPDATE wip_move_txn_interface wmti
1414 SET wmti.process_status = WIP_CONSTANTS.ERROR
1415 WHERE wmti.group_id = g_group_id
1416 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1417 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1418 AND NOT EXISTS
1419 (SELECT 'X'
1420 FROM mtl_item_uoms_view miuv
1421 WHERE miuv.organization_id = wmti.organization_id
1422 AND miuv.inventory_item_id = wmti.primary_item_id
1423 AND miuv.uom_code = wmti.transaction_uom)
1424 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1425
1426 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1427 fnd_message.set_token('ENTITY', 'TRANSACTION_UOM');
1428 add_error(p_txn_ids => enums,
1429 p_err_col => 'TRANSACTION_UOM',
1430 p_err_msg => fnd_message.get);
1431
1432 END transaction_uom;
1433
1434 -- validate overcompletion_transaction_qty. This is an optional info.
1435 -- The caller need to provide this only for overmove/overcompletion txns.
1436 -- However, we do not allow overreturn, and over move for backward move.
1437 -- We also not allow overmove/overcomplete from scrap or reject step.
1438 -- This value cannot be zero or negative either.
1439 PROCEDURE ocpl_txn_qty IS
1440 BEGIN
1441 -- reset enums table
1442 enums.delete;
1443
1444 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is negative or zero
1445 UPDATE wip_move_txn_interface wmti
1446 SET wmti.process_status = WIP_CONSTANTS.ERROR
1447 WHERE wmti.group_id = g_group_id
1448 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1449 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1450 AND wmti.overcompletion_transaction_qty IS NOT NULL
1451 AND wmti.overcompletion_transaction_qty <= 0
1452 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1453
1454 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1455 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_TRANSACTION_QTY');
1456 fnd_message.set_token('ENTITY2', 'zero');
1457 add_error(p_txn_ids => enums,
1458 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1459 p_err_msg => fnd_message.get);
1460
1461 -- reset enums table
1462 enums.delete;
1463 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is greater than
1464 -- TRANSACTION_QUANTITY
1465 UPDATE wip_move_txn_interface wmti
1466 SET wmti.process_status = WIP_CONSTANTS.ERROR
1467 WHERE wmti.group_id = g_group_id
1468 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1469 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1470 AND wmti.overcompletion_transaction_qty IS NOT NULL
1471 AND wmti.overcompletion_transaction_qty > wmti.transaction_quantity
1472 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1473
1474 fnd_message.set_name('MFG', 'MFG_GREATER_OR_EQUAL');
1475 fnd_message.set_token('ENTITY1', 'TRANSACTION_QUANTITY');
1476 fnd_message.set_token('ENTITY2', 'OVERCOMPLETION_TRANSACTION_QTY');
1477 add_error(p_txn_ids => enums,
1478 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1479 p_err_msg => fnd_message.get);
1480
1481 -- reset enums table
1482 enums.delete;
1483 -- Error out if user try to do over Return
1484 UPDATE wip_move_txn_interface wmti
1485 SET wmti.process_status = WIP_CONSTANTS.ERROR
1486 WHERE wmti.group_id = g_group_id
1487 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1488 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1489 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
1490 AND wmti.overcompletion_transaction_qty IS NOT NULL
1491 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1492
1493 fnd_message.set_name('WIP', 'WIP_NO_OC_RET');
1494 add_error(p_txn_ids => enums,
1495 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1496 p_err_msg => fnd_message.get);
1497
1498 -- reset enums table
1499 enums.delete;
1500 -- Error out if user try to do over Return from Scrap/Return from Reject
1501 UPDATE wip_move_txn_interface wmti
1502 SET wmti.process_status = WIP_CONSTANTS.ERROR
1503 WHERE wmti.group_id = g_group_id
1504 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1505 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1506 AND wmti.fm_intraoperation_step_type IN (WIP_CONSTANTS.SCRAP,
1507 WIP_CONSTANTS.REJECT)
1508 AND wmti.overcompletion_transaction_qty IS NOT NULL
1509 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1510
1511 fnd_message.set_name('WIP', 'WIP_NO_OC_SCR_REJ');
1512 add_error(p_txn_ids => enums,
1513 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1514 p_err_msg => fnd_message.get);
1515
1516 -- reset enums table
1517 enums.delete;
1518 -- Error out if OVERCOMPLETION_TRANSACTION_QTY is specified for backward
1519 -- move txns
1520 UPDATE wip_move_txn_interface wmti
1521 SET wmti.process_status = WIP_CONSTANTS.ERROR
1522 WHERE wmti.group_id = g_group_id
1523 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1524 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1525 AND (wmti.to_operation_seq_num < wmti.fm_operation_seq_num OR
1526 (wmti.to_operation_seq_num = wmti.fm_operation_seq_num AND
1527 wmti.to_intraoperation_step_type <
1528 wmti.fm_intraoperation_step_type))
1529 AND wmti.overcompletion_transaction_qty IS NOT NULL
1530 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
1531
1532 fnd_message.set_name('WIP', 'WIP_NO_OC_REV_MOVE');
1533 add_error(p_txn_ids => enums,
1534 p_err_col => 'OVERCOMPLETION_TRANSACTION_QTY',
1535 p_err_msg => fnd_message.get);
1536
1537 END ocpl_txn_qty;
1538
1539 -- validate transaction_id against the one in WIP_MOVE_TRANSACTIONS, and
1540 -- WIP_MOVE_TXN_INTERFACE. This value need to be unique.
1541 PROCEDURE transaction_id IS
1542 l_errMsg VARCHAR2(240);
1543 BEGIN
1544 -- Generate TRANSACTION_ID if user does not provide this value
1545 UPDATE wip_move_txn_interface wmti
1546 SET wmti.transaction_id = wip_transactions_s.nextval
1547 WHERE wmti.group_id = g_group_id
1548 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1549 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1550 AND wmti.transaction_id IS NULL;
1551
1552 -- Set Error Message
1553 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
1554 fnd_message.set_token('ENTITY', 'TRANSACTION_ID');
1555 l_errMsg := substrb(fnd_message.get, 1, 240);
1556
1557 INSERT INTO wip_txn_interface_errors(
1558 transaction_id,
1559 error_message,
1560 error_column,
1561 last_update_date,
1562 last_updated_by,
1563 creation_date,
1564 created_by,
1565 last_update_login,
1566 request_id,
1567 program_application_id,
1568 program_id,
1569 program_update_date
1570 )
1571 SELECT wmti1.transaction_id, -- transaction_id
1572 l_errMsg, -- error_message
1573 'TRANSACTION_ID', -- error_column
1574 SYSDATE, -- last_update_date
1575 NVL(wmti1.last_updated_by, -1),
1576 SYSDATE, -- creation_date
1577 NVL(wmti1.created_by, -1),
1578 wmti1.last_update_login,
1579 wmti1.request_id,
1580 wmti1.program_application_id,
1581 wmti1.program_id,
1582 wmti1.program_update_date
1583 FROM wip_move_txn_interface wmti1
1584 WHERE wmti1.group_id = g_group_id
1585 AND wmti1.process_phase = WIP_CONSTANTS.MOVE_VAL
1586 AND wmti1.process_status = WIP_CONSTANTS.RUNNING
1587 AND (EXISTS
1588 (SELECT 'X'
1589 FROM wip_move_transactions wmt
1590 WHERE wmt.transaction_id = wmti1.transaction_id)
1591 OR
1592 (1 <>
1593 (SELECT count(*)
1594 FROM wip_move_txn_interface wmti2
1595 WHERE wmti2.transaction_id = wmti1.transaction_id)));
1596
1597 END transaction_id;
1598
1599 -- derive primary_quantity from transaction_quantity and transaction_uom.
1600 -- you cannot easy return more than available quantity and organization do
1601 -- not allow negative balance
1602 PROCEDURE primary_qty IS
1603
1604 CURSOR c_availQty IS
1605 SELECT wmti.transaction_id txn_id,
1606 wmti.organization_id org_id,
1607 wmti.primary_item_id item_id,
1608 wmti.primary_quantity primary_qty,
1609 DECODE(msik.serial_number_control_code,
1610 WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
1611 WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
1612 fnd_api.g_false) is_ser_ctrl,
1613 DECODE(msik.lot_control_code,
1614 WIP_CONSTANTS.LOT, fnd_api.g_true,
1615 fnd_api.g_false) is_lot_ctrl,
1616 DECODE(msik.revision_qty_control_code,
1617 WIP_CONSTANTS.REV, fnd_api.g_true,
1618 fnd_api.g_false) is_rev_ctrl,
1619 DECODE(msik.revision_qty_control_code, -- revision
1620 WIP_CONSTANTS.REV, NVL(wdj.bom_revision,
1621 bom_revisions.get_item_revision_fn
1622 ('EXCLUDE_OPEN_HOLD', -- eco_status
1623 'ALL', -- examine_type
1624 wmti.organization_id, -- org_id
1625 wmti.primary_item_id, -- item_id
1626 wmti.transaction_date -- rev_date
1627 )),
1628 NULL) revision, -- revision
1629 wdj.lot_number lot,
1630 wmti.transaction_date txn_date,
1631 wdj.completion_subinventory subinv,
1632 wdj.completion_locator_id locID,
1633 mp.negative_inv_receipt_code negative_allow,
1634 msik.concatenated_segments assembly_name
1635 FROM wip_discrete_jobs wdj,
1636 mtl_system_items_kfv msik,
1637 mtl_parameters mp,
1638 wip_move_txn_interface wmti
1639 WHERE wdj.primary_item_id = msik.inventory_item_id
1640 AND wdj.organization_id = msik.organization_id
1641 AND wdj.organization_id = mp.organization_id
1642 AND wmti.wip_entity_id = wdj.wip_entity_id
1643 AND wmti.organization_id = wdj.organization_id
1644 AND wmti.entity_type <> WIP_CONSTANTS.REPETITIVE
1645 AND wmti.group_id = g_group_id
1646 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1647 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1648 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
1649
1650
1651 CURSOR c_repAvailQty IS
1652 SELECT wmti.transaction_id txn_id,
1653 wmti.organization_id org_id,
1654 wmti.primary_item_id item_id,
1655 wmti.primary_quantity primary_qty,
1656 DECODE(msik.serial_number_control_code,
1657 WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
1658 WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
1659 fnd_api.g_false) is_ser_ctrl,
1660 DECODE(msik.lot_control_code,
1661 WIP_CONSTANTS.LOT, fnd_api.g_true,
1662 fnd_api.g_false) is_lot_ctrl,
1663 DECODE(msik.revision_qty_control_code,
1664 WIP_CONSTANTS.REV, fnd_api.g_true,
1665 fnd_api.g_false) is_rev_ctrl,
1666 DECODE(msik.revision_qty_control_code, -- revision
1667 WIP_CONSTANTS.REV, NVL(wrs.bom_revision,
1668 bom_revisions.get_item_revision_fn
1669 ('EXCLUDE_OPEN_HOLD', -- eco_status
1670 'ALL', -- examine_type
1671 wmti.organization_id, -- org_id
1672 wmti.primary_item_id, -- item_id
1673 wmti.transaction_date -- rev_date
1674 )),
1675 NULL) revision, -- revision
1676 NULL lot,
1677 wmti.transaction_date txn_date,
1678 wri.completion_subinventory subinv,
1679 wri.completion_locator_id locID,
1680 mp.negative_inv_receipt_code negative_allow,
1681 msik.concatenated_segments assembly_name
1682 FROM wip_repetitive_schedules wrs,
1683 wip_repetitive_items wri,
1684 mtl_system_items_kfv msik,
1685 mtl_parameters mp,
1686 wip_move_txn_interface wmti
1687 WHERE wmti.primary_item_id = msik.inventory_item_id
1688 AND wmti.organization_id = msik.organization_id
1689 AND wmti.organization_id = mp.organization_id
1690 AND wrs.wip_entity_id = wmti.wip_entity_id
1691 AND wrs.organization_id = wmti.organization_id
1692 AND wrs.line_id = wmti.line_id
1693 AND wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
1694 AND wri.organization_id = wmti.organization_id
1695 AND wri.wip_entity_id = wmti.wip_entity_id
1696 AND wri.line_id = wmti.line_id
1697 AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
1698 AND wmti.group_id = g_group_id
1699 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1700 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1701 AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
1702
1703 l_availQty c_availQty%ROWTYPE;
1704 l_repAvailQty c_repAvailQty%ROWTYPE;
1705 l_returnStatus VARCHAR2(1);
1706 l_qoh NUMBER;
1707 l_rqoh NUMBER;
1708 l_qr NUMBER;
1709 l_qs NUMBER;
1710 l_att NUMBER;
1711 l_atr NUMBER;
1712 l_errMsg VARCHAR2(240);
1713 l_msg_count NUMBER;
1714 l_msg_data VARCHAR2(2000);
1715 BEGIN
1716 -- Derive PRIMARY_QUANTITY from TRANSACTION_QUANTITY and TRANSACTION_UOM
1717 -- if PRIMARY_QUANTITY is null
1718
1719 /** Bug fix 5000113. primary_quantity should be updated in sync with
1720 * transaction_quantity, and not just when primary_quantity is null.
1721 */
1722
1723 UPDATE wip_move_txn_interface wmti
1724 SET wmti.primary_quantity =
1725 (SELECT ROUND(wmti.transaction_quantity * mucv.conversion_rate,
1726 WIP_CONSTANTS.INV_MAX_PRECISION)
1727 FROM mtl_uom_conversions_view mucv
1728 WHERE mucv.organization_id = wmti.organization_id
1729 AND mucv.inventory_item_id = wmti.primary_item_id
1730 AND mucv.uom_code = wmti.transaction_uom)
1731 WHERE wmti.group_id = g_group_id
1732 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1733 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
1734 --AND wmti.primary_quantity IS NULL;
1735
1736 /* End of bug fix 5000113.
1737
1738 -- Set Error Message
1739 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
1740 fnd_message.set_token('ENTITY1', 'PRIMARY_QUANTITY');
1741 fnd_message.set_token('ENTITY2', 'zero');
1742 l_errMsg := substrb(fnd_message.get, 1, 240);
1743
1744 -- Error out if PRIMARY_QUANTITY is zero
1745 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
1746 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
1747 -- continue validating other values.
1748 INSERT INTO wip_txn_interface_errors(
1749 transaction_id,
1750 error_message,
1751 error_column,
1752 last_update_date,
1753 last_updated_by,
1754 creation_date,
1755 created_by,
1756 last_update_login,
1757 request_id,
1758 program_application_id,
1759 program_id,
1760 program_update_date
1761 )
1762 SELECT wmti.transaction_id, -- transaction_id
1763 l_errMsg, -- error_message
1764 'PRIMARY_QUANTITY', -- error_column
1765 SYSDATE, -- last_update_date
1766 NVL(wmti.last_updated_by, -1),
1767 SYSDATE, -- creation_date
1768 NVL(wmti.created_by, -1),
1769 wmti.last_update_login,
1770 wmti.request_id,
1771 wmti.program_application_id,
1772 wmti.program_id,
1773 wmti.program_update_date
1774 FROM wip_move_txn_interface wmti
1775 WHERE wmti.group_id = g_group_id
1776 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1777 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1778 AND wmti.primary_quantity = 0;
1779
1780 -- Set Error Message
1781 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
1782 fnd_message.set_token('ENTITY1', 'PRIMARY_QUANTITY');
1783 fnd_message.set_token('ENTITY2', 'TRANSACTION_QUANTITY');
1784 l_errMsg := substrb(fnd_message.get, 1, 240);
1785
1786 -- Error out if PRIMARY_QUANTITY is not consistent with TRANSACTION_QUANTITY
1787 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
1788 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
1789 -- continue validating other values.
1790 INSERT INTO wip_txn_interface_errors(
1791 transaction_id,
1792 error_message,
1793 error_column,
1794 last_update_date,
1795 last_updated_by,
1796 creation_date,
1797 created_by,
1798 last_update_login,
1799 request_id,
1800 program_application_id,
1801 program_id,
1802 program_update_date
1803 )
1804 SELECT wmti.transaction_id, -- transaction_id
1805 l_errMsg, -- error_message
1806 'PRIMARY_QUANTITY', -- error_column
1807 SYSDATE, -- last_update_date
1808 NVL(wmti.last_updated_by, -1),
1809 SYSDATE, -- creation_date
1810 NVL(wmti.created_by, -1),
1811 wmti.last_update_login,
1812 wmti.request_id,
1813 wmti.program_application_id,
1814 wmti.program_id,
1815 wmti.program_update_date
1816 FROM wip_move_txn_interface wmti,
1817 mtl_uom_conversions_view mucv
1818 WHERE mucv.organization_id = wmti.organization_id
1819 AND mucv.inventory_item_id = wmti.primary_item_id
1820 AND mucv.uom_code = wmti.transaction_uom
1821 AND wmti.group_id = g_group_id
1822 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1823 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1824 -- Fixed bug 4900010. Round both transaction_quantity and
1825 -- primary_quantity before making comparison.
1826 AND ROUND(wmti.transaction_quantity * mucv.conversion_rate,
1827 WIP_CONSTANTS.INV_MAX_PRECISION) <>
1828 ROUND(wmti.primary_quantity, WIP_CONSTANTS.INV_MAX_PRECISION);
1829
1830 -- Set Error Message
1831 /* Fix for Bug#4192541. Removed following check as this is only warning
1832 condition
1833 */
1834
1835 /*
1836 fnd_message.set_name('WIP', 'WIP_MIN_XFER_QTY');
1837 l_errMsg := substrb(fnd_message.get, 1, 240);
1838
1839 -- Error out if PRIMARY_QUANTITY less than MININUM_TRANSFER_QUANTITY
1840 -- defined at FM_OPERATION_SEQ_NUM and transactions are not Scrap/Reject
1841 INSERT INTO wip_txn_interface_errors(
1842 transaction_id,
1843 error_message,
1844 error_column,
1845 last_update_date,
1846 last_updated_by,
1847 creation_date,
1848 created_by,
1849 last_update_login,
1850 request_id,
1851 program_application_id,
1852 program_id,
1853 program_update_date
1854 )
1855 SELECT wmti.transaction_id, -- transaction_id
1856 l_errMsg, -- error_message
1857 'PRIMARY_QUANTITY', -- error_column
1858 SYSDATE, -- last_update_date
1859 NVL(wmti.last_updated_by, -1),
1860 SYSDATE, -- creation_date
1861 NVL(wmti.created_by, -1),
1862 wmti.last_update_login,
1863 wmti.request_id,
1864 wmti.program_application_id,
1865 wmti.program_id,
1866 wmti.program_update_date
1867 FROM wip_move_txn_interface wmti,
1868 wip_operations wo
1869 WHERE wo.organization_id = wmti.organization_id
1870 AND wo.wip_entity_id = wmti.wip_entity_id
1871 AND NVL(wo.repetitive_schedule_id, -1) =
1872 NVL(wmti.repetitive_schedule_id, -1)
1873 AND wo.operation_seq_num = wmti.fm_operation_seq_num
1874 AND wmti.fm_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
1875 WIP_CONSTANTS.REJECT)
1876 AND wmti.to_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
1877 WIP_CONSTANTS.REJECT)
1878 AND wmti.group_id = g_group_id
1879 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1880 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1881 AND wo.minimum_transfer_quantity > wmti.primary_quantity;
1882
1883
1884 */
1885 -- Set Error Message
1886 fnd_message.set_name ('INV', 'INV_NO_NEG_BALANCES');
1887 l_errMsg := substrb(fnd_message.get, 1, 240);
1888
1889 -- User cannot do easy return more than available quantity if
1890 -- organization do not allow negative balance. (Discrete/OSFM)
1891 FOR l_availQty IN c_availQty
1892 LOOP
1893 inv_quantity_tree_pub.query_quantities(
1894 p_api_version_number => 1.0,
1895 p_init_msg_lst => 'T',
1896 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
1897 p_organization_id => l_availQty.org_id,
1898 p_inventory_item_id => l_availQty.item_id,
1899 p_tree_mode => inv_quantity_tree_pvt.g_loose_only_mode,
1900 p_is_revision_control => fnd_api.to_boolean(l_availQty.is_rev_ctrl),
1901 p_is_lot_control => fnd_api.to_boolean(l_availQty.is_lot_ctrl),
1902 p_is_serial_control => fnd_api.to_boolean(l_availQty.is_ser_ctrl),
1903 p_demand_source_type_id => 5, -- WIP
1904 p_revision => l_availQty.revision,
1905 p_lot_number => l_availQty.lot,
1906 p_lot_expiration_date => l_availQty.txn_date,
1907 p_subinventory_code => l_availQty.subinv,
1908 p_locator_id => l_availQty.locID,
1909 x_return_status => l_returnStatus,
1910 x_msg_count => l_msg_count,
1911 x_msg_data => l_msg_data,
1912 x_qoh => l_qoh,
1913 x_rqoh => l_rqoh,
1914 x_qr => l_qr,
1915 x_qs => l_qs,
1916 x_att => l_att,
1917 x_atr => l_atr);
1918
1919 IF(l_returnStatus <> 'S')THEN
1920 add_error(p_txn_id => l_availQty.txn_id,
1921 p_err_col => 'PRIMARY_QUANTITY',
1922 p_err_msg => l_msg_data);
1923 ELSE
1924 IF(l_availQty.negative_allow = WIP_CONSTANTS.NO AND
1925 l_att < l_availQty.primary_qty) THEN
1926 add_error(p_txn_id => l_availQty.txn_id,
1927 p_err_col => 'PRIMARY_QUANTITY',
1928 p_err_msg => l_availQty.assembly_name||':'||l_errMsg);
1929 END IF;
1930 END IF;
1931 END LOOP; -- Only for EZ Return transactions (Discrete/OSFM)
1932
1933 -- User cannot do easy return more than available quantity if
1934 -- organization do not allow negative balance (Repetitive)
1935 FOR l_repAvailQty IN c_repAvailQty
1936 LOOP
1937 inv_quantity_tree_pub.query_quantities(
1938 p_api_version_number => 1.0,
1939 p_init_msg_lst => 'T',
1940 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
1941 p_organization_id => l_repAvailQty.org_id,
1942 p_inventory_item_id => l_repAvailQty.item_id,
1943 p_tree_mode => inv_quantity_tree_pvt.g_loose_only_mode,
1944 p_is_revision_control => fnd_api.to_boolean(l_repAvailQty.is_rev_ctrl),
1945 p_is_lot_control => fnd_api.to_boolean(l_repAvailQty.is_lot_ctrl),
1946 p_is_serial_control => fnd_api.to_boolean(l_repAvailQty.is_ser_ctrl),
1947 p_demand_source_type_id => 5, -- WIP
1948 p_revision => l_repAvailQty.revision,
1949 p_lot_number => l_repAvailQty.lot,
1950 p_lot_expiration_date => l_repAvailQty.txn_date,
1951 p_subinventory_code => l_repAvailQty.subinv,
1952 p_locator_id => l_repAvailQty.locID,
1953 x_return_status => l_returnStatus,
1954 x_msg_count => l_msg_count,
1955 x_msg_data => l_msg_data,
1956 x_qoh => l_qoh,
1957 x_rqoh => l_rqoh,
1958 x_qr => l_qr,
1959 x_qs => l_qs,
1960 x_att => l_att,
1961 x_atr => l_atr);
1962
1963 IF(l_returnStatus <> 'S')THEN
1964 add_error(p_txn_id => l_repAvailQty.txn_id,
1965 p_err_col => 'PRIMARY_QUANTITY',
1966 p_err_msg => l_msg_data);
1967 ELSE
1968 IF(l_repAvailQty.negative_allow = WIP_CONSTANTS.NO AND
1969 l_att < l_repAvailQty.primary_qty) THEN
1970 add_error(p_txn_id => l_repAvailQty.txn_id,
1971 p_err_col => 'PRIMARY_QUANTITY',
1972 p_err_msg => l_repAvailQty.assembly_name||':'||l_errMsg);
1973 END IF;
1974 END IF;
1975 END LOOP; -- Only for EZ Return transactions(Repetitive)
1976 END primary_qty;
1977
1978 -- derive primary_uom from primary_item_id
1979 PROCEDURE primary_uom IS
1980 l_errMsg VARCHAR2(240);
1981 BEGIN
1982 -- Derive PRIMARY_UOM from PRIMARY_ITEM_ID provided if PRIMARY_UOM is null
1983 UPDATE wip_move_txn_interface wmti
1984 SET wmti.primary_uom =
1985 (SELECT msi.primary_uom_code
1986 FROM mtl_system_items msi
1987 WHERE msi.organization_id = wmti.organization_id
1988 AND msi.inventory_item_id = wmti.primary_item_id)
1989 WHERE wmti.group_id = g_group_id
1990 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
1991 AND wmti.process_status = WIP_CONSTANTS.RUNNING
1992 AND wmti.primary_uom IS NULL;
1993
1994 -- Set Error Message
1995 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
1996 fnd_message.set_token('ENTITY1', 'PRIMARY_UOM');
1997 fnd_message.set_token('ENTITY2', 'PRIMARY_ITEM_ID');
1998 l_errMsg := substrb(fnd_message.get, 1, 240);
1999
2000 -- If caller provide PRIMARY_UOM, it must be consistent with
2001 -- primary_item_id provided
2002 INSERT INTO wip_txn_interface_errors(
2003 transaction_id,
2004 error_message,
2005 error_column,
2006 last_update_date,
2007 last_updated_by,
2008 creation_date,
2009 created_by,
2010 last_update_login,
2011 request_id,
2012 program_application_id,
2013 program_id,
2014 program_update_date
2015 )
2016 SELECT wmti.transaction_id, -- transaction_id
2017 l_errMsg, -- error_message
2018 'PRIMARY_UOM', -- error_column
2019 SYSDATE, -- last_update_date
2020 NVL(wmti.last_updated_by, -1),
2021 SYSDATE, -- creation_date
2022 NVL(wmti.created_by, -1),
2023 wmti.last_update_login,
2024 wmti.request_id,
2025 wmti.program_application_id,
2026 wmti.program_id,
2027 wmti.program_update_date
2028 FROM wip_move_txn_interface wmti,
2029 mtl_system_items msi
2030 WHERE msi.organization_id = wmti.organization_id
2031 AND msi.inventory_item_id = wmti.primary_item_id
2032 AND wmti.group_id = g_group_id
2033 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2034 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2035 AND msi.primary_uom_code <> wmti.primary_uom;
2036
2037 END primary_uom;
2038
2039 -- derive overcomplete_primary_quantity from overcomplete_transaction_quantity
2040 -- and transaction_uom provided.
2041 PROCEDURE ocpl_primary_qty IS
2042 l_errMsg VARCHAR2(240);
2043 BEGIN
2044 -- Derive OVERCOMPLETE_PRIMARY_QUANTITY from
2045 -- OVERCOMPLETE_TRANSACTION_QUANTITY and TRANSACTION_UOM provided.
2046
2047 /** Bug fix 5000113. overcompletion_primary_qty should be updated in sync with
2048 * transaction_quantity, and not just when overcompletion_quantity is null.
2049 */
2050
2051 UPDATE wip_move_txn_interface wmti
2052 SET wmti.overcompletion_primary_qty =
2053 (SELECT ROUND(wmti.overcompletion_transaction_qty *
2054 mucv.conversion_rate, WIP_CONSTANTS.INV_MAX_PRECISION)
2055 FROM mtl_uom_conversions_view mucv
2056 WHERE mucv.organization_id = wmti.organization_id
2057 AND mucv.inventory_item_id = wmti.primary_item_id
2058 AND mucv.uom_code = wmti.transaction_uom)
2059 WHERE wmti.group_id = g_group_id
2060 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2061 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2062 AND wmti.overcompletion_transaction_qty IS NOT NULL;
2063 --AND wmti.overcompletion_primary_qty IS NULL;
2064
2065 -- End of bug fix 5000113.
2066
2067 -- Set Error Message
2068 fnd_message.set_name('MFG', 'MFG_GREATER_THAN');
2069 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_PRIMARY_QTY');
2070 fnd_message.set_token('ENTITY2', 'zero');
2071 l_errMsg := substrb(fnd_message.get, 1, 240);
2072
2073 -- Error out if OVERCOMPLETION_PRIMARY_QTY is zero
2074 -- Insert error record into WIP_TXN_INTERFACE_ERRORS. Do not update
2075 -- WMTI.PROCESS_STATUS to Error because it is a minor issue. We will
2076 -- continue validating other values.
2077 INSERT INTO wip_txn_interface_errors(
2078 transaction_id,
2079 error_message,
2080 error_column,
2081 last_update_date,
2082 last_updated_by,
2083 creation_date,
2084 created_by,
2085 last_update_login,
2086 request_id,
2087 program_application_id,
2088 program_id,
2089 program_update_date
2090 )
2091 SELECT wmti.transaction_id, -- transaction_id
2092 l_errMsg, -- error_message
2093 'OVERCOMPLETION_PRIMARY_QTY', -- error_column
2094 SYSDATE, -- last_update_date
2095 NVL(wmti.last_updated_by, -1),
2096 SYSDATE, -- creation_date
2097 NVL(wmti.created_by, -1),
2098 wmti.last_update_login,
2099 wmti.request_id,
2100 wmti.program_application_id,
2101 wmti.program_id,
2102 wmti.program_update_date
2103 FROM wip_move_txn_interface wmti
2104 WHERE wmti.group_id = g_group_id
2105 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2106 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2107 AND wmti.overcompletion_primary_qty = 0;
2108
2109 -- Set Error Message
2110 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2111 fnd_message.set_token('ENTITY1', 'OVERCOMPLETION_PRIMARY_QTY');
2112 fnd_message.set_token('ENTITY2', 'OVERCOMPLETION_TRANSACTION_QTY');
2113 l_errMsg := substrb(fnd_message.get, 1, 240);
2114
2115 -- If caller provide this info, it must be consistent with
2116 -- overcompletion_transaction_qty provided
2117 INSERT INTO wip_txn_interface_errors(
2118 transaction_id,
2119 error_message,
2120 error_column,
2121 last_update_date,
2122 last_updated_by,
2123 creation_date,
2124 created_by,
2125 last_update_login,
2126 request_id,
2127 program_application_id,
2128 program_id,
2129 program_update_date
2130 )
2131 SELECT wmti.transaction_id, -- transaction_id
2132 l_errMsg, -- error_message
2133 'OVERCOMPLETION_PRIMARY_QTY', -- error_column
2134 SYSDATE, -- last_update_date
2135 NVL(wmti.last_updated_by, -1),
2136 SYSDATE, -- creation_date
2137 NVL(wmti.created_by, -1),
2138 wmti.last_update_login,
2139 wmti.request_id,
2140 wmti.program_application_id,
2141 wmti.program_id,
2142 wmti.program_update_date
2143 FROM wip_move_txn_interface wmti,
2144 mtl_uom_conversions_view mucv
2145 WHERE mucv.organization_id = wmti.organization_id
2146 AND mucv.inventory_item_id = wmti.primary_item_id
2147 AND mucv.uom_code = wmti.transaction_uom
2148 AND wmti.group_id = g_group_id
2149 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2150 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2151 AND wmti.overcompletion_transaction_qty IS NOT NULL
2152 -- Fixed bug 4900010. Round both transaction_quantity and
2153 -- primary_quantity before making comparison.
2154 AND ROUND(wmti.overcompletion_transaction_qty * mucv.conversion_rate,
2155 WIP_CONSTANTS.INV_MAX_PRECISION) <>
2156 ROUND(wmti.overcompletion_primary_qty,
2157 WIP_CONSTANTS.INV_MAX_PRECISION);
2158 END ocpl_primary_qty;
2159
2160 -- This value must be null. The move processor will be the one who insert
2161 -- child record and link it with parent record
2162 PROCEDURE ocpl_txn_id IS
2163 l_errMsg VARCHAR2(240);
2164 BEGIN
2165 -- Set Error Message
2166 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
2167 fnd_message.set_token('ENTITY', 'OVERCOMPLETION_TRANSACTION_ID');
2168 l_errMsg := substrb(fnd_message.get, 1, 240);
2169
2170 -- This value must be null because New Move Processor will be the one
2171 -- who insert child record and populate this value
2172 INSERT INTO wip_txn_interface_errors(
2173 transaction_id,
2174 error_message,
2175 error_column,
2176 last_update_date,
2177 last_updated_by,
2178 creation_date,
2179 created_by,
2180 last_update_login,
2181 request_id,
2182 program_application_id,
2183 program_id,
2184 program_update_date
2185 )
2186 SELECT wmti.transaction_id, -- transaction_id
2187 l_errMsg, -- error_message
2188 'OVERCOMPLETION_TRANSACTION_ID', -- error_column
2189 SYSDATE, -- last_update_date
2190 NVL(wmti.last_updated_by, -1),
2191 SYSDATE, -- creation_date
2192 NVL(wmti.created_by, -1),
2193 wmti.last_update_login,
2194 wmti.request_id,
2195 wmti.program_application_id,
2196 wmti.program_id,
2197 wmti.program_update_date
2198 FROM wip_move_txn_interface wmti
2199 WHERE wmti.group_id = g_group_id
2200 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2201 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2202 AND wmti.overcompletion_transaction_id IS NOT NULL;
2203
2204 END ocpl_txn_id;
2205
2206 -- This is an optional info. However, if the caller provided some values,
2207 -- it must be valid. If the caller pass reason_name, we will derive the
2208 -- reason_id. If the caller pass both, both value must be consistent to
2209 -- each other.
2210 PROCEDURE reason_id IS
2211 l_errMsg VARCHAR2(240);
2212 BEGIN
2213 -- Derive REASON_ID from REASON_NAME provided
2214 UPDATE wip_move_txn_interface wmti
2215 SET wmti.reason_id =
2216 (SELECT mtr.reason_id
2217 FROM mtl_transaction_reasons mtr
2218 WHERE mtr.reason_name = wmti.reason_name
2219 AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE)
2220 WHERE wmti.group_id = g_group_id
2221 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2222 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2223 AND wmti.reason_id IS NULL
2224 AND wmti.reason_name IS NOT NULL;
2225
2226 -- Set Error Message
2227 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2228 fnd_message.set_token('ENTITY1', 'REASON_ID');
2229 fnd_message.set_token('ENTITY2', 'REASON_NAME');
2230 l_errMsg := substrb(fnd_message.get, 1, 240);
2231
2232 -- If caller provide REASON_ID, it must be consistent with
2233 -- REASON_NAME provided
2234 INSERT INTO wip_txn_interface_errors(
2235 transaction_id,
2236 error_message,
2237 error_column,
2238 last_update_date,
2239 last_updated_by,
2240 creation_date,
2241 created_by,
2242 last_update_login,
2243 request_id,
2244 program_application_id,
2245 program_id,
2246 program_update_date
2247 )
2248 SELECT wmti.transaction_id, -- transaction_id
2249 l_errMsg, -- error_message
2250 'REASON_ID/NAME', -- error_column
2251 SYSDATE, -- last_update_date
2252 NVL(wmti.last_updated_by, -1),
2253 SYSDATE, -- creation_date
2254 NVL(wmti.created_by, -1),
2255 wmti.last_update_login,
2256 wmti.request_id,
2257 wmti.program_application_id,
2258 wmti.program_id,
2259 wmti.program_update_date
2260 FROM wip_move_txn_interface wmti
2261 WHERE wmti.group_id = g_group_id
2262 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2263 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2264 AND (wmti.reason_id IS NOT NULL OR wmti.reason_name IS NOT NULL)
2265 AND NOT EXISTS
2266 (SELECT 'X'
2267 FROM mtl_transaction_reasons mtr
2268 WHERE mtr.reason_id = NVL(wmti.reason_id, mtr.reason_id)
2269 AND mtr.reason_name = NVL(wmti.reason_name, mtr.reason_name)
2270 AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE);
2271
2272 END reason_id;
2273
2274 -- validate scrap_account_id. This value can be either required or optional
2275 -- info for the discrete and repetitive scrap transaction. It depends on the
2276 -- value setup in WIP_PARAMETERS. However it is always an optional info for
2277 -- OSFM txns. If the caller provided this info, it must be valid account_id
2278 PROCEDURE scrap_account_id IS
2279 l_scrap_flag NUMBER;
2280 l_errMsg VARCHAR2(240);
2281 BEGIN
2282 -- Set Error Message
2283 fnd_message.set_name('WIP', 'WIP_NOT_VALID');
2284 fnd_message.set_token('ENTITY', 'SCRAP_ACCOUNT_ID');
2285 l_errMsg := substrb(fnd_message.get, 1, 240);
2286
2287 INSERT INTO wip_txn_interface_errors(
2288 transaction_id,
2289 error_message,
2290 error_column,
2291 last_update_date,
2292 last_updated_by,
2293 creation_date,
2294 created_by,
2295 last_update_login,
2296 request_id,
2297 program_application_id,
2298 program_id,
2299 program_update_date
2300 )
2301 SELECT wmti.transaction_id, -- transaction_id
2302 l_errMsg, -- error_message
2303 'SCRAP_ACCOUNT_ID', -- error_column
2304 SYSDATE, -- last_update_date
2305 NVL(wmti.last_updated_by, -1),
2306 SYSDATE, -- creation_date
2307 NVL(wmti.created_by, -1),
2308 wmti.last_update_login,
2309 wmti.request_id,
2310 wmti.program_application_id,
2311 wmti.program_id,
2312 wmti.program_update_date
2313 FROM wip_move_txn_interface wmti,
2314 wip_parameters wp
2315 WHERE wp.organization_id = wmti.organization_id
2316 AND wmti.group_id = g_group_id
2317 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2318 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2319 AND (wmti.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP OR
2320 wmti.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP)
2321 AND ((wmti.scrap_account_id IS NULL
2322 AND wp.mandatory_scrap_flag = WIP_CONSTANTS.YES
2323 AND wmti.entity_type NOT IN(WIP_CONSTANTS.LOTBASED,
2324 WIP_CONSTANTS.CLOSED_OSFM))
2325 OR
2326 (wmti.scrap_account_id IS NOT NULL AND
2327 NOT EXISTS
2328 (SELECT 'X'
2329 FROM hr_organization_information hoi,
2330 gl_sets_of_books gsob,
2331 gl_code_combinations gcc
2332 WHERE gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
2333 and gsob.set_of_books_id =
2334 to_number(decode(rtrim(translate(
2335 hoi.org_information1,'0123456789',' ')),
2336 null, hoi.org_information1,
2337 -99999))
2338 and (hoi.org_information_context || '') =
2339 'Accounting Information'
2340 AND hoi.organization_id = wmti.organization_id
2341 AND gcc.code_combination_id = wmti.scrap_account_id
2342 AND gcc.detail_posting_allowed_flag = 'Y'
2343 AND gcc.summary_flag = 'N'
2344 and gcc.enabled_flag = 'Y'
2345 AND TRUNC(wmti.transaction_date) BETWEEN
2346 NVL(gcc.start_date_active,
2347 TRUNC(wmti.transaction_date))
2348 AND NVL(gcc.end_date_active,
2349 TRUNC(wmti.transaction_date)))));
2350
2351 END scrap_account_id;
2352
2353 -- validate last_updated_by against fnd_user table. The caller have an option
2354 -- to provide either last_updated_by or last_updated_by_name. If the caller
2355 -- pass last_updated_by, the id need to be valid. If the caller pass
2356 -- last_updated_by_name, we will derive the ID. If the caller pass both
2357 -- both value must be consistent to each other.
2358 PROCEDURE last_updated_by IS
2359 l_errMsg VARCHAR2(240);
2360 BEGIN
2361 -- Derive LAST_UPDATED_BY if user provided only LAST_UPDATED_BY_NAME
2362 UPDATE wip_move_txn_interface wmti
2363 SET wmti.last_updated_by =
2364 (SELECT fu.user_id
2365 FROM fnd_user fu
2366 WHERE fu.user_name = wmti.last_updated_by_name
2367 AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
2368 WHERE wmti.group_id = g_group_id
2369 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2370 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2371 AND wmti.last_updated_by IS NULL
2372 AND wmti.last_updated_by_name IS NOT NULL;
2373
2374 -- Set Error Message
2375 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2376 fnd_message.set_token('ENTITY1', 'LAST_UPDATED_BY');
2377 fnd_message.set_token('ENTITY2', 'LAST_UPDATED_BY_NAME');
2378 l_errMsg := substrb(fnd_message.get, 1, 240);
2379
2380 -- Error out if LAST_UPDATED_BY is not consistent with LAST_UPDATED_BY_NAME
2381 INSERT INTO wip_txn_interface_errors(
2382 transaction_id,
2383 error_message,
2384 error_column,
2385 last_update_date,
2386 last_updated_by,
2387 creation_date,
2388 created_by,
2389 last_update_login,
2390 request_id,
2391 program_application_id,
2392 program_id,
2393 program_update_date
2394 )
2395 SELECT wmti.transaction_id, -- transaction_id
2396 l_errMsg, -- error_message
2397 'LAST_UPDATED_BY/BY_NAME', -- error_column
2398 SYSDATE, -- last_update_date
2399 NVL(wmti.last_updated_by, -1),
2400 SYSDATE, -- creation_date
2401 NVL(wmti.created_by, -1),
2402 wmti.last_update_login,
2403 wmti.request_id,
2404 wmti.program_application_id,
2405 wmti.program_id,
2406 wmti.program_update_date
2407 FROM wip_move_txn_interface wmti
2408 WHERE wmti.group_id = g_group_id
2409 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2410 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2411 AND wmti.last_updated_by IS NULL; -- cannot derive LAST_UPDATED_BY
2412
2413 END last_updated_by;
2414
2415 -- validate created_by against fnd_user table. The caller have an option
2416 -- to provide either created_by or created_by_name. If the caller
2417 -- pass created_by, the id need to be valid. If the caller pass
2418 -- created_by_name, we will derive the ID. If the caller pass both
2419 -- both value must be consistent to each other.
2420 PROCEDURE created_by IS
2421 l_errMsg VARCHAR2(240);
2422 BEGIN
2423 -- Derive CREATED_BY if user provided only CREATED_BY_NAME
2424 UPDATE wip_move_txn_interface wmti
2425 SET wmti.created_by =
2426 (SELECT fu.user_id
2427 FROM fnd_user fu
2428 WHERE fu.user_name = wmti.created_by_name
2429 AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
2430 WHERE wmti.group_id = g_group_id
2431 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2432 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2433 AND wmti.created_by IS NULL
2434 AND wmti.created_by_name IS NOT NULL;
2435
2436 -- Set Error Message
2437 fnd_message.set_name('WIP', 'WIP_ID_CODE_COMBINATION');
2438 fnd_message.set_token('ENTITY1', 'CREATED_BY');
2439 fnd_message.set_token('ENTITY2', 'CREATED_BY_NAME');
2440 l_errMsg := substrb(fnd_message.get, 1, 240);
2441
2442 -- Error out if CREATED_BY is not consistent with CREATED_BY_NAME
2443 INSERT INTO wip_txn_interface_errors(
2444 transaction_id,
2445 error_message,
2446 error_column,
2447 last_update_date,
2448 last_updated_by,
2449 creation_date,
2450 created_by,
2451 last_update_login,
2452 request_id,
2453 program_application_id,
2454 program_id,
2455 program_update_date
2456 )
2457 SELECT wmti.transaction_id, -- transaction_id
2458 l_errMsg, -- error_message
2459 'CREATED_BY/BY_NAME', -- error_column
2460 SYSDATE, -- last_update_date
2461 NVL(wmti.last_updated_by, -1),
2462 SYSDATE, -- creation_date
2463 NVL(wmti.created_by, -1),
2464 wmti.last_update_login,
2465 wmti.request_id,
2466 wmti.program_application_id,
2467 wmti.program_id,
2468 wmti.program_update_date
2469 FROM wip_move_txn_interface wmti
2470 WHERE wmti.group_id = g_group_id
2471 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2472 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2473 AND ((wmti.created_by IS NULL) -- cannot derive LAST_UPDATED_BY
2474 OR
2475 (NOT EXISTS
2476 (SELECT 'X'
2477 FROM fnd_user fu
2478 WHERE fu.user_name = NVL(wmti.created_by_name,
2479 fu.user_name)
2480 AND fu.user_id = wmti.created_by
2481 AND SYSDATE BETWEEN fu.start_date AND
2482 NVL(fu.end_date, SYSDATE))));
2483 END created_by;
2484
2485 -- This procedure is used to validate osp transactions. User cannot move into
2486 -- a queue of OSP operation unless the department associated to that operation
2487 -- has a location for PO_RECEIVE. For PO_MOVE the department associated with
2488 -- the next operation after to_op must have location. If to_op is the last op
2489 -- , the department associated to that operation must have location.
2490 -- The user must be a valid employee to perform osp transactions.
2491 PROCEDURE osp_validation IS
2492 l_errMsg VARCHAR2(240);
2493 BEGIN
2494 -- Set Error Message
2495 fnd_message.set_name('WIP', 'WIP_PO_MOVE_LOCATION');
2496 l_errMsg := substrb(fnd_message.get, 1, 240);
2497
2498 -- Error out if user try to move into a queue of OSP operation and the
2499 -- department associated to that operation does not have a location
2500 -- for PO_RECEIVE. For PO_MOVE the department associated with
2501 -- the next operation after to_op must have location. If to_op is the last op
2502 -- the department associated to that operation must have location.
2503 INSERT INTO wip_txn_interface_errors(
2504 transaction_id,
2505 error_message,
2506 error_column,
2507 last_update_date,
2508 last_updated_by,
2509 creation_date,
2510 created_by,
2511 last_update_login,
2512 request_id,
2513 program_application_id,
2514 program_id,
2515 program_update_date
2516 )
2517 SELECT wmti.transaction_id, -- transaction_id
2518 l_errMsg, -- error_message
2519 'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
2520 SYSDATE, -- last_update_date
2521 NVL(wmti.last_updated_by, -1),
2522 SYSDATE, -- creation_date
2523 NVL(wmti.created_by, -1),
2524 wmti.last_update_login,
2525 wmti.request_id,
2526 wmti.program_application_id,
2527 wmti.program_id,
2528 wmti.program_update_date
2529 FROM wip_move_txn_interface wmti
2530 WHERE wmti.group_id = g_group_id
2531 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2532 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2533 AND EXISTS
2534 (SELECT 'X'
2535 FROM bom_departments bd,
2536 wip_operation_resources wor,
2537 wip_operations wo1,
2538 wip_operations wo2
2539 WHERE wor.organization_id = wmti.organization_id
2540 AND wor.wip_entity_id = wmti.wip_entity_id
2541 AND wor.operation_seq_num = wmti.to_operation_seq_num
2542 AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
2543 AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
2544 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2545 WIP_CONSTANTS.LOTBASED)
2546 OR
2547 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2548 wor.repetitive_schedule_id IN
2549 (SELECT wrs.repetitive_schedule_id
2550 FROM wip_repetitive_schedules wrs
2551 WHERE wrs.wip_entity_id = wmti.wip_entity_id
2552 AND wrs.organization_id = wmti.organization_id
2553 AND wrs.line_id = wmti.line_id
2554 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
2555 WIP_CONSTANTS.COMP_CHRG))))
2556 AND wo1.organization_id = wor.organization_id
2557 AND wo1.wip_entity_id = wor.wip_entity_id
2558 AND NVL(wo1.repetitive_schedule_id,-1) =
2559 NVL(wor.repetitive_schedule_id,-1)
2560 AND wo1.operation_seq_num = wor.operation_seq_num
2561 AND wo2.organization_id = wo1.organization_id
2562 AND wo2.wip_entity_id = wo1.wip_entity_id
2563 AND NVL(wo2.repetitive_schedule_id,-1) =
2564 NVL(wo1.repetitive_schedule_id,-1)
2565 AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
2566 wo2.operation_seq_num = wor.operation_seq_num)
2567 OR
2568 (wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
2569 ((wo1.next_operation_seq_num IS NOT NULL AND
2570 wo1.next_operation_seq_num = wo2.operation_seq_num)
2571 OR
2572 (wo1.next_operation_seq_num IS NULL AND
2573 wo2.operation_seq_num = wor.operation_seq_num))))
2574 AND bd.organization_id = wmti.organization_id
2575 AND wo2.department_id = bd.department_id
2576 AND bd.location_id IS NULL);
2577
2578 -- Set Error Message
2579 fnd_message.set_name('WIP', 'WIP_VALID_EMPLOYEE');
2580 l_errMsg := substrb(fnd_message.get, 1, 240);
2581
2582 -- Error out if the user who try to do OSP transaction is not an employee
2583 INSERT INTO wip_txn_interface_errors(
2584 transaction_id,
2585 error_message,
2586 error_column,
2587 last_update_date,
2588 last_updated_by,
2589 creation_date,
2590 created_by,
2591 last_update_login,
2592 request_id,
2593 program_application_id,
2594 program_id,
2595 program_update_date
2596 )
2597 SELECT wmti.transaction_id, -- transaction_id
2598 l_errMsg, -- error_message
2599 'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
2600 SYSDATE, -- last_update_date
2601 NVL(wmti.last_updated_by, -1),
2602 SYSDATE, -- creation_date
2603 NVL(wmti.created_by, -1),
2604 wmti.last_update_login,
2605 wmti.request_id,
2606 wmti.program_application_id,
2607 wmti.program_id,
2608 wmti.program_update_date
2609 FROM wip_move_txn_interface wmti
2610 WHERE wmti.group_id = g_group_id
2611 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2612 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2613 AND EXISTS
2614 (SELECT 'Outside processing resources exist'
2615 FROM wip_operation_resources wor
2616 WHERE wor.organization_id = wmti.organization_id
2617 AND wor.wip_entity_id = wmti.wip_entity_id
2618 AND wor.operation_seq_num = wmti.to_operation_seq_num
2619 AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
2620 AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
2621 AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
2622 WIP_CONSTANTS.PO_MOVE)
2623 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2624 WIP_CONSTANTS.LOTBASED)
2625 OR
2626 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2627 wor.repetitive_schedule_id IN
2628 (SELECT wrs.repetitive_schedule_id
2629 FROM wip_repetitive_schedules wrs
2630 WHERE wrs.organization_id = wmti.organization_id
2631 AND wrs.wip_entity_id = wmti.wip_entity_id
2632 AND wrs.line_id = wmti.line_id
2633 AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
2634 WIP_CONSTANTS.COMP_CHRG)))))
2635 AND NOT EXISTS
2636 (SELECT 'Current user is an employee'
2637 FROM fnd_user fu,
2638 per_people_f ppf
2639 WHERE fu.user_id = wmti.created_by
2640 AND fu.employee_id = ppf.person_id);
2641
2642 END osp_validation;
2643
2644 -- validate serial related information. This validation is only useful if
2645 -- user try to do background serialized txns.
2646 PROCEDURE serial_validation IS
2647 BEGIN
2648 -- reset enums table
2649 enums.delete;
2650 -- Users cannot move cross 'Queue' of serialization start op. User need to
2651 -- move 2 step. The first time move to Queue of serialization start op, then
2652 -- serial move. For backward move, do serial move first.
2653 UPDATE wip_move_txn_interface wmti
2654 SET wmti.process_status = WIP_CONSTANTS.ERROR
2655 WHERE wmti.group_id = g_group_id
2656 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2657 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2658 AND EXISTS -- serialized job
2659 (SELECT 'X'
2660 FROM wip_discrete_jobs wdj
2661 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2662 AND wdj.serialization_start_op IS NOT NULL
2663 AND (-- Forward move
2664 (wmti.fm_operation_seq_num < wdj.serialization_start_op AND
2665 (wmti.to_operation_seq_num > wdj.serialization_start_op
2666 OR
2667 (wmti.to_operation_seq_num = wdj.serialization_start_op AND
2668 wmti.to_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))
2669 OR
2670 -- Backward move
2671 (wmti.to_operation_seq_num < wdj.serialization_start_op AND
2672 (wmti.fm_operation_seq_num > wdj.serialization_start_op
2673 OR
2674 (wmti.fm_operation_seq_num = wdj.serialization_start_op AND
2675 wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))))
2676 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2677
2678 fnd_message.set_name('WIP', 'WIP_MOVE_CROSS_START_OP');
2679 add_error(p_txn_ids => enums,
2680 p_err_col => 'FM/TO_STEP, FM/TO_OP',
2681 p_err_msg => fnd_message.get);
2682
2683 -- Comment out the validation below because Express Move can be done for more
2684 -- then one quantity. Moreover, this validation was already done through the
2685 -- UI(mobile and MES), and we do not support serilized move in the background.
2686 /*
2687 -- reset enums table
2688 enums.delete;
2689 -- If user try to do serialized transaction, primary_quantity must be 1.
2690 -- This validation is only for serialized discrete job. For serialized
2691 -- OSFM job, primary_quantity can be more than 1.
2692 UPDATE wip_move_txn_interface wmti
2693 SET wmti.process_status = WIP_CONSTANTS.ERROR
2694 WHERE wmti.group_id = g_group_id
2695 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2696 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2697 AND EXISTS -- serialized discrete job and serialized transaction
2698 (SELECT 'X'
2699 FROM wip_discrete_jobs wdj,
2700 wip_entities we
2701 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2702 AND wdj.wip_entity_id = we.wip_entity_id
2703 AND we.entity_type = WIP_CONSTANTS.DISCRETE
2704 AND wdj.serialization_start_op IS NOT NULL
2705 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2706 AND wmti.to_operation_seq_num >= wdj.serialization_start_op
2707 AND wmti.primary_quantity <> 1)
2708 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2709
2710 fnd_message.set_name('WIP', 'WIP_INVALID_SERIAL_QTY');
2711 add_error(p_txn_ids => enums,
2712 p_err_col => 'TRANSACTION/PRIMARY_QUANTITY',
2713 p_err_msg => fnd_message.get);
2714 */
2715
2716 -- reset enums table
2717 enums.delete;
2718 -- if user provide serial number information for non-serialized job, or
2719 -- serialized job with non-serialized move, error out.
2720 UPDATE wip_move_txn_interface wmti
2721 SET wmti.process_status = WIP_CONSTANTS.ERROR
2722 WHERE wmti.group_id = g_group_id
2723 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2724 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2725 AND EXISTS -- regular job
2726 (SELECT 'X'
2727 FROM wip_discrete_jobs wdj
2728 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2729 AND (wdj.serialization_start_op IS NULL -- non-serialized job
2730 OR -- serialized job with non-serialized move
2731 (wdj.serialization_start_op IS NOT NULL
2732 AND
2733 (wmti.fm_operation_seq_num < wdj.serialization_start_op OR
2734 (wmti.fm_operation_seq_num = wdj.serialization_start_op AND
2735 wmti.fm_intraoperation_step_type = WIP_CONSTANTS.QUEUE))
2736 AND
2737 (wmti.to_operation_seq_num < wdj.serialization_start_op OR
2738 (wmti.to_operation_seq_num = wdj.serialization_start_op AND
2739 wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE))))
2740 )
2741 AND EXISTS
2742 (SELECT 'X'
2743 FROM wip_serial_move_interface wsmi
2744 WHERE wsmi.transaction_id = wmti.transaction_id)
2745 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2746
2747 fnd_message.set_name('WIP', 'WIP_SERIAL_INFO_NOT_ALLOW');
2748 add_error(p_txn_ids => enums,
2749 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2750 p_err_msg => fnd_message.get);
2751
2752
2753 -- reset enums table
2754 enums.delete;
2755 -- if user try to do serialized transaction, number of serial records must be
2756 -- equal to wmti.primary_quantity
2757 UPDATE wip_move_txn_interface wmti
2758 SET wmti.process_status = WIP_CONSTANTS.ERROR
2759 WHERE wmti.group_id = g_group_id
2760 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2761 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2762 AND EXISTS -- serialized job and serialized transaction
2763 (SELECT 'X'
2764 FROM wip_discrete_jobs wdj
2765 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2766 AND wdj.serialization_start_op IS NOT NULL
2767 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2768 AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
2769 AND wmti.primary_quantity <>
2770 (SELECT COUNT(*)
2771 FROM wip_serial_move_interface wsmi,
2772 mtl_serial_numbers msn
2773 WHERE wsmi.transaction_id = wmti.transaction_id
2774 AND wsmi.assembly_serial_number = msn.serial_number
2775 AND wmti.organization_id = msn.current_organization_id
2776 AND wmti.primary_item_id = msn.inventory_item_id
2777 AND msn.wip_entity_id IS NOT NULL
2778 AND msn.wip_entity_id = wmti.wip_entity_id)
2779 AND wmti.primary_quantity <>
2780 (SELECT COUNT(*)
2781 FROM wip_serial_move_interface wsmi,
2782 wip_entities we,
2783 mtl_serial_numbers msn,
2784 mtl_object_genealogy mog
2785 WHERE wsmi.transaction_id = wmti.transaction_id
2786 AND wsmi.assembly_serial_number = msn.serial_number
2787 AND wmti.organization_id = msn.current_organization_id
2788 AND wmti.primary_item_id = msn.inventory_item_id
2789 AND msn.current_status = WIP_CONSTANTS.IN_STORES
2790 AND wmti.wip_entity_id = we.wip_entity_id
2791 AND ((mog.genealogy_origin = 1 AND
2792 mog.parent_object_id = we.gen_object_id AND
2793 mog.object_id = msn.gen_object_id)
2794 OR
2795 (mog.genealogy_origin = 2 AND
2796 mog.parent_object_id = msn.gen_object_id AND
2797 mog.object_id = we.gen_object_id))
2798 AND mog.end_date_active IS NULL)
2799 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2800
2801 fnd_message.set_name('WIP', 'WIP_SERIAL_QTY_MISSMATCH');
2802 add_error(p_txn_ids => enums,
2803 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2804 p_err_msg => fnd_message.get);
2805
2806 -- reset enums table
2807 enums.delete;
2808 -- if user try to do serialized transaction, the status of the serial
2809 -- must correspond to the transaction type.
2810 UPDATE wip_move_txn_interface wmti
2811 SET wmti.process_status = WIP_CONSTANTS.ERROR
2812 WHERE wmti.group_id = g_group_id
2813 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2814 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2815 AND EXISTS -- serialized job and serialized transaction
2816 (SELECT 'X'
2817 FROM wip_discrete_jobs wdj
2818 WHERE wdj.wip_entity_id = wmti.wip_entity_id
2819 AND wdj.serialization_start_op IS NOT NULL
2820 AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
2821 AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
2822 AND NOT EXISTS
2823 (SELECT 'X'
2824 FROM wip_serial_move_interface wsmi,
2825 mtl_serial_numbers msn
2826 WHERE wsmi.transaction_id = wmti.transaction_id
2827 AND wsmi.assembly_serial_number = msn.serial_number
2828 AND wmti.organization_id = msn.current_organization_id
2829 AND wmti.primary_item_id = msn.inventory_item_id
2830 AND msn.line_mark_id IS NULL
2831 AND ((wmti.transaction_type = WIP_CONSTANTS.RET_TXN AND
2832 msn.group_mark_id IS NULL AND
2833 msn.wip_entity_id IS NULL AND
2834 msn.current_status = WIP_CONSTANTS.IN_STORES)
2835 OR
2836 (wmti.transaction_type IN (WIP_CONSTANTS.MOVE_TXN,
2837 WIP_CONSTANTS.COMP_TXN) AND
2838 msn.group_mark_id IS NOT NULL AND
2839 msn.wip_entity_id IS NOT NULL AND
2840 wmti.wip_entity_id = msn.wip_entity_id AND
2841 -- Define but not use or Issue out of store.
2842 msn.current_status IN (WIP_CONSTANTS.DEF_NOT_USED,
2843 WIP_CONSTANTS.OUT_OF_STORES))))
2844 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2845
2846 fnd_message.set_name('WIP', 'WIP_INVALID_SERIAL_STATUS');
2847 add_error(p_txn_ids => enums,
2848 p_err_col => 'WSMI.ASSEMBLY_SERIAL_NUMBER',
2849 p_err_msg => fnd_message.get);
2850
2851 END serial_validation;
2852
2853 -- If there are some errors occur, this routine will set
2854 -- PROCESS_STATUS to WIP_CONSTANTS.ERROR. Then it will insert all the errors
2855 -- into WIP_TXN_INTERFACE_ERRORS
2856 PROCEDURE update_interface_tbl IS
2857 BEGIN
2858 -- there are some errors occur, so set the process_status to error so that
2859 -- move processor will not pick up this record
2860 UPDATE wip_move_txn_interface wmti
2861 SET wmti.process_status = WIP_CONSTANTS.ERROR
2862 WHERE wmti.group_id = g_group_id
2863 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2864 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2865 AND EXISTS
2866 (SELECT 'X'
2867 FROM wip_txn_interface_errors wtie
2868 WHERE wtie.transaction_id = wmti.transaction_id);
2869
2870 -- insert error message to WIP_TXN_INTERFACE_ERRORS, and clear error table
2871 load_errors;
2872 END update_interface_tbl;
2873
2874 /* Fixed bug 5056289. Added more validation for assembly to prevent the whole
2875 batch error out when assembly fail inventory validation.
2876 */
2877 -- Validate assembly related information to prevent the whole batch failing
2878 -- if there is something wrong with the assembly like assembly is not
2879 -- transactable, or assembly is not an inventory item. This check is only for
2880 -- EZ Completion and EZ Return.
2881 PROCEDURE assembly_validation IS
2882 BEGIN
2883 -- reset enums table
2884 enums.delete;
2885 -- Users cannot do EZ Completion/EZ Return if an assembly is not transactable
2886 -- or an assembly is not an inventory item.
2887 UPDATE wip_move_txn_interface wmti
2888 SET wmti.process_status = WIP_CONSTANTS.ERROR
2889 WHERE wmti.group_id = g_group_id
2890 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2891 AND wmti.process_status = WIP_CONSTANTS.RUNNING
2892 AND wmti.transaction_type IN (WIP_CONSTANTS.RET_TXN,
2893 WIP_CONSTANTS.COMP_TXN)
2894 AND EXISTS -- Item flag was not set properly.
2895 (SELECT 'X'
2896 FROM mtl_system_items msi
2897 WHERE msi.inventory_item_id = wmti.primary_item_id
2898 AND msi.organization_id = wmti.organization_id
2899 AND (msi.inventory_item_flag = 'N' OR
2900 msi.mtl_transactions_enabled_flag = 'N'))
2901 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
2902
2903 fnd_message.set_name('INV', 'INV_INT_ITMEXP');
2904 add_error(p_txn_ids => enums,
2905 p_err_col => 'PRIMARY_ITEM_ID',
2906 p_err_msg => fnd_message.get);
2907
2908 END assembly_validation;
2909
2910
2911 -- If pass all the validation, and there is no error, this routine will
2912 -- derive all the rest info (fm_operation_code, fm_department_id,
2913 -- fm_department_code, to_operation_code, to_department_id, to_department_code)
2914 -- , then update PROCESS_PHASE to WIP_CONSTANTS.MOVE_PROC. This routine
2915 -- should be called after we called all the validation code and
2916 -- update_interface_tbl
2917 PROCEDURE derive IS
2918 l_PrevOpSeq NUMBER;
2919 l_NextOpSeq NUMBER;
2920 l_OpExists BOOLEAN;
2921 BEGIN
2922
2923 UPDATE wip_move_txn_interface wmti
2924 SET (wmti.fm_operation_code,
2925 wmti.fm_department_id,
2926 wmti.fm_department_code,
2927 wmti.to_operation_code,
2928 wmti.to_department_id,
2929 wmti.to_department_code,
2930 wmti.process_phase) =
2931 (SELECT bso1.operation_code,
2932 wo1.department_id,
2933 bd1.department_code,
2934 bso2.operation_code,
2935 wo2.department_id,
2936 bd2.department_code,
2937 WIP_CONSTANTS.MOVE_PROC
2938 FROM bom_standard_operations bso1,
2939 bom_standard_operations bso2,
2940 bom_departments bd1,
2941 bom_departments bd2,
2942 wip_operations wo1,
2943 wip_operations wo2
2944 WHERE wo1.organization_id = wmti.organization_id
2945 AND wo1.wip_entity_id = wmti.wip_entity_id
2946 AND wo1.operation_seq_num = wmti.fm_operation_seq_num
2947 AND wo2.organization_id = wmti.organization_id
2948 AND wo2.wip_entity_id = wmti.wip_entity_id
2949 AND wo2.operation_seq_num = wmti.to_operation_seq_num
2950 /* Standard operation ID is optional, so we should use outer join */
2951 AND bso1.standard_operation_id(+) = wo1.standard_operation_id
2952 AND bso2.standard_operation_id(+) = wo2.standard_operation_id
2953 AND wo1.department_id = bd1.department_id
2954 AND wo2.department_id = bd2.department_id
2955 AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
2956 WIP_CONSTANTS.LOTBASED)
2957 OR
2958 (wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
2959 wo1.repetitive_schedule_id = wmti.repetitive_schedule_id AND
2960 wo2.repetitive_schedule_id = wmti.repetitive_schedule_id)))
2961 WHERE wmti.group_id = g_group_id
2962 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
2963 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
2964
2965 END derive;
2966
2967 PROCEDURE validate(p_group_id IN NUMBER,
2968 p_initMsgList IN VARCHAR2) IS
2969 l_params wip_logger.param_tbl_t;
2970 l_returnStatus VARCHAR2(1);
2971 l_logLevel NUMBER ;
2972
2973 BEGIN
2974 l_logLevel := fnd_log.g_current_runtime_level;
2975 IF(fnd_api.to_boolean(p_initMsgList)) THEN
2976 fnd_msg_pub.initialize;
2977 END IF;
2978
2979 -- write parameter value to log file
2980 if (l_logLevel <= wip_constants.trace_logging) then
2981 l_params(1).paramName := 'p_group_id';
2982 l_params(1).paramValue := p_group_id;
2983 wip_logger.entryPoint(p_procName => 'wip_move_validator.validate',
2984 p_params => l_params,
2985 x_returnStatus => l_returnStatus);
2986 end if;
2987
2988 -- reset global_variable everytime this routine is called
2989 g_group_id := p_group_id;
2990 enums.delete;
2991 -- Call last_updatd_by and created_by first even if it is a low priority
2992 -- validation because we want to insert last_updated_by and created_by
2993 -- into WIP_TXN_INTERFACE_ERRORS
2994 last_updated_by;
2995 created_by;
2996 /*****************************
2997 * Start critical validation *
2998 *****************************/
2999 -- If any of the procedure below error out, set WMTI.PROCESS_STATUS to
3000 -- Error and stop validation.
3001 /* Bug#2956953 - commented call to organization_id procedure as the validation
3002 for organization_id/organization_code are called from wip move manager code
3003 - Changes done as part of the Wip Move Sequencing Project */
3004 -- organization_id;
3005 wip_entity_id;
3006 transaction_type;
3007 transaction_date;
3008 fm_operation;
3009 fm_step;
3010 to_operation;
3011 to_step;
3012 transaction_qty;
3013 transaction_uom;
3014 ocpl_txn_qty;
3015 /*****************************
3016 * End critical validation *
3017 *****************************/
3018
3019 /*********************************
3020 * Start low priority validation *
3021 *********************************/
3022 -- If any of the procedure below error out, continue validating other
3023 -- low priority validation because we support multiple error message
3024 -- for one record.
3025 transaction_id;
3026 primary_qty;
3027 primary_uom;
3028 ocpl_primary_qty;
3029 ocpl_txn_id;
3030 reason_id;
3031 scrap_account_id;
3032
3033 -- need to call this routine before osp_validation because use
3034 -- created_by as a user_id to validate OSP
3035 osp_validation;
3036 serial_validation;
3037 /* Fixed bug 5056289. */
3038 -- Add more validation for assembly to prevent the whole batch failing if
3039 -- there is something wrong with the assembly. This check is only for
3040 -- EZ Completion and EZ Return.
3041 assembly_validation;
3042 /*******************************
3043 * End low priority validation *
3044 *******************************/
3045 -- set WMTI.PROCESS_STATUS to error if there is an error from any
3046 -- validation and insert error message into WIP_TXN_INTERFACE_ERRORS
3047 update_interface_tbl;
3048 -- derive the rest nessary info
3049 derive;
3050 -- write to the log file
3051 if (l_logLevel <= wip_constants.trace_logging) then
3052 wip_logger.exitPoint(p_procName => 'wip_move_validator.validate',
3053 p_procReturnStatus => fnd_api.g_ret_sts_success,
3054 p_msg => 'procedure complete',
3055 x_returnStatus => l_returnStatus);
3056 end if;
3057 EXCEPTION
3058 WHEN others THEN
3059 if (l_logLevel <= wip_constants.trace_logging) then
3060 wip_logger.exitPoint(p_procName => 'wip_move_validator.validate',
3061 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
3062 p_msg => 'Unexpected Errors: ' || SQLERRM,
3063 x_returnStatus => l_returnStatus);
3064 end if;
3065
3066 END validate;
3067
3068 PROCEDURE get_move_txn_type(p_move_id IN NUMBER,
3069 p_org_id IN NUMBER DEFAULT NULL,
3070 p_wip_entity_id IN NUMBER DEFAULT NULL,
3071 p_assm_item_id IN NUMBER DEFAULT NULL,
3072 p_txn_type OUT NOCOPY VARCHAR2)
3073 IS
3074 BEGIN
3075 p_txn_type := move_txn_type(p_move_id => p_move_id,
3076 p_org_id => p_org_id,
3077 p_wip_entity_id => p_wip_entity_id,
3078 p_assm_item_id => p_assm_item_id);
3079 END get_move_txn_type;
3080
3081
3082 FUNCTION move_txn_type(p_move_id IN NUMBER,
3083 p_org_id IN NUMBER DEFAULT NULL,
3084 p_wip_entity_id IN NUMBER DEFAULT NULL,
3085 p_assm_item_id IN NUMBER DEFAULT NULL) return VARCHAR2
3086 IS
3087 p_txn_type VARCHAR2(80);
3088 l_org_id NUMBER ;
3089 l_wip_entity_id NUMBER ;
3090 l_assm_item_id NUMBER ;
3091 BEGIN
3092 l_org_id := p_org_id;
3093 l_wip_entity_id := p_wip_entity_id;
3094 l_assm_item_id := p_assm_item_id;
3095
3096
3097 if (l_org_id is NULL or l_wip_entity_id is NULL) then
3098 select organization_id,
3099 wip_entity_id
3100 into l_org_id,
3101 l_wip_entity_id
3102 from wip_move_transactions
3103 where transaction_id = p_move_id;
3104 end if;
3105
3106 if (l_assm_item_id is NULL) then
3107 select wdj.primary_item_id
3108 into l_assm_item_id
3109 from wip_discrete_jobs wdj
3110 where wdj.organization_id = l_org_id
3111 and wdj.wip_entity_id = l_wip_entity_id;
3112 end if;
3113
3114 begin
3115 -- Should have at most one match
3116 select distinct lu.meaning
3117 into p_txn_type
3118 from mfg_lookups lu,
3119 mtl_material_transactions mmt
3120 where mmt.move_transaction_id = p_move_id
3121 and mmt.organization_id = l_org_id
3122 and mmt.transaction_source_id = l_wip_entity_id
3123 and mmt.inventory_item_id = l_assm_item_id
3124 and mmt.transaction_type_id in (wip_constants.CPLASSY_TYPE, wip_constants.RETASSY_TYPE)
3125 and lu.lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
3126 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);
3127 exception
3128 -- no inv txn involved; just a plain move txn
3129 when no_data_found then
3130 select meaning
3131 into p_txn_type
3132 from mfg_lookups
3133 where lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
3134 and lookup_code = wip_constants.move_txn;
3135 when others then
3136 p_txn_type := -1;
3137 end;
3138
3139 return p_txn_type;
3140 END move_txn_type;
3141
3142 PROCEDURE validateOATxn(p_group_id IN NUMBER) IS
3143 l_params wip_logger.param_tbl_t;
3144 l_returnStatus VARCHAR2(1);
3145 l_logLevel NUMBER ;
3146 l_sf_status VARCHAR2(100); /*Fix Bug 9758848*/
3147
3148 BEGIN
3149 l_logLevel := fnd_log.g_current_runtime_level;
3150
3151 -- write parameter value to log file
3152 if (l_logLevel <= wip_constants.trace_logging) then
3153 l_params(1).paramName := 'p_group_id';
3154 l_params(1).paramValue := p_group_id;
3155 wip_logger.entryPoint(p_procName => 'wip_move_validator.validateOATxn',
3156 p_params => l_params,
3157 x_returnStatus => l_returnStatus);
3158 end if;
3159
3160 -- reset global_variable everytime this routine is called
3161 g_group_id := p_group_id;
3162 enums.delete;
3163
3164 -- Derive ACCT_PERIOD_ID from TRANSACTION_DATE
3165 UPDATE wip_move_txn_interface wmti
3166 SET wmti.acct_period_id =
3167 (SELECT oap.acct_period_id
3168 FROM org_acct_periods oap
3169 WHERE oap.organization_id = wmti.organization_id
3170 -- modified the statement below for timezone project in J
3171 AND TRUNC(inv_le_timezone_pub.get_le_day_for_inv_org(
3172 wmti.transaction_date, -- p_trxn_date
3173 wmti.organization_id -- p_inv_org_id
3174 )) BETWEEN
3175 oap.period_start_date AND oap.schedule_close_date)
3176 WHERE wmti.group_id = g_group_id
3177 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3178 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
3179
3180 -- reset enums table
3181 enums.delete;
3182 -- Error out if there is no open accout period for the TRANSACTION_DATE
3183 -- specified or there is no WIP_PERIOD_BALANCES
3184 UPDATE wip_move_txn_interface wmti
3185 SET wmti.process_status = WIP_CONSTANTS.ERROR
3186 WHERE wmti.group_id = g_group_id
3187 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3188 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3189 AND (wmti.acct_period_id IS NULL
3190 OR
3191 NOT EXISTS
3192 (SELECT 'X'
3193 FROM wip_period_balances wpb
3194 WHERE wpb.acct_period_id = wmti.acct_period_id
3195 AND wpb.wip_entity_id = wmti.wip_entity_id
3196 AND wpb.organization_id = wmti.organization_id))
3197 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3198
3199 fnd_message.set_name('WIP', 'WIP_NO_BALANCE');
3200 add_error(p_txn_ids => enums,
3201 p_err_col => 'TRANSACTION_DATE',
3202 p_err_msg => fnd_message.get);
3203
3204 -- Fixed bug 5310474
3205 -- reset enums table
3206 enums.delete;
3207 -- Error out if FM_OPERATION_SEQ_NUM/FM_INTRAOPERATION_STEP_TYPE has
3208 -- no move shop floor status attached
3209 UPDATE wip_move_txn_interface wmti
3210 SET wmti.process_status = WIP_CONSTANTS.ERROR
3211 WHERE wmti.group_id = g_group_id
3212 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3213 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3214 AND EXISTS
3215 (SELECT 'X'
3216 FROM wip_shop_floor_status_codes wsc,
3217 wip_shop_floor_statuses ws
3218 WHERE wsc.organization_id = wmti.organization_id
3219 AND ws.organization_id = wmti.organization_id
3220 AND ws.wip_entity_id = wmti.wip_entity_id
3221 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3222 AND ws.operation_seq_num = wmti.fm_operation_seq_num
3223 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
3224 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3225 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3226 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
3227 AND (wmti.source_code IS NULL OR
3228 wmti.source_code <> 'RCV' OR
3229 (wmti.source_code = 'RCV' AND
3230 NOT EXISTS
3231 (SELECT 'X'
3232 FROM wip_parameters wp
3233 WHERE wp.organization_id = wmti.organization_id
3234 AND wp.osp_shop_floor_status =
3235 wsc.shop_floor_status_code))))
3236 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3237
3238 /* Added the following for SUN enhancement in Bug 9758848*/
3239 if enums.count > 0 then
3240 for i in 1..enums.count loop
3241 SELECT ws.shop_floor_status_code
3242 INTO l_sf_status
3243 FROM wip_shop_floor_status_codes wsc,
3244 wip_shop_floor_statuses ws,
3245 wip_move_txn_interface wmti
3246 WHERE wsc.organization_id = wmti.organization_id
3247 AND ws.organization_id = wmti.organization_id
3248 AND ws.wip_entity_id = wmti.wip_entity_id
3249 AND wmti.transaction_id = enums(i)
3250 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3251 AND ws.operation_seq_num = wmti.fm_operation_seq_num
3252 AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
3253 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3254 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3255 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
3256 AND rownum = 1;
3257
3258 fnd_message.set_name('WIP', 'WIP_SF_STATUS_NO_TXN1');
3259 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
3260 add_error(p_txn_id => enums(i),
3261 p_err_col => 'FM_INTRAOPERATION_STEP_TYPE',
3262 p_err_msg => fnd_message.get);
3263 end loop;
3264 end if;
3265 -- reset enums table
3266 enums.delete;
3267 -- Error out if user try to easy complete job/schedule that has No Move shop
3268 -- floor status attached to Tomove of the last operation
3269 UPDATE wip_move_txn_interface wmti
3270 SET wmti.process_status = WIP_CONSTANTS.ERROR
3271 WHERE wmti.group_id = g_group_id
3272 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3273 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3274 AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
3275 AND EXISTS
3276 (SELECT 'X'
3277 FROM wip_shop_floor_status_codes wsc,
3278 wip_shop_floor_statuses ws
3279 WHERE wsc.organization_id = wmti.organization_id
3280 AND ws.organization_id = wmti.organization_id
3281 AND ws.wip_entity_id = wmti.wip_entity_id
3282 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3283 AND ws.operation_seq_num = wmti.to_operation_seq_num
3284 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
3285 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3286 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3287 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
3288 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3289
3290 /* Added the following for SUN enhancement in Bug 9758848*/
3291 if enums.count > 0 then
3292 for i in 1..enums.count loop
3293 SELECT ws.shop_floor_status_code
3294 INTO l_sf_status
3295 FROM wip_shop_floor_status_codes wsc,
3296 wip_shop_floor_statuses ws,
3297 wip_move_txn_interface wmti
3298 WHERE wsc.organization_id = wmti.organization_id
3299 AND ws.organization_id = wmti.organization_id
3300 AND ws.wip_entity_id = wmti.wip_entity_id
3301 AND wmti.transaction_id = enums(i)
3302 AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
3303 AND ws.operation_seq_num = wmti.to_operation_seq_num
3304 AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
3305 AND ws.shop_floor_status_code = wsc.shop_floor_status_code
3306 AND wsc.status_move_flag = WIP_CONSTANTS.NO
3307 AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
3308 and rownum = 1;
3309
3310 fnd_message.set_name('WIP', 'WIP_SF_STATUS_NO_TXN2');
3311 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
3312 add_error(p_txn_id => enums(i),
3313 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
3314 p_err_msg => fnd_message.get);
3315 end loop;
3316 -- End of fix for bug 5310474
3317 end if;
3318
3319 -- Validate whether there is no move shopfloor status in between or not.
3320 -- reset enums table
3321 enums.delete;
3322 -- Error out if wip_parameter do not allow move over no_move shop floor
3323 -- status, and there are no_move status in between
3324 UPDATE wip_move_txn_interface wmti
3325 SET wmti.process_status = WIP_CONSTANTS.ERROR
3326 WHERE wmti.group_id = g_group_id
3327 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3328 AND wmti.process_status = WIP_CONSTANTS.RUNNING
3329 AND wip_sf_status.count_no_move_statuses(
3330 wmti.organization_id, -- p_org_id
3331 wmti.wip_entity_id, -- p_wip_id
3332 wmti.line_id, -- p_line_id
3333 wmti.repetitive_schedule_id, -- p_sched_id
3334 wmti.fm_operation_seq_num, -- p_fm_op
3335 wmti.fm_intraoperation_step_type, -- p_fm_step
3336 wmti.to_operation_seq_num, -- p_to_op
3337 wmti.to_intraoperation_step_type, -- p_to_step
3338 -- Fixed bug 2121222
3339 wmti.source_code) > 0 -- p_source_code
3340 RETURNING wmti.transaction_id BULK COLLECT INTO enums;
3341
3342 /* Added the following for SUN enhancement in Bug 9758848*/
3343 if enums.count > 0 then
3344 for i in 1..enums.count loop
3345 select wip_sf_status.get_no_move_status(
3346 wmti.organization_id, -- p_org_id
3347 wmti.wip_entity_id, -- p_wip_id
3348 wmti.line_id, -- p_line_id
3349 wmti.repetitive_schedule_id, -- p_sched_id
3350 wmti.fm_operation_seq_num, -- p_fm_op
3351 wmti.fm_intraoperation_step_type, -- p_fm_step
3352 wmti.to_operation_seq_num, -- p_to_op
3353 wmti.to_intraoperation_step_type, -- p_to_step
3354 wmti.source_code)
3355 into l_sf_status
3356 from wip_move_txn_interface wmti
3357 where wmti.transaction_id = enums(i)
3358 and wmti.process_status = WIP_CONSTANTS.ERROR;
3359 fnd_message.set_name ('WIP', 'WIP_NO_MOVE_STATUS_BETWEEN');
3360 fnd_message.set_token('SF_STATUS', to_char(l_sf_status));
3361 add_error(p_txn_id => enums(i),
3362 p_err_col => 'TO_INTRAOPERATION_STEP_TYPE',
3363 p_err_msg => fnd_message.get);
3364 end loop;
3365 end if;
3366
3367 -- Do OSP related validation.
3368 osp_validation;
3369
3370 -- Set WMTI.PROCESS_STATUS to error if there is an error from any
3371 -- validation and insert error message into WIP_TXN_INTERFACE_ERRORS
3372 update_interface_tbl;
3373
3374 -- Set WMTI.PROCESS_PHASE to WIP_CONSTANTS.MOVE_PROC so that move processing
3375 -- code can process these records.
3376 UPDATE wip_move_txn_interface wmti
3377 SET process_phase = WIP_CONSTANTS.MOVE_PROC
3378 WHERE wmti.group_id = g_group_id
3379 AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
3380 AND wmti.process_status = WIP_CONSTANTS.RUNNING;
3381
3382 -- write to the log file
3383 if (l_logLevel <= wip_constants.trace_logging) then
3384 wip_logger.exitPoint(p_procName => 'wip_move_validator.validateOATxn',
3385 p_procReturnStatus => fnd_api.g_ret_sts_success,
3386 p_msg => 'procedure complete',
3387 x_returnStatus => l_returnStatus);
3388 end if;
3389 EXCEPTION
3390 WHEN others THEN
3391 if (l_logLevel <= wip_constants.trace_logging) then
3392 wip_logger.exitPoint(p_procName => 'wip_move_validator.validateOATxn',
3393 p_procReturnStatus => fnd_api.g_ret_sts_unexp_error,
3394 p_msg => 'Unexpected Errors: ' || SQLERRM,
3395 x_returnStatus => l_returnStatus);
3396 end if;
3397
3398 END validateOATxn;
3399
3400 END wip_move_validator;
[Home] [Help]