create or replace procedure xx_lot_weight_details_proc(p_lot_number varchar2) IS cursor c1 is select mln.attribute15 as mln_order_number ,mtln.ORGANIZATION_ID as mtln_organization_id ,mtln.lot_number as mtln_lot_number ,sum(mtln.TRANSACTION_QUANTITY) as mtln_transaction_quantity ,sum(mtln.PRIMARY_QUANTITY) as mtln_primary_quantity ,mln.lot_number as mln_lot_number ,mln.organization_id as mln_organization_id ,moq.lot_number as moq_lot_number ,moq.organization_id as moq_organization_id ,sum(moq.TRANSACTION_QUANTITY) as moq_transaction_quantity ,mln.inventory_item_id ,msib.segment1 as item_number ,msib.PRIMARY_UOM_CODE as uom ,moq.SUBINVENTORY_CODE --,mmt.DISTRIBUTION_ACCOUNT_ID from mtl_transaction_lot_numbers mtln , mtl_lot_numbers mln ,mtl_onhand_quantities moq ,mtl_system_items_b msib --,mtl_material_transactions mmt where mtln.lot_number =mln.lot_number and mtln.lot_number =moq.lot_number and mtln.organization_id = moq.organization_id and mtln.TRANSACTION_ID = moq.CREATE_TRANSACTION_ID and moq.inventory_item_id=msib.inventory_item_id and moq.organization_id=msib.organization_id --and mtln.transaction_id=mmt.transaction_id and mln.organization_id=moq.organization_id and mln.attribute15 is not null and mln.lot_number=p_lot_number and not exists(select 'x' from wsh_delivery_details wdd where mln.lot_number = wdd.lot_number) and mln.attribute_category IN('NSAIL Lot Information CCL','NSAIL Lot Information') group by mln.attribute15 ,mtln.lot_number ,mtln.organization_id ,mln.lot_number ,mln.organization_id ,moq.lot_number ,moq.organization_id ,mln.inventory_item_id ,msib.segment1 ,msib.PRIMARY_UOM_CODE ,moq.SUBINVENTORY_CODE; --,mmt.DISTRIBUTION_ACCOUNT_ID; v_lot_number varchar2(80); v_lot_weight number; begin for r1 in c1 loop begin select lot_number,weight into v_lot_number,v_lot_weight from xx_lot_details where lot_number=r1.moq_lot_number; if r1.moq_transaction_quantity=v_lot_weight then null; else update xx_lot_details set weight=r1.moq_transaction_quantity where lot_number=r1.moq_lot_number; end if; exception when no_data_found then insert into xx_lot_details(lot_number ,inventory_item_id ,item_number ,uom ,weight ,weighing_bridge_weight ,initial_lot_weight ,update_lot_weight_flag ,organization_id ,sub_inventory_code ,distribution_account_id ,created_by ,creation_date ,last_updated_by ,last_update_date ,last_update_login ) values(r1.moq_lot_number ,r1.inventory_item_id ,r1.item_number ,r1.uom ,r1.moq_transaction_quantity ,null --weighing_bridge_weight ,r1.moq_transaction_quantity --initial_lot_weight ,null --update_lot_weight_flag ,r1.mln_organization_id ,r1.SUBINVENTORY_CODE ,14038 --this is for test instance .for production instance id is 13547-8020 ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ); when others then --dbms_output.put_line(sqlerrm); null; end; end loop; commit; end; CREATE OR REPLACE PROCEDURE XX_UPDATE_LOT_WEIGHT(p_lot_number varchar2) is cursor c is select * from xx_lot_details where lot_number=p_lot_number; --and update_lot_weight='Y'; v_transaction_quantity number; v_transaction_interface_id number; begin for r in c loop v_transaction_quantity:=null; v_transaction_interface_id:=null; begin select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into v_transaction_interface_id from dual; end; v_transaction_quantity:=(r.WEIGHING_BRIDGE_WEIGHT)-(r.weight); if v_transaction_quantity>0 then INSERT INTO mtl_transactions_interface (source_code ,source_line_id ,source_header_id ,process_flag ,transaction_mode ,last_update_date ,last_updated_by ,creation_date ,created_by ,organization_id ,transaction_quantity -- ,SECONDARY_TRANSACTION_QUANTITY ,transaction_uom -- ,SECONDARY_UOM_CODE ,transaction_date ,TRANSACTION_TYPE_id ,inventory_item_id ,subinventory_code ,distribution_account_id ,transaction_interface_id --,TRANSACTION_COST --,LOCATOR_ID ) VALUES('CONVERSION' ,0 ,0 ,1 ,3 ,SYSDATE ,FND_GLOBAL.USER_ID ,SYSDATE ,FND_GLOBAL.USER_ID ,r.organization_id --V_ORGANIZATION_ID, ,v_transaction_quantity --transaction_quantity -- R1.SECONDARY_QTY, ,r.uom --V_UOM_CODE1, -- V_UOM_CODE2, ,sysdate --transaction_date ,100002 --TRANSACTION_TYPE_id hard coded for +ve quantity ,r.inventory_item_id --V_INVENTORY_ITEM_ID, ,r.SUB_INVENTORY_CODE --V_SECONDARY_INVENTORY_NAME, ,r.distribution_account_id ,v_transaction_interface_id --,R1.TRANSACTION_COST --,V_LOCATOR_ID ); Insert into mtl_transaction_lots_interface (transaction_interface_id ,lot_number ,transaction_quantity --,SECONDARY_TRANSACTION_QUANTITY ,last_update_date ,last_updated_by ,creation_date ,created_by ) VALUES (v_transaction_interface_id --MTL_MATERIAL_TRANSACTIONS_S.CURRVAL ,r.lot_number --R1.new_LOT_No ,v_transaction_quantity --R1.PRIMARY_QTY, --,R1.SECONDARY_QTY ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID ); /* elsif v_transaction_quantity<0 then INSERT INTO mtl_transactions_interface (source_code ,source_line_id ,source_header_id ,process_flag ,transaction_mode ,last_update_date ,last_updated_by ,creation_date ,created_by ,organization_id ,transaction_quantity -- ,SECONDARY_TRANSACTION_QUANTITY ,transaction_uom -- ,SECONDARY_UOM_CODE ,transaction_date ,TRANSACTION_TYPE_id ,inventory_item_id ,subinventory_code ,distribution_account_id ,transaction_interface_id --,TRANSACTION_COST --,LOCATOR_ID ) VALUES('CONVERSION' ,0 ,0 ,1 ,3 ,SYSDATE ,FND_GLOBAL.USER_ID ,SYSDATE ,FND_GLOBAL.USER_ID ,r.organization_id --V_ORGANIZATION_ID, ,v_transaction_quantity --transaction_quantity -- R1.SECONDARY_QTY, ,r.uom --V_UOM_CODE1, -- V_UOM_CODE2, ,sysdate --transaction_date ,97 --TRANSACTION_TYPE_id hard coded for -ve quantity ,r.inventory_item_id --V_INVENTORY_ITEM_ID, ,r.SUB_INVENTORY_CODE --V_SECONDARY_INVENTORY_NAME, ,r.distribution_account_id ,v_transaction_interface_id --,R1.TRANSACTION_COST --,V_LOCATOR_ID ); Insert into mtl_transaction_lots_interface (transaction_interface_id ,lot_number ,transaction_quantity --,SECONDARY_TRANSACTION_QUANTITY ,last_update_date ,last_updated_by ,creation_date ,created_by ) VALUES (v_transaction_interface_id --MTL_MATERIAL_TRANSACTIONS_S.CURRVAL ,r.lot_number --R1.new_LOT_No ,v_transaction_quantity --R1.PRIMARY_QTY, --,R1.SECONDARY_QTY ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID ); */ end if; end loop; commit; end;