1 Package Body Wip_Flow_Utilities as
2 /* $Header: wipfcomb.pls 120.2 2006/04/27 17:25:35 yulin noship $ */
3
4 /* *********************************************************************
5 Public Procedures
6 ***********************************************************************/
7 wip_line_ops BOM_RTG_Network_API.Op_Tbl_Type;
8
9
10 Procedure Update_Completion_UOM(p_item_id in number, p_org_id in number,
11 p_txn_qty in number, p_txn_uom in varchar2,
12 p_pri_qty in out nocopy number) is
13 x_primary_uom VARCHAR2(3);
14 BEGIN
15
16
17 Select PRIMARY_UOM_CODE INTO x_primary_uom
18 FROM mtl_system_items
19 WHERE inventory_item_Id = p_item_id
20 AND organization_Id = p_org_id;
21
22 -- Call the Inventory Conversion Routine to get the Quantity
23 -- in the Original UOM, if the Primary Uom and the transaction
24 -- uom are not the same
25 if (x_primary_uom <> p_txn_uom ) then
26
27 p_pri_qty := inv_convert.inv_um_convert(
28 item_id => p_item_id,
29 precision => NULL,
30 from_quantity => p_txn_qty,
31 from_unit => p_txn_uom,
32 to_unit => x_primary_uom,
33 from_name => NULL,
34 to_name => NULL ) ;
35
36 else
37
38 p_pri_qty := p_txn_qty ;
39
40
41 end if;
42
43
44 end Update_Completion_UOM ;
45
46
47
48
49 function Subs_Check(
50 p_parent_id in number,
51 p_organization_id in number,
52 p_err_num in out nocopy number,
53 p_err_mesg in out nocopy varchar2
54 ) return number is
55
56 /********************************************
57 * Cursor for Substitutions
58 *********************************************/
59 CURSOR Substitute_Cursor(p_parent_id NUMBER,
60 P_organization_id NUMBER) is
61 Select
62 INVENTORY_ITEM_ID,
63 SUBSTITUTION_ITEM_ID,
64 SUBSTITUTION_TYPE_ID,
65 OPERATION_SEQ_NUM,
66 FLOW_SCHEDULE
67 from Mtl_transactions_interface
68 where parent_id = p_parent_id
69 and substitution_type_id is not null
70 and process_flag = 2
71 and organization_id = p_organization_Id ;
72
73 x_error VARCHAR2(240);
74 x_message VARCHAR2(240);
75 x_success number := 0;
76 x_see_eng_item number := 2; -- default it to not a eng item
77 x_parent_op_seq NUMBER;
78 x_rout_rev_date DATE;
79 x_rout_seq_id NUMBER;
80 begin
81
82
83 -- Get the WIP profile for the whole section
84 begin
85 x_see_eng_item := to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS'));
86
87 exception
88 when others then
89 x_see_eng_item := 2; --Default to not an engineering item
90
91 end ;
92
93 select operation_seq_num
94 into x_parent_op_seq
95 from mtl_transactions_interface
96 where transaction_interface_id = p_parent_id;
97
98 begin
99 SELECT mti.routing_revision_date, bor.routing_sequence_id -- CFM Scrap
100 INTO x_rout_rev_date, x_rout_seq_id
101 FROM mtl_transactions_interface mti, bom_operational_routings bor
102 WHERE mti.transaction_interface_id = p_parent_id
103 AND mti.inventory_item_id = bor.assembly_item_id(+)
104 AND mti.organization_id = bor.organization_id(+)
105 AND Nvl(mti.alternate_routing_designator,'@@@@') = Nvl(bor.alternate_routing_designator,'@@@@');
106 exception
107 when others then
108 x_rout_rev_date := null;
109 x_rout_seq_id := null;
110 end;
111
112 For sub_rec in Substitute_Cursor(p_parent_id, p_organization_id) LOOP
113
114
115 x_success := 0;
116 select 1 into x_success
117 from sys.dual
118 where Sub_Rec.substitution_type_id in (
119 select lookup_code from mfg_lookups
120 where lookup_type = 'WIP_SUBSTITUTION_TYPE') ;
121
122 if (x_success = 0) then
123
124
125 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_TYPE');
126 fnd_message.set_token('ENTITY1',
127 to_char(sub_rec.operation_seq_num) );
128 p_err_mesg := fnd_message.get ;
129 return 0;
130
131 end if ;
132
133 --
134 -- We will fail it only if it is 'N', if it is NULL, we will
135 -- derive it later in Post_SubsMerge
136 --
137
138 if( UPPER(NVL(Sub_Rec.flow_schedule,'Y')) = 'N' ) then
139 fnd_message.set_name('WIP', 'WIP_FLOW_FLAG_ERROR');
140 p_err_mesg := fnd_message.get ;
141 return 0;
142 end if;
143
144 x_success := 0; -- CFM Scrap
145 IF x_parent_op_seq IS NOT NULL AND
146 x_rout_seq_id is not null and
147 sub_rec.substitution_type_id <> 3 AND
148 event_in_same_or_prior_lineop(x_rout_seq_id,
149 x_rout_rev_date,
150 Sub_Rec.operation_seq_num,
151 x_parent_op_seq,
152 'N') <> 1 THEN
153 fnd_message.set_name('WIP', 'WIP_INVALID_COMP_OP_SEQ');
154 p_err_mesg := fnd_message.get ;
155 RETURN 0;
156 END IF;
157
158
159 if (Sub_Rec.substitution_type_id <> 3) then
160
161 x_success := 0;
162 select 1 into x_success
163 from mtl_system_items
164 where organization_id = p_organization_id
165 and inventory_item_id = Sub_Rec.inventory_item_id
166 and mtl_transactions_enabled_flag = 'Y'
167 and inventory_item_flag = 'Y'
168 and bom_enabled_flag = 'Y'
169 and eng_item_flag = decode(x_see_eng_item,
170 1, eng_item_flag,
171 'N')
172 and bom_item_type = 4 ; -- Standard Type
173 if (x_success = 0) then
174
175
176 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_ASSEMBLY');
177 x_error := 'Original Item Id' ||to_char(Sub_Rec.inventory_item_id)
178 || ' at OP Seq :'
179 || to_char(sub_rec.operation_seq_num) || '.' ;
180 fnd_message.set_token('ENTITY1', x_error);
181 fnd_message.set_token('ENTITY2', 'Original Component');
182 p_err_mesg := fnd_message.get ;
183 return 0;
184
185 end if ;
186 end if ;
187
188 if (Sub_Rec.substitution_type_id in(1,3) ) then
189
190 x_success := 0;
191 select 1 into x_success
192 from mtl_system_items
193 where organization_id = p_organization_id
194 and inventory_item_id = Sub_Rec.substitution_item_id
195 and mtl_transactions_enabled_flag = 'Y'
196 and inventory_item_flag = 'Y'
197 and bom_enabled_flag = 'Y'
198 and eng_item_flag = decode(x_see_eng_item,
199 1, eng_item_flag,
200 'N')
201 and sub_rec.substitution_type_id in (1,3)
202 and bom_item_type = 4 ; -- Standard Type
203
204 if (x_success = 0) then
205
206
207 fnd_message.set_name('WIP', 'WIP_ERROR_SUBST_ASSEMBLY');
208 x_error := 'Substitution Item Id' ||to_char(Sub_Rec.substitution_item_id)
209 || ' at OP Seq :'
210 || to_char(sub_rec.operation_seq_num) || '.' ;
211 fnd_message.set_token('ENTITY1', x_error);
212 fnd_message.set_token('ENTITY2', 'Substitute Component');
213 p_err_mesg := fnd_message.get ;
214 return 0;
215
216 end if ;
217 end if ;
218
219 END LOOP ;
220
221 return 1;
222
223 exception
224
225 when others then
226
227 /**********************************************************************
228 * The Error Message in this case is handled by the calling wilctv.ppc *
229 ***********************************************************************/
230
231 return 0;
232
233 end Subs_Check ;
234
235
236
237
238
239 function Derive_Completion(
240 p_org_id in out nocopy number,
241 p_txn_src_name in out nocopy varchar2,
242 p_txn_src_id in out nocopy number,
243 p_sched_num in out nocopy varchar2,
244 p_src_proj_id in out nocopy number,
245 p_proj_id in out nocopy number,
246 p_src_task_id in out nocopy number,
247 p_task_id in out nocopy number,
248 p_bom_rev in out nocopy varchar2,
249 p_rev in out nocopy varchar2,
250 p_bom_rev_date in out nocopy date,
251 p_rout_rev in out nocopy varchar2,
252 p_rout_rev_date in out nocopy date,
253 p_comp_sub in out nocopy varchar2,
254 p_class_code in out nocopy varchar2,
255 p_wip_entity_type in out nocopy number,
256 p_comp_loc in out nocopy number,
257 p_alt_rtg_des in out nocopy varchar2,
258 p_alt_bom_des in out nocopy varchar2,
259 p_scheduled_flag in out nocopy number,
260 p_transaction_date in out nocopy date,
261 p_item_id in out nocopy number,
262 p_txn_int_id in out nocopy number,
263 p_txn_action_id IN NUMBER -- CFM Scrap
264 ) return number is
265 x_err_mesg varchar2(240);
266 begin
267
268
269 if (p_scheduled_flag <> 1) then
270
271 /****************************************************************
272 * Removed the call for defaulting the Transaction Source Id and *
273 * Transaction Source Name as it doesnot make sense to call in *
274 * the Unscheduled Case, as we will not have a Wip_Entity exist *
275 * ing already - as we create a New WIP Entity Id at runtime *
276 * - dsoosai 11/12/97 *
277 ****************************************************************/
278
279 if(
280 (Wip_Flow_Derive.schedule_number(
281 p_sched_num => p_sched_num) = 0 )
282
283 or (Wip_Flow_Derive.src_project_id(
284 p_src_proj_id => p_src_proj_id,
285 p_proj_id => p_proj_id) = 0 )
286
287 or (Wip_Flow_Derive.src_task_id(
288 p_src_task_id => p_src_task_id,
289 p_task_id => p_task_id) = 0)
290
291 or (Wip_Flow_Derive.bom_revision(
292 p_bom_rev => p_bom_rev,
293 p_rev => p_rev,
294 p_bom_rev_date => p_bom_rev_date,
295 p_item_id => p_item_id,
296 p_start_date => p_transaction_date,
297 p_Org_id => p_Org_id) = 0 )
298
299 or (Wip_Flow_Derive.routing_revision(
300 p_rout_rev => p_rout_rev,
301 p_rout_rev_date => p_rout_rev_date,
302 p_item_id => p_item_id,
303 p_start_date => p_transaction_date,
304 p_Org_id => p_Org_id) = 0 )
305
306 or (p_txn_action_id <> 30 AND -- CFM Scrap
307 Wip_Flow_Derive.completion_sub(p_comp_sub => p_comp_sub,
308 p_item_id => p_item_id,
309 p_org_id => p_org_id,
310 p_alt_rtg_des => p_alt_rtg_des) = 0 )
311
312 or (Wip_Flow_Derive.class_code(
313 p_class_code =>p_class_code,
314 p_err_mesg => x_err_mesg,
315 p_org_id => p_org_id,
316 p_item_id => p_item_id,
317 p_wip_entity_type => p_wip_entity_type,
318 p_project_id => p_src_proj_id) = 0 )
319
320 or (p_txn_action_id <> 30 AND -- CFM Scrap
321 Wip_Flow_Derive.completion_loc(p_comp_loc => p_comp_loc,
322 p_item_id => p_item_id,
323 p_org_id => p_org_id,
324 p_alt_rtg_des => p_alt_rtg_des,
325 p_proj_id => p_src_proj_id,
326 p_task_id => p_src_task_id,
327 p_comp_sub => p_comp_sub,
328 p_txn_int_id => p_txn_int_id) = 0 )
329 ) then
330
331 -- This is failure
332 return 0 ;
333 end if ;
334
335 elsif (p_scheduled_flag = 1) then
336
337 if (
338
339 (Wip_Flow_Derive.Transaction_Source_Name(
340 p_txn_src_name => p_txn_src_name,
341 p_txn_src_id => p_txn_src_id,
342 p_org_id => p_org_id) = 0 )
343
344 or (Wip_Flow_Derive.Scheduled_Flow_Derivation(
345 p_txn_action_id => p_txn_action_id,-- CFM Scrap
346 p_item_id => p_item_id,
347 p_org_id => p_org_id,
348 p_txn_src_id => p_txn_src_id,
349 p_sched_num => p_sched_num,
350 p_src_proj_id => p_src_proj_id,
351 p_proj_id => p_proj_id,
352 p_src_task_id => p_src_task_id,
353 p_task_id => p_task_id,
354 p_bom_rev => p_bom_rev,
355 p_rev => p_rev,
356 p_bom_rev_date => p_bom_rev_date,
357 p_rout_rev => p_rout_rev,
358 p_rout_rev_date => p_rout_rev_date,
359 p_comp_sub => p_comp_sub,
360 p_class_code => p_class_code,
361 p_wip_entity_type => p_wip_entity_type,
362 p_comp_loc => p_comp_loc,
363 p_alt_rtg_des => p_alt_rtg_des,
364 p_alt_bom_des => p_alt_bom_des) = 0 )
365 ) then
366
367 return 0;
368
369 end if ;
370
371 end if ;
372
373 return 1;
374
375 exception
376 when others then
377 return 0;
378
379
380 end Derive_Completion ;
381
382
383
384
385 function Validate_Completion(
386 p_rowid in rowid,
387 p_item_id in number,
388 p_schedule_number in out nocopy varchar2,
389 p_organization_id in number,
390 p_class_code in varchar2,
391 p_scheduled_flag in number,
392 p_txn_src_id in number,
393 p_see_eng_item in number) return number is
394 x_success number := 0;
395 x_message varchar2(240);
396 begin
397
398
399
400 if (p_scheduled_flag = 1) then
401
402 begin
403
404 /* We don't have to validate on the
405 status type, as the schedule has to
406 be open */
407
408 select 1 into x_success
409 from wip_flow_schedules wfs,
410 mtl_transactions_interface mti
411 where wfs.wip_entity_id = p_txn_src_id
412 and wfs.organization_id = p_organization_id
413 and trunc(wfs.scheduled_completion_date,WIP_CONSTANTS.DATE_FMT) =
414 trunc(mti.transaction_date,WIP_CONSTANTS.DATE_FMT)
415 and mti.rowid = p_rowid;
416
417
418 exception
419 when others then
420 -- It is closed
421 return 0 ;
422 end ;
423
424 end if ;
425
426 /* Notes :
427 1. The messages are being set in this routine because the actual validation routines may be called by planning.
428 2. I am reusing the "ML" messages instead of creating new ones because the message text fits this validation also.
429 */
430
431 IF (Wip_Flow_Validation.primary_item_id(p_rowid => p_rowid) = 0 ) THEN
432 fnd_message.set_name('WIP', 'WIP_CANNOT_BUILD_ITEM');
433
434 ELSIF (Wip_Flow_Validation.class_code(p_rowid => p_rowid) = 0 ) THEN
435 fnd_message.set_name('WIP', 'WIP_INTERFACE_INVALID_CLASS');
436
437 ELSIF (Wip_Flow_Validation.bom_rev_date(p_rowid => p_rowid) = 0 ) THEN
438 fnd_message.set_name('WIP', 'WIP_INVALID_BOM_REVISION_DATE');
439
440 ELSIF (Wip_Flow_Validation.bom_revision(p_rowid => p_rowid) = 0 ) THEN
441 fnd_message.set_name('WIP', 'WIP_INVALID_BOM_REVISION');
442
443 ELSIF (Wip_Flow_Validation.rout_rev_date(p_rowid => p_rowid) = 0 ) THEN
444 fnd_message.set_name('WIP', 'WIP_INVALID_ROUT_REVISION_DATE');
445
446 ELSIF (Wip_Flow_Validation.routing_revision(p_rowid => p_rowid) = 0 ) THEN
447 fnd_message.set_name('WIP', 'WIP_INVALID_ROUT_REVISION');
448
449 ELSIF (Wip_Flow_Validation.alt_bom_desg(p_rowid => p_rowid) = 0 ) THEN
450 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_BOM');
451
452 ELSIF (Wip_Flow_Validation.alt_rout_desg(p_rowid => p_rowid) = 0 ) THEN
453 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_ROUTING');
454
455 ELSIF (Wip_Flow_Validation.completion_sub(p_rowid => p_rowid) = 0 ) THEN
456 fnd_message.set_name('WIP', 'WIP_INVALID_COMPLETION_SUB');
457
458 ELSIF (Wip_Flow_Validation.completion_locator_id(p_rowid => p_rowid) = 0 ) THEN
459 fnd_message.set_name('WIP', 'WIP_INVALID_LOCATOR');
460
461 ELSIF (Wip_Flow_Validation.demand_class(p_rowid => p_rowid) = 0 ) THEN
462 fnd_message.set_name('WIP', 'WIP_ML_DEMAND_CLASS');
463
464 ELSIF (Wip_Flow_Validation.schedule_group_id(p_rowid => p_rowid) = 0 ) THEN
465 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULE_GROUP');
466
467 ELSIF (Wip_Flow_Validation.build_sequence(p_rowid => p_rowid) = 0 ) THEN
468 fnd_message.set_name('WIP', 'WIP_BUILD_SEQUENCE');
469
470 ELSIF (Wip_Flow_Validation.line_id(p_rowid => p_rowid) = 0 ) THEN
471 fnd_message.set_name('WIP', 'WIP_ML_LINE_ID');
472
473 ELSIF (Wip_Flow_Validation.project_id(p_rowid => p_rowid) = 0 ) THEN
474 fnd_message.set_name('WIP', 'WIP_INVALID_PROJECT');
475
476 ELSIF (Wip_Flow_Validation.task_id(p_rowid => p_rowid) = 0 ) THEN
477 fnd_message.set_name('WIP', 'WIP_INVALID_TASK');
478
479 ELSIF (Wip_Flow_Validation.schedule_number(p_rowid => p_rowid) = 0 ) THEN
480 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_NUMBER');
481
482 ELSIF (Wip_Flow_Validation.scheduled_flag(p_rowid => p_rowid) = 0 ) THEN
483 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULED_FLAG');
484
485 ELSIF (Wip_Flow_Validation.unit_number(p_rowid => p_rowid) = 0 ) THEN
486 fnd_message.set_name('WIP', 'UEFF-UNIT NUMBER INVALID');
487
488 ELSE
489 RETURN 1;
490 END IF;
491
492 -- This is failure
493
494 x_message := fnd_message.get ;
495
496 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
497 SET LAST_UPDATE_DATE = SYSDATE,
498 PROGRAM_UPDATE_DATE = SYSDATE,
499 PROCESS_FLAG = 3,
500 LOCK_FLAG = 2,
501 ERROR_CODE = NULL,
502 ERROR_EXPLANATION = x_message
503 WHERE rowid = p_rowid ;
504
505
506 return 0 ;
507
508 exception
509 when others then
510
511 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_VALIDATION');
512 fnd_message.set_token('ENTITY1', p_schedule_number);
513 x_message := fnd_message.get ;
514
515 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
516 SET LAST_UPDATE_DATE = SYSDATE,
517 PROGRAM_UPDATE_DATE = SYSDATE,
518 PROCESS_FLAG = 3,
519 LOCK_FLAG = 2,
520 ERROR_CODE = NULL,
521 ERROR_EXPLANATION = x_message
522 WHERE rowid = p_rowid ;
523
524 return 0;
525
526 end Validate_Completion ;
527
528
529
530 function Revision_Generation(
531 p_interface_id in number,
532 p_err_num in out nocopy number,
533 p_err_mesg in out nocopy varchar2) return number is
534 begin
535
536
537 UPDATE mtl_transactions_interface MTI
538 SET LAST_UPDATE_DATE = SYSDATE,
539 PROGRAM_UPDATE_DATE = SYSDATE,
540 REVISION =
541 (SELECT NVL(mti.revision, MAX(mir.revision))
542 FROM mtl_item_revisions mir
543 WHERE mir.organization_id = mti.organization_id
544 AND mir.inventory_item_id = mti.inventory_item_id
545 AND mir.effectivity_date <=
546 mti.transaction_date
547 AND mir.effectivity_date =
548 (SELECT MAX(mir2.effectivity_date)
549 FROM mtl_item_revisions mir2
550 WHERE mir2.organization_id = mti.organization_id
551 AND mir2.inventory_item_id = mti.inventory_item_id
552 AND mir2.effectivity_date <=
553 mti.transaction_date
554 ))
555 WHERE
556 PARENT_ID = p_interface_id
557 AND TRANSACTION_SOURCE_TYPE_ID = 5
558 AND FLOW_SCHEDULE = 'Y'
559 AND TRANSACTION_ACTION_ID IN (1,27,33,34)
560 AND PROCESS_FLAG = 2
561 AND REVISION IS NULL
562 AND EXISTS (
563 SELECT 'X'
564 FROM MTL_SYSTEM_ITEMS msi
565 WHERE msi.ORGANIZATION_ID = mti.ORGANIZATION_ID
566 AND msi.inventory_item_id = mti.inventory_item_id
567 AND msi.revision_qty_control_code = 2);
568
569 return 1 ;
570
571 exception
572
573 when others then
574
575 /**********************************************************************
576 * The Error Message in this case is handled by the calling wilctv.ppc *
577 ***********************************************************************/
578
579 return 0;
580
581 end Revision_Generation ;
582
583
584
585
586
587 /* Generate the issue locators for all the issues associated with a completion
588 This would be called only for a project related completions*/
589 function Generate_Issue_Locator_Id(
590 p_parent_id in number,
591 p_organization_id in number,
592 p_src_prj_id in number,
593 p_src_tsk_id in number,
594 p_wip_entity_id in number,
595 p_err_num in out nocopy number,
596 p_err_mesg in out nocopy varchar2) return number is
597 x_project_id number;
598 x_task_id number;
599 x_wip_entity_id number;
600 x_org_id number;
601 x_success number := 1 ;
602
603 begin
604
605 /*******************************************************
606 * The cursor for going through the Material Issues in *
607 * this program is not required as this is being done *
608 * in the PJM_Project_Locator.Get_Flow_ProjectSupply in*
609 * PJMLOCB.pls - this is the fix required for Bug# : *
610 * 598471 *
611 * - dsoosai 12/11/97 *
612 *******************************************************/
613
614 /*******************************************************
615 * The actual cursor for going through each one of the *
616 * Material Issue that is associated with Completion *
617 * is done in the PJM_Project_Locator routine *
618 *******************************************************/
619
620 PJM_Project_Locator.Get_Flow_ProjectSupply(
621 p_organization_id => p_organization_id,
622 p_wip_entity_id => p_wip_entity_id,
623 p_project_id => p_src_prj_id,
624 p_task_id => p_src_tsk_id,
625 p_parent_id => p_parent_id,
626 p_success => x_success );
627
628 if (x_success = 0) then
629 return x_success ;
630 end if;
631
632 return x_success ;
633
634 exception
635
636 when others then
637
638 /**********************************************************************
639 * The Error Message in this case is handled by the calling wilctv.ppc *
640 ***********************************************************************/
641 return 0 ;
642
643 end Generate_Issue_Locator_Id ;
644
645
646 procedure Create_Flow_Schedules( p_header_id in number) is
647
648 /* **********************************************
649 Cursor to get all the Flow Completions
650 ********************************************** */
651 CURSOR Flow_Completion (header_id number) is
652 SELECT
653 transaction_interface_id,
654 transaction_action_id, -- CFM Scrap
655 organization_id,
656 last_update_date,
657 last_updated_by,
658 creation_date,
659 created_by,
660 last_update_login,
661 request_id,
662 program_application_id,
663 program_id,
664 program_update_date,
665 inventory_item_id,
666 accounting_class,
667 transaction_date,
668 transaction_quantity, -- we have to convert it to primary qty
669 transaction_uom,
670 primary_quantity,
671 transaction_source_id,
672 transaction_source_name,
673 revision,
674 bom_revision,
675 routing_revision,
676 bom_revision_date,
677 routing_revision_date,
678 alternate_bom_designator,
679 alternate_routing_designator,
680 subinventory_code,
681 locator_id,
682 demand_class,
683 schedule_group,
684 build_sequence,
685 repetitive_line_id,
686 source_project_id,
687 project_id,
688 source_task_id,
689 task_id,
690 schedule_number,
691 scheduled_flag,
692 wip_entity_type,
693 end_item_unit_number,
694 rowid
695 FROM mtl_transactions_interface
696 WHERE transaction_header_id = header_id
697 AND TRANSACTION_SOURCE_TYPE_ID = 5
698 AND FLOW_SCHEDULE = 'Y'
699 AND transaction_action_id in (31, 32, 30) -- CFM Scrap
700 AND scheduled_flag in (1, 2, 3) -- Unscheduled from form and interface
701 AND process_flag = 1;
702
703 x_message varchar2(240);
704
705 x_sql_err_num number;
706 x_sql_message varchar2(240);
707 x_material_account number;
708 x_material_overhead_account number;
709 x_resource_account number;
710 x_outside_processing_account number;
711 x_material_variance_account number;
712 x_resource_variance_account number;
713 x_outside_proc_var_account number;
714 x_std_cost_adjustment_account number;
715 x_overhead_account number;
716 x_overhead_variance_account number ;
717 x_see_eng_item number := 2; --Default to not an engineering item
718 x_wip_entity_id number;
719 x_success number := 0;
720 general exception;
721 begin
722
723 -- Get the WIP profile for the whole section
724 begin
725 x_see_eng_item := to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS'));
726
727 exception
728 when others then
729 x_see_eng_item := 2; --Default to not an engineering item
730
731 end ;
732
733
734
735 FOR flow_rec in Flow_Completion(p_header_id) LOOP
736
737 BEGIN
738
739 x_success := 0;
740
741 /* we do the defaulting for scheduled, Unscheduled and for the
742 records coming from the Form, the derive completions
743 procedure differenciates between the various cases and
744 performs actions accordingly */
745
746 flow_rec.wip_entity_type := 4 ; -- Set it to Flow Schedule
747
748 IF((flow_rec.transaction_source_id is not null)
749 and (flow_rec.scheduled_flag = 2)) THEN
750 -- Reset these for resubmission records.
751 flow_rec.transaction_source_id := null;
752 flow_rec.transaction_source_name := null;
753 flow_rec.schedule_number := null;
754 end if;
755
756 if(
757 derive_completion(
758 p_org_id => flow_rec.organization_id,
759 p_txn_src_name => flow_rec.transaction_source_name,
760 p_txn_src_id => flow_rec.transaction_source_id,
761 p_sched_num => flow_rec.schedule_number,
762 p_src_proj_id => flow_rec.source_project_id,
763 p_proj_id => flow_rec.project_id,
764 p_src_task_id => flow_rec.source_task_id,
765 p_task_id => flow_rec.task_id,
766 p_bom_rev => flow_rec.bom_revision,
767 p_rev => flow_rec.revision,
768 p_bom_rev_date => flow_rec.bom_revision_date,
769 p_rout_rev => flow_rec.routing_revision,
770 p_rout_rev_date => flow_rec.routing_revision_date,
771 p_comp_sub => flow_rec.subinventory_code,
772 p_class_code => flow_rec.accounting_class,
773 p_wip_entity_type => flow_rec.wip_entity_type,
774 p_comp_loc => flow_rec.locator_id,
775 p_alt_rtg_des => flow_rec.alternate_routing_designator,
776 p_alt_bom_des => flow_rec.alternate_bom_designator,
777 p_scheduled_flag => flow_rec.scheduled_flag,
778 p_transaction_date => flow_rec.transaction_date,
779 p_item_id => flow_rec.inventory_item_id,
780 p_txn_int_id => flow_rec.transaction_interface_id,
781 p_txn_action_id => flow_rec.transaction_action_id -- CFM Scrap
782 ) = 1) then
783
784 Update Mtl_Transactions_Interface
785 Set
786 transaction_source_id = flow_rec.transaction_source_id,
787 schedule_number = flow_rec.schedule_number,
788 source_project_id = flow_rec.source_project_id,
789 project_id = flow_rec.project_id,
790 source_task_id = flow_rec.source_task_id,
791 task_id = flow_rec.task_id,
792 bom_revision = flow_rec.bom_revision,
793 revision = flow_rec.revision,
794 bom_revision_date = flow_rec.bom_revision_date,
795 routing_revision = flow_rec.routing_revision,
796 routing_revision_date = flow_rec.routing_revision_date,
797 subinventory_code = flow_rec.subinventory_code,
798 accounting_class = flow_rec.accounting_class,
799 wip_entity_type = flow_rec.wip_entity_type,
800 locator_id = flow_rec.locator_id
801 Where
802 rowid = flow_rec.rowid ;
803
804 else
805
806 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_DEFAULTING');
807 fnd_message.set_token('ENTITY1',to_char(flow_rec.transaction_interface_id));
808
809 x_message := fnd_message.get ;
810
811 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
812 SET LAST_UPDATE_DATE = SYSDATE,
813 PROGRAM_UPDATE_DATE = SYSDATE,
814 PROCESS_FLAG = 3,
815 LOCK_FLAG = 2,
816 ERROR_CODE = NULL,
817 ERROR_EXPLANATION = x_message
818 WHERE rowid = flow_rec.rowid ;
819
820
821 end if ;
822
823
824 If (flow_rec.transaction_source_id is null)
825 and (flow_rec.scheduled_flag<> 1) then
826
827
828 -- This will convert the Completion UOM to the corresponding primary
829 -- UOM. Note : you have to convert the column types in the record
830 -- to the corresponding values inorder to make the function call
831 -- correctly
832 Update_Completion_UOM(
833 p_item_id => flow_rec.inventory_item_id,
834 p_org_id => flow_rec.organization_id,
835 p_txn_qty => flow_rec.transaction_quantity,
836 p_txn_uom => flow_rec.transaction_uom,
837 p_pri_qty => flow_rec.primary_quantity);
838
839
840 /* We do the validation and completion only for interface
841 records */
842 If (flow_rec.scheduled_flag =2) then
843
844 x_success := Validate_Completion(
845 p_rowid => flow_rec.rowid,
846 p_item_id => flow_rec.inventory_item_id,
847 p_schedule_number => flow_rec.schedule_number,
848 p_organization_id => flow_rec.organization_id,
849 p_class_code => flow_rec.accounting_class,
850 p_scheduled_flag => flow_rec.scheduled_flag,
851 p_txn_src_id => flow_rec.transaction_source_id,
852 p_see_eng_item => x_see_eng_item);
853
854
855 elsif (flow_rec.scheduled_flag = 3) then
856
857 if (wip_flow_validation.schedule_number(
858 p_rowid => flow_rec.rowid ) = 0 ) then
859
860 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_NUMBER');
861
862 x_message := fnd_message.get ;
863
864 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
865 SET LAST_UPDATE_DATE = SYSDATE,
866 PROGRAM_UPDATE_DATE = SYSDATE,
867 PROCESS_FLAG = 3,
868 LOCK_FLAG = 2,
869 ERROR_CODE = NULL,
870 ERROR_EXPLANATION = x_message
871 WHERE rowid = flow_rec.rowid ;
872
873
874 end if ;
875
876 End if ;
877
878 /* We enter this section only if the scheduled_flag = 3 or if it
879 passed all the validation for scheduled_flag = 2 */
880 Select wip_entities_s.nextval into x_wip_entity_id
881 from dual ;
882
883 x_success := Create_Flow_Schedule(
884 x_wip_entity_id,
885 flow_rec.organization_id,
886 flow_rec.last_update_date,
887 flow_rec.last_updated_by,
888 flow_rec.creation_date,
889 flow_rec.created_by,
890 flow_rec.last_update_login,
891 flow_rec.request_id,
892 flow_rec.program_application_id,
893 flow_rec.program_id,
894 flow_rec.program_update_date,
895 flow_rec.inventory_item_id,
896 flow_rec.accounting_class,
897 flow_rec.transaction_date,
898 NULL,
899 0,
900 0, -- We have to insert it as the primary_quantity
901 0, -- CFM Scrap
902 NULL,
903 NULL,
904 flow_rec.bom_revision,
905 flow_rec.routing_revision,
906 flow_rec.bom_revision_date,
907 flow_rec.routing_revision_date,
908 flow_rec.alternate_bom_designator,
909 flow_rec.alternate_routing_designator,
910 flow_rec.subinventory_code,
911 flow_rec.locator_id, -- actually this will be validated by INV proc
912 flow_rec.demand_class,
913 flow_rec.transaction_date,
914 flow_rec.schedule_group,
915 flow_rec.build_sequence,
916 flow_rec.repetitive_line_id,
917 flow_rec.project_id,
918 flow_rec.task_id,
919 1, -- 1. Open, 2. Close
920 flow_rec.schedule_number,
921 2, -- Unscheduled
922 flow_rec.end_item_unit_number, -- end item unit number
923 NULL,
924 NULL, NULL, NULL, NULL, NULL,
925 NULL, NULL, NULL, NULL, NULL,
926 NULL, NULL, NULL, NULL, NULL );
927
928
929 if(x_success = 0) then
930 raise general ;
931 end if;
932
933 Update Mtl_transactions_interface
934 set transaction_source_id = wip_entities_s.currval,
935 scheduled_flag = 2, -- No
936 schedule_number = flow_rec.schedule_number,
937 primary_quantity = flow_rec.primary_quantity
938 where rowid = flow_rec.rowid ;
939
940
941 elsif (flow_rec.transaction_source_id is not null)
942 and (flow_rec.scheduled_flag = 1) then
943
944 x_success := Validate_Completion(
945 p_rowid => flow_rec.rowid,
946 p_item_id => flow_rec.inventory_item_id,
947 p_schedule_number => flow_rec.schedule_number,
948 p_organization_id => flow_rec.organization_id,
949 p_class_code => flow_rec.accounting_class,
950 p_scheduled_flag => flow_rec.scheduled_flag,
951 p_txn_src_id => flow_rec.transaction_source_id,
952 p_see_eng_item => x_see_eng_item);
953
954 else
955
956 raise general ;
957
958 End if ;
959
960 exception
961
962 when others then
963 -- Error creating the wip flow schedule
964 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_CREATION');
965 fnd_message.set_token('ENTITY1',to_char(flow_rec.transaction_interface_id));
966
967 x_message := fnd_message.get ;
968 x_sql_err_num := SQLCODE;
969 x_sql_message := substr(SQLERRM,1,240);
970
971 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
972 SET LAST_UPDATE_DATE = SYSDATE,
973 PROGRAM_UPDATE_DATE = SYSDATE,
974 PROCESS_FLAG = 3,
975 LOCK_FLAG = 2,
976 ERROR_CODE = x_message,
977 ERROR_EXPLANATION = decode(x_sql_err_num,0,NULL,x_sql_message)
978 WHERE rowid = flow_rec.rowid ;
979
980
981 END ;
982 END LOOP ;
983
984
985 exception
986
987 when others then
988
989 -- Error creating the wip flow schedules
990 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_CREATION');
991 fnd_message.set_token('ENTITY1',to_char(p_header_id));
992 x_message := fnd_message.get ;
993
994 x_sql_err_num := SQLCODE;
995 x_sql_message := substr(SQLERRM,1,240);
996
997
998 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
999 SET LAST_UPDATE_DATE = SYSDATE,
1000 PROGRAM_UPDATE_DATE = SYSDATE,
1001 PROCESS_FLAG = 3,
1002 LOCK_FLAG = 2,
1003 ERROR_CODE = x_message,
1004 ERROR_EXPLANATION = decode(x_sql_err_num,0,NULL,x_sql_message)
1005 WHERE transaction_header_id = p_header_id
1006 and transaction_source_type_id = 5
1007 and transaction_action_id in (31, 32, 30); -- CFM Scrap
1008
1009 end Create_Flow_Schedules ;
1010
1011
1012
1013 /* Whenever we call this all the class accounts should be
1014 a snapshot of the class code at that particular time,
1015 before calling this they should have validated the
1016 class code */
1017 function Create_Flow_Schedule(
1018 p_wip_entity_id in number,
1019 p_organization_id in number,
1020 p_last_update_date in date,
1021 p_last_updated_by in number,
1022 p_creation_date in date,
1023 p_created_by in number,
1024 p_last_update_login in number,
1025 p_request_id in number,
1026 p_program_application_id in number,
1027 p_program_id in number,
1028 p_program_update_date in date,
1029 p_primary_item_id in number,
1030 p_class_code in varchar2,
1031 p_scheduled_start_date in date,
1032 p_date_closed in date,
1033 p_planned_quantity in number,
1034 p_quantity_completed in number,
1035 p_quantity_scrapped in number, -- CFM Scrap
1036 p_mps_sched_comp_date in date,
1037 p_mps_net_quantity in number,
1038 p_bom_revision in varchar2,
1039 p_routing_revision in varchar2,
1040 p_bom_revision_date in date,
1041 p_routing_revision_date in date,
1042 p_alternate_bom_designator in varchar2,
1043 p_alternate_routing_designator in varchar2,
1044 p_completion_subinventory in varchar2,
1045 p_completion_locator_id in number,
1046 p_demand_class in varchar2,
1047 p_scheduled_completion_date in date,
1048 p_schedule_group_id in number,
1049 p_build_sequence in number,
1050 p_line_id in number,
1051 p_project_id in number,
1052 p_task_id in number,
1053 p_status in number,
1054 p_schedule_number in varchar2,
1055 p_scheduled_flag in number,
1056 p_unit_number IN VARCHAR2,
1057 p_attribute_category in varchar2,
1058 p_attribute1 in varchar2,
1059 p_attribute2 in varchar2,
1060 p_attribute3 in varchar2,
1061 p_attribute4 in varchar2,
1062 p_attribute5 in varchar2,
1063 p_attribute6 in varchar2,
1064 p_attribute7 in varchar2,
1065 p_attribute8 in varchar2,
1066 p_attribute9 in varchar2,
1067 p_attribute10 in varchar2,
1068 p_attribute11 in varchar2,
1069 p_attribute12 in varchar2,
1070 p_attribute13 in varchar2,
1071 p_attribute14 in varchar2,
1072 p_attribute15 in varchar2 ) return number is
1073 x_material_account number;
1074 x_material_overhead_account number;
1075 x_resource_account number;
1076 x_outside_processing_account number;
1077 x_material_variance_account number;
1078 x_resource_variance_account number;
1079 x_outside_proc_var_account number;
1080 x_std_cost_adjustment_account number;
1081 x_overhead_account number;
1082 x_overhead_variance_account number ;
1083
1084 begin
1085
1086
1087 /* Get the snapshot of the account class */
1088 select material_account,
1089 material_overhead_account,
1090 resource_account,
1091 outside_processing_account,
1092 material_variance_account,
1093 resource_variance_account,
1094 outside_proc_variance_account,
1095 std_cost_adjustment_account,
1096 overhead_account,
1097 overhead_variance_account
1098 into
1099 x_material_account,
1100 x_material_overhead_account,
1101 x_resource_account,
1102 x_outside_processing_account,
1103 x_material_variance_account,
1104 x_resource_variance_account,
1105 x_outside_proc_var_account,
1106 x_std_cost_adjustment_account,
1107 x_overhead_account,
1108 x_overhead_variance_account
1109 from
1110 wip_accounting_classes
1111 where
1112 class_code = p_class_code
1113 and organization_id = p_organization_id;
1114
1115
1116 /* Create the New Flow Schedule */
1117 Insert into wip_flow_schedules(
1118 WIP_ENTITY_ID,
1119 ORGANIZATION_ID,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 CREATION_DATE,
1123 CREATED_BY,
1124 LAST_UPDATE_LOGIN,
1125 REQUEST_ID,
1126 PROGRAM_APPLICATION_ID,
1127 PROGRAM_ID,
1128 PROGRAM_UPDATE_DATE,
1129 PRIMARY_ITEM_ID,
1130 CLASS_CODE,
1131 SCHEDULED_START_DATE,
1132 DATE_CLOSED,
1133 PLANNED_QUANTITY,
1134 QUANTITY_COMPLETED,
1135 QUANTITY_SCRAPPED, -- CFM Scrap
1136 MPS_SCHEDULED_COMPLETION_DATE,
1137 MPS_NET_QUANTITY,
1138 BOM_REVISION,
1139 ROUTING_REVISION,
1140 BOM_REVISION_DATE,
1141 ROUTING_REVISION_DATE,
1142 ALTERNATE_BOM_DESIGNATOR,
1143 ALTERNATE_ROUTING_DESIGNATOR,
1144 COMPLETION_SUBINVENTORY,
1145 COMPLETION_LOCATOR_ID,
1146 MATERIAL_ACCOUNT,
1147 MATERIAL_OVERHEAD_ACCOUNT,
1148 RESOURCE_ACCOUNT,
1149 OUTSIDE_PROCESSING_ACCOUNT,
1150 MATERIAL_VARIANCE_ACCOUNT,
1151 RESOURCE_VARIANCE_ACCOUNT,
1152 OUTSIDE_PROC_VARIANCE_ACCOUNT,
1153 STD_COST_ADJUSTMENT_ACCOUNT,
1154 OVERHEAD_ACCOUNT,
1155 OVERHEAD_VARIANCE_ACCOUNT,
1156 DEMAND_CLASS,
1157 SCHEDULED_COMPLETION_DATE,
1158 SCHEDULE_GROUP_ID,
1159 BUILD_SEQUENCE,
1160 LINE_ID,
1161 PROJECT_ID,
1162 TASK_ID,
1163 STATUS,
1164 SCHEDULE_NUMBER,
1165 SCHEDULED_FLAG,
1166 END_ITEM_UNIT_NUMBER,
1167 ATTRIBUTE_CATEGORY,
1168 ATTRIBUTE1,
1169 ATTRIBUTE2,
1170 ATTRIBUTE3,
1171 ATTRIBUTE4,
1172 ATTRIBUTE5,
1173 ATTRIBUTE6,
1174 ATTRIBUTE7,
1175 ATTRIBUTE8,
1176 ATTRIBUTE9,
1177 ATTRIBUTE10,
1178 ATTRIBUTE11,
1179 ATTRIBUTE12,
1180 ATTRIBUTE13,
1181 ATTRIBUTE14,
1182 ATTRIBUTE15
1183 )
1184 VALUES (
1185 p_wip_entity_id,
1186 p_organization_id,
1187 p_last_update_date,
1188 p_last_updated_by,
1189 p_creation_date,
1190 p_created_by,
1191 p_last_update_login,
1192 p_request_id,
1193 p_program_application_id,
1194 p_program_id,
1195 p_program_update_date,
1196 p_primary_item_id,
1197 p_class_code,
1198 p_scheduled_start_date,
1199 p_date_closed,
1200 p_planned_quantity,
1201 p_quantity_completed,
1202 p_quantity_scrapped, -- CFM Scrap
1203 p_mps_sched_comp_date,
1204 p_mps_net_quantity,
1205 p_bom_revision,
1206 p_routing_revision,
1207 p_bom_revision_date,
1208 p_routing_revision_date,
1209 p_alternate_bom_designator,
1210 p_alternate_routing_designator,
1211 p_completion_subinventory,
1212 p_completion_locator_id,
1213 x_material_account,
1214 x_material_overhead_account,
1215 x_resource_account,
1216 x_outside_processing_account,
1217 x_material_variance_account,
1218 x_resource_variance_account,
1219 x_outside_proc_var_account,
1220 x_std_cost_adjustment_account,
1221 x_overhead_account,
1222 x_overhead_variance_account,
1223 p_demand_class,
1224 p_scheduled_completion_date,
1225 p_schedule_group_id,
1226 p_build_sequence,
1227 p_line_id,
1228 p_project_id,
1229 p_task_id,
1230 p_status,
1231 p_schedule_number,
1232 p_scheduled_flag,
1233 p_unit_number,
1234 p_attribute_category,
1235 p_attribute1,
1236 p_attribute2,
1237 p_attribute3,
1238 p_attribute4,
1239 p_attribute5,
1240 p_attribute6,
1241 p_attribute7,
1242 p_attribute8,
1243 p_attribute9,
1244 p_attribute10,
1245 p_attribute11,
1246 p_attribute12,
1247 p_attribute13,
1248 p_attribute14,
1249 p_attribute15) ;
1250
1251 return 1;
1252
1253 exception
1254 when others then
1255
1256 /*********************************************************************************
1257 * The Error Message in this case is handled by the calling Create_Flow_Schedules *
1258 **********************************************************************************/
1259
1260 return 0;
1261
1262 end Create_Flow_Schedule;
1263
1264
1265
1266
1267
1268 procedure Delete_Flow_Schedules( p_header_id in number ) is
1269
1270 -- **********************************************
1271 -- Cursor to get all the Failed Flow Completions
1272 -- **********************************************
1273 CURSOR Del_Flow(header_id number) is
1274 SELECT transaction_interface_id, rowid,
1275 transaction_source_id, organization_Id
1276 FROM mtl_transactions_interface
1277 WHERE transaction_header_id = header_id
1278 AND TRANSACTION_SOURCE_TYPE_ID = 5
1279 AND FLOW_SCHEDULE = 'Y'
1280 AND scheduled_flag <> 1
1281 AND transaction_action_id in (31, 32, 30) -- CFM Scrap
1282 AND process_flag = 3;
1283
1284 x_message varchar2(240);
1285
1286 begin
1287
1288 FOR del_rec in del_flow(p_header_id) LOOP
1289
1290 BEGIN
1291
1292 delete wip_flow_schedules
1293 where wip_entity_id = del_rec.transaction_source_id
1294 and organization_id = del_rec.organization_id ;
1295
1296 update mtl_transactions_interface
1297 set transaction_source_id = NULL
1298 where rowid = del_rec.rowid ;
1299
1300 exception
1301
1302 when others then
1303
1304 -- Error deleting the wip flow schedules
1305 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_DELETION');
1306 fnd_message.set_token('ENTITY1',
1307 to_char(del_rec.transaction_interface_id) );
1308 x_message := fnd_message.get ;
1309
1310 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
1311 SET LAST_UPDATE_DATE = SYSDATE,
1312 PROGRAM_UPDATE_DATE = SYSDATE,
1313 PROCESS_FLAG = 3,
1314 LOCK_FLAG = 2,
1315 ERROR_CODE = NULL,
1316 ERROR_EXPLANATION = x_message
1317 WHERE rowid = del_rec.rowid ;
1318
1319 Commit ;
1320 END ;
1321
1322 END LOOP ;
1323
1324
1325 exception
1326
1327 when others then
1328
1329 -- Error deleting the wip flow schedules
1330 fnd_message.set_name('WIP', 'WIP_ERROR_FLOW_DELETION');
1331 fnd_message.set_token('ENTITY1',
1332 to_char(p_header_id) );
1333 x_message := fnd_message.get ;
1334
1335
1336 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
1337 SET LAST_UPDATE_DATE = SYSDATE,
1338 PROGRAM_UPDATE_DATE = SYSDATE,
1339 PROCESS_FLAG = 3,
1340 LOCK_FLAG = 2,
1341 ERROR_CODE = NULL,
1342 ERROR_EXPLANATION = x_message
1343 WHERE transaction_header_id = p_header_id
1344 and transaction_source_type_id = 5
1345 and transaction_action_id in (31, 32);
1346
1347 Commit ;
1348
1349 end Delete_Flow_Schedules ;
1350
1351
1352
1353
1354
1355
1356 procedure Delete_Flow_Schedule( p_wip_entity_id in number ) is
1357 begin
1358
1359 delete wip_flow_schedules
1360 where wip_entity_id = p_wip_entity_id ;
1361
1362 exception
1363 when others then
1364
1365 /*********************************************************************************
1366 * The Error Message in this case is handled by the calling Delete_Flow_Schedules *
1367 **********************************************************************************/
1368
1369 null ;
1370
1371 end Delete_Flow_Schedule ;
1372
1373
1374 /*
1375 It's a function called by Update_Flow_Schedule.
1376 This function determines if the schedule need to open / close :
1377 - 0 means : no change
1378 - 1 means : reopen the schedule
1379 - 2 means : close the schedule
1380 */
1381 function Status_Change(p_planned_qty number,
1382 p_cur_completed_qty number,
1383 p_qty_completed number) return number is
1384 l_new_completed_qty number;
1385 begin
1386 l_new_completed_qty := p_cur_completed_qty + p_qty_completed;
1387
1388 if (l_new_completed_qty >= p_planned_qty) then
1389 -- Only close the schedule if the current completed quantity < planned quantity
1390 if (p_cur_completed_qty < p_planned_qty) then
1391 return 2;
1392 else
1393 return 0;
1394 end if;
1395 else
1396 -- This logic is to reopen the schedule that has been previously closed
1397 -- and need to be reopen. It's caused from the Assembly return of
1398 -- the schedule that has been fully completed/over completed before.
1399 -- This schedule is characterized by the fact that
1400 -- 1. New completed quantity < planned quantity
1401 -- 2. Current completed quantity >= planned quantity
1402 if (p_cur_completed_qty >= p_planned_qty) then
1403 return 1;
1404 else
1405 return 0;
1406 end if;
1407 end if;
1408 end Status_Change;
1409
1410
1411 function Update_Flow_Schedule( p_wip_entity_id in number,
1412 p_quantity_completed in number, -- CFM Scrap (primary qty)
1413 p_quantity_scrapped IN NUMBER, -- CFM Scrap (primary qty)
1414 p_transaction_date in date,
1415 p_schedule_flag in varchar2,
1416 p_last_updated_by number ) return number is
1417 begin
1418
1419 -- This has to atleast perform the following functions
1420 -- 1. Update the Completed Quantity (or the scrapped quantity)
1421 -- (the sign is changed to handle it from WIP perspective.)
1422 -- 2. Completion_Date
1423 -- 3. set the status flag
1424 -- ----------------------------------------------------
1425
1426 -- Set the DATE_CLOSED and STATUS based on :
1427 -- 1. NVL(p_schedule_flag,'N') = 'Y'
1428 -- 2. Status_Change() returns value
1429
1430 Update wip_flow_schedules
1431 set QUANTITY_COMPLETED = Nvl(QUANTITY_COMPLETED,0)+(p_quantity_completed * -1), -- CFM Scrap
1432 QUANTITY_SCRAPPED = Nvl(QUANTITY_SCRAPPED,0)+(p_quantity_scrapped * -1), -- CFM Scrap
1433 TRANSACTED_FLAG = 'Y',
1434 DATE_CLOSED =
1435 decode(UPPER(NVL(p_schedule_flag,'N')), 'Y',
1436 decode(Wip_Flow_Utilities.Status_Change(PLANNED_QUANTITY,QUANTITY_COMPLETED,p_quantity_completed*-1),
1437 0,DATE_CLOSED,1,null,2,p_transaction_date),
1438 DATE_CLOSED),
1439 STATUS =
1440 decode(UPPER(NVL(p_schedule_flag,'N')), 'Y',
1441 decode(Wip_Flow_Utilities.Status_Change(PLANNED_QUANTITY,QUANTITY_COMPLETED,p_quantity_completed*-1),
1442 0,STATUS,1,1,2,2),
1443 STATUS),
1444 LAST_UPDATED_BY = p_last_updated_by,
1445 LAST_UPDATE_DATE = sysdate
1446 where wip_entity_id = p_wip_entity_id ;
1447
1448 return 1;
1449
1450 exception
1451 when others then
1452
1453 /*********************************************************************************
1454 * The Error Message in this case is handled by the calling Delete_Flow_Schedules *
1455 **********************************************************************************/
1456 return 0;
1457
1458 end Update_Flow_Schedule ;
1459
1460
1461
1462
1463 function Pre_Inv_Validations(p_interface_id in number,
1464 p_org_id in number,
1465 p_user_id in number,
1466 p_login_id in number,
1467 p_appl_id in number,
1468 p_prog_id in number,
1469 p_reqstid in number,
1470 p_err_num in out nocopy number,
1471 p_err_mesg in out nocopy varchar2,
1472 p_hdr_id in out nocopy number)
1473 return number is
1474 x_new_txn_hdr number;
1475 begin
1476
1477 select MTL_MATERIAL_TRANSACTIONS_S.nextval into x_new_txn_hdr
1478 from sys.dual ;
1479
1480
1481 Update Mtl_transactions_interface
1482 set TRANSACTION_HEADER_ID = x_new_txn_hdr,
1483 PROCESS_FLAG = 1,
1484 LAST_UPDATE_DATE = SYSDATE,
1485 LAST_UPDATED_BY = p_user_id,
1486 LAST_UPDATE_LOGIN = p_login_id,
1487 PROGRAM_APPLICATION_ID = p_appl_id,
1488 PROGRAM_ID = p_prog_id,
1489 REQUEST_ID = p_reqstid,
1490 PROGRAM_UPDATE_DATE = SYSDATE,
1491 LOCK_FLAG = 1,
1492 ERROR_CODE = NULL,
1493 ERROR_EXPLANATION = NULL,
1494 TRANSACTION_MODE = 3
1495 where
1496 parent_id = p_interface_id
1497 AND TRANSACTION_SOURCE_TYPE_ID = 5
1498 AND FLOW_SCHEDULE = 'Y'
1499 AND TRANSACTION_ACTION_ID IN (1,27,33,34)
1500 AND PROCESS_FLAG = 2
1501 AND NVL(LOCK_FLAG,2) = 2;
1502
1503
1504 p_hdr_id := x_new_txn_hdr ;
1505 return 1;
1506
1507 exception
1508 when others then
1509
1510 /*********************************************************************************
1511 * The Error Message in this case is handled by the calling wilctv.ppc *
1512 **********************************************************************************/
1513 return 0;
1514
1515
1516 end Pre_Inv_Validations;
1517
1518
1519
1520
1521
1522
1523
1524 function Post_Inv_Validations(p_interface_id in number,
1525 p_org_id in number,
1526 p_user_id in number,
1527 p_login_id in number,
1528 p_appl_id in number,
1529 p_prog_id in number,
1530 p_reqstid in number,
1531 p_err_num in out nocopy number,
1532 p_err_mesg in out nocopy varchar2,
1533 p_hdr_id in number,
1534 p_org_hdr_id in number)
1535 return number is
1536 x_new_txn_hdr number;
1537 begin
1538
1539
1540 -- Update the header id of the original completion
1541 Update Mtl_transactions_interface
1542 set TRANSACTION_HEADER_ID = p_hdr_id
1543 where transaction_interface_id = p_interface_id ;
1544
1545 -- Set the information for the backflushed/substitution
1546 -- issues
1547 Update Mtl_transactions_interface
1548 set PROCESS_FLAG = 1,
1549 LAST_UPDATE_DATE = SYSDATE,
1550 LAST_UPDATED_BY = p_user_id,
1551 LAST_UPDATE_LOGIN = p_login_id,
1552 PROGRAM_APPLICATION_ID = p_appl_id,
1553 PROGRAM_ID = p_prog_id,
1554 REQUEST_ID = p_reqstid,
1555 PROGRAM_UPDATE_DATE = SYSDATE,
1556 LOCK_FLAG = 1,
1557 ERROR_CODE = NULL,
1558 ERROR_EXPLANATION = NULL
1559 where
1560 parent_id = p_interface_id
1561 and transaction_header_id = p_hdr_id;
1562
1563 return 1;
1564
1565 exception
1566 when others then
1567
1568 /*********************************************************************************
1569 * The Error Message in this case is handled by the calling wilctv.ppc *
1570 **********************************************************************************/
1571 return 0;
1572
1573 end Post_Inv_Validations;
1574
1575
1576
1577 function Post_Transaction_Cleanup(p_header_id in number)
1578 return number is
1579 x_wip_entity_id number;
1580 x_txn_date date;
1581 x_cpl_qty number;
1582 x_scr_qty NUMBER; -- CFM Scrap
1583 x_flow_schedule varchar(1) := 'N';
1584 x_success number := 0;
1585 x_last_updated_by number ; /* Fix for Bug#2517396 */
1586
1587 begin
1588
1589 SELECT DISTINCT
1590 transaction_source_id,
1591 Decode( transaction_action_id, 30, 0, (primary_quantity)*-1),-- CFM Scrap
1592 Decode( transaction_action_id, 30, (primary_quantity)*-1, 0),-- CFM Scrap
1593 transaction_date,
1594 flow_schedule,
1595 last_updated_by
1596 into x_wip_entity_id,
1597 x_cpl_qty, -- CFM Scrap
1598 x_scr_qty, -- CFM Scrap
1599 x_txn_date,
1600 x_flow_schedule,
1601 x_last_updated_by
1602 from mtl_material_transactions
1603 where transaction_set_id = p_header_id
1604 and transaction_action_id in (31, 32, 30); -- CFM Scrap
1605
1606 x_success := Update_Flow_Schedule( x_wip_entity_id, x_cpl_qty, x_scr_qty, -- CFM Scrap
1607 x_txn_date, x_flow_schedule, x_last_updated_by) ;
1608
1609 if(x_success=0) then
1610
1611 /**********************************************************************
1612 * The Error Message in this case is handled by the calling wilctv.ppc *
1613 **********************************************************************/
1614 return 0;
1615
1616 end if;
1617
1618
1619 /******************************************************************
1620 * All the Correlated Sub Queries that we used for cleaning up the *
1621 * Serial and Lot Information for the Completion/Return are now *
1622 * changed to joins inorder to remove Full Table Scans on the child*
1623 * tables - MSNI, MTLI *
1624 * - bbaby, dsoosai 12/11/97 *
1625 ******************************************************************/
1626
1627 DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
1628 WHERE TRANSACTION_INTERFACE_ID IN (
1629 SELECT MTI.TRANSACTION_INTERFACE_ID
1630 FROM MTL_TRANSACTIONS_INTERFACE MTI
1631 WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
1632 AND MTI.PROCESS_FLAG = 1);
1633
1634 DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE MSNI
1635 WHERE TRANSACTION_INTERFACE_ID IN (
1636 SELECT MTLI.SERIAL_TRANSACTION_TEMP_ID
1637 FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
1638 MTL_TRANSACTIONS_INTERFACE MTI
1639 WHERE MTI.TRANSACTION_INTERFACE_ID =
1640 MTLI.TRANSACTION_INTERFACE_ID
1641 AND MTI.TRANSACTION_HEADER_ID = p_header_id
1642 AND MTI.PROCESS_FLAG = 1);
1643
1644 DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
1645 WHERE TRANSACTION_INTERFACE_ID IN (
1646 SELECT MTI.TRANSACTION_INTERFACE_ID
1647 FROM MTL_TRANSACTIONS_INTERFACE MTI
1648 WHERE MTI.TRANSACTION_HEADER_ID = p_header_id
1649 AND MTI.PROCESS_FLAG = 1);
1650
1651 DELETE FROM MTL_TRANSACTIONS_INTERFACE
1652 WHERE TRANSACTION_HEADER_ID = p_header_id
1653 AND PROCESS_FLAG = 1;
1654
1655 return 1;
1656
1657 exception
1658 when others then
1659
1660 /*********************************************************************************
1661 * The Error Message in this case is handled by the calling wilctv.ppc *
1662 **********************************************************************************/
1663 return 0;
1664
1665 end Post_Transaction_Cleanup ;
1666
1667
1668
1669
1670
1671 function Check_Validation_Errors(p_header_id in number,
1672 p_err_num in out nocopy number,
1673 p_err_mesg in out nocopy varchar2) return number is
1674 l_err_mesg varchar2(5000);
1675 begin
1676
1677 select ERROR_EXPLANATION
1678 into l_err_mesg
1679 from MTL_TRANSACTIONS_INTERFACE
1680 where PROCESS_FLAG = 3
1681 AND LOCK_FLAG = 2
1682 AND TRANSACTION_HEADER_ID = p_header_id
1683 AND ROWNUM <2;
1684
1685 p_err_mesg := SUBSTR(l_err_mesg, 1, 240);
1686
1687 /*********************************************************************************
1688 * The Error Message in this case is handled by the calling wilctv.ppc *
1689 **********************************************************************************/
1690
1691 return 0;
1692
1693
1694 exception
1695 when no_data_found then
1696 return 1;
1697 when others then
1698
1699 /*********************************************************************************
1700 * The Error Message in this case is handled by the calling wilctv.ppc *
1701 **********************************************************************************/
1702 return 0;
1703
1704 end Check_Validation_Errors;
1705
1706
1707 function Flow_Error_Cleanup(p_txn_int_id in number,
1708 p_wip_entity_id in number,
1709 p_user_id in number,
1710 p_login_id in number,
1711 p_err_mesg in out nocopy varchar2
1712 ) return number is
1713 l_unsched number := 0;
1714 begin
1715
1716 UPDATE MTL_TRANSACTIONS_INTERFACE MTI
1717 SET LAST_UPDATE_DATE = SYSDATE,
1718 LAST_UPDATED_BY = p_user_id,
1719 LAST_UPDATE_LOGIN = p_login_id,
1720 PROGRAM_UPDATE_DATE = SYSDATE,
1721 PROCESS_FLAG = 3,
1722 LOCK_FLAG = 2,
1723 ERROR_CODE = substrb(p_err_mesg,1,240),
1724 ERROR_EXPLANATION = substrb(p_err_mesg,1,240)
1725 WHERE (TRANSACTION_INTERFACE_ID = p_txn_int_id
1726 or parent_id = p_txn_int_id)
1727 AND process_flag <> 3 ;
1728
1729
1730 select 1 into l_unsched
1731 from wip_flow_schedules
1732 where wip_entity_id = p_wip_entity_id
1733 and scheduled_flag <> 1;
1734
1735 if(l_unsched = 1) then
1736 Delete_Flow_Schedule(p_wip_entity_id);
1737 end if ;
1738
1739 return 1;
1740
1741 exception
1742 when no_data_found then
1743 return 1;
1744 when others then
1745 -- Error Deleting the wip flow schedules
1746 fnd_message.set_name('WIP', 'WIP_FLOW_CLEANUP_ERROR');
1747 p_err_mesg := fnd_message.get ;
1748 return 0;
1749
1750 end Flow_Error_Cleanup;
1751
1752 /* Construct_Wip_Line_Ops: This function constructs the wip line ops table of records
1753 by calling the appropriate BOM API.
1754
1755 If the p_teriminal_op_seq_num is null, this API will try to derive the
1756 operation seq num from the operation seq id. Then it calls the BOM API to get all
1757 the line ops before the terminal line op in the primary path of the routing network.
1758
1759 If the terminal line op is -1 then all the line ops in the primary path of the
1760 routing network are cached.
1761
1762 If the terminal line op is -2 then all the line ops (except rework loops) in the
1763 routing network are cached.
1764 */
1765
1766 PROCEDURE Construct_Wip_Line_Ops(p_routing_sequence_id IN NUMBER,
1767 p_terminal_op_seq_num IN NUMBER,
1768 p_terminal_op_seq_id IN NUMBER,
1769 p_date IN DATE DEFAULT NULL)
1770
1771 IS
1772 /* Note: In this procedure op seq always refers to line op seq. */
1773
1774 l_op_exists_in_network NUMBER;
1775 l_terminal_op_seq_num NUMBER;
1776 l_assembly_item_id NUMBER;
1777 l_org_id NUMBER;
1778 l_alt_rout_desig VARCHAR2(10);
1779 BEGIN
1780
1781 IF (p_terminal_op_seq_num IS NULL
1782 AND Nvl(p_terminal_op_seq_id,-1) > 0) THEN
1783 SELECT operation_seq_num
1784 INTO l_terminal_op_seq_num
1785 FROM bom_operation_sequences
1786 WHERE routing_sequence_id = p_routing_sequence_id
1787 AND operation_sequence_id = p_terminal_op_seq_id;
1788 ELSIF p_terminal_op_seq_num IS NOT NULL THEN
1789 l_terminal_op_seq_num := p_terminal_op_seq_num;
1790 END IF;
1791
1792 IF (wip_line_ops.COUNT = 0) THEN
1793
1794 IF (l_terminal_op_seq_num > 0 ) THEN
1795 Bom_RTG_Network_API.get_primary_prior_line_ops(p_routing_sequence_id,
1796 NULL,
1797 NULL,
1798 NULL,
1799 l_terminal_op_seq_num,
1800 wip_line_ops);
1801 ELSIF (l_terminal_op_seq_num = -1) THEN
1802 Bom_RTG_Network_API.get_all_primary_line_ops(p_routing_sequence_id,
1803 NULL,
1804 NULL,
1805 NULL,
1806 wip_line_ops);
1807
1808 IF wip_line_ops.COUNT = 0 THEN
1809 -- There are no line ops defined
1810 wip_line_ops(1).operation_sequence_id := 0;
1811 wip_line_ops(1).operation_seq_num := 0;
1812 END IF;
1813
1814 ELSIF (l_terminal_op_seq_num = -2) THEN
1815 Bom_RTG_Network_API.get_all_line_ops(p_routing_sequence_id,
1816 NULL,
1817 NULL,
1818 NULL,
1819 wip_line_ops);
1820
1821 ELSE
1822 NULL;
1823 END IF;
1824
1825 END IF;
1826
1827
1828
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 NULL;
1832 -- Check for exceptions from the BOM APIs.
1833
1834 END Construct_Wip_Line_Ops;
1835
1836 /* This function checks the wip_line_ops table of records to see if the given op_seq_id exists in the table */
1837
1838 FUNCTION line_op_exists(p_op_seq_id IN NUMBER)
1839 RETURN NUMBER
1840 IS
1841 l_success NUMBER := 0;
1842 BEGIN
1843
1844 FOR i IN 1..wip_line_ops.COUNT LOOP
1845 IF wip_line_ops(i).operation_sequence_id = p_op_seq_id THEN
1846 l_success := 1;
1847 EXIT;
1848 END IF;
1849 END LOOP;
1850
1851 RETURN l_success;
1852
1853 END line_op_exists;
1854
1855 /* This procedure clears out nocopy the wip_line_ops table of records */
1856
1857 PROCEDURE clear_wip_line_ops_cache
1858 IS
1859 general EXCEPTION;
1860 BEGIN
1861
1862 wip_line_ops.delete;
1863
1864 IF wip_line_ops.COUNT > 0 THEN
1865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1866 END IF;
1867
1868 END clear_wip_line_ops_cache;
1869
1870 /* The following function is only for debugging purpose and is not used in the actual logic */
1871
1872 PROCEDURE show_wip_line_ops(x_all_ops OUT NOCOPY VARCHAR2)
1873 IS
1874 l_all_ops VARCHAR2(4000) := NULL;
1875 BEGIN
1876
1877 FOR i IN 1..wip_line_ops.COUNT LOOP
1878 l_all_ops := l_all_ops||', '||To_char(wip_line_ops(i).operation_sequence_id);
1879 END LOOP;
1880 x_all_ops := l_all_ops;
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 NULL;
1885 END show_wip_line_ops;
1886
1887 /* This function checks the sequence of two line ops. It returns 1 if p_line_op_seq1 is
1888 the same or prior to p_line_op_seq2. Otherwise it returns 2. In the case of error -1 is
1889 returned. */
1890
1891 /* If line_op_seq_id is not specified it is derived from line_op_seq_num. However, if
1892 both op_seq_num and op_seq_id are provided it doesnot check if op_seq_id and
1893 op_seq_num are consistent with each other
1894 Note that a line_op_seq_num of -1 is a fictitious line_op that is used to group all
1895 events that do not belong to any line op. This fictitious line op is assumed to be
1896 before the first line op in the routing of the assembly */
1897
1898 /* If p_destroy_cache is set to Y then the wip_line_ops table of records will be
1899 deleted. So set it to Y only if you are calling this function once. However if you
1900 are calling it over and over again with the same p_line_op_seq2_id, it makes more
1901 sense to set the destroy_cache to N because creating this table of records can be
1902 expensive. */
1903
1904
1905 FUNCTION Line_Op_same_or_prior(p_routing_sequence_id IN NUMBER,
1906 p_eff_date IN DATE,
1907 p_line_op_seq1_id IN NUMBER,
1908 p_line_op_seq1_num IN NUMBER,
1909 p_line_op_seq2_id IN NUMBER,
1910 p_line_op_seq2_num IN NUMBER,
1911 p_destroy_cache IN VARCHAR2) RETURN NUMBER
1912 IS
1913 l_success NUMBER := 0;
1914 l_line_op_seq2_id NUMBER;
1915 l_line_op_seq1_id NUMBER;
1916 BEGIN
1917
1918
1919 -- Raise unexpected error if insufficient parameters.
1920
1921 IF (p_routing_sequence_id IS NULL
1922 OR p_eff_date IS NULL
1923 OR (p_line_op_seq1_id IS NULL AND p_line_op_seq1_num IS NULL)
1924 OR (p_line_op_seq2_id IS NULL AND p_line_op_seq2_num IS NULL)) THEN
1925 l_success := -1;
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 END IF;
1928
1929 IF p_line_op_seq2_id IS NULL
1930 AND p_line_op_seq2_num > 0 THEN
1931 SELECT operation_sequence_id
1932 INTO l_line_op_seq2_id
1933 FROM bom_operation_sequences
1934 WHERE routing_sequence_id = p_routing_sequence_id
1935 AND operation_seq_num = p_line_op_seq2_num
1936 AND operation_type = 3;
1937 ELSE
1938 l_line_op_seq2_id := p_line_op_seq2_id;
1939 END IF;
1940
1941 IF p_line_op_seq1_id IS NULL
1942 AND p_line_op_seq1_num > 0 THEN
1943 SELECT operation_sequence_id
1944 INTO l_line_op_seq1_id
1945 FROM bom_operation_sequences
1946 WHERE routing_sequence_id = p_routing_sequence_id
1947 AND operation_seq_num = p_line_op_seq1_num
1948 AND operation_type = 3;
1949 ELSE
1950 l_line_op_seq1_id := p_line_op_seq1_id;
1951 END IF;
1952
1953
1954
1955 -- If both the line ops are the same
1956 IF p_line_op_seq1_num = p_line_op_seq2_num THEN
1957 RETURN 1;
1958 END IF;
1959
1960 -- If the second line op is fictitious (and the first one is not)
1961 /*
1962 IF (p_line_op_seq2_num = -1 OR l_line_op_seq2_id = -1) THEN
1963 RETURN 2;
1964 END IF;
1965 */
1966
1967 -- Create the wip_line_ops table of records if one doesn't exist
1968
1969 IF wip_line_ops.COUNT = 0 THEN
1970
1971 construct_wip_line_ops(p_routing_sequence_id,
1972 p_line_op_seq2_num,
1973 l_line_op_seq2_id,
1974 p_eff_date);
1975 END IF;
1976
1977 IF(line_op_exists(l_line_op_seq1_id) = 1) THEN
1978 l_success := 1;
1979 ELSE
1980 l_success := 2;
1981 END IF;
1982
1983
1984 IF p_destroy_cache = 'Y' THEN
1985 clear_wip_line_ops_cache;
1986 END IF;
1987
1988
1989 RETURN l_success;
1990
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 RETURN -1;
1994
1995 END Line_Op_same_or_prior;
1996
1997 /* This is the same as the previous function but lacks the cache handling part.
1998 This function is created especially for use with WNDS, WNPS restrictions.
1999 This means it neither creates nor destroys the wip_line_ops table of records,
2000 but just reads from it.*/
2001
2002 FUNCTION same_or_prior_safe(p_routing_sequence_id IN NUMBER,
2003 p_eff_date IN DATE,
2004 p_line_op_seq1_id IN NUMBER,
2005 p_line_op_seq1_num IN NUMBER,
2006 p_line_op_seq2_id IN NUMBER,
2007 p_line_op_seq2_num IN NUMBER) RETURN NUMBER
2008 IS
2009 l_success NUMBER := 0;
2010 l_line_op_seq2_id NUMBER;
2011 l_line_op_seq1_id NUMBER;
2012 BEGIN
2013
2014 IF p_line_op_seq2_id IS NULL
2015 AND p_line_op_seq2_num > 0 THEN
2016 SELECT operation_sequence_id
2017 INTO l_line_op_seq2_id
2018 FROM bom_operation_sequences
2019 WHERE routing_sequence_id = p_routing_sequence_id
2020 AND operation_seq_num = p_line_op_seq2_num
2021 AND operation_type = 3;
2022 ELSE
2023 l_line_op_seq2_id := p_line_op_seq2_id;
2024 END IF;
2025
2026 IF p_line_op_seq1_id IS NULL
2027 AND p_line_op_seq1_num > 0 THEN
2028 SELECT operation_sequence_id
2029 INTO l_line_op_seq1_id
2030 FROM bom_operation_sequences
2031 WHERE routing_sequence_id = p_routing_sequence_id
2032 AND operation_seq_num = p_line_op_seq1_num
2033 AND operation_type = 3;
2034 ELSE
2035 l_line_op_seq1_id := p_line_op_seq1_id;
2036 END IF;
2037
2038
2039 -- If both the line ops are the same
2040 IF p_line_op_seq1_num = p_line_op_seq2_num THEN
2041 RETURN 1;
2042 END IF;
2043
2044 -- If the second line op is fictitious (and the first one is not)
2045 /*
2046 IF p_line_op_seq2_num = -1
2047 OR l_line_op_seq2_id = -1 THEN
2048 RETURN 2;
2049 END IF;
2050 */
2051
2052 IF(line_op_exists(l_line_op_seq1_id) = 1) THEN
2053 l_success := 1;
2054 ELSE
2055 l_success := 2;
2056 END IF;
2057
2058
2059 RETURN l_success;
2060
2061 END same_or_prior_safe;
2062
2063 /* This procedure returns the line op seq num and the line op seq id of the line op to
2064 which a given event belongs. If no line op is found for this event, both the opseq_num
2065 and the opseq_id are set to -1*/
2066
2067 PROCEDURE get_line_op_from_event(p_routing_sequence_id IN NUMBER,
2068 p_eff_date IN DATE,
2069 p_event_op_seq_num IN NUMBER,
2070 x_line_op_seq_num OUT NOCOPY NUMBER,
2071 x_line_op_seq_id OUT NOCOPY NUMBER)
2072 IS
2073 l_line_op_seq_num NUMBER := 0;
2074 l_line_op_seq_id NUMBER := 0;
2075 BEGIN
2076
2077 SELECT bos2.operation_seq_num, bos2.operation_sequence_id
2078 INTO l_line_op_seq_num, l_line_op_seq_id
2079 FROM bom_operation_sequences bos1, bom_operation_sequences bos2
2080 WHERE bos2.operation_sequence_id = bos1.line_op_seq_id
2081 AND bos1.routing_sequence_id = p_routing_sequence_id
2082 AND bos1.operation_seq_num = p_event_op_seq_num
2083 AND bos1.effectivity_date <= p_eff_date
2084 AND Nvl(bos1.disable_date,p_eff_date+1) > p_eff_date
2085 AND bos1.operation_type = 1;
2086
2087 x_line_op_seq_num := l_line_op_seq_num;
2088 x_line_op_seq_id := l_line_op_seq_id;
2089
2090 EXCEPTION
2091 WHEN no_data_found THEN
2092 x_line_op_seq_num := -1;
2093 x_line_op_seq_id := -1;
2094
2095 WHEN OTHERS THEN
2096 x_line_op_seq_num := NULL;
2097 x_line_op_seq_id := NULL;
2098
2099 END get_line_op_from_event;
2100
2101 /* This is the function version of the above procedure */
2102
2103 FUNCTION event_to_lineop_seq_num(p_routing_sequence_id IN NUMBER,
2104 p_eff_date IN DATE,
2105 p_event_op_seq_num IN NUMBER) RETURN NUMBER
2106 IS
2107 l_line_op_seq_num NUMBER := NULL;
2108 l_line_op_seq_id NUMBER;
2109 BEGIN
2110 get_line_op_from_event(p_routing_sequence_id => p_routing_sequence_id,
2111 p_eff_date => p_eff_date,
2112 p_event_op_seq_num => p_event_op_seq_num,
2113 x_line_op_seq_num => l_line_op_seq_num,
2114 x_line_op_seq_id => l_line_op_seq_id);
2115 RETURN l_line_op_seq_num;
2116
2117 EXCEPTION
2118 WHEN OTHERS THEN
2119 RETURN NULL;
2120
2121 END event_to_lineop_seq_num;
2122
2123 /* This function checks to see if a given event op seq is in the same line
2124 ops as p_line_op_seq_num or if it is in a line op that is prior
2125 to p_line_op_seq_num. This fucntion returns 1 on success, 2 or failure
2126 and -1 on error. This function works by conventing the given event
2127 to its line op and the calls the line op comparison function,
2128 so destroy cache is used exactly in the same way as
2129 in the lineop comparison function*/
2130
2131
2132 FUNCTION Event_in_same_or_prior_lineop(p_routing_sequence_id IN NUMBER,
2133 p_eff_date IN DATE,
2134 p_event_op_seq_num IN NUMBER,
2135 p_line_op_seq_num IN NUMBER,
2136 p_destroy_cache IN VARCHAR2) RETURN NUMBER
2137 IS
2138 l_success NUMBER := 0;
2139 l_line_op_seq1_num NUMBER;
2140 l_line_op_seq1_id NUMBER;
2141 l_return_status VARCHAR2(1);
2142 BEGIN
2143
2144
2145 -- Insufficient arguments
2146
2147 IF (p_routing_sequence_id IS NULL
2148 OR p_eff_date IS NULL
2149 OR p_event_op_seq_num IS NULL
2150 OR p_line_op_seq_num IS NULL) THEN
2151
2152 l_success := -1;
2153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2154 END IF;
2155
2156 get_line_op_from_event(p_routing_sequence_id,
2157 p_eff_date,
2158 p_event_op_seq_num,
2159 l_line_op_seq1_num,
2160 l_line_op_seq1_id);
2161
2162 -- IF l_line_op_seq1_num IS NULL THEN
2163 -- l_success := -1;
2164 -- ELSIF l_line_op_seq1_num = -1 THEN
2165 -- -- The event specified does not have a line op return success.
2166 --
2167 -- l_success := 1;
2168 -- ELSE
2169 l_success := Line_Op_same_or_prior(p_routing_sequence_id => p_routing_sequence_id,
2170 p_eff_date => p_eff_date,
2171 p_line_op_seq1_id => l_line_op_seq1_id,
2172 p_line_op_seq1_num => l_line_op_seq1_num,
2173 p_line_op_seq2_id => NULL,
2174 p_line_op_seq2_num => p_line_op_seq_num,
2175 p_destroy_cache => p_destroy_cache);
2176 -- END IF;
2177
2178
2179 RETURN l_success;
2180 EXCEPTION
2181 WHEN OTHERS THEN
2182 RETURN -1;
2183
2184 END Event_in_same_or_prior_lineop;
2185
2186 /* This function the similar to the one before but has the WNDS, WNPS pragma set so it calls the "safe" version of the lineop comparison function */
2187
2188 FUNCTION same_or_prior_lineop_safe(p_routing_sequence_id IN NUMBER,
2189 p_eff_date IN DATE,
2190 p_event_op_seq_num IN NUMBER,
2191 p_line_op_seq_num IN NUMBER) RETURN NUMBER
2192 IS
2193 l_success NUMBER := 0;
2194 l_line_op_seq1_num NUMBER;
2195 l_line_op_seq1_id NUMBER;
2196 l_return_status VARCHAR2(1);
2197 BEGIN
2198
2199 get_line_op_from_event(p_routing_sequence_id,
2200 p_eff_date,
2201 p_event_op_seq_num,
2202 l_line_op_seq1_num,
2203 l_line_op_seq1_id);
2204
2205 IF l_line_op_seq1_num IS NULL THEN
2206 l_success := -1;
2207 ELSIF l_line_op_seq1_num = -1 THEN
2208 -- The event specified does not have a line op. All such events are grouped together before the first line op.
2209
2210 l_success := 1;
2211 ELSE
2212 l_success := same_or_prior_safe(p_routing_sequence_id => p_routing_sequence_id,
2213 p_eff_date => p_eff_date,
2214 p_line_op_seq1_id => l_line_op_seq1_id,
2215 p_line_op_seq1_num => l_line_op_seq1_num,
2216 p_line_op_seq2_id => NULL,
2217 p_line_op_seq2_num => p_line_op_seq_num);
2218 END IF;
2219
2220
2221 RETURN l_success;
2222 EXCEPTION
2223 WHEN OTHERS THEN
2224 RETURN -1;
2225
2226 END same_or_prior_lineop_safe;
2227
2228 PROCEDURE default_lots(txn_interface_id IN NUMBER, txn_source_name IN VARCHAR2,
2229 txn_type_id IN NUMBER, wip_entity_id IN NUMBER) IS
2230
2231 /* attributes of a given lot */
2232 Cursor c_lot(p_org_id NUMBER, p_inventory_item_id NUMBER, p_lot_number VARCHAR2) IS
2233 select
2234 lot_number,
2235 expiration_date as lot_expiration_date,
2236 description,
2237 vendor_name,
2238 supplier_lot_number,
2239 grade_code,
2240 origination_date,
2241 date_code,
2242 status_id,
2243 change_date,
2244 age,
2245 retest_date,
2246 maturity_date,
2247 lot_attribute_category,
2248 item_size,
2249 color,
2250 volume,
2251 volume_uom,
2252 place_of_origin,
2253 best_by_date,
2254 length,
2255 length_uom,
2256 recycled_content,
2257 thickness,
2258 thickness_uom,
2259 width,
2260 width_uom,
2261 curl_wrinkle_fold,
2262 c_attribute1,
2263 c_attribute2,
2264 c_attribute3,
2265 c_attribute4,
2266 c_attribute5,
2267 c_attribute6,
2268 c_attribute7,
2269 c_attribute8,
2270 c_attribute9,
2271 c_attribute10,
2272 c_attribute11,
2273 c_attribute12,
2274 c_attribute13,
2275 c_attribute14,
2276 c_attribute15,
2277 c_attribute16,
2278 c_attribute17,
2279 c_attribute18,
2280 c_attribute19,
2281 c_attribute20,
2282 d_attribute1,
2283 d_attribute2,
2284 d_attribute3,
2285 d_attribute4,
2286 d_attribute5,
2287 d_attribute6,
2288 d_attribute7,
2289 d_attribute8,
2290 d_attribute9,
2291 d_attribute10,
2292 n_attribute1,
2293 n_attribute2,
2294 n_attribute3,
2295 n_attribute4,
2296 n_attribute5,
2297 n_attribute6,
2298 n_attribute7,
2299 n_attribute8,
2300 n_attribute9,
2301 n_attribute10,
2302 vendor_id,
2303 territory_code
2304 from mtl_lot_numbers
2305 where organization_id = p_org_id
2306 and inventory_item_id = p_inventory_item_id
2307 and lot_number = p_lot_number;
2308
2309 Cursor c_lotItems IS
2310 select mti.operation_seq_num,
2311 mti.inventory_item_id,
2312 msi.concatenated_segments,
2313 mti.primary_quantity * -1 primary_quantity,
2314 mti.transaction_quantity * -1 transaction_quantity,
2315 msi.primary_uom_code,
2316 mti.subinventory_code,
2317 mti.locator_id,
2318 msi.mtl_transactions_enabled_flag,
2319 msi.serial_number_control_code,
2320 msi.lot_control_code,
2321 mti.revision,
2322 mti.organization_id,
2323 mti.transaction_source_id,
2324 mti.transaction_action_id,
2325 mti.transaction_interface_id,
2326 mti.transaction_source_name,
2327 mti.transfer_cost_group_id cost_group_id,
2328 least( 1, NVL(mti.transfer_lpn_id, 0) + NVL(mti.content_lpn_id, 0) ) containerized,
2329 mti.transaction_header_id -- bugfix 4455722
2330 from mtl_transactions_interface mti,
2331 mtl_system_items_kfv msi
2332 where mti.parent_id = txn_interface_id
2333 and nvl(mti.operation_seq_num, 1) > 0 /* not for phatom assembly */
2334 and mti.transaction_action_id in (1,27,33,34) /*wip_constants.isscomp_action, wip_constants.retcomp_action, wip_constants.issnegc_action, wip_constants.retnegc_action)*/
2335 and mti.inventory_item_id = msi.inventory_item_id
2336 and mti.organization_id = msi.organization_id
2337 and msi.lot_control_code = 2
2338 and msi.serial_number_control_code = 1;
2339
2340 l_lotItemsRecords c_lotItems%ROWTYPE;
2341 l_lotNumber c_lot%ROWTYPE;
2342
2343 x_returnStatus varchar2(1);
2344 l_compObj system.wip_lot_serial_obj_t;
2345 l_lot system.wip_txn_lot_obj_t;
2346 l_org_id NUMBER;
2347 l_wipentityid NUMBER;
2348 l_errorcode NUMBER;
2349 l_errormesg VARCHAR2(100);
2350
2351 l_cur_item system.wip_component_obj_t := null;
2352 l_cur_lot system.wip_txn_lot_obj_t := null;
2353 l_more boolean;
2354 l_qty NUMBER;
2355
2356 l_msg_count NUMBER;
2357 l_msg_data VARCHAR2(255);
2358 l_rev_code VARCHAR2(255);
2359 l_is_revision_control boolean;
2360 tree_id NUMBER;
2361 l_qoh NUMBER;
2362 l_rqoh NUMBER;
2363 l_qr NUMBER;
2364 l_qs NUMBER;
2365 l_att NUMBER;
2366 l_atr NUMBER;
2367
2368 l_comp_txn_type_id NUMBER;
2369 l_txn_type_id NUMBER;
2370 l_txn_act_id NUMBER;
2371 BEGIN
2372 savepoint defaultlotsp_10;
2373 x_returnStatus := fnd_api.g_ret_sts_success;
2374
2375 l_compObj := system.wip_lot_serial_obj_t(null, null, null, null, null, null);
2376 l_compObj.initialize;
2377
2378 if ( txn_type_id in (44, 90) ) then
2379 l_comp_txn_type_id := 35;
2380 elsif( txn_type_id in (17, 91) ) then
2381 l_comp_txn_type_id := 43;
2382 else
2383 l_comp_txn_type_id := 35;
2384 end if;
2385
2386 select t.transaction_action_id
2387 into l_txn_act_id
2388 from mtl_transaction_types t
2389 where t.transaction_type_id = l_comp_txn_type_id;
2390
2391 open c_lotItems;
2392
2393 l_org_id := null;
2394
2395 LOOP
2396 Fetch c_lotItems into l_lotItemsRecords;
2397
2398 if ( c_lotItems%NOTFOUND ) THEN
2399 close c_lotItems;
2400 EXIT;
2401 end if;
2402
2403 l_org_id := l_lotItemsRecords.organization_id;
2404 --- l_wipentityid := l_lotItemsRecords.transaction_source_id;
2405 l_wipentityid := wip_entity_id;
2406 if(l_wipentityid is null ) then
2407 l_wipentityid := -1;
2408 end if;
2409
2410 -- Add the items to the wip_lot_serial_obj_t
2411 l_compObj.addItem(p_opSeqNum => l_lotItemsRecords.operation_seq_num,
2412 p_itemID => l_lotItemsRecords.inventory_item_id,
2413 p_itemName => l_lotItemsRecords.concatenated_segments,
2414 p_priQty => l_lotItemsRecords.primary_quantity,
2415 p_priUomCode => l_lotItemsRecords.primary_uom_code,
2416 p_supplySubinv => l_lotItemsRecords.subinventory_code,
2417 p_supplyLocID => l_lotItemsRecords.locator_id,
2418 p_wipSupplyType => 3,
2419 p_txnActionID => l_txn_act_id, --l_lotItemsRecords.transaction_action_id,
2420 p_mtlTxnsEnabledFlag => l_lotItemsRecords.mtl_transactions_enabled_flag,
2421 p_revision => l_lotItemsRecords.revision,
2422 p_lotControlCode => l_lotItemsRecords.lot_control_code,
2423 p_serialControlCode => l_lotItemsRecords.serial_number_control_code,
2424 p_genericid => null );
2425 End LOOP; -- end of for loop
2426
2427 if( l_org_id is null) then
2428 -- nothing to default lot
2429 return;
2430 end if;
2431
2432 -- <Default the Lot>
2433 wip_autolotproc_priv.derivelots(
2434 x_compLots => l_compObj,
2435 p_orgID => l_org_id,
2436 p_wipEntityID => l_wipentityid,
2437 p_initMsgList => fnd_api.g_true,
2438 p_endDebug => fnd_api.g_true,
2439 p_destroyTrees => fnd_api.g_false,
2440 p_treeMode => 3, -- make sure we use the same tree_mode as inv lot entry form
2441 p_treeSrcName => txn_source_name,
2442 x_returnStatus => x_returnStatus );
2443
2444 if ( x_returnStatus = fnd_api.g_ret_sts_unexp_error ) then
2445 raise fnd_api.g_exc_unexpected_error;
2446 end if;
2447
2448 open c_lotItems;
2449
2450 LOOP
2451 l_more := l_compObj.setnextitem();
2452 exit when not l_more;
2453
2454 Fetch c_lotItems into l_lotItemsRecords;
2455
2456 if ( c_lotItems%NOTFOUND ) THEN
2457 close c_lotItems;
2458 EXIT;
2459 end if;
2460
2461 l_more := l_compObj.getcurrentitem(l_cur_item);
2462
2463 select decode(t.revision_qty_control_code, 2, 'T', 'F') into l_rev_code
2464 From mtl_system_items_b t
2465 Where t.inventory_item_id = l_lotItemsRecords.inventory_item_id
2466 and t.organization_id = l_lotItemsRecords.organization_id;
2467
2468 if ( l_rev_code = 'T' ) then
2469 l_is_revision_control := true;
2470 else
2471 l_is_revision_control := false;
2472 end if;
2473
2474 LOOP
2475 l_more := l_compObj.getnextlot(l_cur_lot);
2476 exit when not l_more;
2477
2478 -- added to populate values to the table columns (expiration date, flex fields),
2479 -- which would be populated if you use the lot-entry form the usual way
2480 -- fix bug 2349555
2481 open c_lot(l_lotItemsRecords.organization_id,
2482 l_lotItemsRecords.inventory_item_id, l_cur_lot.lot_number);
2483 fetch c_lot into l_lotNumber;
2484
2485 l_qty := abs(round(l_cur_lot.primary_quantity *
2486 l_lotItemsRecords.transaction_quantity/l_lotItemsRecords.primary_quantity,
2487 wip_constants.max_displayed_precision));
2488 insert into mtl_transaction_lots_temp
2489 (
2490 transaction_temp_id,
2491 last_update_date,
2492 last_updated_by,
2493 creation_date,
2494 created_by,
2495 last_update_login,
2496 request_id,
2497 program_application_id,
2498 program_id,
2499 program_update_date,
2500 transaction_quantity,
2501 primary_quantity,
2502 lot_number,
2503 group_header_id, -- bugfix 4455722
2504
2505 -- added
2506 lot_expiration_date,
2507 description,
2508 vendor_name,
2509 supplier_lot_number,
2510 grade_code,
2511 origination_date,
2512 date_code,
2513 status_id,
2514 change_date,
2515 age,
2516 retest_date,
2517 maturity_date,
2518 lot_attribute_category,
2519 item_size,
2520 color,
2521 volume,
2522 volume_uom,
2523 place_of_origin,
2524 best_by_date,
2525 length,
2526 length_uom,
2527 recycled_content,
2528 thickness,
2529 thickness_uom,
2530 width,
2531 width_uom,
2532 curl_wrinkle_fold,
2533 c_attribute1,
2534 c_attribute2,
2535 c_attribute3,
2536 c_attribute4,
2537 c_attribute5,
2538 c_attribute6,
2539 c_attribute7,
2540 c_attribute8,
2541 c_attribute9,
2542 c_attribute10,
2543 c_attribute11,
2544 c_attribute12,
2545 c_attribute13,
2546 c_attribute14,
2547 c_attribute15,
2548 c_attribute16,
2549 c_attribute17,
2550 c_attribute18,
2551 c_attribute19,
2552 c_attribute20,
2553 d_attribute1,
2554 d_attribute2,
2555 d_attribute3,
2556 d_attribute4,
2557 d_attribute5,
2558 d_attribute6,
2559 d_attribute7,
2560 d_attribute8,
2561 d_attribute9,
2562 d_attribute10,
2563 n_attribute1,
2564 n_attribute2,
2565 n_attribute3,
2566 n_attribute4,
2567 n_attribute5,
2568 n_attribute6,
2569 n_attribute7,
2570 n_attribute8,
2571 n_attribute9,
2572 n_attribute10,
2573 vendor_id,
2574 territory_code
2575 )
2576 values
2577 (
2578 l_lotItemsRecords.transaction_interface_id,
2579 sysdate,
2580 fnd_global.user_id,
2581 sysdate,
2582 fnd_global.user_id,
2583 fnd_global.login_id,
2584 fnd_global.conc_request_id,
2585 fnd_global.prog_appl_id,
2586 fnd_global.conc_program_id,
2587 sysdate,
2588 l_qty,
2589 abs(l_cur_lot.primary_quantity),
2590 l_cur_lot.lot_number,
2591 l_lotItemsRecords.transaction_header_id, --bugfix 4455722
2592
2593 -- added
2594 l_lotNumber.lot_expiration_date,
2595 l_lotNumber.description,
2596 l_lotNumber.vendor_name,
2597 l_lotNumber.supplier_lot_number,
2598 l_lotNumber.grade_code,
2599 l_lotNumber.origination_date,
2600 l_lotNumber.date_code,
2601 l_lotNumber.status_id,
2602 l_lotNumber.change_date,
2603 l_lotNumber.age,
2604 l_lotNumber.retest_date,
2605 l_lotNumber.maturity_date,
2606 l_lotNumber.lot_attribute_category,
2607 l_lotNumber.item_size,
2608 l_lotNumber.color,
2609 l_lotNumber.volume,
2610 l_lotNumber.volume_uom,
2611 l_lotNumber.place_of_origin,
2612 l_lotNumber.best_by_date,
2613 l_lotNumber.length,
2614 l_lotNumber.length_uom,
2615 l_lotNumber.recycled_content,
2616 l_lotNumber.thickness,
2617 l_lotNumber.thickness_uom,
2618 l_lotNumber.width,
2619 l_lotNumber.width_uom,
2620 l_lotNumber.curl_wrinkle_fold,
2621 l_lotNumber.c_attribute1,
2622 l_lotNumber.c_attribute2,
2623 l_lotNumber.c_attribute3,
2624 l_lotNumber.c_attribute4,
2625 l_lotNumber.c_attribute5,
2626 l_lotNumber.c_attribute6,
2627 l_lotNumber.c_attribute7,
2628 l_lotNumber.c_attribute8,
2629 l_lotNumber.c_attribute9,
2630 l_lotNumber.c_attribute10,
2631 l_lotNumber.c_attribute11,
2632 l_lotNumber.c_attribute12,
2633 l_lotNumber.c_attribute13,
2634 l_lotNumber.c_attribute14,
2635 l_lotNumber.c_attribute15,
2636 l_lotNumber.c_attribute16,
2637 l_lotNumber.c_attribute17,
2638 l_lotNumber.c_attribute18,
2639 l_lotNumber.c_attribute19,
2640 l_lotNumber.c_attribute20,
2641 l_lotNumber.d_attribute1,
2642 l_lotNumber.d_attribute2,
2643 l_lotNumber.d_attribute3,
2644 l_lotNumber.d_attribute4,
2645 l_lotNumber.d_attribute5,
2646 l_lotNumber.d_attribute6,
2647 l_lotNumber.d_attribute7,
2648 l_lotNumber.d_attribute8,
2649 l_lotNumber.d_attribute9,
2650 l_lotNumber.d_attribute10,
2651 l_lotNumber.n_attribute1,
2652 l_lotNumber.n_attribute2,
2653 l_lotNumber.n_attribute3,
2654 l_lotNumber.n_attribute4,
2655 l_lotNumber.n_attribute5,
2656 l_lotNumber.n_attribute6,
2657 l_lotNumber.n_attribute7,
2658 l_lotNumber.n_attribute8,
2659 l_lotNumber.n_attribute9,
2660 l_lotNumber.n_attribute10,
2661 l_lotNumber.vendor_id,
2662 l_lotNumber.territory_code
2663 );
2664
2665 close c_lot;
2666
2667 END LOOP;
2668 END LOOP;
2669
2670 EXCEPTION
2671 WHEN fnd_api.g_exc_unexpected_error then
2672 rollback to defaultlotsp1_10;
2673 WHEN OTHERS then
2674 l_errorcode := SQLCODE;
2675 l_errormesg := SUBSTR( SQLERRM, 1, 100 );
2676 rollback to defaultlotsp_10;
2677
2678 END default_lots;
2679
2680 end Wip_Flow_Utilities;
[Home] [Help]