Procedure
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Procedure as PDF for free.
More details
- Words: 1,202
- Pages: 6
CREATE OR REPLACE PROCEDURE Insert_Multiple_Recipient(alert_id_seq IN NUMBER, eventid IN NUMBER, initid IN NUMBER, pdnOrderid IN VARCHAR2, crid IN VARCHAR2) AS new_resourceid NUMBER; new_resourcetype NUMBER; new_initid NUMBER; record_exists NUMBER; new_emailid our_user.OUR_USER_EMAIL_ID%TYPE; our_user_id OUR_USER.our_user_id%TYPE; our_id OUR_USER.our_id%TYPE; our_user_email_id OUR_USER.our_user_email_id%TYPE; CURSOR all_recipients IS SELECT ces1.resource_id,ces1.resource_type FROM ces_event_subscribers ces1 where ces1.ces_event_id = eventid and ces1.resource_type <>1; CURSOR C1 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID IN (select distinct OUT_USER_ID from OUT_USER_TEAM_ASSOC out where out.OUT_TEAM_ID IN (select distinct OUT_TEAM_ID from OUT_USER_TEAM_ASSOC where OUT_USER_ID IN (select ord.ORD_INITIATOR from CBM_BP_MAPPING cbm,ORD_ORDER ord where cbm.CBM_CUSTOMER_ID=ord.ORD_CUSTOMERID and ord.ord_orderid=pdnOrderid) ) ); C1_row C1%ROWTYPE; CURSOR C2 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID IN (select distinct OUT_USER_ID from OUT_USER_TEAM_ASSOC out where out.OUT_TEAM_ID IN (select distinct OUT_TEAM_ID from OUT_USER_TEAM_ASSOC where OUT_USER_ID IN (SELECT our.our_id FROM CCR_CHANGE_REQUEST_CR cr, our_user our WHERE cr.CCR_INITIATED_BY_USER = our.OUR_USER_ID AND cr.CCR_CR_ID=crid) ) ); C2_row C2%ROWTYPE; CURSOR C3 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID from CBM_BP_MAPPING cbm,ORD_ORDER ord where cbm.CBM_CUSTOMER_ID=ord.ORD_CUSTOMERID
and ord.ord_orderid=pdnOrderid) );
C3_row C3%ROWTYPE; CURSOR C4 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID from CBM_BP_MAPPING cbm where cbm.CBM_CUSTOMER_ID in (select cr.CST_CUSTOMER_ID CST_CUSTOMER_CR cr
from where
cr.CST_CR_ID=crid)
) );
C4_row C4%ROWTYPE; CURSOR C5 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID = (select OUR_SUPERVISOR_ID from OUR_USER our where our.OUR_ID IN (select our.our_id from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID from CBM_BP_MAPPING cbm,ORD_ORDER ord where cbm.CBM_CUSTOMER_ID=ord.ORD_CUSTOMERID and ord.ord_orderid=pdnOrderid)
) )
); C5_row C5%ROWTYPE; CURSOR C6 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID = (select OUR_SUPERVISOR_ID from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID
f
rom CBM_BP_MAPPING cbm
w
here cbm.CBM_CUSTOMER_ID in (select cr.CST_CUSTOMER_ID from CST_CUSTOMER_CR cr where cr.CST_CR_ID=crid)
) )
); C6_row C6%ROWTYPE; CURSOR C7 IS select OTR_RESOURCE_ID_TEAM,OTR_TEAM_ID,OTR_RECIPIENT from OTR_TEAM_RELATED_RESOURCE where OTR_RESOURCE_ID_TEAM = new_resourceid; C7_row C7%ROWTYPE; CURSOR C8 IS select csd.CSD_DESCRIPTION, csd.CSD_RESOURCE_ID, csd.CSD_RECIPIENT from CSD_EVENT_SUBSCRIBERS_DETAILS csd where csd.CSD_RESOURCE_TYPE=6 AND csd.CSD_RESOURCE_ID =new_resourceid; C8_row C8%ROWTYPE; CURSOR C9 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID IN (select distinct OUT_USER_ID from OUT_USER_TEAM_ASSOC out where out.OUT_TEAM_ID IN (select to_number(csd.CSD_RECIPIENT) from CSD_EVENT_SUBSCRIBERS_DETAILS csd where csd.CSD_RESOURCE_TYPE=7 AND csd.CSD_RESOURCE_ID =2) ); C9_row C9%ROWTYPE; -- This procedure is called from the procedure st_checkEvent procedure. It takes the input as alert_seq_id, eventid,initid, pdnOrderId and customer id. -- The purpose of this procedure is to find all the possible combination of Resource Id and Resource Type for a given Eventid, -- and for all the resourcetypes found, we are going to find the userid and the corresponding email id. Then we would copy -- the record for the input alert_seq_id from CAL_ALERTS table and insert it into the new table CAO_ALERTS_OTHERS. Then we would -- update this record with the new userid and emailid. --For a given eventid, we have to get all the possible resource_id and resources_type. ---The alerts are currently inserted into CAL_ALERTS table on for the Initiator where the --resource_type is 1. ---When the resource_type is anything other than 1 we have to insert the alert into another --table CAO_ALERT_OTHERS with the same alert message but the email_id of either a single --person or the group of people depending upon the resource type. ---We have to insert as many rows into the table as we have members of the resource type BEGIN OPEN all_recipients; LOOP FETCH all_recipients INTO new_resourceid,new_resourcetype; EXIT WHEN all_recipients%NOTFOUND; new_initid := 0; new_emailid := '0'; if new_resourcetype = 2 THEN --2 THEN
if pdnOrderid is not null then OPEN C1; LOOP FETCH C1 INTO C1_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C1%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C1_row.our_id, cal_recipient_email_id=C1_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C2; LOOP FETCH C2 INTO C2_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C2%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C2_row.our_id, cal_recipient_email_id=C2_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 3 THEN if pdnOrderid is not null then OPEN C3; LOOP FETCH C3 INTO C3_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C3%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C3_row.our_id, cal_recipient_email_id=C3_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C4;
our_user_email_id;
LOOP FETCH C4 INTO C4_row;--our_user_id, our_id,
EXIT WHEN C4%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C4_row.our_id, cal_recipient_email_id=C4_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 4 then if pdnOrderid is not null then OPEN C5; LOOP FETCH C5 INTO C5_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C5%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C5_row.our_id, cal_recipient_email_id=C5_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C6; LOOP FETCH C6 INTO C6_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C6%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C6_row.our_id, cal_recipient_email_id=C6_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 5 then OPEN C7; LOOP
FETCH C7 INTO C7_row; EXIT WHEN C7%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C7_row.otr_resource_id_team, cal_recipient_email_id=C7_row.otr_recipient where cal_recipient_user_id = initid ; end if; END LOOP; end if; if new_resourcetype = 6 then OPEN C8; LOOP FETCH C8 INTO C8_row; EXIT WHEN C8%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C8_row.CSD_DESCRIPTION, cal_recipient_email_id=C8_row.CSD_RECIPIENT where cal_recipient_user_id = initid ; end if; END LOOP; end if; if new_resourcetype = 7 then OPEN C9; LOOP FETCH C9 INTO C9_row; EXIT WHEN C9%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C9_row.our_id, cal_recipient_email_id=C9_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; END LOOP; CLOSE all_recipients; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END; /
and ord.ord_orderid=pdnOrderid) );
C3_row C3%ROWTYPE; CURSOR C4 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID from CBM_BP_MAPPING cbm where cbm.CBM_CUSTOMER_ID in (select cr.CST_CUSTOMER_ID CST_CUSTOMER_CR cr
from where
cr.CST_CR_ID=crid)
) );
C4_row C4%ROWTYPE; CURSOR C5 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID = (select OUR_SUPERVISOR_ID from OUR_USER our where our.OUR_ID IN (select our.our_id from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID from CBM_BP_MAPPING cbm,ORD_ORDER ord where cbm.CBM_CUSTOMER_ID=ord.ORD_CUSTOMERID and ord.ord_orderid=pdnOrderid)
) )
); C5_row C5%ROWTYPE; CURSOR C6 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID = (select OUR_SUPERVISOR_ID from OUR_USER our where our.OUR_QUARTZ_ID IN (select distinct ACC_MGR from CUSTOMER where BP_ID IN (select cbm.CBM_QZ_BP_ID
f
rom CBM_BP_MAPPING cbm
w
here cbm.CBM_CUSTOMER_ID in (select cr.CST_CUSTOMER_ID from CST_CUSTOMER_CR cr where cr.CST_CR_ID=crid)
) )
); C6_row C6%ROWTYPE; CURSOR C7 IS select OTR_RESOURCE_ID_TEAM,OTR_TEAM_ID,OTR_RECIPIENT from OTR_TEAM_RELATED_RESOURCE where OTR_RESOURCE_ID_TEAM = new_resourceid; C7_row C7%ROWTYPE; CURSOR C8 IS select csd.CSD_DESCRIPTION, csd.CSD_RESOURCE_ID, csd.CSD_RECIPIENT from CSD_EVENT_SUBSCRIBERS_DETAILS csd where csd.CSD_RESOURCE_TYPE=6 AND csd.CSD_RESOURCE_ID =new_resourceid; C8_row C8%ROWTYPE; CURSOR C9 IS select our.our_user_id, our.OUR_ID, our.OUR_USER_EMAIL_ID from OUR_USER our where our.OUR_ID IN (select distinct OUT_USER_ID from OUT_USER_TEAM_ASSOC out where out.OUT_TEAM_ID IN (select to_number(csd.CSD_RECIPIENT) from CSD_EVENT_SUBSCRIBERS_DETAILS csd where csd.CSD_RESOURCE_TYPE=7 AND csd.CSD_RESOURCE_ID =2) ); C9_row C9%ROWTYPE; -- This procedure is called from the procedure st_checkEvent procedure. It takes the input as alert_seq_id, eventid,initid, pdnOrderId and customer id. -- The purpose of this procedure is to find all the possible combination of Resource Id and Resource Type for a given Eventid, -- and for all the resourcetypes found, we are going to find the userid and the corresponding email id. Then we would copy -- the record for the input alert_seq_id from CAL_ALERTS table and insert it into the new table CAO_ALERTS_OTHERS. Then we would -- update this record with the new userid and emailid. --For a given eventid, we have to get all the possible resource_id and resources_type. ---The alerts are currently inserted into CAL_ALERTS table on for the Initiator where the --resource_type is 1. ---When the resource_type is anything other than 1 we have to insert the alert into another --table CAO_ALERT_OTHERS with the same alert message but the email_id of either a single --person or the group of people depending upon the resource type. ---We have to insert as many rows into the table as we have members of the resource type BEGIN OPEN all_recipients; LOOP FETCH all_recipients INTO new_resourceid,new_resourcetype; EXIT WHEN all_recipients%NOTFOUND; new_initid := 0; new_emailid := '0'; if new_resourcetype = 2 THEN --2 THEN
if pdnOrderid is not null then OPEN C1; LOOP FETCH C1 INTO C1_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C1%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C1_row.our_id, cal_recipient_email_id=C1_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C2; LOOP FETCH C2 INTO C2_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C2%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C2_row.our_id, cal_recipient_email_id=C2_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 3 THEN if pdnOrderid is not null then OPEN C3; LOOP FETCH C3 INTO C3_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C3%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C3_row.our_id, cal_recipient_email_id=C3_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C4;
our_user_email_id;
LOOP FETCH C4 INTO C4_row;--our_user_id, our_id,
EXIT WHEN C4%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C4_row.our_id, cal_recipient_email_id=C4_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 4 then if pdnOrderid is not null then OPEN C5; LOOP FETCH C5 INTO C5_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C5%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C5_row.our_id, cal_recipient_email_id=C5_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; if crid is not null then OPEN C6; LOOP FETCH C6 INTO C6_row;--our_user_id, our_id, our_user_email_id; EXIT WHEN C6%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C6_row.our_id, cal_recipient_email_id=C6_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; end if; if new_resourcetype = 5 then OPEN C7; LOOP
FETCH C7 INTO C7_row; EXIT WHEN C7%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C7_row.otr_resource_id_team, cal_recipient_email_id=C7_row.otr_recipient where cal_recipient_user_id = initid ; end if; END LOOP; end if; if new_resourcetype = 6 then OPEN C8; LOOP FETCH C8 INTO C8_row; EXIT WHEN C8%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C8_row.CSD_DESCRIPTION, cal_recipient_email_id=C8_row.CSD_RECIPIENT where cal_recipient_user_id = initid ; end if; END LOOP; end if; if new_resourcetype = 7 then OPEN C9; LOOP FETCH C9 INTO C9_row; EXIT WHEN C9%NOTFOUND; select count(*) into record_exists from cao_alerts_others where cal_alert_id = alert_id_seq and cal_recipient_user_id = our_id; if (record_exists = 0) then insert into CAO_ALERTS_OTHERS (select * from cal_alerts where cal_alert_id = alert_id_seq); update CAO_ALERTS_OTHERS set cal_recipient_user_id=C9_row.our_id, cal_recipient_email_id=C9_row.our_user_email_id where cal_recipient_user_id = initid ; end if; END LOOP; end if; END LOOP; CLOSE all_recipients; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END; /
