Advance Pricing -- Formula and Custom Price Function

在測試MST轉播計價時
發現Price List除了用item number每一筆設定單價外
可以用Formula與All item的方式
一次就給所有的item一個function當價格的邏輯
以下為相關的步驟
(雖然後來才知道MST inter-company有另外的設定
但這部份可以應用在非 inter-company的範圍)

1. 新增Pricing Formula:

* Formula type設定為"Function"
它其實會去執行 QP_CUSTOM.GET_CUSTOM_PRICE這個程式

2. 建立Get Custom Price的package body:

*須特別注意的是Formula id
首先要找出剛剛定義的Formula它的id是多少
透過if block定義該Formula所對應的程式

當有多個Formula時..也可以由Formula id的區分同時定義在這個Function中

CREATE or REPLACE PACKAGE BODY QP_CUSTOM AS
FUNCTION Get_Custom_Price (p_price_formula_id IN NUMBER,
p_list_price IN NUMBER,
p_price_effective_date IN DATE,
p_req_line_attrs_tbl IN QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL)
RETURN NUMBER
is
v_request_item varchar2(240);
BEGIN
if p_price_formula_id = 10045 then
for i in 1..p_req_line_attrs_tbl.count loop
if p_req_line_attrs_tbl(i).attribute_type = 'PRODUCT' and p_req_line_attrs_tbl(i).context = 'ITEM' and p_req_line_attrs_tbl(i).attribute = 'PRICING_ATTRIBUTE1' then
v_request_item := p_req_line_attrs_tbl(i).value;
end if;
end loop;
return to_number(v_request_item);
/* get cost from item id in v_request_item*/
end if;
end get_custom_price;
END QP_CUSTOM;
/
commit
/

3. 執行Concurrent去重建Formula Package:

* 執行"Build Formula Package".

4. 建立Price List:

* 用All item的方法對每一個UOM都指定Dynamic Formulas為剛剛設定的Formula.

5. 執行Concurrent去重建 Attribute Mapping:

* 執行 "Build Attribute Mapping Rules".

*** IMPORTANT ****

1. 靈異現象
當一開始作上面的設定時
會發現All item+Formula是失效的..
Trace程式發現Get_Custom_Price裡面並沒有抓到PRICE_ATTRIBUTE1 (item id)
而只有抓到PRICE_ATTRIBUTE3(All item)
這樣無法依每一個item去做價格的邏輯

但如果再Price List上給一個指定的料號
一樣設其Dynamic Formulas為剛剛設定的Formula
重跑一次..
Get_Custom_Price裡面就可以抓到PRICE_ATTRIBUTE1 (item id)
而且是所有item的item id

因此在這個類似Bug的狀況下
我會先加一次指定的item到Price List上
確認所有item都可以正確運作後
再移除這個指定的item...使Price List上仍只有All item的簡潔設定

2. MTL的部份
要改的不是Get_Custom_Price
而是MTL_INTERCOMPANY_INVOICE.get_transfer_price

Interface -[QP] Price List Interface

以下程式是修改自 patch 4900462.
然而感覺起來QP裡面還有Bug
因為直接於系統上建立的Price List
居然不存在ORIG_SYS_HEADER_REF的欄位
因此如果要以程式操控Price List
一開始就要以QP open interface去建目的Price List
之後才能透過ORIG_SYS_HEADER_REF去做line的Insert/Update/Delete..

INSERT INTO QP_INTERFACE_LIST_HEADERS(
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
NAME,
DESCRIPTION,
CURRENCY_CODE,
ACTIVE_FLAG,
CURRENCY_HEADER_ID,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ROUNDING_FACTOR,
SOURCE_LANG,
LANGUAGE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_HEADER', /* Unique identification of the price list in the external application
from where the price list is imported.*/
'PRL', /* List Type Code. PRL for standard price list. */
'Sample_BLK_PL', /* Price List Name */
'Sample Bulk Loaded Price List',/* Description */
'USD', /* Currency Code. The currency of the price list. */
'Y', /* Active Flag */
3, /* Currency Header Id. Identifies Multi-Currency List associated with a Price List.
Alternatively, populate CURRENCY_HEADER column with Multi-Currency List Name.*/
sysdate, /* Start Date Active */
NULL, /* End Date Active */
-1, /* Rounding Factor */
'US', /* Source Language */
'US', /* Language */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/

/* Insert Price list line details into QP_INTERFACE_LIST_LINES table. */
INSERT INTO QP_INTERFACE_LIST_LINES (
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
LIST_LINE_TYPE_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ARITHMETIC_OPERATOR,
OPERAND,
PRIMARY_UOM_FLAG,
PRODUCT_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_LINE', /* The combination of this and ORIG_SYS_HEADER_REF is the primary key of the
equivalent of the price list line table of the external system from where
the price data is loaded.*/
'SAMPLE_HEADER', /* orig_sys_header_ref */
'PLL', /* List Line Type Code. PLL for price list line. */
sysdate, /* Start date active */
NULL, /* End date active */
'UNIT_PRICE', /* Arithmetic operator */
999, /* operand */
'Y', /* Primary UOM Flag */
230, /* Product Precedence */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/


/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRODUCT_UOM_CODE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_PATTR', /* The combination of ORIG_SYS_HEADER_REF, ORIG_SYS_LINE_REF, ORIG_SYS_PRICING_ATTR_REF
is the primary key of the equivalent of the pricing attribute table of the external
system from where the price data is loaded.*/
'SAMPLE_LINE', /* orig_sys_line_ref */
'SAMPLE_HEADER', /* orig_sys_header_ref */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
'40-002398-01', /* Product Name. Alternatively populate PRODUCT_ATTR_CODE with
inventory item id of the item. */
'PCE', /* Product UOM */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'Y', /* Process flag for transaction */
'P' /* Process Status Flag for transaction */
)
/

/* Insert Pricing attribute details into QP_INTERFACE_PRICING_ATTRIBS table.*/
INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (
ORIG_SYS_PRICING_ATTR_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_HEADER_REF,
EXCLUDER_FLAG,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTR_CODE,
PRODUCT_ATTR_VAL_DISP,
PRICING_ATTRIBUTE_CONTEXT,
PRICING_ATTR_CODE,
PRICING_ATTR_VALUE_FROM_DISP,
PRICING_ATTR_VALUE_TO_DISP,
COMPARISON_OPERATOR_CODE,
PROCESS_FLAG,
INTERFACE_ACTION_CODE,
PRODUCT_UOM_CODE,
PROCESS_STATUS_FLAG
) VALUES
(
'SAMPLE_PATTR1', /* ORIG_SYS_PRICING_ATTR_REF */
'SAMPLE_LINE', /* ORIG_SYS_LINE_REF */
'SAMPLE_HEADER', /* ORIG_SYS_HEADER_REF */
'N', /* Excluder Flag */
'ITEM', /* Product Attribute Context */
'INVENTORY_ITEM_ID', /* Product Attribute Code */
'40-002398-01', /* Product Attribute Value */
'PRICING ATTRIBUTE', /* Pricing Attribute Context */
'FREIGHT_COST', /* Pricing Attribute Code. Corresponds to the Pricing Attribute 'Freight Cost'. */
'100', /* Value From */
'200', /* Value To */
'BETWEEN', /* Comparison Operator Code */
'Y', /* Process Flag for transaction */
'INSERT', /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE. */
'PCE', /* Product UOM Code */
'P' /* Process Status Flag for transaction */
)
/

COMMIT;
/