This API enables creating or importing negotiations into Oracle Sourcing from backend. The negotiation created will be in draft status.
The user can create negotiation with all the header details including header requirements, scoring, collaboration team, controls and invited suppliers. At line level all the line level details along with attributes, cost factors, price breaks and price tiers, can be created. UDA data creation at header level and line level is also supported when solicitations are created for federal customer.
The procedure that should be called for creating negotiation is PON_OPEN_INTERFACE_PUB.CREATE_NEGOTIATIONS.
The input parameters for the above procedure are: p_group_batch_id: This is the value of the interface_group_id that was inserted in pon_auction_headers_interface table. All the negotiations with this interface_group_id in pon_auction_headers_interface table will be picked for processing.
The output parameters are: EFFBUF: This is mandatory output parameter needed for concurrent program. RETCODE: This is another mandatory parameter needed for concurrent program. Value 0 means success and 2 means failure.
Sample data insertion:
INSERT
INTO pon_auction_headers_interface
(
interface_group_id ,
INTERFACE_header_id ,
AUCTION_TITLE ,
ORG_ID ,
--OPEN_BIDDING_DATE ,
CLOSE_BIDDING_DATE ,
OPEN_AUCTION_NOW_FLAG ,
DOCTYPE_ID ,
STYLE_ID ,
PO_STYLE_name ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
PROCESSING_STATUS_CODE ,
PF_TYPE_ALLOWED ,
PRICE_BREAK_RESPONSE ,
PRICE_TIERS_INDICATOR ,
GLOBAL_AGREEMENT_FLAG ,
trading_partner_contact_name
)
VALUES
(
123,
1234 ,
'solicitation api ',
204 ,
--NULL ,
SYSDATE + 3 ,
'Y' ,
5 ,
1 ,
'Blanket Purchase Agreement' ,
sysdate ,
-1 ,
sysdate ,
-1 ,
'PENDING' ,
'BOTH' ,
'OPTIONAL' ,
'PRICE_BREAKS' ,
'Y' ,
'OPERATIONS'
);
-------------------------------------------------------------------------------------------------------------------
-- Line details
INSERT
INTO pon_item_prices_interface
(
INTERFACE_header_id ,
auction_header_id,
INTERFACE_LINE_ID ,
--INTERFACE_TYPE ,
ITEM_DESCRIPTION ,
CATEGORY_NAME,
QUANTITY ,
PRICE ,
LINE_TYPE,
job_id,
GROUP_TYPE ,
price_break_type ,
price_break_neg_flag
)
--ALLOW_ITEM_DESC_UPDATE_FLAG,
--PRICE_AND_QUANTITY_APPLY)
VALUES
(
1234 ,--pon_item_prices_interface_s.currval,--pon_item_prices_interface_s.NEXTVAL, -- batch_id
1234, -- dummy value for auctioN_header_id
1 ,--line_number,
--'ITEMUPLOAD',
'item' ,
'MISC.MISC',
--CATEGORY_ID,
15 ,
--UOM_CODE,
25 ,
'Goods' ,
17,
'LINE' ,
'Non-Cumulative' ,
'Optional'
);
INSERT
INTO pon_auc_price_breaks_interface
(
INTERFACE_header_id ,
INTERFACE_LINE_ID ,
SHIPMENT_NUMBER ,
has_price_differentials_flag,
--DIFFERENTIAL_RESPONSE_TYPE,
MAX_QUANTITY,
EFFECTIVE_END_DATE,
EFFECTIVE_START_DATE,
PRICE
)
VALUES
(
1234 ,
1 ,
1 ,
'N',
--'OPTIONAL' ,
20,
SYSDATE + 100,
SYSDATE + 80,
10
);
INSERT
INTO PON_AUC_ATTRIBUTES_INTERFACE
(
INTERFACE_header_id ,
INTERFACE_LINE_ID ,
SEQUENCE_NUMBER ,
ATTRIBUTE_NAME ,
DATATYPE ,
RESPONSE_TYPE ,
RESPONSE_TYPE_NAME ,
MANDATORY_FLAG ,
DISPLAY_ONLY_FLAG ,
DISPLAY_TARGET_FLAG,
VALUE ,
GROUP_CODE ,
GROUP_NAME ,
SCORING_TYPE ,
ATTR_MAX_SCORE ,
WEIGHT ,
INTERNAL_ATTR_FLAG ,
SCORING_METHOD
)
VALUES
(
1234,
-1 ,
10,
'Years of experience in this domain',
'DAT' ,
'REQUIRED' ,
NULL ,
'N' ,
'N' ,
'N' ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
100 ,
'N' ,
'AUTOMATIC'
);
-------------------------------------------------------------------------------------------------------------------
-- Acceptable values for header
INSERT
INTO pon_attribute_scores_interface
(
INTERFACE_header_id ,
interface_line_id ,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE ,
FROM_RANGE ,
TO_RANGE ,
SCORE ,
SEQUENCE_NUMBER ,
ACTION ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
1234,
-1,
10,
NULL ,
'01-12-2009',
'10-12-2009',
30,1,
NULL ,
SYSDATE,
-1 ,
SYSDATE,
-1
);
INSERT
INTO pon_attribute_scores_interface
(
INTERFACE_header_id ,
interface_line_id,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE ,
FROM_RANGE ,
TO_RANGE ,
SCORE ,
SEQUENCE_NUMBER ,
ACTION ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
VALUES
(
1234,
-1,
10,
NULL ,
'11-12-2009',
'20-12-2009',
50,1,
NULL ,
SYSDATE,
-1 ,
SYSDATE,
-1
);
INSERT
INTO PON_AUC_PRICE_ELEMENTS_INT
(
INTERFACE_header_id ,
INTERFACE_LINE_ID ,
AUCTION_HEADER_ID ,
SEQUENCE_NUMBER ,
PRICE_ELEMENT_TYPE_NAME,
PRICING_BASIS_name ,
VALUE ,
PF_TYPE,
DISPLAY_TO_SUPPLIERS_FLAG
)
VALUES
(
'1234' ,
1 ,--line_number,
1234 , -- dummy value for auctioN_header_id
10 ,--SEQUENCE_NUMBER,
'FREIGHT' ,--(SELECT name FROM pon_price_element_types_tl WHERE PRICE_ELEMENT_TYPE_ID = ppe.PRICE_ELEMENT_TYPE_ID AND ROWNUM =1),
'Per-Unit',--(SELECT meaning FROM fnd_lookups WHERE lookup_type = 'PON_PRICING_BASIS' AND lookup_code = ppe.pricing_basis aND ROWNUM =1) ,
10 ,
'SUPPLIER',--PF_TYPE,
'Y'
);
commit;
/*
DECLARE
EFFBUF VARCHAR2(1000);
retcode VARCHAR2(1);
BEGIN
pon_open_interface_pub.create_negotiations(EFFBUF,retcode,123);
Dbms_Output.put_line( retcode );
END;
SELECT * FROM pon.pon_interface_errors;
select * from pon_auction_headers_interface;
truncate table pon.pon_auction_headers_interface;
truncate table pon.pon_bid_parties_interface;
truncate table pon.pon_neg_team_interface;
truncate table pon.pon_auc_price_breaks_interface;
truncate table pon.pon_item_prices_interface;
truncate table pon.PON_AUC_ATTRIBUTES_INTERFACE;
truncate table pon.pon_attribute_scores_interface;
truncate table pon.PON_AUC_PRICE_ELEMENTS_INT;
TRUNCATE TABLE pon.pon_auc_price_differ_int;
TRUNCATE TABLE pon.pon_interface_errors;
*/
Sample use of this procedure:
DECLARE EFFBUF VARCHAR2(1000); retcode VARCHAR2(1); BEGIN pon_open_interface_pub.create_negotiations(EFFBUF,retcode,123); END;
Concurrent program 'Import Negotiations' is seeded to invoke this API. This is a PL/SQL based program. It takes the input parameter 'p_group_batch_id' which should be the value of interface_group_id given in pon_auction_headers_interface table. If any there is error in any negotiation, then return status of the concurrent program will be error. The module in which the error occurred will be displayed in concurrent program log. For further details, user should check pon_auction_headers_interface and pon_interface_errors tables.
The negotiation API can be called to create a group of negotiations. All these negotiations should have the same interface_group_id in pon_auction_headers_interface table and the value of PROCESSING_STATUS_CODE column in pon_auction_headers_interface table should be 'PENDING'. When the procedure to create negotiations is called with this interface_group_id passed as input parameter, the API will process the negotiations one by one.
When one negotiation data is successfully processed and negotiation is created in the application, the data gets committed for this negotiation and value 'PROCESSED' is stamped in PROCESSING_STATUS_CODE column of pon_auction_Headers_interface table for that negotiation and then next negotiation will be taken for processing. But if there is any failure in the interface data for a particular negotiation, then the value of 'FAILED' is stamped in PROCESSING_STATUS_CODE column of pon_auction_headers_interface table for that negotiation and then next negotiation will be taken for processing.
Validation errors will be logged in PON_INTERFACE_ERRORS table for the particular batch_id. Errors logged in this table can be checked and then data can be corrected. Again PROCESSING_STATUS_CODE should be updated with 'PENDING' status for these records to be picked again for processing.
Following are the list of interface tables that need to be populated
Header level information needs to be inserted in this interface. This will include the controls information also. Interface_group_id column should have the same value for all the records to be processed in a particular run of the API. Individual records should have unique interface_header_id value for each negotiation.
This table will have line details. Interface_header_id value should correspond with that in pon_auction_headers_interface table. Individual lines should have interface_line_id populated.
This table is for both header requirements and line attributes.Inteface_header_id column in this table should be same as that given in pon_auction_Headers_interface table. For line level attributes value of interface_line_Id should be same as that given in pon_item_prices_interface table for that line.
This table will have values for automatic scoring and the scores for those values. Interface_header_id and interface_line_id should match with values in pon_auction_headers_interface and pon_item_prices_interface tables.
This table has list of invited suppliers. Interface_header_id value should match with the value in pon_auction_headers_interface table for that negotiation.
This table will have members to be added in the collobaration team for a particular negotiation. Interface_header_id value should match with pon_auction_headers_interface table.
This table will have cost factor details of a particular line in a negotiation. Interface_header_id and interface_line_id should be matching with the values in pon_item_prices_interface table.
This table will have list of pay items for negotiations supporting complex outcomes. Interface_header_id and interface_line_id should be matching with the values in pon_item_prices_interface table.
This table will store price break information that needs to be imported into the application. Key fields for this interface are interface_header_id, interface_Line_id and shipment_number.
This table will store price differential information for temp labor lines that need to be imported into the application. Key fields for this interface are interface_header_id, Interface_line_id and sequence_number. If the price differential is associated to price break then auction_shipment_number column should be populated with the corresponding shipment number.
Both header level and line level UDA data needs to be inserted in this table. ATTR_GROUP_TYPE column will tell whether it is line level UDA or header level UDA. INTERFACE_REFERENCE_ID will have unique value for each negotiation and should match with interface_header_id of pon_auction_headers_interface table. For line level UDA information, interface_line_id column should have the value that matches with pon_item_prices_interface table.
The following table describes PON_AUCTION_HEADERS_INTERFACE table.
| Column Name | Type | Required | Comment |
|---|---|---|---|
| INTERFACE_GROUP_ID | Number | Yes | - |
| INTERFACE_HEADER_ID | Number | Yes | - |
| PROCESSING_STATUS_CODE | Varchar2 | Yes | - |
| AUCTION_TITLE | Varchar2 | Yes | - |
| DESCRIPTION | Varchar2 | - | - |
| ORG_ID | Number | Yes | Organization_id from hr_operating_units table |
| DOCTYPE_ID | Number | Yes | Doctype_id from pon_auc_doctypes table. |
| STYLE_ID | Number | Yes | Style_id from pon_negotiation_styles_tl |
| PO_STYLE_ID | Number | Either po_style_id or po_style_name is required | - |
| PO_STYLE_NAME | Varchar2 | Either po_style_id or po_style_name is required | Eg: Standard Purchase Order |
| CONTRACT_TYPE | Varchar2 | - | Derived based on PO style |
| EVENT_ID | Number | - | pon_auction_events table |
| EVENT_TITLE | Varchar2 | - | - |
| SECURITY_LEVEL_CODE | Varchar2 | - | Value will be defaulted from settings if not given by the user |
| PO_AGREED_AMOUNT | Number | - | - |
| PO_MIN_REL_AMOUNT | Number | - | - |
| GLOBAL_AGREEMENT_FLAG | Varchar2 | - | - |
| PO_START_DATE | Date | - | - |
| PO_END_DATE | Date | - | - |
| SHIP_TO_LOCATION_CODE | Varchar2 | - | - |
| SHIP_TO_LOCATION_ID | Number | - | - |
| BILL_TO_LOCATION_CODE | Varchar2 | - | - |
| BILL_TO_LOCATION_ID | Number | - | - |
| PAYMENT_TERMS_ID | Number | - | - |
| FREIGHT_TERMS_CODE | Varchar2 | - | - |
| FOB_CODE | Varchar2 | - | - |
| CARRIER_CODE | Varchar2 | - | - |
| CURRENCY_CODE | Varchar2 | - | - |
| RATE_TYPE | Varchar2 | - | - |
| RATE_DATE | Date | - | - |
| PUBLISH_RATES_TO_BIDDERS_FLAG | Varchar2 | - | - |
| OPEN_BIDDING_DATE | Date | Value shouldBe given ifOpen_auction_now_flagis 'Y' | - |
| CLOSE_BIDDING_DATE | Date | - | - |
| VIEW_BY_DATE | Date | - | - |
| AWARD_BY_DATE | Date | - | - |
| OPEN_AUCTION_NOW_FLAG | Varchar2 | - | - |
| NOTE_TO_BIDDERS | Varchar2 | - | - |
| SHOW_BIDDER_NOTES | Varchar2 | - | - |
| BID_VISIBILITY_CODE | Varchar2 | - | Defaulted |
| BID_SCOPE_CODE | Varchar2 | - | Defaulted |
| BID_LIST_TYPE | Varchar2 | - | Defaulted |
| BID_FREQUENCY_CODE | Varchar2 | - | Defaulted |
| BID_RANKING | Varchar2 | Defaulted | |
| RANK_INDICATOR | Varchar2 | - | Defaulted |
| FULL_QUANTITY_BID_CODE | Varchar2 | - | Defaulted |
| MULTIPLE_ROUNDS_FLAG | Varchar2 | - | Defaulted |
| MANUAL_CLOSE_FLAG | Varchar2 | - | Defaulted |
| MANUAL_EXTEND_FLAG | Varchar2 | - | Defaulted |
| AWARD_APPROVAL_FLAG | Varchar2 | - | Defaulted |
| AUCTION_ORIGINATION_CODE | - | - | - |
| ADVANCE_NEGOTIABLE_FLAG | Varchar2 | - | - |
| RECOUPMENT_NEGOTIABLE_FLAG | Varchar2 | - | - |
| PROGRESS_PAYMENT_NEGOTIABLE_FLAG | Varchar2 | - | - |
| RETAINAGE_NEGOTIABLE_FLAG | Varchar2 | - | - |
| MAX_RETAINAGE_NEGOTIABLE_FLAG | Varchar2 | - | - |
| SUPPLIER_ENTERABLE_PYMT_FLAG | Varchar2 | - | - |
| TWO_PART_FLAG | Varchar2 | - | - |
| PROJECT_ID | Number | - | - |
| PF_TYPE_ALLOWED | Varchar2 | - | Defaulted |
| PRICE_BREAK_RESPONSE | Varchar2 | Conditional | Defaulted |
| PRICE_TIERS_INDICATOR | Varchar2 | - | - |
| CREATION_DATE | Date | - | Defaulted |
| CREATED_BY | Number | - | Defaulted |
| LAST_UPDATE_DATE | Date | - | Defaulted |
| LAST_UPDATE_BY | Number | - | Defaulted |
| INT_ATTRIBUTE_CATEGORY | Varchar2 | - | |
| INT_ATTRIBUTE1 | Varchar2 | - | - |
| INT_ATTRIBUTE2 | Varchar2 | - | - |
| INT_ATTRIBUTE3 | Varchar2 | - | - |
| INT_ATTRIBUTE4 | Varchar2 | - | - |
| INT_ATTRIBUTE5 | Varchar2 | - | - |
| INT_ATTRIBUTE6 | Varchar2 | - | - |
| INT_ATTRIBUTE7 | Varchar2 | - | - |
| INT_ATTRIBUTE8 | Varchar2 | - | - |
| INT_ATTRIBUTE9 | Varchar2 | - | - |
| INT_ATTRIBUTE10 | Varchar2 | - | - |
| INT_ATTRIBUTE11 | Varchar2 | - | - |
| INT_ATTRIBUTE12 | Varchar2 | - | - |
| INT_ATTRIBUTE13 | Varchar2 | - | - |
| INT_ATTRIBUTE14 | Varchar2 | - | - |
| INT_ATTRIBUTE15 | Varchar2 | - | - |
| EXT_ATTRIBUTE_CATEGORY | Varchar2 | - | - |
| EXT_ATTRIBUTE1 | Varchar2 | - | - |
| EXT_ATTRIBUTE2 | Varchar2 | - | - |
| EXT_ATTRIBUTE3 | Varchar2 | - | - |
| EXT_ATTRIBUTE4 | Varchar2 | - | - |
| EXT_ATTRIBUTE5 | Varchar2 | - | - |
| EXT_ATTRIBUTE6 | Varchar2 | - | - |
| EXT_ATTRIBUTE7 | Varchar2 | - | - |
| EXT_ATTRIBUTE8 | Varchar2 | - | - |
| EXT_ATTRIBUTE9 | Varchar2 | - | - |
| EXT_ATTRIBUTE10 | Varchar2 | - | - |
| EXT_ATTRIBUTE11 | Varchar2 | - | - |
| EXT_ATTRIBUTE12 | Varchar2 | - | - |
| EXT_ATTRIBUTE13 | Varchar2 | - | - |
| EXT_ATTRIBUTE14 | Varchar2 | - | - |
| EXT_ATTRIBUTE15 | Varchar2 | - | - |
| SIGNED_DATE | Date | - | - |
| SOLICITATION_TYPE | Varchar2 | - | - |
| UDA_TEMPLATE_ID | Number | - | Derived |
| UDA_TEMPLATE_DATE | Date | - | Derived |
| NO_OF_COPIES | Number | - | - |
| STANDARD_FORM | Varchar2 | - | Derived |
| DOCUMENT_FORMAT | Varchar2 | - | Derived |
| AUTO_EXTEND_MIN_TRIGGER_RANK | Number | - | - |
| ENFORCE_PREVRND_BID_PRICE_FLAG | Varchar2 | - | - |
| STAGGERED_CLOSING_INTERVAL | Number | - | - |
| FIRST_LINE_CLOSE_DATE | Date | - | - |
| DISPLAY_BEST_PRICE_BLIND_FLAG | Varchar2 | - | - |
| BID_DECREMENT_METHOD | Varchar2 | - | - |
| SUPPLIER_VIEW_TYPE | Varchar2 | - | - |
| ABSTRACT_DETAILS | Varchar2 | - | - |
| HDR_ATTR_DISPLAY_SCORE | Varchar2 | - | - |
| PROGRESS_PAYMENT_TYPE | Varchar2 | - | Derived |
| LINE_MAS_ENABLED_FLAG | Varchar2 | - | Derived |
| HDR_ATTR_ENABLED_WEIGHTS | Varchar2 | - | Derived |
| AUTO_EXTEND_FLAG | Varchar2 | - | - |
| AUTO_EXTEND_NUMBER | Number | - | - |
| MIN_BID_DECREMENT | Number | - | - |
| MIN_BID_CHANGE_TYPE | Varchar2 | - | - |
| PRICE_DRIVEN_AUCTION_FLAG | Varchar2 | - | - |
| AUTO_EXTEND_ALL_LINES_FLAG | Varchar2 | - | - |
| ALLOW_OTHER_BID_CURRENCY_FLAG | Varchar2 | - | - |
| AUTO_EXTEND_DURATION | Number | - | - |
| SEALED_AUCTION_STATUS | Varchar2 | - | - |
| AUTO_EXTEND_TYPE_FLAG | Varchar2 | - | - |
| SHOW_BIDDER_SCORES | Varchar2 | - | - |
Following is the description of the PON_ITEM_PRICES_INTERFACE table.
| Column Name | Type | Required | Derived and/orDefaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | Number | Yes | |
| INTERFACE_LINE_ID | Number | Yes | |
| AUCTION_HEADER_ID | Number | Yes | Dummy value |
| ITEM_DESCRIPTION | Varchar2 | Yes | - |
| CATEGORY_ID | Number | - | - |
| CATEGORY_NAME | Varchar2 | - | - |
| UNIT_OF_MEASURE | Varchar2 | - | - |
| CURRENCY_CODE | Varchar2 | - | - |
| QUANTITY | Number | Conditionally | - |
| UOM_CODE | Varchar2 | - | Derived |
| PRICE | Number | Conditionally | - |
| PROMISED_DATE | Date | - | - |
| NEED_BY_DATE | Date | Conditionally | - |
| TARGET_PRICE | Number | - | - |
| THRESHOLD_PRICE | Number | - | - |
| BID_START_PRICE | Number | - | - |
| NOTE_TO_BIDDERS | Varchar2 | - | - |
| NOTE_TO_AUCTION_OWNER | Varchar2 | - | - |
| SHIP_TO_LOCATION | Varchar2 | - | - |
| SHIP_TO_LOCATION_ID | Number | - | - |
| CURRENT_PRICE | Number | - | - |
| RESERVE_PRICE | Number | - | - |
| DISPLAY_TARGET_FLAG | Varchar2 | - | - |
| INTERFACE_ITEM_TYPE | Varchar2 | - | - |
| INTERFACE_LOT_LINE_ID | Number | - | - |
| PO_MIN_REL_AMOUNT | Number | - | - |
| PO_RCPT_TOLERANCE_PCT | Number | - | - |
| NUM_DAYS_EARLY | Number | - | - |
| NUM_DAYS_LATE | Number | - | - |
| DISPLAY_TARGET_PRICE_FLAG | Varchar2 | - | - |
| BID_CURRENCY_LIMIT_PRICE | Number | - | - |
| PROXY_BID_LIMIT_PRICE | Number | - | - |
| BID_CURRENCY_PRICE | Number | - | - |
| ATTACHMENT_URL | Varchar2 | - | - |
| ATTACHMENT_DESC | Varchar2 | - | - |
| NEED_BY_START_DATE | - | - | - |
| LINE_TYPE | Varchar2 | Required | - |
| LINE_TYPE_ID | Number | - | Derived |
| LINE_ORIGINATION_CODE | Varchar2 | - | - |
| ORDER_TYPE_LOOKUP_CODE | Varchar2 | - | Derived |
| ITEM_NUMBER | Varchar2 | Conditionally | - |
| ITEM_REVISION | Varchar2 | - | - |
| ITEM_ID | Number | - | - |
| ALLOW_ITEM_ DESC_UPDATE_FLAG | Varchar2 | - | - |
| PRICE_AND_QUANTITY_APPLY | Varchar2 | - | - |
| DISP_LINE_NUMBER | Number | - | - |
| ADDITIONAL_JOB_DETAILS | Varchar2 | - | - |
| PO_AGREED_AMOUNT | Number | - | - |
| DIFFERENTIAL_RESPONSE_TYPE | Varchar2 | - | - |
| PURCHASE_BASIS | Varchar2 | - | Derived |
| JOB_ID | Number | Conditionally | - |
| GROUP_TYPE | Varchar2 | Required | - |
| PARENT_LINE_NUMBER | Number | - | - |
| DOCUMENT_DISP_LINE_NUMBER | Varchar2 | Required | - |
| UNIT_TARGET_PRICE | Number | - | - |
| UNIT_DISPLAY_TARGET_FLAG | Varchar2 | - | - |
| ADVANCE_AMOUNT | Number | - | - |
| RECOUPMENT_RATE_PERCENT | Number | - | - |
| PROGRESS_PYMT_RATE_PERCENT | Number | - | - |
| RETAINAGE_RATE_PERCENT | Number | - | - |
| MAX_RETAINAGE_AMOUNT | Number | - | - |
| WORK_APPROVER_USER_NAME | Varchar2 | - | - |
| WORK_APPROVER_USER_ID | Number | - | - |
| PROJECT_NUMBER | Varchar2 | - | - |
| PROJECT_ID | Number | - | - |
| PROJECT_TASK_NUMBER | Varchar2 | - | - |
| PROJECT_AWARD_NUMBER | Varchar2 | - | - |
| PROJECT_AWARD_ID | Number | - | - |
| PROJECT_EXPENDITURE_TYPE | Varchar2 | - | - |
| PROJECT_EXP_ORGANIZATION_NAME | Varchar2 | - | - |
| PROJECT_EXP_ORGANIZATION_ID | Number | - | - |
| PROJECT_EXPENDITURE_ITEM_DATE | Date | - | - |
| IP_CATEGORY_NAME | Varchar2 | - | - |
| IP_CATEGORY_ID | Number | - | - |
| OUTSIDE_OPERATION_FLAG | Varchar2 | - | - |
| SUB_LINE_SEQUENCE_NUMBER | Number | - | - |
| LINE_NUM_DISPLAY | Varchar2 | - | - |
| GROUP_LINE_ID | Number | - | - |
| CLM_INFO_FLAG | Varchar2 | - | - |
| CLM_OPTION_INDICATOR | Varchar2 | - | - |
| CLM_BASE_LINE_NUM | Number | - | - |
| CLM_OPTION_NUM | Number | - | - |
| CLM_OPTION_FROM_DATE | Date | - | - |
| CLM_OPTION_TO_DATE | Date | - | - |
| CLM_FUNDED_FLAG | Varchar2 | - | - |
| CLM_NEED_BY_DATE | Date | Conditionally | - |
| CLM_CONTRACT_TYPE | Varchar2 | - | - |
| CLM_COST_CONSTRAINT | Varchar2 | - | - |
| CLM_IDC_TYPE | Varchar2 | - | - |
Following table describes PON_AUC_ATTRIBUTES_INTERFACE table:
| Column Name | Type | Required | Derived and/or Defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | - | - |
| INTERFACE_LINE_ID | NUMBER | Yes | - |
| SEQUENCE_NUMBER | NUMBER | Yes | - |
| SCORING_METHOD | VARCHAR2 | - | - |
| KNOCKOUT_SCORE | VARCHAR2 | - | Defaulted |
| ATTR_GROUP_SEQ_NUMBER | NUMBER | - | Defaulted |
| ATTR_DISP_SEQ_NUMBER | NUMBER | - | Derived |
| SUB_LINE_SEQUENCE_NUMBER | NUMBER | - | - |
| IP_CATEGORY_ID | NUMBER | - | - |
| IP_DESCRIPTOR_ID | NUMBER | - | - |
| ATTR_MAX_SCORE | NUMBER | - | - |
| INTERNAL_ATTR_FLAG | VARCHAR2 | - | - |
| WEIGHT | NUMBER | - | - |
| BATCH_ID | NUMBER | - | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| AUCTION_LINE_NUMBER | NUMBER | - | - |
| ATTRIBUTE_NAME | VARCHAR2 | Yes | - |
| DATATYPE | VARCHAR2 | Yes | - |
| RESPONSE_TYPE_NAME | VARCHAR2 | Yes | - |
| RESPONSE_TYPE | VARCHAR2 | - | Derived |
| MANDATORY_FLAG | VARCHAR2 | - | Derived |
| DISPLAY_ONLY_FLAG | VARCHAR2 | - | Derived |
| DISPLAY_TARGET_FLAG | VARCHAR2 | - | Derived |
| VALUE | VARCHAR2 | - | - |
| GROUP_CODE | VARCHAR2 | - | Defaulted |
| GROUP_NAME | VARCHAR2 | - | Defaulted |
| SCORING_TYPE | VARCHAR2 | - | Defaulted |
Following is the description of the PON_ATTRIBUTE_SCORES_INTERFACE table.
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Yes | - |
| INTERFACE_LINE_ID | NUMBER | Yes | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| LINE_NUMBER | NUMBER | - | - |
| ATTRIBUTE_SEQUENCE_NUMBER | NUMBER | Yes | - |
| VALUE | VARCHAR2 | Conditionally | - |
| FROM_RANGE | VARCHAR2 | Conditionally | - |
| TO_RANGE | VARCHAR2 | Conditionally | - |
| SCORE | NUMBER | - | - |
| SEQUENCE_NUMBER | NUMBER | - | - |
| ACTION | VARCHAR2 | - | - |
| CREATION_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
Following is the description of the PON_NEG_TEAM_INTERFACE table.
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Yes | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| USER_NAME | VARCHAR2 | Yes | - |
| APPROVER_FLAG | VARCHAR2 | - | - |
| TASK_NAME | VARCHAR2 | - | - |
| TARGET_DATE | DATE | - | - |
| ACCESS_VALUE | VARCHAR2 | - | - |
| ACTION | VARCHAR2 | - | - |
| CREATION_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
Following is the description of PON_BID_PARTIES_INTERFACE table.
| Column Name | Type | Required | Defaulted and /or derived |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Yes | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| SEQUENCE_NUMBER | NUMBER | - | - |
| VENDOR_NAME | VARCHAR2 | Either vendor name or vendor id should be given | - |
| VENDOR_ID | NUMBER | - | - |
| VENDOR_SITE_ID | NUMBER | - | - |
| VENDOR_SITE_CODE | VARCHAR2 | - | - |
| TRADING_PARTNER_CONTACT_NAME | VARCHAR2 | - | - |
| TRADING_PARTNER_CONTACT_ID | NUMBER | Either trading partner contact id or additional contact email should be given | - |
| ADDITIONAL_CONTACT_EMAIL | VARCHAR2 | - | - |
| CREATION_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
Following is the description of PON_AUC_PRICE_ELEMENTS_INT table.
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Required | - |
| INTERFACE_LINE_ID | NUMBER | Required | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| PF_TYPE | VARCHAR2 | Required | - |
| DISPLAY_TO_SUPPLIERS_FLAG | VARCHAR2 | - | - |
| AUCTION_HEADER_ID | NUMBER | - | - |
| AUCTION_LINE_NUMBER | NUMBER | - | - |
| SEQUENCE_NUMBER | NUMBER | Required | - |
| PRICE_ELEMENT_TYPE_NAME | VARCHAR2 | Required | - |
| PRICE_ELEMENT_TYPE_ID | NUMBER | - | Derived |
| DESCRIPTION | VARCHAR2 | - | - |
| PRICING_BASIS_NAME | VARCHAR2 | - | - |
| PRICING_BASIS | VARCHAR2 | - | - |
| VALUE | NUMBER | Conditionally | - |
| PRECISION | NUMBER | - | - |
| DISPLAY_TARGET_FLAG | VARCHAR2 | - | - |
Following is the description of PON_AUC_PRICE_BREAKS_INTERFACE table.
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Required | - |
| INTERFACE_LINE_ID | NUMBER | Required | - |
| AUCTION_LINE_NUMBER | NUMBER | - | - |
| SHIPMENT_NUMBER | NUMBER | Required | - |
| SHIPMENT_TYPE | VARCHAR2 | - | Derived |
| QUANTITY | NUMBER | Conditionally | - |
| PRICE | NUMBER | - | - |
| ORG_ID | NUMBER | Derived | |
| SHIP_TO_ORGANIZATION_ID | NUMBER | - | - |
| SHIP_TO_LOCATION_ID | NUMBER | - | - |
| EFFECTIVE_START_DATE | DATE | - | - |
| EFFECTIVE_END_DATE | DATE | - | - |
| MAX_QUANTITY | NUMBER | - | - |
| DIFFERENTIAL_RESPONSE_TYPE | VARCHAR2 | - | - |
| HAS_PRICE_DIFFERENTIALS_FLAG | VARCHAR2 | - | Defaulted |
| LAST_UPDATE_LOGIN | NUMBER | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| CREATION_DATE | DATE | - | - |
| SHIP_TO_ORGANIZATION | VARCHAR2 | - | - |
| SHIP_TO_LOCATION | VARCHAR2 | - | - |
| ORG_NAME | VARCHAR2 | - | - |
Following is the description of PON_AUC_PRICE_DIFFER_INT table.
| Column name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Required | - |
| INTERFACE_LINE_ID | NUMBER | Required | - |
| AUCTION_SHIPMENT_NUMBER | NUMBER | - | - |
| AUCTION_HEADER_ID | NUMBER | - | Derived |
| AUCTION_LINE_NUMBER | NUMBER | - | Derived |
| SEQUENCE_NUMBER | NUMBER | Required | - |
| PRICE_TYPE | VARCHAR2 | - | - |
| PRICE_TYPE_NAME | VARCHAR2 | - | - |
| PRICE_TYPE_DESC | VARCHAR2 | - | - |
| MULTIPLIER | NUMBER | - | - |
| CREATION_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
| LAST_UPDATE_LOGIN | NUMBER | - | - |
Following is the description of PON_AUC_PAYMENTS_INTERFACE table.
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Yes | - |
| INTERFACE_LINE_ID | NUMBER | Yes | - |
| AUCTION_HEADER_ID | NUMBER | - | Derived |
| DOCUMENT_DISP_LINE_NUMBER | VARCHAR2 | Yes | - |
| PAYMENT_DISPLAY_NUMBER | NUMBER | Yes | - |
| PAYMENT_DESCRIPTION | VARCHAR2 | Yes | - |
| CREATION_DATE | DATE | - | - |
| CREATED_BY | NUMBER | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| LAST_UPDATED_BY | NUMBER | - | - |
| LAST_UPDATE_LOGIN | NUMBER | - | - |
| PAYMENT_TYPE | VARCHAR2 | Yes | - |
| QUANTITY | NUMBER | Conditionally | - |
| UNIT_OF_MEASURE | VARCHAR2 | - | - |
| TARGET_PRICE | NUMBER | - | - |
| NEED_BY_DATE | DATE | - | - |
| SHIP_TO_LOCATION_CODE | VARCHAR2 | - | - |
| WORK_APPROVER_USER_NAME | VARCHAR2 | - | - |
| NOTE_TO_BIDDERS | VARCHAR2 | - | - |
| PROJECT_NUMBER | VARCHAR2 | - | - |
| PROJECT_TASK_NUMBER | VARCHAR2 | - | - |
| PROJECT_AWARD_NUMBER | VARCHAR2 | - | - |
| PROJECT_EXPENDITURE_TYPE | VARCHAR2 | - | - |
| PROJECT_EXP_ORGANIZATION_NAME | VARCHAR2 | - | - |
| PROJECT_EXPENDITURE_ITEM_DATE | DATE | - | - |
| ATTACHMENT_DESC | VARCHAR2 | - | - |
| ATTACHMENT_URL | VARCHAR2 | - | - |
Following is the description of PO_UDA_INTERFACE table
| Column Name | Type | Required | Derived and/or defaulted |
|---|---|---|---|
| INTERFACE_REFERENCE_ID | NUMBER | Yes | - |
| INTERFACE_LINE_ID | NUMBER | Yes | - |
| ATTR_NAME | VARCHAR2 | Yes | - |
| ATTR_VALUE_STR | VARCHAR2 | Conditionally | - |
| ATTR_VALUE_NUM | NUMBER | - | - |
| ATTR_VALUE_DATE | DATE | - | - |
| ATTR_DISP_VALUE | VARCHAR2 | - | - |
| ATTR_UNIT_OF_MEASURE | VARCHAR2 | - | - |
| USER_ROW_IDENTIFIER | NUMBER | Yes | - |
| PK1_VALUE | NUMBER | - | Derived |
| PK2_VALUE | NUMBER | - | Derived |
| PK3_VALUE | NUMBER | - | - |
| PK4_VALUE | NUMBER | - | - |
| PK5_VALUE | NUMBER | - | - |
| UDA_TEMPLATE_ID | NUMBER | Yes | - |
| PROCESS_STATUS | NUMBER | Yes | - |
| PROCESS_PHASE | VARCHAR2 | - | Derived |
| REQUEST_ID | NUMBER | - | - |
| PROGRAM_ID | NUMBER | - | - |
| PROGRAM_APPLICATION_ID | NUMBER | - | - |
| PROGRAM_UPDATE_DATE | DATE | - | - |
| LAST_UPDATE_DATE | DATE | - | - |
| CREATION_DATE | DATE | Yes | - |
| LAST_UPDATE_LOGIN | NUMBER | - | - |
| CREATED_BY | NUMBER | Yes | - |
| LAST_UPDATED_BY | NUMBER | - | - |
| CREATED_BY_NAME | VARCHAR2 | Yes | - |
| LAST_UPDATED_BY_NAME | VARCHAR2 | - | - |
| INTERFACE_SOURCE_CODE | VARCHAR2 | - | - |
| INTERFACE_SOURCE_LINE_ID | NUMBER | - | - |
| TRANSACTION_ID | NUMBER | Yes | - |
| ROW_IDENTIFIER | NUMBER | Yes | - |
| ATTR_GROUP_ID | NUMBER | Yes | - |
| ATTR_GROUP_APP_ID | NUMBER | Yes | - |
| ATTR_GROUP_TYPE | VARCHAR2 | Yes | - |
| ATTR_GROUP_NAME | VARCHAR2 | Yes | - |
| DATA_LEVEL_1 | VARCHAR2 | Conditionally | - |
| DATA_LEVEL_2 | VARCHAR2 | - | - |
| DATA_LEVEL_3 | VARCHAR2 | - | - |
| TRANSACTION_TYPE | VARCHAR2 | Yes | - |