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