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