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