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