[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT node_value, parent_id
FROM ozf_terr_v
WHERE hierarchy_id = p_hierarchy_id
AND node_id = p_node_id;
SELECT resource_id
FROM jtf_terr_rsc_all jtra,
jtf_terr_rsc_access_all jtraa
WHERE jtraa.terr_rsc_id = jtra.terr_rsc_id
AND jtraa.access_type = 'OFFER'
AND jtraa.trans_access_code = 'PRIMARY_CONTACT'
-- WHERE primary_contact_flag = 'Y'
AND jtra.resource_type = G_RS_EMPLOYEE_TYPE -- yzhao: 04/09/2003 resource can be employee or group. Only employee can be used as budget owner
AND jtra.terr_id = p_node_id;
SELECT short_name, owner, parent_fund_id
FROM ozf_funds_all_vl
WHERE fund_id = p_node_id;
SELECT node_id
FROM ozf_terr_v
WHERE hierarchy_id = p_hierarchy_id
AND parent_id = p_node_id
-- Bug # 5723438 fixed by ateotia (+)
AND decode(end_date_active,'',sysdate,end_date_active) > = sysdate;
SELECT fund_id
FROM ozf_funds_all_b
WHERE parent_fund_id = p_node_id
AND status_code = 'ACTIVE';
SELECT f.currency_code_tc,
NVL(m.from_date, f.start_date_active),
NVL(m.to_date, f.end_date_active)
/*
TO_DATE(
TO_CHAR( NVL(
NVL(m.from_date,f.start_date_active)
,TO_DATE('01-01-2004','DD-MM-YYYY')
)
,'DD-MM') || '-' || m.basis_year
, 'DD-MM-YYYY'
) derived_start_date,
TO_DATE(
TO_CHAR( NVL(
NVL(m.to_date,f.end_date_active)
,TO_DATE('31-12-2004','DD-MM-YYYY')
)
,'DD-MM') || '-' || m.basis_year
, 'DD-MM-YYYY'
) derived_end_date
*/
FROM ozf_funds_all_b f,
ozf_act_metrics_all m
WHERE m.activity_metric_id = p_alloc_id
AND m.arc_act_metric_used_by = 'FUND'
AND m.act_metric_used_by_id = f.fund_id ;
SELECT DISTINCT cust_account_id
FROM ams_party_market_segments
WHERE market_qualifier_type = 'TERRITORY'
AND market_qualifier_reference = p_node_id;
SELECT DISTINCT pms.cust_account_id
FROM ams_party_market_segments pms,
ozf_terr_v terr
WHERE pms.market_qualifier_type = 'TERRITORY'
AND pms.market_qualifier_reference = terr.node_id
AND terr.parent_id = ( SELECT terr1.parent_id
FROM ozf_terr_v terr1
WHERE terr1.node_id = p_node_id);
SELECT NVL(SUM(b.sales_amt),0) tot_sales
FROM ozf_time_rpt_struct a,
ozf_order_sales_sumry_mv b
WHERE a.report_date = p_as_of_date
AND BITAND(a.record_type_id, 119) = a.record_type_id
AND a.time_id = b.time_id
AND b.sold_to_cust_account_id = p_cust_account_id ;
SELECT fent.formula_entry_operator, fent.formula_entry_value
FROM ozf_act_metric_formulas form, ozf_act_metric_form_ent fent
WHERE form.activity_metric_id=p_alloc_id
AND form.formula_type = 'HOLDBACK'
AND form.level_depth = p_curr_level
AND form.formula_id = fent.formula_id
AND fent.formula_entry_type = 'CONSTANT';
SELECT act_metric_used_by_id, func_actual_value,
hierarchy_id, hierarchy_type, start_node, from_level, NVL(to_level, g_max_end_level),
from_date, to_date, status_code, method_code, basis_year, ex_start_node
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT activity_metric_fact_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_id = p_alloc_id
AND node_id = p_node_id
AND level_depth = p_level_depth;
SELECT 1
FROM ams_act_access
WHERE act_access_to_object_id = p_fund_id
AND arc_act_access_to_object = 'FUND'
AND user_or_role_id = p_resource_id
AND arc_user_or_role_type = p_resource_type
AND delete_flag = 'N';
SELECT jtra.resource_id, jtra.resource_type
FROM jtf_terr_rsc_all jtra,
jtf_terr_rsc_access_all jtraa
-- WHERE primary_contact_flag = p_primary_flag
WHERE jtraa.terr_rsc_id = jtra.terr_rsc_id
AND jtraa.access_type = 'OFFER'
AND DECODE(jtraa.trans_access_code,'PRIMARY_CONTACT','Y','NONE','N','DEFAULT','N','N')=p_primary_flag
AND jtra.terr_id = p_terr_id;
SELECT 1
FROM ams_act_market_segments
WHERE arc_act_market_segment_used_by = 'FUND'
AND act_market_segment_used_by_id = p_fund_id
AND market_segment_id = p_market_id
AND segment_type = 'TERRITORY';
SELECT *
FROM ams_act_market_segments seg
WHERE arc_act_market_segment_used_by = 'FUND'
AND act_market_segment_used_by_id = p_fund_id
AND (segment_type <> 'TERRITORY'
OR (segment_type = 'TERRITORY'
AND NOT EXISTS (SELECT 1 FROM ozf_terr_v WHERE hierarchy_id = p_hierarchy_id AND node_id = seg.market_segment_id))
);
SELECT owner, parent_fund_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_act_budgets_rec => l_act_budget_rec
, p_child_approval_flag => fnd_api.g_false -- false since child budget already active
, p_requestor_owner_flag => 'Y' -- set it to bypass approval
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
SELECT act_metric_used_by_id, from_date, to_date, action_code
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT fu.owner, fu.start_date_active, fu.currency_code_tc, fu.fund_type,
fu.org_id, fu.ledger_id, fu.department_id, fu.short_name, fu.status_code,
fu.fund_number, fu.category_id, fu.custom_setup_id
FROM ozf_funds_all_vl fu
where fund_id = p_fund_id;
SELECT owner, short_name
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id;
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = FND_GLOBAL.User_Id;
SELECT activity_metric_fact_id, object_version_number,
hierarchy_id, hierarchy_type, level_depth, node_id, previous_fact_id,
recommend_total_amount, recommend_hb_amount, fact_type
FROM ozf_act_metric_facts_all
WHERE act_metric_used_by_id = p_fund_id
AND arc_act_metric_used_by = 'FUND'
AND activity_metric_id = p_alloc_id
AND status_code = 'NEW'
ORDER BY level_depth, node_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE UPPER(system_status_code) = UPPER(p_status_code)
AND system_status_type = 'OZF_FUND_STATUS'
AND enabled_flag = 'Y';
GOTO LOOP_UPDATE_STATUS;
<>
/* update fact table used_by_id as the child budget id, set status as ACTIVE or PLANNED */
l_metric_fact_rec.activity_metric_fact_id := l_worksheet_rec.activity_metric_fact_id;
ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => p_validation_level
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_act_metric_fact_rec => l_metric_fact_rec
);
Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => 70
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_act_metric_rec => l_metric_rec
);
SELECT ((NVL(original_budget,0)-NVL(holdback_amt,0))+(NVL(transfered_in_amt,0)-NVL(transfered_out_amt,0)))
FROM ozf_funds_all_b
WHERE FUND_id = p_fund_id ;
Procedure update_basepct_info( p_api_version IN NUMBER := 1.0,
p_alloc_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_alloc_info IS
SELECT from_level
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT fact.activity_metric_fact_id, fact.object_version_number
, fact.level_depth, fact.previous_fact_id
, fact.recommend_total_amount, fact.recommend_hb_amount
, fact.base_total_pct, fact.base_hb_pct
FROM ozf_act_metric_facts_all fact
WHERE fact.activity_metric_id = p_alloc_id
ORDER BY fact.level_depth asc;
SELECT recommend_total_amount - NVL(recommend_hb_amount, 0)
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_parent_fact_id;
ozf_actmetricfact_Pvt.update_actmetricfact(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_metric_fact_rec => l_fact_rec
);
END update_basepct_info;
SELECT previous_fact_id, recommend_total_amount, recommend_hb_amount,
act_metric_used_by_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_start_fact_id;
SELECT TRUNC(SUM(NVL(recommend_total_amount, 0)))
FROM ozf_act_metric_facts_all fact
WHERE activity_metric_id = p_alloc_id
AND previous_fact_id = p_parent_fact_id;
SELECT hierarchy_id, hierarchy_type, node_id
FROM ozf_act_metric_facts_all fact
WHERE activity_metric_fact_id = p_fact_id;
SELECT activity_metric_fact_id, object_version_number,
previous_fact_id, recommend_total_amount, recommend_hb_amount,
base_total_pct, base_hb_pct
FROM ozf_act_metric_facts_all
CONNECT BY prior activity_metric_fact_id = previous_fact_id
START WITH previous_fact_id = p_start_fact_id
ORDER BY level_depth;
SELECT recommend_total_amount - NVL(recommend_hb_amount, 0)
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_fact_id;
SELECT fund_id, object_version_number
FROM ozf_funds_all_b
WHERE fund_id = (SELECT act_metric_used_by_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = l_fact_id);
ozf_actmetricfact_Pvt.update_actmetricfact(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_metric_fact_rec => l_fact_rec
);
ozf_funds_pvt.update_fund( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
/* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
before child budget, which may cause amount validation failure
, p_validation_level => fnd_api.g_valid_level_full
*/
, p_validation_level => fnd_api.g_valid_level_none
, p_fund_rec => l_fund_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT act_metric_used_by_id, from_level, status_code
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT fact.activity_metric_fact_id, fact.hierarchy_id, fact.hierarchy_type
, fact.node_id, fact.level_depth
, fact.recommend_total_amount, fact.recommend_hb_amount
FROM ozf_act_metric_facts_all fact
WHERE fact.activity_metric_id = p_alloc_id
ORDER BY fact.level_depth, fact.node_id asc;
SELECT TRUNC(SUM(NVL(recommend_total_amount, 0)))
FROM ozf_act_metric_facts_all fact
WHERE activity_metric_id = p_alloc_id
AND previous_fact_id = p_parent_fact_id;
Procedure update_worksheet_amount(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_alloc_id IN NUMBER,
p_alloc_obj_ver IN NUMBER,
p_cascade_flag IN VARCHAR2,
p_fact_table IN fact_table_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_full_name VARCHAR2(120) := g_pkg_name || ': update_worksheet_amount() ';
SELECT status_code, start_node, func_actual_value, act_metric_used_by_id, action_code
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT fund_id, object_version_number
FROM ozf_funds_all_b
WHERE fund_id = (SELECT act_metric_used_by_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = l_fact_id);
SAVEPOINT update_worksheet_amount_sp;
Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => 70
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_act_metric_rec => l_metric_rec
);
ozf_actmetricfact_Pvt.update_actmetricfact(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_metric_fact_rec => l_fact_rec
);
ozf_funds_pvt.update_fund( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_fund_rec => l_fund_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
update_basepct_info( p_api_version => p_api_version,
p_alloc_id => p_alloc_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO update_worksheet_amount_sp;
END update_worksheet_amount;
SELECT fund.fund_id, fund.object_version_number, fund.owner, fund.short_name, fund.currency_code_tc
, fact.request_total_amount, fund.fund_type
FROM ozf_funds_all_vl fund, ozf_act_metric_facts_all fact
WHERE fund.fund_id = fact.act_metric_used_by_id
AND fact.activity_metric_fact_id = p_child_fact_id
AND fact.arc_act_metric_used_by = 'FUND';
ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_act_metric_fact_rec => l_metric_fact_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ozf_funds_pvt.update_fund( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_fund_rec => l_fund_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT act_metric_used_by_id, created_by, status_code, action_code, ex_start_node
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT fu.status_code, fu.fund_type
FROM ozf_funds_all_b fu
WHERE fund_id = p_fund_id;
SELECT m.activity_metric_fact_id, m.object_version_number
, m.recommend_total_amount, m.recommend_hb_amount
FROM ozf_act_metric_facts_all m
WHERE activity_metric_id = p_alloc_id
AND previous_fact_id IS NULL;
SELECT m.activity_metric_fact_id, m.object_version_number
, m.recommend_total_amount, m.recommend_hb_amount
FROM ozf_act_metric_facts_all m
WHERE activity_metric_id = p_alloc_id
AND previous_fact_id = p_previous_fact_id;
Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_act_metric_rec => l_metric_rec
);
SELECT created_by, status_code, action_code, act_metric_used_by_id
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT activity_metric_fact_id, object_version_number, act_metric_used_by_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_id = p_alloc_id;
SELECT object_version_number, owner, short_name, fund_type
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id;
SELECT fund_type
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id;
Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_act_metric_rec => l_metric_rec
);
ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_act_metric_fact_rec => l_metric_fact_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ozf_funds_pvt.update_fund( p_api_version => p_api_version
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_fund_rec => l_fund_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
PROCEDURE update_alloc_status(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, p_alloc_id IN NUMBER
, p_alloc_status IN VARCHAR2
, p_alloc_obj_ver IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_old_alloc_status VARCHAR2(30);
SELECT status_code
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SAVEPOINT update_alloc_status_sp;
ROLLBACK TO update_alloc_status_sp;
END update_alloc_status;
SELECT status_code, action_code, activity_metric_id
FROM ozf_act_metrics_all
WHERE activity_metric_id = (SELECT activity_metric_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_fact_id);
SELECT status_code
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_fact_id;
SELECT status_code, act_metric_used_by_id, previous_fact_id
, recommend_total_amount, recommend_hb_amount
, request_total_amount, request_hb_amount
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_fact_id
AND previous_fact_id = p_approver_factid; -- to guarantee it approves next level only
SELECT activity_metric_fact_id, object_version_number
, status_code, act_metric_used_by_id, previous_fact_id
, recommend_total_amount, recommend_hb_amount
, request_total_amount, request_hb_amount
FROM ozf_act_metric_facts_all
CONNECT BY prior activity_metric_fact_id = previous_fact_id
START WITH previous_fact_id = p_fact_id
ORDER BY level_depth;
SELECT fact.status_code, fact.previous_fact_id, fact.activity_metric_id
, fact.request_total_amount, fact.request_hb_amount, fund.short_name, fund.fund_type
FROM ozf_act_metric_facts_all fact, ozf_funds_all_vl fund
WHERE fact.activity_metric_fact_id = p_fact_id
AND fact.act_metric_used_by_id = fund.fund_id;
SELECT owner, currency_code_tc
FROM ozf_funds_all_b
WHERE fund_id = (SELECT act_metric_used_by_id
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_lfact_id
AND arc_act_metric_used_by = 'FUND');
SELECT owner, currency_code_tc
FROM ozf_funds_all_b
WHERE fund_id = (SELECT act_metric_used_by_id
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_lalloc_id
AND arc_act_metric_used_by = 'FUND');
ozf_actmetricfact_Pvt.update_actmetricfact(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_metric_fact_rec => l_fact_rec
);
,p_last_updated_by => fnd_global.user_id
,x_jtf_note_id => l_return_id
,p_note_type => 'AMS_JUSTIFICATION'
,p_last_update_date => SYSDATE
,p_creation_date => SYSDATE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT fund.owner, fund.short_name, fund.currency_code_tc
, fact.status_code, fact.request_total_amount, fact.request_hb_amount
, fact.activity_metric_id, fund.fund_type
FROM ozf_act_metric_facts_all fact, ozf_funds_all_vl fund
WHERE fact.activity_metric_fact_id = p_fact_id
AND fact.arc_act_metric_used_by = 'FUND'
AND fact.act_metric_used_by_id = fund.fund_id;
ozf_actmetricfact_Pvt.update_actmetricfact(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_metric_fact_rec => l_fact_rec
);