-- and drop the temp tables if object_id(tempdb..#guests) is NOT null drop table #guests if object_id(tempdb..#trinfo) is NOT null drop table #trinfo if object_id(tempdb..#trinfnorm) is NOT null drop table #trinfnorm if object_id(tempdb..#trans) is NOT null drop table #trans if object_id(tempdb..#shtmp) is NOT null drop table #shtmp if object_id(tempdb..#invoices) is NOT null drop table #invoices if object_id(tempdb..#trtmp) is NOT null drop table #trtmp if object_id(tempdb..#ITmp) is NOT null drop table #ITmp if object_id(tempdb..#gcSale) is NOT null drop table #gcSale if object_id(tempdb..#gcTran) is NOT null drop table #gcTran SELECT t.trans_no, t.sale_no, t.invoice_no, t.department, t.category, t.item, t.invent_id, t.quantity, t.init_price, t.disc_pct, t.disc_flat, t.disc_amt, t.tax_amount, t.tax_amt2, t.fee_amount, t.extension, t.special, t.message, t.admissions, t.time_span, t.span_type, t.pr_ctr_1, t.pcsplit_1, t.pr_ctr_2, t.pcsplit_2, t.pr_ctr_3, t.pcsplit_3, t.pr_ctr_4, t.pcsplit_4, t.pr_ctr_5, t.pcsplit_5, t.pr_ctr_6, t.pcsplit_6, t.start_date, t.expires, t.adm_end, t.end_date, t.operator, t.salespoint, t.date_time, t.mastertran, t.meet_locat, t.mktg_code, t.srce_code, t.reserv_no, t.cnclstatus, t.rmt_print into #trans from transact t WHERE 1=1 and t.date_time between 20131030 12:00:00 AM and 20131030 11:59:59 PM select distinct t.sale_no, t.pmt_type1, t.pmt_amt1, t.xchg_rate1, t.foreign1, t.pmt_type2, t.pmt_amt2, t.xchg_rate2, t.foreign2, t.pmt_type3, t.pmt_amt3, t.xchg_rate3, t.foreign3, t.pmt_type4, t.pmt_amt4, t.xchg_rate4, t.foreign4, t.acct_name, t.invoice_no, t.zip, t.operator, t.salespoint, t.date_time, t.exportd_on, t.tax_exempt, t.closeout, t.guest_no, t.mastersale, t.reserv_no, CASE WHEN LEN(t.acct_name)=0 THEN SPACE(40) ELSE isnull(Acct: + rtrim(t.acct_name) + - + a.full_name, SPACE(40)) END as aname, a.full_name, isnull(p.pass_no, 0) as pass_no into #shtmp from #trans x join sale_hdr t on t.sale_no = x.sale_no left outer join accounts a on a.acct_name = t.acct_name left outer join transact r (index = sale_no) on r.sale_no = t.sale_no and r.item=**TRANS** and r.invoice_no>0 left outer join gst_pass p on p.invoice_no = r.invoice_no Create Table #trinfnorm (sale_no Numeric (17,0), trans_no Numeric (17,0), guest_no Numeric (17,0), cDescrip varchar(240), price money, amt_paid money, pass_no Numeric (17,0)) Select ti.* Into #trinfo From #trans T join tr_info ti on ti.trans_no=T.trans_no select distinct guest_no into #trguests from #trinfo where guest_no > 0 Select g.* into #guests from #trguests t join guests g On g.guest_no=t.guest_no drop table #trguests Insert Into #trinfnorm (trans_no, sale_no, guest_no, cDescrip, amt_paid, pass_no) Select tr.trans_no, sale_no, MIN(tr.guest_no) as guest_no, MIN(Pass (+Rtrim(i.Descrip)+) Category +Rtrim(gs.category)+ #+Ltrim(Str(gs.pass_no))), MAX(gs.amt_paid), MIN(gs.pass_no) From #trinfo tr Join gst_pass gs On gs.pass_no=tr.info_num Join items i On gs.Item=i.Item and gs.department=i.department and gs.category=i.category Where tr.guest_no > 0 and (tr.info_type=20 Or tr.info_type=30) Group by tr.trans_no, sale_no Insert Into #trinfnorm (trans_no, sale_no, guest_no, cDescrip, price, amt_paid) Select DISTINCT tr.trans_no, sale_no, tr.guest_no, Sched (+Rtrim(i.Descrip)+) Category +Rtrim(bs.category)+ with +Rtrim(resource1), bs.price, bs.amt_paid From #trinfo tr Left Join b_sched bs On bs.booking_id=tr.info_num Left Join items i On bs.Item=i.Item and bs.department=i.department and bs.category=i.category Where tr.info_type=40 and tr.trans_no not in (select trans_no from #trinfnorm) Insert Into #trinfnorm (trans_no, sale_no, guest_no, cDescrip) Select DISTINCT tr.trans_no, sale_no, tr.guest_no, Rental (type +Str(r.rentaltype)+ level +Str(r.rent_level)+ size +Str(r.equip_size)+ DIN +Str(r.din)+ Binding +Rtrim(r.binding)+) From #trinfo tr Left Join r_entals r On r.track_no=tr.info_num And r.trans_no=tr.trans_no Where tr.info_type=50 and tr.trans_no not in (select trans_no from #trinfnorm) Insert Into #trinfnorm (trans_no, sale_no, guest_no, cDescrip) Select tr.trans_no, tr.sale_no, tr.guest_no, cast(count(r.trans_no) as char(3))+ slots From #trinfo tr Left Join res_schd r On r.trans_no=tr.trans_no And r.trans_no=tr.trans_no Where tr.info_type=60 and tr.trans_no not in (select trans_no from #trinfnorm) group by tr.trans_no, tr.sale_no, tr.guest_no Insert Into #trinfnorm (trans_no, sale_no, guest_no) Select tr.trans_no, tr.sale_no, tr.guest_no From #trinfo tr Where tr.info_type=70 and tr.guest_no > 0 and tr.trans_no not in (select trans_no from #trinfnorm) group by tr.trans_no, tr.sale_no, tr.guest_no Insert Into #trinfnorm (trans_no, sale_no, guest_no) Select tr.trans_no, tr.sale_no, tr.guest_no From #trinfo tr Where tr.info_type=25 and tr.guest_no > 0 and tr.trans_no not in (select trans_no from #trinfnorm) group by tr.trans_no, tr.sale_no, tr.guest_no Insert Into #trinfnorm (trans_no, sale_no, guest_no) Select tr.trans_no, sale_no, MIN(tr.guest_no) as guest_no From #trinfo tr Where tr.info_type=10 and tr.guest_no > 0 and tr.trans_no not in (select trans_no from #trinfnorm) Group by tr.trans_no, sale_no Select * Into #invoices From invoices i Where i.invoice_no In (Select T.invoice_no From #Trans T) -- Now Return the Cursor(s) -- result Select 0 As Transact, * From #Trans -- result1 Select 0 As guests, * From #guests Order By trans_no, guest_no -- result2 Select 0 As shtmp, * From #shtmp Select distinct tr.*, tr.date_time as trans_dt, ti.guest_no, Isnull(Rtrim(g.first_name)+Space(1)+g.last_name, Guest Record Not Found!) As GName, CASE WHEN tr.invoice_no = 0 or a.acct_name is null or LEN(a.acct_name)=0 THEN SPACE(40) ELSE Acct: + rtrim(a.acct_name) + - + a.full_name END As AName, isnull(ti.pass_no, 0) as pass_no, cast(0 as money) as collected, cast(isnull(t70.info_num, 0) as numeric(17,0)) as access_no Into #TRTmp From #Trans tr left outer Join #invoices i On i.invoice_no = tr.invoice_no left outer join accounts a on a.acct_name = i.account left outer join #trinfnorm ti on ti.trans_no=tr.trans_no Left outer Join #guests g On g.guest_no = ti.guest_no left outer join tr_info t70 on t70.pri_key = (select top 1 pri_key from tr_info where trans_no=tr.trans_no and info_type=70 and (guest_no=0 or guest_no = ti.guest_no)) -- result3 Select 0 As TRTmp, * From #TRTmp Select Distinct i.department, i.category, i.Item, i.Descrip, i.prn_tkt1, i.stockt1, i.prn_tkt2, i.stockt2, i.prn_vouch1, i.stockv1, i.prn_vouch2, i.stockv2 Into #itmp From items i, #TRTmp T Where i.department = T.department And i.category = T.category And i.Item = T.Item -- result4 Select 0 As itmp, * From #itmp -- result5 (optional) Select distinct 0 as actmp , CASE WHEN extension < 0 THEN 1 ELSE 0 END As AR_AP , isnull(CASE when charindex(*GUESTS*,T.AName)>0 or charindex(*DEBIT*,T.AName)>0 or charindex(*TABLES*,T.AName)>0 or charindex(*RESRVATN*,T.AName)>0 then case when len(i.descrip1)>0 then (CASE when charindex(*GUESTS*,T.AName)>0 then *Guest: else (CASE when charindex(*DEBIT*,T.AName)>0 then *Debit: else (CASE when charindex(*TABLES*,T.AName)>0 then *Table: else *Resrv: end) end) end) + i.descrip1 else T.AName end ELSE case when len(a.full_name)>0 then a.full_name else T.AName end END, SPACE(40)) As AName, t.sale_no, T.department,T.category,T.Item,T.Message,T.quantity, it.Descrip, Acct: + rtrim(a.acct_name) + - + a.full_name As Name, extension As amount, t.invoice_no, t.trans_no from #TRTmp T left outer Join #invoices i On i.invoice_no = T.invoice_no left outer join accounts a on a.acct_name = i.account left outer Join items it On T.department = it.department And T.category = it.category And T.Item = it.Item left outer join #shtmp s On s.sale_no = t.sale_no where T.invoice_no>0 order By 1,2,t.invoice_no -- result6 (optional) SELECT 0 as items, i.department, i.category, i.item, i.descrip, i.pcsplit_1, i.pcsplit_2, i.pcsplit_3, i.pcsplit_4, i.pcsplit_5, i.pcsplit_6 from items i where i.department+i.category+ i.item in (SELECT department + category +item from #trtmp) -- result7 (optional) Select 0 as sstmp, Special, case when sp.descrip is null then Custom Special else sp.Descrip end As sdesc, trt.department, trt.category, trt.Item, it.Descrip As idesc, Message, date_time, quantity, disc_amt, case when sp.disc_pct is null then - - - when sp.disc_pct > 0 then str(sp.disc_pct) + % else $+ltrim(convert(varchar(10),sp.disc_flat)) END As indamt from #TRTmp trt left outer join specials sp on trt.special = sp.name left outer join items it on trt.department = it.department And trt.category = it.category And trt.Item = it.Item where len(trt.Special)>0 order by 3, 4, 5, 1 -- result8 (optional) Select 0 as udsstmp, specials.user_code, Special, specials.Descrip As sdesc, trt.department, trt.category, trt.Item, items.Descrip As idesc, Message, date_time, quantity, disc_amt, extension, CASE when specials.disc_pct > 0 then str(specials.disc_pct) + % ELSE $+ltrim(convert(varchar(10),specials.disc_flat)) END As indamt from #TRTmp trt join specials on trt.Special = specials.Name And len(trt.Special) >0 And len(specials.user_code)>0 join items on items.department = trt.department And items.category = trt.category And items.Item = trt.Item where (disc_amt0 Or len(Special)>0) And len(specials.user_code)>0 order by specials.user_code, Special, trt.department, trt.category, trt.Item -- and drop the temp tables if object_id(tempdb..#guests) is NOT null drop table #guests if object_id(tempdb..#trinfo) is NOT null drop table #trinfo if object_id(tempdb..#trinfnorm) is NOT null drop table #trinfnorm if object_id(tempdb..#trans) is NOT null drop table #trans if object_id(tempdb..#shtmp) is NOT null drop table #shtmp if object_id(tempdb..#invoices) is NOT null drop table #invoices if object_id(tempdb..#trtmp) is NOT null drop table #trtmp if object_id(tempdb..#ITmp) is NOT null drop table #ITmp if object_id(tempdb..#gcSale) is NOT null drop table #gcSale if object_id(tempdb..#gcTran) is NOT null drop table #gcTran
Posted on: Thu, 31 Oct 2013 18:41:39 +0000
Trending Topics
Recently Viewed Topics
© 2015