[Home] [Help]
PACKAGE BODY: APPS.QA_PC_CRITERIA_PKG
Source
1 PACKAGE BODY QA_PC_CRITERIA_PKG as
2 /* $Header: qapccrib.pls 120.2 2005/12/19 04:12:54 srhariha noship $ */
3 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2,
4 X_Criteria_Id IN OUT NOCOPY NUMBER,
5 X_Plan_Relationship_Id NUMBER,
6 X_Char_Id NUMBER,
7 X_Operator NUMBER,
8 X_Low_Value VARCHAR2,
9 X_Low_Value_Id NUMBER,
10 X_High_Value VARCHAR2,
11 X_High_Value_Id NUMBER,
12 X_Last_Update_Date DATE,
13 X_Last_Updated_By NUMBER,
14 X_Creation_Date DATE,
15 X_Created_By NUMBER,
16 X_Last_Update_Login NUMBER
17 ) IS
18 CURSOR C IS SELECT rowid FROM QA_PC_CRITERIA
19 WHERE criteria_id = X_Criteria_Id;
20 CURSOR C2 IS SELECT qa_pc_criteria_s.nextval FROM dual;
21 BEGIN
22 if (X_Criteria_Id is NULL) then
23 OPEN C2;
24 FETCH C2 INTO X_Criteria_Id;
25 CLOSE C2;
26 end if;
27
28 INSERT INTO QA_PC_CRITERIA(
29 criteria_id,
30 plan_relationship_id,
31 char_id,
32 operator,
33 low_value,
34 low_value_id,
35 high_value,
36 high_value_id,
37 last_update_date,
38 last_updated_by,
39 creation_date,
40 created_by,
41 last_update_login
42 )VALUES (
43 X_Criteria_Id,
44 X_Plan_Relationship_Id,
45 X_Char_Id,
46 X_Operator,
47 X_Low_Value,
48 X_Low_Value_Id,
49 X_High_Value,
50 X_High_Value_Id ,
51 X_Last_Update_Date,
52 X_Last_Updated_By ,
53 X_Creation_Date,
54 X_Created_By ,
55 X_Last_Update_Login
56 );
57 OPEN C;
58 FETCH C INTO X_Rowid;
59 if (C%NOTFOUND) then
60 CLOSE C;
61 Raise NO_DATA_FOUND;
62 end if;
63 CLOSE C;
64 END Insert_Row;
65
66 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
67 X_Criteria_Id NUMBER,
68 X_Plan_Relationship_Id NUMBER,
69 X_Char_Id NUMBER,
70 X_Operator NUMBER,
71 X_Low_Value VARCHAR2,
72 X_Low_Value_Id NUMBER,
73 X_High_Value VARCHAR2,
74 X_High_Value_Id NUMBER,
75 X_Last_Update_Date DATE,
76 X_Last_Updated_By NUMBER,
77 X_Creation_Date DATE,
78 X_Created_By NUMBER
79
80 ) IS
81 CURSOR C IS
82 SELECT *
83 FROM QA_PC_CRITERIA
84 WHERE rowid = X_Rowid
85 FOR UPDATE of Criteria_Id NOWAIT;
86 Recinfo C%ROWTYPE;
87
88 BEGIN
89 OPEN C;
90 FETCH C INTO Recinfo;
91 if (C%NOTFOUND) then
92 CLOSE C;
93 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
94 APP_EXCEPTION.Raise_Exception;
95 end if;
96 CLOSE C;
97 if (
98 (Recinfo.criteria_id = X_Criteria_Id)
99 AND (Recinfo.plan_relationship_id = X_Plan_Relationship_Id)
100 AND (Recinfo.char_id = X_Char_Id)
101 AND (Recinfo.operator = X_Operator)
102 AND ((Recinfo.low_value = X_Low_Value)
103 OR ((Recinfo.low_value IS NULL )
104 AND (X_Low_Value IS NULL)))
105 AND ((Recinfo.low_value_id = X_Low_Value_Id)
106 OR ((Recinfo.low_value_id IS NULL )
107 AND (X_Low_Value_Id IS NULL)))
108 AND ((Recinfo.high_value = X_High_Value)
109 OR ((Recinfo.high_value IS NULL )
110 AND (X_High_Value IS NULL)))
111 AND ((Recinfo.high_value_id = X_High_Value_Id)
112 OR ((Recinfo.high_value_id IS NULL )
113 AND (X_High_Value_Id IS NULL)))
114 AND (Recinfo.last_update_date = X_Last_Update_Date)
115 AND (Recinfo.last_updated_by = X_Last_Updated_By)
116 AND (Recinfo.creation_date = X_Creation_Date)
117 AND (Recinfo.created_by = X_Created_By)
118 ) then
119 return;
120 else
121 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
122 APP_EXCEPTION.Raise_Exception;
123 end if;
124 END Lock_Row;
125
126
127 PROCEDURE Update_Row( X_Rowid VARCHAR2,
128 X_Criteria_Id NUMBER,
129 X_Plan_Relationship_Id NUMBER,
130 X_Char_Id NUMBER,
131 X_Operator NUMBER,
132 X_Low_Value VARCHAR2,
133 X_Low_Value_Id NUMBER,
134 X_High_Value VARCHAR2,
135 X_High_Value_Id NUMBER,
136 X_Last_Update_Date DATE,
137 X_Last_Updated_By NUMBER,
138 X_Creation_Date DATE,
139 X_Created_By NUMBER,
140 X_Last_Update_Login NUMBER
141 ) IS
142 BEGIN
143 UPDATE QA_PC_CRITERIA
144 SET
145 criteria_id = X_Criteria_Id ,
146 plan_relationship_id = X_Plan_Relationship_Id ,
147 char_id = X_Char_Id,
148 operator = X_Operator,
149 low_value = X_Low_Value,
150 low_value_id = X_Low_Value_Id,
151 high_value = X_High_Value,
152 high_value_id = X_High_Value_Id,
153 last_update_date = X_Last_Update_Date,
154 last_updated_by = X_Last_Updated_By,
155 creation_date = X_Creation_Date,
156 created_by = X_Created_By,
157 last_update_login = X_Last_Update_Login
158 WHERE rowid = X_Rowid;
159
160 if (SQL%NOTFOUND) then
161 Raise NO_DATA_FOUND;
162 end if;
163 END Update_Row;
164
165 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
166 BEGIN
167 DELETE FROM QA_PC_CRITERIA
168 WHERE rowid = X_Rowid;
169
170 if (SQL%NOTFOUND) then
171 Raise NO_DATA_FOUND;
172 end if;
173 END Delete_Row;
174
175 END QA_PC_CRITERIA_PKG;