La empresa TracFactory S.A. de C.V., dedicada a la comercialización de tractores agrícolas, ha decidido comenzar a digitalizar y automatizar el proceso de envío/recepción de información relacionada con la adquisición de bienes con sus proveedores, por lo tanto, desean crear un equipo que se encargue de ejecutar dicho proyecto con éxito.

Se te ha contratado para intervenir en tareas específicas, las cuales serán detalladas a continuación:

1. Crea una base de datos denominada “TFMerchandasing”.
2. Crea un esquema llamado “Exchange”.
3. Diseña y crea las tablas “Suppliers”, “PurchaseOrders” y “PurchaseOrderDetails” que pertenezcan al esquema “Exchange”.

4. Crea una vista llamada “TOPSuppliers”, que al seleccionarse regrese únicamente los 10 proveedores activos con mayor cantidad de órdenes de compras activas con relación al total, es decir, se deberá filtrar por el estatus activo en SupplierStatus/PurchaseOrderStatus y calcular el total por proveedor.
   
   Las columnas que se esperan de regreso deberán ser las siguientes:
   • SupplierCode.
   • SupplierName.
   • CategoryID.
   • PurchaseOrderCount (conteo de órdenes de compra).
   • PurchaseOrderTotal (suma del importe de órdenes de compra).
     
     
5. Crea una función “fnPurchaseOrderNumber” que retorne una nomenclatura que sirva para dar formato a la columna PurchaseOrderNumber. La longitud para retornar deberá ser a 12 posiciones y solicitará dos datos: sufijo y valor, por ejemplo, si se envía el sufijo “PO” y valor 10, deberá retornar PO0000000010, o si se envía #PO y valor 501, deberá retornar #PO000000501.
   
   
6. Diseña un procedimiento almacenado para monitorear que, cada vez que este sea ejecutado, el objeto filtre las líneas de productos que exceden de 3,000 en amount, pero únicamente se aplique para las órdenes de compra de una semana hacía atrás, considerando como fecha de ejecución la fecha que actualmente es proporcionada por el servidor. Las columnas por regresar serán las siguientes:
   • PurchaseOrderNumber (con uso de la nomenclatura de la función “fnPurchaseOrderNumber”).
   • PurchaseOrderDate.
   • PurchaseOrderDelivery.
   • Amount.
   
   Importante: el procedimiento almacenado se llamará “PurchaseOrderDetailMonitoring” y no recibe ningún parámetro de entrada, por esta razón deberás hacer uso de una función que te permita obtener la fecha actual del servidor. Se puede usar una variable para una    mejor manipulación de este dato, pero es totalmente opcional.
   
   
7. El administrador del proyecto desea conocer cuando un registro cambia de estatus a cancelado. Se deberá crear una tabla llamada “MonitoringStatus” que almacenará el ID del registro que cambió de estatus, el nombre de la tabla, fecha y hora exacta, por ejemplo:

Importante: la tabla debe estar dentro del mismo esquema previamente definido. Estos registros deberán ser insertados mediante un disparador en las diferentes tablas.

8. Crear un procedimiento almacenado “PurchaseOrderTotalByMonth” que tenga como parámetro de entrada el mes y año para que, de acuerdo con esa información, retorne los datos totalizados por proveedor y ordenados de manera descendente, con base en el total por orden de compra como se representa en el siguiente ejemplo:

9. Diseña un reporte desde Reporting Services que muestre los datos más relevantes de los proveedores. Solamente se necesita mostrar SupplierCode, SupplierName, CategoryID, BuyerID, CurrencyID y SupplierStatus. El reporte deberá estar en español.
10. Diseña un reporte usando Reporting Services, el cual represente la información de órdenes de compra. Para este reporte se deberán aplicar conocimientos de agrupación y agregación, pues se requiere mostrar la cantidad de órdenes de compra activas y canceladas con sus respectivos totales en importe total; todo es por año. Si el total por estatus “cancelado” sobrepasa de los 500,000, se deberá mostrar el valor en color rojo y subrayado.
11. Implementa un reporte usando Reporting Services y usar la tabla “MonitoringStatus” para que, por fecha de inicio y fin, se pueda filtrar la información de los registros a los que se les ha modificado su estatus, por lo tanto, se deberán aplicar parámetros de entrada de tipo fecha.
12. Crea un esquema de ETL, es decir, la manera en que se procesarán los datos y se enviarán al respectivo sistema que tiene relación con el sitio del portal de proveedores.
    
    Nota: Deberás definir las reglas, técnicas y formatos necesarios para la ejecución del esquema.

1. Se deberá presentar un script (.sql) con el nombre “TFMerchandisingDefinition.sql” que contenga la ejecución de las instrucciones de los puntos 1, 2 y 3.
2. Se creará un script (.sql) con el nombre “TFMerchandisingObjects.sql” que va a contener los comandos de creación de objetos para responder los puntos 4, 5 y 6.
3. Grabar la pantalla del SSMS comprobando la funcionalidad del punto 7. Se recomienda dar una breve explicación mientras se ejecuta el proceso.
4. Adjuntar una imagen de pantalla ejecutando el procedimiento almacenado creado en el punto 8.
5. Exportar a PDF y adjuntar los tres reportes que se crearon en los puntos 9, 10 y 11.
6. Crear un documento Word con las especificaciones del esquema ETL del punto 12.

Criterio de evaluación	Ponderación
Creación de base de datos y esquema.	5
Diseño y creación de tablas.	10
Creación de vista “TOPSuppliers”.	5
Creación de función para nomenclatura de la orden de compra.	5
Diseño y estructura del procedimiento almacenado “PurchaseOrderDetailMonitoring”.	10
Implementación de disparadores para el monitoreo de cambios de estatus en los registros con ayuda de la tabla “MonitoringStatus”.	10
Reporte de datos de proveedores con uso de Reporting Services.	10
Reporte de datos de órdenes de compra.	10
Reporte que muestra información de la tabla “MonitoringStatus”.	15
Esquema de ETL para intercambio de información con el portal de proveedores.	15
Obtener el diseño Entidad – Relación de la base de datos TFMerchandasing.	5
Total	100