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