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