When there is a business requirement to create a New modifier, we can make use of this code to Register as a Concurrent Program and create a new Modifier after uploading the data to a custom table.
declare
lc_uom VARCHAR2 (5);
lc_cust_account_id NUMBER;
lc_list_type_code VARCHAR2 (150);
lc_list_line_type_code VARCHAR2 (150);
lc_prod_attr_value VARCHAR2 (25);
lc_segment_mapping_column VARCHAR2 (150);
lc_prc_context_name VARCHAR2 (150);
lc_prc_mapping_column VARCHAR2 (150);
lc_prcing_context_name VARCHAR2 (150);
lc_account_number VARCHAR2 (15);
lc_party_id NUMBER;
lc_hqual_mapping_column VARCHAR2 (30);
lc_hqual_context_name VARCHAR2 (50);
lc_hqual_precedence VARCHAR2 (10);
lc_lqual_mapping_column VARCHAR2 (30);
lc_lqual_context_name VARCHAR2 (50);
lc_lqual_precedence VARCHAR2 (10);
/* $Header: QPXEXDS1.sql 120.3 2006/08/22 06:14:06 nirmkuma noship $ */
l_control_rec qp_globals.control_rec_type;
l_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_msg_index NUMBER;
l_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
l_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
l_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
l_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
l_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
l_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
l_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
l_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
l_x_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
l_x_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
l_x_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
l_x_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
l_x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
l_x_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
l_x_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
ln_inventory_item_id NUMBER;
l_x_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
lc_arithmetic_operator VARCHAR2 (30);
lc_pricing_phases NUMBER;
lc_qual_context_name VARCHAR2 (50);
lc_pricing_name VARCHAR2 (240);
line_level VARCHAR2 (15);
ln_list_header_id NUMBER;
ln_transaction_type_id NUMBER;
ln_list_line_id NUMBER;
lcq_account_number VARCHAR2 (20);
lcq_party_id NUMBER;
lcq_cust_account_id NUMBER;
lnq_transaction_type_id NUMBER;
lcq_lqual_mapping_column VARCHAR2 (50);
lcq_lqual_context_name VARCHAR2 (50);
lcq_lqual_precedence VARCHAR2 (50);
ln_category_id NUMBER;
lc_lq_payment_term NUMBER;
ln_lq_org_id NUMBER;
lc_hq_payment_term NUMBER;
ln_hq_org_id NUMBER;
CURSOR modifier_cur
IS
SELECT TYPE, NAME, start_date, end_date, line_level, modifier_type,
formula, application_method, operand, modifier_start_date,
modifier_end_date, product_attribute, product_attr_value,
value_from, value_to, header_qualifier_attribute,
include_exclude, qualifier_start_date, qualifier_end_date,
header_qualifier_value, linequalifier_attribute,
linequalifier_start_date, linequalifier_end_date,
linequalifier_value, record_id
FROM xxxx_modifier_setup_stg
WHERE 1 = 1 AND status IS NULL;
— AND NAME = ‘To Apply 15% Discount if the order quantity is greater than 5’;
BEGIN
BEGIN
mo_global.set_policy_context (‘S’, 3);
END;
fnd_global.apps_initialize (user_id => gn_user_id,
resp_id => gn_resp_id,
resp_appl_id => gn_resp_apid
);
BEGIN
FOR rec_modifier_cur IN modifier_cur
LOOP
ln_list_header_id := NULL;
ln_list_line_id := NULL;
lc_arithmetic_operator := NULL;
lc_pricing_phases := NULL;
lc_qual_context_name := NULL;
lc_pricing_name := NULL;
line_level := NULL;
ln_list_header_id := NULL;
ln_transaction_type_id := NULL;
lc_uom := NULL;
lc_cust_account_id := NULL;
lc_list_type_code := NULL;
lc_list_line_type_code := NULL;
lc_prod_attr_value := NULL;
lc_segment_mapping_column := NULL;
lc_prc_context_name := NULL;
lc_prc_mapping_column := NULL;
lc_prcing_context_name := NULL;
lc_account_number := NULL;
lc_party_id := NULL;
lc_hqual_mapping_column := NULL;
lc_hqual_context_name := NULL;
lc_hqual_precedence := NULL;
lc_lqual_mapping_column := NULL;
lc_lqual_context_name := NULL;
lc_lqual_precedence := NULL;
l_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;
x_msg_index := NULL;
lcq_account_number := NULL;
lcq_party_id := NULL;
lcq_cust_account_id := NULL;
lnq_transaction_type_id := NULL;
lcq_lqual_mapping_column := NULL;
lcq_lqual_context_name := NULL;
lcq_lqual_precedence := NULL;
ln_category_id := NULL;
lc_lq_payment_term := NULL;
ln_lq_org_id := NULL;
lc_hq_payment_term := NULL;
ln_hq_org_id := NULL;
dbms_output.put_line (‘Modifier Upload Program Process Started’);
BEGIN
UPDATE xxxx_modifier_setup_stg
SET request_id = apps.fnd_global.conc_request_id
WHERE status IS NULL;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line (‘Error in updateing Request ID’);
END;
BEGIN
SELECT qlh.list_header_id
INTO ln_list_header_id
FROM qp_list_headers qlh
WHERE UPPER (qlh.NAME) = UPPER (rec_modifier_cur.NAME)
AND SYSDATE BETWEEN start_date_active
AND NVL (end_date_active, SYSDATE);
EXCEPTION
WHEN OTHERS
THEN
ln_list_header_id := NULL;
ln_list_line_id := NULL;
END;
DBMS_OUTPUT.put_line (‘list_header_id ‘ || ln_list_header_id);
IF ln_list_header_id IS NOT NULL
THEN
BEGIN
UPDATE xxxx_modifier_setup_stg
SET list_header_id = ln_list_header_id
WHERE NAME = rec_modifier_cur.NAME;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line ( ‘Error in Update ‘
|| ‘- ‘
|| rec_modifier_cur.NAME
|| ‘-‘
|| SQLERRM
);
END;
dbms_output.put_line (‘Call Update Process’);
update_modifier (ln_list_header_id, rec_modifier_cur.record_id);
END IF;
dbms_output.put_line (‘List header ‘ || ln_list_header_id);
IF ln_list_header_id IS NULL
THEN
dbms_output.put_line (‘Insert Modifier’);
BEGIN
SELECT lookup_code
INTO lc_list_type_code
FROM apps.fnd_lookup_values
WHERE lookup_type = ‘HOMEPG_MODIFIER_LIST_TYPE’
AND meaning = rec_modifier_cur.TYPE;
EXCEPTION
WHEN OTHERS
THEN
lc_list_type_code := NULL;
END;
dbms_output.put_line
(‘********************************************************’);
dbms_output.put_line (‘Name of Modifier ‘ || rec_modifier_cur.NAME);
dbms_output.put_line ( ‘Header Type ‘
|| rec_modifier_cur.TYPE
|| ‘-‘
|| lc_list_type_code
);
BEGIN
SELECT lookup_code
INTO lc_list_line_type_code
FROM apps.fnd_lookup_values
WHERE lookup_type = ‘LIST_LINE_TYPE_CODE’
AND meaning = rec_modifier_cur.modifier_type;
EXCEPTION
WHEN OTHERS
THEN
lc_list_line_type_code := NULL;
END;
dbms_output.put_line ( ‘List Line Type Code ‘
|| rec_modifier_cur.modifier_type
|| ‘-‘
|| lc_list_line_type_code
);
BEGIN
SELECT segment_mapping_column,
UPPER (qpt.user_prc_context_name)
INTO lc_segment_mapping_column,
lc_prc_context_name
FROM qp_segments_v qpv, qp_prc_contexts_tl qpt
WHERE qpt.prc_context_id = qpv.prc_context_id
AND user_segment_name =
rec_modifier_cur.product_attribute;
EXCEPTION
WHEN OTHERS
THEN
lc_segment_mapping_column := NULL;
lc_prc_context_name := NULL;
END;
dbms_output.put_line ( ‘Segment Mapping Column ‘
|| rec_modifier_cur.product_attribute
|| ‘-‘
|| lc_segment_mapping_column
);
dbms_output.put_line ( ‘Context name ‘
|| rec_modifier_cur.product_attribute
|| ‘-‘
|| lc_prc_context_name
);
BEGIN
SELECT lookup_code
INTO lc_arithmetic_operator
FROM apps.fnd_lookup_values
WHERE lookup_type = ‘ARITHMETIC_OPERATOR’
AND meaning = rec_modifier_cur.application_method;
EXCEPTION
WHEN OTHERS
THEN
lc_list_line_type_code := NULL;
END;
dbms_output.put_line (‘Arithmetic Operator ‘ || lc_arithmetic_operator);
IF rec_modifier_cur.line_level = ‘Line’
AND ( rec_modifier_cur.product_attribute = ‘All Items’
OR rec_modifier_cur.product_attribute = ‘Item Number’
)
THEN
lc_pricing_name := ‘List Line Adjustment’;
line_level := UPPER (rec_modifier_cur.line_level);
ELSIF rec_modifier_cur.line_level = ‘Order’
THEN
lc_pricing_name := ‘Header Level Adjustments’;
line_level := UPPER (rec_modifier_cur.line_level);
ELSE
lc_pricing_name := ‘All Lines Adjustment’;
line_level := UPPER (rec_modifier_cur.line_level);
END IF;
BEGIN
SELECT pricing_phase_id
INTO lc_pricing_phases
FROM apps.qp_pricing_phases
WHERE NAME = lc_pricing_name;
EXCEPTION
WHEN OTHERS
THEN
lc_pricing_phases := NULL;
END;
dbms_output.put_line (‘Line Level ‘ || line_level);
dbms_output.put_line ( ‘Pricing Phase : ‘
|| lc_pricing_name
|| ‘-‘
|| lc_pricing_phases
);
IF rec_modifier_cur.header_qualifier_attribute IS NOT NULL
THEN
BEGIN
SELECT segment_mapping_column,
UPPER (qpt.user_prc_context_name),
user_precedence
INTO lc_hqual_mapping_column,
lc_hqual_context_name,
lc_hqual_precedence
FROM qp_segments_v qpv, qp_prc_contexts_tl qpt
WHERE qpt.prc_context_id = qpv.prc_context_id
AND user_segment_name =
rec_modifier_cur.header_qualifier_attribute;
EXCEPTION
WHEN OTHERS
THEN
lc_hqual_mapping_column := NULL;
lc_hqual_context_name := NULL;
lc_hqual_precedence := NULL;
END;
END IF;
dbms_output.put_line ( ‘Header Qualifier ‘
|| lc_hqual_context_name
|| ‘-‘
|| lc_hqual_mapping_column
|| ‘-‘
|| lc_hqual_precedence
);
IF rec_modifier_cur.linequalifier_attribute IS NOT NULL
THEN
BEGIN
SELECT segment_mapping_column,
UPPER (qpt.user_prc_context_name),
user_precedence
INTO lcq_lqual_mapping_column,
lcq_lqual_context_name,
lcq_lqual_precedence
FROM qp_segments_v qpv, qp_prc_contexts_tl qpt
WHERE qpt.prc_context_id = qpv.prc_context_id
AND user_segment_name =
rec_modifier_cur.linequalifier_attribute;
EXCEPTION
WHEN OTHERS
THEN
lcq_lqual_mapping_column := NULL;
lcq_lqual_context_name := NULL;
lcq_lqual_precedence := NULL;
END;
dbms_output.put_line ( ‘Line Qualifier ‘
|| lcq_lqual_context_name
|| ‘-‘
|| lcq_lqual_mapping_column
|| ‘-‘
|| lcq_lqual_precedence
);
END IF;
IF (rec_modifier_cur.header_qualifier_attribute =
‘Customer Name’
)
THEN
BEGIN
SELECT account_number, hp.party_id,
hca.cust_account_id
INTO lc_account_number, lc_party_id,
lc_cust_account_id
FROM apps.hz_parties hp, hz_cust_accounts_all hca
WHERE hp.party_id = hca.party_id
AND hp.party_name =
rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
lc_party_id := NULL;
lc_cust_account_id := NULL;
lc_account_number := NULL;
END;
dbms_output.put_line ( ‘Account Number ‘
|| lc_account_number
|| rec_modifier_cur.header_qualifier_value
);
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Order Type’
THEN
BEGIN
SELECT transaction_type_id
INTO ln_transaction_type_id
FROM apps.oe_transaction_types_tl
WHERE UPPER (NAME) =
UPPER (rec_modifier_cur.header_qualifier_value);
EXCEPTION
WHEN OTHERS
THEN
ln_transaction_type_id := NULL;
END;
END IF;
IF (rec_modifier_cur.header_qualifier_attribute =
‘Payment Terms’
)
THEN
BEGIN
SELECT term_id
INTO lc_hq_payment_term
FROM apps.ra_terms
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND NAME = rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
lc_hq_payment_term := NULL;
END;
END IF;
IF (rec_modifier_cur.header_qualifier_attribute = ‘Ship From’
)
THEN
BEGIN
SELECT organization_id
INTO ln_hq_org_id
FROM apps.org_organization_definitions
WHERE organization_name =
rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
ln_hq_org_id := NULL;
END;
END IF;
IF (rec_modifier_cur.linequalifier_attribute = ‘Customer Name’
)
THEN
BEGIN
SELECT account_number, hp.party_id,
hca.cust_account_id
INTO lcq_account_number, lcq_party_id,
lcq_cust_account_id
FROM apps.hz_parties hp, hz_cust_accounts_all hca
WHERE hp.party_id = hca.party_id
AND hp.party_name =
rec_modifier_cur.linequalifier_value;
EXCEPTION
WHEN OTHERS
THEN
lcq_party_id := NULL;
lcq_cust_account_id := NULL;
lcq_account_number := NULL;
END;
dbms_output.put_line ( ‘Line Qualifier Customer Account Number ‘
|| lcq_account_number
|| rec_modifier_cur.linequalifier_value
);
ELSIF rec_modifier_cur.linequalifier_attribute = ‘Order Type’
THEN
BEGIN
SELECT transaction_type_id
INTO lnq_transaction_type_id
FROM apps.oe_transaction_types_tl
WHERE UPPER (NAME) =
UPPER (rec_modifier_cur.linequalifier_value);
EXCEPTION
WHEN OTHERS
THEN
lnq_transaction_type_id := NULL;
END;
END IF;
IF (rec_modifier_cur.linequalifier_attribute = ‘Payment Terms’
)
THEN
BEGIN
SELECT term_id
INTO lc_lq_payment_term
FROM apps.ra_terms
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND NAME = rec_modifier_cur.linequalifier_value;
EXCEPTION
WHEN OTHERS
THEN
lc_lq_payment_term := NULL;
END;
END IF;
IF (rec_modifier_cur.linequalifier_attribute = ‘Ship From’)
THEN
BEGIN
SELECT organization_id
INTO ln_lq_org_id
FROM apps.org_organization_definitions
WHERE organization_name =
rec_modifier_cur.linequalifier_value;
EXCEPTION
WHEN OTHERS
THEN
ln_lq_org_id := NULL;
END;
END IF;
——–
IF rec_modifier_cur.product_attribute = ‘All Items’
THEN
lc_prod_attr_value := ‘ALL’;
ELSIF rec_modifier_cur.product_attribute = ‘Item Number’
THEN
BEGIN
SELECT inventory_item_id
INTO ln_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = rec_modifier_cur.product_attr_value
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
ln_inventory_item_id := NULL;
END;
ELSIF rec_modifier_cur.product_attribute = ‘Item Category’
THEN
BEGIN
SELECT category_id
INTO ln_category_id
FROM apps.mtl_categories_v
WHERE category_concat_segs =
rec_modifier_cur.product_attr_value;
EXCEPTION
WHEN OTHERS
THEN
ln_category_id := NULL;
END;
END IF;
dbms_output.put_line ( ‘Category’
|| ‘-‘
|| rec_modifier_cur.product_attr_value
|| ‘-‘
|| ln_category_id
);
l_modifier_list_rec.currency_code := ‘USD’;
l_modifier_list_rec.list_type_code := lc_list_type_code;
l_modifier_list_rec.start_date_active :=
rec_modifier_cur.start_date;
l_modifier_list_rec.end_date_active :=
rec_modifier_cur.end_date;
l_modifier_list_rec.source_system_code := ‘QP’;
l_modifier_list_rec.active_flag := ‘Y’;
l_modifier_list_rec.NAME := rec_modifier_cur.NAME;
l_modifier_list_rec.description := rec_modifier_cur.NAME;
l_modifier_list_rec.pte_code := ‘ORDFUL’;
l_modifier_list_rec.operation := qp_globals.g_opr_create;
—————Line Record Values
l_modifiers_tbl (1).list_line_id := qp_list_lines_s.nextval;
l_modifiers_tbl (1).list_line_type_code :=
lc_list_line_type_code;
l_modifiers_tbl (1).automatic_flag := ‘Y’;
l_modifiers_tbl (1).modifier_level_code := line_level;
–chk this
l_modifiers_tbl (1).accrual_flag := ‘N’;
l_modifiers_tbl (1).start_date_active :=
rec_modifier_cur.modifier_start_date;
l_modifiers_tbl (1).end_date_active :=
rec_modifier_cur.modifier_end_date;
l_modifiers_tbl (1).arithmetic_operator :=
lc_arithmetic_operator;
–l_modifiers_tbl (1).pricing_group_sequence := 1; –not rquired
l_modifiers_tbl (1).pricing_phase_id := lc_pricing_phases;
–l_modifiers_tbl (1).price_break_type_code :=’POINT’
l_modifiers_tbl (1).product_precedence := 1;
l_modifiers_tbl (1).operand := rec_modifier_cur.operand;
—
l_modifiers_tbl (1).operation := qp_globals.g_opr_create;
/* Create a Pricing Attribute record to specify the ‘ALL products’ condition */
IF rec_modifier_cur.product_attribute IS NOT NULL
THEN –added on 09/10
l_pricing_attr_tbl (1).product_attribute_context :=
lc_prc_context_name;
l_pricing_attr_tbl (1).product_attribute :=
lc_segment_mapping_column;
IF rec_modifier_cur.product_attribute = ‘All Items’
THEN
l_pricing_attr_tbl (1).product_attr_value :=
lc_prod_attr_value;
ELSIF rec_modifier_cur.product_attribute = ‘Item Number’
THEN
l_pricing_attr_tbl (1).product_attr_value :=
ln_inventory_item_id;
ELSIF rec_modifier_cur.product_attribute = ‘Item Category’
THEN
l_pricing_attr_tbl (1).product_attr_value :=
ln_category_id;
END IF;
l_pricing_attr_tbl (1).excluder_flag := ‘N’;
END IF;
IF rec_modifier_cur.value_from IS NOT NULL
THEN
l_pricing_attr_tbl (1).pricing_attribute_context :=
lc_prcing_context_name;
l_pricing_attr_tbl (2).pricing_attribute :=
lc_prc_mapping_column;
l_pricing_attr_tbl (1).pricing_attr_value_from :=
rec_modifier_cur.value_from;
l_pricing_attr_tbl (1).pricing_attr_value_to :=
rec_modifier_cur.value_to;
END IF;
IF rec_modifier_cur.value_from IS NOT NULL
AND rec_modifier_cur.value_to IS NOT NULL
THEN
l_pricing_attr_tbl (1).comparison_operator_code :=
‘BETWEEN’;
END IF;
IF rec_modifier_cur.product_attribute = ‘Item Number’
THEN
BEGIN
SELECT primary_uom_code
INTO lc_uom
FROM mtl_system_items_b
WHERE segment1 = rec_modifier_cur.product_attr_value
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
lc_uom := NULL;
END;
END IF;
l_pricing_attr_tbl (1).product_uom_code := lc_uom;
l_pricing_attr_tbl (1).accumulate_flag := ‘N’;
l_pricing_attr_tbl (1).modifiers_index := 1;
l_pricing_attr_tbl (1).operation := qp_globals.g_opr_create;
*/ /* Create a Qualifier Record */
IF rec_modifier_cur.header_qualifier_attribute IS NOT NULL
THEN
l_qualifiers_tbl (1).list_line_id := -1;
l_qualifiers_tbl (1).excluder_flag := ‘N’;
l_qualifiers_tbl (1).comparison_operator_code := ‘=’;
l_qualifiers_tbl (1).qualifier_context :=
lc_hqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lc_hqual_mapping_column;
IF rec_modifier_cur.header_qualifier_attribute =
‘Customer Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_cust_account_id;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Order Type’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
ln_transaction_type_id;
END IF;
l_qualifiers_tbl (1).qualifier_precedence :=
lc_hqual_precedence;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
l_qualifiers_tbl (1).start_date_active :=
rec_modifier_cur.qualifier_start_date;
l_qualifiers_tbl (1).end_date_active :=
rec_modifier_cur.qualifier_end_date;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
END IF;
IF rec_modifier_cur.linequalifier_attribute IS NOT NULL
THEN
DBMS_OUTPUT.put_line (‘Inside LineQualifier’);
l_qualifiers_tbl (1).list_line_id := qp_list_lines_s.currval;
l_qualifiers_tbl (1).excluder_flag :=
NVL (rec_modifier_cur.include_exclude, ‘N’);
l_qualifiers_tbl (1).comparison_operator_code := ‘=’;
l_qualifiers_tbl (1).qualifier_context :=
lcq_lqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lcq_lqual_mapping_column;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
l_qualifiers_tbl (1).qualifier_attr_value :=
lcq_cust_account_id;
l_qualifiers_tbl (1).qualifier_precedence :=
lcq_lqual_precedence;
l_qualifiers_tbl (1).start_date_active :=
rec_modifier_cur.linequalifier_start_date;
l_qualifiers_tbl (1).end_date_active :=
rec_modifier_cur.linequalifier_end_date;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
END IF;
qp_modifiers_pub.process_modifiers
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_modifier_list_rec => l_modifier_list_rec,
p_modifiers_tbl => l_modifiers_tbl,
p_qualifiers_tbl => l_qualifiers_tbl,
p_qualifiers_val_tbl => l_qualifiers_val_tbl,
p_pricing_attr_tbl => l_pricing_attr_tbl,
x_modifier_list_rec => l_x_modifier_list_rec,
x_modifier_list_val_rec => l_x_modifier_list_val_rec,
x_modifiers_tbl => l_x_modifiers_tbl,
x_modifiers_val_tbl => l_x_modifiers_val_tbl,
x_qualifiers_tbl => l_x_qualifiers_tbl,
x_qualifiers_val_tbl => l_x_qualifiers_val_tbl,
x_pricing_attr_tbl => l_x_pricing_attr_tbl,
x_pricing_attr_val_tbl => l_x_pricing_attr_val_tbl
);
DBMS_OUTPUT.put_line (l_return_status);
FOR k IN 1 .. x_msg_count
LOOP
x_msg_data :=
oe_msg_pub.get (p_msg_index => k,
p_encoded => ‘F’);
— Get message count and data
END LOOP;
dbms_output.put_line (‘err msg ‘ || x_msg_data);
IF l_return_status = ‘S’
THEN
UPDATE xxxx_modifier_setup_stg
SET status = ‘PROCESSED’,
list_type_code = lc_list_type_code,
list_line_type_code = lc_list_line_type_code,
pricing_phases = lc_pricing_name,
product_attr_context = lc_prc_context_name,
qualifier_context = lc_hqual_context_name,
qualifier_attribute = lc_hqual_mapping_column,
request_id = gn_request_id,
created_by = gn_user_id,
creation_date = SYSDATE,
last_updated_by = gn_user_id,
last_updated_date = SYSDATE
WHERE NAME = rec_modifier_cur.NAME
AND record_id = rec_modifier_cur.record_id;
ELSIF l_return_status = ‘E’
THEN
UPDATE xxxx_modifier_setup_stg
SET status = ‘NOT PROCESSED’,
list_type_code = lc_list_type_code,
list_line_type_code = lc_list_line_type_code,
pricing_phases = lc_pricing_name,
product_attr_context = lc_prc_context_name,
qualifier_context = lc_hqual_context_name,
qualifier_attribute = lc_hqual_mapping_column,
error_message = x_msg_data,
request_id = gn_request_id,
created_by = gn_user_id,
creation_date = SYSDATE,
last_updated_by = gn_user_id,
last_updated_date = SYSDATE
WHERE NAME = rec_modifier_cur.NAME
AND record_id = rec_modifier_cur.record_id;
ELSIF l_return_status = ‘U’
THEN
UPDATE xxxx_modifier_setup_stg
SET status = ‘NOT PROCESSED’,
list_type_code = lc_list_type_code,
list_line_type_code = lc_list_line_type_code,
pricing_phases = lc_pricing_name,
product_attr_context = lc_prc_context_name,
qualifier_context = lc_hqual_context_name,
qualifier_attribute = lc_hqual_mapping_column,
error_message = x_msg_data,
request_id = gn_request_id,
created_by = gn_user_id,
creation_date = SYSDATE,
last_updated_by = gn_user_id,
last_updated_date = SYSDATE
WHERE NAME = rec_modifier_cur.NAME
AND record_id = rec_modifier_cur.record_id;
END IF;
dbms_output.put_line (‘*********************************’);
END IF;
COMMIT;
END LOOP;
END;
END;
