BEGIN;
SELECT C.style_product
FROM m_product_catalog_preparation A
JOIN m_product B ON B.product_code =A.product_code
JOIN m_product_custom C ON C.product_id =B.product_id
WHERE catalog_code ='K0063'
GROUP BY C.style_product
ORDER BY C.style_product;
-- list style

SELECT C.style_product,COUNT(1)
FROM m_product_catalog_preparation A
JOIN m_product B ON B.product_code =A.product_code
JOIN m_product_custom C ON C.product_id =B.product_id
WHERE catalog_code ='K0063'
GROUP BY C.style_product
ORDER BY C.style_product;
-- cek style product di count

SELECT flg_lanjut_catalog,COUNT(1)
FROM m_product_catalog_preparation
WHERE catalog_code= 'K0063'
GROUP BY flg_lanjut_catalog
-- tampilin supplier code, brand code,flg_lanjut

SELECT flg_consign,COUNT(1)
FROM m_product_catalog_preparation
WHERE catalog_code= 'K0063'
GROUP BY flg_consign


SELECT supplier_code,brand_code,product_catalog_code,product_code,flg_lanjut_catalog
FROM m_product_catalog_preparation
WHERE po_id=-99
AND catalog_code='K0063'
AND flg_launching ='N'
AND flg_consign = 'Y'
ORDER BY product_catalog_code
-- ada 1 yang masih po_id -99

-- yang belum di terima ( gak ada di product balance )
SELECT A.supplier_code,A.brand_Code,A.product_catalog_code,A.product_code,C.style_product,flg_lanjut_catalog
FROM m_product_catalog_preparation A
JOIN m_product B ON A.product_code =B.product_code
JOIN m_product_custom C ON C.product_id =B.product_id
WHERE catalog_code ='K0063'
AND B.product_id NOT IN(
    SELECT product_id
    FROM in_product_balance
)ORDER BY flg_lanjut_catalog,A.product_catalog_code
-- ada 356 yang blom terima

SELECT A.product_code,SUM(C.qty),flg_lanjut_catalog,style_product
FROM m_product_catalog_preparation A
JOIN m_product B ON A.product_code = B.product_code
JOIN in_product_balance_stock C ON C.product_id =B.product_id
JOIN m_product_custom D ON D.product_id =B.product_id
WHERE A.catalog_code = 'K0063'
GROUP BY A.product_code,flg_lanjut_catalog,style_product
HAVING SUM(C.qty)<=0;
-- ada 106 barang yang gak ada stock


SELECT A.product_code,SUM(C.qty),flg_lanjut_catalog,style_product
FROM m_product_catalog_preparation A
JOIN m_product B ON A.product_code = B.product_code
JOIN in_product_balance_stock C ON C.product_id =B.product_id
JOIN m_product_custom D ON D.product_id =B.product_id
WHERE A.catalog_code = 'K0063'
GROUP BY A.product_code,flg_lanjut_catalog,style_product
HAVING SUM(C.qty)>0;
-- ada 3130 barang yang ada stock



SELECT COUNT(1),'N'
FROM m_product_catalog_preparation
WHERE catalog_code='K0063'
AND flg_discontinue = 'N'
UNION
SELECT COUNT(1),'Y'
FROM m_product_catalog_preparation
WHERE catalog_code='K0063'
AND flg_discontinue = 'Y'
-- cek berapa" yang discontinue & continue

SELECT * FROM m_product_catalog_preparation WHERE flg_discontinue ='Y' AND catalog_code ='K0063' ORDER BY product_catalog_code; -- ada 133 yang discontinue
END;