存储过程学习五

包定义头

CREATE OR REPLACE PACKAGE PK_VSOP_ISMP_TIMER_SYN IS
  TYPE COMMCUR IS REF CURSOR; -- 定义公游标
/**
   *  模块名称: VSOP增值业务交付平台 - ISMP_TIMER_SYN
   *  功   能:  ISMP_TIMER_SYN数据同步
   *  设 计人:  赵兴国(VSOP增值业务交付平台V1.0)
   *  时  间:   2010-09-02
  **/
 
  --合作伙伴同步
  PROCEDURE P_VSOP_ISMP_TIMER_SPCP_SYN;
  --产品同步
  PROCEDURE P_VSOP_ISMP_TIMER_PRODUCT_SYN;
  --订购关系同步
  PROCEDURE P_VSOP_ISMP_TIMER_ORDER_REL;

END PK_VSOP_ISMP_TIMER_SYN;

下面是包体

CREATE OR REPLACE PACKAGE BODY PK_VSOP_ISMP_TIMER_SYN IS
/**
   *  模块名称: VSOP增值业务交付平台 - ISMP_TIMER_SYN
   *  功   能:  ISMP_TIMER_SYN数据同步
   *  设 计人:  赵兴国(VSOP增值业务交付平台V1.0)
   *  时  间:   2010-09-02
  **/
  --获取序列
  FUNCTION getSeq(seqName varchar2) return number is
    seqId number;
  BEGIN
    EXECUTE IMMEDIATE 'select ' || seqName || '.Nextval from dual'
      INTO seqId;
    RETURN seqId;
  END getSeq;


/**
   *  模块名称: VSOP增值业务交付平台 - ISMP_TIMER_SYN
   *  功   能:  ISMP合作伙伴信息同步入库
   *  设 计人:  赵兴国(VSOP增值业务交付平台V1.0)
   *  时  间:   2010-09-02
  **/
  PROCEDURE P_VSOP_ISMP_TIMER_SPCP_SYN
  IS
    V_PARTNER_CUR         COMMCUR;
    partnerTemp           PARTNER_TEMP%ROWTYPE;
    V_COUNT               NUMBER;
    V_PARTNER_ID          NUMBER;
  BEGIN

  OPEN V_PARTNER_CUR FOR 'SELECT T.SPID,
         T.TYPE,
         T.NAMECN,
         T.NAMEEN,
         T.DESCRIPTIONCN,
         T.DESCRIPTIONEN,
         T.CUSTOMERCARE,
         T.WEBSITEURL,
         T.PROVINCEID,
         T.ROAMPROPERTY,
         T.COMPANYADDRESS,
         T.LEGALREPRESENTATIVE,
         T.PRINCIPAL,
         T.PRINCIPALTEL,
         T.PRINCIPALEMAIL,
         T.LICENSE,
         T.CONTRACTEXPIREDATE,
         T.ACCESSNO,
         T.SETTLEMENTCYCLE,
         T.SETTLEMENTPAYTYPE,
         T.SETTLEMENTPERCENT,
         T.CSWEBSITE,
         DECODE(T.STATUS, ''00'', ''G01'', ''02'', ''G05'', ''G05'')
    FROM PARTNER_TEMP T';
         LOOP
               FETCH V_PARTNER_CUR INTO partnerTemp.Spid,partnerTemp.Type,partnerTemp.Namecn,partnerTemp.Nameen,partnerTemp.Descriptioncn,
               partnerTemp.Descriptionen,partnerTemp.Customercare,partnerTemp.Websiteurl,partnerTemp.Provinceid,partnerTemp.Roamproperty,
               partnerTemp.Companyaddress,partnerTemp.Legalrepresentative,partnerTemp.Principal,partnerTemp.Principaltel,partnerTemp.Principalemail,
               partnerTemp.License,partnerTemp.Contractexpiredate,partnerTemp.Accessno,partnerTemp.Settlementcycle,partnerTemp.Settlementpaytype,
               partnerTemp.Settlementpercent,partnerTemp.Cswebsite,partnerTemp.Status;
        
         EXIT WHEN V_PARTNER_CUR%NOTFOUND;
        
         EXECUTE IMMEDIATE'SELECT COUNT(1) FROM PARTNER T WHERE T.PARTNER_CODE = :1'
         INTO V_COUNT
         USING partnerTemp.Spid;

         IF V_COUNT = 1 THEN
         --UPDATE 合作伙伴表
         EXECUTE IMMEDIATE'UPDATE PARTNER T
         SET T.STATE                = :1,
             T.PARTNER_CODE         = :2,
             T.PARTNER_TYPE         = :3,
             T.PARTNER_NAME         = :4,
             T.PARTNER_ENG_NAME     = :5,
             T.PARTNER_DESC         = :6,
             T.PARTNER_ENG_DESC     = :7,
             T.CUST_SERVICE_PHONE   = :8,
             T.PARTNER_URL          = :9,
             T.PARTNER_AREA_CODE    = :10,
             T.IF_ROAM              = :11,
             T.COMPANY_ADDRESS      = :12,
             T.ARTIFICIAL_PERSON    = :13,
             T.PRIMARY_PERSON_NAME  = :14,
             T.PRIMARY_PERSON_PHONE = :15,
             T.PRIMARY_PERSON_EMAIL = :16,
             T.BUSINESS_LICENSE     = :17,
             T.CONTRACT_EXP_DATE    = :18,
             T.PARTNER_NUMBER       = :19,
             T.SETTLE_CYCLE         = :20,
             T.SETTLE_PAY_METHOD    = :21,
             T.SETTLE_RATE          = :22,
             T.CUST_SERVICE_URL     = :23,
             T.STATE_DATE           = SYSDATE
             WHERE T.PARTNER_CODE   = :24'
             USING partnerTemp.Status,partnerTemp.Spid,partnerTemp.Type,partnerTemp.Namecn,partnerTemp.Nameen,partnerTemp.Descriptioncn,
               partnerTemp.Descriptionen,partnerTemp.Customercare,partnerTemp.Websiteurl,partnerTemp.Provinceid,partnerTemp.Roamproperty,
               partnerTemp.Companyaddress,partnerTemp.Legalrepresentative,partnerTemp.Principal,partnerTemp.Principaltel,partnerTemp.Principalemail,
               partnerTemp.License,partnerTemp.Contractexpiredate,partnerTemp.Accessno,partnerTemp.Settlementcycle,partnerTemp.Settlementpaytype,
               partnerTemp.Settlementpercent,partnerTemp.Cswebsite,partnerTemp.Spid;

         ELSIF V_COUNT = 0 THEN
         --INSERT合作伙伴表
         V_PARTNER_ID := getSeq('SEQ_VSOP_PARTNER_ID');
         EXECUTE IMMEDIATE 'INSERT INTO PARTNER
              (PARTNER_ID,
               PARTNER_CODE,
               PARTNER_TYPE,
               PARTNER_NAME,
               PARTNER_ENG_NAME,
               PARTNER_DESC,
               PARTNER_ENG_DESC,
               CUST_SERVICE_PHONE,
               PARTNER_URL,
               PARTNER_AREA_CODE,
               IF_ROAM,
               COMPANY_ADDRESS,
               ARTIFICIAL_PERSON,
               PRIMARY_PERSON_NAME,
               PRIMARY_PERSON_PHONE,
               PRIMARY_PERSON_EMAIL,
               BUSINESS_LICENSE,
               CONTRACT_EXP_DATE,
               PARTNER_NUMBER,
               SETTLE_CYCLE,
               SETTLE_PAY_METHOD,
               SETTLE_RATE,
               CUST_SERVICE_URL,
               STATE)
               VALUES
               (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24)'
               USING V_PARTNER_ID,partnerTemp.Spid,partnerTemp.Type,partnerTemp.Namecn,partnerTemp.Nameen,partnerTemp.Descriptioncn,
               partnerTemp.Descriptionen,partnerTemp.Customercare,partnerTemp.Websiteurl,partnerTemp.Provinceid,partnerTemp.Roamproperty,
               partnerTemp.Companyaddress,partnerTemp.Legalrepresentative,partnerTemp.Principal,partnerTemp.Principaltel,partnerTemp.Principalemail,
               partnerTemp.License,partnerTemp.Contractexpiredate,partnerTemp.Accessno,partnerTemp.Settlementcycle,partnerTemp.Settlementpaytype,
               partnerTemp.Settlementpercent,partnerTemp.Cswebsite,partnerTemp.Status; 
        
/*         EXECUTE IMMEDIATE'INSERT INTO PARTNER_SYSTEM_INFO
              (PARTNER_SYSTEM_INFO_ID,
               SYSTEM_CODE,
               PARTNER_ID,
               CREATE_DATE,
               STATE,
               STATE_DATE)
            VALUES
              (SEQ_VSOP_PART_SYS_ID.nextval, ''204'', :1, SYSDATE, :2, SYSDATE)'
              USING V_PARTNER_ID,partnerTemp.Status;*/
              
         ELSE
         --数据有问题，存在重复合作伙伴编码
         --RETURN;
         EXECUTE IMMEDIATE 'INSERT INTO ISMP_SYN_ERROR(ISMP_SYN_ERROR_ID,OBJ_ID,OBJ_DESC,MARK,ERROR_DESC,ADD_TIME)
         VALUES
         (SEQ_VSOP_PARTNER_ID.NEXTVAL,:1,''合作伙伴编码'',''同步合作伙伴错误'',''存在重复的合作伙伴编码'',SYSDATE)'
         USING partnerTemp.Spid;
         END IF;
         COMMIT;
        
         END LOOP;
         CLOSE V_PARTNER_CUR;
  --EXECUTE IMMEDIATE 'truncate table PARTNER_TEMP';
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END P_VSOP_ISMP_TIMER_SPCP_SYN;
 
 
/**
   *  模块名称: VSOP增值业务交付平台 - ISMP_TIMER_SYN
   *  功   能:  ISMP产品信息同步入库
   *  设 计人:  赵兴国(VSOP增值业务交付平台V1.0)
   *  时  间:   2010-09-02
  **/
  PROCEDURE P_VSOP_ISMP_TIMER_PRODUCT_SYN
  IS
    V_PRODUCT_CUR         COMMCUR;
    V_ABILITY_CUR         COMMCUR;
    productTemp           PRODUCT_TEMP%ROWTYPE;
    V_COUNT               NUMBER;
    V_PRODUCT_ID          NUMBER;
    V_PARTNER_ID          NUMBER;
    V_PARTNER_SYS_INFO_ID NUMBER;
    V_ABILITY_REL_ID      NUMBER;
    V_PROD_OFFER_ID       NUMBER;
    V_OFFER_ROLE_ID       NUMBER;
    V_ABILITY_ID          VARCHAR2(20);
    V_LATAIN_ID           VARCHAR2(20);
  BEGIN

  OPEN V_PRODUCT_CUR FOR 'SELECT PRODUCTID,
           PNAMECN,
           PDESCRIPTIONCN,
           DECODE(SCOPE, ''13'', ''03'', ''02''),
           PARTNER_ID,
           DECODE(STATUS,
                  ''01'',
                  ''G01'',
                  ''00'',
                  ''G02'',
                  ''02'',
                  ''G04'',
                  ''G05''),
           DECODE(PRODUCTHOST, NULL, ''200''),
           ''888'',
           SERVICEID
      FROM PRODUCT_TEMP T1, PARTNER T2
     WHERE T1.SPID = T2.PARTNER_CODE';
   
         LOOP
               FETCH V_PRODUCT_CUR INTO productTemp.Productid,productTemp.Pnamecn,productTemp.Pdescriptioncn,productTemp.Scope,
               V_PARTNER_ID,productTemp.Status,productTemp.Producthost,V_LATAIN_ID,productTemp.Serviceid;
        
         EXIT WHEN V_PRODUCT_CUR%NOTFOUND;
        
         EXECUTE IMMEDIATE'SELECT COUNT(1) FROM PRODUCT T WHERE T.PRODUCT_NBR = :1'
         INTO V_COUNT
         USING productTemp.Productid;
        
         IF V_COUNT = 0 THEN
         --INSERT产品表，产品接入平台表，产品关系表，业务能力表，并分三步生成基础销售品
         --插入产品表
         V_PRODUCT_ID := getSeq('SEQ_VSOP_PRD_ID');
         EXECUTE IMMEDIATE'INSERT INTO PRODUCT
            (PRODUCT_ID,
             PRODUCT_NBR,
             PRODUCT_NAME,
             PRODUCT_DESC,
             MANAGE_GRADE,
             PRODUCT_PROVIDER_ID,
             PRODUCT_STATE_CD,
             PROD_FUNC_TYPE,
             PROD_BUNDLE_TYPE,
             ORDER_HOST,
             LATN_ID,
             SERVICE_ID)
             VALUES
             (:1,:2,:3,:4,:5,:6,:7,''03'',''01'',:8,:9,:10)'
             USING V_PRODUCT_ID,productTemp.Productid,productTemp.Pnamecn,productTemp.Pdescriptioncn,productTemp.Scope,
               V_PARTNER_ID,productTemp.Status,productTemp.Producthost,V_LATAIN_ID,productTemp.Serviceid;
         --插入产品关系   
         EXECUTE IMMEDIATE 'INSERT INTO PRODUCT_RELATION
            (PRODUCT_REL_ID,
             RELATION_TYPE_CD,
             PRODUCT_ID,
             PRO_PRODUCT_ID,
             STATE_CD,
             STATE_DATE,
             CREATE_DATE)
            SELECT SEQ_VSOP_OFFER_ROLE_ID.NEXTVAL,
                   ''0'',
                   :2,
                   T.PRODUCT_ID,
                   :3,
                   SYSDATE,
                   SYSDATE
              FROM PRODUCT T
             WHERE T.SERVICE_ID = :1'
             USING productTemp.Serviceid,V_PRODUCT_ID,productTemp.Status;

         --插入产品接入平台表
         V_PARTNER_SYS_INFO_ID := getSeq('SEQ_VSOP_PRD_SYS_INFO_ID');
         EXECUTE IMMEDIATE'INSERT INTO PRODUCT_SYSTEM_INFO
            (PARTNER_SYS_INFO_ID,
             PRODUCT_ID,
             SYSTEM_CODE,
             CREATE_DATE,
             STATE,
             STATE_DATE)
             VALUES
             (:1,:2,''204'',SYSDATE,:3,SYSDATE)'
             USING V_PARTNER_SYS_INFO_ID,V_PRODUCT_ID,productTemp.Status;
        
         --插入业务能力表
         OPEN V_ABILITY_CUR FOR'SELECT T.SERVICECAPABILITYID FROM PROD_SERVICE_ABILITY_REL_TEMP T WHERE T.PRODUCTID = :1'
              USING productTemp.Productid; 
         LOOP
              FETCH V_ABILITY_CUR INTO V_ABILITY_ID;
          
           EXIT WHEN V_ABILITY_CUR%NOTFOUND;
        
           V_ABILITY_ID := '1'+V_ABILITY_ID;   
           V_ABILITY_REL_ID := getSeq('SEQ_VSOP_PRD_SERV_ABILI_ID');
           EXECUTE IMMEDIATE'INSERT INTO PRODUCT_SERVICE_ABILITY_REL(PRD_SERV_ABILITY_REL_ID,PRODUCT_ID,SERVICE_CODE,REL_TYPE)
           VALUES
           (:1,:2,:3,''02'')'
           USING V_ABILITY_REL_ID,V_PRODUCT_ID,V_ABILITY_ID;
        
         END LOOP;
         CLOSE V_ABILITY_CUR;
        
         --分三步生成基础销售品
         --1。插入销售品
         V_PROD_OFFER_ID := getSeq('SEQ_VSOP_OFR_ID');
         EXECUTE IMMEDIATE'INSERT INTO PROD_OFFER
          (PROD_OFFER_ID,
           FEE_SET_FLAG,
           PROD_OFFER_SUB_TYPE,
           OFFER_DESC,
           PRICING_DESC,
           PROD_OFFER_NAME,
           STATE,
           STATE_DATE,
           MANAGE_GRADE,
           OFFER_NBR)
           VALUES
           (:1,''1'',''0'',:2,:3,:4,:5,SYSDATE,:6,:7)'
           USING V_PROD_OFFER_ID,productTemp.Pdescriptioncn,productTemp.Chargingpolicycn,productTemp.Pnamecn,productTemp.Status,
           productTemp.Scope,productTemp.Productid;
          
         --2。插入角色表
         V_OFFER_ROLE_ID := getSeq('SEQ_VSOP_OFFER_ROLE_ID');
         EXECUTE IMMEDIATE'INSERT INTO PROD_OFFER_DETAIL_ROLE(PROD_OFFER_ROLE_CD,STATE,PROD_OFFER_ID)
         VALUES
         (:1,:2,:3)'
         USING V_OFFER_ROLE_ID,productTemp.Status,V_PROD_OFFER_ID;
        
         --3。插入角色成员关系表
         EXECUTE IMMEDIATE'INSERT INTO ROLE_PROD_RELA(PROD_OFFER_ROLE_CD,PRODUCT_ID)
         VALUES
         (:1,:2)'
         USING V_OFFER_ROLE_ID,V_PRODUCT_ID;

         ELSIF V_COUNT = 1 THEN
         --开始更新操作
         --UPDATE产品表，产品接入平台表，产品关系表，业务能力表，并分三步生成基础销售品
         --update产品表
         EXECUTE IMMEDIATE 'UPDATE PRODUCT T
           SET T.PRODUCT_NAME        = :1,
               T.PRODUCT_DESC        = :2,
               T.MANAGE_GRADE        = :3,
               T.PRODUCT_PROVIDER_ID = :4,
               T.PRODUCT_STATE_CD    = :5,
               T.PROD_FUNC_TYPE      = ''03'',
               T.PROD_BUNDLE_TYPE    = ''01'',
               T.ORDER_HOST          = :6,
               T.LATN_ID             = :7,
               T.SERVICE_ID          = :8
               WHERE T.PRODUCT_NBR   = :9'
               USING productTemp.Pnamecn,productTemp.Pdescriptioncn,productTemp.Scope,
               V_PARTNER_ID,productTemp.Status,productTemp.Producthost,V_LATAIN_ID,productTemp.Serviceid,productTemp.Productid;
         --更新产品关系
         --删除产品关系
         EXECUTE IMMEDIATE 'SELECT T.PRODUCT_ID FROM PRODUCT T WHERE T.PRODUCT_NBR = :1'
         INTO V_PRODUCT_ID
         USING productTemp.Productid;
         EXECUTE IMMEDIATE 'DELETE FROM PRODUCT_RELATION T WHERE T.PRODUCT_ID = :1'
         USING productTemp.Productid;
         --插入产品关系   
         EXECUTE IMMEDIATE 'INSERT INTO PRODUCT_RELATION
            (PRODUCT_REL_ID,
             RELATION_TYPE_CD,
             PRODUCT_ID,
             PRO_PRODUCT_ID,
             STATE_CD,
             STATE_DATE,
             CREATE_DATE)
            SELECT SEQ_VSOP_OFFER_ROLE_ID.NEXTVAL,
                   ''0'',
                   :2,
                   T.PRODUCT_ID,
                   :3,
                   SYSDATE,
                   SYSDATE
              FROM PRODUCT T
             WHERE T.SERVICE_ID = :1'
             USING productTemp.Serviceid,V_PRODUCT_ID,productTemp.Status;
            
         --update业务能力表
         --插入业务能力表
         OPEN V_ABILITY_CUR FOR'SELECT T.SERVICECAPABILITYID FROM PROD_SERVICE_ABILITY_REL_TEMP T WHERE T.PRODUCTID = :1'
              USING productTemp.Productid; 
         LOOP
              FETCH V_ABILITY_CUR INTO V_ABILITY_ID;
          
           EXIT WHEN V_ABILITY_CUR%NOTFOUND;
          
           IF V_ABILITY_ID IS NOT NULL AND length(V_ABILITY_ID)>0 THEN
             V_ABILITY_ID := '1'+V_ABILITY_ID;
             EXECUTE IMMEDIATE 'DELETE FROM PRODUCT_SERVICE_ABILITY_REL WHERE PRODUCT_ID = :1'
             USING V_PRODUCT_ID;  
             V_ABILITY_REL_ID := getSeq('SEQ_VSOP_PRD_SERV_ABILI_ID');
             EXECUTE IMMEDIATE'INSERT INTO PRODUCT_SERVICE_ABILITY_REL(PRD_SERV_ABILITY_REL_ID,PRODUCT_ID,SERVICE_CODE,REL_TYPE)
             VALUES
             (:1,:2,:3.''02'')'
             USING V_ABILITY_REL_ID,V_PRODUCT_ID,V_ABILITY_ID;
           END IF;
        
         END LOOP;
         CLOSE V_ABILITY_CUR;
        
         --UPDATE销售品表
         EXECUTE IMMEDIATE 'UPDATE PROD_OFFER T
           SET T.MANAGE_GRADE        = :1,
               T.FEE_SET_FLAG        = ''1'',
               T.PROD_OFFER_SUB_TYPE = ''0'',
               T.OFFER_DESC          = :2,
               T.PRICING_DESC        = :3,
               T.PROD_OFFER_NAME     = :4,
               T.STATE               = :5,
               T.STATE_DATE          = SYSDATE
               WHERE T.OFFER_NBR     = :6'
               USING productTemp.Scope, productTemp.Pdescriptioncn,productTemp.Chargingpolicycn,productTemp.Pnamecn,
               productTemp.Status,productTemp.Productid;

         ELSE
         --数据错误
         --RETURN;
        
         EXECUTE IMMEDIATE 'INSERT INTO ISMP_SYN_ERROR(ISMP_SYN_ERROR_ID,OBJ_ID,OBJ_DESC,MARK,ERROR_DESC,ADD_TIME)
         VALUES
         (SEQ_VSOP_PARTNER_ID.NEXTVAL,:1,''产品编码'',''同步产品错误'',''存在重复的产品编码'',SYSDATE)'
         USING productTemp.Productid;
        
         END IF;
         --循环一次就提交
         COMMIT;
        
         END LOOP;
         CLOSE V_PRODUCT_CUR;
  COMMIT;
 
  --EXECUTE IMMEDIATE 'truncate table PRODUCT_TEMP';
  --EXECUTE IMMEDIATE 'truncate table PROD_SERVICE_ABILITY_REL_TEMP';
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END P_VSOP_ISMP_TIMER_PRODUCT_SYN;
 
 
/**
   *  模块名称: VSOP增值业务交付平台 - ISMP_TIMER_SYN
   *  功   能:  ISMP订购关系信息同步入库
   *  设 计人:  赵兴国(VSOP增值业务交付平台V1.0)
   *  时  间:   2010-09-02
  **/
  PROCEDURE P_VSOP_ISMP_TIMER_ORDER_REL
  IS
    V_RELATION_CUR        COMMCUR;
    V_PRODUCT_ID          NUMBER;
    V_OFFER_ID            NUMBER;
    V_PROD_INST_ID        NUMBER;
    V_STATE               VARCHAR2(20);
    V_ACC_NBR             VARCHAR2(20);
    V_PRODUCT_NBR         VARCHAR2(60);
  BEGIN 
  OPEN V_RELATION_CUR FOR 'SELECT T.USERID,T.PRODUCTID,T.STATE FROM ORDER_RELATION_TEMP T';
 
  LOOP         
       FETCH V_RELATION_CUR INTO V_ACC_NBR,V_PRODUCT_NBR,V_STATE;   
       EXIT WHEN V_RELATION_CUR%NOTFOUND;
  CASE
    --正常
    WHEN V_STATE='00' THEN
    V_STATE := '1001';
    --待生效
    WHEN V_STATE='01' THEN
    V_STATE := '3001';
    --暂停＋待生效
    WHEN V_STATE='02' THEN
    V_STATE := '3001';
    --暂停
    WHEN V_STATE='03' THEN
    V_STATE := '1001';
    --待失效
    WHEN V_STATE='04' THEN
    V_STATE := '1001';
    --暂停＋待失效
    WHEN V_STATE='05' THEN
    V_STATE := '1001';
    --注销
    WHEN V_STATE='06' THEN
    V_STATE := '1101';
    --自体验
    WHEN V_STATE='07' THEN
    V_STATE := '1001';
    --批量导入免费体验
    WHEN V_STATE='08' THEN
    V_STATE := '1001';
    --沉默状态
    WHEN V_STATE='09' THEN
    V_STATE := '1001';
    --免费体验
    WHEN V_STATE='10' THEN
    V_STATE := '1001';
    ELSE
    V_STATE := '1101';
    END CASE;
      
  EXECUTE IMMEDIATE'SELECT T.PROD_INST_ID FROM PROD_INST T WHERE T.ACC_NBR = :1'
  INTO V_PROD_INST_ID
  USING V_ACC_NBR;

  IF V_PROD_INST_ID IS NOT NULL AND length(V_PROD_INST_ID)>0 THEN
 
  EXECUTE IMMEDIATE 'SELECT A.PRODUCT_ID, D.PROD_OFFER_ID
      FROM PRODUCT A, ROLE_PROD_RELA B, PROD_OFFER_DETAIL_ROLE C, PROD_OFFER D
     WHERE A.PRODUCT_ID = B.PRODUCT_ID
       AND B.PROD_OFFER_ROLE_CD = C.PROD_OFFER_ROLE_CD
       AND C.PROD_OFFER_ID = D.PROD_OFFER_ID AND A.PRODUCT_NBR = :1'
  INTO V_PRODUCT_ID,V_OFFER_ID
  USING V_PRODUCT_NBR;
 
  IF V_PRODUCT_ID IS NOT NULL AND length(V_PRODUCT_ID)>0 THEN
  EXECUTE IMMEDIATE 'INSERT INTO ORDER_RELATION
  (ORDER_RELATION_ID,PROD_INST_ID,PRODUCT_ID,PROD_OFFER_ID,STATE,SUBSCRIBE_TIME,MODIFY_TIME)
  VALUES
  (SEQ_VSOP_ORDER_REL_ID.NEXTVAL,:1,:2,:3,:4,SYSDATE,SYSDATE)'
  USING V_PROD_INST_ID,V_PRODUCT_ID,V_OFFER_ID,V_STATE;
  ELSE
  --数据错误
  EXECUTE IMMEDIATE 'INSERT INTO ISMP_SYN_ERROR(ISMP_SYN_ERROR_ID,OBJ_ID,OBJ_DESC,MARK,ERROR_DESC,ADD_TIME)
     VALUES
     (SEQ_VSOP_PARTNER_ID.NEXTVAL,:1,''产品编码'',''同步订购关系错误'',''产品编码不存在'',SYSDATE)'
     USING V_PRODUCT_NBR;
  END IF;

  ELSE
  --数据错误
  EXECUTE IMMEDIATE 'INSERT INTO ISMP_SYN_ERROR(ISMP_SYN_ERROR_ID,OBJ_ID,OBJ_DESC,MARK,ERROR_DESC,ADD_TIME)
     VALUES
     (SEQ_VSOP_PARTNER_ID.NEXTVAL,:1,''客户手机号码'',''同步订购关系错误'',''此号码不存在'',SYSDATE)'
     USING V_ACC_NBR;
 
  END IF; 
  COMMIT;
  END LOOP;
  CLOSE V_RELATION_CUR;
 
  --EXECUTE IMMEDIATE 'truncate table ORDER_RELATION_TEMP';
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END P_VSOP_ISMP_TIMER_ORDER_REL;


END PK_VSOP_ISMP_TIMER_SYN;