###### tags: `三商` `IFRS 17` `SAS`

# 12/23(五) 系統整合測試討論會議_QA放題

## 以下開放大家問題填寫

---
## 問題範例：

Q：我對於今天的會議有ｏｏｘｘ疑問
> [name=施俊豪][time=Mon, Jan 2, 2023 12:49 PM]

A：請不要亂提　

---

[問券調查網址](https://www.surveycake.com/s/mZnOM)

問券調查QR Code

---

Q:

Step 1
-- summary pp17jx amount 
select entry_no ,tran_date ,function_code 
, policy_no ,coverage_no 
,plan_code ,rate_scale 
,acct_no ,sum(amount) as total_amt
from pp17jx
group by entry_no ,tran_date ,function_code,policy_no ,coverage_no ,plan_code ,rate_scale ,acct_no
into temp tmp_pp17jx

step 2
-- searching for the different amount between pp17jx and ppjd
-- order by tran_date desc
select a.* , b.amount 
from tmp_pp17jx a
left join ppjd b on a.entry_no = b.entry_no and a.tran_date = b.tran_date 
and a.function_code = b.function_code 
and a.policy_no = b.policy_no and a.coverage_no = b.coverage_no  
and a.plan_code = b.plan_code and a.rate_scale = b.rate_scale 
and a.acct_no = b.acct_no 
where a.total_amt <> b.amount 
order by a.tran_date desc


Step 3
-- check from pp17jx
select a.*,b.acct_title_c
from pp17jx a
join glac b on a.acct_no  = b.acct_no
where entry_no  = '390708922'
and tran_date = '111/12/28'
and policy_no = '177500390303'


Step 4
--  check from ppjd
select a.*,b.acct_title_c
from ppjd a
join glac b on a.acct_no  = b.acct_no
where entry_no  = '390708922'
and tran_date = '111/12/28'
and policy_no = '177500390303'