How to Design Event Alerts:
Procedure of design of Event Alert is same as Periodic Alert.Just you Have to choose
Event Tab and give Event Details. Application Name and Table Name and Click on Check box “After Insert” and “ After Update” according to Requirment and rest of Procedure is same.
Practical Probem and Solution During Design Event Alerts:
We have to Design “Item Attribute Update” in Toshiba, Sydney Projects.Requirment of Alert is that if any item Comes in Inventory Email should sent to MIS USER. We developed Event Alerts. But when we are inserting new record in MTL_SYSTEM_ITEMS_B our Alert is Not Fired.
Solution: Solution of Problem is :
GO TO Alert Details and in Alert Details Forms Click on Installation Tab and give the name of Operating Units for which You Want to Design Alerts.
Query Event Alert:
SELECT :ROWID,
segment1,
organization_id,
inventory_item_id
into
&rowid,
&segment1,
&organization_id,
&inventory_item_id
from mtl_system_items_b
where rowid= :ROWID
Query Periodic Alert with time interval:
select
rsh.receipt_num, msi.segment1, rsl.quantity_shipped, sum(rcv.QUANTITY),
decode(msi.organization_id,184,
'RBatra@toshiba-tap.com jdunnicliff@toshiba-tap.com dmorris@toshiba-tap.com nhirapetians@toshiba-tap.com', 195,'wzarb@Toshiba-Tap.com pbarker@Toshiba-Tap.com kwaterstone@Toshiba-Tap.com smarffy@toshiba-tap.com')
INTO
&RECEIPT_NUM,
&SEGMENT1,
&QUANTITY_SHIPPED,
&QUANTITY_RECEIVED,
&TO_RCP
from
mtl_system_items msi,
rcv_shipment_lines rsl,
RCV_TRANSACTIONS rcv,
rcv_shipment_headers rsh
where
trunc(rsh.creation_date) > '01-jan-90'
and rsh.SHIPMENT_HEADER_ID =rcv.SHIPMENT_HEADER_ID
and rsl.shipment_header_id = rsh.shipment_header_id
and rsl.SHIPMENT_LINE_ID =rcv.SHIPMENT_LINE_ID
and rcv.TRANSACTION_TYPE ='DELIVER'
and msi.organization_id = rsl.to_organization_id
and msi.inventory_item_id = rsl.item_id
and msi.organization_id in(184,195)
and rcv.quantity >0
and rcv.last_update_date >=
to_date(:DATE_LAST_CHECKED, 'DD-MON-YYYY HH24:MI:SS') - (30/(24*60*60))
group by rsh.receipt_num, msi.segment1, rsl.quantity_shipped,msi.organization_id
CREATE OR REPLACE PACKAGE BODY cinv_update_msib AS
PROCEDURE update_row( errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_organization_id NUMBER,
p_inventory_item_id NUMBER) AS
l_serial_control_code NUMBER:=NULL;
BEGIN
IF p_organization_id IN (195, 200, 184, 192) THEN
SELECT msib.serial_number_control_code
INTO l_serial_control_code
FROM mtl_system_items_b msib,
mtl_parameters mp
WHERE msib.inventory_item_id = p_inventory_item_id
AND msib.organization_id = mp.master_organization_id
AND mp.organization_id = p_organization_id;
IF l_serial_control_code <> 1 THEN
UPDATE mtl_system_items_b
SET serial_number_control_code = 6
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
END IF;
ELSE
NULL;
END IF;
IF p_organization_id IN (195, 200, 184, 187, 189, 188, 190, 192) THEN
UPDATE mtl_system_items_b msib
SET msib.atp_flag = 'Y'
WHERE msib.organization_id = p_organization_id
AND msib.inventory_item_id = p_inventory_item_id;
ELSE
NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Process Failed:');
--NULL;
END update_row;
END;
/
Post a Comment