[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FSP.set_of_books_id
INTO l_set_of_books_id
FROM FINANCIALS_SYSTEM_PARAMETERS FSP;
SELECT PO_SESSION_GT_S.nextval INTO l_date_key FROM DUAL;
INSERT INTO PO_SESSION_GT
(key, date1)
VALUES
(l_date_key, p_date_tbl(i))
RETURNING ROWIDTOCHAR
(rowid) BULK COLLECT INTO l_rowid_tbl;
UPDATE PO_SESSION_GT SES
SET (char1 -- period_name
, num1 -- period_year
, num2 -- period_num
, num3 -- quarter_num
) = (SELECT GL_PS.period_name,
GL_PS.period_year,
GL_PS.period_num,
GL_PS.quarter_num
FROM GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_set_of_books_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
-- GL period conditions
-- bug 5498063
-- AND GL_PS.closing_status IN ('O','F') open or future-enterable
AND ((l_validate_gl_period IN ('Y','R') and
GL_PS.closing_status IN ('O', 'F')) OR
(l_validate_gl_period = 'N'))
-- bug 5498063
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND GL_PS.period_year <= SOB.latest_encumbrance_year
-- PO period conditions
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
-- Date logic (to include roll-forward, roll-backward?)
-- See PO_ENCUMBRANCE_PREPROCESSING.find_open_period
AND TRUNC(SES.date1) BETWEEN TRUNC(GL_PS.start_date) AND
TRUNC(GL_PS.end_date))
WHERE SES.rowid = CHARTOROWID(l_rowid_tbl(i))
RETURNING SES.char1 -- period_name
, SES.num1 -- period_year
, SES.num2 -- period_num
, SES.num3 -- quarter_num
BULK COLLECT INTO x_period_name_tbl, x_period_year_tbl, x_period_num_tbl, x_quarter_num_tbl;
SELECT FND_API.G_TRUE
INTO x_invalid_period_flag
FROM PO_SESSION_GT SES
WHERE SES.key = l_date_key
AND SES.char1 IS NULL
AND rownum = 1;
SELECT GL_PS.period_name
into x_gl_period
FROM GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_sob_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
AND ((l_validate_gl_period IN ('Y','R')
and GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND GL_PS.period_year <= SOB.latest_encumbrance_year
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND TRUNC(Nvl(x_gl_date, SYSDATE)) BETWEEN TRUNC(GL_PS.start_date) AND
TRUNC(GL_PS.end_date)
and rownum = 1;
SELECT GL_PS.period_name
into x_gl_period
FROM GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_sob_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
AND ((l_validate_gl_period IN ('Y','R')
and GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND GL_PS.period_year <= SOB.latest_encumbrance_year
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND TRUNC(sysdate) BETWEEN TRUNC(GL_PS.start_date) AND
TRUNC(GL_PS.end_date)
and rownum = 1;
SELECT latest_period_name, latest_open_date
into x_gl_period, x_gl_date
FROM (
SELECT GL_PS.period_year,
GL_PS.period_num,
GL_PS.quarter_num,
gl_ps.period_name latest_period_name,
TRUNC(GL_PS.start_date) latest_open_date
FROM GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_sob_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
AND ((l_validate_gl_period IN ('Y','R')
AND GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND GL_PS.period_year <= SOB.latest_encumbrance_year
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND TRUNC(GL_PS.start_date) >=
Trunc(Nvl(x_gl_date, SYSDATE))
ORDER BY GL_PS.period_year ASC,
GL_PS.period_num ASC,
GL_PS.quarter_num ASC)
where ROWNUM = 1;
SELECT latest_period_name, latest_open_date
into x_gl_period, x_gl_date
FROM (
SELECT GL_PS.period_year,
GL_PS.period_num,
GL_PS.quarter_num,
gl_ps.period_name latest_period_name,
TRUNC(GL_PS.start_date) latest_open_date
FROM GL_PERIOD_STATUSES GL_PS,
GL_PERIOD_STATUSES PO_PS,
GL_SETS_OF_BOOKS SOB
WHERE SOB.set_of_books_id = l_sob_id
AND GL_PS.application_id = 101
AND PO_PS.application_id = 201
AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
AND GL_PS.period_name = PO_PS.period_name --JOIN
AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND ((l_validate_gl_period IN ('Y','R')
AND GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
AND GL_PS.period_year <=
SOB.latest_encumbrance_year
AND PO_PS.closing_status = 'O' -- open
AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
AND TRUNC(GL_PS.start_date) <
Trunc(Nvl(x_gl_date, SYSDATE))
ORDER BY GL_PS.period_year desc,
GL_PS.period_num desc,
GL_PS.quarter_num DESC)
where ROWNUM = 1;