DBA Data[Home] [Help]

PACKAGE BODY: APPS.OTA_CTT_SHD

Source


1 Package Body ota_ctt_shd as
2 /* $Header: otcttrhi.pkb 120.0 2005/05/29 07:09:55 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33) := '  ota_ctt_shd.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14   (p_constraint_name in all_constraints.constraint_name%TYPE
15   ) Is
16 --
17   l_proc        varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20   --
21   If (p_constraint_name = 'OTA_CATEGORY_USAGES_TL_PK1') Then
22     fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23     fnd_message.set_token('PROCEDURE', l_proc);
24     fnd_message.set_token('STEP','5');
25     fnd_message.raise_error;
26   Else
27     fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28     fnd_message.set_token('PROCEDURE', l_proc);
29     fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30     fnd_message.raise_error;
31   End If;
32   --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39   (p_category_usage_id                    in     number
40   ,p_language                             in     varchar2
41   )      Return Boolean Is
42 --
43   --
44   -- Cursor selects the 'current' row from the HR Schema
45   --
46   Cursor C_Sel1 is
47     select
48        category_usage_id
49       ,language
50       ,source_lang
51       ,category
52       ,description
53     from  ota_category_usages_tl
54     where category_usage_id = p_category_usage_id
55     and   language = p_language;
56 --
57   l_fct_ret     boolean;
58 --
59 Begin
60   --
61   If (p_category_usage_id is null or
62       p_language is null
63      ) Then
64     --
65     -- One of the primary key arguments is null therefore we must
66     -- set the returning function value to false
67     --
68     l_fct_ret := false;
69   Else
70     If (p_category_usage_id
71         = ota_ctt_shd.g_old_rec.category_usage_id and
72         p_language
73         = ota_ctt_shd.g_old_rec.language
74        ) Then
75       --
76       -- The g_old_rec is current therefore we must
77       -- set the returning function to true
78       --
79       l_fct_ret := true;
80     Else
81       --
82       -- Select the current row into g_old_rec
83       --
84       Open C_Sel1;
85       Fetch C_Sel1 Into ota_ctt_shd.g_old_rec;
86       If C_Sel1%notfound Then
87         Close C_Sel1;
88         --
89         -- The primary key is invalid therefore we must error
90         --
91         fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
92         fnd_message.raise_error;
93       End If;
94       Close C_Sel1;
95       --
96       l_fct_ret := true;
97     End If;
98   End If;
99   Return (l_fct_ret);
100 --
101 End api_updating;
102 --
103 -- ----------------------------------------------------------------------------
104 -- |---------------------------------< lck >----------------------------------|
105 -- ----------------------------------------------------------------------------
106 Procedure lck
107   (p_category_usage_id                    in     number
108   ,p_language                             in     varchar2
109   ) is
110 --
111 -- Cursor selects the 'current' row from the HR Schema
112 --
113   Cursor C_Sel1 is
114     select
115        category_usage_id
116       ,language
117       ,source_lang
118       ,category
119       ,description
120     from        ota_category_usages_tl
121     where       category_usage_id = p_category_usage_id
122     and   language = p_language
123     for update nowait;
124 --
125   l_proc        varchar2(72) := g_package||'lck';
126 --
127 Begin
128   hr_utility.set_location('Entering:'||l_proc, 5);
129   --
130   hr_api.mandatory_arg_error
131     (p_api_name           => l_proc
132     ,p_argument           => 'CATEGORY_USAGE_ID'
133     ,p_argument_value     => p_category_usage_id
134     );
135   hr_utility.set_location(l_proc,6);
136   hr_api.mandatory_arg_error
137     (p_api_name           => l_proc
138     ,p_argument           => 'LANGUAGE'
139     ,p_argument_value     => p_language
140     );
141   --
142   Open  C_Sel1;
143   Fetch C_Sel1 Into ota_ctt_shd.g_old_rec;
144   If C_Sel1%notfound then
145     Close C_Sel1;
146     --
147     -- The primary key is invalid therefore we must error
148     --
149     fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
150     fnd_message.raise_error;
151   End If;
152   Close C_Sel1;
153   --
154   --
155   hr_utility.set_location(' Leaving:'||l_proc, 10);
156   --
157   -- We need to trap the ORA LOCK exception
158   --
159 Exception
160   When HR_Api.Object_Locked then
161     --
162     -- The object is locked therefore we need to supply a meaningful
163     -- error message.
164     --
165     fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
166     fnd_message.set_token('TABLE_NAME', 'ota_category_usages_tl');
167     fnd_message.raise_error;
168 End lck;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |----------------------------< add_language >------------------------------|
172 -- ----------------------------------------------------------------------------
173 --
174 --  Executed AOL's tltblgen(SOLARIS) program to generate the
175 --  ADD_LANGUAGE procedure.
176 --
177 -- ----------------------------------------------------------------------------
178 PROCEDURE add_language IS
179 begin
180   delete from OTA_CATEGORY_USAGES_TL T
181   where not exists
182     (select NULL
183     from OTA_CATEGORY_USAGES B
184     where B.CATEGORY_USAGE_ID = T.CATEGORY_USAGE_ID
185     );
186 
187   update OTA_CATEGORY_USAGES_TL T set (
188       CATEGORY,
189       DESCRIPTION
190     ) = (select
191       B.CATEGORY,
192       B.DESCRIPTION
193     from OTA_CATEGORY_USAGES_TL B
194     where B.CATEGORY_USAGE_ID = T.CATEGORY_USAGE_ID
195     and B.LANGUAGE = T.SOURCE_LANG)
196   where (
197       T.CATEGORY_USAGE_ID,
198       T.LANGUAGE
199   ) in (select
200       SUBT.CATEGORY_USAGE_ID,
201       SUBT.LANGUAGE
202     from OTA_CATEGORY_USAGES_TL SUBB, OTA_CATEGORY_USAGES_TL SUBT
203     where SUBB.CATEGORY_USAGE_ID = SUBT.CATEGORY_USAGE_ID
204     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
205     and (SUBB.CATEGORY <> SUBT.CATEGORY
206       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
207       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
208       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
209   ));
210 
211   insert into OTA_CATEGORY_USAGES_TL (
212     CATEGORY_USAGE_ID,
213     CATEGORY,
214     DESCRIPTION,
215     CREATED_BY,
216     CREATION_DATE,
217     LAST_UPDATE_DATE,
218     LAST_UPDATED_BY,
219     LAST_UPDATE_LOGIN,
220     LANGUAGE,
221     SOURCE_LANG
222   ) select
223     B.CATEGORY_USAGE_ID,
224     B.CATEGORY,
225     B.DESCRIPTION,
226     B.CREATED_BY,
227     B.CREATION_DATE,
228     B.LAST_UPDATE_DATE,
229     B.LAST_UPDATED_BY,
230     B.LAST_UPDATE_LOGIN,
231     L.LANGUAGE_CODE,
232     B.SOURCE_LANG
233   from OTA_CATEGORY_USAGES_TL B, FND_LANGUAGES L
234   where L.INSTALLED_FLAG in ('I', 'B')
235   and B.LANGUAGE = userenv('LANG')
236   and not exists
237     (select NULL
238     from OTA_CATEGORY_USAGES_TL T
239     where T.CATEGORY_USAGE_ID = B.CATEGORY_USAGE_ID
240     and T.LANGUAGE = L.LANGUAGE_CODE);
241 end ADD_LANGUAGE;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |-----------------------------< convert_args >-----------------------------|
245 -- ----------------------------------------------------------------------------
246 Function convert_args
247   (p_category_usage_id              in number
248   ,p_language                       in varchar2
249   ,p_source_lang                    in varchar2
250   ,p_category                       in varchar2
251   ,p_description                    in varchar2
252   )
253   Return g_rec_type is
254 --
255   l_rec   g_rec_type;
256 --
257 Begin
258   --
259   -- Convert arguments into local l_rec structure.
260   --
261   l_rec.category_usage_id                := p_category_usage_id;
262   l_rec.language                         := p_language;
263   l_rec.source_lang                      := p_source_lang;
264   l_rec.category                         := p_category;
265   l_rec.description                      := p_description;
266   --
267   -- Return the plsql record structure.
268   --
269   Return(l_rec);
270 --
271 End convert_args;
272 --
273 end ota_ctt_shd;