August 5, 2018 at 3:10 am
Dear Friends,
I am new to TSQL programming . Need your help in making this SP work. The first test case I am using is 'What happens when the Cursor query returns no records'. Though there is not record returned when by this query ,the SP keeps running without doing anything instead of closing the cursor and exiting. Please advise where the problem is. I've copy pasted the code for the SP below .Thank you in advance.
/****** Object: StoredProcedure [dbo].[ImportInvoices_Validations_CrossQueryCursor] Script Date: 05-Aug-18 12:27:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================================
-- Created By :Mohammed Arshad
-- Created Date : 13-Jul-2018
-- Last Modified Date : 31-Jul-2018
-- Description:This SP does the invoice processing - Import from Orion Staging to JDE
-- =====================================================================================
ALTER PROCEDURE [dbo].[ImportInvoices_Validations_CrossQueryCursor]
WITH EXECUTE AS OWNER
AS
/****** Variable Declarations for [F03B11Z1] ******/
DECLARE
--@intCurrentMonth INT,
--@intCurrentYear INT,
@VJEDUS nchar(10),
@VJEDTY nchar(1),
@VJEDSQ float,
@VJEDTN nchar(22),
@VJEDCT nchar(2),
@VJEDLN float,
@VJEDTS nchar(6),
@VJEDFT nchar(10),
@VJEDDT numeric(18, 0),
@VJEDER nchar(1),
@VJEDDL float,
@VJEDSP nchar(1),
@VJEDTC nchar(1),
@VJEDTR nchar(1),
@VJEDBT nchar(15),
@VJEDGL nchar(1),
@VJEDDH nchar(1),
@VJEDAN float,
@VJDOC float,
@VJDCT nchar(2),
@VJKCO nchar(5),
@VJSFX nchar (3),
@VJAN8 float,
@VJDGJ numeric(18, 0) ,
@VJDIVJ numeric(18, 0),
@VJICUT nchar(2),
@VJICU float,
@VJDICJ numeric(18, 0),
@VJFY float,
@VJCTRY float,
@VJPN float,
@VJCO nchar(5),
@VJGLC nchar(4),
@VJAID nchar(8),
@VJPA8 float,
@VJAN8J float,
@VJPYR float,
@VJPOST nchar(1),
@VJISTR nchar(1),
@VJBALJ nchar(1),
@VJPST nchar(1),
@VJAG float,
@VJAAP float,
@VJADSC float,
@VJADSA float,
@VJATXA float,
@VJATXN float,
@VJSTAM float,
@VJCRRM nchar(1),
@VJCRCD nchar(3),
@VJCRR float,
@VJDMCD nchar(1),
@VJACR float,
@VJFAP float,
@VJCDS float,
@VJCDSA float,
@VJCTXA float,
@VJCTXN float,
@VJCTAM float,
@VJTXA1 nchar(10),
@VJEXR1 nchar(2),
@VJDSVJ numeric(18, 0),
@VJGLBA nchar(8),
@VJAM nchar(1),
@VJAID2 nchar(8),
@VJAM2 nchar(1),
@VJMCU nchar(12),
@VJOBJ nchar(6),
@VJSUB nchar(8),
@VJSBLT nchar(1),
@VJSBL nchar(8),
@VJPTC nchar(3),
@VJDDJ numeric(18, 0),
@VJDDNJ numeric(18, 0),
@VJRDDJ numeric(18, 0),
@VJRDSJ numeric(18, 0),
@VJSMTJ numeric(18, 0),
@VJNBRR nchar(1) ,
@VJRDRL nchar(1),
@VJRMDS float,
@VJCOLL nchar(1),
@VJCORC nchar(2),
@VJAFC nchar(1),
@VJRSCO nchar(2),
@VJCKNU nchar(25),
@VJODOC float,
@VJODCT nchar(2),
@VJOKCO nchar(5),
@VJOSFX nchar(3),
@VJVINV nchar(25),
@VJPO nchar(8),
@VJPDCT nchar(2),
@VJPKCO nchar(5),
@VJDCTO nchar(2),
@VJLNID float,
@VJSDOC float,
@VJSDCT nchar(2),
@VJSKCO nchar(5),
@VJSFXO nchar(3),
@VJVLDT numeric(18, 0),
@VJCMC1 float,
@VJVR01 nchar(25),
@VJUNIT nchar(8),
@VJMCU2 nchar(12),
@VJRMK nchar(30),
@VJALPH nchar(40),
@VJRF nchar(2),
@VJDRF float,
@VJCTL nchar(13),
@VJFNLP nchar(1),
@VJITM float,
@VJU float,
@VJUM nchar(2),
@VJALT6 nchar(1),
@VJRYIN nchar(1),
@VJVDGJ numeric(18, 0),
@VJVRE nchar(3),
@VJRP1 nchar(1),
@VJRP2 nchar(1),
@VJRP3 nchar(1),
@VJAR01 nchar(3),
@VJAR02 nchar(3),
@VJAR03 nchar(3),
@VJAR04 nchar(3),
@VJAR05 nchar(3),
@VJAR06 nchar(3),
@VJAR07 nchar(3),
@VJAR08 nchar(3),
@VJAR09 nchar(3),
@VJAR10 nchar(3),
@VJURC1 nchar(3),
@VJURDT numeric(18, 0),
@VJURAT float,
@VJURAB float,
@VJURRF nchar(15),
@VJTORG nchar(10),
@VJUSER nchar(10),
@VJPID nchar(10),
@VJUPMJ numeric(18, 0),
@VJUPMT float,
@VJJOBN nchar(10),
@VJHCRR float,
@VJHDGJ numeric(18, 0),
@VJDIM float,
@VJDID float,
@VJDIY float,
@VJDI# float,
@VJDGM float,
@VJDGD float,
@VJDGY float,
@VJDG# float,
@VJDICM float,
@VJDICD float,
@VJDICY float,
@VJDIC# float,
@VJDSVM float,
@VJDSVD float,
@VJDSVY float,
@VJDSV# float,
@VJDDM float,
@VJDDD float,
@VJDDY float,
@VJDD# float,
@VJDDNM float,
@VJDDND float,
@VJDDNY float,
@VJDDN# float,
@VJSMTM float,
@VJSMTD float,
@VJSMTY float,
@VJSMT# float,
@VJRDDM float,
@VJRDDD float,
@VJRDDY float,
@VJRDD# float,
@VJRDSM float,
@VJRDSD float,
@VJRDSY float,
@VJRDS# float,
@VJHDGM float,
@VJHDGD float,
@VJHDGY float,
@VJHDG# float,
@VJSHPN float,
@VJDTXS nchar(1),
@VJOMOD nchar(1),
@VJCLMG nchar(10),
@VJCMGR nchar(10),
@VJATAD float,
@VJCTAD float,
@VJNRTA float,
@VJFNRT float,
@VJPRGF nchar(1),
@VJGFL1 nchar(1),
@VJGFL2 nchar(1),
@VJDOCO float,
@VJKCOO nchar(5),
@VJSOTF nchar(1),
@VJDTPB numeric(18, 0),
@VJERDJ numeric(18, 0),
@VJNETST nchar(1),
@VJRMRI nchar(50),
/**************************************************/
/****** Variable Declarations for [F0911Z1] ******/
@VNEDUS nchar(10),
@VNEDTY nchar (1) ,
@VNEDSQ float ,
@VNEDTN nchar (22) ,
@VNEDCT nchar (2) ,
@VNEDLN float ,
@VNEDTS nchar (6) ,
@VNEDFT nchar (10) ,
@VNEDDT numeric (18, 0) ,
@VNEDER nchar (1) ,
@VNEDDL float ,
@VNEDSP nchar (1) ,
@VNEDTC nchar (1) ,
@VNEDTR nchar (1) ,
@VNEDBT nchar (15),
@VNEDGL nchar (1) ,
@VNEDAN float ,
@VNKCO nchar (5) ,
@VNDCT nchar (2) ,
@VNDOC float ,
@VNDGJ numeric (18, 0) ,
@VNJELN float ,
@VNEXTL nchar (2) ,
@VNPOST nchar (1) ,
@VNICU float ,
@VNICUT nchar (2) ,
@VNDICJ numeric (18, 0) ,
@VNDSYJ numeric (18, 0) ,
@VNTICU float ,
@VNCO nchar (5) ,
@VNANI nchar (29) ,
@VNAM nchar (1) ,
@VNAID nchar (8) ,
@VNMCU nchar (12) ,
@VNOBJ nchar (6) ,
@VNSUB nchar (8) ,
@VNSBL nchar (8) ,
@VNSBLT nchar (1) ,
@VNLT nchar (2) ,
@VNPN float ,
@VNCTRY float ,
@VNFY float ,
@VNFQ nchar (4) ,
@VNCRCD nchar (3) ,
@VNCRR float ,
@VNHCRR float ,
@VNHDGJ numeric (18, 0) ,
@VNAA float ,
@VNU float ,
@VNUM nchar (2) ,
@VNGLC nchar (4) ,
@VNRE nchar (1) ,
@VNEXA nchar (30) ,
@VNEXR nchar (30) ,
@VNR1 nchar (8) ,
@VNR2 nchar (8) ,
@VNR3 nchar (8) ,
@VNSFX nchar (3) ,
@VNODOC float ,
@VNODCT nchar (2) ,
@VNOSFX nchar (3) ,
@VNPKCO nchar (5) ,
@VNOKCO nchar (5) ,
@VNPDCT nchar (2) ,
@VNAN8 float ,
@VNCN nchar (8) ,
@VNDKJ numeric (18, 0) ,
@VNDKC numeric (18, 0) ,
@VNASID nchar (25) ,
@VNBRE nchar (1) ,
@VNRCND nchar (1) ,
@VNSUMM nchar (1) ,
@VNPRGE nchar (1) ,
@VNTNN nchar (1) ,
@VNALT1 nchar (1) ,
@VNALT2 nchar (1) ,
@VNALT3 nchar (1) ,
@VNALT4 nchar (1) ,
@VNALT5 nchar (1) ,
@VNALT6 nchar (1) ,
@VNALT7 nchar (1) ,
@VNALT8 nchar (1) ,
@VNALT9 nchar (1) ,
@VNALT0 nchar (1) ,
@VNALTT nchar (1) ,
@VNALTU nchar (1) ,
@VNALTV nchar (1) ,
@VNALTW nchar (1) ,
@VNALTX nchar (1) ,
@VNALTZ nchar (1) ,
@VNDLNA nchar (1) ,
@VNCFF1 nchar (1) ,
@VNCFF2 nchar (1) ,
@VNASM nchar (1) ,
@VNBC nchar (1) ,
@VNVINV nchar (25) ,
@VNIVD numeric (18, 0) ,
@VNWR01 nchar (4) ,
@VNPO nchar (8) ,
@VNPSFX nchar (3) ,
@VNDCTO nchar (2) ,
@VNLNID float ,
@VNWY float ,
@VNWN float ,
@VNFNLP nchar (1) ,
@VNOPSQ float ,
@VNJBCD nchar (6) ,
@VNJBST nchar (4) ,
@VNHMCU nchar (12) ,
@VNDOI float ,
@VNALID nchar (25) ,
@VNALTY nchar (2) ,
@VNDSVJ numeric (18, 0) ,
@VNTORG nchar (10) ,
@VNREG# float ,
@VNPYID float ,
@VNUSER nchar(10) ,
@VNPID nchar(10) ,
@VNJOBN nchar (10) ,
@VNUPMJ numeric (18, 0) ,
@VNUPMT float ,
@VNCRRM nchar (1) ,
@VNACR float ,
@VNDGM float ,
@VNDGD float ,
@VNDGY float ,
@VNDG# float ,
@VNDICM float ,
@VNDICD float ,
@VNDICY float ,
@VNDIC# float ,
@VNDSYM float ,
@VNDSYD float ,
@VNDSYY float ,
@VNDSY# float ,
@VNDKM float ,
@VNDKD float ,
@VNDKY float ,
@VNDK# float ,
@VNDSVM float ,
@VNDSVD float ,
@VNDSVY float ,
@VNDSV# float ,
@VNHDGM float ,
@VNHDGD float ,
@VNHDGY float ,
@VNHDG# float ,
@VNDKCM float ,
@VNDKCD float ,
@VNDKCY float ,
@VNDKC# float ,
@VNIVDM float ,
@VNIVDD float ,
@VNIVDY float ,
@VNIVD# float ,
@VNABR1 nchar (12) ,
@VNABR2 nchar (12) ,
@VNABR3 nchar (12) ,
@VNABR4 nchar (12) ,
@VNABT1 nchar (1) ,
@VNABT2 nchar (1) ,
@VNABT3 nchar (1) ,
@VNABT4 nchar (1) ,
@VNITM float ,
@VNPM01 nchar (1) ,
@VNPM02 nchar (1) ,
@VNPM03 nchar (1) ,
@VNPM04 nchar (1) ,
@VNPM05 nchar (1) ,
@VNPM06 nchar (1) ,
@VNPM07 nchar (1) ,
@VNPM08 nchar (1) ,
@VNPM09 nchar (1) ,
@VNPM10 nchar (1) ,
@VNBCRC nchar (3) ,
@VNEXR1 nchar (2) ,
@VNTXA1 nchar (10) ,
@VNTXITM float,
@VNACTB nchar (10) ,
@VNSTAM float ,
@VNCTAM float ,
@VNAG float ,
@VNAGF float ,
@VNTKTX nchar (1) ,
@VNDLNID float ,
@VNCKNU nchar (25) ,
@VNBUPC nchar (1) ,
@VNAHBU nchar (12) ,
@VNEPGC nchar (3) ,
@VNJPGC nchar (3) ,
@VNRC5 float ,
@VNSFXE float ,
@VNOFM nchar (1),
@VNRMRI nchar (50),
/**************************************************/
@errorcode NVARCHAR(40);
/********* Cursor definition with outer join of [F03B11Z1] WITH [F0911Z1] ********/
DECLARE Invoices1 CURSOR FOR SELECT * FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I LEFT OUTER JOIN [JDEORIONDV].[PRODDTA].[F0911Z1] J ON I.VJEDUS = J.VNEDUS AND I.VJEDTN = J.VNEDTN AND I.VJEDLN = J.VNEDLN AND I.VJEDSP = J.VNEDSP AND I.VJEDBT = J.VNEDBT AND I.VJDOC = J.VNDOC AND I.VJDCT = J.VNDCT AND I.VJKCO = J.VNKCO AND I.VJSFX = J.VNSFX AND I.VJEDSP = 0 WHERE I.VJEDSP = 0;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements. -- Insert statements for procedure here
OPEN Invoices1
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
SET @errorcode = '';
FETCH NEXT FROM Invoices1 INTO
@VJEDUS,@VJEDTY,@VJEDSQ,@VJEDTN,@VJEDCT,@VJEDLN,@VJEDTS,@VJEDFT,@VJEDDT,@VJEDER,@VJEDDL,@VJEDSP,@VJEDTC,@VJEDTR,@VJEDBT,
@VJEDGL,@VJEDDH,@VJEDAN,@VJDOC,@VJDCT,@VJKCO,@VJSFX,@VJAN8,@VJDGJ,@VJDIVJ,@VJICUT,@VJICU,@VJDICJ,@VJFY,@VJCTRY,@VJPN,@VJCO,@VJGLC,@VJAID,
@VJPA8,@VJAN8J,@VJPYR,@VJPOST,@VJISTR,@VJBALJ,@VJPST,@VJAG,@VJAAP,@VJADSC,@VJADSA,@VJATXA,@VJATXN,@VJSTAM,@VJCRRM,@VJCRCD,@VJCRR,@VJDMCD,
@VJACR,@VJFAP,@VJCDS,@VJCDSA,@VJCTXA,@VJCTXN,@VJCTAM,@VJTXA1,@VJEXR1,@VJDSVJ,@VJGLBA,@VJAM,@VJAID2,@VJAM2,@VJMCU,@VJOBJ,@VJSUB,@VJSBLT,
@VJSBL,@VJPTC,@VJDDJ,@VJDDNJ,@VJRDDJ,@VJRDSJ,@VJSMTJ,@VJNBRR,@VJRDRL,@VJRMDS,@VJCOLL,@VJCORC,@VJAFC,@VJRSCO,@VJCKNU,@VJODOC,@VJODCT,
@VJOKCO,@VJOSFX,@VJVINV,@VJPO,@VJPDCT,@VJPKCO,@VJDCTO,@VJLNID,@VJSDOC,@VJSDCT,@VJSKCO,@VJSFXO,@VJVLDT,@VJCMC1,@VJVR01,@VJUNIT,@VJMCU2,
@VJRMK,@VJALPH,@VJRF,@VJDRF,@VJCTL,@VJFNLP,@VJITM,@VJU,@VJUM,@VJALT6,@VJRYIN,@VJVDGJ,@VJVRE,@VJRP1,@VJRP2,@VJRP3,@VJAR01,@VJAR02,@VJAR03,
@VJAR04,@VJAR05,@VJAR06,@VJAR07,@VJAR08,@VJAR09,@VJAR10,@VJURC1,@VJURDT,@VJURAT,@VJURAB,@VJURRF,@VJTORG,@VJUSER,@VJPID,@VJUPMJ,
@VJUPMT,@VJJOBN,@VJHCRR,@VJHDGJ,@VJDIM,@VJDID,@VJDIY,@VJDI#,@VJDGM,@VJDGD,@VJDGY,@VJDG#,@VJDICM,@VJDICD,@VJDICY,@VJDIC#,@VJDSVM,@VJDSVD,
@VJDSVY,@VJDSV#,@VJDDM,@VJDDD,@VJDDY,@VJDD#,@VJDDNM,@VJDDND,@VJDDNY,@VJDDN#,@VJSMTM,@VJSMTD,@VJSMTY,@VJSMT#,@VJRDDM,@VJRDDD,
@VJRDDY,@VJRDD#,@VJRDSM,@VJRDSD,@VJRDSY,@VJRDS#,@VJHDGM,@VJHDGD,@VJHDGY,@VJHDG#,@VJSHPN,@VJDTXS,@VJOMOD,@VJCLMG,@VJCMGR,@VJATAD,
@VJCTAD,@VJNRTA,@VJFNRT,@VJPRGF,@VJGFL1,@VJGFL2,@VJDOCO,@VJKCOO,@VJSOTF,@VJDTPB,@VJERDJ,@VJNETST,@VJRMRI,
@VNEDUS,@VNEDTY,@VNEDSQ,@VNEDTN,@VNEDCT,@VNEDLN,@VNEDTS,@VNEDFT,@VNEDDT,@VNEDER,@VNEDDL,@VNEDSP,@VNEDTC,@VNEDTR,@VNEDBT,@VNEDGL,
@VNEDAN,@VNKCO,@VNDCT,@VNDOC,@VNDGJ,@VNJELN,@VNEXTL,@VNPOST,@VNICU,@VNICUT,@VNDICJ,@VNDSYJ,@VNTICU,@VNCO,@VNANI,@VNAM,@VNAID,@VNMCU,@VNOBJ,
@VNSUB,@VNSBL,@VNSBLT,@VNLT,@VNPN,@VNCTRY,@VNFY,@VNFQ,@VNCRCD,@VNCRR,@VNHCRR,@VNHDGJ,@VNAA,@VNU,@VNUM,@VNGLC,@VNRE,@VNEXA,@VNEXR,@VNR1,@VNR2,
@VNR3,@VNSFX,@VNODOC,@VNODCT,@VNOSFX,@VNPKCO,@VNOKCO,@VNPDCT,@VNAN8,@VNCN,@VNDKJ,@VNDKC,@VNASID,@VNBRE,@VNRCND,@VNSUMM,@VNPRGE,@VNTNN ,@VNALT1,
@VNALT2,@VNALT3,@VNALT4,@VNALT5,@VNALT6,@VNALT7,@VNALT8,@VNALT9,@VNALT0,@VNALTT,@VNALTU,@VNALTV,@VNALTW,@VNALTX,@VNALTZ,@VNDLNA,@VNCFF1,
@VNCFF2,@VNASM,@VNBC,@VNVINV,@VNIVD,@VNWR01,@VNPO,@VNPSFX,@VNDCTO,@VNLNID,@VNWY,@VNWN,@VNFNLP,@VNOPSQ,@VNJBCD,@VNJBST,@VNHMCU,@VNDOI,@VNALID,@VNALTY,
@VNDSVJ,@VNTORG,@VNREG#,@VNPYID,@VNUSER,@VNPID,@VNJOBN,@VNUPMJ,@VNUPMT,@VNCRRM,@VNACR,@VNDGM,@VNDGD,@VNDGY,@VNDG#,@VNDICM,@VNDICD,@VNDICY,@VNDIC#,
@VNDSYM,@VNDSYD,@VNDSYY,@VNDSY#,@VNDKM,@VNDKD,@VNDKY,@VNDK#,@VNDSVM,@VNDSVD,@VNDSVY,@VNDSV#,@VNHDGM,@VNHDGD,@VNHDGY,@VNHDG#,@VNDKCM,@VNDKCD,
@VNDKCY,@VNDKC#,@VNIVDM,@VNIVDD,@VNIVDY,@VNIVD#,@VNABR1,@VNABR2,@VNABR3,@VNABR4,@VNABT1,@VNABT2,@VNABT3,@VNABT4,@VNITM,@VNPM01,@VNPM02,@VNPM03,
@VNPM04,@VNPM05,@VNPM06,@VNPM07,@VNPM08,@VNPM09,@VNPM10,@VNBCRC,@VNEXR1,@VNTXA1,@VNTXITM,@VNACTB,@VNSTAM,@VNCTAM,@VNAG,@VNAGF,@VNTKTX,@VNDLNID,
@VNCKNU,@VNBUPC,@VNAHBU,@VNEPGC,@VNJPGC,@VNRC5,@VNSFXE,@VNOFM,@VNRMRI;
/******** The Code below goes through the validation cycle record-by-record and determines the Errorcode and logs it to the audit table and
performs the desired operation accordingly **********************/
/************** Added by Mohammed Arshad on 12-Jul-2018 ****************/
/* Start of Validations for Float/Numeric Data Types beforing beginning the
Invoice Import(F03B11Z1) into JDE The numeric-field validations throw a message with code E1.
The same is then logged to database schema.
*/
/* Checks if the EDI - Line Number is greater than 7 digits and reports error accordingly*/
IF (@VJEDLN > 9999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the EDI - Detail Lines Processed is greater than 5 digits and reports error accordingly*/
IF (@VJEDDL > 99999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Document (Voucher, Invoice, etc.) is greater than 8 digits and reports error accordingly*/
IF ( @VJDOC > 99999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Address Number is greater than 8 digits and reports error accordingly*/
IF (@VJAN8 > 99999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Fiscal Year is greater than 2 digits and reports error accordingly*/
IF ( @VJFY > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Century is greater than 2 digits and reports error accordingly*/
IF (@VJCTRY > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Period Number is greater than 2 digits and reports error accordingly*/
IF (@VJPN > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Gross Amount is greater than 15 digits and reports error accordingly*/
IF ( @VJAG > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Open Amount is greater than 15 digits and reports error accordingly*/
IF ( @VJAAP > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Discount Available is greater than 15 digits and reports error accordingly*/
IF ( @VJADSC > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Discount Taken is greater than 15 digits and reports error accordingly*/
IF (@VJADSA > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Taxable Amount is greater than 15 digits and reports error accordingly*/
IF (@VJATXA > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Non-Taxable Amount is greater than 15 digits and reports error accordingly*/
IF ( @VJATXN > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks IF the Tax Amount is greater than 15 digits and reports error accordingly*/
IF (@VJSTAM > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks IF the Currency Conversion Rate is greater than 15 digits and reports error accordingly*/
IF (@VJCRR > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the User Reserved Amount is greater than 15 digits and reports error accordingly*/
IF (@VJURAT > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the User Reserved Number is greater than 8 digits and reports error accordingly*/
IF (@VJURAB > 99999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Time Last Updated is greater than 6 digits and reports error accordingly*/
IF (@VJUPMT > 999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Time Last Updated is greater than 6 digits and reports error accordingly*/
IF (@VJUPMT > 999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Time Last Updated is greater than 6 digits and reports error accordingly*/
IF (@VJUPMT > 999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the EDI - Line Number is greater than 7 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNEDLN > 9999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the EDI - Detail Lines Processed is greater than 5 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNEDDL > 99999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Document (Voucher, Invoice, etc.) is greater than 8 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNDOC > 99999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Period Number is greater than 2 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNPN > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Century is greater than 2 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNCTRY > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Fiscal Year is greater than 2 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNFY > 99)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Amount is greater than 15 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNAA > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Units is greater than 15 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNU > 999999999999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Address is greater than 8 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNAN8 > 99999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/* Checks if the Time - Last Updated is greater than 6 digits and reports error accordingly*/
IF (@VNEDSP = 0 AND @VNUPMT > 999999)
BEGIN
PRINT 'E1 - Size of one of the columns exceeds maximum limit!';
SET @errorcode ='E1';
END;
/******** End of E1 Validations for Float/Numeric Data/Types beforing beginning the Invoice Import into ****JDE */
/************** Added by Mohammed Arshad on 12-Jul-2018 ****************/
/* Start of E2 Validations for Col with other value than pre-determined value like 'Orion' / Non-Blank Columns (that should always blank).
This section for validations deals with the Validations for Null value of the column.
When it encounters a column with a non-null value it throws a message with code E2 .
The same is then logged to database schema catalog for Error Handling.
*/
/* Checks if the EDI User ID has other value than Orion */
IF (@VJEDUS <> 'Orion')
BEGIN
PRINT 'E2 - One of the mandatory columns is blank';
SET @errorcode = @errorcode + ',' + 'E2';
END;
--/* Checks if the Record Type IS NOT NULL*/
--IF (@VJEDTY IS NOT NULL)
--PRINT 'E2 - One of the mandatory columns is blank';
-- SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the Record Sequence is NOT NULL*/
--IF (@VJEDSQ IS NOT NULL)
--PRINT 'E2 - One of the mandatory columns is blank';
-- SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the Document Type is NOT NULL*/
--IF ( @VJEDCT IS NULL)
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the EDI - Transaction Set Number is NOT NULL*/
--IF (@VJEDSP = 0 AND @VJEDTS IS NULL )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the EDI - Translation Format is NOT NULL*/
--IF ( @VJEDFT IS NULL )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
/* Checks if the EDI - Translation Date is NOT NULL*/
--IF ( @VJEDDT IS NULL )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the EDI - EDI - Send/Receive Indicator is NOT NULL*/
--IF ( @VJEDER IS NULL )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
--/* Checks if the EDI - Successfully Processed is not non zero*/
--IF ( @VJEDSP <>0)
-- PRINT 'E2 - Should always be 0 !';
--SET @errorcode = @errorcode + ',' + 'E2';
/* Checks if the EDI - Transaction Action is other than A*/
IF ( @VJEDTC <>'A')
BEGIN
PRINT 'E2 - Should always be A !';
SET @errorcode = @errorcode + ',' + 'E2';
END;
--/* Checks if the EDI - Transaction Type is other than I*/
--IF ( @VJEDTC <>'I')
-- PRINT 'E2 - Should always be I !';
--SET @errorcode = @errorcode + ',' + 'E2';
/* Checks if the EDI - Batchfile Discount is other than 1*/
IF (@VJEDDH <>'1')
BEGIN
PRINT 'E2 - Should always be 1 !';
SET @errorcode = @errorcode + ',' + 'E2';
END;
/* Checks if the EDI - Batch Type is NOT NULL*/
IF (@VJEDTR IS NULL )
BEGIN
PRINT 'E2 - One of the mandatory columns is blank';
SET @errorcode = @errorcode + ',' + 'E2';
END;
--/* Checks if the EDI - Batch Number is NOT NULL*/
--IF ( @VJICUT IS NULL )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
/****** End of E2 checks for F03B11Z1 ******/
/****** Start of of E2 checks for F0911Z1******/
/* Checks if the EDI User ID has other value than Orion */
IF (@VNEDUS <> 'Orion' )
BEGIN
PRINT 'E2 - One of the mandatory columns is blank';
SET @errorcode = @errorcode + ',' + 'E2';
END;
--/* Checks if the EDI - EDI - Send/Receive Indicator is NOT NULL*/
--IF (@VNEDSP = 0 )
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
/* Checks if the Document Type is NOT NULL*/
IF (@VNEDCT <> 'I')
BEGIN
PRINT 'E2 - One of the mandatory columns is blank';
SET @errorcode = @errorcode + ',' + 'E2';
END;
/* Checks if the Document Type is NOT NULL*/
IF (@VNEDTR <> 'A')
BEGIN
PRINT 'E2 - One of the mandatory columns is blank';
SET @errorcode = @errorcode + ',' + 'E2';
END;
--/* Checks if the Document Type is NOT NULL*/
--IF (@VNEDSP = 0 AND @VNEDTR <> 'I')
-- PRINT 'E2 - One of the mandatory columns is blank';
--SET @errorcode = @errorcode + ',' + 'E2';
/************ End of E2 checks for F0911Z1******/
/*************** Added by Mohammed Arshad on 14-Jul-2018 *****************/
/*** Start of Validations for category E3.
This section for validations checks if a non-null value exists for these columns.
When it finds non-null value , it throws the E3 message and writes to the error
handler before exiting/moving to other check in the set.
***************************************************************************/
------------ Checks if the Record Type is NOT NULL ------------
IF (@VJEDTY IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the EDI - Record Sequence is NOT NULL ------------
IF (@VJEDSQ IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Document Type is NOT NULL ------------------
IF ( @VJEDCT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Transaction Set Number is NOT NULL ------------------
IF ( @VJEDTS IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Translation Format is NOT NULL ------------------
IF (@VJEDFT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Transmission Date is NOT NULL ------------------
IF (@VJEDDT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Send-Recieve Indicator is NOT NULL ------------------
IF (@VJEDER IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Batch Type Indicator is NOT NULL ------------------
IF (@VJICUT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Batch Number Indicator is NOT NULL ------------------
IF (@VJICU IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
/************************** Start of the validations for F0911Z1 ***************************/
------------ Checks if the Record Type is NOT NULL ------------
IF (@VNEDTY IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the Record Sequence is NOT NULL ------------
IF (@VNEDSQ IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------ Checks if the EDI - Document Type is NOT NULL ------------------
IF (@VNEDCT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------- Checks if the EDI - Transaction Set Number is NOT NULL ------------------
IF (@VNEDTS IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------- Checks if the EDI - Translation Format is NOT NULL ------------------
IF (@VNEDFT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------- Checks if the EDI - Transmission Date is NOT NULL ------------------
IF (@VNEDDT IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
------------- Checks if the EDI - Send/Receive Indicator is NOT NULL ------------------
IF (@VNEDER IS NOT NULL)
BEGIN
PRINT 'E3 - One of the mandatory blank columns is not blank';
SET @errorcode =@errorcode + ',' + 'E3';
END;
/************************ End of E3 Validations for Both F03B13Z1 and F0911Z1*******************************/
/*************** Added by Mohammed Arshad on 15-Jul-2018 *****************/
---------------- Start of Validations for category E4 ---------------------
------ There should be only 1 record in JDEORIONDV (Staging) with the below combinations
------------------------------------------------------------------------------------
----------- Checks if the E4 - Combination of DOC, DCT, KCO, SFX exists in JDE_Development.F03B11 for the same column names, and handles the check accordingly */
/************ Condition 1 *************/
-- DECLARE @RcCount1 INT
-- DECLARE @CombinedColSet1 varchar(210);
-- SET @CombinedColSet1 = (SELECT CAST(VJDOC AS CHAR) + VJDCT + VJKCO + VJSFX AS ColSet FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE VJEDSP = 0)
-- SET @RcCount1 = (SELECT COUNT(*) FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE VJEDSP = 0 AND (CAST(VJDOC AS CHAR) + VJDCT + VJKCO + VJSFX)=(SELECT CAST(VJDOC AS CHAR) + VJDCT + VJKCO + VJSFX AS ColSet FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE VJEDSP = 0))
-- SELECT CAST(VJDOC AS CHAR),VJDCT,VJKCO,VJSFX,Count(*) FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE VJEDSP = 0 AND (CAST(VJDOC AS CHAR) + VJDCT + VJKCO + VJSFX)=(SELECT CAST(VJDOC AS CHAR) + VJDCT + VJKCO + VJSFX AS ColSet FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE VJEDSP = 0)
IF EXISTS(SELECT VJDOC,VJDCT,VJKCO,VJSFX,Count(*) AS Rwcnt FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] WHERE VJEDSP= 0 GROUP BY VJDOC,VJDCT,VJKCO,VJSFX HAVING COUNT(*) > 1)
-- IF @RcCount1 > 1
BEGIN
PRINT 'E4 - Duplicate invoice number in Staging';
SET @errorcode =@errorcode + ',' + 'E4';
END;
/*********** Condition 2 for the ***********/
IF EXISTS(SELECT 1 FROM [JDEORIONDV].[PRODDTA].[F0911Z1] I,[JDEORIONDV].[PRODDTA].[F03B11Z1] J WHERE I.VNEDUS = @VJEDUS AND I.VNEDTN = @VJEDTN AND I.VNEDLN = @VJEDLN AND I.VNEDSP = @VJEDSP AND I.VNEDBT = @VJEDBT AND I.VNDOC = @VJDOC AND I.VNDCT = @VJDCT AND I.VNKCO = @VJKCO AND I.VNSFX = @VJSFX AND I.VNEDSP = 0 GROUP BY CAST(VNDOC AS CHAR),VNDCT,VNKCO,VNSFX HAVING COUNT(*) > 1)
-- IF @RcCount1 > 1
BEGIN
PRINT 'E4 - Duplicate Invoice Number In Staging';
SET @errorcode =@errorcode + ',' + 'E4';
END;
/******************* End of E4 Validations ********************/
/**************** Start of E5 validations ********************/
------- E5 Checks if the Document Type is other than R0 or R4 ----
IF EXISTS(SELECT 1 FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I,[JDE_DEVELOPMENT].[TESTDTA].[F03B11Z1] J WHERE J.VJDOC=@VJDOC AND J.VJDCT=@VJDCT AND J.VJKCO=@VJKCO AND J.VJSFX=@VJSFX AND I.VJEDSP=0)
BEGIN
PRINT 'E5 - Duplicate invoice number in JDE';
SET @errorcode = @errorcode + ',' + 'E5';
END;
IF EXISTS(SELECT 1 FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I,[JDE_DEVELOPMENT].[TESTDTA].[F03B11] J WHERE J.RPDOC=@VJDOC AND J.RPDOC=@VJDCT AND J.RPKCO=@VJKCO AND J.RPSFX=@VJSFX AND I.VJEDSP=0)
BEGIN
PRINT 'E5 - Duplicate invoice number in JDE';
SET @errorcode = @errorcode + ',' + 'E5';
END;
/************ End of E5 Validations *************/
/*************************** Start of E6 Validations **************************/
------ Checks if the E6 - AN8 should exist in JDE_DEVELOPMENT.F0101 for the same column name
------, and handles the check accordingly */
IF EXISTS (SELECT 1 FROM [JDE_Development].TESTDTA.[F0101] J ,[JDEORIONDV].[PRODDTA].[F03B11Z1] S WHERE J.ABAN8 <> @VJAN8 AND J.ABAT1='C')
BEGIN
PRINT 'E6 - Invalid Customer Number';
SET @errorcode = @errorcode + ',' + 'E6';
END;
/************************* End of E6 Validations *****************************/
/************************ Start of E7 Validations **************************/
------------ Checks the validations for E7 - Month of the date should be greater than or ----------
---equal to F0101.ARPN (F0101.APRN FROM JDE_Development.F0010,JDEORIONDV].[PRODDTA].[F03B11Z1] where F03B11Z1.KCO = F0101.CO
---and) handle the check accordingly */
-- Code to compare the Month of the date time string ***/
IF EXISTS (SELECT 1 FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J,[JDE_DEVELOPMENT].[TESTDTA].[F0010] G WHERE MONTH(JDE_DEVELOPMENT.dbo.JulianDate(@VJDGJ)) > G.CCARPN AND G.CCCO=@VJKCO AND @VJEDSP=0)
BEGIN
PRINT 'E7 - Invalid GL Date';
SET @errorcode = @errorcode + ',' + 'E7';
END;
--Code to compare the Year of the date time string ***/
IF EXISTS (SELECT 1 FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] J,[JDE_DEVELOPMENT].[TESTDTA].[F0010] G WHERE YEAR(JDE_DEVELOPMENT.dbo.JulianDate(@VJDGJ)) > G.CCARFJ AND G.CCCO=@VJKCO AND @VJEDSP=0)
BEGIN
PRINT 'E7 - Invalid GL Date';
SET @errorcode = @errorcode + ',' + 'E7';
END;
/************************** End of E7 Validations *****************************/
/************************ Start of E8 Validations **************************/
------- Checks the validations for E8 - Make sure that the Batch number F03B11Z1.ICU,F0911Z1.ICU is not blank for the given condition,
------- in which case it has to be flagged in the validation accordingly */
DECLARE @BatchNum1 float,@BatchNum2 float;
/********************** Commemted by Arshad as the where condition has been used inthe Cursor definition already ***********************/
--SET @BatchNum1 = (SELECT @VJICU FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] A,[JDEORIONDV].[PRODDTA].[F0911Z1] B WHERE @VJEDUS = B.VNEDUS AND @VJEDTN = B.VNEDTN AND @VJEDLN = B.VNEDLN AND @VJEDSP = B.VNEDSP AND @VJEDBT = B.VNEDBT AND @VJDOC=B.VNDOC AND @VJDCT = B.VNDCT AND @VJKCO = B.VNKCO AND @VJSFX = B.VNSFX AND @VJEDSP = '0')
--SET @BatchNum2 = (SELECT @VJICU FROM [JDEORIONDV].[PRODDTA].[F0911Z1] B,[JDEORIONDV].[PRODDTA].[F03B11Z1] A WHERE B.VNEDUS=@VJEDUS AND B.VNEDTN=@VJEDTN AND B.VNEDLN=@VJEDLN AND B.VNEDSP=@VJEDSP AND B.VNEDBT=@VJEDBT AND B.VNDOC=@VJDOC AND B.VNDCT=@VJDCT AND B.VNKCO=@VJKCO AND B.VNSFX=@VJSFX AND @VJEDSP = '0')
/*************************************************************************************************************/
SET @BatchNum1 = @VJICU;
SET @BatchNum2 = @VJICU;
IF (@BatchNum1 IS NOT NULL OR @BatchNum1 <>'' OR @BatchNum2 IS NOT NULL OR @BatchNum2 <>'')
BEGIN
PRINT 'E8 - Batch Number should be blank';
SET @errorcode = @errorcode + ',' + 'E8';
END;
/************************** End of E8 Validations *****************************/
/************************ Start of E9 Validations **************************/
------------ Checks the validations for E9 to make sure that a matching GL record is found in staging database .If not , it has to be flagged in the validation accordingly ********/
IF (@VNEDUS<>@VJEDUS AND @VNEDTN<>@VJEDTN AND @VNEDLN<>@VJEDLN AND @VNEDSP=@VJEDSP AND @VNEDBT=@VJEDBT AND @VNDOC=@VJDOC AND @VNDCT=@VJDCT AND @VNKCO=@VJKCO AND @VNSFX=@VJSFX AND @VJEDSP = '0')
BEGIN
PRINT 'E9 - GL record not found in Staging';
SET @errorcode = @errorcode + ',' + 'E9';
END;
/************************** End of E9 Validations *****************************/
/************************ Start of E10 Validations **************************/
----------- Checks the validations for E10 to make sure that a matching record for Amount is found in staging database .If not , it has to be flagged in the validation accordingly ********/
IF (@VJATXA = @VNAA)
BEGIN
PRINT 'E10 - Amount Mismatch Against GL record';
SET @errorcode = @errorcode + ',' + 'E10';
END;
/************************** End of E10 Validations *****************************/
/************************** Start of E11 Validations **************************/
------------ Checks the validations for E11 to make sure a valid code is found.If not , it has to be flagged in the validation accordingly ********/
IF NOT EXISTS (SELECT 1 FROM [JDE_DEVELOPMENT].[TESTDTA].[F4008] B WHERE B.TATXA1=@VJTXA1)
BEGIN
PRINT 'E11 - VAT Code Invalid';
SET @errorcode = @errorcode + ',' + 'E11';
END;
/************************** End of E11 Validations *****************************/
/*************************** Start of E12 Validations **************************/
-------- Checks the validations for E12 to make sure Gross amount tallies with the Tax amount. If not , it has to be flagged in the validation accordingly -----
IF (@VJATXA + @VJSTAM <> @VJAG)
BEGIN
PRINT 'E12 - Gross Amount does not tally with tax amount';
SET @errorcode = @errorcode + ',' + 'E12';
END;
/**************************** End of E12 Validations ****************************/
/*************************** Start of E13 Validations **************************/
--- Exception check for the validation of Account code.
DECLARE @Code1 char(34);
DECLARE @Code2 char(34);
DECLARE @Code3 char(34);
DECLARE @Code4 char(34);
---- Gathers the comparison set into separate variables. The variable contents are validated through the use of IF construct and flagged accordingly ********/
SET @Code1 = @VJAID + @VJMCU + @VJOBJ + @VJSUB
SET @Code2 = (SELECT B.GMAID + B.GMMCU + GMOBJ + B.GMSUB FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] A,[JDE_DEVELOPMENT].[TESTDTA].[F0901] B WHERE B.GMCO = @VJKCO AND @VJEDSP =0 )
------------------------------------------------------------------------------------
---- Gathers the comparison set into separate variables. The variable contents are validated through the use of IF construct and flagged accordingly ********/
SET @Code3 = (SELECT A.GMAID + A.GMMCU + A.GMOBJ + A.GMSUB FROM [JDE_DEVELOPMENT].[TESTDTA].[F0901] A WHERE A.GMCO = @VNCO AND @VNEDSP =0)
SET @Code4 = (SELECT B.VNAID + B.VNMCU + B.VNOBJ + B.VNSUB FROM [JDE_DEVELOPMENT].[TESTDTA].[F0901] A,[JDEORIONDV].[PRODDTA].[F0911Z1] B WHERE A.GMCO = @VNCO AND @VNEDSP =0)
------------------------------------------------------------------------------------
/*********** Start of the validation *************/
IF (@Code1 <> @code2 or @code3 <> @code4)
BEGIN
PRINT 'E13 - Account Code Invalid';
SET @errorcode = @errorcode + ',' + 'E13';
END;
/**************************** End of E13 Validations ****************************/
/**************************** End of E14 Validations ****************************/
/************ Checks the validations for E14 to make sure GL record matches with Invoice.If not , it has to be flagged in the validation accordingly ************/
IF (@VJEDSP=@VNEDSP AND @VJAID = @VNAID AND @VJEDGL = @VNEDGL AND @VJU=@VNU AND @VJUM=@VNUM AND @VJEDUS = @VNEDUS AND @VJEDTN = @VNEDTN AND @VJEDLN = @VNEDLN AND @VJEDSP=@VNEDSP AND @VJEDBT = @VNEDBT AND @VJDOC = @VNDOC AND @VJDCT = @VNDCT AND @VJKCO = @VNKCO AND @VJSFX = @VNSFX AND @VJEDSP =0)
BEGIN
PRINT 'E14 - GL record does not match with Invoice';
SET @errorcode = @errorcode + 14;
END;
/********************************* End of E14 Validations ***********************/
/*************************** Start of E15 Validations ***************************/
--------------- Exception check for the validation of SubLedger or Type being Invalid --------------
------------------ Left Padded values of F03B11Z1.AN8 to be compared with SBL F0911Z1.SBL
DECLARE @AddNum varchar(8);
SET @AddNum=(SELECT RIGHT(REPLICATE('0',8) + CAST(Cast(@VNAN8 as int) AS VARCHAR(8)),8));
SET @errorcode = @errorcode + 15;
-------------------------- Match SBLT FROM F0911Z1 to be not equal alphabet A -------
IF @VNSBLT<>'A'
BEGIN
PRINT 'E15 -Subledger or Type Invalid';
END;
------------------------------------------------------------------------
/**************************** End of E15 Validations ****************************/
/****** Begin Post-Validation Processing which Updates the ErrorCode Column (RMRI) and then Import the data into both the tables in JDE,and updats the EDSP flag in both the tables in Staging to 1.******/
/************ Set the @errorcode in both the tables F03B11Z1 and F0911Z1 in value to determine what action has to be performed accordingly *************/
IF (@errorcode = '' OR @errorcode IS NULL)
BEGIN
SET @errorcode ='S';
------- Update F03B11Z1 table in Staging --------
UPDATE JDEORIONDV.PRODDTA.F03B11Z1 SET [VJRMRI]=@errorcode WHERE [VJEDUS]=@VJEDUS AND [VJEDTN]=@VJEDTN AND [VJEDBT]=@VJEDBT;
------ Update F0911Z1 table in Staging ------
UPDATE JDEORIONDV.PRODDTA.F0911Z1 SET [VNRMRI]=@errorcode WHERE [VNEDUS]=@VNEDUS AND [VNEDTN]=@VNEDTN AND [VNEDBT]=@VNEDBT;
/************ Update the RMRI flags in both the Tables with the error code in the for the current record ************/
/**********************************************************************************************************/
/* Code to insert the record [F03B11Z1] from in after going through the validations and updating the error flag column */
INSERT INTO [JDE_DEVELOPMENT].TESTDTA.[F03B11Z1]
([VJEDUS]
,[VJEDTY]
,[VJEDSQ]
,[VJEDTN]
,[VJEDCT]
,[VJEDLN]
,[VJEDTS]
,[VJEDFT]
,[VJEDDT]
,[VJEDER]
,[VJEDDL]
,[VJEDSP]
,[VJEDTC]
,[VJEDTR]
,[VJEDBT]
,[VJEDGL]
,[VJEDDH]
,[VJEDAN]
,[VJDOC]
,[VJDCT]
,[VJKCO]
,[VJSFX]
,[VJAN8]
,[VJDGJ]
,[VJDIVJ]
,[VJICUT]
,[VJICU]
,[VJDICJ]
,[VJFY]
,[VJCTRY]
,[VJPN]
,[VJCO]
,[VJGLC]
,[VJAID]
,[VJPA8]
,[VJAN8J]
,[VJPYR]
,[VJPOST]
,[VJISTR]
,[VJBALJ]
,[VJPST]
,[VJAG]
,[VJAAP]
,[VJADSC]
,[VJADSA]
,[VJATXA]
,[VJATXN]
,[VJSTAM]
,[VJCRRM]
,[VJCRCD]
,[VJCRR]
,[VJDMCD]
,[VJACR]
,[VJFAP]
,[VJCDS]
,[VJCDSA]
,[VJCTXA]
,[VJCTXN]
,[VJCTAM]
,[VJTXA1]
,[VJEXR1]
,[VJDSVJ]
,[VJGLBA]
,[VJAM]
,[VJAID2]
,[VJAM2]
,[VJMCU]
,[VJOBJ]
,[VJSUB]
,[VJSBLT]
,[VJSBL]
,[VJPTC]
,[VJDDJ]
,[VJDDNJ]
,[VJRDDJ]
,[VJRDSJ]
,[VJSMTJ]
,[VJNBRR]
,[VJRDRL]
,[VJRMDS]
,[VJCOLL]
,[VJCORC]
,[VJAFC]
,[VJRSCO]
,[VJCKNU]
,[VJODOC]
,[VJODCT]
,[VJOKCO]
,[VJOSFX]
,[VJVINV]
,[VJPO]
,[VJPDCT]
,[VJPKCO]
,[VJDCTO]
,[VJLNID]
,[VJSDOC]
,[VJSDCT]
,[VJSKCO]
,[VJSFXO]
,[VJVLDT]
,[VJCMC1]
,[VJVR01]
,[VJUNIT]
,[VJMCU2]
,[VJRMK]
,[VJALPH]
,[VJRF]
,[VJDRF]
,[VJCTL]
,[VJFNLP]
,[VJITM]
,[VJU]
,[VJUM]
,[VJALT6]
,[VJRYIN]
,[VJVDGJ]
,[VJVRE]
,[VJRP1]
,[VJRP2]
,[VJRP3]
,[VJAR01]
,[VJAR02]
,[VJAR03]
,[VJAR04]
,[VJAR05]
,[VJAR06]
,[VJAR07]
,[VJAR08]
,[VJAR09]
,[VJAR10]
,[VJURC1]
,[VJURDT]
,[VJURAT]
,[VJURAB]
,[VJURRF]
,[VJTORG]
,[VJUSER]
,[VJPID]
,[VJUPMJ]
,[VJUPMT]
,[VJJOBN]
,[VJHCRR]
,[VJHDGJ]
,[VJDIM]
,[VJDID]
,[VJDIY]
,[VJDI#]
,[VJDGM]
,[VJDGD]
,[VJDGY]
,[VJDG#]
,[VJDICM]
,[VJDICD]
,[VJDICY]
,[VJDIC#]
,[VJDSVM]
,[VJDSVD]
,[VJDSVY]
,[VJDSV#]
,[VJDDM]
,[VJDDD]
,[VJDDY]
,[VJDD#]
,[VJDDNM]
,[VJDDND]
,[VJDDNY]
,[VJDDN#]
,[VJSMTM]
,[VJSMTD]
,[VJSMTY]
,[VJSMT#]
,[VJRDDM]
,[VJRDDD]
,[VJRDDY]
,[VJRDD#]
,[VJRDSM]
,[VJRDSD]
,[VJRDSY]
,[VJRDS#]
,[VJHDGM]
,[VJHDGD]
,[VJHDGY]
,[VJHDG#]
,[VJSHPN]
,[VJDTXS]
,[VJOMOD]
,[VJCLMG]
,[VJCMGR]
,[VJATAD]
,[VJCTAD]
,[VJNRTA]
,[VJFNRT]
,[VJPRGF]
,[VJGFL1]
,[VJGFL2]
,[VJDOCO]
,[VJKCOO]
,[VJSOTF]
,[VJDTPB]
,[VJERDJ]
,[VJNETST]
,[VJRMRI])
VALUES(
@VJEDUS,@VJEDTY,@VJEDSQ,@VJEDTN,@VJEDCT,@VJEDLN,@VJEDTS,@VJEDFT,@VJEDDT,@VJEDER,@VJEDDL,@VJEDSP,@VJEDTC,@VJEDTR,@VJEDBT,
@VJEDGL,@VJEDDH,@VJEDAN,@VJDOC,@VJDCT,@VJKCO,@VJSFX,@VJAN8,@VJDGJ,@VJDIVJ,@VJICUT,@VJICU,@VJDICJ,@VJFY,@VJCTRY,@VJPN,@VJCO,@VJGLC,@VJAID,
@VJPA8,@VJAN8J,@VJPYR,@VJPOST,@VJISTR,@VJBALJ,@VJPST,@VJAG,@VJAAP,@VJADSC,@VJADSA,@VJATXA,@VJATXN,@VJSTAM,@VJCRRM,@VJCRCD,@VJCRR,@VJDMCD,
@VJACR,@VJFAP,@VJCDS,@VJCDSA,@VJCTXA,@VJCTXN,@VJCTAM,@VJTXA1,@VJEXR1,@VJDSVJ,@VJGLBA,@VJAM,@VJAID2,@VJAM2,@VJMCU,@VJOBJ,@VJSUB,@VJSBLT,
@VJSBL,@VJPTC,@VJDDJ,@VJDDNJ,@VJRDDJ,@VJRDSJ,@VJSMTJ,@VJNBRR,@VJRDRL,@VJRMDS,@VJCOLL,@VJCORC,@VJAFC,@VJRSCO,@VJCKNU,@VJODOC,@VJODCT,
@VJOKCO,@VJOSFX,@VJVINV,@VJPO,@VJPDCT,@VJPKCO,@VJDCTO,@VJLNID,@VJSDOC,@VJSDCT,@VJSKCO,@VJSFXO,@VJVLDT,@VJCMC1,@VJVR01,@VJUNIT,@VJMCU2,
@VJRMK,@VJALPH,@VJRF,@VJDRF,@VJCTL,@VJFNLP,@VJITM,@VJU,@VJUM,@VJALT6,@VJRYIN,@VJVDGJ,@VJVRE,@VJRP1,@VJRP2,@VJRP3,@VJAR01,@VJAR02,@VJAR03,
@VJAR04,@VJAR05,@VJAR06,@VJAR07,@VJAR08,@VJAR09,@VJAR10,@VJURC1,@VJURDT,@VJURAT,@VJURAB,@VJURRF,@VJTORG,@VJUSER,@VJPID,@VJUPMJ,
@VJUPMT,@VJJOBN,@VJHCRR,@VJHDGJ,@VJDIM,@VJDID,@VJDIY,@VJDI#,@VJDGM,@VJDGD,@VJDGY,@VJDG#,@VJDICM,@VJDICD,@VJDICY,@VJDIC#,@VJDSVM,@VJDSVD,
@VJDSVY,@VJDSV#,@VJDDM,@VJDDD,@VJDDY,@VJDD#,@VJDDNM,@VJDDND,@VJDDNY,@VJDDN#,@VJSMTM,@VJSMTD,@VJSMTY,@VJSMT#,@VJRDDM,@VJRDDD,
@VJRDDY,@VJRDD#,@VJRDSM,@VJRDSD,@VJRDSY,@VJRDS#,@VJHDGM,@VJHDGD,@VJHDGY,@VJHDG#,@VJSHPN,@VJDTXS,@VJOMOD,@VJCLMG,@VJCMGR,@VJATAD,
@VJCTAD,@VJNRTA,@VJFNRT,@VJPRGF,@VJGFL1,@VJGFL2,@VJDOCO,@VJKCOO,@VJSOTF,@VJDTPB,@VJERDJ,@VJNETST,@errorcode)
/* Code to insert the record into [F0911Z1] from in after going through the validations and updating the error flag column */
INSERT INTO [JDE_DEVELOPMENT].TESTDTA.[F0911Z1]
(VNEDUS,
VNEDTY,
VNEDSQ,
VNEDTN,
VNEDCT,
VNEDLN,
VNEDTS,
VNEDFT,
VNEDDT,
VNEDER,
VNEDDL,
VNEDSP,
VNEDTC,
VNEDTR,
VNEDBT,
VNEDGL,
VNEDAN,
VNKCO,
VNDCT,
VNDOC,
VNDGJ,
VNJELN,
VNEXTL,
VNPOST,
VNICU,
VNICUT,
VNDICJ,
VNDSYJ,
VNTICU,
VNCO,
VNANI,
VNAM,
VNAID,
VNMCU,
VNOBJ,
VNSUB,
VNSBL,
VNSBLT,
VNLT,
VNPN,
VNCTRY,
VNFY,
VNFQ,
VNCRCD,
VNCRR,
VNHCRR,
VNHDGJ,
VNAA,
VNU,
VNUM,
VNGLC,
VNRE,
VNEXA,
VNEXR,
VNR1,
VNR2,
VNR3,
VNSFX,
VNODOC,
VNODCT,
VNOSFX,
VNPKCO,
VNOKCO,
VNPDCT,
VNAN8,
VNCN,
VNDKJ,
VNDKC,
VNASID,
VNBRE,
VNRCND,
VNSUMM,
VNPRGE,
VNTNN,
VNALT1,
VNALT2,
VNALT3,
VNALT4,
VNALT5,
VNALT6,
VNALT7,
VNALT8,
VNALT9,
VNALT0,
VNALTT,
VNALTU,
VNALTV,
VNALTW,
VNALTX,
VNALTZ,
VNDLNA,
VNCFF1,
VNCFF2,
VNASM,
VNBC,
VNVINV,
VNIVD,
VNWR01,
VNPO,
VNPSFX,
VNDCTO,
VNLNID,
VNWY,
VNWN,
VNFNLP,
VNOPSQ,
VNJBCD,
VNJBST,
VNHMCU,
VNDOI,
VNALID,
VNALTY,
VNDSVJ,
VNTORG,
VNREG#,
VNPYID,
VNUSER,
VNPID,
VNJOBN,
VNUPMJ,
VNUPMT,
VNCRRM,
VNACR,
VNDGM,
VNDGD,
VNDGY,
VNDG#,
VNDICM,
VNDICD,
VNDICY,
VNDIC#,
VNDSYM,
VNDSYD,
VNDSYY,
VNDSY#,
VNDKM,
VNDKD,
VNDKY,
VNDK#,
VNDSVM,
VNDSVD,
VNDSVY,
VNDSV#,
VNHDGM,
VNHDGD,
VNHDGY,
VNHDG#,
VNDKCM,
VNDKCD,
VNDKCY,
VNDKC#,
VNIVDM,
VNIVDD,
VNIVDY,
VNIVD#,
VNABR1,
VNABR2,
VNABR3,
VNABR4,
VNABT1,
VNABT2,
VNABT3,
VNABT4,
VNITM,
VNPM01,
VNPM02,
VNPM03,
VNPM04,
VNPM05,
VNPM06,
VNPM07,
VNPM08,
VNPM09,
VNPM10,
VNBCRC,
VNEXR1,
VNTXA1,
VNTXITM,
VNACTB,
VNSTAM,
VNCTAM,
VNAG,
VNAGF,
VNTKTX,
VNDLNID,
VNCKNU,
VNBUPC,
VNAHBU,
VNEPGC,
VNJPGC,
VNRC5,
VNSFXE,
VNOFM,
VNRMRI
)
VALUES(@VNEDUS,@VNEDTY,@VNEDSQ,@VNEDTN,@VNEDCT,@VNEDLN,@VNEDTS,@VNEDFT,@VNEDDT,@VNEDER,@VNEDDL,@VNEDSP,@VNEDTC,@VNEDTR,@VNEDBT,@VNEDGL,
@VNEDAN,@VNKCO,@VNDCT,@VNDOC,@VNDGJ,@VNJELN,@VNEXTL,@VNPOST,@VNICU,@VNICUT,@VNDICJ,@VNDSYJ,@VNTICU,@VNCO,@VNANI,@VNAM,@VNAID,@VNMCU,@VNOBJ,
@VNSUB,@VNSBL,@VNSBLT,@VNLT,@VNPN,@VNCTRY,@VNFY,@VNFQ,@VNCRCD,@VNCRR,@VNHCRR,@VNHDGJ,@VNAA,@VNU,@VNUM,@VNGLC,@VNRE,@VNEXA,@VNEXR,@VNR1,@VNR2,
@VNR3,@VNSFX,@VNODOC,@VNODCT,@VNOSFX,@VNPKCO,@VNOKCO,@VNPDCT,@VNAN8,@VNCN,@VNDKJ,@VNDKC,@VNASID,@VNBRE,@VNRCND,@VNSUMM,@VNPRGE,@VNTNN ,@VNALT1,
@VNALT2,@VNALT3,@VNALT4,@VNALT5,@VNALT6,@VNALT7,@VNALT8,@VNALT9,@VNALT0,@VNALTT,@VNALTU,@VNALTV,@VNALTW,@VNALTX,@VNALTZ,@VNDLNA,@VNCFF1,
@VNCFF2,@VNASM,@VNBC,@VNVINV,@VNIVD,@VNWR01,@VNPO,@VNPSFX,@VNDCTO,@VNLNID,@VNWY,@VNWN,@VNFNLP,@VNOPSQ,@VNJBCD,@VNJBST,@VNHMCU,@VNDOI,@VNALID,@VNALTY,
@VNDSVJ,@VNTORG,@VNREG#,@VNPYID,@VNUSER,@VNPID,@VNJOBN,@VNUPMJ,@VNUPMT,@VNCRRM,@VNACR,@VNDGM,@VNDGD,@VNDGY,@VNDG#,@VNDICM,@VNDICD,@VNDICY,@VNDIC#,
@VNDSYM,@VNDSYD,@VNDSYY,@VNDSY#,@VNDKM,@VNDKD,@VNDKY,@VNDK#,@VNDSVM,@VNDSVD,@VNDSVY,@VNDSV#,@VNHDGM,@VNHDGD,@VNHDGY,@VNHDG#,@VNDKCM,@VNDKCD,
@VNDKCY,@VNDKC#,@VNIVDM,@VNIVDD,@VNIVDY,@VNIVD#,@VNABR1,@VNABR2,@VNABR3,@VNABR4,@VNABT1,@VNABT2,@VNABT3,@VNABT4,@VNITM,@VNPM01,@VNPM02,@VNPM03,
@VNPM04,@VNPM05,@VNPM06,@VNPM07,@VNPM08,@VNPM09,@VNPM10,@VNBCRC,@VNEXR1,@VNTXA1,@VNTXITM,@VNACTB,@VNSTAM,@VNCTAM,@VNAG,@VNAGF,@VNTKTX,@VNDLNID,
@VNCKNU,@VNBUPC,@VNAHBU,@VNEPGC,@VNJPGC,@VNRC5,@VNSFXE,@VNOFM,@errorcode)
/* Import the invoices from the Staging to the JDE environment and update the flag in the staging accordingly */
UPDATE [JDEORIONDV].[PRODDTA].[F03B11Z1]
SET VJEDSP = 1 where VJEDSP = 0 AND [VJEDUS]=@VJEDUS AND [VJEDTN]=@VJEDTN AND [VJEDBT]=@VJEDBT AND VJEDLN=@VJEDLN
PRINT 'Invoices1 Import from Staging to JDE and Post-Import Update Staging done Successfully !';
-------- Update the EDSP Flag in the staging environmnent -------
UPDATE [JDEORIONDV].[PRODDTA].[F0911Z1]
SET VNEDSP = 1 where VNEDSP = 0 AND [VNEDUS]=@VNEDUS AND [VNEDTN]=@VNEDTN AND [VNEDBT]=@VNEDBT AND VNEDLN=@VNEDLN ;
------------- Reset the @errorcode = '' back to blank for the processing of next record ------------
SET @errorcode = '';
END;
ELSE
/********End of code for Invoice1 processing in cursor 1 above. ***********/
/**********************************************************************************************************/
/* Import the Invoices-GL from the Staging to the JDE environment and update the flag VDESP flag in the staging accordingly */
BEGIN
---------- Update F03B11Z1 table in the Staging --------------------------
UPDATE JDEORIONDV.PRODDTA.F03B11Z1 SET [VJRMRI]=@errorcode WHERE [VJEDUS]=@VJEDUS AND [VJEDTN]=@VJEDTN AND [VJEDBT]=@VJEDBT AND VJEDLN=@VJEDLN;
---------- Update F0911Z1 table in Staging --------------------------
UPDATE JDEORIONDV.PRODDTA.F0911Z1 SET [VNRMRI]=@errorcode WHERE [VNEDUS]=@VNEDUS AND [VNEDTN]=@VNEDTN AND [VNEDBT]=@VNEDBT AND VNEDLN=@VNEDLN ;
PRINT 'Invoices-GL Import cannot be done due to a failure in validation check!';
SET @errorcode = '';
END;
CLOSE Invoices1;
DEALLOCATE Invoices1;
END;
GO
August 5, 2018 at 3:16 am
Thanks....Arshad
August 5, 2018 at 3:40 am
USE [JDE_DEVELOPMENT]
GO
/****** Object: StoredProcedure [dbo].[ImportInvoices_Validations_CrossQueryCursor] Script Date: 05-Aug-18 12:27:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportInvoices_ZeroRecords]
WITH EXECUTE AS OWNER
AS
/****** Variable Declarations for [F03B11Z1] ******/
DECLARE
@errorcode NVARCHAR(40);
/********* Cursor definition with outer join of [F03B11Z1] WITH [F0911Z1] ********/
DECLARE Invoices1 CURSOR FOR SELECT * FROM [JDEORIONDV].[PRODDTA].[F03B11Z1] I LEFT OUTER JOIN [JDEORIONDV].[PRODDTA].[F0911Z1] J ON I.VJEDUS = J.VNEDUS AND I.VJEDTN = J.VNEDTN AND I.VJEDLN = J.VNEDLN AND I.VJEDSP = J.VNEDSP AND I.VJEDBT = J.VNEDBT AND I.VJDOC = J.VNDOC AND I.VJDCT = J.VNDCT AND I.VJKCO = J.VNKCO AND I.VJSFX = J.VNSFX AND I.VJEDSP = 0 WHERE I.VJEDSP = 0;
BEGIN
OPEN Invoices1
--LOOP UNTIL RECORDS ARE AVAILABLE.
PRINT 'I am just before while';
WHILE @@FETCH_STATUS = 0
PRINT 'I am in While';
SET @errorcode = '';
CLOSE Invoices1;
DEALLOCATE Invoices1;
END;
GO
August 5, 2018 at 4:02 am
This is because there is no fetch within the cursor loop, hence the fetch_status will always be 0.
😎
Here is the correct structure for a cursor
DECLARE @VARIABLE [DATATYPE]
DECLARE [CURSOR_NAME] CURSOR FAST_FORWARD
FOR [SELECT STATEMENT]
OPEN [CURSOR_NAME]
FETCH NEXT [COLUMN_NAME] FROM [CURSOR_NAME] INTO @VARIABLE
WHILE @@FETCH_STATUS = 0
BEGIN
[DO THE WORK]
FETCH NEXT [COLUMN_NAME] FROM [CURSOR_NAME] INTO @VARIABLE
END
CLOSE [CURSOR_NAME]
DEALLOCATE [CURSOR_NAME]
August 5, 2018 at 4:09 am
Eirikur Eiriksson - Sunday, August 5, 2018 4:02 AMThis is because there is no fetch within the cursor loop, hence the fetch_status will always be 0.
😎Here is the correct structure for a cursor
DECLARE @VARIABLE [DATATYPE]DECLARE [CURSOR_NAME] CURSOR FAST_FORWARD
FOR [SELECT STATEMENT]
OPEN [CURSOR_NAME]
FETCH NEXT [COLUMN_NAME] FROM [CURSOR_NAME] INTO @VARIABLE
WHILE @@FETCH_STATUS = 0
BEGIN
[DO THE WORK]
FETCH NEXT [COLUMN_NAME] FROM [CURSOR_NAME] INTO @VARIABLE
END
CLOSE [CURSOR_NAME]
DEALLOCATE [CURSOR_NAME]
Good concise answer to what is probably the longest question I have ever seen.
August 5, 2018 at 4:37 am
* If you don't include a BEGIN ... END, the WHILE will only ever run the single statement that follows it (BEGIN … END marks a compound statement)
* You need a FETCH NEXT each loop to advance the cursor
*If all you are doing each pass of your loop is updating a value of a table based on some conditions, you can probably re-write it much better as an UPDATE statement and get rid of the RBAR approach
August 5, 2018 at 5:04 am
Drop Table If Exists #Temp
Create Table #Temp
(
ProdId Int,
MustBeOrion VarChar(10),
LinesProcessed Int,
InvoiceNumber Int,
Error Bit,
RowErrorOccured VarChar(300)
)
August 5, 2018 at 8:19 am
Arsh - Sunday, August 5, 2018 3:16 AMDear All,
I've attached the SP in this file for reference in case the code is not readable in the 'Post' window here. Quick advise / input on this is highly appreaciated.Thanks....Arshad
First of all, my hat is off to you, Arshad! Not only is the code well structured, but it's fully and properly commented! Very nicely done!
The problem with this code, though, is that it's RBAR on steroids and the individual "Row By Agonizing Row" validations are going to take "forever". We went through this at work and, yes, it was also for the validation of EDI data. It was for loans rather than invoices but it's the same basic concept and it took "forever" to run. I'll also state that the error reporting being done is sorely lacking because, although the error code and messages clearly identify what the problem with the data is, it doesn't help for quickly identifying the exact problem with the data because it doesn't actually identify WHICH COLUMN(s) had the failure. Because of that, it would take someone or a post process troubleshooting/reporting proc a huge amount of time and require maintenance of "the rules" in two separate procs instead of just one.
Your dupe checks are also killing performance because they're doing a count rather than just an existence check.
I've only looked at the code for several minutes but (thanks to the great documentation you've included in the code), I'm thinking that this could be rewritten as (seriously) just a couple of INSERT statements with a wad of properly written CASE operators and it would be nasty fast and use a whole lot less resources than what your current RBAR method uses.
Heh... and please don't say that you won't even have more than a small amount of row to process to justify the RBAR. That's what the previous folks at work said and when business boomed, they were in some seriously deep Kimchi because they couldn't get the processing done even when the code ran all night and that was back when they has a relatively small number of customers/files to process. A single file run that used to take 10 to 12 hours to process now only takes 30 minutes and we've added a ton of additional checks on each file.
With all of that, I urge you and the folks at your company to rewrite this code to work as set based code rather than the RBAR on steroids that it currently is. Make the "paradigm" shift that I mention in my signature line below. It's an important shift in thinking. Lot's of people are amazed to have just some of their code run twice as fast with SSDs or "up to" (their claim, not mine) 30 times faster if you go full MPP and buy the hardware to do it and change the code to take advantage of it. You can get literally a 60 to 1000 times improvement fairly easily if you know how to write (usually easier, faster, smaller, simpler, etc) the code and the paradigm shift is the first step on how to do that. The second step is to realize that ever SELECT is actually already a cursor (I refer to them as "pseudo cursors", which is a term coined by R. Barry Young on this very site) behind the scenes but they're almost at machine language speeds.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2018 at 8:22 am
p.s. Andy posted while I was typing... that's the basic idea of what I'm talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2018 at 9:11 am
Jeff Moden - Sunday, August 5, 2018 8:22 AMp.s. Andy posted while I was typing... that's the basic idea of what I'm talking about.
My thought was to first fix the error and then and if there are performance problems, then address the REBAR.
😎
Doesn't matter how fast the code is it it does not work 😉
August 5, 2018 at 10:37 am
Eirikur Eiriksson - Sunday, August 5, 2018 9:11 AMJeff Moden - Sunday, August 5, 2018 8:22 AMp.s. Andy posted while I was typing... that's the basic idea of what I'm talking about.My thought was to first fix the error and then and if there are performance problems, then address the REBAR.
😎Doesn't matter how fast the code is it it does not work 😉
Absolutely agreed.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2018 at 4:18 pm
Apologies... I apparently hit the go button without completing the thought. I absolutely agree that it doesn't matter how fast something is if it doesn't work or produces incorrect answers... but while we're fixing that, we might as well fix the massive performance issues so that we don't have to do two sets of tests for two releases. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply