APPS.FND_FORM_CUSTOM_RULES_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 5

** 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;

Line: 20

g_last_delete_rule_type   VARCHAR2(30) := NULL;

Line: 21

g_last_delete_function    VARCHAR2(30) := NULL;

Line: 22

g_last_delete_form        VARCHAR2(30) := NULL;

Line: 30

** 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;

Line: 58

    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;

Line: 67

    ** 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 )));

Line: 79

    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 )));

Line: 87

     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 )));

Line: 95

    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 ));

Line: 103

    ** save last delete context
    */
    g_last_delete_rule_key   := X_RULE_KEY;

Line: 106

    g_last_delete_rule_type  := X_RULE_TYPE;

Line: 107

    g_last_delete_function   := X_FUNCTION_NAME;

Line: 108

    g_last_delete_form       := X_FORM_NAME;

Line: 110

END DELETE_SET;