[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete_flag VARCHAR2(1),
reference2 VARCHAR2(240),
-- FOR bug no 3347237
reference3 VARCHAR2(240)
) ;
SELECT gl_budget_set_id
FROM PSB_GL_BUDGET_SETS
WHERE set_of_books_id = p_set_of_books_id;
SELECT code_combination_id
FROM psb_fund_balance_accounts
WHERE set_of_books_id = g_set_of_books_id
AND template_account = 'Y';
SELECT a.code_combination_id
FROM gl_code_combinations a,
psb_fund_balance_accounts b
WHERE a.code_combination_id = b.code_combination_id
AND b.set_of_books_id = g_set_of_books_id
AND DECODE(g_fund_segment,'SEGMENT1', SEGMENT1,
'SEGMENT2', SEGMENT2,
'SEGMENT3', SEGMENT3,
'SEGMENT4', SEGMENT4,
'SEGMENT5', SEGMENT5,
'SEGMENT6', SEGMENT6,
'SEGMENT7', SEGMENT7,
'SEGMENT8', SEGMENT8,
'SEGMENT9', SEGMENT9,
'SEGMENT10', SEGMENT10,
'SEGMENT11', SEGMENT11,
'SEGMENT12', SEGMENT12,
'SEGMENT13', SEGMENT13,
'SEGMENT14', SEGMENT14,
'SEGMENT15', SEGMENT15,
'SEGMENT16', SEGMENT16,
'SEGMENT17', SEGMENT17,
'SEGMENT18', SEGMENT18,
'SEGMENT19', SEGMENT19,
'SEGMENT20', SEGMENT20,
'SEGMENT21', SEGMENT21,
'SEGMENT22', SEGMENT22,
'SEGMENT23', SEGMENT23,
'SEGMENT24', SEGMENT24,
'SEGMENT25', SEGMENT25,
'SEGMENT26', SEGMENT26,
'SEGMENT27', SEGMENT27,
'SEGMENT28', SEGMENT28,
'SEGMENT29', SEGMENT29,
'SEGMENT30', SEGMENT30
) = p_fund;
SELECT
DECODE
( g_fund_segment,
'SEGMENT1', SEGMENT1, 'SEGMENT2', SEGMENT2,
'SEGMENT3', SEGMENT3, 'SEGMENT4', SEGMENT4, 'SEGMENT5',SEGMENT5,
'SEGMENT6', SEGMENT6, 'SEGMENT7', SEGMENT7, 'SEGMENT8',SEGMENT8,
'SEGMENT9', SEGMENT9, 'SEGMENT10',SEGMENT10, 'SEGMENT11',SEGMENT11,
'SEGMENT12',SEGMENT12, 'SEGMENT13',SEGMENT13, 'SEGMENT14',SEGMENT14,
'SEGMENT15',SEGMENT15, 'SEGMENT16',SEGMENT16, 'SEGMENT17',SEGMENT17,
'SEGMENT18',SEGMENT18, 'SEGMENT19',SEGMENT19, 'SEGMENT20',SEGMENT20,
'SEGMENT21',SEGMENT21, 'SEGMENT22',SEGMENT22, 'SEGMENT23',SEGMENT23,
'SEGMENT24',SEGMENT24, 'SEGMENT25',SEGMENT25, 'SEGMENT26',SEGMENT26,
'SEGMENT27',SEGMENT27, 'SEGMENT28',SEGMENT28, 'SEGMENT29',SEGMENT29,
'SEGMENT30',SEGMENT30)
segment,
a.group_id,
a.status,
a.set_of_books_id,
a.user_je_source_name,
a.user_je_category_name,
a.currency_code,
a.created_by,
a.actual_flag,
a.budget_version_id,
a.period_name,
a.period_year,
a.period_num,
a.quarter_num,
a.reference1,
a.reference2,
sum(a.entered_dr) dr_amt,
sum(a.entered_cr) cr_amt,
a.accounting_date,
a.budget_version_flag,
sum(a.amount) amount
FROM psb_gl_interfaces a,
gl_code_combinations b
WHERE worksheet_id = p_worksheet_id
AND period_name = p_period_name
AND budget_source_type = g_budget_source_type
AND a.code_combination_id = b.code_combination_id
GROUP BY DECODE
( g_fund_segment,
'SEGMENT1', SEGMENT1, 'SEGMENT2', SEGMENT2,
'SEGMENT3', SEGMENT3, 'SEGMENT4', SEGMENT4, 'SEGMENT5',SEGMENT5,
'SEGMENT6', SEGMENT6, 'SEGMENT7', SEGMENT7, 'SEGMENT8',SEGMENT8,
'SEGMENT9', SEGMENT9, 'SEGMENT10',SEGMENT10, 'SEGMENT11',SEGMENT11,
'SEGMENT12',SEGMENT12, 'SEGMENT13',SEGMENT13, 'SEGMENT14',SEGMENT14,
'SEGMENT15',SEGMENT15, 'SEGMENT16',SEGMENT16, 'SEGMENT17',SEGMENT17,
'SEGMENT18',SEGMENT18, 'SEGMENT19',SEGMENT19, 'SEGMENT20',SEGMENT20,
'SEGMENT21',SEGMENT21, 'SEGMENT22',SEGMENT22, 'SEGMENT23',SEGMENT23,
'SEGMENT24',SEGMENT24, 'SEGMENT25',SEGMENT25, 'SEGMENT26',SEGMENT26,
'SEGMENT27',SEGMENT27, 'SEGMENT28',SEGMENT28, 'SEGMENT29',SEGMENT29,
'SEGMENT30',SEGMENT30) ,
a.group_id,
a.status,
a.set_of_books_id,
a.user_je_source_name,
a.user_je_category_name,
a.currency_code,
a.created_by,
a.actual_flag,
a.budget_version_id,
a.period_name,
a.period_year,
a.period_num,
a.quarter_num,
a.reference1,
a.reference2,
a.accounting_date,
a.budget_version_flag;
INSERT INTO psb_gl_interfaces
(worksheet_id,
group_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
period_year,
period_num,
quarter_num,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5,
budget_source_type,
budget_version_flag,
balancing_entry_flag,
amount,
gl_budget_set_id
)
VALUES
(p_worksheet_id,
p_worksheet_id,
c_balacct_rec.status,
c_balacct_rec.set_of_books_id,
c_balacct_rec.user_je_source_name,
c_balacct_rec.user_je_category_name,
c_balacct_rec.currency_code,
sysdate,
c_balacct_rec.created_by,
c_balacct_rec.actual_flag,
c_balacct_rec.budget_version_id,
c_balacct_rec.accounting_date,
c_balacct_rec.period_name,
c_balacct_rec.period_year,
c_balacct_rec.period_num,
c_balacct_rec.quarter_num,
l_ccid,
DECODE(sign(l_out_bal_amt), -1, -1*l_out_bal_amt, null),
DECODE(sign(l_out_bal_amt), 1, l_out_bal_amt, null),
c_balacct_rec.reference1,
c_balacct_rec.reference2,
NULL,
NULL,
g_budget_source_type,
c_balacct_rec.budget_version_flag,
'Y',
c_balacct_rec.amount,
p_GL_budget_set_id
);
SELECT application_column_name
FROM FND_ID_FLEX_SEGMENTS
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_chart_of_accounts_id
AND enabled_flag = 'Y'
ORDER BY segment_num;
SELECT a.worksheet_id, a.budget_by_position,
a.flex_mapping_set_id,
b.set_of_books_id,
b.name,
b.chart_of_accounts_id,
b.currency_code,
b.enable_budgetary_control_flag,
b.enable_average_balances_flag,
b.period_set_name
FROM PSB_WORKSHEETS a,
GL_SETS_OF_BOOKS b,
PSB_BUDGET_GROUPS_V c
WHERE b.set_of_books_id = NVL(c.set_of_books_id, c.root_set_of_books_id)
AND a.budget_group_id = c.budget_group_id
AND a.worksheet_id = p_source_id;
SELECT a.budget_revision_id, a.revise_by_position,
a.permanent_revision,
a.gl_budget_set_id,
a.budget_revision_type,
b.set_of_books_id,
b.name,
b.chart_of_accounts_id,
b.currency_code,
b.enable_budgetary_control_flag,
b.enable_average_balances_flag,
b.period_set_name,
b.latest_opened_period_name,
b.require_budget_journals_flag
FROM PSB_BUDGET_REVISIONS a,
GL_SETS_OF_BOOKS b,
PSB_BUDGET_GROUPS_V c
WHERE b.set_of_books_id = NVL(c.set_of_books_id,c.root_set_of_books_id)
AND a.budget_group_id = c.budget_group_id
AND a.budget_revision_id = p_source_id;
SELECT user_je_source_name
FROM GL_JE_SOURCES
WHERE je_source_name = 'Budget Journal';
SELECT user_je_category_name
FROM GL_JE_CATEGORIES
WHERE je_category_name = 'Budget';
SELECT substr(name,1,15) org_code
FROM hr_operating_units
WHERE organization_id = g_org_id;*/
SELECT multi_org_flag
FROM fnd_product_groups;
SELECT period_name,
effective_period_num,
start_date,
end_date,
closing_status,
period_year,
period_num,
quarter_num
FROM GL_PERIOD_STATUSES
WHERE application_id = 101
AND set_of_books_id = g_set_of_books_id
AND NVL(adjustment_period_flag, 'N') = 'N'
AND p_start_date BETWEEN start_date AND end_date
ORDER BY period_num; -- Bug 3029168
SELECT period_name, start_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = g_set_of_books_id
AND NVL(adjustment_period_flag,'N') = 'N'
AND p_period_end_date+1 BETWEEN start_date AND end_date;
SELECT period_year
FROM GL_PERIOD_STATUSES
WHERE application_id = 101
AND set_of_books_id = g_set_of_books_id
AND p_year_end_date BETWEEN start_date AND end_date;
SELECT gl_budget_version_id
FROM PSB_GL_BUDGETS
WHERE gl_budget_set_id = p_gl_budget_set_id
AND p_year_start_date BETWEEN start_date AND end_date;
SELECT budget_name
FROM gl_budget_versions
WHERE budget_version_id = gl_budver_id;
PROCEDURE Insert_Lines_Into_BCP
(x_return_status OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_called_from IN VARCHAR2,
p_period_name IN VARCHAR2 DEFAULT NULL,
p_packetid IN NUMBER DEFAULT NULL
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_BCP';
SELECT gl_bc_packets_s.nextval
FROM dual;
SELECT s.sid, s.serial#
INTO l_session_id,
l_serial_id
FROM v$session s,v$process p
WHERE s.paddr = p.addr
AND audsid = USERENV('SESSIONID');
INSERT INTO gl_bc_packets
(packet_id,
ledger_id, -- Bug#4310411
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
reference1,
reference2,
reference3,
reference4,
reference5,
application_id, -- Bug 4589283 added the below columns
session_id,
serial_id
)
SELECT P_packetid,
set_of_books_id,
user_je_source_name,
user_je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
'P',
date_created,
created_by,
budget_version_id,
entered_dr,
entered_cr,
entered_dr,
entered_cr,
reference1,
reference2,
reference3,
reference4,
reference5,
8401,
l_session_id,
l_serial_id
FROM psb_gl_interfaces
WHERE worksheet_id = p_worksheet_id
AND period_name = p_period_name
AND budget_version_flag = 'P'
AND NVL(budget_source_type, 'BP') = g_budget_source_type;
END Insert_Lines_Into_BCP;
PROCEDURE Insert_Lines_Into_GL_I
(x_return_status OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_GL_I';
SAVEPOINT Insert_Lines_Into_GL_I;
INSERT INTO gl_interface
(group_id,
status,
ledger_id, -- Bug#4310411
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5
)
SELECT group_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5
FROM psb_gl_interfaces
WHERE worksheet_id = p_worksheet_id
AND NVL(budget_source_type,'BP') = g_budget_source_type;
ROLLBACK TO Insert_Lines_Into_GL_I;
ROLLBACK TO Insert_Lines_Into_GL_I;
ROLLBACK TO Insert_Lines_Into_GL_I;
END Insert_Lines_Into_GL_I;
PROCEDURE Insert_Lines_To_GL
(x_return_status OUT NOCOPY VARCHAR2,
p_source_id IN NUMBER,
p_called_from IN VARCHAR2,
p_event_type IN VARCHAR2 DEFAULT NULL
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_To_GL';
SELECT DISTINCT period_name
FROM psb_gl_interfaces
WHERE worksheet_id = p_source_id
AND NVL(budget_source_type,'BP') = g_budget_source_type;
SELECT gl_bc_packets_s.nextval
FROM dual;
SELECT MAX(period_num),MIN(PERIOD_NUM)
INTO l_max_period,
l_min_period
FROM psb_gl_interfaces
WHERE budget_source_type = p_event_type
AND budget_year_id = g_budget_year_id
AND worksheet_id = p_source_id;
SELECT period_name
INTO l_max_period_name
FROM psb_gl_interfaces
WHERE period_num = l_max_period
AND budget_source_type = p_event_type
AND budget_year_id = g_budget_year_id
AND worksheet_id = p_source_id
AND rownum = 1;
SELECT period_name
INTO l_min_period_name
FROM psb_gl_interfaces
WHERE period_num = l_min_period
AND budget_source_type = p_event_type
AND budget_year_id = g_budget_year_id
AND worksheet_id = p_source_id
AND rownum = 1;
/* Bug 5148282 moved the following logic from Insert_Lines_Into_BCP
as inserts into gl_bc_packets is done as autonomous transaction */
OPEN l_ws_period_csr;
Insert_Lines_Into_BCP
(x_return_status => l_return_status,
p_worksheet_id => p_source_id,
p_called_from => p_called_from,
p_period_name => l_period_name,
p_packetid => l_packetid
);
SELECT iso_language,iso_territory
INTO l_iso_language,l_iso_territory
FROM fnd_languages
WHERE language_code = userenv('LANG');
Insert_Lines_Into_BCP
(x_return_status => l_return_status,
p_worksheet_id => p_source_id,
p_called_from => p_called_from,
p_period_name => l_period_name,
p_packetid => l_packetid
);
Insert_Lines_Into_GL_I
(x_return_status => l_return_status,
p_worksheet_id => p_source_id
);
SELECT gl_interface_control_s.NEXTVAL,
gl_journal_import_s.NEXTVAL
INTO l_group_id,
l_interface_run_id
FROM dual;
INSERT INTO gl_interface_control
(JE_SOURCE_NAME,
STATUS,
INTERFACE_RUN_ID,
GROUP_ID,
SET_OF_BOOKS_ID,
PACKET_ID
)
VALUES
(g_source_name,
'S',
l_interface_run_id,
p_source_id,
g_set_of_books_id,
''
);
END Insert_Lines_To_GL;
SELECT budget_revision_type
FROM psb_budget_revisions
WHERE budget_revision_id = p_source_id;
SELECT include_cbc_commit_balance,
include_cbc_oblig_balance,
include_cbc_budget_balance
FROM psb_worksheets
WHERE worksheet_id = p_source_id;
Insert_Lines_To_GL
(x_return_status => l_return_status,
p_source_id => p_source_id,
p_called_from => 'T',
p_event_type => p_event_type -- Bug 3029168
);
Insert_Lines_To_GL
(x_return_status => l_return_status,
p_source_id => p_source_id,
p_called_from => 'T',
p_event_type => p_event_type -- Bug 3029168
);
SELECT psb_pos.name
FROM PSB_WS_POSITION_LINES pos_lines,
PSB_POSITIONS psb_pos
WHERE pos_lines.position_line_id = p_position_line_id
AND psb_pos.position_id = pos_lines.position_id;
PROCEDURE Insert_Lines_Into_PSB_I_Fund
(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_gl_budget_set_id IN NUMBER,
p_stage_seq IN NUMBER,
p_year_id IN NUMBER,
p_column IN NUMBER,
p_gl_period IN VARCHAR2,
p_gl_period_start IN DATE,
p_gl_year IN VARCHAR2,
p_period_num IN NUMBER,
p_quarter_num IN NUMBER,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_budget_stage_id IN NUMBER,
p_budget_year_id IN NUMBER,
p_detailed IN VARCHAR2,
p_event_type IN VARCHAR2 DEFAULT 'BP'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Lines_Into_PSB_I_Fund';
l_count NUMBER := 0; -- delete this
SELECT a.code_combination_id,
a.position_line_id,
a.account_line_id,
DECODE(a.account_type,'L', NULL, 'O', NULL, 'R', NULL,
DECODE(p_column, 0, NVL(a.ytd_amount, 0),
1, NVL(a.period1_amount, 0),
2, NVL(a.period2_amount, 0),
3, NVL(a.period3_amount, 0),
4, NVL(a.period4_amount, 0),
5, NVL(a.period5_amount, 0),
6, NVL(a.period6_amount, 0),
7, NVL(a.period7_amount, 0),
8, NVL(a.period8_amount, 0),
9, NVL(a.period9_amount, 0),
10, NVL(a.period10_amount, 0),
11, NVL(a.period11_amount, 0),
12, NVL(a.period12_amount, 0)
)
) dr_amount,
DECODE(a.account_type, 'A', NULL, 'E', NULL,
DECODE(p_column, 0, NVL(a.ytd_amount, 0),
1, NVL(a.period1_amount, 0),
2, NVL(a.period2_amount, 0),
3, NVL(a.period3_amount, 0),
4, NVL(a.period4_amount, 0),
5, NVL(a.period5_amount, 0),
6, NVL(a.period6_amount, 0),
7, NVL(a.period7_amount, 0),
8, NVL(a.period8_amount, 0),
9, NVL(a.period9_amount, 0),
10, NVL(a.period10_amount, 0),
11, NVL(a.period11_amount, 0),
12, NVL(a.period12_amount, 0)
)
) cr_amount,
DECODE(p_column, 0, NVL(ytd_amount, 0),
1, NVL(period1_amount, 0),
2, NVL(period2_amount, 0),
3, NVL(period3_amount, 0),
4, NVL(period4_amount, 0),
5, NVL(period5_amount, 0),
6, NVL(period6_amount, 0),
7, NVL(period7_amount, 0),
8, NVL(period8_amount, 0),
9, NVL(period9_amount, 0),
10, NVL(period10_amount, 0),
11, NVL(period11_amount, 0),
12, NVL(period12_amount, 0)
) x_amount
FROM psb_ws_account_lines a,
psb_ws_lines b,
psb_service_packages d
WHERE a.budget_year_id = p_budget_year_id
AND a.balance_type = 'E'
AND a.template_id IS NULL
AND p_stage_seq BETWEEN a.start_stage_seq AND a.current_stage_seq
AND DECODE(p_column,0, NVL(a.ytd_amount,0),
1, NVL(a.period1_amount,0),
2, NVL(a.period2_amount, 0),
3, NVL(a.period3_amount, 0),
4, NVL(a.period4_amount, 0),
5, NVL(a.period5_amount, 0),
6, NVL(a.period6_amount, 0),
7, NVL(a.period7_amount, 0),
8, NVL(a.period8_amount, 0),
9, NVL(a.period9_amount, 0),
10,NVL(a.period10_amount,0),
11,NVL(a.period11_amount, 0),
12, NVL(a.period12_amount, 0)
) <> 0
-- Bug 3029168 added the following join for STAT currency
AND ((a.currency_code <> 'STAT' AND p_event_type = 'BP') OR
(a.currency_code = 'STAT' AND p_event_type = 'SW'))
AND b.worksheet_id = p_worksheet_id
AND b.account_line_id = a.account_line_id
AND d.service_package_id = a.service_package_id
AND b.view_line_flag = 'Y';
SELECT a.code_combination_id,
SUM(DECODE(account_type, 'L', NULL, 'O', NULL, 'R', NULL,
DECODE(p_column, 0, NVL(ytd_amount, 0),
1, NVL(period1_amount, 0),
2, NVL(period2_amount, 0),
3, NVL(period3_amount, 0),
4, NVL(period4_amount, 0),
5, NVL(period5_amount, 0),
6, NVL(period6_amount, 0),
7, NVL(period7_amount, 0),
8, NVL(period8_amount, 0),
9, NVL(period9_amount, 0),
10, NVL(period10_amount, 0),
11, NVL(period11_amount, 0),
12, NVL(period12_amount, 0)
)
)
) dr_amount ,
SUM(DECODE(account_type, 'A', NULL, 'E', NULL,
DECODE(p_column, 0, NVL(ytd_amount, 0),
1, NVL(period1_amount, 0),
2, NVL(period2_amount, 0),
3, NVL(period3_amount, 0),
4, NVL(period4_amount, 0),
5, NVL(period5_amount, 0),
6, NVL(period6_amount, 0),
7, NVL(period7_amount, 0),
8, NVL(period8_amount, 0),
9, NVL(period9_amount, 0),
10, NVL(period10_amount, 0),
11, NVL(period11_amount, 0),
12, NVL(period12_amount, 0)
)
)
) cr_amount,
SUM(DECODE(p_column, 0, NVL(ytd_amount, 0),
1, NVL(period1_amount, 0),
2, NVL(period2_amount, 0),
3, NVL(period3_amount, 0),
4, NVL(period4_amount, 0),
5, NVL(period5_amount, 0),
6, NVL(period6_amount, 0),
7, NVL(period7_amount, 0),
8, NVL(period8_amount, 0),
9, NVL(period9_amount, 0),
10, NVL(period10_amount, 0),
11, NVL(period11_amount, 0),
12, NVL(period12_amount, 0)
)
) x_amount
FROM psb_ws_account_lines a,
psb_ws_lines b,
psb_service_packages d
WHERE a.budget_year_id = p_year_id
AND a.balance_type = 'E'
AND a.template_id IS NULL
AND p_stage_seq BETWEEN a.start_stage_seq AND a.current_stage_seq
AND DECODE(p_column,0, NVL(a.ytd_amount,0),
1, NVL(a.period1_amount,0),
2, NVL(a.period2_amount, 0),
3, NVL(a.period3_amount, 0),
4, NVL(a.period4_amount, 0),
5, NVL(a.period5_amount, 0),
6, NVL(a.period6_amount, 0),
7, NVL(a.period7_amount, 0),
8, NVL(a.period8_amount, 0),
9, NVL(a.period9_amount, 0),
10,NVL(a.period10_amount,0),
11,NVL(a.period11_amount, 0),
12, NVL(a.period12_amount, 0)
) <> 0
-- Bug 3029168 added the following join for STAT currency
AND ((a.currency_code <> 'STAT' AND p_event_type = 'BP') OR
(a.currency_code = 'STAT' AND p_event_type = 'SW'))
AND b.worksheet_id = p_worksheet_id
AND b.account_line_id = a.account_line_id
AND d.service_package_id = a.service_package_id
AND b.view_line_flag = 'Y'
GROUP BY a.code_combination_id;
SELECT gl_budget_version_id
FROM psb_budget_accounts v,
psb_set_relations vs,
psb_gl_budgets vgb
WHERE vgb.gl_budget_set_id = p_gl_budget_set_id
AND vgb.gl_budget_id = vs.gl_budget_id
AND v.code_combination_id = l_code_combination_id
AND vs.account_position_set_id = v.account_position_set_id
AND p_gl_period_start
BETWEEN vgb.start_date AND NVL(vgb.end_date, p_gl_period_start);
SELECT flex_mapping_set_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT budget_year_type_id
FROM psb_budget_periods
WHERE budget_period_id = p_year_id;
SAVEPOINT Insert_Lines_Into_PSB_I_Fund;
INSERT INTO psb_gl_interfaces
(worksheet_id,
group_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
period_year,
period_num,
quarter_num,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5,
budget_stage_id,
budget_year_id,
je_type,
amount,
budget_source_type,
budget_version_flag,
balancing_entry_flag,
gl_budget_set_id
)
VALUES
(p_worksheet_id,
p_worksheet_id ,
'NEW',
g_set_of_books_id ,
p_je_source,
p_je_category ,
l_currency_code, -- Bug 3029168
sysdate,
l_created_by ,
'B',
l_budget_version_id,
p_gl_period_start,
p_gl_period,
p_gl_year,
p_period_num ,
p_quarter_num,
l_ccid ,
c_detail_rec.dr_amount,
c_detail_rec.cr_amount,
g_je_name,
l_reference2,
c_detail_rec.account_line_id,
NULL,
p_budget_stage_id,
p_budget_year_id ,
p_detailed,
c_detail_rec.x_amount,
p_event_type , -- Bug 3029168
l_budget_version_flag,
'N',
p_gl_budget_set_id
);
INSERT INTO psb_gl_interfaces
(worksheet_id,
group_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
period_year,
period_num,
quarter_num,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5,
budget_stage_id,
budget_year_id,
je_type,
amount,
budget_source_type,
budget_version_flag,
balancing_entry_flag,
gl_budget_set_id
)
VALUES
(p_worksheet_id,
p_worksheet_id,
'NEW',
g_set_of_books_id,
p_je_source,
p_je_category ,
l_currency_code,
sysdate,
l_created_by ,
'B',
l_budget_version_id,
p_gl_period_start,
p_gl_period,
p_gl_year,
p_period_num ,
p_quarter_num,
l_ccid ,
c_summary_rec.dr_amount,
c_summary_rec.cr_amount,
g_je_name ,
g_je_description,
NULL,
NULL,
p_budget_stage_id,
p_budget_year_id ,
p_detailed,
c_summary_rec.x_amount,
p_event_type , -- Bug 3029168
l_budget_version_flag,
'N',
p_GL_budget_set_id
);
ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
ROLLBACK TO Insert_Lines_Into_PSB_I_Fund;
END Insert_Lines_Into_PSB_I_Fund;
SELECT sequence_number
FROM psb_budget_stages
WHERE budget_stage_id = p_budget_stage_id;
Insert_Lines_Into_PSB_I_Fund
(x_return_status => l_return_status ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_worksheet_id => p_worksheet_id,
p_gl_budget_set_id => p_gl_budget_set_id ,
p_stage_seq => l_stage_seq,
p_year_id => p_budget_year_id,
p_column => p_column,
p_gl_period => p_period_name,
p_gl_period_start => p_start_date,
p_gl_year => p_gl_year,
p_period_num => p_gl_period_num,
p_quarter_num => p_gl_quarter_num,
p_je_source => p_je_source,
p_je_category => p_je_category,
p_budget_stage_id => p_budget_stage_id,
p_budget_year_id => p_budget_year_id,
p_detailed => p_detailed,
p_event_type => p_event_type); -- Bug 3029168
PROCEDURE Delete_Old_Run
(x_return_status OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER,
p_budget_source_type IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Old_Run';
SAVEPOINT Delete_Old_Run;
DELETE FROM psb_gl_interfaces
WHERE worksheet_id = p_worksheet_id
AND NVL(budget_source_type, 'P') = p_budget_source_type;
ROLLBACK TO Delete_Old_Run;
ROLLBACK TO Delete_Old_Run;
ROLLBACK TO Delete_Old_Run;
END Delete_Old_Run;
SELECT start_date,
end_date,
budget_year_type_id
FROM psb_budget_periods
WHERE budget_period_id = p_budget_year_id;
SELECT sequence_number
FROM psb_budget_stages
WHERE budget_stage_id = p_budget_stage_id;
SELECT budget_period_id,
start_date,
end_date
FROM psb_budget_periods
WHERE budget_period_type = 'P'
AND parent_budget_period_id = p_budget_year_id
ORDER BY start_date;
SELECT include_cbc_commit_balance,
include_cbc_oblig_balance,
include_cbc_budget_balance
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT 1
FROM psb_GL_BUDGETS
WHERE dual_posting_type = 'A'
AND gl_budget_set_id = p_gl_budget_set_id
AND start_date BETWEEN l_year_start_date AND l_year_end_date
);
Delete_Old_Run
(x_return_status => l_return_status,
p_worksheet_id => p_worksheet_id,
p_budget_source_type => g_budget_source_type
);
Insert_Lines_To_GL
(x_return_status => l_return_status,
p_source_id => p_worksheet_id,
p_called_from => 'C',
p_event_type => l_event_type
);
SELECT gl_budget_set_name
FROM PSB_GL_BUDGET_SETS
WHERE gl_budget_set_id = p_gl_budget_set_id;
SELECT permanent_revision
FROM PSB_BUDGET_REVISIONS
WHERE budget_revision_id = p_source_id;
'SELECT a.code_combination_id ' ||
'FROM PSB_WS_ACCOUNT_LINES a, PSB_WS_LINES b ' ||
'WHERE b.worksheet_id = :source_id ' ||
l_curr_string|| -- Bug 3029168
'AND b.account_line_id = a.account_line_id ' ||
'AND a.budget_year_id = :budget_year_id ' ||
'AND a.balance_type = ''E'' ' ||
'AND a.template_id IS NULL ' ||
'AND :stage_sequence BETWEEN a.start_stage_seq AND a.current_stage_seq ' ||
/* Bug No 1357416 Start */
--- 'minus ' ||
'AND NOT exists (' ||
/* Bug No 1357416 END */
'SELECT v.code_combination_id ' ||
'FROM PSB_BUDGET_ACCOUNTS v, PSB_SET_RELATIONS vs, PSB_GL_BUDGETS vgb ' ||
'WHERE :start_date BETWEEN vgb.start_date AND vgb.end_date ' ||
'AND vgb.gl_budget_set_id = :gl_budget_set_id ' ||
'AND vgb.gl_budget_id = vs.gl_budget_id ' ||
'AND vs.account_position_set_id = v.account_position_set_id ' ||
/* Bug No 1357416 Start */
'AND v.code_combination_id = a.code_combination_id)'
/* Bug No 1357416 END */
USING p_source_id, p_budget_year_id, p_stage_sequence, p_start_date, p_gl_budget_set_id;
'SELECT bra.code_combination_id ' ||
'FROM psb_budget_revision_accounts bra, psb_budget_revision_lines brl ' ||
'WHERE brl.budget_revision_id = :source_id ' ||
l_curr_string|| -- Bug 3029168
'AND bra.budget_revision_acct_line_id = brl.budget_revision_acct_line_id ' ||
/* Bug No 1357416 Start */
--- 'minus ' ||
'AND NOT exists (' ||
/* Bug No 1357416 END */
'SELECT v.code_combination_id ' ||
'FROM PSB_BUDGET_ACCOUNTS v, PSB_SET_RELATIONS vs, PSB_GL_BUDGETS vgb ' ||
'WHERE :start_date BETWEEN vgb.start_date AND vgb.end_date ' ||
'AND vgb.gl_budget_set_id = :gl_budget_set_id ' ||
'AND vgb.gl_budget_id = vs.gl_budget_id ' ||
'AND vs.account_position_set_id = v.account_position_set_id ' ||
'AND NVL(dual_posting_type, ''P'') = DECODE(:permanent_revision, ''Y'', NVL(dual_posting_type, ''P''), ''A'') ' ||
/* Bug No 1357416 Start */
'AND v.code_combination_id = bra.code_combination_id)'
/* Bug No 1357416 END */
USING p_source_id, p_start_date, p_gl_budget_set_id, g_permanent_revision;
PROCEDURE Insert_BR_Lines_In_PSB_I_Fund
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_budget_revision_id IN NUMBER,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_auto_offset IN VARCHAR2,
p_gl_budget_set_id IN NUMBER,
p_event_type IN VARCHAR2,
x_validation_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_BR_Lines_In_PSB_I_Fund';
SELECT bra.gl_period_name,
gps.start_date, ---1
pgb.start_date budget_set_start_date, --2
gps.period_name,
gps.effective_period_num,
gps.end_date,
gps.closing_status,
gps.period_year,
gps.period_num,
gps.quarter_num,
bra.code_combination_id,
bra.gl_budget_version_id,
DECODE(bra.account_type,'L', NULL, 'O', NULL, 'R', NULL,
DECODE(bra.revision_type,'I',bra.revision_amount * 1.0,'D',
bra.revision_amount * -1.0
)
) dr_amount,
DECODE(bra.account_type, 'A' , NULL, 'E' , NULL,
DECODE(bra.revision_type,'I',bra.revision_amount * 1.0,'D',
bra.revision_amount * -1.0
)
) cr_amount,
budget_balance x_amount
FROM psb_budget_revisions br,
psb_budget_revision_accounts bra,
psb_budget_revision_lines brl,
gl_period_statuses gps,
psb_gl_budgets pgb
WHERE br.budget_revision_id = p_budget_revision_id
AND brl.budget_revision_id = p_budget_revision_id
AND br.budget_revision_type = 'R'
-- Bug 3029168 added the following OR condition
AND ((bra.currency_code <> 'STAT' AND p_event_type = 'BR') OR
(bra.currency_code = 'STAT' AND p_event_type = 'SR'))
AND brl.budget_revision_acct_line_id = bra.budget_revision_acct_line_id
AND gps.period_name = bra.gl_period_name
AND gps.application_id = 101
AND gps.adjustment_period_flag='N'
AND ((gps.start_date BETWEEN pgb.start_date AND pgb.end_date)
OR
(gps.end_date BETWEEN pgb.start_date AND pgb.end_date)
)
AND gps.set_of_books_id = g_set_of_books_id
AND pgb.gl_budget_set_id = p_gl_budget_set_id
ORDER BY bra.gl_period_name, bra.code_combination_id, gps.period_num;
SAVEPOINT Insert_BR_Lines_In_PSB_I_Fund;
INSERT INTO psb_gl_interfaces
(worksheet_id,
group_id,
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
currency_code,
date_created,
created_by,
actual_flag,
budget_version_id,
accounting_date,
period_name,
period_year,
period_num,
quarter_num,
code_combination_id,
entered_dr,
entered_cr,
reference1,
reference2,
reference4,
reference5,
budget_stage_id,
budget_year_id,
je_type,
amount,
budget_source_type,
budget_version_flag,
balancing_entry_flag
)
VALUES
(
p_budget_revision_id,
p_budget_revision_id,
/* For bug 4654145 --> Changed the status to POSTED, as there is no trial mode for budget revision */
'Posted',
g_set_of_books_id,
p_je_source,
p_je_category,
g_currency_code,
SYSDATE,
l_created_by,
'B',
rec_budget_version_id(l_indx),
rec_accounting_date(l_indx),
rec_period_name(l_indx),
rec_period_year(l_indx),
rec_period_num(l_indx),
rec_quarter_num(l_indx),
rec_code_combination_id(l_indx),
rec_entered_dr(l_indx),
rec_entered_cr(l_indx),
g_je_name,
g_je_description,
NULL,
NULL,
NULL,
NULL,
NULL,
rec_amount(l_indx),
p_event_type,
rec_budget_version_flag(l_indx),
'N'
);
ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
ROLLBACK TO Insert_BR_Lines_In_PSB_I_Fund;
END Insert_BR_Lines_In_PSB_I_Fund;
SELECT MIN(gp.start_date) start_date, MAX(gp.end_date) end_date
FROM PSB_BUDGET_REVISION_ACCOUNTS ac,
GL_PERIOD_STATUSES gp
WHERE ac.budget_revision_acct_line_id IN
(SELECT budget_revision_acct_line_id
FROM psb_budget_revision_lines
WHERE budget_revision_id = p_budget_revision_id
)
AND ac.gl_period_name = gp.period_name
AND gp.application_id = 101
AND gp.set_of_books_id = g_set_of_books_id ;
SELECT currency_code,budget_group_id,gl_budget_set_id
INTO l_currency_code,l_budget_group_id,l_gl_budget_set_id
FROM psb_budget_revisions
WHERE budget_revision_id = p_budget_revision_id;
Delete_Old_Run
(x_return_status => l_return_status,
p_worksheet_id => p_budget_revision_id,
p_budget_source_type => g_budget_source_type
);
Insert_BR_Lines_In_PSB_I_Fund
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_budget_revision_id => p_budget_revision_id,
p_je_source => l_je_source,
p_je_category => l_je_category,
p_auto_offset => l_auto_offset,
p_gl_budget_set_id => l_gl_budget_set_id,
p_event_type => l_event_type, -- Bug 3029168
x_validation_status => l_validation_status
);
Insert_Lines_To_GL
(x_return_status => l_return_status,
p_source_id => p_budget_revision_id,
p_called_from => 'R',
p_event_type => l_event_type -- Bug 3029168
);
SELECT start_date,
end_date,
budget_year_type_id
FROM psb_budget_periods
WHERE budget_period_id = p_budget_year_id;
SELECT sequence_number
FROM psb_budget_stages
WHERE budget_stage_id = p_budget_stage_id;
SELECT budget_period_id,
start_date,
end_date
FROM psb_budget_periods
WHERE budget_period_type = 'P'
AND parent_budget_period_id = p_budget_year_id
ORDER BY start_date;
SELECT include_cbc_commit_balance,
include_cbc_oblig_balance,
include_cbc_budget_balance
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT 1
INTO l_no
FROM psb_gl_interfaces
WHERE worksheet_id = p_document_id
AND budget_source_type = p_document_type
AND rownum = 1;