1 package body BSC_SYS_PERIODS_PKG as
2 /* $Header: BSCSPRDB.pls 115.6 2003/02/12 14:29:33 adeulgao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_YEAR in NUMBER,
6 X_PERIODICITY_ID in NUMBER,
7 X_PERIOD_ID in NUMBER,
8 X_MONTH in NUMBER,
9 X_NAME in VARCHAR2,
10 X_SHORT_NAME in VARCHAR2
11 ) is
12 cursor C is select ROWID from BSC_SYS_PERIODS_TL
13 where YEAR = X_YEAR
14 and PERIODICITY_ID = X_PERIODICITY_ID
15 and PERIOD_ID = X_PERIOD_ID
16 and MONTH = X_MONTH
17 and LANGUAGE = userenv('LANG')
18 ;
19 begin
20 insert into BSC_SYS_PERIODS_TL (
21 YEAR,
22 PERIODICITY_ID,
23 PERIOD_ID,
24 MONTH,
25 NAME,
26 SHORT_NAME,
27 LANGUAGE,
28 SOURCE_LANG
29 ) select
30 X_YEAR,
31 X_PERIODICITY_ID,
32 X_PERIOD_ID,
33 X_MONTH,
34 X_NAME,
35 X_SHORT_NAME,
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_PERIODS_TL T
43 where T.YEAR = X_YEAR
44 and T.PERIODICITY_ID = X_PERIODICITY_ID
45 and T.PERIOD_ID = X_PERIOD_ID
46 and T.MONTH = X_MONTH
47 and T.LANGUAGE = L.LANGUAGE_CODE);
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 X_YEAR in NUMBER,
61 X_PERIODICITY_ID in NUMBER,
62 X_PERIOD_ID in NUMBER,
63 X_MONTH in NUMBER,
64 X_NAME in VARCHAR2,
65 X_SHORT_NAME in VARCHAR2
66 ) is
67 cursor c1 is select
68 NAME,
69 SHORT_NAME,
70 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
71 from BSC_SYS_PERIODS_TL
72 where YEAR = X_YEAR
73 and PERIODICITY_ID = X_PERIODICITY_ID
74 and PERIOD_ID = X_PERIOD_ID
75 and MONTH = X_MONTH
76 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
77 for update of YEAR nowait;
78 begin
79 for tlinfo in c1 loop
80 if (tlinfo.BASELANG = 'Y') then
81 if ( ((tlinfo.NAME = X_NAME)
82 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
83 AND ((tlinfo.SHORT_NAME = X_SHORT_NAME)
84 OR ((tlinfo.SHORT_NAME is null) AND (X_SHORT_NAME 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_YEAR in NUMBER,
98 X_PERIODICITY_ID in NUMBER,
99 X_PERIOD_ID in NUMBER,
100 X_MONTH in NUMBER,
101 X_NAME in VARCHAR2,
102 X_SHORT_NAME in VARCHAR2
103 ) is
104 begin
105 update BSC_SYS_PERIODS_TL set
106 NAME = X_NAME,
107 SHORT_NAME = X_SHORT_NAME,
108 SOURCE_LANG = userenv('LANG')
109 where YEAR = X_YEAR
110 and PERIODICITY_ID = X_PERIODICITY_ID
111 and PERIOD_ID = X_PERIOD_ID
112 and MONTH = X_MONTH
113 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
114
115 if (sql%notfound) then
116 raise no_data_found;
117 end if;
118 end UPDATE_ROW;
119
120 procedure DELETE_ROW (
121 X_YEAR in NUMBER,
122 X_PERIODICITY_ID in NUMBER,
123 X_PERIOD_ID in NUMBER,
124 X_MONTH in NUMBER
125 ) is
126 begin
127 delete from BSC_SYS_PERIODS_TL
128 where YEAR = X_YEAR
129 and PERIODICITY_ID = X_PERIODICITY_ID
130 and PERIOD_ID = X_PERIOD_ID
131 and MONTH = X_MONTH;
132
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136
137 end DELETE_ROW;
138
139 procedure ADD_LANGUAGE
140 is
141 begin
142 update BSC_SYS_PERIODS_TL T set (
143 NAME,
144 SHORT_NAME
145 ) = (select
146 B.NAME,
147 B.SHORT_NAME
148 from BSC_SYS_PERIODS_TL B
149 where B.YEAR = T.YEAR
150 and B.PERIODICITY_ID = T.PERIODICITY_ID
151 and B.PERIOD_ID = T.PERIOD_ID
152 and B.MONTH = T.MONTH
153 and B.LANGUAGE = T.SOURCE_LANG)
154 where (
155 T.YEAR,
156 T.PERIODICITY_ID,
157 T.PERIOD_ID,
158 T.MONTH,
159 T.LANGUAGE
160 ) in (select
161 SUBT.YEAR,
162 SUBT.PERIODICITY_ID,
163 SUBT.PERIOD_ID,
164 SUBT.MONTH,
165 SUBT.LANGUAGE
166 from BSC_SYS_PERIODS_TL SUBB, BSC_SYS_PERIODS_TL SUBT
167 where SUBB.YEAR = SUBT.YEAR
168 and SUBB.PERIODICITY_ID = SUBT.PERIODICITY_ID
169 and SUBB.PERIOD_ID = SUBT.PERIOD_ID
170 and SUBB.MONTH = SUBT.MONTH
171 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
172 and (SUBB.NAME <> SUBT.NAME
173 or (SUBB.NAME is null and SUBT.NAME is not null)
174 or (SUBB.NAME is not null and SUBT.NAME is null)
175 or SUBB.SHORT_NAME <> SUBT.SHORT_NAME
176 or (SUBB.SHORT_NAME is null and SUBT.SHORT_NAME is not null)
177 or (SUBB.SHORT_NAME is not null and SUBT.SHORT_NAME is null)
178 ));
179
180 insert into BSC_SYS_PERIODS_TL (
181 YEAR,
182 PERIODICITY_ID,
183 PERIOD_ID,
184 MONTH,
185 NAME,
186 SHORT_NAME,
187 LANGUAGE,
188 SOURCE_LANG
189 ) select
190 B.YEAR,
191 B.PERIODICITY_ID,
192 B.PERIOD_ID,
193 B.MONTH,
194 B.NAME,
195 B.SHORT_NAME,
196 L.LANGUAGE_CODE,
197 B.SOURCE_LANG
198 from BSC_SYS_PERIODS_TL B, FND_LANGUAGES L
199 where L.INSTALLED_FLAG in ('I', 'B')
200 and B.LANGUAGE = userenv('LANG')
201 and not exists
202 (select NULL
203 from BSC_SYS_PERIODS_TL T
204 where T.YEAR = B.YEAR
205 and T.PERIODICITY_ID = B.PERIODICITY_ID
206 and T.PERIOD_ID = B.PERIOD_ID
207 and T.MONTH = B.MONTH
208 and T.LANGUAGE = L.LANGUAGE_CODE);
209 end ADD_LANGUAGE;
210
211 end BSC_SYS_PERIODS_PKG;
[Home] [Help]