查看所有库存

SELECT

SQ.LOCATION_ID as x_location_id,

PP.ID AS X_PRODUCT_ID,

PT.PRODUCT_BRAND_ID AS X_BRAND_ID,

SQ.QUANTITY - SQ.RESERVED_QUANTITY AS X_AVAILABLE,

SQ.RESERVED_QUANTITY AS X_RESERVED_QUANTITY,

SQ.QUANTITY AS X_QUANTITY,

PP.BARCODE AS X_BARCODE,

SIZE_NAME.SIZE AS X_SIZE,

PT.NAME ->> 'zh_CN' AS X_PRODUCT_NAME

FROM PRODUCT_PRODUCT PP

LEFT JOIN STOCK_QUANT SQ ON PP.ID = SQ.PRODUCT_ID

LEFT JOIN STOCK_LOCATION SL ON SQ.LOCATION_ID = SL.ID

LEFT JOIN PRODUCT_TEMPLATE PT ON PP.PRODUCT_TMPL_ID = PT.ID

LEFT JOIN

(SELECT PVC.PRODUCT_PRODUCT_ID,

PAV.NAME ->> 'zh_CN' SIZE

FROM PRODUCT_VARIANT_COMBINATION PVC

JOIN PRODUCT_TEMPLATE_ATTRIBUTE_VALUE PTAV ON PTAV.ID = PVC.PRODUCT_TEMPLATE_ATTRIBUTE_VALUE_ID

JOIN PRODUCT_ATTRIBUTE_VALUE PAV ON PTAV.PRODUCT_ATTRIBUTE_VALUE_ID = PAV.ID

WHERE PAV.ATTRIBUTE_ID = 99) AS SIZE_NAME ON SIZE_NAME.PRODUCT_PRODUCT_ID = PP.ID

WHERE (SL.USAGE = 'internal' OR SQ.LOCATION_ID IS NULL) and pp.active = True