[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM FEM_BALANCES fb
WHERE fb.dataset_code = p_hier_dataset_code
AND fb.cal_period_id = p_cal_period_id
AND fb.source_system_code = p_source_system_code
AND fb.currency_code = p_from_ccy
AND fb.ledger_id = p_ledger_id
AND fb.entity_id = p_entity_id
AND fb.line_item_id = p_line_item_id;
'INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(entry_id, ' ||
'line_item_id, company_cost_center_org_id, ' ||
'intercompany_id, financial_elem_id, product_id, ' ||
'natural_account_id, channel_id, project_id, customer_id, task_id, ' ||
'user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, ' ||
'user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, ' ||
'xtd_balance_e, ytd_balance_e, ptd_debit_balance_e, ptd_credit_balance_e, ' ||
'ytd_debit_balance_e, ytd_credit_balance_e, creation_date, created_by, ' ||
'last_update_date, last_updated_by, last_update_login)' || g_nl ||
'SELECT ' || p_new_entry_id || ', ' ||
'tgt.line_item_id, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
'NULL, ' ||
g_nl ||
'fxata.number_assign_value *' || g_nl ||
'decode(tgt.account_type_code,' || g_nl ||
' ''REVENUE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,' || g_nl ||
' ''EXPENSE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,' || g_nl ||
' tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),' || g_nl ||
'fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),' || g_nl ||
'tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr, sysdate, ' ||
gcs_translation_pkg.g_fnd_user_id || ', sysdate, ' ||
gcs_translation_pkg.g_fnd_user_id || ', ' ||
gcs_translation_pkg.g_fnd_login_id || g_nl ||
'FROM gcs_translation_gt, tgt,' || g_nl ||
' fem_ln_items_attr li,' || g_nl ||
' fem_ext_acct_types_attr fxata' || g_nl ||
'WHERE li.line_item_id = tgt.line_item_id' || g_nl ||
'AND li.attribute_id = ' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||
'AND li.version_id = ' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||
'AND fxata.ext_account_type_code = li.dim_attribute_varchar_label' || g_nl ||
'AND fxata.attribute_id = ' || gcs_translation_pkg.g_xat_sign_attr_id || g_nl ||
'AND fxata.version_id = ' || gcs_translation_pkg.g_xat_sign_v_id);
INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(
entry_id, line_item_id, company_cost_center_org_id,
intercompany_id, financial_elem_id,
product_id, natural_account_id, channel_id, project_id, customer_id,
task_id, user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id,
user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id,
user_dim10_id, xtd_balance_e, ytd_balance_e, ptd_debit_balance_e,
ptd_credit_balance_e, ytd_debit_balance_e, ytd_credit_balance_e,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
SELECT
p_new_entry_id,
tgt.line_item_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
fxata.number_assign_value *
decode(tgt.account_type_code,
'REVENUE', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,
'EXPENSE', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,
tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),
fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),
tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr,
sysdate, gcs_translation_pkg.g_fnd_user_id, sysdate,
gcs_translation_pkg.g_fnd_user_id, gcs_translation_pkg.g_fnd_login_id
FROM gcs_translation_gt tgt,
fem_ln_items_attr li,
fem_ext_acct_types_attr fxata
WHERE li.line_item_id = tgt.line_item_id
AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id
AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id
AND fxata.ext_account_type_code = li.dim_attribute_varchar_member
AND fxata.attribute_id = gcs_translation_pkg.g_xat_sign_attr_id
AND fxata.version_id = gcs_translation_pkg.g_xat_sign_v_id;