[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_PROCESS_LOG
Source
1 Package Body Pqh_Gsp_process_Log as
2 /* $Header: pqgsplog.pkb 115.7 2004/01/28 07:22 vevenkat noship $ */
3
4 Procedure Start_log
5 (P_Txn_ID IN NUMBER
6 ,P_Txn_Name IN VARCHAR2
7 ,P_Module_Cd IN VARCHAR2) Is
8
9 Begin
10
11 /* This will Start the Process Log for the Given Module Code
12 the module can be AI => Approval UI
13 BP => Batch Process and
14 AI => Assignment API */
15
16 PQH_PROCESS_BATCH_LOG.START_LOG(p_batch_id => P_Txn_ID
17 ,p_module_cd => P_Module_Cd
18 ,p_log_context => P_Txn_Name
19 ,p_information_category => 'POST_PROCESS');
20
21 End Start_log;
22
23 Procedure Insert_Log
24 (p_module_cd IN Varchar2
25 ,p_txn_id IN Number
26 ,p_log_context IN Varchar2
27 ,p_master_process_log_id IN Number
28 ,p_message_text IN Varchar2
29 ,p_message_type_cd IN Varchar2
30 ,P_Information1 IN Varchar2
31 ,P_Information2 IN Varchar2
32 ,P_Information3 IN Varchar2) IS
33
34 L_Ovn Pqh_Process_Log.Object_Version_Number%TYPE;
35 L_process_log_Id Pqh_Process_log.Process_log_id%TYPE;
36 PRAGMA AUTONOMOUS_TRANSACTION;
37
38 Begin
39
40 pqh_process_log_api.create_process_log
41 (p_process_log_id => L_Process_Log_Id
42 ,p_module_cd => P_Module_Cd
43 ,p_txn_id => P_Txn_id
44 ,p_log_context => P_Log_Context
45 ,p_master_process_log_id => P_Master_Process_log_id
46 ,p_message_text => NVL(p_message_text,Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','CMPSUC'))
47 ,p_message_type_cd => P_Message_Type_Cd
48 ,p_information_category => 'GSP'
49 ,p_object_version_number => l_ovn
50 ,P_Information1 => P_Information1
51 ,P_Information2 => P_Information2
52 ,P_Information3 => P_information3
53 ,p_effective_date => sysdate);
54
55 Commit;
56
57 End Insert_Log;
58
59 PROCEDURE Log_process_Dtls(P_Master_txn_Id IN NUMBER
60 ,P_Txn_Id IN Number
61 ,p_module_cd IN Varchar2
62 ,p_Context IN varchar2 Default NULL
63 ,p_message_type_cd IN varchar2
64 ,p_message_text IN varchar2
65 ,P_Effective_Date IN Date) Is
66 Cursor Elctbl_Dtls Is
67 Select GRADE_LADDER_NAME,
68 GRADE_NAME,
69 STEP_NAME,
70 FULL_NAME
71 From PQH_GSP_ELECTABLE_CHOICE_V
72 Where Elig_Per_Elctbl_Chc_id = P_Txn_Id;
73
74 Cursor Person_Dtls is
75 Select Decode(Assgt.Grade_ladder_Pgm_id, NULL, NULL, Ben_Batch_Utils.GET_PGM_NAME(Assgt.Grade_ladder_Pgm_id,
76 Assgt.BUSINESS_GROUP_ID,
77 P_Effective_Date)),
78 Hr_general.Decode_Grade(Grade_Id),
79 pqh_gsp_utility.Get_Step_Dtls(Assgt.Assignment_id,
80 P_Effective_Date,
81 'N',
82 'CURR'),
83 Person.Full_name
84 From Per_All_Assignments_F Assgt,
85 Per_All_People_F Person
86 Where Assignment_id = P_Txn_id
87 and P_Effective_Date
88 Between Assgt.Effective_Start_Date
89 and Assgt.Effective_End_Date
90 and Assgt.Person_Id = Person.Person_id
91 and P_Effective_Date
92 Between Person.Effective_Start_Date
93 and Person.Effective_End_Date;
94
95 Cursor Grdldr Is
96 Select information5
97 From ben_copy_entity_results
98 Where copy_entity_txn_id = P_Txn_id
99 and table_alias ='PGM';
100
101 Cursor Master_Log is
102 Select Process_Log_Id
103 From Pqh_Process_Log
104 Where Txn_id = P_Master_txn_Id
105 and MODULE_CD = P_Module_Cd;
106
107 L_Grade_Ladder_Name Ben_Pgm_F.Name%TYPE;
108 l_Grade_name Per_Grades.Name%TYPE;
109 l_Step_name Per_Spinal_Points.Spinal_POint%TYPE;
110 l_Person_Name Per_All_People_F.Full_name%TYPE;
111 L_LOG_ID Pqh_Process_Log.Process_Log_Id%TYPE;
112 L_MASTER_LOG_ID Pqh_Process_Log.MASTER_PROCESS_LOG_ID%TYPE;
113 l_Message_Type Varchar2(25);
114 L_Log_Context Pqh_Process_log.Log_Context%TYPE;
115
116 Begin
117
118 If p_module_cd = 'PQH_GSP_APPROVAL_UI' Then
119
120 Open Elctbl_Dtls;
121 Fetch Elctbl_Dtls Into L_Grade_Ladder_Name, l_Grade_name, l_Step_name, L_Log_Context;
122 Close Elctbl_Dtls;
123
124 Elsif p_module_cd in ('PQH_GSP_ASSIGN_ENTL','PQH_GSP_BATCH_ENRL','PQH_GSP_DFLT_ENRL') then
125 Open Person_Dtls;
126 Fetch Person_Dtls Into L_Grade_Ladder_Name, l_Grade_name, l_Step_name, l_Log_Context;
127 Close Person_Dtls;
128
129 ElsIf P_Module_Cd in ('PQH_GSP_STGBEN','PQH_GSP_BENSTG') Then
130 if p_context is null then
131 Open Grdldr;
132 Fetch Grdldr into L_Log_Context;
133 Close Grdldr;
134 else
135 l_log_context := p_context ;
136 end If;
137 End If;
138
139 If P_Module_Cd = 'PQH_GSP_DFLT_ENRL' Then
140 Fnd_File.New_Line(WHICH => fnd_file.log);
141 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','EMPDTLS'));
142 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','NAME') || l_Log_Context);
143 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','GRDLDR') || L_Grade_Ladder_Name);
144
145 If l_Grade_name is NOT NULL Then
146 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','GRADE') || l_Grade_name);
147 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','STEP') || l_Step_name);
148 End If;
149 Ben_batch_utils.write(Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','ERRMSG') || p_message_text);
150 Return;
151 End If;
152
153 Open Master_Log;
154 Fetch Master_Log into l_Master_log_id;
155 Close Master_Log;
156
157 If P_message_type_cd = 'E' Then
158 l_Message_Type := 'ERROR';
159 Elsif P_message_type_cd = 'C' Then
160 l_Message_Type := 'COMPLETE';
161 End If;
162
163 Insert_log(p_module_cd => P_Module_Cd
164 ,p_txn_id => P_Txn_id
165 ,p_log_context => l_Log_Context
166 ,p_master_process_log_id => l_Master_log_id
167 ,p_message_text => NVL(p_message_text,Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','CMPSUC'))
168 ,p_message_type_cd => l_Message_Type
169 ,P_Information1 => L_Grade_Ladder_name
170 ,P_Information2 => L_Grade_name
171 ,P_Information3 => L_Step_Name);
172
173 End;
174
175 End Pqh_Gsp_process_Log;