APPS.BIM_SEGMENT_DENORM_PKG dependencies on BIM_I_SGMT_DENORM

Line 263: Execute Immediate 'Truncate Table '||l_schema||'.bim_i_sgmt_denorm';

259: bis_collection_utilities.log('Truncating the Denorm Table ');
260:
261: l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
262:
263: Execute Immediate 'Truncate Table '||l_schema||'.bim_i_sgmt_denorm';
264:
265: BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SEGMENT_DENORM_PKG');
266:
267: COMMON_UTILITIES(l_date,l_period_from,l_period_to,l_temp_start_date,l_start_date,l_end_date);

Line 273: DELETE bim_i_sgmt_denorm

269: ELSE
270:
271: COMMON_UTILITIES(l_date,l_period_from,l_period_to,l_temp_start_date,l_start_date,l_end_date);
272:
273: DELETE bim_i_sgmt_denorm
274: WHERE segment_id IN
275: (SELECT cell_id
276: FROM ams_cells_all_b a
277: WHERE last_update_date > l_temp_start_date

Line 282: DELETE bim_i_sgmt_denorm

278: );
279:
280: /* This Delete Statement for the objects that had an update somewhere in their hierarachy chain.*/
281:
282: DELETE bim_i_sgmt_denorm
283: WHERE segment_id IN (SELECT segment_id
284: FROM bim_i_sgmt_denorm
285: WHERE parent_segment_id in (SELECT b.segment_id
286: FROM ams_cells_all_b a

Line 284: FROM bim_i_sgmt_denorm

280: /* This Delete Statement for the objects that had an update somewhere in their hierarachy chain.*/
281:
282: DELETE bim_i_sgmt_denorm
283: WHERE segment_id IN (SELECT segment_id
284: FROM bim_i_sgmt_denorm
285: WHERE parent_segment_id in (SELECT b.segment_id
286: FROM ams_cells_all_b a
287: ,bim_i_sgmt_denorm b
288: WHERE b.segment_id = a.cell_id

Line 287: ,bim_i_sgmt_denorm b

283: WHERE segment_id IN (SELECT segment_id
284: FROM bim_i_sgmt_denorm
285: WHERE parent_segment_id in (SELECT b.segment_id
286: FROM ams_cells_all_b a
287: ,bim_i_sgmt_denorm b
288: WHERE b.segment_id = a.cell_id
289: AND a.last_update_date > l_temp_start_date
290: )
291: );

Line 302: UPDATE bim_i_sgmt_denorm

298:
299: bis_collection_utilities.log('Updating leaf node flags for incremental load ');
300:
301:
302: UPDATE bim_i_sgmt_denorm
303: SET leaf_node_flag = 'N'
304: WHERE segment_id IN (SELECT parent_cell_id
305: FROM ams_cells_all_b a
306: WHERE NOT EXISTS (SELECT 1 FROM bim_i_sgmt_denorm b WHERE b.segment_id = a.cell_id)

Line 306: WHERE NOT EXISTS (SELECT 1 FROM bim_i_sgmt_denorm b WHERE b.segment_id = a.cell_id)

302: UPDATE bim_i_sgmt_denorm
303: SET leaf_node_flag = 'N'
304: WHERE segment_id IN (SELECT parent_cell_id
305: FROM ams_cells_all_b a
306: WHERE NOT EXISTS (SELECT 1 FROM bim_i_sgmt_denorm b WHERE b.segment_id = a.cell_id)
307: AND object_level = 1
308: AND a.last_update_date > l_temp_start_date
309: );
310:

Line 314: Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||

310:
311:
312: /**********************FOR INCREMENT LOAD**********************/
313: /**************************************************************/
314: Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
315: ' ( segment_id ' ||
316: ' ,parent_segment_id ' ||
317: ' ,immediate_parent_flag ' ||
318: ' ,immediate_parent_id ' ||

Line 372: ' WHERE NOT EXISTS ( SELECT 1 FROM bim_i_sgmt_denorm b ' ||

368: ' AND b.based_on_tca_flag = ''Y'' ' ||
369: ' AND a.sel_type=''DIWB'' ' ||
370: ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
371: ' ) a ' ||
372: ' WHERE NOT EXISTS ( SELECT 1 FROM bim_i_sgmt_denorm b ' ||
373: ' WHERE b.segment_id = a.cell_id ) ' ||
374: ' CONNECT BY PRIOR cell_id = parent_cell_id ' ||
375: ' )x, ams_cells_all_b s ' ||
376: ' WHERE s.cell_id = x.parent_segment_id' ;

Line 387: Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||

383:
384: /**********************FOR INITITAL LOAD**********************/
385: bis_collection_utilities.log('Initial Load of Segment Denorm Program');
386:
387: Execute Immediate ' INSERT INTO bim_i_sgmt_denorm ' ||
388: ' ( segment_id ' ||
389: ' ,parent_segment_id ' ||
390: ' ,immediate_parent_flag ' ||
391: ' ,immediate_parent_id ' ||