PO Purchase Requisitions with PO Details
Description
Run
PO Purchase Requisitions with PO Details and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
with req as (select haouv.name operating_unit, xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) requisition_type, prha.segment1 requisition_number, trunc(prha.creation_date) creation_date, fu.user_name created_by_user, ppx.full_name preparer_name, prha.description description, nvl(xxen_util.meaning(prha.authorization_status,'AUTHORIZATION STATUS',201) ,prha.authorization_status) status, prha.note_to_authorizer, prha.agent_return_flag agent_return_flag, xxen_util.meaning(prha.closed_code,'DOCUMENT STATE',201) closed_status, prla.line_num line_number, trunc(prla.creation_date) line_creation_date, xxen_util.meaning(prla.source_type_code,'REQUISITION SOURCE TYPE',201) line_source_type, xxen_util.meaning(prla.purchase_basis,'PURCHASE BASIS',201) line_type, xxen_util.meaning(prla.order_type_lookup_code,'ORDER TYPE',201) line_order_type, msiv.concatenated_segments item, prla.item_revision item_revision, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mc.structure_id, NULL, mc.category_id, 'ALL', 'Y', 'VALUE') category, nvl(prla.item_description ,msiv.description) line_description, prla.unit_meas_lookup_code uom, prla.quantity, prla.quantity_cancelled, prla.quantity_received, prla.quantity_delivered, prla.unit_price unit_price, prla.amount amount, prla.need_by_date need_by_date, ppx2.full_name requestor_name, (select mp.organization_code from mtl_parameters mp where mp.organization_id = prla.destination_organization_id) destination_organization, nvl(hrl.location_code,(substrb(rtrim(hzl.address1)||'-'||rtrim(hzl.city),1,60))) deliver_to_location, (select mp.organization_code from mtl_parameters mp where mp.organization_id = prla.source_organization_id) source_organization, prla.source_subinventory, prla.suggested_vendor_name suggested_supplier, prla.suggested_vendor_location suggested_supplier_site, prla.suggested_vendor_contact suggested_supplier_contact, prla.suggested_vendor_product_code supplier_item, prla.note_to_agent note_to_agent, xxen_util.meaning(nvl(prla.on_rfq_flag,'N'),'YES/NO',201) on_rfq, prla2.line_num parent_req_line_number, decode(prla.cancel_flag,'Y','Y',null) cancelled_flag, trunc(nvl(prla.cancel_date ,pah.cancelled_date)) cancelled_date, nvl(prla.cancel_reason ,pah.cancelled_note) cancelled_reason, pah.cancelled_by, prla.requisition_header_id, prla.requisition_line_id, prla.line_location_id, prha.attribute_category req_hdr_dff_cat, prha.rowid req_hdr_rowid, prla.attribute_category req_line_dff_cat, prla.rowid req_line_rowid, msiv.attribute_category item_dff_category, msiv.rowid item_rowid &lp_req_dist_columns from po_requisition_headers_all prha, po_requisition_lines_all prla, po_requisition_lines_all prla2, &lp_req_dist_tables financials_system_params_all fspa, gl_ledgers gl, hr_all_organization_units_vl haouv, per_people_x ppx, per_people_x ppx2, fnd_user fu, hr_locations hrl, hz_locations hzl, mtl_categories mc, mtl_system_items_vl msiv, (select ppx.full_name cancelled_by, pah.action_date cancelled_date, pah.note cancelled_note, object_id requisition_header_id from po_action_history pah, per_people_x ppx where ppx.person_id (+) = pah.employee_ID and pah.object_type_code = 'REQUISITION' and pah.action_code = 'CANCEL' ) pah where prla.requisition_header_id = prha.requisition_header_id &lp_req_dist_joins and fspa.org_id = prha.org_id and gl.ledger_id = fspa.set_of_books_id and haouv.organization_id = prha.org_id and ppx.person_id = prha.preparer_id and ppx2.person_id = prla.to_person_id and fu.user_id = prha.created_by and hrl.location_id (+) = prla.deliver_to_location_id and hzl.location_id (+) = prla.deliver_to_location_id and mc.category_id = prla.category_id and prla2.requisition_line_id (+) = prla.parent_req_line_id and msiv.inventory_item_id (+) = prla.item_id and nvl(msiv.organization_id ,fspa.inventory_organization_id) = fspa.inventory_organization_id and pah.requisition_header_id (+) = prha.requisition_header_id and prha.type_lookup_code = 'PURCHASE' and nvl(prha.contractor_requisition_flag , 'N') <> 'Y' and nvl(prha.authorization_status,'INCOMPLETE') != 'INCOMPLETE' and 1=1 ), po as (select haouv.name po_operating_unit, asu.vendor_name po_vendor_name, asu.segment1 po_vendor_number, assa.vendor_site_code po_vendor_site, pha.segment1 po_number, pra.release_num po_release_num, xxen_util.meaning(pha.authorization_status , 'DOCUMENT STATE', 201) po_status, xxen_util.meaning(pra.authorization_status , 'DOCUMENT STATE', 201) po_release_status, trunc(pha.creation_date) po_creation_date, trunc(pra.release_date) po_release_date, pha.currency_code po_currency, pla.line_num po_line_number, pltv.line_type po_line_type, nvl2(pla.category_id ,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', mc.structure_id, NULL, mc.category_id, 'ALL', 'Y', 'VALUE') ,null) po_line_category, msiv.item po_line_item, nvl(pla.item_description,msiv.description) po_line_description, plla.shipment_num po_shipment_number, plla.need_by_date po_shipment_need_by_date, plla.promised_date po_shipment_promised_date, plla.firm_date po_shipment_firm_date, plla.unit_meas_lookup_code po_uom, plla.quantity - nvl(plla.quantity_cancelled,0) po_quantity_ordered, plla.quantity_accepted po_quantity_accepted, plla.quantity_rejected po_quantity_rejected, plla.quantity_received po_quantity_received, plla.quantity_billed po_quantity_billed, nvl(plla.price_override,pla.unit_price) po_unit_price, plla.amount - nvl(plla.amount_cancelled,0) po_amount_ordered, plla.amount_accepted po_amount_accepted, plla.amount_rejected po_amount_rejected, plla.amount_received po_amount_received, plla.amount_billed po_amount_billed, trunc(plla.creation_date) po_shipment_creation_date, decode(plla.cancel_flag,'Y','Y',null) po_shipment_cancelled_flag, trunc(plla.cancel_date) po_shipment_cancelled_date, plla.cancel_reason po_shipment_cancelled_reason, xxen_util.user_name(plla.cancelled_by) po_shipment_cancelled_by, mp1.organization_code po_ship_to_organization, hl1.location_code po_ship_to_location, plla.po_header_id, plla.po_line_id, plla.line_location_id, pha.attribute_category po_hdr_dff_cat, pha.rowid po_hdr_rowid, pla.attribute_category po_line_dff_cat, pla.rowid po_line_rowid &lp_po_dist_columns from po_line_locations_all plla, po_lines_all pla, po_headers_all pha, po_releases_all pra, po_line_types_v pltv, ap_suppliers asu, ap_supplier_sites_all assa, financials_system_params_all fspa, gl_ledgers gl, hr_all_organization_units_vl haouv, hr_locations hl1, mtl_parameters mp1, mtl_categories mc, (select fspa.org_id, msiv.inventory_item_id, msiv.concatenated_segments item, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, msiv.description from financials_system_params_all fspa, mtl_system_items_vl msiv where msiv.organization_id = fspa.inventory_organization_id ) msiv &lp_po_dist_tables where pla.po_header_id = pha.po_header_id and plla.po_header_id = pla.po_header_id and plla.po_line_id = pla.po_line_id and pra.po_release_id (+) = plla.po_release_id and asu.vendor_id = pha.vendor_id and assa.vendor_site_id = pha.vendor_site_id and fspa.org_id = pha.org_id and gl.ledger_id = fspa.set_of_books_id and haouv.organization_id = pha.org_id and pltv.line_type_id = pla.line_type_id and mc.category_id (+) = pla.category_id and msiv.inventory_item_id (+) = pla.item_id and msiv.org_id (+) = pla.org_id and mp1.organization_id (+) = plla.ship_to_organization_id and hl1.location_id (+) = plla.ship_to_location_id and trunc(plla.creation_date) >= nvl(:plla_creation_date_from,trunc(plla.creation_date)) and trunc(plla.creation_date) < nvl(:plla_creation_date_to,trunc(plla.creation_date)) + 1 &lp_po_dist_joins and 2=2 ) select req.operating_unit, req.requisition_type, req.requisition_number, req.creation_date, req.created_by_user, req.preparer_name, req.description, req.status, req.closed_status, req.cancelled_flag, req.cancelled_date, req.cancelled_reason, req.cancelled_by, req.note_to_authorizer, req.agent_return_flag, req.line_number, req.line_source_type, req.line_type, req.line_order_type, req.line_creation_date, req.item, req.item_revision, req.category, req.line_description, req.need_by_date, req.unit_price, req.requestor_name, req.destination_organization, req.deliver_to_location, req.source_organization, req.source_subinventory, req.suggested_supplier, req.suggested_supplier_site, req.suggested_supplier_contact, req.supplier_item, &lp_req_line_columns_disp &lp_req_dist_columns_disp req.note_to_agent, req.on_rfq, req.parent_req_line_number, &lp_item_dff_cols &lp_req_dff_cols po.po_vendor_name, po.po_vendor_number, po.po_vendor_site, po.po_number, po.po_release_num, po.po_status, po.po_release_status, po.po_creation_date, po.po_release_date, po.po_currency, po.po_line_number, po.po_line_type, po.po_line_category, po.po_line_item, po.po_line_description, po.po_shipment_number, po.po_shipment_need_by_date, po.po_shipment_promised_date, po.po_shipment_firm_date, po.po_ship_to_organization, po.po_ship_to_location, po.po_unit_price, po.po_uom, &lp_po_line_columns_disp &lp_po_dist_columns_disp &lp_po_dff_cols po.po_shipment_creation_date, po.po_shipment_cancelled_flag, po.po_shipment_cancelled_date, po.po_shipment_cancelled_reason, po.po_shipment_cancelled_by from req, po where req.line_location_id = po.line_location_id (+) &lp_req_to_po_dist_joins and 3=3 order by req.operating_unit, req.creation_date, req.requisition_number, req.line_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Level |
|
LOV | |
Ledger |
|
LOV | |
Operating Unit |
|
LOV | |
Requisition Number From |
|
LOV Oracle | |
Requisition Number To |
|
LOV Oracle | |
Creation Date From |
|
Date | |
Creation Date To |
|
Date | |
Need By Date From |
|
Date | |
Need By Date To |
|
Date | |
Converted To PO Date From |
|
Date | |
Converted To PO Date To |
|
Date | |
Deliver To Location |
|
LOV Oracle | |
Requestor Name |
|
LOV | |
Preparer Name |
|
Char | |
Created By |
|
LOV | |
Exclude Fully Delivered |
|
LOV Oracle | |
Show Cancelled |
|
LOV Oracle | |
Show Item DFF Attributes |
|