Datanory Production » Datanory - DAILYFRESH

hi Afiq need your help on this ticket.
This is the finding :
this logic is stored in the sp_sync_download_user_sales_info SP as I checked , total sales based on monthly as I check I checked from i of june to 29th june (before 7:35 am)
This is my finding.
based on my understandinf from the SP
Invoice sales= total invoice- CreditNote

Need your assistance on this matter. Thank you

select * from member where member_cd= 'PRK06'

---------
 -- invoice Header

SELECT invoice_status, created_date, updated_date, *
FROM txn_invoice_history_header
WHERE updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
AND invoice_status <> 'Cancelled'
AND invoice_status <> 'Rejected'
AND created_date BETWEEN '2024-06-01' AND '2024-06-29 07:35:59'

SELECT
SUM AS total_currency_value
FROM
txn_invoice_history_header
WHERE
updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
AND invoice_status <> 'Cancelled'
AND invoice_status <> 'Rejected'
AND created_date BETWEEN '2024-06-01' AND '2024-06-29 07:35:59'
-- 39817.10
------------------------

-- Credit_note

select * from txn_credit_note_history_header
WHERE updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
and status <> 'Cancelled' and status <> 'rejected'
AND created_date BETWEEN '2024-06-01' AND '2024-06-29 07:35:59'
-- total Credit note 16,569.89

-- Azhar
declare @dt datetime ='2024-06-28 06:34:00'

CREATE TABLE #temp_table(
action [varchar](50) NULL, -- For 'Insert/Update'
id [varchar](50) NULL, -- For combined 'a.member_id', year, and month
userid [varchar](50) NULL, -- For 'a.member_id'
mtdSalesTarget [decimal](18,2) NULL, -- For 'isnull(tgt.total_target,0.00)'
mtdSales [decimal](18,2) NULL, -- For 'isnull(sales.total_sales,0.00)'
percentageOfAchievement [decimal](18,2) NULL, -- For the calculated percentage
mtdYear [varchar](4) NULL, -- For the year extracted from @dt
mtdMonth [varchar](2) NULL -- For the month extracted from @dt
)

BEGIN

insert into #temp_table
            select 
        'Insert/Update' as action, 
        a.member_id+'-'+cast(year(@dt) as varchar)+right('0'+ cast(month(@dt)as varchar),2) as id, 
        a.member_id as userid, 
        isnull(tgt.total_target,0.00) as mtdSalesTarget,isnull(sales.total_sales,0.00) - isnull(credit_note.total_credit_note, 0.00) as mtdSales,
        case 
            when isnull(tgt.total_target,0.00) = 0 then 0 
            else isnull(sales.total_sales,0.00) / isnull(tgt.total_target,0.00) end * 100 
        as percentageOfAchievement, 
        cast(year(@dt) as varchar) as mtdYear,
        right('0'+ cast(month(@dt)as varchar),2) as mtdMonth
    from 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY member_id) AS TotalRowNumber, member_id FROM 
        (
            SELECT member_id
            FROM  
            member m inner join org o on m.org_id = o.org_id
            where m.status ='Active'
            and m.org_id = 'A000003'
            and member_id = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
            UNION
            select m.member_id FROM on_behalf ob 
            inner join member m on ob.member_id = m.member_id
            inner join on_behalf_access oba on ob.on_behalf_id = oba.on_behalf_id
            inner join org o on m.org_id = o.org_id
            where oba.member_id = '887F2022-E97C-4EB9-BACF-8777AEED8A17' and 
            (@dt between ob.start_date and ob.end_date) and
            m.status ='Active' and m.org_id = 'A000003'
        ) b

) a
    left join (
        select ih.member_id, sum(ih.currency_value) as total_sales from txn_invoice_history_header ih
        where 
        year(invoice_date) = year(@dt) and month(invoice_date) = month(@dt)
        and invoice_status <> 'Cancelled' and invoice_status <> 'Rejected'
        and invoice_date <= @dt
        group by ih.member_id
    ) sales on a.member_id = sales.member_id
    left join (
        select ch.member_id, sum(isnull(cast(ch.currency_value AS decimal(14,2)), 0.00)) as total_credit_note from txn_credit_note_history_header ch
        where 
        year(credit_note_date) = year(@dt) and month(credit_note_date) = month(@dt)
        and status <> 'Cancelled' and status <> 'Rejected'
        and credit_note_date <= @dt
        group by ch.member_id
    ) credit_note on a.member_id = credit_note.member_id
    left join (
        select 
            user_id,
            sum(
                case 
                    when month(@dt) = 1 then s.jan
                    when month(@dt) = 2 then s.feb
                    when month(@dt) = 3 then s.mar
                    when month(@dt) = 4 then s.apr
                    when month(@dt) = 5 then s.may
                    when month(@dt) = 6 then s.jun
                    when month(@dt) = 7 then s.jul
                    when month(@dt) = 8 then s.aug
                    when month(@dt) = 9 then s.sep
                    when month(@dt) = 10 then s.oct
                    when month(@dt) = 11 then s.nov
                    when month(@dt) = 12 then s.[dec]
                end
            ) as total_target
        from mst_sales_target s
        where s.year  = year(@dt) 
        --and (user_id = '887F2022-E97C-4EB9-BACF-8777AEED8A17')
        group by user_id
    ) tgt on tgt.user_id = a.member_id

END

select * from #temp_table

drop table #temp_table

As i check with Choung yu user Download once only
select created_date,* from sync_mobile_activity_log a where token_id= '6A55D676-F1F3-4374-9B23-DC3F2F333FCF'
Already Request user to upload mobile database fir further checking

-- My findings
- Hi arif as I check from our side I notice that the invoice and credit note has been cancelled on that day
-- able to identify discrepency based on user screenshot that has been sent
-- as I asked technical team they claim this is the bug same as goldcrest based on the ticket https://app.clickup.com/t/9018039480/TK-652
-- based on the explanation : the cancel invoice and cancelled cn did not calculate back to dashboard
-- for this case the cancelled invoice and CN already calculated on the dashboard
-- therefore i need your assistance on this ticket to verify on my finding

select * from member where member_id= '887F2022-E97C-4EB9-BACF-8777AEED8A17'
-- invoice
SELECT invoice_status, updated_date,created_date,* 
FROM txn_invoice_history_header 
WHERE updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17' 
and invoice_no like '%240628%'
and invoice_status= 'cancelled'
AND updated_date BETWEEN CAST('2024-06-28 00:00:00' AS datetime) AND CAST('2024-06-29 00:00:00' AS datetime);
--INPRK06002514-240628= 272.98 updated_date= 2024-06-28 19:49:28.740
--INPRK06002504-240628= 142.14 updated_date= 2024-06-28 19:48:33.517
--INPRK06002506-240628 =96.30  Updated_date 2024-06-28 13:58:13.000
select 
272.98+
142.14+
96.30
-- total = 511.42
--CreditNote
select created_date, status,updated_by,updated_date,* from txn_credit_note_history_header where 
 updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17' 
 and status= 'cancelled'
AND updated_date BETWEEN CAST('2024-06-28 00:00:00' AS datetime) AND CAST('2024-06-29 00:00:00' AS datetime);
--CNPRK06001722-240628-17.40--    updated_date =2024-06-28 19:48:39.340
--CNPRK06001726-240628-63.72--    updated_date =2024-06-28 19:49:35.103
--CNPRK06001724-240628-45.34--    updated_date =2024-06-28 19:49:35.103

select 17.40+
63.72+
45.34-- total credit note--> 126.46
--total discrepency
select 511.42- 126.46
--------------------------------------------------------------------------------
-- User sent screenshot on 28/6 7:34pm the invoice sales Dashboard= 29277.43
-- User sent screenshot on 29/6 7:34pm the invoice sales Dashboard= 28892.47
-- discrepency
select 29277.43- 28892.47

-- there is no debit note
select status,* from txn_mobile_collection_debit_note where
updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
and status= 'cancelled'
AND updated_date BETWEEN CAST AND CAST;
--CNPRK06001722-240628-17.40
--CNPRK06001726-240628-63.72
--CNPRK06001724-240628-45.34
select * from txn_sales_order_header
where
updated_by = '887F2022-E97C-4EB9-BACF-8777AEED8A17'
and status= 'cancelled'
AND updated_date BETWEEN CAST AND CAST;

--SOPRK06002514-240628
--SOPRK06002504-240628
--SOPRK06002506-240628
--total cancelled sales order:
select 272.98+
142.14+
96.30

Azhar to follow up the fixes

Second Case Testing For Daily fresh

Used Goldcrest org to trail enviroment since the trail Daily fresh is not completely setup due to time constraint

Daily fresh:

Used goldcrest Org to verify:

Org_id= Goldcrest

User WWV

Customer: 3021/001

Created Credit note:

5073--> inital sale

Good return 3 ctn 250-BC01= 159.00

5073-159 = 4914

Went to portal convert the good return (GRWWV000004-240716)--> credit note CNGC000096-240716

Testing_canclelled_credit_note.png

Downloaded again

in the dashboard invoice sale become 4914

then I cancel int the web

The dashboard updated back to original amount - 5073

This before upload txn and good return

after the good return convert to Credit note

After the credit note is cancelled.