USP_DATAFORMTEMPLATE_EDIT_APPOSTTOGLPROCESS

The save procedure used by the edit dataform template "AP Post to GL Process Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@INVOICEPOSTINGOPTIONCODE	tinyint	IN	Invoices and credit memos
@INVOICEIDSETREGISTERID	uniqueidentifier	IN	Selection
@DISBURSEMENTOPTIONCODE	tinyint	IN	Disbursements
@DISBURSEMENTREGISTERID	uniqueidentifier	IN	Selection
@INCLUDEBYPOSTDATE	bit	IN	Include 'not posted' transactions by post date
@POSTTHROUGHDATECODE	tinyint	IN	Post dates up to and including
@EXPENSEPOSTMETHODCODE	tinyint	IN	Expense
@PAYABLESPOSTMETHODCODE	tinyint	IN	Payables summary
@CASHPOSTMETHODCODE	tinyint	IN	Treasury cash
@SUMMARIZEBYCODE	tinyint	IN	Summarize transactions by
@CREATEOUTPUTIDSET	bit	IN	Create selection from results
@OUTPUTIDSETNAME	nvarchar(100)	IN	Selection name
@OVERWRITEOUTPUTIDSET	bit	IN	Overwrite existing selection

Definition

Copy



create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPOSTTOGLPROCESS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
        @NAME nvarchar(100), 
        @DESCRIPTION nvarchar(255), 
      @INVOICEPOSTINGOPTIONCODE tinyint,
    @INVOICEIDSETREGISTERID uniqueidentifier,
    @DISBURSEMENTOPTIONCODE tinyint,
    @DISBURSEMENTREGISTERID uniqueidentifier,
    @INCLUDEBYPOSTDATE bit,
    @POSTTHROUGHDATECODE tinyint,
    @EXPENSEPOSTMETHODCODE tinyint,
    @PAYABLESPOSTMETHODCODE tinyint,
    @CASHPOSTMETHODCODE tinyint,
    @SUMMARIZEBYCODE tinyint,
    @CREATEOUTPUTIDSET bit,
    @OUTPUTIDSETNAME nvarchar(100),
    @OVERWRITEOUTPUTIDSET bit

)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
    -- handle inserting the data

    update dbo.GLPOSTPROCESS
        set NAME = @NAME, 
    DESCRIPTION = @DESCRIPTION,
    INCLUDEBYPOSTDATE = @INCLUDEBYPOSTDATE,
    POSTTHROUGHDATECODE = @POSTTHROUGHDATECODE,
    SUMMARIZEBYDATECODE = @SUMMARIZEBYCODE,
    CREATEOUTPUTIDSET = @CREATEOUTPUTIDSET,
    OUTPUTIDSETNAME = case @CREATEOUTPUTIDSET when 0 then '' else @OUTPUTIDSETNAME end,
    OVERWRITEOUTPUTIDSET = case @CREATEOUTPUTIDSET when 0 then 0 else @OVERWRITEOUTPUTIDSET end,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where ID = @ID

  update dbo.GLPOSTPROCESSPOSTMETHOD
    set POSTMETHODCODE = @EXPENSEPOSTMETHODCODE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.GLPOSTPROCESSPOSTMETHOD inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on GLPOSTPROCESSPOSTMETHOD.FINANCIALSYSTEMTRANSACTIONTYPEID = FINANCIALSYSTEMTRANSACTIONTYPE.ID
  where GLPOSTPROCESSPOSTMETHOD.GLPOSTPROCESSID = @ID
    and GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE = 0
    and (FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 101 or FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 102)

  update dbo.GLPOSTPROCESSPOSTMETHOD
    set POSTMETHODCODE = @PAYABLESPOSTMETHODCODE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.GLPOSTPROCESSPOSTMETHOD inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on GLPOSTPROCESSPOSTMETHOD.FINANCIALSYSTEMTRANSACTIONTYPEID = FINANCIALSYSTEMTRANSACTIONTYPE.ID
  where GLPOSTPROCESSPOSTMETHOD.GLPOSTPROCESSID = @ID
    and ((GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE = 1 and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 101)
      or (GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE = 1 and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 102)
      or (GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE = 0 and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 105))

  update dbo.GLPOSTPROCESSPOSTMETHOD
    set POSTMETHODCODE = @CASHPOSTMETHODCODE,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.GLPOSTPROCESSPOSTMETHOD inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on GLPOSTPROCESSPOSTMETHOD.FINANCIALSYSTEMTRANSACTIONTYPEID = FINANCIALSYSTEMTRANSACTIONTYPE.ID
  where GLPOSTPROCESSPOSTMETHOD.GLPOSTPROCESSID = @ID
    and GLPOSTPROCESSPOSTMETHOD.TRANSACTIONTYPECODE = 1 
    and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 105

  update dbo.GLPOSTPROCESSTRANSACTIONCRITERIA
    set POSTINGOPTIONCODE = case TYPECODE when 101 then @INVOICEPOSTINGOPTIONCODE when 105 then @DISBURSEMENTOPTIONCODE end,
    IDSETREGISTERID = case TYPECODE when 101 then case @INVOICEPOSTINGOPTIONCODE when 1 then @INVOICEIDSETREGISTERID else null end when 105 then case @DISBURSEMENTOPTIONCODE when 1 then @DISBURSEMENTREGISTERID else null end end,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from dbo.GLPOSTPROCESSTRANSACTIONCRITERIA inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on GLPOSTPROCESSTRANSACTIONCRITERIA.FINANCIALSYSTEMTRANSACTIONTYPEID = FINANCIALSYSTEMTRANSACTIONTYPE.ID
  where GLPOSTPROCESSTRANSACTIONCRITERIA.GLPOSTPROCESSID = @ID
  and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE in (101,105)

  if @INVOICEPOSTINGOPTIONCODE = 0
    merge into dbo.GLPOSTPROCESSTRANSACTIONCRITERIA as Target
    using (select @ID as GLPostProcessID, @INVOICEPOSTINGOPTIONCODE as PostingOption, ID as FINANCIALSYSTEMTRANSACTIONTYPEID from dbo.FINANCIALSYSTEMTRANSACTIONTYPE where TYPECODE = 102) as Source
    on (Target.GLPostPRocessID = Source.GLPostProcessID and Target.FINANCIALSYSTEMTRANSACTIONTYPEID = Source.FINANCIALSYSTEMTRANSACTIONTYPEID)
    when matched then
      update set Target.POSTINGOPTIONCODE = Source.PostingOption,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    when not matched by Target then
      insert (ID, GLPOSTPROCESSID, POSTINGOPTIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, FINANCIALSYSTEMTRANSACTIONTYPEID)
      values (newid(), Source.GLPostProcessID, Source.PostingOption, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, Source.FINANCIALSYSTEMTRANSACTIONTYPEID);
  else
    delete GLPOSTPROCESSTRANSACTIONCRITERIA
    from dbo.GLPOSTPROCESSTRANSACTIONCRITERIA inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on GLPOSTPROCESSTRANSACTIONCRITERIA.FINANCIALSYSTEMTRANSACTIONTYPEID = FINANCIALSYSTEMTRANSACTIONTYPE.ID
    where GLPOSTPROCESSTRANSACTIONCRITERIA.GLPOSTPROCESSID = @ID
    and FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE = 102

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0