[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_LAB_BLG_ETL_PKG
Source
1 PACKAGE BODY ISC_MAINT_LAB_BLG_ETL_PKG as
2 /*$Header: iscmaintlblgetlb.pls 120.3 2006/07/26 03:48:27 kreardon noship $ */
3 g_pkg_name constant varchar2(30) := 'isc_maint_lab_blg_etl_pkg';
4 g_user_id number;
5 g_login_id number;
6 g_program_id number;
7 g_program_login_id number;
8 g_program_application_id number;
9 g_request_id number;
10 g_success constant varchar2(10) := '0';
11 g_error constant varchar2(10) := '-1';
12 g_warning constant varchar2(10) := '1';
13 g_bis_setup_exception exception;
14 g_global_start_date date;
15 g_object_name constant varchar2(30) := 'ISC_MAINT_LAB_BLG_F';
16 g_max_date constant date := to_date('4712/01/01','yyyy/mm/dd');
17 g_bom_hour_code varchar2(50);
18 g_bom_time_class varchar2(50);
19 g_time_base_to_hours number;
20 procedure local_init
21 as
22 cursor c_time_base is
23 select 1 / decode( conversion_rate
24 , 0 , 1 -- prevent "divide by zero" error
25 , conversion_rate )
26 from mtl_uom_conversions
27 where uom_class = g_bom_time_class
28 and uom_code = g_bom_hour_code
29 and inventory_item_id = 0;
30 begin
31 g_user_id := fnd_global.user_id;
32 g_login_id := fnd_global.login_id;
33 g_program_id := fnd_global.conc_program_id;
34 g_program_login_id := fnd_global.conc_login_id;
35 g_program_application_id := fnd_global.prog_appl_id;
36 g_request_id := fnd_global.conc_request_id;
37 g_global_start_date := bis_common_parameters.get_global_start_date;
38 g_bom_hour_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
39 g_bom_time_class := fnd_profile.value('BOM:TIME_UOM_CLASS');
40
41 bis_collection_utilities.log('BOM:HOUR_UOM_CODE -> ' || g_bom_hour_code, 3);
42 bis_collection_utilities.log('BOM:TIME_UOM_CLASS -> ' || g_bom_time_class, 3);
43
44 -- the base UOM_CODE for the UOM_CLASS may not be the same as the
45 -- UOM_CODE for "hours". We need to convert everything to "hours"
46 -- so we
47 open c_time_base;
48 fetch c_time_base into g_time_base_to_hours;
49 close c_time_base;
50
51 bis_collection_utilities.log('TIME_BASE_TO_HOURS -> ' || g_time_base_to_hours, 3);
52
53 end local_init;
54
55 procedure logger
56 ( p_proc_name varchar2
57 , p_stmt_id number
58 , p_message varchar2
59 )
60 as
61
62 begin
63
64 bis_collection_utilities.log
65 ( substr( g_pkg_name || '.' || p_proc_name || ' #' || p_stmt_id || p_message
66 , 1
67 , 1991 -- [2000 - (3*3)]
68 )
69 , 3
70 );
71
72 end logger;
73
74 function get_schema_name
75 ( x_schema_name out nocopy varchar2
76 , x_error_message out nocopy varchar2 )
77 return number as
78 l_biv_schema varchar2(30);
79 l_status varchar2(30);
80 l_industry varchar2(30);
81 begin
82 if(fnd_installation.get_app_info('ISC', l_status, l_industry, l_biv_schema)) then
83 x_schema_name := l_biv_schema;
84 else
85 x_error_message := 'FIND_INSTALLATION.GET_APP_INFO returned false';
86 return -1;
87 end if;
88 return 0;
89 exception
90 when others then
91 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
92 return -1;
93 end get_schema_name;
94
95
96 function truncate_table
97 ( p_biv_schema in varchar2
98 , p_table_name in varchar2
99 , x_error_message out nocopy varchar2 )
100 return number as
101 begin
102 execute immediate 'truncate table ' || p_biv_schema || '.' || p_table_name;
103 return 0;
104
105 exception
106 when others then
107 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
108 return -1;
109 end truncate_table;
110
111
112
113
114
115 ------------------------------------------- Public procedures---------------------------------------------------------------
116 procedure load
117 ( errbuf out nocopy varchar2
118 , retcode out nocopy number
119 )
120 as
121 l_proc_name constant varchar2(30) := 'load';
122 l_stmt_id number;
123 l_exception exception;
124 l_error_message varchar2(4000);
125 l_biv_schema varchar2(100);
126 l_timer number;
127 l_rowcount number;
128 l_temp_rowcount number;
129 l_collect_from_date date;
130 l_collect_to_date date;
131 type t_number_tab is table of number;
132 l_organization_tbl t_number_tab;
133 l_work_order_tbl t_number_tab;
134
135 begin
136
137 bis_collection_utilities.log('Begin Refresh');
138 local_init;
139 l_stmt_id := 0;
140 if not bis_collection_utilities.setup( g_object_name ) then
141 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
142 logger( l_proc_name, l_stmt_id, l_error_message );
143 raise g_bis_setup_exception;
144 end if;
145
146 l_stmt_id := 10;
147
148 if g_global_start_date is null then
149 l_error_message := 'Unable to get DBI global start date.';
150 logger( l_proc_name, l_stmt_id, l_error_message );
151 raise l_exception;
152 end if;
153
154 l_collect_from_date := g_global_start_date;
155 l_collect_to_date := sysdate;
156
157
158 -- get the biv schema name
159 l_stmt_id := 20;
160
161 if get_schema_name
162 ( l_biv_schema
163 , l_error_message ) <> 0 then
164 logger( l_proc_name, l_stmt_id, l_error_message );
165 raise l_exception;
166 end if;
167
168 -- truncate the Fact table
169
170 l_stmt_id := 30;
171
172 if truncate_table
173 ( l_biv_schema
174 , 'ISC_MAINT_LAB_BLG_F'
175 , l_error_message ) <> 0 then
176 logger( l_proc_name, l_stmt_id, l_error_message );
177 raise l_exception;
178 end if;
179 bis_collection_utilities.log('Base Summary table Truncated',1);
180
181 --------------------------------------------- load ----------------------------------
182
183 -- This inserts all the instances of the labor backlog into the fact table.
184 -- The join wiht the ISC_MAINT_WORK_ORDER_F ensures all the work orders bucketed by the
185 -- Global start date criteria.
186 ---------------------------------------------the load query would come here----------
187
188
189
190
191 l_stmt_id := 40;
192
193 ------starts here------
194 Insert /*+ append parallel (ISC_MAINT_LAB_BLG_F) */
195 into ISC_MAINT_LAB_BLG_F
196 (
197 organization_id
198 ,user_defined_status_id /* added user_defined work order status */
199 ,work_order_name
200 ,work_order_id
201 ,resource_id
202 ,department_id
203 ,operation_seq_number
204 ,op_start_date
205 ,op_end_date
206 ,hours_required
207 ,hours_charged
208 ,creation_date
209 ,created_by
210 ,last_update_date
211 ,last_updated_by
212 ,last_update_login
213 ,program_id
214 ,program_login_id
215 ,program_application_id
216 ,request_id
217 )
218 select /*+ parallel(BR) parallel(FACT) parallel(WO) parallel(WOR)
219 parallel(M1) parallel(M2) use_hash(WOR) use_hash(WO)
220 pq_distribute(WO,hash,hash) pq_distribute(WOR,hash,hash) */
221 fact.organization_id organization_id
222 ,fact.user_defined_Status_id user_defined_status_id
223 ,fact.work_order_name work_order_name
224 ,fact.work_order_id work_order_id
225 ,WOR.RESOURCE_ID resource_id
226 ,WO.DEPARTMENT_ID department_id
227 ,WO.OPERATION_SEQ_NUM operation_seq_number
228 ,WO.first_unit_start_date op_start_date
229 ,WO.first_unit_completion_date op_end_date
230 ,WOR.usage_rate_or_amount*m1.conversion_rate*g_time_base_to_hours
231 hours_required
232 ,WOR.applied_resource_units*m1.conversion_rate*g_time_base_to_hours
233 hours_charged
234 ,sysdate creation_date
235 ,g_user_id created_by
236 ,sysdate last_update_date
237 ,g_user_id last_updated_by
238 ,g_login_id last_update_login
239 ,g_program_id program_id
240 ,g_program_login_id program_login_id
241 ,g_program_application_id program_application_id
242 ,g_request_id request_id
243
244
245 from
246 WIP_OPERATIONS wo
247 ,WIP_OPERATION_RESOURCES wor
248 ,BOM_RESOURCES br
249 ,ISC_MAINT_WORK_ORDERS_F fact -- get only the work orders that satisfy the G_start_date criteria
250 ,mtl_uom_conversions m1
251
252 where
253 fact.status_type in (17,6,3,1) and --to get the work orders in the status draft,released,unreleased and on-hold
254 fact.organization_id = wo.organization_id and -- to get the same organizational work orders.
255 fact.organization_id = wor.organization_id and -- to get the same organizational work orders.
256 fact.organization_id = br.organization_id and -- to get the same organizational work orders.
257 -- departmental level join not required as it is not specified in the join.
258 -- courtesy etrm (wip_operation_resources)
259 nvl(wo.operation_completed,'N') = 'N' and -- to get the non completed operations
260 fact.work_order_id = wo.wip_entity_id and -- to get the same work orders.
261 fact.work_order_id = wor.wip_entity_id and -- to get the same work orders.
262 wo.operation_seq_num = wor.operation_seq_num and -- to get the resource
263 wor.resource_id = br.resource_id and -- to get the resource
264 br.resource_type = 2 and -- only labor
265 WOR.usage_rate_or_amount > WOR.applied_resource_units and -- backlog indicator.
266 m1.inventory_item_id = 0 and
267 m1.uom_code = br.unit_of_measure and
268 m1.uom_class = g_bom_time_class;
269
270
271
272
273
274 l_rowcount := sql%rowcount;
275
276 commit;
277
278 ----ends here------
279
280
281 l_stmt_id := 90;
282
283 bis_collection_utilities.log(l_rowcount||' rows inserted into the Base Summary Table',1);
284 bis_collection_utilities.log('End Refresh ');
285
286 bis_collection_utilities.wrapup( p_status => true
287 , p_period_from => l_collect_from_date
288 , p_period_to => l_collect_to_date
289 , p_count => l_rowcount
290 );
291
292
293 errbuf := null;
294 retcode := g_success;
295
296 exception
297 when g_bis_setup_exception then
298 rollback;
299 errbuf := l_error_message;
300 retcode := g_error;
301 when others then
302 rollback;
303
304 if l_error_message is null then
305 l_error_message := substr(sqlerrm,1,4000);
306 end if;
307
308 logger( l_proc_name, l_stmt_id, l_error_message );
309 bis_collection_utilities.wrapup( p_status => false
310 , p_message => l_error_message
311 , p_period_from => l_collect_from_date
312 , p_period_to => l_collect_to_date
313 );
314
315 errbuf := l_error_message;
316 retcode := g_error;
317
318 end load; --- end load
319
320 end isc_maint_lab_blg_etl_pkg;
321