[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
D_delete_price_adjustments CONSTANT VARCHAR2(100) :=
PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'delete_price_adjustments');
PROCEDURE delete_line_adjs
(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE
,p_pricing_events IN VARCHAR2
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src IN VARCHAR2
--To fix price override not allowed error
,p_allow_price_override_flag IN VARCHAR2
,p_log_head IN VARCHAR2
);
PROCEDURE update_adj
(p_draft_id IN NUMBER
,p_price_adjustment_id IN NUMBER
,p_line_detail_index IN NUMBER
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src IN VARCHAR2
--To fix price override not allowed error
,p_allow_price_override_flag IN VARCHAR2
,px_debug_upd_adj_tbl OUT NOCOPY NUMBER_TYPE
,p_log_head IN VARCHAR2
);
PROCEDURE insert_adj
(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_doc_sub_type IN VARCHAR2
,p_log_head IN VARCHAR2
);
PROCEDURE update_adj_attribs
(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_pricing_events IN VARCHAR2
,p_log_head IN VARCHAR2
);
PROCEDURE insert_adj_attribs
(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_log_head IN VARCHAR2
);
PROCEDURE insert_adj_rec
(p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE);
PROCEDURE insert_draft_adj_rec
(p_draft_id IN NUMBER
,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE);
SELECT COUNT(1)
INTO l_man_mod_count
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_po_header_id
AND ADJV.po_line_id = p_po_line_id
AND ADJV.automatic_flag = 'N'
--AND ADJ.update_allowed = 'N'
AND NVL(ADJV.updated_flag,'N') = 'N';
SELECT COUNT(1)
INTO l_man_ovr_mod_count
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_po_header_id
AND ADJV.po_line_id = p_po_line_id
AND ADJV.automatic_flag = 'N'
AND ADJV.update_allowed = 'Y'
AND ADJV.updated_flag = 'Y';
SELECT COUNT(1)
INTO l_auto_mod_count
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_po_header_id
AND ADJV.po_line_id = p_po_line_id
AND ADJV.automatic_flag = 'Y'
--AND ADJ.update_allowed = 'N'
AND ADJV.updated_flag = 'N';
SELECT COUNT(1)
INTO l_auto_ovr_mod_count
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_po_header_id
AND ADJV.po_line_id = p_po_line_id
AND ADJV.automatic_flag = 'Y'
AND ADJV.update_allowed = 'Y'
AND ADJV.updated_flag = 'Y';
l_updated_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
l_selected_volume_attr QP_PREQ_GRP.VARCHAR_TYPE;
l_updated_flag QP_PREQ_GRP.VARCHAR_TYPE;
SELECT MIN(ADJV.price_adjustment_id) "MIN_PRICE_ADJ_ID"
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
AND ADJV.po_line_id = p_order_line_id
AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
OR
(ADJV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
AND
ADJV.updated_flag = QP_PREQ_GRP.G_YES
AND
ADJV.update_allowed = QP_PREQ_GRP.G_YES
)
);
SELECT ADJV.list_header_id "FROM_LIST_HEADER_ID"
,ADJV.list_line_id "FROM_LIST_LINE_ID"
,ADJV.list_line_type_code "FROM_LIST_LINE_TYPE_CODE"
,ADJV.list_type_code "FROM_LIST_TYPE_CODE"
,ADJV.list_line_no "LIST_LINE_NO"
--
,ADJV.arithmetic_operator "OPERAND_CALCULATION_CODE"
,ADJV.operand "OPERAND_VALUE"
--
,ADJV.updated_flag "UPDATED_FLAG"
,ADJV.applied_flag "APPLIED_FLAG"
,ADJV.update_allowed "OVERRIDE_FLAG"
,ADJV.automatic_flag "AUTOMATIC_FLAG"
--
,ADJV.pricing_group_sequence "PRICING_GROUP_SEQUENCE"
,ADJV.price_break_type_code "PRICE_BREAK_TYPE_CODE"
,ADJV.modifier_level_code "MODIFIER_LEVEL_CODE"
,ADJV.change_reason_code "CHANGE_REASON_CODE"
,ADJV.change_reason_text "CHANGE_REASON_TEXT"
--
,ADJV.price_adjustment_id "PRICE_ADJUSTMENT_ID" --Child price adjustment id
,ADJV.parent_adjustment_id "RLTD_PRICE_ADJUSTMENT_ID" --Parent price adjustment id
,ADJV.parent_list_line_type_code "RELATIONSHIP_TYPE_CODE" --Parent Child relationship type code
,ADJV.parent_list_line_id "RLTD_LIST_LINE_ID" --Parent List Line Id
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
AND ADJV.po_line_id = p_order_line_id
AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
OR
(ADJV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
AND
ADJV.updated_flag = QP_PREQ_GRP.G_YES
AND
ADJV.update_allowed = QP_PREQ_GRP.G_YES
)
)
ORDER BY ADJV.price_adjustment_id ASC;
SELECT (ATTRV.price_adjustment_id - p_min_price_adj_id) "LINE_DETAIL_INDEX"
--
,DECODE(ATTRV.flex_title
,'QP_ATTR_DEFNS_QUALIFIER', 'QUALIFIER'
,'QP_ATTR_DEFNS_PRODUCT', 'PRODUCT'
,'PRICING'
) "ATTRIBUTE_TYPE"
,ATTRV.pricing_context "CONTEXT"
,ATTRV.pricing_attribute "ATTRIBUTE"
,ATTRV.pricing_attr_value_from "VALUE_FROM"
,ATTRV.pricing_attr_value_to "VALUE_TO"
,ATTRV.comparison_operator "COMPARISON_OPERATOR_TYPE"
,DECODE(ATTRV.flex_title
,'QP_ATTR_DEFNS_QUALIFIER', 'Y', 'N') "VALIDATED_FLAG"
FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
WHERE ATTRV.po_header_id = p_order_header_id --ATTRV.draft_id = p_draft_id --sometimes draft_id may be passed as null
AND ATTRV.po_line_id = p_order_line_id
AND NVL(ATTRV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
AND (ATTRV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
OR
(ATTRV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
AND
ATTRV.updated_flag = QP_PREQ_GRP.G_YES
AND
ATTRV.update_allowed = QP_PREQ_GRP.G_YES
)
)
ORDER BY ATTRV.price_adjustment_id ASC;
l_from_list_header_id_tbl.delete;
l_from_list_line_id_tbl.delete;
l_from_list_line_type_tbl.delete;
l_from_list_type_code_tbl.delete;
l_list_line_no_tbl.delete;
l_operand_calc_code_tbl.delete;
l_operand_value_tbl.delete;
l_updated_flag_tbl.delete;
l_applied_flag_tbl.delete;
l_override_flag_tbl.delete;
l_automatic_flag_tbl.delete;
l_pricing_group_seq_tbl.delete;
l_price_break_type_code_tbl.delete;
l_modifier_level_code_tbl.delete;
l_change_reason_code_tbl.delete;
l_change_reason_text_tbl.delete;
l_price_adjustment_id_tbl.delete;
l_rltd_price_adj_id_tbl.delete; --Parent price adjustment id
l_relationship_type_code_tbl.delete; --Parent Child relationship type code
l_rltd_list_line_id_tbl.delete; --Parent List Line Id
l_line_detail_index.delete;
l_created_from_list_header_id.delete;
l_created_from_list_line_id.delete;
l_created_from_list_line_type.delete;
l_created_from_list_type_code.delete;
l_list_line_no.delete;
l_operand_calculation_code.delete;
l_operand_value.delete;
l_updated_flag.delete;
l_applied_flag.delete;
l_override_flag.delete;
l_automatic_flag.delete;
l_pricing_group_sequence.delete;
l_price_break_type_code.delete;
l_modifier_level_code.delete;
l_change_reason_code.delete;
l_change_reason_text.delete;
l_line_index.delete;
l_line_detail_type_code.delete;
l_line_quantity.delete;
l_pricing_status_code.delete;
l_pricing_status_text.delete;
l_list_price.delete;
l_created_from_sql.delete;
l_pricing_phase_id.delete;
l_substitution_type_code.delete;
l_substitution_value_from.delete;
l_substitution_value_to.delete;
l_ask_for_flag.delete;
l_price_formula_id.delete;
l_product_precedence.delete;
l_incompatablility_grp_code.delete;
l_primary_uom_flag.delete;
l_print_on_invoice_flag.delete;
l_benefit_qty.delete;
l_benefit_uom_code.delete;
l_accrual_flag.delete;
l_accrual_conversion_rate.delete;
l_estim_accrual_rate.delete;
l_recurring_flag.delete;
l_selected_volume_attr.delete;
l_rounding_factor.delete;
l_header_limit_exists.delete;
l_line_limit_exists.delete;
l_charge_type_code.delete;
l_charge_subtype_code.delete;
l_currency_detail_id.delete;
l_currency_header_id.delete;
l_selling_rounding_factor.delete;
l_order_currency.delete;
l_pricing_effective_date.delete;
l_base_currency_code.delete;
l_calculation_code.delete;
l_price_adjustment_id.delete;
l_accum_context.delete;
l_accum_attribute.delete;
l_accum_flag.delete;
l_break_uom_code.delete;
l_break_uom_context.delete;
l_break_uom_attribute.delete;
l_process_code.delete;
l_line_detail_index_mapping.delete;
l_line_index_rtbl.delete;
l_line_detail_index_rtbl.delete;
l_relationship_type_code_rtbl.delete;
l_rltd_line_index_rtbl.delete;
l_rltd_line_detail_index_rtbl.delete;
l_list_line_id_rtbl.delete;
l_rltd_list_line_id_rtbl.delete;
l_pricing_status_text_rtbl.delete;
l_line_index_atbl.delete;
l_line_detail_index_atbl.delete;
l_attribute_level_atbl.delete;
l_attribute_type_atbl.delete;
l_list_header_id_atbl.delete;
l_list_line_id_atbl.delete;
l_context_atbl.delete;
l_attribute_atbl.delete;
l_value_from_atbl.delete;
l_setup_value_from_atbl.delete;
l_value_to_atbl.delete;
l_setup_value_to_atbl.delete;
l_grouping_number_atbl.delete;
l_no_qualifiers_in_grp_atbl.delete;
l_compar_oper_type_atbl.delete;
l_validated_flag_atbl.delete;
l_applied_flag_atbl.delete;
l_pricing_status_code_atbl.delete;
l_pricing_status_text_atbl.delete;
l_qualifier_precedence_atbl.delete;
l_datatype_atbl.delete;
l_pricing_attr_flag_atbl.delete;
l_qualifier_type_atbl.delete;
l_product_uom_code_atbl.delete;
l_excluder_flag_atbl.delete;
l_pricing_phase_id_atbl.delete;
l_incomp_grp_code_atbl.delete;
l_line_detail_type_code_atbl.delete;
l_modifier_level_code_atbl.delete;
l_primary_uom_flag_atbl.delete;
l_updated_flag_tbl,
l_applied_flag_tbl,
l_override_flag_tbl,
l_automatic_flag_tbl,
l_pricing_group_seq_tbl,
l_price_break_type_code_tbl,
l_modifier_level_code_tbl,
l_change_reason_code_tbl,
l_change_reason_text_tbl,
l_price_adjustment_id_tbl,
l_rltd_price_adj_id_tbl, --Parent price adjustment id
l_relationship_type_code_tbl, --Parent Child relationship type code
l_rltd_list_line_id_tbl; --Parent List Line Id
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Initialize parameters before calling Insert Line Details in QP_PREQ_GRP');
l_updated_flag(m) := QP_PREQ_GRP.G_YES;
l_updated_flag(m) := l_updated_flag_tbl(j); -- if automatic, it can be either Y or N
l_selected_volume_attr(m) := NULL;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Initialize parameters before calling Insert Line Attr Details in QP_PREQ_GRP');
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_LDETS2 to insert line details');
QP_PREQ_GRP.INSERT_LDETS2
(p_line_detail_index => l_line_detail_index
,p_line_detail_type_code => l_line_detail_type_code
,p_price_break_type_code => l_price_break_type_code
,p_list_price => l_list_price
,p_line_index => l_line_index
,p_created_from_list_header_id => l_created_from_list_header_id
,p_created_from_list_line_id => l_created_from_list_line_id
,p_created_from_list_line_type => l_created_from_list_line_type
,p_created_from_list_type_code => l_created_from_list_type_code
,p_created_from_sql => l_created_from_sql
,p_pricing_group_sequence => l_pricing_group_sequence
,p_pricing_phase_id => l_pricing_phase_id
,p_operand_calculation_code => l_operand_calculation_code
,p_operand_value => l_operand_value
,p_substitution_type_code => l_substitution_type_code
,p_substitution_value_from => l_substitution_value_from
,p_substitution_value_to => l_substitution_value_to
,p_ask_for_flag => l_ask_for_flag
,p_price_formula_id => l_price_formula_id
,p_pricing_status_code => l_pricing_status_code
,p_pricing_status_text => l_pricing_status_text
,p_product_precedence => l_product_precedence
,p_incompatablility_grp_code => l_incompatablility_grp_code
,p_processed_flag => l_processed_flag
,p_applied_flag => l_applied_flag
,p_automatic_flag => l_automatic_flag
,p_override_flag => l_override_flag
,p_primary_uom_flag => l_primary_uom_flag
,p_print_on_invoice_flag => l_print_on_invoice_flag
,p_modifier_level_code => l_modifier_level_code
,p_benefit_qty => l_benefit_qty
,p_benefit_uom_code => l_benefit_uom_code
,p_list_line_no => l_list_line_no
,p_accrual_flag => l_accrual_flag
,p_accrual_conversion_rate => l_accrual_conversion_rate
,p_estim_accrual_rate => l_estim_accrual_rate
,p_recurring_flag => l_recurring_flag
,p_selected_volume_attr => l_selected_volume_attr
,p_rounding_factor => l_rounding_factor
,p_header_limit_exists => l_header_limit_exists
,p_line_limit_exists => l_line_limit_exists
,p_charge_type_code => l_charge_type_code
,p_charge_subtype_code => l_charge_subtype_code
,p_currency_detail_id => l_currency_detail_id
,p_currency_header_id => l_currency_header_id
,p_selling_rounding_factor => l_selling_rounding_factor
,p_order_currency => l_order_currency
,p_pricing_effective_date => l_pricing_effective_date
,p_base_currency_code => l_base_currency_code
,p_line_quantity => l_line_quantity
,p_updated_flag => l_updated_flag
,p_calculation_code => l_calculation_code
,p_change_reason_code => l_change_reason_code
,p_change_reason_text => l_change_reason_text
,p_price_adjustment_id => l_price_adjustment_id
,p_accum_context => l_accum_context
,p_accum_attribute => l_accum_attribute
,p_accum_flag => l_accum_flag
,p_break_uom_code => l_break_uom_code
,p_break_uom_context => l_break_uom_context
,p_break_uom_attribute => l_break_uom_attribute
,p_process_code => l_process_code
,x_status_code => x_return_status
,x_status_text => l_return_status_text
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_LDETS2');
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_RLTD_LINES2 to insert relationship between lines');
QP_PREQ_GRP.INSERT_RLTD_LINES2
(p_line_index => l_line_index_rtbl
,p_line_detail_index => l_line_detail_index_rtbl
,p_relationship_type_code => l_relationship_type_code_rtbl
,p_related_line_index => l_rltd_line_index_rtbl
,p_related_line_detail_index => l_rltd_line_detail_index_rtbl
,x_status_code => x_return_status
,x_status_text => l_return_status_text
,p_list_line_id => l_list_line_id_rtbl
,p_related_list_line_id => l_rltd_list_line_id_rtbl
,p_pricing_status_text => l_pricing_status_text_rtbl
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_RLTD_LINES2');
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_LINE_ATTRS2 to insert line attribute details');
QP_PREQ_GRP.INSERT_LINE_ATTRS2
(p_line_index_tbl => l_line_index_atbl
,p_line_detail_index_tbl => l_line_detail_index_atbl
,p_attribute_level_tbl => l_attribute_level_atbl
,p_attribute_type_tbl => l_attribute_type_atbl
,p_list_header_id_tbl => l_list_header_id_atbl
,p_list_line_id_tbl => l_list_line_id_atbl
,p_context_tbl => l_context_atbl
,p_attribute_tbl => l_attribute_atbl
,p_value_from_tbl => l_value_from_atbl
,p_setup_value_from_tbl => l_setup_value_from_atbl
,p_value_to_tbl => l_value_to_atbl
,p_setup_value_to_tbl => l_setup_value_to_atbl
,p_grouping_number_tbl => l_grouping_number_atbl
,p_no_qualifiers_in_grp_tbl => l_no_qualifiers_in_grp_atbl
,p_comparison_operator_type_tbl => l_compar_oper_type_atbl
,p_validated_flag_tbl => l_validated_flag_atbl
,p_applied_flag_tbl => l_applied_flag_atbl
,p_pricing_status_code_tbl => l_pricing_status_code_atbl
,p_pricing_status_text_tbl => l_pricing_status_text_atbl
,p_qualifier_precedence_tbl => l_qualifier_precedence_atbl
,p_datatype_tbl => l_datatype_atbl
,p_pricing_attr_flag_tbl => l_pricing_attr_flag_atbl
,p_qualifier_type_tbl => l_qualifier_type_atbl
,p_product_uom_code_tbl => l_product_uom_code_atbl
,p_excluder_flag_tbl => l_excluder_flag_atbl
,p_pricing_phase_id_tbl => l_pricing_phase_id_atbl
,p_incompatability_grp_code_tbl => l_incomp_grp_code_atbl
,p_line_detail_type_code_tbl => l_line_detail_type_code_atbl
,p_modifier_level_code_tbl => l_modifier_level_code_atbl
,p_primary_uom_flag_tbl => l_primary_uom_flag_atbl
,x_status_code => x_return_status
,x_status_text => l_return_status_text
);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_LINE_ATTRS2');
DELETE FROM QP_PREQ_RLTD_LINES_TMP_TEST;
DELETE FROM QP_PREQ_LINE_ATTRS_TMP_TEST;
DELETE FROM QP_PREQ_LINES_TMP_TEST;
DELETE FROM QP_PREQ_LDETS_TMP_TEST;
INSERT INTO QP_PREQ_RLTD_LINES_TMP_TEST (select * from QP_PREQ_RLTD_LINES_TMP);
INSERT INTO QP_PREQ_LINE_ATTRS_TMP_TEST (select * from QP_PREQ_LINE_ATTRS_TMP);
INSERT INTO QP_PREQ_LINES_TMP_TEST (select * from QP_PREQ_LINES_TMP);
INSERT INTO QP_PREQ_LDETS_TMP_TEST (select * from QP_PREQ_LDETS_TMP);
SELECT ADJ.price_adjustment_id
,LDUP.line_detail_index
FROM QP_LDETS_V LDUP
,QP_PREQ_LINES_TMP QLUP
,PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE LDUP.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
AND LDUP.list_line_id = ADJ.list_line_id
AND ADJ.po_line_id = QLUP.line_id
AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND QLUP.line_index = LDUP.line_index
AND QLUP.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED) --QP_PREQ_GRP.G_STATUS_NEW
AND QLUP.process_status <> 'NOT_VALID'
AND QLUP.line_type_code = 'LINE';
, p_delete_flag => NULL);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete outdated adjustments and dependant details');
delete_line_adjs
(p_draft_id => p_draft_id
,p_order_header_id => p_order_header_id
,p_order_line_id_tbl => p_order_line_id_tbl
,p_pricing_events => p_pricing_events || ','
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src => p_pricing_call_src
,p_allow_price_override_flag => p_allow_price_override_flag
,p_log_head => l_log_head);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update PO Line Adjustment details');
l_debug_upd_line_adj_tbl.delete;
update_adj
(p_draft_id => p_draft_id
,p_price_adjustment_id => upd_line_det.price_adjustment_id
,p_line_detail_index => upd_line_det.line_detail_index
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src => p_pricing_call_src
,p_allow_price_override_flag => p_allow_price_override_flag
,px_debug_upd_adj_tbl => l_debug_upd_line_adj_tbl
,p_log_head => l_log_head);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' LINE LEVEL ADJUSTMENTS');
insert_adj
(p_draft_id => p_draft_id
,p_order_header_id => p_order_header_id
,p_doc_sub_type => p_doc_sub_type
,p_log_head => l_log_head);
update_adj_attribs
(p_draft_id => p_draft_id
,p_order_header_id => p_order_header_id
,p_pricing_events => p_pricing_events || ','
,p_log_head => l_log_head);
PO_PRICE_ADJ_DRAFT_PKG.delete_rows
(p_draft_id => p_draft_id
,p_price_adjustment_id => NULL);
PROCEDURE delete_line_adjs
(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE
,p_pricing_events IN VARCHAR2
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src IN VARCHAR2
,p_allow_price_override_flag IN VARCHAR2
,p_log_head IN VARCHAR2)
IS
--
l_line_index NUMBER;
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Delete old line adjustments for lines with pricing_status_code UPDATED');
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Check if PO Line Id: '||p_order_line_id_tbl(j)||' is UPDATED by pricing engine');
SELECT QLINE.line_id, QLINE.price_flag, QLINE.line_index
INTO l_line_id, l_price_flag, l_line_index
FROM QP_PREQ_LINES_TMP QLINE
WHERE QLINE.line_id = p_order_line_id_tbl(j)
AND QLINE.line_type_code = 'LINE'
AND QLINE.price_flag IN ('Y')
AND QLINE.process_status <> 'NOT_VALID'
AND QLINE.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'The PO Line is not updated by pricing engine');
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
SET ADJD.delete_flag = 'Y'
WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND ADJD.po_header_id = p_order_header_id
AND ADJD.po_line_id = l_line_id
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
AND DECODE(NVL(p_pricing_call_src,'NULL'), 'RETRO', 'N',
DECODE(NVL(p_allow_price_override_flag,'Y'), 'Y', NVL(ADJD.updated_flag, 'N'), 'N')) = 'N' --to avoid deleting overridden-automatic and manual modifiers in normal mode
--AND ADJD.pricing_phase_id IN (SELECT QPP.pricing_phase_id --this condition has to be checked for pass and fail scenarios, right now it fails for most of the cases
-- FROM qp_event_phases QEP
-- ,qp_pricing_phases QPP
-- WHERE instr(p_pricing_events, QEP.pricing_event_code || ',') > 0
-- AND QPP.pricing_phase_id = QEP.pricing_phase_id
-- AND NVL(QPP.user_freeze_override_flag, QPP.freeze_override_flag)
-- = decode(l_price_flag, 'Y', nvl(QPP.user_freeze_override_flag, QPP.freeze_override_flag), 'P', 'Y'))
AND ADJD.list_line_id NOT IN (SELECT LD.list_line_id
FROM qp_ldets_v LD
WHERE LD.line_index = l_line_index
AND LD.process_code IN (QP_PREQ_GRP.G_STATUS_UPDATED
,QP_PREQ_GRP.G_STATUS_UNCHANGED
,QP_PREQ_GRP.G_STATUS_NEW)
AND (LD.applied_flag = 'Y'
OR
(nvl(ld.applied_flag, 'N') = 'N'
AND
nvl(ld.line_detail_type_code, 'x') = 'CHILD_DETAIL_LINE'
)
)
)
RETURNING ADJD.price_adjustment_id BULK COLLECT INTO l_adj_id_tbl;
PO_DEBUG.debug_stmt(p_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' LINE LEVEL ADJUSTMENTS WITH DELETE FLAG');
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
SET delete_flag = 'Y'
WHERE draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND parent_adjustment_id = l_adj_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' CHILD LINES WITH DELETE FLAG');
DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBS');
DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ASSOCS');
DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT WHERE draft_id = p_draft_id AND rltd_price_adj_id = l_adj_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' RLTD ASSOCS');
END delete_line_adjs;
PROCEDURE update_adj(p_draft_id IN NUMBER
,p_price_adjustment_id IN NUMBER
,p_line_detail_index IN NUMBER
--Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
,p_pricing_call_src IN VARCHAR2
,p_allow_price_override_flag IN VARCHAR2
,px_debug_upd_adj_tbl OUT NOCOPY NUMBER_TYPE
,p_log_head IN VARCHAR2)
IS
l_progress VARCHAR2(4) := '170';
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Update PO Line Adjustment details for');
SELECT ADJ.price_adjustment_id
INTO l_price_adjustment_id
FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE ADJ.draft_id = p_draft_id
AND ADJ.price_adjustment_id = p_price_adjustment_id
FOR UPDATE NOWAIT;
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
SET ( LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, operand
, operand_per_pqty
, adjusted_amount
, adjusted_amount_per_pqty
, arithmetic_operator
, pricing_phase_id
, pricing_group_sequence
, automatic_flag
, list_line_type_code
, applied_flag
, update_allowed
--, updated_flag
, charge_type_code
, charge_subtype_code
, range_break_quantity
, accrual_conversion_rate
, accrual_flag
, list_line_no
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, modifier_level_code
, price_break_type_code
, include_on_returns_flag
, lock_control
)
=
(SELECT
SYSDATE -- LAST_UPDATE_DATE
, FND_GLOBAL.user_id -- LAST_UPDATED_BY
, FND_GLOBAL.login_id -- LAST_UPDATE_LOGIN
, LDETS.order_qty_operand
, LDETS.operand_value
, LDETS.order_qty_adj_amt
, LDETS.adjustment_amount
, LDETS.operand_calculation_code
, LDETS.pricing_phase_id
, LDETS.pricing_group_sequence
, LDETS.automatic_flag
, LDETS.list_line_type_code
, LDETS.applied_flag
, LDETS.override_flag
--, LDETS.updated_flag
, LDETS.charge_type_code
, LDETS.charge_subtype_code
, LDETS.line_quantity --range_break_quantity
, LDETS.accrual_conversion_rate
, LDETS.accrual_flag
, LDETS.list_line_no
, LDETS.print_on_invoice_flag
, LDETS.expiration_date
, LDETS.rebate_transaction_type_code
, LDETS.modifier_level_code
, LDETS.price_break_type_code
, LDETS.include_on_returns_flag
, ADJD.lock_control + 1
FROM QP_LDETS_v LDETS
WHERE ldets.line_detail_index = p_line_detail_index
)
WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND ADJD.price_adjustment_id = p_price_adjustment_id
RETURNING ADJD.list_line_id BULK COLLECT INTO px_debug_upd_adj_tbl;
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
SET ADJD.updated_flag = 'N',
ADJD.change_reason_code = null,
ADJD.change_reason_text = null
WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND ADJD.price_adjustment_id = p_price_adjustment_id;
PO_DEBUG.debug_stmt(p_log_head,l_progress,'exiting update_adj procedure');
END update_adj;
PROCEDURE insert_adj(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_doc_sub_type IN VARCHAR2
,p_log_head IN VARCHAR2)
IS
--
l_progress VARCHAR2(4) := '270';
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Insert Adjustments called with Order Header ID: '||p_order_header_id);
INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, ADJ_LINE_NUM
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PO_HEADER_ID
, AUTOMATIC_FLAG
, PO_LINE_ID
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, operand
, Arithmetic_operator
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, list_line_no
, source_system_code
, benefit_qty
, benefit_uom_code
, print_on_invoice_flag
, expiration_date
, rebate_transaction_type_code
, rebate_transaction_reference
, rebate_payment_system_code
, redeemed_date
, redeemed_flag
, accrual_flag
, range_break_quantity
, accrual_conversion_rate
, pricing_group_sequence
, modifier_level_code
, price_break_type_code
, substitution_attribute
, proration_type_code
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
(SELECT
p_draft_id --DRAFT_ID
, po_price_adjustments_s.nextval -- PRICE_ADJUSTMENT_ID
, LDETS.line_detail_index --ADJ_LINE_NUM
, SYSDATE -- CREATION_DATE
, FND_GLOBAL.user_id -- CREATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, FND_GLOBAL.user_id -- LAST_UPDATED_BY
, FND_GLOBAL.login_id -- LAST_UPDATE_LOGIN
, NULL -- PROGRAM_APPLICATION_ID
, NULL -- PROGRAM_ID
, NULL -- PROGRAM_UPDATE_DATE
, NULL -- REQUEST_ID
, p_order_header_id -- HEADER_ID
, LDETS.automatic_flag -- AUTOMATIC_FLAG
, QLINE.line_id -- ORDER_LINE_ID
, NULL -- CONTEXT
, NULL -- ATTRIBUTE1
, NULL -- ATTRIBUTE2
, NULL -- ATTRIBUTE3
, NULL -- ATTRIBUTE4
, NULL -- ATTRIBUTE5
, NULL -- ATTRIBUTE6
, NULL -- ATTRIBUTE7
, NULL -- ATTRIBUTE8
, NULL -- ATTRIBUTE9
, NULL -- ATTRIBUTE10
, NULL -- ATTRIBUTE11
, NULL -- ATTRIBUTE12
, NULL -- ATTRIBUTE13
, NULL -- ATTRIBUTE14
, NULL -- ATTRIBUTE15
, NULL -- ORIG_SYS_DISCOUNT_REF
, LDETS.LIST_HEADER_ID -- LIST_HEADER_ID
, LDETS.LIST_LINE_ID -- LIST_LINE_ID
, LDETS.LIST_LINE_TYPE_CODE -- LIST_LINE_TYPE_CODE
, NULL -- MODIFIED FROM
, NULL -- MODIFIED_TO
, LDETS.updated_flag -- UPDATED_FLAG
, LDETS.override_flag -- UPDATE_ALLOWED
, LDETS.applied_flag -- APPLIED_FLAG
, NULL -- CHANGE_REASON_CODE
, NULL -- CHANGE_REASON_TEXT
, nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code
,'%', ldets.operand_value
,'LUMPSUM', ldets.operand_value
,ldets.operand_value * qline.priced_quantity / nvl(qline.line_quantity, 1)))
--OPERAND
, ldets.operand_calculation_code -- ARITHMETIC_OPERATOR
, NULL -- COST_ID
, NULL -- TAX_CODE
, NULL -- TAX_EXEMPT_FLAG
, NULL -- TAX_EXEMPT_NUMBER
, NULL -- TAX_EXEMPT_REASON_CODE
, NULL -- PARENT_ADJUSTMENT_ID
, NULL -- INVOICED_FLAG
, NULL -- ESTIMATED_FLAG
, NULL -- INC_IN_SALES_PERFORMANCE
, nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount * nvl(qline.priced_quantity, 1) / nvl(qline.line_quantity, 1))
-- ADJUSTED_AMOUNT
, LDETS.pricing_phase_id -- PRICING_PHASE_ID
, LDETS.charge_type_code -- CHARGE_TYPE_CODE
, LDETS.charge_subtype_code -- CHARGE_SUBTYPE_CODE
, LDETS.list_line_no -- LIST_LINE_NO
, QH.source_system_code||' - '
||p_doc_sub_type -- SOURCE_SYSTEM_CODE
, NULL -- LDETS.benefit_qty
, NULL -- LDETS.benefit_uom_code
, NULL -- PRINT_ON_INVOICE_FLAG
, LDETS.expiration_date -- EXPIRATION_DATE
, LDETS.rebate_transaction_type_code
, NULL -- REBATE_TRANSACTION_REFERENCE
, NULL -- REBATE_PAYMENT_SYSTEM_CODE
, NULL -- REDEEMED_DATE
, NULL -- REDEEMED_FLAG
, LDETS.accrual_flag -- ACCRUAL_FLAG
, LDETS.line_quantity -- RANGE_BREAK_QUANTITY
, LDETS.accrual_conversion_rate -- ACCRUAL_CONVERSION_RATE
, LDETS.pricing_group_sequence -- PRICING_GROUP_SEQUENCE
, LDETS.modifier_level_code -- MODIFIER_LEVEL_CODE
, LDETS.price_break_type_code -- PRICE_BREAK_TYPE_CODE
, NULL -- LDETS.SUBSTITUTION_ATTRIBUTE
, NULL -- LDETS.PRORATION_TYPE_CODE
, NULL -- CREDIT_OR_CHARGE_FLAG
, LDETS.include_on_returns_flag -- INCLUDE_ON_RETURNS_FLAG
, NULL -- AC_CONTEXT
, NULL -- AC_ATTRIBUTE1
, NULL -- AC_ATTRIBUTE2
, NULL -- AC_ATTRIBUTE3
, NULL -- AC_ATTRIBUTE4
, NULL -- AC_ATTRIBUTE5
, NULL -- AC_ATTRIBUTE6
, NULL -- AC_ATTRIBUTE7
, NULL -- AC_ATTRIBUTE8
, NULL -- AC_ATTRIBUTE9
, NULL -- AC_ATTRIBUTE10
, NULL -- AC_ATTRIBUTE11
, NULL -- AC_ATTRIBUTE12
, NULL -- AC_ATTRIBUTE13
, NULL -- AC_ATTRIBUTE14
, NULL -- AC_ATTRIBUTE15
, LDETS.operand_value -- OPERAND_PER_PQTY
, LDETS.adjustment_amount -- ADJUSTED_AMOUNT_PER_PQTY
, 1 -- LOCK_CONTROL
FROM QP_LDETS_v LDETS
, QP_PREQ_LINES_TMP QLINE
, QP_LIST_HEADERS_B QH
WHERE LDETS.list_header_id = QH.list_header_id
AND LDETS.process_code = QP_PREQ_GRP.G_STATUS_NEW
AND QLINE.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
AND QLINE.process_status <> 'NOT_VALID'
AND LDETS.line_index = QLINE.line_index
AND (nvl(LDETS.automatic_flag, 'N') = 'Y')
AND LDETS.created_from_list_type_code NOT IN ('PRL', 'AGR')
AND LDETS.list_line_type_code <> 'PLL'
AND LDETS.list_line_id NOT IN (SELECT ADJ.list_line_id
FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE ADJ.list_line_id = LDETS.list_line_id
AND ADJ.po_line_id = QLINE.line_id)
);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' ADJUSTMENTS');
SELECT ADJ.price_adjustment_id
, RADJ.price_adjustment_id
BULK COLLECT INTO l_price_adjustment_id_tbl
,l_rltd_price_adj_id_tbl
FROM QP_PREQ_RLTD_LINES_TMP RLTD
, QP_PREQ_LINES_TMP QPL
, PO_PRICE_ADJUSTMENTS_DRAFT ADJ
, PO_PRICE_ADJUSTMENTS_DRAFT RADJ
WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX
AND QPL.LINE_ID = ADJ.PO_LINE_ID
AND ADJ.draft_id = p_draft_id
AND QPL.LINE_TYPE_CODE = 'LINE'
AND QPL.PROCESS_STATUS <> 'NOT_VALID'
AND RLTD.LIST_LINE_ID = ADJ.LIST_LINE_ID
AND RLTD.RELATED_LINE_INDEX = QPL.LINE_INDEX
AND RLTD.RELATED_LIST_LINE_ID = RADJ.LIST_LINE_ID
AND ADJ.PO_LINE_ID = RADJ.PO_LINE_ID
AND ADJ.draft_id = RADJ.draft_id
AND RLTD.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJ
SET ADJ.parent_adjustment_id = l_price_adjustment_id_tbl(i)
WHERE ADJ.price_adjustment_id = l_rltd_price_adj_id_tbl(i);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'ERROR in inserting adjustments and associations');
END insert_adj;
PROCEDURE update_adj_attribs(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_pricing_events IN VARCHAR2
,p_log_head IN VARCHAR2)
IS
--
l_adj_id_tbl NUMBER_TYPE;
SELECT ADJ.price_adjustment_id, ldets.line_detail_index
FROM QP_PREQ_LINES_TMP QPL
, PO_PRICE_ADJUSTMENTS_DRAFT ADJ
, QP_LDETS_V LDETS
WHERE LDETS.list_line_id = ADJ.list_line_id
AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
AND LDETS.line_index = QPL.line_index
AND ADJ.pricing_phase_id IN (SELECT QEP.pricing_phase_id
FROM qp_event_phases QEP
WHERE instr(p_pricing_events, QEP.pricing_event_code || ',') > 0)
AND LDETS.process_code IN (QP_PREQ_GRP.G_STATUS_UNCHANGED, QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_NEW)
AND nvl(ADJ.updated_flag, 'N') = 'N'
AND QPL.line_id = ADJ.po_line_id
AND QPL.process_status <> 'NOT_VALID'
AND QPL.line_type_code = 'LINE'
AND QPL.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Update Adjustment Attributes called with the below parameters');
l_adj_id_tbl.delete;
l_line_detail_index_tbl.delete;
DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i)
AND ( pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to
)
NOT IN
(SELECT QPLAT.context
, QPLAT.attribute
, QPLAT.setup_value_from
, QPLAT.setup_value_to
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
AND QPLAT.line_detail_index = l_line_detail_index_tbl(i)
);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBUTES');
INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT
p_draft_id
, l_adj_id_tbl(i) --ADJ.PRICE_ADJUSTMENT_ID
, QPLAT.context
, QPLAT.attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, QPLAT.setup_value_from --VALUE_FROM
, QPLAT.setup_value_to --VALUE_TO
, QPLAT.comparison_operator_type_code
, decode(QPLAT.attribute_type,
'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
WHERE QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
AND ( QPLAT.context
, QPLAT.attribute
, QPLAT.setup_value_from
, QPLAT.setup_value_to
)
NOT IN
(SELECT pricing_context
, pricing_attribute
, pricing_attr_value_from
, pricing_attr_value_to
FROM PO_PRICE_ADJ_ATTRIBS_DRAFT
WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i))
);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' CHANGED ATTRIBS');
END update_adj_attribs;
PROCEDURE insert_adj_attribs(p_draft_id IN NUMBER
,p_order_header_id IN NUMBER
,p_log_head IN VARCHAR2)
IS
--
l_progress VARCHAR2(4) := '450';
PO_DEBUG.debug_stmt(p_log_head,l_progress,'Insert Adjustment Attributes called with the below parameters');
INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT p_draft_id
, ADJ.price_adjustment_id
, QPLAT.context
, QPLAT.attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, QPLAT.setup_value_from --VALUE_FROM
, QPLAT.setup_value_to --VALUE_TO
, QPLAT.comparison_operator_type_code
, decode(QPLAT.attribute_type,
'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
'QP_ATTR_DEFNS_PRICING')
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
, QP_LDETS_v LDETS
, PO_PRICE_ADJUSTMENTS_DRAFT ADJ
, QP_PREQ_LINES_TMP QPLINE
WHERE ADJ.po_header_id = p_order_header_id
AND QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
AND QPLAT.line_detail_index = LDETS.line_detail_index
AND QPLAT.line_index = LDETS.line_index
AND LDETS.list_line_id = ADJ.list_line_id
AND LDETS.process_code = QP_PREQ_PUB.G_STATUS_NEW
AND LDETS.line_index = QPLINE.line_index
AND QPLINE.line_id = ADJ.po_line_id
AND QPLINE.line_type_code = 'LINE'
AND QPLINE.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED)
AND QPLINE.process_status <> 'NOT_VALID'
);
PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' ATTRIBS');
END insert_adj_attribs;
SELECT price_adjustment_id
BULK COLLECT INTO l_price_adjustment_id_tbl
FROM po_price_adjustments_draft ADJD
WHERE ADJD.draft_id = p_draft_id
AND ADJD.applied_flag IS NULL --this check is needed to ensure if the manual modifier is newly added
AND NVL(ADJD.automatic_flag, 'N') = 'N' --line is not automatic modifier
AND NVL(ADJD.delete_flag, 'N') = 'N' --line is not marked for deletion
AND NVL(ADJD.change_accepted_flag, 'Y') = 'Y'; --change is accepted for the line. Do we need this condition??
INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT p_draft_id
, ADJD.price_adjustment_id
, PRA.pricing_attribute_context
, PRA.pricing_attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, PRA.pricing_attr_value_from --VALUE_FROM
, PRA.pricing_attr_value_to --VALUE_TO
, PRA.comparison_operator_code
, 'QP_ATTR_DEFNS_PRICING' --FLEX_TITLE
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM QP_PRICING_ATTRIBUTES PRA
,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
WHERE ADJD.draft_id = p_draft_id
AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
AND ADJD.list_header_id = PRA.list_header_id
AND ADJD.list_line_id = PRA.list_line_id
AND PRA.pricing_attribute_context IS NOT NULL --only pricing attributes are picked, also to avoid product attributes
AND PRA.pricing_attr_value_from IS NOT NULL); --pricing attr with from value should not be null
PO_LOG.stmt(d_mod, d_position, 'INSERTED '|| SQL%ROWCOUNT ||' PRICING PRICE ADJ ATTRIBS');
INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT p_draft_id
, ADJD.price_adjustment_id
, QUAL.qualifier_context
, QUAL.qualifier_attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, QUAL.qualifier_attr_value --VALUE_FROM
, QUAL.qualifier_attr_value_to --VALUE_TO
, QUAL.comparison_operator_code
, 'QP_ATTR_DEFNS_QUALIFIER' --FLEX_TITLE
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM QP_QUALIFIERS QUAL
,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
WHERE ADJD.draft_id = p_draft_id
AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
AND ADJD.list_header_id = QUAL.list_header_id
AND ADJD.list_line_id = QUAL.list_line_id
AND QUAL.qualifier_attr_value IS NOT NULL); --qualifier attr with from value should not be null
PO_LOG.stmt(d_mod, d_position, 'INSERTED '|| SQL%ROWCOUNT ||' QUALIFIER PRICE ADJ ATTRIBS');
SELECT ADJ.*
FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.po_line_id = p_src_line_id
AND (p_auto_manual_flag IS NULL OR ADJ.automatic_flag = p_auto_manual_flag)
AND (p_override_allowed_flag IS NULL OR ADJ.update_allowed = p_override_allowed_flag)
AND (p_overridden_flag IS NULL OR ADJ.updated_flag = p_overridden_flag);
SELECT COUNT(1)
INTO l_adjustments_exist
FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.po_line_id = p_dest_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
SELECT COUNT(1)
INTO l_src_adj_count
FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.po_line_id = p_src_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
SELECT COUNT(1)
INTO l_src_attr_count
FROM PO_PRICE_ADJ_ATTRIBS ATTR
,PO_PRICE_ADJUSTMENTS ADJ
WHERE ATTR.price_adjustment_id = ADJ.price_adjustment_id
AND ADJ.po_line_id = p_src_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
SELECT po_price_adjustments_s.nextval
INTO l_po_price_adjustment_record.price_adjustment_id
FROM SYS.DUAL;
l_po_price_adjustment_record.last_updated_by := fnd_global.user_id;
l_po_price_adjustment_record.last_update_date := SYSDATE;
l_po_price_adjustment_record.last_update_login := fnd_global.login_id;
l_po_price_adjustment_record.program_update_date := NULL;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call insert adjustment record');
insert_adj_rec(l_po_price_adjustment_record);
l_return_status_text := 'Insert adjustment record failed';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
INSERT INTO PO_PRICE_ADJ_ATTRIBS
(PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT
l_po_price_adjustment_record.price_adjustment_id --newly copied price_adjustment_id
, ATTR.pricing_context
, ATTR.pricing_attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, ATTR.pricing_attr_value_from
, ATTR.pricing_attr_value_to
, ATTR.comparison_operator
, ATTR.FLEX_TITLE
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM PO_PRICE_ADJ_ATTRIBS ATTR
WHERE ATTR.price_adjustment_id = l_src_price_adjustment_id);
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
UPDATE PO_PRICE_ADJUSTMENTS
SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
AND po_header_id = p_dest_po_header_id
AND po_line_id = p_dest_po_line_id
AND parent_adjustment_id IS NOT NULL; --Only child lines are considered
SELECT ADJV.*
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_line_id = p_src_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
AND (p_auto_manual_flag IS NULL OR ADJV.automatic_flag = p_auto_manual_flag)
AND (p_override_allowed_flag IS NULL OR ADJV.update_allowed = p_override_allowed_flag)
AND (p_overridden_flag IS NULL OR ADJV.updated_flag = p_overridden_flag);
SELECT COUNT(1)
INTO l_adjustments_exist
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.draft_id = p_draft_id
AND ADJV.po_line_id = p_dest_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJV.updated_flag = l_overridden_flag);
SELECT COUNT(1)
INTO l_src_adj_count
FROM PO_PRICE_ADJUSTMENTS_V ADJV
WHERE ADJV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJV.updated_flag = l_overridden_flag);
SELECT COUNT(1)
INTO l_src_asoc_count
FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.draft_id = p_draft_id
AND ASOC.line_id = p_src_po_line_id
AND EXISTS (SELECT 1
FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE ADJ.draft_id = p_draft_id
AND ADJ.line_id = p_src_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag));
SELECT COUNT(1)
INTO l_src_attr_count
FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
WHERE ATTRV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id AND ATTRV.draft_id = ADJV.draft_id --the draft id may not have been initialized when copy event was triggered
AND (l_auto_manual_flag IS NULL OR ATTRV.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ATTRV.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ATTRV.updated_flag = l_overridden_flag);
INSERT INTO PO_PRICE_ADJ_ASSOCS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICE_ADJ_ASSOC_ID
, LINE_ID
, RLTD_PRICE_ADJ_ID
, LOCK_CONTROL
)
(SELECT p_draft_id
, ASOC.price_adjustment_id
, SYSDATE --p_Line_Adj_Assoc_Rec.creation_date
, fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
, SYSDATE --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
, fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
, fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
, NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
, NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
, NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
, NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
, PO_PRICE_ADJ_ASSOCS_S.nextval
, p_dest_po_line_id
, ASOC.rltd_price_adj_id
, 1
FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.draft_id = p_draft_id
AND ASOC.line_id = p_src_po_line_id
AND EXISTS (SELECT 1
FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE ADJ.draft_id = p_draft_id
AND ADJ.line_id = p_src_po_line_id
AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag)));
SELECT po_price_adjustments_s.nextval
INTO l_po_price_adjustment_record.price_adjustment_id
FROM SYS.DUAL;
l_po_price_adjustment_record.last_updated_by := fnd_global.user_id;
l_po_price_adjustment_record.last_update_date := SYSDATE;
l_po_price_adjustment_record.last_update_login := fnd_global.login_id;
l_po_price_adjustment_record.program_update_date := NULL;
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call insert adjustment record');
insert_draft_adj_rec(p_draft_id, l_po_price_adjustment_record);
l_return_status_text := 'Insert adjustment record failed';
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
(DRAFT_ID
, PRICE_ADJUSTMENT_ID
, PRICING_CONTEXT
, PRICING_ATTRIBUTE
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PRICING_ATTR_VALUE_FROM
, PRICING_ATTR_VALUE_TO
, COMPARISON_OPERATOR
, FLEX_TITLE
, PRICE_ADJ_ATTRIB_ID
, LOCK_CONTROL
)
(SELECT
p_draft_id --ATTRV.draft_id --draft_id may not have been initialized when copy event was triggered
, l_po_price_adjustment_record.price_adjustment_id --newly copied price_adjustment_id
, ATTRV.pricing_context
, ATTRV.pricing_attribute
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, NULL
, NULL
, NULL
, NULL
, ATTRV.pricing_attr_value_from
, ATTRV.pricing_attr_value_to
, ATTRV.comparison_operator
, ATTRV.FLEX_TITLE
, PO_PRICE_ADJ_ATTRIBS_S.nextval
, 1
FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
WHERE ATTRV.price_adjustment_id = l_src_price_adjustment_id); --ATTRV.draft_id = p_draft_id --draft_id may not have been initialized when copy event was triggered
PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
AND draft_id = p_draft_id
AND po_header_id = p_dest_po_header_id
AND po_line_id = p_dest_po_line_id
AND parent_adjustment_id IS NOT NULL; --Only child lines are considered
PROCEDURE insert_adj_rec(p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE)
IS
BEGIN
INSERT INTO PO_PRICE_ADJUSTMENTS
(PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PO_HEADER_ID
, AUTOMATIC_FLAG
, PO_LINE_ID
, ADJ_LINE_NUM
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, OPERAND
, ARITHMETIC_OPERATOR
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, LIST_LINE_NO
, SOURCE_SYSTEM_CODE
, BENEFIT_QTY
, BENEFIT_UOM_CODE
, PRINT_ON_INVOICE_FLAG
, EXPIRATION_DATE
, REBATE_TRANSACTION_TYPE_CODE
, REBATE_TRANSACTION_REFERENCE
, REBATE_PAYMENT_SYSTEM_CODE
, REDEEMED_DATE
, REDEEMED_FLAG
, ACCRUAL_FLAG
, RANGE_BREAK_QUANTITY
, ACCRUAL_CONVERSION_RATE
, PRICING_GROUP_SEQUENCE
, MODIFIER_LEVEL_CODE
, PRICE_BREAK_TYPE_CODE
, SUBSTITUTION_ATTRIBUTE
, PRORATION_TYPE_CODE
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
(SELECT p_adj_rec.price_adjustment_id
, p_adj_rec.creation_date
, p_adj_rec.created_by
, p_adj_rec.last_update_date
, p_adj_rec.last_updated_by
, p_adj_rec.last_update_login
, p_adj_rec.program_application_id
, p_adj_rec.program_id
, p_adj_rec.program_update_date
, p_adj_rec.request_id
, p_adj_rec.po_header_id
, p_adj_rec.automatic_flag
, p_adj_rec.po_line_id
, p_adj_rec.adj_line_num
, p_adj_rec.context
, p_adj_rec.attribute1
, p_adj_rec.attribute2
, p_adj_rec.attribute3
, p_adj_rec.attribute4
, p_adj_rec.attribute5
, p_adj_rec.attribute6
, p_adj_rec.attribute7
, p_adj_rec.attribute8
, p_adj_rec.attribute9
, p_adj_rec.attribute10
, p_adj_rec.attribute11
, p_adj_rec.attribute12
, p_adj_rec.attribute13
, p_adj_rec.attribute14
, p_adj_rec.attribute15
, p_adj_rec.orig_sys_discount_ref
, p_adj_rec.list_header_id
, p_adj_rec.list_line_id
, p_adj_rec.list_line_type_code
, p_adj_rec.modified_from
, p_adj_rec.modified_to
, p_adj_rec.updated_flag
, p_adj_rec.update_allowed
, p_adj_rec.applied_flag
, p_adj_rec.change_reason_code
, p_adj_rec.change_reason_text
, p_adj_rec.operand
, p_adj_rec.arithmetic_operator
, p_adj_rec.cost_id
, p_adj_rec.tax_code
, p_adj_rec.tax_exempt_flag
, p_adj_rec.tax_exempt_number
, p_adj_rec.tax_exempt_reason_code
, p_adj_rec.parent_adjustment_id
, p_adj_rec.invoiced_flag
, p_adj_rec.estimated_flag
, p_adj_rec.inc_in_sales_performance
, p_adj_rec.adjusted_amount
, p_adj_rec.pricing_phase_id
, p_adj_rec.charge_type_code
, p_adj_rec.charge_subtype_code
, p_adj_rec.list_line_no
, p_adj_rec.source_system_code
, p_adj_rec.benefit_qty
, p_adj_rec.benefit_uom_code
, p_adj_rec.print_on_invoice_flag
, p_adj_rec.expiration_date
, p_adj_rec.rebate_transaction_type_code
, p_adj_rec.rebate_transaction_reference
, p_adj_rec.rebate_payment_system_code
, p_adj_rec.redeemed_date
, p_adj_rec.redeemed_flag
, p_adj_rec.accrual_flag
, p_adj_rec.range_break_quantity
, p_adj_rec.accrual_conversion_rate
, p_adj_rec.pricing_group_sequence
, p_adj_rec.modifier_level_code
, p_adj_rec.price_break_type_code
, p_adj_rec.substitution_attribute
, p_adj_rec.proration_type_code
, p_adj_rec.credit_or_charge_flag
, p_adj_rec.include_on_returns_flag
, p_adj_rec.ac_context
, p_adj_rec.ac_attribute1
, p_adj_rec.ac_attribute2
, p_adj_rec.ac_attribute3
, p_adj_rec.ac_attribute4
, p_adj_rec.ac_attribute5
, p_adj_rec.ac_attribute6
, p_adj_rec.ac_attribute7
, p_adj_rec.ac_attribute8
, p_adj_rec.ac_attribute9
, p_adj_rec.ac_attribute10
, p_adj_rec.ac_attribute11
, p_adj_rec.ac_attribute12
, p_adj_rec.ac_attribute13
, p_adj_rec.ac_attribute14
, p_adj_rec.ac_attribute15
, p_adj_rec.operand_per_pqty
, p_adj_rec.adjusted_amount_per_pqty
, 1 -- LOCK_CONTROL
FROM DUAL
);
END insert_adj_rec;
PROCEDURE insert_draft_adj_rec(p_draft_id IN NUMBER
,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE)
IS
BEGIN
INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
(DRAFT_ID
, CHANGE_ACCEPTED_FLAG
, DELETE_FLAG
, PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, PO_HEADER_ID
, AUTOMATIC_FLAG
, PO_LINE_ID
, ADJ_LINE_NUM
, CONTEXT
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORIG_SYS_DISCOUNT_REF
, LIST_HEADER_ID
, LIST_LINE_ID
, LIST_LINE_TYPE_CODE
, MODIFIED_FROM
, MODIFIED_TO
, UPDATED_FLAG
, UPDATE_ALLOWED
, APPLIED_FLAG
, CHANGE_REASON_CODE
, CHANGE_REASON_TEXT
, OPERAND
, ARITHMETIC_OPERATOR
, COST_ID
, TAX_CODE
, TAX_EXEMPT_FLAG
, TAX_EXEMPT_NUMBER
, TAX_EXEMPT_REASON_CODE
, PARENT_ADJUSTMENT_ID
, INVOICED_FLAG
, ESTIMATED_FLAG
, INC_IN_SALES_PERFORMANCE
, ADJUSTED_AMOUNT
, PRICING_PHASE_ID
, CHARGE_TYPE_CODE
, CHARGE_SUBTYPE_CODE
, LIST_LINE_NO
, SOURCE_SYSTEM_CODE
, BENEFIT_QTY
, BENEFIT_UOM_CODE
, PRINT_ON_INVOICE_FLAG
, EXPIRATION_DATE
, REBATE_TRANSACTION_TYPE_CODE
, REBATE_TRANSACTION_REFERENCE
, REBATE_PAYMENT_SYSTEM_CODE
, REDEEMED_DATE
, REDEEMED_FLAG
, ACCRUAL_FLAG
, RANGE_BREAK_QUANTITY
, ACCRUAL_CONVERSION_RATE
, PRICING_GROUP_SEQUENCE
, MODIFIER_LEVEL_CODE
, PRICE_BREAK_TYPE_CODE
, SUBSTITUTION_ATTRIBUTE
, PRORATION_TYPE_CODE
, CREDIT_OR_CHARGE_FLAG
, INCLUDE_ON_RETURNS_FLAG
, AC_CONTEXT
, AC_ATTRIBUTE1
, AC_ATTRIBUTE2
, AC_ATTRIBUTE3
, AC_ATTRIBUTE4
, AC_ATTRIBUTE5
, AC_ATTRIBUTE6
, AC_ATTRIBUTE7
, AC_ATTRIBUTE8
, AC_ATTRIBUTE9
, AC_ATTRIBUTE10
, AC_ATTRIBUTE11
, AC_ATTRIBUTE12
, AC_ATTRIBUTE13
, AC_ATTRIBUTE14
, AC_ATTRIBUTE15
, OPERAND_PER_PQTY
, ADJUSTED_AMOUNT_PER_PQTY
, LOCK_CONTROL
)
(SELECT p_draft_id
, p_adj_rec.change_accepted_flag
, p_adj_rec.delete_flag
, p_adj_rec.price_adjustment_id
, p_adj_rec.creation_date
, p_adj_rec.created_by
, p_adj_rec.last_update_date
, p_adj_rec.last_updated_by
, p_adj_rec.last_update_login
, p_adj_rec.program_application_id
, p_adj_rec.program_id
, p_adj_rec.program_update_date
, p_adj_rec.request_id
, p_adj_rec.po_header_id
, p_adj_rec.automatic_flag
, p_adj_rec.po_line_id
, p_adj_rec.adj_line_num
, p_adj_rec.context
, p_adj_rec.attribute1
, p_adj_rec.attribute2
, p_adj_rec.attribute3
, p_adj_rec.attribute4
, p_adj_rec.attribute5
, p_adj_rec.attribute6
, p_adj_rec.attribute7
, p_adj_rec.attribute8
, p_adj_rec.attribute9
, p_adj_rec.attribute10
, p_adj_rec.attribute11
, p_adj_rec.attribute12
, p_adj_rec.attribute13
, p_adj_rec.attribute14
, p_adj_rec.attribute15
, p_adj_rec.orig_sys_discount_ref
, p_adj_rec.list_header_id
, p_adj_rec.list_line_id
, p_adj_rec.list_line_type_code
, p_adj_rec.modified_from
, p_adj_rec.modified_to
, p_adj_rec.updated_flag
, p_adj_rec.update_allowed
, p_adj_rec.applied_flag
, p_adj_rec.change_reason_code
, p_adj_rec.change_reason_text
, p_adj_rec.operand
, p_adj_rec.arithmetic_operator
, p_adj_rec.cost_id
, p_adj_rec.tax_code
, p_adj_rec.tax_exempt_flag
, p_adj_rec.tax_exempt_number
, p_adj_rec.tax_exempt_reason_code
, p_adj_rec.parent_adjustment_id
, p_adj_rec.invoiced_flag
, p_adj_rec.estimated_flag
, p_adj_rec.inc_in_sales_performance
, p_adj_rec.adjusted_amount
, p_adj_rec.pricing_phase_id
, p_adj_rec.charge_type_code
, p_adj_rec.charge_subtype_code
, p_adj_rec.list_line_no
, p_adj_rec.source_system_code
, p_adj_rec.benefit_qty
, p_adj_rec.benefit_uom_code
, p_adj_rec.print_on_invoice_flag
, p_adj_rec.expiration_date
, p_adj_rec.rebate_transaction_type_code
, p_adj_rec.rebate_transaction_reference
, p_adj_rec.rebate_payment_system_code
, p_adj_rec.redeemed_date
, p_adj_rec.redeemed_flag
, p_adj_rec.accrual_flag
, p_adj_rec.range_break_quantity
, p_adj_rec.accrual_conversion_rate
, p_adj_rec.pricing_group_sequence
, p_adj_rec.modifier_level_code
, p_adj_rec.price_break_type_code
, p_adj_rec.substitution_attribute
, p_adj_rec.proration_type_code
, p_adj_rec.credit_or_charge_flag
, p_adj_rec.include_on_returns_flag
, p_adj_rec.ac_context
, p_adj_rec.ac_attribute1
, p_adj_rec.ac_attribute2
, p_adj_rec.ac_attribute3
, p_adj_rec.ac_attribute4
, p_adj_rec.ac_attribute5
, p_adj_rec.ac_attribute6
, p_adj_rec.ac_attribute7
, p_adj_rec.ac_attribute8
, p_adj_rec.ac_attribute9
, p_adj_rec.ac_attribute10
, p_adj_rec.ac_attribute11
, p_adj_rec.ac_attribute12
, p_adj_rec.ac_attribute13
, p_adj_rec.ac_attribute14
, p_adj_rec.ac_attribute15
, p_adj_rec.operand_per_pqty
, p_adj_rec.adjusted_amount_per_pqty
, 1 -- LOCK_CONTROL
FROM DUAL
);
END insert_draft_adj_rec;
PROCEDURE delete_price_adjustments
( p_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
, p_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE DEFAULT NULL
)
IS
l_price_adj_tbl NUMBER_TYPE;
DELETE FROM PO_PRICE_ADJUSTMENTS
WHERE po_header_id = p_po_header_id
AND (po_line_id = p_po_line_id OR p_po_line_id IS NULL)
RETURNING
price_adjustment_id
BULK COLLECT INTO
l_price_adj_tbl;
DELETE FROM PO_PRICE_ADJ_ASSOCS WHERE price_adjustment_id = l_price_adj_tbl(i);
DELETE FROM PO_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = l_price_adj_tbl(i);
DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
WHERE ATTR.price_adjustment_id IN (SELECT ADJ.price_adjustment_id
FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.header_id = p_header_id
AND ADJ.line_id = p_line_id);
DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
WHERE ASOC.line_id = p_line_id;
DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.header_id = p_header_id
AND ADJ.line_id = p_line_id;
PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments','000',sqlcode);
END delete_price_adjustments;
PROCEDURE delete_adjustment
( p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE )
IS
BEGIN
--Delete dependant fields first
--Delete Price Adjustment Attributes
DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
WHERE ATTR.price_adjustment_id = p_price_adjustment_id;
DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
WHERE ASOC.price_adjustment_id = p_price_adjustment_id;
DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
WHERE ASOC.rltd_price_adj_id = p_price_adjustment_id;
DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.price_adjustment_id = p_price_adjustment_id;
PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment','000',sqlcode);
END delete_adjustment;
PROCEDURE delete_adjustment_dependants
( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
, p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE )
IS
BEGIN
--Delete dependant fields first
--Delete Price Adjustment Attributes
DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT ATTR
WHERE ATTR.draft_id = p_draft_id
AND ATTR.price_adjustment_id IN (SELECT p_price_adjustment_id FROM DUAL
UNION
SELECT ASOC.rltd_price_adj_id
FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
WHERE ADJ.draft_id = p_draft_id
AND ADJ.price_adjustment_id IN (SELECT ASOC.rltd_price_adj_id
FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.draft_id = p_draft_id
AND ASOC.price_adjustment_id = p_price_adjustment_id;
DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
WHERE ASOC.draft_id = p_draft_id
AND ASOC.rltd_price_adj_id = p_price_adjustment_id;
PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment_dependants','000',sqlcode);
END delete_adjustment_dependants;