Package Spec:
Package Body:
CREATE OR REPLACE PACKAGE APPS.XX_CHECKS_EXTRACT_PKG
AS
PROCEDURE LOG(
p_msg IN VARCHAR2);
PROCEDURE export_checks_details(
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER );
END ;
/
CREATE OR REPLACE PACKAGE BODY APPS.xx_checks_extract_pkg
AS
g_conc_success NUMBER := 0;
g_conc_warning NUMBER := 1;
g_conc_error NUMBER := 2;
PROCEDURE LOG(
p_msg IN VARCHAR2)
IS
l_msg VARCHAR2 (32000) := TO_CHAR (SYSTIMESTAMP, 'HH.MI.SS.FF') || '-' || p_msg;
BEGIN
fnd_file.put_line (fnd_file.LOG, l_msg);
END LOG;
PROCEDURE export_checks_details(
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER)
AS
/*-- Cursor to get the Check Details --*/
CURSOR c_check_details
IS
SELECT ai.attribute10 voucher_number,
ac.check_number check_number,
TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,
aip.invoice_id client_voucher_number,
ac.currency_code currency_code,
--AT TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,
TO_CHAR (ai.terms_date, 'YYYYMMDD') pymnt_schd_date,
--ac.amount CHECK_AMOUNT,
ai.invoice_amount check_amount,
ac.check_id,
ac.status_lookup_code
FROM ap_invoices_all ai,
ap_checks_all ac,
ap_invoice_payments_all aip
WHERE aip.invoice_id = ai.invoice_id
AND ai.attribute10 IS NOT NULL
AND ac.check_id = aip.check_id
AND ac.status_lookup_code = 'NEGOTIABLE';
-- and rownum <5;
CURSOR c_check_details1
IS
SELECT ai.attribute10 voucher_number,
ac.check_number check_number,
TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,
aip.invoice_id client_voucher_number,
ac.currency_code currency_code,
TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,
--ac.amount CHECK_AMOUNT,
ai.invoice_amount check_amount,
ac.check_id,
ac.status_lookup_code
FROM ap_invoices_all ai,
ap_checks_all ac,
ap_invoice_payments_all aip
WHERE aip.invoice_id = ai.invoice_id
AND ai.attribute10 IS NOT NULL
AND ac.check_id >
(SELECT MAX (check_id) FROM apps.xx_cor360_checks_ext
)
AND ac.status_lookup_code = 'NEGOTIABLE';
CURSOR c_check_details2
IS
SELECT ai.attribute10 voucher_number,
NULL check_number,
TO_CHAR (ac.check_date, 'YYYYMMDD') check_date,
aip.invoice_id client_voucher_number,
ac.currency_code currency_code,
TO_CHAR (ai.terms_date, 'YYYYDDMM') pymnt_schd_date,
ac.amount check_amount,
ac.check_id,
ac.status_lookup_code
FROM ap_invoices_all ai,
ap_checks_all ac,
ap_invoice_payments_all aip,
xxx_cor360_checks_ext scc
WHERE aip.invoice_id = ai.invoice_id
AND ai.attribute10 IS NOT NULL
AND ac.check_id = scc.check_id
AND ac.status_lookup_code = 'VOIDED'
AND scc.status_lookup_code = 'NEGOTIABLE'
AND ac.last_update_date > scc.extract_date;
/* -- Variable declarations --*/
l_utl_file UTL_FILE.file_type;
l_current_time fnd_lookup_values.meaning%TYPE := '';
l_run_date DATE;
/* -- Output File Directory -- */
l_outfile_path VARCHAR2 (4000) := 'COR360_OUT_DATA_DIR';
l_file_name VARCHAR2 (240);
l_create_dir VARCHAR2 (500);
l_rec_count NUMBER := 0;
l_file_name2 VARCHAR2 (240);
l_time_comp VARCHAR2 (100) := TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');
v_count NUMBER;
v_count_id NUMBER;
l_sftp_request_id NUMBER;
l_SFTPUSER VARCHAR(50);
l_SFTPPASSWRD VARCHAR(50);
l_remote_dir VARCHAR (50);
BEGIN
/*-------------In the main program ------------------*/
LOG ('Directory File Path: ' || l_outfile_path);
/*-------Set the Output File Name -----------*/
l_file_name := 'AP_' || l_time_comp || '_checkreference.txt';
BEGIN
l_utl_file := UTL_FILE.fopen (l_outfile_path, l_file_name, 'W', 32767);
/*--Writing Column Headers---*/
UTL_FILE.put (l_utl_file, 'VOUCHER_NUMBER');
UTL_FILE.put (l_utl_file, '|' || 'CHECK_NUMBER');
UTL_FILE.put (l_utl_file, '|' || 'CHECK_DATE');
UTL_FILE.put (l_utl_file, '|' || 'CLIENT_VOUCHER_NUMBER');
UTL_FILE.put (l_utl_file, '|' || 'CURRENCY_CODE');
UTL_FILE.put (l_utl_file, '|' || 'PYMNT_SCHD_DATE');
UTL_FILE.put (l_utl_file, '|' || 'CHECK_AMOUNT');
UTL_FILE.new_line (l_utl_file);
-- for c_check_details_check in c_check_details (l_run_date) loop
SELECT COUNT (*)
INTO v_count
FROM xx_cor360_checks_ext;
--where check_id=c_check_details_check.check_id;
/*-------- Write records ---------*/
--IF condition need here
IF v_count = 0 THEN
FOR c_check_rec IN c_check_details
LOOP
UTL_FILE.put (l_utl_file, c_check_rec.voucher_number);
UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_number);
UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec.client_voucher_number );
UTL_FILE.put (l_utl_file, '|' || c_check_rec.currency_code);
UTL_FILE.put (l_utl_file, '|' || c_check_rec.pymnt_schd_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec.check_amount);
INSERT
INTO xx_cor360_checks_ext
(
check_id,
status_lookup_code,
extract_date
)
VALUES
(
c_check_rec.check_id,
c_check_rec.status_lookup_code,
SYSDATE
);
COMMIT;
UTL_FILE.new_line (l_utl_file);
l_rec_count := l_rec_count + 1;
END LOOP;
ELSE
FOR c_check_rec1 IN c_check_details1
LOOP
UTL_FILE.put
(
l_utl_file, c_check_rec1.voucher_number
)
;
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_number);
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.client_voucher_number );
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.currency_code);
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.pymnt_schd_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec1.check_amount);
SELECT COUNT (*)
INTO v_count_id
FROM xx_cor360_checks_ext
WHERE check_id = c_check_rec1.check_id;
IF v_count_id = 0 THEN
INSERT
INTO xx_cor360_checks_ext
(
check_id,
status_lookup_code,
extract_date
)
VALUES
(
c_check_rec1.check_id,
c_check_rec1.status_lookup_code,
SYSDATE
);
END IF;
COMMIT;
UTL_FILE.new_line (l_utl_file);
l_rec_count := l_rec_count + 1;
END LOOP;
FOR c_check_rec2 IN c_check_details2
LOOP
UTL_FILE.put
(
l_utl_file, c_check_rec2.voucher_number
)
;
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_number);
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.client_voucher_number );
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.currency_code);
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.pymnt_schd_date);
UTL_FILE.put (l_utl_file, '|' || c_check_rec2.check_amount);
UPDATE xx_cor360_checks_ext
SET status_lookup_code = 'VOIDED',
extract_date = SYSDATE
WHERE check_id = c_check_rec2.check_id;
COMMIT;
UTL_FILE.new_line (l_utl_file);
l_rec_count := l_rec_count + 1;
END LOOP;
END IF;
-- end loop;
UTL_FILE.fclose (l_utl_file);
l_file_name2 := 'AP_' || l_time_comp || '_checkreference.ctl';
l_utl_file := UTL_FILE.fopen (l_outfile_path, l_file_name2, 'W', 32767);
UTL_FILE.put (l_utl_file, 'ROWCOUNT');
UTL_FILE.new_line (l_utl_file);
UTL_FILE.put (l_utl_file, l_rec_count);
UTL_FILE.fclose (l_utl_file);
fnd_file.put_line (fnd_file.LOG, 'Submitting SFTP Program');
EXCEPTION
WHEN UTL_FILE.invalid_operation THEN
p_retcode := g_conc_error;
LOG ( 'Error occured when attempting as trying to do invalid operation' || SQLERRM || ' ' || SQLCODE );
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_path THEN
p_retcode := g_conc_error;
LOG ( 'Error occured when attempting to write a file into server: ' || SQLERRM || ' ' || SQLCODE );
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_mode THEN
p_retcode := g_conc_error;
LOG ('Invalid mode: ' || SQLERRM || ' ' || SQLCODE);
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_filehandle THEN
p_retcode := g_conc_error;
LOG ('invalid filehandle' || SQLERRM || ' ' || SQLCODE);
UTL_FILE.fclose_all;
WHEN UTL_FILE.read_error THEN
p_retcode := g_conc_error;
LOG ('read error' || SQLERRM || ' ' || SQLCODE);
UTL_FILE.fclose_all;
WHEN UTL_FILE.internal_error THEN
p_retcode := g_conc_error;
LOG ('internal error' || SQLERRM || ' ' || SQLCODE);
UTL_FILE.fclose_all;
WHEN OTHERS THEN
p_retcode := g_conc_error;
LOG ('other error' || SQLERRM || ' ' || SQLCODE);
UTL_FILE.fclose_all;
END;
EXCEPTION
WHEN OTHERS THEN
p_retcode := g_conc_error;
LOG ('Error occured in the main program: ' || SQLERRM || ' ' || SQLCODE );
-- IF l_file_name IS NOT NULL
-- THEN
-- END IF;
END;
END xx_checks_extract_pkg;
/
0 comments:
Post a Comment