function fran_price_qualifier (p_customer_id varchar2, p_order_type_id varchar2, p_price_list_id varchar2) return number as v_match boolean: = true; v_list_header_id number; v_list_line_id number; v_order_type_id varchar2 (30); v_price_list_id varchar2 (30); t_customer_name varchar2 (200); t_customer_class varchar2 (200) ; t_sales_channel varchar2 (100); v_group_no_header number; v_group_no_line number; v_context varchar2 (200); v_attribute varchar2 (200); v_comparision varchar2 (30); v_value varchar2 (200); v_discount number: = 0; discount number: = 0; v_temp number: = 0; v_arithmetic varchar2 (20); v_count number; v_out number; cursor c_price is select qb.list_header_id from qp.qp_list_headers_b qb where qb.active_flag = 'Y' and qb.list_type_code = 'DLT' and (qb.start_date_active Is null or qb.start_date_active <= sysdate) and (qb.end_date_active is null or qb.end_date_active> = sysdate); CURSOR C_GROUP_NO_HEADER IS SELECT DI stinct qq.qualifier_grouping_no from qp.qp_qualifiers qq where qq.list_header_id = v_list_header_id and qq.list_line_id = -1 and qq.active_flag = 'Y' and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate); cursor c_group_header is select qq.qualifier_context, qq.qualifier_attribute, qq.qualifier_attr_value, qq.comparison_operator_code from qp.qp_qualifiers qq where qq.list_header_id = v_list_header_id and qq.list_line_id = -1 and ( QQ.Start_Date_Active is null or qq.start_date_active <=
sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate) and qq.active_flag = 'Y' and qq.qualifier_grouping_no = v_group_no_header; cursor c_line_id is select ql.list_line_id, ql.operand, ql.arithmetic_operator from qp. qp_list_lines ql where ql.list_header_id = v_list_header_id and (ql.start_date_active is null or ql.start_date_active <= sysdate) and (ql.end_date_active is null or ql.end_date_active> = sysdate) and ql.modifier_level_code = 'LINE' and ql.list_line_type_code = 'DIS'; cursor c_group_no_line is select distinct qq.qualifier_grouping_no from qp.qp_qualifiers qq where qq.list_header_id = v_list_header_id and qq.list_line_id = v_list_line_id and qq.active_flag = 'Y' and (qq.start_date_active is null or qq.start_date_active < = sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate); cursor c_group_line is select qq.qualifier_context, qq.qualifier_attribute, qq.qualifier_attr_value, qq.comparison_operator_code from q p.qp_qualifiers qq where qq.list_header_id = v_list_header_id and qq.list_line_id = v_list_line_id and qq.active_flag = 'Y' and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active > = sysdate) and qq.qualifier_grouping_no = v_group_no_line; becom becom dual; select p_price_list_id irl;
select to_char (t.customer_id), t.sales_channel_code, t.customer_class_code into t_customer_name, t_customer_class, t_sales_channel from apps.ra_customers t where t.status = 'A' and t.customer_id = to_number (p_customer_id); exception when others then discount: = 0; RETURN discount; end;
open c_price; loop fetch c_price into v_list_header_id; exit when c_price% notfound; select count (*) into v_count from qp.qp_list_lines ql where ql.list_header_id = v_list_header_id and (ql.start_date_active is null or ql.start_date_active <= sysdate) and ( ql.end_date_active is null or ql.end_date_active> = sysdate) and ql.modifier_level_code = 'LINE'; if v_count = 0 then v_discount: = 0; else select count (*) into v_count from qp.qp_qualifiers qq where qq.list_header_id = v_list_header_id and qq.list_line_id = -1 and qq.list_type_code = 'DLT' and qq.active_flag = 'Y' and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active > = sysdate); / * =================================================== ============================== * / if v_count> 0 THEN open c_group_no_header; loop fetch c_group_no_header into v_group_no_header; exit when c_group_no_header% notfound; open c_group_header; loop fetch c_group_header into v_context, v_attribute, v_value, v_comparision; exit when c_group_header% notfound; if v_comparision = '='
then select count (*) into v_count from qp.qp_qualifiers qq where 1 = 1 and qq.list_header_id = v_list_header_id and qq.list_line_id = -1 and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate) and qq.active_flag = 'Y' and qq.qualifier_grouping_no = v_group_no_header and qq.qualifier_context = v_context and qq.qualifier_attribute = v_attribute and v_value = decode (qq.qualifier_context, 'ORDER', decode (QQ.qualifier_attribute, 'Qualifier_ttribute9', v_order_type_id, '-99),' modlist ', decode (QQ.Qualifier_attribute,' Qualifier_Attribute4 ', V_Price_List_ID,' -99 '),' Customer '
, Decode (qq.qualifier_attribute, 'QUALIFIER_ATTRIBUTE2', t_customer_name, 'QUALIFIER_ATTRIBUTE1', t_customer_class, 'QUALIFIER_ATTRIBUTE13', t_sales_channel, '-99'), '-99'); else select count (*) into v_count from qp.qp_qualifiers qq Where 1 = 1 and qq.list_header_id = v_list_header_id and qq.list_line_id = -1 and (QQ.Start_Date_Active Is Null or QQ.Start_Date_Active <= sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate) and qq.active_flag = 'Y' and qq.qualifier_grouping_no = v_group_no_header and qq.comparison_operator_code = 'NOT =' and qq.qualifier_context = v_context and qq.qualifier_attribute = v_attribute And v_value! = decode (QQ.qualifier_Context, 'Order'
, Decode (qq.qualifier_attribute, 'QUALIFIER_ATTRIBUTE9', v_order_type_id, '-99'), 'MODLIST', decode (qq.qualifier_attribute, 'QUALIFIER_ATTRIBUTE4', v_price_list_id, '-99'), 'CUSTOMER', decode (qq.qualifier_attribute , 'Qualifier_Attribute2', T_Customer_Name, 'Qualifier_Attribute1', T_Customer_Class, 'Qualifier_Attribute13', T_Sales_Channel, '-99),' -99 '); end if; if v_count = 0 THEN V_MATCH: = FALSE; EXIT; Else V_Match: = true; end if; end loop; close c_group_header; if v_match = TRUE THEN EXIT;
End if; end loop; close c_group_no_header; Else V_match: = true; end if; / * if v_match = true dam = = true dam ========================================================================================================================================== ============================================================================================================================================================================================================= ======= * / if v_match = true then open c_line_id; loop fetch c_line_id into v_list_line_id, v_temp, v_arithmetic; exit when c_line_id% notfound; select count (*) into v_count from qp.qp_qualifiers qq where 1 = 1 and qq.list_header_id = v_list_header_id and qq.list_line_id = v_list_line_id and qq.list_type_code = 'DLT' and qq.active_flag = 'Y' and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or QQ.End_date_Active> = sysdate); if v_count = 0 THEN V_match: = true; else open c_group_no_Line; loop fetch c_group_no_line into v_group_no_line; exit when c_group_no_line% notfound; Open C_Group_Line;
loop fetch c_group_line into v_context, v_attribute, v_value, v_comparision; exit when c_group_line% notfound; if v_comparision = '=' then select count (*) into v_count from qp.qp_qualifiers qq where 1 = 1 and qq.list_header_id = v_list_header_id and qq. list_line_id = v_list_line_id and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate) and qq.active_flag = 'Y' and qq.qualifier_grouping_no = v_group_no_line and qq. Qualifier_context = v_context and qq.qualifier_attribute = v_attribute and qq.qualifier_attr_value = decode (QQ.qualifier_Context, 'Order', Decode (Qq.qualifier_Attribute, 'Qualifier_attribute9', V_Order_Type_ID, '-99'), 'MODLIST', DECODE (QQ.qualifier_attribute, 'Qualifier_Attribute4'
, V_price_list_id, '-99'), 'CUSTOMER', decode (qq.qualifier_attribute, 'QUALIFIER_ATTRIBUTE2', t_customer_name, 'QUALIFIER_ATTRIBUTE1', t_customer_class, 'QUALIFIER_ATTRIBUTE13', t_sales_channel, '-99'), '-99'); else SELECT Count (*) Into v_count from qp.qp_qualifiers qq where 1 = 1 and qq.list_header_id = v_list_header_id and qq.list_line_id = v_list_line_id and (qq.start_date_active is null or qq.start_date_active <= sysdate) and (qq.end_date_active is null or qq.end_date_active> = sysdate) And QQ.Active_flag = 'y' and qq.qualifier_grouping_no =
v_group_no_line and qq.qualifier_context = v_context and qq.qualifier_attribute = v_attribute and qq.qualifier_attr_value! = decode (qq.qualifier_context, 'ORDER', decode (qq.qualifier_attribute, 'QUALIFIER_ATTRIBUTE9', v_order_type_id, '-99'), 'MODLIST' , decode (QQ.Qualifier_attribute, 'Qualifier_Attribute4', v_price_list_id, '-99),' Customer ', Decode (QQ.Qualifier_attribute,' Qualifier_Attribute2 ', T_Customer_Name,' Qualifier_Attribute1 ', T_Customer_Class,' Qualifier_Attribute 13 '
, t_sales_channel, '-99),' -99 '); end if; if; = false; exit; Else v_match: = true; end if; end loop; close c_Group_Line; if v_match = true kil EXIT; END loop; close c_group_no_line; endiff; IF v_match = true dam = - 3; --Exit; Else V_out: = - 4; v_temp: = 0; end if; v_discount: = v_discount v_temp;
end loop; close c_line_id; else v_discount: = 0; end if; end if; discount: = discount v_discount; end loop; close c_price; return discount; exception when others then discount: = 0; return discount; end;
