[Home] [Help]
PACKAGE BODY: APPS.CSTPPINV
Source
1 PACKAGE BODY CSTPPINV AS
2 /* $Header: CSTPINVB.pls 120.27.12010000.3 2008/10/29 13:00:19 lchevala ship $ */
3
4 l_debug CONSTANT VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
5
6
7 PROCEDURE cost_inv_txn (
8 i_pac_period_id IN NUMBER,
9 i_legal_entity IN NUMBER,
10 i_cost_type_id IN NUMBER,
11 i_cost_group_id IN NUMBER,
12 i_cost_method IN NUMBER,
13 i_txn_id IN NUMBER,
14 i_txn_action_id IN NUMBER,
15 i_txn_src_type_id IN NUMBER,
16 i_item_id IN NUMBER,
17 i_txn_qty IN NUMBER,
18 i_txn_org_id IN NUMBER,
19 i_txfr_org_id IN NUMBER,
20 i_subinventory_code IN VARCHAR2,
21 i_exp_flag IN NUMBER,
22 i_exp_item IN NUMBER,
23 i_pac_rates_id IN NUMBER,
24 i_process_group IN NUMBER,
25 i_master_org_id IN NUMBER,
26 i_uom_control IN NUMBER,
27 i_user_id IN NUMBER,
28 i_login_id IN NUMBER,
29 i_request_id IN NUMBER,
30 i_prog_id IN NUMBER,
31 i_prog_appl_id IN NUMBER,
32 i_txn_category IN NUMBER,
33 i_transfer_price_pd IN NUMBER, -- := 0 INVCONV for process-discrete txfer
34 o_err_num OUT NOCOPY NUMBER,
35 o_err_code OUT NOCOPY VARCHAR2,
36 o_err_msg OUT NOCOPY VARCHAR2
37 )
38 IS
39 l_err_num NUMBER;
40 l_err_code VARCHAR2(240);
41 l_err_msg VARCHAR2(240);
42 l_cost_layer_id NUMBER;
43 l_quantity_layer_id NUMBER;
44 l_txn_cost_group_id NUMBER;
45 l_txfr_cost_group_id NUMBER;
46 l_fob_point NUMBER;
47 l_count NUMBER;
48 l_txn_cost NUMBER;
49 l_um_rate NUMBER;
50 l_converted_txn_qty NUMBER;
51 l_dropship_type_code NUMBER;
52 l_parent_ds_type_code NUMBER; --These 3 variables added for bug 3907495
53 l_parent_rcv_txn_id NUMBER;
54 l_grandpa_rcv_txn_id NUMBER;
55 l_already_processed NUMBER;
56 l_stmt_num NUMBER := 0;
57 PROCESS_ERROR EXCEPTION;
58
59 -- Revenue / COGS Matching Enhancement
60 l_prior_period_shipment NUMBER := 0;
61 l_so_line_id NUMBER := NULL;
62 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.cost_inv_txn';
66
67 begin
68 l_err_num := 0;
69 l_err_code := '';
70 l_err_msg := '';
71
72 IF l_debug = 'Y' THEN
73 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
74 || ' transaction_id: ' || i_txn_id
75 || ', cost group id: ' || i_cost_group_id
76 || ', cost type id: ' || i_cost_type_id
77 || ', pac period id: ' || i_pac_period_id
78 || ', legal entity: ' || i_legal_entity);
79 END IF;
80
81 l_stmt_num := 10;
82 -- check the existence of layer
83 CSTPPCLM.layer_id(i_pac_period_id, i_legal_entity, i_item_id,
84 i_cost_group_id, l_cost_layer_id, l_quantity_layer_id,
85 l_err_num, l_err_code, l_err_msg);
86 IF (l_err_num <> 0) THEN
87 raise PROCESS_ERROR;
88 END IF;
89
90 l_stmt_num := 20;
91 -- create a layer if not exist
92 IF (l_cost_layer_id = 0) THEN
93 CSTPPCLM.create_layer(i_pac_period_id, i_legal_entity, i_item_id,
94 i_cost_group_id, i_user_id, i_login_id, i_request_id,
95 i_prog_id, i_prog_appl_id,
96 l_cost_layer_id, l_quantity_layer_id,
97 l_err_num, l_err_code, l_err_msg);
98
99 IF (l_err_num <> 0) THEN
100 raise PROCESS_ERROR;
101 END IF;
102 END IF;
103
104 -- Obtain some parameters needed by cost processor
105
106 IF (i_txn_action_id IN (3,12,21,15,22)) THEN -- normal processing -- INVCONV
107
108 l_stmt_num := 30;
109 SELECT NVL(MAX(cost_group_id),-1)
110 INTO l_txn_cost_group_id
111 FROM cst_cost_group_assignments
112 WHERE organization_id = i_txn_org_id;
113
114 l_stmt_num := 40;
115 SELECT NVL(MAX(cost_group_id),-1)
116 INTO l_txfr_cost_group_id
117 FROM cst_cost_group_assignments
118 WHERE organization_id = i_txfr_org_id;
119
120 -- Modified for fob stamping project
121 l_stmt_num := 50;
122 IF (i_txn_action_id in( 21,22)) THEN -- INVCONV sikhanna
123 SELECT nvl(MMT.fob_point, MIP.fob_point)
124 INTO l_fob_point
125 FROM mtl_interorg_parameters MIP, mtl_material_transactions MMT
126 WHERE MIP.from_organization_id = i_txn_org_id
127 AND MIP.to_organization_id = i_txfr_org_id
128 AND MMT.transaction_id = i_txn_id;
129 ELSIF (i_txn_action_id in (12,15)) THEN
130 SELECT nvl(MMT.fob_point, MIP.fob_point)
131 INTO l_fob_point
132 FROM mtl_interorg_parameters MIP, mtl_material_transactions MMT
133 WHERE MIP.from_organization_id = i_txfr_org_id
134 AND MIP.to_organization_id = i_txn_org_id
135 AND MMT.transaction_id = i_txn_id;
136 ELSE
137 l_fob_point := 0;
138 END IF;
139 ELSE
140 l_txn_cost_group_id := i_cost_group_id;
141 l_txfr_cost_group_id := -1;
142 l_fob_point := 0;
143 END IF;
144
145 /*---------------------------------------------------------------------------
146 | Get the um conversion rate.
147 | uom_of_txn_qty = l_um_rate * uom_of_converted_txn_qty
148 | e.g. : DZ = 12 EACH
149 ---------------------------------------------------------------------------*/
150 l_stmt_num := 55;
151
152 get_um_rate(i_txn_org_id, i_master_org_id, l_txn_cost_group_id,
153 l_txfr_cost_group_id,
154 i_txn_action_id, i_item_id, i_uom_control,
155 i_user_id, i_login_id, i_request_id, i_prog_id, i_prog_appl_id,
156 l_um_rate, l_err_num, l_err_code, l_err_msg);
157 IF (l_err_num <> 0) THEN
158 raise PROCESS_ERROR;
159 END IF;
160
161 l_converted_txn_qty := i_txn_qty * l_um_rate;
162
163 /* Update mmt with quantity in the master org um */
164 /* Bug 6751847 fix to prevent update from both shipping as well as
165 receiving cost groups,to avoid lock and hold when run in
166 parallel for multiple Cost Groups */
167
168 UPDATE mtl_material_transactions
169 SET periodic_primary_quantity = l_converted_txn_qty
170 WHERE transaction_id = i_txn_id
171 AND organization_id = i_txn_org_id
172 AND EXISTS ( SELECT 'x'
173 FROM cst_cost_group_assignments ccga
174 WHERE ccga.cost_group_id = i_cost_group_id
175 AND ccga.organization_id = i_txn_org_id);
176
177
178 /*---------------------------------------------------------------------------
179 | Insert into mptcd if necessary.
180 | Five cases :
181 | - Interorg Accross CG with ownership change (group 1).
182 | Calculate the txn cost using data from mtl_transaction_accounts,
183 | , then insert that into mptcd elementally.
184 | - PO related txns (group 1 and i_txn_src_type_id = 1 and i_txn_action_id <> 6).
185 | Obtain the transaction cost from po table, then insert that into mptcd.
186 | i_txn_action_id of 6 is an ownership txfr, which has no acquisition cost.
187 | Therefore these fall into "Other cost owned txns" below.
188 | - Periodic Cost Update.
189 | Update the period id into current period id. This value might be null
190 | when it's inserted by the form, because the period might not yet
191 | open at the time of form insertion.
192 | - Other cost owned txns (group 1).
193 | Copy from mctcd into mptcd.
194 | - Other txns
195 | No insertion into mptcd is done.
196 ---------------------------------------------------------------------------*/
197 IF (i_process_group = 1 AND i_txn_action_id IN (3,12,21,15)) THEN -- INVCONV sikhanna 22 (removed)
198 l_stmt_num := 60;
199 get_interorg_cost(i_legal_entity, i_pac_period_id, i_cost_type_id,
200 i_cost_group_id,l_txn_cost_group_id, l_txfr_cost_group_id,
201 i_txn_id, i_txn_action_id, i_item_id, l_converted_txn_qty,
202 i_txn_org_id,i_txfr_org_id, i_user_id, i_login_id,
203 i_request_id, i_prog_id, i_prog_appl_id, i_transfer_price_pd, -- INVCONV sikhanna
204 l_err_num, l_err_code, l_err_msg);
205 IF (l_err_num <> 0) THEN
206 raise PROCESS_ERROR;
207 END IF;
208
209 ELSIF (i_process_group = 1 AND i_txn_src_type_id = 1 OR
210 (i_txn_action_id = 6 AND i_txn_src_type_id = 13)) THEN
211
212 l_stmt_num := 63;
213
214 /* propogation of bug 4385294.Consigned ownership txfrs do not have rcv_txn_id stamped on them */
215
216 IF (i_txn_action_id = 6) then
217
218 Select mmt.transaction_cost into l_txn_cost
219 from mtl_material_transactions mmt
220 where mmt.transaction_id = i_txn_id ;
221
222 ELSE
223
224 l_stmt_num := 65;
225
226 -- Patchset J change: External DropShipments do not have any acquisition cost,
227 -- they come in at PO price
228
229 select nvl(rt.dropship_type_code, 3), mmt.transaction_cost, rt.parent_transaction_id
230 into l_dropship_type_code, l_txn_cost, l_parent_rcv_txn_id
231 from rcv_transactions rt, mtl_material_transactions mmt
232 where mmt.transaction_id = i_txn_id
233 and mmt.rcv_transaction_id = rt.transaction_id;
234
235 END IF; /* end of check for consigned txfrs */
236
237 -- added for bug 3907495
238 -- For RTVs corresponding to true dropship receipts, there is no acquisition
239 -- cost. In this case, we should get the hypothetical acquisition cost from
240 -- the unit_price of the forward flow Receive or Match (whatever the parent
241 -- is), and make sure we don't call the get_acq_cost procedure.
242 if (i_txn_action_id = 1 and i_txn_src_type_id = 1) then -- RTVs
243 l_stmt_num := 66;
244 --"transaction_type = 'MATCH'" is added for bug 4892685, for non-dropship RTVs (unordered->Match->Deliver->RTV)
245 select dropship_type_code, transaction_id
246 into l_parent_ds_type_code, l_grandpa_rcv_txn_id
247 from rcv_transactions
248 where (transaction_type = 'RECEIVE' -- 'MATCH' cannot be parent because
249 and parent_transaction_id = -1)
250 OR transaction_type = 'MATCH'-- there won't be one in a true dropship
251 start with transaction_id = l_parent_rcv_txn_id
252 connect by transaction_id = prior parent_transaction_id;
253
254 if (l_parent_ds_type_code = 1) then -- external drop ship in the forward flow
255 l_stmt_num := 67;
256
257 BEGIN
258 select unit_price
259 into l_txn_cost
260 from rcv_accounting_events
261 where rcv_transaction_id = l_grandpa_rcv_txn_id;
262 EXCEPTION
263 when others then
264 NULL; -- if no row exists in RAE, just use MMT.transaction_cost
265 END;
266
267 l_dropship_type_code := 1;
268 end if;
269 end if;
270 -- end of additions for bug 3907495
271
272 l_stmt_num := 70;
273
274 /* FP BUG 5845861 fix - For External Dropshipments also include code 2 */
275 if (l_dropship_type_code in (2,3) and i_txn_action_id <> 6 AND nvl(l_parent_ds_type_code,3) = 3) then
276 CSTPPACQ.get_acq_cost(i_cost_group_id, i_txn_id, i_cost_type_id, 'I',
277 l_txn_cost, l_err_num, l_err_code, l_err_msg);
278 IF (l_err_num <> 0) THEN
279 raise PROCESS_ERROR;
280 END IF;
281 end if;
282
283 INSERT INTO mtl_pac_txn_cost_details (
284 transaction_id,
285 pac_period_id,
286 cost_type_id,
287 cost_group_id,
288 cost_element_id,
289 level_type,
290 inventory_item_id,
291 transaction_cost,
292 last_update_date,
293 last_updated_by,
294 creation_date,
295 created_by,
296 request_id,
297 program_application_id,
298 program_id,
299 program_update_date,
300 last_update_login)
301 VALUES(
302 i_txn_id,
303 i_pac_period_id,
304 i_cost_type_id,
305 i_cost_group_id,
306 1,
307 1,
308 i_item_id,
309 l_txn_cost/l_um_rate,
310 SYSDATE,
311 i_user_id,
312 SYSDATE,
313 i_user_id,
314 i_request_id,
315 i_prog_appl_id,
316 i_prog_id,
317 SYSDATE,
318 i_login_id);
319
320 ELSIF (i_process_group = 1 AND i_txn_action_id = 24) THEN
321
322 UPDATE mtl_pac_txn_cost_details
323 SET pac_period_id = i_pac_period_id,
324 last_update_date = SYSDATE
325 WHERE transaction_id = i_txn_id
326 AND cost_type_id = i_cost_type_id
327 AND cost_group_id = i_cost_group_id;
328
329 ELSIF (i_process_group = 1 AND i_txn_action_id = 27 AND i_txn_src_type_id = 12) THEN
330 -- Revenue / COGS Matching enhancement
331 -- If this RMA references a sales order issue, then this query
332 -- will return the line ID of that sales order. Else NULL
333 l_stmt_num := 74;
334 SELECT RMA.reference_line_id
335 INTO l_so_line_id
336 FROM mtl_material_transactions MMT,
337 oe_order_lines_all RMA
338 WHERE MMT.transaction_id = i_txn_id
339 AND RMA.line_id = MMT.trx_source_line_id;
340
341 -- If the RMA references a sales order issue, and if the
342 -- sales order is in a prior period, this will return # > 0
343 IF (l_so_line_id IS NOT NULL) THEN
344 l_stmt_num := 75;
345 SELECT count(*)
346 INTO l_prior_period_shipment
347 FROM cst_pac_periods cpp,
348 oe_order_lines_all SO
349 WHERE cpp.pac_period_id = i_pac_period_id
350 AND cpp.period_start_date > SO.ACTUAL_SHIPMENT_DATE
351 AND SO.line_id = l_so_line_id;
352
353 IF (l_prior_period_shipment > 0) THEN
354 -- check if the SO has been processed in this PAC cost type
355 l_stmt_num := 76;
356 SELECT count(*)
357 INTO l_already_processed
358 FROM cst_revenue_cogs_match_lines
359 WHERE pac_cost_type_id = i_cost_type_id
360 AND cogs_om_line_id = l_so_line_id;
361
362 IF (l_already_processed >0) THEN
363 l_stmt_num := 77;
364 -- Use the PAC cost of the Sales Order Issue
365 INSERT INTO mtl_pac_txn_cost_details (
366 transaction_id,
367 pac_period_id,
368 cost_type_id,
369 cost_group_id,
370 cost_element_id,
371 level_type,
372 inventory_item_id,
373 transaction_cost,
374 last_update_date,
375 last_updated_by,
376 creation_date,
377 created_by,
378 request_id,
379 program_application_id,
380 program_id,
381 program_update_date,
382 last_update_login
383 )
384 SELECT i_txn_id,
385 i_pac_period_id,
386 i_cost_type_id,
387 i_cost_group_id,
388 MPACD.cost_element_id,
389 MPACD.level_type,
390 i_item_id,
391 (SUM(MMT.primary_quantity*MPACD.actual_cost)/SUM(MMT.primary_quantity))/l_um_rate,
392 SYSDATE,
393 i_user_id,
394 SYSDATE,
395 i_user_id,
396 i_request_id,
397 i_prog_appl_id,
398 i_prog_id,
399 SYSDATE,
400 i_login_id
401 FROM mtl_material_transactions MMT,
402 mtl_pac_actual_cost_details MPACD
403 WHERE MMT.transaction_action_id = 1
404 AND MMT.transaction_source_type_id = 2
405 AND MMT.organization_id = i_txn_org_id
406 AND MMT.inventory_item_id = i_item_id
407 AND MMT.trx_source_line_id = l_so_line_id
408 AND MPACD.transaction_id = MMT.transaction_id
409 AND MPACD.cost_type_id = i_cost_type_id
410 GROUP BY
411 MPACD.cost_element_id,
412 MPACD.level_type;
413 ELSE
414 -- Create MPTCD for this cost-owned RMA receipt using the perpetual cost
415 l_stmt_num := 78;
416
417 INSERT INTO mtl_pac_txn_cost_details (
418 transaction_id,
419 pac_period_id,
420 cost_type_id,
421 cost_group_id,
422 cost_element_id,
423 level_type,
424 inventory_item_id,
425 transaction_cost,
426 last_update_date,
427 last_updated_by,
428 creation_date,
429 created_by,
430 request_id,
431 program_application_id,
432 program_id,
433 program_update_date,
434 last_update_login
435 )
436 SELECT i_txn_id,
437 i_pac_period_id,
438 i_cost_type_id,
439 i_cost_group_id,
440 MCACD.cost_element_id,
441 MCACD.level_type,
442 i_item_id,
443 (SUM(MMT.primary_quantity*MCACD.actual_cost)/SUM(MMT.primary_quantity))/l_um_rate,
444 SYSDATE,
445 i_user_id,
446 SYSDATE,
447 i_user_id,
448 i_request_id,
449 i_prog_appl_id,
450 i_prog_id,
451 SYSDATE,
452 i_login_id
453 FROM mtl_material_transactions MMT,
454 mtl_cst_actual_cost_details MCACD
455 WHERE MMT.transaction_action_id = 1
456 AND MMT.transaction_source_type_id = 2
457 AND MMT.organization_id = i_txn_org_id
458 AND MMT.inventory_item_id = i_item_id
459 AND MMT.trx_source_line_id = l_so_line_id
460 AND MCACD.transaction_id = MMT.transaction_id
461 GROUP BY
462 MCACD.cost_element_id,
463 MCACD.level_type;
464 END IF;
465 ELSE -- RMA points to a sales order in the same period
466 return; -- process the RMA the 2nd time it gets passed up - cost derived receipts
467 END IF;
468 ELSE -- RMA does not reference a sales order
469 return; -- process the RMA the 2nd time it gets passed up - cost derived receipts
470 END IF;
471
472 ELSIF (i_process_group = 2 AND i_txn_action_id = 27 AND i_txn_src_type_id = 12) THEN
473 -- If transaction has been already processed during first pass (i_process_group = 1)
474 -- then return without further processing
475 -- performance bug fix 6751847 fix: removed cst_pc_txn_history
476 -- instead of use mtl_pac_txn_cost_details
477 l_stmt_num := 79;
478 SELECT count(1)
479 INTO l_already_processed
480 FROM mtl_pac_txn_cost_details
481 WHERE pac_period_id = i_pac_period_id
482 AND cost_group_id = i_cost_group_id
483 AND transaction_id = i_txn_id;
484
485 IF (l_already_processed > 0) THEN
486 return;
487 END IF;
488
489 ELSIF (i_process_group = 1) THEN
490 l_stmt_num := 80;
491 INSERT INTO mtl_pac_txn_cost_details (
492 transaction_id,
493 pac_period_id,
494 cost_type_id,
495 cost_group_id,
496 cost_element_id,
497 level_type,
498 inventory_item_id,
499 transaction_cost,
500 last_update_date,
501 last_updated_by,
502 creation_date,
503 created_by,
504 request_id,
505 program_application_id,
506 program_id,
507 program_update_date,
508 last_update_login)
509 SELECT
510 transaction_id,
511 i_pac_period_id,
512 i_cost_type_id,
513 i_cost_group_id,
514 cost_element_id,
515 level_type,
516 inventory_item_id,
517 transaction_cost/l_um_rate,
518 SYSDATE,
519 i_user_id,
520 SYSDATE,
521 i_user_id,
522 i_request_id,
523 i_prog_appl_id,
524 i_prog_id,
525 SYSDATE,
526 i_login_id
527 FROM
528 mtl_cst_txn_cost_details mctcd
529 WHERE
530 mctcd.transaction_id = i_txn_id;
531 END IF;
532
533 CSTPPWAC.cost_processor( i_legal_entity, i_pac_period_id, i_txn_org_id,
534 i_cost_group_id, l_txn_cost_group_id,
535 l_txfr_cost_group_id, i_cost_type_id, i_cost_method,
536 i_process_group, i_txn_id, l_quantity_layer_id,
537 l_cost_layer_id, i_pac_rates_id, i_item_id,
538 l_converted_txn_qty, i_txn_action_id,
539 i_txn_src_type_id,
540 l_fob_point, i_exp_item, i_exp_flag, -1, i_user_id,
541 i_login_id, i_request_id, i_prog_appl_id, i_prog_id,
542 i_txn_category, l_err_num, l_err_code, l_err_msg);
543
544 IF (l_err_num <> 0) THEN
545 raise PROCESS_ERROR;
546 END IF;
547
548 IF (i_txn_src_type_id = 2 AND i_txn_action_id = 1) THEN -- Revenue / COGS Matching
549 -- cost derived sales order issue
550 CST_RevenueCogsMatch_PVT.Insert_PacSoIssue( p_api_version => 1.0,
551 x_return_status => l_return_status,
552 x_msg_count => l_msg_count,
553 x_msg_data => l_msg_data,
554 p_transaction_id => i_txn_id,
555 p_layer_id => l_cost_layer_id,
556 p_cost_type_id => i_cost_type_id,
557 p_cost_group_id => i_cost_group_id,
558 p_user_id => i_user_id,
559 p_login_id => i_login_id,
560 p_request_id => i_request_id,
561 p_pgm_app_id => i_prog_appl_id,
562 p_pgm_id => i_prog_id);
563
564 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
565 l_err_num := -1;
566 IF (l_msg_count = 1) THEN
567 l_err_msg := substr(l_msg_data,1,240);
568 ELSE
569 l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_PacSoIssue()';
570 END IF;
571 raise PROCESS_ERROR;
572 END IF;
573 END IF;
574
575 l_stmt_num := 200;
576 IF l_debug = 'Y' THEN
577 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': return >>');
578 END IF;
579
580
581 EXCEPTION
582 when PROCESS_ERROR then
583 o_err_num := l_err_num;
584 o_err_code := l_err_code;
585 o_err_msg := 'CSTPPINV.COST_INV_TXN:' || l_err_msg;
586 when OTHERS then
587 o_err_num := SQLCODE;
588 o_err_msg := 'CSTPPINV.COST_INV_TXN: (' || to_char(l_stmt_num) || '): '
589 || substr(SQLERRM,1,150);
590
591 end cost_inv_txn;
592
593
594 /*---------------------------------------------------------------------------
595 | Procedure get_interorg_cost()
596 |
597 | This routine is called for interorg transactions involving an ownership
598 | change.
599 |
600 | There are 3 cases in which the ownership changes, and rows need
601 | to be inserted into MPTCD :
602 | 1. Direct Transfer - Rcv txn , processed by the Rcv CG
603 | 2. FOB Shipment - Shipment txn , processed by the Rcv CG
604 | 3. FOB Receipt - Rcv txn , processed by the Rcv CG
605 |
606 | Algorithm followed for inserting into MPTCD:
607 | |-IF (FOB SHIPMENT/RECEIPT and Internal Order with Transfer Pricing) THEN
608 | | |- Create MPTCD (cost element 1) using the transfer price from MMT
609 | | | stamped on shipment txn)
610 | |
611 | |-ELSE --FOB or DIRECT interorg
612 | | |-IF (both send and receiving CGs implemented in same LE/CT) THEN
613 | | | |- IF (PACP iterative process was used) THEN
614 | | | | |- create MPTCD with sending CG cost from CPIC
615 | | | | |- if sending CG cost from CPIC not available, default to prior period cost
616 | | | | |- if no prior period cost exists, default to perpetual shipment cost
617 | | | |- ELSE --PACP not used
618 | | | | |- create MPTCD w/sending CG PWAC cost in prior period
619 | | | | |- if no prior period cost exists, default to perpetual shipment cost
620 | | | |- END IF;
621 | | |- ELSE --send and receiving not implemented in same LE/CT
622 | | | |- create MPTCD with sending org perpetual shipment cost
623 | | |- END IF;
624 | |
625 | |-END IF;
626
627 | Appropriate currency conversion done before stamping into MPTCD.
628 |
629 ---------------------------------------------------------------------------*/
630
631 PROCEDURE get_interorg_cost(
632 i_legal_entity IN NUMBER,
633 i_pac_period_id IN NUMBER,
634 i_cost_type_id IN NUMBER,
635 i_cost_group_id IN NUMBER,
636 i_txn_cost_group_id IN NUMBER,
637 i_txfr_cost_group_id IN NUMBER,
638 i_txn_id IN NUMBER,
639 i_txn_action_id IN NUMBER,
640 i_item_id IN NUMBER,
641 i_txn_qty IN NUMBER,
642 i_txn_org_id IN NUMBER,
643 i_txfr_org_id IN NUMBER,
644 i_user_id IN NUMBER,
645 i_login_id IN NUMBER,
646 i_request_id IN NUMBER,
647 i_prog_id IN NUMBER,
648 i_prog_appl_id IN NUMBER,
649 i_transfer_price_pd IN NUMBER, -- := 0 INVCONV for process-discrete txfer
650 o_err_num OUT NOCOPY NUMBER,
651 o_err_code OUT NOCOPY VARCHAR2,
652 o_err_msg OUT NOCOPY VARCHAR2
653 )
654 IS
655 l_stmt_num NUMBER := 0;
656 l_from_org NUMBER;
657 l_to_org NUMBER;
658 l_trp_cost NUMBER;
659 l_txfr_credit NUMBER;
660 l_txn_id NUMBER;
661 l_conv_rate NUMBER;
662
663 l_err_num NUMBER;
664 l_err_code VARCHAR2(240);
665 l_err_msg VARCHAR2(240);
666 process_error EXCEPTION;
667
668 l_fob_point NUMBER;
669 l_transfer_cost_flag VARCHAR2(1);
670
671 l_cost_source_cost_group NUMBER;
672 l_shipment_txn_id NUMBER;
673 l_tprice_option NUMBER;
674 l_txfr_price NUMBER;
675 l_same_le_ct NUMBER;
676 l_prev_period_id NUMBER;
677 l_pacp_used NUMBER;
678 l_include_txfr_txp_costs NUMBER := 1;
679 l_sending_cg_cost NUMBER;
680 l_ovhd_cost NUMBER;
681
682 l_txn_src_type_id NUMBER;
683 l_txfr_legal_entity NUMBER;
684
685 l_pe_flag VARCHAR2(1); -- INVCONV
686 l_pd_txfr_ind NUMBER := 0; -- INVCONV
687
688 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.get_interorg_cost';
689
690 BEGIN
691
692 IF l_debug = 'Y' THEN
693 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
694 || ' transaction_id: ' || i_txn_id);
695 END IF;
696
697 -- initialize local variables
698 l_err_num := 0;
699 l_err_code := '';
700 l_err_msg := '';
701
702 l_stmt_num := 5;
703
704 IF (i_txn_qty < 0) THEN
705 l_from_org := i_txn_org_id;
706 l_to_org := i_txfr_org_id;
707 ELSE
708 l_from_org := i_txfr_org_id;
709 l_to_org := i_txn_org_id;
710 END IF;
711
712 l_stmt_num := 10;
713
714 /* Get currency conversion rate */
715 Get_Snd_Rcv_Rate (i_txn_id,
716 l_from_org,
717 l_to_org,
718 l_conv_rate,
719 l_err_num,
720 l_err_code,
721 l_err_msg);
722
723 IF (l_err_num <> 0) THEN
724 raise process_error;
725 END IF;
726
727 IF l_debug = 'Y' THEN
728 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
729 || ' l_conv_rate: ' || l_conv_rate);
730 END IF;
731
732 /* Get shipment transaction_id */
733 /* Get trp cost for process-discrete transfer */
734 SELECT decode(i_txn_action_id,
735 21, transaction_id,
736 22, transaction_id, -- INVCONV sikhanna
737 transfer_transaction_id),
738 transaction_source_type_id,
739 /*decode(i_txn_action_id,
740 3, decode(sign(i_txn_qty),
741 1,transfer_transaction_id,
742 transaction_id),
743 transaction_id),*/
744 nvl(transportation_cost,0) -- INVCONV sikhanna
745 INTO l_shipment_txn_id,
746 l_txn_src_type_id,
747 /*l_txn_id,*/
748 l_trp_cost -- INVCONV
749 FROM mtl_material_transactions
750 WHERE transaction_id = i_txn_id;
751
752 IF l_debug = 'Y' THEN
753 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
754 || ' l_shipment_txn_id: ' || l_shipment_txn_id
755 || ', l_txn_src_type_id: ' || l_txn_src_type_id
756 /* || ', l_txn_id: ' || l_txn_id */
757 || ', l_trp_cost: ' || l_trp_cost);
758 END IF;
759
760 l_stmt_num := 15;
761
762 /* Get FOB Point */
763 SELECT nvl(mmt.fob_point,mip.fob_point)
764 INTO l_fob_point
765 FROM mtl_material_transactions mmt,
766 mtl_interorg_parameters mip
767 WHERE mmt.transaction_id = i_txn_id
768 AND mip.from_organization_id = l_from_org
769 AND mip.to_organization_id = l_to_org;
770
771 IF l_debug = 'Y' THEN
772 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
773 || ' l_fob_point: ' || l_fob_point);
774 END IF;
775
776 l_stmt_num := 20;
777
778 /* Get the profile status for Internal Sales Order. */
779 BEGIN
780 SELECT nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'), 0)
781 INTO l_tprice_option
782 FROM mtl_intercompany_parameters MIP
783 WHERE fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
784 AND MIP.flow_type = 1
785 AND MIP.ship_organization_id = (SELECT to_number(HOI.org_information3)
786 FROM hr_organization_information HOI
787 WHERE HOI.organization_id = l_from_org
788 AND HOI.org_information_context = 'Accounting Information')
789 AND MIP.sell_organization_id = (SELECT to_number(HOI2.org_information3)
790 FROM hr_organization_information HOI2
791 WHERE HOI2.organization_id = l_to_org
792 AND HOI2.org_information_context = 'Accounting Information');
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 l_tprice_option := -1; /* Chenged it to be -1, will toggle to 0 later */
796 END;
797
798 -- Processing the process-discrete txns
799 -- INVCONV sikhanna
800 SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
801 INTO l_pd_txfr_ind
802 FROM MTL_PARAMETERS MP
803 WHERE MP.ORGANIZATION_ID = i_txn_org_id
804 OR MP.ORGANIZATION_ID = i_txfr_org_id;
805
806 /* Process-Discrete X-fers set the profile to 2 if ICR relations setup and in diff OU */
807 IF (l_pd_txfr_ind=1 and l_tprice_option <> -1) THEN
808 l_tprice_option := 2; -- Make it 2 to ignore the profile.
809 END IF;
810
811 IF l_tprice_option = -1 THEN
812 l_tprice_option := 0; /* Toggle it to 0 as 0 is used later */
813 END IF;
814 -- INVCONV sikhanna END
815
816 l_stmt_num := 22;
817
818 IF l_debug = 'Y' THEN
819 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
820 || ' l_tprice_option: ' || l_tprice_option || ', process-discrete xfer: ' || l_pd_txfr_ind);
821 END IF;
822
823 IF ((l_tprice_option = 2)
824 AND ((i_txn_action_id in (21,22) AND l_txn_src_type_id = 8)
825 OR (i_txn_action_id in (12,15) AND l_txn_src_type_id = 7))) THEN
826
827 /* Internal Sales Order with transfer price specified and profiles set.
828 CST_TRANSFER_PRICING_OPTION is set to: Yes,Price as Incoming Cost.
829 For other values of this profile, the incoming cost populated into
830 MPTCD will follow the same rules as in the case of ordinary interorg
831 transfers */
832
833 l_stmt_num := 25;
834
835 /* Get transfer price from MMT */
836 SELECT transfer_price
837 INTO l_txfr_price
838 FROM mtl_material_transactions
839 WHERE transaction_id = l_shipment_txn_id;
840
841 IF (l_txfr_price is NULL) THEN
842 fnd_file.put_line(fnd_file.log,'Transfer Price not available');
843 l_err_msg := 'CSTPPINV.get_interorg_cost : ' || to_char(l_stmt_num) ||' : '|| ' Transfer Price not available';
844 l_err_num := 9999;
845 raise process_error;
846 END IF;
847
848 IF l_debug = 'Y' THEN
849 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
850 || ' l_txfr_price: ' || l_txfr_price);
851 END IF;
852
853 l_stmt_num := 30;
854
855 /* Insert transfer price into MPTCD with cost element 1 */
856
857 CSTPPINV.insert_elemental_cost(
858 i_pac_period_id => i_pac_period_id,
859 i_cost_type_id => i_cost_type_id,
860 i_cost_group_id => i_cost_group_id,
861 i_txn_id => i_txn_id,
862 i_item_id => i_item_id,
863 i_cost_element_id => 1,
864 i_level_type => 1,
865 i_cost => l_txfr_price * l_conv_rate,
866 i_user_id => i_user_id,
867 i_login_id => i_login_id,
868 i_request_id => i_request_id,
869 i_prog_id => i_prog_id,
870 i_prog_appl_id => i_prog_appl_id,
871 o_err_num => l_err_num,
872 o_err_code => l_err_code,
873 o_err_msg => l_err_msg
874 );
875
876 ELSIF (l_pd_txfr_ind <> 1) THEN /* Not a transfer pricing situation - Ordinary Interorg transfers */
877
878 l_stmt_num := 32;
879
880 -- discrete-discrete interorg transfer
881
882 IF l_debug = 'Y' THEN
883 fnd_file.put_line(fnd_file.log,' Regular Interorg Transfer:' || ' discrete-discrete xfer: ');
884 END IF;
885
886 IF ((l_tprice_option = 1)
887 AND ((i_txn_action_id in (21,22) AND l_txn_src_type_id = 8)
888 OR (i_txn_action_id in (12,15) AND l_txn_src_type_id = 7))) THEN
889 /* This is a case of internal sales order with
890 CST:Transfer Pricing Option = Yes, Price NOT as Incoming Cost.
891 In this case, although we populate MPTCD with the same sending CG
892 cost as if this were a regular interorg transfer, we should NOT
893 be adding the transfer credit and transportation charge
894 to the sending CG cost. This is to be consistent with
895 the functionality in perpetual costing. */
896 l_include_txfr_txp_costs := 0;
897 ELSE
898 /* In all other cases, including internal sales order with
899 CST:Transfer Pricing Option = No, we should add the transfer
900 credit and transportation charge to the sending CG cost. */
901 l_include_txfr_txp_costs := 1;
902 END IF;
903
904
905 l_stmt_num := 35;
906
907 /* Get legal entity of the other cost group,if available */
908
909 BEGIN
910 SELECT legal_entity
911 INTO l_txfr_legal_entity
912 FROM cst_cost_groups
913 WHERE cost_group_id = decode(i_txn_action_id,
914 21,decode(l_fob_point,
915 1,i_txn_cost_group_id,
916 i_txfr_cost_group_id),
917 i_txfr_cost_group_id);
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 l_txfr_legal_entity := -1;
921 END;
922
923 /* See if i_cost_type_id is attached to the transfer LE as well */
924 SELECT count(*)
925 INTO l_same_le_ct
926 FROM cst_le_cost_types
927 WHERE legal_entity = l_txfr_legal_entity
928 AND cost_type_id = i_cost_type_id;
929
930 l_stmt_num := 40;
931
932 /* The transfer_cost_flag status indicates if PACP is used */
933 SELECT TRANSFER_COST_FLAG
934 INTO l_transfer_cost_flag
935 FROM CST_LE_COST_TYPES
936 WHERE LEGAL_ENTITY = i_legal_entity
937 AND COST_TYPE_ID = i_cost_type_id;
938
939 IF l_debug = 'Y' THEN
940 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
941 || ' l_txfr_legal_entity: ' || l_txfr_legal_entity
942 || ', l_same_le_ct: ' || l_same_le_ct
943 || ', l_transfer_cost_flag (PACP): ' || l_transfer_cost_flag);
944 END IF;
945
946 l_stmt_num := 45;
947
948 /* Check for the same LE/CT combination */
949 IF (i_legal_entity = l_txfr_legal_entity AND l_same_le_ct > 0) THEN
950
951 /* Find the Cost group to get the estimated sending CG cost from.
952 For direct interorgs, the sending CG is the transfer CG.
953 For the shipment transaction of FOB shipment processed by the
954 receiving CG, the sending CG is the transaction CG.
955 For the receipt transaction of FOB receipt processed by
956 the receiving CG, the sending CG is the transfer CG. */
957
958 l_stmt_num := 49;
959
960 IF ( I_TXN_ACTION_ID = 21 AND L_FOB_POINT = 1 ) THEN
961 L_COST_SOURCE_COST_GROUP := I_TXN_COST_GROUP_ID;
962 ELSE
963 L_COST_SOURCE_COST_GROUP := I_TXFR_COST_GROUP_ID;
964 END IF;
965
966 IF l_debug = 'Y' THEN
967 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
968 || ' l_cost_source_cost_group: ' || l_cost_source_cost_group);
969 END IF;
970
971 IF (l_transfer_cost_flag = 'Y') THEN
972
973 IF l_debug = 'Y' THEN
974 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
975 || ' Same LE/CT Transfer with PACP enabled');
976 END IF;
977
978 /* Use PACP cost if available */
979 l_stmt_num := 50;
980
981 CSTPPINV.get_pacp_cost(
982 i_cost_source_cost_group => l_cost_source_cost_group,
983 i_pac_period_id => i_pac_period_id,
984 i_cost_type_id => i_cost_type_id,
985 i_cost_group_id => i_cost_group_id,
986 i_txn_id => i_txn_id,
987 i_item_id => i_item_id,
988 i_conv_rate => l_conv_rate,
989 i_user_id => i_user_id,
990 i_login_id => i_login_id,
991 i_request_id => i_request_id,
992 i_prog_id => i_prog_id,
993 i_prog_appl_id => i_prog_appl_id,
994 x_pacp_used => l_pacp_used,
995 x_pacp_cost => l_sending_cg_cost,
996 o_err_num => l_err_num,
997 o_err_code => l_err_code,
998 o_err_msg => l_err_msg);
999
1000 IF (l_err_num <> 0) THEN
1001 raise process_error;
1002 END IF;
1003
1004 IF (l_pacp_used = -1) THEN
1005
1006 /* There is no PACP cost, so no costs were inserted into MPTCD.
1007 Insert perpetual shipment cost instead. */
1008
1009 CSTPPINV.get_perp_ship_cost(
1010 i_pac_period_id => i_pac_period_id,
1011 i_cost_type_id => i_cost_type_id,
1012 i_cost_group_id => i_cost_group_id,
1013 i_txn_id => i_txn_id,
1014 i_mta_txn_id => l_shipment_txn_id,
1015 i_item_id => i_item_id,
1016 i_from_org => l_from_org,
1017 i_conv_rate => l_conv_rate,
1018 i_user_id => i_user_id,
1019 i_login_id => i_login_id,
1020 i_request_id => i_request_id,
1021 i_prog_id => i_prog_id,
1022 i_prog_appl_id => i_prog_appl_id,
1023 x_perp_ship_cost => l_sending_cg_cost,
1024 o_err_num => l_err_num,
1025 o_err_code => l_err_code,
1026 o_err_msg => l_err_msg
1027 );
1028
1029 IF (l_err_num <> 0) THEN
1030 raise process_error;
1031 END IF;
1032
1033 END IF; /* IF (l_pacp_used = -1) THEN */
1034
1035 ELSE /* PACP is not enabled */
1036
1037 l_stmt_num := 80;
1038
1039 IF l_debug = 'Y' THEN
1040 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
1041 || ' Same LE/CT Transfer with PACP not enabled');
1042 END IF;
1043
1044 /* PACP is not enabled. Use prior period sending CG PWAC Cost */
1045
1046 CSTPPINV.get_prev_period_cost(
1047 i_legal_entity => i_legal_entity,
1048 i_cost_source_cost_group => l_cost_source_cost_group,
1049 i_pac_period_id => i_pac_period_id,
1050 i_cost_type_id => i_cost_type_id,
1051 i_cost_group_id => i_cost_group_id,
1052 i_txn_id => i_txn_id,
1053 i_item_id => i_item_id,
1054 i_conv_rate => l_conv_rate,
1055 i_user_id => i_user_id,
1056 i_login_id => i_login_id,
1057 i_request_id => i_request_id,
1058 i_prog_id => i_prog_id,
1059 i_prog_appl_id => i_prog_appl_id,
1060 x_prev_period_id => l_prev_period_id,
1061 x_prev_period_cost => l_sending_cg_cost,
1062 o_err_num => l_err_num,
1063 o_err_code => l_err_code,
1064 o_err_msg => l_err_msg
1065 );
1066
1067 IF (l_err_num <> 0) THEN
1068 raise process_error;
1069 END IF;
1070
1071 IF (l_prev_period_id = -1) THEN
1072
1073 /* There is no prior period cost, so no costs were inserted into MPTCD.
1074 Insert perpetual shipment cost instead. */
1075
1076 CSTPPINV.get_perp_ship_cost(
1077 i_pac_period_id => i_pac_period_id,
1078 i_cost_type_id => i_cost_type_id,
1079 i_cost_group_id => i_cost_group_id,
1080 i_txn_id => i_txn_id,
1081 i_mta_txn_id => l_shipment_txn_id,
1082 i_item_id => i_item_id,
1083 i_from_org => l_from_org,
1084 i_conv_rate => l_conv_rate,
1085 i_user_id => i_user_id,
1086 i_login_id => i_login_id,
1087 i_request_id => i_request_id,
1088 i_prog_id => i_prog_id,
1089 i_prog_appl_id => i_prog_appl_id,
1090 x_perp_ship_cost => l_sending_cg_cost,
1091 o_err_num => l_err_num,
1092 o_err_code => l_err_code,
1093 o_err_msg => l_err_msg
1094 );
1095
1096 IF (l_err_num <> 0) THEN
1097 raise process_error;
1098 END IF;
1099
1100 END IF; /* IF (l_prev_period_id = -1) THEN */
1101
1102 END IF; /* End for IF l_transfer_cost_flag */
1103
1104 ELSE /* Not in same LE/CT */
1105
1106 l_stmt_num := 120;
1107
1108 IF l_debug = 'Y' THEN
1109 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':' || ' Different LE/CT Transfer:');
1110 END IF;
1111
1112 /* Sending and Receiving Orgs not in the same LE/CT
1113 Use MTA Cost */
1114
1115 CSTPPINV.get_perp_ship_cost(
1116 i_pac_period_id => i_pac_period_id,
1117 i_cost_type_id => i_cost_type_id,
1118 i_cost_group_id => i_cost_group_id,
1119 i_txn_id => i_txn_id,
1120 i_mta_txn_id => l_shipment_txn_id,
1121 i_item_id => i_item_id,
1122 i_from_org => l_from_org,
1123 i_conv_rate => l_conv_rate,
1124 i_user_id => i_user_id,
1125 i_login_id => i_login_id,
1126 i_request_id => i_request_id,
1127 i_prog_id => i_prog_id,
1128 i_prog_appl_id => i_prog_appl_id,
1129 x_perp_ship_cost => l_sending_cg_cost,
1130 o_err_num => l_err_num,
1131 o_err_code => l_err_code,
1132 o_err_msg => l_err_msg
1133 );
1134
1135 IF (l_err_num <> 0) THEN
1136 raise process_error;
1137 END IF;
1138
1139 END IF; /* IF (i_legal_entity = l_txfr_legal_entity AND l_same_le_ct > 0) THEN */
1140
1141 IF l_debug = 'Y' THEN
1142 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
1143 || ' l_sending_cg_cost: ' || l_sending_cg_cost);
1144 END IF;
1145
1146
1147 IF (l_include_txfr_txp_costs = 1) THEN
1148 /* Add transfer credit and transportation charges on top of the sending CG cost. */
1149 l_stmt_num := 130;
1150
1151 CSTPPINV.get_txfr_trp_cost(
1152 i_source_txn_id => l_shipment_txn_id,
1153 i_source_cost => (l_sending_cg_cost),
1154 x_txfr_credit => l_txfr_credit,
1155 x_trp_cost => l_trp_cost,
1156 o_err_num => l_err_num,
1157 o_err_code => l_err_code,
1158 o_err_msg => l_err_msg
1159 );
1160
1161 IF (l_err_num <> 0) THEN
1162 raise process_error;
1163 END IF;
1164
1165 l_ovhd_cost := (l_trp_cost + l_txfr_credit) * l_conv_rate;
1166 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': l_ovhd_cost = ' || l_ovhd_cost);
1167
1168 IF (l_ovhd_cost > 0) THEN
1169 CSTPPINV.add_elemental_cost(
1170 i_pac_period_id => i_pac_period_id,
1171 i_cost_type_id => i_cost_type_id,
1172 i_cost_group_id => i_cost_group_id,
1173 i_txn_id => i_txn_id,
1174 i_item_id => i_item_id,
1175 i_cost_element_id => 2,
1176 i_level_type => 1,
1177 i_incr_cost => l_ovhd_cost,
1178 i_user_id => i_user_id,
1179 i_login_id => i_login_id,
1180 i_request_id => i_request_id,
1181 i_prog_id => i_prog_id,
1182 i_prog_appl_id => i_prog_appl_id,
1183 o_err_num => l_err_num,
1184 o_err_code => l_err_code,
1185 o_err_msg => l_err_msg
1186 );
1187
1188 IF (l_err_num <> 0) THEN
1189 raise process_error;
1190 END IF;
1191 END IF; /* IF (l_ovhd_cost > 0) THEN */
1192
1193 END IF; /* IF (i_include_txfr_txp_costs = 1) THEN */
1194
1195 ELSE -- INVCONV Process-Discrete Transfer.
1196
1197 IF l_debug = 'Y' THEN
1198 fnd_file.put_line(fnd_file.log,' INVCONV process-discrete xfer');
1199 END IF;
1200
1201 l_stmt_num := 145;
1202
1203 --INVCONV sikhanna
1204 -- From organization is a process org. just get transaction cost
1205 -- trp cost and conv rate is selected in the begining itself
1206
1207 INSERT INTO mtl_pac_txn_cost_details (
1208 transaction_id,
1209 pac_period_id,
1210 cost_type_id,
1211 cost_group_id,
1212 cost_element_id,
1213 level_type,
1214 inventory_item_id,
1215 transaction_cost,
1216 last_update_date,
1217 last_updated_by,
1218 creation_date,
1219 created_by,
1220 request_id,
1221 program_application_id,
1222 program_id,
1223 program_update_date,
1224 last_update_login)
1225 VALUES(
1226 i_txn_id,
1227 i_pac_period_id,
1228 i_cost_type_id,
1229 i_cost_group_id,
1230 1,
1231 1,
1232 i_item_id,
1233 i_transfer_price_pd,
1234 SYSDATE,
1235 i_user_id,
1236 SYSDATE,
1237 i_user_id,
1238 i_request_id,
1239 i_prog_appl_id,
1240 i_prog_id,
1241 SYSDATE,
1242 i_login_id);
1243
1244 l_stmt_num := 150;
1245
1246 IF l_debug = 'Y' THEN
1247 fnd_file.put_line(fnd_file.log,' transfer price stamped: ' || i_transfer_price_pd);
1248 fnd_file.put_line(fnd_file.log,' txn_axn_id/qty: ' || i_txn_action_id || '/' || i_txn_qty);
1249 END IF;
1250
1251 /* Don't earn overhead for Transportation Cost for Direct Interorg Receipt */
1252 /* Also transfer cost=transfer price for receiving org, intransit rcpt, fob rcpt */
1253 IF ((i_txn_action_id = 3 and i_txn_qty > 0) or (i_txn_action_id=12 and l_fob_point=2)) THEN
1254
1255 IF l_debug = 'Y' THEN
1256 fnd_file.put_line(fnd_file.log,' No MOH earned: ');
1257 END IF;
1258
1259 ELSE -- Earn MOH in all other cases
1260
1261 INSERT INTO mtl_pac_txn_cost_details (
1262 transaction_id,
1263 pac_period_id,
1264 cost_type_id,
1265 cost_group_id,
1266 cost_element_id,
1267 level_type,
1268 inventory_item_id,
1269 transaction_cost,
1270 last_update_date,
1271 last_updated_by,
1272 creation_date,
1273 created_by,
1274 request_id,
1275 program_application_id,
1276 program_id,
1277 program_update_date,
1278 last_update_login)
1279 VALUES (
1280 i_txn_id,
1281 i_pac_period_id,
1282 i_cost_type_id,
1283 i_cost_group_id,
1284 2,
1285 1,
1286 i_item_id,
1287 l_trp_cost / abs(i_txn_qty) * decode(i_txn_action_id,
1288 15, 1,
1289 22, 1,
1290 12, decode(l_fob_point,
1291 2, 1,
1292 l_conv_rate),
1293 l_conv_rate),
1294 SYSDATE,
1295 i_user_id,
1296 SYSDATE,
1297 i_user_id,
1298 i_request_id,
1299 i_prog_appl_id,
1300 i_prog_id,
1301 SYSDATE,
1302 i_login_id);
1303
1304 IF l_debug = 'Y' THEN
1305 fnd_file.put_line(fnd_file.log,' transportation cost as MOH stamped: ' || l_trp_cost);
1306 END IF;
1307
1308 END IF; /* IF ((i_txn_action_id = 3 and i_txn_qty > 0) or (i_txn_action_id=12 and l_fob_point=2)) THEN */
1309
1310 END IF; /* IF internal sales order ... */
1311
1312 l_stmt_num := 200;
1313
1314 IF l_debug = 'Y' THEN
1315 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1316 END IF;
1317
1318 EXCEPTION
1319
1320 when process_error then
1321 o_err_num := l_err_num;
1322 o_err_code := l_err_code;
1323 o_err_msg := l_err_msg;
1324
1325 when OTHERS then
1326 o_err_num := SQLCODE;
1327 o_err_msg := 'CSTPPINV.get_interorg_cost (' || to_char(l_stmt_num) ||
1328 '): ' || substr(SQLERRM,1,200);
1329 fnd_file.put_line(fnd_file.log, o_err_msg);
1330
1331 END get_interorg_cost;
1332
1333 /*---------------------------------------------------------------------------
1334 | Procedure get_txfr_trp_cost()
1335 |
1336 | This routine returns the unit transfer credit and transportation charge
1337 | for interorg transactions involving an ownership change, given
1338 | the transaction from which to obtain the transfer percentage, transfer cost,
1339 | and transportation charge, and i_source_cost, which is the unit cost upon which
1340 | a transfer percentage should be applied. i_source_cost should be in the
1341 | currency of the source_txn_id org. x_txfr_credit and x_trp_cost will be
1342 | unit costs returned in the currency of the source_txn_id org
1343 |
1344 |
1345 ---------------------------------------------------------------------------*/
1346
1347 PROCEDURE get_txfr_trp_cost(
1348 i_source_txn_id IN NUMBER,
1349 i_source_cost IN NUMBER,
1350 x_txfr_credit OUT NOCOPY NUMBER,
1351 x_trp_cost OUT NOCOPY NUMBER,
1352 o_err_num OUT NOCOPY NUMBER,
1353 o_err_code OUT NOCOPY VARCHAR2,
1354 o_err_msg OUT NOCOPY VARCHAR2
1355 )
1356 IS
1357 l_stmt_num NUMBER := 0;
1358 l_err_num NUMBER;
1359 l_err_code VARCHAR2(240);
1360 l_err_msg VARCHAR2(240);
1361
1362 l_trp_cost NUMBER;
1363 l_txfr_percent NUMBER;
1364 l_txfr_cost NUMBER;
1365
1366 l_txfr_credit NUMBER;
1367 l_shipment_txn_qty NUMBER;
1368
1369 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.get_txfr_trp_cost';
1370
1371 BEGIN
1372
1373 IF l_debug = 'Y' THEN
1374 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
1375 || ' source_transaction_id: ' || i_source_txn_id
1376 || ' source_cost: ' || i_source_cost);
1377 END IF;
1378
1379 -- initialize local variables
1380 l_err_num := 0;
1381 l_err_code := '';
1382 l_err_msg := '';
1383
1384 l_stmt_num := 10;
1385
1386 /* Pick up transfer credit and transportation charge from the shipment txn */
1387 SELECT nvl(transfer_percentage,0),
1388 nvl(transfer_cost,0),
1389 nvl(transportation_cost,0),
1390 primary_quantity
1391 INTO l_txfr_percent,
1392 l_txfr_cost,
1393 l_trp_cost,
1394 l_shipment_txn_qty
1395 FROM mtl_material_transactions
1396 WHERE transaction_id = i_source_txn_id;
1397
1398 IF l_debug = 'Y' THEN
1399 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
1400 || ' l_txfr_percent: ' || l_txfr_percent
1401 || ', l_txfr_cost: ' || l_txfr_cost
1402 || ', l_trp_cost: ' || l_trp_cost
1403 || ', l_shipment_txn_qty: ' || l_shipment_txn_qty);
1404 END IF;
1405
1406 l_stmt_num := 20;
1407
1408 IF (l_txfr_percent <> 0) THEN
1409 l_txfr_credit := (l_txfr_percent * i_source_cost / 100);
1410 ELSIF (l_txfr_cost <> 0) THEN
1411 l_txfr_credit := l_txfr_cost / abs(l_shipment_txn_qty);
1412 ELSE
1413 l_txfr_credit := 0;
1414 END IF;
1415
1416 l_stmt_num := 30;
1417 x_txfr_credit := l_txfr_credit;
1418 x_trp_cost := l_trp_cost / abs(l_shipment_txn_qty);
1419
1420 IF l_debug = 'Y' THEN
1421 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
1422 || ' x_txfr_credit: ' || x_txfr_credit
1423 || ', x_trp_cost: ' || x_trp_cost);
1424 END IF;
1425
1426 l_stmt_num := 40;
1427
1428 IF l_debug = 'Y' THEN
1429 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1430 END IF;
1431
1432 EXCEPTION
1433
1434 when OTHERS then
1435 o_err_num := SQLCODE;
1436 o_err_msg := 'CSTPPINV.get_txfr_trp_cost (' || to_char(l_stmt_num) ||
1437 '): ' || substr(SQLERRM,1,200);
1438 fnd_file.put_line(fnd_file.log, o_err_msg);
1439
1440 END get_txfr_trp_cost;
1441
1442 /*---------------------------------------------------------------------------
1443 | Procedure add_elemental_cost()
1444 |
1445 | This routine inserts the specified incremental cost
1446 | (parameter i_incr_cost) into the given cost element of MCTCD if
1447 | the element does not yet exist, or updates the cost element
1448 | with the incremental cost if the cost element already exists.
1449 |
1450 | The i_incr_cost is assumed to already be in the proper currency.
1451 |
1452 ---------------------------------------------------------------------------*/
1453
1454 PROCEDURE add_elemental_cost(
1455 i_pac_period_id IN NUMBER,
1456 i_cost_type_id IN NUMBER,
1457 i_cost_group_id IN NUMBER,
1458 i_txn_id IN NUMBER,
1459 i_item_id IN NUMBER,
1460 i_cost_element_id IN NUMBER,
1461 i_level_type IN NUMBER,
1462 i_incr_cost IN NUMBER,
1463 i_user_id IN NUMBER,
1464 i_login_id IN NUMBER,
1465 i_request_id IN NUMBER,
1466 i_prog_id IN NUMBER,
1467 i_prog_appl_id IN NUMBER,
1468 o_err_num OUT NOCOPY NUMBER,
1469 o_err_code OUT NOCOPY VARCHAR2,
1470 o_err_msg OUT NOCOPY VARCHAR2
1471 )
1472 IS
1473 l_stmt_num NUMBER := 0;
1474 l_err_num NUMBER;
1475 l_err_code VARCHAR2(240);
1476 l_err_msg VARCHAR2(240);
1477
1478 process_error EXCEPTION;
1479 l_elem_cnt NUMBER;
1480 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.add_elemental_cost';
1481
1482 BEGIN
1483
1484 IF l_debug = 'Y' THEN
1485 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
1486 || ' i_txn_id: ' || i_txn_id);
1487 END IF;
1488
1489 -- initialize local variables
1490 l_err_num := 0;
1491 l_err_code := '';
1492 l_err_msg := '';
1493
1494 l_stmt_num := 10;
1495
1496 SELECT count(*)
1497 INTO l_elem_cnt
1498 FROM mtl_pac_txn_cost_details
1499 WHERE transaction_id = I_TXN_ID
1500 AND pac_period_id = I_PAC_PERIOD_ID
1501 AND cost_type_id = I_COST_TYPE_ID
1502 AND cost_group_id = I_COST_GROUP_ID
1503 AND cost_element_id = i_cost_element_id
1504 AND level_type = i_level_type;
1505
1506 IF l_elem_cnt > 0 THEN
1507
1508 l_stmt_num := 20;
1509
1510 UPDATE MTL_PAC_TXN_COST_DETAILS
1511 SET transaction_cost = (transaction_cost + i_incr_cost)
1512 WHERE transaction_id = I_TXN_ID
1513 AND pac_period_id = I_PAC_PERIOD_ID
1514 AND cost_type_id = I_COST_TYPE_ID
1515 AND cost_group_id = I_COST_GROUP_ID
1516 AND cost_element_id = i_cost_element_id
1517 AND level_type = i_level_type;
1518
1519 ELSE
1520
1521 l_stmt_num := 30;
1522
1523 CSTPPINV.insert_elemental_cost(
1524 i_pac_period_id => i_pac_period_id,
1525 i_cost_type_id => i_cost_type_id,
1526 i_cost_group_id => i_cost_group_id,
1527 i_txn_id => i_txn_id,
1528 i_item_id => i_item_id,
1529 i_cost_element_id => i_cost_element_id,
1530 i_level_type => i_level_type,
1531 i_cost => i_incr_cost,
1532 i_user_id => i_user_id,
1533 i_login_id => i_login_id,
1534 i_request_id => i_request_id,
1535 i_prog_id => i_prog_id,
1536 i_prog_appl_id => i_prog_appl_id,
1537 o_err_num => l_err_num,
1538 o_err_code => l_err_code,
1539 o_err_msg => l_err_msg
1540 );
1541
1542 IF (l_err_num <> 0) THEN
1543 raise process_error;
1544 END IF;
1545
1546 END IF; /* (l_incr_cnt > 0) */
1547
1548 l_stmt_num := 40;
1549
1550 IF l_debug = 'Y' THEN
1551 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1552 END IF;
1553
1554 EXCEPTION
1555
1556 when process_error then
1557 o_err_num := l_err_num;
1558 o_err_code := l_err_code;
1559 o_err_msg := l_err_msg;
1560
1561 when OTHERS then
1562 o_err_num := SQLCODE;
1563 o_err_msg := 'CSTPPINV.add_elemental_cost (' || to_char(l_stmt_num) ||
1564 '): ' || substr(SQLERRM,1,200);
1565 fnd_file.put_line(fnd_file.log, o_err_msg);
1566 END add_elemental_cost;
1567
1568 /*---------------------------------------------------------------------------
1569 | Procedure insert_elemental_cost()
1570 |
1571 | This routine inserts the specified cost
1572 | (parameter i_incr_cost) into the given cost element of MCTCD.
1573 |
1574 | The i_cost is assumed to already be in the proper currency.
1575 |
1576 ---------------------------------------------------------------------------*/
1577
1578 PROCEDURE insert_elemental_cost(
1579 i_pac_period_id IN NUMBER,
1580 i_cost_type_id IN NUMBER,
1581 i_cost_group_id IN NUMBER,
1582 i_txn_id IN NUMBER,
1583 i_item_id IN NUMBER,
1584 i_cost_element_id IN NUMBER,
1585 i_level_type IN NUMBER,
1586 i_cost IN NUMBER,
1587 i_user_id IN NUMBER,
1588 i_login_id IN NUMBER,
1589 i_request_id IN NUMBER,
1590 i_prog_id IN NUMBER,
1591 i_prog_appl_id IN NUMBER,
1592 o_err_num OUT NOCOPY NUMBER,
1593 o_err_code OUT NOCOPY VARCHAR2,
1594 o_err_msg OUT NOCOPY VARCHAR2
1595 )
1596 IS
1597 l_stmt_num NUMBER := 0;
1598 l_err_num NUMBER;
1599 l_err_code VARCHAR2(240);
1600 l_err_msg VARCHAR2(240);
1601
1602 l_elem_cnt NUMBER;
1603 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.insert_elemental_cost';
1604
1605 BEGIN
1606
1607 IF l_debug = 'Y' THEN
1608 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
1609 || ' i_txn_id: ' || i_txn_id);
1610 END IF;
1611
1612 -- initialize local variables
1613 l_err_num := 0;
1614 l_err_code := '';
1615 l_err_msg := '';
1616
1617 l_stmt_num := 10;
1618
1619
1620 INSERT INTO MTL_PAC_TXN_COST_DETAILS (
1621 transaction_id,
1622 pac_period_id,
1623 cost_type_id,
1624 cost_group_id,
1625 cost_element_id,
1626 level_type,
1627 inventory_item_id,
1628 transaction_cost,
1629 last_update_date,
1630 last_updated_by,
1631 creation_date,
1632 created_by,
1633 request_id,
1634 program_application_id,
1635 program_id,
1636 program_update_date,
1637 last_update_login )
1638 VALUES(
1639 I_TXN_ID,
1640 I_PAC_PERIOD_ID,
1641 I_COST_TYPE_ID,
1642 I_COST_GROUP_ID,
1643 i_cost_element_id,
1644 i_level_type,
1645 i_item_id,
1646 (i_cost),
1647 sysdate,
1648 i_user_id,
1649 sysdate,
1650 i_user_id,
1651 i_request_id,
1652 i_prog_appl_id,
1653 i_prog_id,
1654 sysdate,
1655 i_login_id);
1656
1657
1658 l_stmt_num := 20;
1659
1660 IF l_debug = 'Y' THEN
1661 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1662 END IF;
1663
1664 EXCEPTION
1665 when OTHERS then
1666 o_err_num := SQLCODE;
1667 o_err_msg := 'CSTPPINV.insert_elemental_cost (' || to_char(l_stmt_num) ||
1668 '): ' || substr(SQLERRM,1,200);
1669 fnd_file.put_line(fnd_file.log, o_err_msg);
1670 END insert_elemental_cost;
1671
1672
1673 /*---------------------------------------------------------------------------
1674 | Procedure get_pacp_cost()
1675 |
1676 | This is a helper routine to get_interorg_cost.
1677 | It inserts the PACP cost of i_cost_source_cost_group from CPIC into MPTCD
1678 | plus any transfer and transportation charges as appropriate.
1679 |
1680 | Returns x_pacp_used = -1, if no costs were inserted into MPTCD.
1681 ---------------------------------------------------------------------------*/
1682 PROCEDURE get_pacp_cost(
1683 i_cost_source_cost_group IN NUMBER,
1684 i_pac_period_id IN NUMBER,
1685 i_cost_type_id IN NUMBER,
1686 i_cost_group_id IN NUMBER,
1687 i_txn_id IN NUMBER,
1688 i_item_id IN NUMBER,
1689 i_conv_rate IN NUMBER,
1690 i_user_id IN NUMBER,
1691 i_login_id IN NUMBER,
1692 i_request_id IN NUMBER,
1693 i_prog_id IN NUMBER,
1694 i_prog_appl_id IN NUMBER,
1695 x_pacp_used OUT NOCOPY NUMBER,
1696 x_pacp_cost OUT NOCOPY NUMBER,
1697 o_err_num OUT NOCOPY NUMBER,
1698 o_err_code OUT NOCOPY VARCHAR2,
1699 o_err_msg OUT NOCOPY VARCHAR2
1700 )
1701 IS
1702 l_stmt_num NUMBER := 0;
1703 l_err_num NUMBER;
1704 l_err_code VARCHAR2(240);
1705 l_err_msg VARCHAR2(240);
1706
1707 l_pacp_pwac_cost NUMBER;
1708 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.get_pacp_cost';
1709
1710 BEGIN
1711
1712 IF l_debug = 'Y' THEN
1713 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
1714 || ' i_txn_id: ' || i_txn_id);
1715 END IF;
1716
1717 -- initialize local variables
1718 l_err_num := 0;
1719 l_err_code := '';
1720 l_err_msg := '';
1721
1722 l_stmt_num := 10;
1723
1724 BEGIN
1725 /* Get PACP cost from CPICD */
1726 SELECT NVL(CPIC.item_cost,0)
1727 INTO l_pacp_pwac_cost
1728 FROM CST_PAC_ITEM_COSTS CPIC
1729 WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
1730 AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
1731 AND CPIC.PAC_PERIOD_ID = i_pac_period_id;
1732
1733 EXCEPTION
1734 WHEN no_data_found THEN
1735 x_pacp_used := -1;
1736 x_pacp_cost := NULL;
1737 return;
1738 END;
1739
1740 l_stmt_num := 20;
1741
1742 INSERT INTO MTL_PAC_TXN_COST_DETAILS (
1743 transaction_id,
1744 pac_period_id,
1745 cost_type_id,
1746 cost_group_id,
1747 cost_element_id,
1748 level_type,
1749 inventory_item_id,
1750 transaction_cost,
1751 last_update_date,
1752 last_updated_by,
1753 creation_date,
1754 created_by,
1755 request_id,
1756 program_application_id,
1757 program_id,
1758 program_update_date,
1759 last_update_login )
1760 SELECT
1761 I_TXN_ID,
1762 I_PAC_PERIOD_ID,
1763 I_COST_TYPE_ID,
1764 I_COST_GROUP_ID,
1765 CPICD.cost_element_id,
1766 CPICD.level_type,
1767 i_item_id,
1768 (CPICD.item_cost * i_conv_rate),
1769 sysdate,
1770 i_user_id,
1771 sysdate,
1772 i_user_id,
1773 i_request_id,
1774 i_prog_appl_id,
1775 i_prog_id,
1776 sysdate,
1777 i_login_id
1778 FROM CST_PAC_ITEM_COSTS CPIC,
1779 CST_PAC_ITEM_COST_DETAILS CPICD
1780 WHERE CPICD.COST_LAYER_ID = CPIC.COST_LAYER_ID
1781 AND CPIC.INVENTORY_ITEM_ID = i_item_id
1782 AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
1783 AND CPIC.PAC_PERIOD_ID = I_PAC_PERIOD_ID;
1784
1785 l_stmt_num := 30;
1786 x_pacp_used := 1;
1787 x_pacp_cost := l_pacp_pwac_cost;
1788
1789 IF l_debug = 'Y' THEN
1790 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ':'
1791 || ' x_pacp_used: ' || x_pacp_used
1792 || ', x_pacp_cost: ' || x_pacp_cost);
1793 END IF;
1794
1795 l_stmt_num := 110;
1796
1797 IF l_debug = 'Y' THEN
1798 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1799 END IF;
1800
1801 EXCEPTION
1802
1803 when OTHERS then
1804 o_err_num := SQLCODE;
1805 o_err_msg := 'CSTPPINV.get_pacp_cost (' || to_char(l_stmt_num) ||
1806 '): ' || substr(SQLERRM,1,200);
1807 fnd_file.put_line(fnd_file.log, o_err_msg);
1808
1809 END get_pacp_cost;
1810
1811 /*---------------------------------------------------------------------------
1812 | Procedure get_prev_period_cost()
1813 |
1814 | This is a helper routine for get_interorg_cost.
1815 | It inserts the prior period cost from CPIC into MPTCD
1816 | plus any transfer and transportation charges as appropriate.
1817 |
1818 | Returns x_prev_period_id = -1, if no costs were inserted into MPTCD.
1819 ---------------------------------------------------------------------------*/
1820 PROCEDURE get_prev_period_cost(
1821 i_legal_entity IN NUMBER,
1822 i_cost_source_cost_group IN NUMBER,
1823 i_pac_period_id IN NUMBER,
1824 i_cost_type_id IN NUMBER,
1825 i_cost_group_id IN NUMBER,
1826 i_txn_id IN NUMBER,
1827 i_item_id IN NUMBER,
1828 i_conv_rate IN NUMBER,
1829 i_user_id IN NUMBER,
1830 i_login_id IN NUMBER,
1831 i_request_id IN NUMBER,
1832 i_prog_id IN NUMBER,
1833 i_prog_appl_id IN NUMBER,
1834 x_prev_period_id OUT NOCOPY NUMBER,
1835 x_prev_period_cost OUT NOCOPY NUMBER,
1836 o_err_num OUT NOCOPY NUMBER,
1837 o_err_code OUT NOCOPY VARCHAR2,
1838 o_err_msg OUT NOCOPY VARCHAR2
1839 )
1840 IS
1841 l_stmt_num NUMBER := 0;
1842 l_err_num NUMBER;
1843 l_err_code VARCHAR2(240);
1844 l_err_msg VARCHAR2(240);
1845
1846 l_prev_period_id NUMBER;
1847 l_prev_period_pwac_cost NUMBER;
1848 l_prev_period_pwac_cnt NUMBER := 0;
1849
1850
1851 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.get_prev_period_cost';
1852
1853
1854 BEGIN
1855
1856 IF l_debug = 'Y' THEN
1857 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
1858 || ' transaction_id: ' || i_txn_id);
1859 END IF;
1860
1861 -- initialize local variables
1862 l_err_num := 0;
1863 l_err_code := '';
1864 l_err_msg := '';
1865
1866 l_stmt_num := 10;
1867
1868 /* Get prior period id */
1869 SELECT nvl(max(cpp.pac_period_id), -1)
1870 INTO l_prev_period_id
1871 FROM cst_pac_periods cpp
1872 WHERE cpp.cost_type_id = i_cost_type_id
1873 AND cpp.legal_entity = i_legal_entity
1874 AND cpp.pac_period_id < i_pac_period_id;
1875
1876 l_stmt_num := 20;
1877 /* In addition to checking whether a prior period exists,
1878 also need to check whether the item itself has a cost
1879 in the prior period. It may not have a prior period cost
1880 if, for example, it was created after the close of the
1881 prior period. */
1882 SELECT count(*)
1883 INTO l_prev_period_pwac_cnt
1884 FROM cst_pac_item_costs cpic
1885 WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
1886 AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
1887 AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
1888
1889 l_stmt_num := 30;
1890
1891 IF (l_prev_period_id <> -1 AND l_prev_period_pwac_cnt > 0) THEN
1892
1893 /* Prior period exists */
1894
1895 l_stmt_num := 40;
1896
1897 INSERT INTO MTL_PAC_TXN_COST_DETAILS (
1898 transaction_id,
1899 pac_period_id,
1900 cost_type_id,
1901 cost_group_id,
1902 cost_element_id,
1903 level_type,
1904 inventory_item_id,
1905 transaction_cost,
1906 last_update_date,
1907 last_updated_by,
1908 creation_date,
1909 created_by,
1910 request_id,
1911 program_application_id,
1912 program_id,
1913 program_update_date,
1914 last_update_login)
1915 SELECT
1916 I_TXN_ID,
1917 I_PAC_PERIOD_ID,
1918 I_COST_TYPE_ID,
1919 I_COST_GROUP_ID,
1920 CPICD.cost_element_id,
1921 CPICD.level_type,
1922 i_item_id,
1923 (CPICD.item_cost * i_conv_rate),
1924 sysdate,
1925 i_user_id,
1926 sysdate,
1927 i_user_id,
1928 i_request_id,
1929 i_prog_appl_id,
1930 i_prog_id,
1931 sysdate,
1932 i_login_id
1933 FROM CST_PAC_ITEM_COSTS CPIC,
1934 CST_PAC_ITEM_COST_DETAILS CPICD
1935 WHERE CPICD.COST_LAYER_ID = CPIC.COST_LAYER_ID
1936 AND CPIC.INVENTORY_ITEM_ID = i_item_id
1937 AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
1938 AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
1939
1940 l_stmt_num := 50;
1941
1942 /* Get prior period PWAC Cost */
1943 SELECT nvl(CPIC.item_cost,0)
1944 INTO l_prev_period_pwac_cost
1945 FROM CST_PAC_ITEM_COSTS CPIC
1946 WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
1947 AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
1948 AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
1949
1950 l_stmt_num := 60;
1951
1952 x_prev_period_id := l_prev_period_id;
1953 x_prev_period_cost := l_prev_period_pwac_cost;
1954
1955 ELSE /* There is no prior period cost */
1956
1957 l_stmt_num := 100;
1958 x_prev_period_id := -1;
1959 x_prev_period_cost := NULL;
1960
1961 END IF; /* (l_prev_period_id <> -1) */
1962
1963 l_stmt_num := 200;
1964
1965 IF l_debug = 'Y' THEN
1966 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
1967 END IF;
1968
1969 EXCEPTION
1970
1971 when OTHERS then
1972 o_err_num := SQLCODE;
1973 o_err_msg := 'CSTPPINV.get_prev_period_cost (' || to_char(l_stmt_num) ||
1974 '): ' || substr(SQLERRM,1,200);
1975 fnd_file.put_line(fnd_file.log, o_err_msg);
1976
1977 END get_prev_period_cost;
1978
1979 /*---------------------------------------------------------------------------
1980 | Procedure get_perp_ship_cost()
1981 |
1982 | This is a helper routine for get_interorg_cost.
1983 | Given the shipment transaction id, this procedure inserts the perpetual
1984 | shipment cost from MTA into MPTCD,
1985 | plus any transfer and transportation charges as appropriate.
1986 |
1987 ---------------------------------------------------------------------------*/
1988 PROCEDURE get_perp_ship_cost(
1989 i_pac_period_id IN NUMBER,
1990 i_cost_type_id IN NUMBER,
1991 i_cost_group_id IN NUMBER,
1992 i_txn_id IN NUMBER,
1993 i_mta_txn_id IN NUMBER,
1994 i_item_id IN NUMBER,
1995 i_from_org IN NUMBER,
1996 i_conv_rate IN NUMBER,
1997 i_user_id IN NUMBER,
1998 i_login_id IN NUMBER,
1999 i_request_id IN NUMBER,
2000 i_prog_id IN NUMBER,
2001 i_prog_appl_id IN NUMBER,
2002 x_perp_ship_cost OUT NOCOPY NUMBER,
2003 o_err_num OUT NOCOPY NUMBER,
2004 o_err_code OUT NOCOPY VARCHAR2,
2005 o_err_msg OUT NOCOPY VARCHAR2
2006 )
2007 IS
2008 l_stmt_num NUMBER := 0;
2009 l_err_num NUMBER;
2010 l_err_code VARCHAR2(240);
2011 l_err_msg VARCHAR2(240);
2012
2013 l_perp_ship_cost NUMBER;
2014 l_mta_txn_qty NUMBER;
2015
2016 l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.get_perp_ship_cost';
2017
2018 BEGIN
2019
2020 IF l_debug = 'Y' THEN
2021 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': begin <<'
2022 || ' transaction_id: ' || i_txn_id
2023 || ' i_mta_txn_id: ' || i_mta_txn_id);
2024 END IF;
2025
2026 -- initialize local variables
2027 l_err_num := 0;
2028 l_err_code := '';
2029 l_err_msg := '';
2030
2031 l_stmt_num := 10;
2032
2033 SELECT primary_quantity
2034 INTO l_mta_txn_qty
2035 FROM mtl_material_transactions
2036 WHERE transaction_id = i_mta_txn_id;
2037
2038 IF l_debug = 'Y' THEN
2039 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': l_mta_txn_qty = ' || l_mta_txn_qty);
2040 END IF;
2041
2042 /* Select the perpetual accounted value for the sending org's credit
2043 to On-hand (either Inventory or Expense accounting line type). */
2044 BEGIN
2045 SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0)))/abs(l_mta_txn_qty),0)
2046 INTO l_perp_ship_cost
2047 FROM mtl_transaction_accounts mta
2048 WHERE mta.transaction_id = i_mta_txn_id
2049 and mta.organization_id = i_from_org
2050 and mta.accounting_line_type IN (1,2)
2051 and mta.base_transaction_value < 0;
2052 EXCEPTION
2053 WHEN no_data_found THEN
2054 l_perp_ship_cost := 0;
2055 END;
2056
2057 IF l_debug = 'Y' THEN
2058 fnd_file.put_line(fnd_file.log, l_api_name || ': ' || l_stmt_num || ': l_perp_ship_cost = ' || l_perp_ship_cost);
2059 END IF;
2060
2061 IF l_perp_ship_cost <> 0 THEN
2062 /* Insert all cost elements */
2063 INSERT INTO mtl_pac_txn_cost_details (
2064 transaction_id,
2065 pac_period_id,
2066 cost_type_id,
2067 cost_group_id,
2068 cost_element_id,
2069 level_type,
2070 inventory_item_id,
2071 transaction_cost,
2072 last_update_date,
2073 last_updated_by,
2074 creation_date,
2075 created_by,
2076 request_id,
2077 program_application_id,
2078 program_id,
2079 program_update_date,
2080 last_update_login)
2081 SELECT
2082 i_txn_id,
2083 i_pac_period_id,
2084 i_cost_type_id,
2085 i_cost_group_id,
2086 NVL(mta.cost_element_id, 1),
2087 1,
2088 i_item_id,
2089 i_conv_rate*abs(mta.base_transaction_value/abs(l_mta_txn_qty)),
2090 SYSDATE,
2091 i_user_id,
2092 SYSDATE,
2093 i_user_id,
2094 i_request_id,
2095 i_prog_appl_id,
2096 i_prog_id,
2097 SYSDATE,
2098 i_login_id
2099 FROM
2100 mtl_transaction_accounts mta
2101 WHERE
2102 mta.transaction_id = i_mta_txn_id
2103 and mta.organization_id = i_from_org
2104 and mta.accounting_line_type IN (1,2)
2105 and mta.base_transaction_value < 0;
2106
2107 ELSE
2108 /* Insert 0 Material Cost into MPTCD only. */
2109 CSTPPINV.insert_elemental_cost(
2110 i_pac_period_id => i_pac_period_id,
2111 i_cost_type_id => i_cost_type_id,
2112 i_cost_group_id => i_cost_group_id,
2113 i_txn_id => i_txn_id,
2114 i_item_id => i_item_id,
2115 i_cost_element_id => 1,
2116 i_level_type => 1,
2117 i_cost => 0,
2118 i_user_id => i_user_id,
2119 i_login_id => i_login_id,
2120 i_request_id => i_request_id,
2121 i_prog_id => i_prog_id,
2122 i_prog_appl_id => i_prog_appl_id,
2123 o_err_num => l_err_num,
2124 o_err_code => l_err_code,
2125 o_err_msg => l_err_msg
2126 );
2127 END IF;
2128
2129 l_stmt_num := 30;
2130 x_perp_ship_cost := l_perp_ship_cost;
2131
2132 IF l_debug = 'Y' THEN
2133 fnd_file.put_line(fnd_file.log,l_api_name || ': ' || l_stmt_num || ': return >>');
2134 END IF;
2135
2136 EXCEPTION
2137
2138 when OTHERS then
2139 o_err_num := SQLCODE;
2140 o_err_msg := 'CSTPPINV.get_perp_ship_cost (' || to_char(l_stmt_num) ||
2141 '): ' || substr(SQLERRM,1,200);
2142 fnd_file.put_line(fnd_file.log, o_err_msg);
2143
2144 END get_perp_ship_cost;
2145
2146
2147
2148 /*---------------------------------------------------------------------------
2149 | Procedure get_snd_rcv_rate()
2150 |
2151 | Returns the currency conversion rate from i_from_org to i_to_org for
2152 | the currency conversion type stamped on i_txn_id.
2153 ---------------------------------------------------------------------------*/
2154 PROCEDURE get_snd_rcv_rate(
2155 i_txn_id IN NUMBER,
2156 i_from_org IN NUMBER,
2157 i_to_org IN NUMBER,
2158 o_conv_rate OUT NOCOPY NUMBER,
2159 o_err_num OUT NOCOPY NUMBER,
2160 o_err_code OUT NOCOPY VARCHAR2,
2161 o_err_msg OUT NOCOPY VARCHAR2
2162 )
2163 IS
2164 l_snd_sob_id NUMBER;
2165 l_snd_curr VARCHAR2(10);
2166 l_rcv_sob_id NUMBER;
2167 l_rcv_curr VARCHAR2(10);
2168 l_curr_type VARCHAR2(30);
2169 l_conv_rate NUMBER;
2170 l_conv_date DATE;
2171 l_txn_date DATE;
2172 l_err_num NUMBER;
2173 l_err_code VARCHAR2(240);
2174 l_err_msg VARCHAR2(240);
2175 l_stmt_num NUMBER;
2176
2177 BEGIN
2178 -- initialize local variables
2179 l_err_num := 0;
2180 l_err_code := '';
2181 l_err_msg := '';
2182
2183 l_stmt_num := 10;
2184
2185 SELECT org_information1
2186 INTO l_snd_sob_id
2187 FROM hr_organization_information
2188 WHERE organization_id = i_from_org
2189 and org_information_context = 'Accounting Information';
2190
2191 l_stmt_num := 20;
2192
2193 SELECT currency_code
2194 INTO l_snd_curr
2195 FROM gl_sets_of_books
2196 WHERE set_of_books_id = l_snd_sob_id;
2197
2198 l_stmt_num := 30;
2199
2200 SELECT org_information1
2201 INTO l_rcv_sob_id
2202 FROM hr_organization_information
2203 WHERE organization_id = i_to_org
2204 and org_information_context = 'Accounting Information';
2205
2206 l_stmt_num := 40;
2207
2208 SELECT currency_code
2209 INTO l_rcv_curr
2210 FROM gl_sets_of_books
2211 WHERE set_of_books_id = l_rcv_sob_id;
2212
2213 l_stmt_num := 50;
2214
2215 SELECT currency_conversion_type, TRUNC(transaction_date)
2216 INTO l_curr_type, l_txn_date
2217 FROM mtl_material_transactions
2218 WHERE transaction_id = i_txn_id;
2219
2220 if (l_curr_type is NULL) then
2221 FND_PROFILE.get('CURRENCY_CONVERSION_TYPE', l_curr_type);
2222 end if;
2223
2224 if (l_rcv_curr <> l_snd_curr) then
2225 l_stmt_num := 60;
2226 l_conv_rate := gl_currency_api.get_rate(l_rcv_sob_id,l_snd_curr,l_txn_date,
2227 l_curr_type);
2228 else
2229 l_conv_rate := 1;
2230 end if;
2231
2232 o_conv_rate := l_conv_rate;
2233
2234 EXCEPTION
2235
2236 when gl_currency_api.NO_RATE then
2237 O_err_num := 9999;
2238 O_err_code := 'CST_NO_GL_RATE';
2239 FND_MESSAGE.set_name('BOM', 'CST_NO_GL_RATE');
2240 O_err_msg := FND_MESSAGE.Get;
2241
2242 when others then
2243 o_err_num := SQLCODE;
2244 o_err_msg := 'CSTPPINV.get_snd_rcv_rate (' || to_char(l_stmt_num) ||
2245 '): ' || substr(SQLERRM, 1,200);
2246
2247 END get_snd_rcv_rate;
2248
2249 PROCEDURE get_from_to_uom(
2250 i_item_id IN NUMBER,
2251 i_from_org IN NUMBER,
2252 i_to_org IN NUMBER,
2253 o_from_uom OUT NOCOPY VARCHAR2,
2254 o_to_uom OUT NOCOPY VARCHAR2,
2255 o_err_num OUT NOCOPY NUMBER,
2256 o_err_code OUT NOCOPY VARCHAR2,
2257 o_err_msg OUT NOCOPY VARCHAR2
2258 )
2259 IS
2260 l_stmt_num NUMBER;
2261
2262 BEGIN
2263 l_stmt_num := 10;
2264
2265 SELECT primary_uom_code
2266 INTO o_from_uom
2267 FROM mtl_system_items
2268 WHERE organization_id = i_from_org
2269 AND inventory_item_id = i_item_id;
2270
2271 l_stmt_num := 20;
2272
2273 SELECT primary_uom_code
2274 INTO o_to_uom
2275 FROM mtl_system_items
2276 WHERE organization_id = i_to_org
2277 AND inventory_item_id = i_item_id;
2278
2279 EXCEPTION
2280 when others then
2281 o_err_num := SQLCODE;
2282 o_err_msg := 'CSTPPINV.get_from_to_uom (' || to_char(l_stmt_num) ||
2283 '): ' || substr(SQLERRM, 1,200);
2284
2285 END get_from_to_uom;
2286
2287 /*--------------------------------------------------------------------------
2288 |
2289 | Cases :
2290 | - Non inter-org or inter-org within cost group transactions having control
2291 | at master will have conversion of 1.
2292 | - Non inter-org transactions.
2293 | Conversion is done from transaction org to the master org of
2294 | that cost group.
2295 |-----------------------------------------------------------------------------*/
2296 PROCEDURE get_um_rate(
2297 i_txn_org_id IN NUMBER,
2298 i_master_org_id IN NUMBER,
2299 i_txn_cost_group_id IN NUMBER,
2300 i_txfr_cost_group_id IN NUMBER,
2301 i_txn_action_id IN NUMBER,
2302 i_item_id IN NUMBER,
2303 i_uom_control IN NUMBER,
2304 i_user_id IN NUMBER,
2305 i_login_id IN NUMBER,
2306 i_request_id IN NUMBER,
2307 i_prog_id IN NUMBER,
2308 i_prog_appl_id IN NUMBER,
2309 o_um_rate OUT NOCOPY NUMBER,
2310 o_err_num OUT NOCOPY NUMBER,
2311 o_err_code OUT NOCOPY VARCHAR2,
2312 o_err_msg OUT NOCOPY VARCHAR2
2313 )
2314 IS
2315 l_txn_org_uom VARCHAR2(3);
2316 l_master_org_uom VARCHAR2(3);
2317 l_err_num NUMBER;
2318 l_err_code VARCHAR2(240);
2319 l_err_msg VARCHAR2(240);
2320 process_error EXCEPTION;
2321 conversion_error EXCEPTION;
2322 BEGIN
2323
2324 IF ( (i_uom_control = 1) AND ((i_txn_action_id NOT IN (3,12,21)) OR
2325 (i_txn_cost_group_id = i_txfr_cost_group_id)) ) THEN
2326 -- dbms_output.put_line('No Conversion');
2327 o_um_rate := 1;
2328 return;
2329 END IF;
2330
2331 get_from_to_uom (i_item_id, i_txn_org_id, i_master_org_id,
2332 l_txn_org_uom,l_master_org_uom,l_err_num, l_err_code, l_err_msg);
2333 IF (l_err_num <> 0) THEN
2334 raise process_error;
2335 END IF;
2336
2337 -- dbms_output.put_line('convert from '||i_txn_org_id||'_'||l_txn_org_uom||' to '
2338 -- ||i_master_org_id||'_'||l_master_org_uom);
2339 o_um_rate := inv_convert.inv_um_convert (i_item_id, NULL, 1,
2340 l_txn_org_uom, l_master_org_uom, NULL, NULL);
2341 IF (o_um_rate < 0) THEN
2342 raise conversion_error;
2343 END IF;
2344
2345
2346 EXCEPTION
2347
2348 when process_error then
2349 o_err_num := l_err_num;
2350 o_err_code := l_err_code;
2351 o_err_msg := l_err_msg;
2352
2353 when conversion_error then
2354 o_err_num := 9999;
2355 o_err_code := 'INV_NO_CONVERSIONS';
2356 FND_MESSAGE.set_name('INV', 'INV_NO_CONVERSIONS');
2357 o_err_msg := FND_MESSAGE.Get;
2358
2359 END get_um_rate;
2360
2361 /*--------------------------------------------------------------------------
2362 | Drop Ship Global Procurement transactions
2363 | Consigned price update transaction
2364 |
2365 | This procedure is used to cost process the logical transactions.
2366 |
2367 | 19-Jul-03 Anju Creation
2368 |-----------------------------------------------------------------------------*/
2369 PROCEDURE cost_acct_events(
2370 i_pac_period_id IN NUMBER,
2371 i_legal_entity IN NUMBER,
2372 i_cost_type_id IN NUMBER,
2373 i_cost_group_id IN NUMBER,
2374 i_cost_method IN NUMBER,
2375 i_txn_id IN NUMBER,
2376 i_item_id IN NUMBER,
2377 i_txn_qty IN NUMBER,
2378 i_txn_org_id IN NUMBER,
2379 i_master_org_id IN NUMBER,
2380 i_uom_control IN NUMBER,
2381 i_user_id IN NUMBER,
2382 i_login_id IN NUMBER,
2383 i_request_id IN NUMBER,
2384 i_prog_id IN NUMBER,
2385 i_prog_appl_id IN NUMBER,
2386 o_err_num OUT NOCOPY NUMBER,
2387 o_err_code OUT NOCOPY VARCHAR2,
2388 o_err_msg OUT NOCOPY VARCHAR2
2389 ) IS
2390
2391 l_err_num NUMBER;
2392 l_err_code VARCHAR2(240);
2393 l_err_msg VARCHAR2(240);
2394 l_parent_transaction_id NUMBER := -1;
2395 l_logical_transaction NUMBER := 3;
2396 l_parent_organization_id NUMBER := -1;
2397 l_um_rate NUMBER := 1;
2398 l_txn_src_type_id MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_TYPE_ID%TYPE;
2399 l_txn_action_id MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ACTION_ID%TYPE;
2400 l_cost_layer_id CST_PAC_ITEM_COSTS.COST_LAYER_ID%TYPE;
2401 l_quantity_layer_id CST_PAC_QUANTITY_LAYERS.COST_LAYER_ID%TYPE;
2402 l_converted_txn_qty NUMBER;
2403 l_stmt_num NUMBER;
2404 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2405 l_msg_count NUMBER;
2406 l_msg_data VARCHAR2(2000);
2407
2408 PROCESS_ERROR EXCEPTION;
2409 begin
2410 l_err_num := 0;
2411 l_err_code := '';
2412 l_err_msg := '';
2413
2414 l_stmt_num := 10;
2415 fnd_file.put_line(fnd_file.log,'In Cost_Acct_Events: Txn ID:' ||
2416 to_char(i_txn_id));
2417
2418 /* Determine parent transaction id and parent_transaction organization id */
2419
2420 select nvl(parent_transaction_id, -1),
2421 transaction_source_type_id,
2422 transaction_action_id
2423 into l_parent_transaction_id,
2424 l_txn_src_type_id,
2425 l_txn_action_id
2426 from mtl_material_transactions
2427 where transaction_id = i_txn_id;
2428
2429 if (l_parent_transaction_id <> -1 ) then
2430
2431 select organization_id,
2432 nvl(logical_transaction, 3)
2433 into l_parent_organization_id,
2434 l_logical_transaction
2435 from mtl_material_transactions
2436 where transaction_id = l_parent_transaction_id;
2437
2438 end if;
2439
2440 /*If the parent is a physical transaction, use parent's details in mptcd, mpacd
2441 for cost processing this logical transaction
2442 Else use mctcd to cost process the logical transaction */
2443
2444 if (l_parent_organization_id = i_txn_org_id and
2445 l_logical_transaction = 2 ) then
2446
2447 l_stmt_num := 20;
2448
2449 /* update mmt.periodic_quantity */
2450 /* Bug 6751847 fix: to prevent execution twice from both
2451 shipping and receiving cost groups,to avoid lock when run in
2452 parallel for multiple CGs */
2453
2454 UPDATE mtl_material_transactions
2455 SET periodic_primary_quantity = (select mmt2.periodic_primary_quantity
2456 from mtl_material_transactions mmt2
2457 where mmt2.transaction_id =
2458 l_parent_transaction_id)
2459 WHERE transaction_id = i_txn_id
2460 AND organization_id = i_txn_org_id
2461 AND EXISTS (SELECT 'x'
2462 FROM cst_cost_group_assignments ccga
2463 WHERE ccga.cost_group_id = i_cost_group_id
2464 AND ccga.organization_id = i_txn_org_id);
2465
2466
2467 l_stmt_num := 30;
2468
2469 /* insert into mpacd */
2470 l_stmt_num := 40;
2471
2472 INSERT INTO mtl_pac_actual_cost_details (
2473 transaction_id,
2474 pac_period_id,
2475 cost_type_id,
2476 cost_group_id,
2477 cost_layer_id,
2478 cost_element_id,
2479 level_type,
2480 last_update_date,
2481 last_updated_by,
2482 creation_date,
2483 created_by,
2484 last_update_login,
2485 request_id,
2486 program_application_id,
2487 program_id,
2488 program_update_date,
2489 inventory_item_id,
2490 actual_cost,
2491 prior_cost,
2492 prior_buy_cost,
2493 prior_make_cost,
2494 new_cost,
2495 new_buy_cost,
2496 new_make_cost,
2497 insertion_flag,
2498 user_entered,
2499 transaction_costed_date)
2500 SELECT i_txn_id,
2501 i_pac_period_id,
2502 i_cost_type_id,
2503 i_cost_group_id,
2504 cost_layer_id,
2505 cost_element_id,
2506 level_type,
2507 sysdate,
2508 i_user_id,
2509 sysdate,
2510 i_user_id,
2511 i_login_id,
2512 i_request_id,
2513 i_prog_appl_id,
2514 i_prog_id,
2515 sysdate,
2516 inventory_item_id,
2517 actual_cost,
2518 prior_cost,
2519 prior_buy_cost,
2520 prior_make_cost,
2521 new_cost,
2522 new_buy_cost,
2523 new_make_cost,
2524 insertion_flag,
2525 user_entered,
2526 sysdate
2527 FROM mtl_pac_actual_cost_details
2528 WHERE transaction_id = l_parent_transaction_id
2529 AND pac_period_id = i_pac_period_id
2530 AND cost_group_id = i_cost_group_id;
2531
2532 /* Delete the MPACD row of the parent */
2533
2534 Delete from mtl_pac_actual_cost_details
2535 where transaction_id = l_parent_transaction_id
2536 AND pac_period_id = i_pac_period_id
2537 AND cost_group_id = i_cost_group_id;
2538
2539 else
2540 l_stmt_num := 50;
2541
2542 /* Update mmt with quantity in the master org um */
2543 /* BUG 6751847 fix: to prevent execution twice
2544 for both shipping and receiving cost group, to avoid
2545 lock when run in parallel for CGs */
2546 UPDATE mtl_material_transactions
2547 SET periodic_primary_quantity = i_txn_qty
2548 WHERE transaction_id = i_txn_id
2549 AND organization_id = i_txn_org_id
2550 AND EXISTS (SELECT 'x'
2551 FROM cst_cost_group_assignments ccga
2552 WHERE ccga.cost_group_id = i_cost_group_id
2553 AND ccga.organization_id = i_txn_org_id);
2554
2555
2556 l_stmt_num := 60;
2557
2558 INSERT INTO mtl_pac_txn_cost_details (
2559 transaction_id,
2560 pac_period_id,
2561 cost_type_id,
2562 cost_group_id,
2563 cost_element_id,
2564 level_type,
2565 inventory_item_id,
2566 transaction_cost,
2567 last_update_date,
2568 last_updated_by,
2569 creation_date,
2570 created_by,
2571 request_id,
2572 program_application_id,
2573 program_id,
2574 program_update_date,
2575 last_update_login)
2576 SELECT
2577 transaction_id,
2578 i_pac_period_id,
2579 i_cost_type_id,
2580 i_cost_group_id,
2581 cost_element_id,
2582 level_type,
2583 inventory_item_id,
2584 transaction_cost/l_um_rate,
2585 SYSDATE,
2586 i_user_id,
2587 SYSDATE,
2588 i_user_id,
2589 i_request_id,
2590 i_prog_appl_id,
2591 i_prog_id,
2592 SYSDATE,
2593 i_login_id
2594 FROM
2595 mtl_cst_txn_cost_details mctcd
2596 WHERE
2597 mctcd.transaction_id = i_txn_id;
2598
2599 /* For logical transactions, values from mptcd can be directly copied into
2600 mpacd */
2601
2602 l_stmt_num := 70;
2603
2604 INSERT INTO mtl_pac_actual_cost_details (
2605 transaction_id,
2606 pac_period_id,
2607 cost_type_id,
2608 cost_group_id,
2609 cost_layer_id,
2610 cost_element_id,
2611 level_type,
2612 last_update_date,
2613 last_updated_by,
2614 creation_date,
2615 created_by,
2616 last_update_login,
2617 request_id,
2618 program_application_id,
2619 program_id,
2620 program_update_date,
2621 inventory_item_id,
2622 actual_cost,
2623 insertion_flag,
2624 user_entered,
2625 transaction_costed_date)
2626 SELECT i_txn_id,
2627 i_pac_period_id,
2628 cost_type_id,
2629 i_cost_group_id,
2630 -1,
2631 cost_element_id,
2632 level_type,
2633 sysdate,
2634 i_user_id,
2635 sysdate,
2636 i_user_id,
2637 i_login_id,
2638 i_request_id,
2639 i_prog_appl_id,
2640 i_prog_id,
2641 sysdate,
2642 inventory_item_id,
2643 transaction_cost,
2644 'Y',
2645 'N',
2646 sysdate
2647 FROM mtl_pac_txn_cost_details
2648 WHERE transaction_id = i_txn_id
2649 AND pac_period_id = i_pac_period_id
2650 AND cost_group_id = i_cost_group_id;
2651
2652 IF (l_txn_src_type_id = 2 AND l_txn_action_id = 7) THEN -- Revenue / COGS Matching
2653
2654 l_stmt_num := 80;
2655 -- check the existence of layer
2656 CSTPPCLM.layer_id(i_pac_period_id, i_legal_entity, i_item_id,
2657 i_cost_group_id, l_cost_layer_id, l_quantity_layer_id,
2658 l_err_num, l_err_code, l_err_msg);
2659 IF (l_err_num <> 0) THEN
2660 raise PROCESS_ERROR;
2661 END IF;
2662
2663 l_stmt_num := 90;
2664 -- create a layer if not exist
2665 IF (l_cost_layer_id = 0) THEN
2666 CSTPPCLM.create_layer(i_pac_period_id, i_legal_entity, i_item_id,
2667 i_cost_group_id, i_user_id, i_login_id, i_request_id,
2668 i_prog_id, i_prog_appl_id,
2669 l_cost_layer_id, l_quantity_layer_id,
2670 l_err_num, l_err_code, l_err_msg);
2671
2672 IF (l_err_num <> 0) THEN
2673 raise PROCESS_ERROR;
2674 END IF;
2675 END IF;
2676
2677 l_stmt_num := 100;
2678 -- logical sales order issue from customer facing org
2679 CST_RevenueCogsMatch_PVT.Insert_PacSoIssue( p_api_version => 1.0,
2680 x_return_status => l_return_status,
2681 x_msg_count => l_msg_count,
2682 x_msg_data => l_msg_data,
2683 p_transaction_id => i_txn_id,
2684 p_layer_id => l_cost_layer_id,
2685 p_cost_type_id => i_cost_type_id,
2686 p_cost_group_id => i_cost_group_id,
2687 p_user_id => i_user_id,
2688 p_login_id => i_login_id,
2689 p_request_id => i_request_id,
2690 p_pgm_app_id => i_prog_appl_id,
2691 p_pgm_id => i_prog_id);
2692
2693 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2694 l_err_num := -1;
2695 IF (l_msg_count = 1) THEN
2696 l_err_msg := substr(l_msg_data,1,240);
2697 ELSE
2698 l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_PacSoIssue()';
2699 END IF;
2700 raise PROCESS_ERROR;
2701 END IF;
2702 END IF;
2703 end if;
2704
2705 EXCEPTION
2706
2707 when PROCESS_ERROR then
2708 rollback;
2709 o_err_num := l_err_num;
2710 o_err_code := l_err_code;
2711 o_err_msg := 'CSTPPINV.COST_ACCT_EVENTS(' || l_stmt_num || ')' || l_err_msg;
2712 when OTHERS then
2713 rollback;
2714 o_err_num := SQLCODE;
2715 o_err_msg := 'CSTPPINV.COST_ACCT_EVENTS: (' || to_char(l_stmt_num) || '): '
2716 || substr(SQLERRM,1,150);
2717
2718
2719
2720 End cost_acct_events;
2721
2722
2723 /* Added procedure get_exp_flag BUG 6751847 performance fix */
2724 PROCEDURE get_exp_flag(
2725 i_item_id IN NUMBER,
2726 i_txn_org_id IN NUMBER,
2727 i_subinventory_code IN VARCHAR2,
2728 o_exp_item OUT NOCOPY NUMBER,
2729 o_exp_flag OUT NOCOPY NUMBER,
2730 o_err_num OUT NOCOPY NUMBER,
2731 o_err_code OUT NOCOPY VARCHAR2,
2732 o_err_msg OUT NOCOPY VARCHAR2
2733 )
2734 IS
2735 l_stmt_num NUMBER;
2736 l_asset_item VARCHAR2(10);
2737 BEGIN
2738 l_stmt_num := 10;
2739
2740
2741 SELECT inventory_asset_flag
2742 INTO l_asset_item
2743 FROM mtl_system_items
2744 WHERE inventory_item_id = i_item_id AND organization_id = i_txn_org_id;
2745
2746
2747 IF (l_asset_item = 'Y') THEN
2748 o_exp_item := 0;
2749 o_exp_flag := 0; -- assignment
2750
2751 SELECT decode(asset_inventory,1,0,1)
2752 INTO o_exp_flag
2753 FROM mtl_secondary_inventories
2754 WHERE secondary_inventory_name = i_subinventory_code
2755 AND organization_id = i_txn_org_id;
2756 ELSE
2757 o_exp_item := 1;
2758 o_exp_flag := 1;
2759 END IF;
2760
2761 EXCEPTION
2762 when others then
2763 o_err_num := SQLCODE;
2764 o_err_msg := 'CSTPPINV.get_exp_flag (' || to_char(l_stmt_num) ||
2765 '): ' || substr(SQLERRM, 1,200);
2766
2767 END get_exp_flag;
2768
2769 -- ===================================================================================================
2770 -- Added procedure "cost_interorg_txn_grp1 " for perf. BUG6751847 to process Inter-Org transactions
2771 -- across cost groups - cost owned transactions
2772 -- ===================================================================================================
2773 PROCEDURE cost_interorg_txn_grp1 (
2774 i_pac_period_id IN NUMBER,
2775 i_legal_entity IN NUMBER,
2776 i_cost_type_id IN NUMBER,
2777 i_cost_group_id IN NUMBER,
2778 i_cost_method IN NUMBER,
2779 i_start_date IN VARCHAR2,
2780 i_end_date IN VARCHAR2,
2781 i_pac_rates_id IN NUMBER,
2782 i_process_group IN NUMBER,
2783 i_master_org_id IN NUMBER,
2784 i_uom_control IN NUMBER,
2785 i_user_id IN NUMBER,
2786 i_login_id IN NUMBER,
2787 i_request_id IN NUMBER,
2788 i_prog_id IN NUMBER,
2789 i_prog_appl_id IN NUMBER,
2790 o_err_num OUT NOCOPY NUMBER,
2791 o_err_code OUT NOCOPY VARCHAR2,
2792 o_err_msg OUT NOCOPY VARCHAR2
2793 )
2794 IS
2795 l_error_num NUMBER;
2796 l_error_code VARCHAR2(240);
2797 l_error_msg VARCHAR2(240);
2798 l_count NUMBER;
2799 l_stmt_num NUMBER;
2800 l_exp_flag NUMBER;
2801 l_exp_item NUMBER;
2802 i_txn_id NUMBER;
2803 i_txn_action_id NUMBER;
2804 i_txn_src_type_id NUMBER;
2805 i_item_id NUMBER;
2806 i_txn_qty NUMBER;
2807 i_txn_org_id NUMBER;
2808 i_txfr_org_id NUMBER;
2809 i_subinventory_code VARCHAR2(240);
2810 i_trf_price NUMBER;
2811
2812 PROCESS_ERROR EXCEPTION;
2813 EXP_FLAG_ERROR EXCEPTION;
2814 CPPB_ERROR EXCEPTION;
2815
2816 l_start_date date;
2817 l_end_date date;
2818 l_rec_count NUMBER;
2819
2820 g_bulk_limit NUMBER := 5000;
2821
2822 TYPE num_type_tab IS TABLE OF NUMBER;
2823 TYPE char_type_tab IS TABLE OF VARCHAR2(100);
2824
2825
2826 txn_id_tab num_type_tab;
2827 txn_act_id_tab num_type_tab;
2828 txn_src_type_tab num_type_tab;
2829 item_id_tab num_type_tab;
2830 primary_qty_tab num_type_tab;
2831 org_id_tab num_type_tab;
2832 trf_org_id_tab num_type_tab;
2833 sub_inv_code_tab char_type_tab;
2834 trf_price_tab num_type_tab;
2835
2836 -- Phase 5 Group 1 Interorg transactions across cost groups
2837 CURSOR inter_trx is
2838 SELECT /*+ LEADING (mmt) */
2839 /* Modified for fob stamping project */
2840 mmt.transaction_id,
2841 mmt.transaction_action_id,
2842 mmt.transaction_source_type_id,
2843 mmt.inventory_item_id,
2844 mmt.primary_quantity,
2845 mmt.organization_id,
2846 nvl(mmt.transfer_organization_id,-1),
2847 mmt.subinventory_code,
2848 nvl(mmt.transfer_price,0) -- INVCONV
2849 FROM
2850 mtl_material_transactions mmt,
2851 mtl_parameters mp --INVCONV sikhanna changes
2852 WHERE
2853 transaction_date between l_start_date AND l_end_date
2854 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
2855 AND nvl(mmt.owning_tp_type,2) = 2
2856 AND mmt.organization_id = mp.organization_id
2857 AND nvl(mp.process_enabled_flag,'N') = 'N' --INVCONV sikhanna
2858 AND NOT EXISTS ( SELECT 'X'
2859 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
2860 WHERE c1.organization_id = mmt.organization_id
2861 AND c2.organization_id = mmt.transfer_organization_id
2862 AND c1.cost_group_id = c2.cost_group_id)
2863 AND (
2864 (mmt.transaction_action_id = 3
2865 AND EXISTS ( SELECT 'X'
2866 FROM cst_cost_group_assignments ccga1
2867 WHERE ccga1.cost_group_id = i_cost_group_id
2868 AND ccga1.organization_id = mmt.organization_id
2869 AND mmt.primary_quantity > 0))
2870 OR (mmt.transaction_action_id = 21
2871 AND EXISTS ( SELECT 'X'
2872 FROM mtl_interorg_parameters mip,
2873 cst_cost_group_assignments ccga2
2874 WHERE mip.from_organization_id = mmt.organization_id
2875 AND mip.to_organization_id = mmt.transfer_organization_id
2876 AND NVL(mmt.fob_point,mip.fob_point) = 1
2877 AND ccga2.organization_id = mip.to_organization_id
2878 AND ccga2.cost_group_id = i_cost_group_id))
2879 OR (mmt.transaction_action_id = 12
2880 AND EXISTS ( SELECT 'X'
2881 FROM mtl_interorg_parameters mip,
2882 cst_cost_group_assignments ccga2
2883 WHERE mip.from_organization_id = mmt.transfer_organization_id
2884 AND mip.to_organization_id = mmt.organization_id
2885 AND NVL(mmt.fob_point,mip.fob_point) = 2
2886 AND ccga2.organization_id = mip.to_organization_id
2887 AND ccga2.cost_group_id = i_cost_group_id))
2888 /* Logical Intransit Receipt for receiving organization cost group */ -- INVCONV sikhanna
2889 OR (mmt.transaction_action_id = 15
2890 AND EXISTS ( SELECT 'X'
2891 FROM cst_cost_group_assignments ccga0
2892 WHERE ccga0.organization_id = mmt.organization_id
2893 AND ccga0.cost_group_id = i_cost_group_id))
2894 )
2895 ORDER BY inventory_item_id;
2896
2897 BEGIN
2898 -- initialize local variables
2899 l_error_num := 0;
2900 l_error_code := '';
2901 l_error_msg := '';
2902 l_start_date:=to_date(i_start_date, 'YYYY/MM/DD HH24:MI:SS') ;
2903 l_end_date :=to_date(i_end_date, 'YYYY/MM/DD HH24:MI:SS') + 0.99999;
2904
2905 l_count :=0;
2906
2907 fnd_file.put_line(fnd_file.LOG, ' Start Processing group 1 - interorg txns across cost groups... '
2908 || TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
2909
2910 l_stmt_num := 10;
2911
2912 OPEN inter_trx;
2913
2914 LOOP
2915
2916 FETCH inter_trx BULK COLLECT
2917 INTO
2918 txn_id_tab,
2919 txn_act_id_tab,
2920 txn_src_type_tab,
2921 item_id_tab,
2922 primary_qty_tab,
2923 org_id_tab,
2924 trf_org_id_tab,
2925 sub_inv_code_tab,
2926 trf_price_tab
2927 LIMIT g_bulk_limit;
2928
2929 l_rec_count := item_id_tab.COUNT;
2930
2931 IF l_rec_count > 0 then
2932 FOR i in 1.. l_rec_count LOOP
2933 i_txn_id := txn_id_tab(i);
2934 i_txn_action_id := txn_act_id_tab(i);
2935 i_txn_src_type_id := txn_src_type_tab(i);
2936 i_item_id := item_id_tab(i);
2937 i_txn_qty := primary_qty_tab(i);
2938 i_txn_org_id := org_id_tab(i);
2939 i_txfr_org_id := trf_org_id_tab(i);
2940 i_subinventory_code := sub_inv_code_tab(i);
2941 i_trf_price := trf_price_tab(i);
2942
2943 fnd_file.put_line(fnd_file.log,'txn_id:'||i_txn_id);
2944 fnd_file.put_line(fnd_file.log,'txn_action_id:'||i_txn_action_id);
2945 fnd_file.put_line(fnd_file.log,'txn_source_type_id:'||i_txn_src_type_id);
2946 fnd_file.put_line(fnd_file.log,'item_id:'||i_item_id);
2947
2948 /* Any intransit shipments in this cursor are FOB shipment processed by receiving CG,
2949 so we should be passing in the txfr_txn_org_id to determine expense status.
2950 Since this step hits receiving org's intransit, it is assumed to be asset sub,
2951 so we pass in i_subinventory_code = -1 */
2952 IF (i_txn_action_id = 21) THEN
2953 get_exp_flag(i_item_id, i_txfr_org_id, i_subinventory_code, l_exp_item,
2954 l_exp_flag, l_error_num, l_error_code, l_error_msg);
2955 ELSE
2956 get_exp_flag(i_item_id, i_txn_org_id, i_subinventory_code, l_exp_item,
2957 l_exp_flag, l_error_num, l_error_code, l_error_msg);
2958 END IF;
2959
2960 /*BUG 7415281*/
2961
2962 l_error_num := NVL(l_error_num, 0);
2963 l_error_code := NVL(l_error_code, 'No Error');
2964 l_error_msg := NVL(l_error_msg, 'No Error');
2965
2966 IF (l_error_num <> 0) THEN
2967 raise EXP_FLAG_ERROR;
2968 END IF;
2969
2970 l_stmt_num := 20;
2971
2972 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000 AND i_cost_method <> 4) THEN
2973 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
2974 i_cost_group_id => i_cost_group_id,
2975 i_txn_category => 3, /* Cost Owned Transactions */
2976 i_user_id => i_user_id,
2977 i_login_id => i_login_id,
2978 i_request_id => i_request_id,
2979 i_prog_id => i_prog_id,
2980 i_prog_appl_id => i_prog_appl_id,
2981 o_err_num => l_error_num,
2982 o_err_code => l_error_code,
2983 o_err_msg => l_error_msg);
2984
2985 l_error_num := NVL(l_error_num, 0);
2986 l_error_code := NVL(l_error_code, 'No Error');
2987 l_error_msg := NVL(l_error_msg, 'No Error');
2988
2989 IF (l_error_num <> 0) THEN
2990 raise CPPB_ERROR;
2991 END IF;
2992
2993 END IF;
2994
2995 l_stmt_num := 30;
2996
2997 IF l_error_num = 0 THEN
2998 CSTPPINV.cost_inv_txn(i_pac_period_id => i_pac_period_id,
2999 i_legal_entity => i_legal_entity,
3000 i_cost_type_id => i_cost_type_id,
3001 i_cost_group_id => i_cost_group_id,
3002 i_cost_method => i_cost_method,
3003 i_txn_id => i_txn_id,
3004 i_txn_action_id => i_txn_action_id,
3005 i_txn_src_type_id => i_txn_src_type_id,
3006 i_item_id => i_item_id,
3007 i_txn_qty => i_txn_qty,
3008 i_txn_org_id => i_txn_org_id,
3009 i_txfr_org_id => i_txfr_org_id,
3010 i_subinventory_code => i_subinventory_code,
3011 i_exp_flag => l_exp_flag,
3012 i_exp_item => l_exp_item,
3013 i_pac_rates_id => i_pac_rates_id,
3014 i_process_group => i_process_group,
3015 i_master_org_id => i_master_org_id,
3016 i_uom_control => i_uom_control,
3017 i_user_id => i_user_id,
3018 i_login_id => i_login_id,
3019 i_request_id => i_request_id,
3020 i_prog_id => i_prog_id,
3021 i_prog_appl_id => i_prog_appl_id,
3022 i_txn_category => 3, /* Cost Owned Transactions */
3023 i_transfer_price_pd => i_trf_price,
3024 o_err_num => l_error_num,
3025 o_err_code => l_error_code,
3026 o_err_msg => l_error_msg);
3027
3028 l_error_num := NVL(l_error_num, 0);
3029 l_error_code := NVL(l_error_code, 'No Error');
3030 l_error_msg := NVL(l_error_msg, 'No Error');
3031
3032 IF (l_error_num <> 0) THEN
3033 raise PROCESS_ERROR;
3034 END IF;
3035 END IF;
3036
3037 END LOOP;
3038
3039 l_count := l_count + l_rec_count;
3040
3041 END IF;
3042 EXIT WHEN inter_trx%NOTFOUND;
3043 END LOOP;
3044 CLOSE inter_trx;
3045
3046 fnd_file.put_line(fnd_file.LOG, 'COMPLETE Processing group 1 - interorg txns across cost groups:'
3047 ||l_count||'txns...'|| TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
3048
3049
3050 l_error_num := 0;
3051
3052 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND i_cost_method <> 4) THEN
3053 /* more records left out in the PL/SQL tables, Move them to permanent tables
3054 and clear PL/SQL tables */
3055 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
3056 ,i_cost_group_id => i_cost_group_id
3057 ,i_txn_category => 3 /* cost owned transactions */
3058 ,i_user_id => i_user_id
3059 ,i_login_id => i_login_id
3060 ,i_request_id => i_request_id
3061 ,i_prog_id => i_prog_id
3062 ,i_prog_appl_id => i_prog_appl_id
3063 ,o_err_num => l_error_num
3064 ,o_err_code => l_error_code
3065 ,o_err_msg => l_error_msg
3066 );
3067
3068 l_error_num := NVL(l_error_num, 0);
3069 l_error_code := NVL(l_error_code, 'No Error');
3070 l_error_msg := NVL(l_error_msg, 'No Error');
3071
3072 IF (l_error_num <> 0) THEN
3073 raise CPPB_ERROR;
3074 END IF;
3075
3076 END IF;
3077
3078 IF (l_error_num = 0 AND i_cost_method <> 4 and l_count > 0) THEN
3079 /* l_count > 0 implies that there has been atleast one cost owned
3080 transaction processed upto this point in inter_trx cursor */
3081
3082 CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
3083 i_cost_group_id => i_cost_group_id,
3084 i_txn_category => 3, /* Cost Owned Transactions */
3085 i_low_level_code => -2,
3086 i_user_id => i_user_id,
3087 i_login_id => i_login_id,
3088 i_request_id => i_request_id,
3089 i_prog_id => i_prog_id,
3090 i_prog_appl_id => i_prog_appl_id,
3091 o_err_num => l_error_num,
3092 o_err_code => l_error_code,
3093 o_err_msg => l_error_msg);
3094
3095 l_error_num := nvl(l_error_num, 0);
3096 l_error_msg := nvl(l_error_msg, 'No Error');
3097 l_error_code := nvl(l_error_code, 'No Error');
3098
3099 IF l_error_num <> 0 THEN
3100 raise CPPB_ERROR;
3101 END IF;
3102
3103 END IF;
3104
3105
3106 EXCEPTION
3107 WHEN PROCESS_ERROR THEN
3108 o_err_num := l_error_num;
3109 o_err_code := l_error_code;
3110 o_err_msg := 'CSTPPINV.COST_INV_TXN:' || l_error_msg;
3111 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV inter_trx');
3112 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
3113
3114 WHEN CPPB_ERROR THEN
3115 o_err_num := l_error_num;
3116 o_err_code := l_error_code;
3117 o_err_msg := 'CSTPPINV:' || l_error_msg;
3118 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV while insert or update CPPB');
3119
3120 WHEN EXP_FLAG_ERROR THEN
3121 o_err_num := l_error_num;
3122 o_err_code := l_error_code;
3123 o_err_msg := 'CSTPPINV.get_exp_flag:' || l_error_msg;
3124 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.get_exp_flag');
3125 fnd_file.put_line(fnd_file.log,'item id:'||i_item_id || ' txn action id:' || i_txn_action_id
3126 || ' organization id:' || i_txn_org_id || ' transfer org id:' || i_txfr_org_id);
3127
3128 WHEN OTHERS THEN
3129 o_err_num := SQLCODE;
3130 o_err_msg := 'CSTPPINV (' || to_char(l_stmt_num) || '): '
3131 || substr(SQLERRM,1,150);
3132 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.cost_interorg_txn_grp1');
3133 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
3134
3135 END cost_interorg_txn_grp1;
3136
3137 -- ===================================================================================================
3138 -- Added procedure "cost_interorg_txn_grp2 " for perf. BUG6751847 to process Inter-Org transactions
3139 -- across cost groups - cost derived transactions */
3140 -- ===================================================================================================
3141 PROCEDURE cost_interorg_txn_grp2 (
3142 i_pac_period_id IN NUMBER,
3143 i_legal_entity IN NUMBER,
3144 i_cost_type_id IN NUMBER,
3145 i_cost_group_id IN NUMBER,
3146 i_cost_method IN NUMBER,
3147 i_start_date IN VARCHAR2,
3148 i_end_date IN VARCHAR2,
3149 i_pac_rates_id IN NUMBER,
3150 i_process_group IN NUMBER,
3151 i_master_org_id IN NUMBER,
3152 i_uom_control IN NUMBER,
3153 i_user_id IN NUMBER,
3154 i_login_id IN NUMBER,
3155 i_request_id IN NUMBER,
3156 i_prog_id IN NUMBER,
3157 i_prog_appl_id IN NUMBER,
3158 o_err_num OUT NOCOPY NUMBER,
3159 o_err_code OUT NOCOPY VARCHAR2,
3160 o_err_msg OUT NOCOPY VARCHAR2
3161 )
3162 IS
3163 l_error_num NUMBER;
3164 l_error_code VARCHAR2(240);
3165 l_error_msg VARCHAR2(240);
3166 l_count NUMBER;
3167 l_stmt_num NUMBER;
3168 l_exp_flag NUMBER;
3169 l_exp_item NUMBER;
3170 i_txn_id NUMBER;
3171 i_txn_action_id NUMBER;
3172 i_txn_src_type_id NUMBER;
3173 i_item_id NUMBER;
3174 i_txn_qty NUMBER;
3175 i_txn_org_id NUMBER;
3176 i_txfr_org_id NUMBER;
3177 i_subinventory_code VARCHAR2(240);
3178 i_trf_price NUMBER;
3179
3180 PROCESS_ERROR EXCEPTION;
3181 EXP_FLAG_ERROR EXCEPTION;
3182 CPPB_ERROR EXCEPTION;
3183
3184 l_start_date date;
3185 l_end_date date;
3186 l_rec_count NUMBER;
3187
3188 g_bulk_limit NUMBER := 5000;
3189
3190 TYPE num_type_tab IS TABLE OF NUMBER;
3191 TYPE char_type_tab IS TABLE OF VARCHAR2(100);
3192
3193
3194 txn_id_tab num_type_tab;
3195 txn_act_id_tab num_type_tab;
3196 txn_src_type_tab num_type_tab;
3197 item_id_tab num_type_tab;
3198 primary_qty_tab num_type_tab;
3199 org_id_tab num_type_tab;
3200 trf_org_id_tab num_type_tab;
3201 sub_inv_code_tab char_type_tab;
3202 trf_price_tab num_type_tab;
3203
3204
3205 -- ====================================================================
3206 -- Phase 5 Group 2 - no completion , InterOrg txns across CG
3207 -- ====================================================================
3208 CURSOR group2_interorg_trx is
3209 SELECT /*+ LEADING (mmt) */
3210 mmt.transaction_id,
3211 mmt.transaction_action_id,
3212 mmt.transaction_source_type_id,
3213 mmt.inventory_item_id,
3214 mmt.primary_quantity,
3215 mmt.organization_id,
3216 nvl(mmt.transfer_organization_id,-1),
3217 mmt.subinventory_code,
3218 nvl(mmt.transfer_price,0) -- INVCONV
3219 FROM
3220 mtl_material_transactions mmt,
3221 mtl_parameters mp -- INCONV sikhanna
3222 WHERE
3223 transaction_date between l_start_date AND l_end_date
3224 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3225 AND nvl(mmt.owning_tp_type,2) = 2
3226 AND mmt.organization_id = mp.organization_id
3227 AND nvl(mp.process_enabled_flag,'N') = 'N'
3228 AND ((transaction_action_id in (3,12,21)
3229 AND EXISTS (SELECT 'EXISTS'
3230 FROM cst_cost_group_assignments ccga
3231 WHERE ccga.cost_group_id = i_cost_group_id
3232 AND (ccga.organization_id = mmt.organization_id OR
3233 ccga.organization_id = mmt.transfer_organization_id)))
3234 /* Logical Intransit Shipment for shipping organization cost group */ -- INVCONV sikhanna
3235 OR (mmt.transaction_action_id = 22
3236 AND EXISTS ( SELECT 'X'
3237 FROM cst_cost_group_assignments ccga0
3238 WHERE ccga0.organization_id = mmt.organization_id
3239 AND ccga0.cost_group_id = i_cost_group_id)))
3240 AND (transaction_action_id IN (3,12,21,22)
3241 AND NOT EXISTS (
3242 SELECT 'X'
3243 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
3244 WHERE c1.organization_id = mmt.organization_id
3245 AND c2.organization_id = mmt.transfer_organization_id
3246 AND c1.cost_group_id = c2.cost_group_id)
3247 AND (
3248 (mmt.transaction_action_id = 3
3249 AND EXISTS (
3250 SELECT 'X'
3251 FROM cst_cost_group_assignments ccga1
3252 WHERE ccga1.cost_group_id = i_cost_group_id
3253 AND ccga1.organization_id = mmt.organization_id
3254 AND mmt.primary_quantity < 0))
3255 OR (mmt.transaction_action_id = 21
3256 AND EXISTS (
3257 SELECT 'X'
3258 FROM cst_cost_group_assignments ccga2
3259 WHERE ccga2.organization_id = mmt.organization_id
3260 AND ccga2.cost_group_id = i_cost_group_id))
3261 OR (mmt.transaction_action_id = 12
3262 AND EXISTS (
3263 SELECT 'X'
3264 FROM mtl_interorg_parameters mip
3265 WHERE mip.from_organization_id = mmt.transfer_organization_id
3266 AND mip.to_organization_id = mmt.organization_id
3267 AND (
3268 (NVL(mmt.fob_point,mip.fob_point) = 1 AND EXISTS (
3269 SELECT 'X'
3270 FROM cst_cost_group_assignments ccga2
3271 WHERE ccga2.organization_id = mip.to_organization_id
3272 AND ccga2.cost_group_id = i_cost_group_id ))
3273 Or (NVL(mmt.fob_point,mip.fob_point) = 2 AND EXISTS (
3274 SELECT 'X'
3275 FROM cst_cost_group_assignments ccga3
3276 WHERE ccga3.organization_id = mip.from_organization_id
3277 AND ccga3.cost_group_id = i_cost_group_id )))))
3278 /* Logical Intransit Shipment for shipping organization cost group */
3279 OR (mmt.transaction_action_id = 22
3280 AND EXISTS ( SELECT 'X'
3281 FROM cst_cost_group_assignments ccga0
3282 WHERE ccga0.organization_id = mmt.organization_id
3283 AND ccga0.cost_group_id = i_cost_group_id))
3284 ))
3285 AND NOT EXISTS (
3286 SELECT 'X'
3287 FROM cst_pac_low_level_codes cpllc
3288 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
3289 AND cpllc.pac_period_id = i_pac_period_id
3290 AND cpllc.cost_group_id = i_cost_group_id)
3291 ORDER BY inventory_item_id;
3292
3293 BEGIN
3294 -- initialize local variables
3295 l_error_num := 0;
3296 l_error_code := '';
3297 l_error_msg := '';
3298 l_start_date:=to_date(i_start_date, 'YYYY/MM/DD HH24:MI:SS') ;
3299 l_end_date :=to_date(i_end_date, 'YYYY/MM/DD HH24:MI:SS') + 0.99999;
3300
3301 l_count :=0;
3302
3303 fnd_file.put_line(fnd_file.LOG, ' Start Processing group 2 - interorg txns across cost groups... '
3304 || TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
3305
3306 l_stmt_num := 10;
3307
3308 OPEN group2_interorg_trx;
3309
3310 LOOP
3311
3312 FETCH group2_interorg_trx BULK COLLECT
3313 INTO
3314 txn_id_tab,
3315 txn_act_id_tab,
3316 txn_src_type_tab,
3317 item_id_tab,
3318 primary_qty_tab,
3319 org_id_tab,
3320 trf_org_id_tab,
3321 sub_inv_code_tab,
3322 trf_price_tab
3323 LIMIT g_bulk_limit;
3324
3325 l_rec_count := item_id_tab.COUNT;
3326
3327 IF l_rec_count > 0 then
3328 FOR i in 1.. l_rec_count LOOP
3329 i_txn_id := txn_id_tab(i);
3330 i_txn_action_id := txn_act_id_tab(i);
3331 i_txn_src_type_id := txn_src_type_tab(i);
3332 i_item_id := item_id_tab(i);
3333 i_txn_qty := primary_qty_tab(i);
3334 i_txn_org_id := org_id_tab(i);
3335 i_txfr_org_id := trf_org_id_tab(i);
3336 i_subinventory_code := sub_inv_code_tab(i);
3337 i_trf_price := trf_price_tab(i);
3338
3339 fnd_file.put_line(fnd_file.log,'txn_id:'||i_txn_id);
3340 fnd_file.put_line(fnd_file.log,'txn_action_id:'||i_txn_action_id);
3341 fnd_file.put_line(fnd_file.log,'txn_source_type_id:'||i_txn_src_type_id);
3342 fnd_file.put_line(fnd_file.log,'item_id:'||i_item_id);
3343
3344 get_exp_flag(i_item_id, i_txn_org_id, i_subinventory_code, l_exp_item,
3345 l_exp_flag, l_error_num, l_error_code, l_error_msg);
3346
3347 /*BUG7415281*/
3348
3349 l_error_num := NVL(l_error_num, 0);
3350 l_error_code := NVL(l_error_code, 'No Error');
3351 l_error_msg := NVL(l_error_msg, 'No Error');
3352
3353 IF (l_error_num <> 0) THEN
3354 raise EXP_FLAG_ERROR;
3355 END IF;
3356
3357 l_stmt_num := 20;
3358
3359 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000 AND i_cost_method <> 4) THEN
3360 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
3361 i_cost_group_id => i_cost_group_id,
3362 i_txn_category => 9, /* Cost Derived Transactions */
3363 i_user_id => i_user_id,
3364 i_login_id => i_login_id,
3365 i_request_id => i_request_id,
3366 i_prog_id => i_prog_id,
3367 i_prog_appl_id => i_prog_appl_id,
3368 o_err_num => l_error_num,
3369 o_err_code => l_error_code,
3370 o_err_msg => l_error_msg);
3371
3372 l_error_num := NVL(l_error_num, 0);
3373 l_error_code := NVL(l_error_code, 'No Error');
3374 l_error_msg := NVL(l_error_msg, 'No Error');
3375
3376 IF (l_error_num <> 0) THEN
3377 raise CPPB_ERROR;
3378 END IF;
3379
3380 END IF;
3381
3382 l_stmt_num := 30;
3383
3384 IF l_error_num = 0 THEN
3385 CSTPPINV.cost_inv_txn(i_pac_period_id => i_pac_period_id,
3386 i_legal_entity => i_legal_entity,
3387 i_cost_type_id => i_cost_type_id,
3388 i_cost_group_id => i_cost_group_id,
3389 i_cost_method => i_cost_method,
3390 i_txn_id => i_txn_id,
3391 i_txn_action_id => i_txn_action_id,
3392 i_txn_src_type_id => i_txn_src_type_id,
3393 i_item_id => i_item_id,
3394 i_txn_qty => i_txn_qty,
3395 i_txn_org_id => i_txn_org_id,
3396 i_txfr_org_id => i_txfr_org_id,
3397 i_subinventory_code => i_subinventory_code,
3398 i_exp_flag => l_exp_flag,
3399 i_exp_item => l_exp_item,
3400 i_pac_rates_id => i_pac_rates_id,
3401 i_process_group => i_process_group,
3402 i_master_org_id => i_master_org_id,
3403 i_uom_control => i_uom_control,
3404 i_user_id => i_user_id,
3405 i_login_id => i_login_id,
3406 i_request_id => i_request_id,
3407 i_prog_id => i_prog_id,
3408 i_prog_appl_id => i_prog_appl_id,
3409 i_txn_category => 9, /* Cost Derived Transactions */
3410 i_transfer_price_pd => i_trf_price,
3411 o_err_num => l_error_num,
3412 o_err_code => l_error_code,
3413 o_err_msg => l_error_msg);
3414
3415 l_error_num := NVL(l_error_num, 0);
3416 l_error_code := NVL(l_error_code, 'No Error');
3417 l_error_msg := NVL(l_error_msg, 'No Error');
3418
3419 IF (l_error_num <> 0) THEN
3420 raise PROCESS_ERROR;
3421 END IF;
3422 END IF;
3423
3424 END LOOP;
3425
3426 l_count := l_count + l_rec_count;
3427
3428 END IF;
3429 EXIT WHEN group2_interorg_trx%NOTFOUND;
3430 END LOOP;
3431 CLOSE group2_interorg_trx;
3432
3433 fnd_file.put_line(fnd_file.LOG, 'COMPLETE Processing group 2 - interorg txns across cost groups:'
3434 ||l_count||'txns...'|| TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
3435
3436
3437 l_error_num := 0;
3438
3439 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND i_cost_method <> 4) THEN
3440 /* more records left out in the PL/SQL tables, Move them to permanent tables
3441 and clear PL/SQL tables */
3442 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
3443 ,i_cost_group_id => i_cost_group_id
3444 ,i_txn_category => 9 /* cost derived transactions */
3445 ,i_user_id => i_user_id
3446 ,i_login_id => i_login_id
3447 ,i_request_id => i_request_id
3448 ,i_prog_id => i_prog_id
3449 ,i_prog_appl_id => i_prog_appl_id
3450 ,o_err_num => l_error_num
3451 ,o_err_code => l_error_code
3452 ,o_err_msg => l_error_msg
3453 );
3454
3455 l_error_num := NVL(l_error_num, 0);
3456 l_error_code := NVL(l_error_code, 'No Error');
3457 l_error_msg := NVL(l_error_msg, 'No Error');
3458
3459 IF (l_error_num <> 0) THEN
3460 raise CPPB_ERROR;
3461 END IF;
3462
3463 END IF;
3464
3465 IF (l_error_num = 0 AND i_cost_method <> 4 and l_count > 0) THEN
3466 /* l_count > 0 implies that there has been atleast one cost derived
3467 transaction processed upto this point in group2_interorg_trx cursor */
3468
3469 CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
3470 i_cost_group_id => i_cost_group_id,
3471 i_txn_category => 9, /* Cost Derived Transactions */
3472 i_low_level_code => -1, /* No completions */
3473 i_user_id => i_user_id,
3474 i_login_id => i_login_id,
3475 i_request_id => i_request_id,
3476 i_prog_id => i_prog_id,
3477 i_prog_appl_id => i_prog_appl_id,
3478 o_err_num => l_error_num,
3479 o_err_code => l_error_code,
3480 o_err_msg => l_error_msg);
3481
3482 l_error_num := nvl(l_error_num, 0);
3483 l_error_msg := nvl(l_error_msg, 'No Error');
3484 l_error_code := nvl(l_error_code, 'No Error');
3485
3486 IF l_error_num <> 0 THEN
3487 raise CPPB_ERROR;
3488 END IF;
3489
3490 END IF;
3491
3492
3493 EXCEPTION
3494 WHEN PROCESS_ERROR THEN
3495 o_err_num := l_error_num;
3496 o_err_code := l_error_code;
3497 o_err_msg := 'CSTPPINV.COST_INV_TXN:' || l_error_msg;
3498 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV group2_interorg_trx');
3499 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
3500
3501 WHEN CPPB_ERROR THEN
3502 o_err_num := l_error_num;
3503 o_err_code := l_error_code;
3504 o_err_msg := 'CSTPPINV:' || l_error_msg;
3505 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV while insert or update CPPB');
3506
3507 WHEN EXP_FLAG_ERROR THEN
3508 o_err_num := l_error_num;
3509 o_err_code := l_error_code;
3510 o_err_msg := 'CSTPPINV.get_exp_flag:' || l_error_msg;
3511 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.get_exp_flag');
3512 fnd_file.put_line(fnd_file.log,'item id:'||i_item_id || ' txn action id:' || i_txn_action_id
3513 || ' organization id:' || i_txn_org_id || ' transfer org id:' || i_txfr_org_id);
3514
3515 WHEN OTHERS THEN
3516 o_err_num := SQLCODE;
3517 o_err_msg := 'CSTPPINV (' || to_char(l_stmt_num) || '): '
3518 || substr(SQLERRM,1,150);
3519 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.cost_interorg_txn_grp2');
3520 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
3521
3522 END cost_interorg_txn_grp2;
3523
3524 -- ===================================================================================================
3525 -- Added procedure "cost_txn_grp2 " for perf. BUG6751847 to process Inter-Org transactions within
3526 -- same cost group and other non-interorg transactions - no completion - cost derived transactions
3527 -- ===================================================================================================
3528 PROCEDURE cost_txn_grp2 (
3529 i_pac_period_id IN NUMBER,
3530 i_legal_entity IN NUMBER,
3531 i_cost_type_id IN NUMBER,
3532 i_cost_group_id IN NUMBER,
3533 i_cost_method IN NUMBER,
3534 i_start_date IN VARCHAR2,
3535 i_end_date IN VARCHAR2,
3536 i_pac_rates_id IN NUMBER,
3537 i_process_group IN NUMBER,
3538 i_master_org_id IN NUMBER,
3539 i_uom_control IN NUMBER,
3540 i_mat_relief_algo IN NUMBER,
3541 i_user_id IN NUMBER,
3542 i_login_id IN NUMBER,
3543 i_request_id IN NUMBER,
3544 i_prog_id IN NUMBER,
3545 i_prog_appl_id IN NUMBER,
3546 o_err_num OUT NOCOPY NUMBER,
3547 o_err_code OUT NOCOPY VARCHAR2,
3548 o_err_msg OUT NOCOPY VARCHAR2
3549 )
3550 IS
3551 l_error_num NUMBER;
3552 l_error_code VARCHAR2(240);
3553 l_error_msg VARCHAR2(240);
3554 l_count NUMBER;
3555 l_stmt_num NUMBER;
3556 l_exp_flag NUMBER;
3557 l_exp_item NUMBER;
3558 i_txn_id NUMBER;
3559 i_txn_action_id NUMBER;
3560 i_txn_src_type_id NUMBER;
3561 i_item_id NUMBER;
3562 i_txn_qty NUMBER;
3563 i_txn_org_id NUMBER;
3564 i_txfr_org_id NUMBER;
3565 i_subinventory_code VARCHAR2(240);
3566
3567 PROCESS_ERROR EXCEPTION;
3568 EXP_FLAG_ERROR EXCEPTION;
3569 CPPB_ERROR EXCEPTION;
3570 CHARGE_WIP_ERROR EXCEPTION;
3571
3572 l_start_date date;
3573 l_end_date date;
3574 l_rec_count NUMBER;
3575 l_hook_used NUMBER;
3576
3577 g_bulk_limit NUMBER := 5000;
3578
3579 TYPE num_type_tab IS TABLE OF NUMBER;
3580 TYPE char_type_tab IS TABLE OF VARCHAR2(100);
3581
3582
3583 txn_id_tab num_type_tab;
3584 txn_act_id_tab num_type_tab;
3585 txn_src_type_tab num_type_tab;
3586 item_id_tab num_type_tab;
3587 primary_qty_tab num_type_tab;
3588 org_id_tab num_type_tab;
3589 trf_org_id_tab num_type_tab;
3590 sub_inv_code_tab char_type_tab;
3591
3592 -- =========================================================================
3593 -- Phase 5 processing - Group 2 txns of items having no completion
3594 -- Interorg transactions within same cost group as cost derived transactions
3595 -- Other non-interorg transactions as cost derived transactions
3596 -- NOTE: Interorg transactions across cost groups as group 2 are processed
3597 -- through a separate procedure.
3598 -- ==========================================================================
3599
3600 /* gwu: Performance fix for BUG 1866168
3601 Split the cursor into two sections, one
3602 for interorg and one for non-interorg transactions
3603 NOTE: This code assumes that relevant MMT.transfer_organization_id
3604 is populated only for transaction_action_id 3,12,21 (interorg)
3605
3606 Bug 1987364 - Cursor group2_trx modified for inter-org txns to avoid
3607 duplicate rows getting returned for same txn id .
3608 Join between ccga and mmt replaced with EXISTS clause
3609 Modified for fob stamping project
3610 Modified for Drop Shipment projects */
3611 CURSOR group2_trx is
3612 SELECT /*+ LEADING (mmt) */
3613 mmt.transaction_id,
3614 mmt.transaction_action_id,
3615 mmt.transaction_source_type_id,
3616 mmt.inventory_item_id,
3617 mmt.primary_quantity,
3618 mmt.organization_id,
3619 nvl(mmt.transfer_organization_id,-1),
3620 mmt.subinventory_code
3621 FROM
3622 mtl_material_transactions mmt
3623 WHERE
3624 transaction_date between l_start_date AND l_end_date
3625 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3626 AND nvl(mmt.owning_tp_type,2) = 2
3627 and transaction_action_id in (3,12,21)
3628 AND EXISTS (SELECT 'EXISTS'
3629 FROM cst_cost_group_assignments ccga
3630 WHERE ccga.cost_group_id = i_cost_group_id
3631 AND (ccga.organization_id = mmt.organization_id OR
3632 ccga.organization_id = mmt.transfer_organization_id))
3633 AND
3634 (
3635 (transaction_action_id IN (12,21)
3636 OR (transaction_action_id = 3 AND primary_quantity < 0))
3637 AND EXISTS (
3638 SELECT 'X'
3639 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
3640 WHERE c1.organization_id = mmt.organization_id
3641 AND c2.organization_id = mmt.transfer_organization_id
3642 AND c1.cost_group_id = c2.cost_group_id))
3643 AND NOT EXISTS (
3644 SELECT 'X'
3645 FROM cst_pac_low_level_codes cpllc
3646 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
3647 AND cpllc.pac_period_id = i_pac_period_id
3648 AND cpllc.cost_group_id = i_cost_group_id)
3649 union all
3650 SELECT /*+ LEADING (mmt) */
3651 mmt.transaction_id,
3652 mmt.transaction_action_id,
3653 mmt.transaction_source_type_id,
3654 mmt.inventory_item_id,
3655 mmt.primary_quantity,
3656 mmt.organization_id,
3657 nvl(mmt.transfer_organization_id,-1),
3658 mmt.subinventory_code
3659 FROM
3660 mtl_material_transactions mmt,
3661 cst_cost_group_assignments ccga
3662 WHERE
3663 transaction_date between l_start_date AND l_end_date
3664 AND transaction_action_id in (4,8,28,33,34,1,2,5,27) /* Added VMI Planning Transfer */
3665 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3666 AND nvl(mmt.owning_tp_type,2) = 2
3667 AND ccga.cost_group_id = i_cost_group_id
3668 AND ccga.organization_id = mmt.organization_id
3669 AND nvl(mmt.logical_transactions_created, 1) <> 2
3670 AND nvl(mmt.logical_transaction, 3) <> 1
3671 AND (transaction_action_id IN (4,8,33,34)
3672 OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
3673 OR ( transaction_action_id = 28 AND
3674 ((transaction_source_type_id = 8 AND primary_quantity < 0)
3675 OR
3676 transaction_source_type_id <> 8))
3677 OR (transaction_action_id in (1, 27)
3678 AND transaction_source_type_id IN (3,6,13)
3679 AND transaction_cost IS NULL)
3680 OR (transaction_action_id in (1,27)
3681 AND transaction_source_type_id NOT IN (1,3,6,13)) )
3682 AND NOT EXISTS (
3683 SELECT 'X'
3684 FROM cst_pac_low_level_codes cpllc
3685 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
3686 AND cpllc.pac_period_id = i_pac_period_id
3687 AND cpllc.cost_group_id = i_cost_group_id)
3688 ORDER BY 4; /* order by inventory item id */
3689
3690 BEGIN
3691 -- initialize local variables
3692 l_error_num := 0;
3693 l_error_code := '';
3694 l_error_msg := '';
3695 l_start_date:=to_date(i_start_date, 'YYYY/MM/DD HH24:MI:SS') ;
3696 l_end_date :=to_date(i_end_date, 'YYYY/MM/DD HH24:MI:SS') + 0.99999;
3697
3698 l_count :=0;
3699
3700 fnd_file.put_line(fnd_file.LOG, ' Start Processing group 2 - interorg txns within same cost group and non-interorg txns ... '
3701 || TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
3702
3703 l_stmt_num := 10;
3704
3705 OPEN group2_trx;
3706
3707 LOOP
3708
3709 FETCH group2_trx BULK COLLECT
3710 INTO
3711 txn_id_tab,
3712 txn_act_id_tab,
3713 txn_src_type_tab,
3714 item_id_tab,
3715 primary_qty_tab,
3716 org_id_tab,
3717 trf_org_id_tab,
3718 sub_inv_code_tab
3719 LIMIT g_bulk_limit;
3720
3721
3722 l_rec_count := item_id_tab.COUNT;
3723
3724 IF l_rec_count > 0 then
3725 FOR i in 1.. l_rec_count LOOP
3726 i_txn_id := txn_id_tab(i);
3727 i_txn_action_id := txn_act_id_tab(i);
3728 i_txn_src_type_id := txn_src_type_tab(i);
3729 i_item_id := item_id_tab(i);
3730 i_txn_qty := primary_qty_tab(i);
3731 i_txn_org_id := org_id_tab(i);
3732 i_txfr_org_id := trf_org_id_tab(i);
3733 i_subinventory_code := sub_inv_code_tab(i);
3734
3735 fnd_file.put_line(fnd_file.log,'txn_id:'||i_txn_id);
3736 fnd_file.put_line(fnd_file.log,'txn_action_id:'||i_txn_action_id);
3737 fnd_file.put_line(fnd_file.log,'txn_source_type_id:'||i_txn_src_type_id);
3738 fnd_file.put_line(fnd_file.log,'item_id:'||i_item_id);
3739
3740 l_stmt_num := 20;
3741 get_exp_flag(i_item_id, i_txn_org_id, i_subinventory_code, l_exp_item,
3742 l_exp_flag, l_error_num, l_error_code, l_error_msg);
3743
3744 /*BUG 7415281*/
3745
3746 l_error_num := NVL(l_error_num, 0);
3747 l_error_code := NVL(l_error_code, 'No Error');
3748 l_error_msg := NVL(l_error_msg, 'No Error');
3749
3750 IF (l_error_num <> 0) THEN
3751 raise EXP_FLAG_ERROR;
3752 END IF;
3753
3754 /* Bug 1855971: Exclude the txn_action_id of 2 */
3755 IF ((i_txn_src_type_id = 5) AND (i_txn_action_id <> 2)) THEN
3756
3757 l_stmt_num := 30;
3758
3759 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000 AND i_cost_method <> 4) THEN
3760 l_stmt_num := 40;
3761 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
3762 i_cost_group_id => i_cost_group_id,
3763 i_txn_category => 9, /* Cost Derived Transactions */
3764 i_user_id => i_user_id,
3765 i_login_id => i_login_id,
3766 i_request_id => i_request_id,
3767 i_prog_id => i_prog_id,
3768 i_prog_appl_id => i_prog_appl_id,
3769 o_err_num => l_error_num,
3770 o_err_code => l_error_code,
3771 o_err_msg => l_error_msg);
3772
3773 l_error_num := NVL(l_error_num, 0);
3774 l_error_code := NVL(l_error_code, 'No Error');
3775 l_error_msg := NVL(l_error_msg, 'No Error');
3776
3777 IF (l_error_num <> 0) THEN
3778 raise CPPB_ERROR;
3779 END IF;
3780
3781 END IF;
3782
3783 IF l_error_num = 0 THEN
3784 l_stmt_num := 50;
3785 CSTPPWMT.charge_wip_material( p_pac_period_id => i_pac_period_id,
3786 p_cost_group_id => i_cost_group_id,
3787 p_txn_id => i_txn_id,
3788 p_exp_item => l_exp_item,
3789 p_exp_flag => l_exp_flag,
3790 p_legal_entity => i_legal_entity,
3791 p_cost_type_id => i_cost_type_id,
3792 p_cost_method => i_cost_method,
3793 p_pac_rates_id => i_pac_rates_id,
3794 p_master_org_id => i_master_org_id,
3795 p_material_relief_algorithm => i_mat_relief_algo,
3796 p_uom_control => i_uom_control,
3797 p_user_id => i_user_id,
3798 p_login_id => i_login_id,
3799 p_request_id => i_request_id,
3800 p_prog_id => i_prog_id,
3801 p_prog_app_id => i_prog_appl_id,
3802 p_txn_category => 9, /* Cost Derived Transactions */
3803 x_cost_method_hook => l_hook_used,
3804 x_err_num => l_error_num,
3805 x_err_code => l_error_code,
3806 x_err_msg => l_error_msg );
3807
3808 l_error_num := nvl(l_error_num, 0);
3809 l_error_msg := nvl(l_error_msg, 'No Error');
3810 l_error_code := nvl(l_error_code, 'No Error');
3811 l_hook_used := nvl(l_hook_used, -1);
3812
3813 IF l_error_num <> 0 THEN
3814 raise CHARGE_WIP_ERROR;
3815 END IF;
3816 END IF;
3817
3818
3819 /* Custom hook used */
3820 IF l_hook_used <> -1 THEN
3821 fnd_file.put_line(fnd_file.log,'Hook is used');
3822 END IF;
3823
3824 ELSE
3825 /* other than wip source */
3826 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000 AND i_cost_method <> 4) THEN
3827 l_stmt_num := 60;
3828 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
3829 i_cost_group_id => i_cost_group_id,
3830 i_txn_category => 9, /* Cost Derived Transactions */
3831 i_user_id => i_user_id,
3832 i_login_id => i_login_id,
3833 i_request_id => i_request_id,
3834 i_prog_id => i_prog_id,
3835 i_prog_appl_id => i_prog_appl_id,
3836 o_err_num => l_error_num,
3837 o_err_code => l_error_code,
3838 o_err_msg => l_error_msg);
3839
3840 l_error_num := NVL(l_error_num, 0);
3841 l_error_code := NVL(l_error_code, 'No Error');
3842 l_error_msg := NVL(l_error_msg, 'No Error');
3843
3844 IF (l_error_num <> 0) THEN
3845 raise CPPB_ERROR;
3846 END IF;
3847
3848 END IF;
3849
3850 IF l_error_num = 0 THEN
3851 l_stmt_num := 60;
3852 CSTPPINV.cost_inv_txn(i_pac_period_id => i_pac_period_id,
3853 i_legal_entity => i_legal_entity,
3854 i_cost_type_id => i_cost_type_id,
3855 i_cost_group_id => i_cost_group_id,
3856 i_cost_method => i_cost_method,
3857 i_txn_id => i_txn_id,
3858 i_txn_action_id => i_txn_action_id,
3859 i_txn_src_type_id => i_txn_src_type_id,
3860 i_item_id => i_item_id,
3861 i_txn_qty => i_txn_qty,
3862 i_txn_org_id => i_txn_org_id,
3863 i_txfr_org_id => i_txfr_org_id,
3864 i_subinventory_code => i_subinventory_code,
3865 i_exp_flag => l_exp_flag,
3866 i_exp_item => l_exp_item,
3867 i_pac_rates_id => i_pac_rates_id,
3868 i_process_group => i_process_group,
3869 i_master_org_id => i_master_org_id,
3870 i_uom_control => i_uom_control,
3871 i_user_id => i_user_id,
3872 i_login_id => i_login_id,
3873 i_request_id => i_request_id,
3874 i_prog_id => i_prog_id,
3875 i_prog_appl_id => i_prog_appl_id,
3876 i_txn_category => 9, /* Cost Derived Transactions */
3877 i_transfer_price_pd => 0,
3878 o_err_num => l_error_num,
3879 o_err_code => l_error_code,
3880 o_err_msg => l_error_msg);
3881
3882 l_error_num := NVL(l_error_num, 0);
3883 l_error_code := NVL(l_error_code, 'No Error');
3884 l_error_msg := NVL(l_error_msg, 'No Error');
3885
3886 IF (l_error_num <> 0) THEN
3887 raise PROCESS_ERROR;
3888 END IF;
3889 END IF;
3890
3891 END IF; -- txn src type check
3892
3893 END LOOP;
3894
3895 l_count := l_count + l_rec_count;
3896
3897 END IF;
3898 EXIT WHEN group2_trx%NOTFOUND;
3899 END LOOP;
3900 CLOSE group2_trx;
3901
3902 fnd_file.put_line(fnd_file.LOG, 'COMPLETE Processing group 2 - interorg txns within same cost group and non-interorg txns:'
3903 ||l_count||'txns...'|| TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
3904
3905
3906 l_error_num := 0;
3907
3908 IF (CSTPPINV.l_item_id_tbl.COUNT > 0 AND i_cost_method <> 4) THEN
3909 /* more records left out in the PL/SQL tables, Move them to permanent tables
3910 and clear PL/SQL tables */
3911 l_stmt_num := 70;
3912 CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
3913 ,i_cost_group_id => i_cost_group_id
3914 ,i_txn_category => 9 /* cost derived transactions */
3915 ,i_user_id => i_user_id
3916 ,i_login_id => i_login_id
3917 ,i_request_id => i_request_id
3918 ,i_prog_id => i_prog_id
3919 ,i_prog_appl_id => i_prog_appl_id
3920 ,o_err_num => l_error_num
3921 ,o_err_code => l_error_code
3922 ,o_err_msg => l_error_msg
3923 );
3924
3925 l_error_num := NVL(l_error_num, 0);
3926 l_error_code := NVL(l_error_code, 'No Error');
3927 l_error_msg := NVL(l_error_msg, 'No Error');
3928
3929 IF (l_error_num <> 0) THEN
3930 raise CPPB_ERROR;
3931 END IF;
3932
3933 END IF;
3934
3935 IF (l_error_num = 0 AND i_cost_method <> 4 and l_count > 0) THEN
3936 /* l_count > 0 implies that there has been atleast one cost derived
3937 transaction processed upto this point in group2_trx cursor */
3938 l_stmt_num := 80;
3939 CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
3940 i_cost_group_id => i_cost_group_id,
3941 i_txn_category => 9, /* Cost Derived Transactions */
3942 i_low_level_code => -1, /* No completions */
3943 i_user_id => i_user_id,
3944 i_login_id => i_login_id,
3945 i_request_id => i_request_id,
3946 i_prog_id => i_prog_id,
3947 i_prog_appl_id => i_prog_appl_id,
3948 o_err_num => l_error_num,
3949 o_err_code => l_error_code,
3950 o_err_msg => l_error_msg);
3951
3952 l_error_num := nvl(l_error_num, 0);
3953 l_error_msg := nvl(l_error_msg, 'No Error');
3954 l_error_code := nvl(l_error_code, 'No Error');
3955
3956 IF l_error_num <> 0 THEN
3957 raise CPPB_ERROR;
3958 END IF;
3959
3960 END IF;
3961
3962
3963 EXCEPTION
3964 WHEN PROCESS_ERROR THEN
3965 o_err_num := l_error_num;
3966 o_err_code := l_error_code;
3967 o_err_msg := 'CSTPPINV.COST_INV_TXN:' || l_error_msg;
3968 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV group2_trx');
3969 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
3970
3971 WHEN CPPB_ERROR THEN
3972 o_err_num := l_error_num;
3973 o_err_code := l_error_code;
3974 o_err_msg := 'CSTPPINV:' || l_error_msg;
3975 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV group2_trx while insert or update CPPB');
3976
3977 WHEN EXP_FLAG_ERROR THEN
3978 o_err_num := l_error_num;
3979 o_err_code := l_error_code;
3980 o_err_msg := 'CSTPPINV.get_exp_flag:' || l_error_msg;
3981 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.get_exp_flag');
3982 fnd_file.put_line(fnd_file.log,'item id:'||i_item_id || ' txn action id:' || i_txn_action_id
3983 || ' organization id:' || i_txn_org_id || ' transfer org id:' || i_txfr_org_id);
3984
3985 WHEN CHARGE_WIP_ERROR THEN
3986 o_err_num := l_error_num;
3987 o_err_code := l_error_code;
3988 o_err_msg := 'CSTPPINV.charge_wip_material:' || l_error_msg;
3989 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.charge_wip_material');
3990 fnd_file.put_line(fnd_file.log,'item id:'||i_item_id || ' txn action id:' || i_txn_action_id
3991 || ' organization id:' || i_txn_org_id || ' Material Relief Algorithm:' || i_mat_relief_algo);
3992 fnd_file.put_line(fnd_file.log,' Expense Item:' || l_exp_item || ' Expense Flag:' || l_exp_flag);
3993
3994 WHEN OTHERS THEN
3995 o_err_num := SQLCODE;
3996 o_err_msg := 'CSTPPINV (' || to_char(l_stmt_num) || '): '
3997 || substr(SQLERRM,1,150);
3998 fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV.cost_txn_grp2');
3999 fnd_file.put_line(fnd_file.log,'txn_id'||i_txn_id);
4000
4001 END cost_txn_grp2;
4002
4003
4004 end CSTPPINV;