Cláusulas START WITH y CONNECT BY PRIOR

Cuando nos encontramos ante una tabla en la que los datos se encadenan siguiendo una estructura jerárquica (es decir, existen registros padre y registros hijo), puede llegar a ser necesario recuperar los datos de forma recursiva, mostrando la estructura jerárquica o la relación existente entre unos datos y otros.

Si todavía no tenéis claro de que estoy hablando, sólo tenéis que mirar a la estructura tipo árbol que aparece en la figura, donde vemos que del presidente de una empresa (nodo principal) cuelgan tres directores (nodos descendientes de primer nivel), de estos 6 supervisores (nodos descendientes de segundo nivel) y así hasta llegar a los empleados que no tienen gente a su cargo.

Cláusulas START WITH y CONNECT BY PRIOR
Para construir una consulta jerárquica, en el comando SQL SELECT se deben utilizar las cláusulas START WITH y CONNECT BY PRIOR. Por ejemplo, si utilizamos los datos de nuestra estructura y ejecutamos la siguiente consulta:

SELECT nombre, cargo, level

FROM jerarquia_empleados

START WITH nombre = 'USER'

CONNECT BY PRIOR emp_id = sup_id;

El script devolvera el nombre de todos los empleados y su cargo que dependen de USER. También podemos ver que la columna LEVEL muestra el nivel de la estructura en que se encuentra el registro. Por lo tanto, con la cláusula START WITH identificamos el registro inicial, y con la cláusula CONNECT BY PRIOR, indicamos las columnas entre las que establece la relación registro padre = registro hijo.

Existe una función que puede resultar de mucha utilidad en las consultas jerárquicas, se trata de la función PL/SQL SYS_CONNECT_BY_PATH(), que permite concatenar los diferentes valores durante el recorrido dentro de una estructura jerárquica. Veamos un ejemplo para comprender mejor su funcionamiento:

SELECT SUBSTR(SYS_CONNECT_BY_PATH(NOMBRE, ','), 2) CSV

        FROM (SELECT NOMBRE,

                   ROW_NUMBER() OVER(ORDER BY CARGO) RN,

                   COUNT(*) OVER() CNT

              FROM JERARQUIA_EMPLEADOS

             WHERE EMP_ID = SUP_ID)

     WHERE RN = CNT

     START WITH RN = 1

    CONNECT BY RN = PRIOR RN + 1;

Al consultar  tendremos lo siguiente : PEDRO,JUAN,MARIA. Con ello resulta muy sencillo realizar consultas jerárquicas mediante la utilización de las cláusulas START WITH y CONNECT BY PRIOR, y la función PLSQL SYS_CONNECT_BY_PATH ().

Disparadores Compuestos (Compound Triggers)

Los Disparadores Compuestos o Compound Triggers son una nueva herramienta para apoyar en la codificación reducida y la interactividad entre objetos en Base de datos. Combina los cuatro eventos de activación en una sola pieza de código. Además de la eficacia de la codificación, que aborda algunos problemas como:

• Error en Tablas mutantes (ORA-04091)
• Cuando se usa Trigger con EACH ROW que por cada fila hacer alguna transacción en alguna otra tabla utilizando nuevos valores.

Anteriormente, las soluciones alternativas  para estos problemas contenía  codificación compleja utilizando colecciones y múltiples Disparadores. Lo cual se convierte en pesadilla para los desarrolladores para simular y probar tales escenarios.

Los Disparadores Compuestos tratan fácilmente con todos los escenarios anteriores de una manera eficiente e interactiva. La nueva función en Base datos Oracle 11g no sólo aumenta el rendimiento durante las operaciones en masa, también mejora el estado de sus variables y el rendimiento en las consultas. Ellos se restablecen sólo al comienzo de una nueva declaración. Un punto importante a mencionar aquí es el manejo de excepciones. Se tiene que ser manejado de forma explícita en todos los bloques de sincronización, y no en el cuerpo del Trigger.

La sintaxis de un Disparador Compuesto contiene cuatro bloques que representan cuatro tiempos asociados con eventos DML de la siguiente forma: 

CREATE OR REPLACE TRIGGER compound_trigger_name

  FOR INSERT | DELETE UPDATE OF column ON table COMPOUND TRIGGER

-- Sección declaratica (optional)

-- Variables se declaran durante el Trigger

--Ejecución antes de una consulta DML

  BEFORE STATEMENT IS

  BEGIN

  NULL;

  END BEFORE STATEMENT;

-- Ejecución antes de cada fila, variables :NEW, :OLD son permitidas

  BEFORE EACH ROW IS

  BEGIN

  NULL;

  END BEFORE EACH ROW;

-- Ejecución despues de cada fila, variables :NEW, :OLD son permitidas

  AFTER EACH ROW IS

  BEGIN

  NULL;

  END AFTER EACH ROW;

--Ejecución despues de una consulta DML

  AFTER STATEMENT IS

  BEGIN

  NULL;

  END AFTER STATEMENT;

END compound_trigger_name;

Dinero a Corto Plazo

La pantalla de Dinero a Corto Plazo sirve para registrar aquellas transacciones donde se realizan  prestamos  o inversión de fondos en un corto plazo.

El dinero a corto plazo, también conocido como CALL MONEY, el cual es dinero depositado o tomado prestado por breves periodos de tiempo, generalmente entre bancos e instituciones financieras, con un tipo de interés y sin vencimiento fijo. El banco prestamista puede exigir el reembolso a la demanda o el prestatario puede efectuar el reembolso en cualquier momento sin aviso previo.

Cada vez que se negocie o se reembolse una transacción, se puede pagar parte o la totalidad del principal, y restablecer la tasa de interés y plazo. También puede provisionar, liquidar  o agravar el interés.

La ventana contiene dos regiones principales:

La región Detalles de Transacción Común contiene información general que identifica la operación.

La región de las pestañas Transacciones Actuales para Transacción Superior y Historial Transacción para Contraparte Superior contiene una lista transacciones individuales asociados con el acuerdo seleccionado en Detalles de Transacción Común.

Ingresando Transacciones de Corto Plazo

Para ello navegar a una  Responsabilidad de Treasury >>  Transacciones de Mercado Monetario >> Dinero a Corto Plazo. En la región Detalles de Transacción Común, elegir un acuerdo de una compañía y una Contraparte y luego dar al botón Transacciones.

Los datos de la cabecera como el Representante, subtipo de transacción, Compañía, Contraparte, la divisa y Cartera de Valores son copiados tal se creó en la pantalla anterior. Luego se debe ingresar en la pestaña de Detalles Principales Los datos como el Tipo de Valores si estará asegurado o no. Debe elegir  un Tipo de Producto. Ingresar una Fecha de Liquidación, Si la transacción tiene una Fecha de Vencimiento que difiere de la fecha de liquidación, debe ingresar una  fecha de vencimiento. El campo Días muestra el número de días entre estas fechas. Si quieres enlazar la transacción a otra transacción o grupo de transacciones, en el campo Código Enlace elegir un código de enlace.

Luego ingresar un Importe Principal y en la pestaña de Detalle Interés debe colocar colocar una Tasa Interés para la transacción. Tener en cuenta que una vez ingresado este valor ya no se podrá cambiar por lo que es muy importante saber la tasa que tendrá cada transacción.

Para finalizar grabar la transacción. Puede cerrar la pantalla y regresara la pantalla principal o puede  añadir otra transacción, para ello debe dar clic al botón Nueva  Transacción.

Triggers o Disparadores de Base de Datos

Los Triggers o disparadores de base de datos son bloques PL/SQL almacenados asociados a una tabla que se ejecutan automáticamente cuando se producen ciertos eventos o sucesos que afectan  a la tabla.

La sintaxis para crear un trigger es la siguiente: 

CREATE {OR REPLACE} TRIGGER nombre_disp
  [BEFORE|AFTER]
  [DELETE|INSERT|UPDATE {OF columnas}] [ OR [DELETE|INSERT|UPDATE {OF columnas}]...]
  ON tabla
  [FOR EACH ROW [WHEN condicion disparo]]
[DECLARE]
  -- Declaración de variables locales
BEGIN
  -- Instrucciones de ejecución
[EXCEPTION]
  -- Instrucciones de excepción
END;

El uso de OR REPLACE permite sobreescribir un trigger ya existente en Base de datos. Si se omite, y el trigger existe, se producirá, un error. Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de cada operación.   El modificador BEFORE AFTER indica si el trigger se ejecutará antes o despues de ejecutarse la sentencia SQL definida por DELETE, INSERT o UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica si el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción. La cláusula WHEN sólo es válida para los disparadores con nivel de fila.

Dentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

El siguiente ejemplo muestra un trigger que inserta un registro en la tabla Clientes2 cada vez que insertamos un nuevo registro en la tabla Clientes1:

CREATE OR REPLACE TRIGGER TR_Clientes   AFTER INSERT ON Clientes1   FOR EACH ROW DECLARE   -- local variables BEGIN   INSERT INTO Clientes2   (Customer_Id,Customer_name,Customer_number)   VALUES   (:NEW.Customer_Id,:NEW.Customer_name,:NEW.Customer_number); END ;

Restricciones de los  Triggers

El cuerpo de un trigger es un bloque PL/SQL. Cualquier orden que sea legal en un bloque PL/SQL, es legal en el cuerpo de un disparador, con las siguientes restricciones:

• Un disparador no puede emitir ninguna orden de control de transacciones:  COMMIT, ROLLBACK  o SAVEPOINT. El disparador se activa como parte de la ejecución de la orden que provocó el disparo, y forma parte de la misma transacción que dicha orden. Cuando la orden que provoca el disparo es confirmada o cancelada, se confirma o cancela también el trabajo realizado por el disparador.
• Por razones idénticas, ningún procedimiento o función llamado por el disparador puede emitir órdenes de control de transacciones.

• El cuerpo del disparador no puede contener ninguna declaración de variables LONG o LONG RAW

Uso variables  :OLD y :NEW   

Dentro de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELETE) que ha ejecutado en el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

El acceso a estos campos depende del evento del disparador:

En un trigger ejecutado por un INSERT, se puede acceder al campo :NEW uúnicamente el campo :OLDcontiene null. En una inserción se puede emplear :NEW para escribir nuevos valores en las columnas de la tabla.

En un trigger ejecutado con UPDATE, se puede acceder a ambos campos. En una actualizacion, se pueden comparar los valores :NEW y :OLD.

En un trigger ejecutado por un DELETE, únicamente se puede acceder al campo :OLD, ya que el campo :NEW no existe luego que el registro es eliminado, el campo :new contiene null y no puede ser modificado.

Origenes y Categorias de General Ledger

En General Ledger los asientos diarios usan los términos de Orígenes y Categorías  para poder diferenciar el origen de estos ingresos y poder dar un revisión a detalle. General Ledger da la opción de seleccionar los orígenes y categorías predefinido o poder crear sus propias definiciones.

Orígenes de Asientos

Los Orígenes de Asientos indican de donde proviene el ingreso de cada registro que se ubica en un asiento contable. El modulo de Contabilidad General suministra una lista de orígenes o fuentes predefinidos para los diferentes ingresos de asientos  se originan en los diferentes  módulos  como Payables o Assets.

Puede definir sus propios orígenes para sistemas externos de alimentación al Oracle. Para cada asiento, debe especificar  si desea importar información de referencia para sus asientos de resumen. Para  ingresar orígenes debemos navegar a una responsabilidad de General Ledger >> Configuración >> Asiento >> Orígenes.

Ingresar un Nombre único y una Descripción para el Origen de Asientos. No se puede eliminar un nombre de origen después de guardar.  Activar o desactivar las siguientes casillas:

Importar Referencias Asiento para la información de Asientos de Resumen que se importa de sus sistemas de alimentación. 

Congelar Asientos del origen evitando que los usuarios realicen cambios en las asientos no contabilizadas con el mismo origen.

Requerir Aprobación Asiento para exigir que los asientos con una origen determinado deban ser aprobados antes de contabilizarlos

Categorías de Asientos

Las Categorías de Asientos le ayudan a diferenciar los asientos por tipo, como pagos o recibos. Al crear Asientos, debe elegir la opción predeterminada o especifique una categoría. Puede utilizar secuencias de documentos para los ingresos de asientos por cada categoría. 

Para  ingresar Categorías debemos navegar a una responsabilidad de General Ledger >> Configuración >> Asiento >> Categorias. Ingresar un Categoría única y una Descripción para la Categoría de Asientos.

Query para obtener información de Asientos contables

El siguiente query que les comparto obtiene datos principales de los asientos diarios que se generan en General Ledger donde podrá filtrar por Periodo contable y obtener información del origen y la categoria del asiento.

select

  gjh.je_header_id,

  gjh.set_of_books_id ,

       (SELECT user_je_source_name

          FROM GL_JE_SOURCES_TL

         where language = 'ESA'

           and je_source_name = gjh.je_source) origen,

       (SELECT user_je_category_name

          FROM GL_JE_CATEGORIES_TL

         where language = 'ESA'

           and je_category_name = gjh.je_category) categoria,

   

       gjh.period_Name periodo,

       gjh.name asiento,

       gjh.currency_code divisa,

       gjl.je_line_num,

       gcc.segment1 ||'.'|| gcc.segment2  ||'.'||

       gcc.segment3 ||'.'|| gcc.segment4  ||'.'||

       gcc.segment5  cuenta, /*de acuerdo al plan de cuentas*/

          (NVL(gjl.ACCOUNTED_DR, 0) - NVL(gjl.ACCOUNTED_CR, 0)) importe

  from GL_JE_LINES                gjl,

       GL_JE_headers              gjh,

       GL_PERIODS                 glp,

       GL_CODE_COMBINATIONS       gcc

 where gjl.period_name = glp.period_name

   and gjl.je_header_id = gjh.je_header_id

   and gcc.code_combination_id = gjl.code_combination_id

   and gjh.period_Name=&PERIODO

   AND glp.ADJUSTMENT_PERIOD_FLAG = 'N'

   and gcc.DETAIL_POSTING_ALLOWED_FLAG = 'Y';