[Home] [Help]
PACKAGE BODY: APPS.OKE_APPLY_REMOVE_HOLDS_PKG
Source
1 package body OKE_APPLY_REMOVE_HOLDS_PKG as
2 /* $Header: OKEHDPLB.pls 115.8 2002/11/19 23:03:39 jxtang ship $ */
3
4
5
6 PROCEDURE Lock_Row(X_hold_id NUMBER,
7 X_k_header_id NUMBER,
8 X_k_line_id NUMBER,
9 X_remove_date DATE,
10 X_remove_reason_code VARCHAR2,
11 X_remove_comment VARCHAR2,
12 X_Attribute_Category VARCHAR2,
13 X_Attribute1 VARCHAR2,
14 X_Attribute2 VARCHAR2,
15 X_Attribute3 VARCHAR2,
16 X_Attribute4 VARCHAR2,
17 X_Attribute5 VARCHAR2,
18 X_Attribute6 VARCHAR2,
19 X_Attribute7 VARCHAR2,
20 X_Attribute8 VARCHAR2,
21 X_Attribute9 VARCHAR2,
22 X_Attribute10 VARCHAR2,
23 X_Attribute11 VARCHAR2,
24 X_Attribute12 VARCHAR2,
25 X_Attribute13 VARCHAR2,
26 X_Attribute14 VARCHAR2,
27 X_Attribute15 VARCHAR2
28 ) is
29
30 cursor c is
31 select hold_id,
32 k_header_id,
33 k_line_id,
34 remove_date,
35 remove_reason_code,
36 remove_comment,
37 attribute_category,
38 attribute1,
39 attribute2,
40 attribute3,
41 attribute4,
42 attribute5,
43 attribute6,
44 attribute7,
45 attribute8,
46 attribute9,
47 attribute10,
48 attribute11,
49 attribute12,
50 attribute13,
51 attribute14,
52 attribute15
53 from OKE_K_HOLDS
54 where hold_id = X_hold_id
55 for update of hold_id nowait;
56
57 recinfo c%rowtype;
58
59 begin
60
61 open c;
62 fetch c into recinfo;
63 if (c%notfound) then
64 close c;
65 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
66 app_exception.raise_exception;
67 end if;
68 close c;
69
70 if (
71 ((rtrim(recinfo.remove_date) = rtrim(X_remove_date))
72 OR ((recinfo.remove_date is null) AND (X_remove_date is null)))
73 AND ((rtrim(recinfo.remove_reason_code) = rtrim(X_remove_reason_code))
74 OR ((recinfo.remove_reason_code is null) AND (X_remove_reason_code is null)))
75 AND ((rtrim(recinfo.remove_comment) = rtrim(X_remove_comment))
76 OR ((recinfo.remove_comment is null) AND (X_remove_comment is null)))
77 AND ((rtrim(recinfo.attribute_category) = rtrim(X_Attribute_Category))
78 OR ((recinfo.attribute_category is null) AND (X_Attribute_Category is null)))
79 AND ((rtrim(recinfo.attribute1) = rtrim(X_Attribute1))
80 OR ((recinfo.attribute1 is null) AND (X_Attribute1 is null)))
81 AND ((rtrim(recinfo.attribute2) = rtrim(X_Attribute2))
82 OR ((recinfo.attribute2 is null) AND (X_Attribute2 is null)))
83 AND ((rtrim(recinfo.attribute3) = rtrim(X_Attribute3))
84 OR ((recinfo.attribute3 is null) AND (X_Attribute3 is null)))
85 AND ((rtrim(recinfo.attribute4) = rtrim(X_Attribute4))
86 OR ((recinfo.attribute4 is null) AND (X_Attribute4 is null)))
87 AND ((rtrim(recinfo.attribute5) = rtrim(X_Attribute5))
88 OR ((recinfo.attribute5 is null) AND (X_Attribute5 is null)))
89 AND ((rtrim(recinfo.attribute6) = rtrim(X_Attribute6))
90 OR ((recinfo.attribute6 is null) AND (X_Attribute6 is null)))
91 AND ((rtrim(recinfo.attribute7) = rtrim(X_Attribute7))
92 OR ((recinfo.attribute7 is null) AND (X_Attribute7 is null)))
93 AND ((rtrim(recinfo.attribute8) = rtrim(X_Attribute8))
94 OR ((recinfo.attribute8 is null) AND (X_Attribute8 is null)))
95 AND ((rtrim(recinfo.attribute9) = rtrim(X_Attribute9))
96 OR ((recinfo.attribute9 is null) AND (X_Attribute9 is null)))
97 AND ((rtrim(recinfo.attribute10) = rtrim(X_Attribute10))
98 OR ((recinfo.attribute10 is null) AND (X_Attribute10 is null)))
99 AND ((rtrim(recinfo.attribute11) = rtrim(X_Attribute11))
100 OR ((recinfo.attribute11 is null) AND (X_Attribute11 is null)))
101 AND ((rtrim(recinfo.attribute12) = rtrim(X_Attribute12))
102 OR ((recinfo.attribute12 is null) AND (X_Attribute12 is null)))
103 AND ((rtrim(recinfo.attribute13) = rtrim(X_Attribute13))
104 OR ((recinfo.attribute13 is null) AND (X_Attribute13 is null)))
105 AND ((rtrim(recinfo.attribute14) = rtrim(X_Attribute14))
106 OR ((recinfo.attribute14 is null) AND (X_Attribute14 is null)))
107 AND ((rtrim(recinfo.attribute15) = rtrim(X_Attribute15))
108 OR ((recinfo.attribute15 is null) AND (X_Attribute15 is null)))
109 ) then
110 null;
111 else
112 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
113 app_exception.raise_exception;
114 end if;
115
116 end Lock_Row;
117
118
119 PROCEDURE Update_Row(X_hold_id NUMBER,
120 X_k_header_id NUMBER,
121 X_k_line_id NUMBER,
122 X_remove_date DATE,
123 X_remove_reason_code VARCHAR2,
124 X_remove_comment VARCHAR2,
125 X_hold_status_code VARCHAR2,
126 X_wf_item_type VARCHAR2,
127 X_wf_process VARCHAR2,
128 X_Last_Update_Date DATE,
129 X_Last_Updated_By NUMBER,
130 X_Creation_Date DATE,
131 X_Created_By NUMBER,
132 X_Last_Update_Login NUMBER,
133 X_Attribute_Category VARCHAR2,
134 X_Attribute1 VARCHAR2,
135 X_Attribute2 VARCHAR2,
136 X_Attribute3 VARCHAR2,
137 X_Attribute4 VARCHAR2,
138 X_Attribute5 VARCHAR2,
139 X_Attribute6 VARCHAR2,
140 X_Attribute7 VARCHAR2,
141 X_Attribute8 VARCHAR2,
142 X_Attribute9 VARCHAR2,
143 X_Attribute10 VARCHAR2,
144 X_Attribute11 VARCHAR2,
145 X_Attribute12 VARCHAR2,
146 X_Attribute13 VARCHAR2,
147 X_Attribute14 VARCHAR2,
148 X_Attribute15 VARCHAR2
149
150 ) is
151
152 x_prev_hold_status_code OKE_K_HOLDS.hold_status_code%TYPE;
153 x_k_Deliverable_ID OKE_K_HOLDS.Deliverable_ID%TYPE;
154 x_hold_type_code OKE_K_HOLDS.hold_type_code%TYPE;
155 x_hold_reason_code OKE_K_HOLDS.hold_reason_code%TYPE;
156
157 cursor C_prev is
158 select hold_status_code
159 ,Deliverable_ID
160 ,hold_type_code
161 ,hold_reason_code
162 from OKE_K_HOLDS
163 where hold_id = X_hold_id;
164
165 begin
166
167 open c_prev;
168 fetch c_prev into X_prev_hold_status_code
169 ,x_k_Deliverable_ID
170 ,x_hold_type_code
171 ,x_hold_reason_code;
172 close c_prev;
173
174 update OKE_K_HOLDS
175 set
176 remove_date = X_remove_date,
177 remove_reason_code = X_remove_reason_code,
178 remove_comment = X_remove_comment,
179 hold_status_code = X_hold_status_code,
180 wf_item_type = X_wf_item_type,
181 wf_process = X_wf_process,
182 Last_Update_Date = X_Last_Update_Date,
183 Last_Updated_By = X_Last_Updated_By,
184 Creation_Date = X_Creation_Date,
185 Created_By = X_Created_By,
186 Last_Update_Login = X_Last_Update_Login,
187 attribute_Category = X_Attribute_Category,
188 attribute1 = X_Attribute1,
189 attribute2 = X_Attribute2,
190 attribute3 = X_Attribute3,
191 attribute4 = X_Attribute4,
192 attribute5 = X_Attribute5,
193 attribute6 = X_Attribute6,
194 attribute7 = X_Attribute7,
195 attribute8 = X_Attribute8,
196 attribute9 = X_Attribute9,
197 attribute10 = X_Attribute10,
198 attribute11 = X_Attribute11,
199 attribute12 = X_Attribute12,
200 attribute13 = X_Attribute13,
201 attribute14 = X_Attribute14,
202 attribute15 = X_Attribute15
203 where hold_id = X_hold_id;
204 -- and k_header_id = X_k_header_id;
205 -- and k_line_id = X_k_line_id;
206
207 if NVL(X_Hold_Status_Code, '@' ) <> NVL(X_prev_hold_status_code, '@' ) then
208 OKE_HOLD_UTILS.Status_Change
209 ( X_Hold_ID
210 , X_K_Header_ID
211 , X_K_Line_ID
212 , X_k_Deliverable_ID
213 , X_Hold_Type_Code
214 , X_Hold_Reason_Code
215 , X_Remove_Reason_Code
216 , X_prev_hold_status_code
217 , X_Hold_Status_Code
218 , X_Last_Updated_By
219 , X_Last_Update_Date
220 , X_Last_Update_Login
221 );
222 end if;
223
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228
229 end Update_Row;
230
231
232 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
233 X_hold_id NUMBER,
234 X_k_header_id NUMBER,
235 X_k_line_id NUMBER,
236 X_k_deliverable_id NUMBER,
237 X_apply_date DATE,
238 X_schedule_remove_date DATE,
239 X_hold_type_code VARCHAR2,
240 X_hold_reason_code VARCHAR2,
241 X_hold_status_code VARCHAR2,
242 X_hold_comment VARCHAR2,
243 X_wf_item_type VARCHAR2,
244 X_wf_process VARCHAR2,
245 X_Last_Update_Date DATE,
246 X_Last_Updated_By NUMBER,
247 X_Creation_Date DATE,
248 X_Created_By NUMBER,
249 X_Last_Update_Login NUMBER
250 ) is
251
252 cursor C is
253 select rowid
254 from OKE_K_HOLDS
255 where hold_id = X_hold_id;
256
257 begin
258
259 insert into OKE_K_HOLDS(
260 hold_id,
261 k_header_id,
262 k_line_id,
263 deliverable_id,
264 apply_date,
265 schedule_remove_date,
266 hold_type_code,
267 hold_reason_code,
268 hold_status_code,
269 hold_comment,
270 wf_item_type,
271 wf_process,
272 last_update_date,
273 last_updated_by,
274 creation_date,
275 created_by,
276 last_update_login
277 ) VALUES (
278 X_hold_id,
279 X_k_header_id,
280 X_k_line_id,
281 X_k_deliverable_id,
282 X_apply_date,
283 X_schedule_remove_date,
284 X_hold_type_code,
285 X_hold_reason_code,
286 X_hold_status_code,
287 X_hold_comment,
288 X_wf_item_type,
289 X_wf_process,
290 X_Last_Update_Date,
291 X_Last_Updated_By,
292 X_Creation_Date,
293 X_Created_By,
294 X_Last_Update_Login
295 );
296
297 OKE_HOLD_UTILS.Status_Change
298 ( X_Hold_ID
299 , X_K_Header_ID
300 , X_K_Line_ID
301 , X_k_Deliverable_ID
302 , X_Hold_Type_Code
303 , X_Hold_Reason_Code
304 , null
305 , null
306 , X_Hold_Status_Code
307 , X_Last_Updated_By
308 , X_Last_Update_Date
309 , X_Last_Update_Login
310 );
311
312 open c;
313 fetch c into X_Rowid;
314 if (c%notfound) then
315 close c;
316 raise no_data_found;
317 end if;
318 close c;
319
320 end Insert_Row;
321
322
323 end OKE_APPLY_REMOVE_HOLDS_PKG;