[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 1,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 2,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 3,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 4,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 5,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 6,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 7,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 8,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 9,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 10,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 11,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 12,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
nvl(sum(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 13,
(nvl(BP.entered_dr,0)-nvl(BP.entered_cr,0))*X_dr_sign, 0)),0),
decode(max(BP.rowid), null, 0, 1)
FROM
GL_BC_PACKET_ARRIVAL_ORDER AO,
GL_BC_PACKETS BP,
GL_PERIOD_STATUSES PS
WHERE
PS.application_id = 101
AND PS.ledger_id = X_ledger_id
AND PS.period_year = X_period_year
AND PS.period_num BETWEEN X_start_period_num
AND X_end_period_num
AND BP.code_combination_id = X_code_combination_id
AND BP.period_name = PS.period_name||''
AND BP.actual_flag = X_actual_flag
AND nvl(BP.budget_version_id,-1) = decode(X_actual_flag,
'B', X_budget_version_id,
-1)
AND BP.currency_code = X_currency_code
AND BP.ledger_id = X_ledger_id
AND BP.status_code = 'A'
AND AO.packet_id = BP.packet_id
AND AO.ledger_id = X_ledger_id
AND AO.affect_funds_flag = 'Y';
X_LAST_UPDATE_DATE IN OUT NOCOPY DATE,
X_LAST_UPDATED_BY IN OUT NOCOPY NUMBER,
X_LAST_UPDATE_LOGIN IN OUT NOCOPY NUMBER,
X_CREATION_DATE IN OUT NOCOPY DATE,
X_CREATED_BY IN OUT NOCOPY NUMBER,
X_ACCOUNT_TYPE IN OUT NOCOPY VARCHAR2,
X_CC_ACTIVE_FLAG IN OUT NOCOPY VARCHAR2,
X_CC_BUDGETING_ALLOWED_FLAG IN OUT NOCOPY VARCHAR2,
X_PERIOD1_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD2_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD3_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD4_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD5_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD6_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD7_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD8_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD9_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD10_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD11_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD12_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_PERIOD13_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD1_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD2_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD3_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD4_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD5_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD6_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD7_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD8_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD9_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD10_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD11_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD12_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_OLD_PERIOD13_AMOUNT_QRY IN OUT NOCOPY NUMBER,
X_SEGMENT1 IN OUT NOCOPY VARCHAR2,
X_SEGMENT2 IN OUT NOCOPY VARCHAR2,
X_SEGMENT3 IN OUT NOCOPY VARCHAR2,
X_SEGMENT4 IN OUT NOCOPY VARCHAR2,
X_SEGMENT5 IN OUT NOCOPY VARCHAR2,
X_SEGMENT6 IN OUT NOCOPY VARCHAR2,
X_SEGMENT7 IN OUT NOCOPY VARCHAR2,
X_SEGMENT8 IN OUT NOCOPY VARCHAR2,
X_SEGMENT9 IN OUT NOCOPY VARCHAR2,
X_SEGMENT10 IN OUT NOCOPY VARCHAR2,
X_SEGMENT11 IN OUT NOCOPY VARCHAR2,
X_SEGMENT12 IN OUT NOCOPY VARCHAR2,
X_SEGMENT13 IN OUT NOCOPY VARCHAR2,
X_SEGMENT14 IN OUT NOCOPY VARCHAR2,
X_SEGMENT15 IN OUT NOCOPY VARCHAR2,
X_SEGMENT16 IN OUT NOCOPY VARCHAR2,
X_SEGMENT17 IN OUT NOCOPY VARCHAR2,
X_SEGMENT18 IN OUT NOCOPY VARCHAR2,
X_SEGMENT19 IN OUT NOCOPY VARCHAR2,
X_SEGMENT20 IN OUT NOCOPY VARCHAR2,
X_SEGMENT21 IN OUT NOCOPY VARCHAR2,
X_SEGMENT22 IN OUT NOCOPY VARCHAR2,
X_SEGMENT23 IN OUT NOCOPY VARCHAR2,
X_SEGMENT24 IN OUT NOCOPY VARCHAR2,
X_SEGMENT25 IN OUT NOCOPY VARCHAR2,
X_SEGMENT26 IN OUT NOCOPY VARCHAR2,
X_SEGMENT27 IN OUT NOCOPY VARCHAR2,
X_SEGMENT28 IN OUT NOCOPY VARCHAR2,
X_SEGMENT29 IN OUT NOCOPY VARCHAR2,
X_SEGMENT30 IN OUT NOCOPY VARCHAR2,
X_JE_DRCR_SIGN_REFERENCE IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION1 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION2 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION3 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION4 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION5 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION6 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION7 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION8 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION9 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION10 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION11 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION12 IN OUT NOCOPY VARCHAR2,
X_JE_LINE_DESCRIPTION13 IN OUT NOCOPY VARCHAR2,
X_STAT_AMOUNT1 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT2 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT3 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT4 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT5 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT6 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT7 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT8 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT9 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT10 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT11 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT12 IN OUT NOCOPY NUMBER,
X_STAT_AMOUNT13 IN OUT NOCOPY NUMBER,
X_old_period1_amount IN OUT NOCOPY NUMBER,
X_old_period2_amount IN OUT NOCOPY NUMBER,
X_old_period3_amount IN OUT NOCOPY NUMBER,
X_old_period4_amount IN OUT NOCOPY NUMBER,
X_old_period5_amount IN OUT NOCOPY NUMBER,
X_old_period6_amount IN OUT NOCOPY NUMBER,
X_old_period7_amount IN OUT NOCOPY NUMBER,
X_old_period8_amount IN OUT NOCOPY NUMBER,
X_old_period9_amount IN OUT NOCOPY NUMBER,
X_old_period10_amount IN OUT NOCOPY NUMBER,
X_old_period11_amount IN OUT NOCOPY NUMBER,
X_old_period12_amount IN OUT NOCOPY NUMBER,
X_old_period13_amount IN OUT NOCOPY NUMBER,
X_period1_amount IN OUT NOCOPY NUMBER,
X_period2_amount IN OUT NOCOPY NUMBER,
X_period3_amount IN OUT NOCOPY NUMBER,
X_period4_amount IN OUT NOCOPY NUMBER,
X_period5_amount IN OUT NOCOPY NUMBER,
X_period6_amount IN OUT NOCOPY NUMBER,
X_period7_amount IN OUT NOCOPY NUMBER,
X_period8_amount IN OUT NOCOPY NUMBER,
X_period9_amount IN OUT NOCOPY NUMBER,
X_period10_amount IN OUT NOCOPY NUMBER,
X_period11_amount IN OUT NOCOPY NUMBER,
X_period12_amount IN OUT NOCOPY NUMBER,
X_period13_amount IN OUT NOCOPY NUMBER)
IS
X_dr_sign NUMBER;
SELECT
BI.rowid,
decode(CC.account_type,
'A', 'Y',
'E', 'Y',
'D', 'Y', 'N'),
nvl(BI.status_number,0),
BI.last_update_date,
BI.last_updated_by,
BI.last_update_login,
BI.creation_date,
BI.created_by,
CC.account_type,
decode(CC.enabled_flag,
'N', 'N',
decode(sign(trunc(SYSDATE)-
trunc(nvl(CC.start_date_active,SYSDATE))),
-1, 'N',
decode(sign(trunc(nvl(CC.end_date_active,SYSDATE))-
trunc(SYSDATE)),
-1, 'N','Y'))),
CC.detail_budgeting_allowed_flag,
nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0),
nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0),
nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0),
nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0),
nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0),
nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0),
nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0),
nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0),
nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0),
nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0),
nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0),
nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0),
nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
CC.segment1,
CC.segment2,
CC.segment3,
CC.segment4,
CC.segment5,
CC.segment6,
CC.segment7,
CC.segment8,
CC.segment9,
CC.segment10,
CC.segment11,
CC.segment12,
CC.segment13,
CC.segment14,
CC.segment15,
CC.segment16,
CC.segment17,
CC.segment18,
CC.segment19,
CC.segment20,
CC.segment21,
CC.segment22,
CC.segment23,
CC.segment24,
CC.segment25,
CC.segment26,
CC.segment27,
CC.segment28,
CC.segment29,
CC.segment30,
BI.je_drcr_sign_reference,
BI.je_line_description1,
BI.je_line_description2,
BI.je_line_description3,
BI.je_line_description4,
BI.je_line_description5,
BI.je_line_description6,
BI.je_line_description7,
BI.je_line_description8,
BI.je_line_description9,
BI.je_line_description10,
BI.je_line_description11,
BI.je_line_description12,
BI.je_line_description13,
BI.stat_amount1,
BI.stat_amount2,
BI.stat_amount3,
BI.stat_amount4,
BI.stat_amount5,
BI.stat_amount6,
BI.stat_amount7,
BI.stat_amount8,
BI.stat_amount9,
BI.stat_amount10,
BI.stat_amount11,
BI.stat_amount12,
BI.stat_amount13
INTO
X_row_id,
X_DR_FLAG,
X_STATUS_NUMBER,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_CREATION_DATE,
X_CREATED_BY,
X_ACCOUNT_TYPE,
X_CC_ACTIVE_FLAG,
X_CC_BUDGETING_ALLOWED_FLAG,
X_PERIOD1_AMOUNT_QRY,
X_PERIOD2_AMOUNT_QRY,
X_PERIOD3_AMOUNT_QRY,
X_PERIOD4_AMOUNT_QRY,
X_PERIOD5_AMOUNT_QRY,
X_PERIOD6_AMOUNT_QRY,
X_PERIOD7_AMOUNT_QRY,
X_PERIOD8_AMOUNT_QRY,
X_PERIOD9_AMOUNT_QRY,
X_PERIOD10_AMOUNT_QRY,
X_PERIOD11_AMOUNT_QRY,
X_PERIOD12_AMOUNT_QRY,
X_PERIOD13_AMOUNT_QRY,
X_OLD_PERIOD1_AMOUNT_QRY,
X_OLD_PERIOD2_AMOUNT_QRY,
X_OLD_PERIOD3_AMOUNT_QRY,
X_OLD_PERIOD4_AMOUNT_QRY,
X_OLD_PERIOD5_AMOUNT_QRY,
X_OLD_PERIOD6_AMOUNT_QRY,
X_OLD_PERIOD7_AMOUNT_QRY,
X_OLD_PERIOD8_AMOUNT_QRY,
X_OLD_PERIOD9_AMOUNT_QRY,
X_OLD_PERIOD10_AMOUNT_QRY,
X_OLD_PERIOD11_AMOUNT_QRY,
X_OLD_PERIOD12_AMOUNT_QRY,
X_OLD_PERIOD13_AMOUNT_QRY,
X_SEGMENT1,
X_SEGMENT2,
X_SEGMENT3,
X_SEGMENT4,
X_SEGMENT5,
X_SEGMENT6,
X_SEGMENT7,
X_SEGMENT8,
X_SEGMENT9,
X_SEGMENT10,
X_SEGMENT11,
X_SEGMENT12,
X_SEGMENT13,
X_SEGMENT14,
X_SEGMENT15,
X_SEGMENT16,
X_SEGMENT17,
X_SEGMENT18,
X_SEGMENT19,
X_SEGMENT20,
X_SEGMENT21,
X_SEGMENT22,
X_SEGMENT23,
X_SEGMENT24,
X_SEGMENT25,
X_SEGMENT26,
X_SEGMENT27,
X_SEGMENT28,
X_SEGMENT29,
X_SEGMENT30,
X_JE_DRCR_SIGN_REFERENCE,
X_JE_LINE_DESCRIPTION1,
X_JE_LINE_DESCRIPTION2,
X_JE_LINE_DESCRIPTION3,
X_JE_LINE_DESCRIPTION4,
X_JE_LINE_DESCRIPTION5,
X_JE_LINE_DESCRIPTION6,
X_JE_LINE_DESCRIPTION7,
X_JE_LINE_DESCRIPTION8,
X_JE_LINE_DESCRIPTION9,
X_JE_LINE_DESCRIPTION10,
X_JE_LINE_DESCRIPTION11,
X_JE_LINE_DESCRIPTION12,
X_JE_LINE_DESCRIPTION13,
X_STAT_AMOUNT1,
X_STAT_AMOUNT2,
X_STAT_AMOUNT3,
X_STAT_AMOUNT4,
X_STAT_AMOUNT5,
X_STAT_AMOUNT6,
X_STAT_AMOUNT7,
X_STAT_AMOUNT8,
X_STAT_AMOUNT9,
X_STAT_AMOUNT10,
X_STAT_AMOUNT11,
X_STAT_AMOUNT12,
X_STAT_AMOUNT13
FROM
GL_BUDGET_RANGE_INTERIM BI,
GL_CODE_COMBINATIONS CC
WHERE
BI.ledger_id (+) = X_ledger_id
AND BI.code_combination_id (+) = CC.code_combination_id
AND BI.currency_code (+) = X_currency_code
AND BI.budget_version_id (+) = X_budget_version_id
AND BI.period_year (+) = X_period_year
AND BI.start_period_num (+) = X_start_period_num
AND BI.status_number(+) = X_status_num
AND CC.code_combination_id = X_code_combination_id;
SELECT
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 1,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 2,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 3,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 4,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 5,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 6,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 7,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 8,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 9,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 10,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 11,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 12,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0),
nvl(sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 13,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)),0)
INTO X_old_period1_amount, X_old_period2_amount, X_old_period3_amount,
X_old_period4_amount, X_old_period5_amount, X_old_period6_amount,
X_old_period7_amount, X_old_period8_amount, X_old_period9_amount,
X_old_period10_amount, X_old_period11_amount, X_old_period12_amount,
X_old_period13_amount
FROM GL_BALANCES GB
WHERE GB.ledger_id (+) = X_ledger_id
AND GB.code_combination_id (+) = X_code_combination_id
AND GB.currency_code (+) = X_currency_code
AND GB.actual_flag (+) = 'B'
AND GB.budget_version_id (+) = X_budget_version_id
AND GB.period_year (+) = X_period_year
AND GB.period_num (+) BETWEEN X_start_period_num
AND X_end_period_num;
SELECT
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 1, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 2, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 3, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 4, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 5, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 6, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 7, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 8, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 9, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 10, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 11, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 12, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 13, PS.period_name,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 1, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 2, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 3, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 4, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 5, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 6, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 7, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 8, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 9, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 10, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 11, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 12, PS.adjustment_period_flag,
NULL)),
max(decode(PS.period_num,
floor((X_start_period_num-1)/13) * 13 + 13, PS.adjustment_period_flag,
NULL)),
sum(decode(PS.adjustment_period_flag,'Y',1,0)),
max(PS.application_id)
FROM
GL_PERIOD_STATUSES PS
WHERE
PS.application_id = 101
AND PS.ledger_id = X_ledger_id
AND PS.period_year = X_period_year
AND PS.period_num BETWEEN X_start_period_num and X_end_period_num;
SELECT
count(*),
nvl(sum(decode(sign(X_start_period_num-period_num),1,1,0)),0)
INTO X_num_adj_per_total,X_num_adj_per_before
FROM
GL_PERIOD_STATUSES PS
WHERE
PS.application_id = 101
AND PS.ledger_id = X_ledger_id
AND PS.period_year = X_period_year
AND PS.adjustment_period_flag = 'Y';
SELECT
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 1,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period1_amount,0)-nvl(BI.old_period1_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 2,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period2_amount,0)-nvl(BI.old_period2_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 3,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period3_amount,0)-nvl(BI.old_period3_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 4,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period4_amount,0)-nvl(BI.old_period4_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 5,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period5_amount,0)-nvl(BI.old_period5_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 6,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period6_amount,0)-nvl(BI.old_period6_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 7,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period7_amount,0)-nvl(BI.old_period7_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 8,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period8_amount,0)-nvl(BI.old_period8_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 9,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period9_amount,0)-nvl(BI.old_period9_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 10,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period10_amount,0)-nvl(BI.old_period10_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 11,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period11_amount,0)-nvl(BI.old_period11_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 12,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period12_amount,0)-nvl(BI.old_period12_amount,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 13,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign, 0)) +
max(nvl(BI.period13_amount,0)-nvl(BI.old_period13_amount,0)),
decode(max(nvl(GB.rowid, BI.rowid)), null, 0, 1)
FROM
GL_CODE_COMBINATIONS CC,
GL_BALANCES GB,
GL_BUDGET_RANGE_INTERIM BI
WHERE CC.code_combination_id = X_code_combination_id
AND GB.ledger_id (+) = X_ledger_id
AND GB.code_combination_id (+) = CC.code_combination_id
AND GB.currency_code (+) = defined_currency
AND GB.actual_flag (+) = 'B'
AND GB.budget_version_id (+) = X_budget_version_id
AND GB.period_year (+) = X_period_year -
decode(X_budget_rule,
'PYBS', 1,
'PYBM', 1,
0)
AND GB.period_num (+) BETWEEN X_start_period_num
AND X_end_period_num
/* */
AND BI.ledger_id (+) = X_ledger_id
AND BI.code_combination_id (+) = CC.code_combination_id
AND BI.currency_code (+) = defined_currency
AND BI.budget_version_id (+) = X_budget_version_id
AND BI.period_year (+) = X_period_year -
decode(X_budget_rule,
'PYBS', 1,
'PYBM', 1,
0)
AND BI.start_period_num (+) = X_start_period_num
AND BI.status_number(+) = X_status_number;
SELECT
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 1,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 2,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 3,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 4,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 5,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 6,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 7,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 8,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 9,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 10,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 11,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 12,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
sum(decode(GB.period_num,
floor((X_start_period_num-1)/13) * 13 + 13,
(nvl(GB.period_net_dr,0)-nvl(GB.period_net_cr,0)) * X_dr_sign,0)),
decode(max(GB.rowid), null, 0, 1)
FROM
GL_BALANCES GB
WHERE
GB.ledger_id = X_ledger_id
AND GB.code_combination_id = X_code_combination_id
AND GB.currency_code = decode(X_budget_rule,
'PYAS', 'STAT', 'CYAS', 'STAT',
X_currency_code)
AND GB.actual_flag = 'A'
AND GB.period_year = X_period_year -
decode(X_budget_rule, 'PYAS', 1, 'PYAM', 1, 0)
AND GB.period_num BETWEEN X_start_period_num AND X_end_period_num
AND nvl(GB.translated_flag, 'R') NOT IN ('Y', 'N');
gl_period_types_pkg.select_columns(x_ledger_period_type, tmpbuf, tmpbuf,
x_num_periods_per_year);