[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select benefit_type_code from pv_ge_benefits_b
where benefit_id = pc_benefit_id;
select user_role, wf_message_type, wf_message_name
from pv_notification_setups
where benefit_id = pc_benefit_id
and entity_status = pc_status
AND user_role like pc_userrole;
select fnd_user.user_name
from pv_partner_accesses acc, jtf_rs_resource_extns res, fnd_user
where acc.partner_id = pc_partner_id
and acc.resource_id = res.resource_id
and res.user_id = fnd_user.user_id;
select fnd_user.user_name
from pv_ge_temp_approvers apr, fnd_user
where apr.arc_appr_for_entity_code = pc_benefit_type
and apr.appr_for_entity_id = pc_entity_id
and apr.approver_id = fnd_user.user_id
AND APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
and apr.approver_type_code = 'USER';
execute immediate 'select ' || p_user_callback_api ||
'(:itemtype, :entity_id, :usertype, :status) from dual'
into l_role_list using l_benefit_type, p_entity_id, l_user_type, p_status ;
* Updates : Made changes for Bug 5189270.
*/
PROCEDURE CONVERT_LIST_TO_TABLE(p_role_list IN VARCHAR2
,x_role_list_tbl OUT NOCOPY wf_directory.UserTable)
IS
l_index NUMBER := 1;
select 'P' from pv_benft_status_maps
WHERE partner_status_code = pc_status
and benefit_type = pc_benefit_type;
select ref.referral_code, ben.benefit_type_code, ref.entity_type, ref.entity_id_linked_to,
ref.decline_reason_code, ref.order_id
from pv_referrals_b ref, pv_ge_benefits_b ben
where ref.referral_id = pc_entity_id and ref.benefit_id = ben.benefit_id;
select lead_number, prm_referral_code from as_leads_all where lead_id = pc_lead_id;
select lead_number, decode(source_system,'REFERRAL',source_primary_reference,NULL)
from as_sales_leads where sales_lead_id = pc_lead_id;
select referral_code from pv_referrals_b where referral_id = pc_referral_id;
select decline_reason_code from pv_referrals_b
where referral_id = P_ENTITY_ID;
select apr.approver_id, jrre.source_name
from pv_ge_temp_approvers apr, jtf_rs_resource_extns jrre
where apr.arc_appr_for_entity_code = pc_benefit_type
and apr.appr_for_entity_id = pc_referral_id
and apr.approver_id = jrre.user_id
and APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT')
and apr.approver_type_code = 'USER';
FOR x IN (SELECT COUNT(*) approved_count
FROM pv_ge_history_log_vl
WHERE ARC_HISTORY_FOR_ENTITY_CODE = 'PVREFFRL' AND
history_for_entity_id = p_entity_id AND
message_code IN ('PV_LG_REF_APPROVED',
'PV_LG_REF_APPROVED_DUP_OPPTY',
'PV_LG_REF_APPROVED_DUP_LEAD',
'PV_LG_REF_APPROVED_DUP_REF'))
LOOP
l_approved_count := x.approved_count;
FOR x IN (SELECT COUNT(*) approved_count
FROM pv_ge_history_log_vl
WHERE ARC_HISTORY_FOR_ENTITY_CODE = 'PVDEALRN' AND
history_for_entity_id = p_entity_id AND
message_code IN ('PV_LG_DEAL_APPROVED',
'PV_LG_DEAL_APPROVED_DUP_DEAL',
'PV_LG_DEAL_APPROVED_DUP_LEAD',
'PV_LG_DEAL_APPROVED_DUP_OPPTY'))
LOOP
l_approved_count := x.approved_count;
l_log_params_tbl.DELETE;
SELECT REF.benefit_id, REF.referral_id, REF.partner_id
FROM pv_referrals_b REF
WHERE REF.claim_id = l_claim_id;
ELSIF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus') THEN
-- -----------------------------------------------------------------
-- if status_code is 'CANCELLED'
-- -----------------------------------------------------------------
l_referral_status_code := 'COMP_CANCELLED';
IF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus' AND
UPPER(l_claim_status_code) <> 'CANCELLED')
THEN
RETURN 'SUCCESS';
IF (LOWER(l_event_name) = 'oracle.apps.ozf.claim.updatestatus' AND
l_claim_id IS NULL)
THEN
if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
'pv.plsql.PV_BENFT_STATUS_CHANGE.CLAIM_REF_STATUS_CHANGE_SUB',
'Event name: ' || l_event_name || ' ' ||
'--> There is no claim ID for this event.');
UPDATE pv_referrals_b
SET referral_status = l_referral_status_code
WHERE referral_id = l_referral_id;
select notes, NOTES_DETAIL
from jtf_notes_vl
where source_object_code = pc_entity_type
AND SOURCE_OBJECT_ID = pc_entity_id
AND NOTE_STATUS = 'E' -- only publish notes
ORDER BY CREATION_DATE DESC;
select function_id from fnd_form_functions
where function_name = pc_function_name;
'select
a.referral_id,
a.referral_code,
a.referral_name,
c.party_name,
a.customer_name,
ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(Null,a.customer_address1,a.customer_address2,
a.customer_address3,a.customer_address4,a.customer_CITY,a.customer_COUNTY,a.customer_STATE,
a.customer_PROVINCE,a.customer_POSTAL_CODE,Null,a.customer_country,Null,Null,Null,Null,Null,
NULL,NULL,NULL,2000,1,1) ADDRESS,
hzp.party_name pt_contact_name,
(CASE
WHEN creator.source_first_name IS NULL AND creator.source_last_name IS NULL
AND creator.category = ''PARTY'' THEN
(SELECT hzp.party_name
FROM hz_relationships hzr, hz_parties hzp
WHERE hzr.party_id = creator.source_id
AND hzr.subject_type=''PERSON''
AND hzr.subject_id = hzp.party_id
AND hzr.object_type= ''ORGANIZATION'')
ELSE
creator.source_name
END) creator_name,
a.customer_contact_first_name || '' '' || a.customer_contact_last_name,
lkup.meaning,
A.creation_date,
a.actual_compensation_amt || '' '' || a.currency_code
from
pv_referrals_vl a,
pv_partner_profiles b,
hz_parties c,
jtf_rs_resource_extns pt_cont,
jtf_rs_resource_extns creator,
pv_lookups lkup,
hz_relationships hzr,
hz_parties hzp
where a.referral_id = :1
and a.partner_id = b.partner_id
and b.partner_party_id = c.party_id
and a.partner_contact_resource_id = pt_cont.resource_id
and a.created_by = creator.user_id
and a.referral_status = lkup.lookup_code
and lkup.lookup_type = ''PV_BENEFIT_ENTITY_STATUS''
AND hzr.party_id = pt_cont.source_id
AND hzr.subject_type=''PERSON''
AND hzr.subject_id = hzp.party_id
AND hzr.object_type= ''ORGANIZATION'' ';
SELECT
usr.user_name
FROM
pv_partner_profiles prof,
hz_relationships pr2,
jtf_rs_resource_extns pj,
fnd_user usr
WHERE
prof.partner_id = pc_partner_id
and prof.partner_party_id = pr2.object_id
and pr2.subject_table_name = 'HZ_PARTIES'
and pr2.object_table_name = 'HZ_PARTIES'
and pr2.directional_flag = 'F'
and pr2.relationship_code = 'EMPLOYEE_OF'
and pr2.relationship_type = 'EMPLOYMENT'
and (pr2.end_date is null or pr2.end_date > sysdate)
and pr2.status = 'A'
and pr2.party_id = pj.source_id
and pj.category = 'PARTY'
and usr.user_id = pj.user_id
and (usr.end_date > sysdate OR usr.end_date IS NULL)
and exists(select 1 from jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
where PJ.user_name = jtfp1.principal_name
and jtfp1.is_user_flag=1
and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=0
and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
and jtfrp.positive_flag = 1
and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name = pc_permission
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN' );
select usr.user_name
from jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm, jtf_rs_resource_extns pj,
fnd_user usr
where PJ.user_name = jtfp1.principal_name
and pj.category = 'EMPLOYEE'
and usr.user_id = pj.user_id
and (usr.end_date > sysdate OR usr.end_date IS NULL)
and jtfp1.is_user_flag=1
and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=0
and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
and jtfrp.positive_flag = 1
and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name = pc_permission
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN';
select partner_id from pv_referrals_b where referral_id = pc_entity_id;
select fnd.user_name
from fnd_user fnd, pv_referrals_b ref, jtf_rs_resource_extns jtf
where ref.partner_contact_resource_id = jtf.resource_id
and jtf.user_id = fnd.user_id
and ref.referral_id = pc_entity_id;
select c.user_name
from as_sales_leads a, pv_referrals_b b, jtf_rs_resource_extns c
where b.referral_id = pc_entity_id
and b.entity_id_linked_to = a.sales_lead_id
and a.assign_to_salesforce_id = c.resource_id;
select c.user_name
from as_accesses_all a, pv_referrals_b b, jtf_rs_resource_extns c
where b.referral_id = pc_entity_id
and b.entity_id_linked_to = a.lead_id
and a.salesforce_id = c.resource_id
and c.category = 'EMPLOYEE';
select distinct fnd_user.user_name
from pv_ge_temp_approvers apr, fnd_user
where apr.arc_appr_for_entity_code = pc_benefit_type
and apr.appr_for_entity_id = pc_entity_id
and apr.approver_id = fnd_user.user_id
AND APR.approval_status_code IN ('PENDING_APPROVAL','PENDING_DEFAULT','APPROVED')
and apr.approver_type_code = 'USER';
select b.meaning from pv_referrals_b a, FND_LOOKUP_VALUES_VL b
where a.referral_id = pc_entity_id
and a.decline_reason_code = b.lookup_code
and b.lookup_type = 'PV_REFERRAL_DECLINE_REASON';
select c.CONCAT_CAT_PARENTAGE, b.amount || ' ' || a.currency_code amount
from pv_referrals_b a, pv_referred_products b, eni_prod_den_hrchy_parents_v c
where a.referral_id = pc_entity_id
and a.referral_id = b.referral_id
and b.product_category_set_id = c.category_set_id
and b.product_category_id = c.category_id;
select max(length(c.CONCAT_CAT_PARENTAGE)), max(length(to_char(b.amount) || ' ' || a.currency_code))
from pv_referrals_b a, pv_referred_products b, eni_prod_den_hrchy_parents_v c
where a.referral_id = pc_entity_id
and a.referral_id = b.referral_id
and b.product_category_set_id = c.category_set_id
and b.product_category_id = c.category_id;
select attribute_code,attribute_label_long
from ak_attributes_vl ak
where attribute_application_id = 522
AND ATTRIBUTE_code in ('ASF_AMOUNT','ASF_PRODUCT_CATEGORY');