Automated Credit Memos or Work Orders

Let's say you want to automatically create a credit memo or work order whenever an invoice completes.

This requires TWO things:

#1 - Create a price/cost breakdown entry on the sales order line item, and mark it as "Auto Bill". See the picture below.

#2 - Email support to have this "Automated Credit Memo" or "Automated Work Order" job set up in your system. The code that runs the system event is attached.

When all is set up properly, the credit memo (or work order) will be created whenever an invoice is marked as completed, and there is a price/cost breakdown record as shown above. It may take up to 10 minutes for this to happen after you mark the invoice as complete. It will only create it once, and will not update it if you mess up. :)

Here is the code:

update a

set a.IsActive=1, a.ActionParameter1 = 'DECLARE @Number varchar(20)

EXEC [dbo].[GetNumberScheme] @Code=N''CREDIT'',@Output=@Number OUTPUT
select @Number+''-''+cast(ROW_NUMBER() over (partition by s.ShipmentId order by s.ShipmentId) as varchar) BillNumber

, cm.BillTypeID, isnull(s.PlannedDeparture, GETUTCDATE()) BillDate

, null Completed

, isnull(nullif(ai.Reference1,''''), nullif(a.Reference1,'''')) Reference1

, ''OVERVALUE'' Reference2, 0 IsProForma, terms.TermsId

, a.Buyer_PartyId, a.Buyer_PartyAddressId, inv.BuyerAddressBlock, a.Seller_PartyId

, a.Seller_PartyAddressId, inv.SellerAddressBlock, a.ShipTo_PartyId, a.ShipTo_PartyAddressId

, inv.ShipToAddressBlock, ''auto'' CreatedBy, getutcdate() CreatedDate

, null Related_BillId, 1 LineNumber, itm.ItemId

, ''Booking:'' + s.ShipmentNumber + ''; Sale:'' + a.AgreementNumber + isnull(''; Ref:'' + nullif(ai.Reference1,''''), ''; Ref:'' + nullif(a.Reference1,'''')) Description

, case when pc.UnitTypeId = st.UnitTypeId then s.TotalST

when pc.UnitTypeId = mt.UnitTypeId then s.TotalMT

else 0 end Quantity

, pc.UnitTypeId UnitTypeId, pc.Price Price, pc.UnitTypeId Per_UnitTypeId

, null AgreementItemId, s.ShipmentNumber ItemReference1, a.AgreementId, s.ShipmentId

into #toadd
from dbo.ReportShipment s 
join dbo.AgreementItemAssignment aif on aif.ShipmentId=s.ShipmentId
join dbo.AgreementItem ai on ai.AgreementItemId=aif.AgreementItemId
join dbo.Agreement a 

join dbo.AgreementType at on at.AgreementTypeId = a.AgreementTypeId and at.Mnemonic = ''SORDER''

on a.AgreementId=ai.AgreementId

join dbo.BillLink b on b.ShipmentId=s.ShipmentId
join dbo.BillType cm on cm.Mnemonic = ''CREDIT''
join dbo.Terms terms on terms.ShortCode=''N30''
join dbo.Item itm on itm.ItemNumber = ''CLAIM''
join dbo.UnitType mt on mt.AlternateCode = ''TNE''
join dbo.UnitType st on st.AlternateCode = ''STN''
join dbo.Bill inv 

join dbo.BillType invt

on invt.BillTypeID = inv.BillTypeID and invt.Mnemonic = ''INVOICE''

on inv.BillId = b.BillId

join dbo.PriceComponent pc on pc.AgreementItemId=aif.AgreementItemId and pc.IsAutoBilled=1 and pc.ItemId = itm.ItemId
left outer join (select bi.BillId, bi.ItemId

from dbo.BillItem bi

join dbo.Bill cm

join dbo.BillType bt

on bt.BillTypeID = cm.BillTypeID and bt.Mnemonic = ''CREDIT''

on cm.BillId = bi.BillId

group by bi.BillId,bi.ItemId) w on w.BillId=b.BillId and w.ItemId=pc.Itemid

where b.BillId=@BillId 
and w.ItemId is null
insert dbo.Bill

(BillNumber, BillTypeID, BillDate, Completed, Reference1, Reference2, Memo,

IsProForma, TermsId,

Buyer_PartyId, Buyer_PartyAddressId, BuyerAddressBlock,

Seller_PartyId, Seller_PartyAddressId, SellerAddressBlock,

ShipTo_PartyId, ShipTo_PartyAddressId, ShipToAddressBlock,

CreatedUser, CreatedDate, Related_BillId)

select

a.BillNumber,

a.BillTypeId,

a.BillDate,

a.Completed,

a.Reference1,

a.Reference2,

a.Description,

a.IsProForma,

a.TermsId,

a.Buyer_PartyId,

a.Buyer_PartyAddressId,

a.BuyerAddressBlock,

a.Seller_PartyId,

a.Seller_PartyAddressId,

a.SellerAddressBlock,

a.ShipTo_PartyId,

a.ShipTo_PartyAddressId,

a.ShipToAddressBlock,

a.CreatedBy,

a.CreatedDate,

a.Related_BillId -- Invoice

from #toadd a
insert dbo.BillItem
    (BillId, LineNumber, ItemId, Description, Reference1, Quantity, UnitTypeId, Price, Per_UnitTypeId, AgreementItemId, ItemDate)
select
    b.BillId,

a.LineNumber,

a.ItemId,

a.Description,

a.ItemReference1,

a.Quantity,

a.UnitTypeId,

a.Price,

a.Per_UnitTypeId,

a.AgreementItemId,

a.BillDate

from #toadd a
join dbo.Bill b 

join dbo.BillType bt on bt.BillTypeID = b.BillTypeID and bt.Mnemonic = ''CREDIT''

on b.BillNumber = a.BillNumber

insert dbo.BillLink
    (BillId, ShipmentId, AgreementId)
select
    b.BillId,

a.ShipmentId,

a.AgreementId

from #toadd a
join dbo.Bill b 

join dbo.BillType bt on bt.BillTypeID = b.BillTypeID and bt.Mnemonic = ''CREDIT''

on b.BillNumber = a.BillNumber

drop table #toadd'
from dbo.EventAction a
where a.eventactionid=111