daily pastebin goal
59%
SHARE
TWEET

ul_upload_customer

a guest Nov 9th, 2018 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION ul_upload_customer(bigint)
  2.   RETURNS bigint AS
  3. $BODY$
  4. DECLARE
  5.     pUploadHeaderId         ALIAS FOR $1;
  6.    
  7.     vFail                   character varying(4) := 'FAIL';
  8.     vOk                     character varying(2) := 'OK';
  9.     vYes                    character varying(1) := 'Y';
  10.     vNo                     character varying(1) := 'N';
  11.     vGood                   character varying(1) := 'G';
  12.     vActionInsert           character varying(1) := 'I';
  13.     vActionUpdate           character varying(1) := 'U';
  14.     vActionError            character varying(1) := 'E';
  15.     vGroupCustomer          character varying(1) := 'C';
  16.     vEmpty                  character varying(1) := '';
  17.     vSpaceValue             character varying(1) := ' ';
  18.      
  19.     vTenantId               bigint;
  20.     vUserId                 bigint;
  21.     vTypePartnerId          bigint;
  22.     vOuCompanyId            bigint;
  23.     vDatetime               character varying(14);
  24.     vCurrCode               character varying;
  25.    
  26.     vDefaultRoleId          bigint := -99;
  27.     vDefaultPolicyId        bigint := -99;
  28.     vEmptyId                bigint := -99;
  29.     vCountFail              bigint := 0;
  30.    
  31. BEGIN
  32.     -- siapkan parameter
  33.     vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
  34.     vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
  35.     vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
  36.  
  37.     -- get default role
  38.     SELECT role_id INTO vDefaultRoleId
  39.     FROM t_role
  40.     WHERE role_name = f_get_value_system_config_by_param_code(vTenantId, 'default.role.customer');
  41.    
  42.     IF FOUND THEN
  43.    
  44.         -- RESET TEMP TABLE
  45.         DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
  46.         DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
  47.         DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
  48.        
  49.         -- INIT data temp table
  50.         INSERT INTO tt_ul_customer_unique(
  51.                 upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
  52.                 kode_kategori, rank, line_of_business, industry_type, npwp_status,
  53.                 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
  54.                 npwp, status_pkp, active)
  55.         SELECT upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
  56.                 kode_kategori, rank, line_of_business, industry_type, npwp_status,
  57.                 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
  58.                 npwp, status_pkp, active
  59.         FROM ul_customer
  60.         WHERE upload_header_id = pUploadHeaderId
  61.         AND trim(kode_pelanggan) <> vEmpty
  62.         GROUP BY upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
  63.                 kode_kategori, rank, line_of_business, industry_type, npwp_status,
  64.                 price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
  65.                 npwp, status_pkp, active;
  66.    
  67.         INSERT INTO tt_ul_customer_partner_address(
  68.                 upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
  69.                 address_3, zip_code, city, state_or_province, country, official_addr,
  70.                 shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
  71.                 fax_1, fax_2)
  72.         SELECT upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
  73.                 address_3, zip_code, city, state_or_province, country, official_addr,
  74.                 shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
  75.                 fax_1, fax_2
  76.         FROM ul_customer
  77.         WHERE upload_header_id = pUploadHeaderId
  78.         AND trim(kode_pelanggan) <> vEmpty
  79.         AND trim(address_desc) <> vEmpty;
  80.        
  81.         INSERT INTO tt_ul_customer_partner_cp(
  82.                 upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
  83.                 cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
  84.                 cp_mobile_2)
  85.         SELECT upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
  86.                 cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
  87.                 cp_mobile_2
  88.         FROM ul_customer
  89.         WHERE upload_header_id = pUploadHeaderId
  90.         AND trim(kode_pelanggan) <> vEmpty
  91.         AND trim(cp_name) <> vEmpty;
  92.        
  93.         -- GET id type partner CUST
  94.         SELECT type_partner_id INTO vTypePartnerId
  95.         FROM m_type_partner
  96.         WHERE type_partner_code = 'CUST';
  97.        
  98.        
  99.         -- Update kolom yang kosong ke nilai default
  100.         UPDATE ul_customer A
  101.         SET longitude = '0'
  102.         WHERE A.upload_header_id = pUploadHeaderId
  103.             AND TRIM(A.longitude) = vEmpty;
  104.            
  105.         UPDATE ul_customer A
  106.         SET latitude = '0'
  107.         WHERE A.upload_header_id = pUploadHeaderId
  108.             AND TRIM(A.latitude) = vEmpty;
  109.        
  110.         -- Validasi
  111.         -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
  112.         -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
  113.         -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
  114.         -- # Nama external jika diisi, maka max characternya harus 100
  115.         -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
  116.         -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
  117.         -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
  118.         -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
  119.         -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
  120.         -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
  121.         -- # TOP in days wajib diisi & harus integer
  122.         -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
  123.         -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
  124.         -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
  125.         -- # Address Desc wajib diisi & Max character Address Desc adalah 100
  126.         -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
  127.         -- # Max character Address 2 adalah 100 jika diisi
  128.         -- # Max character Address 3 adalah 100 jika diisi
  129.         -- # Max character Zip Code adalah 100 jika diisi
  130.         -- # City wajib diisi & harus terdaftar di system (m_city)
  131.         -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
  132.         -- # Official addr wajib diisi dengan nilai Y/N
  133.         -- # Shipping Addr Wajib diisi dengan nilai Y/N
  134.         -- # Billing Addr Wajib diisi dengan nilai Y/N
  135.         -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
  136.         -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
  137.         -- # Max character Phone 1 adalah 100, jika diisi
  138.         -- # Max character Phone 2 adalah 100, jika diisi
  139.         -- # Max character Fax 1 adalah 100, jika diisi
  140.         -- # Max character Fax 2 adalah 100, jika diisi
  141.         -- # CP Name wajib diisi dan max character adalah 100
  142.         -- # CP Job wajib diisi dan max character adalah 100
  143.         -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
  144.         -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
  145.         -- # Max character CP Email adalah 100 jika diisi
  146.         -- # Max character CP Phone 1 adalah 100 jika diisi
  147.         -- # Max character CP Phone 2 adalah 100 jika diisi
  148.         -- # Max character CP Mobile 1 adalah 100 jika diisi
  149.         -- # Max character CP Mobile 2 adalah 100 jika diisi
  150.         -- # Jika NPWP diisi maka max characternya adalah 50
  151.         -- # Jika Status PKP diisi, maka nilainya harus Y/N
  152.         -- # Active harus diisi dengan nilai Y/N
  153.         -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"-- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
  154.         -- # Validasi Address desc harus unique untuk kode pelanggan yang sama
  155.         -- # Validasi CP Name harus unique untuk kode pelanggan yang sama
  156.            
  157.         -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
  158.         UPDATE ul_customer A
  159.         SET status = vFail,
  160.             message = message || 'Customer code must be filled, '
  161.         WHERE A.upload_header_id = pUploadHeaderId
  162.             AND TRIM(A.kode_pelanggan) = vEmpty;
  163.            
  164.         UPDATE ul_customer A
  165.         SET status = vFail,
  166.             message = message || 'Max customer code is 50 character, '
  167.         WHERE A.upload_header_id = pUploadHeaderId
  168.             AND length(A.kode_pelanggan) > 50;
  169.            
  170.         -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
  171.         UPDATE ul_customer A
  172.         SET status = vFail,
  173.             message = message || 'Code already registered on system for non customer partner, '
  174.         WHERE A.upload_header_id = pUploadHeaderId
  175.             AND EXISTS (
  176.                 SELECT 1 FROM m_partner Z
  177.                 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
  178.                 WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
  179.                 AND Y.type_partner_id <> vTypePartnerId
  180.             );
  181.            
  182.         -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
  183.         UPDATE ul_customer A
  184.         SET status = vFail,
  185.             message = message || 'Customer name must be filled, '
  186.         WHERE A.upload_header_id = pUploadHeaderId
  187.             AND TRIM(A.nama_pelanggan) = vEmpty;
  188.            
  189.         UPDATE ul_customer A
  190.         SET status = vFail,
  191.             message = message || 'Max Customer name is 1024 character, '
  192.         WHERE A.upload_header_id = pUploadHeaderId
  193.             AND length(A.nama_pelanggan) > 1024;
  194.            
  195.         -- # Nama external jika diisi, maka max characternya harus 100
  196.         UPDATE ul_customer A
  197.         SET status = vFail,
  198.             message = message || 'Max external name is 100 character, '
  199.         WHERE A.upload_header_id = pUploadHeaderId
  200.             AND TRIM(A.nama_external) <> vEmpty
  201.             AND length(A.nama_external) > 100;
  202.            
  203.         -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
  204.         UPDATE ul_customer A
  205.         SET status = vFail,
  206.             message = message || 'Category code must be filled, '
  207.         WHERE A.upload_header_id = pUploadHeaderId
  208.             AND TRIM(A.kode_kategori) = vEmpty;
  209.            
  210.         UPDATE ul_customer A
  211.         SET status = vFail,
  212.             message = message || 'Category is not registred on system, '
  213.         WHERE A.upload_header_id = pUploadHeaderId
  214.             AND TRIM(A.kode_kategori) <> vEmpty
  215.             AND NOT EXISTS(
  216.                 SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori AND Z.tenant_id = vTenantId
  217.             );
  218.            
  219.         -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
  220.         UPDATE ul_customer A
  221.         SET status = vFail,
  222.             message = message || 'Rank must be filled, '
  223.         WHERE A.upload_header_id = pUploadHeaderId
  224.             AND TRIM(A.rank) = vEmpty;
  225.            
  226.         UPDATE ul_customer A
  227.         SET status = vFail,
  228.             message = message || 'Rank is not registred on system, '
  229.         WHERE A.upload_header_id = pUploadHeaderId
  230.             AND TRIM(A.rank) <> vEmpty
  231.             AND NOT EXISTS(
  232.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'PARTNERRANK' AND Z.code = A.rank
  233.             );
  234.            
  235.         -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
  236.         UPDATE ul_customer A
  237.         SET status = vFail,
  238.             message = message || 'Line of business is not registred on system, '
  239.         WHERE A.upload_header_id = pUploadHeaderId
  240.             AND TRIM(A.line_of_business) <> vEmpty
  241.             AND NOT EXISTS(
  242.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'LINEOFBUSINESS' AND Z.code = A.line_of_business
  243.             );
  244.            
  245.         -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
  246.         UPDATE ul_customer A
  247.         SET status = vFail,
  248.             message = message || 'Industry Type must be filled, '
  249.         WHERE A.upload_header_id = pUploadHeaderId
  250.             AND TRIM(A.industry_type) = vEmpty;
  251.        
  252.         UPDATE ul_customer A
  253.         SET status = vFail,
  254.             message = message || 'Industry Type is not registred on system, '
  255.         WHERE A.upload_header_id = pUploadHeaderId
  256.             AND TRIM(A.industry_type) <> vEmpty
  257.             AND NOT EXISTS(
  258.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'INDUSTRYTYPE' AND Z.code = A.industry_type
  259.             );
  260.            
  261.         -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
  262.         UPDATE ul_customer A
  263.         SET status = vFail,
  264.             message = message || 'NPWP status must be filled, '
  265.         WHERE A.upload_header_id = pUploadHeaderId
  266.             AND TRIM(A.npwp_status) = vEmpty;
  267.        
  268.         UPDATE ul_customer A
  269.         SET status = vFail,
  270.             message = message || 'NPWP status is not registred on system, '
  271.         WHERE A.upload_header_id = pUploadHeaderId
  272.             AND TRIM(A.npwp_status) <> vEmpty
  273.             AND NOT EXISTS(
  274.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'TAXCODE' AND Z.code = A.npwp_status
  275.             );
  276.            
  277.         -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
  278.         UPDATE ul_customer A
  279.         SET status = vFail,
  280.             message = message || 'Price level must be filled, '
  281.         WHERE A.upload_header_id = pUploadHeaderId
  282.             AND TRIM(A.price_level) = vEmpty;
  283.            
  284.         UPDATE ul_customer A
  285.         SET status = vFail,
  286.             message = message || 'Price level is not registred on system, '
  287.         WHERE A.upload_header_id = pUploadHeaderId
  288.             AND TRIM(A.price_level) <> vEmpty
  289.             AND NOT EXISTS(
  290.                 SELECT 1 FROM m_level_price Z WHERE Z.level_price_code = A.price_level AND Z.tenant_id = vTenantId
  291.             );
  292.            
  293.         -- # TOP in days wajib diisi & harus integer, dan harus >= 0
  294.         UPDATE ul_customer A
  295.         SET status = vFail,
  296.             message = message || 'TOP in days must be filled, '
  297.         WHERE A.upload_header_id = pUploadHeaderId
  298.             AND TRIM(A.top_in_days) = vEmpty;
  299.            
  300.         UPDATE ul_customer A
  301.         SET status = vFail,
  302.             message = message || 'TOP in days must be integer, '
  303.         WHERE A.upload_header_id = pUploadHeaderId
  304.             AND TRIM(top_in_days) <> vEmpty
  305.             AND NOT is_integer(A.top_in_days);
  306.            
  307.         WITH data_customer AS (
  308.             SELECT ul_customer_id, top_in_days
  309.             FROM ul_customer
  310.             WHERE upload_header_id = pUploadHeaderId
  311.             AND TRIM(top_in_days) <> vEmpty
  312.             AND is_integer(top_in_days)
  313.         )  
  314.         UPDATE ul_customer A
  315.         SET status = vFail,
  316.             message = message || 'TOP in days must be greater than or equals 0, '
  317.         FROM data_customer B
  318.         WHERE A.ul_customer_id = B.ul_customer_id
  319.             AND B.top_in_days::numeric < 0;
  320.            
  321.         -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
  322.         UPDATE ul_customer A
  323.         SET status = vFail,
  324.             message = message || 'Region code must be filled, '
  325.         WHERE A.upload_header_id = pUploadHeaderId
  326.             AND TRIM(A.kode_region) = vEmpty;
  327.            
  328.         UPDATE ul_customer A
  329.         SET status = vFail,
  330.             message = message || 'Region code is not registred on system, '
  331.         WHERE A.upload_header_id = pUploadHeaderId
  332.             AND TRIM(A.kode_region) <> vEmpty
  333.             AND NOT EXISTS(
  334.                 SELECT 1 FROM m_region Z WHERE Z.region_code = A.kode_region AND Z.tenant_id = vTenantId
  335.             );
  336.            
  337.         -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
  338.         UPDATE ul_customer A
  339.         SET status = vFail,
  340.             message = message || 'Credit limit must be filled, '
  341.         WHERE A.upload_header_id = pUploadHeaderId
  342.             AND TRIM(A.credit_limit) = vEmpty;
  343.            
  344.         UPDATE ul_customer A
  345.         SET status = vFail,
  346.             message = message || 'Credit limit must be numeric, '
  347.         WHERE A.upload_header_id = pUploadHeaderId
  348.             AND TRIM(credit_limit) <> vEmpty
  349.             AND NOT is_numeric(A.credit_limit);
  350.            
  351.         WITH data_customer AS (
  352.             SELECT ul_customer_id, credit_limit
  353.             FROM ul_customer
  354.             WHERE upload_header_id = pUploadHeaderId
  355.             AND TRIM(credit_limit) <> vEmpty
  356.             AND is_numeric(credit_limit)
  357.         )  
  358.         UPDATE ul_customer A
  359.         SET status = vFail,
  360.             message = message || 'Credit limit must be greater than or equals -1, '
  361.         FROM data_customer B
  362.         WHERE A.ul_customer_id = B.ul_customer_id
  363.             AND B.credit_limit::numeric < -1;
  364.            
  365.         -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
  366.         UPDATE ul_customer A
  367.         SET status = vFail,
  368.             message = message || 'Disc reguler pct must be filled, '
  369.         WHERE A.upload_header_id = pUploadHeaderId
  370.             AND TRIM(A.disc_reguler_pct) = vEmpty;
  371.            
  372.         UPDATE ul_customer A
  373.         SET status = vFail,
  374.             message = message || 'Disc reguler pct must be numeric, '
  375.         WHERE A.upload_header_id = pUploadHeaderId
  376.             AND TRIM(disc_reguler_pct) <> vEmpty
  377.             AND NOT is_numeric(A.disc_reguler_pct);
  378.            
  379.         WITH data_customer AS (
  380.             SELECT ul_customer_id, disc_reguler_pct
  381.             FROM ul_customer
  382.             WHERE upload_header_id = pUploadHeaderId
  383.             AND TRIM(disc_reguler_pct) <> vEmpty
  384.             AND is_numeric(disc_reguler_pct)
  385.         ), val_greater_or_equals_zero AS (
  386.             UPDATE ul_customer A
  387.             SET status = vFail,
  388.                 message = message || 'Disc reguler pct must be greater than or equals to zero, '
  389.             FROM data_customer B
  390.             WHERE A.ul_customer_id = B.ul_customer_id
  391.                 AND B.disc_reguler_pct::numeric < 0
  392.         )
  393.         UPDATE ul_customer A
  394.         SET status = vFail,
  395.             message = message || 'Disc reguler pct must be less than or equals to 100, '
  396.         FROM data_customer B
  397.         WHERE A.ul_customer_id = B.ul_customer_id
  398.             AND B.disc_reguler_pct::numeric > 100;
  399.            
  400.         -- # Setiap kode customer baru harus memiliki minimal 1 partner address baru
  401.         UPDATE ul_customer A
  402.         SET status = vFail,
  403.             message = message || 'Customer must have address at least 1, '
  404.         WHERE A.upload_header_id = pUploadHeaderId
  405.         AND TRIM(A.kode_pelanggan) <> vEmpty
  406.         AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
  407.         AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
  408.                             WHERE Z.upload_header_id = pUploadHeaderId
  409.                             AND Z.kode_pelanggan = A.kode_pelanggan);
  410.        
  411.         -- # Partner address harus unique by address desc untuk setiap kode pelanggan
  412.         WITH data_partner_address AS (
  413.             SELECT A.kode_pelanggan, A.address_desc
  414.             FROM tt_ul_customer_partner_address A
  415.             WHERE A.upload_header_id = pUploadHeaderId
  416.             GROUP BY A.kode_pelanggan, A.address_desc
  417.             HAVING COUNT(1) > 1
  418.         )
  419.         UPDATE ul_customer A
  420.         SET status = vFail,
  421.             message = message || 'Duplicate address desc, '
  422.         WHERE A.upload_header_id = pUploadHeaderId
  423.         AND EXISTS (
  424.             SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_desc = Z.address_desc
  425.         );
  426.        
  427.         -- # Pada partner address desc yang sudah ada di system, address 1 & address 2 & address 3 nya harus belum ada di system
  428.         WITH partner_desc_existing AS (
  429.             SELECT A.upload_header_id, A.kode_pelanggan, A.address_desc, A.address_1, A.address_2, A.address_3
  430.             FROM tt_ul_customer_partner_address A
  431.             WHERE A.upload_header_id = pUploadHeaderId
  432.             AND EXISTS (
  433.                 SELECT 1 FROM m_partner_address Z
  434.                 WHERE A.kode_pelanggan = f_get_partner_code(Z.partner_id)
  435.                 AND Z.tenant_id = vTenantId
  436.             )
  437.         )
  438.         UPDATE ul_customer A
  439.         SET status = vFail,
  440.             message = message || 'Address 1 and address 2 and address 3 already exists on system, '
  441.         FROM partner_desc_existing B
  442.         WHERE A.upload_header_id = pUploadHeaderId
  443.         AND A.upload_header_id = B.upload_header_id
  444.         AND A.kode_pelanggan = B.kode_pelanggan
  445.         AND A.address_1 = B.address_1
  446.         AND A.address_2 = B.address_2
  447.         AND A.address_3 = B.address_3
  448.         AND EXISTS (
  449.             SELECT 1 FROM m_partner_address Z
  450.                 WHERE B.kode_pelanggan = f_get_partner_code(Z.partner_id)
  451.                 AND Z.address1 = B.address_1
  452.                 AND Z.address2 = B.address_2
  453.                 AND Z.address3 = B.address_3
  454.                 AND Z.tenant_id = vTenantId
  455.                 AND Z.address_desc <> B.address_desc
  456.         );
  457.        
  458.         -- # Partner address harus unique by address 1, address 2, address 3 untuk setiap kode pelanggan
  459.         WITH data_partner_address AS (
  460.             SELECT A.kode_pelanggan, A.address_1, A.address_2, A.address_3
  461.             FROM tt_ul_customer_partner_address A
  462.             WHERE A.upload_header_id = pUploadHeaderId
  463.             GROUP BY A.kode_pelanggan, A.address_1, A.address_2, A.address_3
  464.             HAVING COUNT(1) > 1
  465.         )
  466.         UPDATE ul_customer A
  467.         SET status = vFail,
  468.             message = message || 'Duplicate address 1 and address 2 and address 3, '
  469.         WHERE A.upload_header_id = pUploadHeaderId
  470.         AND EXISTS (
  471.             SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_1 = Z.address_1 AND A.address_2 = Z.address_2 AND A.address_3 = Z.address_3
  472.         );
  473.        
  474.         -- # Official address yang bernilai Y harus cuma ada 1 untuk tiap customer
  475.         WITH data_official_addr AS (
  476.             SELECT A.kode_pelanggan, A.official_addr
  477.             FROM tt_ul_customer_partner_address A
  478.             WHERE A.upload_header_id = pUploadHeaderId
  479.             AND A.official_addr = vYes
  480.             GROUP BY A.kode_pelanggan, A.official_addr
  481.             HAVING COUNT(1) > 1
  482.         )
  483.         UPDATE ul_customer A
  484.         SET status = vFail,
  485.             message = message || 'Customer can only have 1 official address, '
  486.         WHERE A.upload_header_id = pUploadHeaderId
  487.         AND EXISTS (
  488.             SELECT 1 FROM data_official_addr Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.official_addr = Z.official_addr
  489.         );
  490.        
  491.         -- # Untuk customer baru harus punya partner address dengan flg official = Y minimal 1
  492.         UPDATE ul_customer A
  493.         SET status = vFail,
  494.             message = message || 'Customer must have exactly one official address, '
  495.         WHERE A.upload_header_id = pUploadHeaderId
  496.         AND TRIM(A.kode_pelanggan) <> vEmpty
  497.         AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
  498.         AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
  499.                             WHERE Z.upload_header_id = pUploadHeaderId
  500.                             AND Z.kode_pelanggan = A.kode_pelanggan
  501.                             AND Z.official_addr = vYes);
  502.                            
  503.         -- # Untuk customer existing hanya boleh mempunyai 1 Official address
  504.         WITH data_official_address AS (
  505.             SELECT A.kode_pelanggan, COUNT(1) AS result
  506.             FROM tt_ul_customer_partner_address A
  507.             WHERE A.upload_header_id = pUploadHeaderId
  508.             AND A.official_addr = vYes
  509.             GROUP BY A.kode_pelanggan
  510.            
  511.             UNION ALL
  512.            
  513.             SELECT f_get_partner_code(A.partner_id) AS kode_pelanggan, COUNT(1) AS result
  514.             FROM m_partner_address A
  515.             WHERE A.flg_official = vYes
  516.             AND EXISTS (
  517.                 SELECT 1 FROM tt_ul_customer_unique Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
  518.             )
  519.             AND NOT EXISTS (
  520.                 SELECT 1 FROM tt_ul_customer_partner_address Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
  521.                 AND A.address_desc = Z.address_desc
  522.             )
  523.             GROUP BY A.partner_id
  524.         ), sum_data_official_address AS (
  525.             SELECT kode_pelanggan, SUM(result)
  526.             FROM data_official_address
  527.             GROUP BY kode_pelanggan
  528.             HAVING SUM(result) > 1
  529.         )
  530.         UPDATE ul_customer A
  531.         SET status = vFail,
  532.             message = message || 'Customer must have exactly 1 official address include existing address, '
  533.         WHERE A.upload_header_id = pUploadHeaderId
  534.         AND EXISTS (
  535.             SELECT 1 FROM m_partner Z WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
  536.         )
  537.         AND EXISTS (
  538.             SELECT 1 FROM sum_data_official_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan
  539.         );
  540.            
  541.         -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
  542.         UPDATE ul_customer A
  543.         SET status = vFail,
  544.             message = message || 'Address desc must be filled, '
  545.         WHERE A.upload_header_id = pUploadHeaderId
  546.             AND TRIM(A.kode_pelanggan) <> vEmpty
  547.             AND TRIM(A.address_desc) = vEmpty
  548.             AND TRIM(A.address_1
  549.                     ||A.address_2
  550.                     ||A.address_3
  551.                     ||A.zip_code
  552.                     ||A.city
  553.                     ||A.country
  554.                     ||A.official_addr
  555.                     ||A.shipping_addr
  556.                     ||A.billing_addr
  557.                     ||CASE WHEN A.longitude='0' THEN vEmpty ELSE A.longitude END
  558.                     ||CASE WHEN A.latitude='0' THEN vEmpty ELSE A.latitude END
  559.                     ||A.phone_1
  560.                     ||A.phone_2
  561.                     ||A.fax_1
  562.                     ||A.fax_2) <> vEmpty;
  563.            
  564.         UPDATE ul_customer A
  565.         SET status = vFail,
  566.             message = message || 'Max address desc is 100 character, '
  567.         WHERE A.upload_header_id = pUploadHeaderId
  568.             AND TRIM(A.address_desc) <> vEmpty
  569.             AND length(A.address_desc) > 100;
  570.            
  571.         -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
  572.         UPDATE ul_customer A
  573.         SET status = vFail,
  574.             message = message || 'Address 1 must be filled, '
  575.         WHERE A.upload_header_id = pUploadHeaderId
  576.             AND TRIM(A.address_desc) <> vEmpty
  577.             AND TRIM(A.address_1) = vEmpty;
  578.            
  579.         UPDATE ul_customer A
  580.         SET status = vFail,
  581.             message = message || 'Max address 1 is 100 character, '
  582.         WHERE A.upload_header_id = pUploadHeaderId
  583.             AND TRIM(A.address_1) <> vEmpty
  584.             AND length(A.address_1) > 100;
  585.            
  586.         -- # Max character Address 2 adalah 100 jika diisi
  587.         UPDATE ul_customer A
  588.         SET status = vFail,
  589.             message = message || 'Max address 2 is 100 character, '
  590.         WHERE A.upload_header_id = pUploadHeaderId
  591.             AND TRIM(A.address_2) <> vEmpty
  592.             AND length(A.address_2) > 100;
  593.            
  594.         -- # Max character Address 3 adalah 100 jika diisi
  595.         UPDATE ul_customer A
  596.         SET status = vFail,
  597.             message = message || 'Max address 3 is 100 character, '
  598.         WHERE A.upload_header_id = pUploadHeaderId
  599.             AND TRIM(A.address_3) <> vEmpty
  600.             AND length(A.address_3) > 100;
  601.            
  602.         -- # Max character Zip Code adalah 100 jika diisi
  603.         UPDATE ul_customer A
  604.         SET status = vFail,
  605.             message = message || 'Max zip code is 100 character, '
  606.         WHERE A.upload_header_id = pUploadHeaderId
  607.             AND TRIM(A.zip_code) <> vEmpty
  608.             AND length(A.zip_code) > 100;
  609.        
  610.         -- # City wajib diisi & harus terdaftar di system (m_city)
  611.         UPDATE ul_customer A
  612.         SET status = vFail,
  613.             message = message || 'City 1 must be filled, '
  614.         WHERE A.upload_header_id = pUploadHeaderId
  615.             AND TRIM(A.address_desc) <> vEmpty
  616.             AND TRIM(A.city) = vEmpty;
  617.            
  618.         UPDATE ul_customer A
  619.         SET status = vFail,
  620.             message = message || 'City is not registred on system, '
  621.         WHERE A.upload_header_id = pUploadHeaderId
  622.             AND TRIM(A.city) <> vEmpty
  623.             AND NOT EXISTS(
  624.                 SELECT 1 FROM m_city Z WHERE Z.city_code = A.city AND Z.tenant_id = vTenantId
  625.             );
  626.            
  627.         -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
  628.         UPDATE ul_customer A
  629.         SET status = vFail,
  630.             message = message || 'Country must be filled, '
  631.         WHERE A.upload_header_id = pUploadHeaderId
  632.             AND TRIM(A.address_desc) <> vEmpty
  633.             AND TRIM(A.country) = vEmpty;
  634.            
  635.         UPDATE ul_customer A
  636.         SET status = vFail,
  637.             message = message || 'Country is not registred on system, '
  638.         WHERE A.upload_header_id = pUploadHeaderId
  639.             AND TRIM(A.country) <> vEmpty
  640.             AND NOT EXISTS(
  641.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'COUNTRY' AND Z.code = A.country
  642.             );
  643.            
  644.         -- # Official addr wajib diisi dengan nilai Y/N
  645.         UPDATE ul_customer A
  646.         SET status = vFail,
  647.             message = message || 'Official addr can only be filled with Y or N, '
  648.         WHERE A.upload_header_id = pUploadHeaderId
  649.             AND TRIM(A.address_desc) <> vEmpty
  650.             AND A.official_addr NOT IN (vYes, vNo);
  651.            
  652.         -- # Shipping Addr Wajib diisi dengan nilai Y/N
  653.         UPDATE ul_customer A
  654.         SET status = vFail,
  655.             message = message || 'Shipping Addr can only be filled with Y or N, '
  656.         WHERE A.upload_header_id = pUploadHeaderId
  657.             AND TRIM(A.address_desc) <> vEmpty
  658.             AND A.shipping_addr NOT IN (vYes, vNo);
  659.            
  660.         -- # Billing Addr Wajib diisi dengan nilai Y/N
  661.         UPDATE ul_customer A
  662.         SET status = vFail,
  663.             message = message || 'Billing Addr can only be filled with Y or N, '
  664.         WHERE A.upload_header_id = pUploadHeaderId
  665.             AND TRIM(A.address_desc) <> vEmpty
  666.             AND A.billing_addr NOT IN (vYes, vNo);
  667.            
  668.         -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
  669.         UPDATE ul_customer A
  670.         SET status = vFail,
  671.             message = message || 'Longitude must be numeric, '
  672.         WHERE A.upload_header_id = pUploadHeaderId
  673.             AND TRIM(A.longitude) <> vEmpty
  674.             AND NOT is_numeric(A.longitude);
  675.            
  676.         -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
  677.         UPDATE ul_customer A
  678.         SET status = vFail,
  679.             message = message || 'Latitude must be numeric, '
  680.         WHERE A.upload_header_id = pUploadHeaderId
  681.             AND TRIM(A.latitude) <> vEmpty
  682.             AND NOT is_numeric(A.latitude);
  683.            
  684.         -- # Max character Phone 1 adalah 100, jika diisi
  685.         UPDATE ul_customer A
  686.         SET status = vFail,
  687.             message = message || 'Max phone 1 is 100 character, '
  688.         WHERE A.upload_header_id = pUploadHeaderId
  689.             AND TRIM(A.phone_1) <> vEmpty
  690.             AND length(A.phone_1) > 100;
  691.            
  692.         -- # Max character Phone 2 adalah 100, jika diisi
  693.         UPDATE ul_customer A
  694.         SET status = vFail,
  695.             message = message || 'Max phone 2 is 100 character, '
  696.         WHERE A.upload_header_id = pUploadHeaderId
  697.             AND TRIM(A.phone_2) <> vEmpty
  698.             AND length(A.phone_2) > 100;
  699.            
  700.         -- # Max character Fax 1 adalah 100, jika diisi
  701.         UPDATE ul_customer A
  702.         SET status = vFail,
  703.             message = message || 'Max fax 1 is 100 character, '
  704.         WHERE A.upload_header_id = pUploadHeaderId
  705.             AND TRIM(A.fax_1) <> vEmpty
  706.             AND length(A.fax_1) > 100;
  707.            
  708.         -- # Max character Fax 2 adalah 100, jika diisi
  709.         UPDATE ul_customer A
  710.         SET status = vFail,
  711.             message = message || 'Max fax 2 is 100 character, '
  712.         WHERE A.upload_header_id = pUploadHeaderId
  713.             AND TRIM(A.fax_2) <> vEmpty
  714.             AND length(A.fax_2) > 100;
  715.            
  716.         -- # Setiap kode customer baru harus memiliki minimal 1 partner baru
  717.         UPDATE ul_customer A
  718.         SET status = vFail,
  719.             message = message || 'Customer must have Partner CP at least 1, '
  720.         WHERE A.upload_header_id = pUploadHeaderId
  721.         AND TRIM(A.kode_pelanggan) <> vEmpty
  722.         AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
  723.         AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_cp Z
  724.                             WHERE Z.upload_header_id = pUploadHeaderId
  725.                             AND Z.kode_pelanggan = A.kode_pelanggan);
  726.                            
  727.         -- # Partner CP harus unique by CP Name untuk setiap kode pelanggan
  728.         WITH data_partner_cp AS (
  729.             SELECT A.kode_pelanggan, A.cp_name
  730.             FROM tt_ul_customer_partner_cp A
  731.             WHERE A.upload_header_id = pUploadHeaderId
  732.             GROUP BY A.kode_pelanggan, A.cp_name
  733.             HAVING COUNT(1) > 1
  734.         )
  735.         UPDATE ul_customer A
  736.         SET status = vFail,
  737.             message = message || 'Duplicate CP Name, '
  738.         WHERE A.upload_header_id = pUploadHeaderId
  739.         AND EXISTS (
  740.             SELECT 1 FROM data_partner_cp Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.cp_name = Z.cp_name
  741.         );
  742.        
  743.         -- # CP Name wajib diisi apabila kolom partner cp lainya ada yang diisi & Max character Address Desc adalah 100
  744.         UPDATE ul_customer A
  745.         SET status = vFail,
  746.             message = message || 'CP name must be filled, '
  747.         WHERE A.upload_header_id = pUploadHeaderId
  748.             AND TRIM(A.kode_pelanggan) <> vEmpty
  749.             AND TRIM(A.cp_name) = vEmpty
  750.             AND TRIM(A.cp_job
  751.                     ||A.cp_department
  752.                     ||A.cp_job_level
  753.                     ||A.cp_email
  754.                     ||A.cp_phone_1
  755.                     ||A.cp_phone_2
  756.                     ||A.cp_mobile_1
  757.                     ||A.cp_mobile_2) <> vEmpty;
  758.            
  759.         UPDATE ul_customer A
  760.         SET status = vFail,
  761.             message = message || 'Max CP name is 100 character, '
  762.         WHERE A.upload_header_id = pUploadHeaderId
  763.             AND TRIM(A.cp_name) <> vEmpty
  764.             AND length(A.cp_name) > 100;
  765.            
  766.         -- # CP Job wajib diisi dan max character adalah 100
  767.         UPDATE ul_customer A
  768.         SET status = vFail,
  769.             message = message || 'CP job must be filled, '
  770.         WHERE A.upload_header_id = pUploadHeaderId
  771.             AND TRIM(A.cp_name) <> vEmpty
  772.             AND TRIM(A.cp_job) = vEmpty;
  773.            
  774.         UPDATE ul_customer A
  775.         SET status = vFail,
  776.             message = message || 'Max CP job is 100 character, '
  777.         WHERE A.upload_header_id = pUploadHeaderId
  778.             AND TRIM(A.cp_job) <> vEmpty
  779.             AND length(A.cp_job) > 100;
  780.            
  781.         -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
  782.         UPDATE ul_customer A
  783.         SET status = vFail,
  784.             message = message || 'CP Department must be filled, '
  785.         WHERE A.upload_header_id = pUploadHeaderId
  786.             AND TRIM(A.cp_name) <> vEmpty
  787.             AND TRIM(A.cp_department) = vEmpty;
  788.            
  789.         UPDATE ul_customer A
  790.         SET status = vFail,
  791.             message = message || 'CP department is not registred on system, '
  792.         WHERE A.upload_header_id = pUploadHeaderId
  793.             AND TRIM(A.cp_department) <> vEmpty
  794.             AND NOT EXISTS(
  795.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'DEPARTMENT' AND Z.code = A.cp_department
  796.             );
  797.            
  798.         -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
  799.         UPDATE ul_customer A
  800.         SET status = vFail,
  801.             message = message || 'CP job level must be filled, '
  802.         WHERE A.upload_header_id = pUploadHeaderId
  803.             AND TRIM(A.cp_name) <> vEmpty
  804.             AND TRIM(A.cp_job_level) = vEmpty;
  805.            
  806.         UPDATE ul_customer A
  807.         SET status = vFail,
  808.             message = message || 'CP job level is not registred on system, '
  809.         WHERE A.upload_header_id = pUploadHeaderId
  810.             AND TRIM(A.cp_job_level) <> vEmpty
  811.             AND NOT EXISTS(
  812.                 SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'JOBLEVEL' AND Z.code = A.cp_job_level
  813.             );
  814.            
  815.         -- # Max character CP Email adalah 100 jika diisi
  816.         UPDATE ul_customer A
  817.         SET status = vFail,
  818.             message = message || 'Max CP Email is 100 character, '
  819.         WHERE A.upload_header_id = pUploadHeaderId
  820.             AND TRIM(A.cp_email) <> vEmpty
  821.             AND length(A.cp_email) > 100;
  822.            
  823.         -- # Max character CP Phone 1 adalah 100 jika diisi
  824.         UPDATE ul_customer A
  825.         SET status = vFail,
  826.             message = message || 'Max CP Phone 1 is 100 character, '
  827.         WHERE A.upload_header_id = pUploadHeaderId
  828.             AND TRIM(A.cp_phone_1) <> vEmpty
  829.             AND length(A.cp_phone_1) > 100;
  830.            
  831.         -- # Max character CP Phone 2 adalah 100 jika diisi
  832.         UPDATE ul_customer A
  833.         SET status = vFail,
  834.             message = message || 'Max CP Phone 2 is 100 character, '
  835.         WHERE A.upload_header_id = pUploadHeaderId
  836.             AND TRIM(A.cp_phone_2) <> vEmpty
  837.             AND length(A.cp_phone_2) > 100;
  838.            
  839.         -- # Max character CP Mobile 1 adalah 100 jika diisi
  840.         UPDATE ul_customer A
  841.         SET status = vFail,
  842.             message = message || 'Max CP Mobile 1 is 100 character, '
  843.         WHERE A.upload_header_id = pUploadHeaderId
  844.             AND TRIM(A.cp_mobile_1) <> vEmpty
  845.             AND length(A.cp_mobile_1) > 100;
  846.            
  847.         -- # Max character CP Mobile 2 adalah 100 jika diisi
  848.         UPDATE ul_customer A
  849.         SET status = vFail,
  850.             message = message || 'Max CP Mobile 2 is 100 character, '
  851.         WHERE A.upload_header_id = pUploadHeaderId
  852.             AND TRIM(A.cp_mobile_2) <> vEmpty
  853.             AND length(A.cp_mobile_2) > 100;
  854.            
  855.         -- # NPWP harus diisi jika Status NPWP = Y
  856.         UPDATE ul_customer A
  857.         SET status = vFail,
  858.             message = message || 'NPWP must be filled when Status NPWP is Y, '
  859.         WHERE A.upload_header_id = pUploadHeaderId
  860.             AND TRIM(A.npwp) = vEmpty
  861.             AND A.status_pkp = vYes;
  862.            
  863.         -- # Jika NPWP diisi maka max characternya adalah 50
  864.         UPDATE ul_customer A
  865.         SET status = vFail,
  866.             message = message || 'Max NPWP is 50 character, '
  867.         WHERE A.upload_header_id = pUploadHeaderId
  868.             AND TRIM(A.npwp) <> vEmpty
  869.             AND length(A.npwp) > 50;
  870.            
  871.         -- # Status PKP wajib diisi dan nilainya harus Y/N
  872.         UPDATE ul_customer A
  873.         SET status = vFail,
  874.             message = message || 'Status PKP can only be filled with Y or N, '
  875.         WHERE A.upload_header_id = pUploadHeaderId
  876.             AND A.status_pkp NOT IN (vYes, vNo);
  877.            
  878.         -- # Active harus diisi dengan nilai Y/N
  879.         UPDATE ul_customer A
  880.         SET status = vFail,
  881.             message = message || 'Active can only be filled with Y or N, '
  882.         WHERE A.upload_header_id = pUploadHeaderId
  883.             AND A.active NOT IN (vYes, vNo);
  884.            
  885.         WITH invalid_data_customer AS (
  886.             SELECT A.kode_pelanggan
  887.             FROM tt_ul_customer_unique A
  888.             WHERE A.upload_header_id = pUploadHeaderId
  889.             GROUP BY A.kode_pelanggan
  890.             HAVING COUNT(1) > 1
  891.         )
  892.         -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
  893.         UPDATE ul_customer A
  894.         SET status = vFail,
  895.             message = message || 'Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active", '
  896.         WHERE A.upload_header_id = pUploadHeaderId
  897.             AND EXISTS (
  898.                 SELECT 1 FROM invalid_data_customer Z WHERE A.kode_pelanggan = Z.kode_pelanggan
  899.             );
  900.            
  901.         -- Cek apakah ada item CSV yang statusnya fail
  902.         IF EXISTS ( SELECT 1 FROM ul_customer B
  903.                     WHERE B.upload_header_id = pUploadHeaderId
  904.                     AND B.status = vFail ) THEN
  905.                    
  906.             -- Sesuaikan message, agar message paling belakang tidak ada koma
  907.             UPDATE ul_customer A
  908.             SET message = substr(A.message, 1, length(A.message)-2)
  909.             WHERE A.upload_header_id = pUploadHeaderId
  910.                 AND A.status = vFail
  911.                 AND TRIM(A.message) != vEmpty;
  912.                
  913.             -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
  914.             -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
  915.             -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
  916.             UPDATE ul_customer A
  917.             SET status = vFail,
  918.                 update_status = vActionError
  919.             WHERE upload_header_id = pUploadHeaderId;
  920.            
  921.             -- Hitung jumlah data yang FAIL
  922.             SELECT COUNT(1) INTO vCountFail
  923.             FROM ul_customer
  924.             WHERE upload_header_id = pUploadHeaderId
  925.                 AND status = vFail;
  926.         ELSE
  927.                
  928.             -- RESET table temp
  929.             DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
  930.            
  931.             -- GET id OU company
  932.             SELECT A.ou_id INTO vOuCompanyId
  933.             FROM t_ou A
  934.             INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
  935.             WHERE A.tenant_id = vTenantId
  936.             AND B.flg_bu = vYes
  937.             AND B.flg_sub_bu = vNo
  938.             AND B.flg_branch = vNo
  939.             AND B.tenant_id = vTenantId;
  940.        
  941.             -- Update status non FAIL to OK
  942.             UPDATE ul_customer
  943.             SET status = vOk
  944.             WHERE upload_header_id = pUploadHeaderId;
  945.            
  946.             -- UPDATE OK data untuk customer code terdaftar
  947.             WITH update_data_customer AS (
  948.                 UPDATE m_partner A SET
  949.                     partner_name=B.nama_pelanggan,
  950.                     ctgr_partner_id=C.ctgr_partner_id,
  951.                     partner_ext_name=B.nama_external,
  952.                     rank=B.rank,
  953.                     line_of_business=B.line_of_business,
  954.                     industry_type=B.industry_type,
  955.                     npwp_status=B.npwp_status,
  956.                     price_level=B.price_level,
  957.                     update_datetime=vDatetime,
  958.                     update_user_id=vUserId,
  959.                     version=A.version+1,
  960.                     active=B.active,
  961.                     active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
  962.                                         THEN vDatetime
  963.                                         ELSE A.active_datetime
  964.                                     END,
  965.                     non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
  966.                                             THEN vDatetime
  967.                                             ELSE A.non_active_datetime
  968.                                         END
  969.                 FROM tt_ul_customer_unique B
  970.                 INNER JOIN m_ctgr_partner C ON B.kode_kategori = C.ctgr_partner_code AND c.tenant_id = vTenantId
  971.                 WHERE B.upload_header_id = pUploadHeaderId
  972.                 AND A.partner_code = B.kode_pelanggan
  973.                 AND A.tenant_id = vTenantId
  974.                 RETURNING A.*
  975.             )
  976.             INSERT INTO tt_ul_partner(
  977.                 upload_header_id, action_type, partner_id, tenant_id, partner_code,
  978.                 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
  979.                 flg_holding, rank, create_datetime, create_user_id, update_datetime,
  980.                 update_user_id, version, active, active_datetime, non_active_datetime,
  981.                 line_of_business, price_level, industry_type, npwp_status)
  982.             SELECT pUploadHeaderId, vActionUpdate, partner_id, tenant_id, partner_code,
  983.                 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
  984.                 flg_holding, rank, create_datetime, create_user_id, update_datetime,
  985.                 update_user_id, version, active,  active_datetime, non_active_datetime,
  986.                 line_of_business, price_level, industry_type, npwp_status
  987.             FROM update_data_customer;
  988.            
  989.             -- INSERT OK data untuk customer code baru
  990.             WITH insert_data_customer AS (
  991.                 INSERT INTO m_partner(
  992.                         tenant_id, partner_code, partner_name, ctgr_partner_id,
  993.                         partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
  994.                         create_user_id, update_datetime, update_user_id, version, active,
  995.                         active_datetime, non_active_datetime, line_of_business, price_level,
  996.                         industry_type, npwp_status)
  997.                 SELECT vTenantId, A.kode_pelanggan, A.nama_pelanggan, B.ctgr_partner_id,
  998.                         A.nama_external, vEmptyId, vEmptyId, vNo, A.rank, vDatetime,
  999.                         vUserId, vDatetime, vUserId, 0, A.active,
  1000.                         CASE WHEN A.active = vYes
  1001.                             THEN vDatetime
  1002.                             ELSE vSpaceValue
  1003.                         END AS active_datetime,
  1004.                         CASE WHEN A.active = vNo
  1005.                             THEN vDatetime
  1006.                             ELSE vSpaceValue
  1007.                         END AS non_active_datetime,
  1008.                         vSpaceValue, vSpaceValue,
  1009.                         vSpaceValue, vSpaceValue
  1010.                 FROM tt_ul_customer_unique A
  1011.                 INNER JOIN m_ctgr_partner B ON A.kode_kategori = B.ctgr_partner_code AND B.tenant_id = vTenantId
  1012.                 WHERE A.upload_header_id = pUploadHeaderId
  1013.                 AND NOT EXISTS (
  1014.                     SELECT 1 FROM m_partner Z
  1015.                     WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
  1016.                 )
  1017.                 RETURNING *
  1018.             )
  1019.             INSERT INTO tt_ul_partner(
  1020.                 upload_header_id, action_type, partner_id, tenant_id, partner_code,
  1021.                 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
  1022.                 flg_holding, rank, create_datetime, create_user_id, update_datetime,
  1023.                 update_user_id, version, active, active_datetime, non_active_datetime,
  1024.                 line_of_business, price_level, industry_type, npwp_status)
  1025.             SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
  1026.                 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
  1027.                 flg_holding, rank, create_datetime, create_user_id, update_datetime,
  1028.                 update_user_id, version, active,  active_datetime, non_active_datetime,
  1029.                 line_of_business, price_level, industry_type, npwp_status
  1030.             FROM insert_data_customer;
  1031.            
  1032.             -- ACTION FOR UPPDATE
  1033.             IF  EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
  1034.                
  1035.                 -- Ubah update_status menjadi U jika data digunakan untuk Update
  1036.                 UPDATE ul_customer A SET
  1037.                     update_status = vActionUpdate
  1038.                 WHERE A.upload_header_id = pUploadHeaderId
  1039.                     AND EXISTS (
  1040.                         SELECT 1 FROM tt_ul_partner B
  1041.                         WHERE A.kode_pelanggan = B.partner_code
  1042.                         AND B.tenant_id = vTenantId
  1043.                         AND B.action_type = vActionUpdate
  1044.                     );
  1045.                
  1046.                 -- UPDATE data partner address
  1047.                 UPDATE m_partner_address A SET
  1048.                     address1=C.address_1,
  1049.                     address2=C.address_2,
  1050.                     address3=C.address_3,
  1051.                     city=C.city,
  1052.                     zip_code=C.zip_code,
  1053.                     state_or_province=D.province,
  1054.                     country=C.country,
  1055.                     phone1=C.phone_1,
  1056.                     phone2=C.phone_2,
  1057.                     fax1=C.fax_1,
  1058.                     fax2=C.fax_2,
  1059.                     flg_default=CASE WHEN C.official_addr = vYes THEN vYes ELSE vNo END,
  1060.                     update_datetime=vDatetime,
  1061.                     update_user_id=vUserId,
  1062.                     version=A.version+1,
  1063.                     flg_ship=C.shipping_addr,
  1064.                     flg_bill=C.billing_addr,
  1065.                     flg_official=C.official_addr,
  1066.                     longitude=C.longitude::numeric,
  1067.                     latitude=C.latitude::numeric
  1068.                 FROM tt_ul_partner B
  1069.                 INNER JOIN tt_ul_customer_partner_address C ON B.partner_code = C.kode_pelanggan
  1070.                 INNER JOIN m_city D ON C.city = D.city_code AND D.tenant_id = vTenantId
  1071.                 WHERE B.upload_header_id = pUploadHeaderId
  1072.                 AND B.upload_header_id = C.upload_header_id
  1073.                 AND A.partner_id = B.partner_id
  1074.                 AND A.address_desc = C.address_desc
  1075.                 AND A.tenant_id = B.tenant_id
  1076.                 AND B.action_type = vActionUpdate;
  1077.                
  1078.                 -- INSERT data partner new address
  1079.                 INSERT INTO m_partner_address(
  1080.                     tenant_id, partner_id, address_desc, address1,
  1081.                     address2, address3, city, zip_code, state_or_province, country,
  1082.                     phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
  1083.                     update_datetime, update_user_id, version, active, active_datetime,
  1084.                     non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
  1085.                     flg_others, longitude, latitude)
  1086.                 SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
  1087.                     B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
  1088.                     B.phone_1, B.phone_2, B.fax_1, B.fax_2, CASE WHEN B.official_addr = vYes THEN vYes ELSE vNo END, vDatetime, vUserId,
  1089.                     vDatetime, vUserId, 0, vYes, vDatetime,
  1090.                     vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
  1091.                     vNo, B.longitude::numeric, B.latitude::numeric
  1092.                 FROM tt_ul_partner A
  1093.                 INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
  1094.                 INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
  1095.                 WHERE A.upload_header_id = pUploadHeaderId
  1096.                 AND A.upload_header_id = B.upload_header_id
  1097.                 AND A.action_type = vActionUpdate
  1098.                 AND NOT EXISTS (
  1099.                     SELECT 1 FROM m_partner_address Z
  1100.                     WHERE B.address_desc = Z.address_desc
  1101.                     AND A.partner_id = Z.partner_id
  1102.                     AND Z.tenant_id = vTenantId
  1103.                 );
  1104.                
  1105.                 -- UPDATE data partner CP
  1106.                 UPDATE m_partner_cp A SET
  1107.                     cp_job=C.cp_job,
  1108.                     phone1=C.cp_phone_1,
  1109.                     phone2=C.cp_phone_2,
  1110.                     email=C.cp_email,
  1111.                     update_datetime=vDatetime,
  1112.                     update_user_id=vUserId,
  1113.                     version=A.version+1,
  1114.                     department=C.cp_department,
  1115.                     job_level=C.cp_job_level,
  1116.                     mobile_phone1=C.cp_mobile_1,
  1117.                     mobile_phone2=C.cp_mobile_2
  1118.                 FROM tt_ul_partner B
  1119.                 INNER JOIN tt_ul_customer_partner_cp C ON B.partner_code = C.kode_pelanggan
  1120.                 WHERE B.upload_header_id = pUploadHeaderId
  1121.                 AND B.upload_header_id = C.upload_header_id
  1122.                 AND A.partner_id = B.partner_id
  1123.                 AND A.cp_name = C.cp_name
  1124.                 AND B.action_type = vActionUpdate;
  1125.                
  1126.                 -- INSERT data partner cp
  1127.                 INSERT INTO m_partner_cp(
  1128.                     tenant_id, partner_id, cp_name, cp_job, address1,
  1129.                     address2, address3, city, zip_code, country, phone1, phone2,
  1130.                     fax1, fax2, email, create_datetime, create_user_id, update_datetime,
  1131.                     update_user_id, version, active, active_datetime, non_active_datetime,
  1132.                     department, job_level, phone_ext1, phone_ext2, mobile_phone1,
  1133.                     mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
  1134.                     flg_email_notif_ar)
  1135.                 SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
  1136.                     vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
  1137.                     vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
  1138.                     vUserId, 0, vYes, vDatetime, vSpaceValue,
  1139.                     B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
  1140.                     B.cp_mobile_2, vNo, vNo, vNo,
  1141.                     vNo
  1142.                 FROM tt_ul_partner A
  1143.                 INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
  1144.                 WHERE A.upload_header_id = pUploadHeaderId
  1145.                 AND A.upload_header_id = B.upload_header_id
  1146.                 AND A.action_type = vActionUpdate
  1147.                 AND NOT EXISTS (
  1148.                     SELECT 1 FROM m_partner_cp Z
  1149.                     WHERE B.cp_name = Z.cp_name
  1150.                     AND A.partner_id = Z.partner_id
  1151.                     AND Z.tenant_id = vTenantId
  1152.                 );
  1153.    
  1154.                 -- UPDATE data partner type
  1155.                 UPDATE m_partner_type A SET
  1156.                     due_date=C.top_in_days::integer,
  1157.                     amount_limit=C.credit_limit::numeric,
  1158.                     update_datetime=vDatetime,
  1159.                     update_user_id=vUserId,
  1160.                     version=A.version+1
  1161.                 FROM tt_ul_partner B
  1162.                 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
  1163.                 WHERE B.upload_header_id = pUploadHeaderId
  1164.                 AND B.upload_header_id = C.upload_header_id
  1165.                 AND A.partner_id = B.partner_id
  1166.                 AND B.action_type = vActionUpdate;
  1167.                
  1168.                 -- UPDATE data customer ext
  1169.                 UPDATE m_customer_ext A SET
  1170.                     regular_discount_percentage=C.disc_reguler_pct::numeric,
  1171.                     update_datetime=vDatetime,
  1172.                     update_user_id=vUserId,
  1173.                     version=A.version+1
  1174.                 FROM tt_ul_partner B
  1175.                 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
  1176.                 WHERE B.upload_header_id = pUploadHeaderId
  1177.                 AND B.upload_header_id = C.upload_header_id
  1178.                 AND A.partner_id = B.partner_id
  1179.                 AND B.action_type = vActionUpdate;
  1180.    
  1181.                 -- UPDATE data customer ext for dlg
  1182.                 UPDATE m_customer_ext_for_dlg A SET
  1183.                     top_external=C.top_in_days::integer,
  1184.                     update_datetime=vDatetime,
  1185.                     update_user_id=vUserId,
  1186.                     version=A.version+1
  1187.                 FROM tt_ul_partner B
  1188.                 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
  1189.                 WHERE B.upload_header_id = pUploadHeaderId
  1190.                 AND B.upload_header_id = C.upload_header_id
  1191.                 AND A.partner_id = B.partner_id
  1192.                 AND B.action_type = vActionUpdate;
  1193.                
  1194.                 -- DELETE existing data partner npwp
  1195.                 DELETE FROM m_partner_npwp A
  1196.                 WHERE EXISTS (
  1197.                     SELECT 1 FROM tt_ul_partner Z WHERE A.partner_id = Z.partner_id
  1198.                 );
  1199.                
  1200.                 -- INSERT data partner npwp
  1201.                 INSERT INTO m_partner_npwp(
  1202.                     tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
  1203.                     address1, address2, address3, city, zip_code, country, phone1,
  1204.                     phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
  1205.                     update_user_id, version, active, active_datetime, non_active_datetime,
  1206.                     flg_pkp, pkp_date)
  1207.                 SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
  1208.                     vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
  1209.                     vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
  1210.                     vUserId, 0, vYes, vDatetime, vSpaceValue,
  1211.                     B.status_pkp, to_char(current_date, 'YYYYMMDD')
  1212.                 FROM tt_ul_partner A
  1213.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1214.                 WHERE A.upload_header_id = pUploadHeaderId
  1215.                 AND A.upload_header_id = B.upload_header_id
  1216.                 AND A.action_type = vActionUpdate;
  1217.                
  1218.                 -- UPDATE data cust payment
  1219.                 UPDATE m_cust_payment A SET
  1220.                     due_payment_days=C.top_in_days::integer,
  1221.                     update_datetime=vDatetime,
  1222.                     update_user_id=vUserId,
  1223.                     version=A.version+1
  1224.                 FROM tt_ul_partner B
  1225.                 INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
  1226.                 WHERE B.upload_header_id = pUploadHeaderId
  1227.                 AND B.upload_header_id = C.upload_header_id
  1228.                 AND A.partner_id = B.partner_id
  1229.                 AND B.action_type = vActionUpdate;
  1230.                    
  1231.             END IF;
  1232.            
  1233.             -- ACTION FOR INSERT
  1234.             IF  EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
  1235.            
  1236.                 -- GET curr code from sysconfig
  1237.                 SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
  1238.            
  1239.                 -- INSERT data partner address
  1240.                 INSERT INTO m_partner_address(
  1241.                     tenant_id, partner_id, address_desc, address1,
  1242.                     address2, address3, city, zip_code, state_or_province, country,
  1243.                     phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
  1244.                     update_datetime, update_user_id, version, active, active_datetime,
  1245.                     non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
  1246.                     flg_others, longitude, latitude)
  1247.                 SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
  1248.                     B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
  1249.                     B.phone_1, B.phone_2, B.fax_1, B.fax_2, CASE WHEN B.official_addr = vYes THEN vYes ELSE vNo END, vDatetime, vUserId,
  1250.                     vDatetime, vUserId, 0, vYes, vDatetime,
  1251.                     vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
  1252.                     vNo, B.longitude::numeric, B.latitude::numeric
  1253.                 FROM tt_ul_partner A
  1254.                 INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
  1255.                 INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
  1256.                 WHERE A.upload_header_id = pUploadHeaderId
  1257.                 AND A.upload_header_id = B.upload_header_id
  1258.                 AND A.action_type = vActionInsert;
  1259.                
  1260.                 -- INSERT data partner cp
  1261.                 INSERT INTO m_partner_cp(
  1262.                     tenant_id, partner_id, cp_name, cp_job, address1,
  1263.                     address2, address3, city, zip_code, country, phone1, phone2,
  1264.                     fax1, fax2, email, create_datetime, create_user_id, update_datetime,
  1265.                     update_user_id, version, active, active_datetime, non_active_datetime,
  1266.                     department, job_level, phone_ext1, phone_ext2, mobile_phone1,
  1267.                     mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
  1268.                     flg_email_notif_ar)
  1269.                 SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
  1270.                     vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
  1271.                     vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
  1272.                     vUserId, 0, vYes, vDatetime, vSpaceValue,
  1273.                     B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
  1274.                     B.cp_mobile_2, vNo, vNo, vNo,
  1275.                     vNo
  1276.                 FROM tt_ul_partner A
  1277.                 INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
  1278.                 WHERE A.upload_header_id = pUploadHeaderId
  1279.                 AND A.upload_header_id = B.upload_header_id
  1280.                 AND A.action_type = vActionInsert;
  1281.                
  1282.                 -- INSERT data customer ext
  1283.                 INSERT INTO m_customer_ext(
  1284.                     partner_id, tenant_id, regular_discount_percentage, create_datetime,
  1285.                     create_user_id, update_datetime, update_user_id, version, active,
  1286.                     active_datetime, non_active_datetime)
  1287.                 SELECT A.partner_id, vTenantId, B.disc_reguler_pct::numeric, vDatetime,
  1288.                     vUserId, vDatetime, vUserId, 0, vYes,
  1289.                     vDatetime, vSpaceValue
  1290.                 FROM tt_ul_partner A
  1291.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1292.                 WHERE A.upload_header_id = pUploadHeaderId
  1293.                 AND A.upload_header_id = B.upload_header_id
  1294.                 AND A.action_type = vActionInsert;
  1295.                
  1296.                 -- INSERT data customer ext for dlg
  1297.                 INSERT INTO m_customer_ext_for_dlg(
  1298.                     partner_id, tenant_id, flg_show_inv_tax, create_datetime, create_user_id,
  1299.                     update_datetime, update_user_id, version, top_external)
  1300.                 SELECT A.partner_id, vTenantId, B.status_pkp, vDatetime, vUserId,
  1301.                     vDatetime, vUserId, 0, B.top_in_days::integer
  1302.                 FROM tt_ul_partner A
  1303.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1304.                 WHERE A.upload_header_id = pUploadHeaderId
  1305.                 AND A.upload_header_id = B.upload_header_id
  1306.                 AND A.action_type = vActionInsert;
  1307.                
  1308.                 -- INSERT data partner npwp
  1309.                 INSERT INTO m_partner_npwp(
  1310.                     tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
  1311.                     address1, address2, address3, city, zip_code, country, phone1,
  1312.                     phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
  1313.                     update_user_id, version, active, active_datetime, non_active_datetime,
  1314.                     flg_pkp, pkp_date)
  1315.                 SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
  1316.                     vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
  1317.                     vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
  1318.                     vUserId, 0, vYes, vDatetime, vSpaceValue,
  1319.                     B.status_pkp, to_char(current_date, 'YYYYMMDD')
  1320.                 FROM tt_ul_partner A
  1321.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1322.                 WHERE A.upload_header_id = pUploadHeaderId
  1323.                 AND A.upload_header_id = B.upload_header_id
  1324.                 AND A.action_type = vActionInsert;
  1325.                    
  1326.                 -- INSERT data partner ou
  1327.                 INSERT INTO m_partner_ou(
  1328.                         partner_id, ou_id, version, create_datetime, create_user_id,
  1329.                         update_datetime, update_user_id)
  1330.                 SELECT A.partner_id, vOuCompanyId, 0, vDatetime, vUserId,
  1331.                         vDatetime, vUserId
  1332.                 FROM tt_ul_partner A
  1333.                 WHERE A.upload_header_id = pUploadHeaderId
  1334.                 AND A.action_type = vActionInsert;
  1335.                
  1336.                 -- INSERT data partner type
  1337.                 INSERT INTO m_partner_type(
  1338.                     tenant_id, partner_id, group_partner, type_partner_id,
  1339.                     due_date, curr_code, amount_limit, flg_commision, create_datetime,
  1340.                     create_user_id, update_datetime, update_user_id, version, active,
  1341.                     active_datetime, non_active_datetime)
  1342.                 SELECT vTenantId, A.partner_id, vGroupCustomer, vTypePartnerId,
  1343.                     B.top_in_days::integer, vCurrCode, B.credit_limit::numeric, vNo, vDatetime,
  1344.                     vUserId, vDatetime, vUserId, 0, vYes,
  1345.                     vDatetime, vSpaceValue
  1346.                 FROM tt_ul_partner A
  1347.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1348.                 WHERE A.upload_header_id = pUploadHeaderId
  1349.                 AND A.upload_header_id = B.upload_header_id
  1350.                 AND A.action_type = vActionInsert;
  1351.                
  1352.                 -- INSERT data partner rel
  1353.                 INSERT INTO m_partner_rel(
  1354.                     tenant_id, partner_id, relation_id, flg_bill,
  1355.                     flg_shipp, create_datetime, create_user_id, update_datetime,
  1356.                     update_user_id, version, active, active_datetime, non_active_datetime)
  1357.                 SELECT vTenantId, A.partner_id, A.partner_id, vYes,
  1358.                     vYes, vDatetime, vUserId, vDatetime,
  1359.                     vUserId, 0, vYes, vDatetime, vSpaceValue
  1360.                 FROM tt_ul_partner A
  1361.                 WHERE A.upload_header_id = pUploadHeaderId
  1362.                 AND A.action_type = vActionInsert;
  1363.                    
  1364.                 -- INSERT data cust payment
  1365.                 INSERT INTO m_cust_payment(
  1366.                     tenant_id, partner_id, flg_invoice, due_payment_days,
  1367.                     flg_payment_mode, payment_day, payment_date, partner_bank_id,
  1368.                     create_datetime, create_user_id, update_datetime, update_user_id,
  1369.                     version, active, active_datetime, non_active_datetime)
  1370.                 SELECT vTenantId, A.partner_id, vNo, B.top_in_days::integer,
  1371.                     vNo, vEmpty, vEmpty, vEmptyId,
  1372.                     vDatetime, vUserId, vDatetime, vUserId,
  1373.                     0, vYes, vDatetime, vSpaceValue
  1374.                 FROM tt_ul_partner A
  1375.                 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
  1376.                 WHERE A.upload_header_id = pUploadHeaderId
  1377.                 AND A.upload_header_id = B.upload_header_id
  1378.                 AND A.action_type = vActionInsert;
  1379.                    
  1380.             END IF;
  1381.            
  1382.             /**
  1383.              * Membuatkan data policy untuk OU yang belum ada pada data policy OU
  1384.              */
  1385.             WITH insert_data_policy AS (
  1386.                 INSERT INTO t_policy(
  1387.                     tenant_id, policy_code, policy_name, create_datetime,
  1388.                     create_user_id, update_datetime, update_user_id, version, active,
  1389.                     active_datetime, non_active_datetime)
  1390.                 SELECT vTenantId, f_get_ou_code(vOuCompanyId)||'_'||vDatetime, f_get_ou_name(vOuCompanyId), vDatetime,
  1391.                     vUserId, vDatetime, vUserId, 0, vYes,
  1392.                     vDatetime, vSpaceValue
  1393.                 WHERE NOT EXISTS (
  1394.                     SELECT 1 FROM t_policy_ou Z
  1395.                     WHERE Z.ou_id = vOuCompanyId
  1396.                 )
  1397.                    
  1398.                 RETURNING *
  1399.             )
  1400.             INSERT INTO t_policy_ou(
  1401.                 policy_id, ou_id, create_datetime, create_user_id,
  1402.                 update_datetime, update_user_id, version, active, active_datetime,
  1403.                 non_active_datetime)
  1404.             SELECT A.policy_id, vOuCompanyId, vDatetime, vUserId,
  1405.                 vDatetime, vUserId, 0, vYes, vDatetime,
  1406.                 vSpaceValue
  1407.             FROM insert_data_policy A;
  1408.                
  1409.             /**
  1410.              * Membuat / Update data user
  1411.              */
  1412.            
  1413.             -- Update default role data user jika data user sudah ada
  1414.             UPDATE t_user A SET
  1415.                 role_default_id=vDefaultRoleId,
  1416.                 version=A.version+1,
  1417.                 update_datetime=vDatetime,
  1418.                 update_user_id=vUserId
  1419.             FROM tt_ul_partner B
  1420.             WHERE B.upload_header_id = pUploadHeaderId
  1421.             AND A.username = B.partner_code
  1422.             AND A.role_default_id <> vDefaultRoleId;
  1423.                
  1424.             -- Buat data user role untuk user existing
  1425.             INSERT INTO t_user_role(
  1426.                         user_id, role_id, policy_id, create_datetime, create_user_id,
  1427.                         update_datetime, update_user_id, version, active, active_datetime,
  1428.                         non_active_datetime)
  1429.             SELECT A.user_id, vDefaultRoleId, A.policy_default_id, vDatetime, vUserId,
  1430.                         vDatetime, vUserId, 0, vYes, vDatetime,
  1431.                         vSpaceValue
  1432.             FROM t_user A
  1433.             WHERE EXISTS (
  1434.                 SELECT 1 FROM tt_ul_partner Z WHERE Z.upload_header_id = pUploadHeaderId AND A.username = Z.partner_code
  1435.             )
  1436.             AND NOT EXISTS (
  1437.                 SELECT 1 FROM t_user_role Z
  1438.                 WHERE Z.role_id = vDefaultRoleId
  1439.                 AND A.user_id = Z.user_id
  1440.             );
  1441.            
  1442.             -- Buat data user jika belum ada data user
  1443.             WITH insert_data_user AS (
  1444.                 INSERT INTO t_user(
  1445.                     tenant_id, username, email, fullname, password, phone,
  1446.                     role_default_id, private_key, create_datetime, create_user_id,
  1447.                     update_datetime, update_user_id, version, active, active_datetime,
  1448.                     non_active_datetime, ou_default_id, policy_default_id)
  1449.                 SELECT vTenantId, A.partner_code, vEmpty, CASE WHEN trim(A.partner_ext_name) <> vEmpty THEN A.partner_ext_name ELSE substr(A.partner_name,1,255) END, A.partner_code||'_'||vDatetime, vEmpty,
  1450.                     vDefaultRoleId, md5(A.partner_code||vDatetime), vDatetime, vUserId,
  1451.                     vDatetime, vUserId, 0, vYes, vDatetime,
  1452.                     vSpaceValue, vOuCompanyId, f_get_policy_ou_for_upload(vOuCompanyId)
  1453.                 FROM tt_ul_partner A
  1454.                 WHERE A.upload_header_id = pUploadHeaderId
  1455.                 AND NOT EXISTS (
  1456.                     SELECT 1 FROM t_user Z WHERE Z.username = A.partner_code
  1457.                 )
  1458.                 RETURNING *
  1459.             ), insert_data_user_prop AS (
  1460.                 -- Buat data user props
  1461.                 INSERT INTO t_user_props(
  1462.                             prop_key, user_id, prop_val, create_datetime, create_user_id,
  1463.                             update_datetime, update_user_id, version)
  1464.                 SELECT 'count.login', A.user_id, 0::character varying, vDatetime, vUserId,
  1465.                             vDatetime, vUserId, 0
  1466.                 FROM insert_data_user A
  1467.                
  1468.                 UNION ALL
  1469.                
  1470.                 SELECT 'last.login', A.user_id, ' ', vDatetime, vUserId,
  1471.                             vDatetime, vUserId, 0
  1472.                 FROM insert_data_user A
  1473.             )
  1474.             -- Buat data user role
  1475.             INSERT INTO t_user_role(
  1476.                         user_id, role_id, policy_id, create_datetime, create_user_id,
  1477.                         update_datetime, update_user_id, version, active, active_datetime,
  1478.                         non_active_datetime)
  1479.             SELECT A.user_id, A.role_default_id, A.policy_default_id, vDatetime, vUserId,
  1480.                         vDatetime, vUserId, 0, vYes, vDatetime,
  1481.                         vSpaceValue
  1482.             FROM insert_data_user A;
  1483.                
  1484.             -- RESET table temp
  1485.             DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
  1486.                
  1487.         END IF;
  1488.        
  1489.         -- RESET TEMP TABLE
  1490.         DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
  1491.         DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
  1492.         DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
  1493.        
  1494.     ELSE
  1495.         -- JIKA DEFAULT ROLE TIDAK DITEMUKAN, MAKA UPLOAD AKA DIGAGALKAN
  1496.         UPDATE ul_customer A
  1497.         SET status = vFail,
  1498.             update_status = vActionError,
  1499.             message = 'Default Role is not registered on system'
  1500.         WHERE upload_header_id = pUploadHeaderId;
  1501.        
  1502.         -- Hitung jumlah data yang FAIL
  1503.         SELECT COUNT(1) INTO vCountFail
  1504.         FROM ul_customer
  1505.         WHERE upload_header_id = pUploadHeaderId
  1506.             AND status = vFail;
  1507.    
  1508.     END IF;
  1509.        
  1510.     RETURN vCountFail;
  1511.      
  1512. END;  
  1513. $BODY$
  1514.   LANGUAGE plpgsql VOLATILE
  1515.   COST 100;
  1516.   /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top