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.CSC_PROF_TABLE_COLUMNS_PKG

Source


1 PACKAGE BODY CSC_PROF_TABLE_COLUMNS_PKG as
2 /* $Header: csctptcb.pls 120.2 2005/09/18 23:33:50 vshastry noship $ */
3 -- Start of Comments
4 -- Package name     : CSC_PROF_TABLE_COLUMNS_PKG
5 -- Purpose          :
6 -- History          : 25-Nov-02 JAmose Fnd_Api_G_MISS*,NOCOPY related changes
7 -- History          : 24-Feb-2003, Introduced new procedure delete_existing_row to
8 --                    delete before loading a row
9 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
10 -- NOTE             :
11 -- End of Comments
12 
13 
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_TABLE_COLUMNS_PKG';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctuvab.pls';
16 
17 PROCEDURE Insert_Row(
18           px_TABLE_COLUMN_ID   IN OUT NOCOPY NUMBER,
19           p_BLOCK_ID    NUMBER,
20           p_TABLE_NAME    VARCHAR2,
21           p_COLUMN_NAME    VARCHAR2,
22           p_LABEL    VARCHAR2,
23           p_TABLE_ALIAS VARCHAR2,
24           p_COLUMN_SEQUENCE NUMBER,
25 	       p_DRILLDOWN_COLUMN_FLAG VARCHAR2,
26           p_LAST_UPDATE_DATE    DATE,
27           p_LAST_UPDATED_BY    NUMBER,
28           p_CREATION_DATE    DATE,
29           p_CREATED_BY    NUMBER,
30           p_LAST_UPDATE_LOGIN    NUMBER,
31           p_SEEDED_FLAG   VARCHAR2,
32           x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)
33 
34  IS
35    CURSOR C2 IS SELECT CSC_PROF_TABLE_COLUMNS_S.nextval FROM sys.dual;
36    l_object_version_number NUMBER := 1;
37    ps_SEEDED_FLAG    Varchar2(3);
38 BEGIN
39 
40    /* added the below 2 lines for bug 4596220 */
41    ps_seeded_flag := p_seeded_flag;
42    IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
43 
44    /* Added This If Condition for Bug 1944040*/
45       If p_Created_by=1 then
46            ps_seeded_flag:='Y';
47       Else
48            ps_seeded_flag:='N';
49       End If;
50    END IF;
51 
52    If (px_TABLE_COLUMN_ID IS NULL) OR (px_TABLE_COLUMN_ID = CSC_CORE_UTILS_PVT.G_MISS_NUM) then
53        OPEN C2;
54        FETCH C2 INTO px_TABLE_COLUMN_ID;
55        CLOSE C2;
56    End If;
57    INSERT INTO CSC_PROF_TABLE_COLUMNS_B(
58            TABLE_COLUMN_ID,
59            BLOCK_ID,
60            TABLE_NAME,
61            COLUMN_NAME,
62            ALIAS_NAME,
63            COLUMN_SEQUENCE,
64 	   DRILLDOWN_COLUMN_FLAG,
65            LAST_UPDATE_DATE,
66            LAST_UPDATED_BY,
67            CREATION_DATE,
68            CREATED_BY,
69            LAST_UPDATE_LOGIN,
70            SEEDED_FLAG,
71            OBJECT_VERSION_NUMBER
72           ) VALUES (
73            px_TABLE_COLUMN_ID,
74            decode( p_BLOCK_ID, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_BLOCK_ID),
75            decode( p_TABLE_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_TABLE_NAME),
76            decode( p_COLUMN_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_COLUMN_NAME),
77            decode( p_TABLE_ALIAS, CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,p_TABLE_ALIAS),
78            decode( p_COLUMN_SEQUENCE,CSC_CORE_UTILS_PVT.G_MISS_NUM,NULL,p_COLUMN_SEQUENCE),
79 	        decode( p_DRILLDOWN_COLUMN_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,p_DRILLDOWN_COLUMN_FLAG),
80            decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
81            decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
82            decode( p_CREATION_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_CREATION_DATE),
83            decode( p_CREATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_CREATED_BY),
84            decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
85            decode( p_SEEDED_FLAG, CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,ps_SEEDED_FLAG),
86            l_OBJECT_VERSION_NUMBER);
87 
88   -- assign out parameters
89   x_object_version_number := l_object_version_number;
90 
91    INSERT INTO CSC_PROF_TABLE_COLUMNS_TL(
92            TABLE_COLUMN_ID,
93            LABEL,
94            CREATED_BY,
95            CREATION_DATE,
96            LAST_UPDATED_BY,
97            LAST_UPDATE_DATE,
98            LAST_UPDATE_LOGIN,
99            LANGUAGE,
100            SOURCE_LANG
101            ) select
102            px_TABLE_COLUMN_ID,
103     	     decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_LABEL),
104            decode( p_CREATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_CREATED_BY),
105            decode( p_CREATION_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_CREATION_DATE),
106            decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
107            decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
108            decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
109     	     L.LANGUAGE_CODE,
110            userenv('LANG')
111   from FND_LANGUAGES L
112   where L.INSTALLED_FLAG in ('I', 'B')
113   and not exists
114     (select NULL
115     from CSC_PROF_TABLE_COLUMNS_TL T
116     where T.TABLE_COLUMN_ID = Px_TABLE_COLUMN_ID
117     and T.LANGUAGE = L.LANGUAGE_CODE);
118 
119 
120 End Insert_Row;
121 
122 PROCEDURE Update_Row(
123           p_TABLE_COLUMN_ID    NUMBER,
124           p_BLOCK_ID    NUMBER,
125           p_TABLE_NAME    VARCHAR2,
126           p_COLUMN_NAME    VARCHAR2,
127           p_LABEL    VARCHAR2,
128           p_TABLE_ALIAS VARCHAR2,
129           p_COLUMN_SEQUENCE NUMBER,
130 	       p_DRILLDOWN_COLUMN_FLAG VARCHAR2,
131           p_LAST_UPDATE_DATE    DATE,
132           p_LAST_UPDATED_BY    NUMBER,
133           p_LAST_UPDATE_LOGIN    NUMBER,
134           p_SEEDED_FLAG    VARCHAR2,
135           px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER)
136 
137  IS
138  BEGIN
139 
140     Update CSC_PROF_TABLE_COLUMNS_B
141     SET
142        BLOCK_ID = decode( p_BLOCK_ID, CSC_CORE_UTILS_PVT.G_MISS_NUM, BLOCK_ID, p_BLOCK_ID),
143        TABLE_NAME = decode( p_TABLE_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, TABLE_NAME, p_TABLE_NAME),
144        COLUMN_NAME = decode( p_COLUMN_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, COLUMN_NAME, p_COLUMN_NAME),
145               -- LABEL = decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, LABEL, p_LABEL),
146 	    ALIAS_NAME = decode(p_TABLE_ALIAS,CSC_CORE_UTILS_PVT.G_MISS_CHAR,ALIAS_NAME,p_TABLE_ALIAS),
147 	    COLUMN_SEQUENCE = decode(p_COLUMN_SEQUENCE, CSC_CORE_UTILS_PVT.G_MISS_NUM,COLUMN_SEQUENCE,p_COLUMN_SEQUENCE),
148 		 DRILLDOWN_COLUMN_FLAG = decode(p_DRILLDOWN_COLUMN_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR,DRILLDOWN_COLUMN_FLAG,p_DRILLDOWN_COLUMN_FLAG),
149        LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
150        LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
151        LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
152        SEEDED_FLAG = decode( p_SEEDED_FLAG, CSC_CORE_UTILS_PVT.G_MISS_CHAR,SEEDED_FLAG, NVL(p_SEEDED_FLAG,SEEDED_FLAG)),
153        OBJECT_VERSION_NUMBER = object_version_number + 1
154     where TABLE_COLUMN_ID = p_TABLE_COLUMN_ID
155     RETURNING OBJECT_VERSION_NUMBER INTO px_OBJECT_VERSION_NUMBER;
156 
157     Update CSC_PROF_TABLE_COLUMNS_TL
158     SET
159       LABEL = decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, LABEL, p_LABEL),
160       LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
161       LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
162       LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
163     	SOURCE_LANG = userenv('LANG')
164     where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
165     and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
166 
167    if (sql%rowcount = 0 ) then
168 	 raise no_data_found;
169    end if;
170 END Update_Row;
171 
172 procedure LOCK_ROW (
173   P_TABLE_COLUMN_ID in NUMBER,
174   P_OBJECT_VERSION_NUMBER in NUMBER
175 ) is
176   cursor c is select
177       BLOCK_ID,
178       TABLE_NAME,
179       ALIAS_NAME,
180       COLUMN_NAME,
181       COLUMN_SEQUENCE,
182       OBJECT_VERSION_NUMBER
183     from CSC_PROF_TABLE_COLUMNS_B
184     where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
185     and object_version_number = p_object_version_number
186     for update of TABLE_COLUMN_ID nowait;
187   recinfo c%rowtype;
188 
189   cursor c1 is select
190       LABEL,
191       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
192     from CSC_PROF_TABLE_COLUMNS_TL
193     where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
194     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
195     for update of TABLE_COLUMN_ID nowait;
196 begin
197   open c;
198   fetch c into recinfo;
199   if (c%notfound) then
200     close c;
201     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
202     app_exception.raise_exception;
203   end if;
204   close c;
205   return;
206 end LOCK_ROW;
207 
208 procedure DELETE_ROW (
209   P_TABLE_COLUMN_ID       NUMBER,
210   P_OBJECT_VERSION_NUMBER NUMBER
211 ) is
212 begin
213   delete from CSC_PROF_TABLE_COLUMNS_TL
214   where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID;
215 
216   if (sql%notfound) then
217     raise no_data_found;
218   end if;
219 
220   delete from CSC_PROF_TABLE_COLUMNS_B
221   where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
222   and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
223 
224   if (sql%notfound) then
225     raise no_data_found;
226   end if;
227 end DELETE_ROW;
228 
229 Procedure DELETE_EXISTING_ROW (
230 	p_BLOCK_ID 		NUMBER,
231    p_TABLE_NAME 	VARCHAR2,
232    p_COLUMN_NAME	VARCHAR2) IS
233 Begin
234    DELETE FROM CSC_PROF_TABLE_COLUMNS_TL
235      WHERE TABLE_COLUMN_ID IN (SELECT TABLE_COLUMN_ID FROM CSC_PROF_TABLE_COLUMNS_B
236        WHERE BLOCK_ID = p_BLOCK_ID
237        AND TABLE_NAME = p_TABLE_NAME
238        AND COLUMN_NAME = p_COLUMN_NAME);
239    IF (SQL%FOUND) THEN
240       DELETE FROM CSC_PROF_TABLE_COLUMNS_B
241          WHERE BLOCK_ID = p_BLOCK_ID
242          AND TABLE_NAME = p_TABLE_NAME
243          AND COLUMN_NAME = p_COLUMN_NAME ;
244    END IF;
245 End Delete_Existing_Row;
246 
247 procedure ADD_LANGUAGE
248 is
249 begin
250   delete from CSC_PROF_TABLE_COLUMNS_TL T
251   where not exists
252     (select NULL
253     from CSC_PROF_TABLE_COLUMNS_B B
254     where B.TABLE_COLUMN_ID = T.TABLE_COLUMN_ID
255     );
256 
257   update CSC_PROF_TABLE_COLUMNS_TL T set (
258       LABEL
259     ) = (select
260       B.LABEL
261     from CSC_PROF_TABLE_COLUMNS_TL B
262     where B.TABLE_COLUMN_ID = T.TABLE_COLUMN_ID
263     and B.LANGUAGE = T.SOURCE_LANG)
264   where (
265       T.TABLE_COLUMN_ID,
266       T.LANGUAGE
267   ) in (select
268       SUBT.TABLE_COLUMN_ID,
269       SUBT.LANGUAGE
270     from CSC_PROF_TABLE_COLUMNS_TL SUBB, CSC_PROF_TABLE_COLUMNS_TL SUBT
271     where SUBB.TABLE_COLUMN_ID = SUBT.TABLE_COLUMN_ID
272     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
273     and (SUBB.LABEL <> SUBT.LABEL
274       or (SUBB.LABEL is null and SUBT.LABEL is not null)
275       or (SUBB.LABEL is not null and SUBT.LABEL is null)
276   ));
277 
278   insert into CSC_PROF_TABLE_COLUMNS_TL (
279     TABLE_COLUMN_ID,
280     LABEL,
281     CREATED_BY,
282     CREATION_DATE,
283     LAST_UPDATED_BY,
284     LAST_UPDATE_DATE,
285     LAST_UPDATE_LOGIN,
286     LANGUAGE,
287     SOURCE_LANG
288   ) select
289     B.TABLE_COLUMN_ID,
290     B.LABEL,
291     B.CREATED_BY,
292     B.CREATION_DATE,
293     B.LAST_UPDATED_BY,
294     B.LAST_UPDATE_DATE,
295     B.LAST_UPDATE_LOGIN,
296     L.LANGUAGE_CODE,
297     B.SOURCE_LANG
298   from CSC_PROF_TABLE_COLUMNS_TL B, FND_LANGUAGES L
299   where L.INSTALLED_FLAG in ('I', 'B')
300   and B.LANGUAGE = userenv('LANG')
301   and not exists
302     (select NULL
303     from CSC_PROF_TABLE_COLUMNS_TL T
304     where T.TABLE_COLUMN_ID = B.TABLE_COLUMN_ID
305     and T.LANGUAGE = L.LANGUAGE_CODE);
306 end ADD_LANGUAGE;
307 
308 Procedure TRANSLATE_ROW (
309    p_LABEL 		in varchar2,
310    p_TABLE_COLUMN_ID 	in number,
311    p_OWNER 		in varchar2 ) is
312 
313 Begin
314      Update Csc_Prof_Table_Columns_TL set
315           label = nvl(P_LABEL,label),
316           last_update_date  = sysdate,
317 	  last_updated_by   = decode(p_OWNER,'SEED',1,0),
318 	  last_update_login = 0,
319 	  source_lang       = userenv('LANG')
320      Where table_column_id = P_TABLE_COLUMN_ID
321      and  userenv('LANG') in (language, source_lang);
322 
323 end TRANSLATE_ROW;
324 
325 Procedure LOAD_ROW (
326    p_TABLE_COLUMN_ID        in number,
327    p_BLOCK_ID               in number,
328    p_TABLE_NAME             in varchar2,
329    p_COLUMN_NAME            in varchar2,
330    p_LABEL                  in varchar2,
331    p_ALIAS_NAME             in varchar2,
332    p_COLUMN_SEQUENCE        in number,
333    p_DRILLDOWN_COLUMN_FLAG  in varchar2,
334    p_SEEDED_FLAG            in varchar2,
335    p_last_update_date       IN DATE,
336    p_last_updated_by        IN NUMBER,
337    p_last_update_login      IN NUMBER
338  ) is
339 
340  l_object_version_number number := 0;
341  l_table_column_id       number := p_table_column_id;
342 Begin
343 
344 
345   Csc_Prof_Table_Columns_Pkg.Update_Row(
346    p_TABLE_COLUMN_ID        => P_TABLE_COLUMN_ID,
347    p_BLOCK_ID               => P_BLOCK_ID,
348    p_TABLE_NAME             => P_TABLE_NAME,
349    p_COLUMN_NAME            => P_COLUMN_NAME,
350    p_LABEL                  => P_LABEL,
351    p_TABLE_ALIAS            => P_ALIAS_NAME,
352    p_COLUMN_SEQUENCE        => P_COLUMN_SEQUENCE,
353    p_drilldown_column_flag  => P_DRILLDOWN_COLUMN_FLAG,
354    p_LAST_UPDATE_DATE       => p_last_update_date,
355    p_LAST_UPDATED_BY        => p_last_updated_by,
356    p_LAST_UPDATE_LOGIN      => p_last_update_login,
357    p_SEEDED_FLAG            => p_SEEDED_FLAG,
358    px_OBJECT_VERSION_NUMBER => l_object_version_number);
359 
360  Exception
361   When no_data_found then
362     /* Added this procedure not to cause the duplicate records while
363     loading the seeded table columns. The reason being is if the seeded
364     profile are changed it removes the records and recreates brand new
365     records with new sequence number. So in the customer site it will
366     introduce new set of records with diff sequence numbers. So the following
367     procedure will check and will remove the existing record first before
368     uploading new set of records -jamose */
369    Csc_Prof_Table_Columns_Pkg.Delete_Existing_Row(
370    	p_BLOCK_ID			=>p_block_id,
371    	p_TABLE_NAME      =>p_table_name,
372    	p_COLUMN_NAME     =>p_column_name
373    	);
374 
375    Csc_Prof_Table_Columns_Pkg.Insert_Row(
376       px_TABLE_COLUMN_ID      => l_table_column_id,
377       p_BLOCK_ID              => P_block_id,
378       p_TABLE_NAME            => P_table_name,
379       p_COLUMN_NAME           => P_column_name,
380       p_LABEL                 => P_label,
381       p_TABLE_ALIAS           => P_alias_name,
382       p_COLUMN_SEQUENCE       => P_column_sequence,
383       p_drilldown_column_flag  =>P_drilldown_column_flag,
384       p_LAST_UPDATE_DATE      => p_last_update_date,
385       p_LAST_UPDATED_BY       => p_last_updated_by,
386       p_CREATION_DATE         => p_last_update_date,
387       p_CREATED_BY            => p_last_updated_by,
388       p_LAST_UPDATE_LOGIN     => p_last_update_login,
389       p_SEEDED_FLAG           => p_SEEDED_FLAG,
390       x_OBJECT_VERSION_NUMBER => l_object_version_number);
391 
392 End LOAD_ROW;
393 
394 
395 End CSC_PROF_TABLE_COLUMNS_PKG;