Datanory Production » Datanory - GOLDCREST

select * from member where member_cd = 'wwq'
select * from customer where customer_id in ('295E20FA57A14CDBA1572993DC36AA18', '295E20FA57A14CDBA1572993DC36AA18', 'B2332B5B15E64E0EA29FAF44C6AEDA73')

--col_no in ('CCWWQ000438-240424', 'CCWWQ000439-240425', 'CCWWQ000440-240425')
--col_id in ('49380340-61ae-44cd-a49b-d49a4efe1dd6', '4ab35302-fa52-4a2c-a10a-434b4b8a343a', '56f2cb58-7a72-4d69-a148-a6b96399e0e2')
--invoice_id in ('f28a62f2-e6e5-4cf0-aab5-497345adcd21', 'c70753bd-ba89-4851-b6ee-0e67cb995a23', '763dc676-950f-461f-9bc6-7bd8538a46ae')

select * from txn_mobile_collection_header where org_id = 'goldcrest' and col_no = 'CCWWQ000438-240424'
--sync_date NULL
select * from txn_mobile_collection_detail where org_id = 'goldcrest' and col_id = '49380340-61ae-44cd-a49b-d49a4efe1dd6'
select * from txn_mobile_collection_invoice where col_id = '49380340-61ae-44cd-a49b-d49a4efe1dd6'
select * from txn_invoice_history_header where invoice_id = 'f28a62f2-e6e5-4cf0-aab5-497345adcd21'
--sync_date NULL, invoice_status Cancelled

select * from txn_mobile_collection_header where org_id = 'goldcrest' and col_no = 'CCWWQ000439-240425'
--sync_date NULL, status Cancelled
select * from txn_mobile_collection_invoice where col_id = '56f2cb58-7a72-4d69-a148-a6b96399e0e2'
select * from txn_invoice_history_header where invoice_id = '763dc676-950f-461f-9bc6-7bd8538a46ae'

select * from txn_mobile_collection_header where org_id = 'goldcrest' and col_no = 'CCWWQ000440-240425'
--sync_date NULL
select * from txn_mobile_collection_detail where org_id = 'goldcrest' and col_id = '4ab35302-fa52-4a2c-a10a-434b4b8a343a'
select * from txn_mobile_collection_invoice where col_id = '4ab35302-fa52-4a2c-a10a-434b4b8a343a'
select * from txn_invoice_history_header where invoice_id = 'c70753bd-ba89-4851-b6ee-0e67cb995a23'

--start 26/4 integration include cancelled txn

SELECT

b.customer_code,

b.customer_name,

*

FROM

txn_invoice_history_header a

INNER JOIN

customer b ON a.customer_id = b.customer_id

WHERE

a.org_id ='goldcrest'  

--AND (customer_code = '301k/384' or customer_code = '301n/052' or customer_code = '301S/064')

AND (invoice_id='c70753bd-ba89-4851-b6ee-0e67cb995a23' OR invoice_id='763dc676-950f-461f-9bc6-7bd8538a46ae' OR invoice_id='f28a62f2-e6e5-4cf0-aab5-497345adcd21')

order by a.created_date desc

select * from customer_at where customer_code= '302P/110' order by at_date desc

select * from customer_at where customer_code= '302S/078' order by at_date desc

AsI checked in the customer_at table there is no code has been changed in the datanory side
have to check in the autocount either the customer code has been changed or not

-- three of them are not in the autocount
select * from ARPayment where DocNo like 'CCWWQ000438%'

select * from ARPayment where DocNo like 'CCWWQ000439-240425%'

select * from ARPayment where DocNo like 'CCWWQ000440%'

this invoice has not in the autocount yet

patching has been done for temporary
has to verify with customer.
have to check tomorrow in the autocount

-- script
update txn_mobile_collection_detail set col_id = '4ab35302-fa52-4a2c-a10a-434b4b8a343a' where col_dtl_id = '5e1e015f-0915-4b6e-8da4-35919dc719a3'

SELECT * FROM txn_mobile_collection_header WHERE COL_NO LIKE 'CCWWQ000438-240424'
select * from txn_mobile_collection_invoice where col_id='49380340-61ae-44cd-a49b-d49a4efe1dd6'
select invoice_status,* from txn_invoice_history_header where invoice_id='f28a62f2-e6e5-4cf0-aab5-497345adcd21'
----------------------------------------------------------------------------------------------------------------
select * from txn_mobile_collection_header where org_id = 'goldcrest' and
col_no in ('CCWWQ000438-240424', 'CCWWQ000439-240425', 'CCWWQ000440-240425')
select * from txn_mobile_collection_header where org_id = 'goldcrest' and
col_no in ( 'CCWWQ000439-240425', 'CCWWQ000440-240425')

select * from txn_mobile_collection_detail where col_id in (
'4ab35302-fa52-4a2c-a10a-434b4b8a343a' )

)
select
---------------------------------------------------------------------------------------------------------------------

select * from txn_mobile_collection_detail where col_id in (
'56f2cb58-7a72-4d69-a148-a6b96399e0e2' --CCWWQ000439-240425 cancelled one
)
-- patching has to be done
-- THERE IS COLLECTION IN THE DATANORY CCWWQ000439-240425 to CCWWQ000440-240425 with the transaction number 0818baab-9907-4093-a8bf-6064290f9c67

SELECT *
FROM txn_mobile_collection_header h
WHERE NOT EXISTS (
SELECT *
FROM txn_mobile_collection_detail d
WHERE d.col_id = h.col_id
)
AND h.org_id= 'goldcrest';
--CCWWQ000440-240425

`--backup
select * into DMS_EcoSystem_backup_table.dbo.txn_mobile_collection_header_06052024
from DMS_EcoSystem.dbo.txn_mobile_collection_header

select * into DMS_EcoSystem_backup_table.dbo.txn_mobile_collection_detail_06052024
from DMS_EcoSystem.dbo.txn_mobile_collection_detail

SELECT * FROM txn_mobile_collection_header WHERE COL_NO LIKE 'CCWWQ000438-240424'
select * from txn_mobile_collection_invoice where col_id='49380340-61ae-44cd-a49b-d49a4efe1dd6'
select invoice_status,* from txn_invoice_history_header_at where invoice_id='f28a62f2-e6e5-4cf0-aab5-497345adcd21' order by at_date desc
--a80bc13e-08d1-41f6-832d-3df3bc9bb3c4
select * from txn_mobile_check_in where txn_id= 'a80bc13e-08d1-41f6-832d-3df3bc9bb3c4'

select * from txn_mobile_check_out where txn_id= 'a80bc13e-08d1-41f6-832d-3df3bc9bb3c4'

select * from txn_mobile_collection_invoice_at where col_id= '49380340-61ae-44cd-a49b-d49a4efe1dd6'
-- checkin date = 2024-04-24 13:16:52.000
-- created date invoice- 2024-04-24 13:21:25.000
-- INWWQ000410-240424-- cancelled date -- 2024-04-24 15:19:46.057
-- checkout date-- 2024-04-24 13:21:42.000

https://app.clickup.com/t/86epa86v1

https://app.clickup.com/t/86ep9na7q

https://app.clickup.com/t/86ep9na7q--> this issue has been solved

https://app.clickup.com/t/86epa86v1--> due date May 13

As I checked with shu ern this is already fixed and waiting for jann advise on production release.

As I checked with shu ern the next release will be 30/5/2024