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_VARIATIONS_PKG

Source


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