[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lookup_code, meaning
FROM CN_lookups
WHERE lookup_type = 'PERIOD_CLOSING_STATUS';
SELECT set_of_books_id,
period_set_id,
period_type_id
FROM cn_repositories
WHERE repository_id > 0
AND application_type = 'CN';
SELECT cn.period_name,
cn.period_year,
cn.start_date,
cn.end_date,
'O' closing_status_meaning,
cp.meaning processing_status,
p_freeze_flag,
cn.object_version_number
FROM cn_period_statuses cn,
cn_lookups cp
WHERE cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
AND cn.period_set_id = l_period_set_id
AND cn.period_type_id = l_period_type_id
AND cn.period_name = p_period_name
UNION
SELECT gl.period_name,
gl.period_year,
gl.start_date,
gl.end_date,
'O' closing_status_meaning,
cp.meaning processing_status,
p_freeze_flag,
cn.object_version_number
FROM gl_period_statuses gl,
cn_period_statuses cn,
cn_lookups cp
WHERE gl.set_of_books_id = l_set_of_books_id
AND gl.application_id = 283
AND gl.adjustment_period_flag = 'N'
AND gl.period_name = cn.period_name(+)
AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
AND cn.period_type_id(+) = l_period_type_id
AND cn.period_set_id(+) = l_period_set_id
AND gl.period_name = p_period_name
AND not exists
(select 's' from cn_period_statuses cn1
where gl.period_name = cn1.period_name
and cn1.period_set_id = l_period_set_id
and cn1.period_type_id = l_period_type_id);
update_acc_periods
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_org_id => l_org_id,
p_acc_period_tbl => l_acc_period_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
update cn_period_statuses
set processing_status_code = 'FAILED'
where processing_status_code = 'PROCESSING'
and period_name = l_period_rec.period_name;
PROCEDURE Update_Acc_Periods
(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_acc_period_tbl IN acc_period_tbl_type ,
p_org_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Acc_Periods';
l_update_flag VARCHAR2(1) := 'N';
SELECT r.set_of_books_id,
r.repository_id,
ps.period_set_id,
ps.period_set_name,
pt.period_type,
pt.period_type_id
FROM cn_repositories r,
cn_period_sets ps,
cn_period_types pt
WHERE r.repository_id > 0
AND r.application_type = 'CN'
AND r.period_set_id = ps.period_set_id
AND r.period_type_id = pt.period_type_id
AND r.org_id = p_org_id -- MOAC Change
AND r.org_id = ps.org_id -- MOAC Change
AND ps.org_id = pt.org_id; -- MOAC Change
SELECT nvl(cn.org_id, p_org_id) org_id, -- MOAC Change
gl.quarter_num,
gl.period_num,
cn.object_version_number,
gl.closing_status,
cn.freeze_flag
FROM gl_period_statuses gl, cn_period_statuses cn
WHERE gl.application_id = 283
AND gl.set_of_books_id = l_set_of_books_id
AND gl.period_name = p_period_name
AND gl.adjustment_period_flag = 'N'
AND gl.period_name = cn.period_name(+)
AND cn.org_id(+) = p_org_id -- MOAC Change
FOR UPDATE OF gl.closing_status nowait;
SELECT cn.period_status, processing_status_code
FROM cn_period_statuses cn
WHERE cn.period_name = p_period_name
AND cn.period_year = p_period_year
AND cn.org_id = p_org_id -- MOAC Change
AND period_type_id = l_period_type_id
AND period_set_id = l_period_set_id;
SELECT count(gl.closing_status)
FROM gl_period_statuses gl
WHERE gl.set_of_books_id = l_set_of_books_id
AND gl.application_id = 283
AND gl.adjustment_period_flag = 'N'
--AND gl.period_year = p_period_year
AND gl.start_date < p_start_date
ORDER BY gl.start_date;
SELECT 'N', start_date
FROM gl_period_statuses gl
WHERE gl.set_of_books_id = l_set_of_books_id
AND gl.application_id = 283
AND gl.adjustment_period_flag = 'N'
AND start_date < p_start_date
and not exists
(select 's' from cn_period_statuses cn1
where gl.period_name = cn1.period_name
and cn1.period_set_id = l_period_set_id
and cn1.PERIOD_TYPE_id = l_period_type_id
and cn1.org_id = p_org_id) -- MOAC Change
UNION
SELECT cn.period_status,start_date
FROM cn_period_statuses cn
WHERE cn.period_set_id = l_period_set_id
AND cn.period_type_id = l_period_type_id
AND cn.org_id = p_org_id -- MOAC Change
AND cn.start_date < p_start_date
ORDER BY start_date DESC;
SELECT count(gl.closing_status)
FROM gl_period_statuses gl
WHERE gl.set_of_books_id = l_set_of_books_id
AND gl.application_id = 283
AND gl.adjustment_period_flag = 'N'
--AND gl.period_year = p_period_year
AND gl.start_date > p_start_date
ORDER BY gl.start_date;
SELECT cn.period_status
FROM cn_period_statuses cn
WHERE cn.period_set_id = l_period_set_id
AND cn.PERIOD_TYPE_id = l_period_type_id
AND cn.org_id = p_org_id -- MOAC Change
AND cn.start_date > p_start_date
ORDER BY cn.start_date;
UPDATE cn_repositories
SET status = 'A'
WHERE repository_id > 0
AND application_type = 'CN'
AND org_id = p_org_id; -- MOAC Change
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
l_update_flag := 'Y';
-- update the existing gl_period_statuses record
IF (i > p_acc_period_tbl.first) THEN
l_pre_status := get_closing_status(p_acc_period_tbl(i-1).closing_status_meaning);
cn_periods_api.update_gl_status(p_org_id, -- MOAC Change
p_acc_period_tbl(i).period_name,
l_closing_status,
'Y',
283,
l_set_of_books_id,
p_acc_period_tbl(i).freeze_flag,
sysdate,
fnd_global.login_id,
fnd_global.user_id);
IF (l_update_flag = 'N') THEN
fnd_message.set_name('CN', 'CN_NO_CHANGES');
END update_acc_periods;
SELECT period_year
FROM gl_period_statuses
WHERE trunc(sysdate) BETWEEN start_date AND end_date
AND application_id = 283
AND adjustment_period_flag = 'N'
AND set_of_books_id = (SELECT set_of_books_id
FROM cn_repositories
WHERE repository_id > 0
AND application_type = 'CN')
AND ROWNUM = 1;
SELECT max(period_year)
FROM gl_period_statuses
WHERE application_id = 283
AND adjustment_period_flag = 'N'
AND set_of_books_id = (SELECT set_of_books_id
FROM cn_repositories
WHERE repository_id > 0
AND application_type = 'CN')
;
SELECT status,
set_of_books_id,
period_set_id,
period_type_id
FROM cn_repositories
WHERE repository_id > 0
AND application_type = 'CN';
SELECT period_set_name
FROM cn_period_sets
WHERE period_set_id = l_period_set_id;
SELECT period_type
FROM cn_period_types
WHERE period_type_id = l_period_type_id;
SELECT cn.period_name,
cn.period_year,
cn.start_date,
cn.end_date,
gp.meaning closing_status_meaning,
cp.meaning processing_status,
cn.freeze_flag,
cn.object_version_number
FROM cn_period_statuses cn,
cn_lookups gp,
cn_lookups cp
WHERE
gp.lookup_type = 'PERIOD_CLOSING_STATUS'
AND gp.lookup_code = cn.PERIOD_STATUS
AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
AND cn.period_year = nvl(p_year, l_current_year)
AND cn.period_set_id = l_period_set_id
AND cn.period_type_id = l_period_type_id
UNION
SELECT gl.period_name,
gl.period_year,
gl.start_date,
gl.end_date,
gp.meaning closing_status_meaning,
cp.meaning processing_status,
cn.freeze_flag,
cn.object_version_number
FROM gl_period_statuses gl,
cn_period_statuses cn,
cn_lookups gp,
cn_lookups cp
WHERE gl.set_of_books_id = l_set_of_books_id
AND gl.application_id = 283
AND gl.adjustment_period_flag = 'N'
AND gl.period_name = cn.period_name(+)
AND gp.lookup_type = 'PERIOD_CLOSING_STATUS'
AND gp.lookup_code = DECODE(gl.CLOSING_STATUS,'N','N','X')
AND cp.lookup_type = 'PERIOD_PROCESSING_STATUS'
AND cp.lookup_code = nvl(cn.processing_status_code, 'CLEAN')
AND gl.period_year = nvl(p_year, l_current_year)
AND cn.period_type_id(+) = l_period_type_id
AND cn.period_set_id (+) = l_period_set_id
and not exists
(select 's' from cn_period_statuses cn1
where gl.period_name = cn1.period_name
and cn1.period_set_id = l_period_set_id
and cn1.period_type_id = l_period_type_id)
order by 2,3;