We help IT Professionals succeed at work.

Store procedure not executing fully

118 Views
Last Modified: 2018-09-06
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

Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
Where is the @ID parameter in your first proc call being set?
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
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 ?
ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
*laughing* thanks for the acknowledgements
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.