[Home] [Help]
1: Package Body Pa_Otc_Api As
2: -- $Header: PAXVOTCB.pls 120.18.12010000.6 2008/12/19 06:42:43 anuragar ship $
3:
4: -- Package Variables
5:
20: -- Used to indentify the path taken to the point on where an unexpected error occurs.
21: G_Path Varchar2(2000) := ' ';
22:
23: -- These pl/sql table are used for flushing out procedure tables
24: G_Msg_Tokens_Table Pa_Otc_Api.Message_Tokens;
25:
26: -- This pl/sql table stores a list of the expenditure_ending_dates and there associated batch_name
27: -- This pl/sql table is used during the Pre-Import phase of Trx Import
28: G_EndDateBatchName_Table Pa_Otc_Api.EndDateBatchName_Tab;
24: G_Msg_Tokens_Table Pa_Otc_Api.Message_Tokens;
25:
26: -- This pl/sql table stores a list of the expenditure_ending_dates and there associated batch_name
27: -- This pl/sql table is used during the Pre-Import phase of Trx Import
28: G_EndDateBatchName_Table Pa_Otc_Api.EndDateBatchName_Tab;
29:
30: -- Used to store the binary_index for the pl/sql table records inserted into the interface table so that can speed up
31: -- the tieback process.
32: G_Trx_Inserted_Tab Pa_Otc_Api.Trx_Inserted_Table;
28: G_EndDateBatchName_Table Pa_Otc_Api.EndDateBatchName_Tab;
29:
30: -- Used to store the binary_index for the pl/sql table records inserted into the interface table so that can speed up
31: -- the tieback process.
32: G_Trx_Inserted_Tab Pa_Otc_Api.Trx_Inserted_Table;
33:
34: -- Used to store the binary_index for pl/sql table records that were directly updated. This is so that can speed up
35: -- the tieback process.
36: G_Trx_Direct_Upd_Tab Pa_Otc_Api.Trx_Inserted_Table;
32: G_Trx_Inserted_Tab Pa_Otc_Api.Trx_Inserted_Table;
33:
34: -- Used to store the binary_index for pl/sql table records that were directly updated. This is so that can speed up
35: -- the tieback process.
36: G_Trx_Direct_Upd_Tab Pa_Otc_Api.Trx_Inserted_Table;
37:
38: -- Used as a check flag for the looping that is now been added in Trx Import for OTL. The flag is used within
39: -- the exception handler for Upload_Otc_Timecard() procedure.
40: G_Processed_Import_Batch Boolean := Null;
235: -- P_Message_Table - Hxc_User_Type_Definition_Grp.Message_Table%TYPE
236: -- P_Message_Name - Fnd_New_Messages.Message_Name%TYPE
237: -- P_Message_Level - Varchar2
238: -- P_Message_Field - Varchar2
239: -- P_Msg_Tokens - Pa_Otc_Api.Messages_Tokens
240: -- P_Time_Building_Block_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
241: -- P_Time_Attribute_Id - Hxc_Time_Attributes.Time_Attribute_Id%TYPE
242: -- P_Message_App - Varchar2 Default 'PA'
243: --
251: P_Message_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Message_Table, -- 2672653
252: P_Message_Name IN Fnd_New_Messages.Message_Name%TYPE,
253: P_Message_Level IN Varchar2,
254: P_Message_Field IN Varchar2,
255: P_Msg_Tokens IN Pa_Otc_Api.Message_Tokens,
256: P_Time_Building_Block_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
257: P_Time_Attribute_Id IN Hxc_Time_Attributes.Time_Attribute_Id%TYPE,
258: P_Message_App IN Varchar2 )
259:
264:
265: Begin
266:
267: G_Stage := 'Entering Add_Error_To_Table().';
268: Pa_Otc_Api.TrackPath('ADD','Add_Error_To_Table');
269:
270: G_Stage := 'Determine Error token count.';
271: If P_Msg_Tokens.Count > 0 Then
272:
315: G_Stage := 'Assign Time Attribute Id to error table record.';
316: P_Message_Table(l_last_index).Time_Attribute_Id := P_Time_Attribute_Id;
317:
318: G_Stage := 'Leaving Add_Error_To_Table() procedure.';
319: Pa_Otc_Api.TrackPath('STRIP','Add_Error_To_Table');
320:
321: Exception
322: When Others Then
323: Raise;
363: l_Org_Id Hr_All_Organization_Units.Organization_Id%TYPE := NULL;
364: l_Direct_Update_Flag Boolean := False;
365: l_Comment_Or_Dff Varchar2(1);
366: l_Comment Varchar2(150);
367: l_New_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
368: l_Old_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
369: l_Temp_Timecard_Rec Pa_Otc_Api.Timecard_Rec := NULL;
370: l_Old_Detail_Index Binary_Integer := 0;
371: l_Error_Code Varchar2(30) := NULL;
364: l_Direct_Update_Flag Boolean := False;
365: l_Comment_Or_Dff Varchar2(1);
366: l_Comment Varchar2(150);
367: l_New_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
368: l_Old_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
369: l_Temp_Timecard_Rec Pa_Otc_Api.Timecard_Rec := NULL;
370: l_Old_Detail_Index Binary_Integer := 0;
371: l_Error_Code Varchar2(30) := NULL;
372: l_dummy Number := NULL;
365: l_Comment_Or_Dff Varchar2(1);
366: l_Comment Varchar2(150);
367: l_New_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
368: l_Old_Timecard_Rec Pa_Otc_Api.Timecard_Rec;
369: l_Temp_Timecard_Rec Pa_Otc_Api.Timecard_Rec := NULL;
370: l_Old_Detail_Index Binary_Integer := 0;
371: l_Error_Code Varchar2(30) := NULL;
372: l_dummy Number := NULL;
373: l_Error_Text Varchar2(2000);
415: G_Path := ' ';
416:
417: G_Stage := 'Entering Upload_Otc_Timecard(), add procedure to trackpath.';
418: If G_Debug_Mode = 'Y' Then
419: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
420: pa_cc_utils.log_message( Pa_Debug.G_Err_Stage,1);
421: End If;
422: Pa_Otc_Api.TrackPath('ADD','Upload_Otc_Timecards');
423:
418: If G_Debug_Mode = 'Y' Then
419: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
420: pa_cc_utils.log_message( Pa_Debug.G_Err_Stage,1);
421: End If;
422: Pa_Otc_Api.TrackPath('ADD','Upload_Otc_Timecards');
423:
424: If G_Processed_Import_Batch is Null Then
425:
426: G_Stage := 'Single time process initialization section needed.';
424: If G_Processed_Import_Batch is Null Then
425:
426: G_Stage := 'Single time process initialization section needed.';
427: If G_Debug_Mode = 'Y' Then
428: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
429: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
430: End If;
431:
432: G_Stage := 'Call BulkInsertReset() procedure for reset.';
430: End If;
431:
432: G_Stage := 'Call BulkInsertReset() procedure for reset.';
433: If G_Debug_Mode = 'Y' Then
434: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
435: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
436: End If;
437: Pa_Otc_Api.BulkInsertReset(P_Command => 'RESET');
438:
433: If G_Debug_Mode = 'Y' Then
434: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
435: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
436: End If;
437: Pa_Otc_Api.BulkInsertReset(P_Command => 'RESET');
438:
439: G_Stage := 'Initialize what remains.';
440: If G_Debug_Mode = 'Y' Then
441: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
437: Pa_Otc_Api.BulkInsertReset(P_Command => 'RESET');
438:
439: G_Stage := 'Initialize what remains.';
440: If G_Debug_Mode = 'Y' Then
441: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
442: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
443: End If;
444: G_Processed_Import_Batch := FALSE;
445: G_EndDateBatchName_Table.Delete;
448: End If;
449:
450: G_Stage := 'Each time Upload_Otc_Timecards() called initializaion of global variables.';
451: If G_Debug_Mode = 'Y' Then
452: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
453: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
454: End If;
455:
456: G_Old_Detail_Attr_Index := 0;
471: If Pa_Utils.Pa_Morg_Implemented = 'Y' Then
472:
473: G_Stage := 'Create where clause.';
474: If G_Debug_Mode = 'Y' Then
475: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
476: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
477: End If;
478:
479: -- 12i MOAC changes
483: Else
484:
485: G_Stage := 'No where clause.';
486: If G_Debug_Mode = 'Y' Then
487: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
488: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
489: End If;
490:
491: l_Where_Clause := NULL;
512: */
513:
514: G_Stage := 'Call execute retrieval process API.';
515: If G_Debug_Mode = 'Y' Then
516: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
517: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
518: End If;
519: Hxc_Integration_Layer_V1_Grp.Execute_Retrieval_Process(
520: P_Process => 'Projects Retrieval Process',
532: Pa_Trx_Import.G_Exit_Main := FALSE;
533:
534: G_Stage := 'Loop thru all hxc detail building block records.';
535: If G_Debug_Mode = 'Y' Then
536: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
537: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
538: End If;
539: For i IN Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks.First .. Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks.Last
540: Loop
541:
542: G_Stage := 'Process bb/ovn: ' || to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) || ':' ||
543: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn) ||
544: ' for Resource_Id(Person_Id): ' || to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Resource_Id);
545: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
546: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
547:
548: Begin
549:
548: Begin
549:
550: G_Stage := 'Primary condition evaluation within the loop.';
551: If G_Debug_Mode = 'Y' Then
552: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
553: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
554: End If;
555:
556: If Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Changed = 'N' and
556: If Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Changed = 'N' and
557: Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'Y' Then
558:
559: G_Stage := 'Item Changed: N, Deleted: Y.';
560: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
561: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
562:
563:
564: -- Even though the building block was deleted prior to being imported into projects
569: -- There is also no need to look at the old pl/sql tables records.
570:
571: G_Stage := 'Call Populate Project Record new 1 for positioning purposes only.';
572: If G_Debug_Mode = 'Y' Then
573: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
574: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
575: End If;
576:
577: Pa_Otc_Api.PopulateProjRec(
573: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
574: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
575: End If;
576:
577: Pa_Otc_Api.PopulateProjRec(
578: P_New_Old_BB => 'NEW',
579: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
580: P_Detail_Index => i,
581: P_Old_Detl_Ind => l_Old_Detail_Index,
591: -- If this is the condition then there is only new data to process via PopulateProjRec() procedure
592: -- and there is no need to look at the old data via the same procedure.
593:
594: G_Stage := 'Item Changed: N, Deleted: N.';
595: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
596: pa_cc_Utils.log_message(Pa_Debug.G_Err_Stage,1);
597:
598: G_Stage := 'Call Populate Project Record new 2.';
599: If G_Debug_Mode = 'Y' Then
596: pa_cc_Utils.log_message(Pa_Debug.G_Err_Stage,1);
597:
598: G_Stage := 'Call Populate Project Record new 2.';
599: If G_Debug_Mode = 'Y' Then
600: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
601: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
602: End If;
603:
604: Pa_Otc_Api.PopulateProjRec(
600: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
601: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
602: End If;
603:
604: Pa_Otc_Api.PopulateProjRec(
605: P_New_Old_BB => 'NEW',
606: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
607: P_Detail_Index => i,
608: P_Old_Detl_Ind => l_Old_Detail_Index,
609: P_Timecard_Rec => l_New_Timecard_Rec);
610:
611: G_Stage := 'EI data status check 2.';
612: If G_Debug_Mode = 'Y' Then
613: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
614: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
615: End If;
616:
617: If l_New_Timecard_Rec.Status is Not Null Then
617: If l_New_Timecard_Rec.Status is Not Null Then
618:
619: G_Stage := 'New Record Status is not null 2. Timecard status is ' || l_New_Timecard_Rec.Status;
620: If G_Debug_Mode = 'Y' Then
621: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
622: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
623: End If;
624:
625: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
629: Else
630:
631: G_Stage := 'Create new orig trx ref 2.';
632: If G_Debug_Mode = 'Y' Then
633: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
634: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
635: End If;
636:
637: l_new_orig_trx_ref :=
639: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn);
640:
641: G_Stage := 'Check if original transaction reference already exists 2';
642: If G_Debug_Mode = 'Y' Then
643: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
644: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
645: End If;
646:
647: -- Calling the function is a sanity check to confirm that this is solely new data.
644: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
645: End If;
646:
647: -- Calling the function is a sanity check to confirm that this is solely new data.
648: If NOT Pa_Otc_Api.OrigTrxRefValueExists(l_new_orig_trx_ref) Then
649:
650: -- Insert a new record. There should be no records in either
651: -- pl/sql tables t_old_detail_bld_blks or t_old_detail_attributes
652: -- for this building block id.
655: l_insert_rec_flag := TRUE;
656:
657: G_Stage := 'Call the GetBatchName procedure 2';
658: If G_Debug_Mode = 'Y' Then
659: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
660: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
661: End If;
662:
663: Pa_Otc_Api.GetBatchName(
659: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
660: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
661: End If;
662:
663: Pa_Otc_Api.GetBatchName(
664: P_Exp_End_Date => l_New_Timecard_Rec.Expenditure_Ending_Date,
665: X_Batch_Name => l_Batch_Name);
666:
667: Else
668: -- This condition result should never happen, but it's here just in case.
669:
670: G_Stage := 'Building block/ovn combo already exists in projects 2.';
671: If G_Debug_Mode = 'Y' Then
672: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
673: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
674: End If;
675: l_insert_rec_flag := FALSE;
676: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'SUCCESS';
682: ElsIf Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Changed = 'Y' and
683: Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'N' Then
684:
685: G_Stage := 'Item Changed: Y, Deleted: N.';
686: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
687: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
688:
689: l_Old_Detail_Index := l_Old_Detail_Index + 1;
690:
689: l_Old_Detail_Index := l_Old_Detail_Index + 1;
690:
691: G_Stage := 'Create new orig trx ref 3.';
692: If G_Debug_Mode = 'Y' Then
693: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
694: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
695: End If;
696:
697: l_new_orig_trx_ref :=
697: l_new_orig_trx_ref :=
698: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) || ':' ||
699: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn);
700:
701: If NOT Pa_Otc_Api.OrigTrxRefValueExists(l_new_orig_trx_ref) Then
702:
703: G_Stage := 'Call Populate Project Record new 3a.';
704: If G_Debug_Mode = 'Y' Then
705: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
701: If NOT Pa_Otc_Api.OrigTrxRefValueExists(l_new_orig_trx_ref) Then
702:
703: G_Stage := 'Call Populate Project Record new 3a.';
704: If G_Debug_Mode = 'Y' Then
705: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
706: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
707: End If;
708:
709: Pa_Otc_Api.PopulateProjRec(
705: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
706: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
707: End If;
708:
709: Pa_Otc_Api.PopulateProjRec(
710: P_New_Old_BB => 'NEW',
711: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
712: P_Detail_Index => i,
713: P_Old_Detl_Ind => l_Old_Detail_Index,
714: P_timecard_Rec => l_New_Timecard_Rec);
715:
716: G_Stage := 'EI data status check 3a.';
717: If G_Debug_Mode = 'Y' Then
718: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
719: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
720: End If;
721:
722: If l_New_Timecard_Rec.Status is Not Null Then
722: If l_New_Timecard_Rec.Status is Not Null Then
723:
724: G_Stage := 'New Record Status is not null 3a. Timecard status is ' || l_New_Timecard_Rec.Status;
725: If G_Debug_Mode = 'Y' Then
726: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
727: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
728: End If;
729:
730: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
734: G_Stage := 'Since the Status is not null 3a has been hit for new record, ' ||
735: 'call PopulateProjRec() for old records for positioning ' ||
736: 'purposes only to maintain sync with new pl/sql tables.';
737: If G_Debug_Mode = 'Y' Then
738: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
739: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
740: End If;
741:
742: Pa_Otc_Api.PopulateProjRec(
738: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
739: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
740: End If;
741:
742: Pa_Otc_Api.PopulateProjRec(
743: P_New_Old_BB => 'OLD',
744: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).bb_id,
745: P_Detail_Index => i,
746: P_Old_Detl_Ind => l_Old_Detail_Index,
752: Else
753:
754: G_Stage := 'Call Populate Project Record old 3a.';
755: If G_Debug_Mode = 'Y' Then
756: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
757: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
758: End If;
759:
760: Pa_Otc_Api.PopulateProjRec(
756: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
757: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
758: End If;
759:
760: Pa_Otc_Api.PopulateProjRec(
761: P_New_Old_BB => 'OLD',
762: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).bb_id,
763: P_Detail_Index => i,
764: P_Old_Detl_Ind => l_Old_Detail_Index,
767: If l_Old_Timecard_Rec.Status is Not Null Then
768:
769: G_Stage := 'Old Record Status is not null 3a. Timecard status is ' || l_Old_Timecard_Rec.Status;
770: If G_Debug_Mode = 'Y' Then
771: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
772: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
773: End If;
774:
775: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
782: -- insert record into pa_transaction_interface or update tables
783: -- pa_expenditure_comments and/or pa_expenditure_items_all.
784: G_Stage := 'Call Determine Direct Update 3.';
785: If G_Debug_Mode = 'Y' Then
786: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
787: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
788: End If;
789:
790: Pa_Otc_Api.DetermineDirectUpdate(
786: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
787: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
788: End If;
789:
790: Pa_Otc_Api.DetermineDirectUpdate(
791: P_New_Timecard_Rec => l_New_Timecard_Rec,
792: P_Old_Timecard_Rec => l_Old_Timecard_Rec,
793: P_Direct_Update_Flag => l_direct_update_flag,
794: P_Comment_or_Dff => l_Comment_or_Dff);
798: l_insert_rec_flag := FALSE;
799:
800: G_Stage := 'Direct Update of the exp item in projects. ' ||
801: 'Need to update (C)omment, (D)ff, (B)oth: ' || l_Comment_or_Dff;
802: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
803: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
804:
805: G_Stage := 'Direct Update get trx ref. by calling GetOrigTrxRef '||
806: 'to use to update the expenditure item.';
804:
805: G_Stage := 'Direct Update get trx ref. by calling GetOrigTrxRef '||
806: 'to use to update the expenditure item.';
807: If G_Debug_Mode = 'Y' Then
808: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
809: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
810: End If;
811:
812: Pa_Otc_Api.GetOrigTrxRef(
808: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
809: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
810: End If;
811:
812: Pa_Otc_Api.GetOrigTrxRef(
813: P_Building_Block_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
814: X_OrigTrxRef => l_old_orig_trx_ref,
815: X_Status => l_New_Timecard_Rec.Status);
816:
817: If l_New_Timecard_Rec.Status is not null Then
818:
819: G_Stage := 'Update hxc exception pl/sql tables 1.';
820: If G_Debug_Mode = 'Y' Then
821: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
822: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
823: End If;
824:
825: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
829: End If;
830:
831: G_Stage := 'Call Update Changed Original Txn 3.';
832: If G_Debug_Mode = 'Y' Then
833: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
834: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
835: End If;
836:
837: Pa_Otc_Api.UpdateChangedOrigTxn(
833: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
834: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
835: End If;
836:
837: Pa_Otc_Api.UpdateChangedOrigTxn(
838: P_Old_Orig_Txn_Ref => l_old_orig_trx_ref,
839: P_New_Orig_Txn_Ref => l_new_orig_trx_ref,
840: P_Comment_or_Dff => l_comment_or_dff,
841: P_Timecard_Rec => l_new_timecard_rec,
843:
844: G_Stage := 'Store the Bb_Id and index in pl/sql table for use ' ||
845: 'in tieback process 3a for directly updated ei.';
846: If G_Debug_Mode = 'Y' Then
847: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
848: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
849: End If;
850:
851: G_Trx_Direct_Upd_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
855: l_insert_rec_flag := TRUE;
856:
857: G_Stage := 'Call the GetBatchName procedure 3';
858: If G_Debug_Mode = 'Y' Then
859: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
860: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
861: End If;
862:
863: Pa_Otc_Api.GetBatchName(
859: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
860: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
861: End If;
862:
863: Pa_Otc_Api.GetBatchName(
864: P_Exp_End_Date => l_New_Timecard_Rec.Expenditure_Ending_Date,
865: X_Batch_Name => l_Batch_Name);
866:
867: G_Stage := 'Get old orig trx ref 3 using the new bb_id/ovn ' || 'combo ' ||
867: G_Stage := 'Get old orig trx ref 3 using the new bb_id/ovn ' || 'combo ' ||
868: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) || ':' ||
869: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn);
870: If G_Debug_Mode = 'Y' Then
871: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
872: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
873: End If;
874:
875: -- Bug ?
877: -- ei is available for adjustment in projects
878: G_Stage := 'Calling procedure GetOrigTrxRef() Determine if ' ||
879: 'availability of ei for adjusted 1';
880: If G_Debug_Mode = 'Y' Then
881: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
882: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
883: End If;
884:
885: Pa_Otc_Api.GetOrigTrxRef(
881: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
882: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
883: End If;
884:
885: Pa_Otc_Api.GetOrigTrxRef(
886: P_Building_Block_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
887: X_OrigTrxRef => l_old_orig_trx_ref,
888: X_Status => l_New_Timecard_Rec.Status);
889:
890: If l_New_Timecard_Rec.Status is not null Then
891:
892: G_Stage := 'Update hxc exception pl/sql tables 2.';
893: If G_Debug_Mode = 'Y' Then
894: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
895: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
896: End If;
897: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
898: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Exception(i) :=
901:
902: End If;
903:
904: -- l_old_orig_trx_ref :=
905: -- Pa_Otc_Api.GetOrigTrxRef(
906: -- Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id);
907:
908: -- End bug enhancement
909:
908: -- End bug enhancement
909:
910: G_Stage := 'Call Build Reverse Item 3.';
911: If G_Debug_Mode = 'Y' Then
912: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
913: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
914: End If;
915:
916: Pa_Otc_Api.Build_Reverse_Item(
912: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
913: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
914: End If;
915:
916: Pa_Otc_Api.Build_Reverse_Item(
917: P_Old_Orig_Trx_Ref => l_old_orig_trx_ref,
918: P_New_Orig_Trx_Ref => l_new_orig_trx_ref,
919: P_Batch_Name => l_batch_name,
920: P_User_Id => P_User_Id,
923:
924: G_Stage := 'Store the Bb_Id and index in pl/sql table for use ' ||
925: 'in tieback process 3b.';
926: If G_Debug_Mode = 'Y' Then
927: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
928: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
929: End If;
930: G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
931:
949: G_Stage := 'The bb_id/ovn combo already exists in projects, bypassing BB_Id: ' ||
950: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) ||
951: ':' || to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn) ||
952: ' for Resource_Id: ' || to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Resource_Id);
953: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
954: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
955:
956: G_Stage := 'Call Populate Project Record new 3b for positioning purposes only.';
957: If G_Debug_Mode = 'Y' Then
954: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
955:
956: G_Stage := 'Call Populate Project Record new 3b for positioning purposes only.';
957: If G_Debug_Mode = 'Y' Then
958: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
959: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
960: End If;
961:
962: Pa_Otc_Api.PopulateProjRec(
958: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
959: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
960: End If;
961:
962: Pa_Otc_Api.PopulateProjRec(
963: P_New_Old_BB => 'NEW',
964: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
965: P_Detail_Index => i,
966: P_Old_Detl_Ind => l_Old_Detail_Index,
967: P_timecard_Rec => l_New_Timecard_Rec);
968:
969: G_Stage := 'Call Populate Project Record old 3b for positioning purposes only.';
970: If G_Debug_Mode = 'Y' Then
971: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
972: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
973: End If;
974:
975: Pa_Otc_Api.PopulateProjRec(
971: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
972: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
973: End If;
974:
975: Pa_Otc_Api.PopulateProjRec(
976: P_New_Old_BB => 'OLD',
977: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).bb_id,
978: P_Detail_Index => i,
979: P_Old_Detl_Ind => l_Old_Detail_Index,
990: -- The item was deleted in OTL after it was imported into Projects.
991: -- Need to Reverse out the original transaction.
992:
993: G_Stage := 'Item Changed: Y, Deleted: Y.';
994: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
995: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
996:
997: l_insert_rec_flag := FALSE;
998: l_Old_Detail_Index := l_Old_Detail_Index + 1;
998: l_Old_Detail_Index := l_Old_Detail_Index + 1;
999:
1000: G_Stage := 'Create new orig trx ref 4.';
1001: If G_Debug_Mode = 'Y' Then
1002: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1003: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1004: End If;
1005:
1006: l_new_orig_trx_ref := to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) || ':' ||
1007: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn);
1008:
1009: G_Stage := 'Call Populate Project Record new 4.';
1010: If G_Debug_Mode = 'Y' Then
1011: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1012: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1013: End If;
1014:
1015: Pa_Otc_Api.PopulateProjRec(
1011: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1012: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1013: End If;
1014:
1015: Pa_Otc_Api.PopulateProjRec(
1016: P_New_Old_BB => 'NEW',
1017: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
1018: P_Detail_Index => i,
1019: P_Old_Detl_Ind => l_Old_Detail_Index,
1020: P_timecard_Rec => l_New_Timecard_Rec);
1021:
1022: G_Stage := 'EI data status check 4.';
1023: If G_Debug_Mode = 'Y' Then
1024: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1025: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1026: End If;
1027:
1028: If l_New_Timecard_Rec.Status is Not Null Then
1028: If l_New_Timecard_Rec.Status is Not Null Then
1029:
1030: G_Stage := 'Status is not null 4. Timecard status is ' || l_New_Timecard_Rec.Status;
1031: If G_Debug_Mode = 'Y' Then
1032: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1033: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1034: End If;
1035:
1036: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
1039: G_Stage := 'Since Status is not null 4 occured for new record, call the ' ||
1040: 'PopulateProjRec() procedure for the old record for positioning purposes ' ||
1041: 'so as to maintain sync with new pl/sql tables.';
1042: If G_Debug_Mode = 'Y' Then
1043: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1044: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1045: End If;
1046:
1047: Pa_Otc_Api.PopulateProjRec(
1043: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1044: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1045: End If;
1046:
1047: Pa_Otc_Api.PopulateProjRec(
1048: P_New_Old_BB => 'OLD',
1049: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).bb_id,
1050: P_Detail_Index => i,
1051: P_Old_Detl_Ind => l_Old_Detail_Index,
1057: Else
1058:
1059: G_Stage := 'Call the GetBatchName procedure 4';
1060: If G_Debug_Mode = 'Y' Then
1061: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1062: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1063: End If;
1064:
1065: Pa_Otc_Api.GetBatchName(
1061: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1062: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1063: End If;
1064:
1065: Pa_Otc_Api.GetBatchName(
1066: P_Exp_End_Date => l_New_Timecard_Rec.Expenditure_Ending_Date,
1067: X_Batch_Name => l_Batch_Name);
1068:
1069: G_Stage := 'Call Populate Project Record old 4 positioning purposes only.';
1067: X_Batch_Name => l_Batch_Name);
1068:
1069: G_Stage := 'Call Populate Project Record old 4 positioning purposes only.';
1070: If G_Debug_Mode = 'Y' Then
1071: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1072: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1073: End If;
1074:
1075: Pa_Otc_Api.PopulateProjRec(
1071: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1072: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1073: End If;
1074:
1075: Pa_Otc_Api.PopulateProjRec(
1076: P_New_Old_BB => 'OLD',
1077: P_BB_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).bb_id,
1078: P_Detail_Index => i,
1079: P_Old_Detl_Ind => l_Old_Detail_Index,
1084: G_Stage := 'Create old orig trx ref 4 using bb_id/ovn ' ||
1085: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id) || ':' ||
1086: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Ovn);
1087: If G_Debug_Mode = 'Y' Then
1088: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1089: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1090: End If;
1091:
1092: -- Switched from function to procedure to determine if
1094:
1095: G_Stage := 'Calling procedure GetOrigTrxRef() Determine if ' ||
1096: 'availability of ei for adjustment 4';
1097: If G_Debug_Mode = 'Y' Then
1098: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1099: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1100: End If;
1101:
1102: Pa_Otc_Api.GetOrigTrxRef(
1098: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1099: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1100: End If;
1101:
1102: Pa_Otc_Api.GetOrigTrxRef(
1103: P_Building_Block_Id => Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id,
1104: X_OrigTrxRef => l_old_orig_trx_ref,
1105: X_Status => l_New_Timecard_Rec.Status);
1106:
1107: If l_New_Timecard_Rec.Status is not null Then
1108:
1109: G_Stage := 'Updating hxc exception pl/sql tables 3.';
1110: If G_Debug_Mode = 'Y' Then
1111: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1112: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1113: End If;
1114:
1115: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(i) := 'ERRORS';
1118:
1119: End If;
1120:
1121: -- l_old_orig_trx_ref :=
1122: -- Pa_Otc_Api.GetOrigTrxRef(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id);
1123:
1124: -- End switch from function to procedure
1125:
1126: G_Stage := 'Call Build Reverse Item 4.';
1124: -- End switch from function to procedure
1125:
1126: G_Stage := 'Call Build Reverse Item 4.';
1127: If G_Debug_Mode = 'Y' Then
1128: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1129: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1130: End If;
1131:
1132: l_error_code := NULL;
1130: End If;
1131:
1132: l_error_code := NULL;
1133:
1134: Pa_Otc_Api.Build_Reverse_Item(
1135: P_Old_Orig_Trx_Ref => l_old_orig_trx_ref,
1136: P_New_Orig_Trx_Ref => l_new_orig_trx_ref,
1137: P_Batch_Name => l_batch_name,
1138: P_User_id => P_User_Id,
1140: P_Xface_Id => P_Xface_id);
1141:
1142: G_Stage := 'Store the Bb_Id and index in pl/sql table for use in tieback process 4.';
1143: If G_Debug_Mode = 'Y' Then
1144: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1145: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1146: End If;
1147: G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
1148:
1153: If l_insert_rec_flag Then
1154:
1155: G_Stage := 'Get the next available Transaction Interface Id from sequence.';
1156: If G_Debug_Mode = 'Y' Then
1157: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1158: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1159: End If;
1160:
1161: select pa_txn_interface_s.nextval
1163: from dual;
1164:
1165: G_Stage := 'Store new/changed Trx Import Rec in pl/sql arrays for BB/Ovn: ' || l_New_Orig_Trx_Ref ||
1166: ' for Resource_Id(Person_Id): ' || to_char(l_New_Timecard_Rec.Incurred_By_Person_Id);
1167: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
1168: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1169:
1170: G_Stage := 'Store Trx Import record in pl/sql arrays for bulk insert.';
1171: If G_Debug_Mode = 'Y' Then
1168: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1169:
1170: G_Stage := 'Store Trx Import record in pl/sql arrays for bulk insert.';
1171: If G_Debug_Mode = 'Y' Then
1172: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1173: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1174: End If;
1175: /* Bug 6698171 */ if (l_New_Timecard_Rec.Person_Type = 'EMP') then /* Added if..else for bug 7505424 */
1176: patc.check_termination (l_New_Timecard_Rec.Incurred_By_Person_Id, l_New_Timecard_Rec.Expenditure_Item_Date, l_ac_termination_date);
1289: G_OU_Tbl(G_Trx_Import_Index) := G_Current_Org_Id;
1290:
1291: G_Stage := 'Pl/sql record counter increment.';
1292: If G_Debug_Mode = 'Y' Then
1293: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1294: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1295: End If;
1296: G_Txn_Rec_Count := nvl(G_Txn_Rec_Count,0) + 1;
1297:
1296: G_Txn_Rec_Count := nvl(G_Txn_Rec_Count,0) + 1;
1297:
1298: G_Stage := 'Store the Bb_Id and index in pl/sql table for use in tieback process.';
1299: If G_Debug_Mode = 'Y' Then
1300: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1301: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1302: End If;
1303: G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
1304:
1305: End If;
1306:
1307: G_Stage := 'Check if need to call bulk insert.';
1308: If G_Debug_Mode = 'Y' Then
1309: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1310: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1311: End If;
1312: If G_Txn_Rec_Count >= L_MAX_RECS_FOR_BULKINSERT Then
1313:
1312: If G_Txn_Rec_Count >= L_MAX_RECS_FOR_BULKINSERT Then
1313:
1314: G_Stage := 'Call BulkInsertReset() procedure for insert.';
1315: If G_Debug_Mode = 'Y' Then
1316: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1317: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1318: End If;
1319: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1320:
1315: If G_Debug_Mode = 'Y' Then
1316: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1317: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1318: End If;
1319: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1320:
1321: G_Stage := 'Store total records inserted. Reset counter to 0.';
1322: If G_Debug_Mode = 'Y' Then
1323: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1319: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1320:
1321: G_Stage := 'Store total records inserted. Reset counter to 0.';
1322: If G_Debug_Mode = 'Y' Then
1323: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1324: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1325: End If;
1326: Pa_Trx_Import.G_Batch_Size := Nvl(Pa_Trx_Import.G_Batch_Size,0) + G_Txn_Rec_Count;
1327: G_Txn_Rec_Count := 0;
1337: G_Unhandled_Except_Cnt := G_Unhandled_Except_Cnt + 1;
1338:
1339: G_Stage := 'Unhandled exception count is now ' || to_char(G_Unhandled_Except_Cnt);
1340: If G_Debug_Mode = 'Y' Then
1341: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1342: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1343: End If;
1344:
1345: If G_Unhandled_Except_Cnt > G_EXCEPT_CNT_ALLOWED Then
1346:
1347: G_Stage := 'Unhandled exceptions count exceeds maximum allowed of ' ||
1348: to_char(G_EXCEPT_CNT_ALLOWED) || '. Raising user defined exception!';
1349: If G_Debug_Mode = 'Y' Then
1350: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1351: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1352: End If;
1353:
1354: Raise HXC_RETRIEVAL_MAX_ERRORS;
1361: End;
1362:
1363: G_Stage := 'Resetting the pl/sql new and old timecard record variables.';
1364: If G_Debug_Mode = 'Y' Then
1365: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1366: Pa_Cc_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
1367: End If;
1368: l_New_Timecard_Rec := l_Temp_Timecard_Rec;
1369: l_Old_Timecard_Rec := l_Temp_Timecard_Rec;
1371: End Loop;
1372:
1373: G_Stage := 'Exited the loop. Check if need to call bulk insert for any remaining pl/sql records.';
1374: If G_Debug_Mode = 'Y' Then
1375: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1376: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1377: End If;
1378:
1379: If G_Txn_Rec_Count > 0 Then
1379: If G_Txn_Rec_Count > 0 Then
1380:
1381: G_Stage := 'Call bulk insert for the remaining pl/sql records.';
1382: If G_Debug_Mode = 'Y' Then
1383: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1384: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1385: End If;
1386: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1387:
1382: If G_Debug_Mode = 'Y' Then
1383: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1384: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1385: End If;
1386: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1387:
1388: G_Stage := 'Set the final records count for the next import phase. Reset counter.';
1389: If G_Debug_Mode = 'Y' Then
1390: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1386: Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
1387:
1388: G_Stage := 'Set the final records count for the next import phase. Reset counter.';
1389: If G_Debug_Mode = 'Y' Then
1390: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1391: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1392: End If;
1393: Pa_Trx_Import.G_Batch_Size := Nvl(Pa_Trx_Import.G_Batch_Size,0) + G_Txn_Rec_Count;
1394:
1399: G_Stage := 'This chunk has ' || to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks.COUNT) ||
1400: ' records, of which ' || to_char(Pa_Trx_Import.G_Batch_Size) || ' are to be processed' ||
1401: ' in the next phase of Trx Import.';
1402: If G_Debug_Mode = 'Y' Then
1403: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1404: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1405: End If;
1406:
1407: Else
1407: Else
1408:
1409: G_Stage := 'Last chunk from OTL. Flag process we are done. No further records to process.';
1410: If G_Debug_Mode = 'Y' Then
1411: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1412: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1413: End If;
1414:
1415: Pa_Trx_Import.G_Exit_Main := NULL;
1416: Pa_Trx_Import.G_Batch_Size := 0;
1417:
1418: G_Stage := 'Call Hxc_Generic_Retrieval_Pkg.Update_Transaction_Status() one last time.';
1419: If G_Debug_Mode = 'Y' Then
1420: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1421: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1422: End If;
1423: Hxc_Integration_Layer_V1_Grp.Set_Parent_Statuses;
1424: Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
1429: End If; -- Are there records in the otl pl/sql tables to process
1430:
1431: G_Stage := 'Leaving Upload_Otc_Timecards(), strip procedure from trackpath.';
1432: If G_Debug_Mode = 'Y' Then
1433: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1434: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1435: End If;
1436: Pa_Otc_Api.TrackPath('STRIP','Upload_Otc_Timecards');
1437:
1432: If G_Debug_Mode = 'Y' Then
1433: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1434: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1435: End If;
1436: Pa_Otc_Api.TrackPath('STRIP','Upload_Otc_Timecards');
1437:
1438: Exception
1439:
1440: When HXC_RETRIEVAL_MAX_ERRORS Then
1470: -- Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
1471: -- (P_Process => 'Projects Retrieval Process',
1472: -- P_Status => 'ERRORS',
1473: -- P_Exception_Description =>
1474: -- substr('Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage ||
1475: -- ' : ' || SQLERRM, 1, 2000));
1476: --
1477: -- Fnd_Message.Raise_Error;
1478: --
1485:
1486: fnd_message.set_name('HXC','HXC_0013_GNRET_NO_BLD_BLKS');
1487:
1488: End If;
1489: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || fnd_message.get;
1490: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1491:
1492: -- End bug 3422899
1493:
1492: -- End bug 3422899
1493:
1494: Else
1495:
1496: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM;
1497: Hxc_Integration_Layer_V1_Grp.Set_Parent_Statuses;
1498: Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
1499: (P_Process => 'Projects Retrieval Process',
1500: P_Status => 'ERRORS',
1498: Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
1499: (P_Process => 'Projects Retrieval Process',
1500: P_Status => 'ERRORS',
1501: P_Exception_Description =>
1502: substr('Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM, 1, 2000));
1503: Raise;
1504:
1505: End If;
1506:
1585: Begin
1586:
1587: G_Stage := 'Enter Build_Reverse_Item().';
1588: If G_Debug_Mode = 'Y' Then
1589: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1590: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1591: End If;
1592:
1593: G_Stage := 'Add procedure to track path';
1591: End If;
1592:
1593: G_Stage := 'Add procedure to track path';
1594: If G_Debug_Mode = 'Y' Then
1595: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1596: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1597: End If;
1598: Pa_Otc_Api.TrackPath('ADD','Build_Reverse_Item');
1599:
1594: If G_Debug_Mode = 'Y' Then
1595: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1596: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1597: End If;
1598: Pa_Otc_Api.TrackPath('ADD','Build_Reverse_Item');
1599:
1600: G_Stage := 'Get needed data to build reversing item phase 1.';
1601: If G_Debug_Mode = 'Y' Then
1602: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1598: Pa_Otc_Api.TrackPath('ADD','Build_Reverse_Item');
1599:
1600: G_Stage := 'Get needed data to build reversing item phase 1.';
1601: If G_Debug_Mode = 'Y' Then
1602: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1603: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1604: End If;
1605:
1606: Select
1688: And EI.Adjusted_Expenditure_Item_Id is Null;
1689:
1690: G_Stage := 'Get Transaction Interface Id from sequence for reversing item.';
1691: If G_Debug_Mode = 'Y' Then
1692: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1693: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1694: End If;
1695:
1696: Select Pa_Txn_Interface_S.NextVal
1700: If l_Person_Type = 'CWK' And l_PO_Line_Id Is Not Null Then
1701:
1702: G_Stage := 'Get Supplier Info by calling GetPOInfo() 2.';
1703: If G_Debug_Mode = 'Y' Then
1704: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
1705: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1706: End If;
1707:
1708: Pa_Otc_Api.GetPOInfo(
1704: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
1705: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1706: End If;
1707:
1708: Pa_Otc_Api.GetPOInfo(
1709: P_Po_Line_Id => l_PO_Line_Id,
1710: X_Po_Header_Id => l_PO_Header_Id,
1711: X_Vendor_Id => l_Dummy_Vendor_Id);
1712:
1713: End If;
1714:
1715: G_Stage := 'Inserting reversing item into interface table BB/Ovn: ' || P_New_Orig_Trx_Ref ||
1716: ' for Resource_Id(Person_Id): ' || to_char(l_Inc_By_Person_Id);
1717: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
1718: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1719:
1720: G_Stage := 'Store record for reversing item into arrays for bulk insert later.';
1721: If G_Debug_Mode = 'Y' Then
1718: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
1719:
1720: G_Stage := 'Store record for reversing item into arrays for bulk insert later.';
1721: If G_Debug_Mode = 'Y' Then
1722: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1723: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1724: End If;
1725:
1726: G_Trx_Import_Index := G_Trx_Import_Index + 1;
1834: G_Txn_Rec_Count := Nvl(G_Txn_Rec_Count,0) + 1;
1835:
1836: G_Stage := 'About to leave Build_Reverse_Item(), strip procedure from trackpath.';
1837: If G_Debug_Mode = 'Y' Then
1838: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1839: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1840: End If;
1841: Pa_Otc_Api.TrackPath('STRIP','Build_Reverse_Item');
1842:
1837: If G_Debug_Mode = 'Y' Then
1838: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1839: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1840: End If;
1841: Pa_Otc_Api.TrackPath('STRIP','Build_Reverse_Item');
1842:
1843: G_Stage := 'Leaving Build_Reverse_Item().';
1844: If G_Debug_Mode = 'Y' Then
1845: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1841: Pa_Otc_Api.TrackPath('STRIP','Build_Reverse_Item');
1842:
1843: G_Stage := 'Leaving Build_Reverse_Item().';
1844: If G_Debug_Mode = 'Y' Then
1845: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1846: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1847: End If;
1848:
1849: Exception
1873: -- Parameters :
1874: -- IN P_Old_Orig_Txn_Ref - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
1875: -- P_New_Orig_Txn_Ref - Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
1876: -- P_Comment_Or_Dff - Varchar2(1)
1877: -- P_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
1878: -- P_User_Id - Pa_Expenditure_Items_All.Last_Updated_By%TYPE
1879: --
1880: -- OUT NONE
1881: --
1885: Procedure UpdateChangedOrigTxn(
1886: P_Old_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
1887: P_New_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
1888: P_Comment_Or_Dff IN Varchar2,
1889: P_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
1890: P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE)
1891:
1892: Is
1893:
1912: Begin
1913:
1914: G_Stage := 'Entering UpdateChangedOrigTxn(), add procedure to trackpath.';
1915: If G_Debug_Mode = 'Y' Then
1916: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1917: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1918: End If;
1919: Pa_Otc_Api.TrackPath('ADD','UpdateChangedOrigTxn');
1920:
1915: If G_Debug_Mode = 'Y' Then
1916: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1917: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1918: End If;
1919: Pa_Otc_Api.TrackPath('ADD','UpdateChangedOrigTxn');
1920:
1921: G_Stage := 'Get Expenditure Item Id.';
1922: If G_Debug_Mode = 'Y' Then
1923: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1919: Pa_Otc_Api.TrackPath('ADD','UpdateChangedOrigTxn');
1920:
1921: G_Stage := 'Get Expenditure Item Id.';
1922: If G_Debug_Mode = 'Y' Then
1923: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1924: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1925: End If;
1926:
1927: Select
1947: * when P_Comment_Or_Dff has a value of B.
1948: */
1949: G_Stage := 'What updating needs to be done.';
1950: If G_Debug_Mode = 'Y' Then
1951: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1952: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1953: End If;
1954:
1955: If P_Comment_Or_Dff = 'C' Then
1955: If P_Comment_Or_Dff = 'C' Then
1956:
1957: G_Stage := 'P_Comment_Or_Dff Is C: Update ei table.';
1958: If G_Debug_Mode = 'Y' Then
1959: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1960: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1961: End If;
1962:
1963: Update Pa_Expenditure_Items_All
1978: If l_Comment_Count = 0 Then
1979:
1980: G_Stage := 'P_Comment_Or_Dff Is C: Insert record into exp comment table.';
1981: If G_Debug_Mode = 'Y' Then
1982: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
1983: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
1984: End If;
1985:
1986: Insert into Pa_Expenditure_Comments
2013: ElsIf l_Comment_Count > 0 And P_Timecard_Rec.Expenditure_Item_Comment Is Not Null Then -- Bug 3496762
2014:
2015: G_Stage := 'P_Comment_Or_Dff Is C: Update exp comment table.';
2016: If G_Debug_Mode = 'Y' Then
2017: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2018: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2019: End If;
2020:
2021: Update Pa_Expenditure_Comments
2033: ElsIf l_Comment_Count > 0 And P_Timecard_Rec.Expenditure_Item_Comment Is Null Then
2034:
2035: G_Stage := 'P_Comment_Or_Dff Is B: Remove exp comment from table since comment has been updated to null by the user.';
2036: If G_Debug_Mode = 'Y' Then
2037: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2038: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2039: End If;
2040:
2041: Delete From Pa_Expenditure_Comments
2047: ElsIf P_Comment_Or_Dff = 'D' Then
2048:
2049: G_Stage := 'P_Comment_Or_Dff Is D: Update ei table.';
2050: If G_Debug_Mode = 'Y' Then
2051: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2052: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2053: End If;
2054:
2055: Update Pa_Expenditure_Items_All
2083: If l_Comment_Count = 0 Then
2084:
2085: G_Stage := 'P_Comment_Or_Dff Is B: Insert record into exp comment table.';
2086: If G_Debug_Mode = 'Y' Then
2087: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2088: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2089: End If;
2090:
2091: Insert into Pa_Expenditure_Comments
2118: ElsIf l_Comment_Count > 0 and P_Timecard_Rec.Expenditure_Item_Comment Is Not Null Then -- Bug 3496762
2119:
2120: G_Stage := 'P_Comment_Or_Dff Is B: Update exp comment table.';
2121: If G_Debug_Mode = 'Y' Then
2122: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2123: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2124: End If;
2125:
2126: Update Pa_Expenditure_Comments
2138: ElsIf l_Comment_Count > 0 and P_Timecard_Rec.Expenditure_Item_Comment Is Null Then
2139:
2140: G_Stage := 'P_Comment_Or_Dff Is B: Remove exp comment from table since comment has been updated to null by the user.';
2141: If G_Debug_Mode = 'Y' Then
2142: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2143: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2144: End If;
2145:
2146: Delete From Pa_Expenditure_Comments
2150: End If;
2151:
2152: G_Stage := 'P_Comment_Or_Dff Is B: Update ei table.';
2153: If G_Debug_Mode = 'Y' Then
2154: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2155: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2156: End If;
2157:
2158: Update Pa_Expenditure_Items_All
2179:
2180: End If; -- P_Comment_Of_Dff is C or D or B
2181:
2182: G_Stage := 'Leaving UpdateChangedOrigTxn(), strip procedure from trackpath.';
2183: Pa_Otc_Api.TrackPath('STRIP','UpdateChangedOrigTxn');
2184: If G_Debug_Mode = 'Y' Then
2185: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2186: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2187: End If;
2181:
2182: G_Stage := 'Leaving UpdateChangedOrigTxn(), strip procedure from trackpath.';
2183: Pa_Otc_Api.TrackPath('STRIP','UpdateChangedOrigTxn');
2184: If G_Debug_Mode = 'Y' Then
2185: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2186: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2187: End If;
2188:
2189: Exception
2240: Where
2241: Interface_Id = P_Interface_Id
2242: And Transaction_Source = 'ORACLE TIME AND LABOR'
2243: And Transaction_Status_Code in ('I','R')
2244: And Pa_Otc_Api.TrxInCurrentChunk(To_Number(Substr(Orig_Transaction_Reference,1,Instr(Orig_Transaction_Reference,':') - 1))) = 'Y'
2245: Order by 7,3; -- Bug 3355510
2246:
2247: TrxRecord TrxRecords%ROWTYPE;
2248: l_Detail_Index Binary_Integer := Null;
2253: G_Path := ' ';
2254:
2255: G_Stage := 'Entering Tieback_Otc_Timecards().';
2256: If G_Debug_Mode = 'Y' Then
2257: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2258: Pa_CC_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
2259: End If;
2260: Pa_Otc_Api.TrackPath('ADD','Tieback_Otc_Timecards');
2261:
2256: If G_Debug_Mode = 'Y' Then
2257: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2258: Pa_CC_Utils.Log_Message(Pa_Debug.G_Err_Stage,0);
2259: End If;
2260: Pa_Otc_Api.TrackPath('ADD','Tieback_Otc_Timecards');
2261:
2262: G_Stage := 'Open cursor TrxRecords.';
2263: If G_Debug_Mode = 'Y' Then
2264: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2260: Pa_Otc_Api.TrackPath('ADD','Tieback_Otc_Timecards');
2261:
2262: G_Stage := 'Open cursor TrxRecords.';
2263: If G_Debug_Mode = 'Y' Then
2264: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2265: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2266: End If;
2267:
2268: Open TrxRecords(P_Interface_Id => P_Xface_Id);
2271: Loop
2272:
2273: G_Stage := 'Fetch record from cursor TrxRecs.';
2274: If G_Debug_Mode = 'Y' Then
2275: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2276: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2277: End If;
2278:
2279: Fetch TrxRecords Into TrxRecord;
2283:
2284: G_Stage := 'Update Detail Status to SUCCESS for BB_Id: ' || to_char(TrxRecord.Detail_BB_Id) ||
2285: ' Index position is: ' || to_char((G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index));
2286: If G_Debug_Mode = 'Y' Then
2287: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2288: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2289: End If;
2290: Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index) :=
2291: 'SUCCESS';
2294:
2295: G_Stage := 'Update Detail Status to ERRORS for BB_Id: ' || to_char(TrxRecord.Detail_BB_Id) ||
2296: ' Index position is: ' || to_char((G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index));
2297: If G_Debug_Mode = 'Y' Then
2298: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2299: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2300: End If;
2301:
2302: /* Since we now have the looping functionality in Trx Import and we can't restrict the cursor
2329: ' Exception: ' || TrxRecord.Transaction_Rejection_Code ||
2330: ' Resource_Id(Person_Id): ' || TrxRecord.Person_Id ||
2331: ' Exp_Id(Null when Errors): ' || to_char(TrxRecord.Expenditure_Id) ||
2332: ' Ei_Id(Null when Errors): ' || to_char(TrxRecord.Expenditure_Item_Id);
2333: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Stage;
2334: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
2335:
2336: End Loop;
2337:
2336: End Loop;
2337:
2338: G_Stage := 'Loop is done so lose cursor TrxRecs.';
2339: If G_Debug_Mode = 'Y' Then
2340: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2341: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2342: End If;
2343:
2344: Close TrxRecords;
2344: Close TrxRecords;
2345:
2346: G_Stage := 'Update the Transaction_Status_Code for successful transactions in interface table.';
2347: If G_Debug_Mode = 'Y' Then
2348: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2349: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2350: End If;
2351:
2352: Update Pa_Transaction_Interface
2357: And Transaction_Status_Code = 'I';
2358:
2359: G_Stage := 'Loop thru and flag as success those building blocks where we directly updated the eis.';
2360: If G_Debug_Mode = 'Y' Then
2361: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2362: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2363: End If;
2364:
2365: If G_Trx_Direct_Upd_Tab.COUNT > 0 Then
2383:
2384: G_Stage := 'Update Detail Status to SUCCESS for direct updated ei BB_Id: ' || to_char(j) ||
2385: ' Index position is: ' || to_char((G_Trx_Direct_Upd_Tab(j).BB_Index));
2386: If G_Debug_Mode = 'Y' Then
2387: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2388: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2389: End If;
2390:
2391: Hxc_Generic_Retrieval_Pkg.T_Tx_Detail_Status(G_Trx_Direct_Upd_Tab(j).BB_Index) := 'SUCCESS';
2396: End If;
2397:
2398: G_Stage := 'Call OTL API to update transactions';
2399: If G_Debug_Mode = 'Y' Then
2400: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2401: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2402: End If;
2403:
2404: Hxc_Integration_Layer_V1_Grp.Set_Parent_Statuses;
2408: P_Exception_Description => NULL);
2409:
2410: G_Stage := 'Set the process import looping flag to true since completed at least loop thru.';
2411: If G_Debug_Mode = 'Y' Then
2412: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2413: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2414: End If;
2415: -- G_Processed_Import_Batch effects exception handling in the Upload_Otc_Timecard() procedure.
2416: -- When G_Processed_Import_Batch is TRUE then the exception handler in Upload_Otc_Timecard()
2419: G_Processed_Import_Batch := TRUE;
2420:
2421: G_Stage := 'Leaving Tieback_Otc_Timecards(), strip procedure from trackpath.';
2422: If G_Debug_Mode = 'Y' Then
2423: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2424: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
2425: End If;
2426: Pa_Otc_Api.TrackPath('STRIP','Tieback_Otc_Timecards');
2427:
2422: If G_Debug_Mode = 'Y' Then
2423: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2424: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
2425: End If;
2426: Pa_Otc_Api.TrackPath('STRIP','Tieback_Otc_Timecards');
2427:
2428: Exception
2429: When Others Then
2430: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM;
2426: Pa_Otc_Api.TrackPath('STRIP','Tieback_Otc_Timecards');
2427:
2428: Exception
2429: When Others Then
2430: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM;
2431: Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status (
2432: P_Process => 'Projects Retrieval Process',
2433: P_Status => 'ERRORS',
2434: P_Exception_Description => Pa_Debug.G_err_Stage);
2449: -- : nothing else then the item should be directly updated.
2450: --
2451: -- Parameters :
2452: -- IN
2453: -- P_New_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
2454: -- P_Old_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
2455: -- OUT
2456: -- P_Direct_Update_Flag - Boolean
2457: -- P_comment_or_dff - Varchar2
2450: --
2451: -- Parameters :
2452: -- IN
2453: -- P_New_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
2454: -- P_Old_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
2455: -- OUT
2456: -- P_Direct_Update_Flag - Boolean
2457: -- P_comment_or_dff - Varchar2
2458: -- B - Both comment and DFFs
2461: --
2462: /*--------------------------------------------------------------------------*/
2463:
2464: Procedure DetermineDirectUpdate(
2465: P_New_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
2466: P_Old_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
2467: P_Direct_Update_Flag OUT NOCOPY Boolean,
2468: P_Comment_Or_Dff OUT NOCOPY Varchar2) IS
2469:
2462: /*--------------------------------------------------------------------------*/
2463:
2464: Procedure DetermineDirectUpdate(
2465: P_New_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
2466: P_Old_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
2467: P_Direct_Update_Flag OUT NOCOPY Boolean,
2468: P_Comment_Or_Dff OUT NOCOPY Varchar2) IS
2469:
2470: l_Others_Changed Boolean := False;
2474: Begin
2475:
2476: G_Stage := 'Entering DetermineDirectUpdate(), add procedure to trackpath.';
2477: If G_Debug_Mode = 'Y' Then
2478: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2479: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2480: End If;
2481: Pa_Otc_Api.TrackPath('ADD','DetermineDirectUpdate');
2482:
2477: If G_Debug_Mode = 'Y' Then
2478: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2479: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2480: End If;
2481: Pa_Otc_Api.TrackPath('ADD','DetermineDirectUpdate');
2482:
2483: If P_New_Timecard_Rec.Project_Id <> P_Old_Timecard_Rec.Project_Id OR -- bug 3241052
2484: P_New_Timecard_Rec.Task_Id <> P_Old_Timecard_Rec.Task_Id OR -- changed from number to id
2485: P_New_Timecard_Rec.Expenditure_Type <> P_Old_Timecard_Rec.Expenditure_Type OR
2491: Nvl(P_New_Timecard_Rec.PO_Price_Type,'-99999') <> Nvl(P_Old_Timecard_Rec.PO_Price_Type,'-99999') Then
2492:
2493: G_Stage := 'Detail has changed.';
2494: If G_Debug_Mode = 'Y' Then
2495: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2496: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2497: End If;
2498:
2499: l_Others_Changed := True;
2504: nvl(P_Old_Timecard_Rec.Expenditure_Item_Comment,'-9999999999') Then
2505:
2506: G_Stage := 'Comment has changed.';
2507: If G_Debug_Mode = 'Y' Then
2508: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2509: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2510: End If;
2511:
2512: l_Comment_Changed := True;
2527: nvl(P_New_Timecard_Rec.Attribute10,'-9999999999') <> nvl(P_Old_Timecard_Rec.Attribute10,'-9999999999') Then
2528:
2529: G_Stage := 'DFFs have changed.';
2530: If G_Debug_Mode = 'Y' Then
2531: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2532: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2533: End If;
2534:
2535: l_DFFs_Changed := True;
2541: If l_Others_Changed Then
2542:
2543: G_Stage := 'No Direct Update.';
2544: If G_Debug_Mode = 'Y' Then
2545: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2546: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2547: End If;
2548: /* No direct update since other columns have been updated besides
2549: * the comments and DFFs.
2558: */
2559:
2560: G_Stage := 'Direct Update.';
2561: If G_Debug_Mode = 'Y' Then
2562: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2563: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2564: End If;
2565:
2566: P_Direct_Update_Flag := TRUE;
2568: If l_Comment_Changed And l_DFFs_Changed Then
2569:
2570: G_Stage := 'Direct Update - Both.';
2571: If G_Debug_Mode = 'Y' Then
2572: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2573: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2574: End If;
2575: /* Update both the comment in the comment table and
2576: * the DFFs in the ei table.
2580: ElsIf l_Comment_Changed Then
2581:
2582: G_Stage := 'Direct Update - Comment only.';
2583: If G_Debug_Mode = 'Y' Then
2584: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2585: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2586: End If;
2587: /* Only need to update the comment in the comment table */
2588: P_Comment_Or_Dff := 'C';
2590: Else
2591:
2592: G_Stage := 'Direct Update - DFFs only.';
2593: If G_Debug_Mode = 'Y' Then
2594: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2595: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2596: End If;
2597: /* Only need to update the DFFs in the ei table. */
2598: P_Comment_Or_Dff := 'D';
2602: End If;
2603:
2604: G_Stage := 'Leaving DetermineDirectUpdate(), strip procedure from trackpath.';
2605: If G_Debug_Mode = 'Y' Then
2606: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2607: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2608: End If;
2609: Pa_Otc_Api.TrackPath('STRIP','DetermineDirectUpdate');
2610:
2605: If G_Debug_Mode = 'Y' Then
2606: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2607: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2608: End If;
2609: Pa_Otc_Api.TrackPath('STRIP','DetermineDirectUpdate');
2610:
2611: Exception
2612: When Others Then
2613: Raise;
2633: -- P_BB_Id - Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE
2634: -- P_Detail_Index - Binary_Integer
2635: -- P_Old_Detl_Ind - Binary_Integer
2636: -- OUT
2637: -- P_Timecard_Rec - Pa_Otc_Api.Timecard_Rec
2638: --
2639: /*--------------------------------------------------------------------------*/
2640:
2641: Procedure PopulateProjRec(
2642: P_New_Old_BB IN Varchar2,
2643: P_BB_Id IN Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE,
2644: P_Detail_Index IN Binary_Integer,
2645: P_Old_Detl_Ind IN Binary_Integer,
2646: P_Timecard_Rec OUT NOCOPY Pa_Otc_Api.Timecard_Rec) IS -- 2672653
2647:
2648: l_attribute_category Varchar2(100) := NULL;
2649: i Binary_Integer;
2650: j Binary_Integer;
2656: Begin
2657:
2658: G_Stage := 'Entering PopulateProjRec(), add procedure to trackpath.';
2659: If G_Debug_Mode = 'Y' Then
2660: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2661: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2662: End If;
2663: Pa_Otc_Api.TrackPath('ADD','PopulateProjRec');
2664:
2659: If G_Debug_Mode = 'Y' Then
2660: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2661: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2662: End If;
2663: Pa_Otc_Api.TrackPath('ADD','PopulateProjRec');
2664:
2665: G_Stage := 'Set Timecard Rec to NULL.';
2666: If G_Debug_Mode = 'Y' Then
2667: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2663: Pa_Otc_Api.TrackPath('ADD','PopulateProjRec');
2664:
2665: G_Stage := 'Set Timecard Rec to NULL.';
2666: If G_Debug_Mode = 'Y' Then
2667: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2668: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2669: End If;
2670:
2671: P_Timecard_Rec := Null;
2671: P_Timecard_Rec := Null;
2672:
2673: G_Stage := 'The current Detail Building Block Id being processed is: ' || to_char(P_BB_Id);
2674: If G_Debug_Mode = 'Y' Then
2675: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2676: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2677: End If;
2678:
2679: G_Stage := 'Checking if processsing new or old Building Block.';
2677: End If;
2678:
2679: G_Stage := 'Checking if processsing new or old Building Block.';
2680: If G_Debug_Mode = 'Y' Then
2681: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2682: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2683: End If;
2684:
2685: If P_New_Old_BB = 'NEW' Then
2686:
2687: G_Stage := 'Get New Detail Building Block data. Inc by person id(Resource Id): ' ||
2688: to_char(Hxc_Generic_Retrieval_Pkg.T_Detail_Bld_Blks(P_Detail_Index).Resource_Id);
2689: If G_Debug_Mode = 'Y' Then
2690: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2691: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2692: End If;
2693:
2694: P_Timecard_Rec.Incurred_By_Person_Id := Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Resource_Id;
2695:
2696: G_Stage := 'Get New Detail Building Block data. Quantity: ' ||
2697: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Measure);
2698: If G_Debug_Mode = 'Y' Then
2699: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2700: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2701: End If;
2702:
2703: P_Timecard_Rec.Quantity := Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Measure;
2706: G_Stage := 'Get New Detail Building Block data. Expenditure Item Comment: ' ||
2707: Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Comment_Text ||
2708: '(End of comment)';
2709: If G_Debug_Mode = 'Y' Then
2710: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2711: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2712: End If;
2713:
2714: G_Stage := 'Assigning comment to local variable.';
2712: End If;
2713:
2714: G_Stage := 'Assigning comment to local variable.';
2715: If G_Debug_Mode = 'Y' Then
2716: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2717: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2718: End If;
2719: l_comment_text := Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Comment_Text;
2720:
2719: l_comment_text := Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Comment_Text;
2720:
2721: G_Stage := 'Checking if comment is not null.';
2722: If G_Debug_Mode = 'Y' Then
2723: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2724: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2725: End If;
2726:
2727: If l_comment_text is not Null Then
2727: If l_comment_text is not Null Then
2728:
2729: G_Stage := 'The comment is not null so determine the length of comment.';
2730: If G_Debug_Mode = 'Y' Then
2731: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2732: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2733: End If;
2734:
2735: /* Bug 2930551 If the length of the comment_text is greater than 240 then we should only
2740: If lengthb(l_comment_text) > 240 Then
2741:
2742: If G_Debug_Mode = 'Y' Then
2743: G_Stage := 'Comment_Text length > 240.';
2744: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2745: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2746: End If;
2747:
2748: -- P_Timecard_Rec.Expenditure_Item_Comment :=
2755: Else
2756:
2757: If G_Debug_Mode = 'Y' Then
2758: G_Stage := 'Comment_Text length <= 240.';
2759: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2760: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2761: End If;
2762:
2763: -- P_Timecard_Rec.Expenditure_Item_Comment :=
2769: Else -- l_comment_text is Null
2770:
2771: If G_Debug_Mode = 'Y' Then
2772: G_Stage := 'Setting the comment to null.';
2773: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2774: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2775: End If;
2776: P_Timecard_Rec.Expenditure_Item_Comment := Null;
2777:
2779: /* End bug 5412033 */
2780:
2781: G_Stage := 'Get New Detail Building Block data. Exp Item Date, first attempt.';
2782: If G_Debug_Mode = 'Y' Then
2783: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2784: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2785: End If;
2786:
2787: P_Timecard_Rec.Expenditure_Item_Date :=
2790: If P_Timecard_Rec.Expenditure_Item_Date Is Null Then
2791:
2792: G_Stage := 'Get New Detail Building Block data. Exp Item Date, second attempt.';
2793: If G_Debug_Mode = 'Y' Then
2794: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2795: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2796: End If;
2797:
2798: i := 1;
2798: i := 1;
2799:
2800: G_Stage := 'Get New Detail Building Block data. Exp Item Date, via day scope building block.';
2801: If G_Debug_Mode = 'Y' Then
2802: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2803: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2804: End If;
2805:
2806: While i <= Hxc_User_Type_Definition_Grp.T_Day_Bld_Blks.LAST and NOT l_Rec_Found
2808:
2809: G_Stage := 'Check if the current record is the correct DAY ' ||
2810: 'record to grab Exp Item Date from.';
2811: If G_Debug_Mode = 'Y' Then
2812: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2813: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2814: End If;
2815:
2816: If Hxc_User_Type_Definition_Grp.T_Day_Bld_Blks(i).BB_Id =
2817: Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(P_Detail_Index).Parent_BB_Id Then
2818:
2819: G_Stage := 'Grab the Exp Item Date from new day building block record.';
2820: If G_Debug_Mode = 'Y' Then
2821: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2822: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2823: End If;
2824:
2825: P_Timecard_Rec.Expenditure_Item_Date := Trunc(Hxc_User_Type_Definition_Grp.T_Day_Bld_Blks(i).Start_Time);
2834: End If; -- P_Timecard_Rec.Expenditure_Item_Date Is Null
2835:
2836: G_Stage := 'Exp Item Date(Detail Building Block Start Time): ' || to_char(P_Timecard_Rec.Expenditure_Item_Date);
2837: If G_Debug_Mode = 'Y' Then
2838: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2839: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2840: End If;
2841:
2842: G_Stage := 'Get Expenditure Ending Date from new building block data.';
2840: End If;
2841:
2842: G_Stage := 'Get Expenditure Ending Date from new building block data.';
2843: If G_Debug_Mode = 'Y' Then
2844: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2845: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2846: End If;
2847:
2848: If P_Timecard_Rec.Expenditure_Item_Date is not null Then
2855: -- Begin PA.M/CWK changes
2856: -- The person type can be returned as null and will be handled later in the code.
2857: G_Stage := 'Get Person_Type New.';
2858: If G_Debug_Mode = 'Y' Then
2859: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2860: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2861: End If;
2862:
2863: P_Timecard_Rec.Person_Type := GetPersonType(P_Person_Id => P_Timecard_Rec.Incurred_By_Person_Id,
2864: P_Ei_Date => P_Timecard_Rec.Expenditure_Item_Date);
2865:
2866: G_Stage := 'New Person_Type: ' || P_Timecard_Rec.Person_Type;
2867: If G_Debug_Mode = 'Y' Then
2868: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2869: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2870: End If;
2871: -- End PA.M/CWK changes
2872:
2872:
2873: /* Pull data from the new detail_attributes pl/sql table */
2874: G_Stage := 'Get NEW detail bb attribution using for loop.';
2875: If G_Debug_Mode = 'Y' Then
2876: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2877: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2878: End If;
2879:
2880: If G_Detail_Attr_Index = 0 Then
2886: i := G_Detail_Attr_Index;
2887:
2888: G_Stage := 'Looping thru NEW attibution starting at index position: ' || to_char(i) ;
2889: If G_Debug_Mode = 'Y' Then
2890: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2891: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2892: End If;
2893:
2894: G_Stage := 'Current position in NEW attribute pl/sql table bb_id value is: ' ||
2893:
2894: G_Stage := 'Current position in NEW attribute pl/sql table bb_id value is: ' ||
2895: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).BB_Id);
2896: If G_Debug_Mode = 'Y' Then
2897: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2898: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2899: End If;
2900:
2901: While i <= Hxc_User_Type_Definition_Grp.T_Detail_Attributes.LAST And
2906:
2907: G_Stage := 'Retrieved Project Id: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2908: ' Index position: ' || to_char(i);
2909: If G_Debug_Mode = 'Y' Then
2910: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2911: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2912: End If;
2913:
2914: P_Timecard_Rec.Project_Id := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2917:
2918: G_Stage := 'Retrieved Task Id: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2919: ' Index position: ' || to_char(i);
2920: If G_Debug_Mode = 'Y' Then
2921: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2922: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2923: End If;
2924:
2925: P_Timecard_Rec.Task_Id := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2928:
2929: G_Stage := 'Retrieved Expenditure Type: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2930: ' Index position: ' || to_char(i);
2931: If G_Debug_Mode = 'Y' Then
2932: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2933: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2934: End If;
2935:
2936: P_Timecard_Rec.Expenditure_Type := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2940: G_Stage := 'Retrieve System Linkage Function: ' ||
2941: Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2942: ' Index position: ' || to_char(i);
2943: If G_Debug_Mode = 'Y' Then
2944: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2945: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2946: End If;
2947:
2948: P_Timecard_Rec.System_Linkage_Function := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2952: G_Stage := 'Retrieve Overidding Approver Person Id: ' ||
2953: Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2954: ' Index position: ' || to_char(i);
2955: If G_Debug_Mode = 'Y' Then
2956: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2957: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2958: End If;
2959:
2960: P_Timecard_Rec.Override_Approver_Person_Id := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2969:
2970: G_Stage := 'Get new attribute category: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
2971: ' Index position: ' || to_char(i);
2972: If G_Debug_Mode = 'Y' Then
2973: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2974: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2975: End If;
2976:
2977: l_attribute_category := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
2981: */
2982:
2983: G_Stage := 'Get new attribute category, checking if Null.';
2984: If G_Debug_Mode = 'Y' Then
2985: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2986: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2987: End If;
2988:
2989: If l_attribute_category is not null Then
2989: If l_attribute_category is not null Then
2990:
2991: G_Stage := 'Get new attribute category, Strip out prefix.';
2992: If G_Debug_Mode = 'Y' Then
2993: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
2994: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
2995: End If;
2996:
2997: P_Timecard_Rec.Attribute_Category := substr(l_attribute_category,instr(l_attribute_category,' - ') + 3);
3002:
3003: G_Stage := 'Got new attribute1: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3004: ' Index position: ' || to_char(i);
3005: If G_Debug_Mode = 'Y' Then
3006: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3007: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3008: End If;
3009:
3010: P_Timecard_Rec.Attribute1 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3013:
3014: G_Stage := 'Got new attribute2: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3015: ' Index position: ' || to_char(i);
3016: If G_Debug_Mode = 'Y' Then
3017: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3018: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3019: End If;
3020:
3021: P_Timecard_Rec.Attribute2 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3024:
3025: G_Stage := 'Got new attribute3: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3026: ' Index position: ' || to_char(i);
3027: If G_Debug_Mode = 'Y' Then
3028: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3029: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3030: End If;
3031:
3032: P_Timecard_Rec.Attribute3 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3035:
3036: G_Stage := 'Got new attribute4: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3037: ' Index position: ' || to_char(i);
3038: If G_Debug_Mode = 'Y' Then
3039: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3040: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3041: End If;
3042:
3043: P_Timecard_Rec.Attribute4 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3046:
3047: G_Stage := 'Got new attribute5: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3048: ' Index position: ' || to_char(i);
3049: If G_Debug_Mode = 'Y' Then
3050: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3051: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3052: End If;
3053:
3054: P_Timecard_Rec.Attribute5 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3057:
3058: G_Stage := 'Got new attribute6: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3059: ' Index position: ' || to_char(i);
3060: If G_Debug_Mode = 'Y' Then
3061: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3062: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3063: End If;
3064:
3065: P_Timecard_Rec.Attribute6 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3068:
3069: G_Stage := 'Got new attribute7: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3070: ' Index position: ' || to_char(i);
3071: If G_Debug_Mode = 'Y' Then
3072: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3073: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3074: End If;
3075:
3076: P_Timecard_Rec.Attribute7 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3079:
3080: G_Stage := 'Got new attribute8: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3081: ' Index position: ' || to_char(i);
3082: If G_Debug_Mode = 'Y' Then
3083: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3084: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3085: End If;
3086:
3087: P_Timecard_Rec.Attribute8 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3090:
3091: G_Stage := 'Got new attribute9: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3092: ' Index position: ' || to_char(i);
3093: If G_Debug_Mode = 'Y' Then
3094: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3095: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3096: End If;
3097:
3098: P_Timecard_Rec.Attribute9 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3101:
3102: G_Stage := 'Got new attribute10: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3103: ' Index position: ' || to_char(i);
3104: If G_Debug_Mode = 'Y' Then
3105: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3106: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3107: End If;
3108:
3109: P_Timecard_Rec.Attribute10 := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3112:
3113: G_Stage := 'Got new Billable Flag: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3114: ' Index position: ' || to_char(i);
3115: If G_Debug_Mode = 'Y' Then
3116: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3117: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3118: End If;
3119:
3120: P_Timecard_Rec.Billable_Flag := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3124:
3125: G_Stage := 'Retrieved Po Line Id: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3126: ' Index position: ' || to_char(i);
3127: If G_Debug_Mode = 'Y' Then
3128: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3129: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3130: End If;
3131:
3132: P_Timecard_Rec.PO_Line_Id := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3135:
3136: G_Stage := 'Retrieved Po Price Type: ' || Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value ||
3137: ' Index position: ' || to_char(i);
3138: If G_Debug_Mode = 'Y' Then
3139: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3140: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3141: End If;
3142:
3143: P_Timecard_Rec.PO_Price_Type := Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).Value;
3151:
3152: G_Stage := 'Exited loop. No more NEW attribution for BB_Id. Store the current index position ' ||
3153: to_char(i) || ' in global variable.';
3154: If G_Debug_Mode = 'Y' Then
3155: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3156: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3157: End If;
3158:
3159: /* Note that variable G_Detail_Attr_Index is already pointing the the next building block.
3164: G_Detail_Attr_Index := i;
3165:
3166: G_Stage := 'Determine why exited loop looking for NEW record attribution.';
3167: If G_Debug_Mode = 'Y' Then
3168: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3169: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3170: End If;
3171:
3172: If i > Hxc_User_Type_Definition_Grp.T_Detail_Attributes.LAST Then
3174: G_Stage := 'Reached last record index position ' ||
3175: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Attributes.LAST) || ' ' ||
3176: 'in NEW attrib pl/sql table.';
3177: If G_Debug_Mode = 'Y' Then
3178: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3179: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3180: End If;
3181:
3182: ElsIf Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).BB_Id <> P_BB_Id Then
3185: 'BB_Id in NEW attrib pl/sql: ' ||
3186: to_char(Hxc_User_Type_Definition_Grp.T_Detail_Attributes(i).BB_Id) || '. ' ||
3187: 'They do not match!';
3188: If G_Debug_Mode = 'Y' Then
3189: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3190: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3191: End If;
3192:
3193: End If;
3194:
3195: -- Begin PA.M/CWK changes
3196: G_Stage := 'Check Person Type is CWK and PO_Line_Id is Not Null New.';
3197: If G_Debug_Mode = 'Y' Then
3198: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3199: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3200: End If;
3201:
3202: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id is Not Null Then
3200: End If;
3201:
3202: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id is Not Null Then
3203:
3204: G_Stage := 'Calling Pa_Otc_Api.GetPOInfo() procedure New.';
3205: If G_Debug_Mode = 'Y' Then
3206: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3207: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3208: End If;
3202: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id is Not Null Then
3203:
3204: G_Stage := 'Calling Pa_Otc_Api.GetPOInfo() procedure New.';
3205: If G_Debug_Mode = 'Y' Then
3206: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3207: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3208: End If;
3209:
3210: Pa_Otc_Api.GetPOInfo(
3206: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3207: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3208: End If;
3209:
3210: Pa_Otc_Api.GetPOInfo(
3211: P_Po_Line_Id => P_Timecard_Rec.PO_Line_Id,
3212: X_PO_Header_Id => P_Timecard_Rec.PO_Header_Id,
3213: X_Vendor_Id => P_Timecard_Rec.Vendor_Id);
3214:
3213: X_Vendor_Id => P_Timecard_Rec.Vendor_Id);
3214:
3215: G_Stage := 'Got new Vendor Id: ' || to_char(P_Timecard_Rec.Vendor_Id) || ' based on PO_Line_Id';
3216: If G_Debug_Mode = 'Y' Then
3217: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3218: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3219: End If;
3220:
3221: Else
3221: Else
3222:
3223: G_Stage := 'Set to null po info, vendor_id, and price_type New.';
3224: If G_Debug_Mode = 'Y' Then
3225: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3226: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3227: End If;
3228: P_Timecard_Rec.PO_Line_Id := NULL;
3229: P_Timecard_Rec.PO_Header_Id := NULL;
3237:
3238: G_Stage := 'Got Old Detail Building Block data. Incurred by Person Id(Resource Id): ' ||
3239: to_char(Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Resource_Id);
3240: If G_Debug_Mode = 'Y' Then
3241: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3242: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3243: End If;
3244:
3245: P_Timecard_Rec.Incurred_By_Person_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Resource_Id;
3245: P_Timecard_Rec.Incurred_By_Person_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Resource_Id;
3246:
3247: G_Stage := 'Got Old Detail Building Block data. Incurred by Person Id';
3248: If G_Debug_Mode = 'Y' Then
3249: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3250: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3251: End If;
3252:
3253: G_Stage := 'Got Old Detail Building Block data. Quantity: ' ||
3252:
3253: G_Stage := 'Got Old Detail Building Block data. Quantity: ' ||
3254: to_char(Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Measure);
3255: If G_Debug_Mode = 'Y' Then
3256: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3257: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3258: End If;
3259:
3260: P_Timecard_Rec.Quantity := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Measure;
3263: G_Stage := 'Get Old Detail Building Block data. Expenditure Item Comment: ' ||
3264: Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Comment_Text ||
3265: '(End of comment)';
3266: If G_Debug_Mode = 'Y' Then
3267: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3268: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3269: End If;
3270:
3271: G_Stage := 'Assigning Old comment to local variable.';
3269: End If;
3270:
3271: G_Stage := 'Assigning Old comment to local variable.';
3272: If G_Debug_Mode = 'Y' Then
3273: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3274: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3275: End If;
3276: l_comment_text := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Comment_Text;
3277:
3276: l_comment_text := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Comment_Text;
3277:
3278: G_Stage := 'Checking if Old comment is not null.';
3279: If G_Debug_Mode = 'Y' Then
3280: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3281: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3282: End If;
3283:
3284: If l_comment_text is not Null Then
3284: If l_comment_text is not Null Then
3285:
3286: G_Stage := 'The old comment is not null so determine the length of comment.';
3287: If G_Debug_Mode = 'Y' Then
3288: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3289: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3290: End If;
3291:
3292: /* Bug 2930551 If the length of the comment_text is greater than 240 then we should only
3297: If lengthb(l_comment_text) > 240 Then
3298:
3299: If G_Debug_Mode = 'Y' Then
3300: G_Stage := 'Old Comment_Text length > 240.';
3301: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3302: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3303: End If;
3304:
3305: -- P_Timecard_Rec.Expenditure_Item_Comment :=
3312: Else
3313:
3314: If G_Debug_Mode = 'Y' Then
3315: G_Stage := 'Old Comment_Text length <= 240.';
3316: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3317: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3318: End If;
3319:
3320: -- P_Timecard_Rec.Expenditure_Item_Comment :=
3326: Else -- l_comment_text is null
3327:
3328: If G_Debug_Mode = 'Y' Then
3329: G_Stage := 'Setting the Old comment to null.';
3330: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3331: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3332: End If;
3333: P_Timecard_Rec.Expenditure_Item_Comment := Null;
3334:
3336: /* End Bug 5412033 */
3337:
3338: G_Stage := 'Get Old Day Building Block data. Exp Item Date. First Attempt.';
3339: If G_Debug_Mode = 'Y' Then
3340: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3341: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3342: End If;
3343:
3344: P_Timecard_Rec.Expenditure_Item_Date := Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Start_Time;
3346: If P_Timecard_Rec.Expenditure_Item_Date Is Null Then
3347:
3348: G_Stage := 'Get Old Day Building Block data. Exp Item Date. Second Attempt.';
3349: If G_Debug_Mode = 'Y' Then
3350: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3351: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3352: End If;
3353:
3354: j := 1;
3354: j := 1;
3355:
3356: G_Stage := 'Get Old Detail Building Block data. Exp Item Date, via old day scope building block.';
3357: If G_Debug_Mode = 'Y' Then
3358: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3359: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3360: End If;
3361:
3362: While j <= Hxc_User_Type_Definition_Grp.T_Old_Day_Bld_Blks.LAST and NOT l_Rec_Found
3364:
3365: G_Stage := 'Check if the current record is the correct DAY ' ||
3366: 'record to grab Exp Item Date from.';
3367: If G_Debug_Mode = 'Y' Then
3368: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3369: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3370: End If;
3371:
3372: If Hxc_User_Type_Definition_Grp.T_Day_Bld_Blks(j).BB_Id =
3373: Hxc_User_Type_Definition_Grp.T_Old_Detail_Bld_Blks(P_Old_Detl_Ind).Parent_BB_Id Then
3374:
3375: G_Stage := 'Grab the Exp Item Date from old day building block record.';
3376: If G_Debug_Mode = 'Y' Then
3377: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3378: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3379: End If;
3380:
3381: P_Timecard_Rec.Expenditure_Item_Date := Trunc(Hxc_User_Type_Definition_Grp.T_Old_Day_Bld_Blks(j).Start_Time);
3390: End If;
3391:
3392: G_Stage := 'Exp Item Date(Detail Building Block Start Time): ' || to_char(P_Timecard_Rec.Expenditure_Item_Date);
3393: If G_Debug_Mode = 'Y' Then
3394: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3395: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3396: End If;
3397:
3398: G_Stage := 'Get Expenditure Ending Date for old building block data.';
3396: End If;
3397:
3398: G_Stage := 'Get Expenditure Ending Date for old building block data.';
3399: If G_Debug_Mode = 'Y' Then
3400: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3401: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3402: End If;
3403:
3404: P_Timecard_Rec.Expenditure_Ending_date := Pa_Utils.NewGetWeekEnding(P_Timecard_Rec.Expenditure_Item_Date);
3404: P_Timecard_Rec.Expenditure_Ending_date := Pa_Utils.NewGetWeekEnding(P_Timecard_Rec.Expenditure_Item_Date);
3405:
3406: G_Stage := 'Old Index check and assignment for use in loop';
3407: If G_Debug_Mode = 'Y' Then
3408: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3409: Pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3410: End If;
3411:
3412: -- Begin PA.M/CWK changes
3412: -- Begin PA.M/CWK changes
3413: -- The person type can be returned as null and will be handled later in the code.
3414: G_Stage := 'Get Person_Type old.';
3415: If G_Debug_Mode = 'Y' Then
3416: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3417: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3418: End If;
3419:
3420: P_Timecard_Rec.Person_Type := GetPersonType(P_Person_Id => P_Timecard_Rec.Incurred_By_Person_Id,
3421: P_Ei_Date => P_Timecard_Rec.Expenditure_Item_Date);
3422:
3423: G_Stage := 'Old Person_Type: ' || P_Timecard_Rec.Person_Type;
3424: If G_Debug_Mode = 'Y' Then
3425: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3426: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3427: End If;
3428: -- End PA.M/CWK changes
3429:
3437:
3438: /* Pull data from the detail_attributes pl/sql table */
3439: G_Stage := 'Looping thru OLD attibution starting at index position: ' || to_char(j) ;
3440: If G_Debug_Mode = 'Y' Then
3441: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3442: Pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3443: End If;
3444:
3445: G_Stage := 'Current position in OLD attribute pl/sql table bb_id value: ' ||
3444:
3445: G_Stage := 'Current position in OLD attribute pl/sql table bb_id value: ' ||
3446: to_char(Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).BB_Id);
3447: If G_Debug_Mode = 'Y' Then
3448: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3449: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3450: End If;
3451:
3452: While j <= Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes.LAST and
3457:
3458: G_Stage := 'Got old Project Id: ' || Hxc_Generic_Retrieval_Pkg.T_Old_Detail_Attributes(j).Value ||
3459: ' Index position: ' || to_char(j);
3460: If G_Debug_Mode = 'Y' Then
3461: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3462: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3463: End If;
3464:
3465: P_Timecard_Rec.Project_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3468:
3469: G_Stage := 'Got old Task Id: ' || Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3470: ' Index position: ' || to_char(j);
3471: If G_Debug_Mode = 'Y' Then
3472: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3473: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3474: End If;
3475:
3476: P_Timecard_Rec.Task_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3480: G_Stage := 'Got old Expenditure Type: ' ||
3481: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3482: ' Index position: ' || to_char(j);
3483: If G_Debug_Mode = 'Y' Then
3484: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3485: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3486: End If;
3487:
3488: P_Timecard_Rec.Expenditure_Type := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3492: G_Stage := 'Got old System Linkage Function: ' ||
3493: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3494: ' Index position: ' || to_char(j);
3495: If G_Debug_Mode = 'Y' Then
3496: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3497: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3498: End If;
3499:
3500: P_Timecard_Rec.System_Linkage_Function := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3504: G_Stage := 'Got old Overriding Approver Person Id: ' ||
3505: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3506: ' Index position: ' || to_char(j);
3507: If G_Debug_Mode = 'Y' Then
3508: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3509: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3510: End If;
3511:
3512: P_Timecard_Rec.Override_Approver_Person_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3522: G_Stage := 'Get old attribute category info: ' ||
3523: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3524: ' Index position: ' || to_char(j);
3525: If G_Debug_Mode = 'Y' Then
3526: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3527: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3528: End If;
3529:
3530: l_attribute_category := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3533: * avoiding unhandled exceptions.
3534: */
3535: G_Stage := 'Get old attribute category, checking if Null.'|| to_char(j);
3536: If G_Debug_Mode = 'Y' Then
3537: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3538: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3539: End If;
3540:
3541: If l_attribute_category is not null Then
3541: If l_attribute_category is not null Then
3542:
3543: G_Stage := 'Get old attribute category, Strip out prefix.'|| to_char(j);
3544: If G_Debug_Mode = 'Y' Then
3545: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3546: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3547: End If;
3548:
3549: P_Timecard_Rec.Attribute_Category := substr(l_attribute_category,instr(l_attribute_category,' - ') + 3);
3555: G_Stage := 'Got old Attribute1: ' ||
3556: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3557: ' Index position: ' || to_char(j);
3558: If G_Debug_Mode = 'Y' Then
3559: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3560: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3561: End If;
3562:
3563: P_Timecard_Rec.Attribute1 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3567: G_Stage := 'Got old Attribute2: ' ||
3568: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3569: ' Index position: ' || to_char(j);
3570: If G_Debug_Mode = 'Y' Then
3571: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3572: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3573: End If;
3574:
3575: P_Timecard_Rec.Attribute2 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3579: G_Stage := 'Got old Attribute3: ' ||
3580: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3581: ' Index position: ' || to_char(j);
3582: If G_Debug_Mode = 'Y' Then
3583: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3584: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3585: End If;
3586:
3587: P_Timecard_Rec.Attribute3 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3591: G_Stage := 'Got old Attribute4: ' ||
3592: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3593: ' Index position: ' || to_char(j);
3594: If G_Debug_Mode = 'Y' Then
3595: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3596: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3597: End If;
3598:
3599: P_Timecard_Rec.Attribute4 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3603: G_Stage := 'Got old Attribute5: ' ||
3604: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3605: ' Index position: ' || to_char(j);
3606: If G_Debug_Mode = 'Y' Then
3607: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3608: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3609: End If;
3610:
3611: P_Timecard_Rec.Attribute5 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3615: G_Stage := 'Got old Attribute6: ' ||
3616: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3617: ' Index position: ' || to_char(j);
3618: If G_Debug_Mode = 'Y' Then
3619: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3620: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3621: End If;
3622:
3623: P_Timecard_Rec.Attribute6 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3627: G_Stage := 'Got old Attribute7: ' ||
3628: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3629: ' Index position: ' || to_char(j);
3630: If G_Debug_Mode = 'Y' Then
3631: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3632: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3633: End If;
3634:
3635: P_Timecard_Rec.Attribute7 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3639: G_Stage := 'Got old Attribute8: ' ||
3640: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3641: ' Index position: ' || to_char(j);
3642: If G_Debug_Mode = 'Y' Then
3643: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3644: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3645: End If;
3646:
3647: P_Timecard_Rec.Attribute8 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3651: G_Stage := 'Got old Attribute9: ' ||
3652: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3653: ' Index position: ' || to_char(j);
3654: If G_Debug_Mode = 'Y' Then
3655: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3656: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3657: End If;
3658:
3659: P_Timecard_Rec.Attribute9 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3663: G_Stage := 'Got old Attribute10: ' ||
3664: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3665: ' Index position: ' || to_char(j);
3666: If G_Debug_Mode = 'Y' Then
3667: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3668: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3669: End If;
3670:
3671: P_Timecard_Rec.Attribute10 := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3675: G_Stage := 'Got old Billable Flag: ' ||
3676: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3677: ' Index position: ' || to_char(j);
3678: If G_Debug_Mode = 'Y' Then
3679: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3680: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3681: End If;
3682:
3683: P_Timecard_Rec.Billable_Flag := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3688: G_Stage := 'Got old Po Line Id: ' ||
3689: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3690: ' Index position: ' || to_char(j);
3691: If G_Debug_Mode = 'Y' Then
3692: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3693: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3694: End If;
3695:
3696: P_Timecard_Rec.PO_Line_Id := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3700: G_Stage := 'Got old PO Price Type: ' ||
3701: Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value ||
3702: ' Index position: ' || to_char(j);
3703: If G_Debug_Mode = 'Y' Then
3704: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3705: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3706: End If;
3707:
3708: P_Timecard_Rec.PO_Price_Type := Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).Value;
3716:
3717: G_Stage := 'Exited loop. No more OLD attribution for BB_Id. Store the current index position ' ||
3718: to_char(j) || ' in global variable.';
3719: If G_Debug_Mode = 'Y' Then
3720: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3721: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3722: End If;
3723:
3724: /* Note that variable G_Old_Detail_Attr_Index is already pointing the the next building block.
3729: G_Old_Detail_Attr_Index := j;
3730:
3731: G_Stage := 'Determine why exited loop while looking for OLD record attribution.';
3732: If G_Debug_Mode = 'Y' Then
3733: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3734: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3735: End If;
3736:
3737: If j > Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes.LAST Then
3739: G_Stage := 'Reached last record index position ' ||
3740: to_char(Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes.LAST) || ' ' ||
3741: 'in OLD attrib pl/sql table.';
3742: If G_Debug_Mode = 'Y' Then
3743: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3744: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3745: End If;
3746:
3747: ElsIf Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).BB_Id <> P_BB_Id Then
3750: 'BB_Id in OLD attrib pl/sql: ' ||
3751: to_char(Hxc_User_Type_Definition_Grp.T_Old_Detail_Attributes(j).BB_Id) || '. ' ||
3752: 'They do not match!';
3753: If G_Debug_Mode = 'Y' Then
3754: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3755: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3756: End If;
3757:
3758: End If;
3759:
3760: -- Begin PA.M/CWK changes
3761: G_Stage := 'Check if P_Timecard_Rec.Person_Type is CWK and PO_Line_Id Is Not Null old.';
3762: If G_Debug_Mode = 'Y' Then
3763: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3764: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3765: End If;
3766: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id Is Not Null Then
3767:
3764: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3765: End If;
3766: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id Is Not Null Then
3767:
3768: G_Stage := 'Calling Pa_Otc_Api.GetPOInfo() procedure old.';
3769: If G_Debug_Mode = 'Y' Then
3770: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3771: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3772: End If;
3766: If P_Timecard_Rec.Person_Type = 'CWK' and P_Timecard_Rec.PO_Line_Id Is Not Null Then
3767:
3768: G_Stage := 'Calling Pa_Otc_Api.GetPOInfo() procedure old.';
3769: If G_Debug_Mode = 'Y' Then
3770: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3771: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3772: End If;
3773:
3774: Pa_Otc_Api.GetPOInfo(
3770: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3771: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3772: End If;
3773:
3774: Pa_Otc_Api.GetPOInfo(
3775: P_Po_Line_Id => P_Timecard_Rec.PO_Line_Id,
3776: X_PO_Header_Id => P_Timecard_Rec.PO_Header_Id,
3777: X_Vendor_Id => P_Timecard_Rec.Vendor_Id);
3778:
3777: X_Vendor_Id => P_Timecard_Rec.Vendor_Id);
3778:
3779: G_Stage := 'Got old Vendor Id: ' || to_char(P_Timecard_Rec.Vendor_Id) || ' based on PO_Line_Id';
3780: If G_Debug_Mode = 'Y' Then
3781: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3782: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3783: End If;
3784:
3785: Else
3785: Else
3786:
3787: G_Stage := 'Set po infor, vendor_id and price_type to null old.';
3788: If G_Debug_Mode = 'Y' Then
3789: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3790: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3791: End If;
3792:
3793: P_Timecard_Rec.PO_Line_Id := NULL;
3801: End If;
3802:
3803: G_Stage := 'Checking that needed data was retrieved to properly process building block for import.';
3804: If G_Debug_Mode = 'Y' Then
3805: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3806: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3807: End If;
3808:
3809: If P_Timecard_Rec.Project_Id is null and
3886: End If;
3887:
3888: G_Stage := 'Leaving PopulateProjRec(), strip procedure from trackpath.';
3889: If G_Debug_Mode = 'Y' Then
3890: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3891: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3892: End If;
3893: Pa_Otc_Api.TrackPath('STRIP','PopulateProjRec');
3894:
3889: If G_Debug_Mode = 'Y' Then
3890: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3891: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3892: End If;
3893: Pa_Otc_Api.TrackPath('STRIP','PopulateProjRec');
3894:
3895: Exception
3896: When Others Then
3897: l_error_text := SubStr('Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' -- ' || SqlErrM, 1, 1800);
3893: Pa_Otc_Api.TrackPath('STRIP','PopulateProjRec');
3894:
3895: Exception
3896: When Others Then
3897: l_error_text := SubStr('Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' -- ' || SqlErrM, 1, 1800);
3898: fnd_message.set_name('HXC', 'HXC_RET_UNEXPECTED_ERROR');
3899: fnd_message.set_token('ERR', l_Error_Text);
3900: P_Timecard_Rec.Status := SubStr(Fnd_Message.Get,1,2000);
3901:
3905: End If;
3906:
3907: l_error_text := 'Leaving PopulateProjRec() due to unhandled exception, strip procedure from trackpath.';
3908: If G_Debug_Mode = 'Y' Then
3909: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || l_error_text;
3910: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3911: End If;
3912: Pa_Otc_Api.TrackPath('STRIP','PopulateProjRec');
3913:
3908: If G_Debug_Mode = 'Y' Then
3909: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || l_error_text;
3910: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3911: End If;
3912: Pa_Otc_Api.TrackPath('STRIP','PopulateProjRec');
3913:
3914: Raise G_BAD_OTL_DATA;
3915:
3916: End PopulateProjRec;
3944: Begin
3945:
3946: G_Stage := 'Entering procedure GetDetailIndex(), add procedure to trackpath.';
3947: If G_Debug_Mode = 'Y' Then
3948: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3949: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3950: End If;
3951: Pa_Otc_Api.TrackPath('ADD','GetDetailIndex');
3952:
3947: If G_Debug_Mode = 'Y' Then
3948: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3949: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3950: End If;
3951: Pa_Otc_Api.TrackPath('ADD','GetDetailIndex');
3952:
3953: G_Stage := 'Begin loop searching for the matching BB_Id.';
3954: If G_Debug_Mode = 'Y' Then
3955: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3951: Pa_Otc_Api.TrackPath('ADD','GetDetailIndex');
3952:
3953: G_Stage := 'Begin loop searching for the matching BB_Id.';
3954: If G_Debug_Mode = 'Y' Then
3955: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3956: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3957: End If;
3958:
3959: For i in Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks.FIRST .. Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks.LAST
3960: Loop
3961:
3962: G_Stage := 'Determine if BB_Id in the pl/sql matches the one provided.';
3963: If G_Debug_Mode = 'Y' Then
3964: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3965: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3966: End If;
3967:
3968: If Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id = P_Detail_BB_Id Then
3968: If Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id = P_Detail_BB_Id Then
3969:
3970: G_Stage := 'Set Index to use since found the one needed.';
3971: If G_Debug_Mode = 'Y' Then
3972: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3973: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3974: End If;
3975:
3976: X_Detail_Index := i;
3976: X_Detail_Index := i;
3977:
3978: G_Stage := 'Exiting the loop since found the index needed.';
3979: If G_Debug_Mode = 'Y' Then
3980: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3981: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3982: End If;
3983:
3984: EXIT;
3988: End Loop;
3989:
3990: G_Stage := 'Leaving procedure GetDetailIndex().';
3991: If G_Debug_Mode = 'Y' Then
3992: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3993: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3994: End If;
3995: Pa_Otc_Api.TrackPath('STRIP','GetDetailIndex');
3996:
3991: If G_Debug_Mode = 'Y' Then
3992: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
3993: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
3994: End If;
3995: Pa_Otc_Api.TrackPath('STRIP','GetDetailIndex');
3996:
3997: Exception
3998: When Others Then
3999: Raise;
4065:
4066: G_Path := ' ';
4067:
4068: G_Stage := 'Entering Update_Otc_Data() procedure.';
4069: Pa_Otc_Api.TrackPath('ADD','Update_Otc_Data');
4070:
4071: If P_Operation <> 'MIGRATION' Then
4072:
4073: G_Stage := 'Call Hxc_Integration_Layer_V1_Grp.Get_App_Hook_Params()';
4076: P_App_Attributes => l_Attributes,
4077: P_Messages => l_Messages);
4078:
4079: G_Stage := 'Call the Upate_Process() to update the billable flag.';
4080: Pa_Otc_Api.Update_Process (
4081: P_Operation => P_Operation,
4082: P_Building_Blocks => l_Blocks,
4083: P_Attribute_Table => l_Attributes);
4084:
4090:
4091: End If; -- P_Operation
4092:
4093: G_Stage := 'Leaving Update_Otc_Data() procedure.';
4094: Pa_Otc_Api.TrackPath('STRIP','Update_Otc_Data');
4095:
4096: Exception
4097: When Others Then
4098: Raise_Application_Error(-20010, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SqlErrM );
4094: Pa_Otc_Api.TrackPath('STRIP','Update_Otc_Data');
4095:
4096: Exception
4097: When Others Then
4098: Raise_Application_Error(-20010, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SqlErrM );
4099:
4100: End Update_Otc_Data;
4101:
4102:
4132: P_Attribute_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.App_Attributes_Info) -- 2672653
4133:
4134: Is
4135:
4136: l_Proj_Attrib_Rec Pa_Otc_Api.Project_Attribution_Rec;
4137:
4138: /* Stores a single record from the Building Block Table */
4139: l_Building_Block_Record Hxc_User_Type_Definition_Grp.Building_Block_Info;
4140:
4166:
4167: Begin
4168:
4169: G_Stage := 'Entering procedure Update_Process().';
4170: Pa_Otc_Api.TrackPath('ADD','Update_Process');
4171:
4172: G_Stage := 'Loop thru Building Blocks record to validate detail records.';
4173: -- For i in P_Building_Blocks.First .. P_Building_Blocks.Last
4174: Loop
4189: l_Building_Block_Record := P_Building_Blocks(i);
4190: G_Stage := 'Pull out the data to friendlier formated variables.';
4191: /* Pull out the data to project friendly variables */
4192: /* Bug 4318639
4193: Pa_Otc_Api.RetrieveProjAttribution( */
4194: Pa_Otc_Api.RetrieveProjAttribForUpd(
4195: P_Building_Block_Rec => l_Building_Block_Record,
4196: P_Building_Block => P_Building_Blocks,
4197: P_Attribute_Table => P_Attribute_Table,
4190: G_Stage := 'Pull out the data to friendlier formated variables.';
4191: /* Pull out the data to project friendly variables */
4192: /* Bug 4318639
4193: Pa_Otc_Api.RetrieveProjAttribution( */
4194: Pa_Otc_Api.RetrieveProjAttribForUpd(
4195: P_Building_Block_Rec => l_Building_Block_Record,
4196: P_Building_Block => P_Building_Blocks,
4197: P_Attribute_Table => P_Attribute_Table,
4198: X_Detail_Attr_Changed => l_Detail_Attr_Changed,
4209:
4210: End If;
4211:
4212: G_Stage := 'Determine the processing flags for using further in code.';
4213: Pa_Otc_Api.DetermineProcessingFlags(
4214: P_BB_Id => P_Building_Blocks(i).Time_Building_Block_Id,
4215: P_BB_Ovn => P_Building_Blocks(i).Object_Version_Number,
4216: P_BB_Date_To => P_Building_Blocks(i).Date_To,
4217: P_BB_Changed => l_Logical_Rec_Changed,
4294: -- Populate the Billable Flag in the pl/sql table
4295: -- Bug 4318639 Added IF condition to see if the billable_flag_index is populated.
4296: -- If no project related data was entered or there is no project_id attribution data in the
4297: -- attribution pl/sql table then we are able to build the billable_flag attribution record in
4298: -- Pa_Otc_Api.RetrieveProjAttribForUpd() so won't be able to assign the billable_flag to the
4299: -- attribution record.
4300: G_Stage := 'Check if billable_flag attribution pl/sql record exists.';
4301: If l_Proj_Attrib_Rec.Billable_Flag_Index is Not Null Then
4302: G_Stage := 'Populate the Billable Flag in the attribution pl/sql table record.';
4314:
4315: End Loop; -- End of looping thru the building_block table
4316:
4317: G_Stage := 'Leaving procedure Update_Process().';
4318: Pa_Otc_Api.TrackPath('STRIP','Update_Process');
4319:
4320: Exception
4321: When Others Then
4322: Raise;
4354:
4355: G_Path := ' ';
4356:
4357: G_Stage := 'Entering procedure Validate_Otc_Data().';
4358: Pa_Otc_Api.TrackPath('ADD','Validate_Otc_Data');
4359:
4360: If P_Operation <> 'MIGRATION' Then
4361:
4362: G_Stage := 'Call hxc_self_service_time_deposit.get_app_hook_params()';
4365: P_App_Attributes => l_Attributes,
4366: P_Messages => l_Messages);
4367:
4368: G_Stage := 'Call the Validate_Process() to validate data.';
4369: Pa_Otc_Api.Validate_Process (
4370: P_Operation => P_Operation,
4371: P_Building_Blocks => l_Blocks,
4372: P_Attribute_Table => l_Attributes,
4373: P_Message_Table => l_Messages);
4384:
4385: End If; -- P_Operation
4386:
4387: G_Stage := 'Leaving procedure Validate_Otc_Data().';
4388: Pa_Otc_Api.TrackPath('STRIP','Validate_Otc_Data');
4389:
4390: Exception
4391: When Others Then
4392: Raise_Application_Error(-20020,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
4388: Pa_Otc_Api.TrackPath('STRIP','Validate_Otc_Data');
4389:
4390: Exception
4391: When Others Then
4392: Raise_Application_Error(-20020,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
4393:
4394: End Validate_Otc_Data;
4395:
4396:
4447: l_Ovr_Approver_Person_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE := Null;
4448:
4449: l_Bill_Flag_Meaning Fnd_Lookups.Meaning%TYPE := Null;
4450:
4451: l_Proj_Attrib_Rec Pa_Otc_Api.Project_Attribution_Rec;
4452:
4453: l_Msg_Name VARCHAR2(30) := Null;
4454: l_Msg_Application VARCHAR2(2) := Null;
4455: l_Msg_Type VARCHAR2(30) := Null;
4478: l_strReferenceField Varchar2(200);
4479: l_bresult Boolean;
4480:
4481: /* Variables needed for running of the summary-validation and Business Message APIs */
4482: l_Timecard_Table Pa_Otc_Api.Timecard_Table;
4483: l_Timecard_Table_Week Pa_Otc_Api.Timecard_Table;
4484: l_Weeks_To_Process Binary_Integer := 0;
4485: l_Exp_Ending_Date_Check Pa_Expenditures_All.Expenditure_Ending_Date%TYPE := Null;
4486: l_Timecard_Table_Index Binary_Integer := 0;
4479: l_bresult Boolean;
4480:
4481: /* Variables needed for running of the summary-validation and Business Message APIs */
4482: l_Timecard_Table Pa_Otc_Api.Timecard_Table;
4483: l_Timecard_Table_Week Pa_Otc_Api.Timecard_Table;
4484: l_Weeks_To_Process Binary_Integer := 0;
4485: l_Exp_Ending_Date_Check Pa_Expenditures_All.Expenditure_Ending_Date%TYPE := Null;
4486: l_Timecard_Table_Index Binary_Integer := 0;
4487: l_Time_Building_Block_Id Hxc_Time_Building_Blocks.Time_Building_Block_Id%TYPE := Null;
4529:
4530: Begin
4531:
4532: G_Stage := 'Entering procedure Validate_Process().';
4533: Pa_Otc_Api.TrackPath('ADD','Validate_Process');
4534:
4535: -- The out variables are used in the summary level validation and the business message
4536: -- API calls.
4537:
4535: -- The out variables are used in the summary level validation and the business message
4536: -- API calls.
4537:
4538: G_Stage := 'Find and Validate Header Record in the Building Blocks Pl/Sql table.';
4539: Pa_Otc_Api.FindandValidateHeader(
4540: P_Building_Blocks_Table => P_Building_Blocks,
4541: P_Attribute_Table => P_Attribute_Table,
4542: P_Message_Table => P_Message_Table,
4543: X_TimeBB_Id => l_Time_Building_Block_Id,
4638: l_Detail_Attr_Changed := 'N';
4639:
4640: G_Stage := 'Pull out the data to friendlier formated variables.';
4641: -- Pull out the data to project friendly variables
4642: Pa_Otc_Api.RetrieveProjAttribution(
4643: P_Building_Block_Rec => l_Building_Block_Record,
4644: P_Building_Block => P_Building_Blocks,
4645: P_Attribute_Table => P_Attribute_Table,
4646: X_Detail_Attr_Changed => l_Detail_Attr_Changed,
4657:
4658: End If;
4659:
4660: G_Stage := 'Determine the processing flags for use further in code.';
4661: Pa_Otc_Api.DetermineProcessingFlags(
4662: P_BB_Id => P_Building_Blocks(i).Time_Building_Block_Id,
4663: P_BB_Ovn => P_Building_Blocks(i).Object_Version_Number,
4664: P_BB_Date_To => P_Building_Blocks(i).Date_To,
4665: P_BB_Changed => l_Logical_Rec_Changed,
4702:
4703: G_Msg_Tokens_Table.Delete;
4704:
4705: -- Add record to error table.
4706: Pa_Otc_Api.Add_Error_To_Table(
4707: P_Message_Table => P_Message_Table,
4708: P_Message_Name => 'PA_TR_UNDO_DEL_IN_OTC',
4709: P_Message_Level => 'ERROR',
4710: P_Message_Field => NULL,
4717: G_Stage := 'Not allowed to Delete Item In OTL - Inserting error rec.';
4718: G_Msg_Tokens_Table.Delete;
4719:
4720: -- Add record to error table.
4721: Pa_Otc_Api.Add_Error_To_Table(
4722: P_Message_Table => P_Message_Table,
4723: P_Message_Name => 'PA_NO_DEL_EX_ITEM',
4724: P_Message_Level => 'ERROR',
4725: P_Message_Field => NULL,
4741: G_Stage := 'Not allowed to Adjust Item In OTL confict - Inserting error rec.';
4742: G_Msg_Tokens_Table.Delete;
4743:
4744: -- Add record to error table.
4745: Pa_Otc_Api.Add_Error_To_Table(
4746: P_Message_Table => P_Message_Table,
4747: P_Message_Name => 'PA_TR_UNDO_CHGE_IN_OTC',
4748: P_Message_Level => 'ERROR',
4749: P_Message_Field => NULL,
4757:
4758: G_Msg_Tokens_Table.Delete;
4759:
4760: -- Add record to error table.
4761: Pa_Otc_Api.Add_Error_To_Table(
4762: P_Message_Table => P_Message_Table,
4763: P_Message_Name => 'PA_TR_ADJ_NO_NET_ZERO',
4764: P_Message_Level => 'ERROR',
4765: P_Message_Field => NULL,
4787:
4788: G_Msg_Tokens_Table.Delete;
4789:
4790: -- Add record to error table.
4791: Pa_Otc_Api.Add_Error_To_Table(
4792: P_Message_Table => P_Message_Table,
4793: P_Message_Name => 'PA_UOM_MUST_BE_HOURS',
4794: P_Message_Level => 'ERROR',
4795: P_Message_Field => NULL,
4812: G_Stage := 'Negative quantity not allowed In OTL - Inserting error rec.';
4813: G_Msg_Tokens_Table.Delete;
4814:
4815: -- Add record to error table.
4816: Pa_Otc_Api.Add_Error_To_Table(
4817: P_Message_Table => P_Message_Table,
4818: P_Message_Name => 'PA_SU_NEGATIVE_NUM_NOT_ALLOWED',
4819: P_Message_Level => 'ERROR',
4820: P_Message_Field => NULL,
4830: G_Stage := 'Invalid Person Type - Inserting error rec.';
4831: G_Msg_Tokens_Table.Delete;
4832:
4833: -- Add record to error table.
4834: Pa_Otc_Api.Add_Error_To_Table(
4835: P_Message_Table => P_Message_Table,
4836: P_Message_Name => 'PA_INVALID_PERSON_TYPE',
4837: P_Message_Level => 'ERROR',
4838: P_Message_Field => NULL,
4849: G_Stage := 'Project organization does not allow CWK timecards - Inserting error rec.';
4850: G_Msg_Tokens_Table.Delete;
4851:
4852: -- Add record to error table.
4853: Pa_Otc_Api.Add_Error_To_Table(
4854: P_Message_Table => P_Message_Table,
4855: P_Message_Name => 'PA_CWK_TC_NOT_ALLOWED',
4856: P_Message_Level => 'ERROR',
4857: P_Message_Field => NULL,
4870: G_Stage := 'Price Type is null - Inserting error rec.';
4871: G_Msg_Tokens_Table.Delete;
4872:
4873: -- Add record to error table.
4874: Pa_Otc_Api.Add_Error_To_Table(
4875: P_Message_Table => P_Message_Table,
4876: P_Message_Name => 'PA_CWK_PRICE_TYPE_NULL',
4877: P_Message_Level => 'ERROR',
4878: P_Message_Field => NULL,
4885: G_Stage := 'Derived Vendor Id is null - Inserting error rec.';
4886: G_Msg_Tokens_Table.Delete;
4887:
4888: -- Add record to error table.
4889: Pa_Otc_Api.Add_Error_To_Table(
4890: P_Message_Table => P_Message_Table,
4891: P_Message_Name => 'PA_CWK_VEND_INFO_NULL',
4892: P_Message_Level => 'ERROR',
4893: P_Message_Field => NULL,
4906:
4907: -- Get Project Number
4908: l_Error_Code := Null;
4909: G_stage := 'Get Project Number.';
4910: Pa_Otc_Api.Validate_Project_Exists(
4911: P_Project_Id => l_Proj_Attrib_Rec.Project_Id,
4912: X_Error_Code => l_Error_Code,
4913: X_Error_Type => l_Error_Type,
4914: X_Project_Number => l_Proj_Attrib_Rec.Project_Number);
4918: G_Stage := 'Get Project Number - Inserting error rec.';
4919: G_Msg_Tokens_Table.Delete;
4920:
4921: -- Add record to error table.
4922: Pa_Otc_Api.Add_Error_To_Table(
4923: P_Message_Table => P_Message_Table,
4924: P_Message_Name => l_Error_Code,
4925: P_Message_Level => 'ERROR',
4926: P_Message_Field => 'PROJECT_ID',
4932:
4933: -- Get Task Number
4934: l_Error_Code := Null;
4935: G_stage := 'Get Task Number.';
4936: Pa_Otc_Api.Validate_Task_Exists(
4937: P_Task_Id => l_Proj_Attrib_Rec.Task_Id,
4938: P_Project_Id => l_Proj_Attrib_Rec.Project_Id,
4939: X_Error_Code => l_Error_Code,
4940: X_Error_Type => l_Error_Type,
4945: G_Stage := 'Get Task Number - Inserting error rec.';
4946: G_Msg_Tokens_Table.Delete;
4947:
4948: -- Add record to error table.
4949: Pa_Otc_Api.Add_Error_To_Table(
4950: P_Message_Table => P_Message_Table,
4951: P_Message_Name => l_Error_Code,
4952: P_Message_Level => 'ERROR',
4953: P_Message_Field => 'TASK_ID',
4960: -- Validate the expenditure type and system linkage function
4961: G_Stage := 'Validation the expenditure type and system linkage function.';
4962: l_Error_Code := Null;
4963:
4964: Pa_Otc_Api.Validate_Exp_Type_Exists(
4965: P_System_Linkage => l_Proj_Attrib_Rec.Sys_Linkage_Func,
4966: P_Expenditure_Type => l_Proj_Attrib_Rec.Expenditure_Type,
4967: P_Exp_Item_Date => l_Proj_Attrib_Rec.Expenditure_Item_Date,
4968: X_Error_Type => l_Error_Type,
4977:
4978: If l_Proj_Attrib_Rec.Sys_Linkage_Func Is Null OR
4979: l_Proj_Attrib_Rec.Sys_Linkage_Func Not in ('OT','ST') Then
4980:
4981: Pa_Otc_Api.Add_Error_To_Table(
4982: P_Message_Table => P_Message_Table,
4983: P_Message_Name => l_Error_Code,
4984: P_Message_Level => 'ERROR',
4985: P_Message_Field => 'SYSTEM_LINKAGE_FUNCTION',
4988: P_Time_Attribute_Id => l_Proj_Attrib_Rec.Sys_Link_Attr_Id);
4989:
4990: Else
4991:
4992: Pa_Otc_Api.Add_Error_To_Table(
4993: P_Message_Table => P_Message_Table,
4994: P_Message_Name => l_Error_Code,
4995: P_Message_Level => 'ERROR',
4996: P_Message_Field => 'EXPENDITURE_TYPE',
5006: -- Add record to error table.
5007:
5008: G_Msg_Tokens_Table.Delete;
5009:
5010: Pa_Otc_Api.Add_Error_To_Table(
5011: P_Message_Table => P_Message_Table,
5012: P_Message_Name => 'INVALID_ETYPE_SYSLINK',
5013: P_Message_Level => 'ERROR',
5014: P_Message_Field => 'SYSTEM_LINKAGE_FUNCTION',
5035: G_Msg_Tokens_Table(1).Token_Value :=
5036: fnd_date.date_to_displaydate(l_Proj_Attrib_Rec.Expenditure_Item_Date);
5037:
5038: -- Add record to error table.
5039: Pa_Otc_Api.Add_Error_To_Table(
5040: P_Message_Table => P_Message_Table,
5041: P_Message_Name => 'NO_ASSIGNMENT',
5042: P_Message_Level => 'ERROR',
5043: P_Message_Field => Null,
5069:
5070: G_Stage := 'Check Job Id - Inserting error rec.';
5071:
5072: -- Add record to error table.
5073: Pa_Otc_Api.Add_Error_To_Table(
5074: P_Message_Table => P_Message_Table,
5075: P_Message_Name => 'NO_ASSIGNMENT',
5076: P_Message_Level => 'ERROR',
5077: P_Message_Field => Null,
5139: -- messages to OTL. Only message names and token info.
5140:
5141: G_Msg_Tokens_Table.Delete;
5142:
5143: Pa_Otc_Api.Add_Error_To_Table(
5144: P_Message_Table => P_Message_Table,
5145: P_Message_Name => 'PA_DFF_VALIDATION_FAILED',
5146: P_Message_Level => 'ERROR',
5147: P_Message_Field => Null,
5217: If (l_status <> 'PA_NO_ASSIGNMENT' or test_term_with_pay is Null) then
5218: -- check if patc has returned any errors
5219: If l_Status is Not Null Then
5220:
5221: If Pa_Otc_Api.IsNumber(l_Status) Then
5222:
5223: Raise E_Unhandled_Exception;
5224:
5225: Else
5289: End If;
5290:
5291: If l_Status <> 'NO_ASSIGNMENT' Then
5292:
5293: Pa_Otc_Api.Add_Error_To_Table(
5294: P_Message_Table => P_Message_Table,
5295: P_Message_Name => l_Status,
5296: P_Message_Level => l_Error_Level, /* Bug#2798986 */
5297: P_Message_Field => Null,
5334: G_Msg_Tokens_Table(1).Token_Name := 'BILL_FLAG';
5335: G_Msg_Tokens_Table(1).Token_Value := l_Bill_Flag_Meaning;
5336:
5337: -- Add record to error message table.
5338: Pa_Otc_Api.Add_Error_To_Table(
5339: P_Message_Table => P_Message_Table,
5340: P_Message_Name => 'BILL_FLAG_CHGE_INVALID',
5341: P_Message_Level => 'ERROR',
5342: P_Message_Field => 'BILLABLE_FLAG',
5380: G_Msg_Tokens_Table(1).Token_Name := 'BILL_FLAG';
5381: G_Msg_Tokens_Table(1).Token_Value := l_Bill_Flag_Meaning;
5382:
5383: -- Add record to error message table.
5384: Pa_Otc_Api.Add_Error_To_Table(
5385: P_Message_Table => P_Message_Table,
5386: P_Message_Name => 'BILL_FLAG_CHGE_INVALID',
5387: P_Message_Level => 'ERROR',
5388: P_Message_Field => 'BILLABLE_FLAG',
5477: -- Add record to error message table
5478: G_Stage := 'Calling Summary-validation Extension - Insert Error Rec.';
5479: G_Msg_Tokens_Table.Delete;
5480:
5481: Pa_Otc_Api.Add_Error_To_Table(
5482: P_Message_Table => P_Message_Table,
5483: P_Message_Name => l_Msg_Name,
5484: P_Message_Level => 'ERROR',
5485: P_Message_Field => Null,
5539: l_Msg_Application := 'PA';
5540:
5541: End If;
5542:
5543: Pa_Otc_Api.Add_Error_To_Table(
5544: P_Message_Table => P_Message_Table,
5545: P_Message_Name => l_Msg_Name,
5546: P_Message_Level => 'BUSINESS',
5547: P_Message_Field => Null,
5558:
5559: End If; -- P_Message_Table.Count = 0 and l_Timecard_Table.Count > 0
5560:
5561: G_Stage := 'Leaving procedure Validate_Process().';
5562: Pa_Otc_Api.TrackPath('STRIP','Validate_Process');
5563:
5564: Exception
5565: When E_Unhandled_Exception Then
5566: Raise_Application_Error(-20021,SqlErrm(to_number(l_Status)));
5626:
5627: Begin
5628:
5629: G_Stage := 'Entering procedure Validate_Project_Exists().';
5630: Pa_Otc_Api.TrackPath('ADD','Validate_Project_Exists');
5631:
5632: If P_Project_Id is Not Null Then
5633:
5634: G_Stage := 'Open cursor GetProjInfo.';
5672:
5673: End If; -- End Project Id is not null
5674:
5675: G_Stage := 'Leaving procedure Validate_Project_Exists().';
5676: Pa_Otc_Api.TrackPath('STRIP','Validate_Project_Exists');
5677:
5678: EXCEPTION
5679: When Others Then
5680: Raise;
5742:
5743: -- Validate project_id parameter
5744:
5745: G_Stage := 'Entering procedure Validate_Task_Exists().';
5746: Pa_Otc_Api.TrackPath('ADD','Validate_Task_Exists');
5747:
5748: If P_Task_Id is Not Null Then
5749:
5750: G_Stage := 'Parameter P_Task_Id is not Null, get Task Info.';
5798:
5799: End If; -- End P_Task_Id is not null
5800:
5801: G_Stage := 'Leaving procedure Validate_Task_Exists().';
5802: Pa_Otc_Api.TrackPath('STRIP','Validate_Task_Exists');
5803:
5804: Exception
5805: When Others Then
5806: Raise;
5855:
5856: Begin
5857:
5858: G_Stage := 'Entering procedure Validate_Exp_Type_Exists().';
5859: Pa_Otc_Api.TrackPath('ADD','Validate_Exp_Type_Exists');
5860:
5861: G_Stage := 'Open cursor Fetch AdjustmentAllowed.';
5862: Open Cur_Etype_Slink(P_Exp_Item_Date,P_System_Linkage,P_Expenditure_Type);
5863:
5876:
5877: End If;
5878:
5879: G_Stage := 'Leaving procedure Validate_Exp_Type_Exists().';
5880: Pa_Otc_Api.TrackPath('STRIP','Validate_Exp_Type_Exists');
5881:
5882: Exception
5883: When Others Then
5884: Raise;
5919:
5920: Begin
5921:
5922: G_Stage := 'Entering procedure Validate_Overriding_Approver().';
5923: Pa_Otc_Api.TrackPath('ADD','Validate_Overriding_Approver');
5924:
5925: G_Stage := 'Check if Approver Id populated or not.';
5926: If P_Approver_Id is Not Null Then
5927:
5947:
5948: End If; -- End Approver_Id is not null
5949:
5950: G_Stage := 'Leaving procedure Validate_Overriding_Approver().';
5951: Pa_Otc_Api.TrackPath('STRIP','Validate_Overriding_Approver');
5952:
5953: Exception
5954: When No_Data_Found Then
5955: X_Error_Type := 'E';
5983: -- P_BB_Ovn - Hxc_Time_Building_Blocks.Object_Version_Number%TYPE
5984: -- P_BB_Date_To - Hxc_Time_Building_Blocks.Date_To%TYPE
5985: -- P_BB_Changed - VARCHAR2
5986: -- P_BB_New - VARCHAR2
5987: -- P_Proj_Attribute_Rec - Pa_Otc_Api.Project_Attribution_Rec
5988: -- P_Mode - VARCHAR2(10)
5989: -- P_Process_Flag - Varchar2(30)
5990: -- OUT
5991: -- X_BB_Detail_Changed - VARCHAR2(1)
6001: P_BB_Ovn IN Hxc_Time_Building_Blocks.Object_Version_Number%TYPE,
6002: P_BB_Date_To IN Hxc_Time_Building_Blocks.Date_To%TYPE,
6003: P_BB_Changed IN Varchar2,
6004: P_BB_New IN Varchar2,
6005: P_Proj_Attribute_Rec IN Pa_Otc_Api.Project_Attribution_Rec,
6006: P_Mode IN Varchar2,
6007: P_Process_Flag IN Varchar2,
6008: X_BB_Detail_Changed OUT NOCOPY Varchar2,
6009: X_Data_Conflict_Flag OUT NOCOPY Varchar2,
6069:
6070: Begin
6071:
6072: G_Stage := 'Entering procedure DetermineProcessingFlags().';
6073: Pa_Otc_Api.TrackPath('ADD','DetermineProcessingFlags');
6074:
6075: G_Stage := 'Initialize the out parameters to No.';
6076: X_BB_Detail_Changed := 'N';
6077: X_Data_Conflict_Flag := 'N';
6253:
6254: End If; -- l_Max_Version > 0
6255:
6256: G_Stage := 'Leaving procedure DetermineProcessingFlags().';
6257: Pa_Otc_Api.TrackPath('STRIP','DetermineProcessingFlags');
6258:
6259: Exception
6260: When Others Then
6261: Raise;
6289:
6290: Begin
6291:
6292: G_Stage := 'Entering procedure AdjustAllowedToOTCItem().';
6293: Pa_Otc_Api.TrackPath('ADD','AdjustAllowedToOTCItem');
6294:
6295: G_Path := ' ';
6296:
6297: G_Stage := 'Check if the original transaction reference is null or not.';
6328:
6329: End If;
6330:
6331: G_Stage := 'Leaving procedure AdjustAllowedToOTCItem().';
6332: Pa_Otc_Api.TrackPath('STRIP','AdjustAllowedToOTCItem');
6333:
6334: Exception
6335: When Others Then
6336: Raise_Application_Error(-20003,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6332: Pa_Otc_Api.TrackPath('STRIP','AdjustAllowedToOTCItem');
6333:
6334: Exception
6335: When Others Then
6336: Raise_Application_Error(-20003,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6337:
6338: End AdjustAllowedToOTCItem;
6339:
6340:
6369:
6370: Begin
6371:
6372: G_Stage := 'Entering procedure ProjectTaskUsed().';
6373: Pa_Otc_Api.TrackPath('ADD','ProjectTaskUsed');
6374:
6375: G_Path := ' ';
6376:
6377: G_Stage := 'Check that the parameters is valid.';
6406:
6407: End If;
6408:
6409: G_Stage := 'Leaving procedure ProjectTaskUsed().';
6410: Pa_Otc_Api.TrackPath('STRIP','ProjectTaskUsed');
6411:
6412: Exception
6413: When Others Then
6414: Raise_Application_Error(-20005,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6410: Pa_Otc_Api.TrackPath('STRIP','ProjectTaskUsed');
6411:
6412: Exception
6413: When Others Then
6414: Raise_Application_Error(-20005,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6415:
6416: End ProjectTaskUsed;
6417:
6418: -- =======================================================================
6446:
6447: Begin
6448:
6449: G_Stage := 'Entering procedure ProjectTaskPurgeable().';
6450: Pa_Otc_Api.TrackPath('ADD','ProjectTaskPurgeable');
6451:
6452: G_Path := ' ';
6453:
6454: G_Stage := 'Check that the parameters is valid.';
6497:
6498: End If;
6499:
6500: G_Stage := 'Leaving procedure ProjectTaskPurgeable().';
6501: Pa_Otc_Api.TrackPath('STRIP','ProjectTaskPurgeable');
6502:
6503: Exception
6504: When Others Then
6505: Raise_Application_Error(-20006,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6501: Pa_Otc_Api.TrackPath('STRIP','ProjectTaskPurgeable');
6502:
6503: Exception
6504: When Others Then
6505: Raise_Application_Error(-20006,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
6506:
6507: End ProjectTaskPurgeable;
6508:
6509:
6523: -- P_Attribute_Table - Hxc_User_Type_Definition_Grp.App_Attributes_Info
6524: -- X_Detail_Attr_Changed - Varchar2
6525: -- OUT
6526: -- X_Detail_Attr_Changed - Varchar2
6527: -- X_Proj_Attrib_Rec - Pa_Otc_Api.Project_Attribution_Rec
6528: --
6529:
6530: /*--------------------------------------------------------------------------*/
6531:
6533: P_Building_Block_Rec IN Hxc_User_Type_Definition_Grp.Building_Block_Info,
6534: P_Building_Block IN Hxc_User_Type_Definition_Grp.Timecard_Info,
6535: P_Attribute_Table IN Hxc_User_Type_Definition_Grp.App_Attributes_Info,
6536: X_Detail_Attr_Changed IN OUT NOCOPY Varchar2,
6537: X_Proj_Attrib_Rec OUT NOCOPY Pa_Otc_Api.Project_Attribution_Rec) -- 2672653
6538:
6539: Is
6540:
6541: j Binary_Integer := Null;
6544:
6545: Begin
6546:
6547: G_Stage := 'Entering procedure RetrieveProjAttribution().';
6548: Pa_Otc_Api.TrackPath('ADD','RetrieveProjAttribution');
6549:
6550: G_Stage := 'Entering attribute table loop.';
6551: Loop
6552:
6845: End If;
6846:
6847: -- Beging CWK changes PA.M
6848: G_Stage := 'Get Person_Type.';
6849: X_Proj_Attrib_Rec.Person_Type := Pa_Otc_Api.GetPersonType(
6850: P_Person_Id => X_Proj_Attrib_Rec.Inc_By_Person_Id,
6851: P_Ei_Date => X_Proj_Attrib_Rec.Expenditure_Item_Date);
6852:
6853:
6853:
6854: If X_Proj_Attrib_Rec.Person_Type = 'CWK' And X_Proj_Attrib_Rec.Po_Line_Id Is Not Null Then
6855:
6856: G_Stage := 'Get PO Info.';
6857: PA_Otc_Api.GetPOInfo(
6858: P_PO_Line_Id => X_Proj_Attrib_Rec.Po_Line_Id,
6859: X_PO_Header_Id => X_Proj_Attrib_Rec.PO_Header_Id,
6860: X_Vendor_Id => X_Proj_Attrib_Rec.Vendor_Id);
6861:
6862: End If;
6863:
6864: -- End CWK changes PA.M
6865: G_Stage := 'Leaving procedure RetrieveProjAttribution().';
6866: Pa_Otc_Api.TrackPath('STRIP','RetrieveProjAttribution');
6867:
6868: Exception
6869: When Others Then
6870: Raise;
6889: -- X_Detail_Attr_Changed - Varchar2
6890: -- OUT
6891: -- P_Attribute_Table - Hxc_User_Type_Definition_Grp.App_Attributes_Info
6892: -- X_Detail_Attr_Changed - Varchar2
6893: -- X_Proj_Attrib_Rec - Pa_Otc_Api.Project_Attribution_Rec
6894: --
6895:
6896: /*--------------------------------------------------------------------------*/
6897:
6899: P_Building_Block_Rec IN Hxc_User_Type_Definition_Grp.Building_Block_Info,
6900: P_Building_Block IN Hxc_User_Type_Definition_Grp.Timecard_Info,
6901: P_Attribute_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.App_Attributes_Info,
6902: X_Detail_Attr_Changed IN OUT NOCOPY Varchar2,
6903: X_Proj_Attrib_Rec OUT NOCOPY Pa_Otc_Api.Project_Attribution_Rec) -- 2672653
6904:
6905: Is
6906:
6907: j Binary_Integer := Null;
6913:
6914: Begin
6915:
6916: G_Stage := 'Entering procedure RetrieveProjAttribution().';
6917: Pa_Otc_Api.TrackPath('ADD','RetrieveProjAttribution');
6918:
6919: found_billable := false;
6920:
6921: G_Stage := 'Entering attribute table loop.';
7277: End If;
7278:
7279: -- Beging CWK changes PA.M
7280: G_Stage := 'Get Person_Type.';
7281: X_Proj_Attrib_Rec.Person_Type := Pa_Otc_Api.GetPersonType(
7282: P_Person_Id => X_Proj_Attrib_Rec.Inc_By_Person_Id,
7283: P_Ei_Date => X_Proj_Attrib_Rec.Expenditure_Item_Date);
7284:
7285:
7285:
7286: If X_Proj_Attrib_Rec.Person_Type = 'CWK' And X_Proj_Attrib_Rec.Po_Line_Id Is Not Null Then
7287:
7288: G_Stage := 'Get PO Info.';
7289: PA_Otc_Api.GetPOInfo(
7290: P_PO_Line_Id => X_Proj_Attrib_Rec.Po_Line_Id,
7291: X_PO_Header_Id => X_Proj_Attrib_Rec.PO_Header_Id,
7292: X_Vendor_Id => X_Proj_Attrib_Rec.Vendor_Id);
7293:
7294: End If;
7295:
7296: -- End CWK changes PA.M
7297: G_Stage := 'Leaving procedure RetrieveProjAttribution().';
7298: Pa_Otc_Api.TrackPath('STRIP','RetrieveProjAttribution');
7299:
7300: Exception
7301: When Others Then
7302: Raise;
7488:
7489: G_Path := ' ';
7490:
7491: G_Stage := 'Entering procedure GetPRMAssignTemplates().';
7492: Pa_Otc_Api.TrackPath('ADD','GetPRMAssignTemplates');
7493:
7494: G_Stage := 'Initialize the pl/sql message table.';
7495: l_Message_Table.Delete;
7496: l_Building_Blocks_Table.Delete;
7516:
7517: G_Msg_Tokens_Table.Delete;
7518:
7519: -- Add record to error table.
7520: Pa_Otc_Api.Add_Error_To_Table(
7521: P_Message_Table => l_Message_Table,
7522: P_Message_Name => 'PA_INVALID_WEEK_ENDING_DATE',
7523: P_Message_Level => 'ERROR',
7524: P_Message_Field => Null,
7743:
7744: If l_Val_Proj_Flag = 'N' Then
7745:
7746: G_Stage := 'Create error message for Invalid Project in PRM Template.';
7747: Pa_Otc_Api.Add_Error_To_Table(
7748: P_Message_Table => l_Message_Table,
7749: P_Message_Name => 'INVALID_PROJECT',
7750: P_Message_Level => 'ERROR',
7751: P_Message_Field => 'Project_Id',
7758: If l_Valid_ExpTypClass_Combo = 'N' Then
7759:
7760: G_Stage := 'Create error message for Invalid Exp Type/Sys ' ||
7761: 'Link func in PRM Template.';
7762: Pa_Otc_Api.Add_Error_To_Table(
7763: P_Message_Table => l_Message_Table,
7764: P_Message_Name => 'INVALID_ETYPE_SYSLINK',
7765: P_Message_Level => 'ERROR',
7766: P_Message_Field => 'Expenditure_Type',
7807:
7808: -- End Bug 3766110
7809:
7810: G_Stage := 'Leaving procedure GetPRMAssignTemplates().';
7811: Pa_Otc_Api.TrackPath('STRIP','GetPRMAssignTemplates');
7812:
7813: Exception
7814: When Others then
7815: Raise_Application_Error(-20007,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
7811: Pa_Otc_Api.TrackPath('STRIP','GetPRMAssignTemplates');
7812:
7813: Exception
7814: When Others then
7815: Raise_Application_Error(-20007,'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM);
7816:
7817: End GetPRMAssignTemplates;
7818:
7819: -- ========================================================================
7877:
7878: Begin
7879:
7880: G_Stage := 'Entering FindandValidateHeader procedure.';
7881: Pa_Otc_Api.TrackPath('ADD','FindandValidateHeader');
7882:
7883: Loop
7884:
7885: If i is Null Then
7929: X_Pass_Val_Flag := 'N';
7930: G_Msg_Tokens_Table.Delete;
7931:
7932: -- Add record to error table.
7933: Pa_Otc_Api.Add_Error_To_Table(
7934: P_Message_Table => P_Message_Table,
7935: P_Message_Name => 'PA_INVALID_WEEK_ENDING_DATE',
7936: P_Message_Level => 'ERROR',
7937: P_Message_Field => Null,
7984: * though the user could have assigned an overriding approver.
7985: */
7986:
7987: G_Stage := 'Call Validate_Overriding_Approver procedure.';
7988: Pa_Otc_Api.Validate_Overriding_Approver(
7989: P_Approver_Id => to_number(P_Attribute_Table(x).Attribute_Value),
7990: X_Approver_Id => X_Ovr_Approver_Person_Id,
7991: X_Error_Code => l_Error_Code,
7992: X_Error_Type => l_Error_Type);
7996: G_Stage := 'Validate Overriding Approver - Inserting error rec.';
7997: G_Msg_Tokens_Table.Delete;
7998:
7999: -- Add record to error table.
8000: Pa_Otc_Api.Add_Error_To_Table(
8001: P_Message_Table => P_Message_Table,
8002: P_Message_Name => l_Error_Code,
8003: P_Message_Level => 'ERROR',
8004: P_Message_Field => 'OVERRIDING_APPROVER_PERSON_ID',
8027: G_Stage := 'No attribute record was found for overriding approver when there should have been.';
8028: G_Msg_Tokens_Table.Delete;
8029:
8030: -- Add record to error table.
8031: Pa_Otc_Api.Add_Error_To_Table(
8032: P_Message_Table => P_Message_Table,
8033: P_Message_Name => 'PA_OVRRDE_APPROVER_NOT_VALID',
8034: P_Message_Level => 'ERROR',
8035: P_Message_Field => 'OVERRIDING_APPROVER_PERSON_ID',
8058:
8059: End If;
8060:
8061: G_Stage := 'Leaving FindandValidateHeader() procedure.';
8062: Pa_Otc_Api.TrackPath('STRIP','FindandValidateHeader');
8063:
8064: Exception
8065: When Others Then
8066: Raise;
8089: ,X_Messages OUT NOCOPY Varchar2)
8090:
8091: Is
8092:
8093: l_Timecard_Table Pa_Otc_Api.Timecard_Table;
8094: l_Inc_By_Person_Id Pa_Expenditures_All.Incurred_By_Person_Id%TYPE;
8095: l_Overriding_Approver_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE;
8096:
8097: /* Stores a single record from the Building Block Table */
8115:
8116: G_Path := ' ';
8117:
8118: G_Stage := 'Entering Wf_AutoApproval_BusMsg() procedure.';
8119: Pa_Otc_Api.TrackPath('ADD','Wf_AutoApproval_BusMsg');
8120:
8121: G_Stage := 'Initialize the hxc pl/sql tables that will be used.';
8122: l_Timecard_Table.delete;
8123: l_Message_Table.delete;
8121: G_Stage := 'Initialize the hxc pl/sql tables that will be used.';
8122: l_Timecard_Table.delete;
8123: l_Message_Table.delete;
8124:
8125: G_Stage := 'Calling Pa_Otc_Api.CreateProjTimecardTable()';
8126: Pa_Otc_Api.CreateProjTimecardTable(
8127: X_Inc_By_Person_Id => l_Inc_By_Person_Id,
8128: X_Timecard_Table => l_Timecard_Table,
8129: X_Overriding_Approver_Id => l_Overriding_Approver_Id );
8122: l_Timecard_Table.delete;
8123: l_Message_Table.delete;
8124:
8125: G_Stage := 'Calling Pa_Otc_Api.CreateProjTimecardTable()';
8126: Pa_Otc_Api.CreateProjTimecardTable(
8127: X_Inc_By_Person_Id => l_Inc_By_Person_Id,
8128: X_Timecard_Table => l_Timecard_Table,
8129: X_Overriding_Approver_Id => l_Overriding_Approver_Id );
8130:
8146: X_AutoApproval_Flag := Null;
8147:
8148: G_Msg_Tokens_Table.Delete;
8149:
8150: Pa_Otc_Api.Add_Error_To_Table(
8151: P_Message_Table => l_Message_Table,
8152: P_Message_Name => l_Message_Name,
8153: P_Message_Level => 'ERROR',
8154: P_Message_Field => Null,
8209: l_Msg_Application := 'PA';
8210:
8211: End If;
8212:
8213: Pa_Otc_Api.Add_Error_To_Table(
8214: P_Message_Table => l_Message_Table,
8215: P_Message_Name => l_Msg_Name,
8216: P_Message_Level => 'BUSINESS',
8217: P_Message_Field => Null,
8230: X_Messages := Hxc_Integration_Layer_V1_Grp.Messages_To_String(P_Messages => l_Message_Table);
8231: End If;
8232:
8233: G_Stage := 'Leaving Wf_AutoApproval_BusMsg() procedure.';
8234: Pa_Otc_Api.TrackPath('STRIP','Wf_AutoApproval_BusMsg');
8235:
8236: Exception
8237: When Others Then
8238: Raise_Application_Error(-20500, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
8234: Pa_Otc_Api.TrackPath('STRIP','Wf_AutoApproval_BusMsg');
8235:
8236: Exception
8237: When Others Then
8238: Raise_Application_Error(-20500, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
8239:
8240: End Wf_AutoApproval_BusMsg;
8241:
8242: -- =======================================================================
8265: X_Messages OUT NOCOPY Varchar2)
8266:
8267: Is
8268:
8269: l_Timecard_Table Pa_Otc_Api.Timecard_Table;
8270: l_Inc_By_Person_Id Pa_Expenditures_All.Incurred_By_Person_Id%TYPE;
8271: l_Overriding_Approver_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE := Null;
8272:
8273: l_Message_Table Hxc_User_Type_Definition_Grp.Message_Table;
8290:
8291: G_Path := ' ';
8292:
8293: G_Stage := 'Entering Wf_RouteTo_CheckApproval() procedure.';
8294: Pa_Otc_Api.TrackPath('ADD','Wf_RouteTo_CheckApproval');
8295:
8296: G_Stage := 'Initialize the PA pl/sql tables that will be used.';
8297: l_Timecard_Table.Delete;
8298:
8295:
8296: G_Stage := 'Initialize the PA pl/sql tables that will be used.';
8297: l_Timecard_Table.Delete;
8298:
8299: G_Stage := 'Calling Pa_Otc_Api.CreateProjTimecardTable()';
8300: Pa_Otc_Api.CreateProjTimecardTable(
8301: X_Inc_By_Person_Id => l_Inc_By_Person_Id,
8302: X_Timecard_Table => l_Timecard_Table,
8303: X_Overriding_Approver_Id => l_Overriding_Approver_Id);
8296: G_Stage := 'Initialize the PA pl/sql tables that will be used.';
8297: l_Timecard_Table.Delete;
8298:
8299: G_Stage := 'Calling Pa_Otc_Api.CreateProjTimecardTable()';
8300: Pa_Otc_Api.CreateProjTimecardTable(
8301: X_Inc_By_Person_Id => l_Inc_By_Person_Id,
8302: X_Timecard_Table => l_Timecard_Table,
8303: X_Overriding_Approver_Id => l_Overriding_Approver_Id);
8304:
8387: l_Msg_App := 'PA';
8388:
8389: End If;
8390:
8391: Pa_Otc_Api.Add_Error_To_Table (
8392: P_Message_Table => l_Message_Table,
8393: P_Message_Name => l_Msg_Code,
8394: P_Message_Level => 'ERROR',
8395: P_Message_Field => Null,
8450: l_Message_Name := 'PA_TR_NO_ROUTE_TO_PERSON';
8451:
8452: G_Msg_Tokens_Table.Delete;
8453:
8454: Pa_Otc_Api.Add_Error_To_Table(
8455: P_Message_Table => l_Message_Table,
8456: P_Message_Name => l_Message_Name,
8457: P_Message_Level => 'ERROR',
8458: P_Message_Field => Null,
8466: 'Create an error message.';
8467: l_Message_Name := 'PA_NO_APPROVER_FOUND';
8468: G_Msg_Tokens_Table.Delete;
8469:
8470: Pa_Otc_Api.Add_Error_To_Table(
8471: P_Message_Table => l_Message_Table,
8472: P_Message_Name => l_Message_Name,
8473: P_Message_Level => 'ERROR',
8474: P_Message_Field => Null,
8499: X_Messages := Hxc_Integration_Layer_V1_Grp.Messages_To_String(P_Messages => l_Message_Table);
8500: End If;
8501:
8502: G_Stage := 'Leaving Wf_RouteTo_CheckApproval() procedure.';
8503: Pa_Otc_Api.TrackPath('STRIP','Wf_RouteTo_CheckApproval');
8504:
8505: Exception
8506: When Others Then
8507: Raise_Application_Error(-20600, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
8503: Pa_Otc_Api.TrackPath('STRIP','Wf_RouteTo_CheckApproval');
8504:
8505: Exception
8506: When Others Then
8507: Raise_Application_Error(-20600, 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_stage || ' : ' || SQLERRM );
8508:
8509: End Wf_RouteTo_CheckApproval;
8510:
8511: -- =======================================================================
8519: --
8520: -- Parameters :
8521: -- OUT
8522: -- X_Inc_By_Person_Id - Pa_Expenditures_All.Incurred_By_Person_Id%TYPE
8523: -- X_Timecard_Table - Pa_Otc_Api.Timecard_Table
8524: -- X_Overriding_Approver_Id - Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE
8525:
8526: /*------------------------------------------------------------------------- */
8527:
8526: /*------------------------------------------------------------------------- */
8527:
8528: Procedure CreateProjTimecardTable(
8529: X_Inc_By_Person_Id OUT NOCOPY Pa_Expenditures_All.Incurred_By_Person_Id%TYPE,
8530: X_Timecard_Table OUT NOCOPY Pa_Otc_Api.Timecard_Table, --2672653
8531: X_Overriding_Approver_Id OUT NOCOPY Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE)
8532:
8533: Is
8534:
8531: X_Overriding_Approver_Id OUT NOCOPY Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE)
8532:
8533: Is
8534:
8535: l_Proj_Attrib_Rec Pa_Otc_Api.Project_Attribution_Rec;
8536: j Binary_Integer := 0;
8537:
8538: l_Time_Building_Blocks Hxc_User_Type_Definition_Grp.Timecard_Info;
8539: l_Time_Attributes Hxc_User_Type_Definition_Grp.App_Attributes_Info;
8544:
8545: Begin
8546:
8547: G_Stage := 'Entering CreateProjTimecardTable() procedure.';
8548: Pa_Otc_Api.TrackPath('ADD','CreateProjTimecardTable');
8549:
8550: G_Stage := 'Initialize PA structure pl/sql table.';
8551:
8552: X_Timecard_Table.delete;
8596: l_Building_Block_Record := l_Time_Building_Blocks(i);
8597: l_Detail_Attr_Changed := 'N';
8598:
8599: G_Stage := 'Pull out the data to PA friendly format by calling RetrieveProjAttribution().';
8600: Pa_Otc_Api.RetrieveProjAttribution(
8601: P_Building_Block_Rec => l_Building_Block_Record,
8602: P_Building_Block => l_Time_Building_Blocks,
8603: P_Attribute_Table => l_Time_Attributes,
8604: X_Detail_Attr_Changed => l_Detail_Attr_Changed,
8640:
8641: End Loop;
8642:
8643: G_Stage := 'Leaving CreateProjTimecardTable() procedure.';
8644: Pa_Otc_Api.TrackPath('STRIP','CreateProjTimecardTable');
8645:
8646: Exception
8647: When Others Then
8648: Raise;
8803: Begin
8804:
8805: G_Stage := 'Entering GetBatchName(), add procedure to trackpath.';
8806: If G_Debug_Mode = 'Y' Then
8807: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8808: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8809: End If;
8810: Pa_Otc_Api.TrackPath('ADD','GetBatchName');
8811:
8806: If G_Debug_Mode = 'Y' Then
8807: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8808: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8809: End If;
8810: Pa_Otc_Api.TrackPath('ADD','GetBatchName');
8811:
8812: If G_EndDateBatchName_Table.Count > 0 Then
8813:
8814: G_Stage := 'There are already batch name recs in pl/sql table. See if find a matching one via loop.';
8812: If G_EndDateBatchName_Table.Count > 0 Then
8813:
8814: G_Stage := 'There are already batch name recs in pl/sql table. See if find a matching one via loop.';
8815: If G_Debug_Mode = 'Y' Then
8816: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8817: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8818: End If;
8819:
8820: For i IN G_EndDateBatchName_Table.First .. G_EndDateBatchName_Table.Last
8830: End Loop;
8831:
8832: G_Stage := 'Done searching for matching batch name record via loop.';
8833: If G_Debug_Mode = 'Y' Then
8834: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8835: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8836: End If;
8837:
8838: End If; -- G_EndDateBatchName_Table.Count > 0
8841:
8842: /* Create a batch Name for Import run.*/
8843: G_Stage := 'Get string of sysdate in YYMMDD format.';
8844: If G_Debug_Mode = 'Y' Then
8845: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8846: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8847: End If;
8848:
8849: Select
8854: Dual;
8855:
8856: G_Stage := 'Use mod(pa_expenditure_groups_s.nextval,1000) to create number value.';
8857: If G_Debug_Mode = 'Y' Then
8858: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8859: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8860: End If;
8861:
8862: SELECT
8867: Dual;
8868:
8869: G_Stage := 'Create a batch Name for Import run.';
8870: If G_Debug_Mode = 'Y' Then
8871: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8872: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8873: End If;
8874:
8875: X_Batch_Name := l_Date_String || '-' || To_Char(l_Sequence_No);
8882: End If; -- Not l_Found_Match
8883:
8884: G_Stage := 'Leaving GetBatchName(), strip procedure from trackpath.';
8885: If G_Debug_Mode = 'Y' Then
8886: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8887: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8888: End If;
8889: Pa_Otc_Api.TrackPath('STRIP','GetBatchName');
8890:
8885: If G_Debug_Mode = 'Y' Then
8886: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8887: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8888: End If;
8889: Pa_Otc_Api.TrackPath('STRIP','GetBatchName');
8890:
8891: Exception
8892: When Others Then
8893: l_debug_text := 'Leaving GetBatchName() due to unhandled exception, strip procedure from trackpath.';
8891: Exception
8892: When Others Then
8893: l_debug_text := 'Leaving GetBatchName() due to unhandled exception, strip procedure from trackpath.';
8894: If G_Debug_Mode = 'Y' Then
8895: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || l_debug_text;
8896: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8897: End If;
8898: Pa_Otc_Api.TrackPath('STRIP','GetBatchName');
8899: Raise;
8894: If G_Debug_Mode = 'Y' Then
8895: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || l_debug_text;
8896: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
8897: End If;
8898: Pa_Otc_Api.TrackPath('STRIP','GetBatchName');
8899: Raise;
8900:
8901: End GetBatchName;
8902:
8967: Begin
8968:
8969: G_Stage := 'Entering GetOrigTrxRef(), add procedure to trackpath.';
8970: If G_Debug_Mode = 'Y' Then
8971: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8972: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
8973: End If;
8974: Pa_Otc_Api.TrackPath('ADD','GetOrigTrxRef');
8975:
8970: If G_Debug_Mode = 'Y' Then
8971: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8972: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
8973: End If;
8974: Pa_Otc_Api.TrackPath('ADD','GetOrigTrxRef');
8975:
8976: G_Stage := 'Build search string.';
8977: If G_Debug_Mode = 'Y' Then
8978: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8974: Pa_Otc_Api.TrackPath('ADD','GetOrigTrxRef');
8975:
8976: G_Stage := 'Build search string.';
8977: If G_Debug_Mode = 'Y' Then
8978: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8979: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
8980: End If;
8981: l_Search := to_char(P_Building_Block_Id) || ':%';
8982:
8981: l_Search := to_char(P_Building_Block_Id) || ':%';
8982:
8983: G_Stage := 'Search ei table for available ei record.';
8984: If G_Debug_Mode = 'Y' Then
8985: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
8986: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,1);
8987: End If;
8988: Select
8989: Orig_Transaction_Reference
9008: And Adjusted_Expenditure_Item_Id is Null);
9009:
9010: G_Stage := 'Leaving GetOrigTrxRef(), strip procedure from trackpath.';
9011: If G_Debug_Mode = 'Y' Then
9012: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9013: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
9014: End If;
9015: Pa_Otc_Api.TrackPath('STRIP','GetOrigTrxRef');
9016:
9011: If G_Debug_Mode = 'Y' Then
9012: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9013: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
9014: End If;
9015: Pa_Otc_Api.TrackPath('STRIP','GetOrigTrxRef');
9016:
9017: Exception
9018: When No_Data_Found Then
9019: -- Data corruption exists if this happens here.
9016:
9017: Exception
9018: When No_Data_Found Then
9019: -- Data corruption exists if this happens here.
9020: l_error_text := SubStr('Pa_Otc_Api ::: ' || G_Path || ' : ' || G_Stage || ' -- ' ||
9021: SqlErrM, 1, 1800);
9022: fnd_message.set_name('HXC', 'HXC_RET_UNEXPECTED_ERROR');
9023: fnd_message.set_token('ERR', l_error_text);
9024: X_Status := SubStr(fnd_message.get,1,2000);
9024: X_Status := SubStr(fnd_message.get,1,2000);
9025:
9026: G_Stage := 'Leaving GetOrigTrxRef() due to unhandled exception, strip procedure from trackpath';
9027: If G_Debug_Mode = 'Y' Then
9028: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9029: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
9030: End If;
9031: Pa_Otc_Api.TrackPath('STRIP','GetOrigTrxRef');
9032:
9027: If G_Debug_Mode = 'Y' Then
9028: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9029: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
9030: End If;
9031: Pa_Otc_Api.TrackPath('STRIP','GetOrigTrxRef');
9032:
9033: If G_Debug_Mode = 'Y' Then
9034: Pa_Debug.G_err_Stage := l_error_text;
9035: pa_cc_utils.log_message(Pa_Debug.G_Err_Stage,0);
9070: Begin
9071:
9072: G_Stage := 'Entering GetAdditionalTrxData(), add procedure to trackpath.';
9073: If G_Debug_Mode = 'Y' Then
9074: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9075: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9076: End If;
9077: Pa_Otc_Api.TrackPath('ADD','GetAdditionalTrxData');
9078:
9073: If G_Debug_Mode = 'Y' Then
9074: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9075: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9076: End If;
9077: Pa_Otc_Api.TrackPath('ADD','GetAdditionalTrxData');
9078:
9079: G_Stage := 'Get Incurred by Organization Id.';
9080: If G_Debug_Mode = 'Y' Then
9081: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9077: Pa_Otc_Api.TrackPath('ADD','GetAdditionalTrxData');
9078:
9079: G_Stage := 'Get Incurred by Organization Id.';
9080: If G_Debug_Mode = 'Y' Then
9081: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9082: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9083: End If;
9084:
9085: Pa_Utils3.GetCachedOrgId( P_Inc_By_Per_Id => P_Person_Id,
9087: X_Inc_By_Org_Id => X_Org_Id);
9088:
9089: G_Stage := 'Incurred by Organization Id is ' || to_char(X_Org_Id) ;
9090: If G_Debug_Mode = 'Y' Then
9091: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9092: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9093: End If;
9094:
9095: X_Error_Status := Null;
9095: X_Error_Status := Null;
9096:
9097: G_Stage := 'Leaving GetAdditionalTrxData(), strip procedure from trackpath.';
9098: If G_Debug_Mode = 'Y' Then
9099: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9100: Pa_Cc_Utils.Log_Message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9101: End If;
9102: Pa_Otc_Api.TrackPath('STRIP','GetAdditionalTrxData');
9103:
9098: If G_Debug_Mode = 'Y' Then
9099: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9100: Pa_Cc_Utils.Log_Message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9101: End If;
9102: Pa_Otc_Api.TrackPath('STRIP','GetAdditionalTrxData');
9103:
9104: Exception
9105: When Others Then
9106: X_Error_Status := 'Could not get Incurred by Organization Id.';
9105: When Others Then
9106: X_Error_Status := 'Could not get Incurred by Organization Id.';
9107: G_Stage := 'Leaving GetAdditionalTrxData() in exception handler.';
9108: If G_Debug_Mode = 'Y' Then
9109: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9110: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9111: End If;
9112: Pa_Otc_Api.TrackPath('STRIP','GetAdditionalTrxData');
9113:
9108: If G_Debug_Mode = 'Y' Then
9109: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9110: pa_cc_utils.log_message('GetAdditionalTrxData: ' || Pa_Debug.G_Err_Stage,0);
9111: End If;
9112: Pa_Otc_Api.TrackPath('STRIP','GetAdditionalTrxData');
9113:
9114: End GetAdditionalTrxData;
9115:
9116:
9142: Begin
9143:
9144: G_Stage := 'Entering BulkInsertReset(), add procedure to trackpath.';
9145: If G_Debug_Mode = 'Y' Then
9146: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9147: pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9148: End If;
9149: Pa_Otc_Api.TrackPath('ADD','BulkInsertReset');
9150:
9145: If G_Debug_Mode = 'Y' Then
9146: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9147: pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9148: End If;
9149: Pa_Otc_Api.TrackPath('ADD','BulkInsertReset');
9150:
9151: If P_Command = 'INSERT' Then
9152:
9153: G_Stage := 'Bulk Insert records into Interface table via Pa_Txn_Interface_Items_Pkg.Bulk_Insert().';
9151: If P_Command = 'INSERT' Then
9152:
9153: G_Stage := 'Bulk Insert records into Interface table via Pa_Txn_Interface_Items_Pkg.Bulk_Insert().';
9154: If G_Debug_Mode = 'Y' Then
9155: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9156: pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9157: End If;
9158:
9159: Pa_Txn_Interface_Items_Pkg.Bulk_Insert(
9265: If P_Command in ( 'INSERT', 'RESET' ) Then
9266:
9267: G_Stage := 'Reset all pl/sql table arrays used for bulk insert.';
9268: If G_Debug_Mode = 'Y' Then
9269: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9270: pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9271: End If;
9272:
9273: G_Txn_Interface_Id_Tbl.Delete;
9379: End If;
9380:
9381: G_Stage := 'Leaving BulkInsertReset(), strip procedure from trackpath.';
9382: If G_Debug_Mode = 'Y' Then
9383: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9384: Pa_Cc_Utils.Log_Message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9385: End If;
9386: Pa_Otc_Api.TrackPath('STRIP','BulkInsertReset');
9387:
9382: If G_Debug_Mode = 'Y' Then
9383: Pa_Debug.G_err_Stage := 'Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage;
9384: Pa_Cc_Utils.Log_Message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
9385: End If;
9386: Pa_Otc_Api.TrackPath('STRIP','BulkInsertReset');
9387:
9388: Exception
9389: When Others Then
9390: Raise;
9571:
9572: End GetPOInfo;
9573:
9574:
9575: END Pa_Otc_Api ;