Wednesday, 24 February 2016

How to Upload the OPM OPERATIONS Conversion program using API



How to Upload the OPM OPERATIONS Conversion program using API

Part 1 :
Step 1:
First See the sample file of Template file given by the Functional Team or Client.
Step 2:
Then See the Screen of oracle apps, In which screen or form the data is uploaded.
Like 
Nevigation: Login to the oracle apps then go to the
Process Engineer > Process Operations >  then choose the “ORG_NAME” organization then the Operation form 











See the Mandatory Fields because When we make the table we will  take the fields .
Create the custom table à XXAKG_OPM_OPERATIONS_CONV_STG.tbl

OPM_OPERATIONS_CONV_STG table Script 
Step 3:
SET VERIFY OFF
WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
-------------------------------------------------------------------------------------
--$Revision$

--Description:            This table is used to act as staging table to OPM Operation details
--Maintenance History
--    Date         Author Name            Version         Description
--    -------       -----------           --------        -----------
--    17-FEB-2016   Danish Halim             1.0            Created
-------------------------------------------------------------------------------------------

--drop table XXAKG.XXAKG_OPM_OPERATIONS_CONV_STG

SET TERM ON
PROMPT 'Creating table XXAKG_OPM_OPERATIONS_CONV_STG'
SET TERM OFF

CREATE TABLE XXAKG.XXAKG_OPM_OPERATIONS_CONV_STG
(
  oprn_no                   VARCHAR2(160 CHAR),  --Operation No
  oprn_desc                 VARCHAR2(240 CHAR),  --Operation Desc 
  status                    VARCHAR2(230 CHAR), 
  oprn_vers                 NUMBER,              --Operation Version
  oprn_class                VARCHAR2(214 CHAR),
  d_effective_start_date    DATE,
  d_effective_end_date      DATE,
  owner_orgn_code           VARCHAR2(214 CHAR),  --Owner Organization
  minimum_transfer_qty      NUMBER,
  process_qty_uom           VARCHAR2(214 CHAR),  --Process Quantity UOM
  activity                  VARCHAR2(240 CHAR),  --Activity
  activity_factor           NUMBER,              --Activity Factor
  offset_interval           NUMBER,
  break_ind                 NUMBER,
  max_break                 NUMBER,
  material_ind              NUMBER,
  resources                 VARCHAR2(162 CHAR),  --Resource
  process_qty               NUMBER,              --Process Quantity
  resource_process_uom      VARCHAR2(202 CHAR),  --Resource Usage PCS
  resource_usage            NUMBER,             --Resource Usage
  resource_usage_uom        VARCHAR2(142 CHAR),  --Resource Usage UOM HR
  cmpnt_class               VARCHAR2(162 CHAR),
  cost_analysis_code        VARCHAR2(142 CHAR),
  prim_rsrc_ind             VARCHAR2(262 CHAR),
  resource_count            NUMBER,
  resource_offset_interval  VARCHAR2(262 CHAR),
  scale_type                VARCHAR2(802 CHAR),
  req_seq                   NUMBER,
  process_flag              VARCHAR2(202 CHAR),
  process_instruction       VARCHAR2(240 CHAR),
  sequence_dependent_ind    VARCHAR2(10 CHAR),
  error_message             VARCHAR2(4000),
  last_update_date          DATE,
  last_updated_by           NUMBER,
  creation_date             DATE,
  created_by                NUMBER,
  last_update_login         NUMBER
)
tablespace APPS_TS_TX_DATA;

--drop PUBLIC SYNONYM XXAKG_OPM_OPERATIONS_CONV_STG

CREATE PUBLIC SYNONYM XXAKG_OPM_OPERATIONS_CONV_STG FOR XXAKG.XXAKG_OPM_OPERATIONS_CONV_STG;

--SELECT * FROM XXAKG_OPM_OPERATIONS_CONV_STG

/
SHOW ERRORS

EXIT

Creating sequence XXAKG_OPM_OPERATIONS_CONV_S

----------------------------------------------------------------------
SET VERIFY OFF
WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
---------------------------------------------------------------------------
--$Revision$
--File Name:                 XXAKG_OPM_OPERATIONS_CONV_S.seq
--Table Name:               
--Description:                This sequence is used for serial_no in staging table for Operation NO
--Maintenance History
--    Date                Author Name                       Version             Description
--    -------             -----------                       --------            -----------
--    17-FEB-2016        Danish Halim                          1.0                 Created
---------------------------------------------------------------------------
SET TERM ON
PROMPT 'Creating sequence XXAKG_OPM_OPERATIONS_CONV_S'
SET TERM OFF

CREATE SEQUENCE  XXAKG.XXAKG_OPM_OPERATIONS_CONV_S
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE;

CREATE    PUBLIC SYNONYM XXAKG_OPM_OPERATIONS_CONV_S FOR XXAKG.XXAKG_OPM_OPERATIONS_CONV_S;
/
SHOW ERRORS
EXIT
--------------------------------------------------------------------------------------------------------------------------------------
Step 4: 
Create the . CTL (Control file) 
OPM_OPERATIONS_CTL.ctl Script 
-- File Name           : XXAKG_OPM_OPERATIONS_CTL.ctl
-- Author's name       : Danish Halim
-- Date written        : 17-FEB-2016
-- Description         : Loader Script.
-- Program Style       : Loader Script.
-- Main Procedure name : N/A
--
-- Maintenance History:
--
--    Date                Author Name                       Version             Description
--    -------             -----------                       --------            -----------
--    17-FEB-2016        Danish Halim                        1.0                 Created
-- ----------------------------------------------------------------------------------------
OPTIONS (SKIP=1)
--LOAD DATA REPLACE
LOAD DATA APPEND
INTO TABLE XXAKG_OPM_OPERATIONS_CONV_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 OPRN_NO                     "(UPPER(TRIM(REPLACE(:OPRN_NO,CHR(34),' '))))"
,OPRN_DESC                   "TRIM(:OPRN_DESC)"
,STATUS                      "TRIM(:STATUS)"
,OPRN_VERS                   "TRIM(:OPRN_VERS)"
,OPRN_CLASS                  "TRIM(:OPRN_CLASS)"
,D_EFFECTIVE_START_DATE      "TO_DATE(:D_EFFECTIVE_START_DATE,'DD/MM/YYYY HH24:MI:SS')"
,D_EFFECTIVE_END_DATE        "TO_DATE(:D_EFFECTIVE_END_DATE,'DD/MM/YYYY HH24:MI:SS')"
,OWNER_ORGN_CODE             "TRIM(:OWNER_ORGN_CODE)"
,MINIMUM_TRANSFER_QTY        "TRIM(:MINIMUM_TRANSFER_QTY)"
,PROCESS_QTY_UOM             "TRIM(:PROCESS_QTY_UOM)"
,ACTIVITY                    "TRIM(:ACTIVITY)"
,ACTIVITY_FACTOR             "TRIM(:ACTIVITY_FACTOR)"
,SEQUENCE_DEPENDENT_IND      "TRIM(:SEQUENCE_DEPENDENT_IND)"
,OFFSET_INTERVAL             "TRIM(:OFFSET_INTERVAL)"
,BREAK_IND                   "TRIM(:BREAK_IND)"   
,MAX_BREAK                   "TRIM(:MAX_BREAK)"
,MATERIAL_IND                "TRIM(:MATERIAL_IND)"
,RESOURCES                   "(UPPER(TRIM(:RESOURCES)))"
,PROCESS_QTY                 "TRIM(:PROCESS_QTY)"
,RESOURCE_PROCESS_UOM        "TRIM(:RESOURCE_PROCESS_UOM)"
,RESOURCE_USAGE              "TRIM(:RESOURCE_USAGE)"
,RESOURCE_USAGE_UOM          "TRIM(:RESOURCE_USAGE_UOM)"
,CMPNT_CLASS                 "(UPPER(TRIM(:CMPNT_CLASS)))"
,COST_ANALYSIS_CODE          "TRIM(:COST_ANALYSIS_CODE)"
,PRIM_RSRC_IND               "TRIM(:PRIM_RSRC_IND)"
,RESOURCE_COUNT              "TRIM(:RESOURCE_COUNT)"
,RESOURCE_OFFSET_INTERVAL    "TRIM(:RESOURCE_OFFSET_INTERVAL)"
,SCALE_TYPE                  "TRIM(REPLACE(:SCALE_TYPE,CHR(13),''))"
,PROCESS_INSTRUCTION          "TRIM(SUBSTR(REPLACE(:PROCESS_INSTRUCTION,CHR(34),' '), 1, 240))"
,REQ_SEQ                     "XXAKG_OPM_OPERATIONS_CONV_S.NEXTVAL"
,PROCESS_FLAG                CONSTANT "NEW"
,LAST_UPDATE_DATE            "TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS')"
,LAST_UPDATED_BY             "FND_GLOBAL.USER_ID"
,CREATION_DATE               "TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS')"
,CREATED_BY                  "FND_GLOBAL.USER_ID"
,LAST_UPDATE_LOGIN           "FND_GLOBAL.USER_ID"
)
Step 5:
First we make the .csv file given by the Functional or client Template V1.
Like
XXAKG_OPM_OPERATION_COV.csv

XXAKG_OPM_OPERATION_COV.csv file 


Note:
We always match the .csv file fields to .ctl file fields sequence by respectively. It is more Important and always remember other wise You have face many errors.

Step 6: 




Complete the process of registration of OPM OPERATION CONVERSION file to upload the data into the custom staging table.

 Like
(This Program inserted the data into the XXAKG_OPM_OPERATIONS_CONV_STG staging table from the XXAKG_OPM_OPERATION_COV.csv )

Registration Process  of
AKG OPM Operations Conversion Upload
(This Program inserted the data into the XXAKG_OPM_OPERATIONS_CONV_STG staging table from the XXAKG_OPM_OPERATION_COV.csv )
Executable   :     XXAKG_OPM_OPERATIONS_CONV
Short Name  :    XXAKG_OPM_OPERATIONS_CONV
Application  :     AKG Custom Application
Execution Method : SQL *Loader
Exection File Name :  XXAKG_OPM_OPERATIONS_CTL

(Exection File Name :  XXAKG_OPM_OPERATIONS_CTL --  This is the .ctl file(XXAKG_OPM_OPERATIONS_CTL.ctl) we have crated becouse system undertsand from this file.)
 





Program Name : AKG OPM Operations Conversion Upload
 






Add the Parameter Name : DATA
Value Set : 240 Characters
Default Type : Constant
Default value : /applakg1/AKG27/fs2/EBSapps/appl/xxakg/12.0.0/bin/XXAKG_OPM_OPERATION_COV.csv
(This path is the SERVER path where we have kept  the .csv file (XXAKG_OPM_OPERATION_COV.csv ) )
Because the .ctl file(XXAKG_OPM_OPERATIONS_CTL.ctl) is read the data from this .csv file (XXAKG_OPM_OPERATION_COV.csv) and inserted the data into to custom staging table (XXAKG_OPM_OPERATIONS_CONV_STG).

 







Add this program to the Request Group for RUN the Request .
Like -- Process Engineer (Responsibility) >>
Group : OPM GMD Request Group
Add the program to the Request Group : AKG OPM Operations Conversion Upload
 





Then go to the Responsibility
Process Engineer > View > Request > Submit a New Request  >
 



Then give the Program name like : AKG OPM Operations Conversion Upload






Then check the staging table
select * from XXAKG_OPM_OPERATIONS_CONV_STG;




Data are shown.
Write the Package to inserted the data from the Staging table XXAKG_OPM_OPERATIONS_CONV_STG to Base Table of OPM Operation.
--- API Running
--  Will insert in following tables



select * from GMD_OPERATIONS_B
select * from GMD_OPERATION_ACTIVITIES
select * from GMD_OPERATION_RESOURCES


Step 1:  Create Package
Package Name : APPS.xxakg_opm_operation_conv_pkg

--------------------------- Package Specification -------------------------
CREATE OR REPLACE PACKAGE APPS.xxakg_opm_operation_conv_pkg AUTHID CURRENT_USER
-------------------------------------------------------------------------------
/*
 Created By   : Danish Halim
 Creation Date: 18-FEB-2016
 Filename     : xxakg_opm_operation_conv_pkg.pks
 Description  : This package is used to Import OPM Operation
 Change History:
--    Date                Author Name                       Version             Description
--    -------             -----------                       --------            -----------
--    18-FEB-2016        Danish Halim                        1.0                 Created
*/
-------------------------------------------------------------------------------
AS
--
   gv_field_sep   VARCHAR2 (1) := '.';

   PROCEDURE xxakg_opm_operation_conv_prc (
      x_errbuf           OUT      VARCHAR2,
      x_retcode          OUT      NUMBER,
      p_process_status   IN       VARCHAR2
   );
END xxakg_opm_operation_conv_pkg;
/
------------------------------ Package Body -----------------------------
CREATE OR REPLACE PACKAGE BODY APPS.xxakg_opm_operation_conv_pkg
IS
-------------------------------------------------------------------------------
/*
 Created By   : Danish Halim
 Creation Date: 18-FEB-2016
 Filename     : xxakg_opm_operation_conv_pkg.pkb
 Description  : Program to validate and load the Operations
 Change History:
--    Date                Author Name                       Version             Description
--    -------             -----------                       --------            -----------
--    18-FEB-2016         Danish Halim                       1.0                 Created
*/
-------------------------------------------------------------------------------
   gn_request_id         NUMBER := fnd_profile.VALUE ('CONC_REQUEST_ID');
   gn_user_id            NUMBER := fnd_profile.VALUE ('USER_ID');
   gn_resp_app_id        NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
   gn_resp_id            NUMBER := fnd_profile.VALUE ('RESP_ID');
   ln_processed_recs     NUMBER := 0;
   ln_successful_recs    NUMBER := 0;
   ln_error_recs         NUMBER := 0;
   ln_oper_api_error     NUMBER := 0;
   ln_oper_api_success   NUMBER := 0;

   PROCEDURE operation_main (
      x_errbuf           OUT      VARCHAR2,
      x_retcode          OUT      NUMBER,
      p_process_status   IN       VARCHAR2
   );

   PROCEDURE modify_status (
      p_entity_name     IN       VARCHAR2,
      p_entity_id       IN       NUMBER,
      p_status_to       IN       VARCHAR2,
      x_message_count   OUT      NUMBER,
      x_message_list    OUT      VARCHAR2,
      x_return_status   OUT      VARCHAR2
   )
   IS
---------------------------------------------------------------------
-- Purpose: This procedure is to modify status
--
-- Pre-reqs:
--
-- Modifies:
--
-- Parameters:
-- IN
--  p_entity_name:The name if Operation or Routing
--  p_entity_id:The id for operation/routing
--  p_status_to:The status of operation/routing
--
-- OUT
--  x_message_count:The message count
--  x_message_list:The message list
--  x_return_status:Status if Success or Error
----------------------------------------------------------------------
--  Modification History
--  Date          Developer         Comments
----------------------------------------------------------------------
--  18-FEB-2016  Danish Halim     Initial Creation
----------------------------------------------------------------------
      ln_msg_cnt      NUMBER;
      ln_out_index    NUMBER;
      lv_msg_lst      VARCHAR2 (4000);
      lv_ret_status   VARCHAR2 (10);
      lv_msg_data     VARCHAR2 (4000);
   BEGIN
      x_message_count := 0;
      x_message_list := '';
      x_return_status := fnd_api.g_ret_sts_success;
      fnd_file.put_line (fnd_file.LOG, ' Calling Modify Status API ');
      gmd_status_pub.modify_status (p_api_version         => 1,
                                    p_init_msg_list       => TRUE,
                                    p_entity_name         => p_entity_name,
                                    p_entity_id           => p_entity_id,
                                    p_entity_no           => NULL,
                                    p_entity_version      => NULL,
                                    p_to_status           => p_status_to,
                                    p_ignore_flag         => FALSE,
                                    x_message_count       => ln_msg_cnt,
                                    x_message_list        => lv_msg_lst,
                                    x_return_status       => lv_ret_status
                                   );
      fnd_file.put_line (fnd_file.LOG, 'Checking Error 1');

      IF lv_ret_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. ln_msg_cnt
         LOOP
            fnd_msg_pub.get (p_msg_index          => i,
                             p_encoded            => 'F',
                             p_data               => lv_msg_data,
                             p_msg_index_out      => ln_out_index
                            );
            fnd_file.put_line (fnd_file.LOG,
                               'Message Text :' || SUBSTR (lv_msg_data, 1, 60)
                              );
         END LOOP;
      ELSE
         COMMIT;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
      x_return_status := fnd_api.g_ret_sts_error;
         fnd_file.put_line (fnd_file.LOG,
          'Exception in Modify Status Program :' || SQLERRM );
      x_message_list := 'Exception in Modify Status Program :' || SQLERRM;
      x_message_count := 1;
   END;

  


PROCEDURE xxakg_opm_operation_conv_prc (
      x_errbuf           OUT      VARCHAR2,
      x_retcode          OUT      NUMBER,
      p_process_status   IN       VARCHAR2
   )
   IS
---------------------------------------------------------------------
-- Purpose: This procedure is to load operations tables
--
-- Pre-reqs:
--
-- Modifies:
--
-- Parameters:
-- IN
--
-- OUT
-- x_errbuf : Errors stored
-- x_retcode : A number returned
----------------------------------------------------------------------
--  Modification History
--  Date          Developer         Comments
----------------------------------------------------------------------
--  18-FEB-2016  Danish Halim    Initial Creation
----------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
-- Private Variable Declaration Section
--------------------------------------------------------------------------------------------------------
      lv_error_flag     VARCHAR2 (1)     := 'N';
      lv_errbuf         VARCHAR2 (32767);
      lv_process_flag   VARCHAR2 (10)    := p_process_status;
      ln_retcode        NUMBER (20);
   BEGIN
      fnd_file.put_line
         (fnd_file.LOG,
          '---------------------------------------------------------------'
         );
      fnd_file.put_line
         (fnd_file.LOG,
          '..................Operations Main program Start ...............'
         );
      operation_main (lv_errbuf, ln_retcode, lv_process_flag);
      fnd_file.put_line
         (fnd_file.LOG,
          '---------------------------------------------------------------'
         );
   END xxakg_opm_operation_conv_prc;

   PROCEDURE operation_main (
      x_errbuf           OUT      VARCHAR2,
      x_retcode          OUT      NUMBER,
      p_process_status   IN       VARCHAR2
   )
   IS
-- This procedure is to load operations data into the base tables through API
--------------------------------------------------------------------------------------------------------
-- Cursor Declaration Section
--------------------------------------------------------------------------------------------------------
-- This cursor extracts operation data from the staging table
      CURSOR cur_operations
      IS
         SELECT   oprn_no, oprn_vers, oprn_desc, process_qty_uom,
                  d_effective_start_date, d_effective_end_date,
                  owner_orgn_code, status, process_instruction,
                  minimum_transfer_qty, oprn_class
             FROM XXAKG_OPM_OPERATIONS_CONV_STG
            WHERE (process_flag IN ('NEW', 'ERROR', 'VALIDATE')
                               AND (p_process_status = 'ALL')
                                OR  process_flag = ('NEW')   AND (p_process_status = 'NEW')
                                OR  process_flag = ('ERROR') AND (p_process_status = 'ERROR')
                  )
            ORDER BY oprn_no, oprn_vers;

-- This cursor extracts activities data related to operations from the staging table
--with operation no and version being IN parameters
      CURSOR cur_operation_activities (
         p_oprn_no     IN   VARCHAR2,
         p_oprn_vers   IN   NUMBER
      )
      IS
         SELECT   oprn_no, oprn_vers, activity, NVL(offset_interval,0) offset_interval,
                  activity_factor, break_ind, max_break, material_ind,
                  process_instruction, minimum_transfer_qty, process_qty_uom,
                  DECODE (sequence_dependent_ind,
                          'Y', 1,
                          'N', 0,
                          0
                         ) sequence_dependent_ind
             FROM XXAKG_OPM_OPERATIONS_CONV_STG
            WHERE oprn_no   = p_oprn_no
              AND oprn_vers = p_oprn_vers
              AND ( process_flag IN ('NEW', 'ERROR', 'VALIDATE')
                                AND (p_process_status = 'ALL')
                                 OR  process_flag = ('NEW') AND (p_process_status = 'NEW')
                                 OR  process_flag = ('ERROR')
                                AND (p_process_status = 'ERROR')
                  )
         ORDER BY activity, sequence_dependent_ind DESC;
    
-- This cursor extracts resources data related to operations from the staging table
--with operation no ,version and activity being IN parameters
      CURSOR cur_operation_resources (
         p_oprn_no         IN   VARCHAR2,
         p_oprn_vers       IN   NUMBER,
         p_oprn_activity   IN   VARCHAR2
      )
      IS
      SELECT oprn_no, oprn_vers, UPPER (resources) resources,
                resource_usage, resource_count, process_qty, prim_rsrc_ind,
                scale_type, cost_analysis_code, cmpnt_class,
                resource_offset_interval, resource_process_uom,
                resource_usage_uom, process_instruction, minimum_transfer_qty,
                process_qty_uom
           FROM XXAKG_OPM_OPERATIONS_CONV_STG
          WHERE oprn_no   = p_oprn_no
            AND oprn_vers = p_oprn_vers
            AND activity  = p_oprn_activity
            AND ( process_flag IN ('NEW', 'ERROR', 'VALIDATE')
                              AND (p_process_status = 'ALL')
                               OR process_flag = ('NEW') AND (p_process_status = 'NEW')
                               OR process_flag = ('ERROR') AND (p_process_status = 'ERROR')
                );
               
--------------------------------------------------------------------------------------------------------
-- Private Variable Declaration Section
--------------------------------------------------------------------------------------------------------
      lv_operation_rec           cur_operations%ROWTYPE;
      lv_operations              gmd_operations%ROWTYPE;
      lv_user_name               fnd_user.user_name%TYPE;
      lv_oprn_no_previous        gmd_operations_b.oprn_no%TYPE;
      lv_oprn_vers_previous      gmd_operations_b.oprn_vers%TYPE;
      ln_header_id               NUMBER;
      lv_oprn_actv_previous      gmd_operation_activities.activity%TYPE;
      lv_oprn_actv_tbl           gmd_operations_pub.gmd_oprn_activities_tbl_type;
      lv_oprn_rsrc_tbl           gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
      ln_request_id              NUMBER;
      lv_error_flag              VARCHAR2 (1)                           := 'N';
      lv_msg_data                VARCHAR2 (4000);
      lv_return_status           VARCHAR2 (1);
      lv_error_message           VARCHAR2 (3000);
      lv_process_flag            VARCHAR2 (1)                           := 'Y';
      lv_status_code             VARCHAR2 (30);
      lv_msg_list                VARCHAR2 (3000);
      lv_ret_sts                 VARCHAR2 (30);
      lv_return_sts              BOOLEAN;
      ln_user_id                 NUMBER;
      ln_login_id                NUMBER;
      ln_msg_count               NUMBER                                   := 0;
      ln_responsibility_app_id   NUMBER;
      ln_version                 NUMBER                                   := 3;
      ln_responsibility_id       NUMBER       := fnd_profile.VALUE ('RESP_ID');
      ln_out_index               NUMBER                                   := 0;
      ln_count                   NUMBER                                   := 1;
      ln_owner_organization_id   NUMBER;
      ln_prim_rsrc_ind           NUMBER;
      ln_scale_type              NUMBER;
      ln_cost_cmpntcls_id        NUMBER;
      ln_oprn_actv_counter       NUMBER;
      ln_oprn_rsrc_counter       NUMBER;
      ln_oprn_id                 NUMBER;
      ln_msg_cnt                 NUMBER;
      ln_return_value            NUMBER                                := NULL;
      --stores number returned by EMF API's
      ln_proc_stg_recs           NUMBER                                   := 0;
      ln_err_stg_recs            NUMBER                                   := 0;
      ln_succ_stg_recs           NUMBER                                   := 0;
      lv_error_cat_flag          VARCHAR2 (1)                             := 'N';
      --Flag to capture error (Y=ERROR/N=SUCCESS)
      lv_warning_flag            VARCHAR2 (1); --Flag to capture warning ('W')
      lv_output_message          VARCHAR2 (1000);
      --stores the message returned from external routines
      lv_level                   VARCHAR2 (1000);
      l_conc_segment             VARCHAR2 (1000);
      lv_error_mesage            VARCHAR2 (10000)                      := NULL;
      cntrl_fail_exp             EXCEPTION;
      control_record_error_exp   EXCEPTION;
   BEGIN                                                          --Main Begin
      fnd_file.put_line
                      (fnd_file.LOG,
                       'Staring the OPM Operations Conversion Program ...'
                      );
      -- Initializing the basic variables
      ln_request_id := fnd_global.conc_request_id;
      ln_user_id    := fnd_global.user_id;
      ln_login_id   := fnd_global.login_id;
      lv_user_name  := fnd_global.user_name;
      fnd_file.put_line (fnd_file.LOG,'EMF initialized, header_id :' || TO_CHAR (ln_header_id));

      ----Begin for header validation
      OPEN cur_operations;

      FETCH cur_operations
       INTO lv_operation_rec;

                  IF cur_operations%NOTFOUND
                  THEN
                     fnd_file.put_line (fnd_file.output,
                                        'No more Operation Records to Process'
                                       );

                     CLOSE cur_operations;
                  ELSE
                     CLOSE cur_operations;

         FOR oprn_rec IN cur_operations
         LOOP
            fnd_file.put_line (fnd_file.LOG,
                                  'Debug -> Fisrt Loop Starts for -- Operations -- : '
                               || oprn_rec.oprn_no
                               || ' '
                               || oprn_rec.oprn_vers
                               || ' '
                               || oprn_rec.owner_orgn_code
                              );

            IF (    (lv_oprn_no_previous = oprn_rec.oprn_no)
                AND (lv_oprn_vers_previous = oprn_rec.oprn_vers)
               )
            THEN
               NULL;
            ELSE
               fnd_file.put_line (fnd_file.LOG,
                                  'Debug -> Before Variable initialization operations : '
                                 );
               lv_oprn_no_previous := oprn_rec.oprn_no;
               lv_oprn_vers_previous := oprn_rec.oprn_vers;
               lv_error_flag := 'N';
               lv_return_sts := gmigutl.setup ('OPM');
               fnd_global.apps_initialize (gn_user_id,
                                           gn_resp_id,
                                           gn_resp_app_id
                                          );

               BEGIN
                  ------ Retrieve Organization ID
                  BEGIN
                     fnd_file.put_line (fnd_file.LOG,
                                        'Debug -> Before getting org - owner_organization_id '
                                       );

                     SELECT organization_id
                       INTO ln_owner_organization_id
                       FROM org_organization_definitions
                      WHERE organization_code = oprn_rec.owner_orgn_code;

                     fnd_file.put_line (fnd_file.LOG,
                                           'Debug -> organization id '
                                        || ln_owner_organization_id
                                       );
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        fnd_file.put_line
                                         (fnd_file.LOG,
                                             'XXAKG OPM OPERATIONS CONVERSION STG - 0001  '
                                          || '      '
                                          || 'Organization ID does not exist'
                                          || '      '
                                          || oprn_rec.oprn_no
                                          || '      '
                                          || oprn_rec.oprn_vers
                                         );
                        lv_error_flag := 'Y';
                        lv_error_mesage := lv_error_mesage
                                           || '~~'
                                           || 'Organization ID does not exist';
                     WHEN OTHERS
                     THEN
                        fnd_file.put_line
                                    (fnd_file.LOG,
                                        'XXAKG OPM OPERATIONS CONVERSION STG - 0002  '
                                     || '      '
                                     || 'Error in Organization ID Derivation'
                                     || '      '
                                     || oprn_rec.oprn_no
                                     || '      '
                                     || oprn_rec.oprn_vers
                                    );
                        lv_error_flag := 'Y';
                        lv_error_mesage := lv_error_mesage
                                           || '~~'
                                           || 'Error in Organization ID Derivation';
                  END;

                  fnd_file.put_line (fnd_file.LOG,
                                     'Debug -> Before Var initialization '
                                    );
                  -- This Operation Header Data
                  lv_operations.oprn_no     := oprn_rec.oprn_no;
                  lv_operations.oprn_vers   := oprn_rec.oprn_vers;
                  lv_operations.oprn_desc   := oprn_rec.oprn_desc;
                  lv_operations.oprn_class  := oprn_rec.oprn_class;
                  lv_operations.process_qty_uom := oprn_rec.process_qty_uom;
                  lv_operations.effective_start_date := oprn_rec.d_effective_start_date;
                  lv_operations.effective_end_date :=   oprn_rec.d_effective_end_date;
                  lv_operations.owner_organization_id := ln_owner_organization_id;
                  lv_operations.operation_status := oprn_rec.status;
                  lv_operations.minimum_transfer_qty := oprn_rec.minimum_transfer_qty;
                  lv_operations.creation_date := TO_DATE (SYSDATE, 'DD-MON-RR');
                  lv_operations.last_update_date := TO_DATE (SYSDATE, 'DD-MON-RR');
                  lv_operations.created_by := gn_user_id;
                  lv_operations.last_updated_by := gn_user_id;
                  lv_operations.attribute1 := oprn_rec.process_instruction;
                  lv_operations.delete_mark := 0;
                  ln_oprn_actv_counter := 0;
                  lv_oprn_actv_tbl.DELETE;
                  lv_oprn_actv_previous := '';
                  lv_oprn_rsrc_tbl.DELETE;
                  ln_oprn_rsrc_counter := 0;
                  fnd_file.put_line (fnd_file.LOG,
                                     'Debug -> After Var initialization '
                                    );

                  FOR c_oprn_actv IN
                     cur_operation_activities (oprn_rec.oprn_no,
                                               oprn_rec.oprn_vers
                                              )
                  LOOP
                     fnd_file.put_line (fnd_file.LOG,
                                        'Debug -> Second Loop starts for -- Activity --'
                                       );

                     IF (c_oprn_actv.activity = lv_oprn_actv_previous)
                     THEN
                        NULL;
                     ELSE
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 1 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_previous := c_oprn_actv.activity;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 2 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        ln_oprn_actv_counter := ln_oprn_actv_counter + 1;
                        -- Operations Activity Details
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 3 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).activity := c_oprn_actv.activity;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 4 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).offset_interval := c_oprn_actv.offset_interval;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 5 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).activity_factor := c_oprn_actv.activity_factor;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 6 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).break_ind :=    c_oprn_actv.break_ind;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 7 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).max_break :=    c_oprn_actv.max_break;
                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 8 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).material_ind := c_oprn_actv.material_ind;
                        lv_oprn_actv_tbl (ln_oprn_actv_counter).sequence_dependent_ind := c_oprn_actv.sequence_dependent_ind;

                        /*fnd_file.put_line (fnd_file.LOG,
                                              'Test Print 9 '
                                           || oprn_rec.oprn_no
                                           || '            '
                                           || oprn_rec.oprn_vers
                                          );*/
                        FOR c_oprn_rsrc IN
                           cur_operation_resources (oprn_rec.oprn_no,
                                                    oprn_rec.oprn_vers,
                                                    c_oprn_actv.activity
                                                   )
                        LOOP
                           ----- Retrieve COST_CMPNTCLS_ID for resources
                           BEGIN
                                SELECT cmb.cost_cmpntcls_id
                                INTO ln_cost_cmpntcls_id
                                FROM cm_cmpt_mst_b cmb,CR_RSRC_MST_VL crm
                                where cmb.COST_CMPNTCLS_ID = crm.COST_CMPNTCLS_ID
                                and  crm.RESOURCES =  UPPER (c_oprn_rsrc.resources); 
                          
                            /*
                              SELECT cost_cmpntcls_id
                                INTO ln_cost_cmpntcls_id
                                FROM cm_cmpt_mst_b
                               WHERE cost_cmpntcls_code = UPPER (c_oprn_rsrc.cmpnt_class);
                               */
                           EXCEPTION
                              WHEN NO_DATA_FOUND
                              THEN
                                 fnd_file.put_line
                                         (fnd_file.LOG,
                                             'XXAKG OPM OPERATIONS CONVERSION STG - 0003  '
                                          || '      '
                                         -- || 'Component Class does not exist for OPERATIONS : '
                                          || 'RESOURCE does not exist for Component Class in OPERATIONS : '
                                          || '      '
                                          || c_oprn_rsrc.oprn_no
                                          || '      and OPERATIONS VERSION : '
                                          || c_oprn_rsrc.oprn_vers
                                         );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'RESOURCE does not exist for Component Class';
                              WHEN OTHERS
                              THEN
                                 fnd_file.put_line
                                     (fnd_file.LOG,
                                         'XXAKG OPM OPERATIONS CONVERSION STG - 0004 '
                                      || '      '
                                      || 'Error in RESOURCE does not exist for Component Class Retrieval for OPERATIONS :'
                                      || '      '
                                      || c_oprn_rsrc.oprn_no
                                      || '       and OPERATIONS VERSION :  '
                                      || c_oprn_rsrc.oprn_vers
                                     );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'Error in RESOURCE does not exist for Component Class Retrieval';
                           END;

                           ----- Retrieve Scale Type for resources
                           BEGIN
                            
                            SELECT TO_NUMBER (lookup_code)
                               INTO ln_scale_type
                               FROM fnd_lookup_values
                              WHERE lookup_type = 'SCALE_TYPE'
                                AND UPPER (meaning) =UPPER('Proportional')
                                              -- UPPER (c_oprn_rsrc.scale_type)
                            AND LANGUAGE = USERENV ('LANG'); 
                           EXCEPTION
                              WHEN NO_DATA_FOUND
                              THEN
                                 fnd_file.put_line
                                              (fnd_file.LOG,
                                                  'XXAKG OPM OPERATIONS CONVERSION STG - 0005  '
                                               || '      '
                                               || 'Scale Type does not exist for OPERATIONS : '
                                               || '      '
                                               || c_oprn_rsrc.oprn_no
                                               || '     and OPERATIONS VERSION :  '
                                               || c_oprn_rsrc.oprn_vers
                                              );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'Scale Type does not exist';
                              WHEN OTHERS
                              THEN
                                 fnd_file.put_line
                                          (fnd_file.LOG,
                                              'XXAKG OPM OPERATIONS CONVERSION STG - 0006  '
                                           || '      '
                                           || 'Error in Scale Type Retrieval for OPERATIONS : '
                                           || '      '
                                           || c_oprn_rsrc.oprn_no
                                           || '    and OPERATIONS VERSION :  '
                                           || c_oprn_rsrc.oprn_vers
                                          );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'Error in Scale Type Retrieval';
                           END;

                           ----- Retrieve PRIM_RSRC_IND for resources
                           BEGIN
                            
                          
                             SELECT TO_NUMBER (lookup_code)
                              INTO ln_prim_rsrc_ind
                              FROM fnd_lookup_values
                             WHERE lookup_type = 'GMD_PRIM_RSRC_IND'
                               AND meaning = NVL (c_oprn_rsrc.prim_rsrc_ind,'Primary')
                               AND LANGUAGE = USERENV ('LANG');
                           
                       
                              /*
                              SELECT TO_NUMBER (lookup_code)
                                INTO ln_prim_rsrc_ind
                                FROM fnd_lookup_values
                               WHERE lookup_type = 'GMD_PRIM_RSRC_IND'
                                 AND meaning = c_oprn_rsrc.prim_rsrc_ind
                                 AND LANGUAGE = USERENV ('LANG');
                               */
                           EXCEPTION
                              WHEN NO_DATA_FOUND
                              THEN
                                 fnd_file.put_line
                                           (fnd_file.LOG,
                                               'XXAKG OPM OPERATIONS CONVERSION STG - 0007  '
                                            || '      '
                                            || 'PRIM_RSRC_IND does not exist  for OPERATIONS : '
                                            || '      '
                                            || c_oprn_rsrc.oprn_no
                                            || '     and OPERATIONS VERSION :  '
                                            || c_oprn_rsrc.oprn_vers
                                           );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'PRIM_RSRC_IND does not exist';
                              WHEN OTHERS
                              THEN
                                 fnd_file.put_line
                                       (fnd_file.LOG,
                                           'XXAKG OPM OPERATIONS CONVERSION STG - 0008  '
                                        || '      '
                                        || 'Error in PRIM_RSRC_IND Retrieval'
                                        || '      '
                                        || c_oprn_rsrc.oprn_no
                                        || '      '
                                        || c_oprn_rsrc.oprn_vers
                                       );
                                 lv_error_flag := 'Y';
                                 lv_error_mesage := lv_error_mesage
                                                    || '~~'
                                                    || 'Error in PRIM_RSRC_IND Retrieval';
                           END;

                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           ln_oprn_rsrc_counter := ln_oprn_rsrc_counter + 1;
                           ---- Operation resources
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10.1 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                    lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resources := c_oprn_rsrc.resources;      --Danish 19-FB-2016  --
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10.2 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_usage := c_oprn_rsrc.resource_usage;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10.3 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                              --Danish 19-FB-2016  --  lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_count := c_oprn_rsrc.resource_count;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10.4 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).process_qty := c_oprn_rsrc.process_qty;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 10.5 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).prim_rsrc_ind :=ln_prim_rsrc_ind;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 11 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).scale_type := ln_scale_type;
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).cost_analysis_code := c_oprn_rsrc.cost_analysis_code;
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).cost_cmpntcls_id := ln_cost_cmpntcls_id;
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).offset_interval := c_oprn_actv.offset_interval;
                                       --c_oprn_rsrc.resource_offset_interval;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 12 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           -- lv_oprn_rsrc_tbl(1).delete_mark    := 0;
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_process_uom :=  c_oprn_rsrc.resource_process_uom;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 13 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).resource_usage_uom := c_oprn_rsrc.resource_usage_uom;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 14 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           lv_oprn_rsrc_tbl (ln_oprn_rsrc_counter).activity :=  c_oprn_actv.activity;
                           /*fnd_file.put_line (fnd_file.LOG,
                                                 'Test Print 15 '
                                              || oprn_rec.oprn_no
                                              || '            '
                                              || oprn_rec.oprn_vers
                                             );*/
                           ln_processed_recs := ln_processed_recs + 1;
                        END LOOP;
                     END IF;
                  --- (c_oprn_actv.activity != lv_oprn_actv_previous)
                  END LOOP;

                  --- c_oprn_actv IN cur_operation_activities loop ended
                  fnd_file.put_line
                                 (fnd_file.LOG,
                                     'oprn_rec.oprn_no ## oprn_rec.oprn_vers for OPERATIONS NAME : '
                                  || oprn_rec.oprn_no
                                  || '   and OPERATIONS VERSION : '
                                  || oprn_rec.oprn_vers
                                 );
                  fnd_file.put_line (fnd_file.LOG,
                                        'p_oprn_rsrc_tbl count is  :'
                                     || lv_oprn_rsrc_tbl.COUNT
                                    );
                          --- API Running
                  ---  Will insert in following tables GMD_OPERATIONS_B
                  ---- GMD_OPERATION_ACTIVITIES
                  ---- GMD_OPERATION_RESOURCES
                  fnd_file.put_line (fnd_file.LOG,'lv_error_flag  :'   || lv_error_flag );

                  IF (lv_error_flag = 'Y')
                  THEN
                     UPDATE XXAKG_OPM_OPERATIONS_CONV_STG
                        SET process_flag = 'E',
                            error_message = lv_error_message
                      WHERE oprn_no = oprn_rec.oprn_no
                        AND oprn_vers = oprn_rec.oprn_vers;

                     fnd_file.put_line (fnd_file.LOG,
                                           'lv_error_message -- Please Chink Errors'
                                        || lv_error_message
                                       );
                  ELSE
                     fnd_file.put_line (fnd_file.LOG, 'Calling the OPERATIONS API for Inserting data...');
                     gmd_operations_pub.insert_operation
                                        (p_api_version        => 1.0,
                                         p_init_msg_list      => TRUE,
                                         p_commit             => FALSE,
                                         p_operations         => lv_operations,
                                         p_oprn_actv_tbl      => lv_oprn_actv_tbl,
                                         x_message_count      => ln_msg_count,
                                         x_message_list       => lv_msg_data,
                                         x_return_status      => lv_return_status,
                                         p_oprn_rsrc_tbl      => lv_oprn_rsrc_tbl
                                        );
                     COMMIT;
                     fnd_file.put_line (fnd_file.LOG, lv_return_status);
                   --  fnd_file.put_line (fnd_file.LOG, 'Checking Error 2');

                     IF lv_return_status <> 'S'
                    
                     THEN
                     fnd_file.put_line (fnd_file.LOG, 'Checking lv_return_status Error ');
                        BEGIN
                           lv_error_flag := 'Y';
                           ln_oper_api_error := ln_oper_api_error + 1;

                           -- ln_successful_recs := 0;
                           FOR i IN 1 .. ln_msg_count
                           LOOP
                              fnd_msg_pub.get
                                             (p_msg_index          => i,
                                              p_encoded            => 'F',
                                              p_data               => lv_msg_data,
                                              p_msg_index_out      => ln_out_index
                                             );
                              fnd_file.put_line (fnd_file.LOG,
                                                    'Message Text :     '
                                                 || oprn_rec.oprn_no
                                                 || '     '
                                                 || oprn_rec.oprn_vers
                                                 || '     '
                                                 || SUBSTR (lv_msg_data,
                                                            1,
                                                            200
                                                           )
                                                );
                              fnd_file.put_line (fnd_file.LOG,
                                                    'lv_msg_data chking erors'
                                                 || oprn_rec.oprn_no
                                                 || '     '
                                                 || oprn_rec.oprn_vers
                                                 || '     '
                                                 || SUBSTR (lv_msg_data,
                                                            1,
                                                            200
                                                           )
                                                );

                              UPDATE XXAKG_OPM_OPERATIONS_CONV_STG
                                 SET process_flag = 'E',
                                     error_message = lv_msg_data
                               WHERE oprn_no = oprn_rec.oprn_no
                                 AND oprn_vers = oprn_rec.oprn_vers;

                              COMMIT;
                              fnd_file.put_line (fnd_file.LOG,
                                                    'Checking Error 3'
                                                 || oprn_rec.oprn_no
                                                 || '     '
                                                 || oprn_rec.oprn_vers
                                                 || '     '
                                                 || SUBSTR (lv_msg_data,
                                                            1,
                                                            200
                                                           )
                                                );
                           END LOOP;

                           COMMIT;
                        EXCEPTION
                           WHEN OTHERS
                           THEN
                              fnd_file.put_line
                                 (fnd_file.LOG,
                                  'Error in the loop when API is not successful:'
                                 );
                        END;

                        fnd_file.put_line (fnd_file.LOG,
                                           'Debug -> Message Text Completed '
                                          );
                     ELSE
                        ln_oper_api_success := ln_oper_api_success + 1;

                        IF oprn_rec.status IS NOT NULL
                        THEN
                           ----- Call modify_Status procedure
                             ---  retrieve the operation_id
                           BEGIN
                              SELECT oprn_id
                                INTO ln_oprn_id
                                FROM gmd_operations_b
                               WHERE oprn_no = oprn_rec.oprn_no
                                 AND oprn_vers = oprn_rec.oprn_vers;
                           EXCEPTION
                              WHEN OTHERS
                              THEN
                                 fnd_file.put_line
                                          (fnd_file.LOG,
                                           'Error in operation id retrieval:'
                                          );
                           END;

                           --- Retrieve the status code
                           BEGIN
                              SELECT status_code
                               INTO lv_status_code
                                FROM gmd_status_vl
                               WHERE meaning = oprn_rec.status;
                           EXCEPTION
                              WHEN OTHERS
                              THEN
                                 fnd_file.put_line
                                           (fnd_file.LOG,
                                            'Error in status_code retrieval:'
                                           );
                           END;

                           modify_status (p_entity_name        => 'Operation',
                                          p_entity_id          => ln_oprn_id,
                                          p_status_to          => lv_status_code,
                                          x_message_count      => ln_msg_cnt,
                                          x_message_list       => lv_msg_list,
                                          x_return_status      => lv_ret_sts
                                         );
                           fnd_file.put_line (fnd_file.LOG,
                                              'Checking -- modify_Status procedure for OPERATIONS ID : ' ||ln_oprn_id
                                               ||' lv_status_code: '|| lv_status_code ||' lv_ret_sts : '||lv_ret_sts);

                           IF lv_ret_sts <> fnd_api.g_ret_sts_success
                           THEN                   --- modify_status api failed
                              BEGIN
                                 lv_error_flag := 'Y';

                                 -- ln_successful_recs := 0;
                                 UPDATE XXAKG_OPM_OPERATIONS_CONV_STG
                                    SET process_flag = 'E',
                                        error_message = lv_error_message
                                  WHERE oprn_no = oprn_rec.oprn_no
                                    AND oprn_vers = oprn_rec.oprn_vers;

                                 fnd_file.put_line
                                             (fnd_file.LOG,
                                                 'lv_error_message CHKING 123'
                                              || lv_error_message
                                             );
                              EXCEPTION
                                 WHEN OTHERS
                                 THEN
                                    fnd_file.put_line
                                       (fnd_file.LOG,
                                           'Error in the  staging table status updation:'
                                        || SQLERRM
                                       );
                              END;
                           ELSE                  --- modify_status api success
                              fnd_file.put_line (fnd_file.LOG, 'Successfully data uploaded through Operations API ....');

                              BEGIN
                                 UPDATE XXAKG_OPM_OPERATIONS_CONV_STG
                                    SET process_flag = 'P',
                                        error_message = lv_error_message
                                  WHERE oprn_no = oprn_rec.oprn_no
                                    AND oprn_vers = oprn_rec.oprn_vers;

                                 fnd_file.put_line
                                    (fnd_file.LOG,
                                        'Successfully Custom Table is updated with process_flag = P'
                                     || lv_error_message
                                    );
                                 ln_successful_recs :=
                                           ln_successful_recs + SQL%ROWCOUNT;
                              EXCEPTION
                                 WHEN OTHERS
                                 THEN
                                    fnd_file.put_line
                                       (fnd_file.LOG,
                                           'Error in the  staging table status updation:'
                                        || SQLERRM
                                       );
                              END;
                           END IF;      --- modify_status api success IF Ended
                        ELSE                       ---oprn_rec.status IS  NULL
                           fnd_file.put_line (fnd_file.LOG, 'Success Again');

                           UPDATE XXAKG_OPM_OPERATIONS_CONV_STG
                              SET process_flag = 'P',
                                  error_message = lv_error_message
                            WHERE oprn_no = oprn_rec.oprn_no
                              AND oprn_vers = oprn_rec.oprn_vers;

                           fnd_file.put_line
                              (fnd_file.LOG,
                                  'Success Again - Successfully Custom Table is updated with process_flag = P'
                               || lv_error_message
                              );
                           ln_successful_recs :=
                                           ln_successful_recs + SQL%ROWCOUNT;
                        END IF;         ----- oprn_rec.status IS NOT NULL end.
                     END IF;                              -- lv_ret_sts <> 'S'
                  END IF;                     -- IF (lv_error_flag = 'Y') THEN

                  fnd_file.put_line (fnd_file.LOG, 'Debug -> Inside Loop ');
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     fnd_file.put_line (fnd_file.LOG,
                                        'Error while calling API:' || SQLERRM
                                       );
                     fnd_file.put_line (fnd_file.LOG, lv_ret_sts);
                     fnd_file.put_line (fnd_file.LOG, ln_msg_count);

                     FOR i IN 1 .. ln_msg_count
                     LOOP
                        fnd_msg_pub.get (p_msg_index          => i,
                                         p_encoded            => 'F',
                                         p_data               => lv_msg_data,
                                         p_msg_index_out      => ln_out_index
                                        );
                     END LOOP;
               END;
            END IF;
         END LOOP;                     --oprn_rec IN cur_operations loop ended

         fnd_file.put_line (fnd_file.LOG, 'Debug -> Outside Loop ');
         ln_error_recs := ln_processed_recs - ln_successful_recs;
      END IF;                                     ---- cur_operations%NOTFOUND
   EXCEPTION                                                  --Main Exception
      WHEN OTHERS
      THEN
         lv_error_flag := 'Y';
         fnd_file.put_line
                  (fnd_file.LOG,
                      'Exception in Main Procedure while creating operation:'
                   || SQLERRM
                  );
   END operation_main;


END xxakg_opm_operation_conv_pkg;
/

Then Compile all the package
Then Executive Script.



Step 2: Register the program with package.
Registration Process:
Application Developer > Concurrent > Executable >
Executable Name            : XXAKG_OPERATIONS_CONV_PKG
Application                         : AKG Custom Application
Execution Method          : PL/SQL Procedure
Execution File Name       : xxakg_opm_operation_conv_pkg.xxakg_opm_operation_conv_prc




Program : AKG OPM Operations Conversion Program





Add this program to the Request Group for RUN the Request .
Like -- Process Engineer (Responsibility) >>
Group : OPM GMD Request Group
Add the program to the Request Group : AKG OPM Operations Conversion Program





Then go to the Responsibility
Process Engineer > View > Request > Submit a New Request  >




Then give the Program name like :  AKG OPM Operations Conversion Program






Then check the Base table
select * from GMD_OPERATIONS_B where oprn_no like '%TEST_PACK5%'  --298
select * from GMD_OPERATION_ACTIVITIES  where activity like '%PACK%' order by CREATION_DATE desc --302 -- 7

select * from GMD_OPERATION_RESOURCES  where RESOURCES like '%PACK_PACKER%' order by CREATION_DATE desc –353

Then Check from the Front End.
Negigation :

Process Engineer  > Process Operations






------------------- End of OPM OPERATION CONVERSION Program USING API -----------

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...