[Home] [Help]
PACKAGE BODY: APPS.CS_SERVICE_BILLING_ENGINE_PVT
Source
1 PACKAGE BODY CS_Service_Billing_Engine_PVT AS
2 /* $Header: csxvsbeb.pls 120.10.12010000.2 2008/10/06 05:51:40 sshilpam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CS_Service_Billing_Engine_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csxvsbeb.pls';
6 --g_debug VARCHAR2(1) := NVL(fnd_profile.value('APPS_DEBUG'), 'N');
7 --g_debug number := ASO_DEBUG_PUB.G_DEBUG_LEVEL;
8
9
10 -------------------------------------------------------------------------
11 -- Get the inventory item's primary unit of measurement.
12
13 Procedure Get_Primary_UOM(
14 p_inventory_item_id IN NUMBER,
15 x_unit_of_measure_code OUT NOCOPY varchar2)
16 IS
17
18 Cursor uom_csr(p_inventory_item_id number) IS
19 Select primary_uom_code
20 from mtl_system_items_kfv
21 where inventory_item_id = p_inventory_item_id
22 and organization_id = cs_std.get_item_valdn_orgzn_id;
23
24 BEGIN
25 OPEN uom_csr(p_inventory_item_id);
26 FETCH uom_csr INTO x_unit_of_measure_code;
27 CLOSE uom_csr;
28 END Get_Primary_UOM;
29
30
31 Procedure Consolidate_Labor_Coverages(
32 p_lbr_in_tbl IN oks_con_coverage_pub.bill_rate_tbl_type,
33 x_lbr_out_tbl OUT NOCOPY oks_con_coverage_pub.bill_rate_tbl_type)
34 IS
35
36 i number;
37 j number;
38 l_lbr_tmp_rec oks_con_coverage_pub.bill_rate_rec_type;
39
40 BEGIN
41 j := 0;
42 FOR i IN 1..p_lbr_in_tbl.count LOOP
43 IF p_lbr_in_tbl(i).labor_item_id is not null THEN
44 IF l_lbr_tmp_rec.start_datetime is not null THEN
45 j := j + 1;
46 x_lbr_out_tbl(j).start_datetime := l_lbr_tmp_rec.start_datetime;
47 x_lbr_out_tbl(j).end_datetime := l_lbr_tmp_rec.end_datetime;
48 x_lbr_out_tbl(j).labor_item_id := l_lbr_tmp_rec.labor_item_id;
49 x_lbr_out_tbl(j).labor_item_org_id := l_lbr_tmp_rec.labor_item_org_id;
50 x_lbr_out_tbl(j).bill_rate_code := l_lbr_tmp_rec.bill_rate_code;
51 x_lbr_out_tbl(j).flat_rate := l_lbr_tmp_rec.flat_rate;
52 x_lbr_out_tbl(j).flat_rate_uom_code := l_lbr_tmp_rec.flat_rate_uom_code;
53 x_lbr_out_tbl(j).percent_over_listprice := l_lbr_tmp_rec.percent_over_listprice;
54
55 l_lbr_tmp_rec.start_datetime := null;
56 l_lbr_tmp_rec.end_datetime := null;
57 l_lbr_tmp_rec.labor_item_id := null;
58 l_lbr_tmp_rec.labor_item_org_id := null;
59 l_lbr_tmp_rec.bill_rate_code := null;
60 l_lbr_tmp_rec.flat_rate := null;
61 l_lbr_tmp_rec.flat_rate_uom_code := null;
62 l_lbr_tmp_rec.percent_over_listprice := null;
63 END IF;
64
65 j := j + 1;
66 x_lbr_out_tbl(j).start_datetime := p_lbr_in_tbl(i).start_datetime;
67 x_lbr_out_tbl(j).end_datetime := p_lbr_in_tbl(i).end_datetime;
68 x_lbr_out_tbl(j).labor_item_id := p_lbr_in_tbl(i).labor_item_id;
69 x_lbr_out_tbl(j).labor_item_org_id := p_lbr_in_tbl(i).labor_item_org_id;
70 x_lbr_out_tbl(j).bill_rate_code := p_lbr_in_tbl(i).bill_rate_code;
71 x_lbr_out_tbl(j).flat_rate := p_lbr_in_tbl(i).flat_rate;
72 x_lbr_out_tbl(j).flat_rate_uom_code := p_lbr_in_tbl(i).flat_rate_uom_code;
73 x_lbr_out_tbl(j).percent_over_listprice := p_lbr_in_tbl(i).percent_over_listprice;
74 ELSE
75 IF l_lbr_tmp_rec.start_datetime is null THEN
76 l_lbr_tmp_rec.start_datetime := p_lbr_in_tbl(i).start_datetime;
77 l_lbr_tmp_rec.end_datetime := p_lbr_in_tbl(i).end_datetime;
78 l_lbr_tmp_rec.labor_item_id := p_lbr_in_tbl(i).labor_item_id;
79 l_lbr_tmp_rec.labor_item_org_id := p_lbr_in_tbl(i).labor_item_org_id;
80 l_lbr_tmp_rec.bill_rate_code := p_lbr_in_tbl(i).bill_rate_code;
81 l_lbr_tmp_rec.flat_rate := p_lbr_in_tbl(i).flat_rate;
82 l_lbr_tmp_rec.flat_rate_uom_code := p_lbr_in_tbl(i).flat_rate_uom_code;
83 l_lbr_tmp_rec.percent_over_listprice := p_lbr_in_tbl(i).percent_over_listprice;
84 ELSE
85 l_lbr_tmp_rec.end_datetime := p_lbr_in_tbl(i).end_datetime;
86 END IF;
87 END IF;
88
89 END LOOP;
90
91 IF l_lbr_tmp_rec.start_datetime is not null THEN
92 j := j + 1;
93 x_lbr_out_tbl(j).start_datetime := l_lbr_tmp_rec.start_datetime;
94 x_lbr_out_tbl(j).end_datetime := l_lbr_tmp_rec.end_datetime;
95 x_lbr_out_tbl(j).labor_item_id := l_lbr_tmp_rec.labor_item_id;
96 x_lbr_out_tbl(j).labor_item_org_id := l_lbr_tmp_rec.labor_item_org_id;
97 x_lbr_out_tbl(j).bill_rate_code := l_lbr_tmp_rec.bill_rate_code;
98 x_lbr_out_tbl(j).flat_rate := l_lbr_tmp_rec.flat_rate;
99 x_lbr_out_tbl(j).flat_rate_uom_code := l_lbr_tmp_rec.flat_rate_uom_code;
100 x_lbr_out_tbl(j).percent_over_listprice := l_lbr_tmp_rec.percent_over_listprice;
101 END IF;
102 /*
103 -- print original records
104 for i in 1..p_lbr_in_tbl.count loop
105 d--DBMS_OUTPUT.PUT_LINE('p_lbr_in_tbl('||i||').start_datetime='|| TO_CHAR(p_lbr_in_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS'));
106 d--DBMS_OUTPUT.PUT_LINE('p_lbr_in_tbl('||i||').end_datetime='|| TO_CHAR(p_lbr_in_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS'));
107 d--DBMS_OUTPUT.PUT_LINE('p_lbr_in_tbl('||i||').labor_item_id='|| p_lbr_in_tbl(i).labor_item_id);
108 end loop;
109
110 -- print out final result
111 for i in 1..x_lbr_out_tbl.count loop
112 d--DBMS_OUTPUT.PUT_LINE('x_lbr_out_tbl('||i||').start_datetime='|| TO_CHAR(x_lbr_out_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS'));
113 d--DBMS_OUTPUT.PUT_LINE('x_lbr_out_tbl('||i||').end_datetime='|| TO_CHAR(x_lbr_out_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS'));
114 d--DBMS_OUTPUT.PUT_LINE('x_lbr_out_tbl('||i||').labor_item_id='|| x_lbr_out_tbl(i).labor_item_id);
115 end loop;
116 */
117 END Consolidate_Labor_Coverages;
118
119
120 -------------------------------------------------------------------------
121 -- Procedure: create_charges
122 -- Purpose : This create_charges api can be evoked from:
123 -- - Service Debrief
124 -- Generate either in progress charges or final charges for a service debrief line.
125 -- Service Debrief should pass in p_sbe_record, p_commit set to false, and p_final_charge_flag
126 -- set to TRUE or FALSE based on the task assignment status.
127 -- When the task assignment status is permanently closed, the Service Debrief should update
128 -- inventory, IB, and call the Billing Engine with p_final_charge_flag set to TRUE so the
129 -- final charges will be generated.
130 -- If the task assignment status is not permanently closed, the Service Debrief should
131 -- just call the Billing Engine with p_final_charge_flag set to FALSE so in progress charges
132 -- will be generated.
133 -- p_commit set to FALSE is because SD can roll back all changes in case of failure occurs
134 -- so it can roll back.
135 --
136
137 PROCEDURE Create_Charges(
138 P_Api_Version_Number IN NUMBER,
139 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
140 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
141 p_sbe_record IN SBE_Rec_Type,
142 p_final_charge_flag IN VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2)
146 IS
147
148 l_api_name CONSTANT VARCHAR2(30) := 'Create_Charges';
149 l_api_version_number CONSTANT NUMBER := 1.0;
150 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
151 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
152
153 EXCP_USER_DEFINED EXCEPTION;
154
155 l_charges_rec cs_charge_details_pub.charges_rec_type;
156 x_tm_coverage_tbl cs_tm_labor_schedule_pvt.tm_coverage_tbl_type;
157 l_input_br_rec oks_con_coverage_pub.input_br_rec;
158 l_labor_sch_tbl oks_con_coverage_pub.labor_sch_tbl_type;
159 l_con_lbr_coverage_tbl oks_con_coverage_pub.bill_rate_tbl_type;
160 x_con_lbr_coverage_tbl oks_con_coverage_pub.bill_rate_tbl_type;
161
162 i number;
163 j number;
164 l_duration number;
165 l_base_uom varchar2(3);
166 l_primary_uom Varchar2(3);
167 l_incident_date date;
168 l_creation_date date;
169 l_customer_id number;
170 l_account_id number;
171 l_customer_product_id number;
172 l_contract_id number;
173 l_contract_service_id number;
174 --l_coverage_id number; --commented for R12 by mviswana
175 --l_coverage_txn_group_id number; --commented for R12 by mviswana
176 l_po_number varchar2(50);
177 l_activity_start_date_time date;
178 l_activity_end_date_time date;
179 l_inventory_item_id number;
180 x_object_version_number number;
181 x_estimate_detail_id number;
182 x_line_number number;
183 l_holiday_flag varchar2(1);
184 l_add_one_minute varchar2(1);
185 -- fix for bug#4120101
186 --
187 l_resource_id number;
188 l_task_id number;
189 l_labor_start_date_time date;
190 l_labor_end_date_time date;
191 lx_timezone_id number;
192 lx_timezone_name varchar2(50);
193 lx_labor_start_date_time date;
194 lx_labor_end_date_time date;
195 --
196 --
197 -- Added for bug:5136865
198 l_rate_code VARCHAR2(40);
199 l_rate_amount NUMBER;
200 l_rate_percent NUMBER;
201 l_rate_uom VARCHAR2(30);
202 k NUMBER := 0;
203
204
205
206
207
208 CURSOR incident_csr(p_incident_id number) IS
209 SELECT incident_date, creation_date, customer_id, account_id, customer_product_id, contract_id, contract_service_id
210 FROM cs_incidents_all_b
211 WHERE incident_id = p_incident_id;
212
213 /* CURSOR base_uom_csr IS
214 SELECT uom_code
215 FROM mtl_uom_conversions
216 WHERE uom_class = 'Time' and conversion_rate = 1; */
217
218 -- Bug 7229344
219 Cursor billing_category IS
220 Select cbtc.billing_category
221 From mtl_system_items_kfv kfv, cs_billing_type_categories cbtc
222 Where kfv.inventory_item_id = p_sbe_record.inventory_item_id
223 and kfv.material_billable_flag = cbtc.billing_type
224 and organization_id = cs_std.get_item_valdn_orgzn_id;
225
226 Cursor txn_billing_type_csr(p_transaction_type_id number,p_billing_category varchar2) IS
227 Select ctbt.txn_billing_type_id
228 from cs_txn_billing_types ctbt, cs_billing_type_categories cbtc
229 where ctbt.transaction_type_id = p_transaction_type_id
230 and ctbt.billing_type = cbtc.billing_type
231 and cbtc.billing_category = p_billing_category;
232 /*
233 Cursor txn_billing_type_csr(p_transaction_type_id number) IS
234 /*Select txn_billing_type_id
235 from cs_txn_billing_types
236 -where transaction_type_id = p_transaction_type_id
237 and billing_type = 'L';
238 Select ctbt.txn_billing_type_id
239 from cs_txn_billing_types ctbt, cs_billing_type_categories cbtc
240 where ctbt.transaction_type_id = p_transaction_type_id
241 and ctbt.billing_type = cbtc.billing_type
242 and cbtc.billing_category = 'L';
243 */
244 --End Bug 7229344
245 -- Added for ER 4120077, vkjain.
246 -- Depot task debrief lines will use RO contract (NOT SR contract).
247 CURSOR ro_contract_csr(p_repair_line_id number) IS
248 SELECT ro.contract_line_id
249 FROM csd_repairs ro
250 WHERE ro.repair_line_id = p_repair_line_id;
251
252 -- Added fix for bug#4120101
253 CURSOR get_task_resource(p_debrief_line_id number) IS
254 SELECT j.resource_id,j.task_id
255 FROM csf_debrief_headers h,
256 csf_debrief_lines l,
257 jtf_task_assignments_v j,
258 jtf_tasks_b jt
259 WHERE l.debrief_line_id = p_debrief_line_id
260 AND h.debrief_header_id = l.debrief_header_id
261 AND h.task_assignment_id = j.task_assignment_id
262 AND j.task_id = jt.task_id
263 AND jt.source_object_type_code = 'SR';
264
265
266
267 -- Added fix for bug:5136865
268 Cursor get_rate_type(p_contract_line_id NUMBER,
269 p_txn_billing_type_id NUMBER,
270 p_business_process_id NUMBER) IS
271 SELECT br.rate_code,
272 br.rate_uom,
273 br.rate_amount,
274 br.rate_percent
275 FROM oks_ent_txn_groups_v txn,
276 oks_ent_bill_rates_v br,
277 oks_ent_bill_types_v bt
278 WHERE bt.txn_group_id = txn.txn_group_id
279 AND br.billing_type_id = bt.contract_billing_type_id
280 AND bt.billing_type_id = p_txn_billing_type_id
281 AND txn.business_process_id = p_business_process_id
282 AND txn.contract_line_id = p_contract_line_id;
283
284 -- bugfix#5443461
285 Cursor get_billing_category(p_txn_billing_type_id NUMBER) IS
286 SELECT bc.billing_category
287 FROM cs_billing_type_categories bc,
288 cs_txn_billing_types bt
289 WHERE bt.billing_type = bc.billing_type
290 AND bt.txn_billing_type_id = p_txn_billing_type_id;
291
292 l_billing_category VARCHAR2(30);
293 l_bill_category VARCHAR2(30);
294
295
296 --Added for 12.1 Service Costing
297 Cursor get_sac_cost_flag(p_transaction_type_id NUMBER) IS
298 select nvl(create_cost_flag ,'N'),
299 nvl(create_charge_flag,'Y')
300 from cs_transaction_types_b
301 where transaction_type_id = p_transaction_type_id;
302
303
304 l_create_cost_detail VARCHAR2(1);
305 x_cost_id NUMBER;
306 l_cost_flag VARCHAR2(1);
307 l_charge_flag VARCHAR2(1);
308
309
310 BEGIN
311 SAVEPOINT Create_Charges;
312
313 -- Standard call to check for call compatibility.
314 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
315 p_api_version_number,
316 l_api_name,
317 G_PKG_NAME)
318 THEN
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 END IF;
321
322 -- Initialize message list if p_init_msg_list is set to TRUE.
323 IF FND_API.to_Boolean(p_init_msg_list)
324 THEN
325 FND_MSG_PUB.initialize;
326 END IF;
327
328 -- Initialize API return status to SUCCESS
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330
331 ----------------------- FND Logging -----------------------------------
332 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
333 THEN
334 FND_LOG.String
335 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
336 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
337 );
338 FND_LOG.String
339 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
340 , 'P_Api_Version_Number:' || P_Api_Version_Number
341 );
342 FND_LOG.String
343 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
344 , 'p_init_msg_list:' || p_init_msg_list
345 );
346 FND_LOG.String
347 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
348 , 'p_commit:' || p_commit
349 );
350 FND_LOG.String
351 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
352 ,'incident_id :' || p_sbe_record.incident_id
353 );
354 FND_LOG.String
355 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
356 ,'business_process_id :' || p_sbe_record.business_process_id
357 );
358 FND_LOG.String
359 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
360 ,'transaction_type_id :' || p_sbe_record.transaction_type_id
361 );
362 FND_LOG.String
363 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
364 ,'txn_billing_type_id :' || p_sbe_record.txn_billing_type_id
365 );
366 FND_LOG.String
367 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
368 ,'line_category_code :' || p_sbe_record.line_category_code
369 );
370 FND_LOG.String
371 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
372 ,'contract_id :' || p_sbe_record.contract_id
373 );
374 FND_LOG.String
375 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
376 ,'contract_line_id :' || p_sbe_record.contract_line_id
377 );
378 FND_LOG.String
379 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
380 ,'price_list_id :' || p_sbe_record.price_list_id
381 );
382 FND_LOG.String
383 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
384 ,'currency_code :' || p_sbe_record.currency_code
385 );
386 FND_LOG.String
387 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
388 ,'service_date :' || p_sbe_record.service_date
389 );
390 FND_LOG.String
391 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
392 ,'labor_start_date_time :' || p_sbe_record.labor_start_date_time
393 );
394 FND_LOG.String
395 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
396 ,'labor_end_date_time :' || p_sbe_record.labor_end_date_time
397 );
398 FND_LOG.String
399 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
400 ,'inventory_item_id :' || p_sbe_record.inventory_item_id
401 );
402 FND_LOG.String
403 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
404 ,'serial_number :' || p_sbe_record.serial_number
405 );
406 FND_LOG.String
407 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
408 ,'item_revision :' || p_sbe_record.item_revision
409 );
410 FND_LOG.String
411 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
412 ,'unit_of_measure_code :' || p_sbe_record.unit_of_measure_code
413 );
414 FND_LOG.String
415 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
416 ,'quantity :' || p_sbe_record.quantity
417 );
418 FND_LOG.String
419 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
420 ,'after_warranty_cost :' || p_sbe_record.after_warranty_cost
421 );
422 FND_LOG.String
423 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
424 ,'return_reason_code :' || p_sbe_record.return_reason_code
425 );
426 FND_LOG.String
427 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
428 ,'installed_cp_return_by_date :' || p_sbe_record.installed_cp_return_by_date
429 );
430 FND_LOG.String
431 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
432 ,'customer_product_id :' || p_sbe_record.customer_product_id
433 );
434 FND_LOG.String
435 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
436 ,'transaction_inventory_org_id :' || p_sbe_record.transaction_inventory_org_id
437 );
438 FND_LOG.String
439 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
440 ,'transaction_sub_inventory :' || p_sbe_record.transaction_sub_inventory
441 );
442 FND_LOG.String
443 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
444 ,'original_source_id :' || p_sbe_record.original_source_id
445 );
446 FND_LOG.String
447 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
448 ,'original_source_code :' || p_sbe_record.original_source_code
449 );
450 FND_LOG.String
451 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
452 ,'source_id :' || p_sbe_record.source_id
453 );
454 FND_LOG.String
455 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
456 ,'source_code :' || p_sbe_record.source_code
457 );
458 FND_LOG.String
459 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
460 , 'p_final_charge_flag:' || p_final_charge_flag
461 );
462 END IF;
463
464 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
465 THEN
466 FND_LOG.String
467 ( FND_LOG.level_statement
468 , L_LOG_MODULE, '----- Private API: ' || g_pkg_name || '.' || l_api_name || ' starts at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SSSSS')
469 );
470 FND_LOG.String
471 ( FND_LOG.level_statement
472 , L_LOG_MODULE, 'p_sbe_record.incident_id = ' || p_sbe_record.incident_id
473 );
474 FND_LOG.String
475 ( FND_LOG.level_statement
476 , L_LOG_MODULE, 'p_sbe_record.business_process_id = ' || p_sbe_record.business_process_id
477 );
478 FND_LOG.String
479 ( FND_LOG.level_statement
480 , L_LOG_MODULE, 'p_sbe_record.transaction_type_id = ' || p_sbe_record.transaction_type_id
481 );
482 FND_LOG.String
483 ( FND_LOG.level_statement
484 , L_LOG_MODULE, 'p_sbe_record.txn_billing_type_id = ' || p_sbe_record.txn_billing_type_id
485 );
486 FND_LOG.String
487 ( FND_LOG.level_statement
488 , L_LOG_MODULE, 'p_sbe_record.line_category_code = ' || p_sbe_record.line_category_code
489 );
490 FND_LOG.String
491 ( FND_LOG.level_statement
492 , L_LOG_MODULE, 'p_sbe_record.contract_id = ' || p_sbe_record.contract_id
493 );
494 FND_LOG.String
495 ( FND_LOG.level_statement
496 , L_LOG_MODULE, 'p_sbe_record.price_list_id = ' || p_sbe_record.price_list_id
497 );
498 FND_LOG.String
499 ( FND_LOG.level_statement
500 , L_LOG_MODULE, 'p_sbe_record.currency_code = ' || p_sbe_record.currency_code
501 );
502 FND_LOG.String
503 ( FND_LOG.level_statement
504 , L_LOG_MODULE, 'p_sbe_record.service_date = ' || p_sbe_record.service_date
505 );
506 FND_LOG.String
507 ( FND_LOG.level_statement
508 , L_LOG_MODULE, 'p_sbe_record.labor_start_date_time = ' || TO_CHAR(p_sbe_record.labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS')
509 );
510 FND_LOG.String
511 ( FND_LOG.level_statement
512 , L_LOG_MODULE, 'p_sbe_record.labor_end_date_time = ' || TO_CHAR(p_sbe_record.labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS')
513 );
514 FND_LOG.String
515 ( FND_LOG.level_statement
516 , L_LOG_MODULE, 'p_sbe_record.inventory_item_id = ' || p_sbe_record.inventory_item_id
517 );
518 FND_LOG.String
519 ( FND_LOG.level_statement
520 , L_LOG_MODULE, 'p_sbe_record.quantity = ' || p_sbe_record.quantity
521 );
522 FND_LOG.String
523 ( FND_LOG.level_statement
524 , L_LOG_MODULE, 'p_sbe_record.original_source_code = ' || p_sbe_record.original_source_code || ' original_source_id = ' || p_sbe_record.original_source_id
525 );
526 FND_LOG.String
527 ( FND_LOG.level_statement
528 , L_LOG_MODULE, 'p_sbe_record.source_code = ' || p_sbe_record.source_code || ' source_id = ' || p_sbe_record.source_id
529 );
530 FND_LOG.String
531 ( FND_LOG.level_statement
532 , L_LOG_MODULE, 'p_final_charge_flag = ' || p_final_charge_flag
533 );
534 END IF;
535
536 --DBMS_OUTPUT.PUT_LINE('----- Private API: ' || g_pkg_name || '.' || l_api_name || ' starts at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SSSSS'));
537 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.incident_id = ' || p_sbe_record.incident_id);
538 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.business_process_id = ' || p_sbe_record.business_process_id);
539 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.transaction_type_id = ' || p_sbe_record.transaction_type_id);
540 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.txn_billing_type_id = ' || p_sbe_record.txn_billing_type_id);
541 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.line_category_code = ' || p_sbe_record.line_category_code);
542 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.contract_id = ' || p_sbe_record.contract_id);
543 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.contract_line_id = ' || p_sbe_record.contract_line_id);
544 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.price_list_id = ' || p_sbe_record.price_list_id);
545 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.currency_code = ' || p_sbe_record.currency_code);
546 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.service_date = ' || p_sbe_record.service_date);
547 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.labor_start_date_time = ' || TO_CHAR(p_sbe_record.labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS'));
548 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.labor_end_date_time = ' || TO_CHAR(p_sbe_record.labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS'));
549 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.inventory_item_id = ' || p_sbe_record.inventory_item_id);
550 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.quantity = ' || p_sbe_record.quantity);
551 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.original_source_code = ' || p_sbe_record.original_source_code || ' original_source_id = ' || p_sbe_record.original_source_id);
552 --DBMS_OUTPUT.PUT_LINE('p_sbe_record.source_code = ' || p_sbe_record.source_code || ' source_id = ' || p_sbe_record.source_id);
553 --DBMS_OUTPUT.PUT_LINE('p_final_charge_flag = ' || p_final_charge_flag);
554
555 /* Take out this feature per enhancement 2878467
556 -- Exclude DR debriefs to prevent double billing.
557 IF p_sbe_record.original_source_code = 'DR' THEN
558 return;
559 END IF;
560 */
561
562 -- Retrieve service request incident date, customer id, account id, customer product id, and contract.
563 OPEN incident_csr(p_sbe_record.incident_id);
564 FETCH incident_csr into l_incident_date, l_creation_date, l_customer_id, l_account_id, l_customer_product_id, l_contract_id, l_contract_service_id;
565 IF incident_csr%NOTFOUND THEN
566 CLOSE incident_csr;
567 FND_MESSAGE.SET_NAME('CS', 'CS_INCIDENT_NOT_FOUND');
568 FND_MESSAGE.SET_TOKEN('INCIDENT_ID', p_sbe_record.incident_id);
569 FND_MSG_PUB.ADD;
570 RAISE EXCP_USER_DEFINED;
571 END IF;
572 CLOSE incident_csr;
573
574 -- Added for ER 4120077, vkjain.
575 -- Depot task debrief lines will use RO contract (NOT SR contract).
576 IF( p_sbe_record.original_source_code = 'DR') THEN
577 -- For Depot originated debrief lines we will only use
578 -- RO contract, even if SR contract exists.
579 -- If there is no RO contract then no contract is applied, even
580 -- if SR contract exists.
581 l_contract_service_id := NULL;
582
583 -- Get the RO contract, if one exists.
584 OPEN ro_contract_csr(p_sbe_record.original_source_id);
585 FETCH ro_contract_csr into l_contract_service_id;
586 IF ro_contract_csr%ISOPEN THEN
587 CLOSE ro_contract_csr;
588 END IF;
589 END IF;
590
591 l_contract_id := null;
592 --l_coverage_id := null; --commented for R12 by mviswana
593 --l_coverage_txn_group_id := null; --commented for R12 by mviswana
594
595 -- Get contract coverage.
596 IF l_contract_service_id IS NOT NULL THEN
597
598 cs_charge_details_pvt.get_contract(
599 p_api_name => l_api_name,
600 p_contract_SR_ID => l_contract_service_id,
601 p_incident_date => l_incident_date,
602 p_creation_date => l_creation_date,
603 p_customer_id => l_customer_id,
604 p_cust_account_id => l_account_id,
605 p_cust_product_id => l_customer_product_id,
606 p_business_process_id => p_sbe_record.business_process_id,
607 x_contract_id => l_contract_id,
608 --x_coverage_id => l_coverage_id, --commented for R12 by mviswana
609 --x_coverage_txn_group_id => l_coverage_txn_group_id, --commented for R12 by mviswana
610 x_po_number => l_po_number,
611 x_return_status => x_return_status,
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data);
614
615 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
616 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_GET_CONTRACT_API_ERROR');
617 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
618 FND_MSG_PUB.ADD;
619 RAISE EXCP_USER_DEFINED;
620 END IF;
621 --DBMS_OUTPUT.PUT_LINE('x_contract_id => l_contract_id = ' || l_contract_id);
622 ----DBMS_OUTPUT.PUT_LINE('x_coverage_id => l_coverage_id = ' || l_coverage_id);
623 ----DBMS_OUTPUT.PUT_LINE('x_coverage_txn_group_id => l_coverage_txn_group_id = ' || l_coverage_txn_group_id);
624
625 END IF;
626 l_charges_rec.contract_line_id := l_contract_service_id;
627 l_charges_rec.rate_type_code := NULL; --MAYA Need To determine the value of this
628 l_charges_rec.contract_id := l_contract_id;
629 l_charges_rec.coverage_id := null;
630 l_charges_rec.coverage_txn_group_id := null;
631 l_charges_rec.purchase_order_num := l_po_number;
632 l_charges_rec.price_list_id := p_sbe_record.price_list_id;
633 l_charges_rec.original_source_id := p_sbe_record.original_source_id;
634 l_charges_rec.original_source_code := p_sbe_record.original_source_code;
635 l_charges_rec.source_id := p_sbe_record.source_id;
636 l_charges_rec.source_code := p_sbe_record.source_code;
637 l_charges_rec.transaction_type_id := p_sbe_record.transaction_type_id;
638 l_charges_rec.txn_billing_type_id := p_sbe_record.txn_billing_type_id;
639 l_charges_rec.incident_id := p_sbe_record.incident_id;
640 l_charges_rec.business_process_id := p_sbe_record.business_process_id;
641 l_charges_rec.currency_code := p_sbe_record.currency_code;
642 l_charges_rec.item_revision := p_sbe_record.item_revision;
643 l_charges_rec.customer_product_id := p_sbe_record.customer_product_id;
644 l_charges_rec.installed_cp_return_by_date := p_sbe_record.installed_cp_return_by_date;
645 l_charges_rec.apply_contract_discount := 'Y'; -- Always automatically apply contract discount.
646 l_charges_rec.interface_to_oe_flag := FND_API.G_MISS_CHAR;
647 l_charges_rec.rollup_flag := 'N';
648 l_charges_rec.add_to_order_flag := 'N';
649 l_charges_rec.no_charge_flag := FND_API.G_MISS_CHAR;
650 l_charges_rec.generated_by_bca_engine := 'Y';
651 l_charges_rec.line_category_code := p_sbe_record.line_category_code;
652 l_charges_rec.return_reason_code := p_sbe_record.return_reason_code;
653 l_charges_rec.serial_number := p_sbe_record.serial_number;
654 l_charges_rec.transaction_inventory_org := p_sbe_record.transaction_inventory_org_id;
655 l_charges_rec.transaction_sub_inventory := p_sbe_record.transaction_sub_inventory;
656
657
658 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
659 THEN
660 FND_LOG.String
661 ( FND_LOG.level_statement
662 , L_LOG_MODULE, 'l_charges_rec.contract_id = ' || l_charges_rec.contract_id
663 );
664 FND_LOG.String
665 ( FND_LOG.level_statement
666 , L_LOG_MODULE, 'l_charges_rec.coverage_id = ' || l_charges_rec.coverage_id
667 );
668 FND_LOG.String
669 ( FND_LOG.level_statement
670 , L_LOG_MODULE, 'l_charges_rec.coverage_txn_group_id = ' || l_charges_rec.coverage_txn_group_id
671 );
672 END IF;
673 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.contract_id = ' || l_charges_rec.contract_id);
674 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.coverage_id = ' || l_charges_rec.coverage_id);
675 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.coverage_txn_group_id = ' || l_charges_rec.coverage_txn_group_id);
676
677 -- If p_final_charge_flag = 'Y', create actual charge line,
678 -- otherwise, create in progress charge line.
679 IF p_final_charge_flag = 'Y' THEN
680 l_charges_rec.charge_line_type := 'ACTUAL';
681 ELSE
682 l_charges_rec.charge_line_type := 'IN_PROGRESS';
683 END IF;
684
685 --Added For 12.1 Service Costing
686 OPEN get_sac_cost_flag(p_sbe_record.transaction_type_id);
687 FETCH get_sac_cost_flag
688 INTO l_cost_flag,l_charge_flag;
689 CLOSE get_sac_cost_flag;
690
691
692
693 -- If inventory_item_id is null (labor for sure), get the labor coverages and billing rates
694 -- based on the Contracts Labor Coverages and Time Material Labor Schedules.
695 IF p_sbe_record.inventory_item_id IS NULL THEN
696
697 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
698 THEN
699 FND_LOG.String
700 ( FND_LOG.level_statement
701 , L_LOG_MODULE, '--- p_sbe_record.inventory_item_id is null.'
702 );
703 END IF;
704
705 --DBMS_OUTPUT.PUT_LINE('--- p_sbe_record.inventory_item_id is null.');
706
707 -- Validate if the start datetime and end datetime are passed in and are not the same.
708 IF p_sbe_record.labor_start_date_time IS NULL OR
709 p_sbe_record.labor_end_date_time IS NULL OR
710 TO_CHAR(p_sbe_record.labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS') = TO_CHAR(p_sbe_record.labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS') THEN
711 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_LBR_DATETIME_REQD');
712 FND_MESSAGE.SET_TOKEN('LABOR_START_DATETIME', TO_CHAR(p_sbe_record.labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS'));
713 FND_MESSAGE.SET_TOKEN('LABOR_END_DATETIME', TO_CHAR(p_sbe_record.labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS'));
714 FND_MSG_PUB.ADD;
715 RAISE EXCP_USER_DEFINED;
716 END IF;
717
718 -- Convert labor start datetime and end datetime using the new timezone routine
719 -- fix for bug#4120101
720 -- IF fnd_profile.value ('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' THEN
721
722 OPEN get_task_resource(l_charges_rec.source_id);
723 FETCH get_task_resource
724 INTO l_resource_id,
725 l_task_id;
726 CLOSE get_task_resource;
727
728 --DBMS_OUTPUT.PUT_LINE('Resource_Id' || l_resource_id);
729 --DBMS_OUTPUT.PUT_LINE('Task_Id' || l_task_id);
730
731
732 CS_TZ_GET_DETAILS_PVT.Customer_Preferred_Time_Zone(
733 P_INCIDENT_ID => l_charges_rec.incident_id,
734 P_TASK_ID => l_task_id,
735 P_RESOURCE_ID => l_resource_id,
736 P_INCIDENT_LOCATION_ID => NULL,
737 P_incident_location_type => NULL,
738 P_CONTACT_PARTY_ID => NULL,
739 P_CUSTOMER_ID => NULL,
740 X_TIMEZONE_ID => lx_timezone_id,
741 X_TIMEZONE_NAME => lx_timezone_name);
742
743 --DBMS_OUTPUT.PUT_LINE('Timezone_Id' || lx_timezone_id);
744 --DBMS_OUTPUT.PUT_LINE('Timezone_name' || lx_timezone_name);
745
746 IF p_sbe_record.labor_start_date_time IS NOT NULL AND
747 lx_timezone_id IS NOT NULL THEN
748
749 HZ_TIMEZONE_PUB.Get_time(
750 p_api_version => 1.0,
751 p_init_msg_list => 'T',
752 p_source_tz_id => fnd_profile.value('SERVER_TIMEZONE_ID'),
753 p_dest_tz_id => lx_timezone_id,
754 p_source_day_time => p_sbe_record.labor_start_date_time,
755 x_dest_day_time => lx_labor_start_date_time,
756 x_return_status => x_return_status,
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data);
759 --DBMS_OUTPUT.PUT_LINE('Labor_start_date' || lx_labor_start_date_time);
760
761
762 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
763 FND_MESSAGE.SET_NAME('CS', 'HZ_TIMEZONE_PUB_API_ERR');
764 FND_MSG_PUB.ADD;
765 RAISE EXCP_USER_DEFINED;
766 END IF;
767 END IF;
768
769 IF p_sbe_record.labor_end_date_time IS NOT NULL AND
770 lx_timezone_id IS NOT NULL THEN
771
772 HZ_TIMEZONE_PUB.Get_time(
773 p_api_version => 1.0,
774 p_init_msg_list => 'T',
775 p_source_tz_id => fnd_profile.value('SERVER_TIMEZONE_ID'),
776 p_dest_tz_id => lx_timezone_id,
777 p_source_day_time => p_sbe_record.labor_end_date_time,
778 x_dest_day_time => lx_labor_end_date_time,
779 x_return_status => x_return_status,
780 x_msg_count => x_msg_count,
781 x_msg_data => x_msg_data);
782
783
784 --DBMS_OUTPUT.PUT_LINE('Labor_end_date' || lx_labor_end_date_time);
785
786 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
787 FND_MESSAGE.SET_NAME('CS', 'HZ_TIMEZONE_PUB_API_ERR');
788 FND_MSG_PUB.ADD;
789 RAISE EXCP_USER_DEFINED;
790 END IF;
791
792
793 END IF;
794
795 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
796 THEN
797 FND_LOG.String
798 ( FND_LOG.level_statement
799 , L_LOG_MODULE, 'l_resource_id' || l_resource_id
800 );
801 FND_LOG.String
802 ( FND_LOG.level_statement
803 , L_LOG_MODULE, 'l_task_id' || l_task_id
804 );
805 FND_LOG.String
806 ( FND_LOG.level_statement
807 , L_LOG_MODULE, 'lx_timezone_id' || lx_timezone_id
808 );
809 FND_LOG.String
810 ( FND_LOG.level_statement
811 , L_LOG_MODULE, 'lx_timezone_name' || lx_timezone_name
812 );
813 FND_LOG.String
814 ( FND_LOG.level_statement
815 , L_LOG_MODULE, 'lx_labor_start_date_time' || lx_labor_start_date_time
816 );
817 FND_LOG.String
818 ( FND_LOG.level_statement
819 , L_LOG_MODULE, 'lx_labor_end_date_time' || lx_labor_end_date_time
820 );
821 END IF;
822 -- END IF; -- timezone profile
823 -- End of fix for bug#4120101
824
825 -- Retrieve the time base unit of measurement code (HR). It will be used to ververt labor duration.
826 /* OPEN base_uom_csr;
827 FETCH base_uom_csr into l_base_uom;
828 IF base_uom_csr%NOTFOUND THEN
829 CLOSE base_uom_csr;
830 END IF;
831 CLOSE base_uom_csr; */
832 l_base_uom := fnd_profile.value('CSF_UOM_HOURS');
833
834 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
835 THEN
836 FND_LOG.String
837 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
838 , 'The Value of profile CSF_UOM_HOURS :' || l_base_uom
839 );
840 END IF;
841 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
842 THEN
843 FND_LOG.String
844 ( FND_LOG.level_statement
845 , L_LOG_MODULE, 'l_base_uom = ' || l_base_uom
846 );
847 END IF;
848 --DBMS_OUTPUT.PUT_LINE('l_base_uom = ' || l_base_uom);
849
850 -- Get transaction billing type for service debrief labor activity that without labor inventory item specified.
851 IF l_charges_rec.transaction_type_id IS NOT NULL THEN
852 OPEN txn_billing_type_csr(l_charges_rec.transaction_type_id,'L');
853 FETCH txn_billing_type_csr into l_charges_rec.txn_billing_type_id;
854 IF txn_billing_type_csr%NOTFOUND THEN
855 CLOSE txn_billing_type_csr;
856 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TXN_BILLING_TYPE_ERROR');
857 FND_MESSAGE.SET_TOKEN('TRANSACTION_TYPE_ID', l_charges_rec.transaction_type_id);
858 FND_MSG_PUB.ADD;
859 RAISE EXCP_USER_DEFINED;
860 END IF;
861 CLOSE txn_billing_type_csr;
862 ELSE
863 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TXN_BILLING_TYPE_ERROR');
864 FND_MESSAGE.SET_TOKEN('TRANSACTION_TYPE_ID', l_charges_rec.transaction_type_id);
865 FND_MSG_PUB.ADD;
866 RAISE EXCP_USER_DEFINED;
867 END IF;
868
869 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
870 THEN
871 FND_LOG.String
872 ( FND_LOG.level_statement
873 , L_LOG_MODULE, 'l_charges_rec.txn_billing_type_id = ' || l_charges_rec.txn_billing_type_id
874 );
875 END IF;
876
877 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.txn_billing_type_id = ' || l_charges_rec.txn_billing_type_id);
878
879 -- Assign values to l_input_br_rec.
880 --l_input_br_rec.contract_line_id := l_charges_rec.coverage_id; --commented for R12 by mviswana
881 l_input_br_rec.contract_line_id := l_contract_service_id;
882 l_input_br_rec.business_process_id := l_charges_rec.business_process_id;
883 l_input_br_rec.txn_billing_type_id := l_charges_rec.txn_billing_type_id;
884 l_input_br_rec.request_date := l_incident_date;
885
886 --DBMS_OUTPUT.PUT_LINE('l_input_br_rec.contract_line_id'||l_input_br_rec.contract_line_id);
887 --DBMS_OUTPUT.PUT_LINE('l_input_br_rec.business_process_id'||l_input_br_rec.business_process_id);
888 --DBMS_OUTPUT.PUT_LINE('l_input_br_rec.txn_billing_type_id'||l_input_br_rec.txn_billing_type_id);
889 --DBMS_OUTPUT.PUT_LINE('l_input_br_rec.request_date'||l_input_br_rec.request_date);
890
891 -- Break up the labor activity into different days if it is across days.
892
893 -- fix for bug#4120101
894 l_activity_start_date_time := lx_labor_start_date_time;
895 l_activity_end_date_time := lx_labor_end_date_time;
896 l_labor_start_date_time := lx_labor_start_date_time;
897 l_labor_end_date_time := lx_labor_end_date_time;
898
899 -- Break up the labor activity into different days if it is across days.
900 -- l_activity_start_date_time := p_sbe_record.labor_start_date_time;
901 -- l_activity_end_date_time := p_sbe_record.labor_end_date_time;
902
903 --Fixed Issue for Labor Items in a Timezone environment
904 --Bug # 4120101
905
906 WHILE (trunc(l_activity_start_date_time) <= trunc(l_labor_end_date_time)) AND
907 TO_CHAR(l_activity_start_date_time, 'DD-MON-YYYY HH24:MI:SS') <> TO_CHAR(l_labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS') LOOP
908
909 IF (trunc(l_labor_end_date_time) > trunc(l_activity_start_date_time)) THEN
910 l_activity_end_date_time := trunc(l_activity_start_date_time) + 1 - 1/(24*60);
911 l_add_one_minute := 'Y';
912 ELSE
913 l_activity_end_date_time := l_labor_end_date_time;
914 l_add_one_minute := 'N';
915 END IF;
916
917 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
918 THEN
919 FND_LOG.String
920 ( FND_LOG.level_statement
921 , L_LOG_MODULE, '-- Process break up date => ' || TO_CHAR(l_activity_start_date_time, 'DD-MON-YYYY HH24:MI:SS') || ' ~ ' || TO_CHAR(l_activity_end_date_time, 'DD-MON-YYYY HH24:MI:SS')
922 );
923 END IF;
924 --DBMS_OUTPUT.PUT_LINE('-- Process break up date => ' || TO_CHAR(l_activity_start_date_time, 'DD-MON-YYYY HH24:MI:SS') || ' ~ ' || TO_CHAR(l_activity_end_date_time, 'DD-MON-YYYY HH24:MI:SS'));
925 --DBMS_OUTPUT.PUT_LINE('l_contract_id is '||l_contract_id);
926
927 -- Fixed the issue to resolve Bug # 5024792
928
929 IF l_contract_service_id IS NOT NULL THEN -- If contract exists.
930
931 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
932 THEN
933 FND_LOG.String
934 ( FND_LOG.level_statement
935 , L_LOG_MODULE, '-- Contract exists.'
936 );
937 END IF;
938 --DBMS_OUTPUT.PUT_LINE('-- Contract exists.');
939
940 -- Determine if the labor activity date is a holiday or not.
941 -- Calender team provided API will be used in the near future.
942 l_holiday_flag := 'N';
943
944 -- Assign values to l_labor_sch_rec_type.
945 l_labor_sch_tbl(1).start_datetime := l_activity_start_date_time;
946 l_labor_sch_tbl(1).end_datetime := l_activity_end_date_time;
947 l_labor_sch_tbl(1).holiday_flag := l_holiday_flag;
948
949 --DBMS_OUTPUT.PUT_LINE('l_labor_sch_tbl(1).start_datetime'||l_labor_sch_tbl(1).start_datetime);
950 --DBMS_OUTPUT.PUT_LINE('l_labor_sch_tbl(1).end_datetime'||l_labor_sch_tbl(1).end_datetime);
951 --DBMS_OUTPUT.PUT_LINE('l_labor_sch_tbl(1).holiday_flag'||l_labor_sch_tbl(1).holiday_flag);
952
953 -- Get Contract labor coverage billing data.
954 oks_con_coverage_pub.get_bill_rates(
955 p_api_version => p_api_version_number,
956 p_init_msg_list => FND_API.G_FALSE,
957 p_input_br_rec => l_input_br_rec,
958 p_labor_sch_tbl => l_labor_sch_tbl,
959 x_return_status => x_return_status,
960 x_msg_count => x_msg_count,
961 x_msg_data => x_msg_data,
962 x_bill_rate_tbl => l_con_lbr_coverage_tbl);
963
964 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
965 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CON_LBR_COVERAGE_API_ER');
966 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
967 FND_MSG_PUB.ADD;
968 RAISE EXCP_USER_DEFINED;
969 END IF;
970
971 -- Consolidate contracts labor coverage time segments
972 Consolidate_Labor_Coverages(
973 p_lbr_in_tbl => l_con_lbr_coverage_tbl,
974 x_lbr_out_tbl => x_con_lbr_coverage_tbl);
975
976 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
977 THEN
978 FND_LOG.String
979 ( FND_LOG.level_statement
980 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl.count =' || x_con_lbr_coverage_tbl.count
981 );
982 END IF;
983 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl.count = ' || x_con_lbr_coverage_tbl.count);
984
985 -- For record returned that is covered by the contract, create charge line.
986 -- For record that is not covered by the contract, get TM labor activity billing data,
987 -- then create charge line.
988 FOR i in 1..x_con_lbr_coverage_tbl.count LOOP
989
990 -- If covered by contract.
991 IF x_con_lbr_coverage_tbl(i).labor_item_id IS NOT NULL OR
992 x_con_lbr_coverage_tbl(i).flat_rate IS NOT NULL THEN
993
994 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
995 THEN
996 FND_LOG.String
997 ( FND_LOG.level_statement
998 , L_LOG_MODULE, '-- Covered by Contract Labor Coverage.'
999 );
1000 END IF;
1001 --DBMS_OUTPUT.PUT_LINE('-- Covered by Contract Labor Coverage.');
1002
1003 l_charges_rec.inventory_item_id_in := x_con_lbr_coverage_tbl(i).labor_item_id;
1004 l_charges_rec.activity_start_time := x_con_lbr_coverage_tbl(i).start_datetime;
1005 --l_charges_rec.activity_end_time := x_con_lbr_coverage_tbl(i).end_datetime;
1006
1007 -- add one minute back for last minute coverage of a day
1008 IF to_char(x_con_lbr_coverage_tbl(i).end_datetime, 'HH24:MI') = '23:59' AND
1009 l_add_one_minute = 'Y' THEN
1010 l_charges_rec.activity_end_time := x_con_lbr_coverage_tbl(i).end_datetime + (1 / (24 * 60));
1011 ELSE
1012 l_charges_rec.activity_end_time := x_con_lbr_coverage_tbl(i).end_datetime;
1013 END IF;
1014
1015 -- Calculate labor quantity in base labor unit of measurement (HR).
1016 l_duration := (l_charges_rec.activity_end_time
1017 - l_charges_rec.activity_start_time) * 24;
1018
1019 -- Convert labor quantity to labor item primary UOM or contract specified UOM.
1020 IF x_con_lbr_coverage_tbl(i).flat_rate_uom_code IS NOT NULL THEN
1021 l_primary_uom := x_con_lbr_coverage_tbl(i).flat_rate_uom_code;
1022 ELSE
1023 get_primary_uom(l_charges_rec.inventory_item_id_in, l_primary_uom);
1024 END IF;
1025 l_charges_rec.unit_of_measure_code := l_primary_uom;
1026 l_charges_rec.quantity_required := inv_convert.inv_um_convert(
1027 Null, 2, l_duration, l_base_uom, l_primary_uom, NULL, NULL);
1028
1029 IF x_con_lbr_coverage_tbl(i).flat_rate IS NOT NULL THEN
1030 l_charges_rec.list_price := x_con_lbr_coverage_tbl(i).flat_rate;
1031 ELSE
1032 l_charges_rec.list_price := NULL;
1033 END IF;
1034
1035 IF x_con_lbr_coverage_tbl(i).percent_over_listprice IS NOT NULL THEN
1036 l_charges_rec.con_pct_over_list_price := x_con_lbr_coverage_tbl(i).percent_over_listprice;
1037 ELSE
1038 l_charges_rec.con_pct_over_list_price := NULL;
1039 END IF;
1040
1041
1042 --Code Added for Bug # 5136865
1043 IF x_con_lbr_coverage_tbl(i).bill_rate_code IS NOT NULL THEN
1044 l_charges_rec.rate_type_code := x_con_lbr_coverage_tbl(i).bill_rate_code;
1045 ELSE
1046 l_charges_rec.rate_type_code := NULL;
1047 END IF;
1048
1049
1050 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1051 THEN
1052 FND_LOG.String
1053 ( FND_LOG.level_statement
1054 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').start_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS')
1055 );
1056 FND_LOG.String
1057 ( FND_LOG.level_statement
1058 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').end_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS')
1059 );
1060 FND_LOG.String
1061 ( FND_LOG.level_statement
1062 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').labor_item_id = ' || x_con_lbr_coverage_tbl(i).labor_item_id
1063 );
1064 FND_LOG.String
1065 ( FND_LOG.level_statement
1066 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').flat_rate = ' || x_con_lbr_coverage_tbl(i).flat_rate
1067 );
1068 FND_LOG.String
1069 ( FND_LOG.level_statement
1070 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').flat_rate_uom_code = ' || x_con_lbr_coverage_tbl(i).flat_rate_uom_code
1071 );
1072 FND_LOG.String
1073 ( FND_LOG.level_statement
1074 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').percent_over_listprice = ' || x_con_lbr_coverage_tbl(i).percent_over_listprice
1075 );
1076 FND_LOG.String
1077 ( FND_LOG.level_statement
1078 , L_LOG_MODULE, 'l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code
1079 );
1080 FND_LOG.String
1081 ( FND_LOG.level_statement
1082 , L_LOG_MODULE, 'l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required
1083 );
1084 FND_LOG.String
1085 ( FND_LOG.level_statement
1086 , L_LOG_MODULE, 'l_charges_rec.list_price = ' || l_charges_rec.list_price
1087 );
1088 FND_LOG.String
1089 ( FND_LOG.level_statement
1090 , L_LOG_MODULE, 'l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price
1091 );
1092 END IF;
1093 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').start_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS'));
1094 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').end_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS'));
1095 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').labor_item_id = ' || x_con_lbr_coverage_tbl(i).labor_item_id);
1096 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').flat_rate = ' || x_con_lbr_coverage_tbl(i).flat_rate);
1097 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').flat_rate_uom_code = ' || x_con_lbr_coverage_tbl(i).flat_rate_uom_code);
1098 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').percent_over_listprice = ' || x_con_lbr_coverage_tbl(i).percent_over_listprice);
1099 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code);
1100 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required);
1101 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.list_price = ' || l_charges_rec.list_price);
1102 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price);
1103
1104
1105 if l_cost_flag ='Y' and l_charge_flag ='Y' then
1106 l_create_cost_detail := 'Y';
1107 else
1108 l_create_cost_detail :='N';
1109 end if;
1110
1111 -- Create charge line.
1112 cs_charge_details_pub.Create_Charge_Details(
1113 p_api_version => p_api_version_number,
1114 p_init_msg_list => FND_API.G_FALSE,
1115 p_commit => p_commit,
1116 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1117 x_return_status => x_return_status,
1118 x_msg_count => x_msg_count,
1119 x_object_version_number => x_object_version_number,
1120 x_msg_data => x_msg_data,
1121 x_estimate_detail_id => x_estimate_detail_id,
1122 x_line_number => x_line_number,
1123 p_Charges_Rec => l_charges_rec,
1124 p_create_cost_detail =>l_create_cost_detail, --Added for Service Costing
1125 x_cost_id =>x_cost_id);
1126 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1127 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CHARGE_DETAILS_API_ERR');
1128 FND_MSG_PUB.ADD;
1129 RAISE EXCP_USER_DEFINED;
1130 END IF;
1131
1132 ELSE -- Not covered by contract.
1133
1134 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1135 THEN
1136 FND_LOG.String
1137 ( FND_LOG.level_statement
1138 , L_LOG_MODULE, '-- Not covered by Contract Labor Coverage. Calling TM Labor Coverage...'
1139 );
1140 FND_LOG.String
1141 ( FND_LOG.level_statement
1142 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').start_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS')
1143 );
1144 FND_LOG.String
1145 ( FND_LOG.level_statement
1146 , L_LOG_MODULE, 'x_con_lbr_coverage_tbl('||i||').end_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS')
1147 );
1148 END IF;
1149 --DBMS_OUTPUT.PUT_LINE('-- Not covered by Contract Labor Coverage. Calling TM Labor Coverage...');
1150 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').start_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).start_datetime, 'DD-MON-YYYY HH24:MI:SS'));
1151 --DBMS_OUTPUT.PUT_LINE('x_con_lbr_coverage_tbl('||i||').end_datetime = ' || TO_CHAR(x_con_lbr_coverage_tbl(i).end_datetime, 'DD-MON-YYYY HH24:MI:SS'));
1152
1153 -- Get labor activity billing data for those not covered by contract.
1154 cs_tm_labor_schedule_pvt.get_labor_coverages(
1155 p_business_process_id => p_sbe_record.business_process_id,
1156 p_activity_start_date_time => x_con_lbr_coverage_tbl(i).start_datetime,
1157 p_activity_end_date_time => x_con_lbr_coverage_tbl(i).end_datetime,
1158 x_labor_coverage_tbl => x_tm_coverage_tbl,
1159 x_return_status => x_return_status,
1160 x_msg_count => x_msg_count,
1161 x_msg_data => x_msg_data,
1162 p_api_version => p_api_version_number,
1163 p_init_msg_list => FND_API.G_FALSE);
1164
1165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1166 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TM_LBR_SCHEDULE_API_ERR');
1167 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
1168 FND_MSG_PUB.ADD;
1169 RAISE EXCP_USER_DEFINED;
1170 END IF;
1171
1172 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1173 THEN
1174 FND_LOG.String
1175 ( FND_LOG.level_statement
1176 , L_LOG_MODULE, 'x_tm_coverage_tbl.count = ' || x_tm_coverage_tbl.count
1177 );
1178 END IF;
1179 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl.count = ' || x_tm_coverage_tbl.count);
1180
1181 -- Calling Charges API to create labor charge lines.
1182 FOR j in 1..x_tm_coverage_tbl.count LOOP
1183
1184 l_charges_rec.inventory_item_id_in := x_tm_coverage_tbl(j).inventory_item_id;
1185 l_charges_rec.activity_start_time := x_tm_coverage_tbl(j).labor_start_date_time;
1186
1187 -- add one minute back for last minute coverage of a day
1188 --IF to_char(x_tm_coverage_tbl(j).labor_end_date_time, 'HH24:MI') = '23:59' then
1189 IF to_char(x_tm_coverage_tbl(j).labor_end_date_time, 'HH24:MI') = '23:59' AND
1190 l_add_one_minute = 'Y' THEN
1191 l_charges_rec.activity_end_time := x_tm_coverage_tbl(j).labor_end_date_time + (1 / (24 * 60));
1192 ELSE
1193 l_charges_rec.activity_end_time := x_tm_coverage_tbl(j).labor_end_date_time;
1194 END IF;
1195
1196 -- Calculate labor quantity.
1197 l_duration := (l_charges_rec.activity_end_time
1198 - l_charges_rec.activity_start_time) * 24;
1199
1200 get_primary_uom(l_charges_rec.inventory_item_id_in, l_primary_uom);
1201 l_charges_rec.unit_of_measure_code := l_primary_uom;
1202 l_charges_rec.quantity_required := inv_convert.inv_um_convert(
1203 Null, 2, l_duration, l_base_uom, l_primary_uom, NULL, NULL);
1204 l_charges_rec.list_price := NULL;
1205 l_charges_rec.con_pct_over_list_price := NULL;
1206
1207 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1208 THEN
1209 FND_LOG.String
1210 ( FND_LOG.level_statement
1211 , L_LOG_MODULE, '- TM Labor Coverage.'
1212 );
1213 FND_LOG.String
1214 ( FND_LOG.level_statement
1215 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').inventory_item_id = ' || x_tm_coverage_tbl('||j||').inventory_item_id
1216 );
1217 FND_LOG.String
1218 ( FND_LOG.level_statement
1219 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').labor_start_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS')
1220 );
1221 FND_LOG.String
1222 ( FND_LOG.level_statement
1223 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').labor_end_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS')
1224 );
1225 FND_LOG.String
1226 ( FND_LOG.level_statement
1227 , L_LOG_MODULE, 'l_charges_rec.activity_end_time = ' || TO_CHAR(l_charges_rec.activity_end_time, 'DD-MON-YYYY HH24:MI:SS')
1228 );
1229 FND_LOG.String
1230 ( FND_LOG.level_statement
1231 , L_LOG_MODULE, 'l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code
1232 );
1233 FND_LOG.String
1234 ( FND_LOG.level_statement
1235 , L_LOG_MODULE, 'l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required
1236 );
1237 FND_LOG.String
1238 ( FND_LOG.level_statement
1239 , L_LOG_MODULE, 'l_charges_rec.list_price = ' || l_charges_rec.list_price
1240 );
1241 FND_LOG.String
1242 ( FND_LOG.level_statement
1243 , L_LOG_MODULE, 'l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price
1244 );
1245 END IF;
1246 --DBMS_OUTPUT.PUT_LINE('- TM Labor Coverage.');
1247 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').inventory_item_id = ' || x_tm_coverage_tbl(j).inventory_item_id);
1248 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').labor_start_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS'));
1249 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').labor_end_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS'));
1250 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.activity_end_time = ' || TO_CHAR(l_charges_rec.activity_end_time, 'DD-MON-YYYY HH24:MI:SS'));
1251 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code);
1252 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required);
1253 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.list_price = ' || l_charges_rec.list_price);
1254 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price);
1255
1256 if l_cost_flag ='Y' and l_charge_flag ='Y' then
1257 l_create_cost_detail := 'Y';
1258 else
1259 l_create_cost_detail :='N';
1260 end if;
1261
1262 -- Create charge line.
1263 cs_charge_details_pub.Create_Charge_Details(
1264 p_api_version => p_api_version_number,
1265 p_init_msg_list => FND_API.G_FALSE,
1266 p_commit => p_commit,
1267 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_object_version_number => x_object_version_number,
1271 x_msg_data => x_msg_data,
1272 x_estimate_detail_id => x_estimate_detail_id,
1273 x_line_number => x_line_number,
1274 p_Charges_Rec => l_charges_rec,
1275 p_create_cost_detail =>l_create_cost_detail, --Added for Service Costing
1276 x_cost_id =>x_cost_id);
1277 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1278 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CHARGE_DETAILS_API_ERR');
1279 FND_MSG_PUB.ADD;
1280 RAISE EXCP_USER_DEFINED;
1281 END IF;
1282
1283 END LOOP;
1284
1285 END IF;
1286
1287 END LOOP;
1288
1289 ELSE -- No contract.
1290
1291 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1292 THEN
1293 FND_LOG.String
1294 ( FND_LOG.level_statement, L_LOG_MODULE, '-- No contract. Calling TM Labor Coverage...'
1295 );
1296 END IF;
1297 --DBMS_OUTPUT.PUT_LINE('-- No contract. Calling TM Labor Coverage...');
1298
1299 -- Get TM labor schedule for those not covered by contract.
1300 cs_tm_labor_schedule_pvt.get_labor_coverages(
1301 p_business_process_id => p_sbe_record.business_process_id,
1302 p_activity_start_date_time => l_activity_start_date_time,
1303 p_activity_end_date_time => l_activity_end_date_time,
1304 x_labor_coverage_tbl => x_tm_coverage_tbl,
1305 x_return_status => x_return_status,
1306 x_msg_count => x_msg_count,
1307 x_msg_data => x_msg_data,
1308 p_api_version => p_api_version_number,
1309 p_init_msg_list => FND_API.G_FALSE);
1310
1311 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1312 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_TM_LBR_SCHEDULE_API_ERR');
1313 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
1314 FND_MSG_PUB.ADD;
1315 RAISE EXCP_USER_DEFINED;
1316 END IF;
1317
1318 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1319 THEN
1320 FND_LOG.String
1321 ( FND_LOG.level_statement, L_LOG_MODULE || 'x_tm_coverage_tbl.count = '
1322 , x_tm_coverage_tbl.count
1323 );
1324 END IF;
1325 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl.count = ' || x_tm_coverage_tbl.count);
1326
1327 -- Calling Charges API to create labor charge lines.
1328 FOR j in 1..x_tm_coverage_tbl.count LOOP
1329
1330 l_charges_rec.inventory_item_id_in := x_tm_coverage_tbl(j).inventory_item_id;
1331 l_charges_rec.activity_start_time := x_tm_coverage_tbl(j).labor_start_date_time;
1332
1333 -- add one minute back for last minute coverage of a day
1334 --IF to_char(x_tm_coverage_tbl(j).labor_end_date_time, 'HH24:MI') = '23:59' then
1335 IF to_char(x_tm_coverage_tbl(j).labor_end_date_time, 'HH24:MI') = '23:59' AND
1336 l_add_one_minute = 'Y' THEN
1337 l_charges_rec.activity_end_time := x_tm_coverage_tbl(j).labor_end_date_time + (1 / (24 * 60));
1338 ELSE
1339 l_charges_rec.activity_end_time := x_tm_coverage_tbl(j).labor_end_date_time;
1340 END IF;
1341
1342 -- Calculate labor quantity.
1343 l_duration := (l_charges_rec.activity_end_time
1344 - l_charges_rec.activity_start_time) * 24;
1345
1346 get_primary_uom(l_charges_rec.inventory_item_id_in, l_primary_uom);
1347 l_charges_rec.unit_of_measure_code := l_primary_uom;
1348 l_charges_rec.quantity_required := inv_convert.inv_um_convert(
1349 Null, 2, l_duration, l_base_uom, l_primary_uom, NULL, NULL);
1350 l_charges_rec.list_price := NULL;
1351 l_charges_rec.con_pct_over_list_price := NULL;
1352
1353 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1354 THEN
1355 FND_LOG.String
1356 ( FND_LOG.level_statement
1357 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').inventory_item_id = ' || x_tm_coverage_tbl('||j||').inventory_item_id
1358 );
1359 FND_LOG.String
1360 ( FND_LOG.level_statement
1361 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').labor_start_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS')
1362 );
1363 FND_LOG.String
1364 ( FND_LOG.level_statement
1365 , L_LOG_MODULE, 'x_tm_coverage_tbl('||j||').labor_end_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS')
1366 );
1367 FND_LOG.String
1368 ( FND_LOG.level_statement
1369 , L_LOG_MODULE, 'l_charges_rec.activity_end_time = ' ||TO_CHAR(l_charges_rec.activity_end_time, 'DD-MON-YYYY HH24:MI:SS')
1370 );
1371 FND_LOG.String
1372 ( FND_LOG.level_statement
1373 , L_LOG_MODULE, 'l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code
1374 );
1375 FND_LOG.String
1376 ( FND_LOG.level_statement
1377 , L_LOG_MODULE, 'l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required
1378 );
1379 FND_LOG.String
1380 ( FND_LOG.level_statement
1381 , L_LOG_MODULE, 'l_charges_rec.list_price = ' || l_charges_rec.list_price
1382 );
1383 FND_LOG.String
1384 ( FND_LOG.level_statement
1385 , L_LOG_MODULE, 'l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price
1386 );
1387 END IF;
1388 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').inventory_item_id = ' || x_tm_coverage_tbl(j).inventory_item_id);
1389 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').labor_start_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_start_date_time, 'DD-MON-YYYY HH24:MI:SS'));
1390 --DBMS_OUTPUT.PUT_LINE('x_tm_coverage_tbl('||j||').labor_end_date_time = ' || TO_CHAR(x_tm_coverage_tbl(j).labor_end_date_time, 'DD-MON-YYYY HH24:MI:SS'));
1391 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.activity_end_time = ' || TO_CHAR(l_charges_rec.activity_end_time, 'DD-MON-YYYY HH24:MI:SS'));
1392 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.unit_of_measure_code = ' || l_charges_rec.unit_of_measure_code);
1393 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.quantity_required = ' || l_charges_rec.quantity_required);
1394 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.list_price = ' || l_charges_rec.list_price);
1395 --DBMS_OUTPUT.PUT_LINE('l_charges_rec.con_pct_over_list_price = ' || l_charges_rec.con_pct_over_list_price);
1396
1397
1398 if l_cost_flag ='Y' and l_charge_flag ='Y' then
1399 l_create_cost_detail := 'Y';
1400 else
1401 l_create_cost_detail :='N';
1402 end if;
1403
1404 -- Create charge line.
1405 cs_charge_details_pub.Create_Charge_Details(
1406 p_api_version => p_api_version_number,
1407 p_init_msg_list => FND_API.G_FALSE,
1408 p_commit => p_commit,
1409 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_object_version_number => x_object_version_number,
1413 x_msg_data => x_msg_data,
1414 x_estimate_detail_id => x_estimate_detail_id,
1415 x_line_number => x_line_number,
1416 p_Charges_Rec => l_charges_rec,
1417 p_create_cost_detail =>l_create_cost_detail, --Added for Service Costing
1418 x_cost_id =>x_cost_id);
1419
1420 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CHARGE_DETAILS_API_ERR');
1422 FND_MSG_PUB.ADD;
1423 RAISE EXCP_USER_DEFINED;
1424 END IF;
1425
1426 END LOOP;
1427
1428 END IF;
1429
1430 l_activity_start_date_time := trunc(l_activity_start_date_time) + 1;
1431
1432 END LOOP;
1433
1434 ELSE -- Inventory item is specified.
1435
1436 -- bugfix#5443461
1437 OPEN get_billing_category(p_sbe_record.txn_billing_type_id);
1438 FETCH get_billing_category
1439 INTO l_billing_category;
1440 CLOSE get_billing_category;
1441
1442 -- Bug 7229344
1443 OPEN billing_category;
1444 FETCH billing_category into l_bill_category;
1445 CLOSE billing_category;
1446 -- End bug 7229344
1447
1448
1449 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1450 THEN
1451 FND_LOG.String
1452 ( FND_LOG.level_statement, L_LOG_MODULE , '--- p_sbe_record.inventory_item_id is not null.'
1453 );
1454 END IF;
1455 -- DBMS_OUTPUT.PUT_LINE('--- p_sbe_record.inventory_item_id is not null.');
1456
1457 --dbms_output.put_line('--- Checking for Single Rate Type.');
1458 --Added for SIMPLEX Enhancement for Defaulting Rate Type
1459 --Check to see if there is a single rate defined for the inventory item
1460 --Bug # 5136865
1461
1462 -- Get transaction billing type for service debrief labor activity that without labor inventory item specified.
1463 --
1464 IF l_charges_rec.transaction_type_id IS NOT NULL THEN
1465 OPEN txn_billing_type_csr(l_charges_rec.transaction_type_id,l_bill_category); -- Bug 7229344
1466 FETCH txn_billing_type_csr into l_charges_rec.txn_billing_type_id;
1467 CLOSE txn_billing_type_csr;
1468 END IF;
1469
1470 --dbms_output.put_line('l_charges_rec.txn_billing_type_id'||l_charges_rec.txn_billing_type_id);
1471 --dbms_output.put_line('l_charges_rec.txn_billing_type_id'||l_charges_rec.txn_billing_type_id);
1472 --dbms_output.put_line('l_charges_rec.coverage_txn_group_id'||l_charges_rec.coverage_txn_group_id);
1473
1474 IF l_charges_rec.txn_billing_type_id IS NOT NULL AND
1475 l_charges_rec.business_process_id IS NOT NULL THEN
1476 FOR v_get_rate_type in get_rate_type( l_charges_rec.contract_line_id,
1477 l_charges_rec.txn_billing_type_id,
1478 l_charges_rec.business_process_id)
1479 LOOP
1480 --dbms_output.put_line('In the loop');
1481 k := k + 1;
1482 l_charges_rec.rate_type_code := v_get_rate_type.rate_code;
1483 l_rate_amount := v_get_rate_type.rate_amount;
1484 l_rate_percent := v_get_rate_type.rate_percent;
1485 l_rate_uom := v_get_rate_type.rate_uom;
1486 IF k > 1 THEN
1487 EXIT;
1488 END IF;
1489 END LOOP;
1490
1491 ----dbms_output.put_line('k ='||k);
1492 IF k > 1 THEN
1493 l_charges_rec.rate_type_code := NULL ;
1494 l_rate_amount := NULL;
1495 l_rate_percent := NULL;
1496 l_rate_uom := NULL;
1497 ELSE
1498 IF l_rate_uom <> p_sbe_record.unit_of_measure_code THEN
1499 -- reset the rate fields from contract
1500 -- no rate will be defaulted from contract
1501 l_charges_rec.rate_type_code := NULL ;
1502 l_rate_amount := NULL;
1503 l_rate_percent := NULL;
1504 l_rate_uom := NULL;
1505 END IF;
1506 END IF;
1507 ELSE
1508 --txn_billing_type_id IS NULL and
1509 --business_process_id IS NULL
1510 l_charges_rec.rate_type_code := NULL ;
1511 END IF;
1512
1513
1514 l_charges_rec.inventory_item_id_in := p_sbe_record.inventory_item_id;
1515 l_charges_rec.quantity_required := p_sbe_record.quantity;
1516 l_charges_rec.unit_of_measure_code := p_sbe_record.unit_of_measure_code;
1517 -- l_charges_rec.list_price := p_sbe_record.after_warranty_cost;
1518 -- l_charges_rec.con_pct_over_list_price := NULL;
1519 -- l_charges_rec.after_warranty_cost := p_sbe_record.after_warranty_cost;
1520
1521
1522 --check if flat rate is vailable
1523 -- Fix for the bug:5136865
1524 IF l_rate_amount IS NOT NULL THEN
1525 l_charges_rec.list_price := l_rate_amount;
1526 ELSE
1527 l_charges_rec.list_price := NULL;
1528 END IF;
1529
1530 -- check if %over list price is vailable
1531 IF l_rate_percent IS NOT NULL THEN
1532 l_charges_rec.con_pct_over_list_price := l_rate_percent;
1533 ELSE
1534 l_charges_rec.con_pct_over_list_price := NULL;
1535 END IF;
1536
1537 -- If both not avaible then then assign after warranty cost to list price.
1538 IF l_rate_amount IS NULL AND
1539 l_rate_percent IS NULL THEN
1540 l_charges_rec.list_price := p_sbe_record.after_warranty_cost;
1541 l_charges_rec.con_pct_over_list_price := NULL;
1542 END IF;
1543
1544 -- If expense line is passed by debrief with after_warranty_cost. Part of bugfix#5443461
1545 -- Bug 7257903, use l_bill_category instead of l_billing_category
1546 IF l_bill_category = 'E' AND
1547 p_sbe_record.after_warranty_cost IS NOT NULL THEN
1548 l_charges_rec.after_warranty_cost := p_sbe_record.after_warranty_cost;
1549 END IF;
1550
1551 -- Create charge line.
1552 cs_charge_details_pub.Create_Charge_Details(
1553 p_api_version => p_api_version_number,
1554 p_init_msg_list => FND_API.G_FALSE,
1555 p_commit => p_commit,
1556 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_object_version_number => x_object_version_number,
1560 x_msg_data => x_msg_data,
1561 x_estimate_detail_id => x_estimate_detail_id,
1562 x_line_number => x_line_number,
1563 p_Charges_Rec => l_charges_rec,
1564 p_create_cost_detail =>'N',
1565 x_cost_id =>x_cost_id);
1566
1567 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1568 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CHARGE_DETAILS_API_ERR');
1569 FND_MSG_PUB.ADD;
1570 RAISE EXCP_USER_DEFINED;
1571 END IF;
1572
1573 END IF; -- inventory_item_id check
1574
1575 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1576 THEN
1577 FND_LOG.String
1578 ( FND_LOG.level_statement, L_LOG_MODULE, '----- Private API:' || g_pkg_name || '.' || l_api_name || ' ends at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SSSSS')
1579 );
1580 END IF;
1581 --DBMS_OUTPUT.PUT_LINE('----- Private API: ' || g_pkg_name || '.' || l_api_name || ' ends at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SSSSS'));
1582 --DBMS_OUTPUT.PUT_LINE('Return status = ' || x_return_status);
1583
1584 -- Commit if p_commit is true.
1585 IF FND_API.to_Boolean(p_commit) THEN
1586 COMMIT;
1587 END IF;
1588
1589 -- Exception Block
1590 EXCEPTION
1591 WHEN EXCP_USER_DEFINED THEN
1592 Rollback to Create_Charges;
1593 x_return_status := FND_API.G_RET_STS_ERROR;
1594 fnd_msg_pub.count_and_get(
1595 p_count => x_msg_count
1596 ,p_data => x_msg_data);
1597 WHEN FND_API.G_EXC_ERROR THEN
1598 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1599 P_API_NAME => L_API_NAME
1600 ,P_PKG_NAME => G_PKG_NAME
1601 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1602 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1603 ,X_MSG_COUNT => x_MSG_COUNT
1604 ,X_MSG_DATA => x_MSG_DATA
1605 ,X_RETURN_STATUS => x_RETURN_STATUS);
1606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1607 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1608 P_API_NAME => L_API_NAME
1609 ,P_PKG_NAME => G_PKG_NAME
1610 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1611 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1612 ,X_MSG_COUNT => x_MSG_COUNT
1613 ,X_MSG_DATA => x_MSG_DATA
1614 ,X_RETURN_STATUS => x_RETURN_STATUS);
1615 WHEN OTHERS THEN
1616 Rollback to Create_Charges;
1617 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_UNEXPECTED_EXEC_ERRORS');
1618 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name);
1619 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm);
1620 FND_MSG_PUB.ADD;
1621 fnd_msg_pub.count_and_get(
1622 p_count => x_msg_count
1623 ,p_data => x_msg_data);
1624 x_return_status := FND_API.G_RET_STS_ERROR;
1625
1626 END Create_Charges;
1627
1628
1629 -------------------------------------------------------------------------
1630 -- Delete all Billing Engine generated in progress charges for the service request (p_incident_id) passed in.
1631
1632 PROCEDURE Delete_In_Progress_Charges(
1633 P_Api_Version_Number IN NUMBER,
1634 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1635 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1636 p_incident_id IN NUMBER,
1637 p_debrief_header_id IN NUMBER := NULL, -- Enhancement 2983340
1638 p_debrief_line_id IN NUMBER := NULL, -- To make the API backward compatible
1639 x_return_status OUT NOCOPY VARCHAR2,
1640 x_msg_count OUT NOCOPY NUMBER,
1641 x_msg_data OUT NOCOPY VARCHAR2)
1642 IS
1643
1644 l_api_name CONSTANT VARCHAR2(30) := 'Delete_In_Progress_Charges';
1645 l_api_version_number CONSTANT NUMBER := 1.0;
1646 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
1647 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
1648
1649 EXCP_USER_DEFINED EXCEPTION;
1650
1651 l_estimate_detail_id NUMBER;
1652
1653 -- Enhancement 2983340 CURSOR in_progress_charges_csr(p_incident_id number) IS
1654 CURSOR in_progress_charges_csr(p_incident_id number, p_debrief_header_id number) IS -- Enhancement 2983340
1655 SELECT estimate_detail_id
1656 FROM cs_estimate_details
1657 WHERE incident_id = p_incident_id
1658 and source_code = 'SD' -- Enhancement 2983340
1659 and source_id in (select debrief_line_id from csf_debrief_lines where debrief_header_id = nvl(p_debrief_header_id, debrief_header_id)) -- Enhancement 2983340
1660 -- Fix bug 3137168 and generated_by_bca_engine_flag = 'Y'
1661 and charge_line_type = 'IN_PROGRESS';
1662
1663 --Added to Fix Bug # 3539583
1664 CURSOR in_progress_charge_csr(p_incident_id number, p_debrief_line_id number) IS -- Enhancement 2983340
1665 SELECT estimate_detail_id
1666 FROM cs_estimate_details
1667 WHERE incident_id = p_incident_id
1668 and source_code = 'SD' -- Enhancement 2983340
1669 and source_id in (select debrief_line_id from csf_debrief_lines where debrief_line_id = nvl(p_debrief_line_id, debrief_line_id)) -- Enhancement 2983340
1670 and charge_line_type = 'IN_PROGRESS';
1671
1672
1673 BEGIN
1674 SAVEPOINT Delete_In_Progress_Charges;
1675
1676 -- Standard call to check for call compatibility.
1677 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1678 p_api_version_number,
1679 l_api_name,
1680 G_PKG_NAME)
1681 THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684
1685 -- Initialize message list if p_init_msg_list is set to TRUE.
1686 IF FND_API.to_Boolean(p_init_msg_list)
1687 THEN
1688 FND_MSG_PUB.initialize;
1689 END IF;
1690
1691 ----------------------- FND Logging -----------------------------------
1692 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1693 THEN
1694 FND_LOG.String
1695 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
1696 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
1697 );
1698 FND_LOG.String
1699 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1700 , 'P_Api_Version_Number:' || P_Api_Version_Number
1701 );
1702 FND_LOG.String
1703 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1704 , 'p_init_msg_list:' || p_init_msg_list
1705 );
1706 FND_LOG.String
1707 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1708 , 'p_commit:' || p_commit
1709 );
1710 FND_LOG.String
1711 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1712 , 'p_incident_id:' || p_incident_id
1713 );
1714 FND_LOG.String
1715 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1716 , 'p_debrief_header_id:' || p_debrief_header_id
1717 );
1718 FND_LOG.String
1719 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1720 , 'p_debrief_line_id:' || p_debrief_line_id
1721 );
1722 END IF;
1723
1724 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1725 THEN
1726 FND_LOG.String
1727 ( FND_LOG.level_statement, L_LOG_MODULE , '----- Private API:'
1728 || g_pkg_name || '.' || l_api_name || ' starts at ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS')
1729 );
1730 END IF;
1731 --DBMS_OUTPUT.PUT_LINE('----- Private API: ' || g_pkg_name || '.' || l_api_name || ' starts at ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1732 --DBMS_OUTPUT.PUT_LINE('p_incident_id = ' || p_incident_id);
1733
1734 -- Initialize API return status to SUCCESS
1735 x_return_status := FND_API.G_RET_STS_SUCCESS;
1736
1737 --Added to Fix Bug # 3539583
1738 IF p_debrief_line_id IS NOT NULL THEN
1739
1740 OPEN in_progress_charge_csr(p_incident_id, p_debrief_line_id);
1741 FETCH in_progress_charge_csr into l_estimate_detail_id;
1742 CLOSE in_progress_charge_csr;
1743
1744 IF l_estimate_detail_id IS NOT NULL THEN
1745
1746 cs_charge_details_pub.Delete_Charge_Details(
1747 p_api_version => p_api_version_number,
1748 p_init_msg_list => FND_API.G_FALSE,
1749 p_commit => p_commit,
1750 x_return_status => x_return_status,
1751 x_msg_count => x_msg_count,
1752 x_msg_data => x_msg_data,
1753 p_estimate_detail_id => l_estimate_detail_id,
1754 p_delete_cost_detail =>'N');
1755
1756 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1757 CLOSE in_progress_charges_csr;
1758 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_DLT_IN_PROGRESS_CHRG_ER');
1759 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
1760 FND_MSG_PUB.ADD;
1761 RAISE EXCP_USER_DEFINED;
1762 END IF;
1763
1764 END IF;
1765
1766 ELSE
1767 -- p_debrief_line_id is null
1768 -- delete all in progress charges for a service request
1769 -- Enhancement 2983340 OPEN in_progress_charges_csr(p_incident_id);
1770 OPEN in_progress_charges_csr(p_incident_id, p_debrief_header_id); -- Enhancement 2983340
1771 LOOP
1772 FETCH in_progress_charges_csr into l_estimate_detail_id;
1773 EXIT WHEN in_progress_charges_csr%NOTFOUND;
1774
1775 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1776 THEN
1777 FND_LOG.String
1778 ( FND_LOG.level_statement, L_LOG_MODULE || 'Delete l_estimate_detail_id = '
1779 , l_estimate_detail_id
1780 );
1781 END IF;
1782 --DBMS_OUTPUT.PUT_LINE('Delete l_estimate_detail_id = ' || l_estimate_detail_id);
1783
1784 cs_charge_details_pub.Delete_Charge_Details(
1785 p_api_version => p_api_version_number,
1786 p_init_msg_list => FND_API.G_FALSE,
1787 p_commit => p_commit,
1788 x_return_status => x_return_status,
1789 x_msg_count => x_msg_count,
1790 x_msg_data => x_msg_data,
1791 p_estimate_detail_id => l_estimate_detail_id,
1792 p_delete_cost_detail =>'N');
1793
1794 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1795 CLOSE in_progress_charges_csr;
1796 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_DLT_IN_PROGRESS_CHRG_ER');
1797 FND_MESSAGE.SET_TOKEN('TEXT', x_msg_data);
1798 FND_MSG_PUB.ADD;
1799 RAISE EXCP_USER_DEFINED;
1800 END IF;
1801
1802 END LOOP;
1803 CLOSE in_progress_charges_csr;
1804 END IF;
1805
1806 -- Commit if p_commit is TRUE.
1807 IF FND_API.to_Boolean(p_commit) THEN
1808 COMMIT;
1809 END IF;
1810
1811 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
1812 THEN
1813 FND_LOG.String
1814 ( FND_LOG.level_statement, L_LOG_MODULE || 'Private API:'
1815 , g_pkg_name || '.' || l_api_name || ' ends at ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS')
1816 );
1817 END IF;
1818 --DBMS_OUTPUT.PUT_LINE('Private API: ' || g_pkg_name || '.' || l_api_name || ' ends at ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1819 --DBMS_OUTPUT.PUT_LINE('Return status = ' || x_return_status);
1820
1821 -- Exception Block
1822 EXCEPTION
1823 WHEN EXCP_USER_DEFINED THEN
1824 Rollback to Delete_In_Progress_Charges;
1825 x_return_status := FND_API.G_RET_STS_ERROR;
1826 fnd_msg_pub.count_and_get(
1827 p_count => x_msg_count
1828 ,p_data => x_msg_data);
1829 WHEN FND_API.G_EXC_ERROR THEN
1830 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1831 P_API_NAME => L_API_NAME
1832 ,P_PKG_NAME => G_PKG_NAME
1833 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1834 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1835 ,X_MSG_COUNT => x_MSG_COUNT
1836 ,X_MSG_DATA => x_MSG_DATA
1837 ,X_RETURN_STATUS => x_RETURN_STATUS);
1838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1839 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1840 P_API_NAME => L_API_NAME
1841 ,P_PKG_NAME => G_PKG_NAME
1842 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1843 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1844 ,X_MSG_COUNT => x_MSG_COUNT
1845 ,X_MSG_DATA => x_MSG_DATA
1846 ,X_RETURN_STATUS => x_RETURN_STATUS);
1847 WHEN OTHERS THEN
1848 Rollback to Delete_In_Progress_Charges;
1849 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_UNEXPECTED_EXEC_ERRORS');
1850 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name);
1851 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm);
1852 FND_MSG_PUB.ADD;
1853 fnd_msg_pub.count_and_get(
1854 p_count => x_msg_count
1855 ,p_data => x_msg_data);
1856 x_return_status := FND_API.G_RET_STS_ERROR;
1857
1858 END Delete_In_Progress_Charges;
1859
1860 End CS_Service_Billing_Engine_PVT;
1861