[Home] [Help]
PACKAGE BODY: APPS.CSP_REQUIREMENT_HEADERS_PUB
Source
1 PACKAGE BODY CSP_REQUIREMENT_HEADERS_PUB AS
2 /* $Header: cspprqhb.pls 120.1 2011/04/22 00:11:39 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_Requirement_Headers_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Requirement_Headers_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspprqhb.pls';
13
14 -- Start of Comments
18 -- This procedure takes a public Requirement_Headers record as input. It may contain
15 -- ***************** Private Conversion Routines Values -> Ids **************
16 -- Purpose
17 --
19 -- values or ids. All values are then converted into ids and a
20 -- private Requirement_Headers record is returned for the private
21 -- API call.
22 --
23 -- Conversions:
24 --
25 -- Notes
26 --
27 -- 1. IDs take precedence over values. If both are present for a field, ID is used,
28 -- the value based parameter is ignored and a warning message is created.
29 -- 2. This is automatically generated procedure, it converts public record type to
30 -- private record type for all attributes.
31 -- Developer must manually add conversion logic to the attributes.
32 --
33 -- End of Comments
34 PROCEDURE Convert_RQH_Values_To_Ids(
35 P_RQH_Rec IN CSP_requirement_headers_PUB.RQH_Rec_Type,
36 x_pvt_RQH_rec OUT NOCOPY CSP_requirement_headers_PVT.Requirement_Header_Rec_Type
37 )
38 IS
39 -- Hint: Declare cursor and local variables
40 CURSOR C_Get_Task_Id(X_Task_Number VARCHAR2) IS
41 SELECT task_id
42 FROM jtf_Tasks_b
43 WHERE task_number = x_Task_Number;
44 l_any_errors BOOLEAN := FALSE;
45 l_task_id Number;
46 EXCP_USER_DEFINED EXCEPTION;
47 BEGIN
48
49 If(p_rqh_rec.task_id is NOT NULL and p_rqh_rec.task_id <> FND_API.G_MISS_NUM)
50 THEN
51 x_pvt_rqh_rec.task_id := p_rqh_rec.task_id;
52 ELSIF(p_rqh_rec.task_number is NOT NULL and p_rqh_rec.task_number <> FND_API.G_MISS_CHAR)
53 THEN
54 OPEN C_Get_Task_Id(P_RQH_Rec.task_number);
55 FETCH C_Get_Task_Id INTO l_task_id;
56
57 IF C_Get_Task_Id%NOTFOUND THEN
58 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TASK_INVALID_TASK_NUMBER');
59 FND_MESSAGE.SET_TOKEN ('P_TASK_NUMBER', P_RQH_Rec.task_number, FALSE);
60 FND_MSG_PUB.ADD;
61 RAISE EXCP_USER_DEFINED;
62 END IF;
63 CLOSE C_Get_Task_Id;
64 x_pvt_rqh_rec.task_id := l_task_id;
65 ELSE
66 x_pvt_rqh_rec.task_id := nvl(p_RQH_Rec.task_id, NULL);
67 END IF;
68
69
70 -- Now copy the rest of the columns to the private record
71 -- Hint: We provide copy all columns to the private record.
72 -- Developer should delete those fields which are used by Value-Id conversion above
73 -- Hint: Developer should remove some of the following statements because of inconsistent column name between table and view.
74
75 x_pvt_RQH_rec.REQUIREMENT_HEADER_ID := P_RQH_Rec.REQUIREMENT_HEADER_ID;
76 x_pvt_RQH_rec.CREATED_BY := P_RQH_Rec.CREATED_BY;
77 x_pvt_RQH_rec.CREATION_DATE := P_RQH_Rec.CREATION_DATE;
78 x_pvt_RQH_rec.LAST_UPDATED_BY := P_RQH_Rec.LAST_UPDATED_BY;
79 x_pvt_RQH_rec.LAST_UPDATE_DATE := P_RQH_Rec.LAST_UPDATE_DATE;
80 x_pvt_RQH_rec.LAST_UPDATE_LOGIN := P_RQH_Rec.LAST_UPDATE_LOGIN;
81 x_pvt_RQH_rec.OPEN_REQUIREMENT := P_RQH_Rec.OPEN_REQUIREMENT;
82 x_pvt_RQH_rec.ADDRESS_TYPE := P_RQH_Rec.ADDRESS_TYPE;
83 x_pvt_RQH_rec.SHIP_TO_LOCATION_ID := P_RQH_Rec.SHIP_TO_LOCATION_ID;
84 x_pvt_RQH_rec.TIMEZONE_ID := P_RQH_Rec.TIMEZONE_ID;
85 -- x_pvt_RQH_rec.TASK_ID := P_RQH_Rec.TASK_ID;
86 x_pvt_RQH_rec.TASK_ASSIGNMENT_ID := P_RQH_Rec.TASK_ASSIGNMENT_ID;
87 x_pvt_RQH_rec.RESOURCE_TYPE := P_RQH_Rec.RESOURCE_TYPE;
88 x_pvt_RQH_rec.RESOURCE_ID := P_RQH_Rec.RESOURCE_ID;
89 x_pvt_RQH_rec.SHIPPING_METHOD_CODE := P_RQH_Rec.SHIPPING_METHOD_CODE;
90 x_pvt_RQH_rec.NEED_BY_DATE := P_RQH_Rec.NEED_BY_DATE;
91 x_pvt_RQH_rec.DESTINATION_ORGANIZATION_ID := P_RQH_Rec.DESTINATION_ORGANIZATION_ID;
92 x_pvt_RQH_rec.ORDER_TYPE_ID := P_RQH_Rec.ORDER_TYPE_ID;
93 x_pvt_RQH_rec.PARTS_DEFINED := P_RQH_Rec.PARTS_DEFINED;
94 x_pvt_RQH_rec.ATTRIBUTE_CATEGORY := P_RQH_Rec.ATTRIBUTE_CATEGORY;
95 x_pvt_RQH_rec.ATTRIBUTE1 := P_RQH_Rec.ATTRIBUTE1;
96 x_pvt_RQH_rec.ATTRIBUTE2 := P_RQH_Rec.ATTRIBUTE2;
97 x_pvt_RQH_rec.ATTRIBUTE3 := P_RQH_Rec.ATTRIBUTE3;
98 x_pvt_RQH_rec.ATTRIBUTE4 := P_RQH_Rec.ATTRIBUTE4;
99 x_pvt_RQH_rec.ATTRIBUTE5 := P_RQH_Rec.ATTRIBUTE5;
100 x_pvt_RQH_rec.ATTRIBUTE6 := P_RQH_Rec.ATTRIBUTE6;
101 x_pvt_RQH_rec.ATTRIBUTE7 := P_RQH_Rec.ATTRIBUTE7;
102 x_pvt_RQH_rec.ATTRIBUTE8 := P_RQH_Rec.ATTRIBUTE8;
103 x_pvt_RQH_rec.ATTRIBUTE9 := P_RQH_Rec.ATTRIBUTE9;
104 x_pvt_RQH_rec.ATTRIBUTE10 := P_RQH_Rec.ATTRIBUTE10;
105 x_pvt_RQH_rec.ATTRIBUTE11 := P_RQH_Rec.ATTRIBUTE11;
106 x_pvt_RQH_rec.ATTRIBUTE12 := P_RQH_Rec.ATTRIBUTE12;
107 x_pvt_RQH_rec.ATTRIBUTE13 := P_RQH_Rec.ATTRIBUTE13;
108 x_pvt_RQH_rec.ATTRIBUTE14 := P_RQH_Rec.ATTRIBUTE14;
109 x_pvt_RQH_rec.ATTRIBUTE15 := P_RQH_Rec.ATTRIBUTE15;
110 x_pvt_RQH_rec.DESTINATION_SUBINVENTORY := P_RQH_Rec.DESTINATION_SUBINVENTORY;
111
112 -- If there is an error in conversion precessing, raise an error.
113 IF l_any_errors
114 THEN
115 raise FND_API.G_EXC_ERROR;
116 END IF;
117
118 END Convert_RQH_Values_To_Ids;
119
120 PROCEDURE Validate_Requirement_Header(l_pvt_RQH_rec CSP_REQUIREMENT_HEADERS_PVT.Requirement_Header_Rec_Type) IS
121 l_count NUMBER;
122 EXCP_USER_DEFINED EXCEPTION;
123 BEGIN
124 IF (l_pvt_RQH_rec.address_type IS NOT NULL
125 AND l_pvt_RQH_rec.address_type <> FND_API.G_MISS_CHAR
126 AND l_pvt_RQH_rec.address_type NOT IN ('R', 'T', 'C', 'S')) THEN
127 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_ADDRESS_TYPE');
128 FND_MSG_PUB.ADD;
129 RAISE EXCP_USER_DEFINED;
130 END IF;
131 IF (l_pvt_RQH_Rec.ship_to_location_id IS NOT NULL
132 AND l_pvt_RQH_rec.ship_to_location_id <> FND_API.G_MISS_NUM) THEN
133 BEGIN
134 SELECT count(location_id)
138 IF (l_count <= 0) THEN
135 INTO l_count
136 FROM hr_locations
137 WHERE location_id = l_pvt_RQH_Rec.ship_to_location_id;
139 FND_MESSAGE.SET_NAME ('PAY', 'HR_52034_DPF_LOCATION_EXIST');
140 FND_MSG_PUB.ADD;
141 RAISE EXCP_USER_DEFINED;
142 END IF;
143 EXCEPTION
144 when no_Data_found then
145 null;
146 END;
147 END IF;
148 IF (l_pvt_RQH_Rec.task_id IS NOT NULL
149 AND l_pvt_RQH_rec.task_id <> FND_API.G_MISS_NUM) THEN
150 BEGIN
151 SELECT count(task_id)
152 INTO l_count
153 FROM jtf_Tasks_b
154 WHERE task_id = l_pvt_RQH_Rec.task_id;
155 IF (l_count <= 0) THEN
156 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TASK_INVALID_TASK_ID');
157 FND_MESSAGE.SET_TOKEN ('P_TASK_ID', l_pvt_RQH_rec.task_id, FALSE);
158 FND_MSG_PUB.ADD;
159 RAISE EXCP_USER_DEFINED;
160 END IF;
161 EXCEPTION
162 when no_Data_found then
163 null;
164 END;
165 END IF;
166 IF (l_pvt_RQH_Rec.task_assignment_id IS NOT NULL
167 AND l_pvt_RQH_rec.task_assignment_id <> FND_API.G_MISS_NUM) THEN
168 BEGIN
169 SELECT count(task_assignment_id)
170 INTO l_count
171 FROM jtf_Task_assignments
172 WHERE task_assignment_id = l_pvt_RQH_Rec.task_assignment_id;
173 IF (l_count <= 0) THEN
174 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TASK_INV_TK_ASS');
175 FND_MESSAGE.SET_TOKEN ('P_TASK_ASSIGNMENT_ID', l_pvt_RQH_rec.task_assignment_id, FALSE);
176 FND_MSG_PUB.ADD;
177 RAISE EXCP_USER_DEFINED;
178 END IF;
179 EXCEPTION
180 when no_Data_found then
181 null;
182 END;
183 END IF;
184 IF (l_pvt_RQH_Rec.resource_type IS NOT NULL
185 AND l_pvt_RQH_rec.resource_type <> FND_API.G_MISS_CHAR) THEN
186 BEGIN
187 SELECT count(jov.object_code)
188 INTO l_count
189 FROM jtf_objects_vl jov,
190 jtf_object_usages jou
191 WHERE trunc(sysdate) between trunc(nvl(jov.start_date_active,sysdate))
192 and trunc(nvl(jov.end_date_active,sysdate))
193 AND jou.object_code = jov.object_code
194 AND jou.object_user_code = 'RESOURCES'
195 AND jov.object_code = l_pvt_RQH_rec.resource_type;
196 IF (l_count <= 0) THEN
197 FND_MESSAGE.SET_NAME ('JTF', 'JTF_AM_INVALID_RESOURCE_TYPE');
198 FND_MSG_PUB.ADD;
199 RAISE EXCP_USER_DEFINED;
200 END IF;
201 EXCEPTION
202 when no_Data_found then
203 null;
204 END;
205 END IF;
206 IF (l_pvt_RQH_Rec.resource_id IS NOT NULL
207 AND l_pvt_RQH_rec.resource_id <> FND_API.G_MISS_NUM) THEN
208 BEGIN
209 SELECT count(resource_id)
210 INTO l_count
211 FROM jtf_rs_resource_extns
212 WHERE resource_id = l_pvt_RQH_rec.resource_id
213 AND ( end_date_active is null OR
214 trunc(end_date_active) >= trunc(sysdate));
215 IF (l_count <= 0) THEN
216 FND_MESSAGE.SET_NAME ('JTF', 'JTF_TASK_INV_RES_ID');
217 FND_MESSAGE.SET_TOKEN ('P_RESOURCE_ID', l_pvt_RQH_rec.resource_id, FALSE);
218 FND_MSG_PUB.ADD;
219 RAISE EXCP_USER_DEFINED;
220 END IF;
221 EXCEPTION
222 when no_Data_found then
223 null;
224 END;
225 END IF;
226 IF (l_pvt_RQH_Rec.destination_organization_id IS NOT NULL
227 AND l_pvt_RQH_rec.destination_organization_id <> FND_API.G_MISS_NUM) THEN
228 BEGIN
229 SELECT count(organization_id)
230 INTO l_count
231 FROM mtl_parameters
232 WHERE organization_id = l_pvt_RQH_rec.destination_organization_id;
233 IF (l_count <= 0) THEN
234 FND_MESSAGE.SET_NAME ('INV', 'INV_ENTER_VALID_TOORG');
235 --FND_MESSAGE.SET_TOKEN ('PARAMETER', 'DESTINATION_ORGANIZATION', FALSE);
236 FND_MSG_PUB.ADD;
237 RAISE EXCP_USER_DEFINED;
238 END IF;
239 EXCEPTION
240 when no_Data_found then
241 null;
242 END;
243 END IF;
244 IF (l_pvt_RQH_Rec.destination_subinventory IS NOT NULL
245 AND l_pvt_RQH_rec.destination_subinventory <> FND_API.G_MISS_CHAR) THEN
246 BEGIN
247 SELECT count(secondary_inventory_name)
248 INTO l_count
249 FROM mtl_secondary_inventories
250 WHERE organization_id = nvl(l_pvt_RQH_rec.destination_organization_id, organization_id)
251 AND secondary_inventory_name = l_pvt_RQH_rec.destination_subinventory;
252 IF (l_count <= 0) THEN
253 FND_MESSAGE.SET_NAME ('INV', 'INV-NO SUBINVENTORY RECORD');
254 FND_MESSAGE.SET_TOKEN ('SUBINV', l_pvt_RQH_rec.destination_subinventory, FALSE);
255 FND_MESSAGE.SET_TOKEN ('ORG', l_pvt_RQH_rec.destination_organization_id, FALSE);
256 FND_MSG_PUB.ADD;
257 RAISE EXCP_USER_DEFINED;
258 END IF;
259 EXCEPTION
260 when no_Data_found then
261 null;
262 END;
263 END IF;
264 IF (l_pvt_RQH_Rec.need_by_date IS NOT NULL
265 AND l_pvt_RQH_rec.need_by_date <> FND_API.G_MISS_DATE
266 AND trunc(l_pvt_RQH_rec.need_By_date) < trunc(sysdate)) THEN
267 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_NEED_BY_DATE');
268 FND_MSG_PUB.ADD;
269 RAISE EXCP_USER_DEFINED;
270 END IF;
271 END;
272
273 PROCEDURE Create_requirement_headers(
274 P_Api_Version_Number IN NUMBER,
275 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
276 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
277 P_RQH_Rec IN RQH_Rec_Type := G_MISS_RQH_REC,
278 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
282 X_Msg_Data OUT NOCOPY VARCHAR2
279 X_REQUIREMENT_HEADER_ID OUT NOCOPY NUMBER,
280 X_Return_Status OUT NOCOPY VARCHAR2,
281 X_Msg_Count OUT NOCOPY NUMBER,
283 )
284
285 IS
286 l_api_name CONSTANT VARCHAR2(30) := 'Create_requirement_headers';
287 l_api_version_number CONSTANT NUMBER := 1.0;
288 l_pvt_RQH_rec CSP_Requirement_Headers_PVT.Requirement_Header_Rec_Type;
289 l_requirement_header_id NUMBER;
290 BEGIN
291 -- Standard Start of API savepoint
292 SAVEPOINT CREATE_Requirement_Headers_PUB;
293
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
296 p_api_version_number,
297 l_api_name,
298 G_PKG_NAME)
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303
304 -- Initialize message list if p_init_msg_list is set to TRUE.
305 IF FND_API.to_Boolean( p_init_msg_list )
306 THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309
310
311 -- Debug Message
312 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
313
314
315 -- Initialize API return status to SUCCESS
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 --
319 -- API body
320 --
321
322 -- Debug Message
323 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_RQH_Values_To_Ids');
324
325 -- Convert the values to ids
326 --
327 Convert_RQH_Values_To_Ids (
328 p_RQH_rec => p_RQH_rec,
329 x_pvt_RQH_rec => l_pvt_RQH_rec
330 );
331
332 Validate_Requirement_Header(l_pvt_RQH_rec);
333
334 -- Calling Private package: Create_Packlist_Headers
335 -- Hint: Primary key needs to be returned
336 CSP_requirement_headers_PVT.Create_requirement_headers(
337 P_Api_Version_Number => 1.0,
338 P_Init_Msg_List => FND_API.G_FALSE,
339 P_Commit => FND_API.G_FALSE,
340 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
341 P_REQUIREMENT_HEADER_Rec => l_pvt_RQH_Rec ,
342 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
343 X_REQUIREMENT_HEADER_ID => l_REQUIREMENT_HEADER_ID,
344 X_Return_Status => x_return_status,
345 X_Msg_Count => x_msg_count,
346 X_Msg_Data => x_msg_data);
347
348 -- Check return status from the above procedure call
349 IF x_return_status = FND_API.G_RET_STS_ERROR then
350 raise FND_API.G_EXC_ERROR;
351 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
352 raise FND_API.G_EXC_UNEXPECTED_ERROR;
353 END IF;
354
355 x_requirement_header_id := l_requirement_header_id;
356 --
357 -- End of API body.
358 --
359 -- Standard check for p_commit
360 IF FND_API.to_Boolean( p_commit )
361 THEN
362 COMMIT WORK;
363 END IF;
364
365
366 -- Debug Message
367 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
368
369
370 -- Standard call to get message count and if count is 1, get message info.
371 FND_MSG_PUB.Count_And_Get
372 ( p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375
376 EXCEPTION
377 WHEN FND_API.G_EXC_ERROR THEN
378 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
379 P_API_NAME => L_API_NAME
380 ,P_PKG_NAME => G_PKG_NAME
381 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
382 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
383 ,X_MSG_COUNT => X_MSG_COUNT
384 ,X_MSG_DATA => X_MSG_DATA
385 ,X_RETURN_STATUS => X_RETURN_STATUS);
386
387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
388 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
389 P_API_NAME => L_API_NAME
390 ,P_PKG_NAME => G_PKG_NAME
391 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
392 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
393 ,X_MSG_COUNT => X_MSG_COUNT
394 ,X_MSG_DATA => X_MSG_DATA
395 ,X_RETURN_STATUS => X_RETURN_STATUS);
396
397 WHEN OTHERS THEN
398 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
399 P_API_NAME => L_API_NAME
400 ,P_PKG_NAME => G_PKG_NAME
401 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
402 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
403 ,X_MSG_COUNT => X_MSG_COUNT
404 ,X_MSG_DATA => X_MSG_DATA
405 ,X_RETURN_STATUS => X_RETURN_STATUS);
406 End Create_requirement_headers;
407
408
409 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
410 PROCEDURE Update_requirement_headers(
411 P_Api_Version_Number IN NUMBER,
412 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
413 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
414 P_RQH_Rec IN RQH_Rec_Type := G_MISS_RQH_REC,
418 )
415 X_Return_Status OUT NOCOPY VARCHAR2,
416 X_Msg_Count OUT NOCOPY NUMBER,
417 X_Msg_Data OUT NOCOPY VARCHAR2
419
420 IS
421 l_api_name CONSTANT VARCHAR2(30) := 'Update_requirement_headers';
422 l_api_version_number CONSTANT NUMBER := 1.0;
423 l_pvt_RQH_rec CSP_Requirement_Headers_PVT.Requirement_Header_Rec_Type;
424 BEGIN
425 -- Standard Start of API savepoint
426 SAVEPOINT UPDATE_Packlist_Headers_PUB;
427
428 -- Standard call to check for call compatibility.
429 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
430 p_api_version_number,
431 l_api_name,
432 G_PKG_NAME)
433 THEN
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437
438 -- Initialize message list if p_init_msg_list is set to TRUE.
439 IF FND_API.to_Boolean( p_init_msg_list )
440 THEN
441 FND_MSG_PUB.initialize;
442 END IF;
443
444 -- Initialize API return status to SUCCESS
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446
447 --
448 -- API body
449 --
450
451 -- Convert the values to ids
452 --
453 Convert_RQH_Values_To_Ids (
454 p_RQH_rec => p_RQH_rec,
455 x_pvt_RQH_rec => l_pvt_RQH_rec
456 );
457
458 Validate_Requirement_Header(l_pvt_RQH_rec);
459
460 CSP_requirement_headers_PVT.Update_requirement_headers(
461 P_Api_Version_Number => 1.0,
462 P_Init_Msg_List => FND_API.G_FALSE,
463 P_Commit => p_commit,
464 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
465 P_Requirement_Header_Rec => l_pvt_RQH_Rec ,
466 X_Return_Status => x_return_status,
467 X_Msg_Count => x_msg_count,
468 X_Msg_Data => x_msg_data);
469
470
471
472 -- Check return status from the above procedure call
473 IF x_return_status = FND_API.G_RET_STS_ERROR then
474 raise FND_API.G_EXC_ERROR;
475 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
476 raise FND_API.G_EXC_UNEXPECTED_ERROR;
477 END IF;
478
479 --
480 -- End of API body
481 --
482
483 -- Standard check for p_commit
484 IF FND_API.to_Boolean( p_commit )
485 THEN
486 COMMIT WORK;
487 END IF;
488
489
490 -- Debug Message
491 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
492
493
494 -- Standard call to get message count and if count is 1, get message info.
495 FND_MSG_PUB.Count_And_Get
496 ( p_count => x_msg_count,
497 p_data => x_msg_data
498 );
499
500 EXCEPTION
501 WHEN FND_API.G_EXC_ERROR THEN
502 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
503 P_API_NAME => L_API_NAME
504 ,P_PKG_NAME => G_PKG_NAME
505 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
506 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
507 ,X_MSG_COUNT => X_MSG_COUNT
508 ,X_MSG_DATA => X_MSG_DATA
509 ,X_RETURN_STATUS => X_RETURN_STATUS);
510
511 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
512 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
513 P_API_NAME => L_API_NAME
514 ,P_PKG_NAME => G_PKG_NAME
515 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
516 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
517 ,X_MSG_COUNT => X_MSG_COUNT
518 ,X_MSG_DATA => X_MSG_DATA
519 ,X_RETURN_STATUS => X_RETURN_STATUS);
520
521 WHEN OTHERS THEN
522 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
523 P_API_NAME => L_API_NAME
524 ,P_PKG_NAME => G_PKG_NAME
525 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
526 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
527 ,X_MSG_COUNT => X_MSG_COUNT
528 ,X_MSG_DATA => X_MSG_DATA
529 ,X_RETURN_STATUS => X_RETURN_STATUS);
530 End Update_requirement_headers;
531
532
533 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
534 -- The Master delete procedure may not be needed depends on different business requirements.
535 PROCEDURE Delete_requirement_headers(
536 P_Api_Version_Number IN NUMBER,
537 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
538 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
539 P_RQH_Rec IN RQH_Rec_Type,
540 X_Return_Status OUT NOCOPY VARCHAR2,
541 X_Msg_Count OUT NOCOPY NUMBER,
542 X_Msg_Data OUT NOCOPY VARCHAR2
543 )
544
545 IS
546 l_api_name CONSTANT VARCHAR2(30) := 'Delete_requirement_headers';
547 l_api_version_number CONSTANT NUMBER := 1.0;
548 l_pvt_RQH_rec CSP_Requirement_Headers_PVT.Requirement_Header_Rec_Type;
549 I NUMBER;
550 CURSOR rqmt_lines_cur(p_rqmt_header_id NUMBER) IS
551 SELECT REQUIREMENT_LINE_ID
552 /* CREATED_BY,
553 CREATION_DATE,
557 REQUIREMENT_HEADER_ID,
554 LAST_UPDATED_BY,
555 LAST_UPDATE_DATE,
556 LAST_UPDATE_LOGIN,
558 INVENTORY_ITEM_ID,
559 UOM_CODE,
560 REQUIRED_QUANTITY,
561 SHIP_COMPLETE_FLAG,
562 LIKELIHOOD,
563 REVISION,
564 SOURCE_ORGANIZATION_ID,
565 SOURCE_SUBINVENTORY,
566 ORDERED_QUANTITY,
567 ORDER_LINE_ID,
568 RESERVATION_ID,
569 LOCAL_RESERVATION_ID,
570 ORDER_BY_DATE ,
571 ARRIVAL_DATE,
572 ITEM_SCRATCHPAD,
573 SHIPPING_METHOD_CODE,
574 ATTRIBUTE_CATEGORY,
575 ATTRIBUTE1,
576 ATTRIBUTE2,
577 ATTRIBUTE3,
578 ATTRIBUTE4,
579 ATTRIBUTE5,
580 ATTRIBUTE6,
581 ATTRIBUTE7,
582 ATTRIBUTE8,
583 ATTRIBUTE9,
584 ATTRIBUTE10,
585 ATTRIBUTE11,
586 ATTRIBUTE12,
587 ATTRIBUTE13,
588 ATTRIBUTE14,
589 ATTRIBUTE15,
590 SECURITY_GROUP_ID,
591 SOURCED_FROM
592 */
593 FROM csp_requirement_lines
594 WHERE requirement_header_id = p_rqmt_header_id;
595 l_RQL_TBL CSP_Requirement_lines_PUB.RQL_Tbl_Type;
596 l_RQL_Rec CSP_Requirement_lines_PUB.RQL_Rec_Type;
597 BEGIN
598 -- Standard Start of API savepoint
599 SAVEPOINT DELETE_Requirement_Headers_PUB;
600
601 -- Standard call to check for call compatibility.
602 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
603 p_api_version_number,
604 l_api_name,
605 G_PKG_NAME)
606 THEN
607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
608 END IF;
609
610
611 -- Initialize message list if p_init_msg_list is set to TRUE.
612 IF FND_API.to_Boolean( p_init_msg_list )
613 THEN
614 FND_MSG_PUB.initialize;
615 END IF;
616
617
618 -- Debug Message
619 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'start');
620
621
622 -- Initialize API return status to SUCCESS
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624
625 --
626 -- API body
627 --
628
629 -- Debug Message
630 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'AS: Public API: Convert_RQH_Values_To_Ids');
631
632 -- Convert the values to ids
633 --
634 Convert_RQH_Values_To_Ids (
635 p_RQH_rec => p_RQH_rec,
636 x_pvt_RQH_rec => l_pvt_RQH_rec
637 );
638
639 -- Delete all requirement lines for this header
640 -- before deleting the header
641 OPEN rqmt_lines_cur(P_RQH_Rec.requirement_header_id);
642 I := 1;
643 LOOP
644 FETCH rqmt_lines_cur INTO l_RQL_Rec.requirement_line_id;
645 EXIT WHEN rqmt_lines_cur%NOTFOUND;
646 l_RQL_TBL(I) := l_RQL_rec;
647 I := I + 1;
648 END LOOP;
649
650 IF (l_RQL_TBL.COUNT > 0) THEN
651 CSP_requirement_lines_pub.Delete_requirement_lines(
652 P_Api_Version_Number => 1.0,
653 P_Init_Msg_List => FND_API.G_FALSE,
654 P_Commit => p_commit,
655 P_RQL_TBL => l_RQL_TBL,
656 X_Return_Status => x_return_status,
657 X_Msg_Count => x_msg_count,
658 X_Msg_Data => x_msg_data);
659
660 -- Check return status from the above procedure call
661 IF x_return_status = FND_API.G_RET_STS_ERROR then
662 raise FND_API.G_EXC_ERROR;
663 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
664 raise FND_API.G_EXC_UNEXPECTED_ERROR;
665 END IF;
666 END IF;
667
668 CSP_requirement_headers_PVT.Delete_requirement_headers(
669 P_Api_Version_Number => 1.0,
670 P_Init_Msg_List => FND_API.G_FALSE,
671 P_Commit => p_commit,
672 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
673 P_Requirement_Header_Rec => l_pvt_RQH_Rec,
674 X_Return_Status => x_return_status,
675 X_Msg_Count => x_msg_count,
676 X_Msg_Data => x_msg_data);
677
678 -- Check return status from the above procedure call
679 IF x_return_status = FND_API.G_RET_STS_ERROR then
680 raise FND_API.G_EXC_ERROR;
681 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
682 raise FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF;
684
685 --
686 -- End of API body
687 --
688
689 -- Standard check for p_commit
690 IF FND_API.to_Boolean( p_commit )
691 THEN
692 COMMIT WORK;
693 END IF;
694
695 -- Debug Message
696 --JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Public API: ' || l_api_name || 'end');
697
698 -- Standard call to get message count and if count is 1, get message info.
699 FND_MSG_PUB.Count_And_Get
700 ( p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703
704 EXCEPTION
705 WHEN FND_API.G_EXC_ERROR THEN
706 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
707 P_API_NAME => L_API_NAME
708 ,P_PKG_NAME => G_PKG_NAME
709 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
710 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
711 ,X_MSG_COUNT => X_MSG_COUNT
712 ,X_MSG_DATA => X_MSG_DATA
713 ,X_RETURN_STATUS => X_RETURN_STATUS);
714
715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
716 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
717 P_API_NAME => L_API_NAME
718 ,P_PKG_NAME => G_PKG_NAME
719 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
720 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
721 ,X_MSG_COUNT => X_MSG_COUNT
722 ,X_MSG_DATA => X_MSG_DATA
723 ,X_RETURN_STATUS => X_RETURN_STATUS);
724
725 WHEN OTHERS THEN
726 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
727 P_API_NAME => L_API_NAME
728 ,P_PKG_NAME => G_PKG_NAME
729 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
730 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
731 ,X_MSG_COUNT => X_MSG_COUNT
732 ,X_MSG_DATA => X_MSG_DATA
733 ,X_RETURN_STATUS => X_RETURN_STATUS);
734 End Delete_requirement_headers;
735
736 END CSP_REQUIREMENT_HEADERS_PUB;