VIEW: APPS.PA_CC_EXCEPTIONS_SUM_V

Source

View Text - Preformatted

SELECT CDL.project_id project_id , PP.segment1 project_number , CDL.task_id task_id , EI.recvr_org_id recvr_org_id , otl.name recvr_org_name , EXP.INCURRED_BY_PERSON_ID employee_id , exp.vendor_id vendor_id , EI.organization_id non_labor_org_id , nvl(EI.OVERRIDE_TO_ORGANIZATION_ID,EXP.INCURRED_BY_ORGANIZATION_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 CDL_LINE_NUM , CDL.PA_DATE PA_DATE , CDL.ACCT_CURRENCY_CODE , CDL.AMOUNT , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP', CDL.TRANSFER_REJECTION_CODE, null, 'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP', CDL.TRANSFER_rejection_CODE, null, 'A') CORRECTIVE_ACTION ,nvl(CDL.TRANSFER_REJECTION_CODE,CDL.TRANSFER_STATUS_CODE) ,ei.system_linkage_function ,'I' cdl_exception_type ,cdl.PA_PERIOD_NAME period_name ,PP.org_id FROM PA_CC_DIST_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_EXPENDITURES_ALL EXP, PA_PROJECTS PP, hr_all_organization_units_tl otl WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EXP.EXPENDITURE_ID=EI.EXPENDITURE_ID AND CDL.project_id=pp.project_id AND EI.recvr_org_id=otl.organization_id(+) AND decode(otl.organization_id,null,'1',otl.language)= decode(otl.organization_id,null,'1',userenv('lang')) AND CDL.TRANSFER_STATUS_CODE IN ('P','R','X') UNION SELECT CDL.project_id project_id , PP.segment1 project_number , CDL.task_id task_id , EI.recvr_org_id recvr_org_id , otl.name recvr_org_name , EXP.INCURRED_BY_PERSON_ID employee_id , exp.vendor_id vendor_id , EI.organization_id non_labor_org_id , NVL(EI.OVERRIDE_TO_ORGANIZATION_ID,EXP.INCURRED_BY_ORGANIZATION_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 CDL_LINE_NUM , CDL.PA_DATE PA_DATE , CDL.ACCT_CURRENCY_CODE , CDL.AMOUNT , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP', 'FA', NULL, 'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP', 'FA', NULL, 'A') CORRECTIVE_ACTION , NVL(CDL.TRANSFER_REJECTION_CODE,CDL.TRANSFER_STATUS_CODE) , ei.system_linkage_function , 'Z' cdl_exception_type , cdl.PA_PERIOD_NAME period_name , PP.org_id FROM PA_CC_DIST_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_EXPENDITURES_ALL EXP, PA_PROJECTS PP, hr_all_organization_units_tl otl, xla_events xe WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EXP.EXPENDITURE_ID =EI.EXPENDITURE_ID AND cdl.acct_event_id = xe.event_id AND CDL.project_id =pp.project_id AND EI.recvr_org_id =otl.organization_id(+) AND DECODE(otl.organization_id,NULL,'1',otl.language)= DECODE(otl.organization_id,NULL,'1',userenv('lang')) AND CDL.TRANSFER_STATUS_CODE ='A' and (xe.event_status_code<>'P' or xe.process_status_code<>'P')

View Text - HTML Formatted

SELECT CDL.PROJECT_ID PROJECT_ID
, PP.SEGMENT1 PROJECT_NUMBER
, CDL.TASK_ID TASK_ID
, EI.RECVR_ORG_ID RECVR_ORG_ID
, OTL.NAME RECVR_ORG_NAME
, EXP.INCURRED_BY_PERSON_ID EMPLOYEE_ID
, EXP.VENDOR_ID VENDOR_ID
, EI.ORGANIZATION_ID NON_LABOR_ORG_ID
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, EXP.INCURRED_BY_ORGANIZATION_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 CDL_LINE_NUM
, CDL.PA_DATE PA_DATE
, CDL.ACCT_CURRENCY_CODE
, CDL.AMOUNT
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP'
, CDL.TRANSFER_REJECTION_CODE
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP'
, CDL.TRANSFER_REJECTION_CODE
, NULL
, 'A') CORRECTIVE_ACTION
, NVL(CDL.TRANSFER_REJECTION_CODE
, CDL.TRANSFER_STATUS_CODE)
, EI.SYSTEM_LINKAGE_FUNCTION
, 'I' CDL_EXCEPTION_TYPE
, CDL.PA_PERIOD_NAME PERIOD_NAME
, PP.ORG_ID
FROM PA_CC_DIST_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PA_PROJECTS PP
, HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EXP.EXPENDITURE_ID=EI.EXPENDITURE_ID
AND CDL.PROJECT_ID=PP.PROJECT_ID
AND EI.RECVR_ORG_ID=OTL.ORGANIZATION_ID(+)
AND DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, OTL.LANGUAGE)= DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND CDL.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X') UNION SELECT CDL.PROJECT_ID PROJECT_ID
, PP.SEGMENT1 PROJECT_NUMBER
, CDL.TASK_ID TASK_ID
, EI.RECVR_ORG_ID RECVR_ORG_ID
, OTL.NAME RECVR_ORG_NAME
, EXP.INCURRED_BY_PERSON_ID EMPLOYEE_ID
, EXP.VENDOR_ID VENDOR_ID
, EI.ORGANIZATION_ID NON_LABOR_ORG_ID
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, EXP.INCURRED_BY_ORGANIZATION_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 CDL_LINE_NUM
, CDL.PA_DATE PA_DATE
, CDL.ACCT_CURRENCY_CODE
, CDL.AMOUNT
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP'
, 'FA'
, NULL
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('CC_EXCP'
, 'FA'
, NULL
, 'A') CORRECTIVE_ACTION
, NVL(CDL.TRANSFER_REJECTION_CODE
, CDL.TRANSFER_STATUS_CODE)
, EI.SYSTEM_LINKAGE_FUNCTION
, 'Z' CDL_EXCEPTION_TYPE
, CDL.PA_PERIOD_NAME PERIOD_NAME
, PP.ORG_ID
FROM PA_CC_DIST_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, PA_PROJECTS PP
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, XLA_EVENTS XE
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EXP.EXPENDITURE_ID =EI.EXPENDITURE_ID
AND CDL.ACCT_EVENT_ID = XE.EVENT_ID
AND CDL.PROJECT_ID =PP.PROJECT_ID
AND EI.RECVR_ORG_ID =OTL.ORGANIZATION_ID(+)
AND DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, OTL.LANGUAGE)= DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND CDL.TRANSFER_STATUS_CODE ='A'
AND (XE.EVENT_STATUS_CODE<>'P' OR XE.PROCESS_STATUS_CODE<>'P')