Group Team
Whenever item is created, default Revision will be assigned to item.

We can also modify the item Revision of an item.

Base table for Item Revision is Mtl_Item_Revisions.

To Modify the item revision, we need to insert new record in Mtl_item_revisions_interface.

Following are the important columns in Mtl_Item_Revisions_Interface

a. Inventory_item_id
b. Organization_id
c. Revision
d. Process_Flag
e. set_process_id
f. Transaction_type

Below code helps you to unserstand how to create a Item Revision.

Code to Import Item Revision
-------------------------------------

DECLARE
l_error                              VARCHAR2(1000) :=NULL;
l_error_message               VARCHAR2(1000) :=NULL;
v_flag                               NUMBER :=0;
v_date                              DATE := SYSDATE;
v_user_id                         fnd_user.user_id%TYPE := FND_PROFILE.VALUE('USER_ID');
v_request_id                    NUMBER :=0;
v_rev_org_id                   NUMBER :=0;
v_rev_item_id                  NUMBER :=0;
l_request_id                     NUMBER :=0;
v_revision                        NUMBER :=0;
v_phase                          VARCHAR2(240);
v_status                          VARCHAR2(240);
v_request_phase             VARCHAR2(240);
v_request_status             VARCHAR2(240);
v_finished                       BOOLEAN;
v_message                      VARCHAR2(240);

BEGIN
      BEGIN
---------Getting Organization Id for Item Revision----------------
             BEGIN
                    SELECT organization_id
                    INTO v_rev_org_id
                    FROM mtl_parameters
                    WHERE organization_code = c_item_revision_rec.organization_code;
             EXCEPTION
                   WHEN OTHERS THEN
                          l_error := c_item_revision_rec.organization_code' does not exist for the item ''Item Name';
                          l_error_message := l_error_messagel_error ;
                         v_flag :=1;
                         dbms_output.put_line( l_error);
              END;
------Checking whether Item exists in Oracl 11i for Item revision ---------------
              BEGIN
                     SELECT inventory_item_id
                     INTO v_rev_item_id
                     FROM mtl_system_items_b msi,
                                 mtl_parameters mp
                     WHERE 1 = 1
                     AND msi.organization_id = mp.organization_id
                     AND msi.segment1 = 'Item_Name'
                     AND mp.organization_code = 'Org_Code';
               EXCEPTION
                     WHEN OTHERS THEN
                            l_error := 'Item Name''--''Item Does not exist in Oracle 11i';
                            l_error_message := l_error_messagel_error ;
                           v_flag :=1;
                          dbms_output.put_line( l_error);
               END;
------Getting Item count inserted in the interface table mainly used for Synchronizng the Effectivity date----
              BEGIN
                      SELECT count(1)
                      INTO v_count
                      FROM MTL_ITEM_REVISIONS
                      WHERE inventory_item_id = v_rev_item_id;
              EXCEPTION
                    WHEN OTHERS THEN
                              dbms_output.put_line( 'Error in counting the Item revision available in Interface');
                              v_flag :=1;
              END;

             IF v_flag = 0 THEN
                    BEGIN
---------Inserting into Revision Table---------
                          v_revision := (0.003472 * ( 1 + v_count ));

                         INSERT INTO MTL_ITEM_REVISIONS_INTERFACE
                                                 ( INVENTORY_ITEM_ID
                                                  ,ORGANIZATION_ID
                                                  ,REVISION
                                                  ,LAST_UPDATE_DATE
                                                  ,LAST_UPDATED_BY
                                                  ,CREATION_DATE
                                                  ,CREATED_BY
                                                  ,IMPLEMENTATION_DATE
                                                  ,EFFECTIVITY_DATE
                                                  ,PROCESS_FLAG
                                                  ,TRANSACTION_TYPE
                                                  ,SET_PROCESS_ID
                                                 )
                                     VALUES
                                                 ( v_rev_item_id
                                                  ,v_rev_org_id
                                                  ,'Revision Number'
                                                  ,v_date
                                                  ,v_user_id
                                                  ,v_date
                                                  ,v_user_id
                                                  ,(SYSDATE+v_revision)
                                                  ,(SYSDATE+v_revision)
                                                  ,1 --PROCESS_FLAG(1 for pending)
                                                  ,'CREATE'
                                                  ,1 --SET_PROCESS_ID
                                                  );
                                           COMMIT;

                    EXCEPTION
                          WHEN OTHERS THEN
                                   l_error := 'Error in inserting Item Revision to Interface table ''and error is  'SQLERRM;
                                  l_error_message := l_error_messagel_error;
                                 dbms_output.put_line( l_error);
                    END;
             ELSE
                       dbms_output.put_line( 'Program failed in Initial validation ');
             END IF; --End of If l_flag=0

      EXCEPTION
           WHEN OTHERS THEN
                dbms_output.put_line( 'No Revision Exists for any Item');
       END;

-- Calling Item Import Program for Revision----
      BEGIN
             dbms_output.put_line('--Submitting Item Import Program for assigning revision to the Item--');
              l_request_id := apps.Fnd_Request.submit_request ( 'INV',
                                                                                             'INCOIN',
                                                                                              NULL, -- Description
                                                                                              NULL, -- Start Time
                                                                                              FALSE, -- Sub Request
                                                                                              1, -- All Organizations
                                                                                              1, -- Validate Items,
                                                                                              1, -- Process Items
                                                                                              1, -- Delete Process Rows
                                                                                              1, -- Process Set
                                                                                              1 ); -- CREATE

               COMMIT;
               DBMS_output.Put_line('Item Import Program submitted');

              IF ( l_request_id = 0 ) THEN
                      dbms_output.put_line( 'Submission of Import failed');
             END IF;

-- Wait for request to run the import Program to Finish
             v_finished := fnd_concurrent.wait_for_request (request_id => l_request_id,
                                                                                     interval => 0,
                                                                                     max_wait => 0,
                                                                                     phase => v_phase,
                                                                                     status => v_status,
                                                                                    dev_phase => v_request_phase,
                                                                                    dev_status => v_request_status,
                                                                                    message => v_message);

          DBMS_output.Put_line('Item Import Program for Revision is Waiting');
          DBMS_output.Put_line('Request Phase : ' v_request_phase);
          DBMS_output.Put_line('Request Status : ' v_request_status );
          DBMS_output.Put_line('Request ID : ' l_request_id );


         IF ( UPPER(v_request_status) = 'NORMAL') THEN
               DBMS_output.Put_line('Item Import Program completed succesfully');
         ELSE
                 DBMS_output.Put_line('Item Import Program completed with error. Check Mtl_interface_errors table for Knowing about the error');
         END IF;

      EXCEPTION
            WHEN OTHERS THEN
                   DBMS_output.Put_line('Error Occured during calling Item Import Program for Revision. 'SQLERRM);
      END;

commit;
dbms_output.put_line('End of Revision');

EXCEPTION
      WHEN OTHERS THEN
                dbms_output.put_line( 'Error in Item Revision Program and error is :'SQLERRM);
END;
1 Response
  1. Sridevi K Says:

    Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment