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;

Amount in words

create or replace function amtinwords (numinput number) return char is

    LvInput     Number(16,2);

    Output         Varchar2(600);

    LvOutput     Varchar2(600);

    Paise         Number(2);

    Ps         Varchar2(30);

    Thous         Number(6);

    Ths         VarChar2(200);

    Lakhs         Number(2);

    Ls         VarChar2(30);

    Crores         Number(6);

    Cs         VarChar2(200);

  --If Amount is Negative set the flag to 'T'

  NegAmt varchar2(1):='F';

BEGIN

    Output  := ' Only';

         --LvInput := :CF_GRAND_TOTAL;

        LvInput := NUMINPUT;

         IF     LvInput < 0 then

              LvInput:=ABS(LvInput);

              NegAmt:='T';

         ELSE

                 NegAmt:='F';

      END IF;

         LvInput := LvInput*100;

         Paise   := MOD(LvInput,100);

         dbms_output.put_line('Paise:'||paise);

         IF Paise != 0 THEN

          SELECT TO_CHAR(TO_DATE(Paise,'J'),'Jsp') INTO Ps FROM Dual;

          Output := 'Rupee '||LTRIM(RTRIM(Ps))||' Paise '||OutPut;

        END IF;

         LvInput := TRUNC(LvInput/100);

         Thous   := MOD(LvInput,100000);

         IF Thous != 0 THEN

        SELECT TO_CHAR(TO_DATE(Thous,'J'),'Jsp')INTO Ths FROM Dual;

        Output := LTRIM(RTRIM(Ths))||' '||OutPut;

         END IF;

         LvInput := TRUNC(LvInput/100000);

         Lakhs   := MOD(LvInput,100);

         IF Lakhs != 0 THEN

        SELECT TO_CHAR(TO_DATE(Lakhs,'J'),'Jsp')INTO Ls FROM Dual;

          Output := LTRIM(RTRIM(Ls))||' Lakh '||OutPut;

         END IF;

         LvInput := TRUNC(LvInput/100);

         Crores  := MOD(LvInput,100000);

         IF Crores !=0 THEN

             SELECT TO_CHAR(TO_DATE(Crores,'J'),'Jsp')INTO Cs FROM Dual;

          Output := LTRIM(RTRIM(Cs))||' Crore '||OutPut;

         END IF;

            IF NegAmt='T' THEN

                    -- <> Indicates Negative Amount

                    RETURN(REPLACE(Output,'-',' ')||'>');

            ELSE

                    RETURN(REPLACE(Output,'-',' '));

            END IF;

end;

Subinventory Transfer API

--The following code performs subinventory transfer  of an item from one subinventory to another subinventory.The lot number is chosen based on FIFO(First In First Out) basis.


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(100);
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;


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 = 'Subinventory Transfer';
       
       EXCEPTION
       WHEN OTHERS THEN
      
        ln_trn_type_id := 0;
       
       END;
      
    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_name,
             --attribute9,
            transaction_interface_id,
            transfer_locator
            )
     VALUES ('Subinventory Transfer',
             99,
             99,
             1,
             3,
             33008,--rec_sl.inventory_item_id,
             --rec_sl.revision,
             81,--rec_sl.organization_id,
             'ASSY_IN',--From Subinventory
--             ln_to_loc_id,
             1,--transaction_quantity
             'Nos',
             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, lc_job_no,
             --ln_dcs_no,
             ln_interface_id,
             NULL
            );
                
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE');
       
    END;
   
    ln_quantity := 1;

 FOR rec_lot_num IN cur_lot_num(33008,81,'ASSY_IN')
 LOOP                    
   
   IF rec_lot_num.quantity <= ln_quantity AND ln_quantity <> 0 THEN
       
    ln_out := rec_lot_num.quantity;
       
    BEGIN

               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
                            rec_lot_num.lot_number,--                rec_mtl_lot.lot_number,
                            --Lot number
                            SYSDATE + 100,               --Lot expiration date
                            rec_lot_num.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
                           );                      
                          
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE');
       
    END;
   
   ELSE
  
    BEGIN

               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
                            rec_lot_num.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
                           );                      
                          
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE');
       
    END;
   
    EXIT;    
   
   END IF;
  
   ln_quantity := ln_quantity - ln_out;
  
  
 END LOOP; 
     
      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 = 'Subinventory Transfer' 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;