/* Begin_Procs taSopLineIvcInsertPre */
if exists (select * from dbo.sysobjects where id = Object_id('dbo.taSopLineIvcInsertPre') and type = 'P')
begin
drop proc dbo.taSopLineIvcInsertPre
end
go
create procedure dbo.taSopLineIvcInsertPre
/*
**********************************************************************************************************
* (c) 2004 Microsoft Business Solutions, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: taSopLineIvcInsertPre
*
* SANSCRIPT NAME: NA
*
* PARAMETERS:
*
* DESCRIPTION: TA SOP Line Insert Integration Stored Procedure
*
* TABLES:
* Table Name Access
* ========== ======
*
* PROCEDURES CALLED:
*
* DATABASE: Company
*
* RETURN VALUE:
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
* Date Who Comments
* ------------- -------- -------------------------------------------------
*
*
*********************************************************************************************************************************************************
*
*********************************************************************************************************************************************************
*/
@I_vSOPTYPE smallint output, /* 1=QUOTE,2=ORDER,3=INVOICE,4=RETURN,5=BKORDER,6=FULFILLMENT ORDER - Required */
@I_vSOPNUMBE char(21) output, /* Invoice Number - Required */
@I_vCUSTNMBR char(15) output, /* Customer Number - Required */
@I_vDOCDATE datetime output, /* Invoice Date */
@I_vUSERDATE datetime output, /* <Optional> User Date - used to determine if a lot is expired, defaults to system date */
@I_vLOCNCODE char(10) output, /* Will use default from IV if empty */
@I_vITEMNMBR char(30) output, /* Item Number - Required */
@I_vAutoAssignBin smallint output, /* Auto Assign Bin - either the SOFULFILLMENT or SORETURN bin from item site combination or from site default */
@I_vUNITPRCE numeric(19,5) output, /* Unit Price for Item */
@I_vXTNDPRCE numeric(19,5) output, /* Extended Price for line Item */
@I_vQUANTITY numeric(19,5) output, /* Quantity to Invoice for SOPTYPE=1,2&5; Billed Qty for SOPTYPE=3 */
@I_vMRKDNAMT numeric(19,5) output, /* Markdown amount */
@I_vMRKDNPCT numeric(19,2) output, /* Markdown Percent */
@I_vCOMMNTID char(15) output, /* Comment ID */
@I_vCOMMENT_1 char(50) output, /* not required */
@I_vCOMMENT_2 char(50) output, /* not required */
@I_vCOMMENT_3 char(50) output, /* not required */
@I_vCOMMENT_4 char(50) output, /* not required */
@I_vUNITCOST numeric (19,5) output, /* Used only for NON IV items */
@I_vPRCLEVEL char(10) output, /* Price level */
@I_vITEMDESC char(100) output, /* Item description */
@I_vTAXAMNT numeric(19,5) output, /* Tax amount on the line item */
@I_vQTYONHND numeric(19,5) output, /* Return Quantity Type: On Hand */
@I_vQTYRTRND numeric(19,5) output, /* Return Quantity Type: Returned */
@I_vQTYINUSE numeric(19,5) output, /* Return Quantity Type: In Use */
@I_vQTYINSVC numeric(19,5) output, /* Return Quantity Type: In Service */
@I_vQTYDMGED numeric(19,5) output, /* Return Quantity Type: Damaged */
@I_vNONINVEN smallint output, /* Added on 7/17/00 to allow for NON-IV items */
@I_vLNITMSEQ int output, /* Added on 10/23/2000 to allow line seq. num. as an input parameter */
@I_vDROPSHIP smallint output, /* Drop Ship Flag 1=DropShip */
@I_vQTYTBAOR numeric(19,5) output, /* Added on 12/06/00 for Quantity to Back Order */
/* Remaining @I_QUANTITY will be set to the field QTYTOINV */
@I_vDOCID char(15) output, /* Document ID, key field in SOP40200 - optional */
@I_vSALSTERR char(15) output, /* <Required/Optional> Sales Territory */
@I_vSLPRSNID char(15) output, /* <Required/Optional> Sales Person ID, no commissions will be calculated if blank */
@I_vITMTSHID char(15) output, /* Item Tax schedule ID */
@I_vIVITMTXB smallint output, /* IV Item Taxable - user can change item tax option per line 1=tax 2=nontax 3=based on cust */
@I_vTAXSCHID char(15) output, /* Tax Schedule ID <Optional> can be used to override the default delivery method tax schedule */
@I_vPRSTADCD char(15) output, /* Primary Shipto Address Code */
@I_vShipToName char(64) output, /* ShipToName */
@I_vCNTCPRSN char(60) output, /* Contact Person */
@I_vADDRESS1 char(60) output, /* Customer Address One per shipto line item */
@I_vADDRESS2 char(60) output, /* Customer Address Two per shipto line item */
@I_vADDRESS3 char(60) output, /* Customer Address three per shipto line item */
@I_vCITY char(35) output, /* City per shipto line item */
@I_vSTATE char(29) output, /* State per shipto line item */
@I_vZIPCODE char(10) output, /* ZipCode per shipto line item */
@I_vCOUNTRY char(60) output, /* Country per shipto line item */
@I_vPHONE1 char(21) output, /* Phone 1 per shipto line item */
@I_vPHONE2 char(21) output, /* Phone 2 per shipto line item */
@I_vPHONE3 char(21) output, /* Phone 3 per shipto line item */
@I_vFAXNUMBR char(21)output, /* Fax per shipto line item */
@I_vEXCEPTIONALDEMAND tinyint output, /* Resource Planning */
@I_vReqShipDate datetime output, /* Requested Ship Date */
@I_vFUFILDAT datetime output, /* Fulfill Date */
@I_vACTLSHIP datetime output, /* Actual Ship Date */
@I_vSHIPMTHD char(15) output, /* Shipping Method */
@I_vINVINDX varchar(75) output, /* Inventory Account Number String */
@I_vCSLSINDX varchar(75) output, /* Cost of Goods Sold Account Number String */
@I_vSLSINDX varchar(75) output, /* Sales Account Number String */
@I_vMKDNINDX varchar(75) output, /* Markdowns Account Number String */
@I_vRTNSINDX varchar(75) output, /* Returns Account Number String */
@I_vINUSINDX varchar(75) output, /* In Use Account Number String */
@I_vINSRINDX varchar(75) output, /* In Service Account Number String */
@I_vDMGDINDX varchar(75) output, /* Damaged Account Number String */
@I_vAUTOALLOCATESERIAL int output, /* Auto Allocate Serial Numbers on the Fly 0 = Auto & 1 = Manual & 2 = Mixed */
@I_vAUTOALLOCATELOT int output, /* Auto Allocate Lot Numbers on the Fly 0 = Auto & 1 = Manual & 2 = Mixed */
@I_vGPSFOINTEGRATIONID char(30) output, /* Front Office Integration ID <optional> */
@I_vINTEGRATIONSOURCE smallint output, /* Integration Source <optional> */
@I_vINTEGRATIONID char(30) output, /* Integration ID <optional> */
@I_vRequesterTrx smallint output, /* Requester Transaction - 0=false,1=true - if true than populates Requester shadow table */
@I_vQTYCANCE numeric(19,5) output, /* Quantity Canceled - will subtract from the @I_vQUANTITY passed in */
@I_vQTYFULFI numeric(19,5) output, /* Quantity Fulfilled */
@I_vALLOCATE smallint output, /* Allocates quantities - 0=use @I_vDOCID default; 1=Allocate quantities */
@I_vUpdateIfExists smallint output, /* Used to Modify existing lines - 1=Modify - entire line needs to be resent */
@I_vRecreateDist smallint output, /* Used only if @I_vUpdateIfExists = 1 - determines if distributions auto recreate */
@I_vQUOTEQTYTOINV numeric(19,5) output, /* Quote Qty to Invoice - added to @I_vQUANTITY and @I_vQTYCANCE for total quote amount */
@I_vTOTALQTY numeric(19,5) output, /* Total Line Qty - if not specified then it is calculated based on other quantities passed in */
@I_vCMMTTEXT varchar(500) output, /* Comment Text; only use if not using @I_vCOMMENT_1 thu @I_vCOMMENT_4 */
@I_vKitCompMan smallint output, /* Manually passing Kit Components - 0=False; 1=True - If 1 then @I_vLNITMSEQ is required */
@I_vDEFPRICING int output, /* 1 = unit price and extended price will be defaulted */
@I_vDEFEXTPRICE int output, /* Extended price will default based on the Unit Price and quantity passed in */
@I_vCURNCYID char(15) output, /* Currency ID <Optional> - 02/10/02 - Added for Multi-Currency */
@I_vUOFM char(8) output, /* Unit of Measure */
@I_vIncludePromo smallint output, /* Include Extended Pricing Promotion, including free items */
@I_vCKCreditLimit tinyint output, /* 0=Do Not check limit; 1=check limit - this is on a per line basis, or you can just do at Hdr level */
@I_vQtyShrtOpt smallint output, /* Qty Shortage Opt.: 1=Sell Balance; 2=Override Shortage; 3=BO All; 4=BO Balance; 5=Cancel All; 6=Cancel Balance */
@I_vUSRDEFND1 char(50) output, /* User Defined field - developer use only */
@I_vUSRDEFND2 char(50) output, /* User Defined field - developer use only */
@I_vUSRDEFND3 char(50) output, /* User Defined field - developer use only */
@I_vUSRDEFND4 varchar(8000) output, /* User Defined field - developer use only */
@I_vUSRDEFND5 varchar(8000) output, /* User Defined field - developer use only */
@O_iErrorState int output, /* Return value: 0=No Errors, 1=Error Occurred */
@oErrString varchar(255) output /* Return Error Code List */
as
set nocount on
select @O_iErrorState = 0
/* Create Custom Business Logic */
declare @Price numeric(19,5)
declare @PriceLevel varchar(11)
declare @markup numeric(19,5)
--GET THE PRICE LEVEL
select @PriceLevel = prclevel
from RM00101p c
where CUSTNMBR = @I_vCUSTNMBR
--GET THE UOMPRICE
select @Price = UOMPRICE
from IV00108 ip
where ip.ITEMNMBR = @I_vITEMNMBR
and PRCLEVEL = @PriceLevel
--GET THE MARKUP
select @markup = rp.MARKUP
from RM00101P rp
where rp.CUSTNMBR = @I_vCUSTNMBR
and rp.PRCLEVEL = @PriceLevel
--COMPUTE THE PRICE
select @Price = @Price * (1.00 + (@MARKUP/10000.00))
--SET THE NEW VALUES TO ECONNECT
select @I_vUNITPRCE = @Price,
@I_vXTNDPRCE = @I_vQUANTITY * @Price
/* End Create Custom Business Logic */
return (@O_iErrorState)
go
grant execute on dbo.taSopLineIvcInsertPre to DYNGRP
go
/* End_Procs taSopLineIvcInsertPre */