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_RESOURCE_LIST_PKG SQL Statements

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

Line: 5


PROCEDURE Insert_parent_row (X_ROW_ID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                             X_RESOURCE_LIST_MEMBER_ID NUMBER,
                             X_RESOURCE_LIST_ID        NUMBER,
                             X_RESOURCE_ID             NUMBER,
                             X_ALIAS                   VARCHAR2,
                             X_SORT_ORDER              NUMBER,
                             X_MEMBER_LEVEL            NUMBER,
                             X_DISPLAY_FLAG            VARCHAR2,
                             X_ENABLED_FLAG            VARCHAR2,
                             X_TRACK_AS_LABOR_FLAG     VARCHAR2,
                             X_LAST_UPDATED_BY         NUMBER,
                             X_LAST_UPDATE_DATE        DATE,
                             X_CREATION_DATE           DATE,
                             X_CREATED_BY              NUMBER,
                             X_LAST_UPDATE_LOGIN       NUMBER,
                             X_Funds_Control_Level_Code VARCHAR2,
                             p_migration_code          VARCHAR2
                             ) IS
CURSOR PARENT_RES_CUR IS
Select
Rowid
from
PA_RESOURCE_LIST_MEMBERS
Where Resource_List_Member_Id   =  X_Resource_List_Member_Id;

Line: 54


  SELECT prta.person_id,
         prta.job_id,
         prta.organization_id,
         prta.vendor_id,
         prta.project_role_id,
         prta.expenditure_type,
         prta.event_type,
         prta.expenditure_category,
         prta.revenue_category,
         prta.non_labor_resource,
         prta.non_labor_resource_org_id,
         prta.event_type_classification,
         prta.system_linkage_function,
         prta.resource_format_id,
         prt.resource_type_id,
         prt.resource_type_code
  INTO   l_person_id,
         l_job_id,
         l_organization_id,
         l_vendor_id,
         l_project_role_id,
         l_expenditure_type,
         l_event_type,
         l_expenditure_category,
         l_revenue_category,
         l_nlr_resource,
         l_nlr_res_org_id,
         l_event_type_cls,
         l_system_link_function,
         l_resource_format_id,
         l_resource_type_id,
         l_res_type_code
  FROM   PA_RESOURCE_TXN_ATTRIBUTES PRTA,
         PA_RESOURCES PR,
         PA_RESOURCE_TYPES PRT
  WHERE  prta.resource_id = pr.resource_id
    AND  pr.resource_id =X_RESOURCE_ID
    AND  pr.resource_type_id= prt.resource_type_id;

Line: 93


/* As this select will not be used to insert unclassified resource, so no outer join is kept for
   prta table  */

  Insert Into PA_RESOURCE_LIST_MEMBERS
                            (RESOURCE_LIST_MEMBER_ID,
                             RESOURCE_LIST_ID,
                             RESOURCE_ID ,
                             ALIAS ,
                             SORT_ORDER ,
                             MEMBER_LEVEL,
                             DISPLAY_FLAG ,
                             ENABLED_FLAG,
                             TRACK_AS_LABOR_FLAG,
                             LAST_UPDATED_BY,
                             LAST_UPDATE_DATE,
                             CREATION_DATE,
                             CREATED_BY,
                             LAST_UPDATE_LOGIN,
                             PARENT_MEMBER_ID,
                             Funds_Control_Level_Code,
                             PERSON_ID,             /*16 newly added columns-bug 1889671*/
                             JOB_ID,
                             ORGANIZATION_ID,
                             VENDOR_ID,
                             PROJECT_ROLE_ID,
                             EXPENDITURE_TYPE,
                             EVENT_TYPE,
                             EXPENDITURE_CATEGORY,
                             REVENUE_CATEGORY,
                             NON_LABOR_RESOURCE,
                             NON_LABOR_RESOURCE_ORG_ID,
                             EVENT_TYPE_CLASSIFICATION,
                             SYSTEM_LINKAGE_FUNCTION,
                             RESOURCE_FORMAT_ID,
                             RESOURCE_TYPE_ID,
                             RESOURCE_TYPE_CODE,
                             migration_code
                             )
              VALUES
                            (X_RESOURCE_LIST_MEMBER_ID,
                             X_RESOURCE_LIST_ID,
                             X_RESOURCE_ID,
                             X_ALIAS ,
                             X_SORT_ORDER ,
                             X_MEMBER_LEVEL,
                             X_DISPLAY_FLAG,
                             X_ENABLED_FLAG,
                             X_TRACK_AS_LABOR_FLAG,
                             X_LAST_UPDATED_BY,
                             X_LAST_UPDATE_DATE,
                             X_CREATION_DATE,
                             X_CREATED_BY,
                             X_LAST_UPDATE_LOGIN,
                             NULL,
                             X_Funds_Control_Level_Code,
                             l_person_id,         /*16 newly added columns-bug 1889671*/
                             l_job_id,
                             l_organization_id,
                             l_vendor_id,
                             l_project_role_id,
                             l_expenditure_type,
                             l_event_type,
                             l_expenditure_category,
                             l_revenue_category,
                             l_nlr_resource,
                             l_nlr_res_org_id,
                             l_event_type_cls,
                             l_system_link_function,
                             l_resource_format_id,
                             l_resource_type_id,
                             l_res_type_code,
                             p_migration_code
                             );

Line: 180


END Insert_Parent_Row;

Line: 182


PROCEDURE Update_Parent_Row (X_ROW_ID IN VARCHAR2,
                             X_RESOURCE_LIST_MEMBER_ID NUMBER,
                             X_ALIAS                   VARCHAR2,
                             X_SORT_ORDER              NUMBER,
                             X_MEMBER_LEVEL            NUMBER,
                             X_DISPLAY_FLAG            VARCHAR2,
                             X_ENABLED_FLAG            VARCHAR2,
                             X_TRACK_AS_LABOR_FLAG     VARCHAR2,
                             X_LAST_UPDATED_BY         NUMBER,
                             X_LAST_UPDATE_DATE        DATE,
                             X_LAST_UPDATE_LOGIN       NUMBER,
                             X_Funds_Control_Level_Code VARCHAR2,
                             p_migration_code          VARCHAR2
                             ) IS
BEGIN

         Update PA_RESOURCE_LIST_MEMBERS
                SET
                ALIAS               = X_ALIAS,
                SORT_ORDER          = X_SORT_ORDER,
                DISPLAY_FLAG        = X_DISPLAY_FLAG,
                ENABLED_FLAG        = X_ENABLED_FLAG,
                TRACK_AS_LABOR_FLAG = X_TRACK_AS_LABOR_FLAG,
                MIGRATION_CODE      = p_migration_code,
                LAST_UPDATED_BY     = X_LAST_UPDATED_BY,
                LAST_UPDATE_DATE    = X_LAST_UPDATE_DATE,
                LAST_UPDATE_LOGIN   = X_LAST_UPDATE_LOGIN,
                Funds_Control_Level_Code = X_Funds_Control_Level_Code
                WHERE ROWID         = X_ROW_ID;

Line: 214


END Update_parent_row;

Line: 226


    Select * From PA_RESOURCE_LIST_MEMBERS WHERE ROWID = X_ROW_ID
    For Update of RESOURCE_LIST_MEMBER_ID NOWAIT;

Line: 234


       FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');

Line: 261


Procedure Delete_Parent_Row (X_ROW_ID IN VARCHAR2) Is
Begin
   Delete from PA_RESOURCE_LIST_MEMBERS Where RowId = X_Row_Id;

Line: 268


End Delete_Parent_Row;

Line: 270


PROCEDURE Insert_child_row  (X_ROW_ID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                             X_RESOURCE_LIST_MEMBER_ID NUMBER,
                             X_RESOURCE_LIST_ID        NUMBER,
                             X_RESOURCE_ID             NUMBER,
                             X_ALIAS                   VARCHAR2,
                             X_PARENT_MEMBER_ID        NUMBER,
                             X_SORT_ORDER              NUMBER,
                             X_MEMBER_LEVEL            NUMBER,
                             X_DISPLAY_FLAG            VARCHAR2,
                             X_ENABLED_FLAG            VARCHAR2,
                             X_TRACK_AS_LABOR_FLAG     VARCHAR2,
                             X_LAST_UPDATED_BY         NUMBER,
                             X_LAST_UPDATE_DATE        DATE,
                             X_CREATION_DATE           DATE,
                             X_CREATED_BY              NUMBER,
                             X_LAST_UPDATE_LOGIN       NUMBER,
                             X_Funds_Control_Level_Code VARCHAR2
                             ) IS

CURSOR CHILD_RES_CUR IS Select Rowid from PA_RESOURCE_LIST_MEMBERS
 Where Resource_List_Member_Id   =  X_Resource_List_Member_Id;

Line: 317


SELECT   prta.person_id,
         prta.job_id,
         prta.organization_id,
         prta.vendor_id,
         prta.project_role_id,
         prta.expenditure_type,
         prta.event_type,
         prta.expenditure_category,
         prta.revenue_category,
         prta.non_labor_resource,
         prta.non_labor_resource_org_id,
         prta.event_type_classification,
         prta.system_linkage_function,
         prta.resource_format_id,
         prt.resource_type_id,
         prt.resource_type_code

  INTO   l_person_id,
         l_job_id,
         l_organization_id,
         l_vendor_id,
         l_project_role_id,
         l_expenditure_type,
         l_event_type,
         l_expenditure_category,
         l_revenue_category,
         l_nlr_resource,
         l_nlr_res_org_id,
         l_event_type_cls,
         l_system_link_function,
         l_resource_format_id,
         l_resource_type_id,
         l_res_type_code
  FROM   PA_RESOURCE_TXN_ATTRIBUTES PRTA,
         PA_RESOURCES PR,
         PA_RESOURCE_TYPES PRT
  WHERE  prta.resource_id(+) = pr.resource_id
    AND  pr.resource_id =X_RESOURCE_ID
    AND  pr.resource_type_id= prt.resource_type_id;

Line: 357


/* As this select will not be used to insert unclassified resource, so no outer join is kept for
   prta table  */

  Insert Into PA_RESOURCE_LIST_MEMBERS
                            (RESOURCE_LIST_MEMBER_ID,
                             RESOURCE_LIST_ID,
                             RESOURCE_ID ,
                             ALIAS ,
                             PARENT_MEMBER_ID,
                             SORT_ORDER ,
                             MEMBER_LEVEL,
                             DISPLAY_FLAG ,
                             ENABLED_FLAG,
                             TRACK_AS_LABOR_FLAG,
                             LAST_UPDATED_BY,
                             LAST_UPDATE_DATE,
                             CREATION_DATE,
                             CREATED_BY,
                             LAST_UPDATE_LOGIN,
                             Funds_Control_Level_Code,
                             PERSON_ID,       /*16 newly added columns-bug 1889671*/
                             JOB_ID,
                             ORGANIZATION_ID,
                             VENDOR_ID,
                             PROJECT_ROLE_ID,
                             EXPENDITURE_TYPE,
                             EVENT_TYPE,
                             EXPENDITURE_CATEGORY,
                             REVENUE_CATEGORY,
                             NON_LABOR_RESOURCE,
                             NON_LABOR_RESOURCE_ORG_ID,
                             EVENT_TYPE_CLASSIFICATION,
                             SYSTEM_LINKAGE_FUNCTION,
                             RESOURCE_FORMAT_ID,
                             RESOURCE_TYPE_ID,
                             RESOURCE_TYPE_CODE
                             )
              VALUES
                            (X_RESOURCE_LIST_MEMBER_ID,
                             X_RESOURCE_LIST_ID,
                             X_RESOURCE_ID,
                             X_ALIAS ,
                             X_PARENT_MEMBER_ID,
                             X_SORT_ORDER ,
                             X_MEMBER_LEVEL,
                             X_DISPLAY_FLAG,
                             X_ENABLED_FLAG,
                             X_TRACK_AS_LABOR_FLAG,
                             X_LAST_UPDATED_BY,
                             X_LAST_UPDATE_DATE,
                             X_CREATION_DATE,
                             X_CREATED_BY,
                             X_LAST_UPDATE_LOGIN,
                             X_Funds_Control_Level_Code,
                             l_person_id,          /*16 newly added columns-bug 1889671*/
                             l_job_id,
                             l_organization_id,
                             l_vendor_id,
                             l_project_role_id,
                             l_expenditure_type,
                             l_event_type,
                             l_expenditure_category,
                             l_revenue_category,
                             l_nlr_resource,
                             l_nlr_res_org_id,
                             l_event_type_cls,
                             l_system_link_function,
                             l_resource_format_id,
                             l_resource_type_id,
                             l_res_type_code
                             );

Line: 442


END Insert_CHILD_Row;

Line: 444


PROCEDURE Update_CHILD_Row (X_ROW_ID IN VARCHAR2,
                             X_RESOURCE_LIST_MEMBER_ID NUMBER,
                             X_ALIAS                   VARCHAR2,
                             X_SORT_ORDER              NUMBER,
                             X_MEMBER_LEVEL            NUMBER,
                             X_DISPLAY_FLAG            VARCHAR2,
                             X_ENABLED_FLAG            VARCHAR2,
                             X_TRACK_AS_LABOR_FLAG     VARCHAR2,
                             X_LAST_UPDATED_BY         NUMBER,
                             X_LAST_UPDATE_DATE        DATE,
                             X_LAST_UPDATE_LOGIN       NUMBER,
                             X_Funds_Control_Level_Code VARCHAR2
                             ) IS
BEGIN

         Update PA_RESOURCE_LIST_MEMBERS
                SET
                    ALIAS = X_ALIAS,
               SORT_ORDER = X_SORT_ORDER,
             DISPLAY_FLAG = X_DISPLAY_FLAG,
             ENABLED_FLAG = X_ENABLED_FLAG,
       TRACK_AS_LABOR_FLAG = X_TRACK_AS_LABOR_FLAG,
       LAST_UPDATED_BY    = X_LAST_UPDATED_BY,
       LAST_UPDATE_DATE   = X_LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN   = X_LAST_UPDATE_LOGIN,
      Funds_Control_Level_Code = X_Funds_Control_Level_Code
  Where ROWID   = X_ROW_ID;

Line: 474


END Update_CHILD_row;

Line: 486


    Select * From PA_RESOURCE_LIST_MEMBERS WHERE ROWID = X_ROW_ID
    For Update of RESOURCE_LIST_MEMBER_ID NOWAIT;

Line: 494


       FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');

Line: 520


Procedure Delete_CHILD_Row (X_ROW_ID IN VARCHAR2) Is
Begin
   Delete from PA_RESOURCE_LIST_MEMBERS Where RowId = X_Row_Id;

Line: 526


End Delete_Child_Row;

Line: 529


This procedure will delete the unclassified resource list member if all the children for
parent is deleted */

Procedure Delete_Unclassified_Child (x_resource_list_id IN
                                          PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_ID%TYPE,
                                     x_parent_member_id IN
                                          PA_RESOURCE_LIST_MEMBERS.Parent_Member_ID%TYPE,
                                     X_msg_Count  OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                                     X_msg_Data   OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                                     X_return_Status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
                                    ) IS


CURSOR Cur_Child_Count IS
SELECT count(*)
FROM pa_resource_list_members PRLM
WHERE prlm.Parent_member_id=x_parent_member_id
AND   NVL(prlm.resource_type_code,'xyz') <> 'UNCLASSIFIED';

Line: 561


 SELECT Group_Resource_Type_ID
 INTO  l_group_resource_type_id
 FROM  PA_RESOURCE_LISTS_ALL_BG
 WHERE resource_list_id = x_resource_list_id;

Line: 577


/*IF the number of children for resource parent is zero, delete the unclassified list member also.*/
 If L_count = 0 THEN

    Delete pa_resource_list_members prlm
    WHere prlm.parent_member_id=x_parent_member_id
    AND   resource_type_code = 'UNCLASSIFIED';

Line: 595


end DELETE_UNCLASSIFIED_CHILD;