[Home] [Help]
PACKAGE BODY: APPS.QASPSET_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY QASPSET_TABLE_HANDLER_PKG as
2 /* $Header: qaspsetb.plb 115.3 2002/11/27 19:20:26 jezheng ship $ */
3
4 PROCEDURE insert_plan_header_row(
5 X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Sampling_Plan_id IN OUT NOCOPY NUMBER,
7 X_Sampling_Plan_Code VARCHAR2,
8 X_Description VARCHAR2,
9 X_Insp_Level_Code VARCHAR2,
10 X_Sampling_Std_Code NUMBER,
11 X_AQL NUMBER,
12 X_Org_id NUMBER,
13 X_Last_Update_Date DATE,
14 X_Last_Updated_By NUMBER,
15 X_Creation_Date DATE,
16 X_Created_By NUMBER,
17 X_Last_Update_Login NUMBER
18 ) IS
19
20 CURSOR C1 IS SELECT rowid FROM qa_sampling_plans
21 WHERE sampling_plan_id = X_Sampling_Plan_id;
22
23 CURSOR C2 IS SELECT qa_sampling_plan_s.nextval FROM dual;
24
25 BEGIN
26
27 if (X_Sampling_Plan_id is NULL) then
28 OPEN C2;
29 FETCH C2 INTO X_Sampling_Plan_id;
30 CLOSE C2;
31 end if;
32
33 INSERT INTO QA_SAMPLING_PLANS(
34 sampling_plan_id,
35 sampling_plan_code,
36 description,
37 insp_level_code,
38 sampling_std_code,
39 aql,
40 organization_id,
41 last_update_date,
42 last_updated_by,
43 creation_date,
44 created_by,
45 last_update_login
46 )
47 values(
48 X_Sampling_Plan_id,
49 X_Sampling_Plan_Code,
50 X_Description,
51 X_Insp_Level_Code,
52 X_Sampling_Std_Code,
53 X_AQL,
54 X_Org_id,
55 X_Last_Update_Date,
56 X_Last_Updated_By,
57 X_Creation_Date,
58 X_Created_By,
59 X_Last_Update_Login
60 );
61
62 --commit;
63
64 OPEN C1;
65 FETCH C1 INTO X_Rowid;
66 if (C1%NOTFOUND) then
67 CLOSE C1;
68 Raise NO_DATA_FOUND;
69 end if;
70 CLOSE C1;
71
72 END insert_plan_header_row;
73
74
75
76 PROCEDURE insert_customized_rules_row(
77 X_Rowid IN OUT NOCOPY VARCHAR2,
78 X_Rule_id IN OUT NOCOPY NUMBER,
79 X_Sampling_Plan_id NUMBER,
80 X_Min_Lot_Size NUMBER,
81 X_Max_Lot_Size NUMBER,
82 X_Sample_Size NUMBER,
83 X_Last_Update_Date DATE,
84 X_Last_Updated_By NUMBER,
85 X_Creation_Date DATE,
86 X_Created_By NUMBER,
87 X_Last_Update_Login NUMBER
88 ) IS
89
90 CURSOR C1 IS SELECT rowid FROM qa_sampling_custom_rules
91 WHERE rule_id = X_Rule_id;
92
93 CURSOR C2 IS SELECT qa_sampling_rules_s.nextval FROM dual;
94
95 BEGIN
96 null;
97
98 if (X_Rule_id is NULL) then
99 OPEN C2;
100 FETCH C2 INTO X_Rule_id;
101 CLOSE C2;
102 end if;
103
104 INSERT INTO QA_SAMPLING_CUSTOM_RULES(
105 rule_id,
106 sampling_plan_id,
107 min_lot_size,
108 max_lot_size,
109 sample_size,
110 last_update_date,
111 last_updated_by,
112 creation_date,
113 created_by,
114 last_update_login
115 )
116 values(
117 X_Rule_id,
118 X_Sampling_Plan_id,
119 X_Min_Lot_Size,
120 X_Max_Lot_Size,
121 X_Sample_Size,
122 X_Last_Update_Date,
123 X_Last_Updated_By,
124 X_Creation_Date,
125 X_Created_By,
126 X_Last_Update_Login
127 );
128
129 --commit;
130
131
132 OPEN C1;
133 FETCH C1 INTO X_Rowid;
134 if (C1%NOTFOUND) then
135 CLOSE C1;
136 Raise NO_DATA_FOUND;
137 end if;
138 CLOSE C1;
139
140 END insert_customized_rules_row;
141
142
143
144
145 PROCEDURE update_plan_header_row(
146 X_Rowid VARCHAR2,
147 X_Sampling_Plan_id NUMBER,
148 X_Sampling_Plan_Code VARCHAR2,
149 X_Description VARCHAR2,
150 X_Insp_Level_Code VARCHAR2,
151 X_Sampling_Std_Code NUMBER,
152 X_AQL NUMBER,
153 X_Org_id NUMBER,
154 X_Last_Update_Date DATE,
155 X_Last_Updated_By NUMBER,
156 X_Creation_Date DATE,
157 X_Created_By NUMBER,
158 X_Last_Update_Login NUMBER
159 ) IS
160
161 BEGIN
162
163 UPDATE QA_SAMPLING_PLANS
164 SET
165 sampling_plan_id = X_Sampling_Plan_id,
166 sampling_plan_code = X_Sampling_Plan_Code,
167 description = X_Description,
168 insp_level_code = X_Insp_Level_Code,
169 sampling_std_code = X_Sampling_Std_Code,
170 aql = X_AQL,
171 organization_id = X_Org_id,
172 last_update_date = X_Last_Update_Date,
173 last_updated_by = X_Last_Updated_By,
174 creation_date = X_Creation_Date,
175 created_by = X_Created_By,
176 last_update_login = X_Last_Update_Login
177 WHERE rowid = X_Rowid;
178
179 --commit;
180
181 if (SQL%NOTFOUND) then
182 Raise NO_DATA_FOUND;
183 end if;
184
185 END update_plan_header_row;
186
187
188
189
190 PROCEDURE update_customized_rules_row(
191 X_Rowid VARCHAR2,
192 X_Rule_id NUMBER,
193 X_Sampling_Plan_id NUMBER,
194 X_Min_Lot_Size NUMBER,
195 X_Max_Lot_Size NUMBER,
196 X_Sample_Size NUMBER,
197 X_Last_Update_Date DATE,
198 X_Last_Updated_By NUMBER,
199 X_Creation_Date DATE,
200 X_Created_By NUMBER,
201 X_Last_Update_Login NUMBER
202 ) IS
203
204 BEGIN
205
206 UPDATE QA_SAMPLING_CUSTOM_RULES
207 SET
208 rule_id = X_Rule_id,
209 sampling_plan_id = X_Sampling_Plan_id,
210 min_lot_size = X_Min_Lot_Size,
211 max_lot_size = X_Max_Lot_Size,
212 sample_size = X_Sample_Size,
213 last_update_date = X_Last_Update_Date,
214 last_updated_by = X_Last_Updated_By,
215 creation_date = X_Creation_Date,
216 created_by = X_Created_By,
217 last_update_login = X_Last_Update_Login
218 WHERE rowid = X_Rowid;
219
220 --commit;
221
222 if (SQL%NOTFOUND) then
223 Raise NO_DATA_FOUND;
224 end if;
225
226 END update_customized_rules_row;
227
228
229
230
231
232 PROCEDURE lock_plan_header_row(
233 X_Rowid VARCHAR2,
234 X_Sampling_Plan_id NUMBER,
235 X_Sampling_Plan_Code VARCHAR2,
236 X_Description VARCHAR2,
237 X_Insp_Level_Code VARCHAR2,
238 X_Sampling_Std_Code NUMBER,
239 X_AQL NUMBER,
240 X_Org_id NUMBER,
241 X_Last_Update_Date DATE,
242 X_Last_Updated_By NUMBER,
243 X_Creation_Date DATE,
244 X_Created_By NUMBER,
245 X_Last_Update_Login NUMBER
246 ) IS
247
248 CURSOR C IS
249 SELECT *
250 FROM QA_SAMPLING_PLANS
251 WHERE rowid = X_Rowid
252 FOR UPDATE of sampling_plan_id NOWAIT;
253
254 Recinfo C%ROWTYPE;
255
256
257 BEGIN
258
259 OPEN C;
260 FETCH C INTO Recinfo;
261
262 if (C%NOTFOUND) then
263 CLOSE C;
264 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
265 APP_EXCEPTION.Raise_Exception;
266 end if;
267
268 CLOSE C;
269
270 if (
271 (Recinfo.sampling_plan_id = X_Sampling_Plan_id)
272 AND (Recinfo.sampling_plan_code = X_Sampling_Plan_Code)
273 AND ( (Recinfo.description = X_Description)
274 OR ( (Recinfo.description IS NULL)
275 AND (X_Description IS NULL)))
276 AND ( (Recinfo.insp_level_code = X_Insp_Level_Code)
277 OR ( (Recinfo.insp_level_code IS NULL)
278 AND (X_Insp_Level_Code IS NULL)))
279 AND (Recinfo.sampling_std_code = X_Sampling_Std_Code)
280 AND ( (Recinfo.aql = X_AQL)
281 OR ( (Recinfo.aql IS NULL)
282 AND (X_AQL IS NULL)))
283 AND (Recinfo.organization_id = X_Org_id)
284 AND (Recinfo.last_update_date = X_Last_Update_Date)
285 AND (Recinfo.last_updated_by = X_Last_Updated_By)
286 AND (Recinfo.creation_date = X_Creation_Date)
287 AND (Recinfo.created_by = X_Created_By)
288 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
289 OR ( (Recinfo.last_update_login IS NULL)
290 AND (X_Last_Update_Login IS NULL)))
291
292 ) then
293 null;
294 return;
295
296 else
297 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
298 APP_EXCEPTION.Raise_Exception;
299
300 end if;
301
302 END lock_plan_header_row;
303
304
305
306
307 PROCEDURE lock_customized_rules_row(
308 X_Rowid VARCHAR2,
309 X_Rule_id NUMBER,
310 X_Sampling_Plan_id NUMBER,
311 X_Min_Lot_Size NUMBER,
312 X_Max_Lot_Size NUMBER,
313 X_Sample_Size NUMBER,
314 X_Last_Update_Date DATE,
315 X_Last_Updated_By NUMBER,
316 X_Creation_Date DATE,
317 X_Created_By NUMBER,
318 X_Last_Update_Login NUMBER
319 ) IS
320
321 CURSOR C IS
322 SELECT *
323 FROM QA_SAMPLING_CUSTOM_RULES
324 WHERE rowid = X_Rowid
325 FOR UPDATE of rule_id NOWAIT;
326
327 Recinfo C%ROWTYPE;
328
329 BEGIN
330
331 OPEN C;
332 FETCH C INTO Recinfo;
333
334 if (C%NOTFOUND) then
335 CLOSE C;
336 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
337 APP_EXCEPTION.Raise_Exception;
338 end if;
339
340 CLOSE C;
341
342 if (
343 (Recinfo.rule_id = X_Rule_id)
344 AND (Recinfo.sampling_plan_id = X_Sampling_Plan_id)
345 AND (Recinfo.min_lot_size = X_Min_Lot_Size)
346 AND ( (Recinfo.max_lot_size = X_Max_Lot_Size)
347 OR ( (Recinfo.max_lot_size IS NULL)
348 AND (X_Max_Lot_Size IS NULL)))
349 AND (Recinfo.sample_size = X_Sample_Size)
350 AND (Recinfo.last_update_date = X_Last_Update_Date)
351 AND (Recinfo.last_updated_by = X_Last_Updated_By)
352 AND (Recinfo.creation_date = X_Creation_Date)
353 AND (Recinfo.created_by = X_Created_By)
354 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
355 OR ( (Recinfo.last_update_login IS NULL)
356 AND (X_Last_Update_Login IS NULL)))
357
358 ) then
359
360 return;
361
362 else
363 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
364 APP_EXCEPTION.Raise_Exception;
365
366 end if;
367
368 END lock_customized_rules_row;
369
370
371
372
373
374 PROCEDURE delete_plan_header_row(X_Rowid VARCHAR2) IS
375
376 BEGIN
377 delete from QA_SAMPLING_PLANS
378 where rowid = X_Rowid;
379
380 if (SQL%NOTFOUND) then
381 Raise NO_DATA_FOUND;
382 end if;
383
384 END delete_plan_header_row;
385
386
387
388
389 PROCEDURE delete_customized_rules_row(X_Rowid VARCHAR2) IS
390
391 BEGIN
392 delete from QA_SAMPLING_CUSTOM_RULES
393 where rowid = X_Rowid;
394
395 if (SQL%NOTFOUND) then
396 Raise NO_DATA_FOUND;
397 end if;
398
399 END delete_customized_rules_row;
400
401
402
403
404
405
406 END QASPSET_TABLE_HANDLER_PKG;