Oracle Apps World: Validations/Code for GL Journal Import

Oracle-R12-GL-Journal-Import

CREATE OR REPLACE PACKAGE BODY APPS.XXXX_GL_JOURNAL_CONV_PK
AS

PROCEDURE get_budget_version_id_p( p_ledger_id IN VARCHAR2
,p_budget_name IN VARCHAR2
,x_budget_ver_id OUT NOCOPY NUMBER
,x_error_msg OUT NOCOPY VARCHAR2)
IS
ln_budget_ver_id gl_budget_versions.budget_version_id%type :=NULL;

BEGIN

SELECT budget_version_id
INTO ln_budget_ver_id
FROM gl_budget_versions gbv
,gl_budgets gb
WHERE UPPER(gb.budget_name) = UPPER(p_budget_name)
AND gbv.budget_name = gb.budget_name
AND ledger_id = p_ledger_id
AND gbv.status = 'O';

x_error_msg := NULL;
x_budget_ver_id := ln_budget_ver_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (
fnd_file.LOG,
'NO Record found in GL_BUDGETS table FOR GIVEN BUDGET NAME :'||p_budget_name
);
x_error_msg := 'NO Record found in GL_BUDGETS table FOR GIVEN BUDGET NAME :'||p_budget_name;
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while finding Budget versin id:-'|| SQLERRM
);
x_error_msg := 'NO Record found in GL_BUDGETS table FOR GIVEN BUDGET NAME :'||p_budget_name||substr(SQLERRM,1,200);
END;

FUNCTION INSERT_IFACE_P (p_srec xxglstgrec%rowtype)
RETURN BOOLEAN
IS
l_retval BOOLEAN := FALSE;
p_rec xxglintfrec%rowtype;
l_budget_ver_id gl_budget_versions.budget_version_id%type;
x_error_msg varchar2(4000);
BEGIN
p_rec.status :='NEW';
p_rec.accounting_date :=p_srec.accounting_date;
p_rec.currency_code :=p_srec.currency_code;
p_rec.DATE_CREATED := NVL(p_srec.DATE_CREATED,SYSDATE);
p_rec.created_by := p_srec.created_by;
p_rec.actual_flag :=p_srec.actual_flag;
p_rec.user_je_source_name :=p_srec.user_je_source_name;
p_rec.entered_dr := p_srec.entered_dr;
p_rec.entered_cr := p_srec.entered_cr;
p_rec.accounted_dr := p_srec.accounted_dr;
p_rec.accounted_cr :=p_srec.accounted_cr;

IF nvl(gv_conv_type,0) = 1
THEN
p_rec.reference4:=p_srec.reference4;
--p_rec.reference4:=p_srec.user_je_source_name||'-'||p_srec.accounting_date;--changes made by Pawan on 03/16/2010
p_rec.attribute5:=p_srec.reference4;----changes made by Pawan on 03/16/2010. To keep old Journal Reference Name
Else
p_rec.reference4:=p_srec.reference4;
END IF;

p_rec.reference5:=p_srec.reference5;
p_rec.user_je_category_name := p_srec.user_je_category_name;
p_rec.reference10:=p_srec.reference10a||p_srec.reference10b;
p_rec.attribute2:=p_srec.je_header_id;
p_rec.attribute3:=p_srec.je_line_number;
p_rec.attribute4:=p_srec.gl_sl_link_id;
p_rec.attribute1:= p_srec.segment1||'-'||p_srec.segment2||'-'||p_srec.segment3||'-'||p_srec.segment4||'-'||
p_srec.segment5||'-'||p_srec.segment6||'-'||p_srec.segment7;
get_ledger_id (p_srec.ledger_name,
p_rec.ledger_id,
x_error_msg
);
p_rec.set_of_books_id := p_rec.ledger_id;
p_rec.code_combination_id := XXXX_UTILITY_PKG.get_r12_ccid( p_datasource =>p_srec.source_system,
p_sob_name => p_srec.ledger_name,
p_segment1 => p_srec.segment1,
p_segment2 => p_srec.segment2,
p_segment3 => p_srec.segment3,
p_segment4 => p_srec.segment4,
p_segment5 => p_srec.segment5,
p_segment6 => p_srec.segment6,
p_segment7 => p_srec.segment7,
p_errmsg => x_error_msg);

IF nvl(gv_conv_type,0) = 2
THEN
l_budget_ver_id := 0;
get_budget_version_id_p( p_ledger_id => p_rec.ledger_id
,p_budget_name => p_srec.budget_version_id
,x_budget_ver_id => p_rec.budget_version_id
,x_error_msg => x_error_msg);
p_rec.period_name:=to_char(p_srec.accounting_date,'Mon-YY');--'Jan-10';
p_rec.reference4:=p_srec.user_je_source_name||'-'||p_srec.accounting_date;
END IF;
INSERT INTO gl_interface (status,
ledger_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
currency_conversion_date,
encumbrance_type_id,
budget_version_id,
user_currency_conversion_type,
currency_conversion_rate,
average_journal_flag,
originating_bal_seg_value,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
transaction_date,
reference1,
reference2,
reference3,
reference4,
reference5,
reference6,
reference7,
reference8,
reference9,
reference10,
reference11,
reference12,
reference13,
reference14,
reference15,
reference16,
reference17,
reference18,
reference19,
reference20,
reference21,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27,
reference28,
reference29,
reference30,
je_batch_id,
period_name,
je_header_id,
je_line_num,
chart_of_accounts_id,
functional_currency_code,
code_combination_id,
date_created_in_gl,
warning_code,
status_description,
stat_amount,
GROUP_ID,
request_id,
subledger_doc_sequence_id,
subledger_doc_sequence_value,
attribute1,
attribute2,
gl_sl_link_id,
gl_sl_link_table,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
context,
context2,
invoice_date,
tax_code,
invoice_identifier,
invoice_amount,
context3,
ussgl_transaction_code,
descr_flex_error_message,
jgzz_recon_ref,
reference_date,
set_of_books_id,
balancing_segment_value,
management_segment_value,
funds_reserved_flag)
VALUES (p_rec.status,
p_rec.ledger_id,
p_rec.accounting_date,
p_rec.currency_code,
p_rec.date_created,
p_rec.created_by,
p_rec.actual_flag,
p_rec.user_je_category_name,
p_rec.user_je_source_name,
p_rec.currency_conversion_date,
p_rec.encumbrance_type_id,
p_rec.budget_version_id,
p_rec.user_currency_conversion_type,
p_rec.currency_conversion_rate,
p_rec.average_journal_flag,
p_rec.originating_bal_seg_value,
p_rec.segment1,
p_rec.segment2,
p_rec.segment3,
p_rec.segment4,
p_rec.segment5,
p_rec.segment6,
p_rec.segment7,
p_rec.segment8,
p_rec.segment9,
p_rec.segment10,
p_rec.segment11,
p_rec.segment12,
p_rec.segment13,
p_rec.segment14,
p_rec.segment15,
p_rec.segment16,
p_rec.segment17,
p_rec.segment18,
p_rec.segment19,
p_rec.segment20,
p_rec.segment21,
p_rec.segment22,
p_rec.segment23,
p_rec.segment24,
p_rec.segment25,
p_rec.segment26,
p_rec.segment27,
p_rec.segment28,
p_rec.segment29,
p_rec.segment30,
p_rec.entered_dr,
p_rec.entered_cr,
p_rec.accounted_dr,
p_rec.accounted_cr,
p_rec.transaction_date,
p_rec.reference1,
p_rec.reference2,
p_rec.reference3,
p_rec.reference4,
p_rec.reference5,
p_rec.reference6,
p_rec.reference7,
p_rec.reference8,
p_rec.reference9,
p_rec.reference10,
p_rec.reference11,
p_rec.reference12,
p_rec.reference13,
p_rec.reference14,
p_rec.reference15,
p_rec.reference16,
p_rec.reference17,
p_rec.reference18,
p_rec.reference19,
p_rec.reference20,
p_rec.reference21,
p_rec.reference22,
p_rec.reference23,
p_rec.reference24,
p_rec.reference25,
p_rec.reference26,
p_rec.reference27,
p_rec.reference28,
p_rec.reference29,
p_rec.reference30,
p_rec.je_batch_id,
p_rec.period_name,
p_rec.je_header_id,
p_rec.je_line_num,
p_rec.chart_of_accounts_id,
p_rec.functional_currency_code,
p_rec.code_combination_id,
p_rec.date_created_in_gl,
p_rec.warning_code,
p_rec.status_description,
p_rec.stat_amount,
p_rec.GROUP_ID,
p_rec.request_id,
p_rec.subledger_doc_sequence_id,
p_rec.subledger_doc_sequence_value,
p_rec.attribute1,
p_rec.attribute2,
p_rec.gl_sl_link_id,
p_rec.gl_sl_link_table,
p_rec.attribute3,
p_rec.attribute4,
p_rec.attribute5,
p_rec.attribute6,
p_rec.attribute7,
p_rec.attribute8,
p_rec.attribute9,
p_rec.attribute10,
p_rec.attribute11,
p_rec.attribute12,
p_rec.attribute13,
p_rec.attribute14,
p_rec.attribute15,
p_rec.attribute16,
p_rec.attribute17,
p_rec.attribute18,
p_rec.attribute19,
p_rec.attribute20,
p_rec.context,
p_rec.context2,
p_rec.invoice_date,
p_rec.tax_code,
p_rec.invoice_identifier,
p_rec.invoice_amount,
p_rec.context3,
p_rec.ussgl_transaction_code,
p_rec.descr_flex_error_message,
p_rec.jgzz_recon_ref,
p_rec.reference_date,
p_rec.set_of_books_id,
p_rec.balancing_segment_value,
p_rec.management_segment_value,
p_rec.funds_reserved_flag);

l_retval := TRUE;
RETURN l_retval;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
fnd_file.put_line (
fnd_file.LOG,
'Duplicate Record found in gl_interface table at INSERT_IFACE_P function:-'
|| SQLERRM
);
l_retval := FALSE;
RETURN l_retval;
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while populating gl_interface table at INSERT_IFACE_P function:-'
|| SQLERRM
);
l_retval := FALSE;
RETURN l_retval;
END INSERT_IFACE_P;
---------------------------------------------------------------------------------------------------------------------------

/***********************

FUNCTION INSERT_IFACECONT_P(p_rec xxglintconrec%ROWTYPE)
RETURN BOOLEAN
IS
l_retval BOOLEAN := FALSE;
BEGIN
INSERT INTO gl_interface_control
(status,
set_of_books_id,
je_source_name,
group_id,
interface_run_id)
VALUES
(p_rec.status,
p_rec.set_of_books_id,
p_rec.je_source_name,
p_rec.group_id,
p_rec.interface_run_id
);
RETURN l_retval;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
fnd_file.put_line (
fnd_file.LOG,
'Duplicate Record found in gl_interface_control table at INSERT_IFACECONT_P function:-'
|| SQLERRM
);
l_retval := FALSE;
RETURN l_retval;
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while populating gl_interface_control table at INSERT_IFACECONT_P function:-'
|| SQLERRM
);
l_retval := FALSE;
RETURN l_retval;
END INSERT_IFACECONT_P;
**************************/
PROCEDURE get_ledger_id(p_ledger IN gl_ledgers.name%TYPE,
p_ledger_id OUT NOCOPY gl_ledgers.ledger_id%TYPE,
p_error_msg OUT NOCOPY VARCHAR2
)
AS
cursor get_ledger
IS
SELECT LEDGER_ID
FROM GL_LEDGERS
WHERE NAME=p_ledger;

v_ledger_id gl_ledgers.ledger_id%TYPE;

BEGIN

IF (get_ledger%ISOPEN)
THEN
CLOSE get_ledger;
END IF;

OPEN get_ledger;

FETCH get_ledger
INTO v_ledger_id;

CLOSE get_ledger;

p_ledger_id:=v_ledger_id;

EXCEPTION
WHEN OTHERS
THEN
IF (get_ledger%ISOPEN)
THEN
CLOSE get_ledger;
END IF;
fnd_file.put_line (fnd_file.LOG, 'ERROR in get_ledger_id - ' || SQLERRM);
p_error_msg :='ERROR in get_ledger_id - ' || SQLERRM;
END get_ledger_id;
------------------------------------------------------------------------------------------------------------------------------

-- +===================================================================+
-- | Name : write_log |
-- | |
-- | Description: This Procedure shall write to the concurrent |
-- | program log file |
-- | |
-- +===================================================================+
PROCEDURE write_log_p (p_flag IN VARCHAR2, p_message IN VARCHAR2)
IS
lv_error_message VARCHAR2 (30000);
BEGIN
IF p_flag = 'Y'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Unexpected Error while writing to the log file : ' || SQLERRM;
FND_FILE.PUT_LINE (FND_FILE.LOG,SUBSTR(lv_error_message,1,200));
END write_log_p;

-- +===================================================================+
-- | Name : WRITE_OUT_P |
-- | |
-- | Description: This Procedure shall write to the concurrent |
-- | program output file |
-- | |
-- | |
-- +===================================================================+
PROCEDURE write_out_p (p_message IN VARCHAR2)
IS
lv_error_message VARCHAR2 (30000);
BEGIN
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, p_message);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Unexpected Error while writing to the output file : ' || SQLERRM;
FND_FILE.PUT_LINE (FND_FILE.LOG,SUBSTR(lv_error_message,1,200));
END write_out_p;
-- +===================================================================+
-- | Name : log_error |
-- | |
-- | Description: This Procedure shall write to the Custom Errr |
-- | table |
-- | |
-- +===================================================================+
PROCEDURE log_error (P_REC XXXX_GL_ERRORS_STG%ROWTYPE)
AS
BEGIN
INSERT INTO XXXX_GL_ERRORS_STG
(
RECORD_ID,
BATCH_NAME,
BATCH_DATE,
LEDGER_NAME,
ACCOUNTING_DATE,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
ERROR_TYPE,
ERROR_CODE,
ERROR_MESSAGE,
CONC_REQUEST_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ORG_ID ,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7
)
VALUES (
P_REC.RECORD_ID,
P_REC.BATCH_NAME,
P_REC.BATCH_DATE,
P_REC.LEDGER_NAME,
P_REC.ACCOUNTING_DATE,
P_REC.USER_JE_CATEGORY_NAME,
P_REC.USER_JE_SOURCE_NAME,
P_REC.ERROR_TYPE,
P_REC.ERROR_CODE,
P_REC.ERROR_MESSAGE,
P_REC.CONC_REQUEST_ID,
P_REC.CREATION_DATE,
P_REC.CREATED_BY,
P_REC.LAST_UPDATED_BY,
P_REC.LAST_UPDATE_DATE,
P_REC.ORG_ID ,
P_REC.SEGMENT1,
P_REC.SEGMENT2,
P_REC.SEGMENT3,
P_REC.SEGMENT4,
P_REC.SEGMENT5,
P_REC.SEGMENT6,
P_REC.SEGMENT7

);

EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'ERROR in log_error ' || SQLERRM);
END log_error;
-- +===================================================================+
-- | Name : validate_gl_period |
-- | |
-- | Description: This Procedure shall validate GL period |
-- | |
-- +===================================================================+

PROCEDURE validate_gl_period (
p_accounting_date IN XXXX_gl_data_stg.accounting_date%TYPE,
p_sob_id IN gl_period_statuses.set_of_books_id%TYPE,
p_error_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR check_glperiod
IS
SELECT closing_status
FROM gl_period_statuses
WHERE application_id IN (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'GL')
AND set_of_books_id =p_sob_id
AND TO_CHAR (p_accounting_date, 'DD-MON-RRRR') BETWEEN start_date AND end_date
AND NVL (closing_status, '*') = 'O';

x_closing_status gl_period_statuses.closing_status%TYPE := NULL;
BEGIN
IF (check_glperiod%ISOPEN)
THEN
CLOSE check_glperiod;
END IF;

OPEN check_glperiod;

FETCH check_glperiod
INTO x_closing_status;

CLOSE check_glperiod;

IF x_closing_status <> 'O'
THEN
p_error_msg:='GL Period is not opened for Accounting Date:-'||p_accounting_date;
END IF;
EXCEPTION
WHEN OTHERS
THEN
IF (check_glperiod%ISOPEN)
THEN
CLOSE check_glperiod;
END IF;
fnd_file.put_line (fnd_file.LOG, 'ERROR in validate_gl_period ' || SQLERRM);
END validate_gl_period;

-- +===================================================================+
-- | Name : VALIDATE_RECORDS_P |
-- | |
-- | Description: This Procedure shall validate Record |
-- | |
-- +===================================================================+
PROCEDURE VALIDATE_RECORDS_P(p_stg_rec IN XXXX_gl_data_stg%ROWTYPE,
p_error_count OUT NUMBER
)
IS
l_ledger_id gl_ledgers.ledger_id%TYPE;
l_error_msg varchar2(4000);
P_ERR_REC XXXX_gl_errors_stg%ROWTYPE;
l_exist_cnt Number := 0;
l_ccid varchar2(2000) :=0;
l_budget_ver_id gl_budget_versions.budget_version_id%type;
BEGIN

dbms_output.put_line('10 inside validate');

--- Validates the currency code
l_exist_cnt :=0;
SELECT count(1)
INTO l_exist_cnt
FROM apps.gl_currencies GC
WHERE GC.currency_code = p_stg_rec.currency_code;

IF l_exist_cnt < 1 then
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid Currency Code :'||p_stg_rec.currency_code;
P_ERR_REC.ERROR_MESSAGE := l_error_msg||'invalid currency code '||p_stg_rec.currency_code;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;
dbms_output.put_line('20 inside validate');
--- Validates the user je category name
l_exist_cnt :=0;
SELECT COUNT(1) into
l_exist_cnt
FROM gl_je_categories GJE
WHERE GJE.user_je_category_name = p_stg_rec.USER_JE_CATEGORY_NAME;
dbms_output.put_line('201 inside validate');
l_error_msg:=NULL;

IF l_exist_cnt < 1 then
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid Category Name';
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;
dbms_output.put_line('30 inside validate');
--- Validate ledger_id
l_error_msg:=NULL;
get_ledger_id(p_stg_rec.ledger_name,
l_ledger_id ,
l_error_msg
);

IF l_error_msg is not null then
p_error_count:=nvl(p_error_count,0)+1;

--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid Ledger:'||p_stg_rec.ledger_name;
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;

--- Validate Budget Name
dbms_output.put_line('40 inside validate');
IF nvl(gv_conv_type,0) = 2
THEN

l_budget_ver_id := 0;

get_budget_version_id_p( p_ledger_id => l_ledger_id
,p_budget_name => p_stg_rec.budget_version_id
,x_budget_ver_id => l_budget_ver_id
,x_error_msg => l_error_msg);

IF l_error_msg is not null then
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid Budget :'||p_stg_rec.budget_version_id;
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;
END IF;
dbms_output.put_line('50 inside validate');
--- Validate gl period
l_error_msg:=NULL;
IF nvl(l_ledger_id,0) != 0
Then
validate_gl_period (p_stg_rec.accounting_date,
l_ledger_id,
l_error_msg
);
END IF;
IF l_error_msg is not null then
p_error_count:=nvl(p_error_count,0)+1;

--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid GL Period:'||p_stg_rec.accounting_date;
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;

--- Validate CCID
l_ccid :=0;
l_ccid := XXXX_UTILITY_PKG.get_r12_ccid( p_datasource =>p_stg_rec.source_system,
p_sob_name => p_stg_rec.ledger_name,
p_segment1 => p_stg_rec.segment1,
p_segment2 => p_stg_rec.segment2,
p_segment3 => p_stg_rec.segment3,
p_segment4 => p_stg_rec.segment4,
p_segment5 => p_stg_rec.segment5,
p_segment6 => p_stg_rec.segment6,
p_segment7 => p_stg_rec.segment7,
p_errmsg => l_error_msg);
IF l_ccid = 0 then
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid Segment Names :';
P_ERR_REC.ERROR_MESSAGE := l_error_msg||'Value provided for Segments is not valid :'||
p_stg_rec.segment1||'-'||
p_stg_rec.segment2||'-'||
p_stg_rec.segment3||'-'||
p_stg_rec.segment4||'-'||
p_stg_rec.segment5||'-'||
p_stg_rec.segment6||'-'||
p_stg_rec.segment7;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;

--- Validates the user je Source name
l_exist_cnt :=0;
SELECT COUNT(1) into
l_exist_cnt
FROM gl_je_sources GJE
WHERE GJE.USER_JE_SOURCE_NAME = p_stg_rec.USER_JE_SOURCE_NAME;
dbms_output.put_line('201 inside validate');
l_error_msg:=NULL;

IF l_exist_cnt < 1 then
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Invalid User Source Name';
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;
--- Validates the Accounted Debit and Accounted Credit
l_exist_cnt :=0;

l_error_msg:=NULL;

IF NVL(G_ACCOUNT_AMT,0) = 999999999999 THEN
SELECT SUM(NVL(accounted_dr,0))-SUM(NVL(accounted_cr,0))
INTO G_ACCOUNT_AMT
FROM XXXX_gl_data_stg
WHERE NVL(REC_TYPE,'X')='D'
AND conc_request_id=g_con_request_id
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%';

If NVL(G_ACCOUNT_AMT,0) <> 0 AND p_stg_rec.BUDGET_VERSION_ID IS NULL
Then
l_error_msg := ' TOTAL Accounted Debit and Accounted Credit Amount is not Matching.?';
p_error_count:=nvl(p_error_count,0)+1;
--log error
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Total of Dr/Cr. Amount Mismatch';
P_ERR_REC.ERROR_MESSAGE := l_error_msg;
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('60 inside validate');
p_error_count:=nvl(p_error_count,0)+1;
P_ERR_REC.RECORD_ID := p_stg_rec.RECORD_ID;
P_ERR_REC.BATCH_NAME := p_stg_rec.BATCH_NAME;
P_ERR_REC.BATCH_DATE := p_stg_rec.BATCH_DATE;
P_ERR_REC.LEDGER_NAME := p_stg_rec.LEDGER_NAME;
P_ERR_REC.ACCOUNTING_DATE := p_stg_rec.ACCOUNTING_DATE;
P_ERR_REC.USER_JE_CATEGORY_NAME := p_stg_rec.USER_JE_CATEGORY_NAME;
P_ERR_REC.USER_JE_SOURCE_NAME := p_stg_rec.USER_JE_SOURCE_NAME;
P_ERR_REC.segment1 := p_stg_rec.SEGMENT1;
P_ERR_REC.segment2 := p_stg_rec.SEGMENT2;
P_ERR_REC.segment3 := p_stg_rec.SEGMENT3;
P_ERR_REC.segment4 := p_stg_rec.SEGMENT4;
P_ERR_REC.segment5 := p_stg_rec.SEGMENT5;
P_ERR_REC.segment6 := p_stg_rec.SEGMENT6;
P_ERR_REC.segment7 := p_stg_rec.SEGMENT7;
P_ERR_REC.ERROR_TYPE := 'Custom Validattion Error';
P_ERR_REC.ERROR_CODE := 'Exception :'||sqlcode;
P_ERR_REC.ERROR_MESSAGE := l_error_msg||substr(sqlerrm,1,230);
P_ERR_REC.CONC_REQUEST_ID := g_con_request_id;
log_error (P_ERR_REC);
END VALIDATE_RECORDS_P;

-- +===================================================================+
-- | Name : PROCESS_MAIN_P |
-- | |
-- | Description: This Procedure is Main Program for Processing |
-- | It will call all the sub concurrent programs |
-- +===================================================================+

PROCEDURE PROCESS_MAIN_P
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_conv_type IN NUMBER DEFAULT 0 --- Flag 0 for Balance , Flag 1 for Journals , Flag 2 for Budget
)
IS
l_error_count Number :=0;
l_ret_insrt BOOLEAN;
p_exception EXCEPTION;
l_error_msg Varchar2(200);
BEGIN
gv_conv_type := nvl(p_conv_type,1);--Written by Pawan on 03/25/2010 to fix the golb al variable assignment issue

SELECT conc_request_id
INTO g_con_request_id
FROM XXXX_GL_DATA_STG
where conc_request_id is not null
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%'
and rownum <2;

for gl_stg_rec IN gl_stg('V')
loop
----------- POPULATING GL_INTERFACE TABLE.
xxglstgrec := gl_stg_rec;
l_ret_insrt := INSERT_IFACE_P(xxglstgrec);
IF NOT l_ret_insrt THEN
EXIT;
END IF;
end loop;

If l_ret_insrt THEN
COMMIT;
write_log_p (gv_debug_flag,'All Records Inserted in GL_INTERFACE Table!!');
write_log_p (gv_debug_flag,'Calling Journal IMPORT PROGRAM............!!');
PROCESS_JOURNALIMPORT_P(p_conv_type);
Else
raise p_exception;
End if;
EXCEPTION
WHEN p_exception THEN
ROLLBACK;
write_log_p (gv_debug_flag, 'Unable to Insert into Std. Interface Table :');
errbuf := 'Unable to Insert into Std. Interface Table';
retcode := -1;
WHEN OTHERS THEN
ROLLBACK;
write_log_p (gv_debug_flag,l_error_count ||': ERRORS IN PROCESS_MAIN_P :'||SUBSTR(SQLERRM,1,200));
END PROCESS_MAIN_P;

-- +===================================================================+
-- | Name : PROCESS_JOURNALIMPORT_P |
-- | |
-- | Description: This Procedure is Main Program for JOURNAL Import |
-- +===================================================================+

PROCEDURE PROCESS_JOURNALIMPORT_P
(
p_conv_type IN NUMBER DEFAULT 0 --- Flag 0 for Balance , Flag 1 for Journals , Flag 2 for Budget
)
IS
lv_error_message VARCHAR2 (4000) := NULL;
lc_return_code VARCHAR2 (10);
ln_load_request_id NUMBER;
ln_create_request_id NUMBER;
lv_phase VARCHAR2 (10);
ln_wait_count NUMBER;
lv_status VARCHAR2 (20) := NULL;
lv_mesg VARCHAR2 (4000) := NULL;
lb_req_wait BOOLEAN;
lv_dev_phase VARCHAR2 (20) := NULL;
lv_dev_status VARCHAR2 (20);
l_error_count Number :=0;
ln_irun_id number;
p_exception EXCEPTION;
l_error_msg Varchar2(200);
l_ledger_id NUMBER;
CURSOR C_BATCH_REC IS
SELECT DISTINCT ledger_name,user_je_source_name
FROM XXXX_gl_data_stg
WHERE NVL(REC_TYPE,'X')='D'
AND PROCESS_FLAG='V'
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%'
AND conc_request_id=g_con_request_id;
l_interface_run_id NUMBER;
l_group_id NUMBER;

BEGIN
for glbth IN C_BATCH_REC
loop
----------- POPULATING GL_INTERFACE_CONTROL TABLE.
get_ledger_id (glbth.ledger_name,
l_ledger_id,
l_error_msg
);

gl_journal_import_pkg.populate_interface_control(user_je_source_name => glbth.user_je_source_name
,group_id => l_group_id
,set_of_books_id => l_ledger_id
,interface_run_id => l_interface_run_id);

UPDATE GL_INTERFACE
SET GROUP_ID=l_group_id
WHERE USER_JE_SOURCE_NAME=GLBTH.USER_JE_SOURCE_NAME
AND GROUP_ID IS NULL
AND LEDGER_ID=l_ledger_id;

COMMIT;
write_log_p (gv_debug_flag, 'Submitting Std GL Import Program For Batch : '||GLBTH.USER_JE_SOURCE_NAME);
ln_irun_id := fnd_request.submit_request(
application => 'SQLGL'
,program => 'GLLEZL'
,description => NULL
,start_time => SYSDATE
,sub_request => FALSE
,argument1 => to_char(l_interface_run_id)
,argument2 => l_ledger_id
,argument3 => 'N'
,argument4 => NULL
,argument5 => NULL
,argument6 => 'N'
,argument7 => 'W');
write_log_p (gv_debug_flag,'Submitted JOURNAL Import Program For Batch : '||GLBTH.USER_JE_SOURCE_NAME||'Request ID : '||ln_irun_id);

IF ln_irun_id > 0
THEN
COMMIT;
write_log_p (gv_debug_flag, 'Request Id: '
|| ln_irun_id
);
--Waiting for the request to complete
ln_wait_count := 0;
lv_dev_phase := NULL;
lv_phase := NULL;

LOOP
ln_wait_count := ln_wait_count + 1;
lb_req_wait :=
fnd_concurrent.wait_for_request
(request_id => ln_irun_id,
INTERVAL => 1,
max_wait => 1,
phase => lv_phase,
status => lv_status,
dev_phase => lv_dev_phase,
dev_status => lv_dev_status,
MESSAGE => lv_mesg
);

IF ( (UPPER (lv_dev_phase) = 'COMPLETE')
OR (UPPER (lv_phase) = 'COMPLETED')
)
THEN
write_log_p (gv_debug_flag, 'Request_id:'
|| ln_irun_id
|| ', lv_dev_phase: '
|| lv_dev_phase
|| ',lv_phase: '
|| lv_phase
);
write_log_p (gv_debug_flag, 'COMPLETED JOURNAL Import Program For Batch : '||GLBTH.USER_JE_SOURCE_NAME
|| ' STATUS :'||UPPER(lv_status));

COMMIT;
EXIT;
END IF;
END LOOP;
END IF; --ln_load_request_id

end loop; --- Main Loop

/*************************
Posting
v_chart_of_Accounts_id := 101;

select set_of_books_id
into v_set_of_books_id
from financials_system_parameters;

select gl_je_posting_s.NextVal
into v_posting_run_id
from dual;
ln_req_id := Fnd_Request.SUBMIT_REQUEST('SQLGL', 'GLPPOS', 'Gastos', NULL, FALSE,
to_char(v_set_of_Books_id), to_char(v_chart_of_Accounts_id),
to_char(v_posting_run_id));
***********************/
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
write_log_p (gv_debug_flag,l_error_count ||': Unable to Run Journal Import Program :');
END PROCESS_JOURNALIMPORT_P;

-- +===================================================================+
-- | Name : VALIDATE_MAIN_P |
-- | |
-- | Description: This Procedure is Main Program for validate |
-- | It will |
-- +===================================================================+

PROCEDURE VALIDATE_MAIN_P
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_conv_type IN NUMBER DEFAULT 0 --- Flag 0 for Balance , Flag 1 for Journals , Flag 2 for Budget
)

IS
x_error_count NUMBER :=0;
l_error_count NUMBER :=0;
l_stg_rec XXXX_gl_data_stg%ROWTYPE;
BEGIN

gv_debug_flag :='Y';
write_log_p (gv_debug_flag,l_error_count ||': concurrent request id in Validate :'||g_con_request_id);

SELECT conc_request_id
INTO g_con_request_id
FROM XXXX_GL_DATA_STG
where conc_request_id is not null
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%'
and rownum <2;
for gl_stg_rec IN gl_stg('N') loop
l_stg_rec := gl_stg_rec;
---Call Vaildate Program
VALIDATE_RECORDS_P(l_stg_rec,x_error_count);
if x_error_count > 0 then
update XXXX_gl_data_stg
set PROCESS_FLAG ='E'
where record_id=gl_stg_rec.record_id
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%';
l_error_count := l_error_count+ nvl(x_error_count,0);
else
update XXXX_gl_data_stg
set PROCESS_FLAG ='V'
where record_id=gl_stg_rec.record_id
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%';
end IF;
end loop;

commit;
if l_error_count > 0 then
write_log_p (gv_debug_flag,l_error_count ||': ERRORS While Validating in VALIDATE_MAIN_P :');
retcode := l_error_count;
end if;
END VALIDATE_MAIN_P;

----- Main program
-- +===================================================================+
-- | Name : MAIN_JOURNAL_P |
-- | |
-- | Description: This Procedure is Main Program for SUBMITING |
-- | IMPORT/VALIDATE/JOURNAL IMPORT PROGRAM |
-- +===================================================================+
PROCEDURE MAIN_JOURNAL_P
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_file_name IN VARCHAR2,
p_conv_type IN NUMBER DEFAULT 0, --- Flag 0 for Balance , Flag 1 for Journals , Flag 2 for Budget
p_val_import IN VARCHAR2 DEFAULT 'I'
)
IS
ex_main EXCEPTION;
lv_error_message VARCHAR2 (4000) := NULL;
lc_return_code VARCHAR2 (10);
ln_load_request_id NUMBER;
ln_create_request_id NUMBER;
lv_phase VARCHAR2 (10);
ln_wait_count NUMBER;
lv_status VARCHAR2 (20) := NULL;
lv_mesg VARCHAR2 (4000) := NULL;
lb_req_wait BOOLEAN;
lv_dev_phase VARCHAR2 (20) := NULL;
lv_dev_status VARCHAR2 (20);
l_error_count NUMBER :=0;

BEGIN

gv_debug_flag := 'Y';
gv_conv_type := nvl(p_conv_type,1);

gv_val_import := nvl(p_val_import,'I');

IF p_conv_type = 0 THEN
gv_object_name := 'XXXX: GL Balance Conversion Main';
ELSIF p_conv_type = 1 THEN
gv_object_name := 'XXXX: GL Journals Conversion Main';
ELSE
gv_object_name := 'XXXX: GL Budget Conversion Main';
END IF;
-- --------------------------------------
-- DISPLAY PROJECT NAME AND PROGRAM NAME
-- --------------------------------------
write_log_p (gv_debug_flag,RPAD ('-', 80, '-'));
write_log_p (gv_debug_flag,RPAD ('Concurrent Program Name : ',30)|| gv_object_name);
write_log_p (gv_debug_flag,RPAD('Concurrent Request ID : ',30)|| gn_request_id);
write_log_p (gv_debug_flag,RPAD ('User ID : ', 30)|| gn_user_id);
write_log_p (gv_debug_flag,RPAD ('Date : ', 30) || gd_sysdate);
write_log_p (gv_debug_flag,RPAD ('-', 80, '-'));

--------------------------------------------------------------------
-- Submit the Loader program to load the data
--------------------------------------------------------------------
IF p_conv_type = 0 OR p_conv_type = 1
THEN
ln_load_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => 'XXXX_GL_JOURNAL_CONV_LOAD',
sub_request => FALSE,
argument1 => p_file_name
);
write_log_p (gv_debug_flag, 'gl JOURNAL Request Id: '
|| ln_load_request_id
);

ELSE
ln_load_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => 'XXXX_GL_BUDGET_CONV_LOAD',
sub_request => FALSE,
argument1 => p_file_name
);
write_log_p (gv_debug_flag, 'gl Budget Request Id: '
|| ln_load_request_id
);

END IF ;

g_con_request_id := ln_load_request_id;

IF ln_load_request_id > 0
THEN
COMMIT;
write_log_p (gv_debug_flag, 'Request Id: '
|| ln_load_request_id
);
--Waiting for the request to complete
ln_wait_count := 0;
lv_dev_phase := NULL;
lv_phase := NULL;

LOOP
ln_wait_count := ln_wait_count + 1;
lb_req_wait :=
fnd_concurrent.wait_for_request
(request_id => ln_load_request_id,
INTERVAL => 1,
max_wait => 1,
phase => lv_phase,
status => lv_status,
dev_phase => lv_dev_phase,
dev_status => lv_dev_status,
MESSAGE => lv_mesg
);

IF ( (UPPER (lv_dev_phase) = 'COMPLETE')
OR (UPPER (lv_phase) = 'COMPLETED')
)
THEN
write_log_p (gv_debug_flag, 'Request_id:'
|| ln_load_request_id
|| ', lv_dev_phase: '
|| lv_dev_phase
|| ',lv_phase: '
|| lv_phase
);
UPDATE XXXX_GL_DATA_STG
SET process_flag ='N',
conc_request_id = ln_load_request_id,
created_by = 1,
last_update_date = sysdate,
last_updated_by = 1
,org_id = fnd_global.org_id
WHERE conc_request_id IS NULL
and USER_JE_SOURCE_NAME NOT LIKE 'OR: ORACLE TRANSFER%';
COMMIT;
EXIT;
END IF;
END LOOP;
END IF; --ln_load_request_id

----------------------------------------------------------------
-- Now call the program for validating and processing the GL
-- Submit the program
----------------------------------------------------------------

IF ( (UPPER (lv_phase) = 'COMPLETED')
AND (UPPER (lv_status) = 'NORMAL')
)
THEN
ln_load_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => 'XXXX_GL_JOURNAL_CONV_VALIDATE',
sub_request => FALSE,
argument1 => p_conv_type
);

-----------------------------------------
-- Wait till the load program finishes
-----------------------------------------
IF ln_load_request_id > 0
THEN
COMMIT;
write_log_p (gv_debug_flag, 'Request Id: '
|| ln_load_request_id
);
--Waiting for the request to complete
ln_wait_count := 0;
lv_dev_phase := NULL;
lv_phase := NULL;

LOOP
ln_wait_count := ln_wait_count + 1;
lb_req_wait :=
fnd_concurrent.wait_for_request
(request_id => ln_load_request_id,
interval => 1,
max_wait => 1,
phase => lv_phase,
status => lv_status,
dev_phase => lv_dev_phase,
dev_status => lv_dev_status,
message => lv_mesg
);

IF ( (UPPER (lv_dev_phase) = 'COMPLETE')
OR (UPPER (lv_phase) = 'COMPLETED')
)
THEN
write_log_p (gv_debug_flag, 'Request_id: '
|| ln_load_request_id
|| ', lv_dev_phase: '
|| lv_dev_phase
|| ',lv_phase: '
|| lv_phase
);
EXIT;
END IF;
END LOOP;
END IF;

END IF;
----------------------------------------------------------------
-- Now call the program for inserting into std interface table
-- and processing the GL by Calling Std. GL Import program.
----------------------------------------------------------------
--Populate interface tables if error count is 0
SELECT count(1) INTO
l_error_count
FROM XXXX_GL_ERRORS_STG ERRSTG
WHERE ERRSTG.CONC_REQUEST_ID=g_con_request_id;

IF gv_val_import ='I'
AND ( (UPPER (lv_phase) = 'COMPLETED')
AND (UPPER (lv_status) = 'NORMAL')
)
AND ( NVL(l_error_count,0) = 0 )
THEN
ln_load_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => 'XXXX_GL_JOURNAL_CONV_CONVERT',
sub_request => FALSE,
argument1 => p_conv_type
);

-----------------------------------------
-- Wait till the load program finishes
-----------------------------------------
IF ln_load_request_id > 0
THEN
COMMIT;
write_log_p (gv_debug_flag, 'Request Id: '
|| ln_load_request_id
);
--Waiting for the request to complete
ln_wait_count := 0;
lv_dev_phase := NULL;
lv_phase := NULL;

LOOP
ln_wait_count := ln_wait_count + 1;
lb_req_wait :=
fnd_concurrent.wait_for_request
(request_id => ln_load_request_id,
interval => 1,
max_wait => 1,
phase => lv_phase,
status => lv_status,
dev_phase => lv_dev_phase,
dev_status => lv_dev_status,
message => lv_mesg
);

IF ( (UPPER (lv_dev_phase) = 'COMPLETE')
OR (UPPER (lv_phase) = 'COMPLETED')
)
THEN
write_log_p (gv_debug_flag, 'Request_id: '
|| ln_load_request_id
|| ', lv_dev_phase: '
|| lv_dev_phase
|| ',lv_phase: '
|| lv_phase
);
EXIT;
END IF;
END LOOP;
END IF;
END IF;

EXCEPTION
WHEN ex_main THEN
retcode := 1;
WHEN OTHERS THEN
lv_error_message :=
'Unexpected error in the procedure main : ' || SQLERRM;
errbuf := lv_error_message;
write_log_p (gv_debug_flag,errbuf || lv_error_message);
retcode := 2;
END main_journal_p;
END XXXX_GL_JOURNAL_CONV_PK;
/

Oracle Apps World

Pages

Validations/Code for GL Journal Import

No comments:

Post a Comment