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.PAY_BALANCES_UPLOAD_PKG SQL Statements

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

Line: 8


select business_group_id
from per_business_groups
where name =p_BG_NAME;

Line: 14


SELECT userenv('sessionid') from dual;

Line: 17


select session_id
from hr_owner_definitions
where session_id=p_session_id;

Line: 78


SELECT balance_category_id
	,category_name
	,effective_start_date
	,effective_end_date
	,legislation_code
	,business_group_id
	,object_version_number
	,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
FROM	PAY_BALANCE_CATEGORIES_F
WHERE	category_name	= p_CATEGORY_NAME
AND	effective_start_date  =to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD')
AND 	effective_end_date   =to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD')
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);

Line: 93


SELECT balance_category_id
	,category_name
	,effective_start_date
	,effective_end_date
	,legislation_code
	,business_group_id
	,object_version_number
	,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
FROM	PAY_BALANCE_CATEGORIES_F
WHERE	category_name	= p_CATEGORY_NAME
AND	effective_start_date  =to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD')
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);

Line: 107


SELECT balance_category_id
	,category_name
	,effective_start_date
	,effective_end_date
	,legislation_code
	,business_group_id
	,object_version_number
	,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
FROM	PAY_BALANCE_CATEGORIES_F
WHERE	category_name	= p_CATEGORY_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);

Line: 142


	      update  pay_balance_categories_f
	      set
	         save_run_balance_enabled           = p_SAVE_RUN_BALANCE_ENABLED
                ,user_category_name                 = p_user_category_name
		,pbc_information_category           = p_PBC_INFORMATION_CATEGORY
		,pbc_information1                   = p_pbc_information1
		,pbc_information2                   = p_pbc_information2
		,pbc_information3                   = p_pbc_information3
		,pbc_information4                   = p_pbc_information4
		,pbc_information5                   = p_pbc_information5
		,pbc_information6                   = p_pbc_information6
		,pbc_information7                   = p_pbc_information7
    	        ,pbc_information8                   = p_pbc_information8
		,pbc_information9                   = p_pbc_information9
		,pbc_information10                  = p_pbc_information10
		,pbc_information11                  = p_pbc_information11
		,pbc_information12                  = p_pbc_information12
		,pbc_information13                  = p_pbc_information13
		,pbc_information14                  = p_pbc_information14
		,pbc_information15                  = p_pbc_information15
		,pbc_information16                  = p_pbc_information16
		,pbc_information17                  = p_pbc_information17
		,pbc_information18                  = p_pbc_information18
		,pbc_information19                  = p_pbc_information19
		,pbc_information20                  = p_pbc_information20
		,pbc_information21                  = p_pbc_information21
		,pbc_information22                  = p_pbc_information22
		,pbc_information23                  = p_pbc_information23
		,pbc_information24                  = p_pbc_information24
		,pbc_information25                  = p_pbc_information25
		,pbc_information26                  = p_pbc_information26
		,pbc_information27                  = p_pbc_information27
		,pbc_information28                  = p_pbc_information28
		,pbc_information29                  = p_pbc_information29
		,pbc_information30                  = p_pbc_information30
		,object_version_number              = l_ovn
		where balance_category_id = l_balance_category_id;

Line: 188


	      --need to perform a date-track update
	         if(p_OWNER='SEED') then
               	   hr_general2.init_fndload(800,1);

Line: 194


	         update  pay_balance_categories_f
		 set
		 effective_end_date = to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD')
		,save_run_balance_enabled       = p_SAVE_RUN_BALANCE_ENABLED
                ,user_category_name             = p_user_category_name
		,pbc_information_category       = p_PBC_INFORMATION_CATEGORY
		,pbc_information1               = p_pbc_information1
		,pbc_information2               = p_pbc_information2
		,pbc_information3               = p_pbc_information3
		,pbc_information4               = p_pbc_information4
		,pbc_information5               = p_pbc_information5
		,pbc_information6               = p_pbc_information6
		,pbc_information7               = p_pbc_information7
    	      	,pbc_information8               = p_pbc_information8
		,pbc_information9               = p_pbc_information9
		,pbc_information10              = p_pbc_information10
		,pbc_information11              = p_pbc_information11
		,pbc_information12              = p_pbc_information12
		,pbc_information13              = p_pbc_information13
		,pbc_information14              = p_pbc_information14
		,pbc_information15              = p_pbc_information15
		,pbc_information16              = p_pbc_information16
		,pbc_information17              = p_pbc_information17
		,pbc_information18              = p_pbc_information18
		,pbc_information19              = p_pbc_information19
		,pbc_information20              = p_pbc_information20
		,pbc_information21              = p_pbc_information21
		,pbc_information22              = p_pbc_information22
		,pbc_information23              = p_pbc_information23
		,pbc_information24              = p_pbc_information24
		,pbc_information25              = p_pbc_information25
		,pbc_information26              = p_pbc_information26
		,pbc_information27              = p_pbc_information27
		,pbc_information28              = p_pbc_information28
		,pbc_information29              = p_pbc_information29
		,pbc_information30              = p_pbc_information30
		,object_version_number          = l_ovn
		where   balance_category_id = l_balance_category_id;

Line: 233


	      --need to insert the new date-track row.
	         if(p_OWNER='SEED') then
               	   hr_general2.init_fndload(800,1);

Line: 239


	         insert into
	         pay_balance_categories_f(balance_category_id,category_name,
     	         effective_start_date,effective_end_date,legislation_code,
	         business_group_id,save_run_balance_enabled,user_category_name,
	         pbc_information_category,pbc_information1,pbc_information2,
		 pbc_information3,pbc_information4,pbc_information5,
		 pbc_information6,pbc_information7,pbc_information8,
	         pbc_information9,pbc_information10,pbc_information11,
	         pbc_information12,pbc_information13,pbc_information14,
	         pbc_information15,pbc_information16,pbc_information17,
	         pbc_information18, pbc_information19,pbc_information20,
	         pbc_information21,pbc_information22,pbc_information23,
	         pbc_information24,pbc_information25,pbc_information26 ,
	         pbc_information27,pbc_information28,pbc_information29,
	         pbc_information30,object_version_number)
	         Values
	         (l_balance_category_id,p_CATEGORY_NAME,
	          to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD'),
	          to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD'),
	          p_LEGISLATION_CODE,l_bus_grp_id,p_SAVE_RUN_BALANCE_ENABLED,
                  p_user_category_name,
	          p_PBC_INFORMATION_CATEGORY,p_PBC_INFORMATION1,
		  p_PBC_INFORMATION2,p_PBC_INFORMATION3,p_PBC_INFORMATION4,
		  p_PBC_INFORMATION5,p_PBC_INFORMATION6,p_PBC_INFORMATION7,
		  p_PBC_INFORMATION8,p_PBC_INFORMATION9,p_PBC_INFORMATION10,
		  p_PBC_INFORMATION11,p_PBC_INFORMATION12,p_PBC_INFORMATION13,
		  p_PBC_INFORMATION14,p_PBC_INFORMATION15,p_PBC_INFORMATION16,
		  p_PBC_INFORMATION17,p_PBC_INFORMATION18,p_PBC_INFORMATION19,
		  p_PBC_INFORMATION20,p_PBC_INFORMATION21,p_PBC_INFORMATION22,
		  p_PBC_INFORMATION23,p_PBC_INFORMATION24,p_PBC_INFORMATION25,
		  p_PBC_INFORMATION26,p_PBC_INFORMATION27,p_PBC_INFORMATION28,
		  p_PBC_INFORMATION29,p_PBC_INFORMATION30,to_number(p_OVN)
		 );

Line: 276


	--row does not exist in the table. so insert the new row into the table
	    if(p_OWNER='SEED') then
               	   hr_general2.init_fndload(800,1);

Line: 283


	    insert into
	    pay_balance_categories_f(balance_category_id,category_name,
	    effective_start_date,effective_end_date,legislation_code,
	    business_group_id,save_run_balance_enabled,user_category_name,
            pbc_information_category,
	    pbc_information1,pbc_information2,pbc_information3,pbc_information4,
            pbc_information5,pbc_information6,pbc_information7,pbc_information8,
	    pbc_information9,pbc_information10,pbc_information11,
	    pbc_information12,pbc_information13,pbc_information14,
	    pbc_information15,pbc_information16,pbc_information17,
	    pbc_information18, pbc_information19,pbc_information20,
	    pbc_information21,pbc_information22,pbc_information23,
	    pbc_information24,pbc_information25,pbc_information26 ,
	    pbc_information27,pbc_information28,pbc_information29,
	    pbc_information30,object_version_number)
	    Values
	    (pay_balance_categories_s.nextval,p_CATEGORY_NAME,
	     to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD'),
	     to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD'),
	     p_LEGISLATION_CODE,l_bus_grp_id,p_SAVE_RUN_BALANCE_ENABLED,
             p_user_category_name,
	     p_PBC_INFORMATION_CATEGORY,p_PBC_INFORMATION1,p_PBC_INFORMATION2,
	     p_PBC_INFORMATION3,p_PBC_INFORMATION4,p_PBC_INFORMATION5,
	     p_PBC_INFORMATION6,p_PBC_INFORMATION7,p_PBC_INFORMATION8,
	     p_PBC_INFORMATION9,p_PBC_INFORMATION10,p_PBC_INFORMATION11,
	     p_PBC_INFORMATION12,p_PBC_INFORMATION13,p_PBC_INFORMATION14,
	     p_PBC_INFORMATION15,p_PBC_INFORMATION16,p_PBC_INFORMATION17,
	     p_PBC_INFORMATION18,p_PBC_INFORMATION19,p_PBC_INFORMATION20,
	     p_PBC_INFORMATION21,p_PBC_INFORMATION22,p_PBC_INFORMATION23,
	     p_PBC_INFORMATION24,p_PBC_INFORMATION25,p_PBC_INFORMATION26,
	     p_PBC_INFORMATION27,p_PBC_INFORMATION28,p_PBC_INFORMATION29,
	     p_PBC_INFORMATION30,to_number(p_OVN)
	    );

Line: 345


SELECT balance_type_id
FROM   PAY_BALANCE_TYPES
WHERE  balance_name =p_BALANCE_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);--added

Line: 357


select balance_category_id
from pay_balance_categories_f
where category_name =p_CATEGORY_NAME
and effective_start_date =to_date(p_ESD,'YYYY/MM/DD')
and effective_end_date =to_date(p_EED,'YYYY/MM/DD')
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)  --Bug 5044079
and (business_group_id =p_bg_id or business_group_id is null);  --Bug 5044079

Line: 388


			update pay_balance_types
			set balance_category_id =l_balance_category_id
			where balance_type_id=l_balance_type_id;

Line: 422


select attribute_id
from pay_balance_attributes
where attribute_id=p_attrib_id;

Line: 428


select attribute_id
from pay_bal_attribute_defaults
where attribute_id=p_attrib_id;

Line: 436


SELECT attribute_id,
       attribute_name,
       alterable,
       legislation_code,
       DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
FROM   PAY_BAL_ATTRIBUTE_DEFINITIONS
WHERE  attribute_name = p_ATTRIBUTE_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
AND    (business_group_id=p_bg_id OR business_group_id is NULL);

Line: 498


	   --ALTERABLE flag needs to be updated
	   if(l_alterable <> p_ALTERABLE) then
	      if((l_business_group_id is null and l_legislation_code is not
	          null) AND (l_ALTERABLE ='Y' and p_ALTERABLE='N')) then
	         --its a startup definition
 	         --updating from Y to N ...so check if child rows exist
	         open csr_pba_id(l_attribute_id);

Line: 530


	         update PAY_BAL_ATTRIBUTE_DEFINITIONS
	         set ALTERABLE =p_ALTERABLE
	         where attribute_id=l_attribute_id;

Line: 537


	      --update

	         update PAY_BAL_ATTRIBUTE_DEFINITIONS
	         set ALTERABLE =p_ALTERABLE
	         where attribute_id=l_attribute_id;

Line: 597


select attribute_id
from pay_bal_attribute_definitions
where  attribute_name = p_ATTRIBUTE_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 608


select defined_balance_id
from pay_defined_balances
where balance_type_id =p_balance_type_id
and balance_dimension_id =p_balance_dimension_id
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 619


select balance_type_id
from pay_balance_types
where balance_name =p_BALANCE_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 629


select balance_dimension_id
from pay_balance_dimensions
where dimension_name =p_DIMENSION_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 721


	SELECT balance_attribute_id,
	       attribute_id,
	       defined_balance_id,
	       legislation_code,
	       DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
	INTO   l_balance_attribute_id,
	       l_attribute_id,
	       l_defined_balance_id,
	       l_legislation_code,
	       l_owner
	FROM   PAY_BALANCE_ATTRIBUTES
	WHERE  attribute_id = l_attribute_id
	AND    defined_balance_id = l_defined_balance_id
	AND    (business_group_id =l_business_group_id
		OR business_group_id is null);

Line: 807


select balance_dimension_id
from pay_balance_dimensions
where dimension_name=p_DIMENSION_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 817


select attribute_id
from pay_bal_attribute_definitions
where attribute_name =p_ATTRIBUTE_NAME
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
and (business_group_id =p_bg_id or business_group_id is null);

Line: 828


select balance_category_id
from pay_balance_categories_f
where category_name =p_CATEGORY_NAME
and effective_start_date =to_date(p_ESD,'YYYY/MM/DD')
and effective_end_date =to_date(p_EED,'YYYY/MM/DD')
AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);

Line: 907


	SELECT bal_attribute_default_id,
	       balance_category_id,
	       balance_dimension_id,
	       attribute_id,
	       legislation_code,
	       DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
	INTO   l_bal_attribute_default_id,
	       l_balance_category_id,
	       l_balance_dimension_id,
	       l_attribute_id,
	       l_legislation_code,
	       l_owner
       	FROM   PAY_BAL_ATTRIBUTE_DEFAULTS
	WHERE  balance_category_id = l_balance_category_id
        AND    attribute_id = l_attribute_id
	AND    balance_dimension_id =l_balance_dimension_id;

Line: 976


select business_group_id
from   per_business_groups
where  upper(name) = p_bg_name;

Line: 982


select balance_category_id
from   pay_balance_categories_f
where  category_name = p_category_name
and    nvl(business_group_id, -1) = nvl(p_bg_id, -1)
and    nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE');

Line: 1011


  update pay_balance_categories_f
  set user_category_name = p_user_category_name
  where balance_category_id = l_bal_cat_id
  and   userenv('LANG') = (select language_code
                           from   fnd_languages
                           where  installed_flag = 'B');

Line: 1033


select business_group_id
from   per_business_groups
where  upper(name) = p_bg_name;

Line: 1039


select attribute_id
from   pay_bal_attribute_definitions
where  attribute_name = p_attribute_name
and    nvl(business_group_id, -1) = nvl(p_bg_id, -1)
and    nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE');

Line: 1068


  update pay_bal_attribute_definitions
  set user_attribute_name = p_user_attribute_name
  where attribute_id = l_att_def_id
  and   userenv('LANG') = (select language_code
                           from   fnd_languages
                           where  installed_flag = 'B');