Untitled
unknown
plain_text
7 months ago
8.0 kB
1
Indexable
Never
select * from preaffiliation.preaffiliation_person; select * from product.plan; SELECT pla.id venta, pla.sale_date fecha_venta, pla.start_contract_date inicio_contrato, (SELECT NAME FROM optimus2.product_type WHERE ID = pla.product_type_id) producto, (SELECT NAME FROM optimus2.sub_product_type WHERE ID = pla.sub_product_type_id) tipo_producto, pla.number_of_workers cantidad_trabajadores, pla.total_population poblacion_empresa, decode(pla.waiting_period_applies, 0, 'NO', 'SI') periodo_espera, (SELECT DESCRIPTION FROM preaffiliation.previous_insurer WHERE ID = pla.previous_insurer_id) asegurador_anterior, (SELECT DESCRIPTION FROM preaffiliation.healtcare_system WHERE ID = healtcare_system_id) sistema_salud_ant, pla.observation observacion_venta, decode(pla.bag_measurement, 0, 'NO', 'SI') medicion_bolsa, decode(pla.different_plan_standard, 1, 'NO', 'SI') plan_estandar, (SELECT DECODE(person_type, 'J', NVL(phantasy_name, NAME), names || ' ' || first_surname) FROM optimus2.broker WHERE ID = pla.broker_id) vendedor, (SELECT names || ' ' || first_surname FROM preaffiliation.contact WHERE ID = contact_representative_id) representante, (SELECT names || ' ' || first_surname FROM preaffiliation.contact WHERE ID = contact_billing_id) facturador, (SELECT DECODE(person_type, 'J', NVL(bussiness_name, corporate_name), names || ' ' || first_surname) FROM preaffiliation.client WHERE ID = pla.client_id) cliente, pla.creation_date fecha_creacion, (SELECT LOGIN FROM optimus2.user_profile WHERE user_id = pla.creation_user_id) usuario_creacion, decode(pla.document_dispatch, 0, 'NO', 'SI') despacho_agrupado, (pla.electronic_certificate) partida_eletronica, (SELECT NAME FROM preaffiliation.company_group WHERE ID = pla.commercial_group) grupo_empresarial, pla.contract_number contrato_registrado, est.name estado_venta, --------- pre.id numero_solicitud, pre.contract_reference_id contrato_referencia, pre.creation_date fecha_solicitud, (SELECT LOGIN FROM optimus2.user_profile WHERE user_id = pre.creation_user_id) usuario_solicitud, round(pre.monthly_quota, 2) monto_mensual, round(pre.first_amount, 2) monto_periodo, round(pre.discount, 2) descuento_periodo, round(pre.FIRST_AMOUNT_IGV, 2) impuesto_periodo, round((pre.first_amount - pre.discount + pre.FIRST_AMOUNT_IGV), 2) monto_total, pre.start_validity inicio_vigencia, (SELECT NAME FROM preaffiliation.preaffiliation_type WHERE ID = pre.preaffiliation_type_id) tipo_afiliacion, (SELECT NAME FROM product.plan WHERE ID = pre.plan_id) PLAN, (SELECT NAME FROM optimus2.payment_periodicity WHERE ID = pre.periodicity_id) periodicidad, (SELECT decode(is_standard, 1, 'SI', 'NO') FROM product.plan WHERE ID = pre.plan_id) es_estandar, pre.status_declaration estado_declaracion, est2.name estado_solicitud, pre.family familia, (SELECT NAME FROM optimus2.identity_document_TYPE WHERE ID = prp.identity_document_type_id) tipo_documento, --- prp.id, prp.identity_document numero_documento, prp.names nombre, prp.first_surname apellido_paterno, prp.second_surname apellido_materno, prp.MARRIED_NAME apellido_casada, to_date(prp.birth_date, 'DD/MM/RRRR') fecha_nacimiento, (SELECT NAME FROM OPTIMUS2.MARITAL_STATUS WHERE ID = prp.marital_status_id) estado_civil, prp.gender genero, prp.phone telefono, prp.cellphone celular, lower(prp.email) email, (SELECT dd.NAME FROM optimus2.district dd WHERE ID = prp.district_id) distrito, (SELECT pp.NAME FROM optimus2.state pp WHERE pp.id = (SELECT dd.state_id FROM optimus2.district dd WHERE ID = prp.district_id)) provincia, (SELECT DE.NAME FROM optimus2.department DE WHERE DE.ID = (SELECT pp.department_id FROM optimus2.state pp WHERE pp.id = (SELECT dd.state_id FROM optimus2.district dd WHERE DD.ID = prp.district_id))) departamento, (SELECT z.shortname FROM optimus2.street_type z WHERE z.id = prp.street_type_id) tipo_via, prp.address direccion, prp.address_number numero_direccion, decode(pre.niubiz_payment_done, 1, 'PAGADO', 'SIN PAGO') estado_pago, (SELECT ID FROM optimus2.niubiz_result WHERE ID = pre.niubiz_payment_id AND status = 1) pago_id, (SELECT NAME FROM optimus2.relationship WHERE ID = prd.relationship_id) parentesco, round(prd.gross_amount, 2) monto_afiliado, round(prd.net_amount, 2) total_afiliado FROM preaffiliation.placement pla INNER JOIN preaffiliation.preaffiliation pre ON (pla.id = pre.placement_id) INNER JOIN preaffiliation.preaffiliation_declaration prd ON (prd.preaffiliation_id = pre.id) INNER JOIN preaffiliation.preaffiliation_person prp ON (prd.preaffiliation_person_id = prp.id) inner join (SELECT ps.name, pt.placement_id, pt.commentary observacion_venta FROM preaffiliation.placement_status ps INNER JOIN preaffiliation.placement_tracking pt ON ps.id = pt.placement_status_id WHERE pt.id IN (SELECT MAX(zz.id) FROM preaffiliation.placement_tracking zz GROUP BY zz.PLACEMENT_ID)) est on est.placement_id = pla.id INNER JOIN (SELECT ps.name, pt.preaffiliation_id, pt.commentary observacion_solicitud FROM preaffiliation.preaffiliation_status ps INNER JOIN preaffiliation.preaffiliation_tracking pt ON ps.id = pt.preaffiliation_status_id WHERE pt.id IN (SELECT MAX(zz.id) FROM preaffiliation.preaffiliation_tracking zz GROUP BY zz.Preaffiliation_ID)) est2 on est2.preaffiliation_id = pre.id ORDER BY pla.id DESC;