CA_WC_CF_DSC_VW

(SQL View)
 Index Back

PC CF Discrepancies


SELECT HDR.CONTRACT_NUM ,DTL.CONTRACT_LINE_NUM ,DST.EFFDT ,DST.DST_SEQ_NUM ,HDR.BUSINESS_UNIT ,HDR.DESCR ,HDR.CA_STATUS ,DTL.BUSINESS_UNIT_PC ,DST.BUSINESS_UNIT_PC ,PRJ.PROJECT_ID ,DST.PROJECT_ID ,PRJ.ACTIVITY_ID ,DST.ACTIVITY_ID ,PRJ.RESOURCE_TYPE ,DST.RESOURCE_TYPE ,PRJ.RESOURCE_CATEGORY ,DST.RESOURCE_CATEGORY ,PRJ.RESOURCE_SUB_CAT ,DST.RESOURCE_SUB_CAT ,'REV' ,(CASE WHEN DTL.BUSINESS_UNIT_PC <> DST.BUSINESS_UNIT_PC THEN 'BU' WHEN (PRJ.PROJECT_ID <> DST.PROJECT_ID OR (PRJ.PROJECT_ID IS NULL AND DST.PROJECT_ID <> ' ')) THEN 'PRJ' WHEN (PRJ.ACTIVITY_ID <> DST.ACTIVITY_ID OR (PRJ.ACTIVITY_ID IS NULL AND DST.ACTIVITY_ID <> ' ') ) THEN 'ACT' ELSE 'OTH' END) FROM PS_CA_DETAIL_DST DST ,PS_CA_CONTR_HDR HDR ,PS_CA_DETAIL DTL LEFT OUTER JOIN PS_CA_DETAIL_PROJ PRJ ON PRJ.CONTRACT_NUM=DTL.CONTRACT_NUM AND PRJ.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM WHERE DTL.CONTRACT_NUM=DST.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = DST.CONTRACT_LINE_NUM AND DTL.CONTRACT_NUM=HDR.CONTRACT_NUM AND DTL.PRICING_STRUCTURE IN ( 'AMT','PCT','RCR') AND HDR.USE_PC_CFS = 'Y' AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CA_PROC_STATUS <> 'C' AND DTL.CA_LINE_STATUS <> 'C' AND NOT (DTL.BUSINESS_UNIT_PC = ' ' AND PRJ.PROJECT_ID = ' ' AND PRJ.ACTIVITY_ID = ' ' AND PRJ.RESOURCE_TYPE = ' ' AND PRJ.RESOURCE_CATEGORY = ' ' AND PRJ.RESOURCE_SUB_CAT = ' ') AND NOT (DST.BUSINESS_UNIT_PC = ' ' AND DST.PROJECT_ID = ' ' AND DST.ACTIVITY_ID = ' ' AND DST.RESOURCE_TYPE = ' ' AND DST.RESOURCE_CATEGORY = ' ' AND DST.RESOURCE_SUB_CAT = ' ') AND (NOT (DTL.BUSINESS_UNIT_PC = DST.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = DST.PROJECT_ID AND PRJ.ACTIVITY_ID = DST.ACTIVITY_ID AND PRJ.RESOURCE_TYPE = DST.RESOURCE_TYPE AND PRJ.RESOURCE_CATEGORY = DST.RESOURCE_CATEGORY AND PRJ.RESOURCE_SUB_CAT = DST.RESOURCE_SUB_CAT ) OR (DTL.BUSINESS_UNIT_PC = DST.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID IS NULL AND (DST.PROJECT_ID <> ' ' OR DST.ACTIVITY_ID <> ' ' OR DST.RESOURCE_TYPE <> ' ' OR DST.RESOURCE_CATEGORY <> ' ' OR DST.RESOURCE_SUB_CAT <> ' ' ))) UNION SELECT HDR.CONTRACT_NUM ,DTL.CONTRACT_LINE_NUM ,DFR.EFFDT ,DFR.DST_SEQ_NUM ,HDR.BUSINESS_UNIT ,HDR.DESCR ,HDR.CA_STATUS ,DTL.BUSINESS_UNIT_PC ,DFR.BUSINESS_UNIT_PC ,PRJ.PROJECT_ID ,DFR.PROJECT_ID ,PRJ.ACTIVITY_ID ,DFR.ACTIVITY_ID ,PRJ.RESOURCE_TYPE ,DFR.RESOURCE_TYPE ,PRJ.RESOURCE_CATEGORY ,DFR.RESOURCE_CATEGORY ,PRJ.RESOURCE_SUB_CAT ,DFR.RESOURCE_SUB_CAT ,'DFR' ,(CASE WHEN DTL.BUSINESS_UNIT_PC <> DFR.BUSINESS_UNIT_PC THEN 'BU' WHEN (PRJ.PROJECT_ID <> DFR.PROJECT_ID OR (PRJ.PROJECT_ID IS NULL AND DFR.PROJECT_ID <> ' ')) THEN 'PRJ' WHEN (PRJ.ACTIVITY_ID <> DFR.ACTIVITY_ID OR (PRJ.ACTIVITY_ID IS NULL AND DFR.ACTIVITY_ID <> ' ') ) THEN 'ACT' ELSE 'OTH' END) FROM PS_CA_DETAIL_DFR DFR ,PS_CA_CONTR_HDR HDR ,PS_CA_DETAIL DTL LEFT OUTER JOIN PS_CA_DETAIL_PROJ PRJ ON PRJ.CONTRACT_NUM=DTL.CONTRACT_NUM AND PRJ.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM WHERE DTL.CONTRACT_NUM=DFR.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = DFR.CONTRACT_LINE_NUM AND DTL.CONTRACT_NUM=HDR.CONTRACT_NUM AND DTL.PRICING_STRUCTURE IN ( 'AMT','PCT') AND HDR.USE_PC_CFS = 'Y' AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CA_PROC_STATUS <> 'C' AND DTL.CA_LINE_STATUS <> 'C' AND NOT (DTL.BUSINESS_UNIT_PC = ' ' AND PRJ.PROJECT_ID = ' ' AND PRJ.ACTIVITY_ID = ' ' AND PRJ.RESOURCE_TYPE = ' ' AND PRJ.RESOURCE_CATEGORY = ' ' AND PRJ.RESOURCE_SUB_CAT = ' ') AND NOT (DFR.BUSINESS_UNIT_PC = ' ' AND DFR.PROJECT_ID = ' ' AND DFR.ACTIVITY_ID = ' ' AND DFR.RESOURCE_TYPE = ' ' AND DFR.RESOURCE_CATEGORY = ' ' AND DFR.RESOURCE_SUB_CAT = ' ') AND (NOT (DTL.BUSINESS_UNIT_PC = DFR.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = DFR.PROJECT_ID AND PRJ.ACTIVITY_ID = DFR.ACTIVITY_ID AND PRJ.RESOURCE_TYPE = DFR.RESOURCE_TYPE AND PRJ.RESOURCE_CATEGORY = DFR.RESOURCE_CATEGORY AND PRJ.RESOURCE_SUB_CAT = DFR.RESOURCE_SUB_CAT ) OR (DTL.BUSINESS_UNIT_PC = DFR.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID IS NULL AND (DFR.PROJECT_ID <> ' ' OR DFR.ACTIVITY_ID <> ' ' OR DFR.RESOURCE_TYPE <> ' ' OR DFR.RESOURCE_CATEGORY <> ' ' OR DFR.RESOURCE_SUB_CAT <> ' ' ))) UNION SELECT HDR.CONTRACT_NUM ,DTL.CONTRACT_LINE_NUM ,UAR.EFFDT ,UAR.DST_SEQ_NUM ,HDR.BUSINESS_UNIT ,HDR.DESCR ,HDR.CA_STATUS ,DTL.BUSINESS_UNIT_PC ,UAR.BUSINESS_UNIT_PC ,PRJ.PROJECT_ID ,UAR.PROJECT_ID ,PRJ.ACTIVITY_ID ,UAR.ACTIVITY_ID ,PRJ.RESOURCE_TYPE ,UAR.RESOURCE_TYPE ,PRJ.RESOURCE_CATEGORY ,UAR.RESOURCE_CATEGORY ,PRJ.RESOURCE_SUB_CAT ,UAR.RESOURCE_SUB_CAT ,'UAR' ,(CASE WHEN DTL.BUSINESS_UNIT_PC <> UAR.BUSINESS_UNIT_PC THEN 'BU' WHEN (PRJ.PROJECT_ID <> UAR.PROJECT_ID OR (PRJ.PROJECT_ID IS NULL AND UAR.PROJECT_ID <> ' ')) THEN 'PRJ' WHEN (PRJ.ACTIVITY_ID <> UAR.ACTIVITY_ID OR (PRJ.ACTIVITY_ID IS NULL AND UAR.ACTIVITY_ID <> ' ') ) THEN 'ACT' ELSE 'OTH' END) FROM PS_CA_DETAIL_UAR UAR ,PS_CA_CONTR_HDR HDR ,PS_CA_DETAIL DTL LEFT OUTER JOIN PS_CA_DETAIL_PROJ PRJ ON PRJ.CONTRACT_NUM=DTL.CONTRACT_NUM AND PRJ.CONTRACT_LINE_NUM = DTL.CONTRACT_LINE_NUM WHERE DTL.CONTRACT_NUM=UAR.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = UAR.CONTRACT_LINE_NUM AND DTL.CONTRACT_NUM=HDR.CONTRACT_NUM AND DTL.PRICING_STRUCTURE IN ( 'AMT','PCT') AND HDR.USE_PC_CFS = 'Y' AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CA_PROC_STATUS <> 'C' AND DTL.CA_LINE_STATUS <> 'C' AND NOT (DTL.BUSINESS_UNIT_PC = ' ' AND PRJ.PROJECT_ID = ' ' AND PRJ.ACTIVITY_ID = ' ' AND PRJ.RESOURCE_TYPE = ' ' AND PRJ.RESOURCE_CATEGORY = ' ' AND PRJ.RESOURCE_SUB_CAT = ' ') AND NOT (UAR.BUSINESS_UNIT_PC = ' ' AND UAR.PROJECT_ID = ' ' AND UAR.ACTIVITY_ID = ' ' AND UAR.RESOURCE_TYPE = ' ' AND UAR.RESOURCE_CATEGORY = ' ' AND UAR.RESOURCE_SUB_CAT = ' ') AND (NOT (DTL.BUSINESS_UNIT_PC = UAR.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = UAR.PROJECT_ID AND PRJ.ACTIVITY_ID = UAR.ACTIVITY_ID AND PRJ.RESOURCE_TYPE = UAR.RESOURCE_TYPE AND PRJ.RESOURCE_CATEGORY = UAR.RESOURCE_CATEGORY AND PRJ.RESOURCE_SUB_CAT = UAR.RESOURCE_SUB_CAT ) OR (DTL.BUSINESS_UNIT_PC = UAR.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID IS NULL AND (UAR.PROJECT_ID <> ' ' OR UAR.ACTIVITY_ID <> ' ' OR UAR.RESOURCE_TYPE <> ' ' OR UAR.RESOURCE_CATEGORY <> ' ' OR UAR.RESOURCE_SUB_CAT <> ' ' )))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
2 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 DST_SEQ_NUM Number(6,0) INTEGER NOT NULL Distribution Sequence
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
7 CA_STATUS Character(24) VARCHAR2(24) NOT NULL Contract Status
8 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
9 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit
10 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
11 PROJECT_ID_2 Character(15) VARCHAR2(15) NOT NULL Project
12 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
13 ACTIVITY_ID_2 Character(15) VARCHAR2(15) NOT NULL Activity ID
14 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
15 RESOURCE_TYPE_BIL Character(5) VARCHAR2(5) NOT NULL Source Type for Bil Fee Cost
16 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
17 RESOURCE_CAT_BIL Character(5) VARCHAR2(5) NOT NULL Category for Bil Cost Fee
18 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
19 RESOURCE_SUB_C_BIL Character(5) VARCHAR2(5) NOT NULL Subcategory
20 CA_DISTRIBUTION Character(3) VARCHAR2(3) NOT NULL Distribution
DFR=Contract Liability
REV=Revenue
UAR=Contract Asset
21 CA_ACCT_DISCRPCY Character(3) VARCHAR2(3) NOT NULL Highest Level of Discrepancy
ACT=Activity
BU=PC BU
OTH=Other ChartFields
PRJ=Project