6 queries found for "Payment Header"

Get all from Payment

select * 
from CI_PAY

uploaded by Nuno Rodrigues

Get specific Payment

select * 
from CI_PAY
where 
PAY_ID = 'XXXX'

uploaded by Nuno Rodrigues

Retrieve Historical Payments for Account

SELECT
  PEVT.PAY_DT,
  SUM(PAY.PAY_AMT) as PAY_AMT,
  'P' AS TYPE
FROM 
  CI_PAY PAY,
  CI_PAY_EVENT PEVT
WHERE
  PAY.ACCT_ID = 'INSERT_ACCOUNT_ID'
  AND PAY.PAY_STATUS_FLG = '50'
  AND PEVT.PAY_EVENT_ID = PAY.PAY_EVENT_ID 
  AND PEVT.PAY_DT > 'INSERT_INITIAL_SEARCH_DATE'
  AND PEVT.PAY_DT <= 'INSERT_FINAL_SEARCH_DATE'
GROUP BY PEVT.PAY_DT

uploaded by Nuno Rodrigues

Retrieve Open Item Payment FT without Match Event

SELECT FT.FT_ID
FROM   CI_PAY P,
       CI_FT FT
WHERE  FT.PARENT_ID = P.PAY_ID
       AND P.PAY_EVENT_ID = 'INSERT_PAYMENT_EVENT_ID'
       AND MATCH_EVT_ID = ' '

uploaded by Nuno Rodrigues

Retrieve Match Events for Payment Event

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_PAY P,
       CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.PARENT_ID = P.PAY_ID
       AND P.PAY_EVENT_ID = 'INSERT_PAY_EVENT_ID'
       AND ( FT.FT_TYPE_FLG = 'PS'
              OR FT.FT_TYPE_FLG = 'PX' )
       AND FT.MATCH_EVT_ID = ME.MATCH_EVT_ID

uploaded by Nuno Rodrigues

Get payment

SELECT 
    PE.PAY_DT,
    PY.PAY_AMT,
    PY.PAY_ID,
    PE.PAY_EVENT_ID
FROM
    CI_PAY PY
WHERE 
    PY.ACCT_ID = :1
    AND PY.PAY_STATUS_FLG = :2
    AND PY.PAY_EVENT_ID = PE.PAY_EVENT_ID
ORDER BY
    PE.PAY_DT DESC
--
-- :1 – Account Id
-- :2 – Payment Status = 50 (Frozen)

uploaded by Greg Kung