APPS.ARP_PAY_REL_CUST_PKG SQL Statements

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

Line: 52

   SELECT distinct ca.class_code  class
   FROM   hz_code_assignments ca
         ,hz_relationship_types rt
   WHERE ca.class_category     = 'RELATIONSHIP_TYPE_GROUP'
     AND ca.owner_table_name   = 'HZ_RELATIONSHIP_TYPES'
     AND ca.status             = 'A'
     AND ca.owner_table_id     = rt.relationship_type_id
     AND rt.object_type        = 'ORGANIZATION'
     AND rt.subject_type       = 'ORGANIZATION'
     AND ca.class_code like '%AR_PAY%';

Line: 67

   l_rows_inserted  NUMBER;

Line: 107

          /* l_ins_sql is the full insert with all of the UNION clauses
             appended */
          l_ins_sql :=
         'INSERT INTO ar_potential_customers_gt' || CRLF ||
         '  (cust_account_id, related_cust_account_id)' || CRLF ||
         'SELECT distinct customer_id, customer_id' || CRLF ||
         'FROM   ar_cash_remit_refs_interim' || CRLF ||
         'WHERE  customer_id IS NOT NULL' || CRLF ||
         'AND    worker_number = :worker_number1' || CRLF ||
         'UNION ' || CRLF ||
         'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
         'FROM   ar_cash_remit_refs_interim int, ' || CRLF ||
         '       hz_cust_acct_relate_all rel ' || CRLF ||
         'WHERE  int.customer_id         = rel.cust_account_id ' || CRLF ||
         'AND    rel.bill_to_flag        = ''Y'' ' || CRLF ||
         'AND    rel.status              = ''A'' ' || CRLF ||
         'AND    int.customer_id IS NOT NULL ' || CRLF ||
         'AND    int.worker_number = :worker_number2' || CRLF;

Line: 130

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
         'FROM   ar_cash_remit_refs_interim int,' || CRLF ||
         '       ar_cash_receipts cr, ' || CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes gettop,' || CRLF ||
         '       hz_hierarchy_nodes top,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  int.cash_receipt_id = cr.cash_receipt_id ' || CRLF ||
         'AND  cr.pay_from_customer = acc.cust_account_id ' || CRLF ||
         'AND  acc.party_id                  = gettop.child_id  ' || CRLF ||
         'AND  cr.receipt_date           >= hd.effective_start_date' || CRLF ||
         'AND  cr.receipt_date           >= top.effective_start_date' || CRLF ||
         'AND  cr.receipt_date           >= gettop.effective_start_date' || CRLF ||
         'AND  cr.receipt_date           <= hn.effective_end_date' || CRLF ||
         'AND  cr.receipt_date           <= top.effective_end_date' || CRLF ||
         'AND  cr.receipt_date           <= gettop.effective_end_date' || CRLF ||
         'AND  int.customer_id IS NOT NULL' || CRLF ||
         'AND  int.worker_number         = :worker_number3' || CRLF ||
         'AND  top.hierarchy_type        = gettop.hierarchy_type' || CRLF ||
         'AND  top.parent_id             = gettop.parent_id' || CRLF ||
         'AND  gettop.parent_table_name  = ''HZ_PARTIES''' || CRLF ||
         'AND  gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
         'AND  gettop.child_table_name   = ''HZ_PARTIES'''  || CRLF ||
         'AND  gettop.child_object_type  = ''ORGANIZATION''' || CRLF ||
         'AND  top.parent_table_name     = ''HZ_PARTIES''' || CRLF ||
         'AND  top.parent_object_type    = ''ORGANIZATION''' || CRLF ||
         'AND  top.top_parent_flag       = ''Y''' || CRLF ||
         'AND  hn.hierarchy_type         = top.hierarchy_type' || CRLF ||
         'AND  hn.parent_id              = top.parent_id' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type         = ''ORGANIZATION'')' || CRLF;

Line: 185

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
         'FROM   ar_cash_remit_refs_interim int,' || CRLF ||
         '       ar_cash_receipts cr,' || CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  int.cash_receipt_id = cr.cash_receipt_id' || CRLF ||
         'AND  cr.pay_from_customer          = acc.cust_account_id ' || CRLF ||
         'AND  acc.party_id                  = hn.parent_id  ' || CRLF ||
         'AND  cr.receipt_date BETWEEN hn.effective_start_date AND ' || CRLF ||
         '                              hn.effective_end_date' || CRLF ||
         'AND  int.customer_id IS NOT NULL' || CRLF ||
         'AND  int.worker_number         = :worker_number4' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type       = ''ORGANIZATION'')' || CRLF;

Line: 236

         l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);

Line: 242

      arp_debug.debug('  rows inserted: ' || l_rows_inserted);

Line: 285

 l_rows_inserted  NUMBER;

Line: 291

   SELECT distinct ca.class_code  class
   FROM   hz_code_assignments ca
         ,hz_relationship_types rt
   WHERE ca.class_category     = 'RELATIONSHIP_TYPE_GROUP'
     AND ca.owner_table_name   = 'HZ_RELATIONSHIP_TYPES'
     AND ca.status             = 'A'
     AND ca.owner_table_id     = rt.relationship_type_id
     AND rt.object_type        = 'ORGANIZATION'
     AND rt.subject_type       = 'ORGANIZATION'
     AND ca.class_code like '%AR_PAY%';

Line: 347

          /* l_ins_sql is the full insert with all of the UNION clauses
             appended */
          l_ins_sql :=
         'INSERT INTO ar_potential_customers_gt' || CRLF ||
         '  (cust_account_id, related_cust_account_id)' || CRLF ||
         'SELECT distinct customer_id, customer_id' || CRLF ||
         'FROM   ar_payments_interface' || CRLF ||
         'WHERE  customer_id IS NOT NULL' || CRLF ||
         'AND    transmission_id = :transmission_id1' || CRLF ||
         'AND    record_type = :pay_rec_type1' || CRLF ||
         'UNION ' || CRLF ||
         'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
         'FROM   ar_payments_interface int, ' || CRLF ||
         '       hz_cust_acct_relate_all rel ' || CRLF ||
         'WHERE  int.customer_id         = rel.cust_account_id ' || CRLF ||
         'AND    rel.bill_to_flag        = ''Y'' ' || CRLF ||
         'AND    rel.status              = ''A'' ' || CRLF ||
         'AND    int.customer_id IS NOT NULL ' || CRLF ||
         'AND    transmission_id = :transmission_id2' || CRLF ||
         'AND    record_type = :pay_rec_type2' || CRLF;

Line: 372

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
         'FROM   ar_payments_interface int,' || CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes gettop,' || CRLF ||
         '       hz_hierarchy_nodes top,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  int.customer_id             = acc.cust_account_id ' || CRLF ||
         'AND  acc.party_id                  = gettop.child_id  ' || CRLF ||
         'AND  int.receipt_date             >= ' || CRLF ||
         '                GREATEST(hn.effective_start_date, ' || CRLF ||
         '                         top.effective_start_date, ' || CRLF ||
         '                         gettop.effective_start_date)' || CRLF ||
         'AND  int.receipt_date             <= ' || CRLF ||
         '                LEAST(hn.effective_end_date, ' || CRLF ||
         '                      top.effective_end_date,' || CRLF ||
         '                      gettop.effective_end_date, ' || CRLF ||
         '                      hn.effective_end_date)' || CRLF ||
         'AND  int.customer_id IS NOT NULL' || CRLF ||
         'AND  int.transmission_id       = :transmission_id3' || CRLF ||
         'AND  int.record_type           = :pay_rec_type3' || CRLF ||
         'AND  top.hierarchy_type        = gettop.hierarchy_type' || CRLF ||
         'AND  top.parent_id             = gettop.parent_id' || CRLF ||
         'AND  gettop.parent_table_name  = ''HZ_PARTIES''' || CRLF ||
         'AND  gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
         'AND  gettop.child_table_name   = ''HZ_PARTIES'''  || CRLF ||
         'AND  gettop.child_object_type  = ''ORGANIZATION''' || CRLF ||
         'AND  top.parent_table_name     = ''HZ_PARTIES''' || CRLF ||
         'AND  top.parent_object_type    = ''ORGANIZATION''' || CRLF ||
         'AND  top.top_parent_flag       = ''Y''' || CRLF ||
         'AND  hn.hierarchy_type         = top.hierarchy_type' || CRLF ||
         'AND  hn.parent_id              = top.parent_id' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type         = ''ORGANIZATION'')' || CRLF;

Line: 429

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
         'FROM   ar_payments_interface int,' || CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  int.customer_id             = acc.cust_account_id ' || CRLF ||
         'AND  acc.party_id                  = hn.parent_id  ' || CRLF ||
         'AND  int.receipt_date BETWEEN hn.effective_start_date AND ' || CRLF ||
         '                              hn.effective_end_date' || CRLF ||
         'AND  int.customer_id IS NOT NULL' || CRLF ||
         'AND  int.transmission_id       = :transmission_id4' || CRLF ||
         'AND  int.record_type           = :pay_rec_type4' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type       = ''ORGANIZATION'')' || CRLF;

Line: 483

         l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);

Line: 493

      arp_debug.debug('  inserted ' || l_rows_inserted || ' customer/relationships. ');

Line: 532

 l_rows_inserted  NUMBER;

Line: 535

   SELECT distinct ca.class_code  class
   FROM   hz_code_assignments ca
         ,hz_relationship_types rt
   WHERE ca.class_category     = 'RELATIONSHIP_TYPE_GROUP'
     AND ca.owner_table_name   = 'HZ_RELATIONSHIP_TYPES'
     AND ca.status             = 'A'
     AND ca.owner_table_id     = rt.relationship_type_id
     AND rt.object_type        = 'ORGANIZATION'
     AND rt.subject_type       = 'ORGANIZATION'
     AND ca.class_code like '%AR_PAY%';

Line: 572

              select 'found'
              into   l_is_cust_there
              from   ar_potential_customers_gt
              where  cust_account_id = p_customer_id;

Line: 606

          /* l_ins_sql is the full insert with all of the UNION
             clauses appended */
          l_ins_sql :=
         'INSERT INTO ar_potential_customers_gt' || CRLF ||
         '  (cust_account_id, related_cust_account_id)' || CRLF ||
         'SELECT :customer_id1, :customer_id2' || CRLF ||
         'FROM   dual' || CRLF ||
         'UNION ' || CRLF ||
         'SELECT DISTINCT rel.cust_account_id, rel.related_cust_account_id ' || CRLF ||
         'FROM   hz_cust_acct_relate_all rel ' || CRLF ||
         'WHERE  rel.cust_account_id = :customer_id3' || CRLF ||
         'AND    rel.bill_to_flag        = ''Y'' ' || CRLF ||
         'AND    rel.status              = ''A'' ' || CRLF;

Line: 624

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id' || CRLF ||
         'FROM   '|| CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes gettop,' || CRLF ||
         '       hz_hierarchy_nodes top,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  acc.cust_account_id         = :customer_id4' || CRLF ||
         'AND  acc.party_id                  = gettop.child_id  ' || CRLF ||
         'AND  :apply_date1        >= hn.effective_start_date ' || CRLF ||
         'AND  :apply_date2        >= top.effective_start_date ' || CRLF ||
         'AND  :apply_date3        >= gettop.effective_start_date ' || CRLF ||
         'AND  :apply_date4        <= top.effective_end_date ' || CRLF ||
         'AND  :apply_date5        <= gettop.effective_end_date ' || CRLF ||
         'AND  :apply_date6        <= hn.effective_end_date ' || CRLF ||
         'AND  top.hierarchy_type        = gettop.hierarchy_type' || CRLF ||
         'AND  top.parent_id             = gettop.parent_id' || CRLF ||
         'AND  gettop.parent_table_name  = ''HZ_PARTIES''' || CRLF ||
         'AND  gettop.parent_object_type = ''ORGANIZATION''' || CRLF ||
         'AND  gettop.child_table_name   = ''HZ_PARTIES'''  || CRLF ||
         'AND  gettop.child_object_type  = ''ORGANIZATION''' || CRLF ||
         'AND  top.parent_table_name     = ''HZ_PARTIES''' || CRLF ||
         'AND  top.parent_object_type    = ''ORGANIZATION''' || CRLF ||
         'AND  top.top_parent_flag       = ''Y''' || CRLF ||
         'AND  hn.hierarchy_type         = top.hierarchy_type' || CRLF ||
         'AND  hn.parent_id              = top.parent_id' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_ANY'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type         = ''ORGANIZATION'')' || CRLF;

Line: 675

         'SELECT /*+ ORDERED */ ' || CRLF ||
         '       DISTINCT acc.cust_account_id, relacc.cust_account_id ' || CRLF ||
         'FROM   ' || CRLF ||
         '       hz_cust_accounts acc,' || CRLF ||
         '       hz_hierarchy_nodes hn, ' || CRLF ||
         '       hz_cust_accounts relacc ' || CRLF ||
         'WHERE  acc.cust_account_id         = :customer_id5' || CRLF ||
         'AND  acc.party_id                  = hn.parent_id  ' || CRLF ||
         'AND  :apply_date7     BETWEEN hn.effective_start_date AND ' || CRLF ||
         '                              hn.effective_end_date' || CRLF ||
         'AND  hn.parent_table_name      = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.parent_object_type     = ''ORGANIZATION''' || CRLF ||
         'AND  hn.child_id               = relacc.party_id' || CRLF ||
         'AND  hn.child_table_name       = ''HZ_PARTIES''' || CRLF ||
         'AND  hn.child_object_type      = ''ORGANIZATION''' || CRLF ||
         'AND  EXISTS ' || CRLF ||
         '  (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1' || CRLF ||
         '   FROM   hz_code_assignments ca' || CRLF ||
         '         ,hz_relationship_types rt ' || CRLF ||
         '   WHERE ca.class_category     = ''RELATIONSHIP_TYPE_GROUP''' || CRLF ||
         '   AND   ca.owner_table_name   = ''HZ_RELATIONSHIP_TYPES''' || CRLF ||
         '   AND   ca.class_code         = ''PARTY_REL_GRP_AR_PAY_TOP_DOWN'''|| CRLF ||
         '   AND   ca.status             = ''A'''|| CRLF ||
         '   AND   ca.owner_table_id     = rt.relationship_type_id' || CRLF ||
         '   AND   hn.hierarchy_type     = rt.relationship_type' || CRLF ||
         '   AND   rt.object_type        = ''ORGANIZATION''' || CRLF ||
         '   AND   rt.subject_type       = ''ORGANIZATION'')' || CRLF;

Line: 730

         l_rows_inserted := DBMS_SQL.EXECUTE(cur_ins);

Line: 740

      arp_debug.debug('  inserted ' || l_rows_inserted || ' customer/relationships. ');

Line: 752

   delete from ar_potential_customers_gt;