CREATE OR REPLACE PACKAGE APPS.xx_gli_import AS
PROCEDURE execute_import(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_je_source_name IN VARCHAR2 DEFAULT NULL,
p_je_category_name IN VARCHAR2 DEFAULT NULL,
p_validate_only IN VARCHAR2 DEFAULT 'N');
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.xx_gli_import
AS
-- Author: Faraz Anwar
-- Date: 26-Jul-2010
-- Status: Tested
-- code meaning
--
-- status_flag = 'A' (Avaliable for Processing)
-- status_flag = 'P' (Successfully Processed into Oracle GL)
-- status_flag = 'E' (Error)
--
-- Last Update Date: 25-Dec-2010
-- Comments : The Journal Number was corrected to use JOURNAL_NO instead of JOURNAL_NAME
-- : in the PUSH_TO-IFACE Procedure
--
-- Last Update Date: 24-Jan-2011
-- Comments :
--
g_error_threshold CONSTANT NUMBER := 50;
--
--
g_flex_structure_name VARCHAR2 (250);
g_ledger_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
--
--
invalid_source EXCEPTION;
invalid_category_xcp EXCEPTION;
invalid_amount_xcp EXCEPTION;
date_not_in_period_range EXCEPTION;
invalid_currency_code EXCEPTION;
invalid_account_code EXCEPTION;
invalid_cost_center EXCEPTION;
invalid_entry_combination EXCEPTION;
not_in_open_period EXCEPTION;
--invalid_code_combination EXCEPTION;
--
--
g_segment1 VARCHAR2 (25);
g_segment2 VARCHAR2 (25);
g_segment3 VARCHAR2 (25);
g_segment4 VARCHAR2 (25);
g_segment5 VARCHAR2 (25);
g_segment6 VARCHAR2 (25);
g_segment7 VARCHAR2 (25);
g_segment8 VARCHAR2 (25);
g_segment9 VARCHAR2 (25);
g_segment10 VARCHAR2 (25);
g_segment11 VARCHAR2 (25);
g_segment12 VARCHAR2 (25);
g_segment13 VARCHAR2 (25);
g_segment14 VARCHAR2 (25);
g_segment15 VARCHAR2 (25);
PROCEDURE verify_transaction (p_rec IN xx_iit_gl_interface%ROWTYPE);
PROCEDURE mark_error (p_transaction_id NUMBER, p_error_message VARCHAR2);
PROCEDURE mark_error (p_journal_name VARCHAR2, p_error_message IN VARCHAR2 DEFAULT NULL);
PROCEDURE get_segment_default (p_seg_column IN VARCHAR2, x_default_value OUT VARCHAR2);
PROCEDURE push_to_iface (rec IN xx_iit_gl_interface%ROWTYPE, p_batch_name VARCHAR2, p_batch_description VARCHAR2, p_group_id NUMBER);
PROCEDURE execute_import (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_je_source_name IN VARCHAR2 DEFAULT NULL,
p_je_category_name IN VARCHAR2 DEFAULT NULL,
p_validate_only IN VARCHAR2 DEFAULT 'N')
IS
l_group_id NUMBER := 0;
l_batch_name gl_interface.reference2%TYPE;
l_batch_description gl_interface.reference4%TYPE;
l_success_count NUMBER := 0;
l_conc_request_id NUMBER := 0;
l_error_count NUMBER := 0;
-- all transaction lines
CURSOR csr_all_lines
IS
SELECT *
FROM xx_iit_gl_interface
WHERE je_category_name = nvl (p_je_category_name, je_category_name)
AND je_source_name = nvl (p_je_source_name, je_source_name)
AND status_flag IN ('A', 'E')
FOR UPDATE;
BEGIN
SELECT xx_gli_group_id_s.NEXTVAL
INTO l_group_id
FROM dual;
--
-- get accouting structure
--
SELECT chart_of_accounts_name
INTO g_flex_structure_name
FROM gl_sets_of_books_v
WHERE set_of_books_id = g_ledger_id;
IF g_ledger_id IS NULL
THEN
fnd_file.put_line (fnd_file.LOG, 'Ledger ID not found.');
RETURN;
END IF;
--
-- printing program paramters
--
fnd_file.put_line (fnd_file.LOG, 'Version: XX_GLI_IMPORT v20110204 FA');
fnd_file.put_line (fnd_file.LOG, 'p_JE_CATEGORY_NAME = ' || p_je_category_name);
fnd_file.put_line (fnd_file.LOG, 'p_je_source_name = ' || p_je_source_name);
fnd_file.put_line (fnd_file.LOG, 'validate = ' || p_validate_only);
fnd_file.new_line (fnd_file.LOG, 1);
fnd_file.put_line (fnd_file.LOG, 'ledger_id = ' || g_ledger_id);
fnd_file.put_line (fnd_file.LOG, 'group_id = ' || l_group_id);
fnd_file.put_line (fnd_file.LOG, 'flex_structure_name = ' || g_flex_structure_name);
fnd_file.new_line (fnd_file.LOG, 1);
--
-- get segment default values
--
get_segment_default ('SEGMENT1', g_segment1);
get_segment_default ('SEGMENT2', g_segment2);
get_segment_default ('SEGMENT3', g_segment3);
get_segment_default ('SEGMENT4', g_segment4);
get_segment_default ('SEGMENT5', g_segment5);
get_segment_default ('SEGMENT6', g_segment6);
get_segment_default ('SEGMENT7', g_segment7);
get_segment_default ('SEGMENT8', g_segment8);
get_segment_default ('SEGMENT9', g_segment9);
get_segment_default ('SEGMENT10', g_segment10);
get_segment_default ('SEGMENT11', g_segment11);
get_segment_default ('SEGMENT12', g_segment12);
get_segment_default ('SEGMENT13', g_segment13);
get_segment_default ('SEGMENT14', g_segment14);
get_segment_default ('SEGMENT15', g_segment15);
--
-- mark error records as 'A' to retry import for error lines
--
FOR r1 IN csr_all_lines
LOOP
UPDATE xx_iit_gl_interface
SET status_flag = 'A',
error_message = NULL
WHERE status_flag = 'E';
IF SQL%FOUND
THEN
fnd_file.put_line (fnd_file.LOG, SQL%ROWCOUNT || ' records marked for retry');
END IF;
END LOOP;
--
-- verify individual lines
--
fnd_file.put_line (fnd_file.LOG, 'Verifying individual transaction lines...');
FOR r1 IN csr_all_lines
LOOP
BEGIN
verify_transaction (r1);
EXCEPTION
WHEN invalid_source
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'invalid_source_name');
-- Added 25/12/2010 to check valid Category input
WHEN invalid_category_xcp
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'invalid_category');
-- Added 25/12/2010 to check valid Invalid Amounts
WHEN invalid_amount_xcp
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'invalid_entered_amount');
WHEN date_not_in_period_range
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'date_not_in_period_range');
WHEN invalid_currency_code
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'invalid_currency_code');
WHEN invalid_entry_combination
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'invalid_entry_combination');
WHEN not_in_open_period
THEN
mark_error (r1.journal_name);
mark_error (r1.transaction_id, 'not_in_open_period');
END;
END LOOP;
--
-- verify jounral sums
--
DECLARE
CURSOR csr_uneq_jrnls
IS
SELECT SUM (round (entered_dr, 2)),
SUM (round (entered_cr, 2)),
journal_name
FROM xx_iit_gl_interface
WHERE je_category_name = nvl (p_je_category_name, je_category_name)
AND je_source_name = nvl (p_je_source_name, je_source_name)
AND status_flag IN ('A')
GROUP BY journal_name
HAVING SUM (round (entered_dr, 2)) <> SUM (round (entered_cr, 2));
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Verifying journal equality...');
FOR r2 IN csr_uneq_jrnls
LOOP
mark_error (r2.journal_name, 'debits_not_equal_credits');
END LOOP;
END;
--
-- generate error log
--
DECLARE
CURSOR c1
IS
SELECT transaction_id,
journal_name,
status_flag,
error_message
FROM xx_iit_gl_interface
WHERE status_flag = 'E';
BEGIN
FOR r1 IN c1
LOOP
l_error_count := l_error_count + 1;
END LOOP;
IF l_error_count > 1
THEN
fnd_file.new_line (fnd_file.LOG, 1);
fnd_file.put_line (which => fnd_file.LOG,
buff => 'TRX_ID' || chr (9) || 'journal_name' || CHR (9) || 'STATUS' || CHR (9) || 'ERROR DESCRIPTION');
FOR r1 IN c1
LOOP
fnd_file.put_line (which => fnd_file.LOG,
buff => r1.transaction_id
|| chr (9)
|| r1.journal_name
|| CHR (9)
|| chr (9)
|| r1.status_flag
|| CHR (9)
|| r1.error_message);
END LOOP;
fnd_file.new_line (fnd_file.LOG, 1);
END IF;
fnd_file.put_line (which => fnd_file.LOG, buff => l_error_count || ' erorrs encounterd during validation');
END;
IF p_validate_only = 'Y'
THEN
RETURN;
END IF;
fnd_file.put_line (fnd_file.LOG, 'Transferring to GL. Category: ' || p_je_category_name || ', Source: ' || p_je_source_name);
--
-- transfer to gl interface
--
DECLARE
CURSOR csr_batches
IS
SELECT DISTINCT je_category_name,
je_source_name,
trunc (accounting_date) accounting_date
FROM xx_iit_gl_interface
WHERE je_category_name = nvl (p_je_category_name, je_category_name)
AND je_source_name = nvl (p_je_source_name, je_source_name)
AND status_flag = 'A'
GROUP BY je_category_name,
je_source_name,
trunc (accounting_date);
CURSOR csr_batch_lines (p_je_category_name VARCHAR2, p_je_source_name VARCHAR2)
IS
SELECT *
FROM xx_iit_gl_interface
WHERE je_category_name = nvl (p_je_category_name, je_category_name)
AND je_source_name = nvl (p_je_source_name, je_source_name)
AND status_flag = 'A';
BEGIN
IF l_error_count > g_error_threshold
THEN
fnd_file.put_line (which => fnd_file.LOG, buff => 'Too Many Errors Encountered. Aborting GL Import Program');
ELSE
-- fnd_file.put_line(fnd_file.LOG, 'Before Batch Loop'); -- Debugging marker
FOR r3 IN csr_batches
LOOP
-- fnd_file.put_line(fnd_file.LOG, 'Before Batch: ' || r3.JE_SOURCE_NAME); -- Debugging marker
--
-- batch name logic
--
l_batch_name := NULL;
l_batch_description := NULL;
--
FOR r4 IN csr_batch_lines (r3.je_category_name, r3.je_source_name)
LOOP
push_to_iface (r4,
l_batch_name,
l_batch_description,
l_group_id);
-- fnd_file.put_line(fnd_file.LOG, 'Before Update: ' || r4.TRANSACTION_ID); -- Debugging marker
UPDATE xx_iit_gl_interface
SET status_flag = 'P',
error_message = NULL,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
ebs_conc_request_id = fnd_global.conc_request_id
WHERE transaction_id = r4.transaction_id;
-- fnd_file.put_line(fnd_file.LOG, 'After Update: ' || r4.TRANSACTION_ID); -- Debugging marker
l_success_count := l_success_count + 1;
END LOOP;
END LOOP;
fnd_file.put_line (fnd_file.LOG, l_success_count || ' records transferred to GL Interface');
END IF;
END;
END;
PROCEDURE verify_transaction (p_rec IN xx_iit_gl_interface%ROWTYPE)
IS
l_error_message VARCHAR2 (1000);
dummy VARCHAR2 (1);
l_code_combination_id NUMBER;
l_application_id NUMBER;
l_closing_status VARCHAR2 (1);
BEGIN
--
-- validate je_source_name
--
BEGIN
SELECT 'x'
INTO dummy
FROM gl_je_sources
WHERE user_je_source_name = p_rec.je_source_name;
EXCEPTION
WHEN no_data_found
THEN
RAISE invalid_source;
END;
--
-- validate JE_CATEGORY_NAME_name (Added by AB 25/12/2010)
--
BEGIN
SELECT 'x'
INTO dummy
FROM gl_je_categories
WHERE user_je_category_name = p_rec.je_category_name;
EXCEPTION
WHEN no_data_found
THEN
RAISE invalid_category_xcp;
END;
--
-- Validate individual line Amount Errors
-- Both Debit and Credit is 0
--
IF p_rec.entered_dr = 0 AND p_rec.entered_cr = 0
THEN
RAISE invalid_amount_xcp;
END IF;
--
-- Validate individual line Amount Errors
-- Both Debit and Credit is greater than 0
--
IF p_rec.entered_dr > 0 AND p_rec.entered_cr > 0
THEN
RAISE invalid_amount_xcp;
END IF;
-- validate period
BEGIN
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT closing_status
INTO l_closing_status
FROM gl_period_statuses
-- to avoid adjusting period check both start and end date
WHERE start_date = TO_DATE (to_char (p_rec.accounting_date, 'Mon-YYYY'), 'Mon-YYYY')
AND end_date = last_day (p_rec.accounting_date)
AND set_of_books_id = g_ledger_id
AND application_id = l_application_id;
IF l_closing_status NOT IN ('O', 'F')
THEN
RAISE not_in_open_period;
END IF;
EXCEPTION
WHEN no_data_found
THEN
RAISE not_in_open_period;
END;
-- validate currency code
BEGIN
SELECT 'x'
INTO dummy
FROM fnd_currencies
WHERE currency_code = p_rec.currency_code;
EXCEPTION
WHEN no_data_found
THEN
RAISE invalid_currency_code;
END;
END verify_transaction;
PROCEDURE mark_error (p_transaction_id NUMBER, p_error_message VARCHAR2)
IS
BEGIN
-- mark individual trx line with detail error
UPDATE xx_iit_gl_interface
SET status_flag = 'E',
error_message = p_error_message
WHERE transaction_id = p_transaction_id;
END;
PROCEDURE mark_error (p_journal_name VARCHAR2, p_error_message IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
-- mark entire journal with error
UPDATE xx_iit_gl_interface
SET status_flag = 'E',
error_message = p_error_message
WHERE journal_name = p_journal_name;
END;
PROCEDURE get_segment_default (p_seg_column IN VARCHAR2, x_default_value OUT VARCHAR2)
IS
CURSOR c1 (p_seg_column VARCHAR2)
IS
SELECT nvl (default_value, lpad ('0',
display_size,
'0')) segment_default
FROM fnd_id_flex_structures_vl ffs,
fnd_id_flex_segments_vl ffs2,
fnd_application fa
WHERE ffs.application_id = ffs2.application_id
AND ffs.id_flex_code = ffs2.id_flex_code
AND ffs.id_flex_num = ffs2.id_flex_num
AND ffs.application_id = fa.application_id
AND ffs.id_flex_code = 'GL#'
AND id_flex_structure_code = g_flex_structure_name
AND fa.application_short_name = 'SQLGL'
AND application_column_name = p_seg_column;
dummy NUMBER;
BEGIN
OPEN c1 (p_seg_column);
FETCH c1
INTO x_default_value;
CLOSE c1;
END;
PROCEDURE push_to_iface (rec IN xx_iit_gl_interface%ROWTYPE, p_batch_name VARCHAR2, p_batch_description VARCHAR2, p_group_id NUMBER)
IS
BEGIN
--
-- push valid records in gl_interface
--
-- fnd_file.put_line(fnd_file.LOG, 'Before Insert: ' || rec.TRANSACTION_ID); -- Debugging marker
INSERT INTO gl_interface
(ledger_id,
status,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
GROUP_ID,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
accounting_date,
code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference2,
reference4,
reference5,
reference6,
reference10,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
VALUES (g_ledger_id,
'NEW',
rec.je_source_name,
rec.je_category_name,
rec.currency_code,
SYSDATE, --DATE_CREATED
fnd_global.user_id, --CREATED_BY
'A', --ACTUAL_FLAG
p_group_id,
rec.entered_dr,
rec.entered_cr,
NULL, --ACCOUNTED_DR
NULL, --ACCOUNTED_CR
rec.accounting_date,
NULL, --CODE_COMBINATION_ID
nvl (rec.ebs_segment1, g_segment1),
nvl (rec.ebs_segment2, g_segment2),
nvl (rec.ebs_segment3, g_segment3),
nvl (rec.ebs_segment4, g_segment4),
nvl (rec.ebs_segment5, g_segment5),
nvl (rec.ebs_segment6, g_segment6),
nvl (rec.ebs_segment7, g_segment7),
nvl (rec.ebs_segment8, g_segment8),
nvl (rec.ebs_segment9, g_segment9),
nvl (rec.ebs_segment10, g_segment10),
nvl (rec.ebs_segment11, g_segment11),
nvl (rec.ebs_segment12, g_segment12),
nvl (rec.ebs_segment13, g_segment13),
NULL,
NULL,
p_batch_name,
p_batch_description,
rec.journal_name,
rec.journal_description,
NULL, --rec.journal_name,
rec.je_source_name,
rec.ebs_attribute1,
rec.ebs_attribute2,
rec.ebs_attribute3,
rec.ebs_attribute4,
rec.ebs_attribute5,
rec.ebs_attribute6,
rec.ebs_attribute7,
rec.ebs_attribute8,
rec.ebs_attribute9,
rec.ebs_attribute10);
-- fnd_file.put_line(fnd_file.LOG, 'After Insert: ' || rec.TRANSACTION_ID); -- Debugging marker
END push_to_iface;
END xx_gli_import;
/
No comments:
Post a Comment