How to Block A/R invoice when landed cost is completed for a Purchase GRPO?

[vitusberny]

Hi All,

I need to block posting A/R invoice for the open GRPO (Purchased Items) without landed cost for the Document Quantity alone, this sp should not affect the prior instock items when added in A/R Invoice.

[stevediaz]

Hello

You can use the following stored procedure to block A/R invoice posting for open GRPOs without landed costs for the document quantity:

CREATE PROCEDURE BlockARInvoice(IN grpoId INT) BEGIN DECLARE hasLandedCost INT; SELECT COUNT(*) INTO hasLandedCost FROM LandedCostTable WHERE GRPO_ID = grpoId; IF hasLandedCost > 0 THEN UPDATE GRPOTable SET IsARInvoiceBlocked = 0 WHERE GRPO_ID = grpoId; ELSE UPDATE GRPOTable SET IsARInvoiceBlocked = 1 WHERE GRPO_ID = grpoId; END IF; END;

This stored procedure checks if there's a landed cost for a given GRPO. If found, it allows A/R invoice posting; otherwise, it blocks it for the document quantity. Adapt it to match your database schema.

[narayanis]

Hi,

You are talking about reverse traceability of a batch. Handling this in TN would be complex and may take time to execute in case of multiple line items with batches.

Instead, I would suggest to create a UDF at header level of GRPO to be updated after adding a landed cost through Post transaction notification and check this flag while adding sales invoice.

Check if this suits you.

Regards

[vitusberny]

thanks for the details it was helpful!!!!!!!!!!!!!!!!

[mohamedelshafei97]

you should be using validation

Stored Procedure / B1UP Validation