/* Begin_Procs taPoLinePost */
if exists (select * from dbo.sysobjects where id = Object_id('dbo.taPoLinePost') and type = 'P')
begin
drop proc dbo.taPoLinePost
end
go
create procedure dbo.taPoLinePost
/*
**********************************************************************************************************
* (c) 2004 Microsoft Business Solutions, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: taPoLinePost
*
* SANSCRIPT NAME: NA
*
* PARAMETERS:
*
* DESCRIPTION: TA PO Line Post 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_vPOTYPE smallint, /* PO Type - 1 = Standard, 2 = Drop Ship, 3 = Blanket, 4 = Drop Ship Blanket */
@I_vPONUMBER char(17), /* PO Number - Unique identifier for the PO */
@I_vDOCDATE datetime, /* Document Date Date of the PO <Optional/Required> Required for PA */
@I_vVENDORID char(15), /* Vendor ID - ID of the vendor you're purchasing items from */
@I_vLOCNCODE char(10), /* Location Code - ID of the site for which you're purchasing the items */
@I_vVNDITNUM char(30), /* Vendor Item Number */
@I_vITEMNMBR char(30), /* Item Number */
@I_vQUANTITY numeric (19,5), /* Quantity - Total Quantity for line Item */
@I_vQTYCANCE numeric (19,5), /* Quantity Cancelled - Quantity that is to be canceled */
@I_vFREEONBOARD smallint, /* Terms of sale - identify point at which ownership of goods passes to buyer. 1=None,2=Origin,3=Destination */
@I_vREQSTDBY char(20), /* Requested by - Person or department who requested this line item */
@I_vCOMMNTID char(15), /* Comment ID */
@I_vCOMMENT_1 char(50), /* Comment part 1 */
@I_vCOMMENT_2 char(50), /* Comment part 2 */
@I_vCOMMENT_3 char(50), /* Comment part 3 */
@I_vCOMMENT_4 char(50), /* Comment part 4 */
@I_vREQDATE datetime, /* Required date by which the items must be received */
@I_vRELEASEBYDATE datetime, /* Date the purchase order line needs to be released to the vendor to receive the item by the Required Date */
@I_vPRMDATE datetime, /* Date the vendor promised you would receive merchandise or services */
@I_vPRMSHPDTE datetime, /* Date the vendor promised the merchandise would be shipped */
@I_vNONINVEN smallint, /* 0=Inventory item, 1=Non Inventory item */
@I_vIVIVINDX int, /* Inventory Account Index */
@I_vInventoryAccount varchar(75), /* Inventory Account Number String */
@I_vITEMDESC char(100), /* Item Description for the line item */
@I_vUNITCOST numeric (19,5), /* Unit Cost for the item in the specified unit of measure */
@I_vVNDITDSC char(100), /* Vendor Item Description for the line item */
@I_vUOFM char(8), /* Unit of Measure in which this item is being purchased */
@I_vPurchase_IV_Item_Taxable smallint, /* Purchase Inventory Item Taxable 1=Taxable,2=Nontaxable,3=Base on Vendor */
@I_vPurchase_Item_Tax_Schedu char(15), /* Purchase Item Tax Schedule - blank unless @I_vPurchase_IV_Item_Taxable = 1 */
@I_vPurchase_Site_Tax_Schedu char(15), /* Purchase Site Tax Schedule */
@I_vBSIVCTTL smallint, /* Based on invoice total */
@I_vTAXAMNT numeric (19,5), /* Tax Amount */
@I_vBCKTXAMT numeric (19,5), /* Backout Tax Amount */
@I_vLanded_Cost_Group_ID char(15), /* Landed Cost Group ID */
@I_vPLNNDSPPLID smallint, /* Planned Supplier ID */
@I_vSHIPMTHD char(15), /* Shipping Method */
@I_vBackoutTradeDiscTax numeric (19,5), /* Backout Trade Discount Tax Amount */
@I_vPOLNESTA smallint, /* PO Line Status - (valid: 1=new, 2=released 3=change order, 4=received, 5=closed, 6 =cancelled) */
@I_vCMMTTEXT varchar(500), /* Comment Text; only use if not using @I_vCOMMENT_1 thu @I_vCOMMENT_4 */
@I_vORD int, /* Sequence number used to track each item. Must be unique per PO */
@I_vCUSTNMBR char(15), /* Customer Number <Required for drop ships or blanket drop ships> */
@I_vADRSCODE char(15), /* Address Code */
@I_vCMPNYNAM char(64), /* Name */
@I_vCONTACT char(60), /* Contact */
@I_vADDRESS1 char(60), /* Address Line 1 */
@I_vADDRESS2 char(60), /* Address Line 2 */
@I_vADDRESS3 char(60), /* Address Line 3 */
@I_vCITY char(35), /* City */
@I_vSTATE char(29), /* State */
@I_vZIPCODE char(10), /* Zip Code */
@I_vCCode char(6), /* Country Code */
@I_vCOUNTRY char(60), /* Country */
@I_vPHONE1 char(21), /* Phone 1 */
@I_vPHONE2 char(21), /* Phone 2 */
@I_vPHONE3 char(21), /* Phone 3 */
@I_vFAX char(21), /* Fax */
@I_vCURNCYID char(15), /* Currency ID <Optional> */
@I_vProjNum char(15), /* Project Number, for use with Project Accounting only */
@I_vCostCatID char(15), /* Cost Category ID, for use with Project Accounting only */
@I_vLineNumber int, /* Line Number, Blanket PO's */
@I_vUpdateIfExists smallint, /* Used to Add/Modify existing documents - 1=Add/Modify - partial updates allowed */
@I_vNOTETEXT varchar(8000), /* Notes field */
@I_vRequesterTrx smallint, /* Requester Transaction <Optional> - 0=false,1=true - if true then populates Requester shadow table */
@I_vUSRDEFND1 char(50), /* User Defined field - developer use only */
@I_vUSRDEFND2 char(50), /* User Defined field - developer use only */
@I_vUSRDEFND3 char(50), /* User Defined field - developer use only */
@I_vUSRDEFND4 varchar(8000), /* User Defined field - developer use only */
@I_vUSRDEFND5 varchar(8000), /* User Defined field - developer use only */
@O_iErrorState int output, /* Error Code */
@oErrString varchar(255) output /* List of errors encountered */
as
set nocount on
select @O_iErrorState = 0
/*
@I_vUSRDEFND1 has sopnumbe
@I_vUSRDEFND2 has the line item sequence
@I_vPONUMBER
@I_vORD
*/
--delete the SOP to POP linking table for this sales order
delete SOP60100 where SOPNUMBE = @I_vUSRDEFND1 and LNITMSEQ = @I_vUSRDEFND2
--re-insert the lines
insert into SOP60100 (SOPNUMBE , SOPTYPE, LNITMSEQ , CMPNTSEQ , PONUMBER , ORD , QTYONPO , QTYONPOBASE , QTYBSUOM , LOCNCODE)
values (@I_vUSRDEFND1, 2 , @I_vUSRDEFND2, 0 , @I_vPONUMBER, @I_vORD, @I_vQUANTITY, @I_vQUANTITY, @I_vQUANTITY, @I_vLOCNCODE)
--fix some fields in the PO Lines table
update POP10110 set
QTYCMTBASE = @I_vQUANTITY,
QTYUNCMTBASE = 0,
LINEORIGIN = 3
where PONUMBER = @I_vPONUMBER
and ORD = @I_vORD
--fix some fields in the Sales Order Lines table
update SOP10200 set
QTYONPO = @I_vQUANTITY,
PURCHSTAT = 3
where SOPNUMBE = @I_vUSRDEFND1
and SOPTYPE = 2
return (@O_iErrorState)
go
grant execute on dbo.taPoLinePost to DYNGRP
go
/* End_Procs taPoLinePost */