CREATE OR REPLACE PACKAGE xxfa_gl_camra_pkg
IS
PROCEDURE xxfa_gl_camra_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER);
END xxfa_gl_camra_pkg;
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY xxfa_gl_camra_pkg
IS
l_n_user_id NUMBER := fnd_global.user_id;
l_n_request_id NUMBER := fnd_global.conc_request_id;
l_c_gl_app_short_name CONSTANT VARCHAR2 (10) := 'SQLGL';
l_c_gl_flex_code CONSTANT VARCHAR2 (10) := 'GL#';
l_c_gl_flex_structure_code CONSTANT VARCHAR2 (30)
:= 'ACCOUNTING_FLEXFIELD';
l_err_msg VARCHAR2 (4000);
------ Procedure -----------
PROCEDURE xxfa_gl_camra_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_chart_of_accounts_id NUMBER;
v_stg_status VARCHAR2 (100);
--v_gl_segment_value FND_FLEX_EXT.segmentarray;
v_x_oracle_string VARCHAR2 (4000);
v_segment_con_seg VARCHAR2 (4000);
v_n_code_combination_id VARCHAR2 (4000);
v_status_flag VARCHAR2 (3);
v_rows NUMBER (10) := 0;
v_insert_rows NUMBER (10) := 0;
v_error_rows NUMBER (10) := 0;
v_enter_cr NUMBER (10) := 0;
v_enter_dr NUMBER (10) := 0;
v_error_enter_dr NUMBER (10) := 0;
v_error_enter_cr NUMBER (10) := 0;
v_total_dr NUMBER (15) := 0;
v_total_cr NUMBER (15) := 0;
CURSOR c1
IS
SELECT xgcst.ROWID row_id, ROWNUM, xgcst.*
FROM xxfa_gl_camra_stg_tab xgcst
WHERE 1 = 1 AND stg_status IN ('New', 'Error');
TYPE gl_data_tbl IS TABLE OF xxfa_gl_camra_stg_tab_v%ROWTYPE
INDEX BY BINARY_INTEGER;
rec_gl_balances gl_data_tbl;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN -- Procedure begin
SELECT gsob.chart_of_accounts_id -- To Get the Chart of Accounts id
INTO v_chart_of_accounts_id
FROM gl_sets_of_books gsob
WHERE gsob.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
OPEN c1; --Open The Cursor c1
LOOP
FETCH c1
BULK COLLECT INTO rec_gl_balances;
--Move the data from cursor c1to pl/sql table type variable.
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1; -- Close the Cursor c1
FOR i IN 1 .. rec_gl_balances.COUNT
LOOP
v_rows := v_rows + 1;
v_segment_con_seg :=
rec_gl_balances (i).segment1
|| '.'
|| -- Concatinated the all segments
rec_gl_balances (i).segment2
|| '.'
|| rec_gl_balances (i).segment3
|| '.'
|| rec_gl_balances (i).segment4
|| '.'
|| '00000'
|| '.'
|| '0'
|| '.'
|| '0000'
|| '.'
|| '00'
|| '.'
|| '00';
-- using api FND_FLEX_EXT.GET_CCID to check the valid combination id .
v_n_code_combination_id :=
fnd_flex_ext.get_ccid (l_c_gl_app_short_name,
'GL#',
v_chart_of_accounts_id,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
v_segment_con_seg
);
--Fnd_File.Put_Line(fnd_File.LOG, 'CCID ' || v_n_Code_Combination_Id);
IF v_n_code_combination_id = 0
THEN
l_err_msg := fnd_message.get;
v_stg_status := 'Error';
v_status_flag := 'E';
-- Set the Status flag E if it is invalid combination id .
fnd_file.put_line (fnd_file.LOG,
'Message:'
|| l_err_msg
|| '---'
|| 'Combinationid:'
|| v_segment_con_seg
);
ELSE -- Set the Status flag P if it is valid combination id .
--DBMS_OUTPUT.PUT_LINE( 'finalCCID not 0 ' || v_n_Code_Combination_Id);
v_stg_status := 'Process Completed';
v_status_flag := 'P';
END IF;
--fnd_file.put_line(fnd_file.log,v_x_Oracle_String);
--fnd_file.put_line(fnd_file.log,fnd_message.get);
IF v_status_flag = 'E'
THEN
-- IF Record is errout then update the stage table with error status.
v_error_rows := v_error_rows + 1;
UPDATE xxfa_gl_camra_stg_tab xgcst
SET stg_status = v_stg_status,
err_description = l_err_msg,
request_id = l_n_request_id,
last_updated_by = l_n_user_id,
last_updated_date = SYSDATE
WHERE xgcst.ROWID = rec_gl_balances (i).row_id;
v_error_enter_dr :=
v_error_enter_dr + rec_gl_balances (i).entered_dr;
v_error_enter_cr :=
v_error_enter_cr + rec_gl_balances (i).entered_cr;
ELSE
-- IF record is not errorout then Inserting data into interface table
INSERT INTO gl_interface
(status,
accounting_date,
segment1,
segment2,
segment3,
segment4, segment5, segment6, segment7,
segment8, segment9, entered_dr,
entered_cr,
set_of_books_id,
currency_code,
date_created, created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
accounted_dr,
accounted_cr,
GROUP_ID, request_id,
status_description
)
VALUES (rec_gl_balances (i).status,
rec_gl_balances (i).accounting_date,
rec_gl_balances (i).segment1,
rec_gl_balances (i).segment2,
rec_gl_balances (i).segment3,
rec_gl_balances (i).segment4, '00000', '0', '0000',
'00', '00', rec_gl_balances (i).entered_dr,
rec_gl_balances (i).entered_cr,
fnd_profile.VALUE ('GL_SET_OF_BKS_ID'),
rec_gl_balances (i).currency_code,
rec_gl_balances (i).date_created, l_n_user_id,
rec_gl_balances (i).actual_flag,
rec_gl_balances (i).user_je_category_name,
rec_gl_balances (i).user_je_source_name,
rec_gl_balances (i).accounted_dr,
rec_gl_balances (i).accounted_cr,
rec_gl_balances (i).GROUP_ID, l_n_request_id,
rec_gl_balances (i).status_description
);
UPDATE xxfa_gl_camra_stg_tab xgcst
-- IF Record insertd into Gl interafce then update the stage table with 'Process Completed 'status.
SET stg_status = v_stg_status,
request_id = l_n_request_id,
last_updated_by = l_n_user_id,
last_updated_date = SYSDATE
WHERE xgcst.ROWID = rec_gl_balances (i).row_id;
v_insert_rows := v_insert_rows + 1;
v_enter_dr := v_enter_dr + rec_gl_balances (i).entered_dr;
v_enter_cr := v_enter_cr + rec_gl_balances (i).entered_cr;
END IF;
COMMIT;
END LOOP;
v_total_dr := v_enter_dr + v_error_enter_dr;
v_total_cr := v_enter_cr + v_error_enter_cr;
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output,
'No of records Processed :'
|| v_rows
);
fnd_file.put_line (fnd_file.output,
'No of records inserted to GL interface table:'
|| v_insert_rows
);
fnd_file.put_line (fnd_file.output,
'No of records rejected :'
|| v_error_rows
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output,
'Total Debit Amount :'
|| v_total_dr
);
fnd_file.put_line (fnd_file.output,
'Total Credit Amount :'
|| v_total_cr
);
fnd_file.put_line (fnd_file.output,
'dr_amount enetered into interface table :'
|| v_enter_dr
);
fnd_file.put_line (fnd_file.output,
'cr_amount enetered into interface table :'
|| v_enter_cr
);
fnd_file.put_line (fnd_file.output,
'Rejected dr_amount :'
|| v_error_enter_dr
);
fnd_file.put_line (fnd_file.output,
'Rejected cr_amount :'
|| v_error_enter_cr
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'** More Information about Rejected records check the stage table XXFA_GL_CAMRA_STG_TAB'
);
fnd_file.put_line
(fnd_file.output,
' Reason for rejecting check the Error Description column **'
);
------------
fnd_file.put_line
(fnd_file.LOG,
'------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'Request id :' || l_n_request_id);
fnd_file.put_line (fnd_file.LOG, 'User id :' || l_n_user_id);
fnd_file.put_line
(fnd_file.LOG,
'------------------------------------------------------------'
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'EXCEPTION');
fnd_file.put_line
(fnd_file.LOG,
'---------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'Request id :' || l_n_request_id);
-- fnd_file.put_line (fnd_file.log,'CCID :'||v_n_Code_Combination_Id);
fnd_file.put_line (fnd_file.LOG,
'Errmsg :' || SQLCODE || ' ' || SQLERRM
);
END xxfa_gl_camra_proc; -- End procedure
END xxfa_gl_camra_pkg; -- End Pkg .
hi thank you but i`ve got this err
ReplyDeletePLS-00386 type mismatch found at rec_gl_balances between FETCH cursor and INTO variables.
Any help.