The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,p_old_QUALIFIERS_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATED_BY;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,p_old_QUALIFIERS_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_DATE;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,p_old_QUALIFIERS_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,p_old_QUALIFIERS_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATED_BY;
ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_LOGIN;
ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_UPDATE_DATE;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,p_old_QUALIFIERS_rec.last_updated_by)
THEN
NULL;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,p_old_QUALIFIERS_rec.last_update_date)
THEN
NULL;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,p_old_QUALIFIERS_rec.last_update_login)
THEN
NULL;
IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,p_old_QUALIFIERS_rec.program_update_date)
THEN
NULL;
IF l_QUALIFIERS_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_QUALIFIERS_rec.last_updated_by := p_old_QUALIFIERS_rec.last_updated_by;
IF l_QUALIFIERS_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_QUALIFIERS_rec.last_update_date := p_old_QUALIFIERS_rec.last_update_date;
IF l_QUALIFIERS_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_QUALIFIERS_rec.last_update_login := p_old_QUALIFIERS_rec.last_update_login;
IF l_QUALIFIERS_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_QUALIFIERS_rec.program_update_date := p_old_QUALIFIERS_rec.program_update_date;
IF l_QUALIFIERS_rec.last_updated_by = FND_API.G_MISS_NUM THEN
l_QUALIFIERS_rec.last_updated_by := NULL;
IF l_QUALIFIERS_rec.last_update_date = FND_API.G_MISS_DATE THEN
l_QUALIFIERS_rec.last_update_date := NULL;
IF l_QUALIFIERS_rec.last_update_login = FND_API.G_MISS_NUM THEN
l_QUALIFIERS_rec.last_update_login := NULL;
IF l_QUALIFIERS_rec.program_update_date = FND_API.G_MISS_DATE THEN
l_QUALIFIERS_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
)
IS
l_check_active_flag VARCHAR2(1);
UPDATE QP_QUALIFIERS
SET ATTRIBUTE1 = p_QUALIFIERS_rec.attribute1
, ATTRIBUTE10 = p_QUALIFIERS_rec.attribute10
, ATTRIBUTE11 = p_QUALIFIERS_rec.attribute11
, ATTRIBUTE12 = p_QUALIFIERS_rec.attribute12
, ATTRIBUTE13 = p_QUALIFIERS_rec.attribute13
, ATTRIBUTE14 = p_QUALIFIERS_rec.attribute14
, ATTRIBUTE15 = p_QUALIFIERS_rec.attribute15
, ATTRIBUTE2 = p_QUALIFIERS_rec.attribute2
, ATTRIBUTE3 = p_QUALIFIERS_rec.attribute3
, ATTRIBUTE4 = p_QUALIFIERS_rec.attribute4
, ATTRIBUTE5 = p_QUALIFIERS_rec.attribute5
, ATTRIBUTE6 = p_QUALIFIERS_rec.attribute6
, ATTRIBUTE7 = p_QUALIFIERS_rec.attribute7
, ATTRIBUTE8 = p_QUALIFIERS_rec.attribute8
, ATTRIBUTE9 = p_QUALIFIERS_rec.attribute9
, COMPARISON_OPERATOR_CODE = p_QUALIFIERS_rec.comparison_operator_code
, CONTEXT = p_QUALIFIERS_rec.context
, CREATED_BY = p_QUALIFIERS_rec.created_by
, CREATED_FROM_RULE_ID = p_QUALIFIERS_rec.created_from_rule_id
, CREATION_DATE = p_QUALIFIERS_rec.creation_date
, END_DATE_ACTIVE = p_QUALIFIERS_rec.end_date_active
, EXCLUDER_FLAG = p_QUALIFIERS_rec.excluder_flag
, LAST_UPDATED_BY = p_QUALIFIERS_rec.last_updated_by
, LAST_UPDATE_DATE = p_QUALIFIERS_rec.last_update_date
, LAST_UPDATE_LOGIN = p_QUALIFIERS_rec.last_update_login
, LIST_HEADER_ID = p_QUALIFIERS_rec.list_header_id
, LIST_LINE_ID = p_QUALIFIERS_rec.list_line_id
, PROGRAM_APPLICATION_ID = p_QUALIFIERS_rec.program_application_id
, PROGRAM_ID = p_QUALIFIERS_rec.program_id
, PROGRAM_UPDATE_DATE = p_QUALIFIERS_rec.program_update_date
, QUALIFIER_ATTRIBUTE = p_QUALIFIERS_rec.qualifier_attribute
, QUALIFIER_ATTR_VALUE = l_qual_attr_value_from
, QUALIFIER_ATTR_VALUE_TO = p_QUALIFIERS_rec.qualifier_attr_value_to
, QUALIFIER_CONTEXT = p_QUALIFIERS_rec.qualifier_context
, QUALIFIER_DATATYPE = p_QUALIFIERS_rec.qualifier_datatype
-- , QUALIFIER_DATE_FORMAT = p_QUALIFIERS_rec.qualifier_date_format
, QUALIFIER_GROUPING_NO = p_QUALIFIERS_rec.qualifier_grouping_no
, QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
--, QUALIFIER_NUMBER_FORMAT = p_QUALIFIERS_rec.qualifier_number_format
, QUALIFIER_PRECEDENCE = p_QUALIFIERS_rec.qualifier_precedence
, QUALIFIER_RULE_ID = p_QUALIFIERS_rec.qualifier_rule_id
, REQUEST_ID = p_QUALIFIERS_rec.request_id
, START_DATE_ACTIVE = p_QUALIFIERS_rec.start_date_active
, QUAL_ATTR_VALUE_FROM_NUMBER = l_qual_attr_value_from_number
, QUAL_ATTR_VALUE_TO_NUMBER = l_qual_attr_value_to_number
, QUALIFY_HIER_DESCENDENTS_FLAG = p_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
WHERE QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
;
UPDATE qp_pte_segments SET used_in_setup='Y'
WHERE nvl(used_in_setup,'N')='N'
AND segment_id IN
(SELECT a.segment_id
FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=p_QUALIFIERS_rec.qualifier_attribute
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type='QUALIFIER'
AND b.prc_context_code=p_QUALIFIERS_rec.qualifier_context);
, 'Update_Row'
);
END Update_Row;
PROCEDURE Update_coupon_Row
( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
)
IS
BEGIN
UPDATE QP_COUPONS
SET expiration_date=p_QUALIFIERS_rec.end_date_active
,start_date=p_QUALIFIERS_rec.start_date_active
,LAST_UPDATED_BY=p_QUALIFIERS_rec.LAST_UPDATED_BY
,LAST_UPDATE_DATE=p_QUALIFIERS_rec.LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN=p_QUALIFIERS_rec.LAST_UPDATE_LOGIN
WHERE coupon_id = p_QUALIFIERS_rec.qualifier_attr_value;
, 'Update_Coupon_Row'
);
END Update_Coupon_Row;
PROCEDURE Insert_Row
( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
)
IS
l_list_type_code VARCHAR2(30) := '';
cursor update_pte_cur(l_qual_context varchar2,l_qual_attribute varchar2) is
SELECT a.segment_id
FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=l_qual_attribute
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type='QUALIFIER'
AND b.prc_context_code=l_qual_context;
SELECT list_type_code
INTO l_list_type_code
FROM qp_list_headers_vl
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
SELECT ACTIVE_FLAG, LIST_TYPE_CODE
INTO l_active_flag, l_list_type_code
FROM QP_LIST_HEADERS_B
WHERE LIST_HEADER_ID = p_QUALIFIERS_rec.list_header_id;
INSERT INTO QP_QUALIFIERS
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, HEADER_QUALS_EXIST_FLAG
, DISTINCT_ROW_COUNT
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
( p_QUALIFIERS_rec.attribute1
, p_QUALIFIERS_rec.attribute10
, p_QUALIFIERS_rec.attribute11
, p_QUALIFIERS_rec.attribute12
, p_QUALIFIERS_rec.attribute13
, p_QUALIFIERS_rec.attribute14
, p_QUALIFIERS_rec.attribute15
, p_QUALIFIERS_rec.attribute2
, p_QUALIFIERS_rec.attribute3
, p_QUALIFIERS_rec.attribute4
, p_QUALIFIERS_rec.attribute5
, p_QUALIFIERS_rec.attribute6
, p_QUALIFIERS_rec.attribute7
, p_QUALIFIERS_rec.attribute8
, p_QUALIFIERS_rec.attribute9
, p_QUALIFIERS_rec.comparison_operator_code
, p_QUALIFIERS_rec.context
, p_QUALIFIERS_rec.created_by
, p_QUALIFIERS_rec.created_from_rule_id
, p_QUALIFIERS_rec.creation_date
, p_QUALIFIERS_rec.end_date_active
, p_QUALIFIERS_rec.excluder_flag
, p_QUALIFIERS_rec.last_updated_by
, p_QUALIFIERS_rec.last_update_date
, p_QUALIFIERS_rec.last_update_login
, p_QUALIFIERS_rec.list_header_id
, p_QUALIFIERS_rec.list_line_id
, p_QUALIFIERS_rec.program_application_id
, p_QUALIFIERS_rec.program_id
, p_QUALIFIERS_rec.program_update_date
, p_QUALIFIERS_rec.qualifier_attribute
, l_qual_attr_value_from
, p_QUALIFIERS_rec.qualifier_attr_value_to
, p_QUALIFIERS_rec.qualifier_context
, p_QUALIFIERS_rec.qualifier_datatype
--, p_QUALIFIERS_rec.qualifier_date_format
, l_qualifier_grouping_no
, p_QUALIFIERS_rec.qualifier_id
--, p_QUALIFIERS_rec.qualifier_number_format
, p_QUALIFIERS_rec.qualifier_precedence
, p_QUALIFIERS_rec.qualifier_rule_id
, p_QUALIFIERS_rec.request_id
, p_QUALIFIERS_rec.start_date_active
, l_active_flag
, l_list_type_code
, l_qual_attr_value_from_number
, l_qual_attr_value_to_number
, p_QUALIFIERS_rec.search_ind
, p_QUALIFIERS_rec.qualifier_group_cnt
, p_QUALIFIERS_rec.header_quals_exist_flag
, p_QUALIFIERS_rec.distinct_row_count
, p_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(p_QUALIFIERS_rec.qualifier_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_QUALIFIERS_rec.list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_QUALIFIERS_rec.list_header_id)
);
/* USed the cursor update_pte_cur instead of a nested subquery for the update statement
* for the bug 3544961*/
for i in update_pte_cur(p_QUALIFIERS_rec.qualifier_context, p_QUALIFIERS_rec.qualifier_attribute) loop
update qp_pte_segments set used_in_setup='Y'
where nvl(used_in_Setup,'N')='N'
and segment_id = i.segment_id;
UPDATE qp_pte_segments SET used_in_setup='Y'
WHERE nvl(used_in_setup,'N')='N'
AND segment_id IN
(SELECT a.segment_id
FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=p_QUALIFIERS_rec.qualifier_attribute
AND a.prc_context_id=b.prc_context_id AND b.prc_context_type='QUALIFIER'
AND b.prc_context_code=p_QUALIFIERS_rec.qualifier_context);
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Insert_Row(p_qual_grp_no IN NUMBER,
p_list_header_id IN NUMBER,
p_list_line_id IN NUMBER,
p_transaction_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
insert into qp_qualifiers_fwk_dummy
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, HEADER_QUALS_EXIST_FLAG
, DISTINCT_ROW_COUNT
, TRANSACTION_ID
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
select
ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, sysdate
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, 'Q'
, DISTINCT_ROW_COUNT
, p_transaction_id
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(qualifier_id)
,nvl(ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id))
,nvl(ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id))
from qp_qualifiers qual
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and ((p_qual_grp_no = -1
and qualifier_grouping_no = p_qual_grp_no)
or (p_qual_grp_no <> -1
and (qualifier_grouping_no = -1
or qualifier_grouping_no = p_qual_grp_no)))
and ((qual.list_type_code = 'PRL'
and not (qual.qualifier_context = 'MODLIST' and qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
or (qual.list_type_code <> 'PRL'))
and not exists (select 'Y' from qp_qualifiers_fwk_dummy dummy
where dummy.qualifier_id = qual.qualifier_id
and dummy.transaction_id = p_transaction_id);
END Insert_Row;
Procedure Delete_Dummy_Rows(p_transaction_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
delete from qp_qualifiers_fwk_dummy where transaction_id = p_transaction_id
or last_update_date < sysdate-5;
END Delete_Dummy_Rows;
Procedure Mark_Delete_Dummy_Qual(p_qual_id IN NUMBER
,p_mode IN VARCHAR2
,p_transaction_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
update qp_qualifiers_fwk_dummy set header_quals_exist_flag = decode(p_mode, 'HGRID', 'X', 'D'),
last_update_date = sysdate
where qualifier_id = p_qual_id
and transaction_id = p_transaction_id;
insert into qp_qualifiers_fwk_dummy
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, HEADER_QUALS_EXIST_FLAG
, DISTINCT_ROW_COUNT
, TRANSACTION_ID
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
select
ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, sysdate
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, decode(p_mode, 'HGRID', 'X', 'D')
, DISTINCT_ROW_COUNT
, p_transaction_id
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(qual.qualifier_id)
,nvl(qual.ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=qual.list_line_id))
,nvl(qual.ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=qual.list_header_id))
from qp_qualifiers qual
where qualifier_id = p_qual_id;
END Mark_Delete_Dummy_Qual;
Procedure Mark_Delete_Dummy_Qual(p_qual_grp_no IN NUMBER,
p_list_header_id IN NUMBER,
p_list_line_id IN NUMBER,
p_transaction_id IN NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
update qp_qualifiers_fwk_dummy set header_quals_exist_flag = 'X'
,last_update_date = sysdate
where qualifier_grouping_no = p_qual_grp_no
and list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and ((list_type_code = 'PRL'
and not (qualifier_context = 'MODLIST' and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
or (list_type_code <> 'PRL'))
and transaction_id = p_transaction_id;
insert into qp_qualifiers_fwk_dummy
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, HEADER_QUALS_EXIST_FLAG
, DISTINCT_ROW_COUNT
, TRANSACTION_ID
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
select
ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, sysdate
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, ACTIVE_FLAG
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, 'X'
, DISTINCT_ROW_COUNT
, p_transaction_id
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(qualifier_id)
,nvl(ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id))
,nvl(ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id))
from qp_qualifiers qual
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and qualifier_grouping_no = p_qual_grp_no
and ((qual.list_type_code = 'PRL'
and not (qual.qualifier_context = 'MODLIST' and qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
or (qual.list_type_code <> 'PRL'));
END Mark_Delete_Dummy_Qual;
delete from qp_qualifiers_fwk_dummy
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and transaction_id = p_transaction_id
and nvl(header_quals_exist_flag, 'N') in ('Q');--, 'D', 'U', 'N');
delete from qp_qualifiers_fwk_dummy
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and transaction_id = p_transaction_id
and nvl(header_quals_exist_flag, 'N') = 'Q';
PROCEDURE Delete_Row
( p_qualifier_id IN NUMBER := FND_API.G_MISS_NUM,
p_qualifier_rule_id IN NUMBER := FND_API.G_MISS_NUM
)
IS
--l_check_active_flag VARCHAR2(1);
DELETE FROM QP_QUALIFIERS
WHERE QUALIFIER_RULE_ID = p_qualifier_rule_id ;
DELETE FROM QP_QUALIFIERS
WHERE QUALIFIER_ID = p_qualifier_id ;
UPDATE qp_pte_segments SET used_in_setup='N'
WHERE
segment_id IN
(SELECT a.segment_id
FROM qp_segments_b a,qp_prc_contexts_b b
WHERE a.segment_mapping_column=l_qualifiers_rec.qualifier_attribute
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type='QUALIFIER'
AND b.prc_context_code=l_qualifiers_rec.qualifier_context);
, 'Delete_Row'
);
END Delete_Row;
PROCEDURE Delete_Row(p_qual_grp_no IN NUMBER,
p_list_header_id IN NUMBER,
p_list_line_id IN NUMBER,
p_transaction_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
delete from qp_qualifiers_fwk_dummy
where transaction_id = p_transaction_id
or last_update_date < sysdate - 5;
End Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
-- , QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
-- , QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, LIST_TYPE_CODE
, QUAL_ATTR_VALUE_FROM_NUMBER
, QUAL_ATTR_VALUE_TO_NUMBER
, ACTIVE_FLAG
, SEARCH_IND
, QUALIFIER_GROUP_CNT
, HEADER_QUALS_EXIST_FLAG
, DISTINCT_ROW_COUNT
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
FROM QP_QUALIFIERS
WHERE ( QUALIFIER_ID = p_qualifier_id
)
OR ( QUALIFIER_RULE_ID = p_qualifier_rule_id
);
l_QUALIFIERS_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_QUALIFIERS_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_QUALIFIERS_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_QUALIFIERS_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, COMPARISON_OPERATOR_CODE
, CONTEXT
, CREATED_BY
, CREATED_FROM_RULE_ID
, CREATION_DATE
, END_DATE_ACTIVE
, EXCLUDER_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LIST_HEADER_ID
, LIST_LINE_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, QUALIFIER_ATTRIBUTE
, QUALIFIER_ATTR_VALUE
, QUALIFIER_ATTR_VALUE_TO
, QUALIFIER_CONTEXT
, QUALIFIER_DATATYPE
--, QUALIFIER_DATE_FORMAT
, QUALIFIER_GROUPING_NO
, QUALIFIER_ID
--, QUALIFIER_NUMBER_FORMAT
, QUALIFIER_PRECEDENCE
, QUALIFIER_RULE_ID
, REQUEST_ID
, START_DATE_ACTIVE
, QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
INTO l_QUALIFIERS_rec.attribute1
, l_QUALIFIERS_rec.attribute10
, l_QUALIFIERS_rec.attribute11
, l_QUALIFIERS_rec.attribute12
, l_QUALIFIERS_rec.attribute13
, l_QUALIFIERS_rec.attribute14
, l_QUALIFIERS_rec.attribute15
, l_QUALIFIERS_rec.attribute2
, l_QUALIFIERS_rec.attribute3
, l_QUALIFIERS_rec.attribute4
, l_QUALIFIERS_rec.attribute5
, l_QUALIFIERS_rec.attribute6
, l_QUALIFIERS_rec.attribute7
, l_QUALIFIERS_rec.attribute8
, l_QUALIFIERS_rec.attribute9
, l_QUALIFIERS_rec.comparison_operator_code
, l_QUALIFIERS_rec.context
, l_QUALIFIERS_rec.created_by
, l_QUALIFIERS_rec.created_from_rule_id
, l_QUALIFIERS_rec.creation_date
, l_QUALIFIERS_rec.end_date_active
, l_QUALIFIERS_rec.excluder_flag
, l_QUALIFIERS_rec.last_updated_by
, l_QUALIFIERS_rec.last_update_date
, l_QUALIFIERS_rec.last_update_login
, l_QUALIFIERS_rec.list_header_id
, l_QUALIFIERS_rec.list_line_id
, l_QUALIFIERS_rec.program_application_id
, l_QUALIFIERS_rec.program_id
, l_QUALIFIERS_rec.program_update_date
, l_QUALIFIERS_rec.qualifier_attribute
, l_QUALIFIERS_rec.qualifier_attr_value
, l_QUALIFIERS_rec.qualifier_attr_value_to
, l_QUALIFIERS_rec.qualifier_context
, l_QUALIFIERS_rec.qualifier_datatype
--, l_QUALIFIERS_rec.qualifier_date_format
, l_QUALIFIERS_rec.qualifier_grouping_no
, l_QUALIFIERS_rec.qualifier_id
--, l_QUALIFIERS_rec.qualifier_number_format
, l_QUALIFIERS_rec.qualifier_precedence
, l_QUALIFIERS_rec.qualifier_rule_id
, l_QUALIFIERS_rec.request_id
, l_QUALIFIERS_rec.start_date_active
, l_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
FROM QP_QUALIFIERS
WHERE QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
FOR UPDATE NOWAIT;
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,
l_QUALIFIERS_rec.last_update_login)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,
l_QUALIFIERS_rec.list_header_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,
l_QUALIFIERS_rec.list_line_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,
l_QUALIFIERS_rec.program_application_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,
l_QUALIFIERS_rec.program_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,
-- l_QUALIFIERS_rec.program_update_date)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,
l_QUALIFIERS_rec.qualifier_attribute)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,
l_QUALIFIERS_rec.qualifier_attr_value)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,
l_QUALIFIERS_rec.qualifier_attr_value_to)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,
l_QUALIFIERS_rec.qualifier_context)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,
l_QUALIFIERS_rec.qualifier_datatype)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,
-- l_QUALIFIERS_rec.qualifier_date_format)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
l_QUALIFIERS_rec.qualifier_grouping_no)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,
l_QUALIFIERS_rec.qualifier_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,
-- l_QUALIFIERS_rec.qualifier_number_format)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,
l_QUALIFIERS_rec.qualifier_precedence)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,
l_QUALIFIERS_rec.qualifier_rule_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,
l_QUALIFIERS_rec.request_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,
l_QUALIFIERS_rec.start_date_active)*/
IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute1,
l_QUALIFIERS_rec.attribute1)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute10,
l_QUALIFIERS_rec.attribute10)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute11,
l_QUALIFIERS_rec.attribute11)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute12,
l_QUALIFIERS_rec.attribute12)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute13,
l_QUALIFIERS_rec.attribute13)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute14,
l_QUALIFIERS_rec.attribute14)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute15,
l_QUALIFIERS_rec.attribute15)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute2,
l_QUALIFIERS_rec.attribute2)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute3,
l_QUALIFIERS_rec.attribute3)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute4,
l_QUALIFIERS_rec.attribute4)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute5,
l_QUALIFIERS_rec.attribute5)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute6,
l_QUALIFIERS_rec.attribute6)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute7,
l_QUALIFIERS_rec.attribute7)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute8,
l_QUALIFIERS_rec.attribute8)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute9,
l_QUALIFIERS_rec.attribute9)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,
l_QUALIFIERS_rec.comparison_operator_code)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.context,
l_QUALIFIERS_rec.context)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_by,
-- l_QUALIFIERS_rec.created_by)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,
l_QUALIFIERS_rec.created_from_rule_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.creation_date,
-- l_QUALIFIERS_rec.creation_date)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.end_date_active,
l_QUALIFIERS_rec.end_date_active)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,
l_QUALIFIERS_rec.excluder_flag)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,
-- l_QUALIFIERS_rec.last_updated_by)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,
-- l_QUALIFIERS_rec.last_update_date)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,
-- l_QUALIFIERS_rec.last_update_login)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,
l_QUALIFIERS_rec.list_header_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,
l_QUALIFIERS_rec.list_line_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,
-- l_QUALIFIERS_rec.program_application_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,
-- l_QUALIFIERS_rec.program_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,
-- l_QUALIFIERS_rec.program_update_date)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,
l_QUALIFIERS_rec.qualifier_attribute)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,
l_QUALIFIERS_rec.qualifier_attr_value)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,
l_QUALIFIERS_rec.qualifier_attr_value_to)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,
l_QUALIFIERS_rec.qualifier_context)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,
l_QUALIFIERS_rec.qualifier_datatype)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,
-- l_QUALIFIERS_rec.qualifier_date_format)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
l_QUALIFIERS_rec.qualifier_grouping_no)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,
l_QUALIFIERS_rec.qualifier_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,
-- l_QUALIFIERS_rec.qualifier_number_format)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,
l_QUALIFIERS_rec.qualifier_precedence)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,
l_QUALIFIERS_rec.qualifier_rule_id)
--AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,
-- l_QUALIFIERS_rec.request_id)
AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,
l_QUALIFIERS_rec.start_date_active)
-- Added for TCA
AND (QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualify_hier_descendent_flag,
l_QUALIFIERS_rec.qualify_hier_descendent_flag)
OR (p_QUALIFIERS_rec.qualify_hier_descendent_flag = FND_API.G_MISS_CHAR
and l_QUALIFIERS_rec.qualify_hier_descendent_flag is null))
THEN
-- Row has not changed. Set out parameter.
oe_debug_pub.add('row not changed');
oe_debug_pub.ADD('last_updated_by :'||l_QUALIFIERS_rec.last_updated_by||':'||p_QUALIFIERS_rec.last_updated_by||':');
oe_debug_pub.ADD('last_update_date :'||l_QUALIFIERS_rec.last_update_date||':'||p_QUALIFIERS_rec.last_update_date||':');
oe_debug_pub.ADD('last_update_login :'||l_QUALIFIERS_rec.last_update_login||':'||p_QUALIFIERS_rec.last_update_login||':');
oe_debug_pub.ADD('program_update_date :'||l_QUALIFIERS_rec.program_update_date||':'||p_QUALIFIERS_rec.program_update_date||':');
oe_debug_pub.add('row deleted');
FND_MESSAGE.SET_NAME('QP','QP_LOCK_ROW_DELETED');
QP_GLOBALS.G_OPR_DELETE) ) THEN
-- Get the List Type Code
BEGIN
SELECT list_type_code
INTO l_list_type_code
FROM qp_list_headers_vl
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
/*SELECT 1
INTO l_hlq_count
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM qp_qualifiers
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
AND list_line_id = -1
AND NOT (qualifier_context = 'MODLIST' AND
qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));*/
SELECT count(1) into l_hlq_count
FROM qp_qualifiers
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
AND list_line_id = -1
AND NOT (qualifier_context = 'MODLIST' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE4');
/*SELECT 1
INTO l_hlq_count
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM qp_qualifiers
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
AND list_line_id = -1); */
SELECT 1 into l_hlq_count
FROM qp_qualifiers
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
AND list_line_id = -1
and rownum = 1;
/* SELECT 1 INTO l_llq_count
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM qp_qualifiers
WHERE list_line_id = p_QUALIFIERS_rec.list_line_id);*/
SELECT 1 into l_llq_count
FROM qp_qualifiers
WHERE list_line_id = p_QUALIFIERS_rec.list_line_id
and rownum=1;
(p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id = -1 AND
l_hlq_count = 1)
THEN
/* Bug 4191955 No need to log delayed request to update header qualification indicator
for secondary price list */
IF NOT (l_list_type_code = 'PRL' and p_QUALIFIERS_rec.qualifier_context='MODLIST'
and p_QUALIFIERS_rec.qualifier_attribute='QUALIFIER_ATTRIBUTE4') THEN
qp_delayed_requests_PVT.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type =>QP_GLOBALS.G_UPDATE_LIST_QUAL_IND,
x_return_status => l_return_status);
(p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id <> -1 AND
l_llq_count = 1)
THEN
qp_delayed_requests_PVT.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_line_id,
p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_line_id,
p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
x_return_status => l_return_status);
END IF; -- If operation is create or delete.
if p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE then
BEGIN
SELECT list_type_code
INTO l_list_type_code
FROM qp_list_headers_vl
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
QP_GLOBALS.G_OPR_UPDATE,
QP_GLOBALS.G_OPR_DELETE) THEN
IF (l_list_type_code in ('PRL','AGR')) OR ((l_list_type_code NOT IN ('PRL','AGR')) AND (l_denormalize_qual= 'Y')) then --7120399
qp_delayed_requests_PVT.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_QUALIFIER_DEN_COLS,
x_return_status => l_return_status);
(QP_GLOBALS.G_OPR_CREATE, QP_GLOBALS.G_OPR_UPDATE)) THEN
oe_debug_pub.add('log delayed request--------warn_same_qual_grp');
OR (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE) THEN
-- Get the List Type Code
BEGIN
SELECT list_type_code
INTO l_list_type_code
FROM qp_list_headers_vl
WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
and p_qualifiers_rec.operation = QP_GLOBALS.G_OPR_UPDATE then
declare
cursor c_qual_grp is
select distinct qualifier_grouping_no qualifier_grouping_no
from qp_qualifiers
where list_header_id = p_qualifiers_rec.list_header_id
and list_line_id = p_qualifiers_rec.list_line_id
and qualifier_grouping_no <> -1;
oe_debug_pub.add('In LOOP - update of qualifier grp to -1');
end if; -- grp_no is -1 and operation is update
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
p_QUALIFIERS_rec.list_line_id = -1 THEN
-- header qualifier is modified
IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
-- updated other than qualifier grouping number
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => NULL,
p_request_unique_key2 => 'U',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id = -1 THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 then
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => NULL,
p_request_unique_key2 => 'D',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
p_QUALIFIERS_rec.list_line_id <> -1 THEN
IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
p_request_unique_key2 => NULL,
p_request_unique_key3 => 'U',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id <> -1 THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
p_request_unique_key2 => NULL,
p_request_unique_key3 => 'D',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
p_QUALIFIERS_rec.list_line_id = -1 THEN
-- header qualifier is modified
IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
-- updated other than qualifier grouping number
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => NULL,
p_request_unique_key2 => 'U',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id = -1 THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 then
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => NULL,
p_request_unique_key2 => 'D',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
p_QUALIFIERS_rec.list_line_id <> -1 THEN
IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
p_request_unique_key2 => NULL,
p_request_unique_key3 => 'U',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
x_return_status => l_return_status);
IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
p_QUALIFIERS_rec.list_line_id <> -1 THEN
IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
qp_delayed_requests_pvt.log_request(
p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
p_request_unique_key2 => NULL,
p_request_unique_key3 => 'D',
p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
x_return_status => l_return_status);