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.