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
Long LeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt BowlerDB team leadCommented:
Where is the @ID parameter in your first proc call being set?
0
James0628Commented:
There is no "(" after INSERT INTO CUST_ORDER_LINE.  I don't know if that will fix things, but it's a start.

 James
1
Mark WillsTopic AdvisorCommented:
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 ?
0
Determine the Perfect Price for Your IT Services

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

ste5anSenior DeveloperCommented:
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.
2
Mark WillsTopic AdvisorCommented:
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.
0
Long LeAuthor 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.
0
ste5anSenior DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
*laughing* thanks for the acknowledgements
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.