[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT d.*
FROM oe_def_attr_condns b,
ak_object_attributes a,
oe_def_condn_elems c,
oe_def_condn_elems c1,
oe_def_attr_def_rules d
WHERE substr(c.value_string,1,30) = p_prc_context_code and
b.attribute_code = p_segment_mapping_column and
substr(c1.value_string,1,30) = p_application_short_name and
b.attr_def_condition_id = d.attr_def_condition_id and
b.database_object_name = a.database_object_name and
b.database_object_name = d.database_object_name and
b.attribute_code = a.attribute_code and
b.attribute_code = d.attribute_code and
c.condition_id = b.condition_id and
c1.condition_id = b.condition_id and
c.attribute_code like '%CONTEXT%' and
c1.attribute_code = 'SRC_SYSTEM_CODE' and
a.attribute_application_id = 661 and
nvl(b.enabled_flag,'Y') = 'Y';
UPDATE oe_def_attr_condns
SET enabled_flag = 'N'
WHERE
Condition_id IN (
SELECT C.condition_id
FROM
oe_def_attr_condns C,
oe_def_conditions_vl CT
WHERE
C.condition_id = CT.condition_id AND
C. database_object_name IN (
'QP_HDR_QUALIF_ATTRIBS_V',
'QP_LINE_PRICING_ATTRIBS_V',
'QP_LINE_QUALIF_ATTRIBS_V' ) AND
CT.display_name like 'ONT%' AND
C.condition_id > 1000 AND
C.system_flag= 'Y' AND
1< (SELECT count(*) FROM oe_def_conditions_vl CTS,
oe_def_attr_condns CS
WHERE
CTS.display_name = CT.display_name
AND CTS.condition_id = CS.condition_id
AND CS.attribute_code = C.attribute_code )
);
select pte_code
into l_pte_code
from qp_pte_request_types_b
where request_type_code = p_request_type_code and
rownum = 1;
select 'x'
into dummy
from qp_pte_source_systems
where application_short_name = p_application_short_name and
pte_code = p_pte_code and
rownum = 1;
select 'x'
into dummy
from qp_pte_request_types_b
where request_type_code = p_request_type_code and
rownum = 1;
select 'x'
into dummy
from qp_pte_segments
where pte_code = p_pte_code and
segment_id = p_segment_id and
rownum = 1;
select 'x'
into dummy
from qp_attribute_sourcing
where segment_id = p_segment_id and
request_type_code = p_request_type_code and
attribute_sourcing_level = p_attribute_sourcing_level and
rownum = 1;
select prc_context_id
into l_prc_context_id
from qp_prc_contexts_b
where prc_context_code = p_prc_context_code and
((p_flexfield_name = 'QP_ATTR_DEFNS_QUALIFIER' and prc_context_type = 'QUALIFIER') or
(p_flexfield_name <> 'QP_ATTR_DEFNS_QUALIFIER' and prc_context_type <> 'QUALIFIER')) and
rownum = 1;
select 'x'
into dummy
from qp_segments_b a,qp_prc_contexts_b b
where a.prc_context_id = b.prc_context_id and
b.prc_context_code = p_prc_context_code and
(a.segment_code = p_segment_code or a.segment_mapping_column = p_segment_mapping_column) and
((p_flexfield_name = 'QP_ATTR_DEFNS_QUALIFIER' and b.prc_context_type = 'QUALIFIER') or
(p_flexfield_name <> 'QP_ATTR_DEFNS_QUALIFIER' and b.prc_context_type <> 'QUALIFIER')) and
rownum = 1;
SELECT 'x'
INTO dummy
FROM oe_def_attr_condns b,
ak_object_attributes oa,
oe_def_condn_elems c1,
oe_def_condn_elems c2
WHERE c1.value_string = p_prc_context_code and
b.attribute_code = p_segment_mapping_column and
b.database_object_name = oa.database_object_name and
b.attribute_code = oa.attribute_code and
c1.condition_id = b.condition_id and
c1.attribute_code like '%CONTEXT%' and
c2.condition_id = b.condition_id and
c2.attribute_code = 'SRC_SYSTEM_CODE' and
--c2.value_string = 'QP' and
oa.attribute_application_id = 661 and
/* commented out as enabled_flag is also upgraded now */
--nvl(b.enabled_flag,'Y') = 'Y' and,
rownum = 1;
SELECT 'x'
INTO dummy
FROM oe_def_attr_condns b,
ak_object_attributes oa,
oe_def_condn_elems c1
WHERE c1.value_string = p_prc_context_code and
b.attribute_code = p_segment_mapping_column and
b.database_object_name = oa.database_object_name and
b.attribute_code = oa.attribute_code and
c1.condition_id = b.condition_id and
c1.attribute_code like '%CONTEXT%' and
oa.attribute_application_id = 661 and
(b.database_object_name like '%HEADER%' or
b.database_object_name like '%HDR%') and
/* commented out as enabled_flag is also upgraded now */
-- nvl(b.enabled_flag,'Y') = 'Y' and
rownum = 1;
SELECT 'x'
INTO dummy
FROM oe_def_attr_condns b,
ak_object_attributes oa,
oe_def_condn_elems c1
WHERE c1.value_string = p_prc_context_code and
b.attribute_code = p_segment_mapping_column and
b.database_object_name = oa.database_object_name and
b.attribute_code = oa.attribute_code and
c1.condition_id = b.condition_id and
c1.attribute_code like '%CONTEXT%' and
oa.attribute_application_id = 661 and
b.database_object_name like '%LINE%' and
/* commented out as enabled_flag is also upgraded now */
-- nvl(b.enabled_flag,'Y') = 'Y' and
rownum = 1;
select 'x'
into dummy
from qp_pte_segments
where segment_id = p_segment_id and
pte_code = 'ONT' and
rownum = 1;
select format_type
into l_format_type
from fnd_flex_value_sets
where flex_value_set_id = p_value_set_id and
rownum = 1;
PROCEDURE p_insert_lookup_code (p_lookup_type in varchar2,
p_lookup_code in varchar2) is
-- Private procedure to insert new PTEs and Request Types.
l_row_id varchar2(25);
FND_LOOKUP_VALUES_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_LOOKUP_TYPE => p_lookup_type,
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 661,
X_LOOKUP_CODE => p_lookup_code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => sysdate,
X_END_DATE_ACTIVE => null,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_MEANING => l_meaning,
X_DESCRIPTION => l_meaning,
X_CREATION_DATE => sysdate,
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => null);
PROCEDURE p_insert_context_b (p_flexfield_name in varchar2) is
-- Private procedure to insert Contexts in QP_PRC_CONTEXTS_B.
begin
g_context_seqno := g_context_seqno + 1;
INSERT into QP_PRC_CONTEXTS_B
(PRC_CONTEXT_ID,
PRC_CONTEXT_CODE,
PRC_CONTEXT_TYPE,
SEEDED_FLAG,
ENABLED_FLAG,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(g_context_seqno,
g_context_b_rec.descriptive_flex_context_code,
decode(p_flexfield_name,'QP_ATTR_DEFNS_QUALIFIER','QUALIFIER',
decode(g_context_b_rec.descriptive_flex_context_code,'ITEM','PRODUCT','PRICING_ATTRIBUTE')),
decode(g_context_b_rec.created_by,1,'Y','N'),
g_context_b_rec.enabled_flag,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
g_context_b_rec.created_by,
g_context_b_rec.creation_date,
g_context_b_rec.last_updated_by,
g_context_b_rec.last_update_date,
g_context_b_rec.last_update_login,
null,null,null);
PROCEDURE p_insert_context_tl is
-- Private procedure to insert Contexts in QP_PRC_CONTEXTS_TL.
begin
INSERT into QP_PRC_CONTEXTS_TL
(PRC_CONTEXT_ID,
LANGUAGE,
SOURCE_LANG,
SEEDED_PRC_CONTEXT_NAME,
USER_PRC_CONTEXT_NAME,
SEEDED_DESCRIPTION,
USER_DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ) values
(g_context_seqno,
g_context_tl_rec.language,
g_context_tl_rec.source_lang,
g_context_tl_rec.descriptive_flex_context_name,
g_context_tl_rec.descriptive_flex_context_name,
g_context_tl_rec.description,
nvl(g_context_tl_rec.description,'** No description found **'),
g_context_tl_rec.created_by,
g_context_tl_rec.creation_date,
g_context_tl_rec.last_updated_by,
g_context_tl_rec.last_update_date,
g_context_tl_rec.last_update_login);
PROCEDURE p_insert_segment_b (p_prc_context_id in number,
p_valueset_id in number,
p_format_type in varchar2) is
-- Private procedure to insert Segments in QP_SEGMENTS_B.
begin
g_segment_seqno := g_segment_seqno + 1;
INSERT into QP_SEGMENTS_B
(SEGMENT_ID,
SEGMENT_CODE,
PRC_CONTEXT_ID,
AVAILABILITY_IN_BASIC,
APPLICATION_ID,
SEGMENT_MAPPING_COLUMN,
SEEDED_FLAG,
REQUIRED_FLAG,
SEEDED_PRECEDENCE,
USER_PRECEDENCE,
SEEDED_VALUESET_ID,
USER_VALUESET_ID,
SEEDED_FORMAT_TYPE,
USER_FORMAT_TYPE,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(g_segment_seqno,
g_segment_b_rec.end_user_column_name,
p_prc_context_id,
decode(qp_util.get_qp_status,'S','Y','N'),
661,
g_segment_b_rec.application_column_name,
decode(g_segment_b_rec.created_by,1,'Y','N'),
g_segment_b_rec.required_flag,
g_segment_b_rec.column_seq_num,
g_segment_b_rec.column_seq_num,
p_valueset_id,
p_valueset_id,
p_format_type,
p_format_type,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
g_segment_b_rec.created_by,
g_segment_b_rec.creation_date,
g_segment_b_rec.last_updated_by,
g_segment_b_rec.last_update_date,
g_segment_b_rec.last_update_login,
null,null,null);
PROCEDURE p_insert_segment_tl is
-- Private procedure to insert Segments in QP_SEGMENTS_TL.
begin
INSERT into QP_SEGMENTS_TL
(SEGMENT_ID,
LANGUAGE,
SOURCE_LANG,
SEEDED_SEGMENT_NAME,
USER_SEGMENT_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN ) values
(g_segment_seqno,
g_segment_tl_rec.language,
g_segment_tl_rec.source_lang,
g_segment_tl_rec.form_left_prompt,
g_segment_tl_rec.form_left_prompt,
g_segment_tl_rec.created_by,
g_segment_tl_rec.creation_date,
g_segment_tl_rec.last_updated_by,
g_segment_tl_rec.last_update_date,
g_segment_tl_rec.last_update_login);
PROCEDURE p_insert_ssc (p_source_system_code in varchar2,
p_pte_code in varchar2) is
-- Private procedure to insert Source Systems.
begin
g_ssc_seqno := g_ssc_seqno + 1;
INSERT into QP_PTE_SOURCE_SYSTEMS
(PTE_SOURCE_SYSTEM_ID,
PTE_CODE,
APPLICATION_SHORT_NAME,
ENABLED_FLAG,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(g_ssc_seqno,
p_pte_code,
p_source_system_code,
'Y',
null,null,null,null,
null,null,null,null,
null,null,null,null,
null,null,null,null,
1,
sysdate,
1,
sysdate,
null,
null,null,null);
PROCEDURE p_insert_rqt (p_request_type_code in varchar2,
p_pte_code in varchar2) is
-- Private procedure to insert Request Types in QP_PTE_REQUEST_TYPES_B/TL tables.
l_order_level_global_struct varchar2(80);
INSERT into QP_PTE_REQUEST_TYPES_B
(REQUEST_TYPE_CODE,
PTE_CODE,
ORDER_LEVEL_GLOBAL_STRUCT,
LINE_LEVEL_GLOBAL_STRUCT,
ORDER_LEVEL_VIEW_NAME,
LINE_LEVEL_VIEW_NAME,
ENABLED_FLAG,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(p_request_type_code,
p_pte_code,
l_order_level_global_struct,
l_line_level_global_struct,
null,
null,
'Y',
null,null,null,null,
null,null,null,null,
null,null,null,null,
null,null,null,null,
1,
sysdate,
1,
sysdate,
null,
null,null,null);
INSERT into QP_PTE_REQUEST_TYPES_TL
(REQUEST_TYPE_CODE,
LANGUAGE,
SOURCE_LANG,
REQUEST_TYPE_DESC,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
select p_request_type_code,
language,
source_lang,
nvl(description,meaning),
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
from fnd_lookup_values
where lookup_type = 'REQUEST_TYPE' and
lookup_code = p_request_type_code;
PROCEDURE p_insert_pte_segments (p_segment_id in number,
p_pte_code in varchar2,
p_segment_level in varchar2,
p_sourcing_method in varchar2,
p_sourcing_enabled in varchar2) is
-- Private procedure to insert PTE-Segments in qp_pte_segments.
begin
g_psg_seqno := g_psg_seqno + 1;
INSERT into QP_PTE_SEGMENTS
(SEGMENT_PTE_ID,
SEGMENT_ID,
PTE_CODE,
SEGMENT_LEVEL,
SOURCING_ENABLED,
SEEDED_SOURCING_METHOD,
USER_SOURCING_METHOD,
SOURCING_STATUS,
LOV_ENABLED,
LIMITS_ENABLED,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(g_psg_seqno,
p_segment_id,
p_pte_code,
p_segment_level,
p_sourcing_enabled,
p_sourcing_method,
p_sourcing_method,
'N',
'Y',
'Y',
null,null,null,null,
null,null,null,null,
null,null,null,null,
null,null,null,null,
1, -- fnd_profile.value('USER_ID')
sysdate,
1,
sysdate,
null,
null,null,null);
PROCEDURE p_insert_sourcing( p_request_type_code in varchar2,
p_attribute_sourcing_level in varchar2,
p_value_string in varchar2,
p_enabled_flag in varchar2) is
-- Private procedure to insert Sourcing rules in QP_ATTRIBUTE_SOURCING.
begin
g_source_seqno := g_source_seqno + 1;
INSERT into QP_ATTRIBUTE_SOURCING
(ATTRIBUTE_SOURCING_ID,
SEGMENT_ID,
REQUEST_TYPE_CODE,
ATTRIBUTE_SOURCING_LEVEL,
APPLICATION_ID,
SEEDED_SOURCING_TYPE,
USER_SOURCING_TYPE,
SEEDED_VALUE_STRING,
USER_VALUE_STRING,
SEEDED_FLAG,
ENABLED_FLAG,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE) values
(g_source_seqno,
g_all_seg_rec.segment_id,
p_request_type_code,
p_attribute_sourcing_level,
661,
g_sourcing_rec.src_type,
g_sourcing_rec.src_type,
p_value_string,
p_value_string,
decode(g_sourcing_rec.created_by,1,'Y','N'),
p_enabled_flag, --'Y', upgrade enabled_flag also
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
g_sourcing_rec.created_by,
g_sourcing_rec.creation_date,
g_sourcing_rec.last_updated_by,
g_sourcing_rec.last_update_date,
g_sourcing_rec.last_update_login,
null,null,null);
PROCEDURE p_delete_PTE_attribute_links is
-- Private procedure to delete certain unwanted PTE_Attribute Links.
-- Private procedure to find out if Attribute Mapping exists.
begin
/*
--
-- 1. For LOGISTICS context and PTEs other than 'Logistics',
--
delete from qp_pte_segments a
where a.pte_code <> 'LOGSTX' and
exists ( select 'x'
from qp_segments_b b,
qp_prc_contexts_b c
where b.prc_context_id = c.prc_context_id and
b.segment_id = a.segment_id and
c.prc_context_code = 'LOGISTICS') and
a.created_by = 1;
delete from qp_pte_segments a
where a.pte_code = 'LOGSTX' and
exists ( select 'x'
from qp_segments_b b
where b.segment_id = a.segment_id and
b.segment_code = 'COUPON_NO') and
a.created_by = 1;
delete from qp_pte_segments a
where a.pte_code <> 'ORDFUL' and
exists ( select 'x'
from qp_segments_b b
where b.segment_id = a.segment_id and
b.segment_code = 'Number of students') and
a.created_by = 1;
delete from qp_pte_segments a
where a.pte_code = 'INTCOM' and
a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
not exists ( select 'x'
from qp_attribute_sourcing b,
qp_pte_request_types_b c
where b.segment_id = a.segment_id and
b.request_type_code = c.request_type_code and
a.pte_code = c.pte_code) and
a.created_by = 1;
delete from qp_pte_segments a
where a.pte_code = 'DEMAND' and
a.seeded_sourcing_method = 'ATTRIBUTE MAPPING' and
not exists ( select 'x'
from qp_attribute_sourcing b,
qp_pte_request_types_b c
where b.segment_id = a.segment_id and
b.request_type_code = c.request_type_code and
a.pte_code = c.pte_code) and
a.created_by = 1;
select a.segment_id,
a.segment_code
into l_segment_id,
l_segment_code
from qp_segments_b a, qp_prc_contexts_b b
where a.prc_context_id = b.prc_context_id and
a.prc_context_id = p_prc_context_id and
segment_mapping_column = p_segment_mapping_column and
segment_code <> p_segment_code and
rownum = 1;
insert into qp_upgrade_errors
(creation_date,
created_by,
last_update_date,
last_updated_by,
error_type,
error_desc,
error_module) values
(sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
'ATTRIBUTE_MANAGER_DATA_UPGRADE',
substr('A new attribute '|| l_segment_code ||
' mapped to '|| g_segment_b_rec.application_column_name ||
' already exists. Add this Attribute and it''s PTE-Links and AM Rules manually.'||
' Refer to the Pricing Implementation Guide for details.',1,200),
'Attribute Manager');
delete from qp_segments_tl where segment_id = l_segment_id;
delete from qp_segments_b where segment_id = l_segment_id;
select nvl(max(prc_context_id),100)
into g_context_seqno
from qp_prc_contexts_b
where prc_context_id < 100000;
select nvl(max(segment_id),100)
into g_segment_seqno
from qp_segments_b
where segment_id < 100000;
select nvl(max(pte_source_system_id),100)
into g_ssc_seqno
from qp_pte_source_systems
where pte_source_system_id < 100000;
select nvl(max(segment_pte_id),100)
into g_psg_seqno
from qp_pte_segments
where segment_pte_id < 100000;
select nvl(max(attribute_sourcing_id),100)
into g_source_seqno
from qp_attribute_sourcing
where attribute_sourcing_id < 100000;
SELECT *
FROM fnd_descr_flex_contexts
WHERE application_id = 661
AND descriptive_flexfield_name = p_flexfield_name
ORDER BY descriptive_flex_context_code;
SELECT *
FROM fnd_descr_flex_contexts_tl
WHERE application_id = 661
AND descriptive_flexfield_name = p_flexfield_name
AND descriptive_flex_context_code = p_context_code;
SELECT *
FROM fnd_descr_flex_column_usages
WHERE application_id = 661
AND descriptive_flexfield_name = p_flexfield_name
AND descriptive_flex_context_code = p_context_code
ORDER BY end_user_column_name;
SELECT *
FROM fnd_descr_flex_col_usage_tl
WHERE application_id = 661
AND descriptive_flexfield_name = p_flexfield_name
AND descriptive_flex_context_code = p_context_code
AND application_column_name = p_segment_code;
SELECT request_type_code,
source_system_code
FROM qp_price_req_sources
UNION
SELECT 'ONT','AMS'
FROM dual
UNION
SELECT 'ONT','QP'
FROM dual
UNION
SELECT 'MSD','QP'
FROM dual
/*
UNION
SELECT 'KEN','KQM'
FROM dual
*/
UNION
SELECT 'FTE','FTE'
FROM dual;
SELECT *
FROM qp_lookups
WHERE lookup_type = 'QP_PTE_TYPE';
SELECT pte_code
FROM qp_pte_source_systems
WHERE application_short_name = p_application_short_name;
SELECT b.prc_context_code,
a.*
FROM qp_segments_b a,
qp_prc_contexts_b b
WHERE b.prc_context_id = a.prc_context_id;
SELECT substr(c1.value_string,1,30) application_short_name
FROM oe_def_attr_condns b,
ak_object_attributes a,
oe_def_condn_elems c,
oe_def_condn_elems c1
WHERE substr(c.value_string,1,30) = p_prc_context_code and
b.attribute_code = p_segment_mapping_column and
b.database_object_name = a.database_object_name and
b.attribute_code = a.attribute_code and
c.condition_id = b.condition_id and
c.attribute_code like '%CONTEXT%' and
c1.condition_id = b.condition_id and
c1.attribute_code = 'SRC_SYSTEM_CODE' and
a.attribute_application_id = 661
/* commented out as enabled_flag is also upgraded now */
-- nvl(b.enabled_flag,'Y') = 'Y'
GROUP BY substr(c1.value_string,1,30),
substr(c.value_string,1,30),
b.attribute_code;
select a.list_header_id,b.pte_code
from qp_list_headers_b a,qp_pte_source_systems b
where nvl(a.source_system_code,'x') = b.application_short_name
order by decode(b.pte_code,'ORDFUL',2,1);
p_insert_context_b (l_flexfield_name);
p_insert_context_tl;
p_insert_segment_b (x_prc_context_id,g_segment_b_rec.flex_value_set_id, l_format_type);
p_insert_segment_b (g_context_seqno,g_segment_b_rec.flex_value_set_id, l_format_type);
p_insert_segment_tl;
p_insert_ssc(g_req_ssc_rec.source_system_code,l_pte_code);
p_insert_lookup_code('QP_PTE_TYPE',l_pte_code);
p_insert_lookup_code('REQUEST_TYPE',g_req_ssc_rec.request_type_code);
p_insert_rqt (g_req_ssc_rec.request_type_code,l_pte_code);
p_insert_ssc(g_req_ssc_rec.source_system_code,l_pte_code);
p_insert_pte_segments (g_all_seg_rec.segment_id,
l_pte_code,
l_segment_level,
'ATTRIBUTE MAPPING',
'Y');
p_insert_sourcing(l_request_type_code,l_attribute_sourcing_level,l_value_string, g_sourcing_rec.enabled_flag);
p_insert_sourcing('ASO',l_attribute_sourcing_level,l_value_string, g_sourcing_rec.enabled_flag);
p_insert_pte_segments (g_all_seg_rec.segment_id,
g_pte_rec.lookup_code,
'LINE',
'USER ENTERED',
'N');
p_delete_PTE_attribute_links;
update qp_list_headers_b
set pte_code = l_pte_code
where list_header_id = l_list_header_id;