Solved

Check Pre Posting on purchase order release

  • 5 November 2020
  • 6 replies
  • 84 views

Badge +3

Hi,

Has anyone created an event that checks that Pre posting has been added to each PO line when they are released?

I have created one that works OK on the PO header, but get the dreaded Mutating trigger when i try to run this on the lines.

Thanks,

Mick.

icon

Best answer by johnw66 5 November 2020, 18:20

@MickD 

See example below:

 

DECLARE margin_ DECIMAL;

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR c_get_margin_ IS
SELECT CASE WHEN 
SUM(Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)) <> 0 
THEN  ROUND(100/SUM(Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)) * SUM(Order_Quotation_Line_API.Get_Quot_Line_Contribution(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)),2) ELSE 0 END FROM ORDER_QUOTATION_LINE_TAB WHERE QUOTATION_NO = '&NEW:QUOTATION_NO';

BEGIN
OPEN c_get_margin_ ;
FETCH c_get_margin_ INTO margin_ ;
CLOSE c_get_margin_;
IF margin_ < 40 THEN
                Error_SYS.Record_General('OrderQuotation', 'CHECKMARGIN:  The Sales Quotation Margin % is less than 40%. Quotation Lines where the "Contribution Margin Rate" is below 40% will need to be Approved.' );
END IF;
END;

 

Best Regards

 

John

View original

6 replies

Userlevel 5
Badge +12

Have you tried using the following:

 

PRAGMA AUTONOMOUS_TRANSACTION;

 

By adding the above got me around the mutating trigger.

Badge +3

Hi John,

I did you your post regarding this earlier, but I’m unsure how to use this, do you have an example please?

 

Regards,

Mick.

Userlevel 5
Badge +12

@MickD 

See example below:

 

DECLARE margin_ DECIMAL;

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR c_get_margin_ IS
SELECT CASE WHEN 
SUM(Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)) <> 0 
THEN  ROUND(100/SUM(Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)) * SUM(Order_Quotation_Line_API.Get_Quot_Line_Contribution(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO)),2) ELSE 0 END FROM ORDER_QUOTATION_LINE_TAB WHERE QUOTATION_NO = '&NEW:QUOTATION_NO';

BEGIN
OPEN c_get_margin_ ;
FETCH c_get_margin_ INTO margin_ ;
CLOSE c_get_margin_;
IF margin_ < 40 THEN
                Error_SYS.Record_General('OrderQuotation', 'CHECKMARGIN:  The Sales Quotation Margin % is less than 40%. Quotation Lines where the "Contribution Margin Rate" is below 40% will need to be Approved.' );
END IF;
END;

 

Best Regards

 

John

Badge +3

John,

Many thanks, got around to trying this today and it looks like its fixed the issue and the event is now working as expected.

 

Thanks again.

Mick.

Userlevel 2
Badge +5

Hi @MickD ,

 

Would you be willing to share you code for stopping pre-postings on lines. Something we have been on about for a while - just never managed to get it quite right.

 

Thansk,


Matthew

Badge +3

Hi,

I’m running the event against the  PurchaseOrderLinePart LU and PURCHASE_ORDER_LINE_TAB.

Below is the code i used, there maybe a better way to do this ( I’m not an expert ) but adding  PRAGMA AUTONOMOUS_TRANSACTION;  fixed the mutating trigger ( thanks @johnw66  )

 

‘code_a is NULL’ checks to see if any pre posting has been entered.

INVENTORY_PART_DB = ‘FALSE’ - we’re only looking for non inventory parts at the moment.

 

Regards,

Mick.

 

 

 

Reply