[Home] [Help]
PACKAGE BODY: APPS.WIP_EXPLODE_PHANTOM_RTGS
Source
1 PACKAGE BODY WIP_EXPLODE_PHANTOM_RTGS AS
2 /* $Header: wiphrtgb.pls 120.6 2011/06/27 11:45:54 sisankar ship $ */
3
4 g_line_code VARCHAR2(10) := NULL ; -- fow flow schedule
5
6 /* *********************************************************************
7 Private functions for flow schedule
8 ***********************************************************************/
9 function Charge_flow_Resources (p_txn_temp_id in number,
10 p_comp_txn_id in number,
11 p_org_id in number,
12 p_phantom_item_id in number,
13 p_op_seq_num in number,
14 p_rtg_rev_date in varchar2) return number is
15 l_org_code VARCHAR2(3);
16
17 BEGIN
18 --bug 5231366
19 select mp.organization_code
20 into l_org_code
21 from mtl_material_transactions_temp mmtt,
22 mtl_parameters mp
23 where mmtt.transaction_temp_id = p_txn_temp_id
24 and mmtt.organization_id = mp.organization_id;
25
26 INSERT INTO WIP_COST_TXN_INTERFACE
27 (transaction_id,
28 last_update_date,
29 last_updated_by,
30 last_updated_by_name,
31 creation_date,
32 created_by,
33 created_by_name,
34 last_update_login,
35 request_id,
36 program_application_id,
37 program_id,
38 program_update_date,
39 group_id,
40 source_code,
41 source_line_id,
42 process_phase,
43 process_status,
44 transaction_type,
45 organization_id,
46 organization_code, --bug 5231366
47 wip_entity_id,
48 entity_type,
49 primary_item_id,
50 line_id,
51 line_code,
52 transaction_date,
53 acct_period_id,
54 operation_seq_num,
55 department_id,
56 department_code,
57 employee_id,
58 resource_seq_num,
59 resource_id,
60 resource_code,
61 usage_rate_or_amount,
62 basis_type,
63 autocharge_type,
64 standard_rate_flag,
65 transaction_quantity,
66 transaction_uom,
67 primary_quantity,
68 primary_uom,
69 actual_resource_rate,
70 activity_id,
71 reason_id,
72 reference,
73 completion_transaction_id,
74 po_header_id,
75 po_line_id,
76 repetitive_schedule_id,
77 attribute_category,
78 attribute1, attribute2, attribute3, attribute4, attribute5,
79 attribute6, attribute7, attribute8, attribute9, attribute10,
80 attribute11, attribute12,attribute13, attribute14, attribute15,
81 project_id,
82 task_id,
83 phantom_flag
84 )
85 SELECT
86 NULL,
87 SYSDATE,
88 MMTT.LAST_UPDATED_BY,
89 NULL,
90 SYSDATE,
91 MMTT.CREATED_BY,
92 NULL,
93 MMTT.LAST_UPDATE_LOGIN,
94 MMTT.REQUEST_ID,
95 MMTT.PROGRAM_APPLICATION_ID,
96 MMTT.PROGRAM_ID,
97 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
98 NULL,
99 MMTT.SOURCE_CODE,
100 MMTT.SOURCE_LINE_ID,
101 2, -- Process_Phase
102 1, -- Process Status
103 1, -- transaction_type: resource
104 MMTT.ORGANIZATION_ID,
105 l_org_code, --bug 5231366
106 MMTT.TRANSACTION_SOURCE_ID, -- wip_entity_id
107 4, -- Wip_Entity_Type
108 wfs.primary_item_id,
109 MMTT.REPETITIVE_LINE_ID,
110 g_line_code, -- the global line code variable
111 MMTT.TRANSACTION_DATE,
112 MMTT.ACCT_PERIOD_ID,
113 p_op_seq_num,
114 BOS.DEPARTMENT_ID,
115 BD.DEPARTMENT_CODE,
116 NULL, -- employee_id
117 BOR.RESOURCE_SEQ_NUM,
118 BOR.RESOURCE_ID,
119 BR.RESOURCE_CODE,
120 BOR.USAGE_RATE_OR_AMOUNT,
121 BOR.BASIS_TYPE,
122 BOR.AUTOCHARGE_TYPE,
123 BOR.STANDARD_RATE_FLAG,
124 BOR.USAGE_RATE_OR_AMOUNT * DECODE (BOR.BASIS_TYPE,
125 1, -1*MMTT.PRIMARY_QUANTITY,
126 2, DECODE(wfs.QUANTITY_COMPLETED,
127 0, 1,
128 0 ),
129 0 ), -- transaction_quantity
130 BR.UNIT_OF_MEASURE,
131 BOR.USAGE_RATE_OR_AMOUNT * DECODE (BOR.BASIS_TYPE,
132 1, -1*MMTT.PRIMARY_QUANTITY,
133 2, DECODE(wfs.QUANTITY_COMPLETED,
134 0, 1,
135 0 ),
136 0 ), -- primary_quantity
137 BR.UNIT_OF_MEASURE,
138 NULL, -- actual resource rate
139 NVL(BOR.ACTIVITY_ID,-1),
140 MMTT.REASON_ID,
141 MMTT.TRANSACTION_REFERENCE,
142 MMTT.COMPLETION_TRANSACTION_ID,
143 NULL,
144 NULL,
145 NULL,
146 NULL,
147 NULL, NULL, NULL, NULL, NULL,
148 NULL, NULL, NULL, NULL, NULL,
149 NULL, NULL, NULL, NULL, NULL,
150 wfs.PROJECT_ID,
151 wfs.TASK_ID,
152 1 -- phantom flag
153 FROM
154 BOM_OPERATION_RESOURCES BOR,
155 WIP_FLOW_SCHEDULES wfs,
156 BOM_DEPARTMENTS BD,
157 BOM_RESOURCES BR,
158 CST_ACTIVITIES CA,
159 BOM_OPERATION_SEQUENCES BOS,
160 BOM_OPERATIONAL_ROUTINGS ROUT,
161 mtl_material_transactions_temp MMTT
162 WHERE
163 MMTT.transaction_temp_id = p_txn_temp_id
164 AND MMTT.inventory_item_id = p_phantom_item_id
165 AND MMTT.organization_id = p_org_id
166 AND ROUT.assembly_item_id = p_phantom_item_id
167 AND ROUT.organization_id = p_org_id
168 AND ROUT.alternate_routing_designator is NULL
169 AND ROUT.common_routing_sequence_id = bos.routing_sequence_id
170 AND BOS.effectivity_date <= to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
171 AND NVL(BOS.disable_date, to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT))
172 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
173 AND bos.operation_sequence_id = bor.operation_sequence_id
174 AND ROUT.organization_id = bd.organization_id
175 AND bos.department_id = bd.department_id
176 AND ROUT.organization_id = br.organization_id
177 AND bor.resource_id = br.resource_id
178 AND wfs.wip_entity_id = MMTT.transaction_source_id
179 AND wfs.organization_id = MMTT.organization_id
180 AND bor.autocharge_type <> 2 -- not manual
181 AND br.cost_element_id in (3, 4) -- resource/osp
182 AND bor.usage_rate_or_amount <> 0
183 AND bos.count_point_type in (1, 2)
184 AND DECODE (BOR.BASIS_TYPE,
185 1, MMTT.TRANSACTION_QUANTITY,
186 2, DECODE(wfs.QUANTITY_COMPLETED, 0, 1, 0 ), 0 ) <> 0
187 AND bor.activity_id = ca.activity_id (+)
188 AND Nvl(bos.operation_type,1) = 1;
189
190 -- Taking care of the Activity update in two stages
191 -- as we have an index on completion_txn_id
192 UPDATE WIP_COST_TXN_INTERFACE
193 SET ACTIVITY_ID = DECODE(ACTIVITY_ID,
194 -1, NULL,
195 ACTIVITY_ID)
196 WHERE COMPLETION_TRANSACTION_ID = p_comp_txn_id;
197
198 return 1;
199
200 exception
201 when No_Data_Found then
202 return 1;
203 when others then
204 return 0;
205
206 End Charge_flow_Resources ;
207
208
209 function Charge_Item_Overheads(p_txn_temp_id in number,
210 p_org_id in number,
211 p_phantom_item_id in number,
212 p_op_seq_num in number,
213 p_rtg_rev_date in varchar2 ) return number is
214 l_org_code VARCHAR2(3);
215 Begin
216 --bug 5231366
217 select mp.organization_code
218 into l_org_code
219 from mtl_material_transactions_temp mmtt,
220 mtl_parameters mp
221 where mmtt.transaction_temp_id = p_txn_temp_id
222 and mmtt.organization_id = mp.organization_id;
223
224 INSERT INTO WIP_COST_TXN_INTERFACE
225 ( transaction_id,
226 last_update_date,
227 last_updated_by,
228 last_updated_by_name,
229 creation_date,
230 created_by,
231 created_by_name,
232 last_update_login,
233 request_id,
234 program_application_id,
235 program_id,
236 program_update_date,
237 group_id,
238 source_code,
239 source_line_id,
240 process_phase,
241 process_status,
242 transaction_type,
243 organization_id,
244 organization_code, --bug 5231366
245 wip_entity_id,
246 entity_type,
247 primary_item_id,
248 line_id,
249 line_code,
250 transaction_date,
251 acct_period_id,
252 operation_seq_num,
253 department_id,
254 department_code,
255 employee_id,
256 resource_seq_num,
257 resource_id,
258 resource_code,
259 usage_rate_or_amount,
260 basis_type,
261 autocharge_type,
262 standard_rate_flag,
263 transaction_quantity,
264 transaction_uom,
265 primary_quantity,
266 primary_uom,
267 actual_resource_rate,
268 activity_id,
269 reason_id,
270 reference,
271 completion_transaction_id,
272 po_header_id,
273 po_line_id,
274 repetitive_schedule_id,
275 attribute_category,
276 attribute1, attribute2, attribute3, attribute4, attribute5,
277 attribute6, attribute7, attribute8, attribute9, attribute10,
278 attribute11, attribute12, attribute13, attribute14, attribute15,
279 project_id,
280 task_id,
281 phantom_flag)
282 SELECT
283 NULL,
284 SYSDATE,
285 MMTT.LAST_UPDATED_BY,
286 NULL,
287 SYSDATE,
288 MMTT.CREATED_BY,
289 NULL,
290 MMTT.LAST_UPDATE_LOGIN,
291 MMTT.REQUEST_ID,
292 MMTT.PROGRAM_APPLICATION_ID,
293 MMTT.PROGRAM_ID,
294 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
295 NULL,
296 MMTT.SOURCE_CODE,
297 MMTT.SOURCE_LINE_ID,
298 2,
299 1,
300 2,
301 MMTT.ORGANIZATION_ID,
302 l_org_code, --bug 5231366
303 MMTT.TRANSACTION_SOURCE_ID,
304 4,
305 wfs.primary_item_id,
306 MMTT.REPETITIVE_LINE_ID,
307 g_line_code, -- the global line code variable
308 MMTT.TRANSACTION_DATE,
309 MMTT.ACCT_PERIOD_ID,
310 p_op_seq_num,
311 BOS.DEPARTMENT_ID,
312 BD.DEPARTMENT_CODE,
313 NULL,
314 NULL,
315 NULL,
316 NULL,
317 NULL,
318 1, -- Per Item
319 1, -- WWIP_MOVE
320 NULL,
321 -1*NVL(MMTT.transaction_quantity, 0),
322 MMTT.TRANSACTION_UOM,
323 -1*NVL(MMTT.primary_quantity, 0),
324 MMTT.ITEM_PRIMARY_UOM_CODE,
325 NULL,
326 NULL,
327 MMTT.REASON_ID,
328 MMTT.TRANSACTION_REFERENCE,
329 MMTT.COMPLETION_TRANSACTION_ID,
330 NULL,
331 NULL,
332 NULL,
333 NULL,
334 NULL, NULL, NULL, NULL, NULL,
335 NULL, NULL, NULL, NULL, NULL,
336 NULL, NULL, NULL, NULL, NULL,
337 wfs.PROJECT_ID,
338 wfs.TASK_ID,
339 1
340 FROM
341 BOM_DEPARTMENTS bd,
342 BOM_OPERATION_SEQUENCES bos,
343 WIP_FLOW_SCHEDULES wfs,
344 BOM_OPERATIONAL_ROUTINGS BOR,
345 mtl_material_transactions_temp mmtt
346 WHERE
347 MMTT.transaction_temp_id = p_txn_temp_id
348 AND MMTT.transaction_source_id = wfs.wip_entity_id
349 AND MMTT.organization_id = wfs.organization_Id
350 AND MMTT.inventory_item_id = p_phantom_item_id
351 AND MMTT.organization_id = p_org_id
352 AND BOR.assembly_item_id = p_phantom_item_id
353 AND BOR.organization_id = p_org_id
354 AND BOR.alternate_routing_designator is NULL
355 AND BOR.common_routing_sequence_id = bos.routing_sequence_id
356 AND BOS.effectivity_date <= to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
357 AND NVL(BOS.disable_date, to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT))
358 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
359 AND bor.organization_id = bd.organization_id
360 AND bos.department_id = bd.department_id
361 AND bos.count_point_type in (1, 2) -- ovhd for autocharge operations
362 AND Nvl(bos.operation_type,1) = 1;
363
364 return 1;
365
366 exception
367 when No_Data_Found then
368 return 1;
369 when others then
370 return 0;
371
372 end Charge_Item_Overheads;
373
374 function Charge_Lot_Overheads(p_txn_temp_id in number,
375 p_org_id in number,
376 p_phantom_item_id in number,
377 p_op_seq_num in number,
378 p_rtg_rev_date in varchar2 ) return number is
379 l_org_code VARCHAR2(3);
380 Begin
381 --bug 5231366
382 select mp.organization_code
383 into l_org_code
384 from mtl_material_transactions_temp mmtt,
385 mtl_parameters mp
386 where mmtt.transaction_temp_id = p_txn_temp_id
387 and mmtt.organization_id = mp.organization_id;
388
389 INSERT INTO WIP_COST_TXN_INTERFACE
390 ( transaction_id,
391 last_update_date,
392 last_updated_by,
393 last_updated_by_name,
394 creation_date,
395 created_by,
396 created_by_name,
397 last_update_login,
398 request_id,
399 program_application_id,
400 program_id,
401 program_update_date,
402 group_id,
403 source_code,
404 source_line_id,
405 process_phase,
406 process_status,
407 transaction_type,
408 organization_id,
409 organization_code, --bug 5231366
410 wip_entity_id,
411 entity_type,
412 primary_item_id,
413 line_id,
414 line_code,
415 transaction_date,
416 acct_period_id,
417 operation_seq_num,
418 department_id,
419 department_code,
420 employee_id,
421 resource_seq_num,
422 resource_id,
423 resource_code,
424 usage_rate_or_amount,
425 basis_type,
426 autocharge_type,
427 standard_rate_flag,
428 transaction_quantity,
429 transaction_uom,
430 primary_quantity,
431 primary_uom,
432 actual_resource_rate,
433 activity_id,
434 reason_id,
435 reference,
436 completion_transaction_id,
437 po_header_id,
438 po_line_id,
439 repetitive_schedule_id,
440 attribute_category,
441 attribute1, attribute2, attribute3, attribute4, attribute5,
442 attribute6, attribute7, attribute8, attribute9, attribute10,
443 attribute11, attribute12, attribute13, attribute14, attribute15,
444 project_id,
445 task_id,
446 phantom_flag)
447 SELECT
448 NULL,
449 SYSDATE,
450 MMTT.LAST_UPDATED_BY,
451 NULL,
452 SYSDATE,
453 MMTT.CREATED_BY,
454 NULL,
455 MMTT.LAST_UPDATE_LOGIN,
456 MMTT.REQUEST_ID,
457 MMTT.PROGRAM_APPLICATION_ID,
458 MMTT.PROGRAM_ID,
459 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
460 NULL,
461 MMTT.SOURCE_CODE,
462 MMTT.SOURCE_LINE_ID,
463 2,
464 1,
465 2,
466 MMTT.ORGANIZATION_ID,
467 l_org_code, --bug 5231366
468 MMTT.TRANSACTION_SOURCE_ID,
469 4,
470 p_phantom_item_id,
471 MMTT.REPETITIVE_LINE_ID,
472 g_line_code, -- the global line code variable
473 MMTT.TRANSACTION_DATE,
474 MMTT.ACCT_PERIOD_ID,
475 p_op_seq_num,
476 BOS.DEPARTMENT_ID,
477 BD.DEPARTMENT_CODE,
478 NULL,
479 NULL,
480 NULL,
481 NULL,
482 NULL,
483 2, -- Per Lot
484 1, -- WWIP_MOVE
485 NULL,
486 DECODE( NVL(wfs.Quantity_Completed, 0),
487 0, 1,
488 0 ),
489 MMTT.TRANSACTION_UOM,
490 DECODE( NVL(wfs.Quantity_Completed, 0),
491 0, 1,
492 0 ),
493 MMTT.ITEM_PRIMARY_UOM_CODE,
494 NULL,
495 NULL,
496 MMTT.REASON_ID,
497 MMTT.TRANSACTION_REFERENCE,
498 MMTT.COMPLETION_TRANSACTION_ID,
499 NULL,
500 NULL,
501 NULL,
502 NULL,
503 NULL, NULL, NULL, NULL, NULL,
504 NULL, NULL, NULL, NULL, NULL,
505 NULL, NULL, NULL, NULL, NULL,
506 wfs.PROJECT_ID,
507 wfs.TASK_ID,
508 1
509 FROM
510 BOM_DEPARTMENTS bd,
511 BOM_OPERATION_SEQUENCES bos,
512 WIP_flow_schedules wfs,
513 BOM_OPERATIONAL_ROUTINGS BOR,
514 mtl_material_transactions_temp mmtt
515 WHERE
516 MMTT.transaction_temp_id = p_txn_temp_id
517 AND MMTT.transaction_source_id = wfs.wip_entity_id
518 AND MMTT.organization_id = wfs.organization_Id
519 AND MMTT.inventory_item_id = p_phantom_item_id
520 AND BOR.assembly_item_id = p_phantom_item_id
521 AND BOR.organization_id = p_org_id
522 AND BOR.alternate_routing_designator is NULL
523 AND BOR.common_routing_sequence_id = bos.routing_sequence_id
524 AND decode( NVL(wfs.Quantity_Completed, 0),
525 0, 1,
526 0 ) <> 0
527 AND BOS.effectivity_date <=
528 to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
529 AND NVL(BOS.disable_date,
530 to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT))
531 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DATETIME_FMT)
532 AND bor.organization_id = bd.organization_id
533 AND bos.department_id = bd.department_id
534 AND bos.count_point_type in (1, 2) -- ovhd for autocharge operations
535 AND Nvl(bos.operation_type, 1) = 1;
536
537 return 1;
538
539 exception
540 when No_Data_Found then
541 return 1;
542
543 when others then
544 return 0;
545
546 end Charge_Lot_Overheads;
547
548 procedure explode_resources(
549 p_wip_entity_id in number,
550 p_sched_id in number,
551 p_org_id in number,
552 p_entity_type in number,
553 p_phantom_item_id in number,
554 p_op_seq_num in number,
555 p_rtg_rev_date in date) IS
556
557 /* local variables */
558 x_last_update_date date;
559 x_last_updated_by number;
560 x_creation_date date;
561 x_created_by number;
562 x_last_update_login number;
563 x_request_id number;
564 x_program_application_id number;
565 x_program_id number;
566 x_program_update_date date;
567
568 x_max_resc_seq_num number := 0;
569 x_uom_code varchar2(3);
570 x_applied_resource_units number := 0;
571 x_applied_resource_value number := 0;
572 x_start_date date;
573 x_completion_date date;
574
575 x_comp_qty number ;
576 x_yield_factor number ;
577
578 /*Fixed Bug# 1818055 */
579
580 /* Fixed Bug 5366856. Added COMPONENT_YIELD_FACTOR in Cursor SQL to consider yield
581 while exploding resources for phantom assemblies. */
582
583
584
585 CURSOR phan_comp_qty
586 IS
587 SELECT WRO.QUANTITY_PER_ASSEMBLY,WRO.COMPONENT_YIELD_FACTOR
588 FROM WIP_REQUIREMENT_OPERATIONS WRO
589 WHERE WRO.WIP_ENTITY_ID = p_wip_entity_id
590 AND WRO.INVENTORY_ITEM_ID = p_phantom_item_id
591 AND WRO.OPERATION_SEQ_NUM = -p_op_seq_num
592 AND WRO.ORGANIZATION_ID = p_org_id
593 AND ((WRO.REPETITIVE_SCHEDULE_ID = p_sched_id ) or (WRO.REPETITIVE_SCHEDULE_ID is null));
594
595
596
597 CURSOR phan_resc_cursor(p_rtg_revision_date date) IS
598 SELECT BOR.resource_id ,
599 BOR.activity_id ,
600 BOR.standard_rate_flag ,
601 BOR.assigned_units ,
602 BOR.usage_rate_or_amount ,
603 BOR.basis_type ,
604 BOR.autocharge_type ,
605 BOS.operation_seq_num phantom_op_seq_num,
606 BOS.department_id,
607 BOR.schedule_flag
608 FROM
609 MTL_UOM_CONVERSIONS CON,
610 BOM_RESOURCES BR,
611 BOM_OPERATION_RESOURCES BOR,
612 BOM_DEPARTMENT_RESOURCES BDR1,
613 BOM_DEPARTMENT_RESOURCES BDR2,
614 BOM_OPERATION_SEQUENCES BOS,
615 BOM_OPERATIONAL_ROUTINGS BRTG,
616 MTL_SYSTEM_ITEMS msi
617 WHERE
618 BRTG.organization_id = p_org_id
619 and BRTG.assembly_item_id = p_phantom_item_id
620 and BRTG.organization_id = msi.organization_id
621 and BRTG.assembly_item_id = msi.inventory_item_id
622 and msi.bom_item_type not in ( 1, 2) /* Exclude AIO Model and option class */
623 and NVL(BRTG.cfm_routing_flag, 2) = 2 /* not a flow routing */
624 and BRTG.alternate_routing_designator IS NULL /* primary routing */
625 and BRTG.common_routing_sequence_id = BOS.routing_sequence_id
626 and BOS.effectivity_date <= p_rtg_revision_date
627 and NVL(operation_type, 1) = 1
628 and NVL(BOS.disable_date, p_rtg_revision_date+ 2) >= p_rtg_revision_date
629 and BOS.department_id = BDR1.department_id
630 AND NVL(BDR1.share_from_dept_id, BDR1.department_id) = BDR2.department_id
631 and BOR.resource_id = BDR1.resource_id
632 AND BOR.resource_id = BDR2.resource_id
633 and BOR.operation_sequence_id = BOS.operation_sequence_id
634 AND BOR.resource_id = BR.resource_id
635 AND CON.UOM_CODE (+) = BR.UNIT_OF_MEASURE
636 AND CON.INVENTORY_ITEM_ID (+) = 0
637 AND NVL(BOR.acd_type,0) <> 3 --bug 7315072 (FP 7272795): inserting resources that are not disabled
638 ORDER BY BOS.operation_seq_num,
639 BOR.resource_seq_num ;
640
641
642 CURSOR c_explodephantom IS
643 SELECT bos.operation_sequence_id
644 FROM BOM_OPERATIONAL_ROUTINGS BOR,BOM_OPERATION_SEQUENCES BOS
645 WHERE bor.organization_id=p_org_id
646 AND bor.assembly_item_id= p_phantom_item_id
647 AND bos.routing_sequence_id=bor.routing_sequence_id
648 AND bor.alternate_routing_designator IS NULL
649 AND EXISTS (SELECT 1 FROM
650 FND_ATTACHED_DOCUMENTS fad
651 WHERE fad.pk1_value = TO_CHAR(bos.operation_sequence_id)
652 AND fad.entity_name = 'BOM_OPERATION_SEQUENCES' );
653 BEGIN
654
655
656 /* -------------------------------------------------------------*
657 * get current max resource_seq_num and who columns information *
658 * from resources in main routing, for the operation *
659 * The two select clauses can not be combined into one because *
660 * of the MAX function
661 * -------------------------------------------------------------*/
662 SELECT max(resource_seq_num)
663 INTO x_max_resc_seq_num
664 FROM WIP_OPERATION_RESOURCES
665 WHERE wip_entity_id = p_wip_entity_id
666 and organization_id = p_org_id
667 and NVL(repetitive_schedule_id, -1) =
668 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
669 and operation_seq_num = p_op_seq_num;
670
671 if x_max_resc_seq_num is null then
672 x_max_resc_seq_num := 0;
673 end if;
674
675 begin
676 SELECT last_update_date, last_updated_by, creation_date,
677 created_by, last_update_login, request_id,
678 program_application_id, program_id, program_update_date
679 INTO x_last_update_date, x_last_updated_by, x_creation_date,
680 x_created_by, x_last_update_login, x_request_id,
681 x_program_application_id, x_program_id, x_program_update_date
682 FROM WIP_OPERATION_RESOURCES
683 WHERE wip_entity_id = p_wip_entity_id
684 and organization_id = p_org_id
685 and NVL(repetitive_schedule_id, -1) =
686 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
687 and resource_seq_num = x_max_resc_seq_num
688 and operation_seq_num = p_op_seq_num;
689
690 exception
691 when no_data_found then
692 x_last_update_date := SYSDATE;
693 x_last_updated_by := FND_GLOBAL.USER_ID ;
694 x_creation_date := SYSDATE;
695 x_created_by := FND_GLOBAL.USER_ID;
696 x_last_update_login := FND_GLOBAL.LOGIN_ID;
697 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
698 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
699 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
700 x_program_update_date := SYSDATE;
701
702 end;
703 /* --------------------------------------------------------- *
704 * get date information from operation *
705 * ----------------------------------------------------------*/
706
707 SELECT first_unit_start_date, last_unit_completion_date
708 INTO x_start_date, x_completion_date
709 FROM WIP_OPERATIONS
710 WHERE wip_entity_id = p_wip_entity_id
711 AND organization_id = p_org_id
712 and NVL(repetitive_schedule_id, -1) =
713 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
714 AND operation_seq_num = p_op_seq_num;
715
716 /* --------------------------------------------------------- *
717 * GO through the cursor. Populate phantom resources *
718 * information to WIP_OPERATION_RESOURCES *
719 * ----------------------------------------------------------*/
720
721 FOR cur_resc IN phan_resc_cursor(p_rtg_rev_date) LOOP
722
723 /* set resource_seq_num to be unique */
724 x_max_resc_seq_num := x_max_resc_seq_num + 10;
725
726 /* get UOM_code */
727 select unit_of_measure
728 into x_uom_code
729 from BOM_RESOURCES
730 where resource_id = cur_resc.resource_id;
731
732 /* Bug 1691488 */
733
734 /* Fixed Bug 5366856. Fetching COMPONENT_YIELD_FACTOR from Cursor to consider yield
735 while exploding resources for phantom assemblies. */
736
737 OPEN phan_comp_qty ;
738 FETCH phan_comp_qty into x_comp_qty,x_yield_factor ;
739 CLOSE phan_comp_qty ;
740
741 /* insert phantom resources */
742 INSERT INTO WIP_OPERATION_RESOURCES(
743 wip_entity_id,
744 operation_seq_num,
745 resource_seq_num,
746 organization_id,
747 repetitive_schedule_id,
748 last_update_date,
749 last_updated_by,
750 creation_date,
751 created_by,
752 last_update_login,
753 request_id,
754 program_application_id,
755 program_id,
756 program_update_date,
757 resource_id,
758 uom_code,
759 basis_type,
760 usage_rate_or_amount,
761 activity_id,
762 scheduled_flag,
763 assigned_units,
764 autocharge_type,
765 standard_rate_flag,
766 applied_resource_units,
767 applied_resource_value,
768 start_date,
769 completion_date,
770 department_id,
771 phantom_flag,
772 phantom_op_seq_num,
773 phantom_item_id)
774 VALUES(
775 p_wip_entity_id,
776 p_op_seq_num,
777 x_max_resc_seq_num,
778 p_org_id,
779 DECODE(p_sched_id, 0, null, p_sched_id),
780 x_last_update_date,
781 x_last_updated_by,
782 x_creation_date,
783 x_created_by,
784 x_last_update_login,
785 x_request_id,
786 x_program_application_id,
787 x_program_id,
788 x_program_update_date,
789 cur_resc.resource_id,
790 x_uom_code,
791 cur_resc.basis_type,
792 /*Fixed Bug 5366856. Modified to consider yield factor for resources.
793 Lot based resources should be independent of Yield and QPA.
794 Item based Phantom resources should consider yield and QPA. */
795 decode(cur_resc.basis_type, wip_constants.PER_LOT , cur_resc.usage_rate_or_amount,
796 round((cur_resc.usage_rate_or_amount * nvl(x_comp_qty, 1)/nvl(x_yield_factor,1)),
797 wip_constants.max_displayed_precision)),/* Bug# 2115415 */
798 cur_resc.activity_id,
799 cur_resc.schedule_flag, /* Bug 11893957. Default Schedule Flag From BOM. */
800 cur_resc.assigned_units,
801 cur_resc.autocharge_type,
802 cur_resc.standard_rate_flag,
803 x_applied_resource_units,
804 x_applied_resource_value,
805 x_start_date,
806 x_completion_date,
807 cur_resc.department_id,
808 1, /* phantom_flag = YES */
809 cur_resc.phantom_op_seq_num,
810 p_phantom_item_id);
811
812 END LOOP;
813 FOR op_attach IN c_explodephantom LOOP
814 fnd_attached_documents2_pkg.copy_attachments(
815 x_from_entity_name => 'BOM_OPERATION_SEQUENCES',
816 x_from_pk1_value => to_char(op_attach.operation_sequence_id),
817 x_to_entity_name => 'WIP_DISCRETE_OPERATIONS',
818 x_to_pk1_value => to_char(p_wip_entity_id),
819 x_to_pk2_value => to_char(p_op_seq_num),
820 x_to_pk3_value => to_char(p_org_id),
821 x_created_by => fnd_global.user_id,
822 x_last_update_login => fnd_global.login_id,
823 x_program_application_id => fnd_global.prog_appl_id,
824 x_program_id => fnd_global.conc_program_id,
825 x_request_id => fnd_global.conc_request_id);
826 END LOOP;
827
828 exception
829 when No_Data_Found then
830 null;
831
832 when others then
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834
835 END explode_resources;
836
837 /* public function */
838 function charge_flow_resource_ovhd(
839 p_org_id in number,
840 p_phantom_item_id in number,
841 p_op_seq_num in number,
842 p_comp_txn_id in number,
843 p_txn_temp_id in number,
844 p_line_id in number,
845 p_rtg_rev_date in varchar2) return number IS
846
847 x_success number := 0;
848
849 BEGIN
850
851 begin
852 select line_code into g_line_code
853 from wip_lines
854 where line_id = p_line_id ;
855
856 exception
857 when no_data_found then
858 g_line_code := null ;
859 end ;
860
861 x_success := Charge_flow_Resources(p_txn_temp_id,
862 p_comp_txn_id,
863 p_org_id,
864 p_phantom_item_id,
865 p_op_seq_num,
866 p_rtg_rev_date);
867
868 if (x_success<>0) then
869 x_success := Charge_Item_Overheads(p_txn_temp_id,
870 p_org_id,
871 p_phantom_item_id,
872 p_op_seq_num,
873 p_rtg_rev_date );
874 if (x_success<>0) then
875 x_success := Charge_Lot_Overheads(p_txn_temp_id,
876 p_org_id,
877 p_phantom_item_id,
878 p_op_seq_num,
879 p_rtg_rev_date );
880 else
881 return x_success ;
882 end if;
883 else
884 return x_success ;
885 end if;
886
887 return 1;
888
889 exception
890 when No_Data_Found then
891 return 1;
892 when others then
893 return 0;
894
895 END charge_flow_resource_ovhd;
896
897 END WIP_EXPLODE_PHANTOM_RTGS;