[Home] [Help]
PACKAGE BODY: APPS.ASO_LINE_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY ASO_LINE_RELATIONSHIPS_PKG as
2 /* $Header: asotlinb.pls 120.1 2005/06/29 12:39:16 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_LINE_RELATIONSHIPS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_LINE_RELATIONSHIPS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asotlinb.pls';
13
14 PROCEDURE Insert_Row(
15 px_LINE_RELATIONSHIP_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
16 p_CREATION_DATE DATE,
17 p_CREATED_BY NUMBER,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_REQUEST_ID NUMBER,
22 p_PROGRAM_APPLICATION_ID NUMBER,
23 p_PROGRAM_ID NUMBER,
24 p_PROGRAM_UPDATE_DATE DATE,
25 p_QUOTE_LINE_ID NUMBER,
26 p_RELATED_QUOTE_LINE_ID NUMBER,
27 p_RECIPROCAL_FLAG VARCHAR2,
28 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
29 p_OBJECT_VERSION_NUMBER NUMBER)
30
31 IS
32 CURSOR C2 IS SELECT ASO_LINE_RELATIONSHIPS_S.nextval FROM sys.dual;
33 BEGIN
34 If (px_LINE_RELATIONSHIP_ID IS NULL) OR (px_LINE_RELATIONSHIP_ID = FND_API.G_MISS_NUM) then
35 OPEN C2;
36 FETCH C2 INTO px_LINE_RELATIONSHIP_ID;
37 CLOSE C2;
38 End If;
39 INSERT INTO ASO_LINE_RELATIONSHIPS(
40 LINE_RELATIONSHIP_ID,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 REQUEST_ID,
47 PROGRAM_APPLICATION_ID,
48 PROGRAM_ID,
49 PROGRAM_UPDATE_DATE,
50 QUOTE_LINE_ID,
51 RELATED_QUOTE_LINE_ID,
52 RECIPROCAL_FLAG,
53 RELATIONSHIP_TYPE_CODE,
54 OBJECT_VERSION_NUMBER
55 ) VALUES (
56 px_LINE_RELATIONSHIP_ID,
57 ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
58 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
59 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
60 ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
61 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
62 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
63 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
64 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
65 ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
66 decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_LINE_ID),
67 decode( p_RELATED_QUOTE_LINE_ID, FND_API.G_MISS_NUM, NULL, p_RELATED_QUOTE_LINE_ID),
68 decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, NULL, p_RECIPROCAL_FLAG),
69 decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_RELATIONSHIP_TYPE_CODE),
70 decode ( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,1,NULL,1, p_OBJECT_VERSION_NUMBER)
71 );
72 End Insert_Row;
73
74 PROCEDURE Update_Row(
75 p_LINE_RELATIONSHIP_ID NUMBER,
76 p_CREATION_DATE DATE,
77 p_CREATED_BY NUMBER,
78 p_LAST_UPDATED_BY NUMBER,
79 p_LAST_UPDATE_DATE DATE,
80 p_LAST_UPDATE_LOGIN NUMBER,
81 p_REQUEST_ID NUMBER,
82 p_PROGRAM_APPLICATION_ID NUMBER,
83 p_PROGRAM_ID NUMBER,
84 p_PROGRAM_UPDATE_DATE DATE,
85 p_QUOTE_LINE_ID NUMBER,
86 p_RELATED_QUOTE_LINE_ID NUMBER,
87 p_RECIPROCAL_FLAG VARCHAR2,
88 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
89 p_OBJECT_VERSION_NUMBER NUMBER)
90
91 IS
92 BEGIN
93 Update ASO_LINE_RELATIONSHIPS
94 SET
95 CREATION_DATE = ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
96 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
97 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
98 LAST_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
99 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
100 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
101 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
102 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
103 PROGRAM_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
104 QUOTE_LINE_ID = decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, QUOTE_LINE_ID, p_QUOTE_LINE_ID),
105 RELATED_QUOTE_LINE_ID = decode( p_RELATED_QUOTE_LINE_ID, FND_API.G_MISS_NUM, RELATED_QUOTE_LINE_ID, p_RELATED_QUOTE_LINE_ID),
106 RECIPROCAL_FLAG = decode( p_RECIPROCAL_FLAG, FND_API.G_MISS_CHAR, RECIPROCAL_FLAG, p_RECIPROCAL_FLAG),
107 RELATIONSHIP_TYPE_CODE = decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, RELATIONSHIP_TYPE_CODE, p_RELATIONSHIP_TYPE_CODE),
108 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, nvl(OBJECT_VERSION_NUMBER,0)+1, nvl(p_OBJECT_VERSION_NUMBER, nvl(OBJECT_VERSION_NUMBER,0))+1)
109 where LINE_RELATIONSHIP_ID = p_LINE_RELATIONSHIP_ID;
110
111 If (SQL%NOTFOUND) then
112 RAISE NO_DATA_FOUND;
113 End If;
114 END Update_Row;
115
116 PROCEDURE Delete_Row(
117 p_LINE_RELATIONSHIP_ID NUMBER)
118 IS
119 BEGIN
120 DELETE FROM ASO_LINE_RELATIONSHIPS
121 WHERE LINE_RELATIONSHIP_ID = p_LINE_RELATIONSHIP_ID;
122 If (SQL%NOTFOUND) then
123 RAISE NO_DATA_FOUND;
124 End If;
125 END Delete_Row;
126
127 PROCEDURE Lock_Row(
128 --p_OBJECT_VERSION_NUMBER NUMBER,
129 p_LINE_RELATIONSHIP_ID NUMBER,
130 p_CREATION_DATE DATE,
131 p_CREATED_BY NUMBER,
132 p_LAST_UPDATED_BY NUMBER,
133 p_LAST_UPDATE_DATE DATE,
134 p_LAST_UPDATE_LOGIN NUMBER,
135 p_REQUEST_ID NUMBER,
136 p_PROGRAM_APPLICATION_ID NUMBER,
137 p_PROGRAM_ID NUMBER,
138 p_PROGRAM_UPDATE_DATE DATE,
139 p_QUOTE_LINE_ID NUMBER,
140 p_RELATED_QUOTE_LINE_ID NUMBER,
141 p_RECIPROCAL_FLAG VARCHAR2,
142 p_RELATIONSHIP_TYPE_CODE VARCHAR2)
143
144 IS
145 CURSOR C IS
146 SELECT LINE_RELATIONSHIP_ID,
147 --OBJECT_VERSION_NUMBER,
148 CREATION_DATE,
149 CREATED_BY,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_DATE,
152 LAST_UPDATE_LOGIN,
153 REQUEST_ID,
154 PROGRAM_APPLICATION_ID,
155 PROGRAM_ID,
156 PROGRAM_UPDATE_DATE,
157 QUOTE_LINE_ID,
158 RELATED_QUOTE_LINE_ID,
159 RELATIONSHIP_TYPE_CODE,
160 RECIPROCAL_FLAG
161 FROM ASO_LINE_RELATIONSHIPS
162 WHERE LINE_RELATIONSHIP_ID = p_LINE_RELATIONSHIP_ID
163 FOR UPDATE of LINE_RELATIONSHIP_ID NOWAIT;
164 Recinfo C%ROWTYPE;
165 BEGIN
166 OPEN C;
167 FETCH C INTO Recinfo;
168 If (C%NOTFOUND) then
169 CLOSE C;
170 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 End If;
173 CLOSE C;
174 if (
175 /*
176 ( Recinfo.LINE_RELATIONSHIP_ID = p_LINE_RELATIONSHIP_ID)
177 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
178 OR ( ( Recinfo.CREATION_DATE IS NULL )
179 AND ( p_CREATION_DATE IS NULL )))
180 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
181 OR ( ( Recinfo.CREATED_BY IS NULL )
182 AND ( p_CREATED_BY IS NULL )))
183 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
184 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
185 AND ( p_LAST_UPDATED_BY IS NULL )))
186 AND
187 */
188 ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
189 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
190 AND ( p_LAST_UPDATE_DATE IS NULL )))
191 /*
192 AND
193 ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
194 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
195 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
196 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
197 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
198 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
199 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
200 OR ( ( Recinfo.REQUEST_ID IS NULL )
201 AND ( p_REQUEST_ID IS NULL )))
202 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
203 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
204 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
205 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
206 OR ( ( Recinfo.PROGRAM_ID IS NULL )
207 AND ( p_PROGRAM_ID IS NULL )))
208 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
209 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
210 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
211 AND ( ( Recinfo.QUOTE_LINE_ID = p_QUOTE_LINE_ID)
212 OR ( ( Recinfo.QUOTE_LINE_ID IS NULL )
213 AND ( p_QUOTE_LINE_ID IS NULL )))
214 AND ( ( Recinfo.RELATED_QUOTE_LINE_ID = p_RELATED_QUOTE_LINE_ID)
215 OR ( ( Recinfo.RELATED_QUOTE_LINE_ID IS NULL )
216 AND ( p_RELATED_QUOTE_LINE_ID IS NULL )))
217 AND ( ( Recinfo.RECIPROCAL_FLAG = p_RECIPROCAL_FLAG)
218 OR ( ( Recinfo.RECIPROCAL_FLAG IS NULL )
219 AND ( p_RECIPROCAL_FLAG IS NULL )))
220 AND ( ( Recinfo.RELATIONSHIP_TYPE_CODE = p_RELATIONSHIP_TYPE_CODE)
221 OR ( ( Recinfo.RELATIONSHIP_TYPE_CODE IS NULL )
222 AND ( p_RELATIONSHIP_TYPE_CODE IS NULL )))
223 */
224 ) then
225 return;
226 else
227 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
228 APP_EXCEPTION.RAISE_EXCEPTION;
229 End If;
230 END Lock_Row;
231
232 End ASO_LINE_RELATIONSHIPS_PKG;