CREATE OR REPLACE PACKAGE "DOTSEND_SALES_STAFF_PULL"
as

TYPE rc_rectype IS RECORD
  ( SALESREP_NUM       NUMBER(38)        ,
    BUSINESS_UNIT_ID   NUMBER(38)        ,
    SALESREP_NAME      VARCHAR2(50 CHAR) ,
    PHONE_EXTENSION    VARCHAR2( 8 CHAR) ,
    EMAIL_ADDRESS      VARCHAR2(50 CHAR)
   );

  FUNCTION fn_verify_launch RETURN varchar2;

  procedure pull_sales_staff;

end dotsend_sales_staff_pull;
/


CREATE OR REPLACE PACKAGE BODY "DOTSEND_SALES_STAFF_PULL"
as


FUNCTION fn_verify_launch RETURN varchar2
IS
  verify_msg  VARCHAR2(20);
BEGIN
  verify_msg := 'Verified.';
  RETURN (verify_msg);
END fn_verify_launch;


procedure pull_sales_staff
is

		rc sys_refcursor           ;  /* cursor variable passed to SQLServer stored proc   */
		--type rc_tabtype is table of salesrep_info%rowtype INDEX BY BINARY_INTEGER;
		type rc_tabtype is table of rc_rectype;
		rc_tab rc_tabtype          ;  /* local PLSQL type of ROWTYPE reqd to bulk collect   */
		rc_sql_tab sales_staff_tab ;  /* reqd    SQL type to use in - you guessed it, SQL ! */
		
		gateway_connection_down EXCEPTION;
		PRAGMA EXCEPTION_INIT(gateway_connection_down, -28500);
		err_msg VARCHAR2(100);
		err_num NUMBER;
		mail_msg varchar2(500);
		inserted_sql_count number(12) :=0 ;
		merged_sql_count   number(12) :=0 ;
		table_sql_count    number(12) :=0 ;

        PROCEDURE convert_local_to_sql(srl_local_tab IN rc_tabtype,
                                       srl_sql_tab   IN OUT sales_staff_tab) is
          i number;
        begin
          i := srl_local_tab.FIRST; -- get subscript of first element

          WHILE i IS NOT NULL LOOP
            srl_sql_tab.extend;
            srl_sql_tab(srl_sql_tab.count) := sales_staff(srl_local_tab(i).SALESREP_NUM ,
                                                          srl_local_tab(i).BUSINESS_UNIT_ID,
                                                          srl_local_tab(i).SALESREP_NAME,
                                                          srl_local_tab(i).PHONE_EXTENSION,
                                                          srl_local_tab(i).EMAIL_ADDRESS
                                                         );
             i := srl_local_tab.NEXT(i); -- get subscript of next element
          END LOOP;
        end;

begin

DBMS_OUTPUT.PUT_LINE(' START PROC');


	rc_sql_tab := sales_staff_tab();

    dbo.sp_ob_pull_sales_staff@OB_INITTG4MSQL_DOTSEND1.WORLD(rc);
	
	fetch rc bulk collect into rc_tab;
	close rc;
	rollback;

-- convert_local_to_sql( rc_tab, rc_sql_tab);

-- DBMS_OUTPUT.PUT_LINE(' SQL TO ORACLE CONVERSION COMPLETE');

-- delete from salesrep_info;
-- mail_msg := mail_msg||chr(10)||sql%rowcount||' rows deleted ';

--   insert into salesrep_info
--              (salesrep_num       ,
--               business_unit_id   ,
--               salesrep_name      ,
--               phone_extension    ,
--               email_address
--              )
--       select salesrep_num       ,
--               business_unit_id   ,
--               salesrep_name      ,
--               phone_extension    ,
--               email_address
--         from (select a.*,
--                      row_number() over (partition by salesrep_num, business_unit_id
--                                              order by rownum) as row_number
--                 from table(cast(rc_sql_tab as sales_staff_tab)) a
--                 )
--         where row_number=1;

--         inserted_sql_count:= sql%rowcount;

--   merge into salesrep_info a
--   using ( select * from
--             (select salesrep_num,
--                  707 as business_unit_id,
--                  first_name||CASE WHEN middle_initial IS NOT NULL THEN ' '
--                             ||middle_initial||'.' END||' '||last_name as salesrep_name,
--                  phone_extension,
--                  email_address,
--                  row_number() over(partition by salesrep_num, 707
--                                    order by create_date desc) as most_recent_unique
--             FROM USER_PROFILE
--             where user_id in (select user_id
--                                 from USER_OMS_SYSTEM
--                                WHERE OMS_SYSTEM_ID = 'CDOMS'
--                               )
--              and salesrep_num <> -1
--              )
--              where most_recent_unique = 1
--           ) s
--   on (s.salesrep_num = a.salesrep_num
--   and s.business_unit_id = a.business_unit_id)
--   WHEN MATCHED THEN UPDATE SET a.email_address = s.email_address,
--                                a.salesrep_name = s.salesrep_name,
--                                a.phone_extension = s.phone_extension
--   WHEN NOT MATCHED THEN INSERT
--              (a.salesrep_num       ,
--               a.business_unit_id   ,
--               a.salesrep_name      ,
--               a.phone_extension    ,
--               a.email_address
--              )
--       VALUES (s.salesrep_num       ,
--               s.business_unit_id   ,
--               s.salesrep_name      ,
--               s.phone_extension    ,
--               s.email_address
--              );

--         merged_sql_count:= sql%rowcount;
--       select count(*) into table_sql_count from salesrep_info;

--        mail_msg := mail_msg||', '||table_sql_count||' rows inserted.   '||
--                    '('|| table_sql_count||' inserted from dotsend.  '||merged_sql_count||' merged from ob user_profile.)';

-- commit;
-- mail_msg := 'salesrep_info table successfully loaded  @'||to_char(sysdate,'mm/dd/yyyy:hh24:mi')||' '||mail_msg;
-- ob_smtp_mail.mail(sender => 'oracle',
--                  --recipients => 'james_henderson@dell.com, john_ip@dell.com',
--          recipients => ob_global_context_pkg.get_informational_recipients(),
--          subject    => UPPER(SYS_CONTEXT ('USERENV', 'DB_NAME'))||' : Salesrep_Info Load',
--                  message    => mail_msg);

exception
when others then
  rollback;
if rc%isopen then
  close rc;
end if;


raise;

end pull_sales_staff;

end dotsend_sales_staff_pull;
/