[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_dyt_pkg_head.delete(l_index);
g_dyt_pkg_body.delete(l_index);
FUNCTION insert_parameters(
p_usage_type IN VARCHAR2 DEFAULT NULL,
p_usage_id IN NUMBER DEFAULT NULL,
p_parameter_type IN VARCHAR2 DEFAULT NULL,
p_parameter_name IN VARCHAR2 DEFAULT NULL,
p_value_name IN VARCHAR2 DEFAULT NULL,
p_automatic IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
--
-- Get the next primary key value from the database sequence
CURSOR get_id IS
SELECT pay_trigger_parameters_s.NEXTVAL
FROM dual;
SELECT parameter_id,automatic
FROM pay_trigger_parameters
WHERE UPPER(parameter_name) = UPPER(cp_name)
AND usage_type = cp_type
AND usage_id = cp_id;
INSERT INTO pay_trigger_parameters(
parameter_id,
usage_type,
usage_id,
parameter_type,
parameter_name,
value_name,
automatic
) VALUES (
l_rc,
p_usage_type,
p_usage_id,
p_parameter_type,
p_parameter_name,
p_value_name,
p_automatic
);
UPDATE pay_trigger_parameters
SET usage_type = p_usage_type,
usage_id = p_usage_id,
parameter_type = p_parameter_type,
parameter_name = p_parameter_name,
value_name = p_value_name,
automatic = p_automatic
WHERE parameter_id = l_rc;
END insert_parameters;
FUNCTION insert_declarations(
p_event_id IN NUMBER DEFAULT NULL,
p_variable_name IN VARCHAR2 DEFAULT NULL,
p_data_type IN VARCHAR2 DEFAULT NULL,
p_variable_size IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
--
-- Get the next value from the primary key sequence
CURSOR get_id IS
SELECT pay_trigger_declarations_s.NEXTVAL
FROM dual;
INSERT INTO pay_trigger_declarations(
declaration_id,
event_id,
variable_name,
data_type,
variable_size
) VALUES (
l_rc,
p_event_id,
p_variable_name,
p_data_type,
p_variable_size
);
END insert_declarations;
FUNCTION insert_initialisations(
p_event_id IN NUMBER DEFAULT NULL,
p_process_order IN NUMBER DEFAULT NULL,
p_plsql_code IN VARCHAR2 DEFAULT NULL,
p_process_type IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
--
-- Fetch the next primary key value from the database sequence
CURSOR get_id IS
SELECT pay_trigger_initialisations_s.NEXTVAL
FROM dual;
INSERT INTO pay_trigger_initialisations(
initialisation_id,
event_id,
process_order,
plsql_code,
process_type
) VALUES (
l_rc,
p_event_id,
p_process_order,
p_plsql_code,
p_process_type
);
END insert_initialisations;
FUNCTION insert_support(
p_event_id IN NUMBER DEFAULT NULL,
p_header_code IN VARCHAR2 DEFAULT NULL,
p_body_code IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
--
-- Fetch the next value from the primary key generating sequence
CURSOR get_id IS
SELECT pay_trigger_support_s.NEXTVAL
FROM dual;
INSERT INTO pay_trigger_support(
support_id,
event_id,
header_code,
body_code
) VALUES (
l_rc,
p_event_id,
p_header_code,
p_body_code
);
END insert_support;
SELECT fou.oracle_username
FROM fnd_oracle_userid fou,
fnd_product_installations fpi,
fnd_application fa
WHERE fou.oracle_id = fpi.oracle_id
AND fpi.application_id = fa.application_id
AND fa.application_short_name = cp_appl;
SELECT fa.application_short_name
FROM fnd_application fa,
fnd_tables ft
WHERE fa.application_id = ft.application_id
AND ft.table_name = cp_name;
SELECT atr.table_name
FROM user_triggers atr
WHERE trigger_name = cp_name;
SELECT 'Y'
FROM user_triggers
WHERE trigger_name = cp_name;
SELECT table_name,
triggering_action
FROM pay_trigger_events
WHERE event_id = cp_id;
l_mode := 'INSERT';
l_mode := 'UPDATE';
l_mode := 'DELETE';
SELECT parameter_name,
value_name
FROM pay_trigger_parameters
WHERE usage_type = 'I'
AND parameter_type = 'I'
AND usage_id = cp_id;
PROCEDURE map_select_list(
p_id IN NUMBER,
p_code IN VARCHAR2,
p_validate_only IN BOOLEAN DEFAULT FALSE
) IS
--
l_csr INTEGER;
l_rc := insert_parameters(
'I',
p_id,
'R',
LOWER(l_desc(l_cnt).col_name),
l_name,
'Y'
);
END map_select_list;
l_rc := insert_parameters(
'I',
p_id,
'I',
SUBSTR(p_code,l_st,(l_en-l_st)+1),
l_name,
'Y'
);
l_rc := insert_parameters('I',p_id,'R',NULL,l_name,'Y');
PROCEDURE validate_select(
p_id IN NUMBER,
p_code IN VARCHAR2,
p_type IN VARCHAR2
) IS
BEGIN
-- Should the validate only flag be set on these?
-- I think it probably should, I'll check that later.
scan_for_placeholders(p_id,p_code,p_type);
map_select_list(p_id,p_code);
END validate_select;
l_rc := insert_parameters(
p_usage,
p_id,
l_mode,
NULL,
'return_variable',
'Y'
);
l_rc := insert_parameters(p_usage,p_id,l_mode,l_arg,l_name,'Y');
SELECT initialisation_id,
plsql_code,
process_type
FROM pay_trigger_initialisations
WHERE (initialisation_id = cp_comp OR cp_comp IS NULL)
AND (event_id = cp_id OR cp_id IS NULL);
SELECT component_id,
module_name
FROM pay_trigger_components
WHERE (component_id = cp_comp OR cp_comp IS NULL)
AND (event_id = cp_id OR cp_id IS NULL);
map_select_list(init_rec.initialisation_id,init_rec.plsql_code);
SELECT header_code,body_code
FROM pay_trigger_support
WHERE support_id = cp_id;
SELECT 'SOME'
FROM user_errors
WHERE name = UPPER(cp_name)
AND type = cp_type;
SELECT atc.nullable
FROM all_tab_columns atc
WHERE atc.column_name = 'BUSINESS_GROUP_ID'
AND atc.table_name = p_table
AND atc.owner = g_pay_schema;
SELECT atc.nullable
FROM all_tab_columns atc
WHERE atc.column_name = 'PAYROLL_ID'
AND atc.table_name = p_table
AND atc.owner = g_pay_schema;
l_rc := insert_declarations(p_event_id,'business_group_id','N');
l_rc := insert_declarations(p_event_id,'legislation_code','C',30);
l_rc := insert_declarations(p_event_id,'payroll_id','N');
p_bus_id := insert_initialisations(
p_event_id,
-20,
'$'||l_age||'_BUSINESS_GROUP_ID$',
'A'
);
p_leg_id := insert_initialisations(
p_event_id,
-10,
'SELECT legislation_code '||
'FROM per_business_groups '||
'WHERE business_group_id = $L_BUSINESS_GROUP_ID$',
'S'
);
p_pay_id := insert_initialisations(
p_event_id,
-30,
'$'||l_age||'_PAYROLL_ID$',
'A'
);
SELECT pte.table_name, pte.triggering_action,
pte.generated_flag, pte.enabled_flag,
pdt.dated_table_id, nvl(pdt.dyn_trigger_type,'T'),
pdt.dyn_trigger_package_name
FROM pay_dated_tables pdt,
pay_trigger_events pte
WHERE pte.table_name = pdt.table_name(+)
AND pte.event_id = cp_id;
update pay_dated_tables set dyn_trigger_package_name = null
where table_name = l_dyt_pkg and dated_table_id = l_tab_id;
update pay_trigger_events
set generated_flag = 'N', enabled_flag = 'N'
where event_id = p_id;
SELECT pte.event_id,
pte.table_name,
pts.header_code,
pts.body_code
FROM pay_trigger_events pte,
pay_trigger_support pts
WHERE pte.event_id = pts.event_id
AND pts.support_id = cp_id;
SELECT pte.table_name,
pte.triggering_action
FROM pay_trigger_events pte
WHERE pte.event_id = cp_id;
-- Insert some dummy code for the support package to give the user a hint
-- then compile it (for what it's worth :-)
l_rc := insert_support(
p_id,
'/* Add your support package header code here */',
'/* Add your support package body code here */'
);
SELECT 'l_'||variable_name variable_name,
DECODE(data_type,
'C','VARCHAR2',
'D','DATE',
'N','NUMBER'
) data_type,
data_type data_type_code,
variable_size
FROM pay_trigger_declarations
WHERE event_id = cp_id;
SELECT initialisation_id,
plsql_code,
process_type,
process_order
FROM pay_trigger_initialisations
WHERE event_id = cp_id
ORDER BY process_order;
SELECT parameter_name,
value_name
FROM pay_trigger_parameters
WHERE parameter_type = 'R'
AND usage_id = cp_id
AND usage_type = 'I'
ORDER BY parameter_id;
SELECT parameter_name,
value_name
FROM pay_trigger_parameters
WHERE parameter_type IN ('I','O')
AND value_name IS NOT NULL
AND usage_id = cp_id
AND usage_type = 'I'
ORDER BY parameter_id;
-- Add the parameter name to the select list and the return
-- variable to the 'into' clause
l_sel := l_sel||l_inner.parameter_name;
-- Concatenate the select list, into list and the remainder of the
-- select statement (the FROM clause onwards) to the PL/SQL code
-- N.B. This code relies on the SQL select only bringing back 1
-- record, otherwise it will fail at runtime, could be changed to
-- generate a cursor, but that's quite a big change
p_sql := p_sql||' SELECT '||l_sel||g_eol||' INTO '||l_int||g_eol;
SELECT parameter_name,
value_name
FROM pay_trigger_parameters
WHERE parameter_type IN ('I','O')
AND value_name IS NOT NULL
AND usage_id = cp_id
AND usage_type = 'C'
ORDER BY parameter_id;
SELECT component_id,
module_name
FROM pay_trigger_components
WHERE legislation_code IS NULL
AND business_group_id IS NULL
AND payroll_id IS NULL
AND enabled_flag = 'Y'
AND event_id = cp_id;
SELECT component_id,
legislation_code,
module_name
FROM pay_trigger_components
WHERE legislation_code IS NOT NULL
AND payroll_id IS NULL
AND business_group_id IS NULL
AND enabled_flag = 'Y'
AND event_id = cp_id
ORDER BY legislation_code;
SELECT component_id,
business_group_id,
module_name
FROM pay_trigger_components
WHERE legislation_code IS NULL
AND payroll_id IS NULL
AND business_group_id IS NOT NULL
AND enabled_flag = 'Y'
AND event_id = cp_id
ORDER BY business_group_id;
SELECT component_id,
payroll_id,
module_name
FROM pay_trigger_components
WHERE legislation_code IS NULL
AND business_group_id IS NULL
AND payroll_id IS NOT NULL
AND enabled_flag = 'Y'
AND event_id = cp_id
ORDER BY payroll_id;
SELECT pte.table_name,
pte.short_name,
pte.triggering_action,
DECODE(pte.triggering_action,
'I','Insert',
'U','Update',
'D','Delete'
),
pte.description
FROM pay_trigger_events pte
WHERE pte.event_id = cp_id;
PROCEDURE delete_event_children(p_id IN NUMBER) IS
--
-- Get some more information we need before we can delete the event
CURSOR get_trigger(cp_id IN NUMBER) IS
SELECT pte.table_name,
pte.triggering_action
FROM pay_trigger_events pte
WHERE pte.event_id = cp_id;
SELECT initialisation_id
FROM pay_trigger_initialisations
WHERE event_id = cp_id;
SELECT component_id
FROM pay_trigger_components
WHERE event_id = cp_id;
delete_initialisation_children(l_rec.initialisation_id);
DELETE
FROM pay_trigger_initialisations
WHERE event_id = p_id;
delete_component_children(l_rec.component_id);
DELETE
FROM pay_trigger_components
WHERE event_id = p_id;
DELETE
FROM pay_trigger_declarations
WHERE event_id = p_id;
DELETE
FROM pay_trigger_support
WHERE event_id = p_id;
END delete_event_children;
PROCEDURE delete_initialisation_children(p_id IN NUMBER) IS
BEGIN
--
-- Delete all the parameters that the requested initialisation uses
DELETE
FROM pay_trigger_parameters
WHERE usage_type = 'I'
AND usage_id = p_id;
END delete_initialisation_children;
PROCEDURE delete_component_children(p_id IN NUMBER) IS
BEGIN
--
-- Delete all the parameters that the requested component uses
DELETE
FROM pay_trigger_parameters
WHERE usage_type = 'C'
AND usage_id = p_id;
END delete_component_children;
PROCEDURE delete_parameters_directly(p_param_id IN NUMBER) IS
BEGIN
--
-- Delete a parameter directly, used by table event updates form
DELETE
FROM pay_trigger_parameters
WHERE parameter_id = p_param_id;
END delete_parameters_directly;
select parameter_name, usage_type, value_name
from pay_trigger_parameters
where usage_type in ('PI','PU','PD')
and usage_id = cp_tab_id
order by parameter_name;
select data_type
from all_tab_columns
where table_name = cp_tab_name
and column_name like cp_col_name
and owner = g_pay_schema
and rownum = 1; --Assuming if the params been truc'd to 30 then its the only col
SELECT /*+ ORDERED */ a.pls_type
FROM (select /*+ NO_MERGE */
DISTINCT hook_package
from hr_api_hooks h,
hr_api_modules m
where m.api_module_id = h.api_module_id
and m.module_name = cp_tab_name) V,
USER_OBJECTS B,
SYS.ARGUMENT$ A
WHERE A.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME = V.hook_package
AND A.LEVEL# = 0
AND A.argument = cp_param_name
AND B.object_type = 'PACKAGE'
AND rownum = 1;
select hook_package pkg, hook_procedure proc
,decode(hook_procedure, 'AFTER_INSERT', 'PI',
'AFTER_UPDATE', 'PU',
'AFTER_DELETE', 'PD') usage_type
from hr_api_hooks h, hr_api_modules m
where m.api_module_id = h.api_module_id
and m.module_name = cp_tab_name;
SELECT upper(value_name) name
FROM pay_trigger_parameters
WHERE parameter_type IN ('I','O')
AND value_name IS NOT NULL
AND usage_id in (select component_id
from pay_trigger_events te, pay_trigger_components tc
where te.event_id = tc.event_id
and triggering_action = cp_type
and te.table_name = cp_table_name)
AND usage_type = 'C'
AND value_name like ':%'
MINUS
/* aru params use this */
SELECT upper(parameter_name)
FROM pay_trigger_parameters
WHERE usage_type = 'P'||cp_type
AND usage_id = (select dated_table_id
from pay_dated_tables
where table_name = cp_table_name);
SELECT pdt.dated_table_id,
pdt.table_name,
nvl(pdt.dyn_trigger_type,'T'),
pdt.dyn_trigger_package_name,
decode(start_date_name,
null, 'N',
'Y')
FROM pay_dated_tables pdt
WHERE pdt.dated_table_id = (cp_tab_id);
SELECT pte.event_id, pte.short_name,
pte.triggering_action, pte.description,
DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info,
enabled_flag,generated_flag
FROM pay_trigger_events pte
WHERE pte.table_name = (cp_name)
AND nvl(pte.protected_flag,'N') <> 'Y';
gen_dyt_pkg_rhi_proc(l_tab_name,'I','INSERT',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
gen_dyt_pkg_rhi_proc(l_tab_name,'U','UPDATE',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
gen_dyt_pkg_rhi_proc(l_tab_name,'D','DELETE',l_hok_params,l_hs,l_bs,l_dyt_params, l_dyt_pkg_head_tbl, l_dyt_pkg_body_tbl, l_datetracked_table);
update pay_dated_tables
set dyn_trig_pkg_generated = l_flag
where table_name = l_tab_name
and dated_table_id = l_tab_id;
update pay_trigger_events
set generated_flag = l_flag,
enabled_flag = dyt_rec.enabled_flag
where event_id = dyt_rec.event_id;
update pay_trigger_events --failure so disabled
set generated_flag = l_flag,
enabled_flag = l_flag
where event_id = dyt_rec.event_id;
commit; --make sure updates are saved
SELECT pte.event_id, pte.short_name,
pte.triggering_action, pte.description,
DECODE(pte.triggering_action, 'I','Insert','U','Update', 'D','Delete' ) info
FROM pay_trigger_events pte
WHERE pte.table_name = (cp_name)
AND pte.triggering_action = (cp_action)
AND nvl(pte.protected_flag,'N') <> 'Y';
if (upper(p_dyt_info) = 'INSERT') then
p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_insert;'||g_eos;
if (upper(p_dyt_info) = 'UPDATE') then
p_bs := p_bs ||' pay_dyn_triggers.g_dyt_mode := hr_api.g_correction;'||g_eos;
SELECT enabled_flag
FROM pay_trigger_events
where short_name = cp_dyt;
select event_id,short_name from pay_trigger_events
where table_name = cp_table_name;
select api_module_id
from hr_api_modules
where module_name = p_table_name;
update pay_dated_tables
set dyn_trigger_type = p_dyt_type, dyn_trig_pkg_generated = 'N'
where table_name = p_table_name;
update hr_api_hook_calls
set enabled_flag = l_hooks
where api_hook_call_id in (
select api_hook_call_id
from hr_api_hook_calls ahc,
hr_api_hooks ah,
hr_api_modules am
where ahc.api_hook_id = ah.api_hook_id
and ah.api_module_id = am.api_module_id
and ahc.call_package = (select dyn_trigger_package_name
from pay_dated_tables
where table_name = am.module_name)
and am.module_name = p_table_name );
select count(*)
from hr_api_hook_calls ahc,
hr_api_hooks ah,
hr_api_modules am
where ahc.api_hook_id = ah.api_hook_id
and ah.api_module_id = am.api_module_id
and ahc.call_package = (select dyn_trigger_package_name
from pay_dated_tables
where table_name = am.module_name)
and am.module_name = p_table_name;
select dated_table_id,dyn_trigger_type,dyn_trigger_package_name,dyn_trig_pkg_generated
from pay_dated_tables
where table_name = p_table_name;
select status from user_objects
where object_type = 'PACKAGE BODY'
and object_name = cp_pkg;
select 'x'
from pay_trigger_declarations
where variable_name = cp_name
and event_id = cp_id;
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_created_by in number,
p_creation_date in date
) is
begin
insert into pay_trigger_events (
event_id,
table_name,
short_name,
description,
generated_flag,
enabled_flag,
protected_flag,
triggering_action,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
) values (
p_event_id,
p_table_name,
p_short_name,
p_description,
p_generated_flag,
p_enabled_flag,
p_protected_flag,
p_triggering_action,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_created_by,
p_creation_date
);
p_last_update_date in date,
p_last_updated_by in number,
p_last_update_login in number,
p_created_by in number,
p_creation_date in date
) is
begin
update pay_trigger_events
set table_name = p_table_name,
short_name = p_short_name,
description = p_description,
generated_flag = p_generated_flag,
enabled_flag = p_enabled_flag,
protected_flag = p_protected_flag,
triggering_action = p_triggering_action,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
created_by = p_created_by,
creation_date = p_creation_date
where event_id = p_event_id;
delete from pay_trigger_events
where event_id = p_event_id;
select *
from pay_trigger_events
where event_id = p_event_id
for update nowait;
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM fnd_tables tab
WHERE tab.table_name = p_table
AND (tab.application_id BETWEEN 800 AND 810
OR tab.application_id IN (8301,453,8302,8303,8403,203))
);
SELECT application_id
FROM fnd_tables
WHERE table_name = p_table
AND ((application_id < 800 OR application_id > 810)
AND application_id NOT IN (8301,453,8302,8303,8403,203));
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM fnd_columns col,fnd_tables tab
WHERE tab.table_name = p_table
AND col.table_id = tab.table_id
AND col.application_id = tab.application_id
AND col.column_name = p_column
AND (tab.application_id BETWEEN 800 AND 810
OR tab.application_id IN (8301,453,8302,8303,8403,203))
);
SELECT tab.application_id
FROM fnd_columns col,fnd_tables tab
WHERE tab.table_name = p_table
AND col.table_id = tab.table_id
AND col.application_id = tab.application_id
AND ((tab.application_id < 800 OR tab.application_id > 810)
AND tab.application_id NOT IN (8301,453,8302,8303,8403,203))
AND col.column_name = p_column;
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM fnd_tables tab,
fnd_product_installations prd,
fnd_oracle_userid usr
WHERE tab.table_name = p_table
AND tab.application_id = prd.application_id
AND (prd.application_id BETWEEN 800 AND 810
OR prd.application_id IN (8301,453,8302,8303,8403,203))
AND usr.oracle_id = prd.oracle_id
AND usr.oracle_username = p_owner
);
SELECT prd.application_id
FROM fnd_tables tab,
fnd_product_installations prd,
fnd_oracle_userid usr
WHERE tab.table_name = p_table
AND tab.application_id = prd.application_id
AND ((prd.application_id < 800 OR prd.application_id > 810)
AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
AND usr.oracle_id = prd.oracle_id
AND usr.oracle_username = p_owner;
SELECT usr.oracle_username
FROM fnd_tables tab,
fnd_product_installations prd,
fnd_oracle_userid usr
WHERE tab.table_name = p_table
AND tab.application_id = prd.application_id
AND (prd.application_id BETWEEN 800 AND 810
OR prd.application_id IN (8301,453,8302,8303,8403,203))
AND usr.oracle_id = prd.oracle_id;
SELECT usr.oracle_username,
prd.application_id
FROM fnd_tables tab,
fnd_product_installations prd,
fnd_oracle_userid usr
WHERE tab.table_name = p_table
AND tab.application_id = prd.application_id
AND ((prd.application_id < 800 OR prd.application_id > 810)
AND prd.application_id NOT IN (8301,453,8302,8303,8403,203))
AND usr.oracle_id = prd.oracle_id;