VIEW: APPS.PO_SP_VAL_V

Source

View Text - Preformatted

SELECT poh.segment1 po_number , hroutl.name operating_unit_name , hrou.business_group_id business_group_id , pds.display_name document_type , plc.displayed_field authorization_status_dsp , poh.po_header_id po_header_id , poh.comments Description , poh.org_id org_id , pol.po_line_id po_line_id , pol.line_num line_number , poll.line_location_id line_location_id , pod.po_distribution_id po_distribution_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 , NVL(pol.start_date, hr_general.start_of_time) pol_start_date , NVL(pol.expiration_date, hr_general.end_of_time) pol_expiration_date , pol.job_id job_id , pod.project_id project_id , pod.task_id task_id , paaf.person_id , paaf.effective_start_date assignmt_effective_start_date, paaf.effective_end_date assignmt_effective_end_date, Decode ( Nvl(pia.xface_cwk_timecards_flag,'N'), 'Y', Nvl(ppt.cwk_clearing_flag,'N'), 'N' ) validate_project_flag FROM po_headers_all poh , po_lines_all pol , po_line_locations_all poll , po_distributions_all pod , 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_doc_style_lines_vl pds , per_all_assignments_f paaf , pa_implementations_all pia , pa_projects_all ppa , pa_project_types_all ppt 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.user_hold_flag, 'N') = 'N' 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 SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date 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 poh.style_id = pds.style_id AND poh.type_lookup_code = pds.document_subtype AND pol.po_header_id = poh.po_header_id AND NVL(pol.cancel_flag,'N') <> 'Y' AND pol.matching_basis = 'AMOUNT' AND pol.purchase_basis = 'TEMP LABOR' AND pol.order_type_lookup_code = 'RATE' AND poll.po_line_id = pol.po_line_id AND NVL (poll.approved_flag, 'N') = 'Y' AND NVL (poll.cancel_flag, 'N') = 'N' AND NVL (poll.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND NOT ( NVL (fnd_profile.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N') <> 'Y' AND NVL (poll.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING') ) AND pod.line_location_id = poll.line_location_id AND paaf.po_header_id = poh.po_header_id AND paaf.po_line_id = pol.po_line_id AND paaf.assignment_type = 'C' AND paaf.po_header_id IS NOT NULL AND paaf.po_line_id IS NOT NULL AND paaf.job_id = pol.job_id AND poh.org_id = pia.org_id(+) AND pod.project_id = ppa.project_id(+) AND ppa.project_type=ppt.project_type(+) AND ppa.org_id=ppt.org_id(+) UNION SELECT poh.segment1 po_number , hroutl.name operating_unit_name , hrou.business_group_id business_group_id , pds.display_name document_type , plc.displayed_field authorization_status_dsp , poh.po_header_id po_header_id , poh.comments Description , poh.org_id org_id , pol.po_line_id po_line_id , pol.line_num line_number , poll.line_location_id line_location_id , pod.po_distribution_id po_distribution_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 , NVL(pol.start_date, hr_general.start_of_time) pol_start_date , NVL(pol.expiration_date, hr_general.end_of_time) pol_expiration_date , pol.job_id job_id , pod.project_id project_id , pod.task_id task_id , paaf.person_id , paaf.effective_start_date assignmt_effective_start_date, paaf.effective_end_date assignmt_effective_end_date, Decode ( Nvl(pia.xface_cwk_timecards_flag,'N'), 'Y', Nvl(ppt.cwk_clearing_flag,'N'), 'N' ) validate_project_flag FROM po_headers_all poh , po_lines_all pol , po_line_locations_all poll , po_distributions_all pod , 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_doc_style_lines_vl pds , per_all_assignments_f paaf , po_cwk_associations pca , pa_implementations_all pia , pa_projects_all ppa , pa_project_types_all ppt 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.user_hold_flag, 'N') = 'N' 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 SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date 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 poh.style_id = pds.style_id AND poh.type_lookup_code = pds.document_subtype AND pol.po_header_id = poh.po_header_id AND NVL(pol.cancel_flag,'N') <> 'Y' AND pol.matching_basis = 'AMOUNT' AND pol.purchase_basis = 'TEMP LABOR' AND pol.order_type_lookup_code = 'RATE' AND poll.po_line_id = pol.po_line_id AND NVL (poll.approved_flag, 'N') = 'Y' AND NVL (poll.cancel_flag, 'N') = 'N' AND NVL (poll.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND NOT ( NVL (fnd_profile.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N') <> 'Y' AND NVL (poll.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING') ) AND pod.line_location_id = poll.line_location_id AND pca.po_header_id = poh.po_header_id AND pca.po_line_id = pol.po_line_id AND paaf.assignment_type = 'C' AND paaf.job_id = pol.job_id AND pca.cwk_person_id = paaf.person_id AND pca.status = 'A' AND poh.org_id = pia.org_id(+) AND pod.project_id = ppa.project_id(+) AND ppa.project_type=ppt.project_type(+) AND ppa.org_id=ppt.org_id(+)

View Text - HTML Formatted

SELECT POH.SEGMENT1 PO_NUMBER
, HROUTL.NAME OPERATING_UNIT_NAME
, HROU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PDS.DISPLAY_NAME DOCUMENT_TYPE
, PLC.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP
, POH.PO_HEADER_ID PO_HEADER_ID
, POH.COMMENTS DESCRIPTION
, POH.ORG_ID ORG_ID
, POL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM LINE_NUMBER
, POLL.LINE_LOCATION_ID LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_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
, NVL(POL.START_DATE
, HR_GENERAL.START_OF_TIME) POL_START_DATE
, NVL(POL.EXPIRATION_DATE
, HR_GENERAL.END_OF_TIME) POL_EXPIRATION_DATE
, POL.JOB_ID JOB_ID
, POD.PROJECT_ID PROJECT_ID
, POD.TASK_ID TASK_ID
, PAAF.PERSON_ID
, PAAF.EFFECTIVE_START_DATE ASSIGNMT_EFFECTIVE_START_DATE
, PAAF.EFFECTIVE_END_DATE ASSIGNMT_EFFECTIVE_END_DATE
, DECODE ( NVL(PIA.XFACE_CWK_TIMECARDS_FLAG
, 'N')
, 'Y'
, NVL(PPT.CWK_CLEARING_FLAG
, 'N')
, 'N' ) VALIDATE_PROJECT_FLAG
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, 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_DOC_STYLE_LINES_VL PDS
, PER_ALL_ASSIGNMENTS_F PAAF
, PA_IMPLEMENTATIONS_ALL PIA
, PA_PROJECTS_ALL PPA
, PA_PROJECT_TYPES_ALL PPT
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.USER_HOLD_FLAG
, 'N') = 'N'
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 SYSDATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
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 POH.STYLE_ID = PDS.STYLE_ID
AND POH.TYPE_LOOKUP_CODE = PDS.DOCUMENT_SUBTYPE
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND NVL(POL.CANCEL_FLAG
, 'N') <> 'Y'
AND POL.MATCHING_BASIS = 'AMOUNT'
AND POL.PURCHASE_BASIS = 'TEMP LABOR'
AND POL.ORDER_TYPE_LOOKUP_CODE = 'RATE'
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND NVL (POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL (POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL (POLL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NOT ( NVL (FND_PROFILE.VALUE('RCV_CLOSED_PO_DEFAULT_OPTION')
, 'N') <> 'Y'
AND NVL (POLL.CLOSED_CODE
, 'OPEN') IN ('CLOSED'
, 'CLOSED FOR RECEIVING') )
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND PAAF.PO_HEADER_ID = POH.PO_HEADER_ID
AND PAAF.PO_LINE_ID = POL.PO_LINE_ID
AND PAAF.ASSIGNMENT_TYPE = 'C'
AND PAAF.PO_HEADER_ID IS NOT NULL
AND PAAF.PO_LINE_ID IS NOT NULL
AND PAAF.JOB_ID = POL.JOB_ID
AND POH.ORG_ID = PIA.ORG_ID(+)
AND POD.PROJECT_ID = PPA.PROJECT_ID(+)
AND PPA.PROJECT_TYPE=PPT.PROJECT_TYPE(+)
AND PPA.ORG_ID=PPT.ORG_ID(+) UNION SELECT POH.SEGMENT1 PO_NUMBER
, HROUTL.NAME OPERATING_UNIT_NAME
, HROU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PDS.DISPLAY_NAME DOCUMENT_TYPE
, PLC.DISPLAYED_FIELD AUTHORIZATION_STATUS_DSP
, POH.PO_HEADER_ID PO_HEADER_ID
, POH.COMMENTS DESCRIPTION
, POH.ORG_ID ORG_ID
, POL.PO_LINE_ID PO_LINE_ID
, POL.LINE_NUM LINE_NUMBER
, POLL.LINE_LOCATION_ID LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_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
, NVL(POL.START_DATE
, HR_GENERAL.START_OF_TIME) POL_START_DATE
, NVL(POL.EXPIRATION_DATE
, HR_GENERAL.END_OF_TIME) POL_EXPIRATION_DATE
, POL.JOB_ID JOB_ID
, POD.PROJECT_ID PROJECT_ID
, POD.TASK_ID TASK_ID
, PAAF.PERSON_ID
, PAAF.EFFECTIVE_START_DATE ASSIGNMT_EFFECTIVE_START_DATE
, PAAF.EFFECTIVE_END_DATE ASSIGNMT_EFFECTIVE_END_DATE
, DECODE ( NVL(PIA.XFACE_CWK_TIMECARDS_FLAG
, 'N')
, 'Y'
, NVL(PPT.CWK_CLEARING_FLAG
, 'N')
, 'N' ) VALIDATE_PROJECT_FLAG
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, 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_DOC_STYLE_LINES_VL PDS
, PER_ALL_ASSIGNMENTS_F PAAF
, PO_CWK_ASSOCIATIONS PCA
, PA_IMPLEMENTATIONS_ALL PIA
, PA_PROJECTS_ALL PPA
, PA_PROJECT_TYPES_ALL PPT
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.USER_HOLD_FLAG
, 'N') = 'N'
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 SYSDATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
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 POH.STYLE_ID = PDS.STYLE_ID
AND POH.TYPE_LOOKUP_CODE = PDS.DOCUMENT_SUBTYPE
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND NVL(POL.CANCEL_FLAG
, 'N') <> 'Y'
AND POL.MATCHING_BASIS = 'AMOUNT'
AND POL.PURCHASE_BASIS = 'TEMP LABOR'
AND POL.ORDER_TYPE_LOOKUP_CODE = 'RATE'
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND NVL (POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL (POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL (POLL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NOT ( NVL (FND_PROFILE.VALUE('RCV_CLOSED_PO_DEFAULT_OPTION')
, 'N') <> 'Y'
AND NVL (POLL.CLOSED_CODE
, 'OPEN') IN ('CLOSED'
, 'CLOSED FOR RECEIVING') )
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND PCA.PO_HEADER_ID = POH.PO_HEADER_ID
AND PCA.PO_LINE_ID = POL.PO_LINE_ID
AND PAAF.ASSIGNMENT_TYPE = 'C'
AND PAAF.JOB_ID = POL.JOB_ID
AND PCA.CWK_PERSON_ID = PAAF.PERSON_ID
AND PCA.STATUS = 'A'
AND POH.ORG_ID = PIA.ORG_ID(+)
AND POD.PROJECT_ID = PPA.PROJECT_ID(+)
AND PPA.PROJECT_TYPE=PPT.PROJECT_TYPE(+)
AND PPA.ORG_ID=PPT.ORG_ID(+)