APPS.CN_NOTIFY_AIA_OM SQL Statements

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

Line: 29

    CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;

Line: 43

     cn_process_audits_pkg.insert_row(x_rowid,
                                       x_proc_audit_id,
                                       NULL,
                                      'NOT',
                                      'Notification run',
                                      NULL,
                                      NULL,
                                      NULL,
                                      NULL,
                                      NULL,
                                      SYSDATE,
                                      NULL,
                                      x_org_id);

Line: 75

    INSERT INTO  cn_not_trx (
        not_trx_id,
        batch_id,
        processed_date,
        notified_date,
        notification_run_id,
        collected_flag,
        event_id,
        source_trx_id,
        source_trx_line_id,
        source_doc_type,
        org_id)
    SELECT
        cn_not_trx_s.NEXTVAL,
        cnot.t_batch_id,
        cnot.t_processed_date,
        cnot.t_notified_date,
        cnot.t_notification_run_id,
        cnot.t_collected_flag,
        cnot.t_event_id,
        cnot.t_source_trx_id,   --*** Header.Primary_Key
        cnot.t_source_trx_line_id,     --*** Line Table Key Column
        cnot.t_source_doc_type,     --*** Source Type
        cnot.t_org_id
    FROM
     (SELECT
        distinct
        x_proc_audit_id t_batch_id,
        processed_date t_processed_date,
        SYSDATE t_notified_date,
        x_proc_audit_id t_notification_run_id,
        'N' t_collected_flag,
        -1030 t_event_id,
        null t_source_trx_id,   --*** Header.Primary_Key
        cco10145.trans_seq_id t_source_trx_line_id,     --*** Line Table Key Column
        'AIA_OM' t_source_doc_type,     --*** Source Type
        x_org_id t_org_id
      FROM
        cn_aia_order_capture cco10145
      WHERE     --*** Header.Primary_Key = Line.Foreign_Key
        1 = 1
        AND TRUNC(processed_date) BETWEEN x_start_date AND x_end_date
        AND preprocess_flag  = FND_API.G_FALSE
        AND   org_id = x_org_id
          AND NOT EXISTS (
            SELECT 1
            FROM  cn_not_trx
            WHERE source_trx_line_id = cco10145.trans_seq_id      --*** Line.Primary_Key
            AND   event_id = -1030
            AND   org_id = x_org_id)
            ) cnot;

Line: 131

    cn_process_audits_pkg.update_row(x_proc_audit_id,
                                      NULL,
                                      SYSDATE,
                                      0,
                                      'Finished notification run: Notified ' || x_trx_count || ' aia order records.');

Line: 139

      cn_message_pkg.debug('notify_aia_om: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');

Line: 140

      fnd_file.put_line(fnd_file.Log, 'notify_aia_om: No rows inserted into CN_NOT_TRX. Possible reason: Transactions have not been posted to AIA Interface table and processed date is null.');

Line: 160

    cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
      SQLERRM);

Line: 191

      SELECT distinct original_order_number, ln_num
      FROM CN_AIA_ORDER_CAPTURE
      WHERE org_id  = x_org_id and trans_seq_id in
      ( SELECT source_trx_line_id
        FROM cn_not_trx_all
        WHERE batch_id = p_batch_id and org_id = x_org_id and source_trx_line_id not in
        (SELECT source_trx_line_id
         FROM cn_comm_lines_api_all
         WHERE process_batch_id =  p_batch_id and org_id = x_org_id)
      ) AND TRUNC(processed_date) BETWEEN p_start_date AND p_end_date;