The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = citem_id
AND organization_id = corg_id;
SELECT gsob.currency_code
FROM gl_sets_of_books gsob
WHERE gsob.set_of_books_id = psob_id;
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);
DELETE FROM OE_ITEM_CUST_VOLS_ALL;
DELETE FROM OE_ITEM_CUST_VOLS_ALL
WHERE ORG_ID = g_org_id;
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);
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);
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);
DELETE FROM OE_CUST_TOTAL_AMTS_ALL;
DELETE FROM OE_CUST_TOTAL_AMTS_ALL
WHERE ORG_ID = g_org_id;
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);
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);