Custom Trigger for Mktg Expense allocate, no COGS will generate, But Stock will raised.

Updated :

CREATE OR REPLACE TRIGGER APPS.rcv_transactions_cust_trigr
    after insert on RCV_TRANSACTIONS
    for each row
WHEN (
new.transaction_type      =  'DELIVER' and
       new.destination_type_code ='EXPENSE'
      )
     
     
declare

    A number(1);

      l_orgid  po_headers_all.org_id%type;
      l_params wf_parameter_list_t;
      l_clntyp varchar2(30);
      l_docnum varchar2(100);
     
 
    v_account_code_id number(6);
    v_inventory_item_id number(10);
    uom_code varchar2(6);
    v_inventory_location_id number(10);
    v_subinventory_code varchar2(10);
    v_code_combination_id number(10);
    v_transaction_type_id number(8);
    v_tran_Source_Name   mtl_transactions_interface.TRANSACTION_SOURCE_NAME%type;

 /*
  ||  Here initialising the PO_Received_new record to raised Stock but no cost will generate when received the Mktg PO,
  ||  type in the inline procedure transaction_type      =  'DELIVER' and destination_type_code ='EXPENSE'
  ||  Expense will allocate to Mktg Dept, or Company wise PO amount will allocate without cogs stock will raige.
  ||
  */
  /****************************************************************************
  *     Creation HISTORY:
  *
  *         25-Mar-2016  Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.2
  *    Last Updated By :
  *         19-Sep-2016  Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.3
  *
  ****************************************************************************/


cursor ch_1 is


select distinct i.INVENTORY_ITEM_ID item_id, i.PRIMARY_UOM_CODE uom_code from   rcv_shipment_lines l, mtl_system_items_b i
where l.po_header_id=:new.po_header_id and l.ITEM_ID=i.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=102 and SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;



begin

   
    select distinct TO_ORGANIZATION_ID into l_orgid
    from  rcv_shipment_lines 
    where  po_header_id = :new.po_header_id;
   
  
  select distinct s.RECEIPT_NUM into v_tran_Source_Name from  rcv_shipment_lines sl,rcv_shipment_headers s
    where sl.SHIPMENT_HEADER_ID=s.SHIPMENT_HEADER_ID
    and  po_header_id =:new.po_header_id  and sl.SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;

      
    select material_account into v_account_code_id from mtl_parameters where organization_id=l_orgid;
   
  A:=1;
   
  begin
       select secondary_inventory_name into v_subinventory_code  from mtl_secondary_inventories where upper(secondary_inventory_name) like upper('%MK') and organization_id=l_orgid;
       
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
       A:=2;
      
   END;
    
   if (A=1) then
    
                select
                inventory_location_id into v_inventory_location_id
                from mtl_item_locations
                where subinventory_code =v_subinventory_code;
   
                for c1_rec in ch_1 loop
                  exit when ch_1%notfound;
                    begin
                    insert into mtl_transactions_interface
                    (
                    transaction_interface_id,
                    source_code,
                    source_header_id,
                    source_line_id,
                    inventory_item_id,
                    process_flag,
                    validation_required,
                    transaction_mode,
                    transaction_quantity,
                    last_update_date,
                    last_updated_by,
                    creation_date,
                    created_by,
                    organization_id,
                    transaction_date,
                    transaction_type_id,
                    distribution_account_id,
                    transaction_uom,
                    subinventory_code,
                    locator_id,
                    transaction_cost,
                    TRANSACTION_SOURCE_NAME
                    )
                    values
                    (
                    mtl_material_transactions_s.nextval,
                    'MKTG Expense Rcving Stock',  --c1_rec.source_code
                    1,
                    1,
                    c1_rec.item_id,
                    1,
                    2,
                    3,
                    :new.QUANTITY,
                    sysdate,
                    1110,
                    sysdate,
                    1110,
                    l_orgid,
                    sysdate, --c1_rec.transaction_date,
                    42,
                    v_account_code_id, --c1_rec.distribution_account_id,
                    c1_rec.uom_code,
                    v_subinventory_code,
                    v_inventory_location_id,
                    null,
                    'Rcv No : ' || v_tran_Source_Name
                    );
               
                     exception
                        when no_data_found then
                        v_inventory_item_id :=null;
                        v_inventory_location_id :=null;
                        v_subinventory_code :=null;
                        v_tran_Source_Name:=null;
                       
                    end;

                
         /*         
          exception
                    when no_data_found then
                    v_inventory_item_id :=null;
                    v_inventory_location_id :=null;
                    v_subinventory_code :=null;
                    v_tran_Source_Name:=null;
                   
                end;
             */  
               
        end loop; 
           
 end if;

end;
--/


---------------Last one ---------------------------------------------------

CREATE OR REPLACE TRIGGER APPS.rcv_transactions_cust_trigr
after insert on RCV_TRANSACTIONS
for each row
WHEN (
new.transaction_type = 'DELIVER' and
new.destination_type_code ='EXPENSE'
)
declare

l_orgid po_headers_all.org_id%type;
l_params wf_parameter_list_t;
l_clntyp varchar2(30);
l_docnum varchar2(100);



v_account_code_id number(6);
v_inventory_item_id number(10);
uom_code varchar2(6);
v_inventory_location_id number(10);
v_subinventory_code varchar2(10);
v_code_combination_id number(10);
v_transaction_type_id number(8);
v_tran_Source_Name varchar2(20);
v_tran_Source_Name mtl_transactions_interface.TRANSACTION_SOURCE_NAME%type;


/*
|| Here initialising the PO_Received_new record to raised Stock but no cost will generate when received the Mktg PO,
|| type in the inline procedure transaction_type = 'DELIVER' and destination_type_code ='EXPENSE'
|| Expense will allocate to Mktg Dept, or Company wise PO amount will allocate without cogs stock will raige.
||
*/
/****************************************************************************
* Creation HISTORY:
*
* 25-Mar-2016 Shah Mahfuz for Requirment of PRAN File Version Mahfuz_103.1
*
****************************************************************************/


cursor ch_1 is


select distinct i.INVENTORY_ITEM_ID item_id, i.PRIMARY_UOM_CODE uom_code from rcv_shipment_lines l, mtl_system_items_b i
where l.po_header_id=:new.po_header_id and l.ITEM_ID=i.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=102 and
SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;







begin


select distinct TO_ORGANIZATION_ID into l_orgid
from rcv_shipment_lines
where po_header_id = :new.po_header_id;

select distinct s.RECEIPT_NUM into v_tran_Source_Name from rcv_shipment_lines sl,rcv_shipment_headers s
where sl.SHIPMENT_HEADER_ID=s.SHIPMENT_HEADER_ID
and po_header_id =:new.po_header_id and sl.SHIPMENT_LINE_ID=:new.SHIPMENT_LINE_ID;

select material_account into v_account_code_id from mtl_parameters where organization_id=l_orgid;

select secondary_inventory_name into v_subinventory_code from mtl_secondary_inventories where
upper(secondary_inventory_name) like upper('%MK') and organization_id=l_orgid;

select
inventory_location_id into v_inventory_location_id
from mtl_item_locations
where subinventory_code =v_subinventory_code;


for c1_rec in ch_1 loop
exit when ch_1%notfound;
begin
insert into mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_header_id,
source_line_id,
inventory_item_id,
process_flag,
validation_required,
transaction_mode,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
transaction_date,
transaction_type_id,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id,
transaction_cost,
TRANSACTION_SOURCE_NAME
)
values
(
mtl_material_transactions_s.nextval,
'MK Stock Upload', --c1_rec.source_code
1,
1,
c1_rec.item_id,
1,
2,
3,
:new.QUANTITY,
sysdate,
1110,
sysdate,
1110,
l_orgid,
sysdate, --c1_rec.transaction_date,
42,
v_account_code_id, --c1_rec.distribution_account_id,
c1_rec.uom_code,
v_subinventory_code,
v_inventory_location_id,
null,
'RCV_No : ' || v_tran_Source_Name
);

exception
when no_data_found then
v_inventory_item_id :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;
v_tran_Source_Name :=null;
end;

end loop;

end;
/
/