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 .
No comments:
Post a Comment