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