[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT o.profile_option_name,
v.profile_option_value,
v.level_id,
v.level_value
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND v.level_id <> 10004
AND o.profile_option_name IN ('PN_ACCOUNTING_OPTION',
'PN_SET_OF_BOOKS_ID',
'PN_AUTOMATIC_COMPANY_NUMBER',
'PN_AUTOMATIC_INDEX_RENT_NUMBERING',
'PN_AUTOMATIC_LEASE_NUMBER',
'PN_AUTOMATIC_SPACE_DISTRIBUTION',
'PN_AUTO_VAR_RENT_NUM',
'PN_CURRENCY_CONV_RATE_TYPE',
'PN_SPASGN_CHNGDT_OPTN',
'PN_MULTIPLE_LEASE_FOR_LOCATION',
'PN_MULT_TNC_FOR_SAME_LEASE')
ORDER BY 1, 3 DESC;
SELECT hr.organization_id org_id,
hr.set_of_books_id set_of_books_id
FROM hr_operating_units hr,
fnd_profile_options o,
fnd_profile_option_values v
WHERE v.profile_option_id = o.profile_option_id
AND o.profile_option_name = 'ORG_ID'
AND v.level_id <> 10004
AND TRIM(v.profile_option_value) = TO_CHAR(hr.organization_id)
GROUP BY hr.organization_id, hr.set_of_books_id
ORDER BY 1;
SELECT TO_NUMBER(v.profile_option_value) org_id, v.level_id
FROM fnd_profile_option_values v, fnd_profile_options o
WHERE v.profile_option_id = o.profile_option_id
AND o.profile_option_name = 'ORG_ID'
AND v.level_id IN (10002,10001)
ORDER BY 2;
SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'ORG_ID';
def_set_of_books_tbl.delete;
profile_value_tbl.delete;
| PURPOSE : inserts data into the pn_system_setup_options table
| NOTE : o assumes -1 is not a valid level_value
| o optimization note:
| oo the search algorithm assumes the tables are structured in a
| specific order
| oo for entries with the same org_id, the one with the most user
| is placed on top of list
| HISTORY :
| 26-MAR-02 ftanudja created.
| 11-SEP-02 ftanudja added handle for PN_MULTIPLE_LEASE_FOR_LOCATION.
| 30-JUN-03 ftanudja during INSERT, added new required columns from recovery
| module. And populated them with default values
| (since these are new profiles)
| 22-JUL-04 atuppad Optimized the main cursor for performance bug#3779117.
| Also, issued a mass insert on the table.
| 26-JUL-04 atuppad Added the default value of default_user_view_code col.
| 30-AUG-04 ftanudja add default value of 'extend_indexrent_term_flag'
| Reference bug #3756208.
| 28-OCT-04 atuppad o Added code for 4 columns of Retro.
| 30-DEC-04 kkhegde o Added calc_annualized_basis_code column.
| 15-DEC-05 kkhegde o Added recalc_ir_on_acc_chg_flag column default 'Y'
| 23-MAR-06 Hareesha o Bug 5106419 Modified to handle case when
| mandatory columns of pn_system_setup_options are NULL.
| 24-MAR-06 Hareesha o Bug 5106419 Needed to update the default value of
| ACCOUNTING_OPTION
| 03-APR-06 Kiran o Bug 5135571 changed OrgId, RespId, Count to tables
| INDEX BY BINARY_INTEGER
\----------------------------------------------------------------------------*/
PROCEDURE populate_profile_tbl
IS
CURSOR active_pn_resp_cur IS
SELECT NVL(v.profile_option_value, g_default_org_id)
org_id,
r.responsibility_id resp_id,
COUNT(u.user_id) num_users
FROM fnd_user u,
wf_user_roles wur,
fnd_responsibility r,
fnd_profile_option_values v
WHERE r.application_id = 240
AND r.responsibility_id = wur.role_orig_system_id (+)
AND wur.role_orig_system (+) = 'FND_RESP'
AND not wur.role_name (+) like 'FND_RESP|%|ANY'
AND u.user_name(+) = wur.user_name
AND r.start_date <= SYSDATE
AND NVL(r.end_date, SYSDATE) >= SYSDATE
AND v.profile_option_id = g_profileid4orgid
AND v.level_value = r.responsibility_id
AND v.profile_option_value NOT IN (SELECT org_id
FROM pn_system_setup_options)
AND v.level_value_application_id = 240
AND v.level_id = 10003
GROUP BY r.responsibility_id, v.profile_option_value
UNION
SELECT TO_CHAR(g_default_org_id) org_id,
0 resp_id,
0 num_users
FROM dual
WHERE NOT EXISTS (SELECT NULL
FROM fnd_profile_option_values v
WHERE v.profile_option_id = g_profileid4orgid
AND v.profile_option_value = g_default_org_id
AND v.level_id = 10003
AND v.level_value_application_id = 240
AND EXISTS (SELECT null
FROM pn_system_setup_options
WHERE org_id = g_default_org_id))
ORDER BY 1,3 DESC;
SELECT nvl(multi_org_flag, 'N') multi_org
FROM fnd_product_groups;
l_info_text := 'Selecting multi org flag';
orgids.DELETE;
respids.DELETE;
counts.DELETE;
INSERT INTO pn_system_setup_options
(profile_id,
org_id,
accounting_option,
set_of_books_id,
default_currency_conv_type,
space_assign_sysdate_optn,
multiple_tenancy_lease,
auto_comp_num_gen,
auto_lease_num_gen,
auto_index_num_gen,
auto_space_distribution,
auto_var_rent_num_gen,
auto_rec_agr_num_flag,
auto_rec_exp_num_flag,
auto_rec_arcl_num_flag,
auto_rec_expcl_num_flag,
cons_rec_agrterms_flag,
default_locn_area_flag,
default_user_view_code,
extend_indexrent_term_flag,
sysdate_for_adj_flag,
sysdate_as_trx_date_flag,
renorm_adj_acc_all_draft_flag,
consolidate_adj_items_flag,
calc_annualized_basis_code,
allow_tenancy_overlap_flag,
recalc_ir_on_acc_chg_flag,
created_by,
last_update_login,
last_updated_by,
creation_date,
last_update_date)
VALUES(pn_system_setup_options_s.nextval,
orgids(i),
NVL(get_value(respids(i), 'PN_ACCOUNTING_OPTION'),'Y'),
l_set_of_books_id,
get_value(respids(i), 'PN_CURRENCY_CONV_RATE_TYPE'),
NVL(get_value(respids(i), 'PN_SPASGN_CHNGDT_OPTN'),'Y'),
NVL(get_value(respids(i), 'PN_MULTIPLE_LEASE_FOR_LOCATION'),'N'),
NVL(get_value(respids(i), 'PN_AUTOMATIC_COMPANY_NUMBER'),'Y'),
NVL(get_value(respids(i), 'PN_AUTOMATIC_LEASE_NUMBER'),'Y'),
NVL(get_value(respids(i), 'PN_AUTOMATIC_INDEX_RENT_NUMBERING'),'N'),
NVL(get_value(respids(i), 'PN_SPACE_DISTRIBUTION'),'N'),
NVL(get_value(respids(i), 'PN_AUTO_VAR_RENT_NUMBER'),'N'),
'N',
'N',
'N',
'N',
'N',
'N',
'TENANT',
'Y',
'Y',
'N',
'Y',
'N',
'PERIOD',
NVL(get_value(respids(i), 'PN_MULT_TNC_FOR_SAME_LEASE'),'N'),
'Y',
NVL(fnd_global.user_id, -1),
NVL(fnd_global.user_id, -1),
NVL(fnd_global.user_id, -1),
SYSDATE,
SYSDATE);
SELECT o.profile_option_name,
v.profile_option_value,
v.level_id,
v.level_value
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND v.level_id <> 10004
AND o.profile_option_name IN ('PN_GL_TRANSFER_MODE','PN_SUBMIT_JOURNAL_IMPORT')
ORDER BY 1, 3 DESC;
SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'ORG_ID';
SELECT TO_NUMBER(v.profile_option_value) org_id, v.level_id
FROM fnd_profile_option_values v, fnd_profile_options o
WHERE v.profile_option_id = o.profile_option_id
AND o.profile_option_name = 'ORG_ID'
AND v.level_id IN (10002,10001)
ORDER BY 2;
profile_value_tbl.delete;
| PROCEDURE : update_profile_tbl
| PURPOSE : updates data into the pn_system_setup_options table for migration
| DESCRIPTION: This procedure will update the pn_system_setup_option table for
| migration of GL_TRANSFER_MODE and SUBMIT_JOURNAL_IMPORT
| NOTE : Please refer to populate_profile_tbl procedure.
| HISTORY :
| 28-JAN-04 atuppad o created.
| 22-JUL-04 atuppad o Optimized the main cursor for performance bug#3779117.
| Also, issued a mass update on the table.
\----------------------------------------------------------------------------*/
PROCEDURE update_profile_tbl
IS
CURSOR active_pn_resp_cur IS
SELECT NVL(v.profile_option_value, g_default_org_id)
org_id,
r.responsibility_id resp_id,
COUNT(u.user_id) num_users
FROM fnd_user u,
wf_user_roles wur,
fnd_responsibility r,
fnd_profile_option_values v
WHERE r.application_id = 240
AND r.responsibility_id = wur.role_orig_system_id (+)
AND wur.role_orig_system (+) = 'FND_RESP'
AND not wur.role_name (+) like 'FND_RESP|%|ANY'
AND u.user_name(+) = wur.user_name
AND r.start_date <= SYSDATE
AND NVL(r.end_date, SYSDATE) >= SYSDATE
AND v.profile_option_id = g_profileid4orgid
AND v.level_value = r.responsibility_id
AND v.profile_option_value IN (SELECT org_id
FROM pn_system_setup_options)
AND v.level_value_application_id = 240
AND v.level_id = 10003
GROUP BY r.responsibility_id, v.profile_option_value
UNION
SELECT TO_CHAR(g_default_org_id) org_id,
0 resp_id,
0 num_users
FROM dual
WHERE NOT EXISTS (SELECT NULL
FROM fnd_profile_option_values v
WHERE v.profile_option_id = g_profileid4orgid
AND v.profile_option_value = g_default_org_id
AND v.level_id = 10003
AND v.level_value_application_id = 240
AND EXISTS (SELECT null
FROM pn_system_setup_options
WHERE org_id = g_default_org_id))
ORDER BY 1,3 DESC;
SELECT nvl(multi_org_flag, 'N') multi_org
FROM fnd_product_groups;
l_info_text := 'Selecting multi org flag';
UPDATE PN_SYSTEM_SETUP_OPTIONS
SET gl_transfer_mode = get_value(respids(i),'PN_GL_TRANSFER_MODE'),
submit_journal_import_flag = get_value(respids(i),'PN_SUBMIT_JOURNAL_IMPORT'),
last_update_login = NVL(fnd_global.user_id, -1),
last_updated_by = NVL(fnd_global.user_id, -1),
last_update_date = SYSDATE
WHERE org_id = orgids(i);
END update_profile_tbl;