[Home] [Help]
PACKAGE BODY: APPS.ASO_LINE_RLTSHIP_PVT
Source
1 PACKAGE BODY ASO_LINE_RLTSHIP_PVT as
2 /* $Header: asovlinb.pls 120.1 2005/06/29 12:42:06 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_LINE_RLTSHIP_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_LINE_RLTSHIP_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovlinb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 PROCEDURE Create_line_rltship(
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_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type := ASO_QUOTE_PUB.G_MISS_LINE_RLTSHIP_REC,
22 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
23 X_LINE_RELATIONSHIP_ID OUT NOCOPY /* file.sql.39 change */ NUMBER,
24 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
25 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
26 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
27 )
28
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'Create_line_rltship';
31 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
32 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
33 l_api_version_number CONSTANT NUMBER := 1.0;
34 l_return_status_full VARCHAR2(1);
35 BEGIN
36 -- Standard Start of API savepoint
37 SAVEPOINT CREATE_LINE_RLTSHIP_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 --ASO_UTILITY_PVT.Print(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 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
68 /*
69 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
70 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
71 THEN
72 AS_CALLOUT_PKG.Create_line_rltship_BC(
73 p_api_version_number => 2.0,
74 p_init_msg_list => FND_API.G_FALSE,
75 p_commit => FND_API.G_FALSE,
76 p_validation_level => p_validation_level,
77 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
78 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
79 x_return_status => x_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data);
82 END IF;
83 */
84 -- ******************************************************************
85 -- Validate Environment
86 -- ******************************************************************
87 IF FND_GLOBAL.User_Id IS NULL
88 THEN
89 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
90 THEN
91 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
92 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
93 FND_MSG_PUB.ADD;
94 END IF;
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 /*
99 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
100 p_api_version_number => 2.0
101 ,p_salesforce_id => NULL
102 ,x_return_status => x_return_status
103 ,x_msg_count => x_msg_count
104 ,x_msg_data => x_msg_data
105 ,x_sales_member_rec => l_identity_sales_member_rec);
106
107 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110 */
111 --Commented by Bmishra on 01/23/2002 Bug # 2193415
112 /*
113 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
114 THEN
115 -- Debug message
116 -- --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_line_rltship');
117
118 -- Invoke validation procedures
119 Validate_line_rltship(
120 p_init_msg_list => FND_API.G_FALSE,
121 p_validation_level => p_validation_level,
122 p_validation_mode => ASO_UTILITY_PVT.G_CREATE,
123 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
124 x_return_status => x_return_status,
125 x_msg_count => x_msg_count,
126 x_msg_data => x_msg_data);
127 END IF;
128
129 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132 */
133 -- End of commenting by Bmishra 01/23/2002 Bug # 2193415
134
135 -- Hint: Add corresponding Master-Detail business logic here if necessary.
136
137 -- Debug Message
138 -- --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
139
140 -- Invoke table handler(ASO_LINE_RELATIONSHIPS_PKG.Insert_Row)
141 ASO_LINE_RELATIONSHIPS_PKG.Insert_Row(
142 px_LINE_RELATIONSHIP_ID => x_LINE_RELATIONSHIP_ID,
143 p_CREATION_DATE => SYSDATE,
144 p_CREATED_BY => G_USER_ID,
145 p_LAST_UPDATED_BY => G_USER_ID,
146 p_LAST_UPDATE_DATE => SYSDATE,
147 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
148 p_REQUEST_ID => p_LINE_RLTSHIP_rec.REQUEST_ID,
149 p_PROGRAM_APPLICATION_ID => p_LINE_RLTSHIP_rec.PROGRAM_APPLICATION_ID,
150 p_PROGRAM_ID => p_LINE_RLTSHIP_rec.PROGRAM_ID,
151 p_PROGRAM_UPDATE_DATE => p_LINE_RLTSHIP_rec.PROGRAM_UPDATE_DATE,
152 p_QUOTE_LINE_ID => p_LINE_RLTSHIP_rec.QUOTE_LINE_ID,
153 p_RELATED_QUOTE_LINE_ID => p_LINE_RLTSHIP_rec.RELATED_QUOTE_LINE_ID,
154 -- p_RELATIONAL_TYPE_CODE => p_LINE_RLTSHIP_rec.RELATIONAL_TYPE_CODE,
155 p_RECIPROCAL_FLAG => p_LINE_RLTSHIP_rec.RECIPROCAL_FLAG,
156 p_RELATIONSHIP_TYPE_CODE => p_LINE_RLTSHIP_rec.RELATIONSHIP_TYPE_CODE,
157 p_OBJECT_VERSION_NUMBER => p_LINE_RLTSHIP_rec.OBJECT_VERSION_NUMBER
158 );
159 -- Hint: Primary key should be returned.
160 -- x_LINE_RELATIONSHIP_ID := px_LINE_RELATIONSHIP_ID;
161
162 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 --
167 -- End of API body
168 --
169
170 -- Standard check for p_commit
171 IF FND_API.to_Boolean( p_commit )
172 THEN
173 COMMIT WORK;
174 END IF;
175
176
177 -- Debug Message
178 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
179
180
181 -- Standard call to get message count and if count is 1, get message info.
182 FND_MSG_PUB.Count_And_Get
183 ( p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186
187 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
188 /*
189 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
190 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
191 THEN
192 AS_CALLOUT_PKG.Create_line_rltship_AC(
193 p_api_version_number => 2.0,
194 p_init_msg_list => FND_API.G_FALSE,
195 p_commit => FND_API.G_FALSE,
196 p_validation_level => p_validation_level,
197 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
198 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data);
202 END IF;
203 */
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
207 P_API_NAME => L_API_NAME
208 ,P_PKG_NAME => G_PKG_NAME
209 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
210 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
211 ,X_MSG_COUNT => X_MSG_COUNT
212 ,X_MSG_DATA => X_MSG_DATA
213 ,X_RETURN_STATUS => X_RETURN_STATUS);
214
215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
216 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
217 P_API_NAME => L_API_NAME
218 ,P_PKG_NAME => G_PKG_NAME
219 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
220 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
221 ,X_MSG_COUNT => X_MSG_COUNT
222 ,X_MSG_DATA => X_MSG_DATA
223 ,X_RETURN_STATUS => X_RETURN_STATUS);
224
225 WHEN OTHERS THEN
226 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
227 P_API_NAME => L_API_NAME
228 ,P_PKG_NAME => G_PKG_NAME
229 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
230 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
231 ,X_MSG_COUNT => X_MSG_COUNT
232 ,X_MSG_DATA => X_MSG_DATA
233 ,X_RETURN_STATUS => X_RETURN_STATUS);
234
235 End Create_line_rltship;
236
237
238 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
239 PROCEDURE Update_line_rltship(
240 P_Api_Version_Number IN NUMBER,
241 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
242 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
243 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
244 -- P_Identity_Salesforce_Id IN NUMBER := NULL,
245 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
246 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
247 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
248 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
249 )
250
251 IS
252
253 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
254 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
255 Cursor C_Get_line_rltship(LINE_RELATIONSHIP_ID Number) IS
256 Select --rowid,
257 LINE_RELATIONSHIP_ID,
258 CREATION_DATE,
259 CREATED_BY,
260 LAST_UPDATED_BY,
261 LAST_UPDATE_DATE,
262 LAST_UPDATE_LOGIN,
263 REQUEST_ID,
264 PROGRAM_APPLICATION_ID,
265 PROGRAM_ID,
266 PROGRAM_UPDATE_DATE,
267 QUOTE_LINE_ID,
268 RELATED_QUOTE_LINE_ID,
269 RELATIONSHIP_TYPE_CODE,
270 RECIPROCAL_FLAG
271 -- RELATIONSHIP_TYPE_CODE
272 From ASO_LINE_RELATIONSHIPS
273 where LINE_RELATIONSHIP_ID = P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID;
274 -- Hint: Developer need to provide Where clause
275 -- For Update NOWAIT;
276
277 l_api_name CONSTANT VARCHAR2(30) := 'Update_line_rltship';
278 l_api_version_number CONSTANT NUMBER := 1.0;
279 -- Local Variables
280 --l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
281 l_ref_LINE_RLTSHIP_rec ASO_QUOTE_PUB.LINE_RLTSHIP_Rec_Type;
282 l_tar_LINE_RLTSHIP_rec ASO_QUOTE_PUB.LINE_RLTSHIP_Rec_Type := P_LINE_RLTSHIP_Rec;
283 l_rowid ROWID;
284 BEGIN
285 -- Standard Start of API savepoint
286 SAVEPOINT UPDATE_LINE_RLTSHIP_PVT;
287
288 -- Standard call to check for call compatibility.
289 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
290 p_api_version_number,
291 l_api_name,
292 G_PKG_NAME)
293 THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296
297
298 -- Initialize message list if p_init_msg_list is set to TRUE.
299 IF FND_API.to_Boolean( p_init_msg_list )
300 THEN
301 FND_MSG_PUB.initialize;
302 END IF;
303
304
305 -- Debug Message
306 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
307
308
309 -- Initialize API return status to SUCCESS
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311
312 --
313 -- Api body
314 --
315 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
316 /*
317 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
318 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
319 THEN
320 AS_CALLOUT_PKG.Update_line_rltship_BU(
321 p_api_version_number => 2.0,
322 p_init_msg_list => FND_API.G_FALSE,
323 p_commit => FND_API.G_FALSE,
327 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
324 p_validation_level => p_validation_level,
325 p_identity_salesforce_id => p_identity_salesforce_id,
326 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
328 x_return_status => x_return_status,
329 x_msg_count => x_msg_count,
330 x_msg_data => x_msg_data);
331 END IF;
332
333 */
334 Open C_Get_line_rltship( l_tar_LINE_RLTSHIP_rec.LINE_RELATIONSHIP_ID);
335
336 Fetch C_Get_line_rltship into
337 -- l_rowid,
338 l_ref_LINE_RLTSHIP_rec.LINE_RELATIONSHIP_ID,
339 l_ref_LINE_RLTSHIP_rec.CREATION_DATE,
340 l_ref_LINE_RLTSHIP_rec.CREATED_BY,
341 l_ref_LINE_RLTSHIP_rec.LAST_UPDATED_BY,
342 l_ref_LINE_RLTSHIP_rec.LAST_UPDATE_DATE,
343 l_ref_LINE_RLTSHIP_rec.LAST_UPDATE_LOGIN,
344 l_ref_LINE_RLTSHIP_rec.REQUEST_ID,
345 l_ref_LINE_RLTSHIP_rec.PROGRAM_APPLICATION_ID,
346 l_ref_LINE_RLTSHIP_rec.PROGRAM_ID,
347 l_ref_LINE_RLTSHIP_rec.PROGRAM_UPDATE_DATE,
348 l_ref_LINE_RLTSHIP_rec.QUOTE_LINE_ID,
349 l_ref_LINE_RLTSHIP_rec.RELATED_QUOTE_LINE_ID,
350 -- l_ref_LINE_RLTSHIP_rec.RELATIONAL_TYPE_CODE,
351 l_ref_LINE_RLTSHIP_rec.RELATIONSHIP_TYPE_CODE,
352 l_ref_LINE_RLTSHIP_rec.RECIPROCAL_FLAG;
353
354
355 If ( C_Get_line_rltship%NOTFOUND) Then
356 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
357 THEN
358 FND_MESSAGE.Set_Name('ASO', 'API_MISSING_UPDATE_TARGET');
359 FND_MESSAGE.Set_Token ('INFO', 'line_rltship', FALSE);
360 FND_MSG_PUB.Add;
361 END IF;
362 raise FND_API.G_EXC_ERROR;
363 END IF;
364 -- Debug Message
365 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
366 Close C_Get_line_rltship;
367
368
369
370 If (l_tar_LINE_RLTSHIP_rec.last_update_date is NULL or
371 l_tar_LINE_RLTSHIP_rec.last_update_date = FND_API.G_MISS_Date ) Then
372 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
373 THEN
374 FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
375 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
376 FND_MSG_PUB.ADD;
377 END IF;
378 raise FND_API.G_EXC_ERROR;
379 End if;
380 -- Check Whether record has been changed by someone else
381 If (l_tar_LINE_RLTSHIP_rec.last_update_date <> l_ref_LINE_RLTSHIP_rec.last_update_date) Then
382 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
383 THEN
384 FND_MESSAGE.Set_Name('ASO', 'API_RECORD_CHANGED');
385 FND_MESSAGE.Set_Token('INFO', 'line_rltship', FALSE);
386 FND_MSG_PUB.ADD;
387 END IF;
388 raise FND_API.G_EXC_ERROR;
389 End if;
390
391 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
392 THEN
393 -- Debug message
394 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_line_rltship');
395
396 -- Invoke validation procedures
397 Validate_line_rltship(
398 p_init_msg_list => FND_API.G_FALSE,
399 p_validation_level => p_validation_level,
400 p_validation_mode => ASO_UTILITY_PVT.G_UPDATE,
401 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
402 x_return_status => x_return_status,
403 x_msg_count => x_msg_count,
404 x_msg_data => x_msg_data);
405 END IF;
406
407 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 -- Hint: Add corresponding Master-Detail business logic here if necessary.
412
413 -- Debug Message
414 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
415
416 -- Invoke table handler(ASO_LINE_RELATIONSHIPS_PKG.Update_Row)
417 ASO_LINE_RELATIONSHIPS_PKG.Update_Row(
418 p_LINE_RELATIONSHIP_ID => p_LINE_RLTSHIP_rec.LINE_RELATIONSHIP_ID,
419 p_CREATION_DATE => SYSDATE,
420 p_CREATED_BY => G_USER_ID,
421 p_LAST_UPDATED_BY => G_USER_ID,
422 p_LAST_UPDATE_DATE => SYSDATE,
423 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
424 p_REQUEST_ID => p_LINE_RLTSHIP_rec.REQUEST_ID,
425 p_PROGRAM_APPLICATION_ID => p_LINE_RLTSHIP_rec.PROGRAM_APPLICATION_ID,
426 p_PROGRAM_ID => p_LINE_RLTSHIP_rec.PROGRAM_ID,
427 p_PROGRAM_UPDATE_DATE => p_LINE_RLTSHIP_rec.PROGRAM_UPDATE_DATE,
428 p_QUOTE_LINE_ID => p_LINE_RLTSHIP_rec.QUOTE_LINE_ID,
429 p_RELATED_QUOTE_LINE_ID => p_LINE_RLTSHIP_rec.RELATED_QUOTE_LINE_ID,
430 -- p_RELATIONAL_TYPE_CODE => p_LINE_RLTSHIP_rec.RELATIONAL_TYPE_CODE,
431 p_RECIPROCAL_FLAG => p_LINE_RLTSHIP_rec.RECIPROCAL_FLAG,
432 p_RELATIONSHIP_TYPE_CODE => p_LINE_RLTSHIP_rec.RELATIONSHIP_TYPE_CODE,
436 --
433 p_OBJECT_VERSION_NUMBER => p_LINE_RLTSHIP_rec.OBJECT_VERSION_NUMBER);
434 --
435 -- End of API body.
437
438 -- Standard check for p_commit
439 IF FND_API.to_Boolean( p_commit )
440 THEN
441 COMMIT WORK;
442 END IF;
443
444
445 -- Debug Message
446 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
447
448
449 -- Standard call to get message count and if count is 1, get message info.
450 FND_MSG_PUB.Count_And_Get
451 ( p_count => x_msg_count,
452 p_data => x_msg_data
453 );
454
455 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
456 /*
457 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
458 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
459 THEN
460 AS_CALLOUT_PKG.Update_line_rltship_AU(
461 p_api_version_number => 2.0,
462 p_init_msg_list => FND_API.G_FALSE,
463 p_commit => FND_API.G_FALSE,
464 p_validation_level => p_validation_level,
465 p_identity_salesforce_id => p_identity_salesforce_id,
466 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
467 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
468 x_return_status => x_return_status,
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data);
471 END IF;
472 */
473 EXCEPTION
474 WHEN FND_API.G_EXC_ERROR THEN
475 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
476 P_API_NAME => L_API_NAME
477 ,P_PKG_NAME => G_PKG_NAME
478 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
479 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
480 ,X_MSG_COUNT => X_MSG_COUNT
481 ,X_MSG_DATA => X_MSG_DATA
482 ,X_RETURN_STATUS => X_RETURN_STATUS);
483
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
486 P_API_NAME => L_API_NAME
487 ,P_PKG_NAME => G_PKG_NAME
488 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
489 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
490 ,X_MSG_COUNT => X_MSG_COUNT
491 ,X_MSG_DATA => X_MSG_DATA
492 ,X_RETURN_STATUS => X_RETURN_STATUS);
493
494 WHEN OTHERS THEN
495 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
496 P_API_NAME => L_API_NAME
497 ,P_PKG_NAME => G_PKG_NAME
498 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
499 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
500 ,X_MSG_COUNT => X_MSG_COUNT
501 ,X_MSG_DATA => X_MSG_DATA
502 ,X_RETURN_STATUS => X_RETURN_STATUS);
503 End Update_line_rltship;
504
505
506 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
507 -- The Master delete procedure may not be needed depends on different business requirements.
508
509
510
511 -- this procedure does the following:
512 -- if only the relationship_line_id is passed the relationship line is deleted and the delete is cascaded to the quote lines if necessary
513 -- if only line id is given then all relationship lines with the quote_line_id or related_quote_line_id equal to the line id are deleted.
514 -- if line id, related line id are passed and reciprocal flag is set to 'N'and the reciprocal flag is 'Y'
515 -- in the database then the relationship line is deleted and a new line is created with
516 -- related_quote_line_id = line_id and quote_line_id = related line id and reciprocal flag = 'N'
517
518
519
520
521 PROCEDURE Delete_line_rltship(
522 P_Api_Version_Number IN NUMBER,
523 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
524 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
525 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
526 p_control_rec IN ASO_QUOTE_PUB.control_rec_type := ASO_QUOTE_PUB.G_MISS_Control_Rec,
527 -- P_identity_salesforce_id IN NUMBER := NULL,
528 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
529 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
530 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
531 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
532 )
533
534 IS
535
536 CURSOR C1(line_id NUMBER) IS
537 select line_relationship_id, quote_line_id, related_quote_line_id, relationship_type_code, reciprocal_flag
538 from aso_line_relationships
539 where quote_line_id = line_id;
540
541
542 CURSOR C2(line_id NUMBER) IS
543 select line_relationship_id
544 from aso_line_relationships
545 where related_quote_line_id = line_id;
546
547 CURSOR C3(line_id NUMBER, related_line_id NUMBER) IS
551 and related_quote_line_id = related_line_id;
548 select line_relationship_id, quote_line_id, related_quote_line_id, relationship_type_code, reciprocal_flag
549 from aso_line_relationships
550 where quote_line_id = line_id
552
553
554
555 l_api_name CONSTANT VARCHAR2(30) := 'Delete_line_rltship';
556 l_api_version_number CONSTANT NUMBER := 1.0;
557 --identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
558
559 l_line_rltship_rec ASO_quote_PUB.Line_Rltship_Rec_Type;
560 l_relationship_id NUMBER;
561 l_qte_line_rec ASO_QUOTE_PUB.qte_line_Rec_Type;
562
563 BEGIN
564 -- Standard Start of API savepoint
565 SAVEPOINT DELETE_LINE_RLTSHIP_PVT;
566
567 -- Standard call to check for call compatibility.
568 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
569 p_api_version_number,
570 l_api_name,
571 G_PKG_NAME)
572 THEN
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576
577 -- Initialize message list if p_init_msg_list is set to TRUE.
578 IF FND_API.to_Boolean( p_init_msg_list )
579 THEN
580 FND_MSG_PUB.initialize;
581 END IF;
582
583
584 -- Debug Message
585 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
586
587
588 -- Initialize API return status to SUCCESS
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590
591 --
592 -- Api body
593 --
594 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
595 /*
596 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
597 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
598 THEN
599 AS_CALLOUT_PKG.Delete_line_rltship_BD(
600 p_api_version_number => 2.0,
601 p_init_msg_list => FND_API.G_FALSE,
602 p_commit => FND_API.G_FALSE,
603 p_validation_level => p_validation_level,
604 p_identity_salesforce_id => p_identity_salesforce_id,
605 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
606 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
607 x_return_status => x_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data);
610 END IF;
611
612
613 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
614 p_api_version_number => 2.0
615 ,p_salesforce_id => p_identity_salesforce_id
616 ,x_return_status => x_return_status
617 ,x_msg_count => x_msg_count
618 ,x_msg_data => x_msg_data
619 ,x_sales_member_rec => l_identity_sales_member_rec);
620
621 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624 -- Debug Message
625 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
626 */
627
628
629 IF (p_LINE_RLTSHIP_rec.quote_line_id <> FND_API.G_MISS_NUM and
630 p_LINE_RLTSHIP_rec.related_quote_line_id <> FND_API.G_MISS_NUM) THEN
631
632 FOR i in C3(p_LINE_RLTSHIP_rec.quote_line_id, p_LINE_RLTSHIP_rec.related_quote_line_id) LOOP
633 IF p_LINE_RLTSHIP_rec.reciprocal_flag = FND_API.G_MISS_CHAR OR
634 p_LINE_RLTSHIP_rec.reciprocal_flag = FND_API.G_TRUE OR
635 p_LINE_RLTSHIP_rec.reciprocal_flag = i.reciprocal_flag THEN
636
637 ASO_LINE_RELATIONSHIPS_PKG.Delete_Row(
638 p_LINE_RELATIONSHIP_ID => i.LINE_RELATIONSHIP_ID);
639
640 IF (i.RELATIONSHIP_TYPE_CODE = 'SERVICE'
641 or i.RELATIONSHIP_TYPE_CODE = 'CONFIG') then
642 null;
643
644
645 l_qte_line_rec.quote_line_id := i.related_quote_line_id;
646
647 ASO_quote_lines_PVT.Delete_Quote_Line(
648 P_Api_Version_Number => 1.0,
649 P_qte_line_Rec => l_qte_line_rec,
650 p_control_rec => p_control_rec,
651 P_Update_Header_Flag =>FND_API.G_FALSE,
652 X_Return_Status => x_return_status,
653 X_Msg_Count => x_msg_count,
654 X_Msg_Data => x_msg_data
655 );
656 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659
660
661 END IF;
662 ELSE
663
664 ASO_LINE_RELATIONSHIPS_PKG.Delete_Row(
665 p_LINE_RELATIONSHIP_ID => i.LINE_RELATIONSHIP_ID);
666
667 l_line_rltship_rec.quote_line_id := i.related_quote_line_id;
668 l_line_rltship_rec.related_quote_line_id := i.quote_line_id;
669 -- l_line_rltship_rec.relational_type_code := i.relationship_type_code;
670 l_line_rltship_rec.reciprocal_flag := FND_API.G_FALSE;
671
672 Create_line_rltship(
673 P_Api_Version_Number => 1.0,
674 P_LINE_RLTSHIP_Rec => l_line_rltship_rec,
675 X_LINE_RELATIONSHIP_ID => l_relationship_id,
676 X_Return_Status => x_return_status ,
680 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
677 X_Msg_Count => x_msg_count,
678 X_Msg_Data => x_msg_data
679 );
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683 END IF;
684 END LOOP;
685
686 ELSIF (p_LINE_RLTSHIP_rec.quote_line_id <> FND_API.G_MISS_NUM) THEN
687
688 FOR i in C1(p_LINE_RLTSHIP_rec.quote_line_id) LOOP
689
690 ASO_LINE_RELATIONSHIPS_PKG.Delete_Row(
691 p_LINE_RELATIONSHIP_ID => i.LINE_RELATIONSHIP_ID);
692
693 IF (i.RELATIONSHIP_TYPE_CODE = 'SERVICE'
694 or i.RELATIONSHIP_TYPE_CODE = 'CONFIG') then
695 l_qte_line_rec.quote_line_id := i.related_quote_line_id;
696
697 ASO_quote_lines_PVT.Delete_Quote_Line(
698 P_Api_Version_Number => 1.0,
699 P_qte_line_Rec => l_qte_line_rec,
700 p_control_rec => p_control_rec,
701 P_Update_Header_Flag =>FND_API.G_FALSE,
702 X_Return_Status => x_return_status,
703 X_Msg_Count => x_msg_count,
704 X_Msg_Data => x_msg_data
705 );
706 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709 END IF;
710 END LOOP;
711
712 FOR i in C2(p_LINE_RLTSHIP_rec.quote_line_id) LOOP
713 ASO_LINE_RELATIONSHIPS_PKG.Delete_Row(
714 p_LINE_RELATIONSHIP_ID => i.LINE_RELATIONSHIP_ID);
715 END LOOP;
716
717
718 ELSIF (p_LINE_RLTSHIP_rec.line_relationship_id <> FND_API.G_MISS_NUM) THEN
719 ASO_LINE_RELATIONSHIPS_PKG.Delete_Row(
720 p_LINE_RELATIONSHIP_ID => p_LINE_RLTSHIP_rec.LINE_RELATIONSHIP_ID);
721
722 END IF;
723
724
725
726
727
728 --
729 -- End of API body
730 --
731
732 -- Standard check for p_commit
733 IF FND_API.to_Boolean( p_commit )
734 THEN
735 COMMIT WORK;
736 END IF;
737
738
739 -- Debug Message
740 -- ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
741
742
743 -- Standard call to get message count and if count is 1, get message info.
744 FND_MSG_PUB.Count_And_Get
745 ( p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
750 /*
751 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
752 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
753 THEN
754 AS_CALLOUT_PKG.Delete_line_rltship_AD(
755 p_api_version_number => 2.0,
756 p_init_msg_list => FND_API.G_FALSE,
757 p_commit => FND_API.G_FALSE,
758 p_validation_level => p_validation_level,
759 p_identity_salesforce_id => p_identity_salesforce_id,
760 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
761 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
762 x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data);
765 END IF;
766 */
767 EXCEPTION
768
769 WHEN FND_API.G_EXC_ERROR THEN
770 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
771 P_API_NAME => L_API_NAME
772 ,P_PKG_NAME => G_PKG_NAME
773 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
774 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
775 ,X_MSG_COUNT => X_MSG_COUNT
776 ,X_MSG_DATA => X_MSG_DATA
777 ,X_RETURN_STATUS => X_RETURN_STATUS);
778
779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
780 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
781 P_API_NAME => L_API_NAME
782 ,P_PKG_NAME => G_PKG_NAME
783 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
784 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
785 ,X_MSG_COUNT => X_MSG_COUNT
786 ,X_MSG_DATA => X_MSG_DATA
787 ,X_RETURN_STATUS => X_RETURN_STATUS);
788
789 WHEN OTHERS THEN
790 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
791 P_API_NAME => L_API_NAME
792 ,P_PKG_NAME => G_PKG_NAME
793 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
794 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
795 ,X_MSG_COUNT => X_MSG_COUNT
796 ,X_MSG_DATA => X_MSG_DATA
797 ,X_RETURN_STATUS => X_RETURN_STATUS);
798
799 End Delete_line_rltship;
800
801
802 -- This procudure defines the columns for the Dynamic SQL.
803 PROCEDURE Define_Columns(
804 P_LINE_RLTSHIP_Rec IN ASO_QUOTE_PUB.LINE_RLTSHIP_Rec_Type,
805 p_cur_get_LINE_RLTSHIP IN NUMBER
806 )
807 IS
808 BEGIN
809 -- Debug Message
810 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Begins');
811 /*
815 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 3, P_LINE_RLTSHIP_Rec.REQUEST_ID);
812 -- define all columns for ASO_QUOTE_LINES_V view
813 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 1, P_LINE_RLTSHIP_Rec.QUOTE_LINE_ID);
814 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 2, P_LINE_RLTSHIP_Rec.QUOTE_HEADER_ID);
816 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 4, P_LINE_RLTSHIP_Rec.PROGRAM_APPLICATION_ID);
817 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 5, P_LINE_RLTSHIP_Rec.PROGRAM_ID);
818 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 6, P_LINE_RLTSHIP_Rec.PROGRAM_UPDATE_DATE);
819 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 7, P_LINE_RLTSHIP_Rec.ORG_ID);
820 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 8, P_LINE_RLTSHIP_Rec.LINE_CATEGORY_CODE, 30);
821 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 9, P_LINE_RLTSHIP_Rec.ITEM_TYPE_CODE, 30);
822 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 10, P_LINE_RLTSHIP_Rec.LINE_NUMBER);
823 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 11, P_LINE_RLTSHIP_Rec.START_DATE_ACTIVE);
824 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 12, P_LINE_RLTSHIP_Rec.END_DATE_ACTIVE);
825 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 13, P_LINE_RLTSHIP_Rec.ORDER_LINE_TYPE_ID);
826 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 14, P_LINE_RLTSHIP_Rec.ORGANIZATION_ID);
827 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 15, P_LINE_RLTSHIP_Rec.INVENTORY_ITEM_ID);
828 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 16, P_LINE_RLTSHIP_Rec.QUANTITY);
829 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 17, P_LINE_RLTSHIP_Rec.UOM_CODE, 3);
830 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 18, P_LINE_RLTSHIP_Rec.MARKETING_SOURCE_CODE_ID);
831 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 19, P_LINE_RLTSHIP_Rec.PRICE_LIST_ID);
832 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 20, P_LINE_RLTSHIP_Rec.PRICE_LIST_NAME, 240);
833 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 21, P_LINE_RLTSHIP_Rec.PRICE_LIST_LINE_ID);
834 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 22, P_LINE_RLTSHIP_Rec.CURRENCY_CODE, 15);
835 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 23, P_LINE_RLTSHIP_Rec.LINE_LIST_PRICE);
836 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 24, P_LINE_RLTSHIP_Rec.LINE_ADJUSTED_AMOUNT);
837 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 25, P_LINE_RLTSHIP_Rec.LINE_ADJUSTED_PERCENT);
838 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 26, P_LINE_RLTSHIP_Rec.LINE_QUOTE_PRICE);
839 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 27, P_LINE_RLTSHIP_Rec.RELATED_ITEM_ID);
840 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 28, P_LINE_RLTSHIP_Rec.ITEM_RELATIONSHIP_TYPE, 15);
841 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 29, P_LINE_RLTSHIP_Rec.ACCOUNTING_RULE_ID);
842 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 30, P_LINE_RLTSHIP_Rec.INVOICING_RULE_ID);
843 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 31, P_LINE_RLTSHIP_Rec.SPLIT_SHIPMENT_FLAG, 1);
844 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 32, P_LINE_RLTSHIP_Rec.BACKORDER_FLAG, 1);
845 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 33, P_LINE_RLTSHIP_Rec.QUOTE_LINE_DETAIL_ID);
846 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 34, P_LINE_RLTSHIP_Rec.SERVICE_COTERMINATE_FLAG, 240);
847 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 35, P_LINE_RLTSHIP_Rec.SERVICE_DURATION);
848 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 36, P_LINE_RLTSHIP_Rec.SERVICE_UNIT_SELLING_PERCENT);
849 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 37, P_LINE_RLTSHIP_Rec.SERVICE_UNIT_LIST_PERCENT);
850 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 38, P_LINE_RLTSHIP_Rec.SERVICE_NUMBER);
851 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 39, P_LINE_RLTSHIP_Rec.UNIT_PERCENT_BASE_PRICE);
852 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 40, P_LINE_RLTSHIP_Rec.SERVICE_PERIOD, 240);
853 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 41, P_LINE_RLTSHIP_Rec.ATTRIBUTE_CATEGORY, 30);
854 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 42, P_LINE_RLTSHIP_Rec.ATTRIBUTE1, 150);
855 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 43, P_LINE_RLTSHIP_Rec.ATTRIBUTE2, 150);
856 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 44, P_LINE_RLTSHIP_Rec.ATTRIBUTE3, 150);
857 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 45, P_LINE_RLTSHIP_Rec.ATTRIBUTE4, 150);
858 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 46, P_LINE_RLTSHIP_Rec.ATTRIBUTE5, 150);
859 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 47, P_LINE_RLTSHIP_Rec.ATTRIBUTE6, 150);
860 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 48, P_LINE_RLTSHIP_Rec.ATTRIBUTE7, 150);
861 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 49, P_LINE_RLTSHIP_Rec.ATTRIBUTE8, 150);
862 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 50, P_LINE_RLTSHIP_Rec.ATTRIBUTE9, 150);
863 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 51, P_LINE_RLTSHIP_Rec.ATTRIBUTE10, 150);
864 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 52, P_LINE_RLTSHIP_Rec.ATTRIBUTE11, 150);
865 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 53, P_LINE_RLTSHIP_Rec.ATTRIBUTE12, 150);
866 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 54, P_LINE_RLTSHIP_Rec.ATTRIBUTE13, 150);
867 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 55, P_LINE_RLTSHIP_Rec.ATTRIBUTE14, 150);
868 dbms_sql.define_column(p_cur_get_LINE_RLTSHIP, 56, P_LINE_RLTSHIP_Rec.ATTRIBUTE15, 150);
869
870 -- Debug Message
871 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Define Columns Ends');
872 */
873 null;
874 END Define_Columns;
875
876 -- This procudure gets column values by the Dynamic SQL.
877 PROCEDURE Get_Column_Values(
878 p_cur_get_LINE_RLTSHIP IN NUMBER,
882 BEGIN
879 X_LINE_RLTSHIP_Rec OUT NOCOPY /* file.sql.39 change */ ASO_quote_PUB.LINE_RLTSHIP_Rec_Type
880 )
881 IS
883 null;
884 END Get_Column_Values;
885
886 -- This procedure bind the variables for the Dynamic SQL
887 PROCEDURE Bind(
888 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
889 -- Hint: Add more binding variables here
890 p_cur_get_LINE_RLTSHIP IN NUMBER
891 )
892 IS
893 BEGIN
894 -- Bind variables
895 -- Only those that are not NULL
896 -- Debug Message
897 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Begins');
898
899 -- The following example applies to all columns,
900 -- developers can copy and paste them.
901 IF( (P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID IS NOT NULL) AND (P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID <> FND_API.G_MISS_NUM) )
902 THEN
903 DBMS_SQL.BIND_VARIABLE(p_cur_get_LINE_RLTSHIP, ':p_LINE_RELATIONSHIP_ID', P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID);
904 END IF;
905
906 -- Debug Message
907 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Bind Variables Ends');
908 END Bind;
909
910 PROCEDURE Gen_Select(
911 x_select_cl OUT NOCOPY /* file.sql.39 change */ VARCHAR2
912 )
913 IS
914 BEGIN
915 null;
916 END Gen_Select;
917
918 PROCEDURE Gen_LINE_RLTSHIP_Where(
919 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
920 x_LINE_RLTSHIP_where OUT NOCOPY /* file.sql.39 change */ VARCHAR2
921 )
922 IS
923 -- cursors to check if wildcard values '%' and '_' have been passed
924 -- as item values
925 -- return values from cursors
926 str_csr1 NUMBER;
927 str_csr2 NUMBER;
928 l_operator VARCHAR2(10);
929 BEGIN
930 -- Debug Message
931 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Begins');
932
933 -- There are three example for each kind of datatype:
934 -- NUMBER, DATE, VARCHAR2.
935 -- Developer can copy and paste the following codes for your own record.
936
937 -- example for NUMBER datatype
938 IF( (P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID IS NOT NULL) AND (P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID <> FND_API.G_MISS_NUM) )
939 THEN
940 IF(x_LINE_RLTSHIP_where IS NULL) THEN
941 x_LINE_RLTSHIP_where := 'Where';
942 ELSE
943 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || ' AND ';
944 END IF;
945 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || 'P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID = :p_LINE_RELATIONSHIP_ID';
946 END IF;
947
948 -- example for DATE datatype
949 IF( (P_LINE_RLTSHIP_Rec.CREATION_DATE IS NOT NULL) AND (P_LINE_RLTSHIP_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
950 THEN
951 -- check if item value contains '%' wildcard
952
953 str_csr1 := INSTR(P_LINE_RLTSHIP_Rec.CREATION_DATE, '%', 1, 1);
954
955 IF(str_csr1 <> 0) THEN
956 l_operator := ' LIKE ';
957 ELSE
958 l_operator := ' = ';
959 END IF;
960
961 -- check if item value contains '_' wildcard
962
963 str_csr2 := INSTR(P_LINE_RLTSHIP_Rec.CREATION_DATE, '_', 1, 1);
964
965 IF(str_csr2 <> 0) THEN
966 l_operator := ' LIKE ';
967 ELSE
968 l_operator := ' = ';
969 END IF;
970
971 IF(x_LINE_RLTSHIP_where IS NULL) THEN
972 x_LINE_RLTSHIP_where := 'Where ';
973 ELSE
974 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || ' AND ';
975 END IF;
976 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || 'P_LINE_RLTSHIP_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
977 END IF;
978
979 -- example for VARCHAR2 datatype
980 IF( (P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE IS NOT NULL) AND (P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE <> FND_API.G_MISS_CHAR) )
981 THEN
982 -- check if item value contains '%' wildcard
983 /*
984 OPEN c_chk_str1(P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE);
985 FETCH c_chk_str1 INTO str_csr1;
986 CLOSE c_chk_str1;
987 */
988
989 str_csr1 := INSTR(P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE, '%', 1, 1);
990
991 IF(str_csr1 <> 0) THEN
992 l_operator := ' LIKE ';
993 ELSE
994 l_operator := ' = ';
995 END IF;
996
997 -- check if item value contains '_' wildcard
998 /*
999 OPEN c_chk_str2(P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE);
1000 FETCH c_chk_str2 INTO str_csr2;
1001 CLOSE c_chk_str2;
1002 */
1003
1004 str_csr2 := INSTR(P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE, '%', 1, 1);
1005
1006 IF(str_csr2 <> 0) THEN
1007 l_operator := ' LIKE ';
1008 ELSE
1009 l_operator := ' = ';
1010 END IF;
1011
1012 IF(x_LINE_RLTSHIP_where IS NULL) THEN
1013 x_LINE_RLTSHIP_where := 'Where ';
1014 ELSE
1015 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || ' AND ';
1016 END IF;
1020 -- Add more IF statements for each column below
1017 x_LINE_RLTSHIP_where := x_LINE_RLTSHIP_where || 'P_LINE_RLTSHIP_Rec.RELATIONAL_TYPE_CODE ' || l_operator || ' :p_RELATIONAL_TYPE_CODE';
1018 END IF;
1019
1021
1022 -- Debug Message
1023 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Generate Where Ends');
1024
1025 END Gen_LINE_RLTSHIP_Where;
1026
1027 -- Item-level validation procedures
1028 PROCEDURE Validate_LINE_RELATIONSHIP_ID (
1029 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1030 P_Validation_mode IN VARCHAR2,
1031 P_LINE_RELATIONSHIP_ID IN NUMBER,
1032 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1033 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1034 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1035 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1036 )
1037 IS
1038 l_count NUMBER;
1039 BEGIN
1040
1041 -- Initialize message list if p_init_msg_list is set to TRUE.
1042 IF FND_API.to_Boolean( p_init_msg_list )
1043 THEN
1044 FND_MSG_PUB.initialize;
1045 END IF;
1046
1047
1048 -- Initialize API return status to SUCCESS
1049 x_return_status := FND_API.G_RET_STS_SUCCESS;
1050
1051 -- validate NOT NULL column
1052 IF(p_LINE_RELATIONSHIP_ID is NULL)
1053 THEN
1054 --ASO_UTILITY_PVT.Print('ERROR', 'Private line_rltship API: -Violate NOT NULL constraint(LINE_RELATIONSHIP_ID)');
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 END IF;
1057
1058 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1059 THEN
1060 -- Hint: Validate data
1061 -- IF p_LINE_RELATIONSHIP_ID is not NULL and p_LINE_RELATIONSHIP_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 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1066 THEN
1067 -- Hint: Validate data
1068 -- IF p_LINE_RELATIONSHIP_ID <> G_MISS_CHAR
1069 -- verify if data is valid
1070 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1071
1072 IF p_LINE_RELATIONSHIP_ID is not NULL AND p_LINE_RELATIONSHIP_ID <> FND_API.G_MISS_NUM THEN
1073
1074 select count(*) into l_count
1075 from aso_line_relationships
1076 where line_relationship_id = p_LINE_RELATIONSHIP_ID;
1077
1078 IF l_count < 1 THEN
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 END IF;
1081 END IF;
1082 END IF;
1083
1084 -- Standard call to get message count and if count is 1, get message info.
1085 FND_MSG_PUB.Count_And_Get
1086 ( p_count => x_msg_count,
1087 p_data => x_msg_data
1088 );
1089
1090 END Validate_LINE_RELATIONSHIP_ID;
1091
1092
1093 PROCEDURE Validate_REQUEST_ID (
1094 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1095 P_Validation_mode IN VARCHAR2,
1096 P_REQUEST_ID IN NUMBER,
1097 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1098 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1099 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1100 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1101 )
1102 IS
1103 BEGIN
1104
1105 -- Initialize message list if p_init_msg_list is set to TRUE.
1106 IF FND_API.to_Boolean( p_init_msg_list )
1107 THEN
1108 FND_MSG_PUB.initialize;
1109 END IF;
1110
1111
1112 -- Initialize API return status to SUCCESS
1113 x_return_status := FND_API.G_RET_STS_SUCCESS;
1114
1115 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1116 THEN
1117 -- Hint: Validate data
1118 -- IF p_REQUEST_ID is not NULL and p_REQUEST_ID <> G_MISS_CHAR
1119 -- verify if data is valid
1120 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1121 NULL;
1122 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1123 THEN
1124 -- Hint: Validate data
1125 -- IF p_REQUEST_ID <> G_MISS_CHAR
1126 -- verify if data is valid
1127 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1128 NULL;
1129 END IF;
1130
1131 -- Standard call to get message count and if count is 1, get message info.
1132 FND_MSG_PUB.Count_And_Get
1133 ( p_count => x_msg_count,
1134 p_data => x_msg_data
1135 );
1136
1137 END Validate_REQUEST_ID;
1138
1139
1140 PROCEDURE Validate_PROG_APPL_ID (
1141 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1142 P_Validation_mode IN VARCHAR2,
1146 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1143 P_PROGRAM_APPLICATION_ID IN NUMBER,
1144 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1145 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1147 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1148 )
1149 IS
1150 BEGIN
1151
1152 -- Initialize message list if p_init_msg_list is set to TRUE.
1153 IF FND_API.to_Boolean( p_init_msg_list )
1154 THEN
1155 FND_MSG_PUB.initialize;
1156 END IF;
1157
1158
1159 -- Initialize API return status to SUCCESS
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161
1162 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1163 THEN
1164 -- Hint: Validate data
1165 -- IF p_PROGRAM_APPLICATION_ID is not NULL and p_PROGRAM_APPLICATION_ID <> G_MISS_CHAR
1166 -- verify if data is valid
1167 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1168 NULL;
1169 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1170 THEN
1171 -- Hint: Validate data
1172 -- IF p_PROGRAM_APPLICATION_ID <> G_MISS_CHAR
1173 -- verify if data is valid
1174 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1175 NULL;
1176 END IF;
1177
1178 -- Standard call to get message count and if count is 1, get message info.
1179 FND_MSG_PUB.Count_And_Get
1180 ( p_count => x_msg_count,
1181 p_data => x_msg_data
1182 );
1183
1184 END Validate_PROG_APPL_ID;
1185
1186
1187 PROCEDURE Validate_PROGRAM_ID (
1188 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1189 P_Validation_mode IN VARCHAR2,
1190 P_PROGRAM_ID IN NUMBER,
1191 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1192 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1193 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1194 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1195 )
1196 IS
1197 BEGIN
1198
1199 -- Initialize message list if p_init_msg_list is set to TRUE.
1200 IF FND_API.to_Boolean( p_init_msg_list )
1201 THEN
1202 FND_MSG_PUB.initialize;
1203 END IF;
1204
1205
1206 -- Initialize API return status to SUCCESS
1207 x_return_status := FND_API.G_RET_STS_SUCCESS;
1208
1209 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1210 THEN
1211 -- Hint: Validate data
1212 -- IF p_PROGRAM_ID is not NULL and p_PROGRAM_ID <> G_MISS_CHAR
1213 -- verify if data is valid
1214 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1215 NULL;
1216 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1217 THEN
1218 -- Hint: Validate data
1219 -- IF p_PROGRAM_ID <> G_MISS_CHAR
1220 -- verify if data is valid
1221 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1222 NULL;
1223 END IF;
1224
1225 -- Standard call to get message count and if count is 1, get message info.
1226 FND_MSG_PUB.Count_And_Get
1227 ( p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230
1231 END Validate_PROGRAM_ID;
1232
1233
1234 PROCEDURE Validate_PROGRAM_UPDATE_DATE (
1235 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1236 P_Validation_mode IN VARCHAR2,
1237 P_PROGRAM_UPDATE_DATE IN DATE,
1238 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1239 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1240 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1241 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1242 )
1243 IS
1244 BEGIN
1245
1246 -- Initialize message list if p_init_msg_list is set to TRUE.
1247 IF FND_API.to_Boolean( p_init_msg_list )
1248 THEN
1249 FND_MSG_PUB.initialize;
1250 END IF;
1251
1252
1253 -- Initialize API return status to SUCCESS
1254 x_return_status := FND_API.G_RET_STS_SUCCESS;
1255
1256 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1257 THEN
1258 -- Hint: Validate data
1259 -- IF p_PROGRAM_UPDATE_DATE is not NULL and p_PROGRAM_UPDATE_DATE <> G_MISS_CHAR
1260 -- verify if data is valid
1261 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1262 NULL;
1263 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1264 THEN
1265 -- Hint: Validate data
1266 -- IF p_PROGRAM_UPDATE_DATE <> G_MISS_CHAR
1267 -- verify if data is valid
1268 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1269 NULL;
1273 FND_MSG_PUB.Count_And_Get
1270 END IF;
1271
1272 -- Standard call to get message count and if count is 1, get message info.
1274 ( p_count => x_msg_count,
1275 p_data => x_msg_data
1276 );
1277
1278 END Validate_PROGRAM_UPDATE_DATE;
1279
1280
1281 PROCEDURE Validate_QUOTE_LINE_ID (
1282 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1283 P_Validation_mode IN VARCHAR2,
1284 P_QUOTE_LINE_ID IN NUMBER,
1285 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1286 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1287 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1288 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1289 )
1290 IS
1291 l_count NUMBER;
1292 BEGIN
1293
1294 -- Initialize message list if p_init_msg_list is set to TRUE.
1295 IF FND_API.to_Boolean( p_init_msg_list )
1296 THEN
1297 FND_MSG_PUB.initialize;
1298 END IF;
1299
1300
1301 -- Initialize API return status to SUCCESS
1302 x_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304 -- validate NOT NULL column
1305 IF(p_QUOTE_LINE_ID is NULL)
1306 THEN
1307 --ASO_UTILITY_PVT.Print('ERROR', 'Private line_rltship API: -Violate NOT NULL constraint(QUOTE_LINE_ID)');
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309 END IF;
1310
1311 IF p_QUOTE_LINE_ID is not NULL AND p_QUOTE_LINE_ID <> FND_API.G_MISS_NUM THEN
1312 select count(*) into l_count
1313 from aso_quote_lines_all
1314 where quote_line_id = p_QUOTE_LINE_ID;
1315
1316 if l_count < 1 then
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1318 end if;
1319 END IF;
1320
1321 -- Standard call to get message count and if count is 1, get message info.
1322 FND_MSG_PUB.Count_And_Get
1323 ( p_count => x_msg_count,
1324 p_data => x_msg_data
1325 );
1326
1327 END Validate_QUOTE_LINE_ID;
1328
1329
1330 PROCEDURE Validate_RELATED_QUOTE_LINE_ID (
1331 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1332 P_Validation_mode IN VARCHAR2,
1333 P_RELATED_QUOTE_LINE_ID IN NUMBER,
1334 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1335 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1336 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1337 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1338 )
1339 IS
1340 l_count NUMBER;
1341 BEGIN
1342
1343 -- Initialize message list if p_init_msg_list is set to TRUE.
1344 IF FND_API.to_Boolean( p_init_msg_list )
1345 THEN
1346 FND_MSG_PUB.initialize;
1347 END IF;
1348
1349
1350 -- Initialize API return status to SUCCESS
1351 x_return_status := FND_API.G_RET_STS_SUCCESS;
1352
1353 -- validate NOT NULL column
1354 IF(p_RELATED_QUOTE_LINE_ID is NULL)
1355 THEN
1356 --ASO_UTILITY_PVT.Print('ERROR', 'Private line_rltship API: -Violate NOT NULL constraint(RELATED_QUOTE_LINE_ID)');
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 END IF;
1359
1360
1361 IF p_RELATED_QUOTE_LINE_ID is not NULL AND p_RELATED_QUOTE_LINE_ID <> FND_API.G_MISS_NUM THEN
1362 select count(*) into l_count
1363 from aso_quote_lines_all
1364 where quote_line_id = p_RELATED_QUOTE_LINE_ID;
1365
1366 if l_count < 1 then
1367 x_return_status := FND_API.G_RET_STS_ERROR;
1368 end if;
1369 END IF;
1370
1371
1372 -- Standard call to get message count and if count is 1, get message info.
1373 FND_MSG_PUB.Count_And_Get
1374 ( p_count => x_msg_count,
1375 p_data => x_msg_data
1376 );
1377
1378 END Validate_RELATED_QUOTE_LINE_ID;
1379
1380
1381 PROCEDURE Validate_RELATIONAL_TYPE_CODE (
1382 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1383 P_Validation_mode IN VARCHAR2,
1384 P_RELATIONAL_TYPE_CODE IN VARCHAR2,
1385 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1386 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1387 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1388 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1389 )
1390 IS
1391 l_count NUMBER;
1392 BEGIN
1393
1394 -- Initialize message list if p_init_msg_list is set to TRUE.
1395 IF FND_API.to_Boolean( p_init_msg_list )
1396 THEN
1397 FND_MSG_PUB.initialize;
1398 END IF;
1399
1400
1401 -- Initialize API return status to SUCCESS
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1408 -- verify if data is valid
1405 THEN
1406 -- Hint: Validate data
1407 -- IF p_RELATIONAL_TYPE_CODE is not NULL and p_RELATIONAL_TYPE_CODE <> G_MISS_CHAR
1409 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1410 NULL;
1411 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1412 THEN
1413 -- Hint: Validate data
1414 -- IF p_RELATIONAL_TYPE_CODE <> G_MISS_CHAR
1415 -- verify if data is valid
1416 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1417 NULL;
1418 END IF;
1419
1420 IF p_RELATIONAL_TYPE_CODE is not NULL AND p_RELATIONAL_TYPE_CODE <> FND_API.G_MISS_NUM THEN
1421 select count(*) into l_count
1422 from aso_lookups
1423 where lookup_type = 'ASO_LINE_RELATIONSHIP_TYPE'
1424 and lookup_code = p_RELATIONAL_TYPE_CODE;
1425
1426 if l_count < 1 then
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 end if;
1429 END IF;
1430
1431 -- Standard call to get message count and if count is 1, get message info.
1432 FND_MSG_PUB.Count_And_Get
1433 ( p_count => x_msg_count,
1434 p_data => x_msg_data
1435 );
1436
1437 END Validate_RELATIONAL_TYPE_CODE;
1438
1439
1440 PROCEDURE Validate_RECIPROCAL_FLAG (
1441 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1442 P_Validation_mode IN VARCHAR2,
1443 P_RECIPROCAL_FLAG IN VARCHAR2,
1444 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1445 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1446 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1447 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1448 )
1449 IS
1450 l_count NUMBER;
1451 BEGIN
1452
1453 -- Initialize message list if p_init_msg_list is set to TRUE.
1454 IF FND_API.to_Boolean( p_init_msg_list )
1455 THEN
1456 FND_MSG_PUB.initialize;
1457 END IF;
1458
1459
1460 -- Initialize API return status to SUCCESS
1461 x_return_status := FND_API.G_RET_STS_SUCCESS;
1462
1463 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1464 THEN
1465 -- Hint: Validate data
1466 -- IF p_RECIPROCAL_FLAG is not NULL and p_RECIPROCAL_FLAG <> G_MISS_CHAR
1467 -- verify if data is valid
1468 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1469 NULL;
1470 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1471 THEN
1472 -- Hint: Validate data
1473 -- IF p_RECIPROCAL_FLAG <> G_MISS_CHAR
1474 -- verify if data is valid
1475 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1476 NULL;
1477 END IF;
1478
1479 IF(p_validation_mode is not NULL AND p_RECIPROCAL_FLAG <> FND_API.G_MISS_CHAR)THEN
1480 IF (p_RECIPROCAL_FLAG <> FND_API.G_TRUE and p_RECIPROCAL_FLAG <> FND_API.G_FALSE) THEN
1481 x_return_status := FND_API.G_RET_STS_ERROR;
1482 END IF;
1483 END IF;
1484
1485 -- Standard call to get message count and if count is 1, get message info.
1486 FND_MSG_PUB.Count_And_Get
1487 ( p_count => x_msg_count,
1488 p_data => x_msg_data
1489 );
1490
1491 END Validate_RECIPROCAL_FLAG;
1492
1493
1494 PROCEDURE Validate_RLTSHIP_TYPE_CODE (
1495 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1496 P_Validation_mode IN VARCHAR2,
1497 P_RELATIONSHIP_TYPE_CODE IN VARCHAR2,
1498 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY /* file.sql.39 change */ ASO_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1499 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1500 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1501 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1502 )
1503 IS
1504 l_count NUMBER;
1505 BEGIN
1506
1507 -- Initialize message list if p_init_msg_list is set to TRUE.
1508 IF FND_API.to_Boolean( p_init_msg_list )
1509 THEN
1510 FND_MSG_PUB.initialize;
1511 END IF;
1512
1513
1514 -- Initialize API return status to SUCCESS
1515 x_return_status := FND_API.G_RET_STS_SUCCESS;
1516
1517 IF(p_validation_mode = ASO_UTILITY_PVT.G_CREATE)
1518 THEN
1519 -- Hint: Validate data
1520 -- IF p_RELATIONSHIP_TYPE_CODE is not NULL and p_RELATIONSHIP_TYPE_CODE <> G_MISS_CHAR
1521 -- verify if data is valid
1522 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1523 NULL;
1524 ELSIF(p_validation_mode = ASO_UTILITY_PVT.G_UPDATE)
1525 THEN
1526 -- Hint: Validate data
1527 -- IF p_RELATIONSHIP_TYPE_CODE <> G_MISS_CHAR
1528 -- verify if data is valid
1529 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1530 NULL;
1531 END IF;
1532
1533 -- Standard call to get message count and if count is 1, get message info.
1534 FND_MSG_PUB.Count_And_Get
1535 ( p_count => x_msg_count,
1539 END Validate_RLTSHIP_TYPE_CODE;
1536 p_data => x_msg_data
1537 );
1538
1540
1541
1542 -- Hint: inter-field level validation can be added here.
1543 -- Hint: If p_validation_mode = ASO_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
1544 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1545 -- stored in database table.
1546 PROCEDURE Validate_LINE_RLTSHIP_rec(
1547 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1548 P_Validation_mode IN VARCHAR2,
1549 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
1550 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1551 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1552 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1553 )
1554 IS
1555 BEGIN
1556
1557 -- Initialize message list if p_init_msg_list is set to TRUE.
1558 IF FND_API.to_Boolean( p_init_msg_list )
1559 THEN
1560 FND_MSG_PUB.initialize;
1561 END IF;
1562
1563
1564 -- Initialize API return status to SUCCESS
1565 x_return_status := FND_API.G_RET_STS_SUCCESS;
1566
1567 -- Hint: Validate data
1568 -- If data not valid
1569 -- THEN
1570 -- x_return_status := FND_API.G_RET_STS_ERROR;
1571
1572 -- Debug Message
1573 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'API_INVALID_RECORD');
1574
1575 -- Standard call to get message count and if count is 1, get message info.
1576 FND_MSG_PUB.Count_And_Get
1577 ( p_count => x_msg_count,
1578 p_data => x_msg_data
1579 );
1580
1581 END Validate_LINE_RLTSHIP_Rec;
1582
1583 PROCEDURE Validate_line_rltship(
1584 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1585 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1586 P_Validation_mode IN VARCHAR2,
1587 P_LINE_RLTSHIP_Rec IN ASO_quote_PUB.LINE_RLTSHIP_Rec_Type,
1588 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1589 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1590 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1591 )
1592 IS
1593 l_api_name CONSTANT VARCHAR2(30) := 'Validate_line_rltship';
1594 BEGIN
1595
1596 -- Debug Message
1597 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1598
1599
1600 -- Initialize API return status to SUCCESS
1601 x_return_status := FND_API.G_RET_STS_SUCCESS;
1602
1603 IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_ITEM) THEN
1604 -- Hint: We provide validation procedure for every column. Developer should delete
1605 -- unnecessary validation procedures.
1606 Validate_LINE_RELATIONSHIP_ID(
1607 p_init_msg_list => FND_API.G_FALSE,
1608 p_validation_mode => p_validation_mode,
1609 p_LINE_RELATIONSHIP_ID => P_LINE_RLTSHIP_Rec.LINE_RELATIONSHIP_ID,
1610 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1611 x_return_status => x_return_status,
1612 x_msg_count => x_msg_count,
1613 x_msg_data => x_msg_data);
1614 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1615 raise FND_API.G_EXC_ERROR;
1616 END IF;
1617
1618 Validate_REQUEST_ID(
1619 p_init_msg_list => FND_API.G_FALSE,
1620 p_validation_mode => p_validation_mode,
1621 p_REQUEST_ID => P_LINE_RLTSHIP_Rec.REQUEST_ID,
1622 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1623 x_return_status => x_return_status,
1624 x_msg_count => x_msg_count,
1625 x_msg_data => x_msg_data);
1626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1627 raise FND_API.G_EXC_ERROR;
1628 END IF;
1629
1630 Validate_PROG_APPL_ID(
1631 p_init_msg_list => FND_API.G_FALSE,
1632 p_validation_mode => p_validation_mode,
1633 p_PROGRAM_APPLICATION_ID => P_LINE_RLTSHIP_Rec.PROGRAM_APPLICATION_ID,
1634 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1635 x_return_status => x_return_status,
1636 x_msg_count => x_msg_count,
1637 x_msg_data => x_msg_data);
1638 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1639 raise FND_API.G_EXC_ERROR;
1640 END IF;
1641
1642 Validate_PROGRAM_ID(
1643 p_init_msg_list => FND_API.G_FALSE,
1644 p_validation_mode => p_validation_mode,
1645 p_PROGRAM_ID => P_LINE_RLTSHIP_Rec.PROGRAM_ID,
1646 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1650 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1647 x_return_status => x_return_status,
1648 x_msg_count => x_msg_count,
1649 x_msg_data => x_msg_data);
1651 raise FND_API.G_EXC_ERROR;
1652 END IF;
1653
1654 Validate_PROGRAM_UPDATE_DATE(
1655 p_init_msg_list => FND_API.G_FALSE,
1656 p_validation_mode => p_validation_mode,
1657 p_PROGRAM_UPDATE_DATE => P_LINE_RLTSHIP_Rec.PROGRAM_UPDATE_DATE,
1658 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1659 x_return_status => x_return_status,
1660 x_msg_count => x_msg_count,
1661 x_msg_data => x_msg_data);
1662 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1663 raise FND_API.G_EXC_ERROR;
1664 END IF;
1665
1666 Validate_QUOTE_LINE_ID(
1667 p_init_msg_list => FND_API.G_FALSE,
1668 p_validation_mode => p_validation_mode,
1669 p_QUOTE_LINE_ID => P_LINE_RLTSHIP_Rec.QUOTE_LINE_ID,
1670 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1671 x_return_status => x_return_status,
1672 x_msg_count => x_msg_count,
1673 x_msg_data => x_msg_data);
1674 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1675 raise FND_API.G_EXC_ERROR;
1676 END IF;
1677
1678 Validate_RELATED_QUOTE_LINE_ID(
1679 p_init_msg_list => FND_API.G_FALSE,
1680 p_validation_mode => p_validation_mode,
1681 p_RELATED_QUOTE_LINE_ID => P_LINE_RLTSHIP_Rec.RELATED_QUOTE_LINE_ID,
1682 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1683 x_return_status => x_return_status,
1684 x_msg_count => x_msg_count,
1685 x_msg_data => x_msg_data);
1686 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1687 raise FND_API.G_EXC_ERROR;
1688 END IF;
1689
1690 Validate_RECIPROCAL_FLAG(
1691 p_init_msg_list => FND_API.G_FALSE,
1692 p_validation_mode => p_validation_mode,
1693 p_RECIPROCAL_FLAG => P_LINE_RLTSHIP_Rec.RECIPROCAL_FLAG,
1694 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1695 x_return_status => x_return_status,
1696 x_msg_count => x_msg_count,
1697 x_msg_data => x_msg_data);
1698 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1699 raise FND_API.G_EXC_ERROR;
1700 END IF;
1701
1702 Validate_RLTSHIP_TYPE_CODE(
1703 p_init_msg_list => FND_API.G_FALSE,
1704 p_validation_mode => p_validation_mode,
1705 p_RELATIONSHIP_TYPE_CODE => P_LINE_RLTSHIP_Rec.RELATIONSHIP_TYPE_CODE,
1706 -- Hint: You may add x_item_property_rec as one of your OUT NOCOPY /* file.sql.39 change */ parameter if you'd like to pass back item property.
1707 x_return_status => x_return_status,
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data);
1710 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1711 raise FND_API.G_EXC_ERROR;
1712 END IF;
1713
1714 END IF;
1715
1716 IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_RECORD) THEN
1717 -- Hint: Inter-field level validation can be added here
1718 -- invoke record level validation procedures
1719 Validate_LINE_RLTSHIP_Rec(
1720 p_init_msg_list => FND_API.G_FALSE,
1721 p_validation_mode => p_validation_mode,
1722 P_LINE_RLTSHIP_Rec => P_LINE_RLTSHIP_Rec,
1723 x_return_status => x_return_status,
1724 x_msg_count => x_msg_count,
1725 x_msg_data => x_msg_data);
1726
1727 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1728 raise FND_API.G_EXC_ERROR;
1729 END IF;
1730 END IF;
1731
1732 IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_INTER_RECORD) THEN
1733 -- invoke inter-record level validation procedures
1734 NULL;
1735 END IF;
1736
1737 IF (p_validation_level >= ASO_UTILITY_PVT.G_VALID_LEVEL_INTER_ENTITY) THEN
1738 -- invoke inter-entity level validation procedures
1739 NULL;
1740 END IF;
1741
1742
1743 -- Debug Message
1744 --ASO_UTILITY_PVT.Print(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
1745
1746 END Validate_line_rltship;
1747
1748 End ASO_LINE_RLTSHIP_PVT;