troubleshooting Question

Store procedure not executing fully

Avatar of Long Le
Long Le asked on
SoftwareWindows Server 2012Microsoft SQL Server
8 Comments1 Solution126 ViewsLast Modified:
Trying to create a script that will create a customer order and the lines for that order when our other branch office creates a purchase order.

The Tables I am working with are:
Purchase_Order
Purc_Order_Line
Customer_Order
Cust_Order_Line

So when they enter in an order on the software it inserts that into the purchase_order table. So I
added a code into the Insert trigger

BEGIN
If @VENDOR_ID = 'RIP01' AND @SITE_ID = '111RIPUK'
EXEC dbo.POIMPORT_UK @PO_NUM = @ID
END

So that works pretty well and executes the POIMPORT_UK Stored procedure but only some of the query works.
When the insert trigger get triggered the Customer Order Gets created, The Next Order ID advances to the next number, PO gets updated with the Reference customer order Id, and email gets sent out. The one that doesnt seem work automatically is the query that creates the lines on the customer order.

If I manually run the exec command manually and specify the PO #, all of the code works.
EXEC dbo.POIMPORT_UK @PO_NUM = '#####'    



Stored Procedure
/****** Object:  StoredProcedure [dbo].[POIMPORT_UK]    Script Date: 9/5/2018 11:43:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[POIMPORT_UK]
@PO_NUM NVARCHAR(15)
AS
SET NOCOUNT ON
--For synchronization machine id placement


This Query Creates the Customer Order
INSERT INTO CUSTOMER_ORDER(ID, CUSTOMER_ID, SITE_ID, SELL_RATE, BUY_RATE, 
TERMS_NET_TYPE, TERMS_DISC_TYPE, FREIGHT_TERMS, BACK_ORDER, STATUS, POSTING_CANDIDATE, 
MARKED_FOR_PURGE, CURRENCY_ID, CUSTOMER_PO_REF, WAREHOUSE_ID, DESIRED_SHIP_DATE) 
SELECT (SELECT Next_number FROM NEXT_NUMBER_GEN where rowid = '72'),'ABECO','101RIPUS',
'1.0','1.0','A','A','P','N','F','N','N','(USD) $',ID,'CROM',Getdate() FROM PURCHASE_ORDER 
WHERE ID = @PO_NUM 

This Query Copies the lines from Purc_Order_Line into Cust_order_line
INSERT INTO CUST_ORDER_LINE 
CUST_ORDER_ID,LINE_NO,PART_ID,LINE_STATUS,ORDER_QTY,
USER_ORDER_QTY,SELLING_UM,UNIT_PRICE,SITE_ID,MISC_REFERENCE,
PRODUCT_CODE,COMMODITY_CODE,DRAWING_ID,DRAWING_REV_NO,
MAT_GL_ACCT_ID,LAB_GL_ACCT_ID,BUR_GL_ACCT_ID,SER_GL_ACCT_ID,
GL_REVENUE_ACCT_ID,TOTAL_AMT_ORDERED,ENTERED_BY,WAREHOUSE_ID) 
SELECT(select id from customer_order where CUSTOMER_PO_REF = PURC_ORDER_LINE.
PURC_ORDER_ID),LINE_NO,PURC_ORDER_LINE.PART_ID,'A',ORDER_QTY,
USER_ORDER_QTY,PURCHASE_UM,PURC_ORDER_LINE.UNIT_PRICE,'101RIPUS',
PART.DESCRIPTION,PART.PRODUCT_CODE,PART.COMMODITY_CODE,PART.DRAWING_ID,
PART.DRAWING_REV_NO,PART_SITE.MAT_GL_ACCT_ID,PART_SITE.LAB_GL_ACCT_ID,
PART_SITE.BUR_GL_ACCT_ID,PART_SITE.SER_GL_ACCT_ID,
(SELECT REV_GL_ACCT_ID FROM PRODUCT WHERE CODE = PART.PRODUCT_CODE),
USER_ORDER_QTY*PURC_ORDER_LINE.UNIT_PRICE,
'SYSADM',PART_SITE.PRIMARY_WHS_ID From PURC_ORDER_LINE  
Inner Join PART On PART.ID = PURC_ORDER_LINE.PART_ID 
Inner Join PART_SITE On PART_SITE.PART_ID = PART.ID 
WHERE PURC_ORDER_ID = @PO_NUM AND PART_SITE.sITE_ID = '101RIPUS'


This Query advances the next available Order ID
Update NEXT_NUMBER_GEN Set Next_number = Next_number + 1 where rowid = '72'

This Query adds the Customer Order ID into a reference field on the purchase Order.
Update PURCHASE_ORDER Set SALES_ORDER_ID = (select id from customer_order where CUSTOMER_PO_REF = @PO_NUM) where id = @PO_NUM

Declare @PO NVARCHAR(15) = @PO_NUM
Declare @CO NVARCHAR(15) = (select id from customer_order where CUSTOMER_PO_REF = @PO)
Declare @MYBODY VARCHAR(MAX) = 'Hello CSR,
A new internal order has been created for UK.

US US Customer Order Number: ' + @CO +
'

Ripley UK Purchase Order Number: ' + @PO
EXEC msdb.dbo.sp_send_dbmail @profile_name='office365',
@recipients='orders@test.com',
@subject='UK - Internal Order',
@body= @MYBODY

 
  SET NOCOUNT OFF
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros