eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_CI_DIR_COST_PUB SQL Statements

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

Line: 11


procedure insert_row(
    p_api_version                  IN NUMBER,
    p_init_msg_list                IN VARCHAR2 DEFAULT FND_API.G_FALSE,
    x_return_status                OUT NOCOPY VARCHAR2,
    x_msg_count                    OUT NOCOPY NUMBER,
    x_msg_data                     OUT NOCOPY VARCHAR2,
    p_bvid                         IN NUMBER,
    p_dc_line_id_tbl               IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_ci_id                        IN NUMBER,
    p_project_id                   IN NUMBER,
    p_task_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_expenditure_type_tbl         IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_rlmi_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_unit_of_measure_tbl          IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_currency_code_tbl            IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_planning_resource_rate_tbl   IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_quantity_tbl                 IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_raw_cost_tbl                 IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_burdened_cost_tbl            IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_raw_cost_rate_tbl            IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_burden_cost_rate_tbl         IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_resource_assignment_id_tbl   IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
    p_effective_from_tbl           IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(),
    p_effective_to_tbl             IN SYSTEM.PA_DATE_TBL_TYPE DEFAULT SYSTEM.PA_DATE_TBL_TYPE(),
    p_change_reason_code           IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
    p_change_description           IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_2000_TBL_TYPE()) IS

 l_api_version	     number := 1;

Line: 39


 l_api_name          CONSTANT VARCHAR2(30) := 'PUB.insert_row';

Line: 51


select prac.resource_assignment_id,
       planning_start_date,
       planning_end_date,
       prac.txn_average_raw_cost_rate planning_resource_rate,
       prac.txn_average_burden_cost_rate burden_cost_rate
  from pa_resource_assignments pra, pa_resource_asgn_curr prac
 where pra.budget_version_id = c_bvId
   and pra.task_id = c_task_id
   and pra.resource_list_member_id = c_rlmi
   and prac.resource_assignment_id = pra.resource_assignment_id;

Line: 64


   savepoint pub_insert_row;

Line: 71


	   select pa_ci_dir_cost_details_s.nextval
	     into l_dc_line_id_tbl(i)
		 from dual;

Line: 107


        p_resource_assignment_id_tbl are not available during insert
       */

       l_PaCiDirCostDetTbl(i).burdened_cost            := NULL;

Line: 133


       l_PaCiDirCostDetTbl(i).last_update_date         := sysdate;

Line: 134


       l_PaCiDirCostDetTbl(i).last_update_by           := FND_GLOBAL.USER_ID;

Line: 135


       l_PaCiDirCostDetTbl(i).last_update_login        := FND_GLOBAL.LOGIN_ID;

Line: 138


      pa_ci_dir_cost_pvt.insert_row(
        p_api_version                  => l_api_version,
        p_init_msg_list                => FND_API.G_FALSE,
        x_return_status                => l_return_status,
        x_msg_count                    => l_msg_count,
        x_msg_data                     => l_msg_data,
        PPaCiDirectCostDetailsTbl      => l_PaCiDirCostDetTbl,
        XPaCiDirectCostDetailsTbl      => x_PaCiDirCostDetTbl);

Line: 160


		                   p_action_type        => 'INSERT',
		                   p_bvid               => p_bvid,
		                   p_ci_id              => p_ci_id,
		                   p_line_id_tbl        => l_dc_line_id_tbl,
		                   p_project_id         => p_project_id,
		                   p_task_id_tbl        => p_task_id_tbl,
		                   p_currency_code_tbl  => p_currency_code_tbl,
		                   p_rlmi_id_tbl        => p_rlmi_id_tbl,
				           p_res_assgn_id_tbl   => p_resource_assignment_id_tbl,
				           p_quantity_tbl       => p_quantity_tbl,
				           p_raw_cost_tbl       => p_raw_cost_tbl
                           );

Line: 180


          update pa_ci_direct_cost_details pcdc
             set (resource_assignment_id, effective_from, effective_to,
                  planning_resource_rate, burden_cost_rate,
			      raw_cost, burdened_cost) =
                     (select prac.resource_assignment_id,
                             decode(pcdc.effective_from,
						            null,pra.planning_start_date, pcdc.effective_from),
                             decode(pcdc.effective_to,
						            null, pra.planning_end_date, pcdc.effective_to),
                             prac.txn_average_raw_cost_rate,
                             prac.txn_average_burden_cost_rate,
                             decode(pcdc.quantity, null, pcdc.raw_cost,
                                       pcdc.quantity * prac.txn_average_raw_cost_rate),
                             decode(pcdc.quantity, null,
                                       pcdc.raw_cost * prac.txn_average_burden_cost_rate,
                                       pcdc.quantity * prac.txn_average_burden_cost_rate)
                        from pa_resource_assignments pra, pa_resource_asgn_curr prac
                       where pra.budget_version_id = p_bvId
                         and pra.task_id = pcdc.task_id
                         and pra.resource_list_member_id = pcdc.resource_list_member_id
					     and prac.txn_currency_code = pcdc.currency_code
                         and prac.resource_assignment_id = pra.resource_assignment_id)
           where ci_id = p_ci_id
		     and dc_line_id = l_dc_line_id_tbl(i);

Line: 213


		ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;

Line: 222


		ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;

Line: 231


		ROLLBACK TO SAVEPOINT PUB_INSERT_ROW;

Line: 244


end insert_row;

Line: 253


  PROCEDURE update_row(
    p_api_version                  IN NUMBER,
    p_init_msg_list                IN VARCHAR2 DEFAULT FND_API.G_FALSE,
    x_return_status                OUT NOCOPY VARCHAR2,
    x_msg_count                    OUT NOCOPY NUMBER,
    x_msg_data                     OUT NOCOPY VARCHAR2,
    p_bvid                         IN NUMBER,
    p_dc_line_id_tbl               IN SYSTEM.PA_NUM_TBL_TYPE,
    p_ci_id                        IN NUMBER,
    p_project_id                   IN NUMBER,
    p_task_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_expenditure_type_tbl         IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_rlmi_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_unit_of_measure_tbl          IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_currency_code_tbl            IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_quantity_tbl                 IN SYSTEM.PA_NUM_TBL_TYPE,
    p_planning_resource_rate_tbl   IN SYSTEM.PA_NUM_TBL_TYPE,
    p_raw_cost_tbl                 IN SYSTEM.PA_NUM_TBL_TYPE,
    p_burdened_cost_tbl            IN SYSTEM.PA_NUM_TBL_TYPE,
    p_raw_cost_rate_tbl            IN SYSTEM.PA_NUM_TBL_TYPE,
    p_burden_cost_rate_tbl         IN SYSTEM.PA_NUM_TBL_TYPE,
    p_resource_assignment_id_tbl   IN SYSTEM.PA_NUM_TBL_TYPE,
    p_effective_from_tbl           IN SYSTEM.PA_DATE_TBL_TYPE,
    p_effective_to_tbl             IN SYSTEM.PA_DATE_TBL_TYPE,
    p_change_reason_code           IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_change_description           IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE) IS

 l_api_version	     number := 1;

Line: 281


 l_api_name          CONSTANT VARCHAR2(30) := 'Pub.update_row';

Line: 289


 select task_id, resource_list_member_id,
        expenditure_type, nvl(quantity, -1) quantity,
		nvl(raw_cost, -1) raw_cost,
		effective_from, effective_to
   from pa_ci_direct_cost_details
  where ci_id = p_ci_id
    and dc_line_id = c_dc_line_id;

Line: 314


   budget_impact_tbl.delete;

Line: 316


   SAVEPOINT PUB_UPDATE_ROW;

Line: 398


      pa_ci_dir_cost_pvt.update_row(
      	   p_api_version                  => l_api_version,
     	   p_init_msg_list                => FND_API.G_FALSE,
    	   x_return_status                => l_return_status,
    	   x_msg_count                    => l_msg_count,
    	   x_msg_data                     => l_msg_data,
    	   PPaCiDirectCostDetailsTbl      => l_PaCiDirCostDetTbl,
    	   XPaCiDirectCostDetailsTbl      => x_PaCiDirCostDetTbl);

Line: 452


				              p_action_type        => 'UPDATE',
				              p_bvid               => p_bvid,
				              p_ci_id              => p_ci_id,
				              p_line_id_tbl        => b_dc_line_id_tbl,
				              p_project_id         => p_project_id,
				              p_task_id_tbl        => b_task_id_tbl,
				              p_currency_code_tbl  => b_currency_code_tbl,
				              p_rlmi_id_tbl        => b_rlmi_id_tbl,
				              p_res_assgn_id_tbl   => b_res_assgn_id_tbl,
				              p_quantity_tbl       => b_quantity_tbl,
				              p_raw_cost_tbl       => b_raw_cost_tbl
                              );

Line: 472


            update pa_ci_direct_cost_details pcdc
               set ( raw_cost, burdened_cost) =
                       (select decode(pcdc.quantity, null, pcdc.raw_cost,
						                 pcdc.quantity * prac.txn_average_raw_cost_rate),
						         decode(pcdc.quantity, null,
						                 pcdc.raw_cost * prac.txn_average_burden_cost_rate,
									     pcdc.quantity * prac.txn_average_burden_cost_rate)
                          from pa_resource_assignments pra, pa_resource_asgn_curr prac
                         where pra.budget_version_id = p_bvId
                           and pra.resource_assignment_id = pcdc.resource_assignment_id
                           and prac.resource_assignment_id = pra.resource_assignment_id
					       and prac.txn_currency_code = pcdc.currency_code)
             where ci_id = p_ci_id
		       and dc_line_id = b_dc_line_id_tbl(i);

Line: 488


            update pa_ci_direct_cost_details pcdc
               set raw_cost = decode(pcdc.quantity, null, pcdc.raw_cost,
			                         pcdc.quantity * pcdc.planning_resource_rate),
                   burdened_cost = decode(pcdc.quantity, null,
						                 pcdc.raw_cost * pcdc.burden_cost_rate,
									     pcdc.quantity * pcdc.burden_cost_rate)
             where ci_id = p_ci_id
		       and dc_line_id = b_dc_line_id_tbl(i);

Line: 508


     ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;

Line: 517


     ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;

Line: 526


      ROLLBACK TO SAVEPOINT PUB_UPDATE_ROW;

Line: 539


end update_row;

Line: 549


PROCEDURE delete_row(
    p_api_version                  IN NUMBER,
    p_init_msg_list                IN VARCHAR2 DEFAULT FND_API.G_FALSE,
    x_return_status                OUT NOCOPY VARCHAR2,
    x_msg_count                    OUT NOCOPY NUMBER,
    x_msg_data                     OUT NOCOPY VARCHAR2,
    p_dc_line_id_TBL               IN SYSTEM.PA_NUM_TBL_TYPE,
    p_ci_id                        IN NUMBER,
    p_project_id                   IN NUMBER,
    p_task_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_expenditure_type_tbl         IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
    p_rlmi_id_tbl                  IN SYSTEM.PA_NUM_TBL_TYPE,
    p_currency_code_tbl            IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE) IS

 l_api_version	     number := 1;

Line: 564


 l_api_name          CONSTANT VARCHAR2(30) := 'Pub.delete_row';

Line: 575


 select budget_version_id
   from pa_budget_versions
  where ci_id = c_ci_id
    and version_type in ('COST', 'ALL');

Line: 582


   SAVEPOINT PUB_DELETE_ROW;

Line: 603


      pa_ci_dir_cost_pvt.delete_row(
         p_api_version                  => l_api_version,
         p_init_msg_list                => FND_API.G_FALSE,
         x_return_status                => l_return_status,
         x_msg_count                    => l_msg_count,
         x_msg_data                     => l_msg_data,
         PPaCiDirectCostDetailsTbl      => l_PaCiDirCostDetTbl);

Line: 624


				           p_action_type        => 'DELETE',
				           p_bvid               => l_bvid,
				           p_ci_id              => p_ci_id,
				           p_line_id_tbl        => p_dc_line_id_tbl,
				           p_project_id         => p_project_id,
				           p_task_id_tbl        => p_task_id_tbl,
				           p_currency_code_tbl  => p_currency_code_tbl,
				           p_rlmi_id_tbl        => p_rlmi_id_tbl,
				           p_res_assgn_id_tbl   => l_resource_assignment_id_tbl
                           );

Line: 649


      ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;

Line: 658


      ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;

Line: 667


      ROLLBACK TO SAVEPOINT PUB_DELETE_ROW;

Line: 680


end delete_row;