October 19, 2014 at 4:16 am
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory
Parent Table name is INVENTORY
Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE,
Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,
its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
Please help me to write the query i have attached the Full stored procedure Script
-- ============================================================================================================================================
-- STORED PROCEDURE FOR FLATFILE_INVENTORY
-- ==============================================================================================================================================
USE [IconicMarketing]
---=========================================================SALES_CURSUR========================================================================
--USE IconicMarketing
--GO
DECLARE
@FileType varchar(50) ,
@ACDealerID varchar(50) ,
@ClientDealerID varchar(50) ,
@DMSType varchar(50) ,
@StockNumber varchar(50) ,
@InventoryDate datetime ,
@StockType varchar(100) ,
@DMSStatus varchar(50) ,
@InvoicePrice numeric(18, 2) ,
@CostPack varchar(50) ,
@SalesCost numeric(18, 2) ,
@HoldbackAmount numeric(18, 2) ,
@ListPrice numeric(18, 2) ,
@MSRP varchar(max) ,
@LotLocation varchar(50) ,
@TagLine varchar(max) ,
@Certification varchar(max) ,
@CertificationNumber varchar(max) ,
@VehicleVIN varchar(50) ,
@VehicleYear bigint ,
@VehicleMake varchar(50) ,
@VehicleModel varchar(50) ,
@VehicleModelCode varchar(50) ,
@VehicleTrim varchar(50) ,
@VehicleSubTrimLevel varchar(max) ,
@Classification varchar(max) ,
@TypeCode varchar(100) ,
@VehicleMileage bigint ,
@EngineCylinderCount bigint ,
@TransmissionType varchar(50) ,
@VehicleExteriorColor varchar(50) ,
@VehicleInteriorColor varchar(50) ,
@CreatedDate datetime ,
@LastModifiedDate datetime ,
@ModifiedFlag varchar(max) ,
@InteriorColorCode varchar(50) ,
@ExteriorColorCode varchar(50) ,
@PackageCode varchar(50) ,
@CodedCost varchar(50) ,
@Air varchar(100) ,
@OrderType varchar(max) ,
@AgeDays bigint ,
@OutstandingRO varchar(50) ,
@DlrAccessoryRetail varchar(50) ,
@DlrAccessoryCost varchar(max) ,
@DlrAccessoryDesc varchar(max) ,
@ModelDesc varchar(50) ,
@Memo1 varchar(1000) ,
@Memo2 varchar(max) ,
@Weight varchar(max) ,
@FloorPlan numeric(18, 2) ,
@Purchaser varchar(max) ,
@PurchasedFrom varchar(max) ,
@InternetPrice varchar(50) ,
@InventoryAcctDollar numeric(18, 2) ,
@VehicleType varchar(50) ,
@DealerAccessoryCode varchar(50) ,
@AllInventoryAcctDollar numeric(18, 2) ,
@BestPrice varchar(50) ,
@instock bigint ,
@AccountingMake varchar(50) ,
@GasDiesel varchar(max) ,
@BookValue varchar(10) ,
@FactoryAccessoryDescription varchar(max) ,
@TotalReturn varchar(10) ,
@TotalCost varchar(10) ,
@SS varchar(max) ,
@VehicleBody varchar(max) ,
@StandardEquipment varchar(max) ,
@account varchar(max) ,
@CalculatedPrice varchar(10) ,
@OriginalCost varchar(10) ,
@AccessoryCore varchar(10) ,
@OtherDollar varchar(10) ,
@PrimaryBookValue varchar(10) ,
@AmountDue varchar(10) ,
@LicenseFee varchar(10) ,
@ICompany varchar(max) ,
@InvenAcct varchar(max) ,
@Field23 varchar(max) ,
@Field24 varchar(max) ,
@SalesCode varchar(max) ,
@BaseRetail varchar(10) ,
@BaseInvAmt varchar(10) ,
@CommPrice varchar(10) ,
@Price1 varchar(10) ,
@Price2 varchar(10) ,
@StickerPrice varchar(10) ,
@TotInvAmt varchar(10) ,
@OptRetail varchar(max) ,
@OptInvAmt varchar(10) ,
@OptCost varchar(10) ,
@Options varchar(max) ,
@Category varchar(max) ,
@Description varchar(max) ,
@Engine varchar(max) ,
@ModelType varchar(max) ,
@FTCode varchar(max) ,
@Wholesale varchar(max) ,
@Retail varchar(max) ,
@Draft varchar(max) ,
@myerrorvarchar(500),
@Inventoryid int ,
@errornumber int,
@errorseverity varchar(500),
@errorstate int,
@errorprocedure varchar(500),
@errorline varchar(50),
@errormessage varchar(1000);
DECLARE Inventory_Cursor CURSOR FOR
SELECT * from FLATFILE_INVENTORY;
OPEN Inventory_Cursor
FETCH NEXT FROM Inventory_Cursor
INTO @FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VehicleVIN ;
--================================================================================================================================================
-- ***************************************************** INSERT INTO INVENTORY TABLE********************************************************
--================================================================================================================================================
BEGIN TRY
INSERT INTO INVENTORY
(
IconicDealerID,
StockNumber,
DMSType,
InventoryDate
)
VALUES (@ClientDealerID,@StockNumber,@DMSType,@InventoryDate);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER()
,@errorseverity = ERROR_SEVERITY()
,@errorstate = ERROR_STATE()
,@errorprocedure = ERROR_PROCEDURE()
,@errorline = ERROR_LINE()
,@errormessage = ERROR_MESSAGE();
set @Inventoryid = scope_identity();
PRINT @Inventoryid;
INSERT INTO [dbo].[LOG_INVENTORY_ERROR] (FileType ,
ACDealerID ,
ClientDealerID ,//
DMSType ,
StockNumber ,
InventoryDate ,
StockType ,
DMSStatus ,
InvoicePrice ,
CostPack ,
SalesCost ,
HoldbackAmount ,
ListPrice ,
MSRP ,
LotLocation ,
TagLine ,
Certification ,
CertificationNumber ,
VehicleVIN ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleModelCode ,
VehicleTrim ,
VehicleSubTrimLevel ,
Classification ,
TypeCode ,
VehicleMileage ,
EngineCylinderCount ,
TransmissionType ,
VehicleExteriorColor ,
VehicleInteriorColor ,
CreatedDate ,
LastModifiedDate ,
ModifiedFlag ,
InteriorColorCode ,
ExteriorColorCode ,
PackageCode ,
CodedCost ,
Air ,
OrderType ,
AgeDays ,
OutstandingRO ,
DlrAccessoryRetail ,
DlrAccessoryCost ,
DlrAccessoryDesc ,
ModelDesc ,
Memo1 ,
Memo2 ,
Weight ,
FloorPlan ,
Purchaser ,
PurchasedFrom ,
InternetPrice ,
InventoryAcctDollar ,
VehicleType ,
DealerAccessoryCode ,
AllInventoryAcctDollar ,
BestPrice ,
InStock ,
AccountingMake ,
GasDiesel ,
BookValue ,
FactoryAccessoryDescription ,
TotalReturn ,
TotalCost ,
SS ,
VehicleBody ,
StandardEquipment ,
Account ,
CalculatedPrice ,
OriginalCost ,
AccessoryCore ,
OtherDollar ,
PrimaryBookValue ,
AmountDue ,
LicenseFee ,
ICompany ,
InvenAcct ,
Field23 ,
Field24 ,
SalesCode ,
BaseRetail ,
BaseInvAmt ,
CommPrice ,
Price1 ,
Price2 ,
StickerPrice ,
TotInvAmt ,
OptRetail ,
OptInvAmt ,
OptCost ,
Options ,
Category ,
Description ,
Engine ,
ModelType ,
FTCode ,
Wholesale ,
Retail ,
Draft ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORPROCEDURE ,
ERRORLINE ,
ERRORMESSAGE )
VALUES (
@FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORPROCEDURE ,
@ERRORLINE ,
@errormessage);
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;
--=================================================================Insert into Inventory_Details Table==============================================================
BEGIN TRY
INSERT INTO [INVENTORY_DETAILS]
(
InventoryID,
StockType,
DMSStatus,
LotLocation,
TagLine,
Certification,
CertificationNumber,
CreatedDate,
LastModifiedDate,
ModifiedFlag,
PackageCode,
OrderType,
AgeDays,
OutstandingRO,
Memo1,
Memo2,
Purchaser,
PurchasedFrom,
DealerAccessoryCode,
InStock,
AccountingMake,
SS,
Account,
AccessoryCore,
ICompany,
InvenAcct,
Field23,
Field24,
SalesCode,
Draft,
FTCode
)
VALUES (
@InventoryID,
@StockType,
@DMSStatus,
@LotLocation,
@TagLine,
@Certification,
@CertificationNumber,
@CreatedDate,
@LastModifiedDate,
@ModifiedFlag,
@PackageCode,
@OrderType,
@AgeDays,
@OutstandingRO,
@Memo1,
@Memo2,
@Purchaser,
@PurchasedFrom,
@DealerAccessoryCode,
@AccountingMake,
@SS,
@AccessoryCore,
@ICompany,
@InvenAcct,
@Field23,
@Field24,
@SalesCode,
@Draft,
@FTCode
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER()
,@errorseverity = ERROR_SEVERITY()
,@errorstate = ERROR_STATE()
,@errorprocedure = ERROR_PROCEDURE()
,@errorline = ERROR_LINE()
,@errormessage = ERROR_MESSAGE();
INSERT INTO [dbo].[LOG_INVENTORY_ERROR] (FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
StockNumber ,
InventoryDate ,
StockType ,
DMSStatus ,
InvoicePrice ,
CostPack ,
SalesCost ,
HoldbackAmount ,
ListPrice ,
MSRP ,
LotLocation ,
TagLine ,
Certification ,
CertificationNumber ,
VehicleVIN ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleModelCode ,
VehicleTrim ,
VehicleSubTrimLevel ,
Classification ,
TypeCode ,
VehicleMileage ,
EngineCylinderCount ,
TransmissionType ,
VehicleExteriorColor ,
VehicleInteriorColor ,
CreatedDate ,
LastModifiedDate ,
ModifiedFlag ,
InteriorColorCode ,
ExteriorColorCode ,
PackageCode ,
CodedCost ,
Air ,
OrderType ,
AgeDays ,
OutstandingRO ,
DlrAccessoryRetail ,
DlrAccessoryCost ,
DlrAccessoryDesc ,
ModelDesc ,
Memo1 ,
Memo2 ,
Weight ,
FloorPlan ,
Purchaser ,
PurchasedFrom ,
InternetPrice ,
InventoryAcctDollar ,
VehicleType ,
DealerAccessoryCode ,
AllInventoryAcctDollar ,
BestPrice ,
InStock ,
AccountingMake ,
GasDiesel ,
BookValue ,
FactoryAccessoryDescription ,
TotalReturn ,
TotalCost ,
SS ,
VehicleBody ,
StandardEquipment ,
Account ,
CalculatedPrice ,
OriginalCost ,
AccessoryCore ,
OtherDollar ,
PrimaryBookValue ,
AmountDue ,
LicenseFee ,
ICompany ,
InvenAcct ,
Field23 ,
Field24 ,
SalesCode ,
BaseRetail ,
BaseInvAmt ,
CommPrice ,
Price1 ,
Price2 ,
StickerPrice ,
TotInvAmt ,
OptRetail ,
OptInvAmt ,
OptCost ,
Options ,
Category ,
Description ,
Engine ,
ModelType ,
FTCode ,
Wholesale ,
Retail ,
Draft ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORPROCEDURE ,
ERRORLINE ,
ERRORMESSAGE )
VALUES (
@FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORPROCEDURE ,
@ERRORLINE ,
@errormessage);
END CATCH
-- ==========================================================Insert into Inventory_Amount Table===================================================================
BEGIN TRY
INSERT INTO INVENTORY_AMOUNT
(
InventoryID,
AllInventoryAcctDollar,
OtherDollar,
PrimaryBookValue,
AmountDue,
LicenseFee,
CalculatedPrice,
OriginalCost,
BookValue,
TotalReturn,
TotalCost,
DlrAccessoryRetail,
DlrAccessoryCost,
DlrAccessoryDesc,
InternetPrice,
InventoryAcctDollar,
BestPrice,
Weight,
FloorPlan,
CodedCost,
InvoicePrice,
CostPack,
SalesCost,
HoldbackAmount,
ListPrice,
MSRP,
BaseRetail,
BaseInvAmt,
CommPrice,
Price1,
Price2,
StickerPrice,
TotInvAmt,
OptRetail,
OptInvAmt,
OptCost,
Wholesale,
Retail
)
VALUES (
@InventoryID,
@AllInventoryAcctDollar,
@OtherDollar,
@PrimaryBookValue,
@AmountDue,
@LicenseFee,
@CalculatedPrice,
@OriginalCost,
@BookValue,
@TotalReturn,
@TotalCost,
@DlrAccessoryRetail,
@DlrAccessoryCost,
@DlrAccessoryDesc,
@InternetPrice,
@InventoryAcctDollar,
@BestPrice,
@Weight,
@FloorPlan,
@CodedCost,
@InvoicePrice,
@CostPack,
@SalesCost,
@HoldbackAmount,
@ListPrice,
@MSRP,
@BaseRetail,
@BaseInvAmt,
@CommPrice,
@Price1,
@Price2,
@StickerPrice,
@TotInvAmt,
@OptRetail,
@OptInvAmt,
@OptCost,
@Wholesale,
@Retail
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER()
,@errorseverity = ERROR_SEVERITY()
,@errorstate = ERROR_STATE()
,@errorprocedure = ERROR_PROCEDURE()
,@errorline = ERROR_LINE()
,@errormessage = ERROR_MESSAGE();
INSERT INTO [dbo].[LOG_INVENTORY_ERROR] (FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
StockNumber ,
InventoryDate ,
StockType ,
DMSStatus ,
InvoicePrice ,
CostPack ,
SalesCost ,
HoldbackAmount ,
ListPrice ,
MSRP ,
LotLocation ,
TagLine ,
Certification ,
CertificationNumber ,
VehicleVIN ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleModelCode ,
VehicleTrim ,
VehicleSubTrimLevel ,
Classification ,
TypeCode ,
VehicleMileage ,
EngineCylinderCount ,
TransmissionType ,
VehicleExteriorColor ,
VehicleInteriorColor ,
CreatedDate ,
LastModifiedDate ,
ModifiedFlag ,
InteriorColorCode ,
ExteriorColorCode ,
PackageCode ,
CodedCost ,
Air ,
OrderType ,
AgeDays ,
OutstandingRO ,
DlrAccessoryRetail ,
DlrAccessoryCost ,
DlrAccessoryDesc ,
ModelDesc ,
Memo1 ,
Memo2 ,
Weight ,
FloorPlan ,
Purchaser ,
PurchasedFrom ,
InternetPrice ,
InventoryAcctDollar ,
VehicleType ,
DealerAccessoryCode ,
AllInventoryAcctDollar ,
BestPrice ,
InStock ,
AccountingMake ,
GasDiesel ,
BookValue ,
FactoryAccessoryDescription ,
TotalReturn ,
TotalCost ,
SS ,
VehicleBody ,
StandardEquipment ,
Account ,
CalculatedPrice ,
OriginalCost ,
AccessoryCore ,
OtherDollar ,
PrimaryBookValue ,
AmountDue ,
LicenseFee ,
ICompany ,
InvenAcct ,
Field23 ,
Field24 ,
SalesCode ,
BaseRetail ,
BaseInvAmt ,
CommPrice ,
Price1 ,
Price2 ,
StickerPrice ,
TotInvAmt ,
OptRetail ,
OptInvAmt ,
OptCost ,
Options ,
Category ,
Description ,
Engine ,
ModelType ,
FTCode ,
Wholesale ,
Retail ,
Draft ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORPROCEDURE ,
ERRORLINE ,
ERRORMESSAGE )
VALUES (
@FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORPROCEDURE ,
@ERRORLINE ,
@errormessage);
END CATCH
--===================================================================Insert into Inventory_Vehicle Table========================================================================
BEGIN TRY
INSERT INTO INVENTORY_VEHICLE
(
InventoryID,
InteriorColorCode,
ExteriorColorCode,
Air,
ModelDesc,
VehicleType,
VehicleVIN,
VehicleYear,
VehicleMake,
VehicleModel,
VehicleModelCode,
VehicleTrim,
VehicleSubTrimLevel,
Classification,
TypeCode,
VehicleMileage
)
VALUES (
@InventoryID,
@InteriorColorCode,
@ExteriorColorCode,
@Air,
@ModelDesc,
@VehicleType,
@VehicleVIN,
@VehicleYear,
@VehicleMake,
@VehicleModel,
@VehicleModelCode,
@VehicleTrim,
@VehicleSubTrimLevel,
@Classification,
@TypeCode,
@VehicleMileage
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER()
,@errorseverity = ERROR_SEVERITY()
,@errorstate = ERROR_STATE()
,@errorprocedure = ERROR_PROCEDURE()
,@errorline = ERROR_LINE()
,@errormessage = ERROR_MESSAGE();
INSERT INTO [dbo].[LOG_INVENTORY_ERROR] (FileType ,
ACDealerID ,
ClientDealerID ,
DMSType ,
StockNumber ,
InventoryDate ,
StockType ,
DMSStatus ,
InvoicePrice ,
CostPack ,
SalesCost ,
HoldbackAmount ,
ListPrice ,
MSRP ,
LotLocation ,
TagLine ,
Certification ,
CertificationNumber ,
VehicleVIN ,
VehicleYear ,
VehicleMake ,
VehicleModel ,
VehicleModelCode ,
VehicleTrim ,
VehicleSubTrimLevel ,
Classification ,
TypeCode ,
VehicleMileage ,
EngineCylinderCount ,
TransmissionType ,
VehicleExteriorColor ,
VehicleInteriorColor ,
CreatedDate ,
LastModifiedDate ,
ModifiedFlag ,
InteriorColorCode ,
ExteriorColorCode ,
PackageCode ,
CodedCost ,
Air ,
OrderType ,
AgeDays ,
OutstandingRO ,
DlrAccessoryRetail ,
DlrAccessoryCost ,
DlrAccessoryDesc ,
ModelDesc ,
Memo1 ,
Memo2 ,
Weight ,
FloorPlan ,
Purchaser ,
PurchasedFrom ,
InternetPrice ,
InventoryAcctDollar ,
VehicleType ,
DealerAccessoryCode ,
AllInventoryAcctDollar ,
BestPrice ,
InStock ,
AccountingMake ,
GasDiesel ,
BookValue ,
FactoryAccessoryDescription ,
TotalReturn ,
TotalCost ,
SS ,
VehicleBody ,
StandardEquipment ,
Account ,
CalculatedPrice ,
OriginalCost ,
AccessoryCore ,
OtherDollar ,
PrimaryBookValue ,
AmountDue ,
LicenseFee ,
ICompany ,
InvenAcct ,
Field23 ,
Field24 ,
SalesCode ,
BaseRetail ,
BaseInvAmt ,
CommPrice ,
Price1 ,
Price2 ,
StickerPrice ,
TotInvAmt ,
OptRetail ,
OptInvAmt ,
OptCost ,
Options ,
Category ,
Description ,
Engine ,
ModelType ,
FTCode ,
Wholesale ,
Retail ,
Draft ,
ERRORNUMBER ,
ERRORSEVERITY ,
ERRORSTATE ,
ERRORPROCEDURE ,
ERRORLINE ,
ERRORMESSAGE )
VALUES (
@FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ,
@ERRORNUMBER ,
@ERRORSEVERITY ,
@ERRORSTATE ,
@ERRORPROCEDURE ,
@ERRORLINE ,
@errormessage);
END CATCH
-- Move cursor to Next record
FETCH NEXT FROM Inventory_Cursor
INTO @FileType ,
@ACDealerID ,
@ClientDealerID ,
@DMSType ,
@StockNumber ,
@InventoryDate ,
@StockType ,
@DMSStatus ,
@InvoicePrice ,
@CostPack ,
@SalesCost ,
@HoldbackAmount ,
@ListPrice ,
@MSRP ,
@LotLocation ,
@TagLine ,
@Certification ,
@CertificationNumber ,
@VehicleVIN ,
@VehicleYear ,
@VehicleMake ,
@VehicleModel ,
@VehicleModelCode ,
@VehicleTrim ,
@VehicleSubTrimLevel ,
@Classification ,
@TypeCode ,
@VehicleMileage ,
@EngineCylinderCount ,
@TransmissionType ,
@VehicleExteriorColor ,
@VehicleInteriorColor ,
@CreatedDate ,
@LastModifiedDate ,
@ModifiedFlag ,
@InteriorColorCode ,
@ExteriorColorCode ,
@PackageCode ,
@CodedCost ,
@Air ,
@OrderType ,
@AgeDays ,
@OutstandingRO ,
@DlrAccessoryRetail ,
@DlrAccessoryCost ,
@DlrAccessoryDesc ,
@ModelDesc ,
@Memo1 ,
@Memo2 ,
@Weight ,
@FloorPlan ,
@Purchaser ,
@PurchasedFrom ,
@InternetPrice ,
@InventoryAcctDollar ,
@VehicleType ,
@DealerAccessoryCode ,
@AllInventoryAcctDollar ,
@BestPrice ,
@instock ,
@AccountingMake ,
@GasDiesel ,
@BookValue ,
@FactoryAccessoryDescription ,
@TotalReturn ,
@TotalCost ,
@SS ,
@VehicleBody ,
@StandardEquipment ,
@account ,
@CalculatedPrice ,
@OriginalCost ,
@AccessoryCore ,
@OtherDollar ,
@PrimaryBookValue ,
@AmountDue ,
@LicenseFee ,
@ICompany ,
@InvenAcct ,
@Field23 ,
@Field24 ,
@SalesCode ,
@BaseRetail ,
@BaseInvAmt ,
@CommPrice ,
@Price1 ,
@Price2 ,
@StickerPrice ,
@TotInvAmt ,
@OptRetail ,
@OptInvAmt ,
@OptCost ,
@Options ,
@Category ,
@Description ,
@Engine ,
@ModelType ,
@FTCode ,
@Wholesale ,
@Retail ,
@Draft ;
END
CLOSE Inventory_Cursor;
DEALLOCATE Inventory_Cursor;
GO
SET ANSI_PADDING OFF
GO
October 19, 2014 at 4:38 am
Quick suggestion, change this to a merge statement, look into the merge match conditionals "when matched" straight forward to handle this that way.
😎
October 19, 2014 at 6:20 am
Can u pls explain more clear .!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply