APPS.POS_IMP_SUPP_PKG SQL Statements

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

Line: 173

    /* Select the counts for particular batch from all the interface tables */
    SELECT COUNT(int.sdh_batch_id)
    INTO   l_suppliers_in_batch
    FROM   ap_suppliers_int INT
    WHERE  int.sdh_batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 180

    SELECT COUNT(int.sdh_batch_id)
    INTO   l_supp_sites_in_batch
    FROM   ap_supplier_sites_int INT
    WHERE  int.sdh_batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 186

    SELECT COUNT(int.sdh_batch_id)
    INTO   l_sup_contact_in_batch
    FROM   ap_sup_site_contact_int INT
    WHERE  int.sdh_batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 192

    SELECT COUNT(int.sdh_batch_id)
    INTO   l_prodserv_in_batch
    FROM   pos_product_service_int INT
    WHERE  int.sdh_batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 199

    SELECT COUNT(int.sdh_batch_id)
    INTO   l_bus_class_in_batch
    FROM   pos_business_class_int INT
    WHERE  int.sdh_batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 206

    SELECT COUNT(int.batch_id)
    INTO   l_taxdtls1_in_batch
    FROM   pos_party_tax_profile_int INT
    WHERE  int.batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 213

    SELECT COUNT(int.batch_id)
    INTO   l_taxdtls2_in_batch
    FROM   pos_party_tax_reg_int INT
    WHERE  int.batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 220

    SELECT COUNT(int.batch_id)
    INTO   l_taxdtls3_in_batch
    FROM   pos_fiscal_class_int INT
    WHERE  int.batch_id = p_batch_id
    /*AND    int.source_system = p_original_system*/
    ;

Line: 227

    SELECT COUNT(int.batch_id)
    INTO   l_bankdtls1_in_batch
    FROM   pos_bank_account_det_int INT
    WHERE  int.batch_id = p_batch_id;

Line: 232

    SELECT COUNT(int.batch_id)
    INTO   l_bankdtls2_in_batch
    FROM   pos_bank_accnt_owners_int INT
    WHERE  int.batch_id = p_batch_id;

Line: 237

    SELECT COUNT(INT.BATCH_ID)
    INTO   l_uda_in_batch
    FROM   POS_SUPP_PROF_EXT_INTF INT
    WHERE  int.batch_id = p_batch_id
           and NVL(int.PROCESS_STATUS, 1) = 1;

Line: 253

    UPDATE pos_imp_batch_summary
    SET    suppliers_in_batch       = l_suppliers_in_batch,
           sites_in_batch           = l_supp_sites_in_batch,
           contacts_in_batch        = l_sup_contact_in_batch,
           buss_class_in_batch      = l_bus_class_in_batch,
           prod_serv_in_batch       = l_prodserv_in_batch,
           bank_detls_in_batch      = l_bankdtls1_in_batch +
                                      l_bankdtls2_in_batch,
           tax_dtls_in_batch        = l_taxdtls1_in_batch +
                                      l_taxdtls2_in_batch +
                                      l_taxdtls3_in_batch,
           total_batch_records      = l_total_batch_records,
           total_inserts            = 0,
           total_updates            = 0,
           total_merge_requests     = 0,
           total_auto_merged        = 0,
           suppliers_inserted       = 0,
           sites_inserted           = 0,
           contacts_inserted        = 0,
           buss_class_inserted      = 0,
           prod_serv_inserted       = 0,
           bank_detls_inserted      = 0,
           tax_dtls_inserted        = 0,
           suppliers_updated        = 0,
           sites_updated            = 0,
           contacts_updated         = 0,
           buss_class_updated       = 0,
           prod_serv_updated        = 0,
           bank_detls_updated       = 0,
           tax_dtls_updated         = 0,
           suppliers_merge_requests = 0,
           sites_merge_requests     = 0,
           contacts_merge_requests  = 0,
           suppliers_auto_merged    = 0,
           suppliers_imported       = 0,
           sites_imported           = 0,
           contacts_imported        = 0,
           buss_class_imported      = 0,
           prod_serv_imported       = 0,
           bank_detls_imported      = 0,
           tax_dtls_imported        = 0,
           --total_errors           = 0,
           --total_batch_records    = 0,
           total_records_imported = 0
    WHERE  batch_id = p_batch_id;

Line: 341

      SELECT batch_id,
             original_system
      INTO   l_batch_id,
             l_original_system
      FROM   pos_imp_batch_summary
      WHERE  batch_id = p_batch_id;

Line: 364

      SELECT batch_id
      INTO   l_batch_id
      FROM   pos_imp_batch_summary
      WHERE  batch_id = p_batch_id
      AND    nvl(batch_status, 'ACTIVE') NOT IN
             ('PURGED',
               'REJECTED',
               'PROCESSING',
               'COMPLETED',
               'ACTION_REQUIRED');

Line: 391

    UPDATE pos_imp_batch_summary
    SET    batch_status      = 'ACTIVE',
           last_updated_by   = hz_utility_v2pub.last_updated_by,
           last_update_date  = hz_utility_v2pub.last_update_date,
           last_update_login = hz_utility_v2pub.last_update_login
    WHERE  batch_id = p_batch_id;

Line: 459

      SELECT REPLACE(substr(version, 1, instr(version, '.', 1, 3)), '.')
      INTO   i
      FROM   v$instance;

Line: 496

    /* Delete party data from HZ interface tables */
    hz_imp_purge_pkg.purge_batch(errbuf, retcode, p_batch_id);

Line: 501

      SELECT main_conc_status
      INTO   l_status
      FROM   pos_imp_batch_summary
      WHERE  batch_id = p_batch_id;

Line: 527

    DELETE ap_suppliers_int WHERE sdh_batch_id = p_batch_id;

Line: 530

    DELETE ap_supplier_sites_int WHERE sdh_batch_id = p_batch_id;

Line: 533

    DELETE ap_sup_site_contact_int WHERE sdh_batch_id = p_batch_id;

Line: 536

    DELETE pos_product_service_int WHERE sdh_batch_id = p_batch_id;

Line: 539

    DELETE pos_business_class_int WHERE sdh_batch_id = p_batch_id;

Line: 542

    DELETE pos_party_tax_profile_int WHERE batch_id = p_batch_id;

Line: 545

    DELETE pos_party_tax_reg_int WHERE batch_id = p_batch_id;

Line: 548

    DELETE pos_fiscal_class_int WHERE batch_id = p_batch_id;

Line: 551

    DELETE pos_bank_account_det_int WHERE batch_id = p_batch_id;

Line: 554

    DELETE pos_bank_accnt_owners_int WHERE batch_id = p_batch_id;

Line: 560

                      ' Update pos_imp_batch_summary table (+)');

Line: 562

    UPDATE pos_imp_batch_summary
    SET    batch_status      = 'PURGED',
           purge_date        = SYSDATE,
           purged_by_user_id = hz_utility_v2pub.user_id
    WHERE  batch_id = p_batch_id;

Line: 570

                      ' Update pos_imp_batch_summary table (-)');

Line: 614

      SELECT pos.batch_id,
             pos.original_system
      INTO   l_batch_id,
             l_original_system
      FROM   pos_imp_batch_summary pos,
             hz_imp_batch_summary  hz
      WHERE  pos.batch_id = hz.batch_id
      AND    pos.batch_id = p_batch_id;

Line: 639

      SELECT pos.batch_id
      INTO   l_batch_id
      FROM   pos_imp_batch_summary pos, hz_imp_batch_summary hz
      WHERE  pos.batch_id = hz.batch_id
      and    pos.batch_id = p_batch_id
      AND    nvl(pos.batch_status,'ACTIVE') NOT IN
             ('PURGED',
              'REJECTED',
              'PROCESSING',
              'COMPLETED',
              'ACTION_REQUIRED')
      AND    nvl(hz.batch_status,'ACTIVE') NOT IN
             ('PURGED',
              'REJECTED',
              'PROCESSING',
              'COMPLETED',
              'ACTION_REQUIRED');

Line: 673

    UPDATE hz_imp_batch_summary
    SET    batch_status      = 'REJECTED',
           import_status     = 'REJECTED',
           last_updated_by   = hz_utility_v2pub.last_updated_by,
           last_update_date  = hz_utility_v2pub.last_update_date,
           last_update_login = hz_utility_v2pub.last_update_login
    WHERE  batch_id = p_batch_id;

Line: 681

    UPDATE pos_imp_batch_summary
    SET    batch_status      = 'REJECTED',
           import_status     = 'REJECTED',
           last_updated_by   = hz_utility_v2pub.last_updated_by,
           last_update_date  = hz_utility_v2pub.last_update_date,
           last_update_login = hz_utility_v2pub.last_update_login
    WHERE  batch_id = p_batch_id;

Line: 754

    hz_utility_v2pub.validate_mandatory(p_create_update_flag => 'C',
                                        p_column             => 'batch_name',
                                        p_column_value       => p_batch_name,
                                        p_restricted         => 'Y',
                                        x_return_status      => x_return_status);

Line: 761

    hz_utility_v2pub.validate_mandatory(p_create_update_flag => 'C',
                                        p_column             => 'original_system',
                                        p_column_value       => p_original_system,
                                        p_restricted         => 'Y',
                                        x_return_status      => x_return_status);

Line: 768

      SELECT 'Y'
      INTO   os_exists_flag
      FROM   hz_orig_systems_b
      WHERE  orig_system = p_original_system
      AND    orig_system <> 'SST'
      AND    status = 'A';

Line: 792

    INSERT INTO pos_imp_batch_summary
      (batch_id,
       batch_name,
       description,
       original_system,
       load_type,
       est_no_of_records,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date,
       last_update_login)
    VALUES
      (p_batch_id,
       p_batch_name,
       p_description,
       p_original_system,
       p_load_type,
       p_est_no_of_records,
       hz_utility_v2pub.created_by,
       hz_utility_v2pub.creation_date,
       hz_utility_v2pub.last_updated_by,
       hz_utility_v2pub.last_update_date,
       hz_utility_v2pub.last_update_login);