Oracle Apps Knowledge Sharing

Query to Fetch Catalog Details (Blanket Purchase Agreement) with Price Breaks in R12 (PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_GA_ORG_ASSIGNMENTS, PO_ATTRIBUTE_VALUES, PO_ATTRIBUTE_VALUES_TLP)

                            In this post, we have a given a query which helps you to fetch the catalog details with price breaks from Oracle Applications R12. This is the same information which is shown while requester searches for catalog data in iProcurement. 

Key Tables: 

PO_HEADERS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_GA_ORG_ASSIGNMENTS

PO_ATTRIBUTE_VALUES

PO_ATTRIBUTE_VALUES_TLP

Query: 

SELECT pl.vendor_product_num,
       ph.po_header_id po_header_id,
       ph.revision_num,
       ph.vendor_id vendor_id,
       ph.creation_date,
       ph.supplier_auth_enabled_flag supplier_auth_enabled,
       ph.cat_admin_auth_enabled_flag cat_admin_auth_enabled,
       pv.vendor_name vendor_name,
       pv.hold_flag hold_flag,
       pvsa.vendor_site_id vendor_site_id,
       pvsa.vendor_site_code vendor_site_code,
       pvsa.duns_number vendor_duns_number,
       ph.email_address,
       pur_type.displayed_field type_lookup_code, 
       auth_status.displayed_field document_status_code, 
       ph.segment1 document_num,
       ph.vendor_order_num,
       ph.agent_id agent_id,
       ppf.full_name agent_name,
       ph.currency_code,
       ph.approval_required_flag,
       PH.global_agreement_flag,
       hou.organization_id org_id,
       hou.name ou_name,
       ph.start_date header_effective_from,
       ph.end_date header_effective_to, 
       pl.po_line_id,
       pl.line_num,
       pl.line_type_id,
       pl.ip_category_id line_ip_category_id,
       (SELECT order_type.displayed_field
         FROM po_lookup_codes order_type
        WHERE order_type.lookup_type = 'ORDER TYPE'
           AND order_type.lookup_code = pl.order_type_lookup_code
       ) order_type_lookup_code,       
      (SELECT purch_basis.displayed_field
         FROM po_lookup_codes purch_basis
        WHERE purch_basis.lookup_type = 'PURCHASE BASIS'
          AND purch_basis.lookup_code = pl.purchase_basis
       ) purchase_basis,
       mct.description category_name,
       pl.category_id,
       pl.item_description,
       uom1.unit_of_measure_tl line_uom, 
       uom1.uom_code line_uom_code, 
       pl.quantity,
       pl.unit_price,
       pl.vendor_product_num,
       pl.quantity_committed,
       pl.min_order_quantity,
       PL.max_order_quantity,
       pl.amount,
       pl.job_id,
       pl.expiration_date,
       (CASE
         WHEN NVL((pl.expiration_date), (SYSDATE)) >= (SYSDATE)
          AND SYSDATE BETWEEN NVL(ph.start_date,SYSDATE-1) 
		  AND NVL(ph.end_date,SYSDATE+1) 
        THEN
          'ACTIVE'   
         ELSE
          'INACTIVE'  
        END
        ) line_status_code,
       pll.line_location_id,
       pll.quantity - NVL(pll.quantity_cancelled, 0) shipment_quantity,
       pll.quantity price_break_quantity,
       pll.price_override,
       uom2.unit_of_measure_tl shipment_uom,
       pll.ship_to_location_id price_break_loc_id, 
       (SELECT hrl.location_code
          FROM hr_locations hrl
         WHERE hrl.location_id = pll.ship_to_location_id
        ) price_break_loc_code,  
       pll.start_date shipment_effective_from,
       pll.end_date shipment_effective_to,
        (CASE
          WHEN SYSDATE BETWEEN NVL(pll.start_date, SYSDATE-1) 
		      AND NVL(pll.end_date,SYSDATE + 1)
              AND NVL((pl.expiration_date), (SYSDATE)) >= (SYSDATE) 
              AND SYSDATE BETWEEN NVL(ph.start_date,SYSDATE-1) 
			  AND NVL(ph.end_date,SYSDATE+1) 
         THEN
           'ACTIVE'
         ELSE
           'INACTIVE'
         END) price_break_status,
       (SELECT match_basis.displayed_field
          FROM po_lookup_codes match_basis
         WHERE match_basis.lookup_type = 'MATCHING BASIS'
           AND match_basis.lookup_code = pl.matching_basis
       ) matching_basis,
       pav.ip_category_id attribute_ip_category_id,
       icct.category_name shopping_category,
       -- Base Descriptors
       pav.num_base_attribute1 number_base_attributes,
       pav.text_base_attribute1 text_base_attributes,
       pav.manufacturer_part_num,
       pav.picture,
       pav.thumbnail_image,
       pav.supplier_url,
       pav.attachment_url,
       pav.unspsc,
       pav.lead_time,
       pav.availability,
       pavt.manufacturer,
       pavt.alias,
       pavt.long_description,
       pavt.comments,
       pavt.description attribute_description,
	   -- Global Agreements key table
       pgoa.organization_id ga_requesting_org_id,
       pgoa.purchasing_org_id ga_purchasing_org_id
  FROM po_headers_all            ph,
       mtl_units_of_measure_tl   uom1, 
       mtl_units_of_measure_tl   uom2, 
       po_lines_all              pl,
       po_line_locations_all     pll,
       po_attribute_values       pav,
       po_attribute_values_tlp   pavt,
       per_all_people_f          ppf,
       ap_suppliers              pv,
       ap_supplier_sites_all     pvsa,
       po_line_types             plt,
       po_ga_org_assignments     pgoa,
       mtl_categories            mc,
       mtl_categories_tl         mct,
       icx_cat_categories_tl     icct,
       hr_all_organization_units hou,
       po_lookup_codes           auth_status,
       po_lookup_codes           pur_type
 WHERE ph.type_lookup_code = 'BLANKET'
   AND ph.global_agreement_flag = 'Y'
   AND ph.po_header_id = pl.po_header_id
   AND hou.organization_id = pgoa.organization_id
   AND pav.po_line_id = pl.po_line_id
   AND pavt.po_line_id = pl.po_line_id
   AND pl.po_line_id = pll.po_line_id
   AND pav.ip_category_id = icct.rt_category_id
   AND uom1.unit_of_measure = NVL(pll.unit_meas_lookup_code, pl.unit_meas_lookup_code)
   AND uom1.language = USERENV('LANG')
   AND uom2.unit_of_measure = PLL.UNIT_MEAS_LOOKUP_CODE
   AND uom2.language = USERENV('LANG')
   AND icct.language = USERENV('LANG')
   AND mct.language = USERENV('LANG')
   AND mct.category_id = mc.category_id
   AND pll.shipment_type = 'PRICE BREAK'
   AND ph.vendor_id = pv.vendor_id
   AND pavt.language = ph.created_language 
   AND pgoa.vendor_site_id = pvsa.vendor_site_id
   AND pl.category_id = mc.category_id
   AND ph.po_header_id = pgoa.po_header_id
   AND pgoa.enabled_flag = 'Y'
   AND ph.agent_id = ppf.person_id
   AND trunc(SYSDATE) BETWEEN
       nvl(ppf.effective_start_date, trunc(SYSDATE) - 1) AND
       nvl(ppf.effective_end_date, trunc(SYSDATE) + 1)
   AND pl.line_type_id = plt.line_type_id
   AND auth_status.lookup_type = 'AUTHORIZATION STATUS'
   and auth_status.lookup_code = ph.authorization_status
   AND pur_type.lookup_type = 'PO TYPE'
   and pur_type.lookup_code = ph.type_lookup_code