Procedure : Waiting for child concurrent program to complete
PROCEDURE wait_for_program_completion (p_req_id IN OUT NUMBER) IS
l_success BOOLEAN;
l_phase VARCHAR2 (80); -- phase displyed on screen
l_status VARCHAR2 (80); -- status displayed on screen
l_dev_phase VARCHAR2 (80); -- phase available for developer
l_dev_status VARCHAR2 (80); -- status available for developer
l_err_msg VARCHAR2 (255);
l_req_submitted BOOLEAN;
BEGIN
IF p_req_id <> 0 THEN
l_req_submitted :=
fnd_concurrent.get_request_status
(p_req_id -- Request ID
, NULL
, NULL
, l_phase--Phase displyed on screen
, l_status--Status displayed on screen
, l_dev_phase--Phase for developer
, l_dev_status--Status for developer
, l_err_msg
); -- Execution Message
-- note check Request Status --
WHILE (l_dev_phase != 'COMPLETE') LOOP
l_success :=
fnd_concurrent.wait_for_request
(p_req_id -- Request ID
, 20 --0.5 Mins -- Time Interval
, 1800 --30 Mins -- Total Time to wait
, l_phase -- Phase displyed on screen
, l_status -- Status displayed on screen
, l_dev_phase -- Phase for developer
, l_dev_status -- Status for developer
, l_err_msg
); -- Execution Message
EXIT WHEN (l_dev_status = 'COMPLETE' OR l_success = FALSE);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END wait_for_program_completion;
SELECT main_prog.description Module
, fcr.hold_flag hold
, fcr.phase_code phase
, fcr.status_code status
, fcr.request_id
, fcr.parent_request_id parent_req_id
, TRUNC( ( NVL( fcr.actual_completion_date, SYSDATE ) - fcr.actual_start_date ) * 24 * 60, 2 ) time_in_mins
, fu.user_name
, fcpl.user_concurrent_program_name conc_program_name
, frl.responsibility_name
, fcr.requested_start_date req_start_date
, TO_CHAR( fcr.actual_start_date, 'DD-MON-YYYY HH:MI AM' ) start_date
, TO_CHAR( fcr.actual_completion_date, 'DD-MON-YYYY HH:MI AM' ) completion_date
, TRUNC( fcr.actual_start_date - fcr.requested_start_date, 2 ) kick_off_time
, fcrs.completion_text
, fcr.argument_text
, fcr.printer
, fcp.concurrent_program_id||'-'||fcp.concurrent_program_name conc_prog_id_code
, fcp.executable_id ||'-' ||fe.execution_method_code||'-'||fe.executable_name ID_CODE_NAME
, fe.execution_file_name
, fl.meaning execution_type
, fe.execution_file_path
, fcr.has_sub_request
, fcr.resubmitted
, fcr.resubmit_interval
, fu.description user_full_name
, fcr.outfile_name
, fcr.logfile_name
FROM apps.fnd_executables fe
, apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpl
, apps.fnd_concurrent_requests fcr
, apps.fnd_lookups fl
, apps.fnd_responsibility_tl frl
, apps.fnd_user fu
, apps.fnd_conc_req_summary_v fcrs
, apps.fnd_lookups main_prog
WHERE fe.executable_id = fcp.executable_id
AND fcp.execution_method_code = fe.execution_method_code
AND fcr.responsibility_application_id = frl.application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND frl.responsibility_id = fcr.responsibility_id
AND fu.user_id = fcr.requested_by
AND fl.lookup_code = fe.execution_method_code
AND fcrs.concurrent_program_id = fcr.concurrent_program_id
AND fcrs.request_id = fcr.request_id
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND frl.language = 'US'
AND fcpl.language = 'US'
AND fcp.concurrent_program_name = main_prog.meaning(+)
AND main_prog.lookup_type(+) like 'REPORTERR'
--and fu.user_name = 'USER-IF-REQUIRED'
--and fcr.argument_text like '%Argument Content%'
--AND fcpl.user_concurrent_program_name like '%Any Program Name if Any%'
and fcr.actual_start_date <= to_date('08-Mar-2013 09:40:00','DD-MON-YYYY HH24:MI:SS')--To Time
and nvl(fcr.actual_completion_date,sysdate) >= to_date('08-Mar-2013 09:30:00','DD-MON-YYYY HH24:MI:SS') --From Time
ORDER BY fcr.actual_start_date desc,request_id desc
--*************************************************************************
--Query for getting GL, AP, AR, FA and PO Period Statuses
--Pass Period or Ledger ID for selected data; otherwise all statuses will appear as per setup.
--*************************************************************************
SELECT ( SELECT sob.name
FROM apps.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id )
"SOB_Name"
, a.set_of_books_id ledger_id
, a.period_name "Period_Name"
, a.period_num "Period_Num"
, a.gl_status "GL_Status"
, b.po_status "PO_Status"
, c.ap_status "AP_Status"
, d.ar_status "AR_Status"
, e.fa_status "FA_Status"
--, f.inv_status "INV_Status"
FROM (SELECT period_name
, period_num
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) gl_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101)a-- AND UPPER( period_name ) = UPPER( :period_name ) )a
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) po_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201)b-- AND UPPER( period_name ) = UPPER( :period_name ))b
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) ap_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200)c-- AND UPPER( period_name ) = UPPER( :period_name ))c
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) ar_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222)d-- AND UPPER( period_name ) = UPPER(:period_name ) )d
, (SELECT fdp.period_name, DECODE( fdp.period_close_date, NULL, 'Open', 'Closed' ) fa_status, fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.book_type_code = fdp.book_type_code
--AND UPPER( fdp.period_name ) = UPPER( nvl(:period_name,fdp.period_name) )
) e
, (SELECT period_name
, DECODE( closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) inv_status
, set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 401)f-- AND UPPER( period_name ) = UPPER(:period_name ) )d
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.period_name = f.period_name(+)
AND a.set_of_books_id = b.set_of_books_id(+)
AND a.set_of_books_id = c.set_of_books_id(+)
AND a.set_of_books_id = d.set_of_books_id(+)
AND a.set_of_books_id = e.set_of_books_id(+)
AND a.set_of_books_id = f.set_of_books_id(+)
AND a.set_of_books_id = nvl(:p_ledger_id,a.set_of_books_id)
AND a.period_name = upper(nvl(:period_name,a.period_name))
AND substr(a.period_name,1,1)||substr(a.period_name,5,2)||substr(a.period_name,2,2) >= 'P01-12'
ORDER BY 1,2, substr(a.period_name,1,1)||substr(a.period_name,5,2)||substr(a.period_name,2,2)
