[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(offset_cnt) from
(select a.lead_id,count(*)-1 offset_cnt from
pv_lead_workflows a, pv_lead_assignments b, as_leads_all c, as_statuses_b d
where a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and a.latest_routing_flag = 'Y'
and a.routing_status = 'ACTIVE'
and b.status in ('PT_APPROVED', 'CM_APP_FOR_PT')
and a.routing_type = 'JOINT'
and a.lead_id = c.lead_id
and c.status = d.status_code
and d.opp_open_status_flag = 'Y'
and exists
(select 1
from pv_partner_accesses aa, jtf_rs_rep_managers bb,
jtf_rs_group_usages cc where bb.parent_resource_id = pc_id
and nvl(bb.end_date_active, sysdate) >= sysdate
and bb.group_id = cc.group_id and cc.usage = 'PRM'
and bb.resource_id = aa.resource_id
and aa.partner_id = b.partner_id
)
group by a.lead_id);
select sum(offset_AMT) from
(SELECT A.LEAD_ID,SUM(pv_check_match_pub.currency_conversion(c.total_amount,
c.currency_code,sysdate, pc_currency)) /count(*) * (count(*)-1) offset_amt
FROM PV_LEAD_WORKFLOWS A, PV_LEAD_ASSIGNMENTS B, AS_LEADS_ALL C, AS_STATUSES_B D
WHERE A.WF_ITEM_TYPE = B.WF_ITEM_TYPE
AND A.WF_ITEM_KEY = B.WF_ITEM_KEY
AND A.LATEST_ROUTING_FLAG = 'Y'
AND A.ROUTING_STATUS = 'ACTIVE'
and b.status IN ('PT_APPROVED', 'CM_APP_FOR_PT')
AND A.ROUTING_TYPE = 'JOINT'
AND A.LEAD_ID = C.LEAD_ID
AND C.TOTAL_AMOUNT IS NOT NULL
and c.status = d.status_code
and d.opp_open_status_flag = 'Y'
and exists
(select 1
from pv_partner_accesses aa, jtf_rs_rep_managers bb,
jtf_rs_group_usages cc where bb.parent_resource_id = pc_id
and nvl(bb.end_date_active, sysdate) >= sysdate
and bb.group_id = cc.group_id and cc.usage = 'PRM'
and bb.resource_id = aa.resource_id
and aa.partner_id = b.partner_id
)
GROUP BY A.LEAD_ID);
select a.attribute_id, c.name, b.return_type, b.attribute_type, nvl(b.additive_flag, 'N') additive_flag,
a.sql_text, a.batch_sql_text, a.refresh_frequency, a.refresh_frequency_uom, a.last_refresh_date,
nvl(decode(a.refresh_frequency_uom, 'HOUR', a.refresh_frequency * 1/24,
'DAY', a.refresh_frequency * 1,
'WEEK', (trunc(nvl(a.last_refresh_date,SYSDATE), 'IW') + a.refresh_frequency * 7)
-nvl(a.last_refresh_date,sysdate),
'MONTH', add_months(trunc(NVL(a.last_refresh_date,SYSDATE), 'MM'), a.refresh_frequency)
-nvl(a.last_refresh_date,sysdate)
),0) refresh_interval_days
from pv_entity_attrs a, pv_attributes_b b, pv_attributes_tl c
where a.entity = 'PARTNER_TREND'
and a.attribute_id = b.attribute_id
and a.enabled_flag = 'Y' and b.performance_flag = 'Y'
and b.enabled_flag = 'Y'
and b.attribute_id = c.attribute_id
and c.LANGUAGE = userenv('LANG');
SELECT partner_id
FROM pv_partner_profiles pvpp
WHERE pvpp.status = 'A' AND
pvpp.partner_resource_id IS NOT NULL AND
pvpp.creation_date >= pc_creation_date
union all
select partner_id from pv_partner_profiles pvpp
where status = 'A' AND
pvpp.partner_resource_id IS NOT NULL AND
partner_id = pc_partner_id;
FOR x IN (SELECT COUNT(*) count FROM v$session
WHERE module LIKE 'Refresh Partner Trends Program%')
LOOP
IF (x.count > 0) THEN
Debug('There is already a Refresh Partner Trends CC session running.');
select nvl(max(attribute_trend_id),0) + 1 into l_next_trend_id from pv_entity_attr_trends;
l_last_incr_refresh_str := FND_PROFILE.VALUE('PV_PT_TREND_LAST_UPDATE');
Debug('system since the refresh date will be retrieved and updated.');
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and entity_id = l_new_pt_rec.partner_id
and attribute_id in ( select b.attribute_id from pv_entity_attrs a, pv_attributes_b b
where a.entity = 'PARTNER_TREND' and a.attribute_id = b.attribute_id
and b.performance_flag = 'Y' and b.additive_flag = 'Y')
and trend_timeline between l_from_date and l_to_date;
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and attribute_id in ( select b.attribute_id from pv_entity_attrs a, pv_attributes_b b
where a.entity = 'PARTNER_TREND' and a.attribute_id = b.attribute_id
and b.performance_flag = 'Y' and b.additive_flag = 'N')
and entity_id = l_new_pt_rec.partner_id and trend_timeline = trunc(l_run_date, 'MM');
insert into pv_entity_attr_trends
(ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
(l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER',
l_partner_id_tbl(i), l_attribute_id_tbl(i), l_result_tbl(i),
l_currency_code_tbl(i), l_currency_date_tbl(i), l_month_tbl(i), 'MONTH');
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and attribute_id = lc_attr_rec.attribute_id
and trend_timeline between l_from_date and l_to_date;
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and attribute_id = lc_attr_rec.attribute_id and trend_timeline = trunc(l_run_date, 'MM');
insert into pv_entity_attr_trends
(ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
(l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER', l_partner_id_tbl(i),
lc_attr_rec.attribute_id, l_result_tbl(i), l_currency_code, l_currency_date, l_month_tbl(i), 'MONTH');
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and attribute_id = lc_attr_rec.attribute_id
and trend_timeline between l_from_date and l_to_date;
delete from pv_entity_attr_trends where entity = 'PARTNER' and time_uom = 'MONTH'
and attribute_id = lc_attr_rec.attribute_id and trend_timeline = trunc(l_run_date, 'MM');
insert into pv_entity_attr_trends
(ATTRIBUTE_TREND_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,ENTITY,ENTITY_ID,ATTRIBUTE_ID,ATTR_VALUE,
CURRENCY_CODE,CURRENCY_DATE,TREND_TIMELINE,TIME_UOM) values
(l_attr_trend_id_tbl(i), sysdate, 1, sysdate, 1, 1, 1, 'PARTNER', l_partner_id_tbl(i),
lc_attr_rec.attribute_id, l_result_tbl(i), l_currency_code, l_currency_date, l_month_tbl(i), 'MONTH');
update pv_entity_attrs set last_refresh_date = l_run_date where attribute_id = lc_attr_rec.attribute_id
and entity = 'PARTNER_TREND';
FND_PROFILE.PUT('PV_PT_TREND_LAST_UPDATE', l_run_date_str);
l_ret_val := FND_PROFILE.SAVE('PV_PT_TREND_LAST_UPDATE', l_run_date_str,'SITE');