[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT activity_id
FROM ams_campaign_schedules_b
WHERE schedule_id = p_used_by_id;
SELECT activity_media_id
FROM ozf_offers
WHERE qp_list_header_id = p_used_by_id;
SELECT 'X'
FROM ams_cat_activities
WHERE category_id = p_category_id
AND activity_id = p_activity_id;
SELECT COUNT(cat_activity_id) count
FROM ams_cat_activities
WHERE category_id = p_category_id;
SELECT category_id
FROM ozf_funds_all_b
WHERE fund_id = p_budget_source_id;
SELECT (NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0)) total_amount
FROM ozf_object_fund_summary
WHERE object_id =p_object_id
AND object_type = p_object_type
AND fund_id = p_budget_source_id;
SELECT SUM (amount) total_amount
FROM(
SELECT --- request amount
NVL (SUM (a1.approved_amount), 0) amount
FROM ozf_act_budgets a1
WHERE a1.act_budget_used_by_id = p_object_id
AND a1.arc_act_budget_used_by = p_object_type
AND a1.budget_source_type = 'FUND'
AND a1.budget_source_id = p_budget_source_id
AND a1.status_code = 'APPROVED'
AND a1.transfer_type ='REQUEST'
UNION
SELECT -NVL (SUM (a2.approved_original_amount), 0) amount
FROM ozf_act_budgets a2
WHERE a2.budget_source_id = p_object_id
AND a2.budget_source_type = p_object_type
AND a2.act_budget_used_by_id = p_budget_source_id
AND a2.arc_act_budget_used_by = 'FUND'
AND a2.status_code = 'APPROVED'
AND a2.transfer_type = 'TRANSFER'
UNION
SELECT SUM(amount) amount
FROM ozf_funds_utilized_all_b
where plan_type = p_object_type
and plan_id = p_object_id
and fund_id = p_budget_source_id);
SELECT SUM(NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0)) total_amount
FROM ozf_object_fund_summary
WHERE object_id =p_object_id
AND object_type = p_object_type;
SELECT parent_source, parent_curr, SUM (amount) total_amount
FROM (SELECT a1.fund_id parent_source, a1.currency_code parent_curr
,NVL (SUM (a1.amount), 0) amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_budget_source_id
AND a1.component_type = p_budget_source_type
-- AND a1.status_code = 'APPROVED' -- only approved record are present here
AND a1.utilization_type IN ('TRANSFER', 'REQUEST')
AND a1.fund_id = DECODE (p_object_type,'FUND',p_object_id , a1.fund_id )
-- AND a1.budget_source_type = DECODE (p_object_type, 'FUND', p_object_type, a1.budget_source_type )
GROUP BY a1.fund_id, a1.currency_code
UNION
SELECT a2.fund_id parent_source, a2.currency_code parent_curr
,-NVL (SUM (a2.amount), 0) amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_budget_source_id
AND a2.plan_type = p_budget_source_type
-- yzhao: 12/02/2003 11.5.10 added CHARGEBACK
AND a2.utilization_type IN ('TRANSFER', 'REQUEST', 'UTILIZED','ADJUSTMENT','ACCRUAL','SALES_ACCRUAL', 'CHARGEBACK')
AND a2.fund_id = DECODE (p_object_type,'FUND',p_object_id , a2.fund_id )
-- AND a2.arc_act_budget_used_by = DECODE (p_object_type, 'FUND', p_object_type, a2.arc_act_budget_used_by )
-- AND a2.status_code = 'APPROVED' -- -- only approved record are present here
GROUP BY a2.fund_id, a2.currency_code)
GROUP BY parent_source, parent_curr
ORDER BY parent_source;
SELECT 'X'
FROM ams_act_market_segments mkt1
WHERE mkt1.arc_act_market_segment_used_by = p_used_by_type
AND mkt1.act_market_segment_used_by_id = p_used_by_id
AND mkt1.exclude_flag = 'N'
AND EXISTS ( SELECT mkt1.market_segment_id
FROM ams_act_market_segments mkt2
WHERE mkt2.arc_act_market_segment_used_by = 'FUND'
AND mkt2.act_market_segment_used_by_id = p_budget_source_id
AND mkt2.exclude_flag = 'N');
SELECT 'X'
FROM qp_qualifiers qp
WHERE list_header_id = p_used_by_id
AND EXISTS ( SELECT 1
FROM ams_act_market_segments
WHERE arc_act_market_segment_used_by = 'FUND'
AND act_market_segment_used_by_id = p_budget_source_id
AND exclude_flag = qp.excluder_flag);
SELECT 'X'
FROM ams_act_market_segments mkt2
WHERE mkt2.arc_act_market_segment_used_by = 'FUND'
AND mkt2.act_market_segment_used_by_id = p_budget_source_id;
SELECT 'X'
FROM ams_act_products pdt1
WHERE pdt1.arc_act_product_used_by = p_used_by_type
AND pdt1.act_product_used_by_id = p_used_by_id
AND pdt1.excluded_flag = 'N'
AND EXISTS ( SELECT 1
FROM ams_act_products pdt2
WHERE pdt2.arc_act_product_used_by = 'FUND'
AND pdt2.act_product_used_by_id = p_budget_source_id
AND pdt2.excluded_flag = 'N');
SELECT 'X'
FROM qp_modifier_summary_v qp
WHERE list_header_id = p_used_by_id
AND EXISTS ( SELECT 1
FROM ams_act_products
WHERE arc_act_product_used_by = 'FUND'
AND act_product_used_by_id = p_budget_source_id
AND excluded_flag = qp.excluder_flag);
SELECT 'X'
FROM ams_act_products pdt2
WHERE pdt2.arc_act_product_used_by = 'FUND'
AND pdt2.act_product_used_by_id = p_budget_source_id;
SELECT NVL (SUM (approved_amount), 0)
FROM ams_act_budgets
WHERE arc_act_budget_used_by = p_source_type
AND act_budget_used_by_id = p_source_id;
SELECT SUM(NVL(committed_amt,0)-NVL(utilized_amt,0)) total_amount
FROM ozf_object_fund_summary
WHERE object_id =p_source_id
AND object_type = p_source_type;
SELECT SUM (amount) total_amount
FROM (SELECT NVL (SUM (a1.amount), 0) amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_source_id
AND a1.component_type = p_source_type
AND a1.utilization_type NOT IN
('ADJUSTMENT', 'UTILIZED')
GROUP BY a1.fund_id, a1.currency_code
UNION
SELECT -NVL (SUM (a2.amount), 0) amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_source_id
AND a2.plan_type = p_source_type
GROUP BY a2.fund_id, a2.currency_code);
SELECT custom_setup_id, owner_user_id
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT custom_setup_id, owner_user_id
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_object_id;
SELECT setup_type_id, owner_user_id
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT setup_type_id, owner_user_id
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT custom_setup_id, owner_user_id
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT custom_setup_id, owner
FROM ozf_funds_all_vl
WHERE fund_id = p_object_id;
SELECT custom_setup_id, owner_id
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT NVL (attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = 'BAPL';
SELECT budget_amount_tc, status_code
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT budget_amount_tc, status_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_object_id;
SELECT fund_amount_tc, system_status_code
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT fund_amount_tc, system_status_code
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT budget_amount_tc, status_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT budget_amount_tc, status_code
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT SUM (amount) amount FROM (
SELECT DECODE(a1.status_code ,'NEW',a1.request_amount,'APPROVED', a1.approved_amount,0) amount
FROM ozf_act_budgets a1
WHERE a1.act_budget_used_by_id = p_object_id
AND a1.arc_act_budget_used_by = p_object_type
AND a1.transfer_type = 'REQUEST'
AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
AND status_code <> 'REJECTED'
UNION ALL
SELECT DECODE(a1.status_code ,'NEW',a1.src_curr_request_amt,'APPROVED', -a1.approved_original_amount) amount
FROM ozf_act_budgets a1
WHERE a1.budget_source_id = p_object_id
AND a1.budget_source_type = p_object_type
AND a1.transfer_type = 'TRANSFER'
AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
AND status_code <> 'REJECTED');
SELECT SUM( NVL(
DECODE(a1.transfer_type ,
'REQUEST',
DECODE(a1.status_code ,'NEW',a1.request_amount,'APPROVED', a1.approved_amount,0),
'TRANSFER' ,
DECODE(a1.status_code ,'NEW',a1.src_curr_request_amt,'APPROVED', -a1.approved_original_amount))
,0)
) amount
FROM ozf_act_budgets a1
WHERE DECODE(a1.transfer_type , 'REQUEST', a1.act_budget_used_by_id, 'TRANSFER' , a1.budget_source_id ) = p_object_id
AND DECODE(a1.transfer_type , 'REQUEST', a1.arc_act_budget_used_by, 'TRANSFER' , a1.budget_source_type ) = p_object_type
AND a1.transfer_type <> 'UTILIZED'
AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
AND status_code <> 'REJECTED' ;
SELECT NVL (SUM (NVL (a1.approved_amount, a1.request_amount)), 0) amount
FROM ozf_act_budgets a1
WHERE a1.act_budget_used_by_id = p_object_id
AND a1.arc_act_budget_used_by = p_object_type
AND a1.transfer_type <> 'UTILIZED'
AND a1.activity_budget_id <> NVL (p_act_budget_id, 0)
AND status_code <> 'REJECTED';
SELECT (NVL(original_budget, 0) - NVL(holdback_amt, 0)
+ NVL(transfered_in_amt, 0) - NVL(transfered_out_amt, 0))
- NVL (committed_amt, 0)
FROM ozf_funds_all_b
WHERE fund_id = p_source_id;
SELECT NVL (available_budget, 0)
- NVL (committed_amt, 0)
FROM ozf_fund_details_v
WHERE fund_id = p_source_id;
SELECT user_id user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user;
SELECT jtf_note_id
FROM jtf_notes_b
WHERE source_object_code = 'AMS_'
|| p_activity_type
AND source_object_id = p_activity_id
AND note_type = p_note_type;
jtf_notes_pub.update_note (
p_api_version=> 1.0
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_jtf_note_id=> l_note_id
,p_entered_by=> l_user
,p_last_updated_by=> l_user
,p_notes=> p_note
,p_note_type=> p_note_type
);
,p_last_updated_by=> l_user
,x_jtf_note_id=> l_id
,p_note_type=> p_note_type
,p_last_update_date=> SYSDATE
,p_creation_date=> SYSDATE
);