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