APPS.QP_VALIDATE_PLL_PRICING_ATTR SQL Statements

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

Line: 81

 IF p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
    THEN

    IF  nvl(p_old_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X') <>
       nvl(p_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X')
         THEN
       l_return_status := FND_API.G_RET_STS_ERROR;

Line: 89

       FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');

Line: 100

       FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');

Line: 111

       FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');

Line: 276

          SELECT pte_code, source_system_code
          INTO l_pte_code, l_ss_code
          FROM qp_list_headers_b
          WHERE list_header_id = p_PRICING_ATTR_rec.list_header_id;

Line: 312

	 SELECT primary_uom_flag
	 INTO   l_primary_uom_flag
	 FROM   qp_list_lines
	 WHERE  list_line_id = p_PRICING_ATTR_rec.list_line_id;

Line: 324

    select start_date_active, end_date_active , revision, list_header_id
    into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
    from qp_list_lines
    where list_line_id = p_PRICING_ATTR_rec.list_line_id;

Line: 334

	select  b.list_line_id col2
	from    qp_pricing_attributes b, qp_list_lines c
	where 	b.list_line_id <> p_PRICING_ATTR_rec.list_line_id
	and     b.list_line_id=c.list_line_id
	and     c.primary_uom_flag='Y'
	and     b.product_attribute_context = p_PRICING_ATTR_rec.product_attribute_context
	and     b.product_attribute = p_PRICING_ATTR_rec.product_attribute
	and     b.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
	and     b.list_header_id = p_PRICING_ATTR_rec.list_header_id
	AND     b.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code  -- for bug 7135111
	)
	Loop
	/*----------------------------------------------*/
	l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');

Line: 351

		SELECT  start_date_active, end_date_active
		into  l_sdate, l_edate
		from qp_list_lines
		where list_line_id = c1.col2;

Line: 403

		select start_date_active, end_date_active , revision, list_header_id
		into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
		from qp_list_lines
		where list_line_id = p_PRICING_ATTR_rec.list_line_id;

Line: 465

			      SELECT 'VALID' INTO l_dummy
				  FROM mtl_system_items_b
			      where inventory_item_id =
						p_PRICING_ATTR_rec.product_attr_value
				     AND NVL( CUSTOMER_ORDER_FLAG, 'Y' ) = 'Y'
					 and organization_id =
						fnd_profile.value('QP_ORGANIZATION_ID');

Line: 473

				SELECT 'VALID' INTO l_dummy
				  FROM mtl_system_items_b
			      where inventory_item_id =
						p_PRICING_ATTR_rec.product_attr_value
				     AND NVL(PURCHASING_ENABLED_FLAG,'N') = 'Y'
					 and organization_id =
						fnd_profile.value('QP_ORGANIZATION_ID');

Line: 507

		select 'VALID' INTO l_dummy
		from mtl_item_uoms_view
		where uom_code = p_PRICING_ATTR_rec.product_uom_code
		and organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
		and inventory_item_id = p_PRICING_ATTR_rec.product_attr_value
                and rownum = 1;

Line: 525

	-- Bug 6891094 :UOM validity check to be done only for new list lines and lines in which the UOM is updated.
	elsif (p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2') THEN
		IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
		( p_PRICING_ATTR_rec.product_uom_code <> p_old_PRICING_ATTR_rec.product_uom_code
		OR
		p_old_PRICING_ATTR_rec.product_uom_code IS NULL ) THEN
			IF NOT QP_VALIDATE.Product_Uom(p_pricing_attr_rec.product_uom_code,
							to_number(p_PRICING_ATTR_rec.product_attr_value),
							p_PRICING_ATTR_rec.list_header_id) THEN
				IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
					FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');

Line: 546

		SELECT 'VALID' INTO l_dummy
		FROM MTL_UNITS_OF_MEASURE_VL MTLUOM2
		WHERE
		  EXISTS
		    (
		    SELECT /*+no_unnest*--/ 1
		    FROM MTL_SYSTEM_ITEMS_B MTLITM1,
			 MTL_UOM_CONVERSIONS MTLUCV
		    WHERE MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
                        AND MTLUOM2.UOM_CODE = p_PRICING_ATTR_rec.product_uom_code
			AND MTLITM1.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
			AND MTLITM1.inventory_item_id in
			(
		 	 SELECT inventory_item_id FROM mtl_item_categories
		 	 WHERE category_id = to_number(p_PRICING_ATTR_rec.product_attr_value)
		 	 AND organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
			)
			AND NVL( MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1 ) > TRUNC(SYSDATE)
			AND
			(
			    (
				MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1, 3)
				AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
				OR
				(
				    MTLUCV.INVENTORY_ITEM_ID = 0
				    AND MTLUOM2.BASE_UOM_FLAG = 'Y'
				    AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
				    AND MTLUCV.UOM_CLASS IN
				    (
				    SELECT MTLPRI1.UOM_CLASS
				    FROM MTL_UNITS_OF_MEASURE MTLPRI1
				    WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
				    )
				)
				OR
				(
				    MTLUCV.INVENTORY_ITEM_ID = 0
				    AND MTLUCV.UOM_CODE IN
				    (
				    SELECT MTLUCC1.TO_UOM_CODE
				    FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
				    WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
				    AND NVL(MTLUCC1.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
				    )
				)
			    )
			    OR
			    (
				MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2, 3)
				AND MTLUCV.INVENTORY_ITEM_ID = 0
				AND
				(
				    MTLUCV.UOM_CLASS IN
				    (
				    SELECT MTLUCC.TO_UOM_CLASS
				    FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
				    WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
				    AND NVL(MTLUCC.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
				    )
				    OR MTLUCV.UOM_CLASS =
				    (
				    SELECT MTLPRI.UOM_CLASS
				    FROM MTL_UNITS_OF_MEASURE MTLPRI
				    WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
				    )
				)
			    )
			)
		    )
                    and rownum = 1;

Line: 629

		select 'VALID' INTO l_dummy
		from MTL_UNITS_OF_MEASURE_VL
		where uom_code = p_PRICING_ATTR_rec.product_uom_code
		and rownum = 1;

Line: 656

         SELECT count(*)
	    INTO   l_count
         FROM   qp_list_lines l, qp_pricing_attributes a
         WHERE  l.list_line_id = a.list_line_id
	    AND    a.list_header_id = p_PRICING_ATTR_rec.list_header_id
         AND    a.product_attribute_context =
			    p_PRICING_ATTR_rec.product_attribute_context
         AND    a.product_attribute = p_PRICING_ATTR_rec.product_attribute
         AND    a.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
	    AND    a.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code
         AND    l.primary_uom_flag = 'Y';

Line: 684

    select start_date_active, end_date_active , revision, list_header_id
    into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
    from qp_list_lines
    where list_line_id = p_PRICING_ATTR_rec.list_line_id;

Line: 1010

      SELECT  lookup_code
	 INTO    l_comparison_operator_code
	 FROM    QP_LOOKUPS
      WHERE   LOOKUP_TYPE = 'COMPARISON_OPERATOR'
	 AND     LOOKUP_CODE = UPPER(p_Pricing_Attr_rec.comparison_operator_code);

Line: 1233

	   select from_rltd_modifier_id
	   into   l_from_rltd_modifier_id
	   from   qp_rltd_modifiers
	   where  to_rltd_modifier_id = p_Pricing_Attr_rec.list_line_id;

Line: 1245

	   select continuous_price_break_flag
	   into   l_continuous_price_break_flag
	   from   qp_list_lines
	   where  list_line_id = l_from_rltd_modifier_id;

Line: 1445

    IF  p_PRICING_ATTR_rec.last_updated_by IS NOT NULL AND
        (   p_PRICING_ATTR_rec.last_updated_by <>
            p_old_PRICING_ATTR_rec.last_updated_by OR
            p_old_PRICING_ATTR_rec.last_updated_by IS NULL )
    THEN
        IF NOT QP_Validate.Last_Updated_By(p_PRICING_ATTR_rec.last_updated_by) THEN
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 1455

    IF  p_PRICING_ATTR_rec.last_update_date IS NOT NULL AND
        (   p_PRICING_ATTR_rec.last_update_date <>
            p_old_PRICING_ATTR_rec.last_update_date OR
            p_old_PRICING_ATTR_rec.last_update_date IS NULL )
    THEN
        IF NOT QP_Validate.Last_Update_Date(p_PRICING_ATTR_rec.last_update_date) THEN
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 1465

    IF  p_PRICING_ATTR_rec.last_update_login IS NOT NULL AND
        (   p_PRICING_ATTR_rec.last_update_login <>
            p_old_PRICING_ATTR_rec.last_update_login OR
            p_old_PRICING_ATTR_rec.last_update_login IS NULL )
    THEN
        IF NOT QP_Validate.Last_Update_Login(p_PRICING_ATTR_rec.last_update_login) THEN
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 1609

    IF  p_PRICING_ATTR_rec.program_update_date IS NOT NULL AND
        (   p_PRICING_ATTR_rec.program_update_date <>
            p_old_PRICING_ATTR_rec.program_update_date OR
            p_old_PRICING_ATTR_rec.program_update_date IS NULL )
    THEN
        IF NOT QP_Validate.Program_Update_Date(p_PRICING_ATTR_rec.program_update_date) THEN
            x_return_status := FND_API.G_RET_STS_ERROR;

Line: 1833

PROCEDURE Entity_Delete
(   x_return_status                 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,   p_PRICING_ATTR_rec              IN  QP_Price_List_PUB.Pricing_Attr_Rec_Type
)
IS
l_return_status               VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;

Line: 1867

            ,   'Entity_Delete'
            );

Line: 1871

END Entity_Delete;

Line: 1892

select a.list_line_id col1, b.list_line_id col2
from qp_pricing_attributes a, qp_pricing_attributes b
where 	a.list_line_id = l_List_Line_ID
and b.list_line_id <> l_List_Line_ID
and not exists (select Null
                from    qp_rltd_modifiers qrm
                Where   qrm.to_rltd_modifier_id = b.list_line_id
                and     qrm.rltd_modifier_grp_type = 'PRICE BREAK')
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
and b.product_uom_code = a.product_uom_code   --2943344
--BEGIN Bug No. 9158257
AND (
	(b.pricing_attribute_context = a.pricing_attribute_context
	   AND b.pricing_attribute = a.pricing_attribute
	   AND  nvl(b.pricing_attr_value_from,0) = nvl(a.pricing_attr_value_from,0)
	   AND nvl(b.pricing_attr_value_to,0) = nvl(a.pricing_attr_value_to,0)
	   AND b.comparison_operator_code = a.comparison_operator_code
        )
        OR ( a.pricing_attribute_context IS NULL
	     AND b.pricing_attribute_context IS NULL
        )
)
--END Bug No. 9158257
and b.list_header_id = p_List_Header_Id
and a.list_header_id = p_List_Header_Id
group by a.list_line_id, b.list_line_id
having count(b.list_line_id ) = l_attr_lines_count
and count(b.list_line_id) = ( select count(*) from qp_pricing_attributes where list_line_id = b.list_line_id); --2326820

Line: 1932

     select count(*)
     Into l_attr_lines_count
     from qp_pricing_attributes
     where list_line_id = p_List_Line_ID;

Line: 1945

	    SELECT revision, start_date_active, end_date_active
	    into l_count, l_sdate, l_edate
	    from qp_list_lines
	    where list_line_id = rec.col2;

Line: 2043

select a.list_line_id, b.list_line_id
into l_dummy, l_dummy1
from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
where 	a.list_line_id = p_List_Line_ID
and b.list_line_id <> p_List_Line_ID
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
-- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
-- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
-- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
and b.comparison_operator_code = a.comparison_operator_code                 --Added for 2128739
and a.list_line_id = c.list_line_id
and nvl(c.revision,' ') = nvl(p_Revision,' ')
group by a.list_line_id, b.list_line_id
having count(b.list_line_id) = ( select count(*)
					     from qp_pricing_attributes
						where list_line_id = b.list_line_id)  ;

Line: 2105

select a.list_line_id, b.list_line_id
into l_dummy, l_dummy1
from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
where 	a.list_line_id = p_List_Line_ID
and b.list_line_id <> p_List_Line_ID
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
-- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
-- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
-- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
and b.comparison_operator_code = a.comparison_operator_code                 --Added for 2128739
and a.list_line_id = c.list_line_id
and ( nvl(trunc(start_date_active),sysdate)  BETWEEN  nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate)
and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate) OR
     nvl(trunc(end_date_active),sysdate) BETWEEN nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'),sysdate )
			and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate ) )
group by a.list_line_id, b.list_line_id
having count(b.list_line_id) = ( select count(*)
					     from qp_pricing_attributes
						where list_line_id = b.list_line_id)  ;