Thursday, June 29, 2017

Calling Request Set from PLSQL on Specific Time

CREATE OR REPLACE PROCEDURE XXAP_SUBMIT_REQUEST_SET (
P_errbuf    OUT VARCHAR2,
P_retcode   OUT NUMBER)
AS
V_REQUEST_SET_EXIST   BOOLEAN := FALSE;
req_id                INTEGER := 0;
l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
srs_failed            EXCEPTION;
submitprog_failed     EXCEPTION;
submitset_failed      EXCEPTION;
l_start_date          VARCHAR2(250);
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Calling set_request_set…');
V_REQUEST_SET_EXIST :=
FND_SUBMIT.set_request_set (application   => 'EC',
request_set   => 'JM856ASNOUTBOUND');

IF (NOT V_REQUEST_SET_EXIST)
THEN
RAISE srs_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling submit program first stage');
l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW2',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW1',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'XXJM856OUTBOUND_WLMRT',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

/*l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('XXAP',
'XXAP_FOURTH_PROGRAM',
'STAGE40');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;*/

fnd_file.put_line (fnd_file.LOG, 'Calling submit_set…');

--–l_start_date is to schedule the request
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')
  into l_start_date
FROM dual
WHERE 1                            =1
AND TO_CHAR( SYSdate,'HH24:MI:SS') > '13:00:00'
AND TO_CHAR( SYSdate,'HH24:MI:SS') < '14:30:00'
AND  TO_CHAR(SYSDATE,'DY')         IN('MON','TUE','WED','THU','FRI');

req_id :=
FND_SUBMIT.submit_set (start_time    => l_start_date,
sub_request   => FALSE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;
EXCEPTION
WHEN srs_failed
THEN
p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitprog_failed
THEN
p_errbuf := 'Call to submit_program failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitset_failed
THEN
p_errbuf := 'Call to submit_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN OTHERS
THEN
p_errbuf := 'Request set submission failed – unknown error: ' || SQLERRM;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
END;

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...