Datanory Production » Datanory - GOLDCREST

select * from customer where org_id = 'goldcrest' and customer_code in ('302P/321', '302K/395')
select * from member where org_id = 'goldcrest'

--------------------------------------------Sales Order--------------------------------------------
--Found sales order SOMCJ000041-240319 for customer 302P/321
--and SOMCJ000042-240319 for customer 302K/395
select b.customer_code, * from txn_sales_order_header a
inner join customer b on a.customer_id = b.customer_id
where a.org_id = 'goldcrest'
and a.member_id = '2c9e40b5f5904775b71192b8fd8b71ad'
and a.customer_id in ('2220C09127CD4559894B3E2471AABD05', 'F57C9301EFB64FE69FEF32F9D2F0792B')

--------------------------------------------invoice--------------------------------------------
--Not found invoice no INMCJ000041-240319 and INMCJ000042-240319 in txn_invoice_history_header
select * from txn_invoice_history_header
where org_id = 'goldcrest'
and invoice_no in ('INMCJ000041-240319', 'INMCJ000042-240319')

--Found invoice no INMCJ000041-240319 and INMCJ000042-240319 in txn_invoice_history_header_at
--at_type D
select at_date, at_type, * from txn_invoice_history_header_at
where org_id = 'goldcrest'
and invoice_no = 'INMCJ000041-240319'
select at_date, at_type, * from txn_invoice_history_header_at
where org_id = 'goldcrest'
and invoice_no = 'INMCJ000042-240319'

--------------------------------------------collection--------------------------------------------
--Try to find invoice no with updated_date 2024-03-19
--Found invoice no INMCJ000041-240319 for customer 302P/321 and I297451 for customer 302K/395
select * from txn_invoice_history_header
where org_id = 'goldcrest'
and updated_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and customer_id in ('2220C09127CD4559894B3E2471AABD05', 'F57C9301EFB64FE69FEF32F9D2F0792B')
select * from txn_invoice_history_header_at
where org_id = 'goldcrest'
and updated_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and customer_id = '2220C09127CD4559894B3E2471AABD05'

--Not found customer 302P/321 and 302K/395 collection in txn_mobile_collection_invoice
select c.customer_code, b.customer_id, b.invoice_no, * from txn_mobile_collection_invoice a
inner join txn_invoice_history_header b on a.inv_id = b.invoice_id
inner join customer c on b.customer_id = c.customer_id
where a.org_id = 'goldcrest'
and a.created_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and convert (date, a.created_date) = '2024-03-19'
and c.customer_code in ('302P/321', '302K/395')

--Not found customer 302P/321 and 302K/395 collection in txn_mobile_collection_header
select c.customer_code, * from txn_mobile_collection_header a
inner join txn_mobile_header b on a.txn_id = b.txn_id
inner join customer c on b.customer_id = c.customer_id
where a.org_id = 'goldcrest'
and a.created_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and convert (date, a.created_date) = '2024-03-19'
and c.customer_code in ('302P/321', '302K/395')

--Found collection for invoice no INMCJ000041-240319 and I297451 in txn_mobile_collection_invoice_at
--at_type D
select * from txn_mobile_collection_invoice_at
where org_id = 'goldcrest'
and created_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and inv_id in ('50cb7787-05fa-49cf-b8cf-734b7b286f6c', '5FFE852808984C768ABB8767BF682E9A')

--Found collection for invoice no INMCJ000041-240319 and I297451 in txn_mobile_collection_invoice_at
--at_type D
select * from txn_mobile_collection_header_at
where org_id = 'goldcrest'
and created_by = '2c9e40b5f5904775b71192b8fd8b71ad'
and col_id in ('8bfb479f-2559-406a-9a9f-ad389494cf19', '37ac8832-a76d-4601-8923-0884d883c1a5')