SHARE
TWEET

in_submit_cost_alloc_to_product

tercnem Jul 22nd, 2019 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: in_submit_cost_alloc_to_product(bigint, character varying, character varying)
  2.  
  3. -- DROP FUNCTION in_submit_cost_alloc_to_product(bigint, character varying, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION in_submit_cost_alloc_to_product(
  6.     bigint,
  7.     character varying,
  8.     character varying)
  9.   RETURNS void AS
  10. $BODY$
  11. DECLARE
  12.     pTenantId           ALIAS FOR $1;
  13.     pSessionId          ALIAS FOR $2;
  14.     pProcessNo          ALIAS FOR $3;
  15.  
  16.     vProcessId          bigint;
  17.     vCostAllocToProductId       bigint;
  18.     vUserId             bigint;
  19.     vDatetime           character varying(14);
  20.     vFlagNo             character varying(1);
  21.     vEmptyId            bigint;
  22.     vEmptyValue         character varying(1);
  23.     vActivityCOA        character varying(20);
  24.     vStatusRelease      character varying(1);
  25.     vStatusDraft        character varying(1);  
  26.     vJournalType        character varying(20);
  27.     vSignDebit          character varying(1);
  28.     vSignCredit         character varying(1);
  29.     vTypeRate           character varying(3);
  30.     vProductCOA         character varying(10);
  31.     vParentOuId         bigint;
  32.     vJournalTrxId       bigint;
  33.  
  34.     vDocJournal             DOC_JOURNAL%ROWTYPE;
  35.     vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
  36.     result                  RECORD;
  37.    
  38. BEGIN
  39.    
  40.     vEmptyId := -99;
  41.     vFlagNo := 'N';
  42.     vActivityCOA := 'ACTIVITY';
  43.     vStatusRelease := 'R';
  44.     vStatusDraft := 'D';   
  45.     vSignDebit := 'D';
  46.     vSignCredit := 'C';
  47.     vTypeRate := 'COM';
  48.     vProductCOA := 'PRODUCT';
  49.     vEmptyValue := ' ';
  50.    
  51.     SELECT A.process_message_id INTO vProcessId
  52.     FROM t_process_message A
  53.     WHERE A.tenant_id = pTenantId AND
  54.         A.process_name = 'in_submit_cost_alloc_to_product' AND
  55.         A.process_no = pProcessNo;
  56.        
  57.     SELECT CAST(A.process_parameter_value AS bigint) INTO vCostAllocToProductId
  58.     FROM t_process_parameter A
  59.     WHERE A.process_message_id = vProcessId AND
  60.         A.process_parameter_key = 'costAllocationId';
  61.    
  62.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  63.     FROM t_process_parameter A
  64.     WHERE A.process_message_id = vProcessId AND
  65.         A.process_parameter_key = 'userId';
  66.  
  67.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  68.     FROM t_process_parameter A
  69.     WHERE A.process_message_id = vProcessId AND
  70.         A.process_parameter_key = 'datetime';
  71.        
  72.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  73.     DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;         
  74.        
  75.     /*
  76.      * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_price_balance
  77.      */
  78.     INSERT INTO tt_in_product_price_balance
  79.     (session_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date, partner_id, warehouse_id,
  80.     inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
  81.      serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id,
  82.      product_price_balance_id, curr_code, amount)
  83.     SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id, A.doc_no, A.doc_date, A.partner_id, A.warehouse_from_id,
  84.         A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
  85.         B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id,
  86.         vEmptyId, B.curr_code, B.amount
  87.     FROM in_inventory A, in_inventory_item B
  88.     WHERE A.inventory_id = B.inventory_id AND
  89.         A.inventory_id = vCostAllocToProductId;
  90.        
  91.     UPDATE tt_in_product_price_balance SET product_balance_id = A.product_balance_id
  92.     FROM in_product_balance A
  93.     WHERE tt_in_product_price_balance.session_id = pSessionId AND
  94.         tt_in_product_price_balance.product_id = A.product_id AND
  95.         tt_in_product_price_balance.tenant_id = A.tenant_id AND
  96.         tt_in_product_price_balance.serial_number = A.serial_number AND
  97.         tt_in_product_price_balance.lot_number = A.lot_number AND
  98.         tt_in_product_price_balance.product_balance_id = vEmptyId;
  99.        
  100.     INSERT INTO in_product_price_balance
  101.     (tenant_id, ou_id, product_id, product_balance_id,
  102.     product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
  103.     curr_code, amount, qty, uom_id,  
  104.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  105.     SELECT C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
  106.         C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
  107.         C.curr_code, SUM(C.amount), 0, C.base_uom_id,
  108.         0, vDatetime, vUserId, vDatetime, vUserId
  109.     FROM tt_in_product_price_balance C
  110.     WHERE C.inventory_id = vCostAllocToProductId AND
  111.         C.session_id = pSessionId
  112.     GROUP BY C.tenant_id, C.ou_id, C.product_id, C.product_balance_id,
  113.         C.doc_date, C.partner_id, C.doc_type_id, C.inventory_item_id, C.doc_no, C.doc_date,
  114.         C.curr_code, C.base_uom_id;
  115.    
  116.     /*
  117.      * 1. update status doc in_inventory
  118.      * 2. buat data jurnal
  119.      */
  120.        
  121.     SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  122.     FROM in_inventory A
  123.     WHERE A.inventory_id = vCostAllocToProductId INTO result;
  124.    
  125.     vOuStructure := result.ou;
  126.     vDocJournal := result.doc; 
  127.    
  128.     UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
  129.     WHERE inventory_id = vCostAllocToProductId;
  130.    
  131.     /*
  132.      * journal adjusment qty
  133.      * Debit Inventory, jika qty > 0
  134.      * Credit Inventory, jika qty < 0
  135.      *
  136.      * Mapping ke account activity gl
  137.      */
  138.     /*
  139.      * membuat data transaksi jurnal :
  140.      * 1. buat admin
  141.      * 2. buat temlate jurnal
  142.      */    
  143.     PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
  144.     FROM in_inventory A
  145.     WHERE A.inventory_id = vCostAllocToProductId;
  146.        
  147.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  148.    
  149.     INSERT INTO gl_journal_trx
  150.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  151.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  152.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  153.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  154.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
  155.         (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
  156.         A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
  157.         0, vDatetime, vUserId, vDatetime, vUserId
  158.     FROM in_inventory A
  159.     WHERE A.inventory_id = vCostAllocToProductId;
  160.    
  161.     INSERT INTO tt_journal_trx_item
  162.     (session_id, tenant_id, journal_trx_id, line_no,
  163.     ref_doc_type_id, ref_id,
  164.     partner_id, product_id, cashbank_id, ou_rc_id,
  165.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  166.     coa_id, curr_code, qty, uom_id,
  167.     amount, journal_date, type_rate,
  168.     numerator_rate, denominator_rate, journal_desc, remark,
  169.     gl_curr_code, gl_amount)
  170.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  171.         A.doc_type_id, B.inventory_item_id,
  172.         A.partner_id, B.product_id, vEmptyId, vEmptyId,
  173.         vEmptyId, vSignDebit, vProductCOA, vEmptyId,
  174.         f_get_product_coa_group_product(A.tenant_id, B.product_id), B.curr_code, B.qty_realization, B.base_uom_id,
  175.         B.amount , A.doc_date, vTypeRate,
  176.         1, 1, 'PRODUCT_STOCK', B.remark,
  177.         B.curr_code, B.amount
  178.     FROM in_inventory A, in_inventory_item B
  179.     WHERE A.inventory_id = vCostAllocToProductId AND
  180.         A.inventory_id = B.inventory_id;
  181.        
  182.     INSERT INTO tt_journal_trx_item
  183.     (session_id, tenant_id, journal_trx_id, line_no,
  184.     ref_doc_type_id, ref_id,
  185.     partner_id, product_id, cashbank_id, ou_rc_id,
  186.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  187.     coa_id, curr_code, qty, uom_id,
  188.     amount, journal_date, type_rate,
  189.     numerator_rate, denominator_rate, journal_desc, remark,
  190.     gl_curr_code, gl_amount)
  191.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  192.         vEmptyId, vEmptyId,
  193.         vEmptyId, vEmptyId, vEmptyId, B.ou_rc_id,
  194.         B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
  195.         D.coa_id, B.curr_code, 0, vEmptyId,
  196.         B.amount, A.doc_date, vTypeRate,
  197.         1, 1, 'COST_ALLOC_PRODUCT', vEmptyValue,
  198.         B.gl_curr_code, B.gl_amount
  199.     FROM in_inventory A, in_inventory_alloc_cost_detail B, m_activity_gl D
  200.     WHERE A.inventory_id = vCostAllocToProductId AND
  201.         A.inventory_id = B.inventory_id AND
  202.         B.activity_gl_id = D.activity_gl_id;
  203.        
  204.     INSERT INTO gl_journal_trx_item
  205.     (tenant_id, journal_trx_id, line_no,
  206.     ref_doc_type_id, ref_id,
  207.     partner_id, product_id, cashbank_id, ou_rc_id,
  208.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  209.     coa_id, curr_code, qty, uom_id,
  210.     amount, journal_date, type_rate,
  211.     numerator_rate, denominator_rate, journal_desc, remark,
  212.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  213.     gl_curr_code, gl_amount)
  214.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  215.         A.ref_doc_type_id, A.ref_id,
  216.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  217.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  218.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  219.         A.amount, A.journal_date, A.type_rate,
  220.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  221.         0, vDatetime, vUserId, vDatetime, vUserId,
  222.         A.gl_curr_code, A.gl_amount
  223.     FROM tt_journal_trx_item A
  224.     WHERE A.session_id = pSessionId AND
  225.         A.journal_desc = 'PRODUCT_STOCK';
  226.        
  227.     INSERT INTO gl_journal_trx_mapping
  228.     (tenant_id, journal_trx_id, line_no,
  229.     ref_doc_type_id, ref_id,
  230.     partner_id, product_id, cashbank_id, ou_rc_id,
  231.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  232.     coa_id, curr_code, qty, uom_id,
  233.     amount, journal_date, type_rate,
  234.     numerator_rate, denominator_rate, journal_desc, remark,
  235.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  236.     gl_curr_code, gl_amount)
  237.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  238.         A.ref_doc_type_id, A.ref_id,
  239.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  240.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  241.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  242.         A.amount, A.journal_date, A.type_rate,
  243.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  244.         0, vDatetime, vUserId, vDatetime, vUserId,
  245.         A.gl_curr_code, A.gl_amount
  246.     FROM tt_journal_trx_item A
  247.     WHERE A.session_id = pSessionId AND
  248.         A.journal_desc = 'COST_ALLOC_PRODUCT';
  249.  
  250.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  251.     DELETE FROM tt_in_product_price_balance WHERE session_id = pSessionId;
  252.    
  253. END;
  254. $BODY$
  255.   LANGUAGE plpgsql VOLATILE
  256.   COST 100;
  257. ALTER FUNCTION in_submit_cost_alloc_to_product(bigint, character varying, character varying)
  258.   OWNER TO sts;
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