Код:
SELECT
C.ARTICLE AS PRODUCT_ID,
SL.ID AS STORE_ID,
'' AS Company_Id,
'' AS Network_Id,
C.SHORTNAME AS PRODUCT_DESCRIPTION,
contr.POSTID AS SUPPLIER_ID,
contr.POSTNAME AS SUPPLIER_DESCRIPTION,
0 MANDATORY_ASSORTMENT, -- группа А
0 AS BRAND_TYPE,
'' AS CUSTOMER_INFO_1,
'' AS CUSTOMER_INFO_2,
'' AS CUSTOMER_INFO_3,
'' AS CUSTOMER_INFO_4,
'' AS PRODUCT_TRACKING,
M.ID AS PRODUCT_UNIT_CODE,
0 AS SKU_PACKAGING,
1 AS PRODUCT_UNIT_VALUE,
CLAS.ID_CAT_N1 AS ID_CAT_N1,
CLAS.ID_CAT_N2 AS ID_CAT_N2,
CLAS.ID_CAT_N3 AS ID_CAT_N3,
CLAS.ID_CAT_N4 AS ID_CAT_N4,
CLAS.ID_CAT_N5 AS ID_CAT_N5,
(CASE
WHEN contrRC.POSTID = 2679 THEN 1
ELSE 2
END) AS DSD_WAREHOUSE,
'' AS ORDER_MINIMUM_QUANTITY,
'' AS PAYMENT_TERM,
'' AS PRODUCT_ID2,
'' AS FACING_QUANTITY,
'' AS FACING_SIZE,
'' AS FACING_LOCATION,
'' AS SAFETY_STOCK,
'' AS LINEAR_STOCK,
PROZN.PRICE - PZAK.PRICE AS MARGIN_VALUE,
CP.PROPVAL AS LEAD_TIME_REPLENISHMENT,
'' AS AVERAGE_BEST_BEFORE_DATE,
TORC.QUANTITY AS STOCK_ADJUSTMENT,
(select supermag.SMGETPRICEONDATERL(C.ARTICLE,PROZN.PRICETYPE,SL.ID,TO_DATE('01.08.13','DD.MM.YY')) from dual) AS SALES_PRICE_START,
(select supermag.SMGETPRICEONDATERL(C.ARTICLE,PROZN.PRICETYPE,SL.ID,TO_DATE('20.08.13','DD.MM.YY')) from dual) AS SALES_PRICE_END,
SALE.SALE / SALE.QUANTITY AS AVERAGE_SALES_PRICE,
TAX.RATE AS VAT,
(CASE
WHEN startgoods.QUANTITY > 0 THEN startgoods.QUANTITY
ELSE 0
END) AS STOCK_QUANTITY_START,
(CASE
WHEN endgoods.QUANTITY > 0 THEN endgoods.QUANTITY
ELSE 0
END) AS STOCK_QUANTITY_END,
(CASE
WHEN (startgoods.QUANTITY +endgoods.QUANTITY) >0 THEN (startgoods.QUANTITY +endgoods.QUANTITY) /2
ELSE 0
END) AS AVERAGE_STOCK_QUANTITY,
PRIHOD.QUANTITY AS QUANTITY_RECEIVE_PERIODE,
SALE.QUANTITY AS QUANTITY_SALES_PERIODE,
SALE.MAXQUANTITY AS HIGHEST_DAY_SALE_QUANTITY,
'' AS MINIMUM_SHELVE_SIZE,
'' AS QUANTITY_SALES_FORECAST_PERIOD,
'' AS QUANTITY_SALES_FC_NEXT_PERIOD,
'' AS QUANTITY_SALES_FT_NEXT_PERIOD,
MADATES.DATEBEGIN AS PROMOTION_START_DATE,
MADATES.DATEEND AS PROMOTION_END_DATE,
MAKEY.pKEY AS PROMOTION_ID,
1 AS PERMANENT_TEMPORARY,
PZAK.PRICE AS MINIMUM_COST_PRICE,
-- если по неделям
'' AS F_S_Q_END_FIRST_WEEK,
'' AS F_S_Q_END_SECOND_WEEK,
'' AS F_S_Q_END_THIRD_WEEK,
'' AS F_S_Q_END_FOURTH_WEEK,
'' AS F_S_Q_END_FIFTH_WEEK,
'' AS Q_RECEIVED_FIRST_WEEK,
'' AS Q_RECEIVED_SECOND_WEEK,
'' AS Q_RECEIVED_THIRD_WEEK,
'' AS Q_RECEIVED_FOURTH_WEEK,
'' AS Q_RECEIVED_FIFTH_WEEK,
'' AS Q_SOLD_FIRST_WEEK,
'' AS Q_SOLD_SECOND_WEEK,
'' AS Q_SOLD_THIRD_WEEK,
'' AS Q_SOLD_FOURTH_WEEK,
'' AS Q_SOLD_FIFTH_WEEK,
'' AS PERIOD_ORDER_MODE,
'' AS FIRST_WEEK_ORDER_MODE,
'' AS SECOND_WEEK_ORDER_MODE,
'' AS THIRD_WEEK_ORDER_MODE,
'' AS FOURTH_WEEK_ORDER_MODE,
'' AS FIFTH_WEEK_ORDER_MODE,
-- конец по неделям
0 AS REBATE_VALUE,
2 AS REBATE_INCLUDED_IN_COST_OR_NOT,
'' AS Q_STOCK_TARGET_DAY,
'' AS SEASONAL_PRODUCT,
'' AS SEASON_START_DATE,
'' AS SEASON_END_DATE,
'' AS PRODUCT_ALTERNATIVE_CODE,
reviz.QUANTITY AS STOCK_CORRECTION_QUANTITY,
'' AS PRESENTATION_STOCK,
1 AS NORMAL_BUY_SOLD,
0 AS IMPORT
FROM
SUPERMAG.SMCARD C
RIGHT JOIN SUPERMAG.SMSTORELOCATIONS SL ON SL.ACCEPTED=1 AND SL.IDCLASS=1 AND SL.ID NOT IN (6)
LEFT JOIN SUPERMAG.SAMeasurement M ON M.ID=C.IDMEASUREMENT
LEFT JOIN (SELECT
S.ARTICLE,
COL.LOCATIONID as LOCATIONID ,
CI.ID AS POSTID,
CI.NAME AS POSTNAME
FROM SUPERMAG.SMDOCUMENTS D,SUPERMAG.SMSPEC S ,SUPERMAG.SMCLIENTINFO CI,SUPERMAG.SMCONTRACTLOCATIONS COL, SUPERMAG.SMCONTRACTS SMC
WHERE
S.DOCID = D.ID
AND S.DOCTYPE = D.DOCTYPE
AND D.DOCTYPE = 'CO'
AND D.CLIENTINDEX = CI.ID
AND COL.DOCTYPE = D.DOCTYPE
AND COL.ID = D.ID
AND D.DOCSTATE IN (2,3)
AND SMC.ID = D.ID
AND SMC.DOCTYPE = D.DOCTYPE
AND CI.ID not in (2679)
AND (SMC.ENDSAT >= sysdate or SMC.ENDSAT IS NULL )
AND SMC.BEGINSAT < sysdate
ORDER BY D.CREATEDAT ASC) contr ON contr.LOCATIONID = SL.ID AND contr.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT
S.ARTICLE,
COL.LOCATIONID as LOCATIONID ,
CI.ID AS POSTID
FROM SUPERMAG.SMDOCUMENTS D,SUPERMAG.SMSPEC S ,SUPERMAG.SMCLIENTINFO CI,SUPERMAG.SMCONTRACTLOCATIONS COL, SUPERMAG.SMCONTRACTS SMC
WHERE
S.DOCID = D.ID
AND S.DOCTYPE = D.DOCTYPE
AND D.DOCTYPE = 'CO'
AND D.CLIENTINDEX = CI.ID
AND COL.DOCTYPE = D.DOCTYPE
AND COL.ID = D.ID
AND D.DOCSTATE IN (2,3)
AND SMC.ID = D.ID
AND SMC.DOCTYPE = D.DOCTYPE
AND CI.ID in (2679)
AND (SMC.ENDSAT >= sysdate or SMC.ENDSAT IS NULL )
ORDER BY D.CREATEDAT ASC) contrRC ON contrRC.LOCATIONID = SL.ID AND contrRC.ARTICLE = C.ARTICLE
LEFT JOIN (
select
C.ARTICLE AS ARTICLE,
SUBSTR(CL.TREE,0,INSTR(CL.TREE,'.',1,1)-1) AS ID_CAT_N1,
SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,1)+1,INSTR(CL.TREE,'.',1,2)-(INSTR(CL.TREE,'.',1,1)+1) ) AS ID_CAT_N2,
SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,2)+1,INSTR(CL.TREE,'.',1,3)-(INSTR(CL.TREE,'.',1,2)+1) ) AS ID_CAT_N3,
SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,3)+1,INSTR(CL.TREE,'.',1,4)-(INSTR(CL.TREE,'.',1,3)+1) ) AS ID_CAT_N4,
SUBSTR(CL.TREE,INSTR(CL.TREE,'.',1,4)+1,INSTR(CL.TREE,'.',1,5)-(INSTR(CL.TREE,'.',1,4)+1) ) AS ID_CAT_N5,
CL.NAME AS NAME_CAT
from SUPERMAG.SACARDCLASS CL, SUPERMAG.SMCARD C
WHERE
C.IDCLASS = CL.ID) CLAS ON CLAS.ARTICLE = C.ARTICLE
LEFT JOIN SUPERMAG.SMPRICES PZAK ON PZAK.ARTICLE = C.ARTICLE AND PZAK.STORELOC = SL.ID AND PZAK.PRICETYPE=1
LEFT JOIN SUPERMAG.SMPRICES PROZN ON PROZN.ARTICLE = C.ARTICLE AND PROZN.STORELOC = SL.ID AND PROZN.PRICETYPE > 1 AND PROZN.PRICETYPE <> 24
LEFT JOIN SUPERMAG.SMCLIENTPROPERTIES CP ON CP.IDCLIENT=CONTR.POSTID AND CP.PROPID=TO_CHAR('ZAKAZ'||TO_CHAR(SL.ID))
LEFT JOIN (SELECT
S.ARTICLE AS ARTICLE,
D.LOCATIONFROM AS LOCATIONFROM,
SUM(S.QUANTITY) AS QUANTITY
FROM SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S
WHERE
D.ID = S.DOCID
AND D.DOCTYPE = S.DOCTYPE
AND D.DOCSTATE = 3
AND D.DOCTYPE = 'WO'
AND D.CLIENTINDEX = '2679'
AND D.CREATEDAT BETWEEN TO_DATE('01.08.13','DD.MM.YY') AND TO_DATE('20.08.13','DD.MM.YY')
GROUP BY S.ARTICLE , D.LOCATIONFROM) TORC ON TORC.LOCATIONFROM = SL.ID AND TORC.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT
ARTICLE,
SUM (QUANTITY) AS QUANTITY,
SUM (QUANTITY * ITEMPRICE) AS SALE,
(CASE
WHEN MAX (QUANTITY) > 0 THEN MAX (QUANTITY)
ELSE 0
END) AS MAXQUANTITY,
(CASE
WHEN MAX (QUANTITY * ITEMPRICE) > 0 THEN MAX (QUANTITY * ITEMPRICE)
ELSE 0
END) AS MAXSALE,
(CASE
WHEN MIN (QUANTITY) > 0 THEN MIN (QUANTITY)
ELSE 0
END) AS MINQUANTITY,
(CASE
WHEN MIN (QUANTITY * ITEMPRICE) > 0 THEN MIN (QUANTITY * ITEMPRICE)
ELSE 0
END) AS MINSALE,
MX
FROM(
SELECT
S.ARTICLE AS ARTICLE,
S.ITEMPRICE AS ITEMPRICE,
(CASE
WHEN D.DOCTYPE = 'CS' THEN S.QUANTITY
ELSE S.QUANTITY*-1
END) AS QUANTITY,
(CASE
WHEN D.DOCTYPE = 'CS' THEN D.LOCATIONFROM
ELSE D.LOCATIONTO
END) AS MX
FROM SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S
WHERE
D.DOCTYPE = S.DOCTYPE
AND S.DOCID = D.ID
AND D.DOCTYPE IN ('CS','CR')
AND D.CREATEDAT BETWEEN TO_DATE('01.08.13','DD.MM.YY') AND TO_DATE('20.08.13','DD.MM.YY'))
GROUP BY MX,ARTICLE) SALE ON SALE.ARTICLE = C.ARTICLE AND SL.ID = SALE.MX
LEFT JOIN (select
TAX.ARTICLE AS ARTICLE,
(CASE
WHEN TAX.TAXGROUPID =2 then 10
WHEN TAX.TAXGROUPID =3 then 18
ELSE 0
END) AS RATE
from SUPERMAG.SMCARDTAX tax) TAX ON TAX.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT /*+ USE_MERGE(T1,G) */ G.ARTICLE,
G.STORELOC,
G.QUANTITY + T1.QUANTITY AS QUANTITY
FROM SUPERMAG.SMGOODS G
LEFT JOIN (SELECT ARTICLE,
MX,
SUM(QUANTITY) AS QUANTITY
FROM (SELECT S1.ARTICLE,
CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN S1.QUANTITY
ELSE S1.QUANTITY * -1 END QUANTITY,
CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN D1.LOCATIONTO
ELSE D1.LOCATIONFROM END MX
FROM SUPERMAG.SMDOCUMENTS D1,
SUPERMAG.SMSPEC S1
WHERE D1.DOCTYPE IN ('CR', 'WI', 'CS', 'WO')
AND D1.CREATEDAT >= TO_DATE ('01.08.13', 'DD.MM.YY')
AND S1.DOCID = D1.ID
AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) T1
ON T1.ARTICLE = G.ARTICLE
AND T1.MX = G.STORELOC) startgoods ON startgoods.ARTICLE = C.ARTICLE AND startgoods.STORELOC = SL.ID
LEFT JOIN (SELECT /*+ USE_MERGE(T1,G) */ G.ARTICLE,
G.STORELOC,
G.QUANTITY + T1.QUANTITY AS QUANTITY
FROM SUPERMAG.SMGOODS G
LEFT JOIN (SELECT ARTICLE,
MX,
SUM(QUANTITY) AS QUANTITY
FROM (SELECT S1.ARTICLE,
CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN S1.QUANTITY
ELSE S1.QUANTITY * -1 END QUANTITY,
CASE WHEN D1.DOCTYPE IN ('WI', 'CR') THEN D1.LOCATIONTO
ELSE D1.LOCATIONFROM END MX
FROM SUPERMAG.SMDOCUMENTS D1,
SUPERMAG.SMSPEC S1
WHERE D1.DOCTYPE IN ('CR', 'WI', 'CS', 'WO')
AND D1.CREATEDAT >= TO_DATE ('20.08.13', 'DD.MM.YY')
AND S1.DOCID = D1.ID
AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) T1
ON T1.ARTICLE = G.ARTICLE
AND T1.MX = G.STORELOC) endgoods ON endgoods.ARTICLE = C.ARTICLE AND endgoods.STORELOC = SL.ID
LEFT JOIN (SELECT ARTICLE,
MX,
SUM(QUANTITY) AS QUANTITY
FROM (SELECT S1.ARTICLE,
CASE WHEN D1.DOCTYPE = 'WO' THEN S1.QUANTITY *-1
ELSE S1.QUANTITY END QUANTITY,
CASE WHEN D1.DOCTYPE IN ('WI') THEN D1.LOCATIONTO
ELSE D1.LOCATIONFROM END MX
FROM SUPERMAG.SMDOCUMENTS D1,
SUPERMAG.SMSPEC S1
WHERE D1.DOCTYPE IN ('WI','WO')
AND D1.CREATEDAT >= TO_DATE ('01.08.13', 'DD.MM.YY')
AND D1.CREATEDAT <= TO_DATE ('20.08.13', 'DD.MM.YY')
AND S1.DOCID = D1.ID
AND S1.DOCTYPE = D1.DOCTYPE) GROUP BY ARTICLE, MX) PRIHOD ON PRIHOD.MX = SL.ID AND PRIHOD.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT
S.ARTICLE AS ARTICLE,
AA.LOCID AS MX,
TO_DATE(AC.DATEBEGIN,'DD.MM.YY' ) AS DATEBEGIN,
TO_DATE(AC.DATEEND,'DD.MM.YY' ) AS DATEEND
FROM
SUPERMAG.SMAUCTIONS AC,
SUPERMAG.SMSPEC S ,
SUPERMAG.SMAUCTIONATOMS AA
WHERE
AC.DOCTYPE = 'MA'
AND S.DOCTYPE = AC.DOCTYPE
AND S.DOCID = AC.ID
AND AA.AUCID = AC.ID
AND AA.DOCTYPE = AC.DOCTYPE
AND (LOWER(AC.NAME) LIKE '%акция%')
AND (AC.DATEEND >=TO_DATE('20.08.12','DD.MM.YY'))) MADATES ON MADATES.MX = SL.ID AND MADATES.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT
S.ARTICLE AS ARTICLE,
AA.LOCID AS MX,
(CASE
WHEN (LOWER(AC.NAME) LIKE '%акция%') THEN 4
WHEN (LOWER(AC.NAME) LIKE '%супер%') THEN 6
ELSE 0
END) AS pKEY
FROM
SUPERMAG.SMAUCTIONS AC,
SUPERMAG.SMSPEC S ,
SUPERMAG.SMAUCTIONATOMS AA
WHERE
AC.DOCTYPE = 'MA'
AND S.DOCTYPE = AC.DOCTYPE
AND S.DOCID = AC.ID
AND AA.AUCID = AC.ID
AND AA.DOCTYPE = AC.DOCTYPE
AND (AC.DATEBEGIN >=TO_DATE('01.08.12','DD.MM.YY')) AND(AC.DATEBEGIN <=TO_DATE('20.08.12','DD.MM.YY'))
) MAKEY ON MAKEY.MX = SL.ID AND MAKEY.ARTICLE = C.ARTICLE
LEFT JOIN (SELECT
ARTICLE,
MX,
SUM(QUANTITY) AS QUANTITY
FROM
(select
S.ARTICLE,
CASE WHEN D.DOCTYPE in ('WO','LA') THEN S.QUANTITY *-1
ELSE S.QUANTITY
END QUANTITY,
CASE
WHEN D.DOCTYPE IN ('WI','FA') THEN D.LOCATIONTO
ELSE D.LOCATIONFROM
END MX
from SUPERMAG.SMDOCUMENTS D, SUPERMAG.SMSPEC S
WHERE
--D.DOCTYPE
((D.OPCODE in (14,9) AND D.DOCTYPE in ('WO','WI')) or (D.DOCTYPE in ('FA','LA') AND D.DOCSTATE in (2,3) ))
AND D.DOCSTATE in (2,3)
AND D.ID = S.DOCID
AND D.DOCTYPE = S.DOCTYPE
AND D.CREATEDAT >= TO_DATE('01.08.12','DD.MM.YY')
AND D.CREATEDAT <= TO_DATE('20.08.12','DD.MM.YY')
) GRoUP BY ARTICLE,MX
) reviz ON reviz.MX = SL.ID AND reviz.ARTICLE = C.ARTICLE