CREATE OR REPLACE PACKAGE XX_BOE_GL_INTERFACE_PKG
AUTHID CURRENT_USER
AS ;
PROCEDURE processdata
(p_org_id in number ) ;
END XX_BOE_GL_INTERFACE_PKG;
CREATE OR REPLACE PACKAGE BODY XX_BOE_GL_INTERFACE_PKG
AS
PROCEDURE processdata (p_org_id in number)
IS
CURSOR gl_cur_new
IS
SELECT *
FROM xxsoboe_headers_all
WHERE nvl(receive_flag,'N') = 'Y'
AND nvl(gl_interface_flag,'N') = 'N';
v_currency_code1 VARCHAR2 (15);
v_currency_code2 VARCHAR2 (15);
v_sob_id VARCHAR2 (15);
v_chart_of_account_id NUMBER ;
v_user_je_category_name VARCHAR2 (25);
v_period_name1 VARCHAR2 (15);
v_period_name2 VARCHAR2 (15);
v_segment1 VARCHAR2 (15);
v_segment2 VARCHAR2 (15);
v_segment3 VARCHAR2 (15);
v_segment4 VARCHAR2 (15);
v_segment5 VARCHAR2 (15);
v_segment6 VARCHAR2 (15);
v_segment7 VARCHAR2 (15);
v_error_reason VARCHAR2 (200);
v_sum_amount VARCHAR2 (10);
v_entered_dr VARCHAR2 (15);
v_entered_cr VARCHAR2 (15);
v_conv_type VARCHAR2 (100);
e_segments_vald EXCEPTION;
e_foreign_curr EXCEPTION;
e_dr_cr EXCEPTION;
BEGIN
FOR rec_cur_new IN gl_cur_new
LOOP
-- Initializing all variables
v_currency_code1 := '';
v_currency_code2 := '';
v_sob_id := '';
v_user_je_category_name := '';
v_period_name1 := '';
v_period_name2 := '';
v_segment1 := '';
v_segment2 := '';
v_segment3 := '';
v_segment4 := '';
v_segment5 := '';
v_segment6 := '';
v_segment7 := '';
v_error_reason := '';
v_sum_amount := '';
v_entered_dr := '';
v_entered_cr := '';
v_conv_type := '';
--THIS PLSQL BLOCK FINDS SET_OF_BOOKS ID AND CHART OF ACCOUNTS ID
BEGIN
select distinct OOD.set_of_books_id
, OOD.chart_of_accounts_id
INTO v_sob_id
, v_chart_of_account_id
from org_organization_definitions OOD
where OOD.operating_unit = p_org_id ;
fnd_file.put_line (fnd_file.LOG,'Set of Books and Chart of account validated');
exception
when OTHERS then
v_err_msg := substr(sqlerrm,1,60);
fnd_file.put_line (fnd_file.LOG,'Error while validatating SOB and COA - '||v_err_msg);
p_message := 'Error while validatating SOB and COA - '||v_err_msg;
END;
--END OF SOB AND COA VALIDATIONS
-- THIS PLSQL BLOCK VALIDATES THE CURRENCY CODE
BEGIN
SELECT distinct currency_code
INTO v_currency_code
FROM apps.gl_sets_of_books
WHERE set_of_books_id = v_sob_id;
fnd_file.put_line (fnd_file.LOG,'Currency Code Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Currency Code. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF CURRENCY CODE VALIDATIONS.
-- END OF SET OF BOOKS VALIDATIONS.
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME
BEGIN
SELECT DISTINCT period_name
INTO v_period_name
FROM apps.gl_periods
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
fnd_file.put_line(fnd_file.LOG,'Period Name Validated');
EXCEPTION
WHEN OTHERS then
p_message :=
' Error while validating Period Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF PERIOD NAME VALIDATION
-- THIS PLSQL BLOCK VALIDATES PERIOD NAME STATUSES
BEGIN
SELECT period_name
INTO v_period_status
FROM apps.gl_period_statuses
WHERE trunc(sysdate) between start_date and end_date
AND period_name not like '%ADJ%';
AND set_of_books_id = v_sob_id
AND application_id = fnd_global.resp_appl_id
AND closing_status IN ('O');
fnd_file.put_line(fnd_file.LOG,'Period Name Statuses Validated');
EXCEPTION
WHEN OTHERS
THEN
p_message :=
' Error while validating Period Status. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line (fnd_file.LOG,p_message);
END;
-- END OF VALIDATING PERIOD NAME STATUSES
-- THIS PLSQL BLOCK VALIDATES THE USER JE CATEGORY NAME
BEGIN
SELECT user_je_category_name
INTO v_user_je_category_name
FROM gl_je_categories
WHERE upper(user_je_category_name) = 'ACCRUAL';
fnd_file.put_line(fnd_file.LOG,'User Category Name Validated');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.LOG,' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
p_message := ' Error while validating User Category Name. '
|| SQLERRM
|| SQLCODE;
END;
-- END OF USER JE CATEGORY NAME
-- THIS PLSQL BLOCK VALIDATES THE EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
BEGIN
select nvl(custom_duty_shipment_cost,0)
into v_entered_dr
from xx_boe_document_cost_v
where document_number = rec_cur_new.document_number;
EXCEPTION
WHEN others then
p_message :=
' Error while validating Dr & Cr Amount in Single Line. '
|| SQLERRM
|| SQLCODE;
-- CALLING THE PROCEDURE FOR INSERTING THE ERROR RECORDS IN STAGING TABLE
fnd_file.put_line(fnd_file.LOG,p_message);
END;
IF rec_cur_new.trans_type = 'PO' then
begin
select distinct HCA.absorption_acct_ccid
into ln_code_combination_id
from hl_cst_comp_mst HCM
, hl_cst_comp_mst_acct HCA
where HCM.component_id = HCA.component_id
AND upper(HCM.component_name) = 'CUSTOMS DUTY';
exception
when others then
p_message := 'Please check Custom Duty Account setup in Landed Cost');
fnd_file.put_line(fnd_file.LOG,p_message);
END;
ELSE
begin
NULL;
EXCEPTION
END;
END IF;
-- END OF VALIDATING EXISTENCE OF DR OR CR AMOUNT IN A SINGLE LINE
begin
select ledger_id
into ln_ledger_id
from gl_ledgers
where ledger_category_code = 'PRIMARY'
exception
when others then
p_message := 'Please check Primary Legder setup');
fnd_file.put_line(fnd_file.LOG,p_message);
end;
-- END OF ACCOUNTING FLEXFIELD SEGMENTS
-- INSERTING DATA INTO INTERFACE TABLE
INSERT INTO GL_INTERFACE ( status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
chart_of_accounts_id,
functional_currency_code,
transaction_date,
period_name ,
ledger_id )
VALUES ( 'NEW',
v_sob_id
trunc(SYSDATE),
v_currency_code,
SYSDATE,
fnd_profile.value('USER_ID'),
'A',
,v_user_je_category_name
'Other',
ln_code_combination_id,
,v_entered_dr
0,
v_entered_dr,
0,
v_chart_of_account_id ,
v_currency_code,
SYSDATE,
v_period_name,
ln_ledger_id
)
UPDATE xxsoboe_headers_all
SET gl_interface_flag = 'Y'
, gl_interface_date = sysdate
WHERE doc_number = rec_cur_new.doc_number;
END LOOP;
END processdata;
/*-----------------------------------------------------------------------
runInterface
Called as a concurrent program - manages the import process
-----------------------------------------------------------------------*/
END dllgl_interface_pkg;
/
No comments:
Post a Comment