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