Created
February 5, 2016 02:17
-
-
Save arbo-hacker/9437c0e8933691c62ee2 to your computer and use it in GitHub Desktop.
Como validar si se realizaron las rebajas de inventario de todos los artículos que se vendieron / devolvieron en el día en Oracle Retail (RESA vs RMS)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
RESA AS | |
( | |
SELECT STD.BUSINESS_DATE FECHA,STD.STORE STORE,COUNT(DISTINCT STI.ITEM) ITEMS, SUM(STI.QTY) UNIDADES | |
FROM SA_TRAN_ITEM STI | |
JOIN SA_TRAN_HEAD STH | |
ON (STI.TRAN_SEQ_NO = STH.TRAN_SEQ_NO) | |
JOIN SA_STORE_DAY STD | |
ON (STD.STORE_DAY_SEQ_NO = STH.STORE_DAY_SEQ_NO) | |
WHERE STD.BUSINESS_DATE =&p_sales_date | |
AND STD.DATA_STATUS = 'F' | |
AND STD.STORE =&p_store | |
AND STD.AUDIT_STATUS = 'A' | |
AND STD.STORE_STATUS = 'C' | |
AND STH.TRAN_TYPE IN ('SALE','RETURN') | |
AND STI.ITEM NOT IN (SELECT DISTINCT DETAIL_ITEM FROM ITEM_XFORM_DETAIL) -- no sea un articulo transformado | |
GROUP BY STD.BUSINESS_DATE, STD.STORE | |
), | |
RMS AS | |
( | |
SELECT TRAN_DATE FECHA, LOCATION STORE, COUNT(DISTINCT ITEM) ITEMS, SUM (UNITS) UNIDADES | |
FROM TRAN_DATA_HISTORY | |
WHERE TRAN_CODE IN (1) | |
AND TRAN_DATE =&p_sales_date | |
AND LOCATION =&p_store | |
AND ITEM NOT IN (SELECT DISTINCT HEAD_ITEM FROM ITEM_XFORM_HEAD) | |
GROUP BY TRAN_DATE,LOCATION | |
) | |
-- | |
SELECT TO_CHAR(RESA.FECHA,'dd/mm/yyyy') FECHA, RESA.STORE STORE, | |
NVL(RESA.ITEMS,0) ITEMS_RE, NVL(RESA.UNIDADES,0) UNIDADES_RE, | |
NVL(RMS.ITEMS,0) ITEMS_R, NVL(RMS.UNIDADES,0) UNIDADES_RMS, | |
NVL(RESA.ITEMS - RMS.ITEMS,0) DIFERENCIAS_A, NVL(RESA.UNIDADES - RMS.UNIDADES,0) DIFERENCIAS_U | |
FROM RESA | |
LEFT JOIN RMS | |
ON (RESA.FECHA = RMS.FECHA AND RESA.STORE = RMS.STORE) | |
ORDER BY 1,2; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment