[Home] [Help]
PACKAGE BODY: APPS.FPA_SCORECARDS_PKG
Source
1 package body FPA_SCORECARDS_PKG as
2 /* $Header: FPASSCRB.pls 120.3 2005/09/29 13:59:49 ashariff noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PROJECT_ID in NUMBER,
6 X_STRATEGIC_OBJ_ID in NUMBER,
7 X_SCENARIO_ID in NUMBER,
8 X_COMMENTS 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 FPA_SCORECARDS_TL
16 where PROJECT_ID = X_PROJECT_ID
17 and STRATEGIC_OBJ_ID = X_STRATEGIC_OBJ_ID
18 and SCENARIO_ID = X_SCENARIO_ID
19 and LANGUAGE = userenv('LANG')
20 ;
21 begin
22 insert into FPA_SCORECARDS_TL (
23 PROJECT_ID,
24 STRATEGIC_OBJ_ID,
25 COMMENTS,
26 CREATED_BY,
27 CREATION_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATE_LOGIN,
31 SCENARIO_ID,
32 LANGUAGE,
33 SOURCE_LANG
34 ) select
35 X_PROJECT_ID,
36 X_STRATEGIC_OBJ_ID,
37 X_COMMENTS,
38 X_CREATED_BY,
39 X_CREATION_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATE_LOGIN,
43 X_SCENARIO_ID,
44 L.LANGUAGE_CODE,
45 userenv('LANG')
46 from FND_LANGUAGES L
47 where L.INSTALLED_FLAG in ('I', 'B')
48 and not exists
49 (select NULL
50 from FPA_SCORECARDS_TL T
51 where T.PROJECT_ID = X_PROJECT_ID
52 and T.STRATEGIC_OBJ_ID = X_STRATEGIC_OBJ_ID
53 and T.SCENARIO_ID = X_SCENARIO_ID
54 and T.LANGUAGE = L.LANGUAGE_CODE);
55
56 open c;
57 fetch c into X_ROWID;
58 if (c%notfound) then
59 close c;
60 raise no_data_found;
61 end if;
62 close c;
63
64 end INSERT_ROW;
65
66 procedure LOCK_ROW (
67 X_PROJECT_ID in NUMBER,
68 X_STRATEGIC_OBJ_ID in NUMBER,
69 X_SCENARIO_ID in NUMBER,
70 X_COMMENTS in VARCHAR2
71 ) is
72 cursor c1 is select
73 COMMENTS,
74 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
75 from FPA_SCORECARDS_TL
76 where PROJECT_ID = X_PROJECT_ID
77 and STRATEGIC_OBJ_ID = X_STRATEGIC_OBJ_ID
78 and SCENARIO_ID = X_SCENARIO_ID
79 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
80 for update of PROJECT_ID nowait;
81 begin
82 for tlinfo in c1 loop
83 if (tlinfo.BASELANG = 'Y') then
84 if ( ((tlinfo.COMMENTS = X_COMMENTS)
85 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
86 ) then
87 null;
88 else
89 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
90 app_exception.raise_exception;
91 end if;
92 end if;
93 end loop;
94 return;
95 end LOCK_ROW;
96
97 procedure UPDATE_ROW (
98 X_PROJECT_ID in NUMBER,
99 X_STRATEGIC_OBJ_ID in NUMBER,
100 X_SCENARIO_ID in NUMBER,
101 X_COMMENTS in VARCHAR2,
102 X_LAST_UPDATE_DATE in DATE,
103 X_LAST_UPDATED_BY in NUMBER,
104 X_LAST_UPDATE_LOGIN in NUMBER
105 ) is
106 begin
107 update FPA_SCORECARDS_TL set
108 COMMENTS = X_COMMENTS,
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 PROJECT_ID = X_PROJECT_ID
114 and STRATEGIC_OBJ_ID = X_STRATEGIC_OBJ_ID
115 and SCENARIO_ID = X_SCENARIO_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
117
118 if (sql%notfound) then
119 raise no_data_found;
120 end if;
121 end UPDATE_ROW;
122
123 procedure DELETE_ROW (
124 X_PROJECT_ID in NUMBER,
125 X_STRATEGIC_OBJ_ID in NUMBER,
126 X_SCENARIO_ID in NUMBER
127 ) is
128 begin
129 delete from FPA_SCORECARDS_TL
130 where PROJECT_ID = X_PROJECT_ID
131 and STRATEGIC_OBJ_ID = X_STRATEGIC_OBJ_ID
132 and SCENARIO_ID = X_SCENARIO_ID;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137
138 end DELETE_ROW;
139
140 procedure ADD_LANGUAGE
141 is
142 begin
143 update FPA_SCORECARDS_TL T set (
144 COMMENTS
145 ) = (select
146 B.COMMENTS
147 from FPA_SCORECARDS_TL B
148 where B.PROJECT_ID = T.PROJECT_ID
149 and B.STRATEGIC_OBJ_ID = T.STRATEGIC_OBJ_ID
150 and B.SCENARIO_ID = T.SCENARIO_ID
151 and B.LANGUAGE = T.SOURCE_LANG)
152 where (
153 T.PROJECT_ID,
154 T.STRATEGIC_OBJ_ID,
155 T.SCENARIO_ID,
156 T.LANGUAGE
157 ) in (select
158 SUBT.PROJECT_ID,
159 SUBT.STRATEGIC_OBJ_ID,
160 SUBT.SCENARIO_ID,
161 SUBT.LANGUAGE
162 from FPA_SCORECARDS_TL SUBB, FPA_SCORECARDS_TL SUBT
163 where SUBB.PROJECT_ID = SUBT.PROJECT_ID
164 and SUBB.STRATEGIC_OBJ_ID = SUBT.STRATEGIC_OBJ_ID
165 and SUBB.SCENARIO_ID = SUBT.SCENARIO_ID
166 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
167 and (SUBB.COMMENTS <> SUBT.COMMENTS
168 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
169 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
170 ));
171
172 insert into FPA_SCORECARDS_TL (
173 PROJECT_ID,
174 STRATEGIC_OBJ_ID,
175 COMMENTS,
176 CREATED_BY,
177 CREATION_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_DATE,
180 LAST_UPDATE_LOGIN,
181 SCENARIO_ID,
182 LANGUAGE,
183 SOURCE_LANG
184 ) select /*+ ORDERED */
185 B.PROJECT_ID,
186 B.STRATEGIC_OBJ_ID,
187 B.COMMENTS,
188 B.CREATED_BY,
189 B.CREATION_DATE,
190 B.LAST_UPDATED_BY,
191 B.LAST_UPDATE_DATE,
192 B.LAST_UPDATE_LOGIN,
193 B.SCENARIO_ID,
194 L.LANGUAGE_CODE,
195 B.SOURCE_LANG
196 from FPA_SCORECARDS_TL B, FND_LANGUAGES L
197 where L.INSTALLED_FLAG in ('I', 'B')
198 and B.LANGUAGE = userenv('LANG')
199 and not exists
200 (select NULL
201 from FPA_SCORECARDS_TL T
202 where T.PROJECT_ID = B.PROJECT_ID
203 and T.STRATEGIC_OBJ_ID = B.STRATEGIC_OBJ_ID
204 and T.SCENARIO_ID = B.SCENARIO_ID
205 and T.LANGUAGE = L.LANGUAGE_CODE);
206 end ADD_LANGUAGE;
207
208
209 procedure TRANSLATE_ROW(
210 P_PROJECT_ID in NUMBER,
211 P_STRATEGIC_OBJ_ID in NUMBER,
212 P_SCENARIO_ID in NUMBER,
213 P_COMMENTS in VARCHAR2,
214 P_OWNER in VARCHAR2
215 ) is
216 begin
217
218 update fpa_scorecards_tl set
219 COMMENTS = P_COMMENTS,
220 LAST_UPDATE_DATE = sysdate,
221 LAST_UPDATED_BY = decode(P_OWNER, 'SEED', 1, 0),
222 LAST_UPDATE_LOGIN = 0,
223 SOURCE_LANG = userenv('LANG')
224 where PROJECT_ID = P_PROJECT_ID
225 and STRATEGIC_OBJ_ID = P_STRATEGIC_OBJ_ID
226 and SCENARIO_ID = P_SCENARIO_ID
227 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 end TRANSLATE_ROW;
234
235 procedure LOAD_ROW(
236 P_PROJECT_ID in NUMBER,
237 P_STRATEGIC_OBJ_ID in NUMBER,
238 P_SCENARIO_ID in NUMBER,
239 P_COMMENTS in VARCHAR2,
240 P_OWNER in VARCHAR2
241 ) is
242
243 user_id NUMBER;
244 l_rowid VARCHAR2(64);
245
246 begin
247
248 if (P_OWNER = 'SEED')then
249 user_id := 1;
250 else
251 user_id :=0;
252 end if;
253
254 FPA_SCORECARDS_PKG.UPDATE_ROW (
255 X_PROJECT_ID => P_PROJECT_ID,
256 X_STRATEGIC_OBJ_ID => P_STRATEGIC_OBJ_ID,
257 X_SCENARIO_ID => P_SCENARIO_ID,
258 X_COMMENTS => P_COMMENTS,
259 X_LAST_UPDATE_DATE => sysdate,
260 X_LAST_UPDATED_BY => user_id,
261 X_LAST_UPDATE_LOGIN => 0);
262
263 EXCEPTION
264 WHEN no_data_found then
265 FPA_SCORECARDS_PKG.INSERT_ROW (
266 X_ROWID => l_rowid,
267 X_PROJECT_ID => P_PROJECT_ID,
268 X_STRATEGIC_OBJ_ID => P_STRATEGIC_OBJ_ID,
269 X_SCENARIO_ID => P_SCENARIO_ID,
270 X_COMMENTS => P_COMMENTS,
271 X_CREATION_DATE => sysdate ,
272 X_CREATED_BY => user_id ,
273 X_LAST_UPDATE_DATE => sysdate ,
274 X_LAST_UPDATED_BY => user_id ,
275 X_LAST_UPDATE_LOGIN => 0 );
276 end LOAD_ROW;
277
278
279
280
281 end FPA_SCORECARDS_PKG;