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.
Best answer by RutJWhalen
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
Have you tried using the following:
PRAGMA AUTONOMOUS_TRANSACTION;
By adding the above got me around the mutating trigger.
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.
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
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.
Hi
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
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
‘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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
