|
Arunraj
Starting Member
18 Posts |
 Posted - 2014-10-23 : 10:20:27
|
| Hi Friends,I have Flatfile table in which i need to export the details to respective Parent and Child table.And also i have the LOG Error Table seperatly,in which If i any rows got rejected while Transmitting data from Flatfile table to Parent and Child Table.My Problem is I have almost 1 Parent table and 10 Child Table.If any Rows got rejected i need to know in which Parent or Child table i got the error in a seperate column as ERROR TABLE.Please tell me how to do this in the Script .I have attached the full script with this message.-- ============================================================================================================================================-- STORED PROCEDURE FOR FLATFILE_SALES-- ==============================================================================================================================================USE [IconicMarketing] ---=========================================================SALES_CURSUR===================================================================--USE [IconicMarketing]--GODECLARE @FileType varchar(50), @ACDealerID varchar(50), @ClientDealerID varchar(50), @DMSType varchar(50), @DealNumber varchar(50), @CustomerNumber varchar(50), @CustomerName varchar(50), @CustomerFirstName varchar(50), @CustomerLastName varchar(50), @CustomerAddress varchar(50), @CustomerCity varchar(50), @CustomerState varchar(50), @CustomerZip varchar(50), @CustomerCounty varchar(50), @CustomerHomePhone varchar(50), @CustomerWorkPhone varchar(50), @CustomerCellPhone varchar(50), @CustomerPagerPhone varchar(50), @CustomerEmail varchar(50), @CustomerBirthDate date, @MailBlock varchar(50), @CoBuyerName varchar(50), @CoBuyerFirstName varchar(50), @CoBuyerLastName varchar(50), @CoBuyerAddress varchar(50), @CoBuyerCity varchar(50), @CoBuyerState varchar(50), @CoBuyerZip varchar(50), @CoBuyerCounty varchar(50), @CoBuyerHomePhone varchar(50), @CoBuyerWorkPhone varchar(50), @CoBuyerBirthDate date, @Salesman_1_Number varchar(50), @Salesman_1_Name varchar(50), @Salesman_2_Number varchar(50), @Salesman_2_Name varchar(50), @ClosingManagerName varchar(50), @ClosingManagerNumber varchar(50), @F_AND_I_ManagerNumber varchar(50), @F_AND_I_ManagerName varchar(50), @SalesManagerNumber varchar(50), @SalesManagerName varchar(50), @EntryDate varchar(30), @DealBookDate varchar(30), @VehicleYear varchar(50), @VehicleMake varchar(50), @VehicleModel varchar(50), @VehicleStockNumber varchar(50), @VehicleVIN varchar(50), @VehicleExteriorColor varchar(50), @VehicleInteriorColor varchar(50), @VehicleMileage varchar(50), @VehicleType varchar(50), @InServiceDate varchar(50), @HoldBackAmount varchar(50), @DealType varchar(50), @SaleType varchar(50), @BankCode varchar(50), @BankName varchar(50), @SalesmanCommission varchar(50), @GrossProfitSale varchar(50), @FinanceReserve varchar(50), @CreditLifePremium varchar(50), @CreditLifeCommision varchar(50), @TotalInsuranceReserve varchar(50), @BalloonAmount varchar(50), @CashPrice varchar(50), @AmountFinanced varchar(50), @TotalOfPayments varchar(50), @MSRP varchar(50), @DownPayment varchar(50), @SecurityDesposit varchar(50), @Rebate varchar(50), @Term varchar(50), @RetailPayment varchar(50), @PaymentType varchar(50), @RetailFirstPayDate varchar(50), @LeaseFirstPayDate varchar(50), @DayToFirstPayment varchar(50), @LeaseAnnualMiles varchar(50), @MileageRate varchar(50), @APRRate varchar(50), @ResidualAmount varchar(50), @LicenseFee varchar(50), @RegistrationFee varchar(50), @TotalTax varchar(50), @ExtendedWarrantyName varchar(50), @ExtendedWarrantyTerm varchar(50), @ExtendedWarrantyLimitMiles varchar(50), @ExtendedWarrantyDollar varchar(50), @ExtendedWarrantyProfit varchar(50), @FrontGross varchar(50), @BackGross varchar(50), @TradeIn_1_VIN varchar(50), @TradeIn_2_VIN varchar(50), @TradeIn_1_Make varchar(50), @TradeIn_2_Make varchar(50), @TradeIn_1_Model varchar(50), @TradeIn_2_Model varchar(50), @TradeIn_1_ExteriorColor varchar(50), @TradeIn_2_ExteriorColor varchar(50), @TradeIn_1_Year varchar(50), @TradeIn_2_Year varchar(50), @TradeIn_1_Mileage varchar(50), @TradeIn_2_Mileage varchar(50), @TradeIn_1_Gross varchar(50), @TradeIn_2_Gross varchar(50), @TradeIn_1_Payoff varchar(50), @TradeIn_2_Payoff varchar(50), @TradeIn_1_ACV varchar(50), @TradeIn_2_ACV varchar(50), @Fee_1_Name varchar(50), @Fee_1_Fee varchar(50), @Fee_1_Commission varchar(50), @Fee_2_Name varchar(50), @Fee_2_Fee varchar(50), @Fee_2_Commission varchar(50), @Fee_3_Name varchar(50), @Fee_3_Fee varchar(50), @Fee_3_Commission varchar(50), @Fee_4_Name varchar(50), @Fee_4_Fee varchar(50), @Fee_4_Commission varchar(50), @Fee_5_Name varchar(50), @Fee_5_Fee varchar(50), @Fee_5_Commission varchar(50), @Fee_6_Name varchar(50), @Fee_6_Fee varchar(50), @Fee_6_Commission varchar(50), @Fee_7_Name varchar(50), @Fee_7_Fee varchar(50), @Fee_7_Commission varchar(50), @Fee_8_Name varchar(50), @Fee_8_Fee varchar(50), @Fee_8_Commission varchar(50), @Fee_9_Name varchar(50), @Fee_9_Fee varchar(50), @Fee_9_Commission varchar(50), @Fee_10_Name varchar(50), @Fee_10_Fee varchar(50), @Fee_10_Commission varchar(50), @ContractDate varchar(50), @InsuranceName varchar(50), @InsuranceAgentName varchar(50), @InsuranceAddress varchar(50), @InsuranceCity varchar(50), @InsuranceState varchar(50), @InsuranceZip varchar(50), @InsurancePhone varchar(50), @InsurancePolicyNumber varchar(50), @InsuranceEffectiveDate varchar(50), @InsuranceExpirationDate varchar(50), @InsuranceCompensationDeduction varchar(50), @TradeIn_1_InteriorColor varchar(50), @TradeIn_2_InteriorColor varchar(50), @PhoneBlock varchar(50), @LicensePlateNumber varchar(50), @Cost varchar(50), @InvoiceAmount varchar(50), @FinanceCharge varchar(50), @TotalPickupPayment varchar(50), @TotalAccessories varchar(50), @TotalDriveOffAmount varchar(50), @EmailBlock varchar(50), @ModelDescriptionOfCarSold varchar(50), @VehicleClassification varchar(50), @ModelNumberOfCarSold varchar(50), @GAPPremium varchar(50), @LastInstallmentDate varchar(50), @CashDeposit varchar(50), @AHPremium varchar(50), @LeaseRate varchar(50), @DealerSelect varchar(50), @LeasePayment varchar(50), @LeaseNetCapCost varchar(50), @LeaseTotalCapReduction varchar(50), @DealStatus varchar(50), @CustomerSuffix varchar(50), @CustomerSalutation varchar(50), @CustomerAddress2 varchar(50), @CustomerMiddleName varchar(50), @GlobalOptOut varchar(50), @LeaseTerm varchar(50), @ExtendedWarrantyFlag varchar(50), @Salesman_3_Number varchar(50), @Salesman_3_Name varchar(50), @Salesman_4_Number varchar(50), @Salesman_4_Name varchar(50), @Salesman_5_Number varchar(50), @Salesman_5_Name varchar(50), @Salesman_6_Number varchar(50), @Salesman_6_Name varchar(50), @APRRate2 varchar(50), @APRRate3 varchar(50), @APRRate4 varchar(50), @Term2 varchar(50), @SecurityDeposit2 varchar(50), @DownPayment2 varchar(50), @TotalOfPayments2 varchar(50), @BasePayment varchar(50), @JournalSaleAmount varchar(50), @IndividualBusinessFlag varchar(50), @InventoryDate varchar(30), @StatusDate varchar(50), @ListPrice varchar(50), @NetTradeAmount varchar(50), @TrimLevel varchar(50), @SubTrimLevel varchar(50), @BodyDescription varchar(50), @BodyDoorCount varchar(50), @TransmissionDesc varchar(50), @EngineDesc varchar(50), @TypeCode varchar(50), @SLCT2 varchar(50), @DealDateOffset varchar(50), @AccountingDate varchar(30), @CoBuyerCustNum varchar(50), @CoBuyerCell varchar(50), @CoBuyerEmail varchar(50), @CoBuyerSalutation varchar(50), @CoBuyerPhoneBlock varchar(50), @CoBuyerMailBlock varchar(50), @CoBuyerEmailBlock varchar(50), @RealBookDate varchar(50), @CoBuyerMiddleName varchar(50), @CoBuyerCountry varchar(50), @CoBuyerAddress2 varchar(50), @CoBuyerOptOut varchar(50), @CoBuyerOccupation varchar(50), @CoBuyerEmployer varchar(50), @Country varchar(50), @Occupation varchar(50), @Employer varchar(50), @Salesman2Commission varchar(50), @BankAddress varchar(50), @BankCity varchar(50), @BankState varchar(50), @BankZip varchar(50), @LeaseEstimatedMiles varchar(50), @AFTReserve varchar(50), @CreditLifePrem varchar(50), @CreditLifeRes varchar(50), @AHRes varchar(50), @Language varchar(50), @BuyRate varchar(50), @DMVAmount varchar(50), @Weight varchar(50), @StateDMVTotFee varchar(50), @ROSNumber varchar(50), @Incentives varchar(50), @CASS_STD_LINE1 varchar(50), @CASS_STD_LINE2 varchar(50), @CASS_STD_CITY varchar(50), @CASS_STD_STATE varchar(50), @CASS_STD_ZIP varchar(50), @CASS_STD_ZIP4 varchar(50), @CASS_STD_DPBC varchar(50), @CASS_STD_CHKDGT varchar(50), @CASS_STD_CART varchar(50), @CASS_STD_LOT varchar(50), @CASS_STD_LOTORD varchar(50), @CASS_STD_URB varchar(50), @CASS_STD_FIPS varchar(50), @CASS_STD_EWS varchar(50), @CASS_STD_LACS varchar(50), @CASS_STD_ZIPMOV varchar(50), @CASS_STD_Z4LOM varchar(50), @CASS_STD_NDIAPT varchar(50), @CASS_STD_NDIRR varchar(50), @CASS_STD_LACSRT varchar(50), @CASS_STD_ERROR_CD varchar(50), @NCOA_AC_ID varchar(50), @NCOA_COA_ADDSRC varchar(50), @NCOA_COA_MATCH varchar(50), @NCOA_COA_MOVTYP varchar(50), @NCOA_COA_DATE varchar(50), @NCOA_COA_DELCD varchar(50), @NCOA_COA_RTYPE varchar(50), @NCOA_COA_RTNCD varchar(50), @NCOA_COA_LINE1 varchar(50), @NCOA_COA_LINE2 varchar(50), @NCOA_COA_CITY varchar(50), @NCOA_COA_STATE varchar(50), @NCOA_COA_ZIP varchar(50), @NCOA_COA_ZIP4 varchar(50), @NCOA_COA_DPBC varchar(50), @NCOA_COA_CHKDGT varchar(50), @NCOA_COA_CART varchar(50), @NCOA_COA_LOT varchar(50), @NCOA_COA_LOTORD varchar(50), @NCOA_COA_URB varchar(50), @NCOA_COA_Z4LOM varchar(50), @NCOA_COA_ACTION varchar(50), @NCOA_COA_QNAME varchar(50), @NCOA_DPV_AA varchar(50), @NCOA_DPV_A1 varchar(50), @NCOA_DPV_BB varchar(50), @NCOA_DPV_CC varchar(50), @NCOA_DPV_M1 varchar(50), @NCOA_DPV_M3 varchar(50), @NCOA_DPV_N1 varchar(50), @NCOA_DPV_P1 varchar(50), @NCOA_DPV_P3 varchar(50), @NCOA_DPV_RR varchar(50), @NCOA_DPV_R1 varchar(50), @NCOA_DPV_STATUS varchar(50), @NCOA_DPV_F1 varchar(50), @NCOA_DPV_G1 varchar(50), @NCOA_DPV_U1 varchar(50), @myerror varchar(500), @SalesID int, @errornumber int, @errorseverity varchar(500), @errorstate int, @errorprocedure varchar(500), @errorline varchar(50), @errormessage varchar(1000), @Sal_Id Int;DECLARE Sales_Cursor CURSOR FOR SELECT * from FLATFILE_SALES;OPEN Sales_Cursor;--:r C:\Clients\BlackBook\BlackBookMarketing\Bharath\FETCH_SALES.sqlFETCH NEXT FROM Sales_Cursor INTO @FileType , @ACDealerID , @ClientDealerID , @DMSType , @DealNumber , @CustomerNumber , @CustomerName , @CustomerFirstName , @CustomerLastName , @CustomerAddress , @CustomerCity , @CustomerState , @CustomerZip , @CustomerCounty , @CustomerHomePhone , @CustomerWorkPhone , @CustomerCellPhone , @CustomerPagerPhone , @CustomerEmail , @CustomerBirthDate , @MailBlock , @CoBuyerName , @CoBuyerFirstName , @CoBuyerLastName , @CoBuyerAddress , @CoBuyerCity , @CoBuyerState , @CoBuyerZip , @CoBuyerCounty , @CoBuyerHomePhone , @CoBuyerWorkPhone , @CoBuyerBirthDate , @Salesman_1_Number , @Salesman_1_Name , @Salesman_2_Number , @Salesman_2_Name , @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName , @EntryDate , @DealBookDate , @VehicleYear , @VehicleMake , @VehicleModel , @VehicleStockNumber , @VehicleVIN , @VehicleExteriorColor , @VehicleInteriorColor , @VehicleMileage , @VehicleType , @InServiceDate , @HoldBackAmount , @DealType , @SaleType , @BankCode , @BankName , @SalesmanCommission , @GrossProfitSale , @FinanceReserve , @CreditLifePremium , @CreditLifeCommision , @TotalInsuranceReserve , @BalloonAmount , @CashPrice , @AmountFinanced , @TotalOfPayments , @MSRP , @DownPayment , @SecurityDesposit , @Rebate , @Term , @RetailPayment , @PaymentType , @RetailFirstPayDate , @LeaseFirstPayDate , @DayToFirstPayment , @LeaseAnnualMiles , @MileageRate , @APRRate , @ResidualAmount , @LicenseFee , @RegistrationFee , @TotalTax , @ExtendedWarrantyName , @ExtendedWarrantyTerm , @ExtendedWarrantyLimitMiles , @ExtendedWarrantyDollar , @ExtendedWarrantyProfit , @FrontGross , @BackGross , @TradeIn_1_VIN , @TradeIn_2_VIN , @TradeIn_1_Make , @TradeIn_2_Make , @TradeIn_1_Model , @TradeIn_2_Model , @TradeIn_1_ExteriorColor , @TradeIn_2_ExteriorColor , @TradeIn_1_Year , @TradeIn_2_Year , @TradeIn_1_Mileage , @TradeIn_2_Mileage , @TradeIn_1_Gross , @TradeIn_2_Gross , @TradeIn_1_Payoff , @TradeIn_2_Payoff , @TradeIn_1_ACV , @TradeIn_2_ACV , @Fee_1_Name , @Fee_1_Fee , @Fee_1_Commission , @Fee_2_Name , @Fee_2_Fee , @Fee_2_Commission , @Fee_3_Name , @Fee_3_Fee , @Fee_3_Commission , @Fee_4_Name , @Fee_4_Fee , @Fee_4_Commission , @Fee_5_Name , @Fee_5_Fee , @Fee_5_Commission , @Fee_6_Name , @Fee_6_Fee , @Fee_6_Commission , @Fee_7_Name , @Fee_7_Fee , @Fee_7_Commission , @Fee_8_Name , @Fee_8_Fee , @Fee_8_Commission , @Fee_9_Name , @Fee_9_Fee , @Fee_9_Commission , @Fee_10_Name , @Fee_10_Fee , @Fee_10_Commission , @ContractDate , @InsuranceName , @InsuranceAgentName , @InsuranceAddress , @InsuranceCity , @InsuranceState , @InsuranceZip , @InsurancePhone , @InsurancePolicyNumber , @InsuranceEffectiveDate , @InsuranceExpirationDate , @InsuranceCompensationDeduction , @TradeIn_1_InteriorColor , @TradeIn_2_InteriorColor , @PhoneBlock , @LicensePlateNumber , @Cost , @InvoiceAmount , @FinanceCharge , @TotalPickupPayment , @TotalAccessories , @TotalDriveOffAmount , @EmailBlock , @ModelDescriptionOfCarSold , @VehicleClassification , @ModelNumberOfCarSold , @GAPPremium , @LastInstallmentDate , @CashDeposit , @AHPremium , @LeaseRate , @DealerSelect , @LeasePayment , @LeaseNetCapCost , @LeaseTotalCapReduction , @DealStatus , @CustomerSuffix , @CustomerSalutation , @CustomerAddress2 , @CustomerMiddleName , @GlobalOptOut , @LeaseTerm , @ExtendedWarrantyFlag , @Salesman_3_Number , @Salesman_3_Name , @Salesman_4_Number , @Salesman_4_Name , @Salesman_5_Number , @Salesman_5_Name , @Salesman_6_Number , @Salesman_6_Name , @APRRate2 , @APRRate3 , @APRRate4 , @Term2 , @SecurityDeposit2 , @DownPayment2 , @TotalOfPayments2 , @BasePayment , @JournalSaleAmount , @IndividualBusinessFlag , @InventoryDate , @StatusDate , @ListPrice , @NetTradeAmount , @TrimLevel , @SubTrimLevel , @BodyDescription , @BodyDoorCount , @TransmissionDesc , @EngineDesc , @TypeCode , @SLCT2 , @DealDateOffset , @AccountingDate , @CoBuyerCustNum , @CoBuyerCell , @CoBuyerEmail , @CoBuyerSalutation , @CoBuyerPhoneBlock , @CoBuyerMailBlock , @CoBuyerEmailBlock , @RealBookDate , @CoBuyerMiddleName , @CoBuyerCountry , @CoBuyerAddress2 , @CoBuyerOptOut , @CoBuyerOccupation , @CoBuyerEmployer , @Country , @Occupation , @Employer , @Salesman2Commission , @BankAddress , @BankCity , @BankState , @BankZip , @LeaseEstimatedMiles , @AFTReserve , @CreditLifePrem , @CreditLifeRes , @AHRes , @Language , @BuyRate , @DMVAmount , @Weight , @StateDMVTotFee , @ROSNumber , @Incentives , @CASS_STD_LINE1 , @CASS_STD_LINE2 , @CASS_STD_CITY , @CASS_STD_STATE , @CASS_STD_ZIP , @CASS_STD_ZIP4 , @CASS_STD_DPBC , @CASS_STD_CHKDGT , @CASS_STD_CART , @CASS_STD_LOT , @CASS_STD_LOTORD , @CASS_STD_URB , @CASS_STD_FIPS , @CASS_STD_EWS , @CASS_STD_LACS , @CASS_STD_ZIPMOV , @CASS_STD_Z4LOM , @CASS_STD_NDIAPT , @CASS_STD_NDIRR , @CASS_STD_LACSRT , @CASS_STD_ERROR_CD , @NCOA_AC_ID , @NCOA_COA_ADDSRC , @NCOA_COA_MATCH , @NCOA_COA_MOVTYP , @NCOA_COA_DATE , @NCOA_COA_DELCD , @NCOA_COA_RTYPE , @NCOA_COA_RTNCD , @NCOA_COA_LINE1 , @NCOA_COA_LINE2 , @NCOA_COA_CITY , @NCOA_COA_STATE , @NCOA_COA_ZIP , @NCOA_COA_ZIP4 , @NCOA_COA_DPBC , @NCOA_COA_CHKDGT , @NCOA_COA_CART , @NCOA_COA_LOT , @NCOA_COA_LOTORD , @NCOA_COA_URB , @NCOA_COA_Z4LOM , @NCOA_COA_ACTION , @NCOA_COA_QNAME , @NCOA_DPV_AA , @NCOA_DPV_A1 , @NCOA_DPV_BB , @NCOA_DPV_CC , @NCOA_DPV_M1 , @NCOA_DPV_M3 , @NCOA_DPV_N1 , @NCOA_DPV_P1 , @NCOA_DPV_P3 , @NCOA_DPV_RR , @NCOA_DPV_R1 , @NCOA_DPV_STATUS , @NCOA_DPV_F1 , @NCOA_DPV_G1 , @NCOA_DPV_U1;WHILE @@FETCH_STATUS = 0BEGINPRINT @VehicleVIN ;--================================================================================================================================================================-- ******************************************** SCRIPT TO REMOVE DUPLICATE ROWS IN SALES PARENT AND CHILD TABLE ***********************************************--================================================================================================================================================================IF Exists (Select 1 From SALES Where IconicDealerID = @ClientDealerID And DealNumber = @DealNumber And CustomerNumber = @CustomerNumber And DMSType = @DMSType And ContractDate = @ContractDate) Begin Select @SalesID = ID From SALES Where IconicDealerID = @ClientDealerID And DealNumber = @DealNumber And CustomerNumber = @CustomerNumber And DMSType = @DMSType And ContractDate = @ContractDate Delete From [SALES_AMOUNT] Where SalesID = @Sal_Id Delete From [dbo].[SALES_BANKINFO] Where SalesID = @Sal_Id Delete From [dbo].[SALES_COBUYER] Where SalesID = @Sal_Id Delete From [dbo].[SALES_CUSTOMER] Where SalesID = @Sal_Id Delete From [dbo].[SALES_DATE] Where SalesID = @Sal_Id Delete From [dbo].[SALES_FEE] Where SalesID = @Sal_Id Delete From [dbo].[SALES_HR] Where SalesID = @Sal_Id Delete From [dbo].[SALES_TRADEIN] Where SalesID = @Sal_Id Delete From [dbo].[SALES_VEHICLE] Where SalesID = @Sal_Id Delete From SALES Where ID = @Sal_Id End--================================================================================================================================================-- ***************************************************** INSERT INTO SALES TABLE******************************************************** --================================================================================================================================================BEGIN TRY INSERT INTO Sales ( IconicDealerID, DealNumber, CustomerNumber, DMSType, ContractDate ) VALUES (@ClientDealerID,@DealNumber,@CustomerNumber,@DMSType,@ContractDate);END TRYBEGIN CATCH SELECT @errornumber = ERROR_NUMBER(), @errorseverity = ERROR_SEVERITY(), @errorstate = ERROR_STATE() , @errorprocedure = ERROR_PROCEDURE() , @errorline = ERROR_LINE(), @errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCHPRINT @errornumber;PRINT @errorseverity;PRINT @errorprocedure;PRINT @errorline;PRINT @errormessage;PRINT @errorstate; set @myerror = @@ERROR; -- This PRINT statement prints 'Error = 0' because -- @@ERROR is reset in the IF statement above. PRINT N'Error = ' + @myerror;set @SalesID = scope_identity();PRINT @SalesID;--================================================================================================================================================-- *********************************************Insert into SALES_TRADEIN Table****************************************************************--================================================================================================================================================BEGIN TRYIF(@TradeIn_1_VIN is not null)BEGIN -- 1INSERT INTO SALES_TRADEIN ( SalesID, TradeIn_VIN, TradeIn_Make, TradeIn_Model, TradeIn_ExteriorColor, TradeIn_Year, TradeIn_Mileage, TradeIn_Gross, TradeIn_Payoff, TradeIn_ACV, TradeIn_InteriorColor ) VALUES ( @SalesID, @TradeIn_1_VIN, @TradeIn_1_Make , @TradeIn_1_Model, @TradeIn_1_ExteriorColor, @TradeIn_1_Year , @TradeIn_1_Mileage, @TradeIn_1_Gross , @TradeIn_1_Payoff, @TradeIn_1_ACV, @TradeIn_1_InteriorColor ); END -- 1 IF(@TradeIn_2_VIN is not null) BEGIN -- 2 INSERT INTO SALES_TRADEIN ( SalesID, TradeIn_VIN, TradeIn_Make, TradeIn_Model, TradeIn_ExteriorColor, TradeIn_Year, TradeIn_Mileage, TradeIn_Gross, TradeIn_Payoff, TradeIn_ACV, TradeIn_InteriorColor ) VALUES ( @SalesID, @TradeIn_2_VIN, @TradeIn_2_Make , @TradeIn_2_Model, @TradeIn_2_ExteriorColor, @TradeIn_2_Year , @TradeIn_2_Mileage, @TradeIn_2_Gross , @TradeIn_2_Payoff, @TradeIn_2_ACV, @TradeIn_2_InteriorColor ); END -- 2 --IF(@TradeIn_3_VIN is not null) --BEGIN -- 3 --INSERT INTO SALES_TRADEIN -- ( -- SalesID, -- TradeIn_VIN, -- TradeIn_Make, -- TradeIn_Model, -- TradeIn_ExteriorColor, -- TradeIn_Year, -- TradeIn_Mileage, -- TradeIn_Gross, -- TradeIn_Payoff, -- TradeIn_ACV, -- TradeIn_InteriorColor -- ) --VALUES ( -- @SalesID, -- @TradeIn_3_VIN, -- @TradeIn_3_Make , -- @TradeIn_3_Model, -- @TradeIn_3_ExteriorColor, -- @TradeIn_3_Year , -- @TradeIn_3_Mileage, -- @TradeIn_3_Gross , -- @TradeIn_3_Payoff, -- @TradeIn_3_ACV, -- @TradeIn_3_InteriorColor -- ); --END -- 3END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- **************************************************Insert into SALES_VEHICLE Table************************************************************--================================================================================================================================================BEGIN TRYINSERT INTO SALES_VEHICLE ( SalesID , VehicleYear , VehicleMake , VehicleModel , VehicleStockNumber , VehicleVIN , VehicleExteriorColor , VehicleInteriorColor , VehicleMileage , VehicleType , InServiceDate , LeaseAnnualMiles , ExtendedWarrantyName , ExtendedWarrantyTerm , ExtendedWarrantyLimitMiles , LicensePlateNumber , ModelDescriptionOfCarSold , VehicleClassification , ModelNumberOfCarSold , ExtendedWarrantyFlag , TrimLevel , SubTrimLevel , BodyDescription , BodyDoorCount , TransmissionDesc , EngineDesc , TypeCode , Weight , LeaseEstimatedMiles ) VALUES ( @SalesID,@VehicleYear,@VehicleMake,@VehicleModel,@VehicleStockNumber,@VehicleVIN, @VehicleExteriorColor,@VehicleInteriorColor,@VehicleMileage,@VehicleType, @InServiceDate,@LeaseAnnualMiles,@ExtendedWarrantyName,@ExtendedWarrantyTerm, @ExtendedWarrantyLimitMiles,@LicensePlateNumber,@ModelDescriptionOfCarSold, @VehicleClassification,@ModelNumberOfCarSold,@ExtendedWarrantyFlag, @TrimLevel,@SubTrimLevel,@BodyDescription,@BodyDoorCount, @TransmissionDesc,@EngineDesc,@TypeCode,@Weight,@LeaseEstimatedMiles );END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- ******************************************************Insert into SALES_FEE Table*************************************************************--================================================================================================================================================BEGIN TRYif ((@Fee_1_Name is not null) or( @Fee_1_Fee is not null) or (@Fee_1_Commission is not null))BEGIN --1INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_1_Name, @Fee_1_Fee, @Fee_1_Commission ); END --1If ((@Fee_2_Name is not null) or (@Fee_2_Fee is not null) or (@Fee_2_Commission is not null))BEGIN --2INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_2_Name, @Fee_2_Fee, @Fee_2_Commission );END --2 If ((@Fee_3_Name is not null) or (@Fee_3_Fee is not null) or (@Fee_3_Commission is not null))BEGIN --3INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_3_Name, @Fee_3_Fee, @Fee_3_Commission );END --3 If ((@Fee_4_Name is not null) or (@Fee_3_Fee is not null) or (@Fee_4_Commission is not null))BEGIN --4INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_4_Name, @Fee_4_Fee, @Fee_4_Commission );END --4 If ((@Fee_5_Name is not null) or (@Fee_5_Fee is not null) or (@Fee_5_Commission is not null))BEGIN --5INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_5_Name, @Fee_5_Fee, @Fee_5_Commission );END --5If ((@Fee_6_Name is not null) or (@Fee_6_Fee is not null) or (@Fee_6_Commission is not null))BEGIN --6INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_6_Name, @Fee_6_Fee, @Fee_6_Commission );END --6 If ((@Fee_7_Name is not null) or (@Fee_7_Fee is not null) or (@Fee_7_Commission is not null))BEGIN --7INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_7_Name, @Fee_7_Fee, @Fee_7_Commission );END --7If ((@Fee_8_Name is not null) or (@Fee_8_Fee is not null) or (@Fee_8_Commission is not null))BEGIN --8INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_8_Name, @Fee_8_Fee, @Fee_8_Commission );END --8 If ((@Fee_9_Name is not null) or (@Fee_9_Fee is not null) or (@Fee_9_Commission is not null))BEGIN --9INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_9_Name, @Fee_9_Fee, @Fee_9_Commission );END --9 If ((@Fee_10_Name is not null) or (@Fee_10_Fee is not null) or (@Fee_10_Commission is not null))BEGIN --10INSERT INTO SALES_FEE ( SalesID , Fee_Name , Fee_Fee , Fee_Commission ) VALUES ( @SalesID, @Fee_10_Name, @Fee_10_Fee, @Fee_10_Commission );END --10 END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- *********************************************************Insert into SALES_HR Table*********************************************************--================================================================================================================================================BEGIN TRYIF ((@Salesman_1_Number is not null) or (@Salesman_1_Name is not null))BEGIN --1INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_1_Number, @Salesman_1_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --1IF ((@Salesman_2_Number is not null) or (@Salesman_2_Name is not null))BEGIN --2INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_2_Number, @Salesman_2_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --2IF ((@Salesman_3_Number is not null) or (@Salesman_3_Name is not null))BEGIN --3INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_3_Number, @Salesman_3_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --3IF ((@Salesman_4_Number is not null) or (@Salesman_4_Name is not null))BEGIN --4INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_4_Number, @Salesman_4_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --4IF ((@Salesman_5_Number is not null) or (@Salesman_5_Name is not null))BEGIN --5INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_5_Number, @Salesman_5_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --5IF ((@Salesman_6_Number is not null) or (@Salesman_6_Name is not null))BEGIN --6INSERT INTO SALES_HR ( SalesID , Salesman_Number , Salesman_Name , ClosingManagerName , ClosingManagerNumber , F_AND_I_ManagerNumber , F_AND_I_ManagerName , SalesManagerNumber , SalesManagerName ) VALUES ( @SalesID , @Salesman_6_Number, @Salesman_6_Name, @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName );END --6END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- *************************************************Insert into SALES_COBUYER Table***************************************************************--================================================================================================================================================BEGIN TRYINSERT INTO SALES_COBUYER ( SalesID , CoBuyerName , CoBuyerFirstName , CoBuyerLastName , CoBuyerAddress , CoBuyerCity , CoBuyerState , CoBuyerZip , CoBuyerCounty , CoBuyerHomePhone , CoBuyerWorkPhone , CoBuyerBirthDate , CoBuyerCustNum , CoBuyerCell , CoBuyerEmail , CoBuyerSalutation , CoBuyerMiddleName , CoBuyerCountry , CoBuyerAddress2 , CoBuyerOptOut , CoBuyerOccupation , CoBuyerEmployer , CoBuyerPhoneBlock , CoBuyerMailBlock , CoBuyerEmailBlock ) VALUES ( @SalesID,@CoBuyerName,@CoBuyerFirstName,@CoBuyerLastName,@CoBuyerAddress, @CoBuyerCity,@CoBuyerState,@CoBuyerZip,@CoBuyerCounty,@CoBuyerHomePhone, @CoBuyerWorkPhone,@CoBuyerBirthDate,@CoBuyerCustNum,@CoBuyerCell, @CoBuyerEmail,@CoBuyerSalutation,@CoBuyerMiddleName,@CoBuyerCountry, @CoBuyerAddress2,@CoBuyerOptOut,@CoBuyerOccupation,@CoBuyerEmployer, @CoBuyerPhoneBlock,@CoBuyerMailBlock,@CoBuyerEmailBlock );END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- ***********************************************Insert into SALES_CUSTOMER Table**************************************************************--================================================================================================================================================BEGIN TRYINSERT INTO SALES_CUSTOMER ( SalesID , IndividualBusinessFlag , PhoneBlock , EmailBlock , CustomerName , CustomerFirstName , CustomerLastName , CustomerAddress , CustomerCity , CustomerState , CustomerZip , CustomerCounty , CustomerHomePhone , CustomerWorkPhone , CustomerCellPhone , CustomerPagerPhone , CustomerEmail , CustomerBirthDate , MailBlock , CustomerSuffix , CustomerSalutation , CustomerAddress2 , CustomerMiddleName , GlobalOptOut , InsuranceName , InsuranceAgentName , InsuranceAddress , InsuranceCity , InsuranceState , InsuranceZip , InsurancePhone , InsurancePolicyNumber , InsuranceEffectiveDate , InsuranceExpirationDate , InsuranceCompensationDeduction , Country , Occupation , Employer , CASS_STD_LINE1 , CASS_STD_LINE2 , CASS_STD_CITY , CASS_STD_STATE , CASS_STD_ZIP , CASS_STD_ZIP4 , CASS_STD_DPBC , CASS_STD_CHKDGT , CASS_STD_CART , CASS_STD_LOT , CASS_STD_LOTORD , CASS_STD_URB , CASS_STD_FIPS , CASS_STD_EWS , CASS_STD_LACS , CASS_STD_ZIPMOV , CASS_STD_Z4LOM , CASS_STD_NDIAPT , CASS_STD_NDIRR , CASS_STD_LACSRT , CASS_STD_ERROR_CD , NCOA_AC_ID , NCOA_COA_ADDSRC , NCOA_COA_MATCH , NCOA_COA_MOVTYP , NCOA_COA_DATE , NCOA_COA_DELCD , NCOA_COA_RTYPE , NCOA_COA_RTNCD , NCOA_COA_LINE1 , NCOA_COA_LINE2 , NCOA_COA_CITY , NCOA_COA_STATE , NCOA_COA_ZIP , NCOA_COA_ZIP4 , NCOA_COA_DPBC , NCOA_COA_CHKDGT , NCOA_COA_CART , NCOA_COA_LOT , NCOA_COA_LOTORD , NCOA_COA_URB , NCOA_COA_Z4LOM , NCOA_COA_ACTION , NCOA_COA_QNAME , NCOA_DPV_AA , NCOA_DPV_A1 , NCOA_DPV_BB , NCOA_DPV_CC , NCOA_DPV_M1 , NCOA_DPV_M3 , NCOA_DPV_N1 , NCOA_DPV_P1 , NCOA_DPV_P3 , NCOA_DPV_RR , NCOA_DPV_R1 , NCOA_DPV_STATUS , NCOA_DPV_F1 , NCOA_DPV_G1 , NCOA_DPV_U1 ) VALUES ( @SalesID , @IndividualBusinessFlag , @PhoneBlock , @EmailBlock , @CustomerName , @CustomerFirstName , @CustomerLastName , @CustomerAddress , @CustomerCity , @CustomerState , @CustomerZip , @CustomerCounty , @CustomerHomePhone , @CustomerWorkPhone , @CustomerCellPhone , @CustomerPagerPhone , @CustomerEmail , @CustomerBirthDate , @MailBlock , @CustomerSuffix , @CustomerSalutation , @CustomerAddress2 , @CustomerMiddleName , @GlobalOptOut , @InsuranceName , @InsuranceAgentName , @InsuranceAddress , @InsuranceCity , @InsuranceState , @InsuranceZip , @InsurancePhone , @InsurancePolicyNumber , @InsuranceEffectiveDate , @InsuranceExpirationDate , @InsuranceCompensationDeduction , @Country , @Occupation , @Employer , @CASS_STD_LINE1 , @CASS_STD_LINE2 , @CASS_STD_CITY , @CASS_STD_STATE , @CASS_STD_ZIP , @CASS_STD_ZIP4 , @CASS_STD_DPBC , @CASS_STD_CHKDGT , @CASS_STD_CART , @CASS_STD_LOT , @CASS_STD_LOTORD , @CASS_STD_URB , @CASS_STD_FIPS , @CASS_STD_EWS , @CASS_STD_LACS , @CASS_STD_ZIPMOV , @CASS_STD_Z4LOM , @CASS_STD_NDIAPT , @CASS_STD_NDIRR , @CASS_STD_LACSRT , @CASS_STD_ERROR_CD , @NCOA_AC_ID , @NCOA_COA_ADDSRC , @NCOA_COA_MATCH , @NCOA_COA_MOVTYP , @NCOA_COA_DATE , @NCOA_COA_DELCD , @NCOA_COA_RTYPE , @NCOA_COA_RTNCD , @NCOA_COA_LINE1 , @NCOA_COA_LINE2 , @NCOA_COA_CITY , @NCOA_COA_STATE , @NCOA_COA_ZIP , @NCOA_COA_ZIP4 , @NCOA_COA_DPBC , @NCOA_COA_CHKDGT , @NCOA_COA_CART , @NCOA_COA_LOT , @NCOA_COA_LOTORD , @NCOA_COA_URB , @NCOA_COA_Z4LOM , @NCOA_COA_ACTION , @NCOA_COA_QNAME , @NCOA_DPV_AA , @NCOA_DPV_A1 , @NCOA_DPV_BB , @NCOA_DPV_CC , @NCOA_DPV_M1 , @NCOA_DPV_M3 , @NCOA_DPV_N1 , @NCOA_DPV_P1 , @NCOA_DPV_P3 , @NCOA_DPV_RR , @NCOA_DPV_R1 , @NCOA_DPV_STATUS , @NCOA_DPV_F1 , @NCOA_DPV_G1 , @NCOA_DPV_U1 );END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- ***************************************************Insert into SALES_AMOUNT Table************************************************************--================================================================================================================================================BEGIN TRYINSERT INTO SALES_AMOUNT ( SalesID , HoldBackAmount , ExtendedWarrantyDollar , ExtendedWarrantyProfit , FrontGross , BackGross , MileageRate , APRRate , ResidualAmount , LicenseFee , RegistrationFee , TotalTax , Cost , InvoiceAmount , FinanceCharge , TotalPickupPayment , TotalAccessories , TotalDriveOffAmount , SalesmanCommission , GrossProfitSale , FinanceReserve , CreditLifePremium , CreditLifeCommision , TotalInsuranceReserve , BalloonAmount , CashPrice , AmountFinanced , TotalOfPayments , MSRP , DownPayment , SecurityDesposit , Rebate , Term , RetailPayment , LeasePayment , LeaseNetCapCost , LeaseTotalCapReduction , APRRate2 , APRRate3 , GAPPremium , LeaseTerm , CashDeposit , AHPremium , LeaseRate , Incentives , StateDMVTotFee , BuyRate , DMVAmount , CreditLifePrem , CreditLifeRes , AHRes , Salesman2Commission , ListPrice , NetTradeAmount , APRRate4 , Term2 , SecurityDeposit2 , DownPayment2 , TotalOfPayments2 , BasePayment , JournalSaleAmount ) VALUES ( @SalesID,@HoldBackAmount,@ExtendedWarrantyDollar,@ExtendedWarrantyProfit, @FrontGross,@BackGross,@MileageRate,@APRRate,@ResidualAmount,@LicenseFee, @RegistrationFee,@TotalTax,@Cost,@InvoiceAmount,@FinanceCharge, @TotalPickupPayment,@TotalAccessories,@TotalDriveOffAmount,@SalesmanCommission, @GrossProfitSale,@FinanceReserve,@CreditLifePremium,@CreditLifeCommision, @TotalInsuranceReserve,@BalloonAmount,@CashPrice,@AmountFinanced,@TotalOfPayments, @MSRP,@DownPayment,@SecurityDesposit,@Rebate,@Term,@RetailPayment,@LeasePayment, @LeaseNetCapCost,@LeaseTotalCapReduction,@APRRate2,@APRRate3,@GAPPremium, @LeaseTerm,@CashDeposit,@AHPremium,@LeaseRate,@Incentives,@StateDMVTotFee, @BuyRate,@DMVAmount,@CreditLifePrem,@CreditLifeRes,@AHRes,@Salesman2Commission, @ListPrice,@NetTradeAmount,@APRRate4,@Term2,@SecurityDeposit2, @DownPayment2,@TotalOfPayments2,@BasePayment,@JournalSaleAmount );END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- *****************************************************Insert into SALES_BANKINFO Table*********************************************************--================================================================================================================================================BEGIN TRYINSERT INTO SALES_BANKINFO ( SalesID , SLCT2 , DealDateOffset , DealerSelect , DealStatus , DealType , SaleType , BankCode , BankName , PaymentType , BankAddress , BankCity , BankState , BankZip , AFTReserve , Language , ROSNumber ) VALUES ( @SalesID,@SLCT2,@DealDateOffset,@DealerSelect,@DealStatus,@DealType, @SaleType,@BankCode,@BankName,@PaymentType,@BankAddress,@BankCity, @BankState,@BankZip,@AFTReserve,@Language,@ROSNumber );END TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH--================================================================================================================================================-- *****************************************************Insert into SALES_DATE Table*********************************************************--================================================================================================================================================BEGIN TRYBEGIN -- 1Insert into SALES_DATE ( SalesID , AccountingDate , InventoryDate , StatusDate , LastInstallmentDate , RetailFirstPayDate , LeaseFirstPayDate , DayToFirstPayment , EntryDate , DealBookDate , RealBookDate ) VALUES ( @SalesID, Case when ISDATE(@AccountingDate )=1 Then @AccountingDate else NULL End , Case when ISDATE(@InventoryDate)=1 Then @InventoryDate else NULL End , Case when ISDATE(@StatusDate)=1 Then @StatusDate else NULL End , Case when ISDATE(@LastInstallmentDate)=1 Then @LastInstallmentDate else NULL End , Case when ISDATE(@RetailFirstPayDate)=1 Then @RetailFirstPayDate else NULL End , Case when ISDATE(@LeaseFirstPayDate)=1 Then @LeaseFirstPayDate else NULL End , @DayToFirstPayment , Case when ISDATE(@EntryDate)=1 Then @EntryDate else NULL End , Case when ISDATE(@DealBookDate)=1 Then @DealBookDate else NULL End , Case when ISDATE(@RealBookDate)=1 Then @RealBookDate else NULL End );ENDEND TRYBEGIN CATCHSELECT @errornumber = ERROR_NUMBER() ,@errorseverity = ERROR_SEVERITY() ,@errorstate = ERROR_STATE() ,@errorprocedure = ERROR_PROCEDURE() ,@errorline = ERROR_LINE() ,@errormessage = ERROR_MESSAGE();:r "C:\Users\ISLLCdba\Desktop\FINAL CROP FOR BLACKBOOK\SALES\LOG_SALES_ERROR.sql" END CATCH---================================================================================================================================================-- ***************************************************Move cursor to Next record ***************************************************************--================================================================================================================================================--:r C:\Clients\BlackBook\BlackBookMarketing\Bharath\FETCH_SALES.sqlFETCH NEXT FROM Sales_Cursor INTO @FileType , @ACDealerID , @ClientDealerID , @DMSType , @DealNumber , @CustomerNumber , @CustomerName , @CustomerFirstName , @CustomerLastName , @CustomerAddress , @CustomerCity , @CustomerState , @CustomerZip , @CustomerCounty , @CustomerHomePhone , @CustomerWorkPhone , @CustomerCellPhone , @CustomerPagerPhone , @CustomerEmail , @CustomerBirthDate , @MailBlock , @CoBuyerName , @CoBuyerFirstName , @CoBuyerLastName , @CoBuyerAddress , @CoBuyerCity , @CoBuyerState , @CoBuyerZip , @CoBuyerCounty , @CoBuyerHomePhone , @CoBuyerWorkPhone , @CoBuyerBirthDate , @Salesman_1_Number , @Salesman_1_Name , @Salesman_2_Number , @Salesman_2_Name , @ClosingManagerName , @ClosingManagerNumber , @F_AND_I_ManagerNumber , @F_AND_I_ManagerName , @SalesManagerNumber , @SalesManagerName , @EntryDate , @DealBookDate , @VehicleYear , @VehicleMake , @VehicleModel , @VehicleStockNumber , @VehicleVIN , @VehicleExteriorColor , @VehicleInteriorColor , @VehicleMileage , @VehicleType , @InServiceDate , @HoldBackAmount , @DealType , @SaleType , @BankCode , @BankName , @SalesmanCommission , @GrossProfitSale , @FinanceReserve , @CreditLifePremium , @CreditLifeCommision , @TotalInsuranceReserve , @BalloonAmount , @CashPrice , @AmountFinanced , @TotalOfPayments , @MSRP , @DownPayment , @SecurityDesposit , @Rebate , @Term , @RetailPayment , @PaymentType , @RetailFirstPayDate , @LeaseFirstPayDate , @DayToFirstPayment , @LeaseAnnualMiles , @MileageRate , @APRRate , @ResidualAmount , @LicenseFee , @RegistrationFee , @TotalTax , @ExtendedWarrantyName , @ExtendedWarrantyTerm , @ExtendedWarrantyLimitMiles , @ExtendedWarrantyDollar , @ExtendedWarrantyProfit , @FrontGross , @BackGross , @TradeIn_1_VIN , @TradeIn_2_VIN , @TradeIn_1_Make , @TradeIn_2_Make , @TradeIn_1_Model , @TradeIn_2_Model , @TradeIn_1_ExteriorColor , @TradeIn_2_ExteriorColor , @TradeIn_1_Year , @TradeIn_2_Year , @TradeIn_1_Mileage , @TradeIn_2_Mileage , @TradeIn_1_Gross , @TradeIn_2_Gross , @TradeIn_1_Payoff , @TradeIn_2_Payoff , @TradeIn_1_ACV , @TradeIn_2_ACV , @Fee_1_Name , @Fee_1_Fee , @Fee_1_Commission , @Fee_2_Name , @Fee_2_Fee , @Fee_2_Commission , @Fee_3_Name , @Fee_3_Fee , @Fee_3_Commission , @Fee_4_Name , @Fee_4_Fee , @Fee_4_Commission , @Fee_5_Name , @Fee_5_Fee , @Fee_5_Commission , @Fee_6_Name , @Fee_6_Fee , @Fee_6_Commission , @Fee_7_Name , @Fee_7_Fee , @Fee_7_Commission , @Fee_8_Name , @Fee_8_Fee , @Fee_8_Commission , @Fee_9_Name , @Fee_9_Fee , @Fee_9_Commission , @Fee_10_Name , @Fee_10_Fee , @Fee_10_Commission , @ContractDate , @InsuranceName , @InsuranceAgentName , @InsuranceAddress , @InsuranceCity , @InsuranceState , @InsuranceZip , @InsurancePhone , @InsurancePolicyNumber , @InsuranceEffectiveDate , @InsuranceExpirationDate , @InsuranceCompensationDeduction , @TradeIn_1_InteriorColor , @TradeIn_2_InteriorColor , @PhoneBlock , @LicensePlateNumber , @Cost , @InvoiceAmount , @FinanceCharge , @TotalPickupPayment , @TotalAccessories , @TotalDriveOffAmount , @EmailBlock , @ModelDescriptionOfCarSold , @VehicleClassification , @ModelNumberOfCarSold , @GAPPremium , @LastInstallmentDate , @CashDeposit , @AHPremium , @LeaseRate , @DealerSelect , @LeasePayment , @LeaseNetCapCost , @LeaseTotalCapReduction , @DealStatus , @CustomerSuffix , @CustomerSalutation , @CustomerAddress2 , @CustomerMiddleName , @GlobalOptOut , @LeaseTerm , @ExtendedWarrantyFlag , @Salesman_3_Number , @Salesman_3_Name , @Salesman_4_Number , @Salesman_4_Name , @Salesman_5_Number , @Salesman_5_Name , @Salesman_6_Number , @Salesman_6_Name , @APRRate2 , @APRRate3 , @APRRate4 , @Term2 , @SecurityDeposit2 , @DownPayment2 , @TotalOfPayments2 , @BasePayment , @JournalSaleAmount , @IndividualBusinessFlag , @InventoryDate , @StatusDate , @ListPrice , @NetTradeAmount , @TrimLevel , @SubTrimLevel , @BodyDescription , @BodyDoorCount , @TransmissionDesc , @EngineDesc , @TypeCode , @SLCT2 , @DealDateOffset , @AccountingDate , @CoBuyerCustNum , @CoBuyerCell , @CoBuyerEmail , @CoBuyerSalutation , @CoBuyerPhoneBlock , @CoBuyerMailBlock , @CoBuyerEmailBlock , @RealBookDate , @CoBuyerMiddleName , @CoBuyerCountry , @CoBuyerAddress2 , @CoBuyerOptOut , @CoBuyerOccupation , @CoBuyerEmployer , @Country , @Occupation , @Employer , @Salesman2Commission , @BankAddress , @BankCity , @BankState , @BankZip , @LeaseEstimatedMiles , @AFTReserve , @CreditLifePrem , @CreditLifeRes , @AHRes , @Language , @BuyRate , @DMVAmount , @Weight , @StateDMVTotFee , @ROSNumber , @Incentives , @CASS_STD_LINE1 , @CASS_STD_LINE2 , @CASS_STD_CITY , @CASS_STD_STATE , @CASS_STD_ZIP , @CASS_STD_ZIP4 , @CASS_STD_DPBC , @CASS_STD_CHKDGT , @CASS_STD_CART , @CASS_STD_LOT , @CASS_STD_LOTORD , @CASS_STD_URB , @CASS_STD_FIPS , @CASS_STD_EWS , @CASS_STD_LACS , @CASS_STD_ZIPMOV , @CASS_STD_Z4LOM , @CASS_STD_NDIAPT , @CASS_STD_NDIRR , @CASS_STD_LACSRT , @CASS_STD_ERROR_CD , @NCOA_AC_ID , @NCOA_COA_ADDSRC , @NCOA_COA_MATCH , @NCOA_COA_MOVTYP , @NCOA_COA_DATE , @NCOA_COA_DELCD , @NCOA_COA_RTYPE , @NCOA_COA_RTNCD , @NCOA_COA_LINE1 , @NCOA_COA_LINE2 , @NCOA_COA_CITY , @NCOA_COA_STATE , @NCOA_COA_ZIP , @NCOA_COA_ZIP4 , @NCOA_COA_DPBC , @NCOA_COA_CHKDGT , @NCOA_COA_CART , @NCOA_COA_LOT , @NCOA_COA_LOTORD , @NCOA_COA_URB , @NCOA_COA_Z4LOM , @NCOA_COA_ACTION , @NCOA_COA_QNAME , @NCOA_DPV_AA , @NCOA_DPV_A1 , @NCOA_DPV_BB , @NCOA_DPV_CC , @NCOA_DPV_M1 , @NCOA_DPV_M3 , @NCOA_DPV_N1 , @NCOA_DPV_P1 , @NCOA_DPV_P3 , @NCOA_DPV_RR , @NCOA_DPV_R1 , @NCOA_DPV_STATUS , @NCOA_DPV_F1 , @NCOA_DPV_G1 , @NCOA_DPV_U1;END CLOSE Sales_Cursor;DEALLOCATE Sales_Cursor;GOSET ANSI_PADDING OFFGO-- ===============================================================================================================================================-- *************************************************** END OF FLATFILE_SALES TABLE ***************************************************************--================================================================================================================================================ |
|
In Log Error Table i Need to know in which child t