5 Replies Latest reply: Oct 24, 2011 9:41 AM by 578014 RSS

    AZR12LOADER from Command Line

    578014
      We are attempting to find a way to auto-load many ETL operations using iSetup. We have successfully found the way to upload into isetup, but we are having difficulty with the LOAD operation. We are using this PL/SQL based technique to run the load commands:

      declare
      l_request_id number := 0;
      l_resp_id number := null;
      l_app_id number := null;
      l_user_id number := null;
      begin
      select user_id into l_user_id from apps.fnd_user where user_name = 'BUILD.MANAGER';
      --GET RESPONSIBILITY
      BEGIN
      SELECT responsibility_id, RESPONSIBILITY_application_id
      into l_resp_id, l_app_id
      from apps.fnd_user_resp_groups_all
      where user_id = l_user_id
      AND RESPONSIBILITY_ID IN (SELECT MAX(RESPONSIBILITY_ID) FROM APPS.FND_RESPONSIBILITY_VL WHERE responsibility_name LIKE 'iSetup');
      EXCEPTION
      WHEN OTHERS THEN
      l_resp_id := 20420;
      l_app_id := 1;
      END;

      apps.fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);


      l_request_id := apps.fnd_request.submit_request(
           application => 'AZ',
           program => 'AZR12LOADER',
      description => 'Load Extract',
           start_time => NULL,
           sub_request => false,
      argument1 => 'LD_ECSS_CAS001_ACCTG_KFF_T_002',
      argument2 => 'L',
      argument3 => 'uEX_ECSS_CAS001_ACCTG_KFF_T',
      argument4 => 'E',
      argument5 => null,
      argument6 => null,
      argument7 => 'BUILD.MANAGER',
      argument8 => 'N',
      argument9 => null
           );

      dbms_output.put_line('request_id: ' || l_request_id);
      end;
      /

      And the results we get are:

      ---------------------------------------------------------------------------
      Application Implementation: Version : 12.0.0

      Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

      AZR12LOADER module: iSetup R12 Loader
      ---------------------------------------------------------------------------

      Current system time is 18-NOV-2009 15:37:19

      ---------------------------------------------------------------------------


      Concurrent Request Parameters
      ******************************
      RELOAD_REQUEST_ID=
      SNAPSHOT_NAME=uEX_ECSS_CAS001_ACCTG_KFF_T
      JOB_NAME=LD_ECSS_CAS001_ACCTG_KFF_T_002
      DBC_FILE_NAME=
      SNAPSHOT_REQUEST_TYPE=E
      IS_REMOTE=N
      REQUEST_TYPE=L
      USER_NAME=BUILD.MANAGER
      RELOAD_METHOD=

      Downloading the extract from central instance
      Successfully copied the Extract
      Time taken to download Extract and write as zip file = 0 seconds
      Validating Primary Extract...
      Parsing driver.xml
      Time taken to parse the Driver file and construct setup objects:1 milliseconds
      ************************************************
      All APIs have been skipped.
      ************************************************
      Concurrent program completed.

      This api for this extract is a seeded API and this load operation works fine from the iSetup user interface.

      Is there a way to run this from pl/sql or from the command line, so that it will work just like it does when called from the user interface?
        • 1. Re: AZR12LOADER from Command Line
          400672
          Here is way to workaround this problem, Pls. use it with your own discretion. This is not officially supported by Development.

          Steps for Loading zip files using AZR12LOADER_

          1. Use the “AZR12UPLOADFROMDIR” concurrent program to upload the zip file as an extract in the instance where the zip file is to be loaded.

          Syntax:

          CONCSUB <DB USER>/<DB PASSWD> AZ 'Oracle iSetup' <APPS USER NAME> CONCURRENT AZ AZR12UPLOADFROMDIR <EXTRACT JOB NAME> 'E' <ZIP FILE TO BE UPLOADED>

          Example:
          CONCSUB apps/apps AZ 'Oracle iSetup' WIZARD CONCURRENT AZ AZR12UPLOADFROMDIR '"Extract Job"' 'E' '/home/share/Currencies.zip'

          Note:

          • Extract job Name must be unique across all the extracts for that user. Also, every time the command is run, a new job name has to be used.
          • However, if the same extract is to be loaded on the same instance (an unlikely scenario), this step needs to be executed only for the first time.


          2. A row is to be inserted in the az_requests table of DB corresponding to the load request.

          Example:

          INSERT INTO az_requests(job_name, request_type, user_id, request_id, instance_name, job_desc, previous_req_ids, request_phase, request_status, submission_date, completion_date, content, created_by, creation_date, last_updated_by, last_update_date, last_update_login, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, scheduled, driver_file, selection_set) VALUES('Load Job', 'L', 7, 987654, 'source', NULL, NULL, 'C', 'C', sysdate, sysdate, NULL, 7, sysdate, 7, sysdate, 7, NULL, NULL, NULL, 'E', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

          Note:

          • This step has to be executed for the first time. However, there are a few cases where the customer might want to execute this every time:

          o The customer wants to track their loads using different job names. Insert 1 row for each load.
          o The customer wants to run more than 1 load in parallel. Here also, 1 row has to be inserted for each parallel task.

          • The customer has to ensure that the JOB_NAME is unique among loads for a given user.
          • The User Id used in this step should correspond to the Id of the user used in step1.


          3. The “AZR12LOADER” is run to complete the load.

          Syntax:

          CONCSUB apps/apps AZ 'Oracle iSetup' WIZARD CONCURRENT AZ AZR12LOADER '"<Load Job>"' 'L' '"<Extract Job>"' 'E' '""' '""' 'WIZARD' 'N' '""'

          Example:

          CONCSUB apps/apps AZ 'Oracle iSetup' WIZARD CONCURRENT AZ AZR12LOADER '"Load Job"' 'L' '" Extract Job"' 'E' '""' '""' 'WIZARD' 'N' '""'

          Note:

          • The Load Job Name used here should be the same as the job name inserted in the az_requests table in Step 2. Please refer the job name highlighted in the examples.
          • The Extract Job Name used here should be the same as the job name used to run the uploader concurrent program in step 1. Please refer the job name highlighted in the examples.
          • The same user should be used for all the steps.



          Disclaimer:

          This is provided for information purposes only, and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor is it subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose.

          We specifically disclaim any liability with respect to this note, and no contractual obligations are formed either directly or indirectly by this note.
          • 2. Re: AZR12LOADER from Command Line
            461903
            Hi,

            While using eBS 12.1.2 I can confirm that step 1 is OK.

            Step 2 raises some questions:
            - Where is this request_id belonging to ??. It should be the request_id from the Conc Request in step 3 ??
            - source is the SID of the environment ??
            - param1; is this the parameter in which the name of the extract job should be ??
            - what would be the driver_file and the selection_set ??

            Step 3 is NOK if step 2 is unclear !

            If I manully run a load from an upload I can see 1 new record in az_requests (and two conc requests in FND).

            If someone has some interesting comments: please reply

            Guido
            • 3. Re: AZR12LOADER from Command Line
              640021
              #1. Yes, Step 1 is required in 12.1.2.
              #2.
              2.1) Yes, request_id of step no 3 is required in step 2. You can insert some unique values to request_id column in step 2 and then update it with request_id you get in step no 3.
              2.2) Yes, you can use SID
              2.3) Yes. EXTRACT JOB NAME in step 1.
              2.4) You can keep them as null.
              • 4. Re: AZR12LOADER from Command Line
                578014
                I know its been a while since I originally posted this question, but we just picked up this effort again, and I am following this procedure exactly, and I am still getting the "All APIs have been skipped." message as a result. Is there any additional knowledge, or perhaps an improved method to run AZR12LOADER from an external program? Perhaps a method to manually set up an proper az context, and run the java code itself?

                In any case, here is my results log on the Load attempt now. As you can see, it looks like it is properly finding the extract file, copying it, and parsing it, but then it is skipping APIs. These files are using standard, proven APIs that exist, and will work on a user-initiated load from the iSetup client.

                I'm wondering if in step 2, we might need to pre-populate the selection_set and driver_file fields.

                Any thoughts would be greatly appreciated.

                --Gary

                ---------------------------------------------------------------------------
                Application Implementation: Version : 12.0.0

                Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

                AZR12LOADER module: iSetup R12 Loader
                ---------------------------------------------------------------------------

                Current system time is 24-OCT-2011 14:05:09

                ---------------------------------------------------------------------------


                Concurrent Request Parameters
                ******************************
                RELOAD_REQUEST_ID=
                SNAPSHOT_NAME=uEX_CMA001_FUNC_AOL001_000000_R1PC100
                JOB_NAME=LD_CMA001_FUNC_AOL001_000000_R1PC100
                DBC_FILE_NAME=
                SNAPSHOT_REQUEST_TYPE=E
                IS_REMOTE=N
                REQUEST_TYPE=L
                USER_NAME=MYUSERNAME
                RELOAD_METHOD=

                Downloading the extract from central instance
                Successfully copied the Extract
                Time taken to download Extract and write as zip file = 0 seconds
                Validating Primary Extract...
                Parsing driver.xml
                Time taken to parse the Driver file and construct setup objects:2 milliseconds
                ************************************************
                All APIs have been skipped.
                ************************************************
                Concurrent program completed.
                ---------------------------------------------------------------------------
                Start of log messages from FND_FILE
                ---------------------------------------------------------------------------
                ---------------------------------------------------------------------------
                End of log messages from FND_FILE
                ---------------------------------------------------------------------------
                • 5. Re: AZR12LOADER from Command Line
                  578014
                  Never mind. I just altered the insert command in step 2, and added the selection_set an driver_file data found in the uploaded 'E' record, to the inserted 'L' record, and the Load operation worked successfully.

                  This question is now answered successfully.