1 Package Pa_Otc_Api AS
2 --$Header: PAXVOTCS.pls 120.2 2005/12/01 08:53:40 eyefimov noship $
3
4 TYPE Timecard_Rec IS Record (
5 Project_Number Pa_Projects_All.Segment1%TYPE,
6 Project_Id Pa_Projects_All.Project_Id%TYPE,
7 Task_Number Pa_Tasks.Task_Number%TYPE,
8 Task_Id Pa_Tasks.Task_Id%TYPE,
9 Expenditure_Type Pa_Expenditure_Types.Expenditure_Type%TYPE,
10 System_Linkage_Function Pa_System_Linkages.Function%TYPE,
11 Quantity Pa_Expenditure_Items_All.quantity%TYPE,
12 Incurred_By_Person_Id Pa_Expenditures_All.Incurred_By_Person_Id%TYPE,
13 Override_Approver_Person_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE,
14 Expenditure_Item_Date Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE,
15 Expenditure_Ending_Date Pa_Expenditures_All.Expenditure_Ending_Date%TYPE,
16 Attribute_Category Pa_Expenditure_Items_All.Attribute_Category%TYPE,
17 Attribute1 Pa_Expenditure_Items_All.Attribute1%TYPE,
18 Attribute2 Pa_Expenditure_Items_All.Attribute1%TYPE,
19 Attribute3 Pa_Expenditure_Items_All.Attribute1%TYPE,
20 Attribute4 Pa_Expenditure_Items_All.Attribute1%TYPE,
21 Attribute5 Pa_Expenditure_Items_All.Attribute1%TYPE,
22 Attribute6 Pa_Expenditure_Items_All.Attribute1%TYPE,
23 Attribute7 Pa_Expenditure_Items_All.Attribute1%TYPE,
24 Attribute8 Pa_Expenditure_Items_All.Attribute1%TYPE,
25 Attribute9 Pa_Expenditure_Items_All.Attribute1%TYPE,
26 Attribute10 Pa_Expenditure_Items_All.Attribute1%TYPE,
27 Billable_Flag Pa_Expenditure_Items_All.Billable_Flag%TYPE,
28 Expenditure_Item_Comment Pa_Expenditure_Comments.Expenditure_Comment%TYPE,
29 Orig_Exp_Txn_Reference1 Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE,
30 Status Varchar2(2000),
31 Work_Type_Id Pa_Expenditure_Items_All.Work_Type_Id%TYPE,
32 Assignment_Id Pa_Expenditure_Items_All.Assignment_Id%TYPE,
33 PO_Line_Id Pa_Expenditure_Items_All.PO_Line_Id%TYPE,
34 PO_Price_Type Pa_Expenditure_Items_All.PO_Price_Type%TYPE,
35 Person_Type Pa_Expenditures_All.Person_Type%TYPE,
36 Vendor_Id Pa_Expenditures_All.Vendor_Id%TYPE,
37 PO_Header_Id Number,
38 Approval_Status Hxc_Time_Building_Blocks.Approval_Status%TYPE,
39 Action Varchar2(30));
40
41 TYPE Timecard_Table IS Table OF Timecard_Rec
42 INDEX BY Binary_Integer;
43
44 TYPE Project_Attribution_Rec IS Record (
45 Project_Number Pa_Projects_All.Segment1%TYPE,
46 Project_Id Pa_Projects_All.Project_Id%TYPE,
47 Proj_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
48 Proj_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
49 Task_Number Pa_Tasks.Task_Number%TYPE,
50 Task_Id Pa_Tasks.Task_Id%TYPE,
51 Task_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
52 Task_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
53 Expenditure_Type Pa_Expenditure_Types.Expenditure_Type%TYPE,
54 UOM Hxc_Time_Building_Blocks.Unit_Of_Measure%TYPE,
55 Exp_Type_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
56 Exp_Type_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
57 Sys_Linkage_Func Pa_Expenditure_Types.System_Linkage_Function%TYPE,
58 Sys_Link_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
59 Sys_Link_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
60 Quantity Pa_Expenditure_Items_All.Quantity%TYPE,
61 Expenditure_Item_Date Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE,
62 Exp_Ending_Date Pa_Expenditures_All.Expenditure_Ending_Date%TYPE,
63 Inc_By_Person_Id Pa_Expenditures_All.Incurred_By_Person_Id%TYPE,
64 Attrib_Category Pa_Expenditure_Items_All.Attribute_Category%TYPE,
65 Attribute1 Pa_Expenditure_Items_All.Attribute1%TYPE,
66 Attribute2 Pa_Expenditure_Items_All.Attribute2%TYPE,
67 Attribute3 Pa_Expenditure_Items_All.Attribute3%TYPE,
68 Attribute4 Pa_Expenditure_Items_All.Attribute4%TYPE,
69 Attribute5 Pa_Expenditure_Items_All.Attribute5%TYPE,
70 Attribute6 Pa_Expenditure_Items_All.Attribute6%TYPE,
71 Attribute7 Pa_Expenditure_Items_All.Attribute7%TYPE,
72 Attribute8 Pa_Expenditure_Items_All.Attribute8%TYPE,
73 Attribute9 Pa_Expenditure_Items_All.Attribute9%TYPE,
74 Attribute10 Pa_Expenditure_Items_All.Attribute10%TYPE,
75 Expenditure_Item_Comment Pa_Expenditure_Comments.Expenditure_Comment%TYPE,
76 Billable_Flag Pa_Expenditure_Items_All.Billable_Flag%TYPE,
77 Billable_Flag_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
78 Billable_Flag_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
79 Billable_Flag2 Pa_Expenditure_Items_All.Billable_Flag%TYPE,
80 Billable_Flag_Index Binary_Integer,
81 Work_Type_Id Pa_Expenditure_Items_All.Work_Type_Id%TYPE,
82 Work_Type_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
83 Work_Type_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
84 Assignment_Id Pa_Expenditure_Items_All.Assignment_Id%TYPE,
85 Assignment_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
86 Assignment_Attr_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
87 PO_Line_Id Pa_Expenditure_Items_All.PO_Line_Id%TYPE,
88 PO_Line_Id_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
89 PO_Line_Id_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
90 PO_Price_Type Pa_Expenditure_Items_All.PO_Price_Type%TYPE,
91 PO_Price_Type_Attr_Id Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
92 PO_Price_Type_Ovn Hxc_Time_Attributes.Object_Version_Number%TYPE,
93 Person_Type Pa_Expenditures_All.Person_Type%TYPE,
94 Vendor_Id Pa_EXpenditures_All.Vendor_Id%TYPE,
95 PO_Header_Id Number,
96 Approval_Status Hxc_Time_Building_Blocks.Approval_Status%TYPE,
97 Action Varchar2(30));
98
99 G_timecard_table Timecard_Table;
100
101 TYPE Message_Token IS Record (
102 Token_Name Varchar2(30),
103 Token_Value Varchar2(255));
104
105 TYPE Message_Tokens IS Table OF Message_Token
106 INDEX BY Binary_Integer;
107
108 TYPE EndDateBatchName_Rec IS Record (
109 Expenditure_Ending_Date Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE,
110 Batch_Name Pa_Transaction_Interface_All.Batch_Name%TYPE);
111
112 TYPE EndDateBatchName_Tab IS Table OF EndDateBatchName_Rec
113 INDEX BY Binary_Integer;
114
115 TYPE Trx_Inserted IS Record (
116 BB_Index Binary_Integer);
117
118 TYPE Trx_Inserted_Table IS Table OF Trx_Inserted
119 INDEX BY Binary_Integer;
120
121 -- Define exceptions used in the API
122
123 Delete_Line_Exception Exception;
124 E_Validation_Failure Exception;
125 E_Is_Deleted Exception;
126
127
128 -- =======================================================================
129 -- Start of Comments
130 -- API Name : TrackPath
131 -- Type : Private
132 -- Pre-Reqs : None
133 -- Type : Procedure
134 -- Function : This procedure tracks the path thru the code to attach to error messages.
135 --
136 -- Parameters:
137 --
138 -- IN
139 -- P_Function - Varchar2 -- ADD or STRIP
140 -- P_Value - Varchar2
141 --
142 /*-------------------------------------------------------------------------*/
143
144 Procedure TrackPath (
145 P_Function IN Varchar2,
146 P_Value IN Varchar2);
147
148 -- =======================================================================
149 -- Start of Comments
150 -- API Name : Add_Error_To_Table
151 -- Type : Private
152 -- Pre-Reqs : None
153 -- Type : Procedure
154 -- Function : This procedure populates the error table for any expected errors.
155 --
156 -- Parameters:
157 --
158 -- IN
159 -- P_Message_Table - Hxc_User_Type_Definition_Grp.Message_Table
160 -- P_Message_Name - Fnd_New_Messages.Message_Name%TYPE
161 -- P_Message_Level - Varchar2
162 -- P_Message_Field - Varchar2
163 -- P_Msg_Tokens - Pa_Otc_Api.Message_Tokens
164 -- P_Time_Building_Block_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
165 -- P_Time_Attribute_Id - Hxc_Time_Attributes.Time_Attribute_Id%TYPE
166 -- P_Message_App - Varchar2 Default 'PA'
167 --
168 -- OUT
169 -- P_Message_Table - Hxc_User_Type_Definition_Grp.Message_Table
170 --
171
172 /*-------------------------------------------------------------------------*/
173
174 Procedure Add_Error_To_Table(
175 P_Message_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Message_Table, -- 2672653
176 P_Message_Name IN Fnd_New_Messages.Message_Name%TYPE,
177 P_Message_Level IN Varchar2,
178 P_Message_Field IN Varchar2,
179 P_Msg_Tokens IN Pa_Otc_Api.Message_Tokens,
180 P_Time_Building_Block_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
181 P_Time_Attribute_Id IN Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
182 P_Message_App IN Varchar2 Default 'PA');
183
184
185 /* IMPORT ROUTINES */
186
187
188 -- =======================================================================
189 -- Start of Comments
190 -- API Name : Upload_Otc_Timecards
191 -- Type : Public
192 -- Pre-Reqs : None
193 -- Type : Procedure
194 -- Function : This procedure is used to pull approved self service
195 -- timecards into transaction interface table.
196 -- Parameters :
197 -- IN
198 -- P_Transaction_Source: - Pa_Transaction_Interface_All.Transaction_Source%TYPE
199 -- P_Batch: - Pa_Transaction_Interface_All.Batch_Name%TYPE
200 -- P_Xface_Id - Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE
201 -- P_User_Id - Number
202
203 /*--------------------------------------------------------------------------*/
204
205 PROCEDURE Upload_Otc_Timecards(P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
206 P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
207 P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
208 P_User_Id IN Number);
209
210
211 -- =======================================================================
212 -- Start of Comments
213 -- API Name : Build_Reverse_Item
214 -- Type : Private
215 -- Pre-Reqs : None
216 -- Type : Procedure
217 -- Function : This procedure is called when need to reverse expenditure_item
218 -- already imported into projects and need to create a record to
219 -- insert into pa_transaction_interface table for this.
220 -- Conditions : 1. Deleted the original item
221 -- called 2. Changed either project,
222 -- task,
223 -- expenditure_type,
224 -- system_linkage_function,
225 -- quantity
226 --
227 -- Parameters :
228 -- IN
229 -- P_Old_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
230 -- P_New_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
231 -- P_Batch_Name Varchar2
232 -- P_User_Id Pa_Expenditure_Items_All.Last_Updated_By%TYPE
233 -- P_Orig_Exp_Txn_Reference1 Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE
234 -- P_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE
235
236 /*------------------------------------------------------------------------- */
237
238 Procedure Build_Reverse_Item(
239 P_Old_Orig_Trx_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
240 P_New_Orig_Trx_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
241 P_Batch_Name IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
242 P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE,
243 P_Orig_Exp_Txn_Reference1 IN Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE,
244 P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE);
245
246 -- =======================================================================
247 -- Start of Comments
248 -- API Name : UpdateChangedOrigTxn
249 -- Type : Private
250 -- Pre-Reqs : None
251 -- Type : Procedure
252 -- Function : This procedure is used to update the expenditure item directly
253 -- for an item that has not being reversed but ONLY the
254 -- item comment has been changed and/or the DFF has been changed.
255 -- No record will be inserted into table pa_transaction_interface.
256 --
257 -- Values for parameter P_Commend_Or_Dff
258 -- -------------------------------------
259 -- C for Comment
260 -- D for Dff
261 -- B for Both
262 --
263 -- Parameters :
264 -- IN P_Old_Orig_Txn_Ref - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
265 -- P_New_Orig_Txn_Ref - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
266 -- P_Comment_Or_Dff - Varchar2
267 -- P_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
268 -- P_User_Id - Pa_Expenditure_Items_All.Last_Updated_By%TYPE
269 --
270 -- OUT NONE
271 --
272
273 /*--------------------------------------------------------------------------*/
274
275 Procedure UpdateChangedOrigTxn(
276 P_Old_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
277 P_New_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
278 P_Comment_Or_Dff IN Varchar2,
279 P_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
280 P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE);
281
282 -- =======================================================================
283 -- Start of Comments
284 -- API Name : Tieback_Otc_Timecards
285 -- Type : Public
286 -- Pre-Reqs : None
287 -- Type : Procedure
288 -- Function : This procedure is used to tieback timecards that have been
289 -- interfaced to Oracle Projects successfully. This API
290 -- will stamp the OTC timecard PL/SQL tables with the fact that
291 -- timecards retrieved have been sucessfully import. This will be
292 -- done via a call to an OTC API.
293 -- Parameters :
294 -- IN
295 -- P_Transaction_source: Unique identifier for source of the txn
296 -- P_batch: Batch Name to group txns into batches
297 -- P_xface_id: Interface Id
298 -- P_user_id: User Id
299 -- OUT
300 -- none
301 /*--------------------------------------------------------------------------*/
302
303 Procedure Tieback_Otc_Timecards (
304 P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
305 P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
306 P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
307 P_User_Id IN Number);
308
309 -- =======================================================================
310 -- Start of Comments
311 -- API Name : DetermineDirectUpdate
312 -- Type : Private
313 -- Pre-Reqs : None
314 -- Type : Procedure
315 -- Function : This procedure determines if the expenditure item should be updated directly
316 -- : or if a reversing entry and a new entry neeed to be inserted into table
317 -- : pa_transaction_interface_all. This is done by comparing to see what has
318 -- : actually changed. If only the dff and/or the item comment has changed and
319 -- : nothing else then the expenditure item should be directly updated.
320 --
321 -- Parameters :
322 -- IN
323 -- P_New_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
324 -- P_Old_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
325 -- OUT
326 -- P_Direct_Update_Flag - Boolean
327 -- P_Comment_Or_Dff - Varchar2
328 --
329 /*--------------------------------------------------------------------------*/
330
331 Procedure DetermineDirectUpdate(
332 P_New_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
333 P_Old_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
334 P_Direct_Update_Flag OUT NOCOPY Boolean,
335 P_Comment_Or_Dff OUT NOCOPY Varchar2);
336
337
338 -- =======================================================================
339 -- Start of Comments
340 -- API Name : PopulateProjRec
341 -- Type : Private
342 -- Pre-Reqs : None
343 -- Type : Procedure
344 -- Function : This procedure pulls all the data from the OTC pl/sql into a projects
345 -- : oriented structure for easier processing.
346 --
347 -- Parameters :
348 -- IN
349 -- P_New_Old_BB - Varchar2 Allowed Values:
350 -- Import values: 'OLD' 'NEW'
351 -- Validation value:
352 -- P_BB_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
353 -- P_Detail_Index - Binary_Integer
354 -- P_Old_Detl_Ind - Binary_Integer
355 -- OUT
356 -- P_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
357 --
358 /*--------------------------------------------------------------------------*/
359
360 Procedure PopulateProjRec(
361 P_New_Old_BB IN Varchar2,
362 P_BB_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
363 P_Detail_Index IN Binary_Integer,
364 P_Old_Detl_Ind IN Binary_Integer,
365 P_Timecard_Rec OUT NOCOPY Pa_Otc_Api.Timecard_Rec); -- 2672653
366
367
368 -- ========================================================================
369 -- Start Of Comments
370 -- API Name : GetDetailIndex
371 -- Type : Private
372 -- Pre-Reqs : None
373 -- Type : Procedure
374 -- Return : n/a
375 -- Function : This procedure finds the Index located in the detail pl/sql
376 -- Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks table generated
377 -- during the generic retrieval process for the
378 -- Building_Block_Id that is passed in to it.
379 --
380 -- Parameters :
381 -- IN
382 -- P_Detail_BB_Id - Hxc_User_Type_Definition_Grp.Resource_Id%TYPE
383 -- OUT
384 -- X_Detail_Index - Binary_Integer
385
386 /*--------------------------------------------------------------------------*/
387
388
389 Procedure GetDetailIndex( P_Detail_BB_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
390 X_Detail_Index OUT NOCOPY Binary_Integer);
391
392
393
394 /* UPDATE and VALIDATION ROUTINES */
395
396
397 -- ========================================================================
398 -- Start Of Comments
399 -- API Name : Projects_Retrieval_Process
400 -- Type : Public
401 -- Pre-Reqs : None
402 -- Type : Function
403 -- Return : Varchar2
404 -- Function : This function is called to provide the retrieval process name so that the appropriate
405 -- attribution is retrieved for validation.
406 --
407
408 /*--------------------------------------------------------------------------*/
409
410
411 Function Projects_Retrieval_Process RETURN Varchar2;
412
413
414 -- =======================================================================
415 -- Start of Comments
416 -- API Name : Update_Otc_Data
417 -- Type : Public
418 -- Pre-Reqs : None
419 -- Type : Procedure
420 -- Function : This procedure is called by the OTC client team server-side
421 -- non-user data modification section of their code.
422 -- The only intent of the Procedure is manipulate the parameters
423 -- passed in and then call the Private procedure Update_Otc_Data()
424 -- to update the Billable_flag when appropriate.
425 --
426 -- Parameters :
427 -- IN
428 -- P_operation - Varchar2
429
430 /*------------------------------------------------------------------------- */
431
432 Procedure Update_Otc_Data
433 (P_Operation IN Varchar2);
434
435
436 -- =======================================================================
437 -- Start of Comments
438 -- API Name : Update_Process
439 -- Type : Private
440 -- Pre-Reqs : None
441 -- Type : Procedure
442 -- Function : This procedure is called by the Public procedure Update_Process()
443 -- The only intent of this procedure is to get the BILLABLE_FLAG
444 -- from patc/patcx and return control back to the calling procedure.
445 -- No handled errors will be returned to the OTC client team server code calling
446 -- procedure. Unhandled exceptions will be allowed.
447 --
448 -- Parameters:
449 -- IN
450 -- P_Operation -- Varchar2
451 -- P_Building_Blocks -- Hxc_User_Type_Definition_Grp.Timecard_Info
452 -- P_Attribute_Table -- Hxc_User_Type_Definition_Grp.App_Attributes_Info
453 -- OUT
454 -- P_Building_Blocks -- Hxc_User_Type_Definition_Grp.Timecard_Info
455 -- P_Attribute_Table -- Hxc_User_Type_Definition_Grp.App_Attributes_Info
456
457 /*------------------------------------------------------------------------- */
458
459 Procedure Update_Process(
460 P_Operation IN Varchar2,
461 P_Building_Blocks IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Timecard_Info, -- 2672653
462 P_Attribute_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.App_Attributes_Info); -- 2672653
463
464 -- =======================================================================
465 -- Start of Comments
466 -- API Name : Validate_Otc_Data
467 -- Type : Public
468 -- Pre-Reqs : None
469 -- Type : Procedure
470 -- Function : This procedure is called by the OTC client team server-side
471 -- Validation section of their code. The only intent of the
472 -- Procedure is manipulate the parameters passed in and then call
473 -- the Private procedure Validate_Otc_Date() to Validate the data.
474 --
475 -- Parameters :
476 -- IN
477 -- P_operation - Varchar2
478
479 /*------------------------------------------------------------------------- */
480
481 Procedure Validate_Otc_Data
482 (P_Operation IN Varchar2);
483
484
485 -- =======================================================================
486 -- Start of Comments
487 -- API Name : Validate_Process
488 -- Type : Public
489 -- Pre-Reqs : None
490 -- Type : Procedure
491 -- Function : This procedure validates the Timecard Header/lines
492 -- information entered by the user.
493 --
494 -- Parameters :
495 -- IN
496 -- P_Operation -- Varchar2
497 -- P_Building_Blocks -- Hxc_User_Type_Definition_Grp.Timecard_Info
498 -- P_Attribute_Table -- Hxc_User_Type_Definition_Grp.App_Attributes_Info
499 -- OUT
500 -- P_Message_Table -- Hxc_User_Type_Definition_Grp.Message_Table
501
502 /*------------------------------------------------------------------------- */
503
504 Procedure Validate_Process(
505 P_Operation IN Varchar2,
506 P_Building_Blocks IN Hxc_User_Type_Definition_Grp.Timecard_Info,
507 P_Attribute_Table IN Hxc_User_Type_Definition_Grp.App_Attributes_Info,
508 P_Message_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Message_Table); -- 2672653
509
510
511 -- =======================================================================
512 -- Start of Comments
513 -- API Name : Validate_Project_Exists
514 -- Type : Private
515 -- Pre-Reqs : None
516 -- Type : Procedure
517 -- Function : This procedure accepts the Project_Id as an IN parameter
518 -- and ckecks if this project exists in Oracle Projects.
519 -- This procedure does not perform any extensive project
520 -- related validations. If the project exists in
521 -- Oracle Projects, X_Project_Number will be populated
522 -- with segment1, else X_Project_Number will be null.
523 -- Parameters :
524 -- IN
525 -- P_Project_Id - Pa_Projects_All.Project_Id%TYPE
526 -- OUT
527 -- X_Error_Code - Varchar2
528 -- X_Error_Type - Varchar2
529 -- X_Project_Number - Pa_Projects_All.Segment1%TYPE
530
531 /*-------------------------------------------------------------------------*/
532
533 PROCEDURE Validate_Project_Exists(
534 P_Project_Id IN Pa_Projects_All.Project_Id%TYPE,
535 X_Error_Code OUT NOCOPY Varchar2,
536 X_Error_Type OUT NOCOPY Varchar2,
537 X_Project_Number OUT NOCOPY Pa_Projects_All.Segment1%TYPE);
538
539
540 -- =======================================================================
541 -- Start of Comments
542 -- API Name : Validate_Task_Exists
543 -- Type : Private
544 -- Pre-Reqs : None
545 -- Type : Procedure
546 -- Function : This procedure accepts the Project_Id and Task_Id
547 -- as IN parameters and ckecks if this task exists in
548 -- pa_online_task_v. This procedure does not perform any
549 -- extensive task related validations. If the task
550 -- exists in the online view, X_Task_Number will be populated
551 -- with Task_Number, else X_Task_Number will be null.
552 -- Parameters :
553 -- IN
554 -- P_Task_Id - Pa_Tasks.Task_Id%TYPE
555 -- P_Project_Id - Pa_Projects.Project_Id%TYPE
556 -- OUT
557 -- X_Error_Code - Varchar2
558 -- X_Error_Type - Varchar2
559 -- X_Task_Number - Pa_Tasks.Task_Number%TYPE
560
561 /*-------------------------------------------------------------------------*/
562
563 Procedure Validate_Task_Exists(
564 P_Task_Id IN Pa_Tasks.Task_Id%TYPE,
565 P_Project_Id IN Pa_Projects.Project_Id%TYPE,
566 X_Error_Code OUT NOCOPY Varchar2,
567 X_Error_Type OUT NOCOPY Varchar2,
568 X_Task_Number OUT NOCOPY Pa_Tasks.Task_Number%TYPE);
569
570
571
572 -- ==========================================================================
573 -- Start of Comments
574 -- API Name : Validate_Exp_Type_Exists
575 -- Type : Private
576 -- Pre-Reqs : None
577 -- Type : Procedure
578 -- Function : This procedure checks if the system linkage/expenditure type
579 -- combination exists in the database.
580 -- Parameters :
581 -- IN
582 -- P_System_Linkage - Pa_System_Linkages.Function%TYPE
583 -- P_Expenditure_Type - Pa_Expenditure_Types.Expenditure_Type%TYPE
584 -- P_Exp_Item_date - Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE
585 -- OUT
586 -- X_Error_Code - Varchar2
587 -- X_Error_Type - Varchar2
588
589 /*--------------------------------------------------------------------------*/
590
591 Procedure Validate_Exp_Type_Exists(
592 P_System_Linkage IN Pa_System_Linkages.Function%TYPE,
593 P_Expenditure_Type IN Pa_Expenditure_Types.Expenditure_Type%TYPE,
594 P_Exp_Item_Date IN Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE,
595 X_Error_Type OUT NOCOPY Varchar2,
596 X_Error_Code OUT NOCOPY Varchar2);
597
598
599 -- ===========================================================================
600 -- API Name : Validate_overriding_approver
601 -- Type : Private
602 -- Pre-Reqs : None
603 -- Type : Procedure
604 -- Function : This function validates the overriding approver entered
605 -- in timecard header screen.
606 --
607 -- Parameters :
608 -- IN
609 -- P_Approver_Id - Per_People_F.Person_Id%TYPE
610 --
611 -- OUT
612 -- X_Approver_Id - Per_People_F.Person_Id%TYPE
613 -- X_Error_Type - Varchar2
614 -- X_Error_Code - Varchar2
615
616 /* ------------------------------------------------------------------------*/
617
618 Procedure Validate_Overriding_Approver(
619
620 P_Approver_Id IN Per_People_F.Person_Id%TYPE,
621 X_Approver_Id OUT NOCOPY Per_People_F.Person_Id%TYPE,
622 X_Error_Type OUT NOCOPY Varchar2,
623 X_Error_Code OUT NOCOPY Varchar2);
624
625
626 -- =======================================================================
627 -- Start of Comments
628 -- API Name : DetermineProcessingFlags
629 -- Type : Private
630 -- Pre-Reqs : None
631 -- Type : Procedure
632 -- Return : n/a
633 -- Function : This procedure deteremines the three flags necessary for processing
634 -- of the data further along in the main processing routine
635 -- Update_Validate_Timecard() which class this procedure at the for each
636 -- DETAIL record being looped thru.
637 --
638 -- Parameters :
639 -- IN
640 -- P_BB_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
641 -- P_BB_Ovn - Hxc_Time_Building_Blocks.Object_Version_Number%TYPE
642 -- P_BB_Date_To - Hxc_Time_Building_Blocks.Date_To%TYPE
643 -- P_BB_Changed - Varchar2
644 -- P_BB_New - Varchar2
645 -- P_Proj_Attribute_Rec - Pa_Otc_Api.Project_Attribution_Rec
646 -- P_Mode - Varchar2
647 -- P_Proces_Flag - Varchar2
648 -- OUT
649 -- X_BB_Detail_Changed - Varchar2
650 -- X_Data_Conflict_Flag - Varchar2
651 -- X_BB_Detail_Deleted - Varchar2
652 -- X_Adj_in_Projects_Flag - Varchar2
653 --
654
655 /*--------------------------------------------------------------------------*/
656
657 Procedure DetermineProcessingFlags (
658 P_BB_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
659 P_BB_Ovn IN Hxc_Time_Building_Blocks.Object_Version_Number%TYPE,
660 P_BB_Date_To IN Hxc_Time_Building_Blocks.Date_To%TYPE,
661 P_BB_Changed IN Varchar2,
662 P_BB_New IN Varchar2,
663 P_Proj_Attribute_Rec IN Pa_Otc_Api.Project_Attribution_Rec,
664 P_Mode IN Varchar2,
665 P_Process_Flag IN Varchar2,
666 X_BB_Detail_Changed OUT NOCOPY Varchar2,
667 X_Data_Conflict_Flag OUT NOCOPY Varchar2,
668 X_BB_Detail_Deleted OUT NOCOPY Varchar2,
669 X_Adj_in_Projects_Flag OUT NOCOPY Varchar2);
670
671
672 -- =======================================================================
673 -- Start of Comments
674 -- API Name : AdjustAllowedToOTCItem
675 -- Type : Public
676 -- Pre-Reqs : None
677 -- Type : Procedure
678 -- Function : This procedure is used to check and see if an OTC expenditure item that
679 -- has been imported into Projects can adjusted in Projects. Will be calling
680 -- an OTC API to determine this. Hxc_Integration_Layer_V1_Grp.Time_Bld_Blk_Changed().
681 --
682 -- Parameters :
683 -- IN P_Orig_Txn_Reference - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
684 -- OUT X_Flag - Boolean
685 --
686
687 /*--------------------------------------------------------------------------*/
688
689 Procedure AdjustAllowedToOTCItem(
690 P_Orig_Txn_Reference IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
691 X_Flag OUT NOCOPY Boolean);
692
693
694 -- =======================================================================
695 -- Start of Comments
696 -- API Name : ProjectTaskUsed
697 -- Type : Public
698 -- Pre-Reqs : None
699 -- Type : Procedure
700 -- Return : n/a
701 -- Function : This procedure is used to check to see if there are Project OTC
702 -- expenditure items that are using a specific project or task. Will be calling
703 -- an OTC API to determine this. If parameters are not properly populated then
704 -- return TRUE.
705 --
706 -- Parameters :
707 -- IN P_Search_Attribute - Varchar2 -- 'PROJECT' or 'TASK'
708 -- P_Search_Value - Number -- Project_Id or Task_Id
709 -- OUT X_Used - Boolean
710 --
711
712 /*--------------------------------------------------------------------------*/
713
714 Procedure ProjectTaskUsed(P_Search_Attribute IN Varchar2,
715 P_Search_Value IN Number,
716 X_Used OUT NOCOPY Boolean);
717
718 -- =======================================================================
719 -- Start of Comments
720 -- API Name : ProjectTaskPurgeable
721 -- Type : Public
722 -- Pre-Reqs : None
723 -- Type : Procedure
724 -- Return : n/a
725 -- Function : This procedure is used to check to see if there are Project OTC
726 -- expenditure items that are using a specific project or task that have/have not been
727 -- imported successfullying into projects. Will be calling
728 -- an OTC API to determine this. If parameters are not properly populated then
729 -- return TRUE.
730 --
731 -- Parameters :
732 -- IN P_Search_Attribute - Varchar2 -- 'PROJECT' or 'TASK'
733 -- P_Search_Value - Number -- Project_Id or Task_Id
734 -- OUT X_Purgeable - Boolean
735 --
736
737 /*--------------------------------------------------------------------------*/
738
739 Procedure ProjectTaskPurgeable(P_Search_Attribute IN Varchar2,
740 P_Search_Value IN Number,
741 X_Purgeable OUT NOCOPY Boolean);
742
743 -- ========================================================================
744 -- Start Of Comments
745 -- API Name : RetrieveProjAttribution
746 -- Type : Private
747 -- Pre-Reqs : None
748 -- Type : Procedure
749 -- Return : n/a
750 -- Function : This procedure is used to pull out the needed project specific data from
751 -- the OTC pl/sql table P_Attribute_Table.
752 --
753 -- Parameters :
754 -- IN P_Building_Block_Rec - Hxc_User_Type_Definition_Grp.Building_Block_Info
755 -- P_Building_Block - Hxc_User_Type_Definition_Grp.Timecard_Info,
756 -- P_Attribute_Table - Hxc_User_Type_Definition_Grp.App_Attributes_Info
757 -- X_Detail_Attr_Changed - Varchar2
758 -- OUT
759 -- X_Detail_Attr_Changed - Varchar2
760 -- X_Proj_Attrib_Rec - Pa_Otc_Api.Project_Attribution_Rec
761 --
762
763 /*--------------------------------------------------------------------------*/
764
765 Procedure RetrieveProjAttribution(
766 P_Building_Block_Rec IN Hxc_User_Type_Definition_Grp.Building_Block_Info,
767 P_Building_Block IN Hxc_User_Type_Definition_Grp.Timecard_Info,
768 P_Attribute_Table IN Hxc_User_Type_Definition_Grp.App_Attributes_Info,
769 X_Detail_Attr_Changed IN OUT NOCOPY Varchar2,
770 X_Proj_Attrib_Rec OUT NOCOPY Pa_Otc_Api.Project_Attribution_Rec); -- 2672653
771
772 -- ========================================================================
773 -- Start Of Comments
774 -- API Name : RetrieveProjAttrForUpd
775 -- Type : Private
776 -- Pre-Reqs : None
777 -- Type : Procedure
778 -- Return : n/a
779 -- Function : This procedure is used to pull out the needed project specific data from
780 -- the OTL pl/sql table P_Attribute_Table, and if necessary create
781 -- the billable flag record.
782 --
783 -- Parameters :
784 -- IN P_Building_Block_Rec - Hxc_Self_Service_Time_Deposit.Building_Block_Info
785 -- P_Building_Block - Hxc_Self_Service_Time_Deposit.Timecard_Info
786 -- P_Attribute_Table - Hxc_Self_Service_Time_Deposit.App_Attributes_Info
787 -- X_Detail_Attr_Changed - VARCHAR2(1)
788 -- OUT
789 -- P_Attribute_table - Hxc_Self_Service_Time_Deposit.App_Attributes_Info
790 -- X_Detail_Attr_Changed - VARCHAR2(1)
791 -- X_Proj_Attrib_Rec - Pa_Otc_Api.Project_Attribution_Rec
792 --
793
794 /*--------------------------------------------------------------------------*/
795
796 Procedure RetrieveProjAttribForUpd(
797 P_Building_Block_Rec IN Hxc_User_Type_Definition_Grp.Building_Block_Info,
798 P_Building_Block IN Hxc_User_Type_Definition_Grp.Timecard_Info,
799 P_Attribute_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.App_Attributes_Info,
800 X_Detail_Attr_Changed IN OUT NOCOPY Varchar2,
801 X_Proj_Attrib_Rec OUT NOCOPY Pa_Otc_Api.Project_Attribution_Rec);
802
803
804 -- ========================================================================
805 -- Start Of Comments
806 -- API Name : GetPRMAssignTemplates
807 -- Type : Public
808 -- Pre-Reqs : None
809 -- Type : Procedure
810 -- Return : n/a
811 -- Function : This procedure is used to pull from PRM the Forecast Assignment data and provide
812 -- it as template data for OTC. It will not have TASK information in it. It will
813 -- be placed in OTC friendly format for the OTC team to populate the current timecard
814 -- with the Forecast Assignment data. Validation for expenditure_type and system_linkage_function
815 -- combinations will take place within the code before passing it to OTC. Any combo that
816 -- is not valid for the day in question will not be pulled over.
817 --
818 -- Parameters :
819 -- IN P_Resource_Id IN Hxc_Time_Building_Blocks.Resource_Id%TYPE
820 -- P_Start_Date IN Hxc_Time_Building_Blocks.Start_Time%TYPE
821 -- P_Stop_Date IN Hxc_Time_Building_Blocks.Stop_Time%TYPE
822 -- OUT
823 -- P_Attributes OUT Varchar2
824 -- P_Timecard OUT Varchar2
825 -- P_Messages OUT Varchar2
826
827
828 /*--------------------------------------------------------------------------*/
829
830 Procedure GetPRMAssignTemplates(
831 P_Resource_Id IN Hxc_Time_Building_Blocks.Resource_Id%TYPE,
832 P_Start_Date IN Hxc_Time_Building_Blocks.Start_Time%TYPE,
833 P_Stop_Date IN Hxc_Time_Building_Blocks.Stop_Time%TYPE,
834 P_Attributes OUT NOCOPY Varchar2,
835 P_Timecard OUT NOCOPY Varchar2,
836 P_Messages OUT NOCOPY Varchar2);
837
838
839 -- ========================================================================
840 -- Start Of Comments
841 -- API Name : FindandValidateHeader
842 -- Type : Private
843 -- Pre-Reqs : None
844 -- Type : Procedure
845 -- Return : n/a
846 -- Function : This procedure finds and validate the Timecard Header record.
847 -- This procedure is only called when Mode is VALIDATE.
848 --
849 -- Parameters :
850 -- IN
851 -- P_Building_Blocks_Table - Hxc_User_Type_Definition_Grp.Timecard_Info
852 -- P_Attribute_Table - Hxc_User_Type_Definition_Grp.App_Attributes_Info
853 -- P_Message_Table - Hxc_User_Type_Definition_Grp.Message_Table
854 -- OUT
855 -- P_Message_Table - Hxc_User_Type_Definition_Grp.Message_Table
856 -- X_TimeBB_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
857 -- X_Ovr_Approver_Person_Id - Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE
858 -- X_Pass_Val_Flag - Varchar2
859 -- X_Approval_Status - Hxc_Time_Building_Blocks.Approval_Status%TYPE
860
861 /*--------------------------------------------------------------------------*/
862
863
864 Procedure FindandValidateHeader(
865 P_Building_Blocks_Table IN Hxc_User_Type_Definition_Grp.Timecard_Info,
866 P_Attribute_Table IN Hxc_User_Type_Definition_Grp.App_Attributes_Info,
867 P_Message_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Message_Table,-- 2672653
868 X_TimeBB_Id OUT NOCOPY Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
869 X_Ovr_Approver_Person_Id OUT NOCOPY Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE,
870 X_Pass_Val_Flag OUT NOCOPY Varchar2,
871 X_Approval_Status OUT NOCOPY Hxc_Time_Building_Blocks.Approval_Status%TYPE);
872
873 -- =======================================================================
874 -- Start of Comments
875 -- API Name : Wf_AutoApproval_BusMsg
876 -- Type : Public
877 -- Pre-Reqs : None
878 -- Type : Procedure
879 -- Function : This procedure is called by the OTL client team.
880 -- Calls the Pa_Client_Extn_Pte.Get_Exp_AutoApproval() and
881 -- the PA_Time_Client_Extn.Display_Business_Message() extensions.
882 --
883 -- Parameters :
884 -- OUT
885 -- X_AutoApproval_Flag - Varchar2
886 -- X_Messages - Varchar2
887
888 /*------------------------------------------------------------------------- */
889
890 Procedure Wf_AutoApproval_BusMsg
891 (X_AutoApproval_Flag OUT NOCOPY Varchar2
892 ,X_Messages OUT NOCOPY Varchar2);
893
894
895 -- =======================================================================
896 -- Start of Comments
897 -- API Name : Wf_RouteTo_CheckApproval
898 -- Type : Public
899 -- Pre-Reqs : None
900 -- Type : Procedure
901 -- Function : This procedure is called by the OTL client team.
902 -- Calls the Paroutingx.Route_To_Extension() and
903 -- and Pa_Client_Extn_Rte.Check_Approval() extensions.
904 --
905 -- Parameters :
906 -- IN
907 -- P_Previous_Approver_Id - Number
908 -- OUT
909 -- X_Approver_Person_Id - Number
910 -- X_Messages - Varchar2
911
912 /*------------------------------------------------------------------------- */
913
914 Procedure Wf_RouteTo_CheckApproval
915 (P_Previous_Approver_Id IN Number
916 ,X_Approver_Person_Id OUT NOCOPY Number
917 ,X_Messages OUT NOCOPY Varchar2);
918
919 -- =======================================================================
920 -- Start of Comments
921 -- API Name : CreateProjTimecardTable
922 -- Type : Private
923 -- Pre-Reqs : None
924 -- Type : Procedure
925 -- Function : This procedure create pl/sql table of OTL data in
926 -- project friendly format.
927 --
928 -- Parameters :
929 -- OUT
930 -- X_Inc_By_Person_Id - Pa_Expenditures_All.Incurred_By_Person_Id%TYPE
931 -- X_Timecard_Table - Pa_Otc_Api.Timecard_Table
932 -- X_Overriding_Approver_Id - Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE
933
934 /*------------------------------------------------------------------------- */
935
936 Procedure CreateProjTimecardTable
937 (X_Inc_By_Person_Id OUT NOCOPY Pa_Expenditures_All.Incurred_By_Person_Id%TYPE
938 ,X_Timecard_Table OUT NOCOPY Pa_Otc_Api.Timecard_Table --2672653
939 ,X_Overriding_Approver_Id OUT NOCOPY Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE);
940
941
942 -- =======================================================================
943 -- Start of Comments
944 -- API Name : OrigTrxRefValueExists
945 -- Type : Private
946 -- Pre-Reqs : None
947 -- Type : Function
948 -- Function : This function checks to see if the detail scope bb_id/ovn
949 -- combination is already in Projects. This can occur during
950 -- validation of the timecard under certain conditions. The
951 -- orig_transaction_reference columns may get updated during
952 -- validation to resink with OTL. (Changes are made that
953 -- projects does not recognize and doesn't want to, thus the ovn changes in OTL.)
954 --
955 -- Parameters :
956 -- IN
957 -- P_Orig_Transaction_Reference - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
958
959 /*------------------------------------------------------------------------- */
960
961 Function OrigTrxRefValueExists
962 ( P_Orig_Transaction_Reference IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE) RETURN Boolean;
963 -- Pragma RESTRICT_REFERENCES ( OrigTrxRefValueExists, WNPS );
964
965
966 -- =======================================================================
967 -- Start of Comments
968 -- API Name : ChkAdjustAllowedToOTCItem
969 -- Type : Public
970 -- Pre-Reqs : None
971 -- Type : Function
972 -- Function : This function s used to check and see if an OTC expenditure item that
973 -- has been imported into Projects can adjusted in Projects by calling the
974 -- API Hxc_Generic_Retrieval_Utils.Time_Bld_Blk_Changed.
975 --
976 -- Parameters :
977 -- IN P_Orig_Txn_Reference - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
978 --
979 /*--------------------------------------------------------------------------*/
980
981 Function ChkAdjustAllowedToOTCItem
982 (P_Orig_Txn_Reference IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE) RETURN Varchar2;
983
984 -- =======================================================================
985 -- Start of Comments
986 -- API Name : GetBatchName
987 -- Type : Private
988 -- Pre-Reqs : None
989 -- Type : Procedure
990 -- Function : This procedure returns a batch name for Trx Import to used based on the
991 -- expenditure_ending_date passed in. Each time a new batch is created a new
992 -- record is added to a pl/sql table holding the Ending_Date Batch_Name so as
993 -- to only create single batch_name for each Ending_Date for a Trx Import run.
994 --
995 -- Parameters :
996 -- IN
997 -- P_Exp_End_Date - Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE
998 -- OUT
999 -- X_Batch_Name - Pa_Transaction_Interface_All.Batch_Name%TYPE
1000 /*--------------------------------------------------------------------------*/
1001
1002 Procedure GetBatchName (P_Exp_End_Date IN Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE,
1003 X_Batch_Name OUT NOCOPY Pa_Transaction_Interface_All.Batch_Name%TYPE);
1004
1005
1006 -- =======================================================================
1007 -- Start of Comments
1008 -- API Name : IsNumber
1009 -- Type : Private
1010 -- Pre-Reqs : None
1011 -- Type : Function
1012 -- Returns : BOOLEAN
1013 -- Function : This functions determines if the varchar passed back is a number.
1014 --
1015 -- Parameters :
1016 -- IN
1017 -- P_Value - VARCHAR2
1018 /*--------------------------------------------------------------------------*/
1019
1020 Function IsNumber (P_Value IN Varchar2) RETURN Boolean;
1021
1022
1023 -- =======================================================================
1024 -- Start of Comments
1025 -- API Name : GetOrigTrxRef
1026 -- Type : Private
1027 -- Pre-Reqs : None
1028 -- Type : Procedure
1029 -- Returns :
1030 -- Function : This procedure determines the max orig_transaction_reference to
1031 -- : return based on the Building Block Id passed in.
1032 -- : Since this procedure is only called when OTL is sending a detail bb for
1033 -- : adjustment of existing data in Projects a NO_DATA_FOUND error means data corruption
1034 -- : Used in the Upload_Otc_Timecards() procedure.
1035 --
1036 -- Parameters :
1037 -- IN
1038 -- P_Building_Block_Id - Number
1039 -- OUT
1040 -- X_OrigTrxRef - Varchar2
1041 -- X_Status - Varchar2
1042 /*--------------------------------------------------------------------------*/
1043
1044 Procedure GetOrigTrxRef (P_Building_Block_Id IN Number,
1045 X_OrigTrxRef OUT NOCOPY Varchar2,
1046 X_Status OUT NOCOPY Varchar2);
1047
1048
1049 -- =======================================================================
1050 -- Start of Comments
1051 -- API Name : GetAdditionalTrxData
1052 -- Type : Private
1053 -- Pre-Reqs : None
1054 -- Type : Procedure
1055 -- Returns :
1056 -- Function : Gets all the addition data needed to insert records into the Trx Interface table
1057 --
1058 -- Parameters :
1059 -- IN
1060 -- P_Ei_Date Date
1061 -- P_Person_Id Number
1062 -- OUT
1063 -- X_Org_Id Number
1064 -- X_Error_Status Varchar2
1065 /*--------------------------------------------------------------------------*/
1066
1067 Procedure GetAdditionalTrxData (P_Ei_Date IN Date,
1068 P_Person_Id IN Number,
1069 X_Org_Id OUT NOCOPY Number,
1070 X_Error_Status OUT NOCOPY Varchar2);
1071
1072
1073 -- =======================================================================
1074 -- Start of Comments
1075 -- API Name : BulkInsertReset
1076 -- Type : Private
1077 -- Pre-Reqs : None
1078 -- Type : Procedure
1079 -- Returns :
1080 -- Function : Calls bulk insert API.
1081 -- Empties out all the global pl/sql arrays used for the bulk insert.
1082 -- If the P_Command is 'INSERT' then the bulk insert command table handler is executed,
1083 -- then the pl/sql table arrays are reset for the next time thru.
1084 -- If the P_Command is 'RESET' then pl/sql table arrays are only reset.
1085 --
1086 -- Parameters :
1087 -- IN :
1088 -- : P_Command Varchar2
1089 -- OUT : n/a
1090
1091 /*--------------------------------------------------------------------------*/
1092
1093 Procedure BulkInsertReset (P_Command IN Varchar2);
1094
1095
1096 -- =======================================================================
1097 -- Start of Comments
1098 -- API Name : TrxInCurrentChunk
1099 -- Type : Private
1100 -- Pre-Reqs : None
1101 -- Type : Function
1102 -- Returns : Varchar2
1103 -- Function : Determine if the Trx in part of the current chunk being processed.
1104 --
1105 -- Parameters :
1106 -- IN :
1107 -- : P_Detail_BB_Id - Number
1108 -- OUT : n/a
1109
1110 /*--------------------------------------------------------------------------*/
1111
1112 Function TrxInCurrentChunk (P_Detail_BB_Id IN Number) RETURN Varchar2;
1113
1114
1115 -- =======================================================================
1116 -- Start of Comments
1117 -- API Name : GetProjectManager
1118 -- Type : Public
1119 -- Pre-Reqs : None
1120 -- Type : Function
1121 -- Returns : Number
1122 -- Function : Returns the current project manager, person_id, for a project
1123 --
1124 -- Parameters :
1125 -- IN :
1126 -- : P_Project_Id - Number
1127 -- OUT : n/a
1128
1129 /*--------------------------------------------------------------------------*/
1130
1131 Function GetProjectManager ( P_Project_Id IN Number ) RETURN Number;
1132
1133
1134 -- =======================================================================
1135 -- Start of Comments
1136 -- API Name : GetPersonType
1137 -- Type : Private
1138 -- Pre-Reqs : None
1139 -- Type : Function
1140 -- Returns : Varchar2
1141 -- Function : Returns the Person Type based on the employee id.
1142 -- Valid Values: 'CWK' or 'EMP'
1143 --
1144 -- Parameters :
1145 -- IN :
1146 -- : P_Person_Id - Number
1147 -- : P_Ei_Date - Date
1148 -- OUT : n/a
1149
1150 /*--------------------------------------------------------------------------*/
1151
1152 Function GetPersonType ( P_Person_Id IN Number, P_Ei_Date IN Date) RETURN Varchar2;
1153
1154 -- =======================================================================
1155 -- Start of Comments
1156 -- API Name : GetPOInfo
1157 -- Type : Private
1158 -- Pre-Reqs : None
1159 -- Type : Procedure
1160 -- Returns : n/a
1161 -- Function : Returns the Vendor_Id and PO_Header_Id
1162 --
1163 -- Parameters :
1164 -- IN :
1165 -- : P_PO_Line_Id - Number
1166 -- OUT :
1167 -- : X_Po_Header_Id - Number
1168 -- : X_Vendor_Id - Number
1169
1170 /*--------------------------------------------------------------------------*/
1171
1172 Procedure GetPOInfo(P_Po_Line_Id IN Number,
1173 X_Po_Header_Id OUT NOCOPY Number,
1174 X_Vendor_Id OUT NOCOPY Number);
1175
1176
1177 END;
[Home] [Help]