[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT d.column_name, d.target_member_id
FROM GCS_RULE_SCOPE_DIMS d
WHERE d.rule_step_id = rsi;
SELECT statement_num, statement_text, compiled_variables
FROM GCS_FORMULA_STATEMENTS
WHERE rule_type_code = ruleTypeCode
OR rule_type_code = 'E'
ORDER BY statement_num;
select h.threshold_amount,
h.threshold_currency,
t.financial_elem_id,
t.product_id,
t.natural_account_id,
t.channel_id,
t.line_item_id,
t.project_id,
t.customer_id,
t.task_id,
t.user_dim1_id,
t.user_dim2_id,
t.user_dim3_id,
t.user_dim4_id,
t.user_dim5_id,
t.user_dim6_id,
t.user_dim7_id,
t.user_dim8_id,
t.user_dim9_id,
t.user_dim10_id
from gcs_dimension_templates t, gcs_hierarchies_b h
where t.hierarchy_id = h.hierarchy_id
and t.template_code = 'SUSPENSE'
and h.hierarchy_id = contextData.hierarchy;
SELECT rule_id,
step_seq,
rule_step_id,
step_name,
formula_text,
parsed_formula,
compiled_variables,
sql_statement_num
FROM GCS_ELIM_RULE_STEPS_VL
WHERE rule_id = ruleId
ORDER BY rule_step_id;
Cursor getDimSelections(rsi number) is
SELECT s.rule_step_id,
s.column_name,
s.all_source_members_flag,
s.target_member_id,
s.offset_member_id,
s.hierarchy_obj_id,
x.hierarchy_table_name,
initcap(replace(replace(s.column_name, '_', ''), 'ID', '')) alias
FROM FEM_XDIM_DIMENSIONS x,
GCS_RULE_SCOPE_DIMS s,
FEM_TAB_COLUMNS_B ftcb
WHERE ftcb.table_name = 'FEM_BALANCES'
AND ftcb.column_name = s.column_name
AND ftcb.dimension_id = x.dimension_id
AND s.rule_step_id = rsi
ORDER BY s.column_name;
TYPE dimsTable IS TABLE OF getDimSelections%ROWTYPE INDEX BY VARCHAR2(30);
selectDims dimsTable;
Select nvl(precision, 2), minimum_accountable_unit
From fnd_currencies
Where currency_code = contextData.currencyCode;
Select decode(target_entity_code,
'ELIMINATION',
contextData.elimsEntity,
'PARENT',
contextData.parentEntity,
'CHILD',
contextData.childEntity,
-1) entityId, --default in case codes change
org_output_code,
-- interco_output_code, -- changes made by yingliu
net_to_re_flag,
support_multi_parents_flag -- changes made by yingliu
From gcs_categories_b
Where category_code = contextData.eventCategory;
select distinct e.tgt_line_item_id lineItem,
nvl(ata.number_assign_value, 1) signFactor
from fem_ln_items_attr lia,
fem_ext_acct_types_attr ata,
gcs_entries_gt e
where ata.ext_account_type_code = lia.dim_attribute_varchar_member
and ata.attribute_id = ataAtt
and ata.version_id = ataVer
and lia.attribute_id = liaAtt
and lia.version_id = liaVer
and lia.line_item_id = e.tgt_line_item_id;
Select tgt_company_cost_center_org_id cctr_org_id,
tgt_product_id product_id,
tgt_natural_account_id nat_acct_id,
tgt_channel_id channel_id,
tgt_project_id project_id,
tgt_customer_id customer_id,
tgt_intercompany_id interco_id,
tgt_entity_id entity_id,
tgt_financial_elem_id finl_elem_id,
tgt_line_item_id line_item_id,
tgt_task_id task_id,
tgt_user_dim1_id user_dim1_id,
tgt_user_dim2_id user_dim2_id,
tgt_user_dim3_id user_dim3_id,
tgt_user_dim4_id user_dim4_id,
tgt_user_dim5_id user_dim5_id,
tgt_user_dim6_id user_dim6_id,
tgt_user_dim7_id user_dim7_id,
tgt_user_dim8_id user_dim8_id,
tgt_user_dim9_id user_dim9_id,
tgt_user_dim10_id user_dim10_id,
1 balance_factor,
decode(ccyMinAcctUnit,
null,
decode(min(sql_statement_num),
0,
round(min(nvl(output_amount, 0)), ccyPrecision),
round(sum(nvl(output_amount, 0)), ccyPrecision)),
decode(min(sql_statement_num),
0,
round(min(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) *
ccyMinAcctUnit,
round(sum(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) *
ccyMinAcctUnit)) net_amount,
decode(count(unique step_name),
1,
min(step_name),
'MULTIPLE_RULE_STEPS') description
From (Select min(sql_statement_num) sql_statement_num,
min(tgt_company_cost_center_org_id) tgt_company_cost_center_org_id,
min(tgt_product_id) tgt_product_id,
min(tgt_natural_account_id) tgt_natural_account_id,
min(tgt_channel_id) tgt_channel_id,
min(tgt_project_id) tgt_project_id,
min(tgt_customer_id) tgt_customer_id,
min(tgt_intercompany_id) tgt_intercompany_id,
min(tgt_entity_id) tgt_entity_id,
min(tgt_financial_elem_id) tgt_financial_elem_id,
min(tgt_line_item_id) tgt_line_item_id,
min(tgt_task_id) tgt_task_id,
min(tgt_user_dim1_id) tgt_user_dim1_id,
min(tgt_user_dim2_id) tgt_user_dim2_id,
min(tgt_user_dim3_id) tgt_user_dim3_id,
min(tgt_user_dim4_id) tgt_user_dim4_id,
min(tgt_user_dim5_id) tgt_user_dim5_id,
min(tgt_user_dim6_id) tgt_user_dim6_id,
min(tgt_user_dim7_id) tgt_user_dim7_id,
min(tgt_user_dim8_id) tgt_user_dim8_id,
min(tgt_user_dim9_id) tgt_user_dim9_id,
min(tgt_user_dim10_id) tgt_user_dim10_id,
min(nvl(output_amount, 0)) output_amount,
min(step_name) step_name
from gcs_entries_gt
Where currency_code = contextData.currencyCode
And output_amount <> 0
Group By rule_id,
step_seq,
rule_step_id,
src_company_cost_center_org_id,
src_product_id,
src_natural_account_id,
src_channel_id,
src_project_id,
src_customer_id,
src_intercompany_id,
src_entity_id,
src_financial_elem_id,
src_line_item_id,
src_task_id,
src_user_dim1_id,
src_user_dim2_id,
src_user_dim3_id,
src_user_dim4_id,
src_user_dim5_id,
src_user_dim6_id,
src_user_dim7_id,
src_user_dim8_id,
src_user_dim9_id,
src_user_dim10_id,
tgt_company_cost_center_org_id,
tgt_product_id,
tgt_natural_account_id,
tgt_channel_id,
tgt_project_id,
tgt_customer_id,
tgt_intercompany_id,
tgt_entity_id,
tgt_financial_elem_id,
tgt_line_item_id,
tgt_task_id,
tgt_user_dim1_id,
tgt_user_dim2_id,
tgt_user_dim3_id,
tgt_user_dim4_id,
tgt_user_dim5_id,
tgt_user_dim6_id,
tgt_user_dim7_id,
tgt_user_dim8_id,
tgt_user_dim9_id,
tgt_user_dim10_id) t1
Group By tgt_company_cost_center_org_id,
tgt_product_id,
tgt_natural_account_id,
tgt_channel_id,
tgt_project_id,
tgt_customer_id,
tgt_intercompany_id,
tgt_entity_id,
tgt_financial_elem_id,
tgt_line_item_id,
tgt_task_id,
tgt_user_dim1_id,
tgt_user_dim2_id,
tgt_user_dim3_id,
tgt_user_dim4_id,
tgt_user_dim5_id,
tgt_user_dim6_id,
tgt_user_dim7_id,
tgt_user_dim8_id,
tgt_user_dim9_id,
tgt_user_dim10_id
Having decode(ccyMinAcctUnit, null, decode(min(sql_statement_num), 0,
round(min(nvl(output_amount, 0)), ccyPrecision),
round(sum(nvl(output_amount, 0)), ccyPrecision)),
decode(min(sql_statement_num), 0,
round(min(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) * ccyMinAcctUnit,
round(sum(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) * ccyMinAcctUnit)) <> 0;
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FORMULA_TEXT,
'ELIMTB',
1),
'CHILDTB',
1),
'PARTB',
1),
'%MI',
1 - p_ownership_percent),
'%OWN',
p_ownership_percent)
FROM gcs_elim_rule_steps_b
WHERE rule_id = p_rule_id;
SELECT fea.dim_attribute_numeric_member elim_entity_id,
delta_owned,
gcs_entry_headers_s.nextval,
geca.currency_code,
nvl(precision, 2) precision
FROM gcs_cons_relationships gcr,
fem_entities_attr fea,
gcs_entity_cons_attrs geca,
fnd_currencies fc
WHERE gcr.hierarchy_id = contextData.hierarchy
AND gcr.child_entity_id = contextData.childEntity
AND gcr.actual_ownership_flag = 'N'
AND l_end_date between gcr.start_date and
nvl(gcr.end_date, l_end_date)
AND fea.entity_id = gcr.parent_entity_id
AND fea.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
.attribute_id
AND fea.version_id =
gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
.version_id
AND geca.hierarchy_id = gcr.hierarchy_id
AND geca.entity_id = gcr.parent_entity_id
AND geca.currency_code = fc.currency_code;
SELECT gcr.parent_entity_id,
delta_owned,
gcs_entry_headers_s.nextval,
geca.currency_code,
nvl(precision, 2) precision
FROM gcs_cons_relationships gcr,
gcs_entity_cons_attrs geca,
fnd_currencies fc
WHERE gcr.hierarchy_id = contextData.hierarchy
AND gcr.child_entity_id = contextData.childEntity
AND gcr.actual_ownership_flag = 'N'
AND l_end_date between gcr.start_date and
nvl(gcr.end_date, l_end_date)
AND geca.hierarchy_id = gcr.hierarchy_id
AND geca.entity_id = gcr.parent_entity_id
AND geca.currency_code = fc.currency_code;
SELECT geh.entity_id,
delta_owned,
gcs_entry_headers_s.nextval,
geh.currency_code,
nvl(precision, 2) precision
FROM gcs_cons_relationships gcr,
gcs_entry_headers geh,
fnd_currencies fc
WHERE gcr.hierarchy_id = contextData.hierarchy
AND gcr.child_entity_id = contextData.childEntity
AND gcr.actual_ownership_flag = 'N'
AND l_end_date between gcr.start_date and
nvl(gcr.end_date, l_end_date)
AND geh.entry_id = p_entry_id
AND geh.currency_code = fc.currency_code;
SELECT date_assign_value
FROM fem_cal_periods_attr
where cal_period_id = contextData.calPeriodId
AND attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.attribute_id
AND version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.version_id;
SELECT ownership_percent
FROM gcs_cons_relationships
WHERE cons_relationship_id = contextData.relationship;
SELECT target_entity_code, net_to_re_flag
FROM gcs_categories_b
WHERE category_code = contextData.eventCategory;
INSERT INTO gcs_entry_headers
(ENTRY_ID,
ENTRY_NAME,
HIERARCHY_ID,
DISABLED_FLAG,
ENTITY_ID,
CURRENCY_CODE,
BALANCE_TYPE_CODE,
START_CAL_PERIOD_ID,
END_CAL_PERIOD_ID,
YEAR_TO_APPLY_RE,
DESCRIPTION,
ENTRY_TYPE_CODE,
ASSOC_ENTRY_ID,
CATEGORY_CODE,
PROCESS_CODE,
SUSPENSE_EXCEEDED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PERIOD_INIT_ENTRY_FLAG,
RULE_ID,
PROCESSED_RUN_NAME)
SELECT l_seq(i),
l_seq(i),
HIERARCHY_ID,
DISABLED_FLAG,
l_entities(i),
l_currency(i),
BALANCE_TYPE_CODE,
START_CAL_PERIOD_ID,
END_CAL_PERIOD_ID,
YEAR_TO_APPLY_RE,
DESCRIPTION,
'MULTIPLE_PARENTS',
p_entry_id,
CATEGORY_CODE,
PROCESS_CODE,
DECODE(SUSPENSE_EXCEEDED_FLAG, 'Y', 'Y', l_flag(i)),
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PERIOD_INIT_ENTRY_FLAG,
ruleId,
contextData.runName
FROM gcs_entry_headers
WHERE entry_id = p_entry_id;
INSERT INTO gcs_entry_lines
(ENTRY_ID,
LINE_TYPE_CODE,
DESCRIPTION,
COMPANY_COST_CENTER_ORG_ID,
FINANCIAL_ELEM_ID,
PRODUCT_ID,
NATURAL_ACCOUNT_ID,
CHANNEL_ID,
LINE_ITEM_ID,
PROJECT_ID,
CUSTOMER_ID,
INTERCOMPANY_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 l_seq(i),
LINE_TYPE_CODE,
DESCRIPTION,
COMPANY_COST_CENTER_ORG_ID,
FINANCIAL_ELEM_ID,
PRODUCT_ID,
NATURAL_ACCOUNT_ID,
CHANNEL_ID,
LINE_ITEM_ID,
PROJECT_ID,
CUSTOMER_ID,
INTERCOMPANY_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,
NULL,
round(nvl(YTD_BALANCE_E, 0) * l_percent(i) * -1,
l_precision(i)),
NULL,
NULL,
round(nvl(YTD_CREDIT_BALANCE_E, 0) * l_percent(i),
l_precision(i)),
round(nvl(YTD_DEBIT_BALANCE_E, 0) * l_percent(i),
l_precision(i)),
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM gcs_entry_lines
WHERE entry_id = p_entry_id;
stmts.DELETE;
dimInfo.DELETE;
dimInfo.DELETE(i);
SELECT SPECIFIC_INTERCOMPANY_ID
FROM GCS_HIERARCHIES_B
WHERE hierarchy_id = contextData.hierarchy;
SELECT SPECIFIC_INTERCOMPANY_ID
FROM GCS_CATEGORIES_B
WHERE CATEGORY_CODE = 'INTRACOMPANY';
SELECT dim_attribute_varchar_member
FROM FEM_ENTITIES_ATTR
WHERE entity_id = nvl(contextData.childEntity, -1)
AND attribute_id =
GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
.attribute_id
AND version_id =
GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
.version_id
AND value_set_id =
GCS_UTILITY_PKG.g_gcs_dimension_info('ENTITY_ID')
.associated_value_set_id;
b.' || selectDims(i).column_name;
AND e.src_' || selectDims(i)
.column_name || ' = b.' || selectDims(i)
.column_name;
entriesStmt := 'INSERT INTO GCS_ENTRIES_GT (
rule_id, step_seq, step_name, formula_text, rule_step_id, offset_flag,
sql_statement_num, currency_code, ad_input_amount, pe_input_amount,
ce_input_amount, ee_input_amount, output_amount, entity_id,
ytd_credit_balance_e, ytd_debit_balance_e' ||
srcColumnList || tgtColumnList || ')
SELECT DISTINCT :rid, :seq, :sna, :ftx, :rsi, :osf, :stn, b.currency_code,
0, 0, 0, 0, 0,
b.entity_id,
b.ytd_credit_balance_e,
b.ytd_debit_balance_e' || selColumnList;
SELECT o.company_cost_center_org_id
FROM GCS_ENTITY_CCTR_ORGS o
WHERE ( o.entity_id = :cid';
SELECT r.child_entity_id
FROM GCS_CONS_RELATIONSHIPS r
START WITH r.parent_entity_id = :cid
AND r.hierarchy_id = :hid
AND r.actual_ownership_flag = ''Y''
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate))
CONNECT BY prior r.child_entity_id = r.parent_entity_id
AND r.hierarchy_id = :hid
AND r.actual_ownership_flag = ''Y''
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate)))';
IF selectDims(i).all_source_members_flag = 'Y' THEN
IF selectDims(i).target_member_id IS NULL THEN
tgtDimStmt := tgtDimStmt || ',
b.' || selectDims(i).column_name;
b.' || selectDims(i).column_name;
' || selectDims(i).TARGET_MEMBER_ID;
IF selectDims(i).offset_member_id IS NOT NULL THEN
oTgtDimStmt := oTgtDimStmt || ',
' || selectDims(i).OFFSET_MEMBER_ID;
' || selectDims(i).TARGET_MEMBER_ID;
END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
END IF; --IF selectDims(i).target_member_id IS NULL THEN
IF selectDims(i).target_member_id IS NULL THEN
tgtDimStmt := tgtDimStmt || ',
b.' || selectDims(i).column_name;
b.' || selectDims(i).column_name;
' || selectDims(i).TARGET_MEMBER_ID;
IF selectDims(i).offset_member_id IS NOT NULL THEN
oTgtDimStmt := oTgtDimStmt || ',
' || selectDims(i).OFFSET_MEMBER_ID;
' || selectDims(i).TARGET_MEMBER_ID;
END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
END IF; --IF selectDims(i).target_member_id IS NULL THEN
AND ((b.' || selectDims(i).column_name || ' = D' || j || '.source_member_id
AND D' || j || '.rule_step_id = ' || selectDims(i).rule_step_id ||'
AND D' || j || '.column_name = ''' || selectDims(i).column_name ||''')';
IF selectDims(i).hierarchy_obj_id is null THEN
-- whereClause := whereClause || ')';
AND (b.' || selectDims(i).column_name || ' = D' || j ||
'.source_member_id';
AND b.' || selectDims(i)
.column_name || ' IN (
SELECT h.child_id
FROM ' || selectDims(i).hierarchy_table_name || ' h
WHERE h.hierarchy_obj_def_id = ' ||
selectDims(i).hierarchy_obj_id || '
AND h.parent_value_set_id = ' ||
dimInfo(i).associated_value_set_id || '
AND h.child_value_set_id = h.parent_value_set_id
AND h.parent_id = D' || j ||
'.source_member_id';
END IF; --if selectDims(r).hierarchy_object IS NULL then
selectDims(i)
.rule_step_id || '
AND D' || j || '.column_name = ''' ||
selectDims(i).column_name || ''')';
END IF; --IF selectDims(i).all_source_members_flag = 'Y' THEN
IF selectDims(i).offset_member_id IS NOT NULL THEN
offsetFlag := 'Y';
END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
SELECT o.company_cost_center_org_id
FROM GCS_ENTITY_CCTR_ORGS o
WHERE ( o.entity_id = :cid
OR
o.entity_id IN(
SELECT r.child_entity_id
FROM GCS_CONS_RELATIONSHIPS r
START WITH r.parent_entity_id = :cid
AND r.hierarchy_id = :hid
AND r.actual_ownership_flag = ''Y''
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate))
CONNECT BY prior r.child_entity_id = r.parent_entity_id
AND r.hierarchy_id = :hid
AND r.actual_ownership_flag = ''Y''
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate)))
))))',
'
AND ( b.entity_id = :cid )');
SELECT o.company_cost_center_org_id
FROM GCS_ENTITY_CCTR_ORGS o
WHERE ( o.entity_id = :cid
))))',
'
AND ( b.entity_id = :cid )');
SELECT 1
FROM GCS_ENTRIES_GT E
WHERE E.ENTITY_ID = B.ENTITY_ID
AND ( E.RULE_ID = :rid
AND E.STEP_SEQ = :seq)' || adtColumnList ||'
)';
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
SELECT dim_attribute_varchar_member
FROM FEM_ENTITIES_ATTR
WHERE entity_id = nvl(contextData.childEntity, -1)
AND attribute_id =
GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
.attribute_id
AND version_id =
GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
.version_id
AND value_set_id =
GCS_UTILITY_PKG.g_gcs_dimension_info('ENTITY_ID')
.associated_value_set_id;
entriesStmt := gcs_rp_utility_pkg.g_core_insert_stmt || srcColumnList ||
tgtColumnList || offColumnList ||
gcs_rp_utility_pkg.g_core_sel_stmt || selColumnList;
i := selectDims.FIRST;
if selectDims(i).target_member_id IS NULL then
tgtDimStmt := tgtDimStmt || ',
b.' || selectDims(i).column_name;
bindVarInfo('target' || selectDims(i).alias) := selectDims(i)
.target_member_id;
:target' || selectDims(i).alias;
:offset' || selecTdims(i).alias;
if selectDims(i).offset_member_id IS NOT NULL THEN
offsetFlag := 'Y';
bindVarInfo('offset' || selectDims(i).alias) := selectDims(i)
.offset_member_id;
bindVarInfo('offset' || selectDims(i).alias) := NULL;
end if; --IF selectDims(i).offset_member_id IS NOT NULL THEN
if selectDims(i).all_source_members_flag <> 'Y' then
fromList := fromList || ' ,
gcs_rule_scope_dtls ' || selectDims(i).alias;
AND ' || selectDims(i)
.alias || '.rule_step_id = :rsi';
bindVarInfo('sourcecolumn' || selectDims(i).alias) := selectDims(i)
.column_name;
AND ' || selectDims(i)
.alias || '.column_name = :sourcecolumn' ||
selectDims(i).alias;
if selectDims(i).hierarchy_obj_id IS NULL then
whereClause := whereClause || '
AND b.' || selectDims(i)
.column_name || ' = ' || selectDims(i)
.alias || '.source_member_id';
SELECT object_definition_id
INTO objectDefnId
FROM fem_object_definition_b
WHERE object_id = selectDims(i)
.hierarchy_obj_id
AND contextData.calPeriodEndDate between
effective_start_date and effective_end_date;
' || selectDims(i)
.hierarchy_table_name || ' ' || selectDims(i)
.hierarchy_table_name;
bindVarInfo('sourcehierarchy' || selectDims(i).alias) := objectDefnId;
AND ' || selectDims(i).hierarchy_table_name ||
'.hierarchy_obj_def_id = :sourcehierarchy' ||
selectDims(i).alias;
AND b.' || selectDims(i)
.column_name || ' = ' || selectDims(i)
.hierarchy_table_name || '.child_id';
AND ' || selectDims(i)
.alias || '.source_member_id = ' || selectDims(i)
.hierarchy_table_name || '.parent_id';
i := selectDims.NEXT(i);
outputStmt := 'UPDATE gcs_entries_gt
SET output_amount = round((' || setOutput ||
') / :currPrecision) * :currPrecision
WHERE rule_id = :rule_id
AND step_seq = :seq';
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
SELECT SPECIFIC_INTERCOMPANY_ID
FROM GCS_HIERARCHIES_B
WHERE hierarchy_id = contextData.hierarchy;
SELECT SPECIFIC_INTERCOMPANY_ID
FROM GCS_CATEGORIES_B
WHERE CATEGORY_CODE = 'INTRACOMPANY';
entriesStmt := 'INSERT INTO GCS_ENTRIES_GT (
rule_id, step_seq, step_name, formula_text, rule_step_id,
currency_code,ad_input_amount, pe_input_amount,
ce_input_amount, ee_input_amount, output_amount' ||
tgtColumnList || ')
SELECT DISTINCT :rid, :seq, :sna, :ftx, r..rule_step_id, :ccy, 0, 0, 0, 0,0 ';
'Rows inserted = ' || to_char(SQL%ROWCOUNT));
row_id.DELETE;
sourceAmt.DELETE;
targetAmt.DELETE;
t1Amt.DELETE;
t2Amt.DELETE;
peAmt.DELETE;
ceAmt.DELETE;
eeAmt.DELETE;
selClause := 'SELECT rowidtochar(e.rowid) row_id, ' || '
' || stepData(stepSeq).parsed_formula || ' formula ';
SELECT count(*)
INTO lRowCount
FROM gcs_entries_gt
WHERE rule_id = stepData(stepSeq).rule_id
AND step_seq = stepSeq
AND offset_flag = 'N';
UPDATE GCS_ENTRIES_GT
SET output_amount = decode(offset_flag,
'N',
targetAmt(r),
-1 * targetAmt(r)),
ad_input_amount = t1Amt(r) + t2Amt(r),
pe_input_amount = peAmt(r),
ce_input_amount = ceAmt(r),
ee_input_amount = eeAmt(r)
WHERE rowid = chartorowid(row_id(r));
to_char(SQL%ROWCOUNT) || ' row(s) updated');
SELECT 1
FROM GCS_RULE_SCOPE_DTLS T, GCS_RULE_SCOPE_DIMS D
WHERE D.ALL_SOURCE_MEMBERS_FLAG = 'N'
AND D.TARGET_MEMBER_ID IS NOT NULL
AND D.RULE_STEP_ID = T.RULE_STEP_ID
AND D.RULE_STEP_ID = rsi;
SELECT SPECIFIC_INTERCOMPANY_ID
FROM GCS_CATEGORIES_B
WHERE CATEGORY_CODE = 'INTRACOMPANY';
stepData.DELETE;
SELECT NVL(specific_intercompany_id, organizationId)
INTO intercompanyId
FROM gcs_categories_b
WHERE category_code = 'INTRACOMPANY';
selectDims.DELETE;
For r in getDimSelections(ruleStepId) Loop
selectDims(r.column_name) := r;
If selectDims.COUNT = 0 Then
RAISE invalid_dim_set_id;
Select gcs_entry_headers_s.nextval From dual;
SELECT B.assoc_entry_id
FROM GCS_AD_TRANSACTIONS B
WHERE B.AD_TRANSACTION_ID = contextData.eventKey;
UPDATE GCS_ENTRY_HEADERS
SET RULE_ID = ruleId
WHERE ENTRY_ID = entryId;
to_char(SQL%ROWCOUNT) || ' row(s) updated');
DELETE FROM GCS_ENTRY_LINES WHERE ENTRY_ID = entryId;
to_char(SQL%ROWCOUNT) || ' row(s) deleted');
Insert Into gcs_entry_lines
(entry_id,
company_cost_center_org_id,
financial_elem_id,
product_id,
natural_account_id,
channel_id,
line_item_id,
project_id,
customer_id,
intercompany_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,
ytd_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
description)
Values
(entryId,
linesData.cctr_org_id(x),
linesData.finl_elem_id(x),
linesData.product_id(x),
linesData.nat_acct_id(x),
linesData.channel_id(x),
linesData.line_item_id(x),
linesData.project_id(x),
linesData.customer_id(x),
linesData.interco_id(x),
linesData.task_id(x),
linesData.user_dim1_id(x),
linesData.user_dim2_id(x),
linesData.user_dim3_id(x),
linesData.user_dim4_id(x),
linesData.user_dim5_id(x),
linesData.user_dim6_id(x),
linesData.user_dim7_id(x),
linesData.user_dim8_id(x),
linesData.user_dim9_id(x),
linesData.user_dim10_id(x),
linesData.net_amount(x) * linesData.balance_factor(x),
decode(abs(linesData.net_amount(x)),
linesData.net_amount(x),
linesData.net_amount(x),
0),
decode(abs(linesData.net_amount(x)),
linesData.net_amount(x),
0,
abs(linesData.net_amount(x))),
systemDate,
userId,
systemDate,
userId,
null,
linesData.description(x));
select --+ INDEX_DESC( r, GL_DAILY_RATES_U1 )
(conversion_rate * amt)
from gl_daily_rates r
where from_currency = suspenseData.threshold_currency
and to_currency = contextData.currencyCode
and conversion_date < SYSDATE
and rownum < 2;
tmpSign.DELETE;
liiSign.DELETE;
DELETE gcs_cons_eng_run_dtls
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_entry_lines gel
SET line_type_code = (SELECT DECODE(gel.description,
'RE_LINE',
'CALCULATED',
DECODE(feata.dim_attribute_varchar_member,
'ASSET',
'BALANCE_SHEET',
'LIABILITY',
'BALANCE_SHEET',
'EQUITY',
'BALANCE_SHEET',
'PROFIT_LOSS'))
FROM fem_ext_acct_types_attr feata,
fem_ln_items_attr flia
WHERE gel.line_item_id = flia.line_item_id
AND flia.attribute_id =
l_line_item_type_attr
AND flia.version_id =
l_line_item_type_version
AND flia.dim_attribute_varchar_member =
feata.ext_account_type_code
AND feata.attribute_id = l_acct_type_attr
AND feata.version_id =
l_acct_type_version)
WHERE gel.entry_id = entryId;
SELECT SUSPENSE_EXCEEDED_FLAG
INTO l_suspense_exceeded_flag
FROM gcs_entry_headers
WHERE entry_id = entryId;
Select gcs_entry_headers_s.nextval From dual;
'inserting into gcs_entry_lines');
'Inserting into gcs_entry_lines');
select decode(count(1), 0, 'N', 'Y')
into offsetFlag
from gcs_rule_scope_dims grsd, gcs_elim_rule_steps_b grsb
where grsd.column_name = 'LINE_ITEM_ID'
and grsd.offset_member_id is not null
and grsb.rule_step_id = grsd.rule_step_id
and grsb.rule_id = ruleId;
'Completed insert into gcs_entry_lines ' || l_row_count);
DELETE gcs_cons_eng_run_dtls
WHERE run_detail_id = contextData.eventKey;
select --+ INDEX_DESC( r, GL_DAILY_RATES_U1 )
(conversion_rate * amt)
from gl_daily_rates r
where from_currency = suspenseData.threshold_currency
and to_currency = contextData.currencyCode
and conversion_date < SYSDATE
and rownum < 2;
SELECT SUSPENSE_EXCEEDED_FLAG
INTO l_suspense_exceeded_flag
FROM gcs_entry_headers
WHERE entry_id = entryId;
select NVL(minimum_accountable_unit, POWER(10, -precision))
into contextData.currPrecision
from fnd_currencies
where currency_code = contextData.currencyCode;
EXECUTE IMMEDIATE 'SELECT ' || l_parsed_formula(i) || ' FROM DUAL'
INTO l_parsed_result;
delete from gcs_cons_eng_run_dtls
where run_detail_id = contextData.eventKey;
SELECT fem_ledger_id
INTO contextData.ledgerId
FROM gcs_hierarchies_b
WHERE hierarchy_id = contextData.hierarchy;
'Update eventKey: eventType =>',
contextData.eventType);
UPDATE gcs_cons_eng_run_dtls
SET entry_id = mainEntryId,
stat_entry_id = statEntryId,
-- SKAMDAR : Added updates for request_error_code, and bp_request_error_code
request_error_code = DECODE(l_return_value,
1,
'WARNING',
'COMPLETED'),
bp_request_error_code = DECODE(l_return_value,
1,
'WARNING',
'COMPLETED')
WHERE run_detail_id = contextData.eventKey;
to_char(SQL%ROWCOUNT) || ' row(s) updated');
UPDATE gcs_ad_transactions
SET assoc_entry_id = mainEntryId
WHERE ad_transaction_id = contextData.eventKey;
to_char(SQL%ROWCOUNT) || ' row(s) updated' );
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_MISSING_RULE_STEPS',
bp_request_error_code = 'GCS_MISSING_RULE_STEPS'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_MISSING_CURRENCY_DATA',
bp_request_error_code = 'GCS_MISSING_CURRENCY_DATA'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_INVALID_DIM_SET_ID',
bp_request_error_code = 'GCS_INVALID_DIM_SET_ID'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_INVALID_VARIABLE',
bp_request_error_code = 'GCS_INVALID_VARIABLE'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_TEMPLATES_PKG_ERROR',
bp_request_error_code = 'GCS_TEMPLATES_PKG_ERROR'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_CONVERT_THRESHOLD_ERR',
bp_request_error_code = 'GCS_CONVERT_THRESHOLD_ERR'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_ENTRY_HEADER_ERROR',
bp_request_error_code = 'GCS_ENTRY_HEADER_ERROR'
WHERE run_detail_id = contextData.eventKey;
UPDATE gcs_cons_eng_run_dtls
SET request_error_code = 'GCS_UNHANDLED_EXCEPTION',
bp_request_error_code = 'GCS_UNHANDLED_EXCEPTION'
WHERE run_detail_id = contextData.eventKey;