UFN_DOCUMENT_GETSALESDOCUMENTSTOPRINT
Returns sales document to print and reprint for an order.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SALESORDERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_DOCUMENT_GETSALESDOCUMENTSTOPRINT(@SALESORDERID uniqueidentifier)
returns @SALESDOCUMENTSTOPRINT table
(
TYPE nvarchar(100),
TYPECODE tinyint,
CREDITCARDPAYMENTMETHODDETAILID uniqueidentifier,
CREDITID uniqueidentifier,
CREDITPAYMENTID uniqueidentifier,
PRINTED bit,
TOPRINT bit
)
as begin
declare @SALESMETHODTYPECODE tinyint
declare @CURRENCYSYMBOL nvarchar(5)
select @SALESMETHODTYPECODE = SALESMETHODTYPECODE from dbo.SALESORDER where ID = @SALESORDERID
select top 1 @CURRENCYSYMBOL = CURRENCYSYMBOL from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1
if @CURRENCYSYMBOL is null
set @CURRENCYSYMBOL = '$'
if @SALESMETHODTYPECODE = 3 begin
insert into @SALESDOCUMENTSTOPRINT
--already printed documents
select distinct
PRINTINGHISTORY.TYPE,
PRINTINGHISTORY.TYPECODE,
case when TYPECODE = 1 then null else PRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID end,
PRINTINGHISTORY.CREDITID,
PRINTINGHISTORY.CREDITPAYMENTID,
1 PRINTED,
0 TOPRINT
from dbo.UFN_DOCUMENT_GETPRINTINGHISTORY(@SALESORDERID) PRINTINGHISTORY
where PRINTINGHISTORY.TYPECODE in (1,2,5,6) -- (Itemized Receipt, Credit Card Receipt, Refund Itemized Receipt, Refund Credit Card Receipt
union all
--credit card receipts that should print based on sales method but have not been printed
select distinct
DOCUMENT.TYPE + ': ' + @CURRENCYSYMBOL +
cast(CREDITCARDPAYMENT.AMOUNT as nvarchar) + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) as TYPE,
DOCUMENT.TYPECODE,
CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
1 TOPRINT
from
dbo.SALESORDERPAYMENT CREDITCARDPAYMENT
inner join
dbo.REVENUEPAYMENTMETHOD CREDITCARDREVENUEPAYMENTMETHOD on CREDITCARDPAYMENT.PAYMENTID = CREDITCARDREVENUEPAYMENTMETHOD.REVENUEID and CREDITCARDREVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDREVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on CREDITCARDPAYMENTMETHODDETAIL.ID = DOCUMENTPRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID and DOCUMENTPRINTINGHISTORY.TYPECODE = 2
outer apply
dbo.DOCUMENT
where
CREDITCARDPAYMENT.SALESORDERID = @SALESORDERID and
DOCUMENT.PRINTFORGROUPSALES = 1 and
DOCUMENT.TYPECODE = 2 and -- Credit Card Receipt
DOCUMENTPRINTINGHISTORY.ID is null
union all
--credit card receipts that exist, but are not set up to print based on sales method
select distinct
DOCUMENT.TYPE + ': ' + @CURRENCYSYMBOL +
cast(CREDITCARDPAYMENT.AMOUNT as nvarchar) + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) as TYPE,
DOCUMENT.TYPECODE,
CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
0 TOPRINT
from
dbo.SALESORDERPAYMENT CREDITCARDPAYMENT
inner join
dbo.REVENUEPAYMENTMETHOD CREDITCARDREVENUEPAYMENTMETHOD on CREDITCARDREVENUEPAYMENTMETHOD.REVENUEID = CREDITCARDPAYMENT.PAYMENTID and CREDITCARDREVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDREVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on CREDITCARDPAYMENTMETHODDETAIL.ID = DOCUMENTPRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID and DOCUMENTPRINTINGHISTORY.TYPECODE = 2
outer apply
dbo.DOCUMENT
where
CREDITCARDPAYMENT.SALESORDERID = @SALESORDERID and
DOCUMENT.TYPECODE = 2 and -- Credit Card Receipt
DOCUMENTPRINTINGHISTORY.ID is null and
not exists
(
select DOCUMENT.ID
from dbo.DOCUMENT
where DOCUMENT.TYPECODE = 2 and -- Credit Card Receipt
DOCUMENT.PRINTFORGROUPSALES = 1
)
union all
--refund credit card receipts that should print
select distinct
DOCUMENT.TYPE + ': (' + @CURRENCYSYMBOL +
cast(CREDITPAYMENT.AMOUNT as nvarchar) + ') ' + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' ' +
convert(nvarchar, FT.DATEADDED, 101) as TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
FT.ID CREDITID,
CREDITPAYMENT.ID,
0 PRINTED,
DOCUMENT.PRINTFORGROUPSALES TOPRINT
from
dbo.UFN_SALESORDER_REFUNDS(@SALESORDERID) as REFUNDTRANSACTIONS
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = REFUNDTRANSACTIONS.ID
inner join
dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
inner join
dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = CREDITPAYMENT.REVENUEID
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 6 and DOCUMENTPRINTINGHISTORY.CREDITPAYMENTID = CREDITPAYMENT.ID
outer apply
dbo.DOCUMENT
where
DOCUMENT.TYPECODE = 6 and -- Refund Credit Card Receipt
DOCUMENTPRINTINGHISTORY.ID is null and
DOCUMENT.PRINTFORGROUPSALES = 1
union all
select distinct
DOCUMENT.TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
0 TOPRINT
from
dbo.DOCUMENT
inner join
dbo.DOCUMENTPRINTINGRULE on DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
inner join
dbo.DOCUMENTPRINTINGRULESALESMETHOD on DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
inner join
dbo.SALESMETHOD on DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENT.TYPECODE = DOCUMENTPRINTINGHISTORY.TYPECODE and DOCUMENTPRINTINGHISTORY.SALESORDERID = @SALESORDERID
where
DOCUMENT.TYPECODE = 1 and -- Itemized Receip
DOCUMENTPRINTINGHISTORY.ID is null and
not exists (
select DPR.ID
from dbo.DOCUMENTPRINTINGRULE DPR
inner join dbo.DOCUMENTPRINTINGRULESALESMETHOD DPRSM on DPR.ID = DPRSM.DOCUMENTPRINTINGRULEID
inner join dbo.SALESMETHOD SM on DPRSM.SALESMETHODID = SM.ID
inner join dbo.SALESORDER SO on DPR.DELIVERYMETHODID = SO.DELIVERYMETHODID and SM.TYPECODE = SO.SALESMETHODTYPECODE
where SO.ID = @SALESORDERID and DPR.DOCUMENTID = DOCUMENT.ID
)
union all
--refunds itemized receipts that should print
select distinct
DOCUMENT.TYPE + ': (' + @CURRENCYSYMBOL + cast(FT.TRANSACTIONAMOUNT as nvarchar) + ') ' + convert(nvarchar, FT.DATEADDED, 101) TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
FT.ID CREDITID,
CREDITPAYMENT.ID CREDITPAYMENTID,
0 PRINTED,
1 TOPRINT
from
dbo.UFN_SALESORDER_REFUNDS(@SALESORDERID) as REFUNDTRANSACTIONS
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = REFUNDTRANSACTIONS.ID
inner join
dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 5 and DOCUMENTPRINTINGHISTORY.CREDITID = FT.ID
outer apply
dbo.DOCUMENT
where
DOCUMENT.TYPECODE = 5 and -- Refund Itemized Receipt
DOCUMENTPRINTINGHISTORY.ID is null
end
else begin
insert into @SALESDOCUMENTSTOPRINT
--already printed documents
select distinct
PRINTINGHISTORY.TYPE,
PRINTINGHISTORY.TYPECODE,
PRINTINGHISTORY.CREDITCARDPAYMENTMETHODDETAILID,
PRINTINGHISTORY.CREDITID,
PRINTINGHISTORY.CREDITPAYMENTID,
1 PRINTED,
0 TOPRINT
from
dbo.UFN_DOCUMENT_GETPRINTINGHISTORY(@SALESORDERID) PRINTINGHISTORY
union all
--itemized receipts, mailing header, and will call headers that should print based on sales method and delivery method but have not been printed.
select distinct
DOCUMENT.TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
1 TOPRINT
from
dbo.DOCUMENT
inner join
dbo.DOCUMENTPRINTINGRULE on DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
inner join
dbo.DOCUMENTPRINTINGRULESALESMETHOD on DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
inner join
dbo.SALESMETHOD on DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID
inner join
dbo.SALESORDER on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE and DOCUMENTPRINTINGRULE.DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.SALESORDERID = SALESORDER.ID and DOCUMENTPRINTINGHISTORY.TYPECODE = DOCUMENT.TYPECODE
where
SALESORDER.ID = @SALESORDERID and
DOCUMENT.TYPECODE in (1,3,4) and -- Itemized Receipt, Mailing Header, Will Call Header
DOCUMENTPRINTINGHISTORY.ID is null
union all
--credit card receipts that should print based on sales method and delivery method but have not been printed
select distinct
DOCUMENT.TYPE + ': ' + @CURRENCYSYMBOL +
cast(CREDITCARDPAYMENT.AMOUNT as nvarchar) + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID)
TYPE,
DOCUMENT.TYPECODE,
CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
1 TOPRINT
from
dbo.DOCUMENT
inner join
dbo.DOCUMENTPRINTINGRULE on DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
inner join
dbo.DOCUMENTPRINTINGRULESALESMETHOD on DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
inner join
dbo.SALESMETHOD on DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID
inner join
dbo.SALESORDER on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE and DOCUMENTPRINTINGRULE.DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID
--credit card receipts
inner join
dbo.SALESORDERPAYMENT CREDITCARDPAYMENT on SALESORDER.ID = CREDITCARDPAYMENT.SALESORDERID
inner join
dbo.REVENUEPAYMENTMETHOD CREDITCARDREVENUEPAYMENTMETHOD on CREDITCARDREVENUEPAYMENTMETHOD.REVENUEID = CREDITCARDPAYMENT.PAYMENTID and CREDITCARDREVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDREVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on SALESORDER.ID = DOCUMENTPRINTINGHISTORY.SALESORDERID and DOCUMENT.TYPECODE = DOCUMENTPRINTINGHISTORY.TYPECODE
where
SALESORDER.ID = @SALESORDERID and
DOCUMENT.TYPECODE = 2 and -- Credit Card Receipt
DOCUMENTPRINTINGHISTORY.ID is null
union all
--refunds itemized receipts that should print
select distinct
DOCUMENT.TYPE + ': (' + @CURRENCYSYMBOL +
cast(FT.TRANSACTIONAMOUNT as nvarchar) + ') ' +
convert(nvarchar, FT.DATEADDED, 101) TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
FT.ID CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
1 TOPRINT
from
dbo.UFN_SALESORDER_REFUNDS(@SALESORDERID) as REFUNDTRANSACTIONS
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = REFUNDTRANSACTIONS.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 5 and DOCUMENTPRINTINGHISTORY.CREDITID = FT.ID
outer apply
dbo.DOCUMENT
where
DOCUMENT.TYPECODE = 5 and -- Refund Itemized Receipt
DOCUMENTPRINTINGHISTORY.ID is null
union all
--refund credit card receipts that should print
select distinct
DOCUMENT.TYPE + ': (' + @CURRENCYSYMBOL +
cast(CREDITPAYMENT.AMOUNT as nvarchar) + ') ' + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' ' +
convert(nvarchar, FT.DATEADDED, 101)
TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
FT.ID CREDITID,
CREDITPAYMENT.ID,
0 PRINTED,
1 TOPRINT
from
dbo.UFN_SALESORDER_REFUNDS(@SALESORDERID) as REFUNDTRANSACTIONS
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = REFUNDTRANSACTIONS.ID
inner join
dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
left outer join
dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLI on REFUNDEDLI.ID = CREDITPAYMENT.REVENUESPLITID
inner join
dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = isnull(CREDITPAYMENT.REVENUEID, REFUNDEDLI.FINANCIALTRANSACTIONID)
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 and CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENTPRINTINGHISTORY.TYPECODE = 6 and DOCUMENTPRINTINGHISTORY.CREDITPAYMENTID = CREDITPAYMENT.ID
outer apply
dbo.DOCUMENT
where
DOCUMENT.TYPECODE = 6 and -- Refund Credit Card Receipt
DOCUMENTPRINTINGHISTORY.ID is null
union all
--itemized receipts, mailing header, and will call headers that exist, but are not set up to print based on sales method and delivery method
select distinct
DOCUMENT.TYPE,
DOCUMENT.TYPECODE,
null CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
0 TOPRINT
from
dbo.DOCUMENT
inner join
dbo.DOCUMENTPRINTINGRULE on DOCUMENT.ID = DOCUMENTPRINTINGRULE.DOCUMENTID
inner join
dbo.DOCUMENTPRINTINGRULESALESMETHOD on DOCUMENTPRINTINGRULE.ID = DOCUMENTPRINTINGRULESALESMETHOD.DOCUMENTPRINTINGRULEID
inner join
dbo.SALESMETHOD on DOCUMENTPRINTINGRULESALESMETHOD.SALESMETHODID = SALESMETHOD.ID
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENT.TYPECODE = DOCUMENTPRINTINGHISTORY.TYPECODE and DOCUMENTPRINTINGHISTORY.SALESORDERID = @SALESORDERID
where
DOCUMENT.TYPECODE in (1,3,4) and -- Itemized Receipt, Mailing Header, Will Call Header
DOCUMENTPRINTINGHISTORY.ID is null and
not exists (
select DPR.ID
from dbo.DOCUMENTPRINTINGRULE DPR
inner join dbo.DOCUMENTPRINTINGRULESALESMETHOD DPRSM on DPR.ID = DPRSM.DOCUMENTPRINTINGRULEID
inner join dbo.SALESMETHOD SM on DPRSM.SALESMETHODID = SM.ID
inner join dbo.SALESORDER SO on DPR.DELIVERYMETHODID = SO.DELIVERYMETHODID and SM.TYPECODE = SO.SALESMETHODTYPECODE
where SO.ID = @SALESORDERID and DPR.DOCUMENTID = DOCUMENT.ID
)
union all
--credit card receipts that exist, but are not set up to print based on sales method and delivery method
select distinct
DOCUMENT.TYPE + ': ' + @CURRENCYSYMBOL +
cast(CREDITCARDPAYMENT.AMOUNT as nvarchar) + ' *' +
CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' ' +
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) as TYPE,
DOCUMENT.TYPECODE,
CREDITCARDPAYMENTMETHODDETAIL.ID CREDITCARDPAYMENTMETHODDETAILID,
null CREDITID,
null CREDITPAYMENTID,
0 PRINTED,
0 TOPRINT
from
dbo.SALESORDERPAYMENT CREDITCARDPAYMENT
inner join dbo.REVENUE CREDITCARDREVENUE on
CREDITCARDPAYMENT.PAYMENTID = CREDITCARDREVENUE.ID
inner join
dbo.REVENUEPAYMENTMETHOD CREDITCARDREVENUEPAYMENTMETHOD on CREDITCARDREVENUEPAYMENTMETHOD.REVENUEID = CREDITCARDPAYMENT.PAYMENTID and CREDITCARDREVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2
inner join
dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDREVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
outer apply
dbo.DOCUMENT
left outer join
dbo.DOCUMENTPRINTINGHISTORY on DOCUMENT.TYPECODE = DOCUMENTPRINTINGHISTORY.TYPECODE and DOCUMENTPRINTINGHISTORY.SALESORDERID = CREDITCARDPAYMENT.SALESORDERID
where
CREDITCARDPAYMENT.SALESORDERID = @SALESORDERID and
DOCUMENT.TYPECODE = 2 and
DOCUMENTPRINTINGHISTORY.ID is null and
not exists
(
select DPR.ID
from dbo.DOCUMENTPRINTINGRULE DPR
inner join dbo.DOCUMENTPRINTINGRULESALESMETHOD DPRSM on DPR.ID = DPRSM.DOCUMENTPRINTINGRULEID
inner join dbo.SALESMETHOD SM on DPRSM.SALESMETHODID = SM.ID
inner join dbo.SALESORDER SO on DPR.DELIVERYMETHODID = SO.DELIVERYMETHODID and SM.TYPECODE = SO.SALESMETHODTYPECODE
where SO.ID = @SALESORDERID and DPR.DOCUMENTID = DOCUMENT.ID
)
end
return
end