1 PACKAGE GL_AUTO_ALLOC_WF_PKG AUTHID CURRENT_USER AS
2 /* $Header: glwfalcs.pls 120.7 2005/05/05 02:08:06 kvora ship $ */
3
4 -- Determines if debugging mesg and trace file is created
5 diagn_debug_msg_flag BOOLEAN := FALSE;
6
7 -- Public variables
8 p_item_type Varchar2(10) := 'GLALLOC';
9 p_item_key Varchar2(40) ;
10 G_FILE Varchar2(255) := null;
11 G_DIR Varchar2(255)
12 := nvl(fnd_profile.value ('UTL_FILE_LOG'), NULL);
13 G_FILE_PTR utl_file.file_type;
14 err_name Varchar2(30);
15 err_msg Varchar2(2000);
16 err_stack Varchar2(32000);
17
18 -- Procedure
19 -- Start_AutoAllocation_Workflow
20 -- Purpose
21 -- Called from AutoAllocation launcher concurrent program (glalgen.sql )
22 -- Create work flow process and Start work flow
23 -- Arguments
24 -- Request_Id of AutoAllocation concurrent program
25 -- Example
26 -- gl_auto_allocation_wf_pkg.Start_AutoAllocation_Workflow;
27 -- Notes
28 --
29
30 PROCEDURE Start_AutoAllocation_Workflow(p_request_Id IN NUMBER);
31
32
33 -- Procedure
34 -- set_wf_variables
35 -- Purpose
36 -- Called from Start_AutoAllocation_Workflow
37 -- Initilize all work flow item attributes
38 -- Arguments
39 -- Item key (which is a request_id) of workflow process
40
41 PROCEDURE Create_And_Start_Wf( p_request_Id IN NUMBER );
42
43 -- Procedure
44 -- Next_Step_Type
45 -- Purpose
46 -- Called from Workflow activity
47 -- Determines what is the batch type of the step
48
49 Procedure Next_Step_Type(p_item_type IN VARCHAR2,
50 p_item_key IN VARCHAR2,
51 p_actid IN NUMBER,
52 p_funcmode IN VARCHAR2,
53 p_result OUT NOCOPY VARCHAR2);
54
55 -- Procedure
56 -- Find_Je_Batch_Type
57 -- Purpose
58 -- Called from Workflow activity
59 -- Determines what is the batch type of the GL batch
60
61
62 Procedure Find_Je_Batch_Type(p_item_type IN VARCHAR2,
63 p_item_key IN VARCHAR2,
64 p_actid IN NUMBER,
65 p_funcmode IN VARCHAR2,
66 p_result OUT NOCOPY VARCHAR2);
67
68 -- Procedure
69 -- Is_Review_Required
70 -- Purpose
71 -- Called from Workflow activity
72 -- Determines whether generated JE batch required review before posting
73
74 Procedure Is_Review_Required(p_item_type IN VARCHAR2,
75 p_item_key IN VARCHAR2,
76 p_actid IN NUMBER,
77 p_funcmode IN VARCHAR2,
78 p_result OUT NOCOPY VARCHAR2);
79
80 -- Procedure
81 -- Is_Approval_Required
82 -- Purpose
83 -- Called from Workflow activity
84 -- Determines whether generated JE batch required approval before posting
85 Procedure Is_Approval_Required(p_item_type IN VARCHAR2,
86 p_item_key IN VARCHAR2,
87 p_actid IN NUMBER,
88 p_funcmode IN VARCHAR2,
89 p_result OUT NOCOPY VARCHAR2);
90
91 -- Procedure
92 -- Is_Batch_Approved
93 -- Purpose
94 -- Called from Workflow activity
95 -- Determines whether generated JE batch is approved or not
96 Procedure Is_Batch_Approved(p_item_type IN VARCHAR2,
97 p_item_key IN VARCHAR2,
98 p_actid IN NUMBER,
99 p_funcmode IN VARCHAR2,
100 p_result OUT NOCOPY VARCHAR2);
101
102
103 -- Procedure
104 -- Launch_JE_Approval
105 -- Purpose
106 -- Called from Workflow activity
107 -- Launched Journal Approval Workflow process
108
109 Procedure Launch_JE_Approval(p_item_type IN VARCHAR2,
110 p_item_key IN VARCHAR2,
111 p_actid IN NUMBER,
112 p_funcmode IN VARCHAR2,
113 p_result OUT NOCOPY VARCHAR2);
114
115 -- Procedure
116 -- Is_Batch_Generated
117 -- Purpose
118 -- Called from Workflow activity
119 -- Determines whether any JE batch is generated by generation program
120 Procedure Is_Batch_Generated(p_item_type IN VARCHAR2,
121 p_item_key IN VARCHAR2,
122 p_actid IN NUMBER,
123 p_funcmode IN VARCHAR2,
124 p_result OUT NOCOPY VARCHAR2);
125
126 -- Select_And_Validate_Batch
127 -- Purpose
128 -- Called from Workflow activity
129 -- Validates batch status before posting
130 Procedure Select_And_Validate_Batch(p_item_type IN VARCHAR2,
131 p_item_key IN VARCHAR2,
132 p_actid IN NUMBER,
133 p_funcmode IN VARCHAR2,
134 p_result OUT NOCOPY VARCHAR2);
135
136 -- Is_Batch_Posted
137 -- Purpose
138 -- Called from Workflow activity
139 -- Determines whether batch is posted successfully
140
141 Procedure Is_Batch_Posted(p_item_type IN VARCHAR2,
142 p_item_key IN VARCHAR2,
143 p_actid IN NUMBER,
144 p_funcmode IN VARCHAR2,
145 p_result OUT NOCOPY VARCHAR2);
146
147 -- Delete_Batch
148 -- Purpose
149 -- Called from Workflow activity
150 -- Delete batch generated but not posted during rollback process
151
152 Procedure Delete_Batch(p_item_type IN VARCHAR2,
153 p_item_key IN VARCHAR2,
154 p_actid IN NUMBER,
155 p_funcmode IN VARCHAR2,
156 p_result OUT NOCOPY VARCHAR2);
157
158 -- Are_More_JE_Reverse
159 -- Purpose
160 -- Called from Workflow activity
161 -- Called during rollback process.
162 -- Is there more batches left which needs to be reversed
163
164 Procedure Are_More_JE_Reverse(p_item_type IN VARCHAR2,
165 p_item_key IN VARCHAR2,
166 p_actid IN NUMBER,
167 p_funcmode IN VARCHAR2,
168 p_result OUT NOCOPY VARCHAR2);
169
170 -- Is_Posting_Required
171 -- Purpose
172 -- Called from Workflow activity
173 -- Called during rollback process.
174 -- Any reversed batch needs to be posted
175
176 Procedure Is_Posting_Required(p_item_type IN VARCHAR2,
177 p_item_key IN VARCHAR2,
178 p_actid IN NUMBER,
179 p_funcmode IN VARCHAR2,
180 p_result OUT NOCOPY VARCHAR2);
181
182 -- Is_Jrnl_Reversed
183 -- Purpose
184 -- Called from Workflow activity
185 -- Called during rollback process.
186 -- Determines whether batch is successfully reversed
187
188 Procedure Is_Jrnl_Reversed(p_item_type IN VARCHAR2,
189 p_item_key IN VARCHAR2,
190 p_actid IN NUMBER,
191 p_funcmode IN VARCHAR2,
192 p_result OUT NOCOPY VARCHAR2);
193
194
195 -- Select_And_Validate_AllBatches
196 -- Purpose
197 -- Called from Workflow activity
198 -- Called during rollback process.
199 -- Validates reversed batches status before posting
200
201 Procedure Select_And_Validate_AllBatches(p_item_type IN VARCHAR2,
202 p_item_key IN VARCHAR2,
203 p_actid IN NUMBER,
204 p_funcmode IN VARCHAR2,
205 p_result OUT NOCOPY VARCHAR2);
206
207 -- Select_And_Validate_AllBatches
208 -- Purpose
209 -- Called from Workflow activity
210 -- Called during rollback process.
211 -- Dertermines whether all reversed batches are posted successfully
212
213 Procedure Are_All_Batches_Posted(p_item_type IN VARCHAR2,
214 p_item_key IN VARCHAR2,
215 p_actid IN NUMBER,
216 p_funcmode IN VARCHAR2,
217 p_result OUT NOCOPY VARCHAR2);
218
219
220 -- Is_Rollback_Allowed
221 -- Purpose
222 -- Called from Workflow activity
223 -- Dertermines whether rollback is allowed when when any error occurs
224 -- rollback is allowed only wher set doesnot conatain any project batch
225
226 Procedure Is_Rollback_Allowed(p_item_type IN VARCHAR2,
227 p_item_key IN VARCHAR2,
228 p_actid IN NUMBER,
229 p_funcmode IN VARCHAR2,
230 p_result OUT NOCOPY VARCHAR2);
231
232 -- Submit_MA_Program
233 -- Purpose
234 -- Called from Workflow activity
235 -- Submit request for MassAllocation generation program
236
237 Procedure Submit_MA_Program(p_item_type IN VARCHAR2,
238 p_item_key IN VARCHAR2,
239 p_actid IN NUMBER,
240 p_funcmode IN VARCHAR2,
241 p_result OUT NOCOPY VARCHAR2);
242
243 -- WAITING_TO_COMPLETE
244 -- Purpose
245 -- Called from Workflow activity
246 -- Wait for completion of the submitted concurrent program
247
248 Procedure WAITING_TO_COMPLETE(p_item_type IN VARCHAR2,
249 p_item_key IN VARCHAR2,
250 p_actid IN NUMBER,
251 p_funcmode IN VARCHAR2,
252 p_result OUT NOCOPY VARCHAR2);
253
254 -- Submit_Posting_Program
255 -- Purpose
256 -- Called from Workflow activity
257 -- Submit request for posting program
258
259 Procedure Submit_Posting_Program
260 (p_item_type IN VARCHAR2,
261 p_item_key IN VARCHAR2,
262 p_actid IN NUMBER,
263 p_funcmode IN VARCHAR2,
264 p_result OUT NOCOPY VARCHAR2);
265
266
267 -- Submit_RJE_Program
268 -- Purpose
269 -- Called from Workflow activity
270 -- Submit request for recurring generation
271
272 Procedure Submit_RJE_Program(p_item_type IN VARCHAR2,
273 p_item_key IN VARCHAR2,
274 p_actid IN NUMBER,
275 p_funcmode IN VARCHAR2,
276 p_result OUT NOCOPY VARCHAR2);
277
278 -- is_je_valid_for_posting
279 -- Purpose
280 -- Called from Workflow activity
281 -- Determine whether batch status is correct for posting
282
283 PROCEDURE is_je_valid_for_posting
284 (itemtype IN VARCHAR2,
285 itemkey IN VARCHAR2,
286 l_je_batch_id IN NUMBER,
287 l_invalid_error OUT NOCOPY VARCHAR2,
288 result OUT NOCOPY VARCHAR2 ) ;
289
290 -- Submit_REV_Program
291 -- Purpose
292 -- Called from Workflow activity
293 -- Called only during rollback process
294 -- Submit request for journal reversal program
295
296 Procedure Submit_REV_Program(p_item_type IN VARCHAR2,
297 p_item_key IN VARCHAR2,
298 p_actid IN NUMBER,
299 p_funcmode IN VARCHAR2,
300 p_result OUT NOCOPY VARCHAR2);
301
302 -- Submit_REV_Program
303 -- Purpose
304 -- Called from Workflow activity
305 -- Called only during rollback process
306 -- Validate set before initiating rollback process.
307 -- Initialize rollback context if allocation set is valid
308
309 Procedure VAL_SET_FOR_ROLLBACK
310 (p_item_type IN VARCHAR2,
311 p_item_key IN VARCHAR2,
312 p_actid IN NUMBER,
313 p_funcmode IN VARCHAR2,
314 p_result OUT NOCOPY VARCHAR2) ;
315
316 -- END_FAIL
317 -- Purpose
318 -- Called from Workflow activity
319 -- Top level End Activity if work flow is terminated
320 -- Set the wok flow step status to "Terminated"
321
322 procedure END_FAIL (p_item_type IN VARCHAR2,
323 p_item_key IN VARCHAR2,
324 p_actid IN NUMBER,
325 p_funcmode IN VARCHAR2,
326 p_result OUT NOCOPY VARCHAR2) ;
327
328
329 procedure Selector_Func (p_item_type IN VARCHAR2,
330 p_item_key IN VARCHAR2,
331 p_actid IN NUMBER,
332 p_funcmode IN VARCHAR2,
333 p_result OUT NOCOPY VARCHAR2) ;
334
335 -- Procedure Submit_Request
336 -- Purpose
337 -- Submit concurrent request and insert record into
338 -- GL_AUTO_ALLOC_BAT_HIST_DET table
339
340 Procedure Submit_Request(
341 p_parent_request_id IN NUMBER
342 ,p_step_number IN NUMBER
343 ,Prog_Code IN VARCHAR2
344 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
345 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
346 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
347 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
348 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
349 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
350 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
351 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
352 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
353 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
354 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
355 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
356 ,p_sub_req_id OUT NOCOPY NUMBER);
357
358 -- Procedure get_standard_who
359 -- Purpose
360 -- To set the standard who columns in the table
361
362 Procedure get_standard_who (
363 l_CREATED_BY OUT NOCOPY NUMBER
364 ,l_LAST_UPDATED_BY OUT NOCOPY NUMBER
365 ,l_LAST_UPDATE_LOGIN OUT NOCOPY NUMBER );
366
367 -- Procedure INSERT_BATCH_HIST_DET
368 -- Purpose
369 -- Called after concurrent request is submitted
370 -- To insert record into GL_ALLOC_BATCH_HIST_DET table
371
372 Procedure INSERT_BATCH_HIST_DET(
373 p_request_id IN NUMBER
374 ,p_parent_request_id IN NUMBER
375 ,p_step_number IN NUMBER
376 ,p_program_name_code IN VARCHAR2
377 ,p_run_mode IN VARCHAR2
378 ,p_allocation_type IN VARCHAR2 DEFAULT 'GL'
379 ,p_created_by IN NUMBER DEFAULT -1
380 ,p_last_updated_by IN NUMBER DEFAULT -1
381 ,p_last_update_login IN NUMBER DEFAULT -1
382 );
383
384 -- Function Contain_Projects
385 -- Purpose
386 -- Determine whether allocation set contain any project batch
387 -- To insert record into GL_ALLOC_BATCH_HIST_DET table
388
389 Function Contain_Projects(
390 X_Request_Id IN NUMBER
391 ) RETURN BOOLEAN ;
392
393
394
395 -- Procedure Update_Status
396 -- Purpose
397 -- Update status_code in GL_AUTO_ALLOC_BATCH_HISTORY for a step
398 -- Called from WAITING_TO_COMPLETE
399
400 Procedure Update_Status(
401 l_request_id IN NUMBER
402 ,l_step_number IN NUMBER
403 ,l_status_code IN Varchar2 ) ;
404
405
406 -- Procedure Get_Status_and_Message
407 -- Purpose
408 -- Get status_code and set up the value for workflow notification message name
409 -- Called from WAITING_TO_COMPLETE
410
411 Procedure Get_Status_and_Message(
412 conc_prg_code IN Varchar2
413 ,ptype IN Varchar2
414 ,rollback_allowed IN Varchar2
415 ,status_code OUT NOCOPY Varchar2
416 ,message_name OUT NOCOPY Varchar2 );
417
418
419
420 -- Procedure initialize_debug
421 -- Purpose
422 -- Initialize debug file
423 -- initialize only if debug_flag is TRUE
424
425 Procedure initialize_debug ;
426
427 -- Procedure diagn_debug_msg
428 -- Purpose
429 -- Write debug message to debug file
430 -- debug message is written only if debug_flag is TRUE
431
432 PROCEDURE diagn_debug_msg (debug_message IN VARCHAR2);
433
434 -- Procedure
435 -- Continue_Next_Step
436 -- Purpose
437 -- Called from Workflow activity
438 -- Determines whether the step-down allocation should continue
439 -- to process next step when no journal is generated.
440
441 Procedure continue_next_step(p_item_type IN VARCHAR2,
442 p_item_key IN VARCHAR2,
443 p_actid IN NUMBER,
444 p_funcmode IN VARCHAR2,
445 p_result OUT NOCOPY VARCHAR2);
446
447
448 End GL_AUTO_ALLOC_WF_PKG;