abirama62

API Import Customer

Jan 19th, 2021
1,153
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.     vOneValueLong          BIGINT           := 1;
  21.     vJobPic               character varying(5);
  22.     vComboPriceLevel      character varying(20);
  23.     vGroupPartnerCustomer character varying(5);
  24.     vPartnerCodeCustomer  character varying(10);
  25.     vCurrIDR              character varying(5);
  26.     vPlafonActiveDateType character varying(50);
  27.     vPriceLevelActiveDateType character varying(50);
  28.     vDueDateActiveDateType character varying(50);
  29.     vCustomerRankType     character varying(50);
  30.     vActionInsert         character varying(1);
  31.     vActionUpdate         character varying(1);
  32.     vCash                 character varying(4);
  33.     vMonday               character varying(6);
  34.    
  35. BEGIN
  36.     vCash                 := 'CASH';
  37.     vMonday               := 'MONDAY';
  38.     vJobPic               := 'PIC';
  39.     vComboPriceLevel      := 'PRICELEVEL';
  40.     vGroupPartnerCustomer := 'C';
  41.     vPartnerCodeCustomer  := 'CUST';
  42.     vCurrIDR              := 'IDR';
  43.     vPlafonActiveDateType := 'tanggal_aktif_plafon';
  44.     vPriceLevelActiveDateType := 'tanggal_aktif_price_level';
  45.     vDueDateActiveDateType := 'tanggal_aktif_jatuh_tempo';
  46.     vCustomerRankType     := 'customer_rank';
  47.     vActionInsert         := 'A';
  48.     vActionUpdate         := 'U';
  49.  
  50.     --Update action = 'U', and set customer_id = m_partner.partner_id
  51.     UPDATE tt_migrate_customer_from_wiser Z
  52.     SET action = vActionUpdate, customer_id = A.partner_id
  53.     FROM m_partner A
  54.     WHERE A.tenant_id = pTenantId
  55.     AND A.partner_code = Z.customer_code
  56.     AND Z.session_id = pSessionId;
  57.  
  58.     --Update action = 'A' if customer/partner not exists
  59.     UPDATE tt_migrate_customer_from_wiser Z
  60.     SET action = vActionInsert, customer_id = nextval('partner_seq')
  61.     WHERE Z.session_id = pSessionId
  62.     AND NOT EXISTS(
  63.         SELECT 1 FROM m_partner A
  64.         WHERE A.tenant_id = pTenantId
  65.         AND A.partner_code = Z.customer_code
  66.     );
  67.  
  68.     --update category partner
  69.     UPDATE m_ctgr_partner A
  70.     SET ctgr_partner_name = B.category_partner_name, update_datetime = pDatetime, update_user_id = pUserId, version=+1
  71.     FROM tt_migrate_customer_from_wiser B
  72.     WHERE A.ctgr_partner_code = B.category_partner_code
  73.     AND B.session_id = pSessionId
  74.     AND A.tenant_id = pTenantId;
  75.  
  76.     --insert category partner
  77.     INSERT INTO m_ctgr_partner(tenant_id, ctgr_partner_code, ctgr_partner_name, create_datetime, create_user_id,
  78.         update_datetime, update_user_id, version, active, active_datetime, non_active_datetime)
  79.     SELECT pTenantId, A.category_partner_code, A.category_partner_name, pDatetime, pUserId, pDatetime, pUserId,
  80.         vZeroConstant, vYes, pDatetime, vEmptyValue
  81.     FROM tt_migrate_customer_from_wiser A
  82.     WHERE A.session_id = pSessionId
  83.     AND NOT EXISTS(
  84.         SELECT 1 FROM m_ctgr_partner B
  85.         WHERE B.tenant_id = pTenantId
  86.         AND B.ctgr_partner_code = A.category_partner_code
  87.     )
  88.     GROUP BY A.category_partner_code, A.category_partner_name;
  89.  
  90.     --update partner address
  91.     -- Mod by WTC, 20210119, update untuk address yg aktif saja, karena ada case khusus 1 customer memiliki 3 address, dimana
  92.     --   ketiga address tsb sdh digunakan di SO, sehingga agak repot jika mau dihapus.
  93.     UPDATE m_partner_address A
  94.     SET address_desc = B.address_desc, address1 = B.address1, address2 = B.address2, address3 = B.address3, city = B.city,
  95.         zip_code = B.zip_code, state_or_province = B.province, country = B.country_code, phone1 = B.phone1, phone2 = B.phone2,
  96.         fax1 = B.fax1, fax2 = B.fax2, longitude = B.longitude, latitude = B.latitude, update_datetime = pDatetime,
  97.         update_user_id = pUserId, version=+1
  98.     FROM tt_migrate_customer_from_wiser B
  99.     WHERE A.partner_id = B.customer_id
  100.     AND A.tenant_id = pTenantId
  101.     AND A.active = vYes;
  102.  
  103.     --insert partner address
  104.     INSERT INTO m_partner_address(tenant_id, partner_id, address_desc, address1, address2, address3, city, zip_code, state_or_province,
  105.         country,  phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id, update_datetime, update_user_id,
  106.         version, active, active_datetime, non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official, flg_others, longitude, latitude)
  107.     SELECT pTenantId, A.customer_id, A.address_desc, A.address1, A.address2, A.address3, A.city, A.zip_code, A.province,
  108.         A.country_code, A.phone1, A.phone2, A.fax1, A.fax2, vYes, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant,
  109.         vYes, pDatetime, vEmptyValue, vYes, vYes, vYes, vYes, vNo, A.longitude, A.latitude
  110.     FROM tt_migrate_customer_from_wiser A
  111.     WHERE A.session_id = pSessionId
  112.     AND NOT EXISTS(
  113.         SELECT 1 FROM m_partner_address B
  114.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  115.         WHERE B.tenant_id = pTenantId
  116.         AND B.partner_id = A.customer_id
  117.         AND B.active = vYes
  118.     );
  119.  
  120.     --update partner_cp
  121.     UPDATE m_partner_cp A
  122.     SET cp_name = B.contact_person_name, phone1 = B.contact_person_phone, cp_identity_no = B.contact_person_identity_no,
  123.         update_datetime = pDatetime, update_user_id = pUserId, version=+1
  124.     FROM tt_migrate_customer_from_wiser B
  125.     WHERE A.partner_id = B.customer_id
  126.     AND B.session_id = pSessionId
  127.     AND A.tenant_id = pTenantId;
  128.  
  129.  
  130.     --insert partner_cp
  131.     INSERT INTO m_partner_cp(tenant_id, partner_id, cp_name, cp_job, address1, address2, address3, city, zip_code,
  132.         country, phone1, phone2, fax1, fax2, email, create_datetime, create_user_id, update_datetime,
  133.         update_user_id, version, active, active_datetime, non_active_datetime, flg_responsibility, flg_email_notif,
  134.         flg_email_notif_ap, flg_email_notif_ar, cp_identity_no)
  135.     SELECT pTenantId, A.customer_id, A.contact_person_name, vJobPic, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
  136.         A.country_code, A.contact_person_phone, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId, pDatetime,
  137.         pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue,  vYes, vYes, vYes, vYes, A.contact_person_identity_no
  138.     FROM tt_migrate_customer_from_wiser A
  139.     WHERE A.session_id = pSessionId
  140.     AND NOT EXISTS(
  141.         SELECT 1 FROM m_partner_cp B
  142.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  143.         WHERE B.tenant_id = pTenantId
  144.         AND B.partner_id = A.customer_id
  145.     );
  146.    
  147.    
  148.      --insert partner_rel
  149.     INSERT INTO m_partner_rel (tenant_id, partner_id, relation_id, flg_bill, flg_shipp,
  150.         create_datetime, create_user_id, update_datetime, update_user_id, version, active,
  151.         active_datetime, non_active_datetime)
  152.     SELECT pTenantId, A.customer_id, A.customer_id, vYes, vYes,
  153.         pDatetime, pUserId, pDatetime, pUserId, vZeroConstant, vYes,
  154.         pDatetime, vEmptyValue
  155.     FROM tt_migrate_customer_from_wiser A
  156.     WHERE A.session_id = pSessionId
  157.     AND NOT EXISTS(
  158.         SELECT 1 FROM m_partner_rel B
  159.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  160.         WHERE B.tenant_id = pTenantId
  161.         AND B.partner_id = A.customer_id
  162.     );
  163.    
  164.    
  165.     --update
  166.  
  167.     --update partner npwp (jika flag npwp = Y)
  168.     UPDATE m_partner_npwp A
  169.     SET npwp_name = B.customer_name, npwp_no = B.npwp_no, npwp_date = B.npwp_date, update_datetime = pDatetime,
  170.         update_user_id = pUserId, version=+1, flg_pkp = B.flag_pkp
  171.     FROM tt_migrate_customer_from_wiser B
  172.     WHERE A.partner_id = B.customer_id
  173.     AND B.flag_npwp = vYes
  174.     AND A.tenant_id = pTenantId;
  175.  
  176.     --insert partner npwp (jika flag npwp = Y)
  177.     INSERT INTO m_partner_npwp(tenant_id, partner_id, npwp_name, npwp_no, npwp_date, address1, address2, address3, city,
  178.         zip_code, country, phone1, phone2, fax1, fax2, create_datetime, create_user_id, update_datetime, update_user_id,
  179.         version, active, active_datetime, non_active_datetime, flg_pkp, pkp_date)
  180.     SELECT pTenantId, A.customer_id, A.customer_name, A.npwp_no, A.npwp_date, vEmptyValue, vEmptyValue, vEmptyValue,
  181.         vEmptyValue, vEmptyValue, A.country_code, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, pDatetime, pUserId,
  182.         pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, A.flag_pkp, SUBSTRING(pDatetime, 1, 8)
  183.     FROM tt_migrate_customer_from_wiser A
  184.     WHERE A.session_id = pSessionId
  185.     AND A.flag_npwp = vYes
  186.     AND NOT EXISTS(
  187.         SELECT 1 FROM m_partner_npwp B
  188.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  189.         WHERE B.tenant_id = pTenantId
  190.         AND B.partner_id = A.customer_id
  191.     );
  192.  
  193.     --update m_partner_custom_for_sasa (flag_verified_nik)
  194.     UPDATE m_partner_custom_for_sasa A
  195.     SET flag_verified_nik = B.verified_nik,
  196.         update_datetime = pDatetime, update_user_id = pUserId, version=+1
  197.     FROM tt_migrate_customer_from_wiser B
  198.     WHERE A.partner_id = B.customer_id
  199.     AND NOT B.verified_nik = A.flag_verified_nik
  200.     AND A.tenant_id = pTenantId;
  201.  
  202.     --insert m_partner_custom_for_sasa (flag_verified_nik)
  203.     INSERT INTO m_partner_custom_for_sasa(tenant_id, partner_id, flag_verified_nik,
  204.         create_datetime, create_user_id, update_datetime, update_user_id, version)
  205.     SELECT pTenantId, A.customer_id, A.verified_nik,
  206.         pDatetime, pUserId, pDatetime, pUserId, vZeroConstant
  207.     FROM tt_migrate_customer_from_wiser A
  208.     WHERE A.session_id = pSessionId
  209.     AND NOT EXISTS(
  210.         SELECT 1 FROM m_partner_custom_for_sasa B
  211.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  212.         WHERE B.tenant_id = pTenantId
  213.         AND B.partner_id = A.customer_id
  214.     );
  215.  
  216.     --insert m_partner_ou (mapping OU untuk customer)
  217.     INSERT INTO m_partner_ou(partner_id, ou_id,
  218.         create_datetime, create_user_id, update_datetime, update_user_id, version)
  219.     SELECT A.customer_id, C.ou_id,
  220.         pDatetime, pUserId, pDatetime, pUserId, vZeroConstant
  221.     FROM tt_migrate_customer_from_wiser A
  222.     INNER JOIN t_ou C ON A.partner_ou_code = C.ou_code AND C.tenant_id = pTenantId
  223.     WHERE A.session_id = pSessionId
  224.     AND NOT EXISTS(
  225.         SELECT 1 FROM m_partner_ou B
  226.         INNER JOIN m_partner D ON B.partner_id = D.partner_id
  227.         INNER JOIN t_ou E ON B.ou_id = E.ou_id AND D.tenant_id = E.tenant_id
  228.         WHERE D.tenant_id = pTenantId
  229.         AND B.partner_id = A.customer_id
  230.         AND E.ou_code = A.partner_ou_code
  231.     );
  232.  
  233.     --insert ke partner_rel jika customer adalah toko alias
  234.     INSERT INTO m_partner_rel (tenant_id, partner_id, relation_id, flg_bill, flg_shipp,
  235.         create_datetime, create_user_id, update_datetime, update_user_id, version, active,
  236.         active_datetime, non_active_datetime)
  237.     SELECT pTenantId, A.customer_id, F.partner_id, vYes, vYes,
  238.         pDatetime, pUserId, pDatetime, pUserId, vZeroConstant, vYes,
  239.         pDatetime, vEmptyValue
  240.     FROM tt_migrate_customer_from_wiser A
  241.     INNER JOIN m_partner F ON A.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId
  242.     WHERE A.session_id = pSessionId
  243.     AND F.flg_holding = vYes
  244.     AND NOT EXISTS(
  245.         SELECT 1 FROM m_partner_rel B
  246.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  247.         WHERE B.tenant_id = pTenantId
  248.         AND B.partner_id = A.customer_id
  249.         AND B.relation_id = F.partner_id
  250.     );
  251.  
  252.     --if combo not exists, insert combo level price
  253.     INSERT INTO t_combo_value(combo_id, code, prop_key, code_group, sort_no, version, create_datetime, create_user_id,
  254.         update_datetime, update_user_id)
  255.     SELECT vComboPriceLevel, A.price_level, A.price_level, vEmptyValue, vZeroConstant, vZeroConstant, pDatetime,
  256.         pUserId, pDatetime, pUserId
  257.     FROM tt_migrate_customer_from_wiser A
  258.     WHERE A.session_id = pSessionId
  259.     AND NOT EXISTS (
  260.       SELECT 1 FROM t_combo_value C
  261.       WHERE C.combo_id = vComboPriceLevel
  262.       AND A.price_level = C.code)
  263.     GROUP BY A.price_level;
  264.  
  265.     --cek combo level price. if exists, then create log
  266.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  267.         create_datetime, create_user_id, update_datetime, update_user_id)
  268.     SELECT pTenantId, A.customer_id, vPriceLevelActiveDateType, COALESCE(B.new_data, vEmptyValue),
  269.       A.tanggal_aktif_price_level, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  270.     FROM tt_migrate_customer_from_wiser A
  271.     LEFT JOIN
  272.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  273.       FROM m_log_partner_change_data X
  274.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  275.       WHERE NOT EXISTS (
  276.           SELECT 1
  277.           FROM m_log_partner_change_data Z
  278.           WHERE Z.partner_id = X.partner_id
  279.           AND Z.create_datetime > X.create_datetime
  280.          )
  281.       AND X.data_changed_type = vPriceLevelActiveDateType
  282.       ) B ON A.customer_id = B.customer_id
  283.     WHERE A.session_id = pSessionId
  284.     AND A.tanggal_aktif_price_level <> B.new_data
  285.     AND EXISTS (
  286.       SELECT 1 FROM t_combo_value C
  287.       WHERE C.combo_id = vComboPriceLevel
  288.       AND A.price_level = C.code);
  289.  
  290.     --  update data di m_partner_type, jika partner sudah ada
  291.     UPDATE m_partner_type A
  292.     SET due_date = B.jatuh_tempo_tagihan, amount_limit = B.nilai_plafon, update_datetime = pDatetime,
  293.         update_user_id = pUserId, version=+1
  294.     FROM tt_migrate_customer_from_wiser B
  295.     WHERE B.session_id = pSessionId
  296.     AND A.partner_id = B.customer_id
  297.     AND A.tenant_id = pTenantId
  298.     AND A.group_partner = vGroupPartnerCustomer;
  299.  
  300.     -- insert data baru di m_partner_type, jika partner baru
  301.     INSERT INTO m_partner_type(tenant_id, partner_id, group_partner, type_partner_id, due_date, curr_code, amount_limit,
  302.         flg_commision, version, create_datetime, create_user_id, update_datetime, update_user_id, active,
  303.         active_datetime, non_active_datetime)
  304.     SELECT pTenantId, A.customer_id, vGroupPartnerCustomer, COALESCE(C.type_partner_id, vTypePartnerCustomer),
  305.         A.jatuh_tempo_tagihan, vCurrIDR, A.nilai_plafon, vNo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId,
  306.         vYes, pDatetime, vEmptyValue
  307.     FROM tt_migrate_customer_from_wiser A
  308.     INNER JOIN m_type_partner C ON C.tenant_id = pTenantId
  309.     WHERE A.session_id = pSessionId
  310.     AND C.group_partner = vGroupPartnerCustomer
  311.     AND C.type_partner_code = vPartnerCodeCustomer
  312.     AND NOT EXISTS(
  313.         SELECT 1 FROM m_partner_type B
  314.         WHERE B.tenant_id = pTenantId
  315.         AND B.partner_id = A.customer_id
  316.     );
  317.    
  318.     --===== m_cust_payment =====--
  319.      -- update due_payment_days di m_cust_payment, jika partner sudah ada
  320.     UPDATE m_cust_payment A
  321.     SET due_payment_days = B.jatuh_tempo_tagihan,
  322.     update_datetime = pDatetime, update_user_id = pUserId, version=+1
  323.     FROM tt_migrate_customer_from_wiser B
  324.     WHERE A.partner_id = B.customer_id
  325.     AND B.session_id = pSessionId
  326.     AND A.tenant_id = pTenantId;
  327.    
  328.     -- insert data baru di m_cust_payment, jika partner baru
  329.     INSERT INTO m_cust_payment(
  330.             tenant_id, partner_id, flg_invoice, due_payment_days,
  331.             flg_payment_mode, payment_day, payment_date, partner_bank_id,
  332.             create_datetime, create_user_id, update_datetime, update_user_id,
  333.             version, active, active_datetime, non_active_datetime)
  334.     SELECT pTenantId, A.customer_id, vNo, A.jatuh_tempo_tagihan,
  335.         vCash, vMonday, vOneValueLong, vEmptyId,
  336.         pDatetime, pUserId, pDatetime, pUserId,
  337.         vZeroConstant, vYes, pDatetime, vEmptyValue
  338.     FROM tt_migrate_customer_from_wiser A
  339.     WHERE A.session_id = pSessionId
  340.     AND NOT EXISTS(
  341.         SELECT 1 FROM m_cust_payment B
  342.         INNER JOIN m_partner C ON B.partner_id = C.partner_id AND B.tenant_id = C.tenant_id
  343.         WHERE B.tenant_id = pTenantId
  344.         AND B.partner_id = A.customer_id
  345.     );
  346.  
  347.    
  348.  
  349.     -- create log untuk tanggal aktif plafon dan tanggal aktif jatuh_tempo
  350.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  351.         create_datetime, create_user_id, update_datetime, update_user_id)
  352.     SELECT pTenantId, A.customer_id, vPlafonActiveDateType, COALESCE(B.new_data, vEmptyValue),
  353.       A.tanggal_aktif_plafon, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  354.     FROM tt_migrate_customer_from_wiser A
  355.     LEFT JOIN
  356.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  357.       FROM m_log_partner_change_data X
  358.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  359.       WHERE NOT EXISTS (
  360.           SELECT 1
  361.           FROM m_log_partner_change_data Z
  362.           WHERE Z.partner_id = X.partner_id
  363.           AND Z.create_datetime > X.create_datetime
  364.          )
  365.       AND X.data_changed_type = vPlafonActiveDateType
  366.       ) B ON A.customer_id = B.customer_id
  367.     WHERE A.session_id = pSessionId
  368.     AND A.tanggal_aktif_plafon <> B.new_data;
  369.  
  370.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  371.         create_datetime, create_user_id, update_datetime, update_user_id)
  372.     SELECT pTenantId, A.customer_id, vDueDateActiveDateType, COALESCE(B.new_data, vEmptyValue),
  373.       A.tanggal_aktif_jatuh_tempo, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  374.     FROM tt_migrate_customer_from_wiser A
  375.     LEFT JOIN
  376.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  377.       FROM m_log_partner_change_data X
  378.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  379.       WHERE NOT EXISTS (
  380.           SELECT 1
  381.           FROM m_log_partner_change_data Z
  382.           WHERE Z.partner_id = X.partner_id
  383.           AND Z.create_datetime > X.create_datetime
  384.          )
  385.       AND X.data_changed_type = vDueDateActiveDateType
  386.       ) B ON A.customer_id = B.customer_id
  387.     WHERE A.session_id = pSessionId
  388.     AND A.tanggal_aktif_jatuh_tempo <> B.new_data;
  389.  
  390.     --  create log if customer_rank changed
  391.     INSERT INTO m_log_partner_change_data(tenant_id, partner_id, data_changed_type, old_data, new_data, version,
  392.         create_datetime, create_user_id, update_datetime, update_user_id)
  393.     SELECT pTenantId, A.customer_id, vCustomerRankType, COALESCE(B.new_data, vEmptyValue),
  394.       A.customer_rank, vZeroConstant, pDatetime, pUserId, pDatetime, pUserId
  395.     FROM tt_migrate_customer_from_wiser A
  396.     LEFT JOIN
  397.       (SELECT X.new_data AS new_data, Y.customer_id AS customer_id
  398.       FROM m_log_partner_change_data X
  399.       INNER JOIN tt_migrate_customer_from_wiser Y ON X.partner_id = Y.customer_id
  400.       WHERE NOT EXISTS (
  401.           SELECT 1
  402.           FROM m_log_partner_change_data Z
  403.           WHERE Z.partner_id = X.partner_id
  404.           AND Z.create_datetime > X.create_datetime
  405.          )
  406.       AND X.data_changed_type = vCustomerRankType
  407.       ) B ON A.customer_id = B.customer_id
  408.     WHERE A.session_id = pSessionId
  409.     AND A.customer_rank <> B.new_data;
  410.  
  411.     --  update m_partner, if action = U
  412.     UPDATE m_partner A
  413.     SET partner_name = B.customer_name, ctgr_partner_id = C.ctgr_partner_id, npwp_id = COALESCE(E.npwp_id, vEmptyId),
  414.         holding_id = COALESCE(F.partner_id, vEmptyId), flg_holding = B.flg_holding,
  415.         rank = B.customer_rank, price_level = B.price_level, npwp_status = B.npwp_status,
  416.         update_datetime = pDatetime, update_user_id = pUserId, version=+1
  417.     FROM tt_migrate_customer_from_wiser B
  418.     INNER JOIN m_ctgr_partner C ON B.category_partner_code = C.ctgr_partner_code
  419.     LEFT JOIN m_partner_npwp E ON B.customer_id = E.partner_id AND E.tenant_id = pTenantId
  420.     LEFT JOIN m_partner F ON B.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId AND F.flg_holding = vYes
  421.     WHERE B.session_id = pSessionId
  422.     AND A.partner_id = B.customer_id
  423.     AND A.partner_code = B.customer_code
  424.     AND A.tenant_id = pTenantId
  425.     AND B.action = vActionUpdate;
  426.  
  427.     --  insert m_partner, if action = A
  428.     INSERT INTO m_partner--()
  429.     SELECT A.customer_id, pTenantId, A.customer_code, A.customer_name, C.ctgr_partner_id, vEmptyValue, COALESCE(E.npwp_id, vEmptyId),
  430.         COALESCE(F.partner_id, vEmptyId), A.flg_holding,
  431.         A.customer_rank, pDatetime, pUserId, pDatetime, pUserId, vZeroConstant, vYes, pDatetime, vEmptyValue, vEmptyValue,
  432.         A.price_level, vEmptyValue, A.npwp_status
  433.     FROM tt_migrate_customer_from_wiser A
  434.     INNER JOIN m_ctgr_partner C ON A.category_partner_code = C.ctgr_partner_code
  435.     LEFT JOIN m_partner_npwp E ON A.customer_id = E.partner_id AND E.tenant_id = pTenantId
  436.     LEFT JOIN m_partner F ON A.holding_customer_code = F.partner_code AND F.tenant_id = pTenantId AND F.flg_holding = vYes
  437.     WHERE A.session_id = pSessionId
  438.     AND A.action = vActionInsert;
  439.  
  440.     --  count inserted/updated data
  441.     SELECT COUNT(action) INTO vCountInsert
  442.     FROM tt_migrate_customer_from_wiser A
  443.     WHERE A.session_id = pSessionId
  444.     AND A.action = vActionInsert;
  445.  
  446.     SELECT COUNT(action) INTO vCountUpdate
  447.     FROM tt_migrate_customer_from_wiser A
  448.     WHERE A.session_id = pSessionId
  449.     AND A.action = vActionUpdate;
  450.  
  451. END;
  452. $BODY$
  453.   LANGUAGE plpgsql VOLATILE
  454.   COST 100;
  455.   /
RAW Paste Data