eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HZ_STYLE_FMT_LOCALES_PKG

Source


1 package body HZ_STYLE_FMT_LOCALES_PKG as
2 /* $Header: ARHPSFLB.pls 115.8 2004/02/25 23:15:15 geliu noship $ */
3 
4 L_USER_ID_FOR_SEED NUMBER := NULL;
5 
6 procedure INSERT_ROW (
7   X_ROWID in out NOCOPY VARCHAR2,
8   X_STYLE_FMT_LOCALE_ID in out NOCOPY NUMBER,
9   X_STYLE_FORMAT_CODE in VARCHAR2,
10   X_LANGUAGE_CODE in VARCHAR2,
11   X_TERRITORY_CODE in VARCHAR2,
12   X_START_DATE_ACTIVE in DATE,
13   X_END_DATE_ACTIVE   in DATE,
14   X_OBJECT_VERSION_NUMBER in NUMBER
15 ) is
16   cursor C is select ROWID from HZ_STYLE_FMT_LOCALES
17     where STYLE_FMT_LOCALE_ID = X_STYLE_FMT_LOCALE_ID
18     ;
19 begin
20   insert into HZ_STYLE_FMT_LOCALES (
21     STYLE_FMT_LOCALE_ID,
22     STYLE_FORMAT_CODE,
23     LANGUAGE_CODE,
24     TERRITORY_CODE,
25     START_DATE_ACTIVE,
26     END_DATE_ACTIVE,
27     CREATED_BY,
28     CREATION_DATE,
29     LAST_UPDATE_LOGIN,
30     LAST_UPDATE_DATE,
31     LAST_UPDATED_BY,
32     OBJECT_VERSION_NUMBER
33   ) values (
34     DECODE( X_STYLE_FMT_LOCALE_ID, FND_API.G_MISS_NUM, HZ_STYLE_FMT_LOCALES_S.NEXTVAL, NULL, HZ_STYLE_FMT_LOCALES_S.NEXTVAL, X_STYLE_FMT_LOCALE_ID ),
35     DECODE( X_STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_FORMAT_CODE ),
36     DECODE( X_LANGUAGE_CODE, FND_API.G_MISS_CHAR, NULL, X_LANGUAGE_CODE ),
37     DECODE( X_TERRITORY_CODE, FND_API.G_MISS_CHAR, NULL, X_TERRITORY_CODE ),
38     DECODE( X_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_START_DATE_ACTIVE ),
39     DECODE( X_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_END_DATE_ACTIVE ),
40     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.CREATED_BY),
41     HZ_UTILITY_V2PUB.CREATION_DATE,
42     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
43     HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
44     NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
45     DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
46   ) RETURNING
47     STYLE_FMT_LOCALE_ID
48   INTO
49     X_STYLE_FMT_LOCALE_ID;
50 
51   open c;
52   fetch c into X_ROWID;
53   if (c%notfound) then
54     close c;
55     raise no_data_found;
56   end if;
57   close c;
58 
59 end INSERT_ROW;
60 
61 procedure LOCK_ROW (
62   X_STYLE_FMT_LOCALE_ID in NUMBER,
63   X_STYLE_FORMAT_CODE in VARCHAR2,
64   X_LANGUAGE_CODE in VARCHAR2,
65   X_TERRITORY_CODE in VARCHAR2
66 ) is
67   cursor c is select
68       STYLE_FORMAT_CODE,
69       LANGUAGE_CODE,
70       TERRITORY_CODE
71     from HZ_STYLE_FMT_LOCALES
72     where STYLE_FMT_LOCALE_ID = X_STYLE_FMT_LOCALE_ID
73     for update of LANGUAGE_CODE nowait;
74   recinfo c%rowtype;
75 
76 begin
77   open c;
78   fetch c into recinfo;
79   if (c%notfound) then
80     close c;
81     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
82     app_exception.raise_exception;
83   end if;
84   close c;
85   if (    (recinfo.STYLE_FORMAT_CODE = X_STYLE_FORMAT_CODE)
86       AND (recinfo.LANGUAGE_CODE = X_LANGUAGE_CODE)
87       AND (recinfo.TERRITORY_CODE = X_TERRITORY_CODE)
88   ) then
89     null;
90   else
91     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
92     app_exception.raise_exception;
93   end if;
94 
95   return;
96 end LOCK_ROW;
97 
98 procedure UPDATE_ROW (
99   X_STYLE_FMT_LOCALE_ID in NUMBER,
100   X_STYLE_FORMAT_CODE in VARCHAR2,
101   X_LANGUAGE_CODE in VARCHAR2,
102   X_TERRITORY_CODE in VARCHAR2,
103   X_START_DATE_ACTIVE in DATE,
104   X_END_DATE_ACTIVE   in DATE,
105   X_OBJECT_VERSION_NUMBER in NUMBER
106 ) is
107 begin
108 
109   update HZ_STYLE_FMT_LOCALES set
110     STYLE_FORMAT_CODE = DECODE( X_STYLE_FORMAT_CODE, NULL, STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR, NULL, X_STYLE_FORMAT_CODE ),
111     LANGUAGE_CODE = DECODE( X_LANGUAGE_CODE, NULL, LANGUAGE_CODE, FND_API.G_MISS_CHAR, NULL, X_LANGUAGE_CODE ),
112     TERRITORY_CODE = DECODE( X_TERRITORY_CODE, NULL, TERRITORY_CODE, FND_API.G_MISS_CHAR, NULL, X_TERRITORY_CODE ),
113     START_DATE_ACTIVE = DECODE( X_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_START_DATE_ACTIVE ),
114     END_DATE_ACTIVE = DECODE( X_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_END_DATE_ACTIVE ),
115     LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
116     LAST_UPDATED_BY = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATED_BY),
117     LAST_UPDATE_LOGIN = NVL(L_USER_ID_FOR_SEED,HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN),
118     OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER,
119                                   FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER)
120   where STYLE_FMT_LOCALE_ID = X_STYLE_FMT_LOCALE_ID;
121 
122   if (sql%notfound) then
123     raise no_data_found;
124   end if;
125 
126 end UPDATE_ROW;
127 
128 PROCEDURE SELECT_ROW (
129   X_STYLE_FMT_LOCALE_ID		IN OUT NOCOPY NUMBER,
130   X_STYLE_FORMAT_CODE		OUT    NOCOPY VARCHAR2,
131   X_LANGUAGE_CODE 		OUT    NOCOPY VARCHAR2,
132   X_TERRITORY_CODE		OUT    NOCOPY VARCHAR2,
133   X_START_DATE_ACTIVE		OUT    NOCOPY DATE,
134   X_END_DATE_ACTIVE		OUT    NOCOPY DATE
135 ) IS
136 BEGIN
137     SELECT
138         NVL( STYLE_FMT_LOCALE_ID, FND_API.G_MISS_NUM ),
139         NVL( STYLE_FORMAT_CODE, FND_API.G_MISS_CHAR ),
140         NVL( LANGUAGE_CODE, FND_API.G_MISS_CHAR ),
141         NVL( TERRITORY_CODE, FND_API.G_MISS_CHAR ),
142         NVL( START_DATE_ACTIVE, FND_API.G_MISS_DATE ),
143         NVL( END_DATE_ACTIVE, FND_API.G_MISS_DATE )
144     INTO X_STYLE_FMT_LOCALE_ID,
145          X_STYLE_FORMAT_CODE,
146 	 X_LANGUAGE_CODE,
147 	 X_TERRITORY_CODE,
148          X_START_DATE_ACTIVE,
149 	 X_END_DATE_ACTIVE
150     FROM HZ_STYLE_FMT_LOCALES
151     WHERE STYLE_FMT_LOCALE_ID = X_STYLE_FMT_LOCALE_ID ;
152 
153 EXCEPTION
154     WHEN NO_DATA_FOUND THEN
155         FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
156         FND_MESSAGE.SET_TOKEN( 'RECORD', 'style_fmt_locale_rec');
157         FND_MESSAGE.SET_TOKEN( 'VALUE', 'STYLE_FMT_LOCALE_ID' );
158         FND_MSG_PUB.ADD;
159         RAISE FND_API.G_EXC_ERROR;
160 
161 END SELECT_ROW;
162 
163 
164 
165 procedure DELETE_ROW (
166   X_STYLE_FMT_LOCALE_ID in NUMBER
167 ) is
168 begin
169   delete from HZ_STYLE_FMT_LOCALES
170   where STYLE_FMT_LOCALE_ID = X_STYLE_FMT_LOCALE_ID;
171 
172   if (sql%notfound) then
173     raise no_data_found;
174   end if;
175 end DELETE_ROW;
176 
177 procedure ADD_LANGUAGE
178 is
179 begin
180   null;
181 end ADD_LANGUAGE;
182 
183 procedure LOAD_ROW (
184   X_STYLE_FORMAT_CODE in VARCHAR2,
185   X_LANGUAGE_CODE in VARCHAR2,
186   X_TERRITORY_CODE in VARCHAR2,
187   X_START_DATE_ACTIVE in DATE,
188   X_END_DATE_ACTIVE   in DATE,
189   X_OWNER in VARCHAR2,  -- "SEED" or "CUSTOM"
190   X_LAST_UPDATE_DATE in DATE,
191   X_CUSTOM_MODE in VARCHAR2
192 ) is
193   l_id        number;
194   l_f_luby    number;  -- entity owner in file
195   l_f_ludate  date;    -- entity update date in file
196   l_db_luby   number;  -- entity owner in db
197   l_db_ludate date;    -- entity update date in db
198   l_rowid     varchar2(64);
199   l_object_version_number number;
200 begin
201 
202   -- Translate owner to file_last_updated_by
203   if (x_owner = 'SEED') then
204     l_f_luby := 1;
205     L_USER_ID_FOR_SEED := 1;
206   else
207     l_f_luby := 0;
208   end if;
209 
210   -- Get last update date of ldt entity
211   l_f_ludate := nvl(x_last_update_date, sysdate);
212 
213   begin
214 
215     select STYLE_FMT_LOCALE_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE,  OBJECT_VERSION_NUMBER
216          into l_id, l_db_luby, l_db_ludate, l_object_version_number
217          from HZ_STYLE_FMT_LOCALES
218          where STYLE_FORMAT_CODE = x_style_format_code
219            and nvl(LANGUAGE_CODE,'XXXX') = nvl(x_language_code,'XXXX')
220            and nvl(TERRITORY_CODE,'XX') = nvl(x_territory_code,'XX');
221 
222     -- Update record, honoring customization mode.
223     -- Record should be updated only if:
224     -- a. CUSTOM_MODE = FORCE, or
225     -- b. file owner is CUSTOM, db owner is SEED
226     -- c. owners are the same, and file_date > db_date
227 
228     if ((x_custom_mode = 'FORCE') or
229        ((l_f_luby = 0) and (l_db_luby = 1)) or
230        ((l_f_luby = l_db_luby) and (l_f_ludate > l_db_ludate)))
231     then
232       hz_style_fmt_locales_pkg.update_row (
233         X_STYLE_FMT_LOCALE_ID  => l_id,
234         X_STYLE_FORMAT_CODE    => X_STYLE_FORMAT_CODE,
235         X_LANGUAGE_CODE        => X_LANGUAGE_CODE,
236         X_TERRITORY_CODE       => X_TERRITORY_CODE,
237 	X_START_DATE_ACTIVE    => X_START_DATE_ACTIVE,
238 	X_END_DATE_ACTIVE      => X_END_DATE_ACTIVE,
239 	X_OBJECT_VERSION_NUMBER => l_object_version_number
240       );
241     end if;
242 
243   exception
244     when no_data_found then
245       -- record not found, insert in all cases
246       hz_style_fmt_locales_pkg.insert_row(
247           x_rowid                => l_rowid,
248           x_style_fmt_locale_id  => l_id, -- will be generated
249           x_style_format_code    => X_STYLE_FORMAT_CODE,
250           x_language_code        => X_LANGUAGE_CODE,
251           x_territory_code       => X_TERRITORY_CODE,
252           x_start_date_active    => X_START_DATE_ACTIVE,
253 	  x_end_date_active      => X_END_DATE_ACTIVE,
254 	  x_object_version_number => 1
255       );
256   end;
257 
258 end LOAD_ROW;
259 
260 end HZ_STYLE_FMT_LOCALES_PKG;