VIEW: APPS.PA_GL_CC_EXCEPT_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.GL_DATE GL_DATE , PRD.PERIOD_NAME PERIOD_NAME , 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.org_id ORG_ID ,IMP.same_pa_gl_period ,PA_EXPENDITURES_UTILS.getorgtlname(cdl.org_id) ,imp.set_of_books_id FROM PA_CC_DIST_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_EXPENDITURES_ALL EXP, GL_PERIOD_STATUSES PRD, PA_PROJECTS_ALL PP, hr_all_organization_units_tl otl, PA_IMPLEMENTATIONS_ALL imp WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EXP.EXPENDITURE_ID=EI.EXPENDITURE_ID AND CDL.project_id=pp.project_id AND nvl(pp.org_id,-99) = nvl(cdl.org_id,-99) 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') 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 NVL(CDL.org_id,-99) = IMP.org_id 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.GL_DATE GL_DATE , PRD.PERIOD_NAME PERIOD_NAME , 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.org_id ORG_ID , IMP.same_pa_gl_period , PA_EXPENDITURES_UTILS.getorgtlname(cdl.org_id) , imp.set_of_books_id FROM PA_CC_DIST_LINES_ALL CDL, PA_EXPENDITURE_ITEMS_ALL EI, PA_EXPENDITURES_ALL EXP, GL_PERIOD_STATUSES PRD, PA_PROJECTS_ALL PP, hr_all_organization_units_tl otl, PA_IMPLEMENTATIONS_ALL imp, XLA_EVENTS XE WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EXP.EXPENDITURE_ID =EI.EXPENDITURE_ID AND CDL.project_id =pp.project_id AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID AND (XE.EVENT_STATUS_CODE <> 'P' OR XE.PROCESS_STATUS_CODE <> 'P') AND NVL(pp.org_id,-99) = NVL(cdl.org_id,-99) 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 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 NVL(CDL.org_id,-99) = IMP.org_id

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.GL_DATE GL_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, 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.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(CDL.ORG_ID)
, IMP.SET_OF_BOOKS_ID
FROM PA_CC_DIST_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, GL_PERIOD_STATUSES PRD
, PA_PROJECTS_ALL PP
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, PA_IMPLEMENTATIONS_ALL IMP
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EXP.EXPENDITURE_ID=EI.EXPENDITURE_ID
AND CDL.PROJECT_ID=PP.PROJECT_ID
AND NVL(PP.ORG_ID
, -99) = NVL(CDL.ORG_ID
, -99)
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')
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 NVL(CDL.ORG_ID
, -99) = IMP.ORG_ID 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.GL_DATE GL_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, 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.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(CDL.ORG_ID)
, IMP.SET_OF_BOOKS_ID
FROM PA_CC_DIST_LINES_ALL CDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL EXP
, GL_PERIOD_STATUSES PRD
, PA_PROJECTS_ALL PP
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, PA_IMPLEMENTATIONS_ALL IMP
, XLA_EVENTS XE
WHERE EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EXP.EXPENDITURE_ID =EI.EXPENDITURE_ID
AND CDL.PROJECT_ID =PP.PROJECT_ID
AND CDL.ACCT_EVENT_ID(+) = XE.EVENT_ID
AND (XE.EVENT_STATUS_CODE <> 'P' OR XE.PROCESS_STATUS_CODE <> 'P')
AND NVL(PP.ORG_ID
, -99) = NVL(CDL.ORG_ID
, -99)
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 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 NVL(CDL.ORG_ID
, -99) = IMP.ORG_ID