-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSales and Promotion.txt
10 lines (7 loc) · 4.28 KB
/
Sales and Promotion.txt
1
2
3
4
5
6
7
8
9
10
Sales and Promotion
SELECT Q1.RESA_PROMO_AMT,Q1.TRAN_SEQ_NO, Q1.BUSINESS_DATE,Q2.RDW_PROMO_AMT,Q2.TRAN_IDNT,Q2.DAY_DT,(Q1.RESA_PROMO_AMT-Q2.RDW_PROMO_AMT) DIFFERENCEFROM(SELECT SUM(NVL(STD.DISC, 0)) RESA_PROMO_AMT, STH.TRAN_SEQ_NO, SD.BUSINESS_DATE from SA_TRAN_HEAD STH, SA_TRAN_ITEM STI, (SELECT STORE, DAY, TRAN_SEQ_NO, ITEM_SEQ_NO, SUM(QTY*UNIT_DISCOUNT_AMT)DISC FROM SA_TRAN_DISC WHERE RMS_PROMO_TYPE = '9999' GROUP BY STORE, DAY,TRAN_SEQ_NO, ITEM_SEQ_NO) STD , SA_STORE_DAY SD WHERE STH.TRAN_SEQ_NO = STI.TRAN_SEQ_NO AND STH.STORE = STI.STORE AND STH.DAY = STI.DAY AND STI.TRAN_SEQ_NO = STD.TRAN_SEQ_NO AND STI.ITEM =STI.ITEM AND STI.STORE = STD.STORE AND STI.DAY = STD.DAY AND STI.ITEM_SEQ_NO = STD.ITEM_SEQ_NO AND STH.STORE_DAY_SEQ_NO = SD.STORE_DAY_SEQ_NO AND BUSINESS_DATE BETWEEN '01-Jul-12' and '31-Jul-12' --MAR12 -MAR-13 AND STH.STORE = SD.STORE AND STH.STATUS = 'P' and STH.TRAN_TYPE in ('SALE', 'RETURN', 'EEXCH') AND STI.ITEM_TYPE != 'GCN' GROUP BY STH.TRAN_SEQ_NO,SD.BUSINESS_DATE)Q1,(SELECTSUM(NVL(F_PRMTN_MKDN_AMT_lcl,0)) RDW_PROMO_AMT,S.TRAN_IDNT,T.DAY_DTfrom RDW12DM.SLS_PRMTN_ITEM_LLM_DM@RETEK_LINK_RDW S, RDW12DM.PROD_ITEM_DM@RETEK_LINK_RDW P, RDW12DM.TIME_DAY_DM@RETEK_LINK_RDW T, RDW12DM.ORG_LOC_DM@RETEK_LINK_RDW LWHERE P.ITEM_KEY = S.ITEM_KEY and S.LOC_KEY = L.LOC_KEY AND T.DAY_DT BETWEEN '01-Jul-12' and '31-Jul-12' and T.DAY_IDNT = S.DAY_IDNT
GROUP BY S.TRAN_IDNT, T.DAY_DT )Q2WHERE Q1.BUSINESS_DATE =Q2.DAY_DT(+) ANDQ1.TRAN_SEQ_NO =Q2.TRAN_IDNT(+) and(((q1.RESA_PROMO_AMT-q2.RDW_PROMO_AMT)!=0) ) );
INSERT INTO sa_export_log (store, DAY, store_day_seq_no, system_code, seq_no, status) SELECT store, DAY, store_day_seq_no, system_code, MAX(seq_no) + 1, 'R' FROM sa_export_log sel WHERE store_day_seq_no IN (SELECT store_day_seq_no FROM sa_store_day WHERE store_day_seq_no in () and files_loaded is not null) AND system_code IN ('RDW') AND NOT EXISTS (SELECT 'X' FROM sa_export_log seli
WHERE seli.store_day_seq_no = sel.store_day_seq_no AND seli.system_code = sel.system_code AND seli.status = 'R') GROUP BY store, DAY, store_day_seq_no, system_code;
VARIANCE EXTRACTION
select q1.BUSINESS_DATE,q1.store,q1.TRAN_SEQ_NO,q1.resaitem,RESAVALUE,q2.DAY_DT,q2.RDW_STORE,Q2.TRAN_IDNT,Q2.ITEM_IDNT,q2.RDW_AMT,(q1.RESAVALUE-q2.RDW_AMT) Differencefrom(SELECT SD.BUSINESS_DATE, SD.STORE, sTH.TRAN_SEQ_NO, sti.item resaitem, SUM((NVL(STI.QTY, 0)*NVL(STI.UNIT_RETAIL, 0))-(NVL(STD.DISC, 0))) resavalue FROM SA_TRAN_HEAD STH , SA_TRAN_ITEM STI , (SELECT STORE, DAY, TRAN_SEQ_NO, ITEM_SEQ_NO, SUM(QTY*UNIT_DISCOUNT_AMT) DISC FROM SA_TRAN_DISC GROUP BY STORE, DAY, TRAN_SEQ_NO, ITEM_SEQ_NO) STD , SA_STORE_DAY SD WHERE STH.TRAN_SEQ_NO = STI.TRAN_SEQ_NO AND STH.STORE = STI.STORE AND STH.DAY = STI.DAY AND STI.TRAN_SEQ_NO = STD.TRAN_SEQ_NO(+) AND STI.STORE = STD.STORE(+) AND STI.DAY = STD.DAY(+) AND STI.ITEM_SEQ_NO = STD.ITEM_SEQ_NO(+) AND STH.STORE_DAY_SEQ_NO = SD.STORE_DAY_SEQ_NO AND BUSINESS_DATE BETWEEN '01-Feb-09' AND '28-Feb-09' AND STH.STORE = SD.STORE AND STH.STATUS = 'P' AND STH.TRAN_TYPE IN ('SALE', 'RETURN', 'EEXCH') AND STI.ITEM_TYPE != 'GCN' GROUP BY SD.BUSINESS_DATE,SD.STORE, sTH.TRAN_SEQ_NO, sti.item ORDER BY SD.BUSINESS_DATE,SD.STORE, sTH.TRAN_SEQ_NO, sti.item) Q1, (SELECT T.DAY_DT DAY_DT,L.LOC_IDNT RDW_STORE,S.TRAN_IDNT,P.ITEM_IDNT, SUM(NVL(F_SLS_AMT,0)) - SUM(NVL(F_RTRN_AMT,0)) RDW_AMTfrom RDW12DEV.SLS_ITEM_LM_DM@RMSTORDW S, RDW12DEV.PROD_ITEM_DM@RMSTORDW P, RDW12DEV.TIME_DAY_DM@RMSTORDW T, RDW12DEV.ORG_LOC_DM@RMSTORDW LWHERE P.ITEM_KEY = S.ITEM_KEY and S.LOC_KEY = L.LOC_KEY AND T.DAY_DT BETWEEN '01-Feb-09' AND '28-Feb-09' and T.DAY_IDNT = S.DAY_IDNTGROUP BY T.DAY_DT, L.LOC_IDNT, S.TRAN_IDNT, P.ITEM_IDNTORDER BY T.DAY_DT,L.LOC_IDNT,S.TRAN_IDNT,P.ITEM_IDNT)Q2where q1.BUSINESS_DATE = q2.DAY_DT(+) and q1.store = q2.RDW_STORE(+) and q1.tran_seq_no = q2.tran_idnt(+) and Q1.RESAITEM = Q2.ITEM_IDNT(+) and (((q1.RESAVALUE-q2.RDW_AMT)!=0) or Q2.ITEM_IDNT is null) ;