SELECT prl.requisition_header_id,
prl.line_num,
prl.requisition_line_id prl_requisition_line_id,
prl.candidate_first_name,
prl.candidate_last_name,
prl.assignment_start_date,
prl.order_type_lookup_code,
prl.purchase_basis,
prl.matching_basis,
prl.negotiated_by_preparer_flag,
prl.ship_method,
prl.estimated_pickup_date,
prl.supplier_notified_for_cancel,
prl.base_unit_price,
prl.at_sourcing_flag,
prl.tax_attribute_update_code,
prl.tax_name,
prl.transferred_to_oe_flag,
prl.conformed_line_id,
prl.amendment_type,
prl.amendment_status,
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,
trim(prl.cost_constraint) prl_cost_constraint,
prl.amendment_response_reason,
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.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.contractor_status,
prl.contact_information,
prl.suggested_supplier_flag,
prl.candidate_screening_reqd_flag,
prl.assignment_end_date,
prl.overtime_allowed_flag,
prl.contractor_requisition_flag,
prl.drop_ship_flag,
prl.bid_number,
prl.unspsc_code,
prl.bid_line_number,
prl.global_attribute18,
prl.global_attribute19,
prl.global_attribute20,
prl.global_attribute_category,
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.other_category_code,
prl.supplier_duns,
prl.tax_status_indicator,
prl.pcard_flag,
prl.new_supplier_flag,
prl.auto_receive_flag,
prl.tax_user_override_flag,
prl.tax_code_id,
prl.note_to_vendor,
prl.oke_contract_version_id,
prl.oke_contract_header_id,
prl.item_source_id,
prl.supplier_ref_number,
prl.secondary_unit_of_measure,
prl.secondary_quantity,
prl.preferred_grade,
prl.secondary_quantity_received,
prl.secondary_quantity_cancelled,
prl.vmi_flag,
prl.auction_header_id,
prl.auction_display_number,
prl.auction_line_number,
prl.reqs_in_pool_flag,
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.last_update_date,
prl.last_updated_by,
prl.source_type_code,
prl.last_update_login,
prl.creation_date,
prl.created_by,
prl.item_id 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.justification,
prl.note_to_agent,
prl.note_to_receiver,
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,
prl.un_number_id,
prl.hazard_class_id,
prl.must_use_sugg_vendor_flag,
prl.reference_num,
prl.on_rfq_flag,
prl.urgent_flag,
nvl(prl.cancel_flag,'N') 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.agent_return_note,
prl.changed_after_research_flag,
prl.vendor_id,
prl.vendor_site_id,
prl.vendor_contact_id,
prl.research_agent_id,
prl.on_line_flag,
prl.wip_entity_id,
prl.wip_line_id,
prl.wip_repetitive_schedule_id,
prl.wip_operation_seq_num,
prl.wip_resource_seq_num,
prl.attribute_category,
prl.destination_context,
prl.inventory_source_context,
prl.vendor_source_context,
prl.attribute1,
prl.attribute2,
prl.attribute3,
prl.attribute4,
prl.attribute5,
prl.attribute6,
prl.attribute7,
prl.attribute8,
prl.attribute9,
prl.attribute10,
prl.attribute11,
prl.attribute12,
prl.attribute13,
prl.attribute14,
prl.attribute15,
prl.bom_resource_id,
prl.request_id,
prl.program_application_id,
prl.program_id,
prl.program_update_date,
prl.ussgl_transaction_code,
prl.government_context,
prl.closed_reason,
prl.closed_date,
prl.transaction_reason_code,
prl.quantity_received,
prl.source_req_line_id,
prl.org_id,
prl.global_attribute1,
prl.global_attribute2,
prl.global_attribute3,
prl.global_attribute4,
prl.global_attribute5,
prl.global_attribute6,
prl.global_attribute7,
prl.global_attribute8,
prl.global_attribute9,
prl.global_attribute10,
prl.global_attribute11,
prl.global_attribute12,
prl.global_attribute13,
prl.global_attribute14,
prl.global_attribute15,
prl.global_attribute16,
prl.global_attribute17,
(select meaning from fnd_lookup_values where
lookup_type = 'PO_FEDERAL_CONTRACT_TYPES_AMT'
and lookup_code = prl.contract_type
and language = userenv('lang') ) contract_type_desc,
plt.line_type,
decode(prl.clm_option_exercised , 'Y', null, decode(prl.clm_option_indicator, 'O', 'Y', null)) option_indicator_value,
(PO_POXPRCLM_PKG.GET_UDA_LINES_XML(prl.requisition_line_id)).getClobVal() uda_line_attr,
to_clob(PO_UDA_PUB.GET_single_ATTR_VALUE(prl.uda_template_id, NULL,prl.requisition_line_id, NULL, NULL, NULL, NULL,NULL,'SHIP_INFO', NULL, 'hiddShipAddXml', 'INTERNAL_VALUE')) shipping_details,
nvl2(prl.item_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), NULL) C_FLEX_ITEM_DISP
FROM po_requisition_lines prl,
po_line_types plt,
mtl_system_items msi
WHERE prl.requisition_header_id = :prh_requisition_header_id
and plt.line_type_id = prl.line_type_id
and prl.item_id = msi.inventory_item_id(+)
and msi.organization_id(+) = :inv_org_id
and nvl(prl.modified_by_agent_flag,'N') = 'N'
ORDER BY prl.line_num_display |
