USP_DATALIST_APPOSTTOGLPROCESS

CREATE procedure dbo.USP_DATALIST_APPOSTTOGLPROCESS
as
    set nocount on;

  select GLPOSTPROCESS.ID, GLPOSTPROCESS.NAME, GLPOSTPROCESS.DESCRIPTION,
  case GLPOSTPROCESSPOSTMETHOD.POSTMETHODCODE when 1 then 'Detail' when 2 then 'Detail' when 3 then 'Summary' end as ExpensePostMethod,
  case ppm2.POSTMETHODCODE when 1 then 'Detail' when 2 then 'Detail' when 3 then 'Summary' end as PayablesSummaryPostMethod,
  case ppm3.POSTMETHODCODE when 1 then 'Detail' when 2 then 'Detail' when 3 then 'Summary' end as TreasuryCashPostMethod,
  case GLPOSTPROCESSTRANSACTIONCRITERIA.POSTINGOPTIONCODE when 0 then 'All ''not posted'' invoices and credit memos' when 1 then 'Selected records' when 2 then 'None' end as InvoiceCriteria,
  case ptc.POSTINGOPTIONCODE when 0 then 'All ''not posted'' disbursements' when 1 then 'Selected records' when 2 then 'None' end as DisbursementCriteria
  from dbo.GLPOSTPROCESS inner join dbo.GLPOSTPROCESSPOSTMETHOD on GLPOSTPROCESS.ID = GLPOSTPROCESSPOSTMETHOD.GLPOSTPROCESSID and TRANSACTIONTYPECODE = 0 --Expense

  inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE ft1 on GLPOSTPROCESSPOSTMETHOD.FINANCIALSYSTEMTRANSACTIONTYPEID = ft1.ID and ft1.TYPECODE = 101
  inner join dbo.GLPOSTPROCESSPOSTMETHOD ppm2 on GLPOSTPROCESS.ID = ppm2.GLPOSTPROCESSID and ppm2.TRANSACTIONTYPECODE = 1  --Payables summary

  inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE ft2 on ppm2.FINANCIALSYSTEMTRANSACTIONTYPEID = ft2.ID and ft2.TYPECODE = 101
  inner join dbo.GLPOSTPROCESSPOSTMETHOD ppm3 on GLPOSTPROCESS.ID = ppm3.GLPOSTPROCESSID and ppm3.TRANSACTIONTYPECODE = 1  --Treasury cash

  inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE ft3 on ppm3.FINANCIALSYSTEMTRANSACTIONTYPEID = ft3.ID and ft3.TYPECODE = 105
  inner join dbo.GLPOSTPROCESSTRANSACTIONCRITERIA on GLPOSTPROCESS.ID = GLPOSTPROCESSTRANSACTIONCRITERIA.GLPOSTPROCESSID 
  inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE ft4 on GLPOSTPROCESSTRANSACTIONCRITERIA.FINANCIALSYSTEMTRANSACTIONTYPEID = ft4.ID and ft4.TYPECODE = 101
  inner join dbo.GLPOSTPROCESSTRANSACTIONCRITERIA as ptc on GLPOSTPROCESS.ID = ptc.GLPOSTPROCESSID
  inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE ft5 on ptc.FINANCIALSYSTEMTRANSACTIONTYPEID = ft5.ID and ft5.TYPECODE = 105
  where GLPOSTPROCESS.FINANCIALSYSTEMID = '777AE8A1-67F4-474F-8474-E16F41EE0EC4'