PO Purchase Requisitions with PO Details

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Purchase Requisitions Status with PO Details Report
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 plla.creation_date >= nvl(:plla_creation_date_from,plla.creation_date)
   and plla.creation_date < nvl(:plla_creation_date_to,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 
,   prda.distribution_num                distribution_number,
    prda.req_line_quantity               distribution_quantity,
    prda.req_line_amount               distribution_amount,
    fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'SQLGL', 'GL#', gl.chart_of_accounts_id, NULL, prda.code_combination_id, 'ALL', 'Y', 'VALUE')
                                           distribution_account,
    prda.allocation_type,
    prda.allocation_value,
    prda.distribution_id
 LOV
Ledger 
gl.name=:ledger and 
haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
 LOV
Operating Unit 
haouv.name=:operating_unit
 LOV
Requisition Number 
prha.segment1=:req_number
 LOV
Creation Date From 
prha.creation_date >= :creation_date_from
 Date
Creation Date To 
prha.creation_date < :creation_date_to + 1
 Date
Need By Date From 
prla.need_by_date >= :p_need_by_date_from
 Date
Need By Date To 
prla.need_by_date < :p_need_by_date_to+1
 Date
Converted To PO Date From 
 
 Date
Converted To PO Date To 
 
 Date
Deliver To Location 
hrl.location_code=:location_code
 LOV
Requestor Name 
ppx2.full_name=:requestor_name
 LOV
Preparer Name 
ppx.full_name=:preparer_name
 Char
Created By 
fu.user_name=:created_by
 LOV
Exclude Fully Delivered 
nvl(prla.quantity_delivered,0) < nvl(prla.quantity,0)
 LOV Oracle
Show Cancelled 
1=1
 LOV Oracle
Show Item DFF Attributes 
select
y.text text
from
(
select distinct
x.title,
x.global_flag,
x.form_left_prompt,
case x.global_flag
when 1
then x.attr_text ||' "'||substrb(x.form_left_prompt,1,xxen_report.max_column_length)||'",'
else 'case when ' || x.context_column_name || ' in (' ||
     listagg('''' || x.descriptive_flex_context_code || '''',',') within group (order by x.global_flag,x.form_left_prompt) over (partition by x.application_id,x.application_table_name,x.title,x.global_flag,x.form_left_prompt) ||
     ') then ' || x.attr_text || chr(10) || 'else null end "' || substrb(x.form_left_prompt,1,xxen_report.max_column_length)||'",'
end text
from
(
select
fdfv.application_id,
fdfv.application_table_name,
fdfv.title,
'req.item_dff_category' context_column_name,
decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2) global_flag,
fdfcuv.form_left_prompt,
fdfcuv.descriptive_flex_context_code,
fdfcuv.application_column_name,
fdfcuv.column_seq_num,
case when ffvs.validation_type in ('D','I','X','Y','F')
then
'case when instr(xxen_util.display_flexfield_value(p_application_id => ' || fdfv.application_id || ',p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''',p_context_code => ' || decode(fdfc.descriptive_flex_context_code,'Global Data Elements','''Global Data Elements''','req.item_dff_category') || ',p_column_name => ''' || fdfcuv.application_column_name || ''',p_rowid => req.item_rowid),'': '',1,1) > 0
then substr(xxen_util.display_flexfield_value(p_application_id => ' || fdfv.application_id || ',p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''',p_context_code => ' || decode(fdfc.descriptive_flex_context_code,'Global Data Elements','''Global Data Elements''','req.item_dff_category') || ',p_column_name => ''' || fdfcuv.application_column_name || ''',p_rowid => req.item_rowid)
           ,1
           ,instr(xxen_util.display_flexfield_value(p_application_id => ' || fdfv.application_id || ',p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''',p_context_code => ' || decode(fdfc.descriptive_flex_context_code,'Global Data Elements','''Global Data Elements''','req.item_dff_category') || ',p_column_name => ''' || fdfcuv.application_column_name || ''',p_rowid => req.item_rowid),'': '',1,1)-1)
else xxen_util.display_flexfield_value(p_application_id => ' || fdfv.application_id || ',p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''',p_context_code => ' || decode(fdfc.descriptive_flex_context_code,'Global Data Elements','''Global Data Elements''','req.item_dff_category') || ',p_column_name => ''' || fdfcuv.application_column_name || ''',p_rowid => req.item_rowid)
end'
else
'xxen_util.display_flexfield_value
(p_application_id => ' || fdfv.application_id || '
,p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || '''
,p_context_code => ' || decode(fdfc.descriptive_flex_context_code,'Global Data Elements','''Global Data Elements''','req.item_dff_category') || '
,p_column_name => ''' || fdfcuv.application_column_name || '''
,p_rowid => req.item_rowid
)'
end attr_text
from
fnd_descriptive_flexs_vl    fdfv,
fnd_descr_flex_contexts     fdfc,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets