VIEW: APPS.PA_GL_COST_EXCEPT_SUM_V

Source

View Text - Preformatted

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.GL_DATE GL_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_distribution_id invoice_distribution_id, ei.document_line_number invoice_line_number, ei.document_payment_id invoice_payment_id, ei.document_type document_type, ei.document_distribution_type document_distribution_type, cdl.si_assets_addition_flag si_assets_addition_flag, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP', DECODE ( EXC_TAB.EXC_TYPE , 'COST_EXC' , CDL.TRANSFER_STATUS_CODE, decode(BURDEN_SUM_REJECTION_CODE, null, 'BURDEN_NEW_UNPROC', BURDEN_SUM_REJECTION_CODE) ), TRANSFER_REJECTION_REASON, 'R') EXCEPTION_REASON, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP', DECODE ( EXC_TAB.EXC_TYPE , 'COST_EXC' , CDL.TRANSFER_STATUS_CODE, decode(BURDEN_SUM_REJECTION_CODE, null, 'BURDEN_NEW_UNPROC', BURDEN_SUM_REJECTION_CODE) ), TRANSFER_REJECTION_REASON, 'A') CORRECTIVE_ACTION, DECODE ( EXC_TAB.EXC_TYPE , 'COST_EXC' , DECODE(EI.SYSTEM_LINKAGE_FUNCTION, 'ER','P','VI','P', 'I'), 'B') CDL_EXCEPTION_TYPE, CDL.ORG_ID ORG_ID, IMP.SAME_PA_GL_PERIOD, PA_EXPENDITURES_UTILS.GetOrgTlName(cdl.org_id), IMP.SET_OF_BOOKS_ID, EI.UNIT_OF_MEASURE FROM PA_COST_DISTRIBUTION_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_IMPLEMENTATIONS_ALL IMP, GL_PERIOD_STATUSES PRD , (select 'COST_EXC' EXC_TYPE from sys.dual UNION ALL select 'BURD_EXC' EXC_TYPE from sys.dual ) EXC_TAB WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND ( ( EXC_TAB.EXC_TYPE = 'COST_EXC' AND CDL.TRANSFER_STATUS_CODE in ('P','R','X') AND ( ( EI.SYSTEM_LINKAGE_FUNCTION IN ('ST', 'OT') AND IMP.INTERFACE_LABOR_TO_GL_FLAG = 'Y' AND EXC_TAB.EXC_TYPE = 'COST_EXC' ) 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') ) ) ) OR ( EXC_TAB.EXC_TYPE = 'BURD_EXC' AND CDL.LINE_TYPE='R' AND CDL.BURDEN_SUM_SOURCE_RUN_ID = -9999 AND CDL.IND_COMPILED_SET_ID > 0 AND NVL(CDL.AMOUNT,0) <> 0 AND EXISTS (select null FROM PA_PROJECT_TYPES_ALL PPTA , PA_PROJECTS_ALL PPA WHERE CDL.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 CDL.GL_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 CDL.org_id = IMP.org_id union 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.GL_DATE GL_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_distribution_id invoice_distribution_id, ei.document_line_number invoice_line_number, ei.document_payment_id invoice_payment_id, ei.document_type document_type, ei.document_distribution_type document_distribution_type, cdl.si_assets_addition_flag si_assets_addition_flag, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP', DECODE ( EXC_TAB.EXC_TYPE , 'COST_EXC' , 'FA', DECODE(BURDEN_SUM_REJECTION_CODE, NULL, 'BURDEN_NEW_UNPROC', BURDEN_SUM_REJECTION_CODE) ), TRANSFER_REJECTION_REASON, 'R') EXCEPTION_REASON, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP', DECODE ( EXC_TAB.EXC_TYPE , 'COST_EXC' , 'FA', DECODE(BURDEN_SUM_REJECTION_CODE, NULL, 'BURDEN_NEW_UNPROC', BURDEN_SUM_REJECTION_CODE) ), TRANSFER_REJECTION_REASON, 'A') CORRECTIVE_ACTION, 'Z' CDL_EXCEPTION_TYPE, CDL.ORG_ID ORG_ID, IMP.SAME_PA_GL_PERIOD, PA_EXPENDITURES_UTILS.GetOrgTlName(cdl.org_id), IMP.SET_OF_BOOKS_ID, EI.UNIT_OF_MEASURE FROM PA_COST_DISTRIBUTION_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_IMPLEMENTATIONS_ALL IMP, GL_PERIOD_STATUSES PRD , XLA_EVENTS XE, (SELECT 'COST_EXC' EXC_TYPE FROM sys.dual UNION ALL SELECT 'BURD_EXC' EXC_TYPE FROM sys.dual ) EXC_TAB WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID AND (XE.EVENT_STATUS_CODE <> 'P' OR xE.PROCESS_STATUS_CODE <> 'P') AND ( ( EXC_TAB.EXC_TYPE = 'COST_EXC' AND CDL.TRANSFER_STATUS_CODE ='A' AND ( ( EI.SYSTEM_LINKAGE_FUNCTION IN ('ST', 'OT') AND IMP.INTERFACE_LABOR_TO_GL_FLAG = 'Y' AND EXC_TAB.EXC_TYPE = 'COST_EXC' ) 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') ) ) ) OR ( EXC_TAB.EXC_TYPE = 'BURD_EXC' AND CDL.LINE_TYPE ='R' AND CDL.BURDEN_SUM_SOURCE_RUN_ID = -9999 AND CDL.IND_COMPILED_SET_ID > 0 AND NVL(CDL.AMOUNT,0) <> 0 AND EXISTS (SELECT NULL FROM PA_PROJECT_TYPES_ALL PPTA , PA_PROJECTS_ALL PPA WHERE CDL.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 CDL.GL_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 CDL.org_id = IMP.org_id

View Text - HTML Formatted

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.GL_DATE GL_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_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, EI.DOCUMENT_LINE_NUMBER INVOICE_LINE_NUMBER
, EI.DOCUMENT_PAYMENT_ID INVOICE_PAYMENT_ID
, EI.DOCUMENT_TYPE DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE DOCUMENT_DISTRIBUTION_TYPE
, CDL.SI_ASSETS_ADDITION_FLAG SI_ASSETS_ADDITION_FLAG
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE ( EXC_TAB.EXC_TYPE
, 'COST_EXC'
, CDL.TRANSFER_STATUS_CODE
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE) )
, TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE ( EXC_TAB.EXC_TYPE
, 'COST_EXC'
, CDL.TRANSFER_STATUS_CODE
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE) )
, TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, DECODE ( EXC_TAB.EXC_TYPE
, 'COST_EXC'
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ER'
, 'P'
, 'VI'
, 'P'
, 'I')
, 'B') CDL_EXCEPTION_TYPE
, CDL.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(CDL.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, EI.UNIT_OF_MEASURE
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
, (SELECT 'COST_EXC' EXC_TYPE
FROM SYS.DUAL UNION ALL SELECT 'BURD_EXC' EXC_TYPE
FROM SYS.DUAL ) EXC_TAB
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND ( ( EXC_TAB.EXC_TYPE = 'COST_EXC'
AND CDL.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X')
AND ( ( EI.SYSTEM_LINKAGE_FUNCTION IN ('ST'
, 'OT')
AND IMP.INTERFACE_LABOR_TO_GL_FLAG = 'Y'
AND EXC_TAB.EXC_TYPE = 'COST_EXC' ) 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') ) ) ) OR ( EXC_TAB.EXC_TYPE = 'BURD_EXC'
AND CDL.LINE_TYPE='R'
AND CDL.BURDEN_SUM_SOURCE_RUN_ID = -9999
AND CDL.IND_COMPILED_SET_ID > 0
AND NVL(CDL.AMOUNT
, 0) <> 0
AND EXISTS (SELECT NULL
FROM PA_PROJECT_TYPES_ALL PPTA
, PA_PROJECTS_ALL PPA
WHERE CDL.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 CDL.GL_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 CDL.ORG_ID = IMP.ORG_ID UNION 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.GL_DATE GL_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_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID
, EI.DOCUMENT_LINE_NUMBER INVOICE_LINE_NUMBER
, EI.DOCUMENT_PAYMENT_ID INVOICE_PAYMENT_ID
, EI.DOCUMENT_TYPE DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE DOCUMENT_DISTRIBUTION_TYPE
, CDL.SI_ASSETS_ADDITION_FLAG SI_ASSETS_ADDITION_FLAG
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE ( EXC_TAB.EXC_TYPE
, 'COST_EXC'
, 'FA'
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE) )
, TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('COST_EXCP'
, DECODE ( EXC_TAB.EXC_TYPE
, 'COST_EXC'
, 'FA'
, DECODE(BURDEN_SUM_REJECTION_CODE
, NULL
, 'BURDEN_NEW_UNPROC'
, BURDEN_SUM_REJECTION_CODE) )
, TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, 'Z' CDL_EXCEPTION_TYPE
, CDL.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(CDL.ORG_ID)
, IMP.SET_OF_BOOKS_ID
, EI.UNIT_OF_MEASURE
FROM PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
, XLA_EVENTS XE
, (SELECT 'COST_EXC' EXC_TYPE
FROM SYS.DUAL UNION ALL SELECT 'BURD_EXC' EXC_TYPE
FROM SYS.DUAL ) EXC_TAB
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID
AND (XE.EVENT_STATUS_CODE <> 'P' OR XE.PROCESS_STATUS_CODE <> 'P')
AND ( ( EXC_TAB.EXC_TYPE = 'COST_EXC'
AND CDL.TRANSFER_STATUS_CODE ='A'
AND ( ( EI.SYSTEM_LINKAGE_FUNCTION IN ('ST'
, 'OT')
AND IMP.INTERFACE_LABOR_TO_GL_FLAG = 'Y'
AND EXC_TAB.EXC_TYPE = 'COST_EXC' ) 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') ) ) ) OR ( EXC_TAB.EXC_TYPE = 'BURD_EXC'
AND CDL.LINE_TYPE ='R'
AND CDL.BURDEN_SUM_SOURCE_RUN_ID = -9999
AND CDL.IND_COMPILED_SET_ID > 0
AND NVL(CDL.AMOUNT
, 0) <> 0
AND EXISTS (SELECT NULL
FROM PA_PROJECT_TYPES_ALL PPTA
, PA_PROJECTS_ALL PPA
WHERE CDL.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 CDL.GL_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 CDL.ORG_ID = IMP.ORG_ID