[Home] [Help]
SELECT prh.requisition_header_id, prh.segment1 requisition_num, prh.preparer_id, prl.requisition_line_id, prl.line_num, prl.line_type_id, prl.category_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.deliver_to_location_id, prl.to_person_id, prl.source_type_code, prl.item_id, prl.item_revision, prl.quantity_delivered, prl.suggested_buyer_id, prl.encumbered_flag, prl.rfq_required_flag, prl.need_by_date, prl.line_location_id, prl.modified_by_agent_flag, prl.parent_req_line_id, prl.purchasing_agent_id, prl.document_type_code, prl.blanket_po_header_id, prl.blanket_po_line_num, prl.currency_code, prl.rate_type, prl.rate_date, prl.rate, prl.currency_unit_price, prl.suggested_vendor_name, prl.suggested_vendor_location, prl.suggested_vendor_contact, prl.suggested_vendor_phone, prl.suggested_vendor_product_code supplier_item, prl.un_number_id, prl.hazard_class_id, prl.must_use_sugg_vendor_flag, prl.reference_num, prl.on_rfq_flag, prl.urgent_flag, prl.cancel_flag, prl.source_organization_id, prl.source_subinventory, prl.destination_type_code, prl.destination_organization_id, prl.destination_subinventory, prl.quantity_cancelled, prl.cancel_date, prl.cancel_reason, prl.closed_code, prl.vendor_id, prl.vendor_site_id, prl.vendor_contact_id, prl.on_line_flag, prl.destination_context, prl.inventory_source_context, prl.vendor_source_context, prl.bom_resource_id, prl.ussgl_transaction_code, prl.government_context, prl.closed_reason, prl.closed_date, prl.transaction_reason_code, prl.source_req_line_id, prl.org_id, prl.kanban_card_id, prl.catalog_type, prl.catalog_source, prl.manufacturer_id, prl.manufacturer_name, prl.manufacturer_part_number, prl.requester_email, prl.requester_fax, prl.requester_phone, prl.unspsc_code, prl.other_category_code, prl.supplier_duns, prl.pcard_flag, prl.new_supplier_flag, prl.auto_receive_flag, prl.item_source_id, prl.supplier_ref_number, prl.preferred_grade, prl.vmi_flag, prl.auction_header_id, prl.auction_display_number, prl.auction_line_number, prl.reqs_in_pool_flag, prl.bid_number, prl.bid_line_number, prl.noncat_template_id, prl.suggested_vendor_contact_fax, prl.suggested_vendor_contact_email, prl.amount, prl.currency_amount, prl.labor_req_line_id, prl.job_id, prl.job_long_description, prl.contact_information, prl.suggested_supplier_flag, prl.assignment_end_date, prl.contractor_requisition_flag, prl.drop_ship_flag, prl.assignment_start_date, prl.order_type_lookup_code, prl.purchase_basis, prl.matching_basis, prl.negotiated_by_preparer_flag, prl.ship_method, prl.base_unit_price, prl.at_sourcing_flag, prl.conformed_line_id, prl.line_num_display, prl.group_line_id, prl.clm_info_flag, prl.clm_option_indicator, prl.clm_base_line_num, prl.clm_option_num, prl.clm_option_from_date, prl.clm_option_to_date, prl.clm_funded_flag, prl.uda_template_id, prl.contract_type, prl.cost_constraint, prl.clm_period_perf_start_date, prl.clm_period_perf_end_date, prl.clm_extended_item_description, prl.clm_option_exercised, prl.is_line_num_display_modified, prl.assignment_number, prl.po_draft_id, prl.po_line_id, prl.clm_exercised_date, prl.linked_to_fund, prl.fund_source_not_known, prl.creation_date, nvl((select sum(nvl(prd.funded_value, 0)) from po_req_distributions_all prd where prd.requisition_line_id = prl.requisition_line_id),0) funded_value, nvl(poref.funds_used, 0) funds_used, (nvl((select sum(nvl(prd.funded_value, 0)- NVL(PRD.FUNDS_LIQUIDATED,0) ) from po_req_distributions_all prd where prd.requisition_line_id = prl.requisition_line_id),0) - Nvl(poref.funds_used,0)) funds_remaining, prh.revision_num from po_requisition_headers_clm_v prh, po_requisition_lines_all prl, (select sum(funds_used) funds_used, requisition_line_id from po_clmreq_dist_funds_used_v pof, po_req_distributions_all pod where pof.req_distribution_id = pod.distribution_id group by requisition_line_id) poref where prl.conformed_line_id is null and prh.requisition_header_id = prl.requisition_header_id and nvl(prh.active_shopping_cart_flag, 'N') <> 'Y' and prl.source_type_code = 'VENDOR' and nvl(prl.cancel_flag, 'N') not in ('Y', 'I') and NVL(prh.authorization_status,'INCOMPLETE') <> 'INCOMPLETE' and poref.requisition_line_id(+) = prl.requisition_line_id
SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1 REQUISITION_NUM
, PRH.PREPARER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PRL.LINE_TYPE_ID
, PRL.CATEGORY_ID
, PRL.ITEM_DESCRIPTION
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, PRL.QUANTITY
, PRL.DELIVER_TO_LOCATION_ID
, PRL.TO_PERSON_ID
, PRL.SOURCE_TYPE_CODE
, PRL.ITEM_ID
, PRL.ITEM_REVISION
, PRL.QUANTITY_DELIVERED
, PRL.SUGGESTED_BUYER_ID
, PRL.ENCUMBERED_FLAG
, PRL.RFQ_REQUIRED_FLAG
, PRL.NEED_BY_DATE
, PRL.LINE_LOCATION_ID
, PRL.MODIFIED_BY_AGENT_FLAG
, PRL.PARENT_REQ_LINE_ID
, PRL.PURCHASING_AGENT_ID
, PRL.DOCUMENT_TYPE_CODE
, PRL.BLANKET_PO_HEADER_ID
, PRL.BLANKET_PO_LINE_NUM
, PRL.CURRENCY_CODE
, PRL.RATE_TYPE
, PRL.RATE_DATE
, PRL.RATE
, PRL.CURRENCY_UNIT_PRICE
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE SUPPLIER_ITEM
, PRL.UN_NUMBER_ID
, PRL.HAZARD_CLASS_ID
, PRL.MUST_USE_SUGG_VENDOR_FLAG
, PRL.REFERENCE_NUM
, PRL.ON_RFQ_FLAG
, PRL.URGENT_FLAG
, PRL.CANCEL_FLAG
, PRL.SOURCE_ORGANIZATION_ID
, PRL.SOURCE_SUBINVENTORY
, PRL.DESTINATION_TYPE_CODE
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.DESTINATION_SUBINVENTORY
, PRL.QUANTITY_CANCELLED
, PRL.CANCEL_DATE
, PRL.CANCEL_REASON
, PRL.CLOSED_CODE
, PRL.VENDOR_ID
, PRL.VENDOR_SITE_ID
, PRL.VENDOR_CONTACT_ID
, PRL.ON_LINE_FLAG
, PRL.DESTINATION_CONTEXT
, PRL.INVENTORY_SOURCE_CONTEXT
, PRL.VENDOR_SOURCE_CONTEXT
, PRL.BOM_RESOURCE_ID
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PRL.TRANSACTION_REASON_CODE
, PRL.SOURCE_REQ_LINE_ID
, PRL.ORG_ID
, PRL.KANBAN_CARD_ID
, PRL.CATALOG_TYPE
, PRL.CATALOG_SOURCE
, PRL.MANUFACTURER_ID
, PRL.MANUFACTURER_NAME
, PRL.MANUFACTURER_PART_NUMBER
, PRL.REQUESTER_EMAIL
, PRL.REQUESTER_FAX
, PRL.REQUESTER_PHONE
, PRL.UNSPSC_CODE
, PRL.OTHER_CATEGORY_CODE
, PRL.SUPPLIER_DUNS
, PRL.PCARD_FLAG
, PRL.NEW_SUPPLIER_FLAG
, PRL.AUTO_RECEIVE_FLAG
, PRL.ITEM_SOURCE_ID
, PRL.SUPPLIER_REF_NUMBER
, PRL.PREFERRED_GRADE
, PRL.VMI_FLAG
, PRL.AUCTION_HEADER_ID
, PRL.AUCTION_DISPLAY_NUMBER
, PRL.AUCTION_LINE_NUMBER
, PRL.REQS_IN_POOL_FLAG
, PRL.BID_NUMBER
, PRL.BID_LINE_NUMBER
, PRL.NONCAT_TEMPLATE_ID
, PRL.SUGGESTED_VENDOR_CONTACT_FAX
, PRL.SUGGESTED_VENDOR_CONTACT_EMAIL
, PRL.AMOUNT
, PRL.CURRENCY_AMOUNT
, PRL.LABOR_REQ_LINE_ID
, PRL.JOB_ID
, PRL.JOB_LONG_DESCRIPTION
, PRL.CONTACT_INFORMATION
, PRL.SUGGESTED_SUPPLIER_FLAG
, PRL.ASSIGNMENT_END_DATE
, PRL.CONTRACTOR_REQUISITION_FLAG
, PRL.DROP_SHIP_FLAG
, PRL.ASSIGNMENT_START_DATE
, PRL.ORDER_TYPE_LOOKUP_CODE
, PRL.PURCHASE_BASIS
, PRL.MATCHING_BASIS
, PRL.NEGOTIATED_BY_PREPARER_FLAG
, PRL.SHIP_METHOD
, PRL.BASE_UNIT_PRICE
, PRL.AT_SOURCING_FLAG
, PRL.CONFORMED_LINE_ID
, PRL.LINE_NUM_DISPLAY
, PRL.GROUP_LINE_ID
, PRL.CLM_INFO_FLAG
, PRL.CLM_OPTION_INDICATOR
, PRL.CLM_BASE_LINE_NUM
, PRL.CLM_OPTION_NUM
, PRL.CLM_OPTION_FROM_DATE
, PRL.CLM_OPTION_TO_DATE
, PRL.CLM_FUNDED_FLAG
, PRL.UDA_TEMPLATE_ID
, PRL.CONTRACT_TYPE
, PRL.COST_CONSTRAINT
, PRL.CLM_PERIOD_PERF_START_DATE
, PRL.CLM_PERIOD_PERF_END_DATE
, PRL.CLM_EXTENDED_ITEM_DESCRIPTION
, PRL.CLM_OPTION_EXERCISED
, PRL.IS_LINE_NUM_DISPLAY_MODIFIED
, PRL.ASSIGNMENT_NUMBER
, PRL.PO_DRAFT_ID
, PRL.PO_LINE_ID
, PRL.CLM_EXERCISED_DATE
, PRL.LINKED_TO_FUND
, PRL.FUND_SOURCE_NOT_KNOWN
, PRL.CREATION_DATE
, NVL((SELECT SUM(NVL(PRD.FUNDED_VALUE
, 0))
FROM PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID)
, 0) FUNDED_VALUE
, NVL(POREF.FUNDS_USED
, 0) FUNDS_USED
, (NVL((SELECT SUM(NVL(PRD.FUNDED_VALUE
, 0)- NVL(PRD.FUNDS_LIQUIDATED
, 0) )
FROM PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID)
, 0) - NVL(POREF.FUNDS_USED
, 0)) FUNDS_REMAINING
, PRH.REVISION_NUM
FROM PO_REQUISITION_HEADERS_CLM_V PRH
, PO_REQUISITION_LINES_ALL PRL
, (SELECT SUM(FUNDS_USED) FUNDS_USED
, REQUISITION_LINE_ID
FROM PO_CLMREQ_DIST_FUNDS_USED_V POF
, PO_REQ_DISTRIBUTIONS_ALL POD
WHERE POF.REQ_DISTRIBUTION_ID = POD.DISTRIBUTION_ID GROUP BY REQUISITION_LINE_ID) POREF
WHERE PRL.CONFORMED_LINE_ID IS NULL
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND NVL(PRH.ACTIVE_SHOPPING_CART_FLAG
, 'N') <> 'Y'
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND NVL(PRL.CANCEL_FLAG
, 'N') NOT IN ('Y'
, 'I')
AND NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE') <> 'INCOMPLETE'
AND POREF.REQUISITION_LINE_ID(+) = PRL.REQUISITION_LINE_ID
|
|
|
|