[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR select_activity_obj_cur IS
SELECT column_name,
DECODE(column_name,'TASK_ID', 'Task',
'COMPANY_COST_CENTER_ORG_ID', 'Organization',
'CUSTOMER_ID', 'Customer',
'CHANNEL_ID', 'Channel',
'PRODUCT_ID', 'Product',
'PROJECT_ID', 'Project',
'USER_DIM1_ID', 'User dimension 1',
'USER_DIM2_ID', 'User dimension 2',
'USER_DIM3_ID', 'User dimension 3',
'USER_DIM4_ID', 'User dimension 4',
'USER_DIM5_ID', 'User dimension 5',
'USER_DIM6_ID', 'User dimension 6',
'USER_DIM7_ID', 'User dimension 7',
'USER_DIM8_ID', 'User dimension 8',
'USER_DIM9_ID', 'User dimension 9',
'USER_DIM10_ID', 'User dimension 10',
'UNKNOWN') user_column_name,
DECODE(column_name,'TASK_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT1','UNKNOWN'),
'COMPANY_COST_CENTER_ORG_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT2','UNKNOWN'),
'CUSTOMER_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT3','UNKNOWN'),
'CHANNEL_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT4','UNKNOWN'),
'PRODUCT_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT5','UNKNOWN'),
'PROJECT_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT6','UNKNOWN'),
'USER_DIM1_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT7','UNKNOWN'),
'USER_DIM2_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT8','UNKNOWN'),
'USER_DIM3_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT9','UNKNOWN'),
'USER_DIM4_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT10','UNKNOWN'),
'USER_DIM5_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT11','UNKNOWN'),
'USER_DIM6_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT12','UNKNOWN'),
'USER_DIM7_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT13','UNKNOWN'),
'USER_DIM8_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT14','UNKNOWN'),
'USER_DIM9_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT15','UNKNOWN'),
'USER_DIM10_ID', DECODE(activity_dim_component_flag,'Y','SEGMENT16','UNKNOWN'),
'UNKNOWN') act_segment_num,
vs_member_vl_object_name vs_name
FROM fem_column_requiremnt_b fcr,
fem_xdim_dimensions fxd
WHERE fcr.dimension_id = fxd.dimension_id
AND fcr.activity_dim_component_flag = 'Y'
ORDER BY act_segment_num; --Bug#4209065
FOR sel_act_cost_obj_rec IN select_activity_obj_cur
LOOP
IF sel_act_cost_obj_rec.user_column_name <> 'UNKNOWN' THEN
l_column_name := sel_act_cost_obj_rec.column_name;
CURSOR select_cost_obj_cur IS
SELECT column_name,
DECODE(column_name,'FINANCIAL_ELEM_ID', 'Financial Element',
'LEDGER_ID', 'Ledger',
'PRODUCT_ID', 'Product',
'COMPANY_COST_CENTER_ORG_ID', 'Organization',
'CUSTOMER_ID', 'Customer',
'CHANNEL_ID', 'Channel',
'PROJECT_ID', 'Project',
'USER_DIM1_ID', 'User dimension 1',
'USER_DIM2_ID', 'User dimension 2',
'USER_DIM3_ID', 'User dimension 3',
'USER_DIM4_ID', 'User dimension 4',
'USER_DIM5_ID', 'User dimension 5',
'USER_DIM6_ID', 'User dimension 6',
'USER_DIM7_ID', 'User dimension 7',
'USER_DIM8_ID', 'User dimension 8',
'USER_DIM9_ID', 'User dimension 9',
'USER_DIM10_ID', 'User dimension 10',
'UNKNOWN') user_column_name,
DECODE(column_name,'FINANCIAL_ELEM_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT1','UNKNOWN'),
'LEDGER_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT2','UNKNOWN'),
'PRODUCT_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT3','UNKNOWN'),
'COMPANY_COST_CENTER_ORG_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT4','UNKNOWN'),
'CUSTOMER_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT5','UNKNOWN'),
'CHANNEL_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT6','UNKNOWN'),
'PROJECT_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT7','UNKNOWN'),
'USER_DIM1_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT8','UNKNOWN'),
'USER_DIM2_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT9','UNKNOWN'),
'USER_DIM3_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT10','UNKNOWN'),
'USER_DIM4_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT11','UNKNOWN'),
'USER_DIM5_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT12','UNKNOWN'),
'USER_DIM6_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT13','UNKNOWN'),
'USER_DIM7_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT14','UNKNOWN'),
'USER_DIM8_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT15','UNKNOWN'),
'USER_DIM9_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT16','UNKNOWN'),
'USER_DIM10_ID', DECODE(cost_obj_dim_component_flag,'Y','SEGMENT17','UNKNOWN'),
'UNKNOWN') cost_segment_num,
vs_member_vl_object_name vs_name
FROM fem_column_requiremnt_b fcr,
fem_xdim_dimensions fxd
WHERE fcr.dimension_id = fxd.dimension_id
AND cost_obj_dim_component_flag = 'Y'
ORDER BY cost_segment_num; --Bug#4209065
FOR sel_act_cost_obj_rec IN select_cost_obj_cur
LOOP
IF sel_act_cost_obj_rec.user_column_name <> 'UNKNOWN' THEN
l_column_name := sel_act_cost_obj_rec.column_name;
SELECT id_flex_num,id_flex_structure_code
FROM fnd_id_flex_structures
WHERE application_id = 274
AND id_flex_code = p_ff_name;
dynamic_insert_flag => 'Y',
shorthand_enabled_flag => 'N',
shorthand_prompt => '',
shorthand_length => NULL);
UPDATE fem_xdim_dimensions
SET id_flex_num = l_flex_num ,
id_flex_structure_code = l_flex_structure_code,
id_flex_code= p_ff_name
WHERE dimension_id = 10;
UPDATE fem_xdim_dimensions
SET id_flex_num = l_flex_num ,
id_flex_structure_code = l_flex_structure_code,
id_flex_code= p_ff_name
WHERE dimension_id = 11;
SELECT column_name
BULK COLLECT INTO proc_list_rec
FROM fem_tab_column_prop
WHERE table_name = p_table_name
AND column_property_code = 'PROCESSING_KEY';
SELECT comp.column_name
FROM fem_column_requiremnt_b comp
WHERE comp.activity_dim_component_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM fem_tab_column_prop proc
WHERE proc.table_name = p_table_name
AND proc.column_property_code = 'PROCESSING_KEY'
AND proc.column_name = comp.column_name
);
SELECT comp.column_name
FROM fem_column_requiremnt_b comp
WHERE comp.cost_obj_dim_component_flag = 'Y'
AND NOT EXISTS (
SELECT 1
FROM fem_tab_column_prop proc
WHERE proc.table_name = p_table_name
AND proc.column_property_code = 'PROCESSING_KEY'
AND proc.column_name = comp.column_name
);
| delete_flexfield
|
| DESCRIPTION
|
| This procedure is used to delete the flex field structure which
| was created after freezing the FF definition.
|
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS :
|
| p_dimension_varchar_label - ACTIVITY/COST_OBJECT
|
| RETURNS : NONE
|
| NOTES
|
|
| MODIFICATION HISTORY
| 18-May-06 sshanmug Bug:5224927: Flexfield Delete
| API for EPF Refresh Engine.
|
+===========================================================================*/
PROCEDURE delete_flexfield(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_encoded IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_dimension_varchar_label IN VARCHAR2)
AS
l_api_version NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'delete_flexfield';
fnd_flex_key_api.delete_flexfield('FEM','FEAC');
fnd_flex_key_api.delete_flexfield('FEM','FECO');
END delete_flexfield;