[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select interface_action_code
into l_interface_action_code
from qp_interface_list_lines
where orig_sys_line_ref = p_orig_sys_line_ref;
if l_interface_action_code = 'INSERT' then
begin
select count(*)
into l_count1
from qp_interface_pricing_attribs
where orig_sys_line_ref = p_orig_sys_line_ref
and process_status_flag = 'P'
and request_id = p_request_id;
if l_interface_action_code = 'UPDATE' then
begin
select count(*)
into l_count
from qp_interface_pricing_attribs
where orig_sys_line_ref = p_orig_sys_line_ref
and request_id = p_request_id
and process_status_flag = 'P'
and interface_action_code = 'INSERT';
select count(*)
into l_count1
from qp_pricing_attributes
where orig_sys_line_ref = p_orig_sys_line_ref
and orig_sys_header_ref = p_orig_sys_header_ref;
select count(*)
into l_count
from qp_pricing_attributes
where orig_sys_line_ref = p_orig_sys_line_ref
and orig_sys_header_ref = p_orig_sys_header_ref;
SELECT start_date_active, end_date_active
Into l_sdate, l_edate
From qp_interface_list_lines
Where orig_sys_line_ref=p_orig_sys_line_ref2;
SELECT start_date_active, end_date_active
into l_sdate, l_edate
from qp_list_lines
where orig_sys_line_ref=p_orig_sys_line_ref2
and orig_sys_header_ref = p_orig_sys_header_ref;
Update qp_interface_list_lines set process_status_flag=NULL --'E'
Where orig_sys_line_ref = p_orig_sys_line_ref1
And request_id = p_request_id;
--Insert the message into Interface error table.
Select qp_interface_errors_s.nextval
into l_error_id
from dual;
INSERT INTO
QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(l_error_id, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_orig_sys_header_ref,p_orig_sys_line_ref1,null,null,l_msg);
select /*+ ordered use_nl(a b) index(b QP_INTERFACE_PRCNG_ATTRIBS_N1) */
a.orig_sys_line_ref,
b.orig_sys_line_ref,
a.orig_sys_header_ref,
a.product_uom_code, b.product_uom_code,
a.pricing_attribute_context, b.pricing_attribute_context,
a.pricing_attribute, b.pricing_attribute,
a.pricing_attr_value_from, b.pricing_attr_value_from,
a.pricing_attr_value_to, b.pricing_attr_value_to,
a.comparison_operator_code, b.comparison_operator_code
-- Bug 5092813 RAVI
,b.list_line_id
-- Bug 5234939 RAVI START
,a.orig_sys_pricing_attr_ref
,b.orig_sys_pricing_attr_ref
-- Bug 5234939 RAVI END
from qp_interface_pricing_attribs a, qp_interface_pricing_attribs b
where a.request_id = p_request_id
and b.request_id = p_request_id
and a.process_status_flag ='P'
and b.interface_action_code IN ('INSERT', 'UPDATE')
and b.process_status_flag ='P'
and b.orig_sys_line_ref <> a.orig_sys_line_ref
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.orig_Sys_Header_Ref = a.orig_sys_header_ref;
select a.orig_sys_line_ref , b.orig_sys_line_ref,
a.orig_sys_header_ref,
a.product_uom_code, b.product_uom_code,
a.pricing_attribute_context, b.pricing_attribute_context,
a.pricing_attribute, b.pricing_attribute,
a.pricing_attr_value_from, b.pricing_attr_value_from,
a.pricing_attr_value_to, b.pricing_attr_value_to,
a.comparison_operator_code, b.comparison_operator_code
-- Bug 5092813 RAVI
,b.list_line_id
-- Bug 5234939 RAVI START
,a.orig_sys_pricing_attr_ref
,b.orig_sys_pricing_attr_ref
-- Bug 5234939 RAVI END
from qp_interface_pricing_attribs a, qp_pricing_attributes b
-- Bug 5234939 RAVI
where a.request_id = p_request_id
and a.process_status_flag ='P'
and b.orig_sys_line_ref <> a.orig_sys_line_ref
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.orig_sys_header_ref = a.orig_Sys_Header_Ref
-- Bug 5234939 RAVI
AND NOT EXISTS (SELECT /*+ no_unnest index(C QP_INTERFACE_PRCNG_ATTRIBS_N5) */ 'X' --bug 7433219
from qp_interface_pricing_attribs c
where c.request_id = p_request_id
and c.orig_sys_pricing_attr_ref = b.orig_sys_pricing_attr_ref --bug 7433219
and c.interface_action_code IN ('UPDATE','DELETE')
);
select orig_sys_pricing_attr_ref,
orig_sys_line_ref,
pricing_attribute_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator_code
from qp_interface_pricing_attribs ipa
where request_id = p_request_id
and orig_sys_line_ref = l_orig_sys_line_ref
and orig_sys_pricing_attr_ref <> l_orig_sys_pricing_attr_ref
and pricing_attribute_context is not null
and pricing_attribute is not null
UNION
select orig_sys_pricing_attr_ref,
orig_sys_line_ref,
pricing_attribute_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator_code
from qp_pricing_attributes qpa
where qpa.orig_sys_line_ref = l_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
and qpa.orig_sys_header_ref = l_orig_sys_header_ref
and qpa.pricing_attribute_context is not null
and qpa.pricing_attribute is not null
and qpa.orig_sys_pricing_attr_ref <> l_orig_sys_pricing_attr_ref
and qpa.orig_sys_pricing_attr_ref NOT IN
(Select c.orig_sys_pricing_attr_ref
from qp_interface_pricing_attribs c
where c.orig_sys_line_ref = l_orig_sys_line_ref
and c.interface_action_code IN ('UPDATE','DELETE')
);
l_dup_lin_rec.a_orig_sys_line_ref.delete;
l_dup_lin_rec.b_orig_sys_line_ref.delete;
l_dup_lin_rec.orig_sys_header_ref.delete;
l_dup_lin_rec.a_product_uom_code.delete;
l_dup_lin_rec.b_product_uom_code.delete;
l_dup_lin_rec.a_pricing_attribute_context.delete;
l_dup_lin_rec.b_pricing_attribute_context.delete;
l_dup_lin_rec.a_pricing_attribute.delete;
l_dup_lin_rec.b_pricing_attribute.delete;
l_dup_lin_rec.a_pricing_attr_value_from.delete;
l_dup_lin_rec.b_pricing_attr_value_from.delete;
l_dup_lin_rec.a_pricing_attr_value_to.delete;
l_dup_lin_rec.b_pricing_attr_value_to.delete;
l_dup_lin_rec.a_comparison_operator_code.delete;
l_dup_lin_rec.b_comparison_operator_code.delete;
l_dup_lin_rec.b_list_line_id.delete;
l_dup_lin_rec.a_orig_sys_pricing_attr_ref.delete;
l_dup_lin_rec.b_orig_sys_pricing_attr_ref.delete;
l_dup_lin_rec.a_orig_sys_line_ref.delete;
l_dup_lin_rec.b_orig_sys_line_ref.delete;
l_dup_lin_rec.orig_sys_header_ref.delete;
l_dup_lin_rec.a_product_uom_code.delete;
l_dup_lin_rec.b_product_uom_code.delete;
l_dup_lin_rec.a_pricing_attribute_context.delete;
l_dup_lin_rec.b_pricing_attribute_context.delete;
l_dup_lin_rec.a_pricing_attribute.delete;
l_dup_lin_rec.b_pricing_attribute.delete;
l_dup_lin_rec.a_pricing_attr_value_from.delete;
l_dup_lin_rec.b_pricing_attr_value_from.delete;
l_dup_lin_rec.a_pricing_attr_value_to.delete;
l_dup_lin_rec.b_pricing_attr_value_to.delete;
l_dup_lin_rec.a_comparison_operator_code.delete;
l_dup_lin_rec.b_comparison_operator_code.delete;
l_dup_lin_rec.b_list_line_id.delete;
l_dup_lin_rec.a_orig_sys_pricing_attr_ref.delete;
l_dup_lin_rec.b_orig_sys_pricing_attr_ref.delete;
select count(*) into l_int_pr_attrbs_count
from qp_interface_pricing_attribs a
where a.orig_sys_line_ref=l_dup_lin_rec.a_orig_sys_line_ref(I)
and a.request_id = p_request_id;
select count(*) into l_pr_attrbs_count
from qp_pricing_attributes b
where b.orig_sys_line_ref=l_dup_lin_rec.a_orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and b.orig_sys_header_ref=l_dup_lin_rec.orig_sys_header_ref(I)
and b.list_line_id=l_dup_lin_rec.b_list_line_id(I);
l_pa_line_type1.orig_sys_pricing_attr_ref.delete;
l_pa_line_type1.orig_sys_line_ref.delete;
l_pa_line_type1.pricing_attribute_context.delete;
l_pa_line_type1.pricing_attribute.delete;
l_pa_line_type1.pricing_attr_value_from.delete;
l_pa_line_type1.pricing_attr_value_to.delete;
l_pa_line_type1.comparison_operator_code.delete;
l_pa_line_type2.orig_sys_pricing_attr_ref.delete;
l_pa_line_type2.orig_sys_line_ref.delete;
l_pa_line_type2.pricing_attribute_context.delete;
l_pa_line_type2.pricing_attribute.delete;
l_pa_line_type2.pricing_attr_value_from.delete;
l_pa_line_type2.pricing_attr_value_to.delete;
l_pa_line_type2.comparison_operator_code.delete;
Select 'x'
into l_dummy
From qp_interface_list_lines
where orig_sys_line_ref = l_dup_lin_rec.b_orig_sys_line_ref(I)
and price_break_header_ref is not NULL
and rltd_modifier_grp_type='PRICE BREAK';
select 'x'
into l_dummy
from qp_rltd_modifiers qrm, qp_list_lines b
where b.orig_sys_header_ref = l_dup_lin_rec.orig_sys_header_ref(I)
and b.orig_sys_line_ref = l_dup_lin_rec.b_orig_sys_line_ref(I)
and qrm.rltd_modifier_grp_type = 'PRICE BREAK'
and qrm.to_rltd_modifier_id = b.list_line_id;
select count(*)
into l_count1
from qp_interface_pricing_attribs
where orig_sys_line_ref = l_dup_lin_rec.a_orig_sys_line_ref(I)
and process_status_flag = 'P'
and request_id = p_request_id;
select count(*)
into l_count2
from qp_interface_pricing_attribs
where orig_sys_line_ref = l_dup_lin_rec.b_orig_sys_line_ref(I)
and process_status_flag = 'P'
and request_id = p_request_id;
Select a.start_date_active,
a.end_date_active,
a.Orig_sys_header_ref,
a.Orig_sys_line_ref
into l_start_date_active,
l_end_date_active,
l1_orig_sys_header_ref,
l1_orig_sys_line_ref
From qp_interface_list_lines a
Where a.orig_sys_line_ref=l_dup_lin_rec.a_orig_sys_line_ref(I);
SELECT count(*)
INTO l_count
FROM fnd_descr_flex_column_usages
WHERE APPLICATION_ID = 661
AND DESCRIPTIVE_FLEXFIELD_NAME = p_flex_name
AND ENABLED_FLAG = 'Y'
AND ROWNUM = 1;
select fnd_date.date_to_canonical(TRUNC(SYSDATE))
into l_start_date_active
from DUAL;
IF p_header_rec.interface_action_code(I) = 'INSERT' THEN
Select qp_list_headers_b_s.nextval
into p_header_rec.list_header_id(I)
from dual;
IF p_header_rec.interface_action_code(I) = 'INSERT' THEN
IF p_header_rec.orig_sys_header_ref(I) IS NULL
THEN
p_header_rec.process_status_flag(I):=NULL; --'E';
IF p_header_rec.interface_action_code(I) = 'INSERT' THEN
-- Language
IF p_header_rec.language(I) IS NULL
THEN
p_header_rec.language(I):= '1';
select substr(p_header_rec.start_date_active(I),5,1)
into l_first_date_hash from dual;
select substr(p_header_rec.start_date_active(I),8,1)
into l_second_date_hash from dual;
select length(p_header_rec.start_date_active(I))
into l_date_length from dual;
select substr(p_header_rec.end_date_active(I),5,1)
into l_first_date_hash from dual;
select substr(p_header_rec.end_date_active(I),8,1)
into l_second_date_hash from dual;
select length(p_header_rec.end_date_active(I))
into l_date_length from dual;
SELECT -1*PRECISION, -1*EXTENDED_PRECISION
INTO l_precision, l_extended_precision
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = P_header_rec.CURRENCY_CODE(I);
FND_MESSAGE.SET_NAME('QP', 'QP_ROUNDING_FACTOR_NO_UPDATE');
FND_MESSAGE.SET_NAME('QP', 'QP_ROUNDING_FACTOR_NO_UPDATE');
Select 1 into l_exist
from qp_list_headers_tl
where name= p_header_rec.name(I)
and list_header_id <>
(select list_header_id From qp_list_headers_b
-- ENH unod alcoa changes RAVI
/**
The key between interface and qp tables is only orig_sys_hdr_ref
(not list_header_id)
**/
where orig_system_header_ref =p_header_rec.orig_sys_header_ref(I)
and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*')
)
and language = userenv('LANG');
IF p_header_rec.interface_action_code(I) = 'INSERT' THEN
Select count(*) into l_exist
from qp_interface_list_headers
where request_id = p_header_rec.request_id(I)
and name = p_header_rec.name(I)
and orig_sys_header_ref <> p_header_rec.orig_sys_header_ref(I)
and process_status_flag = 'P'; --is null;
select 1, list_header_id into l_exist, l_list_header_id
from qp_list_headers_b
-- ENH unod alcoa changes START RAVI
/**
The key between interface and qp tables is only orig_sys_hdr_ref
(not list_header_id)
**/
where orig_system_header_ref = p_header_rec.orig_sys_header_ref(I)
and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*');
IF p_header_rec.interface_action_code(I) = 'INSERT' AND l_exist =1 THEN
p_header_rec.process_status_flag(I):=NULL;
ELSIF p_header_rec.interface_action_code(I) = 'UPDATE' AND l_exist =0 THEN
p_header_rec.process_status_flag(I):=NULL;
IF p_header_rec.interface_action_code(I) = 'UPDATE'
AND p_header_rec.list_header_id(I) IS NULL
AND l_exist =1 THEN
p_header_rec.list_header_id(I):=l_list_header_id;
IF p_header_rec.interface_action_code(I) = 'INSERT' THEN
select count(*) into l_exist
from qp_interface_list_headers
where request_id =p_header_rec.request_id(I)
and (list_header_id = p_header_rec.list_header_id(I) or
orig_sys_header_ref = p_header_rec.orig_sys_header_ref(I))
and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*');
ELSIF p_header_rec.interface_action_code(I) = 'UPDATE' AND l_header_id_null = 'N' THEN
qp_bulk_loader_pub.write_log('checking for unique orig_sys_header_ref: ' || p_header_rec.orig_sys_header_ref(I));
select count(distinct list_header_id) into l_exist
from qp_interface_list_headers
where request_id =p_header_rec.request_id(I)
and orig_sys_header_ref = p_header_rec.orig_sys_header_ref(I)
and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*');
/* select count(*) into l_exist
from qp_interface_list_headers
where request_id =p_header_rec.request_id(I)
-- Bug 3604226 RAVI
/**
Multiple headers for insert or delete action with same orig_sys_hdr_ref
are not allowed
**/
-- and p_header_rec.interface_action_code(I) <> 'UPDATE'
-- ENH undo alcoa changes RAVI
/**
The key between interface and qp tables is only orig_sys_hdr_ref
(not list_header_id)
**/
-- and orig_sys_header_ref = p_header_rec.orig_sys_header_ref(I)
-- and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*'); */
IF p_header_rec.interface_action_code(I) = 'UPDATE' THEN
qp_bulk_loader_pub.write_log('Secu. Check list_header_id: '||to_char(p_header_rec.list_header_id(I)));
IF QP_security.check_function( p_function_name => QP_Security.G_FUNCTION_UPDATE,
p_instance_type => QP_Security.G_PRICELIST_OBJECT,
p_instance_pk1 => p_header_rec.list_header_id(I)) <> 'T' THEN
p_header_rec.process_status_flag(I):=NULL;
IF p_header_rec.interface_action_code(I) = 'UPDATE'
AND p_header_rec.process_status_flag(I) = 'P' THEN
qp_bulk_loader_pub.write_log( 'Update header no: '||to_char(l_list_header_id));
qp_bulk_loader_pub.write_log( 'Update source_system_code : '||l_old_price_list_rec.source_system_code);
qp_bulk_loader_pub.write_log( 'Update pte_code : '||l_old_price_list_rec.pte_code);
fnd_message.set_name('QP', 'QP_NO_UPDATE_ATTRIBUTE');
If p_line_rec.interface_action_code(I)='INSERT' THEN
select qp_list_lines_s.nextval
into p_line_rec.list_line_id(I)
from dual;
select list_header_id, start_date_active
into p_line_rec.list_header_id(I), l_start_date
from qp_list_headers_b
where orig_system_header_ref = p_line_rec.orig_sys_header_ref(I);
select count(*) into l_exist
from qp_interface_pricing_attribs qipa, mtl_system_items_vl msiv
where qipa.orig_sys_line_ref = p_line_rec.orig_sys_line_ref(I)
and qipa.request_id = p_line_rec.request_id(I)
and qipa.product_attribute_context = 'ITEM'
and qipa.product_attribute = 'PRICING_ATTRIBUTE1'
and msiv.inventory_item_id = to_number(qipa.PRODUCT_ATTR_VALUE)
and msiv.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
and msiv.service_item_flag = 'Y'
and qipa.interface_action_code in ('INSERT','UPDATE');
select count(*) into l_exist
from qp_pricing_attributes qpa, mtl_system_items_vl msiv
where qpa.orig_sys_line_ref = p_line_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and qpa.orig_sys_header_ref = p_line_rec.orig_sys_header_ref(I)
and qpa.product_attribute_context = 'ITEM'
and qpa.product_attribute = 'PRICING_ATTRIBUTE1'
and msiv.inventory_item_id = to_number(qpa.PRODUCT_ATTR_VALUE)
and msiv.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
and msiv.service_item_flag = 'Y'
and qpa.orig_sys_pricing_attr_ref not in (
select qpa.orig_sys_pricing_attr_ref
from qp_interface_pricing_attribs qipa
where qipa.orig_sys_pricing_attr_ref = qpa.orig_sys_pricing_attr_ref
and qipa.interface_action_code = 'DELETE'
and qipa.request_id = p_line_rec.request_id(I));
select count(*) into l_exist
from qp_list_lines
where orig_sys_line_ref = p_line_rec.orig_sys_line_ref(I)
and orig_sys_header_ref = p_line_rec.orig_sys_header_ref(I) ;
If p_line_rec.interface_action_code(I)='INSERT' AND l_exist >= 1 THEN
p_line_rec.process_status_flag(I):=NULL;
ELSIF p_line_rec.interface_action_code(I)='UPDATE' AND l_exist = 0 THEN
p_line_rec.process_status_flag(I):=NULL;
If p_line_rec.interface_action_code(I)='INSERT' THEN
l_exist:= NULL;
select count(*) into l_exist
from qp_interface_list_lines
where request_id = p_line_rec.request_id(I)
and orig_sys_line_ref = p_line_rec.orig_sys_line_ref(I)
and orig_sys_header_ref = p_line_rec.orig_sys_header_ref(I);
SELECT count(*)
INTO l_dummy
FROM qp_interface_list_lines
WHERE price_break_header_ref = p_LINE_rec.orig_sys_line_ref(I)
AND arithmetic_operator = 'BLOCK_PRICE';
select count(*) into l_dummy
from qp_list_lines l1, qp_list_lines l2, qp_rltd_modifiers l3
where l1.orig_sys_line_ref=p_LINE_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and l1.orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I)
and l1.list_line_id=l3.FROM_RLTD_MODIFIER_ID
and l3.TO_RLTD_MODIFIER_ID=l2.list_line_id
and l3.RLTD_MODIFIER_GRP_TYPE='PRICE BREAK'
AND l2.arithmetic_operator <> 'UNIT_PRICE';
SELECT count(*)
INTO l_dummy
FROM qp_interface_list_lines
WHERE price_break_header_ref = p_LINE_rec.orig_sys_line_ref(I)
AND (arithmetic_operator = 'UNIT_PRICE' OR recurring_value IS NOT NULL);
select count(*) into l_dummy
from qp_list_lines l1, qp_list_lines l2, qp_rltd_modifiers l3
where l1.orig_sys_line_ref=p_LINE_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and l1.orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I)
and l1.list_line_id=l3.FROM_RLTD_MODIFIER_ID
and l3.TO_RLTD_MODIFIER_ID=l2.list_line_id
and l3.RLTD_MODIFIER_GRP_TYPE='PRICE BREAK'
AND l2.arithmetic_operator = 'UNIT_PRICE';
select count(*) into l_dummy
from qp_list_lines l1, qp_list_lines l2, qp_rltd_modifiers l3
where l1.orig_sys_line_ref=p_LINE_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and l1.orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I)
and l1.list_line_id=l3.FROM_RLTD_MODIFIER_ID
and l3.TO_RLTD_MODIFIER_ID=l2.list_line_id
and l3.RLTD_MODIFIER_GRP_TYPE='PRICE BREAK'
AND l2.arithmetic_operator <> 'UNIT_PRICE';
SELECT price_break_type_code, arithmetic_operator
INTO l_pb_type_code, l_art_opr
FROM QP_INTERFACE_LIST_LINES
WHERE orig_sys_line_ref = p_line_rec.price_break_header_ref(I)
AND orig_sys_header_ref = p_line_rec.orig_sys_header_ref(I)
AND request_id = p_line_rec.request_id(I);
SELECT L2.price_break_type_code, L2.arithmetic_operator
INTO l_pb_type_code, l_art_opr
from qp_list_lines l1, qp_list_lines l2, qp_rltd_modifiers l3
where l1.orig_sys_line_ref=p_LINE_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and l1.orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I)
and l1.list_line_id=l3.TO_RLTD_MODIFIER_ID
and l3.FROM_RLTD_MODIFIER_ID=l2.list_line_id
and l3.RLTD_MODIFIER_GRP_TYPE='PRICE BREAK';
select fnd_date.canonical_to_date(start_date_active),
fnd_date.canonical_to_date(end_date_active)
into l_header_start_date, l_header_end_date
from qp_interface_list_headers
where orig_sys_header_ref=p_line_rec.orig_sys_header_ref(I)
and request_id=p_line_rec.request_id(I)
and interface_action_code in ('INSERT','UPDATE');
select start_date_active, end_date_active
into l_header_start_date, l_header_end_date
from qp_list_headers_b
where orig_system_header_ref=p_line_rec.orig_sys_header_ref(I);
select start_date_active, end_date_active
into l_PBH_start_date, l_PBH_end_date
from qp_interface_list_lines
where orig_sys_line_ref=p_line_rec.PRICE_BREAK_HEADER_REF(I)
-- Bug 5246745 Use Composite Index for Ref columns
and orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I)
and request_id=p_line_rec.request_id(I)
and interface_action_code in ('INSERT','UPDATE');
select start_date_active, end_date_active
into l_PBH_start_date, l_PBH_end_date
from qp_list_lines
where orig_sys_line_ref=p_line_rec.PRICE_BREAK_HEADER_REF(I)
-- Bug 5246745 Use Composite Index for Ref columns
and orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I);
qp_bulk_loader_pub.write_log('Line Start date updated');
If p_qualifier_rec.interface_action_code(I)='INSERT' THEN
Select qp_qualifiers_s.nextval
into p_qualifier_rec.qualifier_id(I) from dual;
select 'Y' into l_hierarchy_enabled
from dual
where exists(select 'X'
from qp_segments_b qs,qp_prc_contexts_b qpc
where
qs.prc_context_id = qpc.prc_context_id and
qpc.prc_context_code = p_qualifier_rec.qualifier_context(I) and
qs.segment_mapping_column = p_qualifier_rec.qualifier_attribute(I) and
qpc.PRC_CONTEXT_TYPE = 'QUALIFIER'
and qs.party_hierarchy_enabled_flag ='Y');
select 1 into l_exist from qp_list_headers_b
where list_header_id = to_number(p_qualifier_rec.qualifier_attr_value(I));
Select 1 INTO l_exist
from qp_qualifiers
where orig_sys_qualifier_ref=p_qualifier_rec.orig_sys_qualifier_ref(I)
and orig_sys_header_ref = p_qualifier_rec.orig_sys_header_ref(I);
IF p_qualifier_rec.interface_action_code(I) = 'INSERT' AND l_exist = 1 THEN
p_qualifier_rec.process_status_flag(I):=NULL;
ELSIF p_qualifier_rec.interface_action_code(I) = 'UPDATE' AND l_exist = 0 THEN
p_qualifier_rec.process_status_flag(I):=NULL;
If p_qualifier_rec.interface_action_code(I)='INSERT' THEN
l_exist:=null;
Select count(*) INTO l_exist
from qp_interface_qualifiers
where request_id = p_qualifier_rec.request_id(I)
and orig_sys_qualifier_ref=p_qualifier_rec.orig_sys_qualifier_ref(I)
and p_qualifier_rec.process_status_flag(I) ='P' --is null
and orig_sys_header_ref = p_qualifier_rec.orig_sys_header_ref(I);
select list_header_id, active_flag
into p_qualifier_rec.list_header_id(I), p_qualifier_rec.active_flag(i)
from qp_list_headers_b
where orig_system_header_ref = p_qualifier_rec.orig_sys_header_ref(I);
SELECT qplh.list_header_id
INTO l_list_header_id
FROM QP_interface_list_headers qpilh, qp_list_headers qplh
WHERE qplh.list_header_id = to_number(p_qualifier_rec.qualifier_attr_value(I))
AND qpilh.ORIG_SYS_HEADER_REF = p_qualifier_rec.ORIG_SYS_HEADER_REF(I)
AND qplh.currency_code = qpilh.currency_code;
SELECT to_char(list_header_id)
INTO p_qualifier_rec.qualifier_attr_value(I)
FROM QP_LIST_HEADERS_TL
WHERE NAME = p_qualifier_rec.qualifier_attr_value_code(I)
AND LANGUAGE = userenv('LANG');
select 1 into l_exist
from qp_qualifiers
where qualifier_context = 'MODLIST'
and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
and qualifier_attr_value = to_number(p_qualifier_rec.qualifier_attr_value(I))
-- Bug# 5276935 RAVI
-- Chcek for duplication in the remaining qualifiers
and orig_sys_qualifier_ref <> p_qualifier_rec.orig_sys_qualifier_ref(I)
and list_header_id = p_qualifier_rec.list_header_id(I);
select 1 into l_exist
from qp_interface_qualifiers
where request_id = p_qualifier_rec.request_id(I)
and orig_sys_header_ref = p_qualifier_rec.orig_sys_header_ref(I)
and orig_sys_qualifier_ref <> p_qualifier_rec.orig_sys_qualifier_ref(I)
and qualifier_context = 'MODLIST'
and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
and qualifier_attr_value = p_qualifier_rec.qualifier_attr_value(I);
IF QP_security.check_function(p_function_name => QP_Security.G_FUNCTION_UPDATE,
p_instance_type => QP_Security.G_PRICELIST_OBJECT,
p_instance_pk1 => to_number(p_qualifier_rec.qualifier_attr_value(I))) <> 'T' THEN
p_qualifier_rec.process_status_flag(I):=NULL;
SELECT count(*)
INTO l_exist
FROM QP_SECONDARY_PRICE_LISTS_V
WHERE parent_price_list_id = p_qualifier_rec.qualifier_attr_value(I);
SELECT count(*)
INTO l_exist
FROM QP_INTERFACE_QUALIFIERS
WHERE QUALIFIER_CONTEXT = 'MODLIST'
AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4'
AND request_id = p_qualifier_rec.request_id(I)
AND (QUALIFIER_ATTR_VALUE = p_qualifier_rec.qualifier_attr_value(I)
OR QUALIFIER_ATTR_VALUE_CODE = p_qualifier_rec.qualifier_attr_value_code(I));
SELECT f.segment_level
INTO l_seg_level
FROM qp_prc_contexts_b d,
qp_segments_b e, qp_pte_segments f
WHERE d.prc_context_code =p_qualifier_rec.qualifier_context(I)
AND e.segment_mapping_column = p_qualifier_rec.qualifier_attribute(I)
AND d.prc_context_id = e.prc_context_id
AND e.segment_id = f.segment_id
AND f.pte_code = 'ORDFUL';
SELECT 'OVERLAP'
FROM qp_list_lines la,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_pricing_attributes pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
-- Got Price Break Line
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
-- Got Remaining Price Break Lines
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
-- Got Price Break Line New Values
AND rb.to_rltd_modifier_id = pb.list_line_id
-- Got Remaining Price Break Lines Values
-- Bug# 5236432 RAVI
AND pb.orig_sys_pricing_attr_ref NOT IN (
select pia.orig_sys_pricing_attr_ref
from qp_interface_pricing_attribs pia
where pia.orig_sys_header_ref=pb.orig_sys_header_ref
and pia.orig_sys_line_ref=pb.orig_sys_line_ref
)
-- Got the remaining Price Break Lines that are not being updated in this request
AND (
( qp_number.canonical_to_number(pa.pricing_attr_value_from) >=
qp_number.canonical_to_number(pa.pricing_attr_value_to)
) -- New Price Break To is greater than (or equal to) New Price Break From
OR
( qp_number.canonical_to_number(pa.pricing_attr_value_from) <
qp_number.canonical_to_number(pb.pricing_attr_value_to)
AND qp_number.canonical_to_number(pa.pricing_attr_value_from) >=
qp_number.canonical_to_number(pb.pricing_attr_value_from)
) -- New Price Break From in between Old Price Break
OR
( qp_number.canonical_to_number(pa.pricing_attr_value_to) <=
qp_number.canonical_to_number(pb.pricing_attr_value_to)
AND qp_number.canonical_to_number(pa.pricing_attr_value_to) >
qp_number.canonical_to_number(pb.pricing_attr_value_from)
) -- New Price Break To in between Old Price Break
OR
( qp_number.canonical_to_number(pa.pricing_attr_value_from) <
qp_number.canonical_to_number(pb.pricing_attr_value_from)
AND qp_number.canonical_to_number(pa.pricing_attr_value_to) >
qp_number.canonical_to_number(pb.pricing_attr_value_to)
) -- Old Price Break in between New Price Break
);
SELECT count(*) old_break_pa_count,
min(to_number(pb.pricing_attr_value_from)) new_break_low_value,
max(to_number(pb.pricing_attr_value_to)) new_break_high_value
FROM qp_list_lines la,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_pricing_attributes pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
-- Got Price Break Line
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
-- Got Remaining Price Break Lines
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
-- Got Price Break Line New Values
AND rb.to_rltd_modifier_id = pb.list_line_id;
SELECT count(*) new_break_pa_count,
min(to_number(pb.pricing_attr_value_from)) new_break_low_value,
max(to_number(pb.pricing_attr_value_to)) new_break_high_value
FROM qp_list_lines la,
qp_list_lines lb,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_interface_pricing_attribs pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
AND rb.to_rltd_modifier_id = lb.list_line_id
AND pb.orig_sys_line_ref = lb.orig_sys_line_ref
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id;
SELECT count(*) l_from_value_old
FROM qp_list_lines la,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_pricing_attributes pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
-- Got Price Break Line
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
-- Got Remaining Price Break Lines
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
-- Got Price Break Line New Values
AND rb.to_rltd_modifier_id = pb.list_line_id
-- Got Remaining Price Break Lines Values
AND pa.pricing_attr_value_from=pb.pricing_attr_value_to;
SELECT count(*) l_from_value_new
FROM qp_list_lines la,
qp_list_lines lb,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_interface_pricing_attribs pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
AND rb.to_rltd_modifier_id = lb.list_line_id
AND pb.orig_sys_line_ref = lb.orig_sys_line_ref
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
AND pa.pricing_attr_value_from=pb.pricing_attr_value_to;
SELECT count(*) l_to_value_old
FROM qp_list_lines la,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_pricing_attributes pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
-- Got Price Break Line
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
-- Got Remaining Price Break Lines
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
-- Got Price Break Line New Values
AND rb.to_rltd_modifier_id = pb.list_line_id
-- Got Remaining Price Break Lines Values
AND pa.pricing_attr_value_to=pb.pricing_attr_value_from;
SELECT count(*) l_to_value_new
FROM qp_list_lines la,
qp_list_lines lb,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_interface_pricing_attribs pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
AND rb.to_rltd_modifier_id = lb.list_line_id
AND pb.orig_sys_line_ref = lb.orig_sys_line_ref
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
AND pa.pricing_attr_value_to=pb.pricing_attr_value_from;
SELECT /*+ leading(la) index(pa QP_INTERFACE_PRCNG_ATTRIBS_N4) index(pb QP_INTERFACE_PRCNG_ATTRIBS_N4) */ --7433219
'OVERLAP'
FROM qp_list_lines la,
qp_list_lines lb,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_interface_pricing_attribs pa,
qp_interface_pricing_attribs pb
WHERE la.orig_sys_line_ref = p_orig_sys_line_ref
-- Bug 5246745 Use Composite Index for Ref columns
AND la.orig_sys_header_ref = p_orig_sys_header_ref
AND ra.to_rltd_modifier_id = la.list_line_id
AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
AND pa.orig_sys_line_ref = la.orig_sys_line_ref
AND rb.to_rltd_modifier_id = lb.list_line_id
AND pb.orig_sys_line_ref = lb.orig_sys_line_ref
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
AND ra.to_rltd_modifier_id <> rb.to_rltd_modifier_id
AND nvl(pa.pricing_attribute_datatype,'N') = 'N'
-- changes done by rassharm for bug no 6028305
AND pa.request_id=p_request_id
AND pb.request_id=p_request_id
-- end changes
AND (qp_number.canonical_to_number(pa.pricing_attr_value_from) < -- 4713369
qp_number.canonical_to_number(pb.pricing_attr_value_to) AND
qp_number.canonical_to_number(pa.pricing_attr_value_from) >=
qp_number.canonical_to_number(pb.pricing_attr_value_from)
OR
qp_number.canonical_to_number(pa.pricing_attr_value_to) <=
qp_number.canonical_to_number(pb.pricing_attr_value_to) AND
qp_number.canonical_to_number(pa.pricing_attr_value_to) >
qp_number.canonical_to_number(pb.pricing_attr_value_from)
OR
qp_number.canonical_to_number(pa.pricing_attr_value_from) <=
qp_number.canonical_to_number(pb.pricing_attr_value_from) AND
qp_number.canonical_to_number(pa.pricing_attr_value_to) >=
qp_number.canonical_to_number(pb.pricing_attr_value_to));
select list_line_id
into p_pricing_attr_rec.list_line_id(I)
from qp_list_lines
where orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
and orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
select count(*) into l_pa_count
from qp_interface_pricing_attribs qipa, mtl_system_items msi
where qipa.orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
and qipa.request_id = P_PRICING_ATTR_REC.REQUEST_ID(I)
and qipa.product_attribute_context = 'ITEM'
and qipa.product_attribute = 'PRICING_ATTRIBUTE1'
and msi.inventory_item_id = to_number(qipa.PRODUCT_ATTR_VALUE)
and msi.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
and msi.purchasing_enabled_flag = 'Y'
and qipa.interface_action_code in ('INSERT','UPDATE');
select count(*) into l_pa_count
from qp_interface_pricing_attribs qipa, mtl_system_items msi
where qipa.orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
and qipa.request_id = P_PRICING_ATTR_REC.REQUEST_ID(I)
and qipa.product_attribute_context = 'ITEM'
and qipa.product_attribute = 'PRICING_ATTRIBUTE1'
and msi.inventory_item_id = to_number(qipa.PRODUCT_ATTR_VALUE)
and msi.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
and msi.CUSTOMER_ORDER_flag = 'Y'
and qipa.interface_action_code in ('INSERT','UPDATE');
select list_header_id
into p_pricing_attr_rec.list_header_id(I)
from qp_list_headers_b
where orig_system_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
IF p_pricing_attr_rec.interface_action_code(I) = 'INSERT' THEN
Select qp_pricing_attr_group_no_s.nextval
into p_pricing_attr_rec.attribute_grouping_no(I)
from dual;
SELECT 1,pricing_attribute_id INTO l_exist,l_pricing_attribute_id
FROM qp_pricing_attributes
WHERE orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
IF p_pricing_attr_rec.interface_action_code(I) = 'INSERT' AND l_exist = 1 THEN
p_pricing_attr_rec.process_status_flag(I):=NULL;
ELSIF p_pricing_attr_rec.interface_action_code(I) = 'UPDATE' AND l_exist = 0 THEN
p_pricing_attr_rec.process_status_flag(I):=NULL;
IF p_pricing_attr_rec.interface_action_code(I) = 'INSERT' THEN
l_exist:=null;
SELECT count(*) INTO l_exist
FROM qp_interface_pricing_attribs
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
SELECT nvl(pte_code, fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY')),
nvl(source_system_code, fnd_profile.value('QP_SOURCE_SYSTEM_CODE'))
INTO l_pte_code, l_ss_code
FROM qp_interface_list_headers
WHERE orig_sys_header_ref = p_PRICING_ATTR_rec.orig_sys_header_ref(I);
SELECT count(*) INTO l_exist
FROM qp_pricing_attributes
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND pricing_attribute_context IS NULL
AND pricing_attribute IS NULL
AND orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND (pricing_attr_value_from IS NULL OR pricing_Attr_value_to IS NULL);
SELECT count(*) INTO l_exist1
FROM qp_interface_pricing_attribs
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND pricing_attribute_context IS NULL
AND pricing_attribute IS NULL
AND (pricing_attr_value_from IS NULL OR pricing_attr_value_to IS NULL);
SELECT count(*) INTO l_exist
FROM qp_pricing_attributes
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND pricing_attribute_context <> p_pricing_attr_rec.pricing_attribute_context(I)
AND orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I);
SELECT count(*) INTO l_exist1
FROM qp_interface_pricing_attribs
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND pricing_attribute_context <> p_pricing_attr_rec.pricing_attribute_context(I);
select s.availability_in_basic
into l_availability_in_basic
from qp_segments_b s, qp_prc_contexts_b c
where s.prc_context_id = c.prc_context_id
and c.prc_context_code = p_pricing_attr_rec.pricing_attribute_context(I)
and s.segment_mapping_column = p_pricing_attr_rec.pricing_attribute(I)
and s.availability_in_basic = 'Y';
select s.availability_in_basic
into l_availability_in_basic
from qp_segments_b s, qp_prc_contexts_b c
where s.prc_context_id = c.prc_context_id
and c.prc_context_code = p_pricing_attr_rec.product_attribute_context(I)
and s.segment_mapping_column = p_pricing_attr_rec.product_attribute(I)
and s.availability_in_basic = 'Y';
SELECT count(*) INTO l_exist
FROM qp_pricing_attributes
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND product_attribute_context <> p_pricing_attr_rec.product_attribute_context(I)
AND product_attribute <> p_pricing_attr_rec.product_attribute(I)
AND product_attr_value <> p_pricing_attr_rec.product_attr_value(I);
SELECT /*+ index(qipa QP_INTERFACE_PRCNG_ATTRIBS_N2) */ --7433219
count(*) INTO l_exist1
FROM qp_interface_pricing_attribs
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND process_status_flag = 'P' --is null
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND product_attribute_context <> p_pricing_attr_rec.product_attribute_context(I)
AND product_attribute <> p_pricing_attr_rec.product_attribute(I)
AND product_attr_value <> p_pricing_attr_rec.product_attr_value(I);
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(I));
SELECT 1
into l_exist
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d,
qp_segments_b e, qp_pte_segments f
WHERE orig_sys_pricing_attr_ref = p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND b.product_attribute_context = d.prc_context_code
AND b.product_attribute = e.segment_mapping_column
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRICING_ATTRIBUTE'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level <> 'LINE';
SELECT r.rltd_modifier_grp_type
INTO l_pa_price_break
FROM qp_list_lines l, qp_rltd_modifiers r
WHERE l.orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
AND l.orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
AND r.to_rltd_modifier_id = l.list_line_id
AND r.rltd_modifier_grp_type = 'PRICE BREAK';
SELECT primary_uom_flag
INTO l_primary_uom_flag
FROM qp_list_lines
WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id(I);
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(I)
AND a.product_attribute_context=p_PRICING_ATTR_rec.product_attribute_context(I)
AND a.product_attribute = p_PRICING_ATTR_rec.product_attribute(I)
AND a.product_attr_value = p_PRICING_ATTR_rec.product_attr_value(I)
AND a.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code(I)
AND l.primary_uom_flag = 'Y'
AND l.list_line_id <> p_PRICING_ATTR_rec.list_line_id(I);
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I),661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
SELECT count(*)
INTO l_count
FROM qp_list_lines l, qp_interface_pricing_attribs a
WHERE l.orig_sys_line_ref = a.orig_sys_line_ref
AND l.orig_sys_header_ref = a.orig_sys_header_ref
AND a.orig_sys_header_ref = p_PRICING_ATTR_rec.orig_sys_header_ref(I)
AND a.product_attribute_context = p_PRICING_ATTR_rec.product_attribute_context(I)
AND a.product_attribute = p_PRICING_ATTR_rec.product_attribute(I)
AND a.product_attr_value = p_PRICING_ATTR_rec.product_attr_value(I)
AND a.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code(I)
AND a.orig_sys_pricing_attr_ref <> p_pricing_attr_rec.orig_sys_pricing_attr_ref(I)
AND l.primary_uom_flag = 'Y';
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE request_id = p_pricing_attr_rec.request_id(I)
AND orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I),661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
/* SELECT COUNT(1) INTO l_count
FROM
(SELECT /*+ ordered leading(la) index(pb QP_INTERFACE_PRCNG_ATTRIBS_N4) */ /*DISTINCT pb.pricing_attribute
FROM qp_list_lines la,
qp_pricing_attributes pb,
qp_rltd_modifiers ra
WHERE ra.to_rltd_modifier_id = la.list_line_id
AND pb.list_line_id = ra.to_rltd_modifier_id
AND ra.from_rltd_modifier_id = (select from_rltd_modifier_id
from qp_rltd_modifiers, qp_list_lines
where orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
and to_rltd_modifier_id = list_line_id)
AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
UNION
SELECT /*+ leading(la) */ /*DISTINCT pb.pricing_attribute
FROM qp_list_lines la,
qp_interface_pricing_attribs pb,
qp_rltd_modifiers ra
WHERE ra.to_rltd_modifier_id = la.list_line_id
AND pb.orig_sys_line_ref = la.orig_sys_line_ref
AND pb.request_id = p_pricing_attr_rec.request_id(I)
AND pb.process_status_flag = 'P' --IS NULL
AND ra.from_rltd_modifier_id = (select from_rltd_modifier_id
from qp_rltd_modifiers, qp_list_lines
where orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
-- Bug 5246745 Use Composite Index for Ref columns
and orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I)
and to_rltd_modifier_id = list_line_id)
AND ra.rltd_modifier_grp_type = 'PRICE BREAK');*/
SELECT COUNT(1) INTO l_count
FROM
(SELECT /*+ ordered leading(la) index(pb QP_INTERFACE_PRCNG_ATTRIBS_N4) */ DISTINCT pb.pricing_attribute
FROM qp_list_lines la,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb,
qp_list_lines lb,
qp_interface_pricing_attribs pb
WHERE la.orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I) AND
la.list_line_type_code = 'PLL' AND
la.pricing_phase_id = 1 AND
ra.to_rltd_modifier_id = la.list_line_id AND
rb.from_rltd_modifier_id = ra.FROM_RLTD_MODIFIER_ID AND
lb.list_line_id = rb.to_rltd_modifier_id AND
pb.orig_sys_line_ref = lb.orig_sys_line_ref AND
pb.request_id = p_pricing_attr_rec.request_id(I) AND
pb.process_status_flag = 'P' AND --IS NULL AND
rb.rltd_modifier_grp_type = 'PRICE BREAK'
UNION
SELECT /*+ leading(la) */ DISTINCT pb.pricing_attribute
FROM qp_list_lines la,
qp_pricing_attributes pb,
qp_rltd_modifiers ra,
qp_rltd_modifiers rb
WHERE la.orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I) AND
la.list_line_type_code = 'PLL' AND
la.pricing_phase_id = 1 AND
ra.to_rltd_modifier_id = la.list_line_id AND
rb.from_rltd_modifier_id = ra.FROM_RLTD_MODIFIER_ID AND
pb.list_line_id = rb.to_rltd_modifier_id AND
rb.rltd_modifier_grp_type = 'PRICE BREAK');
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I), 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I), 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I), 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I), 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
UPDATE qp_interface_list_lines
SET process_status_flag = NULL --'E'
WHERE orig_sys_line_ref = p_pricing_attr_rec.orig_sys_line_ref(I)
AND orig_sys_header_ref = p_pricing_attr_rec.orig_sys_header_ref(I);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
VALUES
(qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_pricing_attr_rec.request_id(I), 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
null,l_msg_txt);
IF p_pricing_attr_rec.interface_action_code(I) = 'UPDATE'
AND p_pricing_attr_rec.process_status_flag(I)='P' THEN --,'*') <> 'E' THEN
l_old_pricing_attr_rec := Qp_pll_pricing_attr_Util.Query_Row
( p_pricing_attribute_id => l_pricing_attribute_id
);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'CURRENCY_CODE',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.currency_code is not null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS (SELECT currency_code -- Validation
FROM fnd_currencies_vl
WHERE enabled_flag = 'Y'
AND currency_flag='Y'
AND currency_code = qpih.currency_code
);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FREIGHT TERMS',
qpih.orig_sys_header_ref,null,null,null, l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.freight_terms_code IS NOT NULL
AND qpih.freight_terms_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpih.freight_terms IS NOT NULL
AND NOT EXISTS (SELECT freight_terms_code
FROM OE_FRGHT_TERMS_ACTIVE_V
WHERE freight_terms_code = qpih.freight_terms_code
AND freight_terms = qpih.freight_terms
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate));
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FREIGHT TERMS',
qpih.orig_sys_header_ref,null,null,null, l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.freight_terms IS NOT NULL
AND qpih.freight_terms_code IS NULL
AND NOT EXISTS (SELECT freight_terms_code
FROM OE_FRGHT_TERMS_ACTIVE_V
WHERE freight_terms = qpih.freight_terms
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate));
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FREIGHT TERMS',
qpih.orig_sys_header_ref,null,null,null, l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.freight_terms IS NULL
AND qpih.freight_terms_code IS NOT NULL
AND qpih.freight_terms_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS (SELECT freight_terms_code
FROM OE_FRGHT_TERMS_ACTIVE_V
WHERE freight_terms_code = qpih.freight_terms_code
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate));
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'LIST_TYPE_CODE',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.list_type_code IS NOT NULL
AND NOT EXISTS (SELECT lookup_code
FROM qp_lookups
WHERE lookup_type = 'LIST_TYPE_CODE'
AND lookup_code = qpih.list_type_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FRIEGHT CARRIER',
qpih.orig_sys_header_ref, null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.ship_method IS NOT NULL
AND qpih.ship_method_code IS NOT NULL
AND qpih.ship_method_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_SHIP_METHODS_V
WHERE LOOKUP_TYPE = 'SHIP_METHOD'
AND LOOKUP_CODE = qpih.ship_method_code
AND MEANING = qpih.ship_method
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FRIEGHT CARRIER',
qpih.orig_sys_header_ref, null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.ship_method IS NULL
AND qpih.ship_method_code IS NOT NULL
AND qpih.ship_method_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_SHIP_METHODS_V
WHERE LOOKUP_TYPE = 'SHIP_METHOD'
AND LOOKUP_CODE = qpih.ship_method_code
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'FRIEGHT CARRIER',
qpih.orig_sys_header_ref, null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.ship_method IS NOT NULL
AND qpih.ship_method_code IS NULL
AND NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_SHIP_METHODS_V
WHERE LOOKUP_TYPE = 'SHIP_METHOD'
AND MEANING = qpih.ship_method
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'PAYMENT TERMS',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.terms_id IS NOT NULL
AND qpih.terms IS NOT NULL
AND qpih.terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
--Bug#5248659 RAVI
--Use Term_id not terms_id
AND NOT EXISTS (SELECT TERM_ID
FROM RA_TERMS
WHERE TERM_ID = qpih.terms_id
AND name = qpih.terms
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'PAYMENT TERMS',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.terms_id IS NOT NULL
AND qpih.terms IS NULL
--Bug#5248659 RAVI
--Use Term_id not terms_id
AND NOT EXISTS (SELECT TERM_ID
FROM RA_TERMS
WHERE TERM_ID = qpih.terms_id
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'PAYMENT TERMS',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.terms_id IS NULL
AND qpih.terms IS NOT NULL
AND qpih.terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
--Bug#5248659 RAVI
--Use Term_id not terms_id
AND NOT EXISTS (SELECT TERM_ID
FROM RA_TERMS
WHERE name = qpih.terms
AND sysdate BETWEEN nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate) );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'CURRENCY_HEADER',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.currency_header_id is NOT NULL
AND qpih.currency_header is NOT NULL
AND qpih.currency_header <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS ( SELECT qpclb.currency_header_id
FROM qp_currency_lists_b qpclb, qp_currency_lists_tl qpclt
WHERE qpclb.currency_header_id = qpih.currency_header_id
AND qpclb.base_currency_code = qpih.currency_code
AND qpclt.currency_header_id = qpclb.currency_header_id
AND qpclt.language = nvl(qpih.language,'US')
AND qpclt.name = qpih.currency_header);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'CURRENCY_HEADER',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.currency_header is NOT NULL
AND qpih.currency_header <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpih.currency_header_id is NULL
AND NOT EXISTS ( SELECT qpclb.currency_header_id
FROM qp_currency_lists_b qpclb, qp_currency_lists_tl qpclt
WHERE qpclb.base_currency_code = qpih.currency_code
AND qpclt.currency_header_id = qpclb.currency_header_id
AND qpclt.language = nvl(qpih.language,'US')
AND qpclt.name = qpih.currency_header);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'CURRENCY_HEADER',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.currency_header_id is NOT NULL
AND qpih.currency_header is NULL
AND NOT EXISTS ( SELECT qpclb.currency_header_id
FROM qp_currency_lists_b qpclb
WHERE qpclb.currency_header_id = qpih.currency_header_id
AND qpclb.base_currency_code = qpih.currency_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
-- AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.interface_action_code IN ('INSERT')
AND (name is null or name = '');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT')
AND EXISTS ( Select qlht.name from qp_list_headers_tl qlht
where qlht.name= qpih.name
and qlht.language = qpih.language);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT')
AND EXISTS ( Select 'x' from qp_interface_list_headers qpih1
where qpih1.request_id = p_request_id
and qpih1.name = qpih.name
and qpih1.process_status_flag is null
and qpih1.rowid <> qpih.rowid
and qpih1.interface_action_code = 'INSERT');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'pte_code',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.pte_code IS NOT NULL
AND NOT EXISTS (SELECT lookup_code
FROM qp_lookups
WHERE lookup_type = 'QP_PTE_TYPE'
AND lookup_code = qpih.pte_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.source_system_code IS NOT NULL
AND NOT EXISTS (SELECT lookup_code
FROM qp_lookups
WHERE lookup_type = 'SOURCE_SYSTEM'
AND lookup_code = qpih.source_system_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
-- Bug 5416556 RAVI
--Only Price Lists with source system code equal to FND_PROFILE.VALUE('QP_SOURCE_SYSTEM_CODE') should be updated.
AND qpih.source_system_code <> FND_PROFILE.VALUE('QP_SOURCE_SYSTEM_CODE');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'Name',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
AND qpih.global_flag = 'N' and qpih.orig_org_id IS NOT NULL
AND QP_UTIL.validate_org_id(qpih.orig_org_id) = 'N';
FND_MESSAGE.SET_NAME('QP','QP_ROUNDING_FACTOR_NO_UPDATE');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'ROUNDING_FACTOR',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code ='UPDATE'
AND EXISTS(
select 1 from qp_list_headers qplh
where qplh.orig_system_header_ref=qpih.orig_sys_header_ref
and qplh.rounding_factor <> qpih.rounding_factor
);
/*fnd_message.set_name('QP', 'QP_HDR_REF_MULTIPLE_UPDATE');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpih.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', 'ORIG_SYS_HEADER_REF',
qpih.orig_sys_header_ref,null,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_HEADERS qpih
WHERE qpih.request_id = p_request_id
AND qpih.process_status_flag ='P' --is null
AND qpih.interface_action_code IN ('UPDATE', 'DELETE')
AND ( select count(*) from qp_interface_list_headers qplh
where qplh.orig_sys_header_ref=qpih.orig_sys_header_ref
) > 1;*/
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', 'COMPARISON_OPERATOR',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null, l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.comparison_operator_code is NOT NULL
AND NOT EXISTS ( SELECT lookup_code
FROM qp_lookups
WHERE lookup_type = 'COMPARISON_OPERATOR'
AND lookup_code = qpiq.comparison_operator_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', 'ORIG_SYS_HEADER_REF',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null,l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.orig_sys_header_ref is NOT NULL
AND NOT EXISTS ( SELECT orig_system_header_ref
FROM qp_list_headers_b
WHERE orig_system_header_ref = qpiq.orig_sys_header_ref);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null, l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND (qpiq.qualifier_context <> 'MODLIST'
OR qpiq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref, error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null, l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.qualifier_context IS NULL;
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', 'QUALIFIER_ATTRIBUTE',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null,l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.qualifier_attribute is not null
AND qpiq.qualifier_attribute_code is not null
AND qpiq.qualifier_attribute_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS (
SELECT 'x'
FROM qp_interface_qualifiers b,
qp_prc_contexts_b d,
qp_segments_b e,
qp_pte_segments f
WHERE b.qualifier_context = d.prc_context_code
AND b.qualifier_attribute = e.segment_mapping_column
and e.segment_code = b.qualifier_attribute_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'QUALIFIER'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level NOT IN ('LINE')
AND qpiq.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', 'QUALIFIER_ATTRIBUTE',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null,l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.qualifier_attribute is null
AND qpiq.qualifier_attribute_code is not null
AND qpiq.qualifier_attribute_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS (
SELECT 'x'
FROM qp_interface_qualifiers b,
qp_prc_contexts_b d,
qp_segments_b e,
qp_pte_segments f
WHERE b.qualifier_context = d.prc_context_code
and e.segment_code = b.qualifier_attribute_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'QUALIFIER'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level NOT IN ('LINE')
AND qpiq.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpiq.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', 'QUALIFIER_ATTRIBUTE',
qpiq.orig_sys_header_ref,null,qpiq.orig_sys_qualifier_ref,null,l_msg_txt
FROM QP_INTERFACE_QUALIFIERS qpiq
WHERE qpiq.request_id = p_request_id
AND qpiq.process_status_flag ='P' --is null
AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
AND qpiq.qualifier_attribute is not null
AND qpiq.qualifier_attribute_code is null
AND NOT EXISTS (
SELECT 'x'
FROM qp_interface_qualifiers b,
qp_prc_contexts_b d,
qp_segments_b e,
qp_pte_segments f
WHERE b.qualifier_context = d.prc_context_code
AND b.qualifier_attribute = e.segment_mapping_column
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'QUALIFIER'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level NOT IN ('LINE')
AND qpiq.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'LIST_LINE_TYPE_CODE',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.list_line_type_code is NOT NULL
AND NOT EXISTS ( SELECT lookup_code
FROM qp_lookups
WHERE lookup_type= 'LIST_LINE_TYPE_CODE'
AND lookup_code= qpil.list_line_type_code
AND qpil.list_line_type_code IN ('PLL', 'PBH') );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'ARITHMETIC_OPERATOR',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.arithmetic_operator is NOT NULL
AND NOT EXISTS ( SELECT meaning
FROM qp_lookups
WHERE lookup_type= 'ARITHMETIC_OPERATOR'
AND lookup_code= qpil.arithmetic_operator );
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRICE_BREAK_TYPE_CODE',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.price_break_type_code is NOT NULL
AND NOT EXISTS ( SELECT meaning
FROM qp_lookups
WHERE lookup_type= 'PRICE_BREAK_TYPE_CODE'
AND lookup_code= qpil.price_break_type_code);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT /*+ index(qpip QP_INTERFACE_PRCNG_ATTRIBS_N4) */
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_PRICING_ATTRIBS', 'PRODUCT_UOM_CODE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip, QP_LIST_HEADERS_B qplh
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
-- ENH unod alcoa changes RAVI
/**
The key between interface and qp tables is only orig_sys_hdr_ref
(not list_header_id)
**/
AND qpil.orig_sys_header_ref = qplh.orig_system_header_ref
AND qpip.pricing_attribute_context IS NULL
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpip.interface_action_code IN ('INSERT', 'UPDATE')
AND qpip.product_uom_code is NOT NULL
and qpip.product_attribute_context = 'ITEM'
and qpip.product_attribute = 'PRICING_ATTRIBUTE1'
AND NOT EXISTS (
select uom_code
from mtl_item_uoms_view
where organization_id = nvl(qpil.organization_id, organization_id)
and inventory_item_id = to_number(qpip.product_attr_value)
and uom_code = qpip.product_uom_code
-- commented out for bug 4713401
/*
union
select uom_code
from mtl_item_uoms_view
where (organization_id = qpil.organization_id or qpil.organization_id is null)
and inventory_item_id in ( select inventory_item_id
from mtl_item_categories
where category_id = to_number(qpip.product_attr_value)
and (organization_id = qpil.organization_id or qpil.organization_id is null ))
and qpip.product_attribute_context = 'ITEM'
and qpip.product_attribute = 'PRICING_ATTRIBUTE2'
and uom_code = qpip.product_uom_code
*/
-- added for bug 4713401
/* commented out as this code is not performant
union
select uom_code
from mtl_item_uoms_view
where (organization_id = qpil.organization_id or qpil.organization_id is null) and
qpip.product_attribute_context = 'ITEM' and
qpip.product_attribute = 'PRICING_ATTRIBUTE2' and
uom_code = qpip.product_uom_code and
inventory_item_id in ( select inventory_item_id
from mtl_item_categories cat
where (category_id = to_number(qpip.product_attr_value) or
exists (SELECT 'Y'
FROM eni_denorm_hierarchies
WHERE parent_id = to_number(qpip.product_attr_value) and
child_id = cat.category_id
and exists (select 'Y' from QP_SOURCESYSTEM_FNAREA_MAP A,
qp_pte_source_systems B ,
mtl_default_category_sets c,
mtl_category_sets d
where A.PTE_SOURCE_SYSTEM_ID = B.PTE_SOURCE_SYSTEM_ID and
B.PTE_CODE = qplh.pte_code and
B.APPLICATION_SHORT_NAME = qplh.source_system_code and
A.FUNCTIONAL_AREA_ID = c.FUNCTIONAL_AREA_ID and
c.CATEGORY_SET_ID = d.CATEGORY_SET_ID and
d.HIERARCHY_ENABLED = 'Y' and
A.ENABLED_FLAG = 'Y' and B.ENABLED_FLAG = 'Y'))))
*/
union
select uom_code
from mtl_units_of_measure_vl
where uom_code = qpip.product_uom_code
);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_PRICING_ATTRIBS', 'PRODUCT_UOM_CODE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip, QP_LIST_HEADERS_B qplh
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpil.orig_sys_header_ref = qplh.orig_system_header_ref
AND qpip.pricing_attribute_context IS NULL
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpip.interface_action_code IN ('INSERT', 'UPDATE')
AND qpip.product_uom_code is NOT NULL
and qpip.product_attribute_context = 'ITEM'
and qpip.product_attribute = 'PRICING_ATTRIBUTE2'
AND NOT EXISTS (
select 'EXISTS'
from dual
where QP_BULK_VALIDATE.product_uom(
qpip.product_uom_code,
to_number(qpip.product_attr_value),
qplh.list_header_id) = 'TRUE'
);
Only one formula should be inserted or updated in a line. Either PriceBy or Generate Formula is to be used.
If one is used the other should be set to null. If both are used then an error should be thrown.
**/
FND_MESSAGE.SET_NAME('QP','QP_STATIC_OR_DYNAMIC_FORMULA');
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRICE_BY_AND_GENERATE_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.price_by_formula_id is NOT NULL
AND qpil.price_by_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER
AND qpil.generate_using_formula_id is NOT NULL
AND qpil.generate_using_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER;
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRICE_BY_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.price_by_formula_id is NOT NULL
AND qpil.price_by_formula is NOT NULL
AND qpil.price_by_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE price_formula_id = qpil.price_by_formula_id
AND name = qpil.price_by_formula);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRICE_BY_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.price_by_formula_id is NOT NULL
AND qpil.price_by_formula is NULL
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE price_formula_id = qpil.price_by_formula_id);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRICE_BY_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.price_by_formula_id is NULL
AND qpil.price_by_formula is NOT NULL
AND qpil.price_by_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE name = qpil.price_by_formula);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'GENERATE_USING_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.generate_using_formula_id is NOT NULL
AND qpil.generate_using_formula is NOT NULL
AND qpil.generate_using_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE price_formula_id = qpil.generate_using_formula_id
AND name = qpil.generate_using_formula);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'GENERATE_USING_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.generate_using_formula_id is NOT NULL
AND qpil.generate_using_formula is NULL
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE price_formula_id = qpil.generate_using_formula_id);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'GENERATE_USING_FORMULA_ID',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND qpil.generate_using_formula_id is NULL
AND qpil.generate_using_formula is NOT NULL
AND qpil.generate_using_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND NOT EXISTS ( SELECT name
FROM qp_price_formulas_vl
WHERE name = qpil.generate_using_formula);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES',
qpil.orig_sys_header_ref,qpil.orig_sys_line_ref,null,null,l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil
WHERE qpil.request_id = p_request_id
AND qpil.process_status_flag ='P' --is null
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND (qpil.list_line_type_code = 'PBH'
OR qpil.price_break_header_ref is not NULL);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.pricing_attribute_context IS NULL
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.product_attribute_context = d.prc_context_code
AND b.product_attribute = e.segment_mapping_column
and e.segment_code = b.product_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRODUCT'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level <> 'LINE'
AND qpip.rowid = b.rowid
union
SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.product_attribute_context = d.prc_context_code
AND b.product_attribute is NULL and e.segment_code = b.product_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRODUCT'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND f.segment_level <> 'LINE'
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.product_attribute_context IS NOT NULL
AND qpip.product_attribute IS NOT NULL
AND qpip.product_attr_code IS NOT NULL
AND qpip.product_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.product_attribute_context = d.prc_context_code
AND b.product_attribute = e.segment_mapping_column
and e.segment_code = b.product_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRODUCT'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.product_attribute_context IS NOT NULL
AND qpip.product_attribute IS NULL
AND qpip.product_attr_code IS NOT NULL
AND qpip.product_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.product_attribute_context = d.prc_context_code
and e.segment_code = b.product_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRODUCT'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.product_attribute_context IS NOT NULL
AND qpip.product_attribute IS NOT NULL
AND qpip.product_attr_code IS NULL
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.product_attribute_context = d.prc_context_code
AND b.product_attribute = e.segment_mapping_column
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRODUCT'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.pricing_attribute_context IS NOT NULL
AND qpip.pricing_attribute IS NOT NULL
AND qpip.pricing_attr_code IS NOT NULL
AND qpip.pricing_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.pricing_attribute_context = d.prc_context_code
AND b.pricing_attribute = e.segment_mapping_column
and e.segment_code = b.pricing_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRICING_ATTRIBUTE'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.pricing_attribute_context IS NOT NULL
AND qpip.pricing_attribute IS NULL
AND qpip.pricing_attr_code IS NOT NULL
AND qpip.pricing_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.pricing_attribute_context = d.prc_context_code
and e.segment_code = b.pricing_attr_code
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRICING_ATTRIBUTE'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
INSERT INTO QP_INTERFACE_ERRORS
(error_id,last_update_date, last_updated_by, creation_date,
created_by, last_update_login, request_id, program_application_id,
program_id, program_update_date, entity_type, table_name, column_name,
orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
orig_sys_pricing_attr_ref,error_message)
SELECT
qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, qpil.request_id, 661,
NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', 'PRODUCT_ATTRIBUTE',
qpip.orig_sys_header_ref,qpip.orig_sys_line_ref,null,
qpip.orig_sys_pricing_attr_ref, l_msg_txt
FROM QP_INTERFACE_LIST_LINES qpil, QP_INTERFACE_PRICING_ATTRIBS qpip
WHERE qpil.request_id = p_request_id
AND qpip.request_id = p_request_id -- bug no 5881528
AND qpil.process_status_flag ='P' --is null
AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
AND qpip.pricing_attribute_context IS NOT NULL
AND qpip.pricing_attribute IS NOT NULL
AND qpip.pricing_attr_code IS NULL
AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
AND NOT EXISTS ( SELECT 'x'
FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
WHERE b.pricing_attribute_context = d.prc_context_code
AND b.pricing_attribute = e.segment_mapping_column
AND d.prc_context_id = e.prc_context_id
AND d.prc_context_type = 'PRICING_ATTRIBUTE'
AND e.segment_id = f.segment_id
AND f.pte_code = l_pte_code
AND qpip.rowid = b.rowid);
UPDATE qp_interface_list_headers h
SET h.process_status_flag = NULL --'E'
WHERE h.request_id = p_request_id
AND EXISTS
(SELECT orig_sys_header_ref
FROM qp_interface_errors e
WHERE e.orig_sys_header_ref = h.orig_sys_header_ref
AND e.table_name ='QP_INTERFACE_LIST_HEADERS'
AND e.request_id = p_request_id );
UPDATE qp_interface_qualifiers q
SET process_status_flag = NULL --'E'
WHERE q.request_id =p_request_id
AND EXISTS
(SELECT orig_sys_qualifier_ref
FROM qp_interface_errors e
WHERE e.orig_sys_qualifier_ref = q.orig_sys_qualifier_ref
AND e.orig_sys_header_ref = q.orig_sys_header_ref
AND e.table_name ='QP_INTERFACE_QUALIFIERS'
AND e.request_id = p_request_id);
UPDATE qp_interface_list_lines l
SET process_status_flag = NULL --'E'
WHERE l.request_id =p_request_id
AND EXISTS
(SELECT orig_sys_line_ref
FROM qp_interface_errors e
WHERE e.orig_sys_line_ref = l.orig_sys_line_ref
AND e.orig_sys_header_ref = l.orig_sys_header_ref
AND table_name ='QP_INTERFACE_LIST_LINES'
AND e.request_id = p_request_id );
UPDATE qp_interface_pricing_attribs a
SET process_status_flag = NULL --'E'
WHERE a.request_id =p_request_id
AND EXISTS
(SELECT orig_sys_pricing_attr_ref
FROM qp_interface_errors e
WHERE e.orig_sys_line_ref = a.orig_sys_line_ref
AND e.orig_sys_header_ref = a.orig_sys_header_ref
AND e.orig_sys_pricing_attr_ref = a.orig_sys_pricing_attr_ref
AND table_name ='QP_INTERFACE_PRICING_ATTRIBS'
AND e.request_id = p_request_id );