Wednesday 10 June 2015

Payment Batch Status query in oracle apps

SELECT *
  FROM ( SELECT checkrun_id
              , checkrun_name
              , status checkrun_status
              , ( SELECT payment_service_request_status
                    FROM iby.iby_pay_service_requests
                   WHERE calling_app_id                = 200
                     AND call_app_pay_service_req_code = isc.checkrun_name
                ) pay_service_req_status
              , ( SELECT pia.payment_instruction_status
                    FROM iby.iby_pay_instructions_all pia
                   WHERE pay_admin_assigned_ref_code   = isc.checkrun_name
                ) instruction_status
              , last_update_date
           FROM ap.ap_inv_selection_criteria_all isc
       )
  WHERE 1 = 1
    --
    -- these conditions will return payment batches in process
    --
    --AND checkrun_status                 NOT IN ('CANCELLED NO PAYMENTS','CANCELED','CONFIRMED')
    --AND NVL(pay_service_req_status,'X') NOT IN ('COMPLETED', 'TERMINATED', 'CONFIRMED')
    --AND NVL(instruction_status    ,'X') NOT IN ('TERMINATED')
 ORDER BY last_update_date DESC;

No comments:

Post a Comment