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

Return Material Authorization (RMA) in Order Management (OM)

The following topics will be discussed in this article:

1. Overview of RMA
2. Creating a New Return (RMA with Credit)
3. Creating a Return using the Copy Function (RMA with Credit)
4. Creating a New Return (RMA no Credit)
5. Creating a New Return (RMA Credit Only)
6. RMA Receipt
7. Viewing the status of RMA
8. Close RMA
   
   Pre-requisites:

1. Return Order Categories and Transaction Types have been defined.
2. Items exist in the Item Master with the attribute Returnable enabled.
3. Those items must exist on a Price List.
4. Customers exist in the Customer Master.
5. Return reasons have been defined.
6. Discounts have been defined.

7. Salespersons have been defined.

1. Overview
Order Management allows the customers to return the goods to you. OM also enables you to authorize the return of your sales orders as well as sales made by other dealers or suppliers, as long as the items are part of your item master and price list. You can authorize returns for replacement that returns with or without credit.

RMA can be created in two different ways in Oracle:

• Create a New Return which will create the RMA from scratch
• Copy an original order into an order with an RMA Order Type

R2i supports four different RMA Order Types, each with a different Order Cycle:

• RMA with Credit is used when the customer returns the physical product and also receives credit as a result of the return.
  This type applies for:
  • Defective Product
  • Customer does not like the product
  • Product does not meet the customer’s expectations

• RMA no Credit is used when the customer will return the product but will not be receiving a credit as a result of the return.
  These returns would be for:
  • Evaluation Orders
  • Samples
  • Other orders where the customer was not originally charged for the product.

• RMA Credit Only is used when the customer will receive a credit, but the physical return of the product is not required.
  • These credits are generally used by software companies when the customer destroys the CD or disk and erases the software from their machine, but no physical thing to return.

• RMA with Credit and Approval is used in the same manner as an RMA with Credit but this order cycle includes an approval process that requires someone to approve the RMA before it is booked. In order for an order/return or order/return line approval workflow to work correctly the profile option OM: Notification Approver must have a value.

 2. Creating a New Return (RMA with Credit)

Select:	Order Management responsibility
Navigate to:	Orders, Returns > Order Organizer or Orders, Returns > Sales Orders
Select:	New Order button if using the Order Organizer

Customer:	Enter Customer Name (Customer Number will default in).
Customer Number:	Alternatively, enter Customer Number and the Customer Name will default in.
Order Type:	RMA Order with Credit
Customer PO:	Enter a Customer PO number if the order type you selected requires it.
Date Ordered:	The Current Date will default in as the Order Date. You may change this date or accept the current date.
Customer Contact:	Enter a Customer contact (optional).
Order Number:	Oracle will assign the RMA number as soon as the information is saved if automatic numbering is enabled.
Price List:	Select a price list from the list of values
Ship To:	Enter the customer’s ship-to address from the list of values or accept the default. (Not required for Returns)
Salesperson:	Enter the Salesperson
Status:	The initial entry status for an RMA is Entered. After Booking the RMA status will changed to Booked.
Currency:	Select the currency for the RMA from the list of values or accept the default.
Bill To:	Enter the customer’s bill-to address from the list of values or accept the default.

Order Information – Others

Payment Terms:	Payment Terms are user-definable and must be setup in advance. (Setup>Orders>Payment Terms). Select from the list of values or accept the default. (Not required for Returns)
Sales Channel:	Sales Channels are user-definable and must be setup in advance (Order Management Quick Codes). Select a Sales Channel from the list of values or accept the default.
Warehouse:	Select a Warehouse (inventory organization) from which Returns will be received.
Shipping Method:	Not used for Returns
Line Set:	Not used for Returns
Freight Terms:
FOB:	Defaults (In order, from Ship-To, Bill-To, Customer, Order Type, or Price List)
Shipment Priority:	Defaults from Order Type
Shipping Instruct:	Shipping instructions are printed on the pick slip. Since this is a return do not use this field.
Packing Instructions:	Packing instructions are printed on the pack slip. Since this is a return do not use this field.
Tax Handling:	Select from the following: Exempt – Indicates that this order is exempt for a normally taxable customer site and/or item. If you select exempt you must enter a reason for exemption. Require – Indicates that this order is taxable for a normally non- taxable customer and/or item. Standard – Indicates that taxation should be based on existing exemption rules. If the customer has a tax exemption defined, Order Management displays any certificate number and reason for the exemption in the corresponding fields.
Tax Exempt Number:	If you choose Exempt in the Tax Handling field then select an existing certificate number for the ship-to customer, or enter a new, unapproved exemption certificate number. If you chose Standard in the Tax Handling field, an existing exemption rule may display a certificate number in this field. Unapproved exemption certificate numbers can be approved using the Tax Exemptions window.
Exempt Reason:	If you chose Exempt in the Tax Handling field then select an exemption reason. If you chose Standard in the Tax Handling field, an existing exemption rule may display an exemption reason in this field. You can define tax exemption reasons using the Receivables Quickcodes window.
Payment Type:
Amount:	Optional unless the payment type selected requires it.
Check Number:	Optional unless the payment type selected requires it.
Credit Card Type:	Optional unless the payment type selected requires it.
Credit Card Num:	Optional unless the payment type selected requires it.
Card Holder:	Optional unless the payment type selected requires it.
Card Expiration Date:	Optional unless the payment type selected requires it.
Approval Code:	Optional unless the payment type selected requires it.
Order Source:	If the RMA is copied from an existing order/return ‘Copy’ will appear in this field.
Order Source Rule:	If the RMA is copied from an existing order/return the original order/return number will appear in this field.

Line Items Tab – Returns

Line:	This field will automatically be populated.
Ordered Item:	Enter the Ordered Item from the list of values.
Qty:	Enter the quantity to be returned.
Return Reason:	Select a defined reason from the list of values.
Line Type:	Select a line type. A line type may default depending on the transaction type setup. Select a line type from the list of values if you wish to change the defaulted value.
Reference:	Select the appropriate Reference Type. Use the Reference Type if you want to refer to a specific Invoice, Purchase Order, or Sales Order. These references must be for transactions originally placed in Oracle. You have the option of leaving them blank, in which case the customer’s credit will be placed On Account when it interfaces to Accounts Receivable. On Account credit memos may be applied to invoices at a future time.
Order:	If referencing a Sales Order then enter the Sales Order number.
Line:	If referencing a Sales Order enter the appropriate line number from the Sales Order referenced. Note: If creating the RMA using the copy function the information in the copied Sales Order will automatically populate in this field.
Invoice:	If you enter Sales Order or Invoice in the Reference field, then you have the option of selecting a specific invoice in the Invoice field. This would allow for a Credit Memo to be created and directly applied to this invoice. Leaving this field blank will yield an On Account credit memo in Receivables.
Invoice Line:	If referencing an Invoice, enter the appropriate line number from the Invoice referenced.
Credit Invoice:
Item Revision:

Line Items Tab – Main

UOM:	The UOM will default in based on the item selected to be returned.
Unit Selling Price:	The price defaults in from the invoice, purchase order, sales order, or invoice if selected in the reference field, otherwise, it will default from the price list selected on the Return.

3. Creating a Return using the Copy Function (RMA with Credit)

Select:	Order Management responsibility
Navigate to:	Orders, Returns > Order Organizer
Query:	Query an existing order or return to copy from.
Select:	Actions button in the Order Organizer window
Select:	Copy

Quick Copy Tab:

Select:	Create New Order
Change Order Type To:	Select RMA Order with Credit
New Order Number:	Enter a new RMA number for RMA order types that require manual numbering.

Copy Header Tab

To exclude child entities (lines, sales credits, notes, descriptive flex, and holds) or to re-price, navigate to the Copy Header, Copy Line, and Pricing Options tabs and deselect options as desired.

Note:

The OM: Credit Card Privileges profile option determines whether you are able to copy customer credit card information.

Copy Lines Tab

Change Line Type To:	Select RMA Line with Credit
Return Reason Code:	Select a return reason from the list of values.
Include Lines:	Includes the lines from the original order/return.
Include Descriptive Flex:	Includes the descriptive flexfield values from the original order/return.
Include Attachments:	Includes the attachments from the original order/return.
Include Fully Cancelled Lines:	Determine whether to include/exclude fully cancelled lines when using the copy feature. If fully cancelled lines are included, the lines are copied over with the original ordered quantity.

A common use of the Copy function is in the case where a customer wants to return all or part of a previous sales order. You may use the Copy function to create the return based directly on the information contained in the original sales order.

Another advantage of using the Copy function to create your RMAs is in the case where the customer will be receiving a credit for the return, Oracle can use the original sales order number to identify the original invoice in Accounts Receivable, and apply the credit directly against the original invoice.

When creating returns for configurations, copy the model line. Select the specific order lines and copy them as return lines to return individual components of a PTO configuration.

Pricing Tab

At Original Selling Price:	Select this option if you want the return to contain the original selling price in the originating order or return. Retaining the original pricing will retain all discounts and charges and the Calculate Price Flag is set to ‘Partial’ for return lines.
Re-price as of this date:	If you choose to re-price, specify the pricing date. Manual discounts and charges are removed and automatic discounts and charges are recalculated.
Select:	OK button. This will perform the copy and close the window.

If any validation errors occur, message(s) in the Messages window are displayed and indicates that an order was successfully created.

Select:

Continue button. The newly copied order is available through Order Organizer.

To update and book the RMA, select the RMA from Today’s Orders in the Order Organizer window.

Select:

Open button.

• The original sales order from which this RMA was created is identified both at the header level (in the Order Source field of the Others tab) and at the line level (in the Order Source field of the Main tab).

You have the option to manually make changes to this RMA before booking it. For example, the customer may only want to return part of one line or not return another line at all.

You may optionally update the Receive From and Credit To Addresses using the Addresses Tab in the Line Items Tab.

Under the Actions button, there are several other options:

Promotions/Pricing Attributes – You may optionally apply Discounts to each lines at this time (assuming that Discounts have been defined and you have the appropriate discounting privileges). A Discount will decrease the amount of the credit the customer will receive.

Return Lot/Serial Numbers – You can enter lot and serial numbers for the return.

Sales Credits – If the Sales Credits button was checked in preparing the Copy then Sales Credits for the return will be derived from the original order. You may change the Sales Credits for the return if you wish by using this option.

To book the order, select the Book Order button.

 4. Creating a New Return (RMA no Credit)

Select:	Order Management responsibility
Navigate to:	Orders, Returns > Order Organizer or Orders, Returns > Sales Orders
Select:	New Order button if using the Order Organizer

The process for creating an RMA no Credit is identical to creating an RMA with Credit. You have the option to create the RMA using the New Return option or the Copy option. The only difference between the two processes is that the Invoice Interface does not exist in the workflow for an Order Type of RMA no Credit. As a result, no credit memo will be created for this RMA.

Note:

Oracle does not provide a seeded workflow process to handle RMAs with Receipt no Credit; therefore, the R2i control environment provides a custom process to fulfill this need. For further information on this custom process refer to OM Transaction Types Setup and R2i OM Order Line Workflow Package.

5. Creating a New Return (RMA Credit Only)

Select:	Order Management responsibility
Navigate to:	Orders, Returns > Order Organizer or Orders, Returns > Sales Orders
Select:	New Order button if using the Order Organizer

The process for creating an RMA Credit Only is identical to creating an RMA with Credit. You have the option to create the RMA using the New Return option or the Copy option. The only difference between the two processes is that the Fulfillment activity does not exist in the workflow for an Order Type of RMA no Credit. As a result, no physical return of product is required.

 6. RMA Receipt

Select:	Purchasing Super User R2i responsibility
Navigate to:	Receiving > Receipts
Select:	The Inventory Organization for the Receipt (not Global).
Select:	OK button.

Select:	The Customer tab in the Find Expected Receipts window.
RMA Num:	Optionally enter a specific RMA number.
Line Num:	Optionally enter a specific line number on a specific RMA. Note: Can only enter a line number if you have enter a number in the RMA Num field.
Line Type:	This field will populate automatically if you enter a value in RMA Num. If you do not enter a value in RMA Num you can optionally select a line type.
Customer:	Optionally select a customer from the LOV. If you enter a value in RMA Num, this field will populate automatically.
Customer Num:	Optionally select a customer number from the LOV. If you enter a value in RMA Num, this field will populate automatically.
Customer Item Num:	Optionally select a customer item number from the LOV.

• You can further search for expected receipts using the Item, Date Ranges, and Shipments tabs.

Select:

Find button.

All the receipt lines that meet the search criteria are displayed in the Receiving Transaction form.

Only lines with a “Destination Type” of “Inventory” can be delivered to Inventory.

Select:	Checkbox next to receipt line to be delivered to inventory.
Quantity:	Enter the “Quantity” to be delivered.
Subinventory:	Enter the subinventory where the items will be delivered to.

Save. Once the transaction is saved a receipt number is assigned.

For more information on Receiving Transactions in Purchasing refer to related R2i Purchasing Training documentation.

 7. Viewing the Status of an RMA

The Sales Orders window displays the RMA header status in the Main tab of the Order Information tabbed region. The RMA line status is displayed in the Main tab of the Line Items tabbed region.

The Workflow Status option on the Sales Order window Tools menu launches the workflow status page. The window shows all the activities an RMA header or line has completed and the corresponding results in tabular format.

Note:In order to view workflow status from the Order Organizer the menu attached to the responsibility in use must have two functions assigned to it: Monitor Activities List and Workflow Status. For more information see the appropriate AOL documentation.

 8. Close the RMA

Closing RMAs that are complete enhances performance, since many programs, windows and report queries retrieve open RMAs only.

An RMA line is closed automatically once it has completed its corresponding workflow successfully. An RMA header is closed at the end of the month once all the lines have been closed.

Return Materials Authorization (RMA):

Oracle Order Management provides Return Materials Authorization (RMA) functionality within the Sales Orders window, where you can enter both standard and return order lines within the same order.

An order can have a mix of outbound (standard) and inbound (return) lines, as restricted by the order type definition.
A return line is indicated by Line Type Category of return negative and highlighted item quantity and negative line total.

Return Line types can include flows like

Creating RMAs
There are three ways to create RMA's within Order Management.

• First, identify a sales order to be returned and query the order lines. After you have selected the sales order or order lines, use the Copy function in the Actions list to generate the return order or line by specifying an RMA line type.

• Second, reference a sales order, invoice, PO number or serial number of an item directly in the Return Reference field within the Line Items tab of the Sales Orders window.


• Lastly, for return without originating sales order line, manually enter return line information and choose the appropriate return line type in the Sales Orders window.
Workflow
Order Management comes with seeded Oracle Workflow processes. Review the seeded flows, activities and notifications to determine if the seeded data can meet your business needs. To successfully enter an RMA in OM, you can use the Generic - Order Flow Return with Approval and Line Flow - Return for Credit only.
With services, OM will use only the seeded "Return for Credit Only" workflow for returning service items when product items are returned
Transaction Types
Both order and line transaction types need to be setup in order to process an RMA.Credit order types have an order type category Return. An order with a Mixed order type category can contain both standard and return lines. Line level workflow processes are assigned based on the order type, line type, and item type combination. When you setup a return order type or mixed order type, you have the option to set a default return line type, so that the user doesn't have to manually choose the line type unless they want it to be different.
Master Items
You can create a return line only if an item is Returnable. Therefore, a standard, finished good item should be defined in Oracle Inventory with appropriately set attributes. The best way to create your items is to copy them from the Finished Good seeded template and set additional attributes as needed in the Master Item window
Return Reason Codes
You can set up your own reason codes in the Receivables QuickCodes window. Navigate to the Order Management responsibility and select the menu: Setup > Quickcodes > Receivables. The Oracle Receivables Lookup window will appear. Query the CREDIT_MEMO_REASON code from the query manager (Flashlight icon). View the existing codes or add a new code. These codes appear in the Return Reason list of values.

Freight and Special Charges for Returns
When setting up freight or special charges, you can specify if the charge is returnable, meaning the charge may be refunded. When you create a return line from an original order line, you should copy the refundable invoiced charges. You can also setup special charges to be applied specifically to returns, like restocking fees, return handling, damage etc. You can set this through an attribute called Refundable Flag (Include on Returns field) within the Pricing Modifier setup

Process Flow

There are two ways in which to create an RMA in Oracle:

• Create a New Return which will create the RMA from scratch.
• Copy an original order into an order with an RMA Order Type.

Normal business is delivered with four different RMA Order Types, each with a different Order Cycle:
RMA with Credit is used when the customer is going to be returning a physical product and will also be receiving a credit in Accounts Receivable as a result of the return.These types of returns are for:

• Defective Product
• Customer does not like the product
• Product does not meet the customer’s expectations

RMA no Credit is used when the customer is receiving authorization to return the product but will not be receiving a credit as a result of the return.

• These returns would be for:
• Evaluation Orders
• Samples
• Other orders where the customer was not originally charged for the product.

RMA Credit Only is used when the customer will be receiving a credit, but the physical return of the product is not required.

• These credits are generally used by software companies when the customer destroys the CD or disk and erases the software from their machine, but no physical thing to return.

RMA with Credit and Approval is used in the same manner as an RMA with Credit but this order cycle includes an approval process that requires someone to approve the RMA before it is booked. In order for an order/return or order/return line approval workflow to work correctly the profile option OM: Notification Approver must have a value.
This section will guide you through a basic flow for a Return for Credit with Receipt, from entry to generating a credit memo, including:
1. Create an RMA having a single line whose originating transaction is unknown
while entering an RMA in the Returns tab, the user will need to enter the Line Type as a return (i.e. Return for Credit with Receipt of Goods) and enter a Return Reason. A Return Reason is required to be entered (i.e. Product Discontinued). Since we did not reference a sales order, we are entering a single line RMA where the originating transaction is unknown.
If you receive the returns partially, and if the Calculate Price Flag is set to Y (Calculate Price) or P (Partial Price), then freight charges get applied automatically on the partially received lines. However if the Calculate Price Flag is set to N, then
the freight charges do not get applied on the partially received lines.

2. Book the RMA
3. Receive the RMA using the Receipts window of Oracle Purchasing
4. Check the on-hand quantity of the item in Inventory to verify that correct quantity was received

5. Fulfill RMA line
The fulfillment activity acts as a synchronization point for all lines on the order that are in a fulfillment set. The lines in the fulfillment set will wait at the fulfillment activity until all the lines in the set have reached the activity. Lines that are not in a fulfillment set simply pass through the activity automatically. The user will not have to perform anything during this step. The eligible lines will automatically be put into a fulfillment set.

6. Generate a Credit MemoThe Workflow process of the return line(s) will be on the Invoice Interface activity, once the Fulfillment activity completes. The invoice interface activity places the information from the return line into the Receivables Interface tables. Once the information is written to the tables, the invoice interface activity is complete, and the line proceeds to the close line activity.
However, note that the credit memo is not actually generated until the Autoinvoice program in Receivables has been run. The credit memo will then be viewable in the Sales Orders window.
To run the Autoinvoice program, the user needs to change responsibilities to Receivables and navigate to the Interfaces window. Select the Autoinvoice Master program and run the program for your RMA # and specify the invoice source as the one associated with the line type of the RMA line. The Autoinvoice Master program will generate the Autoinvoice Import program which 5-50 Oracle Order Management Implementation Manual generates the credit memo. These programs can be setup to run automatically in the background. Just set the programs as 'Deferred.'
7. View the Credit Memo in Order Management
View the credit memo in Order Management. To view the credit memo in Order Management, the user need to change responsibilities to Order Management > Orders, Returns > Order Organizer window. Query your RMA # in the Order
Organizer. Once the RMA is queried, open the RMA order, click Actions and choose Additional Order Information. Once the Additional Order Information window has opened, click on the Receivables tab to view the credit memo. This window will show your the credit memo number and amount.
8. Check the Shipped and Fulfilled quantity on the RMA line
From the above step, navigate in the Sales Orders window to the Line Items tab for the RMA. Scroll to view the Shipped Quantity field. To access the Fulfilled Quantity field, the user needs to use the folder technology to add the field to the sales orders window. To add the field, click on the Warehouse field in the Shipping Tab of the Line Items window. Next, select the Folder menu at the top of the window, select Show Field and choose the Quantity Fulfilled field from the list. The field will populate in the window. The Shipped Quantity means the received quantity for return lines and the Fulfilled Quantity means the delivered quantity for the return lines.