SHARE
TWEET

Untitled

a guest Aug 22nd, 2019 96 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_inventory_summary_mutation_monthly_by_product_code_periode(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying)
  2.   RETURNS SETOF refcursor 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.     pYearMonthFrom              ALIAS FOR $7;
  14.     pYearMonthTo                ALIAS FOR $8;
  15.     pCodeProduct                ALIAS FOR $9;
  16.    
  17.     vDefaultDocTypeId                       integer := -99;
  18.     vDocTypeIdGr                            integer := 111;
  19.     vDocTypeIdDo                            integer := 311;
  20.     vDocTypeIdDoReceipt                     integer := 526;
  21.     vDocTypeIdRn                            integer := 502;
  22.     vDocTypeidAsq                           integer := 521;
  23.     vDocTypeIdAsa                           integer := 522;
  24.     vDocTypeIdClaimNote                     integer := 511;
  25.     vDocTypeIdCostingAllocationToProduct    integer := 528;
  26.     vEmptyString                character varying := '';
  27.    
  28.     vGroupCoaPersediaanId               integer := 13;
  29.     vGroupCoaPersediaanIdTenant11       integer := 1113;
  30.     vEmptyId                            integer := -99;
  31.     vSignJournalCredit                  character varying := 'C';
  32.     vSignJournalDebit                   character varying := 'D';
  33.    
  34. BEGIN
  35.    
  36.     -- SUM for stock_awal
  37.     INSERT INTO tt_mutasi_nilai_persediaan(
  38.             session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id,
  39.             qty_stock_awal)
  40.             SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  41.             sum(A.qty)
  42.             FROM in_summary_monthly_amount A
  43.             INNER JOIN m_product B USING (product_id)
  44.             WHERE A.tenant_id = pTenantId
  45.             AND A.ou_bu_id = pOuId
  46.             AND A.date_year_month = pYearMonthFrom
  47.             AND A.doc_type_id = vDefaultDocTypeId
  48.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;
  49.            
  50.     -- SUM for stock_awal: Journal Voucher Debt
  51.     INSERT INTO tt_mutasi_nilai_persediaan(
  52.             session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
  53.             qty_stock_awal)
  54.             SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
  55.             sum(A.qty)
  56.             FROM vw_gl_journal_trx_details A
  57.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  58.             INNER JOIN m_coa C ON A.coa_id = C.coa_id
  59.             WHERE A.tenant_id = pTenantId
  60.             AND B.ou_bu_id = pOuId
  61.             AND SUBSTRING(B.doc_date, 1, 6) < pYearMonthFrom
  62.             AND B.doc_type_id NOT IN (
  63.                 vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
  64.                 vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
  65.                 vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct  
  66.             )
  67.             AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
  68.             AND A.sign_journal = vSignJournalDebit
  69.             GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;
  70.            
  71.     -- SUM for stock_awal: Journal Voucher Credit
  72.     INSERT INTO tt_mutasi_nilai_persediaan(
  73.             session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
  74.             qty_stock_awal)
  75.             SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount) * -1,vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
  76.             sum(A.qty)
  77.             FROM vw_gl_journal_trx_details A
  78.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  79.             INNER JOIN m_coa C ON A.coa_id = C.coa_id
  80.             WHERE A.tenant_id = pTenantId
  81.             AND B.ou_bu_id = pOuId
  82.             AND SUBSTRING(B.doc_date, 1, 6) < pYearMonthFrom
  83.             AND B.doc_type_id NOT IN (
  84.                 vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
  85.                 vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
  86.                 vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct
  87.             )
  88.             AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
  89.             AND A.sign_journal = vSignJournalCredit
  90.             GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;
  91.  
  92.     -- SUM for stock_receipt
  93.     INSERT INTO tt_mutasi_nilai_persediaan(
  94.             session_id,year_month,stock_receipt,product_id,ctgr_product_id,sub_ctgr_product_id,
  95.             qty_stock_receipt)
  96.             SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  97.             sum(A.qty)
  98.             FROM in_summary_monthly_amount A
  99.             INNER JOIN m_product B USING (product_id)
  100.             WHERE A.tenant_id = pTenantId
  101.             AND A.ou_bu_id = pOuId
  102.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  103.             AND A.doc_type_id = vDocTypeIdGr
  104.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;       
  105.  
  106.     -- SUM for stock_sales
  107.     INSERT INTO tt_mutasi_nilai_persediaan(
  108.             session_id,year_month,stock_sales,product_id,ctgr_product_id,sub_ctgr_product_id,
  109.             qty_stock_sales)
  110.             SELECT pSessionId,A.date_year_month,(SUM(gl_amount) * -1),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  111.             sum(A.qty) * -1
  112.             FROM in_summary_monthly_amount A
  113.             INNER JOIN m_product B USING (product_id)
  114.             WHERE A.tenant_id = pTenantId
  115.             AND A.ou_bu_id = pOuId
  116.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  117.             AND A.doc_type_id IN ( vDocTypeIdDo, vDocTypeIdDoReceipt )
  118.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;       
  119.  
  120.     -- SUM for stock_return_sales
  121.     INSERT INTO tt_mutasi_nilai_persediaan(
  122.             session_id,year_month,stock_return_sales,product_id,ctgr_product_id,sub_ctgr_product_id,
  123.             qty_stock_return_sales)
  124.             SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  125.             sum(A.qty)
  126.             FROM in_summary_monthly_amount A
  127.             INNER JOIN m_product B USING (product_id)
  128.             WHERE A.tenant_id = pTenantId
  129.             AND A.ou_bu_id = pOuId
  130.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  131.             AND A.doc_type_id = vDocTypeIdRn
  132.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;       
  133.  
  134.     -- SUM for stock_adj_qty
  135.     INSERT INTO tt_mutasi_nilai_persediaan(
  136.             session_id,year_month,stock_adj_qty,product_id,ctgr_product_id,sub_ctgr_product_id,
  137.             qty_stock_adj_qty)
  138.             SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  139.             sum(A.qty)
  140.             FROM in_summary_monthly_amount A
  141.             INNER JOIN m_product B USING (product_id)
  142.             WHERE A.tenant_id = pTenantId
  143.             AND A.ou_bu_id = pOuId
  144.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  145.             AND A.doc_type_id = vDocTypeidAsq
  146.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;       
  147.  
  148.     -- SUM for stock_adj_amount
  149.     INSERT INTO tt_mutasi_nilai_persediaan(
  150.             session_id,year_month,stock_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id,
  151.             qty_stock_adj_amount)
  152.             SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
  153.             sum(A.qty)
  154.             FROM in_summary_monthly_amount A
  155.             INNER JOIN m_product B USING (product_id)
  156.             WHERE A.tenant_id = pTenantId
  157.             AND A.ou_bu_id = pOuId
  158.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  159.             AND A.doc_type_id IN (vDocTypeIdAsa, vDocTypeIdCostingAllocationToProduct)
  160.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;
  161.    
  162.     -- SUM for stock_claim_note
  163.     INSERT INTO tt_mutasi_nilai_persediaan(
  164.             session_id, year_month, stock_claim_note, product_id, ctgr_product_id, sub_ctgr_product_id,
  165.             qty_stock_claim_note)
  166.             SELECT pSessionId, A.date_year_month, SUM(gl_amount) * -1, A.product_id, B.ctgr_product_id, B.sub_ctgr_product_id,
  167.             sum(A.qty)* -1
  168.             FROM in_summary_monthly_amount A
  169.             INNER JOIN m_product B USING (product_id)
  170.             WHERE A.tenant_id = pTenantId
  171.             AND A.ou_bu_id = pOuId
  172.             AND A.date_year_month BETWEEN pYearMonthFrom AND pYearMonthTo
  173.             AND A.doc_type_id = vDocTypeIdClaimNote
  174.             GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;
  175.            
  176.     -- SUM for Journal Voucher Persediaan Debit
  177.     INSERT INTO tt_mutasi_nilai_persediaan(
  178.             session_id,year_month,stock_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
  179.             qty_stock_adj_amount)
  180.             SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
  181.             sum(A.qty)
  182.             FROM vw_gl_journal_trx_details A
  183.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  184.             INNER JOIN m_coa C ON A.coa_id = C.coa_id
  185.             WHERE A.tenant_id = pTenantId
  186.             AND B.ou_bu_id = pOuId
  187.             AND SUBSTRING(B.doc_date, 1, 6) BETWEEN pYearMonthFrom AND pYearMonthTo
  188.             AND B.doc_type_id NOT IN (
  189.                 vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
  190.                 vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
  191.                 vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct  
  192.             )
  193.             AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
  194.             AND A.sign_journal = vSignJournalDebit
  195.             GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;
  196.            
  197.     -- SUM for Journal Voucher Persediaan Credit
  198.     INSERT INTO tt_mutasi_nilai_persediaan(
  199.             session_id,year_month,stock_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
  200.             qty_stock_adj_amount)
  201.             SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount) * -1,vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
  202.             sum(A.qty)
  203.             FROM vw_gl_journal_trx_details A
  204.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  205.             INNER JOIN m_coa C ON A.coa_id = C.coa_id
  206.             WHERE A.tenant_id = pTenantId
  207.             AND B.ou_bu_id = pOuId
  208.             AND SUBSTRING(B.doc_date, 1, 6) BETWEEN pYearMonthFrom AND pYearMonthTo
  209.             AND B.doc_type_id NOT IN (
  210.                 vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
  211.                 vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
  212.                 vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct
  213.             )
  214.             AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
  215.             AND A.sign_journal = vSignJournalCredit
  216.             GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;
  217.  
  218.     OPEN pRefHeader FOR
  219.         SELECT  f_get_ou_name(pOuId) AS ou_name, pYearMonthFrom AS year_month_from, pYearMonthTo AS year_month_to,
  220.                 f_get_username(pUserId) AS username, f_get_role_name(pRoleId) AS role_name, pDatetime AS datetime;
  221.     RETURN NEXT pRefHeader;
  222.  
  223.    
  224.     OPEN pRefDetail FOR
  225.         SELECT C.*
  226.         FROM (
  227.             SELECT A.session_id, B.doc_no AS ctgr_product_name,
  228.                 f_get_coa_desc(A.coa_id) AS sub_ctgr_product_name,
  229.                 vEmptyString AS product_code,
  230.                 vEmptyString AS product_name,  
  231.                 SUM(A.qty_stock_awal) AS qty_stock_awal,
  232.                 SUM(A.stock_awal) AS stock_awal,
  233.                 SUM(A.qty_stock_receipt) AS qty_stock_receipt,
  234.                 SUM(A.stock_receipt) AS stock_receipt,
  235.                 SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
  236.                 SUM(A.stock_claim_note) AS stock_claim_note,
  237.                 SUM(A.qty_stock_sales) AS qty_stock_sales,
  238.                 SUM(A.stock_sales) AS stock_sales,
  239.                 SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
  240.                 SUM(A.stock_return_sales) AS stock_return_sales,
  241.                 SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
  242.                 SUM(A.stock_adj_qty) AS stock_adj_qty,
  243.                 SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
  244.                 SUM(A.stock_adj_amount) AS stock_adj_amount,
  245.                 SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
  246.                   AS qty_stock_akhir,
  247.                 SUM( A.stock_awal + stock_receipt - stock_sales + stock_return_sales + stock_adj_qty + stock_adj_amount - stock_claim_note )
  248.                   AS stock_akhir
  249.             FROM tt_mutasi_nilai_persediaan A
  250.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  251.             WHERE A.session_id = pSessionId
  252.             AND A.journal_trx_id <> vEmptyId
  253.             AND A.year_month < pYearMonthFrom
  254.             GROUP BY A.session_id, A.journal_trx_id, A.year_month, B.doc_no, A.coa_id
  255.             ORDER BY A.year_month, B.doc_no, f_get_coa_desc(A.coa_id)
  256.         ) C
  257.    
  258.         UNION ALL
  259.    
  260.         SELECT A.*
  261.         FROM (
  262.             SELECT A.session_id,f_get_ctgr_product_name(A.ctgr_product_id) AS ctgr_product_name,
  263.                 f_get_sub_ctgr_product_name(A.sub_ctgr_product_id) AS sub_ctgr_product_name,
  264.                 f_get_product_code(A.product_id) AS product_code,
  265.                 f_get_product_name(A.product_id) AS product_name,
  266.                 SUM(A.qty_stock_awal) AS qty_stock_awal,
  267.                 SUM(A.stock_awal) AS stock_awal,
  268.                 SUM(A.qty_stock_receipt) AS qty_stock_receipt,
  269.                 SUM(A.stock_receipt) AS stock_receipt,
  270.                 SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
  271.                 SUM(A.stock_claim_note) AS stock_claim_note,
  272.                 SUM(A.qty_stock_sales) AS qty_stock_sales,
  273.                 SUM(A.stock_sales) AS stock_sales,
  274.                 SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
  275.                 SUM(A.stock_return_sales) AS stock_return_sales,
  276.                 SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
  277.                 SUM(A.stock_adj_qty) AS stock_adj_qty,
  278.                 SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
  279.                 SUM(A.stock_adj_amount) AS stock_adj_amount,
  280.                 SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
  281.                   AS qty_stock_akhir,
  282.                 SUM( A.stock_awal + stock_receipt - stock_sales + stock_return_sales + stock_adj_qty + stock_adj_amount - stock_claim_note )
  283.                   AS stock_akhir
  284.             FROM tt_mutasi_nilai_persediaan A
  285.             WHERE A.session_id = pSessionId AND
  286.             CASE WHEN pCodeProduct <> vEmptyString THEN UPPER(f_get_product_code(A.product_id)) LIKE UPPER('%'||pCodeProduct||'%') ELSE f_get_product_code(A.product_id) <> vEmptyString END
  287.             AND A.journal_trx_id = vEmptyId
  288.             GROUP BY A.session_id, A.product_id, A.sub_ctgr_product_id, A.ctgr_product_id
  289.             ORDER BY f_get_ctgr_product_name(A.ctgr_product_id), f_get_sub_ctgr_product_name(A.sub_ctgr_product_id), f_get_product_code(A.product_id)
  290.     --           A.stock_sales,A.stock_return_sales,A.stock_adj_qty,A.stock_adj_amount,A.stock_awal,A.stock_receipt;
  291.         ) A
  292.        
  293.         UNION ALL
  294.        
  295.         SELECT B.*
  296.         FROM (
  297.             SELECT A.session_id, B.doc_no AS ctgr_product_name,
  298.                 f_get_coa_desc(A.coa_id) AS sub_ctgr_product_name,
  299.                 vEmptyString AS product_code,
  300.                 vEmptyString AS product_name,  
  301.                 SUM(A.qty_stock_awal) AS qty_stock_awal,
  302.                 SUM(A.stock_awal) AS stock_awal,
  303.                 SUM(A.qty_stock_receipt) AS qty_stock_receipt,
  304.                 SUM(A.stock_receipt) AS stock_receipt,
  305.                 SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
  306.                 SUM(A.stock_claim_note) AS stock_claim_note,
  307.                 SUM(A.qty_stock_sales) AS qty_stock_sales,
  308.                 SUM(A.stock_sales) AS stock_sales,
  309.                 SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
  310.                 SUM(A.stock_return_sales) AS stock_return_sales,
  311.                 SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
  312.                 SUM(A.stock_adj_qty) AS stock_adj_qty,
  313.                 SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
  314.                 SUM(A.stock_adj_amount) AS stock_adj_amount,
  315.                 SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
  316.                   AS qty_stock_akhir,
  317.                 SUM( A.stock_awal + stock_receipt - stock_sales + stock_return_sales + stock_adj_qty + stock_adj_amount - stock_claim_note )
  318.                   AS stock_akhir
  319.             FROM tt_mutasi_nilai_persediaan A
  320.             INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
  321.             WHERE A.session_id = pSessionId
  322.             AND A.journal_trx_id <> vEmptyId
  323.             AND A.year_month >= pYearMonthFrom
  324.             GROUP BY A.session_id, A.journal_trx_id, B.doc_no, A.coa_id
  325.             ORDER BY B.doc_no, f_get_coa_desc(A.coa_id)
  326.         ) B;
  327.     RETURN NEXT pRefDetail;
  328.  
  329.     DELETE FROM tt_mutasi_nilai_persediaan WHERE session_id = pSessionId;
  330. END;
  331. $BODY$
  332.   LANGUAGE plpgsql VOLATILE
  333.   COST 100
  334.   ROWS 1000;
  335.  /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top