[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_spkg_selection_exists VARCHAR2,
/* Bug No 2543015 Start */
p_spkg_name VARCHAR2,
/* Bug No 2543015 End */
p_flexfield_low VARCHAR2,
p_flexfield_high VARCHAR2,
p_flexfield_delimiter VARCHAR2,
p_chart_of_accounts NUMBER,
p1_amount OUT NOCOPY NUMBER,
p2_amount OUT NOCOPY NUMBER,
p3_amount OUT NOCOPY NUMBER,
p4_amount OUT NOCOPY NUMBER,
p5_amount OUT NOCOPY NUMBER,
p6_amount OUT NOCOPY NUMBER,
p7_amount OUT NOCOPY NUMBER,
p8_amount OUT NOCOPY NUMBER,
p9_amount OUT NOCOPY NUMBER,
p10_amount OUT NOCOPY NUMBER,
p11_amount OUT NOCOPY NUMBER,
p12_amount OUT NOCOPY NUMBER,
p_year_amount OUT NOCOPY NUMBER
)
IS
l1_amount NUMBER;
SELECT APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS
WHERE ID_FLEX_CODE = l_id_flex_code -- bug #4924031
AND application_id = l_application_id -- bug #4924031
AND ID_FLEX_NUM = p_chart_of_accounts
AND SEGMENT_NUM = l_segment_num;
l_sql := 'SELECT ' ||
' NVL(SUM(decode(account_type,''A'',-period1_amount,''E'',-period1_amount,''D'',-period1_amount,period1_amount)),0) * :b_acct_type A ' ||
' ,NVL(SUM(decode(account_type,''A'',-period2_amount,''E'',-period2_amount,''D'',-period2_amount,period2_amount)),0) * :b_acct_type B ' ||
' ,NVL(SUM(decode(account_type,''A'',-period3_amount,''E'',-period3_amount,''D'',-period3_amount,period3_amount)),0) * :b_acct_type C ' ||
' ,NVL(SUM(decode(account_type,''A'',-period4_amount,''E'',-period4_amount,''D'',-period4_amount,period4_amount)),0) * :b_acct_type D ' ||
' ,NVL(SUM(decode(account_type,''A'',-period5_amount,''E'',-period5_amount,''D'',-period5_amount,period5_amount)),0) * :b_acct_type E ' ||
' ,NVL(SUM(decode(account_type,''A'',-period6_amount,''E'',-period6_amount,''D'',-period6_amount,period6_amount)),0) * :b_acct_type F ' ||
' ,NVL(SUM(decode(account_type,''A'',-period7_amount,''E'',-period7_amount,''D'',-period7_amount,period7_amount)),0) * :b_acct_type G ' ||
' ,NVL(SUM(decode(account_type,''A'',-period8_amount,''E'',-period8_amount,''D'',-period8_amount,period8_amount)),0) * :b_acct_type H ' ||
' ,NVL(SUM(decode(account_type,''A'',-period9_amount,''E'',-period9_amount,''D'',-period9_amount,period9_amount)),0) * :b_acct_type I ' ||
' ,NVL(SUM(decode(account_type,''A'',-period10_amount,''E'',-period10_amount,''D'',-period10_amount,period10_amount)),0) * :b_acct_type J ' ||
' ,NVL(SUM(decode(account_type,''A'',-period11_amount,''E'',-period11_amount,''D'',-period11_amount,period11_amount)),0) * :b_acct_type K ' ||
' ,NVL(SUM(decode(account_type,''A'',-period12_amount,''E'',-period12_amount,''D'',-period12_amount,period12_amount)),0) * :b_acct_type L ' ||
' ,NVL(SUM(decode(account_type,''A'',-ytd_amount,''E'',-ytd_amount,''D'',-ytd_amount,ytd_amount)),0) * :b_acct_type M ' ||
' FROM psb_ws_line_period_v WLP ' ||
' WHERE worksheet_id = :b_worksheet_id ' ||
' AND budget_year_id = :b_budget_year_id ' ||
' AND balance_type = :b_balance_type ' ||
' AND (:b_account_flag = ''T'' OR account_type = :b_account_flag ' ||
' OR (account_type = DECODE(:b_account_flag,''P'',''R'',''~'') OR account_type = DECODE(:b_account_flag,''P'',''E'',''~'')) '||
' OR (account_type = DECODE(:b_account_flag,''N'',''A'',''~'') OR account_type = DECODE(:b_account_flag,''N'',''L'',''~'')) '||
' OR (account_type = DECODE(:b_account_flag,''B'',''D'',''~'') OR account_type = DECODE(:b_account_flag,''B'',''C'',''~'')) '||
' ) '||
/* Bug No 3140882 End */
' AND ( (:b_currency_flag = ''C'' AND currency_code <> ''STAT'') ' ||
' OR ' ||
' (:b_currency_flag = ''S'' AND currency_code = ''STAT'') ' ||
' ) ' ||
' AND ( (:b_template_id is NULL AND template_id is null) ' ||
' OR ' ||
' (:b_template_id is NOT NULL AND template_id = :b_template_id) ' ||
' ) ' ||
/* Bug No 2543015 Start */
' AND ( :b_spkg_flag = ''A'' ' ||
' OR (:b_spkg_selection_exists = ''N'' ' ||
' AND service_package_id in ( select sp.service_package_id ' ||
' from PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w ' ||
' where sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id) ' ||
' and w.worksheet_id = :b_profile_worksheet_id ' ||
' and sp.name like :b_spkg_name) ' ||
' ) ' ||
' OR (:b_spkg_selection_exists = ''Y'' ' ||
' AND service_package_id in ( select service_package_id ' ||
' from PSB_WS_SERVICE_PKG_PROFILES_V ' ||
' where worksheet_id = :b_profile_worksheet_id ' ||
' and (user_id = :b_user_id or (:b_user_id is null and user_id is null)) ' ||
' and service_package_name like :b_spkg_name) ' ||
' ) ' ||
' ) ' ;
' = (select code_combination_id from gl_code_combinations '||
' where WLP.code_combination_id = code_combination_id '||
' and chart_of_accounts_id = :b_chart_of_accounts ' ||
query_condition || '))' ;
dbms_sql.bind_variable(l_cursor_id, ':b_spkg_selection_exists', p_spkg_selection_exists);
PROCEDURE Get_Data_Selection_Profile
(
p_current_worksheet_id IN NUMBER,
p_current_user_id IN NUMBER,
p_global_profile_user_id IN NUMBER,
p_profile_worksheet_id OUT NOCOPY NUMBER,
p_profile_user_id OUT NOCOPY NUMBER
)
IS
l_global_worksheet_flag varchar2(1):= NULL;
SELECT '1'
FROM psb_ws_user_profiles
WHERE worksheet_id = v_ws_id
AND user_id = v_user_id;
SELECT '1'
FROM psb_ws_user_profiles
WHERE worksheet_id = v_global_worksheet_id
AND user_id IS NULL;
SELECT global_worksheet_flag,
global_worksheet_id,
local_copy_flag,
copy_of_worksheet_id
FROM psb_worksheets
WHERE worksheet_id = p_current_worksheet_id;
l_inherit_profile := FND_PROFILE.VALUE('PSB_INHERIT_DATA_SELECTION_PROFILE');
End Get_Data_Selection_Profile;