[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_Trx_Inserted_Tab Pa_Otc_Api.Trx_Inserted_Table;
G_Trx_Direct_Upd_Tab Pa_Otc_Api.Trx_Inserted_Table;
G_Last_Update_Date_Tbl Pa_Txn_Interface_Items_Pkg.Last_Update_Date_Typ;
G_Last_Updated_By_Tbl Pa_Txn_Interface_Items_Pkg.Last_Updated_By_Typ;
G_Delete_op Varchar2(10) :='N';
G_Delete_Change Varchar2(10) :='N';
l_Insert_Rec_Flag Boolean := False;
l_Direct_Update_Flag Boolean := True; /* changed for bug 13831042 */
L_MAX_RECS_FOR_BULKINSERT Number := 1000;
Select
h.Vendor_Id
Into
X_Vendor_Id
from
PO_Headers_All h,
PO_Lines_All l
where
l.po_line_Id = P_Po_Line_Id
and l.po_header_id = h.po_header_Id;
G_Stage := 'Call BulkInsertReset() procedure for reset.';
Pa_Otc_Api.BulkInsertReset(P_Command => 'RESET');
G_EndDateBatchName_Table.Delete;
G_Trx_Inserted_Tab.Delete;
G_Trx_Direct_Upd_Tab.Delete;
Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'Y' Then
G_Stage := 'Item Changed: N, Deleted: Y.';
-- Even though the building block was deleted prior to being imported into projects
-- appearantly attribution data is created and sent via the Generic retrieval process
-- via the Generic retrieval process. Need to sequence thru to position
-- the global index variable for the next bb to process.
-- Since it was deleted means it does not need to be imported into projects.
-- There is also no need to look at the old pl/sql tables records.
G_Stage := 'Call Populate Project Record new 1 for positioning purposes only.';
l_insert_rec_flag := FALSE;
Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'N' Then
-- If this is the condition then there is only new data to process via PopulateProjRec() procedure
-- and there is no need to look at the old data via the same procedure.
G_Stage := 'Item Changed: N, Deleted: N.';
l_insert_rec_flag := FALSE;
-- Insert a new record. There should be no records in either
-- pl/sql tables t_old_detail_bld_blks or t_old_detail_attributes
-- for this building block id.
--
l_insert_rec_flag := TRUE;
l_insert_rec_flag := FALSE;
Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'N' Then
G_Stage := 'Item Changed: Y, Deleted: N.';
l_insert_rec_Flag := FALSE;
l_insert_rec_Flag := FALSE;
-- insert record into pa_transaction_interface or update tables
-- pa_expenditure_comments and/or pa_expenditure_items_all.
G_Stage := 'Call Determine Direct Update 3.';
Pa_Otc_Api.DetermineDirectUpdate(
P_New_Timecard_Rec => l_New_Timecard_Rec,
P_Old_Timecard_Rec => l_Old_Timecard_Rec,
P_Direct_Update_Flag => l_direct_update_flag,
P_Comment_or_Dff => l_Comment_or_Dff);
If l_direct_update_flag Then
l_insert_rec_flag := FALSE;
G_Stage := 'Direct Update of the exp item in projects. ' ||
'Need to update (C)omment, (D)ff, (B)oth: ' || l_Comment_or_Dff;
G_Stage := 'Direct Update get trx ref. by calling GetOrigTrxRef '||
'to use to update the expenditure item.';
G_Stage := 'Update hxc exception pl/sql tables 1.';
G_Stage := 'Call Update Changed Original Txn 3.';
Pa_Otc_Api.UpdateChangedOrigTxn(
P_Old_Orig_Txn_Ref => l_old_orig_trx_ref,
P_New_Orig_Txn_Ref => l_new_orig_trx_ref,
P_Comment_or_Dff => l_comment_or_dff,
P_Timecard_Rec => l_new_timecard_rec,
P_User_Id => P_User_Id);
'in tieback process 3a for directly updated ei.';
l_insert_rec_flag := TRUE;
G_Stage := 'Update hxc exception pl/sql tables 2.';
G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
End If; -- l_direct_update_flag
l_insert_rec_Flag := FALSE;
Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).Deleted = 'Y' Then
-- The item was deleted in OTL after it was imported into Projects.
-- Need to Reverse out the original transaction.
G_Stage := 'Item Changed: Y, Deleted: Y.';
l_insert_rec_flag := FALSE;
G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
End If; -- Change/Delete flags
If l_insert_rec_flag Then
G_Stage := 'Get the next available Transaction Interface Id from sequence.';
select pa_txn_interface_s.nextval
into l_txn_xface_id
from dual;
G_Stage := 'Store Trx Import record in pl/sql arrays for bulk insert.';
G_Last_Update_Date_Tbl(G_Trx_Import_Index) := sysdate;
G_Last_Updated_By_Tbl(G_Trx_Import_Index) := P_User_Id;
G_Trx_Inserted_Tab(Hxc_User_Type_Definition_Grp.T_Detail_Bld_Blks(i).BB_Id).BB_Index := i;
G_Stage := 'Check if need to call bulk insert.';
If G_Txn_Rec_Count >= L_MAX_RECS_FOR_BULKINSERT Then
G_Stage := 'Call BulkInsertReset() procedure for insert.';
Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
G_Stage := 'Store total records inserted. Reset counter to 0.';
G_Stage := 'Exited the loop. Check if need to call bulk insert for any remaining pl/sql records.';
G_Stage := 'Call bulk insert for the remaining pl/sql records.';
Pa_Otc_Api.BulkInsertReset(P_Command => 'INSERT');
G_Stage := 'Call Hxc_Generic_Retrieval_Pkg.Update_Transaction_Status() one last time.';
Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
(P_Process => 'Projects Retrieval Process',
P_Status => 'SUCCESS',
P_Exception_Description => NULL);
Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
(P_Process => 'Projects Retrieval Process',
P_Status => 'ERRORS',
P_Exception_Description => l_Error_Text);
Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status
(P_Process => 'Projects Retrieval Process',
P_Status => 'ERRORS',
P_Exception_Description =>
substr('Pa_Otc_Api ::: ' || G_Path || ' :: ' || G_Stage || ' : ' || SQLERRM, 1, 2000));
P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE,
P_Orig_Exp_Txn_Reference1 IN Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE,
P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE )
Is
l_txn_rowid RowId := Null;
Select
EI.Task_Id,
EI.Project_Id, -- Changed from T.Project_Id
E.Expenditure_Ending_Date,
E.Incurred_By_Organization_Id,
E.Incurred_By_Person_Id,
EI.Expenditure_Item_Date,
EI.Expenditure_Type,
EI.System_Linkage_Function,
-(EI.quantity),
EI.Attribute_Category,
EI.Attribute1,
EI.Attribute2,
EI.Attribute3,
EI.Attribute4,
EI.Attribute5,
EI.Attribute6,
EI.Attribute7,
EI.Attribute8,
EI.Attribute9,
EI.Attribute10,
EI.Assignment_Id,
EI.Work_Type_Id,
EI.Billable_Flag,
PEC.Expenditure_Comment,
-- Begin CWK changes PA.M
EI.PO_Line_Id,
EI.PO_Price_Type,
E.Person_Type,
E.Vendor_Id,
-- End CWK changes PA.M
-- 12i changes
ei.org_id,
-- bug 10253400: Payroll integration enhancement for 12.2..start
ei.job_id,
ei.location_id,
ei.pay_element_type_id
-- bug 10253400: Payroll integration enhancement for 12.2..end
,ei.Cbs_Element_Id -- Added for CBS Enhancement Bug 16220146
Into
l_Task_Id,
l_Proj_Id,
l_Exp_End_Date,
l_Inc_By_Org_Id,
l_Inc_By_Person_Id,
l_Exp_Item_Date,
l_Exp_Type,
l_Sys_Link,
l_Quantity,
l_Attribute_Category,
l_Attribute1,
l_Attribute2,
l_Attribute3,
l_Attribute4,
l_Attribute5,
l_Attribute6,
l_Attribute7,
l_Attribute8,
l_Attribute9,
l_Attribute10,
l_Assignment_Id,
l_Work_Type_Id,
l_Billable_Flag,
l_Exp_Comment,
-- Begin CWK changes PA.M
l_PO_Line_Id,
l_PO_Price_Type,
l_Person_Type,
l_Vendor_Id,
-- End CWK changes PA.M
-- 12i changes
l_org_id,
-- bug 10253400: Payroll integration enhancement for 12.2..start
l_job_id,
l_location_id,
l_pay_element_type_id
-- bug 10253400: Payroll integration enhancement for 12.2..end
,l_Cbs_Element_Id -- Added for CBS Enhancement Bug 16220146
From
Pa_Expenditure_Items EI,
Pa_Expenditure_Comments PEC,
Pa_Expenditures E
-- 3457943 S.N.
--Pa_Tasks T
-- 3457943 E.N.
Where
-- 3457943 S.N.
--T.Task_Id = EI.Task_Id
-- 3457943 E.N.
E.Expenditure_Id = EI.Expenditure_Id
And EI.Expenditure_Item_Id = PEC.Expenditure_Item_Id(+)
And EI.Transaction_Source = 'ORACLE TIME AND LABOR'
And EI.Orig_Transaction_Reference = P_Old_Orig_Trx_Ref
And Nvl(EI.Net_Zero_Adjustment_Flag,'N') = 'N'
And EI.Adjusted_Expenditure_Item_Id is Null;
Select Pa_Txn_Interface_S.NextVal
Into l_Txn_Xface_Id
From Dual;
G_Stage := 'Inserting reversing item into interface table BB/Ovn: ' || P_New_Orig_Trx_Ref ||
' for Resource_Id(Person_Id): ' || to_char(l_Inc_By_Person_Id);
G_Stage := 'Store record for reversing item into arrays for bulk insert later.';
G_Last_Update_Date_Tbl(G_Trx_Import_Index) := SysDate;
G_Last_Updated_By_Tbl(G_Trx_Import_Index) := P_User_Id;
Procedure UpdateChangedOrigTxn(
P_Old_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
P_New_Orig_Txn_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
P_Comment_Or_Dff IN Varchar2,
P_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE)
Is
l_Exp_Item_Id Pa_Expenditure_Items_All.Expenditure_Item_Id%TYPE := Null;
l_Last_Update_Login Pa_Expenditure_Items_All.Last_Update_Login%TYPE := to_Number(Fnd_Profile.Value('LOGIN_ID'));
Select
Count(*)
From
Pa_Expenditure_Comments
Where
Expenditure_Item_Id = P_Ei_Id;
G_Stage := 'Entering UpdateChangedOrigTxn(), add procedure to trackpath.';
Pa_Otc_Api.TrackPath('ADD','UpdateChangedOrigTxn');
Select
RowId,
Expenditure_Item_Id
Into
l_RowId,
l_Exp_Item_Id
From
Pa_Expenditure_Items_All
Where
Transaction_Source = 'ORACLE TIME AND LABOR'
And Orig_Transaction_Reference = P_Old_Orig_Txn_Ref
And Net_Zero_Adjustment_Flag = 'N'; -- Bug 3480159
* faster code due to potentially less updates taking place
* when P_Comment_Or_Dff has a value of B.
*/
G_Stage := 'What updating needs to be done.';
G_Stage := 'P_Comment_Or_Dff Is C: Update ei table.';
Update Pa_Expenditure_Items_All
Set Orig_Transaction_Reference = P_New_Orig_Txn_Ref,
Last_Updated_By = P_User_Id,
Last_Update_Date = SysDate,
Last_Update_Login = l_Last_Update_Login,
Request_id = x_request_id, --Added for bug 4105561
Program_application_id = x_program_application_id,
Program_id = x_program_id,
Program_update_date = sysdate
Where Rowid = l_RowId ;
G_Stage := 'P_Comment_Or_Dff Is C: Insert record into exp comment table.';
Insert into Pa_Expenditure_Comments
( Expenditure_Item_Id,
Line_Number,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Expenditure_Comment,
Last_Update_Login,
Request_Id,
Program_Id,
Program_Application_Id,
Program_Update_Date )
Values (
l_Exp_Item_Id,
10,
SysDate,
P_User_Id,
SysDate,
P_User_Id,
P_Timecard_Rec.Expenditure_Item_Comment,
l_Last_Update_Login,
X_request_id, --Added for bug 4105561
X_program_id,
X_program_application_id,
sysdate);
G_Stage := 'P_Comment_Or_Dff Is C: Update exp comment table.';
Update Pa_Expenditure_Comments
Set Expenditure_Comment = P_Timecard_Rec.Expenditure_Item_Comment,
Last_Updated_By = P_User_Id,
Last_Update_Date = SysDate,
Last_Update_Login = l_Last_Update_Login,
Request_id = x_request_id, --Added for bug 4105561
Program_application_id= x_program_application_id,
Program_id = x_program_id,
Program_update_date = sysdate
Where Expenditure_Item_Id = l_exp_item_id ;
G_Stage := 'P_Comment_Or_Dff Is B: Remove exp comment from table since comment has been updated to null by the user.';
Delete From Pa_Expenditure_Comments
Where Expenditure_Item_Id = l_Exp_Item_Id ;
G_Stage := 'P_Comment_Or_Dff Is D: Update ei table.';
Update Pa_Expenditure_Items_All
Set Orig_Transaction_Reference = P_New_Orig_Txn_Ref,
Attribute_category = P_Timecard_Rec.Attribute_Category,
Attribute1 = P_Timecard_Rec.Attribute1,
Attribute2 = P_Timecard_Rec.Attribute2,
Attribute3 = P_Timecard_Rec.Attribute3,
Attribute4 = P_Timecard_Rec.Attribute4,
Attribute5 = P_Timecard_Rec.Attribute5,
Attribute6 = P_Timecard_Rec.Attribute6,
Attribute7 = P_Timecard_Rec.Attribute7,
Attribute8 = P_Timecard_Rec.Attribute8,
Attribute9 = P_Timecard_Rec.Attribute9,
Attribute10 = P_Timecard_Rec.Attribute10,
Last_Updated_By = P_User_Id,
Last_Update_Date = SysDate,
Last_Update_Login = l_Last_Update_Login,
Request_id = x_request_id,
Program_application_id = x_program_application_id,
Program_id = x_program_id,
Program_update_date = sysdate
Where RowId = l_RowId ;
G_Stage := 'P_Comment_Or_Dff Is P: Update ei table.';
Update Pa_Expenditure_Items_All
Set pay_element_type_id = P_Timecard_Rec.Pay_Element_Type_Id,
Orig_Transaction_Reference = P_New_Orig_Txn_Ref,
Last_Updated_By = P_User_Id,
Last_Update_Date = SysDate,
Last_Update_Login = l_Last_Update_Login,
Request_id = x_request_id,
Program_application_id = x_program_application_id,
Program_id = x_program_id,
Program_update_date = sysdate
Where RowId = l_RowId ;
G_Stage := 'Leaving UpdateChangedOrigTxn(), strip procedure from trackpath.';
Pa_Otc_Api.TrackPath('STRIP','UpdateChangedOrigTxn');
End UpdateChangedOrigTxn;
Select To_Number(Substr(Orig_Transaction_Reference,1,Instr(Orig_Transaction_Reference,':') - 1)) Detail_BB_Id,
Transaction_Status_Code,
Transaction_Rejection_Code,
Txn_Interface_Id,
Expenditure_Id,
Expenditure_Item_Id,
Orig_Transaction_Reference,
Person_Id
From
Pa_Transaction_Interface
Where
Interface_Id = P_Interface_Id
And Transaction_Source = 'ORACLE TIME AND LABOR'
And Transaction_Status_Code in ('I','R')
And Pa_Otc_Api.TrxInCurrentChunk(To_Number(Substr(Orig_Transaction_Reference,1,Instr(Orig_Transaction_Reference,':') - 1))) = 'Y'
Order by 7,3; -- Bug 3355510
G_Stage := 'Update Detail Status to SUCCESS for BB_Id: ' || to_char(TrxRecord.Detail_BB_Id) ||
' Index position is: ' || to_char((G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index));
Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index) :=
'SUCCESS';
G_Stage := 'Update Detail Status to ERRORS for BB_Id: ' || to_char(TrxRecord.Detail_BB_Id) ||
' Index position is: ' || to_char((G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index));
* more than it currently is. We have to make sure that we don't try to update rejected
* Trx records from other loops that have already been ran. They are at a status of 'R'
* So we check to first see if it exists in the pl/sql table and then if it does
* we update the otl pl/sql table accordingly run.
*/
Hxc_User_Type_Definition_Grp.T_Tx_Detail_Status(G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index) := 'ERRORS';
Hxc_User_Type_Definition_Grp.T_Tx_Detail_Exception(G_Trx_Inserted_Tab(TrxRecord.Detail_BB_Id).BB_Index) :=
Substr(Fnd_Message.Get,1,2000);
G_Stage := 'Update the Transaction_Status_Code for successful transactions in interface table.';
Update Pa_Transaction_Interface
Set
Transaction_Status_Code = 'A'
Where
Interface_Id = P_Xface_Id
And Transaction_Status_Code = 'I';
G_Stage := 'Loop thru and flag as success those building blocks where we directly updated the eis.';
* records that were inserted.
* DO NOT CHANGE THIS SO WE DO NOT GET ANY NO_DATA_FOUND ERRORS.
*/
j := G_Trx_Direct_Upd_Tab.Next(j);
G_Stage := 'Update Detail Status to SUCCESS for direct updated ei BB_Id: ' || to_char(j) ||
' Index position is: ' || to_char((G_Trx_Direct_Upd_Tab(j).BB_Index));
G_Stage := 'Call OTL API to update transactions';
Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status (
P_Process => 'Projects Retrieval Process',
P_Status => 'SUCCESS',
P_Exception_Description => NULL);
Hxc_Integration_Layer_V1_Grp.Update_Transaction_Status (
P_Process => 'Projects Retrieval Process',
P_Status => 'ERRORS',
P_Exception_Description => Pa_Debug.G_err_Stage);
Procedure DetermineDirectUpdate(
P_New_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
P_Old_Timecard_Rec IN Pa_Otc_Api.Timecard_Rec,
P_Direct_Update_Flag OUT NOCOPY Boolean,
P_Comment_Or_Dff OUT NOCOPY Varchar2) IS
l_Others_Changed Boolean := False;
P_Direct_Update_Flag := True; /*Added for bug 13831042*/
G_Stage := 'Entering DetermineDirectUpdate(), add procedure to trackpath.';
Pa_Otc_Api.TrackPath('ADD','DetermineDirectUpdate');
G_Stage := 'No Direct Update.';
/* No direct update since other columns have been updated besides
* the comments and DFFs.
*/
P_Direct_Update_Flag := FALSE;
-- Looks like the comment or DFFS where updated in
-- OTL so will do direct updating of the tables instead of
-- using TRX_IMPORT.
--
G_Stage := 'Direct Update.';
P_Direct_Update_Flag := TRUE;
G_Stage := 'Leaving DetermineDirectUpdate(), strip procedure from trackpath.';
Pa_Otc_Api.TrackPath('STRIP','DetermineDirectUpdate');
End DetermineDirectUpdate;
Select Proj_Element_Id, Cbs_Element_Id
Into l_Actual_Task_Id, l_Cbs_Element_Id
From Pa_Alternate_Tasks
Where Alt_Task_Id = P_Timecard_Rec.Task_Id;
Select Proj_Element_Id, Cbs_Element_Id
Into l_Actual_Task_Id, l_Cbs_Element_Id
From Pa_Alternate_Tasks
Where Alt_Task_Id = P_Timecard_Rec.Task_Id;
/* UPDATE and VALIDATION ROUTINES */
-- ========================================================================
-- Start Of Comments
-- API Name : Projects_Retrieval_Process
-- Type : Public
-- Pre-Reqs : None
-- Type : Function
-- Return : Varchar2
-- Function : This function is called to provide the retrieval process name so that the appropriate
-- attribution is retrieved for validation.
--
/*--------------------------------------------------------------------------*/
Function Projects_Retrieval_Process RETURN Varchar2
Is
l_retrieval_process Hxc_Time_Recipients.Application_Retrieval_Function%TYPE;
Procedure Update_Otc_Data
(P_Operation IN Varchar2)
Is
l_Blocks Hxc_User_Type_Definition_Grp.Timecard_Info;
G_Stage := 'Entering Update_Otc_Data() procedure.';
Pa_Otc_Api.TrackPath('ADD','Update_Otc_Data');
G_Stage := 'Call the Upate_Process() to update the billable flag.';
Pa_Otc_Api.Update_Process (
P_Operation => P_Operation,
P_Building_Blocks => l_Blocks,
P_Attribute_Table => l_Attributes);
G_Stage := 'Leaving Update_Otc_Data() procedure.';
Pa_Otc_Api.TrackPath('STRIP','Update_Otc_Data');
End Update_Otc_Data;
Procedure Update_Process(
P_Operation IN Varchar2,
P_Building_Blocks IN OUT NOCOPY Hxc_User_Type_Definition_Grp.Timecard_Info, -- 2672653
P_Attribute_Table IN OUT NOCOPY Hxc_User_Type_Definition_Grp.App_Attributes_Info) -- 2672653
Is
l_Proj_Attrib_Rec Pa_Otc_Api.Project_Attribution_Rec;
l_BB_Detail_Deleted Varchar2(1) := 'N'; -- The OTL item has been deleted.
l_Data_Conflict_Flag Varchar2(1) := 'N'; -- If attempt to delete,update after already saved change or
G_Stage := 'Entering procedure Update_Process().';
Pa_Otc_Api.TrackPath('ADD','Update_Process');
P_Mode => 'UPDATE',
P_Process_Flag => P_Building_Blocks(i).Process,
X_BB_Detail_Changed => l_BB_Detail_Changed,
X_Data_Conflict_Flag => l_Data_Conflict_Flag,
X_BB_Detail_Deleted => l_BB_Detail_Deleted,
X_Adj_in_Projects_Flag => l_Adjusted_In_Projects);
G_Delete_op := l_BB_Detail_Deleted;
G_Delete_Change := l_Logical_Rec_Changed;
If /* l_BB_Detail_Deleted = 'N' and */ -- again commented for 11656282 /* removed the comments for bug 10033549 */--Commented for bug 8546092 / 8284884
(l_BB_Detail_Deleted = 'N' OR (l_BB_Detail_Deleted = 'Y'
and l_Proj_Attrib_Rec.Quantity is null)) and /*Added for bug#13803367 */
(l_BB_Detail_Changed = 'Y' or P_Building_Blocks(i).New = 'Y') and
l_Adjusted_In_Projects = 'N' and
l_Data_Conflict_Flag = 'N' Then
/*Start changes for bug#13803367 */
IF (l_BB_Detail_Deleted = 'Y' and
l_Proj_Attrib_Rec.Quantity is null) THEN
--{
l_calling_module :='PAXVOTCBDEL';
End If; -- Detail building block not deleted and has changed.
G_Stage := 'Leaving procedure Update_Process().';
Pa_Otc_Api.TrackPath('STRIP','Update_Process');
End Update_Process;
l_BB_Detail_Deleted Varchar2(1) := 'N'; -- The OTL item has been deleted.
l_Data_Conflict_Flag Varchar2(1) := 'N'; -- If attempt to delete,update after already saved change or
Select
Meaning
From
Fnd_Lookups
Where
Lookup_Type = 'YES_NO'
And Lookup_Code = P_Lookup_Code;
select max(person_id) from per_all_assignments_f paf,per_assignment_status_types past
where paf.person_id = p_person_id
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN';
Select
'Y'
into l_closed_proj_flag from dual
where exists (select 1
From
Pa_Expenditure_Items_All ei,
pa_projects_all ppa
Where
ei.project_id <> l_Proj_Attrib_Rec.PROJECT_ID and
ei.project_id = ppa.project_id and
nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y' and
ei.Transaction_Source = 'ORACLE TIME AND LABOR' and
ei.Orig_Transaction_Reference like l_bbid
--and ppa.project_status_code = 'CLOSED'
AND PA_PROJECT_UTILS.check_prj_stus_action_allowed(ppa.PROJECT_STATUS_CODE, 'NEW_TXNS') = 'N' --Bug 8532951
);
X_BB_Detail_Deleted => l_BB_Detail_Deleted,
X_Adj_in_Projects_Flag => l_Adjusted_In_Projects);
If l_BB_Detail_Deleted = 'Y' Then
G_Stage := 'Building Block has been deleted.';
G_Stage := 'Not allowed to delete Item In OTL data conflict - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Not allowed to Delete Item In OTL - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
End If; -- l_BB_Detail_Deleted is Y
If l_BB_Detail_Changed = 'Y' and l_BB_Detail_Deleted = 'N' Then
If l_Data_Conflict_Flag = 'Y' Then
-- Really don't need to process this record any further.
-- The timecard cannot be save due to this. Period!
G_Stage := 'Not allowed to Adjust Item In OTL confict - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Not allowed to Adjust Item In OTL - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
End If; -- l_BB_Detail_Changed is Y and l_BB_Detail_Deleted is N
If /* l_BB_Detail_Deleted = 'N' and */ -- again commented for 11656282 /* removed the comments for bug 10033549 */--Commented for bug 8546092 / 8284884
(l_BB_Detail_Deleted = 'N' OR (l_BB_Detail_Deleted = 'Y'
and l_Proj_Attrib_Rec.Quantity is null)) and --Added for bug#13803367
l_Adjusted_In_Projects = 'N' and
( l_BB_Detail_Changed = 'Y' or P_Building_Blocks(i).New = 'Y') Then
/*Added for bug#13803367*/
IF (l_BB_Detail_Deleted = 'Y' and
l_Proj_Attrib_Rec.Quantity is null) THEN
--{
l_calling_module :='PAXVOTCBDEL';
G_Stage := 'Cannot have data where UOM is not HOURS - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Negative quantity not allowed In OTL - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Invalid Person Type - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Project organization does not allow CWK timecards - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Price Type is null - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Derived Vendor Id is null - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Project Number - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Task Number - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Work Type Info - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Job Info - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Location Info - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Validate the exp type and syst link func - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Invalid sys link func - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Get Inc by Org Id - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Msg_Tokens_Table.Delete;
G_Stage := 'Check Job Id - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Patc returned status that is Not Null - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
* in update phase of validation logic and billable no longer
* matches what patc returns.
*/
G_Stage := 'Get translated value for Billable_flag code using fnd_lookups.';
G_Stage := 'Invalid external change of billable flag - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
* in update phase of validation logic and billable no longer
* matches what patc returns.
*/
G_Stage := 'Get translated value for Billable_flag code using fnd_lookups.';
G_Stage := 'Invalid external change of billable flag - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
End If; -- l_Adjusted_In_Projects = 'N' and l_BB_Detail_Changed = 'Y' and l_BB_Detail_Deleted = 'N'
If P_Message_Table.Count = 0 and l_BB_Detail_Deleted = 'N' Then
-- Add ei record to l_Timecard_Table
G_Stage := 'Add ei record to l_Timecard_Table for use by extensions.';
G_Stage := 'Reset variable used for table insert';
G_Stage := 'Calling Summary-validation Extension - Insert Error Rec.';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Calling Business Message API - Insert Business Rec Msg.';
G_Msg_Tokens_Table.Delete;
Select
Project_Number
From
Pa_Online_Projects_V
Where
Project_Id = P_Proj_Id; */
select
p.segment1
from Pa_Online_Projects_V pp,
pa_projects_all p
where pp.Project_Id = P_Proj_Id
and pp.project_id = p.project_id;
Select
Task_Number
From
Pa_Online_Tasks_V
Where
Task_Id = P_Tsk_Id
And Project_Id = P_Prj_Id; */
select
t.task_number
from pa_online_tasks_v tt,
pa_tasks t
where
tt.task_id = P_Tsk_Id
and tt.project_id = P_Prj_Id
and t.task_id = tt.task_id;
Select
System_Linkage_Function,
Expenditure_Type
From
Pa_Online_Expenditure_Types_V
Where
System_Linkage_Function = P_Sys_Link_Func
And Expenditure_Type = P_Exp_Type
And P_Exp_Item_Date Between Sys_Link_Start_Date_Active
And Nvl(Sys_Link_End_Date_Active,P_EI_Date)
And P_Exp_Item_Date Between Expnd_Typ_Start_Date_Active
And Nvl(Expnd_Typ_End_Date_Active,P_EI_Date)
And system_linkage_function in ('ST','OT'); -- bug 5020394
Select
Distinct
Person_Id
Into
X_Approver_Id
From
Pa_Exp_Ovrrde_Approver_V
Where
Person_Id = P_Approver_Id;
select
name
from pa_work_types_v w
where w.work_type_Id = P_Work_Type_Id
and p_Exp_Item_Date between start_date_active and nvl(end_date_active, p_exp_item_date);
select
name
from per_jobs_v w
where w.job_Id = P_job_Id
and p_Exp_Item_Date between date_from and nvl(date_to, p_exp_item_date)
and ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' AND
fnd_profile.value('PER_BUSINESS_GROUP_ID') = W.BUSINESS_GROUP_ID)
OR
PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y');
select l.location_code
from hr_locations l
where location_id = P_Location_Id
and p_exp_item_date <= nvl(l.inactive_date, p_exp_item_date)
and l.location_use = 'HR';
X_BB_Detail_Deleted OUT NOCOPY Varchar2,
X_Adj_in_Projects_Flag OUT NOCOPY Varchar2)
Is
l_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE := Null;
Select
RowId,
Expenditure_Item_Id,
Net_Zero_Adjustment_Flag,
Orig_Transaction_Reference
From
Pa_Expenditure_Items_All
Where
Transaction_Source = 'ORACLE TIME AND LABOR'
And Orig_Transaction_Reference like l_orig_trx_ref
Order By Orig_Transaction_Reference;
Select
'Y'
From
Pa_Expenditure_Items_All ei,
Pa_Expenditure_Comments c
Where
ei.Expenditure_Item_Id = c.Expenditure_Item_Id(+)
And (ei.Task_Id <> nvl(P_Proj_Attribute_Rec.Task_Id,-99)
Or ei.Expenditure_Type <> nvl(P_Proj_Attribute_Rec.Expenditure_Type,'-999999999')
Or ei.System_Linkage_Function <> nvl(P_Proj_Attribute_Rec.Sys_Linkage_Func,'-99')
Or ei.Quantity <> nvl(P_Proj_Attribute_Rec.Quantity,-99)
Or nvl(ei.Attribute_Category,'-99') <> nvl(P_Proj_Attribute_Rec.Attrib_Category,'-99')
Or nvl(ei.Attribute1,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute1,'-99')
Or nvl(ei.Attribute2,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute2,'-99')
Or nvl(ei.Attribute3,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute3,'-99')
Or nvl(ei.Attribute4,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute4,'-99')
Or nvl(ei.Attribute5,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute5,'-99')
Or nvl(ei.Attribute6,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute6,'-99')
Or nvl(ei.Attribute7,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute7,'-99')
Or nvl(ei.Attribute8,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute8,'-99')
Or nvl(ei.Attribute9,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute9,'-99')
Or nvl(ei.Attribute10,'-99') <> nvl(P_Proj_Attribute_Rec.Attribute10,'-99')
Or nvl(c.Expenditure_Comment,'-99') <> nvl(P_Proj_Attribute_Rec.Expenditure_Item_Comment,'-99')
Or nvl(ei.PO_Line_Id,-99) <> nvl(P_Proj_Attribute_Rec.PO_Line_Id,-99)
Or nvl(ei.PO_Price_Type,'-99') <> nvl(P_Proj_Attribute_Rec.PO_Price_Type,'-99')
-- Bug 10253400.. 12.2 payroll intg .. start
Or nvl(ei.job_id, -99) <> nvl(P_Proj_Attribute_Rec.Job_Id, nvl(ei.job_id, -99))
Or nvl(ei.location_id, -99) <> nvl(P_Proj_Attribute_Rec.Location_Id, nvl(ei.location_id, -99))
Or nvl(ei.pay_element_type_id, -99) <> nvl(P_Proj_Attribute_Rec.Pay_Element_Type_Id, nvl(ei.pay_element_type_id, -99))
-- Bug 10253400.. 12.2 payroll intg .. end
Or nvl(ei.Cbs_Element_Id, -99) <> nvl(P_Proj_Attribute_Rec.Cbs_Element_Id, nvl(ei.Cbs_Element_Id, -99))) -- Added for CBS Enhancement Bug 16220146
And ei.RowId = P_RowId;
X_BB_Detail_Deleted := 'N';
G_Stage := 'Check for deleted detail bb.';
G_Stage := 'Detail bb block has been deleted.';
X_BB_Detail_Deleted := 'Y';
-- If an item has been deleted in OTL then P_BB_Changed = Y as well.
G_Stage := 'Check and setting the ovn value to scan Project ei table with';
ElsIf X_BB_Detail_Deleted = 'Y' Then
G_Stage := 'The detail scope bb is being deleted.';
X_BB_Detail_Deleted = 'N' Then
G_Stage := 'Only Ovn has changed. Update the orig_transaction_reference of the ei.';
Update Pa_Expenditure_Items_all --for Bug# 13519235
Set
Orig_Transaction_Reference = To_Char(P_BB_Id) || ':' || To_Char(l_Ovn_Check_Value),
last_update_date = sysdate,
last_updated_by = to_Number(Fnd_Profile.Value('USER_ID')),
last_update_login = to_Number(Fnd_Profile.Value('LOGIN_ID'))
Where
RowId = l_RowId;
select project_id , task_id, expenditure_type,
job_id, location_id, pay_element_type_id, cbs_element_id
into l_Proj_Id , l_task_id , l_Exp_Type,
l_job_id, l_location_id, l_pay_element_type_id, l_Cbs_Element_Id
from Pa_Expenditure_Items_all --for Bug# 13519235
where RowId = l_RowId;
/* Bug 2283011 The changed_flag is coming in as 'N' for deleted items not 'Y' so
we don't need to consider it value then.
ElsIf P_BB_Changed = 'Y' and */
ElsIf X_BB_Detail_Deleted = 'Y' and
l_Net_Zero_flag = 'Y' Then
G_Stage := 'BB/EI has been already changed in Projects, so cannot delete.';
If P_BB_Changed = 'N' and X_BB_Detail_Deleted = 'N' Then
G_Stage := 'Data conflict exists.';
Select Proj_Element_Id, Cbs_Element_Id
Into l_Actual_Task_Id, l_Cbs_Element_Id
From Pa_Alternate_Tasks
Where Alt_Task_Id = X_Proj_Attrib_Rec.Task_Id;
Select Proj_Element_Id, Cbs_Element_Id
Into l_Actual_Task_Id, l_Cbs_Element_Id
From Pa_Alternate_Tasks
Where Alt_Task_Id = X_Proj_Attrib_Rec.Task_Id;
select mc.segment
Into G_Billable_Segment
from hxc_mapping_components mc,
hxc_bld_blk_info_types bbit
where mc.field_name = 'BILLABLE_FLAG'
and bbit.bld_blk_info_type_id = mc.bld_blk_info_type_id
and bbit.bld_blk_info_type = 'PROJECTS';
P_Attribute_Table(billable_index).updated := 'N';
Select
FI.Project_Id,
P.Segment1,
FI.Item_Date,
FI.Expenditure_Type,
FI.Expenditure_Type_Class,
FI.Item_Quantity
From
Pa_Forecast_Items FI,
Pa_Projects_All P,
Pa_Project_Assignments PPA
Where
Person_Id = P_Emp_Id
And Item_Date Between P_Start_Date
And P_Stop_Date
And Forecast_Item_Type in ('A','U')
And FI.Project_Id = P.Project_Id
And PPA.assignment_id = FI.assignment_id
And FI.delete_flag = 'N'
And Nvl(FI.expenditure_org_id,-99) = Nvl(P_Exp_Org_Id,-99)
And Nvl(FI.Item_Quantity,0) <> 0
Order by 2,4,3; -- project/exp_type/item_date
Select
FI.Project_Id,
P.Segment1,
FI.Item_Date,
FI.Expenditure_Type,
FI.Expenditure_Type_Class,
FI.Item_Quantity
From
Pa_Forecast_Items FI,
Pa_Projects_All P
Where
Person_Id = P_Emp_Id
And Item_Date Between P_Start_Date
And P_Stop_Date
And Forecast_Item_Type = 'A'
And FI.Project_Id = P.Project_Id
And FI.delete_flag = 'N'
And FI.expenditure_org_id = P_Exp_Org_Id
And FI.Item_Quantity <> 0
Order by 2,4,3; -- project/exp_type/item_date
Select
FI.Project_Id,
P.Segment1,
FI.Item_Date,
FI.Expenditure_Type,
FI.Expenditure_Type_Class,
FI.Item_Quantity
From
Pa_Forecast_Items FI,
Pa_Projects_All P,
Pa_Resource_Txn_Attributes PTA
Where
PTA.Person_Id = P_Emp_Id
And FI.Resource_id = PTA.Resource_id
And FI.Item_Date Between P_Start_Date
And P_Stop_Date
And FI.Forecast_Item_Type = 'A'
And FI.Project_Id = P.Project_Id
And FI.delete_flag = 'N'
And FI.expenditure_org_id = P_Exp_Org_Id
And FI.Item_Quantity <> 0
Order by 2,4,3; -- project/exp_type/item_date
-- Select
-- 'Y'
-- From
-- Pa_Online_Projects_V
-- Where
-- Project_Id = P_Project_Id;
l_Message_Table.Delete;
l_Building_Blocks_Table.Delete;
l_Attribute_Table.Delete;
l_dummy_bb_Table.Delete;
l_dummy_Attrib_Table.Delete;
G_Msg_Tokens_Table.Delete;
-- Insert Timecard Scope record
G_Stage := 'Build/Insert Timecard Scope BB record.';
-- Insert Day Scope records.
G_Stage := 'Build/Insert day scope records using loop based on the number of days in timecard period.';
G_Stage := 'Building and insert detail scope record.';
G_Stage := 'Building and insert detail scope attribution records.';
G_Stage := 'Building and insert detail scope attribution record project_Id.';
l_Attribute_Table(l_Attrib_Index).Updated := 'N';
G_Stage := 'Building and insert detail scope attribution record Expenditure Type.';
l_Attribute_Table(l_Attrib_Index).Updated := 'N';
G_Stage := 'Building and insert detail scope attribution record Expenditure_Type_Class.';
l_Attribute_Table(l_Attrib_Index).Updated := 'N';
G_Msg_Tokens_Table.Delete;
select hrp.period_type
from hxc_recurring_periods hrp
where hrp.recurring_period_id = p_rec_id;
Select max(effective_end_date) from per_people_f
where person_id = p_person_id and
effective_end_date <= p_stop_date and
(current_employee_flag = 'Y' or current_npw_flag = 'Y');
Select '1' from per_assignments_f where
person_id = p_person_id and
trunc(p_stop_date) + 1 between effective_start_date and effective_end_date
and assignment_status_type_id in (select assignment_status_type_id
from PER_ASSIGNMENT_STATUS_TYPES where per_system_status = 'SUSP_ASSIGN');
IF (G_Detail_Change = 'N' and G_Delete_Change = 'Y') Then
X_Pass_Val_Flag := 'N';
G_Msg_Tokens_Table.Delete;
G_Stage := 'Validate Overriding Approver - Inserting error rec.';
G_Msg_Tokens_Table.Delete;
G_Msg_Tokens_Table.Delete;
l_Timecard_Table.delete;
l_Message_Table.delete;
G_Msg_Tokens_Table.Delete;
G_Msg_Tokens_Table.Delete;
l_Timecard_Table.Delete;
G_Msg_Tokens_Table.Delete;
G_Msg_Tokens_Table.Delete;
G_Msg_Tokens_Table.Delete;
X_Timecard_Table.delete;
Select
distinct 'Y' -- Bug#9374217: Added distinct clause to avoid the ORA-1422 error.
Into
l_Exists_Flag
From
Pa_Expenditure_Items_All
Where
Transaction_Source = 'ORACLE TIME AND LABOR'
And Orig_Transaction_Reference = P_Orig_Transaction_Reference;
Select
To_Char(P_Exp_End_Date,'YYMMDD')
Into
l_Date_String
From
Dual;
SELECT
Mod(Pa_Expenditure_Groups_S.NextVal,1000)
INTO
l_Sequence_No
FROM
Dual;
Select
Orig_Transaction_Reference
Into
X_OrigTrxRef
From
Pa_Expenditure_Items_All
Where
Orig_Transaction_Reference like l_Search
And Transaction_Source = 'ORACLE TIME AND LABOR'
And Nvl(Net_Zero_Adjustment_Flag,'N') = 'N'
And Adjusted_Expenditure_Item_Id is Null
And to_number(substr(Orig_Transaction_Reference,instr(Orig_Transaction_Reference,':') + 1)) = (
Select
Max(to_Number(Substr(Orig_Transaction_Reference,instr(Orig_Transaction_Reference,':') + 1)))
From
Pa_Expenditure_Items_All
Where
Orig_Transaction_Reference like l_Search
And Transaction_Source = 'ORACLE TIME AND LABOR'
And Nvl(Net_Zero_Adjustment_Flag,'N') = 'N'
And Adjusted_Expenditure_Item_Id is Null);
Procedure BulkInsertReset (P_Command IN Varchar2)
Is
Begin
G_Stage := 'Entering BulkInsertReset(), add procedure to trackpath.';
pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
Pa_Otc_Api.TrackPath('ADD','BulkInsertReset');
If P_Command = 'INSERT' Then
G_Stage := 'Bulk Insert records into Interface table via Pa_Txn_Interface_Items_Pkg.Bulk_Insert().';
pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
Pa_Txn_Interface_Items_Pkg.Bulk_Insert(
P_Txn_Interface_Id_Tbl => G_Txn_Interface_Id_Tbl,
P_Transaction_Source_Tbl => G_Transaction_Source_Tbl,
P_User_Transaction_Source_Tbl => G_User_Transaction_Source_Tbl,
P_Batch_Name_Tbl => G_Batch_Name_Tbl,
P_Expenditure_End_Date_Tbl => G_Expenditure_End_Date_Tbl,
P_Person_Bus_Grp_Name_Tbl => G_Person_Bus_Grp_Name_Tbl,
P_Person_Bus_Grp_Id_Tbl => G_Person_Bus_Grp_Id_Tbl,
P_Employee_Number_Tbl => G_Employee_Number_Tbl,
P_Person_Id_Tbl => G_Person_Id_Tbl,
P_Organization_Name_Tbl => G_Organization_Name_Tbl,
P_Organization_Id_Tbl => G_Organization_Id_Tbl,
P_Expenditure_Item_Date_Tbl => G_Expenditure_Item_Date_Tbl,
P_Project_Number_Tbl => G_Project_Number_Tbl,
P_Project_Id_Tbl => G_Project_Id_Tbl,
P_Task_Number_Tbl => G_Task_Number_Tbl,
P_Task_Id_Tbl => G_Task_Id_Tbl,
P_Expenditure_Type_Tbl => G_Expenditure_Type_Tbl,
P_System_Linkage_Tbl => G_System_Linkage_Tbl,
P_Non_Labor_Resource_Tbl => G_Non_Labor_Resource_Tbl,
P_Non_Labor_Res_Org_Name_Tbl => G_Non_Labor_Res_Org_Name_Tbl,
P_Non_Labor_Res_Org_Id_Tbl => G_Non_Labor_Res_Org_Id_Tbl,
P_Quantity_Tbl => G_Quantity_Tbl,
P_Raw_Cost_Tbl => G_Raw_Cost_Tbl,
P_Raw_Cost_Rate_Tbl => G_Raw_Cost_Rate_Tbl,
P_Burden_Cost_Tbl => G_Burden_Cost_Tbl,
P_Burden_Cost_Rate_Tbl => G_Burden_Cost_Rate_Tbl,
P_Expenditure_Comment_Tbl => G_Expenditure_Comment_Tbl,
P_Gl_Date_Tbl => G_Gl_Date_Tbl,
P_Transaction_Status_Code_Tbl => G_Transaction_Status_Code_Tbl,
P_Trans_Rejection_Code_Tbl => G_Trans_Rejection_Code_Tbl,
P_Orig_Trans_Reference_Tbl => G_Orig_Trans_Reference_Tbl,
P_Unmatched_Neg_Txn_Flag_Tbl => G_Unmatched_Neg_Txn_Flag_Tbl,
P_Expenditure_Id_Tbl => G_Expenditure_Id_Tbl,
P_Attribute_Category_Tbl => G_Attribute_Category_Tbl,
P_Attribute1_Tbl => G_Attribute1_Tbl,
P_Attribute2_Tbl => G_Attribute2_Tbl,
P_Attribute3_Tbl => G_Attribute3_Tbl,
P_Attribute4_Tbl => G_Attribute4_Tbl,
P_Attribute5_Tbl => G_Attribute5_Tbl,
P_Attribute6_Tbl => G_Attribute6_Tbl,
P_Attribute7_Tbl => G_Attribute7_Tbl,
P_Attribute8_Tbl => G_Attribute8_Tbl,
P_Attribute9_Tbl => G_Attribute9_Tbl,
P_Attribute10_Tbl => G_Attribute10_Tbl,
P_Dr_Code_Combination_Id_Tbl => G_Dr_Code_Combination_Id_Tbl,
P_Cr_Code_Combination_Id_Tbl => G_Cr_Code_Combination_Id_Tbl,
P_Cdl_System_Reference1_Tbl => G_Cdl_System_Reference1_Tbl,
P_Cdl_System_Reference2_Tbl => G_Cdl_System_Reference2_Tbl,
P_Cdl_System_Reference3_Tbl => G_Cdl_System_Reference3_Tbl,
P_Interface_Id_Tbl => G_Interface_Id_Tbl,
P_Receipt_Currency_Amount_Tbl => G_Receipt_Currency_Amount_Tbl,
P_Receipt_Currency_Code_Tbl => G_Receipt_Currency_Code_Tbl,
P_Receipt_Exchange_Rate_Tbl => G_Receipt_Exchange_Rate_Tbl,
P_Denom_Currency_Code_Tbl => G_Denom_Currency_Code_Tbl,
P_Denom_Raw_Cost_Tbl => G_Denom_Raw_Cost_Tbl,
P_Denom_Burdened_Cost_Tbl => G_Denom_Burdened_Cost_Tbl,
P_Acct_Rate_Date_Tbl => G_Acct_Rate_Date_Tbl,
P_Acct_Rate_Type_Tbl => G_Acct_Rate_Type_Tbl,
P_Acct_Exchange_Rate_Tbl => G_Acct_Exchange_Rate_Tbl,
P_Acct_Raw_Cost_Tbl => G_Acct_Raw_Cost_Tbl,
P_Acct_Burdened_Cost_Tbl => G_Acct_Burdened_Cost_Tbl,
P_Acct_Exch_Rounding_Limit_Tbl => G_Acct_Exch_Rounding_Limit_Tbl,
P_Project_Currency_Code_Tbl => G_Project_Currency_Code_Tbl,
P_Project_Rate_Date_Tbl => G_Project_Rate_Date_Tbl,
P_Project_Rate_Type_Tbl => G_Project_Rate_Type_Tbl,
P_Project_Exchange_Rate_Tbl => G_Project_Exchange_Rate_Tbl,
P_Orig_Exp_Txn_Reference1_Tbl => G_Orig_Exp_Txn_Reference1_Tbl,
P_Orig_Exp_Txn_Reference2_Tbl => G_Orig_Exp_Txn_Reference2_Tbl,
P_Orig_Exp_Txn_Reference3_Tbl => G_Orig_Exp_Txn_Reference3_Tbl,
P_Orig_User_Exp_Txn_Ref_Tbl => G_Orig_User_Exp_Txn_Ref_Tbl,
P_Vendor_Number_Tbl => G_Vendor_Number_Tbl,
P_Vendor_Id_Tbl => G_Vendor_Id_Tbl,
P_Override_To_Org_Name_Tbl => G_Override_To_Org_Name_Tbl,
P_Override_To_Org_Id_Tbl => G_Override_To_Org_Id_Tbl,
P_Reversed_Orig_Txn_Ref_Tbl => G_Reversed_Orig_Txn_Ref_Tbl,
P_Billable_Flag_Tbl => G_Billable_Flag_Tbl,
P_ProjFunc_Currency_Code_Tbl => G_ProjFunc_Currency_Code_Tbl,
P_ProjFunc_Cost_Rate_Date_Tbl => G_ProjFunc_Cost_Rate_Date_Tbl,
P_ProjFunc_Cost_Rate_Type_Tbl => G_ProjFunc_Cost_Rate_Type_Tbl,
P_ProjFunc_Cost_Exch_Rate_Tbl => G_ProjFunc_Cost_Exch_Rate_Tbl,
P_Project_Raw_Cost_Tbl => G_Project_Raw_Cost_Tbl,
P_Project_Burdened_Cost_Tbl => G_Project_Burdened_Cost_Tbl,
P_Assignment_Name_Tbl => G_Assignment_Name_Tbl,
P_Assignment_Id_Tbl => G_Assignment_Id_Tbl,
P_Work_Type_Name_Tbl => G_Work_Type_Name_Tbl,
P_Work_Type_Id_Tbl => G_Work_Type_Id_Tbl,
P_Cdl_System_Reference4_Tbl => G_Cdl_System_Reference4_Tbl,
P_Accrual_Flag_Tbl => G_Accrual_Flag_Tbl,
P_Last_Update_Date_Tbl => G_Last_Update_Date_Tbl,
P_Last_Updated_By_Tbl => G_Last_Updated_By_Tbl,
P_Creation_Date_Tbl => G_Creation_Date_Tbl,
P_Created_By_Tbl => G_Created_By_Tbl,
P_PO_Number_Tbl => G_PO_Number_Tbl,
P_PO_Header_Id_Tbl => G_PO_Header_Id_Tbl,
P_PO_Line_Num_Tbl => G_PO_Line_Num_Tbl,
P_PO_Line_Id_Tbl => G_PO_Line_Id_Tbl,
P_PO_Price_Type_Tbl => G_PO_Price_Type_Tbl,
P_Person_Type_Tbl => G_Person_Type_Tbl,
P_Inventory_Item_Id_Tbl => G_Inventory_Item_Id_Tbl,
P_WIP_Resource_Id_Tbl => G_WIP_Resource_Id_Tbl,
P_Unit_Of_Measure_Tbl => G_Unit_Of_Measure_Tbl,
P_Org_Id_Tbl => G_OU_Tbl,
-- Bug 10253400.. 12.2 payroll intg .. start
P_Job_Id_Tbl => G_Job_Id_Tbl,
p_Job_Name_Tbl => G_Job_Name_Tbl,
p_location_Code_tbl => G_Location_Code_Tbl,
p_pay_element_Type_Id_tbl => G_Pay_Element_Type_Id_Tbl,
P_Location_Id_Tbl => G_Location_ID_Tbl
-- Bug 10253400.. 12.2 payroll intg .. end
-- Start: Added for CBS Enhancement Bug 16220146
,P_Cbs_Element_Id_Tbl => G_Cbs_Element_Id_Tbl
,P_Cbs_Element_Name_Tbl => G_Cbs_Element_Name_Tbl
-- End: Added for CBS Enhancement Bug 16220146
);
If P_Command in ( 'INSERT', 'RESET' ) Then
G_Stage := 'Reset all pl/sql table arrays used for bulk insert.';
pa_cc_utils.log_message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
G_Txn_Interface_Id_Tbl.Delete;
G_Transaction_Source_Tbl.Delete;
G_User_Transaction_Source_Tbl.Delete;
G_Batch_Name_Tbl.Delete;
G_Expenditure_End_Date_Tbl.Delete;
G_Person_Bus_Grp_Name_Tbl.Delete;
G_Person_Bus_Grp_Id_Tbl.Delete;
G_Employee_Number_Tbl.Delete;
G_Person_Id_Tbl.Delete;
G_Organization_Name_Tbl.Delete;
G_Organization_Id_Tbl.Delete;
G_Expenditure_Item_Date_Tbl.Delete;
G_Project_Number_Tbl.Delete;
G_Project_Id_Tbl.Delete;
G_Task_Number_Tbl.Delete;
G_Task_Id_Tbl.Delete;
G_Expenditure_Type_Tbl.Delete;
G_System_Linkage_Tbl.Delete;
G_Non_Labor_Resource_Tbl.Delete;
G_Non_Labor_Res_Org_Name_Tbl.Delete;
G_Non_Labor_Res_Org_Id_Tbl.Delete;
G_Quantity_Tbl.Delete;
G_Raw_Cost_Tbl.Delete;
G_Raw_Cost_Rate_Tbl.Delete;
G_Burden_Cost_Tbl.Delete;
G_Burden_Cost_Rate_Tbl.Delete;
G_Expenditure_Comment_Tbl.Delete;
G_Gl_Date_Tbl.Delete;
G_Transaction_Status_Code_Tbl.Delete;
G_Trans_Rejection_Code_Tbl.Delete;
G_Orig_Trans_Reference_Tbl.Delete;
G_Unmatched_Neg_Txn_Flag_Tbl.Delete;
G_Expenditure_Id_Tbl.Delete;
G_Attribute_Category_Tbl.Delete;
G_Attribute1_Tbl.Delete;
G_Attribute2_Tbl.Delete;
G_Attribute3_Tbl.Delete;
G_Attribute4_Tbl.Delete;
G_Attribute5_Tbl.Delete;
G_Attribute6_Tbl.Delete;
G_Attribute7_Tbl.Delete;
G_Attribute8_Tbl.Delete;
G_Attribute9_Tbl.Delete;
G_Attribute10_Tbl.Delete;
G_Dr_Code_Combination_Id_Tbl.Delete;
G_Cr_Code_Combination_Id_Tbl.Delete;
G_Cdl_System_Reference1_Tbl.Delete;
G_Cdl_System_Reference2_Tbl.Delete;
G_Cdl_System_Reference3_Tbl.Delete;
G_Interface_Id_Tbl.Delete;
G_Receipt_Currency_Amount_Tbl.Delete;
G_Receipt_Currency_Code_Tbl.Delete;
G_Receipt_Exchange_Rate_Tbl.Delete;
G_Denom_Currency_Code_Tbl.Delete;
G_Denom_Raw_Cost_Tbl.Delete;
G_Denom_Burdened_Cost_Tbl.Delete;
G_Acct_Rate_Date_Tbl.Delete;
G_Acct_Rate_Type_Tbl.Delete;
G_Acct_Exchange_Rate_Tbl.Delete;
G_Acct_Raw_Cost_Tbl.Delete;
G_Acct_Burdened_Cost_Tbl.Delete;
G_Acct_Exch_Rounding_Limit_Tbl.Delete;
G_Project_Currency_Code_Tbl.Delete;
G_Project_Rate_Date_Tbl.Delete;
G_Project_Rate_Type_Tbl.Delete;
G_Project_Exchange_Rate_Tbl.Delete;
G_Orig_Exp_Txn_Reference1_Tbl.Delete;
G_Orig_Exp_Txn_Reference2_Tbl.Delete;
G_Orig_Exp_Txn_Reference3_Tbl.Delete;
G_Orig_User_Exp_Txn_Ref_Tbl.Delete;
G_Vendor_Number_Tbl.Delete;
G_Vendor_Id_Tbl.Delete;
G_Override_To_Org_Name_Tbl.Delete;
G_Override_To_Org_Id_Tbl.Delete;
G_Reversed_Orig_Txn_Ref_Tbl.Delete;
G_Billable_Flag_Tbl.Delete;
G_ProjFunc_Currency_Code_Tbl.Delete;
G_ProjFunc_Cost_Rate_Date_Tbl.Delete;
G_ProjFunc_Cost_Rate_Type_Tbl.Delete;
G_ProjFunc_Cost_Exch_Rate_Tbl.Delete;
G_Project_Raw_Cost_Tbl.Delete;
G_Project_Burdened_Cost_Tbl.Delete;
G_Assignment_Name_Tbl.Delete;
G_Assignment_Id_Tbl.Delete;
G_Work_Type_Name_Tbl.Delete;
G_Work_Type_Id_Tbl.Delete;
G_Cdl_System_Reference4_Tbl.Delete;
G_Accrual_Flag_Tbl.Delete;
G_Last_Update_Date_Tbl.Delete;
G_Last_Updated_By_Tbl.Delete;
G_Creation_Date_Tbl.Delete;
G_Created_By_Tbl.Delete;
G_PO_Number_Tbl.Delete;
G_PO_Header_Id_Tbl.Delete;
G_PO_Line_Num_Tbl.Delete;
G_PO_Line_Id_Tbl.Delete;
G_PO_Price_Type_Tbl.Delete;
G_Person_Type_Tbl.Delete;
G_Inventory_Item_Id_Tbl.Delete;
G_WIP_Resource_Id_Tbl.Delete;
G_Unit_Of_Measure_Tbl.Delete;
G_OU_Tbl.Delete;
G_Job_Id_Tbl.delete;
G_Location_Id_Tbl.delete;
G_Job_Name_Tbl.delete;
G_Location_Code_Tbl.delete;
G_Pay_Element_Type_Id_Tbl.delete;
G_Cbs_Element_Id_Tbl.delete;
G_Cbs_Element_Name_Tbl.delete;
G_Stage := 'Leaving BulkInsertReset(), strip procedure from trackpath.';
Pa_Cc_Utils.Log_Message('BulkInsertReset: ' || Pa_Debug.G_Err_Stage,0);
Pa_Otc_Api.TrackPath('STRIP','BulkInsertReset');
End BulkInsertReset;
If G_Trx_Inserted_Tab.Exists(P_Detail_BB_Id) Then
Return ( 'Y' );
Select
Decode(Current_Npw_Flag,'Y','CWK','EMP')
Into
X_Person_Type
From
Per_People_F
Where
P_Ei_Date between Effective_Start_Date
and Effective_End_Date
And Person_Id = P_Person_Id;
Select
h.Vendor_Id,
h.Po_Header_Id
Into
l_Vendor_Id,
l_Po_Header_Id
from
PO_Headers_All h,
PO_Lines_All l
where
l.po_line_Id = P_Po_Line_Id
and l.po_header_id = h.po_header_Id;