Store procedure not executing fully

Long Le
Long Le used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Where is the @ID parameter in your first proc call being set?
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Senior Developer
Commented:
Correct. But it goes further than that. Consider the user enters also the positions. Then 30 minutes later he discovers an error in it, removes a line and adds a new line...

Thus it is much better to establish a process which results after some checks in a fixed or ready state of the purchase order. Only then a customer order can be created. Thus using a stored procedure, either executed manually or scheduled in the background.

The "fixed" state also includes, that you can no longer modify the purchase order. Otherwise you need to cancel (when it was already sent) or modify the customer order. This could be cumbersome to implement in software and as business process.

In the end it is only about the real business process. Thus review it with your customer to get it right.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
*laughing* thanks for the acknowledgements

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial