TRIGGER

CREATE OR REPLACE TRIGGER APPS.INV_MISC_ISSUE_BOM_COMPS
AFTER INSERT ON APPS.MTL_MATERIAL_TRANSACTIONS
FOR EACH ROW
DECLARE
ln_request_id NUMBER;
lv_count      NUMBER;
ln_user_id NUMBER;
ln_responsibility_id NUMBER;
ln_responsibility_app_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ln_user_id:=fnd_profile.value('USER_ID');
ln_responsibility_id:=fnd_profile.value('RESP_ID');
ln_responsibility_app_id :=fnd_profile.value('RESP_APPL_ID');
fnd_global.apps_initialize(ln_user_id,ln_responsibility_id,ln_responsibility_app_id);
--IF :new.transaction_type_id = 262 OR :new.transaction_type_id=42 THEN ----modified on 08-feb-2013 according to harish
IF (:new.transaction_type_id = 262 OR :new.transaction_type_id=42) and (:new.reason_id=4) THEN
--  raise_application_error(-20010,'Misc Receipt');
--262  -Miscellaneous Receipt(RG Update)
--42    --Miscellaneous Receipt
   BEGIN
     select count(jil.item_id)
     into   lv_count
     from   apps.ja_in_osp_items_hdr jih
     ,     apps.ja_in_osp_items_dtl jil
     WHERE  1=1
     AND    jih.osp_item_id     =    jil.osp_item_id
     AND    jih.organization_id=:new.organization_id
     AND    jih.osp_item_id=:new.inventory_item_id;
   END;
IF lv_count > 0 THEN
  --raise_application_error(-20010,'No Component is Defined for this FG Item');
ln_request_id:=  fnd_request.Submit_request(  'INV'
                                             ,'STX_BOM_MISC_ISSUE'
                                             ,''  -- NEW CHANGE'
                                             ,''
                                             ,FALSE
                                             ,:new.organization_id
                                             ,:new.inventory_item_id
                                             ,:new.transaction_date
                                             ,:new.transaction_quantity
                                             ,:new.subinventory_code
                                             ,:new.transaction_type_id
                                             ,:new.distribution_account_id
                                             ,:new.created_by
                                             ,:new.transaction_reference
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                            ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0),CHR(0));


 END IF; --  Checking the count

 --commit;

    END IF; --Checking the transaction_type_id
COMMIT;
END;