[Home] [Help]
PACKAGE BODY: APPS.CSD_WARRANTY_CONTRACT_PVT
Source
1 PACKAGE BODY CSD_WARRANTY_CONTRACT_PVT as
2 /* $Header: csdvawcb.pls 120.6 2011/08/18 23:48:53 swai noship $ */
3 -- Start of Comments
4 -- Package name : CSD_WARRANTY_CONTRACT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_WARRANTY_CONTRACT_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvawcb.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
16
17
18 -- Hint: Primary key needs to be returned.
19 PROCEDURE Create_warranty_contract(
20 P_Api_Version_Number IN NUMBER,
21 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
22 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 P_WARRANTY_Rec IN WARRANTY_Rec_Type := G_MISS_WARRANTY_REC,
25 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
26 X_WARRANTY_CONTRACT_XREF_ID OUT NOCOPY NUMBER,
27 X_Return_Status OUT NOCOPY VARCHAR2,
28 X_Msg_Count OUT NOCOPY NUMBER,
29 X_Msg_Data OUT NOCOPY VARCHAR2
30 )
31
32 IS
33 l_api_name CONSTANT VARCHAR2(30) := 'Create_warranty_contract';
34 l_api_version_number CONSTANT NUMBER := 1.0;
35
36 BEGIN
37 -- Standard Start of API savepoint
38 SAVEPOINT Create_warranty_contract;
39
40 -- Standard call to check for call compatibility.
41 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
42 p_api_version_number,
43 l_api_name,
44 G_PKG_NAME)
45 THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49
50 -- Initialize message list if p_init_msg_list is set to TRUE.
51 IF FND_API.to_Boolean( p_init_msg_list )
52 THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56
57 -- Debug Message
58 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD','Create_warranty_contract API: ' || l_api_name || 'start');
59
60
61 -- Initialize API return status to SUCCESS
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 --
65 -- API body
66 --
67 -- ******************************************************************
68 -- Validate Environment
69 -- ******************************************************************
70 IF G_USER_ID IS NULL
71 THEN
72 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
73 THEN
74 FND_MESSAGE.Set_Name('CSD', 'UT_CANNOT_GET_PROFILE_VALUE');
75 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
76 FND_MSG_PUB.ADD;
77 END IF;
78 RAISE FND_API.G_EXC_ERROR;
79 END IF;
80
81 -- Debug Message
82 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD',
83 'Private API: Calling create table handler');
84
85 -- Invoke table handler(CSD_AHL_W_CONTRACT_XREF_PKG.Insert_Row)
86 CSD_AHL_W_CONTRACT_XREF_PKG.Insert_Row(
87 px_WARRANTY_CONTRACT_XREF_ID => x_WARRANTY_CONTRACT_XREF_ID
88 ,p_OBJECT_VERSION_NUMBER => 1
89 ,p_CREATED_BY => G_USER_ID
90 ,p_CREATION_DATE => SYSDATE
91 ,p_LAST_UPDATED_BY => G_USER_ID
92 ,p_LAST_UPDATE_DATE => SYSDATE
93 ,p_LAST_UPDATE_LOGIN => G_LOGIN_ID
94 ,p_SOURCE_TABLE => p_WARRANTY_rec.SOURCE_TABLE
95 ,p_SOURCE_COLUMN => p_WARRANTY_rec.SOURCE_COLUMN
96 ,p_SOURCE_ID => p_WARRANTY_rec.SOURCE_ID
97 ,p_WARRANTY_CONTRACT_ID => p_WARRANTY_rec.WARRANTY_CONTRACT_ID
98 ,p_ATTRIBUTE_CATEGORY => p_WARRANTY_rec.ATTRIBUTE_CATEGORY
99 ,p_ATTRIBUTE1 => p_WARRANTY_rec.ATTRIBUTE1
100 ,p_ATTRIBUTE2 => p_WARRANTY_rec.ATTRIBUTE2
101 ,p_ATTRIBUTE3 => p_WARRANTY_rec.ATTRIBUTE3
102 ,p_ATTRIBUTE4 => p_WARRANTY_rec.ATTRIBUTE4
103 ,p_ATTRIBUTE5 => p_WARRANTY_rec.ATTRIBUTE5
104 ,p_ATTRIBUTE6 => p_WARRANTY_rec.ATTRIBUTE6
105 ,p_ATTRIBUTE7 => p_WARRANTY_rec.ATTRIBUTE7
106 ,p_ATTRIBUTE8 => p_WARRANTY_rec.ATTRIBUTE8
107 ,p_ATTRIBUTE9 => p_WARRANTY_rec.ATTRIBUTE9
108 ,p_ATTRIBUTE10 => p_WARRANTY_rec.ATTRIBUTE10
109 ,p_ATTRIBUTE11 => p_WARRANTY_rec.ATTRIBUTE11
110 ,p_ATTRIBUTE12 => p_WARRANTY_rec.ATTRIBUTE12
111 ,p_ATTRIBUTE13 => p_WARRANTY_rec.ATTRIBUTE13
112 ,p_ATTRIBUTE14 => p_WARRANTY_rec.ATTRIBUTE14
113 ,p_ATTRIBUTE15 => p_WARRANTY_rec.ATTRIBUTE15
114 ,p_REPAIR_LINE_ID => p_WARRANTY_rec.REPAIR_LINE_ID
115 ,p_INSTANCE_ID => p_WARRANTY_rec.INSTANCE_ID); --yvchen
116 -- Hint: Primary key should be returned.
117 -- x_WARRANTY_CONTRACT_XREF_ID := px_WARRANTY_CONTRACT_XREF_ID;
118
119 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122
123 --
124 -- End of API body
125 --
126
127 -- Standard check for p_commit
128 IF FND_API.to_Boolean( p_commit )
129 THEN
130 COMMIT WORK;
131 END IF;
132
133
134 -- Debug Message
135 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Create_warranty_contract API: ' || l_api_name || 'end');
136
137
138 -- Standard call to get message count and if count is 1, get message info.
139 FND_MSG_PUB.Count_And_Get
140 ( p_count => x_msg_count,
141 p_data => x_msg_data
142 );
143
144 EXCEPTION
145 WHEN FND_API.G_EXC_ERROR THEN
146 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
147 P_API_NAME => L_API_NAME
148 ,P_PKG_NAME => G_PKG_NAME
149 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
150 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
151 ,X_MSG_COUNT => X_MSG_COUNT
152 ,X_MSG_DATA => X_MSG_DATA
153 ,X_RETURN_STATUS => X_RETURN_STATUS);
154
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
157 P_API_NAME => L_API_NAME
158 ,P_PKG_NAME => G_PKG_NAME
159 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
160 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
161 ,X_MSG_COUNT => X_MSG_COUNT
162 ,X_MSG_DATA => X_MSG_DATA
163 ,X_RETURN_STATUS => X_RETURN_STATUS);
164
165 WHEN OTHERS THEN
166 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
167 P_API_NAME => L_API_NAME
168 ,P_PKG_NAME => G_PKG_NAME
169 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
170 ,P_SQLCODE => SQLCODE
171 ,P_SQLERRM => SQLERRM
172 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
173 ,X_MSG_COUNT => X_MSG_COUNT
174 ,X_MSG_DATA => X_MSG_DATA
175 ,X_RETURN_STATUS => X_RETURN_STATUS);
176 End Create_warranty_contract;
177
178
179 PROCEDURE Default_Warranty_Contract (
180 P_Api_Version_Number IN NUMBER,
181 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
182 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
183 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
184 P_Repair_Line_Id IN NUMBER,
185 P_Wip_Entity_Id IN NUMBER,
186 P_Instance_Id IN NUMBER DEFAULT NULL, --yvchen
187 P_Is_Parent IN VARCHAR2 := FND_API.G_TRUE, --yvchen
188 X_Return_Status OUT NOCOPY VARCHAR2,
189 X_Msg_Count OUT NOCOPY NUMBER,
190 X_Msg_Data OUT NOCOPY VARCHAR2
191 )
192
193 IS
194 l_api_name CONSTANT VARCHAR2(30) := 'Default_Warranty_Contract';
195 l_api_version_number CONSTANT NUMBER := 1.0;
196 l_instance_id NUMBER;
197 l_number_contract NUMBER;
198 l_warranty_contract_id NUMBER;
199 l_WARRANTY_Rec CSD_WARRANTY_CONTRACT_PVT.WARRANTY_Rec_Type;
200 l_WARRANTY_CONTRACT_XREF_ID NUMBER;
201
202 --yvchen: for finding components of parent instance
203 i NUMBER;
204 l_relationship_rec csi_datastructures_pub.relationship_query_rec;
205 l_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
206
207 CURSOR C1_get_instance_id(p_wip_entity_id number, p_Repair_Line_Id number)
208 IS
209 SELECT repairs.CUSTOMER_PRODUCT_ID
210 FROM CSD_REPAIRS REPAIRS, wip_entities wip
211 WHERE repairs.repair_line_id = p_Repair_Line_Id
212 and wip.wip_entity_id = p_wip_entity_id
213 and repairs.inventory_item_id = wip.primary_item_id;
214
215 CURSOR C2_get_contract_count(p_instance_id number)
216 IS
217 SELECT count(contract.warranty_contract_id)
218 FROM AHL_WARRANTY_CONTRACTS_B contract
219 WHERE contract.contract_status_code = 'ACTIVE'
220 AND TRUNC(NVL(contract.expiration_date, SYSDATE + 1)) > TRUNC(sysdate)
221 AND contract.item_instance_id = p_instance_id;
222
223 CURSOR C3_get_w_contract_id(p_instance_id number)
224 IS
225 SELECT contract.warranty_contract_id
226 FROM AHL_WARRANTY_CONTRACTS_B contract
227 WHERE contract.contract_status_code = 'ACTIVE'
228 AND TRUNC(NVL(contract.expiration_date, SYSDATE + 1)) > TRUNC(sysdate)
229 AND contract.item_instance_id = p_instance_id;
230
231
232 BEGIN
233 -- Standard Start of API savepoint
234 SAVEPOINT Default_Warranty_Contract;
235
236 -- Standard call to check for call compatibility.
237 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
238 p_api_version_number,
239 l_api_name,
240 G_PKG_NAME)
241 THEN
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243 END IF;
244
245
246 -- Initialize message list if p_init_msg_list is set to TRUE.
247 IF FND_API.to_Boolean( p_init_msg_list )
248 THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 --
256 -- API body
257 --
258 -- ******************************************************************
259 -- Validate Environment
260 -- ******************************************************************
261 IF G_USER_ID IS NULL
262 THEN
263 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
264 THEN
265 FND_MESSAGE.Set_Name('CSD', 'UT_CANNOT_GET_PROFILE_VALUE');
266 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
267 FND_MSG_PUB.ADD;
268 END IF;
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272
273 -- Debug Message
274 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD','Default_Warranty_Contract API: ' || l_api_name || 'start');
275
276
277 IF (p_instance_id IS NULL) THEN --yvchen: if parent, find instance id
278 -- Get instance id
279 OPEN C1_get_instance_id (P_Wip_Entity_Id, P_Repair_Line_Id);
280 FETCH C1_get_instance_id INTO l_instance_id;
281 CLOSE C1_get_instance_id;
282 ELSE
283 l_instance_id := p_instance_id; --yvchen: if child, instance id given
284 END IF;
285
286 IF (l_instance_id is not null) THEN
287
288 OPEN C2_get_contract_count (l_instance_id);
289 FETCH C2_get_contract_count INTO l_number_contract;
290 CLOSE C2_get_contract_count;
291
292 IF (l_number_contract >= 1) THEN --yvchen
293
294 OPEN C3_get_w_contract_id (l_instance_id);
295 FETCH C3_get_w_contract_id INTO l_warranty_contract_id;
296 CLOSE C3_get_w_contract_id;
297
298 l_WARRANTY_Rec.SOURCE_TABLE := 'WIP_ENTITIES';
299 l_WARRANTY_Rec.SOURCE_COLUMN := 'WIP_ENTITY_ID';
300 l_WARRANTY_Rec.SOURCE_ID := P_Wip_Entity_Id;
301
302 --yvchen: if only 1 contract, set it as default; if >1 contracts, do not set default
303 IF (l_number_contract = 1) THEN
304 l_WARRANTY_Rec.WARRANTY_CONTRACT_ID := l_warranty_contract_id;
305 ELSE
306 l_WARRANTY_Rec.WARRANTY_CONTRACT_ID := NULL;
307 END IF;
308
309 l_WARRANTY_Rec.REPAIR_LINE_ID := P_Repair_Line_Id;
310 l_WARRANTY_Rec.INSTANCE_ID := l_instance_id; --yvchen
311
312 Create_warranty_contract(
313 P_Api_Version_Number => 1.0,
314 P_Init_Msg_List => FND_API.G_FALSE,
315 p_commit => FND_API.G_TRUE,
316 X_Return_Status => X_Return_Status,
317 X_Msg_Count => X_Msg_Count,
318 X_Msg_Data => X_Msg_Data,
319 p_validation_level => fnd_api.g_valid_level_none,
320 P_WARRANTY_Rec => l_WARRANTY_Rec,
321 X_WARRANTY_CONTRACT_XREF_ID => l_WARRANTY_CONTRACT_XREF_ID
322 );
323 IF NOT (X_Return_Status = Fnd_Api.G_RET_STS_SUCCESS)
324 THEN
325 RAISE Fnd_Api.G_EXC_ERROR;
326 END IF;
327
328 END IF;
329
330 --yvchen: if parent, find components at all levels
331 IF( p_is_parent = FND_API.G_TRUE ) THEN
332
333 l_relationship_rec.relationship_type_code := 'COMPONENT-OF';
334 l_relationship_rec.object_id := l_instance_id; --parent instance id
335
336 --this method returns components at all levels
337 csi_ii_relationships_pub.get_relationships (
338 p_api_version => 1.0,
339 p_commit => fnd_api.g_false,
340 p_init_msg_list => fnd_api.g_false,
341 p_validation_level => 1.0,
342 p_relationship_query_rec => l_relationship_rec,
343 p_depth => fnd_api.g_miss_num,
344 p_time_stamp => fnd_api.g_miss_date,
345 p_active_relationship_only => fnd_api.g_true,
346 x_relationship_tbl => l_relationship_tbl,
347 x_return_status => x_return_status,
348 x_msg_count => x_msg_count,
349 x_msg_data => x_msg_data
350 );
351 IF NOT (X_Return_Status = Fnd_Api.G_RET_STS_SUCCESS)
352 THEN
353 RAISE Fnd_Api.G_EXC_ERROR;
354 END IF;
355
356 --yvchen: find warranty for each component
357 FOR i IN 1..l_relationship_tbl.count LOOP
358 Default_Warranty_Contract(
359 P_Api_Version_Number => 1.0,
360 P_Init_Msg_List => FND_API.G_FALSE,
361 p_commit => FND_API.G_TRUE,
362 p_validation_level => fnd_api.g_valid_level_none,
363 P_Repair_Line_Id => p_repair_line_id,
364 P_Wip_Entity_Id => p_wip_entity_id,
365 P_Instance_Id => l_relationship_tbl(i).SUBJECT_ID, --component instance id
366 P_Is_Parent => FND_API.G_FALSE, --not parent
367 X_Return_Status => x_return_status,
368 X_Msg_Count => x_msg_count,
369 X_Msg_Data => x_msg_data
370 );
371 IF NOT (X_Return_Status = Fnd_Api.G_RET_STS_SUCCESS)
372 THEN
373 RAISE Fnd_Api.G_EXC_ERROR;
374 END IF;
375 END LOOP;
376
377 END IF;
378
379 END IF;
380
381 --
382 -- End of API body
383 --
384
385 -- Standard check for p_commit
386 IF FND_API.to_Boolean( p_commit )
387 THEN
388 COMMIT WORK;
389 END IF;
390
391
392 -- Debug Message
393 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Default_Warranty_Contract API: ' || l_api_name || 'end');
394
395
396 -- Standard call to get message count and if count is 1, get message info.
397 FND_MSG_PUB.Count_And_Get
398 ( p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401
402 EXCEPTION
403 WHEN FND_API.G_EXC_ERROR THEN
404 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
405 P_API_NAME => L_API_NAME
406 ,P_PKG_NAME => G_PKG_NAME
407 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
408 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
409 ,X_MSG_COUNT => X_MSG_COUNT
410 ,X_MSG_DATA => X_MSG_DATA
411 ,X_RETURN_STATUS => X_RETURN_STATUS);
412
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
415 P_API_NAME => L_API_NAME
416 ,P_PKG_NAME => G_PKG_NAME
417 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
418 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
419 ,X_MSG_COUNT => X_MSG_COUNT
420 ,X_MSG_DATA => X_MSG_DATA
421 ,X_RETURN_STATUS => X_RETURN_STATUS);
422
423 WHEN OTHERS THEN
424 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
425 P_API_NAME => L_API_NAME
426 ,P_PKG_NAME => G_PKG_NAME
427 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
428 ,P_SQLCODE => SQLCODE
429 ,P_SQLERRM => SQLERRM
430 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
431 ,X_MSG_COUNT => X_MSG_COUNT
432 ,X_MSG_DATA => X_MSG_DATA
433 ,X_RETURN_STATUS => X_RETURN_STATUS);
434 End Default_Warranty_Contract;
435
436 /*----------------------------------------------------------------*/
437 /* function name: Get_Default_Warranty_Claim_Amt */
438 /* description : function used to get the default claim */
439 /* amount for a claim line. logic is as */
440 /* follows: */
441 /* (1) If the inventory item being claimed is the */
442 /* same as the item on the job, then return the */
443 /* total material and resource cost for all */
444 /* items/resources transacted to the job */
445 /* (excluding for the cost of the job item) */
446 /* (2) If the inventory item being claimed */
447 /* is NOT the repair job item, and the item */
448 /* has been transacted to the job, then get */
449 /* the item cost only. If the item has not been */
450 /* transacted, then return null. */
451 /* NOTE: Default claim amount is based on */
452 /* item and resource transactions and costs */
453 /* It is NOT based on a specific warraty */
454 /* contract, hance warranty contrct is not passed */
455 /* in. */
456 /* Called from : OA framework Supplier Warranty Claims */
457 /* Input Parm : */
458 /* p_wip_entity_id NUMBER WIP Job ID */
459 /* p_wip_primary_item_id NUMBER Inv Item Id of the WIP job */
460 /* p_inventory_item_id NUMBER Inv Item Id for claim line */
461 /* */
462 /* Output: */
463 /* NUMBER Cost to default for the warranty claim line */
464 /* Returns null if no cost or cost=0 */
465 /* Change Hist : */
466 /*----------------------------------------------------------------*/
467 FUNCTION Get_Default_Warranty_Claim_Amt (
468 p_wip_entity_id IN NUMBER,
469 p_wip_primary_item_id IN NUMBER,
470 p_inventory_item_id IN NUMBER) RETURN NUMBER
471 IS
472 CURSOR c_total_job_cost IS
473 SELECT (nvl(mat.item_cost, 0)+ nvl(res.resource_cost, 0)) total_cost
474 FROM
475 ( SELECT SUM( abs(mmt.primary_quantity)*CIC.item_cost) item_cost
476 FROM MTL_MATERIAL_TRANSACTIONS MMT,
477 WIP_ENTITIES WIP ,
478 CST_ITEM_COSTS CIC
479 WHERE MMT.transaction_source_id = WIP.wip_entity_id
480 AND MMT.transaction_source_type_id = 5
481 AND MMT.transaction_type_id = 35
482 AND MMT.primary_quantity < 0
483 AND MMT.inventory_item_id <> nvl(WIP.primary_item_id, -999)
484 AND WIP.wip_entity_id = p_wip_entity_id
485 AND CIC.inventory_item_id = MMT.inventory_item_id
486 AND CIC.organization_id = WIP.organization_id
487 AND CIC.cost_type_id = 1
488 ) mat,
489 (SELECT SUM( NVL( WTXN.primary_quantity, 0 )*CRC.resource_rate) resource_cost
490 FROM WIP_TRANSACTIONS WTXN,
491 CST_RESOURCE_COSTS CRC,
492 WIP_ENTITIES WIP
493 WHERE WTXN.wip_entity_id = p_wip_entity_id
494 AND WTXN.wip_entity_id = wip.wip_entity_id
495 AND WTXN.transaction_type IN( 1, 2, 3 )
496 AND CRC.resource_id = WTXN.resource_id
497 AND CRC.organization_id = wip.organization_id
498 AND CRC.cost_type_id = 1 ) res;
499
500 CURSOR c_item_cost IS
501 SELECT CIC.item_cost
502 FROM MTL_MATERIAL_TRANSACTIONS MMT,
503 WIP_ENTITIES WIP ,
504 CST_ITEM_COSTS CIC
505 WHERE MMT.transaction_source_id = WIP.wip_entity_id
506 AND MMT.transaction_source_type_id = 5
507 AND MMT.transaction_type_id = 35
508 AND MMT.primary_quantity < 0
509 AND MMT.inventory_item_id <> nvl(WIP.primary_item_id, -999)
510 AND MMT.inventory_item_id = p_inventory_item_id
511 AND WIP.wip_entity_id = p_wip_entity_id
512 AND CIC.inventory_item_id = MMT.inventory_item_id
513 AND CIC.organization_id = WIP.organization_id
514 AND CIC.cost_type_id = 1
515 AND rownum = 1;
516
517 l_cost NUMBER;
518
519 BEGIN
520 if (p_wip_entity_id is null) or (p_inventory_item_id is null) then
521 l_cost := null;
522 else
523 if (p_wip_primary_item_id = p_inventory_item_id) then
524 open c_total_job_cost;
525 fetch c_total_job_cost into l_cost;
526 close c_total_job_cost;
527 else
528 open c_item_cost;
529 fetch c_item_cost into l_cost;
530 close c_item_cost;
531 end if;
532 end if;
533
534 if (l_cost = 0) then
535 l_cost := null;
536 end if;
537
538 return l_cost;
539
540 END Get_Default_Warranty_Claim_Amt;
541
542 End CSD_WARRANTY_CONTRACT_PVT;