

-- ATTENTION

-- ONLY TO BE EXECUTED IF SALES FORCE INTERFACE IS NOT INSTALLED YET!!! 

-- Script with required Functions and Views for the Sales Force Interface, needs to be executed once



-- mapps v8 codes to sf codes  
create or replace view v8_mappingconfigcodes as
/* Used for mapping of combo box values from
   - Opportunties suite 8 to Opportunities Sales force.
   - Fields of Conference booking direct mapped to sales force ( no intermediate xopp).
   From xopp to SF is 2 way
   From Conference booking to SF is one way.
*/     
-- Opportunity types S8 to SF ( xopt - Type)
select 'xopt' tablename, 1002 tableid, (select xopt_longdesc from xopt where xopt_id=1002) V8_desc, 'New Customer' SF_desc from dual
union all
select 'xopt' tablename, 1001 tableid, (select xopt_longdesc from xopt where xopt_id=1001) V8_desc, 'Existing Customer - Upgrade' SF_desc from dual
union all
select 'xopt' tablename, 1003 tableid, (select xopt_longdesc from xopt where xopt_id=1003) V8_desc, 'Existing Customer - Replacement' SF_desc from dual
union all
select 'xopt' tablename, 1004 tableid, (select xopt_longdesc from xopt where xopt_id=1004) V8_desc, 'Existing Customer - Downgrade' SF_desc from dual
union all
select 'xopt' tablename, 1005 tableid, (select xopt_longdesc from xopt where xopt_id=1004) V8_desc, 'New Customer' SF_desc from dual
union all
-- Opportunity Stage S8 to SF (xops - stagename)
select 'xops' tablename, 1000 tableid, (select xops_longdesc from xops where xops_id=1000) V8_desc, 'Closed Won' SF_desc from dual
union all
select 'xops' tablename, 1001 tableid, (select xops_longdesc from xops where xops_id=1001) V8_desc, 'Value Proposition' SF_desc from dual
union all
select 'xops' tablename, 1002 tableid, (select xops_longdesc from xops where xops_id=1002) V8_desc, 'Id. Decision Makers' SF_desc from dual
union all
select 'xops' tablename, 1003 tableid, (select xops_longdesc from xops where xops_id=1003) V8_desc, 'Perception Analysis' SF_desc from dual
union all
select 'xops' tablename, 1004 tableid, (select xops_longdesc from xops where xops_id=1004) V8_desc, 'Proposal/Price Quote' SF_desc from dual
union all
select 'xops' tablename, 1005 tableid, (select xops_longdesc from xops where xops_id=1005) V8_desc, 'Negotiation/Review' SF_desc from dual
union all
select 'xops' tablename, 1006 tableid, (select xops_longdesc from xops where xops_id=1006) V8_desc, 'Needs Analysis' SF_desc from dual
union all
select 'xops' tablename, 1007 tableid, (select xops_longdesc from xops where xops_id=1007) V8_desc, 'Prospecting' SF_desc from dual
union all
select 'xops' tablename, 1008 tableid, (select xops_longdesc from xops where xops_id=1008) V8_desc, 'Qualification' SF_desc from dual
union all
select 'xops' tablename, 1009 tableid, (select xops_longdesc from xops where xops_id=1008) V8_desc, 'Needs Analysis' SF_desc from dual
union all
-- Opportunity Lead Source S8 to SF (xopl - LeadSource)
select 'xopl' tablename, 1000 tableid, (select xopl_longdesc from xopl where xopl_id=1000) V8_desc, 'Other' SF_desc from dual
union all
select 'xopl' tablename, 1001 tableid, (select xopl_longdesc from xopl where xopl_id=1001) V8_desc, 'Other' SF_desc from dual
union all
select 'xopl' tablename, 1002 tableid, (select xopl_longdesc from xopl where xopl_id=1002) V8_desc, 'Partner Referral' SF_desc from dual
union all
select 'xopl' tablename, 1003 tableid, (select xopl_longdesc from xopl where xopl_id=1003) V8_desc, 'Other' SF_desc from dual
union all
select 'xopl' tablename, 1004 tableid, (select xopl_longdesc from xopl where xopl_id=1004) V8_desc, 'Other' SF_desc from dual
union all
select 'xopl' tablename, 1005 tableid, (select xopl_longdesc from xopl where xopl_id=1005) V8_desc, 'Partner' SF_desc from dual
union all
select 'xopl' tablename, 1007 tableid, (select xopl_longdesc from xopl where xopl_id=1007) V8_desc, 'Purchased List' SF_desc from dual
union all
select 'xopl' tablename, 1008 tableid, (select xopl_longdesc from xopl where xopl_id=1008) V8_desc, 'Web' SF_desc from dual
union all
select 'xopl' tablename, 1006 tableid, (select xopl_longdesc from xopl where xopl_id=1006) V8_desc, 'Phone Inquiry' SF_desc from dual
union all
select 'xopl' tablename, 1010 tableid, (select xopl_longdesc from xopl where xopl_id=1010) V8_desc, 'Other' SF_desc from dual
union all
-- Function Type conference booking S8 to SF (YFTY - Opportunity_Type_c)
select 'yfty' tablename, 100 tableid, (select yfty_longdesc from yfty where yfty_id=100) V8_desc, 'Meeting' SF_desc from dual
union all
select 'yfty' tablename, 101 tableid, (select yfty_longdesc from yfty where yfty_id=101) V8_desc, 'Celebration' SF_desc from dual
union all
select 'yfty' tablename, 102 tableid, (select yfty_longdesc from yfty where yfty_id=102) V8_desc, 'Catering' SF_desc from dual
union all
select 'yfty' tablename, 103 tableid, (select yfty_longdesc from yfty where yfty_id=103) V8_desc, 'Wedding' SF_desc from dual
union all
select 'yfty' tablename, 104 tableid, (select yfty_longdesc from yfty where yfty_id=104) V8_desc, 'Conference' SF_desc from dual
union all
select 'yfty' tablename, 105 tableid, (select yfty_longdesc from yfty where yfty_id=105) V8_desc, 'Seminar' SF_desc from dual
union all
select 'yfty' tablename, 106 tableid, (select yfty_longdesc from yfty where yfty_id=106) V8_desc, 'Banquets' SF_desc from dual
union all
select 'yfty' tablename, 107 tableid, (select yfty_longdesc from yfty where yfty_id=107) V8_desc, 'Incentive' SF_desc from dual
union all
select 'yfty' tablename, 108 tableid, (select yfty_longdesc from yfty where yfty_id=108) V8_desc, 'Presentation' SF_desc from dual
union all
-- Function Spaces conference booking S8 to SF (YFSC - Locations_c)
select 'yfsc' tablename, 1005 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1005) V8_desc, 'Room I' SF_desc from dual
union all
select 'yfsc' tablename, 1006 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1006) V8_desc, 'Room II' SF_desc from dual
union all
select 'yfsc' tablename, 1007 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1007) V8_desc, 'Room III' SF_desc from dual
union all
select 'yfsc' tablename, 1008 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1008) V8_desc, 'Ballroom I' SF_desc from dual
union all
select 'yfsc' tablename, 1009 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1009) V8_desc, 'Ballroom II' SF_desc from dual
union all
select 'yfsc' tablename, 1010 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1010) V8_desc, 'Ballroom I+II' SF_desc from dual
union all
select 'yfsc' tablename, 1011 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1011) V8_desc, 'Restaurant' SF_desc from dual
union all
select 'yfsc' tablename, 1012 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1012) V8_desc, 'Foyer' SF_desc from dual
union all
select 'yfsc' tablename, 1145 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1145) V8_desc, 'Wintergarden' SF_desc from dual
union all
select 'yfsc' tablename, 1164 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1164) V8_desc, 'Conference Suite 100' SF_desc from dual
union all
select 'yfsc' tablename, 1165 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1165) V8_desc, 'Conference Suite 101' SF_desc from dual
union all
select 'yfsc' tablename, 1166 tableid, (select YFSC_longdesc from YFSC where YFSC_id=1166) V8_desc, 'Conference Suite 102' SF_desc from dual;


--should base on translation tables
-- maps V8 longdescs to salesforce longdescs as defined in v8_mappingconfigcodes view
create or replace function v8_sf_translate(tname in VARCHAR2, tid in NUMBER, valuestr in VARCHAR2 ) return VARCHAR2 
is
  cursor v8mappingcodes is
    select * from v8_mappingconfigcodes
    where tablename = tname and tableid = tid and v8_desc=valuestr;
  rowv8mappingcodes v8mappingcodes%rowtype;  
  resultstr varchar2(3000);
begin
  resultstr := '';
  open v8mappingcodes;
  fetch v8mappingcodes into rowv8mappingcodes;
  resultstr := rowv8mappingcodes.SF_desc;
  close v8mappingcodes;
  return substr(resultstr,1,3000);
  exception
    when no_data_found then
      null;
end;
/

create or replace function v8_ccm_getfunctionspaces(ybomid in integer) return varchar2
is
  cursor cursorybom is 
    select distinct yfsc_id, yfsc_longdesc 
    from yfsc,yebs,yevm,ybom
    where yfsc_id=yebs_yfsc_id and yebs_yevm_id=yevm_id and yevm_ybom_id=ybom_id and rownum<11 and ybom_id=ybomid;
  rowybom cursorybom%ROWTYPE;
  resultvalue VARCHAR2(3000);
begin
  resultvalue := '';
  open cursorybom;
  loop
    fetch cursorybom into rowybom;
    exit when cursorybom%notfound;
    if resultvalue <> ' ' then
      resultvalue := resultvalue || ';';
    end if;
    resultvalue:=resultvalue || v8_sf_translate('yfsc', rowybom.yfsc_id,rowybom.yfsc_longdesc);  
  end loop;  
  close cursorybom;
  return resultvalue;
  exception
    when no_data_found then 
      null;
end;
/

CREATE OR REPLACE function v8_sf_oppr_getfieldasstring(xoppid in number, fieldname in varchar2) return VARCHAR2
is
  cursor ybomxopp is
    select xopp.*, ybom.*,
      (select xops_longdesc from xops where xops_id=xopp_xops_id) xops_longdesc,
      (select xcmg_globalID from xcmg where upper(xcmg_crstype)='SALESFORCE'   and nvl(xcmg_inactive, 0) = 0 and xcmg_xcms_id=xopp_account_xcms_id 
         and xcmg_id = (select max(v.xcmg_id) from xcmg v where v.xcmg_xcms_id=xopp_account_xcms_id) ) xcmg_globalID,
      (select xced_salesforce_id from xced where xced_id=xopp_owner_xced_id) xced_salesforce_id,
      (select yfty_longdesc yfty from yfty where yfty_id=ybom_yfty_id) yfty_longdesc,
      (select wmlg_time_creation from wmlg where upper(wmlg_tablename)='XOPP' and wmlg_table_id=xopp_id ) wmlg_time_creation,
      (select xopl_longdesc from xopl where xopl_id=xopp_xopl_id) xopl_longdesc,
      (select xopt_longdesc from xopt where xopt_id=xopp_xopt_id) xopt_longdesc,
      (select xopc_sf_id from xopc where xopc_id=xopp_xopc_id) xopc_sf_id
    from xopp, ybom
    where xopp_id=xoppid 
      and xopp_id=ybom_xopp_id(+);
  
  rowybomxopp ybomxopp%ROWTYPE;
  resultvalue VARCHAR2(3000);
begin
  open ybomxopp;
  fetch ybomxopp into rowybomxopp;
  resultvalue := '';
  if fieldname='ybom_id' then resultvalue := to_char(rowybomxopp.ybom_id); end if;
  if fieldname='xopp_name' then resultvalue := rowybomxopp.xopp_name; end if;
  if fieldname='xcmg_globalID' then resultvalue := rowybomxopp.xcmg_globalID; end if;
  if fieldname='xced_salesforce_id' then resultvalue := rowybomxopp.xced_salesforce_id; end if;
  if fieldname='xopt_longdesc' then resultvalue := v8_sf_translate('xopt', rowybomxopp.xopp_xopt_id,rowybomxopp.xopt_longdesc); end if;
  if fieldname='yfty_longdesc' then resultvalue :=  v8_sf_translate('yfty', rowybomxopp.ybom_yfty_id,rowybomxopp.yfty_longdesc); end if;
  if fieldname='wmlg_time_creation' then resultvalue :=  to_char(rowybomxopp.wmlg_time_creation, 'YYYY-MM-DD')|| 'T' || to_char(rowybomxopp.wmlg_time_creation, 'HH24:MI:SS'); end if;
  --if fieldname='wmlg_time_creation' then 
  --  resultvalue := '';
  --  if rowybomxopp.wmlg_time_update = 0 then
  --      resultvalue := to_char(rowybomxopp.wmlg_time_creation, 'YYYY-MM-DD')|| 'T' || to_char(rowybomxopp.wmlg_time_creation, 'HH24:MI:SS'); 
--    end if;  
--  end if;
  if fieldname='xopp_closedate' then resultvalue :=  to_char(rowybomxopp.xopp_closedate, 'yyyy-mm-dd'); end if;
  if fieldname='xopl_longdesc' then resultvalue := v8_sf_translate('xopl', rowybomxopp.xopp_xopl_id,rowybomxopp.xopl_longdesc); end if;
  if fieldname='xopp_totalopportunityquantity' then resultvalue := to_char(rowybomxopp.xopp_totalopportunityquantity); end if;
  if fieldname='xopp_amount' then resultvalue := to_char(rowybomxopp.xopp_amount, '999990D09',  'NLS_NUMERIC_CHARACTERS='||''''||'.,'||''''); end if;
  if fieldname='xops_longdesc' then resultvalue := v8_sf_translate('xops', rowybomxopp.xopp_xops_id,rowybomxopp.xops_longdesc); end if;
  if fieldname='ybom_arrival' then  resultvalue :=  to_char(rowybomxopp.ybom_arrival, 'yyyy-mm-dd'); end if;
  if fieldname='ybom_departure' then  resultvalue :=  to_char(rowybomxopp.ybom_departure, 'yyyy-mm-dd'); end if;
  if fieldname='xopp_probability' then resultvalue := to_char(rowybomxopp.xopp_probability, '999990D09',  'NLS_NUMERIC_CHARACTERS='||''''||'.,'||''''); end if;
  if fieldname='xopc_sf_id' then resultvalue := rowybomxopp.xopc_sf_id; end if;
  if fieldname='xopp_nextstep' then resultvalue := rowybomxopp.xopp_nextstep; end if;
  if fieldname='xopp_description' then resultvalue := rowybomxopp.xopp_description; end if;
  if fieldname='xopp_maincompetitor' then resultvalue := rowybomxopp.xopp_maincompetitor; end if;
  if fieldname='enumeratedfunctionspaces' then resultvalue := v8_ccm_getfunctionspaces(rowybomxopp.ybom_id);end if;
  close ybomxopp;
  
  return resultvalue;
  exception
    when no_data_found then 
      null;
  
end;
/

/*
Mapping between possible fields of Suite 8 and SF.
Fields in suite 8 are fixed and hardcoded. 
SF_FIELD please select the appropriate field of salesforce to link.
Please refer to SFFD for all sales force fields (after import)
FieldStatus =0 download/upload 1 only download
*/
create or replace view v8_mappingsalesforce as
select 'ybom' V8_TableName, 'ybom_id' V8_FieldName,   'Bookingsnumber__c' SF_Field, 'VARCHAR2' FieldType,  0 FieldStatus , 4 Role from dual
union all
select 'xopp', 'xopp_name','Name', 'VARCHAR2',  0 FieldStatus, 4 from dual
union all
select 'xcmg', 'xcmg_globalID', 'AccountId', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'xced', 'xced_salesforce_id', 'OwnerId', 'VARCHAR2',  0 FieldStatus, 4 from dual
union all
select 'xopt', 'xopt_longdesc', 'Type', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'N/A', 'N/A', 'Soort_bijeenkomst_c', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'yfty', 'yfty_longdesc', 'Opportunity_Type__c', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'wmlg', 'wmlg_time_creation', 'Date_Requested__c', 'DATE',  0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_closedate', 'CloseDate', 'DATE',  0 FieldStatus, 4  from dual
union all
select 'xopl', 'xopl_longdesc', 'LeadSource', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_totalopportunityquantity', 'TotalOportunityQuantity', 'NUMBER',  0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_amount', 'Amount', 'NUMBER', 0 FieldStatus, 4  from dual
union all
select 'xops', 'xops_longdesc', 'StageName', 'VARCHAR2', 0 FieldStatus, 4  from dual
union all
select 'ybom', 'ybom_arrival', 'Event_Start_Date__c', 'DATE',  0 FieldStatus, 4  from dual
union all
select 'ybom', 'ybom_departure', 'Event_End_Date__c', 'DATE', 0 FieldStatus, 4  from dual
union all
select 'N/A', 'N/A', 'Opportunitie_voor_c', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'ybom', 'enumeratedfunctionspaces', 'Locations__c', 'VARCHAR2', 0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_probability', 'Probability', 'NUMBER', 0 FieldStatus, 4  from dual
union all
select 'N/A', 'N/A', 'ForecastCategorieName', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'xopc', 'xopc_sf_id', 'CampaignId', 'VARCHAR2', 0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_nextstep', 'NextStep', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_description', 'Description', 'VARCHAR2',  0 FieldStatus, 4  from dual
union all
select 'xopp', 'xopp_maincompetitor', 'MainCompetitors_c', 'VARCHAR2', 0 FieldStatus, 4  from dual
;

create or replace view v8_salesforce_opportunity as
select
  xopp_id, 
  (select wmlg_time_update from wmlg where upper(wmlg_tablename)='XOPP' and wmlg_table_id=xopp_id) wmlg_time_update, 
  V8_FieldName,   V8_TableName, SF_Field, v8_sf_oppr_getfieldasstring(xopp_id, V8_FieldName) Value, FieldType, FieldStatus
from xopp, (select * from v8_mappingsalesforce where FieldStatus=0) --only upload fields
where nvl(xopp_inactive,0) = 0
;
--opportunities that needs to upload

create or replace view v8_SF_ID_opportunity_upload as
select *
from wsfs
where wsfs_sync_date < 
  (select wmlg_time_update from wmlg where upper(wmlg_tablename)='XOPP' and upper(wsfs_table_name)='XOPP' and wsfs_table_id=wmlg_table_id)
  union all  select 0 , 'XOPP',  xopp_id ,  null , '' from xopp WHERE xopp_id not in (select wsfs_table_id from wsfs where upper(wsfs_table_name)='XOPP') and nvl(xopp_inactive,0)=0
;

--opportunity data to upload
create or replace view v8_SF_opportunity_upload as
select * 
from v8_salesforce_opportunity v1, v8_SF_ID_opportunity_upload v2
where v1.xopp_id=v2.wsfs_table_id
;

--mapping between v8 booking and v8 opportunities longdescs; this has to be extended
/* Used for mapping of combo box values from
    - Fields of Conference booking mapped to XOPP
   This is one way from conference booking to XOPP  
   xopl
   
*/ 
create or replace view v8_mappingbookingandopt as
-- Conference booking Source Code S8 to Opportunity LeadSource S8 (yras - xopl)
select 'yras' tablename, 'Source' FieldName, 1049 tableid, (select yras_longdesc from yras where yras_id=1049) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1008 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1008) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1140 tableid, (select yras_longdesc from yras where yras_id=1140) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 101 tableid, (select yras_longdesc from yras where yras_id=101) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 102 tableid, (select yras_longdesc from yras where yras_id=102) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 103 tableid, (select yras_longdesc from yras where yras_id=103) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 104 tableid, (select yras_longdesc from yras where yras_id=104) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 105 tableid, (select yras_longdesc from yras where yras_id=105) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 114 tableid, (select yras_longdesc from yras where yras_id=114) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1008 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1008) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1015 tableid, (select yras_longdesc from yras where yras_id=1015) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1016 tableid, (select yras_longdesc from yras where yras_id=1016) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1017 tableid, (select yras_longdesc from yras where yras_id=1017) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1164 tableid, (select yras_longdesc from yras where yras_id=1164) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
select 'yras' tablename, 'Source' FieldName, 1173 tableid, (select yras_longdesc from yras where yras_id=1173) Longdesc, 'xopl' opportunitytablename, 'LeadSource' OpportunityFieldName, 1009 opportunitytableid, (select xopl_longdesc from xopl where xopl_id=1009) opportunityLongdesc from dual
union all
-- Conference booking Status S8 to Opportunity Stage S8 (yest- xops)
select 'yest', 'Status',2,(select yest_longdesc from yest where yest_id=2), 'xops', 'Stage',1006,(select xops_longdesc from xops where xops_id=1006) from dual
union all
select 'yest', 'Status',3,(select yest_longdesc from yest where yest_id=3), 'xops', 'Stage',1004,(select xops_longdesc from xops where xops_id=1004) from dual
union all
select 'yest', 'Status',4,(select yest_longdesc from yest where yest_id=4), 'xops', 'Stage',1000,(select xops_longdesc from xops where xops_id=1000) from dual
union all
select 'yest', 'Status',5,(select yest_longdesc from yest where yest_id=5), 'xops', 'Stage',1009,(select xops_longdesc from xops where xops_id=1009) from dual
union all
select 'yest', 'Status',6,(select yest_longdesc from yest where yest_id=6), 'xops', 'Stage',1009,(select xops_longdesc from xops where xops_id=1009) from dual
union all
select 'yest', 'Status',7,(select yest_longdesc from yest where yest_id=7), 'xops', 'Stage',1009,(select xops_longdesc from xops where xops_id=1009) from dual
union all
select 'yest', 'Status',1015,(select yest_longdesc from yest where yest_id=1015), 'xops', 'Stage',1008,(select xops_longdesc from xops where xops_id=1008) from dual
union all
select 'yest', 'Status',1109,(select yest_longdesc from yest where yest_id=1109), 'xops', 'Stage',1009,(select xops_longdesc from xops where xops_id=1009) from dual
union all 
-- Conference booking Market code S8 to Opportunity Type S8 (xcma - xopt)
select 'xcma' tablename, 'Market' FieldName, 100 tableid, (select xcma_longdesc from xcma where xcma_id=100) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1001 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1001) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 101 tableid, (select xcma_longdesc from xcma where xcma_id=101) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1001 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1001) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 102 tableid, (select xcma_longdesc from xcma where xcma_id=102) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1005 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1005) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 103 tableid, (select xcma_longdesc from xcma where xcma_id=103) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1001 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1001) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 104 tableid, (select xcma_longdesc from xcma where xcma_id=104) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1001 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1001) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 105 tableid, (select xcma_longdesc from xcma where xcma_id=105) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1001 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1001) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 1162 tableid, (select xcma_longdesc from xcma where xcma_id=1162) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1005 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1005) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 1015 tableid, (select xcma_longdesc from xcma where xcma_id=1015) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1005 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1005) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 1081 tableid, (select xcma_longdesc from xcma where xcma_id=1081) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1005 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1005) opportunityLongdesc from dual
union all
select 'xcma' tablename, 'Market' FieldName, 1138 tableid, (select xcma_longdesc from xcma where xcma_id=1138) Longdesc, 'xopt' opportunitytablename, 'Type' OpportunityFieldName, 1005 opportunitytableid, (select xopt_longdesc from xopt where xopt_id=1005) opportunityLongdesc from dual
;
