Untitled

-----------------------------VALIDACION SCTR-------------------------------------

-- 2 contratos salud y pension

SELECT 'CONTRATO', C.*,
        'MOVIMIENTO',CM.*,
        'MEMBER_MOV',MM.*,
        'MEMBER',M.*
 FROM OPTIMUS2.CONTRACT C LEFT JOIN
        OPTIMUS2.CONTRACT_MOVEMENT CM ON (C.ID = CM.CONTRACT_ID) inner JOIN
        OPTIMUS2.MEMBER_MOVEMENT MM ON (MM.CONTRACT_MOVEMENT_ID = CM.ID) inner JOIN
        OPTIMUS2.MEMBER M ON (MM.MEMBER_CODE = M.CODE AND M.STATUS = 'V')
 WHERE C.CONTRACT_GROUP_NUMBER IN ( 'GPO-SCTR-000369223') and c.STATUS = 'V' and M.STATUS = 'V' and cm.MOVEMENT_TYPE  = 'E'
 ORDER BY cm.id DESC;

-- 1 contratos salud

SELECT 'CONTRATO', C.*,        'MOVIMIENTO',CM.*,        'MEMBER_MOV',MM.*,        'MEMBER',M.*
        FROM OPTIMUS2.CONTRACT C LEFT JOIN
        OPTIMUS2.CONTRACT_MOVEMENT CM ON (C.ID = CM.CONTRACT_ID) inner JOIN
        OPTIMUS2.MEMBER_MOVEMENT MM ON (MM.CONTRACT_MOVEMENT_ID = CM.ID) inner JOIN
        OPTIMUS2.MEMBER M ON (MM.MEMBER_CODE = M.CODE AND M.STATUS = 'V')
 WHERE C.CONTRACT_NUMBER = 498287 and c.STATUS = 'V' and M.STATUS = 'V' and cm.MOVEMENT_TYPE  = 'E'
 ORDER BY cm.id DESC;

--498287 contrato
--43221121 identity document member
--10415714632 identity document client

-- 1 contratos pension

SELECT 'CONTRATO', C.*,        'MOVIMIENTO',CM.*,        'MEMBER_MOV',MM.*,        'MEMBER',M.*
        FROM OPTIMUS2.CONTRACT C LEFT JOIN
        OPTIMUS2.CONTRACT_MOVEMENT CM ON (C.ID = CM.CONTRACT_ID) inner JOIN
        OPTIMUS2.MEMBER_MOVEMENT MM ON (MM.CONTRACT_MOVEMENT_ID = CM.ID) inner JOIN
        OPTIMUS2.MEMBER M ON (MM.MEMBER_CODE = M.CODE AND M.STATUS = 'V')
 WHERE C.CONTRACT_NUMBER = 498311 and c.STATUS = 'V' and M.STATUS = 'V' and cm.MOVEMENT_TYPE  = 'E'
 ORDER BY cm.id DESC;

--9000101363 contrato
--43457887 identity document member
--10415714632 identity document client

------------------------------------------------ Traslado-----------------------

--select * from CLIENT_BROKER

select * from CLIENT
where IDENTITY_DOCUMENT = '';
select * from BROKER
where IDENTITY_DOCUMENT = '';

insert into CLIENT_BROKER(client_id, broker_id, SUB_PRODUCT_TYPE_ID,PAYMENT_POLITIC)
values()

select * from CLIENT_BROKER
order by 1 desc

update PRODUCT.PLAN set SUB_PRODUCT_TYPE_ID=8
where ID=56640;

update PRODUCT.plan set SUB_PRODUCT_TYPE_ID=10,PRODUCT_TYPE_ID=5
where ID=56638;

--38073
select  * from PREAFFILIATION.PLACEMENT
where id = 20508

update PREAFFILIATION.PLACEMENT
set COMMERCIAL_GROUP =42023
where id = 20509


select * from PREAFFILIATION.PREAFFILIATION_TRACKING
WHERE ID = 2058633

select * from PREAFFILIATION.PREAFFILIATION_TRACKING
order by 1 desc

select * from SUB_PRODUCT_TYPE


select * from CLIENT_BRANCH_OFFICE
select * from  IDENTITY_DOCUMENT_TYPE
select * from  econo

select * from contract
where CONTRACT_NUMBER  ='9000101363';

select * from CONTRACT_MOVEMENT
where CONTRACT_ID =2971983



SELECT CL.ECONOMIC_ACTIVITY_ID ID,
             EA.NAME,
             EA.SELFMANAGEMENT,
             EA.STATUS,
             EA.CODE
        FROM CLIENT CL, ECONOMIC_ACTIVITY EA
       WHERE CL.ID = 464716
         AND CL.ECONOMIC_ACTIVITY_ID = EA.ID;

select EA.* from CLIENT cl
inner join  ECONOMIC_ACTIVITY EA on CL.ECONOMIC_ACTIVITY_ID = EA.ID
where CL.ID = 464716


select * from SUB_PRODUCT_TYPE
order by  1 desc


--6,SCTR Pensión (Sanitas - Crecer)
--5,SCTR Pensión (Sanitas - Protecta)

--7,SCTR Salud (Sanitas - Crecer)
--4,SCTR Salud (Sanitas - Protecta)


SELECT cm.id,c.SUB_PRODUCT_TYPE_ID, cm.movement_type, mm.*,m.* FROM optimus2.contract c INNER JOIN
        optimus2.contract_movement cm ON c.id = cm.contract_id INNER JOIN
        optimus2.member_movement mm ON mm.contract_movement_id = cm.id INNER JOIN
        optimus2.member m ON m.code = mm.member_code AND m.status = 'V' --and c.status = 'V'
WHERE --c.sub_product_type_id IN (8,9,10)  and mm.family_number = 1
c.contract_number = '612037326';


select * from contract
where contract_number = '498314';

select * from CLIENT
    where id = 464734
----------------------------------------------
select * from "OPTIMUS2".VALIDATIONS_SCTR_LOG

CREATE TABLE "OPTIMUS2"."VALIDATIONS_SCTR_LOG"
(
   "ID" NUMBER NOT NULL ENABLE PRIMARY KEY,
   "IDENTITY_DOCUMENT_CLIENT" NVARCHAR2(100),
   "CONTRACT_NUMBER" NVARCHAR2(100),
   "IDENTITY_DOCUMENT_MEMBER" NVARCHAR2(100),
   "CREATION_DATE" DATE DEFAULT SYSDATE
);

CREATE SEQUENCE  "OPTIMUS2"."SEQ_VALIDATIONS_SCTR_LOG"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

create or replace TRIGGER "OPTIMUS2"."TR_RETENTION_EVALUATION_LOG"
BEFORE INSERT ON OPTIMUS2.RETENTION_EVALUATION_LOG
FOR EACH ROW
BEGIN
  SELECT SEQ_VALIDATIONS_SCTR_LOG.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

select * from AFFILIATION_NOTIFICATION

select * from AFF_NOTIFICATION_DOCUMENT
WHERE AFFILIATION_NOTIFICATION_ID = 1857

select * from

select * from user_
where IDENTITY_DOCUMENT = '498321'

select * from client
WHERE id = 464740


SELECT cm.id,c.SUB_PRODUCT_TYPE_ID, cm.movement_type, mm.*,m.* FROM optimus2.contract c INNER JOIN
        optimus2.contract_movement cm ON c.id = cm.contract_id INNER JOIN
        optimus2.member_movement mm ON mm.contract_movement_id = cm.id INNER JOIN
        optimus2.member m ON m.code = mm.member_code AND m.status = 'V' and c.status = 'V'
WHERE --c.sub_product_type_id IN (8,9,10)  and mm.family_number = 1
c.contract_number = '498321'


select * from CONTRACT
where id = 498321

select * from CONTRACT_MOVEMENT
where CONTRACT_id = 498321

20607395871
498321
74609605