1 PACKAGE BODY PO_RELEASES_PKG_S1 as
2 /* $Header: POXP2PLB.pls 120.1 2005/06/02 06:42:34 appldev $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: lock_row()
7
8 =============================================================================*/
9 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
10 X_Po_Release_Id NUMBER,
11 X_Po_Header_Id NUMBER,
12 X_Release_Num NUMBER,
13 X_Agent_Id NUMBER,
14 X_Release_Date DATE,
15 X_Revision_Num NUMBER,
16 -- Bug 902976, zxzhang, 10/04/99
17 -- Change REVISED_DATE from VarChar(25) to Date.
18 -- X_Revised_Date VARCHAR2,
19 X_Revised_Date DATE,
20 X_Approved_Flag VARCHAR2,
21 X_Approved_Date DATE,
22 X_Print_Count NUMBER,
23 X_Printed_Date DATE,
24 X_Acceptance_Required_Flag VARCHAR2,
25 X_Acceptance_Due_Date DATE,
26 X_Hold_By NUMBER,
27 X_Hold_Date DATE,
28 X_Hold_Reason VARCHAR2,
29 X_Hold_Flag VARCHAR2,
30 X_Cancel_Flag VARCHAR2,
31 X_Cancelled_By NUMBER,
32 X_Cancel_Date DATE,
33 X_Cancel_Reason VARCHAR2,
34 X_Firm_Status_Lookup_Code VARCHAR2,
35 X_Pay_On_Code VARCHAR2,
36 --X_Firm_Date DATE,
37 X_Attribute_Category VARCHAR2,
38 X_Attribute1 VARCHAR2,
39 X_Attribute2 VARCHAR2,
40 X_Attribute3 VARCHAR2,
41 X_Attribute4 VARCHAR2,
42 X_Attribute5 VARCHAR2,
43 X_Attribute6 VARCHAR2,
44 X_Attribute7 VARCHAR2,
45 X_Attribute8 VARCHAR2,
46 X_Attribute9 VARCHAR2,
47 X_Attribute10 VARCHAR2,
48 X_Attribute11 VARCHAR2,
49 X_Attribute12 VARCHAR2,
50 X_Attribute13 VARCHAR2,
51 X_Attribute14 VARCHAR2,
52 X_Attribute15 VARCHAR2,
53 X_Authorization_Status VARCHAR2,
54 X_Ussgl_Transaction_Code VARCHAR2,
55 X_Government_Context VARCHAR2,
56 X_Closed_Code VARCHAR2,
57 X_Frozen_Flag VARCHAR2,
58 X_Release_Type VARCHAR2,
59 --X_Note_To_Vendor VARCHAR2
60 X_Global_Attribute_Category VARCHAR2,
61 X_Global_Attribute1 VARCHAR2,
62 X_Global_Attribute2 VARCHAR2,
63 X_Global_Attribute3 VARCHAR2,
64 X_Global_Attribute4 VARCHAR2,
65 X_Global_Attribute5 VARCHAR2,
66 X_Global_Attribute6 VARCHAR2,
67 X_Global_Attribute7 VARCHAR2,
68 X_Global_Attribute8 VARCHAR2,
69 X_Global_Attribute9 VARCHAR2,
70 X_Global_Attribute10 VARCHAR2,
71 X_Global_Attribute11 VARCHAR2,
72 X_Global_Attribute12 VARCHAR2,
73 X_Global_Attribute13 VARCHAR2,
74 X_Global_Attribute14 VARCHAR2,
75 X_Global_Attribute15 VARCHAR2,
76 X_Global_Attribute16 VARCHAR2,
77 X_Global_Attribute17 VARCHAR2,
78 X_Global_Attribute18 VARCHAR2,
79 X_Global_Attribute19 VARCHAR2,
80 X_Global_Attribute20 VARCHAR2,
81 p_shipping_control IN VARCHAR2 -- <INBOUND LOGISTICS FPJ>
82 ) IS
83 CURSOR C IS
84 SELECT *
85 FROM PO_RELEASES
86 WHERE rowid = X_Rowid
87 FOR UPDATE of Po_Release_Id NOWAIT;
88 Recinfo C%ROWTYPE;
89 BEGIN
90 OPEN C;
91 FETCH C INTO Recinfo;
92 if (C%NOTFOUND) then
93 CLOSE C;
94 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
95 APP_EXCEPTION.Raise_Exception;
96 end if;
97 CLOSE C;
98 /* Bug 2032728. Modified the lock row procedures for headers to compare truncated
99 dates so that the time stamp is not compared as the time stamp
100 was causing the problem.
101 */
102 if (
103
104 (Recinfo.po_release_id = X_Po_Release_Id)
105 AND (Recinfo.po_header_id = X_Po_Header_Id)
106 AND (Recinfo.release_num = X_Release_Num)
107 AND (Recinfo.agent_id = X_Agent_Id)
108 AND (trunc(Recinfo.release_date) = trunc(X_Release_Date))
109 AND ( (Recinfo.revision_num = X_Revision_Num)
110 OR ( (Recinfo.revision_num IS NULL)
111 AND (X_Revision_Num IS NULL)))
112 AND ( (trunc(Recinfo.revised_date) = trunc(X_Revised_Date))
113 OR ( (Recinfo.revised_date IS NULL)
114 AND (X_Revised_Date IS NULL)))
115 AND ( (Recinfo.approved_flag = X_Approved_Flag)
116 OR ( (Recinfo.approved_flag IS NULL)
117 AND (X_Approved_Flag IS NULL)))
118 AND ( (trunc(Recinfo.approved_date) = trunc(X_Approved_Date))
119 OR ( (Recinfo.approved_date IS NULL)
120 AND (X_Approved_Date IS NULL)))
121 AND ( (Recinfo.print_count = X_Print_Count)
122 OR ( (Recinfo.print_count IS NULL)
123 AND (X_Print_Count IS NULL)))
124 AND ( (trunc(Recinfo.printed_date) = trunc(X_Printed_Date))
125 OR ( (Recinfo.printed_date IS NULL)
126 AND (X_Printed_Date IS NULL)))
127 AND ( (Recinfo.acceptance_required_flag
128 = X_Acceptance_Required_Flag)
129 OR ( (Recinfo.acceptance_required_flag IS NULL)
130 AND (X_Acceptance_Required_Flag IS NULL)))
131 AND ( (trunc(Recinfo.acceptance_due_date) = trunc(X_Acceptance_Due_Date))
132 OR ( (Recinfo.acceptance_due_date IS NULL)
133 AND (X_Acceptance_Due_Date IS NULL)))
134 AND ( (Recinfo.hold_by = X_Hold_By)
135 OR ( (Recinfo.hold_by IS NULL)
136 AND (X_Hold_By IS NULL)))
137 AND ( (trunc(Recinfo.hold_date) = trunc(X_Hold_Date))
138 OR ( (Recinfo.hold_date IS NULL)
139 AND (X_Hold_Date IS NULL)))
140 AND ( (Recinfo.hold_reason = X_Hold_Reason)
141 OR ( (Recinfo.hold_reason IS NULL)
142 AND (X_Hold_Reason IS NULL)))
143 AND ( (Recinfo.hold_flag = X_Hold_Flag)
144 OR ( (Recinfo.hold_flag IS NULL)
145 AND (X_Hold_Flag IS NULL)))
146 AND ( (Recinfo.cancel_flag = X_Cancel_Flag)
147 OR ( (Recinfo.cancel_flag IS NULL)
148 AND (X_Cancel_Flag IS NULL)))
149 AND ( (Recinfo.cancelled_by = X_Cancelled_By)
150 OR ( (Recinfo.cancelled_by IS NULL)
151 AND (X_Cancelled_By IS NULL)))
152 AND ( (trunc(Recinfo.cancel_date) = trunc(X_Cancel_Date))
153 OR ( (Recinfo.cancel_date IS NULL)
154 AND (X_Cancel_Date IS NULL)))
155 AND ( (Recinfo.cancel_reason = X_Cancel_Reason)
156 OR ( (Recinfo.cancel_reason IS NULL)
157 AND (X_Cancel_Reason IS NULL)))
158 AND ( (Recinfo.firm_status_lookup_code
159 = X_Firm_Status_Lookup_Code)
160 OR ( (Recinfo.firm_status_lookup_code IS NULL)
161 AND (X_Firm_Status_Lookup_Code IS NULL)))
162 AND ( (Recinfo.pay_on_code = X_Pay_On_Code)
163 OR ( (Recinfo.pay_on_code IS NULL)
164 AND (X_Pay_On_Code IS NULL)))
165 AND ( (Recinfo.attribute_category = X_Attribute_Category)
166 OR ( (Recinfo.attribute_category IS NULL)
167 AND (X_Attribute_Category IS NULL)))
168 AND ( (Recinfo.attribute1 = X_Attribute1)
169 OR ( (Recinfo.attribute1 IS NULL)
170 AND (X_Attribute1 IS NULL)))
171 AND ( (Recinfo.attribute2 = X_Attribute2)
172 OR ( (Recinfo.attribute2 IS NULL)
173 AND (X_Attribute2 IS NULL)))
174 AND ( (Recinfo.attribute3 = X_Attribute3)
175 OR ( (Recinfo.attribute3 IS NULL)
176 AND (X_Attribute3 IS NULL)))
177 AND ( (Recinfo.attribute4 = X_Attribute4)
178 OR ( (Recinfo.attribute4 IS NULL)
179 AND (X_Attribute4 IS NULL)))
180 AND ( (Recinfo.attribute5 = X_Attribute5)
181 OR ( (Recinfo.attribute5 IS NULL)
182 AND (X_Attribute5 IS NULL)))
183 AND ( (Recinfo.attribute6 = X_Attribute6)
184 OR ( (Recinfo.attribute6 IS NULL)
185 AND (X_Attribute6 IS NULL)))
186 AND ( (Recinfo.attribute7 = X_Attribute7)
187 OR ( (Recinfo.attribute7 IS NULL)
188 AND (X_Attribute7 IS NULL)))
189 AND ( (Recinfo.attribute8 = X_Attribute8)
190 OR ( (Recinfo.attribute8 IS NULL)
191 AND (X_Attribute8 IS NULL)))
192 AND ( (Recinfo.attribute9 = X_Attribute9)
193 OR ( (Recinfo.attribute9 IS NULL)
194 AND (X_Attribute9 IS NULL)))
195 AND ( (Recinfo.attribute10 = X_Attribute10)
196 OR ( (Recinfo.attribute10 IS NULL)
197 AND (X_Attribute10 IS NULL)))
198 AND ( (Recinfo.attribute11 = X_Attribute11)
199 OR ( (Recinfo.attribute11 IS NULL)
200 AND (X_Attribute11 IS NULL)))
201 AND ( (Recinfo.attribute12 = X_Attribute12)
202 OR ( (Recinfo.attribute12 IS NULL)
203 AND (X_Attribute12 IS NULL)))
204 AND ( (Recinfo.attribute13 = X_Attribute13)
205 OR ( (Recinfo.attribute13 IS NULL)
206 AND (X_Attribute13 IS NULL)))
207 AND ( (Recinfo.attribute14 = X_Attribute14)
208 OR ( (Recinfo.attribute14 IS NULL)
209 AND (X_Attribute14 IS NULL)))
210 AND ( (Recinfo.attribute15 = X_Attribute15)
211 OR ( (Recinfo.attribute15 IS NULL)
212 AND (X_Attribute15 IS NULL)))
213 AND ( (Recinfo.authorization_status = X_Authorization_Status)
214 OR ( (Recinfo.authorization_status IS NULL)
215 AND (X_Authorization_Status IS NULL)))
216 AND ( (Recinfo.government_context = X_Government_Context)
217 OR ( (Recinfo.government_context IS NULL)
218 AND (X_Government_Context IS NULL)))
219 AND ( (Recinfo.closed_code = X_Closed_Code)
220 OR ( (Recinfo.closed_code IS NULL)
221 AND (X_Closed_Code IS NULL)))
222 AND ( (Recinfo.frozen_flag = X_Frozen_Flag)
223 OR ( (Recinfo.frozen_flag IS NULL)
224 AND (X_Frozen_Flag IS NULL)))
225 AND ( (Recinfo.release_type = X_Release_Type)
226 OR ( (Recinfo.release_type IS NULL)
227 AND (X_Release_Type IS NULL)))
228 AND ( (Recinfo.global_attribute_category = X_Global_Attribute_Category)
229 OR ( (Recinfo.global_attribute_category IS NULL)
230 AND (X_Global_Attribute_Category IS NULL)))
231 AND ( (Recinfo.global_attribute1 = X_Global_Attribute1)
232 OR ( (Recinfo.global_attribute1 IS NULL)
233 AND (X_Global_Attribute1 IS NULL)))
234 AND ( (Recinfo.global_attribute2 = X_Global_Attribute2)
235 OR ( (Recinfo.global_attribute2 IS NULL)
236 AND (X_Global_Attribute2 IS NULL)))
237 AND ( (Recinfo.global_attribute3 = X_Global_Attribute3)
238 OR ( (Recinfo.global_attribute3 IS NULL)
239 AND (X_Global_Attribute3 IS NULL)))
240 AND ( (Recinfo.global_attribute4 = X_Global_Attribute4)
241 OR ( (Recinfo.global_attribute4 IS NULL)
242 AND (X_Global_Attribute4 IS NULL)))
243 AND ( (Recinfo.global_attribute5 = X_Global_Attribute5)
244 OR ( (Recinfo.global_attribute5 IS NULL)
245 AND (X_Global_Attribute5 IS NULL)))
246 AND ( (Recinfo.global_attribute6 = X_Global_Attribute6)
247 OR ( (Recinfo.global_attribute6 IS NULL)
248 AND (X_Global_Attribute6 IS NULL)))
249 AND ( (Recinfo.global_attribute7 = X_Global_Attribute7)
250 OR ( (Recinfo.global_attribute7 IS NULL)
251 AND (X_Global_Attribute7 IS NULL)))
252 AND ( (Recinfo.global_attribute8 = X_Global_Attribute8)
253 OR ( (Recinfo.global_attribute8 IS NULL)
254 AND (X_Global_Attribute8 IS NULL)))
255 AND ( (Recinfo.global_attribute9 = X_Global_Attribute9)
256 OR ( (Recinfo.global_attribute9 IS NULL)
257 AND (X_Global_Attribute9 IS NULL)))
258 AND ( (Recinfo.global_attribute10 = X_Global_Attribute10)
259 OR ( (Recinfo.global_attribute10 IS NULL)
260 AND (X_Global_Attribute10 IS NULL)))
261 AND ( (Recinfo.global_attribute11 = X_Global_Attribute11)
262 OR ( (Recinfo.global_attribute11 IS NULL)
263 AND (X_Global_Attribute11 IS NULL)))
264 AND ( (Recinfo.global_attribute12 = X_Global_Attribute12)
265 OR ( (Recinfo.global_attribute12 IS NULL)
266 AND (X_Global_Attribute12 IS NULL)))
267 AND ( (Recinfo.global_attribute13 = X_Global_Attribute13)
268 OR ( (Recinfo.global_attribute13 IS NULL)
269 AND (X_Global_Attribute13 IS NULL)))
270 AND ( (Recinfo.global_attribute14 = X_Global_Attribute14)
271 OR ( (Recinfo.global_attribute14 IS NULL)
272 AND (X_Global_Attribute14 IS NULL)))
273 AND ( (Recinfo.global_attribute15 = X_Global_Attribute15)
274 OR ( (Recinfo.global_attribute15 IS NULL)
275 AND (X_Global_Attribute15 IS NULL)))
276 AND ( (Recinfo.global_attribute16 = X_Global_Attribute16)
277 OR ( (Recinfo.global_attribute16 IS NULL)
278 AND (X_Global_Attribute16 IS NULL)))
279 AND ( (Recinfo.global_attribute17 = X_Global_Attribute17)
280 OR ( (Recinfo.global_attribute17 IS NULL)
281 AND (X_Global_Attribute17 IS NULL)))
282 AND ( (Recinfo.global_attribute18 = X_Global_Attribute18)
283 OR ( (Recinfo.global_attribute18 IS NULL)
284 AND (X_Global_Attribute18 IS NULL)))
285 AND ( (Recinfo.global_attribute19 = X_Global_Attribute19)
286 OR ( (Recinfo.global_attribute19 IS NULL)
287 AND (X_Global_Attribute19 IS NULL)))
288 AND ( (Recinfo.global_attribute20 = X_Global_Attribute20)
289 OR ( (Recinfo.global_attribute20 IS NULL)
290 AND (X_Global_Attribute20 IS NULL)))
291 AND ( (Recinfo.shipping_control = p_shipping_control)
292 OR ( (Recinfo.shipping_control IS NULL)
293 AND (p_shipping_control IS NULL))) -- <INBOUND LOGISTICS FPJ>
294 ) then
295 return;
296 else
297 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
298 APP_EXCEPTION.RAISE_EXCEPTION;
299 end if;
300 END Lock_Row;
301
302 END PO_RELEASES_PKG_S1;