[Home] [Help]
PACKAGE BODY: APPS.CSP_REQUIREMENT_HEADERS_PVT
Source
1 PACKAGE BODY CSP_Requirement_headers_PVT as
2 /* $Header: cspvrqhb.pls 115.8 2003/05/02 00:28:13 phegde noship $ */
3 -- Start of Comments
4 -- Package name : CSP_Requirement_headers_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Requirement_headers_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvrqhb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 PROCEDURE Create_requirement_headers(
17 P_Api_Version_Number IN NUMBER,
18 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
19 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
20 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
21 P_REQUIREMENT_HEADER_Rec IN REQUIREMENT_HEADER_Rec_Type := G_MISS_REQUIREMENT_HEADER_REC,
22 X_REQUIREMENT_HEADER_ID OUT NOCOPY NUMBER,
23 X_Return_Status OUT NOCOPY VARCHAR2,
24 X_Msg_Count OUT NOCOPY NUMBER,
25 X_Msg_Data OUT NOCOPY VARCHAR2
26 )
27
28 IS
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_requirement_headers';
30 l_api_version_number CONSTANT NUMBER := 1.0;
31 l_return_status_full VARCHAR2(1);
32 l_access_flag VARCHAR2(1);
33 BEGIN
34 -- Standard Start of API savepoint
35 SAVEPOINT CREATE_Requirement_headers_PUB;
36
37 -- Standard call to check for call compatibility.
38 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
39 p_api_version_number,
40 l_api_name,
41 G_PKG_NAME)
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45
46 -- Initialize message list if p_init_msg_list is set to TRUE.
47 IF FND_API.to_Boolean( p_init_msg_list )
48 THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52
53 -- Debug Message
54 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
55
56
57 -- Initialize API return status to SUCCESS
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 --
61 -- API body
62 --
63 -- ******************************************************************
64 -- Validate Environment
65 -- ******************************************************************
66 /* IF FND_GLOBAL.User_Id IS NULL
67 THEN
68 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
69 THEN
70 FND_MESSAGE.Set_Name('CSP', 'UT_CANNOT_GET_PROFILE_VALUE');
71 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
72 FND_MSG_PUB.ADD;
73 END IF;
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76 */
77
78 /* IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
79 THEN
80 JTF_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
81 p_api_version_number => 2.0
82 ,p_init_msg_list => p_init_msg_list
83 --,p_admin_group_id => p_admin_group_id
84 ,x_return_status => x_return_status
85 ,x_msg_count => x_msg_count
86 ,x_msg_data => x_msg_data
87 --,x_sales_member_rec => l_identity_sales_member_rec);
88
89
90 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
91 RAISE FND_API.G_EXC_ERROR;
92 END IF;
93 */
94 -- Debug message
95 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_requirement_headers');
96
97 -- Invoke validation procedures
98 /* Validate_requirement_headers(
99 p_init_msg_list => FND_API.G_FALSE,
100 p_validation_level => p_validation_level,
101 p_validation_mode => JTF_UTILITY_PVT.G_CREATE,
102 P_REQUIREMENT_HEADER_Rec => P_REQUIREMENT_HEADER_Rec,
103 x_return_status => x_return_status,
104 x_msg_count => x_msg_count,
105 x_msg_data => x_msg_data);
106
107 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110 */
111 -- Debug Message
112 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
113
114 x_requirement_header_id := p_requirement_header_rec.requirement_header_id;
115
116 -- Invoke table handler(CSP_REQUIREMENT_HEADERS_PKG.Insert_Row)
117 CSP_REQUIREMENT_HEADERS_PKG.Insert_Row(
118 px_REQUIREMENT_HEADER_ID => x_REQUIREMENT_HEADER_ID,
119 p_CREATED_BY => nvl(FND_GLOBAL.USER_ID, 1),
120 p_CREATION_DATE => SYSDATE,
121 p_LAST_UPDATED_BY => nvl(FND_GLOBAL.USER_ID, 1),
122 p_LAST_UPDATE_DATE => SYSDATE,
123 p_LAST_UPDATE_LOGIN => nvl(FND_GLOBAL.CONC_LOGIN_ID, -1),
124 p_OPEN_REQUIREMENT => p_REQUIREMENT_HEADER_rec.OPEN_REQUIREMENT,
125 p_SHIP_TO_LOCATION_ID => p_REQUIREMENT_HEADER_rec.SHIP_TO_LOCATION_ID,
126 p_TASK_ID => p_REQUIREMENT_HEADER_rec.TASK_ID,
127 p_TASK_ASSIGNMENT_ID => p_REQUIREMENT_HEADER_rec.TASK_ASSIGNMENT_ID,
128 p_SHIPPING_METHOD_CODE => p_REQUIREMENT_HEADER_rec.SHIPPING_METHOD_CODE,
129 p_NEED_BY_DATE => p_REQUIREMENT_HEADER_rec.NEED_BY_DATE,
130 p_DESTINATION_ORGANIZATION_ID => p_REQUIREMENT_HEADER_rec.DESTINATION_ORGANIZATION_ID,
131 p_PARTS_DEFINED => p_REQUIREMENT_HEADER_rec.PARTS_DEFINED,
132 p_ATTRIBUTE_CATEGORY => p_REQUIREMENT_HEADER_rec.ATTRIBUTE_CATEGORY,
133 p_ATTRIBUTE1 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE1,
134 p_ATTRIBUTE2 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE2,
135 p_ATTRIBUTE3 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE3,
136 p_ATTRIBUTE4 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE4,
137 p_ATTRIBUTE5 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE5,
138 p_ATTRIBUTE6 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE6,
139 p_ATTRIBUTE7 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE7,
140 p_ATTRIBUTE8 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE8,
141 p_ATTRIBUTE9 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE9,
142 p_ATTRIBUTE10 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE10,
143 p_ATTRIBUTE11 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE11,
144 p_ATTRIBUTE12 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE12,
145 p_ATTRIBUTE13 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE13,
146 p_ATTRIBUTE14 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE14,
147 p_ATTRIBUTE15 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE15,
148 p_ORDER_TYPE_ID => p_REQUIREMENT_HEADER_rec.ORDER_TYPE_ID,
149 p_ADDRESS_TYPE => p_REQUIREMENT_HEADER_rec.ADDRESS_TYPE,
150 p_RESOURCE_ID => p_REQUIREMENT_HEADER_rec.RESOURCE_ID,
151 p_RESOURCE_TYPE => p_REQUIREMENT_HEADER_rec.RESOURCE_TYPE,
152 p_TIMEZONE_ID => p_REQUIREMENT_HEADER_rec.TIMEZONE_ID,
153 p_DESTINATION_SUBINVENTORY => p_REQUIREMENT_HEADER_rec.DESTINATION_SUBINVENTORY
154 );
155
156 -- Hint: Primary key should be returned.
157 -- x_REQUIREMENT_HEADER_ID := px_REQUIREMENT_HEADER_ID;
158
159 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162 */
163 --
164 -- End of API body
165 --
166
167 -- Standard check for p_commit
168 IF FND_API.to_Boolean( p_commit )
169 THEN
170 COMMIT WORK;
171 END IF;
172
173
174 -- Debug Message
175 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
176
177
178 -- Standard call to get message count and if count is 1, get message info.
179 /* FND_MSG_PUB.Count_And_Get
180 ( p_count => x_msg_count,
181 p_data => x_msg_data
182 ); */
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
187 P_API_NAME => L_API_NAME
188 ,P_PKG_NAME => G_PKG_NAME
189 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
190 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
191 ,X_MSG_COUNT => X_MSG_COUNT
192 ,X_MSG_DATA => X_MSG_DATA
193 ,X_RETURN_STATUS => X_RETURN_STATUS);
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
196 P_API_NAME => L_API_NAME
197 ,P_PKG_NAME => G_PKG_NAME
198 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
199 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
200 ,X_MSG_COUNT => X_MSG_COUNT
201 ,X_MSG_DATA => X_MSG_DATA
202 ,X_RETURN_STATUS => X_RETURN_STATUS);
203 WHEN OTHERS THEN
204 Rollback to CREATE_Requirement_headers_PUB;
205 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
206 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
207 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
208 FND_MSG_PUB.ADD;
209 fnd_msg_pub.count_and_get
210 ( p_count => x_msg_count
211 , p_data => x_msg_data);
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 End Create_requirement_headers;
214
215
216 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
217 PROCEDURE Update_requirement_headers(
218 P_Api_Version_Number IN NUMBER,
219 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
220 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
222 P_REQUIREMENT_HEADER_Rec IN REQUIREMENT_HEADER_Rec_Type,
223 X_Return_Status OUT NOCOPY VARCHAR2,
224 X_Msg_Count OUT NOCOPY NUMBER,
225 X_Msg_Data OUT NOCOPY VARCHAR2
226 )
227
228 IS
229 /*
230 Cursor C_Get_requirement_headers(REQUIREMENT_HEADER_ID Number) IS
231 Select rowid,
232 REQUIREMENT_HEADER_ID,
233 CREATED_BY,
234 CREATION_DATE,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_DATE,
237 LAST_UPDATE_LOGIN,
238 OPEN_REQUIREMENT,
239 SHIP_TO_LOCATION_ID,
240 TASK_ID,
241 TASK_ASSIGNMENT_ID,
242 SHIPPING_METHOD_CODE,
243 NEED_BY_DATE,
244 DESTINATION_ORGANIZATION_ID,
245 PARTS_DEFINED,
246 ATTRIBUTE_CATEGORY,
247 ATTRIBUTE1,
248 ATTRIBUTE2,
249 ATTRIBUTE3,
250 ATTRIBUTE4,
251 ATTRIBUTE5,
252 ATTRIBUTE6,
253 ATTRIBUTE7,
254 ATTRIBUTE8,
255 ATTRIBUTE9,
256 ATTRIBUTE10,
257 ATTRIBUTE11,
258 ATTRIBUTE12,
259 ATTRIBUTE13,
260 ATTRIBUTE14,
261 ATTRIBUTE15,
262 From CSP_REQUIREMENT_HEADERS
263 -- Hint: Developer need to provide Where clause
264 For Update NOWAIT;
265 */
266 l_api_name CONSTANT VARCHAR2(30) := 'Update_requirement_headers';
267 l_api_version_number CONSTANT NUMBER := 1.0;
268 -- Local Variables
269 l_ref_REQUIREMENT_HEADER_rec CSP_requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type;
270 l_tar_REQUIREMENT_HEADER_rec CSP_requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type := P_REQUIREMENT_HEADER_Rec;
271 l_rowid ROWID;
272 BEGIN
273 -- Standard Start of API savepoint
274 SAVEPOINT UPDATE_Requirement_headers_PUB;
275
276 -- Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
278 p_api_version_number,
279 l_api_name,
280 G_PKG_NAME)
281 THEN
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END IF;
284
285
286 -- Initialize message list if p_init_msg_list is set to TRUE.
287 IF FND_API.to_Boolean( p_init_msg_list )
288 THEN
289 FND_MSG_PUB.initialize;
290 END IF;
291
292
293 -- Debug Message
294 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
295
296
297 -- Initialize API return status to SUCCESS
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 --
301 -- Api body
302 --
303
304 /* IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
305 THEN
306 JTF_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
307 p_api_version_number => 2.0
308 ,p_init_msg_list => p_init_msg_list
309 ,p_salesforce_id => p_identity_salesforce_id
310 ,p_admin_group_id => p_admin_group_id
311 ,x_return_status => x_return_status
312 ,x_msg_count => x_msg_count
313 ,x_msg_data => x_msg_data
314 ,x_sales_member_rec => l_identity_sales_member_rec);
315
316
317 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 END IF;
322 */
323 -- Debug Message
324 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
325
326 /*
327 Open C_Get_requirement_headers( l_tar_REQUIREMENT_HEADER_rec.REQUIREMENT_HEADER_ID);
328
329 Fetch C_Get_requirement_headers into
330 l_rowid,
331 l_ref_REQUIREMENT_HEADER_rec.REQUIREMENT_HEADER_ID,
332 l_ref_REQUIREMENT_HEADER_rec.CREATED_BY,
333 l_ref_REQUIREMENT_HEADER_rec.CREATION_DATE,
334 l_ref_REQUIREMENT_HEADER_rec.LAST_UPDATED_BY,
335 l_ref_REQUIREMENT_HEADER_rec.LAST_UPDATE_DATE,
336 l_ref_REQUIREMENT_HEADER_rec.LAST_UPDATE_LOGIN,
337 l_ref_REQUIREMENT_HEADER_rec.OPEN_REQUIREMENT,
338 l_ref_REQUIREMENT_HEADER_rec.SHIP_TO_LOCATION_ID,
339 l_ref_REQUIREMENT_HEADER_rec.TASK_ID,
340 l_ref_REQUIREMENT_HEADER_rec.TASK_ASSIGNMENT_ID,
341 l_ref_REQUIREMENT_HEADER_rec.SHIPPING_METHOD_CODE,
342 l_ref_REQUIREMENT_HEADER_rec.NEED_BY_DATE,
343 l_ref_REQUIREMENT_HEADER_rec.DESTINATION_ORGANIZATION_ID,
344 l_ref_REQUIREMENT_HEADER_rec.PARTS_DEFINED,
345 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE_CATEGORY,
346 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE1,
347 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE2,
348 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE3,
349 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE4,
350 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE5,
351 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE6,
352 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE7,
353 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE8,
354 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE9,
355 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE10,
356 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE11,
357 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE12,
358 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE13,
359 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE14,
360 l_ref_REQUIREMENT_HEADER_rec.ATTRIBUTE15,
361
362 If ( C_Get_requirement_headers%NOTFOUND) Then
363 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
364 THEN
365 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_UPDATE_TARGET');
366 FND_MESSAGE.Set_Token ('INFO', 'requirement_headers', FALSE);
367 FND_MSG_PUB.Add;
368 END IF;
369 Close C_Get_requirement_headers;
370 raise FND_API.G_EXC_ERROR;
371 END IF;
372 -- Debug Message
373 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
374 Close C_Get_requirement_headers;
375 */
376
377
378 /* If (l_tar_REQUIREMENT_HEADER_rec.last_update_date is NULL or
379 l_tar_REQUIREMENT_HEADER_rec.last_update_date = FND_API.G_MISS_Date ) Then
380 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
381 THEN
382 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_ID');
383 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
384 FND_MSG_PUB.ADD;
385 END IF;
386 raise FND_API.G_EXC_ERROR;
387 End if;
388 -- Check Whether record has been changed by someone else
389 If (l_tar_REQUIREMENT_HEADER_rec.last_update_date <> l_ref_REQUIREMENT_HEADER_rec.last_update_date) Then
390 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
391 THEN
392 FND_MESSAGE.Set_Name('CSP', 'API_RECORD_CHANGED');
393 FND_MESSAGE.Set_Token('INFO', 'requirement_headers', FALSE);
394 FND_MSG_PUB.ADD;
395 END IF;
396 raise FND_API.G_EXC_ERROR;
397 End if;
398
399 -- Debug message
400 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_requirement_headers');
401
402 -- Invoke validation procedures
403 Validate_requirement_headers(
404 p_init_msg_list => FND_API.G_FALSE,
405 p_validation_level => p_validation_level,
406 p_validation_mode => JTF_UTILITY_PVT.G_UPDATE,
407 P_REQUIREMENT_HEADER_Rec => P_REQUIREMENT_HEADER_Rec,
408 x_return_status => x_return_status,
409 x_msg_count => x_msg_count,
410 x_msg_data => x_msg_data);
411
412 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
413 RAISE FND_API.G_EXC_ERROR;
414 END IF;
415
416 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419 */
420
421 -- Debug Message
422 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
423
424 -- Invoke table handler(CSP_REQUIREMENT_HEADERS_PKG.Update_Row)
425 CSP_REQUIREMENT_HEADERS_PKG.Update_Row(
426 p_REQUIREMENT_HEADER_ID => p_REQUIREMENT_HEADER_rec.REQUIREMENT_HEADER_ID,
427 p_CREATED_BY => FND_API.G_MISS_NUM,
428 p_CREATION_DATE => FND_API.G_MISS_DATE,
429 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
430 p_LAST_UPDATE_DATE => SYSDATE,
431 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
432 p_OPEN_REQUIREMENT => p_REQUIREMENT_HEADER_rec.OPEN_REQUIREMENT,
433 p_SHIP_TO_LOCATION_ID => p_REQUIREMENT_HEADER_rec.SHIP_TO_LOCATION_ID,
434 p_TASK_ID => p_REQUIREMENT_HEADER_rec.TASK_ID,
435 p_TASK_ASSIGNMENT_ID => p_REQUIREMENT_HEADER_rec.TASK_ASSIGNMENT_ID,
436 p_SHIPPING_METHOD_CODE => p_REQUIREMENT_HEADER_rec.SHIPPING_METHOD_CODE,
437 p_NEED_BY_DATE => p_REQUIREMENT_HEADER_rec.NEED_BY_DATE,
438 p_DESTINATION_ORGANIZATION_ID => p_REQUIREMENT_HEADER_rec.DESTINATION_ORGANIZATION_ID,
439 p_PARTS_DEFINED => p_REQUIREMENT_HEADER_rec.PARTS_DEFINED,
440 p_ATTRIBUTE_CATEGORY => p_REQUIREMENT_HEADER_rec.ATTRIBUTE_CATEGORY,
441 p_ATTRIBUTE1 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE1,
442 p_ATTRIBUTE2 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE2,
443 p_ATTRIBUTE3 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE3,
444 p_ATTRIBUTE4 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE4,
445 p_ATTRIBUTE5 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE5,
446 p_ATTRIBUTE6 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE6,
447 p_ATTRIBUTE7 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE7,
448 p_ATTRIBUTE8 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE8,
449 p_ATTRIBUTE9 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE9,
450 p_ATTRIBUTE10 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE10,
451 p_ATTRIBUTE11 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE11,
452 p_ATTRIBUTE12 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE12,
453 p_ATTRIBUTE13 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE13,
454 p_ATTRIBUTE14 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE14,
455 p_ATTRIBUTE15 => p_REQUIREMENT_HEADER_rec.ATTRIBUTE15,
456 p_ORDER_TYPE_ID => p_REQUIREMENT_HEADER_rec.ORDER_TYPE_ID,
457 p_ADDRESS_TYPE => p_REQUIREMENT_HEADER_rec.ADDRESS_TYPE,
458 p_RESOURCE_ID => p_REQUIREMENT_HEADER_rec.RESOURCE_ID,
459 p_RESOURCE_TYPE => p_REQUIREMENT_HEADER_rec.RESOURCE_TYPE,
460 p_TIMEZONE_ID => p_REQUIREMENT_HEADER_rec.TIMEZONE_ID,
461 p_DESTINATION_SUBINVENTORY => p_REQUIREMENT_HEADER_rec.DESTINATION_SUBINVENTORY
462 );
463 --
464 -- End of API body.
465 --
466
467 -- Standard check for p_commit
468 IF FND_API.to_Boolean( p_commit )
469 THEN
470 COMMIT WORK;
471 END IF;
472
473
474 -- Debug Message
475 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
476
477
478 -- Standard call to get message count and if count is 1, get message info.
479 FND_MSG_PUB.Count_And_Get
480 ( p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483
484 EXCEPTION
485 WHEN FND_API.G_EXC_ERROR THEN
486 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
487 P_API_NAME => L_API_NAME
488 ,P_PKG_NAME => G_PKG_NAME
489 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
490 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
491 ,X_MSG_COUNT => X_MSG_COUNT
492 ,X_MSG_DATA => X_MSG_DATA
493 ,X_RETURN_STATUS => X_RETURN_STATUS);
494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
496 P_API_NAME => L_API_NAME
497 ,P_PKG_NAME => G_PKG_NAME
498 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
499 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
500 ,X_MSG_COUNT => X_MSG_COUNT
501 ,X_MSG_DATA => X_MSG_DATA
502 ,X_RETURN_STATUS => X_RETURN_STATUS);
503 WHEN OTHERS THEN
504 Rollback to UPDATE_Requirement_headers_PUB;
505 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
506 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
507 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
508 FND_MSG_PUB.ADD;
509 fnd_msg_pub.count_and_get
510 ( p_count => x_msg_count
511 , p_data => x_msg_data);
512 x_return_status := FND_API.G_RET_STS_ERROR;
513 End Update_requirement_headers;
514
515
516 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
517 -- The Master delete procedure may not be needed depends on different business requirements.
518 PROCEDURE Delete_requirement_headers(
519 P_Api_Version_Number IN NUMBER,
520 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
521 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
522 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
523 P_REQUIREMENT_HEADER_Rec IN REQUIREMENT_HEADER_Rec_Type,
524 X_Return_Status OUT NOCOPY VARCHAR2,
525 X_Msg_Count OUT NOCOPY NUMBER,
526 X_Msg_Data OUT NOCOPY VARCHAR2
527 )
528
529 IS
530 l_api_name CONSTANT VARCHAR2(30) := 'Delete_requirement_headers';
531 l_api_version_number CONSTANT NUMBER := 1.0;
532 BEGIN
533 -- Standard Start of API savepoint
534 SAVEPOINT DELETE_Requirement_headers_PUB;
535
536 -- Standard call to check for call compatibility.
537 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
538 p_api_version_number,
539 l_api_name,
540 G_PKG_NAME)
541 THEN
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 END IF;
544
545
546 -- Initialize message list if p_init_msg_list is set to TRUE.
547 IF FND_API.to_Boolean( p_init_msg_list )
548 THEN
549 FND_MSG_PUB.initialize;
550 END IF;
551
552
553 -- Debug Message
554 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
555
556
557 -- Initialize API return status to SUCCESS
558 x_return_status := FND_API.G_RET_STS_SUCCESS;
559
560 --
561 -- Api body
562 --
563
564 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 -- Debug Message
569 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
570
571 -- Invoke table handler(CSP_REQUIREMENT_HEADERS_PKG.Delete_Row)
572 CSP_REQUIREMENT_HEADERS_PKG.Delete_Row(
573 p_REQUIREMENT_HEADER_ID => p_REQUIREMENT_HEADER_rec.REQUIREMENT_HEADER_ID);
574 --
575 -- End of API body
576 --
577
578 -- Standard check for p_commit
579 IF FND_API.to_Boolean( p_commit )
580 THEN
581 COMMIT WORK;
582 END IF;
583
584
585 -- Debug Message
586 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
587
588
589 -- Standard call to get message count and if count is 1, get message info.
590 FND_MSG_PUB.Count_And_Get
591 ( p_count => x_msg_count,
592 p_data => x_msg_data
593 );
594
595 EXCEPTION
596 WHEN FND_API.G_EXC_ERROR THEN
597 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
598 P_API_NAME => L_API_NAME
599 ,P_PKG_NAME => G_PKG_NAME
600 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
601 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
602 ,X_MSG_COUNT => X_MSG_COUNT
603 ,X_MSG_DATA => X_MSG_DATA
604 ,X_RETURN_STATUS => X_RETURN_STATUS);
605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
606 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
607 P_API_NAME => L_API_NAME
608 ,P_PKG_NAME => G_PKG_NAME
609 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
610 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
611 ,X_MSG_COUNT => X_MSG_COUNT
612 ,X_MSG_DATA => X_MSG_DATA
613 ,X_RETURN_STATUS => X_RETURN_STATUS);
614 WHEN OTHERS THEN
615 Rollback to DELETE_Requirement_headers_PUB;
616 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
617 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
618 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
619 FND_MSG_PUB.ADD;
620 fnd_msg_pub.count_and_get
621 ( p_count => x_msg_count
622 , p_data => x_msg_data);
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 End Delete_requirement_headers;
625
626 /*
627 -- This procudure defines the columns for the Dynamic SQL.
628 PROCEDURE Define_Columns(
629 P_REQUIREMENT_HEADER_Rec IN CSP_Requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type,
630 p_cur_get_REQUIREMENT_HEADER IN NUMBER
631 )
632 IS
633 BEGIN
634 -- Debug Message
635 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Begins');
636
637 -- define all columns for CSP_REQUIREMENT_HEADERS view
638 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 1, P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID);
639 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 2, P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT, 240);
640 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 3, P_REQUIREMENT_HEADER_Rec.SHIP_TO_LOCATION_ID);
641 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 4, P_REQUIREMENT_HEADER_Rec.TASK_ID);
642 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 5, P_REQUIREMENT_HEADER_Rec.TASK_ASSIGNMENT_ID);
643 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 6, P_REQUIREMENT_HEADER_Rec.SHIPPING_METHOD_CODE, 30);
644 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 7, P_REQUIREMENT_HEADER_Rec.NEED_BY_DATE);
645 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 8, P_REQUIREMENT_HEADER_Rec.DESTINATION_ORGANIZATION_ID);
646 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 9, P_REQUIREMENT_HEADER_Rec.PARTS_DEFINED, 30);
647 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 10, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE_CATEGORY, 30);
648 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 11, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE1, 150);
649 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 12, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE2, 150);
650 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 13, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE3, 150);
651 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 14, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE4, 150);
652 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 15, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE5, 150);
653 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 16, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE6, 150);
654 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 17, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE7, 150);
655 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 18, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE8, 150);
656 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 19, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE9, 150);
657 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 20, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE10, 150);
658 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 21, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE11, 150);
659 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 22, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE12, 150);
660 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 23, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE13, 150);
661 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 24, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE14, 150);
662 dbms_sql.define_column(p_cur_get_REQUIREMENT_HEADER, 25, P_REQUIREMENT_HEADER_Rec.ATTRIBUTE15, 150);
663
664 -- Debug Message
665 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Ends');
666 END Define_Columns;
667
668 -- This procudure gets column values by the Dynamic SQL.
669 PROCEDURE Get_Column_Values(
670 p_cur_get_REQUIREMENT_HEADER IN NUMBER,
671 X_REQUIREMENT_HEADER_Rec OUT NOCOPY CSP_Requirement_headers_PUB.REQUIREMENT_HEADER_Rec_Type
672 )
673 IS
674 BEGIN
675 -- Debug Message
676 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Get Column Values Begins');
677
678 -- get all column values for CSP_REQUIREMENT_HEADERS table
679 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 1, X_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID);
680 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 2, X_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT);
681 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 3, X_REQUIREMENT_HEADER_Rec.SHIP_TO_LOCATION_ID);
682 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 4, X_REQUIREMENT_HEADER_Rec.TASK_ID);
683 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 5, X_REQUIREMENT_HEADER_Rec.TASK_ASSIGNMENT_ID);
684 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 6, X_REQUIREMENT_HEADER_Rec.SHIPPING_METHOD_CODE);
685 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 7, X_REQUIREMENT_HEADER_Rec.NEED_BY_DATE);
686 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 8, X_REQUIREMENT_HEADER_Rec.DESTINATION_ORGANIZATION_ID);
687 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 9, X_REQUIREMENT_HEADER_Rec.PARTS_DEFINED);
688 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 10, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE_CATEGORY);
689 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 11, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE1);
690 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 12, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE2);
691 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 13, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE3);
692 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 14, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE4);
693 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 15, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE5);
694 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 16, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE6);
695 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 17, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE7);
696 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 18, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE8);
697 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 19, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE9);
698 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 20, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE10);
699 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 21, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE11);
700 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 22, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE12);
701 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 23, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE13);
702 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 24, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE14);
703 dbms_sql.column_value(p_cur_get_REQUIREMENT_HEADER, 25, X_REQUIREMENT_HEADER_Rec.ATTRIBUTE15);
704
705 -- Debug Message
706 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Get Column Values Ends');
707 END Get_Column_Values;
708
709 -- This procedure bind the variables for the Dynamic SQL
710 PROCEDURE Bind(
711 P_REQUIREMENT_HEADER_Rec IN CSP_Requirement_headers_PUB.REQUIREMENT_HEADER_Rec_Type,
712 -- Hint: Add more binding variables here
713 p_cur_get_REQUIREMENT_HEADER IN NUMBER
714 )
715 IS
716 BEGIN
717 -- Bind variables
718 -- Only those that are not NULL
719 -- Debug Message
720 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Begins');
721
722 -- The following example applies to all columns,
723 -- developers can copy and paste them.
724 IF( (P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID IS NOT NULL) AND (P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID <> FND_API.G_MISS_NUM) )
725 THEN
726 DBMS_SQL.BIND_VARIABLE(p_cur_get_REQUIREMENT_HEADER, ':p_REQUIREMENT_HEADER_ID', P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID);
727 END IF;
728
729 -- Debug Message
730 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Ends');
731 END Bind;
732
733 PROCEDURE Gen_Select(
734 x_select_cl OUT NOCOPY VARCHAR2
735 )
736 IS
737 BEGIN
738 -- Debug Message
739 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Select Begins');
740
741 x_select_cl := 'Select ' ||
742 'CSP_REQUIREMENT_HEADERS.REQUIREMENT_HEADER_ID,' ||
743 'CSP_REQUIREMENT_HEADERS.CREATED_BY,' ||
744 'CSP_REQUIREMENT_HEADERS.CREATION_DATE,' ||
745 'CSP_REQUIREMENT_HEADERS.LAST_UPDATED_BY,' ||
746 'CSP_REQUIREMENT_HEADERS.LAST_UPDATE_DATE,' ||
747 'CSP_REQUIREMENT_HEADERS.LAST_UPDATE_LOGIN,' ||
748 'CSP_REQUIREMENT_HEADERS.OPEN_REQUIREMENT,' ||
749 'CSP_REQUIREMENT_HEADERS.SHIP_TO_LOCATION_ID,' ||
750 'CSP_REQUIREMENT_HEADERS.TASK_ID,' ||
751 'CSP_REQUIREMENT_HEADERS.TASK_ASSIGNMENT_ID,' ||
752 'CSP_REQUIREMENT_HEADERS.SHIPPING_METHOD_CODE,' ||
753 'CSP_REQUIREMENT_HEADERS.NEED_BY_DATE,' ||
754 'CSP_REQUIREMENT_HEADERS.DESTINATION_ORGANIZATION_ID,' ||
755 'CSP_REQUIREMENT_HEADERS.PARTS_DEFINED,' ||
756 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE_CATEGORY,' ||
757 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE1,' ||
758 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE2,' ||
759 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE3,' ||
760 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE4,' ||
761 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE5,' ||
762 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE6,' ||
763 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE7,' ||
764 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE8,' ||
765 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE9,' ||
766 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE10,' ||
767 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE11,' ||
768 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE12,' ||
769 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE13,' ||
770 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE14,' ||
771 'CSP_REQUIREMENT_HEADERS.ATTRIBUTE15,' ||
772 'from CSP_REQUIREMENT_HEADERS';
773 -- Debug Message
774 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Select Ends');
775
776 END Gen_Select;
777
778 PROCEDURE Gen_REQUIREMENT_HEADER_Where(
779 P_REQUIREMENT_HEADER_Rec IN CSP_Requirement_headers_PUB.REQUIREMENT_HEADER_Rec_Type,
780 x_REQUIREMENT_HEADER_where OUT NOCOPY VARCHAR2
781 )
782 IS
783 -- cursors to check if wildcard values '%' and '_' have been passed
784 -- as item values
785 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
786 SELECT INSTR(p_rec_item, '%', 1, 1)
787 FROM DUAL;
788 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
789 SELECT INSTR(p_rec_item, '_', 1, 1)
790 FROM DUAL;
791
792 -- return values from cursors
793 str_csr1 NUMBER;
794 str_csr2 NUMBER;
795 l_operator VARCHAR2(10);
796 BEGIN
797 -- Debug Message
798 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Begins');
799
800 -- There are three examples for each kind of datatype:
801 -- NUMBER, DATE, VARCHAR2.
802 -- Developer can copy and paste the following codes for your own record.
803
804 -- example for NUMBER datatype
805 IF( (P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID IS NOT NULL) AND (P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID <> FND_API.G_MISS_NUM) )
806 THEN
807 IF(x_REQUIREMENT_HEADER_where IS NULL) THEN
808 x_REQUIREMENT_HEADER_where := 'Where';
809 ELSE
810 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || ' AND ';
811 END IF;
812 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || 'P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID = :p_REQUIREMENT_HEADER_ID';
813 END IF;
814
815 -- example for DATE datatype
816 IF( (P_REQUIREMENT_HEADER_Rec.CREATION_DATE IS NOT NULL) AND (P_REQUIREMENT_HEADER_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
817 THEN
818 -- check if item value contains '%' wildcard
819 OPEN c_chk_str1(P_REQUIREMENT_HEADER_Rec.CREATION_DATE);
820 FETCH c_chk_str1 INTO str_csr1;
821 CLOSE c_chk_str1;
822
823 IF(str_csr1 <> 0) THEN
824 l_operator := ' LIKE ';
825 ELSE
826 l_operator := ' = ';
827 END IF;
828
829 -- check if item value contains '_' wildcard
830 OPEN c_chk_str2(P_REQUIREMENT_HEADER_Rec.CREATION_DATE);
831 FETCH c_chk_str2 INTO str_csr2;
832 CLOSE c_chk_str2;
833
834 IF(str_csr2 <> 0) THEN
835 l_operator := ' LIKE ';
836 ELSE
837 l_operator := ' = ';
838 END IF;
839
840 IF(x_REQUIREMENT_HEADER_where IS NULL) THEN
841 x_REQUIREMENT_HEADER_where := 'Where ';
842 ELSE
843 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || ' AND ';
844 END IF;
845 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || 'P_REQUIREMENT_HEADER_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
846 END IF;
847
848 -- example for VARCHAR2 datatype
849 IF( (P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT IS NOT NULL) AND (P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT <> FND_API.G_MISS_CHAR) )
850 THEN
851 -- check if item value contains '%' wildcard
852 OPEN c_chk_str1(P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT);
853 FETCH c_chk_str1 INTO str_csr1;
854 CLOSE c_chk_str1;
855
856 IF(str_csr1 <> 0) THEN
857 l_operator := ' LIKE ';
858 ELSE
859 l_operator := ' = ';
860 END IF;
861
862 -- check if item value contains '_' wildcard
863 OPEN c_chk_str2(P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT);
864 FETCH c_chk_str2 INTO str_csr2;
865 CLOSE c_chk_str2;
866
867 IF(str_csr2 <> 0) THEN
868 l_operator := ' LIKE ';
869 ELSE
870 l_operator := ' = ';
871 END IF;
872
873 IF(x_REQUIREMENT_HEADER_where IS NULL) THEN
874 x_REQUIREMENT_HEADER_where := 'Where ';
875 ELSE
876 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || ' AND ';
877 END IF;
878 x_REQUIREMENT_HEADER_where := x_REQUIREMENT_HEADER_where || 'P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT ' || l_operator || ' :p_OPEN_REQUIREMENT';
879 END IF;
880
881 -- Add more IF statements for each column below
882
883 -- Debug Message
884 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Ends');
885
886 END Gen_REQUIREMENT_HEADER_Where;
887
888 -- Item-level validation procedures
889 PROCEDURE Validate_REQUIREMENT_HEADER_ID (
890 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
891 P_Validation_mode IN VARCHAR2,
892 P_REQUIREMENT_HEADER_ID IN NUMBER,
893 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
894 X_Return_Status OUT NOCOPY VARCHAR2,
895 X_Msg_Count OUT NOCOPY NUMBER,
896 X_Msg_Data OUT NOCOPY VARCHAR2
897 )
898 IS
899 BEGIN
900
901 -- Initialize message list if p_init_msg_list is set to TRUE.
902 IF FND_API.to_Boolean( p_init_msg_list )
903 THEN
904 FND_MSG_PUB.initialize;
905 END IF;
906
907
908 -- Initialize API return status to SUCCESS
909 x_return_status := FND_API.G_RET_STS_SUCCESS;
910
911 -- validate NOT NULL column
912 IF(p_REQUIREMENT_HEADER_ID is NULL)
913 THEN
914 JTF_UTILITY_PVT.Debug_Message('ERROR', 'Private requirement_headers API: -Violate NOT NULL constraint(REQUIREMENT_HEADER_ID)');
915 x_return_status := FND_API.G_RET_STS_ERROR;
916 END IF;
917
918 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
919 THEN
920 -- Hint: Validate data
921 -- IF p_REQUIREMENT_HEADER_ID is not NULL and p_REQUIREMENT_HEADER_ID <> G_MISS_CHAR
922 -- verify if data is valid
923 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
924 NULL;
925 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
926 THEN
927 -- Hint: Validate data
928 -- IF p_REQUIREMENT_HEADER_ID <> G_MISS_CHAR
929 -- verify if data is valid
930 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
931 NULL;
932 END IF;
933
934 -- Standard call to get message count and if count is 1, get message info.
935 FND_MSG_PUB.Count_And_Get
936 ( p_count => x_msg_count,
937 p_data => x_msg_data
938 );
939
940 END Validate_REQUIREMENT_HEADER_ID;
941
942
943 PROCEDURE Validate_OPEN_REQUIREMENT (
944 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
945 P_Validation_mode IN VARCHAR2,
946 P_OPEN_REQUIREMENT IN VARCHAR2,
947 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
948 X_Return_Status OUT NOCOPY VARCHAR2,
949 X_Msg_Count OUT NOCOPY NUMBER,
950 X_Msg_Data OUT NOCOPY VARCHAR2
951 )
952 IS
953 BEGIN
954
955 -- Initialize message list if p_init_msg_list is set to TRUE.
956 IF FND_API.to_Boolean( p_init_msg_list )
957 THEN
958 FND_MSG_PUB.initialize;
959 END IF;
960
961
962 -- Initialize API return status to SUCCESS
963 x_return_status := FND_API.G_RET_STS_SUCCESS;
964
965 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
966 THEN
967 -- Hint: Validate data
968 -- IF p_OPEN_REQUIREMENT is not NULL and p_OPEN_REQUIREMENT <> G_MISS_CHAR
969 -- verify if data is valid
970 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
971 NULL;
972 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
973 THEN
974 -- Hint: Validate data
975 -- IF p_OPEN_REQUIREMENT <> G_MISS_CHAR
976 -- verify if data is valid
977 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
978 NULL;
979 END IF;
980
981 -- Standard call to get message count and if count is 1, get message info.
982 FND_MSG_PUB.Count_And_Get
983 ( p_count => x_msg_count,
984 p_data => x_msg_data
985 );
986
987 END Validate_OPEN_REQUIREMENT;
988
989
990 PROCEDURE Validate_SHIP_TO_LOCATION_ID (
991 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
992 P_Validation_mode IN VARCHAR2,
993 P_SHIP_TO_LOCATION_ID IN NUMBER,
994 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
995 X_Return_Status OUT NOCOPY VARCHAR2,
996 X_Msg_Count OUT NOCOPY NUMBER,
997 X_Msg_Data OUT NOCOPY VARCHAR2
998 )
999 IS
1000 BEGIN
1001
1002 -- Initialize message list if p_init_msg_list is set to TRUE.
1003 IF FND_API.to_Boolean( p_init_msg_list )
1004 THEN
1005 FND_MSG_PUB.initialize;
1006 END IF;
1007
1008
1009 -- Initialize API return status to SUCCESS
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011
1012 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1013 THEN
1014 -- Hint: Validate data
1015 -- IF p_SHIP_TO_LOCATION_ID is not NULL and p_SHIP_TO_LOCATION_ID <> G_MISS_CHAR
1016 -- verify if data is valid
1017 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1018 NULL;
1019 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1020 THEN
1021 -- Hint: Validate data
1022 -- IF p_SHIP_TO_LOCATION_ID <> G_MISS_CHAR
1023 -- verify if data is valid
1024 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1025 NULL;
1026 END IF;
1027
1028 -- Standard call to get message count and if count is 1, get message info.
1029 FND_MSG_PUB.Count_And_Get
1030 ( p_count => x_msg_count,
1031 p_data => x_msg_data
1032 );
1033
1034 END Validate_SHIP_TO_LOCATION_ID;
1035
1036
1037 PROCEDURE Validate_TASK_ID (
1038 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1039 P_Validation_mode IN VARCHAR2,
1040 P_TASK_ID IN NUMBER,
1041 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1042 X_Return_Status OUT NOCOPY VARCHAR2,
1043 X_Msg_Count OUT NOCOPY NUMBER,
1044 X_Msg_Data OUT NOCOPY VARCHAR2
1045 )
1046 IS
1047 BEGIN
1048
1049 -- Initialize message list if p_init_msg_list is set to TRUE.
1050 IF FND_API.to_Boolean( p_init_msg_list )
1051 THEN
1052 FND_MSG_PUB.initialize;
1053 END IF;
1054
1055
1056 -- Initialize API return status to SUCCESS
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1060 THEN
1061 -- Hint: Validate data
1062 -- IF p_TASK_ID is not NULL and p_TASK_ID <> G_MISS_CHAR
1063 -- verify if data is valid
1064 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1065 NULL;
1066 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1067 THEN
1068 -- Hint: Validate data
1069 -- IF p_TASK_ID <> G_MISS_CHAR
1070 -- verify if data is valid
1071 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1072 NULL;
1073 END IF;
1074
1075 -- Standard call to get message count and if count is 1, get message info.
1076 FND_MSG_PUB.Count_And_Get
1077 ( p_count => x_msg_count,
1078 p_data => x_msg_data
1079 );
1080
1081 END Validate_TASK_ID;
1082
1083
1084 PROCEDURE Validate_TASK_ASSIGNMENT_ID (
1085 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1086 P_Validation_mode IN VARCHAR2,
1087 P_TASK_ASSIGNMENT_ID IN NUMBER,
1088 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1089 X_Return_Status OUT NOCOPY VARCHAR2,
1090 X_Msg_Count OUT NOCOPY NUMBER,
1091 X_Msg_Data OUT NOCOPY VARCHAR2
1092 )
1093 IS
1094 BEGIN
1095
1096 -- Initialize message list if p_init_msg_list is set to TRUE.
1097 IF FND_API.to_Boolean( p_init_msg_list )
1098 THEN
1099 FND_MSG_PUB.initialize;
1100 END IF;
1101
1102
1103 -- Initialize API return status to SUCCESS
1104 x_return_status := FND_API.G_RET_STS_SUCCESS;
1105
1106 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1107 THEN
1108 -- Hint: Validate data
1109 -- IF p_TASK_ASSIGNMENT_ID is not NULL and p_TASK_ASSIGNMENT_ID <> G_MISS_CHAR
1110 -- verify if data is valid
1111 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1112 NULL;
1113 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1114 THEN
1115 -- Hint: Validate data
1116 -- IF p_TASK_ASSIGNMENT_ID <> G_MISS_CHAR
1117 -- verify if data is valid
1118 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1119 NULL;
1120 END IF;
1121
1122 -- Standard call to get message count and if count is 1, get message info.
1123 FND_MSG_PUB.Count_And_Get
1124 ( p_count => x_msg_count,
1125 p_data => x_msg_data
1126 );
1127
1128 END Validate_TASK_ASSIGNMENT_ID;
1129
1130
1131 PROCEDURE Validate_SHIPPING_METHOD_CODE (
1132 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1133 P_Validation_mode IN VARCHAR2,
1134 P_SHIPPING_METHOD_CODE IN VARCHAR2,
1135 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1136 X_Return_Status OUT NOCOPY VARCHAR2,
1137 X_Msg_Count OUT NOCOPY NUMBER,
1138 X_Msg_Data OUT NOCOPY VARCHAR2
1139 )
1140 IS
1141 BEGIN
1142
1143 -- Initialize message list if p_init_msg_list is set to TRUE.
1144 IF FND_API.to_Boolean( p_init_msg_list )
1145 THEN
1146 FND_MSG_PUB.initialize;
1147 END IF;
1148
1149
1150 -- Initialize API return status to SUCCESS
1151 x_return_status := FND_API.G_RET_STS_SUCCESS;
1152
1153 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1154 THEN
1155 -- Hint: Validate data
1156 -- IF p_SHIPPING_METHOD_CODE is not NULL and p_SHIPPING_METHOD_CODE <> G_MISS_CHAR
1157 -- verify if data is valid
1158 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1159 NULL;
1160 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1161 THEN
1162 -- Hint: Validate data
1163 -- IF p_SHIPPING_METHOD_CODE <> G_MISS_CHAR
1164 -- verify if data is valid
1165 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1166 NULL;
1167 END IF;
1168
1169 -- Standard call to get message count and if count is 1, get message info.
1170 FND_MSG_PUB.Count_And_Get
1171 ( p_count => x_msg_count,
1172 p_data => x_msg_data
1173 );
1174
1175 END Validate_SHIPPING_METHOD_CODE;
1176
1177
1178 PROCEDURE Validate_NEED_BY_DATE (
1179 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1180 P_Validation_mode IN VARCHAR2,
1181 P_NEED_BY_DATE IN DATE,
1182 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1183 X_Return_Status OUT NOCOPY VARCHAR2,
1184 X_Msg_Count OUT NOCOPY NUMBER,
1185 X_Msg_Data OUT NOCOPY VARCHAR2
1186 )
1187 IS
1188 BEGIN
1189
1190 -- Initialize message list if p_init_msg_list is set to TRUE.
1191 IF FND_API.to_Boolean( p_init_msg_list )
1192 THEN
1193 FND_MSG_PUB.initialize;
1194 END IF;
1195
1196
1197 -- Initialize API return status to SUCCESS
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199
1200 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1201 THEN
1202 -- Hint: Validate data
1203 -- IF p_NEED_BY_DATE is not NULL and p_NEED_BY_DATE <> G_MISS_CHAR
1204 -- verify if data is valid
1205 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1206 NULL;
1207 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1208 THEN
1209 -- Hint: Validate data
1210 -- IF p_NEED_BY_DATE <> G_MISS_CHAR
1211 -- verify if data is valid
1212 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1213 NULL;
1214 END IF;
1215
1216 -- Standard call to get message count and if count is 1, get message info.
1217 FND_MSG_PUB.Count_And_Get
1218 ( p_count => x_msg_count,
1219 p_data => x_msg_data
1220 );
1221
1222 END Validate_NEED_BY_DATE;
1223
1224
1225 PROCEDURE Validate_DEST_ORGANIZATION_ID (
1226 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1227 P_Validation_mode IN VARCHAR2,
1228 P_DESTINATION_ORGANIZATION_ID IN NUMBER,
1229 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1230 X_Return_Status OUT NOCOPY VARCHAR2,
1231 X_Msg_Count OUT NOCOPY NUMBER,
1232 X_Msg_Data OUT NOCOPY VARCHAR2
1233 )
1234 IS
1235 BEGIN
1236
1237 -- Initialize message list if p_init_msg_list is set to TRUE.
1238 IF FND_API.to_Boolean( p_init_msg_list )
1239 THEN
1240 FND_MSG_PUB.initialize;
1241 END IF;
1242
1243
1244 -- Initialize API return status to SUCCESS
1245 x_return_status := FND_API.G_RET_STS_SUCCESS;
1246
1247 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1248 THEN
1249 -- Hint: Validate data
1250 -- IF p_DESTINATION_ORGANIZATION_ID is not NULL and p_DESTINATION_ORGANIZATION_ID <> G_MISS_CHAR
1251 -- verify if data is valid
1252 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1253 NULL;
1254 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1255 THEN
1256 -- Hint: Validate data
1257 -- IF p_DESTINATION_ORGANIZATION_ID <> G_MISS_CHAR
1258 -- verify if data is valid
1259 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1260 NULL;
1261 END IF;
1262
1263 -- Standard call to get message count and if count is 1, get message info.
1264 FND_MSG_PUB.Count_And_Get
1265 ( p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268
1269 END Validate_DEST_ORGANIZATION_ID;
1270
1271
1272 PROCEDURE Validate_PARTS_DEFINED (
1273 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1274 P_Validation_mode IN VARCHAR2,
1275 P_PARTS_DEFINED IN VARCHAR2,
1276 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1277 X_Return_Status OUT NOCOPY VARCHAR2,
1278 X_Msg_Count OUT NOCOPY NUMBER,
1279 X_Msg_Data OUT NOCOPY VARCHAR2
1280 )
1281 IS
1282 BEGIN
1283
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list )
1286 THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290
1291 -- Initialize API return status to SUCCESS
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 IF(p_validation_mode = JTF_UTILITY_PVT.G_CREATE)
1295 THEN
1296 -- Hint: Validate data
1297 -- IF p_PARTS_DEFINED is not NULL and p_PARTS_DEFINED <> G_MISS_CHAR
1298 -- verify if data is valid
1299 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1300 NULL;
1301 ELSIF(p_validation_mode = JTF_UTILITY_PVT.G_UPDATE)
1302 THEN
1303 -- Hint: Validate data
1304 -- IF p_PARTS_DEFINED <> G_MISS_CHAR
1305 -- verify if data is valid
1306 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1307 NULL;
1308 END IF;
1309
1310 -- Standard call to get message count and if count is 1, get message info.
1311 FND_MSG_PUB.Count_And_Get
1312 ( p_count => x_msg_count,
1313 p_data => x_msg_data
1314 );
1315
1316 END Validate_PARTS_DEFINED;
1317
1318
1319 -- Hint: inter-field level validation can be added here.
1320 -- Hint: If p_validation_mode = JTF_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
1321 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1322 -- stored in database table.
1323 PROCEDURE Validate_RQMT_HEADER_rec(
1324 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1325 P_Validation_mode IN VARCHAR2,
1326 P_REQUIREMENT_HEADER_Rec IN REQUIREMENT_HEADER_Rec_Type,
1327 X_Return_Status OUT NOCOPY VARCHAR2,
1328 X_Msg_Count OUT NOCOPY NUMBER,
1329 X_Msg_Data OUT NOCOPY VARCHAR2
1330 )
1331 IS
1332 BEGIN
1333
1334 -- Initialize message list if p_init_msg_list is set to TRUE.
1335 IF FND_API.to_Boolean( p_init_msg_list )
1336 THEN
1337 FND_MSG_PUB.initialize;
1338 END IF;
1339
1340
1341 -- Initialize API return status to SUCCESS
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 -- Hint: Validate data
1345 -- If data not valid
1346 -- THEN
1347 -- x_return_status := FND_API.G_RET_STS_ERROR;
1348
1349 -- Debug Message
1350 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'API_INVALID_RECORD');
1351
1352 -- Standard call to get message count and if count is 1, get message info.
1353 FND_MSG_PUB.Count_And_Get
1354 ( p_count => x_msg_count,
1355 p_data => x_msg_data
1356 );
1357
1358 END Validate_RQMT_HEADER_Rec;
1359
1360 PROCEDURE Validate_requirement_headers(
1361 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1362 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1363 P_Validation_mode IN VARCHAR2,
1364 P_REQUIREMENT_HEADER_Rec IN REQUIREMENT_HEADER_Rec_Type,
1365 X_Return_Status OUT NOCOPY VARCHAR2,
1366 X_Msg_Count OUT NOCOPY NUMBER,
1367 X_Msg_Data OUT NOCOPY VARCHAR2
1368 )
1369 IS
1370 l_api_name CONSTANT VARCHAR2(30) := 'Validate_requirement_headers';
1371 BEGIN
1372
1373 -- Debug Message
1374 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1375
1376
1377 -- Initialize API return status to SUCCESS
1378 x_return_status := FND_API.G_RET_STS_SUCCESS;
1379
1380 IF (p_validation_level >= JTF_UTILITY_PVT.G_VALID_LEVEL_ITEM) THEN
1381 -- Hint: We provide validation procedure for every column. Developer should delete
1382 -- unnecessary validation procedures.
1383 Validate_REQUIREMENT_HEADER_ID(
1384 p_init_msg_list => FND_API.G_FALSE,
1385 p_validation_mode => p_validation_mode,
1386 p_REQUIREMENT_HEADER_ID => P_REQUIREMENT_HEADER_Rec.REQUIREMENT_HEADER_ID,
1387 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1388 x_return_status => x_return_status,
1389 x_msg_count => x_msg_count,
1390 x_msg_data => x_msg_data);
1391 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1392 raise FND_API.G_EXC_ERROR;
1393 END IF;
1394
1395 Validate_OPEN_REQUIREMENT(
1396 p_init_msg_list => FND_API.G_FALSE,
1397 p_validation_mode => p_validation_mode,
1398 p_OPEN_REQUIREMENT => P_REQUIREMENT_HEADER_Rec.OPEN_REQUIREMENT,
1399 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1400 x_return_status => x_return_status,
1401 x_msg_count => x_msg_count,
1402 x_msg_data => x_msg_data);
1403 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1404 raise FND_API.G_EXC_ERROR;
1405 END IF;
1406
1407 Validate_SHIP_TO_LOCATION_ID(
1408 p_init_msg_list => FND_API.G_FALSE,
1409 p_validation_mode => p_validation_mode,
1410 p_SHIP_TO_LOCATION_ID => P_REQUIREMENT_HEADER_Rec.SHIP_TO_LOCATION_ID,
1411 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1412 x_return_status => x_return_status,
1413 x_msg_count => x_msg_count,
1414 x_msg_data => x_msg_data);
1415 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1416 raise FND_API.G_EXC_ERROR;
1417 END IF;
1418
1419 Validate_TASK_ID(
1420 p_init_msg_list => FND_API.G_FALSE,
1421 p_validation_mode => p_validation_mode,
1422 p_TASK_ID => P_REQUIREMENT_HEADER_Rec.TASK_ID,
1423 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1424 x_return_status => x_return_status,
1425 x_msg_count => x_msg_count,
1426 x_msg_data => x_msg_data);
1427 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1428 raise FND_API.G_EXC_ERROR;
1429 END IF;
1430
1431 Validate_TASK_ASSIGNMENT_ID(
1432 p_init_msg_list => FND_API.G_FALSE,
1433 p_validation_mode => p_validation_mode,
1434 p_TASK_ASSIGNMENT_ID => P_REQUIREMENT_HEADER_Rec.TASK_ASSIGNMENT_ID,
1435 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1436 x_return_status => x_return_status,
1437 x_msg_count => x_msg_count,
1438 x_msg_data => x_msg_data);
1439 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1440 raise FND_API.G_EXC_ERROR;
1441 END IF;
1442
1443 Validate_SHIPPING_METHOD_CODE(
1444 p_init_msg_list => FND_API.G_FALSE,
1445 p_validation_mode => p_validation_mode,
1446 p_SHIPPING_METHOD_CODE => P_REQUIREMENT_HEADER_Rec.SHIPPING_METHOD_CODE,
1447 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1448 x_return_status => x_return_status,
1449 x_msg_count => x_msg_count,
1450 x_msg_data => x_msg_data);
1451 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1452 raise FND_API.G_EXC_ERROR;
1453 END IF;
1454
1455 Validate_NEED_BY_DATE(
1456 p_init_msg_list => FND_API.G_FALSE,
1457 p_validation_mode => p_validation_mode,
1458 p_NEED_BY_DATE => P_REQUIREMENT_HEADER_Rec.NEED_BY_DATE,
1459 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1460 x_return_status => x_return_status,
1461 x_msg_count => x_msg_count,
1462 x_msg_data => x_msg_data);
1463 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1464 raise FND_API.G_EXC_ERROR;
1465 END IF;
1466
1467 Validate_DEST_ORGANIZATION_ID(
1468 p_init_msg_list => FND_API.G_FALSE,
1469 p_validation_mode => p_validation_mode,
1470 p_DESTINATION_ORGANIZATION_ID => P_REQUIREMENT_HEADER_Rec.DESTINATION_ORGANIZATION_ID,
1471 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1472 x_return_status => x_return_status,
1473 x_msg_count => x_msg_count,
1474 x_msg_data => x_msg_data);
1475 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1476 raise FND_API.G_EXC_ERROR;
1477 END IF;
1478
1479 Validate_PARTS_DEFINED(
1480 p_init_msg_list => FND_API.G_FALSE,
1481 p_validation_mode => p_validation_mode,
1482 p_PARTS_DEFINED => P_REQUIREMENT_HEADER_Rec.PARTS_DEFINED,
1483 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1484 x_return_status => x_return_status,
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data);
1487 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1488 raise FND_API.G_EXC_ERROR;
1489 END IF;
1490
1491 END IF;
1492
1493 IF (p_validation_level >= JTF_UTILITY_PVT.G_VALID_LEVEL_RECORD) THEN
1494 -- Hint: Inter-field level validation can be added here
1495 -- invoke record level validation procedures
1496 Validate_RQMT_HEADER_Rec(
1497 p_init_msg_list => FND_API.G_FALSE,
1498 p_validation_mode => p_validation_mode,
1499 P_REQUIREMENT_HEADER_Rec => P_REQUIREMENT_HEADER_Rec,
1500 x_return_status => x_return_status,
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data);
1503
1504 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1505 raise FND_API.G_EXC_ERROR;
1506 END IF;
1507 END IF;
1508
1509 IF (p_validation_level >= JTF_UTILITY_PVT.G_VALID_LEVEL_INTER_RECORD) THEN
1510 -- invoke inter-record level validation procedures
1511 NULL;
1512 END IF;
1513
1514 IF (p_validation_level >= JTF_UTILITY_PVT.G_VALID_LEVEL_INTER_ENTITY) THEN
1515 -- invoke inter-entity level validation procedures
1516 NULL;
1517 END IF;
1518
1519
1520 -- Debug Message
1521 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
1522
1523 END Validate_requirement_headers; */
1524
1525 End CSP_Requirement_headers_PVT;