[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE LH_LAST_UPDATE_DATE_TAB IS TABLE OF QP_INTERFACE_LIST_HEADERS.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
LH_REPLACE_RC.DELETE;
LH_NEW_RC.DELETE;
SELECT facility_code FROM fte_location_parameters
WHERE modifier_list = p_pricelist_id;
SELECT l.list_header_id, nvl(b.attribute1, 'FTE_RATE_CHART')
INTO l_list_header_id, l_attribute1
FROM qp_list_headers_tl l, qp_list_headers_b b
WHERE l.list_header_id = b.list_header_id
AND l.name = p_name
AND l.language = userenv('LANG');
SELECT l.list_header_id, nvl(b.attribute1, 'FTE_RATE_CHART')
INTO l_list_header_id, l_attribute1
FROM qp_list_headers_tl l, qp_list_headers_b b, qp_qualifiers q
WHERE l.list_header_id = b.list_header_id
AND l.name = p_name
AND l.list_header_id = q.list_header_id
AND q.qualifier_context = 'PARTY'
AND q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1'
AND q.qualifier_attr_value = Fnd_Number.Number_To_Canonical(p_Carrier_Id)
AND l.language = userenv('LANG');
SELECT l.list_header_id, ltl.name, to_char(l.start_date_active,'YYYY-MM-DD'), to_char(l.end_date_active,'YYYY-MM-DD')
FROM qp_list_headers_b l, qp_qualifiers q, qp_list_headers_tl ltl
WHERE ltl.name = p_name
AND l.list_header_id=q.list_header_id
AND ltl.list_header_id = l.list_header_id
AND q.qualifier_context = 'PARTY'
AND q.qualifier_attr_value = TO_CHAR(p_carrier_id)
AND ltl.language = userenv('LANG');
PROCEDURE DELETE_FROM_QP(p_list_header_id IN NUMBER,
p_name IN VARCHAR2,
p_action IN VARCHAR2,
p_line_number IN NUMBER,
p_delete_qualifier IN BOOLEAN DEFAULT TRUE,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_list_header_id NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.DELETE_FROM_QP';
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_DELETE_TYPE_WRONG',
p_tokens => STRINGARRAY('NAME', 'TYPE', 'ACTUAL'),
p_values => l_tokens);
ELSIF (p_action = 'DELETE') THEN
IF (g_chart_type = 'FAC_MODIFIER') THEN
x_status := Check_Facilities(l_list_header_id, x_status, x_error_msg);
DELETE FROM qp_pricing_attributes
WHERE list_header_id = l_list_header_id;
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' attributes.');
delete from qp_rltd_modifiers
where from_rltd_modifier_id in (select list_line_id
from qp_list_lines
where list_header_id = l_list_header_id);
delete from qp_list_lines
where list_header_id = l_list_header_id;
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' List Lines.');
IF (p_action = 'UPDATE' AND g_chart_type IN ('FTE_MODIFIER') AND p_delete_qualifier) THEN
delete from qp_qualifiers
where list_header_id = l_list_header_id
and qualifier_context <> 'PARTY';
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' qualifiers.');
IF (p_action = 'DELETE') THEN
delete from qp_qualifiers
where list_header_id = l_list_header_id;
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' qualifiers.');
delete from qp_list_headers_b
where list_header_id =l_list_header_id;
delete from qp_list_headers_tl
where list_header_id = l_list_header_id;
DELETE FROM fte_prc_parameters
WHERE list_header_id = l_list_header_id
AND parameter_id in (57, 58, 59, 60);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Deleted ' || sql%rowcount || ' prc_parameters.');
END DELETE_FROM_QP;
CURSOR updated_lanes IS
select lane_id
from fte_lane_rate_charts
where list_header_id = p_old_id;
SELECT hb.list_header_id, hb.start_date_active, hb.end_date_active
INTO l_new_id, l_new_start_date, l_new_end_date
FROM qp_list_headers_b hb, qp_list_headers_tl tl
WHERE hb.list_header_id = tl.list_header_id
AND tl.name = p_new_name
AND tl.language = userenv('LANG');
OPEN updated_lanes;
FETCH updated_lanes INTO l_lane_id;
EXIT WHEN updated_lanes%NOTFOUND;
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Update Lane ' || l_lane_id || ' with new rate chart id ' || l_new_id);
UPDATE fte_lane_rate_charts
SET end_date_active = LEAST(end_date_active, l_new_start_date-0.00001),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE list_header_id = p_old_id
AND lane_id = l_lane_id;
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Update expiry date of old rate chart');
UPDATE qp_list_headers_b
SET end_date_active = LEAST(end_date_active, l_new_start_date-0.00001),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE list_header_id = p_old_id;
FTE_LANE_PKG.INSERT_LANE_TABLES(p_lane_tbl => l_lane_tbl,
p_lane_rate_chart_tbl => l_lane_rate_chart_tbl,
p_lane_commodity_tbl => l_lane_commodity_tbl,
p_lane_service_tbl => l_lane_service_tbl,
x_status => x_status,
x_error_msg => x_error_msg);
CLOSE updated_lanes;
IF (updated_lanes%ISOPEN) THEN
CLOSE updated_lanes;
PROCEDURE INSERT_QP_INTERFACE_TABLES(p_qp_list_header_tbl IN OUT NOCOPY qp_list_header_tbl,
p_qp_list_line_tbl IN OUT NOCOPY qp_list_line_tbl,
p_qp_qualifier_tbl IN OUT NOCOPY qp_qualifier_tbl,
p_qp_pricing_attrib_tbl IN OUT NOCOPY qp_pricing_attrib_tbl,
p_qp_call IN BOOLEAN DEFAULT TRUE,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_status VARCHAR2(10);
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_QP_INTERFACE_TABLES';
LH_LAST_UPDATE_DATE LH_LAST_UPDATE_DATE_TAB;
INSERT INTO QP_INTERFACE_LIST_HEADERS ( PROCESS_ID,
INTERFACE_ACTION_CODE,
LIST_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
CURRENCY_CODE,
NAME,
DESCRIPTION,
LIST_HEADER_ID,
ATTRIBUTE1,
PROCESS_TYPE,
AUTOMATIC_FLAG,
SOURCE_SYSTEM_CODE,
ACTIVE_FLAG,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
LH_PROCESS_ID(cnt),
LH_INT_ACTION_CODE(cnt),
LH_LIST_TYPE_CODE(cnt),
LH_START_DATE_ACTIVE(cnt),
LH_END_DATE_ACTIVE(cnt),
LH_CURRENCY_CODE(cnt),
LH_NAME(cnt),
LH_DESCRIPTION(cnt),
LH_LIST_HEADER_ID(cnt),
LH_ATTRIBUTE1(cnt),
'SSH',
'Y',
'FTE',
'Y',
'US',
'US',
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting rate chart header]');
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
END; --FINISH INSERTING LIST HEADERS
INSERT INTO QP_INTERFACE_QUALIFIERS( PROCESS_ID,
INTERFACE_ACTION_CODE,
QUALIFIER_ATTR_VALUE,
QUALIFIER_GROUPING_NO,
PROCESS_TYPE,
EXCLUDER_FLAG,
COMPARISON_OPERATOR_CODE,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
QL_PROCESS_ID(cnt),
QL_INT_ACTION_CODE(cnt),
QL_QUALIFIER_ATTR_VALUE(cnt),
QL_QUALIFIER_GROUPING_NO(cnt),
'SSH',
'N',
'=',
QL_QUALIFIER_CONTEXT(cnt),
QL_QUALIFIER_ATTR(cnt),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting qualifier]');
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
END; --FINISH INSERTING QUALIFIERS
INSERT INTO QP_INTERFACE_LIST_LINES( PROCESS_ID,
OPERAND,
COMMENTS,
LIST_LINE_NO,
PRIMARY_UOM_FLAG,
PROCESS_TYPE,
INTERFACE_ACTION_CODE,
LIST_LINE_TYPE_CODE,
AUTOMATIC_FLAG,
OVERRIDE_FLAG,
MODIFIER_LEVEL_CODE,
ARITHMETIC_OPERATOR,
ACCRUAL_FLAG,
PRICE_BREAK_TYPE_CODE,
PRODUCT_PRECEDENCE,
PRICE_BREAK_HEADER_INDEX,
RLTD_MODIFIER_GRP_NO,
PRICE_BY_FORMULA_ID,
ATTRIBUTE1,
ATTRIBUTE2,
RLTD_MODIFIER_GRP_TYPE,
PRICING_GROUP_SEQUENCE,
PRICING_PHASE_ID,
QUALIFICATION_IND,
CHARGE_TYPE_CODE,
CHARGE_SUBTYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
LL_PROCESS_ID(cnt),
LL_OPERAND(cnt),
LL_COMMENTS(cnt),
LL_LIST_LINE_NO(cnt),
LL_PRIMARY_UOM_FLAG(cnt),
LL_PROCESS_TYPE(cnt),
LL_INT_ACTION_CODE(cnt),
LL_LIST_LINE_TYPE_CODE(cnt),
LL_AUTOMATIC_FLAG(cnt),
LL_OVERRIDE_FLAG(cnt),
LL_MOD_LEVEL_CODE(cnt),
LL_ARITHMETIC_OPERATOR(cnt),
LL_ACCRUAL_FLAG(cnt),
LL_PRC_BRK_TYPE_CODE(cnt),
LL_PRODUCT_PRECEDENCE(cnt),
LL_PRC_BRK_HDR_IDX(cnt),
LL_RLTD_MOD_GRP_NO(cnt),
LL_FORMULA_ID(cnt),
LL_ATTRIBUTE1(cnt),
LL_ATTRIBUTE2(cnt),
LL_RLTD_MOD_GRP_TYPE(cnt),
LL_PRICING_GRP_SEQUENCE(cnt),
LL_PRICING_PHASE_ID(cnt),
LL_QUALIFICATION_IND(cnt),
LL_CHARGE_TYPE_CODE(cnt),
LL_CHARGE_SUBTYPE_CODE(cnt),
LL_START_DATE_ACTIVE(cnt),
LL_END_DATE_ACTIVE(cnt),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting rate chart lines]');
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
END; --FINISH INSERTING LIST_LINES
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS(PROCESS_ID,
PROCESS_TYPE,
INTERFACE_ACTION_CODE,
EXCLUDER_FLAG,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTRIBUTE,
PRODUCT_ATTR_VALUE,
PRODUCT_UOM_CODE,
PRODUCT_ATTRIBUTE_DATATYPE,
PRICING_ATTRIBUTE_DATATYPE,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTRIBUTE,
PRICING_ATTR_VALUE_FROM,
PRICING_ATTR_VALUE_TO,
ATTRIBUTE_GROUPING_NO,
COMPARISON_OPERATOR_CODE,
LIST_LINE_NO,
CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(
AT_PROCESS_ID(cnt),
AT_PROCESS_TYPE(cnt),
AT_INT_ACTION_CODE(cnt),
AT_EXCLUDER_FLAG(cnt),
AT_PRODUCT_ATTR_CONTEXT(cnt),
AT_PRODUCT_ATTRIBUTE(cnt),
AT_PRODUCT_ATTR_VALUE(cnt),
AT_PRODUCT_UOM_CODE(cnt),
AT_PRODUCT_ATTR_DATATYPE(cnt),
AT_PRICING_ATTR_DATATYPE(cnt),
AT_PRICING_ATTR_CONTEXT(cnt),
AT_PRICING_ATTRIBUTE(cnt),
AT_PRICING_ATTR_VALUE_FROM(cnt),
AT_PRICING_ATTR_VALUE_TO(cnt),
AT_ATTR_GROUPING_NO(cnt),
AT_COMP_OPERATOR_CODE(cnt),
AT_LIST_LINE_NO(cnt),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting attributes]');
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
END; --FINISH INSERTING PRICING_ATTRIBS
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
p_qp_list_header_tbl.DELETE;
p_qp_list_line_tbl.DELETE;
p_qp_qualifier_tbl.DELETE;
p_qp_pricing_attrib_tbl.DELETE;
END INSERT_QP_INTERFACE_TABLES;
SELECT modc.list_header_id
FROM qp_list_headers_tl rc, qp_list_headers_b b,
qp_list_headers_tl modc, qp_list_headers_b b2,
qp_qualifiers mod_qual
WHERE rc.list_header_id = b.list_header_id
AND modc.list_header_id = b2.list_header_id
AND mod_qual.qualifier_context = 'MODLIST'
AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
AND mod_qual.list_header_id = modc.list_header_id
AND rc.name = p_pricelist_name
AND rc.language = userenv('LANG')
AND modc.language = userenv('LANG')
ORDER BY rc.creation_date DESC;
SELECT modc.list_header_id
FROM qp_list_headers_tl rc, qp_list_headers_b b,
qp_list_headers_tl modc, qp_list_headers_b b2,
qp_qualifiers mod_qual
WHERE rc.list_header_id = b.list_header_id
AND modc.list_header_id = b2.list_header_id
AND mod_qual.qualifier_context = 'MODLIST'
AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
AND mod_qual.list_header_id = modc.list_header_id
AND rc.list_header_id = p_list_header_id
AND rc.language = userenv('LANG')
AND modc.language = userenv('LANG')
ORDER BY rc.creation_date DESC;
SELECT rc.list_header_id
FROM qp_list_headers_tl rc, qp_list_headers_b b,
qp_list_headers_tl modc, qp_list_headers_b b2,
qp_qualifiers mod_qual
WHERE rc.list_header_id = b.list_header_id
AND modc.list_header_id = b2.list_header_id
AND mod_qual.qualifier_context = 'MODLIST'
AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
AND mod_qual.list_header_id = modc.list_header_id
AND modc.name = p_modifier_name
AND rc.language = userenv('LANG')
AND modc.language = userenv('LANG')
ORDER BY rc.creation_date DESC;
SELECT rc.list_header_id
FROM qp_list_headers_tl rc, qp_list_headers_b b,
qp_list_headers_tl modc, qp_list_headers_b b2,
qp_qualifiers mod_qual
WHERE rc.list_header_id = b.list_header_id
AND modc.list_header_id = b2.list_header_id
AND mod_qual.qualifier_context = 'MODLIST'
AND mod_qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND to_number(mod_qual.qualifier_attr_value) = rc.list_header_id
AND mod_qual.list_header_id = modc.list_header_id
AND modc.name = p_modifier_name
AND rc.language = userenv('LANG')
AND modc.language = userenv('LANG')
ORDER BY rc.creation_date DESC;
SELECT currency_header_id
FROM qp_list_headers
WHERE list_header_id = p_list_header_id;
SELECT 'TRUE'
FROM qp_currency_details
WHERE currency_header_id = p_currency_header_id
AND to_currency_code = p_currency;
SELECT to_char(b.list_header_id)
FROM qp_list_headers_tl lh,
qp_list_headers_b b,
qp_qualifiers qc,
qp_qualifiers qs,
qp_qualifiers qm,
qp_list_headers_tl modlh,
qp_qualifiers modqs,
qp_qualifiers modqc
WHERE modlh.name = p_name AND
modlh.list_header_id = modqs.list_header_id AND
modqc.list_header_id = modlh.list_header_id AND
modqs.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
modqs.qualifier_context = 'LOGISTICS' AND
modqc.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
modqc.qualifier_context = 'PARTY' AND
lh.list_header_id = b.list_header_id AND
qc.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
qc.qualifier_context = 'PARTY' AND
qc.qualifier_attr_value = modqc.qualifier_attr_value AND
qc.list_header_id = lh.list_header_id AND
qs.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
qs.qualifier_context = 'LOGISTICS' AND
qs.qualifier_attr_value = modqs.qualifier_attr_value AND
qs.list_header_id = qc.list_header_id AND
qm.qualifier_attribute = 'QUALIFIER_ATTRIBUTE7' AND
qm.qualifier_context = 'LOGISTICS' AND
qm.qualifier_attr_value = 'TRUCK' AND
qm.list_header_id = qc.list_header_id AND
b.attribute1 = 'TL_RATE_CHART' AND
lh.language = userenv('LANG');
l_CURR_LISTS_rec.operation := QP_GLOBALS.G_OPR_UPDATE;