1 PACKAGE BODY OE_PC_VTMPLT_COLS_PKG AS
2 /* $Header: OEXPCVCB.pls 120.0 2005/06/01 01:34:50 appldev noship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_rowid in out NOCOPY /* file.sql.39 change */ varchar2
7 ,x_validation_tmplt_col_id in out NOCOPY /* file.sql.39 change */ number
8 ,x_validation_tmplt_id in number
9 ,x_column_name in varchar2
10 ,x_validation_op in varchar2
11 ,x_value_string in varchar2
12 ,x_created_by in number
13 ,x_creation_date in date
14 ,x_last_updated_by in number
15 ,x_last_update_date in date
16 ,x_last_update_login in number
17 )
18 Is
19 CURSOR C IS SELECT oe_pc_vtmplt_cols_s.nextval FROM dual;
20 CURSOR CROWID IS SELECT rowid FROM oe_pc_vtmplt_cols
21 WHERE validation_tmplt_id = x_validation_tmplt_id
22 AND column_name = x_column_name;
23
24 Begin
25
26 OPEN C;
27 FETCH C INTO x_validation_tmplt_col_id;
28 if (C%NOTFOUND) then
29 CLOSE C;
30 RAISE NO_DATA_FOUND;
31 end if;
32 CLOSE C;
33
34 INSERT INTO oe_pc_vtmplt_cols (
35 validation_tmplt_col_id
36 ,validation_tmplt_id
37 ,column_name
38 ,validation_op
39 ,value_string
40 ,created_by
41 ,creation_date
42 ,last_updated_by
43 ,last_update_date
44 ,last_update_login
45 )
46 Values
47 (
48 x_validation_tmplt_col_id
49 ,x_validation_tmplt_id
50 ,x_column_name
51 ,x_validation_op
52 ,x_value_string
53 ,x_created_by
54 ,x_creation_date
55 ,x_last_updated_by
56 ,x_last_update_date
57 ,x_last_update_login
58 );
59
60 Open CROWID;
61 Fetch CROWID into x_rowid;
62 if (CROWID%NOTFOUND) then
63 CLOSE CROWID;
64 RAISE NO_DATA_FOUND;
65 end if;
66 CLOSE CROWID;
67
68 UPDATE oe_pc_vtmplts
69 SET
70 last_updated_by = x_last_updated_by
71 ,last_update_date = x_last_update_date
72 ,last_update_login = x_last_update_login
73 WHERE validation_tmplt_id = x_validation_tmplt_id;
74
75 End Insert_Row;
76
77
78 ------------------------------------------
79 PROCEDURE Lock_Row(
80 x_rowid in varchar2
81 ,x_validation_tmplt_col_id in number
82 ,x_validation_tmplt_id in number
83 ,x_column_name in varchar2
84 ,x_validation_op in varchar2
85 ,x_value_string in varchar2
86 ,x_created_by in number
87 ,x_creation_date in date
88 ,x_last_updated_by in number
89 ,x_last_update_date in date
90 ,x_last_update_login in number
91 )
92 Is
93 CURSOR C IS
94 SELECT *
95 FROM oe_pc_vtmplt_cols
96 WHERE rowid = x_rowid
97 FOR UPDATE OF value_string NOWAIT;
98
99 Recinfo C%ROWTYPE;
100 Begin
101 Open C;
102 Fetch C into Recinfo;
103 if (C%NOTFOUND) then
104 Close C;
105 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
106 APP_EXCEPTION.Raise_Exception;
107 end if;
108 Close C;
109 if (
110 (Recinfo.validation_tmplt_col_id = x_validation_tmplt_col_id)
111 AND (Recinfo.validation_tmplt_id = x_validation_tmplt_id)
112 AND (rtrim(Recinfo.column_name) = x_column_name)
113 AND (rtrim(Recinfo.validation_op) = x_validation_op)
114 AND (Recinfo.created_by = x_created_by)
115 AND (Recinfo.creation_date = x_creation_date)
116 AND (Recinfo.last_updated_by = x_last_updated_by)
117 AND (Recinfo.last_update_date = x_last_update_date)
118 AND ( (Recinfo.last_update_login = x_last_update_login)
119 OR ( (recinfo.last_update_login IS NULL)
120 AND(x_last_update_login IS NULL)))
121 AND ( (Recinfo.value_string = x_value_string)
122 OR ( (recinfo.value_string IS NULL)
123 AND(x_value_string IS NULL)))
124 ) then
125 return;
126 else
127 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
128 APP_EXCEPTION.Raise_Exception;
129 end if;
130 End Lock_Row;
131
132 --------------------------------------------------------------------
133 PROCEDURE Update_Row(
134 x_rowid in varchar2
135 ,x_validation_tmplt_col_id in number
136 ,x_validation_tmplt_id in number
137 ,x_column_name in varchar2
138 ,x_validation_op in varchar2
139 ,x_value_string in varchar2
140 ,x_created_by in number
141 ,x_creation_date in date
142 ,x_last_updated_by in number
143 ,x_last_update_date in date
144 ,x_last_update_login in number
145 )
146 Is
147 Begin
148
149 UPDATE oe_pc_vtmplt_cols
150 SET
151 column_name = x_column_name
152 ,validation_op = x_validation_op
153 ,value_string = x_value_string
154 ,last_updated_by = x_last_updated_by
155 ,last_update_date = x_last_update_date
156 ,last_update_login = x_last_update_login
157 WHERE rowid = x_rowid;
158
159 UPDATE oe_pc_vtmplts
160 SET
161 last_updated_by = x_last_updated_by
162 ,last_update_date = x_last_update_date
163 ,last_update_login = x_last_update_login
164 WHERE validation_tmplt_id = x_validation_tmplt_id;
165
166 End Update_Row;
167
168
169 -------------------------------------------------
170 PROCEDURE Delete_Row(
171 x_rowid in varchar2
172 )
173 Is
174 Begin
175
176 DELETE FROM OE_PC_VTMPLT_COLS
177 WHERE rowid = x_rowid;
178 if (SQL%NOTFOUND) then
179 RAISE NO_DATA_FOUND;
180 end if;
181
182 End Delete_Row;
183
184
185 -------------------------------------------------------------------
186 -- Added 2 parameters to fix #2050546 to include column_name,validation_op and
187 -- value_string to check for the unique columns
188
189 PROCEDURE Check_Unique(
190 x_rowid in varchar2
191 ,x_validation_tmplt_id in number
192 ,x_column_name in varchar2
193 ,x_validation_op in varchar2
194 ,x_value_string in varchar2
195 )
196 Is
197 dummy number;
198 Begin
199
200 -- onely one column for a validation tmplt
201 SELECT count(1)
202 INTO dummy
203 FROM oe_pc_vtmplt_cols
204 WHERE validation_tmplt_id = x_validation_tmplt_id
205 AND column_name = x_column_name
206 AND validation_op = x_validation_op
207 AND value_string = x_value_string
208 AND ((x_rowid IS null) OR (rowid <> x_rowid));
209
210 if (dummy >= 1) then
211 fnd_message.set_name('ONT', 'OE_PC_VT_DUP_COLUMN_NAME');
212 app_exception.raise_exception;
213 end if;
214 End Check_Unique;
215 ------------------------------------------------------------------------
216
217 END OE_PC_VTMPLT_COLS_PKG;
[Home] [Help]