VIEW: APPS.PA_CC_EXCEPTIONS_DET_V

Source

View Text - Preformatted

SELECT psv.project_id project_id , psv.project_number project_number , psv.task_id task_id , pt.task_number , psv.recvr_org_id recvr_org_id , psv.recvr_org_name recvr_org_name , psv.employee_id , pf.full_name employee_name , psv.vendor_id vendor_id , pv.vendor_name vendor_name , psv.non_labor_org_id non_labor_org_id , otl1.name non_labor_org_name , psv.expnd_org_id expnd_org_id , otl2.name expnd_org_name , psv.EXPENDITURE_ID , psv.EXPENDITURE_ITEM_ID , psv.EXPENDITURE_ITEM_DATE , psv.EXPENDITURE_TYPE , psv.CDL_LINE_NUM , psv.PA_DATE , psv.PERIOD_NAME , psv.ACCT_CURRENCY_CODE , psv.AMOUNT , psv.EXCEPTION_REASON , psv.CORRECTIVE_ACTION , psv.exception_code , psv.system_linkage_function FROM pa_cc_exceptions_sum_v psv, pa_tasks pt, per_people_f pf, po_vendors pv, hr_all_organization_units_tl otl1, hr_all_organization_units_tl otl2 WHERE psv.task_id=pt.task_id AND psv.employee_id=pf.person_id(+) AND psv.vendor_id=pv.vendor_id(+) AND psv.non_labor_org_id=otl1.organization_id(+) AND decode(otl1.organization_id,null,'1',otl1.language)= decode(otl1.organization_id,null,'1',userenv('lang')) AND psv.expnd_org_id=otl2.organization_id(+) AND decode(otl2.organization_id,null,'1',otl2.language)= decode(otl2.organization_id,null,'1',userenv('lang')) AND exists (select null from pa_cc_dist_lines_all where expenditure_item_id = psv.expenditure_item_id and line_num = psv.cdl_line_num and (line_type = 'BL' or ( line_type = 'PC' and reference_1 in (select draft_invoice_detail_id from pa_draft_invoice_Details_all where expenditure_item_id = psv.expenditure_item_id and draft_invoice_num is not null))))

View Text - HTML Formatted

SELECT PSV.PROJECT_ID PROJECT_ID
, PSV.PROJECT_NUMBER PROJECT_NUMBER
, PSV.TASK_ID TASK_ID
, PT.TASK_NUMBER
, PSV.RECVR_ORG_ID RECVR_ORG_ID
, PSV.RECVR_ORG_NAME RECVR_ORG_NAME
, PSV.EMPLOYEE_ID
, PF.FULL_NAME EMPLOYEE_NAME
, PSV.VENDOR_ID VENDOR_ID
, PV.VENDOR_NAME VENDOR_NAME
, PSV.NON_LABOR_ORG_ID NON_LABOR_ORG_ID
, OTL1.NAME NON_LABOR_ORG_NAME
, PSV.EXPND_ORG_ID EXPND_ORG_ID
, OTL2.NAME EXPND_ORG_NAME
, PSV.EXPENDITURE_ID
, PSV.EXPENDITURE_ITEM_ID
, PSV.EXPENDITURE_ITEM_DATE
, PSV.EXPENDITURE_TYPE
, PSV.CDL_LINE_NUM
, PSV.PA_DATE
, PSV.PERIOD_NAME
, PSV.ACCT_CURRENCY_CODE
, PSV.AMOUNT
, PSV.EXCEPTION_REASON
, PSV.CORRECTIVE_ACTION
, PSV.EXCEPTION_CODE
, PSV.SYSTEM_LINKAGE_FUNCTION
FROM PA_CC_EXCEPTIONS_SUM_V PSV
, PA_TASKS PT
, PER_PEOPLE_F PF
, PO_VENDORS PV
, HR_ALL_ORGANIZATION_UNITS_TL OTL1
, HR_ALL_ORGANIZATION_UNITS_TL OTL2
WHERE PSV.TASK_ID=PT.TASK_ID
AND PSV.EMPLOYEE_ID=PF.PERSON_ID(+)
AND PSV.VENDOR_ID=PV.VENDOR_ID(+)
AND PSV.NON_LABOR_ORG_ID=OTL1.ORGANIZATION_ID(+)
AND DECODE(OTL1.ORGANIZATION_ID
, NULL
, '1'
, OTL1.LANGUAGE)= DECODE(OTL1.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND PSV.EXPND_ORG_ID=OTL2.ORGANIZATION_ID(+)
AND DECODE(OTL2.ORGANIZATION_ID
, NULL
, '1'
, OTL2.LANGUAGE)= DECODE(OTL2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND EXISTS (SELECT NULL
FROM PA_CC_DIST_LINES_ALL
WHERE EXPENDITURE_ITEM_ID = PSV.EXPENDITURE_ITEM_ID
AND LINE_NUM = PSV.CDL_LINE_NUM
AND (LINE_TYPE = 'BL' OR ( LINE_TYPE = 'PC'
AND REFERENCE_1 IN (SELECT DRAFT_INVOICE_DETAIL_ID
FROM PA_DRAFT_INVOICE_DETAILS_ALL
WHERE EXPENDITURE_ITEM_ID = PSV.EXPENDITURE_ITEM_ID
AND DRAFT_INVOICE_NUM IS NOT NULL))))