[Home] [Help]
PACKAGE BODY: APPS.MTL_MVT_STATS_RULES_PKG
Source
1 PACKAGE BODY MTL_MVT_STATS_RULES_PKG AS
2 -- $Header: INVGMVRB.pls 115.2 2002/12/03 21:48:40 vma ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVGMVRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to create procedure for inserting row, updating |
13 --| row, locking row and deleting row on tables MTL_MVT_STATS_RULES |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Insert_Row |
17 --| PROCEDURE Update_Row |
18 --| PROCEDURE Lock_Row |
19 --| PROCEDURE Delete_Row |
20 --| HISTORY |
21 --| 07/14/00 ksaini Created |
22 --| 09/18/00 ksaini Delete_Row procedure corrected |
23 --| 11/22/02 vma Added NOCOPY to x_rowid of Insert_Row to |
24 --| comply with new PL/SQL standard for better |
25 --| performance |
26 --| |
27 --+======================================================================*/
28
29 --==================
30 --CONSTANTS
31 --==================
32 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_MVT_STATS_RULES_PKG';
33
34 --==================
35 --PUBLIC PROCEDURE
36 --==================
37 --========================================================================
38 --PRECEDURE : Insert_Row Public
39 --PARAMETERS: see below
40 --COMMENT : table handler for inserting data to table mtl_mvt_stats_rules
41 -- assignments
42 --========================================================================
43 PROCEDURE Insert_Row
44 ( x_rowid IN OUT NOCOPY VARCHAR2
45 , p_rule_set_code IN VARCHAR2
46 , p_rule_number IN NUMBER
47 , p_source_type IN VARCHAR2
48 , p_attribute_code IN VARCHAR2
49 , p_attribute_property_code IN VARCHAR2
50 , p_attribute_lookup_type IN VARCHAR2
51 , p_commodity_code IN VARCHAR2
52 , p_creation_date IN DATE
53 , p_created_by IN NUMBER
54 , p_last_update_date IN DATE
55 , p_last_updated_by IN NUMBER
56 , p_last_update_login IN NUMBER
57 )
58 IS
59 CURSOR c IS
60 SELECT
61 rowid
62 FROM
63 mtl_mvt_stats_rules
64 WHERE rule_set_code = p_rule_set_code
65 AND rule_number = p_rule_number;
66
67 BEGIN
68 INSERT INTO mtl_mvt_stats_rules
69 ( rule_set_code
70 , rule_number
71 , source_type
72 , attribute_code
73 , attribute_property_code
74 , attribute_lookup_type
75 , commodity_code
76 , creation_date
77 , created_by
78 , last_update_date
79 , last_updated_by
80 , last_update_login
81 )
82 VALUES
83 ( p_rule_set_code
84 , p_rule_number
85 , p_source_type
86 , p_attribute_code
87 , p_attribute_property_code
88 , p_attribute_lookup_type
89 , p_commodity_code
90 , p_creation_date
91 , p_created_by
92 , p_last_update_date
93 , p_last_updated_by
94 , p_last_update_login
95 );
96
97 OPEN c;
98 FETCH c into x_rowid;
99 IF (c%NOTFOUND)
100 THEN
101 CLOSE c;
102 RAISE no_data_found;
103 END IF;
104 CLOSE c;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
109 THEN
110 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Insert_Row');
111 END IF;
112 RAISE;
113
114 END Insert_Row;
115
116 --========================================================================
117 --PRECEDURE : Lock_Row Public
118 --PARAMETERS: see below
119 --COMMENT : table handler for locking table mtl_mvt_stats_rules
120 --EXCEPTION : record_changed
121 --========================================================================
122 PROCEDURE Lock_Row
123 ( p_rowid IN VARCHAR2
124 , p_rule_set_code IN VARCHAR2
125 , p_rule_number IN NUMBER
126 , p_source_type IN VARCHAR2
127 , p_attribute_code IN VARCHAR2
128 , p_attribute_property_code IN VARCHAR2
129 , p_attribute_lookup_type IN VARCHAR2
130 , p_commodity_code IN VARCHAR2
131 )
132 IS
133 cursor c IS
134 SELECT *
135 FROM
136 mtl_mvt_stats_rules
137 WHERE
138 rowid = p_rowid
139 FOR UPDATE OF rule_set_code nowait;
140 recinfo c%ROWTYPE;
141 record_changed EXCEPTION;
142
143 BEGIN
144 OPEN c;
145 FETCH c INTO recinfo;
146 IF (c%NOTFOUND)
147 THEN
148 CLOSE c;
149 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
150 APP_EXCEPTION.raise_exception;
151 END IF;
152 CLOSE c;
153
154 -- check that mandatory and non-mandatory columns match values in form
155 IF NOT( (recinfo.rule_set_code = p_rule_set_code)
156 AND(recinfo.rule_number = p_rule_number)
157 AND(recinfo.attribute_code = p_attribute_code)
158 AND((recinfo.source_type = p_source_type )
159 OR((recinfo.source_type IS NULL)
160 AND(p_source_type IS NULL)))
161 AND((recinfo.attribute_lookup_type = p_attribute_lookup_type )
162 OR((recinfo.attribute_lookup_type IS NULL)
163 AND(p_attribute_lookup_type IS NULL)))
164 AND((recinfo.attribute_property_code = p_attribute_property_code )
165 OR((recinfo.attribute_property_code IS NULL)
166 AND(p_attribute_property_code IS NULL)))
167 AND((recinfo.commodity_code = p_commodity_code )
168 OR((recinfo.commodity_code IS NULL)
169 AND(p_commodity_code IS NULL))))
170 THEN
171 RAISE record_changed;
172 END IF;
173
174 EXCEPTION
175 WHEN record_changed THEN
176 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
177 APP_EXCEPTION.raise_exception;
178 WHEN OTHERS THEN
179 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
180 THEN
181 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Lock_Row');
182 END IF;
183 RAISE;
184
185 END Lock_Row;
186
187 --========================================================================
188 --PRECEDURE : Update_Row Public
189 --PARAMETERS: see below
190 --COMMENT : table handler for updating data of table MTL_MVT_STATS_RULES
191 --========================================================================
192 PROCEDURE Update_Row
193 ( p_rowid IN VARCHAR2
194 , p_rule_set_code IN VARCHAR2
195 , p_rule_number IN NUMBER
196 , p_source_type IN VARCHAR2
197 , p_attribute_code IN VARCHAR2
198 , p_attribute_property_code IN VARCHAR2
199 , p_attribute_lookup_type IN VARCHAR2
200 , p_commodity_code IN VARCHAR2
201 , p_last_update_date IN DATE
202 , p_last_updated_by IN NUMBER
203 , p_last_update_login IN NUMBER
204 )
205 IS
206 BEGIN
207 UPDATE mtl_mvt_stats_rules
208 SET rule_set_code = p_rule_set_code
209 , rule_number = p_rule_number
210 , source_type = p_source_type
211 , attribute_code = p_attribute_code
212 , attribute_property_code = p_attribute_property_code
213 , attribute_lookup_type = p_attribute_lookup_type
214 , commodity_code = p_commodity_code
215 , last_update_date = p_last_update_date
216 , last_updated_by = p_last_updated_by
217 , last_update_login = p_last_update_login
218 WHERE rowid = p_rowid;
219 IF (SQL%NOTFOUND)
220 THEN
221 RAISE no_data_found;
222 END IF;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
227 THEN
228 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Update_Row');
229 END IF;
230 RAISE;
231
232 END Update_row;
233
234 --========================================================================
235 --PRECEDURE : Delete_Row Public
236 --PARAMETERS: see below
237 --COMMENT : table handler for deleting data from table MTL_MVT_STATS_RULES
238 --========================================================================
239 PROCEDURE Delete_row
240 ( p_rule_set_code IN VARCHAR2
241 )
242 IS
243 BEGIN
244 DELETE FROM
245 mtl_mvt_stats_rules
246 WHERE
247 rule_set_code = p_rule_set_code;
248 IF (SQL%NOTFOUND)
249 THEN
250 RAISE no_data_found;
251 END IF;
252
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 NULL;
256 WHEN OTHERS THEN
257 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
258 THEN
259 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Delete_Row'||substr(sqlerrm,1,100));
260 END IF;
261 RAISE;
262
263 END Delete_row;
264
265 END MTL_MVT_STATS_RULES_PKG;