[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_DEFN_CLMN_LOAD_DATA
Source
1 package body msd_CS_DEFN_CLMN_load_data as
2 /* $Header: msdcsdcb.pls 115.6 2003/08/18 18:53:04 pinamati ship $ */
3
4 Procedure load_row (
5 p_definition_name in varchar2,
6 p_table_column in varchar2,
7 p_column_identifier in varchar2,
8 p_source_view_column_name in varchar2,
9 p_planning_view_column_name in varchar2,
10 -- p_alt_key_flag in varchar2,
11 p_aggregation_type in varchar2,
12 p_allocation_type in varchar2,
13 p_uom_conversion_flag in varchar2,
14 p_owner in varchar2,
15 p_last_update_date in varchar2,
16 p_custom_mode in varchar2
17 ) is
18 Begin
19 --
20 Update_row(
21 p_definition_name ,
22 p_table_column ,
23 p_column_identifier ,
24 p_source_view_column_name ,
25 p_planning_view_column_name ,
26 -- p_alt_key_flag ,
27 p_aggregation_type ,
28 p_allocation_type ,
29 p_uom_conversion_flag ,
30 p_owner ,
31 p_last_update_date ,
32 p_custom_mode );
33 Exception
34 when no_data_found then
35 Insert_row(
36 p_definition_name ,
37 p_table_column ,
38 p_column_identifier ,
39 p_source_view_column_name ,
40 p_planning_view_column_name ,
41 -- p_alt_key_flag ,
42 p_aggregation_type ,
43 p_allocation_type ,
44 p_uom_conversion_flag ,
45 p_owner ,
46 p_last_update_date );
47
48 End;
49 --
50 --
51 Procedure Update_row (
52 p_definition_name in varchar2,
53 p_table_column in varchar2,
54 p_column_identifier in varchar2,
55 p_source_view_column_name in varchar2,
56 p_planning_view_column_name in varchar2,
57 -- p_alt_key_flag in varchar2,
58 p_aggregation_type in varchar2,
59 p_allocation_type in varchar2,
60 p_uom_conversion_flag in varchar2,
61 p_owner in varchar2,
62 p_last_update_date in varchar2,
63 p_custom_mode in varchar2
64 ) is
65 --
66 --
67 l_user number;
68 l_definition_id number;
69 l_cs_definition_id number;
70 f_ludate date; -- entity update date in file
71 db_luby number; -- entity owner in db
72 db_ludate date; -- entity update date in db
73 --
74 cursor c1 is
75 select
76 cs_definition_id
77 from
78 msd_cs_definitions
79 where
80 name = p_definition_name;
81
82 cursor c2 (p_id in number, p_column in varchar2) is
83 select last_updated_by,
84 last_update_date
85 from msd_cs_defn_column_dtls
86 where cs_definition_id = p_id
87 and table_column = p_column;
88
89 Begin
90 if p_owner = 'SEED' then
91 l_user := 1;
92 else
93 l_user := 0;
94 end if;
95 --
96 open c1;
97 fetch c1 into l_cs_definition_id;
98 close c1;
99 --
100 if l_cs_definition_id is null then
101 fnd_message.set_name('MSD', 'MSD_CS_LOAD_INVALID_DEFN');
102 fnd_message.raise_error;
103 end if;
104 --
105 open c2(l_cs_definition_id, p_table_column);
106 fetch c2 into db_luby, db_ludate;
107
108 if (c2%notfound) then
109 raise no_data_found;
110 end if;
111
112 close c2;
113
114 -- Translate char last_update_date to date
115 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
116 --
117 -- Update record, honoring customization mode.
118 -- Record should be updated only if:
119 -- a. CUSTOM_MODE = FORCE, or
120 -- b. file owner is CUSTOM, db owner is SEED
121 -- c. owners are the same, and file_date > db_date
122 if ((p_custom_mode = 'FORCE') or
123 ((l_user = 0) and (db_luby = 1)) or
124 ((l_user = db_luby) and (f_ludate > db_ludate)))
125 then
126 update MSD_CS_DEFN_COLUMN_DTLS set
127 source_view_column_name = p_source_view_column_name,
128 planning_view_column_name = p_planning_view_column_name,
129 column_identifier = p_column_identifier,
130 -- alt_key_flag = p_alt_key_flag,
131 uom_conversion_flag = p_uom_conversion_flag,
132 aggregation_type = p_aggregation_type,
133 allocation_type = p_allocation_type,
134 last_updated_by = l_user,
135 last_update_date = f_ludate
136 where
137 cs_definition_id = l_cs_definition_id and
138 table_column = p_table_column;
139 --
140 end if;
141 if (sql%notfound) then
142 raise no_data_found;
143 end if;
144 --
145
146 End;
147 --
148 Procedure Insert_row (
149 p_definition_name in varchar2,
150 p_table_column in varchar2,
151 p_column_identifier in varchar2,
152 p_source_view_column_name in varchar2,
153 p_planning_view_column_name in varchar2,
154 -- p_alt_key_flag in varchar2,
155 p_aggregation_type in varchar2,
156 p_allocation_type in varchar2,
157 p_uom_conversion_flag in varchar2,
158 p_owner in varchar2,
159 p_last_update_date in varchar2
160 ) is
161 --
162 --
163 l_user number;
164 l_cs_column_dtls_id number;
165 l_cs_definition_id number;
166 f_ludate date; -- entity update date in file
167
168 cursor c1 is
169 select
170 cs_definition_id
171 from
172 msd_cs_definitions
173 where
174 name = p_definition_name;
175 Begin
176 if p_owner = 'SEED' then
177 l_user := 1;
178 else
179 l_user := 0;
180 end if;
181 --
182 open c1;
183 fetch c1 into l_cs_definition_id;
184 close c1;
185 --
186 if l_cs_definition_id is null then
187 fnd_message.set_name('MSD', 'MSD_CS_LOAD_INVALID_DEFN');
188 fnd_message.raise_error;
189 end if;
190 -- Translate char last_update_date to date
191 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
192 --
193 select MSD_CS_DEFN_COLUMN_DTLS_S.nextval into l_cs_column_dtls_id from dual;
194 --
195 insert into MSD_CS_DEFN_COLUMN_DTLS(
196 cs_column_dtls_id,
197 cs_definition_id,
198 source_view_column_name,
199 planning_view_column_name,
200 column_identifier,
201 table_column,
202 -- alt_key_flag,
203 uom_conversion_flag,
204 aggregation_type,
205 allocation_type,
206 created_by,
207 creation_date,
208 last_updated_by,
209 last_update_date ,
210 last_update_login
211 )
212 values
213 (
214 l_cs_column_dtls_id,
215 l_cs_definition_id,
216 p_source_view_column_name,
217 p_planning_view_column_name,
218 p_column_identifier,
219 p_table_column,
220 -- p_alt_key_flag,
221 p_uom_conversion_flag,
222 p_aggregation_type,
223 p_allocation_type,
224 l_user,
225 f_ludate,
226 l_user,
227 f_ludate,
228 fnd_global.login_id
229 );
230 --
231 End;
232 --
233 End;