Embedded Files
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
Report abuse