APPS.ARH_DQM_CUST_HELPER SQL Statements

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

Line: 12

  SELECT 'Y'
    FROM hz_cust_account_roles  car,
         hz_cust_accounts       ca,
         hz_matched_parties_gt  mp
   WHERE car.cust_account_role_id = p_cust_account_role_id
     AND car.cust_account_id      = ca.cust_account_id
     AND DECODE(p_status,'ALL','ALL',NVL(car.status,'A'))= p_status
     AND ca.party_id              = mp.party_id
     AND mp.search_context_id     = p_ctx_id;

Line: 42

  SELECT 'Y'
    FROM hz_matched_parties_gt  a,
         hz_cust_accounts       b
   WHERE a.search_context_id = p_ctx_id
     AND -a.party_id         = p_cust_account_id
     AND b.cust_account_id   = p_cust_account_id
     AND DECODE(p_status, 'ALL', 'ALL', NVL(b.status,'A')) = p_status
     AND a.score             < 0;

Line: 73

  SELECT 'Y'
    FROM hz_matched_contacts_gt a,
         hz_cust_account_roles  b
   WHERE a.search_context_id    =  p_ctx_id
     AND -a.party_id            =  p_cust_account_id
     AND -a.org_contact_id      =  p_cust_account_role_id
     AND b.cust_account_role_id =  p_cust_account_role_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
     AND a.score               < 0;

Line: 106

  SELECT 'Y'
    FROM hz_matched_party_sites_gt a,
         hz_cust_acct_sites        b
   WHERE a.search_context_id     = p_ctx_id
     AND -a.party_id          = p_cust_account_id
     AND -a.party_site_id     = p_cust_acct_site_id
     AND b.cust_account_id   = p_cust_account_id
     AND b.cust_acct_site_id = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
     AND a.score < 0;

Line: 118

  SELECT 'Y'
    FROM hz_matched_party_sites_gt a,
         hz_cust_acct_sites_all    b
   WHERE a.search_context_id     = p_ctx_id
     AND -a.party_id          = p_cust_account_id
     AND -a.party_site_id     = p_cust_acct_site_id
     AND b.cust_account_id   = p_cust_account_id
     AND b.cust_acct_site_id = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
     AND a.score < 0;

Line: 161

  SELECT 'Y'
    FROM hz_cust_acct_sites         a,
         hz_party_sites             c,
         hz_matched_parties_gt      d,
         hz_parties                 e
   WHERE a.cust_account_id                     = p_cust_account_id
     AND a.cust_acct_site_id                   = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
     AND a.party_site_id                       = c.party_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status
     AND c.party_id                            = d.party_id
     AND NVL(d.score,0)                        >= 0
     AND d.search_context_id                   = p_ctx_id
     AND d.party_id                            = e.party_id
     AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status;

Line: 178

  SELECT 'Y'
    FROM hz_cust_acct_sites_all     a,
         hz_party_sites             c,
         hz_matched_parties_gt      d,
         hz_parties                 e
   WHERE a.cust_account_id                     = p_cust_account_id
     AND a.cust_acct_site_id                   = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
     AND a.party_site_id                       = c.party_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status
     AND c.party_id                            = d.party_id
     AND NVL(d.score,0)                        >= 0
     AND d.search_context_id                   = p_ctx_id
     AND d.party_id                            = e.party_id
     AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status;

Line: 226

  SELECT NVL(b.score,0)
    FROM hz_cust_acct_sites        a,
         hz_matched_party_sites_gt b,
         hz_party_sites            c
   WHERE a.cust_acct_site_id                   = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
     AND a.party_site_id                       = b.party_site_id
     AND b.search_context_id                   = p_ctx_id
     AND NVL(b.score,0)                        >= 0
     AND b.party_site_id                       = c.party_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status;

Line: 239

  SELECT NVL(b.score,0)
    FROM hz_cust_acct_sites_all    a,
         hz_matched_party_sites_gt b,
         hz_party_sites            c
   WHERE a.cust_acct_site_id                   = p_cust_acct_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
     AND a.party_site_id                       = b.party_site_id
     AND b.search_context_id                   = p_ctx_id
     AND NVL(b.score,0)                        >= 0
     AND b.party_site_id                       = c.party_site_id
     AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status;

Line: 312

       INSERT INTO hz_matched_party_sites_gt
       ( PARTY_ID         , PARTY_SITE_ID          ,SCORE        , SEARCH_CONTEXT_ID ) VALUES
       ( -p_cust_account_id, -p_cust_acct_site_id    ,l_ps_score   , p_ctx_id          );

Line: 329

  SELECT party_id,
         org_contact_id,
         score
    FROM hz_matched_contacts_gt
   WHERE search_context_id =  p_ctx_id
     AND score             >= 0;

Line: 357

  SELECT 'Y'
    FROM hz_matched_party_sites_gt a,
         hz_cust_acct_sites        b
   WHERE a.search_context_id = p_ctx_id
     AND NVL(a.score,0)      >= 0
     AND a.party_site_id     = b.party_site_id
     AND b.cust_acct_site_id = p_acct_site_id
     AND DECODE(p_status, 'ALL','ALL', NVL(b.status,'A')) = p_status;

Line: 367

  SELECT 'Y'
    FROM hz_matched_party_sites_gt a,
         hz_cust_acct_sites_all    b
   WHERE a.search_context_id = p_ctx_id
     AND NVL(a.score,0)      >= 0
     AND a.party_site_id     = b.party_site_id
     AND b.cust_acct_site_id = p_acct_site_id
     AND DECODE(p_status, 'ALL','ALL', NVL(b.status,'A')) = p_status;

Line: 406

  SELECT a.score                ct_score,
         f.score                pty_score,
         d.cust_account_role_id,
         d.cust_account_id,
         d.cust_acct_site_id
    FROM hz_matched_contacts_gt a,
         hz_org_contacts        b,
         hz_relationships       c,
         hz_cust_account_roles  d,
         hz_cust_accounts       e,
         hz_matched_parties_gt  f,
         hz_parties             g
   WHERE a.search_context_id                   = p_ctx_id
     AND a.org_contact_id                      = p_org_contact_id
     AND NVL(a.score,0)                        >= 0
     AND a.org_contact_id                      = b.org_contact_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
     AND b.party_relationship_id               = c.relationship_id
     AND c.directional_flag                    = 'F'
     AND c.party_id                            = d.party_id
     AND DECODE(p_status,'ALL','ALL',NVL(d.status,'A')) = p_status
     AND d.cust_account_id                     = e.cust_account_id
     AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status
     AND e.party_id                            = f.party_id
     AND f.search_context_id                   = p_ctx_id
     AND NVL(f.score,0)                        >= 0
     AND f.party_id                            = a.party_id
     AND f.party_id                            = g.party_id
     AND DECODE(p_status,'ALL','ALL', NVL(g.status,'A'))= p_status;

Line: 460

        INSERT INTO hz_matched_contacts_gt
        ( PARTY_ID            , ORG_CONTACT_ID           ,  SCORE    , SEARCH_CONTEXT_ID) VALUES
        ( -lrec.cust_account_id, -lrec.cust_account_role_id,  l_score  , p_ctx_id         );

Line: 476

          INSERT INTO hz_matched_parties_gt
          ( PARTY_ID            , SCORE       , SEARCH_CONTEXT_ID  ) VALUES
          ( -lrec.cust_account_id, l_score     , p_ctx_id           );

Line: 496

          INSERT INTO hz_matched_contacts_gt
          ( PARTY_ID            , ORG_CONTACT_ID           ,  SCORE    , SEARCH_CONTEXT_ID) VALUES
          ( -lrec.cust_account_id, -lrec.cust_account_role_id,  l_score  , p_ctx_id         );

Line: 522

            INSERT INTO hz_matched_parties_gt
            ( PARTY_ID            , SCORE       , SEARCH_CONTEXT_ID  ) VALUES
            ( -lrec.cust_account_id, l_score     , p_ctx_id           );

Line: 544

  SELECT NVL(score,0)
    FROM hz_cust_accounts      a,
         hz_matched_parties_gt b
   WHERE a.cust_account_id   = p_cust_account_id
     AND b.search_context_id = p_ctx_id
     AND a.party_id          = b.party_id
     AND NVL(b.score,0)      >= 0
     AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status;

Line: 584

     SELECT cust_acct_site_id,
            cust_account_id
       FROM hz_cust_acct_sites
      WHERE party_site_id = p_party_site_id
        AND DECODE(p_status,'ALL','ALL',NVL(status,'A')) = p_status;

Line: 591

     SELECT cust_acct_site_id,
            cust_account_id
       FROM hz_cust_acct_sites_all
      WHERE party_site_id = p_party_site_id
        AND DECODE(p_status,'ALL','ALL',NVL(status,'A')) = p_status;

Line: 615

      INSERT INTO hz_matched_party_sites_gt
      ( PARTY_ID            , PARTY_SITE_ID          ,SCORE        , SEARCH_CONTEXT_ID ) VALUES
      ( -l_cust_account_id   , -l_cust_acct_site_id    ,l_score      , p_ctx_id          );

Line: 635

        INSERT INTO hz_matched_parties_gt
        ( PARTY_ID            , SCORE       , SEARCH_CONTEXT_ID  ) VALUES
        ( -l_cust_account_id   , l_score     , p_ctx_id           );

Line: 658

  SELECT 'Y'
    FROM hz_cust_acct_sites        b,
         hz_matched_party_sites_gt c
   WHERE b.party_site_id     = p_party_site_id
     AND b.cust_acct_site_id = -c.party_site_id
     AND c.search_context_id = p_ctx_id
     AND c.score              <  0
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;

Line: 668

  SELECT 'Y'
    FROM hz_cust_acct_sites_all    b,
         hz_matched_party_sites_gt c
   WHERE b.party_site_id     = p_party_site_id
     AND b.cust_acct_site_id = -c.party_site_id
     AND c.search_context_id = p_ctx_id
     AND c.score              <  0
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;

Line: 704

  SELECT 'Y'
    FROM hz_matched_contacts_gt a,
         hz_org_contacts        b
   WHERE a.search_context_id                           = p_ctx_id
     AND NVL(a.score,0)                                >= 0
     AND a.org_contact_id                              = b.org_contact_id
     AND b.party_site_id                               = p_ps_id
     AND DECODE(p_status,'ALL','ALL',NVL(p_status,'A'))= p_status;

Line: 730

  SELECT 'Y'
    FROM hz_matched_cpts_gt a,
         hz_contact_points b
   WHERE a.search_context_id =  p_ctx_id
     AND a.contact_point_id  =  b.contact_point_id
     AND b.owner_table_name  =  'HZ_PARTY_SITES'
     AND b.owner_table_id    =  p_ps_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;

Line: 758

  SELECT a.party_site_id,
         NVL(a.score,0) score
    FROM hz_matched_party_sites_gt a
   WHERE a.search_context_id  = p_ctx_id
     AND NVL(a.score,0)       >= 0;

Line: 773

insert into hyu_matched_party_sites_gt
(PARTY_ID                       ,
 PARTY_SITE_ID                  ,
 SCORE                          ,
 SEARCH_CONTEXT_ID              ) values
('9999',
 lrec.party_site_id,
 lrec.score,
 p_ctx_id);

Line: 788

insert into hyu_err
(COL1,
 COL2,
 COLN) values
(lrec.party_site_id,
 lyn||' - '||p_cur_all||' - '||p_status,
 p_ctx_id);

Line: 839

  SELECT 'Y'
    FROM hz_matched_party_sites_gt
   WHERE search_context_id = p_ctx_id
     AND NVL(score,0)      >= 0
     AND party_id          = p_party_id;

Line: 846

  SELECT 'Y'
    FROM hz_matched_contacts_gt
   WHERE search_context_id = p_ctx_id
     AND NVL(score,0) >= 0
     AND party_id      = p_party_id;

Line: 853

  SELECT 'Y'
    FROM hz_matched_cpts_gt
   WHERE search_context_id = p_ctx_id
     AND NVL(score,0)  >= 0
     AND party_id       = p_party_id;

Line: 890

  SELECT 'Y'
    FROM hz_matched_cpts_gt a,
         hz_contact_points  b
   WHERE a.search_context_id =  p_ctx_id
     AND a.contact_point_id  =  b.contact_point_id
     AND b.owner_table_name  =  'HZ_PARTIES'
     AND b.owner_table_id    =  p_pty_id
     AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;

Line: 918

  SELECT party_id,
         NVL(score,0) score
    FROM hz_matched_parties_gt
   WHERE NVL(score,0) >= 0
     AND search_context_id = p_ctx_id;

Line: 973

 SELECT 'Y'
   FROM hz_cust_acct_sites
  WHERE cust_account_id = p_cust_account_id
    AND DECODE(p_status, 'ALL','ALL',NVL(status,'A')) = p_status;

Line: 999

  SELECT cust_account_id
    FROM hz_cust_accounts
   WHERE party_id  = p_party_id
     AND DECODE(p_status, 'ALL','ALL',NVL(status,'A')) = p_status;

Line: 1016

      INSERT INTO hz_matched_parties_gt
      ( PARTY_ID            , SCORE       , SEARCH_CONTEXT_ID  ) VALUES
      ( -lrec.cust_account_id, l_score     , p_ctx_id           );

Line: 1021

        INSERT INTO hz_matched_parties_gt
        ( PARTY_ID            , SCORE       , SEARCH_CONTEXT_ID  ) VALUES
        ( -lrec.cust_account_id, l_score     , p_ctx_id           );

Line: 1042

  SELECT 'Y'
    FROM hz_cust_accounts     a,
         hz_matched_parties_gt b
   WHERE a.party_id          = p_party_id
     AND a.cust_account_id   = -b.party_id
     AND b.search_context_id = p_ctx_id
     AND b.score             < 0
     AND DECODE(p_status, 'ALL','ALL',NVL(a.status,'A')) = p_status;