Upload SKU
unknown
mysql
2 years ago
62 kB
4
Indexable
Never
DROP PROCEDURE `usp_ci_upload_skus`
GO
CREATE PROCEDURE `usp_ci_upload_skus`(
int_year int,
int_period int,
int_userid int,
int_error int,
int_typeid int
)
BEGIN
declare recordcount integer;
declare lastskusid integer;
declare uniquerecordcount integer;
declare recordcount2 integer;
declare uniquerecordcount2 integer;
declare has_error int default 0;
declare add_data int default 0;
declare int_upload_flag int default 0;
drop temporary table if exists temp_skus;
set lastskusid = (SELECT max(skuid) from skus);
create temporary table temp_skus
(
trans_id int AUTO_INCREMENT NOT NULL,
`INTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`INTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
BrandID varchar(100) default '' null,
`BRAND` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
`BARCODE` varchar(100) DEFAULT '',
`IPC` varchar(100) DEFAULT '',
`VARIANT` varchar(100) DEFAULT '',
`PACK_SIZE_QTY` varchar(100) DEFAULT '',
`PACK_SIZE` varchar(100) DEFAULT '',
`CASE_QTY` varchar(100) DEFAULT '',
`PRICE` varchar(100) DEFAULT '',
`DISTRIBUTOR` varchar(100) DEFAULT '',
`CATEGORY` varchar(100) DEFAULT '',
`FOR_INVENTORY` double DEFAULT NULL,
`FOR_AVAILABILITY` double DEFAULT NULL,
`FOR_ORDER_TAKING` double DEFAULT NULL,
primary key(`trans_id`)
);
insert into temp_skus
(
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
)
select
`INTERNAL_PRODUCT_CODE`,
`INTERNAL_PRODUCT_NAME`,
`BRAND`,
`EXTERNAL_PRODUCT_CODE`,
`EXTERNAL_PRODUCT_NAME`,
`BARCODE`,
'',
`VARIANT`,
`PACK_SIZE_QTY`,
`PACK_SIZE`,
`CASE_QTY`,
`PRICE`,
`DISTRIBUTOR`,
`CATEGORY`,
`FOR_INVENTORY`,
`FOR_AVAILABILITY`,
`FOR_ORDER_TAKING`
from tbl_upload_skus;
if has_error = 0 then
update temp_skus tv, brands b
set tv.BrandID = b.BrandID
where tv.Brand = b.BrandName;
drop temporary table if exists temp_skus_error;
create temporary table temp_skus_error (
trans_id varchar(100) not null,
`INTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`INTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
BrandID varchar(100) default '' null,
`BRAND` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
`BARCODE` varchar(100) DEFAULT '',
`IPC` varchar(100) DEFAULT '',
`VARIANT` varchar(100) DEFAULT '',
`PACK_SIZE_QTY` varchar(100) DEFAULT '',
`PACK_SIZE` varchar(100) DEFAULT '',
`CASE_QTY` varchar(100) DEFAULT '',
`PRICE` varchar(100) DEFAULT '',
`DISTRIBUTOR` varchar(100) DEFAULT '',
`CATEGORY` varchar(100) DEFAULT '',
`FOR_INVENTORY` double DEFAULT NULL,
`FOR_AVAILABILITY` double DEFAULT NULL,
`FOR_ORDER_TAKING` double DEFAULT NULL,
error_code varchar(100) default '' null,
Error_Descriptions varchar(100) default '' null
) ENGINE=InnoDB COLLATE=utf8_general_ci ;
drop table if exists temp_duplicate;
create temporary table temp_duplicate
select
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
from temp_skus
group by
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
having count(1) > 1;
drop temporary table if exists temp_duplicate_INTERNAL_PRODUCT_CODE;
create temporary table temp_duplicate_INTERNAL_PRODUCT_CODE
select
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
from temp_skus
group by INTERNAL_PRODUCT_CODE
having count(1) > 1;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
Error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-1,
'Duplicate INTERNAL PRODUCT CODE'
from temp_skus a
inner join temp_duplicate_INTERNAL_PRODUCT_CODE b
on a.INTERNAL_PRODUCT_CODE = b.INTERNAL_PRODUCT_CODE;
drop temporary table if exists temp_duplicate_EXTERNAL_PRODUCT_CODE;
create temporary table temp_duplicate_EXTERNAL_PRODUCT_CODE
select
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
from temp_skus
group by EXTERNAL_PRODUCT_CODE
having count(1) > 1;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-2,
'Duplicate EXTERNAL PRODUCT CODE'
from temp_skus a
inner join temp_duplicate_EXTERNAL_PRODUCT_CODE b
on a.EXTERNAL_PRODUCT_CODE = b.EXTERNAL_PRODUCT_CODE;
drop temporary table if exists temp_duplicate_INTERNAL_PRODUCT_NAME;
create temporary table temp_duplicate_INTERNAL_PRODUCT_NAME
select
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
from temp_skus
group by INTERNAL_PRODUCT_NAME
having count(1) > 1;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-3,
'Duplicate INTERNAL PRODUCT NAME'
from temp_skus a
inner join temp_duplicate_INTERNAL_PRODUCT_NAME b
on a.INTERNAL_PRODUCT_NAME = b.INTERNAL_PRODUCT_NAME;
drop temporary table if exists temp_duplicate_EXTERNAL_PRODUCT_NAME;
create temporary table temp_duplicate_EXTERNAL_PRODUCT_NAME
select
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
from temp_skus
group by INTERNAL_PRODUCT_NAME
having count(1) > 1;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-4,
'Duplicate EXTERNAL PRODUCT NAME'
from temp_skus a
inner join temp_duplicate_EXTERNAL_PRODUCT_NAME b
on a.EXTERNAL_PRODUCT_NAME = b.EXTERNAL_PRODUCT_NAME;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-5,
'INTERNAL PRODUCT CODE already Exists'
from temp_skus a
inner join skus s on a.INTERNAL_PRODUCT_CODE = s.IPC
where s.SKUName is not null;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-6,
'EXTERNAL PRODUCT CODE already Exists'
from temp_skus a
inner join skus s on a.EXTERNAL_PRODUCT_CODE = s.UPC
where s.SKUName is not null;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-7,
'Brand does not exists in Brands Table'
from temp_skus a
left join brands s on a.Brand = s.BrandName
where s.BrandName is null;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-8,
'Special Characters Found in INTERNAL PRODUCT CODE Column in Excel File'
from temp_skus a
where a.INTERNAL_PRODUCT_CODE REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-9,
'Special Characters Found in INTERNAL PRODUCT NAME Column in Excel File'
from temp_skus a
where a.INTERNAL_PRODUCT_NAME REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-10,
'Special Characters Found in EXTERNAL PRODUCT CODE Column in Excel File'
from temp_skus a
where a.EXTERNAL_PRODUCT_CODE REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-11,
'Special Characters Found in EXTERNAL PRODUCT NAME Column in Excel File'
from temp_skus a
where a.EXTERNAL_PRODUCT_NAME REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-12,
'Special Characters Found in DISTRIBUTOR Column in Excel File'
from temp_skus a
where a.DISTRIBUTOR REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-12,
'Special Characters Found in CATEGORY Column in Excel File'
from temp_skus a
where a.CATEGORY REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-12,
'Special Characters Found in BRAND Column in Excel File'
from temp_skus a
where a.BRAND REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-13,
'Special Characters Found in FOR_INVENTORY Column in Excel File'
from temp_skus a
where a.FOR_INVENTORY REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-14,
'Special Characters Found in FOR_AVAILABILITY Column in Excel File'
from temp_skus a
where a.FOR_AVAILABILITY REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-15,
'Special Characters Found in FOR_ORDER_TAKING Column in Excel File'
from temp_skus a
where a.FOR_ORDER_TAKING REGEXP "[^a-zA-ZñÑ?0-9[.hyphen-minus.][.space.]&@#():\/,.+']";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-16,
'INTERNAL_PRODUCT_CODE is Empty'
from temp_skus a
where a.INTERNAL_PRODUCT_CODE = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-17,
'INTERNAL_PRODUCT_NAME is Empty'
from temp_skus a
where a.INTERNAL_PRODUCT_NAME = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-18,
'EXTERNAL_PRODUCT_CODE is Empty'
from temp_skus a
where a.EXTERNAL_PRODUCT_CODE = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-19,
'EXTERNAL_PRODUCT_NAME is Empty'
from temp_skus a
where a.EXTERNAL_PRODUCT_NAME = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-20,
'DISTRIBUTOR is Empty'
from temp_skus a
where a.DISTRIBUTOR = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-21,
'CATEGORY is Empty'
from temp_skus a
where a.CATEGORY = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-22,
'BRAND is Empty'
from temp_skus a
where a.BRAND = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-23,
'FOR_INVENTORY is Empty'
from temp_skus a
where a.FOR_INVENTORY = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-24,
'FOR_AVAILABILITY is Empty'
from temp_skus a
where a.FOR_AVAILABILITY = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-25,
'FOR_ORDER_TAKING is Empty'
from temp_skus a
where a.FOR_ORDER_TAKING = '';
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-26,
'INTERNAL_PRODUCT_CODE has apostrophe'
from temp_skus a
where a.INTERNAL_PRODUCT_CODE like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-27,
'INTERNAL_PRODUCT_NAME has apostrophe'
from temp_skus a
where a.INTERNAL_PRODUCT_NAME like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-28,
'EXTERNAL_PRODUCT_CODE has apostrophe'
from temp_skus a
where a.EXTERNAL_PRODUCT_CODE like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-29,
'EXTERNAL_PRODUCT_NAME has apostrophe'
from temp_skus a
where a.EXTERNAL_PRODUCT_NAME like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-30,
'DISTRIBUTOR has apostrophe'
from temp_skus a
where a.DISTRIBUTOR like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-31,
'CATEGORY has apostrophe'
from temp_skus a
where a.CATEGORY like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-32,
'BRAND has apostrophe'
from temp_skus a
where a.BRAND like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-33,
'FOR_INVENTORY has apostrophe'
from temp_skus a
where a.FOR_INVENTORY like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-34,
'FOR_AVAILABILITY has apostrophe'
from temp_skus a
where a.FOR_AVAILABILITY like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-35,
'FOR_ORDER_TAKING has apostrophe'
from temp_skus a
where a.FOR_ORDER_TAKING like "%'%";
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-36,
'FOR_INVENTORY tagging only allowed 1 or 0'
from temp_skus a
where a.FOR_INVENTORY > 1 or a.FOR_AVAILABILITY < 0;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-37,
'FOR_AVAILABILITY tagging only allowed 1 or 0'
from temp_skus a
where a.FOR_AVAILABILITY > 1 or a.FOR_AVAILABILITY < 0;
insert into temp_skus_error(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
error_code,
Error_Descriptions
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
-38,
'FOR_ORDER_TAKING tagging only allowed 1 or 0'
from temp_skus a
where a.FOR_ORDER_TAKING > 1 or a.FOR_ORDER_TAKING < 0;
delete from temp_skus where trans_id in (select distinct trans_id from temp_skus_error);
drop temporary table if exists temp_final_skus;
create temporary table temp_final_skus (
trans_id varchar(100) default '' null,
`INTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`INTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
BrandID varchar(100) default '' null,
`BRAND` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_CODE` varchar(100) DEFAULT '',
`EXTERNAL_PRODUCT_NAME` varchar(100) DEFAULT '',
`BARCODE` varchar(100) DEFAULT '',
`IPC` varchar(100) DEFAULT '',
`VARIANT` varchar(100) DEFAULT '',
`PACK_SIZE_QTY` varchar(100) DEFAULT '',
`PACK_SIZE` varchar(100) DEFAULT '',
`CASE_QTY` varchar(100) DEFAULT '',
`PRICE` varchar(100) DEFAULT '',
`DISTRIBUTOR` varchar(100) DEFAULT '',
`CATEGORY` varchar(100) DEFAULT '',
`FOR_INVENTORY` double DEFAULT NULL,
`FOR_AVAILABILITY` double DEFAULT NULL,
`FOR_ORDER_TAKING` double DEFAULT NULL
) ENGINE=InnoDB COLLATE=utf8_general_ci ;
insert into temp_final_skus(
trans_id,
INTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_NAME,
BrandID,
BRAND,
EXTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BARCODE,
IPC,
VARIANT,
PACK_SIZE_QTY,
PACK_SIZE,
CASE_QTY,
PRICE,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING
)
select
a.trans_id,
a.INTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_NAME,
s.BrandID,
a.BRAND,
a.EXTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.BARCODE,
a.IPC,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING
from temp_skus a
left join brands s on a.Brand = s.BrandName
where a.trans_id not in (select distinct trans_id from temp_skus_error);
truncate table tbl_skus;
insert into tbl_skus(
SkuName,
UPC,
IPC,
Description,
VARIANT,
PackSize,
PackSizeUnit,
CaseQty,
Price,
DISTRIBUTOR,
CATEGORY,
BRANDID,
BRAND,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
barcode
)
select
a.INTERNAL_PRODUCT_NAME,
a.EXTERNAL_PRODUCT_CODE,
a.INTERNAL_PRODUCT_CODE,
a.EXTERNAL_PRODUCT_NAME,
a.VARIANT,
a.PACK_SIZE_QTY,
a.PACK_SIZE,
a.CASE_QTY,
a.PRICE,
a.DISTRIBUTOR,
a.CATEGORY,
s.BRANDID,
a.BRAND,
a.FOR_INVENTORY,
a.FOR_AVAILABILITY,
a.FOR_ORDER_TAKING,
a.BARCODE
from temp_final_skus a
left join brands s on a.Brand = s.BrandName;
if exists(select 1 from temp_skus_error)then
set add_data = 1;
else
if exists (select * from tbl_skus)then
insert into skus(
SKUName,
UPC,
IPC,
SKUCode,
Description,
VARIANT,
PackSizeUnit,
PackSize,
CaseQty,
Price,
BrandID,
Brand,
Distributor,
Category,
inventory_display,
availability_display,
order_taking_display,
barcode
)
select SKUName,
UPC,
IPC,
UPC as 'SKUCode',
Description,
VARIANT,
PackSizeUnit,
PackSize,
CaseQty,
Price,
BrandID,
Brand,
distributor,
category,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
barcode
from tbl_skus;
insert into pricelists(EYear, EPeriod, SKUID, Price, PeriodType)
select year(now()), month(now()), t.SKUID, t.Price, 1
from skus t where skuid > COALESCE(lastskusid ,0) ;
end if;
end if;
if exists(select 1 from temp_skus_error)then
if has_error != 2 then
insert into temp_email_upload(employee_id,type_id,typename,eyear,eperiod,error_code,error_descriptions)
select int_userid,int_typeid,'skus',int_year,int_period,3,'The uploader encountered row/s which contain error in file, you can go to Upload Universe Logs page to see the error data.';
set has_error = 3;
end if;
end if;
truncate table skus_data_error;
insert into skus_data_error(
SKUName,
UPC,
IPC,
Description,
Brand,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
Error_Code,
Error_Descriptions
)
select
INTERNAL_PRODUCT_NAME,
EXTERNAL_PRODUCT_CODE,
INTERNAL_PRODUCT_CODE,
EXTERNAL_PRODUCT_NAME,
BRAND,
DISTRIBUTOR,
CATEGORY,
FOR_INVENTORY,
FOR_AVAILABILITY,
FOR_ORDER_TAKING,
Error_code,
concat(group_concat(Error_Descriptions SEPARATOR '. '))
from temp_skus_error
group by trans_id;
end if;
update queue_upload
set status = 1
where wholesalerid = int_typeid and status = 0 order by trans_id limit 1;
delete from queue_upload where wholesalerid = int_typeid and status = 1;
select has_error;
END