Avatar of Long Le
Long Le
 asked on

Store procedure not executing fully

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 

Open in new window


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'

Open in new window



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

Open in new window


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

Open in new window


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

Open in new window


 
  SET NOCOUNT OFF
SoftwareWindows Server 2012Microsoft SQL Server

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
Matt Bowler

Where is the @ID parameter in your first proc call being set?
James0628

There is no "(" after INSERT INTO CUST_ORDER_LINE.  I don't know if that will fix things, but it's a start.

 James
Mark Wills

Apart from the missing left bracket in the Order line.... (which should error, and doent explain it working when done manually)

Are the detail lines of the PO available at that point ?

I would imagine that PO header gets written first, then PO Details.

So, cannot call / exec the proc until the PO detail lines have materialised.

Kinda depends a lot on when the proc is being executed.

Would imagine a trigger on the PO detail lines as well to help any PO detail changes as well as initial insert.

Can you please describe the process in terms of timing and when various components materialise / executed ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ste5an

Just a comment:

I would start implementing this without triggers at all. Then you can decide later, whether triggers are the correct place to execute the procedure.
Imho triggers are the wrong place. Cause the customer order should be created, when the purchase order is fixed. Thus a trigger would be called multiple times unnecessarily before.
Mark Wills

Good point ste5an.....

Always a difficult task to keep the two in synch via triggers.

Could also run a background process that checks for new PO without a CO - afterall you say it works when invoked manually...

But dare say it would need to manage changes / deletes and not just inserts.
Long Le

ASKER
Ahh, I see you maybe once the PO is created it fires off the trigger but the POL hasn't been created yet. So it has nothing to copy. When I fire it manually everything is already there so the trigger completes.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Wills

*laughing* thanks for the acknowledgements