Developing the Pricelist Modifiers Template

The template is calling the QP_MODIFIERS_PUB.PROCESS_MODIFIERS API. This API can create pricelist modifiers, modifier lines and qualifiers. The modifier header is a record structure, while the rest is plsql tables. The API works very much like the form interface, so it is a good idea to understand that, before working with the API. I had to build a sheet for updating existing lines, and it took some time to figure out how that is working. The final sheet is now generating scripts from a xls with only header_id, line_id and the end_active_date (thats the field we need to update). The generated script looks like this:

-- @"c:\temp\modifiers115.sql"
-- Excel for Oracle Applications - 3.0.0.175
-- Copyright 2008 (C) - Thomas Lundqvist Consulting
-- based on load to database: dev
alter session set nls_date_format='dd-mon-yyyy';
begin fnd_global.apps_initialize(8854,50296,661,0); end;
/
var x_status varchar2(125)
var x_msg_data1 varchar2(125)
var x_msg_data2 varchar2(125)
var x_msg_data3 varchar2(125)
var x_msg_data4 varchar2(125)
var x_msg_data5 varchar2(125)
var x_msg_data6 varchar2(125)
var x_msg_data7 varchar2(125)
var x_msg_data8 varchar2(125)
var x_msg_data1a varchar2(125)
set def off
-- ********************************
-- ROW 2
-- ********************************
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_FALSE;
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_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;
i2 number;
i3 number;
i4 number;
i5 number;
i6 number;
P_header_name varchar2(150);
BEGIN
OE_MSG_PUB.Initialize;
:x_status := '';
--$MAPPING
i1 := 1;                                                    -- default value
P_INIT_MSG_LIST := FND_API.G_TRUE;                          -- default value
P_RETURN_VALUES := FND_API.G_FALSE;                         -- default value
P_COMMIT := FND_API.G_FALSE;                                -- default value
P_MODIFIER_LIST_REC.LIST_HEADER_ID := 213416;               -- mapped col 5
P_MODIFIER_LIST_REC.OPERATION := 'UPDATE';                  -- default value
P_MODIFIERS_TBL(i1).END_DATE_ACTIVE := to_date('01-nov-2008','dd-mon-yyyy');-- mapped col 6
P_MODIFIERS_TBL(i1).LIST_LINE_ID := 2259958;                -- mapped col 4
P_MODIFIERS_TBL(i1).OPERATION := 'UPDATE';                  -- default value
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
);
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_status := :x_status || x_return_status;
:x_msg_data1 := substr(x_msg_data,1,125);
--$ROW_COMMIT
commit;
EXCEPTION WHEN OTHERS THEN
:x_status := 'E';
:x_msg_data1 := substr(SQLERRM,1,125);
END;
/
exec dbms_output.put_line('row 2 completed ' || to_char(sysdate,'yy-mm-dd hh24:mi:ss') || ' x_status = ' || :x_status || ' ' || :x_msg_data1);
exec dbms_output.put_line('--$ROW_STATUS 2 , ' || :x_status || ', ' || :x_msg_data1);
-- :x_status = S
-- row 2 completed 16-Oct-08 21:22:12

The API can handle multi line load, and so can the xls4oracle tool – and if you are going to load multiple lines to the same header, that will improve the load performance a lot.

The generated Mapping section:

--$MAPPING
i1 := 1;                                                    -- default value
P_INIT_MSG_LIST := FND_API.G_TRUE;                          -- default value
P_RETURN_VALUES := FND_API.G_FALSE;                         -- default value
P_COMMIT := FND_API.G_FALSE;                                -- default value
P_MODIFIER_LIST_REC.LIST_HEADER_ID := 213416;               -- mapped col 5
P_MODIFIER_LIST_REC.OPERATION := 'UPDATE';                  -- default value
P_MODIFIERS_TBL(i1).END_DATE_ACTIVE := to_date('01-nov-2008','dd-mon-yyyy');-- mapped col 6
P_MODIFIERS_TBL(i1).LIST_LINE_ID := 2259958;                -- mapped col 4
P_MODIFIERS_TBL(i1).OPERATION := 'UPDATE';                  -- default value

is basicly just setting the header and line id’s and setting the operation on header and line to ‘UPDATE’. Finally the end_date_active can be set. If you want to create headers and lines – just set operation to ‘CREATE’ and add (map) the missing fields. If you are in doubt – create the modifier using the forms interface and check the values using examine. If you get errors like: “ORA-01426: numeric overflow in Package QP_Modifiers_PVT Procedure Process_Modifiers” or “ORA-06503: PL/SQL: Function returned without value in Package QP_Modifiers_PVT Procedure Modifier_List” – there is something wrong with the header_id and/or line_id’s.

Excel For Oracle Applications

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