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;
/