ERP_CHECK_KPS_REPORT_SALES_SUMMARY_6

BEGIN

    SELECT E.ref_id, COUNT(E.ref_id)
    FROM sl_so_balance_invoice A
    INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
    INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
    INNER JOIN m_product_custom B ON E.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
    LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = 10
        AND D.doc_date BETWEEN '20161201' AND '20161231'
        AND A.ou_id = 10
        AND B.flg_buy_konsinyasi = 'Y'
        AND D.doc_type_id = 311
        AND A.do_receipt_item_id = -99
        AND (F.from_manual = 'N' OR F.from_manual IS NULL)
    GROUP BY E.ref_id
    HAVING COUNT(E.ref_id) > 1

    SELECT * FROM pu_po_balance_item_consignment_sold WHERE so_item_id = 16734610

    select column_name from information_schema.columns where table_name='pu_po_item';

    SELECT * FROM pu_po_item  WHERE po_item_id IN (10405, 19702)

    --SELECT SUM(A.qty_dlv_so)
    SELECT C.supplier_id, E.ref_id, f_get_doc_desc(D.doc_type_id), D.doc_type_id, D.do_id , D.doc_no,
           D.doc_date, E.product_id, f_get_product_code(E.product_id), C.supplier_product_code,
           f_get_product_name(E.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin--, SUM(E.qty_dlv_int)
    FROM sl_so_balance_invoice A
    INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
    INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
    INNER JOIN m_product_custom B ON E.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
    LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = 10
        AND D.doc_date BETWEEN '20161201' AND '20161231'
        AND A.ou_id = 10
        AND B.flg_buy_konsinyasi = 'Y'
        AND D.doc_type_id = 311
        AND A.do_receipt_item_id = -99
        AND (F.from_manual = 'N' OR F.from_manual IS NULL)
        AND E.ref_id = 16734610
    GROUP BY C.supplier_id, E.ref_id, D.doc_type_id, D.do_id, D.doc_no, D.doc_date, E.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin



    SELECT C.supplier_id, E.ref_id, f_get_doc_desc(D.doc_type_id), D.doc_type_id, D.do_id , D.doc_no,
           D.doc_date, E.product_id, f_get_product_code(E.product_id), C.supplier_product_code,
           f_get_product_name(E.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
           F.discount, F.margin_supp_correction, F.sold_price_after_margin--, SUM(E.qty_dlv_int)
    SELECT E.ref_id, SUM(A.qty_dlv_so)
    SELECT SUM(A.qty_dlv_so)
    --SELECT E.ref_id, COUNT(E.ref_id)--,  f_get_partner_name(C.supplier_id),
    FROM sl_so_balance_invoice A
    INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
    INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
    INNER JOIN m_product_custom B ON E.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
    --LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = 10
        AND D.doc_date BETWEEN '20170201' AND '20170231'
        AND A.ou_id = 10
        AND B.flg_buy_konsinyasi = 'Y'
        AND D.doc_type_id = 311
        AND A.do_receipt_item_id = -99
        --AND F.from_manual IS NULL
        --AND (F.from_manual = 'N')
        --AND E.ref_id = 16734610
    GROUP BY E.ref_id--, C.supplier_id
    HAVING COUNT(E.ref_id) > 1
    GROUP BY C.supplier_id, E.ref_id, D.doc_type_id, D.do_id, D.doc_no, D.doc_date, E.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin

    SELECT * FROM pu_po_balance_item_consignment_sold WHERE so_item_id = 16744664

    SELECT so_id FROM sl_so_item WHERE so_item_id IN(
    SELECT so_id, so_item_id FROM sl_so_item WHERE so_item_id IN(
        SELECT E.ref_id
        --SELECT SUM(A.qty_dlv_so)
        FROM sl_so_balance_invoice A
        INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
        INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
        INNER JOIN m_product_custom B ON E.product_id = B.product_id
        INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
        LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
        WHERE A.tenant_id = 10
            AND D.doc_date BETWEEN '20170201' AND '20170231'
            AND A.ou_id = 10
            AND B.flg_buy_konsinyasi = 'Y'
            AND D.doc_type_id = 311
            AND A.do_receipt_item_id = -99
            AND F.from_manual IS NULL
        GROUP BY E.ref_id
    )
    --GROUP BY so_id
    ORDER BY so_id


    SELECT f_get_partner_name(partner_id) FROM sl_so WHERE so_id IN(
        --SELECT so_id FROM sl_so_item WHERE so_item_id IN(
        SELECT so_id,so_item_id FROM sl_so_item WHERE so_item_id IN(
            SELECT E.ref_id
            --SELECT SUM(A.qty_dlv_so)
            FROM sl_so_balance_invoice A
            INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
            INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
            INNER JOIN m_product_custom B ON E.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
            LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = 10
                AND D.doc_date BETWEEN '20170301' AND '20170331'
                AND A.ou_id = 10
                AND B.flg_buy_konsinyasi = 'Y'
                AND D.doc_type_id = 311
                AND A.do_receipt_item_id = -99
                AND F.from_manual IS NULL
            GROUP BY E.ref_id
        )
        GROUP BY so_id
    )


    SELECT f_get_partner_name(partner_id) FROM sl_so WHERE so_id = 4477309


    SELECT E.ref_id, COUNT(E.ref_id)
    SELECT SUM (A.qty_dlv_so)
    FROM sl_so_balance_invoice A
    INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
    INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
    INNER JOIN m_product_custom B ON E.product_id = B.product_id
    INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
    LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
    WHERE A.tenant_id = 10
        AND D.doc_date BETWEEN '20170301' AND '20170331'
        AND A.ou_id = 10
        AND B.flg_buy_konsinyasi = 'Y'
        AND D.doc_type_id = 311
        AND A.do_receipt_item_id = -99
    GROUP BY E.ref_id
    HAVING COUNT(E.ref_id) > 2

    SELECT f_get_doc_desc(ref_doc_type_id), SUM(qty_dlv_so) AS qty
        FROM sl_so_balance_invoice
        WHERE ref_doc_date BETWEEN '20170201' AND '20170231'
        AND do_receipt_item_id = -99
        GROUP BY ref_doc_type_id
        UNION
        SELECT 'DO Receipt', SUM(qty_dlv_so)
        FROM sl_so_balance_invoice
        WHERE ref_doc_date BETWEEN '20170201' AND '20170231'
        AND do_receipt_item_id <> -99
        GROUP BY ref_doc_type_id

    SELECT * FROM pu_po_item WHERE po_item_id IN(
    SELECT f_get_product_code(product_id) FROM pu_po_item WHERE po_item_id IN(
    SELECT f_get_product_code(product_id) FROM sl_so_item WHERE so_item_id IN(
        --SELECT * FROM pu_po_balance_item_consignment_sold WHERE so_item_id IN(
        SELECT so_item_id FROM pu_po_balance_item_consignment_sold WHERE so_item_id IN(
            SELECT E.ref_id
            FROM sl_so_balance_invoice A
            INNER JOIN sl_do D ON A.ref_id = D.do_id AND D.doc_type_id = A.ref_doc_type_id AND D.ref_id = A.so_id
            INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.ref_item_id = E.do_item_id
            INNER JOIN m_product_custom B ON E.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON E.product_id = C.product_id
            LEFT JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = 10
                AND D.doc_date BETWEEN '20170301' AND '20170331'
                AND A.ou_id = 10
                AND B.flg_buy_konsinyasi = 'Y'
                AND D.doc_type_id = 311
                AND A.do_receipt_item_id = -99
                --AND F.from_manual IS NULL
                --AND (F.from_manual = 'N' OR F.from_manual IS NULL)
                --AND E.ref_id = 16734610
            GROUP BY E.ref_id--, C.supplier_id
            HAVING COUNT(E.ref_id) > 1
        )
        --ORDER BY so_item_id
        --GROUP BY po_item_sold_id
        --GROUP BY po_item_id
    )
    GROUP BY product_id



ROLLBACK




BEGIN

    SELECT r_sales_summary_accounting('QWERTY', 10, -1, -1, '20170503010101', 10, '20170201', '20170231', -99, 'Y')

    SELECT product_code, (sold_price_after_margin * SUM(qty_so)) AS sold_price_after_margin_multiply_quantity
        FROM tr_report_sales_supplier_for_sales_summary
        WHERE session_id = 'QWERTY'
            AND tenant_id = 10
            AND transaction_type = 'Delivery Order'
        GROUP BY product_code, sold_price_after_margin
        HAVING (sold_price_after_margin * SUM(qty_so)) <> 0
        ORDER BY product_code
        GROUP BY transaction_type, doc_no, doc_date, payment_method, product_code,
           supplier_product_code, product_name, product_style, color, psize, normal_price,
           sold_price, discount, margin_supp, sold_price_after_margin, partner_id
        ORDER BY supplier_name, doc_date, doc_no;

     SELECT transaction_type, doc_no, doc_date, payment_method, product_code,
           supplier_product_code, product_name, product_style, color, psize, normal_price,
           sold_price, discount, margin_supp, sold_price_after_margin, SUM(qty_so) AS qty_so, (sold_price_after_margin * SUM(qty_so)) AS sold_price_after_margin_multiply_quantity,
           f_get_partner_name(partner_id) AS supplier_name, f_get_partner_code(partner_id) AS supplier_code, 'N' AS flg_pkp ,
           (sold_price * SUM(qty_so)) AS sub_total,ROUND((sold_price * SUM(qty_so))/1.1) as dpp_terjual, ((sold_price * SUM(qty_so))-ROUND((sold_price * SUM(qty_so))/1.1)) as ppn_terjual,
           ROUND(sold_price/1.1) AS sold_price_dpp
        FROM tr_report_sales_supplier_for_sales_summary
        WHERE session_id = 'QWERTY'
            AND tenant_id = 10
            AND transaction_type = 'Delivery Order'
        GROUP BY transaction_type, doc_no, doc_date, payment_method, product_code,
           supplier_product_code, product_name, product_style, color, psize, normal_price,
           sold_price, discount, margin_supp, sold_price_after_margin, partner_id
    HAVING (sold_price_after_margin * SUM(qty_so)) < 0

ROLLBACK


BEGIN

    SELECT r_inventory_summary_mutation_monthly_by_product_code('QWERTY', 10, -1, -1, '20170503010101', 10, '201702', '')

    SELECT f_get_product_code(A.product_id) AS product_code,
            SUM(A.stock_sales) AS stock_sales
        FROM tt_mutasi_nilai_persediaan A
        WHERE A.session_id = 'QWERTY'
        GROUP BY A.product_id
        HAVING SUM(A.stock_sales) <> 0
        ORDER BY  f_get_product_code(A.product_id)