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.
No comments:
Post a Comment