dear all,
We want to create an event if the quantities of materials in the purchase order exceed the quantities specified in the order during the delivery process
The code snippet for the event is provided below, but we're not receiving any emails from the event. Does anyone have any comments on this?
BEGIN
DECLARE
v_emails VARCHAR2(200);
v_subject VARCHAR2(100);
v_message VARCHAR2(4000);
v_buy_qty_due NUMBER;
v_total_qty_arrived NUMBER;
BEGIN
SELECT pola.BUY_QTY_DUE,
SUM(ria.SOURCE_QTY_TO_RECEIVE)
INTO v_buy_qty_due, v_total_qty_arrived
FROM PURCHASE_ORDER_LINE_ALL pola
LEFT JOIN RECEIPT_INFO_ALT ria ON ria.SOURCE_REF1 = pola.ORDER_NO
AND pola.LINE_NO = ria.SOURCE_REF2
AND pola.RELEASE_NO = ria.SOURCE_REF3
AND pola.PART_NO = ria.PART_NO
AND pola.CONTRACT = ria.CONTRACT;
IF v_total_qty_arrived > v_buy_qty_due THEN
v_subject := 'Teslim Alınan Miktar SAS Miktarından Fazla!';
v_message := TO_CHAR(SYSDATE, 'DD.MM.YYYY') || ' tarihinde, siparişe teslim alınan miktar SAS miktarından fazla olmuştur.
Aşağıdaki linke tıklayarak ilgili sayfaya gidebilirsiniz.
';
BEGIN
SELECT LISTAGG(DEFAULT_EMAIL, ', ') WITHIN GROUP (ORDER BY DEFAULT_EMAIL)
INTO v_emails
FROM PERSON_PUBLIC_HR_INFO
WHERE POS_CODE = '318_100_40';
EXCEPTION
WHEN OTHERS THEN
v_emails := NULL;
END;
IF v_emails IS NOT NULL THEN
command_sys.mail(
sender_ => 'IFSAPP',
from_ => 'ifs@kontekenerji.com.tr',
to_list_ => v_emails,
cc_list_ => '',
bcc_list_ => '',
subject_ => v_subject,
text_ => v_message,
attach_ => '',
rowkey_ => '',
mail_sender_ => 'MAIL_SENDER1'
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
additionally we run this event action lu:PurchaseOrder table -->PURCHASE_ORDER_TAB
