Query para obtener la información de los Compradores

El siguiente query que les comparto obtiene datos principales de las compradores del modulo de Purchasing. Esto nos puede servir para tener un control o un listado de que usuarios o compradores tenemos en el oracle.

SELECT PA.AGENT_ID,

       PAPF.FULL_NAME       COMPRADOR,

       PA.START_DATE_ACTIVE FECHA_INICIO,

       PA.END_DATE_ACTIVE   FECHA_FIN,

       FU.USER_NAME         USUARIO,

       HAOUT.NAME           ORGANIZACION_HR,

       PAPF.EMAIL_ADDRESS   CORREO,

       PAPF.HONORS          PUESTO

  FROM PO.PO_AGENTS                    PA,

       HR.PER_ALL_PEOPLE_F             PAPF,

       APPLSYS.FND_USER                FU,

       HR.HR_ALL_ORGANIZATION_UNITS_TL HAOUT,

       HR.PER_ALL_ASSIGNMENTS_F        PAAF

 WHERE PA.AGENT_ID = PAPF.PERSON_ID

   AND FU.EMPLOYEE_ID(+) = PAPF.PERSON_ID

   AND PAPF.PERSON_ID = PAAF.PERSON_ID(+)

   AND HAOUT.LANGUAGE='ESA'

   AND PAAF.ORGANIZATION_ID = HAOUT.ORGANIZATION_ID(+)

   AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND

       PAPF.EFFECTIVE_END_DATE

   AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND

       PAAF.EFFECTIVE_END_DATE

   AND PAAF.ASSIGNMENT_TYPE = 'E'

   AND PAAF.PRIMARY_FLAG = 'Y'

    AND PAPF.FULL_NAME = '&COMPRADOR'

   AND PA.END_DATE_ACTIVE IS NULL;

Query para obtener los Grupos de Solicitudes por Programa Concurrente

El siguiente query que les comparto obtiene datos principales del grupo de solicitudes que se ubica un programa concurrente. Es posible tener mas de un programa concurrente en varios Grupos de Solicitudes.

SELECT

FVL.APPLICATION_ID,

FVL.APPLICATION_NAME APLICACION,

RG.REQUEST_GROUP_ID ,

RG.REQUEST_GROUP_NAME "Grupo de Solicitudes" ,

RG.DESCRIPTION,

DECODE(RGU.REQUEST_UNIT_TYPE,'P',

'Program','S','Set',RGU.REQUEST_UNIT_TYPE) "Tipo",

CP.CONCURRENT_PROGRAM_ID,

CP.CONCURRENT_PROGRAM_NAME "Abreviatura",

CPT.USER_CONCURRENT_PROGRAM_NAME "Programa Concurrente"

FROM

  FND_REQUEST_GROUPS RG,

  FND_REQUEST_GROUP_UNITS RGU,

  FND_CONCURRENT_PROGRAMS CP,

  FND_CONCURRENT_PROGRAMS_TL CPT,

  FND_APPLICATION_VL fVL

WHERE RG.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID

AND FVL.APPLICATION_ID = RG.APPLICATION_ID

AND RGU.REQUEST_UNIT_ID = CP.CONCURRENT_PROGRAM_ID

AND CP.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID

AND CPT.USER_CONCURRENT_PROGRAM_NAME =&P_CONC_PROG_NAME;

Columnas de registro histórico(WHO)

La función de registro histórico o Record History (WHO) se caracteriza por dar información acerca de quién creó o actualizo una o varias  filas en tablas de Oracle E-Business Suite.  Si se     agrega columnas especiales WHO a las tablas y la lógica WHO a los formularios y procedimientos almacenados, los usuarios pueden realizar un seguimiento de los cambios realizados en sus datos. Al observar columnas WHO, los usuarios pueden diferenciar entre los cambios realizados por los formularios y los cambios realizados por los programas concurrentes.

Puede presentar las columnas WHO como campos ocultos en cada bloque de su formulario (que corresponden a las columnas de la tabla asociada). Usando la opción  FND_STANDARD.SET_WHO en PRE-UPDATE y PRE-INSERT para rellenar estos campos.

Adición de Columnas de registro histórico

En la siguiente tabla se muestran las columnas estándar utilizados para Historial (WHO), los atributos de la columna y descripciones, como también las fuentes de los valores. Establezca las columnas CREATED_BY y CREATION_DATE sólo cuando se inserta una fila (usando FND_STANDARD.SET_WHO en un formulario).

Cualquier tabla que puede ser actualizado por un programa concurrente también tiene columnas adicionales. La siguiente tabla muestra las columnas de procesamiento simultáneos utilizados para Historial cuando se actualiza por un programa.

Clases de Propiedades

Aplicar la clase de propiedad CREATION_OR_LAST_UPDATE_DATE a los campos CREATION_DATE  y LAST_UPDATE_DATE en los formularios. Estas clases de propiedades establecen los atributos correctos para estos campos, incluyendo el tipo de datos y el ancho. Aplicar la clase de propiedad TEXT_ITEM a los campos del formulario LAST_UPDATED_BY y CREATED_BY.

Insertar FND_STANDARD.SET_WHO en los triggers PRE-UPDATE y PRE-INSERT para a ni vel del bloque de consulta. Una vez guardado los cambios compilar y verificar colocándose en el formulario de la tabla en consulta e ir a Ayuda >> Registrar Historial.

Query para obtener información de transacciones por Pedido de Movimientos

En Oracle Inventory podemos realizar pedidos de Movimiento de una organización de Inventarios a otro. Una vez aprobado y transaccionado se genera movimientos de material por la transacción, Las siguientes tablas se utilizan en el proceso de Pedidos de Movimiento.

MTL_TXN_REQUEST_HEADERS: Tabla donde se localiza la cabecera de los pedidos de movimientos donde se almacena el número de Pedido de movimiento en la columna (REQUEST_NUMBER). Tiene un estado, pero esto no se utiliza tanto como el estado de las líneas para conducir la funcionalidad.

MTL_TXN_REQUEST_LINES: Tabla donde se localiza las línea de los pedidos de Movimiento, este es el que impulsa más consultas y comprobaciones de estado del pedido de movimiento, ya que cada línea puede ser transaccionado de forma individual.

MTL_MATERIAL_TRANSACTIONS_TEMP: Tabla temporal de transacciones de material también llamado la tabla  de transacción temporal, esto retiene las asignaciones que actúan como reservas de inventario. Una asignación es donde tienes que elegir un artículo específico en el inventario hasta el lote, localizador, serial, revisión de movimiento, pero no se realiza en realidad el movimiento.

MTL_MATERIAL_TRANSACTIONS: Tabla que almacena un registro de cada transacción de materiales o de cada actualización de costos realizado en Inventarios. La columna move_order_line_id contiene el id d la línea del pedido de movimiento transaccionado.

1. Cuando se crea el pedido de movimiento se tiene los siguientes valores:

Quantity: 10
Quantity_Delivered: NULL
Quantity_Detailed: NULL
Required_ Quantity: NULL
Line_Status: 1 (Incompleto)

2. Cuando se aprueba el  pedido de movimiento se tiene los siguientes valores:

Quantity: 10
Quantity_Delivered: NULL
Quantity_Detailed: NULL
Required_ Quantity: NULL
Line_Status: 3 (Aprobado)

3. Cuando se asigna el  pedido de movimiento se tiene los siguientes valores:

Quantity: 10
Quantity_Delivered: NULL
Quantity_Detailed: 10
Required_ Quantity: NULL
Line_Status: 3 (Aprobado)

4. Cuando se transacciona el  pedido de movimiento se tiene los siguientes valores:

Quantity: 10
Quantity_Delivered: 10
Quantity_Detailed: 10
Required_ Quantity: NULL
Line_Status: 5 (Cerrado)

NOTA: Cuando un pedido de movimiento se asigna, se inserta un registro correspondiente en la tabla de pendientes MTL_MATERIAL_TRANSACTIONS_TEMP. Cuando el pedido de movimiento se transacciona, el registro se mueve de la tabla pendiente a la tabla histórica MTL_MATERIAL_TRANSACTIONS.

a) Ejemplo de consulta para enlazar pedidos de movimiento con la tabla pendientes:

SELECT mmtt.transaction_temp_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions_temp mmtt

WHERE toh.header_id = tol.header_id

 AND toh.organization_id = tol.organization_id

 AND tol.line_id = mmtt.move_order_line_id;

b) Ejemplo de consulta que une MTL_MATERIAL_TRANSACTIONS a la orden de movimiento:

SELECT mmt.transaction_id,

  tol.organization_id,

  toh.request_number,

  toh.header_id,

  tol.line_number,

  tol.line_id,

  tol.inventory_item_id,

  toh.description,

  toh.move_order_type,

  tol.line_status,

  tol.quantity,

  tol.quantity_delivered,

  tol.quantity_detailed

FROM mtl_txn_request_headers toh,

  mtl_txn_request_lines tol,

  mtl_material_transactions mmt

WHERE toh.header_id = tol.header_id

 AND toh.organization_id = tol.organization_id

 AND tol.line_id = mmt.move_order_line_id

 AND toh.request_number = '&PedidoMovimiento';

La tabla de pedidos de movimientos (MTL_TXN_REQUEST_LINES) proporciona el estado del pedido en la columna de la LINE_STATUS como numérico. A continuación se enumeran los diferentes estados y de los códigos:

 1 Incompleto

 2 Aprobación Pendiente

 3 Aprobado

 4 No Aprobado

 5 Cerrado

 6 Cancelado

 7 Pre-Aprobado

 8 Parcialmente Aprobado

 9 Cancelado por Origen

Estos se pueden encontrar en el lookup MTL_TXN_REQUEST_STATUS:

Operaciones y funciones con fechas y sysdate

Cuando se trabaja con fechas en Oracle  se debe tener en cuenta que la aritmética de fechas en Oracle se trabaja en función de un día. La pseudo-columna SYSDATE muestra la fecha y hora actual de sistema, podemos  aumentar la fecha y hora actual en un día. Se pueden usar fracciones para añadir horas, minutos o segundos a la fecha. Ahora si bien podemos usar el comando to_date para formatear fechas podemos en base a SYSDATE hacer cálculos a futuro bajo los siguientes ejemplos:

SELECT sysdate - 10 FROM dual;

SYSDATE

--------

13/10/2015 05:32:19 p.m

SELECT sysdate + 4 FROM dual;

 

SYSDATE

--------

27/10/2015 05:32:19 p.m

SELECT sysdate + (5 * 2) FROM dual;

SYSDATE

--------

02/11/2015 05:35:01 p.m.

SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

SYSDATE

--------

05:35 PM

SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

SYSDATE

--------

07:35 PM

En base al ultimo ejemplo podemos usar una alternativa usando la función numtodsinterval. Con la función numtodsinterval realiza la división de dividir sobre 24 horas. Las opciones válidas para esta función son: ‘DAY’, ‘HOUR’, ‘MINUTE’, o ‘SECOND’. Aquí algunos ejemplos:

SELECT to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')

FROM dual;

SYSDATE

--------

07:57 PM

SELECT to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')FROM dual;

SYSDATE

--------

06:43 PM

Para trabajar con meses y años (cada uno de los cuales puede variar en número de días) Oracle ofrece la función numtoyminterval. Esta trabaja en forma similar a la función numtodsinterval mencionada antes tomando un numero y una cadena string. Las opciones válidas para esta función son: ‘YEAR’ or ‘MONTH’. . Aquí algunos ejemplos:

SELECT to_char(sysdate + numtoyminterval(5, 'MONTH'), 'DD/MM/YYYY') FROM dual;

SYSDATE

--------

23/03/2016

SELECT to_char(sysdate + numtoyminterval(2, 'YEAR'), 'DD/MM/YYYY') FROM dual;

SYSDATE

--------

23/10/2017

Si deseamos comparar algunas fechas y encontrar el numero de días entre ellas. Para ver este resultado en días debemos usar la función months_between. de la siguiente manera:

SELECT months_between(TRUNC(sysdate), to_date('21/05/2014', 'DD/MM/YYYY')) FROM dual

MONTHS_BETWEEN(TRUNC(SYSDATE),

------------------------------

17.0645161290323

Las funciones greatest y least pueden ser usadas en fechas para retornar la menor o la mayor fecha.

SELECT greatest(sysdate,sysdate+1, to_date('09/11/2013','MM/DD/YYYY'), to_date('12/25/2010','MM/DD/YYYY'))FROM dual;

GREATEST

--------

24/10/2015 06:10:56 p.m.

SELECT least(sysdate,sysdate+1,to_date('09/11/2013','MM/DD/YYYY'), to_date('12/25/2010','MM/DD/YYYY'))FROM dual;

LEAST(SY

--------

25/12/2010

Podemos usar la función last_day para retornar el último día del mes pasado como parámetro.

select last_day(sysdate) from dual;

LAST_DAY

--------

31/10/2015 06:13:48 p.m.

La función next_day retorna la fecha de la próxima ocurrencia de un día de la semana(’Monday’, ‘Tuesday’, etc.) después de una fecha dada. ejemplo: consultar la fecha del próximo domingo:

select next_day(sysdate,'Domingo') from dual;

NEXT_DAY

--------

25/10/2015 06:14:30 p.m.