1 PACKAGE BODY CSP_PRODUCT_TASK_PVT as
2 /* $Header: cspvptab.pls 115.3 2003/05/02 00:25:32 phegde noship $ */
3 -- Start of Comments
4 -- Package name : CSP_PRODUCT_TASK_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PRODUCT_TASK_PVT';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvptab.pls';
11 -- Hint: Primary key needs to be returned.
12 PROCEDURE Create_product_task(
13 P_Api_Version_Number IN NUMBER,
14 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
15 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 P_PROD_TASK_Rec IN PROD_TASK_Rec_Type := G_MISS_PROD_TASK_REC,
18 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
19 X_PRODUCT_TASK_ID OUT NOCOPY NUMBER,
20 X_Return_Status OUT NOCOPY VARCHAR2,
21 X_Msg_Count OUT NOCOPY NUMBER,
22 X_Msg_Data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'Create_product_task';
26 l_api_version_number CONSTANT NUMBER := 1.0;
27 l_return_status_full VARCHAR2(1);
28 l_access_flag VARCHAR2(1);
29 BEGIN
30 -- Standard Start of API savepoint
31 SAVEPOINT CREATE_PRODUCT_TASK_PVT;
32 -- Standard call to check for call compatibility.
33 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
34 p_api_version_number,
35 l_api_name,
36 G_PKG_NAME)
37 THEN
38 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
39 END IF;
40 -- Initialize message list if p_init_msg_list is set to TRUE.
41 IF FND_API.to_Boolean( p_init_msg_list )
42 THEN
43 FND_MSG_PUB.initialize;
44 END IF;
45 -- Initialize API return status to SUCCESS
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 --
48 -- API body
49 --
50 -- ******************************************************************
51 -- Validate Environment
52 -- ******************************************************************
53 IF FND_GLOBAL.User_Id IS NULL
54 THEN
55 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
56 THEN
57 FND_MESSAGE.Set_Name('CSP', 'UT_CANNOT_GET_PROFILE_VALUE');
58 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
59 FND_MSG_PUB.ADD;
60 END IF;
61 RAISE FND_API.G_EXC_ERROR;
62 END IF;
63 -- Invoke validation procedures
64 Validate_product_task(
65 p_init_msg_list => FND_API.G_FALSE,
66 p_validation_level => p_validation_level,
67 p_validation_mode => JTF_PLSQL_API.G_CREATE,
68 P_PROD_TASK_Rec => P_PROD_TASK_Rec,
69 x_return_status => x_return_status,
70 x_msg_count => x_msg_count,
71 x_msg_data => x_msg_data);
72 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
73 RAISE FND_API.G_EXC_ERROR;
74 END IF;
75 -- Invoke table handler(CSP_PRODUCT_TASKS_PKG.Insert_Row)
76 CSP_PRODUCT_TASKS_PKG.Insert_Row(
77 px_PRODUCT_TASK_ID => x_PRODUCT_TASK_ID,
78 p_PRODUCT_ID => p_PROD_TASK_rec.PRODUCT_ID,
79 p_TASK_TEMPLATE_ID => p_PROD_TASK_rec.TASK_TEMPLATE_ID,
80 p_AUTO_MANUAL => p_PROD_TASK_rec.AUTO_MANUAL,
81 p_ACTUAL_TIMES_USED => p_PROD_TASK_rec.ACTUAL_TIMES_USED,
82 p_TASK_PERCENTAGE => p_PROD_TASK_rec.TASK_PERCENTAGE,
83 p_ATTRIBUTE_CATEGORY => p_PROD_TASK_rec.ATTRIBUTE_CATEGORY,
84 p_ATTRIBUTE1 => p_PROD_TASK_rec.ATTRIBUTE1,
85 p_ATTRIBUTE2 => p_PROD_TASK_rec.ATTRIBUTE2,
86 p_ATTRIBUTE3 => p_PROD_TASK_rec.ATTRIBUTE3,
87 p_ATTRIBUTE4 => p_PROD_TASK_rec.ATTRIBUTE4,
88 p_ATTRIBUTE5 => p_PROD_TASK_rec.ATTRIBUTE5,
89 p_ATTRIBUTE6 => p_PROD_TASK_rec.ATTRIBUTE6,
90 p_ATTRIBUTE7 => p_PROD_TASK_rec.ATTRIBUTE7,
91 p_ATTRIBUTE8 => p_PROD_TASK_rec.ATTRIBUTE8,
92 p_ATTRIBUTE9 => p_PROD_TASK_rec.ATTRIBUTE9,
93 p_ATTRIBUTE10 => p_PROD_TASK_rec.ATTRIBUTE10,
94 p_ATTRIBUTE11 => p_PROD_TASK_rec.ATTRIBUTE11,
95 p_ATTRIBUTE12 => p_PROD_TASK_rec.ATTRIBUTE12,
96 p_ATTRIBUTE13 => p_PROD_TASK_rec.ATTRIBUTE13,
97 p_ATTRIBUTE14 => p_PROD_TASK_rec.ATTRIBUTE14,
98 p_ATTRIBUTE15 => p_PROD_TASK_rec.ATTRIBUTE15,
99 p_CREATED_BY => FND_GLOBAL.USER_ID,
100 p_CREATION_DATE => SYSDATE,
101 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
102 p_LAST_UPDATE_DATE => SYSDATE,
103 p_LAST_UPDATE_LOGIN => p_PROD_TASK_rec.LAST_UPDATE_LOGIN);
104 -- Hint: Primary key should be returned.
105 -- x_PRODUCT_TASK_ID := px_PRODUCT_TASK_ID;
106 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109 --
110 -- End of API body
111 --
112 -- Standard check for p_commit
113 IF FND_API.to_Boolean( p_commit )
114 THEN
115 COMMIT WORK;
116 END IF;
117 -- Standard call to get message count and if count is 1, get message info.
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count,
120 p_data => x_msg_data
121 );
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
125 P_API_NAME => L_API_NAME
126 ,P_PKG_NAME => G_PKG_NAME
127 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
128 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
129 ,X_MSG_COUNT => X_MSG_COUNT
130 ,X_MSG_DATA => X_MSG_DATA
131 ,X_RETURN_STATUS => X_RETURN_STATUS);
132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
133 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
134 P_API_NAME => L_API_NAME
135 ,P_PKG_NAME => G_PKG_NAME
136 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
137 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
138 ,X_MSG_COUNT => X_MSG_COUNT
139 ,X_MSG_DATA => X_MSG_DATA
140 ,X_RETURN_STATUS => X_RETURN_STATUS);
141 WHEN OTHERS THEN
142 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
143 P_API_NAME => L_API_NAME
144 ,P_PKG_NAME => G_PKG_NAME
145 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
146 ,P_SQLCODE => SQLCODE
147 ,P_SQLERRM => SQLERRM
148 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
149 ,X_MSG_COUNT => X_MSG_COUNT
150 ,X_MSG_DATA => X_MSG_DATA
151 ,X_RETURN_STATUS => X_RETURN_STATUS);
152 End Create_product_task;
153 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
154 PROCEDURE Update_product_task(
155 P_Api_Version_Number IN NUMBER,
156 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
157 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
158 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
159 P_PROD_TASK_Rec IN PROD_TASK_Rec_Type,
160 X_Return_Status OUT NOCOPY VARCHAR2,
161 X_Msg_Count OUT NOCOPY NUMBER,
162 X_Msg_Data OUT NOCOPY VARCHAR2
163 )
164 IS
165
166 Cursor C_Get_product_task(L_PRODUCT_TASK_ID Number) IS
167 Select rowid,
168 PRODUCT_TASK_ID,
169 PRODUCT_ID,
170 TASK_TEMPLATE_ID,
171 AUTO_MANUAL,
172 ACTUAL_TIMES_USED,
173 TASK_PERCENTAGE,
174 ATTRIBUTE_CATEGORY,
175 ATTRIBUTE1,
176 ATTRIBUTE2,
177 ATTRIBUTE3,
178 ATTRIBUTE4,
179 ATTRIBUTE5,
180 ATTRIBUTE6,
181 ATTRIBUTE7,
182 ATTRIBUTE8,
183 ATTRIBUTE9,
184 ATTRIBUTE10,
185 ATTRIBUTE11,
186 ATTRIBUTE12,
187 ATTRIBUTE13,
188 ATTRIBUTE14,
189 ATTRIBUTE15,
190 CREATED_BY,
191 CREATION_DATE,
192 LAST_UPDATED_BY,
193 LAST_UPDATE_DATE,
194 LAST_UPDATE_LOGIN
195 From CSP_PRODUCT_TASKS
196 WHERE PRODUCT_TASK_ID = L_PRODUCT_TASK_ID
197 -- Hint: Developer need to provide Where clause
198 For Update NOWAIT;
199
200 l_api_name CONSTANT VARCHAR2(30) := 'Update_product_task';
201 l_api_version_number CONSTANT NUMBER := 1.0;
202 -- Local Variables
203 l_ref_PROD_TASK_rec CSP_product_task_PVT.PROD_TASK_Rec_Type;
204 l_tar_PROD_TASK_rec CSP_product_task_PVT.PROD_TASK_Rec_Type := P_PROD_TASK_Rec;
205 l_rowid ROWID;
206 BEGIN
207 -- Standard Start of API savepoint
208 SAVEPOINT UPDATE_PRODUCT_TASK_PVT;
209 -- Standard call to check for call compatibility.
210 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
211 p_api_version_number,
212 l_api_name,
213 G_PKG_NAME)
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217
218 -- Initialize message list if p_init_msg_list is set to TRUE.
219 IF FND_API.to_Boolean( p_init_msg_list )
220 THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223 -- Initialize API return status to SUCCESS
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225 --
226 -- Api body
227 --
228 /*
229 Open C_Get_product_task( l_tar_PROD_TASK_rec.PRODUCT_TASK_ID);
230 Fetch C_Get_product_task into
231 l_rowid,
232 l_ref_PROD_TASK_rec.PRODUCT_TASK_ID,
233 l_ref_PROD_TASK_rec.PRODUCT_ID,
234 l_ref_PROD_TASK_rec.TASK_TEMPLATE_ID,
235 l_ref_PROD_TASK_rec.AUTO_MANUAL,
236 l_ref_PROD_TASK_rec.ACTUAL_TIMES_USED,
237 l_ref_PROD_TASK_rec.TASK_PERCENTAGE,
238 l_ref_PROD_TASK_rec.ATTRIBUTE_CATEGORY,
239 l_ref_PROD_TASK_rec.ATTRIBUTE1,
240 l_ref_PROD_TASK_rec.ATTRIBUTE2,
241 l_ref_PROD_TASK_rec.ATTRIBUTE3,
242 l_ref_PROD_TASK_rec.ATTRIBUTE4,
243 l_ref_PROD_TASK_rec.ATTRIBUTE5,
244 l_ref_PROD_TASK_rec.ATTRIBUTE6,
245 l_ref_PROD_TASK_rec.ATTRIBUTE7,
246 l_ref_PROD_TASK_rec.ATTRIBUTE8,
247 l_ref_PROD_TASK_rec.ATTRIBUTE9,
248 l_ref_PROD_TASK_rec.ATTRIBUTE10,
249 l_ref_PROD_TASK_rec.ATTRIBUTE11,
250 l_ref_PROD_TASK_rec.ATTRIBUTE12,
251 l_ref_PROD_TASK_rec.ATTRIBUTE13,
252 l_ref_PROD_TASK_rec.ATTRIBUTE14,
253 l_ref_PROD_TASK_rec.ATTRIBUTE15,
254 l_ref_PROD_TASK_rec.CREATED_BY,
255 l_ref_PROD_TASK_rec.CREATION_DATE,
256 l_ref_PROD_TASK_rec.LAST_UPDATED_BY,
257 l_ref_PROD_TASK_rec.LAST_UPDATE_DATE,
258 l_ref_PROD_TASK_rec.LAST_UPDATE_LOGIN;
259
260 If ( C_Get_product_task%NOTFOUND) Then
261 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
262 THEN
263 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_UPDATE_TARGET');
264 FND_MESSAGE.Set_Token ('INFO', 'product_task', FALSE);
265 FND_MSG_PUB.Add;
266 END IF;
267 Close C_Get_product_task;
268 raise FND_API.G_EXC_ERROR;
269 END IF;
270 Close C_Get_product_task;
271
272 If (l_tar_PROD_TASK_rec.last_update_date is NULL or
273 l_tar_PROD_TASK_rec.last_update_date = FND_API.G_MISS_Date ) Then
274 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
275 THEN
276 DBMS_OUTPUT.PUT_LINE('UPDATE DATE2 '||l_tar_PROD_TASK_rec.last_update_date);
277 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_ID');
278 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
279 FND_MSG_PUB.ADD;
280 END IF;
281 raise FND_API.G_EXC_ERROR;
282 End if;
283
284 -- Check Whether record has been changed by someone else
285 If (l_tar_PROD_TASK_rec.last_update_date <> l_ref_PROD_TASK_rec.last_update_date) Then
286 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
287 THEN
288 FND_MESSAGE.Set_Name('CSP', 'API_RECORD_CHANGED');
289 FND_MESSAGE.Set_Token('INFO', 'product_task', FALSE);
290 FND_MSG_PUB.ADD;
291 END IF;
292 raise FND_API.G_EXC_ERROR;
293 End if;
294
295 */
296
297 -- Invoke validation procedures
298 Validate_product_task(
299 p_init_msg_list => FND_API.G_FALSE,
300 p_validation_level => p_validation_level,
301 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
302 P_PROD_TASK_Rec => P_PROD_TASK_Rec,
303 x_return_status => x_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data);
306 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 -- Hint: Add corresponding Master-Detail business logic here if necessary.
310 -- Invoke table handler(CSP_PRODUCT_TASKS_PKG.Update_Row)
311
312 CSP_PRODUCT_TASKS_PKG.Update_Row(
313 p_PRODUCT_TASK_ID => p_PROD_TASK_rec.PRODUCT_TASK_ID,
314 p_PRODUCT_ID => p_PROD_TASK_rec.PRODUCT_ID,
315 p_TASK_TEMPLATE_ID => p_PROD_TASK_rec.TASK_TEMPLATE_ID,
316 p_AUTO_MANUAL => p_PROD_TASK_rec.AUTO_MANUAL,
317 p_ACTUAL_TIMES_USED => p_PROD_TASK_rec.ACTUAL_TIMES_USED,
318 p_TASK_PERCENTAGE => p_PROD_TASK_rec.TASK_PERCENTAGE,
319 p_ATTRIBUTE_CATEGORY => p_PROD_TASK_rec.ATTRIBUTE_CATEGORY,
320 p_ATTRIBUTE1 => p_PROD_TASK_rec.ATTRIBUTE1,
321 p_ATTRIBUTE2 => p_PROD_TASK_rec.ATTRIBUTE2,
322 p_ATTRIBUTE3 => p_PROD_TASK_rec.ATTRIBUTE3,
323 p_ATTRIBUTE4 => p_PROD_TASK_rec.ATTRIBUTE4,
324 p_ATTRIBUTE5 => p_PROD_TASK_rec.ATTRIBUTE5,
325 p_ATTRIBUTE6 => p_PROD_TASK_rec.ATTRIBUTE6,
326 p_ATTRIBUTE7 => p_PROD_TASK_rec.ATTRIBUTE7,
327 p_ATTRIBUTE8 => p_PROD_TASK_rec.ATTRIBUTE8,
328 p_ATTRIBUTE9 => p_PROD_TASK_rec.ATTRIBUTE9,
329 p_ATTRIBUTE10 => p_PROD_TASK_rec.ATTRIBUTE10,
330 p_ATTRIBUTE11 => p_PROD_TASK_rec.ATTRIBUTE11,
331 p_ATTRIBUTE12 => p_PROD_TASK_rec.ATTRIBUTE12,
332 p_ATTRIBUTE13 => p_PROD_TASK_rec.ATTRIBUTE13,
333 p_ATTRIBUTE14 => p_PROD_TASK_rec.ATTRIBUTE14,
334 p_ATTRIBUTE15 => p_PROD_TASK_rec.ATTRIBUTE15,
335 p_CREATED_BY => FND_API.G_MISS_NUM,
336 p_CREATION_DATE => FND_API.G_MISS_DATE,
337 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
338 p_LAST_UPDATE_DATE => SYSDATE,
339 p_LAST_UPDATE_LOGIN => p_PROD_TASK_rec.LAST_UPDATE_LOGIN);
340
341 --
342 -- End of API body.
343 --
344 -- Standard check for p_commit
345 IF FND_API.to_Boolean( p_commit )
346 THEN
347 COMMIT WORK;
348 END IF;
349 -- Standard call to get message count and if count is 1, get message info.
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
357 P_API_NAME => L_API_NAME
358 ,P_PKG_NAME => G_PKG_NAME
359 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
360 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
361 ,X_MSG_COUNT => X_MSG_COUNT
362 ,X_MSG_DATA => X_MSG_DATA
363 ,X_RETURN_STATUS => X_RETURN_STATUS);
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
366 P_API_NAME => L_API_NAME
367 ,P_PKG_NAME => G_PKG_NAME
368 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
369 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
370 ,X_MSG_COUNT => X_MSG_COUNT
371 ,X_MSG_DATA => X_MSG_DATA
372 ,X_RETURN_STATUS => X_RETURN_STATUS);
373 WHEN OTHERS THEN
374 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
375 P_API_NAME => L_API_NAME
376 ,P_PKG_NAME => G_PKG_NAME
377 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
378 ,P_SQLCODE => SQLCODE
379 ,P_SQLERRM => SQLERRM
380 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
381 ,X_MSG_COUNT => X_MSG_COUNT
382 ,X_MSG_DATA => X_MSG_DATA
383 ,X_RETURN_STATUS => X_RETURN_STATUS);
384 End Update_product_task;
385 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
386 -- The Master delete procedure may not be needed depends on different business requirements.
387 PROCEDURE Delete_product_task(
388 P_Api_Version_Number IN NUMBER,
389 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
390 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
391 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
392 P_PROD_TASK_Rec IN PROD_TASK_Rec_Type,
393 X_Return_Status OUT NOCOPY VARCHAR2,
394 X_Msg_Count OUT NOCOPY NUMBER,
395 X_Msg_Data OUT NOCOPY VARCHAR2
396 )
397 IS
398 l_api_name CONSTANT VARCHAR2(30) := 'Delete_product_task';
399 l_api_version_number CONSTANT NUMBER := 1.0;
400 BEGIN
401 -- Standard Start of API savepoint
402 SAVEPOINT DELETE_PRODUCT_TASK_PVT;
403 -- Standard call to check for call compatibility.
404 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
405 p_api_version_number,
406 l_api_name,
407 G_PKG_NAME)
408 THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END IF;
411 -- Initialize message list if p_init_msg_list is set to TRUE.
412 IF FND_API.to_Boolean( p_init_msg_list )
413 THEN
414 FND_MSG_PUB.initialize;
415 END IF;
416 -- Initialize API return status to SUCCESS
417 x_return_status := FND_API.G_RET_STS_SUCCESS;
418 --
419 -- Api body
420 --
421 -- Invoke table handler(CSP_PRODUCT_TASKS_PKG.Delete_Row)
422 CSP_PRODUCT_TASKS_PKG.Delete_Row(
423 p_PRODUCT_TASK_ID => p_PROD_TASK_rec.PRODUCT_TASK_ID);
424 --
425 -- End of API body
426 --
427 -- Standard check for p_commit
428 IF FND_API.to_Boolean( p_commit )
429 THEN
430 COMMIT WORK;
431 END IF;
432 -- Standard call to get message count and if count is 1, get message info.
433 FND_MSG_PUB.Count_And_Get
434 ( p_count => x_msg_count,
435 p_data => x_msg_data
436 );
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
440 P_API_NAME => L_API_NAME
441 ,P_PKG_NAME => G_PKG_NAME
442 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
443 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
444 ,X_MSG_COUNT => X_MSG_COUNT
445 ,X_MSG_DATA => X_MSG_DATA
446 ,X_RETURN_STATUS => X_RETURN_STATUS);
447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
448 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
449 P_API_NAME => L_API_NAME
450 ,P_PKG_NAME => G_PKG_NAME
451 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
452 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
453 ,X_MSG_COUNT => X_MSG_COUNT
454 ,X_MSG_DATA => X_MSG_DATA
455 ,X_RETURN_STATUS => X_RETURN_STATUS);
456 WHEN OTHERS THEN
457 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
458 P_API_NAME => L_API_NAME
459 ,P_PKG_NAME => G_PKG_NAME
460 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
461 ,P_SQLCODE => SQLCODE
462 ,P_SQLERRM => SQLERRM
463 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
464 ,X_MSG_COUNT => X_MSG_COUNT
465 ,X_MSG_DATA => X_MSG_DATA
466 ,X_RETURN_STATUS => X_RETURN_STATUS);
467 End Delete_product_task;
468 -- This procudure defines the columns for the Dynamic SQL.
469 PROCEDURE Define_Columns(
470 P_PROD_TASK_Rec IN CSP_PRODUCT_TASK_PUB.PROD_TASK_Rec_Type,
471 p_cur_get_PROD_TASK IN NUMBER
472 )
473 IS
474 BEGIN
475 -- define all columns for CSP_PRODUCT_TASKS view
476 dbms_sql.define_column(p_cur_get_PROD_TASK, 1, P_PROD_TASK_Rec.PRODUCT_TASK_ID);
477 dbms_sql.define_column(p_cur_get_PROD_TASK, 2, P_PROD_TASK_Rec.PRODUCT_ID);
478 dbms_sql.define_column(p_cur_get_PROD_TASK, 3, P_PROD_TASK_Rec.TASK_TEMPLATE_ID);
479 dbms_sql.define_column(p_cur_get_PROD_TASK, 4, P_PROD_TASK_Rec.AUTO_MANUAL, 6);
480 dbms_sql.define_column(p_cur_get_PROD_TASK, 5, P_PROD_TASK_Rec.ACTUAL_TIMES_USED);
481 dbms_sql.define_column(p_cur_get_PROD_TASK, 6, P_PROD_TASK_Rec.TASK_PERCENTAGE);
482 dbms_sql.define_column(p_cur_get_PROD_TASK, 7, P_PROD_TASK_Rec.ATTRIBUTE_CATEGORY, 30);
483 dbms_sql.define_column(p_cur_get_PROD_TASK, 8, P_PROD_TASK_Rec.ATTRIBUTE1, 150);
484 dbms_sql.define_column(p_cur_get_PROD_TASK, 9, P_PROD_TASK_Rec.ATTRIBUTE2, 150);
485 dbms_sql.define_column(p_cur_get_PROD_TASK, 10, P_PROD_TASK_Rec.ATTRIBUTE3, 150);
486 dbms_sql.define_column(p_cur_get_PROD_TASK, 11, P_PROD_TASK_Rec.ATTRIBUTE4, 150);
487 dbms_sql.define_column(p_cur_get_PROD_TASK, 12, P_PROD_TASK_Rec.ATTRIBUTE5, 150);
488 dbms_sql.define_column(p_cur_get_PROD_TASK, 13, P_PROD_TASK_Rec.ATTRIBUTE6, 150);
489 dbms_sql.define_column(p_cur_get_PROD_TASK, 14, P_PROD_TASK_Rec.ATTRIBUTE7, 150);
490 dbms_sql.define_column(p_cur_get_PROD_TASK, 15, P_PROD_TASK_Rec.ATTRIBUTE8, 150);
491 dbms_sql.define_column(p_cur_get_PROD_TASK, 16, P_PROD_TASK_Rec.ATTRIBUTE9, 150);
492 dbms_sql.define_column(p_cur_get_PROD_TASK, 17, P_PROD_TASK_Rec.ATTRIBUTE10, 150);
493 dbms_sql.define_column(p_cur_get_PROD_TASK, 18, P_PROD_TASK_Rec.ATTRIBUTE11, 150);
494 dbms_sql.define_column(p_cur_get_PROD_TASK, 19, P_PROD_TASK_Rec.ATTRIBUTE12, 150);
495 dbms_sql.define_column(p_cur_get_PROD_TASK, 20, P_PROD_TASK_Rec.ATTRIBUTE13, 150);
496 dbms_sql.define_column(p_cur_get_PROD_TASK, 21, P_PROD_TASK_Rec.ATTRIBUTE14, 150);
497 dbms_sql.define_column(p_cur_get_PROD_TASK, 22, P_PROD_TASK_Rec.ATTRIBUTE15, 150);
498 END Define_Columns;
499 -- This procudure gets column values by the Dynamic SQL.
500 PROCEDURE Get_Column_Values(
501 p_cur_get_PROD_TASK IN NUMBER,
502 X_PROD_TASK_Rec OUT NOCOPY CSP_PRODUCT_TASK_PUB.PROD_TASK_Rec_Type
503 )
504 IS
505 BEGIN
506 -- get all column values for CSP_PRODUCT_TASKS table
507 dbms_sql.column_value(p_cur_get_PROD_TASK, 1, X_PROD_TASK_Rec.PRODUCT_TASK_ID);
508 dbms_sql.column_value(p_cur_get_PROD_TASK, 2, X_PROD_TASK_Rec.PRODUCT_ID);
509 dbms_sql.column_value(p_cur_get_PROD_TASK, 3, X_PROD_TASK_Rec.TASK_TEMPLATE_ID);
510 dbms_sql.column_value(p_cur_get_PROD_TASK, 4, X_PROD_TASK_Rec.AUTO_MANUAL);
511 dbms_sql.column_value(p_cur_get_PROD_TASK, 5, X_PROD_TASK_Rec.ACTUAL_TIMES_USED);
512 dbms_sql.column_value(p_cur_get_PROD_TASK, 6, X_PROD_TASK_Rec.TASK_PERCENTAGE);
513 dbms_sql.column_value(p_cur_get_PROD_TASK, 7, X_PROD_TASK_Rec.ATTRIBUTE_CATEGORY);
514 dbms_sql.column_value(p_cur_get_PROD_TASK, 8, X_PROD_TASK_Rec.ATTRIBUTE1);
515 dbms_sql.column_value(p_cur_get_PROD_TASK, 9, X_PROD_TASK_Rec.ATTRIBUTE2);
516 dbms_sql.column_value(p_cur_get_PROD_TASK, 10, X_PROD_TASK_Rec.ATTRIBUTE3);
517 dbms_sql.column_value(p_cur_get_PROD_TASK, 11, X_PROD_TASK_Rec.ATTRIBUTE4);
518 dbms_sql.column_value(p_cur_get_PROD_TASK, 12, X_PROD_TASK_Rec.ATTRIBUTE5);
519 dbms_sql.column_value(p_cur_get_PROD_TASK, 13, X_PROD_TASK_Rec.ATTRIBUTE6);
520 dbms_sql.column_value(p_cur_get_PROD_TASK, 14, X_PROD_TASK_Rec.ATTRIBUTE7);
521 dbms_sql.column_value(p_cur_get_PROD_TASK, 15, X_PROD_TASK_Rec.ATTRIBUTE8);
522 dbms_sql.column_value(p_cur_get_PROD_TASK, 16, X_PROD_TASK_Rec.ATTRIBUTE9);
523 dbms_sql.column_value(p_cur_get_PROD_TASK, 17, X_PROD_TASK_Rec.ATTRIBUTE10);
524 dbms_sql.column_value(p_cur_get_PROD_TASK, 18, X_PROD_TASK_Rec.ATTRIBUTE11);
525 dbms_sql.column_value(p_cur_get_PROD_TASK, 19, X_PROD_TASK_Rec.ATTRIBUTE12);
526 dbms_sql.column_value(p_cur_get_PROD_TASK, 20, X_PROD_TASK_Rec.ATTRIBUTE13);
527 dbms_sql.column_value(p_cur_get_PROD_TASK, 21, X_PROD_TASK_Rec.ATTRIBUTE14);
528 dbms_sql.column_value(p_cur_get_PROD_TASK, 22, X_PROD_TASK_Rec.ATTRIBUTE15);
529 END Get_Column_Values;
530 -- This procedure bind the variables for the Dynamic SQL
531 PROCEDURE Bind(
532 P_PROD_TASK_Rec IN CSP_PRODUCT_TASK_PUB.PROD_TASK_Rec_Type,
533 -- Hint: Add more binding variables here
534 p_cur_get_PROD_TASK IN NUMBER
535 )
536 IS
537 BEGIN
538 -- Bind variables
539 -- Only those that are not NULL
540 -- The following example applies to all columns,
541 -- developers can copy and paste them.
542 IF( (P_PROD_TASK_Rec.PRODUCT_TASK_ID IS NOT NULL) AND (P_PROD_TASK_Rec.PRODUCT_TASK_ID <> FND_API.G_MISS_NUM) )
543 THEN
544 DBMS_SQL.BIND_VARIABLE(p_cur_get_PROD_TASK, ':p_PRODUCT_TASK_ID', P_PROD_TASK_Rec.PRODUCT_TASK_ID);
545 END IF;
546 END Bind;
547 PROCEDURE Gen_Select(
548 x_select_cl OUT NOCOPY VARCHAR2
549 )
550 IS
551 BEGIN
552 x_select_cl := 'Select ' ||
553 'CSP_PRODUCT_TASKS.PRODUCT_TASK_ID,' ||
554 'CSP_PRODUCT_TASKS.PRODUCT_ID,' ||
555 'CSP_PRODUCT_TASKS.TASK_TEMPLATE_ID,' ||
556 'CSP_PRODUCT_TASKS.AUTO_MANUAL,' ||
557 'CSP_PRODUCT_TASKS.ACTUAL_TIMES_USED,' ||
558 'CSP_PRODUCT_TASKS.TASK_PERCENTAGE,' ||
559 'CSP_PRODUCT_TASKS.ATTRIBUTE_CATEGORY,' ||
560 'CSP_PRODUCT_TASKS.ATTRIBUTE1,' ||
561 'CSP_PRODUCT_TASKS.ATTRIBUTE2,' ||
562 'CSP_PRODUCT_TASKS.ATTRIBUTE3,' ||
563 'CSP_PRODUCT_TASKS.ATTRIBUTE4,' ||
564 'CSP_PRODUCT_TASKS.ATTRIBUTE5,' ||
565 'CSP_PRODUCT_TASKS.ATTRIBUTE6,' ||
566 'CSP_PRODUCT_TASKS.ATTRIBUTE7,' ||
567 'CSP_PRODUCT_TASKS.ATTRIBUTE8,' ||
568 'CSP_PRODUCT_TASKS.ATTRIBUTE9,' ||
569 'CSP_PRODUCT_TASKS.ATTRIBUTE10,' ||
570 'CSP_PRODUCT_TASKS.ATTRIBUTE11,' ||
571 'CSP_PRODUCT_TASKS.ATTRIBUTE12,' ||
572 'CSP_PRODUCT_TASKS.ATTRIBUTE13,' ||
573 'CSP_PRODUCT_TASKS.ATTRIBUTE14,' ||
574 'CSP_PRODUCT_TASKS.ATTRIBUTE15,' ||
575 'CSP_PRODUCT_TASKS.CREATED_BY,' ||
576 'CSP_PRODUCT_TASKS.CREATION_DATE,' ||
577 'CSP_PRODUCT_TASKS.LAST_UPDATED_BY,' ||
578 'CSP_PRODUCT_TASKS.LAST_UPDATE_DATE,' ||
579 'CSP_PRODUCT_TASKS.LAST_UPDATE_LOGIN,' ||
580 'from CSP_PRODUCT_TASKS';
581 END Gen_Select;
582 PROCEDURE Gen_PROD_TASK_Where(
583 P_PROD_TASK_Rec IN CSP_PRODUCT_TASK_PUB.PROD_TASK_Rec_Type,
584 x_PROD_TASK_where OUT NOCOPY VARCHAR2
585 )
586 IS
587 -- cursors to check if wildcard values '%' and '_' have been passed
588 -- as item values
589 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
590 SELECT INSTR(p_rec_item, '%', 1, 1)
591 FROM DUAL;
592 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
593 SELECT INSTR(p_rec_item, '_', 1, 1)
594 FROM DUAL;
595 -- return values from cursors
596 str_csr1 NUMBER;
597 str_csr2 NUMBER;
598 l_operator VARCHAR2(10);
599 BEGIN
600 -- There are three examples for each kind of datatype:
601 -- NUMBER, DATE, VARCHAR2.
602 -- Developer can copy and paste the following codes for your own record.
603 -- example for NUMBER datatype
604 IF( (P_PROD_TASK_Rec.PRODUCT_TASK_ID IS NOT NULL) AND (P_PROD_TASK_Rec.PRODUCT_TASK_ID <> FND_API.G_MISS_NUM) )
605 THEN
606 IF(x_PROD_TASK_where IS NULL) THEN
607 x_PROD_TASK_where := 'Where';
608 ELSE
609 x_PROD_TASK_where := x_PROD_TASK_where || ' AND ';
610 END IF;
611 x_PROD_TASK_where := x_PROD_TASK_where || 'P_PROD_TASK_Rec.PRODUCT_TASK_ID = :p_PRODUCT_TASK_ID';
612 END IF;
613 -- example for DATE datatype
614 IF( (P_PROD_TASK_Rec.CREATION_DATE IS NOT NULL) AND (P_PROD_TASK_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
615 THEN
616 -- check if item value contains '%' wildcard
617 OPEN c_chk_str1(P_PROD_TASK_Rec.CREATION_DATE);
618 FETCH c_chk_str1 INTO str_csr1;
619 CLOSE c_chk_str1;
620 IF(str_csr1 <> 0) THEN
621 l_operator := ' LIKE ';
622 ELSE
623 l_operator := ' = ';
624 END IF;
625 -- check if item value contains '_' wildcard
626 OPEN c_chk_str2(P_PROD_TASK_Rec.CREATION_DATE);
627 FETCH c_chk_str2 INTO str_csr2;
628 CLOSE c_chk_str2;
629 IF(str_csr2 <> 0) THEN
630 l_operator := ' LIKE ';
631 ELSE
632 l_operator := ' = ';
633 END IF;
634 IF(x_PROD_TASK_where IS NULL) THEN
635 x_PROD_TASK_where := 'Where ';
636 ELSE
637 x_PROD_TASK_where := x_PROD_TASK_where || ' AND ';
638 END IF;
639 x_PROD_TASK_where := x_PROD_TASK_where || 'P_PROD_TASK_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
640 END IF;
641 -- example for VARCHAR2 datatype
642 IF( (P_PROD_TASK_Rec.AUTO_MANUAL IS NOT NULL) AND (P_PROD_TASK_Rec.AUTO_MANUAL <> FND_API.G_MISS_CHAR) )
643 THEN
644 -- check if item value contains '%' wildcard
645 OPEN c_chk_str1(P_PROD_TASK_Rec.AUTO_MANUAL);
646 FETCH c_chk_str1 INTO str_csr1;
647 CLOSE c_chk_str1;
648 IF(str_csr1 <> 0) THEN
649 l_operator := ' LIKE ';
650 ELSE
651 l_operator := ' = ';
652 END IF;
653 -- check if item value contains '_' wildcard
654 OPEN c_chk_str2(P_PROD_TASK_Rec.AUTO_MANUAL);
655 FETCH c_chk_str2 INTO str_csr2;
656 CLOSE c_chk_str2;
657 IF(str_csr2 <> 0) THEN
658 l_operator := ' LIKE ';
659 ELSE
660 l_operator := ' = ';
661 END IF;
662 IF(x_PROD_TASK_where IS NULL) THEN
663 x_PROD_TASK_where := 'Where ';
664 ELSE
665 x_PROD_TASK_where := x_PROD_TASK_where || ' AND ';
666 END IF;
667 x_PROD_TASK_where := x_PROD_TASK_where || 'P_PROD_TASK_Rec.AUTO_MANUAL ' || l_operator || ' :p_AUTO_MANUAL';
668 END IF;
669 -- Add more IF statements for each column below
670 END Gen_PROD_TASK_Where;
671
672 -- Item-level validation procedures
673 PROCEDURE Validate_PRODUCT_TASK_ID (
674 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
675 P_Validation_mode IN VARCHAR2,
676 P_PRODUCT_TASK_ID IN NUMBER,
677 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
678 X_Return_Status OUT NOCOPY VARCHAR2,
679 X_Msg_Count OUT NOCOPY NUMBER,
680 X_Msg_Data OUT NOCOPY VARCHAR2
681 )
682 IS
683 BEGIN
684 -- Initialize message list if p_init_msg_list is set to TRUE.
685 IF FND_API.to_Boolean( p_init_msg_list )
686 THEN
687 FND_MSG_PUB.initialize;
688 END IF;
689 -- Initialize API return status to SUCCESS
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691 -- validate NOT NULL column
692 IF(p_PRODUCT_TASK_ID is NULL)
693 THEN
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 END IF;
696 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
697 THEN
698 -- Hint: Validate data
699 -- IF p_PRODUCT_TASK_ID is not NULL and p_PRODUCT_TASK_ID <> G_MISS_CHAR
700 -- verify if data is valid
701 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
702 NULL;
703 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
704 THEN
705 -- Hint: Validate data
706 -- IF p_PRODUCT_TASK_ID <> G_MISS_CHAR
707 -- verify if data is valid
708 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
709 NULL;
710 END IF;
711 -- Standard call to get message count and if count is 1, get message info.
712 FND_MSG_PUB.Count_And_Get
713 ( p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716 END Validate_PRODUCT_TASK_ID;
717 PROCEDURE Validate_PRODUCT_ID (
718 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
719 P_Validation_mode IN VARCHAR2,
720 P_PRODUCT_ID IN NUMBER,
721 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
722 X_Return_Status OUT NOCOPY VARCHAR2,
723 X_Msg_Count OUT NOCOPY NUMBER,
724 X_Msg_Data OUT NOCOPY VARCHAR2
725 )
726 IS
727 BEGIN
728 -- Initialize message list if p_init_msg_list is set to TRUE.
729 IF FND_API.to_Boolean( p_init_msg_list )
730 THEN
731 FND_MSG_PUB.initialize;
732 END IF;
733 -- Initialize API return status to SUCCESS
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735 -- validate NOT NULL column
736 IF(p_PRODUCT_ID is NULL)
737 THEN
738 x_return_status := FND_API.G_RET_STS_ERROR;
739 END IF;
740 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
741 THEN
742 -- Hint: Validate data
743 -- IF p_PRODUCT_ID is not NULL and p_PRODUCT_ID <> G_MISS_CHAR
744 -- verify if data is valid
745 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
746 NULL;
747 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
748 THEN
749 -- Hint: Validate data
750 -- IF p_PRODUCT_ID <> G_MISS_CHAR
751 -- verify if data is valid
752 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
753 NULL;
754 END IF;
755 -- Standard call to get message count and if count is 1, get message info.
756 FND_MSG_PUB.Count_And_Get
757 ( p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760 END Validate_PRODUCT_ID;
761 PROCEDURE Validate_TASK_TEMPLATE_ID (
762 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
763 P_Validation_mode IN VARCHAR2,
764 P_TASK_TEMPLATE_ID IN NUMBER,
765 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
766 X_Return_Status OUT NOCOPY VARCHAR2,
767 X_Msg_Count OUT NOCOPY NUMBER,
768 X_Msg_Data OUT NOCOPY VARCHAR2
769 )
770 IS
771 BEGIN
772 -- Initialize message list if p_init_msg_list is set to TRUE.
773 IF FND_API.to_Boolean( p_init_msg_list )
774 THEN
775 FND_MSG_PUB.initialize;
776 END IF;
777 -- Initialize API return status to SUCCESS
778 x_return_status := FND_API.G_RET_STS_SUCCESS;
779 -- validate NOT NULL column
780 IF(p_TASK_TEMPLATE_ID is NULL)
781 THEN
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 END IF;
784 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
785 THEN
786 -- Hint: Validate data
787 -- IF p_TASK_TEMPLATE_ID is not NULL and p_TASK_TEMPLATE_ID <> G_MISS_CHAR
788 -- verify if data is valid
789 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
790 NULL;
791 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
792 THEN
793 -- Hint: Validate data
794 -- IF p_TASK_TEMPLATE_ID <> G_MISS_CHAR
795 -- verify if data is valid
796 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
797 NULL;
798 END IF;
799 -- Standard call to get message count and if count is 1, get message info.
800 FND_MSG_PUB.Count_And_Get
801 ( p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804 END Validate_TASK_TEMPLATE_ID;
805 PROCEDURE Validate_AUTO_MANUAL (
806 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
807 P_Validation_mode IN VARCHAR2,
808 P_AUTO_MANUAL IN VARCHAR2,
809 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
810 X_Return_Status OUT NOCOPY VARCHAR2,
811 X_Msg_Count OUT NOCOPY NUMBER,
812 X_Msg_Data OUT NOCOPY VARCHAR2
813 )
814 IS
815 BEGIN
816 -- Initialize message list if p_init_msg_list is set to TRUE.
817 IF FND_API.to_Boolean( p_init_msg_list )
818 THEN
819 FND_MSG_PUB.initialize;
820 END IF;
821 -- Initialize API return status to SUCCESS
822 x_return_status := FND_API.G_RET_STS_SUCCESS;
823 -- validate NOT NULL column
824 IF(p_AUTO_MANUAL is NULL)
825 THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 END IF;
828 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
829 THEN
830 -- Hint: Validate data
831 -- IF p_AUTO_MANUAL is not NULL and p_AUTO_MANUAL <> G_MISS_CHAR
832 -- verify if data is valid
833 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
834 NULL;
835 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
836 THEN
837 -- Hint: Validate data
838 -- IF p_AUTO_MANUAL <> G_MISS_CHAR
839 -- verify if data is valid
840 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
841 NULL;
842 END IF;
843 -- Standard call to get message count and if count is 1, get message info.
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848 END Validate_AUTO_MANUAL;
849 PROCEDURE Validate_ACTUAL_TIMES_USED (
850 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
851 P_Validation_mode IN VARCHAR2,
852 P_ACTUAL_TIMES_USED IN NUMBER,
853 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
854 X_Return_Status OUT NOCOPY VARCHAR2,
855 X_Msg_Count OUT NOCOPY NUMBER,
856 X_Msg_Data OUT NOCOPY VARCHAR2
857 )
858 IS
859 BEGIN
860 -- Initialize message list if p_init_msg_list is set to TRUE.
861 IF FND_API.to_Boolean( p_init_msg_list )
862 THEN
863 FND_MSG_PUB.initialize;
864 END IF;
865 -- Initialize API return status to SUCCESS
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
868 THEN
869 -- Hint: Validate data
870 -- IF p_ACTUAL_TIMES_USED is not NULL and p_ACTUAL_TIMES_USED <> G_MISS_CHAR
871 -- verify if data is valid
872 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
873 NULL;
874 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
875 THEN
876 -- Hint: Validate data
877 -- IF p_ACTUAL_TIMES_USED <> G_MISS_CHAR
878 -- verify if data is valid
879 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
880 NULL;
881 END IF;
882 -- Standard call to get message count and if count is 1, get message info.
883 FND_MSG_PUB.Count_And_Get
884 ( p_count => x_msg_count,
885 p_data => x_msg_data
886 );
887 END Validate_ACTUAL_TIMES_USED;
888 PROCEDURE Validate_TASK_PERCENTAGE (
889 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
890 P_Validation_mode IN VARCHAR2,
891 P_TASK_PERCENTAGE IN NUMBER,
892 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
893 X_Return_Status OUT NOCOPY VARCHAR2,
894 X_Msg_Count OUT NOCOPY NUMBER,
895 X_Msg_Data OUT NOCOPY VARCHAR2
896 )
897 IS
898 BEGIN
899 -- Initialize message list if p_init_msg_list is set to TRUE.
900 IF FND_API.to_Boolean( p_init_msg_list )
901 THEN
902 FND_MSG_PUB.initialize;
903 END IF;
904 -- Initialize API return status to SUCCESS
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
907 THEN
908 -- Hint: Validate data
909 -- IF p_TASK_PERCENTAGE is not NULL and p_TASK_PERCENTAGE <> G_MISS_CHAR
910 -- verify if data is valid
911 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
912 NULL;
913 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
914 THEN
915 -- Hint: Validate data
916 -- IF p_TASK_PERCENTAGE <> G_MISS_CHAR
917 -- verify if data is valid
918 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
919 NULL;
920 END IF;
921 -- Standard call to get message count and if count is 1, get message info.
922 FND_MSG_PUB.Count_And_Get
923 ( p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926 END Validate_TASK_PERCENTAGE;
927 -- Hint: inter-field level validation can be added here.
928 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
929 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
930 -- stored in database table.
931 PROCEDURE Validate_PROD_TASK_rec(
932 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
933 P_Validation_mode IN VARCHAR2,
934 P_PROD_TASK_Rec IN PROD_TASK_Rec_Type,
935 X_Return_Status OUT NOCOPY VARCHAR2,
936 X_Msg_Count OUT NOCOPY NUMBER,
937 X_Msg_Data OUT NOCOPY VARCHAR2
938 )
939 IS
940 BEGIN
941 -- Initialize message list if p_init_msg_list is set to TRUE.
942 IF FND_API.to_Boolean( p_init_msg_list )
943 THEN
944 FND_MSG_PUB.initialize;
945 END IF;
946 -- Initialize API return status to SUCCESS
947 x_return_status := FND_API.G_RET_STS_SUCCESS;
948 -- Hint: Validate data
949 -- If data not valid
950 -- THEN
951 -- x_return_status := FND_API.G_RET_STS_ERROR;
952 -- Debug Message
953 -- Standard call to get message count and if count is 1, get message info.
954 FND_MSG_PUB.Count_And_Get
955 ( p_count => x_msg_count,
956 p_data => x_msg_data
957 );
958 END Validate_PROD_TASK_Rec;
959 PROCEDURE Validate_product_task(
960 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
961 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
962 P_Validation_mode IN VARCHAR2,
963 P_PROD_TASK_Rec IN PROD_TASK_Rec_Type,
964 X_Return_Status OUT NOCOPY VARCHAR2,
965 X_Msg_Count OUT NOCOPY NUMBER,
966 X_Msg_Data OUT NOCOPY VARCHAR2
967 )
968 IS
969 l_api_name CONSTANT VARCHAR2(30) := 'Validate_product_task';
970 BEGIN
971 -- Initialize API return status to SUCCESS
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
974 -- Hint: We provide validation procedure for every column. Developer should delete
975 -- unnecessary validation procedures.
976 Validate_PRODUCT_TASK_ID(
977 p_init_msg_list => FND_API.G_FALSE,
978 p_validation_mode => p_validation_mode,
979 p_PRODUCT_TASK_ID => P_PROD_TASK_Rec.PRODUCT_TASK_ID,
980 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data);
984 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
985 raise FND_API.G_EXC_ERROR;
986 END IF;
987 Validate_PRODUCT_ID(
988 p_init_msg_list => FND_API.G_FALSE,
989 p_validation_mode => p_validation_mode,
990 p_PRODUCT_ID => P_PROD_TASK_Rec.PRODUCT_ID,
991 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
992 x_return_status => x_return_status,
993 x_msg_count => x_msg_count,
994 x_msg_data => x_msg_data);
995 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
996 raise FND_API.G_EXC_ERROR;
997 END IF;
998 Validate_TASK_TEMPLATE_ID(
999 p_init_msg_list => FND_API.G_FALSE,
1000 p_validation_mode => p_validation_mode,
1001 p_TASK_TEMPLATE_ID => P_PROD_TASK_Rec.TASK_TEMPLATE_ID,
1002 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1003 x_return_status => x_return_status,
1004 x_msg_count => x_msg_count,
1005 x_msg_data => x_msg_data);
1006 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1007 raise FND_API.G_EXC_ERROR;
1008 END IF;
1009 Validate_AUTO_MANUAL(
1010 p_init_msg_list => FND_API.G_FALSE,
1011 p_validation_mode => p_validation_mode,
1012 p_AUTO_MANUAL => P_PROD_TASK_Rec.AUTO_MANUAL,
1013 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1014 x_return_status => x_return_status,
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data);
1017 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1018 raise FND_API.G_EXC_ERROR;
1019 END IF;
1020 Validate_ACTUAL_TIMES_USED(
1021 p_init_msg_list => FND_API.G_FALSE,
1022 p_validation_mode => p_validation_mode,
1023 p_ACTUAL_TIMES_USED => P_PROD_TASK_Rec.ACTUAL_TIMES_USED,
1024 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1025 x_return_status => x_return_status,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data);
1028 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1029 raise FND_API.G_EXC_ERROR;
1030 END IF;
1031 Validate_TASK_PERCENTAGE(
1032 p_init_msg_list => FND_API.G_FALSE,
1033 p_validation_mode => p_validation_mode,
1034 p_TASK_PERCENTAGE => P_PROD_TASK_Rec.TASK_PERCENTAGE,
1035 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1036 x_return_status => x_return_status,
1037 x_msg_count => x_msg_count,
1038 x_msg_data => x_msg_data);
1039 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1040 raise FND_API.G_EXC_ERROR;
1041 END IF;
1042 END IF;
1043 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1044 -- Hint: Inter-field level validation can be added here
1045 -- invoke record level validation procedures
1046 Validate_PROD_TASK_Rec(
1047 p_init_msg_list => FND_API.G_FALSE,
1048 p_validation_mode => p_validation_mode,
1049 P_PROD_TASK_Rec => P_PROD_TASK_Rec,
1050 x_return_status => x_return_status,
1051 x_msg_count => x_msg_count,
1052 x_msg_data => x_msg_data);
1053 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 raise FND_API.G_EXC_ERROR;
1055 END IF;
1056 END IF;
1057 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1058 -- invoke inter-record level validation procedures
1059 NULL;
1060 END IF;
1061 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1062 -- invoke inter-entity level validation procedures
1063 NULL;
1064 END IF;
1065 END Validate_product_task;
1066 End CSP_PRODUCT_TASK_PVT;
1067