[Home] [Help]
PACKAGE BODY: APPS.CTO_TRANSFER_PRICE_PK
Source
1 package body CTO_TRANSFER_PRICE_PK as
2 /* $Header: CTOTPRCB.pls 120.2.12020000.2 2012/12/04 11:54:58 abhissri ship $*/
3
4 /*----------------------------------------------------------------------------+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
6 | All rights reserved.
7 | Oracle Manufacturing
8 |
9 |FILE NAME : CTOTPRCB.pls
10 |DESCRIPTION : Contains modules to :
11 | 1. Get the optional components of a configuration item from either the sales order or the BOM
12 | 2. Calculate Transfer Price for a configuration item
13 |
14 |HISTORY : Created on 29-AUG-2003 by Sajani Sheth
15 | Modifed on 28-JAN-2004 by Renga Kannan
16 | The bom qty was not cosidered during rollup ,
17 | Also UOM conversion was not considered. Fixed this issue
18 |
19 +-----------------------------------------------------------------------------*/
20
21 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_TRANSFER_PRICE_PK';
22 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
23 g_configs_only varchar2(1);
24 g_item_id number;
25
26 /***********************************************************************
27 This procedure returns the optional components of a configuration item
28 and its child configuration items, based on either a sales order or the
29 configuration BOM.
30 If the parameter p_configs_only is 'Y', only child configuration items
31 are returned.
32 Config BOMs created before Patchset-I do not have optional_on_model flag
33 populated. For these configs, all components will be treated as mandatory
34 and so, no components will be returned.
35 The optional components are populated in table bom_explosion_temp with
36 a unique group_id. The group_id is passed back to the calling application.
37 ***********************************************************************/
38 PROCEDURE get_config_details
39 (
40 p_item_id IN number,
41 p_org_id IN number default NULL,
42 p_mode_id IN number default 3,
43 p_configs_only IN varchar2 default 'N',
44 p_line_id IN Number default null,
45 x_group_id OUT NOCOPY number,
46 x_msg_count OUT NOCOPY number,
47 x_msg_data OUT NOCOPY varchar2,
48 x_return_status OUT NOCOPY varchar)
49
50 IS
51
52 x_grp_id number;
53 p_ato_line_id number;
54 p_organization_id number;
55 lStmtNumber number;
56 l_line_id number;
57
58 BEGIN
59
60 lStmtNumber := 10;
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 IF PG_DEBUG <> 0 THEN
64 oe_debug_pub.add('get_config_details:entering');
65 oe_debug_pub.add('get_config_details:ItemId::'||to_char(p_item_id));
66 oe_debug_pub.add('get_config_details:ModeId::'||to_char(p_mode_id));
67 oe_debug_pub.add('get_config_details:ConfigsOnly::'||p_configs_only);
68 END IF;
69
70 g_configs_only := p_configs_only;
71 g_item_id := p_item_id;
72
73 IF p_mode_id = 1 THEN
74 lStmtNumber := 20;
75 -- mode is OM
76 IF PG_DEBUG <> 0 THEN
77 oe_debug_pub.add('get_config_details:Mode is OM');
78 END IF;
79
80 BEGIN
81 /*select ato_line_id
82 into p_ato_line_id
83 from bom_cto_order_lines
84 where config_item_id = p_item_id
85 and rownum = 1;*/
86
87 If p_line_id is null then
88 select line_id
89 into l_line_id
90 from bom_cto_order_lines bcol
91 where config_item_id = p_item_id
92 and exists (select 'x'
93 from oe_order_lines_all oel
94 where oel.header_id = bcol.header_id
95 and oel.ato_line_id = bcol.ato_line_id
96 and oel.item_type_code = 'CONFIG')
97 and rownum = 1;
98 Else
99 l_line_id := p_line_id;
100 End if;
101
102 IF PG_DEBUG <> 0 THEN
103 oe_debug_pub.add('get_config_details:l_line_id::'||to_char(l_line_id));
104 END IF;
105
106 EXCEPTION
107 WHEN no_data_found THEN
108 IF PG_DEBUG <> 0 THEN
109 oe_debug_pub.add('get_config_details:NDF:Mode is OM and no order line for this item.', 1);
110 END IF;
111 raise FND_API.G_EXC_ERROR;
112 END;
113
114 lStmtNumber := 30;
115 select bom_explosion_temp_s.nextval
116 into x_grp_id
117 from dual;
118
119 x_group_id := x_grp_id;
120
121 IF PG_DEBUG <> 0 THEN
122 oe_debug_pub.add('get_config_details:Grp Id::'||to_char(x_group_id));
123 END IF;
124
125 lStmtNumber := 40;
126
127 get_config_details_bcol
128 (l_line_id,
129 x_grp_id,
130 x_msg_count,
131 x_msg_data,
132 x_return_status);
133
134 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
135 IF PG_DEBUG <> 0 THEN
136 oe_debug_pub.add('get_config_details:get_config_details_bcol returned with unexp error',1);
137 END IF;
138 raise FND_API.G_EXC_UNEXPECTED_ERROR;
139
140 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
141 IF PG_DEBUG <> 0 THEN
142 oe_debug_pub.add('get_config_details:get_config_details_bcol returned with exp error',1);
143 END IF;
144 raise FND_API.G_EXC_ERROR;
145 END IF;
146
147 ELSIF p_mode_id = 2 THEN
148
149 lStmtNumber := 50;
150 -- mode is BOM
151 IF PG_DEBUG <> 0 THEN
152 oe_debug_pub.add('get_config_details:Mode is BOM');
153 END IF;
154
155 BEGIN
156 select organization_id
157 into p_organization_id
158 from bom_bill_of_materials
159 where assembly_item_id = p_item_id
160 and rownum = 1;
161
162 IF PG_DEBUG <> 0 THEN
163 oe_debug_pub.add('get_config_details:p_organization_id::'||to_char(p_organization_id));
164 END IF;
165
166 EXCEPTION
167 WHEN no_data_found THEN
168 IF PG_DEBUG <> 0 THEN
169 oe_debug_pub.add('get_config_details:NDF:Mode is BOM and no BOM exists for this item.', 1);
170 END IF;
171 raise FND_API.G_EXC_ERROR;
172 END;
173
174 lStmtNumber := 60;
175 select bom_explosion_temp_s.nextval
176 into x_grp_id
177 from dual;
178
179 x_group_id := x_grp_id;
180
181 IF PG_DEBUG <> 0 THEN
182 oe_debug_pub.add('get_config_details:xsGrp Id::'||to_char(x_group_id));
183 END IF;
184
185 lStmtNumber := 70;
186 get_config_details_bom
187 (p_item_id,
188 p_organization_id,
189 x_grp_id,
190 x_msg_count,
191 x_msg_data,
192 x_return_status);
193
194 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
195 IF PG_DEBUG <> 0 THEN
196 oe_debug_pub.add('get_config_details:get_config_details_bom returned with unexp error',1);
197 END IF;
198 raise FND_API.G_EXC_UNEXPECTED_ERROR;
199
200 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
201 IF PG_DEBUG <> 0 THEN
202 oe_debug_pub.add('get_config_details:get_config_details_bom returned with exp error',1);
203 END IF;
204 raise FND_API.G_EXC_ERROR;
205 END IF;
206
207 ELSE
208
209 -- mode is BOTH, check in OM first and then BOM
210 lStmtNumber := 80;
211 IF PG_DEBUG <> 0 THEN
212 oe_debug_pub.add('get_config_details:Mode is BOTH');
213 oe_debug_pub.add(' P_line_id = '||to_char(p_line_id),1);
214 END IF;
215
216 BEGIN
217 If p_line_id is null Then
218 select line_id
219 into l_line_id
220 from bom_cto_order_lines bcol
221 where config_item_id = p_item_id
222 and exists (select 'x'
223 from oe_order_lines_all oel
224 where oel.header_id = bcol.header_id
225 and oel.ato_line_id = bcol.ato_line_id
226 and oel.item_type_code = 'CONFIG')
227 and rownum = 1;
228 Else
229 l_line_id := p_line_id;
230 End if;
231
232 IF PG_DEBUG <> 0 THEN
233 oe_debug_pub.add('get_config_details:Config found in bcol, getting details from bcol');
234 oe_debug_pub.add('get_config_details:l_line_id::'||to_char(l_line_id));
235 END IF;
236
237 lStmtNumber := 90;
238 select bom_explosion_temp_s.nextval
239 into x_grp_id
240 from dual;
241
242 x_group_id := x_grp_id;
243
244 IF PG_DEBUG <> 0 THEN
245 oe_debug_pub.add('get_config_details:Grp Id::'||to_char(x_group_id));
246 END IF;
247
248 lStmtNumber := 100;
249 get_config_details_bcol
250 (l_line_id,
251 x_grp_id,
252 x_msg_count,
253 x_msg_data,
254 x_return_status);
255
256 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
257 IF PG_DEBUG <> 0 THEN
258 oe_debug_pub.add('get_config_details:get_config_details_bcol returned with unexp error',1);
259 END IF;
260 raise FND_API.G_EXC_UNEXPECTED_ERROR;
261
262 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
263 IF PG_DEBUG <> 0 THEN
264 oe_debug_pub.add('get_config_details:get_config_details_bcol returned with exp error',1);
265 END IF;
266 raise FND_API.G_EXC_ERROR;
267 END IF;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 -- config does not exist in bcol, check in BOM
272
273 IF PG_DEBUG <> 0 THEN
274 oe_debug_pub.add('get_config_details:Mode is BOM');
275 END IF;
276
277 lStmtNumber := 110;
278 BEGIN
279 select organization_id
280 into p_organization_id
281 from bom_bill_of_materials
282 where assembly_item_id = p_item_id
283 and rownum = 1;
284
285 IF PG_DEBUG <> 0 THEN
286 oe_debug_pub.add('get_config_details:p_organization_id::'||to_char(p_organization_id));
287 END IF;
288
289 EXCEPTION
290 WHEN no_data_found THEN
291 IF PG_DEBUG <> 0 THEN
292 oe_debug_pub.add('get_config_details:NDF:Mode is BOTH, but no order line and no BOM exists for this item.', 1);
293 END IF;
294 raise FND_API.G_EXC_ERROR;
295 END;
296
297 lStmtNumber := 120;
298 select bom_explosion_temp_s.nextval
299 into x_grp_id
300 from dual;
301
302 x_group_id := x_grp_id;
303
304 IF PG_DEBUG <> 0 THEN
305 oe_debug_pub.add('get_config_details:Grp Id::'||to_char(x_group_id));
306 END IF;
307
308 lStmtNumber := 130;
309 get_config_details_bom
310 (p_item_id,
311 p_organization_id,
312 x_grp_id,
313 x_msg_count,
314 x_msg_data,
315 x_return_status);
316
317 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
318 IF PG_DEBUG <> 0 THEN
319 oe_debug_pub.add('get_config_details:get_config_details_bom returned with unexp error',1);
320 END IF;
321 raise FND_API.G_EXC_UNEXPECTED_ERROR;
322
323 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
324 IF PG_DEBUG <> 0 THEN
325 oe_debug_pub.add('get_config_details:get_config_details_bom returned with exp error',1);
326 END IF;
327 raise FND_API.G_EXC_ERROR;
328 END IF;
329
330 END; -- sub block
331
332 END IF; -- mode
333
334 IF PG_DEBUG <> 0 THEN
335 oe_debug_pub.add('get_config_details:returning with status',1);
336 END IF;
337
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 IF PG_DEBUG <> 0 THEN
341 oe_debug_pub.add('get_config_details:unexp error::'||lStmtNumber||sqlerrm,1);
342 END IF;
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 CTO_MSG_PUB.Count_And_Get
345 (p_msg_count => x_msg_count
346 ,p_msg_data => x_msg_data
347 );
348
349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 IF PG_DEBUG <> 0 THEN
351 oe_debug_pub.add('get_config_details:unexp error::'||lStmtNumber||sqlerrm,1);
352 END IF;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 CTO_MSG_PUB.Count_And_Get
355 (p_msg_count => x_msg_count
356 ,p_msg_data => x_msg_data
357 );
358
359 WHEN FND_API.G_EXC_ERROR THEN
360 IF PG_DEBUG <> 0 THEN
361 oe_debug_pub.add('get_config_details:exp error::'||lStmtNumber||sqlerrm,1);
362 END IF;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 CTO_MSG_PUB.Count_And_Get
365 (p_msg_count => x_msg_count
366 ,p_msg_data => x_msg_data);
367
368 WHEN OTHERS THEN
369 IF PG_DEBUG <> 0 THEN
370 oe_debug_pub.add('get_config_details:others::'||lStmtNumber||sqlerrm,1);
371 END IF;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
374 FND_MSG_PUB.Add_Exc_Msg
375 (G_PKG_NAME
376 , 'get_config_details'
377 );
378 END IF;
379 CTO_MSG_PUB.Count_And_Get
380 (p_msg_count => x_msg_count
381 ,p_msg_data => x_msg_data
382 );
383
384 END; /* get_config_details */
385
386
387 /***********************************************************************
388 This procedure returns the optional components of a configuration item
389 and its child configuration items, based on a sales order. The optional
390 components are populated in table bom_explosion_temp for input parameter
391 group_id. If p_configs_only = 'Y', only child configuration items are
392 returned.
393 ***********************************************************************/
394 PROCEDURE get_config_details_bcol
395 (p_line_id IN NUMBER,
396 p_grp_id IN NUMBER,
397 x_msg_count OUT NOCOPY NUMBER,
398 x_msg_data OUT NOCOPY VARCHAR2,
399 x_return_status OUT NOCOPY VARCHAR2)
400
401 IS
402
403 lStmtNumber number;
404 l_sort number := 0;
405 rowcount number;
406
407 -- rkaza. 04/28/2005.
408 -- In the case of populating bet from an order, we used to populate
409 -- organization_id as 1 for all the lines. Now we will be populating the oe
410 -- validation org id of the lines OU. Populating a valid organization_id here
411 -- will be later useful in improving performance when making any joins with
412 -- bet (especially join with msi in cost rollup to get cib attribute of the
413 -- config's model. In this case model definitely exists in the
414 -- oe_validation_org of the order lines OU)
415 -- Also, there might be some single org ct's who may not set oe validation org
416 -- for the OU. In which case, we will populate ship_from_org_id.
417
418 l_oeval_org_id number;
419 cursor get_oeval_org_id is
420 select to_number( nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , org_id) , ship_from_org_id))
421 from oe_order_lines_all
422 where line_id = p_line_id;
423
424 BEGIN
425
426 lStmtNumber := 10;
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 open get_oeval_org_id;
430 fetch get_oeval_org_id into l_oeval_org_id;
431 close get_oeval_org_id;
432
433 If PG_DEBUG <> 0 Then
434 cto_wip_workflow_api_pk.cto_debug('get_config_details_bcol:', 'entering');
435 cto_wip_workflow_api_pk.cto_debug('get_config_details_bcol:', 'p_grp_id::'||to_char(p_grp_id));
436 cto_wip_workflow_api_pk.cto_debug('get_config_details_bcol:', 'p_line_id::'||to_char(p_line_id));
437 End if;
438 IF PG_DEBUG <> 0 THEN
439 oe_debug_pub.add('get_config_details_bcol:entering');
440 oe_debug_pub.add('get_config_details_bcol:p_grp_id::'||to_char(p_grp_id));
441 oe_debug_pub.add('get_config_details_bcol:p_line_id::'||to_char(p_line_id));
442 oe_debug_pub.add('get_config_details_bcol:l_oeval_org_id::'||to_char(l_oeval_org_id));
443 END IF;
444
445 IF (g_configs_only = 'N') THEN
446 -- insert details from order lines
447 lStmtNumber := 20;
448 insert into bom_explosion_temp(
449 top_bill_sequence_id, -- not null
450 bill_sequence_id, -- not null
451 organization_id, -- not null
452 sort_order, -- not null
453 assembly_item_id,
454 component_item_id,
455 optional,
456 plan_level, -- not null
457 component_quantity,
458 configurator_flag,
459 line_id,
460 primary_uom_code,
461 group_id)
462 select
463 1, -- top_bill_sequence_id
464 1, -- bill_sequence_id
465 l_oeval_org_id, -- organization_id
466 l_sort, -- sort
467 bcol2.config_item_id, -- assembly_item_id
468 decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
469 1, -- optional
470 bcol1.plan_level - bcol2.plan_level, -- plan_level
471 bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
472 decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
473 bcol1.line_id, -- line_id
474 bcol1.order_quantity_uom, --primary_uom_code
475 p_grp_id
476 from
477 bom_cto_order_lines bcol1 -- component
478 ,bom_cto_order_lines bcol2 -- parent model
479 where bcol1.parent_ato_line_id = p_line_id
480 and bcol1.parent_ato_line_id <> bcol1.line_id
481 and bcol2.line_id = p_line_id
482 UNION
483 select
484 1, -- top_bill_sequence_id
485 1, -- bill_sequence_id
486 l_oeval_org_id, -- organization_id
487 l_sort, -- sort
488 bcol1.config_item_id, -- assembly_item_id
489 bcol1.inventory_item_id,-- component_item_id
490 1, -- optional
491 bcol1.plan_level - bcol1.plan_level,
492 bcol1.ordered_quantity/bcol1.ordered_quantity, -- comp qty
493 'N', -- config flag
494 bcol1.line_id, -- line_id
495 bcol1.order_quantity_uom, --primary_uom_code
496 p_grp_id
497 from
498 bom_cto_order_lines bcol1
499 where bcol1.line_id = p_line_id
500 ;
501
502 lStmtNumber := 30;
503 rowcount := 1 ;
504 WHILE rowcount > 0 LOOP
505
506 l_sort := l_sort + 1;
507
508 insert into bom_explosion_temp(
509 top_bill_sequence_id, -- not null
510 bill_sequence_id, -- not null
511 organization_id, -- not null
512 sort_order, -- not null
513 assembly_item_id,
514 component_item_id,
515 optional,
516 plan_level, -- not null
517 component_quantity,
518 configurator_flag,
519 line_id,
520 primary_uom_code,
521 group_id)
522 select
523 1, -- top_bill_sequence_id
524 1, -- bill_sequence_id
525 l_oeval_org_id, -- organization_id
526 l_sort, -- sort
527 bcol2.config_item_id, -- assembly_item_id
528 decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
529 1, -- optional
530 bcol1.plan_level - bcol2.plan_level + bet.plan_level, -- plan_level
531 bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
532 decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
533 bcol1.line_id, -- line_id
534 bcol1.order_quantity_uom, --primary_uom_code
535 p_grp_id
536 from
537 bom_cto_order_lines bcol1 -- component
538 ,bom_cto_order_lines bcol2 -- parent model
539 ,bom_explosion_temp bet
540 where bcol1.parent_ato_line_id = bet.line_id
541 and bcol2.line_id = bet.line_id
542 and bet.group_id = p_grp_id
543 and bet.sort_order = to_char(l_sort - 1)
544 and nvl(bet.configurator_flag, 'N') = 'Y'
545 UNION
546 select
547 1, -- top_bill_sequence_id
548 1, -- bill_sequence_id
549 l_oeval_org_id, -- organization_id
550 l_sort, -- sort
551 bcol1.config_item_id, -- assembly_item_id
552 bcol1.inventory_item_id,-- component_item_id
553 1, -- optional
554 bcol1.plan_level - bcol1.plan_level + bet.plan_level,
555 bcol1.ordered_quantity/bcol1.ordered_quantity, -- comp qty
556 'N', -- config flag
557 bcol1.line_id, -- line_id
558 bcol1.order_quantity_uom, --primary_uom_code
559 p_grp_id
560 from
561 bom_cto_order_lines bcol1
562 ,bom_explosion_temp bet
563 where bcol1.line_id = bet.line_id
564 and bet.group_id = p_grp_id
565 and bet.sort_order = to_char(l_sort - 1)
566 and nvl(bet.configurator_flag, 'N') = 'Y'
567 ;
568
569 rowcount := SQL%ROWCOUNT;
570
571 IF PG_DEBUG <> 0 THEN
572 oe_debug_pub.add ('get_config_details_bcol:Row Count : ' || rowcount, 2);
573 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'Row Count:'||rowcount);
574
575 END IF;
576
577 END LOOP;
578
579 ELSE /* configs_only = Y */
580 lStmtNumber := 40;
581 insert into bom_explosion_temp(
582 top_bill_sequence_id, -- not null
583 bill_sequence_id, -- not null
584 organization_id, -- not null
585 sort_order, -- not null
586 assembly_item_id,
587 component_item_id,
588 optional,
589 plan_level, -- not null
590 component_quantity,
591 configurator_flag,
592 line_id,
593 primary_uom_code,
594 group_id)
595 select
596 1, -- top_bill_sequence_id
597 1, -- bill_sequence_id
598 l_oeval_org_id, -- organization_id
599 l_sort, -- sort
600 bcol2.config_item_id, -- assembly_item_id
601 decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
602 1, -- optional
603 bcol1.plan_level - bcol2.plan_level, -- plan_level
604 bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
605 decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
606 bcol1.line_id, -- line_id
607 bcol1.order_quantity_uom, --primary_uom_code
608 p_grp_id
609 from
610 bom_cto_order_lines bcol1 -- component
611 ,bom_cto_order_lines bcol2 -- parent model
612 where bcol1.parent_ato_line_id = p_line_id
613 and bcol1.parent_ato_line_id <> bcol1.line_id
614 and bcol1.config_item_id is not null
615 and bcol2.line_id = p_line_id
616 ;
617
618 lStmtNumber := 30;
619 rowcount := 1 ;
620 WHILE rowcount > 0 LOOP
621
622 l_sort := l_sort + 1;
623
624 insert into bom_explosion_temp(
625 top_bill_sequence_id, -- not null
626 bill_sequence_id, -- not null
627 organization_id, -- not null
628 sort_order, -- not null
629 assembly_item_id,
630 component_item_id,
631 optional,
632 plan_level, -- not null
633 component_quantity,
634 configurator_flag,
635 line_id,
636 primary_uom_code,
637 group_id)
638 select
639 1, -- top_bill_sequence_id
640 1, -- bill_sequence_id
641 l_oeval_org_id, -- organization_id
642 l_sort, -- sort
643 bcol2.config_item_id, -- assembly_item_id
644 decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
645 1, -- optional
646 bcol1.plan_level - bcol2.plan_level + bet.plan_level, -- plan_level
647 bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
648 decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
649 bcol1.line_id, -- line_id
650 bcol1.order_quantity_uom, --primary_uom_code
651 p_grp_id
652 from
653 bom_cto_order_lines bcol1 -- component
654 ,bom_cto_order_lines bcol2 -- parent model
655 ,bom_explosion_temp bet
656 where bcol1.parent_ato_line_id = bet.line_id
657 and bcol1.config_item_id is not null
658 and bcol2.line_id = bet.line_id
659 and bet.group_id = p_grp_id
660 and bet.sort_order = to_char(l_sort - 1)
661 and nvl(bet.configurator_flag, 'N') = 'Y'
662 ;
663
664 rowcount := SQL%ROWCOUNT;
665
666 IF PG_DEBUG <> 0 THEN
667 oe_debug_pub.add ('get_config_details_bom:Row Count : ' || rowcount, 2);
668 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'Row Count:'||rowcount);
669
670 END IF;
671
672 END LOOP;
673
674 END IF; /* configs only */
675
676 lStmtNumber := 40;
677 IF PG_DEBUG <> 0 THEN
678 oe_debug_pub.add('get_config_details_bcol:rows inserted into bom_expl_temp::'||to_char(sql%rowcount));
679 cto_wip_workflow_api_pk.cto_debug('get_config_details_bcol:', '
680 rows inserted into bom_expl_temp::'||to_char(sql%rowcount));
681
682 END IF;
683
684 EXCEPTION
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 IF PG_DEBUG <> 0 THEN
687 oe_debug_pub.add('get_config_details_bcol:unexp error::'||lStmtNumber||sqlerrm,1);
688 END IF;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 CTO_MSG_PUB.Count_And_Get
691 (p_msg_count => x_msg_count
692 ,p_msg_data => x_msg_data
693 );
694
695 WHEN FND_API.G_EXC_ERROR THEN
696 IF PG_DEBUG <> 0 THEN
697 oe_debug_pub.add('get_config_details_bcol:exp error::'||lStmtNumber||sqlerrm,1);
698 END IF;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 CTO_MSG_PUB.Count_And_Get
701 (p_msg_count => x_msg_count
702 ,p_msg_data => x_msg_data);
703
704 WHEN OTHERS THEN
705 IF PG_DEBUG <> 0 THEN
706 oe_debug_pub.add('get_config_details_bcol:others::'||lStmtNumber||sqlerrm,1);
707 END IF;
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
710 FND_MSG_PUB.Add_Exc_Msg
711 (G_PKG_NAME
712 ,'get_config_details_bcol'
713 );
714 END IF;
715 CTO_MSG_PUB.Count_And_Get
716 (p_msg_count => x_msg_count
717 ,p_msg_data => x_msg_data
718 );
719
720 END; /* get_config_details_bcol */
721
722
723
724 /***********************************************************************
725 This procedure returns the optional components of a configuration item
726 and its child configuration items, based on the configuration BOM. The
727 optional components are populated in table bom_explosion_temp for
728 input parameter group_id.
729 ***********************************************************************/
730 PROCEDURE get_config_details_bom
731 (p_item_id IN NUMBER,
732 p_organization_id IN NUMBER,
733 p_grp_id IN NUMBER,
734 x_msg_count OUT NOCOPY NUMBER,
735 x_msg_data OUT NOCOPY VARCHAR2,
736 x_return_status OUT NOCOPY VARCHAR2)
737
738 IS
739
740 l_sort number := 0;
741 lStmtNumber number;
742 rowcount number;
743
744 BEGIN
745
746 lStmtNumber := 10;
747 x_return_status := FND_API.G_RET_STS_SUCCESS;
748
749 IF PG_DEBUG <> 0 THEN
750 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:','entering');
751 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:','p_grp_id::'||to_char(p_grp_id));
752 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:','p_item_id::'||to_char(p_item_id));
753
754 oe_debug_pub.add('get_config_details_bom:entering');
755 oe_debug_pub.add('get_config_details_bom:p_grp_id::'||to_char(p_grp_id));
756 oe_debug_pub.add('get_config_details_bom:p_item_id::'||to_char(p_item_id));
757 END IF;
758
759 IF (g_configs_only = 'N') THEN
760
761 lStmtNumber := 20;
762
763 -- insert top level config BOM
764 insert into bom_explosion_temp(
765 top_bill_sequence_id, -- not null
766 bill_sequence_id, -- not null
767 organization_id, -- not null
768 sort_order, -- not null
769 assembly_item_id,
770 component_item_id,
771 optional,
772 plan_level, -- not null
773 component_quantity,
774 configurator_flag,
775 primary_uom_code,
776 group_id,
777 basis_type ) /* LBM Project change */
778 select distinct
779 bic.bill_sequence_id,
780 bic.bill_sequence_id,
781 p_organization_id,
782 to_char(l_sort), -- sort
783 p_item_id,
784 bic.component_item_id,
785 bic.optional_on_model, -- optional
786 nvl(bic.plan_level, 0),
787 bic.component_quantity,
788 decode(msi.base_item_id, NULL, 'N', decode(nvl(bic.model_comp_seq_id, bic.last_update_login), 0, 'N', NULL, 'N', abs(nvl(bic.model_comp_seq_id, bic.last_update_login)), 'Y', 'N')), -- config_flag
789 msi.primary_uom_code, -- primary_uom_code
790 p_grp_id,
791 bic.basis_type /* LBM Project change */
792 from
793 bom_inventory_components bic,
794 bom_bill_of_materials bbom,
795 mtl_system_items msi
796 where bbom.assembly_item_id = p_item_id
797 and bbom.organization_id = p_organization_id
798 and bbom.alternate_bom_designator is null
799 and bbom.common_bill_sequence_id = bic.bill_sequence_id
800 and nvl(bic.optional_on_model,2) = 1
801 and msi.inventory_item_id = bic.component_item_id
802 and msi.organization_id = p_organization_id;
803
804
805 IF PG_DEBUG <> 0 THEN
806 oe_debug_pub.add('get_config_details_bom:rowcount::'||sql%rowcount);
807 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'rowcount::'||sql%rowcount);
808
809 END IF;
810
811 lStmtNumber := 30;
812 rowcount := 1 ;
813 WHILE rowcount > 0 LOOP
814
815 l_sort := l_sort + 1;
816
817 insert into bom_explosion_temp(
818 top_bill_sequence_id, -- not null
819 bill_sequence_id, -- not null
820 organization_id, -- not null
821 sort_order, -- not null
822 assembly_item_id,
823 component_item_id,
824 optional,
825 plan_level, -- not null
826 component_quantity,
827 configurator_flag,
828 primary_uom_code,
829 group_id,
830 basis_type) /* LBM Project change */
831 select distinct
832 bic.bill_sequence_id,
833 bic.bill_sequence_id,
834 bbom.organization_id,
835 to_char(l_sort), -- sort
836 bet.component_item_id,
837 bic.component_item_id,
838 bic.optional_on_model, -- optional
839 decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
840 bic.component_quantity,
841 decode(msi2.base_item_id, NULL, 'N', decode(nvl(bic.model_comp_seq_id, bic.last_update_login), 0, 'N', NULL, 'N', abs(nvl(bic.model_comp_seq_id, bic.last_update_login)), 'Y', 'N')), -- config_flag
842 msi2.primary_uom_code, -- primary_uom_code
843 p_grp_id,
844 bic.basis_type /* LBM Project change */
845 from
846 bom_inventory_components bic,
847 bom_bill_of_materials bbom,
848 bom_explosion_temp bet,
849 mtl_system_items msi, -- bet component join
850 mtl_system_items msi2 -- bic component join
851 where
852 bbom.assembly_item_id = bet.component_item_id
853 and bbom.organization_id =
854 (select bbom1.organization_id
855 from bom_bill_of_materials bbom1
856 where bbom1.assembly_item_id = bet.component_item_id
857 and bbom1.alternate_bom_designator is null
858 and rownum = 1)
859 and bbom.common_bill_sequence_id = bic.bill_sequence_id
860 and nvl(bic.optional_on_model,1) = 1
861 and bet.group_id = p_grp_id
862 and bet.sort_order = to_char(l_sort - 1)
863 and bet.component_item_id = msi.inventory_item_id
864 and bbom.organization_id = msi.organization_id
865 and nvl(bet.configurator_flag, 'N') = 'Y'
866 and msi.base_item_id is not null
867 and msi.replenish_to_order_flag = 'Y'
868 and msi2.inventory_item_id = bic.component_item_id
869 and msi2.organization_id = bbom.organization_id;
870
871
872 rowcount := SQL%ROWCOUNT;
873
874 IF PG_DEBUG <> 0 THEN
875 oe_debug_pub.add ('get_config_details_bom:Row Count : ' || rowcount, 2);
876 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'Row Count:'||rowcount);
877
878 END IF;
879
880 END LOOP;
881
882 ELSE /* g_configs_only = Y */
883
884 lStmtNumber := 40;
885
886 -- insert configs from top level config BOM
887 insert into bom_explosion_temp(
888 top_bill_sequence_id, -- not null
889 bill_sequence_id, -- not null
890 organization_id, -- not null
891 sort_order, -- not null
892 assembly_item_id,
893 component_item_id,
894 optional,
895 plan_level, -- not null
896 component_quantity,
897 configurator_flag,
898 primary_uom_code,
899 group_id,
900 basis_type) /* LBM Project change */
901 select distinct
902 bic.bill_sequence_id,
903 bic.bill_sequence_id,
904 p_organization_id,
905 to_char(l_sort), -- sort
906 p_item_id,
907 bic.component_item_id,
908 bic.optional_on_model, -- optional
909 nvl(bic.plan_level, 0),
910 bic.component_quantity,
911 'Y', -- config flag
912 msi.primary_uom_code, -- primary_uom_code
913 p_grp_id,
914 bic.basis_type /* LBM Project change */
915 from
916 bom_inventory_components bic,
917 bom_bill_of_materials bbom,
918 mtl_system_items msi
919 where bbom.assembly_item_id = p_item_id
920 and bbom.organization_id = p_organization_id
921 and bbom.alternate_bom_designator is null
922 and bbom.common_bill_sequence_id = bic.bill_sequence_id
923 and nvl(bic.model_comp_seq_id, bic.last_update_login) = abs(nvl(bic.model_comp_seq_id, bic.last_update_login))
924 and msi.inventory_item_id = bic.component_item_id
925 and msi.organization_id = p_organization_id
926 and msi.base_item_id is not null;
927
928 IF PG_DEBUG <> 0 THEN
929 oe_debug_pub.add('get_config_details_bom:rowcount::'||sql%rowcount);
930 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'rowcount::'||sql%rowcount);
931
932 END IF;
933
934 lStmtNumber := 50;
935 rowcount := 1 ;
936 WHILE rowcount > 0 LOOP
937
938 l_sort := l_sort + 1;
939
940 insert into bom_explosion_temp(
941 top_bill_sequence_id, -- not null
942 bill_sequence_id, -- not null
943 organization_id, -- not null
944 sort_order, -- not null
945 assembly_item_id,
946 component_item_id,
947 optional,
948 plan_level, -- not null
949 component_quantity,
950 configurator_flag,
951 primary_uom_code,
952 group_id,
953 basis_type ) /* LBM Project change */
954 select distinct
955 bic.bill_sequence_id,
956 bic.bill_sequence_id,
957 bbom.organization_id,
958 to_char(l_sort), -- sort
959 bet.component_item_id,
960 bic.component_item_id,
961 bic.optional_on_model, -- optional
962 decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
963 bic.component_quantity,
964 'Y', -- config flag
965 msi.primary_uom_code, -- primary_uom_code
966 p_grp_id,
967 bic.basis_type /* LBM Project change */
968 from
969 bom_inventory_components bic,
970 bom_bill_of_materials bbom,
971 bom_explosion_temp bet,
972 mtl_system_items msi
973 where
974 bbom.assembly_item_id = bet.component_item_id
975 and bbom.organization_id =
976 (select bbom1.organization_id
977 from bom_bill_of_materials bbom1
978 where bbom1.assembly_item_id = bet.component_item_id
979 and bbom1.alternate_bom_designator is null
980 and rownum = 1)
981 and bbom.common_bill_sequence_id = bic.bill_sequence_id
982 and nvl(bic.model_comp_seq_id, bic.last_update_login) = abs(nvl(bic.model_comp_seq_id, bic.last_update_login))
983 and bet.group_id = p_grp_id
984 and bet.sort_order = to_char(l_sort - 1)
985 and bic.component_item_id = msi.inventory_item_id
986 and bbom.organization_id = msi.organization_id
987 and msi.base_item_id is not null
988 and msi.replenish_to_order_flag = 'Y';
989
990 rowcount := SQL%ROWCOUNT;
991
992 IF PG_DEBUG <> 0 THEN
993 oe_debug_pub.add ('get_config_details_bom:Row Count : ' || rowcount, 2);
994 cto_wip_workflow_api_pk.cto_debug('get_config_details_bom:', 'Row Count:'||rowcount);
995
996 END IF;
997
998 END LOOP;
999
1000 END IF; /* g_configs_only = N */
1001
1002 EXCEPTION
1003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004 IF PG_DEBUG <> 0 THEN
1005 oe_debug_pub.add('get_config_details_bom:unexp error::'||lStmtNumber||sqlerrm,1);
1006 END IF;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 CTO_MSG_PUB.Count_And_Get
1009 (p_msg_count => x_msg_count
1010 ,p_msg_data => x_msg_data
1011 );
1012
1013 WHEN FND_API.G_EXC_ERROR THEN
1014 IF PG_DEBUG <> 0 THEN
1015 oe_debug_pub.add('get_config_details_bom:exp error::'||lStmtNumber||sqlerrm,1);
1016 END IF;
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018 CTO_MSG_PUB.Count_And_Get
1019 (p_msg_count => x_msg_count
1020 ,p_msg_data => x_msg_data);
1021
1022 WHEN OTHERS THEN
1023 IF PG_DEBUG <> 0 THEN
1024 oe_debug_pub.add('get_config_details_bom:others::'||lStmtNumber||sqlerrm,1);
1025 END IF;
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1028 FND_MSG_PUB.Add_Exc_Msg
1029 (G_PKG_NAME
1030 ,'get_config_details_bom'
1031 );
1032 END IF;
1033 CTO_MSG_PUB.Count_And_Get
1034 (p_msg_count => x_msg_count
1035 ,p_msg_data => x_msg_data
1036 );
1037
1038 END; /* get_config_details_bom */
1039
1040
1041 /**************************************************************************
1042 Procedure: Cto_Transfer_Price
1043 Parameters: P_config_item_id
1044 P_selling_oper_unit
1045 P_shipping_oper_unit
1046 P_transaction_uom
1047 P_transaction_id
1048 P_price_list_id
1049 P_global_procurement_flag
1050 P_from_organization_id
1051 P_currency_code
1052 X_transfer_price
1053 X_return_status
1054 X_msg_count
1055 X_msg_data
1056 Description: This API calculates the transfer price for a
1057 configuration item by rolling up the transfer
1058 prices of its optional components.
1059
1060 *****************************************************************************/
1061 Procedure Cto_Transfer_Price (
1062 p_config_item_id IN NUMBER,
1063 p_selling_oper_unit IN NUMBER,
1064 p_shipping_oper_unit IN NUMBER,
1065 p_transaction_uom IN VARCHAR2,
1066 p_transaction_id IN NUMBER,
1067 p_price_list_id IN NUMBER,
1068 p_global_procurement_flag IN VARCHAR2,
1069 p_from_organization_id IN NUMBER DEFAULT NULL,
1070 p_currency_code IN VARCHAR2 DEFAULT NULL,
1071 x_transfer_price OUT NOCOPY NUMBER,
1072 x_currency_code out nocopy varchar2,
1073 x_return_status OUT NOCOPY VARCHAR2,
1074 x_msg_count OUT NOCOPY NUMBER,
1075 x_msg_data OUT NOCOPY VARCHAR2)
1076
1077 IS
1078
1079 x_group_id number;
1080 --x_currency_code varchar2(30);
1081 l_transfer_price number := 0;
1082 lStmtNumber number;
1083 l_base_item_id number;
1084 l_build_in_wip varchar2(1);
1085 l_current_item_id number := p_config_item_id;
1086 l_prim_uom Varchar2(3);
1087
1088 CURSOR c_options (l_item_id number) IS
1089 select component_item_id
1090 , configurator_flag
1091 , component_quantity
1092 , primary_uom_code
1093 from bom_explosion_temp
1094 where group_id = x_group_id
1095 and nvl(optional, 1) = 1
1096 and assembly_item_id = l_item_id;
1097
1098 BEGIN
1099 If PG_DEBUG <> 0 Then
1100 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Entering');
1101 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'p_config_item_id::'||to_char(p_config_item_id));
1102 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'l_current_item_id::'||to_char(l_current_item_id));
1103 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'p_selling_oper_unit::'||to_char(p_selling_oper_unit));
1104 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'p_shipping_oper_unit::'||to_char(p_shipping_oper_unit));
1105 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'p_from_organization_id::'||to_char(p_from_organization_id));
1106 End if;
1107
1108 --
1109 -- validate config
1110 --
1111
1112 -- Modified by Renga Kannan
1113 -- on 01/21/04. We should not check the
1114 -- item from shipping operating unit. Shippin operating unit need not be an inventory organization.
1115 -- But, there is an inventory org associate with each operating unit in the transaction flow. we shoule
1116 -- use this that inventory org now. The inventory org is passed in parameter p_from_organization_id.
1117
1118 select base_item_id,
1119 build_in_wip_flag,
1120 Primary_uom_code
1121 into l_base_item_id,
1122 l_build_in_wip,
1123 l_prim_uom
1124 from mtl_system_items
1125 where inventory_item_id = p_config_item_id
1126 and organization_id = p_from_organization_id; -- org to create the AR
1127 If PG_DEBUG <> 0 Then
1128 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'base_item_id::'||l_base_item_id);
1129 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'build_in_wip_flag::'||l_build_in_wip);
1130 End if;
1131
1132 IF ((l_base_item_id IS NULL) OR (l_build_in_wip <> 'Y')) THEN
1133 x_transfer_price := null;
1134 If PG_DEBUG <> 0 Then
1135 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'ERROR::Config item passed by INV is not valid');
1136 End if;
1137
1138 raise FND_API.G_EXC_ERROR;
1139 END IF;
1140
1141 x_transfer_price := 0;
1142
1143 -- Call API to get optional components
1144 CTO_TRANSFER_PRICE_PK.get_config_details
1145 (
1146 p_item_id => p_config_item_id,
1147 p_org_id => NULL,
1148 p_mode_id => 3,
1149 x_group_id => x_group_id,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data,
1152 x_return_status => x_return_status);
1153
1154 If PG_DEBUG <> 0 Then
1155 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'x_group_id::'||to_char(x_group_id));
1156 End if;
1157
1158 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1159 If PG_DEBUG <> 0 Then
1160 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Exp error in get_config_details:'||sqlerrm);
1161 End if;
1162 raise FND_API.G_EXC_ERROR;
1163 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1164 If PG_DEBUG <> 0 Then
1165 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Unexp error in get_config_details:'||sqlerrm);
1166 End if;
1167 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169
1170 --<< Get_Options >>
1171 FOR v_options IN c_options(l_current_item_id) LOOP
1172 --
1173 -- If option is lower level config, use config item's
1174 -- price instead of rolling up its components
1175 --
1176 If PG_DEBUG <> 0 Then
1177 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'comp being processed::'||to_char(v_options.component_item_id));
1178 End if;
1179
1180 IF v_options.configurator_flag = 'Y' THEN
1181
1182 If PG_DEBUG <> 0 Then
1183 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'comp being processed is a config item');
1184 End if;
1185
1186 INV_TRANSACTION_FLOW_PUB.get_transfer_price_for_item
1187 (
1188 x_return_status => x_return_status
1189 , x_msg_data => x_msg_data
1190 , x_msg_count => x_msg_count
1191 , x_transfer_price => l_transfer_price
1192 , x_currency_code => x_currency_code
1193 , p_api_version => 1
1194 , p_from_org_id => p_shipping_oper_unit
1195 , p_to_org_id => p_selling_oper_unit
1196 , p_transaction_uom => v_options.primary_uom_code
1197 , p_inventory_item_id => v_options.component_item_id
1198 , p_transaction_id => p_transaction_id
1199 , p_price_list_id => p_price_list_id
1200 , p_global_procurement_flag => p_global_procurement_flag
1201 , p_from_organization_id => p_from_organization_id
1202 , p_cto_item_flag => 'Y'
1203 );
1204
1205 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1206 If PG_DEBUG <> 0 Then
1207 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Exp error in get_transfer_price_for_item:'||sqlerrm);
1208 End if;
1209 raise FND_API.G_EXC_ERROR;
1210 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1211 If PG_DEBUG <> 0 Then
1212 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Unexp error in get_transfer_price_for_item:'||sqlerrm);
1213 End if;
1214 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END IF;
1216 If PG_DEBUG <> 0 Then
1217 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Transfer price of comp '||to_char(v_options.component_item_id)||' is '||to_char(l_transfer_price));
1218 End if;
1219
1220 IF l_transfer_price <> 0 THEN
1221 x_transfer_price := x_transfer_price + l_transfer_price * v_options.component_quantity;
1222 ELSE
1223 cto_transfer_price(
1224 p_config_item_id => v_options.component_item_id,
1225 p_selling_oper_unit => p_selling_oper_unit,
1226 p_shipping_oper_unit => p_shipping_oper_unit,
1227 p_transaction_uom => v_options.primary_uom_code,
1228 p_transaction_id => p_transaction_id,
1229 p_price_list_id => p_price_list_id,
1230 p_global_procurement_flag => p_global_procurement_flag,
1231 p_from_organization_id => p_from_organization_id,
1232 p_currency_code => p_currency_code,
1233 x_transfer_price => l_transfer_price,
1234 x_currency_code => x_currency_code,
1235 x_return_status => x_return_status,
1236 x_msg_count => x_msg_count,
1237 x_msg_data => x_msg_data);
1238
1239 x_transfer_price := x_transfer_price + l_transfer_price * v_options.component_quantity;
1240 END IF;
1241
1242 ELSE /* if not config item */
1243
1244 INV_TRANSACTION_FLOW_PUB.get_transfer_price_for_item
1245 (
1246 x_return_status => x_return_status
1247 , x_msg_data => x_msg_data
1248 , x_msg_count => x_msg_count
1249 , x_transfer_price => l_transfer_price
1250 , x_currency_code => x_currency_code
1251 , p_api_version => 1
1252 , p_from_org_id => p_shipping_oper_unit
1253 , p_to_org_id => p_selling_oper_unit
1254 , p_transaction_uom => v_options.primary_uom_code
1255 , p_inventory_item_id => v_options.component_item_id
1256 , p_transaction_id => p_transaction_id
1257 , p_price_list_id => p_price_list_id
1258 , p_global_procurement_flag => p_global_procurement_flag
1259 , p_from_organization_id => p_from_organization_id
1260 --Bugfix 15906840: FP for bug 14791768.
1261 --, p_cto_item_flag => 'Y'
1262 , p_cto_item_flag => 'N'
1263 );
1264
1265 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1266 If PG_DEBUG <> 0 Then
1267 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Exp error in get_transfer_price_for_item:'||sqlerrm);
1268 End if;
1269 raise FND_API.G_EXC_ERROR;
1270 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1271 If PG_DEBUG <> 0 Then
1272 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Unexp error in get_transfer_price_for_item:'||sqlerrm);
1273 End if;
1274 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276
1277 If PG_DEBUG <> 0 Then
1278 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Transfer price of comp '||to_char(v_options.component_item_id)||' is '||to_char(l_transfer_price));
1279 End if;
1280 x_transfer_price := x_transfer_price + l_transfer_price * v_options.component_quantity;
1281
1282 END IF; /* if config item */
1283
1284 END LOOP; /* cursor loop */
1285
1286 x_transfer_price := x_transfer_price/CTO_UTILITY_PK.convert_uom(from_uom => l_prim_uom,
1287 to_uom => p_transaction_uom,
1288 quantity => 1,
1289 item_id => p_config_item_id);
1290
1291 If PG_DEBUG <> 0 Then
1292 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Total Transfer price of item is '||to_char(x_transfer_price));
1293 End if;
1294
1295 EXCEPTION
1296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1297 If PG_DEBUG <> 0 Then
1298 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Unexpected error:'||lStmtNumber||sqlerrm);
1299 End if;
1300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1301 CTO_MSG_PUB.Count_And_Get
1302 (p_msg_count => x_msg_count
1303 ,p_msg_data => x_msg_data
1304 );
1305
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 If PG_DEBUG <> 0 Then
1308 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Expected error:'||lStmtNumber||sqlerrm);
1309 End if;
1310 x_return_status := FND_API.G_RET_STS_ERROR;
1311 CTO_MSG_PUB.Count_And_Get
1312 (p_msg_count => x_msg_count
1313 ,p_msg_data => x_msg_data);
1314
1315 WHEN OTHERS THEN
1316 If PG_DEBUG <> 0 Then
1317 cto_wip_workflow_api_pk.cto_debug('cto_transfer_price:', 'Others error:'||lStmtNumber||sqlerrm);
1318 End if;
1319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1320 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1321 FND_MSG_PUB.Add_Exc_Msg
1322 (G_PKG_NAME
1323 ,'cto_transfer_price'
1324 );
1325 END IF;
1326 CTO_MSG_PUB.Count_And_Get
1327 (p_msg_count => x_msg_count
1328 ,p_msg_data => x_msg_data
1329 );
1330
1331 END; /* cto_transfer_price */
1332
1333 END CTO_TRANSFER_PRICE_PK;