VIEW: APPS.PO_CLMREQ_LINE_AWD_REFS_V

Source

View Text - Preformatted

SELECT prh.requisition_header_id, prl.requisition_line_id, prh.segment1 requisition_number, nvl(prl.line_num_display, prl.line_num) line_number, poref.po_header_id , poref.po_line_id, poref.line_location_id, poref.clm_document_number, poref.authorization_status, poref.agent_id, poref.user_hold_flag, poref.creation_date , poref.vendor_id , poref.vendor_site_id , poref.vendor_contact_id , poref.org_id , poref.acceptance_required_flag , poref.acceptance_due_date , poref.reply_date , poref.reply_method_lookup_code , poref.currency_code , poref.header_cancel_flag , poref.cancel_flag, poref.shipment_type, poref.closed_code, poref.receiving_routing_id, poref.vmi_flag, poref.consigned_flag, poref.drop_ship_flag, poref.transaction_flow_header_id, poref.shipment_closed_date, poref.value_basis, poref.matching_basis, poref.payment_type, poref.approved_date from po_requisition_headers_clm_v prh, po_requisition_lines_all prl, ( select prd.po_header_id, prd.po_line_id, prd.line_location_id, min(ph.clm_document_number) clm_document_number, min(ph.authorization_status) authorization_status, min(ph.agent_id) agent_id, min(ph.user_hold_flag) user_hold_flag, min(ph.creation_date) creation_date, min(ph.vendor_id) vendor_id , min(ph.vendor_site_id) vendor_site_id , min(ph.vendor_contact_id) vendor_contact_id , min(ph.org_id) org_id, min(ph.acceptance_required_flag) acceptance_required_flag , min(ph.acceptance_due_date) acceptance_due_date , min(ph.reply_date) reply_date , min(ph.reply_method_lookup_code) reply_method_lookup_code, min(currency_code) currency_code , min(ph.cancel_flag) header_cancel_flag , min(pll.cancel_flag) cancel_flag, min(pll.shipment_type) shipment_type, min(pll.closed_code) closed_code, min(pll.receiving_routing_id) receiving_routing_id, min(pll.vmi_flag) vmi_flag, min(pll.consigned_flag) consigned_flag, min(pll.drop_ship_flag) drop_ship_flag , min(pll.transaction_flow_header_id) transaction_flow_header_id, min(pll.shipment_closed_date) shipment_closed_date, min(pll.value_basis) value_basis, min(pll.matching_basis) matching_basis, min(pll.payment_type) payment_type, min(ph.approved_date) approved_date, reqd.requisition_line_id from po_distributions_all prd, po_headers_all_sec ph, po_line_locations_all pll, po_req_distributions_all reqd where prd.req_distribution_id is not null and prd.po_header_id = ph.po_header_id and prd.line_location_id = pll.line_location_id and prd.req_distribution_id = reqd.distribution_id group by prd.po_header_id, prd.po_line_id, prd.line_location_id, reqd.requisition_line_id ) poref where prh.requisition_header_id = prl. requisition_header_id and prh.conformed_header_id is null and poref.requisition_line_id = prl.requisition_line_id union all select prh.requisition_header_id, prl.requisition_line_id, prh.segment1 requisition_number, nvl(prl.line_num_display, prl.line_num) line_number, pol.po_header_id, pol.po_line_id, null line_location_id, ph.clm_document_number, ph.authorization_status, ph.agent_id , ph.user_hold_flag, ph.creation_date, ph.vendor_id , ph.vendor_site_id , ph.vendor_contact_id , ph.org_id, ph.acceptance_required_flag, ph.acceptance_due_date , ph.reply_date, ph.reply_method_lookup_code, ph.currency_code, ph.cancel_flag header_cancel_flag, null cancel_flag, null shipment_type, null closed_code, null receiving_routing_id, null vmi_flag, null consigned_flag, null drop_ship_flag, null transaction_flow_header_id, null shipment_closed_date, null value_basis, null matching_basis, null payment_type, ph.approved_date from po_requisition_headers_clm_v prh, po_requisition_lines_all prl, po_lines_all pol, po_headers_all ph where prh.requisition_header_id = prl. requisition_header_id and prh.conformed_header_id is null and ph.po_header_id = pol.po_header_id and prl.po_line_id = pol.po_line_id and ( (nvl(prl.clm_option_indicator, 'N') = 'O' and nvl(prl.clm_option_exercised, 'N') = 'N') or nvl(fund_source_not_known, 'N') = 'Y')

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRH.SEGMENT1 REQUISITION_NUMBER
, NVL(PRL.LINE_NUM_DISPLAY
, PRL.LINE_NUM) LINE_NUMBER
, POREF.PO_HEADER_ID
, POREF.PO_LINE_ID
, POREF.LINE_LOCATION_ID
, POREF.CLM_DOCUMENT_NUMBER
, POREF.AUTHORIZATION_STATUS
, POREF.AGENT_ID
, POREF.USER_HOLD_FLAG
, POREF.CREATION_DATE
, POREF.VENDOR_ID
, POREF.VENDOR_SITE_ID
, POREF.VENDOR_CONTACT_ID
, POREF.ORG_ID
, POREF.ACCEPTANCE_REQUIRED_FLAG
, POREF.ACCEPTANCE_DUE_DATE
, POREF.REPLY_DATE
, POREF.REPLY_METHOD_LOOKUP_CODE
, POREF.CURRENCY_CODE
, POREF.HEADER_CANCEL_FLAG
, POREF.CANCEL_FLAG
, POREF.SHIPMENT_TYPE
, POREF.CLOSED_CODE
, POREF.RECEIVING_ROUTING_ID
, POREF.VMI_FLAG
, POREF.CONSIGNED_FLAG
, POREF.DROP_SHIP_FLAG
, POREF.TRANSACTION_FLOW_HEADER_ID
, POREF.SHIPMENT_CLOSED_DATE
, POREF.VALUE_BASIS
, POREF.MATCHING_BASIS
, POREF.PAYMENT_TYPE
, POREF.APPROVED_DATE
FROM PO_REQUISITION_HEADERS_CLM_V PRH
, PO_REQUISITION_LINES_ALL PRL
, ( SELECT PRD.PO_HEADER_ID
, PRD.PO_LINE_ID
, PRD.LINE_LOCATION_ID
, MIN(PH.CLM_DOCUMENT_NUMBER) CLM_DOCUMENT_NUMBER
, MIN(PH.AUTHORIZATION_STATUS) AUTHORIZATION_STATUS
, MIN(PH.AGENT_ID) AGENT_ID
, MIN(PH.USER_HOLD_FLAG) USER_HOLD_FLAG
, MIN(PH.CREATION_DATE) CREATION_DATE
, MIN(PH.VENDOR_ID) VENDOR_ID
, MIN(PH.VENDOR_SITE_ID) VENDOR_SITE_ID
, MIN(PH.VENDOR_CONTACT_ID) VENDOR_CONTACT_ID
, MIN(PH.ORG_ID) ORG_ID
, MIN(PH.ACCEPTANCE_REQUIRED_FLAG) ACCEPTANCE_REQUIRED_FLAG
, MIN(PH.ACCEPTANCE_DUE_DATE) ACCEPTANCE_DUE_DATE
, MIN(PH.REPLY_DATE) REPLY_DATE
, MIN(PH.REPLY_METHOD_LOOKUP_CODE) REPLY_METHOD_LOOKUP_CODE
, MIN(CURRENCY_CODE) CURRENCY_CODE
, MIN(PH.CANCEL_FLAG) HEADER_CANCEL_FLAG
, MIN(PLL.CANCEL_FLAG) CANCEL_FLAG
, MIN(PLL.SHIPMENT_TYPE) SHIPMENT_TYPE
, MIN(PLL.CLOSED_CODE) CLOSED_CODE
, MIN(PLL.RECEIVING_ROUTING_ID) RECEIVING_ROUTING_ID
, MIN(PLL.VMI_FLAG) VMI_FLAG
, MIN(PLL.CONSIGNED_FLAG) CONSIGNED_FLAG
, MIN(PLL.DROP_SHIP_FLAG) DROP_SHIP_FLAG
, MIN(PLL.TRANSACTION_FLOW_HEADER_ID) TRANSACTION_FLOW_HEADER_ID
, MIN(PLL.SHIPMENT_CLOSED_DATE) SHIPMENT_CLOSED_DATE
, MIN(PLL.VALUE_BASIS) VALUE_BASIS
, MIN(PLL.MATCHING_BASIS) MATCHING_BASIS
, MIN(PLL.PAYMENT_TYPE) PAYMENT_TYPE
, MIN(PH.APPROVED_DATE) APPROVED_DATE
, REQD.REQUISITION_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PRD
, PO_HEADERS_ALL_SEC PH
, PO_LINE_LOCATIONS_ALL PLL
, PO_REQ_DISTRIBUTIONS_ALL REQD
WHERE PRD.REQ_DISTRIBUTION_ID IS NOT NULL
AND PRD.PO_HEADER_ID = PH.PO_HEADER_ID
AND PRD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PRD.REQ_DISTRIBUTION_ID = REQD.DISTRIBUTION_ID GROUP BY PRD.PO_HEADER_ID
, PRD.PO_LINE_ID
, PRD.LINE_LOCATION_ID
, REQD.REQUISITION_LINE_ID ) POREF
WHERE PRH.REQUISITION_HEADER_ID = PRL. REQUISITION_HEADER_ID
AND PRH.CONFORMED_HEADER_ID IS NULL
AND POREF.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID UNION ALL SELECT PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRH.SEGMENT1 REQUISITION_NUMBER
, NVL(PRL.LINE_NUM_DISPLAY
, PRL.LINE_NUM) LINE_NUMBER
, POL.PO_HEADER_ID
, POL.PO_LINE_ID
, NULL LINE_LOCATION_ID
, PH.CLM_DOCUMENT_NUMBER
, PH.AUTHORIZATION_STATUS
, PH.AGENT_ID
, PH.USER_HOLD_FLAG
, PH.CREATION_DATE
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, PH.VENDOR_CONTACT_ID
, PH.ORG_ID
, PH.ACCEPTANCE_REQUIRED_FLAG
, PH.ACCEPTANCE_DUE_DATE
, PH.REPLY_DATE
, PH.REPLY_METHOD_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.CANCEL_FLAG HEADER_CANCEL_FLAG
, NULL CANCEL_FLAG
, NULL SHIPMENT_TYPE
, NULL CLOSED_CODE
, NULL RECEIVING_ROUTING_ID
, NULL VMI_FLAG
, NULL CONSIGNED_FLAG
, NULL DROP_SHIP_FLAG
, NULL TRANSACTION_FLOW_HEADER_ID
, NULL SHIPMENT_CLOSED_DATE
, NULL VALUE_BASIS
, NULL MATCHING_BASIS
, NULL PAYMENT_TYPE
, PH.APPROVED_DATE
FROM PO_REQUISITION_HEADERS_CLM_V PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_LINES_ALL POL
, PO_HEADERS_ALL PH
WHERE PRH.REQUISITION_HEADER_ID = PRL. REQUISITION_HEADER_ID
AND PRH.CONFORMED_HEADER_ID IS NULL
AND PH.PO_HEADER_ID = POL.PO_HEADER_ID
AND PRL.PO_LINE_ID = POL.PO_LINE_ID
AND ( (NVL(PRL.CLM_OPTION_INDICATOR
, 'N') = 'O'
AND NVL(PRL.CLM_OPTION_EXERCISED
, 'N') = 'N') OR NVL(FUND_SOURCE_NOT_KNOWN
, 'N') = 'Y')