1 package body BSC_SYS_PERIODICITIES_PKG as
2 /* $Header: BSCSPERB.pls 115.7 2004/03/04 16:22:54 meastmon ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PERIODICITY_ID in NUMBER,
6 X_NAME in VARCHAR2,
7 X_CREATION_DATE in DATE,
8 X_CREATED_BY in NUMBER,
9 X_LAST_UPDATE_DATE in DATE,
10 X_LAST_UPDATED_BY in NUMBER,
11 X_LAST_UPDATE_LOGIN in NUMBER
12 ) is
13 cursor C is select ROWID from BSC_SYS_PERIODICITIES_TL
14 where PERIODICITY_ID = X_PERIODICITY_ID
15 and LANGUAGE = userenv('LANG')
16 ;
17 begin
18 insert into BSC_SYS_PERIODICITIES_TL (
19 PERIODICITY_ID,
20 NAME,
21 CREATION_DATE ,
22 CREATED_BY,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY ,
25 LAST_UPDATE_LOGIN,
26 LANGUAGE,
27 SOURCE_LANG
28 ) select
29 X_PERIODICITY_ID,
30 X_NAME,
31 X_CREATION_DATE ,
32 X_CREATED_BY,
33 X_LAST_UPDATE_DATE,
34 X_LAST_UPDATED_BY ,
35 X_LAST_UPDATE_LOGIN,
36 L.LANGUAGE_CODE,
37 userenv('LANG')
38 from FND_LANGUAGES L
39 where L.INSTALLED_FLAG in ('I', 'B')
40 and not exists
41 (select NULL
42 from BSC_SYS_PERIODICITIES_TL T
43 where T.PERIODICITY_ID = X_PERIODICITY_ID
44 and T.LANGUAGE = L.LANGUAGE_CODE);
45
46 open c;
47 fetch c into X_ROWID;
48 if (c%notfound) then
49 close c;
50 raise no_data_found;
51 end if;
52 close c;
53
54 end INSERT_ROW;
55
56 procedure LOCK_ROW (
57 X_PERIODICITY_ID in NUMBER,
58 X_NAME in VARCHAR2
59 ) is
60 cursor c1 is select
61 NAME,
62 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
63 from BSC_SYS_PERIODICITIES_TL
64 where PERIODICITY_ID = X_PERIODICITY_ID
65 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
66 for update of PERIODICITY_ID nowait;
67 begin
68 for tlinfo in c1 loop
69 if (tlinfo.BASELANG = 'Y') then
70 if ( (tlinfo.NAME = X_NAME)
71 ) then
72 null;
73 else
74 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
75 app_exception.raise_exception;
76 end if;
77 end if;
78 end loop;
79 return;
80 end LOCK_ROW;
81
82 procedure UPDATE_ROW (
83 X_PERIODICITY_ID in NUMBER,
84 X_NAME in VARCHAR2,
85 X_CREATION_DATE in DATE,
86 X_CREATED_BY in NUMBER,
87 X_LAST_UPDATE_DATE in DATE,
88 X_LAST_UPDATED_BY in NUMBER,
89 X_LAST_UPDATE_LOGIN in NUMBER
90 ) is
91 begin
92 update BSC_SYS_PERIODICITIES_TL set
93 NAME = X_NAME,
94 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
95 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
96 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN),
97 SOURCE_LANG = userenv('LANG')
98 where PERIODICITY_ID = X_PERIODICITY_ID
99 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
100
101 if (sql%notfound) then
102 raise no_data_found;
103 end if;
104 end UPDATE_ROW;
105
106 procedure DELETE_ROW (
107 X_PERIODICITY_ID in NUMBER
108 ) is
109 begin
110 delete from BSC_SYS_PERIODICITIES_TL
111 where PERIODICITY_ID = X_PERIODICITY_ID;
112
113 if (sql%notfound) then
114 raise no_data_found;
115 end if;
116
117 end DELETE_ROW;
118
119 procedure ADD_LANGUAGE
120 is
121 l_user NUMBER;
122 begin
123
124 -- Ref: bug#3482442 In corner cases this query can return more than one
125 -- row and it will fail. AUDSID is not PK. After meeting with
126 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
127 l_user := BSC_APPS.fnd_global_user_id;
128
129 update BSC_SYS_PERIODICITIES_TL T set (
130 NAME
131 ) = (select
132 B.NAME
133 from BSC_SYS_PERIODICITIES_TL B
134 where B.PERIODICITY_ID = T.PERIODICITY_ID
135 and B.LANGUAGE = T.SOURCE_LANG)
136 where (
137 T.PERIODICITY_ID,
138 T.LANGUAGE
139 ) in (select
140 SUBT.PERIODICITY_ID,
141 SUBT.LANGUAGE
142 from BSC_SYS_PERIODICITIES_TL SUBB, BSC_SYS_PERIODICITIES_TL SUBT
143 where SUBB.PERIODICITY_ID = SUBT.PERIODICITY_ID
144 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
145 and (SUBB.NAME <> SUBT.NAME
146 ));
147
148 insert into BSC_SYS_PERIODICITIES_TL (
149 PERIODICITY_ID,
150 NAME,
151 CREATION_DATE ,
152 CREATED_BY,
153 LAST_UPDATE_DATE,
154 LAST_UPDATED_BY ,
155 LAST_UPDATE_LOGIN,
156 LANGUAGE,
157 SOURCE_LANG
158 ) select
159 B.PERIODICITY_ID,
160 B.NAME,
161 SYSDATE,
162 l_user,
163 SYSDATE,
164 l_user,
165 l_user,
166 L.LANGUAGE_CODE,
167 B.SOURCE_LANG
168 from BSC_SYS_PERIODICITIES_TL B, FND_LANGUAGES L
169 where L.INSTALLED_FLAG in ('I', 'B')
170 and B.LANGUAGE = userenv('LANG')
171 and not exists
172 (select NULL
173 from BSC_SYS_PERIODICITIES_TL T
174 where T.PERIODICITY_ID = B.PERIODICITY_ID
175 and T.LANGUAGE = L.LANGUAGE_CODE);
176 end ADD_LANGUAGE;
177
178 end BSC_SYS_PERIODICITIES_PKG;
[Home] [Help]