f_insert_update_data_customer

CREATE OR REPLACE FUNCTION f_insert_update_data_customer (bigint, character varying, bigint, character varying)
RETURNS void AS
$BODY$

DECLARE
      pTenantId                     ALIAS FOR $1;
    pSessionId              ALIAS FOR $2;
    pUserId                 ALIAS FOR $3;
    pDatetime               ALIAS FOR $4;
    pRefCountResult               REFCURSOR := 'refCountResult';

    vYes                    character varying := 'Y';
    vNo                     character varying := 'N';
    vEmptyValue           character varying := '';
    vEmptyId                BIGINT            := -99;
    vZeroConstant         BIGINT            := 0;
    vTypePartnerCustomer  BIGINT            := 30;
    vCountInsert          BIGINT;
    vCountUpdate          BIGINT;
    vJobPic               character varying(5);
    vComboPriceLevel      character varying(20);
    vGroupPartnerCustomer character varying(5);
    vCurrIDR              character varying(5);
    vPlafonActiveDateType character varying(50);
    vPriceLevelActiveDateType character varying(50);
    vDueDateActiveDateType character varying(50);
    vCustomerRankType     character varying(50);
    vActionInsert         character varying(1);
    vActionUpdate         character varying(1);
BEGIN
    vJobPic               := 'PIC';
    vComboPriceLevel      := 'PRICELEVEL';
    vGroupPartnerCustomer := 'C';
    vCurrIDR              := 'IDR';
    vPlafonActiveDateType := 'tanggal_aktif_plafon';
    vPriceLevelActiveDateType := 'tanggal_aktif_price_level';
    vDueDateActiveDateType := 'tanggal_aktif_jatuh_tempo';
    vCustomerRankType     := 'customer_rank';
    vActionInsert         := 'A';
    vActionUpdate         := 'U';

    --Update action = 'U', and set customer_id = m_partner.partner_id
    UPDATE tt_migrate_customer_from_wiser Z
    SET action = vActionUpdate, customer_id = A.partner_id
    FROM m_partner A
    WHERE A.tenant_id = pTenantId
    AND A.partner_code = Z.customer_code
    AND Z.session_id = pSessionId;

    --Update action = 'A' if customer/partner not exists
    UPDATE tt_migrate_customer_from_wiser Z
    SET action = vActionInsert, customer_id = nextval('partner_seq')
    WHERE Z.session_id = pSessionId
    AND NOT EXISTS(
        SELECT 1 FROM m_partner A
        WHERE A.tenant_id = pTenantId
        AND A.partner_code = Z.customer_code
    );

    --update category partner
    UPDATE m_ctgr_partner A
    SET ctgr_partner_name = B.category_partner_name, update_datetime = pDatetime, update_user_id = pUserId, version=+1
    FROM tt_migrate_customer_from_wiser B
    WHERE A.ctgr_partner_code = B.category_partner_code
    AND A.tenant_id = pTenantId;

    --insert category partner
    INSERT INTO m_ctgr_partner(tenant_id, ctgr_partner_code, ctgr_partner_name, create_datetime, create_user_id,
        update_datetime, update_user_id, version, active, active_datetime, non_active_datetime)
    SELECT pTenantId, A.category_partner_code, A.category_partner_name, pDatetime, pUserId, pDatetime, pUserId,
        vZeroConstant, vYes, pDatetime, vEmptyValue
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND NOT EXISTS(
        SELECT 1 FROM m_ctgr_partner B
        WHERE B.tenant_id = pTenantId
        AND B.ctgr_partner_code = A.category_partner_code
    );

    --update partner address
    UPDATE m_partner_address A
    SET address_desc = B.address_desc, address1 = B.address1, address2 = B.address2, address3 = B.address3, city = B.city,
        zip_code = B.zip_code, state_or_province = B.province, country = B.country_code, phone1 = B.phone1, phone2 = B.phone2,
        fax1 = B.fax1, fax2 = B.fax2, longitude = B.longitude, latitude = B.latitude, update_datetime = pDatetime,
        update_user_id = pUserId, version=+1
    FROM tt_migrate_customer_from_wiser B
    WHERE A.partner_id = B.customer_id
    AND A.tenant_id = pTenantId;

    --insert partner address
    INSERT INTO m_partner_address(tenant_id, partner_id, address_desc, address1, address2, address3, city, zip_code, state_or_province,
        country,  phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id, update_datetime, update_user_id,
        version, active, active_datetime, non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official, flg_others, longitude, latitude)
    SELECT pTenantId, A.customer_id, A.address_desc, A.address1, A.address2, A.address3, A.city, A.zip_code, A.province,
        A.country_code, A.phone1, A.phone2, A.fax1, A.fax2, vYes, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant,
        vYes, pDatetime, vEmptyValue, vYes, vYes, vYes, vYes, vNo, A.longitude, A.latitude
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND NOT EXISTS(
        SELECT 1 FROM m_partner_address B
        INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
        WHERE B.tenant_id = pTenantId
        AND B.partner_id = A.customer_id
    );

    --update partner_cp
    UPDATE m_partner_cp A
    SET cp_name = B.contact_person_name, phone1 = B.contact_person_phone, cp_identity_no = B.contact_person_identity_no,
        update_datetime = pDatetime, update_user_id = pUserId, version=+1
    FROM tt_migrate_customer_from_wiser B
    WHERE A.partner_id = B.customer_id
    AND A.tenant_id = pTenantId;


    --insert partner_cp
    INSERT INTO m_partner_cp(tenant_id, partner_id, cp_name, cp_job, address1, address2, address3, city, zip_code,
        country, phone1, phone2, fax1, fax2, email, create_datetime, create_user_id, update_datetime,
        update_user_id, version, active, active_datetime, non_active_datetime, flg_responsibility, flg_email_notif,
        flg_email_notif_ap, flg_email_notif_ar, cp_identity_no)
    SELECT pTenantId, A.customer_id, A.contact_person_name, vJobPic, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
        A.country_code, A.contact_person_phone, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId, pDatetime,
        pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue,  vYes, vYes, vYes, vYes, A.contact_person_identity_no
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND NOT EXISTS(
        SELECT 1 FROM m_partner_cp B
        INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
        WHERE B.tenant_id = pTenantId
        AND B.partner_id = A.customer_id
    );

    --update partner npwp (jika flag npwp = Y)
    UPDATE m_partner_npwp A
    SET npwp_name = B.customer_name, npwp_no = B.npwp_no, npwp_date = B.npwp_date, update_datetime = pDatetime,
        update_user_id = pUserId, version=+1, flg_pkp = B.flag_pkp
    FROM tt_migrate_customer_from_wiser B
    WHERE A.partner_id = B.customer_id
    AND B.flag_npwp = vYes
    AND A.tenant_id = pTenantId;

    --insert partner npwp (jika flag npwp = Y)
    INSERT INTO m_partner_npwp(tenant_id, partner_id, npwp_name, npwp_no, npwp_date, address1, address2, address3, city,
        zip_code, country, phone1, phone2, fax1, fax2, create_datetime, create_user_id, update_datetime, update_user_id,
        version, active, active_datetime, non_active_datetime, flg_pkp, pkp_date)
    SELECT pTenantId, A.customer_id, A.customer_name, A.npwp_no, A.npwp_date, vEmptyValue, vEmptyValue, vEmptyValue,
        vEmptyValue, vEmptyValue, A.country_code, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId,
        pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, A.flag_pkp, SUBSTRING(pDatetime, 1, 8)
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND A.flag_npwp = vYes
    AND NOT EXISTS(
        SELECT 1 FROM m_partner_npwp B
        INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
        WHERE B.tenant_id = pTenantId
        AND B.partner_id = A.customer_id
    );

    --if combo not exists, insert combo level price. then create log
    INSERT INTO t_combo_value(combo_id, code, prop_key, code_group, sort_no, version, create_datetime, create_user_id,
        update_datetime, update_user_id)
    SELECT vComboPriceLevel, A.price_level, A.price_level, vEmptyValue, vZeroConstant, vZeroConstant, pDatetime,
        pUserId, pDatetime, pUserId
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND NOT EXISTS (
      SELECT 1 FROM t_combo_value C
      WHERE C.combo_id = vComboPriceLevel
      AND A.price_level = C.code);

    --cek combo level price. if exists, then create log
    INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.customer_id, vPriceLevelActiveDateType, COALESCE(B.new_data, vEmptyValue),
      A.tanggal_aktif_price_level, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_migrate_customer_from_wiser A
    LEFT JOIN
      (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
      FROM m_log_partner_change_data X
      INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
      WHERE NOT EXISTS (
          SELECT 1
          FROM m_log_partner_change_data Z
          WHERE Z.partner_id = X.partner_id
          AND Z.create_datetime > X.create_datetime
         )
      AND X.data_changed_type = vPriceLevelActiveDateType
      ) B ON A.customer_id = B.customer_id
    WHERE A.session_id = pSessionId
    AND A.tanggal_aktif_price_level <> B.new_data
    AND EXISTS (
      SELECT 1 FROM t_combo_value C
      WHERE C.combo_id = vComboPriceLevel
      AND A.price_level = C.code);

    --  update data di m_partner_type, jika partner sudah ada
    UPDATE m_partner_type A
    SET due_date = B.jatuh_tempo_tagihan, amount_limit = B.nilai_plafon, update_datetime = pDatetime,
        update_user_id = pUserId, version=+1
    FROM tt_migrate_customer_from_wiser B
    WHERE B.session_id = pSessionId
    AND A.partner_id = B.customer_id
    AND A.tenant_id = pTenantId;

    -- insert data baru di m_partner_type, jika partner baru
    INSERT INTO m_partner_type(tenant_id, partner_id, group_partner, type_partner_id, due_date, curr_code, amount_limit,
        flg_commision, version, create_datetime, create_user_id, update_datetime, update_user_id, active,
        active_datetime, non_active_datetime)
    SELECT pTenantId, A.customer_id, vGroupPartnerCustomer, vTypePartnerCustomer, A.jatuh_tempo_tagihan, vCurrIDR,
        A.nilai_plafon, vNo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId, vYes, pDatetime, vEmptyValue
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND NOT EXISTS(
        SELECT 1 FROM m_partner_type B
        WHERE B.tenant_id = pTenantId
        AND B.partner_id = A.customer_id
    );

    -- create log untuk tanggal aktif plafon dan tanggal aktif jatuh_tempo
    INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.customer_id, vPlafonActiveDateType, COALESCE(B.new_data, vEmptyValue),
      A.tanggal_aktif_plafon, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_migrate_customer_from_wiser A
    LEFT JOIN
      (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
      FROM m_log_partner_change_data X
      INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
      WHERE NOT EXISTS (
          SELECT 1
          FROM m_log_partner_change_data Z
          WHERE Z.partner_id = X.partner_id
          AND Z.create_datetime > X.create_datetime
         )
      AND X.data_changed_type = vPlafonActiveDateType
      ) B ON A.customer_id = B.customer_id
    WHERE A.session_id = pSessionId
    AND A.tanggal_aktif_plafon <> B.new_data;

    INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.customer_id, vDueDateActiveDateType, COALESCE(B.new_data, vEmptyValue),
      A.tanggal_aktif_jatuh_tempo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_migrate_customer_from_wiser A
    LEFT JOIN
      (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
      FROM m_log_partner_change_data X
      INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
      WHERE NOT EXISTS (
          SELECT 1
          FROM m_log_partner_change_data Z
          WHERE Z.partner_id = X.partner_id
          AND Z.create_datetime > X.create_datetime
         )
      AND X.data_changed_type = vDueDateActiveDateType
      ) B ON A.customer_id = B.customer_id
    WHERE A.session_id = pSessionId
    AND A.tanggal_aktif_jatuh_tempo <> B.new_data;

    --  create log if customer_rank changed
    INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.customer_id, vCustomerRankType, COALESCE(B.new_data, vEmptyValue),
      A.customer_rank, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_migrate_customer_from_wiser A
    LEFT JOIN
      (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
      FROM m_log_partner_change_data X
      INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
      WHERE NOT EXISTS (
          SELECT 1
          FROM m_log_partner_change_data Z
          WHERE Z.partner_id = X.partner_id
          AND Z.create_datetime > X.create_datetime
         )
      AND X.data_changed_type = vCustomerRankType
      ) B ON A.customer_id = B.customer_id
    WHERE A.session_id = pSessionId
    AND A.customer_rank <> B.new_data;

    --  update m_partner, if action = U
    UPDATE m_partner A
    SET partner_name = B.customer_name, ctgr_partner_id = C.ctgr_partner_id, npwp_id = COALESCE(E.npwp_id, vEmptyId),
        holding_id = COALESCE(F.partner_id, vEmptyId), flg_holding = CASE
          WHEN B.holding_customer_code <> vEmptyValue THEN vYes ELSE vNo END,
        rank = B.customer_rank, price_level = B.price_level, npwp_status = B.npwp_status,
        update_datetime = pDatetime, update_user_id = pUserId, version=+1
    FROM tt_migrate_customer_from_wiser B
    INNER JOIN m_ctgr_partner C ON B.category_partner_code = C.ctgr_partner_code
    LEFT JOIN m_partner_npwp E ON B.customer_id = E.partner_id AND E.tenant_id = pTenantId
    LEFT JOIN m_partner F ON B.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId
    WHERE B.session_id = pSessionId
    AND B.action = vActionUpdate;

    --  insert m_partner, if action = A
    INSERT INTO m_partner--()
    SELECT A.customer_id, pTenantId, A.customer_code, A.customer_name, C.ctgr_partner_id, vEmptyValue, COALESCE(E.npwp_id, vEmptyId),
        COALESCE(F.partner_id, vEmptyId), CASE
          WHEN A.holding_customer_code <> vEmptyValue THEN vYes ELSE vNo END,
        A.customer_rank, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, vEmptyValue,
        A.price_level, vEmptyValue, A.npwp_status
    FROM tt_migrate_customer_from_wiser A
    INNER JOIN m_ctgr_partner C ON A.category_partner_code = C.ctgr_partner_code
    LEFT JOIN m_partner_npwp E ON A.customer_id = E.partner_id AND E.tenant_id = pTenantId
    LEFT JOIN m_partner F ON A.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId
    WHERE A.session_id = pSessionId
    AND A.action = vActionInsert;

    --  count inserted/updated data
    SELECT COUNT(action) INTO vCountInsert
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND A.action = vActionInsert;

    SELECT COUNT(action) INTO vCountUpdate
    FROM tt_migrate_customer_from_wiser A
    WHERE A.session_id = pSessionId
    AND A.action = vActionUpdate;

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