[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
print_debug_log(l_module,'Before insert all valiations of validate_creation for p_batch_id = '||p_batch_id);
SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = p_auction_header_id;
SELECT
pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules,
PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
SELECT price_tiers_indicator INTO l_price_tiers_indicator
FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
UPDATE pon_auc_price_breaks_interface pb_int
SET auction_line_number =
(SELECT line_number
FROM pon_auction_item_prices_all
WHERE LINE_NUM_DISPLAY = pb_int.DOCUMENT_DISP_LINE_NUMBER
AND auction_header_id = p_auction_header_id
)
WHERE batch_id = p_batch_id;
UPDATE pon_auc_price_breaks_interface pb_int
SET auction_line_number =
(SELECT line_number
FROM pon_auction_item_prices_all
WHERE DOCUMENT_DISP_LINE_NUMBER = pb_int.DOCUMENT_DISP_LINE_NUMBER
AND auction_header_id = p_auction_header_id
)
WHERE batch_id = p_batch_id;
PROCEDURE: UPDATE_SHIPMENT_NUMBER PRIVATE
PARAMETERS:
COMMENT : This procedure is used to update the shipment number
======================================================================*/
PROCEDURE update_shipment_number(p_batch_id NUMBER, p_auction_header_id NUMBER)
AS
CURSOR c_interface_lines
IS
SELECT DISTINCT auction_line_number
FROM pon_auc_price_breaks_interface
WHERE BATCH_ID = p_batch_Id
AND AUCTION_HEADER_ID = p_auction_header_id;
SELECT interface_line_id
FROM pon_auc_price_breaks_interface
WHERE BATCH_ID =p_batch_Id
AND AUCTION_HEADER_ID = p_auction_header_id
AND auction_line_number = l_line_number
AND Nvl(shipment_number,-1) NOT IN(SELECT shipment_number FROM pon_auction_shipments_all
WHERE auction_header_id = p_auction_header_id
AND line_number = l_line_number );
SELECT Nvl(max(shipment_number),-1) INTO l_shipment_number
FROM pon_auction_shipments_all
WHERE auction_header_id = p_auction_header_id
AND line_number = c_interface_lines_rec.auction_line_number;
UPDATE pon_auc_price_breaks_interface SET shipment_number = l_shipment_number
WHERE batch_id= p_batch_id
AND auction_header_id = p_auction_header_id
AND interface_line_id = c_interface_ship_count_rec.interface_line_id;
END update_shipment_number;
update_shipment_number(p_batch_id, p_auction_header_id);
-- Update lines table with values in the interface table
MERGE INTO pon_auction_shipments_all sl
USING
(SELECT
pasi.auction_header_id,
pasi.auction_line_number,
pasi.document_disp_line_number,
pasi.batch_id,
pasi.interface_line_id,
pasi.shipment_type,
pasi.shipment_number,
pasi.ship_to_organization_id,
pasi.ship_to_location_id,
pasi.quantity,
pasi.price,
pasi.min_quantity,
pasi.max_quantity,
pasi.org_id,
pasi.effective_start_date,
pasi.effective_end_date,
paip.price_break_type,
pah.price_tiers_indicator
FROM pon_auc_price_breaks_interface pasi,
pon_auction_item_prices_all paip,
pon_auction_headers_all pah
WHERE pasi.batch_id = p_batch_id
AND pasi.auction_header_id = p_auction_header_id
AND pasi.auction_header_id = paip.auction_header_id
AND pasi.auction_line_number = paip.line_number
AND pah.auction_header_id = pasi.auction_header_id
AND Nvl(paip.clm_info_flag,'N') = 'N'
) ship
ON (sl.shipment_number = ship.shipment_number
AND sl.line_number = ship.auction_line_number
AND sl.auction_header_id = ship.auction_header_id)
WHEN MATCHED THEN
UPDATE SET
sl.shipment_type = ship.shipment_type,
sl.ship_to_organization_id = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_organization_id,null),
sl.ship_to_location_id = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_location_id,null),
sl.quantity = decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.quantity,ship.min_quantity),
sl.PRICE = ship.price,
sl.max_quantity = decode(ship.price_tiers_indicator, 'QUANTITY_BASED',ship.max_quantity,null),
sl.org_id = ship.org_id,
sl.effective_start_date = decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_start_date,null),
sl.effective_end_date = decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_end_date,null),
sl.last_update_date = sysdate,
sl.last_updated_by = fnd_global.user_id,
sl.last_update_login = fnd_global.login_id
WHEN NOT MATCHED THEN
INSERT (
AUCTION_HEADER_ID,
LINE_NUMBER,
SHIPMENT_NUMBER,
shipment_type,
ship_to_organization_id,
ship_to_location_id,
quantity,
org_id,
PRICE, -- Auction Currency price
effective_start_date,
effective_end_date,
max_quantity,
has_price_differentials_flag,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES (
ship.auction_header_id,
ship.auction_line_number,
ship.shipment_number,
ship.shipment_type,
decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_organization_id,null),
decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.ship_to_location_id,null),
decode(ship.price_tiers_indicator, 'PRICE_BREAKS',ship.quantity,ship.min_quantity),
ship.org_id,
ship.price,
decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_start_date,null),
decode(ship.price_break_type, 'NON-CUMULATIVE',ship.effective_end_date,null),
decode(ship.price_tiers_indicator, 'QUANTITY_BASED',ship.max_quantity,null),
'N',
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
fnd_global.login_id
) ;
UPDATE pon_auction_item_prices_all
SET has_shipments_flag= 'Y'
WHERE auction_header_id= p_auction_header_id
AND line_number IN(SELECT line_number FROM pon_auc_price_breaks_interface
WHERE batch_id = p_batch_id
AND auction_header_id = p_auction_header_id);
delete from pon_auc_price_breaks_interface where batch_id = p_batch_id;
l_progress := 'delete from pon_auc_shipments_interface completed for p_batch_id = '||p_batch_id;
SELECT price_tiers_indicator INTO l_price_tiers_indicator
FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
UPDATE pon_auc_price_breaks_interface pb_int
SET SHIP_TO_ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM org_organization_definitions
WHERE organization_code = pb_int.SHIP_TO_ORGANIZATION
)
WHERE batch_id = p_batch_id
AND SHIP_TO_ORGANIZATION IS NOT NULL;
UPDATE pon_auc_price_breaks_interface pb_int
SET ship_to_location_id =
(SELECT MAX(location_id)
FROM po_ship_to_loc_org_v po_v
WHERE replace(po_v.location_code,fnd_global.local_chr(9),'') = pb_int.ship_to_location
)
WHERE batch_id = p_batch_id
AND ship_to_location IS NOT null;
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
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- Validate the Line Number
WHEN ( sel_line_number IS NULL) THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_INVALID_LINE_NUM', -- 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
l_exp_date , -- EXPIRATION_DATE
null , -- TOKEN1_NAME
null , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- Validate Agreement Release Quantity for price breaks
/* WHEN ( Nvl(sel_agreement_rel_qty,'NONE') NOT IN (pon_auction_pkg.getmessage('PON_AUC_NON_CUMULATIVE'),pon_auction_pkg.getmessage('PON_AUC_CUMULATIVE'))
AND nvl(sel_clm_info_flag,'N') = 'N')
THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AGREEMENT_RELEASE_QUANTITY'),
l_interface_type , --INTERFACE_TYPE
'PON_INVALID_AGR_REL_QTY', -- 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
l_exp_date , -- EXPIRATION_DATE
null , -- TOKEN1_NAME
null , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)*/
-- Validate Response Type for price breaks
WHEN Nvl(sel_price_break_type,'NONE') = 'NONE'
AND nvl(sel_clm_info_flag,'N') = 'N'
THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_AUC_BAD_PBTYPE_SHIPS', -- 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
l_exp_date , -- EXPIRATION_DATE
'LINENUMBER' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_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 (
nvl(sel_clm_info_flag,'N') = 'N'
AND 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
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_SHIP_TO_LOCATION'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_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_quantity IS NULL
AND nvl(sel_clm_info_flag,'N') = '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 ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_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
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_QUANTITY'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- the price break price should be positive
WHEN ( sel_price < 0 AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_TARGET_PRICE'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE START DATE AFTER SYSDATE OR CLOSE DATE
WHEN (
nvl(sel_clm_info_flag,'N') = 'N'
AND sel_effective_start_date IS NOT NULL
AND sel_effective_start_date <= NVL (sel_close_bidding_date, SYSDATE) ) THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
l_interface_type , --INTERFACE_TYPE
NVL2 (sel_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_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 (sel_close_bidding_date, SYSDATE)
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
l_interface_type , --INTERFACE_TYPE
NVL2 (sel_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- Price Breaks should not be entered for Fixed price line types
WHEN ( sel_price_tiers_indicator = 'PRICE_BREAKS'
AND sel_order_type_lookup_code in ('FIXED PRICE','AMOUNT')
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_AUCTS_PB_FP_LINE_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
/* -- Temp based labor cannot have cumilative response
WHEN ((sel_PURCHASE_BASIS = 'TEMP LABOR' and sel_order_type_lookup_code = 'RATE') and
sel_response_type = pon_auction_pkg.getMessage('PON_AUC_CUMULATIVE')
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_AUCTS_PB_TBL_LINE_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
) */
-- EFFECTIVE START DATE SHOULD BE AFTER PO START DATE
WHEN ( sel_po_start_date IS NOT NULL
AND sel_effective_start_date IS NOT NULL
AND sel_effective_start_date < sel_po_start_date
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- EFFECTIVE END DATE SHOULD BE AFTER PO START DATE
WHEN ( sel_po_start_date IS NOT NULL
AND sel_effective_end_date IS NOT NULL
AND sel_effective_end_date < sel_po_start_date
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- the effective start date should be before po end date if both are entered
WHEN ( sel_po_end_date IS NOT NULL
AND sel_effective_start_date IS NOT NULL
AND sel_effective_start_date > sel_po_end_date
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_FROM'),
l_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*/ null , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_number , -- LINE_NUMBER
sel_shipment_number , -- SHIPMENT_NUMBER
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- effective end date should be before the po end date
WHEN ( sel_po_end_date IS NOT NULL
AND sel_effective_end_date IS NOT NULL
AND sel_effective_end_date > sel_po_end_date
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
batch_id ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUC_EFFEC_TO'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- validating ship_to_organization
WHEN ( sel_ship_to_organization IS NOT null
and sel_ship_to_organization_id IS null
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
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 ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_SHIP_TO_ORGANIZATION'),
l_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
sel_line_number , -- LINE_NUMBER
sel_shipment_number , -- SHIPMENT_NUMBER
l_exp_date , -- EXPIRATION_DATE
'ITEMNUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'SHIPNUM' , -- TOKEN2_NAME
null, -- TOKEN2_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- validating ship_to_location
WHEN ( sel_ship_to_location IS NOT null
and sel_ship_to_location_id IS null
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
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 ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_SHIP_TO_LOCATION'),
l_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
sel_line_number , -- LINE_NUMBER
sel_shipment_number , -- SHIPMENT_NUMBER
l_exp_date , -- EXPIRATION_DATE
'ITEMNUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
'SHIPNUM' , -- TOKEN2_NAME
null, -- TOKEN2_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
SELECT paip.line_number sel_line_number ,
PAIP.document_disp_line_number sel_document_disp_line_number,
papbi.shipment_number sel_shipment_number ,
papbi.interface_line_id interface_line_number,
papbi.PRICE sel_price ,
papbi.min_quantity sel_min_quantity ,
papbi.max_quantity sel_max_quantity ,
papbi.quantity sel_quantity ,
papbi.effective_end_date sel_effective_end_date ,
papbi.effective_start_date sel_effective_start_date ,
papbi.ship_to_location_id sel_ship_to_location_id ,
papbi.ship_to_organization_id sel_ship_to_organization_id ,
paha.po_start_date sel_po_start_date,
paha.po_end_date sel_po_end_date,
paha.price_tiers_indicator sel_price_tiers_indicator,
PAIP.order_type_lookup_code sel_order_type_lookup_code,
paip.PURCHASE_BASIS sel_PURCHASE_BASIS,
paip.clm_info_flag sel_clm_info_flag,
paip.price_break_type sel_price_break_type,
paha.global_agreement_flag sel_global_agreement_flag,
papbi.ship_to_organization sel_ship_to_organization,
papbi.ship_to_location sel_ship_to_location,
paha.close_bidding_date sel_close_bidding_date
FROM PON_AUCTION_ITEM_PRICES_ALL PAIP,
pon_auc_price_breaks_interface papbi,
pon_auction_headers_all paha
WHERE PAIP.AUCTION_HEADER_ID(+) = p_auction_header_id
AND papbi.AUCTION_HEADER_ID = p_auction_header_id
AND paha.AUCTION_HEADER_ID = p_auction_header_id
AND PAIP.LINE_NUMBER(+) = papbi.auction_LINE_NUMBER
AND papbi.batch_id = p_batch_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
-- Validate the Line Number
WHEN ( sel_line_number IS NULL) THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_INVALID_LINE_NUM', -- 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
l_exp_date , -- EXPIRATION_DATE
null , -- TOKEN1_NAME
null , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- min quantity should not be empty or negative
WHEN ( sel_min_quantity IS NOT NULL
AND sel_min_quantity < 0
AND sel_min_quantity <> g_null_int
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_TIERS_MIN_QUANTITY'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- max quantity should not be empty or negative
WHEN ( sel_max_quantity IS NOT NULL
AND sel_max_quantity < 0
AND sel_max_quantity <> g_null_int
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_TIERS_MAX_QUANTITY'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- max quantity should not be less than min qauntity
WHEN ( sel_max_quantity IS NOT NULL
AND sel_min_quantity IS NOT NULL
AND sel_max_quantity < sel_min_quantity
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_TIERS_MIN_QUANTITY'),
l_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_pbs_type , -- ENTITY_TYPE
p_auction_header_id , -- AUCTION_HEADER_ID
sel_line_number , -- LINE_NUMBER
sel_shipment_number , -- SHIPMENT_NUMBER
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- price should be positive
WHEN ( sel_price < 0
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_TARGET_PRICE'),
l_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number, -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
-- Quantity Based Price Tiers should be entered only for than rate based temp labor and goods
WHEN (
NOT ((sel_PURCHASE_BASIS = 'TEMP LABOR' and sel_order_type_lookup_code = 'RATE') OR
sel_PURCHASE_BASIS = 'GOODS')
AND nvl(sel_clm_info_flag,'N') = 'N') THEN
INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
REQUEST_ID ,
BATCH_ID ,
ENTITY_TYPE ,
AUCTION_HEADER_ID ,
LINE_NUMBER ,
SHIPMENT_NUMBER ,
EXPIRATION_DATE ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type , --INTERFACE_TYPE
'PON_AUCTS_QB_LINE_TYP_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
l_exp_date , -- EXPIRATION_DATE
'LINENUM' , -- TOKEN1_NAME
sel_document_disp_line_number , -- TOKEN1_VALUE
interface_line_number ,
l_user_id , -- CREATED_BY
sysdate , -- CREATION_DATE
l_user_id , -- LAST_UPDATED_BY
sysdate , -- LAST_UPDATE_DATE
l_login_id -- LAST_UPDATE_LOGIN
)
SELECT PAIP.LINE_NUMBER sel_line_number ,
PAIP.DOCUMENT_DISP_LINE_NUMBER sel_document_disp_line_number,
papbi.shipment_number sel_shipment_number ,
papbi.INTERFACE_LINE_id INTERFACE_LINE_NUMBER,
papbi.PRICE sel_price ,
papbi.min_quantity sel_min_quantity ,
papbi.max_quantity sel_max_quantity ,
papbi.QUANTITY sel_quantity ,
papbi.EFFECTIVE_END_DATE sel_effective_end_date ,
papbi.EFFECTIVE_START_DATE sel_effective_start_date ,
papbi.SHIP_TO_LOCATION_ID sel_ship_to_location_id ,
papbi.SHIP_TO_ORGANIZATION_ID sel_ship_to_organization_id ,
paha.po_start_date sel_po_start_date,
paha.po_end_date sel_po_end_date,
paha.price_tiers_indicator sel_price_tiers_indicator,
paip.order_type_lookup_code sel_order_type_lookup_code,
paip.PURCHASE_BASIS sel_PURCHASE_BASIS,
paip.clm_info_flag sel_clm_info_flag,
paha.close_bidding_date sel_close_bidding_date
FROM PON_AUCTION_ITEM_PRICES_ALL PAIP,
pon_auc_price_breaks_interface papbi,
pon_auction_headers_all paha
WHERE PAIP.AUCTION_HEADER_ID(+) = p_auction_header_id
AND papbi.AUCTION_HEADER_ID = p_auction_header_id
AND paha.AUCTION_HEADER_ID = p_auction_header_id
AND PAIP.LINE_NUMBER(+) = papbi.auction_LINE_NUMBER
AND papbi.batch_id = p_batch_id;
INSERT INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME,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
distinct
pon_auction_pkg.getMessage('PON_AUCTS_LINENUMBER'),
l_interface_type, --INTERFACE_TYPE
'PON_AUC_OVERLAP_RANGES_QT', -- 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
paip.document_disp_line_number, -- LINE_NUMBER
l_exp_date, -- EXPIRATION_DATE
'LINENUM', -- TOKEN1_NAME
paip.document_disp_line_number, -- TOKEN1_VALUE
l_user_id, -- CREATED_BY
sysdate, -- CREATION_DATE
l_user_id, -- LAST_UPDATED_BY
LAST_UPDATE_DATE,
l_login_id -- LAST_UPDATE_LOGIN
FROM pon_auc_price_breaks_interface paip
WHERE paip.auction_header_id = p_auction_header_id
AND paip.batch_id = p_batch_id
AND paip.auction_line_number IN
( (
SELECT pasa1.auction_line_number
FROM pon_auc_price_breaks_interface pasa1,
pon_auc_price_breaks_interface pasa
WHERE pasa1.auction_header_id = p_auction_header_id
and pasa.auction_header_id = p_auction_header_id
AND pasa.auction_line_number = pasa1.auction_line_number
AND pasa1.batch_id = p_batch_id
AND pasa.batch_id = p_batch_id
AND pasa.INTERFACE_LINE_ID <> pasa1.INTERFACE_LINE_ID
AND pasa1.min_quantity <= pasa.min_quantity
and pasa.min_quantity <= pasa1.max_quantity)
UNION
(SELECT pasa1.auction_line_number
FROM pon_auc_price_breaks_interface 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 pasa1.batch_id = p_batch_id
AND pasa.line_number = pasa1.auction_line_number
AND Nvl(pasa1.shipment_number,-999) <> pasa.shipment_number
AND pasa.quantity <= pasa1.min_quantity
and pasa1.min_quantity <= pasa.max_quantity ));