1 PACKAGE BODY PO_WF_DEBUG_PKG as
2 /* $Header: POXWDBGB.pls 120.1.12020000.2 2013/02/10 15:51:20 vegajula ship $*/
3
4 /***************************************************************************************
5 *
6 * THis package is used to track the progress of all the functions in the workflow as
7 * they are invoked by the workflow process. It captures the following:
8 * - the Process Name
9 * - the unique ID of the Item going through the process
10 * - the Package.procedure name being executed
11 * - the progress within that Package.procedure
12 *
13 ***************************************************************************************/
14
15 procedure insert_debug (itemtype varchar2,
16 itemkey varchar2,
17 x_progress varchar2) is
18
19 /* Bug# 1632741: kagarwal
20 ** Desc: Making procedure insert_debug an autonomous transaction as
21 ** there should never be a commit in any code that is called by the
22 ** workflow engine that will be executed in the same transaction.
23 */
24
25 pragma AUTONOMOUS_TRANSACTION;
26
27 l_document_id number := NULL;
28 l_document_number varchar2(25) := NULL;
29 l_preparer_id number := NULL;
30 l_approver_empid number := NULL;
31 l_Forward_to_id number := NULL;
32 l_Forward_to_username varchar2(100) := NULL;
33 l_Forward_from_id number := NULL;
34 l_Forward_from_username varchar2(100) := NULL;
35 l_Authorization_status varchar2(25) := NULL;
36
37 x_option_value varchar2(10);
38
39 BEGIN
40
41
42 /* If it's Req or PO approval then get some of the info that we want to log.
43 ** If the profile option is set , then log debug messages.
44 */
45 /* Bug 2834040 fixed. replaced the wf_engine call with po_wf_util_pkg
46 wrapper call so that debug messages will get logged inspite of
47 the workflow attributes not being set.
48 */
49 fnd_profile.get('PO_SET_DEBUG_WORKFLOW_ON',x_option_value);
50
51 IF x_option_value = 'Y' THEN
52
53 IF itemtype IN ('REQAPPRV','POAPPRV') THEN
54
55 l_document_number:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
56 itemkey => itemkey,
57 aname => 'DOCUMENT_NUMBER');
58 --
59 l_document_id:= PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemType,
60 itemkey => itemkey,
61 aname => 'DOCUMENT_ID');
62
63 l_preparer_id:= PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemType,
64 itemkey => itemkey,
65 aname => 'PREPARER_ID');
66 --
67 l_Forward_to_id:= PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
68 itemkey => itemkey,
69 aname => 'FORWARD_TO_ID');
70 --
71 l_Forward_from_id:= PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
72 itemkey => itemkey,
73 aname => 'FORWARD_FROM_ID');
74 --
75 l_Forward_to_username:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemType,
76 itemkey => itemkey,
77 aname => 'FORWARD_TO_USERNAME');
78
79 l_Forward_from_username:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemType,
80 itemkey => itemkey,
81 aname => 'FORWARD_FROM_USER_NAME');
82
83 l_approver_empid:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
84 itemkey => itemkey,
85 aname => 'APPROVER_EMPID');
86
87 l_authorization_status := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
88 itemkey => itemkey,
89 aname => 'AUTHORIZATION_STATUS');
90 END IF;
91
92 BEGIN
93
94 insert into PO_WF_DEBUG
95 (EXECUTION_SEQUENCE,
96 EXECUTION_DATE,
97 ITEMTYPE,
98 ITEMKEY,
99 DOCUMENT_ID,
100 DOCUMENT_NUMBER,
101 PREPARER_ID,
102 APPROVER_EMPID,
103 FORWARD_TO_ID,
104 FORWARD_TO_USERNAME,
105 FORWARD_FROM_ID,
106 FORWARD_FROM_USERNAME,
107 AUTHORIZATION_STATUS,
108 DEBUG_MESSAGE)
109 values(po_wf_debug_s.nextval,
110 sysdate,
111 itemtype,
112 itemkey,
113 l_document_id,
114 l_document_number,
115 l_preparer_id,
116 l_approver_empid,
117 l_Forward_to_id,
118 l_Forward_to_username,
119 l_Forward_from_id,
120 l_Forward_from_username,
121 l_Authorization_status,
122 x_progress);
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127 NULL; -- Don't raise any exceptions. Just don't log to the table.
128 END;
129
130 --Bug 14044581, log the wf debug messages into FND log table
131 --Profile PO: Set Debug Workflow ON still need to be set
132 --as existing calling procedures only call this code when the
133 --wf debug profile is set
134 IF (PO_LOG.d_stmt) THEN
135 PO_LOG.stmt('po.plsql.WF LOG TO FND LOG', NULL, 'itemtype: '||itemtype
136 ||' itemkey: '||itemkey||' Message Text: '||x_progress);
137 END IF;
138
139 COMMIT;
140
141 END IF;
142
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 NULL; -- Don't raise any exceptions. Just don't log to the table.
147 END insert_debug;
148
149 -- <R12 PO OTM Integration START>
150 PROCEDURE debug_stmt(
151 p_log_head IN VARCHAR2
152 , p_token IN VARCHAR2
153 , p_message IN VARCHAR2
154 )
155 IS
156
157 PRAGMA AUTONOMOUS_TRANSACTION;
158
159 l_option_value VARCHAR2(10);
160
161 BEGIN
162
163 FND_PROFILE.get('PO_SET_DEBUG_WORKFLOW_ON', l_option_value);
164
165 IF (l_option_value = 'Y') THEN
166 BEGIN
167 INSERT INTO po_wf_debug ( execution_sequence
168 , execution_date
169 , debug_message
170 )
171 VALUES ( po_wf_debug_s.NEXTVAL
172 , SYSDATE
173 , SUBSTRB(p_log_head || '.' || p_token || ':' || p_message, 1, 1000));
174 EXCEPTION
175 WHEN OTHERS THEN
176 NULL;
177 END;
178
179 --Bug 14044581, log the wf debug messages into FND log table
180 IF (PO_LOG.d_stmt) THEN
181 PO_LOG.stmt('po.plsql.WF LOG TO FND LOG', NULL, 'p_log_head: '||p_log_head||' p_token: '
182 ||p_token||' Message Text: '||p_message);
183 END IF;
184
185 COMMIT;
186 END IF;
187
188 END debug_stmt;
189
190 PROCEDURE debug_begin(
191 p_log_head IN VARCHAR2
192 )
193 IS
194 BEGIN
195 debug_stmt(p_log_head,'BEGIN','Entry into procedure '||p_log_head||'.');
196 END debug_begin;
197
198 PROCEDURE debug_end(
199 p_log_head IN VARCHAR2
200 )
201 IS
202 BEGIN
203 debug_stmt(p_log_head,'END','Exiting procedure '||p_log_head||' normally.');
204 END debug_end;
205
206 PROCEDURE debug_var(
207 p_log_head IN VARCHAR2
208 , p_progress IN VARCHAR2
209 , p_name IN VARCHAR2
210 , p_value IN VARCHAR2
211 )
212 IS
213 BEGIN
214 IF (p_value IS NULL) THEN
215 debug_stmt(p_log_head,p_progress,p_name||' IS NULL');
216 ELSE
217 debug_stmt(p_log_head,p_progress,p_name||' = '||p_value);
218 END IF;
219 END debug_var;
220
221 PROCEDURE debug_var(
222 p_log_head IN VARCHAR2
223 , p_progress IN VARCHAR2
224 , p_name IN VARCHAR2
225 , p_value IN NUMBER
226 )
227 IS
228 BEGIN
229 IF (p_value IS NULL) THEN
230 debug_stmt(p_log_head,p_progress,p_name||' IS NULL');
231 ELSE
232 debug_stmt(p_log_head,p_progress,p_name||' = '||TO_CHAR(p_value));
233 END IF;
234 END debug_var;
235
236
237
238 PROCEDURE debug_var(
239 p_log_head IN VARCHAR2
240 , p_progress IN VARCHAR2
241 , p_name IN VARCHAR2
242 , p_value IN DATE
243 )
244 IS
245 BEGIN
246 IF (p_value IS NULL) THEN
247 debug_stmt(p_log_head,p_progress,p_name||' IS NULL');
248 ELSE
249 debug_stmt(p_log_head,p_progress,p_name||' = '||TO_CHAR(p_value));
250 END IF;
251 END debug_var;
252
253
254 PROCEDURE debug_var(
255 p_log_head IN VARCHAR2
256 , p_progress IN VARCHAR2
257 , p_name IN VARCHAR2
258 , p_value IN BOOLEAN
259 )
260 IS
261 BEGIN
262 IF (p_value IS NULL) THEN
263 debug_stmt(p_log_head,p_progress,p_name||' IS NULL');
264 ELSIF (p_value) THEN
265 debug_var(p_log_head,p_progress,p_name,'TRUE');
266 ELSE
267 debug_var(p_log_head,p_progress,p_name,'FALSE');
268 END IF;
269 END debug_var;
270
271 PROCEDURE debug_unexp(
272 p_log_head IN VARCHAR2
273 , p_progress IN VARCHAR2
274 , p_message IN VARCHAR2
275 DEFAULT NULL
276 )
277 IS
278 BEGIN
279 debug_stmt(p_log_head,p_progress,'EXCEPTION: '||p_message||'; SQLCODE = '||
280 TO_CHAR(SQLCODE) || '; SQLERRM = ' || SQLERRM);
281 END debug_unexp;
282 -- <R12 PO OTM Integration END>
283
284
285 END PO_WF_DEBUG_PKG;
[Home] [Help]