FND Design Data [Home] [Help]

View: PA_GL_UNCOST_EXCEPT_SUM_V

Product: PA - Projects
Description: PA_GL_UNCOST_EXCEPT_SUM_V is a view that identifies the Cost Distribution Lines that prevent a period
Implementation/DBA Data: ViewAPPS.PA_GL_UNCOST_EXCEPT_SUM_V
View Text

SELECT PP.PROJECT_ID
, PP.NAME
, EI.TASK_ID TASK_ID
, TT.TASK_NAME
, EI.EXPENDITURE_ID EXPENDITURE_ID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID)
, PA_EXPENDITURES_UTILS.GETORGTLNAME(NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID))
, PRD.PERIOD_NAME PERIOD_NAME
, DECODE(EI.COST_DIST_REJECTION_CODE
, NULL
, 'NO_COST_DIST'
, EI.COST_DIST_REJECTION_CODE) EXCEPTION_CODE
, EI.QUANTITY QUANTITY
, EI.DENOM_CURRENCY_CODE
, EI.DENOM_RAW_COST
, EI.DENOM_BURDENED_COST
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RAW_COST
, EI.ACCT_BURDENED_COST
, EI.PROJECT_CURRENCY_CODE
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') EXCEPTION_REASON
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') CORRECTIVE_ACTION
, EI.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, EMP.FULL_NAME PERSON_NAME
, EXP.INCURRED_BY_PERSON_ID PERSON_ID
, EXP.EXPENDITURE_GROUP EXPENDITURE_BATCH
, NULL VENDOR_ID
, NULL VENDOR_NAME
, NULL VENDOR_NUMBER
, EI.SYSTEM_LINKAGE_FUNCTION
, EI.PRVDR_ACCRUAL_DATE
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
, PA_PROJECTS_ALL PP
, PA_TASKS TT
, PA_EXPENDITURES_ALL EXP
, PER_PEOPLE_F EMP
WHERE EI.COST_DISTRIBUTED_FLAG = 'N'
AND EI.TASK_ID = TT.TASK_ID
AND TT.PROJECT_ID = PP.PROJECT_ID
AND EMP.PERSON_ID(+) = EXP.INCURRED_BY_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
AND EXP.EXPENDITURE_ID(+) = EI.EXPENDITURE_ID
AND ( (EI.SYSTEM_LINKAGE_FUNCTION <> 'PJ'
AND EI.EXPENDITURE_ITEM_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE) OR ( EI.SYSTEM_LINKAGE_FUNCTION = 'PJ'
AND NVL(EI.PRVDR_ACCRUAL_DATE
, EI.EXPENDITURE_ITEM_DATE) BETWEEN PRD.START_DATE
AND PRD.END_DATE) )
AND PRD.APPLICATION_ID = 8721
AND PRD.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND PRD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND NVL(EI.ORG_ID
, -99) =IMP.ORG_ID UNION SELECT PP.PROJECT_ID
, PP.NAME
, EI.TASK_ID TASK_ID
, TT.TASK_NAME
, EI.EXPENDITURE_ID EXPENDITURE_ID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID)
, PA_EXPENDITURES_UTILS.GETORGTLNAME(NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID))
, PRD.PERIOD_NAME PERIOD_NAME
, DECODE(EI.COST_DIST_REJECTION_CODE
, NULL
, 'NO_COST_DIST'
, EI.COST_DIST_REJECTION_CODE) EXCEPTION_CODE
, EI.QUANTITY QUANTITY
, EI.DENOM_CURRENCY_CODE
, EI.DENOM_RAW_COST
, EI.DENOM_BURDENED_COST
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RAW_COST
, EI.ACCT_BURDENED_COST
, EI.PROJECT_CURRENCY_CODE
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') EXCEPTION_REASON
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') CORRECTIVE_ACTION
, EI.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, NULL PERSON_NAME
, EXP.INCURRED_BY_PERSON_ID PERSON_ID
, EXP.EXPENDITURE_GROUP EXPENDITURE_BATCH
, CDL.SYSTEM_REFERENCE1 VENDOR_ID
, VND.VENDOR_NAME VENDOR_NAME
, VND.SEGMENT1 VENDOR_NUMBER
, EI.SYSTEM_LINKAGE_FUNCTION
, EI.PRVDR_ACCRUAL_DATE
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
, PA_PROJECTS_ALL PP
, PA_TASKS TT
, PA_EXPENDITURES_ALL EXP
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PO_VENDORS VND
WHERE EI.COST_DISTRIBUTED_FLAG = 'N'
AND EI.TASK_ID = TT.TASK_ID
AND TT.PROJECT_ID = PP.PROJECT_ID
AND NVL(EXP.INCURRED_BY_PERSON_ID
, 0) = 0
AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND ( (EI.SYSTEM_LINKAGE_FUNCTION <> 'PJ'
AND EI.EXPENDITURE_ITEM_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE) OR ( EI.SYSTEM_LINKAGE_FUNCTION = 'PJ'
AND NVL(EI.PRVDR_ACCRUAL_DATE
, EI.EXPENDITURE_ITEM_DATE) BETWEEN PRD.START_DATE
AND PRD.END_DATE) )
AND PRD.APPLICATION_ID = 8721
AND PRD.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND PRD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND NVL(EI.ORG_ID
, -99) =IMP.ORG_ID
AND CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID(+)
AND CDL.LINE_TYPE = 'R'
AND EI.TRANSACTION_SOURCE IN ('AP EXPENSE'
, 'AP INVOICE')
AND EI.SYSTEM_LINKAGE_FUNCTION IN ('VI'
, 'ER')
AND CDL.SYSTEM_REFERENCE1 IS NOT NULL
AND CDL.SYSTEM_REFERENCE1 = VND.VENDOR_ID UNION SELECT PP.PROJECT_ID
, PP.NAME
, EI.TASK_ID TASK_ID
, TT.TASK_NAME
, EI.EXPENDITURE_ID EXPENDITURE_ID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID)
, PA_EXPENDITURES_UTILS.GETORGTLNAME(NVL(EI.ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID))
, PRD.PERIOD_NAME PERIOD_NAME
, DECODE(EI.COST_DIST_REJECTION_CODE
, NULL
, 'NO_COST_DIST'
, EI.COST_DIST_REJECTION_CODE) EXCEPTION_CODE
, EI.QUANTITY QUANTITY
, EI.DENOM_CURRENCY_CODE
, EI.DENOM_RAW_COST
, EI.DENOM_BURDENED_COST
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RAW_COST
, EI.ACCT_BURDENED_COST
, EI.PROJECT_CURRENCY_CODE
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'R'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') EXCEPTION_REASON
, (SELECT DECODE(PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A')
, 'UNDEFINED'
, LKP.MEANING
, NULL
, LKP.MEANING
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT ('COST_EXCP'
, EI.COST_DIST_REJECTION_CODE
, NULL
, 'A'))
FROM PA_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'UNCOST_EXCP'
AND LKP.LOOKUP_CODE = 'NO_COST_DIST') CORRECTIVE_ACTION
, EI.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, NULL PERSON_NAME
, EXP.INCURRED_BY_PERSON_ID PERSON_ID
, EXP.EXPENDITURE_GROUP EXPENDITURE_BATCH
, NULL VENDOR_ID
, NULL VENDOR_NAME
, NULL VENDOR_NUMBER
, EI.SYSTEM_LINKAGE_FUNCTION
, EI.PRVDR_ACCRUAL_DATE
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
, PA_PROJECTS_ALL PP
, PA_TASKS TT
, PA_EXPENDITURES_ALL EXP
WHERE EI.COST_DISTRIBUTED_FLAG = 'N'
AND EI.TASK_ID = TT.TASK_ID
AND TT.PROJECT_ID = PP.PROJECT_ID
AND EXP.INCURRED_BY_PERSON_ID IS NULL
AND EXP.EXPENDITURE_ID(+) = EI.EXPENDITURE_ID
AND ( (EI.SYSTEM_LINKAGE_FUNCTION <> 'PJ'
AND EI.EXPENDITURE_ITEM_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE) OR ( EI.SYSTEM_LINKAGE_FUNCTION = 'PJ'
AND NVL(EI.PRVDR_ACCRUAL_DATE
, EI.EXPENDITURE_ITEM_DATE) BETWEEN PRD.START_DATE
AND PRD.END_DATE) )
AND PRD.APPLICATION_ID = 8721
AND PRD.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND PRD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND NVL(EI.ORG_ID
, -99) =IMP.ORG_ID
AND ((EI.SYSTEM_LINKAGE_FUNCTION NOT IN ('VI'
, 'ER')) OR (NOT EXISTS (SELECT 1
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL
WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CDL.LINE_TYPE = 'R'
AND CDL.SYSTEM_REFERENCE1 IS NOT NULL)))

Columns

Name
PROJECT_ID
PROJECT_NAME
TASK_ID
TASK_NAME
EXPENDITURE_ID
EXPENDITURE_ITEM_ID
EXPENDITURE_ITEM_DATE
EXPENDITURE_TYPE
EXP_ORGANIZATION_ID
EXP_ORG_NAME
PERIOD_NAME
EXCEPTION_CODE
QUANTITY
DENOM_CURRENCY_CODE
DENOM_RAW_AMOUNT
DENOM_BURDENED_AMOUNT
ACCT_CURRENCY_CODE
ACCT_RAW_AMOUNT
ACCT_BURDENED_AMOUNT
PROJECT_CURRENCY_CODE
EXCEPTION_REASON
CORRECTIVE_ACTION
ORG_ID
SAME_PA_GL_PERIOD
OU_NAME
SET_OF_BOOKS_ID
PERSON_NAME
PERSON_ID
EXPENDITURE_BATCH
VENDOR_ID
VENDOR_NAME
VENDOR_NUMBER
SYSTEM_LINKAGE_FUNCTION
PRVDR_ACCRUAL_DATE