[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(SELECT nvl(global_worksheet_id,worksheet_id) 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, budget_by_position,
stage_set_id, current_stage_seq,
chart_of_accounts_id, currency_code
FROM psb_ws_summary_v
WHERE worksheet_id = p_worksheet_id)
LOOP
l_ws_rec_found := TRUE;
/* Following procedure called for DDSP To get the values of Data Selection profile WS Id and User ID
Start */
PSB_WS_PERIOD_TOTAL.get_data_selection_profile
(p_current_worksheet_id => g_worksheet_id,
p_current_user_id => g_user_id,
p_global_profile_user_id => g_global_profile_user_id,
p_profile_worksheet_id => g_profile_worksheet_id,
p_profile_user_id => g_profile_user_id);
l_account_export_status := 'INSERT';
l_position_export_status := 'INSERT';
Insert into PSB_WORKSHEETS_I
(EXPORT_ID,
EXPORT_NAME,
WORKSHEET_ID,
STAGE_ID,
SELECTED_STAGE_ID,
SELECTED_TEMPLATE_ID,
ACCOUNT_EXPORT_STATUS,
POSITION_EXPORT_STATUS,
BUDGET_BY_POSITION,
CURRENCY_FLAG,
EXPORT_WORKSHEET_TYPE,
ALLOW_ACCOUNT_IMPORT,
ALLOW_POSITION_IMPORT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values
( g_export_id,
l_export_name,
p_worksheet_id,
l_current_stage_seq,
g_stage_id,
g_template_id,
l_account_export_status,
l_position_export_status,
g_budget_by_position,
g_currency_flag,
l_export_worksheet_type,
g_allow_account_import,
g_allow_position_import,
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE );
update psb_worksheets_i
set allow_position_import = g_allow_position_import
where export_id = g_export_id;
select
TEMPLATE_ID,
CURRENCY_FLAG,
YEAR_FLAG,
SERVICE_PACKAGE_FLAG,
ACCOUNT_FLAG
from
PSB_WS_QUERY_PROFILES_V
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id;
SELECT budget_period_id
FROM psb_ws_year_profiles_v
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id;
SELECT service_package_id
FROM PSB_WS_SERVICE_PKG_PROFILES_V
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id;
g_year_flag := 'S'; --Selected
g_service_package_flag := 'S'; --Selected
select chart_of_accounts_id into g_coa_id from psb_ws_summary_v
where worksheet_id = g_worksheet_id;
SELECT current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = g_worksheet_id;
SELECT budget_period_id,
name
FROM psb_budget_periods
WHERE parent_budget_period_id = p_budget_year_id
AND budget_period_type = 'P';
SELECT budget_period_id,
budget_period_name,
year_category_type ,
sequence_number
FROM psb_ws_budget_years_v
WHERE worksheet_id = g_worksheet_id
ORDER by sequence_number;
SELECT budget_period_id,
budget_period_name,
year_category_type,
fte_flag,
actual_flag,
budget_flag,
estimate_flag,
/* Bug No 2656353 Start */
encumbrance_flag,
/* Bug No 2656353 End */
start_year_flag
FROM psb_ws_year_profiles_v
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id
where WORKSHEET_ID = g_profile_worksheet_id
and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL))
ORDER by sequence_number;
insert into psb_ws_columns_i(
EXPORT_ID,
EXPORT_WORKSHEET_TYPE,
COLUMN_NUMBER,
BUDGET_YEAR_ID,
BUDGET_YEAR_NAME,
BALANCE_TYPE,
DISPLAYED_BALANCE_TYPE,
YEAR_CATEGORY_TYPE,
DISPLAYED_YEAR_CATEGORY_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATED_DATE)
values(
g_export_id,
'A', -- for All Accounts
i,
g_ws_cols(i).budget_year_id,
g_ws_cols(i).budget_year_name,
g_ws_cols(i).balance_type,
g_ws_cols(i).display_balance_type, -- Get translated msg
g_ws_cols(i).year_category_type,
g_ws_cols(i).year_category_type, -- Get translated msg
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE
);
insert into psb_ws_columns_i(
EXPORT_ID,
EXPORT_WORKSHEET_TYPE,
COLUMN_NUMBER,
COLUMN_TYPE,
BUDGET_YEAR_ID,
BUDGET_YEAR_NAME,
BUDGET_PERIOD_ID,
BUDGET_PERIOD_NAME,
BALANCE_TYPE,
DISPLAYED_BALANCE_TYPE,
YEAR_CATEGORY_TYPE,
DISPLAYED_YEAR_CATEGORY_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATED_DATE)
values(
g_export_id,
'P', -- for Position Accounts
i,
g_pos_ws_cols(i).column_type,
g_pos_ws_cols(i).budget_year_id,
g_pos_ws_cols(i).budget_year_name,
g_pos_ws_cols(i).budget_period_id,
g_pos_ws_cols(i).budget_period_name,
g_pos_ws_cols(i).balance_type,
g_pos_ws_cols(i).display_balance_type, -- Get translated msg
g_pos_ws_cols(i).year_category_type,
g_pos_ws_cols(i).year_category_type, -- Get translated msg
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE
);
SELECT nvl(root_budget_group_id, budget_group_id) root_budget_group_id
FROM psb_budget_groups_v
WHERE budget_group_id = g_budget_group_id
)
LOOP
l_root_budget_group_id := l_bg_rec.root_budget_group_id;
select
CODE_COMBINATION_ID,
ACCOUNT_TYPE,
SERVICE_PACKAGE_ID,
SERVICE_PACKAGE_NAME,
PRIORITY,
TEMPLATE_ID,
CURRENCY_CODE,
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COLUMN6,
COLUMN7,
COLUMN8,
COLUMN9,
COLUMN10,
COLUMN11,
COLUMN12
from PSB_WS_LINE_YEAR_XL_V
where WORKSHEET_ID = g_worksheet_id
/* Bug No 2008329 Start */
-- and ( g_account_flag = 'A' or ACCOUNT_TYPE = g_account_flag )
and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
OR ( account_type = DECODE(g_account_flag,'P','R')
OR account_type = DECODE(g_account_flag,'P','E')
)
OR ( account_type = DECODE(g_account_flag,'N','A' )
OR account_type = DECODE(g_account_flag,'N','L')
)
OR ( account_type = DECODE(g_account_flag,'B','C' )
OR account_type = DECODE(g_account_flag,'B','D')
)
)
/* Bug No 2008329 End */
and ( (g_template_id is null and TEMPLATE_ID is null)
or
(TEMPLATE_ID = g_template_id) )
and ( ( g_currency_flag = 'C' and CURRENCY_CODE <> 'STAT' )
or
( g_currency_flag = 'S' and CURRENCY_CODE = 'STAT')
)
and ( g_service_package_flag = 'A' or
SERVICE_PACKAGE_ID in
(SELECT SERVICE_PACKAGE_ID
FROM PSB_WS_SERVICE_PKG_PROFILES_V
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id
where WORKSHEET_ID = g_profile_worksheet_id
and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL)) ) )
)
LOOP
IF FND_FLEX_KEYVAL.validate_ccid('SQLGL', 'GL#', g_coa_id, wal_rec.CODE_COMBINATION_ID) THEN
l_account_segments := substr(FND_FLEX_KEYVAL.concatenated_values,1,1000);
-- If not able to decide status set the record to non updateable
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
l_position_account_flag := 'Y';
insert into psb_ws_line_balances_i(
EXPORT_ID,
EXPORT_WORKSHEET_TYPE,
CODE_COMBINATION_ID,
CONCATENATED_ACCOUNT,
ACCOUNT_DESCRIPTION,
SERVICE_PACKAGE_ID,
SERVICE_PACKAGE_NAME,
POSITION_ACCOUNT_FLAG,
AMOUNT1,
AMOUNT2,
AMOUNT3,
AMOUNT4,
AMOUNT5,
AMOUNT6,
AMOUNT7,
AMOUNT8,
AMOUNT9,
AMOUNT10,
AMOUNT11,
AMOUNT12,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATED_DATE)
values(
g_export_id,
'A',
wal_rec.CODE_COMBINATION_ID,
l_account_segments,
l_account_desc,
wal_rec.SERVICE_PACKAGE_ID,
wal_rec.SERVICE_PACKAGE_NAME,
l_position_account_flag,
wal_rec.COLUMN1,
wal_rec.COLUMN2,
wal_rec.COLUMN3,
wal_rec.COLUMN4,
wal_rec.COLUMN5,
wal_rec.COLUMN6,
wal_rec.COLUMN7,
wal_rec.COLUMN8,
wal_rec.COLUMN9,
wal_rec.COLUMN10,
wal_rec.COLUMN11,
wal_rec.COLUMN12,
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE
);
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 element_value_type = 'PS'
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 a.element_value_type = 'PS'
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 factor
from PSB_HRMS_FACTORS
where hrms_period_type = p_element_period_type
and budget_period_type = l_budget_period_type)
loop
l_hrms_factor := factor_rec.factor;
p_select_date => l_pp_start_date
);
SELECT
position_line_id,
position_id,
position_name,
position_definition_id,
position_segments
FROM psb_ws_select_positions_v
WHERE worksheet_id = g_worksheet_id
)
LOOP
l_position_line_id := position_rec.position_line_id;
g_ps_elements.DELETE;
SELECT
emp.employee_id,
emp.employee_number,
emp.full_name
FROM
psb_employees emp, psb_position_assignments pavb
WHERE pavb.assignment_type = 'EMPLOYEE'
AND pavb.position_id = l_position_id
AND emp.employee_id = pavb.employee_id
AND (pavb.worksheet_id = g_worksheet_id
OR pavb.worksheet_id IS NULL)
ORDER BY pavb.effective_start_date DESC, NVL(pavb.worksheet_id,0) DESC
)
LOOP
l_employee_id := emp_rec.employee_id;
SELECT
pava.attribute_id,
patv.attribute_value
FROM psb_attribute_values patv, psb_position_assignments pava
WHERE patv.attribute_value_id = pava.attribute_value_id
AND pava.position_id = l_position_id
AND EXISTS (SELECT 1 FROM psb_attributes pat
WHERE pat.attribute_id = pava.attribute_id
AND pat.system_attribute_type = 'JOB_CLASS')
AND (pava.worksheet_id = g_worksheet_id
OR pava.worksheet_id IS NULL)
ORDER BY pava.effective_start_date DESC, NVL(pava.worksheet_id,0) DESC
)
LOOP
l_job_attribute_id := job_rec.attribute_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 = l_position_line_id)
loop
l_position_start_date := c_Positions_Rec.effective_start_date;
SELECT worksheet_id
,element_set_id
,position_line_id
,code_combination_id
,service_package_id
,service_package_name
,account_type
,currency_code
,stage_set_id
,budget_group_id
,template_id
,salary_account_line
FROM psb_ws_year_positions_v
WHERE worksheet_id = g_worksheet_id
AND position_line_id = l_position_line_id
/* Bug No 2008329 Start */
-- AND ( g_account_flag = 'A' or account_type = g_account_flag )
and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
OR ( account_type = DECODE(g_account_flag,'P','R')
OR account_type = DECODE(g_account_flag,'P','E')
)
OR ( account_type = DECODE(g_account_flag,'N','A' )
OR account_type = DECODE(g_account_flag,'N','L')
)
OR ( account_type = DECODE(g_account_flag,'B','C' )
OR account_type = DECODE(g_account_flag,'B','D')
)
)
/* Bug No 2008329 End */
AND ( ( g_currency_flag = 'C' and currency_code <> 'STAT' )
or
( g_currency_flag = 'S' and currency_code = 'STAT')
)
AND ( g_service_package_flag = 'A' or
service_package_id in
(select service_package_id
from psb_ws_service_pkg_profiles_v
/* Following 2 lines commented and next 2 lines added for DDSP */
-- where WORKSHEET_ID = g_worksheet_id
-- and USER_ID = g_user_id
where WORKSHEET_ID = g_profile_worksheet_id
and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL)) )
)
)
LOOP
l_element_value_type := NULL;
SELECT distinct pe.pay_element_id, pe.name, pe.follow_salary, pe.element_value_type, pe.period_type
FROM psb_ws_element_lines wel, psb_pay_elements_v pe
WHERE wel.position_line_id = l_position_line_id
AND wel.element_set_id = l_element_set_id
AND wel.pay_element_id = pe.pay_element_id)
LOOP
l_element_id := pay_element_rec.pay_element_id;
SELECT column1_id
,column2_id
,column3_id
,column4_id
,column5_id
,column6_id
,column7_id
,column8_id
,column9_id
,column10_id
,column11_id
,column12_id
FROM psb_ws_year_position_amounts_v
WHERE worksheet_id = g_worksheet_id
AND code_combination_id = l_code_combination_id
AND service_package_id = l_service_package_id
AND position_line_id = l_position_line_id
AND element_set_id = l_element_set_id
/* Bug No 2008329 Start */
-- AND ( g_account_flag = 'A' or account_type = g_account_flag )
and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
OR ( account_type = DECODE(g_account_flag,'P','R')
OR account_type = DECODE(g_account_flag,'P','E')
)
OR ( account_type = DECODE(g_account_flag,'N','A' )
OR account_type = DECODE(g_account_flag,'N','L')
)
OR ( account_type = DECODE(g_account_flag,'B','C' )
OR account_type = DECODE(g_account_flag,'B','D')
)
)
/* Bug No 2008329 End */
AND ( ( g_currency_flag = 'C' and currency_code <> 'STAT' )
or
( g_currency_flag = 'S' and currency_code = 'STAT')
)
)
LOOP
-- Get the Account Line IDs and move them to a PL/SQL table
g_acl_ids(1).acl_id := position_year_amt_rec.column1_id;
select
annual_fte
,period1_fte
,period2_fte
,period3_fte
,period4_fte
,period5_fte
,period6_fte
,period7_fte
,period8_fte
,period9_fte
,period10_fte
,period11_fte
,period12_fte
from psb_ws_fte_lines
where position_line_id = l_position_line_id
and budget_year_id = l_budget_year_id
and service_package_id = l_service_package_id
and ( (g_stage_id = 0 and end_stage_seq is null )
or
(g_stage_id between start_stage_seq and nvl(end_stage_seq, 9.99e125) ))
)
LOOP
l_fte_rec_found := FND_API.G_TRUE;
SELECT ytd_amount
,start_stage_seq
,current_stage_seq
,period1_amount
,period2_amount
,period3_amount
,period4_amount
,period5_amount
,period6_amount
,period7_amount
,period8_amount
,period9_amount
,period10_amount
,period11_amount
,period12_amount
FROM psb_ws_account_lines
WHERE account_line_id = g_acl_ids(col_index).acl_id
)
LOOP
l_wal_rec_found := FND_API.G_TRUE;
INSERT INTO psb_ws_line_balances_i(
EXPORT_ID,
EXPORT_WORKSHEET_TYPE,
CODE_COMBINATION_ID,
BUDGET_GROUP_ID,
CONCATENATED_ACCOUNT,
ACCOUNT_DESCRIPTION,
SERVICE_PACKAGE_ID,
SERVICE_PACKAGE_NAME,
PAY_ELEMENT_SET_ID,
PAY_ELEMENT_ID,
PAY_ELEMENT_NAME,
SALARY_ACCOUNT_LINE,
FOLLOW_SALARY,
POSITION_LINE_ID,
POSITION_ID,
POSITION_NAME,
POSITION_SEGMENTS,
JOB_NAME,
EMPLOYEE_ID,
EMPLOYEE_NUMBER,
EMPLOYEE_NAME,
VALUE_TYPE,
PERCENT_OF_SALARY_FLAG,
AMOUNT1,
AMOUNT2,
AMOUNT3,
AMOUNT4,
AMOUNT5,
AMOUNT6,
AMOUNT7,
AMOUNT8,
AMOUNT9,
AMOUNT10,
AMOUNT11,
AMOUNT12,
AMOUNT13,
AMOUNT14,
AMOUNT15,
AMOUNT16,
AMOUNT17,
AMOUNT18,
AMOUNT19,
AMOUNT20,
AMOUNT21,
AMOUNT22,
AMOUNT23,
AMOUNT24,
AMOUNT25,
AMOUNT26,
AMOUNT27,
AMOUNT28,
AMOUNT29,
AMOUNT30,
AMOUNT31,
AMOUNT32,
AMOUNT33,
AMOUNT34,
AMOUNT35,
AMOUNT36,
AMOUNT37,
AMOUNT38,
AMOUNT39,
AMOUNT40,
AMOUNT41,
AMOUNT42,
AMOUNT43,
AMOUNT44,
AMOUNT45,
AMOUNT46,
AMOUNT47,
AMOUNT48,
AMOUNT49,
AMOUNT50,
AMOUNT51,
AMOUNT52,
AMOUNT53,
AMOUNT54,
AMOUNT55,
AMOUNT56,
AMOUNT57,
AMOUNT58,
AMOUNT59,
AMOUNT60,
AMOUNT61,
AMOUNT62,
AMOUNT63,
AMOUNT64,
AMOUNT65,
AMOUNT66,
AMOUNT67,
AMOUNT68,
AMOUNT69,
AMOUNT70,
AMOUNT71,
AMOUNT72,
AMOUNT73,
AMOUNT74,
AMOUNT75,
AMOUNT76,
AMOUNT77,
AMOUNT78,
AMOUNT79,
AMOUNT80,
AMOUNT81,
AMOUNT82,
AMOUNT83,
AMOUNT84,
AMOUNT85,
AMOUNT86,
AMOUNT87,
AMOUNT88,
AMOUNT89,
AMOUNT90,
AMOUNT91,
AMOUNT92,
AMOUNT93,
AMOUNT94,
AMOUNT95,
AMOUNT96,
AMOUNT97,
AMOUNT98,
AMOUNT99,
AMOUNT100,
AMOUNT101,
AMOUNT102,
AMOUNT103,
AMOUNT104,
AMOUNT105,
AMOUNT106,
AMOUNT107,
AMOUNT108,
AMOUNT109,
AMOUNT110,
AMOUNT111,
AMOUNT112,
AMOUNT113,
AMOUNT114,
AMOUNT115,
AMOUNT116,
AMOUNT117,
AMOUNT118,
AMOUNT119,
AMOUNT120,
AMOUNT121,
AMOUNT122,
AMOUNT123,
AMOUNT124,
AMOUNT125,
AMOUNT126,
AMOUNT127,
AMOUNT128,
AMOUNT129,
AMOUNT130,
AMOUNT131,
AMOUNT132,
AMOUNT133,
AMOUNT134,
AMOUNT135,
AMOUNT136,
AMOUNT137,
AMOUNT138,
AMOUNT139,
AMOUNT140,
AMOUNT141,
AMOUNT142,
AMOUNT143,
AMOUNT144,
AMOUNT145,
AMOUNT146,
AMOUNT147,
AMOUNT148,
AMOUNT149,
AMOUNT150,
AMOUNT151,
AMOUNT152,
AMOUNT153,
AMOUNT154,
AMOUNT155,
AMOUNT156,
AMOUNT157,
AMOUNT158,
AMOUNT159,
AMOUNT160,
AMOUNT161,
AMOUNT162,
AMOUNT163,
AMOUNT164,
AMOUNT165,
AMOUNT166,
AMOUNT167,
AMOUNT168,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATED_DATE)
values(
g_export_id,
'P',
l_code_combination_id,
l_budget_group_id,
l_account_segments,
l_account_desc,
l_service_package_id,
l_service_package_name,
l_element_set_id,
l_element_id,
l_element_name,
nvl(position_acct_rec.salary_account_line,'N'),
nvl(l_follow_salary,'N'),
position_rec.position_line_id,
position_rec.position_id,
position_rec.position_name,
position_rec.position_segments,
l_job_name,
l_employee_id,
l_employee_number,
l_employee_name,
'A', -- For Amount
l_percent_of_salary_flag,
g_wlbi_amounts(1).amount,
g_wlbi_amounts(2).amount,
g_wlbi_amounts(3).amount,
g_wlbi_amounts(4).amount,
g_wlbi_amounts(5).amount,
g_wlbi_amounts(6).amount,
g_wlbi_amounts(7).amount,
g_wlbi_amounts(8).amount,
g_wlbi_amounts(9).amount,
g_wlbi_amounts(10).amount,
g_wlbi_amounts(11).amount,
g_wlbi_amounts(12).amount,
g_wlbi_amounts(13).amount,
g_wlbi_amounts(14).amount,
g_wlbi_amounts(15).amount,
g_wlbi_amounts(16).amount,
g_wlbi_amounts(17).amount,
g_wlbi_amounts(18).amount,
g_wlbi_amounts(19).amount,
g_wlbi_amounts(20).amount,
g_wlbi_amounts(21).amount,
g_wlbi_amounts(22).amount,
g_wlbi_amounts(23).amount,
g_wlbi_amounts(24).amount,
g_wlbi_amounts(25).amount,
g_wlbi_amounts(26).amount,
g_wlbi_amounts(27).amount,
g_wlbi_amounts(28).amount,
g_wlbi_amounts(29).amount,
g_wlbi_amounts(30).amount,
g_wlbi_amounts(31).amount,
g_wlbi_amounts(32).amount,
g_wlbi_amounts(33).amount,
g_wlbi_amounts(34).amount,
g_wlbi_amounts(35).amount,
g_wlbi_amounts(36).amount,
g_wlbi_amounts(37).amount,
g_wlbi_amounts(38).amount,
g_wlbi_amounts(39).amount,
g_wlbi_amounts(40).amount,
g_wlbi_amounts(41).amount,
g_wlbi_amounts(42).amount,
g_wlbi_amounts(43).amount,
g_wlbi_amounts(44).amount,
g_wlbi_amounts(45).amount,
g_wlbi_amounts(46).amount,
g_wlbi_amounts(47).amount,
g_wlbi_amounts(48).amount,
g_wlbi_amounts(49).amount,
g_wlbi_amounts(50).amount,
g_wlbi_amounts(51).amount,
g_wlbi_amounts(52).amount,
g_wlbi_amounts(53).amount,
g_wlbi_amounts(54).amount,
g_wlbi_amounts(55).amount,
g_wlbi_amounts(56).amount,
g_wlbi_amounts(57).amount,
g_wlbi_amounts(58).amount,
g_wlbi_amounts(59).amount,
g_wlbi_amounts(60).amount,
g_wlbi_amounts(61).amount,
g_wlbi_amounts(62).amount,
g_wlbi_amounts(63).amount,
g_wlbi_amounts(64).amount,
g_wlbi_amounts(65).amount,
g_wlbi_amounts(66).amount,
g_wlbi_amounts(67).amount,
g_wlbi_amounts(68).amount,
g_wlbi_amounts(69).amount,
g_wlbi_amounts(70).amount,
g_wlbi_amounts(71).amount,
g_wlbi_amounts(72).amount,
g_wlbi_amounts(73).amount,
g_wlbi_amounts(74).amount,
g_wlbi_amounts(75).amount,
g_wlbi_amounts(76).amount,
g_wlbi_amounts(77).amount,
g_wlbi_amounts(78).amount,
g_wlbi_amounts(79).amount,
g_wlbi_amounts(80).amount,
g_wlbi_amounts(81).amount,
g_wlbi_amounts(82).amount,
g_wlbi_amounts(83).amount,
g_wlbi_amounts(84).amount,
g_wlbi_amounts(85).amount,
g_wlbi_amounts(86).amount,
g_wlbi_amounts(87).amount,
g_wlbi_amounts(88).amount,
g_wlbi_amounts(89).amount,
g_wlbi_amounts(90).amount,
g_wlbi_amounts(91).amount,
g_wlbi_amounts(92).amount,
g_wlbi_amounts(93).amount,
g_wlbi_amounts(94).amount,
g_wlbi_amounts(95).amount,
g_wlbi_amounts(96).amount,
g_wlbi_amounts(97).amount,
g_wlbi_amounts(98).amount,
g_wlbi_amounts(99).amount,
g_wlbi_amounts(100).amount,
g_wlbi_amounts(101).amount,
g_wlbi_amounts(102).amount,
g_wlbi_amounts(103).amount,
g_wlbi_amounts(104).amount,
g_wlbi_amounts(105).amount,
g_wlbi_amounts(106).amount,
g_wlbi_amounts(107).amount,
g_wlbi_amounts(108).amount,
g_wlbi_amounts(109).amount,
g_wlbi_amounts(110).amount,
g_wlbi_amounts(111).amount,
g_wlbi_amounts(112).amount,
g_wlbi_amounts(113).amount,
g_wlbi_amounts(114).amount,
g_wlbi_amounts(115).amount,
g_wlbi_amounts(116).amount,
g_wlbi_amounts(117).amount,
g_wlbi_amounts(118).amount,
g_wlbi_amounts(119).amount,
g_wlbi_amounts(120).amount,
g_wlbi_amounts(121).amount,
g_wlbi_amounts(122).amount,
g_wlbi_amounts(123).amount,
g_wlbi_amounts(124).amount,
g_wlbi_amounts(125).amount,
g_wlbi_amounts(126).amount,
g_wlbi_amounts(127).amount,
g_wlbi_amounts(128).amount,
g_wlbi_amounts(129).amount,
g_wlbi_amounts(130).amount,
g_wlbi_amounts(131).amount,
g_wlbi_amounts(132).amount,
g_wlbi_amounts(133).amount,
g_wlbi_amounts(134).amount,
g_wlbi_amounts(135).amount,
g_wlbi_amounts(136).amount,
g_wlbi_amounts(137).amount,
g_wlbi_amounts(138).amount,
g_wlbi_amounts(139).amount,
g_wlbi_amounts(140).amount,
g_wlbi_amounts(141).amount,
g_wlbi_amounts(142).amount,
g_wlbi_amounts(143).amount,
g_wlbi_amounts(144).amount,
g_wlbi_amounts(145).amount,
g_wlbi_amounts(146).amount,
g_wlbi_amounts(147).amount,
g_wlbi_amounts(148).amount,
g_wlbi_amounts(149).amount,
g_wlbi_amounts(150).amount,
g_wlbi_amounts(151).amount,
g_wlbi_amounts(152).amount,
g_wlbi_amounts(153).amount,
g_wlbi_amounts(154).amount,
g_wlbi_amounts(155).amount,
g_wlbi_amounts(156).amount,
g_wlbi_amounts(157).amount,
g_wlbi_amounts(158).amount,
g_wlbi_amounts(159).amount,
g_wlbi_amounts(160).amount,
g_wlbi_amounts(161).amount,
g_wlbi_amounts(162).amount,
g_wlbi_amounts(163).amount,
g_wlbi_amounts(164).amount,
g_wlbi_amounts(165).amount,
g_wlbi_amounts(166).amount,
g_wlbi_amounts(167).amount,
g_wlbi_amounts(168).amount,
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE
);
-- Insert Statement
INSERT INTO psb_ws_line_balances_i(
EXPORT_ID,
EXPORT_WORKSHEET_TYPE,
CODE_COMBINATION_ID,
BUDGET_GROUP_ID,
CONCATENATED_ACCOUNT,
ACCOUNT_DESCRIPTION,
SERVICE_PACKAGE_ID,
SERVICE_PACKAGE_NAME,
PAY_ELEMENT_SET_ID,
PAY_ELEMENT_ID,
PAY_ELEMENT_NAME,
SALARY_ACCOUNT_LINE,
FOLLOW_SALARY,
POSITION_LINE_ID,
POSITION_ID,
POSITION_NAME,
POSITION_SEGMENTS,
JOB_NAME,
EMPLOYEE_ID,
EMPLOYEE_NUMBER,
EMPLOYEE_NAME,
VALUE_TYPE,
PERCENT_OF_SALARY_FLAG,
AMOUNT1,
AMOUNT2,
AMOUNT3,
AMOUNT4,
AMOUNT5,
AMOUNT6,
AMOUNT7,
AMOUNT8,
AMOUNT9,
AMOUNT10,
AMOUNT11,
AMOUNT12,
AMOUNT13,
AMOUNT14,
AMOUNT15,
AMOUNT16,
AMOUNT17,
AMOUNT18,
AMOUNT19,
AMOUNT20,
AMOUNT21,
AMOUNT22,
AMOUNT23,
AMOUNT24,
AMOUNT25,
AMOUNT26,
AMOUNT27,
AMOUNT28,
AMOUNT29,
AMOUNT30,
AMOUNT31,
AMOUNT32,
AMOUNT33,
AMOUNT34,
AMOUNT35,
AMOUNT36,
AMOUNT37,
AMOUNT38,
AMOUNT39,
AMOUNT40,
AMOUNT41,
AMOUNT42,
AMOUNT43,
AMOUNT44,
AMOUNT45,
AMOUNT46,
AMOUNT47,
AMOUNT48,
AMOUNT49,
AMOUNT50,
AMOUNT51,
AMOUNT52,
AMOUNT53,
AMOUNT54,
AMOUNT55,
AMOUNT56,
AMOUNT57,
AMOUNT58,
AMOUNT59,
AMOUNT60,
AMOUNT61,
AMOUNT62,
AMOUNT63,
AMOUNT64,
AMOUNT65,
AMOUNT66,
AMOUNT67,
AMOUNT68,
AMOUNT69,
AMOUNT70,
AMOUNT71,
AMOUNT72,
AMOUNT73,
AMOUNT74,
AMOUNT75,
AMOUNT76,
AMOUNT77,
AMOUNT78,
AMOUNT79,
AMOUNT80,
AMOUNT81,
AMOUNT82,
AMOUNT83,
AMOUNT84,
AMOUNT85,
AMOUNT86,
AMOUNT87,
AMOUNT88,
AMOUNT89,
AMOUNT90,
AMOUNT91,
AMOUNT92,
AMOUNT93,
AMOUNT94,
AMOUNT95,
AMOUNT96,
AMOUNT97,
AMOUNT98,
AMOUNT99,
AMOUNT100,
AMOUNT101,
AMOUNT102,
AMOUNT103,
AMOUNT104,
AMOUNT105,
AMOUNT106,
AMOUNT107,
AMOUNT108,
AMOUNT109,
AMOUNT110,
AMOUNT111,
AMOUNT112,
AMOUNT113,
AMOUNT114,
AMOUNT115,
AMOUNT116,
AMOUNT117,
AMOUNT118,
AMOUNT119,
AMOUNT120,
AMOUNT121,
AMOUNT122,
AMOUNT123,
AMOUNT124,
AMOUNT125,
AMOUNT126,
AMOUNT127,
AMOUNT128,
AMOUNT129,
AMOUNT130,
AMOUNT131,
AMOUNT132,
AMOUNT133,
AMOUNT134,
AMOUNT135,
AMOUNT136,
AMOUNT137,
AMOUNT138,
AMOUNT139,
AMOUNT140,
AMOUNT141,
AMOUNT142,
AMOUNT143,
AMOUNT144,
AMOUNT145,
AMOUNT146,
AMOUNT147,
AMOUNT148,
AMOUNT149,
AMOUNT150,
AMOUNT151,
AMOUNT152,
AMOUNT153,
AMOUNT154,
AMOUNT155,
AMOUNT156,
AMOUNT157,
AMOUNT158,
AMOUNT159,
AMOUNT160,
AMOUNT161,
AMOUNT162,
AMOUNT163,
AMOUNT164,
AMOUNT165,
AMOUNT166,
AMOUNT167,
AMOUNT168,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATED_DATE)
values(
g_export_id,
'P',
null,
null,
null,
null,
null,
null,
g_ps_elements(i).pay_element_set_id,
g_ps_elements(i).pay_element_id,
g_ps_elements(i).pay_element_name,
null,
null,
position_rec.position_line_id,
position_rec.position_id,
position_rec.position_name,
position_rec.position_segments,
l_job_name,
l_employee_id,
l_employee_number,
l_employee_name,
'P', -- For Percent rows
null,
g_wlbi_amounts(1).amount,
g_wlbi_amounts(2).amount,
g_wlbi_amounts(3).amount,
g_wlbi_amounts(4).amount,
g_wlbi_amounts(5).amount,
g_wlbi_amounts(6).amount,
g_wlbi_amounts(7).amount,
g_wlbi_amounts(8).amount,
g_wlbi_amounts(9).amount,
g_wlbi_amounts(10).amount,
g_wlbi_amounts(11).amount,
g_wlbi_amounts(12).amount,
g_wlbi_amounts(13).amount,
g_wlbi_amounts(14).amount,
g_wlbi_amounts(15).amount,
g_wlbi_amounts(16).amount,
g_wlbi_amounts(17).amount,
g_wlbi_amounts(18).amount,
g_wlbi_amounts(19).amount,
g_wlbi_amounts(20).amount,
g_wlbi_amounts(21).amount,
g_wlbi_amounts(22).amount,
g_wlbi_amounts(23).amount,
g_wlbi_amounts(24).amount,
g_wlbi_amounts(25).amount,
g_wlbi_amounts(26).amount,
g_wlbi_amounts(27).amount,
g_wlbi_amounts(28).amount,
g_wlbi_amounts(29).amount,
g_wlbi_amounts(30).amount,
g_wlbi_amounts(31).amount,
g_wlbi_amounts(32).amount,
g_wlbi_amounts(33).amount,
g_wlbi_amounts(34).amount,
g_wlbi_amounts(35).amount,
g_wlbi_amounts(36).amount,
g_wlbi_amounts(37).amount,
g_wlbi_amounts(38).amount,
g_wlbi_amounts(39).amount,
g_wlbi_amounts(40).amount,
g_wlbi_amounts(41).amount,
g_wlbi_amounts(42).amount,
g_wlbi_amounts(43).amount,
g_wlbi_amounts(44).amount,
g_wlbi_amounts(45).amount,
g_wlbi_amounts(46).amount,
g_wlbi_amounts(47).amount,
g_wlbi_amounts(48).amount,
g_wlbi_amounts(49).amount,
g_wlbi_amounts(50).amount,
g_wlbi_amounts(51).amount,
g_wlbi_amounts(52).amount,
g_wlbi_amounts(53).amount,
g_wlbi_amounts(54).amount,
g_wlbi_amounts(55).amount,
g_wlbi_amounts(56).amount,
g_wlbi_amounts(57).amount,
g_wlbi_amounts(58).amount,
g_wlbi_amounts(59).amount,
g_wlbi_amounts(60).amount,
g_wlbi_amounts(61).amount,
g_wlbi_amounts(62).amount,
g_wlbi_amounts(63).amount,
g_wlbi_amounts(64).amount,
g_wlbi_amounts(65).amount,
g_wlbi_amounts(66).amount,
g_wlbi_amounts(67).amount,
g_wlbi_amounts(68).amount,
g_wlbi_amounts(69).amount,
g_wlbi_amounts(70).amount,
g_wlbi_amounts(71).amount,
g_wlbi_amounts(72).amount,
g_wlbi_amounts(73).amount,
g_wlbi_amounts(74).amount,
g_wlbi_amounts(75).amount,
g_wlbi_amounts(76).amount,
g_wlbi_amounts(77).amount,
g_wlbi_amounts(78).amount,
g_wlbi_amounts(79).amount,
g_wlbi_amounts(80).amount,
g_wlbi_amounts(81).amount,
g_wlbi_amounts(82).amount,
g_wlbi_amounts(83).amount,
g_wlbi_amounts(84).amount,
g_wlbi_amounts(85).amount,
g_wlbi_amounts(86).amount,
g_wlbi_amounts(87).amount,
g_wlbi_amounts(88).amount,
g_wlbi_amounts(89).amount,
g_wlbi_amounts(90).amount,
g_wlbi_amounts(91).amount,
g_wlbi_amounts(92).amount,
g_wlbi_amounts(93).amount,
g_wlbi_amounts(94).amount,
g_wlbi_amounts(95).amount,
g_wlbi_amounts(96).amount,
g_wlbi_amounts(97).amount,
g_wlbi_amounts(98).amount,
g_wlbi_amounts(99).amount,
g_wlbi_amounts(100).amount,
g_wlbi_amounts(101).amount,
g_wlbi_amounts(102).amount,
g_wlbi_amounts(103).amount,
g_wlbi_amounts(104).amount,
g_wlbi_amounts(105).amount,
g_wlbi_amounts(106).amount,
g_wlbi_amounts(107).amount,
g_wlbi_amounts(108).amount,
g_wlbi_amounts(109).amount,
g_wlbi_amounts(110).amount,
g_wlbi_amounts(111).amount,
g_wlbi_amounts(112).amount,
g_wlbi_amounts(113).amount,
g_wlbi_amounts(114).amount,
g_wlbi_amounts(115).amount,
g_wlbi_amounts(116).amount,
g_wlbi_amounts(117).amount,
g_wlbi_amounts(118).amount,
g_wlbi_amounts(119).amount,
g_wlbi_amounts(120).amount,
g_wlbi_amounts(121).amount,
g_wlbi_amounts(122).amount,
g_wlbi_amounts(123).amount,
g_wlbi_amounts(124).amount,
g_wlbi_amounts(125).amount,
g_wlbi_amounts(126).amount,
g_wlbi_amounts(127).amount,
g_wlbi_amounts(128).amount,
g_wlbi_amounts(129).amount,
g_wlbi_amounts(130).amount,
g_wlbi_amounts(131).amount,
g_wlbi_amounts(132).amount,
g_wlbi_amounts(133).amount,
g_wlbi_amounts(134).amount,
g_wlbi_amounts(135).amount,
g_wlbi_amounts(136).amount,
g_wlbi_amounts(137).amount,
g_wlbi_amounts(138).amount,
g_wlbi_amounts(139).amount,
g_wlbi_amounts(140).amount,
g_wlbi_amounts(141).amount,
g_wlbi_amounts(142).amount,
g_wlbi_amounts(143).amount,
g_wlbi_amounts(144).amount,
g_wlbi_amounts(145).amount,
g_wlbi_amounts(146).amount,
g_wlbi_amounts(147).amount,
g_wlbi_amounts(148).amount,
g_wlbi_amounts(149).amount,
g_wlbi_amounts(150).amount,
g_wlbi_amounts(151).amount,
g_wlbi_amounts(152).amount,
g_wlbi_amounts(153).amount,
g_wlbi_amounts(154).amount,
g_wlbi_amounts(155).amount,
g_wlbi_amounts(156).amount,
g_wlbi_amounts(157).amount,
g_wlbi_amounts(158).amount,
g_wlbi_amounts(159).amount,
g_wlbi_amounts(160).amount,
g_wlbi_amounts(161).amount,
g_wlbi_amounts(162).amount,
g_wlbi_amounts(163).amount,
g_wlbi_amounts(164).amount,
g_wlbi_amounts(165).amount,
g_wlbi_amounts(166).amount,
g_wlbi_amounts(167).amount,
g_wlbi_amounts(168).amount,
SYSDATE,
g_user_id,
g_user_id,
g_user_id,
SYSDATE
);
select psb_export_s.nextval into l_export_id from dual;
select MIN(start_date) start_date , MAX(end_date) end_date
from psb_budget_periods bp
where bp.budget_calendar_id = p_budget_calendar_id
and budget_period_type = 'Y';
select bp.budget_period_id, yt.year_category_type, bp.start_date, 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 bp.budget_calendar_id = p_budget_calendar_id
order by bp.start_date;
ELSIF p_source_process = 'DELETE_WORKSHEET' THEN
FND_MESSAGE.SET_NAME('PSB', 'PSB_DEL_WORKSHT_ERR_MSG_HDR');
delete from PSB_ERROR_MESSAGES
where source_process = p_source_process
and process_id = g_msg_export_id;
PSB_MESSAGE_S.Insert_Error ( p_source_process => p_source_process,
p_process_id => g_msg_export_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_buf) ;
| PROCEDURE Delete_Worksheet |
+==========================================================================*/
--
-- The Program Deletes the Worksheet from the Interface
--
PROCEDURE Del_Worksheet
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
--
p_export_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Del_Worksheet' ;
DELETE FROM psb_ws_line_balances_i
WHERE export_id = p_export_id;
DELETE FROM psb_ws_columns_i
WHERE export_id = p_export_id;
DELETE FROM psb_worksheets_i
WHERE export_id = p_export_id;
Log_Messages(p_source_process => 'DELETE_WORKSHEET');
Log_Messages(p_source_process => 'DELETE_WORKSHEET');
Log_Messages(p_source_process => 'DELETE_WORKSHEET');