[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 08-JUN-07 nmartine Update for bug 6052152 to only update ledgers with
| the given Global Value Set Combo ID. Also handles
| the added GLOBAL_VS_COMBO_ID column.
+=========================================================================*/
-----------------------
-- Package Constants --
-----------------------
c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
SELECT D.dimension_varchar_label, G.value_set_id
FROM fem_dimensions_b D, fem_global_vs_combo_defs G
WHERE G.dimension_id = D.dimension_id
AND G.global_vs_combo_id = p_global_vs_combo_id;
select ledger_id
from fem_ledgers_attr
where attribute_id = p_attribute_id
and version_id = p_version_id
and dim_attribute_numeric_member = p_global_vs_combo_id;
SELECT 1
INTO v_count
FROM fem_global_vs_combos_vl
WHERE global_vs_combo_id = p_global_vs_combo_id;
SELECT attribute_id
INTO v_attribute_id
FROM fem_dim_attributes_b A, fem_dimensions_b D
WHERE D.dimension_varchar_label = 'LEDGER'
AND D.dimension_id = A.dimension_id
AND A.attribute_varchar_label = 'GLOBAL_VS_COMBO';
SELECT version_id
INTO v_version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attribute_id
AND default_version_flag = 'Y';
USING (SELECT
ledger.ledger_id as ledger_id
,p_global_vs_combo_id as global_vs_combo
,v_channel_vs_id as channel
,v_cctr_org_vs_id as cctr
,v_company_vs_id as company
,v_cost_ctr_vs_id as cost_ctr
,v_customer_vs_id as customer
,v_entity_vs_id as entity
,v_fin_elem_vs_id as fin_elem
,v_geography_vs_id as geography
,v_line_item_vs_id as line_item
,v_natural_account_vs_id as natural_acct
,v_product_vs_id as product
,v_project_vs_id as project
,v_task_vs_id as task
,v_user_dim1_vs_id as user_dim1
,v_user_dim2_vs_id as user_dim2
,v_user_dim3_vs_id as user_dim3
,v_user_dim4_vs_id as user_dim4
,v_user_dim5_vs_id as user_dim5
,v_user_dim6_vs_id as user_dim6
,v_user_dim7_vs_id as user_dim7
,v_user_dim8_vs_id as user_dim8
,v_user_dim9_vs_id as user_dim9
,v_user_dim10_vs_id as user_dim10
FROM dual) A
ON (A.ledger_id = L.ledger_id)
WHEN MATCHED THEN UPDATE SET
L.GLOBAL_VS_COMBO_ID = p_global_vs_combo_id,
L.CHANNEL_VS_ID = v_channel_vs_id,
L.COMPANY_COST_CENTER_ORG_VS_ID = v_cctr_org_vs_id,
L.COMPANY_VS_ID = v_company_vs_id,
L.COST_CENTER_VS_ID = v_cost_ctr_vs_id,
L.CUSTOMER_VS_ID = v_customer_vs_id,
L.ENTITY_VS_ID = v_entity_vs_id,
L.FINANCIAL_ELEM_VS_ID = v_fin_elem_vs_id,
L.GEOGRAPHY_VS_ID = v_geography_vs_id,
L.LINE_ITEM_VS_ID = v_line_item_vs_id,
L.NATURAL_ACCOUNT_VS_ID = v_natural_account_vs_id,
L.PRODUCT_VS_ID = v_product_vs_id,
L.PROJECT_VS_ID = v_project_vs_id,
L.TASK_VS_ID = v_task_vs_id,
L.USER_DIM1_VS_ID = v_user_dim1_vs_id,
L.USER_DIM2_VS_ID = v_user_dim2_vs_id,
L.USER_DIM3_VS_ID = v_user_dim3_vs_id,
L.USER_DIM4_VS_ID = v_user_dim4_vs_id,
L.USER_DIM5_VS_ID = v_user_dim5_vs_id,
L.USER_DIM6_VS_ID = v_user_dim6_vs_id,
L.USER_DIM7_VS_ID = v_user_dim7_vs_id,
L.USER_DIM8_VS_ID = v_user_dim8_vs_id,
L.USER_DIM9_VS_ID = v_user_dim9_vs_id,
L.USER_DIM10_VS_ID= v_user_dim10_vs_id
WHEN NOT MATCHED THEN INSERT (
L.LEDGER_ID,
L.GLOBAL_VS_COMBO_ID,
L.CHANNEL_VS_ID,
L.COMPANY_COST_CENTER_ORG_VS_ID,
L.COMPANY_VS_ID,
L.COST_CENTER_VS_ID,
L.CUSTOMER_VS_ID,
L.ENTITY_VS_ID,
L.FINANCIAL_ELEM_VS_ID,
L.GEOGRAPHY_VS_ID,
L.LINE_ITEM_VS_ID,
L.NATURAL_ACCOUNT_VS_ID,
L.PRODUCT_VS_ID,
L.PROJECT_VS_ID,
L.TASK_VS_ID,
L.USER_DIM1_VS_ID,
L.USER_DIM2_VS_ID,
L.USER_DIM3_VS_ID,
L.USER_DIM4_VS_ID,
L.USER_DIM5_VS_ID,
L.USER_DIM6_VS_ID,
L.USER_DIM7_VS_ID,
L.USER_DIM8_VS_ID,
L.USER_DIM9_VS_ID,
L.USER_DIM10_VS_ID
)
VALUES (ledger.ledger_id
,p_global_vs_combo_id
,v_channel_vs_id
,v_cctr_org_vs_id
,v_company_vs_id
,v_cost_ctr_vs_id
,v_customer_vs_id
,v_entity_vs_id
,v_fin_elem_vs_id
,v_geography_vs_id
,v_line_item_vs_id
,v_natural_account_vs_id
,v_product_vs_id
,v_project_vs_id
,v_task_vs_id
,v_user_dim1_vs_id
,v_user_dim2_vs_id
,v_user_dim3_vs_id
,v_user_dim4_vs_id
,v_user_dim5_vs_id
,v_user_dim6_vs_id
,v_user_dim7_vs_id
,v_user_dim8_vs_id
,v_user_dim9_vs_id
,v_user_dim10_vs_id
);