346: -- P_User_Id: User Id
347:
348: /*--------------------------------------------------------------------------*/
349:
350: Procedure Upload_Otc_Timecards(P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
351: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
352: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
353: P_User_Id IN NUMBER) IS
354:
347:
348: /*--------------------------------------------------------------------------*/
349:
350: Procedure Upload_Otc_Timecards(P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
351: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
352: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
353: P_User_Id IN NUMBER) IS
354:
355: l_Txn_Rowid RowId := Null;
348: /*--------------------------------------------------------------------------*/
349:
350: Procedure Upload_Otc_Timecards(P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
351: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
352: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
353: P_User_Id IN NUMBER) IS
354:
355: l_Txn_Rowid RowId := Null;
356: l_Txn_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE := NULL;
352: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
353: P_User_Id IN NUMBER) IS
354:
355: l_Txn_Rowid RowId := Null;
356: l_Txn_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE := NULL;
357: l_Where_Clause Varchar2(2000);
358: l_Batch_Name Pa_Transaction_Interface_All.Batch_Name%TYPE;
359: l_Override_Approver_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE;
360: l_New_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE;
354:
355: l_Txn_Rowid RowId := Null;
356: l_Txn_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE := NULL;
357: l_Where_Clause Varchar2(2000);
358: l_Batch_Name Pa_Transaction_Interface_All.Batch_Name%TYPE;
359: l_Override_Approver_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE;
360: l_New_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE;
361: l_Old_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE;
362: l_Insert_Rec_Flag Boolean := False;
778:
779: Else
780:
781: -- Need to find out what has changed to determine whether or not need to
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;
1514: -- Pre-Reqs : None
1515: -- Type : Procedure
1516: -- Function : This procedure is called when need to reverse expenditure_item
1517: -- already imported into projects and need to create a record to
1518: -- insert into pa_transaction_interface table for this.
1519: -- Conditions : 1. Deleted the original item
1520: -- called 2. Changed either project,
1521: -- task,
1522: -- expenditure_type,
1526: -- Parameters :
1527: -- IN
1528: -- P_Old_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
1529: -- P_New_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
1530: -- P_Batch_Name Pa_Transaction_Interface_All.Batch_Name%TYPE
1531: -- P_User_Id Pa_Expenditure_Items_All.Last_Updated_By%TYPE
1532: -- P_Orig_Exp_Txn_Reference1 Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE
1533: -- P_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE
1534:
1529: -- P_New_Orig_Trx_Ref Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE
1530: -- P_Batch_Name Pa_Transaction_Interface_All.Batch_Name%TYPE
1531: -- P_User_Id Pa_Expenditure_Items_All.Last_Updated_By%TYPE
1532: -- P_Orig_Exp_Txn_Reference1 Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE
1533: -- P_Xface_Id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE
1534:
1535: /*------------------------------------------------------------------------- */
1536:
1537: Procedure Build_Reverse_Item(
1536:
1537: Procedure Build_Reverse_Item(
1538: P_Old_Orig_Trx_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
1539: P_New_Orig_Trx_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
1540: P_Batch_Name IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
1541: P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE,
1542: P_Orig_Exp_Txn_Reference1 IN Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE,
1543: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE )
1544:
1539: P_New_Orig_Trx_Ref IN Pa_Expenditure_Items_All.Orig_Transaction_Reference%TYPE,
1540: P_Batch_Name IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
1541: P_User_Id IN Pa_Expenditure_Items_All.Last_Updated_By%TYPE,
1542: P_Orig_Exp_Txn_Reference1 IN Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE,
1543: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE )
1544:
1545: Is
1546:
1547: l_txn_rowid RowId := Null;
1544:
1545: Is
1546:
1547: l_txn_rowid RowId := Null;
1548: l_txn_xface_id Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE := Null;
1549:
1550: l_Exp_End_Date Pa_Expenditures_All.Expenditure_Ending_Date%TYPE;
1551: l_Inc_By_Org_Id Hr_Organization_Units.Organization_Id%TYPE;
1552: l_Exp_Item_Date Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE;
1861: -- Type : Procedure
1862: -- Function : This procedure is used to update the expenditure item directly
1863: -- for an item that has not being reversed but ONLY the
1864: -- item comment has been changed and/or the DFF has been changed.
1865: -- No record will be inserted into table pa_transaction_interface.
1866: --
1867: -- Values for parameter P_Comment_or_Dff
1868: -- -------------------------------------
1869: -- C for Comment
2218: -- none
2219: /*--------------------------------------------------------------------------*/
2220:
2221: Procedure Tieback_Otc_Timecards (
2222: P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
2223: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
2224: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
2225: P_User_Id IN Number)
2226:
2219: /*--------------------------------------------------------------------------*/
2220:
2221: Procedure Tieback_Otc_Timecards (
2222: P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
2223: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
2224: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
2225: P_User_Id IN Number)
2226:
2227: Is
2220:
2221: Procedure Tieback_Otc_Timecards (
2222: P_Transaction_Source IN Pa_Transaction_Interface_All.Transaction_Source%TYPE,
2223: P_Batch IN Pa_Transaction_Interface_All.Batch_Name%TYPE,
2224: P_Xface_Id IN Pa_Transaction_Interface_All.Txn_Interface_Id%TYPE,
2225: P_User_Id IN Number)
2226:
2227: Is
2228:
2225: P_User_Id IN Number)
2226:
2227: Is
2228:
2229: Cursor TrxRecords (P_Interface_Id IN Pa_Transaction_Interface_All.Interface_Id%TYPE) Is
2230: Select To_Number(Substr(Orig_Transaction_Reference,1,Instr(Orig_Transaction_Reference,':') - 1)) Detail_BB_Id,
2231: Transaction_Status_Code,
2232: Transaction_Rejection_Code,
2233: Txn_Interface_Id,
2235: Expenditure_Item_Id,
2236: Orig_Transaction_Reference,
2237: Person_Id
2238: From
2239: Pa_Transaction_Interface
2240: Where
2241: Interface_Id = P_Interface_Id
2242: And Transaction_Source = 'ORACLE TIME AND LABOR'
2243: And Transaction_Status_Code in ('I','R')
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
2353: Set
2354: Transaction_Status_Code = 'A'
2355: Where
2356: Interface_Id = P_Xface_Id
2443: -- Pre-Reqs : None
2444: -- Type : Procedure
2445: -- Function : This procedure determines if the expenditure item should be updated directly
2446: -- : or if a reversing entry and a new entry neeed to be inserted into table
2447: -- : pa_transaction_interface_all. This is done by comparing to see what has
2448: -- : actually changed. If only the dff and/or the item comment has changed and
2449: -- : nothing else then the item should be directly updated.
2450: --
2451: -- Parameters :
8783: -- : Used in the Upload_Otc_Timecards() procedure.
8784: --
8785: -- Parameters :
8786: -- IN
8787: -- P_Exp_End_Date - Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE
8788: -- OUT
8789: -- X_Batch_Name - Pa_Transaction_Interface_All.Batch_Name%TYPE
8790: /*--------------------------------------------------------------------------*/
8791:
8785: -- Parameters :
8786: -- IN
8787: -- P_Exp_End_Date - Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE
8788: -- OUT
8789: -- X_Batch_Name - Pa_Transaction_Interface_All.Batch_Name%TYPE
8790: /*--------------------------------------------------------------------------*/
8791:
8792: Procedure GetBatchName (P_Exp_End_Date IN Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE,
8793: X_Batch_Name OUT NOCOPY Pa_Transaction_Interface_All.Batch_Name%TYPE)
8788: -- OUT
8789: -- X_Batch_Name - Pa_Transaction_Interface_All.Batch_Name%TYPE
8790: /*--------------------------------------------------------------------------*/
8791:
8792: Procedure GetBatchName (P_Exp_End_Date IN Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE,
8793: X_Batch_Name OUT NOCOPY Pa_Transaction_Interface_All.Batch_Name%TYPE)
8794:
8795: Is
8796:
8789: -- X_Batch_Name - Pa_Transaction_Interface_All.Batch_Name%TYPE
8790: /*--------------------------------------------------------------------------*/
8791:
8792: Procedure GetBatchName (P_Exp_End_Date IN Pa_Transaction_Interface_All.Expenditure_Ending_Date%TYPE,
8793: X_Batch_Name OUT NOCOPY Pa_Transaction_Interface_All.Batch_Name%TYPE)
8794:
8795: Is
8796:
8797: l_Found_Match Boolean := False;