[Home] [Help]
PACKAGE BODY: APPS.PA_RP_DEFINITIONS_PKG
Source
1 Package Body PA_RP_DEFINITIONS_PKG AS
2 /*$Header: PARPDFKB.pls 120.2 2011/08/02 10:14:31 vgovvala noship $*/
3
4 /*===============================================================================
5 This api creates Reporting pack definition. This is a table handler for PA_RP_DEFINITIONS table
6 ============================================================================*/
7
8 -- Procedure : INSERT_ROW
9 -- Type : Table Handler
10 -- Purpose : This API will create new Reporting pack definition.
11
12 -- Note : This API will make insert into PA_RP_DEFINITIONS_B and PA_RP_DEFINITIONS_TL table
13 PROCEDURE Insert_Row(
14 P_RP_ID IN NUMBER,
15 P_RP_NAME IN VARCHAR2,
16 P_EMAIL_TITLE IN VARCHAR2,
17 P_EMAIL_BODY IN VARCHAR2,
18 P_DESCRIPTIONS IN VARCHAR2,
19 P_RP_TYPE_ID IN NUMBER,
20 P_DT_PROCESS_DATE IN DATE,
21 P_RP_FILE_ID IN NUMBER,
22 P_TEMPLATE_START_DATE IN DATE,
23 P_TEMPLATE_END_DATE IN DATE,
24 P_OBSOLETE_FLAG IN VARCHAR2 )
25 IS
26 BEGIN
27
28 Insert Into PA_RP_DEFINITIONS_B(
29 RP_ID ,
30 RP_TYPE_ID ,
31 DT_PROCESS_DATE ,
32 RP_FILE_ID ,
33 OBJECT_VERSION_NUMBER ,
34 TEMPLATE_START_DATE ,
35 TEMPLATE_END_DATE ,
36 OBSOLETE_FLAG ,
37 CREATION_DATE ,
38 LAST_UPDATE_DATE ,
39 LAST_UPDATED_BY ,
40 CREATED_BY ,
41 LAST_UPDATE_LOGIN )
42 Values(
43 P_RP_ID ,
44 P_RP_TYPE_ID ,
45 P_DT_PROCESS_DATE ,
46 P_RP_FILE_ID ,
47 1 ,
48 P_TEMPLATE_START_DATE ,
49 P_TEMPLATE_END_DATE ,
50 P_OBSOLETE_FLAG ,
51 SysDate ,
52 SysDate ,
53 Fnd_Global.User_Id ,
54 Fnd_Global.User_Id ,
55 Fnd_Global.Login_Id );
56
57 --MLS changes incorporated.
58 Insert Into PA_RP_DEFINITIONS_TL(
59 RP_ID ,
60 RP_NAME ,
61 EMAIL_TITLE ,
62 EMAIL_BODY ,
63 DESCRIPTIONS ,
64 LANGUAGE ,
65 SOURCE_LANG ,
66 CREATION_DATE ,
67 CREATED_BY ,
68 LAST_UPDATE_DATE ,
69 LAST_UPDATED_BY ,
70 LAST_UPDATE_LOGIN )
71 Select
72 P_RP_ID,
73 P_RP_NAME,
74 P_EMAIL_TITLE,
75 P_EMAIL_BODY,
76 P_DESCRIPTIONS,
77 L.Language_Code,
78 UserEnv('LANG'),
79 SysDate,
80 Fnd_Global.User_Id,
81 SysDate,
82 Fnd_Global.User_Id,
83 Fnd_Global.Login_Id
84 From
85 Fnd_Languages L
86 Where L.Installed_Flag in ('I', 'B')
87 And Not Exists
88 (Select
89 Null
90 From
91 PA_RP_DEFINITIONS_TL T
92 WHere
93 T.RP_ID = RP_ID
94 And T.Language = L.Language_Code);
95
96 END Insert_Row;
97
98
99 /*==========================================================================
100 This api updates RBS Header. This is a Table Handler.
101 ============================================================================*/
102
103 -- Procedure : UPDATE_ROW
104 -- Type : Table Handler
105 -- Purpose : This API will be used to update Reporting pack definition.
106 -- Note : This API will updates PA_RP_DEFINITIONS_B and PA_RP_DEFINITIONS_TL table
107
108 Procedure Update_Row(
109 P_RP_ID IN NUMBER,
110 P_RP_NAME IN VARCHAR2,
111 P_EMAIL_TITLE IN VARCHAR2,
112 P_EMAIL_BODY IN VARCHAR2,
113 P_DESCRIPTIONS IN VARCHAR2,
114 P_RP_TYPE_ID IN NUMBER,
115 P_DT_PROCESS_DATE IN DATE,
116 P_RP_FILE_ID IN NUMBER,
117 P_TEMPLATE_START_DATE IN DATE,
118 P_TEMPLATE_END_DATE IN DATE,
119 P_OBSOLETE_FLAG IN VARCHAR2 )
120
121 Is
122
123 Begin
124
125 Update PA_RP_DEFINITIONS_B
126 Set RP_TYPE_ID = P_RP_TYPE_ID,
127 DT_PROCESS_DATE = P_DT_PROCESS_DATE,
128 RP_FILE_ID = P_RP_FILE_ID,
129 TEMPLATE_START_DATE = P_TEMPLATE_START_DATE,
130 TEMPLATE_END_DATE = P_TEMPLATE_END_DATE,
131 OBSOLETE_FLAG = P_OBSOLETE_FLAG,
132 LAST_UPDATE_DATE = SysDate,
133 LAST_UPDATED_BY = Fnd_Global.User_Id,
134 LAST_UPDATE_LOGIN = Fnd_Global.Login_Id,
135 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
136 Where
137 RP_ID = P_RP_ID;
138
139 --MLS changes incorporated.
140 Update PA_RP_DEFINITIONS_TL
141 Set
142 RP_NAME = P_RP_NAME,
143 EMAIL_TITLE = P_EMAIL_TITLE,
144 EMAIL_BODY = P_EMAIL_BODY,
145 DESCRIPTIONS = P_DESCRIPTIONS,
146 Last_update_date = SysDate,
147 Last_updated_by = Fnd_Global.User_Id,
148 Last_update_login = Fnd_Global.Login_Id,
149 Source_Lang = UserEnv('LANG')
150 Where
151 RP_ID = P_RP_ID
152 And UserEnv('LANG') in (Language, Source_Lang);
153
154 Exception
155 When Others Then
156 Raise;
157
158 End Update_Row;
159
160 procedure ADD_LANGUAGE
161 is
162 begin
163 delete from PA_RP_DEFINITIONS_TL T
164 where not exists
165 (select NULL
166 from PA_RP_DEFINITIONS_B B
167 where B.RP_ID = T.RP_ID
168 );
169
170 update PA_RP_DEFINITIONS_TL T set (
171 RP_NAME,
172 DESCRIPTIONS
173 ) = (select
174 B.RP_NAME,
175 B.DESCRIPTIONS
176 from PA_RP_DEFINITIONS_TL b
177 where B.RP_ID = T.RP_ID
178 and B.LANGUAGE = T.SOURCE_LANG)
179 where (
180 T.RP_ID,
181 T.LANGUAGE
182 ) in (select
183 SUBT.RP_ID,
184 SUBT.LANGUAGE
185 from PA_RP_DEFINITIONS_TL SUBB, PA_RP_DEFINITIONS_TL SUBT
186 where SUBB.RP_ID = SUBT.RP_ID
187 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
188 and (SUBB.RP_NAME <> SUBT.RP_NAME
189 or SUBB.DESCRIPTIONS <> SUBT.DESCRIPTIONS
190 or (SUBB.DESCRIPTIONS is null and SUBT.DESCRIPTIONS is not null)
191 or (SUBB.DESCRIPTIONS is not null and SUBT.DESCRIPTIONS is null)
192 ));
193
194 insert into PA_RP_DEFINITIONS_TL (
195 RP_ID ,
196 RP_NAME ,
197 EMAIL_TITLE ,
198 EMAIL_BODY ,
199 DESCRIPTIONS ,
200 LANGUAGE ,
201 SOURCE_LANG ,
202 CREATION_DATE ,
203 CREATED_BY ,
204 LAST_UPDATE_DATE ,
205 LAST_UPDATED_BY ,
206 LAST_UPDATE_LOGIN )
207 select
208 B.RP_ID ,
209 B.RP_NAME ,
210 B.EMAIL_TITLE ,
211 B.EMAIL_BODY ,
212 B.DESCRIPTIONS ,
213 L.LANGUAGE_CODE ,
214 B.SOURCE_LANG ,
215 B.CREATION_DATE ,
216 B.CREATED_BY ,
217 B.LAST_UPDATE_DATE ,
218 B.LAST_UPDATED_BY ,
219 B.LAST_UPDATE_LOGIN
220 from PA_RP_DEFINITIONS_TL B, FND_LANGUAGES L
221 where L.INSTALLED_FLAG in ('I', 'B')
222 and B.LANGUAGE = userenv('LANG')
223 and not exists
224 (select NULL
225 from PA_RP_DEFINITIONS_TL T
226 where T.RP_ID = B.RP_ID
227 and T.LANGUAGE = L.LANGUAGE_CODE);
228
229 end ADD_LANGUAGE;
230
231 END PA_RP_DEFINITIONS_PKG;