[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 21. If the user has selected to enter price breaks then there should be atleast one price break
* If the price breaks are non negotiable (which also means the price break type is 'REQUIRED')
* and the size is zero, the user must enter a price break.
* 22. If the user has selected to enter price differentials then there should be atleast one price differential.
* 23. if there are no price differentials and price differentails type is not NONE just give an error.
* 24. Every LOT or GROUP should have atleast one line inside it.
* 25. Unit Price should be greater than zero
* 26. There should be atleast one supplier price factor if the unit price is entered
* 27. The total weight of all attributes in an MAS auction should be 100
* 28. If quantity is entered then it should be positive
* 29. The precision of po agreed amount should be less than the auction currency PRECISION
* 30. Ship to location should not be NULL in case of RFI
* 31. In a private auction there should be no line without any invitees
*/
PROCEDURE VALIDATE_PROJECTS_DETAILS (
p_project_id IN NUMBER,
p_task_id IN NUMBER,
p_expenditure_date IN DATE,
p_expenditure_type IN VARCHAR2,
p_expenditure_org IN NUMBER,
p_person_id IN NUMBER,
p_auction_header_id IN NUMBER,
p_line_number IN NUMBER,
p_document_disp_line_number IN VARCHAR2,
p_payment_id IN NUMBER,
p_interface_line_id IN NUMBER,
p_payment_display_number IN NUMBER,
p_batch_id IN NUMBER,
p_table_name IN VARCHAR2,
p_interface_type IN VARCHAR2,
p_entity_type IN VARCHAR2,
p_called_from IN VARCHAR2
) IS
l_module CONSTANT VARCHAR2(32) := 'VALIDATE_PROJECTS_DETAILS';
INSERT INTO pon_interface_errors
(
error_message_name,
token1_name,
token1_value,
token2_name,
token2_value,
token3_name,
token3_value,
error_value_datatype,
interface_type,
table_name,
batch_id,
entity_type,
auction_header_id,
line_number,
payment_id,
interface_line_id,
expiration_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_error_message_name,
'PROJMSG',
fnd_message.get_string(l_msg_application, l_msg_data),
l_token2_name,
l_token2_value,
l_token3_name,
l_token3_value,
'TXT',
p_interface_type,
p_table_name,
p_batch_id,
p_entity_type, --DECODE(p_called_from,'LINES',g_auction_item_type,'PAYMENTS',g_rfq_pymts_type),
p_auction_header_id,
p_line_number,
p_payment_id,
p_interface_line_id,
SYSDATE+7,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.login_id
);
Select price_tiers_indicator
into l_price_tiers_indicator
from pon_auction_headers_all
where auction_header_id = p_auction_header_id ;
INSERT ALL --bugfix
-- The group type for a line should be 'GROUP_LINE' or 'LOT_LINE' if the parent_line_number is populated
-- The group type for a line should be 'GROUP' or 'LOT' if the parent_line_number is not populated
WHEN
(
sel_group_type IS NULL OR
(sel_group_type IN ('GROUP_LINE', 'LOT_LINE') AND sel_parent_line_number IS NULL) OR
(sel_group_type IN ('GROUP', 'LOT') AND sel_parent_line_number IS NOT NULL)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_INVALID_GROUP_TYPE', --ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- The item description should not be an empty string if the doc type rules do not allow
WHEN
(
g_document_type_required_rule('ITEM_DESCRIPTION') = 'Y' AND
g_document_type_validity_rule ('ITEM_DESCRIPTION') = 'Y' AND
(sel_item_description IS NULL OR trim (sel_item_description) = '')
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_ITEMDESC_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate,
p_login_id
)
-- THE LINE_TYPE_ID SHOULD NOT BE NULL
WHEN
(
g_document_type_required_rule ('LINE_TYPE') = 'Y' AND
g_document_type_validity_rule ('LINE_TYPE') = 'Y' AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_line_type_id IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_LINETYPE_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- The category should not be empty
WHEN
(
(sel_category_name IS NULL OR TRIM (sel_category_name) = '' OR sel_category_id IS NULL) AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_CAT_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- Target price should be positive
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_target_price IS NOT NULL AND
sel_target_price <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_TARGETPRICE_BE_POSIT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- BID START PRICE SHOULD BE POSITIVE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_bid_start_price IS NOT NULL AND
sel_bid_start_price <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_STARTPRICE_BE_POSIT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- CURRENT_PRICE SHOULD BE POSITIVE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_current_price IS NOT NULL AND
sel_current_price <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_CURRENTPRICE_BE_POS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- TARGET PRICE SHOULD BE LESS THAN BID START PRICE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_target_price >= sel_bid_start_price
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_TARGET_LOWER_START', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id
)
-- IF DISPLAY TARGET PRICE IS SET THEN TARGET PRICES SHOULD BE ENTERED
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_target_price IS NULL AND
sel_display_target_price_flag = 'Y'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_NUM_TARGETPRICE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- PO MINIMUM RELEASE AMOUNT SHOULD BE POSITIVE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_po_min_rel_amount IS NOT NULL AND
sel_po_min_rel_amount <> g_null_int AND
sel_po_min_rel_amount < 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_MINRELAMT_BE_POSITIV', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- WHEN DISPLAY UNIT TARGET PRICES IS SET THEN UNIT TARGET PRICE SHOULD BE ENTERED
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_unit_target_price IS NULL AND
sel_unit_display_target_flag = 'Y'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UNIT_SHOW_TARGET', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- TARGET PRICE PRECISION SHOULD BE LESS THAN THE AUCTION CURRENCYPRECISION
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_target_price IS NOT NULL AND
ABS (sel_target_price * l_temp - TRUNC (sel_target_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_TARGETPRICE_PRECIS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- BID START PRICE PRECISION SHOULD BE LESS THAN THE AUCTION CURRENCY PRECISION
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_bid_start_price IS NOT NULL AND
ABS (sel_bid_start_price * l_temp - TRUNC (sel_bid_start_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_STARTPRICE_PRECISION', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- CURRENT PRICE PRECISION SHOULD BE LESS THAN THE AUCTION CURRENCY PRECISION
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_current_price IS NOT NULL AND
ABS (sel_current_price * l_temp - TRUNC (sel_current_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_CURRENTPRICE_PRECIS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- THE PRECISION OF PO MINIMUM RELEASE AMOUNT SHOULD BE LESS THAN THE FND CURRENCY
-- PRECISION
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_po_min_rel_amount IS NOT NULL AND
sel_po_min_rel_amount <> g_null_int AND
ABS (sel_po_min_rel_amount * l_temp_fnd - TRUNC (sel_po_min_rel_amount * l_temp_fnd)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE,
ERROR_MESSAGE_NAME,
REQUEST_ID,
BATCH_ID,
ENTITY_TYPE,
AUCTION_HEADER_ID,
LINE_NUMBER,
EXPIRATION_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_TOO_MANY_DIGITS_A', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- need_by date should be after start date
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_need_by_date < sel_need_by_start_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_NEEDBY_BEFORE_FROM', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- NEED BY DATE SHOULD BE AFTER CLOSE BIDDING DATE
-- IF NO CLOSE BIDDING DATE THEN AFTER SYSDATE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_need_by_date IS NOT NULL AND
sel_need_by_date < nvl (p_close_bidding_date, SYSDATE)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (p_close_bidding_date, 'PON_AUC_NEEDBY_TO_BEFORE_CLOSE',
'PON_AUC_NEEDBY_TO_BEFORE_TODAY'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
LINE_NUMBER sel_line_number,
DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
GROUP_TYPE sel_group_type,
PARENT_LINE_NUMBER sel_parent_line_number,
ITEM_DESCRIPTION sel_item_description,
LINE_TYPE_ID sel_line_type_id,
CATEGORY_NAME sel_category_name,
CATEGORY_ID sel_category_id,
TARGET_PRICE sel_target_price,
PO_MIN_REL_AMOUNT sel_po_min_rel_amount,
CURRENT_PRICE sel_current_price,
BID_START_PRICE sel_bid_start_price,
UNIT_DISPLAY_TARGET_FLAG sel_unit_display_target_flag,
UNIT_TARGET_PRICE sel_unit_target_price,
DISPLAY_TARGET_PRICE_FLAG sel_display_target_price_flag,
NEED_BY_START_DATE sel_need_by_start_date,
NEED_BY_DATE sel_need_by_date
FROM
PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
INSERT ALL --bugfix
-- NEED BY START DATE SHOULD BE AFTER CLOSE BIDDING DATE
-- IF NO CLOSE BIDDING DATE THEN AFTER SYSDATE
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_need_by_start_date IS NOT NULL AND
sel_need_by_start_date < nvl (p_close_bidding_date, SYSDATE) AND
p_contract_type = 'STANDARD'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (p_close_bidding_date, 'PON_AUC_NEEDBY_FROM_BEF_CLOSE', 'PON_AUC_NEEDBY_FROM_BEF_TODAY'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- for lines with "planned" inventory items in a SPO negotiation
-- either need-by from or need-by to date must be entered
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_need_by_date IS NULL AND
sel_need_by_start_date IS NULL AND
p_contract_type = 'STANDARD' AND
EXISTS (
SELECT
'X'
FROM
MTL_SYSTEM_ITEMS_KFV MSI,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE
NVL(FSP.ORG_ID, -9999) = NVL(sel_org_id,-9999) AND
MSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID AND
MSI.INVENTORY_ITEM_ID = sel_item_id AND
(MSI.INVENTORY_PLANNING_CODE IN (1, 2) OR MSI.MRP_PLANNING_CODE IN
(3, 4, 7, 8, 9))
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_NEED_BY_DATE_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- if this is a global agreement then cummulative price breaks are not
-- allowed
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_price_break_type = 'CUMULATIVE' AND
p_global_agreement_flag = 'Y' AND
l_price_tiers_indicator = 'PRICE_BREAKS'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_BAD_PBTYPE_GLOBAL', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If the user has selected to enter price breaks
-- then there should be atleast one price break
-- If the price breaks are non negotiable (which also means the price
-- break type is 'REQUIRED') and the size is zero, the user must
-- enter a price break.
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_price_break_neg_flag = 'N' AND
NVL (sel_price_break_type, g_invalid_string) <> g_none AND
NOT EXISTS (
SELECT 1
FROM
PON_AUCTION_SHIPMENTS_ALL PASA
WHERE
PASA.AUCTION_HEADER_ID = p_auction_header_id AND
PASA.LINE_NUMBER = sel_line_number
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PRICEBREAK_MUST_BE_ENTERED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If the user has selected to enter price differentials then
-- there should be atleast one price differential
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_differential_response_type IS NOT NULL AND
NOT EXISTS (
SELECT 1
FROM
PON_PRICE_DIFFERENTIALS PPD
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = sel_line_number AND
PPD.SHIPMENT_NUMBER = -1
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE,TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PRICEDIFF_MUST_BE_ENTERED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id-- LAST_UPDATE_LOGIN
)
-- if there are no price differentials and price differentails type
-- is not NONE just give an error
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_differential_response_type IS NULL AND
EXISTS (
SELECT 1
FROM
PON_PRICE_DIFFERENTIALS PPD
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = sel_line_number AND
PPD.SHIPMENT_NUMBER = -1
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE,TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_SET_DIFF_RESPONSE_TYPE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id-- LAST_UPDATE_LOGIN
)
-- Every LOT or GROUP should have atleast one line inside it
WHEN
(
sel_group_type IN ('LOT', 'GROUP') AND
NOT EXISTS (
SELECT LINE_NUMBER
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
PARENT_LINE_NUMBER = sel_line_number
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
decode (sel_group_type, 'LOT', 'PON_LOT_NEEDS_SUBLINES', 'PON_GROUP_NEEDS_SUBLINES'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Unit Price should be greater than zero
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_unit_target_price IS NOT NULL AND
sel_unit_target_price < 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UNIT_TARGET_BE_POS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- There should be atleast one supplier price factor if the unit price is entered
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_unit_target_price IS NOT NULL AND
NOT EXISTS (
SELECT 1
FROM
PON_PRICE_ELEMENTS
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
LINE_NUMBER = sel_line_number AND
PF_TYPE='SUPPLIER'
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UNITPRICE_SUPPLIER', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- The total weight of all attributes in an MAS auction should be 100
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
p_bid_ranking = 'MULTI_ATTRIBUTE_SCORING' AND
EXISTS(
SELECT 1
FROM
PON_AUCTION_ATTRIBUTES
WHERE
AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = sel_line_number
AND (NVL (SCORING_TYPE,g_invalid_string)='RANGE' OR NVL (SCORING_TYPE,g_invalid_string)='LOV')
) AND
(
SELECT
SUM(weight)
FROM
PON_AUCTION_ATTRIBUTES
WHERE
AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = sel_line_number) <> 100
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_INVALID_WEIGHTS_LINE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If quantity is entered then it should be positive
--bug 6193585 - check whether the quantity is less or equal to zero
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_quantity IS NOT NULL AND
sel_quantity <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_QUANTITY_BE_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- The precision of po agreed amount should be less than the auction currency PRECISION
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_po_agreed_amount IS NOT NULL AND
ABS (sel_po_agreed_amount * l_temp - TRUNC (sel_po_agreed_amount * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_POAGREEDAMT_PRECIS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Ship to location should not be NULL for an SPO not coming from a req except in case of RFI
WHEN
(
g_document_type_required_rule('SHIP_TO_LOCATION') = 'Y' AND
g_document_type_names (p_doctype_id) <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION AND
p_contract_type = 'STANDARD' AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
nvl (sel_line_origination_code,g_invalid_string) <> 'REQUISITION' AND
sel_ship_to_location_id IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_SHIPTOLOC_MUST_ENTERED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- In a private auction there should be no line without any invitees
WHEN
(
p_bid_list_type = 'PRIVATE_BID_LIST' AND
(SELECT COUNT(1)
FROM PON_PARTY_LINE_EXCLUSIONS
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
sel_group_type IN ('LOT', 'GROUP', 'LINE') AND
LINE_NUMBER = sel_line_number) = p_invitees_count
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_LINE_SANS_INVITEE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
LINE_NUMBER sel_line_number,
DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
GROUP_TYPE sel_group_type,
UNIT_TARGET_PRICE sel_unit_target_price,
NEED_BY_START_DATE sel_need_by_start_date,
NEED_BY_DATE sel_need_by_date,
ITEM_ID sel_item_id,
ORG_ID sel_org_id,
PRICE_BREAK_TYPE sel_price_break_type,
DIFFERENTIAL_RESPONSE_TYPE sel_differential_response_type,
PRICE_BREAK_NEG_FLAG sel_price_break_neg_flag,
QUANTITY sel_quantity,
PO_AGREED_AMOUNT sel_po_agreed_amount,
LINE_ORIGINATION_CODE sel_line_origination_code,
SHIP_TO_LOCATION_ID sel_ship_to_location_id
FROM
PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
INSERT ALL
-- Uom code should not be empty for non fixed price items
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
NVL (sel_order_type_lookup_code, g_invalid_string) <> g_fixed_price AND
NVL (sel_quantity_disabled_flag, g_invalid_string) <> 'Y' AND
(
sel_uom_code IS NULL OR TRIM (sel_uom_code) = ''
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UOM_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Ship to location should not be NULL in case of RFI and non temp labor lines
WHEN
(
sel_ship_to_location_id IS NULL AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
nvl (sel_line_origination_code,g_invalid_string) <> 'REQUISITION' AND
g_document_type_required_rule('SHIP_TO_LOCATION') = 'Y' AND
((nvl (sel_purchase_basis,g_invalid_string) <> g_temp_labor AND
g_document_type_names (p_doctype_id) = PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION) OR
p_contract_type = 'STANDARD')
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_SHIPTOLOC_MUST_ENTERED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id-- LAST_UPDATE_LOGIN
)
-- Job id should not be NULL for temp labor lines
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_job_id IS NULL AND
sel_purchase_basis = g_temp_labor
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_JOB_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- For temp labor based lines the po agreed amount should be a positive number
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_po_agreed_amount IS NOT NULL AND
sel_purchase_basis = g_temp_labor AND
sel_po_agreed_amount <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_POAGREEDAMT_BE_POSIT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id-- LAST_UPDATE_LOGIN
)
-- Only global agreements and RFIs can have temp labor lines
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_purchase_basis = g_temp_labor AND
g_document_type_names (p_doctype_id) <> PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION AND
NVL (p_global_agreement_flag, g_invalid_string) <> 'Y'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_NOT_GLOBAL_TEMP', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Unit target price precision should be less than currency precision for fixed price lines
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_unit_target_price IS NOT NULL AND
sel_order_type_lookup_code = g_fixed_price AND
ABS (sel_unit_target_price * l_temp_fnd - TRUNC (sel_unit_target_price * l_temp_fnd)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UNITPRICE_CUR_PREC', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- For non fixed price items the unit price should have precision less than
-- then auction currency precision
WHEN
(
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
sel_unit_target_price IS NOT NULL AND
NVL (sel_order_type_lookup_code, g_invalid_string) <> g_fixed_price AND
ABS (sel_unit_target_price * l_temp - TRUNC (sel_unit_target_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_UNITPRICE_PREC', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Quantity should be entered for blanket/contract for lines with fixed amount
-- price elements
WHEN
(
(p_contract_type = 'BLANKET' OR p_contract_type = 'CONTRACT') AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
NVL (sel_quantity_disabled_flag, g_invalid_string) <> 'Y' AND
NVL (sel_order_type_lookup_code, g_invalid_string) <> g_fixed_price AND
sel_quantity IS NULL AND
EXISTS (
SELECT 1
FROM
PON_PRICE_ELEMENTS
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
LINE_NUMBER = sel_line_number AND
PRICING_BASIS = g_fixed_amount)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
BATCH_ID
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_QUAN_FIXED_AMT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id, -- LAST_UPDATE_LOGIN
p_batch_id -- BATCH_ID
)
-- Quantity is required if the document is RFI and the line is not
-- temp labor based
WHEN
(
sel_quantity IS NULL AND
NVL (sel_group_type, g_invalid_string) <> 'GROUP' AND
NVL (sel_quantity_disabled_flag, g_invalid_string) <> 'Y' AND
NVL (sel_order_type_lookup_code, g_invalid_string) <> g_fixed_price AND
NVL (sel_purchase_basis, g_invalid_string) <> g_temp_labor AND
g_document_type_names (p_doctype_id) = PON_CONTERMS_UTL_PVT.SRC_REQUEST_FOR_INFORMATION
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
BATCH_ID
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_QUANTITY_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id, -- LAST_UPDATE_LOGIN
p_batch_id -- BATCH_ID
)
SELECT
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PAIP.GROUP_TYPE sel_group_type,
PLTB.ORDER_TYPE_LOOKUP_CODE sel_order_type_lookup_code,
PAIP.QUANTITY_DISABLED_FLAG sel_quantity_disabled_flag,
PAIP.UOM_CODE sel_uom_code,
PAIP.SHIP_TO_LOCATION_ID sel_ship_to_location_id,
PAIP.LINE_ORIGINATION_CODE sel_line_origination_code,
PLTB.PURCHASE_BASIS sel_purchase_basis,
PAIP.JOB_ID sel_job_id,
PAIP.PO_AGREED_AMOUNT sel_po_agreed_amount,
PAIP.UNIT_TARGET_PRICE sel_unit_target_price,
PAIP.QUANTITY sel_quantity
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PO_LINE_TYPES_B PLTB
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PLTB.LINE_TYPE_ID = PAIP.LINE_TYPE_ID;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_AUC_AMOUNT_CURR' || l_doctype_suffix, -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PO_LINE_TYPES_B PLTB
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_TYPE_ID = PLTB.LINE_TYPE_ID AND
NVL (PAIP.GROUP_TYPE, g_invalid_string) <> 'GROUP' AND
PLTB.ORDER_TYPE_LOOKUP_CODE = g_amount AND
ROWNUM =1;
INSERT ALL
-- ATTRIBUTE NAME SHOULD NOT BE EMPTY
WHEN
(
sel_attribute_name IS NULL OR
TRIM (sel_attribute_name) = ''
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_ATTRIB_NAME_M', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- IF THE DISPLAY TARGET FLAG IS SET TO Y THEN THE VALUE SHOULD NOT BE NULL
WHEN
(
sel_sequence_number is not null and
sel_sequence_number > 0 and
sel_value IS NULL AND
sel_display_target_flag = 'Y'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_ATTR_SHOW_TARGET', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
--WEIGHT SHOULD BE An integer
WHEN
(
sel_weight IS NOT NULL AND
(sel_weight - sel_trunc_weight <> 0)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_LINE_WEIGHT_INT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
'LINE', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'ATTRIBUTE', -- TOKEN2_NAME
sel_attribute_name, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- THE WEIGHT VALUE SHOULD BE BETWEEN 0 AND 100 IF ENTERED
WHEN
(
sel_weight is not null AND
sel_trunc_weight NOT BETWEEN 0 AND 100
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_LINE_WEIGHT_RANGE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- attribute max score should an integer
WHEN
(
sel_attr_max_score is not null AND
(sel_attr_max_score - sel_trunc_attr_max_score <> 0)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_MUST_BE_A_INT_M', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- THE ATTRRIBUTE MAXIMUM SCORE SHOULD BE A POSITIVE NUMBER
WHEN
(
sel_attr_max_score is not null AND
(sel_trunc_attr_max_score < 0)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_INVALID_MAXSCORE_RANGE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If full quantity bids are required then quantity (-20) cannot be scored
WHEN
(
sel_sequence_number = -20 AND
p_bid_ranking = 'MULTI_ATTRIBUTE_SCORING' AND
p_full_quantity_bid_code = 'FULL_QTY_BIDS_REQD'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_QUANTITY_SCORE_ERR', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- For MAS auctions, if weight is greater than zero then the scoring type
-- should not be null or NONE
WHEN
(
sel_weight IS NOT NULL AND
sel_weight > 0 AND
p_bid_ranking = 'MULTI_ATTRIBUTE_SCORING' AND
NVL (sel_scoring_type, 'NONE') = 'NONE'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_SCORE_WLINE_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
'LINE', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'ATTRIBUTE', -- TOKEN2_NAME
sel_attribute_name, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- For MAS auctions, if weight is zero or null and the scoring type
-- is not null and not none then error
WHEN
(
p_bid_ranking = 'MULTI_ATTRIBUTE_SCORING' AND
(sel_weight IS NULL OR sel_weight <= 0) AND
NVL (sel_scoring_type, 'NONE') <> 'NONE'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_WEIGHT_LINE_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_attribute_name, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
'LINE', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'ATTRIBUTE', -- TOKEN2_NAME
sel_attribute_name, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAA.LINE_NUMBER sel_line_number,
PAA.ATTRIBUTE_NAME sel_attribute_name,
PAA.SEQUENCE_NUMBER sel_sequence_number,
PAA.VALUE sel_value,
PAA.DISPLAY_TARGET_FLAG sel_display_target_flag,
PAA.DISPLAY_ONLY_FLAG sel_display_only_flag,
PAA.WEIGHT sel_weight,
TRUNC (PAA.WEIGHT) sel_trunc_weight,
PAA.ATTR_MAX_SCORE sel_attr_max_score,
TRUNC (PAA.ATTR_MAX_SCORE) sel_trunc_attr_max_score,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PAA.SCORING_TYPE sel_scoring_type
FROM
PON_AUCTION_ATTRIBUTES PAA,
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PAA.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PAA.LINE_NUMBER AND
PAA.ATTR_LEVEL = 'LINE';
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, ATTRIBUTE_NAME, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DUPLICATE_LINE_ATTRS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
p_expiration_date, -- EXPIRATION_DATE
'ATTRIBUTE_NAME', -- TOKEN1_NAME
ATTRIBUTE_NAME, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PON_AUCTION_ATTRIBUTES
WHERE
ATTR_LEVEL='LINE' AND
AUCTION_HEADER_ID=p_auction_header_id
GROUP by AUCTION_HEADER_ID, LINE_NUMBER, attribute_name
HAVING count(LINE_NUMBER) > 1;
INSERT ALL
-- Entered score value should be between 0 and 100
WHEN
(
SCORE is not null AND
sel_trunc_score NOT BETWEEN 0 and 100
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID,
ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_INVALID_SCORE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- SCORE MUST BE A POSITIVE NUMBER
WHEN
(
SCORE is not null AND
(SCORE - sel_trunc_score <>0)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_MUST_BE_A_INT_M', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
SCORE,
LINE_NUMBER,
TRUNC (SCORE) sel_trunc_score
FROM
PON_ATTRIBUTE_SCORES
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
LINE_NUMBER > 0;
INSERT ALL
-- PRICE BREAK EFFECTIVE START DATE SHOULD BE BEFORE EFFECTIVE END DATE
WHEN
(
sel_effective_start_date IS NOT NULL AND
sel_effective_end_date IS NOT NULL AND
sel_effective_end_date < sel_effective_start_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_EFFC_END_BEF_START', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- SHIP TO LOCATON AND SHIP TO ORG SHOULD BE PROPER
-- THE SHIP_TO_LOCATION AND SHIP_TO_ORG IF BOTH ARE ENTERED THEN EITHER
-- 1. The Ship_to_location should belong to the Ship_to_organization
-- 2. The Ship_to_location should be a global location (inventory_organization_id is null)
WHEN
(
sel_ship_to_organization_id IS NOT NULL AND
sel_ship_to_location_id IS NOT NULL AND
NOT EXISTS (SELECT l.INVENTORY_ORGANIZATION_ID
FROM HR_LOCATIONS_ALL L
WHERE SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND
NVL(L.SHIP_TO_SITE_FLAG,'N') = 'Y' AND
L.LOCATION_ID = sel_ship_to_location_id AND
nvl (L.INVENTORY_ORGANIZATION_ID, sel_ship_to_organization_id) = sel_ship_to_organization_id)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_SHIP_TO_MATCHING_ERR', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- PRICE BREAK SHOULD NOT BE EMPTY
-- ONLY PRICE SHOULD NOT BE ENTERED
WHEN
(
sel_ship_to_organization_id IS NULL AND
sel_ship_to_location_id IS NULL AND
sel_effective_start_date IS NULL AND
sel_effective_end_date IS NULL AND
sel_quantity IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (sel_price, 'PON_AUCTS_PB_PRICE_ONLY', 'PON_AUCTS_SHIPMENT_EMPTY'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- quantity should not be empty or negative
WHEN
(
sel_quantity IS NOT NULL AND
sel_quantity < 0 AND
sel_quantity <> g_null_int
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PB_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- the price break price should be positive
WHEN
(
sel_price < 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PB_RPICE_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE START DATE AFTER SYSDATE OR CLOSE DATE
WHEN
(
sel_effective_start_date IS NOT NULL AND
sel_effective_start_date <= NVL (p_close_bidding_date, SYSDATE)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (p_close_bidding_date, 'PON_AUC_EFFC_FROM_BEF_CLOSE', 'PON_AUC_EFFC_FROM_BEF_TODAY'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE END DATE AFTER SYSDATE OR CLOSE DATE
WHEN
(
sel_effective_end_date IS NOT NULL AND
sel_effective_end_date <= NVL (p_close_bidding_date, SYSDATE)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (p_close_bidding_date, 'PON_AUC_EFFC_TO_BEFORE_CLOSE',
'PON_AUC_EFFC_TO_BEFORE_TODAY'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- RESPONSE TYPE if entered should have price differentials
WHEN
(
sel_differential_response_type IS NOT NULL AND
NOT EXISTS (
SELECT 1
FROM PON_PRICE_DIFFERENTIALS PPD
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = sel_line_number AND
PPD.SHIPMENT_NUMBER = sel_shipment_number
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PRICEDIFF_REQD_FOR_SHIP', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- RESPONSE TYPE: If the response type is null then there should be no price differentials
WHEN
(
sel_differential_response_type IS NULL AND
EXISTS (
SELECT 1
FROM PON_PRICE_DIFFERENTIALS PPD
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = sel_line_number AND
PPD.SHIPMENT_NUMBER = sel_shipment_number
)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_SET_DIFFER_RESPONSE_TYPE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Precision of the price entered should be less than the auction currency precision
WHEN
(
sel_price >= 0 AND
ABS (sel_price * l_temp - TRUNC (sel_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PB_PRICE_PRECISION', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE START DATE SHOULD BE AFTER PO START DATE
WHEN
(
p_po_start_date IS NOT NULL AND
sel_effective_start_date IS NOT NULL AND
sel_effective_start_date < p_po_start_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, batch_id, ENTITY_TYPE,AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY,CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_EFFC_FROM_BEF_NEG', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE END DATE SHOULD BE AFTER PO START DATE
WHEN
(
p_po_start_date IS NOT NULL AND
sel_effective_end_date IS NOT NULL AND
sel_effective_end_date < p_po_start_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, batch_id, ENTITY_TYPE,AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY,CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_EFFC_TO_BEFORE_NEG', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- the effective start date should be before po end date if both are entered
WHEN
(
p_po_end_date IS NOT NULL AND
sel_effective_start_date IS NOT NULL AND
sel_effective_start_date > p_po_end_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, batch_id, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_EFFC_FROM_AFT_NEG', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- effective end date should be before the po end date
WHEN
(
p_po_end_date IS NOT NULL AND
sel_effective_end_date IS NOT NULL AND
sel_effective_end_date > p_po_end_date
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, batch_id, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_EFFC_TO_AFT_NEG', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PASA.SHIPMENT_NUMBER sel_shipment_number,
PASA.PRICE sel_price,
PASA.QUANTITY sel_quantity,
PASA.EFFECTIVE_END_DATE sel_effective_end_date,
PASA.EFFECTIVE_START_DATE sel_effective_start_date,
PASA.SHIP_TO_LOCATION_ID sel_ship_to_location_id,
PASA.SHIP_TO_ORGANIZATION_ID sel_ship_to_organization_id,
PASA.DIFFERENTIAL_RESPONSE_TYPE sel_differential_response_type
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_AUCTION_SHIPMENTS_ALL PASA
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PASA.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PASA.LINE_NUMBER;
INSERT ALL
-- The min quantity is a required field. If the min quantity is null,
-- we insert rows into the interface errors table.
WHEN
(
sel_min_quantity IS NULL
OR
sel_min_quantity = g_null_int
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PT_MIN_QUANTITY_REQ', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- The max quantity is a required field. If the min quantity is null,
-- we insert rows into the interface errors table.
WHEN
(
sel_max_quantity IS NULL
OR
sel_max_quantity = g_null_int
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PT_MAX_QUANTITY_REQ', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- The min quantity should be a positive number. i.e. strictly greater than zero.
WHEN
(
(sel_min_quantity IS NOT NULL AND
sel_min_quantity <= 0 AND
sel_min_quantity <> g_null_int)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PT_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- The max quantity should be a positive number. i.e. strictly greater than zero.
WHEN
(
(sel_max_quantity IS NOT NULL AND
sel_max_quantity <= 0 AND
sel_max_quantity <> g_null_int)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PT_QUANTITY_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- max quantity should be greater or equal to the min quantity. i.e if min quantity should not
-- be greater than max quantity
WHEN
(
sel_min_quantity > sel_max_quantity
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_QT_MAX_MIN_QTY_ERR', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- the price tier price should be positive( i.e. if it is not null then it should be positive)
WHEN
(
sel_price <> g_null_int
AND
sel_price <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_QT_PRICE_POSITIVE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Precision of the price entered should be less than the auction currency precision
WHEN
(
sel_price > 0 AND
ABS (sel_price * l_temp - TRUNC (sel_price * l_temp)) > 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME,
TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_QT_PRICE_PRECISION', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PASA.SHIPMENT_NUMBER sel_shipment_number,
PASA.PRICE sel_price,
PASA.QUANTITY sel_min_quantity,
PASA.MAX_QUANTITY sel_max_quantity
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_AUCTION_SHIPMENTS_ALL PASA
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PASA.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PASA.LINE_NUMBER;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID, LINE_NUMBER,
EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
Select
g_interface_type, --INTERFACE_TYPE
'PON_AUC_OVERLAP_RANGES_QT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pts_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
paip.line_number, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
paip.line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM pon_auction_item_prices_all paip
WHERE paip.auction_header_id = p_auction_header_id
AND paip.line_number IN
(SELECT DISTINCT pasa.line_number
FROM pon_auction_shipments_all pasa1,
pon_auction_shipments_all pasa
WHERE pasa1.auction_header_id = p_auction_header_id
and pasa.auction_header_id = p_auction_header_id
AND pasa.line_number = pasa1.line_number
AND pasa1.shipment_number <> pasa.shipment_number
AND pasa1.quantity <= pasa.quantity
AND pasa.quantity <= pasa1.max_quantity) ;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_DUPLICATE_PRICE_TYPES_ERR', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
PAIP.LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
PAIP.DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PON_PRICE_DIFFERENTIALS PPD,
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = PAIP.LINE_NUMBER AND
PPD.SHIPMENT_NUMBER = -1
GROUP BY
PPD.AUCTION_HEADER_ID,
PAIP.LINE_NUMBER,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PPD.PRICE_TYPE
HAVING
COUNT(PPD.LINE_NUMBER) > 1;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_DUPLICATE_PRICE_TYPES', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
PAIP.LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
PAIP.DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PON_PRICE_DIFFERENTIALS PPD,
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_AUCTION_SHIPMENTS_ALL PAS
WHERE
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PAS.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.LINE_NUMBER = PAIP.LINE_NUMBER AND
PPD.LINE_NUMBER = PAS.LINE_NUMBER AND
PAS.SHIPMENT_NUMBER = PPD.SHIPMENT_NUMBER
GROUP BY
PPD.AUCTION_HEADER_ID,
PAIP.LINE_NUMBER,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PPD.PRICE_TYPE,
PAS.SHIPMENT_NUMBER
HAVING
COUNT(PPD.LINE_NUMBER) > 1;
INSERT ALL
-- MULTIPLIER SHOULD BE A POSITIVE NUMBER
WHEN
(
sel_multiplier IS NULL OR
sel_multiplier <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, PRICE_DIFFERENTIAL_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
NVL2 (sel_multiplier, 'PON_AUCTS_TAR_MULT_POSITIVE', 'PON_AUCTS_TAR_MULT_REQD'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PRICE_TYPE', -- TOKEN2_NAME
sel_price_differential_desc, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- PRICE DIFFERENTIAL SHOULD NOT BE EMPTY
WHEN
(
sel_price_type = g_empty_price_type AND
sel_multiplier IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, PRICE_DIFFERENTIAL_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME,
TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PRICE_DIFF_EMPTY', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PRICE_TYPE', -- TOKEN1_NAME
sel_price_differential_desc, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PPD.MULTIPLIER sel_multiplier,
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PPD.PRICE_DIFFERENTIAL_NUMBER sel_price_differential_number,
PPDL.PRICE_DIFFERENTIAL_DESC sel_price_differential_desc,
PPD.PRICE_TYPE sel_price_type
FROM
PON_PRICE_DIFFERENTIALS PPD,
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PO_PRICE_DIFF_LOOKUPS_V PPDL
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PPD.LINE_NUMBER AND
PPDL.PRICE_DIFFERENTIAL_TYPE = PPD.PRICE_TYPE AND
PPD.SHIPMENT_NUMBER = -1;
INSERT ALL
-- MULTIPLIER SHOULD BE A POSITIVE NUMBER
WHEN
(
sel_multiplier IS NULL OR
sel_multiplier <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, PRICE_DIFFERENTIAL_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
DECODE (sel_multiplier, 'PON_AUCTS_TAR_MULT_POS_SHIP', 'PON_AUCTS_TAR_MULT_REQD_SHIP'), -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
sel_price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PRICE_TYPE', -- TOKEN2_NAME
sel_price_differential_desc, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- PRICE DIFFERENTIAL SHOULD NOT BE EMPTY
WHEN
(
sel_price_type = g_empty_price_type AND
sel_multiplier IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, PRICE_DIFFERENTIAL_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME,
TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUCTS_PRICE_DIFF_EMPTY', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pds_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_shipment_number, -- SHIPMENT_NUMBER
sel_price_differential_number, -- PRICE_DIFFERENTIAL_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PRICE_TYPE', -- TOKEN1_NAME
sel_price_differential_desc, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PPD.MULTIPLIER sel_multiplier,
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PPD.PRICE_DIFFERENTIAL_NUMBER sel_price_differential_number,
PPD.PRICE_TYPE sel_price_type,
PPD.SHIPMENT_NUMBER sel_shipment_number,
PPDL.PRICE_DIFFERENTIAL_DESC sel_price_differential_desc
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_AUCTION_SHIPMENTS_ALL PASA,
PON_PRICE_DIFFERENTIALS PPD,
PO_PRICE_DIFF_LOOKUPS_V PPDL
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PPD.AUCTION_HEADER_ID = p_auction_header_id AND
PASA.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PASA.LINE_NUMBER AND
PASA.LINE_NUMBER = PPD.LINE_NUMBER AND
PPDL.PRICE_DIFFERENTIAL_TYPE = PPD.PRICE_TYPE AND
PASA.SHIPMENT_NUMBER = PPD.SHIPMENT_NUMBER;
INSERT ALL
-- FOR LINE TYPE PRICE ELEMENTS THE VALUE SHOULD BE > 0
WHEN
(
sel_value IS NOT NULL AND
sel_price_element_type_id = g_item_price_type_id AND
sel_value <= 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME,
TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PE_VALUE_MUST_BE_POS', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- FOR NON LINE TYPE PRICE ELEMENTS THE VALUE SHOULD BE < 0
WHEN
(
sel_VALUE IS NOT NULL AND
sel_PRICE_ELEMENT_TYPE_ID <> g_item_price_type_id AND
sel_VALUE < 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PE_VALUE_BE_POS_OR_ZERO', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- IF THE DISPLAY TARGET FLAG IS SET THEN TARGET VALUE SHOULD BE ENTERED
WHEN
(
sel_value IS NULL AND
sel_display_target_flag = 'Y'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_PE_SHOW_TARGET', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If the pricing basis is per unit then the value enteres should have
-- a precision less than the auction currency precision
WHEN
(
sel_value IS NOT NULL AND
sel_pricing_basis = g_per_unit AND
ABS (sel_value * l_temp - TRUNC (sel_value * l_temp))> 0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PE_TOO_MANY_DIGITS_M' || l_doctype_suffix, -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- If the pricing basis is fixed amount then the value precision should
-- be less than the fnd currency precision
WHEN
(
sel_value IS NOT NULL AND
sel_pricing_basis = g_fixed_amount AND
ABS (sel_value * l_temp_fnd - TRUNC (sel_value * l_temp_fnd)) >0
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PE_TOO_MANY_DIGITS_A', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- There should not be any inactive price elements
WHEN
(
sel_enabled_flag = 'N'
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_AUCTION_INA_PES', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
sel_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Pricing Basis should not be NULL - added this for back button support
-- Pricing basis column is made nullable on the database
WHEN
(
sel_pricing_basis IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY,CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_PRICING_BASIS_M', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- Cost factor name should not be null (added for back button support)
WHEN
(
sel_name IS NULL
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE,
CREATED_BY,CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_PE_REQUIRED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
sel_line_number, -- LINE_NUMBER
sel_price_element_type_id, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.LINE_NUMBER sel_line_number,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
PPE.PRICE_ELEMENT_TYPE_ID sel_price_element_type_id,
PPETL.NAME sel_name,
PPE.VALUE sel_value,
PPE.DISPLAY_TARGET_FLAG sel_display_target_flag,
PPE.PRICING_BASIS sel_pricing_basis,
PPET.ENABLED_FLAG sel_enabled_flag,
PPET.SYSTEM_FLAG sel_system_flag
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_PRICE_ELEMENTS PPE,
PON_PRICE_ELEMENT_TYPES_TL PPETL,
PON_PRICE_ELEMENT_TYPES PPET
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PPE.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PPE.LINE_NUMBER AND
PPE.PRICE_ELEMENT_TYPE_ID = PPETL.PRICE_ELEMENT_TYPE_ID(+) AND
PPE.PRICE_ELEMENT_TYPE_ID = PPET.PRICE_ELEMENT_TYPE_ID(+) AND
PPETL.LANGUAGE(+) = USERENV ('LANG');
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, LINE_NUMBER, PRICE_ELEMENT_TYPE_ID, EXPIRATION_DATE,
TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DUPLICATE_PES', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pfs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
PPE.LINE_NUMBER, -- LINE_NUMBER
PPE.PRICE_ELEMENT_TYPE_ID, -- PRICE_ELEMENT_TYPE_ID
p_expiration_date, -- EXPIRATION_DATE
'LINE_NUMBER', -- TOKEN1_NAME
PAIP.DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
'PE_NAME', -- TOKEN1_NAME
PPETL.NAME, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP,
PON_PRICE_ELEMENTS PPE,
PON_PRICE_ELEMENT_TYPES_TL PPETL
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id AND
PPE.AUCTION_HEADER_ID = p_auction_header_id AND
PAIP.LINE_NUMBER = PPE.LINE_NUMBER AND
PPE.PRICE_ELEMENT_TYPE_ID = PPETL.PRICE_ELEMENT_TYPE_ID AND
PPETL.LANGUAGE = USERENV ('LANG')
GROUP BY
PPE.AUCTION_HEADER_ID,
PPE.LINE_NUMBER,
PPE.PRICE_ELEMENT_TYPE_ID,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PPETL.NAME
HAVING
COUNT (PPE.LINE_NUMBER) > 1;
SELECT
COUNT(LINE_NUMBER)
INTO
l_major_line_count
FROM
pon_auction_item_prices_all
WHERE
auction_header_id = p_auction_header_id AND
group_type IN ('LOT', 'GROUP', 'LINE');
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_PARTY_TOTALLY_EXCLUDED', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_attrs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
p_expiration_date, -- EXPIRATION_DATE
'SUPPLIER_NAME', -- TOKEN1_NAME
pbp.trading_partner_name, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
pon_bidding_parties pbp
WHERE
pbp.auction_header_id = p_auction_header_id AND
(SELECT
COUNT(trading_partner_id)
FROM
pon_party_line_exclusions pple
WHERE
pple.trading_partner_id = pbp.trading_partner_id AND
pple.vendor_site_id = pbp.vendor_site_id AND
pple.auction_header_id = p_auction_header_id) = l_major_line_count;
INSERT ALL
-- VALIDATE JOB ID
WHEN
(
SELECTED_PURCHASE_BASIS = g_temp_labor AND
NOT EXISTS (SELECT 'X'
FROM PO_JOB_ASSOCIATIONS
WHERE JOB_ID = SELECTED_JOB_ID)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_REF_JOB', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE LINE TYPE ERROR
WHEN
(
SELECTED_LINE_ORIGINATION_CODE = 'BLANKET' AND
NOT EXISTS (SELECT 'X'
FROM PO_LINE_TYPES_B
WHERE LINE_TYPE_ID = SELECTED_LINE_TYPE_ID)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_REF_LTYP', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE CATEGORY
WHEN
(
SELECTED_LINE_ORIGINATION_CODE = 'BLANKET' AND
SELECTED_ITEM_ID IS NOT NULL AND
NOT EXISTS (SELECT 'X'
FROM MTL_ITEM_CATEGORIES MIC,
MTL_DEFAULT_SETS_VIEW MDSV,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE MIC.INVENTORY_ITEM_ID = SELECTED_ITEM_ID
AND MIC.ORGANIZATION_ID =
FSP.INVENTORY_ORGANIZATION_ID
AND NVL(FSP.ORG_ID, -9999) = NVL(SELECTED_ORG_ID, -9999)
AND MIC.CATEGORY_SET_ID = MDSV.CATEGORY_SET_ID
AND MDSV.FUNCTIONAL_AREA_ID = 2)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_REF_CAT', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE ITEM REVISION
WHEN
(
SELECTED_LINE_ORIGINATION_CODE = 'BLANKET' AND
SELECTED_ITEM_ID IS NOT NULL AND
SELECTED_ITEM_REVISION IS NOT NULL AND
NOT EXISTS (SELECT 'X'
FROM MTL_ITEM_REVISIONS
WHERE REVISION = SELECTED_ITEM_REVISION AND
INVENTORY_ITEM_ID = SELECTED_ITEM_ID)
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_REF_REV', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE OUTSIDE PROCESSING
WHEN
(
SELECTED_LINE_ORIGINATION_CODE = 'BLANKET' AND
SELECTED_ITEM_ID IS NOT NULL AND
NOT EXISTS (SELECT 'X'
FROM
MTL_SYSTEM_ITEMS MSI,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE
MSI.INVENTORY_ITEM_ID = SELECTED_ITEM_ID AND
MSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID AND
NVL(FSP.ORG_ID, -9999) = NVL(SELECTED_ORG_ID, -9999) AND
(MSI.OUTSIDE_OPERATION_FLAG <> 'Y'
OR (MSI.OUTSIDE_OPERATION_FLAG = 'Y'
AND EXISTS (SELECT 'OP LINE TYPE'
FROM PO_LINE_TYPES_B PLT
WHERE PLT.LINE_TYPE_ID = SELECTED_LINE_TYPE_ID
AND PLT.OUTSIDE_OPERATION_FLAG ='Y') ) ) )
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_INVALID_OP_LINE_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE ITEM PURCHASEABLE
WHEN
(
SELECTED_LINE_ORIGINATION_CODE = 'BLANKET' AND
SELECTED_ITEM_ID IS NOT NULL AND
NOT EXISTS (SELECT 'X'
FROM
MTL_SYSTEM_ITEMS MSI,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE
MSI.INVENTORY_ITEM_ID = SELECTED_ITEM_ID AND
MSI.PURCHASING_ENABLED_FLAG = 'Y' AND
MSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID AND
NVL(FSP.ORG_ID, -9999) = NVL(SELECTED_ORG_ID, -9999))
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_ITEM_NOT_PURCHASE_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'NUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.JOB_ID SELECTED_JOB_ID,
PAIP.PURCHASE_BASIS SELECTED_PURCHASE_BASIS,
PAIP.LINE_ORIGINATION_CODE SELECTED_LINE_ORIGINATION_CODE,
PAIP.LINE_TYPE_ID SELECTED_LINE_TYPE_ID,
PAIP.ITEM_ID SELECTED_ITEM_ID,
PAIP.ORG_ID SELECTED_ORG_ID,
PAIP.ITEM_REVISION SELECTED_ITEM_REVISION,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PAIP.LINE_NUMBER
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id;
INSERT ALL
-- VALIDATE SHIP TO ORG 1
WHEN
(
SELECTED_SHIP_TO_ORG_ID IS NOT NULL AND
SELECTED_ITEM_ID IS NOT NULL AND
SELECTED_ITEM_REVISION IS NOT NULL AND
SELECTED_SHIP_TO_ORG_ID NOT IN
(SELECT OOD.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS MSI,
MTL_ITEM_REVISIONS MIR,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
PO_LINE_TYPES_B PLTB
WHERE OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND
FSP.ORG_ID = SELECTED_ORG_ID AND
PLTB.LINE_TYPE_ID = SELECTED_LINE_TYPE_ID AND
SYSDATE < NVL(OOD.DISABLE_DATE,SYSDATE+1) AND
OOD.ORGANIZATION_ID = MIR.ORGANIZATION_ID AND
MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND
MSI.PURCHASING_ENABLED_FLAG = 'Y' AND
MIR.INVENTORY_ITEM_ID = SELECTED_ITEM_ID AND
MIR.REVISION = SELECTED_ITEM_REVISION AND
MSI.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID AND
((PLTB.OUTSIDE_OPERATION_FLAG = 'Y' AND
MSI.OUTSIDE_OPERATION_FLAG = 'Y') OR PLTB.OUTSIDE_OPERATION_FLAG = 'N'))
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_SHIPO_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
SHIPMENT_NUMBER, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'ITEMNUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
'SHIPNUM', -- TOKEN2_NAME
SHIPMENT_NUMBER, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE SHIP TO LOC
WHEN
(
SELECTED_SHIP_TO_LOCATION_ID IS NOT NULL AND
SELECTED_SHIP_TO_LOCATION_ID NOT IN
(SELECT LOC.LOCATION_ID
FROM HR_LOCATIONS_ALL LOC,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE HAOU.ORGANIZATION_ID = SELECTED_ORG_ID AND
NVL (LOC.BUSINESS_GROUP_ID, NVL(HAOU.BUSINESS_GROUP_ID, -99)) = NVL (HAOU.BUSINESS_GROUP_ID, -99) AND
LOC.SHIP_TO_SITE_FLAG = 'Y' AND
(SELECTED_SHIP_TO_ORG_ID IS NULL OR
NVL(LOC.INVENTORY_ORGANIZATION_ID, NVL(SELECTED_SHIP_TO_ORG_ID,-1)) = NVL(SELECTED_SHIP_TO_ORG_ID,-1)) AND SYSDATE < NVL(LOC.INACTIVE_DATE, SYSDATE + 1))
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE, TOKEN2_NAME,
TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_SHIPL_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
SHIPMENT_NUMBER, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'ITEMNUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
'SHIPNUM', -- TOKEN2_NAME
SHIPMENT_NUMBER, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.LINE_NUMBER,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PSA.SHIPMENT_NUMBER,
PSA.SHIP_TO_LOCATION_ID SELECTED_SHIP_TO_LOCATION_ID,
PSA.SHIP_TO_ORGANIZATION_ID SELECTED_SHIP_TO_ORG_ID,
PAIP.ORG_ID SELECTED_ORG_ID,
PAIP.ITEM_ID SELECTED_ITEM_ID,
PAIP.ITEM_REVISION SELECTED_ITEM_REVISION,
PAIP.LINE_TYPE_ID SELECTED_LINE_TYPE_ID
FROM
PON_AUCTION_SHIPMENTS_ALL PSA,
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PSA.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID AND
PAIP.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID AND
PSA.LINE_NUMBER = PAIP.LINE_NUMBER;
INSERT ALL
-- VALIDATE SHIP TO ORG 2
WHEN
(
SELECTED_SHIP_TO_ORG_ID IS NOT NULL AND
SELECTED_ITEM_ID IS NOT NULL AND
SELECTED_ITEM_REVISION IS NULL AND
SELECTED_SHIP_TO_ORG_ID NOT IN
(SELECT OOD.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS_KFV MSI,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
PO_LINE_TYPES_B PLTB
WHERE OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND
FSP.ORG_ID = SELECTED_ORG_ID AND
PLTB.LINE_TYPE_ID = SELECTED_LINE_TYPE_ID AND
SYSDATE < NVL(OOD.DISABLE_DATE,SYSDATE+1) AND
OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND
MSI.INVENTORY_ITEM_ID = SELECTED_ITEM_ID AND
MSI.PURCHASING_ENABLED_FLAG = 'Y' AND
NVL(MSI.OUTSIDE_OPERATION_FLAG, 'N') =
NVL(PLTB.OUTSIDE_OPERATION_FLAG, 'N'))
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_SHIPO_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
SHIPMENT_NUMBER, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'ITEMNUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
'SHIPNUM', -- TOKEN2_NAME
SHIPMENT_NUMBER, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
-- VALIDATE SHIP TO ORG 3
WHEN
(
SELECTED_SHIP_TO_ORG_ID IS NOT NULL AND
SELECTED_ITEM_ID IS NULL AND
SELECTED_SHIP_TO_ORG_ID NOT IN
(SELECT OOD.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND
FSP.ORG_ID = SELECTED_ORG_ID AND
SYSDATE < NVL(OOD.DISABLE_DATE,SYSDATE+1) )
)
THEN INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE, AUCTION_HEADER_ID,
LINE_NUMBER, SHIPMENT_NUMBER, EXPIRATION_DATE, TOKEN1_NAME, TOKEN1_VALUE,
TOKEN2_NAME, TOKEN2_VALUE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
(
g_interface_type, --INTERFACE_TYPE
'PON_AUC_DATA_INVALID_SHIPO_REF', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, --BATCH_ID
g_auction_pbs_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
LINE_NUMBER, -- LINE_NUMBER
SHIPMENT_NUMBER, -- SHIPMENT_NUMBER
p_expiration_date, -- EXPIRATION_DATE
'ITEMNUM', -- TOKEN1_NAME
DOCUMENT_DISP_LINE_NUMBER, -- TOKEN1_VALUE
'SHIPNUM', -- TOKEN2_NAME
SHIPMENT_NUMBER, -- TOKEN2_VALUE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
)
SELECT
PAIP.LINE_NUMBER,
PAIP.DOCUMENT_DISP_LINE_NUMBER,
PSA.SHIPMENT_NUMBER,
PSA.SHIP_TO_ORGANIZATION_ID SELECTED_SHIP_TO_ORG_ID,
PAIP.ORG_ID SELECTED_ORG_ID,
PAIP.ITEM_ID SELECTED_ITEM_ID,
PAIP.ITEM_REVISION SELECTED_ITEM_REVISION,
PAIP.LINE_TYPE_ID SELECTED_LINE_TYPE_ID
FROM
PON_AUCTION_SHIPMENTS_ALL PSA,
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PSA.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID AND
PAIP.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID AND
PSA.LINE_NUMBER = PAIP.LINE_NUMBER;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE, ERROR_MESSAGE_NAME, REQUEST_ID, BATCH_ID, ENTITY_TYPE,
AUCTION_HEADER_ID, EXPIRATION_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
SELECT
g_interface_type, -- INTERFACE_TYPE
'PON_AUC_GLOBAL_OP_LINE', -- ERROR_MESSAGE_NAME
p_request_id, -- REQUEST_ID
p_batch_id, -- BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
p_auction_header_id, -- AUCTION_HEADER_ID
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id -- LAST_UPDATE_LOGIN
FROM
PO_LINE_TYPES_B PLTB,
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PAIP.AUCTION_HEADER_ID=p_auction_header_id AND
PLTB.LINE_TYPE_ID= PAIP.LINE_TYPE_ID AND
PLTB.OUTSIDE_OPERATION_FLAG='Y' AND
ROWNUM=1;
SELECT line_number, document_disp_line_number,
project_id, project_task_id, project_expenditure_type,
project_exp_organization_id, project_expenditure_item_date
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE auction_header_id=p_auction_header_id
AND project_id IS NOT NULL
AND project_task_id IS NOT NULL
AND project_expenditure_type IS NOT NULL
AND project_exp_organization_id IS NOT NULL
AND project_expenditure_item_date IS NOT NULL;
SELECT pah.contract_type,
pah.progress_payment_type,
pah.recoupment_negotiable_flag,
pah.advance_negotiable_flag
INTO l_contract_type,
l_progress_payment_type,
l_recoupment_negotiable_flag,
l_advance_negotiable_flag
FROM pon_auction_headers_all pah
WHERE auction_header_id = p_auction_header_id;
INSERT ALL
WHEN NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
(order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS'))
OR po_outside_operation_flag = 'Y' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 1
entity_type, auction_header_id, line_number, -- 2
token1_name, token1_value, expiration_date, -- 3
created_by, creation_date, last_updated_by, -- 4
last_update_date, last_update_login, request_id -- 5
)
VALUES
(
g_interface_type, 'PON_LINE_TYPE_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
SELECT
paip.AUCTION_HEADER_ID,
paip.DOCUMENT_DISP_LINE_NUMBER,
paip.PURCHASE_BASIS,
paip.ORDER_TYPE_LOOKUP_CODE,
paip.line_number s_line_number,
plt.outside_operation_flag po_outside_operation_flag
FROM PON_AUCTION_ITEM_PRICES_ALL paip,
PO_LINE_TYPES plt
WHERE paip.auction_header_id = p_auction_header_id
AND paip.line_type_id = plt.line_type_id (+)
AND paip.group_type NOT IN ('GROUP','LOT_LINE');
INSERT ALL
WHEN retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RTNG_RATE_WRONG_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN max_retainage_amount IS NOT NULL AND max_retainage_amount < 0 THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_MAX_RTNG_WRONG_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN advance_amount IS NOT NULL AND advance_amount < 0 THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_ADV_AMT_WRONG_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN progress_pymt_rate_percent IS NOT NULL AND (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) then
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROG_PYMT_RATE_WRONG_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN recoupment_rate_percent IS NOT NULL AND (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RECOUP_RATE_WRONG_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN progress_pymt_rate_percent IS NOT NULL AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(progress_pymt_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id, -- 6
token2_name, token2_value
)
VALUES
(
g_interface_type, 'PON_INVALID_RATE_PRECISION_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'ATTRIBUTENAME', fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id, -- 6
'LINENUM', document_disp_line_number
)
WHEN recoupment_rate_percent IS NOT NULL AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(recoupment_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id, -- 6
token2_name, token2_value
)
VALUES
(
g_interface_type, 'PON_INVALID_RATE_PRECISION_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id, -- 6
'LINENUM', document_disp_line_number
)
WHEN retainage_rate_percent IS NOT NULL AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(retainage_rate_percent, 2) = 'F' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id, -- 6
token2_name, token2_value
)
VALUES
(
g_interface_type, 'PON_INVALID_RATE_PRECISION_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'ATTRIBUTENAME', fnd_message.get_string('PON','PON_RETAINAGE_RATE'), p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id, -- 6
'LINENUM', document_disp_line_number
)
WHEN advance_amount IS NOT NULL AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(advance_amount, p_fnd_currency_precision) = 'F' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id, -- 6
token2_name, token2_value
)
VALUES
(
g_interface_type, 'PON_LINEAMT_INVALID_PRECISION', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'ATTRIBUTENAME', fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id, -- 6
'LINENUM', document_disp_line_number
)
WHEN max_retainage_amount IS NOT NULL AND
PON_BID_VALIDATIONS_PKG.validate_currency_precision(max_retainage_amount, p_fnd_currency_precision) = 'F' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id, -- 6
token2_name, token2_value
)
VALUES
(
g_interface_type, 'PON_LINEAMT_INVALID_PRECISION', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'ATTRIBUTENAME', fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id, -- 6
'LINENUM', document_disp_line_number
)
WHEN l_progress_payment_type = 'FINANCE' AND progress_pymt_rate_percent IS NULL THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROG_PYMT_NEEDED_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN (l_progress_payment_type <> 'NONE' AND
(po_outside_operation_flag = 'Y' OR
NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
(order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS'))
)) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_LINE_TYPE_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN progress_pymt_rate_percent IS NOT NULL AND
recoupment_rate_percent IS NULL AND
l_recoupment_negotiable_flag = 'N' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RECUP_NEEDED_WITH_PPRATE_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN ((advance_amount IS NOT NULL or l_advance_negotiable_flag = 'Y') AND
(recoupment_rate_percent IS NULL AND l_recoupment_negotiable_flag = 'N')) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RECUP_NEEDED_WITH_ADVAMT_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN target_price IS NOT NULL AND advance_amount IS NOT NULL
AND (advance_amount > nvl(s_quantity,1) * target_price) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_ADV_AMT_MORE_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN l_progress_payment_type = 'ACTUAL' AND recoupment_rate_percent IS NOT NULL
AND target_price IS NOT NULL
AND advance_amount IS NOT NULL
AND (recoupment_rate_percent < (advance_amount * 100)/(nvl(s_quantity,1) * target_price)) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RECOUP_LESS_THAN_ADV_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN l_progress_payment_type = 'FINANCE' AND recoupment_rate_percent IS NOT NULL
AND target_price IS NOT NULL AND progress_pymt_rate_percent IS NOT NULL
AND recoupment_rate_percent < (((((progress_pymt_rate_percent/100) * (SELECT nvl(sum(nvl(p_aps.target_price,0)*nvl(p_aps.quantity,nvl(s_quantity,1))),0)
FROM PON_AUC_PAYMENTS_SHIPMENTS p_aps
WHERE p_aps.auction_header_id=p_auction_header_id
AND p_aps.line_number=s_line_number ))
+ NVL(advance_amount,0)) * 100)/((nvl(s_quantity, 1) * target_price))) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_RECOUP_LESS_THAN_PYMT_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN (pai_project_id IS NOT NULL OR pai_project_task_id IS NOT NULL OR pai_project_exp_org_id IS NOT NULL
OR pai_project_exp_item_date IS NOT NULL OR pai_project_exp_type IS NOT NULL)
AND (pai_project_id IS NULL OR pai_project_task_id IS NULL OR pai_project_exp_org_id IS NULL
OR pai_project_exp_item_date IS NULL OR pai_project_exp_type IS NULL) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_INFO_INCOMPLETE_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
SELECT
paip.AUCTION_HEADER_ID,
paip.LINE_NUMBER s_line_number,
paip.DOCUMENT_DISP_LINE_NUMBER,
paip.ADVANCE_AMOUNT,
paip.RECOUPMENT_RATE_PERCENT,
paip.PROGRESS_PYMT_RATE_PERCENT,
paip.RETAINAGE_RATE_PERCENT,
paip.MAX_RETAINAGE_AMOUNT,
paip.TARGET_PRICE,
paip.QUANTITY s_quantity,
paip.PROJECT_ID pai_project_id,
paip.PROJECT_TASK_ID pai_project_task_id,
paip.PROJECT_AWARD_ID pai_project_award_id,
paip.PROJECT_EXPENDITURE_TYPE pai_project_exp_type,
paip.PROJECT_EXP_ORGANIZATION_ID pai_project_exp_org_id,
paip.PROJECT_EXPENDITURE_ITEM_DATE pai_project_exp_item_date,
paip.PURCHASE_BASIS,
paip.ORDER_TYPE_LOOKUP_CODE,
paip.LINE_ORIGINATION_CODE,
paip.has_payments_flag,
plt.outside_operation_flag po_outside_operation_flag
FROM PON_AUCTION_ITEM_PRICES_ALL paip,
PO_LINE_TYPES plt
WHERE paip.auction_header_id = p_auction_header_id
AND paip.line_type_id = plt.line_type_id (+)
AND paip.group_type NOT IN ('GROUP','LOT_LINE');
INSERT ALL
WHEN work_approver_user_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PER_WORKFORCE_CURRENT_X peo,
FND_USER fu
WHERE fu.user_id = work_approver_user_id
AND fu.employee_id = peo.person_id
AND SYSDATE >= nvl(fu.start_date, SYSDATE)
AND SYSDATE <= nvl(fu.end_date, SYSDATE) ) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_LIN_OWNER_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
SELECT
paip.AUCTION_HEADER_ID,
paip.LINE_NUMBER s_line_number,
paip.DOCUMENT_DISP_LINE_NUMBER,
paip.WORK_APPROVER_USER_ID
FROM PON_AUCTION_ITEM_PRICES_ALL paip
WHERE paip.auction_header_id = p_auction_header_id
AND paip.WORK_APPROVER_USER_ID IS NOT NULL;
INSERT ALL
WHEN pai_project_id IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM pa_projects_expend_v pro
WHERE pro.project_id = pai_project_id) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_NUM_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN pai_project_id IS NOT NULL AND pai_project_task_id IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM pa_tasks_expend_v tas
WHERE tas.project_id = pai_project_id
AND tas.task_id = pai_project_task_id) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_TASK_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN pai_project_id IS NOT NULL
AND pai_project_task_id IS NOT NULL
AND pai_project_award_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM GMS_AWARDS_BASIC_V award
WHERE award.project_id = pai_project_id
AND award.task_id = pai_project_task_id
AND award.award_id = pai_project_award_id) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_AWARD_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN pai_project_exp_org_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PA_ORGANIZATIONS_EXPEND_V porg
WHERE porg.organization_id = pai_project_exp_org_id) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_EXPORG_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN pai_project_exp_type IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM pa_expenditure_types_expend_v exptype
WHERE system_linkage_function = 'VI'
AND exptype.expenditure_type = pai_project_exp_type
AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_EXPTYPE_INVALID_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
WHEN pai_project_id IS NOT NULL
AND pai_project_award_id IS NULL
AND IS_PROJECT_SPONSORED(pai_project_id) = 'Y' THEN
INTO pon_interface_errors
(
interface_type, error_message_name, batch_id, -- 2
entity_type, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login, request_id -- 6
)
VALUES
(
g_interface_type, 'PON_PROJ_AWARD_NULL_L', p_batch_id, -- 2
g_auction_item_type, auction_header_id, s_line_number, -- 3
'LINENUM', document_disp_line_number, p_expiration_date, -- 4
p_user_id, SYSDATE, p_user_id, -- 5
SYSDATE, p_login_id, p_request_id -- 6
)
SELECT
paip.AUCTION_HEADER_ID,
paip.LINE_NUMBER s_line_number,
paip.DOCUMENT_DISP_LINE_NUMBER,
paip.PROJECT_ID pai_project_id,
paip.PROJECT_TASK_ID pai_project_task_id,
paip.PROJECT_AWARD_ID pai_project_award_id,
paip.PROJECT_EXPENDITURE_TYPE pai_project_exp_type,
paip.PROJECT_EXP_ORGANIZATION_ID pai_project_exp_org_id,
paip.PROJECT_EXPENDITURE_ITEM_DATE pai_project_exp_item_date
FROM PON_AUCTION_ITEM_PRICES_ALL paip
WHERE paip.auction_header_id = p_auction_header_id
AND paip.PROJECT_ID IS NOT NULL
AND paip.PROJECT_TASK_ID IS NOT NULL
AND paip.PROJECT_EXPENDITURE_TYPE IS NOT NULL
AND paip.PROJECT_EXP_ORGANIZATION_ID IS NOT NULL
AND paip.PROJECT_EXPENDITURE_ITEM_DATE IS NOT NULL;
INSERT INTO PON_INTERFACE_ERRORS
(
interface_type,
error_message_name,
batch_id,
entity_type,
auction_header_id,
line_number,
token1_name,
token1_value,
token2_name,
token2_value,
expiration_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id
)
SELECT
g_interface_type, --INTERFACE_TYPE
'PON_PYMT_NUM_NOT_UNQ', -- ERROR_MESSAGE_NAME
p_batch_id, --BATCH_ID
g_auction_item_type, -- ENTITY_TYPE
paps.auction_header_id, -- AUCTION_HEADER_ID
paps.line_number, -- LINE_NUMBER
'PAYITEMNUM', --Token1
paps.payment_display_number, --token 1 value
'LINENUM', --Token2
pai.document_disp_line_number, --token 2 value
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
p_login_id, -- LAST_UPDATE_LOGIN
p_request_id --REQUEST_ID
FROM
PON_AUC_PAYMENTS_SHIPMENTS paps, PON_AUCTION_ITEM_PRICES_ALL pai
WHERE paps.auction_header_id=pai.auction_header_id
AND paps.line_number = pai.line_number
AND paps.auction_header_id = p_auction_header_id
GROUP BY paps.auction_header_id, paps.line_number,
paps.payment_display_number, pai.document_disp_line_number
HAVING count(*) > 1;
SELECT paps.payment_display_number, paps.payment_id,
paps.project_id, paps.project_task_id, paps.project_expenditure_type,
paps.project_exp_organization_id, paps.project_expenditure_item_date,
paip.line_number, paip.document_disp_line_number
FROM PON_AUC_PAYMENTS_SHIPMENTS paps,
PON_AUCTION_ITEM_PRICES_ALL paip
WHERE paps.auction_header_id=p_auction_header_id
AND paps.project_id IS NOT NULL
AND paps.project_task_id IS NOT NULL
AND paps.project_expenditure_type IS NOT NULL
AND paps.project_exp_organization_id IS NOT NULL
AND paps.project_expenditure_item_date IS NOT NULL
AND paip.auction_header_id = paps.auction_header_id
AND paip.line_number = paps.line_number;
INSERT ALL
WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_PAYITEM_NUMBER', NULL, 'PON_PYMT_NUM_WRONG', -- 1
'NUM', payment_display_number, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_type_code = 'RATE' AND quantity < 0 THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_QUANTITY', NULL, 'PON_PYMT_QTY_WRONG', -- 1
'NUM', quantity, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN target_price IS NOT NULL AND target_price < 0 THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_TARGET_PRICE', NULL, 'PON_PYMT_TPRICE_WRONG', -- 1
'NUM', target_price, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN target_price IS NOT NULL
AND PON_BID_VALIDATIONS_PKG.validate_price_precision(target_price, p_price_precision) = 'F' THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_TARGET_PRICE', NULL, 'PON_TARGETPRICE_INVALID_PREC_P', -- 1
'NUM', target_price, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_display_number IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_PAYITEM_NUMBER', NULL, 'PON_PYMT_NUM_MISSING', -- 1
'NUM', payment_display_number, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_type_code IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_PAYITEM_TYPE', NULL, 'PON_PYMT_TYPE_NULL', -- 1
'TXT', payment_type_code, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_description IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_PAYMENT_DESC', NULL, 'PON_PYMT_DESC_NULL', -- 1
'TXT', payment_description, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_type_code = 'RATE' AND quantity IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_QUANTITY', NULL, 'PON_PYMT_QTY_NULL', -- 1
'NUM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN payment_type_code = 'RATE' AND uom_code IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PYMT_UOM_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN nvl(supplier_enterable_pymt_flag,'N') = 'N' AND ship_to_location_id IS NULL THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PYMT_SHIPTO_NULL', -- 1
'TXT', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN need_by_date IS NOT NULL AND need_by_date <= pah_close_bidding_date THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
'PON_AUCTS_NEEDBY', NULL, 'PON_PYMT_NDATE_LESS_CDATE', -- 1
'TIM', NULL, need_by_date, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN work_approver_user_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PER_WORKFORCE_CURRENT_X peo,
FND_USER fu
WHERE fu.user_id = work_approver_user_id
AND fu.employee_id = peo.person_id
AND SYSDATE >= nvl(fu.start_date, SYSDATE)
AND SYSDATE <= nvl(fu.end_date, SYSDATE) ) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PYMT_OWNER_INVALID', -- 1
'TIM', work_approver_user_id, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN (s_project_id IS NOT NULL OR s_project_task_id IS NOT NULL OR s_project_exp_org_id IS NOT NULL
OR s_project_exp_item_date IS NOT NULL OR s_project_exp_type IS NOT NULL)
AND (s_project_id IS NULL OR s_project_task_id IS NULL OR s_project_exp_org_id IS NULL
OR s_project_exp_item_date IS NULL OR s_project_exp_type IS NULL) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_INFO_INCOMPLETE_P', -- 1
'TXT', s_project_exp_type, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
SELECT
ppi.project_id s_project_id,
ppi.project_task_id s_project_task_id,
ppi.project_expenditure_type s_project_exp_type,
ppi.project_exp_organization_id s_project_exp_org_id,
ppi.project_expenditure_item_date s_project_exp_item_date,
ppi.project_award_id s_project_award_id,
ppi.payment_display_number,
ppi.payment_type_code,
ppi.uom_code,
ppi.auction_header_id auction_header_id,
pai.document_disp_line_number,
ppi.work_approver_user_id,
ppi.ship_to_location_id,
ppi.target_price,
ppi.quantity,
ppi.payment_description,
ppi.need_by_date,
pah.close_bidding_date pah_close_bidding_date,
pah.supplier_enterable_pymt_flag,
pai.line_number s_line_number
FROM PON_AUC_PAYMENTS_SHIPMENTS ppi,
PON_AUCTION_ITEM_PRICES_ALL pai,
PON_AUCTION_HEADERS_ALL pah
WHERE ppi.auction_header_id = pai.auction_header_id
AND ppi.line_number = pai.line_number
AND pah.auction_header_id = ppi.auction_header_id
AND ppi.auction_header_id = p_auction_header_id;
INSERT ALL
WHEN s_project_id IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM pa_projects_expend_v pro
WHERE pro.project_id = s_project_id) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_NUM_INVALID_P', -- 1
'TIM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN s_project_id IS NOT NULL AND s_project_task_id IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM pa_tasks_expend_v tas
WHERE tas.project_id = s_project_id
AND tas.task_id = s_project_task_id) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_TASK_INVALID_P', -- 1
'TIM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN s_project_id IS NOT NULL
AND s_project_task_id IS NOT NULL
AND s_project_award_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM GMS_AWARDS_BASIC_V award
WHERE award.project_id = s_project_id
AND award.task_id = s_project_task_id
AND award.award_id = s_project_award_id) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_AWARD_INVALID_P', -- 1
'TIM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN s_project_id IS NOT NULL
AND s_project_award_id IS NULL
AND IS_PROJECT_SPONSORED(s_project_id) = 'Y' THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_AWARD_NULL_P', -- 1
'TIM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN s_project_exp_org_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PA_ORGANIZATIONS_EXPEND_V porg
WHERE porg.organization_id = s_project_exp_org_id) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value_number, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_EXPORG_INVALID_P', -- 1
'TIM', NULL, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
WHEN s_project_exp_type IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM pa_expenditure_types_expend_v exptype
WHERE system_linkage_function = 'VI'
AND exptype.expenditure_type = s_project_exp_type
AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
INTO pon_interface_errors
(
column_name, entity_attr_name, error_message_name, -- 1
error_value_datatype, error_value, error_value_date, -- 2
token1_name, token1_value, token2_name, -- 3
token2_value, interface_type, table_name, -- 4
batch_id, interface_line_id, entity_type, -- 5
auction_header_id, line_number, payment_id, -- 6
expiration_date, created_by, creation_date, -- 7
last_updated_by, last_update_date, last_update_login -- 8
)
VALUES
(
NULL, NULL, 'PON_PROJ_EXPTYPE_INVALID_P', -- 1
'TXT', s_project_exp_type, NULL, -- 2
'LINENUM', document_disp_line_number, 'PAYITEMNUM', -- 3
payment_display_number, g_interface_type, 'PON_AUC_PAYMENTS_INTERFACE', -- 4
p_batch_id, NULL, g_rfq_pymts_type, -- 5
auction_header_id, s_line_number, NULL, -- 6
p_expiration_date, p_user_id, SYSDATE, -- 7
p_user_id, SYSDATE, p_login_id -- 8
)
SELECT
ppi.project_id s_project_id,
ppi.project_task_id s_project_task_id,
ppi.project_expenditure_type s_project_exp_type,
ppi.project_exp_organization_id s_project_exp_org_id,
ppi.project_expenditure_item_date s_project_exp_item_date,
ppi.project_award_id s_project_award_id,
ppi.payment_display_number,
ppi.auction_header_id auction_header_id,
pai.document_disp_line_number,
ppi.work_approver_user_id,
pai.line_number s_line_number
FROM PON_AUC_PAYMENTS_SHIPMENTS ppi,
PON_AUCTION_ITEM_PRICES_ALL pai,
PON_AUCTION_HEADERS_ALL pah
WHERE ppi.auction_header_id = pai.auction_header_id
AND ppi.line_number = pai.line_number
AND ppi.auction_header_id = p_auction_header_id
AND pah.auction_header_id = pai.auction_header_id
AND ppi.project_id IS NOT NULL
AND ppi.project_task_id IS NOT NULL
AND ppi.project_expenditure_type IS NOT NULL
AND ppi.project_exp_organization_id IS NOT NULL
AND ppi.project_expenditure_item_date IS NOT NULL;
SELECT
doctype_id,
doctype_group_name
BULK COLLECT INTO
l_doctype_id,
l_doctype_group_name
FROM
pon_auc_doctypes;
SELECT
BIZRULE.NAME,
DOCTYPE_RULE.REQUIRED_FLAG,
DOCTYPE_RULE.VALIDITY_FLAG
BULK COLLECT INTO
l_bizrule_name,
l_doctype_rule_required,
l_doctype_rule_validity
FROM
PON_AUC_BIZRULES BIZRULE,
PON_AUC_DOCTYPE_RULES DOCTYPE_RULE
WHERE
BIZRULE.BIZRULE_ID = DOCTYPE_RULE.BIZRULE_ID AND
DOCTYPE_RULE.DOCTYPE_ID = p_doctype_id;
SELECT
LINE_NUMBER
INTO
l_line_number
FROM
PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = p_auction_header_id AND
PURCHASE_BASIS = 'TEMP LABOR' AND
ROWNUM = 1;
UPDATE
PON_AUCTION_ITEM_PRICES_ALL PAIP
SET
HAS_ATTRIBUTES_FLAG = NVL(
(SELECT 'Y'
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1), 'N'),
HAS_SHIPMENTS_FLAG = NVL (
(SELECT 'Y'
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND shipment_type = 'PRICE BREAK'
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1),'N'),
HAS_PRICE_ELEMENTS_FLAG = NVL (
(SELECT 'Y'
FROM PON_PRICE_ELEMENTS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND PF_TYPE = 'SUPPLIER'
AND ROWNUM = 1), 'N'),
HAS_BUYER_PFS_FLAG = NVL (
(SELECT 'Y'
FROM PON_PRICE_ELEMENTS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1
AND PF_TYPE = 'BUYER'),'N'),
HAS_PRICE_DIFFERENTIALS_FLAG = NVL (
(SELECT 'Y'
FROM PON_PRICE_DIFFERENTIALS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND SHIPMENT_NUMBER = -1
AND ROWNUM = 1),'N'),
HAS_QUANTITY_TIERS = NVL (
(SELECT 'Y'
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND shipment_type = 'QUANTITY BASED'
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1),'N'),
CLOSE_BIDDING_DATE = p_close_bidding_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID(),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID()
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
SELECT MAX (DISP_LINE_NUMBER)
INTO x_max_disp_line_number
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND PARENT_LINE_NUMBER = p_parent_line_number;
SELECT DISP_LINE_NUMBER
INTO x_max_disp_line_number
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = p_parent_line_number;
SELECT PON_ITEM_PRICES_INTERFACE_S.NEXTVAL
INTO l_batch_id
FROM DUAL;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE,
ERROR_MESSAGE_NAME,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
REQUEST_ID,
AUCTION_HEADER_ID,
EXPIRATION_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
p_interface_type, --INTERFACE_TYPE
p_error_message_name, --ERROR_MESSAGE_NAME
p_token1_name, -- TOKEN1_NAME
p_token1_value, --TOKEN1_VALUE
p_token2_name, --TOKEN2_NAME
p_token2_value, -- TOKEN2_VALUE
p_request_id, -- REQUEST_ID
p_auction_header_id, -- AUCTION_HEADER_ID
p_expiration_date, -- EXPIRATION_DATE
p_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
p_user_id, -- LAST_UPDATED_BY
sysdate, -- LAST_UPDATE_DATE
FND_GLOBAL.LOGIN_ID -- LAST_UPDATE_LOGIN
);
SELECT
BATCH_ID
INTO
l_batch_id
FROM
PON_INTERFACE_ERRORS
WHERE
BATCH_ID = p_batch_id AND
ROWNUM =1;
l_last_updated_by PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
l_last_update_date PON_NEG_COPY_DATATYPES_GRP.SIMPLE_DATE_TYPE;
l_last_update_login PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT
INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
ERROR_VALUE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
PRICE_ELEMENT_TYPE_ID,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
TOKEN3_NAME,
TOKEN3_VALUE,
TOKEN4_NAME,
TOKEN4_VALUE,
TOKEN5_NAME,
TOKEN5_VALUE,
PAYMENT_ID,
BID_PAYMENT_ID
BULK COLLECT INTO
l_interface_type,
l_column_name,
l_table_name,
l_interface_line_id,
l_error_message_name,
l_error_value,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_request_id,
l_entity_type,
l_entity_attr_name,
l_error_value_date,
l_error_value_number,
l_error_value_datatype,
l_auction_header_id,
l_bid_number,
l_line_number,
l_attribute_name,
l_price_element_type_id,
l_shipment_number,
l_price_differential_number,
l_expiration_date,
l_token1_name,
l_token1_value,
l_token2_name,
l_token2_value,
l_token3_name,
l_token3_value,
l_token4_name,
l_token4_value,
l_token5_name,
l_token5_value,
l_payment_id,
l_bid_payment_id
FROM
PON_INTERFACE_ERRORS
WHERE
BATCH_ID = p_batch_id;
INSERT INTO PON_INTERFACE_ERRORS
(
INTERFACE_TYPE,
COLUMN_NAME,
TABLE_NAME,
BATCH_ID,
INTERFACE_LINE_ID,
ERROR_MESSAGE_NAME,
ERROR_VALUE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
ENTITY_TYPE,
ENTITY_ATTR_NAME,
ERROR_VALUE_DATE,
ERROR_VALUE_NUMBER,
ERROR_VALUE_DATATYPE,
AUCTION_HEADER_ID,
BID_NUMBER,
LINE_NUMBER,
ATTRIBUTE_NAME,
PRICE_ELEMENT_TYPE_ID,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
EXPIRATION_DATE,
TOKEN1_NAME,
TOKEN1_VALUE,
TOKEN2_NAME,
TOKEN2_VALUE,
TOKEN3_NAME,
TOKEN3_VALUE,
TOKEN4_NAME,
TOKEN4_VALUE,
TOKEN5_NAME,
TOKEN5_VALUE,
PAYMENT_ID,
BID_PAYMENT_ID
)
VALUES
(
l_interface_type (x),
l_column_name (x),
l_table_name (x),
p_batch_id,
l_interface_line_id (x),
l_error_message_name (x),
l_error_value (x),
l_created_by (x),
l_creation_date (x),
l_last_updated_by (x),
l_last_update_date (x),
l_last_update_login (x),
l_request_id (x),
l_entity_type (x),
l_entity_attr_name (x),
l_error_value_date (x),
l_error_value_number (x),
l_error_value_datatype (x),
l_auction_header_id (x),
l_bid_number (x),
l_line_number (x),
l_attribute_name (x),
l_price_element_type_id (x),
l_shipment_number (x),
l_price_differential_number (x),
l_expiration_date (x),
l_token1_name (x),
l_token1_value (x),
l_token2_name (x),
l_token2_value (x),
l_token3_name (x),
l_token3_value (x),
l_token4_name (x),
l_token4_value (x),
l_token5_name (x),
l_token5_value (x),
l_payment_id (x),
l_bid_payment_id (x)
);
PROCEDURE update_line_flag_seq_closedate (
x_result OUT NOCOPY VARCHAR,
x_error_code OUT NOCOPY VARCHAR,
x_error_message OUT NOCOPY VARCHAR,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_auction_header_id IN NUMBER,
p_close_bidding_date IN DATE,
p_stag_closing_enabled_flag IN VARCHAR,
p_curr_from_line_number IN NUMBER,
p_curr_to_line_number IN NUMBER
) IS
l_module_name VARCHAR2 (30);
l_module_name := 'update_line_flag_seq_closedate';
UPDATE pon_auction_item_prices_all paip
SET
-- the max sub line sequence number is set to the greatest
-- max_sub_line_sequence_number between this round and the
-- previous round
max_sub_line_sequence_number = GREATEST (
(
SELECT
NVL (MAX (sub_line_sequence_number),0)
FROM
pon_auction_item_prices_all
WHERE
auction_header_id=p_auction_header_id AND
parent_line_number = paip.line_number AND
group_type IN ('LOT_LINE', 'GROUP_LINE')
), NVL(max_sub_line_sequence_number,0))
WHERE
auction_header_id = p_auction_header_id AND
line_number >= p_curr_from_line_number AND
line_number <= p_curr_to_line_number AND
group_type IN ('LOT', 'GROUP');
UPDATE pon_auction_item_prices_all paip
SET
close_bidding_date = decode(p_stag_closing_enabled_flag, 'Y', close_bidding_date, p_close_bidding_date),
-- If the line has any attributes this flag will be Y else N
has_attributes_flag = NVL(
(SELECT 'Y'
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1), 'N'),
-- If the line has any shipments this flag will be Y else N
has_shipments_flag = NVL (
(SELECT 'Y'
FROM pon_auction_shipments_all
WHERE auction_header_id = p_auction_header_id
AND line_number = paip.line_number
AND shipment_type = 'PRICE BREAK'
AND rownum = 1),'N'),
-- If the line has any supplier price factors this flag will be Y else N
has_price_elements_flag = NVL (
(SELECT 'Y'
FROM pon_price_elements
WHERE auction_header_id = p_auction_header_id
AND line_number = paip.line_number
AND pf_type = 'SUPPLIER'
AND rownum = 1), 'N'),
-- If the line has any buyer price factors this flag will be Y else N
has_buyer_pfs_flag = NVL (
(SELECT 'Y'
FROM pon_price_elements
WHERE auction_header_id = p_auction_header_id
AND line_number = paip.line_number
AND pf_type = 'BUYER'
AND rownum = 1),'N'),
-- If the line has any price differentials this flag will be Y else N
has_price_differentials_flag = NVL (
(SELECT 'Y'
FROM pon_price_differentials
WHERE auction_header_id = p_auction_header_id
AND line_number = paip.line_number
AND shipment_number = -1
AND rownum = 1),'N'),
--complex work - If the line has any payments this flag will be Y else N
has_Payments_flag = NVL(
(SELECT 'Y'
FROM PON_AUC_PAYMENTS_SHIPMENTS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1), 'N'),
--Quantity tiers project - If the line has any quantity based price tiers this flag will be Y else N
has_quantity_tiers = NVL (
(SELECT 'Y'
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND shipment_type = 'QUANTITY BASED'
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND ROWNUM = 1),'N'),
-- Is quantity scored flag is set to Y in case quantity there is a line
-- attribute for quantity (sequence_number = QUANTITY_SEQ_NUMBER
is_quantity_scored = NVL (
(SELECT 'Y'
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND SEQUENCE_NUMBER = -20
AND ROWNUM = 1), 'N'),
-- Is quantity need by date scored flag is set to Y in case there is a line
-- attribute for need by date (sequence_number = NEED_BY_DATE_SEQ_NUMBER
is_need_by_date_scored = NVL (
(SELECT 'Y'
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND LINE_NUMBER = PAIP.LINE_NUMBER
AND SEQUENCE_NUMBER = -10
AND ROWNUM = 1), 'N'),
-- standard who columns
last_update_date = sysdate,
last_update_login = p_login_id,
last_updated_by = p_user_id
WHERE
auction_header_id = p_auction_header_id AND
line_number >= p_curr_from_line_number AND
line_number <= p_curr_to_line_number;
END update_line_flag_seq_closedate;
SELECT
auction_header_id,
line_number,
order_type_lookup_code,
unit_target_price,
unit_display_target_flag
FROM
pon_auction_item_prices_all
WHERE
auction_header_id = p_auction_header_id AND
(has_price_elements_flag = 'Y' OR has_buyer_pfs_flag = 'Y') AND
line_number >= p_from_line_number AND
line_number <= p_to_line_number
)paip
ON
(
paip.auction_header_id = ppe.auction_header_id AND
paip.line_number = ppe.line_number AND
ppe.price_element_type_id = -10
)
WHEN MATCHED THEN UPDATE
SET
pricing_basis = decode(paip.order_type_lookup_code, 'FIXED PRICE', 'FIXED_AMOUNT', 'PER_UNIT'),
value = paip.unit_target_price,
display_target_flag = unit_display_target_flag,
last_update_date = sysdate,
last_updated_by = p_user_id
WHEN NOT MATCHED THEN INSERT
(
auction_header_id,
line_number,
list_id,
price_element_type_id,
pricing_basis,
value,
display_target_flag,
sequence_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
pf_type,
display_to_suppliers_flag
)
VALUES
(
paip.auction_header_id,
paip.line_number,
-1,
-10,
decode(paip.order_type_lookup_code, 'FIXED PRICE', 'FIXED_AMOUNT', 'PER_UNIT'),
paip.unit_target_price,
paip.unit_display_target_flag,
-10,
sysdate,
p_user_id,
sysdate,
p_user_id,
'SUPPLIER',
'Y'
);
SELECT
paip.auction_header_id,
paip.line_number,
pbp.trading_partner_id,
pbp.vendor_site_id,
pbp.requested_supplier_id,
sum(decode(ppe.pricing_basis, 'PER_UNIT', ppsv.value, 0)) unit_price,
sum(decode(ppe.pricing_basis, 'FIXED_AMOUNT', ppsv.value, 0)) fixed_amount,
1 + sum(decode(ppe.pricing_basis, 'PERCENTAGE', ppsv.value/100, 0)) percentage
from
pon_auction_item_prices_all paip,
pon_bidding_parties pbp,
pon_pf_supplier_values ppsv,
pon_price_elements ppe
where
paip.auction_header_id = p_auction_header_id and
pbp.auction_header_id = paip.auction_header_id and
pbp.auction_header_id = ppsv.auction_header_id and
paip.line_number >= p_from_line_number and
paip.line_number <= p_to_line_number and
pbp.sequence = ppsv.supplier_seq_number and
paip.line_number = ppsv.line_number and
ppsv.auction_header_id = ppe.auction_header_id and
ppsv.line_number = ppe.line_number and
ppsv.pf_seq_number = ppe.sequence_number
group by
paip.auction_header_id,
paip.line_number,
pbp.trading_partner_id,
pbp.vendor_site_id,
pbp.requested_supplier_id
) pfsdata
ON
(
pfsdata.auction_header_id = ppsf.auction_header_id and
pfsdata.line_number = ppsf.line_number and
(pfsdata.trading_partner_id = ppsf.trading_partner_id
OR pfsdata.requested_supplier_id = ppsf.requested_supplier_id) and
pfsdata.vendor_site_id = ppsf.vendor_site_id
)
WHEN MATCHED THEN UPDATE
SET
unit_price = pfsdata.unit_price,
fixed_amount = pfsdata.fixed_amount,
percentage = pfsdata.percentage,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id
WHEN NOT MATCHED THEN INSERT
(
auction_header_id,
line_number,
trading_partner_id,
vendor_site_id,
requested_supplier_id,
unit_price,
fixed_amount,
percentage,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
pfsdata.auction_header_id,
pfsdata.line_number,
pfsdata.trading_partner_id,
pfsdata.vendor_site_id,
pfsdata.requested_supplier_id,
pfsdata.unit_price,
pfsdata.fixed_amount,
pfsdata.percentage,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id
);
message => 'Update Payments Fields = ' || l_module_name);
UPDATE pon_auc_payments_shipments pay
SET
SHIP_TO_LOCATION_ID = null,
WORK_APPROVER_USER_ID = null,
NOTE_TO_BIDDERS = null,
PROJECT_ID = null,
PROJECT_TASK_ID = null,
PROJECT_AWARD_ID = null,
PROJECT_EXPENDITURE_TYPE = null,
PROJECT_EXP_ORGANIZATION_ID = null,
PROJECT_EXPENDITURE_ITEM_DATE = null,
-- standard who columns
last_update_date = sysdate,
last_update_login = p_login_id,
last_updated_by = p_user_id
WHERE
auction_header_id = p_auction_header_id AND
line_number >= p_curr_from_line_number AND
line_number <= p_curr_to_line_number;
message => 'Before call Delete_Payment_Attachments = ' || l_module_name);
PON_NEGOTIATION_HELPER_PVT.Delete_Payment_Attachments(
p_auction_header_id => p_auction_header_id,
p_curr_from_line_number => p_curr_from_line_number,
p_curr_to_line_number => p_curr_to_line_number);
message => 'After Call Delete_Payment_Attachments = ' || l_module_name);
SELECT NVL (MAX (line_number), 0)
INTO l_max_line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;
message => 'Update Payments Fields = ' || l_module_name);
PROCEDURE update_header_before_publish (
x_result OUT NOCOPY VARCHAR,
x_error_code OUT NOCOPY VARCHAR,
x_error_message OUT NOCOPY VARCHAR,
p_auction_header_id IN NUMBER
) IS
l_line_number NUMBER;
l_module_name := 'update_header_before_publish';
SELECT 'YES'
INTO l_attributes_exist
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND ROWNUM = 1;
SELECT 'BOTH'
INTO l_attributes_exist
FROM PON_AUCTION_ATTRIBUTES
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND MANDATORY_FLAG = 'Y'
AND ROWNUM = 1;
SELECT 'Y'
INTO l_has_price_elements
FROM PON_PRICE_ELEMENTS
WHERE AUCTION_HEADER_ID = p_auction_header_id
AND ROWNUM = 1;
SELECT 'N'
INTO l_has_pe_for_all_items
FROM PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE PAIP.AUCTION_HEADER_ID = P_AUCTION_HEADER_ID AND
HAS_PRICE_ELEMENTS_FLAG = 'N' AND
HAS_BUYER_PFS_FLAG = 'N' AND
ROWNUM = 1;
UPDATE
PON_AUCTION_HEADERS_ALL PAHA
SET
ATTRIBUTES_EXIST = l_attributes_exist,
HAS_PRICE_ELEMENTS = l_has_price_elements,
HAS_PE_FOR_ALL_ITEMS = l_has_pe_for_all_items,
MAX_INTERNAL_LINE_NUM = GREATEST (NVL (PAHA.MAX_INTERNAL_LINE_NUM,0),
(SELECT
MAX(PAIP.LINE_NUMBER)
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PAIP.AUCTION_HEADER_ID = p_auction_header_id)),
MAX_DOCUMENT_LINE_NUM = GREATEST (NVL (PAHA.MAX_DOCUMENT_LINE_NUM,0),
(SELECT
MAX(PAIP.SUB_LINE_SEQUENCE_NUMBER)
FROM
PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE
PAIP.GROUP_TYPE IN ('LOT', 'GROUP', 'LINE')
AND PAIP.AUCTION_HEADER_ID = p_auction_header_id))
WHERE
PAHA.AUCTION_HEADER_ID = p_auction_header_id;
END update_header_before_publish;
PROCEDURE update_lines_before_publish (
x_result OUT NOCOPY VARCHAR,
x_error_code OUT NOCOPY VARCHAR,
x_error_message OUT NOCOPY VARCHAR,
p_auction_header_id IN NUMBER,
p_close_bidding_date IN DATE,
p_user_id IN NUMBER,
p_login_id IN NUMBER
) IS
l_large_neg_enabled_flag VARCHAR2(2);
l_module_name := 'update_lines_before_publish';
SELECT large_neg_enabled_flag, complete_flag, supplier_enterable_pymt_flag,
nvl2(staggered_closing_interval,'Y','N')
INTO l_large_neg_enabled_flag, l_complete_flag, l_supplier_payments,
l_stag_closing_enabled_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
SELECT NVL (MAX (line_number), 0)
INTO l_max_line_number
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id;
DELETE FROM
pon_pf_supplier_values
WHERE
auction_header_id = p_auction_header_id;
DELETE FROM
pon_price_elements
WHERE
auction_header_id = p_auction_header_id and
price_element_type_id = -10;
DELETE FROM
pon_pf_supplier_formula
WHERE
auction_header_id = p_auction_header_id;
' and update_line_flag_seq_closedate with' ||
' p_auction_header_id = ' || p_auction_header_id ||
', p_user_id = ' || p_user_id ||
', p_login_id = ' || p_login_id ||
', p_curr_from_line_number = ' || l_batch_start ||
', p_curr_to_line_number = ' || l_batch_end);
update_line_flag_seq_closedate (
x_result => x_result,
x_error_code => x_error_code,
x_error_message => x_error_message,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_auction_header_id => p_auction_header_id,
p_close_bidding_date => p_close_bidding_date,
p_stag_closing_enabled_flag => l_stag_closing_enabled_flag,
p_curr_from_line_number => l_batch_start,
p_curr_to_line_number => l_batch_end);
END update_lines_before_publish;
PROCEDURE update_before_publish (
x_result OUT NOCOPY VARCHAR2,
x_error_code OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2,
p_auction_header_id IN NUMBER,
p_close_bidding_date IN DATE,
p_user_id IN NUMBER,
p_login_id IN NUMBER
) IS
l_module_name VARCHAR2 (30);
l_module_name := 'update_before_publish';
UPDATE
pon_auction_headers_all
SET
auction_status = 'DRAFT',
last_updated_by = p_user_id,
last_update_date = sysdate
WHERE
auction_header_id = p_auction_header_id;
update_lines_before_publish (
x_result,
x_error_code,
x_error_message,
p_auction_header_id,
p_close_bidding_date,
p_user_id,
p_login_id);
update_header_before_publish (
x_result,
x_error_code,
x_error_message,
p_auction_header_id);
UPDATE
pon_auction_headers_all
SET
auction_status = 'ACTIVE',
last_updated_by = p_user_id,
last_update_date = sysdate
WHERE
auction_header_id = p_auction_header_id;
END update_before_publish;
SELECT
DRAFT_LOCKED,
DRAFT_LOCKED_BY_CONTACT_ID,
DOCTYPE_ID,
NUMBER_PRICE_DECIMALS,
CLOSE_BIDDING_DATE,
CONTRACT_TYPE,
GLOBAL_AGREEMENT_FLAG,
ALLOW_OTHER_BID_CURRENCY_FLAG,
BID_RANKING,
PO_START_DATE,
PO_END_DATE,
TRADING_PARTNER_ID,
FULL_QUANTITY_BID_CODE,
BID_LIST_TYPE,
AMENDMENT_NUMBER,
AUCTION_ROUND_NUMBER,
DOCUMENT_NUMBER,
AUCTION_HEADER_ID_PREV_ROUND,
AUCTION_HEADER_ID_PREV_AMEND,
AUCTION_ORIGINATION_CODE,
AUCTION_TITLE,
TRADING_PARTNER_CONTACT_ID,
LARGE_NEG_ENABLED_FLAG,
AUCTION_HEADER_ID_ORIG_AMEND,
OPEN_AUCTION_NOW_FLAG,
PUBLISH_AUCTION_NOW_FLAG,
OPEN_BIDDING_DATE,
VIEW_BY_DATE,
AUTO_EXTEND_FLAG,
AUTO_EXTEND_NUMBER,
BID_VISIBILITY_CODE,
PRICE_DRIVEN_AUCTION_FLAG,
MIN_BID_DECREMENT,
LINE_ATTRIBUTE_ENABLED_FLAG, --19
PF_TYPE_ALLOWED,
PROGRESS_PAYMENT_TYPE,
TRADING_PARTNER_CONTACT_NAME,
AWARD_BY_DATE,
REMINDER_DATE,
NUMBER_OF_LINES,
EVENT_ID,
TRADING_PARTNER_NAME,
NOTE_TO_BIDDERS,
PRICE_TIERS_INDICATOR
INTO
l_draft_locked,
l_draft_locked_by_contact_id,
l_doctype_id,
l_auction_currency_precision,
l_close_bidding_date,
l_contract_type,
l_global_agreement_flag,
l_allow_other_bid_currency,
l_bid_ranking,
l_po_start_date,
l_po_end_date,
l_trading_partner_id,
l_full_quantity_bid_code,
l_bid_list_type,
l_amendment_number,
l_auction_round_number,
l_document_number,
l_auction_header_id_prev_round,
l_auction_header_id_prev_amend,
l_auction_origination_code,
l_auction_title,
l_trading_partner_contact_id,
l_large_neg_enabled_flag,
l_auction_header_id_orig_amend,
l_open_auction_now_flag,
l_publish_auction_now_flag,
l_open_bidding_date,
l_view_by_date,
l_auto_extend_flag,
l_auto_extend_number,
l_bid_visibility_code,
l_price_driven_auction_flag,
l_min_bid_decrement,
l_line_attribute_enabled_flag,
l_pf_type_allowed,
l_progress_payment_type,
l_trading_partner_contact_name,
l_award_by_date,
l_reminder_date,
l_number_of_lines,
l_event_id,
l_trading_partner_name,
l_note_to_bidders,
l_price_tiers_indicator
FROM
pon_auction_headers_all
WHERE
auction_header_id = p_auction_header_id;
SELECT COUNT(auction_header_id)
INTO l_invitees_count
FROM pon_bidding_parties
WHERE auction_header_id = p_auction_header_id;
PON_NEG_UPDATE_PKG.CAN_EDIT_DRAFT_AMEND (
p_auction_header_id_prev_doc => l_auction_header_id_prev_amend,
x_error_code => l_error_code);
message => 'The return code from call to PON_NEG_UPDATE_PKG.CAN_EDIT_DRAFT_AMEND = ' || l_error_code);
UPDATE PON_AUCTION_HEADERS_ALL
SET
DRAFT_LOCKED = 'N',
DRAFT_LOCKED_BY = NULL,
DRAFT_LOCKED_BY_CONTACT_ID = NULL,
DRAFT_LOCKED_DATE = NULL,
DRAFT_UNLOCKED_BY = p_company_party_id,
DRAFT_UNLOCKED_BY_CONTACT_ID = p_user_party_id,
DRAFT_UNLOCKED_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
message => 'Calling update lines before publish');
update_lines_before_publish (
x_result => l_result,
x_error_code => l_error_code,
x_error_message => l_error_msg,
p_auction_header_id => p_auction_header_id,
p_close_bidding_date => l_close_bidding_date,
p_user_id => p_user_id,
p_login_id => l_login_id);
message => 'Calling update header before publish');
update_header_before_publish (
x_result => l_result,
x_error_code => l_error_code,
x_error_message => l_error_msg,
p_auction_header_id => p_auction_header_id);
message => 'Calling update_to_new_document');
PON_NEG_UPDATE_PKG.update_to_new_document (
p_auction_header_id_curr_doc => p_auction_header_id,
p_doc_number_curr_doc => l_document_number,
p_auction_header_id_prev_doc => l_prev_document_number,
p_auction_origination_code => l_auction_origination_code,
p_is_new => 'N',
p_is_publish => 'Y',
p_transaction_type => l_transaction_type,
p_user_id => p_user_id,
x_error_code => l_error_code,
x_error_msg => l_error_msg);
message => 'Exception in update_to_new_document call = ' || l_error_msg);
message => 'Inserting row into pon discussions');
PON_CONTERMS_UTL_PVT.updatedelivonamendment (
p_auction_header_id_orig => l_auction_header_id_orig_amend,
p_auction_header_id_prev => l_auction_header_id_prev_amend,
p_doc_type_id => l_doctype_id,
p_close_bidding_date => l_close_bidding_date,
x_result => l_return_status,
x_error_code => l_error_code,
x_error_message => l_error_msg);
UPDATE PON_AUCTION_HEADERS_ALL
SET
AUTO_EXTEND_TYPE_FLAG = 'FROM_AUCTION_CLOSE_DATE',
AUTO_EXTEND_DURATION = NULL,
AUTO_EXTEND_NUMBER = NULL,
AUTO_EXTEND_ALL_LINES_FLAG = 'Y',
AUTO_EXTEND_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET
AUTO_EXTEND_NUMBER = g_unlimited_int,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
UPDATE pon_auction_headers_all
SET
min_bid_change_type = null,
price_driven_auction_flag = 'N',
min_bid_decrement = NULL,
last_update_date = sysdate,
last_updated_by = p_user_id
WHERE
auction_header_id = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET
MIN_BID_CHANGE_TYPE = NULL,
MIN_BID_DECREMENT = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET
MIN_BID_CHANGE_TYPE = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET
AUCTION_STATUS = 'ACTIVE',
REQUEST_ID = NULL,
REQUESTED_BY = NULL,
REQUEST_DATE = NULL,
OPEN_BIDDING_DATE = l_open_bidding_date,
VIEW_BY_DATE = l_view_by_date,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
AUCTION_HEADER_ID = p_auction_header_id;