Upload Lookup Values

--- API to upload data into FND_LOOKUP_VALUES in R12
DECLARE
    v_error   VARCHAR2(4000);
    CURSOR get_lookuptype_details IS
    SELECT
        ltype.application_id,
        ltype.customization_level,
        ltype.creation_date,
        ltype.created_by,
        ltype.last_update_date,
        ltype.last_updated_by,
        ltype.last_update_login,
        tl.lookup_type,
        tl.security_group_id,
        tl.view_application_id,
        tl.description,
        tl.meaning
    FROM
        fnd_lookup_types_tl   tl,
        fnd_lookup_types      ltype
    WHERE
        ltype.lookup_type = 'XX_LOOKUP_TYPE'
        AND ltype.lookup_type = tl.lookup_type
            AND language = 'US';

    CURSOR get_lookcode_details IS
    SELECT DISTINCT
        lookup_code,
        meaning,
        description
    FROM
        xx_lookup_values
      --WHERE LOOKUP_CODE = '1'
     -- WHERE upload_status <> 'DONE'
        ;

    l_rowid   VARCHAR2(100) := 0;
BEGIN
    FOR i IN get_lookuptype_details LOOP
        FOR j IN get_lookcode_details LOOP
            l_rowid := NULL;
            BEGIN
                fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid, x_lookup_type => i.lookup_type, x_security_group_id => i.security_group_id
                , x_view_application_id => i.view_application_id, x_lookup_code => j.lookup_code,
                                                 x_tag => '', x_attribute_category => NULL, x_attribute1 => NULL, x_attribute2 =>
                                                 NULL, x_attribute3 => NULL,
                                                 x_attribute4 => NULL, x_enabled_flag => 'Y', x_start_date_active => NULL, x_end_date_active
                                                 => NULL, x_territory_code => NULL,
                                                 x_attribute5 => NULL, x_attribute6 => NULL, x_attribute7 => NULL, x_attribute8 =
                                                 > NULL, x_attribute9 => NULL,
                                                 x_attribute10 => NULL, x_attribute11 => NULL, x_attribute12 => NULL, x_attribute13
                                                 => NULL, x_attribute14 => NULL,
                                                 x_attribute15 => NULL, x_meaning => j.meaning, x_description => j.description, x_creation_date
                                                 => sysdate, x_created_by => i.created_by,
                                                 x_last_update_date => i.last_update_date, x_last_updated_by => i.last_updated_by
                                                 , x_last_update_login => i.last_update_login);

                COMMIT;
                UPDATE xx_lookup_values
                SET
                    upload_status = 'DONE'
                WHERE
                    lookup_code = j.lookup_code;

            EXCEPTION
                WHEN OTHERS THEN
                    v_error := ( sqlerrm );
                    UPDATE xx_lookup_values
                    SET
                        upload_status = 'Inner Exception: ' || v_error
                    WHERE
                        lookup_code = j.lookup_code;

            END;

        END LOOP;
    END LOOP;
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR  WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;

SELECT
    COUNT(*)
FROM
    fnd_lookup_values
WHERE
    lookup_type = 'XX_LOOKUP_TYPE'
    AND language = 'US'

-------------------------------custom table create------------------------------

--drop table XX_LOOKUP_VALUES
CREATE TABLE xx_lookup_values (
    lookup_code     VARCHAR2(50 BYTE),
    meaning         VARCHAR2(200 BYTE),
    description     VARCHAR2(200 BYTE),
    upload_status   VARCHAR2(2000 BYTE)
)

Concurrent Programs

1. PLSQL Based Concurrent Programs:


CREATE OR REPLACE PACKAGE xxcust_pkg AS

   --     Script Name   : 
   --     Created by    :  Abhishek Agrawal
   --     Creation date : 
   --     Purpose       : 
   --     Program History :
   --     Date                Version           Author  Description
   --     ----------------------------------------------------------
   --
   -- **************************************************************
   -----------------------------------------------------------------
    PROCEDURE main (
        x_errbuf       OUT   VARCHAR2,
        x_retcode      OUT   NUMBER,
        p_org_id       IN    NUMBER,
        p_start_date   IN    VARCHAR2,
        p_end_date     IN    VARCHAR2
    );

END xxcust_pkg;

create or replace PACKAGE BODY xxcust_pkg AS
   --     Date                Version           Author               Description
   --     ----------------------------------------------------------------------
   -- **************************************************************************
   -----------------------------------------------------------------------------
    PROCEDURE main (
    x_errbuf           OUT   VARCHAR2,
    x_retcode          OUT   NUMBER,
    p_org_id           IN    NUMBER,
    p_start_date          IN    varchar2,
    p_end_date            in    varchar2
    )
    AS
    ld_start_date      DATE;
    ld_end_date        DATE;
    BEGIN

      fnd_file.put_line (fnd_file.log, '************     PARAMETERS    *******');
      fnd_file.put_line (fnd_file.log, 'p_org_id -  '     || p_org_id);
      fnd_file.put_line (fnd_file.log, 'p_start_date -  ' || p_start_date);
      fnd_file.put_line (fnd_file.log, 'p_end_date- '     || p_end_date);
      fnd_file.put_line (fnd_file.log, '**************************************');
      ld_start_date := fnd_date.canonical_to_date(p_start_date);
      ld_end_date   := fnd_date.canonical_to_date(p_end_date);

    EXCEPTION
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.log,' MAIN error'|| ' - '|| sqlerrm);
    end main;

end xxcust_pkg;


Define Concurrent Program:
p_OrgID: Value : Default: Profile: ORG_ID
dates: value set: fnd_standard_date


2. Host Based Programs

#---------------------------------------------------------------------------
#Program Name   :
#REM $Header    :
#Program Type   : Shell Script
#File Name      :
#---------------------------------------------------------------------------
#Revision:
#Ver        Date                    Author              Description
#
#---------------------------------------------------------------
#Submitting a concurrent program to run generate the extract and insert it into staging table
# from LASR. These variables are $0-$6 respectively.
# $0 - Shellscript name
# $1 - Oracle username/password
# $2 - User_id
# $3 - LASR(Oracle Applications) username
# $4 - Concurrent request id
# $5 - Parameter 1 (start date)
# $6 - Parameter 2 (end date)

echo "1. appsUserP : "$1
echo "2. UserId    : "$2
echo "3. UserName  : "$3
echo "4. RequestID : "$4
echo "5. Param 1   : "$5
echo "6. ParamDt 2 : "$6
echo "7. ParamDt 3 : "$7
echo "8. RespKey   : "$8
echo "9. ApplShortName : "$9

#Calculate Dt Param
start_date=`echo $6 | cut -f1 -d" "`
end_date=`echo $7 | cut -f1 -d" "`
echo $start_date
echo $end_date

echo"Submitting a concurrent program to run generate the extract and insert it into staging table"
v_conc_request=`CONCSUB $1 ${9} '"'$8'"' $3  WAIT = 10 CONCURRENT <XXCUST> <CP_SHORT_NAME> $5 $start_date $end_date`

echo "v_conc_req: "$v_conc_request
v_conc_id=`echo $v_conc_request | cut -f3 -d" "`
echo "v_conc_id: "$v_conc_id

3. Submit a Program from PLSQL

DECLARE
                l_org_id                  VARCHAR2(10);
                l_request_id              NUMBER(15) := 0;
                l_rec_status_cnt          VARCHAR2(100) := 'X';
                l_user_id                 fnd_user.user_id%TYPE;
                l_resp_id                 fnd_responsibility_tl.responsibility_id%TYPE;
                l_resp_appl_id            fnd_responsibility_tl.application_id%TYPE;
                l_error_message           VARCHAR2(2000 BYTE);
                p_result                  VARCHAR2(10);
                lv_req_phase              VARCHAR2(200);
                lv_req_status             VARCHAR2(200);
                lv_req_dev_phase          VARCHAR2(200);
                lv_req_dev_status         VARCHAR2(200);
                lv_req_message            VARCHAR2(200);
                lv_req_return_status      BOOLEAN;
                l_chr_message_data        VARCHAR2(200);
                l_chr_ret_status          VARCHAR2(200);

begin


     BEGIN
         SELECT user_id INTO l_user_id FROM apps.fnd_user WHERE user_name = '<USER_NAME>';
     EXCEPTION
         WHEN OTHERS THEN dbms_output.put_line('l_user_id: ERROR'|| ':'|| sqlerrm);
     END;

     BEGIN
         SELECT application_id, responsibility_id INTO l_resp_appl_id, l_resp_id
         FROM fnd_responsibility_tl
         WHERE responsibility_name = '<responsibility_name>';

     EXCEPTION
         WHEN OTHERS THEN dbms_output.put_line('l_resp_appl_id: ERROR'|| ':'|| sqlerrm);
     END;

     IF ( l_user_id IS NOT NULL AND l_resp_id IS NOT NULL AND l_resp_appl_id IS NOT NULL ) THEN
         fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);
         p_result := 'TRUE';
     ELSE
         p_result := 'FALSE';
     END IF;

     dbms_output.put_line('p_result:'|| ':'|| p_result);


IF p_result = 'TRUE' THEN
    dbms_output.put_line('l_user_id: ERROR'|| ':'|| sqlerrm);
    dbms_output.put_line('Submitting Concurrent Request: ****');
    BEGIN
        l_request_id := fnd_request.submit_request(application => 'XXCUST', program => 'CCXUST_CONC_PROG_SHORT_NAME', argument1=> p_param1);
        COMMIT;
        dbms_output.put_line('Submitting Concurrent Request: l_request_id ****'|| l_request_id|| fnd_message.get|| '".');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception Concurrent Request: l_request_id ****'|| l_request_id|| sqlerrm);
    END;

    dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || l_request_id);
   
    --WAIT FOR REQ
    IF l_request_id = 0 THEN
        dbms_output.put_line('Submitting Concurrent Request: l_request_id eRR****' || l_request_id);
    END IF;
    IF ( l_request_id > 0 ) THEN
        LOOP
       
        --function wait_for_request(request_id IN number default NULL,
        --  interval   IN  number default 60,
        --  max_wait   IN  number default 0,
        --  phase      OUT NOCOPY varchar2,
        --  status     OUT NOCOPY varchar2,
        --  dev_phase  OUT NOCOPY varchar2,
        --  dev_status OUT NOCOPY varchar2,
        --  message    OUT NOCOPY varchar2) return  boolean;
         
            lv_req_return_status := fnd_concurrent.wait_for_request
            (l_request_id,
             60,  --interval IN SECOND
             0,   -- max_wait
             lv_req_phase,
             lv_req_status,
             lv_req_dev_phase,
             lv_req_dev_status,
             lv_req_message
             );

            EXIT WHEN upper(lv_req_phase) = 'COMPLETED' OR upper(lv_req_status) IN (
                'CANCELLED',
                'ERROR',
                'TERMINATED'
            );

        END LOOP;

        IF upper(lv_req_phase) = 'COMPLETED' AND upper(lv_req_status) = 'ERROR' THEN
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'FAILED');
        ELSIF upper(lv_req_phase) = 'COMPLETED' AND upper(lv_req_status) = 'NORMAL' THEN
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'COMPLETED');
        ELSE
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'ERROR');
        END IF;

    END IF; --l_request_id > 0

END IF; --P_RESULT

END;

END;


4. Submit a Concurrent Program from Host Program

5.

Calling a HTTP and HTTPS using PLSQL using oracle wallet

Steps: 
Installed oracle xe 18c on windows 64 bit
Oracle developer suite 10g
Oracle Wallet Manager
Website Refer:

http://jsonplaceholder.typicode.com/posts/1
https://jsonplaceholder.typicode.com/posts/1
Refer: https://oracle-base.com/articles/misc/utl_http-and-ssl


--step01 from sys
create user web_user IDENTIFIED BY web_user;
grant execute on utl_http to web_user;
grant create procedure to web_user;
grant create session to web_user;

step02 create acl
--run from sys
--acl => web_user_all_open.xml

BEGIN
    dbms_network_acl_admin.create_acl(
             acl            => 'web_user_all_open.xml'
            ,description    => 'web user acl blanket access'
            ,principal      => 'WEB_USER'
            ,is_grant       => true
            ,privilege      => 'connect'
            ,start_date     => systimestamp
            ,end_date       => NULL
            );
    commit;
END;

-----

BEGIN
    dbms_network_acl_admin.assign_acl(acl => 'web_user_all_open.xml',host => '*',lower_port => 1,upper_port => 9999);
    COMMIT;
END;

--
SELECT * FROM dba_network_acls;
SELECT * FROM dba_network_acl_privileges;

step03-- create a procedure

 CREATE OR REPLACE PROCEDURE show_html_from_url (
    p_url        IN VARCHAR2,
    p_username   IN VARCHAR2 DEFAULT NULL,
    p_password   IN VARCHAR2 DEFAULT NULL
) AS
    l_http_request    utl_http.req;
    l_http_response   utl_http.resp;
    l_text            VARCHAR2(32767);
BEGIN
  -- Make a HTTP request and get the response.
    l_http_request := utl_http.begin_request(p_url);

  -- Use basic authentication if required.
    IF
        p_username IS NOT NULL AND p_password IS NOT NULL
    THEN
        utl_http.set_authentication(l_http_request,p_username,p_password);
    END IF;

    l_http_response := utl_http.get_response(l_http_request);

  -- Loop through the response.
    BEGIN
        LOOP
            utl_http.read_text(l_http_response,l_text,32766);
            dbms_output.put_line(l_text);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN
            utl_http.end_response(l_http_response);
    END;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('exception'||sqlerrm);
        utl_http.end_response(l_http_response);
        RAISE;
END show_html_from_url;
/
  
step04
SET SERVEROUTPUT ON;

BEGIN
    show_html_from_url('http://jsonplaceholder.typicode.com/posts/1');
END;

--{
--  "userId": 1,
--  "id": 1,
--  "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--  "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--

--PL/SQL procedure successfully completed.

step05

SET SERVEROUTPUT ON;

BEGIN
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/

--exceptionORA-29273: HTTP request failed
--
--
--Error starting at line : 3 in command -
--BEGIN
--    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
--END;
--Error report -
--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-29024: Certificate validation failure
--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 2
--29273. 00000 -  "HTTP request failed"
--*Cause:    The UTL_HTTP package failed to execute the HTTP request.
--*Action:   Use get_detailed_sqlerrm to check the detailed error message.
--           Fix the error and retry the HTTP request.


-- we need to do the oracle wallet manager:
--https://appsoracle-abhi.blogspot.com/2020/01/create-oracle-wallet-in-windows.html
--create wallet
--F:\DevSuiteHome_1\BIN>mkstore -wrl "C:\Users\abhishek\tmp_w" -create
--Enter password:
--add cerification in wallet
--F:\DevSuiteHome_1\BIN>orapki wallet add -wallet "C:\Users\abhishek\tmp_w" -trusted_cert -cert "F:\git-hub-repos\learnings\adv sql\plsql_steven feuerstein\ACL_HTTP_CHTTP\AddTrustExternalCARoot.crt"
--Enter wallet password:
--

refer links above for how to download the AddTrustExternalCARoot.crt file for a url

07: calling a https service or http over ssl
SET SERVEROUTPUT ON;

BEGIN
    UTL_HTTP.set_wallet('file:C:\Users\abhishek\tmp_w', 'abc@123');
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/

--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-28759: failure to open file--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 3
--29273. 00000 -  "HTTP request failed"
--*Cause:    The UTL_HTTP package failed to execute the HTTP request.
--*Action:   Use get_detailed_sqlerrm to check the detailed error message.
--           Fix the error and retry the HTTP request.

--resolution
--Wallet Manager set permissions on the files, so they can only be read by the user who created them. Either modify the permissions or copy the files to another location.
--And then in your "set_wallet" reference the new location.
--I copied the wallet file from above location to another location, and in windows right click and unchecked read only on folders containting the the ewallet.p12 file.

SET SERVEROUTPUT ON;

BEGIN
    UTL_HTTP.set_wallet('file:F:\wallet\tmp_w', 'abc@123');
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
--    exception when others then
--    dbms_output.put_line('err'||sqlerrm);
END;
/
--
--{
--  "userId": 1,
--  "id": 1,
--  "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--  "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--
--
--PL/SQL procedure successfully completed.

REFER:
1. https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
2. https://oracle-base.com/articles/misc/utl_http-and-ssl
3. https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/
4. https://jsonplaceholder.typicode.com/
5.https://www.nyquest.com/showpage/making-api-calls-from-oracle
6.https://www.dbasolutions.in/2017/02/understanding-dbmsnetworkacladmin-with.html
7.https://anjo.pt/wp/keyword-oracle/2019/09/