[Home] [Help]
PACKAGE BODY: APPS.CSD_ESTIMATES_FROM_BOM_PVT
Source
1 PACKAGE BODY CSD_ESTIMATES_FROM_BOM_PVT AS
2 /* $Header: csdvbomb.pls 120.3.12020000.2 2013/04/10 20:57:54 yvchen ship $*/
3
4 g_business_process_id number;
5 g_txn_billing_type_id NUMBER;
6 g_transaction_type_id NUMBER;
7 -- bug# 6890910 subhat
8 -- contract number can be alpha numeric
9 g_contract_num varchar2(120);
10 -- end bug #6890910
11 gc_delimiter CONSTANT VARCHAR2(1) := ':';
12
13 g_repair_line_id NUMBER; --bug#10187275,subhat
14
15 /*--------------------------------------------------------------------------------*/
16 /*procedure Name: Explode_bom_items
17 /*Description: This is the wrapper API for the BOM Exploder. */
18 /*This takes in the Item to be expanded and inserts the explosion hierarchy */
19 /*to csd_bom_expl_tmp temp table. The temp table will be flushed whenever a */
20 /*commit is issued in the session or when a session closes. */
21 /*@ param: P_Item - The Name of the item to be expanded. */
22 /*@ param:p_alt_bom - Alternate, if any, for the item. Default null */
23 /*--------------------------------------------------------------------------------*/
24 PROCEDURE explode_bom_items(p_itemId IN NUMBER,p_alt_bom IN VARCHAR2 DEFAULT NULL ) IS
25
26 l_bom_exp_tab bompxinq.bomexporttabtype;
27 l_err_msg varchar2(2000);
28 l_err_code number;
29 l_count number;
30 l_material_billable_flag varchar2(2);
31 l_dummy varchar2(2) := null;
32 l_inventory_item_id number;
33 l_org_id NUMBER := fnd_profile.value('CSD_DEF_REP_INV_ORG'); --cs_std.get_item_valdn_orgzn_id; bug#10187275,subhat
34 l_profile_id number;
35 --bug#6930575,subhat.
36 l_effectivity_control number;
37 l_max_level number;
38 l_unit_number_from varchar2(50) := null;
39 l_unit_number_to varchar2(50) := null;
40 l_group_id number;
41 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
42
43 lc_mod_name varchar2(60) := 'csd.plsql.csd_estimates_from_bom_pvt.explode_bom_items';
44
45 exploder_error exception;
46
47 BEGIN
48
49 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
50 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'begin',
51 'Entering explode_bom_items');
52 END IF;
53 --bug#6930575, subhat.
54 -- we will use exploder_userexit API no need for profile_id
55 -- get the security profile id for the current session
56 --l_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
57
58 t_org_code_list(1) := l_org_id;
59 -- bug#6930575, subhat
60 -- export_bom api doesnt work fine for Model Unit Controlled items.
61 -- need to use exploder_userexit as BOM team is reluctant to fix this bug in 12.1
62
63 -- Check if the item is MU controlled
64
65 select effectivity_control into l_effectivity_control
66 from mtl_system_items_b
67 where inventory_item_id = p_itemId
68 and organization_id = l_org_id;
69 -- get the max and min unit numbers if the item is MU controlled.
70 if nvl(l_effectivity_control,0) = 2 then
71 select min(unit_number),max(unit_number) into
72 l_unit_number_from,l_unit_number_to
73 from pjm_unit_numbers
74 where master_organization_id = fnd_profile.value('ORG_ID');
75 end if;
76 -- get the maximum explosion level.
77 SELECT MAXIMUM_BOM_LEVEL INTO l_max_level
78 FROM BOM_PARAMETERS
79 WHERE ORGANIZATION_ID = l_org_id;
80 -- get the group id used for explosion.
81 SELECT bom_explosion_temp_s.nextval INTO l_group_id from dual;
82 -- clear the bom temporary table
83 delete from bom_small_expl_temp;
84 -- calling exploder_userexit API.
85 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
86 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'begin',
87 'Calling exploder_userexit');
88 end if;
89
90 bompxinq.exploder_userexit (
91 verify_flag => 0 ,
92 org_id => t_org_code_list(1),
93 order_by => 2 ,
94 grp_id => l_group_id ,
95 session_id => 0 ,
96 levels_to_explode => l_max_level,
97 bom_or_eng => 1,
98 impl_flag => 1,
99 plan_factor_flag => 2,
100 explode_option => 3,
101 module => 2 ,
102 unit_number_from => l_unit_number_from,
103 unit_number_to => l_unit_number_to,
104 cst_type_id => 0 ,
105 std_comp_flag => 2 ,
106 expl_qty => 1,
107 item_id => p_itemId ,
108 alt_desg => p_alt_bom ,
109 comp_code => null,
110 rev_date => sysdate ,
111 show_rev => 1 ,
112 material_ctrl => 1,
113 lead_time => 1,
114 err_msg => l_err_msg ,
115 error_code => l_err_code);
116 if l_err_code = 9998 then
117 raise exploder_error;
118 end if;
119 if l_err_code = 0 or l_err_code = null then
120
121 delete from csd_bom_expl_temp;
122 INSERT INTO csd_bom_expl_temp (
123 TOP_BILL_SEQUENCE_ID ,
124 BILL_SEQUENCE_ID ,
125 COMMON_BILL_SEQUENCE_ID ,
126 ORGANIZATION_ID ,
127 COMPONENT_SEQUENCE_ID ,
128 COMPONENT_ITEM_ID ,
129 BASIS_TYPE ,
130 COMPONENT_QUANTITY ,
131 PLAN_LEVEL ,
132 EXTENDED_QUANTITY ,
133 SORT_ORDER ,
134 GROUP_ID ,
135 TOP_ALTERNATE_DESIGNATOR ,
136 COMPONENT_YIELD_FACTOR ,
137 TOP_ITEM_ID ,
138 COMPONENT_CODE ,
139 INCLUDE_IN_ROLLUP_FLAG ,
140 LOOP_FLAG ,
141 PLANNING_FACTOR ,
142 OPERATION_SEQ_NUM ,
143 BOM_ITEM_TYPE ,
144 PARENT_BOM_ITEM_TYPE ,
145 ASSEMBLY_ITEM_ID ,
146 WIP_SUPPLY_TYPE ,
147 ITEM_NUM ,
148 EFFECTIVITY_DATE ,
149 DISABLE_DATE ,
150 IMPLEMENTATION_DATE ,
151 OPTIONAL ,
152 SUPPLY_SUBINVENTORY ,
153 SUPPLY_LOCATOR_ID ,
154 COMPONENT_REMARKS ,
155 CHANGE_NOTICE ,
156 OPERATION_LEAD_TIME_PERCENT,
157 MUTUALLY_EXCLUSIVE_OPTIONS ,
158 CHECK_ATP ,
159 REQUIRED_TO_SHIP ,
160 REQUIRED_FOR_REVENUE ,
161 INCLUDE_ON_SHIP_DOCS ,
162 LOW_QUANTITY ,
163 HIGH_QUANTITY ,
164 SO_BASIS ,
165 OPERATION_OFFSET ,
166 CURRENT_REVISION ,
167 LOCATOR ,
168 CONTEXT ,
169 ATTRIBUTE1 ,
170 ATTRIBUTE2 ,
171 ATTRIBUTE3 ,
172 ATTRIBUTE4 ,
173 ATTRIBUTE5 ,
174 ATTRIBUTE6 ,
175 ATTRIBUTE7 ,
176 ATTRIBUTE8 ,
177 ATTRIBUTE9 ,
178 ATTRIBUTE10 ,
179 ATTRIBUTE11 ,
180 ATTRIBUTE12 ,
181 ATTRIBUTE13 ,
182 ATTRIBUTE14 ,
183 ATTRIBUTE15 ,
184 ITEM_COST ,
185 EXTEND_COST_FLAG
186 )
187
188 Select
189 TOP_BILL_SEQUENCE_ID ,
190 BILL_SEQUENCE_ID ,
191 COMMON_BILL_SEQUENCE_ID ,
192 ORGANIZATION_ID ,
193 COMPONENT_SEQUENCE_ID ,
194 COMPONENT_ITEM_ID ,
195 BASIS_TYPE ,
196 COMPONENT_QUANTITY ,
197 PLAN_LEVEL ,
198 EXTENDED_QUANTITY ,
199 SORT_ORDER ,
200 GROUP_ID ,
201 TOP_ALTERNATE_DESIGNATOR ,
202 COMPONENT_YIELD_FACTOR ,
203 TOP_ITEM_ID ,
204 COMPONENT_CODE ,
205 INCLUDE_IN_ROLLUP_FLAG ,
206 LOOP_FLAG ,
207 PLANNING_FACTOR ,
208 OPERATION_SEQ_NUM ,
209 BOM_ITEM_TYPE ,
210 PARENT_BOM_ITEM_TYPE ,
211 ASSEMBLY_ITEM_ID ,
212 WIP_SUPPLY_TYPE ,
213 ITEM_NUM ,
214 EFFECTIVITY_DATE ,
215 DISABLE_DATE ,
216 IMPLEMENTATION_DATE ,
217 OPTIONAL ,
218 SUPPLY_SUBINVENTORY ,
219 SUPPLY_LOCATOR_ID ,
220 COMPONENT_REMARKS ,
221 CHANGE_NOTICE ,
222 OPERATION_LEAD_TIME_PERCENT,
223 MUTUALLY_EXCLUSIVE_OPTIONS ,
224 CHECK_ATP ,
225 REQUIRED_TO_SHIP ,
226 REQUIRED_FOR_REVENUE ,
227 INCLUDE_ON_SHIP_DOCS ,
228 LOW_QUANTITY ,
229 HIGH_QUANTITY ,
230 SO_BASIS ,
231 OPERATION_OFFSET ,
232 CURRENT_REVISION ,
233 LOCATOR ,
234 CONTEXT ,
235 ATTRIBUTE1 ,
236 ATTRIBUTE2 ,
237 ATTRIBUTE3 ,
238 ATTRIBUTE4 ,
239 ATTRIBUTE5 ,
240 ATTRIBUTE6 ,
241 ATTRIBUTE7 ,
242 ATTRIBUTE8 ,
243 ATTRIBUTE9 ,
244 ATTRIBUTE10 ,
245 ATTRIBUTE11 ,
246 ATTRIBUTE12 ,
247 ATTRIBUTE13 ,
248 ATTRIBUTE14 ,
249 ATTRIBUTE15 ,
250 ITEM_COST ,
251 EXTEND_COST_FLAG
252 from bom_small_expl_temp
253 where group_id = l_group_id;
254
255 end if;
256
257 -- bug#6930575,subhat. commented the previous API call.
258 -- Call the BOM exploder API to get the PL/SQL table of explosion items.
259
260 /*bompxinq.export_bom(profile_id =>l_profile_id,
261 org_hierarchy_name => null,
262 assembly_item_id => p_itemId,
263 organization_id => l_org_id,
264 alternate_bm_designator => P_alt_bom,
265 bom_export_tab => l_bom_exp_tab,
266 err_msg => l_err_msg,
267 error_code => l_err_code );
268
269 l_count := l_bom_exp_tab.COUNT;
270
271 -- clear the temp table.
272
273 DELETE FROM csd_bom_expl_temp;
274
275 /*FOR i IN 1 ..l_count
276 LOOP
277
278 -- Insert the values into the temp table.
279
280 INSERT INTO csd_bom_expl_temp (
281 top_bill_sequence_id,
282 bill_sequence_id,
283 organization_id,
284 component_sequence_id,
285 component_item_id,
286 plan_level,
287 extended_quantity,
288 sort_order,
289 request_id,
290 program_application_id,
291 program_id,
292 program_update_date,
293 GROUP_ID,
294 session_id,
295 select_flag,
296 select_quantity,
297 extend_cost_flag,
298 top_alternate_designator,
299 top_item_id,
300 CONTEXT,
301 attribute1, attribute2,
302 attribute3, attribute4,
303 attribute5, attribute6,
304 attribute7, attribute8,
305 attribute9, attribute10,
306 attribute11, attribute12,
307 attribute13, attribute14,
308 attribute15, header_id,
309 line_id, list_price,
310 selling_price,
311 component_yield_factor,
312 item_cost,
313 include_in_rollup_flag,
314 based_on_rollup_flag,
315 actual_cost_type_id,
316 component_quantity,
317 shrinkage_rate, so_basis,
318 optional,
319 mutually_exclusive_options,
320 check_atp, shipping_allowed,
321 required_to_ship,
322 required_for_revenue,
323 include_on_ship_docs,
324 include_on_bill_docs,
325 low_quantity, high_quantity,
326 pick_components,
327 primary_uom_code,
328 primary_unit_of_measure,
329 base_item_id,
330 atp_components_flag, atp_flag,
331 bom_item_type,
332 pick_components_flag,
333 replenish_to_order_flag,
334 shippable_item_flag,
335 customer_order_flag,
336 internal_order_flag,
337 customer_order_enabled_flag,
338 internal_order_enabled_flag,
339 so_transactions_flag,
340 mtl_transactions_enabled_flag,
341 stock_enabled_flag,
342 description, assembly_item_id,
343 configurator_flag,
344 price_list_id, rounding_factor,
345 pricing_context,
346 pricing_attribute1,
347 pricing_attribute2,
348 pricing_attribute3,
349 pricing_attribute4,
350 pricing_attribute5,
351 pricing_attribute6,
352 pricing_attribute7,
353 pricing_attribute8,
354 pricing_attribute9,
355 pricing_attribute10,
356 pricing_attribute11,
357 pricing_attribute12,
358 pricing_attribute13,
359 pricing_attribute14,
360 pricing_attribute15,
361 component_code, loop_flag,
362 inventory_asset_flag,
363 planning_factor,
364 operation_seq_num,
365 parent_bom_item_type,
366 wip_supply_type, item_num,
367 effectivity_date, disable_date,
368 implementation_date,
369 supply_subinventory,
370 supply_locator_id,
371 component_remarks,
372 change_notice,
373 operation_lead_time_percent,
374 rexplode_flag,
375 common_bill_sequence_id,
376 operation_offset,
377 current_revision, LOCATOR,
378 from_end_item_unit_number,
379 to_end_item_unit_number,
380 basis_type
381 )
382 VALUES (
383 l_bom_exp_tab(i).top_bill_sequence_id,
384 l_bom_exp_tab(i).bill_sequence_id,
385 l_bom_exp_tab(i).organization_id,
386 l_bom_exp_tab(i).component_sequence_id,
387 l_bom_exp_tab(i).component_item_id,
388 l_bom_exp_tab(i).plan_level,
389 l_bom_exp_tab(i).extended_quantity,
390 l_bom_exp_tab(i).sort_order,
391 l_bom_exp_tab(i).request_id,
392 l_bom_exp_tab(i).program_application_id,
393 l_bom_exp_tab(i).program_id,
394 l_bom_exp_tab(i).program_update_date,
395 l_bom_exp_tab(i).GROUP_ID,
396 l_bom_exp_tab(i).session_id,
397 l_bom_exp_tab(i).select_flag,
398 l_bom_exp_tab(i).select_quantity,
399 l_bom_exp_tab(i).extend_cost_flag,
400 l_bom_exp_tab(i).top_alternate_designator,
401 l_bom_exp_tab(i).top_item_id,
402 l_bom_exp_tab(i).CONTEXT,
403 l_bom_exp_tab(i).attribute1, l_bom_exp_tab(i).attribute2,
404 l_bom_exp_tab(i).attribute3, l_bom_exp_tab(i).attribute4,
405 l_bom_exp_tab(i).attribute5, l_bom_exp_tab(i).attribute6,
406 l_bom_exp_tab(i).attribute7, l_bom_exp_tab(i).attribute8,
407 l_bom_exp_tab(i).attribute9, l_bom_exp_tab(i).attribute10,
408 l_bom_exp_tab(i).attribute11, l_bom_exp_tab(i).attribute12,
409 l_bom_exp_tab(i).attribute13, l_bom_exp_tab(i).attribute14,
410 l_bom_exp_tab(i).attribute15, l_bom_exp_tab(i).header_id,
411 l_bom_exp_tab(i).line_id, l_bom_exp_tab(i).list_price,
412 l_bom_exp_tab(i).selling_price,
413 l_bom_exp_tab(i).component_yield_factor,
414 l_bom_exp_tab(i).item_cost,
415 l_bom_exp_tab(i).include_in_rollup_flag,
416 l_bom_exp_tab(i).based_on_rollup_flag,
417 l_bom_exp_tab(i).actual_cost_type_id,
418 l_bom_exp_tab(i).component_quantity,
419 l_bom_exp_tab(i).shrinkage_rate, l_bom_exp_tab(i).so_basis,
420 l_bom_exp_tab(i).optional,
421 l_bom_exp_tab(i).mutually_exclusive_options,
422 l_bom_exp_tab(i).check_atp, l_bom_exp_tab(i).shipping_allowed,
423 l_bom_exp_tab(i).required_to_ship,
424 l_bom_exp_tab(i).required_for_revenue,
425 l_bom_exp_tab(i).include_on_ship_docs,
426 l_bom_exp_tab(i).include_on_bill_docs,
427 l_bom_exp_tab(i).low_quantity, l_bom_exp_tab(i).high_quantity,
428 l_bom_exp_tab(i).pick_components,
429 l_bom_exp_tab(i).primary_uom_code,
430 l_bom_exp_tab(i).primary_unit_of_measure,
431 l_bom_exp_tab(i).base_item_id,
432 l_bom_exp_tab(i).atp_components_flag, l_bom_exp_tab(i).atp_flag,
433 l_bom_exp_tab(i).bom_item_type,
434 l_bom_exp_tab(i).pick_components_flag,
435 l_bom_exp_tab(i).replenish_to_order_flag,
436 l_bom_exp_tab(i).shippable_item_flag,
437 l_bom_exp_tab(i).customer_order_flag,
438 l_bom_exp_tab(i).internal_order_flag,
439 l_bom_exp_tab(i).customer_order_enabled_flag,
440 l_bom_exp_tab(i).internal_order_enabled_flag,
441 l_bom_exp_tab(i).so_transactions_flag,
442 l_bom_exp_tab(i).mtl_transactions_enabled_flag,
443 l_bom_exp_tab(i).stock_enabled_flag,
444 l_bom_exp_tab(i).description, l_bom_exp_tab(i).assembly_item_id,
445 l_bom_exp_tab(i).configurator_flag,
446 l_bom_exp_tab(i).price_list_id, l_bom_exp_tab(i).rounding_factor,
447 l_bom_exp_tab(i).pricing_context,
448 l_bom_exp_tab(i).pricing_attribute1,
449 l_bom_exp_tab(i).pricing_attribute2,
450 l_bom_exp_tab(i).pricing_attribute3,
451 l_bom_exp_tab(i).pricing_attribute4,
452 l_bom_exp_tab(i).pricing_attribute5,
453 l_bom_exp_tab(i).pricing_attribute6,
454 l_bom_exp_tab(i).pricing_attribute7,
455 l_bom_exp_tab(i).pricing_attribute8,
456 l_bom_exp_tab(i).pricing_attribute9,
457 l_bom_exp_tab(i).pricing_attribute10,
458 l_bom_exp_tab(i).pricing_attribute11,
459 l_bom_exp_tab(i).pricing_attribute12,
460 l_bom_exp_tab(i).pricing_attribute13,
461 l_bom_exp_tab(i).pricing_attribute14,
462 l_bom_exp_tab(i).pricing_attribute15,
463 l_bom_exp_tab(i).component_code, l_bom_exp_tab(i).loop_flag,
464 l_bom_exp_tab(i).inventory_asset_flag,
465 l_bom_exp_tab(i).planning_factor,
466 l_bom_exp_tab(i).operation_seq_num,
467 l_bom_exp_tab(i).parent_bom_item_type,
468 l_bom_exp_tab(i).wip_supply_type, l_bom_exp_tab(i).item_num,
469 l_bom_exp_tab(i).effectivity_date, l_bom_exp_tab(i).disable_date,
470 l_bom_exp_tab(i).implementation_date,
471 l_bom_exp_tab(i).supply_subinventory,
472 l_bom_exp_tab(i).supply_locator_id,
473 l_bom_exp_tab(i).component_remarks,
474 l_bom_exp_tab(i).change_notice,
475 l_bom_exp_tab(i).operation_lead_time_percent,
476 l_bom_exp_tab(i).rexplode_flag,
477 l_bom_exp_tab(i).common_bill_sequence_id,
478 l_bom_exp_tab(i).operation_offset,
479 l_bom_exp_tab(i).current_revision, l_bom_exp_tab(i).LOCATOR,
480 l_bom_exp_tab(i).from_end_item_unit_number,
481 l_bom_exp_tab(i).to_end_item_unit_number,
482 l_bom_exp_tab(i).basis_type
483 ); */
484
485 --END loop;
486
487
488 EXCEPTION
489 when exploder_error then
490 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
491 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'Exploder_error',
492 'Error occured while executing bom_exploder'||l_err_msg);
493 END IF;
494 rollback;
495 WHEN OTHERS THEN
496 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
497 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'others exception',
498 'an unknown error occured');
499 end if;
500 rollback;
501 END explode_bom_items;
502
503 /* This is the wrapper API for the standard Depot API for the estimate lines. */
504 /* This takes a table of Itemid-quantity-primary UOM and creates estimate lines */
505 /* in Loop. This procedure in turn makes calls to other depot standard api's to get the */
506 /* different values. */
507 /* parameters: */
508 /*p_itemQty - This is a table type. Contains the data in the format "invItemid:qty:uom" */
509 /* : is the delimiter. */
510 /*p_repair_line_id: */
511 /*p_repair_type_id: */
512 /*p_currency_code: */
513 /*p_org_id: */
514 /*p_repair_estimate_id: */
515 /*p_contract_line_id: */
516 /*p_incident_id: Incident Id for the repair. */
517 /*x_return_Status: OUT parameter. */
518
519 PROCEDURE create_estimate_lines(p_itemQty IN varchar2_table_200,
520 p_repair_line_id IN NUMBER,
521 p_repair_type_id IN NUMBER,
522 p_currency_code IN VARCHAR2,
523 p_org_id IN NUMBER,
524 p_repair_estimate_id IN NUMBER,
525 p_pricelist_header_id IN NUMBER,
526 p_contract_line_id IN NUMBER default null,
527 p_incident_id IN NUMBER,
528 p_init_msg_list IN VARCHAR2,
529 x_msg_data OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_return_status OUT NOCOPY varchar2) IS
532 l_selling_price number;
533 l_discount_price number;
534 l_item_cost number;
535 l_contract_number number;
536 l_item_id number;
537 l_quantity number;
538 l_item_name varchar2(100);
539 l_count number;
540 l_uom varchar2(10);
541 l_contract_discount_amnt NUMBER;
542 l_estimate_line_id NUMBER;
543
544 lc_mod_name varchar2(100) := 'csd.plsql.csd_estimates_from_bom_pvt.create_estimate_lines';
545 l_org_id NUMBER := p_org_id; --bug#13980152, yvchen
546
547 -- estimate lines rec.
548
549 l_estimate_lines_rec CSD_REPAIR_ESTIMATE_PVT.REPAIR_ESTIMATE_LINE_REC := csd_process_util.ui_estimate_line_rec;
550
551 BEGIN
552 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
553 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'begin',
554 'Entering create_estimate_lines');
555 END IF;
556
557 -- if message init is passed as yes initialize message stack.
558
559 IF FND_API.to_boolean(p_init_msg_list) THEN
560 fnd_msg_pub.initialize;
561 END IF;
562
563 -- set the return status as success status.
564
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566
567 g_repair_line_id := p_repair_line_id; -- --bug#10187275,subhat
568 -- get the billing type id based on the repair_type and billing type
569
570 BEGIN
571 SELECT txn_billing_type_id,transaction_type_id
572 INTO g_txn_billing_type_id,g_transaction_type_id
573 FROM csd_repair_types_sar_vl
574 WHERE repair_type_id = p_repair_type_id
575 AND billing_type = 'M';
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 fnd_message.set_name('CSD','CSD_API_INV_TXN_BILLING_TYPE');
579 fnd_msg_pub.add;
580 RAISE FND_API.G_EXC_ERROR;
581 END;
582
583 -- the required parameter validation is done in the java layer.
584 l_count := p_itemQty.COUNT;
585 --dbms_output.put_line('starting loop');
586 FOR i IN 1 ..l_count
587 LOOP
588 l_item_id := substr(p_itemQty(i),1,(instr(p_itemQty(i),gc_delimiter,1,1) - 1));
589
590 l_quantity := substr(p_itemQty(i),(instr(p_itemQty(i),gc_delimiter) + 1),(instr(p_itemQty(i),gc_delimiter,-1,1) - (instr(p_itemQty(i),gc_delimiter) + 1)));
591 l_uom := substr(p_itemQty(i),instr(p_itemQty(i),gc_delimiter,-1,1)+1,length(p_itemQty(i)));
592
593 -- retrieve the contract number.
594
595 IF p_contract_line_id IS NOT NULL THEN
596 l_contract_number := get_default_contract(p_contract_line_id,p_repair_type_id,x_msg_count,x_msg_data,x_return_status);
597 ELSE
598 l_contract_number := null;
599 END IF;
600
601 -- retrieve the Unit cost of the item.
602 --bug#10187275,subhat
603 l_item_cost := get_item_Cost(l_item_id,l_uom,p_currency_code, fnd_profile.value('CSD_DEF_REP_INV_ORG'),x_msg_count,x_msg_data,x_return_status);
604
605 -- retrieve the selling price for the item.
606 -- bug#13980152, yvchen
607 -- if the SR header doesn't have the operating unit information, then derive the same.
608 IF l_org_id IS NULL
609 THEN
610 l_org_id := csd_process_util.get_org_id(p_incident_id);
611 END IF;
612
613 -- bug#13980152, yvchen: changed p_org_id to l_org_id
614 l_selling_price := get_selling_price(l_item_id,l_uom,l_quantity,p_pricelist_header_id,p_currency_code,l_org_id,
615 x_msg_count,x_msg_data,x_return_status);
616
617 -- get the discounted price for the item.
618 -- If there is no contract applied for the repair, there is no discounted price.
619 --dbms_output.put_line('Discount '||l_selling_price);
620 if p_contract_line_id is not null then
621 l_discount_price := get_discount_price(p_contract_line_id,p_repair_type_id,l_selling_price,l_quantity,
622 x_msg_count,x_msg_data,x_return_status);
623 else
624 l_discount_price := nvl(l_quantity,0) * nvl(l_selling_price,0);
625 end if;
626
627 -- initialize the repair estimate line rec.
628 l_estimate_lines_rec.repair_estimate_line_id := null;
629 l_estimate_lines_rec.repair_estimate_id := p_repair_estimate_id;
630 l_estimate_lines_rec.repair_line_id := p_repair_line_id;
631 l_estimate_lines_rec.estimate_detail_id := null;
632 l_estimate_lines_rec.incident_id := p_incident_id; --l_incident_id;
633 l_estimate_lines_rec.transaction_type_id := g_transaction_type_id;
634 l_estimate_lines_rec.business_process_id := g_business_process_id;
635 l_estimate_lines_rec.txn_billing_type_id := g_txn_billing_type_id;
636 l_estimate_lines_rec.original_source_id := null;
637 l_estimate_lines_rec.original_source_code := null;
638 l_estimate_lines_rec.source_id := null;
639 l_estimate_lines_rec.source_code := null;
640 if (l_item_cost = 0) then
641 l_estimate_lines_rec.item_cost := null;
642 else
643 l_estimate_lines_rec.item_cost := l_item_Cost;
644 end if;
645
646 l_estimate_lines_rec.customer_product_id := null;
647 l_estimate_lines_rec.reference_number := null;
648 l_estimate_lines_rec.item_revision := null;
649 l_estimate_lines_rec.justification_notes := null;
650 l_estimate_lines_rec.estimate_status := 'NEW';
651 -- by default the status will be new for the ones created from
652 -- bom selection screen.
653 l_estimate_lines_rec.order_number := null;
654 l_estimate_lines_rec.purchase_order_num := NULL;-- name_in('Global.default_po_number');
655 l_estimate_lines_rec.source_number := null;
656 l_estimate_lines_rec.status := 'O';
657 l_estimate_lines_rec.currency_code := p_currency_code;
658 l_estimate_lines_rec.line_category_code := 'ORDER';
659 l_estimate_lines_rec.unit_of_measure_code := l_uom;
660 l_estimate_lines_rec.original_source_number := null;
661 l_estimate_lines_rec.order_header_id := null;
662 l_estimate_lines_rec.order_line_id := null;
663 l_estimate_lines_rec.inventory_item_id := l_item_id;
664 l_estimate_lines_rec.after_warranty_cost := l_discount_price;
665 l_estimate_lines_rec.selling_price := l_selling_price;
666 l_estimate_lines_rec.original_system_reference := null;
667 l_estimate_lines_rec.estimate_quantity := l_quantity;
668 l_estimate_lines_rec.serial_number := null;
669 l_estimate_lines_rec.lot_number := null;
670 l_estimate_lines_rec.instance_id := null;
671 l_estimate_lines_rec.instance_number := null;
672 l_estimate_lines_rec.price_list_id := p_pricelist_header_id;
673 /*contracts re arch changes for R12 */
674 l_estimate_lines_rec.contract_line_id := p_contract_line_id;
675 l_estimate_lines_rec.contract_id := l_contract_number;
676 l_estimate_lines_rec.contract_number := g_contract_num;
677 l_estimate_lines_rec.coverage_id := NULL ; --g_coverage_id;
678 l_estimate_lines_rec.coverage_txn_group_id := NULL;--g_coverage_txn_group_id;
679 l_estimate_lines_rec.coverage_bill_rate_id := null;
680 l_estimate_lines_rec.sub_inventory := null;
681
682 l_estimate_lines_rec.organization_id := l_org_id; --bug#13980152, yvchen: changed p_org_id to l_org_id
683 l_estimate_lines_rec.invoice_to_org_id := null;--cs_std.get_item_valdn_orgzn_id;
684 l_estimate_lines_rec.ship_to_org_id := null;--p_ship_to_org_id;
685 l_estimate_lines_rec.no_charge_flag := 'N';
686 l_estimate_lines_rec.return_reason := null;
687 l_estimate_lines_rec.return_by_date := SYSDATE;
688 l_estimate_lines_rec.last_update_date := null;
689 l_estimate_lines_rec.creation_date := null;
690 l_estimate_lines_rec.last_updated_by := null;
691 l_estimate_lines_rec.created_by := null;
692 l_estimate_lines_rec.last_update_login := null;
693 l_estimate_lines_rec.attribute1 := null;
694 l_estimate_lines_rec.attribute2 := null;
695 l_estimate_lines_rec.attribute3 := null;
696 l_estimate_lines_rec.attribute4 := null;
697 l_estimate_lines_rec.attribute5 := null;
698 l_estimate_lines_rec.attribute6 := null;
699 l_estimate_lines_rec.attribute7 := null;
700 l_estimate_lines_rec.attribute8 := null;
701 l_estimate_lines_rec.attribute9 := null;
702 l_estimate_lines_rec.attribute10 := null;
703 l_estimate_lines_rec.attribute11 := null;
704 l_estimate_lines_rec.attribute12 := null;
705 l_estimate_lines_rec.attribute13 := null;
706 l_estimate_lines_rec.attribute14 := null;
707 l_estimate_lines_rec.attribute15 := null;
708 l_estimate_lines_rec.context := null;
709 l_estimate_lines_rec.object_version_number := 1;
710 l_estimate_lines_rec.security_group_id := null;
711 l_estimate_lines_rec.resource_id := null;
712 l_estimate_lines_rec.override_charge_flag := 'N';
713 l_estimate_lines_rec.interface_to_om_flag := 'N';
714 l_estimate_lines_rec.charge_line_type := 'ESTIMATE';
715 l_estimate_lines_rec.apply_contract_discount := 'N'; -- depot always calculates this
716 l_estimate_lines_rec.est_line_source_type_code := 'REPAIR_BOM';
717 l_estimate_lines_rec.est_line_source_id1 := null;
718 l_estimate_lines_rec.est_line_source_id2 := null;
719 l_estimate_lines_rec.ro_service_code_id := null;
720 l_contract_discount_amnt := (nvl(l_selling_price,0) * nvl(l_quantity,0) ) - l_discount_price ;
721 l_estimate_lines_rec.contract_discount_amount := nvl(l_contract_discount_amnt,0);
722
723 -- Initialize the pricing rec.
724
725 l_estimate_lines_rec.pricing_context := null;
726 l_estimate_lines_rec.pricing_attribute1 :=null;
727 l_estimate_lines_rec.pricing_attribute2 := null;
728 l_estimate_lines_rec.pricing_attribute3 := null;
729 l_estimate_lines_rec.pricing_attribute4 := null;
730 l_estimate_lines_rec.pricing_attribute5 := null;
731 l_estimate_lines_rec.pricing_attribute6 := null;
732 l_estimate_lines_rec.pricing_attribute7 := null;
733 l_estimate_lines_rec.pricing_attribute8 := null;
734 l_estimate_lines_rec.pricing_attribute9 := null;
735 l_estimate_lines_rec.pricing_attribute10 := null;
736 l_estimate_lines_rec.pricing_attribute11 := null;
737 l_estimate_lines_rec.pricing_attribute12 := null;
738 l_estimate_lines_rec.pricing_attribute13 := null;
739 l_estimate_lines_rec.pricing_attribute14 := null;
740 l_estimate_lines_rec.pricing_attribute15 := null;
741 l_estimate_lines_rec.pricing_attribute16 := null;
742 l_estimate_lines_rec.pricing_attribute17 := null;
743 l_estimate_lines_rec.pricing_attribute18 := null;
744 l_estimate_lines_rec.pricing_attribute19 := null;
745 l_estimate_lines_rec.pricing_attribute20 := null;
746 l_estimate_lines_rec.pricing_attribute21 := null;
747 l_estimate_lines_rec.pricing_attribute22 := null;
748 l_estimate_lines_rec.pricing_attribute23 := null;
749 l_estimate_lines_rec.pricing_attribute24 := null;
750 l_estimate_lines_rec.pricing_attribute25 := null;
751 l_estimate_lines_rec.pricing_attribute26 := null;
752 l_estimate_lines_rec.pricing_attribute27 := null;
753 l_estimate_lines_rec.pricing_attribute28 := null;
754 l_estimate_lines_rec.pricing_attribute29 := null;
755 l_estimate_lines_rec.pricing_attribute30 := null;
756 l_estimate_lines_rec.pricing_attribute31 := null;
757 l_estimate_lines_rec.pricing_attribute32 := null;
758 l_estimate_lines_rec.pricing_attribute33 := null;
759 l_estimate_lines_rec.pricing_attribute34 := null;
760 l_estimate_lines_rec.pricing_attribute35 := null;
761 l_estimate_lines_rec.pricing_attribute36 := null;
762 l_estimate_lines_rec.pricing_attribute37 := null;
763 l_estimate_lines_rec.pricing_attribute38 := null;
764 l_estimate_lines_rec.pricing_attribute39 := null;
765 l_estimate_lines_rec.pricing_attribute40 := null;
766 l_estimate_lines_rec.pricing_attribute41 := null;
767 l_estimate_lines_rec.pricing_attribute42 := null;
768 l_estimate_lines_rec.pricing_attribute43 := null;
769 l_estimate_lines_rec.pricing_attribute44 := null;
770 l_estimate_lines_rec.pricing_attribute45 := null;
771 l_estimate_lines_rec.pricing_attribute46 := null;
772 l_estimate_lines_rec.pricing_attribute47 := null;
773 l_estimate_lines_rec.pricing_attribute48 := null;
774 l_estimate_lines_rec.pricing_attribute49 := null;
775 l_estimate_lines_rec.pricing_attribute50 := null;
776 l_estimate_lines_rec.pricing_attribute51 := null;
777 l_estimate_lines_rec.pricing_attribute52 := null;
778 l_estimate_lines_rec.pricing_attribute53 := null;
779 l_estimate_lines_rec.pricing_attribute54 := null;
780 l_estimate_lines_rec.pricing_attribute55 := null;
781 l_estimate_lines_rec.pricing_attribute56 := null;
782 l_estimate_lines_rec.pricing_attribute57 := null;
783 l_estimate_lines_rec.pricing_attribute58 := null;
784 l_estimate_lines_rec.pricing_attribute59 := null;
785 l_estimate_lines_rec.pricing_attribute60 := null;
786 l_estimate_lines_rec.pricing_attribute61 := null;
787 l_estimate_lines_rec.pricing_attribute62 := null;
788 l_estimate_lines_rec.pricing_attribute63 := null;
789 l_estimate_lines_rec.pricing_attribute64 := null;
790 l_estimate_lines_rec.pricing_attribute65 := null;
791 l_estimate_lines_rec.pricing_attribute66 := null;
792 l_estimate_lines_rec.pricing_attribute67 := null;
793 l_estimate_lines_rec.pricing_attribute68 := null;
794 l_estimate_lines_rec.pricing_attribute69 := null;
795 l_estimate_lines_rec.pricing_attribute70 := null;
796 l_estimate_lines_rec.pricing_attribute71 := null;
797 l_estimate_lines_rec.pricing_attribute72 := null;
798 l_estimate_lines_rec.pricing_attribute73 := null;
799 l_estimate_lines_rec.pricing_attribute74 := null;
800 l_estimate_lines_rec.pricing_attribute75 := null;
801 l_estimate_lines_rec.pricing_attribute76 := null;
802 l_estimate_lines_rec.pricing_attribute77 := null;
803 l_estimate_lines_rec.pricing_attribute78 := null;
804 l_estimate_lines_rec.pricing_attribute79 := null;
805 l_estimate_lines_rec.pricing_attribute80 := null;
806 l_estimate_lines_rec.pricing_attribute81 := null;
807 l_estimate_lines_rec.pricing_attribute82 := null;
808 l_estimate_lines_rec.pricing_attribute83 := null;
809 l_estimate_lines_rec.pricing_attribute84 := null;
810 l_estimate_lines_rec.pricing_attribute85 := null;
811 l_estimate_lines_rec.pricing_attribute86 := null;
812 l_estimate_lines_rec.pricing_attribute87 := null;
813 l_estimate_lines_rec.pricing_attribute88 := null;
814 l_estimate_lines_rec.pricing_attribute89 := null;
815 l_estimate_lines_rec.pricing_attribute90 := null;
816 l_estimate_lines_rec.pricing_attribute91 := null;
817 l_estimate_lines_rec.pricing_attribute92 := null;
818 l_estimate_lines_rec.pricing_attribute93 := null;
819 l_estimate_lines_rec.pricing_attribute94 := null;
820 l_estimate_lines_rec.pricing_attribute95 := null;
821 l_estimate_lines_rec.pricing_attribute96 := null;
822 l_estimate_lines_rec.pricing_attribute97 := null;
823 l_estimate_lines_rec.pricing_attribute98 := null;
824 l_estimate_lines_rec.pricing_attribute99 := null;
825 l_estimate_lines_rec.pricing_attribute100 := null;
826
827 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
828 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'begin',
829 'calling create_repair_estimate_lines API');
830 END IF;
831 -- call estimate lines API.
832 csd_repair_estimate_pvt.create_repair_estimate_lines(
833 p_api_version => 1.0,
834 p_commit => 'T',
835 p_init_msg_list => FND_API.G_TRUE,
836 p_validation_level => 0,
837 x_estimate_line_rec => l_estimate_lines_rec,
838 x_estimate_line_id => l_estimate_line_id,
839 x_return_status => x_return_status,
840 x_msg_count => x_msg_count,
841 x_msg_data => x_msg_data);
842 --dbms_output.put_line(x_return_status||' - '||x_message_Data);
843 IF x_return_status <> 'S' THEN
844 x_return_Status := 'E';
845 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
846 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,lc_mod_name||'Error',
847 'An error occured during execution of create_repair_estimate API'||x_msg_data);
848 END IF;
849 --RAISE FND_API.G_EXC_ERROR;
850 RETURN;
851 END IF;
852
853 END LOOP;
854 x_return_status := 'S';
855 commit;
856
857 EXCEPTION
858 WHEN FND_API.G_EXC_ERROR THEN
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE,
861 p_count => x_msg_count,
862 p_data => x_msg_data);
863
864 ROLLBACK;
865 WHEN OTHERS THEN
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
867 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE,
868 p_count => x_msg_count,
869 p_data => x_msg_data);
870 ROLLBACK;
871
872 END;
873
874 FUNCTION get_default_contract(l_contract_line_id IN NUMBER,
875 l_repair_type_id IN NUMBER,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2,
878 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
879 l_contract_num number;
880 l_contract_id NUMBER := NULL;
881
882 BEGIN
883
884 IF l_contract_line_id IS NOT NULL THEN
885 BEGIN
886 select distinct h.contract_number
887 into g_contract_num
888 from --okc_k_headers_b h,
889 okc_k_headers_all_b h,
890 okc_k_lines_b l
891 where h.id = l.chr_id
892 and l.id = l_contract_line_id;
893 EXCEPTION
894 when no_data_found THEN
895 NULL;
896 END;
897 IF g_contract_num IS NOT NULL THEN
898 BEGIN
899 SELECT business_process_id
900 INTO g_business_process_id
901 FROM csd_repair_types_b
902 WHERE repair_type_id = l_repair_type_id;
903 exception
904 when no_data_found THEN
905 NULL;
906 END ;
907 IF g_business_process_id IS NOT NULL THEN
908 BEGIN
909 SELECT distinct cov.contract_id
910 INTO l_contract_id
911 FROM oks_ent_coverages_v cov
912 WHERE cov.contract_line_id = l_contract_line_id ;
913 EXCEPTION
914 WHEN NO_DATA_FOUND THEN
915 NULL;
916 END;
917 END IF;
918 END IF;
919 END IF;
920 RETURN l_contract_id;
921 END get_default_contract;
922
923 FUNCTION get_item_cost(p_item_id IN number,
924 p_uom IN varchar2,
925 p_currency_code IN varchar2,
926 p_org_id IN NUMBER,
927 x_msg_count OUT NOCOPY NUMBER,
928 x_msg_data OUT NOCOPY VARCHAR2,
929 x_return_status OUT NOCOPY VARCHAR2) return NUMBER IS
930 l_item_cost number;
931 l_return_status varchar2(2);
932 l_msg_count number;
933 l_msg_data varchar2(2000);
934 l_bom_resource_id number;
935
936 l_exec_error EXCEPTION;
937
938 BEGIN
939
940 -- enable costing manually. probably when running from apps context this is not required.
941 fnd_profile.put('CSD_ENABLE_COSTING','Y');
942 CSD_COST_ANALYSIS_PVT.Get_InvItemCost(
943 p_api_version => 1.0,
944 p_commit => csd_process_util.g_false,
945 p_init_msg_list => csd_process_util.g_true,
946 p_validation_level => csd_process_util.g_valid_level_full,
947 x_return_status => x_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 p_inventory_item_id => p_item_id,
951 p_organization_id => p_org_id,
952 p_charge_date => sysdate,
953 p_currency_code => p_currency_code,
954 p_chg_line_uom_code => p_uom,
955 x_item_cost => l_item_cost
956 );
957
958 if x_return_status <> 'S' THEN
959 l_item_cost := null;
960 RAISE FND_API.G_EXC_ERROR;
961 END IF;
962 RETURN l_item_cost;
963
964 EXCEPTION
965 WHEN FND_API.G_EXC_ERROR THEN
966 RETURN l_item_cost;
967 RAISE FND_API.G_EXC_ERROR;
968 END get_item_cost;
969
970 FUNCTION get_selling_price(p_item_id IN number,
971 p_uom IN varchar2,
972 p_quantity IN number,
973 p_pricelist_header_id IN number,
974 p_currency_code IN varchar2,
975 p_org_id IN NUMBER,
976 x_msg_count OUT NOCOPY NUMBER,
977 x_msg_data OUT NOCOPY VARCHAR2,
978 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
979 l_selling_price NUMBER;
980
981
982 l_pricing_rec csd_process_util.pricing_attr_rec := csd_process_util.ui_pricing_attr_rec;
983 l_account_id NUMBER; -- subhat, bug#10187275
984 BEGIN
985
986 --initialize pricing attributes.
987
988 l_pricing_rec.pricing_context := null;
989 l_pricing_rec.pricing_attribute1 :=null;
990 l_pricing_rec.pricing_attribute2 := null;
991 l_pricing_rec.pricing_attribute3 := null;
992 l_pricing_rec.pricing_attribute4 := null;
993 l_pricing_rec.pricing_attribute5 := null;
994 l_pricing_rec.pricing_attribute6 := null;
995 l_pricing_rec.pricing_attribute7 := null;
996 l_pricing_rec.pricing_attribute8 := null;
997 l_pricing_rec.pricing_attribute9 := null;
998 l_pricing_rec.pricing_attribute10 := null;
999 l_pricing_rec.pricing_attribute11 := null;
1000 l_pricing_rec.pricing_attribute12 := null;
1001 l_pricing_rec.pricing_attribute13 := null;
1002 l_pricing_rec.pricing_attribute14 := null;
1003 l_pricing_rec.pricing_attribute15 := null;
1004 l_pricing_rec.pricing_attribute16 := null;
1005 l_pricing_rec.pricing_attribute17 := null;
1006 l_pricing_rec.pricing_attribute18 := null;
1007 l_pricing_rec.pricing_attribute19 := null;
1008 l_pricing_rec.pricing_attribute20 := null;
1009 l_pricing_rec.pricing_attribute21 := null;
1010 l_pricing_rec.pricing_attribute22 := null;
1011 l_pricing_rec.pricing_attribute23 := null;
1012 l_pricing_rec.pricing_attribute24 := null;
1013 l_pricing_rec.pricing_attribute25 := null;
1014 l_pricing_rec.pricing_attribute26 := null;
1015 l_pricing_rec.pricing_attribute27 := null;
1016 l_pricing_rec.pricing_attribute28 := null;
1017 l_pricing_rec.pricing_attribute29 := null;
1018 l_pricing_rec.pricing_attribute30 := null;
1019 l_pricing_rec.pricing_attribute31 := null;
1020 l_pricing_rec.pricing_attribute32 := null;
1021 l_pricing_rec.pricing_attribute33 := null;
1022 l_pricing_rec.pricing_attribute34 := null;
1023 l_pricing_rec.pricing_attribute35 := null;
1024 l_pricing_rec.pricing_attribute36 := null;
1025 l_pricing_rec.pricing_attribute37 := null;
1026 l_pricing_rec.pricing_attribute38 := null;
1027 l_pricing_rec.pricing_attribute39 := null;
1028 l_pricing_rec.pricing_attribute40 := null;
1029 l_pricing_rec.pricing_attribute41 := null;
1030 l_pricing_rec.pricing_attribute42 := null;
1031 l_pricing_rec.pricing_attribute43 := null;
1032 l_pricing_rec.pricing_attribute44 := null;
1033 l_pricing_rec.pricing_attribute45 := null;
1034 l_pricing_rec.pricing_attribute46 := null;
1035 l_pricing_rec.pricing_attribute47 := null;
1036 l_pricing_rec.pricing_attribute48 := null;
1037 l_pricing_rec.pricing_attribute49 := null;
1038 l_pricing_rec.pricing_attribute50 := null;
1039 l_pricing_rec.pricing_attribute51 := null;
1040 l_pricing_rec.pricing_attribute52 := null;
1041 l_pricing_rec.pricing_attribute53 := null;
1042 l_pricing_rec.pricing_attribute54 := null;
1043 l_pricing_rec.pricing_attribute55 := null;
1044 l_pricing_rec.pricing_attribute56 := null;
1045 l_pricing_rec.pricing_attribute57 := null;
1046 l_pricing_rec.pricing_attribute58 := null;
1047 l_pricing_rec.pricing_attribute59 := null;
1048 l_pricing_rec.pricing_attribute60 := null;
1049 l_pricing_rec.pricing_attribute61 := null;
1050 l_pricing_rec.pricing_attribute62 := null;
1051 l_pricing_rec.pricing_attribute63 := null;
1052 l_pricing_rec.pricing_attribute64 := null;
1053 l_pricing_rec.pricing_attribute65 := null;
1054 l_pricing_rec.pricing_attribute66 := null;
1055 l_pricing_rec.pricing_attribute67 := null;
1056 l_pricing_rec.pricing_attribute68 := null;
1057 l_pricing_rec.pricing_attribute69 := null;
1058 l_pricing_rec.pricing_attribute70 := null;
1059 l_pricing_rec.pricing_attribute71 := null;
1060 l_pricing_rec.pricing_attribute72 := null;
1061 l_pricing_rec.pricing_attribute73 := null;
1062 l_pricing_rec.pricing_attribute74 := null;
1063 l_pricing_rec.pricing_attribute75 := null;
1064 l_pricing_rec.pricing_attribute76 := null;
1065 l_pricing_rec.pricing_attribute77 := null;
1066 l_pricing_rec.pricing_attribute78 := null;
1067 l_pricing_rec.pricing_attribute79 := null;
1068 l_pricing_rec.pricing_attribute80 := null;
1069 l_pricing_rec.pricing_attribute81 := null;
1070 l_pricing_rec.pricing_attribute82 := null;
1071 l_pricing_rec.pricing_attribute83 := null;
1072 l_pricing_rec.pricing_attribute84 := null;
1073 l_pricing_rec.pricing_attribute85 := null;
1074 l_pricing_rec.pricing_attribute86 := null;
1075 l_pricing_rec.pricing_attribute87 := null;
1076 l_pricing_rec.pricing_attribute88 := null;
1077 l_pricing_rec.pricing_attribute89 := null;
1078 l_pricing_rec.pricing_attribute90 := null;
1079 l_pricing_rec.pricing_attribute91 := null;
1080 l_pricing_rec.pricing_attribute92 := null;
1081 l_pricing_rec.pricing_attribute93 := null;
1082 l_pricing_rec.pricing_attribute94 := null;
1083 l_pricing_rec.pricing_attribute95 := null;
1084 l_pricing_rec.pricing_attribute96 := null;
1085 l_pricing_rec.pricing_attribute97 := null;
1086 l_pricing_rec.pricing_attribute98 := null;
1087 l_pricing_rec.pricing_attribute99 := null;
1088 l_pricing_rec.pricing_attribute100 := null;
1089
1090 if(p_item_id is not null and
1091 p_pricelist_header_id is not null and
1092 p_uom is not null and
1093 p_currency_code is not null and
1094 p_quantity is not null ) THEN
1095 -- API call to get the selling price.
1096 l_account_id := CSD_CHARGE_LINE_UTIL.Get_SR_AccountId(g_repair_line_id); --bug#10187275,subhat
1097 csd_process_util.get_charge_selling_price
1098 (p_inventory_item_id => p_item_id,
1099 p_price_list_header_id => p_pricelist_header_id,
1100 p_unit_of_measure_code => p_uom,
1101 p_currency_code => p_currency_code,
1102 p_quantity_required => p_quantity,
1103 p_org_id => p_org_id,
1104 p_account_id => l_account_id,
1105 p_pricing_rec => l_pricing_rec,
1106 x_selling_price => l_selling_price,
1107 x_return_status => x_return_status,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data);
1110 IF x_return_Status <> 'S' THEN
1111 l_selling_price := NULL;
1112 RAISE FND_API.G_EXC_ERROR;
1113 END IF;
1114
1115 RETURN l_selling_price;
1116 END IF;
1117
1118 EXCEPTION
1119 WHEN FND_API.G_EXC_ERROR THEN
1120 RETURN l_selling_price;
1121 RAISE FND_API.G_EXC_ERROR;
1122 when OTHERS then
1123 return l_selling_price;
1124 RAISE;
1125 END get_selling_price;
1126
1127
1128 FUNCTION get_discount_price(p_contract_line_id IN NUMBER,p_repair_type_id IN number,
1129 p_selling_price IN NUMBER,p_quantity IN NUMBER,
1130 x_msg_count OUT NOCOPY NUMBER,
1131 x_msg_data OUT NOCOPY VARCHAR2,
1132 x_return_status OUT NOCOPY VARCHAR2)RETURN NUMBER IS
1133 l_txn_billing_type_id number;
1134 l_cov_txn_grp_id NUMBER := NULL ;
1135 l_discount_price number;
1136 l_extended_price NUMBER;
1137 l_exec_error EXCEPTION;
1138 BEGIN
1139
1140 -- calculate the extended price.
1141
1142 l_extended_price := nvl(p_quantity,0) * nvl(p_selling_price,0);
1143
1144 -- API call to get the discounted price.
1145
1146 CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE
1147 (
1148 p_api_version => 1.0,
1149 p_init_msg_list => 'T',
1150 p_contract_line_id => p_contract_line_id,
1151 p_repair_type_id => p_repair_type_id,
1152 p_txn_billing_type_id => g_txn_billing_type_id,
1153 p_coverage_txn_grp_id => l_cov_txn_grp_id,
1154 p_extended_price => l_extended_price,
1155 p_no_charge_flag => 'N',
1156 x_discounted_price => l_discount_price,
1157 x_return_status => x_return_status,
1158 x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data
1160 );
1161
1162 IF x_return_status <> 'S' THEN
1163 l_discount_price := null;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END if;
1166 RETURN l_discount_price;
1167
1168 EXCEPTION
1169 WHEN FND_API.G_EXC_ERROR THEN
1170 RETURN l_discount_price;
1171 RAISE FND_API.G_EXC_ERROR;
1172 END get_discount_price;
1173
1174
1175 END csd_estimates_from_bom_pvt;