<commit_before>-- Procesos y parametros asociados
Select 
    pd.name as proc_name, pd.version as proc_ver, pp.name as param_name, pp.value as param_value
from 
    BPM.PROC_PARAMETER pp
left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos';

-- Generamos las selects a partir del resultado anterior
SELECT 
 'UPDATE BPM_DES.PROC_PARAMETER SET VALUE = ''2.0'' WHERE ID = ' || pp.id || ';'
FROM BPM.PROC_PARAMETER pp
left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos';


-- Job para actualizar el valor del parametro de una lista de procesos y parametros
BEGIN
  FOR r IN 
  (
    Select 
        pd.name as proc_name, pd.version as proc_ver, pp.name as param_name, pp.value as param_value
    from 
        BPM.PROC_PARAMETER pp
    left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
    where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
    OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
    OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
    OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
    OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
    OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
    OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
  )
  LOOP
    EXECUTE IMMEDIATE  'UPDATE BPM_DES.PROC_PARAMETER SET VALUE = ''2.0'' WHERE ID = ' || pp.id;     
  END LOOP;
END;






-- Buscar aquello proceso que han sido instalados posteriormente a una fecha

SELECT 
TENANTID, ID, PROCESSID, 
   NAME, VERSION, DESCRIPTION, 
   DEPLOYMENTDATE, DEPLOYEDBY, ACTIVATIONSTATE, 
   CONFIGURATIONSTATE, DISPLAYNAME, DISPLAYDESCRIPTION, 
   LASTUPDATEDATE, CATEGORYID, ICONPATH, 
   CONTENT_TENANTID, CONTENT_ID
FROM BPM.PROCESS_DEFINITION
-- donde la fecha de despliegue DEPLOYMENTDATE sea mayor que la fecha dada
WHERE ((select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) > TO_DATE('150219', 'DDMMYY'));


-- MAS LEGIBLE
SELECT 
   NAME, VERSION, (select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) AS DEPLOYMENTDATE, ACTIVATIONSTATE, 
   CONFIGURATIONSTATE, DISPLAYNAME
FROM BPM.PROCESS_DEFINITION
WHERE ((select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) > TO_DATE('150219', 'DDMMYY'));





-- INFORMACION CRUZADA DE LOS PROCESOS CON LAS ORDENES DE TRABAJO, INCIDENCIAS...
select pi.name as process_name, pi.id as process_instance, fi.name as tak_name, PI.id, fi.statename, fi.displayname as task_displayname, 
fi.kind  ,FI.LASTUPDATEDATE,  ord.start_date, ord.provisioning_id, ord.customer_id, ord.customer_name, ord.service_name, sda.value as sales_reference, sda1.value as service_name2,  
sda2.value as service_action,
INCT.INCIDENT_ID, 
WO.SERVICE_REQUEST_ID,
ticket.status
from BPM.PROCESS_INSTANCE pi 
left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID 
-- EN ESTADO DE FALLO, LISTO, EJECUTANDOSE O ESPERANDO
--AND fi.statename IN ('failed', 'ready', 'executing', 'waiting')
-- EN ESPERA POR UN EVENTO
--left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID AND fi.statename IN ('waiting') AND fi.kind IN ('intermediateCatchEvent')
left join CRM_MEDIATOR.BPMPROCESS_CALL pc on  pi.id = pc.PROCESS_INSTANCE_ID
left join CRM_MEDIATOR.ACTION_PRC_CALL apc on pc.ID = apc.PROCESS_CALL_ID
left join CRM_MEDIATOR.PROVISION_ACTION act on act.id = apc.ACTION_ID
left join CRM_MEDIATOR.PROVISION_ORDER ord on ord.id = act.PROVISION_ORDER_ID 
left join CRM_MEDIATOR.SERVICE_DESCRIPTION sd on sd.ID = ord.SERVICE_DESC_ID
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda on sd.id = sda.SERVICE_DESC_ID and sda.name = 'sales.reference'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda1 on sd.id = sda1.SERVICE_DESC_ID and sda1.name = 'service.name'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda2 on sd.id = sda2.SERVICE_DESC_ID and sda2.name = 'worksheet.action'
left join ITSM_MEDIATOR.INCIDENT inct on INCT.PROVISIONING_ID = ORD.PROVISIONING_ID
left join ITSM_MEDIATOR.WORK_ORDER wo on wo.PROVISIONING_ID = ORD.PROVISIONING_ID
left join TICKET@ITSM_DB ticket on (ticket.ticketid = INCT.INCIDENT_ID or  ticket.ticketid = WO.SERVICE_REQUEST_ID) AND ticket.EXTERNALSYSTEM = 'PROVISIONING' 
--where pi.name like '%POTS%'
where pi.name = 'Alta POTS' 
--and  sda2.value ='CHANGE' 
and  sda2.value ='NEW'
--AND pi.id = '44338'
order by pi.name asc, process_instance asc, FI.LASTUPDATEDATE asc, pi.STARTDATE asc, ord.customer_id;<commit_msg>borradores<commit_after>-- worksheets, sales references, id de caso en bonita
select feasibility_case_id, worksheet_code_number, sales_reference from provision.worksheet 
where WORKSHEET_CODE_NUMBER = '12619/DVE/BPAG/2019'
or WORKSHEET_CODE_NUMBER = '12620/DVE/BPAG/2019'
or WORKSHEET_CODE_NUMBER = '12621/DVE/BPAG/2019'
or WORKSHEET_CODE_NUMBER = '12622/DVE/BPAG/2019'
or WORKSHEET_CODE_NUMBER = '12623/DVE/BPAG/2019'
or WORKSHEET_CODE_NUMBER = '12624/DVE/BPAG/2019'

or WORKSHEET_CODE_NUMBER = '12385/DVE/BPAG/2019'






-- Procesos y parametros asociados
Select 
    pd.name as proc_name, pd.version as proc_ver, pp.name as param_name, pp.value as param_value
from 
    BPM.PROC_PARAMETER pp
left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos';

-- Generamos las selects a partir del resultado anterior
SELECT 
 'UPDATE BPM_DES.PROC_PARAMETER SET VALUE = ''2.0'' WHERE ID = ' || pp.id || ';'
FROM BPM.PROC_PARAMETER pp
left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos';


-- Job para actualizar el valor del parametro de una lista de procesos y parametros
BEGIN
  FOR r IN 
  (
    Select 
        pd.name as proc_name, pd.version as proc_ver, pp.name as param_name, pp.value as param_value
    from 
        BPM.PROC_PARAMETER pp
    left join BPM.PROCESS_DEFINITION pd on  pd.PROCESSID = pp.PROCESS_ID 
    where pd.name = 'Mudança producto LTE' AND pd.version = '1.0' AND pp.name = 'v_altaElementosRed'
    OR pd.name = 'Mudança producto LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
    OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vAltaElementosRedLTE'
    OR pd.name = 'Mudança numero telefone LTE' AND pd.version = '1.0'  AND pp.name = 'vBajaElementosRedLTE'
    OR pd.name = 'Alta LTE' AND pd.version = '1.0'  AND pp.name = 'v_altaElementosRed'
    OR pd.name = 'Cancelar provisión LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
    OR pd.name = 'Baja LTE' AND pd.version = '1.0'  AND pp.name = 'v_bajaRecursos'
  )
  LOOP
    EXECUTE IMMEDIATE  'UPDATE BPM_DES.PROC_PARAMETER SET VALUE = ''2.0'' WHERE ID = ' || pp.id;     
  END LOOP;
END;






-- Buscar aquello proceso que han sido instalados posteriormente a una fecha

SELECT 
TENANTID, ID, PROCESSID, 
   NAME, VERSION, DESCRIPTION, 
   DEPLOYMENTDATE, DEPLOYEDBY, ACTIVATIONSTATE, 
   CONFIGURATIONSTATE, DISPLAYNAME, DISPLAYDESCRIPTION, 
   LASTUPDATEDATE, CATEGORYID, ICONPATH, 
   CONTENT_TENANTID, CONTENT_ID
FROM BPM.PROCESS_DEFINITION
-- donde la fecha de despliegue DEPLOYMENTDATE sea mayor que la fecha dada
WHERE ((select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) > TO_DATE('150219', 'DDMMYY'));


-- MAS LEGIBLE
SELECT 
   NAME, VERSION, (select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) AS DEPLOYMENTDATE, ACTIVATIONSTATE, 
   CONFIGURATIONSTATE, DISPLAYNAME
FROM BPM.PROCESS_DEFINITION
WHERE ((select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * DEPLOYMENTDATE from dual) > TO_DATE('150219', 'DDMMYY'));


hay que logarse como system para poder cruzar las tablas


-- INFORMACION CRUZADA DE LOS PROCESOS CON LAS ORDENES DE TRABAJO, INCIDENCIAS...
select pi.name as process_name, pi.id as process_instance, fi.name as tak_name, PI.id, fi.statename, fi.displayname as task_displayname, 
fi.kind  ,FI.LASTUPDATEDATE,  ord.start_date, ord.provisioning_id, ord.customer_id, ord.customer_name, ord.service_name, sda.value as sales_reference, sda1.value as service_name2,  
sda2.value as service_action,
INCT.INCIDENT_ID, 
WO.SERVICE_REQUEST_ID,
ticket.status
from BPM.PROCESS_INSTANCE pi 
left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID 
-- EN ESTADO DE FALLO, LISTO, EJECUTANDOSE O ESPERANDO
--AND fi.statename IN ('failed', 'ready', 'executing', 'waiting')
-- EN ESPERA POR UN EVENTO
--left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID AND fi.statename IN ('waiting') AND fi.kind IN ('intermediateCatchEvent')
left join CRM_MEDIATOR.BPMPROCESS_CALL pc on  pi.id = pc.PROCESS_INSTANCE_ID
left join CRM_MEDIATOR.ACTION_PRC_CALL apc on pc.ID = apc.PROCESS_CALL_ID
left join CRM_MEDIATOR.PROVISION_ACTION act on act.id = apc.ACTION_ID
left join CRM_MEDIATOR.PROVISION_ORDER ord on ord.id = act.PROVISION_ORDER_ID 
left join CRM_MEDIATOR.SERVICE_DESCRIPTION sd on sd.ID = ord.SERVICE_DESC_ID
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda on sd.id = sda.SERVICE_DESC_ID and sda.name = 'sales.reference'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda1 on sd.id = sda1.SERVICE_DESC_ID and sda1.name = 'service.name'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda2 on sd.id = sda2.SERVICE_DESC_ID and sda2.name = 'worksheet.action'
left join ITSM_MEDIATOR.INCIDENT inct on INCT.PROVISIONING_ID = ORD.PROVISIONING_ID
left join ITSM_MEDIATOR.WORK_ORDER wo on wo.PROVISIONING_ID = ORD.PROVISIONING_ID
left join TICKET@ITSM_DB ticket on (ticket.ticketid = INCT.INCIDENT_ID or  ticket.ticketid = WO.SERVICE_REQUEST_ID) AND ticket.EXTERNALSYSTEM = 'PROVISIONING' 
--where pi.name like '%POTS%'
where pi.name = '<NAME>' 
--and  sda2.value ='CHANGE' 
and  sda2.value ='NEW'
--AND pi.id = '44338'
order by pi.name asc, process_instance asc, FI.LASTUPDATEDATE asc, pi.STARTDATE asc, ord.customer_id;





























-- INFORMACION CRUZADA DE LOS PROCESOS CON LAS ORDENES DE TRABAJO, INCIDENCIAS...
select pi.name as process_name, pi.id as process_instance, fi.name as tak_name, PI.id, fi.statename, fi.displayname as task_displayname, 
fi.kind  ,FI.LASTUPDATEDATE,  ord.start_date, ord.provisioning_id, ord.customer_id, ord.customer_name, ord.service_name, sda.value as sales_reference, sda1.value as service_name2,  
sda2.value as service_action, sda3.value as folha_obra,
INCT.INCIDENT_ID, 
WO.SERVICE_REQUEST_ID,
ticket.status
from BPM.PROCESS_INSTANCE pi 
left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID 
-- EN ESTADO DE FALLO, LISTO, EJECUTANDOSE O ESPERANDO
--AND fi.statename IN ('failed', 'ready', 'executing', 'waiting')
-- EN ESPERA POR UN EVENTO
--left join BPM.FLOWNODE_INSTANCE fi on  pi.id = fi.ROOTCONTAINERID AND fi.statename IN ('waiting') AND fi.kind IN ('intermediateCatchEvent')
left join CRM_MEDIATOR.BPMPROCESS_CALL pc on  pi.id = pc.PROCESS_INSTANCE_ID
left join CRM_MEDIATOR.ACTION_PRC_CALL apc on pc.ID = apc.PROCESS_CALL_ID
left join CRM_MEDIATOR.PROVISION_ACTION act on act.id = apc.ACTION_ID
left join CRM_MEDIATOR.PROVISION_ORDER ord on ord.id = act.PROVISION_ORDER_ID 
left join CRM_MEDIATOR.SERVICE_DESCRIPTION sd on sd.ID = ord.SERVICE_DESC_ID
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda on sd.id = sda.SERVICE_DESC_ID and sda.name = 'sales.reference'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda1 on sd.id = sda1.SERVICE_DESC_ID and sda1.name = 'service.name'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda2 on sd.id = sda2.SERVICE_DESC_ID and sda2.name = 'worksheet.action'
left join CRM_MEDIATOR.SERVICE_ATTRIBUTES sda3 on sd.id = sda3.SERVICE_DESC_ID and sda3.name = 'worksheet.number'

left join ITSM_MEDIATOR.INCIDENT inct on INCT.PROVISIONING_ID = ORD.PROVISIONING_ID
left join ITSM_MEDIATOR.WORK_ORDER wo on wo.PROVISIONING_ID = ORD.PROVISIONING_ID
left join TICKET@ITSM_DB ticket on (ticket.ticketid = INCT.INCIDENT_ID or  ticket.ticketid = WO.SERVICE_REQUEST_ID) AND ticket.EXTERNALSYSTEM = 'PROVISIONING' 
--where pi.name like '%POTS%'
--where sda.value = 'SO48532' or 
where sda3.value = '12614/DVG/ZAPA/2019'
order by pi.name asc, process_instance asc, FI.LASTUPDATEDATE asc, pi.STARTDATE asc, ord.customer_id;
{'fraction_non_alphanumeric': 0.10211997592456364, 'fraction_numerical': 0.023607302882364744, 'mean_word_length': 4.254392129304287, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5830804', 'n_tokens_mistral': 6103, 'n_tokens_neox': 5802, 'n_words': 1550}