QP_MODIFIERS_PUB.PROCESS_MODIFIERS – Qualifiers

Qualifiers on header level can be create in one API call but to create a qualifier on modifier level, you need to have a second API call, using the return values from the first API:

DECLARE
– copyright 2008 – www.thomaslundqvist.dk
P_API_VERSION_NUMBER NUMBER := 1;
P_INIT_MSG_LIST VARCHAR2(2000) := FND_API.G_TRUE;
P_RETURN_VALUES VARCHAR2(2000) := FND_API.G_TRUE;
P_COMMIT VARCHAR2(2000) := FND_API.G_TRUE;
X_RETURN_STATUS VARCHAR2(2000);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
P_MODIFIER_LIST_REC APPS.QP_MODIFIERS_PUB.MODIFIER_LIST_REC_TYPE;
P_MODIFIER_LIST_VAL_REC APPS.QP_MODIFIERS_PUB.MODIFIER_LIST_VAL_REC_TYPE;
P_MODIFIERS_TBL APPS.QP_MODIFIERS_PUB.MODIFIERS_TBL_TYPE;
P_MODIFIERS_VAL_TBL APPS.QP_MODIFIERS_PUB.MODIFIERS_VAL_TBL_TYPE;

P_QUALIFIERS_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_TBL_TYPE;
P_QUALIFIERS_VAL_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_VAL_TBL_TYPE;
P_QUALIFIERS2_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_TBL_TYPE;
P_QUALIFIERS2_VAL_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_VAL_TBL_TYPE;
P_PRICING_ATTR_TBL APPS.QP_MODIFIERS_PUB.PRICING_ATTR_TBL_TYPE;
P_PRICING_ATTR_VAL_TBL APPS.QP_MODIFIERS_PUB.PRICING_ATTR_VAL_TBL_TYPE;
X_MODIFIER_LIST_REC APPS.QP_MODIFIERS_PUB.MODIFIER_LIST_REC_TYPE;
X_MODIFIER_LIST_VAL_REC APPS.QP_MODIFIERS_PUB.MODIFIER_LIST_VAL_REC_TYPE;
X_MODIFIERS_TBL APPS.QP_MODIFIERS_PUB.MODIFIERS_TBL_TYPE;
X_MODIFIERS_VAL_TBL APPS.QP_MODIFIERS_PUB.MODIFIERS_VAL_TBL_TYPE;
X_QUALIFIERS_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_TBL_TYPE;
X_QUALIFIERS_VAL_TBL APPS.QP_QUALIFIER_RULES_PUB.QUALIFIERS_VAL_TBL_TYPE;
X_PRICING_ATTR_TBL APPS.QP_MODIFIERS_PUB.PRICING_ATTR_TBL_TYPE;
X_PRICING_ATTR_VAL_TBL APPS.QP_MODIFIERS_PUB.PRICING_ATTR_VAL_TBL_TYPE;
P_CUSTOMER varchar2(150);
i1 number := 1;
i2 number := 1;
i3 number := 1;
i4 number := 1;
i5 number := 1;
i6 number := 1;
TYPE p_Rec_Type IS RECORD
(head_number VARCHAR2(240) := FND_API.G_MISS_CHAR,
prod_value VARCHAR2(240) := FND_API.G_MISS_CHAR,
qual_value VARCHAR2(240) := FND_API.G_MISS_CHAR,
head_qual_cust_ref varchar2(240) := fnd_api.g_miss_char,
line_qual_cust_ref varchar2(240) := fnd_api.g_miss_char);

p_precedence number;

TYPE p_Tbl_Type IS TABLE OF p_Rec_Type INDEX BY BINARY_INTEGER;
p_att p_Tbl_Type;
BEGIN

:x_status := ”;
:x_msg_data1 := ”;
:x_msg_data2 := ”;
OE_MSG_PUB.Initialize;

–$MAPPING

begin

if P_MODIFIER_LIST_REC.NAME <> fnd_api.g_miss_char
and P_MODIFIER_LIST_REC.VERSION_NO <> fnd_api.g_miss_char
and P_MODIFIER_LIST_REC.OPERATION = ‘UPDATE’
and P_MODIFIER_LIST_REC.LIST_HEADER_ID = fnd_api.g_miss_num
then

begin
select LIST_HEADER_ID
into P_MODIFIER_LIST_REC.LIST_HEADER_ID
from QP_LIST_HEADERS_VL qlh
where qlh.name = P_MODIFIER_LIST_REC.NAME
and qlh.VERSION_NO = P_MODIFIER_LIST_REC.VERSION_NO;

exception when others then
:x_status := :x_status || ‘E1′;
:x_msg_data2 := ‘Unable to find list header ‘ || P_MODIFIER_LIST_REC.NAME;
end;
end if;

for i in 1..i5 loop
if p_att(i).prod_value <> fnd_api.g_miss_char then
if P_PRICING_ATTR_TBL(i).PRODUCT_ATTR_VALUE = fnd_api.g_miss_char
and P_PRICING_ATTR_TBL(I).PRODUCT_ATTRIBUTE = ‘PRICING_ATTRIBUTE1′ then
begin
select inventory_item_id
into P_PRICING_ATTR_TBL(i).PRODUCT_ATTR_VALUE
from mtl_system_items msi
where segment1 = p_att(i).prod_value
and organization_id = 108;

exception when others then
:x_status := :x_status || ‘E2′;
:x_msg_data2 := ‘Unable to find item ‘ || P_att(i).prod_value;
end;
end if;
end if;
end loop;

for i in 1..i5 loop
if p_att(i).prod_value <> fnd_api.g_miss_char then
if P_PRICING_ATTR_TBL(i).PRODUCT_ATTR_VALUE = fnd_api.g_miss_char
and P_PRICING_ATTR_TBL(i).PRODUCT_ATTRIBUTE = ‘PRICING_ATTRIBUTE2′ then
begin
select category_id
into P_PRICING_ATTR_TBL(i).PRODUCT_ATTR_VALUE
from MTL_CATEGORIES_V
where 1=1
and structure_id = 50220
and segment1 = p_att(i).prod_value;
exception when others then
:x_status := :x_status || ‘E3′;
:x_msg_data2 := ‘Unable to find item category ‘ || P_att(i).prod_value;
end;
end if;
end if;
end loop;

for i in 1..i3 loop
if p_att(i).qual_value <> fnd_api.g_miss_char then
if P_QUALIFIERS_TBL(i).QUALIFIER_ATTR_VALUE = fnd_api.g_miss_char
and P_QUALIFIERS_TBL(i).QUALIFIER_ATTRIBUTE = ‘QUALIFIER_ATTRIBUTE17′ then
begin

select hps.party_site_id
into P_QUALIFIERS_TBL(i).QUALIFIER_ATTR_VALUE
from HZ_PARTY_SITES HPS
where 1=1
and HPS.PARTY_SITE_NUMBER = p_att(i).qual_value;

exception when others then
:x_status := :x_status || ‘E4′;
:x_msg_data2 := ‘Unable to find customer ‘ || P_att(i).qual_value;
end;
end if;
end if;
end loop;

for i in 1..i1 loop
if P_PRICING_ATTR_TBL(I).OPERATION = ‘UPDATE’
and P_MODIFIERS_TBL(I).OPERATION = ‘UPDATE’
and P_MODIFIERS_TBL(I).LIST_LINE_ID = fnd_api.g_miss_num
and P_PRICING_ATTR_TBL(I).PRICING_ATTRIBUTE_ID = fnd_api.g_miss_num
then

begin

select
qpa.LIST_LINE_ID,
qpa.PRICING_ATTRIBUTE_ID
into
P_MODIFIERS_TBL(I1).LIST_LINE_ID,
P_PRICING_ATTR_TBL(I).PRICING_ATTRIBUTE_ID
from
QP_PRICING_ATTRIBUTES qpa,
QP_LIST_LINES qll
where rownum = 1
and qll.list_header_id = P_MODIFIER_LIST_REC.LIST_HEADER_ID
and qll.START_DATE_ACTIVE = P_MODIFIERS_TBL(I).START_DATE_ACTIVE
and qpa.list_line_id = qll.list_line_id
and qpa.excluder_flag = ‘N’
and qpa.PRODUCT_ATTRIBUTE = P_PRICING_ATTR_TBL(I).PRODUCT_ATTRIBUTE
and qpa.PRODUCT_ATTRIBUTE_CONTEXT = P_PRICING_ATTR_TBL(I).PRODUCT_ATTRIBUTE_CONTEXT
and qpa.PRODUCT_ATTR_VALUE = P_PRICING_ATTR_TBL(I).PRODUCT_ATTR_VALUE;

exception when others then
:x_status := :x_status || ‘E5′;
:x_msg_data2 := ‘Unable to find list line and pricing att on list_header_id ‘ ||

P_MODIFIER_LIST_REC.LIST_HEADER_ID;
end;
end if;
end loop;

for i in 1..i3 loop
if p_att(i).head_qual_cust_ref <> fnd_api.g_miss_char then
if P_QUALIFIERS_TBL(i).QUALIFIER_ATTR_VALUE = fnd_api.g_miss_char
and P_QUALIFIERS_TBL(i).QUALIFIER_ATTRIBUTE = ‘QUALIFIER_ATTRIBUTE2′ then
begin

select hca.CUST_ACCOUNT_ID
into P_QUALIFIERS_TBL(i).QUALIFIER_ATTR_VALUE
from HZ_CUST_ACCOUNTS Hca
where 1=1
and HCA.ORIG_SYSTEM_REFERENCE = p_att(i).head_qual_cust_ref;

exception when others then
:x_status := :x_status || ‘E6′;
:x_msg_data2 := ‘Unable to find customer ref.’ || p_att(i).head_qual_cust_ref;
end;
end if;
end if;
end loop;

for i in 1..i3 loop
if p_att(i).line_qual_cust_ref <> fnd_api.g_miss_char then
if P_QUALIFIERS2_TBL(i).QUALIFIER_ATTR_VALUE = fnd_api.g_miss_char
and P_QUALIFIERS2_TBL(i).QUALIFIER_ATTRIBUTE = ‘QUALIFIER_ATTRIBUTE31′ then
begin

select hca.CUST_ACCOUNT_ID
into P_QUALIFIERS2_TBL(i).QUALIFIER_ATTR_VALUE
from HZ_CUST_ACCOUNTS Hca
where 1=1
and HCA.ORIG_SYSTEM_REFERENCE = p_att(i).line_qual_cust_ref;

exception when others then
:x_status := :x_status || ‘E7′;
:x_msg_data2 := ‘Unable to find customer ref.’ || p_att(i).line_qual_cust_ref;
end;
end if;
end if;
end loop;

exception when others then null;
end;

if :x_status is null then

dbms_output.put_line(’QP_MODIFIERS_PUB.PROCESS_MODIFIERS’ );

QP_MODIFIERS_PUB.PROCESS_MODIFIERS
(P_API_VERSION_NUMBER => P_API_VERSION_NUMBER
,P_INIT_MSG_LIST => P_INIT_MSG_LIST
,P_RETURN_VALUES => P_RETURN_VALUES
,P_COMMIT => P_COMMIT
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
,P_MODIFIER_LIST_REC => P_MODIFIER_LIST_REC
,P_MODIFIER_LIST_VAL_REC => P_MODIFIER_LIST_VAL_REC
,P_MODIFIERS_TBL => P_MODIFIERS_TBL
,P_MODIFIERS_VAL_TBL => P_MODIFIERS_VAL_TBL
,P_QUALIFIERS_TBL => P_QUALIFIERS_TBL
,P_QUALIFIERS_VAL_TBL => P_QUALIFIERS_VAL_TBL
,P_PRICING_ATTR_TBL => P_PRICING_ATTR_TBL
,P_PRICING_ATTR_VAL_TBL => P_PRICING_ATTR_VAL_TBL
,X_MODIFIER_LIST_REC => X_MODIFIER_LIST_REC
,X_MODIFIER_LIST_VAL_REC => X_MODIFIER_LIST_VAL_REC
,X_MODIFIERS_TBL => X_MODIFIERS_TBL
,X_MODIFIERS_VAL_TBL => X_MODIFIERS_VAL_TBL
,X_QUALIFIERS_TBL => X_QUALIFIERS_TBL
,X_QUALIFIERS_VAL_TBL => X_QUALIFIERS_VAL_TBL
,X_PRICING_ATTR_TBL => X_PRICING_ATTR_TBL
,X_PRICING_ATTR_VAL_TBL => X_PRICING_ATTR_VAL_TBL
);
–dbms_output.put_line(’X_MODIFIERS_TBL(1).list_line_id ‘ || X_MODIFIERS_TBL(1).list_line_id);
:x_status := :x_status || x_return_status;
for i in 1 .. X_MSG_COUNT loop
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
end loop;

:x_msg_data1 := substr(x_msg_data,1,125);

if P_QUALIFIERS2_TBL(1).OPERATION = ‘CREATE’ then

P_MODIFIER_LIST_VAL_REC := QP_MODIFIERS_PUB.G_MISS_MODIFIER_LIST_VAL_REC;
p_modifiers_tbl := QP_MODIFIERS_PUB.G_MISS_MODIFIERS_TBL;
p_modifiers_val_tbl := QP_MODIFIERS_PUB.G_MISS_MODIFIERS_VAL_TBL;
P_PRICING_ATTR_TBL := QP_MODIFIERS_PUB.G_MISS_pricing_attr_TBL;
P_PRICING_ATTR_VAL_TBL := QP_MODIFIERS_PUB.G_MISS_PRICING_attr_VAL_TBL;

P_MODIFIER_LIST_REC.OPERATION := trim(’UPDATE ‘);
P_MODIFIER_LIST_REC.list_header_id := X_MODIFIER_LIST_REC.list_header_id;

p_modifiers_tbl(1).OPERATION := trim(’UPDATE ‘);
P_MODIFIERS_TBL(1).list_line_id := X_MODIFIERS_TBL(1).list_line_id;

QP_MODIFIERS_PUB.PROCESS_MODIFIERS
(P_API_VERSION_NUMBER => P_API_VERSION_NUMBER
,P_INIT_MSG_LIST => P_INIT_MSG_LIST
,P_RETURN_VALUES => P_RETURN_VALUES
,P_COMMIT => P_COMMIT
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
,P_MODIFIER_LIST_REC => P_MODIFIER_LIST_REC
,P_MODIFIER_LIST_VAL_REC => P_MODIFIER_LIST_VAL_REC
,P_MODIFIERS_TBL => P_MODIFIERS_TBL
,P_MODIFIERS_VAL_TBL => P_MODIFIERS_VAL_TBL
,P_QUALIFIERS_TBL => P_QUALIFIERS2_TBL
,P_QUALIFIERS_VAL_TBL => P_QUALIFIERS2_VAL_TBL
,P_PRICING_ATTR_TBL => P_PRICING_ATTR_TBL
,P_PRICING_ATTR_VAL_TBL => P_PRICING_ATTR_VAL_TBL
,X_MODIFIER_LIST_REC => X_MODIFIER_LIST_REC
,X_MODIFIER_LIST_VAL_REC => X_MODIFIER_LIST_VAL_REC
,X_MODIFIERS_TBL => X_MODIFIERS_TBL
,X_MODIFIERS_VAL_TBL => X_MODIFIERS_VAL_TBL
,X_QUALIFIERS_TBL => X_QUALIFIERS_TBL
,X_QUALIFIERS_VAL_TBL => X_QUALIFIERS_VAL_TBL
,X_PRICING_ATTR_TBL => X_PRICING_ATTR_TBL
,X_PRICING_ATTR_VAL_TBL => X_PRICING_ATTR_VAL_TBL
);

:x_status := :x_status || x_return_status;
for i in 1 .. X_MSG_COUNT loop
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
end loop;

:x_msg_data3 := substr(x_msg_data,1,125);

end if;
end if;

–$ROW_COMMIT

EXCEPTION WHEN OTHERS THEN
:x_status := :x_status || ‘E’;
:x_msg_data1 := substr(SQLERRM,1,125);
END;
/

Download template

This entry was posted in API, Templates Development. Bookmark the permalink.