APPS.QP_CROSS_ORDER_VOLUME_LOAD SQL Statements

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

Line: 18

      SELECT primary_uom_code
      FROM   mtl_system_items
      WHERE  inventory_item_id = citem_id
      AND    organization_id = corg_id;

Line: 269

     SELECT gsob.currency_code
     FROM  gl_sets_of_books gsob
	WHERE gsob.set_of_books_id = psob_id;

Line: 276

  	  SELECT distinct to_number(qpa.product_attr_value) c_inventory_item_id
	  FROM   qp_list_headers qlh,
              qp_list_lines qpl,
              qp_pricing_attributes qpa
       WHERE qlh.list_header_id = qpl.list_header_id
       AND qpl.list_line_id = qpa.list_line_id
	  AND qlh.active_flag = 'Y'
       AND qpa.product_attribute_context  = 'ITEM'
       AND qpa.product_attribute = qp_util.get_attribute_name('QP',
				 'QP_ATTR_DEFNS_PRICING','ITEM','INVENTORY_ITEM_ID')
       AND qpa.pricing_attribute_context  = 'VOLUME'
       AND (       qpa.pricing_attribute = l_period1_item_qty_attr
               OR  qpa.pricing_attribute = l_period2_item_qty_attr
	          OR  qpa.pricing_attribute = l_period3_item_qty_attr
	          OR  qpa.pricing_attribute = l_period1_item_amt_attr
	          OR  qpa.pricing_attribute = l_period2_item_amt_attr
	          OR  qpa.pricing_attribute = l_period3_item_amt_attr);

Line: 350

        DELETE FROM OE_ITEM_CUST_VOLS_ALL;

Line: 354

        DELETE FROM OE_ITEM_CUST_VOLS_ALL
	   WHERE ORG_ID = g_org_id;

Line: 367

          INSERT INTO OE_ITEM_CUST_VOLS_ALL
	       (org_id,
            inventory_item_id,
            sold_to_org_id,
            primary_uom_code,
            period1_ordered_quantity,
            period2_ordered_quantity,
            period3_ordered_quantity,
            period1_total_amount,
            period2_total_amount,
            period3_total_amount,
            creation_date,
            created_by,
            last_update_date,
            last_updated_by,
            last_update_login,
            program_application_id,
            program_id,
            program_update_date,
            request_id)
	    (SELECT lines.org_id,
                lines.inventory_item_id,
                lines.sold_to_org_id,
                get_uom_code(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id)),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd1,
		       get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
		       lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			  get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
			  lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			  get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
			  lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sysdate,
                p_created_by,
                sysdate,
                p_user_id,
                p_login_id,
                P_program_appl_id,
                P_conc_program_id,
                sysdate,
                p_request_id
          FROM  oe_order_headers_all hdrs,
			 oe_order_lines_all lines,
                hr_operating_units hou,
			 gl_sets_of_books gsob
          WHERE hdrs.header_id = lines.header_id
		AND lines.org_id = hou.organization_id
		AND hou.set_of_books_id = gsob.set_of_books_id
		AND lines.inventory_item_id = i.c_inventory_item_id
          AND lines.line_category_code <> 'RETURN'
          AND lines.org_id = nvl(g_org_id,lines.org_id)
          AND lines.sold_to_org_id is not null
		AND lines.booked_flag = 'Y'
	     AND nvl(lines.cancelled_flag,'N') = 'N'
          AND lines.charge_periodicity_code is null  -- added for recurring charges Bug 4465168
          GROUP BY lines.inventory_item_id,lines.org_id,0,lines.sold_to_org_id);

Line: 448

          INSERT INTO OE_ITEM_CUST_VOLS_ALL
	       (org_id,
            inventory_item_id,
            sold_to_org_id,
            primary_uom_code,
            period1_ordered_quantity,
            period2_ordered_quantity,
            period3_ordered_quantity,
            period1_total_amount,
            period2_total_amount,
            period3_total_amount,
            creation_date,
            created_by,
            last_update_date,
            last_updated_by,
            last_update_login,
            program_application_id,
            program_id,
            program_update_date,
            request_id)
	    (SELECT lines.org_id,
                lines.inventory_item_id,
                lines.sold_to_org_id,
                get_uom_code(lines.inventory_item_id,g_org_id),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd1,
		       get_converted_qty(lines.inventory_item_id,g_org_id,
		       lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			  get_converted_qty(lines.inventory_item_id,g_org_id,
			  lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			  get_converted_qty(lines.inventory_item_id,g_org_id,
			  lines.ordered_quantity,lines.order_quantity_uom),0)),
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sysdate,
                p_created_by,
                sysdate,
                p_user_id,
                p_login_id,
                P_program_appl_id,
                P_conc_program_id,
                sysdate,
                p_request_id
          FROM  oe_order_headers_all hdrs,
			 oe_order_lines_all lines
          WHERE hdrs.header_id = lines.header_id
		AND   lines.inventory_item_id = i.c_inventory_item_id
		AND   lines.line_category_code <> 'RETURN'
          AND   lines.sold_to_org_id is not null
		AND   lines.booked_flag = 'Y'
	     AND   nvl(lines.cancelled_flag,'N') = 'N'
             AND lines.charge_periodicity_code is null   -- added for recurring charges Bug 4465168
          GROUP BY lines.inventory_item_id,lines.org_id,0,lines.sold_to_org_id);

Line: 565

       SELECT 'Y'
       FROM  qp_list_headers qlh,
             qp_qualifiers qq
       WHERE qq.list_header_id = qlh.list_header_id
	  AND  qlh.active_flag = 'Y'
	  AND  qq.qualifier_context     = 'VOLUME'
       AND (     qualifier_attribute = l_period1_order_amt_attr
              OR qualifier_attribute = l_period2_order_amt_attr
              OR qualifier_attribute = l_period3_order_amt_attr);

Line: 581

        DELETE FROM OE_CUST_TOTAL_AMTS_ALL;

Line: 586

        DELETE FROM OE_CUST_TOTAL_AMTS_ALL
	   WHERE ORG_ID = g_org_id;

Line: 611

        INSERT INTO OE_CUST_TOTAL_AMTS_ALL
	   (org_id,
         sold_to_org_id,
         period1_total_amount,
         period2_total_amount,
         period3_total_amount,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by,
         last_update_login,
         program_application_id,
         program_id,
         program_update_date,
         request_id)
        (SELECT lines.org_id,
                lines.sold_to_org_id,
			sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sysdate,
                p_user_id,
                sysdate,
                P_user_id,
                P_login_id,
                P_program_appl_id,
                P_conc_program_id,
                sysdate,
                P_request_id
         FROM  oe_order_headers_all hdrs,
	          oe_order_lines_all lines,
               hr_operating_units hou,
			gl_sets_of_books gsob
         WHERE hdrs.header_id = lines.header_id
	    AND   lines.org_id = hou.organization_id
	    AND   hou.set_of_books_id = gsob.set_of_books_id
         AND   lines.line_category_code <> 'RETURN'
         AND   lines.org_id = NVL(g_org_id,lines.org_id)
         AND   lines.sold_to_org_id is not null
         AND   lines.booked_flag = 'Y'
	    AND   nvl(lines.cancelled_flag,'N') = 'N'
         AND   lines.charge_periodicity_code is null   -- added for recurring charges Bug 4465168
         GROUP BY lines.org_id,lines.sold_to_org_id);

Line: 675

        INSERT INTO OE_CUST_TOTAL_AMTS_ALL
	   (org_id,
         sold_to_org_id,
         period1_total_amount,
         period2_total_amount,
         period3_total_amount,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by,
         last_update_login,
         program_application_id,
         program_id,
         program_update_date,
         request_id)
        (SELECT lines.org_id,
                lines.sold_to_org_id,
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sum(decode(hdrs.transactional_curr_code,l_sob_currency,
			 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
			 lines.ordered_quantity*lines.unit_list_price,0),
			 convert_to_base_curr(get_value(hdrs.ordered_date,
			  l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
			  hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
			  hdrs.conversion_rate,hdrs.conversion_type_code))),
                sysdate,
                p_user_id,
                sysdate,
                P_user_id,
                P_login_id,
                P_program_appl_id,
                P_conc_program_id,
                sysdate,
                P_request_id
         FROM  oe_order_headers_all hdrs,
			oe_order_lines_all lines
         WHERE hdrs.header_id = lines.header_id
	    AND   lines.line_category_code <> 'RETURN'
         AND   lines.sold_to_org_id is not null
         AND   lines.booked_flag = 'Y'
	    AND   nvl(lines.cancelled_flag,'N') = 'N'
         AND   lines.charge_periodicity_code is null     -- added for recurring charges Bug 4465168
         GROUP BY lines.org_id,lines.sold_to_org_id);