[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_Export_Header;
PROCEDURE Delete_Export_Details(p_export_worksheet_type IN VARCHAR2);
PROCEDURE Update_Assignments
( p_return_status OUT NOCOPY VARCHAR2,
p_position_line_id IN NUMBER
);
PROCEDURE Update_Distributions
( p_return_status OUT NOCOPY VARCHAR2);
l_selected_template_id NUMBER;
SELECT worksheet_id, account_export_status, position_export_status,
currency_flag, stage_id, budget_by_position, selected_stage_id,
selected_template_id
FROM psb_worksheets_i
WHERE export_id = p_export_id;
IF ( nvl(Recinfo.selected_stage_id,0) > 0 ) THEN
FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
l_selected_template_id := Recinfo.selected_template_id;
(SELECT global_worksheet_id, global_worksheet_flag, local_copy_flag,
budget_group_id, extract_id , business_group_id,
budget_calendar_id, set_of_books_id, freeze_flag,
stage_set_id,current_stage_seq,
chart_of_accounts_id, currency_code
FROM psb_ws_summary_v ws
WHERE ws.worksheet_id = g_worksheet_id)
LOOP
IF g_current_stage_seq <> ws_rec.current_stage_seq
OR nvl(ws_rec.freeze_flag,'N') = 'Y' THEN
FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
nvl(g_account_export_status,'INSERT') = 'VALIDATE' THEN
l_import_accounts := TRUE;
IF l_import_accounts and ( nvl(l_selected_template_id,0) > 0 ) THEN
FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
nvl(g_position_export_status,'INSERT') = 'VALIDATE' and
g_budget_by_position = 'Y' THEN
l_import_positions := TRUE;
Delete_Export_Details(p_export_worksheet_type => 'A');
Delete_Export_Details(p_export_worksheet_type => 'P');
Delete_Export_Header;
select service_package_name
from psb_ws_line_balances_i
where export_id = g_export_id
and export_worksheet_type = 'P'
and service_package_id is NULL
for update of service_package_id;
update psb_ws_line_balances_i
set service_package_id = l_service_package_id
where current of wlbi_cur;
SELECT SERVICE_PACKAGE_ID
FROM PSB_SERVICE_PACKAGES
WHERE GLOBAL_WORKSHEET_ID = p_worksheet_id
AND SHORT_NAME = p_spname;
select psb_service_packages_s.nextval into l_spid from dual;
INSERT INTO PSB_SERVICE_PACKAGES
(
SERVICE_PACKAGE_ID,
GLOBAL_WORKSHEET_ID,
BASE_SERVICE_PACKAGE,
NAME,
SHORT_NAME,
DESCRIPTION,
PRIORITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE
) VALUES
( l_spid,
p_worksheet_id,
'N',
p_spname,
substr(p_spname,1,15),
g_translated_sp_desc,
NULL,
SYSDATE,
l_userid,
l_loginid,
l_userid,
SYSDATE
);
( SELECT
COLUMN_NUMBER,
BUDGET_YEAR_ID,
BUDGET_YEAR_NAME,
BALANCE_TYPE,
YEAR_CATEGORY_TYPE
FROM PSB_WS_COLUMNS_I
WHERE EXPORT_ID = g_export_id
AND EXPORT_WORKSHEET_TYPE = 'A'
ORDER BY COLUMN_NUMBER
)
LOOP
i := ws_col_rec.COLUMN_NUMBER;
SELECT
MAX(CODE_COMBINATION_ID) CODE_COMBINATION_ID,
CONCATENATED_ACCOUNT,
ACCOUNT_TYPE,
CURRENCY_CODE,
TEMPLATE_ID,
MAX(DECODE(SERVICE_PACKAGE_ID,0,l_service_pack_id,
SERVICE_PACKAGE_ID)) SERVICE_PACKAGE_ID,
DECODE(NVL(UPPER(TRIM(SERVICE_PACKAGE_NAME)),'BASE'),
'BASE', 'BASE',TRIM(SERVICE_PACKAGE_NAME))
SERVICE_PACKAGE_NAME,
SUM(AMOUNT1) AMOUNT1,
SUM(AMOUNT2) AMOUNT2,
SUM(AMOUNT3) AMOUNT3,
SUM(AMOUNT4) AMOUNT4,
SUM(AMOUNT5) AMOUNT5,
SUM(AMOUNT6) AMOUNT6,
SUM(AMOUNT7) AMOUNT7,
SUM(AMOUNT8) AMOUNT8,
SUM(AMOUNT9) AMOUNT9,
SUM(AMOUNT10) AMOUNT10,
SUM(AMOUNT11) AMOUNT11,
SUM(AMOUNT12) AMOUNT12
FROM PSB_WS_LINE_BALANCES_I
WHERE EXPORT_ID = g_export_id
AND EXPORT_WORKSHEET_TYPE = 'A'
AND NVL(POSITION_ACCOUNT_FLAG,'N') = 'N'
GROUP BY CONCATENATED_ACCOUNT,
ACCOUNT_TYPE,
CURRENCY_CODE, TEMPLATE_ID,
DECODE(NVL(UPPER(TRIM(SERVICE_PACKAGE_NAME)),'BASE'),
'BASE', 'BASE', TRIM(SERVICE_PACKAGE_NAME))
ORDER BY CODE_COMBINATION_ID;
SELECT service_package_id
INTO l_service_pack_id
FROM psb_service_packages
WHERE global_worksheet_id = g_global_worksheet_id
AND name = 'BASE'
AND rownum = 1;
(SELECT bp.budget_period_id, bp.end_date
FROM psb_budget_year_types yt,
psb_budget_periods bp
WHERE yt.budget_year_type_id = bp.budget_year_type_id
AND bp.budget_period_type = 'Y'
AND yt.year_category_type = 'CY'
AND bp. budget_calendar_id = g_budget_calendar_id)
LOOP
l_budget_period_id := cy_budget_year_cur.budget_period_id;
(SELECT gl_cutoff_period
FROM psb_worksheets
WHERE worksheet_id = g_worksheet_id)
LOOP
l_gl_cutoff_period := ws_gl_cutoff_cur.gl_cutoff_period;
l_code_combination_id_tbl.DELETE;
--update account
/* Bug 3589696: Added the IF statement */
IF NOT l_last_cy_period THEN
PSB_WS_ACCT_PVT.Create_Account_Dist
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
--
p_distribute_flag => FND_API.G_TRUE,
p_worksheet_id => g_worksheet_id,
p_account_line_id => l_account_line_id,
p_service_package_id => l_spid,
p_ytd_amount => g_year_amts(col_index).amount,
--
p_period_amount => l_period_amount
);
SELECT wal.account_line_id
FROM psb_ws_account_lines wal,
psb_ws_lines wl
WHERE wl.worksheet_id = p_worksheet_id
AND wl.account_line_id = wal.account_line_id
AND wal.code_combination_id = p_ccid
AND wal.service_package_id = p_spid
AND wal.budget_year_id = p_budget_year_id
AND wal.balance_type = 'E'
AND wal.end_stage_seq is null;
( SELECT
COLUMN_NUMBER,
COLUMN_TYPE,
BUDGET_YEAR_ID,
BUDGET_YEAR_NAME,
BUDGET_PERIOD_ID,
BUDGET_PERIOD_NAME,
BALANCE_TYPE,
YEAR_CATEGORY_TYPE
FROM PSB_WS_COLUMNS_I
WHERE EXPORT_ID = g_export_id
AND EXPORT_WORKSHEET_TYPE = 'P'
ORDER BY COLUMN_NUMBER
)
LOOP
i := ws_col_rec.COLUMN_NUMBER;
( SELECT
min(column_number) total_column,
min(column_number)+1 percent_column,
min(column_number)+2 period_start_column,
max(column_number) period_end_column,
budget_year_id
FROM psb_ws_columns_i
WHERE export_id = g_export_id
AND export_worksheet_type = 'P'
AND balance_type = 'E'
GROUP BY budget_year_id )
LOOP
g_estimate_years(ws_col_rec.budget_year_id).total_column := ws_col_rec.total_column;
( select distinct position_line_id
from psb_ws_line_balances_i
where export_id = g_export_id
and export_worksheet_type = 'P'
)
LOOP
l_position_line_id := position_rec.position_line_id;
Update_Assignments(p_return_status => l_return_status,
p_position_line_id => position_rec.position_line_id);
Update_Distributions(p_return_status => ud_return_status);
PROCEDURE Update_Distributions
( p_return_status OUT NOCOPY VARCHAR2)
IS
l_period_start_column NUMBER;
SELECT
CODE_COMBINATION_ID,
CONCATENATED_ACCOUNT,
ACCOUNT_TYPE,
CURRENCY_CODE,
TEMPLATE_ID,
SERVICE_PACKAGE_ID,
SERVICE_PACKAGE_NAME,
POSITION_LINE_ID,
POSITION_ID,
PAY_ELEMENT_ID,
PAY_ELEMENT_SET_ID,
PAY_ELEMENT_NAME,
FOLLOW_SALARY,
SALARY_ACCOUNT_LINE,
AMOUNT1 a1,AMOUNT2 a2,AMOUNT3 a3,AMOUNT4 a4,AMOUNT5 a5,
AMOUNT6 a6,AMOUNT7 a7,AMOUNT8 a8,AMOUNT9 a9,AMOUNT10 a10,
AMOUNT11 a11,AMOUNT12 a12,AMOUNT13 a13,AMOUNT14 a14,AMOUNT15 a15,
AMOUNT16 a16,AMOUNT17 a17,AMOUNT18 a18,AMOUNT19 a19,AMOUNT20 a20,
AMOUNT21 a21,AMOUNT22 a22,AMOUNT23 a23,AMOUNT24 a24,AMOUNT25 a25,
AMOUNT26 a26,AMOUNT27 a27,AMOUNT28 a28,AMOUNT29 a29,AMOUNT30 a30,
AMOUNT31 a31,AMOUNT32 a32,AMOUNT33 a33,AMOUNT34 a34,AMOUNT35 a35,
AMOUNT36 a36,AMOUNT37 a37,AMOUNT38 a38,AMOUNT39 a39,AMOUNT40 a40,
AMOUNT41 a41,AMOUNT42 a42,AMOUNT43 a43,AMOUNT44 a44,AMOUNT45 a45,
AMOUNT46 a46,AMOUNT47 a47,AMOUNT48 a48,AMOUNT49 a49,AMOUNT50 a50,
AMOUNT51 a51,AMOUNT52 a52,AMOUNT53 a53,AMOUNT54 a54,AMOUNT55 a55,
AMOUNT56 a56,AMOUNT57 a57,AMOUNT58 a58,AMOUNT59 a59,AMOUNT60 a60,
AMOUNT61 a61,AMOUNT62 a62,AMOUNT63 a63,AMOUNT64 a64,AMOUNT65 a65,
AMOUNT66 a66,AMOUNT67 a67,AMOUNT68 a68,AMOUNT69 a69,AMOUNT70 a70,
AMOUNT71 a71,AMOUNT72 a72,AMOUNT73 a73,AMOUNT74 a74,AMOUNT75 a75,
AMOUNT76 a76,AMOUNT77 a77,AMOUNT78 a78,AMOUNT79 a79,AMOUNT80 a80,
AMOUNT81 a81,AMOUNT82 a82,AMOUNT83 a83,AMOUNT84 a84,AMOUNT85 a85,
AMOUNT86 a86,AMOUNT87 a87,AMOUNT88 a88,AMOUNT89 a89,AMOUNT90 a90,
AMOUNT91 a91,AMOUNT92 a92,AMOUNT93 a93,AMOUNT94 a94,AMOUNT95 a95,
AMOUNT96 a96,AMOUNT97 a97,AMOUNT98 a98,AMOUNT99 a99,AMOUNT100 a100,
AMOUNT101 a101,AMOUNT102 a102,AMOUNT103 a103,AMOUNT104 a104,AMOUNT105 a105,
AMOUNT106 a106,AMOUNT107 a107,AMOUNT108 a108,AMOUNT109 a109,AMOUNT110 a110,
AMOUNT111 a111,AMOUNT112 a112,AMOUNT113 a113,AMOUNT114 a114,AMOUNT115 a115,
AMOUNT116 a116,AMOUNT117 a117,AMOUNT118 a118,AMOUNT119 a119,AMOUNT120 a120,
AMOUNT121 a121,AMOUNT122 a122,AMOUNT123 a123,AMOUNT124 a124,AMOUNT125 a125,
AMOUNT126 a126,AMOUNT127 a127,AMOUNT128 a128,AMOUNT129 a129,AMOUNT130 a130,
AMOUNT131 a131,AMOUNT132 a132,AMOUNT133 a133,AMOUNT134 a134,AMOUNT135 a135,
AMOUNT136 a136,AMOUNT137 a137,AMOUNT138 a138,AMOUNT139 a139,AMOUNT140 a140,
AMOUNT141 a141,AMOUNT142 a142,AMOUNT143 a143,AMOUNT144 a144,AMOUNT145 a145,
AMOUNT146 a146,AMOUNT147 a147,AMOUNT148 a148,AMOUNT149 a149,AMOUNT150 a150,
AMOUNT151 a151,AMOUNT152 a152,AMOUNT153 a153,AMOUNT154 a154,AMOUNT155 a155,
AMOUNT156 a156,AMOUNT157 a157,AMOUNT158 a158,AMOUNT159 a159,AMOUNT160 a160,
AMOUNT161 a161,AMOUNT162 a162,AMOUNT163 a163,AMOUNT164 a164,AMOUNT165 a165,
AMOUNT166 a166,AMOUNT167 a167,AMOUNT168 a168
FROM psb_ws_line_balances_i
WHERE export_worksheet_type = 'P'
AND export_id = g_export_id
AND value_type = 'A' -- process only Amount rows
ORDER BY position_line_id, salary_account_line ,code_combination_id
-- Process updates before insert; within updates process non salary account lines first
-- Move the values to Update API's Input PL/SQL table
l_period_index := 0;
END IF; --create follow salary acct(called both on insert and update)
END Update_Distributions;
SELECT element_set_id
FROM psb_ws_element_lines
WHERE position_line_id = p_position_line_id
AND pay_element_id = p_pay_element_id
AND end_stage_seq is null;
SELECT wal.account_line_id
FROM psb_ws_account_lines wal,
psb_ws_lines wl
WHERE wl.worksheet_id = p_worksheet_id
AND wl.account_line_id = wal.account_line_id
AND wal.position_line_id = p_position_line_id
AND wal.code_combination_id = p_ccid
AND wal.service_package_id = p_spid
AND wal.budget_year_id = p_budget_year_id
AND wal.end_stage_seq is null
AND wal.element_set_id = p_element_set_id;
select a.position_id,
a.name,
a.effective_start_date,
a.effective_end_date
from PSB_POSITIONS a,
PSB_WS_POSITION_LINES b
where a.position_id = b.position_id
and b.position_line_id = p_position_line_id;
select worksheet_id,
pay_element_id,
pay_element_option_id,
pay_basis,
element_value_type,
element_value,
effective_start_date,
effective_end_date
from PSB_POSITION_ASSIGNMENTS
where (worksheet_id is null or worksheet_id = g_global_worksheet_id)
and currency_code = g_currency_code
and assignment_type = 'ELEMENT'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = l_position_id
order by effective_start_date,
effective_end_date,
element_value desc;
select a.worksheet_id,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.formula_id,
a.effective_start_date,
a.effective_end_date
from PSB_PAY_ELEMENT_RATES a,
PSB_PAY_ELEMENTS b
where (a.worksheet_id is null or a.worksheet_id = g_global_worksheet_id)
and a.currency_code = g_currency_code
and exists
(select 1
from PSB_POSITION_ASSIGNMENTS c
where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
and (c.worksheet_id is null or c.worksheet_id = g_global_worksheet_id)
and c.currency_code = g_currency_code
and (((c.effective_start_date <= l_end_date)
and (c.effective_end_date is null))
or ((c.effective_start_date between l_start_date and l_end_date)
or (c.effective_end_date between l_start_date and l_end_date)
or ((c.effective_start_date < l_start_date)
and (c.effective_end_date > l_end_date))))
and c.pay_element_id = a.pay_element_id
and c.position_id = l_position_id)
and (((a.effective_start_date <= l_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between l_start_date and l_end_date)
or (a.effective_end_date between l_start_date and l_end_date)
or ((a.effective_start_date < l_start_date)
and (a.effective_end_date > l_end_date))))
and a.pay_element_id = b.pay_element_id
and b.business_group_id = g_business_group_id
and b.data_extract_id = g_data_extract_id
order by a.worksheet_id,
a.effective_start_date,
a.effective_end_date,
a.element_value desc;
select worksheet_id,
effective_start_date,
effective_end_date,
attribute_id,
-- Fixed Bug # 3683644
FND_NUMBER.canonical_to_number(attribute_value) attribute_value
from PSB_POSITION_ASSIGNMENTS
where attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
and (worksheet_id is null or worksheet_id = g_global_worksheet_id)
and assignment_type = 'ATTRIBUTE'
and (((effective_start_date <= l_end_date)
and (effective_end_date is null))
or ((effective_start_date between l_start_date and l_end_date)
or (effective_end_date between l_start_date and l_end_date)
or ((effective_start_date < l_start_date)
and (effective_end_date > l_end_date))))
and position_id = l_position_id
order by worksheet_id,
effective_start_date,
effective_end_date,
FND_NUMBER.canonical_to_number(attribute_value) desc; -- Fixed Bug # 3683644
PROCEDURE Update_Assignments
( p_return_status OUT NOCOPY VARCHAR2,
p_position_line_id IN NUMBER
)
IS
l_return_status VARCHAR2(1);
l_element_cost_update_reqd BOOLEAN := FALSE;
select a.position_id,
a.name,
a.effective_start_date,
a.effective_end_date
from PSB_POSITIONS a,
PSB_WS_POSITION_LINES b
where a.position_id = b.position_id
and b.position_line_id = p_position_line_id;
SELECT distinct pay_element_id
FROM psb_ws_line_balances_i
WHERE position_line_id = p_position_line_id
AND export_worksheet_type = 'P'
AND export_id = g_export_id
)
LOOP
l_pay_element_id := element_rec.pay_element_id;
SELECT
SUM(AMOUNT1) a1,SUM(AMOUNT2) a2,SUM(AMOUNT3) a3,SUM(AMOUNT4) a4,SUM(AMOUNT5) a5,
SUM(AMOUNT6) a6,SUM(AMOUNT7) a7,SUM(AMOUNT8) a8,SUM(AMOUNT9) a9,SUM(AMOUNT10) a10,
SUM(AMOUNT11) a11,SUM(AMOUNT12) a12,SUM(AMOUNT13) a13,SUM(AMOUNT14) a14,SUM(AMOUNT15) a15,
SUM(AMOUNT16) a16,SUM(AMOUNT17) a17,SUM(AMOUNT18) a18,SUM(AMOUNT19) a19,SUM(AMOUNT20) a20,
SUM(AMOUNT21) a21,SUM(AMOUNT22) a22,SUM(AMOUNT23) a23,SUM(AMOUNT24) a24,SUM(AMOUNT25) a25,
SUM(AMOUNT26) a26,SUM(AMOUNT27) a27,SUM(AMOUNT28) a28,SUM(AMOUNT29) a29,SUM(AMOUNT30) a30,
SUM(AMOUNT31) a31,SUM(AMOUNT32) a32,SUM(AMOUNT33) a33,SUM(AMOUNT34) a34,SUM(AMOUNT35) a35,
SUM(AMOUNT36) a36,SUM(AMOUNT37) a37,SUM(AMOUNT38) a38,SUM(AMOUNT39) a39,SUM(AMOUNT40) a40,
SUM(AMOUNT41) a41,SUM(AMOUNT42) a42,SUM(AMOUNT43) a43,SUM(AMOUNT44) a44,SUM(AMOUNT45) a45,
SUM(AMOUNT46) a46,SUM(AMOUNT47) a47,SUM(AMOUNT48) a48,SUM(AMOUNT49) a49,SUM(AMOUNT50) a50,
SUM(AMOUNT51) a51,SUM(AMOUNT52) a52,SUM(AMOUNT53) a53,SUM(AMOUNT54) a54,SUM(AMOUNT55) a55,
SUM(AMOUNT56) a56,SUM(AMOUNT57) a57,SUM(AMOUNT58) a58,SUM(AMOUNT59) a59,SUM(AMOUNT60) a60,
SUM(AMOUNT61) a61,SUM(AMOUNT62) a62,SUM(AMOUNT63) a63,SUM(AMOUNT64) a64,SUM(AMOUNT65) a65,
SUM(AMOUNT66) a66,SUM(AMOUNT67) a67,SUM(AMOUNT68) a68,SUM(AMOUNT69) a69,SUM(AMOUNT70) a70,
SUM(AMOUNT71) a71,SUM(AMOUNT72) a72,SUM(AMOUNT73) a73,SUM(AMOUNT74) a74,SUM(AMOUNT75) a75,
SUM(AMOUNT76) a76,SUM(AMOUNT77) a77,SUM(AMOUNT78) a78,SUM(AMOUNT79) a79,SUM(AMOUNT80) a80,
SUM(AMOUNT81) a81,SUM(AMOUNT82) a82,SUM(AMOUNT83) a83,SUM(AMOUNT84) a84,SUM(AMOUNT85) a85,
SUM(AMOUNT86) a86,SUM(AMOUNT87) a87,SUM(AMOUNT88) a88,SUM(AMOUNT89) a89,SUM(AMOUNT90) a90,
SUM(AMOUNT91) a91,SUM(AMOUNT92) a92,SUM(AMOUNT93) a93,SUM(AMOUNT94) a94,SUM(AMOUNT95) a95,
SUM(AMOUNT96) a96,SUM(AMOUNT97) a97,SUM(AMOUNT98) a98,SUM(AMOUNT99) a99,SUM(AMOUNT100) a100,
SUM(AMOUNT101) a101,SUM(AMOUNT102) a102,SUM(AMOUNT103) a103,SUM(AMOUNT104) a104,SUM(AMOUNT105) a105,
SUM(AMOUNT106) a106,SUM(AMOUNT107) a107,SUM(AMOUNT108) a108,SUM(AMOUNT109) a109,SUM(AMOUNT110) a110,
SUM(AMOUNT111) a111,SUM(AMOUNT112) a112,SUM(AMOUNT113) a113,SUM(AMOUNT114) a114,SUM(AMOUNT115) a115,
SUM(AMOUNT116) a116,SUM(AMOUNT117) a117,SUM(AMOUNT118) a118,SUM(AMOUNT119) a119,SUM(AMOUNT120) a120,
SUM(AMOUNT121) a121,SUM(AMOUNT122) a122,SUM(AMOUNT123) a123,SUM(AMOUNT124) a124,SUM(AMOUNT125) a125,
SUM(AMOUNT126) a126,SUM(AMOUNT127) a127,SUM(AMOUNT128) a128,SUM(AMOUNT129) a129,SUM(AMOUNT130) a130,
SUM(AMOUNT131) a131,SUM(AMOUNT132) a132,SUM(AMOUNT133) a133,SUM(AMOUNT134) a134,SUM(AMOUNT135) a135,
SUM(AMOUNT136) a136,SUM(AMOUNT137) a137,SUM(AMOUNT138) a138,SUM(AMOUNT139) a139,SUM(AMOUNT140) a140,
SUM(AMOUNT141) a141,SUM(AMOUNT142) a142,SUM(AMOUNT143) a143,SUM(AMOUNT144) a144,SUM(AMOUNT145) a145,
SUM(AMOUNT146) a146,SUM(AMOUNT147) a147,SUM(AMOUNT148) a148,SUM(AMOUNT149) a149,SUM(AMOUNT150) a150,
SUM(AMOUNT151) a151,SUM(AMOUNT152) a152,SUM(AMOUNT153) a153,SUM(AMOUNT154) a154,SUM(AMOUNT155) a155,
SUM(AMOUNT156) a156,SUM(AMOUNT157) a157,SUM(AMOUNT158) a158,SUM(AMOUNT159) a159,SUM(AMOUNT160) a160,
SUM(AMOUNT161) a161,SUM(AMOUNT162) a162,SUM(AMOUNT163) a163,SUM(AMOUNT164) a164,SUM(AMOUNT165) a165,
SUM(AMOUNT166) a166,SUM(AMOUNT167) a167,SUM(AMOUNT168) a168
FROM psb_ws_line_balances_i
WHERE position_line_id = p_position_line_id
AND export_worksheet_type = 'P'
AND export_id = g_export_id
/* For Bug No. 2378123 : Start */
-- AND ( percent_of_salary_flag = 'N' or value_type = 'P' )
AND value_type = 'A'
/* For Bug No. 2378123 : End */
AND pay_element_id = l_pay_element_id )
LOOP
-- Move the Amounts to PL/SQL table
g_wlbi_amounts(1).amount := element_total_rec.a1;
l_element_cost_update_reqd := FALSE;
l_element_cost_update_reqd := TRUE;
IF l_element_cost_update_reqd THEN
Change_Element_Cost
( p_return_status => l_return_status,
p_position_line_id => p_position_line_id,
p_pay_element_id => element_rec.pay_element_id,
p_element_set_id => l_element_set_id
);
END Update_Assignments;
( select distinct service_package_id
from psb_ws_line_balances_i
where export_id = g_export_id
and export_worksheet_type = 'P'
and position_line_id = p_position_line_id
and pay_element_id = p_pay_element_id
)
LOOP
l_service_package_id := sp_rec.service_package_id;
SELECT
SUM(AMOUNT1) a1,SUM(AMOUNT2) a2,SUM(AMOUNT3) a3,SUM(AMOUNT4) a4,SUM(AMOUNT5) a5,
SUM(AMOUNT6) a6,SUM(AMOUNT7) a7,SUM(AMOUNT8) a8,SUM(AMOUNT9) a9,SUM(AMOUNT10) a10,
SUM(AMOUNT11) a11,SUM(AMOUNT12) a12,SUM(AMOUNT13) a13,SUM(AMOUNT14) a14,SUM(AMOUNT15) a15,
SUM(AMOUNT16) a16,SUM(AMOUNT17) a17,SUM(AMOUNT18) a18,SUM(AMOUNT19) a19,SUM(AMOUNT20) a20,
SUM(AMOUNT21) a21,SUM(AMOUNT22) a22,SUM(AMOUNT23) a23,SUM(AMOUNT24) a24,SUM(AMOUNT25) a25,
SUM(AMOUNT26) a26,SUM(AMOUNT27) a27,SUM(AMOUNT28) a28,SUM(AMOUNT29) a29,SUM(AMOUNT30) a30,
SUM(AMOUNT31) a31,SUM(AMOUNT32) a32,SUM(AMOUNT33) a33,SUM(AMOUNT34) a34,SUM(AMOUNT35) a35,
SUM(AMOUNT36) a36,SUM(AMOUNT37) a37,SUM(AMOUNT38) a38,SUM(AMOUNT39) a39,SUM(AMOUNT40) a40,
SUM(AMOUNT41) a41,SUM(AMOUNT42) a42,SUM(AMOUNT43) a43,SUM(AMOUNT44) a44,SUM(AMOUNT45) a45,
SUM(AMOUNT46) a46,SUM(AMOUNT47) a47,SUM(AMOUNT48) a48,SUM(AMOUNT49) a49,SUM(AMOUNT50) a50,
SUM(AMOUNT51) a51,SUM(AMOUNT52) a52,SUM(AMOUNT53) a53,SUM(AMOUNT54) a54,SUM(AMOUNT55) a55,
SUM(AMOUNT56) a56,SUM(AMOUNT57) a57,SUM(AMOUNT58) a58,SUM(AMOUNT59) a59,SUM(AMOUNT60) a60,
SUM(AMOUNT61) a61,SUM(AMOUNT62) a62,SUM(AMOUNT63) a63,SUM(AMOUNT64) a64,SUM(AMOUNT65) a65,
SUM(AMOUNT66) a66,SUM(AMOUNT67) a67,SUM(AMOUNT68) a68,SUM(AMOUNT69) a69,SUM(AMOUNT70) a70,
SUM(AMOUNT71) a71,SUM(AMOUNT72) a72,SUM(AMOUNT73) a73,SUM(AMOUNT74) a74,SUM(AMOUNT75) a75,
SUM(AMOUNT76) a76,SUM(AMOUNT77) a77,SUM(AMOUNT78) a78,SUM(AMOUNT79) a79,SUM(AMOUNT80) a80,
SUM(AMOUNT81) a81,SUM(AMOUNT82) a82,SUM(AMOUNT83) a83,SUM(AMOUNT84) a84,SUM(AMOUNT85) a85,
SUM(AMOUNT86) a86,SUM(AMOUNT87) a87,SUM(AMOUNT88) a88,SUM(AMOUNT89) a89,SUM(AMOUNT90) a90,
SUM(AMOUNT91) a91,SUM(AMOUNT92) a92,SUM(AMOUNT93) a93,SUM(AMOUNT94) a94,SUM(AMOUNT95) a95,
SUM(AMOUNT96) a96,SUM(AMOUNT97) a97,SUM(AMOUNT98) a98,SUM(AMOUNT99) a99,SUM(AMOUNT100) a100,
SUM(AMOUNT101) a101,SUM(AMOUNT102) a102,SUM(AMOUNT103) a103,SUM(AMOUNT104) a104,SUM(AMOUNT105) a105,
SUM(AMOUNT106) a106,SUM(AMOUNT107) a107,SUM(AMOUNT108) a108,SUM(AMOUNT109) a109,SUM(AMOUNT110) a110,
SUM(AMOUNT111) a111,SUM(AMOUNT112) a112,SUM(AMOUNT113) a113,SUM(AMOUNT114) a114,SUM(AMOUNT115) a115,
SUM(AMOUNT116) a116,SUM(AMOUNT117) a117,SUM(AMOUNT118) a118,SUM(AMOUNT119) a119,SUM(AMOUNT120) a120,
SUM(AMOUNT121) a121,SUM(AMOUNT122) a122,SUM(AMOUNT123) a123,SUM(AMOUNT124) a124,SUM(AMOUNT125) a125,
SUM(AMOUNT126) a126,SUM(AMOUNT127) a127,SUM(AMOUNT128) a128,SUM(AMOUNT129) a129,SUM(AMOUNT130) a130,
SUM(AMOUNT131) a131,SUM(AMOUNT132) a132,SUM(AMOUNT133) a133,SUM(AMOUNT134) a134,SUM(AMOUNT135) a135,
SUM(AMOUNT136) a136,SUM(AMOUNT137) a137,SUM(AMOUNT138) a138,SUM(AMOUNT139) a139,SUM(AMOUNT140) a140,
SUM(AMOUNT141) a141,SUM(AMOUNT142) a142,SUM(AMOUNT143) a143,SUM(AMOUNT144) a144,SUM(AMOUNT145) a145,
SUM(AMOUNT146) a146,SUM(AMOUNT147) a147,SUM(AMOUNT148) a148,SUM(AMOUNT149) a149,SUM(AMOUNT150) a150,
SUM(AMOUNT151) a151,SUM(AMOUNT152) a152,SUM(AMOUNT153) a153,SUM(AMOUNT154) a154,SUM(AMOUNT155) a155,
SUM(AMOUNT156) a156,SUM(AMOUNT157) a157,SUM(AMOUNT158) a158,SUM(AMOUNT159) a159,SUM(AMOUNT160) a160,
SUM(AMOUNT161) a161,SUM(AMOUNT162) a162,SUM(AMOUNT163) a163,SUM(AMOUNT164) a164,SUM(AMOUNT165) a165,
SUM(AMOUNT166) a166,SUM(AMOUNT167) a167,SUM(AMOUNT168) a168
FROM psb_ws_line_balances_i
WHERE export_id = g_export_id
AND export_worksheet_type = 'P'
AND position_line_id = p_position_line_id
AND value_type = 'A'
AND pay_element_id = p_pay_element_id
AND service_package_id = l_service_package_id )
LOOP
-- Move the Amounts to PL/SQL table
g_wlbi_amounts(1).amount := sp_element_cost_rec.a1;
SELECT element_line_id
FROM psb_ws_element_lines
WHERE position_line_id = p_position_line_id
AND budget_year_id = p_budget_year_id
AND service_package_id = p_service_package_id
AND pay_element_id = p_pay_element_id
AND end_stage_seq IS NULL;
(select a.worksheet_id,
abs(l_new_element_value - a.element_value) near_el,
a.pay_element_id,
a.pay_element_option_id
from PSB_PAY_ELEMENT_RATES a,
PSB_PAY_ELEMENTS b
where (a.worksheet_id is null or a.worksheet_id = g_global_worksheet_id)
and a.currency_code = g_currency_code
and (((a.effective_start_date <= l_budget_period_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between l_budget_period_start_date and l_budget_period_end_date)
or (a.effective_end_date between l_budget_period_start_date and l_budget_period_end_date)
or ((a.effective_start_date < l_budget_period_start_date)
and (a.effective_end_date > l_budget_period_end_date))))
and a.pay_element_id = b.pay_element_id
and b.business_group_id = g_business_group_id
and b.data_extract_id = g_data_extract_id
and a.pay_element_id = l_pay_element_id
and a.element_value between l_tol_min_element_value and l_tol_max_element_value
order by 1,2
)
loop
l_new_element_option_id := elem_asgn_rec.pay_element_option_id;
(SELECT sum(ytd_amount) a,
sum(period1_amount) a1,
sum(period2_amount) a2,
sum(period3_amount) a3,
sum(period4_amount) a4,
sum(period5_amount) a5,
sum(period6_amount) a6,
sum(period7_amount) a7,
sum(period8_amount) a8,
sum(period9_amount) a9,
sum(period10_amount) a10,
sum(period11_amount) a11,
sum(period12_amount) a12
FROM psb_ws_account_lines wal , psb_ws_lines wl
WHERE wal.account_line_id = wl.account_line_id
AND wl.worksheet_id = g_worksheet_id
AND wal.position_line_id = p_position_line_id
AND wal.element_set_id = p_element_set_id
AND wal.budget_year_id = p_budget_year_id
AND wal.template_id IS NULL
AND wal.end_stage_seq is null
)
LOOP
p_wal_element_cost := wal_rec.a;
(SELECT sum(annual_fte) a,
sum(period1_fte) a1,
sum(period2_fte) a2,
sum(period3_fte) a3,
sum(period4_fte) a4,
sum(period5_fte) a5,
sum(period6_fte) a6,
sum(period7_fte) a7,
sum(period8_fte) a8,
sum(period9_fte) a9,
sum(period10_fte) a10,
sum(period11_fte) a11,
sum(period12_fte) a12
FROM psb_ws_fte_lines fl
WHERE position_line_id = p_position_line_id
AND budget_year_id = p_budget_year_id
AND end_stage_seq is null
)
LOOP
g_fte_period_amounts(1).amount := ws_fte_rec.a1;
PROCEDURE Delete_Export_Details(p_export_worksheet_type IN VARCHAR2)
IS
BEGIN
delete from psb_ws_line_balances_i
where export_id = g_export_id
and export_worksheet_type = p_export_worksheet_type;
delete from psb_ws_columns_i
where export_id = g_export_id
and export_worksheet_type = p_export_worksheet_type;
update psb_worksheets_i
set account_export_status = 'DELETE'
where export_id = g_export_id;
g_account_export_status := 'DELETE';
update psb_worksheets_i
set position_export_status = 'DELETE'
where export_id = g_export_id;
g_position_export_status := 'DELETE';
END Delete_Export_Details;
PROCEDURE Delete_Export_Header
IS
BEGIN
IF nvl(g_account_export_status,'DELETE') = 'DELETE'
and nvl(g_position_export_status,'DELETE') = 'DELETE' THEN
delete from psb_worksheets_i
where export_id = g_export_id;
END Delete_Export_Header;