-- 創(chuàng)建包
create or replace package sync_PF_OLDCUSTOMER_MESSAGE is

  -- Author  : lwj
  -- Created : 2019/3/29 11:01:17
  -- Purpose : 符合老友貸客戶群體信息表-在我司貸款即將在3個(gè)月內(nèi)結(jié)清

   --新增
  function ap_merger_data_add(v_ptype IN NUMBER,v_comtype IN NUMBER,v_reason OUT VARCHAR) RETURN NUMBER;

end sync_PF_OLDCUSTOMER_MESSAGE;




------------------------------------------------------------------------------------


-- 創(chuàng)建方法體
create or replace package body sync_PF_OLDCUSTOMER_MESSAGE is

  --新增
  function ap_merger_data_add(v_ptype IN NUMBER,v_comtype IN NUMBER,v_reason OUT VARCHAR)
    return NUMBER is
    v_l_result NUMBER := 0;

  begin
    PLOG.log('---sync_PF_OLDCUSTOMER_MESSAGE.ap_merger_data_add insert PF_OLDCUSTOMER_MESSAGE start ');
    -- 新增記錄
    BEGIN
insert into PF_OLDCUSTOMER_MESSAGE
  (id,
   apply_id,
   apply_date,
   bargain_no,
   apply_status,
   fact_end_date,
   time_limit,
   loan_money,
   car_price,
   car_brand_id,
   car_sub_brand_id,
   cust_name,
   identity_type,
   identity_no,
   sex,
   phone,
   dealer_id,
   dealer_name,
   super_organization_no,
   customer_id,
   organization_no)
  select id,
         napplyid,
         dapplydate,
         vbargainno,
         napplystatus,
         dfactenddate,
         ntimelimit,
         nloanmoney,
         ncarprice,
         ncarbrandid,
         ncarsubbrandid,
         vcustname,
         videntitytype,
         videntityno,
         vsex,
         vphone,
         ndealerid,
         vdealername,
         vsuperorganizationno,
         ncustomerid,
         vorganizationno
    from PD_OLDCUSTOMER_MESSAGE @Afuat 
  where NPTYPE= v_ptype and NCOMTYPE= v_comtype);
    EXCEPTION
        WHEN OTHERS THEN
        PLOG.log('---sync_PF_OLDCUSTOMER_MESSAGE.ap_merger_data_add insert PF_OLDCUSTOMER_MESSAGE failed ');
        v_reason :='sync_PF_OLDCUSTOMER_MESSAGE.ap_merger_data_add insert PF_OLDCUSTOMER_MESSAGE failed ';
        ora_raiserror(SQLCODE,'sync_PF_OLDCUSTOMER_MESSAGE.ap_merger_data_add  insert into PF_OLDCUSTOMER_MESSAGE failed',
                      $$PLSQL_LINE);
        RETURN - 1;
    END;
    PLOG.log('---sync_PF_OLDCUSTOMER_MESSAGE.ap_merger_data_add insert PF_OLDCUSTOMER_MESSAGE finish');
    RETURN v_l_result;
  end;
end sync_PF_OLDCUSTOMER_MESSAGE;