VIEW: APPS.POS_ACK_SELECT_V

Source

View Text - Preformatted

SELECT pa.accepted_flag, null, null, pa.acceptance_lookup_code, null, null, pha.po_header_id, -1, pha.segment1, -1, pha.revision_num, pha.type_lookup_code, null, pha.currency_code, null, decode(pha.acceptance_required_flag,'N', 'No', 'Y', 'Yes' , null), nvl(pa.accepted_flag, 'None'), pha.acceptance_due_date, nvl(approved_flag, 'N'), pha.ship_to_location_id, null, pha.ship_via_lookup_code, of1.description, pha.agent_id, PO_INQ_SV.GET_PERSON_NAME(PHA.AGENT_ID), null, pha.vendor_id, pv.vendor_name, pha.vendor_site_id, pvs.vendor_site_code from po_headers pha, po_acceptances pa, hr_locations hl, org_freight of1, hr_employees he, po_vendors pv, po_vendor_sites pvs WHERE pha.po_header_id = pa.po_header_id (+) and pha.revision_num = pa.revision_num (+) and pha.org_id = of1.organization_id (+) and pha.ship_to_location_id = hl.location_id (+) and pha.ship_via_lookup_code = of1.freight_code (+) and pha.agent_id = he.employee_id (+) and pha.vendor_id = pv.vendor_id (+) and pha.vendor_site_id = pvs.vendor_site_id (+) and pha.approved_flag = 'Y' and nvl(pha.closed_code,'OPEN') = 'OPEN' and nvl(pha.cancel_flag,'N') = 'N' union all select pa.accepted_flag, null, null, pa.acceptance_lookup_code, null, null, pr.po_header_id, pr.po_release_id, ph.segment1 || '-' || to_char(pr.release_num), pr.release_num, pr.revision_num, pr.release_type, null, ph.currency_code, null, decode(pr.acceptance_required_flag,'N', 'No', 'Y', 'Yes' , null), nvl(pa.accepted_flag, 'None'), pr.acceptance_due_date, nvl(pr.approved_flag, 'N'), ph.ship_to_location_id, null, ph.ship_via_lookup_code, of1.description, pr.agent_id, PO_INQ_SV.GET_PERSON_NAME(PR.AGENT_ID), null, ph.vendor_id, pv.vendor_name, ph.vendor_site_id, pvs.vendor_site_code from po_releases pr, po_headers ph, po_acceptances pa, hr_locations hl, org_freight of1, hr_employees he, po_vendors pv, po_vendor_sites pvs where ph.po_header_id = pr.po_header_id and pr.po_header_id = pa.po_header_id (+) and pr.po_release_id = pa.po_release_id (+)and ph.org_id = of1.organization_id (+) and ph.ship_to_location_id = hl.location_id (+) and ph.ship_via_lookup_code = of1.freight_code (+) and pr.agent_id = he.employee_id (+) and ph.vendor_id = pv.vendor_id (+) and ph.vendor_site_id = pvs.vendor_site_id (+) and pr.approved_flag = 'Y' and nvl(pr.closed_code,'OPEN') = 'OPEN' and nvl(pr.cancel_flag, 'N' ) = 'N'

View Text - HTML Formatted

SELECT PA.ACCEPTED_FLAG
, NULL
, NULL
, PA.ACCEPTANCE_LOOKUP_CODE
, NULL
, NULL
, PHA.PO_HEADER_ID
, -1
, PHA.SEGMENT1
, -1
, PHA.REVISION_NUM
, PHA.TYPE_LOOKUP_CODE
, NULL
, PHA.CURRENCY_CODE
, NULL
, DECODE(PHA.ACCEPTANCE_REQUIRED_FLAG
, 'N'
, 'NO'
, 'Y'
, 'YES'
, NULL)
, NVL(PA.ACCEPTED_FLAG
, 'NONE')
, PHA.ACCEPTANCE_DUE_DATE
, NVL(APPROVED_FLAG
, 'N')
, PHA.SHIP_TO_LOCATION_ID
, NULL
, PHA.SHIP_VIA_LOOKUP_CODE
, OF1.DESCRIPTION
, PHA.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(PHA.AGENT_ID)
, NULL
, PHA.VENDOR_ID
, PV.VENDOR_NAME
, PHA.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
FROM PO_HEADERS PHA
, PO_ACCEPTANCES PA
, HR_LOCATIONS HL
, ORG_FREIGHT OF1
, HR_EMPLOYEES HE
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE PHA.PO_HEADER_ID = PA.PO_HEADER_ID (+)
AND PHA.REVISION_NUM = PA.REVISION_NUM (+)
AND PHA.ORG_ID = OF1.ORGANIZATION_ID (+)
AND PHA.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND PHA.SHIP_VIA_LOOKUP_CODE = OF1.FREIGHT_CODE (+)
AND PHA.AGENT_ID = HE.EMPLOYEE_ID (+)
AND PHA.VENDOR_ID = PV.VENDOR_ID (+)
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PHA.APPROVED_FLAG = 'Y'
AND NVL(PHA.CLOSED_CODE
, 'OPEN') = 'OPEN'
AND NVL(PHA.CANCEL_FLAG
, 'N') = 'N' UNION ALL SELECT PA.ACCEPTED_FLAG
, NULL
, NULL
, PA.ACCEPTANCE_LOOKUP_CODE
, NULL
, NULL
, PR.PO_HEADER_ID
, PR.PO_RELEASE_ID
, PH.SEGMENT1 || '-' || TO_CHAR(PR.RELEASE_NUM)
, PR.RELEASE_NUM
, PR.REVISION_NUM
, PR.RELEASE_TYPE
, NULL
, PH.CURRENCY_CODE
, NULL
, DECODE(PR.ACCEPTANCE_REQUIRED_FLAG
, 'N'
, 'NO'
, 'Y'
, 'YES'
, NULL)
, NVL(PA.ACCEPTED_FLAG
, 'NONE')
, PR.ACCEPTANCE_DUE_DATE
, NVL(PR.APPROVED_FLAG
, 'N')
, PH.SHIP_TO_LOCATION_ID
, NULL
, PH.SHIP_VIA_LOOKUP_CODE
, OF1.DESCRIPTION
, PR.AGENT_ID
, PO_INQ_SV.GET_PERSON_NAME(PR.AGENT_ID)
, NULL
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
FROM PO_RELEASES PR
, PO_HEADERS PH
, PO_ACCEPTANCES PA
, HR_LOCATIONS HL
, ORG_FREIGHT OF1
, HR_EMPLOYEES HE
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PR.PO_HEADER_ID = PA.PO_HEADER_ID (+)
AND PR.PO_RELEASE_ID = PA.PO_RELEASE_ID (+)AND PH.ORG_ID = OF1.ORGANIZATION_ID (+)
AND PH.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND PH.SHIP_VIA_LOOKUP_CODE = OF1.FREIGHT_CODE (+)
AND PR.AGENT_ID = HE.EMPLOYEE_ID (+)
AND PH.VENDOR_ID = PV.VENDOR_ID (+)
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND PR.APPROVED_FLAG = 'Y'
AND NVL(PR.CLOSED_CODE
, 'OPEN') = 'OPEN'
AND NVL(PR.CANCEL_FLAG
, 'N' ) = 'N'