Load Table and its Related Components from one Instance to Another

Register a concurrent program using the below package and run the program in the source instance. Program output will generate a script that can be used to migrate the table and related components to the target instance.

Program will have two input parameters, Custom Schema Name where the table has been created and the table name.

CREATE OR REPLACE PACKAGE xx_xdfgen_pkg AUTHID CURRENT_USER

AS

REM *********************************************************

REM                                                                                                                                          

REM   PACKAGE SPECIFICATION: XX_XDFGEN_PKG.pls                                            

REM                                                                                                                                       

REM   PURPOSE: Generate the script to create the below objects. 

REM                1.  Table and its constraints

REM                2.  Index  

REM                3.  Synonym and Editionable View

REM                4.  Grant 

REM                5.  Register table and its columns 

REM                              

REM   MODIFICATION HISTORY: 

REM   *********************          

REM    DATE           VER         CREATED BY           DESCRIPTION

REM   ***********     *****        ************         *****************

REM   15-FEB-2022      1.0         Gaurav Kumar         Initial    

REM *********************************************************

  PROCEDURE generate_table(

      errbuf OUT VARCHAR2,

      retcode OUT NUMBER,

      p_schema_name IN VARCHAR2,

      p_table_name  IN VARCHAR2);

END xx_xdfgen_pkg;

/

CREATE OR REPLACE PACKAGE BODY xx_xdfgen_pkg

AS

REM *********************************************************

REM                                                                                                                                          

REM   PACKAGE BODY: XX_XDFGEN_PKG.plb                                            

REM                                                                                                                                       

REM   PURPOSE: Generate the script to create the below objects. 

REM                1.  Table and its constraints

REM                2.  Index  

REM                3.  Synonym and Editionable View

REM                4.  Grant 

REM                5.  Register table and its columns 

REM                              

REM   MODIFICATION HISTORY: 

REM   *********************          

REM    DATE           VER         CREATED BY           DESCRIPTION

REM   ***********     *****        ************         *****************

REM   15-FEB-2022      1.0         Gaurav Kumar         Initial    

REM *********************************************************

PROCEDURE debug_log(

    p_type NUMBER,

    p_msg  VARCHAR2)

IS

BEGIN

  IF p_type = 1 THEN

    dbms_output.put_line(p_msg);

    fnd_file.put_line(fnd_file.output,p_msg);

  ELSE

    dbms_output.put_line(p_msg);

    fnd_file.put_line(fnd_file.output,p_msg);

  END IF;

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Unhandled exception from DEBUG_LOG procedure - '||SQLERRM);

  fnd_file.put_line(fnd_file.output,'Unhandled exception from DEBUG_LOG procedure - '||SQLERRM);

END debug_log;

PROCEDURE generate_table(

    errbuf OUT VARCHAR2,

    retcode OUT NUMBER,

    p_schema_name IN VARCHAR2,

    p_table_name  IN VARCHAR2)

IS

  l_query        VARCHAR2(32767);

  l_table_script VARCHAR2(32767);

  CURSOR cur_indexes (p_script VARCHAR2)

  IS

    SELECT owner,

      index_name

    FROM dba_indexes

    WHERE table_name                   = p_table_name

    AND INSTR(p_script,index_name,1,1) = 0;

  CURSOR cur_non_apps_grants

  IS

    SELECT privilege,

      grantee

    FROM dba_tab_privs

    WHERE table_name = p_table_name

    AND grantee     <> 'APPS'

    ORDER BY grantee;

BEGIN

  debug_log(1,'/******************************************************');

  debug_log(1,' * ENTERED PARAMETERS:                                *');

  debug_log(1,' *     Schema Name: '||RPAD(p_schema_name,34) ||'*');

  debug_log(1,' *     Table Name:  '||RPAD(p_table_name,34) ||'*');

  debug_log(1,' ******************************************************/');

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE);

  debug_log(1,'');

  debug_log(1,'');

  debug_log(1,'-- Session Setup:');

  debug_log(1,'');

  debug_log(1,'WHENEVER SQLERROR CONTINUE;');

  debug_log(1,'');

  debug_log(1,'');

  debug_log(1,'-- Table Creation Script:');

  l_table_script := DBMS_METADATA.GET_DDL('TABLE',p_table_name,p_schema_name);

  debug_log(1,l_table_script);

  debug_log(1,'');

  debug_log(1,'');

  debug_log(1,'-- Index Creation Script:');

  FOR i IN cur_indexes (l_table_script)

  LOOP

    debug_log(1,DBMS_METADATA.GET_DDL('INDEX',i.index_name,i.owner));

    debug_log(1,'');

  END LOOP;

  debug_log(1,'');

  debug_log(1,'');

  debug_log(1,'-- Synonym and Editionable View Creation Script:');

  debug_log(1,'');

  debug_log(1,'EXEC AD_ZD_TABLE.UPGRADE ('''||p_schema_name||''', '''||p_table_name||''');');

  debug_log(1,'');

  debug_log(1,'');

  debug_log(1,'-- Table and its columns registeration Script:');

  l_query := '    

  DECLARE     

  lc_appl_short_name CONSTANT VARCHAR2(40) DEFAULT '''||p_schema_name||''';    

  lc_tab_name        CONSTANT VARCHAR2(32) DEFAULT '''||p_table_name||

    ''';    

  lc_tab_type        CONSTANT VARCHAR2(50) DEFAULT ''T'';    

  lc_next_extent     CONSTANT NUMBER DEFAULT 512;    

  lc_pct_free        CONSTANT NUMBER DEFAULT 10;    

  lc_pct_used        CONSTANT NUMBER DEFAULT 70;  

  

  BEGIN    

  -- Start Register Custom Table    

  -- Get the table details in cursor    

  FOR table_detail IN (SELECT table_name,                                

  tablespace_name,                                

  pct_free,                                

  pct_used,                                

  ini_trans,                                

  max_trans,                                

  initial_extent,                                

  next_extent                           

  FROM dba_tables                          

  WHERE table_name = lc_tab_name                        

  )    

  LOOP      

  -- Call the API to register table      

  ad_dd.register_table         

  (p_appl_short_name => lc_appl_short_name,          

  p_tab_name        => table_detail.table_name,          

  p_tab_type        => lc_tab_type,          

  p_next_extent     => NVL(table_detail.next_extent,lc_next_extent),          

  p_pct_free        => NVL(table_detail.pct_free,lc_pct_free),          

  p_pct_used        => NVL(table_detail.pct_used,lc_pct_used)         

  );    

  END LOOP; -- End Register Custom Table    

  

  -- Start Register Columns    

  -- Get the column details of the table in cursor    

  FOR table_columns IN (SELECT column_name,                                 

  column_id,                                 

  data_type,                                 

  data_length,                                 

  nullable                            

  FROM all_tab_columns                           

  WHERE table_name = lc_tab_name                          

  )    

  LOOP      

  -- Call the API to register column      

  ad_dd.register_column         

  (p_appl_short_name => lc_appl_short_name,          

  p_tab_name        => lc_tab_name,          

  p_col_name        => table_columns.column_name,          

  p_col_seq         => table_columns.column_id,          

  p_col_type        => table_columns.data_type,          

  p_col_width       => table_columns.data_length,          

  p_nullable        => table_columns.nullable,          

  p_translate       => ''N'',          

  p_precision       => NULL,          

  p_scale           => NULL         

  );    

  END LOOP; -- End Register Columns    

  

  -- Start Register Primary Key    

  -- Get the primary key detail of the table in cursor    

  FOR all_keys IN (SELECT constraint_name, table_name, constraint_type                       

  FROM all_constraints                      

  WHERE constraint_type = ''P''                        

  AND table_name = lc_tab_name                    

  )    

  LOOP      

  -- Call the API to register primary_key      

  ad_dd.register_primary_key           

  (p_appl_short_name => lc_appl_short_name,            

  p_key_name        => all_keys.constraint_name,            

  p_tab_name        => all_keys.table_name,            

  p_description     => ''Register primary key'',            

  p_key_type        => ''S'',            

  p_audit_flag      => ''N'',            

  p_enabled_flag    => ''Y''           

  );      

  -- Start Register Primary Key Column      

  -- Get the primary key column detial in cursor      

  FOR all_columns IN (SELECT column_name, position                            

  FROM dba_cons_columns                           

  WHERE table_name = all_keys.table_name                             

  AND constraint_name = all_keys.constraint_name                          

  )      

  LOOP        

  -- Call the API to register primary_key_column        

  ad_dd.register_primary_key_column            

  (p_appl_short_name => lc_appl_short_name,             

  p_key_name        => all_keys.constraint_name,             

  p_tab_name        => all_keys.table_name,             

  p_col_name        => all_columns.column_name,             

  p_col_sequence    => all_columns.position             

  );      

  END LOOP; -- End Register Primary Key Column    

  END LOOP; -- End Register Primary Key    

  COMMIT;    

  DBMS_OUTPUT.PUT_LINE (''SUCCESS'');  

  EXCEPTION    

  WHEN OTHERS THEN      

  DBMS_OUTPUT.PUT_LINE (SQLERRM);  

  END;';

  debug_log(1,l_query);

  debug_log(1,'/');

  debug_log(1,'');

  debug_log(1,'-- Grant Creation Script:');

  debug_log(1,'-- Execute these Grant scripts from XX Custom Schema:');

  debug_log(1,'');

  debug_log(1,'grant ALL on XX.'||p_table_name||' to APPS with grant option;');

  debug_log(1,'');

  debug_log(1,'grant ALL on XX.'||p_table_name||'# to APPS with grant option;');

  debug_log(1,'');

  debug_log(1,'/');

  debug_log(1,'');

  debug_log(1,'-- Execute these Grant scripts from APPS Schema:');

  debug_log(1,'');

  FOR k IN cur_non_apps_grants

  LOOP

    debug_log(1,'EXEC AD_ZD.GRANT_PRIVS ('''||k.privilege||''', '''||p_table_name||''', '''||k.grantee||''');');

    debug_log(1,'');

  END LOOP;

  debug_log(1,'/');

EXCEPTION

WHEN OTHERS THEN

  retcode := 2;

  debug_log (2, 'Unhandled exception from GENERATE_TABLE procedure - '||SQLERRM);

END generate_table;

END xx_xdfgen_pkg;

/

Capture Custom Debug Message in any Oracle Objects

 1. Create a table as below:

CREATE TABLE xx_debug_tbl 
   (SEQ_NUM        NUMBER, 
    DEBUG_MSG      VARCHAR2(500));

2. Create a procedure as below:

CREATE OR REPLACE PROCEDURE xx_debug_proc (p_msg VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO xx_debug_tbl
VALUES(FND_LOG_MESSAGES_S.nextval,
p_msg);
COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK;
  END;

3. Now, you can use this procedure in oracle objects as below and the debug get stored in xx_debug_tbl table.

xx_debug_proc('This is the custom debug');

Script to migrate Form Personalization from one environment to another

In this blog we will use a PL/SQL script to migrate Oracle form personalization from one environment  to another in place of using FNDLOAD ldt file. This script will append the new personalizations in migration environment in place of replacement.

When we UPLOAD form personalization using FNDLOAD from first environment to migration environment then in migration environment, it first delete the existing personalization and then add all those personalizations which are available in first environment. In this case there are changes to loose those personalization in migration environment which are new and does not exist in first environment.


Below are the steps you have to follow to migrate the personalizations.

1. Run the below script from SQL Developer or Toad. This script will ask few input parameters like personalized form name, function name and newly added personalization sequence number from and to. Also, before running this script change the location of spool, where you want to generate a file in your local system.

Script:

/***********************************************************************/
SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
DBMS_OUTPUT.ENABLE(1000000);
spool 'D:\Gaurav\personalization.sql'
DECLARE
  CURSOR rules_cur
  IS
    SELECT *
FROM fnd_form_custom_rules
WHERE rule_key IS NULL
    AND form_name = UPPER('&FORM_NAME')
AND function_name = UPPER('&FUNCTION_NAME')
AND sequence BETWEEN &SEQUENCE_FROM AND &SEQUENCE_TO
ORDER BY sequence;
  CURSOR actions_cur (p_rule_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_actions
WHERE rule_id = p_rule_id
ORDER BY sequence;
  CURSOR params_cur (p_action_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_params
WHERE action_id = p_action_id
ORDER BY sequence;
  CURSOR scopes_cur (p_rule_id NUMBER)
  IS
    SELECT *
FROM fnd_form_custom_scopes
WHERE rule_id = p_rule_id;

  l_count     NUMBER;

  TYPE t_rules_tab IS TABLE OF rules_cur%rowtype INDEX BY BINARY_INTEGER;
  l_rules_tab  t_rules_tab;
BEGIN
  OPEN rules_cur;
  FETCH rules_cur BULK COLLECT INTO l_rules_tab;
  CLOSE rules_cur;
  
  l_count := l_rules_tab.count;
  DBMS_OUTPUT.PUT_LINE('SET DEFINE OFF;');
  DBMS_OUTPUT.PUT_LINE('DECLARE
  l_seq   NUMBER;');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  FOR i IN  l_rules_tab.FIRST .. l_rules_tab.LAST LOOP
    IF (i = 1 ) THEN
DBMS_OUTPUT.PUT_LINE('  BEGIN
    SELECT NVL(MAX(first),1)
    INTO l_seq
    FROM (SELECT sequence first,
             lead(sequence) over(order by sequence) last
          FROM fnd_form_custom_rules
          WHERE rule_key IS NULL
          AND form_name = '''||l_rules_tab(i).form_name||'''
      AND  function_name = '''||l_rules_tab(i).function_name||''')
    WHERE first <> last
    AND ((last-first-1)-'||l_count||') >=0;
  EXCEPTION
  WHEN OTHERS THEN
l_seq := 1;
  END;');
   END IF;
    DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_rules(
      id,
      function_name,
      description,
      trigger_event,
      trigger_object,
      condition,
      sequence,
      created_by, 
      creation_date,
      last_updated_by, 
      last_update_date, 
      last_update_login,
      enabled,
      fire_in_enter_query,
      rule_key,
      form_name,
      rule_type)
  VALUES(
      FND_FORM_CUSTOM_RULES_S.NextVal,
      '''||l_rules_tab(i).function_name||''',
      '''||replace(l_rules_tab(i).description,'''','''''')||''',
      '''||l_rules_tab(i).trigger_event||''',
      '''||l_rules_tab(i).trigger_object||''',
      '''||replace(l_rules_tab(i).Condition,'''','''''')||''',
      l_seq+'||i||',
      1,
      SYSDATE,
      1,
      SYSDATE,
      -1,
      '''||l_rules_tab(i).enabled||''',
      '''||l_rules_tab(i).fire_in_enter_query||''',
      '''||l_rules_tab(i).rule_key||''',
      '''||l_rules_tab(i).form_name||''',
      '''||l_rules_tab(i).rule_type||''');');
FOR actions_rec IN actions_cur (l_rules_tab(i).id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_actions(
  sequence,
      property_value,
      argument_type,
      created_by, 
  creation_date,
      last_updated_by, 
  last_update_date, 
  last_update_login,
      target_object,
      action_type,
      enabled,
      object_type,
      folder_prompt_block,
      message_type,
      message_text,
      summary,
      builtin_type,
      builtin_arguments,
      language,
      rule_id,
      property_name,
      menu_entry,
      menu_label,
      menu_seperator,
      menu_enabled_in,
      menu_action,
      menu_argument_long,
      menu_argument_short,
      action_id,
      request_application_id)
  VALUES(
  '||actions_rec.sequence||',
  '''||REPLACE(actions_rec.property_value,'''','''''')||''',
  '''||actions_rec.argument_type||''',
  1,
      SYSDATE,
      1,
      SYSDATE,
      -1,
  '''||actions_rec.target_object||''',
  '''||actions_rec.action_type||''',
  '''||actions_rec.enabled||''',
  '''||actions_rec.object_type||''',
  '''||REPLACE(actions_rec.folder_prompt_block,'''','''''')||''',
  '''||actions_rec.message_type||''',
  '''||REPLACE(actions_rec.message_text,'''','''''')||''',
  '''||REPLACE(actions_rec.summary,'''','''''')||''',
  '''||actions_rec.builtin_type||''',
  '''||REPLACE(actions_rec.builtin_arguments,'''','''''')||''',
  '''||actions_rec.language||''',
  FND_FORM_CUSTOM_RULES_S.CurrVal,
  '''||actions_rec.property_name||''',
  '''||actions_rec.menu_entry||''',
  '''||REPLACE(actions_rec.menu_label,'''','''''')||''',
  '''||actions_rec.menu_seperator||''',
  '''||actions_rec.menu_enabled_in||''',
  '''||actions_rec.menu_action||''',
  '''||REPLACE(actions_rec.menu_argument_long,'''','''''')||''',
  '''||REPLACE(actions_rec.menu_argument_short,'''','''''')||''',
  FND_FORM_CUSTOM_ACTIONS_S.NextVal,
  '''||actions_rec.request_application_id||'''
  );');
FOR params_rec IN params_cur (actions_rec.action_id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_params(
      action_id,
      name,
      value,
      sequence,
      default_type,
      inherit,
      last_updated_by, 
  last_update_date,
      created_by, 
  creation_date, 
  last_update_login)
  VALUES(
  FND_FORM_CUSTOM_ACTIONS_S.CurrVal,
  '''||REPLACE(params_rec.name,'''','''''')||''',
  '''||REPLACE(params_rec.value,'''','''''')||''',
  '''||params_rec.sequence||''',
  '''||params_rec.default_type||''',
  '''||params_rec.inherit||''',
  1,
      SYSDATE,
      1,
      SYSDATE,
      -1);');
END LOOP;
END LOOP;
FOR scopes_rec IN scopes_cur(l_rules_tab(i).id) LOOP
DBMS_OUTPUT.PUT_LINE('  INSERT INTO fnd_form_custom_scopes(
       rule_id,
       level_id,
       level_value,
       level_value_application_id,
       last_updated_by, 
   last_update_date,
       created_by, 
   creation_date, 
   last_update_login)
  VALUES(
   FND_FORM_CUSTOM_RULES_S.CurrVal,
   '||scopes_rec.level_id||',
   '''||REPLACE(scopes_rec.level_value,'''','''''')||''',
   '''||scopes_rec.level_value_application_id||''',
   1,
      SYSDATE,
      1,
      SYSDATE,
      -1
);');
END LOOP;
  END LOOP;
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(''Error while uploading the personalization. Error: ''||SQLERRM);
END;
/
COMMIT;
EXIT;
');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while downloading the personalization. Error: '||SQLERRM);
END;
/
spool off;

/

/***********************************************************************/

2. After running this script, you will get a file at spool location with the name "personalization.sql".

3. Run this file (personalization.sql script) in migration environment.

4. Validate the personalization in migration environment.

Find all Attributes of a OAF Page

 package oracle.apps.iby.fundcapture.setup.components.webui;
 import oracle.apps.fnd.framework.webui.OAPageContext;
 import oracle.apps.fnd.framework.OAApplicationModule;
 import java.util.Enumeration;
 import oracle.jdbc.OracleCallableStatement;
 import oracle.apps.fnd.framework.OAException;
 import oracle.apps.fnd.framework.server.OADBTransaction;

  public class xx_CreateExtBankAcctCO extends CreateExtBankAcctCO {
    public xx_CreateExtBankAcctCO() {
    }
public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
        super.processRequest(pageContext, webBean);
     }
     public void processFormRequest(OAPageContext pageContext,
                                   OAWebBean webBean)
{
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule oaapplicationmodule1 = pageContext.getApplicationModule(webBean);  
//  Main code Start from here
Enumeration enums = pageContext.getParameterNames();
            while(enums.hasMoreElements()){
               String paramName = enums.nextElement().toString();
               String s10 = "Param name: " +paramName+ "  <=>  Value: "+pageContext.getParameter(paramName);
   OADBTransaction txn = oaapplicationmodule1.getOADBTransaction();
               OracleCallableStatement  cs1 = (OracleCallableStatement)txn.createCallableStatement("begin xx_debug_proc(:1); end;",1);
               try
         {
                   cs1.setString(1, s10);
                   cs1.execute();
                   cs1.close();
                 }
               catch (Exception e1)
                 {
                   throw OAException.wrapperException(e1);
                 }
        // End here.
}
}
  }

Procedure XX_DEBUG_PROC:
   
     CREATE OR REPLACE PROCEDURE xx_debug_proc (p_msg VARCHAR2)
     IS
       PRAGMA AUTONOMOUS_TRANSACTION;
       BEGIN
          INSERT INTO xx_debug_tbl
  VALUES(FND_LOG_MESSAGES_S.nextval, p_msg);
  COMMIT;
       EXCEPTION
       WHEN OTHERS THEN
        ROLLBACK;
       END;

Table XX_DEBUG_TBL:
       CREATE TABLE xx_debug_tbl (SEQ_NUM NUMBER, DEBUG_MSG VARCHAR2(250));
  

"Exception Name: java.lang.ArrayIndexOutOfBoundsException - 1" While uploading the GL Daily Rates Integrator

Symptoms:
When trying to upload GL Daily Rates Integrator, the following error occurs.

Error:
Exception Name: java.lang.ArrayIndexOutOfBoundsException - 1

Log File Bookmark: 786581

Steps:
1. Login the application.
2. Navigate to R) General Ledger Superuser > Setup > Currencies > Currency Rates Manager >Daily Rates.
3. Click on Create in Spreadsheet button.

Cause:
Excel 2003 is not available in WEB ADI viewer LOV.

Solution:
1. Login the application.
2. Navigate to R) System Administrator > Application > Function
3. Search the function with "GL_CRM_DR_WEBADI_CREATE" (Daily Rates Web-ADI Spreadsheet).
4. Go to Web HTML tab and change the viewer value from EXCEL2003 to EXCEL2007.
5. Save the function and re-test the issue.

Previous HTML Call Value:
BneApplicationService?bne:page=BneCreateDoc&bne:reporting=N&bne:integrator=SQLGL:GL_DAILY_RATES&bne:layout=SQLGL:GL_DAILY_RATES_ORIGINAL&bne:content=GL_DR_NONE&bne:viewer=EXCEL2003&bne:noreview=anything

Changed HTML Call Value:
BneApplicationService?bne:page=BneCreateDoc&bne:reporting=N&bne:integrator=SQLGL:GL_DAILY_RATES&bne:layout=SQLGL:GL_DAILY_RATES_ORIGINAL&bne:content=GL_DR_NONE&bne:viewer=EXCEL2007&bne:noreview=anything

"Flexfield View Generator" Program code in PL/SQL

DECLARE
  TYPE global_column_rec_type IS RECORD(
           view_column_name               VARCHAR2(40),
           application_column_name        VARCHAR2(50),
          global_application_column_name VARCHAR2(50));
 TYPE t_global_column_tab IS TABLE OF  global_column_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE column_rec_type IS RECORD(
          view_column_name   VARCHAR2(40));
 TYPE t_column_tab IS TABLE OF column_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE attribute_rec_type IS RECORD(
          application_column_name        VARCHAR2(50),
          descriptive_flex_context_code  VARCHAR2(30));
 TYPE t_attribute_tab IS TABLE OF attribute_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE context_rec_type IS RECORD(
          descriptive_flex_context_code VARCHAR2(30));
 TYPE t_context_tab IS TABLE OF context_rec_type
 INDEX BY BINARY_INTEGER;

 TYPE con_attr_rec_type IS RECORD(
          application_column_name        VARCHAR2(30));
 TYPE t_con_attr_tab IS TABLE OF con_attr_rec_type
 INDEX BY BINARY_INTEGER;

  p_application             VARCHAR2(3)  := 'ONT';
  p_flex_field_name    VARCHAR2(50) := 'OE_LINE_ATTRIBUTES';
  l_table_name             VARCHAR2(30);
  l_view_name             VARCHAR2(30);
  l_context_name         VARCHAR2(30);
  l_context_prompt      VARCHAR2(50);
  l_global_column_tab t_global_column_tab;
  l_column_tab             t_column_tab;
  l_attribute_tab            t_attribute_tab;
  l_context_tab              t_context_tab;
  l_con_attr_tab             t_con_attr_tab;
  l_view_header            CLOB;
  l_view_line                 CLOB;
  l_view                         CLOB;
  l_attribute                   CLOB;
  l_context                    CLOB;

  CURSOR cur_global_column
  IS
    SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name,
           DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
                                 'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 fcu.application_column_name) application_column_name,
fcu.application_column_name global_application_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_flex_value_sets fs,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name      = fc.descriptive_flexfield_name
    AND fcu.flex_value_set_id               = fs.flex_value_set_id(+)
    AND fcu.descriptive_flexfield_name      = p_flex_field_name
    AND fcu.application_id                  = fa.application_id
    AND fcu.descriptive_flex_context_code   = 'Global Data Elements'
    AND fa.application_short_name           = p_application
    AND fc.enabled_flag                     = 'Y'
    AND fcu.enabled_flag                    = 'Y'
ORDER BY fcu.application_column_name;

  CURSOR cur_column
  IS
    SELECT DISTINCT UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_')) view_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name      = fc.descriptive_flexfield_name
    AND fcu.descriptive_flexfield_name      = p_flex_field_name
    AND fcu.application_id                  = fa.application_id
    AND fa.application_short_name           = p_application
AND fcu.descriptive_flex_context_code   <> 'Global Data Elements'
    AND fc.enabled_flag                     = 'Y'
    AND fcu.enabled_flag                    = 'Y'
    ORDER BY UPPER(regexp_replace(end_user_column_name,'[^[:alnum:]""]','_'));

  CURSOR cur_attribute (p_end_user_column_name IN VARCHAR2)
  IS
    SELECT DECODE(fs.format_type,'N','FND_NUMBER.CANONICAL_TO_NUMBER('||fcu.application_column_name||')',
                                 'X','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'Y','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 'I','TO_DATE('||fcu.application_column_name||', ''YYYY/MM/DD HH24:MI:SS'')',
                                 fcu.application_column_name) application_column_name,
      fcu.descriptive_flex_context_code
    FROM fnd_descr_flex_column_usages fcu,
      fnd_descr_flex_contexts fc,
      fnd_flex_value_sets fs,
      fnd_application fa
    WHERE fcu.descriptive_flex_context_code                                  = fc.descriptive_flex_context_code
    AND fcu.descriptive_flexfield_name                                       = fc.descriptive_flexfield_name
    AND fcu.flex_value_set_id                                                = fs.flex_value_set_id(+)
    AND fcu.descriptive_flexfield_name                                       = p_flex_field_name
    AND fcu.application_id                                                   = fa.application_id
    AND fa.application_short_name                                            = p_application
    AND fcu.enabled_flag                                                     = 'Y'
    AND fc.enabled_flag                                                      = 'Y'
    AND UPPER(regexp_replace(fcu.end_user_column_name,'[^[:alnum:]""]','_')) = p_end_user_column_name
    ORDER BY fcu.application_column_name,
      fcu.descriptive_flex_context_code;
   
  CURSOR cur_context
  IS
    SELECT descriptive_flex_context_code
    FROM fnd_descr_flex_contexts fc,
      fnd_application fa
    WHERE descriptive_flexfield_name    = p_flex_field_name
    AND fc.application_id               = fa.application_id
    AND fa.application_short_name       = p_application
AND descriptive_flex_context_code   <> 'Global Data Elements'
    AND enabled_flag                    = 'Y'
    ORDER BY descriptive_flex_context_code;
 
  CURSOR cur_con_attr (p_dff_context_code IN VARCHAR2)
  IS
    SELECT application_column_name
    FROM fnd_descr_flex_column_usages fcu,
      fnd_application fa
    WHERE descriptive_flexfield_name  = p_flex_field_name
    AND fcu.application_id            = fa.application_id
    AND fa.application_short_name     = p_application
    AND enabled_flag                  = 'Y'
    AND descriptive_flex_context_code = p_dff_context_code
    ORDER BY to_number(SUBSTR(application_column_name,10));

  CURSOR cur_table_name
  IS
    SELECT fd.application_table_name,
      fd.concatenated_segs_view_name,
      fd.context_column_name,
      UPPER(regexp_replace(fdt.form_context_prompt,'[^[:alnum:]""]','_')) form_context_prompt
    FROM fnd_descriptive_flexs fd,
      FND_DESCRIPTIVE_FLEXS_TL fdt,
      fnd_application fa
    WHERE fd.descriptive_flexfield_name = fdt.descriptive_flexfield_name
    AND fd.application_id               = fdt.application_id
    AND fdt.language                    = userenv('LANG')
    AND fd.descriptive_flexfield_name   = p_flex_field_name
    AND fd.application_id               = fa.application_id
    AND fa.application_short_name       = p_application;
BEGIN
  OPEN cur_table_name;
  FETCH cur_table_name
  INTO l_table_name,
    l_view_name,
    l_context_name,
    l_context_prompt;
  CLOSE cur_table_name;

  OPEN cur_global_column;
  FETCH cur_global_column BULK COLLECT INTO l_global_column_tab;
  CLOSE cur_global_column;

  OPEN cur_column;
  FETCH cur_column BULK COLLECT INTO l_column_tab;
  CLOSE cur_column;

  OPEN cur_context;
  FETCH cur_context BULK COLLECT INTO l_context_tab;
  CLOSE cur_context;

  l_view_header         := 'CREATE OR REPLACE FORCE VIEW '||l_view_name||' (ROW_ID, '||l_context_prompt;
  l_view_line           := 'SELECT ROWID, '||l_context_name||', ';
  IF (l_global_column_tab.COUNT > 0 )THEN
    FOR m IN l_global_column_tab.FIRST .. l_global_column_tab.LAST
LOOP
  l_view_header := l_view_header||', '||l_global_column_tab(m).view_column_name;
  l_view_line   := l_view_line||l_global_column_tab(m).application_column_name||', ';
  l_context     := l_context || l_global_column_tab(m).global_application_column_name ||'|| ''.'' ||';
END LOOP;
  END IF;
  l_context := l_context||'(DECODE ('||l_context_name||', ';
  IF (l_column_tab.COUNT > 0) THEN
    FOR i IN l_column_tab.FIRST .. l_column_tab.LAST
    LOOP
      l_view_header := l_view_header||', '||l_column_tab(i).view_column_name;
      l_view_line   := l_view_line||'(DECODE ('||l_context_name||', ';
   
      OPEN cur_attribute(l_column_tab(i).view_column_name);
      FETCH cur_attribute BULK COLLECT INTO l_attribute_tab;
      CLOSE cur_attribute;
   
      IF (l_attribute_tab.COUNT > 0) THEN
        FOR j IN l_attribute_tab.FIRST .. l_attribute_tab.LAST
        LOOP
          l_view_line := l_view_line||''''|| l_attribute_tab(j).descriptive_flex_context_code ||''''||', '|| l_attribute_tab(j).application_column_name||','||CHR(10);
        END LOOP;
      END IF;
      l_view_line := l_view_line||'NULL )),'||CHR(10);
    END LOOP;
  END IF;
  IF (l_context_tab.COUNT > 0) THEN
    FOR k IN l_context_tab.FIRST .. l_context_tab.LAST
    LOOP
      OPEN cur_con_attr(l_context_tab(k).descriptive_flex_context_code);
      FETCH cur_con_attr BULK COLLECT INTO l_con_attr_tab;
      CLOSE cur_con_attr;
      IF (l_con_attr_tab.COUNT > 0) THEN
        FOR l IN l_con_attr_tab.FIRST .. l_con_attr_tab.LAST
        LOOP
          l_attribute := l_attribute || '|| ''.'' ||' ||l_con_attr_tab(l).application_column_name;
        END LOOP;
      END IF;
      l_context   := l_context||''''||l_context_tab(k).descriptive_flex_context_code||''''||', '||l_context_name||''||l_attribute||','||CHR(10);
      l_attribute := NULL;
    END LOOP;
  END IF;
  l_context := l_context||'NULL ))';
  l_view    := l_view_header||', CONCATENATED_SEGMENTS ) AS'||CHR(10)||l_view_line||l_context||CHR(10)|| 'FROM '||l_table_name;
  BEGIN
    EXECUTE IMMEDIATE l_view;
  EXCEPTION
  WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.log,'Error while compiling the view: '||SQLERRM);
    retcode := 2;
  END;
EXCEPTION
WHEN OTHERS THEN
  errbuf  := SQLERRM;
  retcode := 2;
END;

Script to create Service Contract with Line and Sub Line

DECLARE
  l_k_header_rec oks_contracts_pub.header_rec_type;
  l_header_contacts_tbl oks_contracts_pub.contact_tbl;
  l_header_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_header_articles_tbl oks_contracts_pub.obj_articles_tbl;
  l_k_line_rec oks_contracts_pub.line_rec_type;
  l_line_contacts_tbl oks_contracts_pub.contact_tbl;
  l_line_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_k_support_rec oks_contracts_pub.line_rec_type;
  l_support_contacts_tbl oks_contracts_pub.contact_tbl;
  l_support_sales_crd_tbl oks_contracts_pub.salescredit_tbl;
  l_k_covd_rec oks_contracts_pub.covered_level_rec_type;
  l_price_attribs_in oks_contracts_pub.pricing_attributes_type;
  l_strm_level_tbl oks_bill_sch.streamlvl_tbl;
  l_merge_rule        VARCHAR2(50);
  l_usage_instantiate VARCHAR2(50);
  l_ib_creation       VARCHAR2(1);
  l_billing_sch_type  VARCHAR2(50);
  l_chrid             NUMBER;
  l_service_line_id   NUMBER;
  l_cp_line_id        NUMBER;
  l_return_status     VARCHAR2(1);
  l_msg_count         NUMBER;
  l_msg_data          VARCHAR2(2000);
  l_sts_code          VARCHAR2(10);
  l_scs_code          VARCHAR2(10) := 'WARRANTY';
  l_tax_status_flag   VARCHAR2(1)  := 'S';
  l_merge_type        VARCHAR2(3)  := 'NEW';
  l_renewal_type      VARCHAR2(3)  := 'DNR';
  l_line_number       VARCHAR2(1)  := '1';
  l_line_type         VARCHAR2(1)  := 'W';
  l_negotiated_amount NUMBER       := 0;
  l_net_amount        NUMBER       := 0;
  l_sub_line_number   VARCHAR2(5)  := '1.1';
  l_tmp_cle_id        NUMBER;
  l_user_id           NUMBER := 0; -- SYSADMIN
  l_resp_id           NUMBER;
  l_resp_appl_id      NUMBER;
Type header_rec_type
IS
  Record
  (
    start_date           DATE,
    end_date             DATE,
    authoring_org_id     NUMBER,
    short_description    VARCHAR2(1995),
    party_id             NUMBER,
    bill_to_id           NUMBER,
    ship_to_id           NUMBER,
    price_list_id        NUMBER,
    cust_po_number       VARCHAR2(240),
    agreement_id         NUMBER,
    currency             VARCHAR2(15),
    accounting_rule_type NUMBER,
    invoice_rule_type    NUMBER,
    order_hdr_id         NUMBER,
    order_number         NUMBER,
    payment_term_id      NUMBER,
    cvn_type             VARCHAR2(25),
    cvn_rate             NUMBER,
    cvn_date             DATE,
    Organization_id      NUMBER,
    contact_id           NUMBER,
    cust_account         NUMBER);
TYPE header_tbl_type
IS
  TABLE OF header_rec_type INDEX BY BINARY_INTEGER;
  l_header_tbl header_tbl_type;
Type line_rec_type
IS
  Record
  (
    order_line_id NUMBER,
    start_date    DATE,
    end_date      DATE,
    srv_id        NUMBER,
    quantity      NUMBER,
    uom_code      VARCHAR2(25));
TYPE line_tbl_type
IS
  TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
  l_line_tbl line_tbl_type;
Type subline_rec_type
IS
  Record
  (
    customer_product_id NUMBER,
    product_desc        VARCHAR2(440));
TYPE subline_tbl_type
IS
  TABLE OF subline_rec_type INDEX BY BINARY_INTEGER;
  l_subline_tbl subline_tbl_type;
  CURSOR header_cur
  IS
    SELECT
      /*+ use_nl index (oe_order_headers_all, RESM_ORDER_HEADERS_N2) */
      MIN(oola.actual_shipment_date + NVL(msib.service_starting_delay,0))start_date,
      MAX(DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
      ooha.org_id authoring_org_id,
      'CUSTOMER : '
      ||hp.party_name
      ||' Warranty/Extended Warranty Contract' short_description,
      hp.party_id party_id,
      ooha.invoice_to_org_id bill_to_id,
      ooha.ship_to_org_id ship_to_id,
      ooha.price_list_id,
      ooha.cust_po_number,
      ooha.agreement_id,
      ooha.transactional_curr_code currency,
      ooha.accounting_rule_id accounting_rule_type,
      ooha.invoicing_rule_id invoice_rule_type ,
      ooha.header_id order_hdr_id,
      ooha.order_number,
      ooha.payment_term_id,
      ooha.conversion_type_code cvn_type,
      ooha.conversion_rate cvn_rate,
      ooha.conversion_rate_date cvn_date,
      ooha.ship_from_org_id organization_id,
      ooha.salesrep_id contact_id,
      hca.cust_account_id cust_account
    FROM mtl_system_items_b msi,
      bom_structures_b bbm,
      bom_components_b bic,
      oe_order_lines_all oola,
      mtl_system_items_b msib,
      csi_item_instances cii,
      oe_order_headers_all ooha,
      oe_transaction_types_all ott,
      hz_cust_accounts hca,
      hz_parties hp
    WHERE msi.vendor_warranty_flag = 'Y'
    AND msi.inventory_item_id      = bic.component_item_id
    AND msi.organization_id        = bbm.organization_id
    AND bbm.bill_sequence_id       = bic.bill_sequence_id
    AND bic.disable_date          IS NULL
    AND bbm.assembly_item_id       = oola.inventory_item_id
    AND bbm.organization_id        = oola.ship_from_org_id
    AND oola.inventory_item_id     = msib.inventory_item_id
    AND oola.ship_from_org_id      = msib.organization_id
    AND oola.line_id               = cii.last_oe_order_line_id
    AND cii.instance_status_id    <> 1
    AND oola.header_id             = ooha.header_id
    AND ooha.order_type_id         = ott.transaction_type_id
    AND ooha.org_id                = ott.org_id
    AND ott.order_category_code   <> 'RETURN'
    AND ooha.order_source_id      <> 7
    AND ooha.sold_to_org_id        = hca.cust_account_id
    AND hca.party_id               = hp.party_id
    AND ooha.order_number          = '606104'
    AND NOT EXISTS
      (SELECT 1
      FROM okc_k_items
      WHERE jtot_object1_code    = 'OKX_CUSTPROD'
      AND to_number(object1_id1) = cii.instance_id
      )
  AND NOT EXISTS
    (SELECT 1 FROM csi_txn_errors ctr WHERE ctr.instance_id = cii.instance_id
    )
  GROUP BY ooha.org_id,
    hp.party_name,
    hp.party_id,
    ooha.invoice_to_org_id,
    ooha.ship_to_org_id,
    ooha.price_list_id,
    ooha.cust_po_number,
    ooha.agreement_id,
    ooha.transactional_curr_code,
    ooha.accounting_rule_id,
    ooha.invoicing_rule_id ,
    ooha.header_id,
    ooha.order_number,
    ooha.payment_term_id,
    ooha.conversion_type_code,
    ooha.conversion_rate,
    ooha.conversion_rate_date,
    ooha.ship_from_org_id,
    ooha.salesrep_id,
    hca.cust_account_id;
  CURSOR line_cur(p_header_id NUMBER)
  IS
    SELECT oola.line_id order_line_id,
      (oola.actual_shipment_date+ NVL(msib.service_starting_delay,0))start_date,
      (DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
      msi.inventory_item_id srv_id,
      bic.Component_Quantity quantity,
      msi.primary_uom_code uom_code
    FROM mtl_system_items_b msi,
      bom_structures_b bbm,
      bom_components_b bic,
      oe_order_lines_all oola,
      mtl_system_items_b msib
    WHERE msi.vendor_warranty_flag   = 'Y'
    AND msi.inventory_item_id        = bic.component_item_id
    AND msi.organization_id          = bbm.organization_id
    AND bbm.bill_sequence_id         = bic.bill_sequence_id
    AND bic.disable_date            IS NULL
    AND bbm.assembly_item_id         = oola.inventory_item_id
    AND bbm.organization_id          = oola.ship_from_org_id
    AND oola.inventory_item_id       = msib.inventory_item_id
    AND oola.ship_from_org_id        = msib.organization_id
    AND msib.comms_nl_trackable_flag = 'Y'
    AND oola.header_id               = p_header_id;
  CURSOR subline_cur(p_line_id NUMBER)
  IS
    SELECT cii.instance_id customer_product_id,
      msib.segment1
      ||';'
      ||cii.serial_number
      ||';'
      ||cii.instance_id product_desc
    FROM oe_order_lines_all oola,
      csi_item_instances cii,
      bom_structures_b bbm,
      bom_components_b bic,
      mtl_system_items_b msib
    WHERE oola.line_id            = cii.last_oe_order_line_id
    AND oola.inventory_item_id    = bbm.assembly_item_id
    AND oola.ship_from_org_id     = bbm.organization_id
    AND bbm.bill_sequence_id      = bic.bill_sequence_id
    AND bic.disable_date         IS NULL
    AND msib.vendor_warranty_flag = 'Y'
    AND msib.inventory_item_id    = bic.component_item_id
    AND msib.organization_id      = bbm.organization_id
    AND line_id                   = p_line_id;
  CURSOR coverage_cur (p_item_id NUMBER, p_org_id NUMBER)
  IS
    SELECT coverage_template_id
    FROM okx_system_items_v
    WHERE id1           = p_item_id
    AND organization_id = p_org_id;
  CURSOR resp_cur
  IS
    SELECT responsibility_id,
      application_id
    FROM fnd_responsibility_vl
    WHERE responsibility_name = 'Service Contracts Manager (Corp) Config';
BEGIN
  OPEN resp_cur;
  FETCH resp_cur INTO l_resp_id,l_resp_appl_id;
  CLOSE resp_cur;
  fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
  okc_context.set_okc_org_context;
  okc_api.init_msg_list(OKC_API.G_TRUE);
  OPEN header_cur;
  FETCH header_cur BULK COLLECT INTO l_header_tbl;
  CLOSE header_cur;
  FOR i IN l_header_tbl.FIRST .. l_header_tbl.LAST
  LOOP
    IF (l_header_tbl(i).start_date  > SYSDATE) THEN
      l_sts_code                   := 'SIGNED';
    ELSIF (l_header_tbl(i).end_date < SYSDATE) THEN
      l_sts_code                   := 'EXPIRED';
    ELSE
      l_sts_code := 'ACTIVE';
    END IF;
    l_K_header_rec.start_date                    := l_header_tbl(i).start_date;
    l_K_header_rec.end_date                      := l_header_tbl(i).end_date;
    l_K_header_rec.sts_code                      := l_sts_code;
    l_K_header_rec.scs_code                      := l_scs_code;
    l_K_header_rec.authoring_org_id              := l_header_tbl(i).authoring_org_id;
    l_K_header_rec.short_description             := l_header_tbl(i).short_description;
    l_K_header_rec.party_id                      := l_header_tbl(i).party_id;
    l_K_header_rec.bill_to_id                    := l_header_tbl(i).bill_to_id;
    l_K_header_rec.ship_to_id                    := l_header_tbl(i).ship_to_id;
    l_K_header_rec.price_list_id                 := l_header_tbl(i).price_list_id;
    l_K_header_rec.cust_po_number                := l_header_tbl(i).cust_po_number;
    l_K_header_rec.agreement_id                  := l_header_tbl(i).agreement_id;
    l_K_header_rec.currency                      := l_header_tbl(i).currency;
    l_K_header_rec.accounting_rule_type          := l_header_tbl(i).accounting_rule_type;
    l_K_header_rec.invoice_rule_type             := l_header_tbl(i).invoice_rule_type;
    l_K_header_rec.order_hdr_id                  := l_header_tbl(i).order_hdr_id;
    l_K_header_rec.payment_term_id               := l_header_tbl(i).payment_term_id;
    l_K_header_rec.cvn_type                      := l_header_tbl(i).cvn_type;
    l_K_header_rec.cvn_rate                      := l_header_tbl(i).cvn_rate;
    l_K_header_rec.cvn_date                      := l_header_tbl(i).cvn_date;
    l_K_header_rec.tax_status_flag               := l_tax_status_flag;
    l_K_header_rec.merge_type                    := l_merge_type;
    l_K_header_rec.renewal_type                  := l_renewal_type;
    l_K_header_rec.organization_id               := l_header_tbl(i).organization_id;
    l_header_contacts_tbl(1).party_role          := 'VENDOR';
    l_header_contacts_tbl(1).contact_role        := 'SALESPERSON';
    l_header_contacts_tbl(1).contact_object_code := 'OKX_SALEPERS';
    l_header_contacts_tbl(1).contact_id          := l_header_tbl(i).contact_id;
    oks_contracts_pub.create_contract_header(p_K_header_rec                   => l_K_header_rec,
                                          p_header_contacts_tbl            => l_header_contacts_tbl,
                                             p_header_sales_crd_tbl           => l_header_sales_crd_tbl,
                                             p_header_articles_tbl            => l_header_articles_tbl,
                                             x_chrid                          => l_chrid,
                                             x_return_status                  => l_return_status,
                                             x_msg_count                      => l_msg_count,
                                             x_msg_data                       => l_msg_data);
    IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
      dbms_output.put_line ('Error while creating the Contract Header for order#: '||l_header_tbl(i).order_number);
      IF NVL(l_msg_count,0) > 0 THEN
        FOR p IN 1..l_msg_count
        LOOP
          dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
        END LOOP;
      END IF;
      ROLLBACK;
    ELSE
      OPEN line_cur(l_header_tbl(i).order_hdr_id);
      FETCH line_cur BULK COLLECT INTO l_line_tbl;
      CLOSE line_cur;
      FOR j IN l_line_tbl.FIRST .. l_line_tbl.LAST
      LOOP
     IF (l_line_tbl(j).start_date  > SYSDATE) THEN
           l_sts_code                   := 'SIGNED';
        ELSIF (l_line_tbl(j).end_date < SYSDATE) THEN
           l_sts_code                   := 'EXPIRED';
        ELSE
           l_sts_code := 'ACTIVE';
        END IF;
        l_k_line_rec.k_hdr_id             := l_chrid;
        l_k_line_rec.k_line_number        := j;
        l_k_line_rec.line_sts_code        := l_sts_code;
        l_k_line_rec.cust_account         := l_header_tbl(i).cust_account;
        l_k_line_rec.org_id               := l_header_tbl(i).authoring_org_id;
        l_k_line_rec.organization_id      := l_header_tbl(i).organization_id;
        l_k_line_rec.bill_to_id           := l_header_tbl(i).bill_to_id;
        l_k_line_rec.ship_to_id           := l_header_tbl(i).ship_to_id;
        l_k_line_rec.order_line_id        := l_line_tbl(j).order_line_id;
        l_k_line_rec.accounting_rule_type := l_header_tbl(i).accounting_rule_type;
        l_k_line_rec.invoicing_rule_type  := l_header_tbl(i).invoice_rule_type;
        l_k_line_rec.line_type            := l_line_type;
        l_k_line_rec.currency             := l_header_tbl(i).currency;
        l_k_line_rec.list_price           := l_header_tbl(i).price_list_id;
        l_k_line_rec.negotiated_amount    := l_negotiated_amount;
        l_k_line_rec.line_renewal_type    := l_renewal_type;
        l_k_line_rec.srv_id               := l_line_tbl(j).srv_id;
        l_k_line_rec.srv_sdt              := l_line_tbl(j).start_date;
        l_k_line_rec.srv_edt              := l_line_tbl(j).end_date;
        l_k_line_rec.customer_id          := l_header_tbl(i).party_id;
        l_k_line_rec.start_date_active    := l_line_tbl(j).start_date;
        l_k_line_rec.end_date_active      := l_line_tbl(j).end_date;
        l_k_line_rec.quantity             := l_line_tbl(j).quantity;
        l_k_line_rec.uom_code             := l_line_tbl(j).uom_code;
        l_k_line_rec.net_amount           := l_net_amount;
        l_k_line_rec.currency_code        := l_header_tbl(i).currency;
        oks_contracts_pub.create_service_line(p_k_line_rec         => l_k_line_rec,
                                              p_Contact_tbl        => l_line_contacts_tbl,
                                              p_line_sales_crd_tbl => l_line_sales_crd_tbl,
                                              x_service_line_id    => l_service_line_id,
                                              x_return_status      => l_return_status,
                                              x_msg_count          => l_msg_count,
                                              x_msg_data           => l_msg_data);
        IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
    OPEN coverage_cur (l_line_tbl(j).srv_id, l_header_tbl(i).organization_id);
          FETCH coverage_cur INTO l_tmp_cle_id;
          CLOSE coverage_cur;
    
    UPDATE oks_k_lines_b
          SET Standard_Cov_YN = 'Y',
            coverage_id = l_tmp_cle_id
          WHERE cle_id = l_service_line_id;
    
          OPEN subline_cur(l_line_tbl(j).order_line_id);
          FETCH subline_cur BULK COLLECT INTO l_subline_tbl;
          CLOSE subline_cur;
          FOR k IN l_subline_tbl.FIRST .. l_subline_tbl.LAST
          LOOP
            l_K_covd_rec.k_id                := l_chrid;
            l_K_covd_rec.Attach_2_Line_id    := l_service_line_id;
            l_k_covd_rec.line_number         := j||'.'||k;
            l_k_covd_rec.product_sts_code    := l_sts_code;
            l_k_covd_rec.customer_product_id := l_subline_tbl(k).customer_product_id;
            l_k_covd_rec.product_desc        := l_subline_tbl(k).product_desc;
            l_k_covd_rec.product_start_date  := l_line_tbl(j).start_date;
            l_k_covd_rec.product_end_date    := l_line_tbl(j).end_date;
            l_k_covd_rec.quantity            := l_line_tbl(j).quantity;
            l_k_covd_rec.uom_code            := l_line_tbl(j).uom_code;
            l_k_covd_rec.list_price          := l_header_tbl(i).price_list_id;
            l_k_covd_rec.negotiated_amount   := l_negotiated_amount;
            l_k_covd_rec.currency_code       := l_header_tbl(i).currency;
            l_k_covd_rec.line_renewal_type   := l_renewal_type;
            oks_contracts_pub.create_covered_line(p_k_covd_rec   => l_k_covd_rec,
                                                  p_price_attribs => l_price_attribs_in,
                                                  x_cp_line_id    => l_cp_line_id,
                                                  x_return_status => l_return_status,
                                                  x_msg_count     => l_msg_count,
                                                  x_msg_data      => l_msg_data);
            IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
              dbms_output.put_line ('Error while creating the Contract Subline for order#: '||l_header_tbl(i).order_number);
              IF NVL(l_msg_count,0) > 0 THEN
                FOR p IN 1..l_msg_count
                LOOP
                  dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
                END LOOP;
              END IF;
     EXIT;
            END IF;
          END LOOP;
  END IF;
  IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
    dbms_output.put_line ('Error while creating the Contract Line for order#: '||l_header_tbl(i).order_number);
          IF NVL(l_msg_count,0) > 0 THEN
            FOR p IN 1..l_msg_count
            LOOP
              dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
            END LOOP;
          END IF;
          ROLLBACK;
    EXIT;
        END IF;
      END LOOP;
 dbms_output.put_line('Contract Created Successfully for Order#'||l_header_tbl(i).order_number||'. Contract ID: '||l_chrid);   
    END IF;
  END LOOP;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line ('Error: '||SQLERRM);
  ROLLBACK;
END;
/