ERP_CHECK)REPORT_SALES_SUMMARY_7

BEGIN

    SELECT * FROM pu_po_balance_item_consignment_sold_manual 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 '20170101' AND '20170131'
            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
        )

        SELECT * FROM in_summary_monthly_amount LIMIT 10

ROLLBACK



BEGIN

    SELECT * FROM m_product_custom WHERE product_id = 8138

    SELECT * FROM pu_po LIMIT 10

    SELECT A.po_id, A.product_id, COUNT(A.po_item_id) FROM pu_po_item A
    INNER JOIN m_product_custom B ON B.product_id = A.product_id
    INNER JOIN pu_po C ON C.po_id = A.po_id
    WHERE B.flg_buy_konsinyasi = 'Y'
        AND C.status_doc = 'R'
    GROUP BY A.po_id, A.product_id
    HAVING COUNT(po_item_id) > 1

    SELECT * FROM pu_po_item WHERE po_id IN (
        SELECT A.po_id FROM pu_po_item A
        INNER JOIN m_product_custom B ON B.product_id = A.product_id
        INNER JOIN pu_po C ON C.po_id = A.po_id
        WHERE B.flg_buy_konsinyasi = 'Y'
            AND C.status_doc NOT IN ('C', 'V')
        GROUP BY A.po_id, A.product_id
        HAVING COUNT(po_item_id) > 1
    )
    AND product_id IN(
        SELECT A.product_id FROM pu_po_item A
        INNER JOIN m_product_custom B ON B.product_id = A.product_id
        INNER JOIN pu_po C ON C.po_id = A.po_id
        WHERE B.flg_buy_konsinyasi = 'Y'
            AND C.status_doc NOT IN ('C', 'V')
        GROUP BY A.po_id, A.product_id
        HAVING COUNT(po_item_id) > 1
    )
    ORDER BY po_id, product_id

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



    SELECT * FROM pu_po_item WHERE po_id::character varying || ' ' || product_id::character varying IN (
        SELECT A.po_id::character varying || ' ' || A.product_id::character varying FROM pu_po_item A
        INNER JOIN m_product_custom B ON B.product_id = A.product_id
        INNER JOIN pu_po C ON C.po_id = A.po_id
        WHERE B.flg_buy_konsinyasi = 'Y'
            AND C.status_doc NOT IN ('C', 'V')
            AND A.ref_doc_type_id <> 106
            AND A.po_id NOT IN(
                SELECT X.ref_id FROM pu_po_rg_generated Z
                INNER JOIN pu_receive_goods X ON X.receive_goods_id = Z.ref_id
            )
        GROUP BY A.po_id, A.product_id
        HAVING COUNT(po_item_id) > 1
    )
    ORDER BY po_id, product_id

    SELECT A.po_id, A.product_id, A.po_id::character varying || ' ' || A.product_id::character varying FROM pu_po_item A
        INNER JOIN m_product_custom B ON B.product_id = A.product_id
        INNER JOIN pu_po C ON C.po_id = A.po_id
        WHERE B.flg_buy_konsinyasi = 'Y'
            AND C.status_doc NOT IN ('C', 'V')
            AND A.ref_doc_type_id <> 106
        GROUP BY A.po_id, A.product_id
        HAVING COUNT(po_item_id) > 1


    SELECT * FROM m_document_url WHERE doc_type_id = 106

    SELECT product_id, COUNT(po_item_id) FROM pu_po_item WHERE po_id = 1464
    GROUP BY product_id
    HAVING COUNT(po_item_id) > 1

    SELECT f_get_product_code(product_id), f_get_product_name(product_id), * FROM pu_po_item WHERE po_id =1464
    ORDER BY product_id

    SELECT f_get_warehouse_name(warehouse_id), f_get_partner_name(partner_id) , * FROM pu_po WHERE po_id =2077

    SELECT f_get_warehouse_name(warehouse_id), f_get_partner_name(partner_id) , * FROM pu_po WHERE po_id =537

    SELECT * FROM t_user WHERE user_id = 113

    SELECT f_get_product_code(product_id), * FROM m_product_custom WHERE flg_buy_konsinyasi = 'Y' AND product_id NOT IN(
        SELECT A.product_id FROM pu_po_item A
        INNER JOIN m_product_custom B ON B.product_id = A.product_id
        WHERE B.flg_buy_konsinyasi = 'Y'
    )
    AND f_get_product_code(product_id) LIKE '%CL019%'

    SELECT f_get_product_code(5169)

    SELECT * FROM pu_po ORDER BY po_id DESC LIMIT 1