i_process_pos_from_outlet

--Fitra 2018 Des 14
--NOTE : type payment di web clinic memakai camel case
CREATE OR REPLACE FUNCTION i_process_pos_from_outlet(character varying,character varying)
  RETURNS bigint AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pDatetime           ALIAS FOR $2;

    vFlgInprogress            character varying := 'I';
    vFlgNo                    character varying := 'N';
    vFlgYes                   character varying := 'Y';
    vFlgSkip                  character varying := 'S';
    vDocTypeIdSalesRetailShop bigint := 401;
    vEmptyString              character varying := '';
    vSpaceString              character varying := ' ';
    vNullRefId                bigint := -99;
    vStatusSubmit             character varying(1) := 'S';

    --vDatetime               character varying(14);
    vUserId                   bigint := -1;
    vUserName                 character varying;
    vStatusDocRelease         character varying := 'R';

    vTaxId                    bigint := -99;
    vTaxPercentage            numeric := 0;
    vSessionId                character varying;
    --vRoundingModeNonTax         character varying(5);
    vCash                     character varying :='CASH';
    vProductStatusGood        character varying :='GOOD';
    vModeLogAdd               character varying :='A';
    vFlgCashBankCASH          character varying :='C';
    vRecTypeKredit            character varying :='K';
    vRecTypeDebit             character varying :='D';
    vProcessName              character varying :='i_ho_process_pos_shop';

    vCardTypeDebit            character varying :='DEBIT';
    vCardTypeCredit           character varying :='CREDIT';

    vTotalPos                 bigint :=0;
BEGIN

    --vDatetime := to_char(pTimestamp,'yyyyMMddHH24mmss');
    vUserName := f_get_username(vUserId);
    vSessionId := pSessionId;
    DELETE FROM tt_i_trx_pos WHERE session_id = vSessionId;
    DELETE FROM tt_i_trx_pos_item WHERE session_id = vSessionId;
    --SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    --beri tanda transaksi NFO untuk diskip
    UPDATE pj.trx_pos A
    SET flg_process = vFlgSkip,
        process_datetime = pDatetime,
        end_process_datetime = pDatetime
    FROM i_clinic_registration_mapping B
    INNER JOIN i_clinic_registration C ON B.clinic_registration_id = C.clinic_registration_id
    WHERE A.flg_process = vFlgNo AND
          A.status_doc = vStatusDocRelease AND
          A.doc_date < SUBSTRING(pDatetime, 1,8) AND
          B.ou_id = A.record_owner_id AND
          C.flg_full_owned = vFlgNo;

    --update flg data yg diproses
    --data yg diproses merupakan transaksi FO
    UPDATE pj.trx_pos A
    SET flg_process = vFlgInprogress,
        process_datetime = pDatetime
    FROM i_clinic_registration_mapping B
    INNER JOIN i_clinic_registration C ON B.clinic_registration_id = C.clinic_registration_id
    WHERE A.flg_process = vFlgNo AND
          A.status_doc = vStatusDocRelease AND
          A.doc_date < SUBSTRING(pDatetime, 1,8) AND
          B.ou_id = A.record_owner_id AND
          C.flg_full_owned = vFlgYes;

    --insert ke header pos temporary
    INSERT INTO tt_i_trx_pos(
            session_id, trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
            curr_code, add_discount_percentage, add_discount_amount, gross_amount,
            nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
            total_payment, total_refund, status, version, create_datetime,
            create_user_id, create_user_name, update_datetime, update_user_id,
            update_user_name, return_amount, warehouse_id, outlet_id)
    SELECT vSessionId, A.pos_id, A.pos_id||'_'||A.doc_no, B.tenant_id, vDocTypeIdSalesRetailShop, A.doc_no, A.doc_date,
           A.record_owner_id,vEmptyString, vEmptyString, A.doc_type_id, A.pos_id, A.remark,
           vNullRefId, B.partner_id, vNullRefId,  vNullRefId,
           A.curr_code, 0, 0, 0,
           0, 0, 0, 0, 0,
           0, 0, vStatusSubmit, 0, pDatetime,
           vUserId, vUserName, pDatetime, vUserId,
           vUserName, 0, A.warehouse_id, B.outlet_id
    FROM pj.trx_pos A
    INNER JOIN i_clinic_registration_mapping B ON B.ou_id = A.record_owner_id
    WHERE A.flg_process = vFlgInprogress;

    --insert header ke pos item temporary
    INSERT INTO tt_i_trx_pos_item(
            session_id, process_no, tenant_id, trx_pos_id, line_no,
            product_id, product_balance_id,
            base_uom_id, qty, curr_code,
            gross_sell_price, flg_tax_amount, tax_id,
            tax_percentage, tax_price,
            promo_code, discount_percentage, discount_amount, nett_sell_price,
            tax_amount, nett_amount_item, remark, version, group_product_ou_id,
            create_datetime, create_user_id, update_datetime, update_user_id,
            ref_item_id, ref_assembly_product_id)
    SELECT  vSessionId, A.process_no, B.tenant_id, A.trx_pos_id, C.line_no,
            C.product_id, f_get_product_balance_non_serialnumber_by_product_id(C.product_id, B.tenant_id),
            f_get_product_base_uom_id(C.product_id), C.qty, A.curr_code,
            C.unit_sell_price , COALESCE(E.flg_pkp, vFlgNo), CASE WHEN COALESCE(E.flg_pkp, vFlgNo) = vFlgYes THEN f_get_tax_by_tenant_id(B.tenant_id) ELSE vNullRefId END,
            CASE WHEN COALESCE(E.flg_pkp, vFlgNo) = vFlgYes THEN f_get_tax_percentage_by_tenant_id(B.tenant_id) ELSE 0 END, 0,
            vEmptyString, 0, (C.item_discount_amount + C.discount_header_on_item)/C.qty, 0,
            0, C.item_amount_after_discount - C.discount_header_on_item, C.item_remark, 0, f_get_group_product_ou(B.ou_id, C.product_id),
            pDatetime, vUserId, pDatetime, vUserId,
            vNullRefId, vNullRefId
    FROM tt_i_trx_pos A
    INNER JOIN i_clinic_registration_mapping B ON B.ou_id = A.ou_id
    INNER JOIN pj.trx_pos_item C ON C.pos_id = A.trx_pos_id
    INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
    LEFT JOIN t_ou_legal E ON D.ou_bu_id = E.ou_id
    --INNER JOIN in_product_balance D ON D.product_id = C.product_id AND D.lot_number = vSpaceString AND D.serial_number = vSpaceString
    WHERE A.session_id = vSessionId;

    --menghitung tax price,  tax_amount
    --ini ikut perhitungan di submit SO
    UPDATE tt_i_trx_pos_item
    SET nett_sell_price = gross_sell_price - discount_amount,
        tax_price = f_tax_rounding(tenant_id, f_get_amount_before_tax((gross_sell_price - discount_amount), flg_tax_amount, tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdSalesRetailShop, curr_code), f_get_value_system_config_by_param_code(tenant_id, 'rounding.mode.non.tax')), tax_percentage),
        tax_amount = f_tax_rounding(tenant_id, f_get_amount_before_tax((qty * (gross_sell_price - discount_amount)), flg_tax_amount, tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdSalesRetailShop, curr_code), f_get_value_system_config_by_param_code(tenant_id, 'rounding.mode.non.tax')), tax_percentage)
    WHERE session_id = vSessionId;

    --menghitung nett_sell_price, nett_amount_item
    --ini ikut perhitungan BFnya addrian
    --UPDATE tt_i_trx_pos_item
    --SET nett_sell_price = gross_sell_price -tax_price,
    --  nett_amount_item = (gross_sell_price * qty) - tax_amount
    --WHERE A.session_id = vSessionId;

    --insert ke trx pos tax
    INSERT INTO i_trx_pos_tax(
            process_no, tenant_id, trx_pos_id, tax_id, flg_amount,
            tax_percentage, base_amount, tax_amount, remark, version, create_datetime,
            create_user_id, update_datetime, update_user_id)
    SELECT A.process_no, A.tenant_id, A.trx_pos_id, A.tax_id, B.flg_amount,
           A.tax_percentage, SUM(A.nett_amount_item - A.tax_amount), SUM(A.tax_amount), vEmptyString, 0,pDatetime,
           vUserId, pDatetime, vUserId
    FROM tt_i_trx_pos_item A
    INNER JOIN m_tax B ON B.tax_id = A.tax_id
    WHERE A.session_id = vSessionId AND A.flg_tax_amount = vFlgYes
    GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.tax_id, B.flg_amount,
           A.tax_percentage;


    --===============INSERT TERKAIT PAYMENT=========================
    -- buat paymentnya CASH
    -- karena currencynya cuma ada IDR, tidak perlu carikan nilai exchange
    INSERT INTO i_trx_pos_cash_payment(
            process_no, tenant_id, trx_pos_id, curr_payment_code,
            payment_amount, date_exchange_rate, numerator_rate, denominator_rate,
            conversion_amount, remark, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
    SELECT  A.process_no, A.tenant_id, A.trx_pos_id, B.curr_code,
            SUM(B.payment_amount), A.doc_date, 1, 1,
            SUM(B.payment_amount), vEmptyString, 0, pDatetime, vUserId,
            pDatetime, vUserId
    FROM tt_i_trx_pos A
    INNER JOIN pj.trx_pos_payment B ON B.pos_id = A.trx_pos_id
    WHERE A.session_id = vSessionId AND
          B.payment_type = vCash
    GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, B.curr_code, A.doc_date;

    -- buat paymentnya selain CASH (EDC)
    -- karena currencynya cuma ada IDR, tidak perlu carikan nilai exchange
    INSERT INTO i_trx_pos_non_cash_payment(
            process_no, tenant_id, trx_pos_id,
            line_no, device_merchant_id, card_type,
            bank_card_code, card_no,
            curr_payment_code, payment_amount, date_exchange_rate, numerator_rate,
            denominator_rate, conversion_amount, remark, version, create_datetime,
            create_user_id, update_datetime, update_user_id)
    SELECT  A.process_no, A.tenant_id, A.trx_pos_id,
            ROW_NUMBER () OVER (ORDER BY B.device_merchant_id), B.device_merchant_id, f_get_card_type_for_pos_intgr(B.payment_type),
            vEmptyString, B.payment_ref_no,
            B.curr_code, SUM(B.payment_amount), vEmptyString, 1,
            1, SUM(B.payment_amount), B.payment_remark, 0, pDatetime,
            vUserId, pDatetime, vUserId
    FROM tt_i_trx_pos A
    INNER JOIN pj.trx_pos_payment B ON B.pos_id = A.trx_pos_id
    WHERE A.session_id = vSessionId AND
          B.payment_type <> vCash
    GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, B.device_merchant_id, B.payment_type, B.payment_ref_no, B.curr_code, B.payment_remark;

    --update data product balance stock
    WITH summary_qty AS(
        SELECT SUM(B.qty) AS qty,
               B.product_id,
               B.product_balance_id,
               vProductStatusGood AS product_status,
               A.warehouse_id,
               B.tenant_id
        FROM pj.trx_pos A
        INNER JOIN tt_i_trx_pos_item B ON A.pos_id = B.trx_pos_id
        WHERE B.session_id = vSessionId
        GROUP BY B.product_id, B.product_balance_id, A.warehouse_id, B.tenant_id
    )
    UPDATE in_product_balance_stock Z
    SET qty = Z.qty - A.qty,
        version = Z.version + 1,
        update_datetime = pDatetime,
        update_user_id = vUserId
    FROM summary_qty A
    WHERE Z.product_id = A.product_id AND
          Z.warehouse_id = A.warehouse_id AND
          Z.product_balance_id = A.product_balance_id AND
          Z.product_status = A.product_status AND
          Z.tenant_id = A.tenant_id;

    --insert data log balance stock
    INSERT INTO in_log_product_balance_stock(
            tenant_id, ou_id, doc_type_id,
            ref_id, doc_no, doc_date, partner_id, product_id, warehouse_id,
            product_balance_id, product_status, base_uom_id, qty, version,
            create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT  A.tenant_id, A.ou_id, A.doc_type_id,
            A.trx_pos_id, A.doc_no, A.doc_date, vNullRefId, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood, B.base_uom_id, -1 * SUM(B.qty), 0,
            pDatetime, vUserId, pDatetime, vUserId
    FROM tt_i_trx_pos A
    INNER JOIN tt_i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND
                                      A.process_no = B.process_no AND A.tenant_id = B.tenant_id AND
                                      A.session_id = B.session_id
    INNER JOIN pj.trx_pos C ON C.pos_id = A.trx_pos_id
    WHERE A.session_id = vSessionId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date,
             B.product_id, C.warehouse_id, B.product_balance_id, B.base_uom_id;

    --insert data i_trx_data_log
    INSERT INTO i_trx_data_log(
            tenant_id, ou_id, doc_date, db_version, type_data,
            mode_log, data_log, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
    SELECT  A.tenant_id, A.ou_id, A.doc_date , '1.0', 'in_log_product_balance_stock',
            vModeLogAdd, row_to_json(B)::TEXT, 0, pDatetime, vUserId,
            pDatetime, vUserId
    FROM    tt_i_trx_pos A
    INNER JOIN in_log_product_balance_stock B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id AND
                                                 A.doc_type_id = B.doc_type_id AND A.trx_pos_id = B.ref_id
    WHERE A.session_id = vSessionId
    GROUP BY B.log_product_balance_stock_id, A.tenant_id, A.ou_id, A.doc_date;

    --insert ke cash bank balance degan amount 0
    WITH cashbank_balance_grouped AS (
        SELECT  A.tenant_id, A.ou_id, f_get_cash_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code) AS cashbank_id,
                A.doc_date AS cash_bank_date, B.curr_payment_code AS curr_code
        FROM tt_i_trx_pos A
        INNER JOIN i_trx_pos_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
                                               B.trx_pos_id = A.trx_pos_id
        WHERE A.session_id = vSessionId
        GROUP BY A.tenant_id, A.ou_id, f_get_cash_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code), A.doc_date,
                 B.curr_payment_code
    )

    INSERT INTO cb_cashbank_balance(
            tenant_id, ou_id, cashbank_id, cash_bank_date,
            rec_type, curr_code, amount, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
    SELECT  A.tenant_id, A.ou_id, A.cashbank_id, A.cash_bank_date,
            vRecTypeDebit, A.curr_code, 0, 0, pDatetime, vUserId,
            pDatetime, vUserId
    FROM cashbank_balance_grouped A
    WHERE NOT EXISTS(
            SELECT 1
            FROM cb_cashbank_balance B
            WHERE B.tenant_id = A.tenant_id AND
                  B.ou_id = A.ou_id AND
                  B.cashbank_id = A.cashbank_id AND
                  B.cash_bank_date = A.cash_bank_date AND
                  B.rec_type = vRecTypeDebit );

    --update cash bank balance untuk semua payment cash
    WITH cashbank_balance_grouped AS (
        SELECT  A.tenant_id, A.ou_id, f_get_cash_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code) AS cashbank_id,
                A.doc_date AS cash_bank_date, B.curr_payment_code AS curr_code, sum(B.conversion_amount) AS amount
        FROM tt_i_trx_pos A
        INNER JOIN i_trx_pos_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
                                               B.trx_pos_id = A.trx_pos_id
        WHERE A.session_id = vSessionId
        GROUP BY A.tenant_id, A.ou_id, f_get_cash_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code), A.doc_date,
                 B.curr_payment_code
    )
    UPDATE cb_cashbank_balance Z
    SET amount = Z.amount + A.amount,
        version = Z.version + 1,
        update_datetime = pDatetime,
        update_user_id = vUserId
    FROM cashbank_balance_grouped A
    WHERE Z.tenant_id = A.tenant_id AND
          Z.ou_id = A.ou_id AND
          Z.cashbank_id = A.cashbank_id AND
          Z.cash_bank_date = A.cash_bank_date AND
          Z.rec_type = vRecTypeDebit;

    --INSERT cb_balance_settlement
    WITH balance_settlement_grouped AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
               B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
               B.curr_payment_code AS curr_code
        FROM tt_i_trx_pos A
        INNER JOIN i_trx_pos_non_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
                                               B.trx_pos_id = A.trx_pos_id
        WHERE A.session_id = vSessionId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
                 B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no, B.curr_payment_code )
    INSERT INTO cb_balance_settlement(
            tenant_id, ou_id, doc_type_id, doc_no,
            doc_date, device_merchant_id, card_type, bank_card_code, card_no,
            curr_payment_code, amount_payment, flg_settle, cashbank_id, cash_bank_date,
            ref_doc_type_id, ref_id, version, create_datetime, create_user_id,
            update_datetime, update_user_id, remark)

    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no,
           A.doc_date, A.device_merchant_id, A.card_type, A.bank_card_code, A.card_no,
           A.curr_code, 0, vFlgNo, vNullRefId, vEmptyString,
           vNullRefId, vNullRefId, 0, pDatetime, vUserId,
           pDatetime, vUserId, vEmptyString
    FROM balance_settlement_grouped A
    WHERE NOT EXISTS (
        SELECT 1 FROM cb_balance_settlement B
        WHERE A.tenant_id = B.tenant_id AND
              A.ou_id = B.ou_id AND
              A.doc_type_id = B.doc_type_id AND
              A.doc_no =  B.doc_no AND
              A.doc_date = B.doc_date AND
              A.device_merchant_id = B.device_merchant_id AND
              A.card_type = B.card_type AND
              A.bank_card_code = B.bank_card_code AND
              A.card_no = B.card_no AND
              A.curr_code = B.curr_payment_code);

     WITH balance_settlement_grouped AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
               B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
               B.curr_payment_code AS curr_code, SUM(B.payment_amount) AS amount_payment
        FROM tt_i_trx_pos A
        INNER JOIN i_trx_pos_non_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
                                               B.trx_pos_id = A.trx_pos_id
        WHERE A.session_id = vSessionId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
                 B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no, B.curr_payment_code )
    UPDATE cb_balance_settlement B
    SET amount_payment = B.amount_payment + A.amount_payment,
        version = B.version + 1,
        update_datetime = pDatetime,
        update_user_id = vUserId
    FROM balance_settlement_grouped A
    WHERE A.tenant_id = B.tenant_id AND
          A.ou_id = B.ou_id AND
          A.doc_type_id = B.doc_type_id AND
          A.doc_no =  B.doc_no AND
          A.doc_date = B.doc_date AND
          A.device_merchant_id = B.device_merchant_id AND
          A.card_type = B.card_type AND
          A.bank_card_code = B.bank_card_code AND
          A.card_no = B.card_no AND
          A.curr_code = B.curr_payment_code;


      --update gross_amount, nett_amount, total_amount
      WITH item_grouped AS (
            SELECT SUM(A.qty * A.discount_amount) AS discount_amount,
                   SUM(A.qty * A.gross_sell_price) AS gross_amount,
                   SUM(A.nett_amount_item) AS nett_amount,
                   SUM(A.tax_amount) AS tax_amount,
                   A.process_no,
                   A.tenant_id,
                   A.trx_pos_id
            FROM tt_i_trx_pos_item A
            WHERE A.session_id = vSessionId
            GROUP BY A.process_no, A.tenant_id, A.trx_pos_id
      )
      UPDATE tt_i_trx_pos Z
      SET gross_amount = Z.gross_amount + A.gross_amount,
          nett_amount = Z.nett_amount + A.nett_amount,
          total_amount = Z.total_amount + A.nett_amount
      FROM item_grouped A
      WHERE Z.session_id = vSessionId AND
            A.process_no = Z.process_no AND
            A.tenant_id = Z.tenant_id AND
            A.trx_pos_id = Z.trx_pos_id;

      --update payment amount untuk pembayaran CASH
      WITH payment_cash_grouped AS (
            SELECT SUM(A.conversion_amount) AS payment_amount,
                   B.process_no,
                   B.tenant_id,
                   B.trx_pos_id
            FROM i_trx_pos_cash_payment A
            INNER JOIN tt_i_trx_pos B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id AND A.trx_pos_id = B.trx_pos_id
            WHERE B.session_id = vSessionId
            GROUP BY B.process_no, B.tenant_id, B.trx_pos_id
      )
      UPDATE tt_i_trx_pos Z
      SET total_payment = Z.total_payment + A.payment_amount
      FROM payment_cash_grouped A
      WHERE Z.session_id = vSessionId AND
            A.process_no = Z.process_no AND
            A.tenant_id = Z.tenant_id AND
            A.trx_pos_id = Z.trx_pos_id;

      --update payment amount untuk pembayaran EDC
      WITH payment_non_cash_grouped AS (
            SELECT SUM(A.conversion_amount) AS payment_amount,
                   B.process_no,
                   B.tenant_id,
                   B.trx_pos_id
            FROM i_trx_pos_non_cash_payment A
            INNER JOIN tt_i_trx_pos B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id AND A.trx_pos_id = B.trx_pos_id
            WHERE B.session_id = vSessionId
            GROUP BY B.process_no, B.tenant_id, B.trx_pos_id
      )
      UPDATE tt_i_trx_pos Z
      SET total_payment = Z.total_payment + A.payment_amount
      FROM payment_non_cash_grouped A
      WHERE Z.session_id = vSessionId AND
            A.process_no = Z.process_no AND
            A.tenant_id = Z.tenant_id AND
            A.trx_pos_id = Z.trx_pos_id;

      --buat data header POS
      INSERT INTO i_trx_pos(
            trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
            curr_code, add_discount_percentage, add_discount_amount, gross_amount,
            nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
            total_payment, total_refund, status, version, create_datetime,
            create_user_id, create_user_name, update_datetime, update_user_id,
            update_user_name, return_amount)
    SELECT trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
            ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
            partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
            curr_code, add_discount_percentage, add_discount_amount, gross_amount,
            nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
            total_payment, total_refund, status, version, create_datetime,
            create_user_id, create_user_name, update_datetime, update_user_id,
            update_user_name, return_amount
    FROM tt_i_trx_pos WHERE session_id = vSessionId;

    --buat data item POS
    INSERT INTO i_trx_pos_item(
            process_no, tenant_id, trx_pos_id, line_no,
            product_id, product_balance_id, base_uom_id, qty, curr_code,
            gross_sell_price, flg_tax_amount, tax_id, tax_percentage, tax_price,
            promo_code, discount_percentage, discount_amount, nett_sell_price,
            tax_amount, nett_amount_item, remark, version, group_product_ou_id,
            create_datetime, create_user_id, update_datetime, update_user_id,
            ref_item_id, ref_assembly_product_id)
    SELECT  process_no, tenant_id, trx_pos_id, line_no,
            product_id, product_balance_id, base_uom_id, qty, curr_code,
            gross_sell_price, flg_tax_amount, tax_id, tax_percentage, tax_price,
            promo_code, discount_percentage, discount_amount, nett_sell_price,
            tax_amount, nett_amount_item, remark, version, group_product_ou_id,
            create_datetime, create_user_id, update_datetime, update_user_id,
            ref_item_id, ref_assembly_product_id
    FROM tt_i_trx_pos_item
    WHERE session_id = vSessionId;

    --panggil function submit
    PERFORM generate_process_message_for_submit_pos_shop(vSessionId, A.tenant_id, vProcessName, A.process_no,
                                                        pDatetime, A.outlet_id, A.ou_id, A.warehouse_id, vUserId)
    FROM tt_i_trx_pos A
    WHERE A.session_id = vSessionId;

    --update trx_pos flg_process = Y
    UPDATE pj.trx_pos A
    SET flg_process = vFlgYes,
        end_process_datetime = to_char(current_timestamp,'yyyyMMddHH24mmss')
    FROM tt_i_trx_pos B
    WHERE A.pos_id = B.trx_pos_id;

    SElECT count(1) INTO vTotalPos
    FROM tt_i_trx_pos A
    WHERE A.session_id = vSessionId;

    DELETE FROM tt_i_trx_pos WHERE session_id = vSessionId;
    DELETE FROM tt_i_trx_pos_item WHERE session_id = vSessionId;

    return vTotalPos;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /