Datanory Production » Datanory - GOLDCREST

select * from approval_flow where org_id = 'goldcrest'

select * from member where member_cd = 'evo'

select * from txn_sales_order_header where order_no = 'SOEVO000214-240523'
select * from txn_sales_order_approval_history where order_id = '598596a1-604e-4309-86a6-71eab9d91724'
select * from txn_sales_order_approval_history where created_by = '2750df1e9be7480da51f72dab18f8cd4'

- end shift.
- handover to arif.
- this SO SOEVO000214-240523 not have in sales order approval table

select * from member where member_id='2750df1e9be7480da51f72dab18f8cd4'

select * from txn_sales_order_header where order_no = 'SOEVO000214-240523'
--select * from txn_sales_order_approval_history_at where order_id = '598596a1-604e-4309-86a6-71eab9d91724'
--select * from txn_sales_order_approval_history where order_id = '598596a1-604e-4309-86a6-71eab9d91724'
--select * from txn_sales_order_approval_history where created_by = '2750df1e9be7480da51f72dab18f8cd4'

select * from di_mobile_txn_sales_orders nolock where sales_orders_id='598596a1-604e-4309-86a6-71eab9d91724'

Create table #approvalTable (Remark varchar(500), SalesOrderId varchar(50));
        Insert Into #approvalTable (Remark, SalesOrderId) exec sp_di_mobile_upload_integration_sales_order_approval '6a2d69c0-cf63-46f2-9be0-364d520fa97c'

--Insert Into txn_sales_order_approval_history ([id], [order_id], [member_id], [status], [status_date], [remark], [approval_flow_id], [approval_type],[approval_sequence], [created_date], [created_by], [updated_date], [updated_by], [org_id], [tenant_id],
-- approval_mode)

Select newid() as [id], 
        so.sales_orders_id as [order_id],
        afa.member_id as [member_id],
        Case when so.status = 'Cancelled' then so.status else (Case When 'Credit Sales' = 'Van Sales' Then 'Auto-Approved' Else Case When approvalTbl.Remark <> '' Then 'Pending' Else 'Auto-Approved' End End)end
         as [status], 
        '2024-05-23 14:51:47' as [status_date], 
        IsNull(approvalTbl.Remark, '') as [remark],
        afa.approval_flow_id as [approval_flow_id],
        'Y' as [approval_type],
        afa.level as [approval_sequence],
        '2024-05-23 14:51:47' as [created_date],
        '2750df1e9be7480da51f72dab18f8cd4' as [created_by],
        '2024-05-23 16:20:10.860' as [updated_date],
        '2750df1e9be7480da51f72dab18f8cd4' as [updated_by],
        so.org_id as [org_id],
        so.tenant_id as [tenant_id], 
        approval_mode
        from (select distinct sales_orders_id,transaction_id, org_id, tenant_id, process_id, status from di_mobile_txn_sales_orders(nolock)) so
        Inner Join di_mobile_txn_salesman_route (nolock) sr On sr.transaction_id = so.transaction_id 
        Left Join (Select customer_id, branch_id from customer c union Select new_customer_id, branch_id from Txn_new_customer where approval_status is null) c On c.customer_id = sr.customer_id
        inner join (
            Select afa.* from approval_flow_approver afa 
            where exists (Select 1 from approval_flow af Where af.activity = 'Sales Order'
            and convert(date,getdate()) between af.start_date and af.end_date and afa.approval_flow_id=af.approval_flow_id and af.status = 'Active') and afa.status = 'Active'
        ) afa On exists (select 1 from approval_flow_branch afb where afb.branch_id=c.branch_id and afa.approval_flow_id=afb.approval_flow_id and afb.status = 'Active')
        Left Join (SELECT SalesOrderId, 
        STUFF((SELECT DISTINCT ',' + Remark FROM #approvalTable Where SalesOrderId = a.SalesOrderId FOR XML PATH ('')) , 1, 1, '') AS Remark
        FROM #approvalTable AS a
        GROUP BY SalesOrderId) approvalTbl on approvalTbl.SalesOrderId COLLATE DATABASE_DEFAULT  = so.sales_orders_id  COLLATE DATABASE_DEFAULT 
        Where cast(so.process_id as varchar(50)) = '6a2d69c0-cf63-46f2-9be0-364d520fa97c'
        and not exists (Select 1 from txn_sales_order_approval_history soah Where soah.order_id = so.sales_orders_id)
        order by so.sales_orders_id, afa.level

drop table #approvalTable