[Home] [Help]
PACKAGE BODY: APPS.ASO_QUOTE_LINE_ATTRIBS_EXT_PKG
Source
1 PACKAGE BODY ASO_QUOTE_LINE_ATTRIBS_EXT_PKG as
2 /* $Header: asotlatb.pls 120.1 2005/06/29 12:39:03 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_QUOTE_LINE_ATTRIBS_EXT_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_QUOTE_LINE_ATTRIBS_EXT_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asotlatb.pls';
13
14 PROCEDURE Insert_Row(
15 px_LINE_ATTRIBUTE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
16 p_CREATION_DATE DATE,
17 p_CREATED_BY NUMBER,
18 p_LAST_UPDATE_DATE DATE,
19 p_LAST_UPDATED_BY NUMBER,
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_APPLICATION_ID NUMBER,
26 p_STATUS VARCHAR2,
27 p_QUOTE_LINE_ID NUMBER,
28 p_ATTRIBUTE_TYPE_CODE VARCHAR2,
29 p_QUOTE_HEADER_ID NUMBER,
30 p_QUOTE_SHIPMENT_ID NUMBER,
31 p_NAME VARCHAR2,
32 p_VALUE VARCHAR2,
33 p_VALUE_TYPE VARCHAR2,
34 p_START_DATE_ACTIVE DATE,
35 p_END_DATE_ACTIVE DATE,
36 p_OBJECT_VERSION_NUMBER NUMBER
37 )
38
39 IS
40 CURSOR C2 IS SELECT ASO_QUOTE_LINE_ATTRIBS_EXT_S.nextval FROM sys.dual;
41 BEGIN
42 If (px_LINE_ATTRIBUTE_ID IS NULL) OR (px_LINE_ATTRIBUTE_ID = FND_API.G_MISS_NUM) then
43 OPEN C2;
44 FETCH C2 INTO px_LINE_ATTRIBUTE_ID;
45 CLOSE C2;
46 End If;
47 INSERT INTO ASO_QUOTE_LINE_ATTRIBS_EXT(
48 LINE_ATTRIBUTE_ID,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN,
54 REQUEST_ID,
55 PROGRAM_APPLICATION_ID,
56 PROGRAM_ID,
57 PROGRAM_UPDATE_DATE,
58 APPLICATION_ID ,
59 STATUS,
60 QUOTE_LINE_ID,
61 ATTRIBUTE_TYPE_CODE,
62 QUOTE_HEADER_ID,
63 QUOTE_SHIPMENT_ID,
64 NAME,
65 VALUE,
66 VALUE_TYPE,
67 START_DATE_ACTIVE,
68 END_DATE_ACTIVE,
69 OBJECT_VERSION_NUMBER
70 ) VALUES (
71 px_LINE_ATTRIBUTE_ID,
72 ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
73 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
74 ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
75 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
76 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
77 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
78 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
79 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
80 ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
81 decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_APPLICATION_ID),
82 decode(p_STATUS, FND_API.G_MISS_CHAR, NULL, p_STATUS),
83 decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_LINE_ID),
84 decode( p_ATTRIBUTE_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_TYPE_CODE),
85 decode( p_QUOTE_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_HEADER_ID),
86 decode( p_QUOTE_SHIPMENT_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_SHIPMENT_ID),
87 decode( p_NAME, FND_API.G_MISS_CHAR, NULL, p_NAME),
88 decode( p_VALUE, FND_API.G_MISS_CHAR, NULL, p_VALUE),
89 decode(p_VALUE_TYPE, FND_API.G_MISS_CHAR, NULL, p_VALUE_TYPE),
90 ASO_UTILITY_PVT.decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, p_START_DATE_ACTIVE),
91 ASO_UTILITY_PVT.decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, p_END_DATE_ACTIVE),
92 decode ( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,1,NULL,1, p_OBJECT_VERSION_NUMBER)
93 );
94 End Insert_Row;
95
96 PROCEDURE Update_Row(
97 p_LINE_ATTRIBUTE_ID NUMBER,
98 p_CREATION_DATE DATE,
99 p_CREATED_BY NUMBER,
100 p_LAST_UPDATE_DATE DATE,
101 p_LAST_UPDATED_BY NUMBER,
102 p_LAST_UPDATE_LOGIN NUMBER,
103 p_REQUEST_ID NUMBER,
104 p_PROGRAM_APPLICATION_ID NUMBER,
105 p_PROGRAM_ID NUMBER,
106 p_PROGRAM_UPDATE_DATE DATE,
107 p_APPLICATION_ID NUMBER,
108 p_STATUS VARCHAR2,
109 p_QUOTE_LINE_ID NUMBER,
110 p_ATTRIBUTE_TYPE_CODE VARCHAR2,
111 p_QUOTE_HEADER_ID NUMBER,
112 p_QUOTE_SHIPMENT_ID NUMBER,
113 p_NAME VARCHAR2,
114 p_VALUE VARCHAR2,
115 p_VALUE_TYPE VARCHAR2,
116 p_START_DATE_ACTIVE DATE,
117 p_END_DATE_ACTIVE DATE,
118 p_OBJECT_VERSION_NUMBER NUMBER
119 )
120
121 IS
122 BEGIN
123 Update ASO_QUOTE_LINE_ATTRIBS_EXT
124 SET
125 /* CREATION_DATE = ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
126 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),*/
127 LAST_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
128 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
129 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
130 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
131 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
132 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
133 PROGRAM_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
134 APPLICATION_ID = decode(p_APPLICATION_ID, FND_API.G_MISS_NUM, APPLICATION_ID, p_APPLICATION_ID),
135 STATUS = decode(p_STATUS, FND_API.G_MISS_CHAR, STATUS, p_STATUS),
136
137 QUOTE_LINE_ID = decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, QUOTE_LINE_ID, p_QUOTE_LINE_ID),
138 ATTRIBUTE_TYPE_CODE = decode( p_ATTRIBUTE_TYPE_CODE, FND_API.G_MISS_CHAR, ATTRIBUTE_TYPE_CODE, p_ATTRIBUTE_TYPE_CODE),
139 QUOTE_HEADER_ID = decode( p_QUOTE_HEADER_ID, FND_API.G_MISS_NUM, QUOTE_HEADER_ID, p_QUOTE_HEADER_ID),
140 QUOTE_SHIPMENT_ID = decode( p_QUOTE_SHIPMENT_ID, FND_API.G_MISS_NUM, QUOTE_SHIPMENT_ID, p_QUOTE_SHIPMENT_ID),
141 NAME = decode( p_NAME, FND_API.G_MISS_CHAR, NAME, p_NAME),
142 VALUE = decode( p_VALUE, FND_API.G_MISS_CHAR, VALUE, p_VALUE),
143 VALUE_TYPE = decode(p_VALUE_TYPE, FND_API.G_MISS_CHAR, VALUE_TYPE, p_VALUE_TYPE),
144 START_DATE_ACTIVE = ASO_UTILITY_PVT.decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, START_DATE_ACTIVE, p_START_DATE_ACTIVE),
145 END_DATE_ACTIVE = ASO_UTILITY_PVT.decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, END_DATE_ACTIVE, p_END_DATE_ACTIVE),
146 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)
147 where LINE_ATTRIBUTE_ID = p_LINE_ATTRIBUTE_ID;
148
149 If (SQL%NOTFOUND) then
150 RAISE NO_DATA_FOUND;
151 End If;
152 END Update_Row;
153
154 PROCEDURE Delete_Row(
155 p_LINE_ATTRIB_ID NUMBER)
156 IS
157 BEGIN
158 DELETE FROM ASO_QUOTE_LINE_ATTRIBS_EXT
159 WHERE LINE_ATTRIBUTE_ID = p_LINE_ATTRIB_ID;
160 If (SQL%NOTFOUND) then
161 RAISE NO_DATA_FOUND;
162 End If;
163 END Delete_Row;
164
165
166
167 PROCEDURE Delete_Row(
168 p_QUOTE_LINE_ID NUMBER)
169 IS
170 BEGIN
171 DELETE FROM ASO_QUOTE_LINE_ATTRIBS_EXT
172 WHERE QUOTE_LINE_ID = p_QUOTE_LINE_ID;
173 If (SQL%NOTFOUND) then
174 RAISE NO_DATA_FOUND;
175 End If;
176 END Delete_Row;
177
178
179 PROCEDURE Lock_Row(
180 --p_OBJECT_VERSION_NUMBER NUMBER,
181 p_LINE_ATTRIBUTE_ID NUMBER,
182 p_CREATION_DATE DATE,
183 p_CREATED_BY NUMBER,
184 p_LAST_UPDATE_DATE DATE,
185 p_LAST_UPDATED_BY NUMBER,
186 p_LAST_UPDATE_LOGIN NUMBER,
187 p_REQUEST_ID NUMBER,
188 p_PROGRAM_APPLICATION_ID NUMBER,
189 p_PROGRAM_ID NUMBER,
190 p_PROGRAM_UPDATE_DATE DATE,
191 p_APPLICATION_ID NUMBER,
192 p_STATUS VARCHAR2,
193 p_QUOTE_LINE_ID NUMBER,
194 p_ATTRIBUTE_TYPE_CODE VARCHAR2,
195 p_QUOTE_HEADER_ID NUMBER,
196 p_QUOTE_SHIPMENT_ID NUMBER,
197 p_NAME VARCHAR2,
198 p_VALUE VARCHAR2,
199 p_VALUE_TYPE VARCHAR2,
200 p_START_DATE_ACTIVE DATE,
201 p_END_DATE_ACTIVE DATE)
202
203 IS
204 CURSOR C IS
205 SELECT LINE_ATTRIBUTE_ID,
206 --OBJECT_VERSION_NUMBER,
207 CREATION_DATE,
208 CREATED_BY,
209 LAST_UPDATE_DATE,
210 LAST_UPDATED_BY,
211 LAST_UPDATE_LOGIN,
212 REQUEST_ID,
213 PROGRAM_APPLICATION_ID,
214 PROGRAM_ID,
215 PROGRAM_UPDATE_DATE,
216 APPLICATION_ID,
217 STATUS,
218 QUOTE_LINE_ID,
219 ATTRIBUTE_TYPE_CODE,
220 QUOTE_HEADER_ID ,
221 QUOTE_SHIPMENT_ID ,
222 NAME,
223 VALUE,
224 VALUE_TYPE,
225 START_DATE_ACTIVE,
226 END_DATE_ACTIVE
227 FROM ASO_QUOTE_LINE_ATTRIBS_EXT
228 WHERE LINE_ATTRIBUTE_ID = p_LINE_ATTRIBUTE_ID
229 FOR UPDATE of LINE_ATTRIBUTE_ID NOWAIT;
230 Recinfo C%ROWTYPE;
231 BEGIN
232 OPEN C;
233 FETCH C INTO Recinfo;
234 If (C%NOTFOUND) then
235 CLOSE C;
236 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 End If;
239 CLOSE C;
240 if (
241 /*
242 ( Recinfo.LINE_ATTRIBUTE_ID = p_LINE_ATTRIBUTE_ID)
243 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
244 OR ( ( Recinfo.CREATION_DATE IS NULL )
245 AND ( p_CREATION_DATE IS NULL )))
246 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
247 OR ( ( Recinfo.CREATED_BY IS NULL )
248 AND ( p_CREATED_BY IS NULL )))
249 AND
250 */
251 ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
252 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
253 AND ( p_LAST_UPDATE_DATE IS NULL )))
254 /*
255 AND
256 ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
257 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
258 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
259 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
260 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
261 AND ( p_LAST_UPDATED_BY IS NULL )))
262 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
263 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
264 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
265 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
266 OR ( ( Recinfo.REQUEST_ID IS NULL )
267 AND ( p_REQUEST_ID IS NULL )))
268 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
269 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
270 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
271 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
272 OR ( ( Recinfo.PROGRAM_ID IS NULL )
273 AND ( p_PROGRAM_ID IS NULL )))
274 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
275 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
276 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
277 AND ( ( Recinfo.APPLICATION_ID = p_APPLICATION_ID)
278 OR ( ( Recinfo.APPLICATION_ID IS NULL )
279 AND ( p_APPLICATION_ID IS NULL )))
280 AND ( ( Recinfo.STATUS = p_STATUS)
281 OR ( ( Recinfo.STATUS IS NULL )
282 AND ( p_STATUS IS NULL )))
283
284 AND ( ( Recinfo.QUOTE_LINE_ID = p_QUOTE_LINE_ID)
285 OR ( ( Recinfo.QUOTE_LINE_ID IS NULL )
286 AND ( p_QUOTE_LINE_ID IS NULL )))
287 AND ( ( Recinfo.ATTRIBUTE_TYPE_CODE = p_ATTRIBUTE_TYPE_CODE)
288 OR ( ( Recinfo.ATTRIBUTE_TYPE_CODE IS NULL )
289 AND ( p_ATTRIBUTE_TYPE_CODE IS NULL )))
290 AND ( ( Recinfo.QUOTE_HEADER_ID = p_QUOTE_HEADER_ID)
291 OR ( ( Recinfo.QUOTE_HEADER_ID IS NULL )
292 AND ( p_QUOTE_HEADER_ID IS NULL )))
293 AND ( ( Recinfo.QUOTE_SHIPMENT_ID = p_QUOTE_SHIPMENT_ID)
294 OR ( ( Recinfo.QUOTE_SHIPMENT_ID IS NULL )
295 AND ( p_QUOTE_SHIPMENT_ID IS NULL )))
296 AND ( ( Recinfo.NAME = p_NAME)
297 OR ( ( Recinfo.NAME IS NULL )
298 AND ( p_NAME IS NULL )))
299 AND ( ( Recinfo.VALUE = p_VALUE)
300 OR ( ( Recinfo.VALUE IS NULL )
301 AND ( p_VALUE IS NULL )))
302 AND ( ( Recinfo.VALUE_TYPE = p_VALUE_TYPE)
303 OR ( ( Recinfo.VALUE_TYPE IS NULL )
304 AND ( p_VALUE_TYPE IS NULL )))
305 AND ( ( Recinfo.START_DATE_ACTIVE = p_START_DATE_ACTIVE)
306 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
307 AND ( p_START_DATE_ACTIVE IS NULL )))
308 AND ( ( Recinfo.END_DATE_ACTIVE = p_END_DATE_ACTIVE)
309 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
310 AND ( p_END_DATE_ACTIVE IS NULL )))
311 */
312 ) then
313 return;
314 else
315 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
316 APP_EXCEPTION.RAISE_EXCEPTION;
317 End If;
318 END Lock_Row;
319
320 End ASO_QUOTE_LINE_ATTRIBS_EXT_PKG;