APPS.PA_TIEBACK_ADJ_COSTS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30

	Select Transaction_Id
	From Rcv_Transactions
	Where (Transaction_Id = P_Parent_Txn_Id OR
	       Parent_Transaction_Id = P_Parent_Txn_Id)
	And Nvl(Pa_Addition_Flag,'N') IN ('Y', 'I');

Line: 69

				G_ParentTxnTab.Delete;

Line: 77

					Select 'Y'
					Into l_Adjusted
					From Dual
					Where Exists
						(Select 1
					 	 From Pa_Expenditure_Items_All Ei,
					      	      Pa_Cost_Distribution_Lines_All Cdl
					 	 Where Nvl(Ei.Adjusted_Expenditure_Item_Id,
									Ei.Transferred_From_Exp_Item_Id) =
												Cdl.Expenditure_Item_Id
					 	 And Nvl(Cdl.System_Reference4,'X') = To_Char(EiRec.Transaction_Id)
					 	 And Cdl.Line_Num = 1);

Line: 198

    |      3. If status is PROCESSED then update transfer status code to 'A',                |
    |         Else if REJECTED then update to 'R'                                            |
    |      4. Write to OUTPUT file how many were rejected/succesfully processed              |
    |      5. Deleting rejected from ap_invoices_interface                                   |
    |      6. Deleting rejected from ap_invoice_lines_interface                              |
    |      7. Commit                                                                         |
    +----------------------------------------------------------------------------------------*/

   Procedure TiebackAdjCosts(X_Return_Status      OUT Varchar2,
                             X_Error_Message_Code OUT Varchar2)

   Is

	Cursor C_TiebackRecs Is
	     Select Cdl.RowId,
	            Cdl.Expenditure_Item_Id,
	            Cdl.Line_Num,
	            Inv.Status
	     From   Pa_Cost_Distribution_Lines Cdl,
	            Ap_Invoices_Interface Inv,
	            Ap_Invoice_Lines_Interface Lines
	     Where  Cdl.Transfer_Status_Code = 'I'
	     And    Inv.Source = 'PA_COST_ADJUSTMENTS'
	     And    Inv.Invoice_Id = Lines.Invoice_Id
	     And    Lines.Reference_2 = To_Char(Cdl.Expenditure_Item_Id) || '-' || To_Char(Cdl.Line_Num);

Line: 249

		l_RowIdTab.Delete;

Line: 250

		l_EiTab.Delete;

Line: 251

		l_LineNumTab.Delete;

Line: 252

		l_InvStatusTab.Delete;

Line: 261

		Pa_Debug.G_Err_Stage := 'Log: No. of records Selected ' || l_RowIdTab.Count;

Line: 279

             		Update  PA_Cost_Distribution_Lines Cdl
                	   Set (Cdl.System_Reference2,
                      		Cdl.System_Reference3,
                      		Cdl.gl_date,
                      		Cdl.gl_period_name) =
                       	       		(Select  Decode(l_InvStatusTab(i),'PROCESSED',
							Dist.Invoice_Id,CDL.System_Reference2),
                          			 Decode(l_InvStatusTab(i),'PROCESSED',
							Dist.Distribution_Line_Number,Cdl.System_Reference3),
                          			 Decode(l_InvStatusTab(i),'PROCESSED',
							Dist.Accounting_Date,Cdl.Gl_Date),
                          			 Decode(l_InvStatusTab(i),'PROCESSED',
							Pa_Utils2.Get_Gl_Period_Name(Dist.Accounting_Date,Dist.Org_Id),
								Cdl.Gl_Period_Name)
                        		From Ap_Invoice_Distributions Dist
                        		Where Dist.Reference_2 = To_Char(Cdl.Expenditure_Item_Id) || '-' ||
												to_char(Cdl.Line_Num)
					And   Pa_Addition_Flag = 'T'),
		      		Cdl.Transfer_Status_Code = Decode(l_InvStatusTab(i), 'PROCESSED','A',
									   	     'REJECTED' ,'R',
											Cdl.Transfer_Status_Code),
                      		Cdl.Request_Id = G_REQUEST_ID,
                      		Cdl.Program_Application_Id = G_PROGRAM_APPLICATION_ID,
                      		Cdl.Program_Id = G_PROGRAM_ID,
                      		Cdl.Program_Update_Date = sysdate
              		Where   Cdl.RowId = l_RowIdTab(i);

Line: 330

	Delete From Ap_Invoice_Lines_Interface
	Where Invoice_Id in (
		Select
			Invoice_Id
		From
			Ap_Invoices_Interface
		Where
			Source = 'PA_COST_ADJUSTMENTS'
		And     Status = 'REJECTED');

Line: 340

	Delete From Ap_Invoices_Interface
	Where Source = 'PA_COST_ADJUSTMENTS'
	And   Status = 'REJECTED';