diff --git a/osgi-bundles/bundles/analytics/src/main/resources/sanity/sanity.sql b/osgi-bundles/bundles/analytics/src/main/resources/sanity/sanity.sql
index a0d011e..a20928b 100644
--- a/osgi-bundles/bundles/analytics/src/main/resources/sanity/sanity.sql
+++ b/osgi-bundles/bundles/analytics/src/main/resources/sanity/sanity.sql
@@ -1,7 +1,7 @@
-- bac
-- A1a
-select a.updated_date, bac.updated_date
+select *
from accounts a
left outer join bac on a.id = bac.account_id
where a.record_id != bac.account_record_id
@@ -26,18 +26,16 @@ where a.record_id != bac.account_record_id
or ( coalesce(a.migrated , '') != coalesce(bac.migrated ,''))
or ( coalesce(a.is_notified_for_invoices , '') != coalesce(bac.notified_for_invoices ,''))
or a.created_date != bac.created_date
- or a.created_by != bac.created_by
- -- RI-1967 or a.updated_date != bac.updated_date
- -- or a.updated_by != bac.updated_by
- -- RI-1966 or a.tenant_record_id != bac.tenant_record_id
+ or a.updated_date != bac.updated_date
+ or a.tenant_record_id != bac.tenant_record_id
-- A1b
-select a.updated_date, bac.updated_date
+select *
from bac
left outer join accounts a on a.id = bac.account_id
where a.record_id != bac.account_record_id
or ( coalesce(a.id , '') != coalesce(bac.account_id ,''))
- or a.external_key , '') != bac.account_external_key
+ or a.external_key != bac.account_external_key
or ( coalesce(a.email , '') != coalesce(bac.email ,''))
or ( coalesce(a.name , '') != coalesce(bac.account_name ,''))
or ( coalesce(a.first_name_length , '') != coalesce(bac.first_name_length ,''))
@@ -56,14 +54,112 @@ where a.record_id != bac.account_record_id
or ( coalesce(a.phone , '') != coalesce(bac.phone ,''))
or ( coalesce(a.migrated , '') != coalesce(bac.migrated ,''))
or ( coalesce(a.is_notified_for_invoices , '') != coalesce(bac.notified_for_invoices ,''))
- or ( coalesce(a.created_date, '') != ( coalesce(bac.created_date,''))
- or ( coalesce(a.created_by != ( coalesce(bac.created_by,''))
- -- RI-1967 or ( coalesce(a.updated_date, '') != ( coalesce(bac.updated_date,''))
- -- or ( coalesce(a.updated_b, '')y != ( coalesce(bac.updated_by,''))
- -- RI-1966 or ( coalesce(a.tenant_record_id, '') != ( coalesce(bac.tenant_record_id,''))
+ or ( coalesce(a.created_date, '') != coalesce(bac.created_date,''))
+ or ( coalesce(a.updated_date, '') != coalesce(bac.updated_date,''))
+ or ( coalesce(a.tenant_record_id, '') != coalesce(bac.tenant_record_id,''))
+
+-- A2
+select *
+from bac b
+ join account_history ah on b.account_record_id = ah.record_id
+ join audit_log al on ah.record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'ACCOUNT_HISTORY'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+*****
+
+-- bac_fields
+
+-- K1a TODO
+select *
+from custom_fields cf
+ left outer join bac_fields b on cf.record_id = b.custom_field_record_id
+where ( coalesce(b.name, 'NULL') != coalesce(cf.field_name,'NULL')
+ or coalesce(b.value, 'NULL') != coalesce(cf.field_value,'NULL')
+ or coalesce(b.created_date, 'NULL') != coalesce(cf.created_date,'NULL')
+ or coalesce(b.account_record_id, 'NULL') != coalesce(cf.account_record_id,'NULL')
+ or coalesce(b.tenant_record_id, 'NULL') != coalesce(cf.tenant_record_id,'NULL') )
+ and cf.object_type = 'ACCOUNT'
+
+-- K1b TODO
+select *
+from bac_fields b
+ left outer join custom_fields cf on cf.record_id = b.custom_field_record_id
+where coalesce(b.name, 'NULL') != coalesce(cf.field_name,'NULL')
+ or coalesce(b.value, 'NULL') != coalesce(cf.field_value,'NULL')
+ or coalesce(b.created_date, 'NULL') != coalesce(cf.created_date,'NULL')
+ or coalesce(b.account_record_id, 'NULL') != coalesce(cf.account_record_id,'NULL')
+ or coalesce(b.tenant_record_id, 'NULL') != coalesce(cf.tenant_record_id,'NULL')
+ or cf.object_type != 'ACCOUNT'
+
+-- K2
+select *
+from bac_fields b
+ left outer join accounts a on a.id = b.account_id
+where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
+ or coalesce(a.id , '') != coalesce(b.account_id ,'')
+ or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
+ or coalesce(a.name , '') != coalesce(b.account_name ,'')
+
+-- K3
+select *
+from bac_fields b
+ join custom_field_history cfh on b.custom_field_record_id = cfh.target_record_id
+ join audit_log al on cfh.record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'CUSTOM_FIELD_HISTORY'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
*****
+-- bac_tags
+
+-- L1a
+select *
+from tags t
+ join tag_definitions td on t.tag_definition_id = td.id
+ left outer join bac_tags b on t.record_id = b.tag_record_id
+where coalesce(b.tag_record_id, 'NULL') != coalesce(t.record_id,'NULL')
+ or coalesce(b.name, 'NULL') != coalesce(td.name,'NULL')
+ or coalesce(b.created_date, 'NULL') != coalesce(t.created_date,'NULL')
+ or coalesce(b.account_record_id, 'NULL') != coalesce(t.account_record_id,'NULL')
+ or coalesce(b.tenant_record_id, 'NULL') != coalesce(t.tenant_record_id,'NULL')
+
+-- L1b
+-- finds system tags incorrectly
+select *
+from bac_tags b
+ left outer join tags t on t.record_id = b.tag_record_id
+ left outer join tag_definitions td on t.tag_definition_id = td.id
+where coalesce(b.tag_record_id, 'NULL') != coalesce(t.record_id,'NULL')
+ or coalesce(b.name, 'NULL') != coalesce(td.name,'NULL')
+ or coalesce(b.created_date, 'NULL') != coalesce(t.created_date,'NULL')
+ or coalesce(b.account_record_id, 'NULL') != coalesce(t.account_record_id,'NULL')
+ or coalesce(b.tenant_record_id, 'NULL') != coalesce(t.tenant_record_id,'NULL')
+
+-- L2
+select *
+from bac_tags b
+ left outer join accounts a on a.id = b.account_id
+where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
+ or coalesce(a.id , '') != coalesce(b.account_id ,'')
+ or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
+ or coalesce(a.name , '') != coalesce(b.account_name ,'')
+
+-- L3
+select *
+from bac_tags b
+ join tag_history th on b.tag_record_id = th.target_record_id
+ join audit_log al on th.record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'TAG_HISTORY'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+*****
+
+
-- bia
-- B1a
@@ -71,7 +167,7 @@ where a.record_id != bac.account_record_id
select *
from invoice_items ii
left outer join bia b on ii.id = b.item_id
-where ii.type in ('CREDIT_ADJ')
+where ii.type in ('CREDIT_ADJ','REFUND_ADJ')
and (( coalesce(ii.record_id, '') != coalesce(b.invoice_item_record_id,''))
or ( coalesce(ii.id, '') != coalesce(b.item_id,''))
or ( coalesce(ii.type, '') != coalesce(b.item_type,''))
@@ -79,14 +175,13 @@ where ii.type in ('CREDIT_ADJ')
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
)
-- B1b
@@ -100,15 +195,14 @@ where ( coalesce(ii.record_id, '') != coalesce(b.invoice_item_record_id,''))
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
- or ii.type not in ('CREDIT_ADJ')
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ii.type not in ('CREDIT_ADJ','REFUND_ADJ')
-- B2
select *
@@ -147,6 +241,16 @@ where b.invoice_balance != bin.balance
or b.invoice_original_amount_charged != bin.original_amount_charged
or b.invoice_amount_credited != bin.amount_credited
+-- B6
+select *
+from bia b
+ join audit_log al on b.invoice_item_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_ITEMS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+
+
*****
-- bii
@@ -163,14 +267,13 @@ where ii.type in ('FIXED','RECURRING','EXTERNAL_CHARGE')
or ( coalesce(ii.account_id , '')!= coalesce(bii.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(bii.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(bii.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(bii.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(bii.amount,''))
or ( coalesce(ii.currency, '') != coalesce(bii.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(bii.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(bii.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(bii.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(bii.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(bii.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(bii.tenant_record_id,''))
)
-- C1b
@@ -184,14 +287,13 @@ where ( coalesce(ii.record_id, '') != coalesce(bii.invoice_item_record_id,''))
or ( coalesce(ii.account_id , '')!= coalesce(bii.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(bii.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(bii.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(bii.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(bii.amount,''))
or ( coalesce(ii.currency, '') != coalesce(bii.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(bii.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(bii.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(bii.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(bii.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(bii.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(bii.tenant_record_id,''))
or ii.type not in ('FIXED','RECURRING','EXTERNAL_CHARGE')
-- C2
@@ -230,6 +332,14 @@ where b.invoice_balance != bin.balance
or b.invoice_original_amount_charged != bin.original_amount_charged
or b.invoice_amount_credited != bin.amount_credited
+-- C6
+select *
+from bii b
+ join audit_log al on b.invoice_item_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_ITEMS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
*****
-- biia
@@ -246,36 +356,34 @@ where ii.type in ('ITEM_ADJ')
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
)
-- D1b
-select *
+select ii.type, b.item_type, ii.amount, b.amount, b.product_type -- *
from biia b
left outer join invoice_items ii on ii.id = b.item_id
where ( coalesce(ii.record_id, '') != coalesce(b.invoice_item_record_id,''))
or ( coalesce(ii.id, '') != coalesce(b.item_id,''))
- or ( coalesce(ii.type, '') != coalesce(b.item_type,''))
+ or ( coalesce(case when ii.type= 'REPAIR_ADJ' then 'ITEM_ADJ' else ii.type end, '') != coalesce(b.item_type,''))
or ( coalesce(ii.invoice_id, '') != coalesce(b.invoice_id,''))
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
- or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
+ or ( ( coalesce(ii.amount, '') != coalesce(b.amount,'')) and ii.type != 'REPAIR_ADJ' ) -- need to calc correct amount in case of REPAIR_ADJ case
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
- or ii.type not in ('ITEM_ADJ')
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ii.type not in ('ITEM_ADJ','REPAIR_ADJ')
-- D2
select *
@@ -313,6 +421,15 @@ where b.invoice_balance != bin.balance
or b.invoice_amount_charged != bin.amount_charged
or b.invoice_original_amount_charged != bin.original_amount_charged
or b.invoice_amount_credited != bin.amount_credited
+
+-- D6
+select *
+from biia b
+ join audit_log al on b.invoice_item_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_ITEMS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
****
-- biic
@@ -329,14 +446,13 @@ where ii.type in ('CBA_ADJ')
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
)
-- E1b
@@ -350,14 +466,13 @@ where ( coalesce(ii.record_id, '') != coalesce(b.invoice_item_record_id,''))
or ( coalesce(ii.account_id , '')!= coalesce(b.account_id,''))
or ( coalesce(ii.phase_name, '') != coalesce(b.slug,''))
or ( coalesce(ii.start_date, '') != coalesce(b.start_date,''))
- -- old trials or ( coalesce(ii.end_date, '') != coalesce(b.end_date,''))
+ -- RI-1986 or ( coalesce(ii.end_date, ii.start_date + interval 30 day) != coalesce(b.end_date,''))
or ( coalesce(ii.amount, '') != coalesce(b.amount,''))
or ( coalesce(ii.currency, '') != coalesce(b.currency,''))
or ( coalesce(ii.linked_item_id, '') != coalesce(b.linked_item_id,''))
- or ( coalesce(ii.created_by, '') != coalesce(b.created_by,''))
or ( coalesce(ii.created_date, '') != coalesce(b.created_date,''))
or ( coalesce(ii.account_record_id, '') != coalesce(b.account_record_id,''))
- -- RI-1966 or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
+ or ( coalesce(ii.tenant_record_id, '') != coalesce(b.tenant_record_id,''))
or ii.type not in ('CBA_ADJ')
-- E2
@@ -396,8 +511,27 @@ where b.invoice_balance != bin.balance
or b.invoice_original_amount_charged != bin.original_amount_charged
or b.invoice_amount_credited != bin.amount_credited
+-- E6
+select *
+from biic b
+ join audit_log al on b.invoice_item_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_ITEMS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+
****
+-- bin_fields
+/* table not currently used */
+
+*****
+
+-- bin_tags
+/* table not currently used */
+
+*****
+
-- bin
-- F1a
@@ -411,10 +545,9 @@ where coalesce(i.record_id, '') != coalesce(bin.invoice_record_id,'')
or ( coalesce(i.invoice_date, '') != coalesce(bin.invoice_date,''))
or ( coalesce(i.target_date, '') != coalesce(bin.target_date,''))
or ( coalesce(i.currency, '') != coalesce(bin.currency,''))
- or ( coalesce(i.created_by, '') != coalesce(bin.created_by,''))
or ( coalesce(i.created_date, '') != coalesce( bin.created_date,''))
or ( coalesce(i.account_record_id, '') != coalesce(bin.account_record_id,''))
- -- RI-1966 or ( coalesce(i.tenant_record_id, '') != coalesce(bin.tenant_record_id,''))
+ or ( coalesce(i.tenant_record_id, '') != coalesce(bin.tenant_record_id,''))
-- F1b
select *
@@ -426,7 +559,6 @@ where ( coalesce(i.record_id, '') != coalesce(bin.invoice_record_id,''))
or ( coalesce(i.invoice_date, '') != coalesce(bin.invoice_date,''))
or ( coalesce(i.target_date, '') != coalesce(bin.target_date,''))
or ( coalesce(i.currency, '') != coalesce(bin.currency,''))
- or ( coalesce(i.created_by, '') != coalesce(bin.created_by,''))
or ( coalesce(i.created_date, '') != coalesce(bin.created_date,''))
or ( coalesce(i.account_record_id, '') != coalesce(bin.account_record_id,''))
-- RI-1966or ( coalesce(i.tenant_record_id, '') != coalesce(bin.tenant_record_id,''))
@@ -440,6 +572,14 @@ where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
or coalesce(a.name , '') != coalesce(b.account_name ,'')
+-- F6
+select *
+from bin b
+ join audit_log al on b.invoice_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICES'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
*****
@@ -450,16 +590,15 @@ select *
from invoice_payments ip
left outer join bip on ip.id = bip.invoice_payment_id
where ( coalesce(ip.RECORD_ID, 'NULL') != coalesce(bip.invoice_payment_record_id,'NULL')
- or coalesce(ip.ID, 'NULL') != coalesce(bip.invoice_payment_id,'NULL')
- or coalesce(ip.invoice_id, 'NULL') != coalesce(bip.invoice_id,'NULL')
- or coalesce(ip.type, 'NULL') != coalesce(bip.invoice_payment_type,'NULL')
- or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bip.linked_invoice_payment_id,'NULL')
- or coalesce(ip.amount, 'NULL') != coalesce(bip.amount,'NULL')
- or coalesce(ip.currency, 'NULL') != coalesce(bip.currency,'NULL')
- or coalesce(ip.created_date, 'NULL') != coalesce(bip.created_date,'NULL')
- or coalesce(ip.created_by, 'NULL') != coalesce(bip.created_by,'NULL')
- or coalesce(ip.account_record_id, 'NULL') != coalesce(bip.account_record_id,'NULL')
- -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bip.tenant_record_id,'NULL')
+ or coalesce(ip.ID, 'NULL') != coalesce(bip.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bip.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bip.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bip.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bip.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bip.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bip.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bip.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bip.tenant_record_id,'NULL')
)
and ip.type = 'ATTEMPT'
@@ -469,19 +608,18 @@ select *
from bip
left outer join invoice_payments ip on ip.id = bip.invoice_payment_id
where coalesce(ip.RECORD_ID, 'NULL') != coalesce(bip.invoice_payment_record_id,'NULL')
- or coalesce(ip.ID, 'NULL') != coalesce(bip.invoice_payment_id,'NULL')
- or coalesce(ip.invoice_id, 'NULL') != coalesce(bip.invoice_id,'NULL')
- or coalesce(ip.type, 'NULL') != coalesce(bip.invoice_payment_type,'NULL')
- or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bip.linked_invoice_payment_id,'NULL')
- or coalesce(ip.amount, 'NULL') != coalesce(bip.amount,'NULL')
- or coalesce(ip.currency, 'NULL') != coalesce(bip.currency,'NULL')
- or coalesce(ip.created_date, 'NULL') != coalesce(bip.created_date,'NULL')
- or coalesce(ip.created_by, 'NULL') != coalesce(bip.created_by,'NULL')
- or coalesce(ip.account_record_id, 'NULL') != coalesce(bip.account_record_id,'NULL')
- -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bip.tenant_record_id,'NULL')
- or bip.invoice_payment_type != 'ATTEMPT'
-
--- G2
+ or coalesce(ip.ID, 'NULL') != coalesce(bip.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bip.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bip.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bip.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bip.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bip.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bip.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bip.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bip.tenant_record_id,'NULL')
+ or bip.invoice_payment_type != 'ATTEMPT'
+
+-- G2
select *
from bip b
left outer join accounts a on a.id = b.account_id
@@ -497,8 +635,8 @@ where coalesce(i.RECORD_ID, 'NULL') != coalesce(b.invoice_number,'NULL')
or coalesce(i.created_date, 'NULL') != coalesce(b.invoice_created_date,'NULL')
or coalesce(i.invoice_date, 'NULL') != coalesce(b.invoice_date,'NULL')
or coalesce(i.target_date, 'NULL') != coalesce(b.invoice_target_date,'NULL')
- or coalesce(i.currency, 'NULL') != coalesce(b.invoice_currency,'NULL')
-
+ or coalesce(i.currency, 'NULL') != coalesce(b.invoice_currency,'NULL')
+
-- G4
select *
from bip b
@@ -507,15 +645,173 @@ where b.invoice_balance != bin.balance
or b.invoice_amount_paid != bin.amount_paid
or b.invoice_amount_charged != bin.amount_charged
or b.invoice_original_amount_charged != bin.original_amount_charged
- or b.invoice_amount_credited != bin.amount_credited
-
--- G5
+ or b.invoice_amount_credited != bin.amount_credited
+
+-- G5
select *
from bip
left outer join invoice_payments ip on bip.invoice_payment_id = ip.id
left outer join payments p on ip.payment_id = p.id
where coalesce(p.RECORD_ID, 'NULL') != coalesce(bip.payment_number,'NULL')
+-- G6ai
+-- Zuora
+select *
+from bip
+ left outer join invoice_payments ip on bip.invoice_payment_id = ip.id
+ left outer join _zuora_payments pp on ip.payment_id = pp.kb_p_id
+where ( coalesce(pp.z_created_date, 'NULL') != coalesce(bip.plugin_created_date,'NULL')
+ or coalesce(pp.z_effective_date, 'NULL') != coalesce(bip.plugin_effective_date,'NULL')
+ or coalesce(pp.z_status, 'NULL') != coalesce(bip.plugin_status,'NULL')
+ or coalesce(pp.z_gateway_error, 'NULL') != coalesce(bip.plugin_gateway_error,'NULL')
+ or coalesce(pp.z_gateway_error_code, 'NULL') != coalesce(bip.plugin_gateway_error_code,'NULL')
+ or coalesce(pp.z_reference_id, 'NULL') != coalesce(bip.plugin_first_reference_id,'NULL')
+ or coalesce(pp.z_snd_reference_id, 'NULL') != coalesce(bip.plugin_second_reference_id,'NULL') ) and pp.kb_p_id is not null -- workaround until we get plugin name, query will miss missing rows
+
+-- Litle
+
+
+
+
+
+-- PayPal
+
+-- G6bi
+-- Zuora
+select *
+from _zuora_payments pp
+ left outer join invoice_payments ip on ip.payment_id = pp.kb_p_id
+ left outer join bip on bip.invoice_payment_id = ip.id
+where ( coalesce(pp.z_created_date, 'NULL') != coalesce(bip.plugin_created_date,'NULL')
+ or coalesce(pp.z_effective_date, 'NULL') != coalesce(bip.plugin_effective_date,'NULL')
+ or coalesce(pp.z_status, 'NULL') != coalesce(bip.plugin_status,'NULL')
+ or coalesce(pp.z_gateway_error, 'NULL') != coalesce(bip.plugin_gateway_error,'NULL')
+ or coalesce(pp.z_gateway_error_code, 'NULL') != coalesce(bip.plugin_gateway_error_code,'NULL')
+ or coalesce(pp.z_reference_id, 'NULL') != coalesce(bip.plugin_first_reference_id,'NULL')
+ or coalesce(pp.z_snd_reference_id, 'NULL') != coalesce(bip.plugin_second_reference_id,'NULL')
+ ) and z_status != 'Error'
+
+-- Litle
+
+
+
+
+
+-- PayPal
+
+
+-- G7i
+--Zuora
+select *
+from bip
+ left outer join _zuora_payment_methods ppm on bip.plugin_pm_id = ppm.z_pm_id
+where ( coalesce(ppm.z_pm_id, 'NULL') != coalesce(bip.plugin_pm_id,'NULL')
+ or coalesce(ppm.z_default, 'NULL') != coalesce(bip.plugin_pm_is_default,'NULL')) and ppm.z_pm_id is not null -- workaround until we get plugin name, query will miss missing rows
+
+-- Litle
+
+-- PayPal
+
+-- G8
+select *
+from bip b
+ join audit_log al on b.invoice_payment_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_PAYMENTS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+*****
+
+-- bip_fields
+/* table not currently used */
+
+*****
+
+-- bip_tags
+/* table not currently used */
+
+*****
+
+
+-- bipc
+-- H1a
+select *
+from invoice_payments ip
+ left outer join bipc on ip.id = bipc.invoice_payment_id
+where ( coalesce(ip.RECORD_ID, 'NULL') != coalesce(bipc.invoice_payment_record_id,'NULL')
+ or coalesce(ip.ID, 'NULL') != coalesce(bipc.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bipc.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bipc.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipc.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bipc.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bipc.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bipc.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bipc.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipc.tenant_record_id,'NULL')
+ )
+ and ip.type = 'CHARGED-BACK'
+
+-- H1b
+
+select *
+from bipc
+ left outer join invoice_payments ip on ip.id = bipc.invoice_payment_id
+where coalesce(ip.RECORD_ID, 'NULL') != coalesce(bipc.invoice_payment_record_id,'NULL')
+ or coalesce(ip.ID, 'NULL') != coalesce(bipc.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bipc.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bipc.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipc.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bipc.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bipc.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bipc.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bipc.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipc.tenant_record_id,'NULL')
+ or bipc.invoice_payment_type != 'CHARGED-BACK'
+
+-- H2
+select *
+from bipc b
+ left outer join accounts a on a.id = b.account_id
+where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
+ or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
+ or coalesce(a.name , '') != coalesce(b.account_name ,'')
+
+-- H3
+select *
+from bipc b
+ left outer join invoices i on i.id = b.invoice_id
+where coalesce(i.RECORD_ID, 'NULL') != coalesce(b.invoice_number,'NULL')
+ or coalesce(i.created_date, 'NULL') != coalesce(b.invoice_created_date,'NULL')
+ or coalesce(i.invoice_date, 'NULL') != coalesce(b.invoice_date,'NULL')
+ or coalesce(i.target_date, 'NULL') != coalesce(b.invoice_target_date,'NULL')
+ or coalesce(i.currency, 'NULL') != coalesce(b.invoice_currency,'NULL')
+
+-- H4
+select *
+from bipc b
+ left outer join bin on b.invoice_id = bin.invoice_id
+where b.invoice_balance != bin.balance
+ or b.invoice_amount_paid != bin.amount_paid
+ or b.invoice_amount_charged != bin.amount_charged
+ or b.invoice_original_amount_charged != bin.original_amount_charged
+ or b.invoice_amount_credited != bin.amount_credited
+
+-- H5
+select *
+from bipc
+ left outer join invoice_payments ip on bipc.invoice_payment_id = ip.id
+ left outer join payments p on ip.payment_id = p.id
+where coalesce(p.RECORD_ID, 'NULL') != coalesce(bipc.payment_number,'NULL')
+
+
+-- H8
+select *
+from bipc b
+ join audit_log al on b.invoice_payment_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_PAYMENTS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
*****
-- bipr
@@ -524,16 +820,15 @@ select *
from invoice_payments ip
left outer join bipr on ip.id = bipr.invoice_payment_id
where ( coalesce(ip.RECORD_ID, 'NULL') != coalesce(bipr.invoice_payment_record_id,'NULL')
- or coalesce(ip.ID, 'NULL') != coalesce(bipr.invoice_payment_id,'NULL')
- or coalesce(ip.invoice_id, 'NULL') != coalesce(bipr.invoice_id,'NULL')
- or coalesce(ip.type, 'NULL') != coalesce(bipr.invoice_payment_type,'NULL')
- or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipr.linked_invoice_payment_id,'NULL')
- or coalesce(ip.amount, 'NULL') != coalesce(bipr.amount,'NULL')
- or coalesce(ip.currency, 'NULL') != coalesce(bipr.currency,'NULL')
- or coalesce(ip.created_date, 'NULL') != coalesce(bipr.created_date,'NULL')
- or coalesce(ip.created_by, 'NULL') != coalesce(bipr.created_by,'NULL')
- or coalesce(ip.account_record_id, 'NULL') != coalesce(bipr.account_record_id,'NULL')
- -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipr.tenant_record_id,'NULL')
+ or coalesce(ip.ID, 'NULL') != coalesce(bipr.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bipr.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bipr.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipr.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bipr.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bipr.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bipr.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bipr.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipr.tenant_record_id,'NULL')
)
and ip.type = 'REFUND' -- ?
@@ -543,19 +838,18 @@ select *
from bipr
left outer join invoice_payments ip on ip.id = bipr.invoice_payment_id
where coalesce(ip.RECORD_ID, 'NULL') != coalesce(bipr.invoice_payment_record_id,'NULL')
- or coalesce(ip.ID, 'NULL') != coalesce(bipr.invoice_payment_id,'NULL')
- or coalesce(ip.invoice_id, 'NULL') != coalesce(bipr.invoice_id,'NULL')
- or coalesce(ip.type, 'NULL') != coalesce(bipr.invoice_payment_type,'NULL')
- or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipr.linked_invoice_payment_id,'NULL')
- or coalesce(ip.amount, 'NULL') != coalesce(bipr.amount,'NULL')
- or coalesce(ip.currency, 'NULL') != coalesce(bipr.currency,'NULL')
- or coalesce(ip.created_date, 'NULL') != coalesce(bipr.created_date,'NULL')
- or coalesce(ip.created_by, 'NULL') != coalesce(bipr.created_by,'NULL')
- or coalesce(ip.account_record_id, 'NULL') != coalesce(bipr.account_record_id,'NULL')
- -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipr.tenant_record_id,'NULL')
- or bipr.invoice_payment_type != 'REFUND' -- ?
-
--- H2
+ or coalesce(ip.ID, 'NULL') != coalesce(bipr.invoice_payment_id,'NULL')
+ or coalesce(ip.invoice_id, 'NULL') != coalesce(bipr.invoice_id,'NULL')
+ or coalesce(ip.type, 'NULL') != coalesce(bipr.invoice_payment_type,'NULL')
+ or coalesce(ip.linked_invoice_payment_id, 'NULL') != coalesce(bipr.linked_invoice_payment_id,'NULL')
+ or coalesce(ip.amount, 'NULL') != coalesce(bipr.amount,'NULL')
+ or coalesce(ip.currency, 'NULL') != coalesce(bipr.currency,'NULL')
+ or coalesce(ip.created_date, 'NULL') != coalesce(bipr.created_date,'NULL')
+ or coalesce(ip.account_record_id, 'NULL') != coalesce(bipr.account_record_id,'NULL')
+ -- or coalesce(ip.tenant_record_id, 'NULL') != coalesce(bipr.tenant_record_id,'NULL')
+ or bipr.invoice_payment_type != 'REFUND' -- ?
+
+-- H2
select *
from bipr b
left outer join accounts a on a.id = b.account_id
@@ -571,8 +865,8 @@ where coalesce(i.RECORD_ID, 'NULL') != coalesce(b.invoice_number,'NULL')
or coalesce(i.created_date, 'NULL') != coalesce(b.invoice_created_date,'NULL')
or coalesce(i.invoice_date, 'NULL') != coalesce(b.invoice_date,'NULL')
or coalesce(i.target_date, 'NULL') != coalesce(b.invoice_target_date,'NULL')
- or coalesce(i.currency, 'NULL') != coalesce(b.invoice_currency,'NULL')
-
+ or coalesce(i.currency, 'NULL') != coalesce(b.invoice_currency,'NULL')
+
-- H4
select *
from bipr b
@@ -581,38 +875,96 @@ where b.invoice_balance != bin.balance
or b.invoice_amount_paid != bin.amount_paid
or b.invoice_amount_charged != bin.amount_charged
or b.invoice_original_amount_charged != bin.original_amount_charged
- or b.invoice_amount_credited != bin.amount_credited
-
--- H5
+ or b.invoice_amount_credited != bin.amount_credited
+
+-- H5
select *
-from bip
- left outer join invoice_payments ip on bip.invoice_payment_id = ip.id
+from bipr
+ left outer join invoice_payments ip on bipr.invoice_payment_id = ip.id
left outer join payments p on ip.payment_id = p.id
-where coalesce(p.RECORD_ID, 'NULL') != coalesce(bip.payment_number,'NULL')
+where coalesce(p.RECORD_ID, 'NULL') != coalesce(bipr.payment_number,'NULL')
+
+
+-- H8
+select *
+from bipr b
+ join audit_log al on b.invoice_payment_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'INVOICE_PAYMENTS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
*****
-- bos
+-- I1a
+select *
+from blocking_states bs
+ left outer join bos on bs.record_id = bos.blocking_state_record_id
+where ( coalesce(bs.RECORD_ID, 'NULL') != coalesce(bos.blocking_state_record_id,'NULL')
+ or coalesce(bs.BLOCKABLE_ID, 'NULL') != coalesce(bos.bundle_id,'NULL')
+ or coalesce(bs.STATE, 'NULL') != coalesce(bos.status,'NULL')
+ or coalesce(bs.CREATED_DATE, 'NULL') != coalesce(bos.created_date,'NULL')
+ or coalesce(bs.CREATED_DATE, 'NULL') != coalesce(bos.start_date,'NULL')
+ -- or coalesce(bs.ACCOUNT_RECORD_ID, 'NULL') != coalesce(bos.account_record_id,'NULL')
+ or coalesce(bs.TENANT_RECORD_ID, 'NULL') != coalesce(bos.tenant_record_id,'NULL') )
+ and coalesce(bs.TYPE, 'NULL') = 'SUBSCRIPTION_BUNDLE'
+
+-- I1b
+select *
+from bos
+ left outer join blocking_states bs on bs.record_id = bos.blocking_state_record_id
+where coalesce(bs.RECORD_ID, 'NULL') != coalesce(bos.blocking_state_record_id,'NULL')
+ or coalesce(bs.BLOCKABLE_ID, 'NULL') != coalesce(bos.bundle_id,'NULL')
+ or coalesce(bs.TYPE, 'NULL') != 'SUBSCRIPTION_BUNDLE'
+ or coalesce(bs.STATE, 'NULL') != coalesce(bos.status,'NULL')
+ or coalesce(bs.CREATED_DATE, 'NULL') != coalesce(bos.created_date,'NULL')
+ or coalesce(bs.CREATED_DATE, 'NULL') != coalesce(bos.start_date,'NULL')
+ -- or coalesce(bs.ACCOUNT_RECORD_ID, 'NULL') != coalesce(bos.account_record_id,'NULL')
+ or coalesce(bs.TENANT_RECORD_ID, 'NULL') != coalesce(bos.tenant_record_id,'NULL')
+
+-- I2
+select *
+from bos b
+ left outer join accounts a on a.id = b.account_id
+where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
+ or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
+ or coalesce(a.name , '') != coalesce(b.account_name ,'')
+
+-- I3
+select *
+from bos
+ left outer join bundles b on b.id = bos.bundle_id
+where coalesce(bos.bundle_external_key )!= coalesce(b.external_key ,'')
+
+-- I4
+select *
+from bos b
+ join audit_log al on b.blocking_state_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'BLOCKING_STATES'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
*****
-- bst
-select se.plan_name, se.phase_name, bst.next_slug
+-- J1
+select *
from bst
left outer join subscription_events se on bst.subscription_event_record_id = se.record_id
where 1=0
- or se.requested_date != bst.requested_timestamp
- or se.effective_date != bst.next_start_date
- or se.subscription_id!= bst.subscription_id
- or se.phase_name!= bst.next_slug
- or se.price_list_name!= bst.next_price_list
- or se.created_by!= bst.created_by
- or se.created_date!= bst.created_date
- or se.account_record_id!= bst.account_record_id
- -- RI-1966 or se.tenant_record_id!= bst.tenant_record_id
+ or coalesce(se.requested_date,'') != coalesce(bst.requested_timestamp,'')
+ or coalesce(se.effective_date,'') != coalesce(bst.next_start_date,'')
+ or coalesce(se.subscription_id,'') != coalesce(bst.subscription_id,'')
+ or coalesce(se.phase_name,'') != coalesce(bst.next_slug,'')
+ or coalesce(se.price_list_name,'') != coalesce(bst.next_price_list,'')
+ or coalesce(se.created_date,'') != coalesce(bst.created_date,'')
+ or coalesce(se.account_record_id,'') != coalesce(bst.account_record_id,'')
+ or coalesce(se.tenant_record_id,'') != coalesce(bst.tenant_record_id,'')
+-- J2
select *
from bst b
left outer join accounts a on a.id = b.account_id
@@ -620,3 +972,25 @@ where coalesce(a.record_id )!= coalesce(b.account_record_id ,'')
or coalesce(a.id , '') != coalesce(b.account_id ,'')
or coalesce(a.external_key, '') != coalesce(b.account_external_key,'')
or coalesce(a.name , '') != coalesce(b.account_name ,'')
+
+
+
+-- J4
+select *
+from bst b
+ join audit_log al on b.subscription_event_record_id = al.target_record_id and al.change_type = 'INSERT' and table_name = 'SUBSCRIPTION_EVENTS'
+where coalesce(b.created_reason_code, 'NULL') != coalesce(al.reason_code,'NULL')
+ or coalesce(b.created_comments, 'NULL') != coalesce(al.comments,'NULL')
+ or coalesce(b.created_by, '') != coalesce(al.created_by,'')
+
+
+-- bst_fields
+/* table not currently used */
+
+*****
+
+-- bst_tags
+/* table not currently used */
+
+*****
+
