Notes

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('ITHODE','FAX', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('EFT','FAX', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('MHV','FAX', 790 , 1050, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('VERGI','FAX', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('SGK','FAX', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('YPGON','FAX', 123 , 180, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('ITHODE','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('EFT','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('MHV','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('VERGI','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('SGK','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('YPGON','SCANNER', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('ITHODE','EMAIL', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('EFT','EMAIL', 700 , 1000, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('MHV','EMAIL', 73 , 123, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('VERGI','EMAIL', 60 , 80, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('SGK','EMAIL', 70 , 100, to_date('20170726','YYYYMMDD'));

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('YPGON','EMAIL', 400 , 800, to_date('20170726','YYYYMMDD'));

commit

select TP.ID, RD.DOCUMENT_ID, UD.FIELD_VALUE, TP.CODE, IC.FINAL_RESULT, IC.CLASSIFICATION_DATE, IC.SAFIR_ENABLED
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD,
ICAN.ICAN_DOCUMENT_RESULTS IC
where TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
AND UD.FIELD_VALUE = IC.DOCUMENT_UID

SELECT * FROM ICAN.ICAN_DOCUMENT_RESULTS IC


DROP TABLE TEST_ICAN_STATS_T

CREATE TABLE TEST_ICAN_STATS_T
(
ID NUMBER(16) NOT NULL,
TYPE VARCHAR2(20 BYTE),
CHANNEL VARCHAR2(20 BYTE),
SUCCESS NUMBER(12),
TOTAL NUMBER(12),
CLASSIFICATION_DATE DATE
)

ALTER TABLE TEST_ICAN_STATS_T ADD(
CONSTRAINT stats_pk PRIMARY KEY(ID));

CREATE SEQUENCE stats_seq START WITH 1;

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON TEST_ICAN_STATS_T
FOR EACH ROW
BEGIN
SELECT stats_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

commit

select * from TEST_ICAN_STATS_T
where CLASSIFICATION_DATE = to_date('20170725','YYYYMMDD')

INSERT INTO TEST_ICAN_STATS_T (TYPE, CHANNEL, SUCCESS, TOTAL, CLASSIFICATION_DATE)
VALUES ('MKK','FAX', 700 , 1000, to_date('20170725','YYYYMMDD'));

select * from ICAN_BFO_ORGANIZATIONS

WHERE PRODUCT = 'CEPTEL' AND CORPORATION = 'AVEA' AND CORPORATION_NAME = 'AVEA' AND CITY = '<HEPSI>';




Update ICAN_BFO_ORGANIZATIONS
SET IS_FREQUENT =0
WHERE 1=1

commit

UPDATE ICAN_BFO_ORGANIZATIONS SET IS_FREQUENT = 4250 WHERE PRODUCT = 'SU' AND CORPORATION = 'ASKI' AND CORPORATION_NAME = 'ANKARA SU (ASKİ)' AND CITY = 'ANKARA';

COMMIT



Select TP.*
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD
where 1=1 --TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 18:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
--AND ICANDOCS.SAFIR_ENABLED = 1

SELECT * from ICAN.ICAN_DOCUMENT_RESULTS
WHERE CLASSIFICATION_DATE >= TO_DATE ('30072017 00:00:00', 'ddmmyyyy HH24:mi:ss')

select TP.ID, RD.DOCUMENT_ID, UD.FIELD_VALUE, TP.CODE, IC.FINAL_RESULT, IC.CLASSIFICATION_DATE, IC.SAFIR_ENABLED
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD,
ICAN.ICAN_DOCUMENT_RESULTS IC
where TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
AND UD.FIELD_VALUE = IC.DOCUMENT_UID

select IC.SAFIR_ENABLED, COUNT(IC.SAFIR_ENABLED)
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD,
ICAN.ICAN_DOCUMENT_RESULTS IC
where TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
AND UD.FIELD_VALUE = IC.DOCUMENT_UID
group by IC.SAFIR_ENABLED



WITH Sales_CTE (ID,DOCUMENT_ID, FIELD_VALUE, CODE, FINAL_RESULT, CLASSIFICATION_DATE, SAFIR_ENABLED )
AS
-- Define the CTE query.
(
select TP.ID, RD.DOCUMENT_ID, UD.FIELD_VALUE, TP.CODE, IC.FINAL_RESULT, IC.CLASSIFICATION_DATE, IC.SAFIR_ENABLED
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD,
ICAN.ICAN_DOCUMENT_RESULTS IC
where TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
AND UD.FIELD_VALUE = IC.DOCUMENT_UID
)
SELECT * FROM
(select count(*) as total from Sales_CTE ),
(Select count(*) as ENABLED from Sales_CTE where SAFIR_ENABLED =1 ),
(Select count(*) as Success from Sales_CTE where FINAL_RESULT='SGKPYM')


select count(*) as total from Sales_CTE
Select count(*) as ENABLED from SALES_CTE where SAFIR_ENABLED =1



select TP.ID, RD.DOCUMENT_ID, UD.FIELD_VALUE, TP.CODE, IC.FINAL_RESULT, IC.CLASSIFICATION_DATE, IC.SAFIR_ENABLED
from
GGATE.WHS_PROCESSES TP,
GGATE.CPS_PROCESS_INSTANCES PI,
GGATE.CPS_PROCESS_INS_DOCUMENTS PD,
GGATE.CPS_REMOTE_DOCUMENTS RD,
GGATE.CHN_UNPROCESSED_DATA_ARCHIVE UD,
ICAN.ICAN_DOCUMENT_RESULTS IC
where TP.CODE = 'SGKODE'
AND TP.CREATE_DATE >= TO_DATE ('05072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND TP.CREATE_DATE < TO_DATE ('06072017 00:00:00', 'ddmmyyyy HH24:mi:ss')
AND PI.CHANNEL_TYPE = 'FAX'
AND TP.ID = PI.UNIQUE_PROCESS_ID
AND PD.UNIQUE_PROCESS_ID = PI.UNIQUE_PROCESS_ID
AND PD.REMOTE_DOCUMENT_ID = RD.ID
AND PI.CHANNEL_DATA_ID = UD.REF_ID
AND UD.FIELD_ID = 'UNIQUE_ID'
AND UD.FIELD_VALUE = IC.DOCUMENT_UID