Common Tables

| cardcore_issuing | tbl_cardcore_card_master tbl_cardcore_facility_group_account_master tbl_cardcore_group_account_master tbl_cardcore_transaction_group_account_master tbl_cardcore_sub_account_master tbl_cardcore_statement tbl_cardcore_transaction tbl_cardcore_interest_transaction_group_bearing_balance tbl_cardcore_payment_posting_history tbl_cardcore_payment_posting_knockoff_transaction_history tbl_cardcore_overpayment tbl_cardcore_authorization tbl_cardcore_authorization_extended tbl_cardcore_temporary_transactions tbl_cardcore_collection_acct tbl_cardcore_notes tbl_cardcore_ipp_order tbl_cardcore_ipp_installment | | --- | --- |

Common Query

-- CM
SELECT * FROM 
cardcore_issuing.tbl_cardcore_card_master 
WHERE card_number IN ('5395050094939868');

-- FGA
SELECT * FROM cardcore_issuing.tbl_cardcore_facility_group_account_master WHERE account_number 
IN (SELECT facility_group_account_number FROM cardcore_issuing.tbl_cardcore_group_account_master WHERE account_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')));

-- GA
SELECT * FROM
cardcore_issuing.tbl_cardcore_group_account_master
WHERE account_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'));

-- GA From FGA
SELECT * FROM
cardcore_issuing.tbl_cardcore_group_account_master WHERE facility_group_account_number 
IN (SELECT facility_group_account_number FROM cardcore_issuing.tbl_cardcore_group_account_master WHERE account_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')));

-- TGA
SELECT * FROM 
cardcore_issuing.tbl_cardcore_transaction_group_account_master
WHERE group_account_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'))
order by transaction_group_account_number;

-- SA
SELECT * FROM 
cardcore_issuing.tbl_cardcore_sub_account_master
WHERE transaction_group_account_number IN (SELECT transaction_group_account_number FROM 
cardcore_issuing.tbl_cardcore_transaction_group_account_master
WHERE group_account_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('7777778892225490','7777778897379116')))
order by sub_account_number;

-- GA stmt
SELECT * FROM
cardcore_issuing.tbl_cardcore_statement
WHERE account_number = (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')) order by period;

-- SA stmt
select * from cardcore_issuing.tbl_cardcore_statement
where account_number in (
   select s.sub_account_number from cardcore_issuing.tbl_cardcore_sub_account_master s
   left join cardcore_issuing.tbl_cardcore_transaction_group_account_master t 
   on s.transaction_group_account_number=t.transaction_group_account_number
   where t.group_account_number = (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'))
) order by period, account_number;

-- All Trx
SELECT * FROM 
cardcore_issuing.tbl_cardcore_transaction 
WHERE group_acount_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')) 
order by entry_date, period;

-- Trx Group brearing
select * from cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance
where sub_account_number in (SELECT sub_account_number FROM
cardcore_issuing.tbl_cardcore_sub_account_master
WHERE transaction_group_account_number IN (SELECT transaction_group_account_number FROM
cardcore_issuing.tbl_cardcore_transaction_group_account_master
WHERE group_account_number = (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('7777778898525803'))))
order by sub_account_number, entry_date, interest_code, value_date;

-- GA PPH
SELECT * FROM cardcore_issuing.tbl_cardcore_payment_posting_history WHERE group_acount_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'));

-- TGA PPH
SELECT * FROM cardcore_issuing.tbl_cardcore_payment_posting_history WHERE group_acount_number 
IN (SELECT transaction_group_account_number FROM cardcore_issuing.tbl_cardcore_transaction_group_account_master
WHERE group_account_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')));

-- SA PPH
select pph.* 
from cardcore_issuing.tbl_cardcore_payment_posting_history pph 
join cardcore_issuing.tbl_cardcore_sub_account_master sa on sa.sub_account_number = pph.account_number
join cardcore_issuing.tbl_cardcore_transaction_group_account_master ta on ta.id = sa.transaction_group_account_id 
where ta.group_account_number IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')) 
and pph.account_category = '3'
order by pph.entry_date, pph.account_number;

-- PPK
select p.* from cardcore_issuing.tbl_cardcore_payment_posting_knockoff_transaction_history p
left join cardcore_issuing.tbl_cardcore_sub_account_master s on p.account_number = s.sub_account_number
left join cardcore_issuing.tbl_cardcore_transaction_group_account_master t on t.transaction_group_account_number = s.transaction_group_account_number
where t.group_account_number in (
	(SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')) 
)
order by p.id, created_datetime, payment_date;

-- Overpayment
SELECT * FROM cardcore_issuing.tbl_cardcore_overpayment WHERE account_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868')) ORDER BY created_datetime DESC;

-- Auth
SELECT * FROM cardcore_issuing.tbl_cardcore_authorization WHERE card_number IN ('5395050094939868') ORDER BY transaction_date DESC;

-- Auth Extended
SELECT * FROM cardcore_issuing.tbl_cardcore_authorization_extended
where authorization_id IN ( SELECT id FROM cardcore_issuing.tbl_cardcore_authorization WHERE card_number = '5395050094939868');

-- Temp Trx
SELECT * FROM cardcore_issuing.tbl_cardcore_temporary_transactions WHERE card_number IN ('5395050094939868') ORDER BY entry_date, period;

-- Collection
SELECT * FROM cardcore_issuing.tbl_cardcore_collection_acct where account_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'));

-- Notes
SELECT * FROM cardcore_issuing.tbl_cardcore_notes WHERE account_number 
IN (SELECT account_number FROM cardcore_issuing.tbl_cardcore_card_master WHERE card_number IN ('5395050094939868'))
ORDER BY account_number, created_datetime DESC;

-- IPP 
SELECT * FROM cardcore_issuing.tbl_cardcore_ipp_order WHERE card_number IN ('5395050094939868');

-- IPP Installement
SELECT * FROM cardcore_issuing.tbl_cardcore_ipp_installment WHERE ipp_order_id IN (SELECT id FROM cardcore_issuing.tbl_cardcore_ipp_order WHERE card_number IN ('5395050094939868'));