[Home] [Help]
PACKAGE BODY: APPS.WIP_EXPLODE_PHANTOM_RTGS
Source
1 PACKAGE BODY WIP_EXPLODE_PHANTOM_RTGS AS
2 /* $Header: wiphrtgb.pls 120.3 2006/09/14 04:21:43 sisankar noship $ */
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,
444 project_id,
441 attribute1, attribute2, attribute3, attribute4, attribute5,
442 attribute6, attribute7, attribute8, attribute9, attribute10,
443 attribute11, attribute12, attribute13, attribute14, attribute15,
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 CURSOR phan_comp_qty
584 IS
585 SELECT WRO.QUANTITY_PER_ASSEMBLY,WRO.COMPONENT_YIELD_FACTOR
586 FROM WIP_REQUIREMENT_OPERATIONS WRO
587 WHERE WRO.WIP_ENTITY_ID = p_wip_entity_id
591 AND ((WRO.REPETITIVE_SCHEDULE_ID = p_sched_id ) or (WRO.REPETITIVE_SCHEDULE_ID is null));
588 AND WRO.INVENTORY_ITEM_ID = p_phantom_item_id
589 AND WRO.OPERATION_SEQ_NUM = -p_op_seq_num
590 AND WRO.ORGANIZATION_ID = p_org_id
592
593
594
595 CURSOR phan_resc_cursor(p_rtg_revision_date date) IS
596 SELECT BOR.resource_id ,
597 BOR.activity_id ,
598 BOR.standard_rate_flag ,
599 BOR.assigned_units ,
600 BOR.usage_rate_or_amount ,
601 BOR.basis_type ,
602 BOR.autocharge_type ,
603 BOS.operation_seq_num phantom_op_seq_num,
604 BOS.department_id
605 FROM
606 MTL_UOM_CONVERSIONS CON,
607 BOM_RESOURCES BR,
608 BOM_OPERATION_RESOURCES BOR,
609 BOM_DEPARTMENT_RESOURCES BDR1,
610 BOM_DEPARTMENT_RESOURCES BDR2,
611 BOM_OPERATION_SEQUENCES BOS,
612 BOM_OPERATIONAL_ROUTINGS BRTG,
613 MTL_SYSTEM_ITEMS msi
614 WHERE
615 BRTG.organization_id = p_org_id
616 and BRTG.assembly_item_id = p_phantom_item_id
617 and BRTG.organization_id = msi.organization_id
618 and BRTG.assembly_item_id = msi.inventory_item_id
619 and msi.bom_item_type not in ( 1, 2) /* Exclude AIO Model and option class */
620 and NVL(BRTG.cfm_routing_flag, 2) = 2 /* not a flow routing */
621 and BRTG.alternate_routing_designator IS NULL /* primary routing */
622 and BRTG.common_routing_sequence_id = BOS.routing_sequence_id
623 and BOS.effectivity_date <= p_rtg_revision_date
624 and NVL(operation_type, 1) = 1
625 and NVL(BOS.disable_date, p_rtg_revision_date+ 2) >= p_rtg_revision_date
626 and BOS.department_id = BDR1.department_id
627 AND NVL(BDR1.share_from_dept_id, BDR1.department_id) = BDR2.department_id
628 and BOR.resource_id = BDR1.resource_id
629 AND BOR.resource_id = BDR2.resource_id
630 and BOR.operation_sequence_id = BOS.operation_sequence_id
631 AND BOR.resource_id = BR.resource_id
632 AND CON.UOM_CODE (+) = BR.UNIT_OF_MEASURE
633 AND CON.INVENTORY_ITEM_ID (+) = 0
634 ORDER BY BOS.operation_seq_num,
635 BOR.resource_seq_num ;
636
637
638 BEGIN
639
640
641 /* -------------------------------------------------------------*
642 * get current max resource_seq_num and who columns information *
643 * from resources in main routing, for the operation *
644 * The two select clauses can not be combined into one because *
645 * of the MAX function
646 * -------------------------------------------------------------*/
647 SELECT max(resource_seq_num)
648 INTO x_max_resc_seq_num
649 FROM WIP_OPERATION_RESOURCES
650 WHERE wip_entity_id = p_wip_entity_id
651 and organization_id = p_org_id
652 and NVL(repetitive_schedule_id, -1) =
653 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
654 and operation_seq_num = p_op_seq_num;
655
656 if x_max_resc_seq_num is null then
657 x_max_resc_seq_num := 0;
658 end if;
659
660 begin
661 SELECT last_update_date, last_updated_by, creation_date,
662 created_by, last_update_login, request_id,
663 program_application_id, program_id, program_update_date
664 INTO x_last_update_date, x_last_updated_by, x_creation_date,
665 x_created_by, x_last_update_login, x_request_id,
666 x_program_application_id, x_program_id, x_program_update_date
667 FROM WIP_OPERATION_RESOURCES
668 WHERE wip_entity_id = p_wip_entity_id
669 and organization_id = p_org_id
670 and NVL(repetitive_schedule_id, -1) =
671 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
672 and resource_seq_num = x_max_resc_seq_num
673 and operation_seq_num = p_op_seq_num;
674
675 exception
676 when no_data_found then
677 x_last_update_date := SYSDATE;
678 x_last_updated_by := FND_GLOBAL.USER_ID ;
679 x_creation_date := SYSDATE;
680 x_created_by := FND_GLOBAL.USER_ID;
681 x_last_update_login := FND_GLOBAL.LOGIN_ID;
682 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
683 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
684 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
685 x_program_update_date := SYSDATE;
686
687 end;
688 /* --------------------------------------------------------- *
689 * get date information from operation *
690 * ----------------------------------------------------------*/
691
692 SELECT first_unit_start_date, last_unit_completion_date
693 INTO x_start_date, x_completion_date
694 FROM WIP_OPERATIONS
695 WHERE wip_entity_id = p_wip_entity_id
696 AND organization_id = p_org_id
697 and NVL(repetitive_schedule_id, -1) =
698 DECODE(p_entity_type, WIP_CONSTANTS.REPETITIVE, p_sched_id,-1)
699 AND operation_seq_num = p_op_seq_num;
700
701 /* --------------------------------------------------------- *
702 * GO through the cursor. Populate phantom resources *
706 FOR cur_resc IN phan_resc_cursor(p_rtg_rev_date) LOOP
703 * information to WIP_OPERATION_RESOURCES *
704 * ----------------------------------------------------------*/
705
707
708 /* set resource_seq_num to be unique */
709 x_max_resc_seq_num := x_max_resc_seq_num + 10;
710
711 /* get UOM_code */
712 select unit_of_measure
713 into x_uom_code
714 from BOM_RESOURCES
715 where resource_id = cur_resc.resource_id;
716
717 /* Bug 1691488 */
718
719 /* Fixed Bug 5366856. Fetching COMPONENT_YIELD_FACTOR from Cursor to consider yield
720 while exploding resources for phantom assemblies. */
721
722 OPEN phan_comp_qty ;
723 FETCH phan_comp_qty into x_comp_qty,x_yield_factor ;
724 CLOSE phan_comp_qty ;
725
726 /* insert phantom resources */
727 INSERT INTO WIP_OPERATION_RESOURCES(
728 wip_entity_id,
729 operation_seq_num,
730 resource_seq_num,
731 organization_id,
732 repetitive_schedule_id,
733 last_update_date,
734 last_updated_by,
735 creation_date,
736 created_by,
737 last_update_login,
738 request_id,
739 program_application_id,
740 program_id,
741 program_update_date,
742 resource_id,
743 uom_code,
744 basis_type,
745 usage_rate_or_amount,
746 activity_id,
747 scheduled_flag,
748 assigned_units,
749 autocharge_type,
750 standard_rate_flag,
751 applied_resource_units,
752 applied_resource_value,
753 start_date,
754 completion_date,
755 department_id,
756 phantom_flag,
757 phantom_op_seq_num,
758 phantom_item_id)
759 VALUES(
760 p_wip_entity_id,
761 p_op_seq_num,
762 x_max_resc_seq_num,
763 p_org_id,
764 DECODE(p_sched_id, 0, null, p_sched_id),
765 x_last_update_date,
766 x_last_updated_by,
767 x_creation_date,
768 x_created_by,
769 x_last_update_login,
770 x_request_id,
771 x_program_application_id,
772 x_program_id,
773 x_program_update_date,
774 cur_resc.resource_id,
775 x_uom_code,
776 cur_resc.basis_type,
777 /*Fixed Bug 5366856. Modified to consider yield factor for resources.
778 Lot based resources should be independent of Yield and QPA.
779 Item based Phantom resources should consider yield and QPA. */
780 decode(cur_resc.basis_type, wip_constants.PER_LOT , cur_resc.usage_rate_or_amount,
781 round((cur_resc.usage_rate_or_amount * nvl(x_comp_qty, 1)/nvl(x_yield_factor,1)),
782 wip_constants.max_displayed_precision)),/* Bug# 2115415 */
783 cur_resc.activity_id,
784 2, /* non-scheduled */
785 cur_resc.assigned_units,
786 cur_resc.autocharge_type,
787 cur_resc.standard_rate_flag,
788 x_applied_resource_units,
789 x_applied_resource_value,
790 x_start_date,
791 x_completion_date,
792 cur_resc.department_id,
793 1, /* phantom_flag = YES */
794 cur_resc.phantom_op_seq_num,
795 p_phantom_item_id);
796
797 END LOOP;
798
799 exception
800 when No_Data_Found then
801 null;
802
803 when others then
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805
806 END explode_resources;
807
808 /* public function */
809 function charge_flow_resource_ovhd(
810 p_org_id in number,
811 p_phantom_item_id in number,
812 p_op_seq_num in number,
813 p_comp_txn_id in number,
814 p_txn_temp_id in number,
815 p_line_id in number,
816 p_rtg_rev_date in varchar2) return number IS
817
818 x_success number := 0;
819
820 BEGIN
821
822 begin
823 select line_code into g_line_code
824 from wip_lines
825 where line_id = p_line_id ;
826
827 exception
828 when no_data_found then
829 g_line_code := null ;
830 end ;
831
832 x_success := Charge_flow_Resources(p_txn_temp_id,
833 p_comp_txn_id,
834 p_org_id,
835 p_phantom_item_id,
836 p_op_seq_num,
837 p_rtg_rev_date);
838
839 if (x_success<>0) then
840 x_success := Charge_Item_Overheads(p_txn_temp_id,
841 p_org_id,
842 p_phantom_item_id,
843 p_op_seq_num,
844 p_rtg_rev_date );
845 if (x_success<>0) then
846 x_success := Charge_Lot_Overheads(p_txn_temp_id,
847 p_org_id,
848 p_phantom_item_id,
849 p_op_seq_num,
850 p_rtg_rev_date );
851 else
852 return x_success ;
853 end if;
854 else
855 return x_success ;
856 end if;
857
858 return 1;
859
860 exception
861 when No_Data_Found then
862 return 1;
863 when others then
864 return 0;
865
866 END charge_flow_resource_ovhd;
867
868 END WIP_EXPLODE_PHANTOM_RTGS;