ERP_KPS_CHECK_REPORT_SALES_SUMMARY_8

BEGIN


    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 '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
    GROUP BY E.ref_id
    HAVING COUNT(E.ref_id) > 1

    16741642

    16734610

    SELECT * FROM pu_po_balance_item_consignment_sold WHERE so_item_id = 16741642

    SELECT * FROM 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 doc_no, to_date(doc_date, 'YYYYMMDD'), po_item_id, line_no, A.po_id, f_get_product_code(product_id), f_get_product_name(product_id), qty_po, tax_name,
     CASE WHEN flg_tax_amount = 'Y' THEN 'Yes'
     ELSE 'No' END,
     nett_price_po
     FROM pu_po_item A
     INNER JOIN pu_po B ON B.po_id = A.po_id
     LEFT JOIN m_tax C ON C.tax_id = A.tax_id
     WHERE A.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 A.po_id, product_id