prc_nrc.prc

数据迁移使用的ETL程序包

                                )
                                values
                                (
                                      :l_ViewId
                                     ,2
                                     ,sysdate
                                     ,sysdate
                                     ,sysdate
                                     ,null
                                     ,:l_TRACKING_ID
                                     ,:l_ServerID
                                     ,:l_TypeIdNrc
                                     ,:BILLING_ACCOUNT_NO
                                     ,1
                                     ,:l_SubscrNo
                                     ,0
                                     ,:l_TRACKING_ID
                                     ,:l_ServerID
                                     ,null
                                     ,sysdate
                                     ,:EFFECTIVE_DATE
                                     ,null
                                     ,null
                                     ,1
                                     ,0
                                     ,14
                                     ,:RATE
                                     ,sysdate
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,''MIG''
                                     ,sysdate
                                     ,:EFFECTIVE_DATE
                                     ,0
                                     ,0
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,null
                                     ,0
                                     ,null
                                     ,null           
                                )'
            using l_ViewId, l_TRACKING_ID, l_ServerID, l_TypeIdNrc, l_AccountNo, l_SubscrNo, l_TRACKING_ID, l_ServerID, l_NrcEffectiveDate, l_NrcAmount, l_NrcEffectiveDate;
       
       --nrc 
          EXECUTE IMMEDIATE 'insert into arbor.nrc' || l_mig_db ||
                            ' (            
                               TRACKING_ID                          
                              ,TRACKING_ID_SERV                    
                              ,TYPE_ID_NRC                         
                              ,BILLING_ACCOUNT_NO                  
                              ,OPEN_ITEM_ID                        
                                 ,PARENT_SUBSCR_NO                    
                              ,PARENT_SUBSCR_NO_RESETS             
                              ,PARENT_TRACKING_ID                  
                                 ,PARENT_TRACKING_ID_SERV             
                              ,ELEMENT_ID                          
                              ,TRANSACT_DATE                       
                              ,EFFECTIVE_DATE                      
                              ,ANNOTATION                          
                                 ,ANNOTATION2                         
                                 ,NRC_CATEGORY                        
                                 ,NO_BILL                             
                                 ,CURRENCY_CODE                       
                              ,RATE                                
                              ,RATE_DT                             
                              ,ORDER_NUMBER                        
                              ,CUSTOMER_ORDER_NUMBER               
                              ,BILL_ORDER_NUMBER                   
                              ,CCARD_ID                            
                              ,CCARD_ID_SERV                       
                              ,SHIP_FROM_GEOCODE                   
                              ,SHIP_TO_GEOCODE                     
                              ,CHG_WHO                             
                              ,CHG_DT                              
                              ,DATE_NRC_JOURNALABLE                
                              ,TOTAL_INSTALLMENTS                  
                              ,CURRENT_INSTALLMENT                 
                              ,SALES_CHANNEL_ID                    
                              ,TAX_PKG_INST_ID                     
                              ,TAX_TYPE_CODE                       
                              ,FEDERAL_TAX                         
                              ,STATE_TAX                           
                              ,COUNTY_TAX                          
                              ,CITY_TAX                            
                              ,OTHER_TAX                           
                              ,SALES_ID                            
                              ,CONTRACT_ASSOCIATION_TYPE           
                              ,CONTRACT_TRACKING_ID                
                              ,CONTRACT_TRACKING_ID_SERV           
                              ,VIEW_ID                             
                              )
                            values
                              (
                                  :l_TRACKING_ID
                                 ,:l_ServerID
                                 ,:l_NrcId
                                 ,:BILLING_ACCOUNT_NO
                                 ,1
                                 ,:l_SubscrNo
                                 ,0
                                 ,:l_TRACKING_ID
                                 ,:l_ServerID
                                 ,null
                                 ,sysdate
                                 ,:EFFECTIVE_DATE
                                 ,null
                                 ,null
                                 ,1
                                 ,0
                                 ,14
                                 ,:RATE
                                 ,sysdate
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,''MIG''
                                 ,sysdate
                                 ,:TOTAL_INSTALLMENTS  
                                 ,0
                                 ,0
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,null
                                 ,0
                                 ,null
                                 ,null
                                 ,:l_ViewId   
                              )'
            using l_TRACKING_ID, l_ServerID, l_TypeIdNrc, l_AccountNo, l_SubscrNo, l_TRACKING_ID, l_ServerID, l_NrcEffectiveDate, l_NrcAmount, l_NrcEffectiveDate, l_ViewId;
        
        exception
          when others then
            v_ErrorCode := SQLCODE;
            v_ErrorMsg  := SUBSTR(SQLERRM, 1, 2000);
            if sqlcode <= -20000 or sqlcode in (-6502, 100) then
              l_flag := 1;
            ELSE
              l_flag := 0;
            end if;
            if l_flag = 1 then
              update src_inr_batch_nrc
                 set status = 'E'
               where row_id = l_RowId;
              GOTO NEXTLOOP01555;
            else
              raise;
            end if;
        End;
        update src_inr_batch_nrc set status = 'S' where row_id = l_RowId;
        l_CommitCount := l_CommitCount + 1;
        if mod(l_CommitCount, 500) = 0 then
          commit;
        end if;
      end loop;
      commit;
    end;
  end loop;
exception
  when others then
    v_ErrorCode   := SQLCODE;
    v_ErrorMsg    := SUBSTR(SQLERRM, 1, 2000);
    v_CurrentUser := USER;
    v_Information := 'row_id=' || to_char(l_RowId) ||
                     '. Error encoutered on ' ||
                     TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') ||
                     ' by database user ' || v_CurrentUser;
    rollback;
    l_ProName := 'prc_nrc';
    insert into log_process_error
      (PROCESS_NAME, ERROR_CODE, ERROR_MESSAGE, ERROR_INFO, ROW_ID)
    VALUES
      (l_ProName, v_ErrorCode, v_ErrorMsg, v_Information, l_RowId1);
    commit;
end prc_nrc;
/