[Home] [Help]
PACKAGE BODY: APPS.WIP_FLOW_CHARGE_UTILITIES
Source
1 Package Body Wip_Flow_Charge_Utilities as
2 /* $Header: wipworob.pls 120.1 2006/08/17 22:13:56 shkalyan noship $ */
3
4
5 g_line_code VARCHAR2(10) := NULL ;
6
7 /* *********************************************************************
8 Private Procedures
9 ***********************************************************************/
10 function Charge_Resources (p_txn_temp_id in number,
11 p_comp_txn_id in number,
12 p_rtg_rev_date in varchar2) return number is
13
14 BEGIN
15
16 INSERT INTO WIP_COST_TXN_INTERFACE
17 (transaction_id,
18 last_update_date,
19 last_updated_by,
20 last_updated_by_name,
21 creation_date,
22 created_by,
23 created_by_name,
24 last_update_login,
25 request_id,
26 program_application_id,
27 program_id,
28 program_update_date,
29 group_id,
30 source_code,
31 source_line_id,
32 process_phase,
33 process_status,
34 transaction_type,
35 organization_id,
36 organization_code,
37 wip_entity_id,
38 entity_type,
39 primary_item_id,
40 line_id,
41 line_code,
42 transaction_date,
43 acct_period_id,
44 operation_seq_num,
45 department_id,
46 department_code,
47 employee_id,
48 resource_seq_num,
49 resource_id,
50 resource_code,
51 usage_rate_or_amount,
52 basis_type,
53 autocharge_type,
54 standard_rate_flag,
55 transaction_quantity,
56 transaction_uom,
57 primary_quantity,
58 primary_uom,
59 actual_resource_rate,
60 activity_id,
61 activity_name,
62 reason_id,
63 reference,
64 completion_transaction_id,
65 po_header_id,
66 po_line_id,
67 repetitive_schedule_id,
68 attribute_category,
69 attribute1, attribute2, attribute3, attribute4, attribute5,
70 attribute6, attribute7, attribute8, attribute9, attribute10,
71 attribute11, attribute12,attribute13, attribute14, attribute15,
72 project_id,
73 task_id
74 )
75 SELECT
76 NULL,
77 SYSDATE,
78 FND_GLOBAL.USER_ID,
79 FND_GLOBAL.USER_NAME,
80 SYSDATE,
81 FND_GLOBAL.USER_ID,
82 FND_GLOBAL.USER_NAME,
83 MMTT.LAST_UPDATE_LOGIN,
84 MMTT.REQUEST_ID,
85 MMTT.PROGRAM_APPLICATION_ID,
86 MMTT.PROGRAM_ID,
87 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
88 NULL,
89 MMTT.SOURCE_CODE,
90 MMTT.SOURCE_LINE_ID,
91 2, -- Process_Phase
92 1, -- Process Status
93 1,
94 MP.ORGANIZATION_ID,
95 MP.ORGANIZATION_CODE,
96 MMTT.TRANSACTION_SOURCE_ID,
97 4, -- Wip_Entity_Type
98 MMTT.INVENTORY_ITEM_ID,
99 MMTT.REPETITIVE_LINE_ID,
100 g_line_code, -- the global line code variable
101 MMTT.TRANSACTION_DATE,
102 MMTT.ACCT_PERIOD_ID,
103 BOS.OPERATION_SEQ_NUM,
104 BOS.DEPARTMENT_ID,
105 BD.DEPARTMENT_CODE,
106 NULL,
107 BOR.RESOURCE_SEQ_NUM,
108 BOR.RESOURCE_ID,
109 BR.RESOURCE_CODE,
110 sum(BOR.USAGE_RATE_OR_AMOUNT),
111 BOR.BASIS_TYPE,
112 BOR.AUTOCHARGE_TYPE,
113 BOR.STANDARD_RATE_FLAG,
114 /* Bug 5472762 - Modified the following DECODE to derive correct transaction quantity*/
115 sum(BOR.USAGE_RATE_OR_AMOUNT * DECODE (BOR.BASIS_TYPE,
116 1, MMTT.PRIMARY_QUANTITY,
117 2, DECODE( wfs.QUANTITY_COMPLETED + MMTT.PRIMARY_QUANTITY + wfs.QUANTITY_SCRAPPED,
118 MMTT.PRIMARY_QUANTITY, Decode(Sign(mmtt.primary_quantity),1,1,-1),
119 0, -1,
120 0
121 ),
122 0
123 )),
124 BR.UNIT_OF_MEASURE,
125 /* Bug 5472762 - Modified the following DECODE to derive correct primary quantity*/
126 sum(BOR.USAGE_RATE_OR_AMOUNT * DECODE (BOR.BASIS_TYPE,
127 1, MMTT.PRIMARY_QUANTITY,
128 2, DECODE( wfs.QUANTITY_COMPLETED + MMTT.PRIMARY_QUANTITY + wfs.QUANTITY_SCRAPPED,
129 MMTT.PRIMARY_QUANTITY, Decode(Sign(mmtt.primary_quantity),1,1,-1),
130 0, -1,
131 0
132 ),
133 0
134 )),
135 BR.UNIT_OF_MEASURE,
136 NULL,
137 NVL(BOR.ACTIVITY_ID,-1),
138 ca.activity,
139 MMTT.REASON_ID,
140 MMTT.TRANSACTION_REFERENCE,
141 MMTT.COMPLETION_TRANSACTION_ID,
142 NULL,
143 NULL,
144 NULL,
145 NULL,
146 NULL, NULL, NULL, NULL, NULL,
147 NULL, NULL, NULL, NULL, NULL,
148 NULL, NULL, NULL, NULL, NULL,
149 wfs.PROJECT_ID,
150 wfs.TASK_ID
151 FROM
152 BOM_OPERATION_RESOURCES BOR,
153 WIP_FLOW_SCHEDULES wfs,
154 BOM_DEPARTMENTS BD,
155 BOM_RESOURCES BR,
156 CST_ACTIVITIES CA,
157 BOM_OPERATION_SEQUENCES BOS,
158 -- BOM_OPERATIONAL_ROUTINGS ROUT,
159 mtl_material_transactions_temp MMTT,
160 mtl_parameters mp
161 WHERE
162 MMTT.transaction_temp_id = p_txn_temp_id
163 AND MMTT.inventory_item_id = wfs.primary_item_id
164 AND MMTT.organization_id = wfs.organization_Id
165 AND MMTT.organization_id = mp.organization_id
166 -- AND ROUT.assembly_item_id = wfs.primary_item_id
167 -- AND ROUT.organization_id = wfs.organization_id
168 -- AND NVL(ROUT.alternate_routing_designator, -1) =
169 -- NVL(wfs.alternate_routing_designator, -1)
170 AND MMTT.common_routing_seq_id = bos.routing_sequence_id
171 -- for implement ECO we only explode those operations with implementation date
172 AND BOS.implementation_date is not null
173 AND BOS.effectivity_date <=
174 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
175 AND NVL(BOS.disable_date,
176 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
177 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
178 AND bos.operation_sequence_id = bor.operation_sequence_id
179 AND wfs.organization_id = bd.organization_id
180 AND bos.department_id = bd.department_id
181 AND wfs.organization_id = br.organization_id
182 AND bor.resource_id = br.resource_id
183 AND wfs.wip_entity_id = MMTT.transaction_source_id
184 AND wfs.organization_id = MMTT.organization_id
185 -- for implement ECO we only explode those undeleted resource
186 AND (bor.acd_type <> 3 or bor.acd_type is null)
187 AND bor.autocharge_type <> 2 -- basically we charge it for everything except for manual
188 AND br.cost_element_id in (3, 4)
189 AND bor.usage_rate_or_amount <> 0
190 AND (bos.count_point_type in (1, 2)
191 OR (mmtt.transaction_action_id = 30
192 AND Nvl(mmtt.operation_seq_num,-1) <> -1
193 AND wip_flow_utilities.event_to_lineop_seq_num(
194 bos.routing_sequence_id,
195 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
196 bos.operation_seq_num) = mmtt.operation_seq_num)) --CFM Scrap. Resources are charged at the scrap line op even if the events are non-autocharge operations.
197 AND DECODE (BOR.BASIS_TYPE,
198 1, MMTT.TRANSACTION_QUANTITY,
199 2, DECODE(wfs.QUANTITY_COMPLETED,
200 0, 1,
201 0 ),
202 0 ) <> 0
203 AND Decode (BOR.BASIS_TYPE,
204 2, Decode(WFS.SCHEDULED_FLAG,
205 1,MMTT.TRANSACTION_ACTION_ID,
206 0),
207 0) <> 30 -- Lot based resources are not charged for scheduled cfm scrap
208 AND bor.activity_id = ca.activity_id (+)
209 AND Nvl(bos.operation_type,1) = 1
210 AND wip_flow_utilities.same_or_prior_lineop_safe(bos.routing_sequence_id,
211 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
212 bos.operation_seq_num,
213 Nvl(mmtt.operation_seq_num,-1)) = 1 -- CFM Scrap
214 GROUP BY
215 BOS.OPERATION_SEQ_NUM,
216 BOS.DEPARTMENT_ID,
217 BD.DEPARTMENT_CODE,
218 BOR.RESOURCE_ID,
219 BOR.RESOURCE_SEQ_NUM,
220 FND_GLOBAL.USER_ID,
221 FND_GLOBAL.USER_NAME,
222 MMTT.LAST_UPDATE_LOGIN,
223 MMTT.REQUEST_ID,
224 MMTT.PROGRAM_APPLICATION_ID,
225 MMTT.PROGRAM_ID,
226 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
227 MMTT.SOURCE_CODE,
228 MMTT.SOURCE_LINE_ID,
229 MMTT.ORGANIZATION_ID,
230 MMTT.TRANSACTION_SOURCE_ID,
231 MMTT.INVENTORY_ITEM_ID,
232 MMTT.REPETITIVE_LINE_ID,
233 MMTT.TRANSACTION_DATE,
234 MMTT.ACCT_PERIOD_ID,
235 BR.RESOURCE_CODE,
236 BOR.BASIS_TYPE,
237 BOR.AUTOCHARGE_TYPE,
238 BOR.STANDARD_RATE_FLAG,
239 BR.UNIT_OF_MEASURE,
240 NVL(BOR.ACTIVITY_ID,-1),
241 ca.activity,
242 MMTT.REASON_ID,
243 MMTT.TRANSACTION_REFERENCE,
244 MMTT.COMPLETION_TRANSACTION_ID,
245 wfs.PROJECT_ID,
246 wfs.TASK_ID,
247 /* although will pass compilation, but will get a run
248 time sql error - without these two in group by */
249 MP.ORGANIZATION_ID,
250 MP.ORGANIZATION_CODE;
251
252 -- Taking care of the Activity update in two stages
253 -- as we have an index on completion_txn_id
254 UPDATE WIP_COST_TXN_INTERFACE
255 SET ACTIVITY_ID = DECODE(ACTIVITY_ID,
256 -1, NULL,
257 ACTIVITY_ID)
258 WHERE COMPLETION_TRANSACTION_ID = p_comp_txn_id;
259
260 return 1;
261
262 exception
263
264 when others then
265 return 0;
266
267 End Charge_Resources ;
268
269
270
271 function Charge_Item_Overheads(p_txn_temp_id in number,
272 p_rtg_rev_date in varchar2 ) return number is
273
274 Begin
275
276 INSERT INTO WIP_COST_TXN_INTERFACE
277 ( transaction_id,
278 last_update_date,
279 last_updated_by,
280 last_updated_by_name,
281 creation_date,
282 created_by,
283 created_by_name,
284 last_update_login,
285 request_id,
286 program_application_id,
287 program_id,
288 program_update_date,
289 group_id,
290 source_code,
291 source_line_id,
292 process_phase,
293 process_status,
294 transaction_type,
295 organization_id,
296 organization_code,
297 wip_entity_id,
298 entity_type,
299 primary_item_id,
300 line_id,
301 line_code,
302 transaction_date,
303 acct_period_id,
304 operation_seq_num,
305 department_id,
306 department_code,
307 employee_id,
308 resource_seq_num,
309 resource_id,
310 resource_code,
311 usage_rate_or_amount,
312 basis_type,
313 autocharge_type,
314 standard_rate_flag,
315 transaction_quantity,
316 transaction_uom,
317 primary_quantity,
318 primary_uom,
319 actual_resource_rate,
320 activity_id,
321 activity_name,
322 reason_id,
323 reference,
324 completion_transaction_id,
325 po_header_id,
326 po_line_id,
327 repetitive_schedule_id,
328 attribute_category,
329 attribute1, attribute2, attribute3, attribute4, attribute5,
330 attribute6, attribute7, attribute8, attribute9, attribute10,
331 attribute11, attribute12, attribute13, attribute14, attribute15,
332 project_id,
333 task_id)
334 SELECT
335 NULL,
336 SYSDATE,
337 FND_GLOBAL.USER_ID,
338 FND_GLOBAL.USER_NAME,
339 SYSDATE,
340 FND_GLOBAL.USER_ID,
341 FND_GLOBAL.USER_NAME,
342 MMTT.LAST_UPDATE_LOGIN,
343 MMTT.REQUEST_ID,
344 MMTT.PROGRAM_APPLICATION_ID,
345 MMTT.PROGRAM_ID,
346 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
347 NULL,
348 MMTT.SOURCE_CODE,
349 MMTT.SOURCE_LINE_ID,
350 2,
351 1,
352 2,
353 MP.ORGANIZATION_ID,
354 MP.ORGANIZATION_CODE,
355 MMTT.TRANSACTION_SOURCE_ID,
356 4,
357 MMTT.INVENTORY_ITEM_ID,
358 MMTT.REPETITIVE_LINE_ID,
359 g_line_code, -- the global line code variable
360 MMTT.TRANSACTION_DATE,
361 MMTT.ACCT_PERIOD_ID,
362 BOS.OPERATION_SEQ_NUM,
363 BOS.DEPARTMENT_ID,
364 BD.DEPARTMENT_CODE,
365 NULL,
366 NULL,
367 NULL,
368 NULL,
369 NULL,
370 1, -- Per Item
371 1, -- WWIP_MOVE
372 NULL,
373 NVL(MMTT.transaction_quantity, 0),
374 MMTT.TRANSACTION_UOM,
375 NVL(MMTT.primary_quantity, 0),
376 MMTT.ITEM_PRIMARY_UOM_CODE,
377 NULL,
378 NULL,
379 NULL,
380 MMTT.REASON_ID,
381 MMTT.TRANSACTION_REFERENCE,
382 MMTT.COMPLETION_TRANSACTION_ID,
383 NULL,
384 NULL,
385 NULL,
386 NULL,
387 NULL, NULL, NULL, NULL, NULL,
388 NULL, NULL, NULL, NULL, NULL,
389 NULL, NULL, NULL, NULL, NULL,
390 wfs.PROJECT_ID,
391 wfs.TASK_ID
392 FROM
393 BOM_DEPARTMENTS bd,
394 BOM_OPERATION_SEQUENCES bos,
395 WIP_FLOW_SCHEDULES wfs,
396 -- BOM_OPERATIONAL_ROUTINGS BOR,
397 mtl_material_transactions_temp mmtt,
398 mtl_parameters mp
399 WHERE
400 MMTT.transaction_temp_id = p_txn_temp_id
401 AND MMTT.transaction_source_id = wfs.wip_entity_id
402 AND MMTT.inventory_item_id = wfs.primary_item_id
403 AND MMTT.organization_id = wfs.organization_Id
404 AND MMTT.organization_id = mp.organization_id
405 -- AND BOR.assembly_item_id = wfs.primary_item_id
406 -- AND BOR.organization_id = wfs.organization_id
407 -- AND NVL(BOR.alternate_routing_designator, -1) =
408 -- NVL(wfs.alternate_routing_designator, -1)
409 AND MMTT.common_routing_seq_id = bos.routing_sequence_id
410 -- for implement ECO we only explode those operations with implementation date
411 AND BOS.implementation_date is not null
412 AND BOS.effectivity_date <=
413 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
414 AND NVL(BOS.disable_date,
415 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
416 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
417 AND wfs.organization_id = bd.organization_id
418 AND bos.department_id = bd.department_id
419 AND (bos.count_point_type in (1, 2) -- ovhd for autocharge operations
420 OR (mmtt.transaction_action_id = 30
421 AND Nvl(mmtt.operation_seq_num,-1) <> -1
422 AND wip_flow_utilities.event_to_lineop_seq_num(
423 bos.routing_sequence_id,
424 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
425 bos.operation_seq_num) = mmtt.operation_seq_num)) --CFM Scrap. Overheads are charged at the scrap line op even if the events are non-autocharge operations.
426 AND Nvl(bos.operation_type,1) = 1
427 AND wip_flow_utilities.same_or_prior_lineop_safe(bos.routing_sequence_id,
428 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
429 bos.operation_seq_num,
430 Nvl(mmtt.operation_seq_num,-1)) = 1; -- CFM Scrap
431 return 1;
432
433 exception
434
435 when others then
436 return 0;
437
438 end Charge_Item_Overheads;
439
440
441 function Charge_Lot_Overheads(p_txn_temp_id in number,
442 p_rtg_rev_date in varchar2 ) return number is
443
444 Begin
445
446 INSERT INTO WIP_COST_TXN_INTERFACE
447 ( transaction_id,
448 last_update_date,
449 last_updated_by,
450 last_updated_by_name,
451 creation_date,
452 created_by,
453 created_by_name,
454 last_update_login,
455 request_id,
456 program_application_id,
457 program_id,
458 program_update_date,
459 group_id,
460 source_code,
461 source_line_id,
462 process_phase,
463 process_status,
464 transaction_type,
465 organization_id,
466 organization_code,
467 wip_entity_id,
468 entity_type,
469 primary_item_id,
470 line_id,
471 line_code,
472 transaction_date,
473 acct_period_id,
474 operation_seq_num,
475 department_id,
476 department_code,
477 employee_id,
478 resource_seq_num,
479 resource_id,
480 resource_code,
481 usage_rate_or_amount,
482 basis_type,
483 autocharge_type,
484 standard_rate_flag,
485 transaction_quantity,
486 transaction_uom,
487 primary_quantity,
488 primary_uom,
489 actual_resource_rate,
490 activity_id,
491 activity_name,
492 reason_id,
493 reference,
494 completion_transaction_id,
495 po_header_id,
496 po_line_id,
497 repetitive_schedule_id,
498 attribute_category,
499 attribute1, attribute2, attribute3, attribute4, attribute5,
500 attribute6, attribute7, attribute8, attribute9, attribute10,
501 attribute11, attribute12, attribute13, attribute14, attribute15,
502 project_id,
503 task_id)
504 SELECT
505 NULL,
506 SYSDATE,
507 FND_GLOBAL.USER_ID,
508 FND_GLOBAL.USER_NAME,
509 SYSDATE,
510 FND_GLOBAL.USER_ID,
511 FND_GLOBAL.USER_NAME,
512 MMTT.LAST_UPDATE_LOGIN,
513 MMTT.REQUEST_ID,
514 MMTT.PROGRAM_APPLICATION_ID,
515 MMTT.PROGRAM_ID,
516 NVL(MMTT.PROGRAM_UPDATE_DATE, SYSDATE),
517 NULL,
518 MMTT.SOURCE_CODE,
519 MMTT.SOURCE_LINE_ID,
520 2,
521 1,
522 2,
523 MP.ORGANIZATION_ID,
524 MP.ORGANIZATION_CODE,
525 MMTT.TRANSACTION_SOURCE_ID,
526 4,
527 MMTT.INVENTORY_ITEM_ID,
528 MMTT.REPETITIVE_LINE_ID,
529 g_line_code, -- the global line code variable
530 MMTT.TRANSACTION_DATE,
531 MMTT.ACCT_PERIOD_ID,
532 BOS.OPERATION_SEQ_NUM,
533 BOS.DEPARTMENT_ID,
534 BD.DEPARTMENT_CODE,
535 NULL,
536 NULL,
537 NULL,
538 NULL,
539 NULL,
540 2, -- Per Lot
541 1, -- WWIP_MOVE
542 NULL,
543 /* Bug 5472762 - Modified the following DECODE to derive correct transaction quantity*/
544 DECODE( wfs.QUANTITY_COMPLETED + MMTT.PRIMARY_QUANTITY + wfs.QUANTITY_SCRAPPED,
545 MMTT.PRIMARY_QUANTITY, Decode(Sign(mmtt.primary_quantity),1,1,-1),
546 0, -1,
547 0
548 ),
549 MMTT.TRANSACTION_UOM,
550 /* Bug 5472762 - Modified the following DECODE to derive correct primary quantity*/
551 DECODE( wfs.QUANTITY_COMPLETED + MMTT.PRIMARY_QUANTITY + wfs.QUANTITY_SCRAPPED,
552 MMTT.PRIMARY_QUANTITY, Decode(Sign(mmtt.primary_quantity),1,1,-1),
553 0, -1,
554 0
555 ),
556 MMTT.ITEM_PRIMARY_UOM_CODE,
557 NULL,
558 NULL,
559 NULL,
560 MMTT.REASON_ID,
561 MMTT.TRANSACTION_REFERENCE,
562 MMTT.COMPLETION_TRANSACTION_ID,
563 NULL,
564 NULL,
565 NULL,
566 NULL,
567 NULL, NULL, NULL, NULL, NULL,
568 NULL, NULL, NULL, NULL, NULL,
569 NULL, NULL, NULL, NULL, NULL,
570 wfs.PROJECT_ID,
571 wfs.TASK_ID
572 FROM
573 BOM_DEPARTMENTS bd,
574 BOM_OPERATION_SEQUENCES bos,
575 WIP_flow_schedules wfs,
576 -- BOM_OPERATIONAL_ROUTINGS BOR,
577 mtl_material_transactions_temp mmtt,
578 mtl_parameters mp
579 WHERE
580 MMTT.transaction_temp_id = p_txn_temp_id
581 AND MMTT.transaction_source_id = wfs.wip_entity_id
582 AND MMTT.inventory_item_id = wfs.primary_item_id
583 AND MMTT.organization_id = wfs.organization_Id
584 AND MMTT.organization_id = mp.organization_id
585 -- AND BOR.assembly_item_id = wfs.primary_item_id
586 -- AND BOR.organization_id = wfs.organization_id
587 -- AND NVL(BOR.alternate_routing_designator, -1) =
588 -- NVL(wfs.alternate_routing_designator, -1)
589 AND MMTT.common_routing_seq_id = bos.routing_sequence_id
590 AND decode( NVL(wfs.Quantity_Completed, 0),
591 0, 1,
592 0 ) <> 0
593 -- for implement ECO we only explode those operations with implementation date
594 AND BOS.implementation_date is not null
595 AND BOS.effectivity_date <=
596 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
597 AND NVL(BOS.disable_date,
598 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
599 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
600 AND wfs.organization_id = bd.organization_id
601 AND bos.department_id = bd.department_id
602 AND (bos.count_point_type in (1, 2) -- ovhd for autocharge operations
603 OR (mmtt.transaction_action_id = 30
604 AND Nvl(mmtt.operation_seq_num,-1) <> -1
605 AND wip_flow_utilities.event_to_lineop_seq_num(
606 bos.routing_sequence_id,
607 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
608 bos.operation_seq_num) = mmtt.operation_seq_num)) --CFM Scrap. Overheads are charged at the scrap line op even if the events are non-autocharge operations.
609 AND Nvl(bos.operation_type,1) = 1
610 AND Decode(WFS.SCHEDULED_FLAG,
611 1,MMTT.TRANSACTION_ACTION_ID,
612 0) <> 30 -- Lot based ovhds are not charged for scheduled cfm scrap
613 AND wip_flow_utilities.same_or_prior_lineop_safe(bos.routing_sequence_id,
614 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT),
615 bos.operation_seq_num,
616 Nvl(mmtt.operation_seq_num,-1)) = 1; -- CFM Scrap
617
618 return 1;
619
620 exception
621 when others then
622 return 0;
623
624
625 end Charge_Lot_Overheads;
626
627
628
629
630 /* *********************************************************************
631 Public Functions
632 ***********************************************************************/
633 Function Charge_Resource_Overhead (p_header_id in number,
634 p_rtg_rev_date in varchar2) return number is
635
636 /* ********************************************************
637 Cursor to get all Unique Flow Completions from MMTT
638 ******************************************************** */
639 CURSOR Flow_Completion (header_id number) is
640 SELECT completion_transaction_id,
641 transaction_temp_id,
642 repetitive_line_id
643 FROM mtl_material_transactions_temp
644 WHERE transaction_header_id = header_id
645 AND transaction_source_type_id = 5
646 AND UPPER(NVL(flow_schedule,'N')) = 'Y'
647 AND transaction_action_id in (31, 32, 30)-- CFM Scrap
648 AND process_flag = 'Y'
649 AND wip_entity_type = 4 ;
650
651 /* ********************************************************
652 Cursor to get all PHANTOMS for Flow Completions
653 The phantoms must be in a count-point operation
654 and also an ECO implemented operation
655 ******************************************************** */
656 CURSOR phantoms (header_id number) is
657 SELECT mmtt.inventory_item_id phantom_item_id,
658 mmtt.operation_seq_num*(-1) operation_seq_num,
659 mmtt.completion_transaction_id,
660 mmtt.transaction_temp_id,
661 mmtt.repetitive_line_id
662 FROM mtl_material_transactions_temp mmtt,
663 wip_flow_schedules wfs,
664 bom_operational_routings bor,
665 bom_operation_sequences bos
666 WHERE
667 mmtt.transaction_header_id = header_id
668 AND mmtt.transaction_source_type_id = 5
669 AND UPPER(NVL(mmtt.flow_schedule,'N')) = 'Y'
670 AND mmtt.transaction_action_id in (1, 27, 33, 34)
671 AND mmtt.operation_seq_num < 0 -- phantoms only
672 AND mmtt.process_flag = 'Y'
673 AND mmtt.wip_entity_type = 4
674 AND MMTT.transaction_source_id = wfs.wip_entity_id
675 AND MMTT.organization_id = wfs.organization_id
676 AND wfs.primary_item_id = bor.assembly_item_id
677 AND wfs.organization_id = bor.organization_id
678 AND NVL(wfs.alternate_routing_designator, -1)
679 = NVL(bor.alternate_routing_designator, -1)
680 -- for implement ECO the routing must be not pending from ecn
681 AND bor.pending_from_ecn is null
682 AND bor.common_routing_sequence_id = bos.routing_sequence_id
683 -- for implement ECO we only explode those operations with implementation date
684 AND BOS.implementation_date is not null
685 AND bos.effectivity_date <=
686 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
687 AND NVL(bos.disable_date,
688 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
689 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
690 AND bos.operation_seq_num = mmtt.operation_seq_num*(-1)
691 AND Nvl(bos.operation_type,1) = 1
692 AND bos.count_point_type in (1, 2)
693 /* Start of fix for bug 2743096: To bring in records from mmtt where
694 phantoms are attached to default operation sequence 1. */
695 UNION
696 SELECT mmtt.inventory_item_id phantom_item_id,
697 mmtt.operation_seq_num*(-1) operation_seq_num,
698 mmtt.completion_transaction_id,
699 mmtt.transaction_temp_id,
700 mmtt.repetitive_line_id
701 FROM mtl_material_transactions_temp mmtt,
702 wip_flow_schedules wfs,
703 bom_operational_routings bor,
704 bom_operation_sequences bos
705 WHERE
706 mmtt.transaction_header_id = header_id
707 AND mmtt.transaction_source_type_id = 5
708 AND UPPER(NVL(mmtt.flow_schedule,'N')) = 'Y'
709 AND mmtt.transaction_action_id in (1, 27, 33, 34)
710 AND mmtt.operation_seq_num < 0 -- phantoms only
711 AND mmtt.process_flag = 'Y'
712 AND mmtt.wip_entity_type = 4
713 AND MMTT.transaction_source_id = wfs.wip_entity_id
714 AND MMTT.organization_id = wfs.organization_id
715 AND wfs.primary_item_id = bor.assembly_item_id
716 AND wfs.organization_id = bor.organization_id
717 AND NVL(wfs.alternate_routing_designator, -1)
718 = NVL(bor.alternate_routing_designator, -1)
719 -- for implement ECO the routing must be not pending from ecn
720 AND bor.pending_from_ecn is null
721 AND bor.common_routing_sequence_id = bos.routing_sequence_id
722 -- for implement ECO we only explode those operations with implementation date
723 AND BOS.implementation_date is not null
724 AND bos.effectivity_date <=
725 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
726 AND NVL(bos.disable_date,
727 to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
728 >= to_date(p_rtg_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
729 AND mmtt.operation_seq_num = -1 /* for phantoms attached to op seq 1 */
730 AND Nvl(bos.operation_type,1) = 1
731 AND bos.count_point_type in (1, 2);
732 /* End of fix for bug 2743096 */
733
734 x_primary_uom VARCHAR2(3);
735 x_primary_txn_qty NUMBER := 0;
736 x_success number := 0;
737 x_org_id number; /* phantom costing */
738
739 BEGIN
740
741 FOR Com_Rec IN Flow_Completion(p_header_id) LOOP
742 x_success := 0;
743
744 begin
745 /* The PK for wip_lines is just Line_id */
746 select line_code into g_line_code
747 from wip_lines
748 where line_id = Com_Rec.repetitive_line_id ;
749
750 exception
751
752 when no_data_found then
753 g_line_code := null ;
754 end ;
755
756 x_success := Charge_Resources(Com_Rec.transaction_temp_id,
757 Com_Rec.completion_transaction_id,
758 p_rtg_rev_date );
759 if (x_success<>0) then
760 x_success := Charge_Item_Overheads(Com_Rec.transaction_temp_id,
761 p_rtg_rev_date );
762 if (x_success<>0) then
763 x_success := Charge_Lot_Overheads(Com_Rec.transaction_temp_id,
764 p_rtg_rev_date );
765 else
766 return x_success ;
767 end if;
768 else
769 return x_success ;
770 end if;
771
772 END LOOP ;
773
774 /* phantom costing */
775 SELECT organization_id
776 INTO x_org_id
777 FROM mtl_material_transactions_temp
778 WHERE transaction_header_id = p_header_id
779 AND rownum = 1;
780
781 IF (wip_globals.USE_PHANTOM_ROUTINGS(x_org_id) = WIP_CONSTANTS.YES) THEN
782 FOR Phan_Rec IN phantoms(p_header_id) LOOP
783 x_success := 0;
784
785 x_success := WIP_EXPLODE_PHANTOM_RTGS.charge_flow_resource_ovhd(
786 x_org_id,
787 phan_rec.phantom_item_id,
788 phan_rec.operation_seq_num,
789 phan_rec.completion_transaction_id,
790 phan_rec.transaction_temp_id,
791 phan_rec.repetitive_line_id,
792 p_rtg_rev_date);
793
794 if (x_success = 0) then
795 return x_success;
796 end if;
797 END LOOP;
798
799 /* delete phantoms from MMTT before moved to MMT */
800 delete mtl_material_transactions_temp
801 where
802 transaction_header_id = p_header_id
803 AND transaction_source_type_id = 5
804 AND UPPER(NVL(flow_schedule,'N')) = 'Y'
805 AND transaction_action_id in (1, 27, 33, 34)
806 AND operation_seq_num < 0
807 AND process_flag = 'Y'
808 AND wip_entity_type = 4 ;
809
810 END IF;
811 /* end of phantom costing */
812
813 return 1;
814 exception
815
816 when No_Data_Found then
817 return 1;
818
819 when others then
820 return 0;
821
822 end Charge_Resource_Overhead ;
823
824 /********************************************************************
825 This function will be called from cmlctw - the cost transaction
826 worker for both the resource as well as the overheads to validate
827 the process phase for the group_id,
828 - This will be called only for cfm flow schedules, so we don not
829 have to worry about the wip_entity_type in here.
830 **********************************************************************/
831
832 function Validate_Resource_Overhead (p_group_id in number,
833 p_err_mesg out NOCOPY varchar) return number is
834 /* Cursor for the rows that will fail validation */
835 CURSOR Failure_Cursor(p_group_id in number) is
836 Select
837 Transaction_id
838 from WIP_COST_TXN_INTERFACE
839 where group_id = p_group_id
840 and process_phase = 1; /* The Process Phase is 1 */
841 x_error_mesg varchar2(240);
842 begin
843
844
845 fnd_message.set_name('WIP', 'WIP_FLOW_RES_OVHD_VALIDATION');
846 x_error_mesg := fnd_message.get ;
847
848 For fail_rec in Failure_Cursor(p_group_id) LOOP
849
850 Update WIP_COST_TXN_INTERFACE
851 set PROCESS_STATUS = 3 /* set the process_phase to error */
852 where transaction_id = fail_rec.transaction_id ;
853
854 Insert into WIP_TXN_INTERFACE_ERRORS
855 ( transaction_id,
856 error_column,
857 error_message,
858 last_update_date,
859 last_updated_by,
860 creation_date,
861 created_by,
862 last_update_login,
863 request_id,
864 program_application_id,
865 program_id,
866 program_update_date)
867 Select
868 transaction_id,
869 'PROCESS_PHASE',
870 x_error_mesg,
871 SYSDATE,
872 last_updated_by,
873 SYSDATE,
874 last_updated_by,
875 last_update_login,
876 request_id,
877 program_application_id,
878 program_id,
879 SYSDATE
880 from WIP_COST_TXN_INTERFACE
881 where transaction_id = fail_rec.transaction_id ;
882
883 end LOOP ;
884
885
886 return 1 ;
887
888 exception
889 when others then
890 p_err_mesg := 'WIP_FLOW_CHARGE_UTILITIES.VALIDATE_RESOURCE_OVERHEAD' ||
891 substr(SQLERRM,1,150);
892 return 0;
893
894 end Validate_Resource_Overhead ;
895
896
897 end Wip_Flow_Charge_Utilities;