[Home] [Help]
PACKAGE BODY: APPS.QUOTATION_APPROVALS_PKG
Source
1 PACKAGE BODY QUOTATION_APPROVALS_PKG as
2 /* $Header: POXAPQPB.pls 120.0 2005/06/01 21:02:18 appldev noship $ */
3
4
5 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Quotation_Approval_ID IN OUT NOCOPY NUMBER,
7 X_Approval_Type VARCHAR2,
8 X_Approval_Reason VARCHAR2,
9 X_Comments VARCHAR2,
10 X_Approver_ID NUMBER,
11 X_Start_Date_Active DATE,
12 X_End_Date_Active DATE,
13 X_Line_Location_ID NUMBER,
14 X_Last_Update_Date DATE,
15 X_Last_Updated_By NUMBER,
16 X_Last_Update_Login NUMBER,
17 X_Creation_Date DATE,
18 X_Created_By NUMBER,
19 X_Attribute_Category VARCHAR2,
20 X_Attribute1 VARCHAR2,
21 X_Attribute2 VARCHAR2,
22 X_Attribute3 VARCHAR2,
23 X_Attribute4 VARCHAR2,
24 X_Attribute5 VARCHAR2,
25 X_Attribute6 VARCHAR2,
26 X_Attribute7 VARCHAR2,
27 X_Attribute8 VARCHAR2,
28 X_Attribute9 VARCHAR2,
29 X_Attribute10 VARCHAR2,
30 X_Attribute11 VARCHAR2,
31 X_Attribute12 VARCHAR2,
32 X_Attribute13 VARCHAR2,
33 X_Attribute14 VARCHAR2,
34 X_Attribute15 VARCHAR2,
35 X_Request_ID NUMBER,
36 X_Program_Application_ID NUMBER,
37 X_Program_ID NUMBER,
38 X_Program_Update_Date DATE,
39 X_Org_ID NUMBER --<R12 MOAC> uncommented /* 2493519 */
40 )
41 IS
42
43 CURSOR C IS SELECT rowid FROM PO_QUOTATION_APPROVALS
44 WHERE quotation_approval_id = X_Quotation_Approval_ID;
45
46 CURSOR C2 IS SELECT po_quotation_approvals_s.nextval FROM sys.dual;
47
48 BEGIN
49 if (X_Quotation_Approval_Id is NULL) then
50 OPEN C2;
51 FETCH C2 INTO X_Quotation_Approval_ID;
52 CLOSE C2;
53 end if;
54
55
56 INSERT INTO PO_QUOTATION_APPROVALS(
57 quotation_approval_id,
58 approval_type,
59 approval_reason,
60 comments,
61 approver_id,
62 start_date_active,
63 end_date_active,
64 line_location_id,
65 last_update_date,
66 last_updated_by,
67 last_update_login,
68 creation_date,
69 created_by,
70 attribute_category,
71 attribute1,
72 attribute2,
73 attribute3,
74 attribute4,
75 attribute5,
76 attribute6,
77 attribute7,
78 attribute8,
79 attribute9,
80 attribute10,
81 attribute11,
82 attribute12,
83 attribute13,
84 attribute14,
85 attribute15,
86 request_id,
87 program_application_id,
88 program_id,
89 program_update_date,
90 org_id --<R12 MOAC> uncommented /* 2493519 */
91 ) VALUES (
92 X_Quotation_Approval_ID,
93 X_Approval_Type,
94 X_Approval_Reason,
95 X_Comments,
96 X_Approver_ID,
97 X_Start_Date_Active,
98 X_End_Date_Active,
99 X_Line_Location_ID,
100 X_Last_Update_Date,
101 X_Last_Updated_By,
102 X_Last_Update_Login,
103 X_Creation_Date,
104 X_Created_By,
105 X_Attribute_Category,
106 X_Attribute1,
107 X_Attribute2,
108 X_Attribute3,
109 X_Attribute4,
110 X_Attribute5,
111 X_Attribute6,
112 X_Attribute7,
113 X_Attribute8,
114 X_Attribute9,
115 X_Attribute10,
116 X_Attribute11,
117 X_Attribute12,
118 X_Attribute13,
119 X_Attribute14,
120 X_Attribute15,
121 X_Request_ID,
122 X_Program_Application_ID,
123 X_Program_ID,
124 X_Program_Update_Date,
125 X_Org_ID --<R12 MOAC> uncommented /* 2493519 */
126 );
127
128 OPEN C;
129 FETCH C INTO X_Rowid;
130 if (C%NOTFOUND) then
131 CLOSE C;
132 Raise NO_DATA_FOUND;
133 end if;
134 CLOSE C;
135 END Insert_Row;
136
137
138 PROCEDURE Lock_Row (X_Rowid VARCHAR2,
139 X_Quotation_Approval_ID NUMBER,
140 X_Approval_Type VARCHAR2,
141 X_Approval_Reason VARCHAR2,
142 X_Comments VARCHAR2,
143 X_Approver_ID NUMBER,
144 X_Start_Date_Active DATE,
145 X_End_Date_Active DATE,
146 X_Line_Location_ID NUMBER,
147 X_Last_Update_Date DATE,
148 X_Last_Updated_By NUMBER,
149 X_Last_Update_Login NUMBER,
150 X_Creation_Date DATE,
151 X_Created_By NUMBER,
152 X_Attribute_Category VARCHAR2,
153 X_Attribute1 VARCHAR2,
154 X_Attribute2 VARCHAR2,
155 X_Attribute3 VARCHAR2,
156 X_Attribute4 VARCHAR2,
157 X_Attribute5 VARCHAR2,
158 X_Attribute6 VARCHAR2,
159 X_Attribute7 VARCHAR2,
160 X_Attribute8 VARCHAR2,
161 X_Attribute9 VARCHAR2,
162 X_Attribute10 VARCHAR2,
163 X_Attribute11 VARCHAR2,
164 X_Attribute12 VARCHAR2,
165 X_Attribute13 VARCHAR2,
166 X_Attribute14 VARCHAR2,
167 X_Attribute15 VARCHAR2,
168 X_Request_ID NUMBER,
169 X_Program_Application_ID NUMBER,
170 X_Program_ID NUMBER,
171 X_Program_Update_Date DATE,
172 X_Org_ID NUMBER --<R12 MOAC> uncommented /* bug2493519*/
173 )
174
175 IS
176 CURSOR C IS
177 SELECT *
178 FROM PO_QUOTATION_APPROVALS
179 WHERE rowid = X_Rowid
180 FOR UPDATE of QUOTATION_APPROVAL_ID NOWAIT;
181 Recinfo C%ROWTYPE;
182 BEGIN
183 OPEN C;
184 FETCH C INTO Recinfo;
185 if (C%NOTFOUND) then
186 CLOSE C;
187 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
188 APP_EXCEPTION.Raise_Exception;
189 end if;
190 CLOSE C;
191 if (
192
193 (Recinfo.quotation_approval_id = X_Quotation_Approval_ID)
194 AND ( (Recinfo.approval_type = X_Approval_Type)
195 OR ( (Recinfo.approval_type IS NULL)
196 AND (X_Approval_Type IS NULL)))
197 AND ( (Recinfo.approval_reason = X_Approval_Reason)
198 OR ( (Recinfo.approval_reason IS NULL)
199 AND (X_Approval_Reason IS NULL)))
200 AND ( (Recinfo.comments = X_Comments)
201 OR ( (Recinfo.comments IS NULL)
202 AND (X_Comments IS NULL)))
203 AND ( (Recinfo.approver_id = X_Approver_ID)
204 OR ( (Recinfo.approver_id IS NULL)
205 AND (X_Approver_ID IS NULL)))
206 AND ( (Recinfo.start_date_active = X_Start_Date_Active)
207 OR ( (Recinfo.start_date_active IS NULL)
208 AND (X_Start_Date_Active IS NULL)))
209 AND ( (Recinfo.end_date_active = X_End_Date_Active)
210 OR ( (Recinfo.end_date_active IS NULL)
211 AND (X_End_Date_Active IS NULL)))
212 AND ( (Recinfo.line_location_id = X_Line_Location_ID)
213 OR ( (Recinfo.line_location_id IS NULL)
214 AND (X_Line_Location_ID IS NULL)))
215 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
216 OR ( (Recinfo.last_update_date IS NULL)
217 AND (X_Last_Update_Date IS NULL)))
218 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
219 OR ( (Recinfo.last_updated_by IS NULL)
220 AND (X_Last_Updated_By IS NULL)))
221 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
222 OR ( (Recinfo.last_update_login IS NULL)
223 AND (X_Last_Update_Login IS NULL)))
224 AND ( (Recinfo.attribute_category = X_Attribute_Category)
225 OR ( (Recinfo.attribute_category IS NULL)
226 AND (X_Attribute_Category IS NULL)))
227 AND ( (Recinfo.attribute1 = X_Attribute1)
228 OR ( (Recinfo.attribute1 IS NULL)
229 AND (X_Attribute1 IS NULL)))
230 AND ( (Recinfo.attribute2 = X_Attribute2)
231 OR ( (Recinfo.attribute2 IS NULL)
232 AND (X_Attribute2 IS NULL)))
233 AND ( (Recinfo.attribute3 = X_Attribute3)
234 OR ( (Recinfo.attribute3 IS NULL)
235 AND (X_Attribute3 IS NULL)))
236 AND ( (Recinfo.attribute4 = X_Attribute4)
237 OR ( (Recinfo.attribute4 IS NULL)
238 AND (X_Attribute4 IS NULL)))
239 AND ( (Recinfo.attribute5 = X_Attribute5)
240 OR ( (Recinfo.attribute5 IS NULL)
241 AND (X_Attribute5 IS NULL)))
242 AND ( (Recinfo.attribute6 = X_Attribute6)
243 OR ( (Recinfo.attribute6 IS NULL)
244 AND (X_Attribute6 IS NULL)))
245 AND ( (Recinfo.attribute7 = X_Attribute7)
246 OR ( (Recinfo.attribute7 IS NULL)
247 AND (X_Attribute7 IS NULL)))
248 AND ( (Recinfo.attribute8 = X_Attribute8)
249 OR ( (Recinfo.attribute8 IS NULL)
250 AND (X_Attribute8 IS NULL)))
251 AND ( (Recinfo.attribute9 = X_Attribute9)
252 OR ( (Recinfo.attribute9 IS NULL)
253 AND (X_Attribute9 IS NULL)))
254 AND ( (Recinfo.attribute10 = X_Attribute10)
255 OR ( (Recinfo.attribute10 IS NULL)
256 AND (X_Attribute10 IS NULL)))
257 AND ( (Recinfo.attribute11 = X_Attribute11)
258 OR ( (Recinfo.attribute11 IS NULL)
259 AND (X_Attribute11 IS NULL)))
260 AND ( (Recinfo.attribute12 = X_Attribute12)
261 OR ( (Recinfo.attribute12 IS NULL)
262 AND (X_Attribute12 IS NULL)))
263 AND ( (Recinfo.attribute13 = X_Attribute13)
264 OR ( (Recinfo.attribute13 IS NULL)
265 AND (X_Attribute13 IS NULL)))
266 AND ( (Recinfo.attribute14 = X_Attribute14)
267 OR ( (Recinfo.attribute14 IS NULL)
268 AND (X_Attribute14 IS NULL)))
269 AND ( (Recinfo.attribute15 = X_Attribute15)
270 OR ( (Recinfo.attribute15 IS NULL)
271 AND (X_Attribute15 IS NULL)))
272 AND (Recinfo.org_id = X_Org_ID) --<R12 MOAC> added
273 /* bug2493519*/
274 -- AND ( (Recinfo.org_id = X_Org_ID)
275 -- OR ( (Recinfo.org_id IS NULL)
276 -- AND (X_Org_ID IS NULL)))
277
278 ) then
279 return;
280 else
281 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
282 APP_EXCEPTION.RAISE_EXCEPTION;
283 end if;
284 END Lock_Row;
285
286
287
288 PROCEDURE Update_Row(X_Rowid VARCHAR2,
289 X_Quotation_Approval_ID NUMBER,
290 X_Approval_Type VARCHAR2,
291 X_Approval_Reason VARCHAR2,
292 X_Comments VARCHAR2,
293 X_Approver_ID NUMBER,
294 X_Start_Date_Active DATE,
295 X_End_Date_Active DATE,
296 X_Line_Location_ID NUMBER,
297 X_Last_Update_Date DATE,
298 X_Last_Updated_By NUMBER,
299 X_Last_Update_Login NUMBER,
300 X_Attribute_Category VARCHAR2,
301 X_Attribute1 VARCHAR2,
302 X_Attribute2 VARCHAR2,
303 X_Attribute3 VARCHAR2,
304 X_Attribute4 VARCHAR2,
305 X_Attribute5 VARCHAR2,
306 X_Attribute6 VARCHAR2,
307 X_Attribute7 VARCHAR2,
308 X_Attribute8 VARCHAR2,
309 X_Attribute9 VARCHAR2,
310 X_Attribute10 VARCHAR2,
311 X_Attribute11 VARCHAR2,
312 X_Attribute12 VARCHAR2,
313 X_Attribute13 VARCHAR2,
314 X_Attribute14 VARCHAR2,
315 X_Attribute15 VARCHAR2,
316 X_Request_ID NUMBER,
317 X_Program_Application_ID NUMBER,
318 X_Program_ID NUMBER,
319 X_Program_Update_Date DATE
320 -- X_Org_ID NUMBER /* bug2493519*/
321 )
322
323 IS
324
325 BEGIN
326
327 UPDATE PO_QUOTATION_APPROVALS
328
329 SET
330 quotation_approval_id = X_Quotation_Approval_ID,
331 approval_type = X_Approval_Type,
332 approval_reason = X_Approval_Reason,
333 comments = X_Comments,
334 approver_id = X_Approver_ID,
335 start_date_active = X_Start_Date_Active,
336 end_date_active = X_End_Date_Active,
337 line_location_id = X_Line_Location_ID,
338 last_update_date = X_Last_Update_Date,
339 last_updated_by = X_Last_Updated_By,
340 last_update_login = X_Last_Update_Login,
341 attribute_category = X_Attribute_Category,
342 attribute1 = X_Attribute1,
343 attribute2 = X_Attribute2,
344 attribute3 = X_Attribute3,
345 attribute4 = X_Attribute4,
346 attribute5 = X_Attribute5,
347 attribute6 = X_Attribute6,
348 attribute7 = X_Attribute7,
349 attribute8 = X_Attribute8,
350 attribute9 = X_Attribute9,
351 attribute10 = X_Attribute10,
352 attribute11 = X_Attribute11,
353 attribute12 = X_Attribute12,
354 attribute13 = X_Attribute13,
355 attribute14 = X_Attribute14,
356 attribute15 = X_Attribute15,
357 request_id = X_Request_ID,
358 program_application_id = X_Program_Application_ID,
359 program_id = X_Program_ID,
360 program_update_date = X_Program_Update_Date
361 -- org_id = X_Org_ID /* bug2493519*/
362 WHERE rowid = X_rowid;
363
364 if (SQL%NOTFOUND) then
365 Raise NO_DATA_FOUND;
366 end if;
367
368 END Update_Row;
369
370 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
371 BEGIN
372 DELETE FROM PO_QUOTATION_APPROVALS
373 WHERE rowid = X_Rowid;
374
375 if (SQL%NOTFOUND) then
376 Raise NO_DATA_FOUND;
377 end if;
378 END Delete_Row;
379
380 END QUOTATION_APPROVALS_PKG;