[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_CLMN_DIM_LOAD_DATA
Source
1 package body msd_CS_CLMN_DIM_load_data as
2 /* $Header: msdcscdb.pls 120.2 2006/06/02 08:58:48 brampall noship $ */
3
4 Procedure load_row (
5 p_definition_name in varchar2,
6 p_dimension_code in varchar2,
7 p_table_column in varchar2,
8 p_aggregation_type in varchar2,
9 p_allocation_type in varchar2,
10 p_owner in varchar2,
11 p_last_update_date in varchar2,
12 p_custom_mode in varchar2
13 ) is
14 Begin
15 --
16 Update_row(
17 p_definition_name ,
18 p_dimension_code ,
19 p_table_column ,
20 p_aggregation_type ,
21 p_allocation_type ,
22 p_owner ,
23 p_last_update_date ,
24 p_custom_mode );
25 Exception
26 when no_data_found then
27 Insert_row(
28 p_definition_name ,
29 p_dimension_code ,
30 p_table_column ,
31 p_aggregation_type ,
32 p_allocation_type ,
33 p_owner ,
34 p_last_update_date );
35 End;
36 --
37 --
38 Procedure Update_row (
39 p_definition_name in varchar2,
40 p_dimension_code in varchar2,
41 p_table_column in varchar2,
42 p_aggregation_type in varchar2,
43 p_allocation_type in varchar2,
44 p_owner in varchar2,
45 p_last_update_date in varchar2,
46 p_custom_mode in varchar2
47 ) is
48 --
49 --
50 l_user number;
51 l_cs_id number;
52
53 cursor c1 is
54 select cs_clmn_dim_dtls_id,
55 last_updated_by,
56 last_update_date
57 from MSD_CS_CLMN_DIM_DTLS_V
58 where
59 cs_definition_id = (select cs_definition_id from msd_cs_definitions where name = p_definition_name) and
60 table_column = p_table_column and
61 dimension_code = p_dimension_code;
62
63 f_ludate date; -- entity update date in file
64 db_luby number; -- entity owner in db
65 db_ludate date; -- entity update date in db
66 --
67 Begin
68 --
69 if p_owner = 'SEED' then
70 l_user := 1;
71 else
72 l_user := 0;
73 end if;
74 --
75 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
76 --
77 open c1;
78 fetch c1 into l_cs_id, db_luby, db_ludate;
79 close c1;
80 --
81 if l_cs_id is null then
82 raise no_data_found;
83 end if;
84 --
85 -- Update record, honoring customization mode.
86 -- Record should be updated only if:
87 -- a. CUSTOM_MODE = FORCE, or
88 -- b. file owner is CUSTOM, db owner is SEED
89 -- c. owners are the same, and file_date > db_date
90 if ((p_custom_mode = 'FORCE') or
91 ((l_user = 0) and (db_luby = 1)) or
92 ((l_user = db_luby) and (f_ludate > db_ludate)))
93 then
94
95 update MSD_CS_CLMN_DIM_DTLS set
96 aggregation_type = p_aggregation_type,
97 allocation_type = p_allocation_type,
98 last_updated_by = l_user,
99 last_update_date = f_ludate
100 where
101 cs_clmn_dim_dtls_id = l_cs_id;
102
103 end if;
104 --
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108 --
109
110 End;
111 --
112 Procedure Insert_row (
113 p_definition_name in varchar2,
114 p_dimension_code in varchar2,
115 p_table_column in varchar2,
116 p_aggregation_type in varchar2,
117 p_allocation_type in varchar2,
118 p_owner in varchar2,
119 p_last_update_date in varchar2
120 ) is
121 --
122 --
123 l_user number;
124 --
125 l_cs_id1 number;
126 l_cs_id2 number;
127 l_pk_id number;
128 f_ludate date; -- entity update date in file
129 --
130 cursor c1 is
131 select cs_defn_dim_dtls_id
132 from msd_cs_defn_dim_dtls
133 where
134 cs_definition_id = (select cs_definition_id from msd_cs_definitions where name = p_definition_name) and
135 dimension_code = p_dimension_code;
136 --
137 cursor c2 is
138 select cs_column_dtls_id
139 from msd_cs_defn_column_dtls
140 where cs_definition_id = (select cs_definition_id from msd_cs_definitions where name =p_definition_name)
141 and table_column = p_table_column;
142
143 Begin
144 --
145 if p_owner = 'SEED' then
146 l_user := 1;
147 else
148 l_user := 0;
149 end if;
150 --
151 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
152 --
153 open c1;
154 fetch c1 into l_cs_id1;
155 close c1;
156 --
157 open c2;
158 fetch c2 into l_cs_id2;
159 close c2;
160 --
161 if l_cs_id1 is null or l_cs_id2 is null then
162 raise NO_DATA_FOUND;
163 end if;
164 --
165 select MSD_CS_CLMN_DIM_DTLS_S.nextval into l_PK_id from dual;
166 --
167 insert into MSD_CS_CLMN_DIM_DTLS(
168 cs_clmn_dim_dtls_id,
169 cs_defn_dim_dtls_id,
170 cs_column_dtls_id,
171 aggregation_type,
172 allocation_type,
173 created_by,
174 creation_date,
175 last_updated_by,
176 last_update_date ,
177 last_update_login
178 )
179 values
180 (
181 l_pk_id,
182 l_cs_id1,
183 l_cs_id2,
184 p_aggregation_type,
185 p_allocation_type,
186 l_user,
187 f_ludate,
188 l_user,
189 f_ludate,
190 fnd_global.login_id
191 );
192 --
193 End;
194 --
195 End;