abirama62

func api import customer 20210114

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