[Home] [Help]
PACKAGE BODY: APPS.CS_COST_DETAILS_PVT
Source
1 PACKAGE BODY CS_Cost_Details_PVT AS
2 /* $Header: csxvcstb.pls 120.25.12020000.3 2013/03/03 02:06:43 bkanimoz ship $ */
3
4 L_RECALC_COST VARCHAR2(1):='N';
5 l_item_changed VARCHAR2(1):='N';
6 L_TRANSACTION_TYPE_CHANGED VARCHAR(1):='N';
7
8 RECORD_LOCK_EXCEPTION EXCEPTION ;
9 G_WARNING EXCEPTION ;
10 PRAGMA EXCEPTION_INIT(RECORD_LOCK_EXCEPTION,-0054);
11
12 --local Procedures and Functions
13 PROCEDURE Validate_Who_Info(P_API_NAME IN VARCHAR2,
14 P_USER_ID IN NUMBER,
15 P_LOGIN_ID IN NUMBER,
16 X_RETURN_STATUS OUT NOCOPY VARCHAR2
17 );
18
19 FUNCTION Do_Cost_Line_Exist(p_api_name IN VARCHAR2,
20 p_cost_id IN NUMBER ,
21 x_return_status OUT NOCOPY VARCHAR2
22 )RETURN VARCHAR2;
23
24 FUNCTION Do_charge_line_Exist(p_api_name IN VARCHAR2,
25 p_cost_id IN NUMBER ,
26 x_return_status OUT NOCOPY VARCHAR2
27 )RETURN VARCHAR2;
28
29 PROCEDURE RECORD_IS_LOCKED_MSG(P_TOKEN_AN IN VARCHAR2);
30
31 PROCEDURE TO_NULL(p_cost_rec_in IN cs_cost_details_pub.Cost_Rec_Type,
32 p_cost_rec_out OUT NOCOPY cs_cost_details_pub.Cost_Rec_Type
33 );
34
35 PROCEDURE VALIDATE_COST_DETAILS
36 (
37 p_api_name IN VARCHAR2,
38 pv_cost_rec IN CS_COST_DETAILS_PUB.COST_REC_TYPE,
39 p_validation_mode IN VARCHAR2,
40 p_user_id IN NUMBER,
41 p_login_id IN NUMBER,
42 x_cost_rec OUT NOCOPY CS_COST_DETAILS_PUB.COST_REC_TYPE,
43 x_msg_data OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_return_status OUT NOCOPY VARCHAR2
46 );
47
48 /*Defaulting occurs for each MISSING attribute.
49 Attributes that are not explicitly passed by the user and therefore, retain the values
50 on the initialized record are defined as MISSING attributes. For e.g. all the number
51 fields on the entity records are initialized to the value FND_API.G_MISS_NUM and
52 thus, all number fields with this value are MISSING attributes.
53 */
54
55 FUNCTION Check_For_Miss ( p_param IN NUMBER ) RETURN NUMBER ;
56 FUNCTION Check_For_Miss ( p_param IN VARCHAR2 ) RETURN VARCHAR2 ;
57 FUNCTION Check_For_Miss ( p_param IN DATE ) RETURN DATE ;
58
59 --===============================
60 -- Add_Invalid_Argument_Msg
61 --===============================
62
63 PROCEDURE Add_Invalid_Argument_Msg( p_token_an VARCHAR2,
64 p_token_v VARCHAR2,
65 p_token_p VARCHAR2
66 ) IS
67 BEGIN
68
69 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
70 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_INVALID_ARGUMENT');
71 FND_MESSAGE.Set_Token('API_NAME', p_token_an);
72 FND_MESSAGE.Set_Token('VALUE', p_token_v);
73 FND_MESSAGE.Set_Token('PARAMETER', p_token_p);
74 FND_MSG_PUB.Add;
75 END IF;
76
77 END Add_Invalid_Argument_Msg;
78
79 --===============================
80 -- Add_Null_Parameter_Msg
81 --===============================
82
83 PROCEDURE Add_Null_Parameter_Msg(p_token_an VARCHAR2,
84 p_token_np VARCHAR2
85 ) IS
86
87 BEGIN
88
89 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
90 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_NULL_PARAMETER');
91 FND_MESSAGE.Set_Token('API_NAME', p_token_an);
92 FND_MESSAGE.Set_Token('NULL_PARAM', p_token_np);
93 FND_MSG_PUB.Add;
94 END IF;
95
96 END Add_Null_Parameter_Msg;
97
98
99 --===============================
100 -- IS_COST_FLAG_CHECKED
101 --===============================
102 ----This Function will check if the Create_Cost flag in the SAC setup Screen is checked
103
104 FUNCTION IS_COST_FLAG_CHECKED ( p_transaction_type_id IN NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 x_msg_count OUT NOCOPY NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2
108 ) RETURN VARCHAR2 IS
109
110 CURSOR c_transaction_type_id IS
111 SELECT 1
112 FROM CS_transaction_types_b
113 WHERE transaction_type_id = p_transaction_type_id
114 AND create_cost_flag = 'Y';
115
116 lv_exists_flag VARCHAR2(1) := 'N';
117 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_cost_flag_checked';
118
119 BEGIN
120
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 FOR v_transaction_type_id IN c_transaction_type_id LOOP
124 lv_exists_flag := 'Y';
125 END LOOP;
126
127 RETURN lv_exists_flag;
128
129 EXCEPTION
130
131 WHEN OTHERS THEN
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 RETURN lv_exists_flag;
134
135 END IS_COST_FLAG_CHECKED;
136
137 --===============================
138 -- IS_CHARGE_LINE_TYPE_VALID
139 --===============================
140 --This Function will check if the Charge Line type is 'ACTUAL'.Cost Lines can be created only for 'ACTUALS'
141
142 FUNCTION IS_CHARGE_LINE_TYPE_VALID(p_charge_line_type IN VARCHAR2,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_return_status OUT NOCOPY VARCHAR2
146 )RETURN VARCHAR2 IS
147
148 CURSOR c_charge_line_type (p_charge_line_type IN VARCHAR2) IS
149 SELECT lookup_code
150 FROM fnd_lookup_values
151 WHERE lookup_type = 'CS_CHG_LINE_TYPE'
152 AND lookup_code = p_charge_line_type;
153
154 lv_exists_flag VARCHAR2(1) := 'N';
155 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_charge_line_type_valid';
156
157 BEGIN
158
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 FOR v_charge_line_type IN c_charge_line_type(p_charge_line_type)
162 LOOP
163 IF v_charge_line_type.lookup_code ='ACTUAL' then
164 lv_exists_flag := 'Y';
165 END IF;
166 END LOOP ;
167 RETURN lv_exists_flag;
168
169 EXCEPTION
170
171 WHEN OTHERS THEN
172 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
173 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
174 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
175 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
176 FND_MSG_PUB.add;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 RETURN lv_exists_flag;
179
180 END IS_CHARGE_LINE_TYPE_VALID;
181
182 --===============================
183 -- IS_INCIDENT_ID_VALID
184 --===============================
185 --This Funtion checks of the incident id is a valid one
186
187 FUNCTION IS_INCIDENT_ID_VALID (p_incident_id IN NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_return_status OUT NOCOPY VARCHAR2
191 ) RETURN VARCHAR2 IS
192
193 CURSOR c_incident IS
194 SELECT 'Y'
195 FROM cs_incidents_all_b
196 WHERE incident_id = p_incident_id;
197
198 lv_exists_flag VARCHAR2(1) := 'N';
199 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_incident_id_valid';
200
201 BEGIN
202
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 OPEN c_incident;
206 FETCH c_incident INTO lv_exists_flag;
207 CLOSE c_incident;
208
209 RETURN lv_exists_flag;
210
211 EXCEPTION
212
213 WHEN OTHERS THEN
214 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
215 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
216 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
217 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
218 FND_MSG_PUB.add;
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 RETURN lv_exists_flag;
221
222 END IS_INCIDENT_ID_VALID;
223
224 --===============================
225 -- IS_ESTIMATE_DETAIL_ID_VALID
226 --===============================
227 --This function checks if the Charge lineid is a valid one
228 FUNCTION IS_ESTIMATE_DETAIL_ID_VALID (p_estimate_detail_id IN NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
232
233 CURSOR c_estimate_detail_id IS
234 SELECT 1
235 FROM CS_ESTIMATE_DETAILS
236 WHERE estimate_detail_id = p_estimate_detail_id;
237
238 lv_exists_flag VARCHAR2(1) := 'N';
239 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_estimate_detail_id_valid';
240
241 BEGIN
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 FOR v_estimate_detail_id IN c_estimate_detail_id
246 LOOP
247 lv_exists_flag := 'Y';
248 END LOOP;
249
250 RETURN lv_exists_flag;
251
252 EXCEPTION
253
254 WHEN OTHERS THEN
255
256 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
257 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
258 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
259 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
260 FND_MSG_PUB.add;
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 RETURN lv_exists_flag;
263
264 END IS_ESTIMATE_DETAIL_ID_VALID;
265
266
267 --===============================
268 -- VALIDATE_SOURCE
269 --===============================
270 --This procedure checks if the Source is valid
271
272 PROCEDURE VALIDATE_SOURCE(p_api_name IN VARCHAR2,
273 p_source_code IN VARCHAR2,
274 p_source_id IN NUMBER,
275 x_source_id OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2) IS
279
280 CURSOR c_val_ch_source(p_source_id IN NUMBER) IS
281 SELECT incident_id
282 FROM CS_INCIDENTS_ALL_B
283 WHERE incident_id =p_source_id;
284
285 CURSOR c_val_dr_source(p_source_id IN NUMBER) IS
286 SELECT repair_line_id
287 FROM CSD_REPAIRS
288 WHERE repair_line_id = p_source_id;
289
290 CURSOR c_val_sd_source(p_source_id IN NUMBER) IS
291 SELECT debrief_line_id
292 FROM csf_debrief_lines
293 WHERE debrief_line_id = p_source_id;
294
295 lv_exists_flag VARCHAR2(1) := 'N';
296 l_ERRM VARCHAR2(100);
297
298 BEGIN
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302
303 -- THe 3 valid source codes are 'SR'(Estimate_Detail_id)
304 -- 'SD'(Debrief_Line_Id)
305 -- 'DR'(Repair_Line_Id)
306 IF p_source_code = 'SR' THEN
307
308 IF p_source_id IS NOT NULL THEN
309 FOR v_val_ch_source IN c_val_ch_source(p_source_id) LOOP
310 lv_exists_flag := 'Y';
311 x_source_id := p_source_id;
312 END LOOP;
313
314 IF lv_exists_flag <> 'Y' THEN
315
316 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_SOURCE');
317 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
318 FND_MESSAGE.SET_TOKEN('SOURCE_ID', p_source_id);
319 FND_MSG_PUB.Add;
320 RAISE FND_API.G_EXC_ERROR;
321
322 END IF;
323
324 ELSE
325 -- source_id cannot be cannot be null
326 Add_Null_Parameter_Msg(p_api_name, 'p_source_id');
327 RAISE FND_API.G_EXC_ERROR;
328 END IF ;
329
330 ELSIF p_source_code = 'DR' THEN
331 IF p_source_id IS NOT NULL THEN
332 FOR v_val_dr_source IN c_val_dr_source(p_source_id) LOOP
333 lv_exists_flag := 'Y';
334 x_source_id := p_source_id;
335 END LOOP;
336
337 IF lv_exists_flag <> 'Y' THEN
338 --RAISE FND_API.G_EXC_ERROR;
339 --null;
340 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_SOURCE');
341 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
342 FND_MESSAGE.SET_TOKEN('SOURCE_ID', p_source_id);
343 FND_MSG_PUB.Add;
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 ELSE
348 -- source_id cannot be cannot be null
349 Add_Null_Parameter_Msg(p_api_name, 'p_source_id');
350 RAISE FND_API.G_EXC_ERROR;
351 END IF ;
352
353 ELSIF p_source_code = 'SD' THEN
354 IF p_source_id IS NOT NULL THEN
355 FOR v_val_dr_source IN c_val_sd_source(p_source_id) LOOP
356 lv_exists_flag := 'Y';
357 x_source_id := p_source_id;
358 END LOOP;
359
360 IF lv_exists_flag <> 'Y' THEN
361 --RAISE FND_API.G_EXC_ERROR;
362 --null;
363 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_SOURCE');
364 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
365 FND_MESSAGE.SET_TOKEN('SOURCE_ID', p_source_id);
366 FND_MSG_PUB.Add;
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 ELSE
371 -- raise error as source_id cannot be cannot be null
372 Add_Null_Parameter_Msg(p_api_name, 'p_source_id');
373 RAISE FND_API.G_EXC_ERROR;
374 END IF ;
375
376
377 ELSE
378 --Invalid source code passed. Raise an exception
379 Add_Invalid_Argument_Msg(
380 p_token_an => p_api_name,
381 p_token_v => p_source_code,
382 p_token_p => 'p_source_code');
383
384 RAISE FND_API.G_EXC_ERROR;
385 END IF ;
386
387 EXCEPTION
388
389 WHEN FND_API.G_EXC_ERROR THEN
390 x_return_status := FND_API.G_RET_STS_ERROR;
391 fnd_msg_pub.count_and_get(p_count => x_msg_count
392 ,p_data => x_msg_data);
393
394 WHEN NO_DATA_FOUND THEN
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 IF p_source_id IS NOT NULL THEN
397 Add_Invalid_Argument_Msg
398 (p_token_an => p_api_name,
399 p_token_v => p_source_id,
400 p_token_p => 'p_source_id');
401 END IF ;
402
403 WHEN OTHERS THEN
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_SOURCE');
406 FND_MESSAGE.SET_TOKEN('SOURCE_CODE', p_source_code);
407 FND_MESSAGE.SET_TOKEN('SOURCE_ID', p_source_id);
408 FND_MSG_PUB.Add;
409 fnd_msg_pub.count_and_get(p_count => x_msg_count
410 ,p_data => x_msg_data);
411
412 END Validate_Source;
413
414 --===============================
415 -- VALIDATE_ORG_ID
416 --===============================
417 --This procedure checks if the Operating Unit is Valid
418
419 PROCEDURE VALIDATE_ORG_ID(p_api_name IN VARCHAR2,
420 p_org_id IN NUMBER,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2) IS
424
425 CURSOR c_org_id IS
426 SELECT organization_id
427 FROM hr_operating_units
428 WHERE organization_id = p_org_id;
429
430 lv_exists_flag VARCHAR2(1) := 'N';
431
432 BEGIN
433
434 FOR v_org_id IN c_org_id
435 LOOP
436 lv_exists_flag := 'Y';
437 END LOOP;
438
439 IF lv_exists_flag = 'Y' THEN
440 x_return_status := FND_API.G_RET_STS_SUCCESS ;
441 ELSE
442 raise NO_DATA_FOUND;
443 END IF;
444
445 EXCEPTION
446
447 WHEN NO_DATA_FOUND THEN
448 Add_Invalid_Argument_Msg(
449 p_token_an => p_api_name,
450 p_token_v => to_char(p_org_id) ,
451 p_token_p => 'p_org_id') ;
452
453 fnd_msg_pub.count_and_get(
454 p_count => x_msg_count
455 ,p_data => x_msg_data);
456
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
458
459 WHEN OTHERS THEN
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461
462 END VALIDATE_ORG_ID;
463
464 --===============================
465 -- IS_ITEM_VALID
466 --===============================
467 --This Function checks if the item exists in the organization passed
468 FUNCTION IS_ITEM_VALID(p_org_id IN NUMBER,
469 p_inventory_item_id IN NUMBER,
470 x_msg_data OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_return_status OUT NOCOPY VARCHAR2
473 ) RETURN VARCHAR2 IS
474
475 CURSOR c_inventory_item_id IS
476 SELECT 1
477 FROM mtl_system_items_b
478 WHERE organization_id =cs_std.get_item_valdn_orgzn_id -- modified by bkanimoz on 21-jan-2007
479 AND inventory_item_id =p_inventory_item_id;
480
481 lv_exists_flag VARCHAR2(1) := 'N';
482 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_item_valid';
483
484 BEGIN
485
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487 FOR v_inventory_item_id IN c_inventory_item_id LOOP
488 lv_exists_flag := 'Y';
489 END LOOP;
490 RETURN lv_exists_flag;
491
492 EXCEPTION
493
494 WHEN OTHERS THEN
495
496 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
497 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
498 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
499 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
500 FND_MSG_PUB.add;
501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
502 RETURN lv_exists_flag;
503
504 END IS_ITEM_VALID;
505
506 --===============================
507 --IS_TXN_INV_ORG_VALID
508 --===============================
509 --This Function checks if the Inventory Org is valid
510
511 FUNCTION IS_TXN_INV_ORG_VALID(p_txn_inv_org IN NUMBER,
512 p_org_id IN NUMBER,
513 x_msg_data OUT NOCOPY VARCHAR2,
514 x_msg_count OUT NOCOPY NUMBER,
515 x_return_status OUT NOCOPY VARCHAR2)RETURN VARCHAR2 IS
516
517 CURSOR c_oper_unit_for_inv_org (p_txn_inv_org number) IS
518 SELECT To_number(hoi2.org_information3) OPERATING_UNIT
519 FROM hr_organization_units hou,
520 hr_organization_information hoi1,
521 hr_organization_information hoi2,
522 mtl_parameters mp
523 WHERE mp.organization_id = p_txn_inv_org
524 AND mp.organization_id = hou.organization_id
525 AND hou.organization_id = hoi1.organization_id
526 AND hoi1.org_information1 = 'INV'
527 AND hoi1.org_information2 = 'Y'
528 AND hoi1.org_information_context = 'CLASS'
529 AND hou.organization_id = hoi2.organization_id
530 AND hoi1.organization_id = hoi2.organization_id
531 AND hoi2.org_information_context = 'Accounting Information';
532
533 lv_exists_flag VARCHAR2(1) := 'N';
534 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_txn_inv_org_valid';
535
536 BEGIN
537
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 FOR v_oper_unit_for_inv_org IN c_oper_unit_for_inv_org (p_txn_inv_org)
541 LOOP
542 IF v_oper_unit_for_inv_org.OPERATING_UNIT = p_org_id THEN
543 lv_exists_flag := 'Y';
544 EXIT;
545 END IF;
546 END LOOP;
547
548 RETURN lv_exists_flag;
549
550 EXCEPTION
551
552 WHEN OTHERS THEN
553
554 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
555 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
556 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
557 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
558 FND_MSG_PUB.add;
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560 RETURN lv_exists_flag;
561 END;
562
563 --===============================
564 --- Get Primary Unit of Measure
565 --===============================
566 --This Procedure gets the Primary UOM for the Item defined in the Item Master window.
567
568 PROCEDURE GET_PRIMARY_UOM(p_inventory_item_id IN NUMBER,
569 p_org_id IN NUMBER,
570 x_primary_uom OUT NOCOPY VARCHAR2,
571 x_msg_data OUT NOCOPY VARCHAR2,
572 x_msg_count OUT NOCOPY NUMBER,
573 x_return_status OUT NOCOPY VARCHAR2
574 ) IS
575
576 CURSOR c_primary_uom(p_inv_id IN NUMBER) IS
577 SELECT mum.uom_code
578 FROM mtl_system_items_b msi,
579 mtl_units_of_measure_tl mum
580 WHERE msi.primary_unit_of_measure = mum.unit_of_measure
581 AND msi.inventory_item_id = p_inventory_item_id
582 AND msi.organization_id = p_org_id;
583
584 lv_exists_flag VARCHAR2(1) := 'N';
585 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'get_primary_uom';
586
587 BEGIN
588
589 -- Initialize API return status to success
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591 FOR v_primary_uom IN c_primary_uom(P_INVentory_item_ID)
592 LOOP
593 lv_exists_flag := 'Y';
594 x_primary_uom := v_primary_uom.uom_code;
595 END LOOP;
596
597 EXCEPTION
598
599 WHEN OTHERS THEN
600
601 FND_MESSAGE.Set_Name('CS', 'CS_CHG_GET_UOM_FAILED');
602 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inventory_item_id);
603 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID', cs_std.get_item_valdn_orgzn_id);
604 FND_MSG_PUB.add;
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
606
607 END GET_PRIMARY_UOM;
608
609 --===============================
610 --- IS_UOM_VALID
611 --===============================
612 --This function checks if the UOM is valid
613
614 FUNCTION IS_UOM_VALID(p_uom_code IN VARCHAR2,
615 p_org_id IN NUMBER,
616 p_inv_id IN NUMBER,
617 x_msg_data OUT NOCOPY VARCHAR2,
618 x_msg_count OUT NOCOPY NUMBER,
619 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
620 CURSOR c_uom_code(p_uom_code IN VARCHAR2,
621 p_inv_id IN NUMBER) IS
622 SELECT uom_code
623 FROM mtl_item_uoms_view
624 WHERE uom_code = p_uom_code
625 AND inventory_item_id = P_INV_ID
626 AND organization_id = p_org_id ;
627
628 lv_exists_flag VARCHAR2(1) := 'N';
629 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_uom_valid';
630
631 BEGIN
632
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 FOR v_uom_code IN c_uom_code(p_uom_code,p_inv_id)
636 LOOP
637 lv_exists_flag := 'Y';
638 END LOOP;
639 RETURN lv_exists_flag;
640
641 EXCEPTION
642
643 WHEN OTHERS THEN
644 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
645 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
646 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
647 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
648 FND_MSG_PUB.add;
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 RETURN lv_exists_flag;
651 END;
652
653 --===============================
654 ---Get Currency Code
655 --===============================
656 --This procedure gets the Functional Currency of the Operating Unit defined in General Ledger
657
658 PROCEDURE GET_CURRENCY_CODE(p_org_id IN NUMBER,
659 x_currency_code OUT NOCOPY VARCHAR2,
660 x_msg_data OUT NOCOPY VARCHAR2,
661 x_msg_count OUT NOCOPY NUMBER,
662 x_return_status OUT NOCOPY VARCHAR2 ) IS
663
664 CURSOR c_currency_code IS
665 SELECT currency_code
666 FROM gl_sets_of_books a,
667 hr_operating_units b
668 WHERE a.NAME = b.NAME
669 AND b.organization_id = p_org_id;
670
671 lv_exists_flag VARCHAR2(1) := 'N';
672 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'get_currency_code';
673
674 BEGIN
675 -- Initialize API return status to success
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 FOR v_currency_code IN c_currency_code
679 LOOP
680 lv_exists_flag := 'Y';
681 x_currency_code := v_currency_code.currency_code ;
682 END LOOP;
683
684 EXCEPTION
685
686 WHEN OTHERS THEN
687
688 FND_MESSAGE.Set_Name('CS', 'CS_COST_GET_CURRENCY_FAILED');
689 FND_MSG_PUB.add;
690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691
692 END GET_CURRENCY_CODE;
693
694 --===============================
695 ---IS_CURRENCY_CODE_VALID
696 --===============================
697 --This Function checks if the passed currency code is valid
698
699 FUNCTION IS_CURRENCY_CODE_VALID (p_currency_code IN VARCHAR2,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 x_msg_count OUT NOCOPY NUMBER,
702 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
703
704 CURSOR c_currency_code IS
705 SELECT currency_code
706 FROM FND_CURRENCIES_TL
707 WHERE currency_code = p_currency_code
708 AND language = Userenv('lang');
709
710 lv_exists_flag VARCHAR2(1) := 'N';
711 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_currency_code_valid';
712
713 BEGIN
714
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716
717 FOR v_currency_code IN c_currency_code
718 LOOP
719 lv_exists_flag := 'Y';
720 END LOOP;
721 RETURN lv_exists_flag;
722
723 EXCEPTION
724
725 WHEN OTHERS THEN
726
727 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
728 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
729 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
730 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
731 FND_MSG_PUB.add;
732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
733 RETURN lv_exists_flag;
734
735 END IS_CURRENCY_CODE_VALID;
736
737 --===============================
738 ---IS_COST_ID_VALID
739 --===============================
740
741 FUNCTION IS_COST_ID_VALID (p_cost_id IN NUMBER,
742 x_msg_data OUT NOCOPY VARCHAR2,
743 x_msg_count OUT NOCOPY NUMBER,
744 x_return_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
745
746 CURSOR c_cost_id IS
747 SELECT 1
748 FROM cs_cost_details
749 WHERE cost_id = p_cost_id;
750
751 lv_exists_flag VARCHAR2(1) := 'N';
752 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'is_cost_id_valid';
753
754 BEGIN
755
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 FOR v_cost_id IN c_cost_id
759 LOOP
760 lv_exists_flag := 'Y';
761 END LOOP;
762
763 RETURN lv_exists_flag;
764
765 EXCEPTION
766
767 WHEN OTHERS THEN
768 FND_MESSAGE.SET_NAME(G_APP_NAME, G_DB_ERROR);
769 FND_MESSAGE.SET_TOKEN(token => G_PROG_NAME_TOKEN, value => l_prog_name);
770 FND_MESSAGE.SET_TOKEN(token => G_SQLCODE_TOKEN, value => SQLCODE);
771 FND_MESSAGE.SET_TOKEN(token => G_SQLERRM_TOKEN, value => SQLERRM);
772 FND_MSG_PUB.add;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 RETURN lv_exists_flag;
775
776 END IS_COST_ID_VALID;
777
778 --===============================
779 ---VALIDATE_BUSINESS_PROCESS
780 --===============================
781 --This procedure just checks if the Business process id is present in cs_bus_process_txns
782
783 PROCEDURE VALIDATE_BUSINESS_PROCESS(p_api_name IN VARCHAR2,
784 p_transaction_type_id IN NUMBER,
785 x_return_status OUT NOCOPY VARCHAR2,
786 x_msg_count OUT NOCOPY NUMBER,
787 x_msg_data OUT NOCOPY VARCHAR2 ) IS
788
789 CURSOR c_business_process IS
790 SELECT '1'
791 FROM cs_bus_process_txns
792 WHERE transaction_type_id = p_transaction_type_id;
793
794 lv_exists_flag VARCHAR2(1) := 'N';
795
796 BEGIN
797 FOR v_business_process IN c_business_process
798 LOOP
799 lv_exists_flag := 'Y';
800 END LOOP;
801
802 IF lv_exists_flag = 'Y' THEN
803 x_return_status := FND_API.G_RET_STS_SUCCESS ;
804 ELSE
805 x_return_status := FND_API.G_RET_STS_ERROR;
806 RAISE NO_DATA_FOUND;
807 END IF;
808
809 EXCEPTION
810
811 WHEN NO_DATA_FOUND THEN
812 x_return_status := FND_API.G_RET_STS_ERROR;
813
814 WHEN OTHERS THEN
815 x_return_status := FND_API.G_RET_STS_ERROR ;
816
817 END VALIDATE_BUSINESS_PROCESS;
818
819 --===============================
820 ---VALIDATE_OPERATING_UNIT
821 --===============================
822 --This procedure checks if the Operating Unit has been set up in the service activity billing types for service activity ID
823
824 PROCEDURE VALIDATE_OPERATING_UNIT(p_api_name IN VARCHAR2,
825 p_txn_billing_type_id IN NUMBER,
826 x_return_status OUT NOCOPY VARCHAR2,
827 x_msg_count OUT NOCOPY NUMBER,
828 x_msg_data OUT NOCOPY VARCHAR2 ) IS
829
830 CURSOR c_operating_unit IS
831 SELECT '1'
832 FROM cs_txn_billing_oetxn_all
833 WHERE txn_billing_type_id = p_txn_billing_type_id;
834
835 lv_exists_flag VARCHAR2(1) := 'N';
836
837 BEGIN
838
839 FOR v_operating_unit IN c_operating_unit
840 LOOP
841 lv_exists_flag := 'Y';
842 END LOOP;
843
844 IF lv_exists_flag = 'Y' THEN
845 x_return_status := FND_API.G_RET_STS_SUCCESS ;
846 ELSE
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 RAISE NO_DATA_FOUND;
849 END IF;
850
851 EXCEPTION
852
853 WHEN NO_DATA_FOUND THEN
854 x_return_status := FND_API.G_RET_STS_ERROR;
855
856 WHEN OTHERS THEN
857 x_return_status := FND_API.G_RET_STS_ERROR ;
858
859 END VALIDATE_OPERATING_UNIT;
860
861 --===============================
862 ---VALIDATE_TRANSACTION_TYPE_ID
863 --===============================
864 --This procedure checks if the SAC is valid
865
866 PROCEDURE VALIDATE_TRANSACTION_TYPE_ID(p_api_name IN VARCHAR2,
867 p_transaction_type_id IN NUMBER,
868 x_line_order_category_code OUT NOCOPY VARCHAR2,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2 ) IS
872
873 CURSOR c_transaction_type_id IS
874 SELECT transaction_type_id,line_order_category_code
875 FROM cs_transaction_types_b
876 WHERE transaction_type_id = p_transaction_type_id;
877
878 lv_exists_flag VARCHAR2(1) := 'N';
879
880 BEGIN
881
882 FOR v_transaction_type_id IN c_transaction_type_id
883 LOOP
884 lv_exists_flag := 'Y';
885 x_line_order_category_code:=v_transaction_type_id.line_order_category_code;
886 END LOOP;
887
888 IF lv_exists_flag = 'Y' THEN
889 x_return_status := FND_API.G_RET_STS_SUCCESS ;
890 ELSE
891 RAISE NO_DATA_FOUND;
892 END IF;
893
894 EXCEPTION
895
896 WHEN NO_DATA_FOUND THEN
897 Add_Invalid_Argument_Msg(
898 p_token_an => p_api_name,
899 p_token_v => to_char(p_transaction_type_id) ,
900 p_token_p => 'p_transaction_type_id') ;
901
902 fnd_msg_pub.count_and_get(
903 p_count => x_msg_count
904 ,p_data => x_msg_data);
905
906 x_return_status := FND_API.G_RET_STS_ERROR ;
907
908 WHEN OTHERS THEN
909 x_return_status := FND_API.G_RET_STS_ERROR ;
910
911 END VALIDATE_transaction_type_id;
912
913 --===============================
914 ---VALIDATE_TRANSACTION_TYPE_ID
915 --===============================
916 -- Added For the Debrief flow
917 PROCEDURE Get_Item_from_Profile(p_transaction_type_id IN NUMBER,
918 p_inv_item_id IN NUMBER,
919 p_no_charge OUT NOCOPY VARCHAR2,
920 x_inv_item_id OUT NOCOPY NUMBER,
921 x_return_status OUT NOCOPY VARCHAR2,
922 x_msg_count OUT NOCOPY NUMBER,
923 x_msg_data OUT NOCOPY VARCHAR2 ) IS
924
925 CURSOR c_check_flag IS
926 SELECT create_charge_flag,create_cost_flag
927 FROM cs_transaction_types_b
928 WHERE transaction_type_id = p_transaction_type_id;
929
930 CURSOR c_get_item_from_profile is
931 select fnd_profile.value('CS_DEFAULT_LABOR_ITEM') from dual;
932
933 lv_exists_flag VARCHAR2(1) := 'N';
934 l_create_charge_flag VARCHAR2(1);
935 l_create_cost_flag VARCHAR2(1);
936
937 BEGIN
938 --For the Debrief Flow
939 /*If the SAC Set up has Create Charge = 'No' and Create Cost ='Yes'
940 and no item is passed from Debrief, then retrieve the inv. item from the profile mentioned below and proceed with the rest of the logic
941 If the profile value is null, and no item is passed by the calling program, then raise an error message and abort the process.
942 */
943 OPEN c_check_flag;
944 FETCH c_check_flag into l_create_charge_flag,l_create_cost_flag;
945 CLOSE c_check_flag;
946
947 IF l_create_charge_flag = 'N' and l_create_cost_flag='Y' and P_INV_ITEM_ID is null
948 THEN
949 p_no_charge :='Y';
950 OPEN c_get_item_from_profile;
951 FETCH c_get_item_from_profile
952 INTO x_inv_item_id;
953
954 IF c_get_item_from_profile%NOTFOUND THEN
955 CLOSE c_get_item_from_profile;
956
957 x_inv_item_id := null;
958 END IF;
959 CLOSE c_get_item_from_profile;
960 else
961 p_no_charge :='N';
962 END IF;
963
964 EXCEPTION
965
966 WHEN OTHERS THEN
967 x_return_status := FND_API.G_RET_STS_ERROR ;
968
969 END Get_Item_from_Profile;
970
971 --===============================
972 ---GET_COST_DETAIL_REC
973 --===============================
974
975 PROCEDURE GET_COST_DETAIL_REC(p_api_name IN VARCHAR2,
976 p_cost_id IN NUMBER,
977 x_cost_detail_rec OUT NOCOPY CS_COST_DETAILS%ROWTYPE ,
978 x_msg_data OUT NOCOPY VARCHAR2,
979 x_msg_count OUT NOCOPY NUMBER,
980 x_return_status OUT NOCOPY VARCHAR2) IS
981 BEGIN
982
983 x_return_status := FND_API.G_RET_STS_SUCCESS ;
984
985 SELECT *
986 INTO x_cost_detail_rec
987 FROM CS_COST_DETAILS
988 WHERE COST_ID = p_cost_id
989 FOR UPDATE OF COST_ID NOWAIT ;
990
991 EXCEPTION
992
993 WHEN NO_DATA_FOUND THEN
994 x_return_status := FND_API.G_RET_STS_ERROR;
995 CS_COST_DETAILS_PVT.Add_Invalid_Argument_Msg(
996 p_token_an => p_api_name,
997 p_token_v => to_char(p_cost_id) ,
998 p_token_p => 'p_cost_id') ;
999 fnd_msg_pub.count_and_get(
1000 p_count => x_msg_count
1001 ,p_data => x_msg_data);
1002
1003 WHEN RECORD_LOCK_EXCEPTION THEN
1004 x_return_status := FND_API.G_RET_STS_ERROR ;
1005 CS_cost_Details_PVT.Record_Is_Locked_Msg(
1006 p_token_an => p_api_name);
1007
1008 WHEN OTHERS THEN
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_COST_FAILED');
1011 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
1012 FND_MSG_PUB.add;
1013 fnd_msg_pub.count_and_get(
1014 p_count => x_msg_count
1015 ,p_data => x_msg_data);
1016
1017 END;
1018
1019 --===============================
1020 ---GET_CHARGE_FLAGS_FROM_SR
1021 --===============================
1022 --This procedure gets the value for the flags
1023 /*1.Disallow Request Update2.Disallow Charge 3.Disallow Charge Update defined in the SR Type Screen
1024 1.Create Charge 2.Create Cost defined in the SAC setup screen
1025 */
1026
1027 PROCEDURE get_charge_flags_from_sr(p_api_name IN VARCHAR2,
1028 p_incident_id IN NUMBER,
1029 p_transaction_type_id IN NUMBER,
1030 x_create_charge_flag OUT NOCOPY VARCHAR2,
1031 x_create_cost_flag OUT NOCOPY VARCHAR2,
1032 x_disallow_request_update OUT NOCOPY VARCHAR2,
1033 x_disallow_new_charge OUT NOCOPY VARCHAR2,
1034 x_disallow_charge_update OUT NOCOPY VARCHAR2,
1035 x_msg_data OUT NOCOPY VARCHAR2,
1036 x_msg_count OUT NOCOPY NUMBER,
1037 x_return_status OUT NOCOPY NUMBER
1038 )IS
1039
1040 CURSOR SAC_FLAGS IS
1041 SELECT NVL(create_charge_flag,'Y') ,
1042 NVL(create_cost_flag,'N')
1043 FROM cs_transaction_types_b
1044 WHERE transaction_type_id = p_transaction_type_id;
1045
1046 cursor c_charge_flags(p_incident_id IN NUMBER) IS
1047 SELECT nvl(csinst.disallow_new_charge, 'N'),
1048 nvl(csinst.disallow_charge_update, 'N'),
1049 nvl(csinst.disallow_request_update,'N') --new check for costing
1050 FROM cs_incident_statuses csinst,
1051 cs_incidents_all csinall
1052 WHERE csinst.incident_status_id = csinall.incident_status_id
1053 AND csinall.incident_id = p_incident_id;
1054
1055 BEGIN
1056
1057 IF p_transaction_type_id is not null and p_transaction_type_id <> fnd_api.g_miss_num
1058 THEN
1059 OPEN sac_flags ;
1060 FETCH sac_flags
1061 INTO x_create_charge_flag ,x_create_cost_flag;
1062 IF sac_flags%NOTFOUND THEN
1063 CLOSE sac_flags;
1064 --Add null argument error
1065 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
1066 p_token_v => TO_CHAR(p_incident_id),
1067 p_token_p => 'p_transaction_type_id');
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070 CLOSE sac_flags;
1071 ELSE
1072 x_create_charge_flag :='Y';
1073 x_create_cost_flag :='Y';
1074 END IF;
1075
1076
1077 OPEN c_charge_flags(p_incident_id);
1078 FETCH c_charge_flags
1079 INTO x_disallow_new_charge, x_disallow_charge_update,x_disallow_request_update;
1080 IF c_charge_flags%NOTFOUND THEN
1081 CLOSE c_charge_flags;
1082 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
1083 p_token_v => TO_CHAR(p_incident_id),
1084 p_token_p => 'p_incident_id');
1085 RAISE FND_API.G_EXC_ERROR;
1086 END IF;
1087 CLOSE c_charge_flags;
1088 END;
1089
1090 --===============================
1091 ---GET_CHARGE_DETAILS
1092 --===============================
1093
1094 PROCEDURE GET_CHARGE_DETAILS(p_api_name IN VARCHAR2,
1095 p_estimate_detail_id IN NUMBER,
1096 x_incident_id OUT NOCOPY NUMBER,
1097 x_transaction_type_id OUT NOCOPY NUMBER,
1098 x_txn_billing_type_id OUT NOCOPY NUMBER,
1099 x_charge_line_type OUT NOCOPY VARCHAR2,
1100 x_inventory_item_id OUT NOCOPY NUMBER,
1101 x_quantity OUT NOCOPY VARCHAR2,
1102 x_unit_of_measure_code OUT NOCOPY VARCHAR2,
1103 x_currency_code OUT NOCOPY VARCHAR2,
1104 x_source_id OUT NOCOPY NUMBER,
1105 x_source_code OUT NOCOPY VARCHAR2,
1106 x_org_id OUT NOCOPY NUMBER,
1107 x_txn_inv_org OUT NOCOPY NUMBER,
1108 x_msg_data OUT NOCOPY VARCHAR2,
1109 x_msg_count OUT NOCOPY NUMBER,
1110 x_return_status OUT NOCOPY VARCHAR2) IS
1111
1112 BEGIN
1113
1114 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1115
1116 SELECT incident_id ,
1117 transaction_type_id ,
1118 txn_billing_type_id ,
1119 charge_line_type,
1120 inventory_item_id,
1121 quantity_required ,
1122 unit_of_measure_code ,
1123 currency_code ,
1124 source_id ,
1125 source_code ,
1126 org_id ,
1127 fnd_profile.value('CS_INV_VALIDATION_ORG') --Bug 7193528
1128 INTO x_incident_id ,
1129 x_transaction_type_id ,
1130 x_txn_billing_type_id ,
1131 x_charge_line_type ,
1132 x_inventory_item_id ,
1133 x_quantity ,
1134 x_unit_of_measure_code ,
1135 x_currency_code ,
1136 x_source_id ,
1137 x_source_code ,
1138 x_org_id ,
1139 x_txn_inv_org
1140 FROM CS_ESTIMATE_DETAILS
1141 WHERE ESTIMATE_DETAIL_ID = p_estimate_detail_id
1142 FOR UPDATE OF ESTIMATE_DETAIL_ID NOWAIT ;
1143
1144 EXCEPTION
1145 WHEN NO_DATA_FOUND THEN
1146 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
1147 p_token_v => to_char(p_estimate_detail_id) ,
1148 p_token_p => 'estimate_detail_id' ) ;
1149 RAISE FND_API.G_EXC_ERROR;
1150
1151 WHEN RECORD_LOCK_EXCEPTION THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR ;
1153 CS_cost_Details_PVT.Record_Is_Locked_Msg(
1154 p_token_an => p_api_name);
1155 RAISE FND_API.G_EXC_ERROR;
1156
1157 WHEN OTHERS THEN
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_GET_CHARGE_FAILED');
1160 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
1161 FND_MSG_PUB.add;
1162 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1163
1164 END Get_charge_details;
1165
1166 --===============================
1167 --- GET_SR_ORG_INFO
1168 --===============================
1169 --This Procedure gets the INV_ORGANIZATION_ID for the SR
1170 --Bug 7193528
1171 PROCEDURE GET_SR_ORG_INFO(p_incident_id IN NUMBER,
1172 x_org_id OUT NOCOPY NUMBER,
1173 x_inv_organization_id OUT NOCOPY NUMBER,
1174 x_msg_data OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_return_status OUT NOCOPY VARCHAR2
1177 ) IS
1178
1179 CURSOR c_get_sr_org(p_incident_id IN NUMBER) IS
1180 SELECT inv_organization_id, org_id
1181 FROM cs_incidents_all_b cia
1182 WHERE cia.incident_id = p_incident_id;
1183
1184 lv_exists_flag VARCHAR2(1) := 'N';
1185 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'GET_SR_ORG_INFO';
1186
1187 BEGIN
1188
1189 -- Initialize API return status to success
1190 x_return_status := FND_API.G_RET_STS_SUCCESS;
1191 FOR v_get_sr_org IN c_get_sr_org(p_incident_id)
1192 LOOP
1193 x_org_id:=v_get_sr_org.org_id;
1194 x_inv_organization_id:= v_get_sr_org.inv_organization_id;
1195 END LOOP;
1196
1197 EXCEPTION
1198
1199 WHEN NO_DATA_FOUND THEN
1200 x_inv_organization_id:= null;
1201 END GET_SR_ORG_INFO;
1202
1203
1204 --===============================
1205 ---Do_Cost_Line_Exist
1206 --===============================
1207 --This Function checks if a record exist in cs_cost_details for the passed cost_id
1208
1209 FUNCTION Do_cost_line_Exist(p_api_name IN VARCHAR2,
1210 p_cost_id IN NUMBER ,
1211 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1212
1213 lv_exists_flag VARCHAR2(1) := 'N';
1214 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'do_cost_line_exist';
1215 l_exist_cost_id NUMBER;
1216
1217 BEGIN
1218
1219 x_return_status := FND_API.G_RET_STS_SUCCESS;
1220
1221 SELECT cost_id
1222 INTO l_exist_cost_id
1223 FROM CS_cost_details
1224 WHERE cost_id =p_cost_id
1225 FOR UPDATE OF COST_ID NOWAIT ;
1226
1227 if l_exist_cost_id is not null then
1228 lv_exists_flag := 'N';
1229 end if;
1230 RETURN lv_exists_flag;
1231
1232 EXCEPTION
1233
1234 WHEN RECORD_LOCK_EXCEPTION THEN
1235
1236 x_return_status := FND_API.G_RET_STS_ERROR ;
1237 CS_cost_Details_PVT.Record_Is_Locked_Msg(
1238 p_token_an => p_api_name);
1239 RAISE FND_API.G_EXC_ERROR;
1240
1241 WHEN OTHERS THEN
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243 RETURN lv_exists_flag;
1244
1245 END Do_Cost_Line_Exist ;
1246
1247 --===============================
1248 ---Do_charge_line_Exist
1249 --===============================
1250 --This Function checks if a record exist in cs_cost_details for the passed cost_id
1251
1252 FUNCTION Do_charge_line_Exist(p_api_name IN VARCHAR2,
1253 p_cost_id IN NUMBER ,
1254 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1255
1256 lv_exists_flag VARCHAR2(1) := 'N';
1257 l_charge_exist number;
1258 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'do_charge_line_exist';
1259
1260 BEGIN
1261
1262 x_return_status := FND_API.G_RET_STS_SUCCESS;
1263
1264 SELECT ced.estimate_detail_id
1265 INTO l_charge_exist
1266 FROM CS_cost_details csd,cs_estimate_details ced
1267 WHERE csd.cost_id =p_cost_id
1268 AND ced.estimate_Detail_id = csd.estimate_Detail_id
1269 FOR UPDATE OF COST_ID NOWAIT ;
1270
1271 if l_charge_exist is not null then
1272 lv_exists_flag := 'Y';
1273 end if;
1274
1275 RETURN lv_exists_flag;
1276
1277 EXCEPTION
1278
1279 WHEN OTHERS THEN
1280 RETURN lv_exists_flag;
1281
1282 END Do_charge_line_Exist;
1283
1284 --===============================
1285 --GET_TXN_BILLING_TYPE
1286 --===============================
1287 --This procedure gets the Billing Type for the Item
1288
1289 PROCEDURE GET_TXN_BILLING_TYPE( p_api_name IN VARCHAR2,
1290 p_inv_id IN NUMBER,
1291 p_txn_type_id IN NUMBER,
1292 x_txn_billing_type_id OUT NOCOPY NUMBER,
1293 x_msg_data OUT NOCOPY VARCHAR2,
1294 x_msg_count OUT NOCOPY NUMBER,
1295 x_return_status OUT NOCOPY VARCHAR2) IS
1296
1297 CURSOR c_txn_billing_type(p_inventory_item_id IN NUMBER,
1298 p_txn_type_id IN NUMBER) IS
1299 SELECT ctbt.txn_billing_type_id
1300 FROM mtl_system_items_kfv kfv,
1301 cs_txn_billing_types ctbt
1302 WHERE kfv.inventory_item_id = p_inventory_item_id
1303 AND organization_id = cs_std.get_item_valdn_orgzn_id
1304 AND ctbt.transaction_type_id = p_txn_type_id
1305 AND ctbt.billing_type = kfv.material_billable_flag;
1306
1307 lv_exists_flag VARCHAR2(1) := 'N';
1308
1309 BEGIN
1310 -- Initialize API return status to success
1311 x_return_status := FND_API.G_RET_STS_SUCCESS;
1312
1313 FOR v_txn_billing_type IN c_txn_billing_type(p_inv_id,p_txn_type_id)
1314 LOOP
1315 x_txn_billing_type_id := v_txn_billing_type.txn_billing_type_id;
1316 lv_exists_flag := 'Y';
1317 END LOOP;
1318
1319 IF lv_exists_flag <> 'Y' THEN
1320 FND_MESSAGE.Set_Name('CS', 'CS_CHG_ITM_BILL_TYP_NOT_IN_TXN');
1321 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inv_id);
1322 FND_MESSAGE.SET_TOKEN('TXN_TYPE_ID', p_txn_type_id);
1323 FND_MSG_PUB.Add;
1324 RAISE FND_API.G_EXC_ERROR;
1325 END IF;
1326
1327 EXCEPTION
1328
1329 WHEN FND_API.G_EXC_ERROR THEN
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331 fnd_msg_pub.count_and_get(
1332 p_count => x_msg_count
1333 ,p_data => x_msg_data);
1334
1335 WHEN OTHERS THEN
1336 x_return_status := FND_API.G_RET_STS_ERROR;
1337 FND_MESSAGE.Set_Name('CS', 'CS_CHG_ITM_BILL_TYP_NOT_IN_TXN');
1338 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inv_id);
1339 FND_MESSAGE.SET_TOKEN('TXN_TYPE_ID', p_txn_type_id);
1340 FND_MSG_PUB.Add;
1341 fnd_msg_pub.count_and_get(
1342 p_count => x_msg_count
1343 ,p_data => x_msg_data);
1344
1345 END GET_TXN_BILLING_TYPE;
1346
1347 /*======================================================================+
1348 ==
1349 == Procedure name : Create_cost_details
1350 == Comments : API to create cost details in cs_cost_details
1351 == Modification History:
1352 ==
1353 == Date Name Desc
1354 == ---------- --------- ---------------------------------------------
1355 == 15-DEC-2007 | BKANIMOZ | Created the procedure
1356 ========================================================================*/
1357
1358 PROCEDURE Create_cost_details
1359 (
1360 p_api_version IN NUMBER,
1361 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1362 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1363 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 x_msg_count OUT NOCOPY NUMBER,
1366 x_object_version_number OUT NOCOPY NUMBER,
1367 x_msg_data OUT NOCOPY VARCHAR2,
1368 x_cost_id OUT NOCOPY NUMBER,
1369 p_resp_appl_id IN NUMBER := FND_GLOBAL.RESP_APPL_ID,
1370 p_resp_id IN NUMBER := FND_GLOBAL.RESP_ID,
1371 p_user_id IN NUMBER := FND_GLOBAL.USER_ID,
1372 p_login_id IN NUMBER := FND_GLOBAL.LOGIN_ID,
1373 p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
1374 p_cost_rec IN CS_Cost_Details_PUB.Cost_Rec_Type,
1375 p_cost_creation_override IN VARCHAR2:='N'
1376 ) IS
1377
1378
1379 l_api_version NUMBER := 1.0 ;
1380 l_api_name CONSTANT VARCHAR2(75) := 'Create_Cost_Details Private API' ;
1381 l_api_name_full CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name ;
1382 l_prog_name CONSTANT VARCHAR2(100) := G_PKG_NAME||'.'||'create_cost_details';
1383 l_log_module CONSTANT VARCHAR2(255) := 'csxvcsts.pls.' || l_api_name_full || '.';
1384
1385 l_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
1386 lx_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
1387
1388 l_valid_check VARCHAR2(1);
1389 l_return_status VARCHAR2(1) ;
1390 l_msg_data VARCHAR2(2000);
1391 l_msg_count NUMBER;
1392 l_errm VARCHAR2(100);
1393 l_cost_id NUMBER;
1394 l_object_version_number NUMBER;
1395
1396 p_cost_group_id NUMBER;
1397 p_cost_type_id NUMBER;
1398
1399 l_unit_cost NUMBER;
1400
1401 l_transaction_type_id NUMBER;
1402 l_charge_line_type VARCHAR2(30);
1403 l_override_ext_cost_flag VARCHAR2(1);
1404 p_estimate_detail_id NUMBER;
1405 lv_cost_id NUMBER;
1406 l_disallow_new_charge VARCHAR2(1);
1407 l_disallow_charge_update VARCHAR2(1);
1408 l_disallow_request_update VARCHAR2(1);
1409 l_create_charge_flag VARCHAR2(1);
1410 l_create_cost_flag VARCHAR2(1);
1411 l_cost_org_id NUMBER;
1412 l_cost_inv_org_id NUMBER;
1413 l_line_order_category_code VARCHAR2(10);
1414 l_org_currency VARCHAR2(10);
1415
1416 CURSOR c_check_cost_exst is
1417 SELECT cost_id
1418 FROM cs_cost_details
1419 WHERE estimate_Detail_id = p_estimate_detail_id;
1420
1421 Cursor get_org_currency is
1422 SELECT gl.currency_code
1423 FROM org_organization_definitions ood,
1424 gl_ledgers gl
1425 WHERE ood.set_of_books_id = gl.LEDGER_ID
1426 AND ood.organization_id = l_cost_inv_org_id;
1427
1428
1429
1430 BEGIN
1431 -- Standard start of API savepoint
1432 IF FND_API.To_Boolean(p_transaction_control) THEN
1433 SAVEPOINT Create_Cost_Details_PVT;
1434 END IF ;
1435 -- Standard call to check for call compatibility
1436 IF NOT FND_API.Compatible_API_Call(l_api_version,
1437 p_api_version,
1438 l_api_name,
1439 G_PKG_NAME) THEN
1440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441 END IF;
1442 -- Initialize message list if p_init_msg_list is set to TRUE
1443 IF FND_API.To_Boolean(p_init_msg_list) THEN
1444 FND_MSG_PUB.Initialize;
1445 END IF;
1446 -- Initialize API return status to success
1447 x_return_status := FND_API.G_RET_STS_SUCCESS;
1448
1449 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1450 THEN
1451 FND_LOG.String
1452 ( FND_LOG.level_procedure ,
1453 L_LOG_MODULE || '',
1454 'Inside Create_cost_details PVT API:'
1455 );
1456 END IF;
1457
1458
1459 ----------------------- FND Logging -----------------------------------
1460 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1461 THEN
1462
1463
1464 FND_LOG.String
1465 ( FND_LOG.level_procedure ,
1466 L_LOG_MODULE || 'start'
1467 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
1468 );
1469 FND_LOG.String
1470 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1471 , 'p_api_version:' || p_api_version
1472 );
1473 FND_LOG.String
1474 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1475 , 'p_init_msg_list:' || p_init_msg_list
1476 );
1477 FND_LOG.String
1478 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1479 , 'p_commit:' || p_commit
1480 );
1481 FND_LOG.String
1482 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1483 , 'p_validation_level:' || p_validation_level
1484 );
1485 FND_LOG.String
1486 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1487 , 'p_resp_appl_id:' || p_resp_appl_id
1488 );
1489 FND_LOG.String
1490 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1491 , 'p_resp_id:' || p_resp_id
1492 );
1493 FND_LOG.String
1494 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1495 , 'p_user_id:' || p_user_id
1496 );
1497 FND_LOG.String
1498 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1499 , 'p_login_id:' || p_login_id
1500 );
1501 FND_LOG.String
1502 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1503 , 'p_transaction_control:' || p_transaction_control
1504 );
1505 FND_LOG.String
1506 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1507 , 'p_cost_creation_override: ' || p_cost_creation_override
1508 );
1509 -- --------------------------------------------------------------------------
1510 -- This procedure Logs the charges record paramters.
1511 -- --------------------------------------------------------------------------
1512 CS_COST_DETAILS_PUB.Log_Cost_Rec_Parameters
1513 ( p_cost_Rec_in => p_cost_rec
1514 );
1515
1516 END IF;
1517
1518 --Convert the IN Parameters from FND_API.G_MISS_XXXX to NULL
1519 --if no value is passed then return NULL otherwise return the value passed
1520
1521 TO_NULL (p_cost_Rec, l_cost_Rec) ;
1522
1523 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1524 THEN
1525 FND_LOG.String
1526 ( FND_LOG.level_procedure ,
1527 L_LOG_MODULE || '',
1528 'l_cost_Rec.estimate_Detail_id : '||l_cost_Rec.estimate_Detail_id
1529 );
1530 END IF;
1531
1532 -- If estimate_Detail_Id is passed to the Create Cost Details API, then get all the data necessry from the charges table .
1533 -- In this case , if any other parameters are passed then they will not be validated
1534
1535 -- If estimate_detail_id is not passed then validate all the parameters passed
1536
1537 if l_cost_Rec.estimate_Detail_id is not null then
1538
1539 p_estimate_detail_id :=l_cost_Rec.estimate_Detail_id;
1540
1541 OPEN c_check_cost_exst;
1542 FETCH c_check_cost_exst into lv_cost_id;
1543 CLOSE c_check_cost_exst;
1544
1545 if lv_cost_id is not null then
1546 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CHARGE_EXIST');
1547 FND_MSG_PUB.ADD;
1548 RAISE FND_API.G_EXC_ERROR;
1549 end if;
1550
1551 --call get_charge_details to get the data from cs_estimate_details table
1552 get_charge_details
1553 (
1554 p_api_name => l_api_name_full,
1555 p_estimate_detail_id => l_cost_Rec.estimate_detail_id,
1556 x_incident_id => l_cost_rec.incident_id,
1557 x_transaction_type_id => l_cost_rec.transaction_type_id ,
1558 x_txn_billing_type_id => l_cost_rec.txn_billing_type_id,
1559 x_charge_line_type => l_cost_rec.charge_line_type ,
1560 x_inventory_item_id => l_cost_rec.inventory_item_id ,
1561 x_quantity => l_cost_rec.quantity ,
1562 x_unit_of_measure_code => l_cost_rec.unit_of_measure_code ,
1563 x_currency_code => l_cost_rec.currency_code ,
1564 x_source_id => l_cost_rec.source_id ,
1565 x_source_code => l_cost_rec.source_code ,
1566 x_org_id => l_cost_rec.org_id ,
1567 x_txn_inv_org => l_cost_rec.inventory_org_id,
1568 x_msg_data => x_msg_data,
1569 x_msg_count => x_msg_count,
1570 x_return_status => x_return_status
1571 );
1572
1573 lx_cost_rec:=l_cost_rec;
1574 --Bug 6972425
1575 --start
1576 get_charge_flags_from_sr
1577 (
1578 p_api_name => l_api_name,
1579 p_incident_id => l_cost_rec.incident_id,
1580 p_transaction_type_id => l_cost_rec.transaction_type_id,
1581 x_create_charge_flag => l_create_charge_flag,
1582 x_create_cost_flag => l_create_cost_flag,
1583 x_disallow_request_update => l_disallow_request_update,
1584 x_disallow_new_charge => l_disallow_new_charge,
1585 x_disallow_charge_update => l_disallow_charge_update,
1586 x_msg_data => l_msg_data,
1587 x_msg_count => l_msg_count,
1588 x_return_status => l_return_status
1589 );
1590
1591 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1592 THEN
1593 FND_LOG.String
1594 ( FND_LOG.level_procedure ,
1595 L_LOG_MODULE || '',
1596 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
1597 );
1598
1599 FND_LOG.String
1600 ( FND_LOG.level_procedure ,
1601 L_LOG_MODULE || '',
1602 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
1603 'l_disallow_charge_update: '||l_disallow_charge_update
1604 );
1605 END IF;
1606
1607 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
1608 if l_disallow_request_update='Y' THEN
1609 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
1610 FND_MSG_PUB.ADD;
1611 RAISE FND_API.G_EXC_ERROR;
1612 end if;
1613
1614 Else
1615 if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
1616 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
1617 FND_MSG_PUB.ADD;
1618 RAISE FND_API.G_EXC_ERROR;
1619 end if;
1620 end if;
1621 --end Bug 6972425
1622
1623 end if;--estimate_detail_id not null
1624
1625 l_transaction_type_id := l_cost_rec.transaction_type_id;
1626 l_charge_line_type := l_cost_rec.charge_line_type;
1627
1628 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1629 THEN
1630 FND_LOG.String
1631 ( FND_LOG.level_procedure ,
1632 L_LOG_MODULE || '',
1633 'l_transaction_type_id : '||l_transaction_type_id ||'l_charge_line_type :'||l_charge_line_type
1634 );
1635 END IF;
1636
1637 /* 1. Transaction_Type_Id
1638 Check if 'Create_Cost_Flag' is set for this transaction_Type
1639 */
1640 --if parameter p_cost_creation_override ='Y' then do not check for the Create_Cost flag in SAC setup
1641
1642 IF p_cost_creation_override = 'N' THEN
1643 if l_transaction_type_id is not null then
1644
1645 -- If transaction type id is passed , first check if it is valid.
1646 -- Then Check if the 'Create_Cost_Flag' for this transaction_type_id is checked
1647 -- Then Check if this transaction_type_id is tied to atleast one business process -- check this with rohit
1648
1649 VALIDATE_TRANSACTION_TYPE_ID (
1650 p_api_name => l_api_name_full,
1651 p_transaction_type_id => l_transaction_type_id,
1652 x_line_order_category_code=>l_line_order_category_code,
1653 x_msg_data => l_msg_data,
1654 x_msg_count => l_msg_count,
1655 x_return_status => l_return_status
1656 ) ;
1657
1658 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1659 RAISE FND_API.G_EXC_ERROR ;
1660 Else
1661 l_valid_check := IS_COST_FLAG_CHECKED
1662 (p_transaction_type_id => l_transaction_type_id,
1663 x_msg_data => l_msg_data,
1664 x_msg_count => l_msg_count,
1665 x_return_status => l_return_status
1666 );
1667
1668 if l_valid_check ='Y' then
1669 l_cost_rec.transaction_type_id := l_transaction_type_id;
1670
1671 VALIDATE_BUSINESS_PROCESS (
1672 p_api_name => l_api_name_full,
1673 p_transaction_type_id => l_transaction_type_id,
1674 x_msg_data => l_msg_data,
1675 x_msg_count => l_msg_count,
1676 x_return_status => l_return_status
1677 ) ;
1678
1679 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1680 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_BUS_PROCESS');
1681 FND_MSG_PUB.ADD;
1682 RAISE FND_API.G_EXC_ERROR;
1683 END IF;
1684 Else
1685 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_COST_FLAG');
1686 FND_MSG_PUB.ADD;
1687 RAISE G_WARNING;
1688 end if;
1689 end if;
1690 else -- transaction_type_id is null
1691 Add_Null_Parameter_Msg(l_api_name,
1692 'p_transaction_type_id') ;
1693 RAISE FND_API.G_EXC_ERROR;
1694 end if;
1695 END IF;--p_cost_creation_overrid
1696
1697
1698
1699 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1700 THEN
1701 FND_LOG.String
1702 ( FND_LOG.level_procedure ,
1703 L_LOG_MODULE || '',
1704 'After Mandatory Check 1'
1705 );
1706 END IF;
1707
1708 /*2.Cost Records will be created only for ACTUAL charge lines.
1709 if charge_line_type is passed the value should be 'ACTUAL'
1710 if not passed then default it to 'ACTUAL'*/
1711
1712 if l_charge_line_type is not null then
1713 l_valid_check := IS_CHARGE_LINE_TYPE_VALID
1714 (p_charge_line_type => l_charge_line_type,
1715 x_msg_data => l_msg_data,
1716 x_msg_count => l_msg_count,
1717 x_return_status => l_return_status
1718 );
1719
1720 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1722 ELSIF l_return_status = G_RET_STS_ERROR THEN
1723 RAISE FND_API.G_EXC_ERROR;
1724 END IF;
1725
1726 if l_valid_check ='Y' then
1727 l_cost_rec.charge_line_type := l_charge_line_type;
1728 else --throw the error message and stop processing
1729 Add_Invalid_Argument_Msg(l_api_name,
1730 l_charge_line_type,
1731 'charge_line_type');
1732
1733 RAISE G_WARNING;
1734 end if;
1735 else
1736 l_cost_rec.charge_line_type := 'ACTUAL';
1737 end if;
1738
1739 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1740 THEN
1741 FND_LOG.String
1742 ( FND_LOG.level_procedure ,
1743 L_LOG_MODULE || '',
1744 'After Mandatory Check 2'
1745 );
1746 END IF;
1747 --------------------------------------------------------------------------------------------------------------------------
1748 -- All the Other Validations would be performed only when the Validation_level is FULL .
1749
1750
1751 IF (p_validation_level = FND_API.G_VALID_LEVEL_NONE) THEN
1752
1753
1754 Validate_Who_Info ( p_api_name => l_api_name_full,
1755 p_user_id => NVL(p_user_id, -1),
1756 p_login_id => p_login_id,
1757 x_return_status => l_return_status
1758 );
1759
1760 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1761 RAISE FND_API.G_EXC_ERROR;
1762 END IF;
1763 L_RECALC_COST := 'Y';
1764
1765
1766 ELSIF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
1767
1768
1769 -- Perform all the validations by calling the procedure VALIDATE_COST_DETAILS which inturn does all the validations
1770
1771 if l_cost_Rec.estimate_Detail_id is null then
1772
1773 VALIDATE_COST_DETAILS(
1774 p_api_name => l_api_name,
1775 pv_cost_rec => l_cost_rec,
1776 p_validation_mode => 'I' ,
1777 p_user_id => p_user_id,
1778 p_login_id => p_login_id,
1779 x_cost_rec => lx_cost_rec,
1780 x_msg_data => x_msg_data,
1781 x_msg_count => x_msg_count,
1782 x_return_status => l_return_status
1783 );
1784
1785 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1786 FND_MESSAGE.Set_Name('CS', 'CS_COST_VALIDATE_COST_DTL_ER');
1787 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
1788 FND_MSG_PUB.Add;
1789 RAISE FND_API.G_EXC_ERROR;
1790 END IF;
1791 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1792 THEN
1793 FND_LOG.String
1794 ( FND_LOG.level_procedure ,
1795 L_LOG_MODULE || '',
1796 'After Validating the Cost Details'
1797 );
1798 END IF;
1799
1800 end if;--validation_level
1801 END IF;--p_cost_rec.estimate_Detail_id is not null
1802
1803 --If the Costing API is called from the backend with a SAC of RETURN type then the user can
1804 -- pass a negative Extened Cost.
1805 /*
1806 if sign(lx_cost_rec.extended_cost)=-1
1807 then
1808
1809 Add_Invalid_Argument_Msg(l_api_name_full,
1810 to_char(lx_cost_rec.extended_cost),
1811 'Extended Cost ');
1812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1813 end if;
1814 */
1815 --===============================
1816 ---Quantity validations
1817 --===============================
1818 if sign(lx_cost_rec.quantity) = (0)
1819 then
1820
1821 Add_Invalid_Argument_Msg(l_api_name_full,
1822 to_char(lx_cost_rec.quantity),
1823 'Quantity ');
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825
1826 end if;
1827
1828 --IF Quantity is not passed then Default it to 1
1829 IF lx_cost_rec.quantity IS NULL THEN
1830 lx_cost_rec.quantity:=1;
1831 END IF;
1832
1833 -- For a Return Transaction the Quantity should be Negative
1834 IF (l_line_order_category_code = 'RETURN') then
1835 if lx_cost_rec.quantity is not null
1836 then
1837 if sign(lx_cost_rec.quantity) = -1 then
1838 lx_cost_rec.quantity := lx_cost_rec.quantity;
1839 else
1840 --assign -ve qty to out record
1841 lx_cost_rec.quantity := (lx_cost_rec.quantity * -1);
1842 end if;
1843 end if;
1844 Else
1845 -- For a Order Transaction
1846 if lx_cost_rec.quantity is not null then
1847 if sign(lx_cost_rec.quantity ) = -1 then
1848 -- need to make this positive as no -ve quantity for orders
1849 lx_cost_rec.quantity := (lx_cost_rec.quantity * -1);
1850 else
1851 lx_cost_rec.quantity := lx_cost_rec.quantity ;
1852 end if;
1853 end if;
1854 End if;
1855
1856 --Bug 7193528
1857
1858 /*1. If Cost Creation is attempted for an SAC with Create_Charge Flag checked
1859 then Use the Value set in the profile "Service :Inventory Validation
1860 Org" to retrieve the Item's Unit Cost
1861 2. If Cost Creation is attempted for an SAC with Create_Charge Flag UnChecked
1862 then Use the Service Request Inventory Org to fetch the Item's Unit Cost.
1863 The Inventory_org_id in CS_COST_DETAILS should be the org from which the Item
1864 Cost is fetched.
1865 The same logic holds good for getting the ORG_ID(operating Unit) in
1866 CS_COST_DETAILS
1867 1.IF Cost Creation is attempted for an exising Charge Line then store the
1868 Charge Line's Operating unit in CS_COST_DETAILS.org_id column
1869 2.If Cost Creation is attempted for the SR, then store the SR's Operating
1870 Unit in the Org Id column of the Cost table
1871 */
1872
1873 if l_create_charge_flag ='N' then
1874 --l_cost_org_id := p_cost_rec.inventory_org_id;
1875
1876 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
1877 x_org_id => l_cost_org_id,
1878 x_inv_organization_id => l_cost_inv_org_id ,
1879 x_msg_data => x_msg_data,
1880 x_msg_count => x_msg_count,
1881 x_return_status => l_return_status
1882 );
1883
1884 else
1885
1886 l_cost_inv_org_id :=lx_cost_rec.inventory_org_id;
1887 l_cost_org_id :=lx_cost_rec.org_id;
1888
1889 if l_cost_Rec.estimate_Detail_id is null then
1890
1891 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
1892 x_org_id => l_cost_org_id,
1893 x_inv_organization_id => l_cost_inv_org_id ,
1894 x_msg_data => x_msg_data,
1895 x_msg_count => x_msg_count,
1896 x_return_status => l_return_status
1897 );
1898 end if;
1899
1900 end if;
1901
1902 --===============================
1903 ---Item Cost
1904 --===============================
1905
1906
1907 -- for 12.2.2 service projects integration
1908 If l_cost_inv_org_id is null then
1909 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_NO_INV_ORG');
1910 FND_MSG_PUB.ADD;
1911 Raise FND_API.G_EXC_ERROR;
1912 End If;
1913 --end;
1914
1915
1916 OPEN get_org_currency;
1917 FETCH get_org_currency INTO l_org_currency;
1918 --EXIT WHEN get_org_currency%NOTFOUND;
1919 CLOSE get_org_currency;
1920
1921
1922 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1923 THEN
1924 FND_LOG.String
1925 ( FND_LOG.level_procedure ,
1926 L_LOG_MODULE || '',
1927 'Before calling CST_COST_API.Get_Item_Cost '||'p_inventory_item_id :'||lx_cost_rec.inventory_item_id||'p_organization_id :'||l_cost_inv_org_id
1928 );
1929 END IF;
1930
1931
1932 l_unit_cost := CST_COST_API.Get_Item_Cost
1933 (
1934 p_api_version => 1.0,
1935 p_inventory_item_id => lx_cost_rec.inventory_item_id,
1936 p_organization_id => l_cost_inv_org_id,
1937 p_cost_group_id => p_cost_group_id,
1938 p_cost_type_id => p_cost_type_id
1939 );
1940
1941 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1942 THEN
1943 FND_LOG.String
1944 ( FND_LOG.level_procedure ,
1945 L_LOG_MODULE || '',
1946 'After calling CST_COST_API.Get_Item_Cost '||'l_unit_cost : '||l_unit_cost
1947 );
1948 END IF;
1949
1950 --Calculate the Item's Extended Cost
1951 -- If extended cost is passed then make the unit cost and quantity column to NULL and set the flag to 'Y'
1952 -- IF extended cost is not passed then calculate it as below and set the flag to 'N'
1953
1954 If lx_cost_rec.extended_cost IS NULL THEN
1955 lx_cost_rec.extended_cost :=l_unit_cost*lx_cost_rec.quantity;
1956 l_override_ext_cost_flag :='N';
1957 Else
1958 --l_unit_cost := null;
1959 --lx_cost_rec.quantity := null;
1960 l_override_ext_cost_flag :='Y';
1961 IF (l_line_order_category_code = 'RETURN') then
1962 if sign(lx_cost_rec.extended_cost) = -1 then
1963 lx_cost_rec.extended_cost := lx_cost_rec.extended_cost;
1964 else
1965 --assign -ve qty to out record
1966 lx_cost_rec.extended_cost := (lx_cost_rec.extended_cost * -1);
1967 end if;
1968 END IF;
1969
1970 END IF;
1971
1972 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1973 THEN
1974 FND_LOG.String
1975 ( FND_LOG.level_procedure ,
1976 L_LOG_MODULE || '',
1977 'Before calling the Insert_Row procedure'
1978 );
1979 END IF;
1980
1981 --This prcoedure will insert data into cs_cost_details table
1982
1983 CS_COST_DETAILS_PKG.Insert_Row
1984 (
1985 x_cost_id =>l_cost_id ,
1986 p_incident_id =>lx_cost_rec.incident_id ,
1987 p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
1988 p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
1989 p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
1990 p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
1991 p_quantity =>lx_cost_rec.quantity ,
1992 p_unit_cost =>l_unit_cost ,
1993 p_extended_cost =>lx_cost_rec.extended_cost ,
1994 p_override_ext_cost_flag =>l_override_ext_cost_flag ,
1995 p_transaction_date => sysdate ,
1996 p_source_id =>lx_cost_rec.source_id ,
1997 p_source_code =>lx_cost_rec.source_code ,
1998 p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
1999 p_currency_code =>l_org_currency , -- 12.2.2 service projects integration
2000 p_org_id =>l_cost_org_id ,
2001 p_inventory_org_id =>l_cost_inv_org_id ,
2002 p_attribute1 =>lx_cost_rec.attribute1 ,
2003 p_attribute2 =>lx_cost_rec.attribute2 ,
2004 p_attribute3 =>lx_cost_rec.attribute3 ,
2005 p_attribute4 =>lx_cost_rec.attribute4 ,
2006 p_attribute5 =>lx_cost_rec.attribute5 ,
2007 p_attribute6 =>lx_cost_rec.attribute6 ,
2008 p_attribute7 =>lx_cost_rec.attribute7 ,
2009 p_attribute8 =>lx_cost_rec.attribute8 ,
2010 p_attribute9 =>lx_cost_rec.attribute9 ,
2011 p_attribute10 =>lx_cost_rec.attribute10 ,
2012 p_attribute11 =>lx_cost_rec.attribute11 ,
2013 p_attribute12 =>lx_cost_rec.attribute12 ,
2014 p_attribute13 =>lx_cost_rec.attribute13 ,
2015 p_attribute14 =>lx_cost_rec.attribute14 ,
2016 p_attribute15 =>lx_cost_rec.attribute15 ,
2017 p_last_update_date => sysdate ,
2018 p_last_updated_by => FND_GLOBAL.USER_ID ,
2019 p_last_update_login => FND_GLOBAL.LOGIN_ID ,
2020 p_created_by => FND_GLOBAL.USER_ID ,
2021 p_creation_date => sysdate ,
2022 x_object_version_number => l_object_version_number
2023 );
2024
2025 x_cost_id :=l_cost_id;
2026
2027 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2028 THEN
2029 FND_LOG.String
2030 ( FND_LOG.level_procedure ,
2031 L_LOG_MODULE || '',
2032 'After calling the Insert Row '
2033 );
2034 END IF;
2035
2036 FND_MSG_PUB.Count_And_Get
2037 ( p_count => x_msg_count,
2038 p_data => x_msg_data,
2039 p_encoded => FND_API.G_FALSE) ;
2040
2041 EXCEPTION
2042
2043 WHEN FND_API.G_EXC_ERROR THEN
2044 x_return_status := FND_API.G_RET_STS_ERROR;
2045 IF FND_API.To_Boolean(p_transaction_control)
2046 THEN
2047 ROLLBACK TO Create_Cost_Details_PVT;
2048 END IF ;
2049 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2050 p_data => x_msg_data,
2051 p_encoded => FND_API.G_FALSE) ;
2052
2053 WHEN G_WARNING THEN
2054 x_return_status := FND_API.G_RET_STS_SUCCESS;
2055 IF FND_API.To_Boolean(p_transaction_control)
2056 THEN
2057 ROLLBACK TO Create_Cost_Details_PVT;
2058 END IF ;
2059 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2060 p_data => x_msg_data,
2061 p_encoded => FND_API.G_FALSE) ;
2062
2063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2064 IF FND_API.To_Boolean(p_transaction_control) THEN
2065 ROLLBACK TO Create_Cost_Details_PVT;
2066 END IF ;
2067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2068 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2069 p_data => x_msg_data,
2070 p_encoded => FND_API.G_FALSE) ;
2071
2072 WHEN OTHERS THEN
2073 IF FND_API.To_Boolean(p_transaction_control) THEN
2074 ROLLBACK TO Create_Cost_Details_PVT;
2075 END IF ;
2076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2077 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2078 THEN
2079 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2080 END IF;
2081 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2082 p_data => x_msg_data,
2083 p_encoded => FND_API.G_FALSE) ;
2084
2085 END create_cost_details;
2086
2087 /*======================================================================+
2088 ==
2089 == Procedure name : Create_cost_details
2090 == Comments : API to Update cost details in cs_cost_details
2091 == Modification History:
2092 ==
2093 == Date Name Desc
2094 == ---------- --------- ---------------------------------------------
2095 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2096 ========================================================================*/
2097
2098 PROCEDURE Update_Cost_Details
2099 (
2100 p_api_version IN NUMBER,
2101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2102 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2103 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2104 x_return_status OUT NOCOPY VARCHAR2,
2105 x_msg_count OUT NOCOPY NUMBER,
2106 x_object_version_number OUT NOCOPY NUMBER,
2107 x_msg_data OUT NOCOPY VARCHAR2,
2108 p_resp_appl_id IN NUMBER := FND_GLOBAL.RESP_APPL_ID,
2109 p_resp_id IN NUMBER := FND_GLOBAL.RESP_ID,
2110 p_user_id IN NUMBER := FND_GLOBAL.USER_ID,
2111 p_login_id IN NUMBER :=FND_GLOBAL.LOGIN_ID,
2112 p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
2113 p_Cost_Rec IN CS_Cost_Details_PUB.Cost_Rec_Type
2114
2115 ) IS
2116
2117 l_api_version NUMBER := 1.0 ;
2118 l_api_name VARCHAR2(100) := 'Update_Cost_Details' ;
2119 l_api_name_full VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name ;
2120 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.pls.' || l_api_name_full || '.';
2121 l_return_status VARCHAR2(1) ;
2122 l_org_id NUMBER ;
2123 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_cost_details';
2124 l_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
2125 lx_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
2126 l_valid_check VARCHAR2(1);
2127
2128 l_msg_data VARCHAR2(2000);
2129 l_msg_count NUMBER;
2130 l_object_version_number NUMBER;
2131 l_errm VARCHAR2(100);
2132 l_cost_id NUMBER;
2133 l_transaction_type_id NUMBER;
2134 l_charge_line_type VARCHAR2(30);
2135
2136 l_unit_cost NUMBER;
2137 p_cost_group_id NUMBER;
2138 p_cost_type_id NUMBER;
2139
2140 l_override_ext_cost_flag VARCHAR2(1);
2141 l_quantity NUMBER;
2142 lt_estimate_detail_id NUMBER;
2143 v_estimate_detail_id NUMBER;
2144
2145 p_cost_id NUMBER;
2146 v_unit_cost NUMBER;
2147 v_extended_cost NUMBER;
2148 v_override_ext_cost_flag VARCHAR2(1);
2149
2150 p_estimate_detail_id NUMBER;
2151 lv_cost_id NUMBER;
2152 l_disallow_new_charge VARCHAR2(1);
2153 l_disallow_charge_update VARCHAR2(1);
2154 l_disallow_request_update VARCHAR2(1);
2155 l_create_charge_flag VARCHAR2(1);
2156 l_create_cost_flag VARCHAR2(1);
2157 l_cost_org_id NUMBER;
2158 l_cost_inv_org_id NUMBER;
2159 l_org_currency VARCHAR2(10);
2160
2161 CURSOR get_flag IS
2162 SELECT unit_cost,
2163 extended_cost,
2164 override_ext_cost_flag
2165 FROM cs_cost_details
2166 WHERE cost_id = p_cost_id;
2167
2168 CURSOR c_check_cost_exst IS
2169 SELECT cost_id
2170 FROM cs_cost_details
2171 WHERE estimate_Detail_id = p_estimate_detail_id;
2172
2173
2174 CURSOR get_org_currency is
2175 SELECT gl.currency_code
2176 FROM org_organization_definitions ood,
2177 gl_ledgers gl
2178 WHERE ood.set_of_books_id = gl.LEDGER_ID
2179 AND ood.organization_id = l_cost_inv_org_id;
2180
2181
2182 BEGIN
2183 -- Standard start of API savepoint
2184 IF FND_API.To_Boolean(p_transaction_control) THEN
2185 SAVEPOINT Update_Cost_Details_PVT;
2186 END IF ;
2187 -- Standard call to check for call compatibility
2188 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2189 THEN
2190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2191 END IF;
2192 -- Initialize message list if p_init_msg_list is set to TRUE
2193 IF FND_API.To_Boolean(p_init_msg_list) THEN
2194 FND_MSG_PUB.Initialize;
2195 END IF;
2196 -- Initialize API return status to success
2197 x_return_status := FND_API.G_RET_STS_SUCCESS;
2198
2199 ----------------------- FND Logging -----------------------------------
2200 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2201 THEN
2202 FND_LOG.String
2203 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
2204 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2205 );
2206 FND_LOG.String
2207 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2208 , 'p_api_version:' || p_api_version
2209 );
2210 FND_LOG.String
2211 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2212 , 'p_init_msg_list:' || p_init_msg_list
2213 );
2214 FND_LOG.String
2215 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2216 , 'p_commit:' || p_commit
2217 );
2218 FND_LOG.String
2219 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2220 , 'p_validation_level:' || p_validation_level
2221 );
2222 FND_LOG.String
2223 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2224 , 'p_resp_appl_id:' || p_resp_appl_id
2225 );
2226 FND_LOG.String
2227 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2228 , 'p_resp_id:' || p_resp_id
2229 );
2230 FND_LOG.String
2231 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2232 , 'p_user_id:' || p_user_id
2233 );
2234 FND_LOG.String
2235 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2236 , 'p_login_id:' || p_login_id
2237 );
2238 FND_LOG.String
2239 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2240 , 'p_transaction_control:' || p_transaction_control
2241 );
2242 -- --------------------------------------------------------------------------
2243 -- This procedure Logs the charges record paramters.
2244 -- --------------------------------------------------------------------------
2245 CS_COST_DETAILS_PUB.Log_Cost_Rec_Parameters
2246 ( p_cost_Rec_in => p_cost_rec
2247 );
2248
2249 END IF;
2250
2251 l_cost_rec:=p_cost_rec;
2252
2253 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2254 THEN
2255 FND_LOG.String
2256 ( FND_LOG.level_procedure ,
2257 L_LOG_MODULE || '',
2258 'p_cost_rec.estimate_Detail_id : '||p_cost_rec.estimate_Detail_id
2259 );
2260 END IF;
2261
2262 lt_estimate_detail_id :=p_cost_rec.estimate_Detail_id ;
2263
2264
2265 /*
2266 If there is a estimate_Detail_id for the cost_id passed , then get all the details from the estimate_Detail_id
2267 and pass it to the cost update package. This is to make sure that the cost level details are in sync with charge line details.
2268
2269 For example say a Charge line has been Created for 10 Quantities of an Item.
2270 Cost Line is also generated for this 10 quantity.
2271 If while calling the Update Cost API , say the Quantity is passed as 20 for this cost line,
2272 the cost API will not update the cost line with 20 quantities .
2273 But if the Update Cost API is called with Extended Cost , then the Cost API will update the Quantity and Unit Cost
2274 to NULL and extended cost to the value passed.
2275 */
2276
2277
2278 IF p_cost_rec.cost_id is not null and p_cost_rec.cost_id <> fnd_api.g_miss_num
2279 THEN
2280
2281 begin
2282
2283 select estimate_detail_id
2284 into v_estimate_detail_id
2285 from cs_cost_details csd
2286 where cost_id =p_cost_rec.cost_id;
2287
2288 if v_estimate_detail_id is not null then
2289 lt_estimate_detail_id:=v_estimate_detail_id;
2290 end if;
2291
2292 exception
2293 when no_data_found then
2294 null;--dbms_output.put_line('Costing1');
2295 when others then
2296 null;
2297 end;
2298
2299 END IF;
2300
2301
2302 IF lt_estimate_detail_id IS NOT NULL
2303 AND lt_estimate_detail_id<> fnd_api.g_miss_num
2304 THEN
2305
2306 p_estimate_Detail_id := lt_estimate_detail_id;
2307
2308 OPEN c_check_cost_exst;
2309
2310 FETCH c_check_cost_exst into lv_cost_id;
2311
2312 CLOSE c_check_cost_exst;
2313
2314 if lv_cost_id is null then
2315 FND_MESSAGE.SET_NAME('CS', 'CS_COST_NO_CHARGE_EXIST');
2316 FND_MSG_PUB.ADD;
2317 RAISE G_WARNING;
2318 end if;
2319
2320 GET_CHARGE_DETAILS
2321 (
2322 p_api_name => l_api_name_full,
2323 p_estimate_detail_id => lt_estimate_detail_id,
2324 x_incident_id => l_cost_rec.incident_id,
2325 x_transaction_type_id => l_cost_rec.transaction_type_id ,
2326 x_txn_billing_type_id => l_cost_rec.txn_billing_type_id,
2327 x_charge_line_type => l_cost_rec.charge_line_type ,
2328 x_inventory_item_id => l_cost_rec.inventory_item_id ,
2329 x_quantity => l_cost_rec.quantity ,
2330 x_unit_of_measure_code => l_cost_rec.unit_of_measure_code ,
2331 x_currency_code => l_cost_rec.currency_code ,
2332 x_source_id => l_cost_rec.source_id ,
2333 x_source_code => l_cost_rec.source_code ,
2334 x_org_id => l_cost_rec.org_id ,
2335 x_txn_inv_org => l_cost_rec.inventory_org_id,
2336 x_msg_data => x_msg_data,
2337 x_msg_count => x_msg_count,
2338 x_return_status => x_return_status
2339 );
2340
2341 --Bug fix for 6972425
2342 --start
2343 get_charge_flags_from_sr
2344 (
2345 p_api_name => l_api_name_full,
2346 p_incident_id => l_cost_rec.incident_id,
2347 p_transaction_type_id => l_cost_rec.transaction_type_id,
2348 x_create_charge_flag => l_create_charge_flag,
2349 x_create_cost_flag => l_create_cost_flag,
2350 x_disallow_request_update => l_disallow_request_update,
2351 x_disallow_new_charge => l_disallow_new_charge,
2352 x_disallow_charge_update => l_disallow_charge_update,
2353 x_msg_data => l_msg_data,
2354 x_msg_count => l_msg_count,
2355 x_return_status => l_return_status
2356 );
2357
2358 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2359 THEN
2360 FND_LOG.String
2361 ( FND_LOG.level_procedure ,
2362 L_LOG_MODULE || '',
2363 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
2364 );
2365
2366 FND_LOG.String
2367 ( FND_LOG.level_procedure ,
2368 L_LOG_MODULE || '',
2369 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
2370 'l_disallow_charge_update: '||l_disallow_charge_update
2371 );
2372 END IF;
2373
2374 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
2375
2376 if l_disallow_request_update='Y' THEN
2377 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
2378 FND_MSG_PUB.ADD;
2379 RAISE FND_API.G_EXC_ERROR;
2380 end if;
2381
2382 Else
2383
2384 if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
2385 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
2386 FND_MSG_PUB.ADD;
2387 RAISE FND_API.G_EXC_ERROR;
2388 end if;
2389
2390 end if;
2391
2392 --end Bug fix for 6972425
2393
2394 END IF;
2395
2396 lx_cost_rec:=l_cost_rec;
2397
2398 --Cost Line can only be created for 'ACTUAL' charge Line types
2399
2400 if lx_cost_rec.charge_line_type is not null and lx_cost_rec.charge_line_type <> fnd_api.g_miss_char
2401 then
2402 if lx_cost_rec.charge_line_type<>'ACTUAL' then
2403
2404 Add_Invalid_Argument_Msg(l_api_name_full,
2405 to_char(lx_cost_rec.charge_line_type),
2406 'Charge_line_Type');
2407 RAISE G_WARNING;
2408 end if;
2409 end if;
2410
2411
2412
2413 IF (p_validation_level = FND_API.G_VALID_LEVEL_NONE) THEN
2414
2415
2416 Validate_Who_Info ( p_api_name => l_api_name_full,
2417 p_user_id => NVL(p_user_id, -1),
2418 p_login_id => p_login_id,
2419 x_return_status => l_return_status);
2420
2421 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2422 RAISE FND_API.G_EXC_ERROR;
2423 END IF;
2424
2425 IF lt_estimate_detail_id is null then
2426
2427 lx_cost_rec:= l_cost_rec;
2428
2429 END IF;
2430
2431 L_RECALC_COST :='Y';
2432
2433
2434 ELSIF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
2435
2436
2437 -- Perform all the validations by calling the procedure VALIDATE_COST_DETAILS which inturn does all the validations
2438 --This is done only when there is no estimate_Detail_id
2439 if lt_estimate_detail_id is null or lt_estimate_detail_id = fnd_api.g_miss_num
2440 then
2441
2442 VALIDATE_COST_DETAILS
2443 (
2444 p_api_name => l_api_name,
2445 pv_cost_rec => l_cost_rec,
2446 p_validation_mode => 'U' ,
2447 p_user_id => p_user_id,
2448 p_login_id => p_login_id,
2449 x_cost_rec => lx_cost_rec,
2450 x_msg_data => x_msg_data,
2451 x_msg_count => x_msg_count,
2452 x_return_status => l_return_status
2453 );
2454
2455
2456 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2457 FND_MESSAGE.Set_Name('CS', 'CS_COST_VALIDATE_COST_DTL_ER');
2458 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
2459 FND_MSG_PUB.Add;
2460 RAISE FND_API.G_EXC_ERROR;
2461 END IF;
2462
2463 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2464 THEN
2465 FND_LOG.String
2466 ( FND_LOG.level_procedure ,
2467 L_LOG_MODULE || '',
2468 'After Validating the Cost Details '
2469 );
2470
2471 END IF;
2472
2473 lv_cost_id := lx_cost_rec.cost_id;
2474
2475 end if;
2476 END IF;
2477
2478 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2479 THEN
2480 FND_LOG.String
2481 ( FND_LOG.level_procedure ,
2482 L_LOG_MODULE || '',
2483 'Before Cost Calculations'
2484 );
2485 END IF;
2486
2487
2488 /*
2489 if sign(lx_cost_rec.extended_cost)= -1
2490 then
2491
2492 Add_Invalid_Argument_Msg(l_api_name_full,
2493 to_char(lx_cost_rec.extended_cost),
2494 'Extended Cost ');
2495 RAISE FND_API.G_EXC_ERROR;
2496
2497 end if;
2498 */
2499
2500 if sign(lx_cost_rec.quantity) = 0
2501 then
2502
2503
2504 Add_Invalid_Argument_Msg(l_api_name_full,
2505 to_char(lx_cost_rec.quantity),
2506 'Quantity ');
2507 RAISE FND_API.G_EXC_ERROR;
2508
2509 end if;
2510
2511
2512 p_cost_id := lv_cost_id;
2513
2514 open get_flag;
2515 fetch get_flag into v_unit_cost,
2516 v_extended_cost,
2517 v_override_ext_cost_flag ;
2518 close get_flag;
2519
2520
2521 l_override_ext_cost_flag:=v_override_ext_cost_flag;
2522 l_unit_cost :=v_unit_cost;
2523 if lx_cost_rec.extended_cost is not null
2524 and lx_cost_rec.extended_cost <> fnd_api.g_miss_num then
2525
2526 --l_unit_cost := NULL;
2527 --lx_cost_rec.quantity := NULL;
2528 l_unit_cost := v_unit_cost;
2529 l_override_ext_cost_flag :='Y';
2530 L_RECALC_COST := 'N'; --If extended cost is passed then need not recalculate the unit cost/extended cost
2531
2532 end if;
2533
2534
2535
2536 if L_RECALC_COST = 'Y' then
2537 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2538 THEN
2539 FND_LOG.String
2540 ( FND_LOG.level_procedure ,
2541 L_LOG_MODULE || '',
2542 'Recalcualting the Cost'
2543 );
2544 END IF;
2545 --Bug 7193528
2546 if l_create_charge_flag ='N' then
2547 --l_cost_org_id := p_cost_rec.inventory_org_id;
2548
2549 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
2550 x_org_id => l_cost_org_id,
2551 x_inv_organization_id => l_cost_inv_org_id ,
2552 x_msg_data => x_msg_data,
2553 x_msg_count => x_msg_count,
2554 x_return_status => l_return_status
2555 );
2556
2557 else
2558
2559 l_cost_inv_org_id :=lx_cost_rec.inventory_org_id;
2560 l_cost_org_id :=lx_cost_rec.org_id;
2561
2562 if l_cost_Rec.estimate_Detail_id is null then
2563
2564 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
2565 x_org_id => l_cost_org_id,
2566 x_inv_organization_id => l_cost_inv_org_id ,
2567 x_msg_data => x_msg_data,
2568 x_msg_count => x_msg_count,
2569 x_return_status => l_return_status
2570 );
2571 end if;
2572
2573 end if;
2574
2575
2576
2577 -- added by bkanimoz for service projects integration costing flow changes spi
2578 /*
2579 Inventory Org
2580 Stote the charge line's inventory organization.If not present get the value from the value 'Service:Inventory Validation Org'
2581
2582 Currency
2583 Store the currency of the Inventory Organization
2584
2585 Unit Cost
2586 Pass the Inventory Organization and the Item to get the Unit cost .The cost returned will be in the currency of the Inventory Org
2587
2588 */
2589
2590
2591
2592 If l_cost_inv_org_id is null then
2593 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_NO_INV_ORG');
2594 FND_MSG_PUB.ADD;
2595 RAISE FND_API.G_EXC_ERROR;
2596 End If;
2597
2598
2599
2600 OPEN get_org_currency;
2601 FETCH get_org_currency INTO l_org_currency;
2602 --EXIT WHEN get_org_currency%NOTFOUND;
2603 CLOSE get_org_currency;
2604
2605
2606 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2607 THEN
2608 FND_LOG.String
2609 ( FND_LOG.level_procedure ,
2610 L_LOG_MODULE || '',
2611 'Before calling CST_COST_API.Get_Item_Cost '||'p_inventory_item_id :'||lx_cost_rec.inventory_item_id||'p_organization_id :'||l_cost_inv_org_id
2612 );
2613 END IF;
2614 --Calculate the Item's Unit Cost
2615 l_unit_cost := CST_COST_API.Get_Item_Cost
2616 (
2617 p_api_version => 1.0,
2618 p_inventory_item_id => lx_cost_rec.inventory_item_id,
2619 p_organization_id => l_cost_inv_org_id,
2620 p_cost_group_id => p_cost_group_id,
2621 p_cost_type_id => p_cost_type_id
2622 );
2623
2624 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2625 THEN
2626 FND_LOG.String
2627 ( FND_LOG.level_procedure ,
2628 L_LOG_MODULE || '',
2629 'After calling CST_COST_API.Get_Item_Cost '||'l_unit_cost : '||l_unit_cost
2630 );
2631 END IF;
2632
2633
2634 --Calculate the Item's Extended Cost
2635 If lx_cost_rec.extended_cost IS NULL or lx_cost_rec.extended_cost = fnd_api.g_miss_num
2636 THEN
2637 lx_cost_rec.extended_cost :=l_unit_cost*lx_cost_rec.quantity;
2638 l_override_ext_cost_flag :='N';
2639 Else
2640
2641 --l_unit_cost := NULL;
2642 --l_quantity := NULL;
2643 l_override_ext_cost_flag :='Y';
2644
2645 END IF;
2646
2647 end if ;
2648
2649 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2650 THEN
2651 FND_LOG.String
2652 ( FND_LOG.level_procedure ,
2653 L_LOG_MODULE || '',
2654 'Before calling the Update_Row Procedure'
2655 );
2656 END IF;
2657
2658
2659 CS_COST_DETAILS_PKG.Update_Row
2660 (
2661 p_cost_id =>lx_cost_rec.cost_id ,
2662 p_incident_id =>lx_cost_rec.incident_id ,
2663 p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
2664 p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
2665 p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
2666 --p_charge_line_type =>lx_cost_rec.charge_line_type ,
2667 p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
2668 p_quantity =>lx_cost_rec.quantity ,
2669 p_unit_cost =>l_unit_cost ,
2670 p_extended_cost =>lx_cost_rec.extended_cost ,
2671 p_override_ext_cost_flag =>l_override_ext_cost_flag ,
2672 p_transaction_date =>sysdate ,
2673 p_source_id =>lx_cost_rec.source_id ,
2674 p_source_code =>lx_cost_rec.source_code ,
2675 p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
2676 p_currency_code =>l_org_currency ,--for 12.2.2 Service Projects Integration
2677 p_org_id =>l_cost_org_id ,
2678 p_inventory_org_id =>l_cost_inv_org_id ,
2679 p_attribute1 =>lx_cost_rec.attribute1 ,
2680 p_attribute2 =>lx_cost_rec.attribute2 ,
2681 p_attribute3 =>lx_cost_rec.attribute3 ,
2682 p_attribute4 =>lx_cost_rec.attribute4 ,
2683 p_attribute5 =>lx_cost_rec.attribute5 ,
2684 p_attribute6 =>lx_cost_rec.attribute6 ,
2685 p_attribute7 =>lx_cost_rec.attribute7 ,
2686 p_attribute8 =>lx_cost_rec.attribute8 ,
2687 p_attribute9 =>lx_cost_rec.attribute9 ,
2688 p_attribute10 =>lx_cost_rec.attribute10 ,
2689 p_attribute11 =>lx_cost_rec.attribute11 ,
2690 p_attribute12 =>lx_cost_rec.attribute12 ,
2691 p_attribute13 =>lx_cost_rec.attribute13 ,
2692 p_attribute14 =>lx_cost_rec.attribute14 ,
2693 p_attribute15 =>lx_cost_rec.attribute15 ,
2694 p_last_update_date => sysdate ,
2695 p_last_updated_by => FND_GLOBAL.USER_ID ,
2696 p_last_update_login => FND_GLOBAL.LOGIN_ID ,
2697 p_created_by => FND_GLOBAL.USER_ID ,
2698 p_creation_date => sysdate ,
2699 x_object_version_number => l_object_version_number
2700 );
2701
2702
2703
2704 -- Standard check of p_commit
2705 IF FND_API.To_Boolean(p_commit) THEN
2706 COMMIT ;
2707
2708 END IF;
2709
2710 -- Standard call to get message count and if count is 1, get message info
2711 FND_MSG_PUB.Count_And_Get
2712 ( p_count => x_msg_count,
2713 p_data => x_msg_data
2714 );
2715
2716
2717 EXCEPTION
2718
2719 WHEN FND_API.G_EXC_ERROR THEN
2720 IF FND_API.To_Boolean(p_transaction_control) THEN
2721 ROLLBACK TO Update_Cost_Details_PVT;
2722 END IF ;
2723 x_return_status := FND_API.G_RET_STS_ERROR;
2724 FND_MSG_PUB.Count_And_Get(
2725 p_count => x_msg_count,
2726 p_data => x_msg_data,
2727 p_encoded => FND_API.G_FALSE) ;
2728
2729 WHEN G_WARNING THEN
2730 x_return_status := FND_API.G_RET_STS_SUCCESS;
2731
2732 IF FND_API.To_Boolean(p_transaction_control) THEN
2733 ROLLBACK TO Update_Cost_Details_PVT;
2734 END IF ;
2735 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2736 p_data => x_msg_data,
2737 p_encoded => FND_API.G_FALSE) ;
2738
2739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2740 IF FND_API.To_Boolean(p_transaction_control) THEN
2741 ROLLBACK TO Update_Cost_Details_PVT;
2742 END IF ;
2743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2744 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2745 p_data => x_msg_data,
2746 p_encoded => FND_API.G_FALSE) ;
2747 WHEN OTHERS THEN
2748 IF FND_API.To_Boolean(p_transaction_control) THEN
2749 ROLLBACK TO Update_Cost_Details_PVT;
2750 END IF ;
2751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2752 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2753 THEN
2754 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2755 END IF;
2756 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2757 p_data => x_msg_data,
2758 p_encoded => FND_API.G_FALSE) ;
2759
2760
2761 END Update_Cost_Details;
2762
2763 /*======================================================================+
2764 ==
2765 == Procedure name : delete_cost_details
2766 == Comments : API to Update cost details in cs_cost_details
2767 == Modification History:
2768 ==
2769 == Date Name Desc
2770 == ---------- --------- ---------------------------------------------
2771 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2772 ========================================================================*/
2773
2774
2775 PROCEDURE Delete_Cost_Details
2776 (
2777 p_api_version IN NUMBER,
2778 p_init_msg_list IN VARCHAR2,
2779 p_commit IN VARCHAR2 ,
2780 p_validation_level IN NUMBER ,
2781 x_return_status OUT NOCOPY VARCHAR2,
2782 x_msg_count OUT NOCOPY NUMBER,
2783 x_msg_data OUT NOCOPY VARCHAR2,
2784 p_transaction_control IN VARCHAR2 ,
2785 p_cost_id IN NUMBER := NULL
2786 )IS
2787
2788
2789 l_api_name CONSTANT VARCHAR2(100) := 'Delete_Cost_Details' ;
2790 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
2791 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.plsl.' || l_api_name_full || '.';
2792 l_api_version CONSTANT NUMBER := 1.0 ;
2793
2794 l_resp_appl_id NUMBER ;
2795 l_resp_id NUMBER ;
2796 l_user_id NUMBER ;
2797 l_login_id NUMBER ;
2798 l_org_id NUMBER := NULL ;
2799 l_charge_line_type VARCHAR2(30);
2800 l_return_status VARCHAR2(1) ;
2801 l_valid_check VARCHAR2(1);
2802
2803 BEGIN
2804
2805 --Standard Start of API Savepoint
2806 IF FND_API.To_Boolean( p_transaction_control ) THEN
2807 SAVEPOINT Delete_Cost_Details_PVT ;
2808 END IF ;
2809 --Standard Call to check API compatibility
2810 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2811 THEN
2812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2813 END IF ;
2814 --Initialize the message list if p_msg_list is set to TRUE
2815 IF FND_API.To_Boolean(p_init_msg_list) THEN
2816 FND_MSG_PUB.initialize ;
2817 END IF ;
2818
2819 --Initialize the API Return Success to True
2820 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2821
2822 ----------------------- FND Logging -----------------------------------
2823 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2824 THEN
2825 FND_LOG.String
2826 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
2827 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2828 );
2829 FND_LOG.String
2830 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2831 , 'p_api_version:' || p_api_version
2832 );
2833 FND_LOG.String
2834 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2835 , 'p_init_msg_list:' || p_init_msg_list
2836 );
2837 FND_LOG.String
2838 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2839 , 'p_commit:' || p_commit
2840 );
2841 FND_LOG.String
2842 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2843 , 'p_validation_level:' || p_validation_level
2844 );
2845 FND_LOG.String
2846 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2847 , 'p_transaction_control:' || p_transaction_control
2848 );
2849 FND_LOG.String
2850 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2851 , 'p_cost_id' || p_cost_id
2852 );
2853
2854 END IF;
2855
2856 IF (p_cost_id IS NULL) THEN
2857 Add_Null_Parameter_Msg(l_api_name_full,
2858 'p_cost_id') ;
2859 RAISE FND_API.G_EXC_ERROR ;
2860 END IF ;
2861
2862 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2863 THEN
2864 FND_LOG.String
2865 ( FND_LOG.level_procedure ,
2866 L_LOG_MODULE || '',
2867 'Before Do_Cost_Line_Exist'
2868 );
2869 END IF;
2870
2871 l_valid_check:= Do_cost_line_Exist
2872 (
2873 l_api_name_full,
2874 p_cost_id,
2875 l_return_status
2876 ) ;
2877 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2878 Add_Invalid_Argument_Msg(l_api_name,
2879 TO_CHAR(p_cost_id),
2880 'cost_id');
2881 RAISE G_WARNING;
2882 ELSIF l_return_status = G_RET_STS_ERROR THEN
2883 Add_Invalid_Argument_Msg(l_api_name,
2884 TO_CHAR(p_cost_id),
2885 'cost_id');
2886 RAISE G_WARNING;
2887 END IF;
2888
2889 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2890 RAISE G_WARNING ;
2891 END IF ;
2892
2893 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2894 THEN
2895 FND_LOG.String
2896 ( FND_LOG.level_procedure ,
2897 L_LOG_MODULE || '',
2898 'Before Do_charge_line_Exist'
2899 );
2900 END IF;
2901
2902 --if there is a charge line existing for this cost then do not delete this cost line
2903
2904 l_valid_check:= Do_charge_line_Exist
2905 (
2906 l_api_name_full,
2907 p_cost_id,
2908 l_return_status
2909 ) ;
2910
2911 if l_valid_check ='Y' then
2912 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_DELETE');
2913 FND_MSG_PUB.ADD;
2914 RAISE G_WARNING;
2915 end if;
2916
2917
2918 delete from cs_cost_details where
2919 cost_id = p_cost_id;
2920
2921 --End of API Body
2922 --Standard Check of p_commit
2923 IF FND_API.To_Boolean( p_commit ) THEN
2924 COMMIT WORK ;
2925 END IF ;
2926
2927 --Standard call to get message count and if count is 1 , get message info
2928 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
2929 p_data => x_msg_data) ;
2930
2931 --Begin Exception Handling
2932
2933 EXCEPTION
2934
2935 WHEN FND_API.G_EXC_ERROR THEN
2936 IF FND_API.To_Boolean( p_transaction_control ) THEN
2937 ROLLBACK TO Delete_Cost_Details_PVT;
2938 END IF ;
2939 x_return_status := FND_API.G_RET_STS_ERROR ;
2940 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2941 p_data => x_msg_data,
2942 p_encoded => FND_API.G_FALSE) ;
2943
2944 WHEN G_WARNING THEN
2945 x_return_status := FND_API.G_RET_STS_SUCCESS;
2946 IF FND_API.To_Boolean(p_transaction_control) THEN
2947 ROLLBACK TO Delete_Cost_Details_PVT;
2948 END IF ;
2949 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2950 p_data => x_msg_data,
2951 p_encoded => FND_API.G_FALSE) ;
2952
2953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2954 IF FND_API.To_Boolean( p_transaction_control ) THEN
2955 ROLLBACK TO Delete_Cost_Details_PVT;
2956 END IF ;
2957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2958 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2959 p_data => x_msg_data,
2960 p_encoded => FND_API.G_FALSE) ;
2961
2962 WHEN OTHERS THEN
2963 IF FND_API.To_Boolean( p_transaction_control ) THEN
2964 ROLLBACK TO Delete_Cost_Details_PVT;
2965 END IF ;
2966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2968 THEN
2969 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2970 END IF ;
2971 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2972 p_data => x_msg_data,
2973 p_encoded => FND_API.G_FALSE) ;
2974
2975 End Delete_Cost_Details;
2976
2977 /*======================================================================+
2978 ==
2979 == Procedure name : Validate_cost_details
2980 == Comments : API to Update cost details in cs_cost_details
2981 == Modification History:
2982 ==
2983 == Date Name Desc
2984 == ---------- --------- ---------------------------------------------
2985 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2986 ========================================================================*/
2987
2988 PROCEDURE VALIDATE_COST_DETAILS
2989 (
2990 p_api_name IN VARCHAR2,
2991 pv_cost_rec IN CS_COST_DETAILS_PUB.COST_REC_TYPE,
2992 p_validation_mode IN VARCHAR2,
2993 p_user_id IN NUMBER,
2994 p_login_id IN NUMBER,
2995 x_cost_rec OUT NOCOPY CS_COST_DETAILS_PUB.COST_REC_TYPE,
2996 x_msg_data OUT NOCOPY VARCHAR2,
2997 x_msg_count OUT NOCOPY NUMBER,
2998 x_return_status OUT NOCOPY VARCHAR2
2999 ) IS
3000
3001 l_valid_check VARCHAR2(1);
3002 l_return_status VARCHAR2(1) ;
3003 l_msg_data VARCHAR2(2000);
3004 l_msg_count NUMBER;
3005 l_api_version NUMBER := 1.0 ;
3006 l_api_name CONSTANT VARCHAR2(100) := 'Validate_Cost_Details Private API' ;
3007 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
3008 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.pls' || l_api_name_full || '.';
3009 l_db_det_rec CS_COST_DETAILS%ROWTYPE;
3010 l_source_id NUMBER;
3011 l_org_id NUMBER;
3012 l_profile VARCHAR2(200);
3013 l_primary_uom VARCHAR2(10);
3014 l_currency_code VARCHAR2(10);
3015
3016 l_disallow_new_charge VARCHAR2(1);
3017 l_disallow_charge_update VARCHAR2(1);
3018 l_disallow_request_update VARCHAR2(1);
3019 l_create_charge_flag VARCHAR2(1);
3020 l_create_cost_flag VARCHAR2(1);
3021
3022 l_txn_billing_type_id NUMBER;
3023 l_inv_item_id NUMBER;
3024 l_line_order_category_code VARCHAR2(10);
3025 l_no_charge VARCHAR2(1);
3026 lx_quantity NUMBER;
3027
3028 BEGIN
3029
3030 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3031 THEN
3032 FND_LOG.String
3033 ( FND_LOG.level_procedure ,
3034 L_LOG_MODULE || '',
3035 'Inside Validate_Cost_Details'
3036 );
3037 END IF;
3038
3039 IF p_validation_mode = 'U' THEN
3040
3041 IF pv_cost_rec.cost_id IS NULL OR
3042 pv_cost_rec.cost_id = FND_API.G_MISS_NUM THEN
3043 Add_Null_Parameter_Msg(l_api_name,'cost_id') ;
3044 Add_Invalid_Argument_Msg(l_api_name,TO_CHAR(pv_cost_rec.estimate_detail_id),'cost_id');
3045 RAISE FND_API.G_EXC_ERROR;
3046 ELSE -- validate the cost id passed
3047 IF IS_COST_ID_VALID(p_cost_id => pv_cost_rec.cost_id,
3048 x_msg_data => l_msg_data,
3049 x_msg_count => l_msg_count,
3050 x_return_status => l_return_status) = 'U' THEN
3051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052
3053 ELSIF IS_COST_ID_VALID( p_cost_id => pv_cost_rec.cost_id,
3054 x_msg_data => l_msg_data,
3055 x_msg_count => l_msg_count,
3056 x_return_status => l_return_status) = 'N' THEN
3057 Add_Invalid_Argument_Msg(l_api_name,TO_CHAR(pv_cost_rec.cost_id),'cost_id');
3058 RAISE FND_API.G_EXC_ERROR;
3059 ELSE
3060 --cost id is valid
3061 --assign to out record
3062 x_cost_Rec.cost_id := pv_cost_rec.cost_id;
3063 -- Get existing cost record for this estimate detail_id
3064 Get_Cost_Detail_Rec (P_API_NAME => l_api_name_full,
3065 P_COST_ID => pv_cost_rec.cost_id,
3066 x_COST_DETAIL_REC => l_db_det_rec,
3067 x_MSG_DATA => l_msg_data,
3068 x_MSG_COUNT => l_msg_count,
3069 x_RETURN_STATUS => l_return_status);
3070
3071
3072 if (l_return_status = fnd_api.g_ret_sts_error) then
3073 RAISE FND_API.G_EXC_ERROR;
3074 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
3075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3076 end if;
3077 END IF;
3078 END IF;
3079 END IF;
3080
3081 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3082 THEN
3083 FND_LOG.String
3084 ( FND_LOG.level_procedure ,
3085 L_LOG_MODULE || '',
3086 'After Cost_Id Validation'
3087 );
3088 END IF;
3089
3090 IF pv_cost_rec.quantity <> l_db_det_rec.quantity and pv_cost_rec.quantity <> fnd_api.g_miss_num THEN
3091 -- cost will be recalculated if quantity changes during updation
3092 l_recalc_cost := 'Y';
3093 END IF;
3094 x_cost_rec:=pv_cost_rec;
3095
3096 ------------------------------------------------------------------------
3097 /* 1. Incident ID - Mandatory, If null or invalid throw Error message and stop processing
3098 */
3099
3100 if p_validation_mode ='I' then
3101 if pv_cost_rec.incident_id is not null then
3102
3103 l_valid_check := IS_INCIDENT_ID_VALID
3104 (
3105 p_incident_id => pv_cost_rec.incident_id,
3106 x_msg_data => l_msg_data,
3107 x_msg_count => l_msg_count,
3108 x_return_status => l_return_status
3109 );
3110 if l_return_status = g_ret_sts_unexp_error then
3111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3112 ELSIF l_return_status = G_RET_STS_ERROR THEN
3113 RAISE FND_API.G_EXC_ERROR;
3114 END IF;
3115
3116 if l_valid_check ='Y' then
3117 x_cost_rec.incident_id := pv_cost_rec.incident_id;
3118 else --throw the error message and stop processing
3119 Add_Invalid_Argument_Msg(l_api_name,
3120 pv_cost_rec.incident_id,
3121 'incident_id');
3122
3123 RAISE FND_API.G_EXC_ERROR;
3124 end if;
3125
3126 else
3127 Add_Null_Parameter_Msg(l_api_name,'p_incident_id') ;
3128 RAISE FND_API.G_EXC_ERROR;
3129 end if;
3130
3131 elsif p_validation_mode ='U' then
3132 -- Incident Id will not change, hence assign from the database
3133 x_cost_rec.incident_id :=l_db_det_rec.incident_id;
3134
3135 end if;
3136
3137 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3138 THEN
3139 FND_LOG.String
3140 ( FND_LOG.level_procedure ,
3141 L_LOG_MODULE || '',
3142 'After Incident_ID Validation'
3143 );
3144 END IF;
3145
3146
3147 ----------------------------------------------------------------------------------------------------------
3148 /* 2.Transaction_type_ID - Mandatory, If null or invalid throw Error message and stop processing
3149 */
3150
3151 IF p_validation_mode = 'U' THEN
3152
3153
3154 if pv_cost_rec.transaction_type_id = FND_API.G_MISS_NUM OR
3155 pv_cost_rec.transaction_type_id IS NULL THEN
3156
3157 --Default attributes using db record
3158 x_cost_rec.transaction_type_id := l_db_det_rec.transaction_type_id;
3159
3160 else
3161 --validate teh transaction type id passed
3162
3163 VALIDATE_TRANSACTION_TYPE_ID
3164 (
3165 p_api_name => p_api_name,
3166 p_transaction_type_id => pv_cost_rec.transaction_type_id,
3167 x_line_order_category_code => l_line_order_category_code,
3168 x_msg_data => l_msg_data,
3169 x_msg_count => l_msg_count,
3170 x_return_status => l_return_status
3171 ) ;
3172
3173 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3174 RAISE FND_API.G_EXC_ERROR ;
3175 end if;
3176
3177 --assign the values source_code, source_id to out record
3178 x_cost_rec.transaction_type_id := pv_cost_rec.transaction_type_id;
3179 l_transaction_type_changed := 'Y';
3180 lx_quantity :=pv_cost_rec.quantity ;
3181
3182 if pv_cost_rec.quantity = FND_API.G_MISS_NUM OR
3183 pv_cost_rec.quantity = null
3184 then
3185 lx_quantity :=l_db_det_rec.quantity;
3186 end if ;
3187
3188 IF (l_line_order_category_code = 'RETURN') then
3189 if lx_quantity is not null
3190 then
3191 if sign(pv_cost_rec.quantity) = -1 then
3192 x_cost_rec.quantity := lx_quantity;
3193 else
3194 --assign -ve qty to out record
3195 x_cost_rec.quantity := ( lx_quantity * -1);
3196 end if;
3197 end if;
3198 Else
3199 if lx_quantity is not null then
3200 if sign( lx_quantity ) = -1 then
3201 -- need to make this positive as no -ve quantity for orders
3202 x_cost_rec.quantity := ( lx_quantity * -1);
3203 else
3204 x_cost_rec.quantity := lx_quantity ;
3205 end if;
3206 end if;
3207 End if;
3208 end if;
3209 END IF;--validation mode
3210
3211 ----------------------------------------------------------------------------------------------------------
3212 /* 3. Check for the Status Flags - Mandatory,
3213 If null or invalid throw Error message and stop processing
3214 */
3215
3216
3217 /*
3218 When the costs are created from charges lines and the "Disallow Charge" flag is Yes (checked)
3219 the behavior should be the same as the current behavior so no records should be created.
3220 If the "Disallow Charge Update" flag is Yes (checked) then we should not allow updates for charges or costs.
3221
3222 However, for the scenario when Create Charge="N" and Create Cost="Y"
3223 we should not validate the flags since the costs are not dependent on the charge creation.
3224 In this case we should create the costs.
3225
3226 The costs should not be created only if the Disallow Request Update is Yes (checked).
3227 */
3228
3229
3230 get_charge_flags_from_sr
3231 (
3232 p_api_name => p_api_name,
3233 p_incident_id => x_cost_rec.incident_id,
3234 p_transaction_type_id => pv_cost_rec.transaction_type_id,
3235 x_create_charge_flag => l_create_charge_flag,
3236 x_create_cost_flag => l_create_cost_flag,
3237 x_disallow_request_update => l_disallow_request_update,
3238 x_disallow_new_charge => l_disallow_new_charge,
3239 x_disallow_charge_update => l_disallow_charge_update,
3240 x_msg_data => l_msg_data,
3241 x_msg_count => l_msg_count,
3242 x_return_status => l_return_status
3243 );
3244
3245 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3246 THEN
3247 FND_LOG.String
3248 ( FND_LOG.level_procedure ,
3249 L_LOG_MODULE || '',
3250 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
3251 );
3252
3253 FND_LOG.String
3254 ( FND_LOG.level_procedure ,
3255 L_LOG_MODULE || '',
3256 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
3257 'l_disallow_charge_update: '||l_disallow_charge_update
3258 );
3259 END IF;
3260
3261
3262 IF p_validation_mode = 'I' THEN
3263 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
3264 if l_disallow_request_update='Y' THEN
3265
3266
3267 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
3268 FND_MSG_PUB.ADD;
3269 RAISE FND_API.G_EXC_ERROR;
3270 end if;
3271
3272 Else
3273 if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
3274
3275
3276 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
3277 FND_MSG_PUB.ADD;
3278 RAISE FND_API.G_EXC_ERROR;
3279 end if;
3280
3281 end if;
3282
3283 ELSIF p_validation_mode = 'U' THEN
3284
3285
3286 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
3287 if l_disallow_request_update='Y' THEN
3288 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
3289 FND_MSG_PUB.ADD;
3290 RAISE FND_API.G_EXC_ERROR;
3291 end if;
3292
3293 Else
3294 if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
3295
3296 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
3297 FND_MSG_PUB.ADD;
3298 RAISE FND_API.G_EXC_ERROR;
3299 end if;
3300
3301 end if;
3302
3303 END IF;
3304
3305 ----------------------------------------------------------------------------------------------------------
3306 /* 4. Check for the Estimate Detail ID
3307 If null or invalid throw Error message and stop processing
3308 */
3309
3310 if p_validation_mode ='I' then
3311
3312 if pv_cost_rec.estimate_Detail_id is not null then
3313
3314 l_valid_check := IS_ESTIMATE_DETAIL_ID_VALID
3315 (
3316 p_estimate_detail_id => pv_cost_rec.estimate_detail_id,
3317 x_msg_data => l_msg_data,
3318 x_msg_count => l_msg_count,
3319 x_return_status => l_return_status
3320 );
3321 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3323 ELSIF l_return_status = G_RET_STS_ERROR THEN
3324 RAISE FND_API.G_EXC_ERROR;
3325 END IF;
3326
3327 if l_valid_check ='Y' then
3328 x_cost_rec.estimate_detail_id := pv_cost_rec.estimate_detail_id ;
3329 else --throw the error message and stop processing
3330 Add_Invalid_Argument_Msg(l_api_name,
3331 pv_cost_rec.estimate_Detail_id,
3332 'estimate_detail_id');
3333 RAISE FND_API.G_EXC_ERROR;
3334 end if;
3335
3336 else -- if null , then just assign
3337 x_cost_rec.estimate_Detail_id := pv_cost_rec.estimate_detail_id;
3338
3339 end if;
3340 elsif p_validation_mode ='U' then
3341 -- Estimate Detail Id will not change for the cost_id, hence assign from the database
3342
3343 x_cost_rec.estimate_detail_id :=l_db_det_rec.estimate_Detail_id;
3344
3345 end if;
3346
3347
3348 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3349 THEN
3350 FND_LOG.String
3351 ( FND_LOG.level_procedure ,
3352 L_LOG_MODULE || '',
3353 'After Estimate_Detail_ID Validation'
3354 );
3355 END IF;
3356
3357 ----------------------------------------------
3358
3359 /* 5. Source Id and Source Code Mandatory
3360 validate the source id and source code against the respective tables
3361 */
3362
3363
3364
3365 IF p_validation_mode = 'I' THEN
3366 if pv_cost_rec.source_id is not null and pv_cost_rec.source_code is not null then
3367
3368 VALIDATE_SOURCE
3369 (
3370 p_api_name => p_api_name,
3371 p_source_code => pv_cost_rec.source_code,
3372 p_source_id => pv_cost_rec.source_id,
3373 --x_source_code => l_source_code,
3374 x_source_id => l_source_id,
3375 x_msg_data => l_msg_data,
3376 x_msg_count => l_msg_count,
3377 x_return_status => l_return_status
3378 ) ;
3379 If l_return_status = G_RET_STS_UNEXP_ERROR then
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 elsif l_return_status = G_RET_STS_ERROR then
3382 RAISE FND_API.G_EXC_ERROR;
3383 end if;
3384
3385 x_cost_Rec.source_id := l_source_id;
3386 x_cost_rec.source_code := pv_cost_rec.source_code;
3387
3388
3389 else
3390
3391 Add_Null_Parameter_Msg(l_api_name,
3392 'p_source_id') ;
3393 /*
3394 Add_Invalid_Argument_Msg(l_api_name,
3395 to_char(pv_cost_rec.incident_id),
3396 'source_id');*/
3397 RAISE FND_API.G_EXC_ERROR;
3398 end if;
3399
3400 ELSIF p_validation_mode = 'U' THEN
3401
3402 if pv_cost_rec.source_code = FND_API.G_MISS_CHAR OR
3403 pv_cost_rec.source_code IS NULL AND
3404 pv_cost_rec.source_id = FND_API.G_MISS_NUM OR
3405 pv_cost_rec.source_id IS NULL THEN
3406
3407 --Default attributes using db record
3408 x_cost_rec.source_code := l_db_det_rec.source_code;
3409 x_cost_rec.source_id := l_db_det_rec.source_id;
3410 else
3411
3412 VALIDATE_SOURCE
3413 (
3414 p_api_name => p_api_name,
3415 p_source_code => pv_cost_rec.source_code,
3416 p_source_id => pv_cost_rec.source_id,
3417 --x_source_code => l_source_code,
3418 x_source_id => l_source_id,
3419 x_msg_data => l_msg_data,
3420 x_msg_count => l_msg_count,
3421 x_return_status => l_return_status
3422 ) ;
3423
3424 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3425 RAISE FND_API.G_EXC_ERROR ;
3426 end if;
3427
3428 --assign the values source_code, source_id to out record
3429 x_cost_rec.source_code := x_cost_rec.source_code;
3430 x_cost_rec.source_id := x_cost_rec.source_id;
3431
3432
3433 end if;
3434
3435 END IF;--validation_mode
3436 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3437 THEN
3438 FND_LOG.String
3439 ( FND_LOG.level_procedure ,
3440 L_LOG_MODULE || '',
3441 'After Source Id and Source Code Validation'
3442 );
3443 END IF;
3444 ---------------------------------------------------
3445 /* 5. Operating Unit
3446 Check if the passed value for the org_id is mandatory .
3447 If not passed then default it from the Multi Org Setup --get this clarified
3448
3449 */
3450
3451 /*Validation Logic
3452
3453 1 If create_charge_flag =N then
3454 1 if org id is passed validate it and assign it to the out record
3455 2 if not passed then assign the multi org id to the out record
3456
3457 2.If create_charge_flag ='Y', then there will be an additional check for the profile
3458 'Service:Allow Charge Operating Unit Update'
3459 IF the profile is set to 'Y' then
3460 1.If Org _id is passed then validate that org id and assign it to the out rec
3461 2.If org id is not passed then assign the Multi Org Id
3462 If Profile is set to 'N'
3463 1.If Org Id is passed
3464 1. Check this with the Multi Org id.If not equal Throw an error message
3465 2.If equal assign it to the OUT record
3466 2 If Org Id is not passed assign Multi Org id to the out record
3467 */
3468
3469 --Get the Multi Org ID
3470
3471 CS_Multiorg_PUB.Get_OrgId
3472 (
3473 P_API_VERSION => 1.0,
3474 P_INIT_MSG_LIST => FND_API.G_FALSE,
3475 -- Fix bug 3236597 P_COMMIT => 'T',
3476 P_COMMIT => 'F', -- Fix bug 3236597
3477 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3478 X_RETURN_STATUS => l_return_status,
3479 X_MSG_COUNT => l_msg_count,
3480 X_MSG_DATA => l_msg_data,
3481 P_INCIDENT_ID => pv_cost_rec.incident_id,
3482 X_ORG_ID => l_org_id,
3483 X_PROFILE => l_profile
3484 );
3485
3486
3487
3488
3489
3490 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3491 RAISE FND_API.G_EXC_ERROR ;
3492 END IF;
3493
3494 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3495 THEN
3496 FND_LOG.String
3497 ( FND_LOG.level_procedure ,
3498 L_LOG_MODULE || '',
3499 'Mutli Org ID l_org_id : '||l_org_id||'l_profile :'||l_profile
3500 );
3501 END IF;
3502
3503
3504 IF p_validation_mode = 'I' THEN
3505 IF l_create_charge_flag ='Y' then
3506 IF l_profile = 'Y' THEN
3507
3508 if pv_cost_rec.org_id is not null then
3509
3510 VALIDATE_ORG_ID
3511 (
3512 P_API_NAME => l_api_name,
3513 P_ORG_ID => pv_cost_rec.org_id,
3514 X_RETURN_STATUS => l_return_status,
3515 X_MSG_COUNT => l_msg_count,
3516 X_MSG_DATA => l_msg_data
3517 );
3518
3519
3520 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3521 RAISE FND_API.G_EXC_ERROR ;
3522 end if;
3523 x_cost_rec.org_id := pv_cost_rec.org_id;
3524 else
3525
3526 --use the default
3527 x_cost_rec.org_id := l_org_id;
3528 end if;
3529 else
3530 -- l_profile = 'N'--Service:Allow Charge Operating Unit Update
3531 if pv_cost_rec.org_id is not null then
3532
3533 -- dbms_output.put_line(' OU 1 l_profile : '||l_profile||pv_cost_rec.org_id||'-'||l_org_id);
3534 if pv_cost_rec.org_id <> l_org_id then
3535 --raise error
3536 --Need to define error here
3537 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_CHANGE_OU');
3538 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
3539 FND_MSG_PUB.ADD;
3540 RAISE FND_API.G_EXC_ERROR;
3541 else
3542 x_cost_rec.org_id := pv_cost_rec.org_id;
3543 end if;
3544 else
3545 --pv_cost_rec.org_id is null
3546 --assign default
3547 x_cost_rec.org_id := l_org_id;
3548 end if;
3549 end if;--l_profile
3550 ELSE --flags create_charge_flag='N'
3551
3552
3553 if pv_cost_rec.org_id is not null then
3554
3555 VALIDATE_ORG_ID(
3556 P_API_NAME => l_api_name,
3557 P_ORG_ID => pv_cost_rec.org_id,
3558 X_RETURN_STATUS => l_return_status,
3559 X_MSG_COUNT => l_msg_count,
3560 X_MSG_DATA => l_msg_data
3561 );
3562
3563 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3564 RAISE FND_API.G_EXC_ERROR ;
3565 end if;
3566 x_cost_rec.org_id := pv_cost_rec.org_id;
3567 else
3568 --use the default
3569 x_cost_rec.org_id := l_org_id;
3570 end if;
3571
3572 END IF;--flags
3573
3574 ELSIF p_validation_mode = 'U' THEN
3575
3576
3577 IF l_create_charge_flag ='Y' then
3578 IF l_profile = 'Y' THEN
3579
3580 -- If l_profile = 'Y' THEN if org_id is not passed
3581 -- or org_id is null then assign the value from the database
3582 -- else if passed then validate the org_id and if valid then
3583 -- assign the value to the out parameter
3584
3585 if pv_cost_rec.org_id = FND_API.G_MISS_NUM or
3586 pv_cost_rec.org_id IS NULL THEN
3587 --use the value from the database
3588 x_cost_rec.org_id := l_db_det_rec.org_id;
3589
3590 else
3591
3592 VALIDATE_ORG_ID(
3593 P_API_NAME => l_api_name,
3594 P_ORG_ID => pv_cost_rec.org_id,
3595 X_RETURN_STATUS => l_return_status,
3596 X_MSG_COUNT => l_msg_count,
3597 X_MSG_DATA => l_msg_data);
3598
3599 if l_return_status <> fnd_api.g_ret_sts_success then
3600 raise fnd_api.g_exc_error ;
3601 end if;
3602 x_cost_rec.org_id := pv_cost_rec.org_id;
3603
3604 end if;
3605
3606 ELSE
3607 -- l_profile = 'N'
3608 -- If l_profile = 'N' THEN if org_id is not passed
3609 -- or org_id is null then assign the value from the database
3610 -- else if passed then validate the org_id and if valid then
3611 -- assign the value to the out parameter
3612
3613 IF pv_cost_rec.org_id = FND_API.G_MISS_NUM OR
3614 pv_cost_rec.org_id IS NULL THEN
3615 --use the value from the database
3616 x_cost_rec.org_id := l_db_det_rec.org_id;
3617
3618 ELSE
3619 IF pv_cost_rec.org_id <> l_db_det_rec.org_id THEN
3620 --raise error
3621 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_CHANGE_OU');
3622 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
3623 FND_MSG_PUB.ADD;
3624 RAISE FND_API.G_EXC_ERROR;
3625 ELSE
3626 x_cost_rec.org_id := pv_cost_rec.org_id;
3627 END IF;
3628 END IF;
3629 END IF;-- profile
3630 END IF;
3631 else--if create_charge_flag='N'
3632
3633 if pv_cost_rec.org_id = FND_API.G_MISS_NUM or
3634 pv_cost_rec.org_id IS NULL THEN
3635 --use the value from the database
3636 x_cost_rec.org_id := l_db_det_rec.org_id;
3637
3638 else
3639 VALIDATE_ORG_ID(
3640 P_API_NAME => l_api_name,
3641 P_ORG_ID => pv_cost_rec.org_id,
3642 X_RETURN_STATUS => l_return_status,
3643 X_MSG_COUNT => l_msg_count,
3644 X_MSG_DATA => l_msg_data);
3645
3646 if l_return_status <> fnd_api.g_ret_sts_success then
3647 raise fnd_api.g_exc_error ;
3648 end if;
3649 x_cost_rec.org_id := pv_cost_rec.org_id;
3650
3651 end if;
3652
3653 END IF;--validation mode
3654
3655 IF x_cost_rec.org_id <> l_db_det_rec.org_id THEN
3656 -- Item is changed so recalculate the cost.
3657 --cost will be recalculated if item,ou or quantity changes during updation
3658 l_recalc_cost := 'Y' ;
3659 --dbms_output.put_line('l_recalc_cost ORG changes');
3660 END IF;
3661 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3662 THEN
3663 FND_LOG.String
3664 ( FND_LOG.level_procedure ,
3665 L_LOG_MODULE || '',
3666 'After Org_ID Validation'
3667 );
3668 END IF;
3669 --------------------------------------------------------
3670 /* 5.Item Validation
3671 */
3672
3673
3674
3675 IF p_validation_mode = 'I' THEN
3676 --Added for the Debrief flow
3677 /* If the SAC setup is Create Charge = 'No' and Create Cost ='Yes' and no item is passed,
3678 then retrieve the inv. item from the profile Service: Default Inventory Item for Labor Transactions
3679 and proceed with the rest of the logic
3680 If the profile value is null, and no item is passed by the calling program,
3681 then raise an error message and abort the process.*/
3682 l_no_charge :='N';
3683 Get_Item_from_Profile(
3684 P_TRANSACTION_TYPE_ID =>pv_cost_rec.transaction_type_id,
3685 p_inv_item_id =>pv_cost_rec.inventory_item_id ,
3686 p_no_charge =>l_no_charge,
3687 x_inv_item_id =>l_inv_item_id,
3688 x_msg_data => l_msg_data,
3689 x_msg_count => l_msg_count,
3690 x_return_status => l_return_status
3691 );
3692 if l_no_charge = 'Y' then
3693 if l_inv_item_id is null then
3694 Add_Null_Parameter_Msg(l_api_name,
3695 'p_inventory_item_id') ;
3696 RAISE FND_API.G_EXC_ERROR;
3697 else
3698 x_cost_rec.inventory_item_id:=l_inv_item_id;
3699 end if;
3700 end if;
3701
3702 if l_no_charge <> 'Y' then
3703 if pv_cost_rec.inventory_item_id is not null then
3704
3705 l_valid_check := IS_ITEM_VALID
3706 (
3707 p_org_id => x_cost_rec.org_id,
3708 p_inventory_item_id => pv_cost_rec.inventory_item_id,
3709 x_msg_data => l_msg_data,
3710 x_msg_count => l_msg_count,
3711 x_return_status => l_return_status
3712 );
3713
3714
3715
3716 if l_valid_check ='Y' then
3717
3718 x_cost_rec.inventory_item_id := pv_cost_rec.inventory_item_id ;
3719 else --throw the error message and stop processing
3720 Add_Invalid_Argument_Msg(l_api_name,
3721 to_char(pv_cost_rec.inventory_item_id),
3722 'inventory_item_id');
3723
3724 RAISE FND_API.G_EXC_ERROR;
3725 end if;
3726
3727 else
3728
3729 Add_Null_Parameter_Msg(l_api_name,
3730 'p_inventory_item_id') ;
3731 /* Add_Invalid_Argument_Msg(l_api_name,
3732 to_char(pv_cost_rec.inventory_item_id),
3733 'inventory_item_id');*/
3734 RAISE FND_API.G_EXC_ERROR;
3735
3736 end if;
3737 end if;
3738 ELSIF p_validation_mode = 'U' THEN
3739
3740 if pv_cost_rec.inventory_item_id = FND_API.G_MISS_NUM OR
3741 pv_cost_rec.inventory_item_id IS NULL
3742 then
3743
3744 --Default attributes using db record
3745 x_cost_rec.inventory_item_id := l_db_det_rec.inventory_item_id;
3746
3747 else
3748
3749 l_valid_check := IS_ITEM_VALID
3750 (
3751 p_org_id => x_cost_rec.org_id,
3752 p_inventory_item_id => x_cost_rec.inventory_item_id,
3753 x_msg_data => l_msg_data,
3754 x_msg_count => l_msg_count,
3755 x_return_status => l_return_status
3756 );
3757
3758 if l_valid_check ='Y' then
3759 --assign the values inventory_item_id to out record
3760 x_cost_rec.inventory_item_id := pv_cost_rec.inventory_item_id ;
3761 else --throw the error message and stop processing
3762 Add_Invalid_Argument_Msg(l_api_name,
3763 to_char(pv_cost_rec.inventory_item_id),
3764 'inventory_item_id');
3765
3766 RAISE FND_API.G_EXC_ERROR;
3767 end if;
3768 end if;
3769
3770
3771 IF x_cost_rec.inventory_item_id <> l_db_det_rec.inventory_item_id THEN
3772 -- Item is changed so recalculate the cost.
3773 --cost will be recalculated if item,ou or quantity changes during updation
3774 l_recalc_cost := 'Y' ;
3775 l_item_changed := 'Y';
3776 END IF;
3777
3778
3779 END IF; --validation_mode
3780
3781 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3782 THEN
3783
3784 FND_LOG.String
3785 ( FND_LOG.level_procedure ,
3786 L_LOG_MODULE || '',
3787 'After Item Id Validation'
3788 );
3789 END If;
3790
3791
3792
3793
3794
3795 IF p_validation_mode = 'I' THEN
3796 if pv_cost_rec.inventory_org_id is not null then
3797
3798 l_valid_check := IS_TXN_INV_ORG_VALID
3799 (p_txn_inv_org => pv_cost_rec.inventory_org_id,
3800 --p_org_id => l_org_id,
3801 p_org_id => x_cost_rec.org_id,
3802 x_msg_data => l_msg_data,
3803 x_msg_count => l_msg_count,
3804 x_return_status => l_return_status ) ;
3805
3806
3807
3808 if l_valid_check ='Y' then
3809
3810 x_cost_rec.inventory_org_id := pv_cost_rec.inventory_org_id ;
3811 else --throw the error message and stop processing
3812 Add_Invalid_Argument_Msg(l_api_name,
3813 to_char(pv_cost_rec.inventory_org_id),
3814 'inventory_org_id');
3815
3816 RAISE FND_API.G_EXC_ERROR;
3817 end if;
3818
3819
3820
3821
3822 end if;
3823
3824 ELSIF p_validation_mode = 'U' THEN
3825
3826 if pv_cost_rec.inventory_org_id = FND_API.G_MISS_NUM OR
3827 pv_cost_rec.inventory_org_id IS NULL
3828 then
3829
3830 --Default attributes using db record
3831 x_cost_rec.inventory_org_id := l_db_det_rec.inventory_org_id;
3832
3833 else
3834
3835 l_valid_check := IS_TXN_INV_ORG_VALID
3836 (p_txn_inv_org => pv_cost_rec.inventory_org_id,
3837 --p_org_id => l_org_id,
3838 p_org_id => x_cost_rec.org_id,
3839 x_msg_data => l_msg_data,
3840 x_msg_count => l_msg_count,
3841 x_return_status => l_return_status ) ;
3842
3843 if l_valid_check ='Y' then
3844 --assign the values inventory_org_id to out record
3845 x_cost_rec.inventory_org_id := pv_cost_rec.inventory_org_id ;
3846 else --throw the error message and stop processing
3847 Add_Invalid_Argument_Msg(l_api_name,
3848 to_char(pv_cost_rec.inventory_org_id),
3849 'inventory_org_id');
3850
3851 RAISE FND_API.G_EXC_ERROR;
3852 end if;
3853 end if;
3854
3855
3856
3857 END IF; --validation_mode
3858
3859 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3860 THEN
3861
3862 FND_LOG.String
3863 ( FND_LOG.level_procedure ,
3864 L_LOG_MODULE || '',
3865 'After Inventory Org Id Validation'
3866 );
3867 END If;
3868
3869
3870
3871 --------------------------------------------------------
3872 /* 5.transaction Billing Type Validation
3873 */
3874
3875 IF p_validation_mode = 'I' THEN
3876
3877 get_txn_billing_type(p_api_name => p_api_name,
3878 p_inv_id => x_cost_rec.inventory_item_id,
3879 p_txn_type_id => x_cost_rec.transaction_type_id,
3880 x_txn_billing_type_id => l_txn_billing_type_id,
3881 x_msg_data => l_msg_data,
3882 x_msg_count => l_msg_count,
3883 x_return_status => l_return_status);
3884
3885
3886 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3887 RAISE FND_API.G_EXC_ERROR ;
3888 ELSE
3889 IF pv_cost_rec.txn_billing_type_id IS NOT NULL THEN
3890 IF pv_cost_rec.txn_billing_type_id <> l_txn_billing_type_id THEN
3891 --RAISE ERROR
3892 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_TXN_BILLING_TYP');
3893 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3894 FND_MSG_PUB.ADD;
3895 RAISE FND_API.G_EXC_ERROR;
3896 ELSE --
3897 --the ids match
3898 --assign to the out record
3899 x_cost_rec.txn_billing_type_id := pv_cost_rec.txn_billing_type_id ;
3900
3901
3902 END IF;
3903
3904 ELSE
3905 -- pv_cost_rec.txn_billing_type_id is null
3906 -- assign l_txn_billing_type_id to out record
3907 x_cost_rec.txn_billing_type_id := l_txn_billing_type_id;
3908 END IF;
3909 VALIDATE_OPERATING_UNIT(p_api_name => l_api_name_full,
3910 p_txn_billing_type_id => l_txn_billing_type_id,
3911 x_return_status => l_return_status,
3912 x_msg_count => l_msg_count,
3913 x_msg_data => l_msg_data);
3914
3915 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3916 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_OU_BILLING_TYP');
3917 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3918 FND_MSG_PUB.ADD;
3919 RAISE FND_API.G_EXC_ERROR;
3920 End if;
3921
3922 END IF;
3923 ELSIF p_validation_mode = 'U' THEN
3924
3925 IF l_item_changed = 'Y' OR
3926 l_transaction_type_changed = 'Y' THEN
3927
3928 --need to get the txn billing type for changed parameters
3929
3930 GET_TXN_BILLING_TYPE(P_API_NAME => p_api_name,
3931 P_INV_ID => x_cost_rec.inventory_item_id,
3932 P_TXN_TYPE_ID => x_cost_rec.transaction_type_id,
3933 X_TXN_BILLING_TYPE_ID => l_txn_billing_type_id,
3934 X_MSG_DATA => l_msg_data,
3935 X_MSG_COUNT => l_msg_count,
3936 X_RETURN_STATUS => l_return_status);
3937
3938 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3939 RAISE FND_API.G_EXC_ERROR ;
3940 ELSE
3941 VALIDATE_OPERATING_UNIT(p_api_name => p_api_name,
3942 p_txn_billing_type_id => l_txn_billing_type_id,
3943 x_return_status => l_return_status,
3944 x_msg_count => l_msg_count,
3945 x_msg_data => l_msg_data);
3946
3947 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3948 RAISE FND_API.G_EXC_ERROR ;
3949 ELSE
3950 IF pv_cost_rec.txn_billing_type_id <> FND_API.G_MISS_NUM AND
3951 pv_cost_rec.txn_billing_type_id IS NOT NULL THEN
3952 IF pv_cost_rec.txn_billing_type_id <> l_txn_billing_type_id THEN
3953
3954 --RAISE ERROR
3955 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_TXN_BILLING_TYP');
3956 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3957 FND_MSG_PUB.ADD;
3958 RAISE FND_API.G_EXC_ERROR;
3959 ELSE --
3960 --the ids match
3961 --assign to the out record
3962 x_cost_rec.txn_billing_type_id := pv_cost_rec.txn_billing_type_id ;
3963 END IF;
3964 ELSE
3965 -- pv_cost_rec.txn_billing_type_id is not passed
3966 -- assign l_txn_billing_type_id to out record
3967 x_cost_rec.txn_billing_type_id := l_txn_billing_type_id;
3968 END IF;
3969 END IF;
3970 END IF;
3971 ELSE
3972
3973 -- niether the item nor the transaction type is changed
3974 -- assign the billing type from db
3975 x_cost_rec.txn_billing_type_id := l_db_det_rec.txn_billing_type_id;
3976
3977 END IF;
3978
3979 --DBMS_OUTPUT.PUT_LINE('Completed the txn billing type id');
3980
3981 END IF;
3982
3983 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3984 THEN
3985 FND_LOG.String
3986 ( FND_LOG.level_procedure ,
3987 L_LOG_MODULE || '',
3988 'After Billing Type Validation'
3989 );
3990 END IF;
3991
3992
3993
3994 --------------------------------------------------------
3995
3996
3997 /* 6.Unit of Measure
3998 Check if the passed value for the UOM is valid
3999 If not passed then default the Item' primary UOM
4000 */
4001
4002
4003
4004 IF p_validation_mode = 'I' THEN
4005
4006 IF pv_cost_rec.unit_of_measure_code IS NOT NULL THEN
4007
4008 l_valid_check := IS_UOM_VALID
4009 (
4010 p_inv_id => x_cost_rec.inventory_item_id,
4011 p_org_id => x_cost_rec.org_id,
4012 p_uom_code => pv_cost_rec.unit_of_measure_code,
4013 x_msg_data => l_msg_data,
4014 x_msg_count => l_msg_count,
4015 x_return_status => l_return_status
4016 );
4017
4018 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4020 ELSIF l_return_status = G_RET_STS_ERROR THEN
4021 RAISE FND_API.G_EXC_ERROR;
4022 END IF;
4023
4024 IF l_valid_check <> 'Y' THEN
4025 Add_Invalid_Argument_Msg(l_api_name,
4026 pv_cost_rec.unit_of_measure_code,
4027 'Unit_of_Measure_Code');
4028 RAISE FND_API.G_EXC_ERROR;
4029
4030 ELSE
4031 --assign to out record
4032 x_cost_rec.unit_of_measure_code := pv_cost_rec.unit_of_measure_code;
4033 END IF;
4034
4035 ELSE --If UOM is not passed then default the Item's Primary UOM
4036
4037
4038 GET_PRIMARY_UOM(P_INVentory_item_ID => x_cost_rec.inventory_item_id,
4039 p_org_id => x_cost_rec.org_id,
4040 X_PRIMARY_UOM => l_primary_uom,
4041 X_MSG_DATA => l_msg_data ,
4042 X_MSG_COUNT => l_msg_count,
4043 X_RETURN_STATUS => l_return_status);
4044
4045 --DBMS_OUTPUT.PUT_LINE('Back from GET_PRIMARY_UOM status='||l_return_status || ' l_primary_uom '||l_primary_uom);
4046
4047 --IF l_return_status <> 'S' THEN
4048 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4049 --raise error
4050 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4051 FND_MESSAGE.SET_TOKEN('INV_ID', x_cost_rec.inventory_item_id);
4052 FND_MSG_PUB.ADD;
4053 RAISE FND_API.G_EXC_ERROR;
4054 END IF;
4055
4056 --assign to out record
4057 x_cost_rec.unit_of_measure_code := l_primary_uom;
4058
4059 END IF;
4060 ELSIF p_validation_mode = 'U' THEN
4061
4062 IF pv_cost_rec.unit_of_measure_code <> FND_API.G_MISS_CHAR AND
4063 pv_cost_rec.unit_of_measure_code IS NOT NULL
4064
4065 then
4066
4067
4068 l_valid_check := IS_UOM_VALID(
4069 p_inv_id => x_cost_rec.inventory_item_id,
4070 p_org_id => x_cost_rec.org_id,
4071 p_uom_code => pv_cost_rec.unit_of_measure_code,
4072 x_msg_data => l_msg_data,
4073 x_msg_count => l_msg_count,
4074 x_return_status => l_return_status
4075 );
4076
4077 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4079 ELSIF l_return_status = G_RET_STS_ERROR THEN
4080 RAISE FND_API.G_EXC_ERROR;
4081 END IF;
4082
4083 IF l_valid_check <> 'Y' THEN
4084 Add_Invalid_Argument_Msg(l_api_name,
4085 pv_cost_rec.unit_of_measure_code,
4086 'Unit_of_Measure_Code');
4087 RAISE FND_API.G_EXC_ERROR;
4088
4089 ELSE
4090 --assign to out record
4091 x_cost_rec.unit_of_measure_code := pv_cost_rec.unit_of_measure_code;
4092 END IF;
4093
4094 Else --If UOM is not passed then default the Item's Primary UOM
4095
4096
4097 GET_PRIMARY_UOM(P_INVentory_item_ID => x_cost_rec.inventory_item_id,
4098 p_org_id => x_cost_rec.org_id,
4099 X_PRIMARY_UOM => l_primary_uom,
4100 X_MSG_DATA => l_msg_data ,
4101 X_MSG_COUNT => l_msg_count,
4102 X_RETURN_STATUS => l_return_status);
4103
4104
4105
4106 --IF l_return_status <> 'S' THEN
4107 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4108 --raise error
4109 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4110 FND_MESSAGE.SET_TOKEN('INV_ID', x_cost_rec.inventory_item_id);
4111 FND_MSG_PUB.ADD;
4112 RAISE FND_API.G_EXC_ERROR;
4113 end if;
4114
4115 --assign to out record
4116 x_cost_rec.unit_of_measure_code := l_primary_uom;
4117
4118 End if;
4119 END IF;
4120
4121 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4122 THEN
4123 FND_LOG.String
4124 ( FND_LOG.level_procedure ,
4125 L_LOG_MODULE || '',
4126 'After UOM Validation'
4127 );
4128 END IF;
4129
4130
4131
4132 -----------------------------------------------
4133 /* 7.Currency Code
4134 Check if the passed value for the Currenc is valid
4135 If not passed then default the Currency from the Service Request Operating Unit
4136 */
4137
4138 IF p_validation_mode = 'I' THEN
4139
4140 IF pv_cost_rec.currency_code IS NOT NULL THEN
4141
4142 l_valid_check := IS_CURRENCY_CODE_VALID
4143 (
4144 p_currency_Code => pv_cost_rec.currency_code,
4145 x_msg_data => l_msg_data,
4146 x_msg_count => l_msg_count,
4147 x_return_status => l_return_status
4148 );
4149
4150 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4152 ELSIF l_return_status = G_RET_STS_ERROR THEN
4153 RAISE FND_API.G_EXC_ERROR;
4154 END IF;
4155
4156 IF l_valid_check <> 'Y' THEN
4157 Add_Invalid_Argument_Msg(l_api_name,
4158 pv_cost_rec.currency_code,
4159 'Currency Code');
4160 RAISE FND_API.G_EXC_ERROR;
4161
4162 ELSE
4163 --assign to out record
4164 x_cost_rec.currency_code := pv_cost_rec.currency_code;
4165 END IF;
4166
4167 ELSE --If UOM is not passed then default the Item's Primary UOM
4168
4169
4170 get_currency_code
4171 (
4172 p_org_id => x_cost_rec.org_id,
4173 X_CURRENCY_CODE => l_currency_code,
4174 X_MSG_DATA => l_msg_data ,
4175 X_MSG_COUNT => l_msg_count,
4176 X_RETURN_STATUS => l_return_status);
4177
4178 --DBMS_OUTPUT.PUT_LINE('Back from GET_PRIMARY_UOM status='||l_return_status || ' l_primary_uom '||l_primary_uom);
4179
4180 --IF l_return_status <> 'S' THEN
4181 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4182 --raise error
4183 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4184 FND_MESSAGE.SET_TOKEN('INV_ID', pv_cost_rec.unit_of_measure_code);
4185 FND_MSG_PUB.ADD;
4186 RAISE FND_API.G_EXC_ERROR;
4187 END IF;
4188
4189 --assign to out record
4190 x_cost_rec.currency_code := l_currency_code;
4191
4192 END IF;
4193 ELSIF p_validation_mode = 'U' THEN
4194
4195 IF pv_cost_rec.currency_code<> FND_API.G_MISS_CHAR AND
4196 pv_cost_rec.currency_code IS NOT NULL
4197
4198 then
4199
4200
4201 l_valid_check := IS_CURRENCY_CODE_VALID
4202 (
4203 p_currency_Code => pv_cost_rec.currency_code,
4204 x_msg_data => l_msg_data,
4205 x_msg_count => l_msg_count,
4206 x_return_status => l_return_status
4207 );
4208
4209 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4211 ELSIF l_return_status = G_RET_STS_ERROR THEN
4212 RAISE FND_API.G_EXC_ERROR;
4213 END IF;
4214
4215 IF l_valid_check <> 'Y' THEN
4216 Add_Invalid_Argument_Msg(l_api_name,
4217 pv_cost_rec.currency_code,
4218 'Currency Code');
4219 RAISE FND_API.G_EXC_ERROR;
4220
4221 ELSE
4222 --assign to out record
4223 x_cost_rec.currency_code := pv_cost_rec.currency_code;
4224 END IF;
4225
4226 Else --If UOM is not passed then default the Item's Primary UOM
4227
4228
4229 get_currency_code
4230 (
4231 p_org_id => x_cost_rec.org_id,
4232 X_CURRENCY_CODE => l_currency_code,
4233 X_MSG_DATA => l_msg_data ,
4234 X_MSG_COUNT => l_msg_count,
4235 X_RETURN_STATUS => l_return_status);
4236
4237
4238
4239
4240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4241 --raise error
4242 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4243 FND_MESSAGE.SET_TOKEN('INV_ID', pv_cost_rec.unit_of_measure_code);
4244 FND_MSG_PUB.ADD;
4245 RAISE FND_API.G_EXC_ERROR;
4246 END IF;
4247
4248 --assign to out record
4249 x_cost_rec.currency_code := l_currency_code;
4250
4251
4252 End if;
4253 END IF;
4254
4255 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4256 THEN
4257 FND_LOG.String
4258 ( FND_LOG.level_procedure ,
4259 L_LOG_MODULE || '',
4260 'After Currency Code Validation'
4261 );
4262 END IF;
4263
4264
4265 EXCEPTION
4266
4267
4268 WHEN FND_API.G_EXC_ERROR THEN
4269 x_return_status := FND_API.G_RET_STS_ERROR;
4270
4271 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4272 p_data => x_msg_data,
4273 p_encoded => FND_API.G_FALSE) ;
4274 WHEN OTHERS THEN
4275 x_return_status := FND_API.G_RET_STS_ERROR;
4276 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4277 p_data => x_msg_data,
4278 p_encoded => FND_API.G_FALSE) ;
4279
4280
4281
4282
4283 END VALIDATE_COST_DETAILS;
4284
4285
4286 --=============================
4287 -- Record_Is_Locked_msg
4288 --=============================
4289
4290 PROCEDURE Record_Is_Locked_Msg
4291 ( p_token_an VARCHAR2
4292 )
4293 IS
4294
4295 BEGIN
4296
4297 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_CANT_LOCK_RECORD');
4298 FND_MESSAGE.Set_Token('API_NAME', p_token_an);
4299 FND_MSG_PUB.Add;
4300 END Record_IS_Locked_Msg;
4301
4302
4303 PROCEDURE Validate_Who_Info(
4304 P_API_NAME IN VARCHAR2,
4305 P_USER_ID IN NUMBER,
4306 P_LOGIN_ID IN NUMBER,
4307 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
4308
4309 CURSOR c_user IS
4310 SELECT 1
4311 FROM fnd_user
4312 WHERE user_id = p_user_id
4313 AND TRUNC(SYSDATE) <= start_date
4314 AND NVL(end_date, SYSDATE) >= SYSDATE;
4315
4316 CURSOR c_login IS
4317 SELECT 1
4318 FROM fnd_logins
4319 WHERE login_id = p_login_id
4320 AND user_id = p_user_id;
4321
4322 l_dummy VARCHAR2(1);
4323
4324 BEGIN
4325
4326 x_return_status := FND_API.G_RET_STS_SUCCESS;
4327
4328 BEGIN
4329 IF p_user_id = -1 then
4330 SELECT 'x' into l_dummy
4331 FROM fnd_user
4332 WHERE user_id = p_user_id;
4333 ELSE
4334 SELECT 'x' into l_dummy
4335 FROM fnd_user
4336 WHERE user_id = p_user_id
4337 AND trunc(sysdate) BETWEEN trunc(nvl(start_date, sysdate))
4338 AND trunc(nvl(end_date, sysdate));
4339 END IF;
4340
4341 EXCEPTION
4342 WHEN NO_DATA_FOUND THEN
4343 x_return_status := FND_API.G_RET_STS_ERROR;
4344 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
4345 p_token_v => TO_CHAR(p_user_id),
4346 p_token_p => 'p_user_id');
4347 return;
4348 END;
4349
4350 IF p_login_id is not null then
4351 BEGIN
4352 SELECT 'x' into l_dummy
4353 FROM fnd_logins
4354 WHERE login_id = p_login_id
4355 AND user_id = p_user_id;
4356
4357 EXCEPTION
4358 WHEN NO_DATA_FOUND THEN
4359 x_return_status := FND_API.G_RET_STS_ERROR;
4360 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
4361 p_token_v => TO_CHAR(p_login_id),
4362 p_token_p => 'p_user_login');
4363 END;
4364 END IF;
4365
4366 END Validate_Who_Info;
4367 /**************************************************
4368 Private Procedure Body TO_NULL
4369 **************************************************/
4370
4371 PROCEDURE TO_NULL(p_cost_rec_in IN cs_cost_details_pub.Cost_Rec_Type,
4372 p_cost_rec_out OUT NOCOPY cs_cost_details_pub.Cost_Rec_Type) IS
4373 BEGIN
4374 p_cost_rec_out.cost_id := Check_For_Miss(p_cost_rec_in.cost_id) ;
4375 p_cost_rec_out.incident_id := Check_For_Miss(p_cost_rec_in.incident_id) ;
4376 p_cost_rec_out.estimate_Detail_id := Check_For_Miss(p_cost_rec_in.estimate_Detail_id) ;
4377 p_cost_rec_out.charge_line_type := Check_For_Miss(p_cost_rec_in.charge_line_type) ;
4378 p_cost_rec_out.transaction_type_id := Check_For_Miss(p_cost_rec_in.transaction_type_id) ;
4379 p_cost_rec_out.txn_billing_type_id := Check_For_Miss(p_cost_rec_in.txn_billing_type_id) ;
4380 p_cost_rec_out.inventory_item_id := Check_For_Miss(p_cost_rec_in.inventory_item_id) ;
4381 p_cost_rec_out. quantity := Check_For_Miss(p_cost_rec_in. quantity) ;
4382 p_cost_rec_out.unit_of_measure_code := Check_For_Miss(p_cost_rec_in.unit_of_measure_code) ;
4383 p_cost_rec_out.currency_code := Check_For_Miss(p_cost_rec_in.currency_code) ;
4384 p_cost_rec_out.source_id := Check_For_Miss(p_cost_rec_in.source_id) ;
4385 p_cost_rec_out.source_code := Check_For_Miss(p_cost_rec_in.source_code) ;
4386 p_cost_rec_out.org_id := Check_For_Miss(p_cost_rec_in.org_id) ;
4387 p_cost_rec_out.inventory_org_id := Check_For_Miss(p_cost_rec_in.inventory_org_id) ;
4388 --p_cost_rec_out.unit_cost := Check_For_Miss(p_cost_rec_in.unit_cost) ;
4389 p_cost_rec_out.extended_cost := Check_For_Miss(p_cost_rec_in.extended_cost) ;
4390 --p_cost_rec_out.override_ext_cost_flag := Check_For_Miss(p_cost_rec_in.override_ext_cost_flag) ;
4391 -- p_cost_rec_out.transaction_date := Check_For_Miss(p_cost_rec_in.transaction_date) ;
4392 p_cost_rec_out.attribute1 := Check_For_Miss(p_cost_rec_in.attribute1) ;
4393 p_cost_rec_out.attribute2 := Check_For_Miss(p_cost_rec_in.attribute2) ;
4394 p_cost_rec_out.attribute3 := Check_For_Miss(p_cost_rec_in.attribute3) ;
4395 p_cost_rec_out.attribute4 := Check_For_Miss(p_cost_rec_in.attribute4) ;
4396 p_cost_rec_out.attribute5 := Check_For_Miss(p_cost_rec_in.attribute5) ;
4397 p_cost_rec_out.attribute6 := Check_For_Miss(p_cost_rec_in.attribute6) ;
4398 p_cost_rec_out.attribute7 := Check_For_Miss(p_cost_rec_in.attribute7) ;
4399 p_cost_rec_out.attribute8 := Check_For_Miss(p_cost_rec_in.attribute8) ;
4400 p_cost_rec_out.attribute9 := Check_For_Miss(p_cost_rec_in.attribute9) ;
4401 p_cost_rec_out.attribute10 := Check_For_Miss(p_cost_rec_in.attribute10) ;
4402 p_cost_rec_out.attribute11 := Check_For_Miss(p_cost_rec_in.attribute11) ;
4403 p_cost_rec_out.attribute12 := Check_For_Miss(p_cost_rec_in.attribute12) ;
4404 p_cost_rec_out.attribute13 := Check_For_Miss(p_cost_rec_in.attribute13) ;
4405 p_cost_rec_out.attribute14 := Check_For_Miss(p_cost_rec_in.attribute14) ;
4406 p_cost_rec_out.attribute15 := Check_For_Miss(p_cost_rec_in.attribute15) ;
4407 END TO_NULL;
4408
4409 /*************************************************
4410 Function Implementations
4411 **************************************************/
4412 FUNCTION Check_For_Miss ( p_param IN NUMBER ) RETURN NUMBER IS
4413 BEGIN
4414
4415 IF p_param = FND_API.G_MISS_NUM THEN
4416 RETURN NULL ;
4417 ELSE
4418 RETURN p_param ;
4419 END IF ;
4420 END Check_For_Miss ;
4421
4422
4423 FUNCTION Check_For_Miss ( p_param IN VARCHAR2 ) RETURN VARCHAR2 IS
4424 BEGIN
4425 IF p_param = FND_API.G_MISS_CHAR THEN
4426 RETURN NULL ;
4427 ELSE
4428 RETURN p_param ;
4429 END IF ;
4430 END Check_For_Miss ;
4431
4432
4433 FUNCTION Check_For_Miss ( p_param IN DATE ) RETURN DATE IS
4434 BEGIN
4435 IF p_param = FND_API.G_MISS_DATE THEN
4436 RETURN NULL ;
4437 ELSE
4438 RETURN p_param ;
4439 END IF ;
4440 END Check_For_Miss ;
4441
4442 --------------------------------------------------------------------------------
4443 -- Procedure Name : PURGE_COST
4444 --
4445 -- Parameters (other than standard ones)
4446 -- IN
4447 -- p_object_type : Type of object for which this procedure is
4448 -- being called. (Here it will be 'SR')
4449 -- p_processing_set_id : Id that helps the API in identifying the
4450 -- set of SRs for which the child objects have
4451 -- to be deleted.
4452 --
4453 -- Description
4454 -- This procedure physically deletes all the cost lines attached to
4455 -- a service request. It reads the list of SRs for which the cost lines
4456 -- have to be deleted from the global temp table, looking only for rows
4457 -- having the purge_status as NULL. Using Set processing, the procedure
4458 -- deletes all the cost lines attached to such SRs.
4459 --
4460 -- HISTORY
4461 --
4462 ----------------+------------+--------------------------------------------------
4463 -- DATE | UPDATED BY | Change Description
4464 ----------------+------------+--------------------------------------------------
4465 -- 26-dec-2007 | bkanimoz | Created
4466 -- | |
4467 ----------------+------------+--------------------------------------------------
4468 PROCEDURE Purge_Cost
4469 (
4470 p_api_version_number IN NUMBER := 1.0
4471 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4472 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4473 , p_object_type IN VARCHAR2
4474 , p_processing_set_id IN NUMBER
4475 , x_return_status OUT NOCOPY VARCHAR2
4476 , x_msg_count OUT NOCOPY NUMBER
4477 , x_msg_data OUT NOCOPY VARCHAR2
4478 )
4479 IS
4480 --------------------------------------------------------------------------------
4481
4482 L_API_VERSION CONSTANT NUMBER := 1.0;
4483 L_API_NAME CONSTANT VARCHAR2(30) := 'PURGE_COST';
4484 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
4485 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'csxvcstb.plsql.' || L_API_NAME_FULL || '.';
4486
4487 l_row_count NUMBER := 0;
4488
4489 BEGIN
4490 x_return_status := FND_API.G_RET_STS_SUCCESS;
4491
4492 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4493 THEN
4494 FND_LOG.String
4495 (
4496 FND_LOG.level_procedure
4497 , L_LOG_MODULE || 'start'
4498 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
4499 );
4500 FND_LOG.String
4501 (
4502 FND_LOG.level_procedure
4503 , L_LOG_MODULE || 'param 1'
4504 , 'p_api_version_number:' || p_api_version_number
4505 );
4506 FND_LOG.String
4507 (
4508 FND_LOG.level_procedure
4509 , L_LOG_MODULE || 'param 2'
4510 , 'p_init_msg_list:' || p_init_msg_list
4511 );
4512 FND_LOG.String
4513 (
4514 FND_LOG.level_procedure
4515 , L_LOG_MODULE || 'param 3'
4516 , 'p_commit:' || p_commit
4517 );
4518 FND_LOG.String
4519 (
4520 FND_LOG.level_procedure
4521 , L_LOG_MODULE || 'param 4'
4522 , 'p_object_type:' || p_object_type
4523 );
4524 FND_LOG.String
4525 (
4526 FND_LOG.level_procedure
4527 , L_LOG_MODULE || 'param 5'
4528 , 'p_processing_set_id:' || p_processing_set_id
4529 );
4530 END IF ;
4531
4532 IF NOT FND_API.Compatible_API_Call
4533 (
4534 L_API_VERSION
4535 , p_api_version_number
4536 , L_API_NAME
4537 , G_PKG_NAME
4538 )
4539 THEN
4540 FND_MSG_PUB.Count_And_Get
4541 (
4542 p_count => x_msg_count
4543 , p_data => x_msg_data
4544 );
4545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4546 END IF ;
4547
4548 IF FND_API.to_Boolean(p_init_msg_list)
4549 THEN
4550 FND_MSG_PUB.initialize;
4551 END IF ;
4552
4553 ------------------------------------------------------------------------------
4554 -- Parameter Validations:
4555 ------------------------------------------------------------------------------
4556
4557 IF NVL(p_object_type, 'X') <> 'SR'
4558 THEN
4559 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4560 THEN
4561 FND_LOG.String
4562 (
4563 FND_LOG.level_unexpected
4564 , L_LOG_MODULE || 'object_type_invalid'
4565 , 'p_object_type has to be SR.'
4566 );
4567 END IF ;
4568
4569 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
4570 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4571 FND_MESSAGE.Set_Token('PARAM', 'p_object_type');
4572 FND_MESSAGE.Set_Token('CURRVAL', p_object_type);
4573 FND_MSG_PUB.ADD;
4574
4575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4576 END IF;
4577
4578 ---
4579
4580 IF p_processing_set_id IS NULL
4581 THEN
4582 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4583 THEN
4584 FND_LOG.String
4585 (
4586 FND_LOG.level_unexpected
4587 , L_LOG_MODULE || 'proc_set_id_invalid'
4588 , 'p_processing_set_id should not be NULL.'
4589 );
4590 END IF ;
4591
4592 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
4593 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4594 FND_MESSAGE.Set_Token('PARAM', 'p_processing_set_id');
4595 FND_MESSAGE.Set_Token('CURRVAL', NVL(to_char(p_processing_set_id),'NULL'));
4596 FND_MSG_PUB.ADD;
4597
4598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4599 END IF;
4600
4601 ------------------------------------------------------------------------------
4602 -- Actual Logic starts below:
4603 ------------------------------------------------------------------------------
4604
4605 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4606 THEN
4607 FND_LOG.String
4608 (
4609 FND_LOG.level_statement
4610 , L_LOG_MODULE || 'del_cost_line_start'
4611 , 'deleting cost lines against SRs in the global temp table'
4612 );
4613 END IF ;
4614
4615 -- Delete all the estimate lines that correspond to the
4616 -- SRs that are available for purge after validations.
4617
4618 DELETE /*+ index(e) */ cs_cost_details e
4619 WHERE
4620 incident_id IN
4621 (
4622 SELECT /*+ no_unnest no_semijoin cardinality(10) */
4623 object_id
4624 FROM
4625 jtf_object_purge_param_tmp
4626 WHERE
4627 processing_set_id = p_processing_set_id
4628 AND object_type = 'SR'
4629 AND NVL(purge_status, 'S') = 'S'
4630 );
4631
4632 l_row_count := SQL%ROWCOUNT;
4633
4634 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4635 THEN
4636 FND_LOG.String
4637 (
4638 FND_LOG.level_statement
4639 , L_LOG_MODULE || 'del_chg_line_end'
4640 , 'after deleting cost lines against SRs in the global temp table'
4641 || l_row_count || ' rows deleted.'
4642 );
4643 END IF ;
4644
4645 ---
4646
4647 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4648 THEN
4649 FND_LOG.String
4650 (
4651 FND_LOG.level_procedure
4652 , L_LOG_MODULE || 'end'
4653 , 'Completed work in ' || L_API_NAME_FULL || ' successfully'
4654 );
4655 END IF ;
4656
4657 EXCEPTION
4658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4660
4661 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4662 THEN
4663 FND_LOG.String
4664 (
4665 FND_LOG.level_unexpected
4666 , L_LOG_MODULE || 'unexpected_error'
4667 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
4668 );
4669 END IF ;
4670
4671 WHEN OTHERS THEN
4672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4673 FND_MESSAGE.Set_Name('CS', 'CS_COST_LINE_DEL_FAIL');
4674 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4675 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
4676 FND_MSG_PUB.ADD;
4677
4678 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4679 THEN
4680 FND_LOG.String
4681 (
4682 FND_LOG.level_unexpected
4683 , L_LOG_MODULE || 'when_others'
4684 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
4685 );
4686 FND_LOG.String
4687 (
4688 FND_LOG.level_unexpected
4689 , L_LOG_MODULE || 'when_others'
4690 , SQLERRM
4691 );
4692 END IF ;
4693 END Purge_Cost;
4694
4695 PROCEDURE get_currency_converted_value(
4696 p_from_currency IN VARCHAR2,
4697 p_to_currency IN VARCHAR2,
4698 p_value IN NUMBER,
4699 p_ou IN VARCHAR2,
4700 x_value OUT NOCOPY NUMBER
4701 ) IS
4702
4703 p_api_name VARCHAR(100);
4704 l_rate NUMBER;
4705 l_numerator NUMBER;
4706 l_denominator NUMBER;
4707 l_return_status VARCHAR2(1);
4708 l_call_api VARCHAR2(1):='Y';
4709 l_conversion_type VARCHAR2(30) := FND_PROFILE.VALUE('CS_CHG_DEFAULT_CONVERSION_TYPE');
4710 l_max_roll_days NUMBER := to_number(FND_PROFILE.VALUE('CS_CHG_MAX_ROLL_DAYS'));
4711 l_from_currency VARCHAR2(15);
4712
4713 BEGIN
4714
4715 l_from_currency :=p_from_currency;
4716
4717
4718 if l_from_currency is null and p_ou is not null then
4719
4720 begin
4721 select currency_code
4722 into l_from_currency
4723 from gl_sets_of_books
4724 where name = p_ou;
4725 exception
4726 when no_data_found then
4727 x_value := p_value;
4728 l_call_api := 'N';
4729 end ;
4730
4731 end if;
4732
4733
4734 if l_call_api ='Y' then
4735
4736 gl_currency_api.get_closest_triangulation_rate
4737 (
4738 x_from_currency =>p_from_currency,
4739 x_to_currency => p_to_currency,
4740 x_conversion_date => SYSDATE,
4741 x_conversion_type => l_conversion_type,--l_conversion_type,
4742 x_max_roll_days => l_max_roll_days,-- l_max_roll_days,
4743 x_denominator => l_denominator,
4744 x_numerator => l_numerator,
4745 x_rate => l_rate );
4746
4747 x_value := l_rate * p_value;
4748
4749 end if;
4750
4751 EXCEPTION
4752 WHEN OTHERS THEN
4753 x_value:=p_value;
4754 END get_currency_converted_value;
4755
4756
4757 END CS_Cost_Details_PVT;