[Home] [Help]
PACKAGE BODY: APPS.CSP_SUPERSEDE_ITEMS_PKG
Source
1 PACKAGE BODY CSP_SUPERSEDE_ITEMS_PKG as
2 /* $Header: csptsupb.pls 115.2 2002/11/26 07:05:03 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_SUPERSEDE_ITEMS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SUPERSEDE_ITEMS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptsupb.pls';
13
14 PROCEDURE Insert_Row(
15 px_SUPERSEDE_ID IN OUT NOCOPY NUMBER
16 ,p_CREATED_BY NUMBER
17 ,p_CREATION_DATE DATE
18 ,p_LAST_UPDATED_BY NUMBER
19 ,p_LAST_UPDATE_DATE DATE
20 ,p_LAST_UPDATE_LOGIN NUMBER
21 ,p_INVENTORY_ITEM_ID NUMBER
22 ,p_ORGANIZATION_ID NUMBER
23 ,p_SUB_INVENTORY_CODE VARCHAR2
24 ,p_ITEM_SUPPLIED NUMBER
25 ,p_ATTRIBUTE_CATEGORY VARCHAR2
26 ,p_ATTRIBUTE1 VARCHAR2
27 ,p_ATTRIBUTE2 VARCHAR2
28 ,p_ATTRIBUTE3 VARCHAR2
29 ,p_ATTRIBUTE4 VARCHAR2
30 ,p_ATTRIBUTE5 VARCHAR2
31 ,p_ATTRIBUTE6 VARCHAR2
32 ,p_ATTRIBUTE7 VARCHAR2
33 ,p_ATTRIBUTE8 VARCHAR2
34 ,p_ATTRIBUTE9 VARCHAR2
35 ,p_ATTRIBUTE10 VARCHAR2
36 ,p_ATTRIBUTE11 VARCHAR2
37 ,p_ATTRIBUTE12 VARCHAR2
38 ,p_ATTRIBUTE13 VARCHAR2
39 ,p_ATTRIBUTE14 VARCHAR2
40 ,p_ATTRIBUTE15 VARCHAR2)
41
42 IS
43 CURSOR C2 IS SELECT CSP_SUPERSEDE_ITEMS_S1.nextval FROM sys.dual;
44 BEGIN
45 If (px_SUPERSEDE_ID IS NULL) OR (px_SUPERSEDE_ID = FND_API.G_MISS_NUM) then
46 OPEN C2;
47 FETCH C2 INTO px_SUPERSEDE_ID;
48 CLOSE C2;
49 End If;
50 INSERT INTO CSP_SUPERSEDE_ITEMS(
51 SUPERSEDE_ID
52 ,CREATED_BY
53 ,CREATION_DATE
54 ,LAST_UPDATED_BY
55 ,LAST_UPDATE_DATE
56 ,LAST_UPDATE_LOGIN
57 ,INVENTORY_ITEM_ID
58 ,ORGANIZATION_ID
59 ,SUB_INVENTORY_CODE
60 ,ITEM_SUPPLIED
61 ,ATTRIBUTE_CATEGORY
62 ,ATTRIBUTE1
63 ,ATTRIBUTE2
64 ,ATTRIBUTE3
65 ,ATTRIBUTE4
66 ,ATTRIBUTE5
67 ,ATTRIBUTE6
68 ,ATTRIBUTE7
69 ,ATTRIBUTE8
70 ,ATTRIBUTE9
71 ,ATTRIBUTE10
72 ,ATTRIBUTE11
73 ,ATTRIBUTE12
74 ,ATTRIBUTE13
75 ,ATTRIBUTE14
76 ,ATTRIBUTE15
77 ) VALUES (
78 px_SUPERSEDE_ID
79 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
80 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
81 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
82 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
83 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
84 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
85 ,decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID)
86 ,decode( p_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, NULL, p_SUB_INVENTORY_CODE)
87 ,decode( p_ITEM_SUPPLIED, FND_API.G_MISS_NUM, NULL, p_ITEM_SUPPLIED)
88 ,decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
89 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
90 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
91 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
92 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
93 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
94 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
95 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
96 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
97 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
98 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
99 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
100 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
101 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
102 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
103 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15));
104 End Insert_Row;
105
106 PROCEDURE Update_Row(
107 p_SUPERSEDE_ID NUMBER
108 ,p_CREATED_BY NUMBER
109 ,p_CREATION_DATE DATE
110 ,p_LAST_UPDATED_BY NUMBER
111 ,p_LAST_UPDATE_DATE DATE
112 ,p_LAST_UPDATE_LOGIN NUMBER
113 ,p_INVENTORY_ITEM_ID NUMBER
114 ,p_ORGANIZATION_ID NUMBER
115 ,p_SUB_INVENTORY_CODE VARCHAR2
116 ,p_ITEM_SUPPLIED NUMBER
117 ,p_ATTRIBUTE_CATEGORY VARCHAR2
118 ,p_ATTRIBUTE1 VARCHAR2
119 ,p_ATTRIBUTE2 VARCHAR2
120 ,p_ATTRIBUTE3 VARCHAR2
121 ,p_ATTRIBUTE4 VARCHAR2
122 ,p_ATTRIBUTE5 VARCHAR2
123 ,p_ATTRIBUTE6 VARCHAR2
124 ,p_ATTRIBUTE7 VARCHAR2
125 ,p_ATTRIBUTE8 VARCHAR2
126 ,p_ATTRIBUTE9 VARCHAR2
127 ,p_ATTRIBUTE10 VARCHAR2
128 ,p_ATTRIBUTE11 VARCHAR2
129 ,p_ATTRIBUTE12 VARCHAR2
130 ,p_ATTRIBUTE13 VARCHAR2
131 ,p_ATTRIBUTE14 VARCHAR2
132 ,p_ATTRIBUTE15 VARCHAR2)
133
134 IS
135 BEGIN
136 Update CSP_SUPERSEDE_ITEMS
137 SET
138 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
139 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
140 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
141 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
142 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
143 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
144 ,ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID)
145 ,SUB_INVENTORY_CODE = decode( p_SUB_INVENTORY_CODE, FND_API.G_MISS_CHAR, SUB_INVENTORY_CODE, p_SUB_INVENTORY_CODE)
146 ,ITEM_SUPPLIED = decode( p_ITEM_SUPPLIED, FND_API.G_MISS_NUM, ITEM_SUPPLIED, p_ITEM_SUPPLIED)
147 ,ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY)
148 ,ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1)
149 ,ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2)
150 ,ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3)
151 ,ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4)
152 ,ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5)
153 ,ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6)
154 ,ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7)
155 ,ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8)
156 ,ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9)
157 ,ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10)
158 ,ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11)
159 ,ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12)
160 ,ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13)
161 ,ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14)
162 ,ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15)
163 where SUPERSEDE_ID = p_SUPERSEDE_ID;
164
165 If (SQL%NOTFOUND) then
166 RAISE NO_DATA_FOUND;
167 End If;
168 END Update_Row;
169
170 PROCEDURE Delete_Row(
171 p_SUPERSEDE_ID NUMBER)
172 IS
173 BEGIN
174 DELETE FROM CSP_SUPERSEDE_ITEMS
175 WHERE SUPERSEDE_ID = p_SUPERSEDE_ID;
176 If (SQL%NOTFOUND) then
177 RAISE NO_DATA_FOUND;
178 End If;
179 END Delete_Row;
180
181 PROCEDURE Lock_Row(
182 p_SUPERSEDE_ID NUMBER
183 ,p_CREATED_BY NUMBER
184 ,p_CREATION_DATE DATE
185 ,p_LAST_UPDATED_BY NUMBER
186 ,p_LAST_UPDATE_DATE DATE
187 ,p_LAST_UPDATE_LOGIN NUMBER
188 ,p_INVENTORY_ITEM_ID NUMBER
189 ,p_ORGANIZATION_ID NUMBER
190 ,p_SUB_INVENTORY_CODE VARCHAR2
191 ,p_ITEM_SUPPLIED NUMBER
192 ,p_ATTRIBUTE_CATEGORY VARCHAR2
193 ,p_ATTRIBUTE1 VARCHAR2
194 ,p_ATTRIBUTE2 VARCHAR2
195 ,p_ATTRIBUTE3 VARCHAR2
196 ,p_ATTRIBUTE4 VARCHAR2
197 ,p_ATTRIBUTE5 VARCHAR2
198 ,p_ATTRIBUTE6 VARCHAR2
199 ,p_ATTRIBUTE7 VARCHAR2
200 ,p_ATTRIBUTE8 VARCHAR2
201 ,p_ATTRIBUTE9 VARCHAR2
202 ,p_ATTRIBUTE10 VARCHAR2
203 ,p_ATTRIBUTE11 VARCHAR2
204 ,p_ATTRIBUTE12 VARCHAR2
205 ,p_ATTRIBUTE13 VARCHAR2
206 ,p_ATTRIBUTE14 VARCHAR2
207 ,p_ATTRIBUTE15 VARCHAR2)
208
209 IS
210 CURSOR C IS
211 SELECT *
212 FROM CSP_SUPERSEDE_ITEMS
213 WHERE SUPERSEDE_ID = p_SUPERSEDE_ID
214 FOR UPDATE of SUPERSEDE_ID NOWAIT;
215 Recinfo C%ROWTYPE;
216 BEGIN
217 OPEN C;
218 FETCH C INTO Recinfo;
219 If (C%NOTFOUND) then
220 CLOSE C;
221 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 End If;
224 CLOSE C;
225 if (
226 ( Recinfo.SUPERSEDE_ID = p_SUPERSEDE_ID)
227 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
228 OR ( ( Recinfo.CREATED_BY IS NULL )
229 AND ( p_CREATED_BY IS NULL )))
230 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
231 OR ( ( Recinfo.CREATION_DATE IS NULL )
232 AND ( p_CREATION_DATE IS NULL )))
233 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
234 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
235 AND ( p_LAST_UPDATED_BY IS NULL )))
236 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
237 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
238 AND ( p_LAST_UPDATE_DATE IS NULL )))
239 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
240 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
241 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
242 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
243 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
244 AND ( p_INVENTORY_ITEM_ID IS NULL )))
245 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
246 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
247 AND ( p_ORGANIZATION_ID IS NULL )))
248 AND ( ( Recinfo.SUB_INVENTORY_CODE = p_SUB_INVENTORY_CODE)
249 OR ( ( Recinfo.SUB_INVENTORY_CODE IS NULL )
250 AND ( p_SUB_INVENTORY_CODE IS NULL )))
251 AND ( ( Recinfo.ITEM_SUPPLIED = p_ITEM_SUPPLIED)
252 OR ( ( Recinfo.ITEM_SUPPLIED IS NULL )
253 AND ( p_ITEM_SUPPLIED IS NULL )))
254 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
255 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
256 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
257 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
258 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
259 AND ( p_ATTRIBUTE1 IS NULL )))
260 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
261 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
262 AND ( p_ATTRIBUTE2 IS NULL )))
263 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
264 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
265 AND ( p_ATTRIBUTE3 IS NULL )))
266 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
267 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
268 AND ( p_ATTRIBUTE4 IS NULL )))
269 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
270 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
271 AND ( p_ATTRIBUTE5 IS NULL )))
272 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
273 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
274 AND ( p_ATTRIBUTE6 IS NULL )))
275 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
276 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
277 AND ( p_ATTRIBUTE7 IS NULL )))
278 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
279 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
280 AND ( p_ATTRIBUTE8 IS NULL )))
281 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
282 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
283 AND ( p_ATTRIBUTE9 IS NULL )))
284 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
285 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
286 AND ( p_ATTRIBUTE10 IS NULL )))
287 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
288 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
289 AND ( p_ATTRIBUTE11 IS NULL )))
290 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
291 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
292 AND ( p_ATTRIBUTE12 IS NULL )))
293 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
294 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
295 AND ( p_ATTRIBUTE13 IS NULL )))
296 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
297 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
298 AND ( p_ATTRIBUTE14 IS NULL )))
299 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
300 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
301 AND ( p_ATTRIBUTE15 IS NULL )))
302 ) then
303 return;
304 else
305 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
306 APP_EXCEPTION.RAISE_EXCEPTION;
307 End If;
308 END Lock_Row;
309
310 End CSP_SUPERSEDE_ITEMS_PKG;