[Home] [Help]
PACKAGE BODY: APPS.MSD_LEVELS_PKG
Source
1 package body MSD_LEVELS_PKG as
2 /* $Header: msdlpkgb.pls 120.1 2006/01/18 01:18:57 amitku noship $ */
3
4 PROCEDURE LOAD_ROW(
5 x_level_id in varchar2,
6 x_plan_type in varchar2,
7 x_level_name varchar2,
8 x_description varchar2,
9 x_DIMENSION_CODE VARCHAR2,
10 x_LEVEL_TYPE_CODE VARCHAR2,
11 X_ORG_RELATIONSHIP_VIEW VARCHAR2,
12 X_ATTRIBUTE1_CONTEXT VARCHAR2,
13 X_ATTRIBUTE2_CONTEXT VARCHAR2,
14 X_ATTRIBUTE3_CONTEXT VARCHAR2,
15 X_ATTRIBUTE4_CONTEXT VARCHAR2,
16 X_ATTRIBUTE5_CONTEXT VARCHAR2,
17 X_ATTRIBUTE_CATEGORY VARCHAR2,
18 X_ATTRIBUTE1 VARCHAR2,
19 X_ATTRIBUTE2 VARCHAR2,
20 X_ATTRIBUTE3 VARCHAR2,
21 X_ATTRIBUTE4 VARCHAR2,
22 X_ATTRIBUTE5 VARCHAR2,
23 X_ATTRIBUTE6 VARCHAR2,
24 X_ATTRIBUTE7 VARCHAR2,
25 X_ATTRIBUTE8 VARCHAR2,
26 X_ATTRIBUTE9 VARCHAR2,
27 X_ATTRIBUTE10 VARCHAR2,
28 X_ATTRIBUTE11 VARCHAR2,
29 X_ATTRIBUTE12 VARCHAR2,
30 X_ATTRIBUTE13 VARCHAR2,
31 X_ATTRIBUTE14 VARCHAR2,
32 X_ATTRIBUTE15 VARCHAR2,
33 x_last_update_date in varchar2,
34 x_owner in varchar2,
35 x_custom_mode in varchar2,
36 X_SYSTEM_ATTRIBUTE1_CONTEXT VARCHAR2,
37 X_SYSTEM_ATTRIBUTE2_CONTEXT VARCHAR2) is
38
39 f_luby number; -- entity owner in file
40 f_ludate date; -- entity update date in file
41 db_luby number; -- entity owner in db
42 db_ludate date; -- entity update date in db
43 begin
44 -- Translate owner to file_last_updated_by
45 if (x_owner = 'SEED') then
46 f_luby := 1;
47 else
48 f_luby := 0;
49 end if;
50
51 -- Translate char last_update_date to date
52 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
53
54 begin
55 select LAST_UPDATED_BY, LAST_UPDATE_DATE
56 into db_luby, db_ludate
57 from MSD_LEVELS
58 where LEVEL_ID = to_number(x_level_id) and nvl(plan_type, -1) = nvl( to_char(x_plan_type),-1) ;
59
60 -- Update record, honoring customization mode.
61 -- Record should be updated only if:
62 -- a. CUSTOM_MODE = FORCE, or
63 -- b. file owner is CUSTOM, db owner is SEED
64 -- c. owners are the same, and file_date > db_date
65 if ((x_custom_mode = 'FORCE') or
66 ((f_luby = 0) and (db_luby = 1)) or
67 ((f_luby = db_luby) and (f_ludate > db_ludate)))
68 then
69 update MSD_LEVELS set
70 LEVEL_NAME = X_LEVEL_NAME,
71 DESCRIPTION = X_DESCRIPTION,
72 DIMENSION_CODE = X_DIMENSION_CODE,
73 LEVEL_TYPE_CODE = X_LEVEL_TYPE_CODE,
74 ORG_RELATIONSHIP_VIEW = X_ORG_RELATIONSHIP_VIEW,
75 ATTRIBUTE1_CONTEXT = X_ATTRIBUTE1_CONTEXT,
76 ATTRIBUTE2_CONTEXT = X_ATTRIBUTE2_CONTEXT,
77 ATTRIBUTE3_CONTEXT = X_ATTRIBUTE3_CONTEXT,
78 ATTRIBUTE4_CONTEXT = X_ATTRIBUTE4_CONTEXT,
79 ATTRIBUTE5_CONTEXT = X_ATTRIBUTE5_CONTEXT,
80 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
81 ATTRIBUTE1 = X_ATTRIBUTE1,
82 ATTRIBUTE2 = X_ATTRIBUTE2,
83 ATTRIBUTE3 = X_ATTRIBUTE3,
84 ATTRIBUTE4 = X_ATTRIBUTE4,
85 ATTRIBUTE5 = X_ATTRIBUTE5,
86 ATTRIBUTE6 = X_ATTRIBUTE6,
87 ATTRIBUTE7 = X_ATTRIBUTE7,
88 ATTRIBUTE8 = X_ATTRIBUTE8,
89 ATTRIBUTE9 = X_ATTRIBUTE9,
90 ATTRIBUTE10 = X_ATTRIBUTE10,
91 ATTRIBUTE11 = X_ATTRIBUTE11,
92 ATTRIBUTE12 = X_ATTRIBUTE12,
93 ATTRIBUTE13 = X_ATTRIBUTE13,
94 ATTRIBUTE14 = X_ATTRIBUTE14,
95 ATTRIBUTE15 = X_ATTRIBUTE15,
96 LAST_UPDATE_DATE = f_ludate,
97 LAST_UPDATED_BY = f_luby,
98 LAST_UPDATE_LOGIN = 0,
99 SYSTEM_ATTRIBUTE1_CONTEXT = X_SYSTEM_ATTRIBUTE1_CONTEXT,
100 SYSTEM_ATTRIBUTE2_CONTEXT = X_SYSTEM_ATTRIBUTE2_CONTEXT
101 where LEVEL_ID = to_number(X_LEVEL_ID)and
102 nvl( PLAN_TYPE, -1) = nvl ( x_plan_type , -1) ;
103
104 else
105
106 update MSD_LEVELS set
107 ORG_RELATIONSHIP_VIEW = X_ORG_RELATIONSHIP_VIEW,
108 LAST_UPDATE_DATE = f_ludate,
109 LAST_UPDATED_BY = f_luby,
110 LAST_UPDATE_LOGIN = 0
111 where LEVEL_ID = to_number(X_LEVEL_ID)
112 and nvl( PLAN_TYPE, -1) = nvl( x_plan_type , -1) ;
113
114 end if;
115
116 exception
117 when no_data_found then
118 -- Record doesn't exist - insert in all cases
119 insert into MSD_LEVELS (
120 LEVEL_ID,
121 PLAN_TYPE,
122 LEVEL_NAME,
123 DESCRIPTION,
124 DIMENSION_CODE,
125 LEVEL_TYPE_CODE,
126 ORG_RELATIONSHIP_VIEW,
127 ATTRIBUTE1_CONTEXT,
128 ATTRIBUTE2_CONTEXT,
129 ATTRIBUTE3_CONTEXT,
130 ATTRIBUTE4_CONTEXT,
131 ATTRIBUTE5_CONTEXT,
132 ATTRIBUTE_CATEGORY,
133 ATTRIBUTE1,
134 ATTRIBUTE2,
135 ATTRIBUTE3,
136 ATTRIBUTE4,
137 ATTRIBUTE5,
138 ATTRIBUTE6,
139 ATTRIBUTE7,
140 ATTRIBUTE8,
141 ATTRIBUTE9,
142 ATTRIBUTE10,
143 ATTRIBUTE11,
144 ATTRIBUTE12,
145 ATTRIBUTE13,
146 ATTRIBUTE14,
147 ATTRIBUTE15,
148 CREATION_DATE,
149 CREATED_BY,
150 LAST_UPDATE_DATE,
151 LAST_UPDATED_BY,
152 LAST_UPDATE_LOGIN,
153 SYSTEM_ATTRIBUTE1_CONTEXT,
154 SYSTEM_ATTRIBUTE2_CONTEXT
155 ) values (
156 to_number(X_LEVEL_ID),
157 X_PLAN_TYPE,
158 X_LEVEL_NAME,
159 X_DESCRIPTION,
160 X_DIMENSION_CODE,
161 X_LEVEL_TYPE_CODE,
162 X_ORG_RELATIONSHIP_VIEW,
163 X_ATTRIBUTE1_CONTEXT,
164 X_ATTRIBUTE2_CONTEXT,
165 X_ATTRIBUTE3_CONTEXT,
166 X_ATTRIBUTE4_CONTEXT,
167 X_ATTRIBUTE5_CONTEXT,
168 X_ATTRIBUTE_CATEGORY,
169 X_ATTRIBUTE1,
170 X_ATTRIBUTE2,
171 X_ATTRIBUTE3,
172 X_ATTRIBUTE4,
173 X_ATTRIBUTE5,
174 X_ATTRIBUTE6,
175 X_ATTRIBUTE7,
176 X_ATTRIBUTE8,
177 X_ATTRIBUTE9,
178 X_ATTRIBUTE10,
179 X_ATTRIBUTE11,
180 X_ATTRIBUTE12,
181 X_ATTRIBUTE13,
182 X_ATTRIBUTE14,
183 X_ATTRIBUTE15,
184 f_ludate,
185 f_luby,
186 f_ludate,
187 f_luby,
188 0,
189 X_SYSTEM_ATTRIBUTE1_CONTEXT,
190 X_SYSTEM_ATTRIBUTE2_CONTEXT);
191 end;
192 end LOAD_ROW;
193
194
195 PROCEDURE TRANSLATE_ROW(
196 x_level_id in varchar2,
197 x_plan_type in varchar2,
198 x_level_name varchar2,
199 x_description varchar2,
200 x_last_update_date in varchar2,
201 x_owner in varchar2,
202 x_custom_mode in varchar2) is
203
204 secgrp_id number;
205 view_appid number;
206 owner_id number;
207 ludate date;
208 f_luby number; -- entity owner in file
209 f_ludate date; -- entity update date in file
210 db_luby number; -- entity owner in db
211 db_ludate date; -- entity update date in db
212 begin
213 -- Translate owner to file_last_updated_by
214 if (x_owner = 'SEED') then
215 f_luby := 1;
216 else
217 f_luby := 0;
218 end if;
219
220 -- Translate char last_update_date to date
221 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
222
223 --
224 -- update the translation
225 --
226 begin
227 select LAST_UPDATED_BY, LAST_UPDATE_DATE
228 into db_luby, db_ludate
229 from msd_levels
230 where level_id = to_number(x_level_id) and nvl( PLAN_TYPE, -1) = nvl( x_plan_type , -1) ;
231
232 -- Update record, honoring customization mode.
233 -- Record should be updated only if:
234 -- a. CUSTOM_MODE = FORCE, or
235 -- b. file owner is CUSTOM, db owner is SEED
236 -- c. owners are the same, and file_date > db_date
237 if ((x_custom_mode = 'FORCE') or
238 ((f_luby = 0) and (db_luby = 1)) or
242 set
239 ((f_luby = db_luby) and (f_ludate > db_ludate)))
240 then
241 update msd_levels
243 level_name = nvl(x_level_name, level_name),
244 DESCRIPTION = nvl(x_description, DESCRIPTION),
245 LAST_UPDATE_DATE = f_ludate,
246 LAST_UPDATED_BY = f_luby,
247 LAST_UPDATE_LOGIN = 0
248 where level_id = to_number(x_level_id)
249 and nvl( plan_type, -1) = nvl( x_plan_type , -1)
250 and userenv('LANG') = (select language_code
251 from FND_LANGUAGES
252 where installed_flag = 'B');
253 end if;
254 EXCEPTION
255 when no_data_found then null;
256 end;
257 end TRANSLATE_ROW;
258
259 end MSD_LEVELS_PKG;