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;
/
/

Custom Price Transfer accross the OU

CREATE OR REPLACE PROCEDURE APPS.PRG_COSTING_PERIOD_test(PERIODID IN VARCHAR2) IS
BEGIN


Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 100, 148,'RUNNING');
COMMIT;

INSERT INTO XX_UPDATE_TRANSFER_PRICE

SELECT mtl.TRANSACTION_ID,mtl.INVENTORY_ITEM_ID,mtl.ORGANIZATION_ID ToOrg,mtl.TRANSFER_PRICE,0 NEW_TRANSFER_PRICE,
mtl.TRANSFER_ORGANIZATION_ID FromOrg, mtl.TRANSFER_PRICE,0 XX_PROCESS_FLAG, 148 XX_PERIOD_ID,NULL
FROM mtl_material_transactions mtl
,org_organization_definitions orgFrm1 -- From org
,org_organization_definitions orgTo -- To org
, cm_cmpt_dtl cst
WHERE mtl.source_code = 'RCV'
and mtl.TRANSACTION_DATE between (select trunc(start_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID-- v_period_id
)
and (select trunc(end_date) from GMF_PERIOD_STATUSES where period_id =148-- PERIODID
)
and mtl.transaction_action_id = 12
and mtl.TRANSFER_ORGANIZATION_ID=orgFrm1.ORGANIZATION_ID
and mtl.ORGANIZATION_ID=orgTo.ORGANIZATION_ID
and orgFrm1.OPERATING_UNIT<>orgTo.OPERATING_UNIT
and mtl.TRANSFER_ORGANIZATION_ID=cst.ORGANIZATION_ID(+) and mtl.INVENTORY_ITEM_ID=cst.INVENTORY_ITEM_ID(+) and cst.PERIOD_ID(+)=148
and( mtl.TRANSFER_PRICE<>cst.CMPNT_COST or cst.CMPNT_COST is null)


;
COMMIT;


--FOR i IN 1 .. 1 LOOP

declare
cursor c_mmt is

SELECT * FROM XX_UPDATE_TRANSFER_PRICE
WHERE TRANSACTION_ID = 148 AND XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING'
ORDER BY TRANSACTION_ID;

h_mmt c_mmt%ROWTYPE;
BEGIN
OPEN c_mmt;
LOOP
FETCH c_mmt INTO h_mmt;
EXIT WHEN c_mmt%NOTFOUND;

-------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE---------------------------

declare
cursor c_mmtN is

select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
order by TRANSACTION_ID;


h_mmtN c_mmtN%ROWTYPE;
BEGIN
OPEN c_mmtN;
LOOP
FETCH c_mmtN INTO h_mmtN;
EXIT WHEN c_mmtN%NOTFOUND;
update XX_UPDATE_TRANSFER_PRICE
set NEW_TRANSFER_PRICE =(

select acctg_cost
from gl_item_cst gic,mtl_material_transactions mmt
where mmt.transaction_id = h_mmtN.transaction_id
and mmt.inventory_item_id=gic.INVENTORY_ITEM_ID
and gic.period_ID = PERIODID --v_period_ID
and final_flag = 0
and mmt.TRANSFER_ORGANIZATION_ID = gic.ORGANIZATION_ID

)
where XX_UPDATE_TRANSFER_PRICE.transaction_id = h_mmtN.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtN;
END;





---------------------------------------------
/*
--update mtl_material_transactions
-- set TRANSFER_PRICE = h_mmt.NEW_TRANSFER_PRICE
-- where mtl_material_transactions.TRANSACTION_ID = h_mmt.transaction_id;

IF i=1 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;
ELSIF I=2 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=991, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

ELSIF I=3 THEN
update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=992, PROCESS_STATUS='COMPLETED' where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100;

END IF;







Insert into XX_UPDATE_TRANSFER_PRICE
(TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSFER_PRICE, NEW_TRANSFER_PRICE,
TRANSFER_ORGANIZATION_ID, XX_TRANSFER_PRICE, XX_PROCESS_FLAG, XX_PERIOD_ID,PROCESS_STATUS)
Values (148, 148, 148, 0, 0, 148, 0, 101, 148,'RUNNING'||i);
COMMIT;


*/


--- mmt transfer price update-----

declare
cursor c_mmtZ is
select * from XX_UPDATE_TRANSFER_PRICE WHERE PROCESS_STATUS IS NULL
--where TRANSACTION_ID = 1227428
order by TRANSACTION_ID;
h_mmtZ c_mmtZ%ROWTYPE;
BEGIN
OPEN c_mmtZ;
LOOP
FETCH c_mmtZ INTO h_mmtZ;
EXIT WHEN c_mmtZ%NOTFOUND;
update mtl_material_transactions
set TRANSFER_PRICE = h_mmtZ.NEW_TRANSFER_PRICE
where mtl_material_transactions.TRANSACTION_ID = h_mmtZ.transaction_id;
commit;
END LOOP;
exception
when others then
null;
CLOSE c_mmtZ;
END;
----------------------------------

update XX_UPDATE_TRANSFER_PRICE set XX_PROCESS_FLAG=990, PROCESS_STATUS='COMPLETED'; -- where transaction_id=148 and inventory_item_id=148 and xx_period_id=148 and ORGANIZATION_ID=148 and TRANSFER_ORGANIZATION_ID =148 and XX_PROCESS_FLAG=100 AND PROCESS_STATUS='RUNNING';
commit;
END LOOP;

exception
when others then
null;
CLOSE c_mmt;
END;



------------------------------------

--END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error - '||SQLERRM);
END;
/