samuel025

Receive Goods

Jul 31st, 2021
1,072
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: memeflorist.pu_submit_receive_goods(bigint, character varying, character varying)
  2.  
  3. -- DROP FUNCTION memeflorist.pu_submit_receive_goods(bigint, character varying, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION memeflorist.pu_submit_receive_goods(
  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.         vRgId                   bigint;
  18.         vUserId                 bigint;
  19.         vAutonumIdDo            bigint;
  20.         vAutonumIdSI            bigint;
  21.         vDocNoNewDo             character varying;
  22.         vDocNoNewSI             character varying;
  23.         vDatetime               character varying(14);
  24.         vFlagInvoice            character varying(1);
  25.         vEmptyId                bigint;
  26.         vStatusRelease          character varying(1);
  27.         vStatusDraft            character varying(1);
  28.         vStatusFinal            character varying(1);
  29.         vEmptyValue             character varying(1);
  30.         vProductStatus          character varying(50);
  31.         vSignDebit              character varying(1);
  32.         vSignCredit             character varying(1);
  33.         vTypeRate               character varying(3);
  34.         vActivityCOA            character varying(10);
  35.         vProductCOA             character varying(10);
  36.         vSystemCOA              character varying(10);
  37.         vPoId                   bigint;
  38.         vUnfinishedItem         bigint;
  39.         vJournalTrxId           bigint;
  40.         vOuId                       bigint;
  41.         vSoId                       bigint;
  42.         vOuWarehouseIdForJournal    bigint;
  43.         vDocTypeSo                  bigint;
  44.         vDocTypeDo                  bigint;
  45.         vDocTypeForSelect           bigint;
  46.         vRefIdForSelect             bigint;
  47.         vZeroValue                  bigint;
  48.         vOustandingQtySO            bigint;
  49.         vQtySOFulfilled             bigint;
  50.         vQtySORequest               bigint;
  51.         vFlgDropshipSO              character varying(1);
  52.         vYes                        character varying(1);
  53.         vNo                         character varying(1);
  54.         vEmptyString                character varying(1);
  55.        
  56.         vDocJournal                 DOC_JOURNAL%ROWTYPE;
  57.         vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
  58.         vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;
  59.         result                      RECORD;
  60.        
  61.         vReceiveGoodsDocTypeId  bigint;
  62.         vRoundingModeNonTax     character varying(5);
  63.        
  64.         vRoleId                 bigint;
  65.         vFlgUserRole            character varying;
  66.        
  67.         vSchemeRG               character varying(20) := 'CB01';
  68.        
  69.         vMappingCbInSoBalanceItemId     bigint;
  70.         vCtgrPartnerIdClientCorporate   bigint;
  71.         vInvoiceArBalanceId     bigint;
  72.         vAutonumIdAllocCbIn     bigint;
  73.         vDocNoNewAllocCbIn              character varying(100);
  74.        
  75.         /*
  76.          * NK, 2 Jan 2015
  77.          * untuk kepentingan auto serial number
  78.          */
  79.         vDocDate                character varying(8);
  80.        
  81.         vOutletId               bigint;
  82.         vOuWarehouseId          bigint;
  83.         vDbVersion              character varying(10);
  84.         vCtgrPartnerId          bigint;
  85.         vPartnerBillToId            bigint;
  86.        
  87.         vStatus             character varying(1);
  88.         cStatus             character varying(1);
  89.         fStatus             character varying(1);
  90.     BEGIN
  91.         vAutonumIdAllocCbIn := -99;
  92.         vDocNoNewAllocCbIn := '';
  93.         vMappingCbInSoBalanceItemId := -99;
  94.         vInvoiceArBalanceId := -99;
  95.        
  96.         vCtgrPartnerIdClientCorporate := -99;
  97.    
  98.         vFlagInvoice := 'N';
  99.         vCtgrPartnerId := -99;
  100.         vPartnerBillToId := -99;
  101.         vEmptyId := -99;
  102.         vStatusRelease := 'R';
  103.         vStatusDraft := 'D';
  104.         vStatusFinal := 'F';
  105.         vEmptyValue := ' ';
  106.         vOutletId := -99;
  107.         vOuWarehouseId := -99;
  108.         vDbVersion := '1.0';
  109.         vDocTypeSo := 301;
  110.         vDocTypeDo := 311;
  111.         vDocTypeForSelect := -99;
  112.         vFlgDropshipSO := 'N';
  113.         vYes := 'Y';
  114.         vNo := 'N';
  115.         vRefIdForSelect := -99;
  116.         vSoId := -99;
  117.     --  vRgId := -99;
  118.         vZeroValue := 0;
  119.         vOustandingQtySO := 0;
  120.         vEmptyString := '';
  121.        
  122.         /*
  123.          * NK, 31 Des 2014
  124.          * diganti dng membaca m_product_status
  125.          */
  126.         --vProductStatus := 'GOOD';
  127.         SELECT product_status_code INTO vProductStatus
  128.         FROM m_product_status
  129.         WHERE tenant_id = pTenantId AND flg_buy = 'Y';
  130.        
  131.         SELECT A.ctgr_partner_id INTO vCtgrPartnerIdClientCorporate
  132.         FROM m_ctgr_partner A
  133.         WHERE ctgr_partner_code = 'CLIENTCORPORATE';
  134.        
  135.         vSignDebit := 'D';
  136.         vSignCredit := 'C';
  137.         vTypeRate := 'COM';
  138.         vActivityCOA := 'ACTIVITY';
  139.         vProductCOA := 'PRODUCT';
  140.         vSystemCOA := 'SYSTEM';
  141.         vUnfinishedItem := 0;
  142.        
  143.         vStatus := 'V';
  144.         cStatus := 'C';
  145.         fStatus := 'F';
  146.        
  147.         vReceiveGoodsDocTypeId := 111;
  148.         SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  149.        
  150.         SELECT A.process_message_id INTO vProcessId
  151.         FROM t_process_message A
  152.         WHERE A.tenant_id = pTenantId AND
  153.             A.process_name = 'pu_submit_receive_goods' AND
  154.             A.process_no = pProcessNo;
  155.            
  156.         SELECT CAST(A.process_parameter_value AS bigint) INTO vRgId
  157.         FROM t_process_parameter A
  158.         WHERE A.process_message_id = vProcessId AND
  159.             A.process_parameter_key = 'receiveGoodsId';
  160.        
  161.        
  162.         SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  163.         FROM t_process_parameter A
  164.         WHERE A.process_message_id = vProcessId AND
  165.             A.process_parameter_key = 'userId';
  166.    
  167.         SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  168.         FROM t_process_parameter A
  169.         WHERE A.process_message_id = vProcessId AND
  170.             A.process_parameter_key = 'datetime';
  171.    
  172.         DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;       
  173.         DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  174.         DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
  175.         DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
  176.        
  177.        
  178.        
  179.             -- Get role id, flg user role awe submit RG
  180.         SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
  181.         FROM awe_historydoc
  182.         WHERE scheme = vSchemeRG
  183.         AND doc_id = vRgId
  184.         AND activity = 'SUBMIT';
  185.        
  186.     /*
  187.      * 1. add pu_log_po_balance_item
  188.      * 2. add pu_po_balance_invoice
  189.      * 3. add pu_po_balance_invoice_tax
  190.      * 4. add in_produt_balance jika belum exists ( bisa ada serial number dan yang tidak ada serial number )
  191.      * 5. add in_product_price_balance
  192.      * 6. add/update in_product_balance_stock
  193.      * 7. add in_log_product_balance_stock
  194.      * 7b. add data log untuk outlet jika gudang nya adalah gudang outlet
  195.      * 8. add in_log_product_price_balance_stock
  196.      * 9. update status pu_receive_goods
  197.      * 10.update status pu_po_balance_item
  198.      * 10b.update status pu_po. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
  199.      * 11.add balance receive goods item
  200.      * 12.add gl_journal_trx
  201.      * 13.add gl_journal_trx_item
  202.      * 14.add gl_journal_trx_mapping
  203.      *
  204.      *
  205.      */    
  206.     /* pakai cara lain, NK, 14 Jan 2014
  207.         SELECT A.ref_id, f_get_parent_ou_bu(A.tenant_id, A.ou_id), f_get_journal_type(A.doc_type_id) INTO vPoId, vParentOuId, vJournalType
  208.         FROM pu_receive_goods A
  209.         WHERE A.receive_goods_id = vRgId;
  210.     */
  211.        
  212.         SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, A.doc_date
  213.         FROM pu_receive_goods A
  214.         WHERE A.receive_goods_id = vRgId INTO result;
  215.    
  216.         vPoId := result.ref_id;
  217.         vOuStructure := result.ou;
  218.         vDocJournal := result.doc;
  219.         vDocDate := result.doc_date;
  220.     /* 
  221.         SELECT INTO vOuStructure f_get_ou_bu_structure(A.ou_id)
  222.         FROM pu_receive_goods A
  223.         WHERE A.receive_goods_id = vRgId;
  224.    
  225.         SELECT INTO vDocJournal f_get_document_journal(A.doc_type_id)
  226.         FROM pu_receive_goods A
  227.         WHERE A.receive_goods_id = vRgId;
  228.    
  229.         SELECT A.ref_id INTO vPoId
  230.         FROM pu_receive_goods A
  231.         WHERE A.receive_goods_id = vRgId;
  232.     */
  233.        
  234.         /*
  235.          * NK, 2 Jan 2015
  236.          * memanggil function untuk membuat auto serial number
  237.          */
  238.         PERFORM pu_create_sn_receive_goods(pTenantId, pSessionId, vDocDate, vRgId, vUserId, vDatetime );
  239.        
  240.         INSERT INTO pu_log_po_balance_item
  241.         (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  242.          qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  243.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  244.         SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
  245.             B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
  246.             0, vDatetime, vUserId, vDatetime, vUserId
  247.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  248.         WHERE A.receive_goods_id = vRgId AND
  249.               A.receive_goods_id = B.receive_goods_id AND
  250.               B.ref_id = C.po_item_id;
  251.              
  252.         INSERT INTO pu_po_balance_invoice
  253.         (tenant_id, ou_id, partner_id, po_id,
  254.         ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  255.         curr_code, price_po, item_amount, flg_invoice, invoice_id,
  256.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  257.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  258.             A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
  259.             C.curr_code, C.nett_price_po,
  260.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
  261.             vFlagInvoice, vEmptyId,
  262.             0, vDatetime, vUserId, vDatetime, vUserId
  263.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  264.         WHERE A.receive_goods_id = vRgId AND
  265.               A.receive_goods_id = B.receive_goods_id AND
  266.               B.ref_id = C.po_item_id;
  267.            
  268.         INSERT INTO pu_po_balance_invoice_tax
  269.         (tenant_id, ou_id, partner_id, po_id,
  270.         ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  271.         tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  272.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  273.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  274.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
  275.             C.tax_percentage, C.curr_code,
  276.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
  277.             f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
  278.             vFlagInvoice, vEmptyId,
  279.             0, vDatetime, vUserId, vDatetime, vUserId
  280.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
  281.         WHERE A.receive_goods_id = vRgId AND
  282.               A.receive_goods_id = B.receive_goods_id AND
  283.               B.ref_id = C.po_item_id AND
  284.               C.tax_id = D.tax_id;
  285.              
  286.         /*
  287.          * tampung data item receive goods, untuk dapat digunakan dan disimpan ke product_balance, product_price_balance
  288.          */
  289.         -- Insert untuk data product yang tidak memiliki serial number
  290.         INSERT INTO tt_pu_product_balance
  291.         (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
  292.         serial_number, lot_number, product_expired_date, product_year_made,
  293.         product_price_balance_id, product_buy_date, partner_id,
  294.         doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
  295.         po_id, po_no, po_date, po_item_id,
  296.         curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  297.         SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
  298.             vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
  299.             vEmptyId, A.doc_date, A.partner_id,
  300.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
  301.             E.po_id, E.doc_no, E.doc_date, D.po_item_id,
  302.             D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
  303.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
  304.         WHERE A.receive_goods_id = vRgId AND
  305.             A.receive_goods_id = B.receive_goods_id AND
  306.             NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
  307.                         WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
  308.             NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn C
  309.                         WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
  310.             B.ref_id = D.po_item_id AND
  311.             D.po_id = E.po_id
  312.         GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
  313.             A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
  314.             E.po_id, E.doc_no, E.doc_date, D.po_item_id,
  315.             D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
  316.    
  317.         -- Insert untuk data product yang memiliki serial number dan serial number tidak di-generate
  318.         INSERT INTO tt_pu_product_balance
  319.         (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
  320.         serial_number, lot_number, product_expired_date, product_year_made,
  321.         product_price_balance_id, product_buy_date, partner_id,
  322.         doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
  323.         po_id, po_no, po_date, po_item_id,
  324.         curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  325.         SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
  326.             C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
  327.             vEmptyId, A.doc_date, A.partner_id,
  328.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
  329.             E.po_id, E.doc_no, E.doc_date, D.po_item_id,       
  330.             D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
  331.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E
  332.         WHERE A.receive_goods_id = vRgId AND
  333.             A.receive_goods_id = B.receive_goods_id AND
  334.             B.receive_goods_item_id = C.receive_goods_item_id AND
  335.             B.ref_id = D.po_item_id AND
  336.             D.po_id = E.po_id
  337.         GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
  338.             C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
  339.             A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
  340.             E.po_id, E.doc_no, E.doc_date, D.po_item_id,
  341.             D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
  342.    
  343.     /* NK, 5 Jan 2015 sudah diganti dengan function pu_create_sn_receive_goods
  344.          -- Insert untuk data product yang memiliki serial number dan serial number di-generate
  345.         INSERT INTO tt_pu_product_balance
  346.         (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
  347.         serial_number, lot_number, product_expired_date, product_year_made,
  348.         product_price_balance_id, product_buy_date, partner_id,
  349.         doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
  350.         po_id, po_no, po_date, po_item_id,
  351.         curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  352.         SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
  353.             C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
  354.             vEmptyId, A.doc_date, A.partner_id,
  355.             A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
  356.             E.po_id, E.doc_no, E.doc_date, D.po_item_id,       
  357.             D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, 1, D.base_uom_id, D.flg_stock
  358.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product_auto_sn C, pu_po_item D, pu_po E
  359.         WHERE A.receive_goods_id = vRgId AND
  360.             A.receive_goods_id = B.receive_goods_id AND
  361.             B.receive_goods_item_id = C.receive_goods_item_id AND
  362.             B.ref_id = D.po_item_id AND
  363.             D.po_id = E.po_id;
  364.     *
  365.      */                
  366.         /*
  367.          * update product_balance_id dari in_product_balance
  368.          */    
  369.         UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
  370.         FROM in_product_balance A
  371.         WHERE tt_pu_product_balance.session_id = pSessionId AND
  372.             tt_pu_product_balance.tenant_id = A.tenant_id AND
  373.             tt_pu_product_balance.product_id = A.product_id AND
  374.             tt_pu_product_balance.serial_number = A.serial_number AND
  375.             tt_pu_product_balance.lot_number = A.lot_number;
  376.            
  377.         /*
  378.          * insert data ke in_product_balance yang product_balance_id masih empty
  379.          */
  380.         INSERT INTO in_product_balance
  381.         (tenant_id, product_id,
  382.         serial_number, lot_number, product_expired_date, product_year_made,
  383.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  384.         SELECT A.tenant_id, A.product_id,
  385.             A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
  386.             0, vDatetime, vUserId, vDatetime, vUserId
  387.         FROM tt_pu_product_balance A
  388.         WHERE A.session_id = pSessionId AND
  389.             A.product_balance_id = vEmptyId
  390.         GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;
  391.            
  392.         /*
  393.          * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
  394.          */
  395.         UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
  396.         FROM in_product_balance A
  397.         WHERE tt_pu_product_balance.session_id = pSessionId AND
  398.             tt_pu_product_balance.product_balance_id = vEmptyId AND
  399.             tt_pu_product_balance.tenant_id = A.tenant_id AND
  400.             tt_pu_product_balance.product_id = A.product_id AND
  401.             tt_pu_product_balance.serial_number = A.serial_number AND
  402.             tt_pu_product_balance.lot_number = A.lot_number;
  403.    
  404.         INSERT INTO in_product_price_balance
  405.         (tenant_id, ou_id, product_id, product_balance_id,
  406.         product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
  407.         curr_code, amount, qty, uom_id,
  408.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  409.         SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
  410.             A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
  411.             A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
  412.             SUM(A.qty_int_rcv), A.base_uom_id,
  413.             0, vDatetime, vUserId, vDatetime, vUserId
  414.         FROM tt_pu_product_balance A, pu_po_item B
  415.         WHERE A.session_id = pSessionId AND
  416.             A.po_item_id = B.po_item_id
  417.         GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
  418.             A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id;
  419.        
  420.         UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
  421.         FROM in_product_price_balance A
  422.         WHERE tt_pu_product_balance.session_id = pSessionId AND
  423.             tt_pu_product_balance.tenant_id = A.tenant_id AND
  424.             tt_pu_product_balance.ou_id = A.ou_id AND
  425.             tt_pu_product_balance.product_id = A.product_id AND    
  426.             tt_pu_product_balance.product_balance_id = A.product_balance_id AND
  427.             tt_pu_product_balance.partner_id = A.partner_id AND
  428.             tt_pu_product_balance.doc_type_id = A.doc_type_id AND
  429.             tt_pu_product_balance.ref_item_id = A.ref_id AND
  430.             tt_pu_product_balance.doc_no = A.doc_no AND
  431.             tt_pu_product_balance.doc_date = A.doc_date;
  432.        
  433.         /*
  434.          * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
  435.          */
  436.         INSERT INTO tt_pu_product_balance_summary_stock
  437.                 (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
  438.                 product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  439.         SELECT  pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
  440.                 vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
  441.         FROM    tt_pu_product_balance A
  442.         WHERE   A.session_id = pSessionId
  443.         GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
  444.                  A.po_uom_id, A.base_uom_id, A.flg_stock;
  445.            
  446.         /*
  447.          * update product_balance_stock, yang sudah ada di in_product_balance_stock
  448.          */
  449.         UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv, update_datetime = vDatetime, update_user_id = vUserId,
  450.         version = version + 1
  451.         FROM tt_pu_product_balance_summary_stock A
  452.         WHERE A.session_id = pSessionId AND
  453.             in_product_balance_stock.product_id = A.product_id AND
  454.             in_product_balance_stock.tenant_id = A.tenant_id AND
  455.             in_product_balance_stock.warehouse_id = A.warehouse_id AND
  456.             in_product_balance_stock.product_balance_id = A.product_balance_id AND
  457.             in_product_balance_stock.product_status = vProductStatus AND
  458.             A.flg_stock = 'Y';
  459.    
  460.         /*
  461.          * insert data in_product_balance_stock
  462.          */
  463.         INSERT INTO in_product_balance_stock
  464.         (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
  465.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  466.         SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
  467.             A.base_uom_id, SUM(A.qty_int_rcv),
  468.             0, vDatetime, vUserId, vDatetime, vUserId
  469.         FROM tt_pu_product_balance A
  470.         WHERE A.session_id = pSessionId AND
  471.             A.flg_stock = 'Y' AND
  472.             NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
  473.                         WHERE A.tenant_id = B.tenant_id AND
  474.                             A.warehouse_id = B.warehouse_id AND
  475.                             A.product_id = B.product_id AND
  476.                             A.product_balance_id = B.product_balance_id AND
  477.                             B.product_status = vProductStatus)
  478.         GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;
  479.            
  480.         /*
  481.          * insert data product_price_balance_stock
  482.          */
  483.         INSERT INTO in_product_price_balance_stock
  484.         (tenant_id, warehouse_id, product_id, product_balance_id,
  485.         product_price_balance_id, product_status, base_uom_id, qty,
  486.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  487.         SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
  488.             A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  489.             0, vDatetime, vUserId, vDatetime, vUserId
  490.         FROM tt_pu_product_balance A
  491.         WHERE A.session_id = pSessionId AND
  492.             A.flg_stock = 'Y'
  493.         GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
  494.        
  495.         /*
  496.          * buat data log product balance stock
  497.          */
  498.         INSERT INTO in_log_product_balance_stock
  499.         (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  500.          product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  501.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  502.         SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  503.             A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  504.             0, vDatetime, vUserId, vDatetime, vUserId      
  505.         FROM tt_pu_product_balance A, m_warehouse_ou B
  506.         WHERE A.session_id = pSessionId AND
  507.             A.flg_stock = 'Y' AND
  508.             A.warehouse_id = B.warehouse_id
  509.         GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  510.             A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;
  511.    
  512.         /*
  513.          * 7b. buat data log untuk update stok outlet jika ou ini adalah ou outlet
  514.          */
  515.         -- cek dulu warehouse ou nya   
  516.         SELECT B.ou_id INTO vOuWarehouseId
  517.         FROM pu_receive_goods A
  518.         INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
  519.         WHERE receive_goods_id = vRgId;
  520.        
  521.         IF vOuWarehouseId IS NULL THEN
  522.             vOuWarehouseId = vEmptyId;
  523.         END IF;
  524.            
  525.         -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
  526.         IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
  527.             SELECT A.outlet_id INTO vOutletId
  528.             FROM i_outlet A WHERE A.ou_id = vOuWarehouseId;
  529.         END IF;
  530.        
  531.         IF vOutletId IS NULL THEN
  532.             vOutletId = vEmptyId;
  533.         END IF;
  534.        
  535.         -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
  536.         IF vOutletId <> vEmptyId THEN
  537.             vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
  538.        
  539.            
  540.             INSERT INTO i_trx_data_log(
  541.                 tenant_id, ou_id, doc_date, db_version, type_data,
  542.                 mode_log, data_log, version, create_datetime, create_user_id,
  543.                 update_datetime, update_user_id)
  544.             SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_product_balance',
  545.                    'A', '{"id":'||C.product_balance_id||',"tenantId":"'||C.tenant_id||'","productId":'||C.product_id||',"serialNumber":"'||C.serial_number||'","lotNumber":"'||C.lot_number||'","productExpiredDate":"'||C.product_expired_date||'","productYearMade":"'||C.product_year_made||'","createDateTime":"'||C.create_datetime||'","createUserId":'||C.create_user_id||',"version":'||C.version||',"updateDateTime":"'||C.update_datetime||'","updateUserId":'||C.update_user_id||'}',
  546.                    0, vDatetime, vUserId, vDatetime, vUserId
  547.             FROM pu_receive_goods A
  548.             INNER JOIN in_log_product_balance_stock B
  549.                 ON A.tenant_id = B.tenant_id
  550.                 AND A.doc_no = B.doc_no
  551.                 AND A.doc_date = B.doc_date
  552.                 AND A.doc_type_id = B.doc_type_id
  553.                 AND A.receive_goods_id = B.ref_id
  554.                 AND A.warehouse_id = B.warehouse_id
  555.             INNER JOIN in_product_balance C
  556.                 ON B.product_balance_id = C.product_balance_id
  557.             WHERE A.receive_goods_id = vRgId
  558.             GROUP BY B.tenant_id, B.ou_id, B.doc_date, C.product_balance_id;
  559.            
  560.             INSERT INTO i_trx_data_log(
  561.                 tenant_id, ou_id, doc_date, db_version, type_data,
  562.                 mode_log, data_log, version, create_datetime, create_user_id,
  563.                 update_datetime, update_user_id)
  564.             SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_log_product_balance_stock',
  565.                    'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
  566.                    0, vDatetime, vUserId, vDatetime, vUserId
  567.             FROM pu_receive_goods A
  568.             INNER JOIN in_log_product_balance_stock B
  569.                 ON A.tenant_id = B.tenant_id
  570.                 AND A.doc_no = B.doc_no
  571.                 AND A.doc_date = B.doc_date
  572.                 AND A.doc_type_id = B.doc_type_id
  573.                 AND A.receive_goods_id = B.ref_id
  574.                 AND A.warehouse_id = B.warehouse_id
  575.             WHERE A.receive_goods_id = vRgId;
  576.         END IF;
  577.    
  578.         /*
  579.          * buat data log product price balance stock
  580.          */
  581.         INSERT INTO in_log_product_price_balance_stock
  582.         (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  583.          product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
  584.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  585.         SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  586.             A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  587.             0, vDatetime, vUserId, vDatetime, vUserId
  588.         FROM tt_pu_product_balance A
  589.         WHERE A.session_id = pSessionId AND
  590.             A.flg_stock = 'Y'
  591.         GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  592.             A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
  593.            
  594.         /*
  595.          * add data balance receive goods item yang akan digunakan di inventory,
  596.          * saat akan membuat claim note
  597.          * receive_goods_item_id
  598.          */
  599.         INSERT INTO in_balance_receive_goods_item
  600.         (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
  601.           po_id, po_no, po_date, po_item_id,
  602.           qty_rcv, qty_return, po_uom_id, qty_int_rcv, 
  603.           qty_int_return, base_uom_id, status_item,
  604.           "version", create_datetime, create_user_id, update_datetime, update_user_id)
  605.         SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  606.                 A.po_id, A.po_no, A.po_date, A.po_item_id,
  607.                 SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
  608.                 0, A.base_uom_id, vStatusRelease,
  609.             0, vDatetime, vUserId, vDatetime, vUserId      
  610.         FROM tt_pu_product_balance A
  611.         WHERE A.session_id = pSessionId
  612.         GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  613.                 A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;
  614.        
  615.         UPDATE pu_receive_goods SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  616.         WHERE receive_goods_id = vRgId;
  617.        
  618.         UPDATE pu_po_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  619.         FROM pu_receive_goods_item A
  620.         WHERE pu_po_balance_item.po_item_id = A.ref_id AND
  621.             pu_po_balance_item.tenant_id = A.tenant_id AND
  622.             A.receive_goods_id = vRgId AND
  623.             pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv > 0;
  624.    
  625.         UPDATE pu_po_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
  626.         FROM pu_receive_goods_item A
  627.         WHERE pu_po_balance_item.po_item_id = A.ref_id AND
  628.             pu_po_balance_item.tenant_id = A.tenant_id AND
  629.             A.receive_goods_id = vRgId AND
  630.             pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv <= 0;
  631.            
  632.         SELECT COUNT(1) INTO vUnfinishedItem
  633.         FROM pu_po_balance_item A, pu_po_item B
  634.         WHERE A.po_item_id = B.po_item_id AND
  635.             B.po_id = vPoId AND
  636.             A.status_item = vStatusRelease;
  637.            
  638.         IF vUnfinishedItem = 0 THEN
  639.             UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
  640.             WHERE po_id = vPoId;
  641.         END IF;
  642.            
  643.         /*
  644.          * create monthly price product
  645.          */
  646.         INSERT INTO tt_pu_monthly_price_product
  647.         (session_id, tenant_id, ou_id, year_month_date,
  648.         product_id, curr_code, amount, qty, base_uom_id,
  649.          ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
  650.          flg_tax_amount, tax_id, tax_percentage)
  651.         SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
  652.             A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
  653.             SUM(A.qty_int_rcv), A.base_uom_id,
  654.             A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  655.             'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
  656.             B.flg_tax_amount, B.tax_id, B.tax_percentage
  657.         FROM tt_pu_product_balance A, pu_po_item B, dt_date C
  658.         WHERE A.session_id = pSessionId AND
  659.             A.po_item_id = B.po_item_id AND
  660.             A.doc_date = C.string_date
  661.         GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
  662.             A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date, B.flg_tax_amount, B.tax_id, B.tax_percentage;
  663.          
  664.         UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
  665.                                             qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
  666.                                             ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
  667.                                             source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
  668.                                             tax_id = A.tax_id, tax_percentage = A.tax_percentage
  669.         FROM tt_pu_monthly_price_product A
  670.         WHERE A.session_id = pSessionId AND
  671.             pu_monthly_price_product.tenant_id = A.tenant_id AND
  672.             pu_monthly_price_product.ou_id = A.ou_id AND
  673.             pu_monthly_price_product.year_month_date = A.year_month_date AND
  674.             pu_monthly_price_product.product_id = A.product_id;
  675.            
  676.         INSERT INTO pu_monthly_price_product
  677.         (tenant_id, ou_id, year_month_date, product_id,
  678.         curr_code, amount, qty, base_uom_id, source_price,
  679.         ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  680.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  681.         flg_tax_amount, tax_id, tax_percentage)
  682.         SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  683.                 A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
  684.                 A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
  685.                 0, vDatetime, vUserId, vDatetime, vUserId,
  686.                 A.flg_tax_amount, A.tax_id, A.tax_percentage
  687.         FROM tt_pu_monthly_price_product A
  688.         WHERE A.session_id = pSessionId AND
  689.             NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
  690.                         WHERE A.tenant_id = B.tenant_id AND
  691.                             A.ou_id = B.ou_id AND
  692.                             A.product_id = B.product_id AND
  693.                             A.year_month_date = B.year_month_date)
  694.         GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
  695.                  A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
  696.        
  697.         INSERT INTO pu_log_monthly_price_product
  698.         (tenant_id, ou_id, year_month_date, product_id,
  699.         curr_code, amount, qty, base_uom_id, source_price,
  700.         ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  701.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  702.         flg_tax_amount, tax_id, tax_percentage)
  703.         SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  704.             A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
  705.             A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
  706.             0, vDatetime, vUserId, vDatetime, vUserId,
  707.             A.flg_tax_amount, A.tax_id, A.tax_percentage
  708.         FROM tt_pu_monthly_price_product A
  709.         WHERE A.session_id = pSessionId
  710.         GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  711.                  A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
  712.                  A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
  713.        
  714.         /*
  715.          * @author TKP, 9 Jun 2016
  716.          * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
  717.          * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
  718.          */
  719.    
  720.         SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseIdForJournal
  721.         FROM pu_receive_goods A
  722.         INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
  723.         WHERE A.receive_goods_id = vRgId;
  724.        
  725.         IF (vOuId <> vOuWarehouseIdForJournal) THEN
  726.             SELECT f_get_ou_bu_structure(vOuWarehouseIdForJournal) as ou_structure INTO result;
  727.             vOuStructureJournalItem := result.ou_structure;
  728.         ELSE
  729.             vOuStructureJournalItem := ROW(-99, -99, -99);
  730.         END IF;
  731.  
  732.         /*Periksa apakah PO sudah selesai (Qty PO = Qty Rcv, qty o/s SO = 0)*/
  733.         SELECT COUNT(1) INTO vOustandingQtySO
  734.         FROM pu_po_balance_item A
  735.         INNER JOIN pu_po_item B ON A.tenant_id = B.tenant_id AND A.po_item_id = B.po_item_id
  736.         WHERE B.tenant_id = pTenantId AND B.po_id = vPoId
  737.         AND (a.qty_po - a.qty_rcv + a.qty_return - a.qty_cancel + a.qty_add) <> 0;
  738.        
  739.         /* Membuat automatic dokumen Delivery Order, dengan kondisi jika dokumen PO nya referensi dari SO dan SO nya dropship */
  740.         SELECT COALESCE(C.so_id, vEmptyId) AS so_id, COALESCE(D.flag_dropship, vNo) AS flg_dropship, COALESCE(C.partner_bill_to_id, vEmptyId) AS partner_bill_to_id
  741.         FROM pu_receive_goods A
  742.         INNER JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id
  743.         LEFT JOIN sl_so C ON C.doc_type_id = B.ref_doc_type_id AND B.ref_id = C.so_id
  744.         INNER JOIN sl_so_ext D ON C.so_id = D.so_Id
  745.         WHERE receive_goods_id = vRgId
  746.         INTO vSoId, vFlgDropshipSO, vPartnerBillToId;
  747.        
  748.         IF FOUND THEN
  749.             IF (vSoId <> vEmptyId AND vFlgDropshipSO <> vNo) THEN
  750.                 INSERT INTO sl_so_dropship_receive_goods_balance
  751.                 (tenant_id, receive_goods_item_id,
  752.                  receive_goods_product_id, serial_number, product_expired_date,
  753.                  lot_number, product_id, receive_goods_id,
  754.                  po_id, so_id, so_item_id,
  755.                  qty_rcv_int,
  756.                  base_uom_id,
  757.                  flg_create_do, do_id, do_item_id, do_product_id, qty_dlv_int,
  758.                  create_datetime, create_user_id, update_datetime,
  759.                  update_user_id, version, product_balance_id)
  760.                 SELECT A.tenant_id, B.receive_goods_item_id,
  761.                     COALESCE(C.receive_goods_product_id, vEmptyId) AS receive_goods_product_id, COALESCE(C.serial_number, vEmptyString) AS serial_number, COALESCE(C.product_expired_date, vEmptyString) AS product_expired_date,
  762.                     COALESCE(C.lot_number, vEmptyString) AS lot_number, B.product_id, A.receive_goods_id,
  763.                     vPoId, vSoId, J.ref_id,
  764.                     CASE WHEN C.receive_goods_product_id <> vEmptyId THEN C.qty_rcv_int ELSE B.qty_rcv_int END AS qty_rcv_int,
  765.                     CASE WHEN C.receive_goods_product_id <> vEmptyId THEN C.base_uom_id ELSE B.base_uom_id END AS base_uom_id,
  766.                     vNo, vEmptyId, vEmptyId, vEmptyId, vZeroValue,
  767.                     vDatetime, vUserId, vDatetime,
  768.                     vUserId, 0, COALESCE(D.product_balance_id, vEmptyId)
  769.                 FROM pu_receive_goods A
  770.                 INNER JOIN pu_receive_goods_item B ON A.tenant_id = B.tenant_id AND A.receive_goods_id = B.receive_goods_id
  771.                 LEFT JOIN pu_receive_goods_product C ON B.tenant_id = C.tenant_id AND B.receive_goods_item_id = C.receive_goods_item_id AND B.line_no = C.line_no
  772.                 LEFT JOIN in_product_balance D ON C.tenant_id = D.tenant_id AND C.product_id = D.product_id AND C.serial_number = D.serial_number AND C.lot_number = D.lot_number
  773.                 INNER JOIN pu_po I ON I.tenant_id = A.tenant_id AND I.doc_type_id = A.ref_doc_type_id AND I.po_id = A.ref_id AND I.ou_id = A.ou_id
  774.                 INNER JOIN pu_po_item J ON I.po_id = J.po_id AND B.ref_id = J.po_item_id
  775.                 WHERE A.receive_goods_id = vRgId;
  776.            
  777.                 --(tenant_id, product_id, serial_number COLLATE pg_catalog."default", lot_number COLLATE pg_catalog."default");
  778.    
  779.                 SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdDo
  780.                 FROM t_process_parameter A
  781.                 WHERE A.process_message_id = vProcessId
  782.                 AND A.process_parameter_key = 'autonumIdDo';
  783.            
  784.                 -- Ambil doc no SO baru
  785.                 SELECT A.process_parameter_value INTO vDocNoNewDo
  786.                 FROM t_process_parameter A
  787.                 WHERE A.process_message_id = vProcessId
  788.                 AND A.process_parameter_key = 'autonumDo';
  789.            
  790.                 PERFORM sl_automatic_create_delivery_order(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vPoId, vRgId, vOuId, vAutonumIdDo, vDocNoNewDo, vRoleId, vFlgUserRole);
  791.  
  792.                 /*Cek apakah SO referensi sudah terpenuhi semua*/
  793.                 /*
  794.                 SELECT COUNT(1) INTO vQtySOFulfilled
  795.                 FROM sl_so_balance_item A
  796.                 INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
  797.                 INNER JOIN sl_so C ON B.so_id = C.so_id
  798.                 WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
  799.                 AND (A.qty_so_int - A.qty_cancel_int + A.qty_add_int - A.qty_dlv_int + A.qty_return_int) = 0;
  800.                 */
  801.                
  802.                 /*
  803.                 SELECT COUNT(1) INTO vQtySOFulfilled
  804.                 FROM sl_so_balance_item A
  805.                 INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
  806.                 INNER JOIN sl_so C ON B.so_id = C.so_id
  807.                 WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
  808.                 AND A.status_item IN (vStatus, cStatus, fStatus);
  809.                
  810.  
  811.                 SELECT COUNT(1) INTO vQtySORequest
  812.                 FROM sl_so_balance_item A
  813.                 INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
  814.                 INNER JOIN sl_so C ON B.so_id = C.so_id
  815.                 WHERE C.so_id = vSoId AND C.tenant_id = pTenantId;
  816.                 */
  817.                
  818.                 SELECT ctgr_partner_id INTO vCtgrPartnerId
  819.                 FROM m_partner
  820.                 WHERE partner_id = vPartnerBillToId;
  821.                
  822.                 SELECT COUNT(1) INTO vQtySOFulfilled
  823.                 FROM sl_so_balance_item A
  824.                 INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
  825.                 INNER JOIN sl_so C ON B.so_id = C.so_id
  826.                 WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
  827.                 AND A.status_item NOT IN (vStatus, cStatus, fStatus);
  828.  
  829.                 --IF (vOustandingQtySO = vZeroValue AND vQtySOFulfilled = vQtySORequest) THEN
  830.                 IF (vQtySOFulfilled = vZeroValue AND vFlgDropshipSO <> vNo AND vCtgrPartnerId <> vCtgrPartnerIdClientCorporate) THEN
  831.                     SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdSI
  832.                     FROM t_process_parameter A
  833.                     WHERE A.process_message_id = vProcessId
  834.                     AND A.process_parameter_key = 'autonumIdSI';
  835.  
  836.                     -- Ambil doc no SI baru
  837.                     SELECT A.process_parameter_value INTO vDocNoNewSI
  838.                     FROM t_process_parameter A
  839.                     WHERE A.process_message_id = vProcessId
  840.                     AND A.process_parameter_key = 'autonumSI';
  841.                    
  842.                             IF EXISTS (
  843.                                 SELECT 1 FROM sl_do A
  844.                                 WHERE A.tenant_id = pTenantId AND A.doc_type_id = vDocTypeDo AND A.doc_no = vDocNoNewDo
  845.                                     AND A.doc_date = vDocDate AND A.ou_id = vOuId
  846.                             ) THEN
  847.                            
  848.                                 --INSERT INTO test_123(test)
  849.                                     --SELECT CONCAT(vSoId,' ',vAutonumIdSI,' ',vDocNoNewSI,' ',vFlgUserRole,' ',' AUTO SI');
  850.            
  851.                                 PERFORM sl_automatic_create_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime,
  852.                                     vSoId, A.do_id, vAutonumIdSI, vDocNoNewSI, vRoleId, vFlgUserRole)
  853.                                 FROM sl_do A
  854.                                 WHERE A.tenant_id = pTenantId AND A.doc_type_id = vDocTypeDo AND A.doc_no = vDocNoNewDo
  855.                                     AND A.doc_date = vDocDate AND A.ou_id = vOuId;
  856.                                    
  857.                                 /*
  858.                                  * Get data invoice AR Balance
  859.                                  * Get data mapping_cbin_so_balance_item_id ada atau tidak
  860.                                  * Generate automatic alloc cb in document
  861.                                  */
  862.                                     -- Get invoice AR balance document
  863.                                     SELECT A.invoice_ar_balance_id INTO vInvoiceArBalanceId
  864.                                     FROM fi_invoice_ar_balance A
  865.                                     INNER JOIN sl_so B
  866.                                     ON A.ref_doc_type_id = vDocTypeSo AND A.ref_id = B.so_id AND A.tenant_id = pTenantId
  867.                                     WHERE B.so_id = vSoId
  868.                                     AND A.tenant_id = pTenantId;
  869.                                    
  870.                                     IF NOT FOUND THEN
  871.                                         vInvoiceArBalanceId := vEmptyId;
  872.                                     END IF;
  873.                                
  874.                                    
  875.                                     --get mapping_cbin_so_balance_item_id
  876.                                     SELECT B.mapping_cbin_so_balance_item_id INTO vMappingCbInSoBalanceItemId
  877.                                     FROM sl_so A
  878.                                     INNER JOIN cb_mapping_cbin_so_balance_item B ON A.tenant_id = B.tenant_id AND A.so_id = B.so_id
  879.                                     WHERE A.tenant_id = pTenantId
  880.                                     AND A.so_id = vSoId;
  881.                                
  882.                                     IF NOT FOUND THEN
  883.                                         vMappingCbInSoBalanceItemId := vEmptyId;
  884.                                     END IF;
  885.                                    
  886.                                    
  887.                                     -- Ambil doc Allocation CB in baru
  888.                                     SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdAllocCbIn
  889.                                     FROM t_process_parameter A
  890.                                     WHERE A.process_message_id = vProcessId
  891.                                     AND A.process_parameter_key = 'autonumIdAllocCbIn';
  892.                                    
  893.                                     IF NOT FOUND THEN
  894.                                         vAutonumIdAllocCbIn := vEmptyId;
  895.                                     END IF;
  896.                                
  897.                                     -- Ambil doc no Allocation CB in baru
  898.                                     SELECT A.process_parameter_value INTO vDocNoNewAllocCbIn
  899.                                     FROM t_process_parameter A
  900.                                     WHERE A.process_message_id = vProcessId
  901.                                     AND A.process_parameter_key = 'autonumAllocCbIn';
  902.                                    
  903.                                     IF NOT FOUND THEN
  904.                                         vDocNoNewAllocCbIn := vEmptyString;
  905.                                     END IF;
  906.                                    
  907.                                     --INSERT INTO test_123(test)
  908.                                     --  SELECT CONCAT(vSoId,'-',vFlgDropshipSO,'-',vMappingCbInSoBalanceItemId,'-',vInvoiceArBalanceId,'-',vAutonumIdAllocCbIn,'-',vDocNoNewAllocCbIn,'-',vProcessId,'-','SUBMIT RG ');
  909.                                    
  910.                                     IF( vSoId <> vEmptyId AND vFlgDropshipSO = vYes AND vMappingCbInSoBalanceItemId <> vEmptyId
  911.                                         AND vInvoiceArBalanceId <> vEmptyId AND vAutonumIdAllocCbIn <> vEmptyId AND vDocNoNewAllocCbIn <> vEmptyString) THEN
  912.                                         PERFORM fi_automatic_alloc_cb_in(pSessionId, pTenantId, vUserId, vDatetime, vAutonumIdAllocCbIn, vDocNoNewAllocCbIn, vMappingCbInSoBalanceItemId, vInvoiceArBalanceId);
  913.                                     END IF;  
  914.                       END IF;
  915.                 END IF;
  916.             END IF;
  917.         END IF;
  918.                  
  919.        
  920.        
  921.        
  922.         /*
  923.          * membuat data transaksi jurnal :
  924.          * 1. buat admin
  925.          * 2. buat temlate jurnal
  926.          */
  927.    
  928.         PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
  929.         FROM pu_receive_goods A
  930.         WHERE A.receive_goods_id = vRgId;
  931.        
  932.         SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  933.        
  934.         INSERT INTO gl_journal_trx
  935.         (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  936.         ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  937.         ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  938.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  939.         SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
  940.             (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  941.             A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
  942.             0, vDatetime, vUserId, vDatetime, vUserId
  943.         FROM pu_receive_goods A, pu_po B
  944.         WHERE A.receive_goods_id = vRgId AND
  945.             A.ref_doc_type_id = B.doc_type_id AND
  946.             A.ref_id = B.po_id;
  947.        
  948.         INSERT INTO tt_journal_trx_item
  949.         (session_id, tenant_id, journal_trx_id, line_no,
  950.         ref_doc_type_id, ref_id,
  951.         partner_id, product_id, cashbank_id, ou_rc_id,
  952.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  953.         coa_id, curr_code, qty, uom_id,
  954.         amount, journal_date, type_rate,
  955.         numerator_rate, denominator_rate, journal_desc, remark)
  956.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  957.             A.doc_type_id, B.receive_goods_item_id,
  958.             A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
  959.             vEmptyId, vSignDebit, vProductCOA, C.activity_gl_id,
  960.             C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
  961.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
  962.             A.doc_date, vTypeRate,
  963.             1, 1, 'PRODUCT_STOCK', B.remark
  964.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
  965.         WHERE A.receive_goods_id = vRgId AND
  966.             A.receive_goods_id = B.receive_goods_id AND
  967.             B.ref_id = C.po_item_id AND
  968.             C.flg_stock = 'Y';
  969.            
  970.         INSERT INTO tt_journal_trx_item
  971.         (session_id, tenant_id, journal_trx_id, line_no,
  972.         ref_doc_type_id, ref_id,
  973.         partner_id, product_id, cashbank_id, ou_rc_id,
  974.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  975.         coa_id, curr_code, qty, uom_id,
  976.         amount, journal_date, type_rate,
  977.         numerator_rate, denominator_rate, journal_desc, remark)
  978.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  979.             A.doc_type_id, B.receive_goods_item_id,
  980.             A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
  981.     -- NK, 30 Agustus 2014 - add segment_id        
  982.             C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id,
  983.             E.coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
  984.             f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
  985.             A.doc_date, vTypeRate,
  986.             1, 1, 'PRODUCT_NON_STOCK', B.remark
  987.         FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_activity_gl E
  988.         WHERE A.receive_goods_id = vRgId AND
  989.             A.receive_goods_id = B.receive_goods_id AND
  990.             B.ref_id = C.po_item_id AND
  991.             C.flg_stock = 'N' AND
  992.             C.activity_gl_id = E.activity_gl_id;
  993.        
  994.         INSERT INTO gl_journal_trx_item
  995.         (tenant_id, journal_trx_id, line_no,
  996.         ref_doc_type_id, ref_id,
  997.         partner_id, product_id, cashbank_id, ou_rc_id,
  998.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  999.         coa_id, curr_code, qty, uom_id,
  1000.         amount, journal_date, type_rate,
  1001.         numerator_rate, denominator_rate, journal_desc, remark,
  1002.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  1003.         ou_branch_id, ou_sub_bu_id)
  1004.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  1005.             A.ref_doc_type_id, A.ref_id,
  1006.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  1007.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  1008.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  1009.             A.amount, A.journal_date, A.type_rate,
  1010.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  1011.             0, vDatetime, vUserId, vDatetime, vUserId,
  1012.             (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
  1013.         FROM tt_journal_trx_item A
  1014.         WHERE A.session_id = pSessionId;
  1015.        
  1016.         INSERT INTO gl_journal_trx_mapping
  1017.         (tenant_id, journal_trx_id, line_no,
  1018.         ref_doc_type_id, ref_id,
  1019.         partner_id, product_id, cashbank_id, ou_rc_id,
  1020.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1021.         coa_id, curr_code, qty, uom_id,
  1022.         amount, journal_date, type_rate,
  1023.         numerator_rate, denominator_rate, journal_desc, remark,
  1024.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1025.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  1026.             vEmptyId, vEmptyId,    
  1027.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  1028.             vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  1029.             f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  1030.             SUM(A.amount), A.journal_date, A.type_rate,
  1031.             1, 1, 'ACCR_AP', vEmptyValue,
  1032.             0, vDatetime, vUserId, vDatetime, vUserId
  1033.         FROM tt_journal_trx_item A
  1034.         WHERE A.session_id = pSessionId
  1035.         GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  1036.            
  1037.         DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;   
  1038.         DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  1039.         DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
  1040.         DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
  1041.     END;   
  1042.     $BODY$
  1043.   LANGUAGE plpgsql VOLATILE
  1044.   COST 100;
  1045. ALTER FUNCTION memeflorist.pu_submit_receive_goods(bigint, character varying, character varying)
  1046.   OWNER TO sts;
  1047.  
RAW Paste Data