widana

f_update_balance_stock

Apr 17th, 2017
93
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_update_balance_stock(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader                  REFCURSOR := 'refHeader';
  6.     pRefDetail                  REFCURSOR := 'refDetail';
  7.     pSessionId                  ALIAS FOR $1;
  8.     pTenantId                   ALIAS FOR $2;
  9.     pUserId                     ALIAS FOR $3;
  10.     pRoleId                     ALIAS FOR $4;
  11.     pDatetime                   ALIAS FOR $5;
  12.     pOuId                       ALIAS FOR $6;
  13.     pStartDate                  ALIAS FOR $7;
  14.     pEndDate                    ALIAS FOR $8;
  15. --    pFlgConsignment               ALIAS FOR $9;
  16.    
  17. BEGIN
  18.    
  19.     DELETE FROM tr_summary_update_stock WHERE session_id = pSessionId;
  20.    
  21.     -- last closing ledger sebelum pStartDate
  22.     SELECT COALESCE(MAX(date_year_month), '') AS last_closing_gl INTO vLastClosingGeneralLedger
  23.     FROM m_admin_process_ledger
  24.     WHERE tenant_id = pTenantId AND
  25.         ou_id = pOuId AND
  26.         ledger_code = 'GL' AND
  27.         status_ledger = '1' AND
  28.         date_year_month <= SUBSTRING(pStartDate, 1, 6);
  29.    
  30.     -- tanggal awal untuk transaksi diantara tgl saldo awal sampai sebelum pStartDate
  31.     vStartDateLog = vLastClosingGeneralLedger || '01';
  32.     SELECT TO_CHAR(TO_DATE(pStartDate,'YYYYMMDD') + INTERVAL '-1 Day','YYYYMMDD') INTO vEndDateLog;
  33.    
  34.     IF pOuId <> vEmptyId THEN
  35.         vFilterOuId := ' AND A.ou_id = ' || pOuId;
  36.     END IF;
  37.    
  38.     -- insert from log product
  39. --    IF pFlgConsignment = 'N' THEN
  40.     -- insert from log product WHEN pFlgConsignment = 'N'
  41.         EXECUTE '
  42.        INSERT INTO tr_summary_update_stock(
  43.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  44.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  45.            warehouse_id, product_status, os_doc_qty
  46.        )
  47.        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
  48.                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
  49.                A.warehouse_id, A.product_status, 0
  50.        FROM (
  51.            SELECT A.product_id, B.product_code, B.product_name,
  52.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  53.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  54.                    A.warehouse_id, A.product_status
  55.            FROM in_log_product_balance_stock A
  56.            INNER JOIN m_product B ON A.product_id = B.product_id
  57.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  58.            WHERE A.doc_date BETWEEN $4 AND $5
  59.                AND A.ou_id = $3
  60.                AND C.flg_buy_konsinyasi = $6
  61.             UNION ALL
  62.            SELECT A.product_id, B.product_code, B.product_name,
  63.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  64.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  65.                    A.warehouse_id, A.product_status
  66.            FROM in_log_product_consignment_balance_stock A
  67.            INNER JOIN m_product B ON A.product_id = B.product_id
  68.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  69.            WHERE A.doc_date BETWEEN $4 AND $5
  70.                AND A.ou_id = $3
  71.                AND C.flg_buy_konsinyasi = $6 ) A
  72.            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  73.         USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vNo;
  74.        
  75. --    ELSE
  76.  
  77.     -- insert from log product WHEN pFlgConsignment = 'Y'
  78.         EXECUTE '
  79.        INSERT INTO tr_summary_update_stock(
  80.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  81.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  82.            warehouse_id, product_status, os_doc_qty
  83.        )
  84.        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
  85.                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
  86.                A.warehouse_id, A.product_status, 0
  87.        FROM (
  88.            SELECT A.product_id, B.product_code, B.product_name,
  89.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  90.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  91.                    A.warehouse_id, A.product_status
  92.            FROM in_log_product_balance_stock A
  93.            INNER JOIN m_product B ON A.product_id = B.product_id
  94.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  95.            WHERE A.doc_date BETWEEN $4 AND $5
  96.                AND A.ou_id = $3
  97.                AND C.flg_buy_konsinyasi = $6
  98.                AND A.doc_type_id <> 111
  99.             UNION ALL
  100.            SELECT A.product_id, B.product_code, B.product_name,
  101.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  102.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  103.                    A.warehouse_id, A.product_status
  104.            FROM in_log_product_consignment_balance_stock A
  105.            INNER JOIN m_product B ON A.product_id = B.product_id
  106.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  107.            WHERE A.doc_date BETWEEN $4 AND $5
  108.                AND A.ou_id = $3
  109.                AND C.flg_buy_konsinyasi = $6
  110.             ) A
  111.            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  112.         USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vYes;
  113.        
  114. --    END IF;
  115.    
  116.     -- update nilai saldo awal qty
  117. --    IF pFlgConsignment = 'N' THEN
  118.    
  119.     -- update nilai saldo awal qty WHEN pFlgConsignment = 'N'
  120.         WITH tt_in_summary_monthly_qty AS (
  121.             SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
  122.             FROM in_summary_monthly_qty
  123.             WHERE tenant_id = pTenantId
  124.                 AND doc_type_id = vEmptyId
  125.                 AND date_year_month = vLastClosingGeneralLedger
  126.                 AND ou_id = pOuId
  127.             GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
  128.         )
  129.         UPDATE tr_summary_update_stock A
  130.         SET last_balance_qty = B.qty,
  131.             expectation = B.qty
  132.         FROM tt_in_summary_monthly_qty B
  133.         WHERE A.tenant_id = B.tenant_id
  134.             AND A.product_id = B.product_id
  135.             AND A.product_balance_id = B.product_balance_id
  136.             AND A.warehouse_id = B.warehouse_id
  137.             AND A.product_status = B.product_status
  138.             AND A.session_id = pSessionId;
  139. --    ELSE
  140.  
  141.     -- update nilai saldo awal qty WHEN pFlgConsignment = 'Y'
  142.         WITH tt_in_summary_monthly_qty AS (
  143.             SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
  144.             FROM in_summary_monthly_qty_consignment
  145.             WHERE tenant_id = pTenantId
  146.                 AND doc_type_id = vEmptyId
  147.                 AND date_year_month = vLastClosingGeneralLedger
  148.                 AND ou_id = pOuId
  149.             GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
  150.         )
  151.         UPDATE tr_summary_update_stock A
  152.         SET last_balance_qty = B.qty,
  153.             expectation = B.qty
  154.         FROM tt_in_summary_monthly_qty B
  155.         WHERE A.tenant_id = B.tenant_id
  156.             AND A.product_id = B.product_id
  157.             AND A.product_balance_id = B.product_balance_id
  158.             AND A.warehouse_id = B.warehouse_id
  159.             AND A.product_status = B.product_status
  160.             AND A.session_id = pSessionId;
  161. --    END IF;
  162.    
  163.     -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi
  164.     IF pFlgConsignment = 'N' THEN
  165.    
  166.     -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'N'
  167.         EXECUTE '
  168.        INSERT INTO tr_summary_update_stock(
  169.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  170.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  171.            warehouse_id, product_status, os_doc_qty
  172.        )
  173.        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
  174.                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
  175.                A.warehouse_id, A.product_status, 0
  176.        FROM in_summary_monthly_qty A
  177.        INNER JOIN m_product B ON A.product_id = B.product_id
  178.        INNER JOIN m_product_custom C ON B.product_id = C.product_id
  179.        WHERE A.doc_type_id = $4
  180.                AND A.date_year_month = $5
  181.                AND A.ou_id = $3
  182.                AND C.flg_buy_konsinyasi = $6
  183.         AND NOT EXISTS(
  184.            SELECT 1 FROM tr_summary_update_stock D
  185.            WHERE A.tenant_id = D.tenant_id
  186.                AND A.product_id = D.product_id
  187.                AND A.product_balance_id = D.product_balance_id
  188.        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  189.         USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vNo;
  190.        
  191. --    ELSE
  192.        
  193.     -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'Y'
  194.         EXECUTE '
  195.        INSERT INTO tr_summary_update_stock(
  196.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  197.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  198.            warehouse_id, product_status, os_doc_qty
  199.        )
  200.        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
  201.                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
  202.                A.warehouse_id, A.product_status, 0
  203.        FROM in_summary_monthly_qty_consignment A
  204.        INNER JOIN m_product B ON A.product_id = B.product_id
  205.        INNER JOIN m_product_custom C ON B.product_id = C.product_id
  206.        WHERE A.doc_type_id = $4
  207.                AND A.date_year_month = $5
  208.                AND A.ou_id = $3
  209.                AND C.flg_buy_konsinyasi = $6
  210.         AND NOT EXISTS(
  211.            SELECT 1 FROM tr_summary_update_stock D
  212.            WHERE A.tenant_id = D.tenant_id
  213.                AND A.product_id = D.product_id
  214.                AND A.product_balance_id = D.product_balance_id
  215.        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  216.         USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vYes;
  217. --    END IF;
  218.    
  219.     IF pStartDate <> vStartDateLog THEN
  220.         -- update nilai saldo awal qty + log product
  221. --        IF pFlgConsignment = 'N' THEN
  222.             -- product non consignment
  223.             EXECUTE '
  224.            WITH tt_in_log_product_balance_stock AS (
  225.                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty, A.warehouse_id, A.product_status
  226.                FROM in_log_product_balance_stock A
  227.                INNER JOIN m_product_custom B ON A.product_id = B.product_id
  228.                WHERE A.doc_date BETWEEN $1 AND $2
  229.                    AND A.ou_id = $3
  230.                    AND B.flg_buy_konsinyasi = $4
  231.                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status  
  232.            )
  233.            UPDATE tr_summary_update_stock A
  234.            SET last_balance_qty = A.last_balance_qty + B.qty,
  235.                expectation = A.expectation + B.qty
  236.            FROM tt_in_log_product_balance_stock B
  237.            WHERE A.tenant_id = B.tenant_id
  238.                AND A.product_id = B.product_id
  239.                AND A.product_balance_id = B.product_balance_id
  240.                AND A.warehouse_id = B.warehouse_id
  241.                AND A.product_status = B.product_status
  242.                AND A.session_id = $5
  243.            ' USING vStartDateLog, vEndDateLog, pOuId, vNo, pSessionId;
  244.            
  245.             EXECUTE '
  246.            WITH tt_in_log_product_consignment_balance_stock AS (
  247.                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty, A.warehouse_id, A.product_status
  248.                FROM in_log_product_consignment_balance_stock A
  249.                INNER JOIN m_product_custom B ON A.product_id = B.product_id
  250.                WHERE A.doc_date BETWEEN $1 AND $2
  251.                    AND A.ou_id = $3
  252.                    AND B.flg_buy_konsinyasi = $4
  253.                 GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
  254.            )
  255.            UPDATE tr_summary_update_stock A
  256.            SET last_balance_qty = A.last_balance_qty + B.qty,
  257.                expectation = A.expectation + B.qty
  258.            FROM tt_in_log_product_consignment_balance_stock B
  259.            WHERE A.tenant_id = B.tenant_id
  260.                AND A.product_id = B.product_id
  261.                AND A.product_balance_id = B.product_balance_id
  262.                AND A.warehouse_id = B.warehouse_id
  263.                AND A.product_status = B.product_status
  264.                AND A.session_id = $5
  265.            ' USING vStartDateLog, vEndDateLog, pOuId, vNo, pSessionId;
  266. --        ELSE
  267.  
  268.             -- product non consignment
  269.             EXECUTE '
  270.            WITH tt_in_log_product_balance_stock AS (
  271.                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty, A.warehouse_id, A.product_status
  272.                FROM in_log_product_balance_stock A
  273.                INNER JOIN m_product_custom B ON A.product_id = B.product_id
  274.                WHERE A.doc_date BETWEEN $1 AND $2
  275.                    AND A.ou_id = $3
  276.                    AND B.flg_buy_konsinyasi = $4
  277.                    AND A.doc_type_id <> 111
  278.                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status  
  279.            )
  280.            UPDATE tr_summary_update_stock A
  281.            SET last_balance_qty = A.last_balance_qty + B.qty,
  282.                expectation = A.expectation + B.qty
  283.            FROM tt_in_log_product_balance_stock B
  284.            WHERE A.tenant_id = B.tenant_id
  285.                AND A.product_id = B.product_id
  286.                AND A.product_balance_id = B.product_balance_id
  287.                AND A.warehouse_id = B.warehouse_id
  288.                AND A.product_status = B.product_status
  289.                AND A.session_id = $5
  290.            ' USING vStartDateLog, vEndDateLog, pOuId, vYes, pSessionId;
  291.            
  292.             EXECUTE '
  293.            WITH tt_in_log_product_consignment_balance_stock AS (
  294.                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty, A.warehouse_id, A.product_status
  295.                FROM in_log_product_consignment_balance_stock A
  296.                INNER JOIN m_product_custom B ON A.product_id = B.product_id
  297.                WHERE A.doc_date BETWEEN $1 AND $2
  298.                    AND A.ou_id = $3
  299.                    AND B.flg_buy_konsinyasi = $4
  300.                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
  301.            )
  302.            UPDATE tr_summary_update_stock A
  303.            SET last_balance_qty = A.last_balance_qty + B.qty,
  304.                expectation = A.expectation + B.qty
  305.            FROM tt_in_log_product_consignment_balance_stock B
  306.            WHERE A.tenant_id = B.tenant_id
  307.                AND A.product_id = B.product_id
  308.                AND A.product_balance_id = B.product_balance_id
  309.                AND A.warehouse_id = B.warehouse_id
  310.                AND A.product_status = B.product_status
  311.                AND A.session_id = $5
  312.            ' USING vStartDateLog, vEndDateLog, pOuId, vYes, pSessionId;
  313. --        END IF;
  314.     END IF;
  315.    
  316.     EXECUTE '
  317.    WITH tt_os_document_qty AS (
  318.        SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(A.qty_realization) AS qty, A.warehouse_from_id AS warehouse_id, A.product_status
  319.        FROM vw_in_product_out_with_outstanding_document A
  320.        WHERE A.tenant_id = $2
  321.            AND A.ou_from_id = $3
  322.        GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_from_id, A.product_status
  323.    )
  324.    UPDATE tr_summary_update_stock A
  325.    SET os_doc_qty = B.qty
  326.    FROM tt_os_document_qty B
  327.    WHERE A.tenant_id = B.tenant_id
  328.        AND A.product_id = B.product_id
  329.        AND A.product_balance_id = B.product_balance_id
  330.        AND A.warehouse_id = B.warehouse_id
  331.        AND A.product_status = B.product_status
  332.        AND A.session_id = $1
  333.    ' USING pSessionId, pTenantId, pOuId;
  334.    
  335.     UPDATE in_product_balance_stock A
  336.     SET qty = B.last_balance_qty + B.mutasi_positif - B.mutasi_minus - B.os_doc_qty
  337.     FROM tr_summary_update_stock B
  338.     WHERE A.tenant_id = B.tenant_id
  339.         AND A.product_id = B.product_id
  340.         AND A.product_balance_id = B.product_balance_id
  341.         AND A.warehouse_id = B.warehouse_id
  342.         AND A.product_status = B.product_status
  343.         AND B.session_id = pSessionId;
  344.    
  345.     DELETE FROM tr_summary_update_stock WHERE session_id = pSessionId;
  346.    
  347. END;
  348. $BODY$
  349.   LANGUAGE plpgsql VOLATILE
  350.   COST 100;
  351.  /
Advertisement
Add Comment
Please, Sign In to add comment