tercnem

in_cancel_closing_costing_finish_goods

Sep 24th, 2020 (edited)
1,013
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION in_cancel_closing_costing_finish_goods(bigint, character varying, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.  
  6.     pTenantId           ALIAS FOR $1;
  7.     pSessionId          ALIAS FOR $2;
  8.     pProcessNo          ALIAS FOR $3;
  9.  
  10.     vProcessId              bigint;
  11.     vOuId                   bigint;
  12.     vUserId                 bigint;
  13.     vDatetime               character varying(14);
  14.     vYearMonth              character varying(6);
  15.     vNextYearMonth          character varying(6);
  16.     vGroupProductFG         character varying(5);
  17.     vAdminProcessLedgerId   bigint;
  18.     vLedgerCodeCostFG       character varying(10);
  19.     vDocTypeAwal            bigint;
  20.     vEmptyId                bigint;
  21.     vStatusDraft            character varying(1);
  22.  
  23. BEGIN
  24.    
  25.     vLedgerCodeCostFG := 'COST.FG';
  26.     vDocTypeAwal := -99;
  27.     vGroupProductFG := 'FG';
  28.     vEmptyId := -99;
  29.     vStatusDraft := 'D';
  30.    
  31.     SELECT A.process_message_id INTO vProcessId
  32.     FROM t_process_message A
  33.     WHERE A.tenant_id = pTenantId AND
  34.         A.process_name = 'in_cancel_closing_costing_finish_goods' AND
  35.         A.process_no = pProcessNo;
  36.        
  37.     SELECT CAST(A.process_parameter_value AS bigint) INTO vOuId
  38.     FROM t_process_parameter A
  39.     WHERE A.process_message_id = vProcessId AND
  40.         A.process_parameter_key = 'ouId';
  41.    
  42.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  43.     FROM t_process_parameter A
  44.     WHERE A.process_message_id = vProcessId AND
  45.         A.process_parameter_key = 'userId';
  46.  
  47.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  48.     FROM t_process_parameter A
  49.     WHERE A.process_message_id = vProcessId AND
  50.         A.process_parameter_key = 'datetime';
  51.        
  52.     SELECT CAST(A.process_parameter_value AS character varying(6)) INTO vYearMonth
  53.     FROM t_process_parameter A
  54.     WHERE A.process_message_id = vProcessId AND
  55.         A.process_parameter_key = 'yearmonth';
  56.  
  57.     SELECT CAST(A.process_parameter_value AS bigint) INTO vAdminProcessLedgerId
  58.     FROM t_process_parameter A
  59.     WHERE A.process_message_id = vProcessId AND
  60.         A.process_parameter_key = 'adminProcessLedgerId';
  61.        
  62.     SELECT TO_CHAR(TO_DATE(vYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
  63.    
  64.     /*
  65.      * Hapus harga dasar pokok di bulan ybs
  66.      */
  67.     DELETE FROM in_summary_monthly_cogs A
  68.     WHERE A.date_year_month = vYearMonth AND
  69.         A.ou_id = vOuId AND
  70.         A.remark = vGroupProductFG;
  71.        
  72.     DELETE FROM in_summary_monthly_assembly_cogs WHERE date_year_month = vYearMonth AND ou_id = vOuId;
  73.        
  74.     /*
  75.      * Hapus data saldo akhir tahun bulan ybs, kecuali saldo awal
  76.      */
  77.     DELETE FROM in_summary_monthly_amount A
  78.     WHERE A.date_year_month = vYearMonth AND
  79.         A.ou_bu_id = vOuId AND
  80.         A.doc_type_id <> vDocTypeAwal AND
  81.         A.remark = vGroupProductFG;
  82.        
  83.     /*
  84.      * delete data in_summary_monthly_zero_qty_amount
  85.      */
  86.     DELETE FROM in_summary_monthly_zero_qty_amount A
  87.     WHERE A.date_year_month = vYearMonth AND
  88.         A.ou_bu_id = vOuId AND
  89.         A.doc_type_id <> vDocTypeAwal AND
  90.         A.remark = vGroupProductFG;
  91.    
  92.     /*
  93.     DELETE FROM in_summary_monthly_amount A
  94.     WHERE A.date_year_month = vYearMonth AND
  95.         A.ou_bu_id = vOuId AND
  96.         A.doc_type_id <> vDocTypeAwal AND
  97.         EXISTS (SELECT 1 FROM vw_finish_goods_product_assembly E WHERE A.product_id = E.product_id);
  98.     */
  99.     /*
  100.      * Hapus data saldo awal tahun bulan berikutnya
  101.      */
  102.     DELETE FROM in_summary_monthly_amount A
  103.     WHERE A.date_year_month = vNextYearMonth AND
  104.         A.ou_bu_id = vOuId AND
  105.         A.remark = vGroupProductFG;
  106.    
  107.     --delete data anomaly
  108.     DELETE FROM in_anomaly_gl_amount_from_process_costing A
  109.     WHERE A.date_year_month = vYearMonth AND
  110.         A.ou_bu_id = vOuId AND
  111.         A.doc_type_id <> vDocTypeAwal AND
  112.         A.remark = vGroupProductFG;
  113.        
  114.     --delete data anomaly tahun bulan berikutnya
  115.     DELETE FROM in_anomaly_gl_amount_from_process_costing A
  116.     WHERE A.date_year_month = vNextYearMonth AND
  117.         A.ou_bu_id = vOuId AND
  118.         A.remark = vGroupProductFG;
  119.    
  120.     /*
  121.     DELETE FROM in_summary_monthly_amount A
  122.     WHERE A.date_year_month = vNextYearMonth AND
  123.         A.ou_bu_id = vOuId AND
  124.         EXISTS (SELECT 1 FROM vw_finish_goods_product_assembly E WHERE A.product_id = E.product_id);
  125.     */
  126.     UPDATE m_admin_process_ledger SET status_ledger = 0, flag_process = 'N',
  127.         process_no = pProcessNo, process_datetime = vDatetime, process_user_id = vUserId
  128.     WHERE tenant_id = pTenantId AND
  129.         ou_id = vOuId AND
  130.         date_year_month = vYearMonth AND
  131.         ledger_code = vLedgerCodeCostFG;
  132.    
  133.     /* Adrian, Feb 27, 2017
  134.      * update status journal menjadi D
  135.      */
  136.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  137.        
  138.     INSERT INTO tt_gl_journal_costing
  139.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  140.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  141.     qty, uom_id, amount, total_amount_product, total_qty_product)
  142.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  143.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
  144.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
  145.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  146.         B.qty, B.uom_id, 0, 0, 0
  147.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product D
  148.     WHERE A.tenant_id = pTenantId AND
  149.         A.ou_bu_id = vOuId AND
  150.         A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND
  151.         A.doc_date = C.string_date AND
  152.         C.year_month_date = vYearMonth AND
  153.         A.journal_trx_id = B.journal_trx_id AND
  154.         B.product_id <> vEmptyId AND
  155.         B.product_id = D.product_id;
  156.        
  157.     INSERT INTO tt_gl_journal_costing
  158.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  159.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  160.     qty, uom_id, amount, total_amount_product, total_qty_product)
  161.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  162.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
  163.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
  164.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  165.         B.qty, B.uom_id, 0, 0, 0
  166.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product_assembly D
  167.     WHERE A.tenant_id = pTenantId AND
  168.         A.ou_bu_id = vOuId AND
  169.         A.doc_type_id IN (522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND   
  170.         A.doc_date = C.string_date AND
  171.         C.year_month_date = vYearMonth AND
  172.         A.journal_trx_id = B.journal_trx_id AND
  173.         B.product_id <> vEmptyId AND
  174.         B.product_id = D.product_id;
  175.        
  176.     UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
  177.     FROM (SELECT journal_trx_id
  178.             FROM tt_gl_journal_costing A
  179.             WHERE A.session_id = pSessionId
  180.             GROUP BY journal_trx_id) A
  181.     WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;    
  182.    
  183.     -- update status document khusus untuk document cost allocation to product (528), dan adjustment stok amount (522)
  184.     UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
  185.     WHERE doc_type_id IN (528,522)
  186.     AND SUBSTRING(doc_date, 1, 6) = vYearMonth;
  187.    
  188.     PERFORM in_delete_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, vYearMonth, vOuId, vDatetime, vUserId, vGroupProductFG);
  189.    
  190.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  191.        
  192.  END;
  193. $BODY$
  194.   LANGUAGE plpgsql VOLATILE
  195.   COST 100;
  196.   /
RAW Paste Data