[Home] [Help]
PACKAGE BODY: APPS.WIP_FLOWRESCHARGE
Source
1 package body wip_flowResCharge as
2 /* $Header: wipfsrcb.pls 120.6.12010000.2 2008/08/15 00:47:32 kboonyap ship $ */
3
4 procedure chargePhantomResource(p_txnTempID in number,
5 p_orgID in number,
6 p_effDate in date,
7 p_hasRouting in boolean,
8 x_returnStatus out nocopy varchar2);
9 /**
10 * This procedure is called by flow completion/work orderless completion to charge the
11 * resource and item/lot overhead.
12 * p_txnTempID is the temp id in the MMTT
13 *
14 * We will base the overhead and resource transaction out of the BOM.
15 * 1. Charge the resources even if the auto-charge flag is set to NO.
16 * -- This is based on the count_point_type in bom_operation_sequences
17 * -- 1 Yes Autocharge
18 * -- 2 No Autocharge
19 * -- 3 No Direct Charge
20 * 2. Charge the Lot Based only once if it is pre-planned. Else we charge it everytime.
21 * -- This is based on basis_type in bom_resources
22 * -- 1 Item
23 * -- 2 Lot
24 * -- 3 Resource Unit
25 * -- 4 Resource Value
26 * -- 5 Total Value
27 * -- 6 Activity
28 * 3. We will NOT charge the Manually Charged resources
29 * -- This is based on autocharge_type in bom_operation_resources
30 * -- If this is set to WIP_MOVE where the info about the standard rate is stored,
31 * -- we will still charge this even though it would be OSP resource
32 * -- 1 Wip move
33 * -- 2 Manual
34 * -- 3 PO receipt
35 * -- 4 PO move
36 * 4. Different overhead and ordinary resource
37 * -- This is based on cost_element_id in bom_resources
38 * -- 1 Material
39 * -- 2 Material Overheads
40 * -- 3 Resource
41 * -- 4 Outside Processing
42 * -- 5 Overhead
43 */
44 procedure chargeResourceAndOverhead(p_txnTempID in number,
45 x_returnStatus out NOCOPY varchar2) is
46 -- cursor for find out all the applicable operation seq num
47 -- to be charged. For normal work orderless scrap, we don't support scrap
48 -- at a particular operation seq num. So in terms of resources to be charged,
49 -- there is no diff between completion and completion.
50 cursor op_c(p_commonRoutSeqID number,
51 p_effDate date) is
52 select operation_sequence_id,
53 operation_seq_num
54 from bom_operation_sequences
55 where routing_sequence_id = p_commonRoutSeqID
56 and nvl(operation_type, 1) = 1
57 and effectivity_date <= p_effDate
58 and nvl(disable_date, p_effDate+1) > p_effDate
59 and implementation_date is not null
60 and count_point_type in (1, 2);
61
62 -- cursor to find out all applicable events to be charged for a particular lineOp.
63 -- for scrap, according to Biju's design doc, resources and overheads used at
64 -- non-autocharge operations will not be charged unless it is the scrap lineop.
65 cursor event_c(p_lineOpSeqID number,
66 p_effDate date,
67 p_scrapLineOp number,
68 p_parentTxnActionID number) is
69 select bos2.operation_sequence_id,
70 bos2.operation_seq_num
71 from bom_operation_sequences bos1,
72 bom_operation_sequences bos2
73 where bos2.line_op_seq_id = bos1.operation_sequence_id
74 and bos2.operation_type = 1
75 and bos1.operation_sequence_id = p_lineOpSeqID
76 and bos2.effectivity_date <= p_effDate
77 and nvl(bos2.disable_date, p_effDate+1) > p_effDate
78 and bos2.implementation_date is not null
79 and ( bos2.count_point_type in (1, 2)
80 or ( p_parentTxnActionID = WIP_CONSTANTS.SCRASSY_ACTION
81 and bos1.operation_seq_num = p_scrapLineOp));
82
83 -- cursor to find out all the events that are not assigned to any lineop.
84 -- It is decided on the meeting(Richard, Barry, Adrian, Serena, Jung and Yong) that
85 -- we should charge/uncharge the resource for the event even the event is not assigned
86 -- to any line op(usually doesn't happen).
87 cursor standalone_event_c(p_routingSeqID number,
88 p_effDate date) is
89 select operation_sequence_id,
90 operation_seq_num
91 from bom_operation_sequences
92 where routing_sequence_id = p_routingSeqID
93 and operation_type = 1
94 and effectivity_date <= p_effDate
95 and nvl(disable_date, p_effDate+1) > p_effDate
96 and implementation_date is not null
97 and line_op_seq_id is null;
98
99 l_chargeTbl bom_rtg_network_api.op_tbl_type;
100 l_lineOpTbl bom_rtg_network_api.op_tbl_type;
101 l_count number := 1;
102 l_index number := 1;
103
104 l_commonRoutSeqID number;
105 l_effDate date;
106 l_parentTxnActionID number;
107 l_toOpSeqNum number := null;
108 l_cfmFlag number;
109 l_orgID number;
110
111 l_params wip_logger.param_tbl_t;
112 l_returnStatus varchar2(1);
113 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
114 l_org_code VARCHAR2(3); --bug 5231366
115
116 begin
117 l_params(1).paramName := 'p_txnTempID';
118 l_params(1).paramValue := p_txnTempID;
119 x_returnStatus := fnd_api.g_ret_sts_success;
120 if (l_logLevel <= wip_constants.trace_logging) then
121 wip_logger.entryPoint(p_procName => 'wip_flowResCharge.chargeResourceAndOverhead',
122 p_params => l_params,
123 x_returnStatus => x_returnStatus);
124 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
125 raise fnd_api.g_exc_unexpected_error;
126 end if;
127 end if;
128
129 begin
130 select bor.common_routing_sequence_id,
131 nvl(bor.cfm_routing_flag, 2),
132 mmtt.routing_revision_date,
133 nvl(mmtt.operation_seq_num, -1),
134 mmtt.transaction_action_id,
135 mmtt.organization_id
136 into l_commonRoutSeqID,
137 l_cfmFlag,
138 l_effDate,
139 l_toOpSeqNum,
140 l_parentTxnActionID,
141 l_orgID
142 from bom_operational_routings bor,
143 mtl_material_transactions_temp mmtt
144 where bor.assembly_item_id = mmtt.inventory_item_id
145 and bor.organization_id = mmtt.organization_id
146 and nvl(bor.alternate_routing_designator, 'NONE') =
147 nvl(mmtt.alternate_routing_designator, 'NONE')
148 and mmtt.transaction_temp_id = p_txnTempID;
149 -- to check the operation effectivity date, we should use routing_revision_date
150 -- instead of transaction date. The reason is that even when a routing revision is
151 -- not in effect, we still allow the user to pick that up to do transactions.
152 exception
153 when others then
154 -- no routing, try to charege phantom
155 select organization_id,
156 nvl(routing_revision_date, sysdate)
157 into l_orgID,
158 l_effDate
159 from mtl_material_transactions_temp
160 where transaction_temp_id = p_txnTempID;
161
162 chargePhantomResource(p_txnTempID, l_orgID, l_effDate, false, x_returnStatus);
163
164 if (l_logLevel <= wip_constants.trace_logging) then
165 wip_logger.exitPoint(p_procName => 'wip_flowResCharge.chargeResourceAndOverhead',
166 p_procReturnStatus => x_returnStatus,
167 p_msg => 'No routing, tried to charge phantom',
168 x_returnStatus => l_returnStatus); --discard logging return status
169 end if;
170 return;
171 end;
172
173
174 -- for flow txn, we needs to construct the lineOp table, and then itereate through the
175 -- the table to find out the event attached and further down resources attached.
176 -- for normal work orderless txn, to keep the code consistent, I will construct an
177 -- equivalent of that op table.
178 if ( l_cfmFlag = 2 ) then
179 for op_rec in op_c(l_commonRoutSeqID, l_effDate) loop
180 l_chargeTbl(l_count).operation_sequence_id := op_rec.operation_sequence_id;
181 l_chargeTbl(l_count).operation_seq_num := op_rec.operation_seq_num;
182 l_count := l_count + 1;
183 end loop;
184 else
185 wip_flowUtil_priv.constructWipLineOps(p_routingSeqID => l_commonRoutSeqID,
186 p_assyItemID => null,
187 p_orgID => null,
188 p_altRoutDesig => null,
189 p_terminalOpSeqNum => l_toOpSeqNum,
190 x_lineOpTbl => l_lineOpTbl);
191 l_count := l_lineOpTbl.first;
192 while ( l_count is not null ) loop
193 for event_rec in event_c(l_lineOpTbl(l_count).operation_sequence_id,
194 l_effDate,
195 l_toOpSeqNum,
196 l_parentTxnActionID) loop
197 l_chargeTbl(l_index).operation_sequence_id := event_rec.operation_sequence_id;
198 l_chargeTbl(l_index).operation_seq_num := event_rec.operation_seq_num;
199 l_index := l_index + 1;
200 end loop;
201
202 l_count := l_lineOpTbl.next(l_count);
203 end loop;
204
205 -- now we need to add those events that are not assigned to any line op
206 for standalone_event_rec in standalone_event_c(l_commonRoutSeqID,
207 l_effDate) loop
208 l_chargeTbl(l_index).operation_sequence_id :=
209 standalone_event_rec.operation_sequence_id;
210 l_chargeTbl(l_index).operation_seq_num :=
211 standalone_event_rec.operation_seq_num;
212 l_index := l_index + 1;
213 end loop;
214 end if;
215
216 -- now we got all the event/operation seq num that needs to be charged
217 -- iterate through that and find all the applicable resources attached to them
218 l_count := l_chargeTbl.first;
219 while ( l_count is not null ) loop
220 if (l_logLevel <= wip_constants.full_logging) then
221 wip_logger.log(p_msg => 'Inserting Resources for op seq/event: ' ||
222 l_chargeTbl(l_count).operation_seq_num,
223 x_returnStatus => l_returnStatus);
224 end if;
225
226 --bug 5231366
227 select mp.organization_code
228 into l_org_code
229 from mtl_material_transactions_temp mmtt,
230 mtl_parameters mp
231 where mmtt.transaction_temp_id = p_txnTempID
232 and mmtt.organization_id = mp.organization_id;
233
234 insert into wip_cost_txn_interface(
235 last_update_date,
236 last_updated_by,
237 creation_date,
238 created_by,
239 last_update_login,
240 request_id,
241 program_application_id,
242 program_id,
243 program_update_date,
244 group_id,
245 source_code,
246 source_line_id,
247 process_phase,
248 process_status,
249 transaction_type,
250 organization_id,
251 organization_code, --bug 5231366
252 wip_entity_id,
253 entity_type,
254 primary_item_id,
255 line_id,
256 transaction_date,
257 acct_period_id,
258 operation_seq_num,
259 department_id,
260 department_code,
261 resource_seq_num,
262 resource_id,
263 resource_code,
264 usage_rate_or_amount,
265 basis_type,
266 autocharge_type,
267 standard_rate_flag,
268 transaction_quantity,
269 transaction_uom,
270 primary_quantity,
271 primary_uom,
272 actual_resource_rate,
273 activity_id,
274 reason_id,
275 reference,
276 completion_transaction_id,
277 project_id,
278 task_id)
279 select
280 sysdate,
281 mmtt.last_updated_by,
282 sysdate,
283 mmtt.created_by,
284 mmtt.last_update_login,
285 mmtt.request_id,
286 mmtt.program_application_id,
287 mmtt.program_id,
288 nvl(mmtt.program_update_date, sysdate),
289 null, -- group id
290 mmtt.source_code,
291 mmtt.source_line_id,
292 2, -- process phase: resource processing
293 1, -- process status: pending
294 1, -- transaction type: resource
295 mmtt.organization_id,
296 l_org_code, --bug 5231366
297 mmtt.transaction_source_id,
298 4,
299 mmtt.inventory_item_id,
300 mmtt.repetitive_line_id,
301 mmtt.transaction_date,
302 mmtt.acct_period_id,
303 l_chargeTbl(l_count).operation_seq_num,
304 bos.department_id,
305 bd.department_code,
306 bor.resource_seq_num,
307 bor.resource_id,
308 br.resource_code,
309 sum(bor.usage_rate_or_amount),
310 bor.basis_type,
311 bor.autocharge_type,
312 bor.standard_rate_flag,
313 sum(bor.usage_rate_or_amount *
314 decode(bor.basis_type,
315 1, mmtt.primary_quantity,
316 /* Fixed bug 4162698. Since wfs.quantity_completed is either 0 or positive
317 number, we have to set the sign based on transaction type.
318 */
319 2, decode(mmtt.transaction_type_id, 17, -1, 91, -1, 1) *
320 decode(wfs.quantity_completed,
321 0, 1,
322 0),
323 0)), -- you may assign the same res multiple times at any op.
324 br.unit_of_measure,
325 sum(bor.usage_rate_or_amount *
326 decode(bor.basis_type,
327 1, mmtt.primary_quantity,
328 /* Fixed bug 4162698. Since wfs.quantity_completed is either 0 or positive
329 number, we have to set the sign based on transaction type.
330 */
331 2, decode(mmtt.transaction_type_id, 17, -1, 91, -1, 1) *
332 decode(wfs.quantity_completed,
333 0, 1,
334 0),
335 0)),
336 br.unit_of_measure,
337 null, -- actual_resource_rate
338 bor.activity_id,
339 mmtt.reason_id,
340 mmtt.transaction_reference,
341 mmtt.completion_transaction_id,
342 mmtt.project_id,
343 mmtt.task_id
344 from
345 bom_operation_resources bor,
346 wip_flow_schedules wfs,
347 bom_departments bd,
348 bom_resources br,
349 bom_operation_sequences bos,
350 mtl_material_transactions_temp mmtt
351 where bos.operation_sequence_id =
352 l_chargeTbl(l_count).operation_sequence_id
353 and mmtt.transaction_temp_id = p_txnTempID
354 and bor.operation_sequence_id = bos.operation_sequence_id
355 and nvl(bor.acd_type, -1) <> 3 -- for implement ECO we only explode those undeleted res
356 and bor.autocharge_type <> 2 -- charge everything but manual
357 and bor.usage_rate_or_amount <> 0
358 and decode(bor.basis_type,
359 1, mmtt.transaction_quantity,
360 2, decode(wfs.quantity_completed, 0, 1, 0),
361 0) <> 0
362 and decode(bor.basis_type,
363 2, decode(wfs.scheduled_flag, 1, mmtt.transaction_action_id, 0),
364 0 ) <> 30 -- Lot based resources are not charged for scheduled cfm scrap
365 and bd.organization_id = mmtt.organization_id
366 and bd.department_id = bos.department_id
367 and br.organization_id = mmtt.organization_id
368 and br.resource_id = bor.resource_id
369 and br.cost_element_id in (3, 4)
370 and wfs.organization_id = mmtt.organization_id
371 and wfs.wip_entity_id = mmtt.transaction_source_id
372 group by
373 bos.operation_seq_num,
374 bos.department_id,
375 bd.department_code,
376 bor.resource_id,
377 br.resource_code,
378 bor.resource_seq_num,
379 bor.autocharge_type,
380 bor.basis_type,
381 bor.standard_rate_flag,
382 br.unit_of_measure,
383 bor.activity_id,
384 mmtt.last_updated_by,
385 mmtt.created_by,
386 mmtt.last_update_login,
387 mmtt.request_id,
388 mmtt.program_application_id,
389 mmtt.program_id,
390 nvl(mmtt.program_update_date, sysdate),
391 mmtt.source_code,
392 mmtt.source_line_id,
393 mmtt.organization_id,
394 l_org_code,
395 mmtt.transaction_source_id,
396 mmtt.inventory_item_id,
397 mmtt.repetitive_line_id,
398 mmtt.transaction_date,
399 mmtt.acct_period_id,
400 mmtt.reason_id,
401 mmtt.transaction_reference,
402 mmtt.transaction_type_id,
403 mmtt.completion_transaction_id,
404 mmtt.project_id,
405 mmtt.task_id;
406
407 if (l_logLevel <= wip_constants.full_logging) then
408 wip_logger.log(p_msg => 'Inserting item overheads for op seq/event: ' ||
409 l_chargeTbl(l_count).operation_seq_num,
410 x_returnStatus => l_returnStatus);
411 end if;
412
413 insert into wip_cost_txn_interface(
414 last_update_date,
415 last_updated_by,
416 creation_date,
417 created_by,
418 last_update_login,
419 request_id,
420 program_application_id,
421 program_id,
422 program_update_date,
423 group_id,
424 source_code,
425 source_line_id,
426 process_phase,
427 process_status,
428 transaction_type,
429 organization_id,
430 organization_code, --bug 5231366
431 wip_entity_id,
432 entity_type,
433 primary_item_id,
434 line_id,
435 transaction_date,
436 acct_period_id,
437 operation_seq_num,
438 department_id,
439 department_code,
440 basis_type,
441 autocharge_type,
442 transaction_quantity,
443 transaction_uom,
444 primary_quantity,
445 primary_uom,
446 reason_id,
447 reference,
448 completion_transaction_id,
449 project_id,
450 task_id)
451 select
452 sysdate,
453 mmtt.last_updated_by,
454 sysdate,
455 mmtt.created_by,
456 mmtt.last_update_login,
457 mmtt.request_id,
458 mmtt.program_application_id,
459 mmtt.program_id,
460 nvl(mmtt.program_update_date, sysdate),
461 null, -- group id
462 mmtt.source_code,
463 mmtt.source_line_id,
464 2, -- process phase: resource processing
465 1, -- process status: pending
466 2, -- transaction type: overhead
467 mmtt.organization_id,
468 l_org_code, --bug 5231366
469 mmtt.transaction_source_id,
470 4,
471 mmtt.inventory_item_id,
472 mmtt.repetitive_line_id,
473 mmtt.transaction_date,
474 mmtt.acct_period_id,
475 l_chargeTbl(l_count).operation_seq_num,
476 bos.department_id,
477 bd.department_code,
478 1, -- per item
479 1, -- wip move
480 mmtt.transaction_quantity,
481 mmtt.transaction_uom,
482 mmtt.primary_quantity,
483 mmtt.item_primary_uom_code,
484 mmtt.reason_id,
485 mmtt.transaction_reference,
486 mmtt.completion_transaction_id,
487 mmtt.project_id,
488 mmtt.task_id
489 from
490 bom_departments bd,
491 bom_operation_sequences bos,
492 wip_flow_schedules wfs,
493 mtl_material_transactions_temp mmtt
494 where bos.operation_sequence_id =
495 l_chargeTbl(l_count).operation_sequence_id
496 and mmtt.transaction_temp_id = p_txnTempID
497 and bd.organization_id = mmtt.organization_id
498 and bd.department_id = bos.department_id
499 and wfs.organization_id = mmtt.organization_id
500 and wfs.wip_entity_id = mmtt.transaction_source_id;
501
502 if (l_logLevel <= wip_constants.full_logging) then
503 wip_logger.log(p_msg => 'Inserting lot overheads for op seq/event: ' ||
504 l_chargeTbl(l_count).operation_seq_num,
505 x_returnStatus => l_returnStatus);
506 end if;
507
508 insert into wip_cost_txn_interface(
509 last_update_date,
510 last_updated_by,
511 creation_date,
512 created_by,
513 last_update_login,
514 request_id,
515 program_application_id,
516 program_id,
517 program_update_date,
518 group_id,
519 source_code,
520 source_line_id,
521 process_phase,
522 process_status,
523 transaction_type,
524 organization_id,
525 organization_code, --bug 5231366
526 wip_entity_id,
527 entity_type,
528 primary_item_id,
529 line_id,
530 transaction_date,
531 acct_period_id,
532 operation_seq_num,
533 department_id,
534 department_code,
535 basis_type,
536 autocharge_type,
537 transaction_quantity,
538 transaction_uom,
539 primary_quantity,
540 primary_uom,
541 reason_id,
542 reference,
543 completion_transaction_id,
544 project_id,
545 task_id)
546 select
547 sysdate,
548 mmtt.last_updated_by,
549 sysdate,
550 mmtt.created_by,
551 mmtt.last_update_login,
552 mmtt.request_id,
553 mmtt.program_application_id,
554 mmtt.program_id,
555 nvl(mmtt.program_update_date, sysdate),
556 null, -- group id
557 mmtt.source_code,
558 mmtt.source_line_id,
559 2, -- process phase: resource processing
560 1, -- process status: pending
561 2, -- transaction type: overhead
562 mmtt.organization_id,
563 l_org_code, --bug 5231366
564 mmtt.transaction_source_id,
565 4,
566 mmtt.inventory_item_id,
567 mmtt.repetitive_line_id,
568 mmtt.transaction_date,
569 mmtt.acct_period_id,
570 l_chargeTbl(l_count).operation_seq_num,
571 bos.department_id,
572 bd.department_code,
573 2, -- lot based
574 1, -- wip move
575 decode(mmtt.transaction_action_id,
576 31, 1,
577 32, -1,
578 30, decode(nvl(wfs.quantity_completed, 0), 0, sign(mmtt.primary_quantity), 0)),
579 mmtt.transaction_uom,
580 decode(mmtt.transaction_action_id,
581 31, 1,
582 32, -1,
583 30, decode(nvl(wfs.quantity_completed, 0), 0, sign(mmtt.primary_quantity), 0)),
584 mmtt.item_primary_uom_code,
585 mmtt.reason_id,
586 mmtt.transaction_reference,
587 mmtt.completion_transaction_id,
588 mmtt.project_id,
589 mmtt.task_id
590 from
591 bom_departments bd,
592 bom_operation_sequences bos,
593 wip_flow_schedules wfs,
594 mtl_material_transactions_temp mmtt
595 where bos.operation_sequence_id =
596 l_chargeTbl(l_count).operation_sequence_id
597 and mmtt.transaction_temp_id = p_txnTempID
598 and wfs.organization_id = mmtt.organization_id
599 and wfs.wip_entity_id = mmtt.transaction_source_id
600 and decode(nvl(wfs.quantity_completed, 0), 0, 1, 0) <> 0
601 and decode(wfs.scheduled_flag, 1, mmtt.transaction_action_id, 0) <> 30
602 -- lot based overheads are not charged for scheduled cfm scrap
603 and bd.organization_id = mmtt.organization_id
604 and bd.department_id = bos.department_id;
605
606
607 l_count := l_chargeTbl.next(l_count);
608 end loop;
609
610 -- now charge the phantom routing is applicable
611 chargePhantomResource(p_txnTempID, l_orgID, l_effDate, true, x_returnStatus);
612
613 if (l_logLevel <= wip_constants.trace_logging) then
614 wip_logger.exitPoint(p_procName => 'wip_flowResCharge.chargeResourceAndOverhead',
615 p_procReturnStatus => x_returnStatus,
616 p_msg => 'Exit resource charge either successfully or failed in charging phantom!',
617 x_returnStatus => l_returnStatus); --discard logging return status
618 end if;
619 exception
620 when others then
621 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
622 if (l_logLevel <= wip_constants.trace_logging) then
623 wip_logger.exitPoint(p_procName => 'wip_flowResCharge.chargeResourceAndOverhead',
624 p_procReturnStatus => x_returnStatus,
625 p_msg => 'unexpected error: ' || SQLERRM,
626 x_returnStatus => l_returnStatus);
627 end if;
628 end chargeResourceAndOverhead;
629
630 procedure chargePhantomResource(p_txnTempID in number,
631 p_orgID in number,
632 p_effDate in date,
633 p_hasRouting in boolean,
634 x_returnStatus out nocopy varchar2) is
635 -- cursor to get all phantoms for completion. The phantoms must be
636 -- in a count-point operation and also an ECO implemented operation
637 -- we already check the op num/event effecvitiy when exploding the bom.
638 cursor phantoms_c1 is
639 /* Bug 4545130; FP 4257633 Add distinct to select clause, and also
640 change wfs.primary_item_id, wfs.organization_id to
641 mmtt.inventory_item_id and mmtt.organization_id in join condition
642 of bor.assembly_item_id, bor.organization_id
643 */
644 select distinct
645 mmtt.inventory_item_id phantom_item_id,
646 mmtt.operation_seq_num*(-1) operation_seq_num,
647 mmtt.transaction_temp_id,
648 mmtt.completion_transaction_id,
649 mmtt.repetitive_line_id
650 from mtl_material_transactions_temp mmtt,
651 wip_flow_schedules wfs,
652 bom_operational_routings bor,
653 bom_operation_sequences bos
654 where mmtt.completion_transaction_id =
655 (select mmtt2.completion_transaction_id
656 from mtl_material_transactions_temp mmtt2
657 where mmtt2.transaction_temp_id = p_txnTempID)
658 and mmtt.transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
659 WIP_CONSTANTS.RETCOMP_ACTION,
660 WIP_CONSTANTS.ISSNEGC_ACTION,
661 WIP_CONSTANTS.RETNEGC_ACTION)
662 and mmtt.operation_seq_num < 0
663 and mmtt.process_flag = 'Y'
664 and mmtt.transaction_source_type_id = 5
665 and wfs.organization_id = mmtt.organization_id
666 and wfs.wip_entity_id = mmtt.transaction_source_id
667 /* Bug 4545130; FP 4257633 */
668 and bor.assembly_item_id = mmtt.inventory_item_id
669 and bor.organization_id = mmtt.organization_id
670 and nvl(bor.alternate_routing_designator, 'NONE') =
671 nvl(mmtt.alternate_routing_designator, 'NONE')
672 and bor.pending_from_ecn is null
673 -- for implement ECO the routing must be not pending from ecn
674 and bos.routing_sequence_id = bor.common_routing_sequence_id
675 /* Bug 4545088; FP 4542270; Comment out following operation_seq_num cnd */
676 /* and bos.operation_seq_num = mmtt.operation_seq_num*(-1) */
677 and bos.count_point_type in (1, 2);
678
679 cursor phantoms_c2 is
680 select mmtt.inventory_item_id phantom_item_id,
681 mmtt.operation_seq_num*(-1) operation_seq_num,
682 mmtt.transaction_temp_id,
683 mmtt.completion_transaction_id,
684 mmtt.repetitive_line_id
685 from mtl_material_transactions_temp mmtt
686 where mmtt.completion_transaction_id =
687 (select mmtt2.completion_transaction_id
688 from mtl_material_transactions_temp mmtt2
689 where mmtt2.transaction_temp_id = p_txnTempID)
690 and mmtt.transaction_action_id in (WIP_CONSTANTS.ISSCOMP_ACTION,
691 WIP_CONSTANTS.RETCOMP_ACTION,
692 WIP_CONSTANTS.ISSNEGC_ACTION,
693 WIP_CONSTANTS.RETNEGC_ACTION)
694 and mmtt.operation_seq_num = -1
695 and mmtt.transaction_source_type_id = 5
696 and mmtt.process_flag = 'Y';
697
698 l_logLevel number := fnd_log.g_current_runtime_level;
699 l_returnStatus varchar2(1);
700 l_params wip_logger.param_tbl_t;
701
702 l_compID number;
703 begin
704 x_returnStatus := fnd_api.g_ret_sts_success;
705 l_params(1).paramName := 'p_txnTempID';
706 l_params(1).paramValue := p_txnTempID;
707 l_params(2).paramName := 'p_orgID';
708 l_params(2).paramValue := p_orgID;
709 l_params(3).paramName := 'p_hasRouting';
710 if ( p_hasRouting ) then
711 l_params(3).paramValue := 'True';
712 else
713 l_params(3).paramValue := 'False';
714 end if;
715 if (l_logLevel <= wip_constants.trace_logging) then
716 wip_logger.entryPoint(p_procName => 'wip_flowResCharge.chargePhantomResource',
717 p_params => l_params,
718 x_returnStatus => x_returnStatus);
719 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
720 raise fnd_api.g_exc_unexpected_error;
721 end if;
722 end if;
723
724 -- now charge the phantom routing is applicable
725 if ( wip_globals.use_phantom_routings(p_orgID) = WIP_CONSTANTS.YES) then
726 if ( p_hasRouting ) then
727 for phantoms_rec in phantoms_c1 loop
728 if (l_logLevel <= wip_constants.full_logging) then
729 wip_logger.log(p_msg => 'Charging phantom: ' ||
730 phantoms_rec.phantom_item_id || ' at op: ' ||
731 phantoms_rec.operation_seq_num || ' tmp id: ' ||
732 phantoms_rec.transaction_temp_id,
733 x_returnStatus => l_returnStatus);
734 end if;
735
736 if ( wip_explode_phantom_rtgs.charge_flow_resource_ovhd(
737 p_org_id => p_orgID,
738 p_phantom_item_id => phantoms_rec.phantom_item_id,
739 p_op_seq_num => phantoms_rec.operation_seq_num,
740 p_comp_txn_id => phantoms_rec.completion_transaction_id,
741 p_txn_temp_id => phantoms_rec.transaction_temp_id,
742 p_line_id => phantoms_rec.repetitive_line_id,
743 p_rtg_rev_date => to_char(p_effDate, 'YYYY/MM/DD HH24:MI')) = 0 ) then
744 raise fnd_api.g_exc_unexpected_error;
745 end if;
746 end loop;
747 else -- it doens't have a routing, use the second cursor
748 for phantoms_rec in phantoms_c2 loop
749 if (l_logLevel <= wip_constants.full_logging) then
750 wip_logger.log(p_msg => 'Charging phantom: ' ||
751 phantoms_rec.phantom_item_id || ' at op: ' ||
752 phantoms_rec.operation_seq_num || ' tmp id: ' ||
753 phantoms_rec.transaction_temp_id,
754 x_returnStatus => l_returnStatus);
755 end if;
756
757 if ( wip_explode_phantom_rtgs.charge_flow_resource_ovhd(
758 p_org_id => p_orgID,
759 p_phantom_item_id => phantoms_rec.phantom_item_id,
760 p_op_seq_num => phantoms_rec.operation_seq_num,
761 p_comp_txn_id => phantoms_rec.completion_transaction_id,
762 p_txn_temp_id => phantoms_rec.transaction_temp_id,
763 p_line_id => phantoms_rec.repetitive_line_id,
764 p_rtg_rev_date => to_char(p_effDate, 'YYYY/MM/DD HH24:MI')) = 0 ) then
765 raise fnd_api.g_exc_unexpected_error;
766 end if;
767 end loop;
768 end if;
769 end if;
770
771
772 exception
773 when others then
774 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
775 if (l_logLevel <= wip_constants.trace_logging) then
776 wip_logger.exitPoint(p_procName => 'wip_flowResCharge.chargePhantomResource',
777 p_procReturnStatus => x_returnStatus,
778 p_msg => 'unexpected error: ' || SQLERRM,
779 x_returnStatus => l_returnStatus);
780 end if;
781 end chargePhantomResource;
782
783 end wip_flowResCharge;