[Home] [Help]
PACKAGE BODY: APPS.IEC_HZ_PHONE_AREA_CODES_PKG
Source
1 package body IEC_HZ_PHONE_AREA_CODES_PKG as
2 /* $Header: IECHZACB.pls 120.1 2005/07/20 06:26:09 appldev noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_TERRITORY_CODE in VARCHAR2,
7 X_PHONE_COUNTRY_CODE in VARCHAR2,
8 X_AREA_CODE in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_CREATED_BY in NUMBER,
11 X_CREATION_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATE_LOGIN in NUMBER,
15 X_OBJECT_VERSION_NUMBER in NUMBER
16 ) is
17 cursor C is select ROWID
18 from HZ_PHONE_AREA_CODES
19 where
20 TERRITORY_CODE = X_TERRITORY_CODE
21 and AREA_CODE = X_AREA_CODE;
22
23 begin
24
25 insert into HZ_PHONE_AREA_CODES
26 (TERRITORY_CODE,
27 PHONE_COUNTRY_CODE,
28 AREA_CODE,
29 DESCRIPTION,
30 CREATED_BY,
31 CREATION_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_DATE,
34 LAST_UPDATE_LOGIN,
35 OBJECT_VERSION_NUMBER)
36 values
37 (X_TERRITORY_CODE,
38 X_PHONE_COUNTRY_CODE,
39 X_AREA_CODE,
40 X_DESCRIPTION,
41 X_CREATED_BY,
42 X_CREATION_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATE_LOGIN,
46 X_OBJECT_VERSION_NUMBER);
47
48 open c;
49 fetch c into X_ROWID;
50 if (c%notfound) then
51 close c;
52 raise no_data_found;
53 end if;
54 close c;
55
56 end INSERT_ROW;
57
58 procedure LOCK_ROW (
59 X_TERRITORY_CODE in VARCHAR2,
60 X_PHONE_COUNTRY_CODE in VARCHAR2,
61 X_AREA_CODE in VARCHAR2,
62 X_DESCRIPTION in VARCHAR2,
63 X_OBJECT_VERSION_NUMBER in NUMBER
64 ) is
65 cursor c is select
66 PHONE_COUNTRY_CODE,
67 DESCRIPTION,
68 OBJECT_VERSION_NUMBER
69 from HZ_PHONE_AREA_CODES
70 where TERRITORY_CODE = X_TERRITORY_CODE
71 and AREA_CODE = X_AREA_CODE
72 for update of PHONE_COUNTRY_CODE, DESCRIPTION, OBJECT_VERSION_NUMBER nowait;
73 recinfo c%rowtype;
74 begin
75 open c;
76 fetch c into recinfo;
77 if (c%notfound) then
78 close c;
79 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
80 app_exception.raise_exception;
81 end if;
82 close c;
83 if ( (recinfo.PHONE_COUNTRY_CODE = X_PHONE_COUNTRY_CODE)
84 AND (recinfo.DESCRIPTION = X_DESCRIPTION)
85 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
86 ) then
87 null;
88 else
89 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
90 app_exception.raise_exception;
91 end if;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_TERRITORY_CODE in VARCHAR2,
97 X_PHONE_COUNTRY_CODE in VARCHAR2,
98 X_AREA_CODE in VARCHAR2,
99 X_DESCRIPTION in VARCHAR2,
100 X_LAST_UPDATED_BY in NUMBER,
101 X_LAST_UPDATE_DATE in DATE,
102 X_LAST_UPDATE_LOGIN in NUMBER,
103 X_OBJECT_VERSION_NUMBER in NUMBER
104 ) is
105 begin
106
107 update HZ_PHONE_AREA_CODES
108 set PHONE_COUNTRY_CODE = X_PHONE_COUNTRY_CODE,
109 DESCRIPTION = X_DESCRIPTION,
110 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
111 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
112 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
113 where TERRITORY_CODE = X_TERRITORY_CODE
114 and AREA_CODE = X_AREA_CODE;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119
120 end UPDATE_ROW;
121
122 procedure DELETE_ROW (
123 X_TERRITORY_CODE in VARCHAR2,
124 X_AREA_CODE in VARCHAR2
125 ) is
126 begin
127 delete from HZ_PHONE_AREA_CODES
128 where TERRITORY_CODE = X_TERRITORY_CODE
129 and AREA_CODE = X_AREA_CODE;
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134 end DELETE_ROW;
135
136 procedure LOAD_ROW (
137 X_TERRITORY_CODE in VARCHAR2,
138 X_PHONE_COUNTRY_CODE in VARCHAR2,
139 X_AREA_CODE in VARCHAR2,
140 X_DESCRIPTION in VARCHAR2,
141 X_OWNER in VARCHAR2
142 ) is
143 USER_ID NUMBER := 0;
144 ROW_ID VARCHAR2(500);
145 begin
146
147 USER_ID := fnd_load_util.owner_id(x_owner);
148
149 UPDATE_ROW (X_TERRITORY_CODE, X_PHONE_COUNTRY_CODE, X_AREA_CODE, X_DESCRIPTION, USER_ID, SYSDATE, USER_ID, 0);
150
151 exception
152 when no_data_found then
153 INSERT_ROW (ROW_ID, X_TERRITORY_CODE, X_PHONE_COUNTRY_CODE, X_AREA_CODE, X_DESCRIPTION, USER_ID, SYSDATE, USER_ID, SYSDATE, USER_ID, 0);
154
155 end LOAD_ROW;
156
157 procedure LOAD_SEED_ROW (
158 X_upload_mode in VARCHAR2,
159 X_TERRITORY_CODE in VARCHAR2,
160 X_PHONE_COUNTRY_CODE in VARCHAR2,
161 X_AREA_CODE in VARCHAR2,
162 X_DESCRIPTION in VARCHAR2,
163 X_OWNER in VARCHAR2
164 ) is
165 begin
166
167 if (X_upload_mode = 'NLS') then
168 IEC_HZ_PHONE_AREA_CODES_PKG.TRANSLATE_ROW(
169 X_TERRITORY_CODE,
170 X_PHONE_COUNTRY_CODE,
171 X_AREA_CODE,
172 X_DESCRIPTION,
173 X_OWNER);
174 else
175 IEC_HZ_PHONE_AREA_CODES_PKG.LOAD_ROW(
176 X_TERRITORY_CODE,
177 X_PHONE_COUNTRY_CODE,
178 X_AREA_CODE,
179 X_DESCRIPTION,
180 X_OWNER);
181 end if;
182 end LOAD_SEED_ROW;
183
184 procedure TRANSLATE_ROW (
185 X_TERRITORY_CODE in VARCHAR2,
186 X_PHONE_COUNTRY_CODE in VARCHAR2,
187 X_AREA_CODE in VARCHAR2,
188 X_DESCRIPTION in VARCHAR2,
189 X_OWNER in VARCHAR2
190 ) is
191 begin
192
193 update HZ_PHONE_AREA_CODES
194 set PHONE_COUNTRY_CODE = X_PHONE_COUNTRY_CODE,
195 DESCRIPTION = X_DESCRIPTION,
196 LAST_UPDATE_DATE = SYSDATE,
197 LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
198 LAST_UPDATE_LOGIN = 0
199 where TERRITORY_CODE = X_TERRITORY_CODE
200 and AREA_CODE = X_AREA_CODE
201 and userenv('LANG') = ( select LANGUAGE_CODE from FND_LANGUAGES
202 where INSTALLED_FLAG = 'B' );
203
204 end TRANSLATE_ROW;
205
206
207 end IEC_HZ_PHONE_AREA_CODES_PKG;