--B_RPT_ASP_OPSMART_CASES_TBL             PSRPT_CASES_PSFT
--B_RPT_ASP_OPSMART_CONTACT_TBL   		  PSRPT_USER_PSFT
--B_RPT_ASP_OPSMART_CASE_COUNT_TBL        PSRPT_CASES_BY_MONTH_PSFT
--B_RPT_ASP_OPSMART_IPRD_TBL              PSRPT_PSFT
--B_RPT_B_TEAM_ROLE                       PSRPT_TEAM_ROLES_PSFT



-- DROP TABLES ORACLE

SELECT getdate() 'Drop Oracle Tables'

EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_CASES_PSFT' ) AT ZMMS_ORA;
EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_USER_PSFT') AT ZMMS_ORA;
EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_CASES_BY_MONTH_PSFT') AT ZMMS_ORA;
EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_PSFT') AT ZMMS_ORA;
EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_TEAM_ROLES_PSFT') AT ZMMS_ORA;


-- CREATE TABLES ON ORACLE
SELECT getdate() 'Create Tables on Oracle'

EXECUTE (
		'CREATE TABLE "ZMMSUSER"."PSRPT_CASES_PSFT" (
		"COMPANYID" NVARCHAR2(15) NOT NULL,
		"CASE_ID" NUMBER(15, 0) NOT NULL,
		"ROW_ADDED_DTTM" DATE,
		"CLOSED_DATE" DATE,
		"PROVIDER_GRP_ID" NVARCHAR2(100) NULL
)'
		) AT ZMMS_ORA;
EXECUTE (
		'CREATE TABLE "ZMMSUSER"."PSRPT_CASES_BY_MONTH_PSFT" (
		"COMPANYID" NVARCHAR2(15) NOT NULL,
		"PROVIDER_GRP_ID" NVARCHAR2(100)  NULL,
		"TOTAL_CASES" INTEGER,
		"MONTH_CREATED" INTEGER,
		"YEAR_CREATED" INTEGER
)'
		) AT ZMMS_ORA;

EXECUTE (
		'CREATE TABLE "ZMMSUSER"."PSRPT_USER_PSFT" (
		"COMPANYID" NVARCHAR2(15) NOT NULL,
		"OPRID" NVARCHAR2(30) NULL,
		"FIRST_NAME" NVARCHAR2(30) NULL,
		"LAST_NAME" NVARCHAR2(30) NULL,
		"MIDDLE_NAME" NVARCHAR2(30) NULL,
		"TITLE" NVARCHAR2(35) NULL,
		"PERSON_ID" NVARCHAR2(15) NULL,
		"CONTACT_EMAIL" NVARCHAR2(70) NULL,
		"LS_SYS_ADMIN" VARCHAR2(1) NULL,
		"LS_PRIMARY_CONTACT" VARCHAR2(1) NULL,
		"WCT_SYS_ADMIN" VARCHAR2(1) NULL,
		"WCT_PRIMARY_CONTACT" VARCHAR2(1) NULL,
		"PRIMARY_PHONE" NVARCHAR2(35)
)'
		) AT ZMMS_ORA;
EXECUTE (
		'CREATE TABLE "ZMMSUSER"."PSRPT_PSFT" (
		"BO_NAME" NVARCHAR2(50) NOT NULL,
		"COMPANYID" NVARCHAR2(15) NOT NULL,
		"CUST_STATUS" NVARCHAR2(10) NULL,
		"CUSTOMER_TYPE" NVARCHAR2(10) NULL,
		"PRODUCT_ID" NVARCHAR2(18) NULL,
		"DESCR" NVARCHAR2(30) NOT NULL,
		"B_VERSION" NVARCHAR2(15) NULL,
		"B_VERSION_BUILD" NVARCHAR2(15) NULL,
		"ATTRIBUTE_VALUE" NVARCHAR2(254) NULL,
		"INST_PROD_ID" NVARCHAR2(20) NOT NULL,
		"ORDER_DATE" DATE,
		"INSTALLED_DATE" DATE,
		"PLATFORM" NVARCHAR2(3) NULL,
		"OS" NVARCHAR2(4) NULL,
		"OS_VERSION" NVARCHAR2(15) NULL,
		"STATE" NVARCHAR2(6) NULL,
		"INDUSTRY_ID" NVARCHAR2(30) NULL,
		"ICM" NVARCHAR2(17) NULL,
		"LEARN_CLIENT_SEGMENT" NVARCHAR2(30) NULL,
		"INST_PROD_STATUS" NVARCHAR2(4) NULL
)'
		) AT ZMMS_ORA;
	
EXECUTE (
		
		'CREATE TABLE "ZMMSUSER"."PSRPT_TEAM_ROLES_PSFT" (
		"COMPANYID" NVARCHAR2(15) NOT NULL,
		"BO_ID" NUMBER(31, 0) NOT NULL,
	`	"AS CM" NVARCHAR2(61),
		"AS RSM" NVARCHAR2(61),
		"AS SE" NVARCHAR2(61),
		"TS CM" NVARCHAR2(61),
		"TS RAE" NVARCHAR2(61),
		"TS RSM" NVARCHAR2(61),
		"TS SE" NVARCHAR2(61),
		"BBONE AM" NVARCHAR2(61),
		"ASM" NVARCHAR2(61),
		"WCT ASM" NVARCHAR2(61),
		"LS TSM" NVARCHAR2(61),
		"WCT TSM" NVARCHAR2(61),
		"TS TSM" NVARCHAR2(61),
		"AS PM" NVARCHAR2(61),
		"AS DEV" NVARCHAR2(61),
		"TS PM" NVARCHAR2(61),
		"TS DEV" NVARCHAR2(61),
		"TS TECH" NVARCHAR2(61),
		"BB EXEC" NVARCHAR2(61),
		"CHM" NVARCHAR2(61),
		"ANGEL Client Manager (CM)" NVARCHAR2(61),
		"ANGEL Engagement Team" NVARCHAR2(61),
		"BbOne Client Manager (CM)" NVARCHAR2(61),
		"Blackboard Executive" NVARCHAR2(61),
		"CE/Vista Hosting Mgr (ASM)" NVARCHAR2(61),
		"CE/Vista Tech Support (TSM)" NVARCHAR2(61),
		"Complex Hosting Manager (CHM)" NVARCHAR2(61),
		"Learn Client Manager (CM)" NVARCHAR2(61),
		"Learn Hosting Mgr (ASM)" NVARCHAR2(61),
		"Learn Reg Sales Mgr (RSM)" NVARCHAR2(61),
		"Learn Sales Engineer (SE)" NVARCHAR2(61),
		"Learn Services Developer" NVARCHAR2(61),
		"Learn Services ICM Mgr" NVARCHAR2(61),
		"Learn Services Project Manager" NVARCHAR2(61),
		"Learn Tech Support (TSM)" NVARCHAR2(61),
		"Transact Client Manager (CM)" NVARCHAR2(61),
		"Transact Project Manager" NVARCHAR2(61),
		"Transact Reg Acct Exec (RAE)" NVARCHAR2(61),
		"Transact Reg Sales Mgr (RSM)" NVARCHAR2(61),
		"Transact Sales Engineer (SE)" NVARCHAR2(61),
		"Transact Services Technician" NVARCHAR2(61),
		"Transact Tech Support (TSM)" NVARCHAR2(61),
		"Xythos Tech Support (TSM)" NVARCHAR2(61)
)'
		) AT ZMMS_ORA;

-- Export Tables

SELECT getdate() 'Start PSRPT_PSFT export'
INSERT INTO OPENQUERY (
	ZMMS_ORA
	,'SELECT COMPANYID, CASE_ID, ROW_ADDED_DTTM, CLOSED_DATE, PROVIDER_GRP_ID FROM ZMMSUSER.PSRPT_CASES_PSFT'
	)
SELECT COMPANYID
	,CASE_ID
	,ROW_ADDED_DTTM
	,CLOSED_DATE
	,PROVIDER_GRP_ID
FROM CRMRPT..B_RPT_ASP_OPSMART_CASES_TBL

SELECT getdate() 'Start PSRPT_USER_PSFT export'

INSERT INTO OPENQUERY (
	ZMMS_ORA
	,'SELECT COMPANYID, 
		OPRID, 
		FIRST_NAME,
		LAST_NAME,
		MIDDLE_NAME,
		TITLE,
		PERSON_ID,
		CONTACT_EMAIL,
		LS_SYS_ADMIN,
		LS_PRIMARY_CONTACT,
		WCT_SYS_ADMIN,
		WCT_PRIMARY_CONTACT,
		PRIMARY_PHONE FROM ZMMSUSER.PSRPT_USER_PSFT'
	)
SELECT COMPANYID
	,OPRID
	,FIRST_NAME
	,LAST_NAME
	,MIDDLE_NAME
	,TITLE
	,PERSON_ID
	,CONTACT_EMAIL
	,LS_SYS_ADMIN
	,LS_PRIMARY_CONTACT
	,WCT_SYS_ADMIN
	,WCT_PRIMARY_CONTACT
	,PRIMARY_PHONE
FROM CRMRPT..B_RPT_ASP_OPSMART_CONTACT_TBL;

--Start PSRPT_CASES_BY_MONTH_PSFT export
SELECT getdate() 'Start PSRPT_CASES_BY_MONTH_PSFT export'

INSERT INTO OPENQUERY (
	ZMMS_ORA
	,' SELECT COMPANYID,
		PROVIDER_GRP_ID,
		TOTAL_CASES,
		MONTH_CREATED,
		YEAR_CREATED
		FROM ZMMSUSER.PSRPT_CASES_BY_MONTH_PSFT'
	)
SELECT COMPANYID
	,PROVIDER_GRP_ID
	,TOTAL_CASES
	,MONTH_CREATED
	,YEAR_CREATED
FROM CRMRPT..B_RPT_ASP_OPSMART_CASE_COUNT_TBL;

--Start PSRPT_PSFT Export
SELECT getdate() 'Start PSRPT_PSFT export'

INSERT INTO OPENQUERY (
	ZMMS_ORA
	,'SELECT 
		BO_NAME,
		COMPANYID,
		CUST_STATUS,
		CUSTOMER_TYPE,
		PRODUCT_ID,
		DESCR,
		B_VERSION,
		B_VERSION_BUILD,
		ATTRIBUTE_VALUE,
		INST_PROD_ID,
		ORDER_DATE,
		INSTALLED_DATE,
		PLATFORM,
		OS,
		OS_VERSION,
		STATE,
		INDUSTRY_ID,
		ICM,
		LEARN_CLIENT_SEGMENT,
		INST_PROD_STATUS FROM ZMMSUSER.PSRPT_PSFT'
	)
SELECT BO_NAME
	,COMPANYID
	,CUST_STATUS
	,CUSTOMER_TYPE
	,PRODUCT_ID
	,DESCR
	,B_VERSION
	,B_VERSION_BUILD
	,ATTRIBUTE_VALUE
	,INST_PROD_ID
	,ORDER_DATE
	,INSTALLED_DATE
	,PLATFORM
	,OS
	,OS_VERSION
	,STATE
	,INDUSTRY_ID
	,ICM
	,LEARN_CLIENT_SEGMENT
	,INST_PROD_STATUS
FROM CRMRPT..B_RPT_ASP_OPSMART_IPRD_TBL;

--Start PSRPT_TEAM_ROLES_PSFT Export
SELECT getdate() 'Start PSRPT_TEAM_ROLES_PSFT export'

INSERT INTO OPENQUERY (
	ZMMS_ORA 
	'SELECT 
		"COMPANYID",
		"BO_ID",
		"AS CM",
		"AS RSM",
		"AS SE",
		"TS CM",
		"TS RAE",
		"TS RSM",
		"TS SE",
		"BBONE AM",
		"ASM",
		"WCT ASM",
		"LS TSM",
		"WCT TSM",
		"TS TSM",
		"AS PM",
		"AS DEV",
		"TS PM",
		"TS DEV",
		"TS TECH",
		"BB EXEC",
		"CHM",
		"ANGEL Client Manager (CM)",
		"ANGEL Engagement Team",
		"BbOne Client Manager (CM)",
		"Blackboard Executive",
		"CE/Vista Hosting Mgr (ASM)",
		"CE/Vista Tech Support (TSM)",
		"Complex Hosting Manager (CHM)",
		"Learn Client Manager (CM)",
		"Learn Hosting Mgr (ASM)",
		"Learn Reg Sales Mgr (RSM)",
		"Learn Sales Engineer (SE)",
		"Learn Services Developer",
		"Learn Services ICM Mgr",
		"Learn Services Project Manager",
		"Learn Tech Support (TSM)",
		"Transact Client Manager (CM)",
		"Transact Project Manager",
		"Transact Reg Acct Exec (RAE)",
		"Transact Reg Sales Mgr (RSM)",
		"Transact Sales Engineer (SE)",
		"Transact Services Technician",
		"Transact Tech Support (TSM)",
		"Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
	)
SELECT "COMPANYID"
	,"BO_ID"
	,"AS CM"
	,"AS RSM"
	,"AS SE"
	,"TS CM"
	,"TS RAE"
	,"TS RSM"
	,"TS SE"
	,"BBONE AM"
	,"ASM"
	,"WCT ASM"
	,"LS TSM"
	,"WCT TSM"
	,"TS TSM"
	,"AS PM"
	,"AS DEV"
	,"TS PM"
	,"TS DEV"
	,"TS TECH"
	,"BB EXEC"
	,"CHM"
	,"ANGEL Client Manager (CM)"
	,"ANGEL Engagement Team"
	,"BbOne Client Manager (CM)"
	,"Blackboard Executive"
	,"CE/Vista Hosting Mgr (ASM)"
	,"CE/Vista Tech Support (TSM)"
	,"Complex Hosting Manager (CHM)"
	,"Learn Client Manager (CM)"
	,"Learn Hosting Mgr (ASM)"
	,"Learn Reg Sales Mgr (RSM)"
	,"Learn Sales Engineer (SE)"
	,"Learn Services Developer"
	,"Learn Services ICM Mgr"
	,"Learn Services Project Manager"
	,"Learn Tech Support (TSM)"
	,"Transact Client Manager (CM)"
	,"Transact Project Manager"
	,"Transact Reg Acct Exec (RAE)"
	,"Transact Reg Sales Mgr (RSM)"
	,"Transact Sales Engineer (SE)"
	,"Transact Services Technician"
	,"Transact Tech Support (TSM)"
	,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;