abirama62

func_costing_finish_goods

Mar 17th, 2021
792
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint)
  2.  
  3. -- DROP FUNCTION in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint);
  4.  
  5. CREATE OR REPLACE FUNCTION in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint)
  6.   RETURNS void AS
  7. $BODY$
  8. DECLARE
  9.  
  10.     pTenantId           ALIAS FOR $1;
  11.     pSessionId          ALIAS FOR $2;
  12.     pYearMonth          ALIAS FOR $3;
  13.     pOuId               ALIAS FOR $4;
  14.     pDatetime           ALIAS FOR $5;
  15.     pUserId             ALIAS FOR $6;
  16.  
  17.     vNextYearMonth          character varying(6);
  18.     vCurrGL                 character varying(5);
  19.     vFlagNo                 character varying(1);
  20.     vFlagYes                character varying(1);
  21.     vEmptyValue             character varying(1);
  22.     vEmptyId                bigint;
  23.     vDocTypeAwal            bigint;
  24.     vGroupProductFG         character varying(5);
  25.     vGroupProductAssembly           character varying(10) :='ASSEMBLY';
  26.  
  27.     vRoundingDigit          integer;
  28.     vTypeRate               character varying(5);
  29.     vStatusRelease          character varying(1);
  30.  
  31.     vGenerateAdjustmentStockAmount      character varying(1);
  32.     vParamGenerateAdjustmentStockAmount character varying := 'generate.adjustment.stock.amount.on.costing.finish.goods';
  33.     vCountItemToGenerate                bigint := 0;
  34. BEGIN
  35.  
  36.     vFlagNo := 'N';
  37.     vFlagYes := 'Y';
  38.     vDocTypeAwal := -99;
  39.     vEmptyId := -99;
  40.     vEmptyValue := ' ';
  41.     vGroupProductFG := 'FG';
  42.  
  43.     vTypeRate := 'COM';
  44.     vStatusRelease := 'R';
  45.  
  46.     SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
  47.  
  48.     vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
  49.     vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
  50.  
  51.     vGenerateAdjustmentStockAmount := f_get_value_system_config_by_param_code(pTenantId,vParamGenerateAdjustmentStockAmount);
  52.  
  53.     DELETE FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId;
  54.  
  55.     DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
  56.  
  57.     DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
  58.  
  59.     DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
  60.  
  61.     DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
  62.  
  63.     DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
  64.  
  65.     DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
  66.  
  67.     DELETE FROM tt_in_summary_monthly_assembly_cogs WHERE session_id = pSessionId;
  68.  
  69.     DELETE FROM tt_in_summary_monthly_product_assembly WHERE session_id = pSessionId;
  70.  
  71.     DELETE FROM tt_in_summary_monthly_amount_remain_assembly WHERE session_id = pSessionId;
  72.  
  73.     /*
  74.      * costing product hanya berdasarkan ou business unit, jadi untuk sementara sub_ou_id diisi dengan -99
  75.      */
  76.  
  77.     /*
  78.      * membuat summary nilai pembelian berdasarkan hasil jurnal
  79.      * untuk transaksi receive goods
  80.      * NK, 16 Feb 2015 : Pembelian Internal menghasilkan dokumen semua sesuai doc type Receive Goods
  81.      * Receive Goods : 111
  82.      */
  83.     INSERT INTO in_summary_monthly_amount
  84.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  85.     doc_type_id, product_id, base_uom_id, qty,
  86.     gl_curr_code, gl_amount, flg_amount,
  87.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  88.     SELECT pYearMonth, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  89.         A.doc_type_id, C.product_id, C.uom_id, SUM(C.qty),
  90.         C.gl_curr_code, SUM(C.gl_amount), 'PURCH_PRICE',
  91.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  92.     FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
  93.     WHERE A.tenant_id = pTenantId AND
  94.         A.ou_bu_id = pOuId AND
  95.         A.status_doc = vStatusRelease AND
  96.         A.doc_date = B.string_date AND
  97.         B.year_month_date = pYearMonth AND
  98.         A.journal_trx_id = C.journal_trx_id AND
  99.         A.doc_type_id = D.doc_type_id AND
  100.         D.ledger_code IN ('PURCH') AND
  101.         C.journal_desc = 'PRODUCT_STOCK' AND
  102.         A.doc_type_id IN (111) AND
  103.         C.product_id = E.product_id
  104.     GROUP BY A.tenant_id, A.ou_bu_id, A.doc_type_id, C.product_id, C.uom_id, C.gl_curr_code;
  105.  
  106.     /*
  107.      * ambil daftar product yang akan diproses,hny yang group FINISH GOODS
  108.      * dan berikan tanda jika barang tersebut adalah barang assembly
  109.      * NOT USED ANYMORE, NK 31 DES 2013, USE VIEW
  110.     INSERT INTO tt_in_product
  111.     (session_id, product_id, base_uom_id, flg_assembly)
  112.     SELECT pSessionId, A.product_id, A.base_uom_id, vFlagNo
  113.     FROM m_product A, m_ctgr_product B, m_group_product C
  114.     WHERE A.ctgr_product_id = B.ctgr_product_id AND
  115.         B.group_product_id = C.group_product_id AND
  116.         C.group_product_code = vGroupProductFG;
  117.  
  118.     UPDATE tt_in_product SET flg_assembly = vFlagYes
  119.     WHERE EXISTS (SELECT 1 FROM m_ext_product A
  120.                 WHERE tt_in_product.product_id = A.product_id);
  121.      */
  122.     /*
  123.      * ambil data receive goods dari in_summary_monthly_amount yang product Finish Goods
  124.      * NK, 16 Feb 2015 : receive goods internal menggunakan doc type receive goods
  125.      */
  126.     INSERT INTO tt_in_doc_product_price
  127.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  128.      doc_type_id, ref_id, doc_no, doc_date,
  129.      curr_code, amount, qty, uom_id,
  130.      numerator_rate, denominator_rate,
  131.      gl_curr_code, gl_amount)
  132.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.product_id,
  133.         A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
  134.         A.gl_curr_code, A.gl_amount, A.qty, A.base_uom_id,
  135.         1, 1,
  136.         A.gl_curr_code, A.gl_amount
  137.     FROM in_summary_monthly_amount A, vw_finish_goods_product E
  138.     WHERE A.date_year_month = pYearMonth AND
  139.         A.doc_type_id IN (111) AND
  140.         A.tenant_id = pTenantId AND
  141.         A.ou_bu_id = pOuId AND
  142.         A.product_id = E.product_id;
  143.  
  144.     /*
  145.      * ambil data adjusment stok amount
  146.      * jika curr <> curr G/L, maka lakukan hitung rate untuk ke nilai sesuai valuta G/L
  147.      * 522 : adj stock amount ( mempengaruhi COGS )
  148.      * 523 : adj stock amount balance ( mempengaruhi saldo akhir )
  149.      * 528 : costing allocation to product ( mempengaruhi COGS )
  150.      */
  151.     INSERT INTO tt_in_doc_product_price
  152.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  153.      doc_type_id, ref_id, doc_no, doc_date,
  154.      curr_code, amount, qty, uom_id,
  155.      numerator_rate, denominator_rate,
  156.      gl_curr_code, gl_amount)
  157.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId, A.product_id,
  158.         A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  159.         A.curr_code, A.amount, A.qty, A.uom_id,
  160.         1, 1, vCurrGL, ROUND( A.amount, vRoundingDigit)
  161.     FROM in_product_price_balance A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  162.     WHERE A.doc_date = C.string_date AND
  163.         C.year_month_date = pYearMonth AND
  164.         A.tenant_id = pTenantId AND
  165.         A.ou_id = B.ou_id AND
  166.         B.ou_bu_id = pOuId AND
  167.         A.doc_type_id IN (522, 523, 528) AND
  168.         A.product_id = E.product_id;
  169.  
  170.     -- hitung nilai pembukuan untuk transaksi adjusment stok amount
  171.     UPDATE tt_in_doc_product_price SET numerator_rate = B.amount_to, denominator_rate = B.amount_from
  172.     FROM m_exchange_rate B
  173.     WHERE tt_in_doc_product_price.session_id = pSessionId AND
  174.         tt_in_doc_product_price.tenant_id = B.tenant_id AND
  175.         B.type_exchange_rate = vTypeRate AND
  176.         tt_in_doc_product_price.doc_date = B.date_from AND
  177.         tt_in_doc_product_price.curr_code = B.curr_code_from AND
  178.         tt_in_doc_product_price.gl_curr_code = B.curr_code_to AND
  179.         tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
  180.         tt_in_doc_product_price.curr_code <> vCurrGL;
  181.  
  182.     UPDATE tt_in_doc_product_price SET gl_amount = ROUND(amount * numerator_rate / denominator_rate, vRoundingDigit)
  183.     WHERE tt_in_doc_product_price.session_id = pSessionId AND
  184.           tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
  185.           tt_in_doc_product_price.curr_code <> vCurrGL;
  186.  
  187.     /*
  188.      * product non assembly
  189.      * ambil data transaksi yg tambah stok, tp belum memiliki nilai
  190.      * 1.return note : 502
  191.      * 2.adj stok qty plus : 521
  192.      * 3.adj stok outlet qty plus : 413
  193.      */
  194.     INSERT INTO tt_in_add_product_unamount
  195.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  196.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  197.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId, A.product_id,
  198.         A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
  199.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  200.     WHERE A.ou_id = B.ou_id AND
  201.         B.ou_bu_id = pOuId AND
  202.         A.doc_date = C.string_date AND
  203.         C.year_month_date = pYearMonth AND
  204.         A.doc_type_id = 502 AND
  205.         A.product_id = E.product_id;
  206.  
  207.     INSERT INTO tt_in_add_product_unamount
  208.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  209.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  210.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId, A.product_id,
  211.         A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
  212.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  213.     WHERE A.ou_id = B.ou_id AND
  214.         B.ou_bu_id = pOuId AND
  215.         A.doc_date = C.string_date AND
  216.         C.year_month_date = pYearMonth AND
  217.         A.doc_type_id IN ( 521, 413 ) AND
  218.         A.product_id = E.product_id AND
  219.         A.qty > 0;
  220.  
  221. /* NK, 25 Jan 2014, di submit adj stok qty diubah sehingga log product balance stock akan ada 2 kelompok,
  222.  * kelompok yang qty > 0 dan qty < 0
  223.  
  224.     INSERT INTO tt_in_add_product_unamount
  225.     (session_id, tenant_id, ou_id, sub_ou_id, product_id,
  226.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  227.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
  228.         A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
  229.     FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
  230.     WHERE A.tenant_id = pTenantId AND
  231.         A.doc_date = C.string_date AND
  232.         C.year_month_date = pYearMonth AND
  233.         A.inventory_id = B.inventory_id AND
  234.         A.ou_from_id = D.ou_id AND
  235.         D.ou_bu_id = pOuId AND
  236.         A.doc_type_id = 521 AND
  237.         B.qty_realization > 0 AND
  238.         B.product_id = E.product_id;
  239.  
  240.     INSERT INTO tt_in_add_product_unamount
  241.     (session_id, tenant_id, ou_id, sub_ou_id, product_id,
  242.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  243.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
  244.         A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
  245.     FROM i_trx_inventory A, i_trx_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
  246.     WHERE A.tenant_id = pTenantId AND
  247.         A.doc_date = C.string_date AND
  248.         C.year_month_date = pYearMonth AND
  249.         A.trx_inventory_id = B.trx_inventory_id AND
  250.         A.ou_id = D.ou_id AND
  251.         D.ou_bu_id = pOuId AND
  252.         A.doc_type_id = 413 AND
  253.         B.qty_realization > 0 AND
  254.         B.product_id = E.product_id;
  255.  */
  256.     /*
  257.      * buat data yang akan menjadi acuan harga standard
  258.      * 1.data purch price di bulan bersangkutan
  259.      * 2.data nilai barang di awal bulan
  260.      * 3.data master harga standard
  261.      */
  262.     INSERT INTO tt_in_product_base_price
  263.     (session_id, tenant_id, ou_id, product_id,
  264.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  265.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
  266.         'PURCH_PRICE', SUM(A.qty), A.uom_id, A.gl_curr_code, SUM(A.gl_amount)
  267.     FROM tt_in_doc_product_price A
  268.     WHERE A.session_id = pSessionId AND
  269.         A.doc_type_id IN ( 111)
  270.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
  271.         A.uom_id, A.gl_curr_code;
  272.  
  273.     INSERT INTO tt_in_product_base_price
  274.     (session_id, tenant_id, ou_id, product_id,
  275.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  276.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.product_id,
  277.         'BGN_BALANCE', SUM(A.qty), A.base_uom_id, A.gl_curr_code, SUM(A.gl_amount)
  278.     FROM in_summary_monthly_amount A, vw_finish_goods_product E
  279.     WHERE A.date_year_month = pYearMonth AND
  280.         A.doc_type_id = vDocTypeAwal AND
  281.         A.tenant_id = pTenantId AND
  282.         A.ou_bu_id = pOuId AND
  283.         A.product_id = E.product_id
  284.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id,
  285.         A.base_uom_id, A.gl_curr_code;
  286.  
  287.     INSERT INTO tt_in_product_base_price
  288.     (session_id, tenant_id, ou_id, product_id,
  289.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  290.     SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
  291.         'STANDARD_PRICE', 0, E.base_uom_id, A.curr_code, SUM(A.amount)
  292.     FROM in_product_standard_cogs A, vw_finish_goods_product E
  293.     WHERE A.tenant_id = pTenantId AND
  294.         A.ou_id = pOuId AND
  295.         A.year_month_date = pYearMonth AND
  296.         A.product_id = E.product_id
  297.     GROUP BY A.tenant_id, A.ou_id, A.product_id, A.curr_code, E.base_uom_id;
  298.  
  299.     /*
  300.      * isi nilai data tt_in_add_product_unamount dengan urutan :
  301.      * 1. update dengan data flg amount STANDARD_PRICE
  302.      * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
  303.      * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
  304.      */
  305.  
  306.     UPDATE tt_in_add_product_unamount SET gl_amount = tt_in_add_product_unamount.qty * A.gl_amount, flg_amount = A.flg_amount
  307.     FROM tt_in_product_base_price A
  308.     WHERE A.session_id = pSessionId AND
  309.         tt_in_add_product_unamount.session_id = A.session_id AND
  310.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  311.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  312.         tt_in_add_product_unamount.product_id = A.product_id AND
  313.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND
  314.         A.flg_amount = 'STANDARD_PRICE';
  315.  
  316.     UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  317.     FROM tt_in_product_base_price A
  318.     WHERE A.session_id = pSessionId AND
  319.         tt_in_add_product_unamount.session_id = A.session_id AND
  320.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  321.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  322.         tt_in_add_product_unamount.product_id = A.product_id AND
  323.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND
  324.         A.flg_amount = 'BGN_BALANCE';
  325.  
  326.     UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  327.     FROM tt_in_product_base_price A
  328.     WHERE A.session_id = pSessionId AND
  329.         tt_in_add_product_unamount.session_id = A.session_id AND
  330.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  331.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  332.         tt_in_add_product_unamount.product_id = A.product_id AND
  333.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND
  334.         A.flg_amount = 'PURCH_PRICE';
  335.  
  336. ------------------------------------------------------------------------------------------------------------------------
  337.     /*
  338.      * HS, 20200428
  339.      * Isi otomatis harga standard berdasarkan :
  340.      * a. costing bulan sebelumnya atau
  341.      * b. harga standard bulan sebelumnya, jika costingnya hasil nya 0
  342.      */
  343.      -- a. costing bulan sebelumnya atau
  344.     WITH from_monthly_cogs_previous_months AS (
  345.         SELECT MAX(A.date_year_month) AS date_year_month, A.tenant_id, A.ou_id, A.product_id
  346.         FROM in_summary_monthly_cogs A
  347.         WHERE A.avg_price <> 0
  348.         GROUP BY A.tenant_id, A.ou_id, A.product_id
  349.     )
  350.     UPDATE tt_in_add_product_unamount A
  351.     SET gl_amount = A.qty * C.avg_price,
  352.         flg_amount = 'COSTING_PREVMONTH'
  353.     FROM from_monthly_cogs_previous_months B
  354.     INNER JOIN in_summary_monthly_cogs C ON B.tenant_id = C.tenant_id AND
  355.                                             B.date_year_month = C.date_year_month AND
  356.                                             B.ou_id = C.ou_id AND
  357.                                             B.product_id = C.product_id
  358.     WHERE A.session_id = pSessionId AND
  359.           A.tenant_id = B.tenant_id AND
  360.           A.ou_bu_id = B.ou_id AND
  361.           A.product_id = B.product_id AND
  362.           A.flg_amount = vEmptyValue;
  363.  
  364.     -- b. harga standard bulan sebelumnya
  365.     WITH from_standard_price_previous_months AS (
  366.         SELECT MAX(A.year_month_date) AS year_month_date, A.tenant_id, A.ou_id, A.product_id
  367.         FROM in_product_standard_cogs A
  368.         WHERE A.amount <> 0
  369.         GROUP BY A.tenant_id, A.ou_id, A.product_id
  370.     )
  371.     UPDATE tt_in_add_product_unamount A
  372.     SET gl_amount = A.qty * C.amount,
  373.         flg_amount = 'STD_PRC_PREVMONTH'
  374.     FROM from_standard_price_previous_months B
  375.     INNER JOIN in_product_standard_cogs C ON B.tenant_id = C.tenant_id AND
  376.                                              B.year_month_date = C.year_month_date AND
  377.                                              B.ou_id = C.ou_id AND
  378.                                              B.product_id = C.product_id
  379.     WHERE A.session_id = pSessionId AND
  380.           A.tenant_id = B.tenant_id AND
  381.           A.ou_bu_id = B.ou_id AND
  382.           A.product_id = B.product_id AND
  383.           A.flg_amount = vEmptyValue;
  384. ------------------------------------------------------------------------------------------------------------------------
  385.  
  386.  
  387.  
  388. /*
  389.  *  menghitung harga rata rata product (non assembly ) berdasarkan :
  390.  *  1. harga purchasing
  391.  *  2. harga adj stock amount ( bukan yg balance amount ), cost allocation product
  392.  *  3. transaksi tambah stok qty, dengan harga sudah diproses : retur jual, adj stok qty > 0
  393.  *  4. harga saldo awal
  394.  */
  395.     INSERT INTO tt_in_summary_monthly_product
  396.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  397.     doc_type_id, product_id, base_uom_id, qty,
  398.     gl_curr_code, gl_amount, flg_amount)
  399.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  400.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  401.         A.gl_curr_code, SUM(A.gl_amount), 'PURCH_PRICE'
  402.     FROM tt_in_doc_product_price A
  403.     WHERE A.session_id = pSessionId AND
  404.         A.doc_type_id IN (111)
  405.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  406.  
  407.     INSERT INTO tt_in_summary_monthly_product
  408.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  409.     doc_type_id, product_id, base_uom_id, qty,
  410.     gl_curr_code, gl_amount, flg_amount)
  411.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  412.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  413.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  414.     FROM tt_in_doc_product_price A
  415.     WHERE A.session_id = pSessionId AND
  416.         A.doc_type_id IN ( 522, 528 )
  417.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  418.  
  419.     INSERT INTO tt_in_summary_monthly_product
  420.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  421.     doc_type_id, product_id, base_uom_id, qty,
  422.     gl_curr_code, gl_amount, flg_amount)
  423.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  424.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  425.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  426.     FROM tt_in_add_product_unamount A
  427.     WHERE A.session_id = pSessionId
  428.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code, A.flg_amount;
  429.  
  430.     INSERT INTO tt_in_summary_monthly_product
  431.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  432.     doc_type_id, product_id, base_uom_id, qty,
  433.     gl_curr_code, gl_amount, flg_amount)
  434.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  435.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  436.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  437.     FROM in_summary_monthly_amount A, vw_finish_goods_product E
  438.     WHERE A.date_year_month = pYearMonth AND
  439.         A.doc_type_id = vDocTypeAwal AND
  440.         A.ou_bu_id = pOuId AND
  441.         A.tenant_id = pTenantId AND
  442.         A.product_id = E.product_id
  443.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id, A.gl_curr_code, A.flg_amount;
  444.  
  445.     /*
  446.      * hitung harga rata-rata sebagai dasar harga pokok
  447.      */
  448.     INSERT INTO tt_in_summary_monthly_cogs
  449.     (session_id, date_year_month, tenant_id, ou_id,
  450.     product_id, qty_total, curr_code, amount_total, avg_price)
  451.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
  452.         A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), 0
  453.     FROM tt_in_summary_monthly_product A
  454.     WHERE A.session_id = pSessionId
  455.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;
  456.  
  457.     UPDATE tt_in_summary_monthly_cogs
  458.       SET avg_price = amount_total / qty_total
  459.     WHERE session_id = pSessionId AND
  460.       qty_total <> 0;
  461.  
  462.     /*
  463.      * memberikan nilai untuk transaksi stok yang mengurangi qty, sesuai harga rata rata bulan ini
  464.      * transaksi selain adj stok qty dan adj stok qty outlet , yang qty < 0
  465.      * transaksi pemakaian Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop
  466.      * transaksi pemakaian Nota klaim (511), DO (311) , POS Shop (431), Pos Shop In Shop(431)
  467.      * NK, 16 Feb 2015 : tambahan transaksi DO Receipt ( 526 )
  468.      * Sales Invoice Konsinyasi(354), DO Internal tidak jadi dipakai(154)
  469.      * Return POS Shop, Return POS SHop In SHop belum ada
  470.      *
  471.      * WTC, 20161208, Tambahan jenis dokumen: GTI Receipt Lost (537)
  472.      * HS, 20200421, Tambahan jenis dokumen : GTO (533), GTI (535), GTI Receipt (536)
  473.      * HS, 20200427, untuk transaksi pemakaian Nota klaim (511) -- nilai amount nya bukan dari harga rata-rata tp diambil dari jurnal spt di RG
  474.      */
  475.     INSERT INTO tt_in_summary_monthly_product
  476.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  477.     doc_type_id, product_id, base_uom_id, qty,
  478.     gl_curr_code, gl_amount, flg_amount)
  479.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  480.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  481.         vCurrGL, 0, 'MONTHLY_AVG'
  482.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  483.     WHERE A.ou_id = B.ou_id AND
  484.         B.ou_bu_id = pOuId AND
  485.         A.doc_date = C.string_date AND
  486.         C.year_month_date = pYearMonth AND
  487.         A.doc_type_id IN (311,431,526,537,533,535,536) AND
  488.         A.product_id = E.product_id
  489.     GROUP BY A.tenant_id, B.ou_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  490.  
  491.     -- khusus untuk DO POS, ambil dari in_log yg doc nya POS
  492.     INSERT INTO tt_in_summary_monthly_product
  493.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  494.     doc_type_id, product_id, base_uom_id, qty,
  495.     gl_curr_code, gl_amount, flg_amount)
  496.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  497.         431, A.product_id, A.base_uom_id, SUM(A.qty),
  498.         vCurrGL, 0, 'MONTHLY_AVG'
  499.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  500.     WHERE A.ou_id = B.ou_id AND
  501.         B.ou_bu_id = pOuId AND
  502.         A.doc_date = C.string_date AND
  503.         C.year_month_date = pYearMonth AND
  504.         A.doc_type_id IN (401,402,403,404,405,406) AND
  505.         A.product_id = E.product_id
  506.     GROUP BY A.tenant_id, B.ou_bu_id, A.product_id, A.base_uom_id
  507.     HAVING SUM(A.qty) <> 0;
  508.  
  509.  
  510.     INSERT INTO tt_in_summary_monthly_product
  511.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  512.     doc_type_id, product_id, base_uom_id, qty,
  513.     gl_curr_code, gl_amount, flg_amount)
  514.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  515.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  516.         vCurrGL, 0, 'MONTHLY_AVG'
  517.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
  518.     WHERE A.ou_id = B.ou_id AND
  519.         B.ou_bu_id = pOuId AND
  520.         A.doc_date = C.string_date AND
  521.         C.year_month_date = pYearMonth AND
  522.         A.doc_type_id IN (521, 413) AND
  523.         A.product_id = E.product_id AND
  524.         A.qty < 0
  525.     GROUP BY A.tenant_id, B.ou_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  526.  
  527. /*  NK, 25 Jan 2014, submit adj stok qty diubah sehingga menghasilkan 2 jenis log data product balance stock
  528.  *  qty < 0 dan qty > 0 dipisahkan
  529.     INSERT INTO tt_in_summary_monthly_product
  530.     (session_id, date_year_month, tenant_id, ou_id, sub_ou_id,
  531.     doc_type_id, product_id, base_uom_id, qty,
  532.     gl_curr_code, gl_amount, flg_amount)
  533.     SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_id,
  534.         A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
  535.         vCurrGL, 0, 'MONTHLY_AVG'
  536.     FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_finish_goods_product E
  537.     WHERE A.ou_from_id = C.ou_id AND
  538.         C.ou_bu_id = pOuId AND
  539.         A.inventory_id = B.inventory_id AND
  540.         A.doc_date = D.string_date AND
  541.         D.date_year_month = pYearMonth AND
  542.         A.doc_type_id IN (521, 413) AND
  543.         B.qty_realization < 0 AND
  544.         B.product_id = E.product_id
  545.     GROUP BY A.tenant_id, C.ou_bu_id, C.ou_id, A.doc_type_id, B.product_id, B.base_uom_id;
  546. */
  547.     UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
  548.     FROM tt_in_summary_monthly_cogs A
  549.     WHERE A.session_id = pSessionId AND
  550.         tt_in_summary_monthly_product.session_id = A.session_id AND
  551.         tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
  552.         tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
  553.         tt_in_summary_monthly_product.product_id = A.product_id AND
  554.         tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,537,533,535,536) AND
  555.         tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
  556.         A.qty_total <> 0;
  557.  
  558.     /*
  559.      * HS, 20200427, ambil transaksi pengurang stock
  560.      * - pemakaian Nota klaim (511), nilainya diambil dari jurnal spt di RG
  561.      */
  562.     INSERT INTO tt_in_summary_monthly_product
  563.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  564.     doc_type_id, product_id, base_uom_id, qty,
  565.     gl_curr_code, gl_amount, flg_amount)
  566.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  567.         A.doc_type_id, C.product_id, C.uom_id, SUM(C.qty),
  568.         C.gl_curr_code, SUM(C.gl_amount), 'TRANSACTION'
  569.     FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
  570.     WHERE A.tenant_id = pTenantId AND
  571.         A.ou_bu_id = pOuId AND
  572.         A.status_doc = vStatusRelease AND
  573.         A.doc_date = B.string_date AND
  574.         B.year_month_date = pYearMonth AND
  575.         A.journal_trx_id = C.journal_trx_id AND
  576.         A.doc_type_id = D.doc_type_id AND
  577.         D.ledger_code IN ('INV') AND
  578.         C.journal_desc = 'PRODUCT_STOCK' AND
  579.         A.doc_type_id IN (511) AND
  580.         C.product_id = E.product_id
  581.     GROUP BY A.tenant_id, A.ou_bu_id, A.doc_type_id, C.product_id, C.uom_id, C.gl_curr_code;
  582.  
  583.     /*
  584.      * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
  585.      * 1. ambil data adj stok balance amount ( adjust nilai akhir saldo barang )
  586.      * 2. hitung saldo akhir
  587.      */
  588.     INSERT INTO tt_in_summary_monthly_product
  589.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  590.     doc_type_id, product_id, base_uom_id, qty,
  591.     gl_curr_code, gl_amount, flg_amount)
  592.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  593.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  594.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  595.     FROM tt_in_doc_product_price A
  596.     WHERE A.session_id = pSessionId AND
  597.         A.doc_type_id = 523
  598.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  599.  
  600.     /*
  601.      * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
  602.      * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
  603.      */
  604.     INSERT INTO tt_in_summary_monthly_amount_remain
  605.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  606.     doc_type_id, product_id, base_uom_id, qty,
  607.     gl_curr_code, gl_amount, flg_amount, summary_monthly_product_id)
  608.     SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  609.         vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
  610.         vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
  611.     FROM tt_in_summary_monthly_product A
  612.     WHERE A.session_id = pSessionId
  613.     GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;
  614.  
  615.     -- Cari id data sebagai penampung alokasi nilai
  616.     UPDATE tt_in_summary_monthly_amount_remain
  617.         SET summary_monthly_product_id = (
  618.             SELECT MAX(A.summary_monthly_product_id)
  619.             FROM tt_in_summary_monthly_product A
  620.             WHERE A.session_id = pSessionId AND
  621.                 tt_in_summary_monthly_amount_remain.session_id = A.session_id AND
  622.                 tt_in_summary_monthly_amount_remain.date_year_month = A.date_year_month AND
  623.                 tt_in_summary_monthly_amount_remain.tenant_id = A.tenant_id AND
  624.                 tt_in_summary_monthly_amount_remain.ou_bu_id = A.ou_bu_id AND
  625.                 tt_in_summary_monthly_amount_remain.product_id = A.product_id AND
  626.                 A.doc_type_id IN (311,431,526,521,413,537,533,535,536) AND
  627.                 A.flg_amount = 'MONTHLY_AVG')
  628.     WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;
  629.  
  630.     /*
  631.      * NK, 16 Feb 2015
  632.      * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  633.      * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  634.  
  635.     INSERT INTO in_summary_monthly_zero_qty_amount
  636.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  637.     product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  638.     doc_type_id, qty, gl_amount, remark)
  639.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  640.         A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  641.         B.doc_type_id, B.qty, B.gl_amount, vGroupProductFG
  642.     FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  643.     WHERE A.session_id = pSessionId AND
  644.         A.summary_monthly_product_id <> vEmptyId AND
  645.         B.summary_monthly_product_id = A.summary_monthly_product_id;
  646.     */
  647.  
  648.     IF (vGenerateAdjustmentStockAmount = vFlagYes) THEN
  649.  
  650.         /**
  651.          * Adrian, Feb 08, 2018
  652.          * Generate Adjustment Stock Amount untuk tt_in_summary_monthly_amount_remain
  653.          * yang memiliki gl_amount <> 0, qty = 0, dan summary_monthly_product_id = vEmptyId
  654.          */
  655.         INSERT INTO tt_data_summary_monthly_amount_exclude(
  656.             session_id, tenant_id, date_year_month,
  657.             ou_bu_id, ou_branch_id, ou_sub_bu_id,
  658.             product_id, qty, base_uom_id,
  659.             gl_curr_code, gl_amount)
  660.         SELECT pSessionId, A.tenant_id, A.date_year_month,
  661.                 A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  662.                 A.product_id, A.qty AS qty, A.base_uom_id,
  663.                 A.gl_curr_code, -1 * A.gl_amount AS gl_amount
  664.         FROM tt_in_summary_monthly_amount_remain A
  665.         WHERE A.session_id = pSessionId AND
  666.             A.tenant_id = pTenantId AND
  667.             A.gl_amount <> 0 AND
  668.             A.qty = 0 AND
  669.             A.summary_monthly_product_id = vEmptyId;
  670.  
  671.         SELECT COUNT(1) FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId INTO vCountItemToGenerate;
  672.  
  673.         IF (vCountItemToGenerate > 0) THEN
  674.  
  675.             PERFORM in_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, pYearMonth, pOuId, pDatetime, pUserId, vGroupProductFG);
  676.  
  677.         END IF;
  678.  
  679.         /*
  680.          * NK, 16 Feb 2015
  681.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  682.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  683.          */
  684.         /**
  685.          * Adrian, Feb 08, 2018
  686.          * Ubah filter menjadi gl_amount <> 0 dan qty = 0
  687.          */
  688.         INSERT INTO in_summary_monthly_zero_qty_amount
  689.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  690.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  691.         doc_type_id, qty, gl_amount, remark)
  692.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  693.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  694.             B.doc_type_id, B.qty, B.gl_amount, vGroupProductFG
  695.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  696.         WHERE A.session_id = pSessionId AND
  697.             --A.summary_monthly_product_id <> vEmptyId AND
  698.             A.gl_amount <> 0 AND A.qty = 0 AND
  699.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  700.  
  701.     ELSE
  702.  
  703.         /*
  704.          * NK, 16 Feb 2015
  705.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  706.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  707.          */
  708.         INSERT INTO in_summary_monthly_zero_qty_amount
  709.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  710.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  711.         doc_type_id, qty, gl_amount, remark)
  712.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  713.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  714.             vEmptyId, B.qty, B.gl_amount, vGroupProductFG
  715.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  716.         WHERE A.session_id = pSessionId AND
  717.             A.summary_monthly_product_id <> vEmptyId AND
  718.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  719.  
  720.     END IF;
  721.  
  722.     UPDATE tt_in_summary_monthly_product SET gl_amount = tt_in_summary_monthly_product.gl_amount - A.gl_amount
  723.     FROM tt_in_summary_monthly_amount_remain A
  724.     WHERE A.session_id = pSessionId AND
  725.         A.summary_monthly_product_id <> vEmptyId AND
  726.         tt_in_summary_monthly_product.summary_monthly_product_id = A.summary_monthly_product_id;
  727.  
  728.     IF ((vGenerateAdjustmentStockAmount = vFlagYes) AND (vCountItemToGenerate > 0)) THEN
  729.  
  730.         /**
  731.          * Adrian, Feb 08, 2018
  732.          * Update tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  733.          * dan product id nya terdaftar di
  734.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  735.          */
  736.         UPDATE tt_in_summary_monthly_product Z
  737.         SET gl_amount = Z.gl_amount - A.gl_amount
  738.         FROM tt_in_summary_monthly_amount_remain A
  739.         WHERE Z.session_id = pSessionId AND
  740.             Z.tenant_id = pTenantId AND
  741.             Z.session_id = A.session_id AND
  742.             Z.date_year_month = A.date_year_month AND
  743.             Z.tenant_id = A.tenant_id AND
  744.             Z.ou_bu_id = A.ou_bu_id AND
  745.             Z.product_id = A.product_id AND
  746.             Z.doc_type_id = 522 AND
  747.             Z.flg_amount = 'TRANSACTION' AND
  748.             A.gl_amount <> 0 AND
  749.             A.qty = 0 AND
  750.             A.summary_monthly_product_id = vEmptyId;
  751.  
  752.         /**
  753.          * Adrian, Feb 08, 2018
  754.          * Insert tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  755.          * jika product id yang terdaftar di
  756.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  757.          * belum terdapat dalam tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  758.          */
  759.         INSERT INTO tt_in_summary_monthly_product(
  760.                 session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  761.                 doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  762.         SELECT A.session_id, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  763.             522, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, (-1 * A.gl_amount), 'TRANSACTION'
  764.         FROM tt_in_summary_monthly_amount_remain A
  765.         WHERE A.session_id = pSessionId AND
  766.             A.tenant_id = pTenantId AND
  767.             A.gl_amount <> 0 AND
  768.             A.qty = 0 AND
  769.             A.summary_monthly_product_id = vEmptyId AND
  770.             NOT EXISTS(
  771.                 SELECT 1
  772.                 FROM tt_in_summary_monthly_product B
  773.                 WHERE B.session_id = A.session_id AND
  774.                     B.date_year_month = A.date_year_month AND
  775.                     B.tenant_id = A.tenant_id AND
  776.                     B.ou_bu_id = A.ou_bu_id AND
  777.                     B.product_id = A.product_id AND
  778.                     B.doc_type_id = 522 AND
  779.                     B.flg_amount = 'TRANSACTION'
  780.             );
  781.  
  782.     END IF;
  783.  
  784.     INSERT INTO tt_in_summary_monthly_product
  785.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  786.     doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  787.     SELECT A.session_id, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  788.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
  789.     FROM tt_in_summary_monthly_amount_remain A
  790.     WHERE A.session_id = pSessionId AND A.qty <> 0;
  791.  
  792.     INSERT INTO in_summary_monthly_cogs
  793.     (date_year_month, tenant_id, ou_id, product_id,
  794.     qty_total, curr_code, amount_total, avg_price,
  795.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  796.     SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
  797.             A.qty_total, A.curr_code, A.amount_total, A.avg_price,
  798.             0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  799.     FROM tt_in_summary_monthly_cogs A
  800.     WHERE A.session_id = pSessionId;
  801.  
  802.     INSERT INTO in_summary_monthly_amount
  803.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  804.     doc_type_id, product_id, base_uom_id, qty,
  805.     gl_curr_code, gl_amount, flg_amount,
  806.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  807.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  808.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  809.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  810.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  811.     FROM tt_in_summary_monthly_product A
  812.     WHERE A.session_id = pSessionId AND
  813.             A.date_year_month = pYearMonth AND
  814.             A.doc_type_id NOT IN (vDocTypeAwal, 111);
  815.  
  816.     INSERT INTO in_summary_monthly_amount
  817.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  818.     doc_type_id, product_id, base_uom_id, qty,
  819.     gl_curr_code, gl_amount, flg_amount,
  820.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  821.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  822.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  823.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  824.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  825.     FROM tt_in_summary_monthly_product A
  826.     WHERE A.session_id = pSessionId AND
  827.         A.date_year_month = vNextYearMonth;
  828.  
  829. /*
  830.  * PERHITUNGAN PRODUCT ASSEMBLY
  831.  * 1. tentukan harga awal transaksi assembly : 541
  832.  * 2. tentukan harga transaksi return, adj
  833.  * 3. ambil nilai saldo awal product assembly
  834.  * 4. tentukan harga rata assembly
  835.  * 5. tentukan nilai transaksi pemakaian assembly
  836.  */
  837.     INSERT INTO tt_in_doc_product_assembly_price
  838.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  839.     ref_id, doc_type_id, child_product_id, child_qty, child_amount)
  840.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  841.         A.log_product_balance_stock_id, A.doc_type_id,
  842.         E.child_product_id, E.qty_base_uom, 0
  843.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly D, m_product_assembly E
  844.     WHERE A.ou_id = B.ou_id AND
  845.         B.ou_bu_id = pOuId AND
  846.         A.doc_date = C.string_date AND
  847.         C.year_month_date = pYearMonth AND
  848.         A.product_id = D.product_id AND
  849.         D.product_id = E.parent_product_id AND
  850.         A.doc_type_id IN (541);
  851.  
  852.     /*
  853.      * memberikan nilai untuk child product, krn child product pasti bukan product assembly
  854.      */
  855.     UPDATE tt_in_doc_product_assembly_price SET child_amount = ROUND(child_qty * (A.amount_total / A.qty_total), vRoundingDigit)
  856.     FROM tt_in_summary_monthly_cogs A
  857.     WHERE A.session_id = pSessionId AND
  858.         tt_in_doc_product_assembly_price.session_id = A.session_id AND
  859.         tt_in_doc_product_assembly_price.tenant_id = A.tenant_id AND
  860.         tt_in_doc_product_assembly_price.ou_bu_id = A.ou_id AND
  861.         tt_in_doc_product_assembly_price.child_product_id = A.product_id;
  862.  
  863.     /*
  864.      * hitung nilai assembly product, sum dari amount child product
  865.      */
  866.     INSERT INTO tt_in_product_assembly_price_balance
  867.     (session_id, tenant_id, ou_id, product_id, product_balance_id,
  868.     product_buy_date, partner_id, doc_type_id, ref_id,
  869.     doc_no, doc_date, curr_code, amount, qty, uom_id)
  870.     SELECT pSessionId, B.tenant_id, pOuId, B.product_id, B.product_balance_id,
  871.         B.doc_date, B.partner_id, B.doc_type_id, B.ref_id,
  872.         B.doc_no, B.doc_date, vCurrGL, SUM(A.total_child_amount * B.qty), SUM(B.qty), B.base_uom_id
  873.     FROM (SELECT A.ref_id, SUM(A.child_amount) AS total_child_amount
  874.             FROM tt_in_doc_product_assembly_price A
  875.             WHERE A.session_id = pSessionId
  876.             GROUP BY A.ref_id) A,
  877.             in_log_product_balance_stock B
  878.     WHERE A.ref_id = B.log_product_balance_stock_id
  879.     GROUP BY B.tenant_id, B.product_id, B.product_balance_id, B.doc_date, B.partner_id, B.doc_type_id, B.ref_id, B.doc_no, B.doc_date, B.base_uom_id;
  880.  
  881.     /*
  882.      * product assembly,harus cari child product nya
  883.      * ambil data transaksi yg tambah stok, tp belum memiliki nilai
  884.      * 1.return note : 502
  885.      * 2.adj stok qty plus : 521
  886.      */
  887.     INSERT INTO tt_in_add_product_assembly_unamount
  888.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  889.     ref_id, doc_type_id, flg_amount,
  890.      child_product_id, child_qty, child_amount)
  891.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, vEmptyId, vEmptyId,
  892.         A.log_product_balance_stock_id, A.doc_type_id, vEmptyValue,
  893.         F.child_product_id, F.qty_base_uom, 0
  894.     FROM in_log_product_balance_stock A, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
  895.     WHERE A.tenant_id = pTenantId AND
  896.         A.doc_date = C.string_date AND
  897.         C.year_month_date = pYearMonth AND
  898.         A.ou_id = D.ou_id AND
  899.         D.ou_bu_id = pOuId AND
  900.         A.doc_type_id = 502 AND
  901.         A.product_id = E.product_id AND
  902.         E.product_id = F.parent_product_id;
  903.  
  904.     INSERT INTO tt_in_add_product_assembly_unamount
  905.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  906.     ref_id, doc_type_id, flg_amount,
  907.      child_product_id, child_qty, child_amount)
  908.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, vEmptyId, vEmptyId,
  909.         A.log_product_balance_stock_id, A.doc_type_id, vEmptyValue,
  910.         F.child_product_id, F.qty_base_uom, 0
  911.     FROM in_log_product_balance_stock A, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
  912.     WHERE A.tenant_id = pTenantId AND
  913.         A.doc_date = C.string_date AND
  914.         C.year_month_date = pYearMonth AND
  915.         A.ou_id = D.ou_id AND
  916.         D.ou_bu_id = pOuId AND
  917.         A.doc_type_id = 521 AND
  918.         A.product_id = E.product_id AND
  919.         E.product_id = F.parent_product_id AND
  920.         A.qty > 0;
  921.  
  922. /*  NK, 26 Jan 2014, submit adj stok akan menghasilkan 2 jenis log data
  923.  *  qty > 0 dan qty < 0 akan dipisahkan dalam row data terpisah
  924.     INSERT INTO tt_in_add_product_assembly_unamount
  925.     (session_id, tenant_id, ou_id, sub_ou_id,
  926.     ref_id, doc_type_id, flg_amount,
  927.      child_product_id, child_qty, child_amount)
  928.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id,
  929.         B.inventory_item_id, A.doc_type_id, vEmptyValue,
  930.         F.child_product_id, F.qty_base_uom, 0
  931.     FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
  932.     WHERE A.tenant_id = pTenantId AND
  933.         A.doc_date = C.string_date AND
  934.         C.year_month_date = pYearMonth AND
  935.         A.inventory_id = B.inventory_id AND
  936.         A.ou_from_id = D.ou_id AND
  937.         D.ou_bu_id = pOuId AND
  938.         A.doc_type_id = 521 AND
  939.         B.product_id = E.product_id AND
  940.         E.product_id = F.parent_product_id AND
  941.         B.qty_realization > 0;
  942. */
  943.  
  944.     /*
  945.      * isi nilai data tt_in_add_product_assembly_unamount untuk nilai child_amount dengan urutan :
  946.      * 1. update dengan data flg amount STANDARD_PRICE
  947.      * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
  948.      * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
  949.      */
  950.  
  951.     UPDATE tt_in_add_product_assembly_unamount SET child_amount = child_qty * A.gl_amount, flg_amount = A.flg_amount
  952.     FROM tt_in_product_base_price A
  953.     WHERE A.session_id = pSessionId AND
  954.         tt_in_add_product_assembly_unamount.session_id = A.session_id AND
  955.         tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
  956.         tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
  957.         tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
  958.         tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
  959.         A.flg_amount = 'STANDARD_PRICE';
  960.  
  961.     UPDATE tt_in_add_product_assembly_unamount SET child_amount = ROUND(child_qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  962.     FROM tt_in_product_base_price A
  963.     WHERE A.session_id = pSessionId AND
  964.         tt_in_add_product_assembly_unamount.session_id = A.session_id AND
  965.         tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
  966.         tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
  967.         tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
  968.         tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
  969.         A.flg_amount = 'BGN_BALANCE';
  970.  
  971.     UPDATE tt_in_add_product_assembly_unamount SET child_amount = ROUND(child_qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  972.     FROM tt_in_product_base_price A
  973.     WHERE A.session_id = pSessionId AND
  974.         tt_in_add_product_assembly_unamount.session_id = A.session_id AND
  975.         tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
  976.         tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
  977.         tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
  978.         tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
  979.         A.flg_amount = 'PURCH_PRICE';
  980.  
  981.  
  982. ------------------------------------------------------------------------------------------------------------------------
  983.     /*
  984.      * HS, 20200428
  985.      * Isi otomatis harga standard (for assembly) berdasarkan :
  986.      * a. costing bulan sebelumnya atau
  987.      * b. harga standard bulan sebelumnya, jika costingnya hasil nya 0
  988.      */
  989.      -- a. costing bulan sebelumnya (for assembly)
  990.     WITH from_monthly_cogs_previous_months AS (
  991.         SELECT MAX(A.date_year_month) AS date_year_month, A.tenant_id, A.ou_id, A.product_id
  992.         FROM in_summary_monthly_assembly_cogs A
  993.         WHERE A.avg_price <> 0
  994.         GROUP BY A.tenant_id, A.ou_id, A.product_id
  995.     )
  996.     UPDATE tt_in_add_product_assembly_unamount A
  997.     SET child_amount = A.child_qty * C.avg_price,
  998.         flg_amount = 'COSTING_PREVMONTH'
  999.     FROM from_monthly_cogs_previous_months B
  1000.     INNER JOIN in_summary_monthly_assembly_cogs C ON B.tenant_id = C.tenant_id AND
  1001.                                                      B.date_year_month = C.date_year_month AND
  1002.                                                      B.ou_id = C.ou_id AND
  1003.                                                      B.product_id = C.product_id
  1004.     WHERE A.session_id = pSessionId AND
  1005.           A.tenant_id = B.tenant_id AND
  1006.           A.ou_bu_id = B.ou_id AND
  1007.           A.child_product_id = B.product_id AND
  1008.           A.flg_amount = vEmptyValue;
  1009.  
  1010.     -- b. harga standard bulan sebelumnya (for assembly)
  1011.     WITH from_standard_price_previous_months AS (
  1012.         SELECT MAX(A.year_month_date) AS year_month_date, A.tenant_id, A.ou_id, A.product_id
  1013.         FROM in_product_standard_cogs A
  1014.         WHERE A.amount <> 0
  1015.         GROUP BY A.tenant_id, A.ou_id, A.product_id
  1016.     )
  1017.     UPDATE tt_in_add_product_assembly_unamount A
  1018.     SET child_amount = A.child_qty * C.amount,
  1019.         flg_amount = 'STD_PRC_PREVMONTH'
  1020.     FROM from_standard_price_previous_months B
  1021.     INNER JOIN in_product_standard_cogs C ON B.tenant_id = C.tenant_id AND
  1022.                                              B.year_month_date = C.year_month_date AND
  1023.                                              B.ou_id = C.ou_id AND
  1024.                                              B.product_id = C.product_id
  1025.     WHERE A.session_id = pSessionId AND
  1026.           A.tenant_id = B.tenant_id AND
  1027.           A.ou_bu_id = B.ou_id AND
  1028.           A.child_product_id = B.product_id AND
  1029.           A.flg_amount = vEmptyValue;
  1030. ------------------------------------------------------------------------------------------------------------------------
  1031.  
  1032.  
  1033. /*
  1034.  * buat data summary harga product assembly :
  1035.  * 1. assembly
  1036.  * 2. add product assembly without amount
  1037.  *    a.return note
  1038.  *    b.adj stock qty plus
  1039.  */
  1040.     INSERT INTO tt_in_summary_monthly_product_assembly
  1041.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1042.     doc_type_id, product_id, base_uom_id, qty,
  1043.     gl_curr_code, gl_amount, flg_amount)
  1044.     SELECT pSessionId, pYearMonth, pTenantId, A.ou_bu_id, vEmptyId, vEmptyId,
  1045.         B.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty),
  1046.         vCurrGL, SUM(A.total_child_amount * B.qty), 'ASSEMBLY'
  1047.     FROM (SELECT A.ref_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
  1048.             FROM tt_in_doc_product_assembly_price A
  1049.             WHERE A.session_id = pSessionId
  1050.             GROUP BY A.ref_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
  1051.         in_log_product_balance_stock B
  1052.     WHERE A.ref_id = B.log_product_balance_stock_id
  1053.     GROUP BY A.ou_bu_id, B.doc_type_id, B.product_id, B.base_uom_id;
  1054.  
  1055.     INSERT INTO tt_in_summary_monthly_product_assembly
  1056.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1057.     doc_type_id, product_id, base_uom_id, qty,
  1058.     gl_curr_code, gl_amount, flg_amount)
  1059.     SELECT pSessionId, pYearMonth, pTenantId,  A.ou_bu_id, vEmptyId, vEmptyId,
  1060.         B.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty),
  1061.         vCurrGL, SUM(A.total_child_amount * B.qty), 'ASSEMBLY'
  1062.     FROM (SELECT A.ref_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
  1063.             FROM tt_in_add_product_assembly_unamount A
  1064.             WHERE A.session_id = pSessionId AND
  1065.                 A.doc_type_id = 502
  1066.             GROUP BY A.ref_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
  1067.             in_log_product_balance_stock B
  1068.     WHERE A.ref_id = B.log_product_balance_stock_id
  1069.     GROUP BY  A.ou_bu_id, B.doc_type_id, B.product_id, B.base_uom_id;
  1070.  
  1071.     INSERT INTO tt_in_summary_monthly_product_assembly
  1072.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1073.     doc_type_id, product_id, base_uom_id, qty,
  1074.     gl_curr_code, gl_amount, flg_amount)
  1075.     SELECT pSessionId, pYearMonth, pTenantId,  A.ou_bu_id, vEmptyId, vEmptyId,
  1076.         A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
  1077.         vCurrGL, SUM(A.total_child_amount * B.qty_realization), 'ASSEMBLY'
  1078.     FROM (SELECT A.ref_id, A.doc_type_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
  1079.             FROM tt_in_add_product_assembly_unamount A
  1080.             WHERE A.session_id = pSessionId AND
  1081.                 A.doc_type_id = 521
  1082.             GROUP BY A.ref_id, A.doc_type_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
  1083.             in_inventory_item B
  1084.     WHERE A.ref_id = B.inventory_item_id
  1085.     GROUP BY  A.ou_bu_id, A.doc_type_id, B.product_id, B.base_uom_id;
  1086.  
  1087.     /*
  1088.      * ambil saldo awal product assembly
  1089.      */
  1090.     INSERT INTO tt_in_summary_monthly_product_assembly
  1091.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1092.     doc_type_id, product_id, base_uom_id, qty,
  1093.     gl_curr_code, gl_amount, flg_amount)
  1094.     SELECT pSessionId, pYearMonth, pTenantId, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1095.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  1096.         A.gl_curr_code, SUM(A.gl_amount), 'BGN_BALANCE'
  1097.     FROM in_summary_monthly_amount A, vw_finish_goods_product_assembly E
  1098.     WHERE A.date_year_month = pYearMonth AND
  1099.         A.doc_type_id = vDocTypeAwal AND
  1100.         A.ou_bu_id = pOuId AND
  1101.         A.tenant_id = pTenantId AND
  1102.         A.product_id = E.product_id
  1103.     GROUP BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id, A.gl_curr_code;
  1104.  
  1105.     /*
  1106.      * hitung harga rata-rata sebagai dasar harga pokok product assembly
  1107.      */
  1108.     INSERT INTO tt_in_summary_monthly_assembly_cogs
  1109.     (session_id, date_year_month, tenant_id, ou_id,
  1110.     product_id, qty_total, curr_code, amount_total, avg_price)
  1111.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
  1112.         A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), SUM(A.gl_amount) / SUM(A.qty)
  1113.     FROM tt_in_summary_monthly_product_assembly A
  1114.     WHERE A.session_id = pSessionId
  1115.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;
  1116.  
  1117.     /*
  1118.      * buat data transaksi yang menggunakan product assembly
  1119.      * DO (311), DO Receipt(526)  POS Shop (431), POS Shop In Shop (431)
  1120.      * Adj Stok Qty (521) < 0
  1121.      * NK, 16 Feb 2015 :
  1122.      * DO Internal, Sales Invoice Konsinyasi,Return POS Shop,Return POS Shop In Shop tidak digunakan
  1123.      *
  1124.      * WTC, 20161208, Tambahan jenis dokumen GTI Receipt Lost (537)
  1125.      * HS, 20200421, Tambahan jenis dokumen : GTO (533), GTI (535), GTI Receipt (536)
  1126.      */
  1127.     INSERT INTO tt_in_summary_monthly_product_assembly
  1128.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1129.     doc_type_id, product_id, base_uom_id, qty,
  1130.     gl_curr_code, gl_amount, flg_amount)
  1131.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  1132.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  1133.         vCurrGL, 0, 'MONTHLY_AVG'
  1134.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
  1135.     WHERE A.ou_id = B.ou_id AND
  1136.         B.ou_bu_id = pOuId AND
  1137.         A.doc_date = C.string_date AND
  1138.         C.year_month_date = pYearMonth AND
  1139.         A.doc_type_id IN (311,431,526,537,533,535,536) AND
  1140.         A.product_id = E.product_id
  1141.     GROUP BY A.tenant_id, B.ou_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  1142.  
  1143.     INSERT INTO tt_in_summary_monthly_product_assembly
  1144.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1145.     doc_type_id, product_id, base_uom_id, qty,
  1146.     gl_curr_code, gl_amount, flg_amount)
  1147.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  1148.         431, A.product_id, A.base_uom_id, SUM(A.qty),
  1149.         vCurrGL, 0, 'MONTHLY_AVG'
  1150.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
  1151.     WHERE A.ou_id = B.ou_id AND
  1152.         B.ou_bu_id = pOuId AND
  1153.         A.doc_date = C.string_date AND
  1154.         C.year_month_date = pYearMonth AND
  1155.         A.doc_type_id IN (401,402,403,404,405,406) AND
  1156.         A.product_id = E.product_id
  1157.     GROUP BY A.tenant_id, B.ou_bu_id, A.product_id, A.base_uom_id
  1158.     HAVING SUM(A.qty) <> 0;
  1159.  
  1160.  
  1161.     INSERT INTO tt_in_summary_monthly_product_assembly
  1162.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1163.     doc_type_id, product_id, base_uom_id, qty,
  1164.     gl_curr_code, gl_amount, flg_amount)
  1165.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, vEmptyId, vEmptyId,
  1166.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  1167.         vCurrGL, 0, 'MONTHLY_AVG'
  1168.     FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
  1169.     WHERE A.ou_id = B.ou_id AND
  1170.         B.ou_bu_id = pOuId AND
  1171.         A.doc_date = C.string_date AND
  1172.         C.year_month_date = pYearMonth AND
  1173.         A.doc_type_id IN (521) AND
  1174.         A.product_id = E.product_id AND
  1175.         A.qty < 0
  1176.     GROUP BY A.tenant_id, B.ou_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  1177. /* NK, 26 Jan 2014
  1178.  * adj stok qty sudah dipisahkan untuk data log menjadi qty > 0 dan qty < 0
  1179.     INSERT INTO tt_in_summary_monthly_product_assembly
  1180.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1181.     doc_type_id, product_id, base_uom_id, qty,
  1182.     gl_curr_code, gl_amount, flg_amount)
  1183.     SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_branch_id, C.ou_sub_bu_id,
  1184.         A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
  1185.         vCurrGL, 0, 'MONTHLY_AVG'
  1186.     FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_finish_goods_product_assembly E
  1187.     WHERE A.ou_from_id = C.ou_id AND
  1188.         C.ou_bu_id = pOuId AND
  1189.         A.inventory_id = B.inventory_id AND
  1190.         A.doc_date = D.string_date AND
  1191.         D.date_year_month = pYearMonth AND
  1192.         A.doc_type_id IN (521) AND
  1193.         B.qty_realization < 0 AND
  1194.         B.product_id = E.product_id
  1195.     GROUP BY A.tenant_id, C.ou_bu_id, C.ou_branch_id, C.ou_sub_bu_id, A.doc_type_id, B.product_id, B.base_uom_id;
  1196. */
  1197.     UPDATE tt_in_summary_monthly_product_assembly SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
  1198.     FROM tt_in_summary_monthly_assembly_cogs A
  1199.     WHERE A.session_id = pSessionId AND
  1200.         tt_in_summary_monthly_product_assembly.session_id = A.session_id AND
  1201.         tt_in_summary_monthly_product_assembly.tenant_id = A.tenant_id AND
  1202.         tt_in_summary_monthly_product_assembly.ou_bu_id = A.ou_id AND
  1203.         tt_in_summary_monthly_product_assembly.product_id = A.product_id AND
  1204.         tt_in_summary_monthly_product_assembly.doc_type_id IN (311,431,526,521,537,533,535,536) AND
  1205.         tt_in_summary_monthly_product_assembly.flg_amount = 'MONTHLY_AVG' AND
  1206.         A.qty_total <> 0;
  1207.  
  1208.     /*
  1209.      * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
  1210.      * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
  1211.      */
  1212.     INSERT INTO tt_in_summary_monthly_amount_remain_assembly
  1213.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1214.     doc_type_id, product_id, base_uom_id, qty,
  1215.     gl_curr_code, gl_amount, flg_amount, summary_monthly_product_assembly_id)
  1216.     SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  1217.         vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
  1218.         vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
  1219.     FROM tt_in_summary_monthly_product_assembly A
  1220.     WHERE A.session_id = pSessionId
  1221.     GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;
  1222.  
  1223.     -- Cari id data sebagai penampung alokasi nilai
  1224.     UPDATE tt_in_summary_monthly_amount_remain_assembly
  1225.         SET summary_monthly_product_assembly_id = (
  1226.             SELECT MAX(A.summary_monthly_product_assembly_id)
  1227.             FROM tt_in_summary_monthly_product_assembly A
  1228.             WHERE A.session_id = pSessionId AND
  1229.                 tt_in_summary_monthly_amount_remain_assembly.session_id = A.session_id AND
  1230.                 tt_in_summary_monthly_amount_remain_assembly.date_year_month = A.date_year_month AND
  1231.                 tt_in_summary_monthly_amount_remain_assembly.tenant_id = A.tenant_id AND
  1232.                 tt_in_summary_monthly_amount_remain_assembly.ou_bu_id = A.ou_bu_id AND
  1233.                 tt_in_summary_monthly_amount_remain_assembly.product_id = A.product_id AND
  1234.                 A.doc_type_id IN (311,431,526,521,537,533,535,536) AND
  1235.                 A.flg_amount = 'MONTHLY_AVG')
  1236.     WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;
  1237.  
  1238.     /*
  1239.      * NK, 16 Feb 2015
  1240.      * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  1241.      * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  1242.  
  1243.     INSERT INTO in_summary_monthly_zero_qty_amount
  1244.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1245.     product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  1246.     doc_type_id, qty, gl_amount)
  1247.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1248.         A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  1249.         B.doc_type_id, B.qty, B.gl_amount
  1250.     FROM tt_in_summary_monthly_amount_remain_assembly A, tt_in_summary_monthly_product_assembly B
  1251.     WHERE A.session_id = pSessionId AND
  1252.         A.summary_monthly_product_assembly_id <> vEmptyId AND
  1253.         B.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;
  1254.     */
  1255.  
  1256.     IF (vGenerateAdjustmentStockAmount = vFlagYes) THEN
  1257.  
  1258.         /**
  1259.          * Adrian, Feb 08, 2018
  1260.          * Generate Adjustment Stock Amount untuk tt_in_summary_monthly_amount_remain
  1261.          * yang memiliki gl_amount <> 0, qty = 0, dan summary_monthly_product_id = vEmptyId
  1262.          */
  1263.         INSERT INTO tt_data_summary_monthly_amount_exclude(
  1264.             session_id, tenant_id, date_year_month,
  1265.             ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1266.             product_id, qty, base_uom_id,
  1267.             gl_curr_code, gl_amount)
  1268.         SELECT pSessionId, A.tenant_id, A.date_year_month,
  1269.                 A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1270.                 A.product_id, A.qty AS qty, A.base_uom_id,
  1271.                 A.gl_curr_code, -1 * A.gl_amount AS gl_amount
  1272.         FROM tt_in_summary_monthly_amount_remain_assembly A
  1273.         WHERE A.session_id = pSessionId AND
  1274.             A.tenant_id = pTenantId AND
  1275.             A.gl_amount <> 0 AND
  1276.             A.qty = 0 AND
  1277.             A.summary_monthly_product_assembly_id = vEmptyId;
  1278.  
  1279.         SELECT COUNT(1) FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId  INTO vCountItemToGenerate;
  1280.  
  1281.         IF (vCountItemToGenerate > 0) THEN
  1282.  
  1283.             PERFORM in_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, pYearMonth, pOuId, pDatetime, pUserId, vGroupProductAssembly);
  1284.  
  1285.         END IF;
  1286.  
  1287.         /*
  1288.          * NK, 16 Feb 2015
  1289.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  1290.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  1291.          */
  1292.         /**
  1293.          * Adrian, Feb 08, 2018
  1294.          * Ubah filter menjadi gl_amount <> 0 dan qty = 0
  1295.          */
  1296.         INSERT INTO in_summary_monthly_zero_qty_amount
  1297.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1298.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  1299.         doc_type_id, qty, gl_amount)
  1300.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1301.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  1302.             B.doc_type_id, B.qty, B.gl_amount
  1303.         FROM tt_in_summary_monthly_amount_remain_assembly A, tt_in_summary_monthly_product_assembly B
  1304.         WHERE A.session_id = pSessionId AND
  1305.             --A.summary_monthly_product_id <> vEmptyId AND
  1306.             A.gl_amount <> 0 AND A.qty = 0 AND
  1307.             B.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;
  1308.  
  1309.     ELSE
  1310.  
  1311.         /*
  1312.          * NK, 16 Feb 2015
  1313.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  1314.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  1315.          */
  1316.         INSERT INTO in_summary_monthly_zero_qty_amount
  1317.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1318.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  1319.         doc_type_id, qty, gl_amount)
  1320.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1321.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  1322.             vEmptyId, B.qty, B.gl_amount
  1323.         FROM tt_in_summary_monthly_amount_remain_assembly A, tt_in_summary_monthly_product_assembly B
  1324.         WHERE A.session_id = pSessionId AND
  1325.             A.summary_monthly_product_assembly_id <> vEmptyId AND
  1326.             B.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;
  1327.  
  1328.     END IF;
  1329.  
  1330.     UPDATE tt_in_summary_monthly_product_assembly SET gl_amount = tt_in_summary_monthly_product_assembly.gl_amount - A.gl_amount
  1331.     FROM tt_in_summary_monthly_amount_remain_assembly A
  1332.     WHERE A.session_id = pSessionId AND
  1333.         A.summary_monthly_product_assembly_id <> vEmptyId AND
  1334.         tt_in_summary_monthly_product_assembly.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;
  1335.  
  1336.     IF ((vGenerateAdjustmentStockAmount = vFlagYes) AND (vCountItemToGenerate > 0)) THEN
  1337.  
  1338.         /**
  1339.          * Adrian, Feb 08, 2018
  1340.          * Update tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  1341.          * dan product id nya terdaftar di
  1342.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  1343.          */
  1344.         UPDATE tt_in_summary_monthly_product_assembly Z
  1345.         SET gl_amount = Z.gl_amount - A.gl_amount
  1346.         FROM tt_in_summary_monthly_amount_remain_assembly A
  1347.         WHERE Z.session_id = pSessionId AND
  1348.             Z.tenant_id = pTenantId AND
  1349.             Z.session_id = A.session_id AND
  1350.             Z.date_year_month = A.date_year_month AND
  1351.             Z.tenant_id = A.tenant_id AND
  1352.             Z.ou_bu_id = A.ou_bu_id AND
  1353.             Z.product_id = A.product_id AND
  1354.             Z.doc_type_id = 522 AND
  1355.             Z.flg_amount = 'TRANSACTION' AND
  1356.             A.gl_amount <> 0 AND
  1357.             A.qty = 0 AND
  1358.             A.summary_monthly_product_assembly_id = vEmptyId;
  1359.  
  1360.         /**
  1361.          * Adrian, Feb 08, 2018
  1362.          * Insert tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  1363.          * jika product id yang terdaftar di
  1364.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  1365.          * belum terdapat dalam tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  1366.          */
  1367.         INSERT INTO tt_in_summary_monthly_product_assembly(
  1368.                 session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1369.                 doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  1370.         SELECT A.session_id, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1371.             522, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, (-1 * A.gl_amount), 'TRANSACTION'
  1372.         FROM tt_in_summary_monthly_amount_remain_assembly A
  1373.         WHERE A.session_id = pSessionId AND
  1374.             A.tenant_id = pTenantId AND
  1375.             A.gl_amount <> 0 AND
  1376.             A.qty = 0 AND
  1377.             A.summary_monthly_product_assembly_id = vEmptyId AND
  1378.             NOT EXISTS(
  1379.                 SELECT 1
  1380.                 FROM tt_in_summary_monthly_product_assembly B
  1381.                 WHERE B.session_id = A.session_id AND
  1382.                     B.date_year_month = A.date_year_month AND
  1383.                     B.tenant_id = A.tenant_id AND
  1384.                     B.ou_bu_id = A.ou_bu_id AND
  1385.                     B.product_id = A.product_id AND
  1386.                     B.doc_type_id = 522 AND
  1387.                     B.flg_amount = 'TRANSACTION'
  1388.             );
  1389.  
  1390.     END IF;
  1391.     /*
  1392.      * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
  1393.      */
  1394.     INSERT INTO tt_in_summary_monthly_product_assembly
  1395.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1396.     doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  1397.     SELECT A.session_id, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1398.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
  1399.     FROM tt_in_summary_monthly_amount_remain_assembly A
  1400.     WHERE A.session_id = pSessionId AND A.qty <> 0;
  1401.  
  1402.     INSERT INTO in_summary_monthly_assembly_cogs
  1403.     (date_year_month, tenant_id, ou_id, product_id,
  1404.     qty_total, curr_code, amount_total, avg_price,
  1405.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1406.     SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
  1407.             A.qty_total, A.curr_code, A.amount_total, A.avg_price,
  1408.             0, pDatetime, pUserId, pDatetime, pUserId
  1409.     FROM tt_in_summary_monthly_assembly_cogs A
  1410.     WHERE A.session_id = pSessionId;
  1411.  
  1412.     INSERT INTO in_summary_monthly_amount
  1413.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1414.     doc_type_id, product_id, base_uom_id, qty,
  1415.     gl_curr_code, gl_amount, flg_amount,
  1416.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  1417.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1418.             A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  1419.             A.gl_curr_code, A.gl_amount, A.flg_amount,
  1420.             0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  1421.     FROM tt_in_summary_monthly_product_assembly A
  1422.     WHERE A.session_id = pSessionId AND
  1423.             A.date_year_month = pYearMonth AND
  1424.             A.doc_type_id <> vDocTypeAwal;
  1425.  
  1426.     INSERT INTO in_summary_monthly_amount
  1427.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  1428.     doc_type_id, product_id, base_uom_id, qty,
  1429.     gl_curr_code, gl_amount, flg_amount,
  1430.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  1431.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  1432.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  1433.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  1434.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
  1435.     FROM tt_in_summary_monthly_product_assembly A
  1436.     WHERE A.session_id = pSessionId AND
  1437.         A.date_year_month = vNextYearMonth;
  1438.  
  1439.     DELETE FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId;
  1440.  
  1441.     DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
  1442.  
  1443.     DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
  1444.  
  1445.     DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
  1446.  
  1447.     DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
  1448.  
  1449.     DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
  1450.  
  1451.     DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
  1452.  
  1453.     DELETE FROM tt_in_summary_monthly_assembly_cogs WHERE session_id = pSessionId;
  1454.  
  1455.     DELETE FROM tt_in_summary_monthly_product_assembly WHERE session_id = pSessionId;
  1456.  
  1457.     DELETE FROM tt_in_summary_monthly_amount_remain_assembly WHERE session_id = pSessionId;
  1458.  
  1459.  END;
  1460. $BODY$
  1461.   LANGUAGE plpgsql VOLATILE
  1462.   COST 100;
  1463.   /
RAW Paste Data