Untitled

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;