brita acara stocker

=============================================================================
--- QUERY SUMMARY
============================================================================
WITH tt_stock_opname AS (
                        SELECT A.stock_opname_period_id, 0 AS qty_stock_taking, SUM(A.qty_adj_system) AS qty_system, 0 AS amount_journal_trx,
                        COUNT(DISTINCT B.product_id) AS total_product, C.flg_buy_konsinyasi, B.product_code
                        FROM stock_opname_planning_adjustment_detail A
                        LEFT JOIN m_product B ON UPPER(A.product_code) = UPPER(B.product_code)
                        LEFT JOIN m_product_custom C ON B.product_id = C.product_id
                        WHERE A.stock_opname_period_id = 12
                            AND A.qty_adj_system <> 0
                           AND A.flg_system = 'Y'
                            AND C.flg_buy_konsinyasi = 'Y'
                        GROUP BY A.stock_opname_period_id, A.product_code, C.flg_buy_konsinyasi, B.product_code
                        UNION
                        SELECT A.stock_opname_period_id, SUM(qty_adj_final) AS qty_stock_taking, 0 AS qty_system, 0 AS amount_journal_trx,
                        COUNT(DISTINCT B.product_id) AS total_product, C.flg_buy_konsinyasi, B.product_code
                        FROM stock_opname_planning_adjustment_detail A
                        LEFT JOIN m_product B ON UPPER(A.product_code) = UPPER(B.product_code)
                        LEFT JOIN m_product_custom C ON B.product_id = C.product_id
                        WHERE A.stock_opname_period_id = 12
                        AND A.qty_adj_final <> 0
                            AND A.flg_system = 'N'
                            AND C.flg_buy_konsinyasi = 'Y'
                        GROUP BY A.stock_opname_period_id, A.product_code, C.flg_buy_konsinyasi, B.product_code)
                    SELECT SUM(A.total_product) AS total_product, SUM(A.qty_system) AS qty_system, SUM(A.qty_stock_taking) AS qty_fisik,
                    SUM(A.qty_stock_taking - A.qty_system) AS selisih, (SUM(A.qty_stock_taking - A.qty_system)*SUM(A.amount_journal_trx)) AS total_nilai,
                    A.flg_buy_konsinyasi
                    FROM tt_stock_opname A
                    WHERE A.stock_opname_period_id = 12
                    GROUP BY A.flg_buy_konsinyasi

======================================
query detail
========================================
 WITH tt_stock_opname AS (
            SELECT A.stock_opname_period_id, qty_fisik as qty_stock_taking, qty_system_snapshot as qty_system, 0 AS amount_journal_trx,
            1 as total_product, C.flg_buy_konsinyasi, UPPER(B.product_code) as product_code
            FROM stock_opname_planning_adjustment A
            LEFT JOIN m_product B ON UPPER(A.product_code) = UPPER(B.product_code)
            LEFT JOIN m_product_custom C ON B.product_id = C.product_id
            WHERE A.stock_opname_period_id = 12
               AND A.qty_adj_final <> 0
               AND C.flg_buy_konsinyasi = 'Y'
                    ), stock_opname_generate_no_form AS (
                        SELECT b.product_code, a.form_no, a.stock_taker_name , a.stock_opname_period_id
                        FROM stock_opname_generate a
                        INNER JOIN stock_opname_generate_item b ON a.stock_opname_generate_id = b.stock_opname_generate_id
                        WHERE a.stock_opname_period_id = 12
                        GROUP BY b.product_code, a.form_no, a.stock_taker_name, a.stock_opname_period_id
                    )
                SELECT A.product_code, string_agg(DISTINCT CAST(B.form_no AS character varying), ',') AS form_no,
                    string_agg(DISTINCT B.stock_taker_name, ',') AS stock_taker,
                    A.qty_system ,
                    A.qty_stock_taking AS qty_fisik,
                    A.qty_stock_taking - A.qty_system AS selisih,
                    A.flg_buy_konsinyasi
                FROM tt_stock_opname A
                LEFT OUTER JOIN stock_opname_generate_no_form B ON A.stock_opname_period_id = B.stock_opname_period_id
                    AND UPPER(A.product_code) = UPPER(B.product_code)
                WHERE A.stock_opname_period_id = 12
                GROUP BY A.flg_buy_konsinyasi, A.product_code, A.qty_system, A.qty_stock_taking, A.flg_buy_konsinyasi