DBA Data[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;