The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Last Delete Context:
**
** The Forms Personalization Loader (affrmcus.lct) uses a replace strategy
** for upload (delete then insert) rather than the ususal merge. This is
** because the individual personalization rules do not have a portable unique
** key, and also for simplicity of operation.
**
** Since there can be multiple rules per target form/function/rule key
** combination, we must only delete the data for a particular context
** once during an upload session.
**
** Warning: requires all rules for a given context to be loaded from the same
** data file (LDT) and requires rules within the file to be grouped by context.
*/
g_last_delete_rule_key VARCHAR2(30) := NULL;
g_last_delete_rule_type VARCHAR2(30) := NULL;
g_last_delete_function VARCHAR2(30) := NULL;
g_last_delete_form VARCHAR2(30) := NULL;
** DELETE_SET - Delete a set of Forms Personalization Rules
** (see AFFRRULS.pls for spec)
*/
PROCEDURE DELETE_SET(
X_RULE_KEY IN VARCHAR2,
X_RULE_TYPE IN VARCHAR2,
X_FUNCTION_NAME IN VARCHAR2,
X_FORM_NAME IN VARCHAR2) IS
BEGIN
/*
** Check if last delete context matches exactly
**
** Note: A NULL RULE_KEY is considered a valid, distinct context value
** but the other values should not be null
*/
/* Bug 9975835 Added g_curr_seq and set to FND_FORM_CUSTOM_RULES_S.NextVal
** Once we have deleted all of the personalizations for a given form where the
** rule type is F, we don't want to delete again if processing an ldt that has multiple
** functions that with a rule type of F for a given form.
** When we insert the data into the FND_FORM_CUSTOM_RULES, the id has a value of FND_FORM_CUSTOM_RULES_S.NextVal (see affrmcus.lct).
** Since we delete before inserting the new rows will always have an id greater then the value of g_curr_seq
** WHERE clauses modified for the X_RULE_TYPE = 'F' to only delete rows that have an id < g_curr_seq
*/
if g_curr_seq = 0 then
select FND_FORM_CUSTOM_RULES_S.NextVal into g_curr_seq from dual;
if nvl(X_RULE_KEY, '*NULL*') = nvl(g_last_delete_rule_key, '*NULL*') and
X_RULE_TYPE = g_last_delete_rule_type and
((X_RULE_TYPE = 'A' and X_FUNCTION_NAME = g_last_delete_function) or
(X_RULE_TYPE = 'F' and X_FORM_NAME = g_last_delete_form)) then
/* delete for this context was already completed, just exit */
return;
** Delete Forms Personalizations for specified context
*/
delete from fnd_form_custom_params
where action_id in
(select action_id
from fnd_form_custom_actions a , fnd_form_custom_rules r
where a.rule_id = r.id
and nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
(X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
delete from fnd_form_custom_actions
where rule_id in
(select id
from fnd_form_custom_rules r
where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
(X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
delete from fnd_form_custom_scopes
where rule_id in
(select id
from fnd_form_custom_rules r
where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
(X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
delete from fnd_form_custom_rules r
where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
(X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq ));
** save last delete context
*/
g_last_delete_rule_key := X_RULE_KEY;
g_last_delete_rule_type := X_RULE_TYPE;
g_last_delete_function := X_FUNCTION_NAME;
g_last_delete_form := X_FORM_NAME;
END DELETE_SET;