[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_JOB_XREF_PVT
Source
1 PACKAGE BODY CSD_REPAIR_JOB_XREF_PVT as
2 /* $Header: csdvdrjb.pls 115.13 2003/09/15 21:34:28 sragunat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIR_JOB_XREF_PVT
5 -- Purpose :
6 -- History : Added Inventory_Item_ID and Item_Revison Columns -- travi
7 -- History : 01/17/2002, TRAVI added column OBJECT_VERSION_NUMBER
8 -- History : 08/20/2003, Shiv Ragunathan, 11.5.10 Changes: Made
9 -- History : Changes to procedure Create_repjobxref to process
10 -- History : source_type_code, source_id1, ro_service_code_id
11 -- History : and job_name.
12 -- NOTE :
13 -- End of Comments
14
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_REPAIR_JOB_XREF_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrjxb.pls';
18
19 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
20 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
21 g_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
22 -- Hint: Primary key needs to be returned.
23 PROCEDURE CREATE_REPJOBXREF(
24 P_Api_Version_Number IN NUMBER,
25 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
26 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
27 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
28 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type := G_MISS_REPJOBXREF_REC,
29 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
30 X_REPAIR_JOB_XREF_ID OUT NOCOPY NUMBER,
31 X_Return_Status OUT NOCOPY VARCHAR2,
32 X_Msg_Count OUT NOCOPY NUMBER,
33 X_Msg_Data OUT NOCOPY VARCHAR2
34 )
35
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REPJOBXREF';
38 l_api_version_number CONSTANT NUMBER := 1.0;
39 l_return_status_full VARCHAR2(1);
40 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
41 BEGIN
42 -- Standard Start of API savepoint
43 SAVEPOINT CREATE_REPJOBXREF_PVT;
44
45 -- Standard call to check for call compatibility.
46 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
47 p_api_version_number,
48 l_api_name,
49 G_PKG_NAME)
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 END IF;
53
54
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 IF FND_API.to_Boolean( p_init_msg_list )
57 THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61
62 -- Debug Message
63 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'start');
64
65
66 -- Initialize API return status to SUCCESS
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 --
70 -- API body
71 --
72
73 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
74 /*
75 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
76 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
77 THEN
78 AS_CALLOUT_PKG.Create_repjobxref_BC(
79 p_api_version_number => 2.0,
80 p_init_msg_list => FND_API.G_FALSE,
81 p_commit => FND_API.G_FALSE,
82 p_validation_level => p_validation_level,
83 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
84 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
85 x_return_status => x_return_status,
86 x_msg_count => x_msg_count,
87 x_msg_data => x_msg_data);
88 END IF;
89 */
90 -- ******************************************************************
91 -- Validate Environment
92 -- ******************************************************************
93 IF FND_GLOBAL.User_Id IS NULL
94 THEN
95 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
96 THEN
97 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
98 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
99 FND_MSG_PUB.ADD;
100 END IF;
101 RAISE FND_API.G_EXC_ERROR;
102 END IF;
103 /*
104
105 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
106 p_api_version_number => 2.0
107 ,p_salesforce_id => NULL
108 ,x_return_status => x_return_status
109 ,x_msg_count => x_msg_count
110 ,x_msg_data => x_msg_data
111 ,x_sales_member_rec => l_identity_sales_member_rec);
112
113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116 */
117
118
119 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
120 THEN
121 -- Debug message
122 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Validate_repjobxref');
123
124 -- Invoke validation procedures
125 Validate_repjobxref(
126 p_init_msg_list => FND_API.G_FALSE,
127 p_validation_level => p_validation_level,
128 p_validation_mode => JTF_PLSQL_API.G_CREATE,
129 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
130 x_return_status => x_return_status,
131 x_msg_count => x_msg_count,
132 x_msg_data => x_msg_data);
133 END IF;
134
135 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138
139 -- Hint: Add corresponding Master-Detail business logic here if necessary.
140
141 -- Debug Message
142 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Calling create table handler');
143
144 -- Invoke table handler(CSD_REPAIR_JOB_XREF_PKG.Insert_Row)
145 CSD_REPAIR_JOB_XREF_PKG.Insert_Row(
146 px_REPAIR_JOB_XREF_ID => x_REPAIR_JOB_XREF_ID,
147 p_CREATED_BY => G_USER_ID,
148 p_CREATION_DATE => SYSDATE,
149 p_LAST_UPDATED_BY => G_USER_ID,
150 p_LAST_UPDATE_DATE => SYSDATE,
151 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
152 p_REPAIR_LINE_ID => p_REPJOBXREF_rec.REPAIR_LINE_ID,
153 p_WIP_ENTITY_ID => p_REPJOBXREF_rec.WIP_ENTITY_ID,
154 p_GROUP_ID => p_REPJOBXREF_rec.GROUP_ID,
155 p_ORGANIZATION_ID => p_REPJOBXREF_rec.ORGANIZATION_ID,
156 p_QUANTITY => p_REPJOBXREF_rec.QUANTITY,
157 p_INVENTORY_ITEM_ID => p_REPJOBXREF_rec.INVENTORY_ITEM_ID,
158 p_ITEM_REVISION => p_REPJOBXREF_rec.ITEM_REVISION,
159 p_SOURCE_TYPE_CODE => p_REPJOBXREF_rec.SOURCE_TYPE_CODE,
160 p_SOURCE_ID1 => p_REPJOBXREF_rec.SOURCE_ID1,
161 p_RO_SERVICE_CODE_ID => p_REPJOBXREF_rec.RO_SERVICE_CODE_ID,
162 p_JOB_NAME => p_REPJOBXREF_rec.JOB_NAME,
163 p_OBJECT_VERSION_NUMBER => p_REPJOBXREF_rec.OBJECT_VERSION_NUMBER,
164 p_ATTRIBUTE_CATEGORY => p_REPJOBXREF_rec.ATTRIBUTE_CATEGORY,
165 p_ATTRIBUTE1 => p_REPJOBXREF_rec.ATTRIBUTE1,
166 p_ATTRIBUTE2 => p_REPJOBXREF_rec.ATTRIBUTE2,
167 p_ATTRIBUTE3 => p_REPJOBXREF_rec.ATTRIBUTE3,
168 p_ATTRIBUTE4 => p_REPJOBXREF_rec.ATTRIBUTE4,
169 p_ATTRIBUTE5 => p_REPJOBXREF_rec.ATTRIBUTE5,
170 p_ATTRIBUTE6 => p_REPJOBXREF_rec.ATTRIBUTE6,
171 p_ATTRIBUTE7 => p_REPJOBXREF_rec.ATTRIBUTE7,
172 p_ATTRIBUTE8 => p_REPJOBXREF_rec.ATTRIBUTE8,
173 p_ATTRIBUTE9 => p_REPJOBXREF_rec.ATTRIBUTE9,
174 p_ATTRIBUTE10 => p_REPJOBXREF_rec.ATTRIBUTE10,
175 p_ATTRIBUTE11 => p_REPJOBXREF_rec.ATTRIBUTE11,
176 p_ATTRIBUTE12 => p_REPJOBXREF_rec.ATTRIBUTE12,
177 p_ATTRIBUTE13 => p_REPJOBXREF_rec.ATTRIBUTE13,
178 p_ATTRIBUTE14 => p_REPJOBXREF_rec.ATTRIBUTE14,
179 p_ATTRIBUTE15 => p_REPJOBXREF_rec.ATTRIBUTE15,
180 p_QUANTITY_COMPLETED => p_REPJOBXREF_rec.QUANTITY_COMPLETED);
181 -- Hint: Primary key should be returned.
182 -- x_REPAIR_JOB_XREF_ID := px_REPAIR_JOB_XREF_ID;
183
184 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 --
189 -- End of API body
190 --
191
192 -- Standard check for p_commit
193 IF FND_API.to_Boolean( p_commit )
194 THEN
195 COMMIT WORK;
196 END IF;
197
198
199 -- Debug Message
200 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'end');
201
202
203 -- Standard call to get message count and if count is 1, get message info.
204 FND_MSG_PUB.Count_And_Get
205 ( p_count => x_msg_count,
206 p_data => x_msg_data
207 );
208
209 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
210 /*
211 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
212 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
213 THEN
214 AS_CALLOUT_PKG.Create_repjobxref_AC(
215 p_api_version_number => 2.0,
216 p_init_msg_list => FND_API.G_FALSE,
217 p_commit => FND_API.G_FALSE,
218 p_validation_level => p_validation_level,
219 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
220 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data);
224 END IF;
225 */
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
229 P_API_NAME => L_API_NAME
230 ,P_PKG_NAME => G_PKG_NAME
231 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
232 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
233 ,X_MSG_COUNT => X_MSG_COUNT
234 ,X_MSG_DATA => X_MSG_DATA
235 ,X_RETURN_STATUS => X_RETURN_STATUS);
236 -- RAISE;
237
238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
239 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
240 P_API_NAME => L_API_NAME
241 ,P_PKG_NAME => G_PKG_NAME
242 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
243 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
244 ,X_MSG_COUNT => X_MSG_COUNT
245 ,X_MSG_DATA => X_MSG_DATA
246 ,X_RETURN_STATUS => X_RETURN_STATUS);
247 -- RAISE;
248
249 WHEN OTHERS THEN
250 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
251 P_API_NAME => L_API_NAME
252 ,P_PKG_NAME => G_PKG_NAME
253 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
254 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
255 ,X_MSG_COUNT => X_MSG_COUNT
256 ,X_MSG_DATA => X_MSG_DATA
257 ,X_RETURN_STATUS => X_RETURN_STATUS);
258 -- RAISE;
259 End Create_repjobxref;
260
261
262 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
263 PROCEDURE Update_repjobxref(
264 P_Api_Version_Number IN NUMBER,
265 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
266 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
269 X_Return_Status OUT NOCOPY VARCHAR2,
270 X_Msg_Count OUT NOCOPY NUMBER,
271 X_Msg_Data OUT NOCOPY VARCHAR2
272 )
273
274 IS
275 /*
276 Cursor C_Get_repjobxref(REPAIR_JOB_XREF_ID Number) IS
277 Select rowid,
278 REPAIR_JOB_XREF_ID,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LAST_UPDATE_LOGIN,
284 REPAIR_LINE_ID,
285 WIP_ENTITY_ID,
286 GROUP_ID,
287 ORGANIZATION_ID,
288 QUANTITY,
289 INVENTORY_ITEM_ID,
290 ITEM_REVISION,
291 OBJECT_VERSION_NUMBER,
292 ATTRIBUTE_CATEGORY,
293 ATTRIBUTE1,
294 ATTRIBUTE2,
295 ATTRIBUTE3,
296 ATTRIBUTE4,
297 ATTRIBUTE5,
298 ATTRIBUTE6,
299 ATTRIBUTE7,
300 ATTRIBUTE8,
301 ATTRIBUTE9,
302 ATTRIBUTE10,
303 ATTRIBUTE11,
304 ATTRIBUTE12,
305 ATTRIBUTE13,
306 ATTRIBUTE14,
307 ATTRIBUTE15,
308 QUANTITY_COMPLETED
309 From CSD_REPAIR_JOB_XREF
310 -- Hint: Developer need to provide Where clause
311 For Update OBJECT_VERSION_NUMBER NOWAIT;
312 -- travi added the OBJECT_VERSION_NUMBER to above line
313 */
314 l_api_name CONSTANT VARCHAR2(30) := 'Update_repjobxref';
315 l_api_version_number CONSTANT NUMBER := 2.0;
316 -- Local Variables
317 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
318 l_ref_REPJOBXREF_rec CSD_repair_job_xref_PVT.REPJOBXREF_Rec_Type;
319 l_tar_REPJOBXREF_rec CSD_repair_job_xref_PVT.REPJOBXREF_Rec_Type := P_REPJOBXREF_Rec;
320 l_rowid ROWID;
321 BEGIN
322 -- Standard Start of API savepoint
323 SAVEPOINT UPDATE_REPJOBXREF_PVT;
324
325 -- Standard call to check for call compatibility.
326 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
327 p_api_version_number,
328 l_api_name,
329 G_PKG_NAME)
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334
335 -- Initialize message list if p_init_msg_list is set to TRUE.
336 IF FND_API.to_Boolean( p_init_msg_list )
337 THEN
338 FND_MSG_PUB.initialize;
339 END IF;
340
341
342 -- Debug Message
343 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'start');
344
345
346 -- Initialize API return status to SUCCESS
347 x_return_status := FND_API.G_RET_STS_SUCCESS;
348
349 --
350 -- Api body
351 --
352 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
353 /*
354 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
355 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
356 THEN
357 AS_CALLOUT_PKG.Update_repjobxref_BU(
358 p_api_version_number => 2.0,
359 p_init_msg_list => FND_API.G_FALSE,
360 p_commit => FND_API.G_FALSE,
361 p_validation_level => p_validation_level,
362 p_identity_salesforce_id => p_identity_salesforce_id,
363 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
364 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
365 x_return_status => x_return_status,
366 x_msg_count => x_msg_count,
367 x_msg_data => x_msg_data);
368 END IF;
369 */
370 /*
371 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
372 p_api_version_number => 2.0
373 ,p_salesforce_id => p_identity_salesforce_id
374 ,x_return_status => x_return_status
375 ,x_msg_count => x_msg_count
376 ,x_msg_data => x_msg_data
377 ,x_sales_member_rec => l_identity_sales_member_rec);
378
379 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382 */
383
384 -- Debug Message
385 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: - Open Cursor to Select');
386
387 /*
388 Open C_Get_repjobxref( l_tar_REPJOBXREF_rec.REPAIR_JOB_XREF_ID);
389
390 -- travi changes
391 Fetch C_Get_repjobxref into
392 l_rowid,
393 l_ref_REPJOBXREF_rec.REPAIR_JOB_XREF_ID,
394 l_ref_REPJOBXREF_rec.CREATED_BY,
395 l_ref_REPJOBXREF_rec.CREATION_DATE,
396 l_ref_REPJOBXREF_rec.LAST_UPDATED_BY,
397 l_ref_REPJOBXREF_rec.LAST_UPDATE_DATE,
398 l_ref_REPJOBXREF_rec.LAST_UPDATE_LOGIN,
399 l_ref_REPJOBXREF_rec.REPAIR_LINE_ID,
400 l_ref_REPJOBXREF_rec.WIP_ENTITY_ID,
401 l_ref_REPJOBXREF_rec.GROUP_ID,
402 l_ref_REPJOBXREF_rec.ORGANIZATION_ID,
403 l_ref_REPJOBXREF_rec.QUANTITY,
404 l_ref_REPJOBXREF_rec.INVENTORY_ITEM_ID,
405 l_ref_REPJOBXREF_rec.ITEM_REVISION,
406 l_ref_REPJOBXREF_rec.OBJECT_VERSION_NUMBER,
407 l_ref_REPJOBXREF_rec.ATTRIBUTE_CATEGORY,
408 l_ref_REPJOBXREF_rec.ATTRIBUTE1,
409 l_ref_REPJOBXREF_rec.ATTRIBUTE2,
410 l_ref_REPJOBXREF_rec.ATTRIBUTE3,
411 l_ref_REPJOBXREF_rec.ATTRIBUTE4,
412 l_ref_REPJOBXREF_rec.ATTRIBUTE5,
413 l_ref_REPJOBXREF_rec.ATTRIBUTE6,
414 l_ref_REPJOBXREF_rec.ATTRIBUTE7,
415 l_ref_REPJOBXREF_rec.ATTRIBUTE8,
416 l_ref_REPJOBXREF_rec.ATTRIBUTE9,
417 l_ref_REPJOBXREF_rec.ATTRIBUTE10,
418 l_ref_REPJOBXREF_rec.ATTRIBUTE11,
419 l_ref_REPJOBXREF_rec.ATTRIBUTE12,
420 l_ref_REPJOBXREF_rec.ATTRIBUTE13,
421 l_ref_REPJOBXREF_rec.ATTRIBUTE14,
422 l_ref_REPJOBXREF_rec.ATTRIBUTE15,
423 l_ref_REPJOBXREF_rec.QUANTITY_COMPLETED;
424
425 If ( C_Get_repjobxref%NOTFOUND) Then
426 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
427 THEN
428 FND_MESSAGE.Set_Name('CSD', 'API_MISSING_UPDATE_TARGET');
429 FND_MESSAGE.Set_Token ('INFO', 'repjobxref', FALSE);
430 FND_MSG_PUB.Add;
431 END IF;
432 raise FND_API.G_EXC_ERROR;
433 END IF;
434 -- Debug Message
435 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: - Close Cursor');
436 Close C_Get_repjobxref;
437 */
438
439
440 If (l_tar_REPJOBXREF_rec.last_update_date is NULL or
441 l_tar_REPJOBXREF_rec.last_update_date = FND_API.G_MISS_Date ) Then
442 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
443 THEN
444 FND_MESSAGE.Set_Name('CSD', 'API_MISSING_ID');
445 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
446 FND_MSG_PUB.ADD;
447 END IF;
448 raise FND_API.G_EXC_ERROR;
449 End if;
450 -- Check Whether record has been changed by someone else
451 If (l_tar_REPJOBXREF_rec.last_update_date <> l_ref_REPJOBXREF_rec.last_update_date) Then
452 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
453 THEN
454 FND_MESSAGE.Set_Name('CSD', 'API_RECORD_CHANGED');
455 FND_MESSAGE.Set_Token('INFO', 'repjobxref', FALSE);
456 FND_MSG_PUB.ADD;
457 END IF;
458 raise FND_API.G_EXC_ERROR;
459 End if;
460
461 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
462 THEN
463 -- Debug message
464 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Validate_repjobxref');
465
466 -- Invoke validation procedures
467 Validate_repjobxref(
468 p_init_msg_list => FND_API.G_FALSE,
469 p_validation_level => p_validation_level,
470 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
471 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
472 x_return_status => x_return_status,
473 x_msg_count => x_msg_count,
474 x_msg_data => x_msg_data);
475 END IF;
476
477 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480
481 -- Hint: Add corresponding Master-Detail business logic here if necessary.
482
483 -- Debug Message
484 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Calling update table handler');
485
486 -- travi changes
487 -- Invoke table handler(CSD_REPAIR_JOB_XREF_PKG.Update_Row)
488 CSD_REPAIR_JOB_XREF_PKG.Update_Row(
489 p_REPAIR_JOB_XREF_ID => p_REPJOBXREF_rec.REPAIR_JOB_XREF_ID,
490 p_CREATED_BY => G_USER_ID,
491 p_CREATION_DATE => SYSDATE,
492 p_LAST_UPDATED_BY => G_USER_ID,
493 p_LAST_UPDATE_DATE => SYSDATE,
494 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
495 p_REPAIR_LINE_ID => p_REPJOBXREF_rec.REPAIR_LINE_ID,
496 p_WIP_ENTITY_ID => p_REPJOBXREF_rec.WIP_ENTITY_ID,
497 p_GROUP_ID => p_REPJOBXREF_rec.GROUP_ID,
498 p_ORGANIZATION_ID => p_REPJOBXREF_rec.ORGANIZATION_ID,
499 p_QUANTITY => p_REPJOBXREF_rec.QUANTITY,
500 p_INVENTORY_ITEM_ID => p_REPJOBXREF_rec.INVENTORY_ITEM_ID,
501 p_ITEM_REVISION => p_REPJOBXREF_rec.ITEM_REVISION,
502 p_OBJECT_VERSION_NUMBER => p_REPJOBXREF_rec.OBJECT_VERSION_NUMBER,
503 p_ATTRIBUTE_CATEGORY => p_REPJOBXREF_rec.ATTRIBUTE_CATEGORY,
504 p_ATTRIBUTE1 => p_REPJOBXREF_rec.ATTRIBUTE1,
505 p_ATTRIBUTE2 => p_REPJOBXREF_rec.ATTRIBUTE2,
506 p_ATTRIBUTE3 => p_REPJOBXREF_rec.ATTRIBUTE3,
507 p_ATTRIBUTE4 => p_REPJOBXREF_rec.ATTRIBUTE4,
508 p_ATTRIBUTE5 => p_REPJOBXREF_rec.ATTRIBUTE5,
509 p_ATTRIBUTE6 => p_REPJOBXREF_rec.ATTRIBUTE6,
510 p_ATTRIBUTE7 => p_REPJOBXREF_rec.ATTRIBUTE7,
511 p_ATTRIBUTE8 => p_REPJOBXREF_rec.ATTRIBUTE8,
512 p_ATTRIBUTE9 => p_REPJOBXREF_rec.ATTRIBUTE9,
513 p_ATTRIBUTE10 => p_REPJOBXREF_rec.ATTRIBUTE10,
514 p_ATTRIBUTE11 => p_REPJOBXREF_rec.ATTRIBUTE11,
515 p_ATTRIBUTE12 => p_REPJOBXREF_rec.ATTRIBUTE12,
516 p_ATTRIBUTE13 => p_REPJOBXREF_rec.ATTRIBUTE13,
517 p_ATTRIBUTE14 => p_REPJOBXREF_rec.ATTRIBUTE14,
518 p_ATTRIBUTE15 => p_REPJOBXREF_rec.ATTRIBUTE15,
519 p_QUANTITY_COMPLETED => p_REPJOBXREF_rec.QUANTITY_COMPLETED);
520 --
521 -- End of API body.
522 --
523
524 -- Standard check for p_commit
525 IF FND_API.to_Boolean( p_commit )
526 THEN
527 COMMIT WORK;
528 END IF;
529
530
531 -- Debug Message
532 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'end');
533
534
535 -- Standard call to get message count and if count is 1, get message info.
536 FND_MSG_PUB.Count_And_Get
537 ( p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540
541 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
542 /*
543 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
544 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
545 THEN
546 AS_CALLOUT_PKG.Update_repjobxref_AU(
547 p_api_version_number => 2.0,
548 p_init_msg_list => FND_API.G_FALSE,
549 p_commit => FND_API.G_FALSE,
550 p_validation_level => p_validation_level,
551 p_identity_salesforce_id => p_identity_salesforce_id,
552 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
553 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
554 x_return_status => x_return_status,
555 x_msg_count => x_msg_count,
556 x_msg_data => x_msg_data);
557 END IF;
558 */
559 EXCEPTION
560 WHEN FND_API.G_EXC_ERROR THEN
561 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
562 P_API_NAME => L_API_NAME
563 ,P_PKG_NAME => G_PKG_NAME
564 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
565 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
566 ,X_MSG_COUNT => X_MSG_COUNT
567 ,X_MSG_DATA => X_MSG_DATA
568 ,X_RETURN_STATUS => X_RETURN_STATUS);
569 -- RAISE;
570
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
573 P_API_NAME => L_API_NAME
574 ,P_PKG_NAME => G_PKG_NAME
575 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
576 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
577 ,X_MSG_COUNT => X_MSG_COUNT
578 ,X_MSG_DATA => X_MSG_DATA
579 ,X_RETURN_STATUS => X_RETURN_STATUS);
580 -- RAISE;
581
582 WHEN OTHERS THEN
583 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
584 P_API_NAME => L_API_NAME
585 ,P_PKG_NAME => G_PKG_NAME
586 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
587 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
588 ,X_MSG_COUNT => X_MSG_COUNT
589 ,X_MSG_DATA => X_MSG_DATA
590 ,X_RETURN_STATUS => X_RETURN_STATUS);
591 -- RAISE;
592 End Update_repjobxref;
593
594
595 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
596 -- The Master delete procedure may not be needed depends on different business requirements.
597 PROCEDURE Delete_repjobxref(
598 P_Api_Version_Number IN NUMBER,
599 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
600 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
601 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
602 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
603 X_Return_Status OUT NOCOPY VARCHAR2,
604 X_Msg_Count OUT NOCOPY NUMBER,
605 X_Msg_Data OUT NOCOPY VARCHAR2
606 )
607
608 IS
609 l_api_name CONSTANT VARCHAR2(30) := 'Delete_repjobxref';
610 l_api_version_number CONSTANT NUMBER := 2.0;
611 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
612 BEGIN
613 -- Standard Start of API savepoint
614 SAVEPOINT DELETE_REPJOBXREF_PVT;
615
616 -- Standard call to check for call compatibility.
617 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
618 p_api_version_number,
619 l_api_name,
620 G_PKG_NAME)
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625
626 -- Initialize message list if p_init_msg_list is set to TRUE.
627 IF FND_API.to_Boolean( p_init_msg_list )
628 THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631
632
633 -- Debug Message
634 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'start');
635
636
637 -- Initialize API return status to SUCCESS
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 --
641 -- Api body
642 --
643 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
644 /*
645 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
646 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
647 THEN
648 AS_CALLOUT_PKG.Delete_repjobxref_BD(
649 p_api_version_number => 2.0,
650 p_init_msg_list => FND_API.G_FALSE,
651 p_commit => FND_API.G_FALSE,
652 p_validation_level => p_validation_level,
653 p_identity_salesforce_id => p_identity_salesforce_id,
654 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
655 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
656 x_return_status => x_return_status,
657 x_msg_count => x_msg_count,
658 x_msg_data => x_msg_data);
659 END IF;
660 */
661 /*
662
663 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
664 p_api_version_number => 2.0
665 ,p_salesforce_id => p_identity_salesforce_id
666 ,x_return_status => x_return_status
667 ,x_msg_count => x_msg_count
668 ,x_msg_data => x_msg_data
669 ,x_sales_member_rec => l_identity_sales_member_rec);
670
671 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
672 RAISE FND_API.G_EXC_ERROR;
673 END IF;
674 */
675 -- Debug Message
676 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Calling delete table handler');
677
678 -- Invoke table handler(CSD_REPAIR_JOB_XREF_PKG.Delete_Row)
679 CSD_REPAIR_JOB_XREF_PKG.Delete_Row(
680 p_REPAIR_JOB_XREF_ID => p_REPJOBXREF_rec.REPAIR_JOB_XREF_ID);
681 --
682 -- End of API body
683 --
684
685 -- Standard check for p_commit
686 IF FND_API.to_Boolean( p_commit )
687 THEN
688 COMMIT WORK;
689 END IF;
690
691
692 -- Debug Message
693 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'end');
694
695
696 -- Standard call to get message count and if count is 1, get message info.
697 FND_MSG_PUB.Count_And_Get
698 ( p_count => x_msg_count,
699 p_data => x_msg_data
700 );
701
702 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
703 /*
704 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
705 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
706 THEN
707 AS_CALLOUT_PKG.Delete_repjobxref_AD(
708 p_api_version_number => 2.0,
709 p_init_msg_list => FND_API.G_FALSE,
710 p_commit => FND_API.G_FALSE,
711 p_validation_level => p_validation_level,
712 p_identity_salesforce_id => p_identity_salesforce_id,
713 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
714 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
715 x_return_status => x_return_status,
716 x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data);
718 END IF;
719 */
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
723 P_API_NAME => L_API_NAME
724 ,P_PKG_NAME => G_PKG_NAME
725 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
726 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
727 ,X_MSG_COUNT => X_MSG_COUNT
728 ,X_MSG_DATA => X_MSG_DATA
729 ,X_RETURN_STATUS => X_RETURN_STATUS);
730 -- RAISE;
731
732 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
733 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
734 P_API_NAME => L_API_NAME
735 ,P_PKG_NAME => G_PKG_NAME
736 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
737 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
738 ,X_MSG_COUNT => X_MSG_COUNT
739 ,X_MSG_DATA => X_MSG_DATA
740 ,X_RETURN_STATUS => X_RETURN_STATUS);
741 -- RAISE;
742
743 WHEN OTHERS THEN
744 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
745 P_API_NAME => L_API_NAME
746 ,P_PKG_NAME => G_PKG_NAME
747 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
748 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
749 ,X_MSG_COUNT => X_MSG_COUNT
750 ,X_MSG_DATA => X_MSG_DATA
751 ,X_RETURN_STATUS => X_RETURN_STATUS);
752 -- RAISE;
753 End Delete_repjobxref;
754
755
756 -- This procudure defines the columns for the Dynamic SQL.
757 PROCEDURE Define_Columns(
758 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
759 p_cur_get_REPJOBXREF IN NUMBER
760 )
761 IS
762 BEGIN
763 -- Debug Message
764 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Define Columns Begins');
765
766 -- travi changes
767 -- define all columns for CSD_REPAIR_JOB_XREF_V view
768 dbms_sql.define_column(p_cur_get_REPJOBXREF, 1, P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID);
769 dbms_sql.define_column(p_cur_get_REPJOBXREF, 2, P_REPJOBXREF_Rec.REPAIR_LINE_ID);
770 dbms_sql.define_column(p_cur_get_REPJOBXREF, 3, P_REPJOBXREF_Rec.WIP_ENTITY_ID);
771 dbms_sql.define_column(p_cur_get_REPJOBXREF, 4, P_REPJOBXREF_Rec.GROUP_ID);
772 dbms_sql.define_column(p_cur_get_REPJOBXREF, 5, P_REPJOBXREF_Rec.ORGANIZATION_ID);
773 dbms_sql.define_column(p_cur_get_REPJOBXREF, 6, P_REPJOBXREF_Rec.QUANTITY);
774 dbms_sql.define_column(p_cur_get_REPJOBXREF, 7, P_REPJOBXREF_Rec.INVENTORY_ITEM_ID);
775 dbms_sql.define_column(p_cur_get_REPJOBXREF, 8, P_REPJOBXREF_Rec.ITEM_REVISION, 3);
776 dbms_sql.define_column(p_cur_get_REPJOBXREF, 9, P_REPJOBXREF_Rec.OBJECT_VERSION_NUMBER);
777 dbms_sql.define_column(p_cur_get_REPJOBXREF, 10, P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY, 30);
778 dbms_sql.define_column(p_cur_get_REPJOBXREF, 11, P_REPJOBXREF_Rec.ATTRIBUTE1, 150);
779 dbms_sql.define_column(p_cur_get_REPJOBXREF, 12, P_REPJOBXREF_Rec.ATTRIBUTE2, 150);
780 dbms_sql.define_column(p_cur_get_REPJOBXREF, 13, P_REPJOBXREF_Rec.ATTRIBUTE3, 150);
781 dbms_sql.define_column(p_cur_get_REPJOBXREF, 14, P_REPJOBXREF_Rec.ATTRIBUTE4, 150);
782 dbms_sql.define_column(p_cur_get_REPJOBXREF, 15, P_REPJOBXREF_Rec.ATTRIBUTE5, 150);
783 dbms_sql.define_column(p_cur_get_REPJOBXREF, 16, P_REPJOBXREF_Rec.ATTRIBUTE6, 150);
784 dbms_sql.define_column(p_cur_get_REPJOBXREF, 17, P_REPJOBXREF_Rec.ATTRIBUTE7, 150);
785 dbms_sql.define_column(p_cur_get_REPJOBXREF, 18, P_REPJOBXREF_Rec.ATTRIBUTE8, 150);
786 dbms_sql.define_column(p_cur_get_REPJOBXREF, 19, P_REPJOBXREF_Rec.ATTRIBUTE9, 150);
787 dbms_sql.define_column(p_cur_get_REPJOBXREF, 20, P_REPJOBXREF_Rec.ATTRIBUTE10, 150);
788 dbms_sql.define_column(p_cur_get_REPJOBXREF, 21, P_REPJOBXREF_Rec.ATTRIBUTE11, 150);
789 dbms_sql.define_column(p_cur_get_REPJOBXREF, 22, P_REPJOBXREF_Rec.ATTRIBUTE12, 150);
790 dbms_sql.define_column(p_cur_get_REPJOBXREF, 23, P_REPJOBXREF_Rec.ATTRIBUTE13, 150);
791 dbms_sql.define_column(p_cur_get_REPJOBXREF, 24, P_REPJOBXREF_Rec.ATTRIBUTE14, 150);
792 dbms_sql.define_column(p_cur_get_REPJOBXREF, 25, P_REPJOBXREF_Rec.ATTRIBUTE15, 150);
793
794 dbms_sql.define_column(p_cur_get_REPJOBXREF, 26, P_REPJOBXREF_Rec.QUANTITY_COMPLETED);
795
796 -- Debug Message
797 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Define Columns Ends');
798 END Define_Columns;
799
800 -- This procudure gets column values by the Dynamic SQL.
801 PROCEDURE Get_Column_Values(
802 p_cur_get_REPJOBXREF IN NUMBER,
803 X_REPJOBXREF_Rec OUT NOCOPY REPJOBXREF_Rec_Type
804 )
805 IS
806 BEGIN
807 -- Debug Message
808 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Get Column Values Begins');
809
810 -- travi changes
811 -- get all column values for CSD_REPAIR_JOB_XREF_V table
812 dbms_sql.column_value(p_cur_get_REPJOBXREF, 1, X_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID);
813 dbms_sql.column_value(p_cur_get_REPJOBXREF, 2, X_REPJOBXREF_Rec.REPAIR_LINE_ID);
814 dbms_sql.column_value(p_cur_get_REPJOBXREF, 3, X_REPJOBXREF_Rec.WIP_ENTITY_ID);
815 dbms_sql.column_value(p_cur_get_REPJOBXREF, 4, X_REPJOBXREF_Rec.GROUP_ID);
816 dbms_sql.column_value(p_cur_get_REPJOBXREF, 5, X_REPJOBXREF_Rec.ORGANIZATION_ID);
817 dbms_sql.column_value(p_cur_get_REPJOBXREF, 6, X_REPJOBXREF_Rec.QUANTITY);
818 dbms_sql.column_value(p_cur_get_REPJOBXREF, 7, X_REPJOBXREF_Rec.INVENTORY_ITEM_ID);
819 dbms_sql.column_value(p_cur_get_REPJOBXREF, 8, X_REPJOBXREF_Rec.ITEM_REVISION);
820 dbms_sql.column_value(p_cur_get_REPJOBXREF, 9, X_REPJOBXREF_Rec.OBJECT_VERSION_NUMBER);
821 dbms_sql.column_value(p_cur_get_REPJOBXREF, 10, X_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY);
822 dbms_sql.column_value(p_cur_get_REPJOBXREF, 11, X_REPJOBXREF_Rec.ATTRIBUTE1);
823 dbms_sql.column_value(p_cur_get_REPJOBXREF, 12, X_REPJOBXREF_Rec.ATTRIBUTE2);
824 dbms_sql.column_value(p_cur_get_REPJOBXREF, 13, X_REPJOBXREF_Rec.ATTRIBUTE3);
825 dbms_sql.column_value(p_cur_get_REPJOBXREF, 14, X_REPJOBXREF_Rec.ATTRIBUTE4);
826 dbms_sql.column_value(p_cur_get_REPJOBXREF, 15, X_REPJOBXREF_Rec.ATTRIBUTE5);
827 dbms_sql.column_value(p_cur_get_REPJOBXREF, 16, X_REPJOBXREF_Rec.ATTRIBUTE6);
828 dbms_sql.column_value(p_cur_get_REPJOBXREF, 17, X_REPJOBXREF_Rec.ATTRIBUTE7);
829 dbms_sql.column_value(p_cur_get_REPJOBXREF, 18, X_REPJOBXREF_Rec.ATTRIBUTE8);
830 dbms_sql.column_value(p_cur_get_REPJOBXREF, 19, X_REPJOBXREF_Rec.ATTRIBUTE9);
831 dbms_sql.column_value(p_cur_get_REPJOBXREF, 20, X_REPJOBXREF_Rec.ATTRIBUTE10);
832 dbms_sql.column_value(p_cur_get_REPJOBXREF, 21, X_REPJOBXREF_Rec.ATTRIBUTE11);
833 dbms_sql.column_value(p_cur_get_REPJOBXREF, 22, X_REPJOBXREF_Rec.ATTRIBUTE12);
834 dbms_sql.column_value(p_cur_get_REPJOBXREF, 23, X_REPJOBXREF_Rec.ATTRIBUTE13);
835 dbms_sql.column_value(p_cur_get_REPJOBXREF, 24, X_REPJOBXREF_Rec.ATTRIBUTE14);
836 dbms_sql.column_value(p_cur_get_REPJOBXREF, 25, X_REPJOBXREF_Rec.ATTRIBUTE15);
837
838 dbms_sql.column_value(p_cur_get_REPJOBXREF, 26, X_REPJOBXREF_Rec.QUANTITY_COMPLETED);
839
840 -- Debug Message
841 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Get Column Values Ends');
842 END Get_Column_Values;
843
844 PROCEDURE Gen_REPJOBXREF_order_cl(
845 p_order_by_rec IN REPJOBXREF_sort_rec_type,
846 x_order_by_cl OUT NOCOPY VARCHAR2,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2
850 )
851 IS
852 l_order_by_cl VARCHAR2(1000) := NULL;
853 l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
854 BEGIN
855 -- Debug Message
856 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Order by Begins');
857
858 -- Hint: Developer should add more statements according to CSD_sort_rec_type
859 -- Ex:
860 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
861 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
862
863 -- Debug Message
864 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
865
866 JTF_PLSQL_API.Translate_OrderBy(
867 p_api_version_number => 1.0
868 ,p_init_msg_list => FND_API.G_FALSE
869 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
870 ,p_order_by_tbl => l_util_order_by_tbl
871 ,x_order_by_clause => l_order_by_cl
872 ,x_return_status => x_return_status
873 ,x_msg_count => x_msg_count
874 ,x_msg_data => x_msg_data);
875
876 IF(l_order_by_cl IS NOT NULL) THEN
877 x_order_by_cl := 'order by' || l_order_by_cl;
878 ELSE
879 x_order_by_cl := NULL;
880 END IF;
881
882 -- Debug Message
883 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Order by Ends');
884 END Gen_REPJOBXREF_order_cl;
885
886 -- This procedure bind the variables for the Dynamic SQL
887 PROCEDURE Bind(
888 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
889 -- Hint: Add more binding variables here
890 p_cur_get_REPJOBXREF IN NUMBER
891 )
892 IS
893 BEGIN
894 -- Bind variables
895 -- Only those that are not NULL
896 -- Debug Message
897 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Bind Variables Begins');
898
899 -- The following example applies to all columns,
900 -- developers can copy and paste them.
901 IF( (P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID IS NOT NULL) AND (P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID <> FND_API.G_MISS_NUM) )
902 THEN
903 DBMS_SQL.BIND_VARIABLE(p_cur_get_REPJOBXREF, ':p_REPAIR_JOB_XREF_ID', P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID);
904 END IF;
905
906 -- Debug Message
907 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Bind Variables Ends');
908 END Bind;
909
910 PROCEDURE Gen_Select(
911 x_select_cl OUT NOCOPY VARCHAR2
912 )
913 IS
914 BEGIN
915 -- Debug Message
916 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Select Begins');
917
918 -- travi changes
919 x_select_cl := 'Select ' ||
920 'CSD_REPAIR_JOB_XREF_V.REPAIR_JOB_XREF_ID,' ||
921 'CSD_REPAIR_JOB_XREF_V.CREATED_BY,' ||
922 'CSD_REPAIR_JOB_XREF_V.CREATION_DATE,' ||
923 'CSD_REPAIR_JOB_XREF_V.LAST_UPDATED_BY,' ||
924 'CSD_REPAIR_JOB_XREF_V.LAST_UPDATE_DATE,' ||
925 'CSD_REPAIR_JOB_XREF_V.LAST_UPDATE_LOGIN,' ||
926 'CSD_REPAIR_JOB_XREF_V.REPAIR_LINE_ID,' ||
927 'CSD_REPAIR_JOB_XREF_V.WIP_ENTITY_ID,' ||
928 'CSD_REPAIR_JOB_XREF_V.GROUP_ID,' ||
929 'CSD_REPAIR_JOB_XREF_V.ORGANIZATION_ID,' ||
930 'CSD_REPAIR_JOB_XREF_V.QUANTITY,' ||
931 'CSD_REPAIR_JOB_XREF_V.INVENTORY_ITEM_ID,' ||
932 'CSD_REPAIR_JOB_XREF_V.ITEM_REVISION,' ||
933 'CSD_REPAIR_JOB_XREF_V.OBJECT_VERSION_NUMBER,' ||
934 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE_CATEGORY,' ||
935 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE1,' ||
936 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE2,' ||
937 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE3,' ||
938 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE4,' ||
939 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE5,' ||
940 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE6,' ||
941 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE7,' ||
942 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE8,' ||
943 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE9,' ||
944 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE10,' ||
945 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE11,' ||
946 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE12,' ||
947 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE13,' ||
948 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE14,' ||
949 'CSD_REPAIR_JOB_XREF_V.ATTRIBUTE15,' ||
950 'CSD_REPAIR_JOB_XREF_V.QUANTITY_COMPLETED,' ||
951 'from CSD_REPAIR_JOB_XREF_V';
952 -- Debug Message
953 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Select Ends');
954
955 END Gen_Select;
956
957 PROCEDURE Gen_REPJOBXREF_Where(
958 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
959 x_REPJOBXREF_where OUT NOCOPY VARCHAR2
960 )
961 IS
962 -- cursors to check if wildcard values '%' and '_' have been passed
963 -- as item values
964 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
965 SELECT INSTR(p_rec_item, '%', 1, 1)
966 FROM DUAL;
967 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
968 SELECT INSTR(p_rec_item, '_', 1, 1)
969 FROM DUAL;
970
971 -- return values from cursors
972 str_csr1 NUMBER;
973 str_csr2 NUMBER;
974 l_operator VARCHAR2(10);
975 BEGIN
976 -- Debug Message
977 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Where Begins');
978
979 -- There are three examples for each kind of datatype:
980 -- NUMBER, DATE, VARCHAR2.
981 -- Developer can copy and paste the following codes for your own record.
982
983 -- example for NUMBER datatype
984 IF( (P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID IS NOT NULL) AND (P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID <> FND_API.G_MISS_NUM) )
985 THEN
986 IF(x_REPJOBXREF_where IS NULL) THEN
987 x_REPJOBXREF_where := 'Where';
988 ELSE
989 x_REPJOBXREF_where := x_REPJOBXREF_where || ' AND ';
990 END IF;
991 x_REPJOBXREF_where := x_REPJOBXREF_where || 'P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID = :p_REPAIR_JOB_XREF_ID';
992 END IF;
993
994 -- example for DATE datatype
995 IF( (P_REPJOBXREF_Rec.CREATION_DATE IS NOT NULL) AND (P_REPJOBXREF_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
996 THEN
997 -- check if item value contains '%' wildcard
998 OPEN c_chk_str1(P_REPJOBXREF_Rec.CREATION_DATE);
999 FETCH c_chk_str1 INTO str_csr1;
1000 CLOSE c_chk_str1;
1001
1002 IF(str_csr1 <> 0) THEN
1003 l_operator := ' LIKE ';
1004 ELSE
1005 l_operator := ' = ';
1006 END IF;
1007
1008 -- check if item value contains '_' wildcard
1009 OPEN c_chk_str2(P_REPJOBXREF_Rec.CREATION_DATE);
1010 FETCH c_chk_str2 INTO str_csr2;
1011 CLOSE c_chk_str2;
1012
1013 IF(str_csr2 <> 0) THEN
1014 l_operator := ' LIKE ';
1015 ELSE
1016 l_operator := ' = ';
1017 END IF;
1018
1019 IF(x_REPJOBXREF_where IS NULL) THEN
1020 x_REPJOBXREF_where := 'Where ';
1021 ELSE
1022 x_REPJOBXREF_where := x_REPJOBXREF_where || ' AND ';
1023 END IF;
1024 x_REPJOBXREF_where := x_REPJOBXREF_where || 'P_REPJOBXREF_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
1025 END IF;
1026
1027 -- example for VARCHAR2 datatype
1028 IF( (P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY IS NOT NULL) AND (P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR) )
1029 THEN
1030 -- check if item value contains '%' wildcard
1031 OPEN c_chk_str1(P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY);
1032 FETCH c_chk_str1 INTO str_csr1;
1033 CLOSE c_chk_str1;
1034
1035 IF(str_csr1 <> 0) THEN
1036 l_operator := ' LIKE ';
1037 ELSE
1038 l_operator := ' = ';
1039 END IF;
1040
1041 -- check if item value contains '_' wildcard
1042 OPEN c_chk_str2(P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY);
1043 FETCH c_chk_str2 INTO str_csr2;
1044 CLOSE c_chk_str2;
1045
1046 IF(str_csr2 <> 0) THEN
1047 l_operator := ' LIKE ';
1048 ELSE
1049 l_operator := ' = ';
1050 END IF;
1051
1052 IF(x_REPJOBXREF_where IS NULL) THEN
1053 x_REPJOBXREF_where := 'Where ';
1054 ELSE
1055 x_REPJOBXREF_where := x_REPJOBXREF_where || ' AND ';
1056 END IF;
1057 x_REPJOBXREF_where := x_REPJOBXREF_where || 'P_REPJOBXREF_Rec.ATTRIBUTE_CATEGORY ' || l_operator || ' :p_ATTRIBUTE_CATEGORY';
1058 END IF;
1059
1060 -- Add more IF statements for each column below
1061
1062 -- Debug Message
1063 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generate Where Ends');
1064
1065 END Gen_REPJOBXREF_Where;
1066
1067 PROCEDURE Get_repjobxref(
1068 P_Api_Version_Number IN NUMBER,
1069 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1070 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1071 P_identity_salesforce_id IN NUMBER := NULL,
1072 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
1073 -- Hint: Add list of bind variables here
1074 p_rec_requested IN NUMBER := G_DEFAULT_NUM_REC_FETCH,
1075 p_start_rec_prt IN NUMBER := 1,
1076 p_return_tot_count IN NUMBER := FND_API.G_FALSE,
1077 -- Hint: user defined record type
1078 p_order_by_rec IN REPJOBXREF_sort_rec_type,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_msg_count OUT NOCOPY NUMBER,
1081 x_msg_data OUT NOCOPY VARCHAR2,
1082 X_REPJOBXREF_Tbl OUT NOCOPY REPJOBXREF_Tbl_Type,
1083 x_returned_rec_count OUT NOCOPY NUMBER,
1084 x_next_rec_ptr OUT NOCOPY NUMBER,
1085 x_tot_rec_count OUT NOCOPY NUMBER
1086 -- other optional parameters
1087 -- x_tot_rec_amount OUT NOCOPY NUMBER
1088 )
1089
1090 IS
1091 l_api_name CONSTANT VARCHAR2(30) := 'Get_repjobxref';
1092 l_api_version_number CONSTANT NUMBER := 2.0;
1093
1094 -- Local identity variables
1095 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1096
1097 -- Local record counters
1098 l_returned_rec_count NUMBER := 0; -- number of records returned in x_X_REPJOBXREF_Rec
1099 l_next_record_ptr NUMBER := 1;
1100 l_ignore NUMBER;
1101
1102 -- total number of records accessable by caller
1103 l_tot_rec_count NUMBER := 0;
1104 l_tot_rec_amount NUMBER := 0;
1105
1106 -- Status local variables
1107 l_return_status VARCHAR2(1); -- Return value from procedures
1108 l_return_status_full VARCHAR2(1); -- Calculated return status from
1109
1110 -- Dynamic SQL statement elements
1111 l_cur_get_REPJOBXREF NUMBER;
1112 l_select_cl VARCHAR2(2000) := '';
1113 l_order_by_cl VARCHAR2(2000);
1114 l_REPJOBXREF_where VARCHAR2(2000) := '';
1115
1116 -- For flex field query
1117 l_flex_where_tbl_type AS_FOUNDATION_PVT.flex_where_tbl_type;
1118 l_flex_where VARCHAR2(2000) := NULL;
1119 l_counter NUMBER;
1120
1121 -- Local scratch record
1122 l_REPJOBXREF_rec REPJOBXREF_Rec_Type;
1123 l_crit_REPJOBXREF_rec REPJOBXREF_Rec_Type;
1124 BEGIN
1125 -- Standard Start of API savepoint
1126 SAVEPOINT GET_REPJOBXREF_PVT;
1127
1128 -- Standard call to check for call compatibility.
1129 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1130 p_api_version_number,
1131 l_api_name,
1132 G_PKG_NAME)
1133 THEN
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137
1138 -- Initialize message list if p_init_msg_list is set to TRUE.
1139 IF FND_API.to_Boolean( p_init_msg_list )
1140 THEN
1141 FND_MSG_PUB.initialize;
1142 END IF;
1143
1144
1145 -- Debug Message
1146 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'start');
1147
1148
1149 -- Initialize API return status to SUCCESS
1150 x_return_status := FND_API.G_RET_STS_SUCCESS;
1151
1152 --
1153 -- Api body
1154 --
1155
1156 /* AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1157 p_api_version_number => 2.0
1158 ,p_salesforce_id => p_identity_salesforce_id
1159 ,x_return_status => x_return_status
1160 ,x_msg_count => x_msg_count
1161 ,x_msg_data => x_msg_data
1162 ,x_sales_member_rec => l_identity_sales_member_rec);
1163 */
1164 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1165 RAISE FND_API.G_EXC_ERROR;
1166 END IF;
1167
1168 -- *************************************************
1169 -- Generate Dynamic SQL based on criteria passed in.
1170 -- Doing this for performance. Indexes are disabled when using NVL within static SQL statement.
1171 -- Ignore condition when criteria is NULL
1172 -- Debug Message
1173 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Generating Dsql');
1174 -- Generate Select clause and From clause
1175 -- Hint: Developer should modify Gen_Select procedure.
1176 Gen_Select(l_select_cl);
1177
1178 -- Hint: Developer should modify and implement Gen_Where precedure.
1179 Gen_REPJOBXREF_Where(l_crit_REPJOBXREF_rec, l_REPJOBXREF_where);
1180
1181 -- Generate Where clause for flex fields
1182 -- Hint: Developer can use table/view alias in the From clause generated in Gen_Select procedure
1183
1184 FOR l_counter IN 1..15 LOOP
1185 l_flex_where_tbl_type(l_counter).name := 'CSD_REPAIR_JOB_XREF_V.attribute' || l_counter;
1186 END LOOP;
1187
1188 l_flex_where_tbl_type(16).name := 'CSD_REPAIR_JOB_XREF_V.attribute_category';
1189 l_flex_where_tbl_type(1).value := P_REPJOBXREF_Rec.attribute1;
1190 l_flex_where_tbl_type(2).value := P_REPJOBXREF_Rec.attribute2;
1191 l_flex_where_tbl_type(3).value := P_REPJOBXREF_Rec.attribute3;
1192 l_flex_where_tbl_type(4).value := P_REPJOBXREF_Rec.attribute4;
1193 l_flex_where_tbl_type(5).value := P_REPJOBXREF_Rec.attribute5;
1194 l_flex_where_tbl_type(6).value := P_REPJOBXREF_Rec.attribute6;
1195 l_flex_where_tbl_type(7).value := P_REPJOBXREF_Rec.attribute7;
1196 l_flex_where_tbl_type(8).value := P_REPJOBXREF_Rec.attribute8;
1197 l_flex_where_tbl_type(9).value := P_REPJOBXREF_Rec.attribute9;
1198 l_flex_where_tbl_type(10).value := P_REPJOBXREF_Rec.attribute10;
1199 l_flex_where_tbl_type(11).value := P_REPJOBXREF_Rec.attribute11;
1200 l_flex_where_tbl_type(12).value := P_REPJOBXREF_Rec.attribute12;
1201 l_flex_where_tbl_type(13).value := P_REPJOBXREF_Rec.attribute13;
1202 l_flex_where_tbl_type(14).value := P_REPJOBXREF_Rec.attribute14;
1203 l_flex_where_tbl_type(15).value := P_REPJOBXREF_Rec.attribute15;
1204 l_flex_where_tbl_type(16).value := P_REPJOBXREF_Rec.attribute_category;
1205
1206
1207 AS_FOUNDATION_PVT.Gen_Flexfield_Where(
1208 p_flex_where_tbl_type => l_flex_where_tbl_type,
1209 x_flex_where_clause => l_flex_where);
1210
1211 -- Hint: if master/detail relationship, generate Where clause for lines level criteria
1212 -- Generate order by clause
1213 Gen_REPJOBXREF_order_cl(p_order_by_rec, l_order_by_cl, l_return_status, x_msg_count, x_msg_data);
1214
1215 -- Debug Message
1216 JTF_PLSQL_API.Debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Open and Parse Cursor');
1217
1218 l_cur_get_REPJOBXREF := dbms_sql.open_cursor;
1219
1220 -- Hint: concatenate all where clause (include flex field/line level if any applies)
1221 -- dbms_sql.parse(l_cur_get_REPJOBXREF, l_select_cl || l_head_where || l_flex_where || l_lines_where
1222 -- || l_steam_where || l_order_by_cl, dbms_sql.native);
1223
1224 -- Hint: Developer should implement Bind Variables procedure according to bind variables in the parameter list
1225 -- Bind(l_crit_REPJOBXREF_rec, l_crit_exp_purchase_rec, p_start_date, p_end_date,
1226 -- p_crit_exp_salesforce_id, p_crit_ptr_salesforce_id,
1227 -- p_crit_salesgroup_id, p_crit_ptr_manager_person_id,
1228 -- p_win_prob_ceiling, p_win_prob_floor,
1229 -- p_total_amt_ceiling, p_total_amt_floor,
1230 -- l_cur_get_REPJOBXREF);
1231
1232 -- Bind flexfield variables
1233 AS_FOUNDATION_PVT.Bind_Flexfield_Where(
1234 p_cursor_id => l_cur_get_REPJOBXREF,
1235 p_flex_where_tbl_type => l_flex_where_tbl_type);
1236
1237 -- Define all Select Columns
1238 Define_Columns(l_crit_REPJOBXREF_rec, l_cur_get_REPJOBXREF);
1239
1240 -- Execute
1241 -- Debug Message
1242 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Execute Dsql');
1243
1244 l_ignore := dbms_sql.execute(l_cur_get_REPJOBXREF);
1245
1246 -- Debug Message
1247 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: Fetch Results');
1248
1249 -- This loop is here to avoid calling a function in the main
1250 -- cursor. Basically, calling this function seems to disable
1251 -- index, but verification is needed. This is a good
1252 -- place to optimize the code if required.
1253
1254 LOOP
1255 -- 1. There are more rows in the cursor.
1256 -- 2. User does not care about total records, and we need to return more.
1257 -- 3. Or user cares about total number of records.
1258 IF((dbms_sql.fetch_rows(l_cur_get_REPJOBXREF)>0) AND ((p_return_tot_count = FND_API.G_TRUE)
1259 OR (l_returned_rec_count<p_rec_requested) OR (p_rec_requested=FND_API.G_MISS_NUM)))
1260 THEN
1261 -- Debug Message
1262 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: found');
1263
1264 -- Hint: Developer need to implement this part
1265 -- dbms_sql.column_value(l_cur_get_opp, 1, l_opp_rec.lead_id);
1266 -- dbms_sql.column_value(l_cur_get_opp, 7, l_opp_rec.customer_id);
1267 -- dbms_sql.column_value(l_cur_get_opp, 8, l_opp_rec.address_id);
1268
1269 -- Hint: Check access for this record (e.x. AS_ACCESS_PVT.Has_OpportunityAccess)
1270 -- Return this particular record if
1271 -- 1. The caller has access to record.
1272 -- 2. The number of records returned < number of records caller requested in this run.
1273 -- 3. The record comes AFTER or Equal to the start index the caller requested.
1274
1275 -- Developer should check whether there is access privilege here
1276 -- IF(l_REPJOBXREF_rec.member_access <> 'N' OR l_REPJOBXREF_rec.member_role <> 'N') THEN
1277 Get_Column_Values(l_cur_get_REPJOBXREF, l_REPJOBXREF_rec);
1278 l_tot_rec_count := l_tot_rec_count + 1;
1279 IF(l_returned_rec_count < p_rec_requested) AND (l_tot_rec_count >= p_start_rec_prt) THEN
1280 l_returned_rec_count := l_returned_rec_count + 1;
1281 -- insert into resultant tables
1282 X_REPJOBXREF_Tbl(l_returned_rec_count) := l_REPJOBXREF_rec;
1283 END IF;
1284 -- END IF;
1285 ELSE
1286 EXIT;
1287 END IF;
1288 END LOOP;
1289 --
1290 -- End of API body
1291 --
1292
1293 -- Debug Message
1294 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'end');
1295
1296
1297 -- Standard call to get message count and if count is 1, get message info.
1298 FND_MSG_PUB.Count_And_Get
1299 ( p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302
1303 EXCEPTION
1304 WHEN FND_API.G_EXC_ERROR THEN
1305 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1306 P_API_NAME => L_API_NAME
1307 ,P_PKG_NAME => G_PKG_NAME
1308 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1309 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1310 ,X_MSG_COUNT => X_MSG_COUNT
1311 ,X_MSG_DATA => X_MSG_DATA
1312 ,X_RETURN_STATUS => X_RETURN_STATUS);
1313 -- RAISE;
1314
1315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1316 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1317 P_API_NAME => L_API_NAME
1318 ,P_PKG_NAME => G_PKG_NAME
1319 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1320 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1321 ,X_MSG_COUNT => X_MSG_COUNT
1322 ,X_MSG_DATA => X_MSG_DATA
1323 ,X_RETURN_STATUS => X_RETURN_STATUS);
1324 -- RAISE;
1325
1326 WHEN OTHERS THEN
1327 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1328 P_API_NAME => L_API_NAME
1329 ,P_PKG_NAME => G_PKG_NAME
1330 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1331 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1332 ,X_MSG_COUNT => X_MSG_COUNT
1333 ,X_MSG_DATA => X_MSG_DATA
1334 ,X_RETURN_STATUS => X_RETURN_STATUS);
1335 -- RAISE;
1336 End Get_repjobxref;
1337
1338
1339 -- Item-level validation procedures
1340 PROCEDURE Validate_REPAIR_JOB_XREF_ID (
1341 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1342 P_Validation_mode IN VARCHAR2,
1343 P_REPAIR_JOB_XREF_ID IN NUMBER,
1344 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1345 X_Return_Status OUT NOCOPY VARCHAR2,
1346 X_Msg_Count OUT NOCOPY NUMBER,
1347 X_Msg_Data OUT NOCOPY VARCHAR2
1348 )
1349 IS
1350 BEGIN
1351
1352 -- Initialize message list if p_init_msg_list is set to TRUE.
1353 IF FND_API.to_Boolean( p_init_msg_list )
1354 THEN
1355 FND_MSG_PUB.initialize;
1356 END IF;
1357
1358
1359 -- Initialize API return status to SUCCESS
1360 x_return_status := FND_API.G_RET_STS_SUCCESS;
1361
1362 -- validate NOT NULL column
1363 IF(p_REPAIR_JOB_XREF_ID is NULL)
1364 THEN
1365 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(REPAIR_JOB_XREF_ID)');
1366 x_return_status := FND_API.G_RET_STS_ERROR;
1367 END IF;
1368
1369 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1370 THEN
1371 -- Hint: Validate data
1372 -- IF p_REPAIR_JOB_XREF_ID is not NULL and p_REPAIR_JOB_XREF_ID <> G_MISS_CHAR
1373 -- verify if data is valid
1374 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1375 NULL;
1376 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1377 THEN
1378 -- Hint: Validate data
1379 -- IF p_REPAIR_JOB_XREF_ID <> G_MISS_CHAR
1380 -- verify if data is valid
1381 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1382 NULL;
1383 END IF;
1384
1385 -- Standard call to get message count and if count is 1, get message info.
1386 FND_MSG_PUB.Count_And_Get
1387 ( p_count => x_msg_count,
1388 p_data => x_msg_data
1389 );
1390
1391 END Validate_REPAIR_JOB_XREF_ID;
1392
1393
1394 PROCEDURE Validate_REPAIR_LINE_ID (
1395 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1396 P_Validation_mode IN VARCHAR2,
1397 P_REPAIR_LINE_ID IN NUMBER,
1398 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1399 X_Return_Status OUT NOCOPY VARCHAR2,
1400 X_Msg_Count OUT NOCOPY NUMBER,
1401 X_Msg_Data OUT NOCOPY VARCHAR2
1402 )
1403 IS
1404 BEGIN
1405
1406 -- Initialize message list if p_init_msg_list is set to TRUE.
1407 IF FND_API.to_Boolean( p_init_msg_list )
1408 THEN
1409 FND_MSG_PUB.initialize;
1410 END IF;
1411
1412
1413 -- Initialize API return status to SUCCESS
1414 x_return_status := FND_API.G_RET_STS_SUCCESS;
1415
1416 -- validate NOT NULL column
1417 IF(p_REPAIR_LINE_ID is NULL)
1418 THEN
1419 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(REPAIR_LINE_ID)');
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421 END IF;
1422
1423 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1424 THEN
1425 -- Hint: Validate data
1426 -- IF p_REPAIR_LINE_ID is not NULL and p_REPAIR_LINE_ID <> G_MISS_CHAR
1427 -- verify if data is valid
1428 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1429 NULL;
1430 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1431 THEN
1432 -- Hint: Validate data
1433 -- IF p_REPAIR_LINE_ID <> G_MISS_CHAR
1434 -- verify if data is valid
1435 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1436 NULL;
1437 END IF;
1438
1439 -- Standard call to get message count and if count is 1, get message info.
1440 FND_MSG_PUB.Count_And_Get
1441 ( p_count => x_msg_count,
1442 p_data => x_msg_data
1443 );
1444
1445 END Validate_REPAIR_LINE_ID;
1446
1447
1448 PROCEDURE Validate_WIP_ENTITY_ID (
1449 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1450 P_Validation_mode IN VARCHAR2,
1451 P_WIP_ENTITY_ID IN NUMBER,
1452 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1453 X_Return_Status OUT NOCOPY VARCHAR2,
1454 X_Msg_Count OUT NOCOPY NUMBER,
1455 X_Msg_Data OUT NOCOPY VARCHAR2
1456 )
1457 IS
1458 BEGIN
1459
1460 -- Initialize message list if p_init_msg_list is set to TRUE.
1461 IF FND_API.to_Boolean( p_init_msg_list )
1462 THEN
1463 FND_MSG_PUB.initialize;
1464 END IF;
1465
1466
1467 -- Initialize API return status to SUCCESS
1468 x_return_status := FND_API.G_RET_STS_SUCCESS;
1469
1470 -- validate NOT NULL column
1471 IF(p_WIP_ENTITY_ID is NULL)
1472 THEN
1473 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(WIP_ENTITY_ID)');
1474 x_return_status := FND_API.G_RET_STS_ERROR;
1475 END IF;
1476
1477 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1478 THEN
1479 -- Hint: Validate data
1480 -- IF p_WIP_ENTITY_ID is not NULL and p_WIP_ENTITY_ID <> G_MISS_CHAR
1481 -- verify if data is valid
1482 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1483 NULL;
1484 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1485 THEN
1486 -- Hint: Validate data
1487 -- IF p_WIP_ENTITY_ID <> G_MISS_CHAR
1488 -- verify if data is valid
1489 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1490 NULL;
1491 END IF;
1492
1493 -- Standard call to get message count and if count is 1, get message info.
1494 FND_MSG_PUB.Count_And_Get
1495 ( p_count => x_msg_count,
1496 p_data => x_msg_data
1497 );
1498
1499 END Validate_WIP_ENTITY_ID;
1500
1501
1502 PROCEDURE Validate_GROUP_ID (
1503 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1504 P_Validation_mode IN VARCHAR2,
1505 P_GROUP_ID IN NUMBER,
1506 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1507 X_Return_Status OUT NOCOPY VARCHAR2,
1508 X_Msg_Count OUT NOCOPY NUMBER,
1509 X_Msg_Data OUT NOCOPY VARCHAR2
1510 )
1511 IS
1512 BEGIN
1513
1514 -- Initialize message list if p_init_msg_list is set to TRUE.
1515 IF FND_API.to_Boolean( p_init_msg_list )
1516 THEN
1517 FND_MSG_PUB.initialize;
1518 END IF;
1519
1520
1521 -- Initialize API return status to SUCCESS
1522 x_return_status := FND_API.G_RET_STS_SUCCESS;
1523
1524 -- validate NOT NULL column
1525 IF(p_GROUP_ID is NULL)
1526 THEN
1527 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(GROUP_ID)');
1528 x_return_status := FND_API.G_RET_STS_ERROR;
1529 END IF;
1530
1531 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1532 THEN
1533 -- Hint: Validate data
1534 -- IF p_GROUP_ID is not NULL and p_GROUP_ID <> G_MISS_CHAR
1535 -- verify if data is valid
1536 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1537 NULL;
1538 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1539 THEN
1540 -- Hint: Validate data
1541 -- IF p_GROUP_ID <> G_MISS_CHAR
1542 -- verify if data is valid
1543 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1544 NULL;
1545 END IF;
1546
1547 -- Standard call to get message count and if count is 1, get message info.
1548 FND_MSG_PUB.Count_And_Get
1549 ( p_count => x_msg_count,
1550 p_data => x_msg_data
1551 );
1552
1553 END Validate_GROUP_ID;
1554
1555
1556 PROCEDURE Validate_ORGANIZATION_ID (
1557 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1558 P_Validation_mode IN VARCHAR2,
1559 P_ORGANIZATION_ID IN NUMBER,
1560 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1561 X_Return_Status OUT NOCOPY VARCHAR2,
1562 X_Msg_Count OUT NOCOPY NUMBER,
1563 X_Msg_Data OUT NOCOPY VARCHAR2
1564 )
1565 IS
1566 BEGIN
1567
1568 -- Initialize message list if p_init_msg_list is set to TRUE.
1569 IF FND_API.to_Boolean( p_init_msg_list )
1570 THEN
1571 FND_MSG_PUB.initialize;
1572 END IF;
1573
1574
1575 -- Initialize API return status to SUCCESS
1576 x_return_status := FND_API.G_RET_STS_SUCCESS;
1577
1578 -- validate NOT NULL column
1579 IF(p_ORGANIZATION_ID is NULL)
1580 THEN
1581 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(ORGANIZATION_ID)');
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583 END IF;
1584
1585 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1586 THEN
1587 -- Hint: Validate data
1588 -- IF p_ORGANIZATION_ID is not NULL and p_ORGANIZATION_ID <> G_MISS_CHAR
1589 -- verify if data is valid
1590 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1591 NULL;
1592 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1593 THEN
1594 -- Hint: Validate data
1595 -- IF p_ORGANIZATION_ID <> G_MISS_CHAR
1596 -- verify if data is valid
1597 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1598 NULL;
1599 END IF;
1600
1601 -- Standard call to get message count and if count is 1, get message info.
1602 FND_MSG_PUB.Count_And_Get
1603 ( p_count => x_msg_count,
1604 p_data => x_msg_data
1605 );
1606
1607 END Validate_ORGANIZATION_ID;
1608
1609
1610 PROCEDURE Validate_QUANTITY (
1611 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1612 P_Validation_mode IN VARCHAR2,
1613 P_QUANTITY IN NUMBER,
1614 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1615 X_Return_Status OUT NOCOPY VARCHAR2,
1616 X_Msg_Count OUT NOCOPY NUMBER,
1617 X_Msg_Data OUT NOCOPY VARCHAR2
1618 )
1619 IS
1620 BEGIN
1621
1622 -- Initialize message list if p_init_msg_list is set to TRUE.
1623 IF FND_API.to_Boolean( p_init_msg_list )
1624 THEN
1625 FND_MSG_PUB.initialize;
1626 END IF;
1627
1628
1629 -- Initialize API return status to SUCCESS
1630 x_return_status := FND_API.G_RET_STS_SUCCESS;
1631
1632 -- validate NOT NULL column
1633 IF(p_QUANTITY is NULL)
1634 THEN
1635 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSD', 'Private repjobxref API: -Violate NOT NULL constraint(QUANTITY)');
1636 x_return_status := FND_API.G_RET_STS_ERROR;
1637 END IF;
1638
1639 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1640 THEN
1641 -- Hint: Validate data
1642 -- IF p_QUANTITY is not NULL and p_QUANTITY <> G_MISS_CHAR
1643 -- verify if data is valid
1644 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1645 NULL;
1646 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1647 THEN
1648 -- Hint: Validate data
1649 -- IF p_QUANTITY <> G_MISS_CHAR
1650 -- verify if data is valid
1651 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1652 NULL;
1653 END IF;
1654
1655 -- Standard call to get message count and if count is 1, get message info.
1656 FND_MSG_PUB.Count_And_Get
1657 ( p_count => x_msg_count,
1658 p_data => x_msg_data
1659 );
1660
1661 END Validate_QUANTITY;
1662
1663
1664 PROCEDURE Validate_OBJECT_VERSION_NUMBER (
1665 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1666 P_Validation_mode IN VARCHAR2,
1667 P_OBJECT_VERSION_NUMBER IN NUMBER,
1668 P_REPAIR_JOB_XREF_ID IN NUMBER,
1669 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1670 X_Return_Status OUT NOCOPY VARCHAR2,
1671 X_Msg_Count OUT NOCOPY NUMBER,
1672 X_Msg_Data OUT NOCOPY VARCHAR2
1673 )
1674 IS
1675
1676 l_OBJECT_VERSION_NUMBER NUMBER;
1677
1678 BEGIN
1679
1680 IF (g_debug > 0 ) THEN
1681 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER in procedure');
1682 END IF;
1683
1684
1685 -- Initialize message list if p_init_msg_list is set to TRUE.
1686 IF FND_API.to_Boolean( p_init_msg_list )
1687 THEN
1688 FND_MSG_PUB.initialize;
1689 END IF;
1690
1691
1692 -- Initialize API return status to SUCCESS
1693 x_return_status := FND_API.G_RET_STS_SUCCESS;
1694
1695 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1696 THEN
1697
1698 IF (g_debug > 0 ) THEN
1699 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER in create');
1700 END IF;
1701
1702 IF (g_debug > 0 ) THEN
1703 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER ovn '||to_char(p_OBJECT_VERSION_NUMBER));
1704 END IF;
1705
1706
1707 -- verify if data is valid
1708 --IF(p_OBJECT_VERSION_NUMBER is NULL) THEN
1709 -- set object_version_number to 1 for create
1710 -- p_OBJECT_VERSION_NUMBER := 1;
1711 --END IF;
1712 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1713 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1714 THEN
1715
1716 IF (g_debug > 0 ) THEN
1717 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER in update');
1718 END IF;
1719
1720 IF (g_debug > 0 ) THEN
1721 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER ovn from form '||to_char(p_OBJECT_VERSION_NUMBER));
1722 END IF;
1723
1724
1725 -- verify if data is valid
1726 SELECT OBJECT_VERSION_NUMBER
1727 INTO l_OBJECT_VERSION_NUMBER
1728 FROM CSD_REPAIR_JOB_XREF
1729 WHERE REPAIR_JOB_XREF_ID = P_REPAIR_JOB_XREF_ID;
1730
1731 IF (g_debug > 0 ) THEN
1732 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER ovn from db '||to_char(l_OBJECT_VERSION_NUMBER));
1733 END IF;
1734
1735
1736 if (l_OBJECT_VERSION_NUMBER <> p_OBJECT_VERSION_NUMBER) then
1737 -- data is not valid
1738 x_return_status := FND_API.G_RET_STS_ERROR;
1739
1740 IF (g_debug > 0 ) THEN
1741 csd_gen_utility_pvt.add('CSD_REPAIR_JOB_XREF_PVT.Validate_OBJECT_VERSION_NUMBER ovn mismatch error');
1742 END IF;
1743
1744
1745 end if;
1746
1747 END IF;
1748
1749 -- Standard call to get message count and if count is 1, get message info.
1750 FND_MSG_PUB.Count_And_Get
1751 ( p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754
1755 END Validate_OBJECT_VERSION_NUMBER;
1756
1757 -- Hint: inter-field level validation can be added here.
1758 -- Hint: If p_validation_mode = JTF_PLSQL_API.G_VALIDATE_UPDATE, we should use cursor
1759 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1760 -- stored in database table.
1761 PROCEDURE Validate_REPJOBXREF_rec(
1762 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1763 P_Validation_mode IN VARCHAR2,
1764 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
1765 X_Return_Status OUT NOCOPY VARCHAR2,
1766 X_Msg_Count OUT NOCOPY NUMBER,
1767 X_Msg_Data OUT NOCOPY VARCHAR2
1768 )
1769 IS
1770 BEGIN
1771
1772 -- Initialize message list if p_init_msg_list is set to TRUE.
1773 IF FND_API.to_Boolean( p_init_msg_list )
1774 THEN
1775 FND_MSG_PUB.initialize;
1776 END IF;
1777
1778
1779 -- Initialize API return status to SUCCESS
1780 x_return_status := FND_API.G_RET_STS_SUCCESS;
1781
1782 -- Hint: Validate data
1783 -- If data not valid
1784 -- THEN
1785 -- x_return_status := FND_API.G_RET_STS_ERROR;
1786
1787 -- Debug Message
1788 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'API_INVALID_RECORD');
1789
1790 -- Standard call to get message count and if count is 1, get message info.
1791 FND_MSG_PUB.Count_And_Get
1792 ( p_count => x_msg_count,
1793 p_data => x_msg_data
1794 );
1795
1796 END Validate_REPJOBXREF_Rec;
1797
1798 PROCEDURE Validate_repjobxref(
1799 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1800 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1801 P_Validation_mode IN VARCHAR2,
1802 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
1803 X_Return_Status OUT NOCOPY VARCHAR2,
1804 X_Msg_Count OUT NOCOPY NUMBER,
1805 X_Msg_Data OUT NOCOPY VARCHAR2
1806 )
1807 IS
1808 l_api_name CONSTANT VARCHAR2(30) := 'Validate_repjobxref';
1809 BEGIN
1810
1811 -- Debug Message
1812 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'start');
1813
1814
1815 -- Initialize API return status to SUCCESS
1816 x_return_status := FND_API.G_RET_STS_SUCCESS;
1817
1818 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1819 -- Hint: We provide validation procedure for every column. Developer should delete
1820 -- unnecessary validation procedures.
1821 Validate_REPAIR_JOB_XREF_ID(
1822 p_init_msg_list => FND_API.G_FALSE,
1823 p_validation_mode => p_validation_mode,
1824 p_REPAIR_JOB_XREF_ID => P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID,
1825 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1826 x_return_status => x_return_status,
1827 x_msg_count => x_msg_count,
1828 x_msg_data => x_msg_data);
1829 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1830 raise FND_API.G_EXC_ERROR;
1831 END IF;
1832
1833 Validate_REPAIR_LINE_ID(
1834 p_init_msg_list => FND_API.G_FALSE,
1835 p_validation_mode => p_validation_mode,
1836 p_REPAIR_LINE_ID => P_REPJOBXREF_Rec.REPAIR_LINE_ID,
1837 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1838 x_return_status => x_return_status,
1839 x_msg_count => x_msg_count,
1840 x_msg_data => x_msg_data);
1841 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1842 raise FND_API.G_EXC_ERROR;
1843 END IF;
1844
1845 Validate_WIP_ENTITY_ID(
1846 p_init_msg_list => FND_API.G_FALSE,
1847 p_validation_mode => p_validation_mode,
1848 p_WIP_ENTITY_ID => P_REPJOBXREF_Rec.WIP_ENTITY_ID,
1849 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1850 x_return_status => x_return_status,
1851 x_msg_count => x_msg_count,
1852 x_msg_data => x_msg_data);
1853 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1854 raise FND_API.G_EXC_ERROR;
1855 END IF;
1856
1857 Validate_GROUP_ID(
1858 p_init_msg_list => FND_API.G_FALSE,
1859 p_validation_mode => p_validation_mode,
1860 p_GROUP_ID => P_REPJOBXREF_Rec.GROUP_ID,
1861 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1862 x_return_status => x_return_status,
1863 x_msg_count => x_msg_count,
1864 x_msg_data => x_msg_data);
1865 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1866 raise FND_API.G_EXC_ERROR;
1867 END IF;
1868
1869 Validate_ORGANIZATION_ID(
1870 p_init_msg_list => FND_API.G_FALSE,
1871 p_validation_mode => p_validation_mode,
1872 p_ORGANIZATION_ID => P_REPJOBXREF_Rec.ORGANIZATION_ID,
1873 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1874 x_return_status => x_return_status,
1875 x_msg_count => x_msg_count,
1876 x_msg_data => x_msg_data);
1877 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1878 raise FND_API.G_EXC_ERROR;
1879 END IF;
1880
1881 Validate_QUANTITY(
1882 p_init_msg_list => FND_API.G_FALSE,
1883 p_validation_mode => p_validation_mode,
1884 p_QUANTITY => P_REPJOBXREF_Rec.QUANTITY,
1885 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1886 x_return_status => x_return_status,
1887 x_msg_count => x_msg_count,
1888 x_msg_data => x_msg_data);
1889 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1890 raise FND_API.G_EXC_ERROR;
1891 END IF;
1892
1893 END IF;
1894
1895 -- travi OBJECT_VERSION_NUMBER validation
1896 Validate_OBJECT_VERSION_NUMBER(
1897 p_init_msg_list => FND_API.G_FALSE,
1898 p_validation_mode => p_validation_mode,
1899 p_OBJECT_VERSION_NUMBER => P_REPJOBXREF_Rec.OBJECT_VERSION_NUMBER,
1900 p_REPAIR_JOB_XREF_ID => P_REPJOBXREF_Rec.REPAIR_JOB_XREF_ID,
1901 -- Hint: You may add x_item_property_rec as one of your out parameter if you'd like to pass back item property.
1902 x_return_status => x_return_status,
1903 x_msg_count => x_msg_count,
1904 x_msg_data => x_msg_data);
1905 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1906 raise FND_API.G_EXC_ERROR;
1907 END IF;
1908
1909 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1910 -- Hint: Inter-field level validation can be added here
1911 -- invoke record level validation procedures
1912 Validate_REPJOBXREF_Rec(
1913 p_init_msg_list => FND_API.G_FALSE,
1914 p_validation_mode => p_validation_mode,
1915 P_REPJOBXREF_Rec => P_REPJOBXREF_Rec,
1916 x_return_status => x_return_status,
1917 x_msg_count => x_msg_count,
1918 x_msg_data => x_msg_data);
1919
1920 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1921 raise FND_API.G_EXC_ERROR;
1922 END IF;
1923 END IF;
1924
1925 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1926 -- invoke inter-record level validation procedures
1927 NULL;
1928 END IF;
1929
1930 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1931 -- invoke inter-entity level validation procedures
1932 NULL;
1933 END IF;
1934
1935
1936 -- Debug Message
1937 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSD', 'Private API: ' || l_api_name || 'end');
1938
1939 END Validate_repjobxref;
1940
1941 End CSD_REPair_JOB_XREF_PVT;