[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /* + Ordered index(dmap QP_DISCOUNT_MAPPING_T1) */
dmap.new_list_header_id,
dmap.new_list_line_id,
dmap.new_type,
dmap.old_price_break_lines_low,
dmap.old_price_break_lines_high,
dmap.old_method_type_code,
lh.source_system_code,
ll.modifier_level_code,
ll.pricing_group_sequence,
ll.list_line_type_code,
ll.override_flag,
ll.pricing_phase_id,
ll.charge_type_code,
ll.charge_subtype_code,
ll.list_line_no,
ll.benefit_qty,
ll.benefit_uom_code,
ll.print_on_invoice_flag,
ll.expiration_date
from
qp_discount_mapping dmap, qp_list_lines ll, qp_list_headers_b lh
where p_discount_id = dmap.old_discount_id
and (p_discount_line_id = dmap.old_discount_line_id or
p_discount_line_id is null or p_discount_line_id = -1)
and dmap.new_list_header_id = ll.list_header_id
and dmap.new_list_line_id = ll.list_line_id
and dmap.new_list_header_id = lh.list_header_id
and (p_pricing_context = dmap.pricing_context
or dmap.pricing_context is null);
select
dmap.old_discount_id,
dmap.new_list_header_id,
dmap.new_list_line_id,
dmap.old_price_break_lines_low,
dmap.old_price_break_lines_high,
dmap.old_method_type_code,
dmap.new_type,
ll.list_line_type_code,
ll.override_flag,
oeordl.ordered_quantity
from
qp_discount_mapping dmap, oe_order_lines_all oeordl, qp_list_lines ll
where
( dmap.old_discount_id = p_discount_id and
oeordl.line_id = p_line_id and
dmap.new_list_line_id = ll.list_line_id and
dmap.new_type = 'B' and
decode( dmap.old_method_type_code,
'UNITS', oeordl.ordered_quantity,
'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
0)
between nvl( dmap.old_price_break_lines_low,
decode( dmap.old_method_type_code,
'UNITS', oeordl.ordered_quantity,
'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
0)) and
nvl( dmap.old_price_break_lines_high,
decode( dmap.old_method_type_code,
'UNITS', oeordl.ordered_quantity,
'DOLLARS', oeordl.ordered_quantity * oeordl.unit_list_price,
0))
);
v_updated_flag varchar2(1);
v_updated_flag := 'N';
x_output.update_allowed := qppadj.override_flag;
x_output.updated_flag := v_updated_flag;
SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
INTO v_order_price_attrib_id
FROM dual;
insert into oe_order_price_attribs
( header_id,
line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
flex_title,
order_price_attrib_id,
override_flag ,
lock_control)
values
( OE_Upg_SO_NEW.g_line_rec.header_id,
OE_Upg_SO_NEW.g_line_rec.line_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
OE_Upg_SO_NEW.g_line_rec.program_application_id,
OE_Upg_SO_NEW.g_line_rec.program_id,
OE_Upg_SO_NEW.g_line_rec.program_update_date,
OE_Upg_SO_NEW.g_line_rec.request_id,
NVL(OE_Upg_SO_NEW.g_line_rec.pricing_context,'Upgrade Context'),
OE_Upg_SO_NEW.g_line_rec.pricing_attribute1,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute2,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute3,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute4,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute5,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute6,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute7,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute8,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute9,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute10,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute11,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute12,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute13,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute14,
OE_Upg_SO_NEW.g_line_rec.pricing_attribute15,
'QP_ATTR_DEFNS_PRICING',
v_order_price_attrib_id,
'N',
1);
SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
INTO v_order_price_attrib_id
FROM dual;
insert into oe_order_price_attribs
( header_id,
line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
flex_title,
order_price_attrib_id,
override_flag ,
lock_control)
values
( p_line_rec.header_id,
p_line_rec.line_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_line_rec.program_application_id,
p_line_rec.program_id,
p_line_rec.program_update_date,
p_line_rec.request_id,
NVL(p_line_rec.pricing_context,'Upgrade Context'),
p_line_rec.pricing_attribute1,
p_line_rec.pricing_attribute2,
p_line_rec.pricing_attribute3,
p_line_rec.pricing_attribute4,
p_line_rec.pricing_attribute5,
p_line_rec.pricing_attribute6,
p_line_rec.pricing_attribute7,
p_line_rec.pricing_attribute8,
p_line_rec.pricing_attribute9,
p_line_rec.pricing_attribute10,
oe_upg_so_new.g_line_rec.pricing_attribute11,
oe_upg_so_new.g_line_rec.pricing_attribute12,
oe_upg_so_new.g_line_rec.pricing_attribute13,
oe_upg_so_new.g_line_rec.pricing_attribute14,
oe_upg_so_new.g_line_rec.pricing_attribute15,
'QP_ATTR_DEFNS_PRICING',
v_order_price_attrib_id,
'N',
1);
SELECT OE_ORDER_PRICE_ATTRIBS_S.nextval
INTO v_order_price_attrib_id
FROM dual;
insert into oe_order_price_attribs
( header_id,
line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
flex_title,
order_price_attrib_id,
override_flag ,
lock_control)
values
( p_upg_line_rec.header_id,
p_upg_line_rec.line_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_upg_line_rec.program_application_id,
p_upg_line_rec.program_id,
p_upg_line_rec.program_update_date,
p_upg_line_rec.request_id,
NVL(p_upg_line_rec.pricing_context,'Upgrade Context'),
p_upg_line_rec.pricing_attribute1,
p_upg_line_rec.pricing_attribute2,
p_upg_line_rec.pricing_attribute3,
p_upg_line_rec.pricing_attribute4,
p_upg_line_rec.pricing_attribute5,
p_upg_line_rec.pricing_attribute6,
p_upg_line_rec.pricing_attribute7,
p_upg_line_rec.pricing_attribute8,
p_upg_line_rec.pricing_attribute9,
p_upg_line_rec.pricing_attribute10,
p_upg_line_rec.pricing_attribute11,
p_upg_line_rec.pricing_attribute12,
p_upg_line_rec.pricing_attribute13,
p_upg_line_rec.pricing_attribute14,
p_upg_line_rec.pricing_attribute15,
'QP_ATTR_DEFNS_PRICING',
v_order_price_attrib_id,
'N',
1);