Datanory Production

Insert Into txn_van_goods_request_approval_history([id], [van_goods_request_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])

Select newid() as [id],
vgr.van_goods_request_hdr_id transaction_id,
afa.member_id as [member_id],
'Pending' as [status],
GetDate() as [status_date],
vgr.remarks [remark],
afa.approval_flow_id as [approval_flow_id],
'Y' as [approval_type],
afa.level as [approval_sequence],
GetDate() as [created_date],
vgr.salesman_id as [created_by],
GetDate() as [updated_date],
vgr.salesman_id as [updated_by],
vgr.org_id as [org_id],
vgr.tenant_id as [tenant_id]

--select vgr.van_goods_request_hdr_id
    from (select distinct van_goods_request_hdr_id, '' as remarks, salesman_id, org_id, tenant_id, process_id from di_txn_mobile_van_goods_request_header where van_goods_request_no like '%trg%') vgr
    Left Join member m On m.member_id = vgr.salesman_id
    left join member_branch mb on m.member_id = mb.member_id
    inner join (
        Select afa.* from approval_flow_approver afa 
        where exists (Select 1 from approval_flow af Where af.activity = 'Van Goods Request'
        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=mb.branch_id and afa.approval_flow_id=afb.approval_flow_id and afb.status = 'Active')
    Where vgr.process_id in (select  process_id from di_txn_mobile_van_goods_request_header where van_goods_request_no like '%trg%')
    and not exists (Select 1 from txn_van_goods_transfer_approval_history rah Where rah.transfer_hdr_id = vgr.van_goods_request_hdr_id)
    order by vgr.van_goods_request_hdr_id, afa.level

--005aa0a9-4048-4017-aba9-45689c87c62a
--06e7a0db-cde0-4e7d-9cfc-61df0e3cc8a9
--261474d0-c474-4826-8c7b-b5835504b618
--41a18005-a335-4a20-a2fe-51eb5fa876a8
--9c411e27-8a10-43db-98d5-69120c13cdb2
--a9f7c2c0-3990-41f6-9c8b-890c8595704f
--cbca1432-ff90-4cc7-b7b0-07c15cd5765d
--d37b94ac-2d6d-4d29-9231-18d1556e53ea
--d6113eb7-89f0-46c3-aa1c-5a259ddb7c6e
--d95351af-2efd-42d1-8579-715874cccab4

select top 10 * from di_txn_mobile_van_goods_request_header where van_goods_request_no=''

select * from approval_flow where org_id='A000003' and information_desc like '%trg%'

select * from member where member_cd='admin' and org_id='A000003'

Select b.member_cd, afa.* from approval_flow_approver afa 
    inner join member b on afa.member_id=b.member_id
        where exists (Select 1 from approval_flow af Where af.activity = 'Van Goods Request'
        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' and afa.org_id='a000003' --and member_id='6A00BFD2-0125-47AA-BA3F-283E4BA0AD9D'

select * from txn_mobile_van_goods_request_header  where van_goods_request_no='VQTRG03000001-240309'
 select * from txn_van_goods_request_approval_history  where van_goods_request_id='06e7a0db-cde0-4e7d-9cfc-61df0e3cc8a9'

select a.van_goods_request_no, a.updated_date [updated header1], a.created_date [created Header 2], b.created_date [approval 1], b.status_date[approval 2], b.updated_date [approval 3] 
 from txn_mobile_van_goods_request_header a
 inner join txn_van_goods_request_approval_history b on a.van_goods_request_hdr_id=b.van_goods_request_id
 where van_goods_request_no like '%trg%'

select a.van_goods_request_no, a.updated_date [updated header1], a.created_date [created Header 2], b.created_date [approval 1], b.status_date[approval 2], b.updated_date [approval 3] 
 from txn_mobile_van_goods_request_header a
 inner join txn_van_goods_request_approval_history b on a.van_goods_request_hdr_id=b.van_goods_request_id
 where van_goods_request_no like '%trg%'

--select * into txn_van_goods_request_approval_history_2240311 from txn_van_goods_request_approval_history

-- select a.van_goods_request_no, a.updated_date [updated header1], a.created_date [created Header 2], b.created_date [approval 1], b.status_date[approval 2], b.updated_date [approval 3]

--update b set b.status_date=a.updated_date, b.created_date = a.updated_date, b.updated_date=a.updated_date
 --from txn_mobile_van_goods_request_header a
 --inner join txn_van_goods_request_approval_history b on a.van_goods_request_hdr_id=b.van_goods_request_id
 --where van_goods_request_no like '%trg%'