Rajesh Somisetti: December 2014

CREATE OR REPLACE PROCEDURE apps.xx_report_prc (
x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_report IN VARCHAR2,
p_from_xx_no IN VARCHAR2,
p_to_xx_no IN VARCHAR2
)
AS
l_resp_appl_id fnd_application.application_id%TYPE:= fnd_global.resp_appl_id;
l_resp_id fnd_responsibility.responsibility_id%TYPE:= fnd_global.resp_id;
l_user_id fnd_user.user_id%TYPE := fnd_global.user_id;
v_request_id VARCHAR2 (100);
p_return_code NUMBER;
p_return_msg VARCHAR2 (100);
p_report_type VARCHAR2 (100);
l_count NUMBER := 0;
l_status VARCHAR2 (10);
v_file_loc BFILE;
v_pdf_file_loc BLOB;
v_pdf_file_size INTEGER;
l_file VARCHAR2 (240);
------------------------------------------------
l_set_layout BOOLEAN;
l_messase VARCHAR2 (240);
l_request_id NUMBER;
l_phase VARCHAR2 (100);
l_status1 VARCHAR2 (100);
l_dev_phase VARCHAR2 (100);
l_dev_status VARCHAR2 (100);
l_wait_for_request BOOLEAN := FALSE;
l_get_request_status BOOLEAN := FALSE;
output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
l_phase_code VARCHAR2 (12);

CURSOR c1
IS
SELECT h.*
FROM xx_custom_table h;
BEGIN
FOR i IN c1
LOOP
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('count' || l_count);
fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
COMMIT;

BEGIN
l_set_layout :=
fnd_request.add_layout
(template_appl_name => 'XX',
template_code => 'XXSTER', --Data Template Code
template_language => 'en',
template_territory => 'US',
output_format => 'PDF'
);

IF l_set_layout
THEN
v_request_id :=
fnd_request.submit_request
(application => 'XX',
program => 'XXSTER', ---Concurrent program Short Name
description => 'XX Output Report',
start_time => SYSDATE,
sub_request => NULL, -- Pass the Concurrent Program Parameters
argument1 => i.organization_id, -- Organization
argument2 => p_report_type, -- REPORT TYPE
argument3 => i.xx_ref_no, -- Document No fROM.
argument4 => i.xx_ref_no, -- Document No To.
argument5 => NULL, -- Email ID
argument6 => 'FTP', -- Report Submitted From
argument7 => NULL,
argument8 => NULL,
argument9 => NULL,
argument10 => NULL,
argument11 => NULL
);
p_return_msg := 'Request submitted.ID=' || v_request_id;
fnd_file.put_line (fnd_file.LOG, 'request_id' || p_return_msg);
DBMS_OUTPUT.put_line ('Request Id ' || p_return_msg);
p_return_code := 0;
DBMS_OUTPUT.put_line ('RETURN CODE' || p_return_code);
COMMIT;

IF v_request_id > 0
THEN
DBMS_OUTPUT.put_line ('request_id >0');

LOOP
l_wait_for_request :=
fnd_concurrent.wait_for_request
(request_id => v_request_id,
INTERVAL => 5,
max_wait => 0,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
MESSAGE => l_messase
);

BEGIN
SELECT phase_code
INTO l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = v_request_id;
EXCEPTION
WHEN OTHERS
THEN
l_phase_code := 'X';
END;

EXIT WHEN ( l_phase_code = 'C'
OR l_phase_code = 'X'
OR l_phase_code = 'I'
);
END LOOP;

BEGIN
SELECT 'X'
INTO l_status
FROM fnd_concurrent_requests
WHERE request_id = v_request_id
AND phase_code = 'C'
AND status_code = 'C';
EXCEPTION
WHEN OTHERS
THEN
l_status := NULL;
END;

DBMS_OUTPUT.put_line ('l_status ' || l_status);

IF l_status = 'X'
THEN
DBMS_OUTPUT.put_line ('file inseration ');

BEGIN
SELECT SUBSTR (file_name, INSTR (file_name, '/', -1) + 1)
INTO l_file
FROM apps.fnd_conc_req_outputs
WHERE concurrent_request_id = v_request_id;
EXCEPTION
WHEN OTHERS
THEN
l_file := NULL;
END;

BEGIN
v_file_loc := BFILENAME ('XX_OUTPUT_FILE', l_file);---- XX_OUTPUT_FILE is directory and l_file is file name
DBMS_LOB.fileopen (v_file_loc, DBMS_LOB.file_readonly);-- it is mandatory it is abled to opened the file
v_pdf_file_size := DBMS_LOB.getlength (v_file_loc);
DBMS_OUTPUT.put_line ('PDF file size: '
|| v_pdf_file_size
); --v_diagram_size
DBMS_OUTPUT.put_line ('Inserting PDF file size Row');

INSERT INTO xx_table
(request_id, pdf_out_put_file,
file_name,
organization_id, file_type,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, status
)
VALUES (v_request_id, EMPTY_BLOB,
i.xx_ref_no
|| '_'
|| (TO_CHAR (SYSDATE,
'DD-MON-YYYY_hh24_mi_ss'
)
)
|| '.pdf',
i.organization_id, p_report_type,
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
fnd_global.user_id, 'N'
)
RETURNING pdf_out_put_file
INTO v_pdf_file_loc;

DBMS_OUTPUT.put_line ('Loading PDF From File');
DBMS_LOB.loadfromfile (v_pdf_file_loc,
v_file_loc,
v_pdf_file_size
);
COMMIT;
DBMS_LOB.filecloseall;---it is not mandatory if you are not use the command the lob file is not closed it is able to processed upto 10 records only

UPDATE xx_custom_table xec
SET attribute2 = 'Y'
WHERE xx_ref_no = i.xx_ref_no;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'insert stmt Exception '
|| SQLCODE
|| SQLERRM
);
DBMS_OUTPUT.put_line ( 'OTHERS Exception '
|| SQLCODE
|| SQLERRM
);
END;
ELSE
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE output_layout_failed;
END IF;
EXCEPTION
WHEN output_layout_failed
THEN
fnd_file.put_line (fnd_file.LOG, 'Out put Layout failed');
WHEN request_submission_failed
THEN
fnd_file.put_line (fnd_file.LOG,
'Concurrent request submission failed'
);
WHEN request_completion_abnormal
THEN
fnd_file.put_line (fnd_file.LOG,
'Submitted request completed with error'
|| l_request_id
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR:' || SUBSTR (SQLERRM, 0, 240)
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
p_return_msg :=
'REQUEST SET SUBMISSION FAILED -UNKNOWN ERROR:' || SQLERRM;
p_return_code := 2;
fnd_file.put_line (fnd_file.LOG, 'Exception error msg' || p_return_msg);
fnd_file.put_line (fnd_file.LOG, 'return code' || p_return_code);
END;

Rajesh Somisetti

Wednesday 17 December 2014

ora-22290: operation would exceed the maximum number of opened files or lobs

Tuesday 16 December 2014

How to insert report output file into a table .

Tuesday 2 December 2014

Rows to columns conversion: