View: PA_COST_EXCEPTIONS_SUM_V

Product:	PA - Projects
Description:	Identifies the details Cost Distribution Lines that prevent a period from being closed.
Implementation/DBA Data:	APPS.PA_COST_EXCEPTIONS_SUM_V

View Text

SELECT EI.TASK_ID TASK_ID
, EI.EXPENDITURE_ID EXPENDITURE_ID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, CDL.LINE_NUM COST_DISTRIBUTION_LINE_NUM
, CDL.PA_DATE PA_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, CDL.TRANSFER_STATUS_CODE TRANSFER_STATUS_CODE
, CDL.TRANSFER_REJECTION_REASON TRANSFER_REJECTION_REASON
, CDL.QUANTITY QUANTITY
, CDL.ACCT_RAW_COST AMOUNT
, CDL.DENOM_RAW_COST DENOM_AMOUNT
, CDL.AMOUNT PROJECT_AMOUNT
, CDL.DENOM_CURRENCY_CODE DENOM_CURRENCY_CODE
, EI.VENDOR_ID VENDOR_ID
, EI.DOCUMENT_HEADER_ID INVOICE_ID
, EI.DOCUMENT_LINE_NUMBER INVOICE_LINE_NUMBER
, EI.DOCUMENT_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, CDL.TRANSFER_STATUS_CODE
, TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, CDL.TRANSFER_STATUS_CODE
, TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ER'
, 'P'
, 'VI'
, 'P'
, 'I') CDL_EXCEPTION_TYPE
, IMP.ORG_ID
, EI.UNIT_OF_MEASURE
, EI.DOCUMENT_PAYMENT_ID INVOICE_PAYMENT_ID
, EI.DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE
, CDL.SI_ASSETS_ADDITION_FLAG
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS IMP
, PA_PERIODS_ALL PRD
WHERE CDL.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X')
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND ((EI.SYSTEM_LINKAGE_FUNCTION IN ('ST'
, 'OT')
AND IMP.INTERFACE_LABOR_TO_GL_FLAG = 'Y') OR (EI.SYSTEM_LINKAGE_FUNCTION IN ('USG'
, 'PJ'
, 'BTC'
, 'INV'
, 'WIP')
AND IMP.INTERFACE_USAGE_TO_GL_FLAG = 'Y') OR (EI.SYSTEM_LINKAGE_FUNCTION IN ('ER'
, 'VI')))
AND CDL.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND IMP.ORG_ID = EI.ORG_ID UNION SELECT /*+ INDEX(CDL PA_COST_DISTRIBUTION_LINES_N7) */ EI.TASK_ID TASK_ID
, EI.EXPENDITURE_ID EXPENDITURE_ID
, EI.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE EXPENDITURE_TYPE
, CDL.LINE_NUM COST_DISTRIBUTION_LINE_NUM
, CDL.PA_DATE PA_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, CDL.TRANSFER_STATUS_CODE TRANSFER_STATUS_CODE
, CDL.TRANSFER_REJECTION_REASON TRANSFER_REJECTION_REASON
, CDL.QUANTITY QUANTITY
, CDL.ACCT_RAW_COST AMOUNT
, CDL.DENOM_RAW_COST DENOM_AMOUNT
, CDL.AMOUNT PROJECT_AMOUNT
, CDL.DENOM_CURRENCY_CODE DENOM_CURRENCY_CODE
, NULL VENDOR_ID
, NULL INVOICE_ID
, NULL INVOICE_LINE_NUMBER
, NULL INVOICE_DISTRIBUTION_ID
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE)
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE)
, NULL
, 'A') CORRECTIVE_ACTION
, 'B' CDL_EXCEPTION_TYPE
, CDL.ORG_ID
, EI.UNIT_OF_MEASURE
, NULL DOCUMENT_PAYMENT_ID
, NULL DOCUMENT_TYPE
, NULL DOCUMENT_DISTRIBUTION_TYPE
, CDL.SI_ASSETS_ADDITION_FLAG
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_PERIODS PRD
WHERE CDL.BURDEN_SUM_SOURCE_RUN_ID = -9999
AND CDL.LINE_TYPE = 'R'
AND NVL(CDL.AMOUNT
, 0) <> 0
AND CDL.IND_COMPILED_SET_ID <> -1
AND EXISTS ( SELECT NULL
FROM PA_PROJECT_TYPES_ALL PPTA
, PA_PROJECTS_ALL PPA
WHERE EI.PROJECT_ID = PPA.PROJECT_ID
AND NVL(PPA.ORG_ID
, -99) = NVL(PPTA.ORG_ID
, -99)
AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
AND PPTA.BURDEN_COST_FLAG IN ('Y'
, 'Y')
AND ( PPTA.BURDEN_AMT_DISPLAY_METHOD IN ('D'
, 'D') OR PPTA.BURDEN_AMT_DISPLAY_METHOD IN ('S'
, 'S')
AND PPTA.BURDEN_ACCOUNT_FLAG IN ('Y'
, 'Y') )
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(PPA.PROJECT_STATUS_CODE
, 'GENERATE_BURDEN') = 'Y' )
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND PRD.ORG_ID = EI.ORG_ID
AND CDL.PA_DATE IS NOT NULL

Columns