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