VIEW: APPS.PO_TEMP_LABOR_HEADERS_V

Source

View Text - Preformatted

SELECT poh.segment1 po_number ,hroutl.name operating_unit_name ,hrou.business_group_id business_group_id ,pdt.type_name po_type ,plc.displayed_field authorization_status_dsp ,poh.po_header_id po_header_id ,poh.vendor_id vendor_id ,poh.vendor_site_id vendor_site_id ,pv.vendor_name vendor_name ,pvs.vendor_site_code vendor_site_name ,poh.agent_id buyer_id ,per.full_name buyer_full_name FROM po_headers_all poh ,hr_all_organization_units hrou ,hr_all_organization_units_tl hroutl ,per_all_people_f per ,po_vendors pv ,po_vendor_sites_all pvs ,po_lookup_codes plc ,po_document_types_all_tl pdt WHERE poh.type_lookup_code = 'STANDARD' AND poh.authorization_status IN ('APPROVED', 'PRE-APPROVED') AND poh.approved_flag = 'Y' AND poh.enabled_flag = 'Y' AND NVL(poh.cancel_flag, 'N') <> 'Y' AND NVL(poh.frozen_flag, 'N') <> 'Y' AND poh.org_id IS NOT NULL AND poh.org_id = hrou.organization_id AND hrou.organization_id = hroutl.organization_id AND hroutl.language = userenv('LANG') AND poh.agent_id = per.person_id AND per.effective_start_date = (select max(per1.effective_start_date) from per_all_people_f per1 where per.person_id = per1.person_id) AND poh.vendor_id = pv.vendor_id AND poh.vendor_site_id = pvs.vendor_site_id AND poh.org_id = pvs.org_id AND plc.lookup_code (+) = poh.authorization_status AND plc.lookup_type (+) = 'AUTHORIZATION STATUS' AND pdt.document_type_code = 'PO' AND pdt.document_subtype = poh.type_lookup_code AND pdt.org_id = poh.org_id AND pdt.language = userenv('LANG') AND EXISTS (SELECT NULL FROM po_lines_all pol ,po_line_types_b polt WHERE pol.po_header_id = poh.po_header_id AND NVL(pol.cancel_flag, 'N') <> 'Y' AND pol.line_type_id = polt.line_type_id AND polt.purchase_basis = 'TEMP LABOR')

View Text - HTML Formatted

SELECT POH.SEGMENT1 PO_NUMBER
, HROUTL.NAME OPERATING_UNIT_NAME
, HROU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PDT.TYPE_NAME PO_TYPE
, PLC.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP
, POH.PO_HEADER_ID PO_HEADER_ID
, POH.VENDOR_ID VENDOR_ID
, POH.VENDOR_SITE_ID VENDOR_SITE_ID
, PV.VENDOR_NAME VENDOR_NAME
, PVS.VENDOR_SITE_CODE VENDOR_SITE_NAME
, POH.AGENT_ID BUYER_ID
, PER.FULL_NAME BUYER_FULL_NAME
FROM PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS HROU
, HR_ALL_ORGANIZATION_UNITS_TL HROUTL
, PER_ALL_PEOPLE_F PER
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_LOOKUP_CODES PLC
, PO_DOCUMENT_TYPES_ALL_TL PDT
WHERE POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND POH.AUTHORIZATION_STATUS IN ('APPROVED'
, 'PRE-APPROVED')
AND POH.APPROVED_FLAG = 'Y'
AND POH.ENABLED_FLAG = 'Y'
AND NVL(POH.CANCEL_FLAG
, 'N') <> 'Y'
AND NVL(POH.FROZEN_FLAG
, 'N') <> 'Y'
AND POH.ORG_ID IS NOT NULL
AND POH.ORG_ID = HROU.ORGANIZATION_ID
AND HROU.ORGANIZATION_ID = HROUTL.ORGANIZATION_ID
AND HROUTL.LANGUAGE = USERENV('LANG')
AND POH.AGENT_ID = PER.PERSON_ID
AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER1.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER1
WHERE PER.PERSON_ID = PER1.PERSON_ID)
AND POH.VENDOR_ID = PV.VENDOR_ID
AND POH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND POH.ORG_ID = PVS.ORG_ID
AND PLC.LOOKUP_CODE (+) = POH.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE (+) = 'AUTHORIZATION STATUS'
AND PDT.DOCUMENT_TYPE_CODE = 'PO'
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.LANGUAGE = USERENV('LANG')
AND EXISTS (SELECT NULL
FROM PO_LINES_ALL POL
, PO_LINE_TYPES_B POLT
WHERE POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND NVL(POL.CANCEL_FLAG
, 'N') <> 'Y'
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID
AND POLT.PURCHASE_BASIS = 'TEMP LABOR')