[Home] [Help]
PACKAGE BODY: APPS.HZ_GEO_STRUCT_MAP_DTL_PVT
Source
1 PACKAGE BODY HZ_GEO_STRUCT_MAP_DTL_PVT AS
2 /*$Header: ARHGEMDB.pls 120.2 2005/09/01 20:02:05 baianand noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_map_id IN NUMBER,
7 x_loc_seq_num IN NUMBER,
8 x_loc_component IN VARCHAR2,
9 x_geography_type IN VARCHAR2,
10 x_geo_element_col IN VARCHAR2
11 ) IS
12
13
14 BEGIN
15
16 INSERT INTO HZ_GEO_STRUCT_MAP_DTL (
17 map_id,
18 loc_seq_num,
19 loc_component,
20 geography_type,
21 geo_element_col,
22 last_updated_by,
23 creation_date,
24 created_by,
25 last_update_date,
26 last_update_login
27 )
28 VALUES (
29 DECODE(x_map_id,
30 FND_API.G_MISS_NUM, NULL,
31 x_map_id),
32 DECODE(x_loc_seq_num,
33 FND_API.G_MISS_NUM, NULL,
34 x_loc_seq_num),
35 DECODE(x_loc_component,
36 FND_API.G_MISS_CHAR, NULL,
37 x_loc_component),
38 DECODE(x_geography_type,
39 FND_API.G_MISS_CHAR, NULL,
40 x_geography_type),
41 DECODE(x_geo_element_col,
42 FND_API.G_MISS_CHAR, NULL,
43 x_geo_element_col),
44 hz_utility_v2pub.last_updated_by,
45 hz_utility_v2pub.creation_date,
46 hz_utility_v2pub.created_by,
47 hz_utility_v2pub.last_update_date,
48 hz_utility_v2pub.last_update_login
49 ) RETURNING
50 rowid
51 INTO
52 x_rowid;
53
54 END Insert_Row;
55
56 PROCEDURE Update_Row (
57 x_rowid IN OUT NOCOPY VARCHAR2,
58 x_map_id IN NUMBER,
59 x_loc_seq_num IN NUMBER,
60 x_loc_component IN VARCHAR2,
61 x_geography_type IN VARCHAR2,
62 x_geo_element_col IN VARCHAR2
63 ) IS
64 BEGIN
65
66 UPDATE HZ_GEO_STRUCT_MAP_DTL
67 SET
68 map_id =
69 DECODE(x_map_id,
70 NULL, map_id,
71 FND_API.G_MISS_NUM, NULL,
72 x_map_id),
73 loc_seq_num =
74 DECODE(x_loc_seq_num,
75 NULL, loc_seq_num,
76 FND_API.G_MISS_NUM, NULL,
77 x_loc_seq_num),
78 loc_component =
79 DECODE(x_loc_component,
80 NULL, loc_component,
81 FND_API.G_MISS_CHAR, NULL,
82 x_loc_component),
83 geography_type =
84 DECODE(x_geography_type,
85 NULL, geography_type,
86 FND_API.G_MISS_CHAR, NULL,
87 x_geography_type),
88 geo_element_col =
89 DECODE(x_geo_element_col,
90 NULL, geo_element_col,
91 FND_API.G_MISS_CHAR, NULL,
92 x_geo_element_col),
93 last_updated_by = hz_utility_v2pub.last_updated_by,
94 creation_date = creation_date,
95 created_by = created_by,
96 last_update_date = hz_utility_v2pub.last_update_date,
97 last_update_login = hz_utility_v2pub.last_update_login
98 WHERE rowid = x_rowid;
99 IF ( SQL%NOTFOUND ) THEN
100 RAISE NO_DATA_FOUND;
101 END IF;
102
103 END Update_Row;
104
105 PROCEDURE Lock_Row (
106 x_rowid IN OUT NOCOPY VARCHAR2,
107 x_map_id IN NUMBER,
108 x_loc_seq_num IN NUMBER,
109 x_loc_component IN VARCHAR2,
110 x_geography_type IN VARCHAR2,
111 x_geo_element_col IN VARCHAR2
112 ) IS
113
114 CURSOR c IS
115 SELECT * FROM hz_geo_struct_map_dtl
116 WHERE rowid = x_rowid
117 FOR UPDATE NOWAIT;
118 Recinfo c%ROWTYPE;
119
120 BEGIN
121
122 OPEN c;
123 FETCH c INTO Recinfo;
124 IF ( c%NOTFOUND ) THEN
125 CLOSE c;
126 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END IF;
129 CLOSE C;
130
131 IF (
132 ( ( Recinfo.map_id = x_map_id )
133 OR ( ( Recinfo.map_id IS NULL )
134 AND ( x_map_id IS NULL ) ) )
135 AND ( ( Recinfo.loc_seq_num = x_loc_seq_num)
136 OR ( ( Recinfo.loc_seq_num IS NULL )
137 AND ( x_loc_seq_num IS NULL ) ) )
138 AND ( ( Recinfo.loc_component = x_loc_component )
139 OR ( ( Recinfo.loc_component IS NULL )
140 AND ( x_loc_component IS NULL ) ) )
141 AND ( ( Recinfo.geography_type = x_geography_type )
142 OR ( ( Recinfo.geography_type IS NULL )
143 AND ( x_geography_type IS NULL ) ) )
144 ) THEN
145 RETURN;
146 ELSE
147 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END IF;
150
151 return;
152
153 END Lock_Row;
154
155 PROCEDURE Select_Row (
156 x_map_id IN OUT NOCOPY NUMBER,
157 x_loc_seq_num OUT NOCOPY NUMBER,
158 x_loc_component OUT NOCOPY VARCHAR2,
159 x_geography_type OUT NOCOPY VARCHAR2,
160 x_geo_element_col OUT NOCOPY VARCHAR2
161 ) IS
162 BEGIN
163
164 SELECT
165 NVL(map_id, FND_API.G_MISS_NUM),
166 NVL(loc_seq_num, FND_API.G_MISS_NUM),
167 NVL(loc_component, FND_API.G_MISS_CHAR),
168 NVL(geography_type, FND_API.G_MISS_CHAR)
169 INTO
170 x_map_id,
171 x_loc_seq_num,
172 x_loc_component,
173 x_geography_type
174 FROM HZ_GEO_STRUCT_MAP_DTL
175 WHERE map_id = x_map_id;
176
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
180 FND_MESSAGE.SET_TOKEN('RECORD', 'geo structure map dtl');
181 FND_MESSAGE.SET_TOKEN('VALUE', x_map_id);
182 FND_MSG_PUB.ADD;
183 RAISE FND_API.G_EXC_ERROR;
184
185 END Select_Row;
186
187 PROCEDURE Delete_Row (
188 x_map_id IN NUMBER
189 ) IS
190 BEGIN
191
192 DELETE FROM hz_geo_struct_map_dtl
193 WHERE map_id = x_map_id;
194
195 IF (SQL%NOTFOUND) THEN
196 RAISE NO_DATA_FOUND;
197 END IF;
198
199 END Delete_Row;
200
201 -- This API can use to delete only one record in mapping detail table.
202 PROCEDURE Delete_Row (
203 x_map_id IN NUMBER,
204 x_geography_type IN VARCHAR2
205 ) IS
206 BEGIN
207
208 DELETE FROM hz_geo_struct_map_dtl
209 WHERE map_id = x_map_id
210 AND geography_type = x_geography_type;
211
212 IF (SQL%NOTFOUND) THEN
213 RAISE NO_DATA_FOUND;
214 END IF;
215
216 END Delete_Row;
217
218 END HZ_GEO_STRUCT_MAP_DTL_PVT;