1 package body BSC_RESPONSIBILITY_PKG as
2 /* $Header: BSCRESPB.pls 115.6 2003/02/12 14:29:09 adeulgao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_RESPONSIBILITY_ID in NUMBER,
7 X_RESPONSIBILITY_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 cursor C is select ROWID from BSC_RESPONSIBILITY_TL
16 where APPLICATION_ID = X_APPLICATION_ID
17 and RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
18 and LANGUAGE = userenv('LANG')
19 ;
20 begin
21 insert into BSC_RESPONSIBILITY_TL (
22 APPLICATION_ID,
23 RESPONSIBILITY_ID,
24 RESPONSIBILITY_NAME,
25 CREATED_BY,
26 CREATION_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATE_LOGIN,
30 DESCRIPTION,
31 LANGUAGE,
32 SOURCE_LANG
33 ) select
34 X_APPLICATION_ID,
35 X_RESPONSIBILITY_ID,
36 X_RESPONSIBILITY_NAME,
37 X_CREATED_BY,
38 X_CREATION_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATE_LOGIN,
42 X_DESCRIPTION,
43 L.LANGUAGE_CODE,
44 userenv('LANG')
45 from FND_LANGUAGES L
46 where L.INSTALLED_FLAG in ('I', 'B')
47 and not exists
48 (select NULL
49 from BSC_RESPONSIBILITY_TL T
50 where T.APPLICATION_ID = X_APPLICATION_ID
51 and T.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
52 and T.LANGUAGE = L.LANGUAGE_CODE);
53
54 open c;
55 fetch c into X_ROWID;
56 if (c%notfound) then
57 close c;
58 raise no_data_found;
59 end if;
60 close c;
61
62 end INSERT_ROW;
63
64 procedure LOCK_ROW (
65 X_APPLICATION_ID in NUMBER,
66 X_RESPONSIBILITY_ID in NUMBER,
67 X_RESPONSIBILITY_NAME in VARCHAR2,
68 X_DESCRIPTION in VARCHAR2
69 ) is
70 cursor c1 is select
71 RESPONSIBILITY_NAME,
72 DESCRIPTION,
73 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
74 from BSC_RESPONSIBILITY_TL
75 where APPLICATION_ID = X_APPLICATION_ID
76 and RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
77 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
78 for update of APPLICATION_ID nowait;
79 begin
80 for tlinfo in c1 loop
81 if (tlinfo.BASELANG = 'Y') then
82 if ( (tlinfo.RESPONSIBILITY_NAME = X_RESPONSIBILITY_NAME)
83 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
84 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
85 ) then
86 null;
87 else
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 end if;
91 end if;
92 end loop;
93 return;
94 end LOCK_ROW;
95
96 procedure UPDATE_ROW (
97 X_APPLICATION_ID in NUMBER,
98 X_RESPONSIBILITY_ID in NUMBER,
99 X_RESPONSIBILITY_NAME in VARCHAR2,
100 X_DESCRIPTION in VARCHAR2,
101 X_LAST_UPDATE_DATE in DATE,
102 X_LAST_UPDATED_BY in NUMBER,
103 X_LAST_UPDATE_LOGIN in NUMBER
104 ) is
105 begin
106 update BSC_RESPONSIBILITY_TL set
107 RESPONSIBILITY_NAME = X_RESPONSIBILITY_NAME,
108 DESCRIPTION = X_DESCRIPTION,
109 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
110 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
112 SOURCE_LANG = userenv('LANG')
113 where APPLICATION_ID = X_APPLICATION_ID
114 and RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
115 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
116
117 if (sql%notfound) then
118 raise no_data_found;
119 end if;
120 end UPDATE_ROW;
121
122 procedure DELETE_ROW (
123 X_APPLICATION_ID in NUMBER,
124 X_RESPONSIBILITY_ID in NUMBER
125 ) is
126 begin
127 delete from BSC_RESPONSIBILITY_TL
128 where APPLICATION_ID = X_APPLICATION_ID
129 and RESPONSIBILITY_ID = X_RESPONSIBILITY_ID;
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134
135 end DELETE_ROW;
136
137 procedure ADD_LANGUAGE
138 is
139 begin
140 update BSC_RESPONSIBILITY_TL T set (
141 RESPONSIBILITY_NAME,
142 DESCRIPTION
143 ) = (select
144 B.RESPONSIBILITY_NAME,
145 B.DESCRIPTION
146 from BSC_RESPONSIBILITY_TL B
147 where B.APPLICATION_ID = T.APPLICATION_ID
148 and B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
149 and B.LANGUAGE = T.SOURCE_LANG)
150 where (
151 T.APPLICATION_ID,
152 T.RESPONSIBILITY_ID,
153 T.LANGUAGE
154 ) in (select
155 SUBT.APPLICATION_ID,
156 SUBT.RESPONSIBILITY_ID,
157 SUBT.LANGUAGE
158 from BSC_RESPONSIBILITY_TL SUBB, BSC_RESPONSIBILITY_TL SUBT
159 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
160 and SUBB.RESPONSIBILITY_ID = SUBT.RESPONSIBILITY_ID
161 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
162 and (SUBB.RESPONSIBILITY_NAME <> SUBT.RESPONSIBILITY_NAME
163 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
164 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
165 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
166 ));
167
168 insert into BSC_RESPONSIBILITY_TL (
169 APPLICATION_ID,
170 RESPONSIBILITY_ID,
171 RESPONSIBILITY_NAME,
172 CREATED_BY,
173 CREATION_DATE,
174 LAST_UPDATED_BY,
175 LAST_UPDATE_DATE,
176 LAST_UPDATE_LOGIN,
177 DESCRIPTION,
178 LANGUAGE,
179 SOURCE_LANG
180 ) select
181 B.APPLICATION_ID,
182 B.RESPONSIBILITY_ID,
183 B.RESPONSIBILITY_NAME,
184 B.CREATED_BY,
185 B.CREATION_DATE,
186 B.LAST_UPDATED_BY,
187 B.LAST_UPDATE_DATE,
188 B.LAST_UPDATE_LOGIN,
189 B.DESCRIPTION,
190 L.LANGUAGE_CODE,
191 B.SOURCE_LANG
192 from BSC_RESPONSIBILITY_TL B, FND_LANGUAGES L
193 where L.INSTALLED_FLAG in ('I', 'B')
194 and B.LANGUAGE = userenv('LANG')
195 and not exists
196 (select NULL
197 from BSC_RESPONSIBILITY_TL T
198 where T.APPLICATION_ID = B.APPLICATION_ID
199 and T.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
200 and T.LANGUAGE = L.LANGUAGE_CODE);
201 end ADD_LANGUAGE;
202
203 end BSC_RESPONSIBILITY_PKG;
[Home] [Help]