[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Primary_Partner(
p_entity_id IN integer,
p_primary_partner_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR
)
IS
l_entity_id integer;
SELECT
entity_id,
version,
attr_value,
attr_value_extn
from
pv_enty_attr_values
where
entity_id = p_entity_id and
latest_flag = 'Y' AND
attribute_id = 3;
SELECT
PARTY_NAME, PARTY_ID
FROM
PV_PARTNER_PROFILES,
HZ_PARTIES
WHERE
HZ_PARTIES.PARTY_ID = PV_PARTNER_PROFILES.PARTNER_PARTY_ID AND
PV_PARTNER_PROFILES.PARTNER_ID = CV_PARTNER_ID;
select ATTR_VALUE from pv_enty_attr_values tabl where entity_id = CV_PARTNER_ID and ATTRIBUTE_id= 3
and version = (SELECT
MAX(case (version -1)
when 0 then
1
else
(version -1)
end ) fROM pv_enty_attr_values
WHERE
entity_id = CV_PARTNER_ID AND
ATTRIBUTE_ID = 3 AND
LAST_UPDATE_DATE = (SELECT MIN(LAST_UPDATE_DATE) FROM pv_enty_attr_values WHERE ATTR_VALUE_EXTN = 'Y' AND
ENTITY_ID = tabl.entity_id and attribute_id = 3)
)
union
SELECT ATTR_VALUE FROM pv_enty_attr_values
WHERE
entity_id = CV_PARTNER_ID and
ATTRIBUTE_ID = 3 AND
ENTITY_ID not IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE entity_id = CV_PARTNER_ID and ATTRIBUTE_ID = 3 and ATTR_VALUE_eXTN is not NULL) AND
LATEST_FLAG = 'Y';
END Update_Primary_Partner;
select
lookup_code,
tag
from
fnd_lookup_values
where
lookup_type = 'PV_PARTNER_TYPE_RANKING' and
enabled_flag = 'Y' AND
language = userenv('LANG') and
TAG IS NOT NULL
order by tag asc ;
select
count(distinct entity_id) as partner_count
from
pv_enty_attr_values
where
attribute_id = 3 and
attr_value_extn = 'Y';
select
attr_value , count(attr_value) as partner_count
from
pv_enty_attr_values
where
attribute_id = 3 and latest_flag = 'Y' and attr_value_extn = 'Y'
group by
attr_value
having
count(attr_value) > 0
order by
count(attr_value) desc;
SELECT
(select count(status) from pv_partner_profiles where status = 'A') Active,
(select count(status) from pv_partner_profiles where status = 'I') Inactive
FROM DUAL;
SELECT
ATTR_VALUE,
entity_id
FROM (
SELECT
ATTR_VALUE,
entity_id
FROM pv_enty_attr_values tabl
WHERE
ATTRIBUTE_ID = 3
AND ATTR_VALUE = 'VAD'
AND VERSION = (SELECT
MAX(case (version -1)
when 0 then
1
else
(version -1)
end )
FROM pv_enty_attr_values
WHERE
entity_id = tabl.entity_id AND
LAST_UPDATE_DATE = ( SELECT MIN(LAST_UPDATE_DATE)
FROM pv_enty_attr_values
WHERE ATTR_VALUE_EXTN = 'Y'
AND ENTITY_ID = tabl.entity_id
AND attribute_id = 3
)
)
UNION
SELECT
ATTR_VALUE,
entity_id
FROM pv_enty_attr_values
WHERE
ATTRIBUTE_ID = 3
AND LATEST_FLAG = 'Y'
AND ATTR_VALUE = 'VAD'
AND entity_id in ( SELECT entity_id
FROM pv_enty_attr_values
WHERE ATTRIBUTE_ID = 3
AND attr_value = 'VAD'
AND latest_flag = 'Y'
)
AND ENTITY_ID NOT IN (SELECT ENTITY_ID
FROM pv_enty_attr_values
WHERE ATTRIBUTE_ID = 3
AND attr_value_extn is not null
)
)
partners,
pv_partner_profiles profiles
WHERE partners.entity_id = profiles.partner_id
AND profiles.status in ('A', 'I');
select distinct
details.attr_value,
details.entity_id
from
(
SELECT
min(tag) as tag,
entity_id
FROM
(
SELECT ATTR_VALUE,entity_id
FROM pv_enty_attr_values tabl
WHERE
ENTITY_ID IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE ATTRIBUTE_ID = 3 AND ATTR_VALUE_EXTN = 'Y') AND
ATTRIBUTE_ID = 3 AND
VERSION = (SELECT
MAX(case (version -1)
when 0 then
1
else
(version -1)
end )
FROM pv_enty_attr_values
WHERE
entity_id = tabl.entity_id and
LAST_UPDATE_DATE = (SELECT MIN(LAST_UPDATE_DATE)
FROM pv_enty_attr_values
WHERE ATTR_VALUE_EXTN = 'Y'
AND ENTITY_ID = tabl.entity_id
AND attribute_id = 3)
)
UNION
SELECT ATTR_VALUE,entity_id
FROM pv_enty_attr_values
WHERE
ATTRIBUTE_ID = 3 AND
ENTITY_ID NOT IN (SELECT ENTITY_ID
FROM pv_enty_attr_values
WHERE ATTRIBUTE_ID = 3
AND attr_value_extn is not null ) AND
LATEST_FLAG = 'Y'
) attr,
fnd_lookup_values lkp where
ENTITY_ID NOT IN (SELECT ENTITY_ID FROM pv_enty_attr_values WHERE ENTITY_ID = attr.entity_id and ATTR_VALUE = 'VAD') and
attr.attr_value = lkp.lookup_code and
lkp.language = userenv('LANG') and
lkp.lookup_type = 'PV_PARTNER_TYPE_RANKING'
group by entity_id
) sorted,
pv_enty_attr_values details,
fnd_lookup_values match,
pv_partner_profiles ppp
where
details.attr_value = lookup_code and
match.lookup_type = 'PV_PARTNER_TYPE_RANKING' and
match.language = userenv('LANG') and
sorted. entity_id = details.entity_id and
details.entity_id = ppp.partner_id and
ppp.status IN ('A' , 'I') and
to_number(sorted.tag) = to_number(match.tag)
order by details.entity_id;
SELECT distinct
attr_value,
entity_id
FROM
pv_enty_attr_values partners,
pv_partner_profiles profiles
WHERE
partners.attribute_id = 3
AND partners.latest_flag = 'Y'
AND partners.attr_value = 'VAD'
AND partners.entity_id not in (
SELECT
distinct entity_id
FROM
pv_enty_attr_values
WHERE
attribute_id = 3
AND attr_value_extn is not null
AND latest_flag = 'Y'
)
AND partners.entity_id = profiles.partner_id
AND profiles.status in ('A', 'I');
SELECT distinct
attr_value,
entity_id
FROM pv_enty_attr_values attr,
fnd_lookup_values lkp,
pv_partner_profiles ppp
WHERE
attribute_id = 3
AND latest_flag = 'Y'
AND entity_id not in (
select
distinct entity_id
from
pv_enty_attr_values
where
attribute_id = 3 and
latest_flag = 'Y' AND
(attr_value = 'VAD' OR attr_value_extn is not null )
)
AND attr.attr_value = lkp.lookup_code
AND lkp.lookup_type = 'PV_PARTNER_TYPE_RANKING'
AND lkp.language = userenv('LANG')
AND attr.entity_id = ppp.partner_id
AND ppp.status IN ('A' , 'I')
AND to_number(tag) = (select min(to_number(tag))
from fnd_lookup_values
where
lookup_code in (select attr_value
from pv_enty_attr_values
where attribute_id=3
and entity_id = attr.entity_id
and latest_flag = 'Y')
and lookup_type = 'PV_PARTNER_TYPE_RANKING'
and tag is not null
and language = userenv('LANG')
and lookup_code <> 'VAD'
);
select count(*)
into l_count
from fnd_lookup_values
where lookup_type = 'PV_PARTNER_TYPE_RANKING'
and language = userenv('LANG')
and enabled_flag = 'Y';
select
COUNT(*) into l_count
from
pv_attribute_codes_b
where
attribute_id = 3 and
attr_code <> 'VAD' AND
ATTR_CODE NOT IN
(
SELECT
LOOKUP_CODE
FROM
FND_LOOKUP_VALUES
WHERE
LOOKUP_CODE<> 'VAD' AND
language = userenv('LANG') and
lookup_type = 'PV_PARTNER_TYPE_RANKING'and
enabled_flag = 'Y'
);
select
count(*) into l_count
from
FND_LOOKUP_VALUES
where
LOOKUP_CODE <> 'VAD' AND
lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
language = userenv('LANG') and
enabled_flag = 'Y' and
TAG IS NULL;
select
count(to_number(TAG)) into l_count
from
FND_LOOKUP_VALUES
where
LOOKUP_CODE <> 'VAD' AND
lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
enabled_flag = 'Y' and
language = userenv('LANG') and
TAG IS NOT NULL;
select
count(distinct tag) into l_distinct_count
from
FND_LOOKUP_VALUES
where
LOOKUP_CODE <> 'VAD' AND
lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
language = userenv('LANG') and
TAG IS NOT NULL;
select
count(tag) into l_count
from
FND_LOOKUP_VALUES
where
LOOKUP_CODE <> 'VAD' AND
lookup_type = 'PV_PARTNER_TYPE_RANKING' AND
language = userenv('LANG') and
TAG IS NOT NULL;
Update_Primary_Partner
(
p_entity_id => l_entity_id,
p_primary_partner_type => l_attr_value,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Update_Primary_Partner
(
p_entity_id => l_entity_id,
p_primary_partner_type => l_attr_value ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Update_Primary_Partner(
p_entity_id => l_entity_id,
p_primary_partner_type => l_attr_value ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Update_Primary_Partner(
p_entity_id => l_entity_id,
p_primary_partner_type => l_attr_value ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);