PO Print CLM Requisitions

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Print CLM Requisitions Report
Short Name: POXPRCLM_XML
DB package: PO_POXPRCLM_PKG
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
Parameter Name SQL text Validation
Requisition Number To
 
LOV Oracle
Requisition Number From
 
LOV Oracle