create procedure pr_sent as var_num integer
;
var_time timestamp
;
i_longth integer
;
var_id NVARCHAR (32)
;
var_sterm varchar (100)
;
var_match varchar (200)
;
var_date date
;
I_EXIST_CAMPAIGN INTEGER
;
I_EXIST_SERVICE INTEGER
;
CURSOR C_values for select
       distinct SocialDataUUID,
       SocialPostSearchTermText ,
       SOCIALPOSTCREATIONDATE
from VOICEOFCUSTOMER
where match_flag='0'
;
 
BEGIN ----PART1 VOICEOFCUSTOMERDETAIL
insert
into VOICEOFCUSTOMERDETAIL (SocialDataUUID ,
       RULE,
       COUNTER,
       VOICEOFCUSTOMERTYPE ,
       VOICEOFCUSTOMERTEXT ) select
       T1.SocialDataUUID,
       T2.TA_RULE,
       T2.TA_COUNTER,
       T2.TA_TYPE,
       T2.TA_TOKEN
from "SAP_SOMI"."sap.hba.somi.db::SOCIALDATA" t1,
       "SAP_SOMI"."$TA_INDEXVOICEOFCUST" t2
where t1.SocialDataUUID=t2.SocialDataUUID
and NOT EXISTS (SELECT
       1
       FROM VOICEOFCUSTOMERDETAIL a4
       WHERE t2.SocialDataUUID=a4.SocialDataUUID )
and (upper (t2.TA_TYPE) = 'TOPIC'
       or t2.TA_TYPE ='PRODUCT'
       OR UPPER (t2.TA_TYPE) LIKE '%REQUEST'
       OR (UPPER (t2.TA_TYPE) LIKE '%SENTIMENT'
              AND UPPER (t2.TA_TYPE) <> 'SENTIMENT' )
       OR UPPER (t2.TA_TYPE) LIKE '%PROBLEM'
       OR UPPER (t2.TA_TYPE) LIKE '%SERVICE' )
;
---VOICEOFCUSTOMER
insert
into VOICEOFCUSTOMER ("SOCIALDATAUUID" ,
       SocialPostSearchTermText ,
       "VOICEOFCUSTOMERAVERAGEOPINION",
       SOCIALPOSTCREATIONDATETIME ,
       SOCIALPOSTCreationDateYear ,
       SOCIALPOSTCreationDateMonth ,
       SOCIALPOSTCREATIONDATE ,
       "SENTIMENT_AVG" ,
       "MATCH_FLAG" ) select
       DISTINCT SocialDataUUID,
       SocialPostSearchTermText,
       case when type_num = 0
or type_num is null
then 'Neutral' when type_num >0
AND type_num <= 1
then 'Weak Positive' when type_num>1
AND type_num <=2
then 'Strong Positive' when type_num>= -1
AND type_num <0
then 'Weak Negative' when type_num>= -2
AND type_num <-1
then 'Strong Negative'
else 'No Sentiment'
end as VoiceOfCustomerSubType,
       to_timestamp(CreationDateTime),
       SUBSTR (CreationDateTime,
       1,
       4 ) AS CreationYear ,
       SUBSTR (CreationDateTime,
       1,
       7 ) AS CreationMonth ,
       TO_DATE (CreationDateTime) AS CreationDate,
       map (type_num,
       null,
       0,
       type_num) AS SENTIMENT_AVG,
       '0' as match_flag
from (select
       t1.SocialDataUUID,
       t1.SocialPostSearchTermText,
       substr_before (to_char(TO_timestamp (TRIM (SUBSTR (T1.CreationDateTime,
       INSTR (T1.CreationDateTime,
       ',')+1,
       INSTR (T1.CreationDateTime,
       '+')-INSTR (T1.CreationDateTime,
       ',')-1 ) ),
       'DD MON YYYY HH24:MI:SS')),
       '.' ) as CreationDateTime,
       t2.TA_TYPE,
       T2.TA_COUNTER,
       T2.TA_TOKEN ,
       t4.type_num
       from "SAP_SOMI"."sap.hba.somi.db::SOCIALDATA" t1
       LEFT JOIN (select
       sum (case when TA_TYPE ='NeutralSentiment'
                     then 0 when TA_TYPE ='StrongNegativeSentiment'
                     then -2 when TA_TYPE ='StrongPositiveSentiment'
                     then 2 when TA_TYPE ='WeakNegativeSentiment'
                     then -1 when TA_TYPE ='WeakPositiveSentiment'
                     then 1
                     else 0
                     end ) / count (1) as type_num,
       SocialDataUUID
              from (select
       a2.SocialDataUUID,
       a2.TA_TYPE
                     from "SAP_SOMI"."$TA_INDEXVOICEOFCUST" a2
                     where NOT EXISTS (SELECT
       1
                           FROM VOICEOFCUSTOMER a4
                           WHERE a2.SocialDataUUID=a4.SocialDataUUID )
                     and ( UPPER (a2.TA_TYPE) LIKE '%SENTIMENT'
                           AND UPPER (a2.TA_TYPE) <> 'SENTIMENT' ) )
              group by SocialDataUUID ) t4 ON t1.SocialDataUUID=t4.SocialDataUUID ,
       "SAP_SOMI"."$TA_INDEXVOICEOFCUST" t2
       where t1.SocialDataUUID=t2.SocialDataUUID
       and NOT EXISTS (SELECT
       1
              FROM VOICEOFCUSTOMER a4
              WHERE t2.SocialDataUUID=a4.SocialDataUUID )
       and (upper (t2.TA_TYPE) = 'TOPIC'
              or t2.TA_TYPE ='PRODUCT'
              OR UPPER (t2.TA_TYPE) LIKE '%REQUEST'
              OR (UPPER (t2.TA_TYPE) LIKE '%SENTIMENT'
                     AND UPPER (t2.TA_TYPE) <> 'SENTIMENT' )
              OR UPPER (t2.TA_TYPE) LIKE '%PROBLEM'
              OR UPPER (t2.TA_TYPE) LIKE '%SERVICE' ) )T
;
-----PART2 MATCH_CAMP
for i_val as c_values do var_id := i_val.SocialDataUUID
;
var_sterm := i_val.SocialPostSearchTermText
;
var_date:=i_val.SOCIALPOSTCREATIONDATE
;
--INSERT INTO VOICEOFCUSTCAMP
SELECT
       COUNT (1)
INTO I_EXIST_CAMPAIGN
FROM VIEWS
WHERE VIEW_NAME='sap.crm.sentiment/AT_CAMP'
AND SCHEMA_NAME='_SYS_BIC'
;
 
IF I_EXIST_CAMPAIGN=1
THEN insert
into VOICEOFCUSTCAMP select
       var_id,
       var_sterm,
       guid,
       CAMP_TEXT,
       PRODUCT_TEXT,
       SNIPPETS (CAMP_TEXT),
       SNIPPETS (PRODUCT_TEXT)
from "_SYS_BIC"."sap.crm.sentiment/AT_CAMP" t1
where contains ( (CAMP_TEXT,
       PRODUCT_TEXT),
       var_sterm ,
       FUZZY (0.9) )
AND PLANSTARTDATE < var_date
;
 
END
IF
;
--INSERT INTO VOICEOFCUSTSERVICE
SELECT
       COUNT (1)
INTO I_EXIST_SERVICE
FROM VIEWS
WHERE VIEW_NAME='sap.crm.sentiment/AT_SERV'
AND SCHEMA_NAME='_SYS_BIC'
;
 
IF I_EXIST_SERVICE=1
THEN insert
into VOICEOFCUSTSERV select
       var_id,
       var_sterm,
       guid,
       DESCRIPTION,
       SHORT_TEXT,
       SNIPPETS (DESCRIPTION),
       SNIPPETS (SHORT_TEXT)
from "_SYS_BIC"."sap.crm.sentiment/AT_SERV" t1
where contains ( (DESCRIPTION,
       SHORT_TEXT),
       var_sterm ,
       FUZZY (0.9) )
;
 
END
IF
;
update VOICEOFCUSTOMER
set match_flag='1'
where match_flag='0'
and SocialDataUUID=var_id
;
 
end for
;
 
END
;