APPS.JTY_TERR_TRIGGER_HANDLERS SQL Statements

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

Line: 43

  IF (Trigger_Mode = 'ON-INSERT') THEN

    IF ((n_start_date_active < sysdate) AND (n_end_date_active > sysdate)) THEN
      INSERT INTO jty_changed_terrs (
         CHANGED_TERRITORY_ID
        ,OBJECT_VERSION_NUMBER
        ,TERR_ID
        ,CHANGE_TYPE
        ,RANK_CALC_FLAG
        ,PROCESS_ATTR_VALUES_FLAG
        ,MATCHING_SQL_FLAG
        ,HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,p_terr_id
        ,'CREATE'
        ,'Y'
        ,'I'
        ,decode(n_named_acct_flag, 'Y', 'N', 'Y')
        ,'I');

Line: 66

  ELSIF (Trigger_Mode = 'ON-UPDATE') THEN
    IF (o_rank is null and n_rank is not null) OR
       (o_rank is not null and n_rank is null) OR
       (o_rank <> n_rank) THEN
      SELECT count(*)
      INTO   l_no_of_records
      FROM   jty_changed_terrs
      WHERE  terr_id = p_terr_id
      AND    change_type = 'CREATE'
      AND    star_request_id IS NULL;

Line: 79

        USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
        ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
        WHEN MATCHED THEN
          UPDATE SET
             A.rank_calc_flag = 'Y'
        WHEN NOT MATCHED THEN
          INSERT (
             A.CHANGED_TERRITORY_ID
            ,A.OBJECT_VERSION_NUMBER
            ,A.TERR_ID
            ,A.SOURCE_ID
            ,A.CHANGE_TYPE
            ,A.RANK_CALC_FLAG
            ,A.PROCESS_ATTR_VALUES_FLAG
            ,A.MATCHING_SQL_FLAG
            ,A.HIER_PROCESSING_FLAG)
          VALUES (
             jty_changed_terrs_s.nextval
            ,0
            ,S.terr_id
            ,S.source_id
            ,'UPDATE'
            ,'Y'
            ,'N'
            ,'N'
            ,'N');

Line: 111

      SELECT count(*)
      INTO   l_no_of_records
      FROM   jty_changed_terrs
      WHERE  terr_id = p_terr_id
      AND    change_type = 'CREATE'
      AND    star_request_id IS NULL;

Line: 120

        USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
        ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
        WHEN MATCHED THEN
          UPDATE SET
             A.HIER_PROCESSING_FLAG = 'I'
        WHEN NOT MATCHED THEN
          INSERT (
             A.CHANGED_TERRITORY_ID
            ,A.OBJECT_VERSION_NUMBER
            ,A.TERR_ID
            ,A.SOURCE_ID
            ,A.CHANGE_TYPE
            ,A.RANK_CALC_FLAG
            ,A.PROCESS_ATTR_VALUES_FLAG
            ,A.MATCHING_SQL_FLAG
            ,A.HIER_PROCESSING_FLAG)
          VALUES (
             jty_changed_terrs_s.nextval
            ,0
            ,S.terr_id
            ,S.source_id
            ,'UPDATE'
            ,'N'
            ,'N'
            ,'N'
            ,'I');

Line: 152

      USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
      ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
      WHEN MATCHED THEN
        UPDATE SET
           A.rank_calc_flag = 'Y'
          ,A.process_attr_values_flag = 'I'
          ,A.matching_sql_flag = 'Y'
          ,A.hier_processing_flag = 'I'
      WHEN NOT MATCHED THEN
        INSERT (
           A.CHANGED_TERRITORY_ID
          ,A.OBJECT_VERSION_NUMBER
          ,A.TERR_ID
          ,A.SOURCE_ID
          ,A.CHANGE_TYPE
          ,A.RANK_CALC_FLAG
          ,A.PROCESS_ATTR_VALUES_FLAG
          ,A.MATCHING_SQL_FLAG
          ,A.HIER_PROCESSING_FLAG)
        VALUES (
           jty_changed_terrs_s.nextval
          ,0
          ,S.terr_id
          ,S.source_id
          ,'UPDATE'
          ,'Y'
          ,'I'
          ,'Y'
          ,'I');

Line: 188

        USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
        ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
        WHEN MATCHED THEN
          UPDATE SET
             A.rank_calc_flag = 'Y'
            ,A.process_attr_values_flag = 'I'
            ,A.matching_sql_flag = 'Y'
            ,A.hier_processing_flag = 'I'
        WHEN NOT MATCHED THEN
          INSERT (
             A.CHANGED_TERRITORY_ID
            ,A.OBJECT_VERSION_NUMBER
            ,A.TERR_ID
            ,A.SOURCE_ID
            ,A.CHANGE_TYPE
            ,A.RANK_CALC_FLAG
            ,A.PROCESS_ATTR_VALUES_FLAG
            ,A.MATCHING_SQL_FLAG
            ,A.HIER_PROCESSING_FLAG)
          VALUES (
             jty_changed_terrs_s.nextval
            ,0
            ,S.terr_id
            ,S.source_id
            ,'UPDATE'
            ,'Y'
            ,'I'
            ,'Y'
            ,'I');

Line: 223

        USING ( SELECT terr_id, source_id from jtf_terr_usgs_all where terr_id = p_terr_id ) S
        ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
        WHEN MATCHED THEN
          UPDATE SET
             A.rank_calc_flag = 'N'
            ,A.process_attr_values_flag = 'D'
            ,A.matching_sql_flag = 'Y'
            ,A.hier_processing_flag = 'D'
        WHEN NOT MATCHED THEN
          INSERT (
             A.CHANGED_TERRITORY_ID
            ,A.OBJECT_VERSION_NUMBER
            ,A.TERR_ID
            ,A.SOURCE_ID
            ,A.CHANGE_TYPE
            ,A.RANK_CALC_FLAG
            ,A.PROCESS_ATTR_VALUES_FLAG
            ,A.MATCHING_SQL_FLAG
            ,A.HIER_PROCESSING_FLAG)
          VALUES (
             jty_changed_terrs_s.nextval
            ,0
            ,S.terr_id
            ,S.source_id
            ,'UPDATE'
            ,'N'
            ,'D'
            ,'Y'
            ,'D');

Line: 254

  ELSIF (Trigger_Mode = 'ON-DELETE') THEN

    MERGE INTO jty_changed_terrs A
    USING ( SELECT p_terr_id terr_id from dual ) S
    ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
    WHEN MATCHED THEN
      UPDATE SET
         A.rank_calc_flag = 'N'
        ,A.process_attr_values_flag = 'D'
        ,A.matching_sql_flag = decode(o_named_acct_flag, 'Y', 'N', 'Y')
        ,A.hier_processing_flag = 'D'
        ,A.change_type = 'DELETE'
    WHEN NOT MATCHED THEN
      INSERT (
         A.CHANGED_TERRITORY_ID
        ,A.OBJECT_VERSION_NUMBER
        ,A.TERR_ID
        ,A.CHANGE_TYPE
        ,A.RANK_CALC_FLAG
        ,A.PROCESS_ATTR_VALUES_FLAG
        ,A.MATCHING_SQL_FLAG
        ,A.HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,S.terr_id
        ,'DELETE'
        ,'N'
        ,'D'
        ,decode(o_named_acct_flag, 'Y', 'N', 'Y')
        ,'D');

Line: 285

  END IF; /* end IF (Trigger_Mode = 'ON-UPDATE') */

Line: 306

    SELECT terr_id
    INTO   l_terr_id
    FROM   jtf_terr_qual
    WHERE  terr_qual_id = p_terr_qual_id;

Line: 322

    SELECT source_id
    INTO   l_source_id
    FROM   jtf_terr_usgs_all
    WHERE  terr_id = l_terr_id;

Line: 332

    SELECT change_type,
           process_attr_values_flag
    INTO   l_change_type,
           l_process_attr_values_flag
    FROM   jty_changed_terrs
    WHERE  terr_id = l_terr_id
    AND    star_request_id IS NULL;

Line: 340

    IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
      UPDATE jty_changed_terrs
      SET    process_attr_values_flag = 'I'
      WHERE  terr_id = l_terr_id
      AND    star_request_id IS NULL;

Line: 348

      INSERT INTO jty_changed_terrs (
         CHANGED_TERRITORY_ID
        ,OBJECT_VERSION_NUMBER
        ,TERR_ID
        ,SOURCE_ID
        ,CHANGE_TYPE
        ,RANK_CALC_FLAG
        ,PROCESS_ATTR_VALUES_FLAG
        ,MATCHING_SQL_FLAG
        ,HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,l_terr_id
        ,l_source_id
        ,'UPDATE'
        ,'N'
        ,'I'
        ,'N'
        ,'N');

Line: 393

    SELECT source_id
    INTO   l_source_id
    FROM   jtf_terr_usgs_all
    WHERE  terr_id = p_terr_id;

Line: 402

  SELECT count(*)
  INTO   l_no_of_records
  FROM   jty_changed_terrs
  WHERE  terr_id = p_terr_id
  AND    tap_request_id IS NULL;

Line: 409

    INSERT INTO jty_changed_terrs (
       CHANGED_TERRITORY_ID
      ,OBJECT_VERSION_NUMBER
      ,TERR_ID
      ,SOURCE_ID
      ,CHANGE_TYPE
      ,RANK_CALC_FLAG
      ,PROCESS_ATTR_VALUES_FLAG
      ,MATCHING_SQL_FLAG
      ,HIER_PROCESSING_FLAG)
    VALUES (
       jty_changed_terrs_s.nextval
      ,0
      ,p_terr_id
      ,l_source_id
      ,'UPDATE'
      ,'N'
      ,'N'
      ,'N'
      ,'N');

Line: 451

    SELECT source_id
    INTO   l_source_id
    FROM   jtf_terr_usgs_all
    WHERE  terr_id = p_terr_id;

Line: 461

    SELECT change_type,
           process_attr_values_flag
    INTO   l_change_type,
           l_process_attr_values_flag
    FROM   jty_changed_terrs
    WHERE  terr_id = p_terr_id
    AND    star_request_id IS NULL;

Line: 469

    IF ((l_change_type = 'UPDATE') AND (l_process_attr_values_flag <> 'I')) THEN
      UPDATE jty_changed_terrs
      SET    process_attr_values_flag = 'I',
             matching_sql_flag = 'Y'
      WHERE  terr_id = p_terr_id
      AND    star_request_id IS NULL;

Line: 478

      INSERT INTO jty_changed_terrs (
         CHANGED_TERRITORY_ID
        ,OBJECT_VERSION_NUMBER
        ,TERR_ID
        ,SOURCE_ID
        ,CHANGE_TYPE
        ,RANK_CALC_FLAG
        ,PROCESS_ATTR_VALUES_FLAG
        ,MATCHING_SQL_FLAG
        ,HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,p_terr_id
        ,l_source_id
        ,'UPDATE'
        ,'N'
        ,'I'
        ,'Y'
        ,'N');

Line: 524

    Select terr_id
    into   l_terr_id
    from   jtf_terr_rsc_all
    where  terr_rsc_id = p_terr_rsc_id;

Line: 535

    SELECT source_id
    INTO   l_source_id
    FROM   jtf_terr_usgs_all
    WHERE  terr_id = l_terr_id;

Line: 544

  SELECT count(*)
  INTO   l_no_of_records
  FROM   jty_changed_terrs
  WHERE  terr_id = l_terr_id
  AND    tap_request_id IS NULL;

Line: 551

    INSERT INTO jty_changed_terrs (
       CHANGED_TERRITORY_ID
      ,OBJECT_VERSION_NUMBER
      ,TERR_ID
      ,SOURCE_ID
      ,CHANGE_TYPE
      ,RANK_CALC_FLAG
      ,PROCESS_ATTR_VALUES_FLAG
      ,MATCHING_SQL_FLAG
      ,HIER_PROCESSING_FLAG)
    VALUES (
       jty_changed_terrs_s.nextval
      ,0
      ,l_terr_id
      ,l_source_id
      ,'UPDATE'
      ,'N'
      ,'N'
      ,'N'
      ,'N');

Line: 589

    SELECT source_id
    INTO   l_source_id
    FROM   jtf_terr_usgs_all
    WHERE  terr_id = p_terr_id;

Line: 599

    SELECT change_type,
           matching_sql_flag
    INTO   l_change_type,
           l_matching_sql_flag
    FROM   jty_changed_terrs
    WHERE  terr_id = p_terr_id
    AND    star_request_id IS NULL;

Line: 607

    IF ((l_change_type = 'UPDATE') AND (l_matching_sql_flag <> 'Y')) THEN
      UPDATE jty_changed_terrs
      SET    matching_sql_flag = 'Y'
      WHERE  terr_id = p_terr_id
      AND    star_request_id IS NULL;

Line: 615

      INSERT INTO jty_changed_terrs (
         CHANGED_TERRITORY_ID
        ,OBJECT_VERSION_NUMBER
        ,TERR_ID
        ,SOURCE_ID
        ,CHANGE_TYPE
        ,RANK_CALC_FLAG
        ,PROCESS_ATTR_VALUES_FLAG
        ,MATCHING_SQL_FLAG
        ,HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,p_terr_id
        ,l_source_id
        ,'UPDATE'
        ,'N'
        ,'N'
        ,'Y'
        ,'N');

Line: 655

  IF (triggering_mode = 'ON-INSERT') THEN
    MERGE INTO jty_changed_terrs A
    USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
    ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
    WHEN MATCHED THEN
      UPDATE SET
         A.source_id = S.source_id
    WHEN NOT MATCHED THEN
      INSERT (
         A.CHANGED_TERRITORY_ID
        ,A.OBJECT_VERSION_NUMBER
        ,A.TERR_ID
        ,A.SOURCE_ID
        ,A.CHANGE_TYPE
        ,A.RANK_CALC_FLAG
        ,A.PROCESS_ATTR_VALUES_FLAG
        ,A.MATCHING_SQL_FLAG
        ,A.HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,S.terr_id
        ,S.source_id
        ,'CREATE'
        ,'Y'
        ,'I'
        ,'Y'
        ,'I');

Line: 683

  ELSIF (triggering_mode = 'ON-DELETE') THEN
    MERGE INTO jty_changed_terrs A
    USING ( SELECT p_terr_id terr_id, p_source_id source_id from dual ) S
    ON    ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
    WHEN MATCHED THEN
      UPDATE SET
         A.source_id = S.source_id
    WHEN NOT MATCHED THEN
      INSERT (
         A.CHANGED_TERRITORY_ID
        ,A.OBJECT_VERSION_NUMBER
        ,A.TERR_ID
        ,A.SOURCE_ID
        ,A.CHANGE_TYPE
        ,A.RANK_CALC_FLAG
        ,A.PROCESS_ATTR_VALUES_FLAG
        ,A.MATCHING_SQL_FLAG
        ,A.HIER_PROCESSING_FLAG)
      VALUES (
         jty_changed_terrs_s.nextval
        ,0
        ,S.terr_id
        ,S.source_id
        ,'DELETE'
        ,'N'
        ,'D'
        ,'Y'
        ,'D');