WIP Negative Issue API

DECLARE

CURSOR cur_com
IS
select * from BOM_INVENTORY_COMPONENTS_V bic,bom_bill_of_materials bom
where bom.assembly_item_id = 33003
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.operation_seq_num = 10
and bom.organization_id = 81;

CURSOR cur_lot_num(p_item_id NUMBER,p_org_id NUMBER,p_subinv_code VARCHAR2)
IS
select lot_number,primary_transaction_quantity quantity from mtl_onhand_quantities_detail
where inventory_item_id = p_item_id
and organization_id = p_org_id
and subinventory_code = p_subinv_code
order by orig_date_received asc;

retval number;
ln_interface_id number;
l_return_status VARCHAR2(10);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2(500);
l_trans_count VARCHAR2(100);
v_header_id number;
ln_out NUMBER;
ln_quantity NUMBER;
ln_trn_type_id NUMBER;
ln_item_id NUMBER;
lv_uom_code VARCHAR2(10);
lv_lot_number VARCHAR2(25);

BEGIN

       BEGIN

         SELECT mtl_material_transactions_s.NEXTVAL
           INTO ln_interface_id
           FROM DUAL;
          
       EXCEPTION
       WHEN OTHERS THEN
      
        ln_interface_id := 0;   
          
       END;
      
       BEGIN
      
        SELECT transaction_type_id
        INTO ln_trn_type_id
        FROM mtl_transaction_types
        WHERE transaction_type_name = 'WIP Negative Issue';
       
       EXCEPTION
       WHEN OTHERS THEN
      
        ln_trn_type_id := 0;
       
       END;
      
         BEGIN
                        SELECT msi.inventory_item_id,msi.primary_uom_code,(msi.inventory_item_id||msi.start_auto_lot_number) lot_number
                        INTO ln_item_id, lv_uom_code,lv_lot_number
                        FROM bom_operational_routings_v bor,wip_discrete_jobs wdj,mtl_system_items_b msi
                        WHERE bor.routing_sequence_id = wdj.common_routing_sequence_id
                        AND bor.attribute5 = msi.segment1
                        AND bor.organization_id = msi.organization_id
                        AND wdj.wip_entity_id = 132107;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                DBMS_OUTPUT.PUT_LINE('Exception in lot number');
         -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'FORM-09 UNDER RULE 107 OF FACTORIES RULES 1969.,,,,,,,,,,,,,,,,,');
         END;      
      
       ln_quantity := 3;
      
    BEGIN       

    INSERT INTO mtl_transactions_interface
            (source_code,
            source_line_id,
            source_header_id,
            process_flag,
            transaction_mode,
            inventory_item_id,
            --revision,
            organization_id,
            subinventory_code,
            -- locator_id,
            transaction_quantity,
            transaction_uom,
            transaction_date,
            transaction_type_id,
--            transfer_subinventory,
            lock_flag,
            last_update_date,
            last_updated_by,
            created_by,
            creation_date,
             --attribute1, transaction_source_id, attribute2,
             --attribute3, attribute4,
             transaction_source_id,
--             attribute9,
            transaction_interface_id,
            transfer_locator,
            wip_entity_type,
            operation_seq_num
            )
     VALUES ('1',
             99,
             99,
             1,
             3,
             1001,--rec_sl.inventory_item_id,
             --rec_sl.revision,
             81,--rec_sl.organization_id,
             'ASSY_IN',--From Subinventory
--             ln_to_loc_id,
             ln_quantity,--transaction_quantity
             'Kg',
             SYSDATE,
             ln_trn_type_id,--Transaction Type Id
--             'MRB',--To subinventory
             2,
             SYSDATE,
             0,
             0,
             SYSDATE,
             --rec_sl.attribute1, ln_wip_id, rec_sl.header_id,
             --rec_sl.attribute2, rec_sl.attribute4,
             132107,--transaction_source_id
             ln_interface_id,--transaction_interface_id
             NULL,--transfer_locator
             2,--wip_entity_type
             10--operation_seq_num
            );
                
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE');
       
    END;
   
  
INSERT INTO mtl_transaction_lots_interface
                           (transaction_interface_id,
                            lot_number,
                            lot_expiration_date,
                            transaction_quantity,
                            last_update_date,
                            last_updated_by,
                            creation_date,
                            created_by
                            --, source_code,
                            --source_line_id, process_flag, attribute9
                           )
                    VALUES (ln_interface_id,        --transaction interface id
                            lv_lot_number,--                rec_mtl_lot.lot_number,
                            --Lot number
                            SYSDATE + 100,               --Lot expiration date
                            ln_quantity,--              ln_qty,       --transaction quantity
                            SYSDATE,                        --last update date
                            0,       --last updated by
                            SYSDATE,                           --creation date
                            0
                            --,'Subinventory Transfer',
                            --rec_sl.line_id, 1, ln_dcs_no
                           );
     
      BEGIN     
                          
                          
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO v_header_id
           FROM DUAL;
          
      EXCEPTION
      WHEN OTHERS THEN
     
        v_header_id := 0;    
          
      END;              
     
      BEGIN 
          
            UPDATE mtl_transactions_interface
               SET transaction_header_id = v_header_id,
                   --TRANSACTION_INTERFACE_ID =v_header_id,
                   transaction_date = SYSDATE
             WHERE source_code = '1' AND transaction_header_id IS NULL;
            
      EXCEPTION
      WHEN OTHERS THEN
     
        dbms_output.put_line('Error while updating MTL_TRANSACTIONS_INTERFACE');
       
      END;        
                       
--      COMMIT;        
                        
         retval :=
            apps.inv_txn_manager_pub.process_transactions
                                          (p_api_version           => 1.0,
                                           p_init_msg_list         => 'T',
                                           p_commit                => 'T',
                                           p_validation_level      => 100,
                                           x_return_status         => l_return_status,
                                           x_msg_count             => l_msg_cnt,
                                           x_msg_data              => l_msg_data,
                                           x_trans_count           => l_trans_count,
                                           p_table                 => 1,
                                           p_header_id             => v_header_id
                                          );
   
dbms_output.put_line('l_msg_data:'||l_msg_data);
dbms_output.put_line('l_return_status:'||l_return_status);
dbms_output.put_line('v_header_id:'||v_header_id);
dbms_output.put_line('retval:'||v_header_id);
                                     
END;