APPS.DPP_PRICING_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 72

  SELECT
        qlh.name Pricelist_Name
  FROM
        qp_secu_list_headers_v qlh,
        qp_list_lines_v qll
  WHERE
				qlh.active_flag = 'Y' AND
				g_trunc_sysdate BETWEEN nvl(qlh.start_date_active, g_trunc_sysdate) AND
				nvl(qlh.end_date_active, g_trunc_sysdate) AND
				qlh.source_system_code = 'QP' AND
				qll.product_id = p_inventory_item_id AND
				qll.list_header_id = qlh.list_header_id AND
				qll.product_attribute_context = 'ITEM' AND
				qll.list_line_type_code = 'PLL' AND
				g_trunc_sysdate BETWEEN nvl(qll.start_date_active, g_trunc_sysdate) AND
				nvl(qll.end_date_active, g_trunc_sysdate) AND
				(qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y');

Line: 92

  SELECT
			msi.concatenated_segments
  FROM
			mtl_system_items_kfv msi
  WHERE
			inventory_item_id = p_inventory_item_id
		AND ROWNUM = 1;

Line: 103

  SELECT
	  vendor_name,
	  segment1 vendor_num
	FROM
	  ap_suppliers
	WHERE
  vendor_id = p_vendor_id;

Line: 113

  SELECT
	  vendor_site_code
	FROM
	  ap_supplier_sites_all
	WHERE
	  vendor_site_id = p_vendor_site_id    and
	  vendor_id = p_vendor_id   and
    org_id = p_org_id;

Line: 124

  SELECT
	  name
	FROM
	  hr_operating_units
	WHERE
	  organization_id = p_org_id;

Line: 350

		SELECT	qlh.name Pricelist_Name
		FROM	qp_secu_list_headers_v qlh,
			qp_qualifiers_v qqv,
			qp_list_lines_v qll
		WHERE	qlh.active_flag='Y'
                  and	g_trunc_sysdate between nvl(qlh.start_date_active,g_trunc_sysdate)
                  and	nvl(qlh.end_date_active,g_trunc_sysdate)
                  and	qlh.list_header_id=qqv.list_header_id
                  and	qlh.source_system_code='PO'
                  and	qqv.qualifier_context='PO_SUPPLIER'
                  and	(qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' and qqv.qualifier_attr_value=NVL(p_vendor_id,qqv.qualifier_attr_value))
                  and	qll.product_id = p_inventory_item_id
                  and	qll.list_header_id=qlh.list_header_id
                  and	qll.product_attribute_context='ITEM'
                  and	qll.list_line_type_code='PLL'
                  and	g_trunc_sysdate between nvl(qll.start_date_active,g_trunc_sysdate)
                  and	nvl(qll.end_date_active,g_trunc_sysdate)
                  AND  (qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y')
                UNION
                SELECT	qlh.name Pricelist_Name
		FROM	qp_secu_list_headers_v qlh,
			qp_qualifiers_v qqv,
			qp_list_lines_v qll
		WHERE	qlh.active_flag='Y'
                  and	g_trunc_sysdate between nvl(qlh.start_date_active,g_trunc_sysdate)
                  and	nvl(qlh.end_date_active,g_trunc_sysdate)
                  and	qlh.list_header_id=qqv.list_header_id
                  and	qlh.source_system_code='PO'
                  and	qqv.qualifier_context='PO_SUPPLIER'
                  and    (qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' and qqv.qualifier_attr_value=NVL(p_vendor_site_id,qqv.qualifier_attr_value))             and	qll.product_id = p_inventory_item_id
                  and	qll.list_header_id=qlh.list_header_id
                  and	qll.product_attribute_context='ITEM'
                  and	qll.list_line_type_code='PLL'
                  and	g_trunc_sysdate between nvl(qll.start_date_active,g_trunc_sysdate)
                  and	nvl(qll.end_date_active,g_trunc_sysdate)
                  AND  (qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y');

Line: 389

	  SELECT
				msi.concatenated_segments
	  FROM
				mtl_system_items_kfv msi
	  WHERE
				inventory_item_id = p_inventory_item_id
		AND ROWNUM = 1;

Line: 400

  SELECT
	  vendor_name,
	  segment1 vendor_num
	FROM
	  ap_suppliers
	WHERE
  vendor_id = p_vendor_id;

Line: 410

  SELECT
	  vendor_site_code
	FROM
	  ap_supplier_sites_all
	WHERE
	  vendor_site_id = p_vendor_site_id    and
	  vendor_id = p_vendor_id   and
    org_id = p_org_id;

Line: 421

  SELECT
	  name
	FROM
	  hr_operating_units
	WHERE
	  organization_id = p_org_id;

Line: 645

    SELECT oov.name offer_name
      FROM ozf_offers_v oov,
           qp_modifier_summary_v qmsv
     WHERE qmsv.list_header_id = oov.list_header_id
       AND g_trunc_sysdate between nvl(oov.start_date_active,g_trunc_sysdate)  AND  nvl(oov.end_date_active,g_trunc_sysdate)
       AND g_trunc_sysdate between nvl(qmsv.start_date_active,g_trunc_sysdate) AND  nvl(qmsv.end_date_active,g_trunc_sysdate)
       AND product_attribute_context = 'ITEM'
       AND qmsv.prod_attr_segment_name = 'INVENTORY_ITEM_ID'
       AND qmsv.product_attr_val = to_char(p_inventory_item_id)
       AND oov.active_flag = 'Y'
  GROUP BY oov.name
UNION
 SELECT oov.name offer_name
   FROM ozf_offers_v oov,
        qp_modifier_summary_v qmsv,
        mtl_item_categories mic
  WHERE qmsv.list_header_id = oov.list_header_id
    AND g_trunc_sysdate between nvl(oov.start_date_active,g_trunc_sysdate) AND nvl(oov.end_date_active,g_trunc_sysdate)
    AND product_attribute_context = 'ITEM'
    AND qmsv.prod_attr_segment_name = 'PRODUCT_CATEGORY'
    AND mic.inventory_item_id = p_inventory_item_id
    AND mic.category_id= product_attr_value
    AND oov.active_flag = 'Y'
GROUP BY oov.name
UNION
SELECT NVL(qpl.description, qpl.name) offer_name
  FROM ozf_activity_products oap,
       qp_list_headers_all qpl
 WHERE oap.OBJECT_TYPE IN('SCAN_DATA','LUMPSUM','NET_ACCRUAL','VOLUME_OFFER')
   AND oap.ITEM = p_inventory_item_id
   AND oap.object_id =  qpl.list_header_id
   AND g_trunc_sysdate between nvl(oap.start_date,g_trunc_sysdate)   AND  nvl(oap.end_date,g_trunc_sysdate)
   AND oap.active_flag = 'Y'
   GROUP BY NVL(qpl.description, qpl.name)
 UNION
 SELECT NVL(qpl.description, qpl.name) offer_name
   FROM ams_act_products oap,
        qp_list_headers_all qpl,
        ozf_offers oo
WHERE oap.arc_act_product_used_by ='OFFR'
 AND  oap.inventory_item_id = p_inventory_item_id
 AND  oap.act_product_used_by_id =  qpl.list_header_id
 AND  oap.enabled_flag = 'Y'
 AND  oo.offer_code = qpl.name
 AND  oo.status_code = 'ACTIVE'
 AND  trunc(nvl(oo.start_date,sysdate)) <= trunc(sysdate)
 GROUP BY NVL(qpl.description, qpl.name);

Line: 695

  SELECT msi.concatenated_segments
    FROM mtl_system_items_kfv msi
   WHERE inventory_item_id = p_inventory_item_id
     AND ROWNUM = 1;

Line: 702

  SELECT
	  vendor_name,
	  segment1 vendor_num
	FROM
	  ap_suppliers
	WHERE
  vendor_id = p_vendor_id;

Line: 712

  SELECT
	  vendor_site_code
	FROM
	  ap_supplier_sites_all
	WHERE
	  vendor_site_id = p_vendor_site_id    and
	  vendor_id = p_vendor_id   and
    org_id = p_org_id;

Line: 723

  SELECT
	  name
	FROM
	  hr_operating_units
	WHERE
	  organization_id = p_org_id;