abirama62

f_insert_update_data_customer

May 29th, 2020
200
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_insert_update_data_customer (bigint, character varying, bigint, character varying)
  2. RETURNS void AS
  3. $BODY$
  4.  
  5. DECLARE
  6.       pTenantId                     ALIAS FOR $1;
  7.     pSessionId              ALIAS FOR $2;
  8.     pUserId                 ALIAS FOR $3;
  9.     pDatetime               ALIAS FOR $4;
  10.     pRefCountResult               REFCURSOR := 'refCountResult';
  11.  
  12.     vYes                    character varying := 'Y';
  13.     vNo                     character varying := 'N';
  14.     vEmptyValue           character varying := '';
  15.     vEmptyId                BIGINT            := -99;
  16.     vZeroConstant         BIGINT            := 0;
  17.     vTypePartnerCustomer  BIGINT            := 30;
  18.     vCountInsert          BIGINT;
  19.     vCountUpdate          BIGINT;
  20.     vJobPic               character varying(5);
  21.     vComboPriceLevel      character varying(20);
  22.     vGroupPartnerCustomer character varying(5);
  23.     vCurrIDR              character varying(5);
  24.     vPlafonActiveDateType character varying(50);
  25.     vPriceLevelActiveDateType character varying(50);
  26.     vDueDateActiveDateType character varying(50);
  27.     vCustomerRankType     character varying(50);
  28.     vActionInsert         character varying(1);
  29.     vActionUpdate         character varying(1);
  30. BEGIN
  31.     vJobPic               := 'PIC';
  32.     vComboPriceLevel      := 'PRICELEVEL';
  33.     vGroupPartnerCustomer := 'C';
  34.     vCurrIDR              := 'IDR';
  35.     vPlafonActiveDateType := 'tanggal_aktif_plafon';
  36.     vPriceLevelActiveDateType := 'tanggal_aktif_price_level';
  37.     vDueDateActiveDateType := 'tanggal_aktif_jatuh_tempo';
  38.     vCustomerRankType     := 'customer_rank';
  39.     vActionInsert         := 'A';
  40.     vActionUpdate         := 'U';
  41.  
  42.     --Update action = 'U', and set customer_id = m_partner.partner_id
  43.     UPDATE tt_migrate_customer_from_wiser Z
  44.     SET action = vActionUpdate, customer_id = A.partner_id
  45.     FROM m_partner A
  46.     WHERE A.tenant_id = pTenantId
  47.     AND A.partner_code = Z.customer_code
  48.     AND Z.session_id = pSessionId;
  49.  
  50.     --Update action = 'A' if customer/partner not exists
  51.     UPDATE tt_migrate_customer_from_wiser Z
  52.     SET action = vActionInsert, customer_id = nextval('partner_seq')
  53.     WHERE Z.session_id = pSessionId
  54.     AND NOT EXISTS(
  55.         SELECT 1 FROM m_partner A
  56.         WHERE A.tenant_id = pTenantId
  57.         AND A.partner_code = Z.customer_code
  58.     );
  59.  
  60.     --update category partner
  61.     UPDATE m_ctgr_partner A
  62.     SET ctgr_partner_name = B.category_partner_name, update_datetime = pDatetime, update_user_id = pUserId, version=+1
  63.     FROM tt_migrate_customer_from_wiser B
  64.     WHERE A.ctgr_partner_code = B.category_partner_code
  65.     AND A.tenant_id = pTenantId;
  66.  
  67.     --insert category partner
  68.     INSERT INTO m_ctgr_partner(tenant_id, ctgr_partner_code, ctgr_partner_name, create_datetime, create_user_id,
  69.         update_datetime, update_user_id, version, active, active_datetime, non_active_datetime)
  70.     SELECT pTenantId, A.category_partner_code, A.category_partner_name, pDatetime, pUserId, pDatetime, pUserId,
  71.         vZeroConstant, vYes, pDatetime, vEmptyValue
  72.     FROM tt_migrate_customer_from_wiser A
  73.     WHERE A.session_id = pSessionId
  74.     AND NOT EXISTS(
  75.         SELECT 1 FROM m_ctgr_partner B
  76.         WHERE B.tenant_id = pTenantId
  77.         AND B.ctgr_partner_code = A.category_partner_code
  78.     );
  79.  
  80.     --update partner address
  81.     UPDATE m_partner_address A
  82.     SET address_desc = B.address_desc, address1 = B.address1, address2 = B.address2, address3 = B.address3, city = B.city,
  83.         zip_code = B.zip_code, state_or_province = B.province, country = B.country_code, phone1 = B.phone1, phone2 = B.phone2,
  84.         fax1 = B.fax1, fax2 = B.fax2, longitude = B.longitude, latitude = B.latitude, update_datetime = pDatetime,
  85.         update_user_id = pUserId, version=+1
  86.     FROM tt_migrate_customer_from_wiser B
  87.     WHERE A.partner_id = B.customer_id
  88.     AND A.tenant_id = pTenantId;
  89.  
  90.     --insert partner address
  91.     INSERT INTO m_partner_address(tenant_id, partner_id, address_desc, address1, address2, address3, city, zip_code, state_or_province,
  92.         country,  phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id, update_datetime, update_user_id,
  93.         version, active, active_datetime, non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official, flg_others, longitude, latitude)
  94.     SELECT pTenantId, A.customer_id, A.address_desc, A.address1, A.address2, A.address3, A.city, A.zip_code, A.province,
  95.         A.country_code, A.phone1, A.phone2, A.fax1, A.fax2, vYes, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant,
  96.         vYes, pDatetime, vEmptyValue, vYes, vYes, vYes, vYes, vNo, A.longitude, A.latitude
  97.     FROM tt_migrate_customer_from_wiser A
  98.     WHERE A.session_id = pSessionId
  99.     AND NOT EXISTS(
  100.         SELECT 1 FROM m_partner_address B
  101.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  102.         WHERE B.tenant_id = pTenantId
  103.         AND B.partner_id = A.customer_id
  104.     );
  105.  
  106.     --update partner_cp
  107.     UPDATE m_partner_cp A
  108.     SET cp_name = B.contact_person_name, phone1 = B.contact_person_phone, cp_identity_no = B.contact_person_identity_no,
  109.         update_datetime = pDatetime, update_user_id = pUserId, version=+1
  110.     FROM tt_migrate_customer_from_wiser B
  111.     WHERE A.partner_id = B.customer_id
  112.     AND A.tenant_id = pTenantId;
  113.  
  114.  
  115.     --insert partner_cp
  116.     INSERT INTO m_partner_cp(tenant_id, partner_id, cp_name, cp_job, address1, address2, address3, city, zip_code,
  117.         country, phone1, phone2, fax1, fax2, email, create_datetime, create_user_id, update_datetime,
  118.         update_user_id, version, active, active_datetime, non_active_datetime, flg_responsibility, flg_email_notif,
  119.         flg_email_notif_ap, flg_email_notif_ar, cp_identity_no)
  120.     SELECT pTenantId, A.customer_id, A.contact_person_name, vJobPic, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
  121.         A.country_code, A.contact_person_phone, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId, pDatetime,
  122.         pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue,  vYes, vYes, vYes, vYes, A.contact_person_identity_no
  123.     FROM tt_migrate_customer_from_wiser A
  124.     WHERE A.session_id = pSessionId
  125.     AND NOT EXISTS(
  126.         SELECT 1 FROM m_partner_cp B
  127.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  128.         WHERE B.tenant_id = pTenantId
  129.         AND B.partner_id = A.customer_id
  130.     );
  131.  
  132.     --update partner npwp (jika flag npwp = Y)
  133.     UPDATE m_partner_npwp A
  134.     SET npwp_name = B.customer_name, npwp_no = B.npwp_no, npwp_date = B.npwp_date, update_datetime = pDatetime,
  135.         update_user_id = pUserId, version=+1, flg_pkp = B.flag_pkp
  136.     FROM tt_migrate_customer_from_wiser B
  137.     WHERE A.partner_id = B.customer_id
  138.     AND B.flag_npwp = vYes
  139.     AND A.tenant_id = pTenantId;
  140.  
  141.     --insert partner npwp (jika flag npwp = Y)
  142.     INSERT INTO m_partner_npwp(tenant_id, partner_id, npwp_name, npwp_no, npwp_date, address1, address2, address3, city,
  143.         zip_code, country, phone1, phone2, fax1, fax2, create_datetime, create_user_id, update_datetime, update_user_id,
  144.         version, active, active_datetime, non_active_datetime, flg_pkp, pkp_date)
  145.     SELECT pTenantId, A.customer_id, A.customer_name, A.npwp_no, A.npwp_date, vEmptyValue, vEmptyValue, vEmptyValue,
  146.         vEmptyValue, vEmptyValue, A.country_code, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId,
  147.         pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, A.flag_pkp, SUBSTRING(pDatetime, 1, 8)
  148.     FROM tt_migrate_customer_from_wiser A
  149.     WHERE A.session_id = pSessionId
  150.     AND A.flag_npwp = vYes
  151.     AND NOT EXISTS(
  152.         SELECT 1 FROM m_partner_npwp B
  153.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  154.         WHERE B.tenant_id = pTenantId
  155.         AND B.partner_id = A.customer_id
  156.     );
  157.  
  158.     --if combo not exists, insert combo level price. then create log
  159.     INSERT INTO t_combo_value(combo_id, code, prop_key, code_group, sort_no, version, create_datetime, create_user_id,
  160.         update_datetime, update_user_id)
  161.     SELECT vComboPriceLevel, A.price_level, A.price_level, vEmptyValue, vZeroConstant, vZeroConstant, pDatetime,
  162.         pUserId, pDatetime, pUserId
  163.     FROM tt_migrate_customer_from_wiser A
  164.     WHERE A.session_id = pSessionId
  165.     AND NOT EXISTS (
  166.       SELECT 1 FROM t_combo_value C
  167.       WHERE C.combo_id = vComboPriceLevel
  168.       AND A.price_level = C.code);
  169.  
  170.     --cek combo level price. if exists, then create log
  171.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  172.         create_datetime, create_user_id, update_datetime, update_user_id)
  173.     SELECT pTenantId, A.customer_id, vPriceLevelActiveDateType, COALESCE(B.new_data, vEmptyValue),
  174.       A.tanggal_aktif_price_level, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  175.     FROM tt_migrate_customer_from_wiser A
  176.     LEFT JOIN
  177.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  178.       FROM m_log_partner_change_data X
  179.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  180.       WHERE NOT EXISTS (
  181.           SELECT 1
  182.           FROM m_log_partner_change_data Z
  183.           WHERE Z.partner_id = X.partner_id
  184.           AND Z.create_datetime > X.create_datetime
  185.          )
  186.       AND X.data_changed_type = vPriceLevelActiveDateType
  187.       ) B ON A.customer_id = B.customer_id
  188.     WHERE A.session_id = pSessionId
  189.     AND A.tanggal_aktif_price_level <> B.new_data
  190.     AND EXISTS (
  191.       SELECT 1 FROM t_combo_value C
  192.       WHERE C.combo_id = vComboPriceLevel
  193.       AND A.price_level = C.code);
  194.  
  195.     --  update data di m_partner_type, jika partner sudah ada
  196.     UPDATE m_partner_type A
  197.     SET due_date = B.jatuh_tempo_tagihan, amount_limit = B.nilai_plafon, update_datetime = pDatetime,
  198.         update_user_id = pUserId, version=+1
  199.     FROM tt_migrate_customer_from_wiser B
  200.     WHERE B.session_id = pSessionId
  201.     AND A.partner_id = B.customer_id
  202.     AND A.tenant_id = pTenantId;
  203.  
  204.     -- insert data baru di m_partner_type, jika partner baru
  205.     INSERT INTO m_partner_type(tenant_id, partner_id, group_partner, type_partner_id, due_date, curr_code, amount_limit,
  206.         flg_commision, version, create_datetime, create_user_id, update_datetime, update_user_id, active,
  207.         active_datetime, non_active_datetime)
  208.     SELECT pTenantId, A.customer_id, vGroupPartnerCustomer, vTypePartnerCustomer, A.jatuh_tempo_tagihan, vCurrIDR,
  209.         A.nilai_plafon, vNo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId, vYes, pDatetime, vEmptyValue
  210.     FROM tt_migrate_customer_from_wiser A
  211.     WHERE A.session_id = pSessionId
  212.     AND NOT EXISTS(
  213.         SELECT 1 FROM m_partner_type B
  214.         WHERE B.tenant_id = pTenantId
  215.         AND B.partner_id = A.customer_id
  216.     );
  217.  
  218.     -- create log untuk tanggal aktif plafon dan tanggal aktif jatuh_tempo
  219.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  220.         create_datetime, create_user_id, update_datetime, update_user_id)
  221.     SELECT pTenantId, A.customer_id, vPlafonActiveDateType, COALESCE(B.new_data, vEmptyValue),
  222.       A.tanggal_aktif_plafon, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  223.     FROM tt_migrate_customer_from_wiser A
  224.     LEFT JOIN
  225.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  226.       FROM m_log_partner_change_data X
  227.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  228.       WHERE NOT EXISTS (
  229.           SELECT 1
  230.           FROM m_log_partner_change_data Z
  231.           WHERE Z.partner_id = X.partner_id
  232.           AND Z.create_datetime > X.create_datetime
  233.          )
  234.       AND X.data_changed_type = vPlafonActiveDateType
  235.       ) B ON A.customer_id = B.customer_id
  236.     WHERE A.session_id = pSessionId
  237.     AND A.tanggal_aktif_plafon <> B.new_data;
  238.  
  239.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  240.         create_datetime, create_user_id, update_datetime, update_user_id)
  241.     SELECT pTenantId, A.customer_id, vDueDateActiveDateType, COALESCE(B.new_data, vEmptyValue),
  242.       A.tanggal_aktif_jatuh_tempo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  243.     FROM tt_migrate_customer_from_wiser A
  244.     LEFT JOIN
  245.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  246.       FROM m_log_partner_change_data X
  247.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  248.       WHERE NOT EXISTS (
  249.           SELECT 1
  250.           FROM m_log_partner_change_data Z
  251.           WHERE Z.partner_id = X.partner_id
  252.           AND Z.create_datetime > X.create_datetime
  253.          )
  254.       AND X.data_changed_type = vDueDateActiveDateType
  255.       ) B ON A.customer_id = B.customer_id
  256.     WHERE A.session_id = pSessionId
  257.     AND A.tanggal_aktif_jatuh_tempo <> B.new_data;
  258.  
  259.     --  create log if customer_rank changed
  260.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  261.         create_datetime, create_user_id, update_datetime, update_user_id)
  262.     SELECT pTenantId, A.customer_id, vCustomerRankType, COALESCE(B.new_data, vEmptyValue),
  263.       A.customer_rank, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  264.     FROM tt_migrate_customer_from_wiser A
  265.     LEFT JOIN
  266.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  267.       FROM m_log_partner_change_data X
  268.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  269.       WHERE NOT EXISTS (
  270.           SELECT 1
  271.           FROM m_log_partner_change_data Z
  272.           WHERE Z.partner_id = X.partner_id
  273.           AND Z.create_datetime > X.create_datetime
  274.          )
  275.       AND X.data_changed_type = vCustomerRankType
  276.       ) B ON A.customer_id = B.customer_id
  277.     WHERE A.session_id = pSessionId
  278.     AND A.customer_rank <> B.new_data;
  279.  
  280.     --  update m_partner, if action = U
  281.     UPDATE m_partner A
  282.     SET partner_name = B.customer_name, ctgr_partner_id = C.ctgr_partner_id, npwp_id = COALESCE(E.npwp_id, vEmptyId),
  283.         holding_id = COALESCE(F.partner_id, vEmptyId), flg_holding = CASE
  284.           WHEN B.holding_customer_code <> vEmptyValue THEN vYes ELSE vNo END,
  285.         rank = B.customer_rank, price_level = B.price_level, npwp_status = B.npwp_status,
  286.         update_datetime = pDatetime, update_user_id = pUserId, version=+1
  287.     FROM tt_migrate_customer_from_wiser B
  288.     INNER JOIN m_ctgr_partner C ON B.category_partner_code = C.ctgr_partner_code
  289.     LEFT JOIN m_partner_npwp E ON B.customer_id = E.partner_id AND E.tenant_id = pTenantId
  290.     LEFT JOIN m_partner F ON B.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId
  291.     WHERE B.session_id = pSessionId
  292.     AND B.action = vActionUpdate;
  293.  
  294.     --  insert m_partner, if action = A
  295.     INSERT INTO m_partner--()
  296.     SELECT A.customer_id, pTenantId, A.customer_code, A.customer_name, C.ctgr_partner_id, vEmptyValue, COALESCE(E.npwp_id, vEmptyId),
  297.         COALESCE(F.partner_id, vEmptyId), CASE
  298.           WHEN A.holding_customer_code <> vEmptyValue THEN vYes ELSE vNo END,
  299.         A.customer_rank, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, vEmptyValue,
  300.         A.price_level, vEmptyValue, A.npwp_status
  301.     FROM tt_migrate_customer_from_wiser A
  302.     INNER JOIN m_ctgr_partner C ON A.category_partner_code = C.ctgr_partner_code
  303.     LEFT JOIN m_partner_npwp E ON A.customer_id = E.partner_id AND E.tenant_id = pTenantId
  304.     LEFT JOIN m_partner F ON A.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId
  305.     WHERE A.session_id = pSessionId
  306.     AND A.action = vActionInsert;
  307.  
  308.     --  count inserted/updated data
  309.     SELECT COUNT(action) INTO vCountInsert
  310.     FROM tt_migrate_customer_from_wiser A
  311.     WHERE A.session_id = pSessionId
  312.     AND A.action = vActionInsert;
  313.  
  314.     SELECT COUNT(action) INTO vCountUpdate
  315.     FROM tt_migrate_customer_from_wiser A
  316.     WHERE A.session_id = pSessionId
  317.     AND A.action = vActionUpdate;
  318.  
  319. END;
  320. $BODY$
  321.   LANGUAGE plpgsql VOLATILE
  322.   COST 100;
  323.   /
RAW Paste Data