Memo Header Import

Memo Header Import Memo header lots can be imported via a csv file using the updatetableExcel program. A procedure was created in order to have validation done on import data and give the ability use minimal fields to create the proper lots headers. The file layout is as followed : BRANCHLABEL,ALTSKUID,SUPPID,REFNUMBER,FIFODATE,PRICE,CURRID,EXCHRATE,ASSETUNIT,MEMOUNIT,NOTES

BRANCHLABEL:

Corresponding branchid and masterbr will be looked up in salesbranch. Error if not found.

ALTSKUID : Corresponding skuid , productid,default supplier id will be lookup based on alternate sku. Error raised if no skuid or productid. If the Product is not marked as being part of memo the process will try and flag it, if the product already has unit or history the update will be rejected.

SUPPID : If not provided process will use default supplier from inventory. Error if does not exist.

REFNUMBER : User reference that will be added to the lot header.

FIFODATE : Date to be assigned to the lot header.

PRICE : Purchase price for the units.

CURRID : Currency, if not provide process will use default currency from masterbanch with a rate of 1 as 100%.

EXCHRATE: Exchange Rate, if not provided or 0. Process will use 1 as 100%.

ASSETUNIT : Units to be counted as Asset.

gMEMOUNIT : Units to be counted as Memo.

NOTES : User note to be added to the lot.

File Sample: BRANCHLABEL,ALTSKUID,SUPPID,REFNUMBER,FIFODATE,PRICE,CURRID,CURRFACT,ASSET UNITS,MEMO UNITS,NOTES 101,100005,0102,PO12333,6/25/2017,3366,USD,1,0,1,User Small notes 101,100014,0102,PO33444,2/27/2019,7500,USD,1,1,5,"User Longer notes, with more characteres and some test that make it somewhat longer" 101,100018,,PO55555,8/30/2018,336,USD,1,1,5, 101,100355,,PO12333,6/27/2022,2085.12,,,0,1,

UpdatetableExcel script :

execute PROCEDURE IMPORT_MEMOHEADER (:COL_1, :COL_2 , :COL_3, :COL_4,:COL_5,:COL_6,:COL_7,:COL_8,:COL_9,:COL_10,:COL_11)

On next page , Import Procedure , Release version. If procedure already exist in database. Do not replace with this version. CREATE PROCEDURE IMPORT_MEMOHEADER( BRANCHLABEL VARCHAR(4), ALTSKU VARCHAR(20), SUPPID VARCHAR(8), REFNUMBER VARCHAR(40), FIFODATE DATE, PRICE NUMERIC(15,2), CURRID VARCHAR(3), CURRFACT NUMERIC(15,2), A_FIFOUNIT INTEGER, M_FIFOUNIT INTEGER, MEMO_NOTES VARCHAR(250) ) AS DECLARE VARIABLE SKUID VARCHAR(8); DECLARE VARIABLE PRODUCTID VARCHAR(8); DECLARE VARIABLE MASTERBR VARCHAR(2); DECLARE VARIABLE BRANCHID VARCHAR(2); DECLARE VARIABLE GLPRICE NUMERIC(15,2); DECLARE VARIABLE CNT INTEGER; DECLARE VARIABLE DEFSUPPID VARCHAR(8); DECLARE VARIABLE REFINVOICE VARCHAR(14); BEGIN /* Paolo Oct 2023 Import and validate Memo_headers If product not flag as REFINVOICE FLAG IT */

SELECT MASTERBRANCH,BRANCHID FROM SALESBRANCH WHERE BRANCHLABEL=:BRANCHLABEL INTO :MASTERBR,:BRANCHID;

IF ( :MASTERBR IS NULL ) THEN EXCEPTION CSV_TO_BTF_BRANCHID;

SELECT SKUID FROM KSKUIDSKUS WHERE SKU=:ALTSKU INTO :SKUID; IF (:SKUID IS NULL) THEN EXCEPTION WH_SKU_ERROR;

SELECT PRODUCTID FROM INVBOSKUSUMM WHERE SKUID=:SKUID INTO :PRODUCTID; IF (:PRODUCTID IS NULL) THEN EXCEPTION ERR_MISSING_PRODUCTID;

SELECT DEFSUPPID,REFINVOICE FROM INVENTORYMASTER WHERE PRODUCTID=:PRODUCTID INTO :DEFSUPPID,:REFINVOICE;

IF ( F_LRTRIM(:REFINVOICE) <> 'YES' ) THEN UPDATE INVENTORYMASTER SET REFINVOICE='YES' WHERE PRODUCTID=:PRODUCTID;

IF ( F_LRTRIM(:SUPPID) = '') THEN SUPPID = :DEFSUPPID;

CNT = 0; SELECT COUNT(*) FROM SUPPLIER WHERE SUPPID=:SUPPID INTO :CNT; IF (:CNT=0) THEN EXCEPTION NOBILLSUPP;

IF (F_LRTRIM(:CURRID) = '') THEN BEGIN SELECT CURRID FROM SALESBRANCH WHERE BRANCHID=:MASTERBR INTO :CURRID; CURRFACT = 1; END

IF ( (CURRFACT IS NULL) OR (:CURRFACT=0) ) THEN CURRFACT=1;

IF (:CURRFACT <> 1 ) THEN GLPRICE = F_ROUNDPOS( :PRICE*:CURRFACT ); ELSE GLPRICE = :PRICE;

insert into MEMO_HEADER
(MEMOHEADERID,MASTERBR,BRANCHID,PRODUCTID,SKUID,SUPPID,HDRTYPE,MOUVTYPE,IMOUVEID, REFNUMBER,FIFODATE,PRICE,CURRID,CURRFACT,GLPRICE,A_FIFOUNIT,M_FIFOUNIT,MEMO_NOTES) VALUES ( 0, :MASTERBR , :BRANCHID, :PRODUCTID,:SKUID,:SUPPID,'0','2','USR:IMPORT', :REFNUMBER,:FIFODATE,:PRICE,:CURRID,:CURRFACT,:GLPRICE,:A_FIFOUNIT,:M_FIFOUNIT, f_strblob( f_lrtrim(:MEMO_NOTES) ) );

END;