[Home] [Help]
PACKAGE BODY: APPS.CSP_RS_CUST_RELATION_PVT
Source
1 PACKAGE BODY CSP_RS_CUST_RELATION_PVT as
2 /* $Header: cspvrcrb.pls 115.6 2003/05/02 00:26:48 phegde noship $ */
3 -- Start of Comments
4 -- Package name : CSP_RS_CUST_RELATION_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_RS_CUST_RELATION_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvrcrb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 PROCEDURE Create_rs_cust_relation(
17 P_Api_Version_Number IN NUMBER,
18 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
19 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
20 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
21 P_RCR_Rec IN RCR_Rec_Type := G_MISS_RCR_REC,
22 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
23 X_RS_CUST_RELATION_ID OUT NOCOPY NUMBER,
24 X_Return_Status OUT NOCOPY VARCHAR2,
25 X_Msg_Count OUT NOCOPY NUMBER,
26 X_Msg_Data OUT NOCOPY VARCHAR2
27 )
28
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'Create_rs_cust_relation';
31 l_api_version_number CONSTANT NUMBER := 2.0;
32 l_return_status_full VARCHAR2(1);
33 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
34 l_access_flag VARCHAR2(1);
35 BEGIN
36 -- Standard Start of API savepoint
37 SAVEPOINT CREATE_RS_CUST_RELATION_PVT;
38
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
41 p_api_version_number,
42 l_api_name,
43 G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48
49 -- Initialize message list if p_init_msg_list is set to TRUE.
50 IF FND_API.to_Boolean( p_init_msg_list )
51 THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55
56 -- Debug Message
57 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
58
59
60 -- Initialize API return status to SUCCESS
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 --
64 -- API body
65 --
66 -- ******************************************************************
67 -- Validate Environment
68 -- ******************************************************************
69 IF FND_GLOBAL.User_Id IS NULL
70 THEN
71 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
72 THEN
73 FND_MESSAGE.Set_Name('CSP', 'UT_CANNOT_GET_PROFILE_VALUE');
74 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
75 FND_MSG_PUB.ADD;
76 END IF;
77 RAISE FND_API.G_EXC_ERROR;
78 END IF;
79
80
81 /* IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
82 THEN
83 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
84 p_api_version_number => 2.0
85 ,p_init_msg_list => p_init_msg_list
86 ,p_salesforce_id => NULL
87 ,p_admin_group_id => p_admin_group_id
88 ,x_return_status => x_return_status
89 ,x_msg_count => x_msg_count
90 ,x_msg_data => x_msg_data
91 ,x_sales_member_rec => l_identity_sales_member_rec);
92
93
94 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 END IF;
99 */
100 -- Debug message
101 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_rs_cust_relation');
102
103 -- Invoke validation procedures
104 Validate_rs_cust_relation(
105 p_init_msg_list => FND_API.G_FALSE,
106 p_validation_level => p_validation_level,
107 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
108 P_RCR_Rec => P_RCR_Rec,
109 x_return_status => x_return_status,
110 x_msg_count => x_msg_count,
111 x_msg_data => x_msg_data);
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 -- Hint: Add corresponding Master-Detail business logic here if necessary.
118
119 -- Debug Message
120 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
121
122 -- Invoke table handler(CSP_RS_CUST_RELATIONS_PKG.Insert_Row)
123 CSP_RS_CUST_RELATIONS_PKG.Insert_Row(
124 px_RS_CUST_RELATION_ID => x_RS_CUST_RELATION_ID,
125 p_RESOURCE_TYPE => p_RCR_rec.RESOURCE_TYPE,
126 p_RESOURCE_ID => p_RCR_rec.RESOURCE_ID,
127 p_CUSTOMER_ID => p_RCR_rec.CUSTOMER_ID,
128 p_CREATED_BY => FND_GLOBAL.USER_ID,
129 p_CREATION_DATE => SYSDATE,
130 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
131 p_LAST_UPDATE_DATE => SYSDATE,
132 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
133 p_ATTRIBUTE_CATEGORY => p_RCR_rec.ATTRIBUTE_CATEGORY,
134 p_ATTRIBUTE1 => p_RCR_rec.ATTRIBUTE1,
135 p_ATTRIBUTE2 => p_RCR_rec.ATTRIBUTE2,
136 p_ATTRIBUTE3 => p_RCR_rec.ATTRIBUTE3,
137 p_ATTRIBUTE4 => p_RCR_rec.ATTRIBUTE4,
138 p_ATTRIBUTE5 => p_RCR_rec.ATTRIBUTE5,
139 p_ATTRIBUTE6 => p_RCR_rec.ATTRIBUTE6,
140 p_ATTRIBUTE7 => p_RCR_rec.ATTRIBUTE7,
141 p_ATTRIBUTE8 => p_RCR_rec.ATTRIBUTE8,
142 p_ATTRIBUTE9 => p_RCR_rec.ATTRIBUTE9,
143 p_ATTRIBUTE10 => p_RCR_rec.ATTRIBUTE10,
144 p_ATTRIBUTE11 => p_RCR_rec.ATTRIBUTE11,
145 p_ATTRIBUTE12 => p_RCR_rec.ATTRIBUTE12,
146 p_ATTRIBUTE13 => p_RCR_rec.ATTRIBUTE13,
147 p_ATTRIBUTE14 => p_RCR_rec.ATTRIBUTE14,
148 p_ATTRIBUTE15 => p_RCR_rec.ATTRIBUTE15);
149 -- Hint: Primary key should be returned.
150 -- x_RS_CUST_RELATION_ID := px_RS_CUST_RELATION_ID;
151
152 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
153 RAISE FND_API.G_EXC_ERROR;
154 END IF;
155
156 --
157 -- End of API body
158 --
159
160 -- Standard check for p_commit
161 IF FND_API.to_Boolean( p_commit )
162 THEN
163 COMMIT WORK;
164 END IF;
165
166
167 -- Debug Message
168 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
169
170
171 -- Standard call to get message count and if count is 1, get message info.
172 FND_MSG_PUB.Count_And_Get
173 ( p_count => x_msg_count,
174 p_data => x_msg_data
175 );
176
177 EXCEPTION
178 WHEN FND_API.G_EXC_ERROR THEN
179 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
180 P_API_NAME => L_API_NAME
181 ,P_PKG_NAME => G_PKG_NAME
182 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
183 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
184 ,X_MSG_COUNT => X_MSG_COUNT
185 ,X_MSG_DATA => X_MSG_DATA
186 ,X_RETURN_STATUS => X_RETURN_STATUS);
187
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
190 P_API_NAME => L_API_NAME
191 ,P_PKG_NAME => G_PKG_NAME
192 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
193 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
194 ,X_MSG_COUNT => X_MSG_COUNT
195 ,X_MSG_DATA => X_MSG_DATA
196 ,X_RETURN_STATUS => X_RETURN_STATUS);
197
198 WHEN OTHERS THEN
199 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
200 P_API_NAME => L_API_NAME
201 ,P_PKG_NAME => G_PKG_NAME
202 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
203 ,P_SQLCODE => SQLCODE
204 ,P_SQLERRM => SQLERRM
205 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
206 ,X_MSG_COUNT => X_MSG_COUNT
207 ,X_MSG_DATA => X_MSG_DATA
208 ,X_RETURN_STATUS => X_RETURN_STATUS);
209 End Create_rs_cust_relation;
210
211
212 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
213 PROCEDURE Update_rs_cust_relation(
214 P_Api_Version_Number IN NUMBER,
215 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
216 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
218 P_Identity_Salesforce_Id IN NUMBER := NULL,
219 P_RCR_Rec IN RCR_Rec_Type,
220 X_Return_Status OUT NOCOPY VARCHAR2,
221 X_Msg_Count OUT NOCOPY NUMBER,
222 X_Msg_Data OUT NOCOPY VARCHAR2
223 )
224
225 IS
226 /*
227 Cursor C_Get_rs_cust_relation(RS_CUST_RELATION_ID Number) IS
228 Select rowid,
229 RS_CUST_RELATION_ID,
230 RESOURCE_TYPE,
231 RESOURCE_ID,
232 CUSTOMER_ID,
233 CREATED_BY,
234 CREATION_DATE,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_DATE,
237 LAST_UPDATE_LOGIN,
238 ATTRIBUTE_CATEGORY,
239 ATTRIBUTE1,
240 ATTRIBUTE2,
241 ATTRIBUTE3,
242 ATTRIBUTE4,
243 ATTRIBUTE5,
244 ATTRIBUTE6,
245 ATTRIBUTE7,
246 ATTRIBUTE8,
247 ATTRIBUTE9,
248 ATTRIBUTE10,
249 ATTRIBUTE11,
250 ATTRIBUTE12,
251 ATTRIBUTE13,
252 ATTRIBUTE14,
253 ATTRIBUTE15
254 From CSP_RS_CUST_RELATIONS
255 -- Hint: Developer need to provide Where clause
256 For Update NOWAIT;
257 */
258 l_api_name CONSTANT VARCHAR2(30) := 'Update_rs_cust_relation';
259 l_api_version_number CONSTANT NUMBER := 2.0;
260 -- Local Variables
261 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
262 l_ref_RCR_rec CSP_rs_cust_relation_PVT.RCR_Rec_Type;
263 l_tar_RCR_rec CSP_rs_cust_relation_PVT.RCR_Rec_Type := P_RCR_Rec;
264 l_rowid ROWID;
265 BEGIN
266 -- Standard Start of API savepoint
267 SAVEPOINT UPDATE_RS_CUST_RELATION_PVT;
268
269 -- Standard call to check for call compatibility.
270 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
271 p_api_version_number,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278
279 -- Initialize message list if p_init_msg_list is set to TRUE.
280 IF FND_API.to_Boolean( p_init_msg_list )
281 THEN
282 FND_MSG_PUB.initialize;
283 END IF;
284
285
286 -- Debug Message
287 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
288
289
290 -- Initialize API return status to SUCCESS
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293 --
294 -- Api body
295 --
296
297 /* IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
298 THEN
299 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
300 p_api_version_number => 2.0
301 ,p_init_msg_list => p_init_msg_list
302 ,p_salesforce_id => p_identity_salesforce_id
303 ,p_admin_group_id => p_admin_group_id
304 ,x_return_status => x_return_status
305 ,x_msg_count => x_msg_count
306 ,x_msg_data => x_msg_data
307 ,x_sales_member_rec => l_identity_sales_member_rec);
308
309
310 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 END IF;
315 */
316 -- Debug Message
317 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
318
319 /*
320 Open C_Get_rs_cust_relation( l_tar_RCR_rec.RS_CUST_RELATION_ID);
321
322 Fetch C_Get_rs_cust_relation into
323 l_rowid,
324 l_ref_RCR_rec.RS_CUST_RELATION_ID,
325 l_ref_RCR_rec.RESOURCE_TYPE,
326 l_ref_RCR_rec.RESOURCE_ID,
327 l_ref_RCR_rec.CUSTOMER_ID,
328 l_ref_RCR_rec.CREATED_BY,
329 l_ref_RCR_rec.CREATION_DATE,
330 l_ref_RCR_rec.LAST_UPDATED_BY,
331 l_ref_RCR_rec.LAST_UPDATE_DATE,
332 l_ref_RCR_rec.LAST_UPDATE_LOGIN,
333 l_ref_RCR_rec.ATTRIBUTE_CATEGORY,
334 l_ref_RCR_rec.ATTRIBUTE1,
335 l_ref_RCR_rec.ATTRIBUTE2,
336 l_ref_RCR_rec.ATTRIBUTE3,
337 l_ref_RCR_rec.ATTRIBUTE4,
338 l_ref_RCR_rec.ATTRIBUTE5,
339 l_ref_RCR_rec.ATTRIBUTE6,
340 l_ref_RCR_rec.ATTRIBUTE7,
341 l_ref_RCR_rec.ATTRIBUTE8,
342 l_ref_RCR_rec.ATTRIBUTE9,
343 l_ref_RCR_rec.ATTRIBUTE10,
344 l_ref_RCR_rec.ATTRIBUTE11,
345 l_ref_RCR_rec.ATTRIBUTE12,
346 l_ref_RCR_rec.ATTRIBUTE13,
347 l_ref_RCR_rec.ATTRIBUTE14,
348 l_ref_RCR_rec.ATTRIBUTE15;
349
350 If ( C_Get_rs_cust_relation%NOTFOUND) Then
351 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
352 THEN
353 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_UPDATE_TARGET');
354 FND_MESSAGE.Set_Token ('INFO', 'rs_cust_relation', FALSE);
355 FND_MSG_PUB.Add;
356 END IF;
357 Close C_Get_rs_cust_relation;
358 raise FND_API.G_EXC_ERROR;
359 END IF;
360 -- Debug Message
361 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
362 Close C_Get_rs_cust_relation;
363 */
364
365
366 If (l_tar_RCR_rec.last_update_date is NULL or
367 l_tar_RCR_rec.last_update_date = FND_API.G_MISS_Date ) Then
368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
369 THEN
370 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_ID');
371 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
372 FND_MSG_PUB.ADD;
373 END IF;
374 raise FND_API.G_EXC_ERROR;
375 End if;
376 -- Check Whether record has been changed by someone else
377 If (l_tar_RCR_rec.last_update_date <> l_ref_RCR_rec.last_update_date) Then
378 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
379 THEN
380 FND_MESSAGE.Set_Name('CSP', 'API_RECORD_CHANGED');
381 FND_MESSAGE.Set_Token('INFO', 'rs_cust_relation', FALSE);
382 FND_MSG_PUB.ADD;
383 END IF;
384 raise FND_API.G_EXC_ERROR;
385 End if;
386
387 -- Debug message
388 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_rs_cust_relation');
389
390 -- Invoke validation procedures
391 Validate_rs_cust_relation(
392 p_init_msg_list => FND_API.G_FALSE,
393 p_validation_level => p_validation_level,
394 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
395 P_RCR_Rec => P_RCR_Rec,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data);
399
400 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
401 RAISE FND_API.G_EXC_ERROR;
402 END IF;
403
404 -- Hint: Add corresponding Master-Detail business logic here if necessary.
405
406 -- Debug Message
407 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
408
409 -- Invoke table handler(CSP_RS_CUST_RELATIONS_PKG.Update_Row)
410 CSP_RS_CUST_RELATIONS_PKG.Update_Row(
411 p_RS_CUST_RELATION_ID => p_RCR_rec.RS_CUST_RELATION_ID,
412 p_RESOURCE_TYPE => p_RCR_rec.RESOURCE_TYPE,
413 p_RESOURCE_ID => p_RCR_rec.RESOURCE_ID,
414 p_CUSTOMER_ID => p_RCR_rec.CUSTOMER_ID,
415 p_CREATED_BY => FND_API.G_MISS_NUM,
416 p_CREATION_DATE => FND_API.G_MISS_DATE,
417 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
418 p_LAST_UPDATE_DATE => SYSDATE,
419 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
420 p_ATTRIBUTE_CATEGORY => p_RCR_rec.ATTRIBUTE_CATEGORY,
421 p_ATTRIBUTE1 => p_RCR_rec.ATTRIBUTE1,
422 p_ATTRIBUTE2 => p_RCR_rec.ATTRIBUTE2,
423 p_ATTRIBUTE3 => p_RCR_rec.ATTRIBUTE3,
424 p_ATTRIBUTE4 => p_RCR_rec.ATTRIBUTE4,
425 p_ATTRIBUTE5 => p_RCR_rec.ATTRIBUTE5,
426 p_ATTRIBUTE6 => p_RCR_rec.ATTRIBUTE6,
427 p_ATTRIBUTE7 => p_RCR_rec.ATTRIBUTE7,
428 p_ATTRIBUTE8 => p_RCR_rec.ATTRIBUTE8,
429 p_ATTRIBUTE9 => p_RCR_rec.ATTRIBUTE9,
430 p_ATTRIBUTE10 => p_RCR_rec.ATTRIBUTE10,
431 p_ATTRIBUTE11 => p_RCR_rec.ATTRIBUTE11,
432 p_ATTRIBUTE12 => p_RCR_rec.ATTRIBUTE12,
433 p_ATTRIBUTE13 => p_RCR_rec.ATTRIBUTE13,
434 p_ATTRIBUTE14 => p_RCR_rec.ATTRIBUTE14,
435 p_ATTRIBUTE15 => p_RCR_rec.ATTRIBUTE15);
436 --
437 -- End of API body.
438 --
439
440 -- Standard check for p_commit
441 IF FND_API.to_Boolean( p_commit )
442 THEN
443 COMMIT WORK;
444 END IF;
445
446
447 -- Debug Message
448 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
449
450
451 -- Standard call to get message count and if count is 1, get message info.
452 FND_MSG_PUB.Count_And_Get
453 ( p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456
457 EXCEPTION
458 WHEN FND_API.G_EXC_ERROR THEN
459 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
460 P_API_NAME => L_API_NAME
461 ,P_PKG_NAME => G_PKG_NAME
462 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
463 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
464 ,X_MSG_COUNT => X_MSG_COUNT
465 ,X_MSG_DATA => X_MSG_DATA
466 ,X_RETURN_STATUS => X_RETURN_STATUS);
467
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
470 P_API_NAME => L_API_NAME
471 ,P_PKG_NAME => G_PKG_NAME
472 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
473 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
474 ,X_MSG_COUNT => X_MSG_COUNT
475 ,X_MSG_DATA => X_MSG_DATA
476 ,X_RETURN_STATUS => X_RETURN_STATUS);
477
478 WHEN OTHERS THEN
479 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
480 P_API_NAME => L_API_NAME
481 ,P_PKG_NAME => G_PKG_NAME
482 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
483 ,P_SQLCODE => SQLCODE
484 ,P_SQLERRM => SQLERRM
485 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
486 ,X_MSG_COUNT => X_MSG_COUNT
487 ,X_MSG_DATA => X_MSG_DATA
488 ,X_RETURN_STATUS => X_RETURN_STATUS);
489 End Update_rs_cust_relation;
490
491
492 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
493 -- The Master delete procedure may not be needed depends on different business requirements.
494 PROCEDURE Delete_rs_cust_relation(
495 P_Api_Version_Number IN NUMBER,
496 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
497 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
498 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
499 P_Identity_Salesforce_Id IN NUMBER := NULL,
500 P_RCR_Rec IN RCR_Rec_Type,
501 X_Return_Status OUT NOCOPY VARCHAR2,
502 X_Msg_Count OUT NOCOPY NUMBER,
503 X_Msg_Data OUT NOCOPY VARCHAR2
504 )
505
506 IS
507 l_api_name CONSTANT VARCHAR2(30) := 'Delete_rs_cust_relation';
508 l_api_version_number CONSTANT NUMBER := 2.0;
509 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
510 BEGIN
511 -- Standard Start of API savepoint
512 SAVEPOINT DELETE_RS_CUST_RELATION_PVT;
513
514 -- Standard call to check for call compatibility.
515 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
516 p_api_version_number,
517 l_api_name,
518 G_PKG_NAME)
519 THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522
523
524 -- Initialize message list if p_init_msg_list is set to TRUE.
525 IF FND_API.to_Boolean( p_init_msg_list )
526 THEN
527 FND_MSG_PUB.initialize;
528 END IF;
529
530
531 -- Debug Message
532 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
533
534
535 -- Initialize API return status to SUCCESS
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 --
539 -- Api body
540 --
541
542 /* IF p_validation_level = FND_API.G_VALID_LEVEL_FULL
543 THEN
544 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
545 p_api_version_number => 2.0
546 ,p_init_msg_list => p_init_msg_list
547 ,p_salesforce_id => p_identity_salesforce_id
548 ,p_admin_group_id => p_admin_group_id
549 ,x_return_status => x_return_status
550 ,x_msg_count => x_msg_count
551 ,x_msg_data => x_msg_data
552 ,x_sales_member_rec => l_identity_sales_member_rec);
553
554
555 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558
559 END IF;
560 */
561
562 -- Debug Message
563 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
564
565 -- Invoke table handler(CSP_RS_CUST_RELATIONS_PKG.Delete_Row)
566 CSP_RS_CUST_RELATIONS_PKG.Delete_Row(
567 p_RS_CUST_RELATION_ID => p_RCR_rec.RS_CUST_RELATION_ID);
568 --
569 -- End of API body
570 --
571
572 -- Standard check for p_commit
573 IF FND_API.to_Boolean( p_commit )
574 THEN
575 COMMIT WORK;
576 END IF;
577
578
579 -- Debug Message
580 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
581
582
583 -- Standard call to get message count and if count is 1, get message info.
584 FND_MSG_PUB.Count_And_Get
585 ( p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588
589 EXCEPTION
590 WHEN FND_API.G_EXC_ERROR THEN
591 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
592 P_API_NAME => L_API_NAME
593 ,P_PKG_NAME => G_PKG_NAME
594 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
595 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
596 ,X_MSG_COUNT => X_MSG_COUNT
597 ,X_MSG_DATA => X_MSG_DATA
598 ,X_RETURN_STATUS => X_RETURN_STATUS);
599
600 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
601 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
602 P_API_NAME => L_API_NAME
603 ,P_PKG_NAME => G_PKG_NAME
604 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
605 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
606 ,X_MSG_COUNT => X_MSG_COUNT
607 ,X_MSG_DATA => X_MSG_DATA
608 ,X_RETURN_STATUS => X_RETURN_STATUS);
609
610 WHEN OTHERS THEN
611 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
612 P_API_NAME => L_API_NAME
613 ,P_PKG_NAME => G_PKG_NAME
614 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
615 ,P_SQLCODE => SQLCODE
616 ,P_SQLERRM => SQLERRM
617 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
618 ,X_MSG_COUNT => X_MSG_COUNT
619 ,X_MSG_DATA => X_MSG_DATA
620 ,X_RETURN_STATUS => X_RETURN_STATUS);
621 End Delete_rs_cust_relation;
622
623
624 -- This procudure defines the columns for the Dynamic SQL.
625 PROCEDURE Define_Columns(
626 P_RCR_Rec IN CSP_RS_CUST_RELATION_PUB.RCR_Rec_Type,
627 p_cur_get_RCR IN NUMBER
628 )
629 IS
630 BEGIN
631 -- Debug Message
632 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Begins');
633
634 -- define all columns for CSP_RS_CUST_RELATIONS view
635 dbms_sql.define_column(p_cur_get_RCR, 1, P_RCR_Rec.RS_CUST_RELATION_ID);
636 dbms_sql.define_column(p_cur_get_RCR, 2, P_RCR_Rec.RESOURCE_TYPE, 30);
637 dbms_sql.define_column(p_cur_get_RCR, 3, P_RCR_Rec.RESOURCE_ID);
638 dbms_sql.define_column(p_cur_get_RCR, 5, P_RCR_Rec.CUSTOMER_ID);
639 dbms_sql.define_column(p_cur_get_RCR, 6, P_RCR_Rec.ATTRIBUTE_CATEGORY, 30);
640 dbms_sql.define_column(p_cur_get_RCR, 7, P_RCR_Rec.ATTRIBUTE1, 150);
641 dbms_sql.define_column(p_cur_get_RCR, 8, P_RCR_Rec.ATTRIBUTE2, 150);
642 dbms_sql.define_column(p_cur_get_RCR, 9, P_RCR_Rec.ATTRIBUTE3, 150);
643 dbms_sql.define_column(p_cur_get_RCR, 10, P_RCR_Rec.ATTRIBUTE4, 150);
644 dbms_sql.define_column(p_cur_get_RCR, 11, P_RCR_Rec.ATTRIBUTE5, 150);
645 dbms_sql.define_column(p_cur_get_RCR, 12, P_RCR_Rec.ATTRIBUTE6, 150);
646 dbms_sql.define_column(p_cur_get_RCR, 13, P_RCR_Rec.ATTRIBUTE7, 150);
647 dbms_sql.define_column(p_cur_get_RCR, 14, P_RCR_Rec.ATTRIBUTE8, 150);
648 dbms_sql.define_column(p_cur_get_RCR, 15, P_RCR_Rec.ATTRIBUTE9, 150);
649 dbms_sql.define_column(p_cur_get_RCR, 16, P_RCR_Rec.ATTRIBUTE10, 150);
650 dbms_sql.define_column(p_cur_get_RCR, 17, P_RCR_Rec.ATTRIBUTE11, 150);
651 dbms_sql.define_column(p_cur_get_RCR, 18, P_RCR_Rec.ATTRIBUTE12, 150);
652 dbms_sql.define_column(p_cur_get_RCR, 19, P_RCR_Rec.ATTRIBUTE13, 150);
653 dbms_sql.define_column(p_cur_get_RCR, 20, P_RCR_Rec.ATTRIBUTE14, 150);
654 dbms_sql.define_column(p_cur_get_RCR, 21, P_RCR_Rec.ATTRIBUTE15, 150);
655
656 -- Debug Message
657 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Ends');
658 END Define_Columns;
659
660 -- This procudure gets column values by the Dynamic SQL.
661 PROCEDURE Get_Column_Values(
662 p_cur_get_RCR IN NUMBER,
663 X_RCR_Rec OUT NOCOPY CSP_RS_CUST_RELATION_PUB.RCR_Rec_Type
664 )
665 IS
666 BEGIN
667 -- Debug Message
668 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Get Column Values Begins');
669
670 -- get all column values for CSP_RS_CUST_RELATIONS table
671 dbms_sql.column_value(p_cur_get_RCR, 1, X_RCR_Rec.RS_CUST_RELATION_ID);
672 dbms_sql.column_value(p_cur_get_RCR, 2, X_RCR_Rec.RESOURCE_TYPE);
673 dbms_sql.column_value(p_cur_get_RCR, 3, X_RCR_Rec.RESOURCE_ID);
674 dbms_sql.column_value(p_cur_get_RCR, 5, X_RCR_Rec.CUSTOMER_ID);
675 dbms_sql.column_value(p_cur_get_RCR, 6, X_RCR_Rec.ATTRIBUTE_CATEGORY);
676 dbms_sql.column_value(p_cur_get_RCR, 7, X_RCR_Rec.ATTRIBUTE1);
677 dbms_sql.column_value(p_cur_get_RCR, 8, X_RCR_Rec.ATTRIBUTE2);
678 dbms_sql.column_value(p_cur_get_RCR, 9, X_RCR_Rec.ATTRIBUTE3);
679 dbms_sql.column_value(p_cur_get_RCR, 10, X_RCR_Rec.ATTRIBUTE4);
680 dbms_sql.column_value(p_cur_get_RCR, 11, X_RCR_Rec.ATTRIBUTE5);
681 dbms_sql.column_value(p_cur_get_RCR, 12, X_RCR_Rec.ATTRIBUTE6);
682 dbms_sql.column_value(p_cur_get_RCR, 13, X_RCR_Rec.ATTRIBUTE7);
683 dbms_sql.column_value(p_cur_get_RCR, 14, X_RCR_Rec.ATTRIBUTE8);
684 dbms_sql.column_value(p_cur_get_RCR, 15, X_RCR_Rec.ATTRIBUTE9);
685 dbms_sql.column_value(p_cur_get_RCR, 16, X_RCR_Rec.ATTRIBUTE10);
686 dbms_sql.column_value(p_cur_get_RCR, 17, X_RCR_Rec.ATTRIBUTE11);
687 dbms_sql.column_value(p_cur_get_RCR, 18, X_RCR_Rec.ATTRIBUTE12);
688 dbms_sql.column_value(p_cur_get_RCR, 19, X_RCR_Rec.ATTRIBUTE13);
689 dbms_sql.column_value(p_cur_get_RCR, 20, X_RCR_Rec.ATTRIBUTE14);
690 dbms_sql.column_value(p_cur_get_RCR, 21, X_RCR_Rec.ATTRIBUTE15);
691
692 -- Debug Message
693 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Get Column Values Ends');
694 END Get_Column_Values;
695
696 PROCEDURE Gen_RCR_order_cl(
697 p_order_by_rec IN CSP_RS_CUST_RELATION_PUB.RCR_sort_rec_type,
698 x_order_by_cl OUT NOCOPY VARCHAR2,
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2
702 )
703 IS
704 l_order_by_cl VARCHAR2(1000) := NULL;
705 l_util_order_by_tbl AS_UTILITY_PVT.Util_order_by_tbl_type;
706 BEGIN
707 -- Debug Message
708 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Order by Begins');
709
710 -- Hint: Developer should add more statements according to CSP_sort_rec_type
711 -- Ex:
712 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
713 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
714
715 -- Debug Message
716 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Invoke AS_UTILITY_PVT.Translate_OrderBy');
717
718 AS_UTILITY_PVT.Translate_OrderBy(
719 p_api_version_number => 1.0
720 ,p_init_msg_list => FND_API.G_FALSE
721 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
722 ,p_order_by_tbl => l_util_order_by_tbl
723 ,x_order_by_clause => l_order_by_cl
724 ,x_return_status => x_return_status
725 ,x_msg_count => x_msg_count
726 ,x_msg_data => x_msg_data);
727
728 IF(l_order_by_cl IS NOT NULL) THEN
729 x_order_by_cl := 'order by' || l_order_by_cl;
730 ELSE
731 x_order_by_cl := NULL;
732 END IF;
733
734 -- Debug Message
735 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Order by Ends');
736 END Gen_RCR_order_cl;
737
738 -- This procedure bind the variables for the Dynamic SQL
739 PROCEDURE Bind(
740 P_RCR_Rec IN CSP_RS_CUST_RELATION_PUB.RCR_Rec_Type,
741 -- Hint: Add more binding variables here
742 p_cur_get_RCR IN NUMBER
743 )
744 IS
745 BEGIN
746 -- Bind variables
747 -- Only those that are not NULL
748 -- Debug Message
749 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Begins');
750
751 -- The following example applies to all columns,
752 -- developers can copy and paste them.
753 IF( (P_RCR_Rec.RS_CUST_RELATION_ID IS NOT NULL) AND (P_RCR_Rec.RS_CUST_RELATION_ID <> FND_API.G_MISS_NUM) )
754 THEN
755 DBMS_SQL.BIND_VARIABLE(p_cur_get_RCR, ':p_RS_CUST_RELATION_ID', P_RCR_Rec.RS_CUST_RELATION_ID);
756 END IF;
757
758 -- Debug Message
759 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Ends');
760 END Bind;
761
762 PROCEDURE Gen_Select(
763 x_select_cl OUT NOCOPY VARCHAR2
764 )
765 IS
766 BEGIN
767 -- Debug Message
768 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Select Begins');
769
770 x_select_cl := 'Select ' ||
771 'CSP_RS_CUST_RELATIONS.RS_CUST_RELATION_ID,' ||
772 'CSP_RS_CUST_RELATIONS.RESOURCE_TYPE,' ||
773 'CSP_RS_CUST_RELATIONS.RESOURCE_ID,' ||
774 'CSP_RS_CUST_RELATIONS.CUSTOMER_ID,' ||
775 'CSP_RS_CUST_RELATIONS.CREATED_BY,' ||
776 'CSP_RS_CUST_RELATIONS.CREATION_DATE,' ||
777 'CSP_RS_CUST_RELATIONS.LAST_UPDATED_BY,' ||
778 'CSP_RS_CUST_RELATIONS.LAST_UPDATE_DATE,' ||
779 'CSP_RS_CUST_RELATIONS.LAST_UPDATE_LOGIN,' ||
780 'CSP_RS_CUST_RELATIONS.ATTRIBUTE_CATEGORY,' ||
781 'CSP_RS_CUST_RELATIONS.ATTRIBUTE1,' ||
782 'CSP_RS_CUST_RELATIONS.ATTRIBUTE2,' ||
783 'CSP_RS_CUST_RELATIONS.ATTRIBUTE3,' ||
784 'CSP_RS_CUST_RELATIONS.ATTRIBUTE4,' ||
785 'CSP_RS_CUST_RELATIONS.ATTRIBUTE5,' ||
786 'CSP_RS_CUST_RELATIONS.ATTRIBUTE6,' ||
787 'CSP_RS_CUST_RELATIONS.ATTRIBUTE7,' ||
788 'CSP_RS_CUST_RELATIONS.ATTRIBUTE8,' ||
789 'CSP_RS_CUST_RELATIONS.ATTRIBUTE9,' ||
790 'CSP_RS_CUST_RELATIONS.ATTRIBUTE10,' ||
791 'CSP_RS_CUST_RELATIONS.ATTRIBUTE11,' ||
792 'CSP_RS_CUST_RELATIONS.ATTRIBUTE12,' ||
793 'CSP_RS_CUST_RELATIONS.ATTRIBUTE13,' ||
794 'CSP_RS_CUST_RELATIONS.ATTRIBUTE14,' ||
795 'CSP_RS_CUST_RELATIONS.ATTRIBUTE15 ' ||
796 'from CSP_RS_CUST_RELATIONS';
797 -- Debug Message
798 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Select Ends');
799
800 END Gen_Select;
801
802 PROCEDURE Gen_RCR_Where(
803 P_RCR_Rec IN CSP_RS_CUST_RELATION_PUB.RCR_Rec_Type,
804 x_RCR_where OUT NOCOPY VARCHAR2
805 )
806 IS
807 -- cursors to check if wildcard values '%' and '_' have been passed
808 -- as item values
809 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
810 SELECT INSTR(p_rec_item, '%', 1, 1)
811 FROM DUAL;
812 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
813 SELECT INSTR(p_rec_item, '_', 1, 1)
814 FROM DUAL;
815
816 -- return values from cursors
817 str_csr1 NUMBER;
818 str_csr2 NUMBER;
819 l_operator VARCHAR2(10);
820 BEGIN
821 -- Debug Message
822 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Begins');
823
824 -- There are three examples for each kind of datatype:
825 -- NUMBER, DATE, VARCHAR2.
826 -- Developer can copy and paste the following codes for your own record.
827
828 -- example for NUMBER datatype
829 IF( (P_RCR_Rec.RS_CUST_RELATION_ID IS NOT NULL) AND (P_RCR_Rec.RS_CUST_RELATION_ID <> FND_API.G_MISS_NUM) )
830 THEN
831 IF(x_RCR_where IS NULL) THEN
832 x_RCR_where := 'Where';
833 ELSE
834 x_RCR_where := x_RCR_where || ' AND ';
835 END IF;
836 x_RCR_where := x_RCR_where || 'P_RCR_Rec.RS_CUST_RELATION_ID = :p_RS_CUST_RELATION_ID';
837 END IF;
838
839 -- example for DATE datatype
840 IF( (P_RCR_Rec.CREATION_DATE IS NOT NULL) AND (P_RCR_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
841 THEN
842 -- check if item value contains '%' wildcard
843 OPEN c_chk_str1(P_RCR_Rec.CREATION_DATE);
844 FETCH c_chk_str1 INTO str_csr1;
845 CLOSE c_chk_str1;
846
847 IF(str_csr1 <> 0) THEN
848 l_operator := ' LIKE ';
849 ELSE
850 l_operator := ' = ';
851 END IF;
852
853 -- check if item value contains '_' wildcard
854 OPEN c_chk_str2(P_RCR_Rec.CREATION_DATE);
855 FETCH c_chk_str2 INTO str_csr2;
856 CLOSE c_chk_str2;
857
858 IF(str_csr2 <> 0) THEN
859 l_operator := ' LIKE ';
860 ELSE
861 l_operator := ' = ';
862 END IF;
863
864 IF(x_RCR_where IS NULL) THEN
865 x_RCR_where := 'Where ';
866 ELSE
867 x_RCR_where := x_RCR_where || ' AND ';
868 END IF;
869 x_RCR_where := x_RCR_where || 'P_RCR_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
870 END IF;
871
872 -- example for VARCHAR2 datatype
873 IF( (P_RCR_Rec.RESOURCE_TYPE IS NOT NULL) AND (P_RCR_Rec.RESOURCE_TYPE <> FND_API.G_MISS_CHAR) )
874 THEN
875 -- check if item value contains '%' wildcard
876 OPEN c_chk_str1(P_RCR_Rec.RESOURCE_TYPE);
877 FETCH c_chk_str1 INTO str_csr1;
878 CLOSE c_chk_str1;
879
880 IF(str_csr1 <> 0) THEN
881 l_operator := ' LIKE ';
882 ELSE
883 l_operator := ' = ';
884 END IF;
885
886 -- check if item value contains '_' wildcard
887 OPEN c_chk_str2(P_RCR_Rec.RESOURCE_TYPE);
888 FETCH c_chk_str2 INTO str_csr2;
889 CLOSE c_chk_str2;
890
891 IF(str_csr2 <> 0) THEN
892 l_operator := ' LIKE ';
893 ELSE
894 l_operator := ' = ';
895 END IF;
896
897 IF(x_RCR_where IS NULL) THEN
898 x_RCR_where := 'Where ';
899 ELSE
900 x_RCR_where := x_RCR_where || ' AND ';
901 END IF;
902 x_RCR_where := x_RCR_where || 'P_RCR_Rec.RESOURCE_TYPE ' || l_operator || ' :p_RESOURCE_TYPE';
903 END IF;
904
905 -- Add more IF statements for each column below
906
907 -- Debug Message
908 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Ends');
909
910 END Gen_RCR_Where;
911
912
913 -- Item-level validation procedures
914 PROCEDURE Validate_RS_CUST_RELATION_ID (
915 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
916 P_Validation_mode IN VARCHAR2,
917 P_RS_CUST_RELATION_ID IN NUMBER,
918 -- 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.
919 X_Return_Status OUT NOCOPY VARCHAR2,
920 X_Msg_Count OUT NOCOPY NUMBER,
921 X_Msg_Data OUT NOCOPY VARCHAR2
922 )
923 IS
924 BEGIN
925
926 -- Initialize message list if p_init_msg_list is set to TRUE.
927 IF FND_API.to_Boolean( p_init_msg_list )
928 THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931
932
933 -- Initialize API return status to SUCCESS
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 -- validate NOT NULL column
937 IF(p_RS_CUST_RELATION_ID is NULL)
938 THEN
939 AS_UTILITY_PVT.Debug_Message('ERROR', 'Private rs_cust_relation API: -Violate NOT NULL constraint(RS_CUST_RELATION_ID)');
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 END IF;
942
943 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
944 THEN
945 -- Hint: Validate data
946 -- IF p_RS_CUST_RELATION_ID is not NULL and p_RS_CUST_RELATION_ID <> G_MISS_CHAR
947 -- verify if data is valid
948 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
949 NULL;
950 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
951 THEN
952 -- Hint: Validate data
953 -- IF p_RS_CUST_RELATION_ID <> G_MISS_CHAR
954 -- verify if data is valid
955 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
956 NULL;
957 END IF;
958
959 -- Standard call to get message count and if count is 1, get message info.
960 FND_MSG_PUB.Count_And_Get
961 ( p_count => x_msg_count,
962 p_data => x_msg_data
963 );
964
965 END Validate_RS_CUST_RELATION_ID;
966
967
968 PROCEDURE Validate_RESOURCE_TYPE (
969 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
970 P_Validation_mode IN VARCHAR2,
971 P_RESOURCE_TYPE IN VARCHAR2,
972 -- 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.
973 X_Return_Status OUT NOCOPY VARCHAR2,
974 X_Msg_Count OUT NOCOPY NUMBER,
975 X_Msg_Data OUT NOCOPY VARCHAR2
976 )
977 IS
978 BEGIN
979
980 -- Initialize message list if p_init_msg_list is set to TRUE.
981 IF FND_API.to_Boolean( p_init_msg_list )
982 THEN
983 FND_MSG_PUB.initialize;
984 END IF;
985
986
987 -- Initialize API return status to SUCCESS
988 x_return_status := FND_API.G_RET_STS_SUCCESS;
989
990 -- validate NOT NULL column
991 IF(p_RESOURCE_TYPE is NULL)
992 THEN
993 AS_UTILITY_PVT.Debug_Message('ERROR', 'Private rs_cust_relation API: -Violate NOT NULL constraint(RESOURCE_TYPE)');
994 x_return_status := FND_API.G_RET_STS_ERROR;
995 END IF;
996
997 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
998 THEN
999 -- Hint: Validate data
1000 -- IF p_RESOURCE_TYPE is not NULL and p_RESOURCE_TYPE <> G_MISS_CHAR
1001 -- verify if data is valid
1002 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1003 NULL;
1004 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1005 THEN
1006 -- Hint: Validate data
1007 -- IF p_RESOURCE_TYPE <> G_MISS_CHAR
1008 -- verify if data is valid
1009 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1010 NULL;
1011 END IF;
1012
1013 -- Standard call to get message count and if count is 1, get message info.
1014 FND_MSG_PUB.Count_And_Get
1015 ( p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018
1019 END Validate_RESOURCE_TYPE;
1020
1021
1022 PROCEDURE Validate_RESOURCE_ID (
1023 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1024 P_Validation_mode IN VARCHAR2,
1025 P_RESOURCE_ID IN NUMBER,
1026 -- 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.
1027 X_Return_Status OUT NOCOPY VARCHAR2,
1028 X_Msg_Count OUT NOCOPY NUMBER,
1029 X_Msg_Data OUT NOCOPY VARCHAR2
1030 )
1031 IS
1032 BEGIN
1033
1034 -- Initialize message list if p_init_msg_list is set to TRUE.
1035 IF FND_API.to_Boolean( p_init_msg_list )
1036 THEN
1037 FND_MSG_PUB.initialize;
1038 END IF;
1039
1040
1041 -- Initialize API return status to SUCCESS
1042 x_return_status := FND_API.G_RET_STS_SUCCESS;
1043
1044 -- validate NOT NULL column
1045 IF(p_RESOURCE_ID is NULL)
1046 THEN
1047 AS_UTILITY_PVT.Debug_Message('ERROR', 'Private rs_cust_relation API: -Violate NOT NULL constraint(RESOURCE_ID)');
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 END IF;
1050
1051 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
1052 THEN
1053 -- Hint: Validate data
1054 -- IF p_RESOURCE_ID is not NULL and p_RESOURCE_ID <> G_MISS_CHAR
1055 -- verify if data is valid
1056 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1057 NULL;
1058 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1059 THEN
1060 -- Hint: Validate data
1061 -- IF p_RESOURCE_ID <> G_MISS_CHAR
1062 -- verify if data is valid
1063 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1064 NULL;
1065 END IF;
1066
1067 -- Standard call to get message count and if count is 1, get message info.
1068 FND_MSG_PUB.Count_And_Get
1069 ( p_count => x_msg_count,
1070 p_data => x_msg_data
1071 );
1072
1073 END Validate_RESOURCE_ID;
1074
1075
1076 PROCEDURE Validate_CUSTOMER_ID (
1077 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1078 P_Validation_mode IN VARCHAR2,
1079 P_CUSTOMER_ID IN NUMBER,
1080 -- 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.
1081 X_Return_Status OUT NOCOPY VARCHAR2,
1082 X_Msg_Count OUT NOCOPY NUMBER,
1083 X_Msg_Data OUT NOCOPY VARCHAR2
1084 )
1085 IS
1086 BEGIN
1087
1088 -- Initialize message list if p_init_msg_list is set to TRUE.
1089 IF FND_API.to_Boolean( p_init_msg_list )
1090 THEN
1091 FND_MSG_PUB.initialize;
1092 END IF;
1093
1094
1095 -- Initialize API return status to SUCCESS
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- validate NOT NULL column
1099 IF(p_CUSTOMER_ID is NULL)
1100 THEN
1101 AS_UTILITY_PVT.Debug_Message('ERROR', 'Private rs_cust_relation API: -Violate NOT NULL constraint(CUSTOMER_ID)');
1102 x_return_status := FND_API.G_RET_STS_ERROR;
1103 END IF;
1104
1105 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
1106 THEN
1107 -- Hint: Validate data
1108 -- IF p_CUSTOMER_ID is not NULL and p_CUSTOMER_ID <> G_MISS_CHAR
1109 -- verify if data is valid
1110 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1111 NULL;
1112 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1113 THEN
1114 -- Hint: Validate data
1115 -- IF p_CUSTOMER_ID <> G_MISS_CHAR
1116 -- verify if data is valid
1117 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1118 NULL;
1119 END IF;
1120
1121 -- Standard call to get message count and if count is 1, get message info.
1122 FND_MSG_PUB.Count_And_Get
1123 ( p_count => x_msg_count,
1124 p_data => x_msg_data
1125 );
1126
1127 END Validate_CUSTOMER_ID;
1128
1129
1130 -- Hint: inter-field level validation can be added here.
1131 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
1132 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1133 -- stored in database table.
1134 PROCEDURE Validate_RCR_rec(
1135 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1136 P_Validation_mode IN VARCHAR2,
1137 P_RCR_Rec IN RCR_Rec_Type,
1138 X_Return_Status OUT NOCOPY VARCHAR2,
1139 X_Msg_Count OUT NOCOPY NUMBER,
1140 X_Msg_Data OUT NOCOPY VARCHAR2
1141 )
1142 IS
1143 BEGIN
1144
1145 -- Initialize message list if p_init_msg_list is set to TRUE.
1146 IF FND_API.to_Boolean( p_init_msg_list )
1147 THEN
1148 FND_MSG_PUB.initialize;
1149 END IF;
1150
1151
1152 -- Initialize API return status to SUCCESS
1153 x_return_status := FND_API.G_RET_STS_SUCCESS;
1154
1155 -- Hint: Validate data
1156 -- If data not valid
1157 -- THEN
1158 -- x_return_status := FND_API.G_RET_STS_ERROR;
1159
1160 -- Debug Message
1161 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'API_INVALID_RECORD');
1162
1163 -- Standard call to get message count and if count is 1, get message info.
1164 FND_MSG_PUB.Count_And_Get
1165 ( p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168
1169 END Validate_RCR_Rec;
1170
1171 PROCEDURE Validate_rs_cust_relation(
1172 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1173 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1174 P_Validation_mode IN VARCHAR2,
1175 P_RCR_Rec IN RCR_Rec_Type,
1176 X_Return_Status OUT NOCOPY VARCHAR2,
1177 X_Msg_Count OUT NOCOPY NUMBER,
1178 X_Msg_Data OUT NOCOPY VARCHAR2
1179 )
1180 IS
1181 l_api_name CONSTANT VARCHAR2(30) := 'Validate_rs_cust_relation';
1182 BEGIN
1183
1184 -- Debug Message
1185 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1186
1187
1188 -- Initialize API return status to SUCCESS
1189 x_return_status := FND_API.G_RET_STS_SUCCESS;
1190
1191 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1192 -- Hint: We provide validation procedure for every column. Developer should delete
1193 -- unnecessary validation procedures.
1194 Validate_RS_CUST_RELATION_ID(
1195 p_init_msg_list => FND_API.G_FALSE,
1196 p_validation_mode => p_validation_mode,
1197 p_RS_CUST_RELATION_ID => P_RCR_Rec.RS_CUST_RELATION_ID,
1198 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1199 x_return_status => x_return_status,
1200 x_msg_count => x_msg_count,
1201 x_msg_data => x_msg_data);
1202 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1203 raise FND_API.G_EXC_ERROR;
1204 END IF;
1205
1206 Validate_RESOURCE_TYPE(
1207 p_init_msg_list => FND_API.G_FALSE,
1208 p_validation_mode => p_validation_mode,
1209 p_RESOURCE_TYPE => P_RCR_Rec.RESOURCE_TYPE,
1210 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1211 x_return_status => x_return_status,
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data);
1214 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1215 raise FND_API.G_EXC_ERROR;
1216 END IF;
1217
1218 Validate_RESOURCE_ID(
1219 p_init_msg_list => FND_API.G_FALSE,
1220 p_validation_mode => p_validation_mode,
1221 p_RESOURCE_ID => P_RCR_Rec.RESOURCE_ID,
1222 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1223 x_return_status => x_return_status,
1224 x_msg_count => x_msg_count,
1225 x_msg_data => x_msg_data);
1226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1227 raise FND_API.G_EXC_ERROR;
1228 END IF;
1229
1230 Validate_CUSTOMER_ID(
1231 p_init_msg_list => FND_API.G_FALSE,
1232 p_validation_mode => p_validation_mode,
1233 p_CUSTOMER_ID => P_RCR_Rec.CUSTOMER_ID,
1234 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1235 x_return_status => x_return_status,
1236 x_msg_count => x_msg_count,
1237 x_msg_data => x_msg_data);
1238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1239 raise FND_API.G_EXC_ERROR;
1240 END IF;
1241
1242 END IF;
1243
1244 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1245 -- Hint: Inter-field level validation can be added here
1246 -- invoke record level validation procedures
1247 Validate_RCR_Rec(
1248 p_init_msg_list => FND_API.G_FALSE,
1249 p_validation_mode => p_validation_mode,
1250 P_RCR_Rec => P_RCR_Rec,
1251 x_return_status => x_return_status,
1252 x_msg_count => x_msg_count,
1253 x_msg_data => x_msg_data);
1254
1255 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1256 raise FND_API.G_EXC_ERROR;
1257 END IF;
1258 END IF;
1259
1260 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1261 -- invoke inter-record level validation procedures
1262 NULL;
1263 END IF;
1264
1265 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1266 -- invoke inter-entity level validation procedures
1267 NULL;
1268 END IF;
1269
1270
1271 -- Debug Message
1272 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
1273
1274 END Validate_rs_cust_relation;
1275
1276 End CSP_RS_CUST_RELATION_PVT;