Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 In Log Error Table i Need to know in which child t

Author  Topic 

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]
--GO

DECLARE
@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.sql

FETCH 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 = 0
BEGIN
PRINT @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 TRY
BEGIN 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 CATCH

PRINT @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 TRY
IF(@TradeIn_1_VIN is not null)
BEGIN -- 1
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_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 -- 3

END TRY

BEGIN 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 CATCH
--================================================================================================================================================
-- **************************************************Insert into SALES_VEHICLE Table************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT 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 TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- ******************************************************Insert into SALES_FEE Table*************************************************************
--================================================================================================================================================
BEGIN TRY
if ((@Fee_1_Name is not null) or( @Fee_1_Fee is not null) or (@Fee_1_Commission is not null))
BEGIN --1
INSERT INTO SALES_FEE
(
SalesID ,
Fee_Name ,
Fee_Fee ,
Fee_Commission
)
VALUES (
@SalesID,
@Fee_1_Name,
@Fee_1_Fee,
@Fee_1_Commission
);

END --1


If ((@Fee_2_Name is not null) or (@Fee_2_Fee is not null) or (@Fee_2_Commission is not null))
BEGIN --2
INSERT 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 --3
INSERT 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 --4
INSERT 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 --5
INSERT INTO SALES_FEE
(
SalesID ,
Fee_Name ,
Fee_Fee ,
Fee_Commission
)
VALUES (
@SalesID,
@Fee_5_Name,
@Fee_5_Fee,
@Fee_5_Commission
);
END --5

If ((@Fee_6_Name is not null) or (@Fee_6_Fee is not null) or (@Fee_6_Commission is not null))
BEGIN --6
INSERT 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 --7
INSERT INTO SALES_FEE
(
SalesID ,
Fee_Name ,
Fee_Fee ,
Fee_Commission
)
VALUES (
@SalesID,
@Fee_7_Name,
@Fee_7_Fee,
@Fee_7_Commission
);
END --7

If ((@Fee_8_Name is not null) or (@Fee_8_Fee is not null) or (@Fee_8_Commission is not null))
BEGIN --8
INSERT 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 --9
INSERT 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 --10
INSERT INTO SALES_FEE
(
SalesID ,
Fee_Name ,
Fee_Fee ,
Fee_Commission
)
VALUES (
@SalesID,
@Fee_10_Name,
@Fee_10_Fee,
@Fee_10_Commission
);
END --10
END TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- *********************************************************Insert into SALES_HR Table*********************************************************
--================================================================================================================================================
BEGIN TRY
IF ((@Salesman_1_Number is not null) or (@Salesman_1_Name is not null))
BEGIN --1
INSERT 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 --1

IF ((@Salesman_2_Number is not null) or (@Salesman_2_Name is not null))
BEGIN --2
INSERT 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 --2

IF ((@Salesman_3_Number is not null) or (@Salesman_3_Name is not null))
BEGIN --3
INSERT 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 --3

IF ((@Salesman_4_Number is not null) or (@Salesman_4_Name is not null))
BEGIN --4
INSERT 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 --4

IF ((@Salesman_5_Number is not null) or (@Salesman_5_Name is not null))
BEGIN --5
INSERT 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 --5

IF ((@Salesman_6_Number is not null) or (@Salesman_6_Name is not null))
BEGIN --6
INSERT 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 --6
END TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- *************************************************Insert into SALES_COBUYER Table***************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT 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 TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- ***********************************************Insert into SALES_CUSTOMER Table**************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT 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 TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- ***************************************************Insert into SALES_AMOUNT Table************************************************************
--================================================================================================================================================
BEGIN TRY
INSERT 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 TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- *****************************************************Insert into SALES_BANKINFO Table*********************************************************
--================================================================================================================================================
BEGIN TRY
INSERT 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 TRY
BEGIN 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 CATCH
--================================================================================================================================================
-- *****************************************************Insert into SALES_DATE Table*********************************************************
--================================================================================================================================================

BEGIN TRY

BEGIN -- 1
Insert 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
);
END
END TRY

BEGIN 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 CATCH
---================================================================================================================================================
-- ***************************************************Move cursor to Next record ***************************************************************
--================================================================================================================================================


--:r C:\Clients\BlackBook\BlackBookMarketing\Bharath\FETCH_SALES.sql
FETCH 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;
GO

SET ANSI_PADDING OFF
GO

-- ===============================================================================================================================================
-- *************************************************** END OF FLATFILE_SALES TABLE ***************************************************************
--================================================================================================================================================




gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-23 : 11:57:56
This whole thing would be soooooo much simpler as a SSIS package
Go to Top of Page
   

- Advertisement -