i_process_pos_from_so_online

--Fitra 2018 Des 14
--NOTE : type payment di web clinic memakai camel case
CREATE OR REPLACE FUNCTION i_process_pos_from_so_online(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';
    vStatusDocShipped         character varying := 'S';

    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;
    vParamCodeWarehouseId     character varying :='pos.from.so.online.default.warehouse.id';

    vTenantId                 bigint;
    vOuId                     bigint;
    vOutletId                 bigint;
    vFlgOuBuPkp               character varying;
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 untuk so online yg akan diproses (status S, dan ada resinya
    UPDATE sl_order A
    SET flg_process = vFlgInprogress,
        process_intgr_datetime = pDatetime
    WHERE A.flg_process = vFlgNo AND
          A.status = vStatusDocShipped AND
          A.delivery_datetime < pDatetime AND
          NOT (A.delivery_datetime IS NULL) AND
          TRIM(A.delivery_datetime) != vEmptyString AND
          EXISTS( SELECT 1 FROM sl_order_address B WHERE A.order_id=B.order_id AND TRIM(B.waybill_number) <> vEmptyString);

    --ambil informasi tenant, ouId, outletId dan flg pkp
    SELECT tenant_id, ou_id, outlet_id INTO vTenantId, vOuId, vOutletId
    FROM i_clinic_registration_mapping
    WHERE flg_online = vFlgYes
    ORDER BY clinic_code
    LIMIT 1;

    vTenantId := COALESCE(vTenantId, vNullRefId);
    vOuId := COALESCE(vOuId, vNullRefId);
    vOutletId := COALESCE(vOutletId, vNullRefId);

    SELECT COALESCE(B.flg_pkp, vFlgNo) INTO vFlgOuBuPkp
    FROM m_ou_structure A
    LEFT JOIN t_ou_legal B ON A.ou_bu_id = B.ou_id
    WHERE A.ou_id = vOuId;

    vFlgOuBuPkp := COALESCE(vFlgOuBuPkp, vFlgNo);

    --insert ke header pos temporary
    -- tenantnya patut diperhatikan
    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.order_id, A.order_id||'_'||A.doc_no||'_'||vSessionId, vTenantId, vDocTypeIdSalesRetailShop, A.doc_no, SUBSTRING(A.delivery_datetime, 1,8),
           vOuId,vEmptyString, vEmptyString, A.doc_type_id, A.order_id, A.remark,
           vNullRefId, vNullRefId, vNullRefId,  vNullRefId,
           A.curr_code, 0, 0, 0,
           0, 0, (CASE WHEN C.shipping_fee - C.shipping_fee_discount < 0 THEN 0 ELSE C.shipping_fee - C.shipping_fee_discount END) + A.handling_fee, 0, 0,
           0, 0, vStatusSubmit, 0, pDatetime,
           vUserId, vUserName, pDatetime, vUserId,
           vUserName, 0, f_get_value_system_config_by_param_code(vTenantId, vParamCodeWarehouseId)::bigint, vOutletId
    FROM sl_order A
    INNER JOIN sl_order_address C ON C.order_id = A.order_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, A.tenant_id, A.trx_pos_id, ROW_NUMBER () OVER(ORDER BY C.product_id),
            C.product_id, f_get_product_balance_non_serialnumber_by_product_id(C.product_id, A.tenant_id),
            f_get_product_base_uom_id(C.product_id), C.qty_order, A.curr_code,
            C.unit_price, vFlgOuBuPkp, CASE WHEN vFlgOuBuPkp = vFlgYes THEN f_get_tax_by_tenant_id(A.tenant_id) ELSE vNullRefId END,
            CASE WHEN vFlgOuBuPkp = vFlgYes THEN f_get_tax_percentage_by_tenant_id(A.tenant_id) ELSE 0 END, 0,
            vEmptyString, 0, (C.item_discount_order + C.discount_header_on_item)/C.qty_order, 0,
            0, C.sub_grand_total_order_amount - C.discount_header_on_item, vEmptyString, 0, f_get_group_product_ou(A.ou_id, C.product_id),
            pDatetime, vUserId, pDatetime, vUserId,
            vNullRefId, vNullRefId
    FROM tt_i_trx_pos A
    INNER JOIN sl_order_item C ON C.order_id = A.trx_pos_id
    WHERE A.session_id = vSessionId;

    --buatkan product balance untuk yg belum ada product balance nya
    UPDATE tt_i_trx_pos_item A
    SET product_balance_id = nextval('in_product_balance_seq')
    WHERE A.session_id = vSessionId AND
          A.product_balance_id = vNullRefId AND
          NOT EXISTS (
            SELECT 1 FROM in_product_balance B
            WHERE B.product_id = A.product_id AND
                  B.lot_number = vSpaceString AND
                  B.serial_number = vSpaceString AND
                  B.tenant_id = A.tenant_id
          );

    INSERT INTO public.in_product_balance(
            product_balance_id, tenant_id, product_id, serial_number, lot_number,
            product_expired_date, product_year_made, version, create_datetime,
            create_user_id, update_datetime, update_user_id)
    SELECT  product_balance_id, tenant_id, product_id, vSpaceString, vSpaceString,
            vSpaceString, vSpaceString, 0, pDatetime,
            vUserId, pDatetime, vUserId
    FROM tt_i_trx_pos_item A
    WHERE A.session_id = vSessionId AND
          NOT EXISTS ( SELECT 1
                       FROM in_product_balance B
                       WHERE B.product_balance_id = A.product_balance_id);

    --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
    IF vFlgOuBuPkp = vFlgYes THEN
        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
        GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.tax_id, B.flg_amount,
               A.tax_percentage;
    END IF;

    --buatkan product balance stock jika tidak ada product balance stocknya
    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 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
        WHERE B.session_id = vSessionId
        GROUP BY B.product_id, B.product_balance_id, A.warehouse_id, B.tenant_id
    )
    INSERT INTO in_product_balance_stock(
            tenant_id, warehouse_id, product_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.warehouse_id, A.product_id,
           A.product_balance_id, A.product_status, f_get_product_base_uom_id(A.product_id), 0, 0,
           pDatetime, vUserId, pDatetime, vUserId
    FROM summary_qty A
    WHERE NOT EXISTS (
            SELECT 1 FROM in_product_balance_stock B
            WHERE B.product_id = A.product_id AND
                  B.product_balance_id = A.product_balance_id AND
                  B.product_status = A.product_status AND
                  B.tenant_id = A.tenant_id AND
                  B.warehouse_id = A.warehouse_id);

    --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 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
        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, A.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
    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, B.product_balance_id, B.base_uom_id, A.warehouse_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;

      --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 + Z.add_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;

      --===============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, A.curr_code,
            A.total_amount, A.doc_date, 1, 1,
            A.total_amount, vEmptyString, 0, pDatetime, vUserId,
            pDatetime, vUserId
    FROM tt_i_trx_pos A
    WHERE A.session_id = vSessionId;

    --insert ke cash bank balance degan amount 0
    WITH cashbank_balance_grouped AS (
        SELECT  A.tenant_id, A.ou_id, f_get_bank_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_bank_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_bank_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_bank_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;

      --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;

      --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 sl_order A
    SET flg_process = vFlgYes,
        end_process_intgr_datetime = to_char(current_timestamp,'yyyyMMddHH24mmss')
    FROM tt_i_trx_pos B
    WHERE A.order_id = B.ref_id AND
          A.doc_type_id = B.ref_doc_type_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;
  /