[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from as_osi_leads_all
where lead_id = lead_id_in;
select *
from as_osi_lead_ovl_all
where osi_lead_id = lead_id_in
order by ovm_code;
select substr(addr.address1||' '||addr.city||','||addr.state,1,50) site_name,
substr(cust.party_name,1,50) cust_name, substr(oppy.description,1,50) oppy_name
from as_leads_all oppy
,hz_parties cust
,as_party_addresses_v addr
where oppy.lead_id = lead_id_in
and cust.party_id = oppy.customer_id
and addr.address_id = oppy.address_id;
l_osi_rec.last_updated_by := to_char(osi.last_updated_by);
l_osi_rec.last_update_login := to_char(osi.last_update_login);
l_osi_rec.last_updated_by := null;
l_osi_rec.last_update_login := null;
PROCEDURE osi_lead_update
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_osi_rec IN OSI_REC_TYPE,
p_osi_ovd_tbl IN OSI_OVD_TBL_TYPE,
x_return_status OUT VARCHAR2,
x_msg_count OUT VARCHAR2,
x_msg_data OUT VARCHAR2
) is
cursor ovd_cur (lead_id_in in varchar2) is
select *
from as_osi_lead_ovl_all
where osi_lead_id = lead_id_in
order by ovm_code;
l_delete_flag boolean;
rgmissc(l_osi_rec.last_updated_by);
select osi_lead_id
into l_dummy
from as_osi_leads_all
where osi_lead_id = p_osi_rec.lead_id;
update as_osi_leads_all
set
last_update_date = sysdate
,last_updated_by = nvl(to_number(l_osi_rec.last_updated_by),1)
,OSI_LEAD_ID = l_osi_rec.OSI_LEAD_ID
,LEAD_ID = l_osi_rec.LEAD_ID
,CVEHICLE = to_number(l_osi_rec.CVEHICLE)
,CNAME_ID = to_number(l_osi_rec.CNAME_ID)
,CONTR_DRAFTING_REQ = l_osi_rec.CONTR_DRAFTING_REQ
,PRIORITY = l_osi_rec.PRIORITY
,SENIOR_CONTR_PERSON_ID = to_number(l_osi_rec.SENIOR_CONTR_PERSON_ID)
,CONTR_SPEC_PERSON_ID = to_number(l_osi_rec.CONTR_SPEC_PERSON_ID)
,BOM_PERSON_ID = to_number(l_osi_rec.BOM_PERSON_ID)
,LEGAL_PERSON_ID = to_number(l_osi_rec.LEGAL_PERSON_ID)
,HIGHEST_APVL = l_osi_rec.HIGHEST_APVL
,CURRENT_APVL_STATUS = l_osi_rec.CURRENT_APVL_STATUS
,SUPPORT_APVL = l_osi_rec.SUPPORT_APVL
,INTERNATIONAL_APVL = l_osi_rec.INTERNATIONAL_APVL
,CREDIT_APVL = l_osi_rec.CREDIT_APVL
,FIN_ESCROW_REQ = l_osi_rec.FIN_ESCROW_REQ
,FIN_ESCROW_STATUS = l_osi_rec.FIN_ESCROW_STATUS
,CSI_ROLLIN = l_osi_rec.CSI_ROLLIN
,LICENCE_CREDIT_VER = l_osi_rec.LICENCE_CREDIT_VER
,SUPPORT_CREDIT_VER = l_osi_rec.SUPPORT_CREDIT_VER
,MD_DEAL_SUMMARY = l_osi_rec.MD_DEAL_SUMMARY
,PROD_AVAIL_VER = l_osi_rec.PROD_AVAIL_VER
,SHIP_LOCATION = l_osi_rec.SHIP_LOCATION
,TAX_EXEMPT_CERT = l_osi_rec.TAX_EXEMPT_CERT
,NL_REV_ALLOC_REQ = l_osi_rec.NL_REV_ALLOC_REQ
,CONSULTING_CC = l_osi_rec.CONSULTING_CC
,SENIOR_CONTR_NOTES = l_osi_rec.SENIOR_CONTR_NOTES
,LEGAL_NOTES = l_osi_rec.LEGAL_NOTES
,BOM_NOTES = l_osi_rec.BOM_NOTES
,CONTR_NOTES = l_osi_rec.CONTR_NOTES
,PO_FROM = l_osi_rec.PO_FROM
,CONTR_TYPE = l_osi_rec.CONTR_TYPE
,CONTR_STATUS = l_osi_rec.CONTR_STATUS
,EXTRA_DOCS = to_number(l_osi_rec.EXTRA_DOCS)
where osi_lead_id = l_osi_rec.osi_lead_id;
insert into as_osi_leads_all(
creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,OSI_LEAD_ID
,LEAD_ID
,CVEHICLE
,CNAME_ID
,CONTR_DRAFTING_REQ
,PRIORITY
,SENIOR_CONTR_PERSON_ID
,CONTR_SPEC_PERSON_ID
,BOM_PERSON_ID
,LEGAL_PERSON_ID
,HIGHEST_APVL
,CURRENT_APVL_STATUS
,SUPPORT_APVL
,INTERNATIONAL_APVL
,CREDIT_APVL
,FIN_ESCROW_REQ
,FIN_ESCROW_STATUS
,CSI_ROLLIN
,LICENCE_CREDIT_VER
,SUPPORT_CREDIT_VER
,MD_DEAL_SUMMARY
,PROD_AVAIL_VER
,SHIP_LOCATION
,TAX_EXEMPT_CERT
,NL_REV_ALLOC_REQ
,CONSULTING_CC
,SENIOR_CONTR_NOTES
,LEGAL_NOTES
,BOM_NOTES
,CONTR_NOTES
,PO_FROM
,CONTR_TYPE
,CONTR_STATUS
,EXTRA_DOCS
) values (
sysdate
,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
,sysdate
,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
,1
,l_osi_rec.OSI_LEAD_ID
,l_osi_rec.LEAD_ID
,to_number(l_osi_rec.CVEHICLE)
,to_number(l_osi_rec.CNAME_ID)
,l_osi_rec.CONTR_DRAFTING_REQ
,l_osi_rec.PRIORITY
,to_number(l_osi_rec.SENIOR_CONTR_PERSON_ID)
,to_number(l_osi_rec.CONTR_SPEC_PERSON_ID)
,to_number(l_osi_rec.BOM_PERSON_ID)
,to_number(l_osi_rec.LEGAL_PERSON_ID)
,l_osi_rec.HIGHEST_APVL
,l_osi_rec.CURRENT_APVL_STATUS
,l_osi_rec.SUPPORT_APVL
,l_osi_rec.INTERNATIONAL_APVL
,l_osi_rec.CREDIT_APVL
,l_osi_rec.FIN_ESCROW_REQ
,l_osi_rec.FIN_ESCROW_STATUS
,l_osi_rec.CSI_ROLLIN
,l_osi_rec.LICENCE_CREDIT_VER
,l_osi_rec.SUPPORT_CREDIT_VER
,l_osi_rec.MD_DEAL_SUMMARY
,l_osi_rec.PROD_AVAIL_VER
,l_osi_rec.SHIP_LOCATION
,l_osi_rec.TAX_EXEMPT_CERT
,l_osi_rec.NL_REV_ALLOC_REQ
,l_osi_rec.CONSULTING_CC
,l_osi_rec.SENIOR_CONTR_NOTES
,l_osi_rec.LEGAL_NOTES
,l_osi_rec.BOM_NOTES
,l_osi_rec.CONTR_NOTES
,l_osi_rec.PO_FROM
,l_osi_rec.CONTR_TYPE
,l_osi_rec.CONTR_STATUS
,to_number(l_osi_rec.EXTRA_DOCS)
);
l_delete_flag := TRUE;
l_delete_flag := FALSE;
if l_delete_flag then
delete from as_osi_lead_ovl_all
where osi_lead_id = ovd.osi_lead_id
and ovm_code = ovd.ovm_code;
insert into as_osi_lead_ovl_all(
creation_date
,created_by
,last_update_date
,last_updated_by
,OSI_LEAD_ID
,OVM_CODE
) values (
sysdate
,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
,sysdate
,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
,l_osi_rec.OSI_LEAD_ID
,l_osi_ovd_tbl(i).ovd_code);
end osi_lead_update;
select cvehicle, vehicle
from as_osi_contr_vhcl_base
where nvl(enabled_flag,'Y') = 'Y'
order by 1;
select CNAME_ID, CONTR_NAME, CVEHICLE
from as_osi_contr_names_base
where nvl(enabled_flag,'Y') = 'Y'
order by CVEHICLE, CONTR_NAME;
select lkp_type, lkp_code, lkp_value
from as_osi_lookup
where (lkp_type = upper(lkp_type_in)
or lkp_type_in = 'ALL')
and nvl(enabled_flag,'Y') = 'Y'
order by 1,2;
select distinct alv.location_segment_user_value lkp_code, alv.location_segment_description lkp_value
from ar_location_values alv,
ar_system_parameters asp
where alv.location_structure_id = asp.location_structure_id
and alv.parent_segment_id is null
order by 1;
select ppf.person_id person_id, max(ppf.email_address) email_address, max(ppf.last_name)
from per_people_f ppf,
as_salesforce as1
where as1.sales_group_id = sales_group_id_in
and as1.employee_person_id is not null
and as1.employee_person_id = ppf.person_id
and sysdate between ppf.effective_start_date and ppf.effective_end_date
and as1.status_code = 'A'
group by ppf.person_id
UNION
select ppf.person_id person_id, max(ppf.email_address) email_address, max(ppf.last_name)
from per_people_f ppf
where ppf.person_id = supervisor_id_in
and sysdate between ppf.effective_start_date and ppf.effective_end_date
group by ppf.person_id
ORDER BY 3,2;
select max(ppf.person_id) supervisor_id, max(asg.sales_group_id) sales_group_id
from per_people_f ppf
,as_sales_groups asg
where ppf.last_name = last_name_in
and upper(ppf.email_address) = email_address_in
and trunc(nvl(ppf.effective_start_date,sysdate)) <= trunc(sysdate)
and trunc(nvl(ppf.effective_end_date,sysdate)) >= trunc(sysdate)
and ppf.person_id = asg.manager_person_id;
select cc, center_name
from as_osi_cons_ccs_base
where nvl(enabled_flag,'Y') = 'Y'
order by 1;
select ovm_code, ovm_value
from as_osi_overlay_base
where nvl(enabled_flag,'Y') = 'Y'
order by 2;