[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qp_list_header_id object_id
,nvl(transaction_currency_code,fund_request_curr_code) object_curr
--, qp.end_date_active object_date
FROM ozf_offers off,qp_list_headers_b qp
WHERE off.qp_list_header_id = qp.list_header_id
AND NVL (qp.end_date_active, p_grace_date) <= p_grace_date
AND status_code IN (p_status)
--AND offr.status_code IN ('CLOSED','COMPLETED','TERMINATED') -- inactive offers;
SELECT ozf.campaign_id object_id
,ozf.transaction_currency_code object_curr
--, ozf.actual_execution_end_date
FROM ams_campaigns_all_b ozf
WHERE NVL (ozf.actual_exec_end_date, p_grace_date) <= p_grace_date
-- changed to archived 08/20/2001 mpande
--AND ozf.status_code IN ('CLOSED','COMPLETED','CANCELLED','ARCHIVED') -- inactive camps;
SELECT ozf.event_header_id object_id
,ozf.currency_code_tc object_curr
FROM ams_event_headers_all_b ozf
WHERE NVL (ozf.active_to_date, p_grace_date) <= p_grace_date
-- changed to archived 08/20/2001 mpande
--AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive;
SELECT ozf.event_offer_id object_id
,ozf.currency_code_tc object_curr
--, ozf.event_end_date
FROM ams_event_offers_all_b ozf
WHERE NVL (ozf.event_end_date, p_grace_date) <= p_grace_date
-- changed to archived 08/20/2001 mpande
--AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive ;
SELECT ozf.event_offer_id object_id
,ozf.currency_code_tc object_curr
--, ozf.event_end_date
FROM ams_event_offers_all_b ozf
WHERE NVL (ozf.event_end_date, p_grace_date) <= p_grace_date
-- changed to archived 08/20/2001 mpande
--AND ozf.system_status_code IN ('CLOSED','CANCELLED','ARCHIVED') -- inactive ;
SELECT ozf.deliverable_id object_id
,ozf.transaction_currency_code object_curr
FROM ams_deliverables_vl ozf
WHERE
NVL(ozf.actual_complete_date, p_grace_date) <= p_grace_date
AND ozf.status_code IN (p_status)
AND ozf.deliverable_name = NVL(p_code, ozf.deliverable_name)
--AND ozf.actual_complete_date <= NVL(p_end_date, ozf.actual_complete_date)
AND NVL(ozf.actual_complete_date,SYSDATE) <= NVL(p_end_date, NVL(ozf.actual_complete_date,SYSDATE))
AND NVL(ozf.accounts_closed_flag,'N') = 'N';
SELECT ozf.schedule_id object_id
,ozf.transaction_currency_code object_curr
-- , ozf.end_date_time
FROM ams_campaign_schedules_vl ozf
WHERE NVL (ozf.end_date_time, p_grace_date) <= p_grace_date
-- changed to archived 08/20/2001 mpande
--AND ozf.status_code IN ('CLOSED','COMPLETED','CANCELLED','ARCHIVED') -- inactive ;
SELECT qp_list_header_id object_id
,nvl(transaction_currency_code,fund_request_curr_code) object_curr
FROM ozf_offers off,ozf_request_headers_all_b req
WHERE off.qp_list_header_id = req.offer_id
AND req.request_class ='SOFT_FUND'
AND NVL (req.approved_date, p_grace_date) <= p_grace_date
AND req.status_code ='APPROVED'
AND req.request_number = NVL(p_code, req.request_number)
--AND req.end_date <= NVL(p_end_date,req.end_date);
SELECT qp_list_header_id object_id
,nvl(transaction_currency_code,fund_request_curr_code) object_curr
FROM ozf_offers off,ozf_request_headers_all_b req
WHERE off.qp_list_header_id = req.offer_id
AND req.request_class ='SPECIAL_PRICE'
AND NVL (req.end_date, p_grace_date) <= p_grace_date
AND req.status_code ='APPROVED'
AND req.request_number = NVL(p_code, req.request_number)
-- AND req.end_date <= NVL(p_end_date,req.end_date);
UPDATE ams_campaigns_all_b
SET accounts_closed_flag = 'Y'
WHERE campaign_id = l_object_tbl (k).object_id;
UPDATE ams_campaign_schedules_b
SET accounts_closed_flag = 'Y'
WHERE schedule_id = l_object_tbl (k).object_id;
UPDATE ams_event_headers_all_b
SET accounts_closed_flag = 'Y'
WHERE event_header_id = l_object_tbl (k).object_id;
UPDATE ams_event_offers_all_b
SET accounts_closed_flag = 'Y'
WHERE event_offer_id = l_object_tbl (k).object_id;
UPDATE ams_campaigns_all_b
SET accounts_closed_flag = 'Y'
WHERE campaign_id = l_object_tbl (k).object_id;
UPDATE ozf_offers
SET account_closed_flag = 'Y'
WHERE qp_list_header_id = l_object_tbl (k).object_id;
UPDATE ozf_offers
SET account_closed_flag = 'Y'
WHERE qp_list_header_id = l_object_tbl (k).object_id;
UPDATE ozf_request_headers_all_b
SET status_code = 'CLOSED'
WHERE offer_id = l_object_tbl (k).object_id;
UPDATE ozf_offers
SET account_closed_flag = 'Y'
WHERE qp_list_header_id = l_object_tbl (k).object_id;
UPDATE ozf_request_headers_all_b
SET status_code = 'CLOSED'
WHERE offer_id = l_object_tbl (k).object_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_budget_used_by_id
AND a1.arc_act_budget_used_by = p_budget_used_by_type
AND a1.status_code = 'APPROVED'
AND a1.transfer_type ='REQUEST'
AND parent_act_budget_id is null
UNION -- transfer and utilized amount
SELECT -NVL (SUM (a2.approved_original_amount), 0) amount
FROM ozf_act_budgets a2
WHERE a2.budget_source_id = p_budget_used_by_id
AND a2.budget_source_type = p_budget_used_by_type
AND a2.status_code = 'APPROVED'
AND a2.transfer_type <>'REQUEST'
AND parent_act_budget_id is null
);
SELECT offr.budget_source_id,NVL(offr.source_from_parent,'N'),camp.transaction_currency_code
FROM ozf_offers offr,ams_campaigns_all_b camp
WHERE offr.qp_list_header_id = p_offer_id
AND camp.campaign_id = offr.budget_source_id;
SELECT sch.campaign_id,NVL(source_from_parent,'N'),camp.transaction_currency_code
FROM ams_campaign_schedules_b sch,ams_campaigns_all_b camp
WHERE sch.schedule_id = p_schedule_id
AND camp.campaign_id = sch.campaign_id;
SELECT sch.event_header_id, NVL(sch.source_from_parent,'N'),evt.currency_code_tc
FROM ams_event_offers_all_b sch,ams_event_headers_all_b evt
WHERE sch.event_offer_id = pschedule_id
AND sch.event_header_id = evt.event_header_id;
SELECT fund_id parent_source_id
,fund_currency parent_curr
,NVL(committed_amt,0)-NVL(utilized_amt,0) total_amount
,NVL(plan_curr_committed_amt,0)-NVL(plan_curr_utilized_amt,0) plan_curr_total_amount
FROM ozf_object_fund_summary
WHERE object_id =p_budget_used_by_id
AND object_type = p_budget_used_by_type;
SELECT count(*)
FROM ozf_offers off
WHERE off.qp_list_header_id = p_budget_used_by_id
AND off.reusable = 'Y' ;
SELECT count(activity_offer_id)
FROM ozf_offers off,ozf_act_offers act
WHERE off.qp_list_header_id = p_budget_used_by_id
AND off.reusable = 'N'
AND off.qp_list_header_id = act.qp_list_header_id ;
SELECT approval_date
FROM ozf_act_budgets a1
WHERE a1.act_budget_used_by_id = p_budget_used_by_id
AND a1.arc_act_budget_used_by = p_budget_used_by_type
AND a1.status_code = 'APPROVED'
AND a1.transfer_type = 'REQUEST'
AND a1.budget_source_id = p_fund_id;
SELECT offs.qp_list_header_id
,nvl(offs.transaction_currency_code,fund_request_curr_code)
,qpl.start_date_active start_date_active
,qpl.end_date_active end_date_active
,NVL(offs.last_recal_date,qpl.start_date_active) last_recal_date -- changed to new column for last recal date
--,qpl.description description
FROM ozf_offers offs, qp_list_headers_b qpl
WHERE offs.offer_type NOT IN ('LUMPSUM', 'TERMS','SCAN_DATA')
AND offs.status_code = 'ACTIVE'
AND NVL (offs.account_closed_flag, 'N') = 'N'
AND offs.qp_list_header_id = qpl.list_header_id(+)
AND qpl.start_date_active < SYSDATE
AND qpl.end_date_active is not NULL
--AND qpl.end_date_active > SYSDATE
AND NVL (offs.budget_offer_yn, 'N') = 'N'
AND NVL(offs.last_recal_date,qpl.start_date_active) <= qpl.end_date_active;
SELECT fund_id,SUM(NVL(plan_curr_committed_amt,0)) total_amt
FROM ozf_object_fund_summary
WHERE object_id = p_object_id
AND object_type = 'OFFR'
--AND NVL(recal_flag,'N') ='N'
GROUP BY fund_id;
SELECT SUM (NVL(plan_curr_amount,0)) utilized_amt
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_object_id
AND plan_type = 'OFFR'
AND fund_id = p_budget_id
-- AND utilization_type NOT IN ('TRANSFER', 'REQUEST')
AND NVL(adjustment_date,creation_date) BETWEEN p_start_date AND p_end_date + 1;
SELECT (NVL(original_budget, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0))
,recal_committed
,currency_code_tc
FROM ozf_funds_all_b
WHERE fund_id = p_budget_id;
SELECT DISTINCT metr.from_date -- need distince for multiple dimension forecast
,metr.TO_DATE
,metr.fact_percent
FROM ozf_act_forecasts_all fore, ozf_act_metric_facts_all metr
WHERE fore.arc_act_fcast_used_by = 'OFFR'
AND fore.act_fcast_used_by_id = p_offer_id
AND fore.base_quantity_type <>'BASELINE'
AND metr.arc_act_metric_used_by = 'FCST'
AND metr.act_metric_used_by_id(+) = fore.forecast_id
AND metr.fact_type= 'TIME'
AND freeze_flag = 'N';
SELECT count(1)
FROM qp_limits ql
WHERE ql.list_header_id = p_offer_id
AND ql.list_line_id = -1
AND limit_number = 1
AND basis='COST'
AND organization_flag='N'
AND limit_level_code='ACROSS_TRANSACTION'
AND limit_exceed_action_code = 'SOFT'
AND limit_hold_flag='Y';
SELECT NVL(plan_curr_recal_committed_amt,0)- NVL(PLAN_CURR_UTILIZED_AMT,0)
FROM ozf_object_fund_summary
WHERE object_id = p_object_id
AND fund_id = p_budget_id
AND object_type = 'OFFR';
UPDATE ozf_offers
SET last_recal_date = l_period_start_date
WHERE qp_list_header_id = l_listHeaderIdTbl(i);
SELECT offr.qp_list_header_id offer_id,offr.offer_type
,offr.transaction_currency_code offer_curr
FROM qp_list_headers_b qpoffr, ozf_offers offr
WHERE offr.qp_list_header_id = qpoffr.list_header_id
AND offr.status_code IN ('ACTIVE')
AND offr.offer_type IN ('LUMPSUM', 'SCAN_DATA')
AND NVL (offr.account_closed_flag, 'N') = 'N'
--AND offr.qp_list_header_id IN(11257,11258);
SELECT a1.fund_id parent_source_id
,a1.currency_code parent_curr
,NVL (SUM (a1.plan_curr_amount_remaining), 0) amount
,a1.product_id,a1.product_level_type,a1.scan_unit,a1.scan_unit_remaining,
a1.activity_product_id,a1.cust_account_id,a1.gl_posted_flag,a1.utilization_id orig_utilization_id
,a1.exchange_rate_type ,a1.exchange_rate_date,a1.org_id --Added for bug 7030415
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_budget_used_by_id
AND a1.component_type = p_budget_used_by_type
AND a1.utilization_type IN -- feliu on 11/11/05: remove UTILIZED and SALES ACCRUAL.
('ADJUSTMENT', 'ACCRUAL','CHARGEBACK','LEAD_ACCRUAL') -- yzhao: 11.5.10 added chargeback
GROUP BY a1.fund_id, a1.currency_code,a1.product_id,
a1.product_level_type,a1.scan_unit,a1.scan_unit_remaining,
a1.activity_product_id,a1.cust_account_id,a1.gl_posted_flag,
a1.exchange_rate_type,a1.exchange_rate_date,
a1.org_id,a1.utilization_id
ORDER BY parent_source_id;
SELECT DECODE(PARAMETER_NAME,'parentId', 'parent_fund_id','statusId','status_code','num','fund_number','typeId',
'fund_type','name','short_name','startDate','start_date_active','endDate','end_date_active','currency','currency_code_tc',
'ownerId','owner','startPeriodName','start_period_name','endPeriodName','end_period_name','categoryId','category_id',
'baseQueryType','baseQueryType','1'), DECODE(PARAMETER_CONDITION,'CONS','LIKE',PARAMETER_CONDITION),PARAMETER_VALUE
FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id AND PARAMETER_TYPE = 'condition';
l_str_query := 'SELECT fund_id FROM ozf_fund_details_v, ams_act_access_denorm accd WHERE ';
SELECT committed_amt,earned_amt
from OZF_FUNDS_ALL_B where fund_id = l_prev_year_fund_id;
SELECT object_type, object_id
,NVL(committed_amt,0) - NVL(earned_amt,0) amount
,NVL(plan_curr_committed_amt,0) - NVL(plan_curr_utilized_amt,0) plan_curr_amount
,fund_currency,object_currency
FROM ozf_object_fund_summary
WHERE fund_id = p_fund_id;
SELECT status_code
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT status_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_object_id;
SELECT system_status_code
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT system_status_code
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT status_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT * FROM OZF_FUNDS_ALL_VL
WHERE PREV_FUND_ID IS NOT NULL
AND TRANSFERED_FLAG IS NULL
AND STATUS_CODE IN('ACTIVE','DRAFT');
SELECT end_date_active,object_version_number
FROM OZF_FUNDS_ALL_VL
WHERE fund_id = p_fund_id;
ozf_utility_pvt.write_conc_log(l_full_name || ' update draft budget to active. ' || l_new_fund_rec_type.fund_id);
OZF_funds_pvt.update_fund(p_api_version => 1.0
,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 => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_new_fund_rec_type
,p_mode => l_mode
);
ozf_utility_pvt.write_conc_log(l_full_name || 'Status Updated for the fund ' || l_new_fund_rec.fund_id || ' without notifying workflow');
ozf_funds_pvt.update_fund(p_api_version=> 1.0
,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_fund_rec=> l_fund_rec
,p_mode=> l_mode
);
SELECT * FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id
AND fund_id NOT IN (SELECT NVL(prev_fund_id,-99) FROM ozf_funds_all_b
WHERE prev_fund_id = p_fund_id);
SELECT * from ozf_funds_all_vl
WHERE fund_id = p_fund_id
AND status_code = 'ACTIVE'
AND fund_id NOT IN (SELECT NVL(prev_fund_id,-99) FROM ozf_funds_all_b
WHERE prev_fund_id = p_fund_id);
SELECT fund_id FROM ozf_funds_all_b
WHERE prev_fund_id = p_fund_id;
SELECT fund_id, parent_fund_id
FROM ozf_funds_all_b
WHERE prev_fund_id is NULL
AND status_code = 'ACTIVE'
CONNECT BY PRIOR fund_id = parent_fund_id
START WITH parent_fund_id = p_fund_id;