[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_PKG_S3
Source
1 PACKAGE BODY PO_HEADERS_PKG_S3 as
2 /* $Header: POXRFQHB.pls 120.1 2006/02/06 11:29:30 dedelgad noship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: Lock_Row()
7
8 ===========================================================================*/
9
10 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
11 X_Po_Header_Id NUMBER,
12 X_Agent_Id NUMBER,
13 X_Type_Lookup_Code VARCHAR2,
14 X_Segment1 VARCHAR2,
15 X_Summary_Flag VARCHAR2,
16 X_Enabled_Flag VARCHAR2,
17 X_Segment2 VARCHAR2,
18 X_Segment3 VARCHAR2,
19 X_Segment4 VARCHAR2,
20 X_Segment5 VARCHAR2,
21 X_Vendor_Id NUMBER,
22 X_Vendor_Site_Id NUMBER,
23 X_Vendor_Contact_Id NUMBER,
24 X_Ship_To_Location_Id NUMBER,
25 X_Bill_To_Location_Id NUMBER,
26 X_Terms_Id NUMBER,
27 X_Ship_Via_Lookup_Code VARCHAR2,
28 X_Fob_Lookup_Code VARCHAR2,
29 X_Freight_Terms_Lookup_Code VARCHAR2,
30 X_Status_Lookup_Code VARCHAR2,
31 X_Currency_Code VARCHAR2,
32 X_Rate_Type VARCHAR2,
33 X_Rate_Date DATE,
34 X_Rate NUMBER,
35 X_From_Header_Id NUMBER,
36 X_From_Type_Lookup_Code VARCHAR2,
37 X_Start_Date DATE,
38 X_End_Date DATE,
39 X_Revision_Num NUMBER,
40 -- Bug 902976, zxzhang, 10/04/99
41 -- Change REVISED_DATE from VarChar(25) to Date.
42 -- X_Revised_Date VARCHAR2,
43 X_Revised_Date DATE,
44 X_Note_To_Vendor VARCHAR2,
45 X_Printed_Date DATE,
46 X_Comments VARCHAR2,
47 X_Reply_Date DATE,
48 X_Reply_Method_Lookup_Code VARCHAR2,
49 X_Rfq_Close_Date DATE,
50 X_Quote_Type_Lookup_Code VARCHAR2,
51 X_Quotation_Class_Code VARCHAR2,
52 X_Quote_Warning_Delay NUMBER,
53 X_Quote_Vendor_Quote_Number VARCHAR2,
54 X_Closed_Date DATE,
55 X_Approval_Required_Flag VARCHAR2,
56 X_Attribute_Category VARCHAR2,
57 X_Attribute1 VARCHAR2,
58 X_Attribute2 VARCHAR2,
59 X_Attribute3 VARCHAR2,
60 X_Attribute4 VARCHAR2,
61 X_Attribute5 VARCHAR2,
62 X_Attribute6 VARCHAR2,
63 X_Attribute7 VARCHAR2,
64 X_Attribute8 VARCHAR2,
65 X_Attribute9 VARCHAR2,
66 X_Attribute10 VARCHAR2,
67 X_Attribute11 VARCHAR2,
68 X_Attribute12 VARCHAR2,
69 X_Attribute13 VARCHAR2,
70 X_Attribute14 VARCHAR2,
71 X_Attribute15 VARCHAR2
72 ) IS
73 CURSOR C IS
74 SELECT *
75 FROM PO_HEADERS
76 WHERE rowid = X_Rowid
77 FOR UPDATE of Po_Header_Id NOWAIT;
78 Recinfo C%ROWTYPE;
79 BEGIN
80 OPEN C;
81 FETCH C INTO Recinfo;
82 if (C%NOTFOUND) then
83 CLOSE C;
84 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
85 APP_EXCEPTION.Raise_Exception;
86 end if;
87 CLOSE C;
88 if (
89
90 (Recinfo.po_header_id = X_Po_Header_Id)
91 AND (Recinfo.agent_id = X_Agent_Id)
92 AND (Recinfo.type_lookup_code = X_Type_Lookup_Code)
93 AND (Recinfo.segment1 = X_Segment1)
94 AND (nvl(Recinfo.summary_flag,'N') = nvl(X_Summary_Flag,'N'))
95 AND (nvl(Recinfo.enabled_flag,'N') = nvl(X_Enabled_Flag,'N'))
96 AND ( (Recinfo.segment2 = X_Segment2)
97 OR ((Recinfo.segment2 IS NULL)
98 AND (X_Segment2 IS NULL)))
99 AND ( (Recinfo.segment3 = X_Segment3)
100 OR ( (Recinfo.segment3 IS NULL)
101 AND (X_Segment3 IS NULL)))
102 AND ( (Recinfo.segment4 = X_Segment4)
103 OR ( (Recinfo.segment4 IS NULL)
104 AND (X_Segment4 IS NULL)))
105 AND ( (Recinfo.segment5 = X_Segment5)
106 OR ( (Recinfo.segment5 IS NULL)
107 AND (X_Segment5 IS NULL)))
108 AND ( (Recinfo.vendor_id = X_Vendor_Id)
109 OR ( (Recinfo.vendor_id IS NULL)
110 AND (X_Vendor_Id IS NULL)))
111 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
112 OR ( (Recinfo.vendor_site_id IS NULL)
113 AND (X_Vendor_Site_Id IS NULL)))
114 AND ( (Recinfo.vendor_contact_id = X_Vendor_Contact_Id)
115 OR ( (Recinfo.vendor_contact_id IS NULL)
116 AND (X_Vendor_Contact_Id IS NULL)))
117 AND ( (Recinfo.ship_to_location_id = X_Ship_To_Location_Id)
118 OR ( (Recinfo.ship_to_location_id IS NULL)
119 AND (X_Ship_To_Location_Id IS NULL)))
120 AND ( (Recinfo.bill_to_location_id = X_Bill_To_Location_Id)
121 OR ( (Recinfo.bill_to_location_id IS NULL)
122 AND (X_Bill_To_Location_Id IS NULL)))
123 AND ( (Recinfo.terms_id = X_Terms_Id)
124 OR ( (Recinfo.terms_id IS NULL)
125 AND (X_Terms_Id IS NULL)))
126 AND ( (Recinfo.ship_via_lookup_code = X_Ship_Via_Lookup_Code)
127 OR ( (Recinfo.ship_via_lookup_code IS NULL)
128 AND (X_Ship_Via_Lookup_Code IS NULL)))
129 AND ( (Recinfo.fob_lookup_code = X_Fob_Lookup_Code)
130 OR ( (Recinfo.fob_lookup_code IS NULL)
131 AND (X_Fob_Lookup_Code IS NULL)))
132 AND ( (Recinfo.freight_terms_lookup_code = X_Freight_Terms_Lookup_Code)
133 OR ( (Recinfo.freight_terms_lookup_code IS NULL)
134 AND (X_Freight_Terms_Lookup_Code IS NULL)))
135 AND ( (Recinfo.status_lookup_code = X_Status_Lookup_Code)
136 OR ( (Recinfo.status_lookup_code IS NULL)
137 AND (X_Status_Lookup_Code IS NULL)))
138 AND ( (Recinfo.currency_code = X_Currency_Code)
139 OR ( (Recinfo.currency_code IS NULL)
140 AND (X_Currency_Code IS NULL)))
141 AND ( (Recinfo.rate_type = X_Rate_Type)
142 OR ( (Recinfo.rate_type IS NULL)
143 AND (X_Rate_Type IS NULL)))
144 AND ( (trunc(Recinfo.rate_date) = trunc(X_Rate_Date))
145 OR ( (Recinfo.rate_date IS NULL)
146 AND (X_Rate_Date IS NULL)))
147 AND ( (Recinfo.rate = X_Rate)
148 OR ( (Recinfo.rate IS NULL)
149 AND (X_Rate IS NULL)))
150 AND ( (Recinfo.from_header_id = X_From_Header_Id)
151 OR ( (Recinfo.from_header_id IS NULL)
152 AND (X_From_Header_Id IS NULL)))
153 AND ( (Recinfo.from_type_lookup_code = X_From_Type_Lookup_Code)
154 OR ( (Recinfo.from_type_lookup_code IS NULL)
155 AND (X_From_Type_Lookup_Code IS NULL)))
156 AND ( (trunc(Recinfo.start_date) = trunc(X_Start_Date))
157 OR ( (Recinfo.start_date IS NULL)
158 AND (X_Start_Date IS NULL)))
159 AND ( (trunc(Recinfo.end_date) = trunc(X_End_Date))
160 OR ( (Recinfo.end_date IS NULL)
161 AND (X_End_Date IS NULL)))
162 AND ( (Recinfo.revision_num = X_Revision_Num)
163 OR ( (Recinfo.revision_num IS NULL)
164 AND (X_Revision_Num IS NULL)))
165 AND ( (trunc(Recinfo.revised_date) = trunc(X_Revised_Date))
166 OR ( (Recinfo.revised_date IS NULL)
167 AND (X_Revised_Date IS NULL)))
168 AND ( (Recinfo.note_to_vendor = X_Note_To_Vendor)
169 OR ( (Recinfo.note_to_vendor IS NULL)
170 AND (X_Note_To_Vendor IS NULL)))
171 AND ( (trunc(Recinfo.printed_date) = trunc(X_Printed_Date))
172 OR ( (Recinfo.printed_date IS NULL)
173 AND (X_Printed_Date IS NULL)))
174 AND ( (Recinfo.comments = X_Comments)
175 OR ( (Recinfo.comments IS NULL)
176 AND (X_Comments IS NULL)))
177 AND ( (trunc(Recinfo.reply_date) = trunc(X_Reply_Date))
178 OR ( (Recinfo.reply_date IS NULL)
179 AND (X_Reply_Date IS NULL)))
180 AND ( (Recinfo.reply_method_lookup_code = X_Reply_Method_Lookup_Code)
181 OR ( (Recinfo.reply_method_lookup_code IS NULL)
182 AND (X_Reply_Method_Lookup_Code IS NULL)))
183
184 ) then
185
186 if (
187 ( (trunc(Recinfo.rfq_close_date) = trunc(X_Rfq_Close_Date))
188 OR ( (Recinfo.rfq_close_date IS NULL)
189 AND (X_Rfq_Close_Date IS NULL)))
190 AND ( (Recinfo.quote_type_lookup_code = X_Quote_Type_Lookup_Code)
191 OR ( (Recinfo.quote_type_lookup_code IS NULL)
192 AND (X_Quote_Type_Lookup_Code IS NULL)))
193 AND ( (Recinfo.quotation_class_code = X_Quotation_Class_Code)
194 OR ( (Recinfo.quotation_class_code IS NULL)
195 AND (X_Quotation_Class_Code IS NULL)))
196 AND ( (Recinfo.quote_warning_delay = X_Quote_Warning_Delay)
197 OR ( (Recinfo.quote_warning_delay IS NULL)
198 AND (X_Quote_Warning_Delay IS NULL)))
199 AND ( (nvl(Recinfo.quote_vendor_quote_number,'0') = nvl(X_Quote_Vendor_Quote_Number,'0'))
200 OR ( (Recinfo.quote_vendor_quote_number IS NULL)
201 AND (X_Quote_Vendor_Quote_Number IS NULL)))
202 AND ( (trunc(Recinfo.closed_date) = trunc(X_Closed_Date))
203 OR ( (Recinfo.closed_date IS NULL)
204 AND (X_Closed_Date IS NULL)))
205 AND ( (nvl(Recinfo.approval_required_flag,'N') = nvl(X_Approval_Required_Flag,'N'))
206 OR ( (Recinfo.approval_required_flag IS NULL)
207 AND (X_Approval_Required_Flag IS NULL)))
208 AND ( (Recinfo.attribute_category = X_Attribute_Category)
209 OR ( (Recinfo.attribute_category IS NULL)
210 AND (X_Attribute_Category IS NULL)))
211 AND ( (Recinfo.attribute1 = X_Attribute1)
212 OR ( (Recinfo.attribute1 IS NULL)
213 AND (X_Attribute1 IS NULL)))
214 AND ( (Recinfo.attribute2 = X_Attribute2)
215 OR ( (Recinfo.attribute2 IS NULL)
216 AND (X_Attribute2 IS NULL)))
217 AND ( (Recinfo.attribute3 = X_Attribute3)
218 OR ( (Recinfo.attribute3 IS NULL)
219 AND (X_Attribute3 IS NULL)))
220 AND ( (Recinfo.attribute4 = X_Attribute4)
221 OR ( (Recinfo.attribute4 IS NULL)
222 AND (X_Attribute4 IS NULL)))
223 AND ( (Recinfo.attribute5 = X_Attribute5)
224 OR ( (Recinfo.attribute5 IS NULL)
225 AND (X_Attribute5 IS NULL)))
226 AND ( (Recinfo.attribute6 = X_Attribute6)
227 OR ( (Recinfo.attribute6 IS NULL)
228 AND (X_Attribute6 IS NULL)))
229 AND ( (Recinfo.attribute7 = X_Attribute7)
230 OR ( (Recinfo.attribute7 IS NULL)
231 AND (X_Attribute7 IS NULL)))
232 AND ( (Recinfo.attribute8 = X_Attribute8)
233 OR ( (Recinfo.attribute8 IS NULL)
234 AND (X_Attribute8 IS NULL)))
235 AND ( (Recinfo.attribute9 = X_Attribute9)
236 OR ( (Recinfo.attribute9 IS NULL)
237 AND (X_Attribute9 IS NULL)))
238 AND ( (Recinfo.attribute10 = X_Attribute10)
239 OR ( (Recinfo.attribute10 IS NULL)
240 AND (X_Attribute10 IS NULL)))
241 AND ( (Recinfo.attribute11 = X_Attribute11)
242 OR ( (Recinfo.attribute11 IS NULL)
243 AND (X_Attribute11 IS NULL)))
244 AND ( (Recinfo.attribute12 = X_Attribute12)
245 OR ( (Recinfo.attribute12 IS NULL)
246 AND (X_Attribute12 IS NULL)))
247 AND ( (Recinfo.attribute13 = X_Attribute13)
248 OR ( (Recinfo.attribute13 IS NULL)
249 AND (X_Attribute13 IS NULL)))
250 AND ( (Recinfo.attribute14 = X_Attribute14)
251 OR ( (Recinfo.attribute14 IS NULL)
252 AND (X_Attribute14 IS NULL)))
253 AND ( (Recinfo.attribute15 = X_Attribute15)
254 OR ( (Recinfo.attribute15 IS NULL)
255 AND (X_Attribute15 IS NULL)))
256 ) then
257 return;
258 else
259 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
260 APP_EXCEPTION.RAISE_EXCEPTION;
261 end if;
262 else
263 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
264 APP_EXCEPTION.RAISE_EXCEPTION;
265
266 end if;
267
268 END Lock_Row;
269
270
271 /*===========================================================================
272
273 PROCEDURE NAME: Update_Row()
274
275 ===========================================================================*/
276
277 PROCEDURE Update_Row(X_Rowid VARCHAR2,
278 X_Po_Header_Id NUMBER,
279 X_Agent_Id NUMBER,
280 X_Type_Lookup_Code VARCHAR2,
281 X_Last_Update_Date DATE,
282 X_Last_Updated_By NUMBER,
283 X_Segment1 VARCHAR2,
284 X_Summary_Flag VARCHAR2,
285 X_Enabled_Flag VARCHAR2,
286 X_Segment2 VARCHAR2,
287 X_Segment3 VARCHAR2,
288 X_Segment4 VARCHAR2,
289 X_Segment5 VARCHAR2,
290 X_Last_Update_Login NUMBER,
291 X_Vendor_Id NUMBER,
292 X_Vendor_Site_Id NUMBER,
293 X_Vendor_Contact_Id NUMBER,
294 X_Ship_To_Location_Id NUMBER,
295 X_Bill_To_Location_Id NUMBER,
296 X_Terms_Id NUMBER,
297 X_Ship_Via_Lookup_Code VARCHAR2,
298 X_Fob_Lookup_Code VARCHAR2,
299 X_Freight_Terms_Lookup_Code VARCHAR2,
300 X_Status_Lookup_Code VARCHAR2,
301 X_Currency_Code VARCHAR2,
305 X_From_Header_Id NUMBER,
302 X_Rate_Type VARCHAR2,
303 X_Rate_Date DATE,
304 X_Rate NUMBER,
306 X_From_Type_Lookup_Code VARCHAR2,
307 X_Start_Date DATE,
308 X_End_Date DATE,
309 X_Revision_Num NUMBER,
310 -- Bug 902976, zxzhang, 10/04/99
311 -- Change REVISED_DATE from VarChar(25) to Date.
312 -- X_Revised_Date VARCHAR2,
313 X_Revised_Date DATE,
314 X_Note_To_Vendor VARCHAR2,
315 X_Printed_Date DATE,
316 X_Comments VARCHAR2,
317 X_Reply_Date DATE,
318 X_Reply_Method_Lookup_Code VARCHAR2,
319 X_Rfq_Close_Date DATE,
320 X_Quote_Type_Lookup_Code VARCHAR2,
321 X_Quotation_Class_Code VARCHAR2,
322 X_Quote_Warning_Delay NUMBER,
323 X_Quote_Vendor_Quote_Number VARCHAR2,
324 X_Closed_Date DATE,
325 X_Approval_Required_Flag VARCHAR2,
326 X_Attribute_Category VARCHAR2,
327 X_Attribute1 VARCHAR2,
328 X_Attribute2 VARCHAR2,
329 X_Attribute3 VARCHAR2,
330 X_Attribute4 VARCHAR2,
331 X_Attribute5 VARCHAR2,
332 X_Attribute6 VARCHAR2,
333 X_Attribute7 VARCHAR2,
334 X_Attribute8 VARCHAR2,
335 X_Attribute9 VARCHAR2,
336 X_Attribute10 VARCHAR2,
337 X_Attribute11 VARCHAR2,
338 X_Attribute12 VARCHAR2,
339 X_Attribute13 VARCHAR2,
340 X_Attribute14 VARCHAR2,
341 X_Attribute15 VARCHAR2
342 ) IS
343
344 BEGIN
345
346 UPDATE PO_HEADERS
347 SET
348 po_header_id = X_Po_Header_Id,
349 agent_id = X_Agent_Id,
350 type_lookup_code = X_Type_Lookup_Code,
351 last_update_date = X_Last_Update_Date,
352 last_updated_by = X_Last_Updated_By,
353 segment1 = X_Segment1,
354 summary_flag = X_Summary_Flag,
355 enabled_flag = X_Enabled_Flag,
356 segment2 = X_Segment2,
357 segment3 = X_Segment3,
361 vendor_id = X_Vendor_Id,
358 segment4 = X_Segment4,
359 segment5 = X_Segment5,
360 last_update_login = X_Last_Update_Login,
362 vendor_site_id = X_Vendor_Site_Id,
363 vendor_contact_id = X_Vendor_Contact_Id,
364 ship_to_location_id = X_Ship_To_Location_Id,
365 bill_to_location_id = X_Bill_To_Location_Id,
366 terms_id = X_Terms_Id,
367 ship_via_lookup_code = X_Ship_Via_Lookup_Code,
368 fob_lookup_code = X_Fob_Lookup_Code,
369 freight_terms_lookup_code = X_Freight_Terms_Lookup_Code,
370 status_lookup_code = X_Status_Lookup_Code,
371 currency_code = X_Currency_Code,
372 rate_type = X_Rate_Type,
373 rate_date = X_Rate_Date,
374 rate = X_Rate,
375 from_header_id = X_From_Header_Id,
376 from_type_lookup_code = X_From_Type_Lookup_Code,
377 start_date = X_Start_Date,
378 end_date = X_End_Date,
379 revision_num = X_Revision_Num,
380 revised_date = X_Revised_Date,
381 note_to_vendor = X_Note_To_Vendor,
382 printed_date = X_Printed_Date,
383 comments = X_Comments,
384 reply_date = X_Reply_Date,
385 reply_method_lookup_code = X_Reply_Method_Lookup_Code,
386 rfq_close_date = X_Rfq_Close_Date,
387 quote_type_lookup_code = X_Quote_Type_Lookup_Code,
388 quotation_class_code = X_Quotation_Class_Code,
389 quote_warning_delay = X_Quote_Warning_Delay,
390 quote_vendor_quote_number = X_Quote_Vendor_Quote_Number,
391 closed_date = X_Closed_Date,
392 approval_required_flag = X_Approval_Required_Flag,
393 attribute_category = X_Attribute_Category,
394 attribute1 = X_Attribute1,
395 attribute2 = X_Attribute2,
396 attribute3 = X_Attribute3,
397 attribute4 = X_Attribute4,
398 attribute5 = X_Attribute5,
399 attribute6 = X_Attribute6,
400 attribute7 = X_Attribute7,
401 attribute8 = X_Attribute8,
402 attribute9 = X_Attribute9,
403 attribute10 = X_Attribute10,
404 attribute11 = X_Attribute11,
405 attribute12 = X_Attribute12,
406 attribute13 = X_Attribute13,
407 attribute14 = X_Attribute14,
408 attribute15 = X_Attribute15
409
410 WHERE rowid = X_rowid;
411
412 if (SQL%NOTFOUND) then
413 Raise NO_DATA_FOUND;
414 end if;
415
416 END Update_Row;
417
418 END PO_HEADERS_PKG_S3;