API tools faq
paste
Advertisement
aadddrr

f_transfer_calculator_catalog_price

aadddrr
Jun 25th, 2018
158
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 17.14 KB | None | 0 0
raw download clone embed print report
  1. /**
  2.  * Adrian, June 25, 2018
  3.  * Transfer data calculator_catalog_price dari WSC
  4.  */
  5.  
  6. CREATE OR REPLACE FUNCTION f_transfer_calculator_catalog_price(bigint, bigint, character varying)
  7.   RETURNS bigint AS
  8. $BODY$
  9. DECLARE
  10.     pTenantId               alias for $1;
  11.     pUserId                 alias for $2;
  12.     pDatetime               alias for $3;
  13.    
  14.     vDbName                 text;
  15.     vDbUser                 text;
  16.     vDbPassword             text;
  17.     vDbPort                 text;
  18.     vDbHostAddr             text;
  19.    
  20.     vCount                  bigint;
  21.     vPrevId                 bigint;
  22.     vMaxId                  bigint;
  23.    
  24. BEGIN
  25.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWSC.DB.NAME')::text INTO vDbName;
  26.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWSC.DB.USER')::text INTO vDbUser;
  27.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWSC.DB.PASSWORD')::text INTO vDbPassword;
  28.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWSC.DB.PORT')::text INTO vDbPort;
  29.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWSC.DB.HOSTADDR')::text INTO vDbHostAddr;
  30.    
  31.     vCount := 0;
  32.    
  33.     /**
  34.      * 1. Ambil id terakhir
  35.      */
  36.     SELECT last_calculator_catalog_price_id FROM tt_last_calculator_catalog_price WHERE tenant_id = pTenantId INTO vPrevId;
  37.    
  38.     vMaxId := vPrevId;
  39.    
  40.     /**
  41.      * 2. Ambil data dari WSC, simpan jumlah datanya
  42.      */
  43.     WITH inserted_calculator_catalog_price AS (
  44.         INSERT INTO sp_calculator_catalog_price(
  45.                 calculator_catalog_price_id, tenant_id, catalog_code, style_product_code,
  46.                 doc_type, merchant_code, status_pkp, brand_code, category_level_1,
  47.                 supplier_price, suggestion_retail_price, discount_member, discount_promo,
  48.                 margin_internal, margin_supplier, product_value, weight, length,
  49.                 width, height, fee_ds, royalty, suggested_ebitda, minimum_ebitda,
  50.                 opex_cost, catalog_cost, marketing_cost, bonus_member, fee_reseller,
  51.                 fee_tambahan_ds, fee_box_packaging, volume_parameter, faktor_harga_netto,
  52.                 faktor_harga_netto_excl_ppn, faktor_gross_profit, faktor_internal_cost,
  53.                 faktor_fee_ds, faktor_harga_netto_amount, faktor_harga_netto_excl_ppn_amount,
  54.                 faktor_gross_profit_amount, faktor_internal_cost_amount, faktor_fee_ds_amount,
  55.                 faktor_fee_ds_percentage, faktor_kinerja_ds_percentage, faktor_operasional_percentage,
  56.                 faktor_gross_profit_percentage, supplier_price_excl_ppn, ppn_in_amount,
  57.                 catalog_price_suggestion, catalog_price_zone_0, catalog_price_zone_1,
  58.                 catalog_price_zone_2, discount_member_amount, discount_member_amount_zone_0,
  59.                 discount_member_amount_zone_1, discount_member_amount_zone_2,
  60.                 member_price_zone_0, member_price_zone_1, member_price_zone_2,
  61.                 fee_ds_amount_zone_0, fee_ds_amount_zone_1, fee_ds_amount_zone_2,
  62.                 nett_price_amount_zone_0, nett_price_amount_zone_1, nett_price_amount_zone_2,
  63.                 nett_price_excl_ppn_amount_zone_0, nett_price_excl_ppn_amount_zone_1,
  64.                 nett_price_excl_ppn_amount_zone_2, ppn_out_amount_zone_0, ppn_out_amount_zone_1,
  65.                 ppn_out_amount_zone_2, product_value_amount_zone_0, product_value_amount_zone_1,
  66.                 product_value_amount_zone_2, fee_reseller_amount_zone_0, fee_reseller_amount_zone_1,
  67.                 fee_reseller_amount_zone_2, fee_tambahan_ds_amount_zone_0, fee_tambahan_ds_amount_zone_1,
  68.                 fee_tambahan_ds_amount_zone_2, bonus_member_amount_zone_0, bonus_member_amount_zone_1,
  69.                 bonus_member_amount_zone_2, total_volume, weight_kg, shipping_cost_zone_0,
  70.                 shipping_cost_zone_1, shipping_cost_zone_2, freight_cost_zone_0,
  71.                 freight_cost_zone_1, freight_cost_zone_2, opex_cost_zone_0, opex_cost_zone_1,
  72.                 opex_cost_zone_2, catalog_cost_zone_0, catalog_cost_zone_1, catalog_cost_zone_2,
  73.                 marketing_cost_zone_0, marketing_cost_zone_1, marketing_cost_zone_2,
  74.                 royalty_amount_zone_0, royalty_amount_zone_1, royalty_amount_zone_2,
  75.                 ebitda_amount_zone_0, ebitda_amount_zone_1, ebitda_amount_zone_2,
  76.                 ebitda_percentage_zone_0, ebitda_percentage_zone_1, ebitda_percentage_zone_2,
  77.                 flg_data_change, flg_calculate, create_datetime, create_user_id,
  78.                 update_datetime, update_user_id, version, flg_new_product, previous_product_value,
  79.                 previous_catalog_price_zone_0, previous_catalog_price_zone_1,
  80.                 previous_catalog_price_zone_2, previous_member_price_zone_0,
  81.                 previous_member_price_zone_1, previous_member_price_zone_2)
  82.         SELECT A.calculator_catalog_price_id, A.tenant_id, A.catalog_code, A.style_product_code,
  83.                 A.doc_type, A.merchant_code, A.status_pkp, A.brand_code, A.category_level_1,
  84.                 A.supplier_price, A.suggestion_retail_price, A.discount_member, A.discount_promo,
  85.                 A.margin_internal, A.margin_supplier, A.product_value, A.weight, A.length,
  86.                 A.width, A.height, A.fee_ds, A.royalty, A.suggested_ebitda, A.minimum_ebitda,
  87.                 A.opex_cost, A.catalog_cost, A.marketing_cost, A.bonus_member, A.fee_reseller,
  88.                 A.fee_tambahan_ds, A.fee_box_packaging, A.volume_parameter, A.faktor_harga_netto,
  89.                 A.faktor_harga_netto_excl_ppn, A.faktor_gross_profit, A.faktor_internal_cost,
  90.                 A.faktor_fee_ds, A.faktor_harga_netto_amount, A.faktor_harga_netto_excl_ppn_amount,
  91.                 A.faktor_gross_profit_amount, A.faktor_internal_cost_amount, A.faktor_fee_ds_amount,
  92.                 A.faktor_fee_ds_percentage, A.faktor_kinerja_ds_percentage, A.faktor_operasional_percentage,
  93.                 A.faktor_gross_profit_percentage, A.supplier_price_excl_ppn, A.ppn_in_amount,
  94.                 A.catalog_price_suggestion, A.catalog_price_zone_0, A.catalog_price_zone_1,
  95.                 A.catalog_price_zone_2, A.discount_member_amount, A.discount_member_amount_zone_0,
  96.                 A.discount_member_amount_zone_1, A.discount_member_amount_zone_2,
  97.                 A.member_price_zone_0, A.member_price_zone_1, A.member_price_zone_2,
  98.                 A.fee_ds_amount_zone_0, A.fee_ds_amount_zone_1, A.fee_ds_amount_zone_2,
  99.                 A.nett_price_amount_zone_0, A.nett_price_amount_zone_1, A.nett_price_amount_zone_2,
  100.                 A.nett_price_excl_ppn_amount_zone_0, A.nett_price_excl_ppn_amount_zone_1,
  101.                 A.nett_price_excl_ppn_amount_zone_2, A.ppn_out_amount_zone_0, A.ppn_out_amount_zone_1,
  102.                 A.ppn_out_amount_zone_2, A.product_value_amount_zone_0, A.product_value_amount_zone_1,
  103.                 A.product_value_amount_zone_2, A.fee_reseller_amount_zone_0, A.fee_reseller_amount_zone_1,
  104.                 A.fee_reseller_amount_zone_2, A.fee_tambahan_ds_amount_zone_0, A.fee_tambahan_ds_amount_zone_1,
  105.                 A.fee_tambahan_ds_amount_zone_2, A.bonus_member_amount_zone_0, A.bonus_member_amount_zone_1,
  106.                 A.bonus_member_amount_zone_2, A.total_volume, A.weight_kg, A.shipping_cost_zone_0,
  107.                 A.shipping_cost_zone_1, A.shipping_cost_zone_2, A.freight_cost_zone_0,
  108.                 A.freight_cost_zone_1, A.freight_cost_zone_2, A.opex_cost_zone_0, opex_cost_zone_1,
  109.                 A.opex_cost_zone_2, A.catalog_cost_zone_0, A.catalog_cost_zone_1, A.catalog_cost_zone_2,
  110.                 A.marketing_cost_zone_0, A.marketing_cost_zone_1, A.marketing_cost_zone_2,
  111.                 A.royalty_amount_zone_0, A.royalty_amount_zone_1, A.royalty_amount_zone_2,
  112.                 A.ebitda_amount_zone_0, A.ebitda_amount_zone_1, A.ebitda_amount_zone_2,
  113.                 A.ebitda_percentage_zone_0, A.ebitda_percentage_zone_1, A.ebitda_percentage_zone_2,
  114.                 A.flg_data_change, A.flg_calculate, A.create_datetime, A.create_user_id,
  115.                 A.update_datetime, A.update_user_id, A.version, A.flg_new_product, A.previous_product_value,
  116.                 A.previous_catalog_price_zone_0, A.previous_catalog_price_zone_1,
  117.                 A.previous_catalog_price_zone_2, A.previous_member_price_zone_0,
  118.                 A.previous_member_price_zone_1, A.previous_member_price_zone_2
  119.         FROM dblink(
  120.             'dbname='|| f_get_value_system_config_by_param_code(11, 'PALOMAWSC.DB.NAME')::text || '
  121.             port='||f_get_value_system_config_by_param_code(11, 'PALOMAWSC.DB.PORT')::text||'
  122.             hostaddr='||f_get_value_system_config_by_param_code(11, 'PALOMAWSC.DB.HOSTADDR')::text||'
  123.             user='||f_get_value_system_config_by_param_code(11, 'PALOMAWSC.DB.USER')::text||'
  124.             password='||f_get_value_system_config_by_param_code(11, 'PALOMAWSC.DB.PASSWORD')::text||''::text,
  125.             'SELECT calculator_catalog_price_id, tenant_id, catalog_code, style_product_code,
  126.                 doc_type, merchant_code, status_pkp, brand_code, category_level_1,
  127.                 supplier_price, suggestion_retail_price, discount_member, discount_promo,
  128.                 margin_internal, margin_supplier, product_value, weight, length,
  129.                 width, height, fee_ds, royalty, suggested_ebitda, minimum_ebitda,
  130.                 opex_cost, catalog_cost, marketing_cost, bonus_member, fee_reseller,
  131.                 fee_tambahan_ds, fee_box_packaging, volume_parameter, faktor_harga_netto,
  132.                 faktor_harga_netto_excl_ppn, faktor_gross_profit, faktor_internal_cost,
  133.                 faktor_fee_ds, faktor_harga_netto_amount, faktor_harga_netto_excl_ppn_amount,
  134.                 faktor_gross_profit_amount, faktor_internal_cost_amount, faktor_fee_ds_amount,
  135.                 faktor_fee_ds_percentage, faktor_kinerja_ds_percentage, faktor_operasional_percentage,
  136.                 faktor_gross_profit_percentage, supplier_price_excl_ppn, ppn_in_amount,
  137.                 catalog_price_suggestion, catalog_price_zone_0, catalog_price_zone_1,
  138.                 catalog_price_zone_2, discount_member_amount, discount_member_amount_zone_0,
  139.                 discount_member_amount_zone_1, discount_member_amount_zone_2,
  140.                 member_price_zone_0, member_price_zone_1, member_price_zone_2,
  141.                 fee_ds_amount_zone_0, fee_ds_amount_zone_1, fee_ds_amount_zone_2,
  142.                 nett_price_amount_zone_0, nett_price_amount_zone_1, nett_price_amount_zone_2,
  143.                 nett_price_excl_ppn_amount_zone_0, nett_price_excl_ppn_amount_zone_1,
  144.                 nett_price_excl_ppn_amount_zone_2, ppn_out_amount_zone_0, ppn_out_amount_zone_1,
  145.                 ppn_out_amount_zone_2, product_value_amount_zone_0, product_value_amount_zone_1,
  146.                 product_value_amount_zone_2, fee_reseller_amount_zone_0, fee_reseller_amount_zone_1,
  147.                 fee_reseller_amount_zone_2, fee_tambahan_ds_amount_zone_0, fee_tambahan_ds_amount_zone_1,
  148.                 fee_tambahan_ds_amount_zone_2, bonus_member_amount_zone_0, bonus_member_amount_zone_1,
  149.                 bonus_member_amount_zone_2, total_volume, weight_kg, shipping_cost_zone_0,
  150.                 shipping_cost_zone_1, shipping_cost_zone_2, freight_cost_zone_0,
  151.                 freight_cost_zone_1, freight_cost_zone_2, opex_cost_zone_0, opex_cost_zone_1,
  152.                 opex_cost_zone_2, catalog_cost_zone_0, catalog_cost_zone_1, catalog_cost_zone_2,
  153.                 marketing_cost_zone_0, marketing_cost_zone_1, marketing_cost_zone_2,
  154.                 royalty_amount_zone_0, royalty_amount_zone_1, royalty_amount_zone_2,
  155.                 ebitda_amount_zone_0, ebitda_amount_zone_1, ebitda_amount_zone_2,
  156.                 ebitda_percentage_zone_0, ebitda_percentage_zone_1, ebitda_percentage_zone_2,
  157.                 flg_data_change, flg_calculate, create_datetime, create_user_id,
  158.                 update_datetime, update_user_id, version, flg_new_product, previous_product_value,
  159.                 previous_catalog_price_zone_0, previous_catalog_price_zone_1,
  160.                 previous_catalog_price_zone_2, previous_member_price_zone_0,
  161.                 previous_member_price_zone_1, previous_member_price_zone_2
  162.             FROM sp_calculator_catalog_price
  163.             WHERE calculator_catalog_price_id > '||vPrevId||''::text ) AS A
  164.             (
  165.                 calculator_catalog_price_id bigint,
  166.                 tenant_id bigint,  
  167.                 catalog_code character varying(50),
  168.                 style_product_code character varying(20),  
  169.                 doc_type character varying(10),
  170.                 merchant_code character varying(20),   
  171.                 status_pkp character varying(1),   
  172.                 brand_code character varying(20),  
  173.                 category_level_1 character varying(20),
  174.                 supplier_price numeric(15,2),  
  175.                 suggestion_retail_price numeric(15,2), 
  176.                 discount_member numeric(7,2),  
  177.                 discount_promo numeric(7,2),   
  178.                 margin_internal numeric(7,2),  
  179.                 margin_supplier numeric(7,2),  
  180.                 product_value numeric(7,2),
  181.                 weight numeric(7,2),   
  182.                 length numeric(7,2),   
  183.                 width numeric(7,2),
  184.                 height numeric(7,2),   
  185.                 fee_ds numeric(7,2),   
  186.                 royalty numeric(7,2),  
  187.                 suggested_ebitda numeric(7,2), 
  188.                 minimum_ebitda numeric(7,2),   
  189.                 opex_cost numeric(7,2),
  190.                 catalog_cost numeric(7,2), 
  191.                 marketing_cost numeric(7,2),   
  192.                 bonus_member numeric(7,2), 
  193.                 fee_reseller numeric(7,2), 
  194.                 fee_tambahan_ds numeric(7,2),  
  195.                 fee_box_packaging numeric(7,2),
  196.                 volume_parameter numeric(7,2), 
  197.                 faktor_harga_netto numeric,
  198.                 faktor_harga_netto_excl_ppn numeric,   
  199.                 faktor_gross_profit numeric,   
  200.                 faktor_internal_cost numeric,  
  201.                 faktor_fee_ds numeric, 
  202.                 faktor_harga_netto_amount numeric(15,2),   
  203.                 faktor_harga_netto_excl_ppn_amount numeric(15,2),  
  204.                 faktor_gross_profit_amount numeric(15,2),  
  205.                 faktor_internal_cost_amount numeric(15,2), 
  206.                 faktor_fee_ds_amount numeric(15,2),
  207.                 faktor_fee_ds_percentage numeric(7,2), 
  208.                 faktor_kinerja_ds_percentage numeric(7,2), 
  209.                 faktor_operasional_percentage numeric(7,2),
  210.                 faktor_gross_profit_percentage numeric(7,2),   
  211.                 supplier_price_excl_ppn numeric(15,2), 
  212.                 ppn_in_amount numeric(15,2),   
  213.                 catalog_price_suggestion numeric(15,2),
  214.                 catalog_price_zone_0 numeric(15,2),
  215.                 catalog_price_zone_1 numeric(15,2),
  216.                 catalog_price_zone_2 numeric(15,2),
  217.                 discount_member_amount numeric(15,2),  
  218.                 discount_member_amount_zone_0 numeric(15,2),   
  219.                 discount_member_amount_zone_1 numeric(15,2),   
  220.                 discount_member_amount_zone_2 numeric(15,2),   
  221.                 member_price_zone_0 numeric(15,2), 
  222.                 member_price_zone_1 numeric(15,2), 
  223.                 member_price_zone_2 numeric(15,2), 
  224.                 fee_ds_amount_zone_0 numeric(15,2),
  225.                 fee_ds_amount_zone_1 numeric(15,2),
  226.                 fee_ds_amount_zone_2 numeric(15,2),
  227.                 nett_price_amount_zone_0 numeric(15,2),
  228.                 nett_price_amount_zone_1 numeric(15,2),
  229.                 nett_price_amount_zone_2 numeric(15,2),
  230.                 nett_price_excl_ppn_amount_zone_0 numeric(15,2),   
  231.                 nett_price_excl_ppn_amount_zone_1 numeric(15,2),   
  232.                 nett_price_excl_ppn_amount_zone_2 numeric(15,2),   
  233.                 ppn_out_amount_zone_0 numeric(15,2),   
  234.                 ppn_out_amount_zone_1 numeric(15,2),   
  235.                 ppn_out_amount_zone_2 numeric(15,2),   
  236.                 product_value_amount_zone_0 numeric(15,2), 
  237.                 product_value_amount_zone_1 numeric(15,2), 
  238.                 product_value_amount_zone_2 numeric(15,2), 
  239.                 fee_reseller_amount_zone_0 numeric(15,2),  
  240.                 fee_reseller_amount_zone_1 numeric(15,2),  
  241.                 fee_reseller_amount_zone_2 numeric(15,2),  
  242.                 fee_tambahan_ds_amount_zone_0 numeric(15,2),   
  243.                 fee_tambahan_ds_amount_zone_1 numeric(15,2),   
  244.                 fee_tambahan_ds_amount_zone_2 numeric(15,2),   
  245.                 bonus_member_amount_zone_0 numeric(15,2),  
  246.                 bonus_member_amount_zone_1 numeric(15,2),  
  247.                 bonus_member_amount_zone_2 numeric(15,2),  
  248.                 total_volume numeric(7,2), 
  249.                 weight_kg numeric(7,2),
  250.                 shipping_cost_zone_0 numeric(15,2),
  251.                 shipping_cost_zone_1 numeric(15,2),
  252.                 shipping_cost_zone_2 numeric(15,2),
  253.                 freight_cost_zone_0 numeric(15,2), 
  254.                 freight_cost_zone_1 numeric(15,2), 
  255.                 freight_cost_zone_2 numeric(15,2), 
  256.                 opex_cost_zone_0 numeric(15,2),
  257.                 opex_cost_zone_1 numeric(15,2),
  258.                 opex_cost_zone_2 numeric(15,2),
  259.                 catalog_cost_zone_0 numeric(15,2), 
  260.                 catalog_cost_zone_1 numeric(15,2), 
  261.                 catalog_cost_zone_2 numeric(15,2), 
  262.                 marketing_cost_zone_0 numeric(15,2),   
  263.                 marketing_cost_zone_1 numeric(15,2),   
  264.                 marketing_cost_zone_2 numeric(15,2),   
  265.                 royalty_amount_zone_0 numeric(15,2),   
  266.                 royalty_amount_zone_1 numeric(15,2),   
  267.                 royalty_amount_zone_2 numeric(15,2),   
  268.                 ebitda_amount_zone_0 numeric(15,2),
  269.                 ebitda_amount_zone_1 numeric(15,2),
  270.                 ebitda_amount_zone_2 numeric(15,2),
  271.                 ebitda_percentage_zone_0 numeric(7,2), 
  272.                 ebitda_percentage_zone_1 numeric(7,2), 
  273.                 ebitda_percentage_zone_2 numeric(7,2), 
  274.                 flg_data_change character varying(14), 
  275.                 flg_calculate character varying(14),   
  276.                 create_datetime character varying(14), 
  277.                 create_user_id bigint, 
  278.                 update_datetime character varying(14), 
  279.                 update_user_id bigint, 
  280.                 version bigint,
  281.                 flg_new_product character varying(1),  
  282.                 previous_product_value numeric(7,2),   
  283.                 previous_catalog_price_zone_0 numeric(15,2),   
  284.                 previous_catalog_price_zone_1 numeric(15,2),   
  285.                 previous_catalog_price_zone_2 numeric(15,2),   
  286.                 previous_member_price_zone_0 numeric(15,2),
  287.                 previous_member_price_zone_1 numeric(15,2),
  288.                 previous_member_price_zone_2 numeric(15,2)
  289.             )
  290.         RETURNING *
  291.     )
  292.     SELECT COUNT(1) FROM inserted_calculator_catalog_price INTO vCount;
  293.    
  294.     IF(vCount > 0) THEN
  295.         SELECT MAX(calculator_catalog_price_id) FROM sp_calculator_catalog_price INTO vMaxId;
  296.        
  297.         /**
  298.          * 3. Update id terakhir
  299.          */
  300.         UPDATE tt_last_calculator_catalog_price SET last_calculator_catalog_price_id = vMaxId WHERE tenant_id = pTenantId;
  301.        
  302.         /**
  303.          * 4. Update nilai sequence terakhir
  304.          */
  305.         PERFORM SETVAL('calculator_catalog_price_seq', vMaxId);
  306.        
  307.     END IF;
  308.    
  309.     RETURN vCount;
  310.    
  311. END;
  312. $BODY$
  313.   LANGUAGE plpgsql VOLATILE
  314.   COST 100;
  315.   /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!