[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ffh.flex_value_set_id, ffh.FLEX_VALUE, SUMMARY_FLAG
from FND_FLEX_VALUE_CHILDREN_V ffh
where ffh.flex_value_set_id = c_valueset_id
and ffh.PARENT_FLEX_VALUE = c_bsv
order by ffh.FLEX_VALUE;
SELECT fifsv.FLEX_VALUE_SET_ID
into l_flex_valueset_id
FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND fifsv.ID_FLEX_NUM in
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id);
SELECT SUMMARY_FLAG
INTO lv_summary
FROM FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
AND ffv.FLEX_VALUE_SET_ID = l_flex_valueset_id
AND ffv.FLEX_VALUE = l_bsv_temp;
SELECT start_date
INTO l_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_start
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT end_date
INTO l_end_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_end
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT Ja_Cn_Utility.GET_ACCOUNTING_SEGMENT(GBS.CODE_COMBINATION_ID) ACCOUNT_SEG,
GBS.CURRENCY_CODE,
GBS.PERIOD_YEAR,
GBS.PERIOD_NUM,
--SUM( DECODE(GBS.CURRENCY_CODE,GLS.CURRENCY_CODE,GBS.BEGIN_BALANCE_DR_BEQ,GBS.BEGIN_BALANCE_DR) ) BEGIN_BALANCE_DR,
-- SUM( GBS.BEGIN_BALANCE_DR_BEQ )BEGIN_BALANCE_DR_BEQ,
--SUM( DECODE(GBS.CURRENCY_CODE,GLS.CURRENCY_CODE,GBS.BEGIN_BALANCE_CR_BEQ,GBS.BEGIN_BALANCE_CR) ) BEGIN_BALANCE_CR,
--SUM( GBS.BEGIN_BALANCE_CR_BEQ )BEGIN_BALANCE_CR_BEQ,
SUM((DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.BEGIN_BALANCE_DR_BEQ,
GBS.BEGIN_BALANCE_DR) -
DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.BEGIN_BALANCE_CR_BEQ,
GBS.BEGIN_BALANCE_CR))) BEGIN_BALANCE,
SUM((GBS.BEGIN_BALANCE_DR_BEQ - GBS.BEGIN_BALANCE_CR_BEQ)) BEGIN_BALANCE_BEQ,
SUM(DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.PERIOD_NET_DR_BEQ,
GBS.PERIOD_NET_DR)) PERIOD_NET_DR,
SUM(GBS.PERIOD_NET_DR_BEQ) PERIOD_NET_DR_BEQ,
SUM(DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.PERIOD_NET_CR_BEQ,
GBS.PERIOD_NET_CR)) PERIOD_NET_CR,
SUM(GBS.PERIOD_NET_CR_BEQ) PERIOD_NET_CR_BEQ,
SUM((DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.BEGIN_BALANCE_DR_BEQ,
GBS.BEGIN_BALANCE_DR) -
DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.BEGIN_BALANCE_CR_BEQ,
GBS.BEGIN_BALANCE_CR) +
DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.PERIOD_NET_DR_BEQ,
GBS.PERIOD_NET_DR) -
DECODE(GBS.CURRENCY_CODE,
GLS.CURRENCY_CODE,
GBS.PERIOD_NET_CR_BEQ,
GBS.PERIOD_NET_CR))) YTD,
SUM((GBS.BEGIN_BALANCE_DR_BEQ - GBS.BEGIN_BALANCE_CR_BEQ +
GBS.PERIOD_NET_DR_BEQ - GBS.PERIOD_NET_CR_BEQ)) YTD_BEQ
FROM GL_BALANCES GBS, GL_LEDGERS GLS
WHERE GBS.LEDGER_ID = GLS.LEDGER_ID
AND GBS.LEDGER_ID =pn_ledger_id
AND Ja_Cn_Utility.get_balancing_segment(GBS.CODE_COMBINATION_ID)=pv_bsv
AND PERIOD_NAME in
(SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN c_from AND c_to) AND
(end_date BETWEEN c_from AND c_to)))
GROUP BY Ja_Cn_Utility.GET_ACCOUNTING_SEGMENT(GBS.CODE_COMBINATION_ID),
GBS.CURRENCY_CODE,
GBS.PERIOD_YEAR,
GBS.PERIOD_NUM;
INSERT INTO JA_CN_BANK_BALANCE_GT
(ACCOUNT_SEG,
CURRENCY_CODE,
PERIOD_YEAR,
PERIOD_NUM,
BEGIN_BALANCE,
BEGIN_BALANCE_BEQ,
PERIOD_NET_DR,
PERIOD_NET_DR_BEQ,
PERIOD_NET_CR,
PERIOD_NET_CR_BEQ,
YTD_BALANCE,
YTD_BALANCE_BEQ)
VALUES
(l_gl_bal.account_seg,
l_gl_bal.currency_code,
l_gl_bal.period_year,
l_gl_bal.period_num,
l_gl_bal.begin_balance,
l_gl_bal.begin_balance_beq,
l_gl_bal.period_net_dr,
l_gl_bal.period_net_dr_beq,
l_gl_bal.period_net_cr,
l_gl_bal.period_net_cr_beq,
l_gl_bal.ytd,
l_gl_bal.ytd_beq);
SELECT ACCOUNT_SEG,
CURRENCY_CODE,
PERIOD_YEAR,
PERIOD_NUM,
SUM(BEGIN_BALANCE) BEGIN_BALANCE,
SUM(BEGIN_BALANCE_BEQ) BEGIN_BALANCE_BEQ,
SUM(PERIOD_NET_DR) PERIOD_NET_DR,
SUM(PERIOD_NET_DR_BEQ) PERIOD_NET_DR_BEQ,
SUM(PERIOD_NET_CR) PERIOD_NET_CR,
SUM(PERIOD_NET_CR_BEQ) PERIOD_NET_CR_BEQ,
SUM(YTD_BALANCE) YTD,
SUM(YTD_BALANCE_BEQ) YTD_BEQ
FROM JA_CN_BANK_BALANCE_GT
GROUP BY ACCOUNT_SEG,
CURRENCY_CODE,
PERIOD_YEAR,
PERIOD_NUM
HAVING SUM(BEGIN_BALANCE)<>0 OR SUM(PERIOD_NET_DR)<>0 OR SUM(PERIOD_NET_CR)<>0
ORDER BY PERIOD_NUM,ACCOUNT_SEG;
SELECT JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID) ACCOUNT_SEG,
GJH.CURRENCY_CODE,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYY') PERIOD_YEAR,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'MM') PERIOD_NUM,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYYMMDD') JOURNAL_DATE,
SUM(GJL.ENTERED_DR) ENTERED_DR,
SUM(GJL.ACCOUNTED_DR) ACCOUNTED_DR ,
SUM(GJL.ENTERED_CR) ENTERED_CR,
SUM(GJL.ACCOUNTED_CR) ACCOUNTED_CR
FROM GL_JE_HEADERS GJH, GL_JE_LINES GJL
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND gjh.status = 'P'
AND JA_CN_UTILITY.get_balancing_segment(GJL.CODE_COMBINATION_ID) = pv_bsv
AND GJH.LEDGER_ID = pn_ledger_id
AND GJH.PERIOD_NAME in
(SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN c_from AND c_to) AND
(end_date BETWEEN c_from AND c_to)))
GROUP BY JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID),GJH.DEFAULT_EFFECTIVE_DATE ,GJH.CURRENCY_CODE;
INSERT INTO JA_CN_BANK_AVG_BAL_GT
(ACCOUNT_SEG,
CURRENCY_CODE,
PERIOD_YEAR,
PERIOD_NUM,
JOURNAL_DATE,
ENTERED_DR,
ACCOUNTED_DR,
ENTERED_CR,
ACCOUNTED_CR)
VALUES
(l_journal.ACCOUNT_SEG,
l_journal.CURRENCY_CODE,
l_journal.PERIOD_YEAR,
l_journal.PERIOD_NUM,
l_journal.JOURNAL_DATE,
l_journal.ENTERED_DR,
l_journal.ACCOUNTED_DR,
l_journal.ENTERED_CR,
l_journal.ACCOUNTED_CR);
SELECT ACCOUNT_SEG,
CURRENCY_CODE,
PERIOD_YEAR,
PERIOD_NUM,
JOURNAL_DATE,
SUM( ENTERED_DR) ENTERED_DR,
SUM( ACCOUNTED_DR) ACCOUNTED_DR ,
SUM( ENTERED_CR) ENTERED_CR,
SUM( ACCOUNTED_CR) ACCOUNTED_CR
FROM JA_CN_BANK_AVG_BAL_GT
GROUP BY ACCOUNT_SEG,JOURNAL_DATE,CURRENCY_CODE,PERIOD_YEAR,PERIOD_NUM
ORDER BY JOURNAL_DATE,ACCOUNT_SEG;
SELECT TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYYMMDD') JOURNAL_DATE,
GJH.DESCRIPTION,
GJL.JE_LINE_NUM,
JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID) ACCOUNT_SEG,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR,
GJL.ACCOUNTED_DR,
GJL.ENTERED_CR,
GJL.ACCOUNTED_CR,
'0' ATTACHMENT,
FU.USER_NAME CREATOR,
FU2.USER_NAME REVIEWER,
FU2.USER_NAME POSTER,
'1' POSTED_FLAG,
'0' CANCEL_FLAG,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'MM') PERIOD_NUM,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYY') PERIOD_YEAR,
GJH.JE_HEADER_ID,
GJH.PERIOD_NAME,
GJH.JE_BATCH_ID
FROM GL_JE_HEADERS GJH, GL_JE_LINES GJL, FND_USER FU, FND_USER FU2
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND gjh.status = 'P'
AND FU.USER_ID = GJL.CREATED_BY
AND FU2.USER_ID = GJL.LAST_UPDATED_BY
AND JA_CN_UTILITY.get_balancing_segment(GJL.CODE_COMBINATION_ID) = pv_bsv
AND GJH.LEDGER_ID = pn_ledger_id
AND GJH.PERIOD_NAME in
(SELECT period_name
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN c_from AND c_to) AND
(end_date BETWEEN c_from AND c_to)))
ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
gjh.default_effective_date ASC,
gjh.posted_date ASC,
gjh.je_header_id ASC,
GJL.JE_LINE_NUM ASC;
SELECT (SELECT NVL(VOUCHER_NUM, '') FROM JA_CN_BANK_JOURNAL
WHERE JE_HEADER_ID = l_journal.Je_Header_Id AND JE_LINE_NUM = l_journal.Je_Line_Num )
INTO l_vouchernum
FROM DUAL;
l_vouchernum_t :=JA_CN_UPDATE_BANK_SEQ_PKG.Fetch_JL_Seq(pn_legal_entity_id ,
pv_bsv ,
pn_ledger_id ,
l_journal.PERIOD_NAME );
INSERT INTO JA_CN_BANK_JOURNAL
(JE_HEADER_ID,
JE_LINE_NUM,
VOUCHER_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(l_journal.JE_HEADER_ID,
l_journal.JE_LINE_NUM,
l_vouchernum,
fnd_global.USER_ID,
sysdate,
fnd_global.USER_ID,
sysdate,
fnd_global.LOGIN_ID);
select fu.user_name
into l_poster from gl_je_batches gjb, fnd_user fu
where gjb.posted_by = fu.user_id
and gjb.je_batch_id = l_journal.je_batch_id;
select full_name into l_fullname
from PER_ALL_PEOPLE_F
where person_id =
(select employee_id from fnd_user where user_name = p_username)
and nvl(effective_start_date, sysdate) <= sysdate
and nvl(effective_end_date, sysdate) >= sysdate;
select assigned_user into l_approver
from wf_item_activity_statuses
where activity_result_code = 'APPROVED'
AND (item_key, notification_id) =
(select max(item_key) item_key, max(notification_id) notification_id
from wf_notifications
where from_user =
(select global_name
from PER_ALL_PEOPLE_F ff
where person_id = (select employee_id
from fnd_user
where user_name = p_fnd_user)
and nvl(effective_start_date, sysdate) <= sysdate
and nvl(effective_end_date, sysdate) >= sysdate)
and user_key =
(select name
from gl_je_batches gjb
where gjb.je_batch_id =
(select gjh.je_batch_id
from gl_je_headers gjh
where gjh.je_header_id = p_je_header)));
SELECT FU2.USER_NAME into l_approver
FROM GL_JE_HEADERS GJH, FND_USER FU2
WHERE GJH.JE_HEADER_ID =p_je_header
AND gjh.status = 'P'
AND FU2.USER_ID = GJH.LAST_UPDATED_BY;