IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_taUpdateCreateItemRcdWrapper'
AND type = 'P')
DROP PROCEDURE dd_taUpdateCreateItemRcdWrapper
GO
CREATE PROCEDURE dd_taUpdateCreateItemRcdWrapper
-- dd_taUpdateCreateItemRcdWrapper 'TEST03','TEST DESC','BOX','FENDI',1,'IGIS',8.00,'VA-PIEDMONT',1,1
/*
SELECT DECPLCUR,* FROM IV00101 WHERE ITEMNMBR LIKE 'TEST%'
SELECT DECPLCUR,* FROM IV00105 WHERE ITEMNMBR LIKE 'TEST%'
*/
--Move the fields from the 'declaration' area below to here if you intend to used the field
--Otherwise, leave them below
@I_vITEMNMBR char(30), /* <Required> Item Number */
@I_vITEMDESC char(100), /* <Required/Optional> Description: Required for a new record */
@I_vITMGEDSC char(10), /* <Optional> Short Description */
@I_vITMCLSCD char(10), /* <Optional> Generic Description */
@I_vITEMTYPE smallint, /* <Optional> Item Type: 1=Sales Inventory; 2=Discontinued; 3=Kit; 4=Misc Charges; 5=Services; 6=Flat Fee */
/* Default=1 on new record */
@I_vUOMSCHDL char(10), /* <Optional> Tax Schedule ID */
@I_vITEMSHWT numeric(8,2), /* <Required/Optional> U of M Schedule ID: Required for a new record, but can roll down from class */
@I_vLOCNCODE char(10), /* <Optional> Default Site ID: Assigns site to item if it is not already assigned */
@I_vUseItemClass tinyint, /* <Optional> Flag to have class roll down to parameters that are not passed in. Uses the @I_vITMCLSCD class */
@I_vUpdateIfExists tinyint /* <Optional> Flag to allow updates: 0=Create only; 1=Update if exists; 2=Create, do NOT update(no error) */
AS
set transaction isolation level read uncommitted
DECLARE @O_iErrorState INT = 0,
@oErrString VARCHAR(256) = '',
@ErrorDesc VARCHAR(256) = ''
--these fields are not currently used, they're here to allow you to easily move them to the area above
declare
@I_vVCTNMTHD smallint, /* <Optional> Valuation Method: 0=Any ITEMTYPE other then 1 or 2; 1=FIFO Perpetual; 2=LIFO Perpetual; */
/* 3=Avg. Perpetual; 4=FIFO Periodic; 5=LIFO Periodic; Default=1 on new record */
@I_vTAXOPTNS smallint, /* <Optional> Sales Tax Option; 1=Taxable; 2=Nontaxable; 3=Base on Customers; Default=1 on new record */
@I_vITMTSHID char(15), /* <Optional> Tax Schedule ID */
@I_vTCC char(30), /* <Optional> Tax Commodity Code: Only used if the 'Enable Intrastat Tracking' opion is enabled in Company Setup*/
@I_vCNTRYORGN char(6), /* <Optional> Country Origin: Only used if the 'Enable Intrastat Tracking' opion is enabled in Company Setup */
@I_vDECPLQTY smallint, /* <Optional> Quantity Decimals: Valid values=0-5; Default=0 on new record; ITEMTYPE=3 must be 0 */
@I_vDECPLCUR smallint, /* <Optional> Currency Decimals: Used for item if not registered for MC or used for functional currency */
/* if registered for MC; If registered MC and want other currencies use taUpdateCreateItemCurrencyRcd node */
@I_vPurchase_Tax_Options smallint,/* <Optional> Purchase Tax option: 1=Taxable; 2=NonTaxable; 3=Base on Vendors; Default=1 */
@I_vPurchase_Item_Tax_Schedu char(15),/* <Optional> Purchases Tax Schedule ID */
@I_vSTNDCOST numeric(19,5), /* <Optional> Standard Cost */
@I_vCURRCOST numeric(19,5), /* <Optional> Current Cost */
@I_vLISTPRCE numeric(19,5), /* <Optional> List Price: Used if MC is not registered or for functional currency if MC registered */
@I_vNOTETEXT varchar(8000), /* <Optional> Note Text */
@I_vALTITEM1 char(30), /* <Optional> Substitute Item 1 */
@I_vALTITEM2 char(30), /* <Optional> Substitute item 2 */
@I_vITMTRKOP smallint, /* <Optional> Track: 1=None; 2=Serial Numbers; 3=Lot Numbers; Default=1 on new record; Used for ITEMTYPE 1,2 */
@I_vLOTTYPE char(10), /* <Optional> Lot Category */
@I_vLOTEXPWARN tinyint, /* <Optional> Lot Expiration Warn: 0 = no warning; 1 = warnings enabled */
@I_vLOTEXPWARNDAYS smallint, /* <Optional> Days Before Lot Expires: 0 - 999 */
@I_vINCLUDEINDP tinyint, /* <Optional> Include in Demand Planning; 0=Do Not Include; 1=Include; Default=0 on new record */
@I_vMINSHELF1 smallint, /* <Optional> Minimum Shelf Life 1: Valid values=0-9999; Default=0 on new record; Valid with ITMTRKOP=3 */
@I_vMINSHELF2 smallint, /* <Optional> Minimum Shelf Life 2: Valid values=0-9999; Default=0 on new record; Valid with ITMTRKOP=3 */
@I_vALWBKORD tinyint, /* <Optional> Allow Back Orders: 0=Do Not Allow Back Orders; 1=Allow Back Orders; Default=0 on new record */
@I_vWRNTYDYS smallint, /* <Optional> Warranty Days: Valid values=0-9999; Default=0 on new record */
@I_vABCCODE smallint, /* <Optional> ABC Code: 1=None; 2=A; 3=B; 4=C; Default=1 on new record */
@I_vUSCATVLS_1 char(10), /* <Optional> Category 1 */
@I_vUSCATVLS_2 char(10), /* <Optional> Category 2 */
@I_vUSCATVLS_3 char(10), /* <Optional> Category 3 */
@I_vUSCATVLS_4 char(10), /* <Optional> Category 4 */
@I_vUSCATVLS_5 char(10), /* <Optional> Category 5 */
@I_vUSCATVLS_6 char(10), /* <Optional> Category 6 */
@I_vKPCALHST tinyint, /* <Optional> Maintain History - Calendar Year: 0=Do Not Keep History; 1=Keep History; Default=0 on new record */
@I_vKPERHIST tinyint, /* <Optional> Maintain History - Fiscal Year: 0=Do Not Keep History; 1=Keep History; Default=0 on new record */
@I_vKPTRXHST tinyint, /* <Optional> Maintain History - Transaction: 0=Do Not Keep History; 1=Keep History; Default=0 on new record */
@I_vKPDSTHST tinyint, /* <Optional> Maintain History - Distribution: 0=Do Not Keep History; 1=Keep History; Default=0 on new record */
@I_vIVIVACTNUMST varchar(75), /* <Optional> Inventory Account */
@I_vIVIVOFACTNUMST varchar(75), /* <Optional> Inventory Offset Account */
@I_vIVCOGSACTNUMST varchar(75), /* <Optional> Cost of Goods Sold Account */
@I_vIVSLSACTNUMST varchar(75), /* <Optional> Sales Account */
@I_vIVSLDSACTNUMST varchar(75), /* <Optional> Markdowns Account */
@I_vIVSLRNACTNUMST varchar(75), /* <Optional> Sales Returns Account */
@I_vIVINUSACTNUMST varchar(75), /* <Optional> In Use Account */
@I_vIVINSVACTNUMST varchar(75), /* <Optional> In Service Account */
@I_vIVDMGACTNUMST varchar(75), /* <Optional> Damaged Account */
@I_vIVVARACTNUMST varchar(75), /* <Optional> Variance Account */
@I_vDPSHPACTNUMST varchar(75), /* <Optional> Drop Ship Items Account */
@I_vPURPVACTNUMST varchar(75), /* <Optional> Purchase Price Variance Account */
@I_vUPPVACTNUMST varchar(75), /* <Optional> Unrealized Purchase Price Variance Account */
@I_vIVRETACTNUMST varchar(75), /* <Optional> Inventory Returns Account */
@I_vASMVRACTNUMST varchar(75), /* <Optional> Assembly Variance Account */
@I_vKTACCTSR smallint, /* <Optional> Cost of Good Sold Account: 0=From Component Item; 1=From Kit Item; Default=0 on new record */
@I_vPRCHSUOM char(8), /* <Optional> Default Purchasing Unit of Measure */
@I_vRevalue_Inventory tinyint, /* <Optional> Revalue Inventory for Cost Variance: 0=Do Not Revalue; 1=Revalue; Default=0 on new record */
@I_vTolerance_Percentage numeric(19,2), /* <Optional> Tolerance Percentage: Valid values=.001 to 9999.999 */
@I_vPRICMTHD smallint, /* <Optional> Price Method: 1=Currency Amount; 2=% of List Price; 3=%Markup-Current Cost; */
/* 4=%Markup-Standard Cost; 5=%Margin-Current Cost,6=%Margin-Standard Cost; Default=1 */
@I_vPriceGroup char(10), /* <Optional> Price Group */
@I_vUseQtyOverageTolerance tinyint, /* <Optional> Use Qty Overage Tolerance */
@I_vUseQtyShortageTolerance tinyint,/* <Optional> Use Qty Shortage Tolerance */
@I_vQtyOverTolerancePercent int, /* <Optional> Qty Over Tolerance Percent */
@I_vQtyShortTolerancePercent int, /* <Optional> Qty Short Tolerance Percent */
@I_vRequesterTrx smallint, /* <Optional> Requester Transaction: 0=False; 1=True - if true than populates Requester shadow table */
@I_vUSRDEFND1 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND2 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND3 char(50), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND4 varchar(8000), /* <Developer> User Defined field - developer use only */
@I_vUSRDEFND5 varchar(8000) /* <Developer> User Defined field - developer use only */
--copy whatever fields are in the parameters here. If the parameter is not used, it will be NULL and it's value will default
EXEC taUpdateCreateItemRcd
@I_vITEMNMBR = @I_vITEMNMBR,
@I_vITEMDESC = @I_vITEMDESC,
@I_vITMGEDSC = @I_vITMGEDSC,
@I_vITMCLSCD = @I_vITMCLSCD,
@I_vITEMTYPE = @I_vITEMTYPE,
@I_vUOMSCHDL = @I_vUOMSCHDL,
@I_vITEMSHWT = @I_vITEMSHWT,
@I_vLOCNCODE = @I_vLOCNCODE,
@I_vUseItemClass = @I_vUseItemClass,
@I_vUpdateIfExists = @I_vUpdateIfExists,
@O_iErrorState = @O_iErrorState OUT,
@oErrString = @oErrString OUT
--sample code on how to deal with errors
IF @O_iErrorState > 0 BEGIN
SELECT @ErrorDesc = tec.ErrorDesc
FROM dynamics..taErrorCode tec
WHERE tec.ErrorCode = @O_iErrorState
END ELSE BEGIN
SELECT @ErrorDesc = ''
end
SELECT @O_iErrorState AS ErrorState, @oErrString AS ErrString, @ErrorDesc AS ErrorDesc
GO
grant exec on dd_taUpdateCreateItemRcdWrapper to public
-- sp_sps 't'