[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TERRITORY_CODE||' - '||territory_short_name territory_short_name
FROM fnd_territories_vl
WHERE UPPER(TERRITORY_CODE||' - '||territory_short_name) NOT IN
( SELECT UPPER(row_low_range_or_name)
FROM pay_user_rows_f
WHERE user_table_id=p_table_id
AND business_group_id = p_bg )
ORDER BY TERRITORY_CODE;
CURSOR c_update_territory(p_bg NUMBER,p_table_id NUMBER) IS
SELECT user_row_id
,display_sequence
,object_version_number
,UPPER(row_low_range_or_name) row_low_range_or_name
,row_high_range
FROM pay_user_rows_f
WHERE user_table_id=p_table_id
AND business_group_id = p_bg
AND UPPER(row_low_range_or_name) IN
(SELECT UPPER(territory_short_name)
FROM fnd_territories_vl );
SELECT UPPER(TERRITORY_CODE||' - '||territory_short_name) territory_short_name
FROM fnd_territories_vl
WHERE UPPER(territory_short_name) = p_row_low_range_or_name ;
SELECT user_table_id
INTO l_user_table_id
FROM pay_user_tables
WHERE user_table_name='PER DIEM OVERSEAS NORMAL AMOUNT'
AND legislation_code='SE';
SELECT user_column_id
INTO l_user_column_id
FROM pay_user_columns
WHERE user_column_name='NORMAL_AMOUNT'
AND legislation_code='SE';
FOR tc_rec IN c_update_territory(p_business_group_id,l_user_table_id)
LOOP
l_effective_date := TO_DATE('01010001','DDMMYYYY');
These are the parameters to call the PAY_USER_ROW_API.UPDATE_USER_ROW
P_VALIDATE BOOLEAN IN DEFAULT
P_EFFECTIVE_DATE DATE IN
P_DATETRACK_UPDATE_MODE VARCHAR2 IN
P_USER_ROW_ID NUMBER IN
P_DISPLAY_SEQUENCE NUMBER IN/OUT
P_OBJECT_VERSION_NUMBER NUMBER IN/OUT
P_ROW_LOW_RANGE_OR_NAME VARCHAR2 IN DEFAULT
P_DISABLE_RANGE_OVERLAP_CHECK BOOLEAN IN DEFAULT
P_DISABLE_UNITS_CHECK BOOLEAN IN DEFAULT
P_ROW_HIGH_RANGE VARCHAR2 IN DEFAULT
P_EFFECTIVE_START_DATE DATE OUT
P_EFFECTIVE_END_DATE DATE OUT
-------------------------------------------------------------
*/
OPEN c_chk_territory(tc_rec.row_low_range_or_name);
PAY_USER_ROW_API.UPDATE_USER_ROW
( P_VALIDATE => FALSE
, P_EFFECTIVE_DATE => l_effective_date
, P_DATETRACK_UPDATE_MODE => 'CORRECTION'
, P_USER_ROW_ID => tc_rec.user_row_id
, P_DISPLAY_SEQUENCE => tc_rec.display_sequence
, P_OBJECT_VERSION_NUMBER => tc_rec.object_version_number
, P_ROW_LOW_RANGE_OR_NAME => l_row_low_range_or_name
, P_DISABLE_RANGE_OVERLAP_CHECK => FALSE
, P_DISABLE_UNITS_CHECK => FALSE
, P_ROW_HIGH_RANGE => tc_rec.row_high_range
, P_EFFECTIVE_START_DATE => l_effective_start_date
, P_EFFECTIVE_END_DATE => l_effective_end_date
);
SELECT max(DISPLAY_SEQUENCE)
INTO l_display_sequence
FROM pay_user_rows_f
WHERE user_table_id=l_user_table_id
AND business_group_id = p_business_group_id;
-- Call the Procedure pay_user_row_api.create_user_row to insert
--rows into the Table
--
l_display_sequence := l_display_sequence + 1 ;
PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW
p_rowid in out varchar2,
p_user_column_instance_id in out number,
p_effective_start_date in date,
p_effective_end_date in date,
p_user_row_id in number,
p_user_column_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_legislation_subgroup in varchar2,
p_value in varchar2 )
-------------------------------------------------------------
*/
-- Call the Procedure PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW to
-- insert rows into the Table
--
l_row_id1:=TO_CHAR( l_row_id);
PAY_USER_COLUMN_INSTANCES_PKG.insert_row
( p_rowid => l_row_id1
,p_user_column_instance_id => l_user_column_instance_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_user_row_id => l_user_row_id
,p_user_column_id => l_user_column_id
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_value => NULL
);