[Home] [Help]
PACKAGE BODY: APPS.CSTPLCIN
Source
1 package body cstplcin as
2 /* $Header: CSTLCINB.pls 120.8.12000000.2 2007/09/26 07:27:36 akhadika ship $ */
3
4 procedure cost_inv_txn (
5 i_txn_id in number,
6 i_org_id in number,
7 i_cost_group_id in number,
8 i_txfr_cost_group_id in number,
9 i_cost_type_id in number,
10 i_cost_method in number,
11 i_rates_ct_id in number,
12 i_user_id in number,
13 i_login_id in number,
14 i_request_id in number,
15 i_prog_id in number,
16 i_prog_appl_id in number,
17 i_item_id in number,
18 i_txn_qty in number,
19 i_txn_action_id in number,
20 i_txn_src_type_id in number,
21 i_txn_org_id in number,
22 i_txfr_org_id in number,
23 i_fob_point in number,
24 i_exp_flag in number,
25 i_exp_item in number,
26 i_citw_flag in number,
27 i_flow_schedule in number,
28 i_tprice_option in number,
29 i_txf_price in number,
30 o_err_num out NOCOPY number,
31 o_err_code out NOCOPY varchar2,
32 o_err_msg out NOCOPY varchar2
33 )
34 is
35 l_layer_cost_org number;
36 l_err_num number;
37 l_err_code varchar2(240);
38 l_err_msg varchar2(240);
39
40 -- Bug 3062249
41 l_txn_source_id number;
42 l_entity_type number;
43 l_return_status varchar2(1);
44 l_msg_count number := 0;
45 l_msg_data varchar2(8000);
46 l_msg_return_status varchar2(1);
47 -- Bug 3062249
48
49
50 l_layer_id number;
51 l_txfr_layer_id number;
52 l_stmt_num number;
53 cost_inv_txn_error EXCEPTION;
54
55 l_pd_txfr_ind NUMBER; --OPM INVCONV sschinch
56 /* SLA Uptake */
57 l_trx_info CST_XLA_PVT.t_xla_inv_trx_info;
58 l_txn_source_code MTL_MATERIAL_TRANSACTIONS.SOURCE_CODE%TYPE;
59 l_std_txfr_flag VARCHAR2(1);
60 l_enc_reversal_flag NUMBER;
61 l_encumbrance_amount NUMBER;
62 l_bc_status VARCHAR2(2000);
63 l_packet_id NUMBER;
64
65
66 begin
67
68 /*
69 ** initialize local variables
70 */
71 l_err_num := 0;
72 l_layer_id := 0;
73 l_txfr_layer_id := 0;
74
75 /*------------------------------------------------------------+
76 | Begin OPM INVCONV sschinch/umoogala Process/discrete Xfer changes.
77 | Following query will return:
78 | 1 for process/discrete xfer
79 | 0 for discrete/discrete xfer
80 +------------------------------------------------------------*/
81 l_stmt_num := 5;
82 SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
83 INTO l_pd_txfr_ind
84 FROM MTL_PARAMETERS MP
85 WHERE MP.ORGANIZATION_ID = i_txn_org_id
86 OR MP.ORGANIZATION_ID = i_txfr_org_id;
87 /*End OPM INVCONV sschinch process discrete changes */
88
89 l_stmt_num := 10;
90 SELECT
91 transaction_date,
92 transaction_source_id,
93 source_code,
94 encumbrance_amount
95 INTO
96 l_trx_info.TRANSACTION_DATE,
97 l_txn_source_id,
98 l_txn_source_code,
99 l_encumbrance_amount
100 FROM MTL_MATERIAL_TRANSACTIONS
101 WHERE TRANSACTION_ID = i_txn_id;
102
103 /*------------------------------------------------------------+
104 | Check cost group layer for following three transaction cases.
105 | 1) Direct InterOrg Transfer
106 | 2) FOB shipment Shipment
107 | 3) FOB receipt Receipt
108 | 4) Logical Intransit Receipt for process discrete transactions. (OPM INVCONV)
109 | 5) Logical Intransit Shipment for process discrete transactions. (OPM INVCONV)
110 | Bug 5324241: Without this condition, control is going to
111 | elsif block and layer is being created with discrete org and
112 | opm orgs cost group id.
113 +------------------------------------------------------------*/
114 if ((i_txn_action_id = 3) or
115 (i_txn_action_id = 12 and i_fob_point = 2) or
116 (i_txn_action_id = 21 and i_fob_point = 1) or
117 (i_txn_action_id = 15 ) or/* Logical Intransit Receipt OPM INVCONV sschinch*/
118 (i_txn_action_id = 22 ) ) then
119
120 l_stmt_num := 10;
121 SELECT count(*)
122 INTO l_layer_cost_org
123 FROM MTL_PARAMETERS
124 WHERE ORGANIZATION_ID = i_txn_org_id
125 AND PRIMARY_COST_METHOD in (5,6);
126
127 if (l_layer_cost_org > 0) then
128 -- check the existence of layer
129 l_layer_id := CSTPACLM.layer_id(i_txn_org_id, i_item_id,
130 i_cost_group_id, l_err_num,
131 l_err_code, l_err_msg);
132 -- check error
133 if (l_err_num <> 0) then
134 raise cost_inv_txn_error;
135 end if;
136
137 -- create a layer
138 if (l_layer_id = 0) then
139 l_layer_id := CSTPACLM.create_layer(i_txn_org_id, i_item_id,
140 i_cost_group_id, i_user_id,
141 i_request_id, i_prog_id,
142 i_prog_appl_id, i_txn_id, l_err_num,
143 l_err_code, l_err_msg);
144 -- check error
145 if (l_layer_id = 0) then
146 raise cost_inv_txn_error;
147 end if;
148 end if;
149 end if;
150
151 /*------------------------------------------------------------+
152 | OPM INVCONV sschinch/umoogala
153 | Assumption here is that we are calling
154 | this routine for discrete organization as receiving org.
155 | It is important that we do not create cost layers for shipping
156 | organization if it is a process org.
157 | So before we create lets check if this transfer is between
158 | a process and discretre organization
159 +------------------------------------------------------------*/
160
161 IF (l_pd_txfr_ind = 0)
162 THEN
163
164 SELECT count(*)
165 INTO l_layer_cost_org
166 FROM MTL_PARAMETERS
167 WHERE ORGANIZATION_ID = i_txfr_org_id
168 AND PRIMARY_COST_METHOD in (5,6);
169
170
171 if (l_layer_cost_org > 0) then
172 -- check the existence of layer
173 l_txfr_layer_id := CSTPACLM.layer_id(i_txfr_org_id, i_item_id,
174 i_txfr_cost_group_id, l_err_num,
175 l_err_code, l_err_msg);
176 -- check error
177 if (l_err_num <> 0) then
178 raise cost_inv_txn_error;
179 end if;
180
181 -- create a layer
182 if (l_txfr_layer_id = 0) then
183 l_txfr_layer_id := CSTPACLM.create_layer(i_txfr_org_id, i_item_id,
184 i_txfr_cost_group_id, i_user_id,
185 i_request_id, i_prog_id,
186 i_prog_appl_id, i_txn_id, l_err_num,
187 l_err_code, l_err_msg);
188 -- check error
189 if (l_txfr_layer_id = 0) then
190 raise cost_inv_txn_error;
191 end if;
192 end if;
193 end if;
194 END IF;
195 /* End OPM INVCONV sschinch */
196
197 /*------------------------------------------------------------+
198 | Check layer for following three transaction cases.
199 | 1) Subinventory Transfer
200 | 2) FOB shipment Receipt
201 | 3) FOB receipt Shipment
202 +------------------------------------------------------------*/
203 /* Bug #2002105. Txf_cost_group_id defaults to -1 if value in database is NULL.*/
204 elsif (i_txfr_cost_group_id <> -1 ) then
205
206 SELECT count(*)
207 INTO l_layer_cost_org
208 FROM MTL_PARAMETERS
209 WHERE ORGANIZATION_ID = i_txn_org_id
210 AND PRIMARY_COST_METHOD in (5,6);
211
212 if (l_layer_cost_org > 0) then
213 -- check the existence of layer
214 l_layer_id := CSTPACLM.layer_id(i_txn_org_id, i_item_id,
215 i_cost_group_id, l_err_num,
216 l_err_code, l_err_msg);
217 -- check error
218 if (l_err_num <> 0) then
219 raise cost_inv_txn_error;
220 end if;
221
222 -- create a layer
223 if (l_layer_id = 0) then
224 l_layer_id := CSTPACLM.create_layer(i_txn_org_id, i_item_id,
225 i_cost_group_id, i_user_id,
226 i_request_id, i_prog_id,
227 i_prog_appl_id, i_txn_id, l_err_num,
228 l_err_code, l_err_msg);
229 -- check error
230 if (l_layer_id = 0) then
231 raise cost_inv_txn_error;
232 end if;
233 end if;
234 -- check the existence of layer
235 l_txfr_layer_id := CSTPACLM.layer_id(i_txn_org_id, i_item_id,
236 i_txfr_cost_group_id, l_err_num,
237 l_err_code, l_err_msg);
238 -- check error
239 if (l_err_num <> 0) then
240 raise cost_inv_txn_error;
241 end if;
242
243 -- create a layer
244 if (l_txfr_layer_id = 0) then
245 l_txfr_layer_id := CSTPACLM.create_layer(i_txn_org_id, i_item_id,
246 i_txfr_cost_group_id, i_user_id,
247 i_request_id, i_prog_id,
248 i_prog_appl_id, i_txn_id, l_err_num,
249 l_err_code, l_err_msg);
250 -- check error
251 if (l_txfr_layer_id = 0) then
252 raise cost_inv_txn_error;
253 end if;
254 end if;
255 end if; /*Checking for FIFO,LIFO org*/
256
257 /*------------------------------------------------------------+
258 | Rest of cases other than subinv txfr, inter org txfr,
259 | fob shipment, or fob receipt transactions
260 +------------------------------------------------------------*/
261 else
262 SELECT count(*)
263 INTO l_layer_cost_org
264 FROM MTL_PARAMETERS
265 WHERE ORGANIZATION_ID = i_txn_org_id
266 AND PRIMARY_COST_METHOD in (5,6);
267
268 if (l_layer_cost_org > 0) then
269
270 -- check the existence of layer
271 l_layer_id := CSTPACLM.layer_id(i_txn_org_id, i_item_id,
272 i_cost_group_id, l_err_num,
273 l_err_code, l_err_msg);
274 -- check error
275 if (l_err_num <> 0) then
276 raise cost_inv_txn_error;
277 end if;
278
279 -- create a layer
280 if (l_layer_id = 0) then
281 l_layer_id := CSTPACLM.create_layer(i_txn_org_id, i_item_id,
282 i_cost_group_id, i_user_id,
283 i_request_id, i_prog_id,
284 i_prog_appl_id, i_txn_id, l_err_num,
285 l_err_code, l_err_msg);
286 -- check error
287 if (l_layer_id = 0) then
288 raise cost_inv_txn_error;
289 end if;
290 end if;
291 end if;
292
293 end if;
294
295 l_stmt_num := 60;
296
297 /*
298 ** call the layer cost processor to cost transactions
299 */
300 CSTPLVCP.cost_processor(i_org_id,
301 i_txn_id,
302 l_layer_id,
303 i_cost_type_id,
304 i_cost_method,
305 i_rates_ct_id,
306 i_rates_ct_id,
307 i_item_id,
308 i_txn_qty,
309 i_txn_action_id,
310 i_txn_src_type_id,
311 i_txn_org_id,
312 i_txfr_org_id,
313 i_cost_group_id,
314 i_txfr_cost_group_id,
315 l_txfr_layer_id,
316 i_fob_point,
317 i_exp_item,
318 i_exp_flag,
319 i_citw_flag,
320 i_flow_schedule,
321 i_user_id,
322 i_login_id,
323 i_request_id,
324 i_prog_appl_id,
325 i_prog_id,
326 i_tprice_option,
327 i_txf_price,
328 l_err_num,
329 l_err_code,
330 l_err_msg);
331
332 /*
333 ** check the return value from the average cost processor
334 */
335 if (l_err_num <> 0) then
336 -- Error occurred
337 raise cost_inv_txn_error;
338 end if;
339
340 l_stmt_num := 70;
341
342 /*
343 ** call the material distribution processor if it's not
344 a layer cost update transaction. This type of transaction
345 has call its own distribution procedure.
346 */
347
348 if (i_txn_action_id = 24 ) then /*Removed i_txn_src_type_id = 15 for bug 6030287*/
349 CSTPLENG.layer_cost_update_dist(i_org_id,
350 i_txn_id,
351 l_layer_id,
352 i_exp_item,
353 i_user_id,
354 i_login_id,
355 i_request_id,
356 i_prog_appl_id,
357 i_prog_id,
358 l_err_num,
359 l_err_code,
360 l_err_msg);
361
362 if (l_err_num <> 0) then
363 -- Error occurred
364 raise cost_inv_txn_error;
365 end if;
366
367 else
368 CSTPACDP.cost_txn(i_org_id,
369 i_txn_id,
370 l_layer_id,
371 i_fob_point,
372 i_exp_item,
373 i_citw_flag,
374 i_flow_schedule,
375 i_user_id,
376 i_login_id,
377 i_request_id,
378 i_prog_appl_id,
379 i_prog_id,
380 i_tprice_option,
381 i_txf_price,
382 l_err_num,
383 l_err_code,
384 l_err_msg);
385
386 -- check the return value from the material distribution
387 -- processor
388 if (l_err_num <> 0) then
389 raise cost_inv_txn_error;
390 end if;
391
392 /* Bug 3062249 */
393 /* If a CITW transaction and an EAM job, then call EAM Cost API to
394 update EAM Elemental costs */
395
396 if (i_citw_flag = 1) then
397 select entity_type
398 into l_entity_type
399 from wip_entities
400 where wip_entity_id = l_txn_source_id;
401
402 if (l_entity_type in (6,7)) then
403 CST_eamCost_PUB.process_matCost (
404 p_api_version => 1.0,
405 x_return_status => l_return_status,
406 x_msg_count => l_msg_count,
407 x_msg_data => l_msg_data,
408 p_txn_id => i_txn_id,
409 p_user_id => i_user_id,
410 p_request_id => i_request_id,
411 p_prog_id => i_prog_id,
412 p_prog_app_id => i_prog_appl_id,
413 p_login_id => i_login_id
414 );
415
416 if (l_return_status <> fnd_api.g_ret_sts_success) then
417 CST_UTILITY_PUB.writelogmessages
418 ( p_api_version => 1.0,
419 p_msg_count => l_msg_count,
420 p_msg_data => l_msg_data,
421 x_return_status => l_msg_return_status );
422 l_err_num := l_msg_count;
423 else
424 l_err_num := 0;
425 end if;
426 end if;
427 end if;
428 end if;
429
430 /* SLA Event Seeding */
431 /* Structure used so that API signature is not disrupted
432 when additional transactional parameters are required
433 (without making redundant calls to TXN tables) */
434
435 /* For intransit interorg transactions that are picked by
436 the cost worker of the non-transaction organization, do not
437 create the events since they have been created by the cost
438 worker of the transaction organization */
439 /* For Std-Ave or Ave-Std transfers, the average/layer cost worker
440 * processes both the sending and receiving transactions. This case
441 * should be excluded from the scenario described above */
442
443 l_stmt_num := 171;
444
445 l_std_txfr_flag := 'N';
446
447 IF ( I_TXN_ACTION_ID in (12, 21) ) THEN
448 BEGIN
449 SELECT 'Y'
450 INTO l_std_txfr_flag
451 FROM MTL_PARAMETERS
452 WHERE ORGANIZATION_ID IN ( i_txn_org_id, i_txfr_org_id )
453 AND PRIMARY_COST_METHOD = 1;
454 EXCEPTION
455 WHEN OTHERS THEN
456 l_std_txfr_flag := 'N';
457 END;
458 END IF;
459
460 IF NOT ( ( ( I_TXN_ACTION_ID = 21 AND I_FOB_POINT = 1 ) OR
461 ( I_TXN_ACTION_ID = 12 AND I_FOB_POINT = 2 ) ) AND
462 I_TXN_ORG_ID <> I_ORG_ID AND l_std_txfr_flag = 'N' ) THEN
463 l_stmt_num := 205;
464
465 SELECT
466 encumbrance_reversal_flag
467 INTO
468 l_enc_reversal_flag
469 FROM
470 MTL_PARAMETERS
471 WHERE
472 organization_id = i_txn_org_id;
473
474 l_trx_info.TRANSACTION_ID := i_txn_id;
475 l_trx_info.TXN_ACTION_ID := i_txn_action_id;
476 l_trx_info.TXN_ORGANIZATION_ID := i_txn_org_id;
477 l_trx_info.TXN_SRC_TYPE_ID := i_txn_src_type_id;
478 l_trx_info.TXFR_ORGANIZATION_ID := i_txfr_org_id;
479 l_trx_info.FOB_POINT := i_fob_point;
480 l_trx_info.PRIMARY_QUANTITY := i_txn_qty;
481
482 IF I_TPRICE_OPTION <> 0 THEN
483 l_trx_info.TP := 'Y';
484 ELSE
485 l_trx_info.TP := 'N';
486 END IF;
487
488 IF i_txn_action_id = 24 and l_txn_source_code is not null THEN
489 l_trx_info.attribute := l_txn_source_code;
490 ELSIF i_citw_flag = 1 THEN
491 l_trx_info.attribute := 'CITW';
492 ELSIF i_txn_action_id = 3 THEN
493 IF i_txn_qty < 0 THEN
494 l_trx_info.attribute := 'SAME';
495 ELSE
496 l_trx_info.attribute := 'TRANSFER';
497 END IF;
498 END IF;
499
500 l_trx_info.ENCUMBRANCE_FLAG := 'N';
501 IF ( i_txn_src_type_id in (1, 7, 8) ) THEN
502 IF (l_encumbrance_amount is NOT NULL AND l_enc_reversal_flag = 1 ) THEN
503 l_trx_info.ENCUMBRANCE_FLAG := 'Y';
504 END IF;
505 END IF;
506
507 l_stmt_num := 210;
508 CST_XLA_PVT.Create_INVXLAEvent (
509 p_api_version => 1.0,
510 p_init_msg_list => FND_API.G_FALSE,
511 p_commit => FND_API.G_FALSE,
512 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
513 x_return_status => l_return_status,
514 x_msg_count => l_msg_count,
515 x_msg_data => l_msg_data,
516 p_trx_info => l_trx_info
517 );
518 IF l_return_status <> 'S' THEN
519 l_err_num := -1;
520 l_err_code := 'Error raising SLA Event for transaction: '||to_char(i_txn_id); l_err_msg := 'CSTPLCIN.COST_INV_TXN:('||l_stmt_num||'): '||l_err_code;
521 RAISE FND_API.g_exc_unexpected_error;
522 END IF;
523
524 END IF; /* Main IF NOT ( ( ( I_TXN_ACTION_ID = 21 .. */
525
526 EXCEPTION
527 when cost_inv_txn_error then
528 o_err_num := l_err_num;
529 o_err_code := l_err_code;
530 o_err_msg := 'CSTPLCIN.COST_INV_TXN:' || l_err_msg;
531 when OTHERS then
532 o_err_num := SQLCODE;
533 o_err_msg := 'CSTPLCIN.COST_INV_TXN: (' || to_char(l_stmt_num) || '): '
534 || substr(SQLERRM,1,150);
535
536 end cost_inv_txn;
537
538 end cstplcin;