[Home] [Help]
PACKAGE BODY: APPS.CS_COST_DETAILS_PVT
Source
1 PACKAGE BODY CS_Cost_Details_PVT AS
2 /* $Header: csxvcstb.pls 120.22.12010000.1 2008/07/24 18:46:41 appldev 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
1415 CURSOR c_check_cost_exst is
1416 SELECT cost_id
1417 FROM cs_cost_details
1418 WHERE estimate_Detail_id = p_estimate_detail_id;
1419
1420
1421 BEGIN
1422 -- Standard start of API savepoint
1423 IF FND_API.To_Boolean(p_transaction_control) THEN
1424 SAVEPOINT Create_Cost_Details_PVT;
1425 END IF ;
1426 -- Standard call to check for call compatibility
1427 IF NOT FND_API.Compatible_API_Call(l_api_version,
1428 p_api_version,
1429 l_api_name,
1430 G_PKG_NAME) THEN
1431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1432 END IF;
1433 -- Initialize message list if p_init_msg_list is set to TRUE
1434 IF FND_API.To_Boolean(p_init_msg_list) THEN
1435 FND_MSG_PUB.Initialize;
1436 END IF;
1437 -- Initialize API return status to success
1438 x_return_status := FND_API.G_RET_STS_SUCCESS;
1439
1440 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1441 THEN
1442 FND_LOG.String
1443 ( FND_LOG.level_procedure ,
1444 L_LOG_MODULE || '',
1445 'Inside Create_cost_details PVT API:'
1446 );
1447 END IF;
1448
1449
1450 ----------------------- FND Logging -----------------------------------
1451 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1452 THEN
1453
1454
1455 FND_LOG.String
1456 ( FND_LOG.level_procedure ,
1457 L_LOG_MODULE || 'start'
1458 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
1459 );
1460 FND_LOG.String
1461 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1462 , 'p_api_version:' || p_api_version
1463 );
1464 FND_LOG.String
1465 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1466 , 'p_init_msg_list:' || p_init_msg_list
1467 );
1468 FND_LOG.String
1469 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1470 , 'p_commit:' || p_commit
1471 );
1472 FND_LOG.String
1473 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1474 , 'p_validation_level:' || p_validation_level
1475 );
1476 FND_LOG.String
1477 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1478 , 'p_resp_appl_id:' || p_resp_appl_id
1479 );
1480 FND_LOG.String
1481 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1482 , 'p_resp_id:' || p_resp_id
1483 );
1484 FND_LOG.String
1485 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1486 , 'p_user_id:' || p_user_id
1487 );
1488 FND_LOG.String
1489 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1490 , 'p_login_id:' || p_login_id
1491 );
1492 FND_LOG.String
1493 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1494 , 'p_transaction_control:' || p_transaction_control
1495 );
1496 FND_LOG.String
1497 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1498 , 'p_cost_creation_override: ' || p_cost_creation_override
1499 );
1500 -- --------------------------------------------------------------------------
1501 -- This procedure Logs the charges record paramters.
1502 -- --------------------------------------------------------------------------
1503 CS_COST_DETAILS_PUB.Log_Cost_Rec_Parameters
1504 ( p_cost_Rec_in => p_cost_rec
1505 );
1506
1507 END IF;
1508
1509 --Convert the IN Parameters from FND_API.G_MISS_XXXX to NULL
1510 --if no value is passed then return NULL otherwise return the value passed
1511
1512 TO_NULL (p_cost_Rec, l_cost_Rec) ;
1513
1514 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1515 THEN
1516 FND_LOG.String
1517 ( FND_LOG.level_procedure ,
1518 L_LOG_MODULE || '',
1519 'l_cost_Rec.estimate_Detail_id : '||l_cost_Rec.estimate_Detail_id
1520 );
1521 END IF;
1522
1523 -- If estimate_Detail_Id is passed to the Create Cost Details API, then get all the data necessry from the charges table .
1524 -- In this case , if any other parameters are passed then they will not be validated
1525
1526 -- If estimate_detail_id is not passed then validate all the parameters passed
1527
1528 if l_cost_Rec.estimate_Detail_id is not null then
1529
1530 p_estimate_detail_id :=l_cost_Rec.estimate_Detail_id;
1531
1532 OPEN c_check_cost_exst;
1533 FETCH c_check_cost_exst into lv_cost_id;
1534 CLOSE c_check_cost_exst;
1535
1536 if lv_cost_id is not null then
1537 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CHARGE_EXIST');
1538 FND_MSG_PUB.ADD;
1539 RAISE FND_API.G_EXC_ERROR;
1540 end if;
1541
1542 --call get_charge_details to get the data from cs_estimate_details table
1543 get_charge_details
1544 (
1545 p_api_name => l_api_name_full,
1546 p_estimate_detail_id => l_cost_Rec.estimate_detail_id,
1547 x_incident_id => l_cost_rec.incident_id,
1548 x_transaction_type_id => l_cost_rec.transaction_type_id ,
1549 x_txn_billing_type_id => l_cost_rec.txn_billing_type_id,
1550 x_charge_line_type => l_cost_rec.charge_line_type ,
1551 x_inventory_item_id => l_cost_rec.inventory_item_id ,
1552 x_quantity => l_cost_rec.quantity ,
1553 x_unit_of_measure_code => l_cost_rec.unit_of_measure_code ,
1554 x_currency_code => l_cost_rec.currency_code ,
1555 x_source_id => l_cost_rec.source_id ,
1556 x_source_code => l_cost_rec.source_code ,
1557 x_org_id => l_cost_rec.org_id ,
1558 x_txn_inv_org => l_cost_rec.inventory_org_id,
1559 x_msg_data => x_msg_data,
1560 x_msg_count => x_msg_count,
1561 x_return_status => x_return_status
1562 );
1563
1564 lx_cost_rec:=l_cost_rec;
1565 --Bug 6972425
1566 --start
1567 get_charge_flags_from_sr
1568 (
1569 p_api_name => l_api_name,
1570 p_incident_id => l_cost_rec.incident_id,
1571 p_transaction_type_id => l_cost_rec.transaction_type_id,
1572 x_create_charge_flag => l_create_charge_flag,
1573 x_create_cost_flag => l_create_cost_flag,
1574 x_disallow_request_update => l_disallow_request_update,
1575 x_disallow_new_charge => l_disallow_new_charge,
1576 x_disallow_charge_update => l_disallow_charge_update,
1577 x_msg_data => l_msg_data,
1578 x_msg_count => l_msg_count,
1579 x_return_status => l_return_status
1580 );
1581
1582 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1583 THEN
1584 FND_LOG.String
1585 ( FND_LOG.level_procedure ,
1586 L_LOG_MODULE || '',
1587 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
1588 );
1589
1590 FND_LOG.String
1591 ( FND_LOG.level_procedure ,
1592 L_LOG_MODULE || '',
1593 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
1594 'l_disallow_charge_update: '||l_disallow_charge_update
1595 );
1596 END IF;
1597
1598 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
1599 if l_disallow_request_update='Y' THEN
1600 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
1601 FND_MSG_PUB.ADD;
1602 RAISE FND_API.G_EXC_ERROR;
1603 end if;
1604
1605 Else
1606 if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
1607 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
1608 FND_MSG_PUB.ADD;
1609 RAISE FND_API.G_EXC_ERROR;
1610 end if;
1611 end if;
1612 --end Bug 6972425
1613
1614 end if;--estimate_detail_id not null
1615
1616 l_transaction_type_id := l_cost_rec.transaction_type_id;
1617 l_charge_line_type := l_cost_rec.charge_line_type;
1618
1619 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1620 THEN
1621 FND_LOG.String
1622 ( FND_LOG.level_procedure ,
1623 L_LOG_MODULE || '',
1624 'l_transaction_type_id : '||l_transaction_type_id ||'l_charge_line_type :'||l_charge_line_type
1625 );
1626 END IF;
1627
1628 /* 1. Transaction_Type_Id
1629 Check if 'Create_Cost_Flag' is set for this transaction_Type
1630 */
1631 --if parameter p_cost_creation_override ='Y' then do not check for the Create_Cost flag in SAC setup
1632
1633 IF p_cost_creation_override = 'N' THEN
1634 if l_transaction_type_id is not null then
1635
1636 -- If transaction type id is passed , first check if it is valid.
1637 -- Then Check if the 'Create_Cost_Flag' for this transaction_type_id is checked
1638 -- Then Check if this transaction_type_id is tied to atleast one business process -- check this with rohit
1639
1640 VALIDATE_TRANSACTION_TYPE_ID (
1641 p_api_name => l_api_name_full,
1642 p_transaction_type_id => l_transaction_type_id,
1643 x_line_order_category_code=>l_line_order_category_code,
1644 x_msg_data => l_msg_data,
1645 x_msg_count => l_msg_count,
1646 x_return_status => l_return_status
1647 ) ;
1648
1649 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1650 RAISE FND_API.G_EXC_ERROR ;
1651 Else
1652 l_valid_check := IS_COST_FLAG_CHECKED
1653 (p_transaction_type_id => l_transaction_type_id,
1654 x_msg_data => l_msg_data,
1655 x_msg_count => l_msg_count,
1656 x_return_status => l_return_status
1657 );
1658
1659 if l_valid_check ='Y' then
1660 l_cost_rec.transaction_type_id := l_transaction_type_id;
1661
1662 VALIDATE_BUSINESS_PROCESS (
1663 p_api_name => l_api_name_full,
1664 p_transaction_type_id => l_transaction_type_id,
1665 x_msg_data => l_msg_data,
1666 x_msg_count => l_msg_count,
1667 x_return_status => l_return_status
1668 ) ;
1669
1670 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1671 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_BUS_PROCESS');
1672 FND_MSG_PUB.ADD;
1673 RAISE FND_API.G_EXC_ERROR;
1674 END IF;
1675 Else
1676 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_COST_FLAG');
1677 FND_MSG_PUB.ADD;
1678 RAISE G_WARNING;
1679 end if;
1680 end if;
1681 else -- transaction_type_id is null
1682 Add_Null_Parameter_Msg(l_api_name,
1683 'p_transaction_type_id') ;
1684 RAISE FND_API.G_EXC_ERROR;
1685 end if;
1686 END IF;--p_cost_creation_overrid
1687
1688
1689
1690 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1691 THEN
1692 FND_LOG.String
1693 ( FND_LOG.level_procedure ,
1694 L_LOG_MODULE || '',
1695 'After Mandatory Check 1'
1696 );
1697 END IF;
1698
1699 /*2.Cost Records will be created only for ACTUAL charge lines.
1700 if charge_line_type is passed the value should be 'ACTUAL'
1701 if not passed then default it to 'ACTUAL'*/
1702
1703 if l_charge_line_type is not null then
1704 l_valid_check := IS_CHARGE_LINE_TYPE_VALID
1705 (p_charge_line_type => l_charge_line_type,
1706 x_msg_data => l_msg_data,
1707 x_msg_count => l_msg_count,
1708 x_return_status => l_return_status
1709 );
1710
1711 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1713 ELSIF l_return_status = G_RET_STS_ERROR THEN
1714 RAISE FND_API.G_EXC_ERROR;
1715 END IF;
1716
1717 if l_valid_check ='Y' then
1718 l_cost_rec.charge_line_type := l_charge_line_type;
1719 else --throw the error message and stop processing
1720 Add_Invalid_Argument_Msg(l_api_name,
1721 l_charge_line_type,
1722 'charge_line_type');
1723
1724 RAISE G_WARNING;
1725 end if;
1726 else
1727 l_cost_rec.charge_line_type := 'ACTUAL';
1728 end if;
1729
1730 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1731 THEN
1732 FND_LOG.String
1733 ( FND_LOG.level_procedure ,
1734 L_LOG_MODULE || '',
1735 'After Mandatory Check 2'
1736 );
1737 END IF;
1738 --------------------------------------------------------------------------------------------------------------------------
1739 -- All the Other Validations would be performed only when the Validation_level is FULL .
1740
1741
1742 IF (p_validation_level = FND_API.G_VALID_LEVEL_NONE) THEN
1743
1744
1745 Validate_Who_Info ( p_api_name => l_api_name_full,
1746 p_user_id => NVL(p_user_id, -1),
1747 p_login_id => p_login_id,
1748 x_return_status => l_return_status
1749 );
1750
1751 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1752 RAISE FND_API.G_EXC_ERROR;
1753 END IF;
1754 L_RECALC_COST := 'Y';
1755
1756
1757 ELSIF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
1758
1759
1760 -- Perform all the validations by calling the procedure VALIDATE_COST_DETAILS which inturn does all the validations
1761
1762 if l_cost_Rec.estimate_Detail_id is null then
1763
1764 VALIDATE_COST_DETAILS(
1765 p_api_name => l_api_name,
1766 pv_cost_rec => l_cost_rec,
1767 p_validation_mode => 'I' ,
1768 p_user_id => p_user_id,
1769 p_login_id => p_login_id,
1770 x_cost_rec => lx_cost_rec,
1771 x_msg_data => x_msg_data,
1772 x_msg_count => x_msg_count,
1773 x_return_status => l_return_status
1774 );
1775
1776 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1777 FND_MESSAGE.Set_Name('CS', 'CS_COST_VALIDATE_COST_DTL_ER');
1778 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
1779 FND_MSG_PUB.Add;
1780 RAISE FND_API.G_EXC_ERROR;
1781 END IF;
1782 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1783 THEN
1784 FND_LOG.String
1785 ( FND_LOG.level_procedure ,
1786 L_LOG_MODULE || '',
1787 'After Validating the Cost Details'
1788 );
1789 END IF;
1790
1791 end if;--validation_level
1792 END IF;--p_cost_rec.estimate_Detail_id is not null
1793
1794 --If the Costing API is called from the backend with a SAC of RETURN type then the user can
1795 -- pass a negative Extened Cost.
1796 /*
1797 if sign(lx_cost_rec.extended_cost)=-1
1798 then
1799
1800 Add_Invalid_Argument_Msg(l_api_name_full,
1801 to_char(lx_cost_rec.extended_cost),
1802 'Extended Cost ');
1803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1804 end if;
1805 */
1806 --===============================
1807 ---Quantity validations
1808 --===============================
1809 if sign(lx_cost_rec.quantity) = (0)
1810 then
1811
1812 Add_Invalid_Argument_Msg(l_api_name_full,
1813 to_char(lx_cost_rec.quantity),
1814 'Quantity ');
1815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1816
1817 end if;
1818
1819 --IF Quantity is not passed then Default it to 1
1820 IF lx_cost_rec.quantity IS NULL THEN
1821 lx_cost_rec.quantity:=1;
1822 END IF;
1823
1824 -- For a Return Transaction the Quantity should be Negative
1825 IF (l_line_order_category_code = 'RETURN') then
1826 if lx_cost_rec.quantity is not null
1827 then
1828 if sign(lx_cost_rec.quantity) = -1 then
1829 lx_cost_rec.quantity := lx_cost_rec.quantity;
1830 else
1831 --assign -ve qty to out record
1832 lx_cost_rec.quantity := (lx_cost_rec.quantity * -1);
1833 end if;
1834 end if;
1835 Else
1836 -- For a Order Transaction
1837 if lx_cost_rec.quantity is not null then
1838 if sign(lx_cost_rec.quantity ) = -1 then
1839 -- need to make this positive as no -ve quantity for orders
1840 lx_cost_rec.quantity := (lx_cost_rec.quantity * -1);
1841 else
1842 lx_cost_rec.quantity := lx_cost_rec.quantity ;
1843 end if;
1844 end if;
1845 End if;
1846
1847 --Bug 7193528
1848
1849 /*1. If Cost Creation is attempted for an SAC with Create_Charge Flag checked
1850 then Use the Value set in the profile "Service :Inventory Validation
1851 Org" to retrieve the Item's Unit Cost
1852 2. If Cost Creation is attempted for an SAC with Create_Charge Flag UnChecked
1853 then Use the Service Request Inventory Org to fetch the Item's Unit Cost.
1854 The Inventory_org_id in CS_COST_DETAILS should be the org from which the Item
1855 Cost is fetched.
1856 The same logic holds good for getting the ORG_ID(operating Unit) in
1857 CS_COST_DETAILS
1858 1.IF Cost Creation is attempted for an exising Charge Line then store the
1859 Charge Line's Operating unit in CS_COST_DETAILS.org_id column
1860 2.If Cost Creation is attempted for the SR, then store the SR's Operating
1861 Unit in the Org Id column of the Cost table
1862 */
1863
1864 if l_create_charge_flag ='N' then
1865 --l_cost_org_id := p_cost_rec.inventory_org_id;
1866
1867 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
1868 x_org_id => l_cost_org_id,
1869 x_inv_organization_id => l_cost_inv_org_id ,
1870 x_msg_data => x_msg_data,
1871 x_msg_count => x_msg_count,
1872 x_return_status => l_return_status
1873 );
1874
1875 else
1876
1877 l_cost_inv_org_id :=lx_cost_rec.inventory_org_id;
1878 l_cost_org_id :=lx_cost_rec.org_id;
1879
1880 if l_cost_Rec.estimate_Detail_id is null then
1881
1882 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
1883 x_org_id => l_cost_org_id,
1884 x_inv_organization_id => l_cost_inv_org_id ,
1885 x_msg_data => x_msg_data,
1886 x_msg_count => x_msg_count,
1887 x_return_status => l_return_status
1888 );
1889 end if;
1890
1891 end if;
1892
1893 --===============================
1894 ---Item Cost
1895 --===============================
1896
1897 l_unit_cost := CST_COST_API.Get_Item_Cost
1898 (
1899 p_api_version => 1.0,
1900 p_inventory_item_id => lx_cost_rec.inventory_item_id,
1901 p_organization_id => l_cost_org_id,
1902 p_cost_group_id => p_cost_group_id,
1903 p_cost_type_id => p_cost_type_id
1904 );
1905
1906 --Calculate the Item's Extended Cost
1907 -- If extended cost is passed then make the unit cost and quantity column to NULL and set the flag to 'Y'
1908 -- IF extended cost is not passed then calculate it as below and set the flag to 'N'
1909
1910 If lx_cost_rec.extended_cost IS NULL THEN
1911 lx_cost_rec.extended_cost :=l_unit_cost*lx_cost_rec.quantity;
1912 l_override_ext_cost_flag :='N';
1913 Else
1914 --l_unit_cost := null;
1915 --lx_cost_rec.quantity := null;
1916 l_override_ext_cost_flag :='Y';
1917 IF (l_line_order_category_code = 'RETURN') then
1918 if sign(lx_cost_rec.extended_cost) = -1 then
1919 lx_cost_rec.extended_cost := lx_cost_rec.extended_cost;
1920 else
1921 --assign -ve qty to out record
1922 lx_cost_rec.extended_cost := (lx_cost_rec.extended_cost * -1);
1923 end if;
1924 END IF;
1925
1926 END IF;
1927
1928 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1929 THEN
1930 FND_LOG.String
1931 ( FND_LOG.level_procedure ,
1932 L_LOG_MODULE || '',
1933 'Before calling the Insert_Row procedure'
1934 );
1935 END IF;
1936
1937 --This prcoedure will insert data into cs_cost_details table
1938
1939 CS_COST_DETAILS_PKG.Insert_Row
1940 (
1941 x_cost_id =>l_cost_id ,
1942 p_incident_id =>lx_cost_rec.incident_id ,
1943 p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
1944 p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
1945 p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
1946 p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
1947 p_quantity =>lx_cost_rec.quantity ,
1948 p_unit_cost =>l_unit_cost ,
1949 p_extended_cost =>lx_cost_rec.extended_cost ,
1950 p_override_ext_cost_flag =>l_override_ext_cost_flag ,
1951 p_transaction_date => sysdate ,
1952 p_source_id =>lx_cost_rec.source_id ,
1953 p_source_code =>lx_cost_rec.source_code ,
1954 p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
1955 p_currency_code =>lx_cost_rec.currency_code ,
1956 p_org_id =>l_cost_org_id ,
1957 p_inventory_org_id =>l_cost_inv_org_id ,
1958 p_attribute1 =>lx_cost_rec.attribute1 ,
1959 p_attribute2 =>lx_cost_rec.attribute2 ,
1960 p_attribute3 =>lx_cost_rec.attribute3 ,
1961 p_attribute4 =>lx_cost_rec.attribute4 ,
1962 p_attribute5 =>lx_cost_rec.attribute5 ,
1963 p_attribute6 =>lx_cost_rec.attribute6 ,
1964 p_attribute7 =>lx_cost_rec.attribute7 ,
1965 p_attribute8 =>lx_cost_rec.attribute8 ,
1966 p_attribute9 =>lx_cost_rec.attribute9 ,
1967 p_attribute10 =>lx_cost_rec.attribute10 ,
1968 p_attribute11 =>lx_cost_rec.attribute11 ,
1969 p_attribute12 =>lx_cost_rec.attribute12 ,
1970 p_attribute13 =>lx_cost_rec.attribute13 ,
1971 p_attribute14 =>lx_cost_rec.attribute14 ,
1972 p_attribute15 =>lx_cost_rec.attribute15 ,
1973 p_last_update_date => sysdate ,
1974 p_last_updated_by => FND_GLOBAL.USER_ID ,
1975 p_last_update_login => FND_GLOBAL.LOGIN_ID ,
1976 p_created_by => FND_GLOBAL.USER_ID ,
1977 p_creation_date => sysdate ,
1978 x_object_version_number => l_object_version_number
1979 );
1980
1981 x_cost_id :=l_cost_id;
1982
1983 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1984 THEN
1985 FND_LOG.String
1986 ( FND_LOG.level_procedure ,
1987 L_LOG_MODULE || '',
1988 'After calling the Insert Row '
1989 );
1990 END IF;
1991
1992 FND_MSG_PUB.Count_And_Get
1993 ( p_count => x_msg_count,
1994 p_data => x_msg_data,
1995 p_encoded => FND_API.G_FALSE) ;
1996
1997 EXCEPTION
1998
1999 WHEN FND_API.G_EXC_ERROR THEN
2000 x_return_status := FND_API.G_RET_STS_ERROR;
2001 IF FND_API.To_Boolean(p_transaction_control)
2002 THEN
2003 ROLLBACK TO Create_Cost_Details_PVT;
2004 END IF ;
2005 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2006 p_data => x_msg_data,
2007 p_encoded => FND_API.G_FALSE) ;
2008
2009 WHEN G_WARNING THEN
2010 x_return_status := FND_API.G_RET_STS_SUCCESS;
2011 IF FND_API.To_Boolean(p_transaction_control)
2012 THEN
2013 ROLLBACK TO Create_Cost_Details_PVT;
2014 END IF ;
2015 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2016 p_data => x_msg_data,
2017 p_encoded => FND_API.G_FALSE) ;
2018
2019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2020 IF FND_API.To_Boolean(p_transaction_control) THEN
2021 ROLLBACK TO Create_Cost_Details_PVT;
2022 END IF ;
2023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2024 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2025 p_data => x_msg_data,
2026 p_encoded => FND_API.G_FALSE) ;
2027
2028 WHEN OTHERS THEN
2029 IF FND_API.To_Boolean(p_transaction_control) THEN
2030 ROLLBACK TO Create_Cost_Details_PVT;
2031 END IF ;
2032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2034 THEN
2035 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2036 END IF;
2037 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2038 p_data => x_msg_data,
2039 p_encoded => FND_API.G_FALSE) ;
2040
2041 END create_cost_details;
2042
2043 /*======================================================================+
2044 ==
2045 == Procedure name : Create_cost_details
2046 == Comments : API to Update cost details in cs_cost_details
2047 == Modification History:
2048 ==
2049 == Date Name Desc
2050 == ---------- --------- ---------------------------------------------
2051 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2052 ========================================================================*/
2053
2054 PROCEDURE Update_Cost_Details
2055 (
2056 p_api_version IN NUMBER,
2057 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2058 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2059 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2060 x_return_status OUT NOCOPY VARCHAR2,
2061 x_msg_count OUT NOCOPY NUMBER,
2062 x_object_version_number OUT NOCOPY NUMBER,
2063 x_msg_data OUT NOCOPY VARCHAR2,
2064 p_resp_appl_id IN NUMBER := FND_GLOBAL.RESP_APPL_ID,
2065 p_resp_id IN NUMBER := FND_GLOBAL.RESP_ID,
2066 p_user_id IN NUMBER := FND_GLOBAL.USER_ID,
2067 p_login_id IN NUMBER :=FND_GLOBAL.LOGIN_ID,
2068 p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
2069 p_Cost_Rec IN CS_Cost_Details_PUB.Cost_Rec_Type
2070
2071 ) IS
2072
2073 l_api_version NUMBER := 1.0 ;
2074 l_api_name VARCHAR2(100) := 'Update_Cost_Details' ;
2075 l_api_name_full VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name ;
2076 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.pls.' || l_api_name_full || '.';
2077 l_return_status VARCHAR2(1) ;
2078 l_org_id NUMBER ;
2079 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_cost_details';
2080 l_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
2081 lx_cost_rec CS_Cost_Details_PUB.Cost_Rec_Type;
2082 l_valid_check VARCHAR2(1);
2083
2084 l_msg_data VARCHAR2(2000);
2085 l_msg_count NUMBER;
2086 l_object_version_number NUMBER;
2087 l_errm VARCHAR2(100);
2088 l_cost_id NUMBER;
2089 l_transaction_type_id NUMBER;
2090 l_charge_line_type VARCHAR2(30);
2091
2092 l_unit_cost NUMBER;
2093 p_cost_group_id NUMBER;
2094 p_cost_type_id NUMBER;
2095
2096 l_override_ext_cost_flag VARCHAR2(1);
2097 l_quantity NUMBER;
2098 lt_estimate_detail_id NUMBER;
2099 v_estimate_detail_id NUMBER;
2100
2101 p_cost_id NUMBER;
2102 v_unit_cost NUMBER;
2103 v_extended_cost NUMBER;
2104 v_override_ext_cost_flag VARCHAR2(1);
2105
2106 p_estimate_detail_id NUMBER;
2107 lv_cost_id NUMBER;
2108 l_disallow_new_charge VARCHAR2(1);
2109 l_disallow_charge_update VARCHAR2(1);
2110 l_disallow_request_update VARCHAR2(1);
2111 l_create_charge_flag VARCHAR2(1);
2112 l_create_cost_flag VARCHAR2(1);
2113 l_cost_org_id NUMBER;
2114 l_cost_inv_org_id NUMBER;
2115
2116 CURSOR get_flag IS
2117 SELECT unit_cost,
2118 extended_cost,
2119 override_ext_cost_flag
2120 FROM cs_cost_details
2121 WHERE cost_id = p_cost_id;
2122
2123 CURSOR c_check_cost_exst IS
2124 SELECT cost_id
2125 FROM cs_cost_details
2126 WHERE estimate_Detail_id = p_estimate_detail_id;
2127
2128 BEGIN
2129 -- Standard start of API savepoint
2130 IF FND_API.To_Boolean(p_transaction_control) THEN
2131 SAVEPOINT Update_Cost_Details_PVT;
2132 END IF ;
2133 -- Standard call to check for call compatibility
2134 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2135 THEN
2136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2137 END IF;
2138 -- Initialize message list if p_init_msg_list is set to TRUE
2139 IF FND_API.To_Boolean(p_init_msg_list) THEN
2140 FND_MSG_PUB.Initialize;
2141 END IF;
2142 -- Initialize API return status to success
2143 x_return_status := FND_API.G_RET_STS_SUCCESS;
2144
2145 ----------------------- FND Logging -----------------------------------
2146 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2147 THEN
2148 FND_LOG.String
2149 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
2150 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2151 );
2152 FND_LOG.String
2153 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2154 , 'p_api_version:' || p_api_version
2155 );
2156 FND_LOG.String
2157 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2158 , 'p_init_msg_list:' || p_init_msg_list
2159 );
2160 FND_LOG.String
2161 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2162 , 'p_commit:' || p_commit
2163 );
2164 FND_LOG.String
2165 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2166 , 'p_validation_level:' || p_validation_level
2167 );
2168 FND_LOG.String
2169 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2170 , 'p_resp_appl_id:' || p_resp_appl_id
2171 );
2172 FND_LOG.String
2173 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2174 , 'p_resp_id:' || p_resp_id
2175 );
2176 FND_LOG.String
2177 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2178 , 'p_user_id:' || p_user_id
2179 );
2180 FND_LOG.String
2181 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2182 , 'p_login_id:' || p_login_id
2183 );
2184 FND_LOG.String
2185 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2186 , 'p_transaction_control:' || p_transaction_control
2187 );
2188 -- --------------------------------------------------------------------------
2189 -- This procedure Logs the charges record paramters.
2190 -- --------------------------------------------------------------------------
2191 CS_COST_DETAILS_PUB.Log_Cost_Rec_Parameters
2192 ( p_cost_Rec_in => p_cost_rec
2193 );
2194
2195 END IF;
2196
2197 l_cost_rec:=p_cost_rec;
2198
2199 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2200 THEN
2201 FND_LOG.String
2202 ( FND_LOG.level_procedure ,
2203 L_LOG_MODULE || '',
2204 'p_cost_rec.estimate_Detail_id : '||p_cost_rec.estimate_Detail_id
2205 );
2206 END IF;
2207
2208 lt_estimate_detail_id :=p_cost_rec.estimate_Detail_id ;
2209
2210
2211 /*
2212 If there is a estimate_Detail_id for the cost_id passed , then get all the details from the estimate_Detail_id
2213 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.
2214
2215 For example say a Charge line has been Created for 10 Quantities of an Item.
2216 Cost Line is also generated for this 10 quantity.
2217 If while calling the Update Cost API , say the Quantity is passed as 20 for this cost line,
2218 the cost API will not update the cost line with 20 quantities .
2219 But if the Update Cost API is called with Extended Cost , then the Cost API will update the Quantity and Unit Cost
2220 to NULL and extended cost to the value passed.
2221 */
2222
2223
2224 IF p_cost_rec.cost_id is not null and p_cost_rec.cost_id <> fnd_api.g_miss_num
2225 THEN
2226
2227 begin
2228
2229 select estimate_detail_id
2230 into v_estimate_detail_id
2231 from cs_cost_details csd
2232 where cost_id =p_cost_rec.cost_id;
2233
2234 if v_estimate_detail_id is not null then
2235 lt_estimate_detail_id:=v_estimate_detail_id;
2236 end if;
2237
2238 exception
2239 when no_data_found then
2240 null;--dbms_output.put_line('Costing1');
2241 when others then
2242 null;
2243 end;
2244
2245 END IF;
2246
2247
2248 IF lt_estimate_detail_id IS NOT NULL
2249 AND lt_estimate_detail_id<> fnd_api.g_miss_num
2250 THEN
2251
2252 p_estimate_Detail_id := lt_estimate_detail_id;
2253
2254 OPEN c_check_cost_exst;
2255
2256 FETCH c_check_cost_exst into lv_cost_id;
2257
2258 CLOSE c_check_cost_exst;
2259
2260 if lv_cost_id is null then
2261 FND_MESSAGE.SET_NAME('CS', 'CS_COST_NO_CHARGE_EXIST');
2262 FND_MSG_PUB.ADD;
2263 RAISE G_WARNING;
2264 end if;
2265
2266 GET_CHARGE_DETAILS
2267 (
2268 p_api_name => l_api_name_full,
2269 p_estimate_detail_id => lt_estimate_detail_id,
2270 x_incident_id => l_cost_rec.incident_id,
2271 x_transaction_type_id => l_cost_rec.transaction_type_id ,
2272 x_txn_billing_type_id => l_cost_rec.txn_billing_type_id,
2273 x_charge_line_type => l_cost_rec.charge_line_type ,
2274 x_inventory_item_id => l_cost_rec.inventory_item_id ,
2275 x_quantity => l_cost_rec.quantity ,
2276 x_unit_of_measure_code => l_cost_rec.unit_of_measure_code ,
2277 x_currency_code => l_cost_rec.currency_code ,
2278 x_source_id => l_cost_rec.source_id ,
2279 x_source_code => l_cost_rec.source_code ,
2280 x_org_id => l_cost_rec.org_id ,
2281 x_txn_inv_org => l_cost_rec.inventory_org_id,
2282 x_msg_data => x_msg_data,
2283 x_msg_count => x_msg_count,
2284 x_return_status => x_return_status
2285 );
2286
2287 --Bug fix for 6972425
2288 --start
2289 get_charge_flags_from_sr
2290 (
2291 p_api_name => l_api_name_full,
2292 p_incident_id => l_cost_rec.incident_id,
2293 p_transaction_type_id => l_cost_rec.transaction_type_id,
2294 x_create_charge_flag => l_create_charge_flag,
2295 x_create_cost_flag => l_create_cost_flag,
2296 x_disallow_request_update => l_disallow_request_update,
2297 x_disallow_new_charge => l_disallow_new_charge,
2298 x_disallow_charge_update => l_disallow_charge_update,
2299 x_msg_data => l_msg_data,
2300 x_msg_count => l_msg_count,
2301 x_return_status => l_return_status
2302 );
2303
2304 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2305 THEN
2306 FND_LOG.String
2307 ( FND_LOG.level_procedure ,
2308 L_LOG_MODULE || '',
2309 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
2310 );
2311
2312 FND_LOG.String
2313 ( FND_LOG.level_procedure ,
2314 L_LOG_MODULE || '',
2315 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
2316 'l_disallow_charge_update: '||l_disallow_charge_update
2317 );
2318 END IF;
2319
2320 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
2321
2322 if l_disallow_request_update='Y' THEN
2323 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
2324 FND_MSG_PUB.ADD;
2325 RAISE FND_API.G_EXC_ERROR;
2326 end if;
2327
2328 Else
2329
2330 if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
2331 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
2332 FND_MSG_PUB.ADD;
2333 RAISE FND_API.G_EXC_ERROR;
2334 end if;
2335
2336 end if;
2337
2338 --end Bug fix for 6972425
2339
2340 END IF;
2341
2342 lx_cost_rec:=l_cost_rec;
2343
2344 --Cost Line can only be created for 'ACTUAL' charge Line types
2345
2346 if lx_cost_rec.charge_line_type is not null and lx_cost_rec.charge_line_type <> fnd_api.g_miss_char
2347 then
2348 if lx_cost_rec.charge_line_type<>'ACTUAL' then
2349
2350 Add_Invalid_Argument_Msg(l_api_name_full,
2351 to_char(lx_cost_rec.charge_line_type),
2352 'Charge_line_Type');
2353 RAISE G_WARNING;
2354 end if;
2355 end if;
2356
2357
2358
2359 IF (p_validation_level = FND_API.G_VALID_LEVEL_NONE) THEN
2360
2361
2362 Validate_Who_Info ( p_api_name => l_api_name_full,
2363 p_user_id => NVL(p_user_id, -1),
2364 p_login_id => p_login_id,
2365 x_return_status => l_return_status);
2366
2367 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2368 RAISE FND_API.G_EXC_ERROR;
2369 END IF;
2370
2371 IF lt_estimate_detail_id is null then
2372
2373 lx_cost_rec:= l_cost_rec;
2374
2375 END IF;
2376
2377 L_RECALC_COST :='Y';
2378
2379
2380 ELSIF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
2381
2382
2383 -- Perform all the validations by calling the procedure VALIDATE_COST_DETAILS which inturn does all the validations
2384 --This is done only when there is no estimate_Detail_id
2385 if lt_estimate_detail_id is null or lt_estimate_detail_id = fnd_api.g_miss_num
2386 then
2387
2388 VALIDATE_COST_DETAILS
2389 (
2390 p_api_name => l_api_name,
2391 pv_cost_rec => l_cost_rec,
2392 p_validation_mode => 'U' ,
2393 p_user_id => p_user_id,
2394 p_login_id => p_login_id,
2395 x_cost_rec => lx_cost_rec,
2396 x_msg_data => x_msg_data,
2397 x_msg_count => x_msg_count,
2398 x_return_status => l_return_status
2399 );
2400
2401
2402 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2403 FND_MESSAGE.Set_Name('CS', 'CS_COST_VALIDATE_COST_DTL_ER');
2404 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
2405 FND_MSG_PUB.Add;
2406 RAISE FND_API.G_EXC_ERROR;
2407 END IF;
2408
2409 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2410 THEN
2411 FND_LOG.String
2412 ( FND_LOG.level_procedure ,
2413 L_LOG_MODULE || '',
2414 'After Validating the Cost Details '
2415 );
2416
2417 END IF;
2418
2419 lv_cost_id := lx_cost_rec.cost_id;
2420
2421 end if;
2422 END IF;
2423
2424 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2425 THEN
2426 FND_LOG.String
2427 ( FND_LOG.level_procedure ,
2428 L_LOG_MODULE || '',
2429 'Before Cost Calculations'
2430 );
2431 END IF;
2432
2433
2434 /*
2435 if sign(lx_cost_rec.extended_cost)= -1
2436 then
2437
2438 Add_Invalid_Argument_Msg(l_api_name_full,
2439 to_char(lx_cost_rec.extended_cost),
2440 'Extended Cost ');
2441 RAISE FND_API.G_EXC_ERROR;
2442
2443 end if;
2444 */
2445
2446 if sign(lx_cost_rec.quantity) = 0
2447 then
2448
2449
2450 Add_Invalid_Argument_Msg(l_api_name_full,
2451 to_char(lx_cost_rec.quantity),
2452 'Quantity ');
2453 RAISE FND_API.G_EXC_ERROR;
2454
2455 end if;
2456
2457
2458 p_cost_id := lv_cost_id;
2459
2460 open get_flag;
2461 fetch get_flag into v_unit_cost,
2462 v_extended_cost,
2463 v_override_ext_cost_flag ;
2464 close get_flag;
2465
2466
2467 l_override_ext_cost_flag:=v_override_ext_cost_flag;
2468 l_unit_cost :=v_unit_cost;
2469 if lx_cost_rec.extended_cost is not null
2470 and lx_cost_rec.extended_cost <> fnd_api.g_miss_num then
2471
2472 --l_unit_cost := NULL;
2473 --lx_cost_rec.quantity := NULL;
2474 l_unit_cost := v_unit_cost;
2475 l_override_ext_cost_flag :='Y';
2476 L_RECALC_COST := 'N'; --If extended cost is passed then need not recalculate the unit cost/extended cost
2477
2478 end if;
2479
2480
2481
2482 if L_RECALC_COST = 'Y' then
2483 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2484 THEN
2485 FND_LOG.String
2486 ( FND_LOG.level_procedure ,
2487 L_LOG_MODULE || '',
2488 'Recalcualting the Cost'
2489 );
2490 END IF;
2491 --Bug 7193528
2492 if l_create_charge_flag ='N' then
2493 --l_cost_org_id := p_cost_rec.inventory_org_id;
2494
2495 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
2496 x_org_id => l_cost_org_id,
2497 x_inv_organization_id => l_cost_inv_org_id ,
2498 x_msg_data => x_msg_data,
2499 x_msg_count => x_msg_count,
2500 x_return_status => l_return_status
2501 );
2502
2503 else
2504
2505 l_cost_inv_org_id :=lx_cost_rec.inventory_org_id;
2506 l_cost_org_id :=lx_cost_rec.org_id;
2507
2508 if l_cost_Rec.estimate_Detail_id is null then
2509
2510 get_sr_org_info ( p_incident_id => lx_cost_rec.incident_id,
2511 x_org_id => l_cost_org_id,
2512 x_inv_organization_id => l_cost_inv_org_id ,
2513 x_msg_data => x_msg_data,
2514 x_msg_count => x_msg_count,
2515 x_return_status => l_return_status
2516 );
2517 end if;
2518
2519 end if;
2520
2521 --Calculate the Item's Unit Cost
2522 l_unit_cost := CST_COST_API.Get_Item_Cost
2523 (
2524 p_api_version => 1.0,
2525 p_inventory_item_id => lx_cost_rec.inventory_item_id,
2526 p_organization_id => lx_cost_rec.org_id,
2527 p_cost_group_id => p_cost_group_id,
2528 p_cost_type_id => p_cost_type_id
2529 );
2530
2531 --Calculate the Item's Extended Cost
2532 If lx_cost_rec.extended_cost IS NULL or lx_cost_rec.extended_cost = fnd_api.g_miss_num
2533 THEN
2534 lx_cost_rec.extended_cost :=l_unit_cost*lx_cost_rec.quantity;
2535 l_override_ext_cost_flag :='N';
2536 Else
2537
2538 --l_unit_cost := NULL;
2539 --l_quantity := NULL;
2540 l_override_ext_cost_flag :='Y';
2541
2542 END IF;
2543
2544 end if ;
2545
2546 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2547 THEN
2548 FND_LOG.String
2549 ( FND_LOG.level_procedure ,
2550 L_LOG_MODULE || '',
2551 'Before calling the Update_Row Procedure'
2552 );
2553 END IF;
2554
2555
2556 CS_COST_DETAILS_PKG.Update_Row
2557 (
2558 p_cost_id =>lx_cost_rec.cost_id ,
2559 p_incident_id =>lx_cost_rec.incident_id ,
2560 p_estimate_detail_id =>lx_cost_rec.estimate_detail_id ,
2561 p_transaction_type_id =>lx_cost_rec.transaction_type_id ,
2562 p_txn_billing_type_id =>lx_cost_rec.txn_billing_type_id ,
2563 --p_charge_line_type =>lx_cost_rec.charge_line_type ,
2564 p_inventory_item_id =>lx_cost_rec.inventory_item_id ,
2565 p_quantity =>lx_cost_rec.quantity ,
2566 p_unit_cost =>l_unit_cost ,
2567 p_extended_cost =>lx_cost_rec.extended_cost ,
2568 p_override_ext_cost_flag =>l_override_ext_cost_flag ,
2569 p_transaction_date =>sysdate ,
2570 p_source_id =>lx_cost_rec.source_id ,
2571 p_source_code =>lx_cost_rec.source_code ,
2572 p_unit_of_measure_code =>lx_cost_rec.unit_of_measure_code ,
2573 p_currency_code =>lx_cost_rec.currency_code ,
2574 p_org_id =>l_cost_org_id ,
2575 p_inventory_org_id =>l_cost_inv_org_id ,
2576 p_attribute1 =>lx_cost_rec.attribute1 ,
2577 p_attribute2 =>lx_cost_rec.attribute2 ,
2578 p_attribute3 =>lx_cost_rec.attribute3 ,
2579 p_attribute4 =>lx_cost_rec.attribute4 ,
2580 p_attribute5 =>lx_cost_rec.attribute5 ,
2581 p_attribute6 =>lx_cost_rec.attribute6 ,
2582 p_attribute7 =>lx_cost_rec.attribute7 ,
2583 p_attribute8 =>lx_cost_rec.attribute8 ,
2584 p_attribute9 =>lx_cost_rec.attribute9 ,
2585 p_attribute10 =>lx_cost_rec.attribute10 ,
2586 p_attribute11 =>lx_cost_rec.attribute11 ,
2587 p_attribute12 =>lx_cost_rec.attribute12 ,
2588 p_attribute13 =>lx_cost_rec.attribute13 ,
2589 p_attribute14 =>lx_cost_rec.attribute14 ,
2590 p_attribute15 =>lx_cost_rec.attribute15 ,
2591 p_last_update_date => sysdate ,
2592 p_last_updated_by => FND_GLOBAL.USER_ID ,
2593 p_last_update_login => FND_GLOBAL.LOGIN_ID ,
2594 p_created_by => FND_GLOBAL.USER_ID ,
2595 p_creation_date => sysdate ,
2596 x_object_version_number => l_object_version_number
2597 );
2598
2599
2600
2601 -- Standard check of p_commit
2602 IF FND_API.To_Boolean(p_commit) THEN
2603 COMMIT ;
2604
2605 END IF;
2606
2607 -- Standard call to get message count and if count is 1, get message info
2608 FND_MSG_PUB.Count_And_Get
2609 ( p_count => x_msg_count,
2610 p_data => x_msg_data
2611 );
2612
2613
2614 EXCEPTION
2615
2616 WHEN FND_API.G_EXC_ERROR THEN
2617 IF FND_API.To_Boolean(p_transaction_control) THEN
2618 ROLLBACK TO Update_Cost_Details_PVT;
2619 END IF ;
2620 x_return_status := FND_API.G_RET_STS_ERROR;
2621 FND_MSG_PUB.Count_And_Get(
2622 p_count => x_msg_count,
2623 p_data => x_msg_data,
2624 p_encoded => FND_API.G_FALSE) ;
2625
2626 WHEN G_WARNING THEN
2627 x_return_status := FND_API.G_RET_STS_SUCCESS;
2628
2629 IF FND_API.To_Boolean(p_transaction_control) THEN
2630 ROLLBACK TO Update_Cost_Details_PVT;
2631 END IF ;
2632 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2633 p_data => x_msg_data,
2634 p_encoded => FND_API.G_FALSE) ;
2635
2636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2637 IF FND_API.To_Boolean(p_transaction_control) THEN
2638 ROLLBACK TO Update_Cost_Details_PVT;
2639 END IF ;
2640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2642 p_data => x_msg_data,
2643 p_encoded => FND_API.G_FALSE) ;
2644 WHEN OTHERS THEN
2645 IF FND_API.To_Boolean(p_transaction_control) THEN
2646 ROLLBACK TO Update_Cost_Details_PVT;
2647 END IF ;
2648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2649 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2650 THEN
2651 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2652 END IF;
2653 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2654 p_data => x_msg_data,
2655 p_encoded => FND_API.G_FALSE) ;
2656
2657
2658 END Update_Cost_Details;
2659
2660 /*======================================================================+
2661 ==
2662 == Procedure name : delete_cost_details
2663 == Comments : API to Update cost details in cs_cost_details
2664 == Modification History:
2665 ==
2666 == Date Name Desc
2667 == ---------- --------- ---------------------------------------------
2668 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2669 ========================================================================*/
2670
2671
2672 PROCEDURE Delete_Cost_Details
2673 (
2674 p_api_version IN NUMBER,
2675 p_init_msg_list IN VARCHAR2,
2676 p_commit IN VARCHAR2 ,
2677 p_validation_level IN NUMBER ,
2678 x_return_status OUT NOCOPY VARCHAR2,
2679 x_msg_count OUT NOCOPY NUMBER,
2680 x_msg_data OUT NOCOPY VARCHAR2,
2681 p_transaction_control IN VARCHAR2 ,
2682 p_cost_id IN NUMBER := NULL
2683 )IS
2684
2685
2686 l_api_name CONSTANT VARCHAR2(100) := 'Delete_Cost_Details' ;
2687 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
2688 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.plsl.' || l_api_name_full || '.';
2689 l_api_version CONSTANT NUMBER := 1.0 ;
2690
2691 l_resp_appl_id NUMBER ;
2692 l_resp_id NUMBER ;
2693 l_user_id NUMBER ;
2694 l_login_id NUMBER ;
2695 l_org_id NUMBER := NULL ;
2696 l_charge_line_type VARCHAR2(30);
2697 l_return_status VARCHAR2(1) ;
2698 l_valid_check VARCHAR2(1);
2699
2700 BEGIN
2701
2702 --Standard Start of API Savepoint
2703 IF FND_API.To_Boolean( p_transaction_control ) THEN
2704 SAVEPOINT Delete_Cost_Details_PVT ;
2705 END IF ;
2706 --Standard Call to check API compatibility
2707 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2708 THEN
2709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2710 END IF ;
2711 --Initialize the message list if p_msg_list is set to TRUE
2712 IF FND_API.To_Boolean(p_init_msg_list) THEN
2713 FND_MSG_PUB.initialize ;
2714 END IF ;
2715
2716 --Initialize the API Return Success to True
2717 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2718
2719 ----------------------- FND Logging -----------------------------------
2720 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2721 THEN
2722 FND_LOG.String
2723 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
2724 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2725 );
2726 FND_LOG.String
2727 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2728 , 'p_api_version:' || p_api_version
2729 );
2730 FND_LOG.String
2731 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2732 , 'p_init_msg_list:' || p_init_msg_list
2733 );
2734 FND_LOG.String
2735 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2736 , 'p_commit:' || p_commit
2737 );
2738 FND_LOG.String
2739 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2740 , 'p_validation_level:' || p_validation_level
2741 );
2742 FND_LOG.String
2743 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2744 , 'p_transaction_control:' || p_transaction_control
2745 );
2746 FND_LOG.String
2747 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
2748 , 'p_cost_id' || p_cost_id
2749 );
2750
2751 END IF;
2752
2753 IF (p_cost_id IS NULL) THEN
2754 Add_Null_Parameter_Msg(l_api_name_full,
2755 'p_cost_id') ;
2756 RAISE FND_API.G_EXC_ERROR ;
2757 END IF ;
2758
2759 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2760 THEN
2761 FND_LOG.String
2762 ( FND_LOG.level_procedure ,
2763 L_LOG_MODULE || '',
2764 'Before Do_Cost_Line_Exist'
2765 );
2766 END IF;
2767
2768 l_valid_check:= Do_cost_line_Exist
2769 (
2770 l_api_name_full,
2771 p_cost_id,
2772 l_return_status
2773 ) ;
2774 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2775 Add_Invalid_Argument_Msg(l_api_name,
2776 TO_CHAR(p_cost_id),
2777 'cost_id');
2778 RAISE G_WARNING;
2779 ELSIF l_return_status = G_RET_STS_ERROR THEN
2780 Add_Invalid_Argument_Msg(l_api_name,
2781 TO_CHAR(p_cost_id),
2782 'cost_id');
2783 RAISE G_WARNING;
2784 END IF;
2785
2786 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2787 RAISE G_WARNING ;
2788 END IF ;
2789
2790 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2791 THEN
2792 FND_LOG.String
2793 ( FND_LOG.level_procedure ,
2794 L_LOG_MODULE || '',
2795 'Before Do_charge_line_Exist'
2796 );
2797 END IF;
2798
2799 --if there is a charge line existing for this cost then do not delete this cost line
2800
2801 l_valid_check:= Do_charge_line_Exist
2802 (
2803 l_api_name_full,
2804 p_cost_id,
2805 l_return_status
2806 ) ;
2807
2808 if l_valid_check ='Y' then
2809 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_DELETE');
2810 FND_MSG_PUB.ADD;
2811 RAISE G_WARNING;
2812 end if;
2813
2814
2815 delete from cs_cost_details where
2816 cost_id = p_cost_id;
2817
2818 --End of API Body
2819 --Standard Check of p_commit
2820 IF FND_API.To_Boolean( p_commit ) THEN
2821 COMMIT WORK ;
2822 END IF ;
2823
2824 --Standard call to get message count and if count is 1 , get message info
2825 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
2826 p_data => x_msg_data) ;
2827
2828 --Begin Exception Handling
2829
2830 EXCEPTION
2831
2832 WHEN FND_API.G_EXC_ERROR THEN
2833 IF FND_API.To_Boolean( p_transaction_control ) THEN
2834 ROLLBACK TO Delete_Cost_Details_PVT;
2835 END IF ;
2836 x_return_status := FND_API.G_RET_STS_ERROR ;
2837 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2838 p_data => x_msg_data,
2839 p_encoded => FND_API.G_FALSE) ;
2840
2841 WHEN G_WARNING THEN
2842 x_return_status := FND_API.G_RET_STS_SUCCESS;
2843 IF FND_API.To_Boolean(p_transaction_control) THEN
2844 ROLLBACK TO Delete_Cost_Details_PVT;
2845 END IF ;
2846 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2847 p_data => x_msg_data,
2848 p_encoded => FND_API.G_FALSE) ;
2849
2850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2851 IF FND_API.To_Boolean( p_transaction_control ) THEN
2852 ROLLBACK TO Delete_Cost_Details_PVT;
2853 END IF ;
2854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2855 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2856 p_data => x_msg_data,
2857 p_encoded => FND_API.G_FALSE) ;
2858
2859 WHEN OTHERS THEN
2860 IF FND_API.To_Boolean( p_transaction_control ) THEN
2861 ROLLBACK TO Delete_Cost_Details_PVT;
2862 END IF ;
2863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2864 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2865 THEN
2866 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2867 END IF ;
2868 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2869 p_data => x_msg_data,
2870 p_encoded => FND_API.G_FALSE) ;
2871
2872 End Delete_Cost_Details;
2873
2874 /*======================================================================+
2875 ==
2876 == Procedure name : Validate_cost_details
2877 == Comments : API to Update cost details in cs_cost_details
2878 == Modification History:
2879 ==
2880 == Date Name Desc
2881 == ---------- --------- ---------------------------------------------
2882 == 15-DEC-2007 | BKANIMOZ | Created the procedure
2883 ========================================================================*/
2884
2885 PROCEDURE VALIDATE_COST_DETAILS
2886 (
2887 p_api_name IN VARCHAR2,
2888 pv_cost_rec IN CS_COST_DETAILS_PUB.COST_REC_TYPE,
2889 p_validation_mode IN VARCHAR2,
2890 p_user_id IN NUMBER,
2891 p_login_id IN NUMBER,
2892 x_cost_rec OUT NOCOPY CS_COST_DETAILS_PUB.COST_REC_TYPE,
2893 x_msg_data OUT NOCOPY VARCHAR2,
2894 x_msg_count OUT NOCOPY NUMBER,
2895 x_return_status OUT NOCOPY VARCHAR2
2896 ) IS
2897
2898 l_valid_check VARCHAR2(1);
2899 l_return_status VARCHAR2(1) ;
2900 l_msg_data VARCHAR2(2000);
2901 l_msg_count NUMBER;
2902 l_api_version NUMBER := 1.0 ;
2903 l_api_name CONSTANT VARCHAR2(100) := 'Validate_Cost_Details Private API' ;
2904 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
2905 l_log_module CONSTANT VARCHAR2(255) := 'csxvcstb.pls' || l_api_name_full || '.';
2906 l_db_det_rec CS_COST_DETAILS%ROWTYPE;
2907 l_source_id NUMBER;
2908 l_org_id NUMBER;
2909 l_profile VARCHAR2(200);
2910 l_primary_uom VARCHAR2(10);
2911 l_currency_code VARCHAR2(10);
2912
2913 l_disallow_new_charge VARCHAR2(1);
2914 l_disallow_charge_update VARCHAR2(1);
2915 l_disallow_request_update VARCHAR2(1);
2916 l_create_charge_flag VARCHAR2(1);
2917 l_create_cost_flag VARCHAR2(1);
2918
2919 l_txn_billing_type_id NUMBER;
2920 l_inv_item_id NUMBER;
2921 l_line_order_category_code VARCHAR2(10);
2922 l_no_charge VARCHAR2(1);
2923 lx_quantity NUMBER;
2924
2925 BEGIN
2926
2927 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2928 THEN
2929 FND_LOG.String
2930 ( FND_LOG.level_procedure ,
2931 L_LOG_MODULE || '',
2932 'Inside Validate_Cost_Details'
2933 );
2934 END IF;
2935
2936 IF p_validation_mode = 'U' THEN
2937
2938 IF pv_cost_rec.cost_id IS NULL OR
2939 pv_cost_rec.cost_id = FND_API.G_MISS_NUM THEN
2940 Add_Null_Parameter_Msg(l_api_name,'cost_id') ;
2941 Add_Invalid_Argument_Msg(l_api_name,TO_CHAR(pv_cost_rec.estimate_detail_id),'cost_id');
2942 RAISE FND_API.G_EXC_ERROR;
2943 ELSE -- validate the cost id passed
2944 IF IS_COST_ID_VALID(p_cost_id => pv_cost_rec.cost_id,
2945 x_msg_data => l_msg_data,
2946 x_msg_count => l_msg_count,
2947 x_return_status => l_return_status) = 'U' THEN
2948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2949
2950 ELSIF IS_COST_ID_VALID( p_cost_id => pv_cost_rec.cost_id,
2951 x_msg_data => l_msg_data,
2952 x_msg_count => l_msg_count,
2953 x_return_status => l_return_status) = 'N' THEN
2954 Add_Invalid_Argument_Msg(l_api_name,TO_CHAR(pv_cost_rec.cost_id),'cost_id');
2955 RAISE FND_API.G_EXC_ERROR;
2956 ELSE
2957 --cost id is valid
2958 --assign to out record
2959 x_cost_Rec.cost_id := pv_cost_rec.cost_id;
2960 -- Get existing cost record for this estimate detail_id
2961 Get_Cost_Detail_Rec (P_API_NAME => l_api_name_full,
2962 P_COST_ID => pv_cost_rec.cost_id,
2963 x_COST_DETAIL_REC => l_db_det_rec,
2964 x_MSG_DATA => l_msg_data,
2965 x_MSG_COUNT => l_msg_count,
2966 x_RETURN_STATUS => l_return_status);
2967
2968
2969 if (l_return_status = fnd_api.g_ret_sts_error) then
2970 RAISE FND_API.G_EXC_ERROR;
2971 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
2972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2973 end if;
2974 END IF;
2975 END IF;
2976 END IF;
2977
2978 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
2979 THEN
2980 FND_LOG.String
2981 ( FND_LOG.level_procedure ,
2982 L_LOG_MODULE || '',
2983 'After Cost_Id Validation'
2984 );
2985 END IF;
2986
2987 IF pv_cost_rec.quantity <> l_db_det_rec.quantity and pv_cost_rec.quantity <> fnd_api.g_miss_num THEN
2988 -- cost will be recalculated if quantity changes during updation
2989 l_recalc_cost := 'Y';
2990 END IF;
2991 x_cost_rec:=pv_cost_rec;
2992
2993 ------------------------------------------------------------------------
2994 /* 1. Incident ID - Mandatory, If null or invalid throw Error message and stop processing
2995 */
2996
2997 if p_validation_mode ='I' then
2998 if pv_cost_rec.incident_id is not null then
2999
3000 l_valid_check := IS_INCIDENT_ID_VALID
3001 (
3002 p_incident_id => pv_cost_rec.incident_id,
3003 x_msg_data => l_msg_data,
3004 x_msg_count => l_msg_count,
3005 x_return_status => l_return_status
3006 );
3007 if l_return_status = g_ret_sts_unexp_error then
3008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3009 ELSIF l_return_status = G_RET_STS_ERROR THEN
3010 RAISE FND_API.G_EXC_ERROR;
3011 END IF;
3012
3013 if l_valid_check ='Y' then
3014 x_cost_rec.incident_id := pv_cost_rec.incident_id;
3015 else --throw the error message and stop processing
3016 Add_Invalid_Argument_Msg(l_api_name,
3017 pv_cost_rec.incident_id,
3018 'incident_id');
3019
3020 RAISE FND_API.G_EXC_ERROR;
3021 end if;
3022
3023 else
3024 Add_Null_Parameter_Msg(l_api_name,'p_incident_id') ;
3025 RAISE FND_API.G_EXC_ERROR;
3026 end if;
3027
3028 elsif p_validation_mode ='U' then
3029 -- Incident Id will not change, hence assign from the database
3030 x_cost_rec.incident_id :=l_db_det_rec.incident_id;
3031
3032 end if;
3033
3034 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3035 THEN
3036 FND_LOG.String
3037 ( FND_LOG.level_procedure ,
3038 L_LOG_MODULE || '',
3039 'After Incident_ID Validation'
3040 );
3041 END IF;
3042
3043
3044 ----------------------------------------------------------------------------------------------------------
3045 /* 2.Transaction_type_ID - Mandatory, If null or invalid throw Error message and stop processing
3046 */
3047
3048 IF p_validation_mode = 'U' THEN
3049
3050
3051 if pv_cost_rec.transaction_type_id = FND_API.G_MISS_NUM OR
3052 pv_cost_rec.transaction_type_id IS NULL THEN
3053
3054 --Default attributes using db record
3055 x_cost_rec.transaction_type_id := l_db_det_rec.transaction_type_id;
3056
3057 else
3058 --validate teh transaction type id passed
3059
3060 VALIDATE_TRANSACTION_TYPE_ID
3061 (
3062 p_api_name => p_api_name,
3063 p_transaction_type_id => pv_cost_rec.transaction_type_id,
3064 x_line_order_category_code => l_line_order_category_code,
3065 x_msg_data => l_msg_data,
3066 x_msg_count => l_msg_count,
3067 x_return_status => l_return_status
3068 ) ;
3069
3070 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3071 RAISE FND_API.G_EXC_ERROR ;
3072 end if;
3073
3074 --assign the values source_code, source_id to out record
3075 x_cost_rec.transaction_type_id := pv_cost_rec.transaction_type_id;
3076 l_transaction_type_changed := 'Y';
3077 lx_quantity :=pv_cost_rec.quantity ;
3078
3079 if pv_cost_rec.quantity = FND_API.G_MISS_NUM OR
3080 pv_cost_rec.quantity = null
3081 then
3082 lx_quantity :=l_db_det_rec.quantity;
3083 end if ;
3084
3085 IF (l_line_order_category_code = 'RETURN') then
3086 if lx_quantity is not null
3087 then
3088 if sign(pv_cost_rec.quantity) = -1 then
3089 x_cost_rec.quantity := lx_quantity;
3090 else
3091 --assign -ve qty to out record
3092 x_cost_rec.quantity := ( lx_quantity * -1);
3093 end if;
3094 end if;
3095 Else
3096 if lx_quantity is not null then
3097 if sign( lx_quantity ) = -1 then
3098 -- need to make this positive as no -ve quantity for orders
3099 x_cost_rec.quantity := ( lx_quantity * -1);
3100 else
3101 x_cost_rec.quantity := lx_quantity ;
3102 end if;
3103 end if;
3104 End if;
3105 end if;
3106 END IF;--validation mode
3107
3108 ----------------------------------------------------------------------------------------------------------
3109 /* 3. Check for the Status Flags - Mandatory,
3110 If null or invalid throw Error message and stop processing
3111 */
3112
3113
3114 /*
3115 When the costs are created from charges lines and the "Disallow Charge" flag is Yes (checked)
3116 the behavior should be the same as the current behavior so no records should be created.
3117 If the "Disallow Charge Update" flag is Yes (checked) then we should not allow updates for charges or costs.
3118
3119 However, for the scenario when Create Charge="N" and Create Cost="Y"
3120 we should not validate the flags since the costs are not dependent on the charge creation.
3121 In this case we should create the costs.
3122
3123 The costs should not be created only if the Disallow Request Update is Yes (checked).
3124 */
3125
3126
3127 get_charge_flags_from_sr
3128 (
3129 p_api_name => p_api_name,
3130 p_incident_id => x_cost_rec.incident_id,
3131 p_transaction_type_id => pv_cost_rec.transaction_type_id,
3132 x_create_charge_flag => l_create_charge_flag,
3133 x_create_cost_flag => l_create_cost_flag,
3134 x_disallow_request_update => l_disallow_request_update,
3135 x_disallow_new_charge => l_disallow_new_charge,
3136 x_disallow_charge_update => l_disallow_charge_update,
3137 x_msg_data => l_msg_data,
3138 x_msg_count => l_msg_count,
3139 x_return_status => l_return_status
3140 );
3141
3142 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3143 THEN
3144 FND_LOG.String
3145 ( FND_LOG.level_procedure ,
3146 L_LOG_MODULE || '',
3147 'l_create_cost_flag: '||l_create_cost_flag||'l_create_charge_flag: '||l_create_charge_flag
3148 );
3149
3150 FND_LOG.String
3151 ( FND_LOG.level_procedure ,
3152 L_LOG_MODULE || '',
3153 'l_disallow_request_update: '||l_disallow_request_update||'l_disallow_new_charge: '||l_disallow_new_charge||
3154 'l_disallow_charge_update: '||l_disallow_charge_update
3155 );
3156 END IF;
3157
3158
3159 IF p_validation_mode = 'I' THEN
3160 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
3161 if l_disallow_request_update='Y' THEN
3162
3163
3164 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
3165 FND_MSG_PUB.ADD;
3166 RAISE FND_API.G_EXC_ERROR;
3167 end if;
3168
3169 Else
3170 if l_disallow_new_charge = 'Y' OR l_disallow_request_update='Y' THEN
3171
3172
3173 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_INSERT');
3174 FND_MSG_PUB.ADD;
3175 RAISE FND_API.G_EXC_ERROR;
3176 end if;
3177
3178 end if;
3179
3180 ELSIF p_validation_mode = 'U' THEN
3181
3182
3183 If l_create_charge_flag ='N' and l_create_cost_flag = 'Y' then
3184 if l_disallow_request_update='Y' THEN
3185 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
3186 FND_MSG_PUB.ADD;
3187 RAISE FND_API.G_EXC_ERROR;
3188 end if;
3189
3190 Else
3191 if l_disallow_charge_update = 'Y' OR l_disallow_request_update='Y' THEN
3192
3193 FND_MESSAGE.SET_NAME('CS', 'CS_COST_CANNOT_UPDATE');
3194 FND_MSG_PUB.ADD;
3195 RAISE FND_API.G_EXC_ERROR;
3196 end if;
3197
3198 end if;
3199
3200 END IF;
3201
3202 ----------------------------------------------------------------------------------------------------------
3203 /* 4. Check for the Estimate Detail ID
3204 If null or invalid throw Error message and stop processing
3205 */
3206
3207 if p_validation_mode ='I' then
3208
3209 if pv_cost_rec.estimate_Detail_id is not null then
3210
3211 l_valid_check := IS_ESTIMATE_DETAIL_ID_VALID
3212 (
3213 p_estimate_detail_id => pv_cost_rec.estimate_detail_id,
3214 x_msg_data => l_msg_data,
3215 x_msg_count => l_msg_count,
3216 x_return_status => l_return_status
3217 );
3218 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3220 ELSIF l_return_status = G_RET_STS_ERROR THEN
3221 RAISE FND_API.G_EXC_ERROR;
3222 END IF;
3223
3224 if l_valid_check ='Y' then
3225 x_cost_rec.estimate_detail_id := pv_cost_rec.estimate_detail_id ;
3226 else --throw the error message and stop processing
3227 Add_Invalid_Argument_Msg(l_api_name,
3228 pv_cost_rec.estimate_Detail_id,
3229 'estimate_detail_id');
3230 RAISE FND_API.G_EXC_ERROR;
3231 end if;
3232
3233 else -- if null , then just assign
3234 x_cost_rec.estimate_Detail_id := pv_cost_rec.estimate_detail_id;
3235
3236 end if;
3237 elsif p_validation_mode ='U' then
3238 -- Estimate Detail Id will not change for the cost_id, hence assign from the database
3239
3240 x_cost_rec.estimate_detail_id :=l_db_det_rec.estimate_Detail_id;
3241
3242 end if;
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 'After Estimate_Detail_ID Validation'
3251 );
3252 END IF;
3253
3254 ----------------------------------------------
3255
3256 /* 5. Source Id and Source Code Mandatory
3257 validate the source id and source code against the respective tables
3258 */
3259
3260
3261
3262 IF p_validation_mode = 'I' THEN
3263 if pv_cost_rec.source_id is not null and pv_cost_rec.source_code is not null then
3264
3265 VALIDATE_SOURCE
3266 (
3267 p_api_name => p_api_name,
3268 p_source_code => pv_cost_rec.source_code,
3269 p_source_id => pv_cost_rec.source_id,
3270 --x_source_code => l_source_code,
3271 x_source_id => l_source_id,
3272 x_msg_data => l_msg_data,
3273 x_msg_count => l_msg_count,
3274 x_return_status => l_return_status
3275 ) ;
3276 If l_return_status = G_RET_STS_UNEXP_ERROR then
3277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3278 elsif l_return_status = G_RET_STS_ERROR then
3279 RAISE FND_API.G_EXC_ERROR;
3280 end if;
3281
3282 x_cost_Rec.source_id := l_source_id;
3283 x_cost_rec.source_code := pv_cost_rec.source_code;
3284
3285
3286 else
3287
3288 Add_Null_Parameter_Msg(l_api_name,
3289 'p_source_id') ;
3290 /*
3291 Add_Invalid_Argument_Msg(l_api_name,
3292 to_char(pv_cost_rec.incident_id),
3293 'source_id');*/
3294 RAISE FND_API.G_EXC_ERROR;
3295 end if;
3296
3297 ELSIF p_validation_mode = 'U' THEN
3298
3299 if pv_cost_rec.source_code = FND_API.G_MISS_CHAR OR
3300 pv_cost_rec.source_code IS NULL AND
3301 pv_cost_rec.source_id = FND_API.G_MISS_NUM OR
3302 pv_cost_rec.source_id IS NULL THEN
3303
3304 --Default attributes using db record
3305 x_cost_rec.source_code := l_db_det_rec.source_code;
3306 x_cost_rec.source_id := l_db_det_rec.source_id;
3307 else
3308
3309 VALIDATE_SOURCE
3310 (
3311 p_api_name => p_api_name,
3312 p_source_code => pv_cost_rec.source_code,
3313 p_source_id => pv_cost_rec.source_id,
3314 --x_source_code => l_source_code,
3315 x_source_id => l_source_id,
3316 x_msg_data => l_msg_data,
3317 x_msg_count => l_msg_count,
3318 x_return_status => l_return_status
3319 ) ;
3320
3321 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3322 RAISE FND_API.G_EXC_ERROR ;
3323 end if;
3324
3325 --assign the values source_code, source_id to out record
3326 x_cost_rec.source_code := x_cost_rec.source_code;
3327 x_cost_rec.source_id := x_cost_rec.source_id;
3328
3329
3330 end if;
3331
3332 END IF;--validation_mode
3333 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3334 THEN
3335 FND_LOG.String
3336 ( FND_LOG.level_procedure ,
3337 L_LOG_MODULE || '',
3338 'After Source Id and Source Code Validation'
3339 );
3340 END IF;
3341 ---------------------------------------------------
3342 /* 5. Operating Unit
3343 Check if the passed value for the org_id is mandatory .
3344 If not passed then default it from the Multi Org Setup --get this clarified
3345
3346 */
3347
3348 /*Validation Logic
3349
3350 1 If create_charge_flag =N then
3351 1 if org id is passed validate it and assign it to the out record
3352 2 if not passed then assign the multi org id to the out record
3353
3354 2.If create_charge_flag ='Y', then there will be an additional check for the profile
3355 'Service:Allow Charge Operating Unit Update'
3356 IF the profile is set to 'Y' then
3357 1.If Org _id is passed then validate that org id and assign it to the out rec
3358 2.If org id is not passed then assign the Multi Org Id
3359 If Profile is set to 'N'
3360 1.If Org Id is passed
3361 1. Check this with the Multi Org id.If not equal Throw an error message
3362 2.If equal assign it to the OUT record
3363 2 If Org Id is not passed assign Multi Org id to the out record
3364 */
3365
3366 --Get the Multi Org ID
3367
3368 CS_Multiorg_PUB.Get_OrgId
3369 (
3370 P_API_VERSION => 1.0,
3371 P_INIT_MSG_LIST => FND_API.G_FALSE,
3372 -- Fix bug 3236597 P_COMMIT => 'T',
3373 P_COMMIT => 'F', -- Fix bug 3236597
3374 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3375 X_RETURN_STATUS => l_return_status,
3376 X_MSG_COUNT => l_msg_count,
3377 X_MSG_DATA => l_msg_data,
3378 P_INCIDENT_ID => pv_cost_rec.incident_id,
3379 X_ORG_ID => l_org_id,
3380 X_PROFILE => l_profile
3381 );
3382
3383
3384
3385
3386
3387 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3388 RAISE FND_API.G_EXC_ERROR ;
3389 END IF;
3390
3391 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3392 THEN
3393 FND_LOG.String
3394 ( FND_LOG.level_procedure ,
3395 L_LOG_MODULE || '',
3396 'Mutli Org ID l_org_id : '||l_org_id||'l_profile :'||l_profile
3397 );
3398 END IF;
3399
3400
3401 IF p_validation_mode = 'I' THEN
3402 IF l_create_charge_flag ='Y' then
3403 IF l_profile = 'Y' THEN
3404
3405 if pv_cost_rec.org_id is not null then
3406
3407 VALIDATE_ORG_ID
3408 (
3409 P_API_NAME => l_api_name,
3410 P_ORG_ID => pv_cost_rec.org_id,
3411 X_RETURN_STATUS => l_return_status,
3412 X_MSG_COUNT => l_msg_count,
3413 X_MSG_DATA => l_msg_data
3414 );
3415
3416
3417 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3418 RAISE FND_API.G_EXC_ERROR ;
3419 end if;
3420 x_cost_rec.org_id := pv_cost_rec.org_id;
3421 else
3422
3423 --use the default
3424 x_cost_rec.org_id := l_org_id;
3425 end if;
3426 else
3427 -- l_profile = 'N'--Service:Allow Charge Operating Unit Update
3428 if pv_cost_rec.org_id is not null then
3429
3430 -- dbms_output.put_line(' OU 1 l_profile : '||l_profile||pv_cost_rec.org_id||'-'||l_org_id);
3431 if pv_cost_rec.org_id <> l_org_id then
3432 --raise error
3433 --Need to define error here
3434 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_CHANGE_OU');
3435 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
3436 FND_MSG_PUB.ADD;
3437 RAISE FND_API.G_EXC_ERROR;
3438 else
3439 x_cost_rec.org_id := pv_cost_rec.org_id;
3440 end if;
3441 else
3442 --pv_cost_rec.org_id is null
3443 --assign default
3444 x_cost_rec.org_id := l_org_id;
3445 end if;
3446 end if;--l_profile
3447 ELSE --flags create_charge_flag='N'
3448
3449
3450 if pv_cost_rec.org_id is not null then
3451
3452 VALIDATE_ORG_ID(
3453 P_API_NAME => l_api_name,
3454 P_ORG_ID => pv_cost_rec.org_id,
3455 X_RETURN_STATUS => l_return_status,
3456 X_MSG_COUNT => l_msg_count,
3457 X_MSG_DATA => l_msg_data
3458 );
3459
3460 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3461 RAISE FND_API.G_EXC_ERROR ;
3462 end if;
3463 x_cost_rec.org_id := pv_cost_rec.org_id;
3464 else
3465 --use the default
3466 x_cost_rec.org_id := l_org_id;
3467 end if;
3468
3469 END IF;--flags
3470
3471 ELSIF p_validation_mode = 'U' THEN
3472
3473
3474 IF l_create_charge_flag ='Y' then
3475 IF l_profile = 'Y' THEN
3476
3477 -- If l_profile = 'Y' THEN if org_id is not passed
3478 -- or org_id is null then assign the value from the database
3479 -- else if passed then validate the org_id and if valid then
3480 -- assign the value to the out parameter
3481
3482 if pv_cost_rec.org_id = FND_API.G_MISS_NUM or
3483 pv_cost_rec.org_id IS NULL THEN
3484 --use the value from the database
3485 x_cost_rec.org_id := l_db_det_rec.org_id;
3486
3487 else
3488
3489 VALIDATE_ORG_ID(
3490 P_API_NAME => l_api_name,
3491 P_ORG_ID => pv_cost_rec.org_id,
3492 X_RETURN_STATUS => l_return_status,
3493 X_MSG_COUNT => l_msg_count,
3494 X_MSG_DATA => l_msg_data);
3495
3496 if l_return_status <> fnd_api.g_ret_sts_success then
3497 raise fnd_api.g_exc_error ;
3498 end if;
3499 x_cost_rec.org_id := pv_cost_rec.org_id;
3500
3501 end if;
3502
3503 ELSE
3504 -- l_profile = 'N'
3505 -- If l_profile = 'N' THEN if org_id is not passed
3506 -- or org_id is null then assign the value from the database
3507 -- else if passed then validate the org_id and if valid then
3508 -- assign the value to the out parameter
3509
3510 IF pv_cost_rec.org_id = FND_API.G_MISS_NUM OR
3511 pv_cost_rec.org_id IS NULL THEN
3512 --use the value from the database
3513 x_cost_rec.org_id := l_db_det_rec.org_id;
3514
3515 ELSE
3516 IF pv_cost_rec.org_id <> l_db_det_rec.org_id THEN
3517 --raise error
3518 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_CHANGE_OU');
3519 FND_MESSAGE.SET_TOKEN('API_NAME', p_api_name);
3520 FND_MSG_PUB.ADD;
3521 RAISE FND_API.G_EXC_ERROR;
3522 ELSE
3523 x_cost_rec.org_id := pv_cost_rec.org_id;
3524 END IF;
3525 END IF;
3526 END IF;-- profile
3527 END IF;
3528 else--if create_charge_flag='N'
3529
3530 if pv_cost_rec.org_id = FND_API.G_MISS_NUM or
3531 pv_cost_rec.org_id IS NULL THEN
3532 --use the value from the database
3533 x_cost_rec.org_id := l_db_det_rec.org_id;
3534
3535 else
3536 VALIDATE_ORG_ID(
3537 P_API_NAME => l_api_name,
3538 P_ORG_ID => pv_cost_rec.org_id,
3539 X_RETURN_STATUS => l_return_status,
3540 X_MSG_COUNT => l_msg_count,
3541 X_MSG_DATA => l_msg_data);
3542
3543 if l_return_status <> fnd_api.g_ret_sts_success then
3544 raise fnd_api.g_exc_error ;
3545 end if;
3546 x_cost_rec.org_id := pv_cost_rec.org_id;
3547
3548 end if;
3549
3550 END IF;--validation mode
3551
3552 IF x_cost_rec.org_id <> l_db_det_rec.org_id THEN
3553 -- Item is changed so recalculate the cost.
3554 --cost will be recalculated if item,ou or quantity changes during updation
3555 l_recalc_cost := 'Y' ;
3556 --dbms_output.put_line('l_recalc_cost ORG changes');
3557 END IF;
3558 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3559 THEN
3560 FND_LOG.String
3561 ( FND_LOG.level_procedure ,
3562 L_LOG_MODULE || '',
3563 'After Org_ID Validation'
3564 );
3565 END IF;
3566 --------------------------------------------------------
3567 /* 5.Item Validation
3568 */
3569
3570
3571
3572 IF p_validation_mode = 'I' THEN
3573 --Added for the Debrief flow
3574 /* If the SAC setup is Create Charge = 'No' and Create Cost ='Yes' and no item is passed,
3575 then retrieve the inv. item from the profile Service: Default Inventory Item for Labor Transactions
3576 and proceed with the rest of the logic
3577 If the profile value is null, and no item is passed by the calling program,
3578 then raise an error message and abort the process.*/
3579 l_no_charge :='N';
3580 Get_Item_from_Profile(
3581 P_TRANSACTION_TYPE_ID =>pv_cost_rec.transaction_type_id,
3582 p_inv_item_id =>pv_cost_rec.inventory_item_id ,
3583 p_no_charge =>l_no_charge,
3584 x_inv_item_id =>l_inv_item_id,
3585 x_msg_data => l_msg_data,
3586 x_msg_count => l_msg_count,
3587 x_return_status => l_return_status
3588 );
3589 if l_no_charge = 'Y' then
3590 if l_inv_item_id is null then
3591 Add_Null_Parameter_Msg(l_api_name,
3592 'p_inventory_item_id') ;
3593 RAISE FND_API.G_EXC_ERROR;
3594 else
3595 x_cost_rec.inventory_item_id:=l_inv_item_id;
3596 end if;
3597 end if;
3598
3599 if l_no_charge <> 'Y' then
3600 if pv_cost_rec.inventory_item_id is not null then
3601
3602 l_valid_check := IS_ITEM_VALID
3603 (
3604 p_org_id => x_cost_rec.org_id,
3605 p_inventory_item_id => pv_cost_rec.inventory_item_id,
3606 x_msg_data => l_msg_data,
3607 x_msg_count => l_msg_count,
3608 x_return_status => l_return_status
3609 );
3610
3611
3612
3613 if l_valid_check ='Y' then
3614
3615 x_cost_rec.inventory_item_id := pv_cost_rec.inventory_item_id ;
3616 else --throw the error message and stop processing
3617 Add_Invalid_Argument_Msg(l_api_name,
3618 to_char(pv_cost_rec.inventory_item_id),
3619 'inventory_item_id');
3620
3621 RAISE FND_API.G_EXC_ERROR;
3622 end if;
3623
3624 else
3625
3626 Add_Null_Parameter_Msg(l_api_name,
3627 'p_inventory_item_id') ;
3628 /* Add_Invalid_Argument_Msg(l_api_name,
3629 to_char(pv_cost_rec.inventory_item_id),
3630 'inventory_item_id');*/
3631 RAISE FND_API.G_EXC_ERROR;
3632
3633 end if;
3634 end if;
3635 ELSIF p_validation_mode = 'U' THEN
3636
3637 if pv_cost_rec.inventory_item_id = FND_API.G_MISS_NUM OR
3638 pv_cost_rec.inventory_item_id IS NULL
3639 then
3640
3641 --Default attributes using db record
3642 x_cost_rec.inventory_item_id := l_db_det_rec.inventory_item_id;
3643
3644 else
3645
3646 l_valid_check := IS_ITEM_VALID
3647 (
3648 p_org_id => x_cost_rec.org_id,
3649 p_inventory_item_id => x_cost_rec.inventory_item_id,
3650 x_msg_data => l_msg_data,
3651 x_msg_count => l_msg_count,
3652 x_return_status => l_return_status
3653 );
3654
3655 if l_valid_check ='Y' then
3656 --assign the values inventory_item_id to out record
3657 x_cost_rec.inventory_item_id := pv_cost_rec.inventory_item_id ;
3658 else --throw the error message and stop processing
3659 Add_Invalid_Argument_Msg(l_api_name,
3660 to_char(pv_cost_rec.inventory_item_id),
3661 'inventory_item_id');
3662
3663 RAISE FND_API.G_EXC_ERROR;
3664 end if;
3665 end if;
3666
3667
3668 IF x_cost_rec.inventory_item_id <> l_db_det_rec.inventory_item_id THEN
3669 -- Item is changed so recalculate the cost.
3670 --cost will be recalculated if item,ou or quantity changes during updation
3671 l_recalc_cost := 'Y' ;
3672 l_item_changed := 'Y';
3673 END IF;
3674
3675
3676 END IF; --validation_mode
3677
3678 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3679 THEN
3680
3681 FND_LOG.String
3682 ( FND_LOG.level_procedure ,
3683 L_LOG_MODULE || '',
3684 'After Item Id Validation'
3685 );
3686 END If;
3687
3688
3689
3690
3691
3692 IF p_validation_mode = 'I' THEN
3693 if pv_cost_rec.inventory_org_id is not null then
3694
3695 l_valid_check := IS_TXN_INV_ORG_VALID
3696 (p_txn_inv_org => pv_cost_rec.inventory_org_id,
3697 --p_org_id => l_org_id,
3698 p_org_id => x_cost_rec.org_id,
3699 x_msg_data => l_msg_data,
3700 x_msg_count => l_msg_count,
3701 x_return_status => l_return_status ) ;
3702
3703
3704
3705 if l_valid_check ='Y' then
3706
3707 x_cost_rec.inventory_org_id := pv_cost_rec.inventory_org_id ;
3708 else --throw the error message and stop processing
3709 Add_Invalid_Argument_Msg(l_api_name,
3710 to_char(pv_cost_rec.inventory_org_id),
3711 'inventory_org_id');
3712
3713 RAISE FND_API.G_EXC_ERROR;
3714 end if;
3715
3716
3717
3718
3719 end if;
3720
3721 ELSIF p_validation_mode = 'U' THEN
3722
3723 if pv_cost_rec.inventory_org_id = FND_API.G_MISS_NUM OR
3724 pv_cost_rec.inventory_org_id IS NULL
3725 then
3726
3727 --Default attributes using db record
3728 x_cost_rec.inventory_org_id := l_db_det_rec.inventory_org_id;
3729
3730 else
3731
3732 l_valid_check := IS_TXN_INV_ORG_VALID
3733 (p_txn_inv_org => pv_cost_rec.inventory_org_id,
3734 --p_org_id => l_org_id,
3735 p_org_id => x_cost_rec.org_id,
3736 x_msg_data => l_msg_data,
3737 x_msg_count => l_msg_count,
3738 x_return_status => l_return_status ) ;
3739
3740 if l_valid_check ='Y' then
3741 --assign the values inventory_org_id to out record
3742 x_cost_rec.inventory_org_id := pv_cost_rec.inventory_org_id ;
3743 else --throw the error message and stop processing
3744 Add_Invalid_Argument_Msg(l_api_name,
3745 to_char(pv_cost_rec.inventory_org_id),
3746 'inventory_org_id');
3747
3748 RAISE FND_API.G_EXC_ERROR;
3749 end if;
3750 end if;
3751
3752
3753
3754 END IF; --validation_mode
3755
3756 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3757 THEN
3758
3759 FND_LOG.String
3760 ( FND_LOG.level_procedure ,
3761 L_LOG_MODULE || '',
3762 'After Inventory Org Id Validation'
3763 );
3764 END If;
3765
3766
3767
3768 --------------------------------------------------------
3769 /* 5.transaction Billing Type Validation
3770 */
3771
3772 IF p_validation_mode = 'I' THEN
3773
3774 get_txn_billing_type(p_api_name => p_api_name,
3775 p_inv_id => x_cost_rec.inventory_item_id,
3776 p_txn_type_id => x_cost_rec.transaction_type_id,
3777 x_txn_billing_type_id => l_txn_billing_type_id,
3778 x_msg_data => l_msg_data,
3779 x_msg_count => l_msg_count,
3780 x_return_status => l_return_status);
3781
3782
3783 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3784 RAISE FND_API.G_EXC_ERROR ;
3785 ELSE
3786 IF pv_cost_rec.txn_billing_type_id IS NOT NULL THEN
3787 IF pv_cost_rec.txn_billing_type_id <> l_txn_billing_type_id THEN
3788 --RAISE ERROR
3789 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_TXN_BILLING_TYP');
3790 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3791 FND_MSG_PUB.ADD;
3792 RAISE FND_API.G_EXC_ERROR;
3793 ELSE --
3794 --the ids match
3795 --assign to the out record
3796 x_cost_rec.txn_billing_type_id := pv_cost_rec.txn_billing_type_id ;
3797
3798
3799 END IF;
3800
3801 ELSE
3802 -- pv_cost_rec.txn_billing_type_id is null
3803 -- assign l_txn_billing_type_id to out record
3804 x_cost_rec.txn_billing_type_id := l_txn_billing_type_id;
3805 END IF;
3806 VALIDATE_OPERATING_UNIT(p_api_name => l_api_name_full,
3807 p_txn_billing_type_id => l_txn_billing_type_id,
3808 x_return_status => l_return_status,
3809 x_msg_count => l_msg_count,
3810 x_msg_data => l_msg_data);
3811
3812 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3813 FND_MESSAGE.SET_NAME('CS', 'CS_COST_INVALID_OU_BILLING_TYP');
3814 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3815 FND_MSG_PUB.ADD;
3816 RAISE FND_API.G_EXC_ERROR;
3817 End if;
3818
3819 END IF;
3820 ELSIF p_validation_mode = 'U' THEN
3821
3822 IF l_item_changed = 'Y' OR
3823 l_transaction_type_changed = 'Y' THEN
3824
3825 --need to get the txn billing type for changed parameters
3826
3827 GET_TXN_BILLING_TYPE(P_API_NAME => p_api_name,
3828 P_INV_ID => x_cost_rec.inventory_item_id,
3829 P_TXN_TYPE_ID => x_cost_rec.transaction_type_id,
3830 X_TXN_BILLING_TYPE_ID => l_txn_billing_type_id,
3831 X_MSG_DATA => l_msg_data,
3832 X_MSG_COUNT => l_msg_count,
3833 X_RETURN_STATUS => l_return_status);
3834
3835 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3836 RAISE FND_API.G_EXC_ERROR ;
3837 ELSE
3838 VALIDATE_OPERATING_UNIT(p_api_name => p_api_name,
3839 p_txn_billing_type_id => l_txn_billing_type_id,
3840 x_return_status => l_return_status,
3841 x_msg_count => l_msg_count,
3842 x_msg_data => l_msg_data);
3843
3844 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3845 RAISE FND_API.G_EXC_ERROR ;
3846 ELSE
3847 IF pv_cost_rec.txn_billing_type_id <> FND_API.G_MISS_NUM AND
3848 pv_cost_rec.txn_billing_type_id IS NOT NULL THEN
3849 IF pv_cost_rec.txn_billing_type_id <> l_txn_billing_type_id THEN
3850
3851 --RAISE ERROR
3852 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_TXN_BILLING_TYP');
3853 FND_MESSAGE.SET_TOKEN('TXN_BILLING_TYPE_ID', pv_cost_rec.txn_billing_type_id);
3854 FND_MSG_PUB.ADD;
3855 RAISE FND_API.G_EXC_ERROR;
3856 ELSE --
3857 --the ids match
3858 --assign to the out record
3859 x_cost_rec.txn_billing_type_id := pv_cost_rec.txn_billing_type_id ;
3860 END IF;
3861 ELSE
3862 -- pv_cost_rec.txn_billing_type_id is not passed
3863 -- assign l_txn_billing_type_id to out record
3864 x_cost_rec.txn_billing_type_id := l_txn_billing_type_id;
3865 END IF;
3866 END IF;
3867 END IF;
3868 ELSE
3869
3870 -- niether the item nor the transaction type is changed
3871 -- assign the billing type from db
3872 x_cost_rec.txn_billing_type_id := l_db_det_rec.txn_billing_type_id;
3873
3874 END IF;
3875
3876 --DBMS_OUTPUT.PUT_LINE('Completed the txn billing type id');
3877
3878 END IF;
3879
3880 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3881 THEN
3882 FND_LOG.String
3883 ( FND_LOG.level_procedure ,
3884 L_LOG_MODULE || '',
3885 'After Billing Type Validation'
3886 );
3887 END IF;
3888
3889
3890
3891 --------------------------------------------------------
3892
3893
3894 /* 6.Unit of Measure
3895 Check if the passed value for the UOM is valid
3896 If not passed then default the Item' primary UOM
3897 */
3898
3899
3900
3901 IF p_validation_mode = 'I' THEN
3902
3903 IF pv_cost_rec.unit_of_measure_code IS NOT NULL THEN
3904
3905 l_valid_check := IS_UOM_VALID
3906 (
3907 p_inv_id => x_cost_rec.inventory_item_id,
3908 p_org_id => x_cost_rec.org_id,
3909 p_uom_code => pv_cost_rec.unit_of_measure_code,
3910 x_msg_data => l_msg_data,
3911 x_msg_count => l_msg_count,
3912 x_return_status => l_return_status
3913 );
3914
3915 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3917 ELSIF l_return_status = G_RET_STS_ERROR THEN
3918 RAISE FND_API.G_EXC_ERROR;
3919 END IF;
3920
3921 IF l_valid_check <> 'Y' THEN
3922 Add_Invalid_Argument_Msg(l_api_name,
3923 pv_cost_rec.unit_of_measure_code,
3924 'Unit_of_Measure_Code');
3925 RAISE FND_API.G_EXC_ERROR;
3926
3927 ELSE
3928 --assign to out record
3929 x_cost_rec.unit_of_measure_code := pv_cost_rec.unit_of_measure_code;
3930 END IF;
3931
3932 ELSE --If UOM is not passed then default the Item's Primary UOM
3933
3934
3935 GET_PRIMARY_UOM(P_INVentory_item_ID => x_cost_rec.inventory_item_id,
3936 p_org_id => x_cost_rec.org_id,
3937 X_PRIMARY_UOM => l_primary_uom,
3938 X_MSG_DATA => l_msg_data ,
3939 X_MSG_COUNT => l_msg_count,
3940 X_RETURN_STATUS => l_return_status);
3941
3942 --DBMS_OUTPUT.PUT_LINE('Back from GET_PRIMARY_UOM status='||l_return_status || ' l_primary_uom '||l_primary_uom);
3943
3944 --IF l_return_status <> 'S' THEN
3945 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3946 --raise error
3947 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
3948 FND_MESSAGE.SET_TOKEN('INV_ID', x_cost_rec.inventory_item_id);
3949 FND_MSG_PUB.ADD;
3950 RAISE FND_API.G_EXC_ERROR;
3951 END IF;
3952
3953 --assign to out record
3954 x_cost_rec.unit_of_measure_code := l_primary_uom;
3955
3956 END IF;
3957 ELSIF p_validation_mode = 'U' THEN
3958
3959 IF pv_cost_rec.unit_of_measure_code <> FND_API.G_MISS_CHAR AND
3960 pv_cost_rec.unit_of_measure_code IS NOT NULL
3961
3962 then
3963
3964
3965 l_valid_check := IS_UOM_VALID(
3966 p_inv_id => x_cost_rec.inventory_item_id,
3967 p_org_id => x_cost_rec.org_id,
3968 p_uom_code => pv_cost_rec.unit_of_measure_code,
3969 x_msg_data => l_msg_data,
3970 x_msg_count => l_msg_count,
3971 x_return_status => l_return_status
3972 );
3973
3974 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3976 ELSIF l_return_status = G_RET_STS_ERROR THEN
3977 RAISE FND_API.G_EXC_ERROR;
3978 END IF;
3979
3980 IF l_valid_check <> 'Y' THEN
3981 Add_Invalid_Argument_Msg(l_api_name,
3982 pv_cost_rec.unit_of_measure_code,
3983 'Unit_of_Measure_Code');
3984 RAISE FND_API.G_EXC_ERROR;
3985
3986 ELSE
3987 --assign to out record
3988 x_cost_rec.unit_of_measure_code := pv_cost_rec.unit_of_measure_code;
3989 END IF;
3990
3991 Else --If UOM is not passed then default the Item's Primary UOM
3992
3993
3994 GET_PRIMARY_UOM(P_INVentory_item_ID => x_cost_rec.inventory_item_id,
3995 p_org_id => x_cost_rec.org_id,
3996 X_PRIMARY_UOM => l_primary_uom,
3997 X_MSG_DATA => l_msg_data ,
3998 X_MSG_COUNT => l_msg_count,
3999 X_RETURN_STATUS => l_return_status);
4000
4001
4002
4003 --IF l_return_status <> 'S' THEN
4004 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4005 --raise error
4006 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4007 FND_MESSAGE.SET_TOKEN('INV_ID', x_cost_rec.inventory_item_id);
4008 FND_MSG_PUB.ADD;
4009 RAISE FND_API.G_EXC_ERROR;
4010 end if;
4011
4012 --assign to out record
4013 x_cost_rec.unit_of_measure_code := l_primary_uom;
4014
4015 End if;
4016 END IF;
4017
4018 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4019 THEN
4020 FND_LOG.String
4021 ( FND_LOG.level_procedure ,
4022 L_LOG_MODULE || '',
4023 'After UOM Validation'
4024 );
4025 END IF;
4026
4027
4028
4029 -----------------------------------------------
4030 /* 7.Currency Code
4031 Check if the passed value for the Currenc is valid
4032 If not passed then default the Currency from the Service Request Operating Unit
4033 */
4034
4035 IF p_validation_mode = 'I' THEN
4036
4037 IF pv_cost_rec.currency_code IS NOT NULL THEN
4038
4039 l_valid_check := IS_CURRENCY_CODE_VALID
4040 (
4041 p_currency_Code => pv_cost_rec.currency_code,
4042 x_msg_data => l_msg_data,
4043 x_msg_count => l_msg_count,
4044 x_return_status => l_return_status
4045 );
4046
4047 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4049 ELSIF l_return_status = G_RET_STS_ERROR THEN
4050 RAISE FND_API.G_EXC_ERROR;
4051 END IF;
4052
4053 IF l_valid_check <> 'Y' THEN
4054 Add_Invalid_Argument_Msg(l_api_name,
4055 pv_cost_rec.currency_code,
4056 'Currency Code');
4057 RAISE FND_API.G_EXC_ERROR;
4058
4059 ELSE
4060 --assign to out record
4061 x_cost_rec.currency_code := pv_cost_rec.currency_code;
4062 END IF;
4063
4064 ELSE --If UOM is not passed then default the Item's Primary UOM
4065
4066
4067 get_currency_code
4068 (
4069 p_org_id => x_cost_rec.org_id,
4070 X_CURRENCY_CODE => l_currency_code,
4071 X_MSG_DATA => l_msg_data ,
4072 X_MSG_COUNT => l_msg_count,
4073 X_RETURN_STATUS => l_return_status);
4074
4075 --DBMS_OUTPUT.PUT_LINE('Back from GET_PRIMARY_UOM status='||l_return_status || ' l_primary_uom '||l_primary_uom);
4076
4077 --IF l_return_status <> 'S' THEN
4078 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4079 --raise error
4080 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4081 FND_MESSAGE.SET_TOKEN('INV_ID', pv_cost_rec.unit_of_measure_code);
4082 FND_MSG_PUB.ADD;
4083 RAISE FND_API.G_EXC_ERROR;
4084 END IF;
4085
4086 --assign to out record
4087 x_cost_rec.currency_code := l_currency_code;
4088
4089 END IF;
4090 ELSIF p_validation_mode = 'U' THEN
4091
4092 IF pv_cost_rec.currency_code<> FND_API.G_MISS_CHAR AND
4093 pv_cost_rec.currency_code IS NOT NULL
4094
4095 then
4096
4097
4098 l_valid_check := IS_CURRENCY_CODE_VALID
4099 (
4100 p_currency_Code => pv_cost_rec.currency_code,
4101 x_msg_data => l_msg_data,
4102 x_msg_count => l_msg_count,
4103 x_return_status => l_return_status
4104 );
4105
4106 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4108 ELSIF l_return_status = G_RET_STS_ERROR THEN
4109 RAISE FND_API.G_EXC_ERROR;
4110 END IF;
4111
4112 IF l_valid_check <> 'Y' THEN
4113 Add_Invalid_Argument_Msg(l_api_name,
4114 pv_cost_rec.currency_code,
4115 'Currency Code');
4116 RAISE FND_API.G_EXC_ERROR;
4117
4118 ELSE
4119 --assign to out record
4120 x_cost_rec.currency_code := pv_cost_rec.currency_code;
4121 END IF;
4122
4123 Else --If UOM is not passed then default the Item's Primary UOM
4124
4125
4126 get_currency_code
4127 (
4128 p_org_id => x_cost_rec.org_id,
4129 X_CURRENCY_CODE => l_currency_code,
4130 X_MSG_DATA => l_msg_data ,
4131 X_MSG_COUNT => l_msg_count,
4132 X_RETURN_STATUS => l_return_status);
4133
4134
4135
4136
4137 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4138 --raise error
4139 FND_MESSAGE.SET_NAME('CS', 'CS_COST_GET_PRIMARY_UOM_ERROR');
4140 FND_MESSAGE.SET_TOKEN('INV_ID', pv_cost_rec.unit_of_measure_code);
4141 FND_MSG_PUB.ADD;
4142 RAISE FND_API.G_EXC_ERROR;
4143 END IF;
4144
4145 --assign to out record
4146 x_cost_rec.currency_code := l_currency_code;
4147
4148
4149 End if;
4150 END IF;
4151
4152 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4153 THEN
4154 FND_LOG.String
4155 ( FND_LOG.level_procedure ,
4156 L_LOG_MODULE || '',
4157 'After Currency Code Validation'
4158 );
4159 END IF;
4160
4161
4162 EXCEPTION
4163
4164
4165 WHEN FND_API.G_EXC_ERROR THEN
4166 x_return_status := FND_API.G_RET_STS_ERROR;
4167
4168 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4169 p_data => x_msg_data,
4170 p_encoded => FND_API.G_FALSE) ;
4171 WHEN OTHERS THEN
4172 x_return_status := FND_API.G_RET_STS_ERROR;
4173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4174 p_data => x_msg_data,
4175 p_encoded => FND_API.G_FALSE) ;
4176
4177
4178
4179
4180 END VALIDATE_COST_DETAILS;
4181
4182
4183 --=============================
4184 -- Record_Is_Locked_msg
4185 --=============================
4186
4187 PROCEDURE Record_Is_Locked_Msg
4188 ( p_token_an VARCHAR2
4189 )
4190 IS
4191
4192 BEGIN
4193
4194 FND_MESSAGE.Set_Name('CS', 'CS_API_ALL_CANT_LOCK_RECORD');
4195 FND_MESSAGE.Set_Token('API_NAME', p_token_an);
4196 FND_MSG_PUB.Add;
4197 END Record_IS_Locked_Msg;
4198
4199
4200 PROCEDURE Validate_Who_Info(
4201 P_API_NAME IN VARCHAR2,
4202 P_USER_ID IN NUMBER,
4203 P_LOGIN_ID IN NUMBER,
4204 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
4205
4206 CURSOR c_user IS
4207 SELECT 1
4208 FROM fnd_user
4209 WHERE user_id = p_user_id
4210 AND TRUNC(SYSDATE) <= start_date
4211 AND NVL(end_date, SYSDATE) >= SYSDATE;
4212
4213 CURSOR c_login IS
4214 SELECT 1
4215 FROM fnd_logins
4216 WHERE login_id = p_login_id
4217 AND user_id = p_user_id;
4218
4219 l_dummy VARCHAR2(1);
4220
4221 BEGIN
4222
4223 x_return_status := FND_API.G_RET_STS_SUCCESS;
4224
4225 BEGIN
4226 IF p_user_id = -1 then
4227 SELECT 'x' into l_dummy
4228 FROM fnd_user
4229 WHERE user_id = p_user_id;
4230 ELSE
4231 SELECT 'x' into l_dummy
4232 FROM fnd_user
4233 WHERE user_id = p_user_id
4234 AND trunc(sysdate) BETWEEN trunc(nvl(start_date, sysdate))
4235 AND trunc(nvl(end_date, sysdate));
4236 END IF;
4237
4238 EXCEPTION
4239 WHEN NO_DATA_FOUND THEN
4240 x_return_status := FND_API.G_RET_STS_ERROR;
4241 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
4242 p_token_v => TO_CHAR(p_user_id),
4243 p_token_p => 'p_user_id');
4244 return;
4245 END;
4246
4247 IF p_login_id is not null then
4248 BEGIN
4249 SELECT 'x' into l_dummy
4250 FROM fnd_logins
4251 WHERE login_id = p_login_id
4252 AND user_id = p_user_id;
4253
4254 EXCEPTION
4255 WHEN NO_DATA_FOUND THEN
4256 x_return_status := FND_API.G_RET_STS_ERROR;
4257 Add_Invalid_Argument_Msg(p_token_an => p_api_name,
4258 p_token_v => TO_CHAR(p_login_id),
4259 p_token_p => 'p_user_login');
4260 END;
4261 END IF;
4262
4263 END Validate_Who_Info;
4264 /**************************************************
4265 Private Procedure Body TO_NULL
4266 **************************************************/
4267
4268 PROCEDURE TO_NULL(p_cost_rec_in IN cs_cost_details_pub.Cost_Rec_Type,
4269 p_cost_rec_out OUT NOCOPY cs_cost_details_pub.Cost_Rec_Type) IS
4270 BEGIN
4271 p_cost_rec_out.cost_id := Check_For_Miss(p_cost_rec_in.cost_id) ;
4272 p_cost_rec_out.incident_id := Check_For_Miss(p_cost_rec_in.incident_id) ;
4273 p_cost_rec_out.estimate_Detail_id := Check_For_Miss(p_cost_rec_in.estimate_Detail_id) ;
4274 p_cost_rec_out.charge_line_type := Check_For_Miss(p_cost_rec_in.charge_line_type) ;
4275 p_cost_rec_out.transaction_type_id := Check_For_Miss(p_cost_rec_in.transaction_type_id) ;
4276 p_cost_rec_out.txn_billing_type_id := Check_For_Miss(p_cost_rec_in.txn_billing_type_id) ;
4277 p_cost_rec_out.inventory_item_id := Check_For_Miss(p_cost_rec_in.inventory_item_id) ;
4278 p_cost_rec_out. quantity := Check_For_Miss(p_cost_rec_in. quantity) ;
4279 p_cost_rec_out.unit_of_measure_code := Check_For_Miss(p_cost_rec_in.unit_of_measure_code) ;
4280 p_cost_rec_out.currency_code := Check_For_Miss(p_cost_rec_in.currency_code) ;
4281 p_cost_rec_out.source_id := Check_For_Miss(p_cost_rec_in.source_id) ;
4282 p_cost_rec_out.source_code := Check_For_Miss(p_cost_rec_in.source_code) ;
4283 p_cost_rec_out.org_id := Check_For_Miss(p_cost_rec_in.org_id) ;
4284 p_cost_rec_out.inventory_org_id := Check_For_Miss(p_cost_rec_in.inventory_org_id) ;
4285 --p_cost_rec_out.unit_cost := Check_For_Miss(p_cost_rec_in.unit_cost) ;
4286 p_cost_rec_out.extended_cost := Check_For_Miss(p_cost_rec_in.extended_cost) ;
4287 --p_cost_rec_out.override_ext_cost_flag := Check_For_Miss(p_cost_rec_in.override_ext_cost_flag) ;
4288 -- p_cost_rec_out.transaction_date := Check_For_Miss(p_cost_rec_in.transaction_date) ;
4289 p_cost_rec_out.attribute1 := Check_For_Miss(p_cost_rec_in.attribute1) ;
4290 p_cost_rec_out.attribute2 := Check_For_Miss(p_cost_rec_in.attribute2) ;
4291 p_cost_rec_out.attribute3 := Check_For_Miss(p_cost_rec_in.attribute3) ;
4292 p_cost_rec_out.attribute4 := Check_For_Miss(p_cost_rec_in.attribute4) ;
4293 p_cost_rec_out.attribute5 := Check_For_Miss(p_cost_rec_in.attribute5) ;
4294 p_cost_rec_out.attribute6 := Check_For_Miss(p_cost_rec_in.attribute6) ;
4295 p_cost_rec_out.attribute7 := Check_For_Miss(p_cost_rec_in.attribute7) ;
4296 p_cost_rec_out.attribute8 := Check_For_Miss(p_cost_rec_in.attribute8) ;
4297 p_cost_rec_out.attribute9 := Check_For_Miss(p_cost_rec_in.attribute9) ;
4298 p_cost_rec_out.attribute10 := Check_For_Miss(p_cost_rec_in.attribute10) ;
4299 p_cost_rec_out.attribute11 := Check_For_Miss(p_cost_rec_in.attribute11) ;
4300 p_cost_rec_out.attribute12 := Check_For_Miss(p_cost_rec_in.attribute12) ;
4301 p_cost_rec_out.attribute13 := Check_For_Miss(p_cost_rec_in.attribute13) ;
4302 p_cost_rec_out.attribute14 := Check_For_Miss(p_cost_rec_in.attribute14) ;
4303 p_cost_rec_out.attribute15 := Check_For_Miss(p_cost_rec_in.attribute15) ;
4304 END TO_NULL;
4305
4306 /*************************************************
4307 Function Implementations
4308 **************************************************/
4309 FUNCTION Check_For_Miss ( p_param IN NUMBER ) RETURN NUMBER IS
4310 BEGIN
4311
4312 IF p_param = FND_API.G_MISS_NUM THEN
4313 RETURN NULL ;
4314 ELSE
4315 RETURN p_param ;
4316 END IF ;
4317 END Check_For_Miss ;
4318
4319
4320 FUNCTION Check_For_Miss ( p_param IN VARCHAR2 ) RETURN VARCHAR2 IS
4321 BEGIN
4322 IF p_param = FND_API.G_MISS_CHAR THEN
4323 RETURN NULL ;
4324 ELSE
4325 RETURN p_param ;
4326 END IF ;
4327 END Check_For_Miss ;
4328
4329
4330 FUNCTION Check_For_Miss ( p_param IN DATE ) RETURN DATE IS
4331 BEGIN
4332 IF p_param = FND_API.G_MISS_DATE THEN
4333 RETURN NULL ;
4334 ELSE
4335 RETURN p_param ;
4336 END IF ;
4337 END Check_For_Miss ;
4338
4339 --------------------------------------------------------------------------------
4340 -- Procedure Name : PURGE_COST
4341 --
4342 -- Parameters (other than standard ones)
4343 -- IN
4344 -- p_object_type : Type of object for which this procedure is
4345 -- being called. (Here it will be 'SR')
4346 -- p_processing_set_id : Id that helps the API in identifying the
4347 -- set of SRs for which the child objects have
4348 -- to be deleted.
4349 --
4350 -- Description
4351 -- This procedure physically deletes all the cost lines attached to
4352 -- a service request. It reads the list of SRs for which the cost lines
4353 -- have to be deleted from the global temp table, looking only for rows
4354 -- having the purge_status as NULL. Using Set processing, the procedure
4355 -- deletes all the cost lines attached to such SRs.
4356 --
4357 -- HISTORY
4358 --
4359 ----------------+------------+--------------------------------------------------
4360 -- DATE | UPDATED BY | Change Description
4361 ----------------+------------+--------------------------------------------------
4362 -- 26-dec-2007 | bkanimoz | Created
4363 -- | |
4364 ----------------+------------+--------------------------------------------------
4365 PROCEDURE Purge_Cost
4366 (
4367 p_api_version_number IN NUMBER := 1.0
4368 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4369 , p_commit IN VARCHAR2 := FND_API.G_FALSE
4370 , p_object_type IN VARCHAR2
4371 , p_processing_set_id IN NUMBER
4372 , x_return_status OUT NOCOPY VARCHAR2
4373 , x_msg_count OUT NOCOPY NUMBER
4374 , x_msg_data OUT NOCOPY VARCHAR2
4375 )
4376 IS
4377 --------------------------------------------------------------------------------
4378
4379 L_API_VERSION CONSTANT NUMBER := 1.0;
4380 L_API_NAME CONSTANT VARCHAR2(30) := 'PURGE_COST';
4381 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
4382 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'csxvcstb.plsql.' || L_API_NAME_FULL || '.';
4383
4384 l_row_count NUMBER := 0;
4385
4386 BEGIN
4387 x_return_status := FND_API.G_RET_STS_SUCCESS;
4388
4389 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4390 THEN
4391 FND_LOG.String
4392 (
4393 FND_LOG.level_procedure
4394 , L_LOG_MODULE || 'start'
4395 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
4396 );
4397 FND_LOG.String
4398 (
4399 FND_LOG.level_procedure
4400 , L_LOG_MODULE || 'param 1'
4401 , 'p_api_version_number:' || p_api_version_number
4402 );
4403 FND_LOG.String
4404 (
4405 FND_LOG.level_procedure
4406 , L_LOG_MODULE || 'param 2'
4407 , 'p_init_msg_list:' || p_init_msg_list
4408 );
4409 FND_LOG.String
4410 (
4411 FND_LOG.level_procedure
4412 , L_LOG_MODULE || 'param 3'
4413 , 'p_commit:' || p_commit
4414 );
4415 FND_LOG.String
4416 (
4417 FND_LOG.level_procedure
4418 , L_LOG_MODULE || 'param 4'
4419 , 'p_object_type:' || p_object_type
4420 );
4421 FND_LOG.String
4422 (
4423 FND_LOG.level_procedure
4424 , L_LOG_MODULE || 'param 5'
4425 , 'p_processing_set_id:' || p_processing_set_id
4426 );
4427 END IF ;
4428
4429 IF NOT FND_API.Compatible_API_Call
4430 (
4431 L_API_VERSION
4432 , p_api_version_number
4433 , L_API_NAME
4434 , G_PKG_NAME
4435 )
4436 THEN
4437 FND_MSG_PUB.Count_And_Get
4438 (
4439 p_count => x_msg_count
4440 , p_data => x_msg_data
4441 );
4442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4443 END IF ;
4444
4445 IF FND_API.to_Boolean(p_init_msg_list)
4446 THEN
4447 FND_MSG_PUB.initialize;
4448 END IF ;
4449
4450 ------------------------------------------------------------------------------
4451 -- Parameter Validations:
4452 ------------------------------------------------------------------------------
4453
4454 IF NVL(p_object_type, 'X') <> 'SR'
4455 THEN
4456 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4457 THEN
4458 FND_LOG.String
4459 (
4460 FND_LOG.level_unexpected
4461 , L_LOG_MODULE || 'object_type_invalid'
4462 , 'p_object_type has to be SR.'
4463 );
4464 END IF ;
4465
4466 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
4467 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4468 FND_MESSAGE.Set_Token('PARAM', 'p_object_type');
4469 FND_MESSAGE.Set_Token('CURRVAL', p_object_type);
4470 FND_MSG_PUB.ADD;
4471
4472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4473 END IF;
4474
4475 ---
4476
4477 IF p_processing_set_id IS NULL
4478 THEN
4479 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4480 THEN
4481 FND_LOG.String
4482 (
4483 FND_LOG.level_unexpected
4484 , L_LOG_MODULE || 'proc_set_id_invalid'
4485 , 'p_processing_set_id should not be NULL.'
4486 );
4487 END IF ;
4488
4489 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
4490 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4491 FND_MESSAGE.Set_Token('PARAM', 'p_processing_set_id');
4492 FND_MESSAGE.Set_Token('CURRVAL', NVL(to_char(p_processing_set_id),'NULL'));
4493 FND_MSG_PUB.ADD;
4494
4495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4496 END IF;
4497
4498 ------------------------------------------------------------------------------
4499 -- Actual Logic starts below:
4500 ------------------------------------------------------------------------------
4501
4502 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4503 THEN
4504 FND_LOG.String
4505 (
4506 FND_LOG.level_statement
4507 , L_LOG_MODULE || 'del_cost_line_start'
4508 , 'deleting cost lines against SRs in the global temp table'
4509 );
4510 END IF ;
4511
4512 -- Delete all the estimate lines that correspond to the
4513 -- SRs that are available for purge after validations.
4514
4515 DELETE /*+ index(e) */ cs_cost_details e
4516 WHERE
4517 incident_id IN
4518 (
4519 SELECT /*+ no_unnest no_semijoin cardinality(10) */
4520 object_id
4521 FROM
4522 jtf_object_purge_param_tmp
4523 WHERE
4524 processing_set_id = p_processing_set_id
4525 AND object_type = 'SR'
4526 AND NVL(purge_status, 'S') = 'S'
4527 );
4528
4529 l_row_count := SQL%ROWCOUNT;
4530
4531 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
4532 THEN
4533 FND_LOG.String
4534 (
4535 FND_LOG.level_statement
4536 , L_LOG_MODULE || 'del_chg_line_end'
4537 , 'after deleting cost lines against SRs in the global temp table'
4538 || l_row_count || ' rows deleted.'
4539 );
4540 END IF ;
4541
4542 ---
4543
4544 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
4545 THEN
4546 FND_LOG.String
4547 (
4548 FND_LOG.level_procedure
4549 , L_LOG_MODULE || 'end'
4550 , 'Completed work in ' || L_API_NAME_FULL || ' successfully'
4551 );
4552 END IF ;
4553
4554 EXCEPTION
4555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4557
4558 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4559 THEN
4560 FND_LOG.String
4561 (
4562 FND_LOG.level_unexpected
4563 , L_LOG_MODULE || 'unexpected_error'
4564 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
4565 );
4566 END IF ;
4567
4568 WHEN OTHERS THEN
4569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4570 FND_MESSAGE.Set_Name('CS', 'CS_COST_LINE_DEL_FAIL');
4571 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
4572 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
4573 FND_MSG_PUB.ADD;
4574
4575 IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
4576 THEN
4577 FND_LOG.String
4578 (
4579 FND_LOG.level_unexpected
4580 , L_LOG_MODULE || 'when_others'
4581 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
4582 );
4583 FND_LOG.String
4584 (
4585 FND_LOG.level_unexpected
4586 , L_LOG_MODULE || 'when_others'
4587 , SQLERRM
4588 );
4589 END IF ;
4590 END Purge_Cost;
4591
4592 PROCEDURE get_currency_converted_value(
4593 p_from_currency IN VARCHAR2,
4594 p_to_currency IN VARCHAR2,
4595 p_value IN NUMBER,
4596 p_ou IN VARCHAR2,
4597 x_value OUT NOCOPY NUMBER
4598 ) IS
4599
4600 p_api_name VARCHAR(100);
4601 l_rate NUMBER;
4602 l_numerator NUMBER;
4603 l_denominator NUMBER;
4604 l_return_status VARCHAR2(1);
4605 l_call_api VARCHAR2(1):='Y';
4606 l_conversion_type VARCHAR2(30) := FND_PROFILE.VALUE('CS_CHG_DEFAULT_CONVERSION_TYPE');
4607 l_max_roll_days NUMBER := to_number(FND_PROFILE.VALUE('CS_CHG_MAX_ROLL_DAYS'));
4608 l_from_currency VARCHAR2(15);
4609
4610 BEGIN
4611
4612 l_from_currency :=p_from_currency;
4613
4614
4615 if l_from_currency is null and p_ou is not null then
4616
4617 begin
4618 select currency_code
4619 into l_from_currency
4620 from gl_sets_of_books
4621 where name = p_ou;
4622 exception
4623 when no_data_found then
4624 x_value := p_value;
4625 l_call_api := 'N';
4626 end ;
4627
4628 end if;
4629
4630
4631 if l_call_api ='Y' then
4632
4633 gl_currency_api.get_closest_triangulation_rate
4634 (
4635 x_from_currency =>p_from_currency,
4636 x_to_currency => p_to_currency,
4637 x_conversion_date => SYSDATE,
4638 x_conversion_type => l_conversion_type,--l_conversion_type,
4639 x_max_roll_days => l_max_roll_days,-- l_max_roll_days,
4640 x_denominator => l_denominator,
4641 x_numerator => l_numerator,
4642 x_rate => l_rate );
4643
4644 x_value := l_rate * p_value;
4645
4646 end if;
4647
4648 EXCEPTION
4649 WHEN OTHERS THEN
4650 x_value:=p_value;
4651 END get_currency_converted_value;
4652
4653
4654 END CS_Cost_Details_PVT;