VIEW: APPS.PA_COST_EXCEPTIONS_SUM_V

Source

View Text - Preformatted

SELECT /*+ index(CDL PA_COST_DISTRIBUTION_LINES_N7) LEADING(IMP, CDL, EI) USE_NL(EI) */ 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 AND PRD.ORG_ID = EI.ORG_ID UNION SELECT /*+ index(CDL PA_COST_DISTRIBUTION_LINES_N7) LEADING(IMP, CDL, EI) USE_NL(EI) */ 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', 'FA', TRANSFER_REJECTION_REASON, 'R') EXCEPTION_REASON, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP', 'FA', TRANSFER_REJECTION_REASON, 'A') CORRECTIVE_ACTION, 'Z' 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, XLA_EVENTS XE WHERE CDL.TRANSFER_STATUS_CODE ='A' AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID AND (XE.EVENT_STATUS_CODE <> 'P' OR XE.PROCESS_STATUS_CODE <> 'P') 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 AND PRD.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

View Text - HTML Formatted

SELECT /*+ INDEX(CDL PA_COST_DISTRIBUTION_LINES_N7) LEADING(IMP
, CDL
, EI) USE_NL(EI) */ 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
AND PRD.ORG_ID = EI.ORG_ID UNION SELECT /*+ INDEX(CDL PA_COST_DISTRIBUTION_LINES_N7) LEADING(IMP
, CDL
, EI) USE_NL(EI) */ 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'
, 'FA'
, TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, 'FA'
, TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, 'Z' 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
, XLA_EVENTS XE
WHERE CDL.TRANSFER_STATUS_CODE ='A'
AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID
AND (XE.EVENT_STATUS_CODE <> 'P' OR XE.PROCESS_STATUS_CODE <> 'P')
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
AND PRD.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