Assign Item to an Organization in Oracle APPS Inventory by using API

EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.

The procedure definition is:

PROCEDURE Assign_Item_To_Org(
    p_api_version             IN      NUMBER
   ,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE
   ,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE
   ,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM
   ,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM
   ,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,x_return_status           OUT NOCOPY  VARCHAR2
   ,x_msg_count               OUT NOCOPY  NUMBER);

The parameters are:

• P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
• P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
• P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
• P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
• P_ITEM_NUMBER – Segment1 of the Item
• P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
• P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
• P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
• X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
• X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.

Sample Code: (Tested in R12.1.3)

DECLARE
        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version    NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit        VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status    VARCHAR2(2);
        x_msg_count        NUMBER := 0;
BEGIN
        SELECT fa.application_id
          INTO l_appl_id
          FROM fnd_application fa
         WHERE fa.application_short_name = 'INV';

        SELECT fr.responsibility_id
          INTO l_resp_id
          FROM fnd_application fa, fnd_responsibility_tl fr
         WHERE fa.application_short_name = 'INV'
           AND fa.application_id = fr.application_id
           AND UPPER (fr.responsibility_name) = 'INVENTORY';

        fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => 1003
                ,  p_item_number          => 000000000001035
                ,  p_organization_id      => 11047
                ,  P_ORGANIZATION_CODE    => 'DXN'
                ,  P_PRIMARY_UOM_CODE     => 'EA'
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;

Item Category Creation APIs

There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.

INV_ITEM_CATEGORY_PUB.Create_Category:

DECLARE
l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status   VARCHAR2(80);
l_error_code      NUMBER;
l_msg_count       NUMBER;
l_msg_data        VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
  l_category_rec.segment1 := 'RED';

  SELECT f.ID_FLEX_NUM
    INTO l_category_rec.structure_id
    FROM FND_ID_FLEX_STRUCTURES f
   WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

  l_category_rec.description := 'Red';

  INV_ITEM_CATEGORY_PUB.Create_Category
          (
          p_api_version   => 1.0,
          p_init_msg_list => FND_API.G_FALSE,
          p_commit        => FND_API.G_TRUE,
          x_return_status => l_return_status,
          x_errorcode     => l_error_code,
          x_msg_count     => l_msg_count,
          x_msg_data      => l_msg_data,
          p_category_rec  => l_category_rec,
          x_category_id   => l_out_category_id
          );
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB. Delete_Category:

DECLARE
l_return_status VARCHAR2(80);
l_error_code    NUMBER;
l_msg_count     NUMBER;
l_msg_data      VARCHAR2(80);
l_category_id   NUMBER;
BEGIN
  SELECT mcb.CATEGORY_ID
    INTO l_category_id
    FROM mtl_categories_b mcb
   WHERE mcb.SEGMENT1='RED'
     AND mcb.STRUCTURE_ID =
        (SELECT mcs_b.STRUCTURE_ID
           FROM mtl_category_sets_b mcs_b
          WHERE mcs_b.CATEGORY_SET_ID =
               (SELECT mcs_tl.CATEGORY_SET_ID
                  FROM mtl_category_sets_tl mcs_tl
                 WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
                 )
        );

    INV_ITEM_CATEGORY_PUB.Delete_Category
          (
          p_api_version     => 1.0,
          p_init_msg_list   => FND_API.G_FALSE,
          p_commit          => FND_API.G_TRUE,
          x_return_status   => l_return_status,
          x_errorcode       => l_error_code,
          x_msg_count       => l_msg_count,
          x_msg_data        => l_msg_data,
          p_category_id     => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;
 
INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.
DECLARE
         l_return_status VARCHAR2(80);
         l_error_code    NUMBER;
         l_msg_count     NUMBER;
         l_msg_data      VARCHAR2(80);
         l_category_id   NUMBER;
         l_description   VARCHAR2(80);
BEGIN
      select mcb.CATEGORY_ID into l_category_id
        from mtl_categories_b mcb
       where mcb.SEGMENT1='BLACK'
         and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
             from mtl_category_sets_b mcs_b
             where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                 from mtl_category_sets_tl mcs_tl
                 where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      l_description := 'new black color';

     INV_ITEM_CATEGORY_PUB.Update_Category_Description (
       p_api_version     => 1.0,
       p_init_msg_list   => FND_API.G_FALSE,
       p_commit          => FND_API.G_TRUE,
       x_return_status   => l_return_status,
       x_errorcode       => l_error_code,
       x_msg_count       => l_msg_count,
       x_msg_data        => l_msg_data,
       p_category_id     => l_category_id,
       p_description     => l_description);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;
 

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.Create_Valid_Category

Create a record in mtl_category_set_valid_cats.
DECLARE
        l_return_status   VARCHAR2(80);
        l_error_code      NUMBER;
        l_msg_count       NUMBER;
        l_msg_data        VARCHAR2(80);
        l_category_set_id NUMBER;
        l_category_id     NUMBER;
BEGIN
       select mcs_tl.CATEGORY_SET_ID into l_category_set_id
         from mtl_category_sets_tl mcs_tl
        where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

       select mcb.CATEGORY_ID into l_category_id
         from mtl_categories_b mcb
        where mcb.SEGMENT1='RED'
          and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
              from mtl_category_sets_b mcs_b
              where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                    from mtl_category_sets_tl mcs_tl
                    where CATEGORY_SET_NAME ='INV_COLORS_SET'));

       INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
             p_api_version        => 1.0,
             p_init_msg_list      => FND_API.G_FALSE,
             p_commit             => FND_API.G_TRUE,
             x_return_status      => l_return_status,
             x_errorcode          => l_error_code,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data,
             p_category_set_id    => l_category_set_id,
             p_category_id        => l_category_id,
             p_parent_category_id => NULL );

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;
 

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category

Delete the record from mtl_category_set_valid_cats.
DECLARE
           l_return_status    VARCHAR2(80);
           l_error_code       NUMBER;
           l_msg_count        NUMBER;
           l_msg_data         VARCHAR2(80);
           l_category_set_id  NUMBER;
           l_category_id      NUMBER;
BEGIN
         select mcs_tl.CATEGORY_SET_ID into l_category_set_id
           from mtl_category_sets_tl mcs_tl
          where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

         select mcb.CATEGORY_ID into l_category_id
           from mtl_categories_b mcb
          where mcb.SEGMENT1='RED'
            and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
                from mtl_category_sets_b mcs_b
                where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                  from mtl_category_sets_tl mcs_tl
                  where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
            p_api_version      => 1.0,
            p_init_msg_list    => FND_API.G_FALSE,
            p_commit           => FND_API.G_TRUE,
            x_return_status    => l_return_status,
            x_errorcode        => l_error_code,
            x_msg_count        => l_msg_count,
            x_msg_data         => l_msg_data,
            p_category_set_id  => l_category_set_id,
            p_category_id      => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

------------------------------------------------------------------------------------
Following APIs can be used to Create/Update/Delete Category Combinations

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE, X_CATEGORY_ID OUT NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER)

Following API is used for assigning a Category to a Category Set. A Category will be available in the list of valid categories for a category set only if it is assigned to the category set. This is a required step for categories if enforce list is checked on
INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_CATEGORY_SET_ID IN NUMBER,
P_CATEGORY_ID IN NUMBER,
P_PARENT_CATEGORY_ID IN NUMBER,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2)

Following APIs can be used to Create/Update/Delete Item Category Assignments

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_DESCRIPTION IN VARCHAR2)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)
 
 

How to Create Category and Category Set in Oracle Inventory

Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.

Suppose we need a category called ‘INV_COLORS’. We can define multiple colors in this category and then assign this category to an item.

Example:

1. Item1 —- Black
2. Item2 —- Red
3. Item3 —- Green
4. Item4 —- Orange

1] First we need to create a value set to hold these colors.

Navigation > Setup: Flexfields: Validation: Sets

Validation type Select: Independent

2] Next we need to enter our values in the INVENTORY_COLOR valueset

RED, GREEN, BLUE, BLACK, and ORANGE

Navigation -> Setup: Flexfields: Validation: Values

Save and close the Screen.

3] Now we need to create a KFF Structure

Navigation Setup: Flexfields: Key: Segments

Create the structure name: In the “Code” field enter INV_COLORS

4] Click on the “Segments” button.

• Enter the “Number” field: 10
• Enter the Name field: Color
• Enter the “Window Prompt”: Color (This value will appear on the screen)
• Enter the “Column” field: Segment1 (you can choose any column)

Save and exit the form.

5] Check the Freeze flex field Definition, the following warning will appear.

Click OK.

6] The “Compile” button is now available to be selected. Click on the compile button.

Click Ok

Close the form.

7] Go to View -> Request and Verify that the new Category flexfield compiled successfully.

8] The new structure is ready for use. Now let’s create a category.

Navigation : Setup: Items: Categories: Category Codes

• Enter the structure name: INV_COLORES
• Enter the category: BLACK
• (Note the form does not provide an LOV for the categories. You will need to use edit symbol at the top of the page or “ e “ to bring up the lov)
• Enter the description.

9] Next we create our category set.

Navigation Setup: Items: Categories: Category Sets

• Fill in the category set Name: INV_COLORS_SET
• The description: Inventory color set
• The Flex Structure: INV_COLORS
• The Controlled: Org Level
• Default Category: BLACK

10] After creating the category set, we can assign it to any items.

There are few category APIs that will be discussed in upcoming posts. Till then GOOD BYE………!! Have a nice day

Julayi

Inter Org Transfer and Subinventory transfer through API

PACKAGE MMD_INTERORG_SUBINVENTORY_TRANS IS
  PROCEDURE TRANSACT_ITEMS;
END;
PACKAGE BODY MMD_INTERORG_SUBINVENTORY_TRANS IS
   PROCEDURE TRANSACT_ITEMS IS
     
      CURSOR C_ITEMS_TRAN IS
      SELECT m.mis_number,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,m.from_whse,m.to_whse,m.reason_code,l.*,
             lt.lot_id,lt.lot_number,lt.sublot,lt.lot_grade,lt.lot_status,
             lt.from_location,lt.to_location,lt.issue_qty,lt.received_qty
      FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
      where m.mis_trn_id = l.mis_trn_id
      AND l.mis_line_id = lt.mis_line_id
      AND m.from_whse = :sddl_mis_mst.from_whse
      AND m.mis_number = :sddl_mis_mst.mis_number
      AND M.ORG_TXFR = 'N'
      AND nvl(lt.received_qty,0) > 0
      ORDER BY l.mis_line_id,lt.mis_lot_id;
 -- Mahammad added cursor for inter_org transfer on 16.SEP.12    
     
      CURSOR C_ITEMS_TRAN_INTORG IS
      SELECT M.MIS_NUMBER,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,M.FROM_WHSE,M.TO_WHSE,M.REASON_CODE,L.*,
            LT.LOT_ID,LT.LOT_NUMBER,LT.SUBLOT,LT.LOT_GRADE,LT.LOT_STATUS,
            LT.FROM_LOCATION,LT.TO_LOCATION,LT.ISSUE_QTY,LT.RECEIVED_QTY
      FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
      WHERE M.MIS_TRN_ID = L.MIS_TRN_ID
      AND L.MIS_LINE_ID = LT.MIS_LINE_ID
     AND M.TO_ORG=:SDDL_MIS_MST.TO_ORG
     AND m.from_whse = :sddl_mis_mst.from_whse
      AND M.MIS_NUMBER = :SDDL_MIS_MST.MIS_NUMBER
     AND M.ORG_TXFR = 'Y'
      AND nvl(lt.received_qty,0) > 0
      ORDER BY l.mis_line_id,lt.mis_lot_id;
--
      l_TRANSACTION_INTERFACE_ID NUMBER;
      l_transaction_header_id NUMBER;
      l_primary_UOM VARCHAR2(10);
      l_sec_UOM VARCHAR2(10);
      l_org_id number;
      l_return_status VARCHAR2(100);
      L_TRANS_COUNT NUMBER;
      L_MSG_DATA VARCHAR2(200);
      L_MSG_CNT NUMBER;
      l_init_msg_list VARCHAr2(10):= 'T';
      l_commit VARCHAr2(10):= 'T';
      l_validation_level NUMBER:= 1;
      V_RET_VAL NUMBER;
      l_from_loc_id NUMBER;
      l_to_loc_id NUMBER;
      V_GROUP_ID NUMBER;
      v_header_id number;
      l_TRANSACT_INTERFACE_ID number;
      l_from_orgid number;
      l_to_orgid number;
   BEGIN
  
   ---- Mahammad added for inter_org transfer on 16.SEP.12
begin 
  if :SDDL_MIS_MST.ORG_TXFR='Y' then
 
   FOR R_ITEMS_TRAN_INTORG IN C_ITEMS_TRAN_INTORG  
    LOOP
     select organization_id
   into l_from_orgid
   from org_organization_definitions
   where organization_code=R_ITEMS_TRAN_INTORG.FROM_ORG;
   
   select organization_id
   into l_to_orgid
   from org_organization_definitions
   where organization_code=R_ITEMS_TRAN_INTORG.TO_ORG;

 
       IF R_ITEMS_TRAN_INTORG.from_location IS NOT NULL THEN
      select inventory_location_id
       INTO l_from_loc_id
       from MTL_ITEM_LOCATIONS_KFV MIL
      where mil.Concatenated_segments=R_ITEMS_TRAN_INTORG.from_location
        and  Organization_id=l_from_orgid;
       END IF;       
  
     select inventory_location_id
       INTO l_to_loc_id
       from MTL_ITEM_LOCATIONS_KFV MIL
      where mil.inventory_item_id=R_ITEMS_TRAN_INTORG.item_id
        and  Organization_id=l_to_orgid;      
       select  primary_uom_code,secondary_uom_code
         INTO  l_primary_UOM,l_sec_UOM
         FROM  MTL_SYSTEM_ITEMS
        WHERE  INVENTORY_ITEM_ID=R_ITEMS_TRAN_INTORG.item_id
         and  Organization_id=:GLOBAL.ORG_ID;
       
     select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
        into l_transaction_INTERFACE_ID
      from DUAL;
  
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_TRANSACT_INTERFACE_ID
       FROM DUAL;
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_transaction_header_id
       FROM DUAL;
      
     select RCV_INTERFACE_GROUPS_S.NEXTVAL
    into V_GROUP_ID
   from dual;

      select rcv_headers_interface_s.nextval
    into v_header_id
   from dual;
        
     INSERT INTO MTL_TRANSACTIONS_INTERFACE
       ( CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       last_update_login ,
       SOURCE_CODE,
       SOURCE_LINE_ID,
       SOURCE_HEADER_ID,
       PROCESS_FLAG,
       INVENTORY_ITEM_ID,
       ORGANIZATION_ID,
       SUBINVENTORY_CODE,
       TRANSACTION_TYPE_ID,
       TRANSACTION_QUANTITY,
       TRANSACTION_UOM,
       TRANSACTION_DATE,
       TRANSFER_ORGANIZATION,
       TRANSFER_SUBINVENTORY,
       TRANSACTION_MODE,
       REVISION,
       SHIPMENT_NUMBER,
       TRANSACTION_HEADER_ID ,
       TRANSACTION_INTERFACE_ID,
       TRANSFER_LOCATOR ,
       locator_id
       )
        VALUES
        (sysdate,
         1234,
         sysdate,
         1234,
         2213,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
         R_ITEMS_TRAN_INTORG.MIS_TRN_ID,
         1,--process_flag,
         R_ITEMS_TRAN_INTORG.item_id,
         l_from_orgid,
         R_ITEMS_TRAN_INTORG.FROM_ORG,
         3,--TRANSACTION_TYPE_ID
         (R_ITEMS_TRAN_INTORG.received_qty)*(-1),
         l_primary_UOM,
         sysdate,
         l_to_orgid,
         R_ITEMS_TRAN_INTORG.TO_ORG,
         1,--TRANSACTION_MODE
         null,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         l_transaction_header_id,
         l_TRANSACT_INTERFACE_ID,
         l_to_loc_id,
         l_from_loc_id                
         );
       
                
         INSERT
         INTO MTL_TRANSACTION_LOTS_INTERFACE
       (
         TRANSACTION_INTERFACE_ID,
         SOURCE_CODE,
         SOURCE_LINE_ID,
         LOT_NUMBER,
         TRANSACTION_QUANTITY,  
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         last_update_login     
       )
        VALUES
       (l_TRANSACT_INTERFACE_ID,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
         R_ITEMS_TRAN_INTORG.LOT_NUMBER,
         (R_ITEMS_TRAN_INTORG.received_qty)*(-1),
         1234,
         sysdate,
         1234,
         sysdate,
         2213
       );
   
      INSERT INTO RCV_HEADERS_INTERFACE
      (
      HEADER_INTERFACE_ID,
      GROUP_ID,
      PROCESSING_STATUS_CODE,
      RECEIPT_SOURCE_CODE,
      TRANSACTION_TYPE,
      AUTO_TRANSACT_CODE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      VALIDATION_FLAG,
      COMMENTS,
      SHIPMENT_NUM,
      FROM_ORGANIZATION_ID,
      SHIP_TO_ORGANIZATION_ID
      )
      VALUES
      (
      V_HEADER_ID, -- HEADER_INTERFACE_ID,
      V_GROUP_ID, -- GROUP_ID,
      'PENDING', -- PROCESSING_STATUS_CODE,
      'INVENTORY',-- RECEIPT_SOURCE_CODE,
      'NEW',-- TRANSACTION_TYPE,
      'DELIVER',-- AUT_TRANSACT_CODE,
      SYSDATE, -- LAST_UPDATE_DATE,
      1234,-- LAST_UPDATE_BY,
      SYSDATE, -- CREATION_DATE,
      1234,-- CREATED_BY,
      'Y',-- VALIDATION_FLAG,
      'Receiving Through Interface',-- COMMENTS,
      R_ITEMS_TRAN_INTORG.MIS_NUMBER,-- SHIPMENT_NUM,
      l_from_orgid,-- FROM_ORGANIZATION_ID,
      l_to_orgid-- SHIP_TO_ORGANIZATION_ID
      );
        
        
       insert into RCV_TRANSACTIONS_INTERFACE
       (
       INTERFACE_TRANSACTION_ID,
       HEADER_INTERFACE_ID,
       GROUP_ID,
       TRANSACTION_TYPE,
       TRANSACTION_DATE,
       PROCESSING_STATUS_CODE,
       PROCESSING_MODE_CODE,
       TRANSACTION_STATUS_CODE,
       QUANTITY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       RECEIPT_SOURCE_CODE,
       DESTINATION_TYPE_CODE,
       AUTO_TRANSACT_CODE,
       SOURCE_DOCUMENT_CODE,
       UNIT_OF_MEASURE,
       ITEM_ID,
       UOM_CODE,     
       SHIPMENT_HEADER_ID,
       SHIPMENT_LINE_ID,
       TO_ORGANIZATION_ID,
       SUBINVENTORY,
       FROM_ORGANIZATION_ID,
       FROM_SUBINVENTORY
       )
       VALUES
       (
       L_TRANSACTION_INTERFACE_ID,-- INTERFACE_TRANSACTION_ID,
       V_HEADER_ID, --- HEADER_INTERFACE_ID,
       V_GROUP_ID, --- GROUP_ID,
       'RECEIVE',-- TRANSACTION_TYPE,
       SYSDATE, -- TRANSACTION_DATE,
       'PENDING',-- PROCESSING_STATUS_CODE,
       'BATCH',-- PROCESSING_MODE_CODE,
       'PENDING',-- TRANSACTION_STATUS_CODE,
       (R_ITEMS_TRAN_INTORG.received_qty),-- QUANTITY,
       SYSDATE, -- LAST_UPDATE_DATE,
       1234,-- LAST_UPDATED_BY,
       SYSDATE, -- CREATION_DATE,
       1234,-- CREATED_BY,
       'INVENTORY',-- RECEIPT_SOURCE_CODE,
       'INVENTORY',-- DESTINATION_TYPE_CODE,
       'DELIVER',-- AUTO_TRANSACT_CODE,
       'INVENTORY',-- SOURCE_DOCUMENT_CODE,
       l_primary_UOM,-- UNIT_OF_MEASURE,
       R_ITEMS_TRAN_INTORG.item_id,-- ITEM_ID,
       l_primary_UOM,-- UOM_CODE,     
       V_HEADER_ID,-- SHIPMENT_HEADER_ID,
       V_HEADER_ID,-- SHIPMENT_LINE_ID,
       l_to_orgid,-- TO_ORGANIZATION_ID,
       R_ITEMS_TRAN_INTORG.TO_ORG,-- SUBINVENTORY_ID,
       l_from_orgid,-- FROM_ORGANIZATION_ID,
       R_ITEMS_TRAN_INTORG.FROM_ORG -- FROM_SUBINVENTORY
       );     
        
           
         V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
        ( p_api_version => 1.0 ,
          p_init_msg_list => l_init_msg_list ,
          p_commit => l_commit ,
          p_validation_level =>l_validation_level ,
          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 => L_TRANSACTION_HEADER_ID);
       
               
     IF (l_return_status <> 'S') THEN
       l_msg_cnt:=nvl(l_msg_cnt,0)+1;
       For i IN 1..l_msg_cnt LOOP
         IF i = 1 then
            l_msg_data :='Error for Item : '|| R_ITEMS_TRAN_INTORG.LOT_NUMBER  ||chr(10) || fnd_msg_pub.get(i,'F');
         ELSE
             l_msg_data := fnd_msg_pub.get(i,'F');        
         END IF;
          fnd_message.set_string(substrb(l_msg_data,1,2000));
          fnd_message.show;
          raise form_trigger_failure;
       END LOOP;  
      END IF;
   end loop;
 end if;
exception
 when others then
  message('cursor in exception of interorg transfer'||sqlerrm);
end;  
 --      
 -- if codition for subinventory transfer
begin
if :SDDL_MIS_MST.ORG_TXFR='N' then

    FOR R_ITEMS_TRAN IN C_ITEMS_TRAN
  
    LOOP
     IF R_ITEMS_TRAN.from_location IS NOT NULL THEN
      select inventory_location_id
        INTO l_from_loc_id
        from MTL_ITEM_LOCATIONS_KFV MIL
       where mil.Concatenated_segments=R_ITEMS_TRAN.from_location
         and  Organization_id=:GLOBAL.ORG_ID;
     END IF;
     
     IF R_ITEMS_TRAN.to_location IS NOT NULL THEN
      select inventory_location_id
        INTO l_to_loc_id
        from MTL_ITEM_LOCATIONS_KFV MIL
       where mil.Concatenated_segments=R_ITEMS_TRAN.to_location
         and  Organization_id=:GLOBAL.ORG_ID;
     END IF;
       select  primary_uom_code,secondary_uom_code
         INTO  l_primary_UOM,l_sec_UOM
         FROM  MTL_SYSTEM_ITEMS
        WHERE  INVENTORY_ITEM_ID=R_ITEMS_TRAN.item_id
          and  Organization_id=:GLOBAL.ORG_ID;
       
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_TRANSACTION_INTERFACE_ID
       FROM DUAL;
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_transaction_header_id
       FROM DUAL;
  
    
   INSERT INTO MTL_TRANSACTIONS_INTERFACE
  (
    created_by ,
    creation_date ,
    last_updated_by ,
    last_update_date ,
    last_update_login ,
    organization_id,
    inventory_item_id ,
    subinventory_code ,
    locator_id ,   
    process_flag ,
    transaction_mode ,
    transaction_source_name,
    source_code ,
    source_header_id ,
    source_line_id ,
    transaction_date ,
    transaction_header_id ,
    transaction_interface_id ,
    transaction_type_id ,
    transaction_quantity ,
    transaction_uom ,
    transfer_locator ,
    transfer_subinventory
  )
   VALUES
   (1234,
    sysdate,
    1234,
    sysdate,
    2213,
    :GLOBAL.ORG_ID,
    R_ITEMS_TRAN.item_id,
    R_ITEMS_TRAN.from_whse,
    l_from_loc_id,
    1,--process_flag,
    2,--transaction_mode
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_TRN_ID,
    R_ITEMS_TRAN.MIS_LINE_ID,
    sysdate,
    l_transaction_header_id, --header_id
    l_TRANSACTION_INTERFACE_ID, -- interface_id
    2,--subinventory
    (R_ITEMS_TRAN.received_qty)*(-1),
    l_primary_UOM,
    l_to_loc_id,
    R_ITEMS_TRAN.to_whse
   );
  

   INSERT
    INTO MTL_TRANSACTION_LOTS_INTERFACE
  (
    TRANSACTION_INTERFACE_ID,
    SOURCE_CODE,
    SOURCE_LINE_ID,
    LOT_NUMBER, 
    TRANSACTION_QUANTITY, 
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,   
    LAST_UPDATE_LOGIN
  )
  VALUES
  (l_TRANSACTION_INTERFACE_ID,
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_LINE_ID,
    R_ITEMS_TRAN.LOT_NUMBER,
    (R_ITEMS_TRAN.received_qty)*(-1),
    1234,
    sysdate,
    1234,
    sysdate,
    2213
  );
 
    
   V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
   ( p_api_version => 1.0 ,
     p_init_msg_list => l_init_msg_list ,
     p_commit => l_commit ,
     p_validation_level =>l_validation_level ,
     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 => L_TRANSACTION_HEADER_ID);

   IF (l_return_status <> 'S') THEN
   l_msg_cnt:=nvl(l_msg_cnt,0)+1;
       For i IN 1..l_msg_cnt LOOP
         IF i = 1 then
            l_msg_data :='Error for Item : '|| R_ITEMS_TRAN.LOT_NUMBER  ||chr(10) || fnd_msg_pub.get(i,'F');
         ELSE
             l_msg_data := fnd_msg_pub.get(i,'F');        
         END IF;
          fnd_message.set_string(substrb(l_msg_data,1,2000));
          fnd_message.show;
          raise form_trigger_failure;
       END LOOP;  
  END IF;    
      
   END LOOP;
end if;
exception
 when others then
  message('cursor in exception of subinventory transfer'||sqlerrm);
end;  
-- end if for subinventory transfer
 
   END  TRANSACT_ITEMS;  
END;

Dynamic Record Group in Oracle forms

declare
R RECORDGROUP;
n number;
B BOOLEAN;
begin
R:=FIND_GROUP('drg');
if not ID_NULL(R) then
delete_group('drg');
end if;
r:=create_group_from_query('drg','select
a.ORGANIZATION_CODE,B.SECONDARY_INVENTORY_NAME
from ORG_ORGANIZATION_DEFINITIONS
a,MTL_SECONDARY_INVENTORIES B
where a.ORGANIZATION_ID=b.ORGANIZATION_ID and
a.organization_id=:SDDL_MIS_MST.FROM_ORG');
N:=POPULATE_GROUP('drg');
if N=0 then
SET_LOV_PROPERTY('sub_inv_lov',GROUP_NAME,'drg');
SET_LOV_COLUMN_PROPERTY('sub_inv_lov',1,TITLE,'org code');
b:=show_lov('sub_inv_lov');
end if;
end;

DECLARE
  ora_err NUMBER;
  rg_id   RECORDGROUP;
BEGIN
  rg_id := CREATE_GROUP_FROM_QUERY('CGLL$POP_LIST','
SELECT LIBELLE , CODE
FROM ( SELECT column1 CODE,
       column2 LABEL
FROM your_table_name
WHERE   ( your_where_clause) ) ');
  /* Populate the record group by executing the query */
  ora_err := POPULATE_GROUP(rg_id);
    /* Intitialise the list item with the record group */
    POPULATE_LIST('VU.CODE_GENRE', rg_id);
  /* Always delete the record group */
  DELETE_GROUP(rg_id);
END;

declare
l_rec_name RecordGroup;
l_rec_col GroupColumn;
l_rec_col1 GroupColumn;
cursor l_ename is select COMPANIES_ID from SYSMODULE.COMPANIES;
l_row number(12):= 1;
begin
l_rec_name := create_group('rg_dynamic',Form_scope, 100);
l_rec_col := add_group_column(l_rec_name,'COMPANIES_ID',char_column,30);
l_rec_col1 :=
add_group_column(l_rec_name,'COMPANY_NAME',number_column,6);
for i in l_ename
loop
add_group_row(l_rec_name, end_of_group);
set_group_char_cell(l_rec_col,l_row, i.COMPANIES_ID);
set_group_number_cell(l_rec_col1,l_row, l_row);
l_row := l_row+1;
end loop;
set_lov_property('LOV7',group_name,'RG_DYNAMIC');
end;

DECLARE
rg_name VARCHAR2(40) := 'company_Range';
rg_id RecordGroup;
errcode NUMBER;
lov_id LOV;
BEGIN
rg_id := Find_Group( rg_name );
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name, 'select COMPANIES_ID from
SYSMODULE.COMPANIES');
END IF;
errcode := Populate_Group( rg_id );
lov_id := Find_LOV('LOV7');
Set_LOV_Property(lov_id,GROUP_NAME,'rg_name');
END;

PROCEDURE POPULATE_LIST_WITH_QUERY
( P_LIST IN VARCHAR2,
P_QUERY IN VARCHAR2)
IS
RG_NAME VARCHAR2(30):='P_RG_NAME';
R_GROUP RECORDGROUP;
STATUS NUMBER;
C VARCHAR2(30):=GET_ITEM_PROPERTY(P_LIST,ITEM_NAME);
BEGIN
R_GROUP:= FIND_GROUP(RG_NAME);
IF NOT ID_NULL(R_GROUP) THEN
DELETE_GROUP(R_GROUP);
END IF;
R_GROUP := CREATE_GROUP_FROM_QUERY( RG_NAME,P_QUERY);
IF POPULATE_GROUP(R_GROUP)= 0 THEN
POPULATE_LIST(P_LIST,R_GROUP);
COPY(GET_LIST_ELEMENT_VALUE(P_LIST,1),P_LIST);
END IF;
END;

declare
 rg_id recordgroup;
 rg_nm varchar2(40) := 'RG_NAME';
 v_num number;
 v_col groupcolumn;
 v_lv  lov;
begin
 :System.Message_Level := 0;
if :block3.vc_org is not null then  
 rg_id := find_group(rg_nm);
   
 if not id_null(rg_id) then
   delete_group(rg_id);
 end if;

   if id_null(rg_id) then
 rg_id := create_group_from_query(rg_nm, 'select  segment1 "segment1", segment1 "segment2"  from mtl_system_items where   organization_id = '||:block3.vc_org);
  end if;
 message('Record Group: '||rg_nm);
 message('Record Group: '||rg_nm);    
 v_num := populate_group(rg_id); 
  message('pop: '|| v_num);
  if v_num = 0 then
      v_lv  := find_lov('LOV69');   
      set_lov_property('LOV69',group_name,rg_nm);
      set_item_property('block3.vc_segment1',lov_name,'LOV69');
  end if;
  delete_group(rg_id);
end if;
exception
 when others then
 message(sqlerrm);
end;

Register concurrent program from backend

Purpose: Package to generate calls to FND_PROGRAM APIs
 which can then be extracted into a sql file
 and ran on any other database to register custom
 reports including their EXEs, PROGRAM Definitions,
 PROGRAM Parameters and register PROGRAM in the group.

DROP TABLE SQL_TEMP CASCADE CONSTRAINTS ;
CREATE TABLE SQL_TEMP (
  SQL_STAGE   VARCHAR2(4),
  LINE_ID     NUMBER(15),
  SQL_STRING  VARCHAR2(300));

CREATE OR REPLACE package vm_apps_program AS
-- ===================================================================================
-- PROGRAM NAME  : VM_APPS_PROGRAM
-- APPLICATION   : AOL
-- VERSION       : 1.0 (Oracle APPS 11.0.3)
-- DESCRIPTION   : Package to generate calls to FND_PROGRAM APIs
--                 which can then be extracted into a sql file
--                 and ran on any other database to register custom
--                 reports including their EXEs, PROGRAM Definitions,
--                 PROGRAM Parameters and register PROGRAM in the group.
-- EXAMPLE       : execute VM_APPS_PROGRAM('HKIS','HKISRMA','P');
--                                           |     |         |
--                                           |     |         +-> report EXE type
--                                           |     +-----------> report EXEs name
--                                           +-----------------> report EXEs application name
--                 Valid Executable Types:
--                 'B' ==> 'Request Set Stage Function',
--                 'Q' ==> 'SQL*Plus',
--                 'H' ==> 'Host',
--                 'L' ==> 'SQL*Loader',
--                 'A' ==> 'Spawned',
--                 'I' ==> 'PL/SQL Stored Procedure',
--                 'P' ==> 'Oracle Reports',
--                 'S' ==> 'Immediate'
-- ===================================================================================
-- MODIFICATION HISTORY
-- ===================================================================================
-- DATE      NAME          DESCRIPTION
-- -----------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------
PROCEDURE reg_exe(
    p_app_short_name  IN VARCHAR2,
    p_exe_name        IN VARCHAR2,
    p_exe_type        IN VARCHAR2 DEFAULT 'P' );
PROCEDURE reg_program(
    p_exe_id          IN NUMBER,
    p_exe_short_name  IN VARCHAR2,
    p_exe_app_name    IN VARCHAR2 );
PROCEDURE reg_param(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2 );
PROCEDURE reg_in_group(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2,
    p_prog_id         IN NUMBER );
END vm_apps_program;
/
CREATE OR REPLACE package body vm_apps_program AS
-- ===================================================================================
-- PROGRAM NAME  : VM_APPS_PROGRAM
-- APPLICATION   : AOL
-- VERSION       : 1.0 (Oracle APPS 11.0.3)
-- DESCRIPTION   : Package to generate calls to FND_PROGRAM APIs
--                 which can then be extracted into a sql file
--                 and ran on any other database to register custom
--                 reports including their EXEs, PROGRAM Definitions,
--                 PROGRAM Parameters and register PROGRAM in the group.
-- EXAMPLE       : execute VM_APPS_PROGRAM('HKIS','HKISRMA','P');
--                                           |     |         |
--                                           |     |         +-> report EXE type
--                                           |     +-----------> report EXEs name
--                                           +-----------------> report EXEs application name
--                 Valid Executable Types:
--                 'B' ==> 'Request Set Stage Function',
--                 'Q' ==> 'SQL*Plus',
--                 'H' ==> 'Host',
--                 'L' ==> 'SQL*Loader',
--                 'A' ==> 'Spawned',
--                 'I' ==> 'PL/SQL Stored Procedure',
--                 'P' ==> 'Oracle Reports',
--                 'S' ==> 'Immediate'
-- ===================================================================================
-- MODIFICATION HISTORY
-- ===================================================================================
-- DATE      NAME          DESCRIPTION
-- -----------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------
/* +--------------------------------------------------------+ */
/* +---------------------- PRIVATE VARS --------------------+ */
/* +--------------------------------------------------------+ */
l_line_id        NUMBER(15) := 1;

/* +--------------------------------------------------------+ */
/* +---------------------- PRIVATE MODULES -----------------+ */
/* +--------------------------------------------------------+ */
FUNCTION write_out (
    p_sql_stage   IN VARCHAR2,
    p_line_id     IN NUMBER,
    p_sql_string  IN VARCHAR2) RETURN NUMBER IS
    l_next_line_id NUMBER(15);
BEGIN
    insert into sql_temp values (p_sql_stage, p_line_id, p_sql_string);
    l_next_line_id := p_line_id + 1;
    RETURN l_next_line_id;
END write_out;

/* +--------------------------------------------------------+ */
/* +---------------------- PUBLIC MODULES ------------------+ */
/* +--------------------------------------------------------+ */
PROCEDURE reg_exe(
    p_app_short_name  IN VARCHAR2,
    p_exe_name        IN VARCHAR2,
    p_exe_type        IN VARCHAR2 DEFAULT 'P' ) AS
 CURSOR exe_cur IS
   select fe.executable_id                          e_id
   ,      fe.executable_name                        e_name
   ,      fet.description                           e_desc
   ,      decode(fe.EXECUTION_METHOD_CODE,
               'B', 'Request Set Stage Function',
               'Q', 'SQL*Plus',
               'H', 'Host',
               'L', 'SQL*Loader',
               'A', 'Spawned',
               'I', 'PL/SQL Stored Procedure',
               'P', 'Oracle Reports',
               'S', 'Immediate',    'N/A')          e_method
   ,      fe.execution_file_name                    e_f_name
   from   fnd_executables     fe
   ,      fnd_executables_tl  fet
   where  fe.execution_file_name   = p_exe_name
   and    fe.execution_method_code = p_exe_type
   and    fe.executable_id         = fet.executable_id(+)
   order by execution_file_name;
 l_cur_exe_id         fnd_executables.executable_id%TYPE;
 l_cur_exe_name       fnd_executables.executable_name%TYPE;
 l_cur_app_short_name VARCHAR2(15) := p_app_short_name;
 l_error_message      VARCHAR2(100);
BEGIN
    FOR e_rec IN exe_cur
    LOOP
       l_cur_exe_id   := e_rec.e_id;
       l_cur_exe_name := e_rec.e_name;
       l_line_id := write_out('EXE',l_line_id,'          ');
       l_line_id := write_out('EXE',l_line_id,'prompt REGISTERING executable '||e_rec.e_name);
       l_line_id := write_out('EXE',l_line_id,'BEGIN');
       l_line_id := write_out('EXE',l_line_id,'FND_PROGRAM.EXECUTABLE( ');
       l_line_id := write_out('EXE',l_line_id,'     executable           => '||chr(39)||e_rec.e_name     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     application          => '||chr(39)||p_app_short_name ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     short_name           => '||chr(39)||e_rec.e_name     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     description          => '||chr(39)||e_rec.e_desc     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     execution_method     => '||chr(39)||e_rec.e_method   ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     execution_file_name  => '||chr(39)||e_rec.e_f_name   ||chr(39)||');');
       l_line_id := write_out('EXE',l_line_id,'END;');
       l_line_id := write_out('EXE',l_line_id,'/');
       /*
       || Register All Programs
       || for this executable calling reg_program
       */
       reg_program(
              p_exe_id         => e_rec.e_id,
              p_exe_short_name => e_rec.e_name,
              p_exe_app_name   => p_app_short_name);
    END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_error_message := substr(SQLERRM,1,100);
          dbms_output.put_line('Error On: '||l_cur_exe_id||'           EXE ID');
          dbms_output.put_line('Error On: '||l_cur_exe_name||'          EXE Name');
          dbms_output.put_line('Error On: '||l_cur_app_short_name||' APP Short Name');
          dbms_output.put_line('Error Message: '||l_error_message);
--    COMMIT;
END reg_exe;
PROCEDURE reg_program(
    p_exe_id          IN NUMBER,
    p_exe_short_name  IN VARCHAR2,
    p_exe_app_name    IN VARCHAR2 ) AS
 CURSOR prog_cur IS
   select  fcp.application_id                 p_app_id
   ,       fcp.concurrent_program_id          p_id
   ,       fcpt.user_concurrent_program_name  p_name
   ,       fa.application_short_name          p_app_name
   ,       fcp.enabled_flag                   p_enabled_flag
   ,       fcp.concurrent_program_name        p_short_name
   ,       fcpt.description                   p_desc
   ,       fcp.execution_options              p_exe_option
   ,       fcp.request_priority               p_priority
   ,       fcp.save_output_flag               p_save_output
   ,       fcp.print_flag                     p_print
   ,       fcp.minimum_width                  p_cols
   ,       fcp.minimum_length                 p_rows
   ,       fcp.output_print_style             p_style
   ,       fcp.required_style                 p_style_required
   ,       fcp.printer_name                   p_printer
   ,       'Y'                                p_use_in_srs
   ,       'Y'                                p_allow_disabled_values
   ,       fcp.run_alone_flag                 p_run_alone
   ,       fcp.output_file_type               p_output_type
   ,       fcp.enable_trace                   p_enable_trace
   ,       fcp.restart                        p_restart
   ,       fcp.nls_compliant                  p_nls_compliant
   from    fnd_application              fa
   ,       fnd_concurrent_programs_tl   fcpt
   ,       fnd_concurrent_programs      fcp
   where   fcp.executable_id          = p_exe_id
   and     fcp.enabled_flag           = 'Y'
   and     fcp.concurrent_program_id  = fcpt.concurrent_program_id
   and     fcp.application_id         = fcpt.application_id
   and     fcp.application_id         = fa.application_id
   order by fcp.concurrent_program_name;
BEGIN
    FOR p_rec IN prog_cur
    LOOP
       l_line_id := write_out('PROG',l_line_id,'          ');
       l_line_id := write_out('PROG',l_line_id,'prompt REGISTERING program '||p_rec.p_name);
       l_line_id := write_out('PROG',l_line_id,'BEGIN');
       l_line_id := write_out('PROG',l_line_id,'FND_PROGRAM.REGISTER( ');
       l_line_id := write_out('PROG',l_line_id,'   program                => '||chr(39)||p_rec.p_name                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   application            => '||chr(39)||p_rec.p_app_name              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   enabled                => '||chr(39)||p_rec.p_enabled_flag          ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   short_name             => '||chr(39)||p_rec.p_short_name            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   description            => '||chr(39)||p_rec.p_desc                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   executable_short_name  => '||chr(39)||p_exe_short_name              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   executable_application => '||chr(39)||p_exe_app_name                ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   execution_options      => '||chr(39)||p_rec.p_exe_option            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   priority               => '||chr(39)||p_rec.p_priority              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   save_output            => '||chr(39)||p_rec.p_save_output           ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   print                  => '||chr(39)||p_rec.p_print                 ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   cols                   => '||chr(39)||p_rec.p_cols                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   rows                   => '||chr(39)||p_rec.p_rows                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   style                  => '||chr(39)||p_rec.p_style                 ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   style_required         => '||chr(39)||p_rec.p_style_required        ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   printer                => '||chr(39)||p_rec.p_printer               ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   use_in_srs             => '||chr(39)||p_rec.p_use_in_srs            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   allow_disabled_values  => '||chr(39)||p_rec.p_allow_disabled_values ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   run_alone              => '||chr(39)||p_rec.p_run_alone             ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   output_type            => '||chr(39)||p_rec.p_output_type           ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   enable_trace           => '||chr(39)||p_rec.p_enable_trace          ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   restart                => '||chr(39)||p_rec.p_restart               ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   nls_compliant          => '||chr(39)||p_rec.p_nls_compliant         ||chr(39)||');');
       l_line_id := write_out('PROG',l_line_id,'END;');
       l_line_id := write_out('PROG',l_line_id,'/');
       reg_param(
            p_prog_app_id     => p_rec.p_app_id,
            p_prog_app_name   => p_rec.p_app_name,
            p_prog_short_name => p_rec.p_short_name);
       reg_in_group(
            p_prog_app_id     => p_rec.p_app_id,
            p_prog_app_name   => p_rec.p_app_name,
            p_prog_short_name => p_rec.p_short_name,
            p_prog_id         => p_rec.p_id);
    END LOOP;
--    COMMIT;
END reg_program;

PROCEDURE reg_param(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2 ) IS
 CURSOR par_cur IS
   SELECT      fdfcu.application_id                  p_app_id
   ,           fdfcu.column_seq_num                  p_seq
   ,           fdfcu.end_user_column_name            p_param_name
   ,           fdfcu.enabled_flag                    p_enabled
   ,           ffvs.flex_value_set_name              p_value_set
   ,           decode(fdfcu.default_type,
                       'C','Constant',
                       'P','Profile',
                       'S','SQL Statement',
                       'A','Segment')                p_defaul_type
   ,           replace(fdfcu.default_value,
                        chr(39),chr(39)||chr(39))    p_default_value
   ,           fdfcu.required_flag                   p_required
   ,           fdfcu.security_enabled_flag           p_enable_security
   ,           decode(fdfcu.range_code,
                       'P','Pair',
                       'H','High',
                       'L','Low')                    p_range
   ,           fdfcu.display_flag                    p_display
   ,           fdfcu.display_size                    p_display_size
   ,           fdfcu.maximum_description_len         p_desc_size
   ,           fdfcu.concatenation_description_len   p_conc_desc_size
   ,           replace(fdfcu.form_left_prompt,
                        chr(39),chr(39)||chr(39))    p_prompt
   ,           fdfcu.srw_param                       p_token
   FROM        fnd_flex_value_sets           ffvs
   ,           fnd_descr_flex_col_usage_vl   fdfcu
   WHERE       fdfcu.descriptive_flexfield_name = '$SRS$.'||p_prog_short_name
   AND         fdfcu.application_id = p_prog_app_id
   AND         fdfcu.application_id = fdfcu.application_id
   AND         fdfcu.flex_value_set_id = ffvs.flex_value_set_id
   ORDER BY    fdfcu.column_seq_num;
BEGIN
    FOR par_rec IN par_cur
    LOOP
       l_line_id := write_out('FLEX',l_line_id,'DECLARE');
       l_line_id := write_out('FLEX',l_line_id,' dummy VARCHAR2(1);');
       l_line_id := write_out('FLEX',l_line_id,'BEGIN');
       l_line_id := write_out('FLEX',l_line_id,'  SELECT '||chr(39)||'X'||chr(39) );
       l_line_id := write_out('FLEX',l_line_id,'  INTO   dummy');
       l_line_id := write_out('FLEX',l_line_id,'  FROM   fnd_flex_value_sets');
       l_line_id := write_out('FLEX',l_line_id,'  WHERE  flex_value_set_name = '||chr(39)||par_rec.p_value_set||chr(39)||';');
       l_line_id := write_out('FLEX',l_line_id,'    EXCEPTION');
       l_line_id := write_out('FLEX',l_line_id,'      WHEN NO_DATA_FOUND');
       l_line_id := write_out('FLEX',l_line_id,'      THEN');
       l_line_id := write_out('FLEX',l_line_id,'         dbms_output.put_line('||chr(39)||'Value Set: '||par_rec.p_value_set||' missing ...'||chr(39)||');' );
       l_line_id := write_out('FLEX',l_line_id,'         dbms_output.put_line('||chr(39)||'===> Program Name: '||p_prog_short_name||chr(39)||');' );
       l_line_id := write_out('FLEX',l_line_id,'END;');
       l_line_id := write_out('FLEX',l_line_id,'/');
       l_line_id := write_out('PAR',l_line_id,'          ');
       l_line_id := write_out('PAR',l_line_id,'prompt REGISTERING parameter '||p_prog_short_name||': '||par_rec.p_param_name);
       l_line_id := write_out('PAR',l_line_id,'DECLARE');
       l_line_id := write_out('PAR',l_line_id,'   l_error_message varchar2(500);');
       l_line_id := write_out('PAR',l_line_id,'   l_par_already_exist BOOLEAN;');
       l_line_id := write_out('PAR',l_line_id,'BEGIN');
       l_line_id := write_out('PAR',l_line_id,'  l_par_already_exist :=  FND_PROGRAM.parameter_exists(');
       l_line_id := write_out('PAR',l_line_id,'       program_short_name => '||chr(39)||p_prog_short_name       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       application        => '||chr(39)||p_prog_app_name         ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       parameter          => '||chr(39)||par_rec.p_param_name    ||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  IF NOT l_par_already_exist');
       l_line_id := write_out('PAR',l_line_id,'  THEN');
       l_line_id := write_out('PAR',l_line_id,'     FND_PROGRAM.PARAMETER( ');
       l_line_id := write_out('PAR',l_line_id,'       program_short_name                => '||chr(39)||p_prog_short_name       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       application                       => '||chr(39)||p_prog_app_name         ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       sequence                          => '||chr(39)||par_rec.p_seq           ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       parameter                         => '||chr(39)||par_rec.p_param_name    ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       description                       => '||chr(39)||par_rec.p_param_name    ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       enabled                           => '||chr(39)||par_rec.p_enabled       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       value_set                         => '||chr(39)||par_rec.p_value_set     ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       default_type                      => '||chr(39)||par_rec.p_defaul_type   ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       default_value                     => '||chr(39)||par_rec.p_default_value ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       required                          => '||chr(39)||par_rec.p_required      ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       enable_security                   => '||chr(39)||par_rec.p_enable_security  ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       range                             => '||chr(39)||par_rec.p_range            ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       display                           => '||chr(39)||par_rec.p_display          ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       display_size                      => '||chr(39)||par_rec.p_display_size     ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       description_size                  => '||chr(39)||par_rec.p_desc_size        ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       concatenated_description_size     => '||chr(39)||par_rec.p_conc_desc_size   ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       prompt                            => '||chr(39)||par_rec.p_prompt           ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       token                             => '||chr(39)||par_rec.p_token            ||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  ELSE');
       l_line_id := write_out('PAR',l_line_id,'     insert into vm_process values('||chr(39)||p_prog_short_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                   '||chr(39)||par_rec.p_param_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                   '||chr(39)||'Parameter Already Exist'||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  END IF;');
       l_line_id := write_out('PAR',l_line_id,'  EXCEPTION');
       l_line_id := write_out('PAR',l_line_id,'    WHEN OTHERS');
       l_line_id := write_out('PAR',l_line_id,'    THEN');
       l_line_id := write_out('PAR',l_line_id,'        l_error_message := SQLERRM;');
       l_line_id := write_out('PAR',l_line_id,'        insert into vm_process values('||chr(39)||p_prog_short_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                      '||chr(39)||par_rec.p_param_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                      l_error_message);');
       l_line_id := write_out('PAR',l_line_id,'END;');
       l_line_id := write_out('PAR',l_line_id,'/');
    END LOOP;
--    COMMIT;
END reg_param;
PROCEDURE reg_in_group(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2,
    p_prog_id         IN NUMBER ) IS
 CURSOR grp_cur IS
    select  frg.request_group_name    p_request_group
    ,       fa.application_short_name p_group_application
    from    fnd_application         fa
    ,       fnd_request_groups      frg
    ,       fnd_request_group_units frgu
    where   frgu.request_unit_id     = p_prog_id
    and     frgu.unit_application_id = p_prog_app_id
    and     frgu.request_group_id    = frg.request_group_id
    and     frgu.application_id      = frg.application_id
    and     frgu.application_id      = fa.application_id;
BEGIN
    FOR g_rec IN grp_cur
    LOOP
       l_line_id := write_out('GRP',l_line_id,'          ');
       l_line_id := write_out('GRP',l_line_id,'prompt REGISTERING program '||p_prog_short_name||' into Group '||g_rec.p_request_group);
       l_line_id := write_out('GRP',l_line_id,'BEGIN');
       l_line_id := write_out('GRP',l_line_id,'FND_PROGRAM.ADD_TO_GROUP( ');
       l_line_id := write_out('GRP',l_line_id,'   program_short_name   => '||chr(39)||p_prog_short_name         ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   program_application  => '||chr(39)||p_prog_app_name           ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   request_group        => '||chr(39)||g_rec.p_request_group     ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   group_application    => '||chr(39)||g_rec.p_group_application ||chr(39)||');');
       l_line_id := write_out('GRP',l_line_id,'END;');
       l_line_id := write_out('GRP',l_line_id,'/');
    END LOOP;
END reg_in_group;
END vm_apps_program;
/

--
-- Create API calls for all CORETEC custom reports
--
truncate table SQL_TEMP;
EXECUTE vm_apps_program.reg_exe('HKIS','HKIS Transaction Print','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISACK','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISCINV','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISDLP','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISNOTE','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISPAK','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISPOL','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRBOMS','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRFP','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRMA','P');
commit;


--
-- Create SPOOL file reg_prog.sql with API calls
--
set pages 0
set term off
set feedback off
spool reg_prog.sql
prompt set feedback off
prompt set serveroutput on
prompt
prompt drop table vm_process
prompt /
prompt
prompt create table vm_process(
prompt stage1 varchar2(50),
prompt stage2 varchar2(100),
prompt error_message varchar2(500)
prompt )
prompt /
prompt
set lines 100
prompt prompt Checking Validation Sets ...
select sql_string
from   sql_temp
where  sql_stage = 'FLEX'
order by sql_stage, line_id;
prompt prompt Done Checking Validation Sets ...
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"

select sql_string
from   sql_temp
where  sql_stage = 'EXE'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"
select sql_string
from   sql_temp
where  sql_stage = 'PROG'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"
select sql_string
from   sql_temp
where  sql_stage = 'GRP'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"

set lines 300
select sql_string
from   sql_temp
where  sql_stage = 'PAR'
order by sql_stage, line_id;
prompt set pages 0
prompt set lines 80
prompt col line_br format a80
prompt
prompt select 'Program Name:   '||stage1            line_br
prompt ,      'Parameter Name: '||stage2            line_br
prompt ,      'Error: '||error_message              line_br
prompt from vm_process
prompt where ERROR_MESSAGE != 'Parameter Already Exist'
prompt /

spool off
set term on
ed reg_prog.sql