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

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

Line: 56


    p_operation_type VARCHAR2, --INSERT, FROM, SELECT, WHERE1, WHERE2
    p_mode           VARCHAR2 DEFAULT 'TMP'
  ) RETURN VARCHAR2 IS

    l_str VARCHAR2(50);

Line: 65


    IF p_operation_type = 'INSERT' OR
       p_operation_type = 'SELECT' OR
       p_operation_type = 'FROM' THEN

       IF p_operation_type = 'SELECT' THEN
         l_str := 'tab1.';

Line: 319


  FUNCTION get_insert_clause (
    p_resource_class_id NUMBER,
    p_res_format_id     NUMBER,
    p_operation_type    VARCHAR2
  ) RETURN VARCHAR2 IS

    l_insert_clause VARCHAR2(4000);

Line: 334


      l_insert_clause := 'INSERT INTO pa_res_list_map_tmp3 ( RES_FORMAT_ID, RESOURCE_LIST_MEMBER_ID, RES_LIST_MEM_SEQ, ';

Line: 341


          l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ', ';

Line: 345


          l_insert_clause := l_insert_clause || get_SQL_tags (l_res_tokens(i), 'INSERT') || ' ';

Line: 352


      l_insert_clause := l_insert_clause || ') ';

Line: 357


      l_insert_clause := ' INSERT INTO pa_res_list_map_tmp4 (PERSON_ID,JOB_ID,ORGANIZATION_ID,VENDOR_ID,'
       || 'EXPENDITURE_TYPE,EVENT_TYPE,NON_LABOR_RESOURCE,EXPENDITURE_CATEGORY,'
       || 'REVENUE_CATEGORY,NON_LABOR_RESOURCE_ORG_ID,EVENT_TYPE_CLASSIFICATION,'
       || 'SYSTEM_LINKAGE_FUNCTION,PROJECT_ROLE_ID,RESOURCE_TYPE_ID,'
       || 'RESOURCE_TYPE_CODE,RESOURCE_CLASS_ID,RESOURCE_CLASS_CODE,RES_FORMAT_ID,'
       || 'MFC_COST_TYPE_ID,RESOURCE_CLASS_FLAG,FC_RES_TYPE_CODE,'
       || 'BOM_LABOR_RESOURCE_ID,BOM_EQUIP_RESOURCE_ID,INVENTORY_ITEM_ID,'
       || 'ITEM_CATEGORY_ID,PERSON_TYPE_CODE,BOM_RESOURCE_ID,NAMED_ROLE,'
       || 'INCURRED_BY_RES_FLAG,TXN_COPY_FROM_RL_FLAG,TXN_SPREAD_CURVE_ID,'
       || 'TXN_ETC_METHOD_CODE,TXN_OBJECT_TYPE,TXN_OBJECT_ID,TXN_PROJECT_ID,'
       || 'TXN_BUDGET_VERSION_ID,TXN_RESOURCE_LIST_MEMBER_ID,TXN_RESOURCE_ID,'
       || 'TXN_ALIAS,TXN_TRACK_AS_LABOR_FLAG,TXN_FUNDS_CONTROL_LEVEL_CODE,'
       || 'TXN_SOURCE_ID,TXN_SOURCE_TYPE_CODE,TXN_PROCESS_CODE,TXN_ERROR_MSG_CODE,'
       || 'TXN_TASK_ID,TXN_WBS_ELEMENT_VERSION_ID,TXN_RBS_ELEMENT_ID,'
       || 'TXN_RBS_ELEMENT_VERSION_ID,TXN_PLANNING_START_DATE,TXN_PLANNING_END_DATE,'
       || 'TXN_RECORD_VERSION_NUMBER,TXN_SP_FIXED_DATE,TXN_RATE_BASED_FLAG,'
       || 'TXN_RES_CLASS_BILL_RATE_SCH_ID,TXN_RES_CLASS_COST_SCH_ID,'
       || 'TXN_USE_PLANNING_RATES_FLAG,TXN_BILL_JOB_GROUP_ID,'
       || 'TXN_PROJECT_CURRENCY_CODE,TXN_PROJFUNC_CURRENCY_CODE,'
       || 'TXN_EMP_BILL_RATE_SCHEDULE_ID,TXN_JOB_BILL_RATE_SCHEDULE_ID,'
       || 'TXN_LABOR_BILL_RATE_ORG_ID,TXN_LABOR_SCH_TYPE,TXN_LABOR_SCHEDULE_DISCOUNT,'
       || 'TXN_LABOR_SCHEDULE_FIXED_DATE,TXN_LABOR_STD_BILL_RATE_SCHDL,'
       || 'TXN_CURRENCY_CODE,TXN_PLAN_QUANTITY,RESOURCE_LIST_MEMBER_ID, TMP_ROWID) ';

Line: 383


    RETURN l_insert_clause;

Line: 385


  END;--end function get_insert_clause

Line: 396


  FUNCTION get_select_clause (
    p_resource_class_id NUMBER,
    p_res_format_id     NUMBER,
    p_operation_type    VARCHAR2
  ) RETURN VARCHAR2 IS

    l_select_clause VARCHAR2(5000);

Line: 411


      l_select_clause := ' SELECT ' || g_res_format_id || ', tab2.RESOURCE_LIST_MEMBER_ID,'
                         || 'DECODE(tab2.RESOURCE_LIST_MEMBER_ID,NULL,pa_resource_list_members_s.NEXTVAL,NULL), ' ;

Line: 417


        l_select_clause := l_select_clause || get_SQL_tags (l_res_tokens(i), 'SELECT');

Line: 421


          l_select_clause := l_select_clause || ', ';

Line: 425


          l_select_clause := l_select_clause || ' ';

Line: 431


      RETURN l_select_clause;

Line: 436


      l_select_clause := ' SELECT tab1.PERSON_ID,tab1.JOB_ID,tab1.ORGANIZATION_ID,tab1.VENDOR_ID,';

Line: 437


      l_select_clause := l_select_clause || 'tab1.EXPENDITURE_TYPE,tab1.EVENT_TYPE,tab1.NON_LABOR_RESOURCE,';

Line: 438


      l_select_clause := l_select_clause || 'tab1.EXPENDITURE_CATEGORY,tab1.REVENUE_CATEGORY,';

Line: 439


      l_select_clause := l_select_clause || 'tab1.NON_LABOR_RESOURCE_ORG_ID,tab1.EVENT_TYPE_CLASSIFICATION,';

Line: 440


      l_select_clause := l_select_clause || 'tab1.SYSTEM_LINKAGE_FUNCTION,tab1.PROJECT_ROLE_ID,tab1.RESOURCE_TYPE_ID,';

Line: 441


      l_select_clause := l_select_clause || 'tab1.RESOURCE_TYPE_CODE,tab1.RESOURCE_CLASS_ID,tab1.RESOURCE_CLASS_CODE,';

Line: 442


      l_select_clause := l_select_clause || 'tab2.RES_FORMAT_ID,tab1.MFC_COST_TYPE_ID,tab1.RESOURCE_CLASS_FLAG,';

Line: 443


      l_select_clause := l_select_clause || 'tab1.FC_RES_TYPE_CODE,tab1.BOM_LABOR_RESOURCE_ID,';

Line: 444


      l_select_clause := l_select_clause || 'tab1.BOM_EQUIP_RESOURCE_ID,tab1.INVENTORY_ITEM_ID,tab1.ITEM_CATEGORY_ID,';

Line: 445


      l_select_clause := l_select_clause || 'tab1.PERSON_TYPE_CODE,tab1.BOM_RESOURCE_ID,tab1.NAMED_ROLE,';

Line: 446


      l_select_clause := l_select_clause || 'tab1.INCURRED_BY_RES_FLAG,tab1.TXN_COPY_FROM_RL_FLAG,';

Line: 447


      l_select_clause := l_select_clause || 'tab1.TXN_SPREAD_CURVE_ID,tab1.TXN_ETC_METHOD_CODE,tab1.TXN_OBJECT_TYPE,';

Line: 448


      l_select_clause := l_select_clause || 'tab1.TXN_OBJECT_ID,tab1.TXN_PROJECT_ID,tab1.TXN_BUDGET_VERSION_ID,';

Line: 449


      l_select_clause := l_select_clause || 'tab1.TXN_RESOURCE_LIST_MEMBER_ID,tab1.TXN_RESOURCE_ID,tab1.TXN_ALIAS,';

Line: 450


      l_select_clause := l_select_clause || 'tab1.TXN_TRACK_AS_LABOR_FLAG,tab1.TXN_FUNDS_CONTROL_LEVEL_CODE,';

Line: 451


      l_select_clause := l_select_clause || 'tab1.TXN_SOURCE_ID,tab1.TXN_SOURCE_TYPE_CODE,tab1.TXN_PROCESS_CODE,';

Line: 452


      l_select_clause := l_select_clause || 'tab1.TXN_ERROR_MSG_CODE,tab1.TXN_TASK_ID,tab1.TXN_WBS_ELEMENT_VERSION_ID,';

Line: 453


      l_select_clause := l_select_clause || 'tab1.TXN_RBS_ELEMENT_ID,tab1.TXN_RBS_ELEMENT_VERSION_ID,';

Line: 454


      l_select_clause := l_select_clause || 'tab1.TXN_PLANNING_START_DATE,tab1.TXN_PLANNING_END_DATE,';

Line: 455


      l_select_clause := l_select_clause || 'tab1.TXN_RECORD_VERSION_NUMBER,tab1.TXN_SP_FIXED_DATE,';

Line: 456


      l_select_clause := l_select_clause || 'tab1.TXN_RATE_BASED_FLAG,tab1.TXN_RES_CLASS_BILL_RATE_SCH_ID,';

Line: 457


      l_select_clause := l_select_clause || 'tab1.TXN_RES_CLASS_COST_SCH_ID,tab1.TXN_USE_PLANNING_RATES_FLAG,';

Line: 458


      l_select_clause := l_select_clause || 'tab1.TXN_BILL_JOB_GROUP_ID,tab1.TXN_PROJECT_CURRENCY_CODE,';

Line: 459


      l_select_clause := l_select_clause || 'tab1.TXN_PROJFUNC_CURRENCY_CODE,tab1.TXN_EMP_BILL_RATE_SCHEDULE_ID,';

Line: 460


      l_select_clause := l_select_clause || 'tab1.TXN_JOB_BILL_RATE_SCHEDULE_ID,tab1.TXN_LABOR_BILL_RATE_ORG_ID,';

Line: 461


      l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCH_TYPE,tab1.TXN_LABOR_SCHEDULE_DISCOUNT,';

Line: 462


      l_select_clause := l_select_clause || 'tab1.TXN_LABOR_SCHEDULE_FIXED_DATE,tab1.TXN_LABOR_STD_BILL_RATE_SCHDL,';

Line: 463


      l_select_clause := l_select_clause || 'tab1.TXN_CURRENCY_CODE,tab1.TXN_PLAN_QUANTITY,';

Line: 464


      l_select_clause := l_select_clause || 'NVL(tab2.RESOURCE_LIST_MEMBER_ID,tab2.RES_LIST_MEM_SEQ),';

Line: 465


      l_select_clause := l_select_clause || 'tab1.ROWID';

Line: 467


      RETURN l_select_clause;

Line: 471


  END;--end function get_select_clause

Line: 494


      l_from_clause  := ' FROM (SELECT DISTINCT tab1.RES_FORMAT_ID, ' ;

Line: 677


    l_INSERT_clause VARCHAR2 (1000);

Line: 678


    l_SELECT_clause VARCHAR2 (1000);

Line: 692


    l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP3');

Line: 693


    l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP3');

Line: 696


    l_SQL_statement := l_INSERT_clause || ' ' ||
                       l_SELECT_clause || ' ' ||
                       l_FROM_clause || ' ' ||
                       l_WHERE_clause || ';' ;

Line: 720


    /* SELECT count(*) into l_count
       FROM pa_res_list_map_tmp1
    IF (l_count = 0) THEN
      RETURN FALSE;

Line: 728


      SELECT 1
      INTO l_count FROM dual WHERE EXISTS (SELECT 1
      FROM pa_res_list_map_tmp1);

Line: 750


    SELECT
      tmp3.RES_LIST_MEM_SEQ,
      tmp3.RES_FORMAT_ID,
      tmp3.BOM_LABOR_RESOURCE_ID,
      tmp3.BOM_EQUIP_RESOURCE_ID,
      tmp3.BOM_RESOURCE_ID,
      tmp3.PERSON_ID,
      tmp3.EVENT_TYPE,
      tmp3.EXPENDITURE_CATEGORY,
      tmp3.EXPENDITURE_TYPE,
      tmp3.ITEM_CATEGORY_ID,
      tmp3.INVENTORY_ITEM_ID,
      tmp3.JOB_ID,
      tmp3.ORGANIZATION_ID,
      tmp3.PERSON_TYPE_CODE,
      tmp3.NON_LABOR_RESOURCE,
      tmp3.REVENUE_CATEGORY,
      tmp3.VENDOR_ID,
      tmp3.PROJECT_ROLE_ID,
      tmp3.FC_RES_TYPE_CODE,
      tmp3.INCURRED_BY_RES_FLAG,
      tmp3.NAMED_ROLE,
      cls.RESOURCE_CLASS_ID,
      cls.RESOURCE_CLASS_CODE
    FROM
      pa_res_list_map_tmp3 tmp3,
      pa_resource_classes_b cls
    WHERE
      RESOURCE_LIST_MEMBER_ID IS NULL AND
      nvl(tmp3.resource_class_id, l_resource_class_id) = cls.resource_class_id;

Line: 894


    l_INSERT_clause VARCHAR2 (4000);

Line: 895


    l_SELECT_clause VARCHAR2 (5000);

Line: 907


    l_INSERT_clause := get_insert_clause (p_resource_class_id, p_format_id, 'TMP4');

Line: 908


    l_SELECT_clause := get_select_clause (p_resource_class_id, p_format_id, 'TMP4');

Line: 912


    l_SQL_statement := l_INSERT_clause || ' ' ||
                       l_SELECT_clause || ' ' ||
                       l_FROM_clause || ' ' ||
                       l_WHERE_clause || ';' ;

Line: 921


	-- Replaced truncate statement with delete for resolving the auto commit issue.
    --EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP3');

Line: 923


    EXECUTE IMMEDIATE ('DELETE FROM ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP3');

Line: 944


      EXECUTE IMMEDIATE ('DELETE FROM ' || get_pa_schema_name || '.PA_RES_LIST_MAP_TMP1 TMP1' || ' WHERE ROWID IN ( SELECT tmp_rowid FROM pa_res_list_map_tmp4  tmp4  WHERE tmp4.tmp_rowid = tmp1.rowid ) ');

Line: 1047


    SELECT
      resource_class_id
    FROM
      pa_resource_classes_b;

Line: 1077


      SELECT RES_FORMAT_ID
      BULK COLLECT
      INTO l_res_list_formats
      FROM pa_plan_rl_formats
      WHERE RESOURCE_LIST_ID = p_resource_list_id;

Line: 1094


      SELECT CONTROL_FLAG
      INTO   g_control_flag
      FROM pa_resource_lists_all_bg
      WHERE RESOURCE_LIST_ID = p_resource_list_id;

Line: 1134


	-- update resource attributes which can be derived but are not present
	/*  split the below update into two to remove 9i dependencies
	UPDATE	pa_res_list_map_tmp1 tmp1
	SET	tmp1.EXPENDITURE_CATEGORY	= nvl(tmp1.EXPENDITURE_CATEGORY, (SELECT typ.EXPENDITURE_CATEGORY
										FROM PA_EXPENDITURE_TYPES typ
										WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE ) )
		,tmp1.ITEM_CATEGORY_ID		= nvl(tmp1.ITEM_CATEGORY_ID, (SELECT cat.CATEGORY_ID
										FROM
										  PA_RESOURCE_CLASSES_B classes,
										  PA_PLAN_RES_DEFAULTS  cls,
										  MTL_ITEM_CATEGORIES   cat
										WHERE
										  classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'          and
										  cls.RESOURCE_CLASS_ID       = classes.RESOURCE_CLASS_ID and
										  cls.ITEM_CATEGORY_SET_ID    = cat.CATEGORY_SET_ID and
										  cat.INVENTORY_ITEM_ID   =  tmp1.INVENTORY_ITEM_ID  and
  cat.organization_id = tmp1.organization_id )
	      ) ;

Line: 1153


     UPDATE	pa_res_list_map_tmp1 tmp1
	SET	tmp1.EXPENDITURE_CATEGORY	=  (SELECT typ.EXPENDITURE_CATEGORY
										FROM PA_EXPENDITURE_TYPES typ
										WHERE typ.EXPENDITURE_TYPE = tmp1.EXPENDITURE_TYPE )
	WHERE  tmp1.EXPENDITURE_CATEGORY IS NULL;

Line: 1158


	UPDATE	pa_res_list_map_tmp1 tmp1
	SET	tmp1.ITEM_CATEGORY_ID		=  (SELECT cat.CATEGORY_ID
										FROM
										  PA_RESOURCE_CLASSES_B classes,
										  PA_PLAN_RES_DEFAULTS  cls,
										  MTL_ITEM_CATEGORIES   cat
										WHERE
										  classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'          and
										  cls.RESOURCE_CLASS_ID       = classes.RESOURCE_CLASS_ID and
										  cls.ITEM_CATEGORY_SET_ID    = cat.CATEGORY_SET_ID and
										  cat.INVENTORY_ITEM_ID   =  tmp1.INVENTORY_ITEM_ID  and
										  cat.organization_id = tmp1.organization_id )
	WHERE tmp1.ITEM_CATEGORY_ID IS NULL;

Line: 1174


	UPDATE  pa_res_list_map_tmp1 tmp1
           SET tmp1.revenue_category = (SELECT evt.revenue_category_code
                                          FROM pa_event_types evt
                                         WHERE evt.event_type=tmp1.event_type)
         WHERE tmp1.revenue_category IS NULL
	   AND tmp1.event_type IS NOT NULL;

Line: 1181


        UPDATE  pa_res_list_map_tmp1 tmp1
           SET tmp1.revenue_category = (SELECT et.revenue_category_code
                                          FROM pa_expenditure_types et
                                         WHERE et.expenditure_type=tmp1.expenditure_type)
         WHERE tmp1.revenue_category IS NULL
	   AND tmp1.expenditure_type IS NOT NULL;

Line: 1191


     SELECT job_group_id INTO l_job_group_id FROM pa_resource_lists_all_bg WHERE resource_list_id = p_resource_list_id ;

Line: 1197


     UPDATE	pa_res_list_map_tmp1 tmp1
	SET	tmp1.job_id	=
	(SELECT PA_Cross_Business_Grp.IsMappedToJob(tmp1.job_id, l_job_group_id)  FROM DUAL)
	WHERE  tmp1.job_id IS NOT NULL;

Line: 1227


      l_eff_formats.DELETE; --cleanup collection for next class