APPS.OKI_LOAD_TERR_PVT SQL Statements

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

Line: 90

   It either truncates/deletes unwanted old records from
   either staging table or Fact table or Worker Status table.
 */
PROCEDURE reset_base_tables
IS
  l_tab_name         VARCHAR2(100);

Line: 112

  DELETE FROM OKI_DBI_WORKER_STATUS
   WHERE OBJECT_NAME = 'OKI_JTF_TERRITORIES';

Line: 117

      BIS_COLLECTION_UTILITIES.DeleteLogForObject('OKIJTFTERR');

Line: 239

         UPDATE OKI_JTF_TERRITORIES_STG
         SET worker_number = worker_no
         WHERE worker_number IS NULL
         AND ROWNUM <= CEIL(l_terr_count/l_no_of_workers);

Line: 245

            INSERT INTO OKI_DBI_WORKER_STATUS (
               object_name
             , worker_number
             , status
             , c_rows
              )
            VALUES(
              'OKI_JTF_TERRITORIES'
             , worker_no
             ,'UNASSIGNED'
             , -1
             );

Line: 278

         SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
                NVL(sum(decode(status,'COMPLETED',1,0)),0),
                NVL(sum(decode(status,'IN PROCESS',1,0)),0),
                NVL(sum(decode(status,'FAILED',1,0)),0),
                count(*)
         INTO   l_unassigned_cnt,
                l_completed_cnt,
                l_wip_cnt,
                l_failed_cnt,
                l_tot_cnt
         FROM   OKI_DBI_WORKER_STATUS
         WHERE object_name = 'OKI_JTF_TERRITORIES';

Line: 342

     INSERT /* + APPEND */  INTO OKI_JTF_TERRITORIES_STG
        (   authoring_org_id
          , party_id
          , party_name
          , country_code
          , state_code
          , record_id
        )
      SELECT
            authoring_org_id
          , party_id
          , party_name
          , country_code
          , state_code
          , ROWNUM
      FROM
      (
       SELECT /* + parallel(b) parallel(sts) parallel(c) parallel(p) parallel(v)
                  parallel(h) parallel(ro) use_hash(b,c,p,v,h,ro,sts) */
              DISTINCT
              h.authoring_org_id,
              p.party_id,
              p.party_Name,
              c.country country_code,
              c.region_2 state_code
         FROM hr_all_organization_units b
            , hr_locations c
            , hz_parties p
            , okc_k_headers_all_b h
            , okc_k_vers_numbers v
            , okc_statuses_b sts
            , okc_k_party_roles_b ro
         WHERE h.authoring_org_id = b.organization_id
           AND b.location_id = c.location_id
           AND p.party_id = ro.object1_id1
           AND (   sts.ste_code = 'ACTIVE'
                 OR (sts.ste_code = 'EXPIRED' AND h.end_date >= g_sysdate - 120)
                 OR (sts.ste_code = 'ENTERED' AND h.start_date >= g_sysdate - 365)
               )
           AND v.last_update_date   >= g_start_date
           AND v.last_update_date+0 <= g_end_date
           AND h.id                = v.chr_id
           AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
           AND h.template_yn       = 'N'
           AND h.application_id    = 515
           AND h.buy_or_sell       ='S'
           AND h.scs_code IN ('SERVICE','WARRANTY')
           AND ro.dnz_chr_id      = h.id
           AND ro.cle_id   IS NULL
           AND ro.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
           AND NVL(ro.primary_yn,'Y') = 'Y'
      );

Line: 395

    	INSERT /* + APPEND */  INTO OKI_JTF_TERRITORIES_STG
        (   authoring_org_id
          , party_id
          , party_name
          , country_code
          , state_code
          , record_id
        )
      SELECT
            authoring_org_id
          , party_id
          , party_name
          , country_code
          , state_code
          , ROWNUM
      FROM
      (
       SELECT
              DISTINCT
              h.authoring_org_id,
              p.party_id,
              p.party_Name,
              c.country country_code,
              c.region_2 state_code
         FROM hr_all_organization_units b
            , hr_locations c
            , hz_parties p
            , okc_k_headers_all_b h
            , okc_k_vers_numbers v
            , okc_statuses_b sts
            , okc_k_party_roles_b ro
         WHERE h.authoring_org_id = b.organization_id
           AND b.location_id = c.location_id
           AND p.party_id = ro.object1_id1
           AND (   sts.ste_code = 'ACTIVE'
                 OR (sts.ste_code = 'EXPIRED' AND h.end_date >= g_sysdate - 120)
                 OR (sts.ste_code = 'ENTERED' AND h.start_date >= g_sysdate - 365)
               )
           AND v.last_update_date   >= g_start_date
           AND v.last_update_date+0 <= g_end_date
           AND h.id                = v.chr_id
           AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
           AND h.template_yn       = 'N'
           AND h.application_id    = 515
           AND h.buy_or_sell       ='S'
           AND h.scs_code IN ('SERVICE','WARRANTY')
           AND ro.dnz_chr_id      = h.id
           AND ro.cle_id   IS NULL
           AND ro.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
           AND NVL(ro.primary_yn,'Y') = 'Y'
      );

Line: 449

  rlog('No of records inserted into Staging table - ' || p_terr_count,1);

Line: 510

    SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
           NVL(sum(decode(status,'FAILED', 1, 0)),0),
           NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
           NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
           count(*)
    INTO   l_unassigned_cnt,
           l_failed_cnt,
           l_wip_cnt,
           l_completed_cnt,
           l_total_cnt
    FROM   OKI_DBI_WORKER_STATUS
    WHERE 1=1
    AND object_name = 'OKI_JTF_TERRITORIES';

Line: 533

      UPDATE OKI_DBI_WORKER_STATUS
      SET status = 'IN PROCESS'
      WHERE object_name = 'OKI_JTF_TERRITORIES'
      AND worker_number = p_worker_no
      AND STATUS ='UNASSIGNED';

Line: 544

        UPDATE OKI_DBI_WORKER_STATUS
        SET    status = 'COMPLETED'
             , c_rows = l_count
        WHERE  object_name = 'OKI_JTF_TERRITORIES'
        AND    status = 'IN PROCESS'
        AND    worker_number = p_worker_no;

Line: 623

  rlog('Launching Sub-Workers to load/update OKI_JTF_TERRITORIES table '  || fnd_date.date_to_displayDT(SYSDATE),1);

Line: 636

    SELECT NVL(SUM(c_rows),0) INTO l_count
      FROM OKI_DBI_WORKER_STATUS
     WHERE object_name ='OKI_JTF_TERRITORIES';

Line: 640

     rlog('No of records inserted/updated in OKI_JTF_TERRITORIES table - ' || l_count , 1);

Line: 754

   x_rec_count        : OUT parameter, gives no of records updated in
                        territory fact table by this worker */
PROCEDURE load_jtf_terr ( p_worker_number IN NUMBER
                        , x_rec_count     OUT NOCOPY NUMBER) IS

  l_errpos             NUMBER;

Line: 775

  l_no_update_refresh   EXCEPTION;

Line: 781

   select
        authoring_org_id
      , party_id
      , party_name
      , country_code
      , state_code
      , record_id
   from OKI_JTF_TERRITORIES_STG t
   where worker_number = l_worker_number
;

Line: 866

         UPDATE OKI_JTF_TERRITORIES_STG
         SET terr_id            = l_gen_return_rec.terr_id(i),
             resource_id        = l_gen_return_rec.resource_id(i)
         WHERE authoring_org_id = l_gen_return_rec.trans_detail_object_id(i)
         AND record_id          = l_gen_return_rec.trans_object_id(i);

Line: 885

         INSERT INTO oki_jtf_territories
            (
              authoring_org_id
            , customer_party_id
            , terr_id
            , resource_id
            , creation_date
            , created_by
            , last_update_date
            , last_updated_by
            , last_update_login
            )
         SELECT
              authoring_org_id
            , party_id
            , terr_id
            , resource_id
            , l_sysdate
            , g_user_id
            , l_sysdate
            , g_user_id
            , g_login_id
         FROM OKI_JTF_TERRITORIES_STG
         WHERE worker_number = p_worker_number
           AND terr_id IS NOT NULL;

Line: 915

           ( SELECT   authoring_org_id
                    , party_id
                    , terr_id
                    , resource_id
              FROM OKI_JTF_TERRITORIES_STG
              WHERE worker_number = p_worker_number
              AND terr_id IS NOT NULL
           ) s
           ON
           (   b.authoring_org_id  = s.authoring_org_id
           AND b.customer_party_id = s.party_id
           )
           WHEN MATCHED THEN UPDATE SET
           	 terr_id             =   s.terr_id
           , resource_id         =   s.resource_id
           , last_update_date    =   l_sysdate
           , last_updated_by     =   g_user_id
           , last_update_login   =   g_user_id
           WHEN NOT MATCHED THEN
           INSERT
           (
             authoring_org_id
           , customer_party_id
           , terr_id
           , resource_id
           , creation_date
           , created_by
           , last_update_date
           , last_updated_by
           , last_update_login
           )
           VALUES
           (
             s.authoring_org_id
           , s.party_id
           , s.terr_id
           , s.resource_id
           , l_sysdate
           , g_user_id
           , l_sysdate
           , g_user_id
           , g_login_id
           );

Line: 963

        	RAISE l_no_update_refresh;

Line: 965

        rlog('No of records inserted/updated in OKI_JTF_TERRITORIES table using subworker '
            || p_worker_number ||' - ' || x_rec_count,2);

Line: 977

  UPDATE OKI_DBI_WORKER_STATUS
       SET    status = 'FAILED'
       WHERE  object_name = 'OKI_JTF_TERRITORIES'
       AND    status = 'IN PROCESS'
       AND    worker_number = p_worker_number;