[Home] [Help]
PACKAGE BODY: APPS.GMD_ROUTING_CLASS_PKG
Source
1 package body GMD_ROUTING_CLASS_PKG as
2 /* $Header: GMDROCMB.pls 120.0 2005/05/25 18:43:00 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_ROUTING_CLASS in VARCHAR2,
6 X_TEXT_CODE in NUMBER,
7 X_TRANS_CNT in NUMBER,
8 X_DELETE_MARK in NUMBER,
9 X_ROUTING_CLASS_UOM in VARCHAR2,
10 X_ROUTING_CLASS_DESC in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from GMD_ROUTING_CLASS_B
18 where ROUTING_CLASS = X_ROUTING_CLASS
19 ;
20 begin
21 insert into GMD_ROUTING_CLASS_B (
22 ROUTING_CLASS,
23 TEXT_CODE,
24 TRANS_CNT,
25 DELETE_MARK,
26 ROUTING_CLASS_UOM,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_ROUTING_CLASS,
34 X_TEXT_CODE,
35 X_TRANS_CNT,
36 X_DELETE_MARK,
37 X_ROUTING_CLASS_UOM,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_LAST_UPDATE_LOGIN
43 );
44
45 insert into GMD_ROUTING_CLASS_TL (
46 ROUTING_CLASS,
47 ROUTING_CLASS_DESC,
48 LAST_UPDATE_DATE,
49 CREATED_BY,
50 LAST_UPDATED_BY,
51 CREATION_DATE,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_ROUTING_CLASS,
57 X_ROUTING_CLASS_DESC,
58 X_LAST_UPDATE_DATE,
59 X_CREATED_BY,
60 X_LAST_UPDATED_BY,
61 X_CREATION_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from GMD_ROUTING_CLASS_TL T
70 where T.ROUTING_CLASS = X_ROUTING_CLASS
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_ROUTING_CLASS in VARCHAR2,
85 X_TEXT_CODE in NUMBER,
86 X_TRANS_CNT in NUMBER,
87 X_DELETE_MARK in NUMBER,
88 X_ROUTING_CLASS_UOM in VARCHAR2,
89 X_ROUTING_CLASS_DESC in VARCHAR2
90 ) is
91 cursor c is select
92 TEXT_CODE,
93 TRANS_CNT,
94 DELETE_MARK,
95 ROUTING_CLASS_UOM
96 from GMD_ROUTING_CLASS_B
97 where ROUTING_CLASS = X_ROUTING_CLASS
98 for update of ROUTING_CLASS nowait;
99 recinfo c%rowtype;
100
101 cursor c1 is select
102 ROUTING_CLASS_DESC,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from GMD_ROUTING_CLASS_TL
105 where ROUTING_CLASS = X_ROUTING_CLASS
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of ROUTING_CLASS nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( ((recinfo.TEXT_CODE = X_TEXT_CODE)
118 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
119 AND ((recinfo.TRANS_CNT = X_TRANS_CNT)
120 OR ((recinfo.TRANS_CNT is null) AND (X_TRANS_CNT is null)))
121 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
122 AND ((recinfo.ROUTING_CLASS_UOM = X_ROUTING_CLASS_UOM)
123 OR ((recinfo.ROUTING_CLASS_UOM is null) AND (X_ROUTING_CLASS_UOM is null)))
124 ) then
125 null;
126 else
127 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128 app_exception.raise_exception;
129 end if;
130
131 for tlinfo in c1 loop
132 if (tlinfo.BASELANG = 'Y') then
133 if ( (tlinfo.ROUTING_CLASS_DESC = X_ROUTING_CLASS_DESC)
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140 end if;
141 end loop;
142 return;
143 end LOCK_ROW;
144
145 procedure UPDATE_ROW (
146 X_ROUTING_CLASS in VARCHAR2,
147 X_TEXT_CODE in NUMBER,
148 X_TRANS_CNT in NUMBER,
149 X_DELETE_MARK in NUMBER,
150 X_ROUTING_CLASS_UOM in VARCHAR2,
151 X_ROUTING_CLASS_DESC in VARCHAR2,
152 X_LAST_UPDATE_DATE in DATE,
153 X_LAST_UPDATED_BY in NUMBER,
154 X_LAST_UPDATE_LOGIN in NUMBER
155 ) is
156 begin
157 update GMD_ROUTING_CLASS_B set
158 TEXT_CODE = X_TEXT_CODE,
159 TRANS_CNT = X_TRANS_CNT,
160 DELETE_MARK = X_DELETE_MARK,
161 ROUTING_CLASS_UOM = X_ROUTING_CLASS_UOM,
162 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
165 where ROUTING_CLASS = X_ROUTING_CLASS;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170
171 update GMD_ROUTING_CLASS_TL set
172 ROUTING_CLASS_DESC = X_ROUTING_CLASS_DESC,
173 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
174 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
176 SOURCE_LANG = userenv('LANG')
177 where ROUTING_CLASS = X_ROUTING_CLASS
178 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183 end UPDATE_ROW;
184
185 procedure DELETE_ROW (
186 X_ROUTING_CLASS in VARCHAR2
187 ) is
188 begin
189
190 UPDATE GMD_ROUTING_CLASS_B
191 SET delete_mark = 1
192 where ROUTING_CLASS = X_ROUTING_CLASS;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end DELETE_ROW;
198
199 procedure ADD_LANGUAGE
200 is
201 begin
202 delete from GMD_ROUTING_CLASS_TL T
203 where not exists
204 (select NULL
205 from GMD_ROUTING_CLASS_B B
206 where B.ROUTING_CLASS = T.ROUTING_CLASS
207 );
208
209 update GMD_ROUTING_CLASS_TL T set (
210 ROUTING_CLASS_DESC
211 ) = (select
212 B.ROUTING_CLASS_DESC
213 from GMD_ROUTING_CLASS_TL B
214 where B.ROUTING_CLASS = T.ROUTING_CLASS
215 and B.LANGUAGE = T.SOURCE_LANG)
216 where (
217 T.ROUTING_CLASS,
218 T.LANGUAGE
219 ) in (select
220 SUBT.ROUTING_CLASS,
221 SUBT.LANGUAGE
222 from GMD_ROUTING_CLASS_TL SUBB, GMD_ROUTING_CLASS_TL SUBT
223 where SUBB.ROUTING_CLASS = SUBT.ROUTING_CLASS
224 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
225 and (SUBB.ROUTING_CLASS_DESC <> SUBT.ROUTING_CLASS_DESC
226 ));
227
228 insert into GMD_ROUTING_CLASS_TL (
229 ROUTING_CLASS,
230 ROUTING_CLASS_DESC,
231 LAST_UPDATE_DATE,
232 CREATED_BY,
233 LAST_UPDATED_BY,
234 CREATION_DATE,
235 LAST_UPDATE_LOGIN,
236 LANGUAGE,
237 SOURCE_LANG
238 ) select
239 B.ROUTING_CLASS,
240 B.ROUTING_CLASS_DESC,
241 B.LAST_UPDATE_DATE,
242 B.CREATED_BY,
243 B.LAST_UPDATED_BY,
244 B.CREATION_DATE,
245 B.LAST_UPDATE_LOGIN,
246 L.LANGUAGE_CODE,
247 B.SOURCE_LANG
248 from GMD_ROUTING_CLASS_TL B, FND_LANGUAGES L
249 where L.INSTALLED_FLAG in ('I', 'B')
250 and B.LANGUAGE = userenv('LANG')
251 and not exists
252 (select NULL
253 from GMD_ROUTING_CLASS_TL T
254 where T.ROUTING_CLASS = B.ROUTING_CLASS
255 and T.LANGUAGE = L.LANGUAGE_CODE);
256 end ADD_LANGUAGE;
257
258 end GMD_ROUTING_CLASS_PKG;