widana

f_update_balance_stock_cland

Apr 18th, 2017
101
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.     pWarehouseId                ALIAS FOR $6;
  13.     pDateNow                    ALIAS FOR $7;
  14.    
  15.     vLastClosingGeneralLedger   CHARACTER VARYING := '';
  16.     vStartDateLog               CHARACTER VARYING := '';
  17.     vEndDateLog                 CHARACTER VARYING := '';
  18.     vNo                         CHARACTER VARYING := 'N';
  19.     vYes                        CHARACTER VARYING := 'Y';
  20.     vEmptyId                    bigint := -99;
  21.     vOuId                       bigint;
  22.    
  23. BEGIN
  24.  
  25.    
  26.     DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;
  27.  
  28.     SELECT ou_id INTO vOuId
  29.     FROM m_warehouse_ou WHERE warehouse_id = pWarehouseId;
  30.    
  31.     -- last closing ledger sebelum pDateNow
  32.     SELECT COALESCE(MAX(date_year_month), MIN(date_year_month)) AS last_closing_gl INTO vLastClosingGeneralLedger
  33.     FROM m_admin_process_ledger
  34.     WHERE tenant_id = pTenantId AND
  35.         ou_id = vOuId AND
  36.         ledger_code = 'INV' AND
  37.         status_ledger = '1' AND
  38.         date_year_month <= SUBSTRING(pDateNow, 1, 6);
  39.    
  40.     -- tanggal awal untuk transaksi diantara tgl saldo awal sampai sebelum pDateNow
  41.     vStartDateLog = vLastClosingGeneralLedger || '01';
  42.     SELECT TO_CHAR(TO_DATE(pDateNow,'YYYYMMDD') + INTERVAL '-1 Day','YYYYMMDD') INTO vEndDateLog;
  43.    
  44.     -- insert from log product WHEN pFlgConsignment = 'N'
  45.         EXECUTE '
  46.        INSERT INTO tr_summary_update_product_stock(
  47.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  48.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  49.            warehouse_id, product_status, os_doc_qty
  50.        )
  51.        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
  52.                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
  53.                A.warehouse_id, A.product_status, 0
  54.        FROM (
  55.            SELECT A.product_id, B.product_code, B.product_name,
  56.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  57.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  58.                    A.warehouse_id, A.product_status
  59.            FROM in_log_product_balance_stock A
  60.            INNER JOIN m_product B ON A.product_id = B.product_id
  61.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  62.            WHERE A.doc_date BETWEEN $4 AND $5
  63.                AND A.warehouse_id = $3
  64.                AND C.flg_buy_konsinyasi = $6
  65.             UNION ALL
  66.            SELECT A.product_id, B.product_code, B.product_name,
  67.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  68.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  69.                    A.warehouse_id, A.product_status
  70.            FROM in_log_product_consignment_balance_stock A
  71.            INNER JOIN m_product B ON A.product_id = B.product_id
  72.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  73.            WHERE A.doc_date BETWEEN $4 AND $5
  74.                AND A.warehouse_id = $3
  75.                AND C.flg_buy_konsinyasi = $6 ) A
  76.            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  77.         USING pSessionId, pTenantId, pWarehouseId, vStartDateLog, pDateNow, vNo;
  78.        
  79.     -- insert from log product WHEN pFlgConsignment = 'Y'
  80.         EXECUTE '
  81.        INSERT INTO tr_summary_update_product_stock(
  82.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  83.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  84.            warehouse_id, product_status, os_doc_qty
  85.        )
  86.        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
  87.                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
  88.                A.warehouse_id, A.product_status, 0
  89.        FROM (
  90.            SELECT A.product_id, B.product_code, B.product_name,
  91.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  92.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  93.                    A.warehouse_id, A.product_status
  94.            FROM in_log_product_balance_stock A
  95.            INNER JOIN m_product B ON A.product_id = B.product_id
  96.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  97.            WHERE A.doc_date BETWEEN $4 AND $5
  98.                AND A.warehouse_id = $3
  99.                AND C.flg_buy_konsinyasi = $6
  100.                AND A.doc_type_id <> 111
  101.             UNION ALL
  102.            SELECT A.product_id, B.product_code, B.product_name,
  103.                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
  104.                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
  105.                    A.warehouse_id, A.product_status
  106.            FROM in_log_product_consignment_balance_stock A
  107.            INNER JOIN m_product B ON A.product_id = B.product_id
  108.            INNER JOIN m_product_custom C ON B.product_id = C.product_id
  109.            WHERE A.doc_date BETWEEN $4 AND $5
  110.                AND A.warehouse_id = $3
  111.                AND C.flg_buy_konsinyasi = $6
  112.             ) A
  113.            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  114.         USING pSessionId, pTenantId, pWarehouseId, vStartDateLog, pDateNow, vYes;
  115.        
  116.        
  117.        
  118.     -- update nilai saldo awal qty WHEN pFlgConsignment = 'N'
  119.         WITH tt_in_summary_monthly_qty AS (
  120.             SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
  121.             FROM in_summary_monthly_qty
  122.             WHERE tenant_id = pTenantId
  123.                 AND doc_type_id = vEmptyId
  124.                 AND date_year_month = vLastClosingGeneralLedger
  125.                 AND warehouse_id = pWarehouseId
  126.             GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
  127.         )
  128.         UPDATE tr_summary_update_product_stock A
  129.         SET last_balance_qty = B.qty,
  130.             expectation = B.qty
  131.         FROM tt_in_summary_monthly_qty B
  132.         WHERE A.tenant_id = B.tenant_id
  133.             AND A.product_id = B.product_id
  134.             AND A.product_balance_id = B.product_balance_id
  135.             AND A.warehouse_id = B.warehouse_id
  136.             AND A.product_status = B.product_status
  137.             AND A.session_id = pSessionId;
  138.  
  139.     -- update nilai saldo awal qty WHEN pFlgConsignment = 'Y'
  140.         WITH tt_in_summary_monthly_qty AS (
  141.             SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
  142.             FROM in_summary_monthly_qty_consignment
  143.             WHERE tenant_id = pTenantId
  144.                 AND doc_type_id = vEmptyId
  145.                 AND date_year_month = vLastClosingGeneralLedger
  146.                 AND warehouse_id = pWarehouseId
  147.             GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
  148.         )
  149.         UPDATE tr_summary_update_product_stock A
  150.         SET last_balance_qty = B.qty,
  151.             expectation = B.qty
  152.         FROM tt_in_summary_monthly_qty B
  153.         WHERE A.tenant_id = B.tenant_id
  154.             AND A.product_id = B.product_id
  155.             AND A.product_balance_id = B.product_balance_id
  156.             AND A.warehouse_id = B.warehouse_id
  157.             AND A.product_status = B.product_status
  158.             AND A.session_id = pSessionId;
  159.    
  160.  
  161.            
  162.     -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'N'
  163.         EXECUTE '
  164.        INSERT INTO tr_summary_update_product_stock(
  165.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  166.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  167.            warehouse_id, product_status, os_doc_qty
  168.        )
  169.        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
  170.                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
  171.                A.warehouse_id, A.product_status, 0
  172.        FROM in_summary_monthly_qty A
  173.        INNER JOIN m_product B ON A.product_id = B.product_id
  174.        INNER JOIN m_product_custom C ON B.product_id = C.product_id
  175.        WHERE A.doc_type_id = $4
  176.                AND A.date_year_month = $5
  177.                AND A.warehouse_id = $3
  178.                AND C.flg_buy_konsinyasi = $6
  179.         AND NOT EXISTS(
  180.            SELECT 1 FROM tr_summary_update_product_stock D
  181.            WHERE A.tenant_id = D.tenant_id
  182.                AND A.product_id = D.product_id
  183.                AND A.product_balance_id = D.product_balance_id
  184.        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  185.         USING pSessionId, pTenantId, pWarehouseId, vEmptyId, vLastClosingGeneralLedger, vNo;
  186.        
  187.     -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'Y'
  188.         EXECUTE '
  189.        INSERT INTO tr_summary_update_product_stock(
  190.            session_id, tenant_id, ou_id, product_id, product_code, product_name,
  191.            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
  192.            warehouse_id, product_status, os_doc_qty
  193.        )
  194.        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
  195.                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
  196.                A.warehouse_id, A.product_status, 0
  197.        FROM in_summary_monthly_qty_consignment A
  198.        INNER JOIN m_product B ON A.product_id = B.product_id
  199.        INNER JOIN m_product_custom C ON B.product_id = C.product_id
  200.        WHERE A.doc_type_id = $4
  201.                AND A.date_year_month = $5
  202.                AND A.warehouse_id = $3
  203.                AND C.flg_buy_konsinyasi = $6
  204.         AND NOT EXISTS(
  205.            SELECT 1 FROM tr_summary_update_product_stock D
  206.            WHERE A.tenant_id = D.tenant_id
  207.                AND A.product_id = D.product_id
  208.                AND A.product_balance_id = D.product_balance_id
  209.        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
  210.         USING pSessionId, pTenantId, pWarehouseId, vEmptyId, vLastClosingGeneralLedger, vYes;
  211.        
  212.    
  213.     EXECUTE '
  214.    WITH tt_os_document_qty AS (
  215.        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
  216.        FROM vw_in_product_out_with_outstanding_document A
  217.        WHERE A.tenant_id = $2
  218.            AND A.warehouse_from_id = $3
  219.        GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_from_id, A.product_status
  220.    )
  221.    UPDATE tr_summary_update_product_stock A
  222.    SET os_doc_qty = B.qty
  223.    FROM tt_os_document_qty B
  224.    WHERE A.tenant_id = B.tenant_id
  225.        AND A.product_id = B.product_id
  226.        AND A.product_balance_id = B.product_balance_id
  227.        AND A.warehouse_id = B.warehouse_id
  228.        AND A.product_status = B.product_status
  229.        AND A.session_id = $1
  230.    ' USING pSessionId, pTenantId, pWarehouseId;
  231.    
  232.     UPDATE in_product_balance_stock A
  233.     SET qty = B.last_balance_qty + B.mutasi_positif - B.mutasi_minus - B.os_doc_qty ,
  234.         update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
  235.     FROM tr_summary_update_product_stock B
  236.     WHERE A.tenant_id = B.tenant_id
  237.         AND A.product_id = B.product_id
  238.         AND A.product_balance_id = B.product_balance_id
  239.         AND A.warehouse_id = B.warehouse_id
  240.         AND A.product_status = B.product_status
  241.         AND B.session_id = pSessionId;
  242.    
  243.     DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;
  244.    
  245. END;
  246. $BODY$
  247.   LANGUAGE plpgsql VOLATILE
  248.   COST 100;
  249.  /
Advertisement
Add Comment
Please, Sign In to add comment