[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/**if period_mask_id is null, then we need to INSERT
*Populate both the master record in pa_period_masks_s + pa_period_masks_tl
*and detail record in pa_period_mask_details*/
IF p_period_mask_id = 0 THEN
SELECT pa_period_masks_s.nextval INTO l_period_masks_s FROM DUAL;
SELECT userenv('LANG') INTO l_language FROM dual;
INSERT INTO pa_period_masks_b (
PERIOD_MASK_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
TIME_PHASE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
RECORD_VERSION_NUMBER,
PRE_DEFINED_FLAG)
VALUES(
l_period_masks_s,
p_effective_start_date,
p_effective_end_date,
p_time_phase_code,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.LOGIN_ID,
sysdate,
1,
'N');
INSERT INTO pa_period_masks_tl (
PERIOD_MASK_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
select l_period_masks_s,
p_name,
p_description,
L.LANGUAGE_CODE, /* Bug 6275098*/
l_language,
sysdate,
FND_GLOBAL.LOGIN_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID /*Changes for Bug 6275098 */
from FND_LANGUAGES L where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select null from PA_PERIOD_MASKS_TL T
where T.PERIOD_MASK_ID = l_period_masks_s
and T.LANGUAGE = L.LANGUAGE_CODE);
/**if period_mask_id is not null, then we need to UPDATE
*Update both the master record in pa_period_masks_s + pa_period_masks_tl
*and detail record in pa_period_mask_details*/
ELSE
UPDATE pa_period_masks_b
SET effective_start_date = p_effective_start_date,
effective_end_date = p_effective_end_date,
time_phase_code = p_time_phase_code,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.LOGIN_ID,
last_update_date = sysdate,
record_version_number = record_version_number + 1
WHERE period_mask_id = p_period_mask_id
and record_version_number = p_record_version_number;
UPDATE pa_period_masks_tl
SET name = p_name,
description = p_description,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_updated_by = FND_GLOBAL.LOGIN_ID,
last_update_date = sysdate
WHERE period_mask_id = p_period_mask_id;
DELETE FROM pa_period_mask_details
WHERE period_mask_id = p_period_mask_id;
SELECT from_anchor_position,num_of_periods FROM
pa_fp_period_mask_tmp WHERE from_anchor_position > 0 ORDER BY from_anchor_position;
SELECT from_anchor_position,num_of_periods FROM
pa_fp_period_mask_tmp WHERE from_anchor_position < 0 ORDER BY from_anchor_position desc;
DELETE FROM pa_fp_period_mask_tmp;
(p_msg => 'Before inserting into temporary table',
p_module_name => l_module_name,
p_log_level => 5);
INSERT INTO pa_fp_period_mask_tmp(NUM_OF_PERIODS,ANCHOR_PERIOD_FLAG,FROM_ANCHOR_POSITION)
VALUES ( p_num_of_periods(i),p_anchor_period_flag(i),p_from_anchor_position(i));
(p_msg => 'After inserting into temporary table',
p_module_name => l_module_name,
p_log_level => 5);
select count(*) into l_count
from pa_fp_period_mask_tmp
where anchor_period_flag = 'Y';
update pa_fp_period_mask_tmp set
anchor_period_flag = 'Y' where
from_anchor_position = 1;
SELECT from_anchor_position,num_of_periods
INTO l_from_anchor_position,l_first_no_of_pds
FROM pa_fp_period_mask_tmp
WHERE anchor_period_flag = 'Y';
UPDATE pa_fp_period_mask_tmp
SET from_anchor_position = from_anchor_position - l_from_anchor_position;
UPDATE pa_fp_period_mask_tmp
SET from_anchor_start = 0, from_anchor_end = num_of_periods - 1
WHERE anchor_period_flag = 'Y';
UPDATE pa_fp_period_mask_tmp
SET from_anchor_start = l_tmp, from_anchor_end = l_tmp_end
WHERE from_anchor_position = succ_pd_rec.from_anchor_position;
UPDATE pa_fp_period_mask_tmp
SET from_anchor_start = l_tmp, from_anchor_end = l_tmp_end
WHERE from_anchor_position = prec_pd_rec.from_anchor_position;
(p_msg => 'Before inserting additional records in the temporary table',
p_module_name => l_module_name,
p_log_level => 5);
--Inserting additional records in the temp table to allow additional periods before/after the project periods
INSERT INTO pa_fp_period_mask_tmp(num_of_periods, anchor_period_flag,
from_anchor_start,from_anchor_end,from_anchor_position
)
VALUES (0,'N',-99999,-99999,-99999);
INSERT INTO pa_fp_period_mask_tmp(num_of_periods,
anchor_period_flag,from_anchor_start,from_anchor_end,from_anchor_position)
VALUES (0,'N',99999,99999,99999 );
(p_msg => 'After inserting additional records in the temporary table',
p_module_name => l_module_name,
p_log_level => 5);
INSERT INTO pa_period_mask_details(PERIOD_MASK_ID,
num_of_periods,
anchor_period_flag,
from_anchor_start,
from_anchor_end,
from_anchor_position,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LAST_UPDATE_DATE )
(SELECT p_period_mask_id,
trunc(num_of_periods),
anchor_period_flag,
from_anchor_start,
from_anchor_end,
from_anchor_position,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.LOGIN_ID,
sysdate FROM pa_fp_period_mask_tmp );
PROCEDURE delete_period_mask( p_period_mask_id IN NUMBER,
p_record_version_number IN NUMBER,
p_init_msg_flag IN VARCHAR2,
p_commit_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_PERIOD_MASKS_PUB.delete_period_mask';
pa_debug.set_curr_function( p_function => 'delete_period_mask',
p_debug_mode => p_pa_debug_mode );
DELETE FROM pa_period_masks_b
WHERE period_mask_id = p_period_mask_id
and record_version_number = p_record_version_number;
DELETE FROM pa_period_masks_tl
WHERE period_mask_id = p_period_mask_id;
DELETE FROM pa_period_mask_details
WHERE period_mask_id = p_period_mask_id;
p_procedure_name => 'delete_period_mask');
END delete_period_mask;