ÿþUSE [DW]

GO

/****** Object:  View [dbo].[v_T_PCARD_TRAN_DETL]    Script Date: 10/12/2006 16:08:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[v_T_PCARD_TRAN_DETL]

AS

SELECT     TRANDETL.PCARD_TRAN_SEQ_NBR, TRANDETL.PCARD_ITEM_SEQ_NBR, TRANDETL.PCARD_ACCTG_SEQ_NBR, TRANDETL.EDW_FIN_DOC_ID, 

                      TRANDETL.PCARD_DOC_NBR, TRANDETL.PCARD_CARD_NBR_LAST_4, TRANDETL.COA_CD, TRANDETL.FIN_FUND_CD, TRANDETL.ORG_CD, 

                      TRANDETL.FIN_ACCT_CD, TRANDETL.FIN_PGM_CD, TRANDETL.FIN_ACTV_CD, TRANDETL.LOCN_CD, 

                      TRANDETL.PCARD_CARDHOLDER_EDW_PERS_ID, 

                      CRDHOLDER.PERS_LNAME + ', ' + CRDHOLDER.PERS_FNAME + ' ' + ISNULL(CRDHOLDER.PERS_MNAME, '') AS cardholder_lfm, 

                      TRANDETL.PCARD_APRV_EDW_PERS_ID, APRVER.PERS_LNAME + ', ' + APRVER.PERS_FNAME + ' ' + ISNULL(APRVER.PERS_MNAME, '') 

                      AS approver_lfm, TRANDETL.PCARD_RECON_EDW_PERS_ID, 

                      RECONCILER.PERS_LNAME + ', ' + RECONCILER.PERS_FNAME + ' ' + ISNULL(RECONCILER.PERS_MNAME, '') AS reconciler_lfm, 

                      TRANDETL.PCARD_LAST_CHG_EDW_PERS_ID, TRANDETL.PCARD_RQSTR_NAME, TRANDETL.PCARD_VEND_ID, TRANDETL.PCARD_PURCH_DT, 

                      TRANDETL.PCARD_RECON_DT, TRANDETL.PCARD_EXTRACT_APRV_DT, TRANDETL.PCARD_AUTO_RECON_APRV_DT, 

                      TRANDETL.PCARD_ITEM_LAST_CHG_DT, TRANDETL.PCARD_TRAN_STATUS_CD, TRANDETL.PCARD_TRAN_STATUS_DESC, 

                      TRANDETL.PCARD_ITEM_DESC, TRANDETL.PCARD_ITEM_PRC, TRANDETL.PCARD_ITEM_QTY, TRANDETL.PCARD_TRAN_AMT, 

                      TRANDETL.PCARD_REF_NBR, TRANDETL.PCARD_USER_LOG_NBR, TRANDETL.PCARD_DEB_CREDIT_IND, TRANDETL.PCARD_AUTO_APRV_IND, 

                      TRANDETL.PCARD_AUTO_MATCH_IND, TRANDETL.PCARD_AUTO_RECON_IND, TRANDETL.PCARD_DISPUTED_IND, 

                      TRANDETL.PCARD_FULL_RCPT_IND, TRANDETL.PCARD_TRAN_DETL_POST_DT, VEND.PCARD_VEND_NAME, VEND.PCARD_VEND_CITY, 

                      VEND.PCARD_VEND_STATE_CD, VEND.PCARD_VEND_ZIP_CD, VEND.PCARD_VEND_CNTRY_CD, COMMENT.PCARD_TRAN_COMMENT_POST_DT, 

                      COMMENT.PCARD_TRAN_COMMENT, 

                      TRANDETL.COA_CD + '-' + TRANDETL.FIN_FUND_CD + '-' + TRANDETL.ORG_CD + '-' + TRANDETL.FIN_PGM_CD AS cfop, FUND.GRANT_CD, 

                      FUND.FIN_FUND_TITLE, FUND.FIN_FUND_TYPE_CD, FUND.FIN_FUND_LEVEL_NBR, FUND.FIN_FUND_LEVEL_1_TITLE, 

                      FUND.FIN_FUND_LEVEL_2_TITLE, ORG.ORG_TITLE, ORG.CAMPUS_LEVEL_1_TITLE, ORG.COLL_LEVEL_3_TITLE, 

                      ORG.SCHOOL_SUB_COLL_LEVEL_4_TITLE, ORG.DEPT_LEVEL_5_TITLE, ACCT.FIN_ACCT_TITLE, ACCT.FIN_ACCT_TYPE_CD, 

                      ACCT.FIN_ACCT_LEVEL_NBR, ACCT.FIN_ACCT_LEVEL_1_TITLE, ACCT.FIN_ACCT_LEVEL_2_TITLE, PGM.FIN_PGM_TITLE, PGM.FIN_PGM_LEVEL_NBR, 

                      PGM.NACUBO_FUNC_LEVEL_1_CD, PGM.NACUBO_FUNC_LEVEL_1_TITLE

FROM         dbo.T_PCARD_TRAN_COMMENT AS COMMENT RIGHT OUTER JOIN

                      dbo.T_ORG_CD_HIST AS ORG RIGHT OUTER JOIN

                      dbo.T_PCARD_TRAN_DETL AS TRANDETL INNER JOIN

                      dbo.T_PCARD_VEND AS VEND ON TRANDETL.PCARD_VEND_ID = VEND.PCARD_VEND_ID ON ORG.COA_CD = TRANDETL.COA_CD AND 

                      ORG.ORG_CD = TRANDETL.ORG_CD RIGHT OUTER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS RECONCILER ON TRANDETL.PCARD_RECON_EDW_PERS_ID = RECONCILER.EDW_PERS_ID LEFT OUTER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS APRVER ON TRANDETL.PCARD_APRV_EDW_PERS_ID = APRVER.EDW_PERS_ID LEFT OUTER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS CRDHOLDER ON 

                      TRANDETL.PCARD_CARDHOLDER_EDW_PERS_ID = CRDHOLDER.EDW_PERS_ID LEFT OUTER JOIN

                      dbo.T_FIN_FUND_CD_HIST AS FUND ON TRANDETL.FIN_FUND_CD = FUND.FIN_FUND_CD AND 

                      TRANDETL.COA_CD = FUND.COA_CD LEFT OUTER JOIN

                      dbo.T_FIN_PGM_CD_HIST AS PGM ON TRANDETL.COA_CD = PGM.COA_CD AND TRANDETL.FIN_PGM_CD = PGM.FIN_PGM_CD LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_CD_HIST AS ACCT ON TRANDETL.FIN_ACCT_CD = ACCT.FIN_ACCT_CD AND TRANDETL.COA_CD = ACCT.COA_CD ON 

                      COMMENT.PCARD_TRAN_SEQ_NBR = TRANDETL.PCARD_TRAN_SEQ_NBR



GO