Datanory Trial

select * from member where org_id ='A000007'

--SFA Part

select * from txn_mobile_header where salesman_id ='06aac100e525424784bbb75ebdcaff3a' order by created_date desc
select * from txn_sales_order_header where mobile_hdr_id ='c001301a-eecb-415f-9a25-33aa1066d104'
select * from txn_sales_order_details where ord_hdr_id ='dbb8a787-9393-4e59-ac41-47fa8bf8d043'
select * from txn_return_header where txn_id ='c001301a-eecb-415f-9a25-33aa1066d104'
select * from txn_return_details where return_id ='81c0a8c6-fd7e-4eda-8a81-26b86f61a7cf'
select * from txn_mobile_collection_header where txn_id ='c001301a-eecb-415f-9a25-33aa1066d104'
select * from txn_mobile_collection_detail where col_id ='2b2107b1-1fcb-42ce-ad08-c672473b39f2'

--SQL Integration Part

exec SP_EXP_SQLAcc_Data 'A000007'

--1. Invoice
--Header
select *
from txn_invoice_history_header h
inner join (
select d.invoice_id, sum(d.qty_invoiced) qty_invoiced, sum(d.line_total) line_total
from txn_invoice_history_detail d
group by d.invoice_id
) d on h.invoice_id=d.invoice_id
left join customer c on h.customer_id=c.customer_id
left join mst_customer_contact_address ca on c.customer_id=ca.customer_id
inner join member m on h.member_id=m.member_id and isnull(m.sales_type,'')<>'Credit Sales'
left join txn_sales_order_header soh on h.sales_order_id=soh.ord_hdr_id
--inner join warehouse w on c.
where not exists (select 1 from EXP_SQLACC_TXN_INVOICE hh where h.invoice_no=hh.INV_NO and hh.dist_id='A000007')
and h.invoice_no not like 'IV%'
and h.org_id = 'A000007'
--Detail
select *
from txn_invoice_history_detail d
inner join txn_invoice_history_header h on h.invoice_id=d.invoice_id
inner join customer c on h.customer_id=c.customer_id
inner join member m on h.member_id=m.member_id and isnull(m.sales_type,'')<>'Credit Sales'
inner join product_item p on d.item_id=p.item_id
left join mst_product_uom uom1 on p.oth_uom_id = uom1.product_uom_id
where not exists (select 1 from EXP_SQLACC_TXN_INVDTL hh where h.invoice_no=hh.INV_NO and p.item_code=hh.PRD_CD and hh.dist_id='A000007')
and h.invoice_no not like 'IV%'
and h.org_id = 'A000007'
--group by h.invoice_no, d.sales_order_line

--2. Credit Note
--Header
select *
from txn_credit_note_history_header h
inner join (
select d.credit_note_id, sum(d.line_total) as amount from txn_credit_note_history_detail d
group by d.credit_note_id
) d on h.credit_note_id=d.credit_note_id
inner join customer c on h.customer_id=c.customer_id
left join mst_customer_contact_address ca on c.customer_id=ca.customer_id
inner join member m on h.member_id=m.member_id
where not exists (select 1 from EXP_SQLACC_TXN_NOTEHDR_CP hh where h.credit_note_no=hh.TXN_NO and hh.dist_id='A000007')
and h.org_id = 'A000007'

--Detail
select *
from txn_credit_note_history_detail d
inner join txn_credit_note_history_header h on h.credit_note_id=d.credit_note_id
inner join product_item p on d.item_id=p.item_id
inner join customer c on h.customer_id=c.customer_id
inner join member m on h.member_id=m.member_id
left join mst_product_uom uom1 on d.mprice_uom = uom1.product_uom_id
where not exists (select 1 from EXP_SQLACC_TXN_NOTEPRD_CP hh where h.credit_note_no=hh.TXN_NO and p.item_code=hh.[PRD_CD] and hh.dist_id='A000007')
and h.org_id = 'A000007'
--group by h.credit_note_no, d.sales_order_line

--3. Collection
--Header
select *
from txn_mobile_collection_header h
inner join txn_mobile_header txn on h.txn_id=txn.txn_id
inner join (
select d.col_id
, sum(case when doc_type not like '%Cheque%' then d.doc_amt else 0 end) [TTL_CASH]
, sum(case when doc_type like '%Cheque%' then d.doc_amt else 0 end) [TTL_CHEQUE]
from txn_mobile_collection_detail d
group by d.col_id
) d on h.col_id=d.col_id
left join customer c on txn.customer_id=c.customer_id
left join mst_customer_contact_address ca on c.customer_id=ca.customer_id
left join member m on txn.salesman_id=m.member_id
--inner join warehouse w on c.
where [TTL_CASH] + [TTL_CHEQUE] > 0
and h.status like '%Approved'
and not exists (select 1 from EXP_SQLACC_TXN_COLHDR hh where h.col_no=hh.TXN_NO and hh.dist_id='A000007')
and h.org_id = 'A000007'

--Detail

Create table #Temp (Customer varchar(50), PaymentAmount decimal(18,2), Bank varchar(50), PaymentType varchar(50), Reference varchar(50), PaymentDate varchar(50), JournalNotation varchar(50), PaymentNarration varchar(50), Invoice varchar(50), Type varchar(50), Amount decimal(18,2));
Create table #ColTemp (ColId varchar(50), Customer varchar(50), PaymentAmount decimal(18,2), Bank varchar(50), PaymentType varchar(50), Reference varchar(50), PaymentDate varchar(50), JournalNotation varchar(50), PaymentNarration varchar(50), Invoice varchar(50), Type varchar(50), Amount decimal(18,2));
DECLARE @txn_id varchar(50), @customer_id varchar(50), @col_id varchar(50);

DECLARE vendor_cursor CURSOR FOR
--txn , cust , col
select ch.txn_id,h.customer_id,ch.col_id from txn_mobile_collection_header ch
inner join txn_mobile_header h on ch.txn_id=h.txn_id
where not exists (select 1 from EXP_SQLACC_TXN_COLDTL hh where ch.col_no=hh.TXN_NO and hh.dist_id='A000007')
and not exists (select 1 from EXP_SQLACC_TXN_COLHDR hdr where hdr.TXN_NO=ch.col_no and hdr.dist_id='A000007')
and ch.org_id='A000007'

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @txn_id, @customer_id, @col_id

--print ('@txn_id=' + @txn_id +', @customer_id=' +   @customer_id +', @col_id=' +  @col_id)

WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Temp
exec [sp_di_syspro_get_payment_details] @txn_id,@customer_id,@col_id

insert into #ColTemp
        select @col_id, * from #Temp

truncate table #Temp

FETCH NEXT FROM vendor_cursor   
    INTO @txn_id, @customer_id, @col_id

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

select h.col_no [TXN_NO]
,h.org_id [DIST_CD]
,c.customer_code [CUST_CD]
,m.login_id [SLSMAN_CD]
,case when d.Type='C' then 'C' else isnull(d.PaymentType ,d.Type) end [PAY_TYPE]
,d.JournalNotation [CHEQUE_NO]
,d.Amount [AMT]
,d.PaymentDate [CHEQUE_DT]
,d.Invoice [CHEQUE_STATUS]
,'' [PROCESS_STATUS]
,'' [BANK_CD]
,'' [BANK_BRANCH]
,'' [DB_ACCT]
,d.PaymentDate [CHEQUE_DT2]
,h.org_id [dist_id]
,null [sync_date]
from txn_mobile_collection_header h
inner join #ColTemp d on h.col_id=d.ColId
inner join txn_mobile_header txn on h.txn_id=txn.txn_id
left join customer c on txn.customer_id=c.customer_id
left join mst_customer_contact_address ca on c.customer_id=ca.customer_id
left join member m on txn.salesman_id=m.member_id
where --(d.doc_type = 'Cash' or d.doc_type like '%Cheque%') and
not exists (select 1 from EXP_SQLACC_TXN_COLDTL hh where h.col_no=hh.TXN_NO and h.col_no=hh.[CHEQUE_NO] and hh.dist_id='A000007')
--and exists (select 1 from [EXP_SQLACC_TXN_COLHDR] hdr where hdr.TXN_NO=h.col_no and hdr.dist_id='A000007')
and h.org_id = 'A000007'

drop table #ColTemp
drop table #Temp