[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT contract_number
FROM okc_k_headers_b
WHERE id = c_k_id;
SELECT TO_CHAR(order_number)
FROM okx_order_headers_v
WHERE id1 = c_o_id;
SELECT TO_CHAR(quote_number)
FROM okx_quote_headers_v
WHERE id1 = c_q_id;
g_price_adjustments_tab.DELETE;
g_pavv_tab.DELETE;
lx_pavv_tab.DELETE;
g_patv_tab.DELETE;
lx_patv_tab.DELETE;
g_paav_tab.DELETE;
lx_paav_tab.DELETE;
g_pacv_tab.DELETE;
lx_pacv_tab.DELETE;
SELECT --parent_adjustment_id, --not used
pa.price_adjustment_id price_adjustment_id,
pa.quote_header_id source_header_id,
pa.quote_line_id source_line_id,
pa.modified_from modified_from,
pa.modified_to modified_to,
NVL(pa.modifier_mechanism_type_code, qh.list_type_code) modifier_mechanism_type_code, --not used
pa.operand operand,
pa.arithmetic_operator arithmetic_operator,
pa.automatic_flag automatic_flag,
pa.update_allowable_flag update_allowable_flag,
pa.updated_flag updated_flag,
pa.applied_flag applied_flag,
pa.on_invoice_flag on_invoice_flag,
pa.pricing_phase_id pricing_phase_id,
pa.attribute_category attribute_category,
---list_header_id obsolete columns in ASO
---list_line_id
---list_line_type_code
pa.modifier_header_id list_header_id,
pa.modifier_line_id list_line_id,
pa.modifier_line_type_code list_line_type_code,
pa.change_reason_code change_reason_code,
pa.change_reason_text change_reason_text,
pa.estimated_flag estimated_flag,
pa.adjusted_amount adjusted_amount,
pa.charge_type_code charge_type_code,
pa.charge_subtype_code charge_subtype_code,
pa.range_break_quantity range_break_quantity,
pa.accrual_conversion_rate accrual_conversion_rate,
pa.pricing_group_sequence pricing_group_sequence,
pa.accrual_flag accrual_flag,
NVL(pa.list_line_no, ql.list_line_no) list_line_no,
pa.source_system_code source_system_code,
pa.benefit_qty benefit_qty,
pa.benefit_uom_code benefit_uom_code,
pa.expiration_date expiration_date,
pa.modifier_level_code modifier_level_code,
pa.price_break_type_code price_break_type_code,
pa.substitution_attribute substitution_attribute,
pa.proration_type_code proration_type_code,
pa.include_on_returns_flag include_on_returns_flag,
pa.object_version_number object_version_number,
pa.attribute1 attribute1,
pa.attribute2 attribute2,
pa.attribute3 attribute3,
pa.attribute4 attribute4,
pa.attribute5 attribute5,
pa.attribute6 attribute6,
pa.attribute7 attribute7,
pa.attribute8 attribute8,
pa.attribute9 attribute9,
pa.attribute10 attribute10,
pa.attribute11 attribute11,
pa.attribute12 attribute12,
pa.attribute13 attribute13,
pa.attribute14 attribute14,
pa.attribute15 attribute15,
pa.rebate_transaction_type_code rebate_transaction_type_code
FROM okx_qte_prc_adjmnts_v pa,
qp_list_lines ql,
qp_list_headers_b qh
WHERE cp_q_flag = OKC_API.G_TRUE
AND pa.quote_header_id = cp_qhr_id
AND pa.modifier_line_type_code <> 'FREIGHT_CHARGE' -- Bug 2054770
AND ((cp_qle_id = OKC_API.G_MISS_NUM AND pa.quote_line_id IS NULL) OR
(cp_qle_id <> OKC_API.G_MISS_NUM AND pa.quote_line_id = cp_qle_id))
AND pa.modifier_header_id = qh.list_header_id
AND pa.modifier_line_id = ql.list_line_id
AND ( pa.applied_flag IS NULL OR pa.applied_flag = 'Y' ) -- Bug 2801279
UNION ALL -- second query to get okx_ord_prc_adjmnts_v information
SELECT ------price_adjustment_id, --not used
pa.price_adjustment_id,
pa.header_id source_header_id,
pa.line_id source_line_id,
TO_NUMBER(pa.modified_from) modified_from,
TO_NUMBER(pa.modified_to) modified_to,
qh.list_type_code modifier_mechanism_type_code,
pa.operand,
pa.arithmetic_operator,
pa.automatic_flag,
pa.update_allowed,
pa.updated_flag,
pa.applied_flag,
pa.invoiced_flag,
pa.pricing_phase_id,
pa.context,
pa.list_header_id,
pa.list_line_id,
pa.list_line_type_code,
pa.change_reason_code,
pa.change_reason_text,
pa.estimated_flag,
pa.adjusted_amount,
pa.charge_type_code,
pa.charge_subtype_code,
pa.range_break_quantity,
pa.accrual_conversion_rate,
pa.pricing_group_sequence,
pa.accrual_flag,
NVL(pa.list_line_no, ql.list_line_no) list_line_no,
pa.source_system_code,
pa.benefit_qty,
pa.benefit_uom_code,
pa.expiration_date,
pa.modifier_level_code,
pa.price_break_type_code,
pa.substitution_attribute,
pa.proration_type_code,
pa.include_on_returns_flag,
TO_NUMBER(NULL), --object_version_number not present in order table
pa.attribute1,
pa.attribute2,
pa.attribute3,
pa.attribute4,
pa.attribute5,
pa.attribute6,
pa.attribute7,
pa.attribute8,
pa.attribute9,
pa.attribute10,
pa.attribute11,
pa.attribute12,
pa.attribute13,
pa.attribute14,
pa.attribute15,
pa.rebate_transaction_type_code
FROM okx_ord_prc_adjmnts_v pa,
qp_list_lines ql,
qp_list_headers_b qh
WHERE cp_o_flag = OKC_API.G_TRUE
AND pa.header_id = cp_ohr_id
AND pa.list_line_type_code <> 'FREIGHT_CHARGE' -- Bug 2054770
AND ((cp_ole_id = OKC_API.G_MISS_NUM AND pa.line_id IS NULL) OR
(cp_ole_id <> OKC_API.G_MISS_NUM AND pa.line_id = cp_ole_id))
AND pa.list_header_id = qh.list_header_id
AND pa.list_line_id = ql.list_line_id ;
g_patv_tab(i).UPDATE_ALLOWED := l_source_patv_rec.update_allowable_flag;
g_patv_tab(i).UPDATED_FLAG := l_source_patv_rec.updated_flag;
SELECT ----price_adjustment_id, --not needed
price_adjustment_id,
rltd_price_adj_id,
quote_line_id source_line_id,
object_version_number
FROM okx_qte_prc_adj_rlshp_v
WHERE cp_q_flag = OKC_API.G_TRUE
AND price_adjustment_id = cp_old_pat_id
UNION ALL
-- second query to get okx_ord_prc_adj_rlshp_v information
SELECT --------price_adjustment_id, --not needed
price_adjustment_id,
rltd_price_adj_id,
line_id source_line_id,
TO_NUMBER(NULL) ----object_version_number --not in order table
FROM okx_ord_prc_adj_rlshp_v
WHERE cp_o_flag = OKC_API.G_TRUE
AND price_adjustment_id = cp_old_pat_id;
SELECT ----price_adjustment_id, --not needed
flex_title,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
object_version_number
FROM okx_qte_prc_adj_atrbs_v
WHERE cp_q_flag = OKC_API.G_TRUE
AND price_adjustment_id = cp_old_pat_id
UNION ALL
-- second query to get okx_ord_prc_adj_atrbs_v information
SELECT --------price_adjustment_id, --not needed
flex_title,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator,
TO_NUMBER(NULL) ---object_version_number not in order table
FROM okx_ord_prc_adj_atrbs_v
WHERE cp_o_flag = OKC_API.G_TRUE
AND price_adjustment_id = cp_old_pat_id;
SELECT quote_header_id source_header_id,
quote_line_id source_line_id,
flex_title,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
pricing_attribute16,
pricing_attribute17,
pricing_attribute18,
pricing_attribute19,
pricing_attribute20,
pricing_attribute21,
pricing_attribute22,
pricing_attribute23,
pricing_attribute24,
pricing_attribute25,
pricing_attribute26,
pricing_attribute27,
pricing_attribute28,
pricing_attribute29,
pricing_attribute30,
pricing_attribute31,
pricing_attribute32,
pricing_attribute33,
pricing_attribute34,
pricing_attribute35,
pricing_attribute36,
pricing_attribute37,
pricing_attribute38,
pricing_attribute39,
pricing_attribute40,
pricing_attribute41,
pricing_attribute42,
pricing_attribute43,
pricing_attribute44,
pricing_attribute45,
pricing_attribute46,
pricing_attribute47,
pricing_attribute48,
pricing_attribute49,
pricing_attribute50,
pricing_attribute51,
pricing_attribute52,
pricing_attribute53,
pricing_attribute54,
pricing_attribute55,
pricing_attribute56,
pricing_attribute57,
pricing_attribute58,
pricing_attribute59,
pricing_attribute60,
pricing_attribute61,
pricing_attribute62,
pricing_attribute63,
pricing_attribute64,
pricing_attribute65,
pricing_attribute66,
pricing_attribute67,
pricing_attribute68,
pricing_attribute69,
pricing_attribute70,
pricing_attribute71,
pricing_attribute72,
pricing_attribute73,
pricing_attribute74,
pricing_attribute75,
pricing_attribute76,
pricing_attribute77,
pricing_attribute78,
pricing_attribute79,
pricing_attribute80,
pricing_attribute81,
pricing_attribute82,
pricing_attribute83,
pricing_attribute84,
pricing_attribute85,
pricing_attribute86,
pricing_attribute87,
pricing_attribute88,
pricing_attribute89,
pricing_attribute90,
pricing_attribute91,
pricing_attribute92,
pricing_attribute93,
pricing_attribute94,
pricing_attribute95,
pricing_attribute96,
pricing_attribute97,
pricing_attribute98,
pricing_attribute99,
pricing_attribute100,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number
FROM okx_qte_prc_atrbs_v
WHERE cp_q_flag = OKC_API.G_TRUE
AND quote_header_id = cp_qhr_id
AND ((cp_qle_id = OKC_API.G_MISS_NUM AND quote_line_id IS NULL) OR
(cp_qle_id <> OKC_API.G_MISS_NUM AND quote_line_id = cp_qle_id))
UNION ALL
-- second query to get okx_ord_prc_atrbs_v information
SELECT header_id source_header_id,
line_id source_line_id,
flex_title,
pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
pricing_attribute16,
pricing_attribute17,
pricing_attribute18,
pricing_attribute19,
pricing_attribute20,
pricing_attribute21,
pricing_attribute22,
pricing_attribute23,
pricing_attribute24,
pricing_attribute25,
pricing_attribute26,
pricing_attribute27,
pricing_attribute28,
pricing_attribute29,
pricing_attribute30,
pricing_attribute31,
pricing_attribute32,
pricing_attribute33,
pricing_attribute34,
pricing_attribute35,
pricing_attribute36,
pricing_attribute37,
pricing_attribute38,
pricing_attribute39,
pricing_attribute40,
pricing_attribute41,
pricing_attribute42,
pricing_attribute43,
pricing_attribute44,
pricing_attribute45,
pricing_attribute46,
pricing_attribute47,
pricing_attribute48,
pricing_attribute49,
pricing_attribute50,
pricing_attribute51,
pricing_attribute52,
pricing_attribute53,
pricing_attribute54,
pricing_attribute55,
pricing_attribute56,
pricing_attribute57,
pricing_attribute58,
pricing_attribute59,
pricing_attribute60,
pricing_attribute61,
pricing_attribute62,
pricing_attribute63,
pricing_attribute64,
pricing_attribute65,
pricing_attribute66,
pricing_attribute67,
pricing_attribute68,
pricing_attribute69,
pricing_attribute70,
pricing_attribute71,
pricing_attribute72,
pricing_attribute73,
pricing_attribute74,
pricing_attribute75,
pricing_attribute76,
pricing_attribute77,
pricing_attribute78,
pricing_attribute79,
pricing_attribute80,
pricing_attribute81,
pricing_attribute82,
pricing_attribute83,
pricing_attribute84,
pricing_attribute85,
pricing_attribute86,
pricing_attribute87,
pricing_attribute88,
pricing_attribute89,
pricing_attribute90,
pricing_attribute91,
pricing_attribute92,
pricing_attribute93,
pricing_attribute94,
pricing_attribute95,
pricing_attribute96,
pricing_attribute97,
pricing_attribute98,
pricing_attribute99,
pricing_attribute100,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
TO_NUMBER(NULL) --object_version_number not present in order table
FROM okx_ord_prc_atrbs_v
WHERE cp_o_flag = OKC_API.G_TRUE
AND header_id = cp_ohr_id
AND ((cp_ole_id = OKC_API.G_MISS_NUM AND line_id IS NULL) OR
(cp_ole_id <> OKC_API.G_MISS_NUM AND line_id = cp_ole_id));
SELECT
qpadj.PRICE_ADJUSTMENT_ID -- quote(header or line) price adj ID
FROM
OKX_QTE_PRC_ADJMNTS_V qpadj
WHERE
b_q_flag = OKC_API.G_TRUE
AND qpadj.quote_header_id = b_qh_id
AND ((b_ql_id IS NULL AND qpadj.quote_line_id IS NULL) OR
(b_ql_id IS NOT NULL AND qpadj.quote_line_id=b_ql_id))
UNION
SELECT
PRICE_ADJUSTMENT_ID -- quote(header or line) price adj ID
FROM
OKX_ORD_PRC_ADJMNTS_V opadj
WHERE
b_o_flag = OKC_API.G_TRUE
AND opadj.header_id = b_oh_id
AND ((b_ol_id IS NULL AND opadj.line_id IS NULL) OR
(b_ol_id IS NOT NULL AND opadj.line_id=b_ol_id));
SELECT
DECODE(qpadj.modifier_header_id,NULL,g_aso_op_code_create,
DECODE(qpadj.modifier_line_id,NULL,g_aso_op_code_create,
DECODE(qpadj.modifier_line_type_code,NULL,
g_aso_op_code_create,g_aso_op_code_update)
)
) OPERATION_CODE,
qpadj.PRICE_ADJUSTMENT_ID, -- quote(header or line) price adj ID
kpadj.id, -- contract(header or line) price adj ID
kpadj.pat_id,
kpadj.chr_id,
kpadj.cle_id,
kpadj.modified_from,
kpadj.modified_to,
kpadj.modifier_mechanism_type_code,
kpadj.operand,
kpadj.arithmetic_operator,
kpadj.automatic_flag,
kpadj.update_allowed,
kpadj.updated_flag,
kpadj.applied_flag,
kpadj.on_invoice_flag,
kpadj.pricing_phase_id,
kpadj.context,
kpadj.attribute1,
kpadj.attribute2,
kpadj.attribute3,
kpadj.attribute4,
kpadj.attribute5,
kpadj.attribute6,
kpadj.attribute7,
kpadj.attribute8,
kpadj.attribute9,
kpadj.attribute10,
kpadj.attribute11,
kpadj.attribute12,
kpadj.attribute13,
kpadj.attribute14,
kpadj.attribute15,
kpadj.list_header_id,
kpadj.list_line_id,
kpadj.list_line_type_code,
kpadj.change_reason_code,
kpadj.change_reason_text,
kpadj.estimated_flag,
kpadj.adjusted_amount,
kpadj.charge_type_code,
kpadj.charge_subtype_code,
kpadj.range_break_quantity,
kpadj.accrual_conversion_rate,
kpadj.pricing_group_sequence,
kpadj.accrual_flag,
kpadj.list_line_no,
kpadj.source_system_code,
kpadj.benefit_qty,
kpadj.benefit_uom_code,
kpadj.expiration_date,
kpadj.modifier_level_code,
kpadj.price_break_type_code,
kpadj.substitution_attribute,
kpadj.proration_type_code,
kpadj.include_on_returns_flag,
kpadj.rebate_transaction_type_code,
kpadj.creation_date
FROM
OKC_PRICE_ADJUSTMENTS kpadj,
OKX_QTE_PRC_ADJMNTS_V qpadj
WHERE
b_q_flag = OKC_API.g_true
AND (kpadj.chr_id = b_kh_id)
AND ((b_kl_id IS NULL and kpadj.cle_id IS NULL ) OR (b_kl_id IS NOT NULL AND kpadj.cle_id = b_kl_id))
--
AND (qpadj.quote_header_id(+) = b_qh_id)
--
AND NVL(qpadj.quote_line_id(+),0) = NVL(b_ql_id,0)
--
AND qpadj.modifier_header_id(+) = kpadj.list_header_id
AND qpadj.modifier_line_id(+) = kpadj.list_line_id
AND qpadj.modifier_line_type_code(+) = kpadj.list_line_type_code
UNION
SELECT
DECODE(opadj.list_header_id,NULL,g_aso_op_code_create,
DECODE(opadj.list_line_id,NULL,g_aso_op_code_create,
DECODE(opadj.list_line_type_code,NULL,
g_aso_op_code_create,g_aso_op_code_update)
)
) OPERATION_CODE,
opadj.PRICE_ADJUSTMENT_ID, -- order(header or line) price adj ID
kpadj.id, -- contract(header or line) price adj ID
kpadj.pat_id,
kpadj.chr_id,
kpadj.cle_id,
kpadj.modified_from,
kpadj.modified_to,
kpadj.modifier_mechanism_type_code,
kpadj.operand,
kpadj.arithmetic_operator,
kpadj.automatic_flag,
kpadj.update_allowed,
kpadj.updated_flag,
kpadj.applied_flag,
kpadj.on_invoice_flag,
kpadj.pricing_phase_id,
kpadj.context,
kpadj.attribute1,
kpadj.attribute2,
kpadj.attribute3,
kpadj.attribute4,
kpadj.attribute5,
kpadj.attribute6,
kpadj.attribute7,
kpadj.attribute8,
kpadj.attribute9,
kpadj.attribute10,
kpadj.attribute11,
kpadj.attribute12,
kpadj.attribute13,
kpadj.attribute14,
kpadj.attribute15,
kpadj.list_header_id,
kpadj.list_line_id,
kpadj.list_line_type_code,
kpadj.change_reason_code,
kpadj.change_reason_text,
kpadj.estimated_flag,
kpadj.adjusted_amount,
kpadj.charge_type_code,
kpadj.charge_subtype_code,
kpadj.range_break_quantity,
kpadj.accrual_conversion_rate,
kpadj.pricing_group_sequence,
kpadj.accrual_flag,
kpadj.list_line_no,
kpadj.source_system_code,
kpadj.benefit_qty,
kpadj.benefit_uom_code,
kpadj.expiration_date,
kpadj.modifier_level_code,
kpadj.price_break_type_code,
kpadj.substitution_attribute,
kpadj.proration_type_code,
kpadj.include_on_returns_flag,
kpadj.rebate_transaction_type_code,
kpadj.creation_date
FROM
OKC_PRICE_ADJUSTMENTS kpadj,
OKX_ORD_PRC_ADJMNTS_V opadj
WHERE
b_o_flag = OKC_API.g_true
AND (kpadj.chr_id = b_kh_id)
AND ((b_kl_id IS NULL and kpadj.cle_id IS NULL ) OR (b_kl_id IS NOT NULL AND kpadj.cle_id = b_kl_id))
--
AND (opadj.header_id(+) = b_oh_id)
--
AND NVL(opadj.line_id(+),0) = NVL(b_ol_id,0)
--
AND opadj.list_header_id(+) = kpadj.list_header_id
AND opadj.list_line_id(+) = kpadj.list_line_id
AND opadj.list_line_type_code(+) = kpadj.list_line_type_code
--
ORDER BY
1, -- kpadj.operation_code, -- CREATE, UPDATE
34, -- kpadj.list_header_id,
35, -- kpadj.list_line_id,
36, -- kpadj.list_line_type_code,
3, -- kpadj.id,
58; -- kpadj.creation_date;
l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
l_price_adj_tab.DELETE;
l_k_tmp_price_adj_tab.DELETE;
x_k_price_adj_tab.DELETE;
x_price_adj_tab.DELETE;
l_price_adj_insert := OKC_API.G_TRUE;
l_price_adj_rec.update_allowable_flag := price_adj_rec.update_allowed;
l_price_adj_rec.updated_flag := price_adj_rec.updated_flag;
IF price_adj_rec.operation_code = g_aso_op_code_update THEN
IF (l_debug = 'Y') THEN
okc_util.print_trace(1,'step 1-2 operation code = '||price_adj_rec.operation_code);
-- updated in the l_price_adj_tab variable
IF l_price_adj_tab.first IS NOT NULL THEN
FOR i IN l_price_adj_tab.first..l_price_adj_tab.last LOOP
IF l_price_adj_tab(i).price_adjustment_id = price_adj_rec.price_adjustment_id THEN
IF (l_debug = 'Y') THEN
okc_util.print_trace(1,'step 1-3 related quote price adjustment is already planned to be updated');
l_price_adj_insert := OKC_API.G_FALSE;
-- not already planned to be updated in the l_price_adj_tab variable.
--
IF l_prec_prc_adj_procesd = OKC_API.G_TRUE THEN
l_price_adj_insert := OKC_API.G_FALSE;
l_price_adj_insert := OKC_API.G_FALSE;
l_price_adj_rec.update_allowable_flag := l_prec_price_adj_rec.update_allowed;
l_price_adj_rec.updated_flag := l_prec_price_adj_rec.updated_flag;
IF l_price_adj_insert = OKC_API.G_TRUE THEN
l_price_adj_rec.quote_header_id := p_qhr_id;
l_price_adj_rec.update_allowable_flag := price_adj_rec.update_allowed;
l_price_adj_rec.updated_flag := price_adj_rec.updated_flag;
IF l_price_adj_insert = OKC_API.G_TRUE THEN
l_price_adj_tab(x) := l_price_adj_rec;
okc_util.print_trace(1,'update_allowable flag = '||l_price_adj_tab(x).update_allowable_flag);
okc_util.print_trace(1,'updated_flag = '||l_price_adj_tab(x).updated_flag);
IF l_prec_price_adj_rec.operation_code = g_aso_op_code_update AND
l_prec_okc_price_adj_id IS NOT NULL AND l_prec_prc_adj_procesd = OKC_API.G_FALSE THEN
-- populate l_price_adj_rec with infomation from l_prec_price_adj_rec;
l_price_adj_rec.update_allowable_flag := l_prec_price_adj_rec.update_allowed;
l_price_adj_rec.updated_flag := l_prec_price_adj_rec.updated_flag;
l_price_adj_insert := OKC_API.G_TRUE;
l_price_adj_insert := OKC_API.G_FALSE;
IF l_price_adj_insert = OKC_API.G_TRUE THEN
-- populate l_price_adj_rec with information from l_price_adj
l_price_adj_rec.operation_code := g_aso_op_code_delete;
okc_util.print_trace(1,'update_allowable flag = '||l_price_adj_tab(i).update_allowable_flag);
okc_util.print_trace(1,'updated_flag = '||l_price_adj_tab(i).updated_flag);
okc_util.print_trace(1,'update_allowable flag = '||x_price_adj_tab(i).update_allowable_flag);
okc_util.print_trace(1,'updated_flag = '||x_price_adj_tab(i).updated_flag);
SELECT flex_title,
pricing_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
comparison_operator
FROM
okc_price_adj_attribs kpadj
WHERE
kpadj.pat_id = b_kpat_id;
SELECT DECODE(qpadj.pricing_context,NULL,g_aso_op_code_create,
DECODE(qpadj.pricing_attribute,NULL,g_aso_op_code_create,g_aso_op_code_update)
) OPERATION_CODE,
qpadj.price_adj_attrib_id, -- quote price adj atribute ID
kpadj.id, -- contract price adj attribute id
kpadj.flex_title,
kpadj.pricing_context,
kpadj.pricing_attribute,
kpadj.pricing_attr_value_from,
kpadj.pricing_attr_value_to,
kpadj.comparison_operator
FROM
okc_price_adj_attribs kpadj,
OKX_QTE_PRC_ADJ_ATRBS_V qpadj
WHERE
b_q_flag = OKC_API.g_true
AND kpadj.pat_id = b_kpat_id
AND qpadj.price_adjustment_id(+) = b_pat_id
AND qpadj.flex_title(+) =kpadj.flex_title
AND qpadj.pricing_context(+)=kpadj.pricing_context
AND qpadj.pricing_attribute(+) = kpadj.pricing_attribute
UNION
SELECT DECODE(opadj.pricing_context,NULL,g_aso_op_code_create,
DECODE(opadj.pricing_attribute,NULL,g_aso_op_code_create,g_aso_op_code_update)
) OPERATION_CODE,
opadj.price_adj_attrib_id, -- order price adj atribute ID
kpadj.id, -- contract price adj attribute id
kpadj.flex_title,
kpadj.pricing_context,
kpadj.pricing_attribute,
kpadj.pricing_attr_value_from,
kpadj.pricing_attr_value_to,
kpadj.comparison_operator
FROM
okc_price_adj_attribs kpadj,
OKX_ORD_PRC_ADJ_ATRBS_V opadj
WHERE
b_o_flag = OKC_API.g_true
AND kpadj.pat_id = b_kpat_id
AND opadj.price_adjustment_id(+) = b_pat_id
AND opadj.flex_title(+) =kpadj.flex_title
AND opadj.pricing_context(+)=kpadj.pricing_context
AND opadj.pricing_attribute(+) = kpadj.pricing_attribute
ORDER BY
operation_code;
SELECT
qpadj.price_adjustment_id,
qpadj.price_adj_attrib_id, -- quote price adj attribute id
qpadj.flex_title,
qpadj.pricing_context,
qpadj.pricing_attribute,
qpadj.pricing_attr_value_from,
qpadj.pricing_attr_value_to,
qpadj.comparison_operator
FROM OKX_QTE_PRC_ADJ_ATRBS_V qpadj
WHERE b_q_flag = OKC_API.g_true
AND qpadj.price_adjustment_id = b_pat_id
UNION
SELECT
opadj.price_adjustment_id,
opadj.price_adj_attrib_id, -- order price adj attribute id
opadj.flex_title,
opadj.pricing_context,
opadj.pricing_attribute,
opadj.pricing_attr_value_from,
opadj.pricing_attr_value_to,
opadj.comparison_operator
FROM OKX_ORD_PRC_ADJ_ATRBS_V opadj
WHERE b_o_flag = OKC_API.g_true
AND opadj.price_adjustment_id = b_pat_id;
l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
x_price_adj_attr_tab.DELETE;
l_price_adj_attr_tab.DELETE;
IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
IF (l_debug = 'Y') THEN
okc_util.print_trace(1,'2 - operation_code = '||p_price_adj_tab(i).operation_code);
okc_util.print_trace(1,'2- Inserting the l_price_adj_attr_rec into the l_price_adj_attr_rec table');
okc_util.print_trace(1,'2- Index value for insert = '||x);
l_price_adj_insert := OKC_API.G_TRUE;
l_price_adj_insert := OKC_API.G_FALSE;
IF l_price_adj_insert = OKC_API.G_TRUE THEN
-- It should be inserted, but in fact no longer valid when coming back from the contract
-- Populate l_price_adj_attr_rec with information from l_price_adj_attr.
l_price_adj_attr_rec.price_adj_attrib_id:= l_price_adj_attr.price_adj_attrib_id;
l_price_adj_attr_rec.operation_code := g_aso_op_code_delete;
okc_util.print_trace(1,'2 A check for delete - Inserting the l_price_adj_attr_rec ');
END IF; -- IF p_price_adj_tab(i).operation_code = g_aso_op_code_update then..
IF p_price_adj_tab(i).operation_code = g_aso_op_code_delete THEN
FOR l_price_adj_attr IN c_price_adj_attr(p_q_flag, p_o_flag,
p_price_adj_tab(i).price_adjustment_id) LOOP
-- populate l_price_adj_attr_rec with information from l_price_adj_attr
l_price_adj_attr_rec.price_adjustment_id:= l_price_adj_attr.price_adjustment_id;
l_price_adj_attr_rec.operation_code := g_aso_op_code_delete;
SELECT
qpattr.PRICE_ATTRIBUTE_ID -- quote header price attribute id
FROM
OKX_QTE_PRC_ATRBS_V qpattr
WHERE b_q_flag = OKC_API.G_TRUE
AND qpattr.quote_header_id = b_qh_id
AND (( b_ql_id IS NULL AND qpattr.quote_line_id IS NULL ) OR
(b_ql_id IS NOT NULL AND qpattr.quote_line_id = b_ql_id ))
UNION
SELECT
opattr.ORDER_PRICE_ATTRIB_ID -- order header price attribute id
FROM
OKX_ORD_PRC_ATRBS_V opattr
WHERE b_o_flag = OKC_API.G_TRUE
AND opattr.header_id = b_oh_id
AND (( b_ol_id IS NULL AND opattr.line_id IS NULL ) OR
(b_ol_id IS NOT NULL AND opattr.line_id = b_ol_id ));
SELECT
kpattr.flex_title,
kpattr.pricing_context,
kpattr.pricing_attribute1,
kpattr.pricing_attribute2,
kpattr.pricing_attribute3,
kpattr.pricing_attribute4,
kpattr.pricing_attribute5,
kpattr.pricing_attribute6,
kpattr.pricing_attribute7,
kpattr.pricing_attribute8,
kpattr.pricing_attribute9,
kpattr.pricing_attribute10,
kpattr.pricing_attribute11,
kpattr.pricing_attribute12,
kpattr.pricing_attribute13,
kpattr.pricing_attribute14,
kpattr.pricing_attribute15,
kpattr.pricing_attribute16,
kpattr.pricing_attribute17,
kpattr.pricing_attribute18,
kpattr.pricing_attribute19,
kpattr.pricing_attribute20,
kpattr.pricing_attribute21,
kpattr.pricing_attribute22,
kpattr.pricing_attribute23,
kpattr.pricing_attribute24,
kpattr.pricing_attribute25,
kpattr.pricing_attribute26,
kpattr.pricing_attribute27,
kpattr.pricing_attribute28,
kpattr.pricing_attribute29,
kpattr.pricing_attribute30,
kpattr.pricing_attribute31,
kpattr.pricing_attribute32,
kpattr.pricing_attribute33,
kpattr.pricing_attribute34,
kpattr.pricing_attribute35,
kpattr.pricing_attribute36,
kpattr.pricing_attribute37,
kpattr.pricing_attribute38,
kpattr.pricing_attribute39,
kpattr.pricing_attribute40,
kpattr.pricing_attribute41,
kpattr.pricing_attribute42,
kpattr.pricing_attribute43,
kpattr.pricing_attribute44,
kpattr.pricing_attribute45,
kpattr.pricing_attribute46,
kpattr.pricing_attribute47,
kpattr.pricing_attribute48,
kpattr.pricing_attribute49,
kpattr.pricing_attribute50,
kpattr.pricing_attribute51,
kpattr.pricing_attribute52,
kpattr.pricing_attribute53,
kpattr.pricing_attribute54,
kpattr.pricing_attribute55,
kpattr.pricing_attribute56,
kpattr.pricing_attribute57,
kpattr.pricing_attribute58,
kpattr.pricing_attribute59,
kpattr.pricing_attribute60,
kpattr.pricing_attribute61,
kpattr.pricing_attribute62,
kpattr.pricing_attribute63,
kpattr.pricing_attribute64,
kpattr.pricing_attribute65,
kpattr.pricing_attribute66,
kpattr.pricing_attribute67,
kpattr.pricing_attribute68,
kpattr.pricing_attribute69,
kpattr.pricing_attribute70,
kpattr.pricing_attribute71,
kpattr.pricing_attribute72,
kpattr.pricing_attribute73,
kpattr.pricing_attribute74,
kpattr.pricing_attribute75,
kpattr.pricing_attribute76,
kpattr.pricing_attribute77,
kpattr.pricing_attribute78,
kpattr.pricing_attribute79,
kpattr.pricing_attribute80,
kpattr.pricing_attribute81,
kpattr.pricing_attribute82,
kpattr.pricing_attribute83,
kpattr.pricing_attribute84,
kpattr.pricing_attribute85,
kpattr.pricing_attribute86,
kpattr.pricing_attribute87,
kpattr.pricing_attribute88,
kpattr.pricing_attribute89,
kpattr.pricing_attribute90,
kpattr.pricing_attribute91,
kpattr.pricing_attribute92,
kpattr.pricing_attribute93,
kpattr.pricing_attribute94,
kpattr.pricing_attribute95,
kpattr.pricing_attribute96,
kpattr.pricing_attribute97,
kpattr.pricing_attribute98,
kpattr.pricing_attribute99,
kpattr.pricing_attribute100
FROM
okc_price_att_values kpattr
WHERE
kpattr.chr_id = b_kh_id
AND
((b_kl_id IS NULL AND kpattr.cle_id IS NULL ) OR
(b_kl_id IS NOT NULL AND kpattr.cle_id = b_kl_id));
x_price_attr_tab.DELETE;
l_price_attr_tab.DELETE;
l_price_attr_rec.operation_code := g_aso_op_code_delete;
SELECT pat_id_from,
cle_id,
pat_id
FROM OKC_PRICE_ADJ_ASSOCS kpadj
WHERE kpadj.pat_id_from = b_kpat_id;
SELECT
qpadj.ADJ_RELATIONSHIP_ID, --quote or order price adj rltship ID
qpadj.price_adjustment_id PRICE_ADJUSTMENT_ID,
qpadj.quote_shipment_id SHIPMENT_ID,
qpadj.quote_line_id LINE_ID
FROM OKX_QTE_PRC_ADJ_RLSHP_V qpadj
WHERE b_q_flag = OKC_API.g_true
AND qpadj.rltd_price_adj_id = b_pat_id
UNION
SELECT
opadj.PRICE_ADJ_ASSOC_ID, -- PRICE_ADJ_ASSOC_ID, -- quote price adj rltship ID
opadj.price_adjustment_id,
to_number(NULL), --qpadj.quote_shipment_id
opadj.line_id
FROM OKX_ORD_PRC_ADJ_RLSHP_V opadj
WHERE b_o_flag = OKC_API.g_true
AND opadj.rltd_price_adj_id = b_pat_id;
SELECT
qpadj.ADJ_RELATIONSHIP_ID, -- quote or order price adj rltship ID
qpadj.price_adjustment_id PRICE_ADJUSTMENT_ID,
qpadj.quote_shipment_id SHIPMENT_ID,
qpadj.quote_line_id LINE_ID,
qpadj.rltd_price_adj_id
FROM OKX_QTE_PRC_ADJ_RLSHP_V qpadj
WHERE b_q_flag = OKC_API.g_true
AND qpadj.price_adjustment_id = b_pat_id
AND ((b_ln_id IS NOT NULL AND qpadj.quote_line_id = b_ln_id) OR b_ln_id IS NULL)
UNION
SELECT opadj.PRICE_ADJ_ASSOC_ID, -- quote price adj rltship ID
opadj.price_adjustment_id,
to_number(NULL), --qpadj.quote_shipment_id
opadj.line_id,
opadj.rltd_price_adj_id
FROM OKX_ORD_PRC_ADJ_RLSHP_V opadj
WHERE b_o_flag = OKC_API.g_true
AND opadj.price_adjustment_id = b_pat_id
AND ((b_ln_id IS NOT NULL AND opadj.line_id = b_ln_id) OR b_ln_id IS NULL);
l_price_adj_insert VARCHAR2(1) := OKC_API.G_TRUE;
x_price_adj_rltship_tab.DELETE;
l_price_adj_rltship_tab.DELETE;
l_price_adj_insert := okc_api.g_false;
l_price_adj_insert := okc_api.g_true;
IF l_price_adj_insert = okc_api.g_true THEN
IF p_line_tab(l_ql).operation_code = g_aso_op_code_create THEN
l_price_adj_rltship_rec.qte_line_index:=l_ql; -- p_line_tab(l_ql).line_number
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
l_price_adj_rltship_rec.quote_line_id:= p_line_tab(l_ql).quote_line_id;
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
--set a specific error message, print it out and raise an exception
OKC_API.set_message(p_app_name => g_app_name,
p_msg_name => 'OKO_PRC_PADJREL1',
p_token1 => 'KLINEID',
p_token1_value => l_kl_id);
l_price_adj_insert := okc_api.g_false;
l_price_adj_insert := okc_api.g_true;
IF l_price_adj_insert = okc_api.g_true THEN
IF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_create THEN
l_price_adj_rltship_rec.rltd_price_adj_index:=l_kpat;
ELSIF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_update THEN
l_price_adj_rltship_rec.rltd_price_adj_id:= p_price_adj_tab(l_kpat).price_adjustment_id;
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
--set a specific error message, print it out and raise an exception
OKC_API.set_message(p_app_name => g_app_name,
p_msg_name => 'OKO_PRC_PADJREL3',
p_token1 => 'PRICEADJID',
p_token1_value => price_adj_rltship_rec.pat_id );
IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
FOR price_adj_rltship_rec IN c_k_price_adj_rltship (p_k_price_adj_tab(i).id) LOOP
-- Need to check up on the operation code of each related quote line
-- At this level, there is a relationship between a price adj and a valid contract line id, therefore
-- if the related quote line id cannot be found (quote line id or index), an exception must be raised.
--
IF price_adj_rltship_rec.cle_id IS NOT NULL THEN
l_price_adj_insert := okc_api.g_false;
l_price_adj_insert := okc_api.g_true;
IF l_price_adj_insert = okc_api.g_true THEN
IF p_line_tab(l_ql).operation_code = g_aso_op_code_create THEN
l_price_adj_rltship_rec.operation_code := g_aso_op_code_create;
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
--Need to check if the related quote line id is associated to the quote price adj id
OPEN c_price_adj_rltship(p_q_flag, p_o_flag,
p_price_adj_tab(i).price_adjustment_id, p_line_tab(l_ql).quote_line_id);
ELSE -- NEED to UPDATE OR NOT with shipment id?
-- Need to check if the quote adj rltship involves a shipment id
IF v_price_adj_rltship.shipment_id IS NOT NULL THEN
-- Need to check if we have a shipment line for the related quote line id
l_shipment := okc_api.g_false;
IF p_line_shipment_tab(l_qs).operation_code = g_aso_op_code_update THEN
l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
-- And we keep the same shipment id which is planned to be updated
ELSIF p_line_shipment_tab(l_qs).operation_code = g_aso_op_code_delete THEN
l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
--set a specific error message, print it out and raise an exception
OKC_API.set_message(p_app_name => g_app_name,
p_msg_name => 'OKO_PRC_PADJREL6');
END IF; -- ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_update THEN
END IF; -- IF l_price_adj_insert = okc_api.g_true THEN
l_price_adj_insert := okc_api.g_false;
l_price_adj_insert := okc_api.g_true;
IF l_price_adj_insert = okc_api.g_true THEN
IF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_create THEN
l_price_adj_rltship_rec.rltd_price_adj_index:=l_kpat;
ELSIF p_price_adj_tab(l_kpat).operation_code = g_aso_op_code_update THEN
l_price_adj_rltship_rec.rltd_price_adj_id:= p_price_adj_tab(l_kpat).price_adjustment_id;
ELSIF p_line_tab(l_ql).operation_code = g_aso_op_code_delete THEN
--set a specific error message, print it out and raise an exception
OKC_API.set_message(p_app_name => g_app_name,
p_msg_name => 'OKO_PRC_PADJREL8',
p_token1 => 'PRICEADJID',
p_token1_value => price_adj_rltship_rec.pat_id );
l_price_adj_insert:=okc_api.g_true;
l_price_adj_insert:=okc_api.g_false;
IF l_price_adj_insert=okc_api.g_true THEN
-- populate l_price_adj_rltship_rec with information from l_price_adj_rltship
----
l_price_adj_rltship_rec.adj_relationship_id := l_price_adj_rltship.adj_relationship_id;
l_price_adj_rltship_rec.operation_code := g_aso_op_code_delete;
END IF; -- IF p_price_adj_tab(i).operation_code = g_aso_op_code_update THEN
IF p_price_adj_tab(i).operation_code = g_aso_op_code_delete THEN
--Delete all relationships pertaining directly to this quote price adj
IF (l_debug = 'Y') THEN
okc_util.print_trace(1,'p_price_adj_tab - price_adjustment_id '||p_price_adj_tab(i).price_adjustment_id);
l_price_adj_rltship_rec.operation_code := g_aso_op_code_delete;
l_price_adj_rltship_rec.operation_code := g_aso_op_code_update;
l_line_tab.DELETE;
l_line_shipment_tab.DELETE;
l_k_price_adj_tab.DELETE;
l_k_temp_price_adj_tab.DELETE;
x_ln_tmp_price_adj_tab.DELETE;
x_ln_tmp_price_attr_tab.DELETE;
x_hd_price_adj_tab.DELETE;
x_ln_price_adj_tab.DELETE;
x_hd_price_adj_attr_tab.DELETE;
x_ln_price_adj_attr_tab.DELETE;
x_hd_price_attr_tab.DELETE;
x_ln_price_attr_tab.DELETE;
x_hd_price_adj_rltship_tab.DELETE;
x_ln_price_adj_rltship_tab.DELETE;
l_k_price_adj_tab.DELETE;
ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN
-- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
--Not valid now in case of an Order update from a contract
--Will need to be modified when K -> O for update will be
--required to be developed
p_o_flag => p_o_flag,
--
p_level =>'L',
--
p_nqhr_id => NULL,
p_nqle_idx => NULL,
--
x_k_price_adj_tab => l_k_temp_price_adj_tab,
x_price_adj_tab => x_ln_tmp_price_adj_tab);
ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN
-- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
--Not valid now in case of an Order update from a contract
--Will need to be modified when K -> O for update will be
--required to be developed
p_o_flag => p_o_flag,
--
p_level =>'L',
--
p_nqhr_id => NULL,
p_nqle_idx => NULL,
--
x_k_price_adj_tab => l_k_temp_price_adj_tab,
x_price_adj_tab => x_ln_tmp_price_adj_tab);
ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN
-- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
--Not valid now in case of an Order update from a contract
--Will need to be modified when K -> O for update will be
--required to be developed
p_o_flag => p_o_flag,
--
p_level =>'L',
--
p_nqhr_id => NULL,
p_nqle_idx => NULL,
--
x_price_attr_tab => x_ln_tmp_price_attr_tab);
ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN
-- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
--Not valid now in case of an Order update from a contract
--Will need to be modified when K -> O for update will be
--required to be developed
p_o_flag => p_o_flag,
--
p_level =>'L',
--
p_nqhr_id => NULL,
p_nqle_idx => NULL,
--
x_price_attr_tab => x_ln_tmp_price_attr_tab);