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;
l_empty_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
l_empty_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
lc_arithmetic_operator VARCHAR2 (30);
lc_pricing_phases NUMBER;
lc_qual_context_name VARCHAR2 (50);
lc_pricing_name VARCHAR2 (240);
line_level VARCHAR2 (15);
update_modifer NUMBER;
ln_transaction_type_id NUMBER;
ln_grouping_no NUMBER;
ln_category_id NUMBER;
ld_end_date_active DATE;
ln_mod_count NUMBER;
ln_list_line_id NUMBER;
ln_prod_count NUMBER;
ln_qual_count NUMBER;
ln_qualifier_id NUMBER;
ln_qual_list_line_id NUMBER;
ln_qplist_line_id NUMBER;
ln_qplist_count NUMBER;
lc_lq_payment_term NUMBER;
ln_lq_org_id NUMBER;
lc_hq_payment_term NUMBER;
ln_hq_org_id NUMBER;
lc_hq_freight_term VARCHAR2 (150);
lc_lq_freight_term VARCHAR2 (150);
lc_hq_ship_method VARCHAR2 (150);
lc_lq_ship_method VARCHAR2 (150);
ln_hq_customer_id VARCHAR2 (150);
ln_lq_customer_id VARCHAR2 (150);
–lc_list_type_code VARCHAR2(15);
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, header_qualifier_value, qualifier_start_date,
qualifier_end_date, linequalifier_start_date,
linequalifier_end_date, linequalifier_attribute,
linequalifier_value, record_id, OPERATOR, modifier_line_number,
qualifier_type
FROM XXXX_modifier_setup_stg
WHERE list_header_id = p_list_header_id ;
BEGIN
BEGIN
mo_global.set_policy_context (‘S’, org_id);
DEBUG (‘Initialization’ || p_list_header_id || ‘ ‘ || p_record_id);
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
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;
ln_grouping_no := NULL;
ld_end_date_active := NULL;
ln_category_id := NULL;
ln_mod_count := NULL;
ln_list_line_id := NULL;
ln_prod_count := NULL;
ln_qual_count := NULL;
ln_qualifier_id := NULL;
ln_qual_list_line_id := NULL;
ln_qplist_line_id := NULL;
ln_qplist_count := NULL;
lc_lq_payment_term := NULL;
ln_lq_org_id := NULL;
lc_hq_payment_term := NULL;
ln_hq_org_id := NULL;
lc_hq_freight_term := NULL;
lc_lq_freight_term := NULL;
lc_hq_ship_method := NULL;
lc_lq_ship_method := NULL;
ln_hq_customer_id := NULL;
ln_lq_customer_id := NULL;
–lc_list_type_code:=NULL;
DEBUG (‘Modifier Qualifier Upload Program Update Process Started’);
IF UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
THEN
UPDATE XXXX_modifier_setup_stg
SET linequalifier_attribute =
rec_modifier_cur.header_qualifier_attribute,
linequalifier_start_date =
rec_modifier_cur.qualifier_start_date,
linequalifier_end_date =
rec_modifier_cur.qualifier_end_date,
linequalifier_value =
rec_modifier_cur.header_qualifier_value
WHERE record_id = p_record_id;
COMMIT;
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute IS NOT NULL
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
THEN
DEBUG (‘Updating the LIST Qualifier’);
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;
DEBUG ( ‘Header Qualifier ‘
|| lc_hqual_context_name
|| ‘-‘
|| lc_hqual_mapping_column
|| ‘-‘
|| lc_hqual_precedence
);
DEBUG ( rec_modifier_cur.linequalifier_attribute
|| UPPER (rec_modifier_cur.qualifier_type)
);
IF ( rec_modifier_cur.linequalifier_attribute IS NOT NULL
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
DEBUG (‘Line Qualifier Context ‘);
BEGIN
SELECT segment_mapping_column,
UPPER (qpt.user_prc_context_name), user_precedence
INTO lc_lqual_mapping_column,
lc_lqual_context_name, lc_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 =
NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
);
EXCEPTION
WHEN OTHERS
THEN
lc_lqual_mapping_column := NULL;
lc_lqual_context_name := NULL;
lc_lqual_precedence := NULL;
END;
DEBUG ( ‘Line Qualifier ‘
|| lc_lqual_context_name
|| ‘-‘
|| lc_lqual_mapping_column
|| ‘-‘
|| lc_lqual_precedence
);
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute = ‘Customer Name’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
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;
DEBUG ( ‘Account Number ‘
|| lc_account_number
|| ‘-‘
|| rec_modifier_cur.header_qualifier_value
);
ELSIF ( rec_modifier_cur.header_qualifier_attribute = ‘Order Type’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
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’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
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 = ‘Freight Terms’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
THEN
BEGIN
SELECT freight_terms_code
INTO lc_hq_freight_term
FROM apps.oe_frght_terms_active_v
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND freight_terms = rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
lc_hq_freight_term := NULL;
END;
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute =
‘Shipping Method’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
THEN
BEGIN
SELECT lookup_code
INTO lc_hq_ship_method
FROM apps.oe_ship_methods_v
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND enabled_flag = ‘Y’
AND meaning = rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
lc_hq_ship_method := NULL;
END;
END IF;
—-Sold By
IF ( rec_modifier_cur.header_qualifier_attribute =
‘Distributor Name’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
THEN
BEGIN
SELECT customer_id
INTO ln_hq_customer_id
FROM apps.qp_customers_v
WHERE customer_name = rec_modifier_cur.header_qualifier_value;
EXCEPTION
WHEN OTHERS
THEN
ln_hq_customer_id := NULL;
END;
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute = ‘Ship From’
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
)
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’)
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
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 =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
lc_party_id := NULL;
lc_cust_account_id := NULL;
lc_account_number := NULL;
END;
DEBUG ( ‘Line Qualifier Customer Account Number ‘
|| lc_account_number
|| ‘-‘
|| rec_modifier_cur.linequalifier_value
);
END IF;
DEBUG (rec_modifier_cur.qualifier_type);
IF ( rec_modifier_cur.linequalifier_attribute IS NOT NULL
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
DEBUG (‘Inside IF’);
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;
DEBUG (‘Trans ‘ || ln_transaction_type_id);
IF ( rec_modifier_cur.linequalifier_attribute = ‘Payment Terms’
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
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 =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
lc_lq_payment_term := NULL;
END;
END IF;
IF ( rec_modifier_cur.linequalifier_attribute = ‘Freight Terms’
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
BEGIN
SELECT freight_terms_code
INTO lc_lq_freight_term
FROM apps.oe_frght_terms_active_v
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND freight_terms =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
lc_lq_freight_term := NULL;
END;
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute =
‘Shipping Method’
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
BEGIN
SELECT lookup_code
INTO lc_lq_ship_method
FROM apps.oe_ship_methods_v
WHERE TRUNC (SYSDATE) BETWEEN NVL (start_date_active,
TRUNC (SYSDATE)
)
AND NVL (end_date_active,
TRUNC (SYSDATE)
)
AND enabled_flag = ‘Y’
AND meaning =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
lc_lq_ship_method := NULL;
END;
END IF;
IF ( rec_modifier_cur.header_qualifier_attribute =
‘Distributor Name’
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
BEGIN
SELECT customer_id
INTO ln_lq_customer_id
FROM apps.qp_customers_v
WHERE customer_name =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
ln_lq_customer_id := NULL;
END;
END IF;
IF ( rec_modifier_cur.linequalifier_attribute = ‘Ship From’
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
BEGIN
SELECT organization_id
INTO ln_lq_org_id
FROM apps.org_organization_definitions
WHERE UPPER (organization_name) =
NVL (UPPER (rec_modifier_cur.linequalifier_value),
UPPER (rec_modifier_cur.header_qualifier_value)
);
EXCEPTION
WHEN OTHERS
THEN
ln_lq_org_id := NULL;
END;
END IF;
–IF p_list_type = ‘Modifier Lines’
–THEN
/* 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;
*/
BEGIN
SELECT list_type_code
INTO lc_list_type_code
FROM apps.qp_list_headers_all
WHERE list_header_id = p_list_header_id;
EXCEPTION
WHEN OTHERS
THEN
lc_list_type_code := NULL;
END;
— END IF;
l_modifiers_tbl (1).list_header_id := p_list_header_id;
——————–
BEGIN
SELECT end_date_active
INTO ld_end_date_active
FROM qp_list_headers_all
WHERE list_header_id = p_list_header_id;
IF rec_modifier_cur.end_date IS NOT NULL
THEN
DEBUG (‘Modifer ‘ || rec_modifier_cur.NAME || ‘ is end dated ‘);
l_modifier_list_rec.list_header_id := p_list_header_id;
l_modifier_list_rec.end_date_active :=
rec_modifier_cur.end_date;
l_modifier_list_rec.operation := qp_globals.g_opr_update;
–l_modifiers_tbl.DELETE;
–l_pricing_attr_tbl.DELETE;
–l_qualifiers_tbl.DELETE;
–l_modifiers_tbl(1).list_header_id := p_list_header_id;
l_modifiers_tbl (1).end_date_active :=
rec_modifier_cur.end_date;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
ld_end_date_active := NULL;
END;
BEGIN
SELECT list_line_id
INTO ln_list_line_id
FROM apps.qp_list_lines
WHERE list_header_id = p_list_header_id
AND list_line_no = rec_modifier_cur.modifier_line_number;
EXCEPTION
WHEN OTHERS
THEN
ln_list_line_id := -1;
END;
/* Create a Qualifier Record */
DBMS_OUTPUT.put_line ( p_list_type
|| UPPER (rec_modifier_cur.qualifier_type)
);
———–Check if qualifier exists or create a new qualifier
IF rec_modifier_cur.header_qualifier_attribute IS NOT NULL
AND UPPER (rec_modifier_cur.qualifier_type) = ‘LIST’
THEN
BEGIN
SELECT COUNT (qualifier_id)
INTO ln_qual_count
FROM qp_qualifiers
WHERE list_header_id = p_list_header_id
AND (list_line_id = -1)
AND qualifier_context = lc_hqual_context_name
AND qualifier_attribute = lc_hqual_mapping_column
AND ( qualifier_attr_value = lc_cust_account_id
OR qualifier_attr_value = ln_transaction_type_id
OR qualifier_attr_value =
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
ln_qual_count := NULL;
END;
BEGIN
SELECT qualifier_id, list_line_id
INTO ln_qualifier_id, ln_qual_list_line_id
FROM qp_qualifiers
WHERE list_header_id = p_list_header_id
AND (list_line_id = -1)
AND qualifier_context = lc_hqual_context_name
AND qualifier_attribute = lc_hqual_mapping_column
AND ( qualifier_attr_value = lc_cust_account_id
OR qualifier_attr_value = ln_transaction_type_id
OR qualifier_attr_value =
rec_modifier_cur.header_qualifier_value
);
EXCEPTION
WHEN OTHERS
THEN
ln_qualifier_id := NULL;
ln_qual_list_line_id := NULL;
END;
IF (NVL (ln_qual_count, 0) = 0)
THEN
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
–l_modifiers_tbl (1).list_line_id := ln_list_line_id;
/* l_modifier_list_rec.operation := qp_globals.g_opr_update;
l_modifiers_tbl (1).operation := qp_globals.g_opr_update;*/
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
— l_modifiers_tbl (1).list_line_type_code :=
— lc_list_line_type_code;
l_qualifiers_tbl (1).list_line_id := -1;
ELSE
DEBUG (‘Update Header Qualifier’);
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;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Payment Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_hq_payment_term;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Freight Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_hq_freight_term;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Shipping Method’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_hq_ship_method;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Distributor Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
ln_hq_customer_id;
ELSIF rec_modifier_cur.header_qualifier_attribute = ‘Ship From’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_hq_org_id;
END IF;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_update;
— l_modifiers_tbl (1).list_line_id := ln_list_line_id;
— l_modifiers_tbl (1).list_header_id := p_list_header_id;
— l_modifier_list_rec.list_type_code := lc_list_type_code;
— l_modifier_list_rec.list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_line_id := ln_qual_list_line_id;
l_qualifiers_tbl (1).qualifier_id := ln_qualifier_id;
l_qualifiers_tbl (1).qualifier_context := lc_hqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lc_hqual_mapping_column;
l_qualifiers_tbl (1).qualifier_precedence :=
lc_hqual_precedence;
l_qualifiers_tbl (1).excluder_flag :=
rec_modifier_cur.include_exclude;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
l_qualifiers_tbl (1).comparison_operator_code :=
rec_modifier_cur.OPERATOR;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_update;
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_modifier_list_rec.operation := qp_globals.g_opr_update;
— l_modifiers_tbl (1).operation := qp_globals.g_opr_update;
— l_modifier_list_rec.operation := qp_globals.g_opr_update;
— l_pricing_attr_tbl (1).operation := qp_globals.g_opr_update;
END IF;
END IF;
— To insert a new qualifier when the operation is create
IF (rec_modifier_cur.header_qualifier_attribute IS NOT NULL
–AND l_qualifiers_tbl (1).operation = qp_globals.g_opr_create
)
AND (NVL (ln_qual_count, 0) = 0)
THEN
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;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Payment Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_hq_payment_term;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Freight Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_hq_freight_term;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Shipping Method’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := lc_hq_ship_method;
ELSIF rec_modifier_cur.header_qualifier_attribute = ‘Ship From’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_hq_org_id;
ELSIF rec_modifier_cur.header_qualifier_attribute =
‘Distributor Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_hq_customer_id;
END IF;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
— l_modifier_list_rec.list_type_code := lc_list_type_code;
— l_modifier_list_rec.operation := qp_globals.g_opr_update;
— l_modifiers_tbl (1).operation := qp_globals.g_opr_update;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
l_qualifiers_tbl (1).list_line_id := -1;
l_qualifiers_tbl (1).qualifier_context := lc_hqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lc_hqual_mapping_column;
l_qualifiers_tbl (1).qualifier_precedence := lc_hqual_precedence;
l_qualifiers_tbl (1).excluder_flag :=
rec_modifier_cur.include_exclude;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
l_qualifiers_tbl (1).comparison_operator_code :=
rec_modifier_cur.OPERATOR;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
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;
END IF;
————-fOR lINE qUALIFIER
IF ( rec_modifier_cur.linequalifier_attribute IS NOT NULL
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
)
THEN
BEGIN
SELECT COUNT (qualifier_id)
INTO ln_qual_count
FROM qp_qualifiers
WHERE list_header_id = p_list_header_id
AND (list_line_id = ln_list_line_id)
AND qualifier_context = lc_lqual_context_name
AND qualifier_attribute = lc_lqual_mapping_column
AND ( qualifier_attr_value = lc_cust_account_id
OR qualifier_attr_value = ln_transaction_type_id
OR qualifier_attr_value =
NVL (rec_modifier_cur.linequalifier_value,
rec_modifier_cur.header_qualifier_value
)
);
EXCEPTION
WHEN OTHERS
THEN
ln_qual_count := NULL;
END;
BEGIN
SELECT qualifier_id, list_line_id
INTO ln_qualifier_id, ln_qual_list_line_id
FROM qp_qualifiers
WHERE list_header_id = p_list_header_id
AND (list_line_id = ln_list_line_id)
AND qualifier_context = lc_lqual_context_name
AND qualifier_attribute = lc_lqual_mapping_column
AND ( qualifier_attr_value = lc_cust_account_id
OR qualifier_attr_value = ln_transaction_type_id
OR qualifier_attr_value =
rec_modifier_cur.linequalifier_value
);
EXCEPTION
WHEN OTHERS
THEN
ln_qualifier_id := NULL;
ln_qual_list_line_id := NULL;
END;
IF (NVL (ln_qual_count, 0) = 0)
THEN
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_line_id := ln_list_line_id;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
ELSE
IF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Customer Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_cust_account_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Order Type’
THEN
DEBUG (‘Order Type’);
l_qualifiers_tbl (1).qualifier_attr_value :=
ln_transaction_type_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Payment Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_lq_payment_term;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Freight Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_lq_freight_term;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Distributor Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
ln_lq_customer_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Shipping Method’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_lq_ship_method;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Ship From’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_lq_org_id;
END IF;
l_qualifiers_tbl (1).operation := qp_globals.g_opr_update;
— l_modifiers_tbl (1).list_line_id := ln_list_line_id;
— l_modifiers_tbl (1).list_header_id := p_list_header_id;
— l_modifier_list_rec.list_type_code := lc_list_type_code;
— l_modifier_list_rec.list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_line_id := ln_qual_list_line_id;
l_qualifiers_tbl (1).qualifier_id := ln_qualifier_id;
l_qualifiers_tbl (1).qualifier_context := lc_lqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lc_lqual_mapping_column;
l_qualifiers_tbl (1).qualifier_precedence :=
lc_lqual_precedence;
l_qualifiers_tbl (1).excluder_flag :=
rec_modifier_cur.include_exclude;
l_qualifiers_tbl (1).comparison_operator_code := ‘=’;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
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_modifier_list_rec.operation := qp_globals.g_opr_update;
— l_modifiers_tbl (1).operation := qp_globals.g_opr_update;
— l_modifier_list_rec.operation := qp_globals.g_opr_update;
— l_pricing_attr_tbl (1).operation := qp_globals.g_opr_update;
END IF;
END IF;
IF ( rec_modifier_cur.linequalifier_attribute IS NOT NULL
OR UPPER (rec_modifier_cur.qualifier_type) = ‘LINE’
–AND l_qualifiers_tbl (1).operation = qp_globals.g_opr_create
)
AND (NVL (ln_qual_count, 0) = 0)
THEN
IF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Customer Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_cust_account_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Order Type’
THEN
DEBUG (‘Order Type’);
l_qualifiers_tbl (1).qualifier_attr_value :=
ln_transaction_type_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Payment Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_lq_payment_term;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Freight Terms’
THEN
l_qualifiers_tbl (1).qualifier_attr_value :=
lc_lq_freight_term;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Shipping Method’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := lc_lq_ship_method;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Distributor Name’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_lq_customer_id;
ELSIF NVL (rec_modifier_cur.linequalifier_attribute,
rec_modifier_cur.header_qualifier_attribute
) = ‘Ship From’
THEN
l_qualifiers_tbl (1).qualifier_attr_value := ln_lq_org_id;
END IF;
DEBUG ( p_list_header_id
|| ‘ ‘
|| -1
|| ‘ ‘
|| lc_list_type_code
|| ‘ ‘
|| lc_lqual_context_name
|| ‘ ‘
|| lc_lqual_mapping_column
|| ‘ ‘
|| lc_lqual_precedence
|| ‘ ‘
|| rec_modifier_cur.include_exclude
|| ‘ ‘
|| rec_modifier_cur.linequalifier_start_date
);
l_qualifiers_tbl (1).operation := qp_globals.g_opr_create;
l_qualifiers_tbl (1).list_header_id := p_list_header_id;
l_qualifiers_tbl (1).list_line_id := ln_list_line_id;
l_qualifiers_tbl (1).qualifier_context := lc_lqual_context_name;
l_qualifiers_tbl (1).qualifier_attribute :=
lc_lqual_mapping_column;
l_qualifiers_tbl (1).qualifier_precedence := lc_lqual_precedence;
l_qualifiers_tbl (1).excluder_flag :=
rec_modifier_cur.include_exclude;
l_qualifiers_tbl (1).comparison_operator_code := ‘=’;
l_qualifiers_tbl (1).qualifier_grouping_no := -1;
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;
END IF;
COMMIT;
–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_empty_modifiers_tbl,
p_qualifiers_tbl => l_qualifiers_tbl,
p_pricing_attr_tbl => l_empty_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
);
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;
DEBUG (‘err msg ‘ || ‘is: ‘ || x_msg_data);
DEBUG (‘*********************************************’);
COMMIT;
END LOOP;
END;
END;
